最近收到粉丝小美同学的问题咨询，

“为什么别人发给我的表格我不能修改呢？，一做修改就发出告警！”小美说道。

到底是什么问题把小美急成这样呢？

看到小美发来的截图才恍然大悟

原来是对方设置了数据有效性(2016版的OFFICE中改称为数据验证)

既然要解决这个问题那么我们就需要了解数据验证功能是如何使用的。

数据有效性功能包含的细分功能比较多，今天我们就以小美的问题给大家做个案例分享。

小美的问题是由于别人通过序列制作了下拉菜单，所以无法修改菜单外的字符。

首先我们讲下如何创建下拉菜单

选中B列数据后单击【数据】选项卡，选择【数据验证功能】

打开【数据验证】选择【设置】>验证条件允许中的【序列】，在下方【来源】入口输入我们要设置的字符内容。此处可以通过手工输入，以英文状态下的逗号隔开，也可以直接应用某个区域单元格中的内容。本例中以手工输入“人事部，市场部”为例。完成输入后单击【确定】

完成确定后我们在B列任意单元格即可看到下拉菜单，内容为“人事部，市场部”

动图演示如下：

我们完成了下拉菜单制作，那么我们现在也学小美来输入一个新的部门！

当我们想要输入一个新的后勤部时系统自动弹出警告，“此值与单元格定义的数据验证限制不匹配”！

那如何改呢？

这就要回到我们之前创建的步骤啦！

在打开【数据验证】对话框中我们把序列中增加一个后勤部然后单击确定。

这样我们再试下！

小美看完后终于明白为啥改不了！但还是有个疑问，如果我不想要这些下拉菜单，我就要自己手工输入怎么办？如何取消呢？

其实这个问题很简单：

打开【数据验证】对话框，将验证条件设置为“任何值”就回到最初的单元格格式啦！

小美高兴的说通过这个问题彻底学会了数据验证制作下拉菜单的功能，下次自己也要做个这么专业的表格。

那么你从案例中学会数据验证功能的使用了吗？

【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

虽然我的文章多次提到，并且极力不推荐大家使用合并单元格，但有时候因为领导喜欢，又或者有强迫证，就是想用，然后合并单元格，遇到vlookup函数，又出错了，怎么办？

如下所示一个实际例子：公司里面有很多员工，每个员工的底薪都不一样，底薪如下所示：

该底薪标准数据位于表格的F:G列，然后现在要对员工的底薪标准进行匹配，表格中的A列是合并单元格的状态，然后在D列输入公式=VLOOKUP(A3,F:G,2,0)

这个结果中只有每个业务的第1行是可以正常匹配的，后面的数据都是错误值#N/A

遇到这种情况最简单的处理方式，就是把合并单元格拆分，填充内容，操作步骤是，选中合并的单元格，取消合并，按CTRL+G，查找空值，在公式编辑栏输入=A2，然后按CTRL+ENTER键，最后将A列的数据复制，粘贴成数值数据，把里面的公式去除，整体操作动图如下所示：

如果你们领导非要要求合并单元格，那你就用长长的公式来处理的，在D2输入公式：=VLOOKUP(VLOOKUP("座",$A$2:A2,1),F:G,2,0)

其实就是把A2单元格再使用一个VLOOKUP函数公式，VLOOKUP("座",$A$2:A2,1)代替，这样将A列的合并单元格进行拆分了。

VLOOKUP("座",$A$2:A2,1)函数使用到了模糊查找，混合累计引用方式，"座"这个字符是编码比较大的一个字符，它会查找到最后一个文本，然后返回值。所以能够得到上述的效果。

虽然这个函数能够解释合并单元格的vlookup函数使用，但是小编还是不建议大家使用合并单元格，这样公式就不用这么复杂使用了。你觉得呢？

本节完，欢迎留言讨论，期待你的转发

上期芒果君教大家了VLOOKUP纵向查找，那个真的真的很基础啦，今天教大家一个它的家族同胞HLOOKUP横向查找。简而言之，就是按行查找。

废话不多说，直接上教程。

