相册 – 第167页

VLOOKUP 函数会以最初达成一致的检索值单元格作为对象

在 A 列中重复输入了同一家客户公司的名称，B 列中则为相应的负责人的名字。

如果以 A 列和 B 列中的数据为基础，想要在 E 列中按顺序输入相应的负责人，这时使用 VLOOKUP 函数可能会无法得到想要的结果。我们来实际操作一下。

➊ 在单元格 E1中输入以下公式：

=VLOOKUP(D2,A:B,2,0)

➋ 将单元格 E1中的公式一直复制粘贴到第7行。

E 列中的相同的公司对应同一名负责人。例如，ABC 股份有限公司，原本是按铃木、田中、加藤这样的顺序排列，而现在全部变为了“铃木”。

像这样检索值存在重复的情况，VLOOKUP 函数会以从上数、与起始处一致的检索值的单元格为对象来处理数据。单元格 E2、E3、E4也同样如此，都以“ABC 股份有限公司”为检索值，在作为检索范围的 A 列中以最初的单元格 A2为对象运行 VLOOKUP 函数，所以会返回“铃木”这个值。

无重复状态下应加工后再处理

为了解决这个问题，我们可以把有重复数据的 A 列和 D 列中的数据“加工”成唯一的状态，也就是该列下无重复的状态。这里，我们需要重新追加操作用的数据列，再进行处理。

这个方法的原理是，给重复的客户公司名称标上不同的固定编号。

首先，在各个表的左侧分别追加2列，作操作用。

表格左侧分别追加2列，供操作用

按照以下步骤，给相同客户公司名称的每个数据分别标上编号。每个公式引用的哪个单元格，进行了怎样的处理，我们一边看一边分析。

➊ 在单元格 A2输入以下公式，一直复制粘贴到第7行。

=COUNTIF($C$2:C2,C2)

※C 列的客户公司名称标上数字

➋ 同样地，单元格 F2输入下列公式，一直复制粘贴到第7行。

=COUNTIF($H$2:H2,H2)

※H 列的客户公司名称标上数字

➌ 在单元格 B2中输入以下结合了固定编号和客户公司名称的公式，一直复制粘贴到第7行。

=A2&C2

➍ 同样地，在单元格 G2输入下列公式，一直复制粘贴到第7行。

=F2&H2

做完以上步骤，在 I 列输入下列 VLOOKUP 函数后，目标单元格中就会自动显示相应的负责人了。

=VLOOKUP(G2,B:D,3,0)

显示个别对应的负责人名称

这个方法的关键在于，用 COUNTIF 函数给每个数据设定编号（出现次数），通过编号与检索值得到新的固定检索值，并将其嵌入 VLOOKUP 函数中，由此就能得出正确结果了。

输入表与负责部分的项目顺序不同时

在刚才的例子中，为了让“输入表”与“负责部分”的项目排列顺序保持一致，第三参数按顺序输入2、3、4……这样连续的序号。因此，VLOOKUP 函数第三参数引用嵌入了 COLUMN 函数，这样做会提高效率。

但是，如果像下面这样，输入表与负责部分的项目顺序不同时该怎么办？也就是说第三参数不是连续数字的话，各单元格中的 VLOOKUP 函数即便运用了 COLUMN 函数，也无法得出正确的第三参数。

输入表与负责部分的项目顺序不同时

此例中，D 列的“单价”对应“负责部分”最左端往右数第6列，E 列的“生产者”对应“负责部分”最左端往右数第5列。在这样的前提下，如果想要在单元格 C3中输入最开始的那个函数公式，之后只要复制到 G 列也都可以得出结果的话，我们应该怎么做呢？

在 C 列商品名的单元格输入的 VLOOKUP 函数中第三参数应该是什么数字呢？答案是2。那么，我们只要输入能自动导出数字2的第三参数就可以了。这时候，我们就要用到 MATCH 函数。

