次のセルがあります:
Number Band Color
1 10 A A1
2 20 A A1
3 30 A A1
4 40 B A1
5 50 B A2
6 60 C B1
7 70 C B1
8 80 A B1
9 90 A A1
10 100 B A1
次のようにデータの「範囲」/概要を作成したいと思います。
Start End Band Color
10 30 A A1
40 40 B A1
50 50 B A2
60 70 C B1
これを手作業で行うこともできますが、データは数千のセルに及ぶため、面倒に思えます。これを容易にする組み込み関数はありますか?
答え1
これを 2 つのヘルパー列で実行できます。次の数式をそれぞれ D2 と E2 に入力します。
=IF(OR(B2<>B1,C2<>C1),A2,"")
=IF(OR(B2<>B3,C2<>C3),A2,"")
次に、データセットの長さをコピーしました。
これにより、SMALL() 式を使用して開始と終了を取得できるリストが作成されました。
G2には以下を入力します:
=IFERROR(SMALL(D:D,ROW(1:1)),"")
次に、空白のセルができるまで、1 列ずつコピーしました。
次に、INDEX/MATCH でそれらの番号を使用して、他の情報を返します。
I2には以下を入力します:
=IF($G2<>"",INDEX(B:B,MATCH($G2,$A:$A,0)),"")
1 列分コピーして、空白になるまで下にコピーしました。
VLOOKUP でも同じことができます:
=VLOOKUP($G2,$A:$C,COLUMN(B:B),FALSE)
答え2
質問には、パターンと使用したいツールの両方について、もう少し説明していただけますか。「関数」とおっしゃっている場合、VBA コードではなく、シートの数式が必要であると想定します。各バンド/カラー ペアの最初のインスタンスのみを探していますか、それとも、連続して一致するバンドとカラー セットをずっと探していますか。後者の場合、テーブルには実際には次の 3 つの行が含まれます。
Start End Band Color
10 30 A A1
40 40 B A1
50 50 B A2
60 70 C B1
80 80 A B1
90 90 A A1
100 100 B A1
この長い表が必要な場合は、2 つの新しい行 (このシート内または新しいシート内) を作成し、A1 に「Number」があると仮定して、次のコードを使用します。
セルD2=IF(NOT(AND($B1=$B2,$C1=$C2)),IFERROR(MAX(INDIRECT("D1:D"&ROW(D2)-1))+1,1),0)
セルE2=IF(NOT(AND($B2=$B3,$C2=$C3)),IFERROR(MAX(INDIRECT("E1:E"&ROW(E2)-1))+1,1),0)
コードをテーブルの列の長さ全体まで拡張します。結果は次のようになります。
Number Band Color Start End
10 A A1 1 0
20 A A1 0 0
30 A A1 0 1
40 B A1 2 2
50 B A2 3 3
60 C B1 4 0
70 C B1 0 4
80 A B1 5 5
90 A A1 6 6
100 B A1 7 7
ここから、各セットの番号 (1、2、3、...) を調べて行番号を見つけ、必要な情報を取得します。開始/終了列を最初に配置すると、vlookupこれに使用できます。
各ペアの最初の出現のみが必要な場合 (しかし、なぜ行 8 が省略されているのでしょうか?)、もう少しロジックが必要になります。追加の列が必要になる場合がありますが、追加のロジックはかなり単純になります。