首先，在第二张表输入“=”，左上角函数选项找到HLOOKUP，然后会跳出一个弹出框，有四个参数。第一个参数选择你要查找的值，第二个参数选择你要查找的区域，第三个参数重要了，输入你要查找数据所在区域的行数，第四个参数输入0即可。然后横拉，所有数据就出来啦。

注意，老生常谈啦，查找完数据，记得复制粘贴为数值，以免你进行其他操作，数据就全没啦。忘说了，第三个参数一定要写你所选区域的第几行，而不是整个表格的第几行，切记切记。最后附上一张参数选择的简单说明。

最近有一个简单需求，在B表中找出所有在A表的id对应的code和label值

我印象中是知道这个能用vlookup函数实现的，奈何我对excel实在是只停留在会普通排序和刷选水平上。通过一番查找，我想到的办法是（可能比较笨，还是记录一下吧）

一，首先是使用vlookup函数在A表的左边找出label值构造出一个新的AA表。此时的函数为=VLOOKUP(B2,E:G,3,0)，其中3表示返回待查表的第三列的值，即是label列的值，0表精确匹配。

二，再次使用vlookup函数，只是这次待查找的元素是label值，带查找的表为AA表，即,$A$2:$B$8，注意，这里使用$是为了固定这个表的范围，避免下拉函数时使得表的范围同步变化。在B表的右边输入函数=VLOOKUP(G2,$A$2:$B$8,2,0)，得到如下图的结果

最后对B表中的新增列tmp进行筛选，就能得到想要的结果。

Hi，我是秋小叶~

我们在学习工作中难免会遇到一些奇葩表格，比如应该写在一列里面的内容，偏偏被拆成了两列……

碰到这种奇葩数据，压根没办法正常统计计算，只能先调整成正常的形式。如果你只会复制粘贴，那可得忙半天，甚至熬夜通宵才能搞定。

而如果你掌握一些整理数据的小技巧，那可就不一样了。别人半天的工作量，可能你只需要 10 秒！

今天 King 老师就来分享 6 个整理数据的万能公式。

01.行列互换

需要将数据的行和列对调过来时，可以用 TRANSPOSE 转置函数。

万能配方：

TRANSPOSE是数组函数，必须先选中区域，输入公式后按【Ctrl+Enter】完成输入才能生效。

案例第 10 行中因为源数据没有更多数据，产生了错误值，这些错误值不能单独删除，因为数组区域是一个整体，必须同时修改。

其实，普通的行列互换转置，用选择性粘贴更简单，还可以保留源格式。So，函数公式虽好，可不要贪多哦 ~

02.一行转多行

想要把一行数据转换成多行数据怎么办？比如，转换成固定 3 列的多行数据：

万能配方：

这是一个万能的函数公式，你不需要懂太多，只要会改参数就能套用。

=INDEX($A$1:$G$1,1,(ROW(A1)-1)*3+COLUMN(A1))

把红色的数据区域换成你的数据区域

把 3 换成一行希望填写的数据个数，比如一行 4 个，就换成 4。

03.一列转多列

相对应的，如果想要把一列数据按固定的行数转成多列怎么办呢？还是一样的配方，稍微改造一下公式结构，就能能套用：

万能配方：

这同样是一个万能的公式结构，只需要改变两个参数，就能套用

=INDEX($A$1:$A$7,ROW(A1)+(COLUMN(A1)-1)*3,1)

把红色的数据区域换成你的数据区域

把 3 换成一列希望填写的数据个数，比如一列 2 个，就换成 2。

04.多行转一行

一行转多行可以，那反过来多行转一行行不行？当然可以，看效果：

万能配方：

这里用的公式看起来更复杂了，但是要做的事情反而更简单了，只需要将下方公式中 3 个一样的数据区域，统一换成你要转换的数据源，就可以直接套用。

=INDEX($A$1:$B$3,INT((COLUMN(A1)-1)/COLUMNS($A$1:$B$3))+1,MOD(COLUMN(A1)-1,COLUMNS($A$1:$B$3))+1)

05.多列转一列

你应该已经猜到了，多列转一列，也很简单：

万能配方：

照猫画虎似的，将下方公式中的标红数据区域统一换掉，就能直接套用

