転職先で周りから「できる！」と言われるためのOfficesoft極意まとめ

エクセル（Excel）関数を使った集計表の作り方を解説

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

図のように購入日、顧客情報、商品をどこの店舗で購入したかなど、多くの要素を詰め込んだ１カ月の売上一覧表を作成しました。その中から、店舗ごとの売上など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関数のように条件を複数設定しなくても「条件を範囲で指定」することができます。

1. 合計を表示させたいセルを選択します。［データベース関数］は［数式］タブに表示されていないので、Shiftキー+F3を押して関数の挿入ダイアログを表示し、関数の分類で［データベース関数］を選択して、 DSUM関数を起動します。
2. 次のように引数を入力します。
   データベース：F3を押して定義された名前の一覧から「売上3月」を選択します。
   フィールド：合計したいフィールドの名前が入ったセル（G1）をクリックします。
   条件：条件に使用したいフィールド名が入ったセルと、条件が入力されるセルをまとめて範囲指定（A1：F2）します。
   


3. OKをクリックします。
   あとは、条件を追加したり削除したりすることで自由に金額の合計を求めることができます。
   

ポイント

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

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

売上の集計結果に順位を付けるには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（＝検査値と等しい値を検索）を入力します。
   


5. 数式バーのINDEXのスペル内をクリックして、INDEXの引数画面に戻ります。
   列番号：配列で指定した範囲の1列目の情報を表示したいので1を入力します。
   


6. 数式をコピーして、INDEX関数の列番号を「3」に変更すると売上個数が求められます。
   

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

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

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