在单元格区域A1:A6中，有一些数据，一些是单个数字，一些是由连字符分隔的一组数字，例如，13-16表示13、14、15和16。现在，有必要将这些数据拆分并依次放入d列，如下图1所示。

图1

先不看答案，自己试试。

公式

在单元格D1中输入数组公式：

=IF(ROWS($D$1:$D1)SUM(倒数第一个1)、""、SMALL(IF(第一个转置(ROW(INDIRECT)(“1:”MAX(倒数第一个)1))-1)last，""，第一个转置(ROW(INDIRECT)(“1:”MAX(倒数第一个)1))-1))、ROWS($D$1:$D1))

向下拖动，直到出现一个空单元格。

公式分析

公式中的第一个和最后一个是定义的两个名称。

姓名：第一

参考位置：=左侧(替代物($ 1: $ a $ a $ 6，"-"，Rept("，"，5))，5)。

姓名：姓氏

参考位置：=右侧(替代物($ 1: $ a $ a $ 6，"-"，Rept("，"，5))，5)。

让我们看看这两个名字是如何起作用的。名字：first=left(替换为$ a $1: $ a $6，"-"，Rept("，"，5))，5)。

转换为：=left(替换为({ " 1-2 ")；"4-6";"9";"10-11";"13-16";“21”}、“-”、“REPT(”、“5”)、5)

转换为：=left(替换为({ " 1-2 ")；"4-6";"9";"10-11";"13-16";"21"},"-"," " ),5)

已转换为：=left({“1 2”；"4 6";"9";"10 11";"13 16";"21"},5)

结果是：={ " 1 "；"4 ";"9";"10 ";"13 ";"21"}

以上公式中的数字5是可选的，只要能保证数字被筛选出来。

第二个名字：姓氏。与上述原理相同，最终结果为：={“2”；" 6";"9";" 11";" 16";"21"}

看看公式中IF语句的第一部分：IF(rows($ d $ 1: $ D1)sum(last-first 1)。"

将公式对应的名称位置替换为定义的名称：if(rows($ d $ 1: $ D1)sum({ " 2 "；" 6";"9";" 11";" 16";"21"}-{"1 ";"4 ";"9";"10 ";"13 ";"21"} 1),""

get:IF(ROWS($ D $ 1: $ D1)SUM({ 2；3;1;2;4;1}),""

请注意，这里不需要对两个数组使用TRIM函数。Excel会忽略数字前后的空格，并在执行数学减法时强制将其转换为数学运算。

这样我们就可以看到上面结果数组中单元格A1:A6中每个数据对应要返回的数字个数，例如“1-2”将返回2个值，“4-6”将返回3个值，以此类推。因此，数组的总和就是我们想要返回的总数：

IF(ROWS(D $ 1: $ D1)13，“”

因此，当公式被向下复制时，超过13行将返回空值。

让我们看看公式的主要部分：

SMALL(IF(第一次转置(ROW(间接(“1:”MAX(倒数第一)1))-1)last，“”，第一次转置(ROW(间接(“1:”MAX(倒数第一)1))-1)))、ROW(D $ 1: $ D1))

可以看到，下面的部分出现了两次：第一次转置(行(间接(“1:”max(倒数第一)1))-1)。

其中，后进先出之前已经说过，生成数组：{ 1；2;0;1;3;0}

最大值为3，然后加1得到4，即MAX(倒数第一)1。

结果是4。实际上，这个值表示我们从A1:A6中的字符串中范围最大的字符串返回的数字的数量。

这样，上面的一些公式就被转换成：第一次转置(row(间接(“1:”4))-1)。

转换为：第一个转置({ 1；2;3;4}-1)

Get: first {0，1，2，3}。

下面是公式的关键技巧：首先形成单行数组，由0到3(即数值范围的最大区间)组成，然后转置为单行数组{0，1，2，3}。然后，将该数组与第一个({“1”；"4 ";"9";"10 ";"13 ";"21"}).因为添加的两个数组是正交的，所以一个6行1列的数组加上一个1行4列的数组会得到一个6行4列24值的数组。

这样，上面的一些公式就转换成：{“1”；"4 ";"9";"10 ";"13 ";"21"} {0,1,2,3}

结果为：{1，2，3，4；4,5,6,7;9,10,11,12;10,11,12,13;13,14,15,16;21,22,23,24}

这个数组包含我们想要的值，但它也包含一些我们不想要的值。实际上，生成4列数组的原因是为了确保可以添加足够多的整数，因为A1:A6中的最大区间范围是4个整数。

要去掉不必要的数值，只需将上述数组中的每个值与last生成的数组进行比较即可(last数组生成的值是A1:A6中每个数值范围的上限)。例如，对于上述数组中的第四行{10，11，12，13}，最后一个数组中的对应值是11，因此12和13被删除，只剩下10和11。公式的比较部分是：

IF(第一次转置(行(间接)(“1:”MAX(倒数第一个)1))-1)倒数第二个，“”

转换为：IF({1，2，3，4；4,5,6,7;9,10,11,12;10,11,12,13;13,14,15,16;21,22,23,24}{2;6;9;11;16;21},""

Excel比较同一行公式中生成的两个数组，例如左数组第二行的值{4，5，6，7}与右数组第二行的值6进行比较，左数组第五行的值{13，14，15，16}与右数组第五行的值16进行比较，等等。结果是：

如果({假，假，真，真；假，假，假，真；假，真，真，真；假，假，真，真；假，假，假，假；假，真，真，真}，”

根据以上推导，公式中的IF语句：

IF(第一次转置(ROW(INDIRECT)(“1:”MAX(倒数第一)1))-1最后，“”，第一次转置(ROW(INDIRECT)(“1:”MAX(倒数第一)1))-1))

可以更改为：

如果({假，假，真，真；假，假，假，真；假，真，真，真；假，假，真，真；假，假，假，假；假，真，真，真}，“，{1，2，3，4；4,5,6,7;9,10,11,12;10,11,12,13;13,14,15,16;21,22,23,24})

转换为：{1，2，""，"；4,5,6,"";9,"","","";10,11,"","";13,14,15,16;21,"","",""}

此外，在公式中：ROWS($ 1:D $ D1)。

当你抄下来的时候，你会得到像1，2，3…

总而言之，单元格D1中的原始公式：

=IF(ROWS($D$1:$D1)SUM(倒数第一个1)、""、SMALL(IF(第一个转置(ROW(INDIRECT)(“1:”MAX(倒数第一个)1))-1)last，""，第一个转置(ROW(INDIRECT)(“1:”MAX(倒数第一个)1))-1))、ROWS($D$1:$D1))

转换为：=if (113，“”，小({1，2，“”，“”；4,5,6,"";9,"","","";10,11,"","";13,14,15,16;21,"","",""}, 1))

结果是：1。

单元格D2中的公式转换为：=if (213，""，"，small ({1，2，""，"；4,5,6,"";9,"","","";10,11,"","";13,14,15,16;21,"","",""}, 2))

结果是：2。

单元格D3中的公式转换为：=if (313，""，"，small ({1，2，""，"；4,5,6,"";9,"","","";10,11,"","";13,14,15,16;21,"","",""}, 3))

结果是：4。

.等等。