エクセルで枝番

質問

Ａ 　　 　 Ｂ 　 　　　　　 Ｃ 　　 　　　　Ｄ 　 　　　　　 Ｅ
１　都道府県　　市　　　　枝番１　　　　　町　　　　　枝番２
２　東京都　　中野区　　　１－１　　　　 あ町　　　　１－１－１
３　　　　　　　　　　　　　　 　　　　　　　　い町　　　　１－１－２
４　　　　　　　　　　　　　　　　　　　　　　　う町　　　　１－１－３
５　　　　　　　 新宿区　　　１－２　　　　 か町　　　　１－２－２
６　　　　　　　 渋谷区　　　１－３　　　　 さ町　　　　 １－３－１
７　　　　　　　 台東区　　　１－４　　　　 し町　　　　 １－４－１　
８　神奈川県　横浜市　　　２－１
９　　　　　　　 横須賀市　 ２－２　　　　た町　　　　２－２－１

上のような表の枝番１と枝番２のところに、関数を使って連番を入れたいと思っています。
Ｃ３、Ｃ４、Ｅ８のようなところは空白のままにしておきたいです。
「COUNTA」「&」「IF」「MID」「FIND」「LEN」などを使うのではないかと思うのですが、複雑すぎて
私の能力を超えています。

どなたか、Ｃ２とＥ２に入れる数式を教えていただけないでしょうか。
どうかよろしくお願いいたします。

回答No.6

tom04

No.5です！
たびたびごめんなさい。

投稿した後で気になったのですが・・・
データの行数として一番多くなる列はD列になるのではないかと思いますので、
前回のコード内の
＞For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
の部分（2行あります）を

＞For i = 2 To Cells(Rows.Count, 4).End(xlUp).Row
に訂正してみてください。

※　2行目から最終データ行まで　を表示したいので、「4」の部分が「D列」になります。

何度も失礼しました。m(_ _)m

回答No.5

tom04

こんばんは！
横からお邪魔します。

データが1万行を超えるということなので・・・
VBAでの一例です。

1行目はタイトル行でデータは2行目からあるとします。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので、
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, k As Long
Dim buf As String
Application.ScreenUpdating = False
For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
If Cells(i, 1) <> "" And Cells(i, 2) <> "" Then
k = 1
With Cells(i, 3)
.NumberFormatLocal = "@"
.Value = WorksheetFunction.CountA(Range(Cells(2, 1), Cells(i, 1))) & "-" & 1
End With
ElseIf Cells(i, 1) = "" And Cells(i, 2) <> "" Then
k = k + 1
With Cells(i, 3)
.NumberFormatLocal = "@"
.Value = WorksheetFunction.CountA(Range(Cells(2, 1), Cells(i, 1))) & "-" & k
End With
End If
Next i
For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row
If Cells(i, 3) <> "" Then
k = 1
buf = Cells(i, 3)
End If
If Cells(i, 3) <> "" And Cells(i, 4) <> "" Then
With Cells(i, 5)
.NumberFormatLocal = "@"
.Value = Cells(i, 3) & "-" & k
End With
ElseIf Cells(i, 3) = "" And Cells(i, 4) <> "" Then
k = k + 1
With Cells(i, 5)
.NumberFormatLocal = "@"
.Value = buf & "-" & k
End With
End If
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

※　少数データでしか検証していませんので、ご希望通りの動きにならなかったら
ごめんなさいね。m(_ _)m

回答No.4

kagakusuki

確認したいのですが、都道府県名や市名、及び町名の表示は、関数によって別のデータから引っ張って来たものではなく、キーボード入力等により、セルに直接入力されているものなのでしょうか？
　又、東京都や中野区といった、都道府県名や市名は、同じ名前は必ず１回ずつしか現れず、
例えば、神奈川県が入力されている8行目よりも下の行に、再び東京都が現れたり、
「あ町」が入力されている行だけではなく、「い町」や「う町」が入力されている行にも、「中野区」と入力されていたり、
といった事は無いと考えて宜しいのでしょうか？

　もし、上記のルールが厳密に守られている場合は、配列変数を使わずとも、普通の関数で完全自動化を図る事が出来ます。

