识别一个单元格中序列中的间隙 - Excel

Question

我的 Excel 表中有一个项目列表，这些项目已编号，并附有总数，如下所示：

例子：

   | Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1  | 01 - 13              |         ?          | 20
2  | 02 - 09              |         ?          | 10
3  | 01 - 04, 06 - 11     |         ?          | 11

现在，我希望 B 列显示缺失的项目编号，如下所示：

   | Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1  | 01 - 13              | 14 - 20            | 20
2  | 02 - 09              | 01, 10             | 10
3  | 01 - 04, 06 - 11     | 05                 | 11

我已经设法为第 1 行的情况创建一个冗长的公式，但它只能通过检查 A 列中单元格的最后两位数字，从该行 C 列的值中减去它，然后用结果填充 B 列来起作用。

（基本上是这样的（有些特殊情况不受手头的“差距问题”的影响））$C1-Right($A1;2)&" - "&$C1：）

但是我的方法（显然）无法处理第 2 行和第 3 行中的间隙，我想知道是否有办法解决我的问题。

PS：我不确定该给这个问题起什么标题，所以如果它产生了误导，我深表歉意。

Answer 1

您的问题可以通过使用TREND函数来解决：

1. 取 A 列中的第一个数据样本，您需要创建第一个序列从 1 到 13和第 2 个从 1 到 20。
2. 在单元格中写入此数组公式，并以& 填充C1结束。Ctrl+Shift+Enter

{=TREND(A1:A13,B1:B13,B14:B20)}

1. 您可以从 获得系列14 to 20。

2. 输入D1此公式即可获得1st Cell value。 =INDEX(C1:C7,MATCH(TRUE,INDEX((C1:C7<>0),0),0))

3. 输入此公式D2可得到last Cell value。

=LOOKUP(2,1/(C1:C7<>""),C1:C7)

4. 在单元格中D4输入这个=D1&"-"&D2，你得到14-20。

笔记： 对于第二个数据样本，您需要创建 2 个系列。

1- 2 to 9.

2- 1 to 10.

第三个数据样本需要 3 个系列。

1- 1 to 4.

2- 6 to 11.

3- 1 to 11。

1. 对其他数据样本重复上述步骤。
2. 根据需要调整公式中的单元格引用。