作者：祝洪忠 转自：Excel之家ExcelHome
小伙伴们好啊，前段时间给大家分享了一期关于数据查询的经典数组公式一对多、多对多查询经典公式，但是很多小伙伴说，数组公式太复杂，直接看晕了
今天老祝和就和大家分享一组特别简单的方法，也能实现一对多、多对多的数据查询需要。
先看数据，是一份各部门的员工信息表：
咱们首先要完成的是：
一对多查询
一对多查询，就是符合条件的有多个结果。本例中，咱们要根据G2单元格指定的部门，提取出所有财务部的人员信息。
步骤1
在D2单元格输入以下公式，向下拖动复制公式：
=(A2=G$2)+D1
(A2=G$2)部分，用A列的部门与G2单元格指定的部门进行对比，如果相同，返回逻辑值TRUE，否则返回逻辑值FALSE。
然后将逻辑值与前一个单元格中的数值相加。
在四则运算中，TRUE的作用相当于1，FALSE的作用相当于0。也就是如果部门条件符合了，D列的数值就增加1，否则还等于前一个数值。
注意观察，1、2、3、4……这些序号首次出现的行，就是符合条件的记录。
步骤2
F6单元格输入以下公式，向下向右拖动：
=IFERROR(INDEX(A:A,MATCH(ROW(A1),$D:$D,0)),"")
这个公式稍长了一点，咱们拆解看看：
先说说ROW(A1)部分：
ROW函数的作用是返回参数的行号，ROW(A1)结果就是A1的行号1。当公式向下复制时，ROW(A1)会依次变成ROW(A2)、ROW(A3)……，最终得到递增的序号1、2、3……，结果用作MATCH函数的查找值。
再看MATCH(ROW(A1),$D:$D,0)部分。
MATCH函数的作用是查询指定内容在一行或一列中首次出现的位置。这里要查询的内容是ROW(A1)的结果(也就是数值1)在D列首次出现的位置，查询结果为2。
公式向下复制时，会依次查询1、2、3……在D列首次出现的位置。
得到位置信息了，接下来再使用INDEX函数在A列返回对应位置的内容：
INDEX(A:A,MATCH(ROW(A1),$D:$D,0))
当公式一直向下复制时，增加的序号在D列找不到了，公式会返回错误值，所以在最外层加上IFERROR函数，将错误值转换为空文本""。
接下来咱们再看看如何实现：
多对多查询
多对多查询，就是根据多个条件返回多个指定条件的内容。接下来要根据G2单元格指定的部门和G3单元格指定的性别，提取出所有财务部的女性人员信息。
咱们只要简单修改一下D2单元格的公式就可以了，F列的原有的公式不用管它：
=(A2=G$2)*(C2=G$3)+D1
看出门道了吗？多个条件同时符合，就是条件1*条件2。
多个条件符合其一
现在更进一步，要根据G2和G3单元格指定的部门，提取出两个部门的所有记录。
同样，只要简单修改一下D2单元格的公式就可以了，F列的原有的公式不用管它：
=(A2=G$2)+(A2=G$3)+D1
看出门道了吗？多个条件符合其一，就是条件1+条件2。
多个条件混合
现在再深入一步，要根据G1~G3单元格指定的条件，提取出财务部女性的记录以及安监部的所有记录。
仍然只要简单修改一下D2单元格的公式就可以了，F列的原有的公式不用管它：
=(A2=G$1)*(C2=G$2)+(A2=G$3)+D1
看出门道了吗？要同时符合的多组条件就用乘法，对符合其一的条件就用加法。
好了，今天的分享就是这些吧，最后留给大家一个小问题：如果要提取财务部女性和销售部女性的所有记录，D2单元格的公式要如何修改呢？
图文制作：祝洪忠

