エクセルで関数同士の組み合わせをしたいと思ったことがあるかもしれません。特に、イフ関数などは使う機会も多いですから、他の関数とまとめて調べる方法がわかるとExcelの表も綺麗にまとまりますよね。今回は、Excel/エクセルでのCOUNTIFとCOUNTAとIFのそれぞれの組み合わせの事例や数式をご紹介します。
なお、今回の記事は応用編です。記事を読む際に、COUNTIFやIFや部分的な一致などについて詳しく知りたいと感じた場合には、それぞれのリンク先の記事をおすすめします。
エクセルでCOUNTIF関数とIF関数を組み合わせる場合には、重複しているデータを調べることなどに有効です。たとえば、以下のような表があったとしましょう。十数個であれば目視でも確かめられますが、これが3桁以上になると見落とすリスクも出てきます。その際にこの2つの関数を使うことで一気に調べることが可能です。
まず、重複があるかを確かめるセルを選択します。次に、入力する数式は『=IF(COUNTIF(項目のある範囲全体,検索条件)>1,”真の場合に入力する内容”,”偽の場合に入力する内容”)』です。こちらの例では、項目のある範囲はQ3からQ15、当てはまる場合に「重複あり」当てはまらない場合を空白にすると「=IF(COUNTIF($Q$3:$Q$15,Q3)>1,”重複あり”,””)」となります。範囲Q3からQ15において、Q3の個数が1より大きいつまり複数個あるかどうかを調べて、ある場合には「複数あり」と記載しているのです。後に数式をコピーすることを踏まえ、範囲は絶対参照$を付ける点に注意しておきましょう。
ここでは「検索条件」にセル番号を入力し、そのセルに書かれている値を基に式を判定しました。この「検索条件」には、条件値を直接入力することもできます。その場合の数式は『=IF(COUNTIF(範囲全体,”条件値”)>1,”真の場合に入力する内容”,”偽の場合に入力する内容”)』です。
もし、検索したい用語が部分的に一致しているものも対象にするのであれば、『=IF(COUNTIF(範囲全体,”条件値“)>1,”真の場合に入力する内容”,”偽の場合に入力する内容”)』とワイルドカードを含めて記載しましょう。ワイルドカードに関してはこちらの記事で詳細に触れていますので、あわせて参考にしていただけますと幸いです。
COUNTIFSとIFの組み合わせは、先ほどの状況で「かつ」の条件が出てきたときに使います。例では、当方の記事の一覧表から、カテゴリーが[エクセル]でかつタイトルに[IF関数]が含まれている記事を探してみましょう。両方に該当する記事に◎を付け、そうでなければ何も表示しない空欄にします。
入力する数式は『=IF(COUNTIFS(範囲1,検索条件1,範囲2,検索条件2…),”真の場合に入力する内容”,”偽の場合に入力する内容”)』です。先ほどの組み合わせとは、範囲と条件の組が複数ある点が異なります。3つ以上の条件でも判定はできますので、条件に応じて範囲3,検索条件3…などと追加しましょう。今回の例では「=IF(COUNTIFS(Z2,”エクセル“,V2,”IF関数“),”◎”,””)」としました。範囲はそれぞれの行のセル、検索条件は直接入力しています。ワイルドカード**で囲むことは、部分的に一致するものを探せるほか、式を短くできる点でも便利です。
もし、エクセル内に書かれている内容を参照するのであれば、検索条件のところにセル番地を入力しましょう。たとえば、AF1に「Excel(エクセル)・Google・Wordpress」と調べたい値が入力されているのであれば「=IF(COUNTIFS(Z2,AF1,V2,”IF関数“),”◎”,””)」と書き替えられます。セル番地は文字列ではないため、もしセル番地の値を参照しかつ他の文字列と組み合わせて調べたい場合には、「=IF(COUNTIFS(Z2,”“&AF1&”“,V2,”IF関数“),”◎”,””)」などとしましょう。
この数式を見て、「COUNTIFSを使わずに、IFとANDで作成できそう」と感じたかもしれません。ANDとIFの組み合わせであれば、『=IF(AND(条件式①,条件式②…),いずれにも当てはまる場合に表示する内容,いずれかに当てはまらない場合に表示する内容)』となるので、「=IF(AND(Z2=”エクセル“,V2=”IF関数“),”◎”,””)」などとできます。
しかし上の図のように、実際には先ほどの式で◎が付いていたセルにさえも何も表示されなくなってしまいました。IF関数は残念ながらワイルドカードに反応しないため、部分的な一致を探すのであれば他の関数で調べる必要があります。IFとANDの組み合わせについてはこちらの記事で詳しく触れていますので、あわせて参考にしていただけますと幸いです。
空欄以外の何かが入力されているセルの数はCOUNTA関数を使うと行えますが、イフ式と合体させることで更に違った使い方が可能です。たとえば、縦に並んだ項目に対してその隣のセルに番号を順番に振っていくことを考えてみましょう。
まず、番号を振る列の一番上の行を選択します。ここに入力する数式が、『=IF(同じ行で項目が並んでいるセル番地=””,””,COUNTA(項目が並ぶ列の最初の行のセル番地の絶対参照：同じ行で項目が並んでいるセル番地))』です。
M列に並ぶ項目に2行目から番号を振りたい場合には、N2に『=IF(M2=””,””,COUNTA($M$2：M2))』とします。COUNTAの範囲の左側が絶対参照になっているのは、後々に下の行にコピーをした際に、範囲の最初を固定して、空欄以外が出たときにその都度数字が増えるようにするためです。また、IFで同じ行のセルが空欄の場合には空白にして、それ以外に何か入っていれば数えられるようにしています。COUNT関数をCOUNTA関数の代わりに使うと、数値のみに番号が振られるバージョンの表も作成できるでしょう。
ここまでCOUNTIF(S)とカウントエーがそれぞれイフ関数とどのように組み合わさることがあるかを見ていきました。残り1組として、COUNTAとCOUNTIFの組み合わせがあるのかも気になるかもしれません。カウントエーは、特定のセル範囲のうち空欄以外のセルの数を算出する関数です。一方で、カウントイフは、特定のセル範囲のうち条件に当てはまるセルの数を算出します。何か書いてあるセルから条件に当てはまるものを除くことでそうではないものを算出する、引き算をするのであれば使えそうです。
たとえば、先ほどの記事一覧表で、カテゴリーが「Excel(エクセル)・Google・Wordpress」ではないものの数をカウントする場合には、『=COUNTA(Z2:Z21)-COUNTIF(Z2:Z21,”Excel(エクセル)・Google・Wordpress”)』などとします。空欄ではないセルから条件に当てはまるセルを除くことで、それ以外のセルを一気に計算することが可能です。
ただ、別の記事でご紹介していますが、COUNTIFは条件に当てはまらないセルの数を算出することもできます。数式をnotの式で表すことで直接「そうではないもの」を数えられてしまうのです。
また、先ほどのようにCOUNTIFによって◎と””で分け、COUNTAによって◎の数値を数える方法も考えられるかもしれません。しかし、COUNTIFによって空欄にした部分には厳密には「空欄」という文字列が入っているので、COUNTAでは◎と同様に数えられてしまいます。そのため、式の中身をなるべく簡単にしたい場合ではない限り、この2つの関数を同時に使う機会はあまり多くはないかもしれません。
今回は、Excelでの関数の組み合わせ事例として、カウントイフ関数・カウントエー関数・イフ関数などをそれぞれ組み合わせて使える事例や数式をご紹介しました。複数をまとめると複雑な印象を受けますが、それぞれの関数の意味を押さえて、問題なく反映できているかを確かめると混乱が少ないかもしれません。最後までお読みいただきありがとうございました。
関連記事