之前有位学员来上课的时候，小编听到她在抱怨说看了半天index函数和match函数，但是不知道该怎么样去使用它们？确实这两个函数看着挺简单，分开使用也还好，但是想要发挥最大的效果就必须要交叉使用才可以。

精确定位：MATCH函数

MATCH函数(lookup-value,lookup-array,match-type)，返回查找内容所在的位置。

lookup-value:表示需要查找的值，数组或单元格引用都可以；

lookup-array:表示包含所要查找数值的区域，数组或数组引用；

match-type:表示用于精确查找或模糊查找。取值为-1、1、0 。其中0为精确查找。

检索查询和交叉查询：index函数

INDEX函数(array,row-num,column-num)，返回制定位置中的内容。

array:要查找值的单元格区域或数组；

row-num:返回值所在的行号；

column-num:返回值所在的列号（行号和列号有一个即可）。

实训习题：

左边是公司所有人员的信息统计表，右边是我们需要查找的信息。我们想用名字去查找这个人所在的部门。

我们需要将名字所在的位置使用match定位出来，再和index函数配合使用。选择姓名所在位置，再选择姓名所在单元格范围，使用精确定位即可。

在使用了match后，在使用index函数会出现这样的对话框，选择第一个点击确定即可。配合index函数，选择部门所在单元格范围即可完成操作。

当然在使用函数的过程中，小编也犯了一个错误，就是没有使用绝对应用，到时候出现的结果会不正确，所以我们需要在要改变的数据前面加上$锁死数据。

计数，尤其是条件计数，在Excel中应用的也是非常的广泛，常用的函数有：COUNTIF、COUNTAIF等，今天我们来学习的事COUNTIF的模糊查询实用技巧。

一、COUNTIF函数功能及语法结构。

作用：统计符合指定条件的个数。

语法：=COUNTIF(条件范围，条件)。

目的：统计“男、女”销售员的人数。

方法：

在目标单元格中输入公式：=COUNTIF(D3:D9,H3)

二、模糊查询示例。

1、包含模糊查询。

目的：查询姓名中包含“小”的人数。

方法：

在目标单元格中输入公式：=COUNTIF(B3:B9,"*小*")。

解读：

公式中"*小*"就为查询条件，意思就是说姓名中只要包含有“小”这个字就在统计范围之内。星号（*）在Excel中为通配符。可以匹配任意长度。

2、以指定字符开头的模糊查询。

目的：查询以“小”开头的人数。

方法：

在目标单元格中输入公式：=COUNTIF(B3:B9,"小*")。

解读：

公式中"小*"为查询条件，“小”字的前面并没有星号（*），而后面有星号（*），所以查询的结果返回以“小”开头的结果。

3、以指定字符结尾的模糊查询。

目的：查询以“小”结尾的人数。

方法：

在目标单元格中输入公式：=COUNTIF(B3:B9,"*小")。

解读：

公式中"*小"为查询条件，“小”字的前面有星号（*），而后面没有星号（*），所以查询结果返回以“小”结尾的结果。

4、指定字符长度的查询。

目的：返回姓名为2个字的人数。

方法：

在目标单元格中输入公式：=COUNTIF(B3:B9,"??")。

解读：公式中"??"为查询条件，问号（？）在Excel中也为通配符，但是和星号（*）不同的是的单个星号（*）可以匹配多个字符，而单个问号（？）只能匹配一个字符。所以"??"就限定了字符的长度为2。

5、非空统计。

目的：查询实有人数。

方法：

在目标单元格中输入公式：=COUNTIF(B3:B9,"*")。

解读：

公式中"*"为查询条件，意思就是说至少包含一个字符，不能为空。

结束语：

COUNTIF函数是条件统计中非常实用的一个函数，也可以应用于模糊查询，其模糊查询功能主要体现在第二个参数和通配符（？、*）的配合使用上。通配符问号（？）只匹配一个字符，而星号（*）匹配任意长度的字符。

