错位引用用得好，函数神技有木有？！

HI，大家好，我是星光。

很多年以前，我还未成年，看过一部很好看的电视剧，叫做《上错花轿嫁对郎》，讲的是两个性格迥异但都美丽的姑娘错上了彼此的花轿却嫁对了男人的故事——不知道有没有看过的朋友，看过的朋友文末点个赞，看看有多少青春期同龄人

今天给大家分享的表格技巧是错位引用，与上错花轿嫁对郎有异曲同工之妙。

多了不举，我就举5个实战小例子。

  1

 多列数据转指定列

如下图所示，有4列数据，需要转换成1列。

这事解决方法有很多，感兴趣的朋友可以参考【往期推文】，但最简单的方法莫过于用一个等号直接搞定。

点击即可播放...▼

也许有朋友觉得多列转单列没啥实用价值那么多列转N列呢？

如下图所示的数据，共有6列，需要按姓名和成绩转换为2列。

还是一个等号即搞定👇

点击即可播放...▼

这里就用了错位引用。当公式向右复制时，不断读取同行右侧的数据；当公式向下复制时，又不断读取上一行公式的引用结果；于是就将右侧的数据不断累加引用到当前列。

你品品，是不是很奇妙？

  2

 为合并单元格添加序号

说个简单的吧。

如下图所示的表格，需要在A列大小不一的合并单元格内添加递增序列号。

选中A2:A12单元格区域，输入以下公式，按<Ctrl+Enter>批量填充即可。

=MAX(A$1:A1)+1

公式引用了公式所在单元格之上的区域，其中第1个A1行绝对引用，第2个A1完全相对引用。

当公式写在A2单元格时，MAX函数统计范围是A$1:A1，计算最大值后加1；当公式填充到A5单元格时，统计范围变为A$1:A4，计算最大值后加1…以此实现序号递增的效果。

短视频演示如下：

点击即可播放...▼

  3

 最后的工资之和

害，朋友，SUMIF函数会用吧？

如上图所示，需要统计A2:F6区域每个人最后的工资之和（也就是标记橙色的单元格之和）

参考公式如下：

=SUMIF(B3:F7,'',B2:F6)

公式还是使用了错位引用。SUMIF的条件区域是B3:F7，求和区域是B2:F6，两个区域刚好错一行。当条件区域内单元格为空时，则向上一个单元格求和，比如B6单元格为空，则取B5单元格的值；最后汇总求和即为结果。

害，朋友，SUMIF函数真会用吧？

  4

 合并单元格求和

害，朋友，SUM函数会用吧？

如下图所示，A:C是数据源，需要在D列大小不一的合并单元格内统计每个人的成绩总分。

选中D2:D10单元格区域后，输入以下公式，按快捷键<Ctrl+Enter>批量填充公式。

=SUM(C2:C$11)-SUM(D3:D$11)

短视频演示效果如下：

点击即可播放...▼

解释一下公式的计算过程。

如上图所示，E列展示的是D2:D10每个单元格的公式。由于D3:D4是被合并单元格，不保留值或公式，也就不存在公式。

求和公式有两部分组成。

第1部分：SUM(C2:C$11)

当公式存在于D2单元格时，它计算的是C2:C11所有人的总分。

第2部分：SUM(D3:D$11)

由于避开了D2单元格的求和公式，同时D3:D4是被合并单元格，不存在值或公式，因此它实际上计算的是D6:D11的单元格区域，也就是除了看见星光以外所有人的总分，两者相减，即是看见星光的总分。

当公式填充到D5时，演变为了=SUM(C5:C$11)-SUM(D6:D$11)，等于用C5:C11的总分，减去D7:D11的总分，因此得到第2个人Excel星球的总分。

摊手，是不是绕晕了？

没事，还有更绕的👇

  5

 按指定次数重复数据

害，朋友，VLOOKUP函数会用吧？

如下图所示，需要将B列的项目按C列的指定次数重复，比如把公众号Excel星球重复3次。

E列是模拟结果。

VLOOKUP解法如短视频所示：

点击即可播放...▼

……

打个响指，差不多了，今天分享的内容就到这里。有啥问题可以在VIP会员群中提问交流↓👇↓ 挥挥手咱们明天再见。