欲しい集計、出せますか? Excelで覚えたい2つの関数Excel編(4)

日々の勤務中、あなたはどのくらいパソコンの前にいますか? 1日あたり10分短縮すると、年間では(休日105日の場合)約43時間に膨らみます。それだけの時間を他の仕事に回せたとしたら――。資料を見る人のために、どのように作れば理解されやすいか。そしてビジネスパーソンがよく使うマイクロソフトのWord(ワード)とExcel(エクセル)のどちらが、より早くきれいに仕上げられるか。パソコン講師の四礼静子さんと、基本ポイントを押さえていきましょう。

「今月の売上合計は?」「今回の参加者人数は?」などなど、業務の中で合計を求めることはどんな業種でもありますよね。普段からExcelを使っている人は、まず指定した範囲内の数値の合計値を求める「SUM関数」を覚えるのではないでしょうか。

ところが「○○社の売り上げだけ集計して!」と言われたら、どうしますか? ○○社のデータだけを抜き出して、SUM関数で合計して……なんてことをしていませんか?

素早く作業を終えるために

●指定した範囲から特定の条件に合うデータのみ集計し合計を出す「SUMIF関数」
→SUM+IF=「こうであるならば合計は?という関数」と覚えると便利
●同じく特定の条件に該当するデータがいくつあるかを求める「COUNTIF関数」
→COUNT+IF=「こうであるならばいくつか?という関数」と覚えると便利

は必ず覚えておきたい関数です。今回はこの2つの関数について、分かりやすく設定する方法をご紹介します。

まず何を求めたいか文章で考えよう

集計を行うということは、下の画像のように元データの他に、必ず集計用の表を作ることが前提となります。最初に行うのは、集計表(下の図の右側部分)のどのセルに集計した値を出すのか、結果を表示させる「セルを選択」することです(今回の場合は下の図の緑の枠で囲ってあるK4が、それにあたります)。セルを選択すると、おのずと条件が見えてきます。

Excelで数式を立てるときは、まず、何を求めたいのかを文章にして考えていきましょう。今さっきも確認していただいた、上の「集計表」を例に説明します。この集計表では、何が「求めたいこと」になるでしょうか。「(株)すぎなみ」の右側のセル(緑の枠で囲ってあるところ)を見て、ちょっと考えてみてください。

上の「元データ」と「集計表」を見比べると、元データには「得意先」や「品名」「数量」などが並んでいますが、集計表の方は「(株)○○」という得意先名だけがズラッと並んでいます。さらに「得意先別売上一覧」とあることから「元データに載っている情報から、○○社との取引だけを抜き出して、その金額を合計する」というのが「求めたいこと」だと分かりますよね。

そして、ここでは「(株)すぎなみ」が「特定の条件」になります。表計算では一度つくった数式を次のセルにもコピーしていくこと(同じ数式を使い回すこと)を前提に式を組み立てます。

上の「集計表」の場合、「(株)すぎなみ」の次(下)のセルには「(株)せたがや」、その次は「(株)たいとう」とあります。つまり、集計結果を表示するセルが1行ずつ下にずれると同時に、つくった数式の「特定の条件」が変わっていく(例:「(株)すぎなみ」としたのが、下のセルでは「(株)せたがや」、その次は「(株)たいとう」……と入れ替わっていく)こととなります。

しかし、しかしですよ。会社名を探す「得意先」と合計したい「金額」の範囲は、条件が変わっても同じでなくてはならないのです。そこで、前回(「Excel数式これが基本 入力効率アップ、共有スムーズ」)ご紹介した「絶対参照」を使うことになります。ということは、これも前回ご紹介した「名前の定義」を使うことで、数式を簡単にわかりやすく組むことができますよね。上にある「元データ」の表全体を選択して①「数式」タブをクリック②「定義された名前」グループから「選択範囲から作成」をクリックして、各列に名前を定義しておきましょう。

ここまでの操作はOKでしょうか。前回の復習も兼ねて、動画も掲載しておきますね。

得意先別の売り上げを出してみよう

●SUMIF関数=条件が1つの合計

さて、「名前の定義」は終わっていますか? ここから定義した名前(例:B列は「得意先」)を使って数式を組んでいきます。では、元データから「(株)すぎなみを探し、その売上金額のみを合計」という場合の数式を集計表に入力していきましょう。

1:「(株)すぎなみ」の合計を求めたいセル(=K4)を選択
2:数式タブ→関数ライブラリ→数学/三角関数から[SUMIF]をクリック

3:引数を入力(範囲=「得意先」/検索条件=「J4」/合計範囲=「金額」)

※冒頭でお示しした「集計表」の「J4」のセルが「(株)すぎなみ」なので、検索条件は「J4」となっています。元データの「得意先」の列から「検索条件」となる「(株)すぎなみ」の「金額」だけを合計する、という意味になります。

記事ランキング
一覧ページへ
日経ビジネススクール
次のページ
数値もテキストもカウント可能