在平时用Excel进行各类数据处理的时候，很多朋友都会碰到的一个问题就是数据查询，说到查询函数大家可能也会说到的就是Vlookup函数。其实在Excel中还有其他更加实用的数据查找函数公式，今天我们就来完整的学习一遍Excel中12种查询方式。

场景1：正常情况下数据查找

案例：查找出对应人员的语文成绩

函数=VLOOKUP(F5,B:C,2,0)

场景2：向左数据查找

案例：根据学号查询出对应姓名

函数=INDEX(B:B,MATCH(G5,C:C,0))

场景3：多函数条件交叉查询

案例：求出赵二第三周考试成绩

函数=VLOOKUP(I6,B:G,MATCH(J6,B$2:G$2,0),0)

场景4：LOOKUP多条件查询

案例：求出B产品在京东平台的销量

函数=LOOKUP(1,0/(B:B=F6)*(C:C=G6),D:D)

场景5：数据等级区间查询

案例：求出销售额对应的提成比例

函数=LOOKUP(F5,$B$3:$C$6)

场景6：横向纵向区间数据查询

案例：根据当月销售额及完成比例求出对应提成

函数=INDEX(C3:F9,MATCH(I4,B3:B9),MATCH(I5,C2:F2))

场景7：根据规律自动提取数值对应系数

案例：按照规律根据我们需要的数值提取出对应的系数

函数=TREND(OFFSET(B1,MATCH(D3,A2:A6,1),,2,1),OFFSET(A1,MATCH(D3,A2:A6,1),,2,1),D3)

场景8：查找符合条件的最后一个数

案例：查找出王五最后一天的销售额

函数=LOOKUP(1,0/(B:B=J4),F:F)

场景9：通配符模糊查找

案例：查找出姓王的人的销售额

函数=VLOOKUP(G5&"*",C:D,2,0)

场景10：高级匹配查找

案例：从对应完整地址中提取所在城市的提成点数

=lookup(9^9.find(A$3:A$6,A10),B$3:B$6)

场景11：查找最后一个非空的单元格内容

案例：求出对应人员最近一次缴纳社保的月份

函数=LOOKUP(1,0/(E4:E10<>""),$A$4:$A$10)

场景12：轻松实现一对多查询

案例：轻松提取部门人员全天的门禁记录

函数=IFERROR(VLOOKUP(ROW(A1),A:D,4,0),"")，首先需要在A列做辅助列，函数=COUNTIF(B$2:B2,G$2)

现在Excel关于数据查询你学会了吗？

今天终于拿到了转录组的数据，充满了期待，可是看过数据傻眼了，为啥？

数据那么多，我怎么看？

需要的数据分散在那么多的表格，基因数那么多，怎么做数据合并？

我想要的关键基因怎么筛选？

筛选出来之后还要做数据汇总？

这么多问题到底应该如何破？

问题一：关键基因筛选（通过“关键词”查找）

解决方案：数据“筛选”功能

通过“数据”菜单栏下的“筛选”功能可以对每列实现自动筛选功能，点击在表格第一行右下角的下拉箭头，可以出现文本筛选，输入关键词，即可筛选出感兴趣的关键基因。需要对每列都进行筛选。

问题二：关键基因相关数据整合（整合不同表格中的数据）

解决方案 ：Vlookup函数

VLOOKUP是一个查找函数，给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找到的值。所有的测序项目中，数据分析时的基因名称或ID都是唯一的，正好完美地匹配了vlookup函数的要求，以保证数据匹配的唯一性。

它的基本语法为：

=VLOOKUP（查找目标，查找范围，返回值的列数，精确OR模糊查找)

第一个参数（查找目标）必须是数据中的唯一匹配项，如果不唯一，返回值会自动匹配为第一个找到的数据结果；

第二个参数（查找范围）可以在同一个文件，也可以跨文件查找，但必须是以包含第一个参数的利作为起始列，可以选择列，也可以选择固定区域；

第三个参数（返回值的列数）必须是整数，是待匹配数据在查找范围区域的第几列；

第四个参数（精确OR模糊查找），精确即完全一样，模糊即包含的意思，小编通常默认为0.