我们通过以下例子具体解释一下。

上述例子中，A1到 D1项目名称分别为“商品名”、“单价”、“生产者”、“最低订购单位”，这些项目在 F1到 I1的范围中位于左数第几列，会相应地显示在 A2到 D2中。以单元格 A2为例，“A1（即商品名）的值，在 F1：I1范围里位于左数第2个”，那么 A2中则会显示数字2。

在单元格 A2做出这种处理的是下面的函数公式。

=MATCH(A1,$F$1:$I$1,0)

MATCH 函数中第一参数指定的值，会导出在第二参数指定范围中位于第几位的数字。第三参数基本上“只要输入0就行了”。

在图中，将单元格 A2的公式一直复制粘贴到 D2。因为第一参数不做绝对引用，单元格 B2里被复制粘贴的公式中的第一参数为 B1，单元格 C2里被复制粘贴的公式的第一参数为 C1，D2中则是 D1。

第二参数限定了纵列或横行的范围。

▲指定纵列的范围

第一参数指定的值为在此范围内的上数第几行。

▲指定横行的范围

第一参数指定的值为在此范围内左数第几列。

单元格范围限定为 F1:I1，则呈现如下状态：

单元格 A1即“商品名”位于左数第2个
单元格 B1即“单价”位于左数第4个
单元格 C1即“生产者”位于左数第3个
单元格 D1即“最低订购单位”位于左数第1个

能够在单元格中显示数字2、4、3、1，是因为 MATCH 函数的处理。

在 VLOOKUP 函数的第三参数中加入 MATCH 函数，即使“输入表”与“负责部分”的项目的排列顺序不同，也能够通过 MATCH 函数取得“‘输入表’的各项目名在‘负责部分’下位于第几列”的数字，把这样的结构嵌入 VLOOKUP 函数第三参数中就能够解决顺序不同的问题。在输入表的单元格 C3，请输入以下公式：

=VLOOKUP($B3,$I:$N,MATCH(C$2,$I$2:$N$2,0),0)

然后复制到整个表格，画面则显示如下：

在单元格 C3中输入=VLOOKUP($B3,$I:$N,MATCH(C$2,$I$2:$N$2,0),0)并复制粘贴至全表

分析 MATCH 函数的处理

可能乍一看上述的公式很复杂，接下来我们来仔细分析一下。关键在于理解嵌入 VLOOKUP 函数第三参数的 MATCH 函数是如何发挥作用的。

MATCH(C$2,$I$2:$N$2,0)

这个公式得出的数字指向的是，第一参数指定的单元格 C2的值（即商品名的值）位于第二参数指定范围（$I$2:$N$2）的左数第几个。在这一例子中为数字2，它与单元格 C3中以 B3的值（数字1）为检索值的 VLOOKUP 函数里，检索范围 I:N 从左数第几列的对应数字是一致的。

将输有单元格 C3内容的单元格一致复制粘贴到 G6，为了不让参照项移位，需要设定绝对引用。

Excel中输入连续的数字：在工作表中沿行方向输入连续的数字

那么，如果想要在工作表中沿行方向，即向右方输入连续的数字，应该怎么做呢？这时，我们可以使用 COLUMN 函数。COLUMN 函数的意义在于，在输入如下内容的单元格中，能够得出该单元格位于工作表的左数第几列。

【公式】

=COLUMN()

例如，在单元格 B1中输入这一函数会得到2。

在单元格 B1中输入=COLUMN()

单元格 B1位于 B 列，即工作表的左数第2列。因此得出数字2。

如果继续向右拖拽复制，就会开始从2连续输入数字。

要想从1开始连续输入的话，与 ROW 函数同理，减去数字做相应调整就行。

➊ 在单元格 B1输入下列公式后，按回车键。

=COLUMN()-1

➋ 将单元格 B1向右方拖拽复制，出现连续的数字。

通过 ROW 函数、COLUMN 函数在工作表中输入连续的数字，可运用在以下的需求中。

