精华内容
下载资源
问答
  • 作者:祝洪忠 转自:Excel之家ExcelHome小伙伴们好啊,前段时间给大家分享了一期关于数据查询的经典数组公式一对多、多对多查询经典公式,但是很多小伙伴说,数组公式太复杂,直接看晕了今天老祝和就和大家分享一组...

    作者:祝洪忠 转自:Excel之家ExcelHome

    小伙伴们好啊,前段时间给大家分享了一期关于数据查询的经典数组公式一对多、多对多查询经典公式,但是很多小伙伴说,数组公式太复杂,直接看晕了

    今天老祝和就和大家分享一组特别简单的方法,也能实现一对多、多对多的数据查询需要。

    先看数据,是一份各部门的员工信息表:

    85378c36f116f8fed2a6c86fb559fb50.png

    咱们首先要完成的是:

    一对多查询

    一对多查询,就是符合条件的有多个结果。本例中,咱们要根据G2单元格指定的部门,提取出所有财务部的人员信息。

    步骤1

    在D2单元格输入以下公式,向下拖动复制公式:

    =(A2=G$2)+D1

    a578bc33b772715e0b2b1d35a28d66e4.png

    (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)),"")

    1276b9e10f97af594858b9967abb904f.png

    这个公式稍长了一点,咱们拆解看看:

    先说说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

    69d6fb9934dd1eecb135ecb9e040ad42.png

    看出门道了吗?多个条件同时符合,就是条件1*条件2

    多个条件符合其一

    现在更进一步,要根据G2和G3单元格指定的部门,提取出两个部门的所有记录。

    同样,只要简单修改一下D2单元格的公式就可以了,F列的原有的公式不用管它:

    =(A2=G$2)+(A2=G$3)+D1

    2c8a40fe1dfbf9b4902fe5df29eeeb82.png

    看出门道了吗?多个条件符合其一,就是条件1+条件2

    多个条件混合

    现在再深入一步,要根据G1~G3单元格指定的条件,提取出财务部女性的记录以及安监部的所有记录。

    仍然只要简单修改一下D2单元格的公式就可以了,F列的原有的公式不用管它:

    =(A2=G$1)*(C2=G$2)+(A2=G$3)+D1

    2003f7083913cf67eb2b7dd3952c3ab6.png

    看出门道了吗?要同时符合的多组条件就用乘法,对符合其一的条件就用加法

    好了,今天的分享就是这些吧,最后留给大家一个小问题:如果要提取财务部女性和销售部女性的所有记录,D2单元格的公式要如何修改呢?

    3d8699b673b16dfbdab82fbe10df573c.png

    图文制作:祝洪忠

    展开全文
  • 日常中,我们所用到的匹配公式,如果有项结果出现,往往只会返回项结果,本日学习利用公式如何提取多项结果。具体案例如下所示:提取出广州市下的所有区,公式为【=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),""))

    b799e370e30f6a12197b3ce521a8ddea.png

    公式解析:

    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(值,如果错误返回什么)

    展开全文
  • 使用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

    结果如下:

    展开全文
  • 本文主要讲解怎样使用Python对多excel文件任意区域内容进行批量提取。在看这篇文章之前,默认你已经具备了python的基础知识。如果没有任何基础,建议先看看上篇文章:【学习心得】我的Python入门之旅。话不多说...

    本文主要讲解怎样使用Python对多个excel文件任意区域内容进行批量提取。在看这篇文章之前,默认你已经具备了python的基础知识。如果没有任何基础,建议先看看上一篇文章:【学习心得】我的Python入门之旅。

    话不多说,直接上原始数据给大家看,注意,本文所使用数据全部为模拟假设数据,并非真实数据,请勿他用f2dbf4db09e466744227f1eb5bf355c7.pngf2dbf4db09e466744227f1eb5bf355c7.pngf2dbf4db09e466744227f1eb5bf355c7.png。本次使用数据为8个excel文件,理论上80个、800个excel都可以用本文方法进行操作…所以,主要学习方法应用…

    数据说明,在文件夹里有多个excel文件如下图:

    4ec6b53cadc04bedc2ae60233b6e416b.png

    同时,文件里的数据结构如下图,给出两个示例图:

    2be6a3a9d6cca0d7a4da043a73252e96.png130b754e227dbae3401c01d776ece6aa.png

    上面示例数据的数据结构是一样的,注意现在提出的提取要求:把上面8个城市(也就是8个文件)的2005-2010年的前5列数据提取至一个sheet表里,该怎么办呢?3ebe490f7f75a348463382b928ad8ea9.png3ebe490f7f75a348463382b928ad8ea9.png3ebe490f7f75a348463382b928ad8ea9.png当然了,8个城市完全可以手动复制粘贴,那么80个、800个城市呢?显然不行,工作量太大。这里我们可以使用python进行批量提取。当然本文只是应用了python的其中一些库,还有很多种方法可以满足这个需求,暂且不论。

    本文主要使用到两个python库,一个是os库,主要用来对系统内的一些处理进行响应;另一个是openpyxl库,主要对excel文件进行操作处理。接下来,我将直接上全部代码,然后再逐一分析每段代码背后的故事…8be332ac1030cac04cdce410b97fc997.png

    完整代码如下

    #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。这就是我们需要的文件,代码运行过程如下:

    312a8946146ed8573c3936e6e74a2848.png

    代码运行结果如下:

    fe22f7c011d4ea375f51fa3a7efd15b5.png

    这里的newdata.xlsx就是我们需要的文件,打开newdata.xlsx文件如下5e8b9e894a75c595ba930b27eaa806d9.png

    b0418c12e39f2a4c4afe1244393b8c8b.png

    可以想象,如果有成千上百的excel文件需要我们提取固定区域的数据的话,这段代码能够给我们减轻很大的负担。

    不行,一路高歌猛进有点累了01ae8095dda2a433d209058ec45a5a99.png01ae8095dda2a433d209058ec45a5a99.png01ae8095dda2a433d209058ec45a5a99.png,先不写了,完整代码分析明天我再写个续文吧,其实有点基础的人自己就能看懂代码的意思了,不过续文我还是会写的,方便没有接触过python的人学习。共同学习,一起进步!明天继续。


    展开全文
  • 所谓一对多,就是符合某个指定条件的有多个结果,要把这些结果都提取出来。下面咱们就说说一对多查询的典型用法,先看数据源:A~D列是一些员工信息,要根据F2单元格指定的学历,提取出所有“本科”的人员姓名。G2...
  • Excel 相信大家都不陌生,就是通过表来数据进行展示和处理。生活中有很的职业需要每天面对非常Excel 表, 进行数据的添加、修改和查找(还包括一些统计)等操作。但是绝大数非程序员根本不知道,咱们其实...
  • 意思就是有很集,这里称他们为tv最下面这几行第列没名字的都是单集的,这里称它们为mv需要统计所有工作表里面的数据把tv放个大的json里面把mv放个大的json里面 2、需要检索图片名列。然后检测文件夹里面...
  • 1、按分隔符拆分按分隔符拆分是分列的常规用法,是根据分隔符把列拆分成列,首先我们先来看个案例,在提取数据的时候,销售将日期和销售金额混在个数列,不利于后续分析数据,我们就可以采取按分隔符...
  • 如何将批量将个...我需要GPS观测数据进行编辑,观测数据报告返回为邮件形式,导出后以eml格式存放在A文件夹中,邮件非常,我想要批量将eml文件中关键词后所需数据提取excel进行编辑, 求大佬施以援手 ...
  • 本文主要讲解怎样使用Python对多excel文件任意区域内容进行批量提取。在看这篇文章之前,默认你已经具备了python的基础知识。如果没有任何基础,建议先看看上篇文章:【学习心得】我的Python入门之旅。话不多说...
  • 点击图片抢购Excel等视频课程编按:跨表提取数据很多...它不但操作简易,轻易解决“一对多”,而且它生成的结果表可以与数据源形成动态链接,数据源变化了,结果也会动态更新!今天给大家分享一个很少人用但有奇效的...
  • EXCEL函数公式大全之利用VLOOKUP、函数INDIRECT、自定义名称从个表格中提取数据。EXCEL函数与公式在工作中使用非常的频繁,会不会使用公式直接决定了我们的工作效率,今天我们来学习一下提高我们工作效率的函数...
  • 本文来自Pconline很小伙伴都身份证提取出生日期不感陌生,一般情况下我们会选用MID函数完成此类操作。但如果只是临时使用,MID函数不免显得有些麻烦。其实提取出生日期并不只有函数操作这条路,借助组神级...
  • 小伙伴们好啊,日常工作中,很小伙伴都会遇到一些不规范的数据,很小伙伴字符串的提取问题也是头疼不已,今天咱们就分享期关于字符串提取的内容。提取字符串中的英文先来看下面的数据,是一些混到一起的...
  • 如图中案例,现在有个学生成绩数据,其中有很个班级混在一起,现在如何自动提取个班级的第名学生信息呢?首先,我们来分析一下工作思路:这里第名的条件是“总分第”,因此我们第步要找出每个班级...
  • EXCEL批量数据提取软件是款好用的数据提取软件。...软件可文件夹中的个exce1文件进行批量数据提取!免费版次可扫描3个文件,提取20行数据。 注册版无数量限制!单机版(绑定电脑)68元,U盘版(绑定自备U盘
  • 该自定义函数可使用个正则表达式目标单元格进行次匹配尝试,如匹配成功,将停止尝试匹配其他正则表达式,并且使用该正则表达式相对应的替换表达式进行替换,返回替换结果。 您可以直接下载包含该函数代码的...
  • iamlaosong文 ...1、提取的方法是用SQL语句,首先建立这张表的查询,方法是数据选项卡—现有连接—浏览更 或者 按快捷键Alt+D+D+D,然后选择要查询的Excel文件和文件中的的工作表,然后点击“属性
  • 一对多查询模式化数组公式: =INDEX(区域,SMALL(IF(条件,行号数组,4^8),ROW(A1))) 三键齐按(ctrl+shift+回车) 在具有多个符合条件的情况下,提取和匹配方法~ 我们需要解决三种类型的问题: 1,李商隐第一次...
  • 说明:思路用的很新奇,也COUNTIF有了更深一步的了解,但是,对于百行数据运算速度特别低,不适合数据的使用 当面对堆数据,我们要提取一列的唯一值的时候,如果单纯用人为个个判断,显然是不科学的,...
  • 因工作原因,很久没有学习python知识了,感觉都快忘记了,前天看到个练习题,如何将字符串中的数字提取出来,然后求和呢?下面我来解释一下如何通过python代码来实现。题目:字符串43…3y2.f67se2.666. 将其中的...
  • excel快速合并,次搞定

    热门讨论 2010-09-27 11:06:10
    可以提取一Excel文件的个工作表,也可以提取多Excel文件的个工作表的数据。例如,上级部门制定了统一的Excel表格,下发到下级部门填报,上级部门收集到下级部门填报的数据后,需要某些数据项进行汇总分析...
  • 除了直接输入外,从已存在的单元格内容中提取特定字符输入,绝对是种省时又省事的方法,特别是一些样式雷同的信息更是如此,比如员工、籍贯等信息。下图中是将人员姓名提取出来图中很明显,省份、姓名、手机号码...
  • 如下图所示,将下面的...主要思路:先将字符串的文本以“元”为分隔符拆分出来,然后再构建个list,即{1..15},或者更大的位数,去截取拆分的每个list的值,这些值转换成数字,无法转化的替换成null值,取出...
  • 因为直接对excel进行操作,速度比较慢,因此,先用“保存点”命令将提取到的点坐标保存到临时txt文件中,再利用“数据转换”命令将数据转换为excel格式。 “清楚数据”命令用于清楚临时数据文件中的数据。 从...
  • 今天整理更文本函数在实际工作中的应用,比如EXCEL单元格里提取指定符号里的内容,分离单元格里的英文字母和汉字,提取单元格里的数字等等。有些源数据比较规律,有些则比较杂乱,我们看看都有哪些方法可以数据...
  • 有很朋友问我,如何快速的从身份证号提取年龄,今天我们来分享两种方法,大家可以根据自己的函数的了解程度,使用适合的方法。 1.粗略提取,就是以年份为准的的提取。而忽略月和日。 函数解读:=MID(B2,7,4) =...
  • 1、按分隔符拆分按分隔符拆分是分列的常规用法,是根据分隔符把列拆分成列,首先我们先来看个案例,在提取数据的时候,销售将日期和销售金额混在个数列,不利于后续分析数据,我们就可以采取按分隔符...
  • Excel公式中,有些函数是独行侠,而有些函数经常是成出现的,今天就带大家一起盘点Excel表格中八大经典函数组合。IF + And作用:并列条件判断 【例】如下图所示,在C列设置公式,如果A列值小于500且B列值为未...
  • 在平日的工作生活中,使用Excel来处理办公数据文档,已经成为很小伙伴们的工作日程。而Excel表格中会存储大量的工作数据,而我们还经常会遇到处理大量数据的时候,表格当中会出现一些重复的内容,而此时我们需要的...

空空如也

空空如也

1 2 3 4 5 ... 17
收藏数 340
精华内容 136
关键字:

一对多提取excel