-
datagridview列 值提取_EXCEL一个辅助列,搞定一对多、多对多查询
2020-12-25 02:44:22作者:祝洪忠 转自:Excel之家ExcelHome小伙伴们好啊,前段时间给大家分享了一期关于数据查询的经典数组公式一对多、多对多查询经典公式,但是很多小伙伴说,数组公式太复杂,直接看晕了今天老祝和就和大家分享一组...作者:祝洪忠 转自: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单元格的公式要如何修改呢?
图文制作:祝洪忠
-
sql结果集一对多_Excel学习day6—用vlookup提取一对多的匹配结果
2020-12-14 09:40:27日常中,我们所用到的匹配公式,如果有多项结果出现,往往只会返回一项结果,本日学习利用公式如何提取多项结果。具体案例如下所示:提取出广州市下的所有区,公式为【=IFERROR(VLOOKUP($G$3,OFFSET($D$1,MATCH($H2,...日常中,我们所用到的匹配公式,如果有多项结果出现,往往只会返回一项结果,本日学习利用公式如何提取多项结果。具体案例如下所示:提取出广州市下的所有区,公式为【=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(值,如果错误返回什么)
-
python提取excel某一行_python对excel操作获取某一列,某一行的值,对某一列信息筛选...
2020-12-03 03:13:36使用pd读取有多个sheet的excelpandas读取的常用格式pd.read_excel(file, sheet_name),其中sheetname可以使用数字进行替代,从0开始,默认为0pandas写入的格式为data.to_excel('filename',sheet_name='A')处理的...在此使用的包是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
结果如下:
-
excel批量提取多个文件单元格_【学习心得】Python批量提取多个Excel文件内容
2020-12-28 01:20:37本文主要讲解怎样使用Python对多个excel文件任意区域内容进行批量提取。在看这篇文章之前,默认你已经具备了python的基础知识。如果没有任何基础,建议先看看上一篇文章:【学习心得】我的Python入门之旅。话不多说...本文主要讲解怎样使用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的人学习。共同学习,一起进步!明天继续。
-
mybatis 一对多查询_Excel中如何一对多、多对多查询数据
2020-12-18 22:38:20所谓一对多,就是符合某个指定条件的有多个结果,要把这些结果都提取出来。下面咱们就说说一对多查询的典型用法,先看数据源:A~D列是一些员工信息,要根据F2单元格指定的学历,提取出所有“本科”的人员姓名。G2... -
python提取excel表中指定行和列的数据_Python操作Excel文件(一)- 获取Excel文件中的内容...
2020-12-01 06:18:20Excel 相信大家都不陌生,就是通过表来对数据进行展示和处理。生活中有很多的职业需要每天面对非常多的 Excel 表, 进行数据的添加、修改和查找(还包括一些统计)等操作。但是绝大数非程序员根本不知道,咱们其实... -
提取excel表数据成json格式的以及对图片重命名
2019-03-04 23:36:00意思就是有很多集,这里称他们为tv最下面这几行第一列没名字的都是单集的,这里称它们为mv需要统计所有工作表里面的数据把tv放一个大的json里面把mv放一个大的json里面 2、需要检索图片名列。然后检测文件夹里面... -
数据提取_EXCEL中数据提取
2021-01-14 20:08:331、按分隔符拆分按分隔符拆分是分列的常规用法,是根据分隔符把一列拆分成多列,首先我们先来看一个案例,在提取数据的时候,销售将日期和销售金额混在一个数列,不利于后续分析数据,我们就可以采取按分隔符... -
如何将批量将一个文件夹中eml文件中关键词后的数据提取至excel
2020-06-16 17:57:40如何将批量将一个...我需要对GPS观测数据进行编辑,观测数据报告返回为邮件形式,导出后以eml格式存放在A文件夹中,邮件非常多,我想要批量将eml文件中关键词后所需数据提取到excel进行编辑, 求大佬施以援手 ... -
python 文件学习心得_【学习心得】Python批量提取多个Excel文件内容
2021-02-01 23:06:16本文主要讲解怎样使用Python对多个excel文件任意区域内容进行批量提取。在看这篇文章之前,默认你已经具备了python的基础知识。如果没有任何基础,建议先看看上一篇文章:【学习心得】我的Python入门之旅。话不多说... -
提取多个字段_跨表提取数据,Excel函数高手被名不经传的Microsoft Query 直接KO
2021-01-15 11:46:45点击图片抢购Excel等视频课程编按:跨表提取数据很多...它不但操作简易,轻易解决“一对多”,而且它生成的结果表可以与数据源形成动态链接,数据源变化了,结果也会动态更新!今天给大家分享一个很少人用但有奇效的... -
vb整合多个excel表格到一张_EXCEL函数公式大全利用VLOOKUP函数INDIRECT从多个表格中提取数据...
2020-12-24 01:24:29EXCEL函数公式大全之利用VLOOKUP、函数INDIRECT、自定义名称从多个表格中提取数据。EXCEL函数与公式在工作中使用非常的频繁,会不会使用公式直接决定了我们的工作效率,今天我们来学习一下提高我们工作效率的函数... -
提取一行数据列表_生日/电话一秒提取!高手必备Excel提数据神技
2021-01-07 09:14:38本文来自Pconline很多小伙伴都对身份证提取出生日期不感陌生,一般情况下我们会选用MID函数完成此类操作。但如果只是临时使用,MID函数不免显得有些麻烦。其实提取出生日期并不只有函数操作这一条路,借助一组神级... -
excel提取不规则字段_Excel提取中文、英文和数字,其实很简单
2020-12-24 01:24:08小伙伴们好啊,日常工作中,很多小伙伴都会遇到一些不规范的数据,很多小伙伴对字符串的提取问题也是头疼不已,今天咱们就分享一期关于字符串提取的内容。一、提取字符串中的英文先来看下面的数据,是一些混到一起的... -
excel根据a列提取b列内容_excel工作案例:如何自动提取每个班级第一名学生的信息?...
2021-01-12 20:45:10如图中案例,现在有一个学生成绩数据,其中有很多个班级混在一起,现在如何自动提取每一个班级的第一名学生信息呢?首先,我们来分析一下工作思路:这里第一名的条件是“总分第一”,因此我们第一步要找出每一个班级... -
Excel批量数据提取V1.5中文绿色版
2019-08-05 23:53:16EXCEL批量数据提取软件是一款好用的数据提取软件。...软件可对文件夹中的多个exce1文件进行批量数据提取!免费版一次可扫描3个文件,提取20行数据。 注册版无数量限制!单机版(绑定电脑)68元,U盘版(绑定自备U盘 -
一个可以使用多个正则表达式进行多次尝试匹配、替换或提取的Excel VBA自定义函数(UFD)...
2017-09-01 22:21:00该自定义函数可使用多个正则表达式对目标单元格进行多次匹配尝试,如匹配成功,将停止尝试匹配其他正则表达式,并且使用该正则表达式相对应的替换表达式进行替换,返回替换结果。 您可以直接下载包含该函数代码的... -
怎样提取Excel工作表中排名前几名的名称和数据
2017-01-06 15:19:32iamlaosong文 ...1、提取的方法是用SQL语句,首先建立一个对这张表的查询,方法是数据选项卡—现有连接—浏览更多 或者 按快捷键Alt+D+D+D,然后选择要查询的Excel文件和文件中的的工作表,然后点击“属性 -
Excel一对多查询(index+small+if)
2019-01-25 19:15:00一对多查询模式化数组公式: =INDEX(区域,SMALL(IF(条件,行号数组,4^8),ROW(A1))) 三键齐按(ctrl+shift+回车) 在具有多个符合条件的情况下,提取和匹配方法~ 我们需要解决三种类型的问题: 1,李商隐第一次... -
EXCEL如何用公式提取一列中的唯一值和不重复值
2019-10-02 09:31:37说明:思路用的很新奇,也对COUNTIF有了更深一步的了解,但是,对于百行数据运算速度特别低,不适合数据多的使用 当面对一堆数据,我们要提取一列的唯一值的时候,如果单纯用人为一个个判断,显然是不科学的,... -
python对excel筛选提取文本中数字_python实现将字符串中的数字提取出来然后求和...
2020-11-30 09:21:57因工作原因,很久没有学习python知识了,感觉都快忘记了,前天看到一个练习题,如何将字符串中的数字提取出来,然后求和呢?下面我来解释一下如何通过python代码来实现。题目:字符串43…3y2.f67se2.666. 将其中的... -
excel快速合并,多表一次搞定
2010-09-27 11:06:10可以提取一个Excel文件的多个工作表,也可以提取多个Excel文件的多个工作表的数据。例如,上级部门制定了统一的Excel表格,下发到下级部门填报,上级部门收集到下级部门填报的数据后,需要对某些数据项进行汇总分析... -
单元格内多个姓名拆分成一列_【Excel技巧】在组合的内容里面把姓名提取出来...
2020-12-31 15:48:23除了直接输入外,从已存在的单元格内容中提取特定字符输入,绝对是一种省时又省事的方法,特别是对一些样式雷同的信息更是如此,比如员工、籍贯等信息。下图中是将人员姓名提取出来图中很明显,省份、姓名、手机号码... -
mysql 取字符串中最大数字_从Excel到PQ:多字符串中提取数字并求和
2020-12-23 19:55:39如下图所示,将下面的...主要思路:先将字符串的文本以“元”为分隔符拆分出来,然后再构建一个list,即{1..15},或者更大的位数,去截取拆分的每一个list的值,对这些值转换成数字,无法转化的替换成null值,取出... -
AutoCAD2008坐标提取及保存数据到excel扩展程序
2011-10-06 17:22:43因为直接对excel进行操作,速度比较慢,因此,先用“保存点”命令将提取到的点坐标保存到临时txt文件中,再利用“数据转换”命令将数据转换为excel格式。 “清楚数据”命令用于清楚临时数据文件中的数据。 从对... -
删除单元格_EXCEL单元格里提取、删除或替换部分内容
2021-01-09 17:50:33今天整理更多文本函数在实际工作中的应用,比如EXCEL单元格里提取指定符号里的内容,分离单元格里的英文字母和汉字,提取单元格里的数字等等。有些源数据比较规律,有些则比较杂乱,我们看看都有哪些方法可以对数据... -
access通过身份证号提取性别_excel:从身份证号提取年龄,两种方法,升职加薪系列...
2020-12-08 18:36:35有很多朋友问我,如何快速的从身份证号提取年龄,今天我们来分享两种方法,大家可以根据自己的对函数的了解程度,使用适合的方法。 1.粗略提取,就是以年份为准的的提取。而忽略月和日。 函数解读:=MID(B2,7,4) =... -
优秀的拆分(民间数据)c++_EXCEL中数据提取
2020-10-25 01:48:151、按分隔符拆分按分隔符拆分是分列的常规用法,是根据分隔符把一列拆分成多列,首先我们先来看一个案例,在提取数据的时候,销售将日期和销售金额混在一个数列,不利于后续分析数据,我们就可以采取按分隔符... -
excel根据a列提取b列内容_8组财务常用的Excel函数组合,拿走直接套!
2021-01-15 07:31:48在Excel公式中,有些函数是独行侠,而有些函数经常是成对出现的,今天就带大家一起盘点Excel表格中八大经典函数组合。IF + And作用:并列多条件判断 【例】如下图所示,在C列设置公式,如果A列值小于500且B列值为未... -
中jsp加载不出来layui_怎么将Excel中不重复的数据提取出来?快速提取法教给你...
2021-01-12 17:50:19在平日的工作生活中,使用Excel来处理办公数据文档,已经成为很多小伙伴们的工作日程。而Excel表格中会存储大量的工作数据,而我们还经常会遇到处理大量数据的时候,表格当中会出现一些重复的内容,而此时我们需要的...
-
用微服务spring cloud架构打造物联网云平台
-
MySQL 高可用工具 DRBD 实战部署详解
-
基于透射相位梯度超表面的宽带频率扫描欺骗表面等离子极化平面天线
-
玩转html5的canvas画图
-
attempt to invoke virtual method boolean java.lang.Boolean.booleanValue() on a null object reference
-
MySQL Router 实现高可用、负载均衡、读写分离
-
Spring IOC初始化流程
-
MySQL 管理利器 mysql-utilities
-
shell脚本编译uboot,Kernel,rootfs指定名保存到指定文件夹
-
Spring Cloud微服务学习系列6 SpringCloud基础入门
-
华为1+X——网络系统建设与运维(高级)
-
Mybatis配置-日志工厂
-
在Ubuntu20.04编译mongodb内核源码笔记
-
周成建的美邦服饰“掉队”:近两年约亏损16亿元,即将披星戴帽
-
工程制图 AutoCAD 2012 从二维到三维
-
Linux从头开始学--学习笔记4--挂载,/proc/uptime,安装anoconda,SSH
-
C和C++课程
-
使用Git管理源代码
-
1085 Perfect Sequence (25 分)
-
算法设计 Algorithm Design Solution 7.27 7.31 7.39