如果你学不会这个技巧,那么加班就是必然

sadomasochism · 发表于 2016-9-20 15:31:08

如果你学不会这个技巧,那么加班就是必然

在我的工作中，经常需要将一个表格里的内容汇总到另一个表格（总表）里去，例如下图（非本人工作中的例子）中，我们需要将F列中的数学成绩填入到C列中去，两列的人名并不一一对应：

这样的要求在工作中是家常便饭

接到这个任务，我是这样做的：用A列的人名到E列中去搜，搜到过后再Copy对应的数学成绩到C列。

你是不是曾经这样干过？

我就这样逐条查找然后复制粘贴，每次收到类似任务都花了不少的时间。最近因为这个事情已经加了不少班，有时候收到的表格上百上千条数据，真是复制粘贴到想吐。可是我花费几个小时才搞定的事情，我老板用下面的方法30s就搞定了：

这就是菜鸟与高手的距离吧

我那时真心觉得，我的老板简直是太厉害了。我后面在学习的过程中对这个函数越来越钟爱，现在几乎是每天不用它心里都痒痒。怪不得这个函数曾被人戏称为“大众情人”。还是上面的例子，我现在简单地说一下这个函数：Vlookup共有4个参数，也就是说我们需要告诉4个事情，它才能把相应的结果告诉我们。那么我们要告诉给Vlookup哪4件事情呢？

1.lookup value：我们用什么去查找？例如我们需要聂倩的数学成绩，因此我们可以通过“聂倩”去查找；

2.Table Array:到哪里去查找？我们要知道数学成绩，数学成绩在哪个表，我们就去哪里找。因此我们在E列和F列中去找。

3.Col-index-column：你想要返回哪一列的值？我们通过“聂倩”在E列中去查找，知道了聂倩在E列中的位置，但是我们得到的是聂倩的数学成绩，因此我们就要告诉Excel，数学成绩在“聂倩”这个右边的第几列。现在我们从聂倩（从左）数到数学成绩（往右），1，2。数学成绩就在第2列，因此第三个参数为2。

4.lookup range：你的查找方式是什么？精确查找（E列和F列构成的区域中一定有一个名字叫聂倩，Excel才会返回正确结果）还是近似查找（如果E列或者F列中没有叫聂倩的，但有一个叫聂小倩的，也可以被找出来）。到底是精确查找还是近似查找，我们用0和1来表示，0就是精确，1就是近似。此例中为精确匹配。

此栗子的公式为“=vlookup($A2,$E$2

F$27,2,0)"

你看明白了吗？

关于单元格的引用的技巧

单元格引用有相对引用、绝对应用以及混合引用。所谓引用，指的是在公式的拖拽中，单元格的位置是否变化。

1.相对引用

一句话概括，引用单元格的地址可能会发生变动。可能大家不理解，其实就是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。在默认的情况下，新公式使用的是相对引用。
例如，B2单元格公式为=A1，将B2单元格的相对引用复制到B3，则会自动从=A1调整为=A2。
2.绝对引用
引用的单元格地址不可能会发生变动。也就是说，总是在指定位置引用单元格，如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整。
例如，将B2单元格的绝对引用复制到B3，那么两个单元格都是$A$1.
3.混合引用
分为列绝对，行相对和行绝对，列相对这两种情况。
列绝对，行相对：复制公式时,列标不会发生变化,行号会发生变化,单元格地址的列标前添加$符号,如$A1,$C10,$B1:$B4。
行绝对，列相对：复制公式时,行号不会发生变化,列标会发生变化,单元格地址的行号前添加$符号,如A$1,C$10,B$1:B$4。

锁不锁定，美元说了算

"$"在行号前，行不变，在列号前，列不变。可自己输入，也可按住选中单元格名称后按F4调整。

精确查找的技巧

在V LOOKUP函数里，通常lookup value为混合引用的固定列，table array是固定的。col-index-column，要么为常量，要么使用match函数或者其他函数确定；第四个参数为0，即为精确查找，lookup value必须在查找范围内必须存在。

1.请从表3中查询内容并填充到表1的黄色单元格；在写公式的话，要充分考虑公式的复制问题。

=VLOOKUP($B8,$L$16:$Q$49,MATCH(C$7,$L$15:$Q$15,0),0)

1）lookup value:C827的值都是通过B列的值查找出来的，因此当公式复制时，列不能动
2）Table Array：查找的值永远都在表3里，因此，table array需要完全固定；
3）Col-index-column:需要填充的列，通过match函数来确定，match函数是专门用来确定某个单元格在某一列或者行的中位置的函数，这个函数有3个函数，（查找值，查找范围，精确还是近似），本例中，需要知道的是C7这个标题在L15：Q15标题行中从左至右数是第几个。
4）第四个参数为“0”时，表示是精确查找，即lookup value在查找范围内必须要存在。
请看操作：