経理で役立つエクセル講座
複数条件を満たすデータの集計方法(応用編)
みなさん、こんばんは。ねむねむ星人です。ゴールデンウイーも
前半が終了。やりたいことは多々あれど、実現できたことは、ほ
んの僅か。欲張り過ぎはいけませんね。さて、前回のエクセル講
座ではDSUM関数を説明しました(興味のある方はバックナンバ
ーを見て下さい)。DSUM関数は、複数条件を満たすデータの合
計を求める関数ですが、数式ごとに検索条件を入力した別表を作
成しなければならないという欠点がありました。欲張りすぎると、
別表だらけの見づらいシートになってしまい、欲張り星人には、
使い勝手が悪いかもしれません。そこで登場するのが配列数式で
す。今回は複数条件を満たすデータの集計方法(応用編)として
配列数式について、プチッとお伝えいたします。それでは始めま
しょう。
配列数式とは、IF関数によって検索条件を満たすデータのみを
抽出し、その抽出データの集まりを引数とする集計関数(例えば、
SUM、AVERAGEなど)のことで、下記のように数式の並び順が
決まっています。
{=集計関数(IF(検索範囲=条件、集計範囲、“”))}
※中括弧{}は、配列数式の証であり、数式の確定時に
「Ctrl」「Shift」「Enter」を押すことで表示されます。
<複数条件を設定する場合>
{=集計関数(IF(検索範囲1=条件1)*(検索範囲2=
条件2)、集計範囲、“”))}
※(「*」=「かつ」の意味、「+」=「または」の意味)
それでは、下記の数値例を使って、具体的に見ていきましょう。
今回は(条件1)「居眠り商事」に対する(条件2)「ねむねむ枕」
の売上高を合計することにします。
<ねむねむ商店取引台帳(4月分)>
------------------------------------------------------------
A B C D
1 取引日 取引先 商品名 売上高
2 4/3 早起き物産 ねむねむ枕 32000
3 4/4 居眠り商事 ねむねむアイマスク 15000
4 4/6 居眠り商事 ねむねむ枕 48000
5 4/11 寝坊産業 ねむねむ耳栓 8450
6 4/16 居眠り商事 ねむねむ耳栓 4550
7 4/20 寝坊産業 ねむねむアイマスク 19500
8 4/23 居眠り商事 ねむねむ枕 22400
9 4/25 早起き物産 ねむねむ枕 25600
それでは、上記の数式にひとつずつ入力して行きましょう。まず
売上高の「合計」ですから、集計関数には「SUM」を入力します。
「(検索範囲1=条件1)」に関しては、具体的な取引先名が入力
されているセル範囲(B2:B9)のうち「居眠り商事」と一致するも
のですから、「B2:B9=“居眠り商事”」を、同様に「(検索範囲2
=条件2)」に関しては、具体的な商品名が入力されているセル範囲
(C2:C9)のうち「ねむねむ枕」と一致するものですから、「C2:
C9=“ねむねむ枕”」を入力します。また「居眠り商事」に対す
る「ねむねむ枕」の売上高合計ですから、2つの条件を「*」で
つなぎ、「売上高合計」ですから「集計範囲」に「D2:D9」を入力
します。最後に「Ctrl」「Shift」「Enter」を同時に押して確定し
ます。以上をまとめると、{=SUM(IF((B2:B9=“居眠り商事”)
*(C2:C9=“ねむねむ枕”)、D2:D9、“”))}となって完成で
す。配列数式ならば、数式の「(検索範囲=条件)」部分を追加す
るだけ で、好きなだけ検索条件を増やすことができます。また集
計関数の部分を「AVERAGE」「MAX」等に置き換えることで、複数
条件を満たすデータの平均値や最大値を求めることもできます。
どうですか、前回のDSUM関数が煩わしく感じませんか。複数条件
を満たすデータの集計をする際には、是非とも配列数式をお試し
下さい。それでは本日は、ここまで。ねむねむ。
前半が終了。やりたいことは多々あれど、実現できたことは、ほ
んの僅か。欲張り過ぎはいけませんね。さて、前回のエクセル講
座ではDSUM関数を説明しました(興味のある方はバックナンバ
ーを見て下さい)。DSUM関数は、複数条件を満たすデータの合
計を求める関数ですが、数式ごとに検索条件を入力した別表を作
成しなければならないという欠点がありました。欲張りすぎると、
別表だらけの見づらいシートになってしまい、欲張り星人には、
使い勝手が悪いかもしれません。そこで登場するのが配列数式で
す。今回は複数条件を満たすデータの集計方法(応用編)として
配列数式について、プチッとお伝えいたします。それでは始めま
しょう。
配列数式とは、IF関数によって検索条件を満たすデータのみを
抽出し、その抽出データの集まりを引数とする集計関数(例えば、
SUM、AVERAGEなど)のことで、下記のように数式の並び順が
決まっています。
{=集計関数(IF(検索範囲=条件、集計範囲、“”))}
※中括弧{}は、配列数式の証であり、数式の確定時に
「Ctrl」「Shift」「Enter」を押すことで表示されます。
<複数条件を設定する場合>
{=集計関数(IF(検索範囲1=条件1)*(検索範囲2=
条件2)、集計範囲、“”))}
※(「*」=「かつ」の意味、「+」=「または」の意味)
それでは、下記の数値例を使って、具体的に見ていきましょう。
今回は(条件1)「居眠り商事」に対する(条件2)「ねむねむ枕」
の売上高を合計することにします。
<ねむねむ商店取引台帳(4月分)>
------------------------------------------------------------
A B C D
1 取引日 取引先 商品名 売上高
2 4/3 早起き物産 ねむねむ枕 32000
3 4/4 居眠り商事 ねむねむアイマスク 15000
4 4/6 居眠り商事 ねむねむ枕 48000
5 4/11 寝坊産業 ねむねむ耳栓 8450
6 4/16 居眠り商事 ねむねむ耳栓 4550
7 4/20 寝坊産業 ねむねむアイマスク 19500
8 4/23 居眠り商事 ねむねむ枕 22400
9 4/25 早起き物産 ねむねむ枕 25600
それでは、上記の数式にひとつずつ入力して行きましょう。まず
売上高の「合計」ですから、集計関数には「SUM」を入力します。
「(検索範囲1=条件1)」に関しては、具体的な取引先名が入力
されているセル範囲(B2:B9)のうち「居眠り商事」と一致するも
のですから、「B2:B9=“居眠り商事”」を、同様に「(検索範囲2
=条件2)」に関しては、具体的な商品名が入力されているセル範囲
(C2:C9)のうち「ねむねむ枕」と一致するものですから、「C2:
C9=“ねむねむ枕”」を入力します。また「居眠り商事」に対す
る「ねむねむ枕」の売上高合計ですから、2つの条件を「*」で
つなぎ、「売上高合計」ですから「集計範囲」に「D2:D9」を入力
します。最後に「Ctrl」「Shift」「Enter」を同時に押して確定し
ます。以上をまとめると、{=SUM(IF((B2:B9=“居眠り商事”)
*(C2:C9=“ねむねむ枕”)、D2:D9、“”))}となって完成で
す。配列数式ならば、数式の「(検索範囲=条件)」部分を追加す
るだけ で、好きなだけ検索条件を増やすことができます。また集
計関数の部分を「AVERAGE」「MAX」等に置き換えることで、複数
条件を満たすデータの平均値や最大値を求めることもできます。
どうですか、前回のDSUM関数が煩わしく感じませんか。複数条件
を満たすデータの集計をする際には、是非とも配列数式をお試し
下さい。それでは本日は、ここまで。ねむねむ。
掲載日:
※本コラムに掲載されている情報は、掲載した時点での情報です。
弊社は掲載された内容に関し、如何なる保証もするものではありません。
また、記載されている事項は変更される場合がありますので、予め御承知おき下さい。
弊社は掲載された内容に関し、如何なる保証もするものではありません。
また、記載されている事項は変更される場合がありますので、予め御承知おき下さい。
RANKINGオススメ
サービス
サービス
1位
2位
3位
SEMINAR新着
セミナー
セミナー
- 2024/05/10(金) 【オンライン開催】制度開始後に経理担当者がやるべき インボイス制度の経理実務対策のポイント
- 2024/05/10(金) 2024年度 労働保険年度更新・社会保険算定セミナー
- 2024/05/12(日) 【WEB配信】【受講満足度90%以上】採用担当者が目をつけるポイントがわかる!経理職のための履歴書・職務経歴書の書き方
- 2024/05/16(木) 経理実務担当者養成セミナー【決算書の見方・読み方・経営分析編】
- 2024/05/19(日) 次世代の経理担当者として活躍するためのキャリアアップ術
- 2024/05/23(木) はじめての給与計算と社会保険の基礎セミナー
- 2024/05/24(金) 経理実務担当者養成セミナー【会社の税金入門編】
- 2024/05/24(金) 【WEB動画】緊急配信!定額減税の実務対応セミナー
- 2024/05/29(水) はじめての給与計算と社会保険の基礎セミナー
- 2024/06/04(火) はじめての給与計算と社会保険の基礎セミナー
- 2024/06/07(金) 経理実務担当者養成セミナー【消費税の実務知識と申告書の作成編】
- 2024/06/11(火) 経理実務担当者養成セミナー【簿記会計入門編】
- 2024/06/13(木) 経理実務担当者養成セミナー【資金繰りの実務とキャッシュフロー計算書作成編】
- 2024/06/21(金) 経理実務担当者養成セミナー【法人税の実務知識編】
- 2024/06/23(日) 【WEB配信】経理担当者として必須の戦略思考力がみにつく!経理担当者のためのKPI実務セミナー
- 2024/06/25(火) はじめての給与計算と社会保険の基礎セミナー
- 2024/06/30(日) 効率的に作る・業績改善のために考える!「月次決算の分析・活用」実践術
- 2024/07/04(木) はじめての給与計算と社会保険の基礎セミナー
- 2024/07/06(土) 税理士事務所に入所3年以内の職員なら知っておきたい 消費税の仕訳実務
- 2024/07/20(土) 新会計基準もスッキリわかる! 國貞克則氏監修「財務3表一体理解法マスター講座」
- 2024/07/26(金) はじめての給与計算と社会保険の基礎セミナー
- 2024/08/29(木) はじめての給与計算と社会保険の基礎セミナー