=INDEX($A$1:$B$3,MOD(ROW(A1)-1,ROWS($A$1:$B$3))+1,INT((ROW(A1)-1)/ROWS($A$1:$B$3))+1)

06.交叉转换

下面这个表格中的数据就比较棘手了，所有的人员信息都分成了两列，一列是人员信息类别的名称，一列是具体的信息。怎么把他们转成一个人的信息为一行的数据记录呢？

这是一个函数公式的高级应用：

万能配方：

案例效果中涉及 3 个函数公式，都是非常经典的套路。

利用 COUNTIF 函数自动分组

=COUNTIF(数据区域,条件)

案例公式COUNTIF($B$2:B2,$B$2) 中第一个参数 $B$2:B2 表示锁定起点为 B2 ，但终点跟随公式自动变化的动态区域；第二个参数则是计数条件。公式含义为，计算 B 列第 2 行到当前公式所在行中，值和 B2 相等的单元格数量。结果就是每一行信息属于第几个人的。

利用LOOKUP 函数实现多条件查找匹配

=LOOKUP(1,0/((条件1)*(条件2)*(条件N)),匹配区域)

案例中的公式为 Lookup(1,0/(($D$2:$D$14=$F2)*($B$2:$B$14=G$1)),$C$2:$C$14)。用到了两个条件，D 列中的数据等于公式所在行的组名,B 列中的数据等于公式所在列的标题。返回同时满足 2 个条件时，C 列中的数据，也就是每一个人的具体信息。

利用 IFERROR 屏蔽错误值

=IFERROR(原公式,出错时返回另一个结果)

当原公式结果出错时，让整个公式结果等于横杠符号。实现暗度陈仓的效果。

这些技巧，是不是很好用呢？

行列互换

一行转多行

一列转多列

多行转一行

多列转一列

交叉转换

将一个excel表中的数据匹配到另一个表中，需要用到VLOOKUP函数。简单介绍一下VLOOKUP函数，VLOOKUP函数是Excel中的一个纵向查找函数，VLOOKUP是按列查找，最终返回该列所需查询列序所对应的值。

工具：Excel 2013、VLOOKUP函数。

1、一个excel表，需要在另一个表中找出相应同学的班级信息。

2、把光标放在要展示数据的单元格中，如下图。

3、在单元格中输入“=vl”会自动提示出VLOOKUP函数，双击蓝色的函数部分。

4、单元格中出来VLOOKUP函数。

5、选择第一列中需要匹配数据的单元格，选中一个就可以，然后输入英文状态下的逗号“,”。

6、返回到第二张表【百度经验-表2】，选中全部数据。

7、因为要返回的是【表2】中第四列的班级信息，所以在公式中再输入“4,”（逗号是英文的）。（ps:提示信息让选择“TRUE”或“FALSE”，不用选，直接按回车键就可以）

8、按回车键之后，展示数据，效果如下图。

9、要把一列中的数据都匹配出来，只需要按下图操作。

10、完成操作，最终效果如下。

注意：

1、输入的符号需要是英文状态下的，如：逗号。

2、所匹配的数据需要和当前数据不在同一个excel表，不然会匹配错误。

（本文内容由百度知道网友艺天逊逻贡献）

Excel中如果通过Vlookup函数查找数值数字，而查找区域相同数字为文本格式，就匹配不到数据，必须转换成同一种格式才行。

软件版本：Office2007

举例说明如下：

1.Vlookup函数匹配不到数据，可以看到D列中的数字是数值，而匹配区域中的数字为文本：

2.利用分列等方法，把文本数字转换成数值数字：

3.这样，Vlookup就匹配到数据了：

（本文内容由百度知道网友鱼木混猪贡献）

很多办公人员，特别是统计业务条线上的朋友，都对数据匹配这个事头疼不已，有什么好的方法吗？当然有，今天我们详细解答一下vlookup函数的实际应用问题：

vlookup函数举例：如下图，已知表sheet1中的数据如下，如何在 sheet2 中进行如下引用：当A列学号随机出现的时候，如何在B列显示其对应的物理成绩？也就是要将表一的数据自动填充道表耳相应位置！

首先我们需要详细了解我们要用到的vlookup函数：vlookup是判断引用数据的函数，它有四个参数：

