公众号后台（不是评论区）回复关键字 Excel实现动态引用_跨境电商结算等 即可获取下载数链接（无密码，后台自动回复的链接复制到浏览器）（长按关键字复制粘贴即可，不必手写）

同一份数据往往需要填在不同的表里面，因为不同的人不同的用处，口径也不一样；

而且给出的表的模板，格式十分复杂，信息杂乱，有些根本不让你动其他东西，就把她/他要的数填上。

面对这些需求，抱怨和叹气没有用的，要么 猪八戒摔耙子——不伺候你这猴儿，要么提升单兵作战能力，面对任何场景都能有单刀赴会的勇气和实力，从从容容，游刃有余。

以跨境电商行业为例，财务数据中的结算，往往会有很多个收入费用类型，接下来用Excel实现动态引用（此处不用Python，不是因为做不到，而是不值得）。

先看基础部分，再看拓展部分，最后实现一个公式全部填充。

原始数据（或者是Python清洗后的）如下：

需要的结果格式：

此处简单模拟：只是列的顺序变了，没有增加小计合计等，不过思路一致。

以表中数据为例，结果中的 Principal 和 PromotionDiscount 放在最前面

以下步骤在Excel中实现，WPS理论上也可以，若存在兼容性问题等，先升级到最新版（64位版本），无法解决的话，评论区或私信留言。

步骤1：把原始数据变成超级表

规范后（规范见如何精进Excel并且为编程做准备）再变成超级表(光标在表格区域内有数据的单元格，按下CTRL 和 T)

弹出如下提示：

点击 确定 ，就会变成超级表，此时可以对表进行调整，如颜色等，也可以默认。

重要的是设置表的名字，默认是表1，表2等，要改成有意义的，比如原始数据，如下：

超级表的作用就是可以用 表的名字 + 列的名字 来引用，不需要考虑单元格

比如 原始数据[国家] 表示的是国家这一整列。

步骤2：找到结果表中每个列在原始数据中的列位置并返回数据

举例：Principal 这个列，在原始数据中是 T列，即第20列。

用到的函数 是 Xmatch , 有 4 个参数 ：

lookup_value :要查找谁，必须填写 ，lookup_array : 在哪里查，行列都行，必须填写 ，match_mode : 匹配模式，非必须，默认是精确匹配，search_mode : 搜索模式，非必须，默认是从头到尾。

2.1 要在结果中找到 Principal 在原始数据中的位置 ，常见的做法是

= XMATCH(结果数据!B1,原始数据!1:1)

如果在结果表中写公式，不需要 结果数据! 这个前缀，直接是 

= XMATCH(B1,原始数据!1:1)

我们要做的是把 原始数据!1:1 换成 原始数据[#标题] ，即

= XMATCH(B1,原始数据[#标题])

2.2 接下来用 index 返回数据

index 的参数很简单数据范围 ，返回哪行 ，返回哪里

此处我们只需要返回列，所以第2个参数为空，第三个参数是动态算出来的，即

=XMATCH(B1,原始数据[#标题])在原始数据中写，就要带前缀 结果数据! 在结果表中不需要

组合起来就是 

=INDEX(    原始数据,    ,    XMATCH(B1,原始数据[#标题]))此处不要遗漏第 2 个逗号。

这个公式可以把一整列都返回，可以看出 B1 有没有前缀都能返回正确结果，看你喜欢哪个。

步骤3：拖拽公式得到全部的

拓展1：Xlookup 代替 index

=XLOOKUP(  $B$1:$AH$1,  原始数据[#标题],  FILTER(原始数据,原始数据[国家]=A2))

FILTER 公式中指定 返回哪行 ，即 等于左侧国家的对应的行

之后拖拽公式

拓展2 ：结果表中国家的顺序与原始数据中不同

如果用Xlookup版本 ，不需要改动。如果用index 版本，先用sortby排序，再返回

=SORTBY(    INDEX(原始数据,,XMATCH(B1,原始数据[#标题])),    $A$2:$A$20)

拓展3 ：一个公式返回全部数据

结果表中有没有国家这列都不影响，假设结果表什么都没有

=INDEX(    原始数据,     SEQUENCE(ROWS(原始数据)),     XMATCH($A$1:$AH$1, 原始数据[#标题]))

秘诀在于 

SEQUENCE(ROWS(原始数据))

ROWS(原始数据) 返回一个数字 ，表示有多少行数据，此例中是19 （标题行不算，超级表只认数据行）

SEQUENCE(19) 会生成一个 1列19行的数字，从 1 到 19

INDEX 就会自动把 19 行数据全部返回。

拓展4：结果表中有原始数据不存在的列

此时由于找不到结果，会返回错误

在外层嵌套 IFERROR

=IFERROR(    INDEX(        原始数据,         SEQUENCE(ROWS(原始数据)),         XMATCH($A$1:$AH$1, 原始数据[#标题])    ),    "")IFERROR 第 2 个参数 要么是空，要么为 0 ，不建议写文字

拓展5 ：结果表中列数增加

之前的写法是指定了 $A$1:$AH$1 ，如果结果表列数超过了 AH 呢？

此时需要动态计算有哪些列

起始列是确定的，主要确认最后一列是哪个

（在结果表中写）

=INDEX(1:1, COUNTA(1:1))1：1 表示标题行（第 1 行）COUNTA(1:1) 表示第 1 行一共有多少个标题，这个数字也是最后一列所在的列数。

这里必须注意 INDEX 的一个特性 ->

INDEX 有两种模式：值模式 vs 引用模式

通常用 INDEX 是取值的，比如：

=INDEX(A1:A10, 3)返回的是 A3 的值。

但当 INDEX 的第一个参数是单元格区域引用（如 1:1），并且它处于需要单元格引用的上下文中（比如放在冒号 : 的一侧）时，Excel 会把它当作引用模式，返回一个真正的单元格引用，而不是那个单元格的值。

所以：

A1:INDEX(1:1, COUNTA(1:1))

A1 是单元格引用。

INDEX(1:1, COUNTA(1:1)) 因为处于 : 的右侧，被视为需要返回一个引用，于是返回了第一行中第 COUNTA(...) 列的单元格引用。

Excel 将这两个引用拼接成一个从 A1 到那个单元格的矩形区域（这里是一行连续列）。

如果单独在一个单元格里写：

=INDEX(1:1, COUNTA(1:1))

它会返回那个单元格显示的文字，因为此时公式的上下文要求一个值。

但在 A1:INDEX(...) 中，: 运算符强制要求两边的操作数是引用，所以 Excel 会把 INDEX 当作引用求值，返回单元格位置，而不是它的内容。

现在有 2 个选择，

(1) 写一个大公式组合 

=IFERROR(    INDEX(        原始数据,        SEQUENCE(ROWS(原始数据)),        XMATCH(A1:INDEX(1:1,COUNTA(1:1)),原始数据[#标题])    ),"")

(2) 启用 let

=LET(    标题, A1:INDEX(1:1, COUNTA(1:1)),    INDEX(        原始数据,         SEQUENCE(ROWS(原始数据)),         XMATCH(标题, 原始数据[#标题])    ))

考虑到 兼容性（比如发给别人，她/他用的是 WPS ,或者Excel 版本很低）和 认知负担 ，推荐暂时不用 let。

当你觉得这些函数没有挑战性或者为了提升性能等，再去研究 let ，本身不难 ，不过有精力不如去学编程，数据库，收益极高，过渡到编程可以看如何精进Excel并且为编程做准备