転職ノウハウ

応募企業の探し方や履歴書の書き方、面接のポイントから円満退職の秘けつまで。あなたの転職を成功に導くためのノウハウを紹介!

Excel関数でできるデータ集計のすべて! レポート業務はこれさえ覚えれば間違いない

Excelで作ったデータグラフの写真

《この記事で扱う関数》

  • 表とフィールドに名前を定義する
  • 店舗別の売上金額の合計を求める:SUMIF関数(1つの条件)
  • 各店舗の区分別売上の合計を求める:SUMIFS関数(複数条件)
  • 店舗別の取引件数を求める:COUNTIF関数(1つの条件)
  • 店舗・商品区分別の取引件数を求める:COUNTIFS関数(複数条件)
  • 複数の条件を自由に変化させて集計を求める:SUMIFS関数
  • 売上個数の多い順に順位を付ける:RANK.EQ関数

合計を求めるSUM関数は、日頃からよく使用している関数ですね。
では、たくさんの要素が含まれる表の中から、特定の条件を満たすものだけの合計はどうやって求めていますか?

SUM関数の説明画像

図のように購入日、顧客情報、商品をどこの店舗で購入したかなど、多くの要素を詰め込んだ1カ月の売上一覧表を作成しました。その中から、店舗ごとの売上など1つの条件を満たすデータの合計を求めたい場合、さらに店舗や顧客種別、購入商品など、合計をする際に絞りたい条件が2つ以上ある場合は、どうでしょうか。
フィルターで絞り込み、別シートにコピーして合計を求める……、なんて手間をかけてはいませんか?

条件が3つ以上でも、名前定義と関数だけでさくっと集計

エクセルで複雑な操作をしようとすると、数式も複雑になりがちです。しかし、作成した表を社内で使いまわす場合などは、誰が見ても分かりやすく、使いやすいものであることが求められます。
その場合、あらかじめ表やフィールドに名前定義をすると、あとで数式を組む時に
・範囲指定が簡単に行える
・数式がぱっと見て分かりやすくなる
・絶対参照の設定忘れが無くなる
などの利点があります。
ここからは、図で示した例を使って実際の操作方法を説明していきます。

表とフィールドに名前を定義する

集計を始める前に、まず表とフィールドにそれぞれ名前定義を行いましょう。

  1. 3月売上シートのデータ内をクリックします。
  2. Ctrlキー+Aで表の一発選択を行います。
  3. シート左上の名前ボックスをクリックし「売上3月」と入力します。(数字から始まる名前は定義できません)
  4. 日本語変換を確定したあと、再度Enterキーを押して入力した名前を確定します。ここできちんと確定しておかなければ、名前定義がされないままエラーになることがあります。《表の名前定義》
  5. 表とフィールドに名前を定義する方法の説明画像
  6. [数式]タブの[定義された名前]グループの中の[選択範囲から作成]をクリックします。
  7. 左端列の☑をオフにし、OKをクリックします。《フィールドの名前定義》 表とフィールドに名前を定義する方法の説明画像

定義された名前の一覧を呼び出すには、Ctrlキー+F3を押します。
すべてのフィールドに名前が定義されていることを確認しましょう。

表とフィールドに名前を定義する方法の説明画像

店舗別の売上金額の合計を求める:SUMIF関数(1つの条件)

名前定義ができたら、各店舗の集計を求めていきましょう。

店舗別の売上金額の合計を求める方法の説明画像
  1. 合計を表示させたいセルを選択し、[数式]タブの[関数ライブラリ]グループの中から[数学/三角]をクリック。SUMIF関数を起動します。
  2. 次のように引数を入力します。
    範囲:F3を押し、定義された名前の一覧から「店舗」を選択します。
    検索条件:新規作成した各店舗の売上合計表の店舗名のセル(A2)をクリックします。
    合計範囲:F3を押し、定義された名前の一覧から「金額」をクリックします。 店舗別の売上金額の合計を求める方法の説明画像
  3. 数式をコピーしすべての店舗の集計を求めます。 店舗別の売上金額の合計を求める方法の説明画像

ポイント

罫線や網掛けが設定されている場合は、オプションボタンから[書式なしコピー]をクリックすると、それらの書式を変更することなく数式のコピーが行えます。

各店舗の区分別売上の合計を求める:SUMIFS関数(複数条件)

「渋谷店で、未婚または既婚の顧客それぞれのアクセサリーの売上合計を求めたい」というように条件が2つ以上重なる場合は、SUMIFS関数を使用します。未婚または既婚の集計の切り替えは、1つのセルで行えるようにします。

