如何在Excel 2019中通过公式或函数实现键值顺序同步调整

适用工具 #

Excel 365/2021、WPS表格、Google Sheets均可使用，低版本Excel可参考文末VBA方案。

前置准备 #

首先预先在固定区域存储所有键值的基准对应关系，例如：

• D列存储所有唯一键文本
• E列存储每个键对应的固定数值
  该基准区域不要随意修改，作为匹配的固定依据

核心公式 #

假设你可调整顺序的键单元格为A1（键之间用逗号分隔，可根据实际替换为顿号、换行符等），要自动同步数值的单元格为B1，直接在B1输入以下公式即可：

=TEXTJOIN(",",TRUE,XLOOKUP(TEXTSPLIT(A1,","),D:D,E:E,"未匹配"))

公式逻辑说明 #

• TEXTSPLIT(A1,",")：将A1单元格内的多个键按分隔符拆分为独立的文本数组
• XLOOKUP(拆分后的键数组, 基准键列D, 基准数值列E, "未匹配")：按顺序逐个匹配每个键对应的固定数值，未找到对应关系时返回「未匹配」提示
• TEXTJOIN(",",TRUE, 匹配得到的数值数组)：将所有数值按键的新顺序用相同分隔符拼接为单个单元格内容，自动忽略空值

低版本Excel兼容方案 #

如果使用没有TEXTSPLIT、XLOOKUP函数的低版本Excel，可使用VBA自定义函数实现：

1. 按下Alt+F11打开VBA编辑器，插入新模块
2. 粘贴以下代码：

Function SyncValue(keyCell As Range, delimiter As String, keyRange As Range, valueRange As Range) As String
    Dim keyArr As Variant, i As Long, res As String
    keyArr = Split(keyCell.Value, delimiter)
    res = ""
    For i = LBound(keyArr) To UBound(keyArr)
        For j = 1 To keyRange.Rows.Count
            If keyRange.Cells(j, 1).Value = keyArr(i) Then
                res = res & valueRange.Cells(j, 1).Value & delimiter
                Exit For
            End If
        Next j
    Next i
    If res <> "" Then SyncValue = Left(res, Len(res) - Len(delimiter))
End Function

3. 回到表格在B1输入公式即可使用：=SyncValue(A1,",",D:D,E:E)

注意事项 #

• 键之间的分隔符要保持统一，若使用顿号分隔则把公式中的","替换为"、"，若使用换行符则替换为CHAR(10)，同时开启单元格的自动换行选项
• 基准键列请勿录入重复键，否则会优先返回第一个匹配到的数值

内容的提问来源于stack exchange，提问作者Parabol