エクセルの関数を使って連番をつけたいです。

Question

以下の画像のB列のような採番を手入力ではなく、エクセルの関数を使用して自動で入るようにしたいです。

B列のNo.以下をx-y-zの形式で入れたいです。
xは学校ごとに番号が振られ、yは各学校の部活ごとに振られ、
zは各部活の部員氏名ごとに振られます。
（部活項目は他校と同じ部活があっても同じ番号ではなくて良いです。）

9,10行目の間に挿入して××中学校、テニス部、鈴木一郎を入れたら2-2-1が振られる。

補足
xは=SUMPRODUCT(1/COUNTIF(C$4:C4,C$4:C4))
zは=COUNTIF(D$4:D4,D4)

でできました。

=SUMPRODUCT(1/COUNTIF(C$4:C4,C$4:C4))&"-"&(yの関数)&"-"&COUNTIF(D$4:D4,D4)
私のイメージはこんな感じです。

Answer 1

作業列なしでも可能ですが、後で非表示とする作業列を作って処理した方がシンプルな式になります。

zは=COUNTIF(D$4:D4,D4)

これだと、学校が違っても同じ部活だと カウント数が増えてしまいます。
この式を適用すると 12行目は 3-2-2 となります。

よって、作業列+ifでやるのが簡単かなと。
G,H,I をそれぞれ x,y,z の自動連番用の作業列とした場合、IFを使うことで対応可能です。

//x (作業列G)
=SUMPRODUCT(1/COUNTIF(C$4:C4,C$4:C4))
//または
=IF(C3=C4,G3,G3+1)


//y (作業列H)
=IF(C3=C4,IF(D3=D4,H3,H3+1),1)

//z (作業列I)
=IF(D3=D4,IF(C3=C4,I3+1,1),1)

// No. (B列)
=TEXTJOIN("-",TRUE,G4:I4)

それぞれ G4,H4,I4 と B4に入れて、あとはコピぺ。

Comments

[退会済みユーザー]

ありがとうございます。
やりたいことができました。
作業場所無しで一つのセルに完結させたかったので、いただいたものをもとに別回答として記載させていただきました（だいぶ長くなってしまいました。）

Answer 2

遣りたい事はこんな感じと思われます。

=VLOOKUP(C3,$C$14:$D$16,2,FALSE)&"-"&VLOOKUP(D3,$D$19:$E$23,2,FALSE)&"-"&VLOOKUP(E3,$E$26:$F$34,2,FALSE)

Comments

[退会済みユーザー]

tosi様、ご回答いただきありがとうございます。
sawa様のご回答が私の求めていた内容に近かったのでベストアンサーとさせていただきました。

Answer 3

以下で一応動きました。

=TEXTJOIN("-",TRUE,SUMPRODUCT(1/COUNTIF(C$4:C4,C$4:C4)),IF(C3=C4,IF(D3=D4,MID(B3,FIND("-",P3)+1,FIND("●", SUBSTITUTE(B3, "-", "●", LEN(B3) - LEN(SUBSTITUTE(B3, "-", ""))))-FIND("-",B3)-1),MID(B3,FIND("-",B3)+1,FIND("●", SUBSTITUTE(B3, "-", "●", LEN(B3) - LEN(SUBSTITUTE(B3, "-", ""))))-FIND("-",B3)-1)+1),1),IF(D3=D4,IF(C3=C4,MID(P3,FIND("●", SUBSTITUTE(B3, "-", "●", LEN(B3) - LEN(SUBSTITUTE(B3, "-", ""))))+1,LEN(B3)-FIND("●", SUBSTITUTE(B3, "-", "●", LEN(B3) - LEN(SUBSTITUTE(B3, "-", "")))))+1,1),1))