　まず、C列の中の適当なセルに次の関数を入力してから、そのセルをコピーして、C列のその他のセルに貼り付けて下さい。

=IF(INDEX($B:$B,ROW())="","",COUNTIF(INDEX($A:$A,ROW($A$1)+1):INDEX($A:$A,ROW()),"*?")&"-"&COUNTIF(INDEX($B:$B,MATCH("゛",$A$1:INDEX($A:$A,ROW()),-1)):INDEX($B:$B,ROW()),"*?"))

　次に、E列の中の適当なセルに次の関数を入力してから、そのセルをコピーして、E列のその他のセルに貼り付けて下さい。

=IF(INDEX($D:$D,ROW())="","",COUNTIF(INDEX($A:$A,ROW($A$1)+1):INDEX($A:$A,ROW()),"*?")&"-"&COUNTIF(INDEX($B:$B,MATCH("゛",$A$1:INDEX($A:$A,ROW()),-1)):INDEX($B:$B,ROW()),"*?")&"-"&COUNTIF(INDEX($D:$D,MATCH("゛",$B$1:INDEX($B:$B,ROW()),-1)):INDEX($D:$D,ROW()),"*?"))

補足

ご回答ありがとうございます。

Ａ、Ｂ、Ｄ列のデータは、全て独立したものです。また、若い列にあるデータが、それ以降の列に出てくることはありません。ただ、Ｂ列やＤ列の行の中に、読み方は違うが漢字にすると同じ漢字になってしまうというもの、または、漢字は同じだが読み方は違うものがあるのですが、その場合は教えていただいた数式は使えないのでしょうか？

配列変数を使わずに済むということは、それだけ処理速度が速いと言うことですね。
すでにこの表にはいくつもの関数や条件付書式が入っていて、データも１万件を超えているため、１行ずらすだけで１分以上処理に時間がかかるということもあります。ですから軽いに越したことはありません。

本当にありがとうございます。
エクセルがここまでできる人ってほんと尊敬します。

回答No.3

MackyNo1

以下の数式でご希望のデータを表示できます。
ただし配列数式ですので、大量のデータを処理するときは、再計算に時間がかかるので、計算方法を手動にして、最後に表示させるときにF9キーで再計算させるなどの対応をしたほうが良いかもません。

C2セル
=IF(B2="","",COUNTA($A$2:A2)&"-"&COUNTA(INDEX(B:B,MAX(INDEX(($A$2:A2<>"")*ROW($A$2:A2),))):B2))

E2セル
=IF(D2="","",COUNTA($A$2:A2)&"-"&COUNTA(INDEX(B:B,MAX(INDEX(($A$2:A2<>"")*ROW($A$2:A2),))):B2))&IF(D2="","","-"&COUNTA(INDEX(D:D,MAX(INDEX(($B$2:B2<>"")*ROW($B$2:B2),))):D2))

お礼

ありがとうございます！！！
それにしてもすごすぎます。
まさかこれほど複雑になるとは思っていませんでした。
おかげで何十時間も節約することができます。

本当にありがとうございます。

回答No.2

-9L9-

まず相互の関連性の法則を明らかにしてください。法則のないものを数式化することは不可能です。

補足

分かりづらくてすみません。
私はあるもののデータベースを現在作成しています。
Ａ列は数が決まっているので固定番号をつけています。
しかしＣ列については、今後どんどん新たなデータが入ってきます。
そのたびに枝番２を手作業で変更するのはかなりの負担ですし、上記のような枝番を振ることができれば、まとまったデータを今後追加するとき、ソートによって入れたい場所にそのデータを入れることができるのではないかと思いました。
ソートをかける際に、あ町～う町の順番を崩さず、その後に続くようにしたいのです。
Ｄ列は実際にはあいうえお順に並んでいるのではなく、更に右にある列によってその順番が決まっています。

これで答えになっていますでしょうか？

回答No.1

noname#204879

セル E5 が「１－２－１」でなくて「１－２－２」になっているのは なぜ？

補足

すみません。
私の入力ミスです。
正しくは「１－２－１」です。