在表格中隔行标注2种不同颜色
输入连续的阿拉伯数字
快速沿行方向输入大量 VLOOKUP 函数

在这之后，我会逐个具体说明。

Excel中输入连续的数字：如何连贯输入连续的数字

前两篇介绍的2种方法有个前提，就是所有输入单元格的数字要为固定值，因此如果删去中间某一行或者插入一行，连续的数字就从中间断开了。要想在这种情况下也让数字保持连贯，我们可以使用 ROW 函数。无论删掉还是穿插一行单元格，都可以保持数字的连贯，不需要逐个修改。

输入下列公式的单元格，会显示“该单元格位于工作表中的第几行”的数据。

【公式】

=ROW()

括号中不要输入任何内容。请记住像这样在函数括号中不输入任何参数的方法（比如 TODAY 函数、NOW 函数等）。

例如，在单元格 A2中输入这个函数，单元格 A2中会显示2。由于单元格 A2位于工作表中的第2行，因此数字2代表的是这个行数。

在单元格 A2中输入=ROW()

如下图所示，直接向下拖拽复制，从2开始的连续。

从单元格 A2向下拖拽复制

各单元格“=ROW()”这个公式导出的数字，这个数字表示该单元格所处的行数，所以会在单元格中显示连续的号码。

但是通常来说，连号都是从1开始。因此，需要在这个 ROW 函数中做减法。例如，想从第2行（这里是单元格 A2）开始输入连续的数字时，请输入下列公式。

=ROW()-1

在单元格 A2输入=ROW()-1

按回车键，ROW()取得的行数2再减去1，显示结果得到1。

显示结果为1

将此单元格向下拖拽复制，各单元格中就会出现连续的数字。

将单元格 A2向下方拖拽复制

这里的连续的数字，按照各单元格中的 ROW 函数取得的该单元格时所在的行数，因此就算中间删除或添加一行单元格，都会从1开始保持数字的连贯。

Excel中输入连续的数字：使用“自动填充”功能

先介绍一下自动填充功能的使用方法。例如，在单元格 A2中输入1，在单元格 A3中输入2，然后同时选中单元格 A2和 A3并向下方拖拽复制，一直拖拽到最后一行。这样，每一行中的单元格中的数字就是连续的。

同时选中单元格 A2和 A3

向下方拖拽复制，一直到拖拽到最后一行

Excel消除重复数据的方法：选中并删除重复的单元格

即使知道工作表中存在重复数据的单元格，也还有问题需要解决。一般来说，确认工作表中存在重复的数据后，需要删除重复的信息，将表格整理为没有重复数据的状态。利用先前的方法只能确认是否存在重复的数据，无法选中并删除重复的单元格。

因此，我们需要将原来的公式修改成这样：

=COUNTIF($A$2:A2,A2)

在单元格 B2中输入=COUNTIF($A$2:A2,A2)

在单元格 B2中输入公式时，指定与第二参数一致的单元格查找范围的第一参数为“$A$2:A2”，也就是单元格 A2。因此，得出的结果自然为1。

接下来，双击右下角游标，将这一单元格复制到最后一行，就会出现以下画面。

将单元格 B2复制到最后一行

这也就是我在第1章里稍微提到过的自动筛选，即只抽出 B 列中值为2的单元格后并删除，即可删除所有重复项。

自动筛选抽取 B 列值为2以上的单元格

单元格 B2的函数中的第一参数“$A$2:A2”，指定从 A2到 A2作为函数的范围。冒号（:）前的内容表示只引用范围起始点的单元格，意为绝对引用。如此一来，将这一单元格向下拖拽复制后，单元格 B3的范围为“$A$2:A3”，单元格 B4为“$A$2:A4”，以此类推。也就是说，作为指定范围的单元格的起点，即单元格 A2是固定的，终点的单元格却是相对引用，可以不断延续。这样就让人觉得第一参数指定的范围在无限扩展。

