ID番号の自動作成をしたい

質問

いつもお世話になります。
WIN7 EXCELL2010 です。

目的は記入時の簡易性及び重複の防止です。
添付図で説明さていただきますと、
1のシートでは
A列　　今回の要望です
B列　　手入力です
C2　 　=MID(D2,1,1)
D2　　=PHONETIC(B2)

２のシートは説明を分かり易くするためのものです。

ご指導をいただきたいのは、

例えば A2(011-001)
シート１　C2は　の場合　ア　で
シート２　では、　行は　「０１」　列は　「１」　からして　「０１１」になり
そのあとは記入順の番号を振っています。

※シート２　B1～F1 は数字ですがアルファベットでもいいのです。

このことが関数では可能ですか。
もし可能ならば是非ともご教示いただけませんでしょうか。
不可能ならVBAでご指導戴けるとうれしいです。
誠に勝手な要望ですが宜しくお願いいたします。

回答No.3

kagakusuki · ベストアンサー

Sheet1のB列に名前を手入力されていさえすれば、Sheet1のC列やD列、及びSheet2を使わなくても良い方法です。

　まず、Sheet1のA2セルに次の2つの関数の内、どちらか好きな方を入力して下さい。

=IF(COUNTIF($B2,"*?"),IF(AND(ASC(PHONETIC($B2))>="ア",ASC(PHONETIC($B2))<"ン"),0&INT((CODE(ASC(PHONETIC($B2)))-172+(ASC(PHONETIC($B2))>"ヤ")*2)/5)&CODE(ASC(PHONETIC($B2)))-FLOOR(CODE(ASC(PHONETIC($B2)))-177+(ASC(PHONETIC($B2))>"ヤ")*2,5)-176&"-"&TEXT(COUNTIF(A$1:A1,0&INT((CODE(ASC(PHONETIC($B2)))-172+(ASC(PHONETIC($B2))>"ヤ")*2)/5)&CODE(ASC(PHONETIC($B2)))-FLOOR(CODE(ASC(PHONETIC($B2)))-177+(ASC(PHONETIC($B2))>"ヤ")*2,5)-176&"-*")+1,"000"),"フリガナ不明"),"")

或いは

=IF(COUNTIF($B2,"*?"),IF(AND(ASC(PHONETIC($B2))>="ア",ASC(PHONETIC($B2))<"ン"),0&MATCH(ASC(PHONETIC($B2)),{"ア","カ","サ","タ","ナ","ハ","マ","ヤ","ラ","ワ"})&CODE(ASC(PHONETIC($B2)))-CODE(LOOKUP(ASC(PHONETIC($B2)),{"ア","カ","サ","タ","ナ","ハ","マ","ヤ","ラ","ワ"}))+1&"-"&TEXT(COUNTIF(I$1:I1,0&MATCH(ASC(PHONETIC($B2)),{"ア","カ","サ","タ","ナ","ハ","マ","ヤ","ラ","ワ"})&CODE(ASC(PHONETIC($B2)))-CODE(LOOKUP(ASC(PHONETIC($B2)),{"ア","カ","サ","タ","ナ","ハ","マ","ヤ","ラ","ワ"}))+1&"-*")+1,"000"),"フリガナ不明"),"")

　そして、Sheet1のA2セルをコピーして、Sheet1のA3以下に貼り付けて下さい。

　以上です。

お礼

早速皆様方からのご指導を賜り本当に幸せな私です。
本当にありがとうございました。
どなたのも試させていただきどれがいいのか決めかねています。
そんな中で二つの提案とも表が不要がよかったです。
上側の数式を採用させていただきます。

回答No.4

kagakusuki

回答No.3です。

　申し訳御座いません、おそらく回答No.3に掲載されている関数をそのままコピー&ペーストしただけでは、正常に動作しない場合があるかと思います。
　何故なら、回答No.3の関数中において、"ｱ"、"ｶ"、"ｻ"、"ﾀ"、"ﾅ"、"ﾊ"、"ﾏ"、"ﾔ"、"ﾗ"、"ﾜ"、"ﾝ"といったカタカナの部分が、元々は半角文字のカタカナで入力されている場合において動作する様に、関数を組んだものでして、全角文字のカタカナでは、正常に動作する事が期待出来ないからです。
　実を申しますと、回答No.3で挙げさせて頂いた関数は、実際にエクセルを使って動作確認を終えた関数を、回答欄にコピー&ペーストで入力したもので、カタカナの部分は、元々は半角文字となっていたのですが、サイトに掲載された後で、よくよく確認してみました処、何故か半角文字のカタカナではなく、全角文字のカタカナに変換されてしまっておりました。（サイト側のシステムが勝手に全角文字に変換してしまったのかも知れません）
　そのままでは、正常に動作しない恐れがありますので、"ｱ"、"ｶ"、"ｻ"、"ﾀ"、"ﾅ"、"ﾊ"、"ﾏ"、"ﾔ"、"ﾗ"、"ﾜ"、"ﾝ"といったカタカナの部分を、全て半角文字に修正して頂く様に願います。（尚、"フリガナ不明"の部分は全角文字のままでも構いません）

