エクセル関数を教えてください

質問

以前こちらで回答を頂き、活用させていただいてます。
今回の質問についてもおねがいします。　

※今回の質問
セル内の4桁の数字（１から0までの数字を使用した4桁；例えば1249）を、別のセル内に、関数（1234567890をそれぞれ2458761390へ変換する関数）で変換された4桁(例えば2489）で表示したいのですがその際に使用する関数を教えてください。参考に前回の質問と回答を参照します。
よろしくお願いします。

※前回の質問
セル内の3桁の数字（１から8までの数字を使用した3桁；例えば124）を、別のセル内に、関数（12345678をそれぞれ24587613へ変換する関数）で変換された3桁(例えば248）で表示したいのですが、その際に使用する関数を教えてください。エクセル上に表示する数字は1から8までのうちの3桁で、12345678をそれぞれ24587613へ変換したいです。
※回答
=SUM(FIND(MID(A1,{1,2,3},1),71823654)*10^{2,1,0})
=SUM(MID("24587613",MID(A1,{1,2,3},1)*1,1)*10^{2,1,0})

回答No.6

kagakusuki

回答番号ANo.5です。
　それから、元の数字が15桁を超えている様な場合には、元の数字を入力するセルの、書式設定を文字列とした上で、次の様な関数とすれば、力技ではありますが、正負の記号や小数点以下の有無、桁数等を気にせずに、変換する事が出来ます。（半角文字と全角文字の違いがありますから、注意して下さい）

=ASC(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"1","２"),"2","４"),"3","５"),"4","８"),"5","７"),"7","１"),"8","３"))

　尚、もしも、御使用になられているExcelのバージョンが、Excel2007よりも前のバージョンである場合には、関数の中に関数を組み込んで、入れ子にする事が出来るのが、7重までしか出来ないため（Excel2007以降では64重まで可能）、Excel2007よりも前のバージョンでは、上記の方法で置換出来る数字の種類は、7種類までです。

お礼

回答ありがとうございました。参考にさせていただきます。

回答No.5

kagakusuki

元の数字が正の整数に限る場合には、次の関数を使えば、1桁～15桁の範囲で、桁数に拠らずに変換する事が出来ます。

=IF(ISNUMBER(A2),IF(A2=INT(ABS(A2)),SUMPRODUCT((FIND(MID(A2,ROW(INDIRECT("Z1:Z"&LEN(A2))),1),"0718236549")-1)*10^(INT(LEN(A2)-ROW(INDIRECT("Z1:Z"&LEN(A2)))))),""),"")

回答No.4

KURUMITO

難しい式をよく理解をしないままに使用するのはやめましょう。しかも、難しい式は計算にも負担がかかります。ご自分の力にあった方法を考えることです。例えば次のような方法を用いることで解決するのも一つの方法です。
A1セルから下方に4ケタの数値が並んでいるとします。
そこで例えば作業列としてD列およびE列を用意します。D1セルから下方に1，2，3・・と入力し、E1セルから下方には変換後の数値を並べます。
答えですがB列に表示させるとしてB1セルには次の式を入力して下方にオートフィルドラッグすればよいでしょう。

=IF(A1="","",VLOOKUP(MID(A1,1,1)*1,D:E,2,FALSE)&VLOOKUP(MID(A1,2,1)*1,D:E,2,FALSE)&VLOOKUP(MID(A1,3,1)*1,D:E,2,FALSE)&VLOOKUP(MID(A1,4,1)*1,D:E,2,FALSE))

お礼

回答ありがとうございました。参考にさせていただきます。

回答No.3

MackyNo1

>この数式でいけるかなと考えたのですが、10桁の場合は"9"と"0"がうまく変換されません。。

良く見たら変換する数字が10個あるのですね。

この場合は、数式を考えてもわかるように、数字に0が含まれている場合、0番目の文字列がないためエラー値が返されます。今回なら「0」の場合は、("2458761390"の左から10番目の数字を検索すべきですので、数式を以下のように変形する必要があります。

=SUM(MID("2458761390",IF(MID(A1,{1,2,3,4},1)="0",10,MID(A1,{1,2,3,4},1)*1),1)*10^{3,2,1,0})

上記の数式を配列数式として認識させるため、入力後Ctrl+Shift+Enterで確定してください。

Ctrl+Shift+Enterで確定せずに、そのままの計算式で表示させるなら、数式を範囲として認識できる以下のような数式にします。

=SUM(MID("2458761390",(MID(A1,{1,2,3,4},1)+(MID(A1,{1,2,3,4},1)="0")*10),1)*10^{3,2,1,0})

お礼

回答ありがとうございました。参考にさせていただきます。

回答No.2

MackyNo1

3桁が4桁になるだけで、前回の質問と全く同じ内容に思いますが、以前の回答の数式の意味を理解されているのでしょうか？

例えば、後者の関数がわかりよいと思いますが、4桁に対応するなら以下のようになります。

=SUM(MID("2458761390",MID(A1,{1,2,3,4},1)*1,1)*10^{3,2,1,0})

数式の意味は、A1セル数字の左から1番目から4番目の数字がそれぞれ「2458761390」という文字列の中の何番目の数字か調べ（左から１，２，３・・・の数字に対応しています）、1番目の数字を4桁目（１０００の位）の数字、2番目の数字が１００の位、3番目の数字を10の位、4番の数字を1の位として表示できるように加算するというような式になっています。

補足

こちらも
=SUM(MID("2458761390",MID(A1,{1,2,3,4},1)*1,1)*10^{3,2,1,0})
この数式でいけるかなと考えたのですが、10桁の場合は"9"と"0"がうまく変換されません。。

回答No.1

mshr1962

=SUM((FIND(MID(A1,{1,2,3,4},1),"0718236549")-1)*10^{3,2,1,0})

=SUM(MID("0245876139",MID(A1,{1,2,3,4},1)+1,1)*10^{3,2,1,0})

ですね

お礼

回答ありがとうございました。参考にさせていただきます。