匹配上第一个数后，选中该单元格，待右下角出现“+”时，双击或下拉即可完成所有数据的匹配项。

问题三：关键基因数据汇总（数据汇总统计）

解决方案：1、数据透视表（简单）

2、函数：countif sumif （难度高）

3、函数：数据“筛选”功能（简单+模糊搜索）

1、数据透视表（简单）

Excel自带的智能数据统计功能，掌握这一技能足以让你在数据分析中事半功倍。该功能嵌入在“插入”菜单下的“数据透视表”，可以选择一个sheet下分析，也可以选择新建工作表进行分析，因测序数据一般涉及的数据较多，很难一个页面全部展现，因此推荐在新建的工作表中进行。

具体操作如下：

操作时仅需要将右上方的字段拖拽到下方的表格内即可，可以选择合适的展现形式，一般是对基因id的计数居多。如果是对数据做求和计算，可以通过点击“Σ值”里面的选项后面的箭头进行设置，如下：

该项统计只支持精确统计，必须完全匹配，若有空格或字符数字的差异均采用不同的项进行计数。

Countif是指对指定条件的单元格进行计数（单条件计数），语法如下：

=COUNTIF（range，criteria）

参数range 表示条件区域——对单元格进行计数的区域。

参数criteria 表示条件——条件的形式可以是数字、表达式或文本，甚至可以使用通配符。

Sumif是条件求和函数,语法如下：

=SUMIF(range，criteria，sum_range)

参数：Range为条件区域，用于条件判断的单元格区域。

参数：Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。

参数：Sum_range 为实际求和区域，需要求和的单元格、区域或引用。

注：因篇幅有限，在此不再详细举例，若有需要可以给小编留言或约稿。

操作方法同第一个问题的解决方案，支持计数功能，统计数量在excel的底部，如图。

今天的excel技能介绍受到篇幅限制，若有问题可以跟“表姐”约哦~~表姐这么用心，喜欢的就赶紧转发啦~~

1.如图：Excel文件中包含多个数值，它们都有一个特点就是都含有5这样一个数值。

2.CTRL+F打开查找对话框，在查找内容是“5”但可以清楚看到，只要单元格含5都可以查找出来。这其实与我们本来是查找“5”的真实目的不一致。因为Excel中默认的查找方式是模糊查找。

3.在查找对话框中点击“选项”。

4.在“选项”中点击勾选“单元格匹配”，就是查找内容与单元格完全一致时还是才查找。再看一下查找结果。

（本文内容由百度知道网友茗童贡献）

模糊及精准查询

如图：Excel文件中包含多个数值，它们都有一个特点就是都含有6这样一个数值。

CTRL+F打开查找对话框，我在查找内容是“6”但大家可以清楚看到，只要单元格含6都可以查找出来。这其实与我们本来是查找“6”的真实目的不一致。因为Excel中默认的查找方式是模糊查找

怎么样才能够达到精确查找呢？在查找对话框中点击“选项”

在“选项”中点击勾选“单元格匹配”单元格匹配什么意思？就是查找内容与单元格完全一致时还是才查找。再看一下查找结果，是不是达到了我们要的精确查找的结果？

vlookup查找

如图所示，要根据I2单元格活动形式，在E-G区域中查询出对应的价格

经典套路：

=VLOOKUP(I2,E:G,3,0)

lookup查询

如下图所示，根据H2单元格姓名，在A-B数据区域中查询对应的工号

=LOOKUP(1,0/(H2=B2:B31),A2:A31)

公式指南：

=LOOKUP(1,0/(条件区域=指定条件),要返回的区域)

组合查找

利用MATCH和INDEX函数

如下图所示，根据姓名查询城市及职务。

G2单元格公式为：

=INDEX(A:A,MATCH($F2,$B:$B,0),0)

H2单元格公式为：

=INDEX(C:C,MATCH($F2,$B:$B,0),0)

如果看了这篇文章还是搞不定，可以直接给我留言

关于作者

专注分享Excel经验技巧