各店舗の区分別売上の合計を求める方法の説明画像
  1. 合計を表示させたいセルを選択し、[数式]タブの[関数ライブラリ]グループの中から[数学/三角]をクリック。SUMIFS関数を起動します。
  2. 次のように引数を入力します。
    合計対象範囲:F3を押し、定義された名前の一覧から「金額」を選択します。
    条件範囲1:F3を押し、定義された名前の一覧から「区分」を選択します。
    条件1:条件の区分が入力されているセル(J2)をクリックし、行のみ絶対参照(J$2)にします。
    条件範囲2:F3を押し、定義された名前の一覧から「店舗」を選択します。
    条件2:条件の店舗が入力されているセル(I3)をクリックし、列のみ絶対参照($I3)にします。
    条件範囲3:F3を押し、定義された名前の一覧から「結婚」を選択します。
    条件3:条件の「未婚」が入力されているセル(I2)をクリックし、絶対参照($I$2)にします。 各店舗の区分別売上の合計を求める方法の説明画像 各店舗の区分別売上の合計を求める方法の説明画像
  3. 列・行へ数式をコピーしましょう。必要があれば、[書式なしコピー]を使用します。 各店舗の区分別売上の合計を求める方法の説明画像

あとは「結婚」の条件が入力されているセル(I2)を変更するだけで、未婚の方の売上集計、既婚の方の売上集計を求めることができます。

ポイント

SUMIF関数とSUMIFS関数では、引数の入力順序が逆になります。
SUMIF関数では、検索条件、合計範囲の順で指定しますが、SUMIFS関数では、先に合計範囲を指定します。SUMIFS関数ではそのあと、「どの範囲から何を探す」という条件を1セットにして追加していきます。

店舗別の取引件数を求める:COUNTIF関数(1つの条件)

店舗別の取引件数を求める方法の説明画像
  1. 取引件数を表示させたいセルを選択し、[数式]タブの[関数ライブラリ]グループの中から[統計]をクリック。COUNTIF関数を起動します。
  2. 次のように引数を入力します。
    範囲:F3を押し、定義された名前の一覧から「店舗」を選択します。
    検索条件:条件の店舗名が入力されているセル(A2)をクリックします。 店舗別の取引件数を求める方法の説明画像
  3. 数式をコピーし、すべての店舗の取引件数を求めます。必要があれば、[書式なしコピー]を使用します。 店舗別の取引件数を求める方法の説明画像

ポイント

SUMIF関数とCOUNTIF関数の引数の違いは、合計範囲があるかないかです。COUNTIF関数では、データの個数を数えるだけで合計は行わないため、合計範囲の指定は不要です。

店舗・商品区分別の取引件数を求める:COUNTIFS関数(複数条件)

「指定した日付で、店舗ごとにそれぞれの商品区分の取引件数を求めたい」というように条件が2つ以上重なる場合は、COUNTIFS関数を使用します。

店舗・商品区分別の取引件数を求める方法の説明画像
  1. 取引件数を表示させたいセルを選択し、[数式]タブの[関数ライブラリ]グループの中から[統計]をクリック。COUNTIFS関数を起動します。
  2. 次のように引数を入力します。
    条件範囲1:F3を押し、定義された名前の一覧から「区分」を選択します。
    条件1:条件の区分が入力されているセル(E2)をクリックし、行のみ絶対参照(E$2)にします。
    条件範囲2:F3を押し、定義された名前の一覧から「店舗」を選択します。
    条件2:条件の店舗が入力されているセル(D3)をクリックし、列のみ絶対参照($D3)にします。
    条件範囲3:F3を押し、定義された名前の一覧から「日付」を選択します。
    条件3:条件の日付が入力されているセル(D2)をクリックし、絶対参照($D$2)にします。 店舗・商品区分別の取引件数を求める方法の説明画像
  3. 列・行へ数式をコピーしましょう。必要があれば、[書式なしコピー]を使用します。 店舗・商品区分別の取引件数を求める方法の説明画像

あとは「日付」の条件が入力されているセル(D2)を変更するだけで、指定した日の取引件数を求めることができます。

複数の条件を自由に変化させて集計を求める:SUMIFS関数

集計用の表にデータを表示させるのではなく、条件によって異なる集計を求める場合には、下記のような表を作成すると便利です。
SUMIFS関数では、条件設定がすべて満たされたものを合計します。条件がAND条件として処理されますので、すべての条件を入力する必要があります。条件が必要ない場合は、「*(半角アスタリスク)」を入力しましょう。

複数の条件を自由に変化させて集計を求める方法の説明画像

関連関数1: DSUM関数

DSUM関数は、SUMIFS関数のように条件を複数設定しなくても「条件を範囲で指定」することができます。

DSUM関数の説明画像
  1. 合計を表示させたいセルを選択します。[データベース関数]は[数式]タブに表示されていないので、Shiftキー+F3を押して関数の挿入ダイアログを表示し、関数の分類で[データベース関数]を選択して、 DSUM関数を起動します。
  2. 次のように引数を入力します。
    データベース:F3を押して定義された名前の一覧から「売上3月」を選択します。
    フィールド:合計したいフィールドの名前が入ったセル(G1)をクリックします。
    条件:条件に使用したいフィールド名が入ったセルと、条件が入力されるセルをまとめて範囲指定(A1:F2)します。 DSUM関数の説明画像
  3. OKをクリックします。
    あとは、条件を追加したり削除したりすることで自由に金額の合計を求めることができます。 DSUM関数の説明画像

