「#DIV/0!」ってなんだ？ ま、いっか。
というあなた。ほんとうにそれでイイんですか？それ、ちょっと関数を知っていれば解決する問題ですよ。
そんな「なぜか知られていない・使われていない」エクセル関数を５つご紹介します。さて、あなたはいくつ使っていますか？
「なんかめんどっちいな」「なんかおかしいな」と思ってもなにもしない人たち
仕事をしているうえでよく使われるツール「エクセル」。とても便利で、役に立つ強力なツールです。
そんなことで多くの人が使っているにもかかわらず。ときどき驚くべき光景を見かけることがあります。
えっ、そんなめんどうなコトしちゃってんの？
エクセルの関数を使えばもっと早く済むのにね。そんな光景です。エクセルには、使えばラクができるたくさんの関数が用意されています。
また、おかしな状況を放っておくという光景も散見されます。たとえば、冒頭の「#DIV/0!」もそうですね。そんな表示が、配布物にまで残っていると思わず「う～ん」と唸ってしまいます。
もちろん、わたしも関数のすべてを覚えているわけではありませんが。エクセルを使ううえで大事なことは、
「なんかめんどっちいな、なんかおかしいな」と感じたら、解決できる関数がないかを調べてみることです。書籍やネットでいくらでも調べられます。
ということで、今回はそのきっかけとして。なぜか知られていない・使われないエクセル関数を５つお話しします。
- IFERROR
- SUBTOTAL
- SUMPRODUCT
- INT
- JIS
IFERRORでエラー表示とはサヨウナラ
エクセルにはさまざまなエラー表示がありますが。よく見るエラーに「#DIV/0!」というものがあります。これは、「分母がゼロの割り算」を行ったときのエラーです。
この「#DIV/0!」がさいごまで残ったままのエクセルは、やはりカッコ悪いです。見ている側は、エラーが気になってしまいます。こんなカンジです ↓
「購買率＝購買客数／来店者数」ということで算式が組まれています。これについて、たとえばF7は「E7/D7」という算式であるため分母がゼロ。ゆえに、「#DIV/0!」となっているわけです。
これを解決する関数が、IFERRORです。さきほどのF7であれば「IFERROR（E7/D7,””）」とすることで、エラー表示ではなく「空白」になります。
算式中の「””」がERROR値に換えて返す値です。たとえば、「””」の部分を「”データなし”」とした場合には、空白ではなく「データなし」と表示されることになります。
フィルターの集計ならSUBTOTALで
たくさんのデータを扱う場合、フィルター機能を使うこともあるでしょう。フィルター機能を使えば、多くのデータの中から条件に合ったものだけを抜き出すことができて便利です。
この条件に合ったデータだけを集計したい。そんなときに使える関数がSUBTOTALです。例で確認をしてみましょう。
全体では店舗数10店の表ですが、「店舗規模」が「大」の店舗だけをフィルタ機能で抽出したものです。では、これについて、「店舗規模 大」の来店者数総計を求めるにはどうしたらよいでしょう？
総計と言えば思い浮かぶドメジャー関数のSUMですが、これではうまく計算することができません。じゃあ、1個づつ足してみます？「＝E4+E8＋E13」って、これも正解ではありますが。数が多くなったらタイヘンです。
ということで結論。SUBTOTALを使います。たとえばE14に答えを表示するならば、「＝SUBTOTAL（9,E4:E13）」です。「E4:E13」とあるように、表の上から下までを範囲に指定するのがポイントです。
また、算式中の「9」ってナニ？ということですが、ここを変えることによって、SUBTOTALではいろいろな集計ができます。参考に、集計方法を下記に掲載しておきます。
| 値 | 集計 |
| 1 | 平均 |
| 2 | 数値の個数 |
| 3 | 文字列、数値データなどの個数 |
| 4 | 最大値 |
| 5 | 最小値 |
| 6 | 積 |
| 7 | 標本標準偏差 |
| 8 | 標準偏差 |
| 9 | 合計 |
| 10 | 標本分散 |
| 11 | 分散 |
金種表ならSUMPRODUCTで一発計算
数量×単価の合計を一発で計算する。そんなときに活躍するのがSUMPRODUCTです。たとえば金種表。現金残高の合計を計算するときに使う表です。
10月1日の現金残高を計算するのに、「10,000×枚数＋5,000×枚数＋2,000×枚数・・・」なんて算式を組むなんてやめましょう。タイヘンですから。SUMPRODUCTならば、
「M4＝SUMPRODUCT（C3:L3,C4:L4）」以上で済むハナシです。「C3:L3」が「金種（単価）」の範囲、「C4:L4」が「数量」の範囲を表しています。
INTを使って、もう端数処理を放置しない
意外にも、計算結果について「端数処理」をしない人がけっこういます。
計算の結果、小数点以下の端数が不自然なデータというものがあります。たとえば、「予測来店者数」などについて。計算結果の「0.3人」が実際に来店することはありません。人間はゼロか1人のどちらかですよね。
また、端数処理を放置した場合。以降の計算に無自覚に影響を及ぼしてしまうことになります。表示上は整数に見えていても、小数点以下の端数が残っていることもあります。
そんなことがないように端数処理をおこなう関数はいくつか用意されています。なかでも最もシンプルなものがINTです。使い方はカンタン。「＝INT（算式or数値）です。これで小数点以下を切り捨てます。
端数処理には、ほかにもROUND、ROUNDUP、ROUNDDOWNがありますが。切り上げや四捨五入などが必要ないときは、INTで十分です。覚えておきましょう。
JISで半角カナを全角カナに統一せよ
膨大なデータを扱っていると、「表記の違い」に苦しむことがあります。たとえば、半角カナと全角カナの違い。「○○サービス」と「○○ｻｰﾋﾞｽ」が混じっている･･･
これでは「○○サービス」の集計などがうまくできません。ひとつひとつ手で修正していたのではかないません。そんなときは、JISを使っていちどデータを整理しましょう。こんなカンジです ↓
もともとのデータのC列では、全角カナと半角カナが混在しています。そこでF列に整理してみたという例です。「F3＝JIS（C3）」とし、あとは下列にコピーであっという間におしまいです。
このように文字を操作する関数はほかにもありますので、参考に掲載しておきます。
| 関数 | 効果 |
| ASC | 全角カナを半角カナに変換する |
| TRIM | 不要なスペースを削除する |
| VALUE | 「文字列」として入力されている「数字」を数値に変換する |
まとめ
「なぜか知られていない・使われていない」エクセル関数についてお話してきました。知らないもの、使っていないものもあったのではないでしょうか。
不便だなと思うようなことは、面倒がらずにそのときに一度調べてみましょう。この一度の手間を惜しまないことが、のちのちのずっと大きな手間を省くことにつながります。
＊＊＊＊＊＊＊＊＊＊＊＊
きょうの執筆後記
＊＊＊＊＊＊＊＊＊＊＊＊
どうも「1度の手間」を惜しむ傾向がヒトにはあるようです。ほかにイイ方法がありそうだと感じながらも、知っている方法で済ませてしまう。そんな傾向です。
油断をしていると、わたしもやってしまいます。とにかく、いま早く終わらせたいんだ！みたいな時です。結果、次回も同じことに繰り返し時間をかけてしまいます。
目の前の時間ではなく、向こう先々までの時間を意識することでその傾向は変えられます。そのためには、目の前の時間に忙殺されない、ある程度の余裕をもってコトに取りかからねば･･･