一个人走得快，一群人走得远，与195位同学共同成长

干货直接上，先看效果图：

同学要问里，这是怎么做到的嫩？

干货来了：

1、单元格H5中的公式：=INDEX(A:A,SMALL(IF(ISERROR(FIND($I$3,$A$3:$A$316,1)),9999,ROW($3:$316)),ROW()-4))&""

解释：A:A是要查询并返回的数值，FIND($I$3,$A$3:$A$316,1)是在A3到A316的单元格中查询I3中你输入的数值，ROW()-4是单元格所在行号减去上面4个行后从该处计算返回值。所解释的参数是需要在实际运用中进行适当的变化的。

2、同样的道理，单元格I中的公式：=INDEX(B:B,SMALL(IF(ISERROR(FIND($I$3,$A$3:$A$316,1)),9999,ROW($3:$316)),ROW()-4))&""

大家注意观察，只有A:A和B:B的变化，所以，这一个公式基本全搞定。

3、这是关键的一步，更是决定成败的一步，记住在每一项公式输入完成后，都必须是CTRL+SHIFT+ENTER组合键按下，这是代表输入的是数组公式，否则，会以失败告终！否则，会以失败告终！否则，会以失败告终！

小伙伴们，你学会了嘛？！

上次讲了vlooup的基本用法，用的是精确查询，那么现在说一下模糊查询，之前说了，vlookup一共有四个参数，vlookup(找谁,去哪找,第几列,模糊还是精确)，第四个参数代表着模糊或者精确，1=模糊，0=精确。可能直接看模糊不知道有什么用。

举个例子，我们给学生分数打优良中差，很多时候用的是if的嵌套，其实用vlookup更方便一点， 毕竟if最多嵌套7层，因此对数字做区间划分还是建议vlookup

根据分数，对成绩给予评定。

数据表：A1:C9

打分列：D列

区间划分参考：G1:G5

目的：在D列根据区间划分参考给C的分数打上评定。

这个时候就要用模糊匹配了，它会根据G列的数字，向下找最接近的那个，向下的意思就是，我给了两个便判定点0，60，如果在59分的时候，向下找最近。。那就是0，对应的就是不及格，如果是60分那么可以对应到60分就是及格，以此类推。那么具体公式在D2输入 vlookup(c2,$g$1:$h$5,2,1) ,注意这里$g$1:$h$5的意思是锁定这个区域，这样在拖拽单元格的时候就不会产生变动，不然g1:h5就会成，g2:h6..g3:h7..等等等。

公式解释就是，取C2（61),在G列区域找向下最接近的那个数值，并取G1:H5区域里相对的第二列数值（G第一列，H第二列），61找到了G3列，相对取回H3，也就是及格。

有疑问可留言，其他公式问题也可留言，后期会做教程。

最近公司新人很多，在带新人遇到的实际问题，也会逐步的制作教程，帮助大家解决实际工作中遇到的问题。

下期讲如何用vlookup进行一对多的查询

【Excel如何模糊性使用高级数据有效性搜索数据？】

数据有效性有时候能带给大家便利，有时候能带给大家烦恼，在数据少的时候，数据有效性非常便利；但是数据累计到一定量的时候，可选项就非常多，有时候并不是很方便。

看着别人在做数据的时候利用下拉菜单好像很高级，下面我带大家从不同层次进行学习这个下拉菜单的制作。

一、制作数据有效性方法一：数据→数据有效性→序列，来源，框选需要的区域。

图1

基本的数据有效性设置已经回顾了，有一些朋友会疑问，数据有效性如何能做到模糊性筛选数据，例如使用筛选功能那样，只需要输入一两个关键词，就可以匹配到相应的数据，得出简化的可视数据，带来不一样的视觉感受。现在我帮大家打破这一常规，来带领大家慢慢建立自己的高级数据有效性王国。

大家可以建一个数据源的工作表和过渡搜索数据的工作表，第三个是使用数据有效性的工作表。如图所示，每一个表格的作用会在实际使用中提及。

图2

二、对数据源工作表建立一个名称管理