ポイント

DSUM関数ではすべての条件を入力していなくても合計を求めることができます。OR条件で求める場合は、行を分けて条件を作成します。

【その他の関連関数】

SUM関数 COUNT関数 COUNTA関数 AVERAGE関数 DCOUNT関数 DAVERAGE関数

集計するだけからステップアップ! 売上順位を求めてデータの精査をしよう

売上の集計結果に順位を付けるにはRANK.EQ関数を使用します。
例として、アクセサリーの中で売上個数の多い順に順位を付けましょう。

売上個数の多い順に順位を付ける:RANK.EQ関数

順位を付けたい場合は、RANK.EQ関数を使用します。

売上個数の多い順に順位を付ける方法の説明画像
  1. 順位を表示させたいセルを選択し、[数式]タブの[関数ライブラリ]グループの中から[統計]をクリック。RANK.EQ関数を起動します。
  2. 次のように引数を入力します。
    数値:順位を求めたい売上個数のセル(D2)を選択します。
    参照:定義された名前「売上個数」を入力します。(あらかじめD2:D21に名前を定義しておきます。) 売上個数の多い順に順位を付ける方法の説明画像
  3. フィルハンドルをWクリックして、数式をコピーします。 売上個数の多い順に順位を付ける方法の説明画像

ポイント

複数の範囲を参照して順位を求める場合は、引数の「参照」に、定義された名前を「,(カンマ)」で区切って入力します。

1位の商品名を表示する:INDEX・MATCH関数

RANK.EQ関数で求めた順位の1位の商品名を抜き出しましょう。
B列の「購入商品名」からE列の「人気順位」までの範囲で、「1位が何行目にあるのか、表示したい購入商品名は何列目にあるのか」というように、行数と列数が交差したセルにある情報を取り出すには、INDEX関数を使用します。
このとき、1位の行が何行目にあるのかは毎月変化します。1位がある行数を求めるためにはMATCH関数を組み合わせます。

  1. 1位の商品名を表示させたいセルを選択し、[数式]タブの[関数ライブラリ]グループの中から[検索/行列]をクリック。INDEX関数を起動します。
  2. 引数の選択画面では「配列・行番号・列番号」が選択されている状態でOKをクリックします。
  3. 次のように引数を入力します。
    配列:購入商品名から人気順位までのデータの範囲を指定します。
    行番号:名前ボックスからMATCH関数を起動します。
  4. MATCH関数の引数は、次のように入力します。
    検査値:1位の行数を求めたいので1を入力します。
    検査範囲:定義された名前「人気順位」を入力します。(あらかじめE2:E21に名前を定義しておきます。)
    照合の種類:0(=検査値と等しい値を検索)を入力します。 1位の商品名を表示する方法の説明画像
  5. 数式バーのINDEXのスペル内をクリックして、INDEXの引数画面に戻ります。
    列番号:配列で指定した範囲の1列目の情報を表示したいので1を入力します。 1位の商品名を表示する方法の説明画像
  6. 数式をコピーして、INDEX関数の列番号を「3」に変更すると売上個数が求められます。 1位の商品名を表示する方法の説明画像

【その他の関連関数】

LARGE関数 SMALL関数 ROW関数 COLUMN関数

以上、いかがでしたか?
使い方をマスターして、データ集計などのレポート作成に役立ててくださいね。

記事内で使用しているExcelデータのサンプルダウンロードが可能です。

ライティング/四禮 静子(しれい しずこ)

日本大学芸術学部卒業。CATVの制作ディレクターを退職後、独学でパソコンを学び、下町浅草に完全マンツーマンのフォーティネットパソコンスクールを開校して17年目。講座企画からテキスト作成・スクール運営を行う。行政主催の講習会・企業に合わせたオリジナル研修や新入社員研修も行っている。
著書に「ビジネス力がみにつくExcel&Word」(発行:翔泳社)、2016年にはWord・Excelの新刊を2冊同時出版予定。http://www.forty40.com

編集/株式会社スペースシップ

併せて読みたい記事
賛成! あたらしい生き方。マイナビ転職

豊富な転職・求人情報と転職ノウハウであなたの転職活動を支援する【マイナビ転職】。マイナビ転職は正社員の求人を中心に“日本最大級”常時 約8,000件以上の全国各地の豊富な求人情報をご紹介する転職・求人サイトです。毎週火・金更新であなたの希望の職種や勤務地、業種などの条件から検索することができます。職務経歴書や転職希望条件を匿名で登録するとあなたに興味を持った企業からスカウトされるサービスや、転職活動に役立つ職務経歴書サンプルや転職Q&A、会員登録をすると専門アドバイザーによる履歴書の添削、面接攻略など充実した転職支援サービスを利用できる転職サイトです。

キーワードから記事を探す

検索フォーム

人気コンテンツランキング
転職成功ガイド