1、判断的条件

2、跟踪数据的区域

3、返回第几列的数据

4、是否精确匹配

根据上面的定义，我们就可以在sheet2表的B2单元格输入公式：

=vlookup(a2,sheet1!$a$2:$f$100,6,true)

四个参数详细定义：

1、a2 是判断的条件，也就是你需要匹配的对象；

2、sheet1!$a$2:$f$100 是需要查找的数据区域；

3、6 这是返回什么数所在的列数，如上图的物理是第6列，所以应该是6；

4、是否绝对引用，如果是就输入 true 如果是近似即可满足条件 那么输入false

结果如下图：

特别提示：如果你比较厌烦输入这些超长的函数公式，你也可以直接插入：公式，插入公式，vlookup

不知道你是否已经会使用vlookup这个条件查找函数，如果有什么不会的可以留言哦！

Hi，我是秋小叶~

做 Excel 表格，一提到函数公式，很多人就头皮发麻。

为什么？其一，因为表格需求千变万化，数据结构千差万别，一点点细微的差别，用法可能就大相径庭；其二，每个函数都有自己的语法规则，必须一五一十严格遵照它的要求，它才会乖乖听话。

那有什么方法，可以在短时间内，快速提升函数公式的应用能力？答案只有一个——用！

怎么用呢？

在工作中以问题和需求为出发点，去找合适的方法。而要想成为个中高手，还有 2 种方式，系列化延伸和一题多解！通过对比不同的思路和方法，能对 Excel 基本技能有更加深入的认知。真到要用的那一刻，就能信手拈来。

拿工作中最常用到的查找匹配为例，要在左边数据区域中查找出小王的销量数据 11，填写进 F2 单元格，怎么做？

有一点 Excel 基础的人都知道，用一个 VLOOKUP 函数就够了：

=VLOOKUP(E2,A:C,3,0)

（拿着 E2 中的「小王」去匹配区域 (A:C) 的第一列，也就是员工一列中查找，找到以后返回匹配区域中同一行第 3 列的数据，也就是 11，其中最后一个参数 0 表示精确匹配，必须找一模一样的「小王」）

这就是单条件的查找匹配。那……假如工作中需要按多个条件查找匹配呢？还能用 VLOOKUP 实现吗？

举个例子，查找匹配出员工、医院、产品同时满足条件的销量数据，又该怎么做？

直接查找匹配不行，我们可以明修栈道，暗度陈仓。既然多条件复杂，我们可以将多个条件合并成一个条件。

首先插入一个空列，设为合并列。在 D2 单元格输入如下公式，将左边的三列合而为一：

=A2&B2&C2

（ & 是连接运算符，可以将单元格、数据拼接成新的文本）

有了这个辅助列，作为查找匹配的索引，我们用 VLOOKUP 也能轻而易举的实现多条件查找匹配，只要在 J2 单元格输入如下公式即可：

=VLOOKUP(G2&H2&I2,D:E,2,0)

（和前面的公式不同点在于，查找对象换成了 G2、H2、I2 三个单元格合并以后的文本，匹配区域从 D 列开始到 E 列，这是因为VLOOKUP有一个前提条件：只在匹配区域的第一列中查找索引对象。）

通过上述系列化延伸，你就能进一步了解更多知识点：

VLOOKUP 的基本用法：只要在两张表中存在可以索引的数据，就可以查找到同一行中的其他数据

用连接符 & 可以将多列数据合并为一列

VLOOKUP 公式中可以嵌套使用其他公式，比如 G2&H2&I2 的计算结果作为查找对象

VLOOKUP 公式只在匹配范围的第一列里查找匹配，按指定的列序返回结果

到这里，问题已经解决。

但是，学习高手可能会继续纵向深挖：

如果用于索引的查找匹配列不在第一列时，例如合并列在销量列后头时，又该怎么做？

或者横向扩展：

多条件查找匹配，除了用 VLOOKUP+ 辅助列的方法，还有哪些方法？哪一种方法会更简单高效？

要解决这个问题，其实我们就是追求 一题多解。预知详情，我们下期再聊。你也可以在评论区留下思路，交流碰撞说不定会激发出新的灵感。