1 つのセル内のシーケンス内のギャップを識別する - 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 のセルの最後の 2 桁をチェックし、その行の列 C の値から減算し、その結果を列 B に入力することによってのみ機能します。

(基本的には次のようになります (「ギャップ問題」の影響を受けない特殊なケースもあります)): $C1-Right($A1;2)&" - "&$C1)

しかし、私のアプローチでは（明らかに）2行目と3行目のようなギャップを処理できないので、問題を解決する方法があるかどうかを知りたいです。

追伸：この問題にどのようなタイトルを付ければよいか分からなかったので、誤解を招くようなタイトルであればお詫び申し上げます。

Answer 1

TREND 関数を使用すると、問題を解決できます。

1. 列Aの最初のデータサンプルを取り、1から13までの1番目のシーケンスそして1から20までの2番目。
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 番目のデータ サンプルでは、​​2 つのシリーズを作成する必要があります。

1- 2 to 9.

2- 1 to 10.

3 番目のデータ サンプルには 3 つのシリーズが必要です。

1- 1 to 4.

2- 6 to 11.

3- 1 to 11.

1. 他のデータ サンプルに対して上記の手順を繰り返します。
2. 必要に応じて数式内のセル参照を調整します。