100円＋200円を計算するには
最近「"Excelの正しい使い方"に関して話をしてください」というオファーが多いです。先日も、北海道の某大手組織さんにうかがって「正しい使い方とは何か」「間違った使い方をすると何が起きるのか」「そのために知っておくべき知識と考え方」などを、たっぷりとお話してきました。その節はお世話になりました。日本酒、とても美味しかったです。"Excelの正しい使い方"といえば、もちろん「入力→計算→出力」の件だったり「田中メソッド」の話などを詳しく解説しますが、そんな中で「計算対象の数値セルに助数詞を入力してはいけない」という、あったり前のこともお話しています。つまり、計算したい数値のセルに「100円」や「200個」などの助数詞を文字として入力してはいけないと。こういうときは、「100」「200」のように計算できる数値だけを入力して、「円」や「個」は表示形式で表示するだけにしなさいと。そうしないと計算できませんよって。
ときどき「へ？なんで計算できないんですか？」みたいな質問もされますが、そんなのあったり前じゃありませんか。文字は計算できますか？「田中＋鈴木」はいくつですか？文字は計算できないっしょ？"円"って文字ですよ。これ、Excel的には単なる"数値の後ろに文字があるデータ"です。つまり「100ではない何か＋200と勘違いしてしまいそうなアレ」を計算させようとしているようなものですね。無理に決まってるじゃありませんか。だから、計算対象の数値セルに助数詞を文字として入力しちゃいけないんです。
とまぁ、言われてみれば当然のことなので、みなさん納得していただけるんですが、先日、私が毎月生放送でExcelの授業をしているSchooで、実に困った質問を受けました。別のところから送られてきたブックを集計しようとしたら、すべてのセルに"円"が手入力されていたと。これじゃ計算できません、ぴえ～ん みたいな。その質問者さんは、今までにも何度か質問をしてくれている常連さんで、正直言って、あまりExcelは詳しくありません。これから勉強していこうという方です。でも、そんな困ったブックを受け取ってしまったのですから、これは仕方ありません。何とか集計するしかないです。仕事ですから。でも、その方法が分からない。で、ネットの掲示板で質問をしたところ「これでできるっしょ」と示されたのが下図のような数式らしいです。
質問者さんがExcel初心者であるということを差し引いても、これ、いろんな意味で？？？です。ツッコミどころ満載ですｗ しかも、質問者さんが、この数式の解説を求めたところ「そんなの自分で考えろ！理解できないのはあなたの勉強不足だ」的に言われたらしいです。何ともかわいそうに。さて、長い前置きはこのくらいにして、では、こうした極悪非道の「100円」「200円」を計算する方法を解説します。最後に、なぜ「これでできるっしょ」の数式が間違っているのかも解説しますね。
最も簡単な方法
まず、最も簡単な方法であり、実務ではこれがベストってやり方をご紹介します。今回の問題は「セルの中に文字の"円"が入力されている」という点です。逆に言えば、セルの中から"円"がなくなればいいんです。だったら置換で消しちゃいましょう。もちろん手動操作で。こんなの、手でやるのが一番速いです。
[検索する文字列]に"円"を指定し、[置換後の文字列]は空欄にします。これで[すべて置換]を実行すれば、すべての"円"が除去されて、セルの中には純粋な数値だけが残ります。ちなみに「1,000円」のように入力されていたときも、"円"だけ除去すればいいです。セルに「1,000」が残ると、Excelはセルに数値の「1000」を入れて桁区切りの表示形式を設定してくれます。置換した結果は下図のとおり。
あとは、合計でも平均でも標準偏差でも、お好きな集計をしてください。まぁ、集計するだけなら以上終了なのですが、実務ではいろいろと理不尽な要求を突きつけられます。この極悪非道ブックを集計して、これを別のところに渡すケースです。集計のために"円"を消したのですが、でも、見た目的には「100円」と表示しておきたいです。なので、"円"を除去したセルに表示形式を設定します。
さらに配置も設定しておけば完璧です。「100円」は文字列ですから、標準では"左寄せ"で表示されます。"円"を除去したことを気づかれないためには、セルの配置を"左寄せ"にします。
集計結果のセルにも、表示形式を設定してください。実務的には、この方法がベストでしょう。
数式だけでやる方法
実務の世界では、何が起きるか分かりません。もし、どーーーーしても「100円」の"円"を除去してはいけないという、イジメのような縛りがあったら、仕方ありません。数式だけで対応しましょう。ここでも、まずは最も簡単なやり方から解説します。さきほど書いたように、要するにセル内の"円"がなくなればいいんです。だからさっきは置換機能を使いました。では、数式で"円"を除去するには、どうしたらいいか。それにはSUBSTITUTE関数を使います。SUBSTITUTEとは「代える」とか「取り替える」などの意味を持つ英単語です。ExcelのSUBSTITUTE関数は、セル内の文字列で、指定した文字を、別の文字に変換した結果を返します。このSUBSTITUTE関数、よく次のようなケースで解説されます。
これ、大昔からよく使われるSUBSTITUTE関数の例なんですが、こんなこたぁしませんってｗ そうじゃないんです。実務でこのSUBSTITUTE関数を使う最も多いケースは、何かの文字を別の文字に置換するのではなく、何かの文字を除去するときです。思い出してください。先に手動操作で"円"を除去したことを。あのとき、"円"を空欄に置換しましたよね。だから、SUBSTITUTE関数でも同じことをしてやればいいんです。
このSUBSTITUTE関数、実務では必須なので絶対に覚えておいてください。
"円"を除去できました。しかし、ここで注意が必要です。このままでは集計できません。
SUBSTITUTE関数は文字列を返す関数です。"円"を除去した結果の「100」は文字列です。文字列をSUM関数で計算することはできません。なので、SUBSTITUTE関数の結果を、計算できる数値に変換してやります。それには、いろいろな方法があります。もし、その変換を関数でやるのなら、たとえばVALUE関数を使います。VALUE関数は、文字列形式の"数値もどき"を、計算できる数値に変換する関数です。
これでもOKですが、たかが文字列形式の"数値もどき"を数値に変換するために、数式が7文字も長くなるのは可読性が劣ります。なので、こんなときは大昔から「"数値もどき"に1を掛ける」や「"数値もどき"に0を足す」という方法がよく使われます。
ここで不思議に感じる方がいるでしょう。先ほど、文字列形式の"数値もどき"をSUM関数で計算できませんでした。でも今回は、文字列形式の"数値もどき"に対して「1を掛ける」という計算ができています。何が違うんだろうって。このへんの仕組みは少しややこしいのですが、簡単に解説します。まず、3つ前の画像をよく見てください。SUBSTITUTE関数が返した文字列をSUM関数で集計したとき、SUM関数はエラーになっていません。計算結果が0になっています。これは、SUM関数の仕様です。SUM関数は、計算対象の中に文字列が含まれていると、その文字列を無視するという決まりがあります。なぜ、そのような仕組みがSUM関数に実装されているかというと、次のように「計算対象の中に文字列が含まれている」場合であっても、とりあえず数値の合計だけを計算したいからです。
この仕様によって、表のタイトル(見出し)を意識する必要がなくなります。SUM関数が作られた1980年代には、SUMIF関数などという便利な関数は存在しません。なので、この「文字列は無視して、計算できる数値だけを合計する」というのは嬉しかったんです。さて、一方で、SUM関数ではなく、普通の四則演算ではどうでしょう。もちろん、文字列を計算することは不可能です。
では、文字列形式の"数値もどき"ではどうでしょう。
計算できるんです。これはExcelの仕様です。Excelは、たとえそれが文字列形式であっても、そのまま計算できるのであれば、裏で数値に変換してから計算してくれます。これはこれで、ユーザーにとってはありがたいです。よろしいですか？ややこしいですから、じっくり考えてください。SUBSTITUTE関数が返す"数値もどき"を純粋な数値に変換したかったのは、"数値もどき"をSUM関数で計算できないからです。そのためにやった「1を掛ける」というのは、別にSUM関数を使っているのではありません。単なる四則演算です。だったらExcelが裏で数値に変換してから計算してくれます。もちろん、その計算をした結果は数値形式です。
作業セルを使わない方法
ここまでの解説は、A列に入力されている「100円」などを、B列で数値に変換してから計算しています。このように、計算の過程で使う別のセルを作業セルと呼びます。Excelは、というか表計算ソフトは、そのように計算の過程を複数のセルで行うのが本来の使い方です。このへん、ほとんどの人が勘違いしていますが、一つのセルに長い数式を入れ一発で計算してる方が初心者ぽいです。みんな逆に思ってませんか？上級者はみんな作業セルを使ってるんですよ。ちなみに、分かりやすく作業セルを使うのって、かなりのセンスと技術を要します。ということで、今回も作業セルで数値に変換するのがベストなのですが、一応作業セルを使わない方法も解説します。ただ、覚悟してくださいね、難易度は高くなります。
まず最初に考えるべきことは、お使いのExcelで「スピルが使えるかどうか」です。スピルは、Microsoft 365からインストールするProPlusで使えます。永続ライセンス版でしたら、おそらくExcel 2021から使えるでしょう。Excel 2016やExcel 2019は対応していません。スピルとは、簡単に言うと次のような仕組みです。
スピルに関する詳しい解説は、下記ページをご覧ください。
とはいえ、上記のような例でスピルを紹介されても、正直「だから？」って感じる方もいるでしょう。そうじゃないんです。このスピルが実装されて、初めて実現可能となった新しい関数たちがあるんです。たとえば、FILTER関数とか、SORT関数とか、UNIQUE関数とか、XLOOKUP関数とか。これら、Excelの歴史を塗り替えるほど強力な関数たちは、どれも"スピルありき"の関数です。つまり、スピルとは何か、どういう仕組みなのかを理解していないと、これら超強力な関数を使えないんです。だから、これからのExcelユーザーにとって、スピルは必須の知識です。FILTER関数やXLOOKUP関数などに関しては、下記のコーナーで詳しく解説しています。
さて、上記の簡単な例を、ちょっと変更してみましょう。
さらに、こんなこともできます。
すみません、このへんスピルの詳しい話をしているとキリがないので割愛させてください。スピルがよく分からないという方は、ぜひ上記のページをご覧ください。さて、今回のケースでしたら、こんな感じになります。SUBSTITUTE関数だとか、1を掛けて数値に変換しているところなどは、これまでの解説をよく読んでください。
これで完成です。もし、桁区切りのカンマや"円"をつけたいのなら、それは表示形式でやってください。"円"を文字列としてセルに入れちゃダメですよ。もし、この計算結果を文字列として作ってしまったら、その計算結果をさらに使うことができません。今回の問題は、そこから始まっているんでしょ？それで困っているんでしょ？人が嫌がることは、しないようにしましょう。
では、スピルが使えないExcelだったら。そのときは、配列数式という仕組みを使います。配列数式とは何かというのも、詳しく解説するとメッチャ長くて難しくなりますので、ここでは割愛します。配列数式の場合は、数式を入力して確定するとき、ただEnterキーを押すのではなく、Ctrl + Shift + Enterキーを押します。
配列数式は、数式全体が中括弧{}で囲まれます。この中括弧{}は、ユーザーが入力するのではなく、Excelが自動的につけてくれます。ちなみに、この配列数式というのは古典的な仕組みです。Microsoftは「もう配列数式は使わないでね」というアナウンスをしています。その代わりにスピルを使ってくださいと。上記の数式を見比べてみれば分かりますが、どちらも数式の内容は同じです。スピルを使う数式のことを、動的配列数式といいます。つまり、スピル(動的配列数式)は、古典的な配列数式の上位版なんです。
「これでできるっしょ」の間違い
さて最後に、質問者さんの質問に対して「これでできるっしょ」と示された数式は、どこが間違っているのかを解説しましょう。ちなみに、ここで言う"間違っている"というのは、答えが違うとか、間違った計算結果になるという意味ではありません。計算結果は正しいです。でも、答えが正しければいいんじゃありません。重要なことは数式のクオリティです。
まずは細かいところから。これは賛否あるでしょうから一概には言えませんが、文字列形式の"数値もどき"を数値に変換するためにVALUE関数を使うのは如何なものかと。この手の変換をするときって、たいてい数式が長くなります。数式全体が長いと、読み解くのが大変です。無理をしない範囲で、できるだけ短くしたいです。なので私は「1を掛ける」方法をお勧めしています。もちろん、1を掛けることで数値に変換するという操作を"マニアックな技術"と感じる方は、VALUE関数を使ってください。ここは、あくまで私の個人的な感想です。
さて次です。この数式、複数の関数が使われていますので、括弧()も多いです。
数式内で使われているのは、FIXED関数、SUM関数、VALUE関数、SUBSTITUTE関数の4つです。あれ？上図をご覧ください。括弧が5組ありませんか？この数式をパッと見たとき、私には違和感がありました。なんか、括弧多くね？って。そのときはまだ詳しく見ていませんでしたが、何となくそんな感じがしました。あらためて数式を解読してみたら、VALUE関数の後ろに括弧が2つあります。何だろう？まぁ、そういう使い方もあるけど。でも、どう考えてもここ、括弧が1つ多いです。無駄です。正しくは、こうです。
「別に、無駄があったっていいじゃん、答えが合ってればいいっしょ」という方は、下図をご覧ください。セルC1で、セル範囲A1:A5を合計しています。
セルC1には、次のような数式が入っています。
=MID(CONCATENATE(CHAR(64+COLUMN()),SUM(A1:A5)),MAX(256-254),255)*(N(ROW()=1))+(FALSE())
これ、真ん中へんにある「SUM(A1:A5)」以外はすべて無駄な記述です。でも、答えは合ってます。数式って、何をしているのかを読み解くのが難しいです。その中に無駄があると、その無駄に"意味があるのでは？"と勘違いします。ですから、数式内で無駄な記述は極力避けるべきです。
さてさて、無駄な括弧があるなんてのは序の口です。この数式を見て、まず思うことは「はぁ？FIXED関数？って何？」じゃありませんか？私も久しぶりで見ました。日本国民の90%くらいはご存じないでしょうから、まずFIXED関数についてご紹介します。
FIXED関数は、次の働きをします。
- 数値を指定した桁で四捨五入する
- 桁区切りのカンマを表示する
- 変換した結果を文字列で返す
こんな関数、知らなかったでしょ。知らなくて当然です。実務で使う機会はまずありません。1.の四捨五入だったらROUND関数を使えばいいです。2.と3.に関しては、迷うことなくTEXT関数ですよね。ちなみに、この回答の数式でやっていることは、桁区切りのカンマを表示するだけです。さらに、そうして桁区切りをつけた結果に対して、最後に"円"を文字列結合しています。だったら、TEXT関数で一発でしょ。
なんで一発でできることを、わざわざ誰も知らないであろう超レア関数を使って二度手間にしているのでしょうね。もしかして「いや、FIXED関数の方が文字数が少ないし…」って言うのなら、じゃ、なんでVALUE関数を使ったのかと。なんで無駄な括弧があるのかと。一貫性に欠けるというか、数式全体に作者の主義や主張を感じませんね。さらにですよ、上でも書きましたが、なんで合計した結果に文字の"円"をつけちゃうのかと。ここは表示形式でしょ。セルに文字の"円"が入力されちまってる～ってのが今回の発端なのですから、それと同じことをしちゃダメですよ。ああ！ひとつ思いつきました。もしかしたら質問者が「結果は文字列にしたい」というようなリクエストをしたのでしょうか。すみません、そこまで細かい情報は得ていません。もしそうだとしたら、百歩譲って"円"をつけるのもやむを得ないですね。だけど、それでも使うならTEXT関数ですよ。ちなみに、FIXED関数というのは、Lotus 1-2-3の時代からあった関数です。Lotus 1-2-3には"表示形式"という仕組みや概念がありませんでした。だから、桁区切りのカンマや、助数詞などは文字列としてセルに入力するしかなかったです。FIXED関数は、その時代の名残です。
最後にもうひとつ、この数式で変なところをご紹介します。FIXED関数の書式は次のとおりです。
FIXED(数値,桁数,桁区切り)
引数「数値」には、変換する元の数値を指定します。引数「桁数」は省略可能です。四捨五入する桁を指定します。この引数を省略すると「2」が指定されたものとして、小数第三位を四捨五入して小数第二位までを表示します。引数「桁区切り」は省略可能です。TRUEまたはFALSEを指定します。省略するかFALSEを指定すると桁区切りを表示し、TRUEを指定すると桁区切りを表示しません。
以上のことから分かるように、FIXED関数の第2引数には"数値"を指定し、第3引数に"TRUE"または"FALSE"を指定します。さて、この数式を見てみましょう。
分かりますか？FIXED関数の第2引数「桁数」に"FALSE"を指定しています。あれ？第2引数の「桁数」って数値を指定するんじゃ？TRUEかFALSEって、その後ろの第3引数「桁区切り」では？そう、ここは数値の「0」を指定するところです。数式内でのFALSEは「0」として扱われますので、結果的にここは「0」を指定したことになっています。なってますけど、なんで？逆なら分かります。たとえばVLOOKUP関数の第4引数に、FALSEでなく0を指定する人は多いです。
おおむね、その理由は「0を入力する方が(入力が)速いから」でしょうね。まぁ、一理あります。VBAでも「ActiveCell.Address(False, False)」を「ActiveCell.Address(0, 0)」って打つことがあります。でも、逆はないでしょ。「0」って入力するところに、あえて"FALSE"ってｗ。これ、たぶんですけど、FIXED関数の引数を正しく理解していないのではないでしょうかね。
こうした数式を「これでできるっしょ」と示されて、数式の内容を解説してくださいと聞いたら「そんなの自分で考えろ！理解できないのはあなたの勉強不足だ」みたいに言われたらしいです。かわいそうに。まぁ正直に言って、勉強不足なのはどちらでしょうかね～って、私は感じました。