-
2020-11-26 01:00:59
我们在使用Excel时,Excel查找数据不仅是数据管理与分析中最频繁的操作。也是很复杂的操作。由于工作表数据的复杂性和多样性。要进行Excel数据查找,需要根据具体情况采用不同的查找函数。设计不同的查找公式。而且在很多情况下。在数据查询中。很少只使用一个函数。而是多个函数联合使用。
Excel提供了十几个查找和引用函数。这些函数都有非常大的用途。可以根据实际问题选择相应的函数。在这些函数中。最常用的是VLOOKUP函数、INDEX函数、MATCH函数和OFFSET函数。关于0FFSET函数。将在以后Excel教程中详细介绍。这里主要介绍VLOOKTUP、INDEX、MATCH函数的使用方法和实际案例。
VLOOKUP函数用于在表格或数值救组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。其语法如下:
=VLOOKUP(Lookup_value,table_array,col_index_num,
[range_lookup])
=VLOOKUP(查询值,数据表,第几列,[是否精确查询])
在使用这个函数时。需要注意以下几点:
①如果range_lookup为TRUE.则table_array的第一列数值必须按升序排列。否则。函数VLOOKUP将不能给出正确的数值。如果range_lookup为FALSE.则table_array不必进行排序。
②函数VLOOKUP查询不区分大小写。
③如果参数range_Lookup为TRUE或省略。则函数VLOOKUP进行近似查询,也就是说,如果找不到精确匹配值,则返回小于lookup_value的最大数值。如果range_value为FALSE.函数VLOOKUP将查找精确匹配值。如果找不到。则返回错误值#N/A!。
图1所示是一个销售记录表。现要求设计一个查询系统。查找指定月份的各个产品的销售数据。即在单元格B1中输入月份名称。将在第3行显示该月各产品的数据。
在单元格A3输入下面的公式。并向右复制。即可得到需要的结果。
=VLOOKUP(SB$1,$SA$6:$F$17,COLUMN()。O)
这里使用了COLUMN函数来获取动态的序列号数字。
MATCH函数用于在指定方式下查找与指定数值匹配的数组中元素的相应位置。其语法如下
=MATCH(L00kup_Value,lookup_array,match_type)
=MATCH(查找值,数据范围,对比方式)
需要注意的是。一般情况下都是精确查找,因此需要把参数matche_type设置为0。
INDEX函数用于涉及两个变量的查询,也就是取出列表或数组中的指定由行序号和列序号交叉处的值。其语法如下:
=INDEX(array,row_num,column_num)
=INDEX(列表或数组。行序号,列序号)
一般情况下,往往需要联合使用MATCH函数和INDEX函数进行查询。也就是先用MATCH函数确定数据所在的行和列。然后利用INDEX函数将该行和列交叉处的教据取出。
我们也可以使用MATCH函数和INDEX函数进行查询。此时,单元格A3中的公式为:
=INDEX(A6:A17,MATCH($B$1,$A$6:$A$17.0))
今天我们学习了在Excel中比较常用的:Excel查找数据的方法,Excel查找数据将来在进行数据分析和数据处理有着非常大的作用。http://cda.pinggu.org/view/19181.html
更多相关内容 -
如何在Excel中计算一组数字的百分位数和四分位数?
2021-08-02 08:41:02如何在Excel中计算一组数字的百分位数和四分位数?在我们的日常学习或工作中,您可能需要根据一些标准计算特定的数字。 例如,对于一组收入,找到30%输入的收入的数字要比显示的屏幕截图要少的工作是什么。 在这种...如何在Excel中计算一组数字的百分位数和四分位数?
在我们的日常学习或工作中,您可能需要根据一些标准计算特定的数字。 例如,对于一组收入,找到30%输入的收入的数字要比显示的屏幕截图要少的工作是什么。 在这种情况下,我们需要在Excel中应用PERCENTILE和QUARTILE公式。
Office选项卡在Office中启用选项卡式编辑和浏览,并使您的工作更加轻松...
Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%
重用任何东西: 将最常用或最复杂的公式,图表等添加到您的收藏夹中,并在将来快速重用它们。
超过20种文字功能: 从文本字符串中提取数字; 提取或删除部分文字; 将数字和货币转换为英文单词。
合并工具:将多个工作簿和工作表合二为一; 合并多个单元格/行/列,而不会丢失数据; 合并重复的行和总和。
分割工具:根据价值将数据分割成多个工作表; 一本工作簿可转换为多个Excel,PDF或CSV文件; 一列到多列。
跳过粘贴 隐藏/过滤的行; 计数与求和 按背景色; 向多个收件人批量发送个性化电子邮件。
超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按星期,日期,频率等 滤波器 用粗体,公式,注释...
超过300种强大的功能;适用于Office 2007-2019和365; 支持所有语言; 在您的企业或组织中轻松部署。
百分位数功能
您可以执行以下操作以应用百分号功能。
选择一个空白单元格,输入此公式 = PERCENTILE(A1:A10,0.3), 按 输入 键,现在您可以看到输入的收入的30%低于3700。请参见屏幕截图:
请注意:
1.在公式中,A1:A10是您要使用的收入集,0.3表示找到收入的30%低于的数字,您可以根据需要进行更改。
2. PERCENTILE函数的第二个参数必须是0到1之间的十进制数。
四分位数功能
使用QUARTILE功能,您还可以找到列表中收入最高的25%大于的数字。
选择一个空白单元格,输入此公式 =四位数(A1:A10,3), 按 输入 键,现在您可以发现输入的收入的75%低于6750,这也意味着输入的收入的25%高于6750。请参见截屏:
备注:
1.在上面的公式中,A1:A10是您使用的收入集,3表示找到收入的75%低于的数字。 您可以根据需要更改它们。
2. QUARTILE函数的第二个自变量必须是0到4之间的整数。
小费。如果您想按日期快速计算年龄,请尝试使用Kutools for Excel的 根据生日计算年龄 如以下屏幕截图所示。 它在60天内无限制地提供完整功能, 请下载并立即免费试用。
最佳办公效率工具
Kutools for Excel解决了您的大多数问题,并使您的生产率提高了80%
重用: 快速插入 复杂的公式,图表 以及您以前使用过的任何东西; 加密单元 带密码 创建邮件列表 并发送电子邮件...
超级公式栏 (轻松编辑多行文本和公式); 阅读版式 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
合并单元格/行/列 不会丢失数据; 拆分单元格内容; 合并重复的行/列...防止细胞重复; 比较范围...
选择重复或唯一 行; 选择空白行 (所有单元格都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择...
确切的副本 多个单元格,无需更改公式参考; 自动创建参考 到多张纸; 插入项目符号,复选框等...
提取文字,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
超级滤镜 (将过滤方案保存并应用于其他工作表); 高级排序 按月/周/日,频率及更多; 特殊过滤器 用粗体,斜体...
结合工作簿和工作表; 根据关键列合并表; 将数据分割成多个工作表; 批量转换xls,xlsx和PDF...
超过300种强大功能。 支持Office / Excel 2007-2019和365。支持所有语言。 在您的企业或组织中轻松部署。 完整功能30天免费试用。 60天退款保证。
Office选项卡为Office带来了选项卡式界面,使您的工作更加轻松
在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
每天将您的工作效率提高50%,并减少数百次鼠标单击!
-
自动将EXCEL中一列数据一个一个的在指定的网页中搜索并获取相应的数据,最后自动生成一个新的Excel文档。...
2020-10-29 16:14:18自动将Excel中一列数据一个一个的在指定的网页中搜索并获取相应的数据,最后自动生成一个新的Excel文档。(可看到进度条以及完成采集的预估时间)此文件为已转化的exe压缩包,若需要源代码或者更改网页之类的定制,... -
Excel如何在一串数字中找出几个之和等于某个特定值
2021-01-15 05:02:53展开全部典型的规划求解问题,但对截图中的数据有个疑问,就是单价中有两个542.1,一个542。对于这3行的e69da5e887aa3231313335323631343130323136353331333431346333数量取值,在期望的误差之内会是按同样的单价来...展开全部
典型的规划求解问题,但对截图中的数据有个疑问,就是单价中有两个542.1,一个542。对于这3行的e69da5e887aa3231313335323631343130323136353331333431346333数量取值,在期望的误差之内会是按同样的单价来处理的,不知单价是随意输入的,还是实际的数据。本回答中按截图数据来处理,但这3行数据分成3行的实际意义不大。
规划求和时先建立数据模型,以截图数据为例,D列(截图无行号和列标——百度知道提问中的通病!)总额为数量(B列)*单价(C列),如:
D2=B2*C2
下拉,然后对总额求和:
D10=SUM(D2:D9)
D11中输入目标数据:8099.8
D12中输入求和总额与目标值的误差绝对值:
D13=ABS(D10-D11)
数据模型建立后,就可以开始用规划求解了,数据选项卡——分析——规划求解,调出规划求解对话框。在对话框中,目标单元格:D12;目标值选择最小(能否达到<5,则与你的数据源有关,如果能有解,误差会很小,如果没有满意解,设置为小于5也没用。可变单元格:B2:B9(数量)。然后添加2个约束:
B2:B9=整数 ——数量必须是整数,添加约束为整数时,选择int就是整数
B2:B9<=A2:A9 ——数量不能超过实际数量吧(提问中并没有提及)
B2:B9>=1 ——要求数量不能为0
然后选择求解方法,一共有3个方法,3种方法具体有什么不同,Excel中也没有提供具体的帮助说明,一般来说,只要问题有解,总有一种方法能得到比较满意的解。为了防止无解的问题或数据模型建立缺乏合理性、约束条件过多或缺乏条理,造成求解列循环或耗时太长,可以在“选项”设置最大求解时间或/和最大循环次数。
以上都设置好后,就点“求解”,根据问题的难易程度、数据的复杂程度,Excel经过数分钟到数十分钟后,只要问题有解,就会给出一个组解。得到的解不满意或得不到求解结果时,可修改设置(如约束条件、改变求解方法等)后,再进行求解。
本解结果误差为0.4,已远小于题主误差小于5的要求。
-
从数百个Excel中查找数据,一分钟用Python搞定
2020-08-17 19:15:07作者 |陈熹来源 | 早起Python今天分享一个真实的办公自动化需求,大家一定要仔细阅读需求说明,在理解需求之后即可体会Python的强大。需求说明首先我们来看下今天的需求,有一份档...作者 | 陈熹
来源 | 早起Python
今天分享一个真实的办公自动化需求,大家一定要仔细阅读需求说明,在理解需求之后即可体会Python的强大。
需求说明
首先我们来看下今天的需求,有一份档案记录总表的Excel工作簿, 每天会根据当天日期建立新表,每天的表格内包含所有档案信息,同时也有可能会添加新的档案名。同个年度的总表在年末可能会有两、三百个工作表,同时每个表中可能也存在千余份档案信息。表格形式如下(为了直观呈现本例以7个工作表和十余份档案的形式呈现)
需要完成的操作:为了方便审查特定档案信息,需要给出档案名后生成一份新表,该表包含指定档案在所有日期(即所有工作表)中的记录。最终结果如下(以档案x003为例):
也就是老板说:给我把这几百个表格中所有包含档案x003的相关数据全部找到并整理个新的表格给我。
步骤分析
正式写代码前可以把需求分析清楚,将复杂问题简单化。
说白了,这个需求要求把所有日期工作表中的特定行都提取出来整合成一个新表。那么我们可以遍历每一张表,然后遍历第一列(名称列,也可以看作A列)每一个有数据的单元格,如果单元格中的文字为我们需要的档案名,就把这一行提取出来放到新的表格中,进一步梳理步骤为
建立一个新的EXCEL工作簿
新表的表头和档案记录Excel中的一样,也是
名称
、配置
、提交日期
等遍历档案记录Excel的每一张工作表sheet,再遍历第一列每一个有数据的单元格,对内容进行判断
找到符合条件的单元格后获取行号,根据行号将当前表中的特定行提取出来,并将行追加新创建的表中
分析清楚就可以着手写代码了。
Python实现
首先导入需要的库本例中涉及旧表的打开和新表的创建,因此需要从
openpyxl
导入load_workbook
和Workbook
(如果是ppt和word用到的模块就更智能了,一个方法就能搞定)from openpyxl import load_workbook, Workbook
接着导入旧表及创建新表
# 从桌面上获取总表 filepath = r'C:\Users\chenx\Desktop\台账.xlsm' # 根据实际情况进行修改 workbook = load_workbook(filepath) # 创建新的Excel工作簿获取到工作表 new_workbook = Workbook() new_sheet = new_workbook.active # 给新表写入表头 new_headers = ['名称', '配置', '提交日期', '受限操作', '操作时间', '状态', '存储位置'] new_sheet.append(new_headers)
现在是核心步骤:多次遍历,可以用
workbook.sheetnames
获取工作簿所有工作表名称的列表,然后遍历即可。for i in workbook.sheetnames: sheet = workbook[i] # 获取档案名称所在列 names = sheet['A']
按照前面的分析,需要遍历名称列,判断每一个单元格的值是不是需要的档案名。这里应注意,如果已经循环到需要的单元格,就可以停止循环了,但一定要把符合单元格的行号传递给一个变量做记录,不然一旦break出循环就没有记忆了。
flag = 0 for cell in names: if cell.value == keyword: # 这里的keyword就是档案名,可以以 档案x003 为例 flag = cell.row break
获得到符合条件的行号后用
sheet[flag]
就可以拿到符合行了。openpyxl
不支持旧表的一整行写入新表,因此应对策略就是将这一行的所有单元格具体值组装成一个列表,用sheet.append
(列表)的方法写入新表,遍历部分的完整代码如下:for i in workbook.sheetnames: sheet = workbook[i] names = sheet['A'] flag = 0 for cell in names: if cell.value == keyword: flag = cell.row break if flag: # 如果flag没有被修改则不需要顺序进行下列代码 data_lst = [] for cell in sheet[flag]: # 这里加上一个对内容的判断,是让无内容的行直接放空,而不是写入一个 none if cell.value: data_lst.append(str(cell.value)) else: data_lst.append(' ') new_sheet.append(data_lst)
最后记得保存
new_workbook.save(r'C:\Users\chenx\Desktop\台账查询.xlsx')
小结
这是经过一定改编的真实案例,可见Python自动化办公确实能够帮助我们解放自己的双手,不过在写自动化脚本之前也要先拆分任务,明确思路再进行,如果对本文的代码和数据感兴趣可以在后台回复自动化获取。最后还是希望大家能够理解Python办公自动化的一个核心就是批量操作-解放双手,让复杂的工作自动化。
推荐阅读
-
excle 文档搜索目录《如何在多个EXCEL文件中搜索某一内容?》
2021-07-30 08:10:42我有100个excel文件,在一个目录下,如何在另外的excel文件中查找数据 举思路:先把另外99个workbook合并到一个book中,然后用vlookup查找即可。用excel在文件夹中查找符合条件的文件,并提取文件路径建立超级链接。... -
Excel如何在一列数字中找出几个之和等于某个特定值
2020-12-19 20:34:07展开全部典型的规划求解32313133353236313431303231363533e59b9ee7ad9431333431346333问题,但对截图中的数据有个疑问,就是单价中有两个542.1,一个542。对于这3行的数量取值,在期望的误差之内会是按同样的单价来... -
excel 查找一个表的数据在另一个表中是否存在
2019-05-24 17:23:00Sub test() Rem MsgBox ("AAAAA") Dim pos, pos1, tmp, total1, total2, tmpStr, tmpStr1, col, sheetR sheetR = 2 Rem 获取Sheet1的行数 ...Rem 获取Sheet1的列数,后面把不满足的条目复制到She... -
从几百个Excel中查找数据,用Python一分钟搞定
2020-12-28 20:04:12一、需求说明首先我们来看下今天的需求,有一份档案记录总表的Excel工作簿, 每天会根据当天日期建立新表,每天的表格内包含所有档案信息,同时也有可能会添加新的档案名。同个年度的总表在年末可能会有两、三百个... -
excel找到一列数中最大数的位置
2021-01-22 12:30:23=MATCH(MAX(N4511:N9019),N4511:N9019,0) -
Excel公式:查找一单元格中某字符最后出现位置
2019-08-19 12:23:10这个需求简单来说,就是从右向左查找字符出现的首个位置,或从左向右查找字符出现的最后位置,例如:查找“中华人民共和国人民代表大会发言人任免人选”中,最后一个“人”字的位置,直接的查找是做不到的。... -
Excel中实现在字符串中查找多个字符串
2021-06-24 03:40:58在字符串里面做查找很简单,用find函数即可,但是如何要同时查找多个字符串呢?看下面的例子:在以上的名称中,我们要判断是不是包含 上海 昆山 浙江 这三个关键字,如果包含则注明,如果不包含则注明 无。我们用的... -
『excel表如何从一列数据中筛选出合计等于某数的某些数?』
2020-12-29 06:33:50如何设置Excel工作表等于另一个工作表中的数值简个例子,如图:让图sheet3中B列等于sheet1中E列总分。1、在sheet3 B2单元格输入“=sheet1!e2”2、回车后就能显示sheet1中e2单元格内容。3、把b2下拖就得到整列的总分... -
excel 外部链接 乱码_在Excel文件中查找外部链接
2020-08-08 01:59:58excel 外部链接 乱码 在Excel文件中查找外部链接 (Find External Links in an Excel File) Last week, I opened an Excel file, and was surprised to see a Security Warning. Apparently there were external ... -
一招教你在Excel中将一个数字拆分为多个数字之和
2021-07-30 02:12:32一招教你在Excel中将一个数字拆分为多个数字之和腾讯视频/爱奇艺/优酷/外卖 充值4折起相信很多小伙伴在日常办公中都会用到Excel,在其中如何才能将一个数字拆分为多个数字之和呢?方法很简单,下面小编就来为大家... -
怎么在excel中利用vlookup函数进行信息查找
2021-06-30 05:35:22怎么在excel中利用vlookup函数进行信息查找随着科技的发展,电脑已经越来越完善,电脑中的软件也成为了人们解决日常问题的主要工具。当我们在使用excel处理数据时,该如何使用vlookup函数查找信息呢?接下来就由小编... -
如何在EXCEL中查找多个单元格中的多个关键字,多个单元格满足多个条件的行的绿色在线工具
2020-07-15 15:05:39Excel查找,偶尔有情况遇到要在Excel中查找多个单元格的多个关键字(或包含或相等),或多个单元格或列满足多个的条件的行,Excel本身的支撑没有那么灵活。本身我们又对代码不熟悉,有一个方便性的工具可以做这个... -
excel多重索引查找
2021-06-13 13:19:14excel里如何自动索引/查找某项目费用某个月的合计数使用LOOKUP(A,B,C)函数。A的位置填“8月”,B的位置是查找A的范围(简单地说,就是在什么范围查找“8月”这个词,即图中的J列),C的位置是结果所在的范围,和B类似... -
excel查找功能_如何使用VBA代码实现Excel的查找和替换功能?
2020-10-22 05:50:28每天一篇Excel技术图文微信公众号:Excel星球NO.145-I ...虽然没有自古以来,但依然众所周知的是,在Excel中有一个很常用的功能:查找与替换。而在VBA编程中也有与之对应的两个方法:Find和Replace。我们先来聊Fi... -
四种方法教您如何在Excel中快速查找重复数据
2021-07-25 04:48:23核心提示: 在许多情况下,我们需要删除Excel表中的某些相同数据. 但是,仅通过眼睛来发现它们就很难区分两列细胞,这非常麻烦. 以下是4种快速帮助您查找重复数据的方法.在许多情况下,我们需要删除Excel表中的某些... -
Excel数据分析系列(4):Excel中的公式、函数及数组
2021-01-20 11:59:02Excel中的数组是指一行、一列或多行多列的一组数据元素的集合,在Excel中用{}进行创建,其中同一行的元素用逗号“,”隔开,不同行的元素用分号“;”隔开。 如下图展示的是一个3行5列的数组: 1 2 3 4 5 6 ... -
Excel之定位和查找(一)
2020-12-21 11:51:56在数据量比较少的情况下,我们要到达Excel中某一位置时,通常会用鼠标拖动滚动条到达需要的位置,查找某已知固定的值,用Ctr+F,在查找内容中输入对应的值即可一个个的查找到其对应的位置。但当数据量较多时,... -
Excel计算一组数据的方差的操作方法
2021-06-22 22:26:39Excel计算一组数据的方差的操作方法1、在Excel里对于计算标准差计算函数,我们只需要如何进行运用这个函数进行操作就行2、笔者这边以计算一些成绩数据的方差作为演示3、首先我们一共有42个数据样本,我们点击需要... -
excel数组和查找and引用函数
2021-05-18 14:07:13数的组合 是有序的元素序列, 组成数组的个个变量称为数组的元素, 可以理解为有着行.列标识并有着尺寸特征的集合,一个单元格的数据就可以是一个数组,即单元素数组;单行数据或者单列数据,是一维数组, 多行多列数据是... -
老板让我从几百个Excel中查找数据,我用Python一分钟搞定!
2020-11-30 07:48:31一、需求说明首先我们来看下今天的需求,有一份档案记录总表的Excel工作簿, 每天会根据当天日期建立新表,每天的表格内包含所有档案信息,同时也有可能会添加新的档案名。同个年度的总表在年末可能会有两、三百个... -
Excel快速查找数据中缺少的数据
2020-07-08 14:45:39案例:有一组数据排序1到35,其中被删掉了5个数据,如何快速把这5个丢失数据造出来? 答:用VLOOKUP函数可以快速查找出来 A列是原始不齐数据 B列是参照判断 C列是正确顺序数据 D列是判断结果 在D1中输入=VLOOKUP... -
Excel中判断某一列的数据在另一列中是否存在
2020-08-12 17:04:53Excel中判断一个表中的某一列的数据在另一列中是否存在 判断c列的值在A列中是否存在(假定C列为需要判断列,A列为目标列) 在D1中输入以下公式,然后下拉公式即可 =IF(COUNTIF(A:A,C1)>0,"有","无") =IF... -
在excel中如何筛选重复数据_EXCEL里如何快速找出重复的数据?
2020-11-18 11:16:16【问题】有网友私信于我“请问一组6个数字。一共450组。EXCEL能不能自动识别出重复3个数字以上组数出来呢?比如第一组1-2-3-4-5-6。第二组7-8-9-10-11-12。第三组1-3-6-15-17-18。能不能自动识别1组合三组的1-3-6是... -
如何在Excel中对指定一列中的数据进行累加排序?
2021-08-01 05:15:20腾讯视频/爱奇艺/优酷/外卖 充值4折起我们在使用Excel表格的时候有的时候需要对一对数据进行处理,比如需要对一列数据的指定内容进行排序,那么我们可以怎么样做呢?来一起去看看吧!具体如下:1. 第一步,我们需要...