Excel动态数组公式应用——拆分字符串

請你好好放低

如果不着急要拆分字符串，可以等Excel推出TEXTSPLIT函数（现在这个函数在Insider Beta）。如果现在就想要这个功能，你的Excel必须是正版的Microsoft Office 365。不想看过程的话，可以直接跳到“具体操作”部分。

目前拆分字符串的方法

相比于其他编程语言，拆分字符串在Excel一直是一个不太容易的操作。例如，我要拆分一下字符串（分隔符为 , ):

在没有动态数组公式以前，有以下方式，但都有不同程度的缺点：

FIND查找分隔符+MID提取子字符串

优点：纯公式，如果输入值改变，输出值自动更新。
缺点：如果有若干个分隔符，公式将会很难看懂。如果有未知数量的分隔符，就不能用FIND+MID去实现这个功能。

VBA定义新公式

优点：纯公式，如果输入值改变，输出值自动更新。
缺点：VBA无法输出动态的数组，所以每个子字符串要单独提取。

文本分列向导功能：

优点：所有字符串，就算不知道有多少个，都能一次性提取。
缺点：不是公式。使用这个功能拆分子字符串后，输出结果是硬编码。如果输入值改变了，要重新运行该功能。

Google Sheets 的 SPLIT 函数

我们看看友商是怎么解决这个难题的：Google Sheets，国外Excel的免费替代品，有SPLIT函数，十分好用。

可以看到，Google Sheets和常用的编程语言一样，用SPLIT就可以拆分字符串，所有子字符串，都能拆分到不同的单元格。

可惜的是，Excel没有类似于SPLIT函数，不过可以自己创造一个，并将函数储存到名称管理器，方便以后运用。

实现方法1

总的来说，第一个方法就是把一个字符串拆分成字符数组，然后遍历数组里面的每一个字符，根据字符是否等于分隔符，重新把字符数组重新合并成想要的的子字符串。

用Excel现有的动态数组公式要分几步：

把一个字符串分成单个字符的数组。这里要用到Excel里面两个动态数组公式： MAP 和 SEQUENCE 。如果输入是 [a,bc,def] 的话，输出就是 [a][,][b][c][,][d][e][f] 。（这里，每一个 [ ] 就是一个单元格。）
遍历第一步的字符数组，根据分隔符累计字符串。这里要用到动态数组公式 SCAN 。例如， [a][,][b][c][,][d][e][f] ，就会变成 [a][a,][b][bc][bc,][d][de][def] 。（可以看到，每遇到一个 , ，就会重新累积字符。
把带 , 结尾的字符和最后一个字符保留下来，清空其他单元格。例如，上一步的结果就会变成 [][a][][][bc][][][def] 。
用 FILTER 来提取非空的子字符串。例如，上一步的结果就会变成 [a][bc][def] 。

我们可以把这几步合并成一个 LAMBDA 函数，并储存起来。

实现方法2

实现拆分字符串还有另外一种思路：直接找出分隔符的位置，然后根据分割符的位置，提取每一个想要的子字符串。

也需要几步方法实现：

查找分隔符。可以用 FIND + MAP + UNIQUE 找出分隔符位置。例如输入值是 a,bc,def ，分隔符位置就是 2,5,9 (注意，这里最后一个位置9是故意加进去的，这会使得之后的操作更方便。）
知道分隔符位置后，就可以知道子字符串的起始位置。同样一个例子，子字符串起始位置是 1,3,6 。
知道子字符串起始位置和分隔符位置后，就可以用 MID 提取子字符串了。

最后把这几步合并成一个 LAMBDA ，又保存起来。

这是具体的实现方法：

这里，第1步所用的函数 MAP + FIND 的时间复杂度是 \Theta(n^2) ，但是我在合并的时候，运用了动态规划的算法思维，运用了 SCAN + FIND ，所以最终的时间复杂度和方法1是一致的，都是 \Theta(n) 。虽然方法2在实现中要处理很多细节，但是总的结果是比方法1更简洁。

具体操作

在 Excel for Windows 中，选择“ 公式 ”>“ 名称管理器”。 在 Excel for Mac 中，选择“ 公式 ”>“ 定义名称 ”。
选择“ 新建 ”，然后在“ 新建名称 ”对话框中输入信息:
在名称中，输入 TEXTSPLIT 。再引用中，拷贝粘贴以下公式，然后按确认。

=LAMBDA(value,delimiter,
  LAMBDA(dlm_loc_arr,
    TRANSPOSE(MAP(
      MAP(SEQUENCE(ROWS(dlm_loc_arr)),LAMBDA(i,IF(i=1,1,LEN(delimiter)+INDEX(dlm_loc_arr,i-1)))),
      dlm_loc_arr,
      LAMBDA(start_num,dlm_loc,MID(value,start_num,dlm_loc-start_num))
    LAMBDA(nxt_dlm_loc,