在 B 列的各单元格中的函数引用的并不是位于该单元格下面的单元格中的内容。所显示的数字表示的是“该单元格相邻的单元格的数值，在 A 列中出现了几次”。

照此推断，就能得出“B 列中显示有2以上的数字的数值表示：在 A 列中的前面的某行中已经出现过有相同值的单元格”，表示数据有重复。因此，如果将 B 列中含有2以上的数据的单元格全部删去，A 列中就不会存在重复的数值了。

专栏：不要使用“删除重复”键：2007之后的 Excel 版本都追加了“删除重复”功能，但我个人不推荐使用，因为在实际操作中曾发生过删除了并没有重复的数据的事例。

Excel消除重复数据的方法：如何判断是否有重复

“电话征订名单中，多次出现同一家公司！”

这是某个正在开展开发新客户的销售部门里发生的事。这个部门负责电话征订的共有10位销售人员，这些人员先制作电话征订名单，然后根据名单给客户打电话。由于每位销售人员都是通过网络等方式调查并收集目标企业信息的，所以同一个企业会出现在不同的销售人员的电话名单中。

这时，如果大家一同开始给目标企业打电话，就会导致同一家公司多次接到同一公司的销售人员的电话，最终一定会听到客户的投诉：“别再给我们打电话了！”因此，经常有人来问我如何才能避免这样的事情发生。

像这样，在管理客户名单时，应该如何检查是否存在重复的数据？

首先我们来看一下简单的判断方法。比如，A 列为 ID 信息，要想检查其中是否有重复的内容，可按照以下逻辑判定。

计算该 ID 在 A 列中的数目
如结果为1个则表示没有重复数据，如果是2个以上则可以认定为有重复

那么，我们来看一下应该如何在 Excel 中处理重复数据。在此，假设想要在 B 列中显示是否有重复数据的判定结果。

➊ 在单元格 B2中输入以下公式：

=COUNTIF(A:A,A2)

这一公式用于计算在 A 列中与单元格 A2有相同数值的单元格的数目。

若结果为1个，说明 A 列中不存在与单元格 A2有相同数值的单元格……也就是说不存在重复数值。

若结果显示为2，说明 A 列中存在与单元格 A2有相同值的单元格，可以得知数据有重复。

➋ 复制到数据的最后一行

像这样，在一列中连续输入已经存在的数值时，需要复制的行数会增多。用鼠标将相邻列中的函数公式拖拽复制到最后一行，是一件十分麻烦的事。下面的技巧可以让你在一瞬间完成这项操作。

在单元格 B2中输入公式后，再次选中单元格 B2，将鼠标移到被选中单元格右下角的浮标上。这时，我们可以看到原本白色十字的游标变成了黑色。接下来，我们需要双击这个黑色游标。

这样，我们就能够确认 A 列中的单元格是否存在重复的数据。

输入商品名，自动显示价格——VLOOKUP函数的基础

假设有以下数据表格。

这时，A 列中输入商品代码后，单价一列即可自动出现价格，这样不仅十分方便，还能避免输入错误。

但是，要想实现这点，需要预先在其他地方准备好“各商品的价格”一览表。在这张 Excel 工作表中，可作为参考信息的表格（商品单价表）位于右侧。

那么，我们试着将与 A 列各商品代码匹配的单价显示在 B 列中吧。

➊ 在单元格 B2中输入以下函数。

=VLOOKUP(A2,F:G,2,0)

➋ 按回车键确定后，将 B2拖拽复制到单元格 B8。

由此，B 列的各单元格中出现了与商品代码匹配的单价。

在此输入的 VLOOKUP 函数，到底是什么样的函数呢？只有能够用文字解释，才算是完全掌握了这个函数。将 VLOOKUP 函数转换成文字，则为以下的指令：

