ExcelでCDを入力して紐付いているNo.を抽出

質問

とある、チェックシートを作成しています。
Sheet1
過去に起きた事象にNo.を割り当て、それぞれの事象に共通する巡回時のポイント(着眼点)をCDで紐付けます。
カウント列は同じCDをカウントしています。
Sheet2
チェックシートに過去の事象をピックアップで紹介(抽出)します。
CDの列にに連続したCDを入力します。　
例) 1.1.1.2.2.2.3.3.3.4.4.4.5.5.5.1.1.1. ...
Sheet1からCDに紐ついたNo.を順番に抽出したいです。
・CDに紐付いているNo.が1つなら1つを連続で抽出
・CDに紐付いているNo.が2つなら2つを連続で抽出、3つ目は再び最初のNo.を抽出
・CDに紐付いているNo.が3つなら3つを連続で抽出
・CDに紐付いているNo.が3つ位上なら3つを連続で抽出、CD列に再び同じCDを入力したら4つ目のNo.を抽出

このように抽出したい場合、関数はどのようにすれば良いでしょうか？
No.をSheet1を参照して手打ちで入力する方法もありますが、関数で一括して抽出できる方法がないかと質問させて頂きました。

詳しい方いましたら、ご教授下さい。
宜しくお願い致します。

回答No.3

msMike

初歩的(?)かも知れませんが、…
Excel を長年使ってますが、CD という用語を知りませんでした。
CD の２文字は Compact Disk を想起しますが、貴方の CD は何の Acronym(頭字語)としてお使いなんですか？
それとも、間違った記号をExcelでも通じると勘違いされてシャーシャーと使っておられるとか？

回答No.2

HohoPapa

一般には、VBAで泥臭くコーディングする課題と思いますが
>関数はどのようにすれば良いでしょうか？
とのことなので、出来合いの関数を使う解を考えてみました。

ただし、それなりに作業列を使うこととなります。
なお、Sheet2での列番号、行番号がわからないので添付画像のレイアウトを前提としました。
さらに、CDは最大999まで、サンプル画像で示すブロックカウントは最大999までとしました。
これを超えるようならコメントしてください。

■Sheet1

F1:H1に　適当なタイトルを埋める
D2:G2に　1を埋める

H2に　　=TEXT(A2,"000")&TEXT(G2,"000")&TEXT(B2,"0")　
F3に　 =IF(MOD(B3,3)=1,1,IF(A2=A3,0,1))
G3に　　=SUMIFS($F$2:F3,$A$2:A3,A3)　を埋め
H3に　　=TEXT(A3,"000")&TEXT(G3,"000")&TEXT(B3,"0")
F3:H3を下方向に必要数複写

■Sheet2

E5:I5に　適当なタイトルを埋める
E6:H6に　1を埋める
I6に　　=TEXT(A6,"000")&TEXT(G6,"000")&TEXT(H6,"0")

E7に　　=IF(A6=A7,E6+1,1)
F7に　　=IF(A6=A7,0,1)
G7に　　=IF(A6>A7,G6+1,G6)
H7に　　=IF(A6=A7,H6+1,1)
I7に　　=TEXT(A7,"000")&TEXT(G7,"000")&TEXT(H7,"0")

E7:I7を下方向に必要数複写

B6に　　=INDEX(Sheet1!$C$2:$E$19,MATCH(I6,Sheet1!$H$2:$H$19,0),1)
B7に　　=IFERROR(INDEX(Sheet1!$C$2:$E$19,MATCH(I7,Sheet1!$H$2:$H$19,0),1),B6)
B8に　　=IFERROR(INDEX(Sheet1!$C$2:$E$19,MATCH(I8,Sheet1!$H$2:$H$19,0),1),B6)

C6に　　=INDEX(Sheet1!$C$2:$E$19,MATCH(I6,Sheet1!$H$2:$H$19,0),2)
C7に　　=IFERROR(INDEX(Sheet1!$C$2:$E$19,MATCH(I7,Sheet1!$H$2:$H$19,0),2),C6)
C8に　　=IFERROR(INDEX(Sheet1!$C$2:$E$19,MATCH(I8,Sheet1!$H$2:$H$19,0),2),C6)

D6に　　=INDEX(Sheet1!$C$2:$E$19,MATCH(I6,Sheet1!$H$2:$H$19,0),3)　　
D7に　　=IFERROR(INDEX(Sheet1!$C$2:$E$19,MATCH(I7,Sheet1!$H$2:$H$19,0),3),D6)
D8に　　=IFERROR(INDEX(Sheet1!$C$2:$E$19,MATCH(I8,Sheet1!$H$2:$H$19,0),3),D6)

B6:D8（つまり3行を選択して）、下方向に必要数複写

回答No.1

AIエージェント あい（@OKWAVE-AI）

回答できます。

ご質問の内容に基づいて、Sheet2においてCDに紐づいたNo.を連続で抽出する方法をご提案いたします。

まず、Sheet2のCD列において連続したCDを抽出するために、以下の手順で関数を使います。

1. B列（CD列）の1行目に以下の関数を入力します。
`=IF(B2=B1,A1,A1+1)`
2. 1行目のセルに関数が入力されたら、そのセルを選択し、コピーします。
3. B列のセルにカーソルを合わせ、貼り付けオプションを選択し「式を貼り付け」を選びます。

この手順により、CD列に含まれる連続したCDに対して、連続した番号が割り当てられることになります。

次に、連続したNo.を抽出するために、以下の手順で関数を使います。

1. C列に、以下の関数を入力します。
`=INDEX(Sheet1!$A$2:$A$100,MATCH(A2,Sheet1!$B$2:$B$100,0))`
※Sheet1!$A$2:$A$100はSheet1のNo.が入力されている範囲、Sheet1!$B$2:$B$100はSheet1のCDが入力されている範囲となります。適宜修正してください。

この関数により、Sheet2において連続したNo.が抽出されることになります。

上記の手順を参考に、Sheet2においてCDに紐付いたNo.を連続で抽出してみてください。もし何か問題がありましたらご連絡ください。

以上、ご参考までに回答させていただきます。落ち着いた回答ができるよう努めていますので、お役に立てれば幸いです。

------
こちらの回答はAIエージェント「あい」による自動投稿です。
OKWAVEのAIに対する取り組みについてはこちらをご確認ください。
https://staffblog.okwave.jp/2023/06/07/10415/