回答No.2

MarcoRossiItaly

No.1さんのように 2 次元の配列を使えばできますね（行番号を TEXT 関数で 2 桁にするのをお忘れかも）。

別解としては、次のように PHONETIC、FIND 関数というのを使っても、ご要望のことは可能です。

A2　=text(int(find(c2,phonetic(sheet1!F$2:J$11))/5)+1,"00")&mod(find(c2,phonetic(sheet1!F$2:J$11))-1,5)+1&"-"&text(countif(C$2:C2,c2),"000")

ただし、次の条件を満たしてください。

必要条件(1)

Sheet2 に対して「ふりがなの設定」ダイアログで設定している「種類」（「半角カタカナ」など）と、Sheet1 の C 列に表示／入力する振り仮名の種類が、必ず一致するようにしてください。そうでないとエラーになります。

C 列の文字列が B 列の振り仮名に由来しているのであれば、B 列を選択した状態で「ふりがなの設定」ダイアログを操作してください。

必要条件(2)

五十音表の中で空白になっているセルには、必ず何らかの文字列（添付図では「★」）を入力してください。文字数は何文字でも構いません。ただし、その文字列の振り仮名の文字数は、1 文字にしてください。そうでないと、Sheet1 で算出される値がずれてしまうので。

振り仮名の編集のショートカットキーは、Alt+Shift+↑です。「★」だったら、振り仮名も「★」 1 文字などにしてもいいかもしれません。

!!!!!!!!!!
数式を入力した後にこれらの設定を行った場合は、数式の計算結果に変更内容が反映されなかったりします。どこかのセルをダブルクリックするなどして、再計算させれば反映されます。

なお上式において PHONETIC は、添付図の A15 セルのような値を算出しています。濁点のことを考慮し、とりあえず半角カタカナにしてみました。この選択は、必要条件(1)における Sheet2 での設定内容に依存しています。

お礼

早速皆様方からのご指導を賜り本当に幸せな私です。
本当にありがとうございました。
どなたのも試させていただきどれがいいのか決めかねています。

回答No.1

MackyNo1

以下の式をA2セルに入力して下方向にオートフィルコピーしてください。

=INDEX(Sheet2!A:A,MAX(INDEX((Sheet2!$B$2:$F$11=C2)*ROW($B$2:$F$11),)))&INDEX(Sheet2!$1:$1,MAX(INDEX((Sheet2!$B$2:$F$11=C2)*COLUMN($B$2:$F$11),)))&TEXT(COUNTIF($C$2:C2,C2),"-000")

お礼

早速皆様方からのご指導を賜り本当に幸せな私です。
本当にありがとうございました。
どなたのも試させていただきどれがいいのか決めかねています。

回答No.5

MarcoRossiItaly

No.2 です。なんか数式にいろいろミスがあったので、次のとおり訂正させてください。今頃、申し訳ありません。

A2　=text(int((find(c2,phonetic(sheet2!B$2:F$11))-1)/5)+1,"00")&mod(find(c2,phonetic(sheet2!B$2:F$11))-1,5)+1&"-"&text(countif(C$2:C2,c2),"000")

INT 関数の中に「-1」を加えました。こうしないと、「オ」の段のときだけ、上 2 桁の数字が 1 大きくなってしまいます。INT だから当たり前のことなのですが、凡ミスです。2 文字だけとはいえ、数式が長くなってしまいました…。また、なぜか PHONETIC の中に「sheet1!F$2:J$11」と書かれていましたが、質問文どおり、「sheet2!B$2:F$11」に修正しました。

これで、正しい値を計算すると思います。No.2 の添付図では本当は「085-001」とならなければならないのに、誤っていますね。