辨識一個儲存格中序列中的間隙 - 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 列的值中減去它，然後使用結果。

（基本上是這樣的（有些特殊情況不受手邊的「間隙問題」影響））$C1-Right($A1;2)&" - "&$C1：）

但我的方法（顯然）無法處理第 2 行和第 3 行中的差距，我想知道是否有辦法解決我的問題。

PS 我不確定該給這個問題取什麼標題，所以如果它有誤導性，我深表歉意。

Answer 1

您的問題可以透過使用 TREND 函數來解決：

1. 取得 A 列中的第一個資料樣本，您需要建立第一個序列從 1 到 13和第二次從 1 到 20。
2. 在 Cell 中寫入此數組公式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. 在Cell中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. 根據需要調整公式中的儲存格引用。