Excel 数字から判断して文字を表示

質問

Excelにて以下の様な処理を関数を用いて行いたいと思います(都合上、VBは不可)

1)数字に名前を割り当てる
ex：「1-田中」、「2-鈴木」、「3-佐藤」、「4-山田」、「5-橋本」、「6-山本」、「7-沢田」、「8-山岡」

2)1～9までの数字を入力する
ex：「12345」、「3456」、「2487」

3)２で入力された数字を元に１の名前を表示する
ex：
「12345」→「田中、鈴木、佐藤、山田、橋本」
「3456」→「佐藤、山田、橋本、山本」
「2487」→「鈴木、山田、山岡、沢田」

この様な処理はどうすれば実現出来ますでしょうか？
数字の組み合わせがいくつもあるのでVLOOKUPは使えない。と思い、質問させて頂きました
よろしくお願い致します。

回答No.5

KURUMITO · ベストアンサー

VLOOKUP関数を使って対応することもできますね。
例えばA１セルから下方に1234の数値などを入力するとしてそれらの数値に該当する名前をB列からF列までにひ表示させるとして、数字の１から９までに対応する氏名をM列とN列に並べて入力します。M１セルから下方には１，２，３・・・とN１セルから下方には氏名を並べます。
その後にB１セルには次の式を入力してF１セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(MID($A1,COLUMN(A1),1)="","",VLOOKUP(MID($A1,COLUMN(A1),1)*1,$M:$N,2,FALSE))

*1は文字列として抽出された数字を数値に変えています。その上でVLOOKUP関数を使っています。

お礼

ありがとうございました
期待通りの結果が得られました。
一番判りやすかったのでベストアンサーに選ばせて頂きます

回答No.6

kagakusuki

web2525様の御回答の改良案です。

　まず、C3セルには次の数式を入力して下さい。

=IF(ISNUMBER($A3),IF(AND(ABS(INT($A3)),LEN($A3)>COLUMNS($C:C),INDEX($A$1:$F$1,MID($A3,COLUMNS($C:C),1)),""),"")

　次に、D3セルには、C3セルの数式を少しだけ変えた、次の数式を入力して下さい。（「、名前」の形式の文字列が表示されます）

=IF(ISNUMBER($A3),IF(AND(ABS(INT($A3)),LEN($A3)>COLUMNS($C:D),"、"&INDEX($A$1:$F$1,MID($A3,COLUMNS($C:D),1)),""),"")

　次に、D3セルを右方向にフィル(最大の数字入力個数分）して下さい。

　そして、結合した名前を表示させるセルには、次の数式を入力して下さい。

=C3&D3&E3&F3&G3&H3&I3&J3&K3&L3

お礼

ありがとうございました
この方法でも期待通りの結果が得られました。

回答No.4

web2525

作業列を使用しますが

=IF(LEN(数字)>COLUMN()-X,INDEX(名前,MID(数字,COLUMN()-Y,1)),"")
数字は数字入力セル、名前は変換する名前リストの範囲、COLUMN()-XとCOLUMN()-YのX,Yは作業セルの配置する場所で変化します

例：数字セルがA3にあり名前リスト範囲がA1:F1にあり、作業セルがC3以降に有る場合

=IF(LEN($A3)>COLUMN()-3,INDEX($A$1:$F$1,MID($A$3,COLUMN()-2,1)),"")

この計算式をC3セルに入力後右方向にフィル(最大の数字入力個数分）
入力した数字に該当する名前が各セルに表示されます

次に抽出された名前の結合

今回は数字入力セルの隣A2セルに表示するとして

=CONCATENATE(C3,IF(D3="","","、"),D3,IF(E3="","","、"),E3,IF(F3="","","、"),F3,IF(G3="","","、"),G3)

この数式をA2セルに入力この場合最大5人までが[、]区切りで連記されます、表示するセルとIF(次ぎのセル="","","、")の連続です最大16人まで連記可能

お礼

ありがとうございました
期待通りの結果が得られました。

回答No.3

mshr1962

桁数が固定で無いと関数では難しいですね。それこそVBでユーザー定義関数作るなら別ですが．．．

現状だと5桁まで対応で
Z2:Z9に田中,鈴木,佐藤,山田,橋本,山本,沢田,山岡と入力（Z1は空白）
=IF(LEN(A1)=0,"",INDEX($Z$1:$Z$9,MID(A1,1,1)+1)&IF(LEN(A1)<=1,"","、"&INDEX($Z$1:$Z$9,MID(A1,2,1)+1)&IF(LEN(A1)<=2,"","、"&INDEX($Z$1:$Z$9,MID(A1,3,1)+1)&IF(LEN(A1)<=3,"","、"&INDEX($Z$1:$Z$9,MID(A1,4,1)+1)&IF(LEN(A1)<=4,"","、"&INDEX($Z$1:$Z$9,MID(A1,5,1)+1))))))

お礼

ありがとうございました
期待通りの結果が得られました。

回答No.2

mu2011

NO1です。
回答中に不適切な記載がありましたので訂正します。
「～該当列は列表示とします。」⇒「～該当列は非表示とします。」

回答No.1

mu2011

この繰り返し処理を関数で実現するには酷です、やっぱりＶＢＡで行う事が筋と思える。
仮に関数で行うなら、次のような方法は如何でしょうか。
（１）別列に数値１桁単位に抽出し、それをVLOOKUP関数で名前に変換（この列は非表示としておく）
　　　別列に=IF(MID(入力セル,COLUMN(A1),1)<>"",INDEX(名前範囲,MID(入力セル,COLUMN(A1),1)),"")として最大入力桁分右にコピー
　　　入力セル、名前範囲は列絶対参照として、該当列は列表示とします。
　　　名前範囲行と数値は対応している事としています。
（２）（１）の非表示列を文字連結（&）で編集表示

お礼

ありがとうございました
期待通りの結果が得られました。