ExcelのCOUNTIFS関数はExcel2007から増えた関数でCOUNTIFの複数条件版です。
一致する条件だけでなくあいまいな条件を指定したり比較演算子を使用すると活用の幅がぐんと広がります。
ポイント
関数の書き方は
=COUNTIFS(検索条件範囲1,検索条件1,…)
条件1までならCOUNTIFと同じですが続けて条件範囲2,条件2と続けて入力できます。
指定した条件に全て当てはまるセルのみカウントされます。
※条件127まで指定可能
画像では列に一つの意味しかないので範囲を列にしています。
次に検索条件の説明をしていきます。
検索条件
検索条件の指定方法は下記の3種類があります。
・文字列で指定
・セル参照や関数で指定
・文字列＋セル参照や関数で指定
また完全一致の条件だけでなく、あいまい検索や比較演算子を利用することができます。
指定方法
文字列で指定
セル参照や関数の利用
セルに検索条件となる情報を記入しておきそのセルを参照する方法です。
セル参照のメリットは作成した関数をオートフィルなどでコピーすることができます。
１つの関数で一気に作成できるとすごく楽ですよね。
文字列とセル参照
文字列とセル参照がある場合は文字列とセル参照を【&】で繋げます。
最初からセルに【>=2019/1/1】と記入してもよいですが
日付ではなく文字列になるのでデータとして扱いづらくなるので
こういう使い方を結構使います。
あいまい検索や比較演算子の利用方法
あいまい検索
文字列を使用する場合はダブルクオーテーション【”】で囲みます。
①と③は完全一致した物
②は文字列の最後にアスタリスク【*】を使用しています。
位置によって意味が変わります。
- 後ろにつけると〇〇で始まる文字
- 前につけると〇〇で終わる文字
- アスタリスクで囲むと〇〇を含む文字
④と⑤は【?】を使用しています【*】と違い文字数の制限があります。
【？？】のように２つつけると2文字という意味になります。
比較演算子の利用
比較演算子を使用することで検索条件の幅を広げることができます。上記画像では期間を指定しています。
例えば、測定場所で【”<>熊本”】を指定すると熊本以外のデータを集めることができます。
その元データは大丈夫？
関数の元となるデータのレイアウト次第では作成する関数の難易度が上がります。
特に関数はオートフィルなどを使用して、複数のセルに同じ関数を活用することが多いので元データによって集計にかかる時間が全く違います。
関数作成時に扱いづらい元データ例
画像の例だと降水量が市ごとに列が分かれています。
見た目としては見易いデータです。しかし元データに見た目を求めることは間違いです。
このデータを参照して関数を組むと今回のSUMIFを市で分けるには難しくなります。
まず、上記の場合だと市ごとに合計範囲を変更する必要が有ります。
また別の市が増えたり気温などの集計項目が増えると集計するのが大変ですよね。
※扱いづらい元データでやるしかない場合もあります。後日記事を書きたいと思います。
関数作成時に扱い易い元データ例
縦方向に1つの情報のみです。
この場合、新しく市が増えても行方向に増えるだけなので
COUNTIFSの条件の変更のみで対応可能です。
気温など新しい項目が増えたとしても
列ごとに情報が決まっていれば集計も楽になるはずです。
まとめ
最後まで閲覧して頂きありがとうございました！
最後にこの記事のまとめを記載します。
- COUNTIFSは複数の条件に合ったセルの件数を求めることができる関数。
- 条件は文字列で指定やセル参照や関数を使用。【＆】で組み合わせも可。
- 条件は【*】や【?】を指定してあいまいな条件にできる。
- 条件は比較演算子を使用して〇以上のみ集計などの条件を設定できる。
- 元データは列ごとに情報を分けると集計が楽になる。
画像で使用しているデータは少し修正していますが環境省の気象データを利用しています。
関数の練習には良い素材だと思いますので活用してみてください。
⇒環境省気象データリンク
コメント