EXCEL完成多条件多结果查找&多行多列变一列
来源:互联网 发布:page 知页还是面 编辑:程序博客网 时间:2024/06/06 08:36
其实我本来对EXCEL是一窍不通的,直到我舅舅给了一串大概1500多行的药品供应目录让帮他我查找。当他告诉他们平时都是花1~2天人工填写查找的时候,我当时就惊呆了,心想EXCEL设计者肯定已经哭晕在厕所,大家竟然都不用便捷的公式来完成,于是乎我就开始上手了。
首先应该明确需求,拥有一个包含药品名称、规格、供应商、价格的数据库A,表A如下。从表A中我们可以发现,同一药品,同一规格具有多个供应商,所以这一定是一个多结果查找。
以上我们明确了本次任务是完成多条件多结果查找,首先应该建立一新的工作环境(Work Space),简单的说就是新建一个EXCEL文件,并把两张表导入Sheet1、Sheet2分页中去,这样方便引用,是个良好的习惯。导入方法如下,右击左下角的Sheet1分页,选择移动或复制
查找
为了解决问题,我们先从简单的方面入手,多条件多结果查找首先需要完成查找的基本功能,查找所用的函数一般是VLOOKUP,意思是在一列(vertical)中找到(Look up)符合条件的第一个值并返回,语法规格为
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
譬如=VLOOKUP($O4,Sheet3!$H:$I,2,0),其中$O4表示查找内容是O4单元框的内容,查找范围是Sheet3!$H:$I,即表二的H到I列,2表示查找到相应行之后,返回O列右边第二列的内容,0是查找类型。符号$表示固定,因为随着公式下拉,数字4会依次增加,变为O4,O5,O5,而随着右拉列号会依次增加,变为O4,P4等,符号$的左右就保证右拉时列号O不会改变。
多条件查找
当查找结果为多个时,我们首先的想法可能是按照C语言,使用&符号,当然,可是肯定行不通的。正统方法是利用数组,这种方法先不理它...
另一种方法更直接一些,建立一个辅助列,将多条件化为单条件,设需要满足的条件是E3、F3,我们只需要建立辅助列,令他的值为=E3&F3,满足该辅助列的单一条件就能实现满足多条件的需求。如下图所示例如H列,令H2=A2&C2,建立辅助列,对H列查找就能满足多条件查找的需求。
多结果查找
但是适用VLOOKUP函数只能返回第一个结果,不符合多结果查找的要求。为了解决多结果查找,我们需要适用INDEX SMALL IF 组合函数。通过IF条件语句,遍历找到所有符合条件的行,并返回该行的序号
=IF(Sheet2!$E2:$E1335=Sheet1!$F3,ROW(Sheet2!$E2:$E1335),4^8)
其中Sheet2:$E2:$E1335=Sheet1!$F3是判断条件,在表二的E列中,找到等于表一中F3单元格内容的行,返回行号ROW(Sheet2!$E2:$E1335),否则返回4^8=65535数值。找到所有符合条件的结果后,将他们排序,并按照行输出(右拉)
=INDEX(Sheet2!$G:$G,SMALL(IF(Sheet2!$E2:$E1335=Sheet1!$F3,ROW(Sheet2!$E2:$E1335),4^8),COLUMN(A1)))&""
其中SMALL仅仅对所有结果从小到大排序,而INDEX则将结果一一输出,按列输出COLUMN,按行输出则是ROW。先做个小实验,如下图。在A列中查找C1=1的值,D1输入公式=INDEX($B:$B,SMALL(IF($A$1:$A$1000=$C1,ROW($1:$1000),4^8),COLUMN(A1)))&"",输入后按下CRTL+ENTER(使结果为数组),再右拉可以得到结果。
将这种方法运用到药品企业价格查找的实例中去,如下图所示,这里只右拉了3格,最多显示了3家企业,O列为条件查找辅助列,P~R为相应的企业,S~V为相应的价格。至此我们完成了多条件和多结果的查找。收尾工作需要把辅助列删除,并且把得到的结果赋值-选择性粘贴-数值,以防移动后出现没有reference的情况。
多行多列变一列
开开心心地把这个表格交给我舅舅,准备听几句夸奖的话,结果他说不行啊,这不同企业和价格必须在一列显示啊,这样才方便比对价格作出选择。于是我就愁了,要怎么才能把这种多行多列的数据变为一列呢?解决办法当然是有的。使用OFFSET函数
=OFFSET(($U$3),INT((ROW(A1)-1)/4),MOD(ROW(A1)-1,4))
其中4代表了多行多列中的列个数,我最后每种药品最多列出4家供应商,对于使用者请按自己情况改变该数字,$U$3是多行多列的最左上角数值。运用到我们得实例中去,其中I~L是多行多列矩阵,在F3中输入公式并下拉,得到一列。
每行增加行
因为一种药品对应了4家供应商,所以每种药品必须占4行,才能和上诉的一列正确对应上,这时我们需要使原有的每行间隔三行。这种苦力活当然不可能一个个增加,必须是批量操作的。
这种方法可以按照下图的辅助列排序方法,最后一步用的是快捷键排序,没显示出来,这种方法适合每行空多行。
或者是
我用的方法是如下建立辅助列,定位空值后插入行,当然还是前两种比较好。
最后应用到实例中,得到结果
把之前多行多列变一列得到的4个为一组的单列复制到后面一行,就能得到最终的结果
删除空白行
许多没有供给公司和药品序号的行就是完全的空白行,这些影响观看,需要删除,建立辅助列,判断A和F同时为空时,赋值#N/A,CRTL+F替换#N/A为空值,F5定位空值,删除空值,可以达到要求,最终结果如下
0 0
- EXCEL完成多条件多结果查找&多行多列变一列
- Excel查找函数--Vlookup(多条件匹配)
- case when 多条件, 只更新一列
- EXCEL中多条件查找的15种方法探讨
- 怎样把excel一列分成多列
- 通过IF({1,0}和VLOOKUP函数实现Excel的双条件多条件查找
- 使用IF({1,0}和VLOOKUP实现Excel双条件和多条件查找
- 通过IF({1,0}和VLOOKUP函数实现Excel的双条件多条件查找
- Excel多条件统计
- oracle两表连接多个结果集合成一列
- Excel高亮查找结果
- Excel 统计一列中相同多行的平均值
- EXCEL 中利用 INDEX 和match 来实现多条件查找
- 如何在Excel的一列中查找重复的数据
- excel 查找一列在另一列的位置
- 如何通过excel公式查找一列数据中的相同项目?
- oracle查询结果中wmsys.wm_concat组合一列多行结果,以及每行中某些列的组合结果
- Excel VBA 多条件查询
- 走过的2014
- hdu 3038 How Many Answers Are Wrong 带权并查集
- 每行代码值多少钱
- POJ 3026 Borg Maze (BFS+MST)
- OC中数组套数组、字典套字典、数组套字典、字典套数组的代码示例
- EXCEL完成多条件多结果查找&多行多列变一列
- Struts2-图片验证码
- 阿J的cocos2d-x学习笔记-元素消消看(三)-菜单的创建
- POJ 1679 The Unique MST
- 蓝的成长记——追逐DBA(13):协调硬件厂商,六个故事:所见所感的“服务器、存储、交换机......”
- 欢迎使用CSDN-markdown编辑器
- java根据jar包反编译后修改再打包回jar的做法
- QlikView处理数据
- javaweb监听器