微精选

欢迎转发和点一下“在看”，文末留言互动!

置顶公众号或设为星标及时接收更新不迷路

朋友们好，今天来和大家分享一道跨表求和的题目。这道题目是一种比较常见的题目类型，在很多场合下都有实际的应用。

题目是这样的：

题目要求我们根据不同的年限来加总求和总的销售量。其中年限是可以任意变动的变量。

这道题该怎么做？要跨表求和，就要使用INDIRECT函数。可是又怎样实现动态年份的呢？今天的这篇帖子，将向大家介绍两种方法，每种方法的构思都非常巧妙！

在单元格D2中输入下列公式，三键确认即可。

=SUM(IFERROR(SUBTOTAL(9,INDIRECT(ROW($2020:$2026)&"年"&"!C3:N12"))*FREQUENCY(IFERROR(--MID(SUBSTITUTE($B$2,"+","s"),ROW($1:$25),4),9^9),ROW($2020:$2026)),0))

这条公式分为两部分，第一部分对不同工作表求和，第二部分确定年份，最后SUM函数汇总。

在源数据中需要汇总的区域都是单元格区域C2:N12。这样就可以借助INDIRECT函数轻松实现多工作表的汇总了。

ROW($2020:$2026)

利用ROW函数构建一个数列。在这个数列中，最小的值要小于等于题目中的最小年份，最大的值要大于等于最大年份。

INDIRECT(ROW($2020:$2026)&"年"&"!C3:N12")

这部分利用INDIRECT函数引用了不同工作表中相同的单元格区域C3:N12。

SUBTOTAL(9,INDIRECT(ROW($2020:$2026)&"年"&"!C3:N12"))

利用SUBTOTAL函数来对不同的区域进行汇总求和。其结果为{#REF!;#REF!;#REF!;9387;14952;20927;21468}，正是每个工作表的汇总数据。

这里不能使用SUM函数。因为INDIRECT函数的结果是三维的，SUM函数不支持三维运算，结果会出错。

这里的错误值是由于工作簿中没有2020年-2022年的工作表。这个错误值放到后面处理。

到此为止，第一部分就处理完成了。下面来做第二部分。

SUBSTITUTE($B$2,"+","s")

将源数据中加号“+”替换为任意一个西文字符。原因加号是一个计算符号，它不改变字符的“文本/数值”属性，在后面提取字符时就会出错了。

MID(SUBSTITUTE($B$2,"+","s"),ROW($1:$25),4)

利用MID函数，从每一个字符开始都提取长度为4的字符。因为年份数据的长度是4。

IFERROR(--MID(SUBSTITUTE($B$2,"+","s"),ROW($1:$25),4),9^9)

减负运算后利用IFERROR函数将错误值变成一个极大值9^9。这部分的结果是{2023;387420489;…;2025;…;387420489;2026;…;387420489}

FREQUENCY(IFERROR(--MID(SUBSTITUTE($B$2,"+","s"),ROW($1:$25),4),9^9),ROW($2020:$2026))

接下来就可以利用FREQUENCY函数来计频了。这部分的结果是{0;0;0;1;0;1;1;22}。在这个结果中，数字“1”分别对应的是“2023”、“2025”和“2026”年。

SUBTOTAL(9,INDIRECT(ROW($2020:$2026)&"年"&"!C3:N12"))*FREQUENCY(IFERROR(--MID(SUBSTITUTE($B$2,"+","s"),ROW($1:$25),4),9^9),ROW($2020:$2026))

第一、第二部分相乘，结果是{#REF!;#REF!;#REF!;9387;0;20927;21468;#N/A}。你看，大于0的部分就是2023年、2025年和2026年的汇总数据了。

SUM(IFERROR(SUBTOTAL(9,INDIRECT(ROW($2020:$2026)&"年"&"!C3:N12"))*FREQUENCY(IFERROR(--MID(SUBSTITUTE($B$2,"+","s"),ROW($1:$25),4),9^9),ROW($2020:$2026)),0))

最后屏蔽错误，SUM函数汇总。完美收工！

上面的公式主要利用的FREQUENCY函数，而下面将要介绍的则是利用的FILTERXML函数。其过程更加简洁明了。

在单元格D2中输入下列公式，三键确认即可。

=SUM(SUBTOTAL(9,INDIRECT(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($B$2,"总销量",""),"+","</b><b>")&"</b></a>","a/b")&"!C3:N12")))

既然年份是变量，那我们干脆就先把年份提取出来，再利用INDIRECT函数取引用年份的工作表区域，最后来求和。这是这条公式的解题思路。

SUBSTITUTE($B$2,"总销量","")

将无用的数据替换为空值。

SUBSTITUTE(SUBSTITUTE($B$2,"总销量",""),"+","</b><b>")

利用SUBSTITUTE函数将加号替换为“</b><b>”。

FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($B$2,"总销量",""),"+","</b><b>")&"</b></a>","a/b")

利用FILTERXML函数提取年份信息。结果为{“2023年“;”2025年“;”2026年“}。

INDIRECT(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($B$2,"总销量",""),"+","</b><b>")&"</b></a>","a/b")&"!C3:N12")

接下来就可以再次利用INDIRECT函数了。

SUBTOTAL(9,INDIRECT(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($B$2,"总销量",""),"+","</b><b>")&"</b></a>","a/b")&"!C3:N12"))

SUBTOTAL函数求和的结果是{9387;20927;21468}

SUM(SUBTOTAL(9,INDIRECT(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($B$2,"总销量",""),"+","</b><b>")&"</b></a>","a/b")&"!C3:N12")))

最后由SUM函数完成汇总。

本期内容练习文件提取方式：

通过网盘分享的文件：跨指定多表求和.xlsx

链接: https://pan.baidu.com/s/1HGuIzkZuhLhJil0JWBfw5Q?pwd=3rkk 提取码: 3rkk