指定した条件を満たすデータだけを集計

 最後に、表形式のデータを対象に、指定した条件を満たすデータの個数などを求める関数と数式のテクニックを紹介しておこう(図10)。解説のためのサンプルとしては、通信販売の注文受け付けと発送を記録した表を使用する。

図10 販売記録の表を対象として、さまざまな条件に該当するデータを集計する関数を紹介する。例えば、「商品ID」列で、IDが「A」で始まる商品の数を調べたり、該当する商品の売上金額の合計や平均を求めたりといった集計処理が可能だ
図10 販売記録の表を対象として、さまざまな条件に該当するデータを集計する関数を紹介する。例えば、「商品ID」列で、IDが「A」で始まる商品の数を調べたり、該当する商品の売上金額の合計や平均を求めたりといった集計処理が可能だ

 まず、対象のセル範囲の中で、指定した条件に該当するセルがいくつあるかを求めよう(図11)。このような集計には、COUNTIF(カウントイフ)関数を利用すればよい。

 この関数の引数「検索条件」ではいろいろな指定方法が使えるが、最も簡単なのは、個数を求めたいデータそのものを指定する方法だ。

図11 対象のセル範囲の中で、指定した1つの条件に該当するデータの個数を求めてみよう。「男」のような特定のデータが入力されているセルや、「40以上の数値」「東京都○○区」「東京都以外」といった条件に該当するセルの数をカウントすることができる
図11 対象のセル範囲の中で、指定した1つの条件に該当するデータの個数を求めてみよう。「男」のような特定のデータが入力されているセルや、「40以上の数値」「東京都○○区」「東京都以外」といった条件に該当するセルの数をカウントすることができる

 例えば、購入した会員の中の男性の数を求めたい場合は、引数「範囲」に「性別」列を、「検索条件」に「男」という文字列を指定すればよい(図12)

図12 1つの条件を満たすセルの個数を求めるには、COUNTIF 関数を利用すればよい。引数「検索条件」に文字列を指定すると、引数「範囲」の中で、その文字列が入力されたセルの数が求められる。ここでは、「性別」列の範囲を対象に、「男」と入力されているセルの数を求めた
図12 1つの条件を満たすセルの個数を求めるには、COUNTIF 関数を利用すればよい。引数「検索条件」に文字列を指定すると、引数「範囲」の中で、その文字列が入力されたセルの数が求められる。ここでは、「性別」列の範囲を対象に、「男」と入力されているセルの数を求めた

 このとき、カウントの対象となるのは「男」だけが入力されたセル、または数式の結果として「男」と表示されているセルだ。「男」の文字があっても、それ以外の文字も含む文字列のセルは、条件に該当するとは見なされない。

 文字列だけでなく、数値のデータを条件に指定することもできる。特定の数値そのものが表示されているセルの個数を調べたい場合は、同様にその数値を直接指定する。

条件指定に比較演算子やワイルドカードを利用

 さらに、図3でも登場した比較演算子と組み合わせて、数値の範囲を指定することも可能だ。具体的には、半角の比較演算子と数値を組み合わせ、全体を「"」(半角ダブルクォーテーション)で囲んで、文字列として指定すればよい。ここでは、「>=40」という条件を指定して、「年齢」列のセル範囲の中で40以上のセルの個数を求めている(図13)

図13 「検索条件」には数値も指定できるが、さらに比較演算子を組み合わせて、数値の範囲を指定することも可能だ。ここでは「>=40」という文字列の形で指定し、「年齢」列の数値が40以上であるセルの数を求めている
図13 「検索条件」には数値も指定できるが、さらに比較演算子を組み合わせて、数値の範囲を指定することも可能だ。ここでは「>=40」という文字列の形で指定し、「年齢」列の数値が40以上であるセルの数を求めている

 これ以外にも、IF関数などで条件判定を行うときに使用するのと同じさまざまな比較演算子を、引数「検索条件」の指定に使用できる。さらに、「?」または「*」という「ワイルドカード」を利用して、いわば文字列のパターンに該当するセルの個数を求めることも可能だ(図14)

図14 COUNTIF 関数の「検索条件」には、「>=」以外にも、表のような記号を組み合わせて指定することが可能だ。これらを使用する場合、組み合わせるのが数値であっても、必ず半角文字の「"」で囲んだ文字列の形で指定する
図14 COUNTIF 関数の「検索条件」には、「>=」以外にも、表のような記号を組み合わせて指定することが可能だ。これらを使用する場合、組み合わせるのが数値であっても、必ず半角文字の「"」で囲んだ文字列の形で指定する

 例えば、購入した会員の中で東京23区に住んでいる人の数は、0文字以上の任意の文字列にマッチする「*」を使用して、「東京都*区」と「検索条件」に指定すれば求められる(図15)。また、居住地が東京都以外である会員の数を求めたい場合は、比較演算子とワイルドカードを組み合わせ、「<>東京都*」と指定することもできる(図16)

図15 「検索条件」には、「*」や「?」といった「ワイルドカード」を使った文字列のパターンも指定できる。例えば、「居住地」が東京23区である会員の数を求めたい場合は、「東京都*区」のように指定すればよい
図15 「検索条件」には、「*」や「?」といった「ワイルドカード」を使った文字列のパターンも指定できる。例えば、「居住地」が東京23区である会員の数を求めたい場合は、「東京都*区」のように指定すればよい
図16 特定の文字列を含まないセルの個数を調べたい場合は、比較演算子「<>」と、ワイルドカード「*」を組み合わせる。例えば、「居住地」が東京都以外である会員の数は、「検索条件」に「<>東京都*」のように指定すれば求められる
図16 特定の文字列を含まないセルの個数を調べたい場合は、比較演算子「<>」と、ワイルドカード「*」を組み合わせる。例えば、「居住地」が東京都以外である会員の数は、「検索条件」に「<>東京都*」のように指定すれば求められる

 なお、ここでは数式の中で条件を直接指定しているが、他のセルに入力された値を参照してもよい。例えば、C1セルに都道府県名だけが入力されている場合、「C1&"*"」のように文字列演算子「&」で「*」と結合することで、その都道府県に住んでいる会員の数を求めることが可能だ。

 このほか、COUNTIF関数と同様に「検索条件」を指定することで、条件を満たすデータの「合計」を求められるのがSUMIF(サムイフ)関数だ。例えば、「性別」列の値が「女」である行の、「数量」列にあるセルの値の合計を求めることができる。またAVERAGEIF(アベレージイフ)関数を使えば、指定した条件を満たすデータの「平均」を求めることが可能。これら集計や属性分析に便利な必修関数については、「ビジネスExcel完全版」を参照してほしい。

文/日経PC21編集部

「ビジネスExcel完全版」とは

 Excelを学ぶために「操作」「関数」「文書・作図」「プログラミング(マクロ)」の4章構成で、それぞれビジネスに直結した題材を用意。1000点を超える図版を用いている。初めてエクセルに取り組む方は、冒頭の「基礎編 入門エクセル」から読み進めるのがおすすめ。急ぐ方は、さっと目で追うだけで作業の要点を理解できる。

 強力な糸を使った特別製本で、机の上に置いてページが180度開く。本書をお手元に置いて、エクセルのスキルを上げて、ビジネスの効率化を目指そう。


「ビジネスExcel完全版」

著者:日経PC21 編
出版社:日経BP社
ビジネスExcel完全版のページ
(日経BPブックナビ)