なぜか知られていない・使われないエクセル関数を５つ言ってやる【仕事に役立つエクセル】

2016年10月31日

「#DIV/0!」ってなんだ？　ま、いっか。

というあなた。ほんとうにそれでイイんですか？それ、ちょっと関数を知っていれば解決する問題ですよ。

そんな「なぜか知られていない・使われていない」エクセル関数を５つご紹介します。さて、あなたはいくつ使っていますか？

「なんかめんどっちいな」「なんかおかしいな」と思ってもなにもしない人たち

仕事をしているうえでよく使われるツール「エクセル」。とても便利で、役に立つ強力なツールです。

そんなことで多くの人が使っているにもかかわらず。ときどき驚くべき光景を見かけることがあります。

えっ、そんなめんどうなコトしちゃってんの？

エクセルの関数を使えばもっと早く済むのにね。そんな光景です。エクセルには、使えばラクができるたくさんの関数が用意されています。

また、おかしな状況を放っておくという光景も散見されます。たとえば、冒頭の「#DIV/0!」もそうですね。そんな表示が、配布物にまで残っていると思わず「う～ん」と唸ってしまいます。

もちろん、わたしも関数のすべてを覚えているわけではありませんが。エクセルを使ううえで大事なことは、

「なんかめんどっちいな、なんかおかしいな」と感じたら、解決できる関数がないかを調べてみることです。書籍やネットでいくらでも調べられます。

ということで、今回はそのきっかけとして。なぜか知られていない・使われないエクセル関数を５つお話しします。

エクセルにはさまざまなエラー表示がありますが。よく見るエラーに「#DIV/0!」というものがあります。これは、「分母がゼロの割り算」を行ったときのエラーです。

この「#DIV/0!」がさいごまで残ったままのエクセルは、やはりカッコ悪いです。見ている側は、エラーが気になってしまいます。こんなカンジです ↓

「購買率＝購買客数／来店者数」ということで算式が組まれています。これについて、たとえばF7は「E7/D7」という算式であるため分母がゼロ。ゆえに、「#DIV/0!」となっているわけです。

これを解決する関数が、IFERRORです。さきほどのF7であれば「IFERROR（E7/D7,””）」とすることで、エラー表示ではなく「空白」になります。

算式中の「””」がERROR値に換えて返す値です。たとえば、「””」の部分を「”データなし”」とした場合には、空白ではなく「データなし」と表示されることになります。

たくさんのデータを扱う場合、フィルター機能を使うこともあるでしょう。フィルター機能を使えば、多くのデータの中から条件に合ったものだけを抜き出すことができて便利です。

この条件に合ったデータだけを集計したい。そんなときに使える関数がSUBTOTALです。例で確認をしてみましょう。

全体では店舗数10店の表ですが、「店舗規模」が「大」の店舗だけをフィルタ機能で抽出したものです。では、これについて、「店舗規模大」の来店者数総計を求めるにはどうしたらよいでしょう？

総計と言えば思い浮かぶドメジャー関数のSUMですが、これではうまく計算することができません。じゃあ、1個づつ足してみます？「＝E4+E8＋E13」って、これも正解ではありますが。数が多くなったらタイヘンです。

ということで結論。SUBTOTALを使います。たとえばE14に答えを表示するならば、「＝SUBTOTAL（9,E4:E13）」です。「E4:E13」とあるように、表の上から下までを範囲に指定するのがポイントです。

また、算式中の「9」ってナニ？ということですが、ここを変えることによって、SUBTOTALではいろいろな集計ができます。参考に、集計方法を下記に掲載しておきます。

数量×単価の合計を一発で計算する。そんなときに活躍するのがSUMPRODUCTです。たとえば金種表。現金残高の合計を計算するときに使う表です。

10月1日の現金残高を計算するのに、「10,000×枚数＋5,000×枚数＋2,000×枚数・・・」なんて算式を組むなんてやめましょう。タイヘンですから。SUMPRODUCTならば、

「M4＝SUMPRODUCT（C3:L3,C4:L4）」以上で済むハナシです。「C3:L3」が「金種（単価）」の範囲、「C4:L4」が「数量」の範囲を表しています。

意外にも、計算結果について「端数処理」をしない人がけっこういます。

計算の結果、小数点以下の端数が不自然なデータというものがあります。たとえば、「予測来店者数」などについて。計算結果の「0.3人」が実際に来店することはありません。人間はゼロか1人のどちらかですよね。

また、端数処理を放置した場合。以降の計算に無自覚に影響を及ぼしてしまうことになります。表示上は整数に見えていても、小数点以下の端数が残っていることもあります。

そんなことがないように端数処理をおこなう関数はいくつか用意されています。なかでも最もシンプルなものがINTです。使い方はカンタン。「＝INT（算式or数値）です。これで小数点以下を切り捨てます。

端数処理には、ほかにもROUND、ROUNDUP、ROUNDDOWNがありますが。切り上げや四捨五入などが必要ないときは、INTで十分です。覚えておきましょう。

膨大なデータを扱っていると、「表記の違い」に苦しむことがあります。たとえば、半角カナと全角カナの違い。「○○サービス」と「○○ｻｰﾋﾞｽ」が混じっている･･･

これでは「○○サービス」の集計などがうまくできません。ひとつひとつ手で修正していたのではかないません。そんなときは、JISを使っていちどデータを整理しましょう。こんなカンジです ↓

もともとのデータのC列では、全角カナと半角カナが混在しています。そこでF列に整理してみたという例です。「F3＝JIS（C3）」とし、あとは下列にコピーであっという間におしまいです。

このように文字を操作する関数はほかにもありますので、参考に掲載しておきます。