日常中，我们所用到的匹配公式，如果有多项结果出现，往往只会返回一项结果，本日学习利用公式如何提取多项结果。具体案例如下所示：提取出广州市下的所有区，公式为【=IFERROR(VLOOKUP($G$3,OFFSET($D$1,MATCH($H2,$E$1:$E$22,0),0,ROW($D$22),2),2,0),""))】
公式解析：
1、首先想到匹配公式vlookup，如果返回一个结果，那么公式应该写作【=VLOOKUP(G3,D:E,2,0)】，但由数据可看到匹配的结果不止一个，所以考虑公式排除应用，如已经返回东山区，那么在单元格下拉过程中，考虑从荔湾区开始匹配；
2、所以，需要偏移量函数offset加持
OFFSET(reference, rows, cols, [height], [width])，
参数依次为OFFSET(参考位置, 向上向下偏移多少单元格, 左右偏移多少,高多少单元格, 宽多少列单元格)
如返回从荔湾区开始的区域，那么公式应该为【OFFSET($D$1,2,0,22,2,0)】，从D1开始，向下偏移两个单元格，向右不偏移，即D3，即是以D3作为左上角单元格，返回一个22行2列的区域。那么以此为查找区域，vlookup函数再去匹配，自然返回荔湾区。
3、但要做到匹配的范围随着单元格下拉而改变，就需要利用查找函数match
MATCH(lookup_value, lookup_array, [match_type])
Match(需要查找的值，查找的范围，匹配类型）
MATCH($H2,$E$1:$E$22,0)，则表示，查找h2在E1:E22中的精确位置，加上$表示绝对值，那么【县/县级市/区】在E1:E22的位置为1，所以返回1，如果下拉到荔湾区，则会返回东山区在E列中的位置，返回2，如此，与offset函数结合，便满足了范围的变换。
而row($D$22）则是返回D22的行号，在实际应用中，可根据需要自行确定这个数字，可以是设置一个较大的范围，也可以如我一样返回最后一个单元格的行号，可预留足够的范围。
4、iferror的作用在于，如果公式返回错误值，则我们显示空值("")，若果结果无误，则返回原值
IFERROR(value, value_if_error) 
iferror（值，如果错误返回什么）

在此使用的包是pandas，因为其可以同时处理xls和xlsx两种excel文件。
使用pd读取有多个sheet的excel
pandas读取的常用格式pd.read_excel(file, sheet_name)，其中sheetname可以使用数字进行替代，从0开始，默认为0
pandas写入的格式为data.to_excel('filename',sheet_name='A')
处理的excel信息如下：
代码如下：
import pandas as pdpath = 'G:\动力系\新建文件夹\什么.xls'data = pd.read_excel(path,None)#读取数据,设置None可以生成一个字典，字典中的key值即为sheet名字，此时不用使用DataFram，会报错print(data.keys())#查看sheet的名字for sh_name in data.keys(): print('sheet_name的名字是：',sh_name) sh_data = pd.DataFrame(pd.read_excel(path,sh_name))#获得每一个sheet中的内容 print(sh_data)
1
2
3
4
5
6
7
8
9
10
结果：
获得某一行，某一列的值
pd对excel的相关基本操作看下面的链接
https://blog.csdn.net/weixin_43245453/article/details/90056884
数据使用的是第一个sheet
import pandas as pdpath = 'G:\动力系\新建文件夹\什么.xls'data = pd.DataFrame(pd.read_excel(path))#读取数据,设置None可以生成一个字典，字典中的key值即为sheet名字，此时不用使用DataFram，会报错print(data.index)#获取行的索引名称print(data.columns)#获取列的索引名称print(data['姓名'])#获取列名为姓名这一列的内容print(data.loc[0])#获取行名为0这一行的内容
1
2
3
4
5
6
7
结果：
对某一列的信息进行筛选
筛选使用的是data.loc[列名称 = 提取的信息]
假如我要提取院系下面的动力，代码如下：
import pandas as pdpath = 'G:\动力系\新建文件夹\什么.xls'data = pd.DataFrame(pd.read_excel(path))#读取数据,设置None可以生成一个字典，字典中的key值即为sheet名字，此时不用使用DataFram，会报错result = data.loc[data['院系'] == '动力']#获取列明为院系，内容为动力的内容print(result)
1
2
3
4
5
结果如下：

	

本文主要讲解怎样使用Python对多个excel文件任意区域内容进行批量提取。在看这篇文章之前，默认你已经具备了python的基础知识。如果没有任何基础，建议先看看上一篇文章：【学习心得】我的Python入门之旅。
话不多说，直接上原始数据给大家看，注意，本文所使用数据全部为模拟假设数据，并非真实数据，请勿他用。本次使用数据为8个excel文件，理论上80个、800个excel都可以用本文方法进行操作…所以，主要学习方法应用…
数据说明，在文件夹里有多个excel文件如下图：
同时，文件里的数据结构如下图，给出两个示例图：
上面示例数据的数据结构是一样的，注意现在提出的提取要求：把上面8个城市(也就是8个文件)的2005-2010年的前5列数据提取至一个sheet表里，该怎么办呢？当然了，8个城市完全可以手动复制粘贴，那么80个、800个城市呢？显然不行，工作量太大。这里我们可以使用python进行批量提取。当然本文只是应用了python的其中一些库，还有很多种方法可以满足这个需求，暂且不论。
本文主要使用到两个python库，一个是os库，主要用来对系统内的一些处理进行响应；另一个是openpyxl库，主要对excel文件进行操作处理。接下来，我将直接上全部代码，然后再逐一分析每段代码背后的故事…
完整代码如下：
#coding=utf-8
import openpyxl
import os
wb2=openpyxl.Workbook()
wb2.save('newdata.xlsx')
print u"新建Excel文件成功"
filepath="c:\\cz\\data"
dataset=os.listdir(filepath)
wb2=openpyxl.load_workbook('newdata.xlsx')
sheets2=wb2.sheetnames
sheet2=wb2[sheets2[0]]
k=1
countnum=1
for i in dataset:
    print "run the number:{0}   {1}".format(countnum,i)
    wb1=openpyxl.load_workbook(filepath+"\\{}".format(i))
    sheets1=wb1.sheetnames
    sheet1=wb1[sheets1[0]]
    j=k
    for m in range(6,12):
        for n in range(1,8):
            cell1=sheet1.cell(row=m,column=n).value
            sheet2.cell(row=j,column=n).value=cell1
        j=j+1
    k=j
    countnum=countnum+1
wb2.save('newdata.xlsx')
wb1.close()
wb2.close()
print "run process successfully"
运行代码后，在代码py格式文件目录下会自动生成一个新的excel文件：newdata.xlsx。这就是我们需要的文件，代码运行过程如下：
代码运行结果如下：
这里的newdata.xlsx就是我们需要的文件，打开newdata.xlsx文件如下：
可以想象，如果有成千上百的excel文件需要我们提取固定区域的数据的话，这段代码能够给我们减轻很大的负担。
不行，一路高歌猛进有点累了，先不写了，完整代码分析明天我再写个续文吧，其实有点基础的人自己就能看懂代码的意思了，不过续文我还是会写的，方便没有接触过python的人学习。共同学习，一起进步！明天继续。