Excelの押えておきたい関数

販売履歴を商品名・性別の複数条件で販売傾向を集計するCOUNTIFS関数

投稿日:

Excelの覚える関数を減らすシリーズ②
COUNTIFS関数を覚えればCOUNTIF関数は覚えなくて大丈夫!

COUNTIFS関数とは

指定された条件(複数でもOK)に一致するセルの個数を求める

商品購入履歴を商品ごと男性・女性それぞれ何人が購入したかで集計することができます。

COUNTIF関数とCOUNTIFS関数の違い

COUNTIF関数
→指定された1つの条件に一致するセルの個数を求める

COUNTIFS関数
→指定された条件(複数でもOK)に一致するセルの個数を求める

COUNTIFS関数の式

式:COUNTIFS(検索条件範囲1,検索条件1,[検索条件範囲2],[検索条件2])

それぞれの説明
検索条件範囲1:集計の対象としたいセル範囲を指定します。
検索条件1:検索条件範囲1で一致する条件を指定します。

単一条件でのCOUNTIFS関数

商品名のみを条件に集計する。

COUNTIFS関数の手順

①COUNTIFS関数(単一条件)
商品名のみを条件に集計する。

入力するセルを選択し、「=cou」と入力すると入力補助が表示されるので、「COUNTIFS」を選択します。

②COUNTIFS関数(単一条件)
COUNTIFSを選択したら「(」を入力。

③COUNTIFS関数(単一条件)
検索条件範囲1を選択します。
B2:B19を選択します。

④COUNTIFS関数(単一条件)
ファンクションキー「F4」を押して、絶対参照にします。
B2:B19  → $B$2:$B$19

これにより式をコピーして他のセルに貼り付けても、 B2:B19の範囲が必ず参照されます。

⑤COUNTIFS関数(単一条件)
検索条件1は検索条件範囲1内で一致するセルを指定します。
セルH11を選択し、ファンクションキー「F4」を3回押して、相対参照にします。
H11  → $H11

これにより式をコピーして他の列に貼り付けても、H列が必ず参照されます。

⑥COUNTIFS関数(単一条件)
式が完成しました。
=COUNTIFS($B$2:$B$19,$H11)

値は5 商品名「みかん」のデータ数の集計額です。

⑦COUNTIFS関数(単一条件)
式をコピーしてセルI12~14に貼り付けます。
=COUNTIFS($B$2:$B$19,$H●)
●が行と同じ12~14に変わりました。

複数条件でのCOUNTIFS関数

商品名と性別との複数条件に一致する数値の合計を求めます。

COUNTIFS関数の手順

①COUNTIFS関数(複数条件)
さきほどのCOUNTIFS関数をコピーして使いましょう。
セルI14をコピーしてセルJ14に貼り付けます。

セルJ14の式:=COUNTIFS($B$2:$B$19,$H14)

②COUNTIFS関数(複数条件)
すでに検索条件1までは入力されていますので、検索条件範囲2と検索条件2を設定します。
$H14の後に「,(カンマ)」を入れ、

③COUNTIFS関数(複数条件)
「,(カンマ)」の後に検索条件範囲2を設定します。
検索条件範囲2は性別となりますので、C2:C19を範囲しています。

④COUNTIFS関数(複数条件)
ファンクションキー「F4」を押して、絶対参照にします。
C2:C19  → $C$2:$C$19

⑤COUNTIFS関数(複数条件)
検索条件2を選択します。
検索条件範囲2内で一致するセルを指定します。
J10を選択します。

⑥COUNTIFS関数(複数条件)
ファンクションキー「F4」を2回押して、相対参照にします。
J10  → J$10

これにより式をコピーして他の行に貼り付けても、10行目が必ず参照されます。

⑦COUNTIFS関数(複数条件)
式が完成しました。
=COUNTIFS($B$2:$B$19,$H14,$C$2:$C$19,J$10)

値は1 商品名「梨」、性別「男」のデータ数の集計額です。

⑧COUNTIFS関数(複数条件)
式をコピーしてセルK14に貼り付けます。
=COUNTIFS($B$2:$B$19,$H14,$C$2:$C$19,K$10)

最後の「J$10」が「K$10」とだけ変わりました。
値は3 商品名「梨」、性別「女」のデータ数の集計額です。

⑨COUNTIFS関数(複数条件)
他のセルに式をコピーして貼り付けます。
=COUNTIFS($B$2:$B$19,$H●,$C$2:$C$19,●$10)

●の部分のみが変わります。
みかんの行 男の列:$H11、J$10  女の列:$H11、K$10
りんごの行 男の列:$H12、J$10  女の列:$H12、K$10
ももの行   男の列:$H13、J$10  女の列:$H13、K$10
梨の行    男の列:$H14、J$10  女の列:$H14、K$10

まとめ

とりあえずCOUNTIFS関数を覚えましょう。
動画版はこちらです。

03投稿記事下_関連コンテンツ

-Excelの押えておきたい関数

Copyright© 経理と総務の効率化 , 2024 All Rights Reserved.