第一步是对数据源工作表的公司名称建立一个名称进行管理—公司，基本操作步骤：选中所需单元格→公式→根据所选内容创建，名称为首行→确定。

图3

三、建立数组公式

第二步是在过渡工作表中建立一个数组公式，该数组公式作用是根据输入单元格内容进行搜索符合条件的内容。温馨提示：数组公式需要Ctrl + Shift + Enter三键一起输入，被提示循环运用的时候，点确定即可。

该辅助数组公式为：

=INDEX(数据源!A:A,SMALL(IF(ISNUMBER(FIND(CELL("contents"),公司)),ROW(公司),1000),ROW(A1)))&""

图4

第三步是继第二步建立第二个名称—公司名，该名称使用一个公式提取第二步搜索的数据，该公式为：

=OFFSET(提取!$A$2,,,COUNTIF(公司,"*"&CELL("contents")&"*"))

图5

四、建立第二个数据有效性

第四步是建立第二个数据有效性，该数据有效性设置与基本设置一致。设置步骤：数据→数据有效性→序列，公司名（已经在名称管理器下建立名称）。

图6

今天我大家分享一个强大的模糊查找功能，相信许多朋友有这个需求，打个比方，我有三个类别，蔬菜，水果，肉类，当然在A1输入火龙果，那么B1显示水果，当我在A1输入牛肉，在B1要显示内类，当我在A1输入香蕉，那么B1显示水果，当我在A1输入白菜，B1显示蔬菜，当我在A1输入猪肉，B1显示肉类，等等

具体效果大家看下面的动画效果

公式：=LOOKUP(FIND(A2,"桔子苹果火龙果梨子香蕉油菜白菜生菜芹菜牛肉猪肉羊肉"),{1,11,20},{"水果","蔬菜","肉类"})

公式解释：

find有3个参数

A.第1参数查找值A2

B.第2参数被查找的一串字符串，这里我们巧妙的利用把菜类相连接，"桔子苹果火龙果梨子香蕉油菜白菜生菜芹菜牛肉猪肉羊肉"，要注意是：大家把这三类分开写在一起，第一步部分是水果1到10个位置，11到19个位置是蔬菜，20个位置以后就是肉类，当然你也可以再添加别的类，但是你要注意的就是把它们放在一起，然后数出它们位置来作lookup函数的第2参数

第3参数是从第1个位置开始，我们可以不写，这里是省略

lookup函数有3个参数，当然也2个参数的，这们这里是用了3个参数的

第1参数是查找值，这里是find作它的第1参数，也就是说find找到查找值的位置

第2参数：,{1,11,20}，这个是活动的，大家不要死记硬背，也就是这几个值是根据的类别分隔位置决定的

第3参数{"水果","蔬菜","肉类"}

现在我们用具体的一个查找值来说明公式

当A2是火龙果，那么在"桔子苹果火龙果梨子香蕉油菜白菜生菜芹菜牛肉猪肉羊肉"那个位置呢？答案是5，也就是find返回5，这个5用来作lookup函数的第1参数， 而lookup函数的第2参数是{1,11,20}用了升序排序，这里有没有5，没有，没有就找比它小的，小的用一个1，那么就找到1，1对应的第loookup函数第3参数第1个值就是水果，所以返回水果

当A2是猪肉，那么在"桔子苹果火龙果梨子香蕉油菜白菜生菜芹菜牛肉猪肉羊肉"那个位置呢？答案是22，也就是find返回22，这个22用来作lookup函数的第1参数， 而lookup函数的第2参数是{1,11,20}用了升序排序，这里有没有22，没有，没有就找比它小的，比22小的有1，11，20，怎么办呢，根据lookup函数的二分法原是理，找小的里面最大那个20，那么就找到22，22对应的第loookup函数第3参数第3个值就是肉类，所以返回肉类。

最后公式=LOOKUP(FIND(A2,"桔子苹果火龙果梨子香蕉油菜白菜生菜芹菜牛肉猪肉羊肉"),{1,11,20},{"水果","蔬菜","肉类"})