“在 F 列到 G 列范围内的左边一列（即 F 列）中，寻找与单元格 A2的值相同的单元格，找到之后输入对应的右边一列（即 G 列）单元格。”

VLOOKUP 中的 V，代表 Vertical，表示“垂直”之意，意为“在垂直方向上查找”。此外，类似函数还有 HLOOKUP 函数，首字母 H 代表 Horizontal，表示“水平”之意。因篇幅有限，本书无法做出更详尽的说明，有兴趣的读者可自行了解。

COUNTIF函数：如何计算每名负责人员的销售件数

用前面写到的 SUMIF 函数可以算出每一位负责人员达成的销售额，那么这回来算一下每个人的销售件数吧。利用前面介绍过的 SUMIF 函数，在 H 列的“销售额”中输入每一位负责人的销售额。

I 列的“销售件数”，则显示“这些销售额分别来自多少件销售业务”这一数据。在这个表格中，单元格 I2中的数字表示“A 列中含有的单元格 G2数值的数目（即“吉田”）有多少”。

➊ 在单元格 I2中输入以下公式，计算 A 列中有单元格 G2数值的数目（即“吉田”）有多少。

=COUNTIF(A:A,G2)

➋ 按回车键后，单元格 I2中显示的数值表示：A 列中出现的与 G2有相同值的单元格（即“吉田”）的数目。

➌ 将单元格 I2中的公式拖拽复制到 I6，然后选择 I7，按Alt +Shift += （AutoSUM 快捷键）获得总和。

完成以上三步，即完成统计。

这里出现的数字，计算的是“A 列中含有各负责人名字的单元格，各有多少个”。把它作为一个商业数据概念来讲的话，表示的是“吉田的销售件数共有5件”。

另外，在这张图中有一列空白的单元格，此列数据是将每一位负责人的销售额除以销售件数，得到的平均销售额的数据。通过计算结果，就能分析出如“虽然以销售件数来说吉田比佐藤多一些，但是佐藤的销售额更高是因为佐藤的平均销售额更高”这样的结果。以此进一步了解到“吉田只要向佐藤看齐，增加每件交易的平均销售额，即可提高总销售额。”

只是像这样简单地分析，也能成为我们探讨一些具体销售战略的契机，比如“为了这一目标，应该具体订立怎样的销售策略？”“我们应该考虑什么样的促销手段？”

通过使用 COUNTIF 函数，我们可以检查数据是否重复、确认指定数据是否存在、单元格中是否包含指定文本，等等。这是一个十分方便的重要函数，请一定要掌握。

参加名单中，有多少人出席—COUNTIF函数的基础

假设要制作活动的参加者名单。参加与否一列中需要输入○、△、×这3种符号。

活动出席人员名单

那么，如自动计算出现在的参加者有几人，即标记“○”的人数是多少，以及“缺席人员，标记‘×’的有几个人”，应该怎样处理呢？当然，我们不可能每次都口头计算，再填到 E1～E3的表格里，这样太浪费时间了。

为节省时间，有一种函数可以算出“在 B 列中，标有‘○’的单元格有多少个”，那就是 COUNTIF 函数。

好了，我们来试着在 B 列中分别计算单元格 E1～E3中的○、△、×的数量吧。也就是说，即使这张参加名单表格有任何追加、变更的情况，各个记号的数量也会自动更新。

➊ 在单元格 E1中，输入以下公式，计算 B 列中与 D1有相同内容的单元格的数量。

=COUNTIF(B:B,D1)

➋ 按回车键，在单元格 E1中显示结果。

➌ 将公式拖拽复制至单元格 E3，显示其他记号的数量。

COUNTIF 函数是由下面2种参数构成的。

第一参数：计算其中非空单元格数目的区域
第二参数：在第一参数的指定范围内计算数目的条件

指定范围（第一参数）中，计算出第二参数指定的值或者与指定的条件一致的单元格的数目。

相册

在有多个相同检索值的工作表中使用 VLOOKUP函数的技巧