当記事では、Excel(エクセル)の関数で、兆、億、万を含む数字を数値化する関数について紹介しています。
会社の業績分析を行う時に、兆や億で含む数字を数値にしたいということがありました。
めちゃくちゃに長い関数になりましたが、良い感じに動作する関数ができたので公開しておきます。
色々と方法は考えられますが、多分VBAを使うのが一番スマートです。
ただし拡張子をxlsmにしたくない場合など、関数だけで済ませたい場合もあるので、関数Verも一定の需要があるのではないでしょうか。
兆、億、万を含む数字を数値化する関数
読む気が起きない程長い関数です。上にも書きましたがVBAが使えれば、VBAで記述するほうがスマートだと思います。
一の位まで表示できます。ただし、"円"という漢字が入ると千の位以下の表示が0になります。
直そうかと思いましたが、ただでさえ長い数式がさらに長くなるし、億や兆みたいな単位なら千の位以下は省略されている事がほとんどだろうし、ってことでやめました。
支障ありましたら、置換やSUBSTITUTE関数で、円を抜いて下さい。
=IF(IF(ISERROR(FIND("兆",A1)),0,FIND("兆",A1))=0,0,MID(A1,1,IF(ISERROR(FIND("兆",A1)),0,FIND("兆",A1))-1))*1000000000000+IF(ISERROR(MID(A1,IF(ISERROR(FIND("兆",A1)),0,FIND("兆",A1))+1,IF(ISERROR(FIND("億",A1)),0,FIND("億",A1))-IF(ISERROR(FIND("兆",A1)),0,FIND("兆",A1))-1)),0,MID(A1,IF(ISERROR(FIND("兆",A1)),0,FIND("兆",A1))+1,IF(ISERROR(FIND("億",$A1)),0,FIND("億",A1))-IF(ISERROR(FIND("兆",A1)),0,FIND("兆",A1))-1))*100000000+IF(IF(ISERROR(FIND("万",A1)),0,FIND("万",A1))=0,0,MID(A1,IF(IF(ISERROR(FIND("億",A1)),0,FIND("億",A1))=0,IF(ISERROR(FIND("兆",A1)),0,FIND("兆",A1))+1,IF(ISERROR(FIND("億",A1)),0,FIND("億",A1))+1),IF(ISERROR(FIND("万",A1)),0,FIND("万",A1))-IF(IF(ISERROR(FIND("億",A1)),0,FIND("億",A1))=0,IF(ISERROR(FIND("兆",A1)),0,FIND("兆",A1)),IF(ISERROR(FIND("億",A1)),0,FIND("億",A1)))-1))*10000+IFERROR(VALUE(RIGHT(A1,LEN(A1)-IF(ISERROR(FIND("万",A1)),IF(ISERROR(FIND("億",A1)),IF(ISERROR(FIND("兆",A1)),0,FIND("兆",A1)),FIND("億",A1)),FIND("万",A1)))),0)
こちらをコピーして任意のセルへ貼り付けて下さい。
数値に変換するセルは、初期状態ではA1に指定しているので、エクセルの置換を使ってA1から任意のセルへ変更して下さい。
別関数ver
上記の関数で動作的には問題無いと思うのですが、SUBSTITUTE関数を使用したバージョンも作ってみたので、記念においておきます。
上記関数も非常に長いですが、こちらはその3倍ぐらい長いです。
しれっと会社のエクセルに混ぜ込んだら、怒られるレベルだと思います。ご利用は計画的に。
何らかの理由で上の関数が動かなかったら、試して見て下さい。
=IFERROR(SUBSTITUTE(IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),"兆","")*1000000000000+SUBSTITUTE(IF(ISERROR(FIND("億",SUBSTITUTE(A1,"-",""))),"0億",LEFT(SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),""),FIND("億",SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),"")))),"億","")*100000000+SUBSTITUTE(IF(ISERROR(FIND("万",SUBSTITUTE(A1,"-",""))),"0万",LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("億",SUBSTITUTE(A1,"-",""))),"0億",LEFT(SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),""),FIND("億",SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),"")))),""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),""),FIND("万",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("億",SUBSTITUTE(A1,"-",""))),"0億",LEFT(SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),""),FIND("億",SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),"")))),""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),"")))),"万","")*10000+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("億",SUBSTITUTE(A1,"-",""))),"0億",LEFT(SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),""),FIND("億",SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),"")))),0),IF(ISERROR(FIND("万",SUBSTITUTE(A1,"-",""))),"0万",LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("億",SUBSTITUTE(A1,"-",""))),"0億",LEFT(SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),""),FIND("億",SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),"")))),""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),""),FIND("万",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("億",SUBSTITUTE(A1,"-",""))),"0億",LEFT(SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),""),FIND("億",SUBSTITUTE(SUBSTITUTE(A1,"-",""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),"")))),""),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),"")))),0),IF(ISERROR(FIND("兆",SUBSTITUTE(A1,"-",""))),"0兆",LEFT(SUBSTITUTE(A1,"-",""),FIND("兆",SUBSTITUTE(A1,"-","")))),0),"")
素晴らしい関数ですね。
返信削除マイナスの場合の変換にも対応できるとパーフェクトになると思います。