メインコンテンツへスキップ
見出し画像

[EXCEL] 集計入門 クロス集計、どうやる? ピボットテーブルか? COUNTIFSか?SUMPRODUCT関数か? その2 COUNTIFS編(おすすめ)

    ▶MAP▶PDF版▶表示▶関数▶ショトカ▶操作/コピー▶実務▶NG▶検索



    【まとめ】
    ・COUNTIFS関数を使うとクロス集計ができる(他の関数でもできる)。
    ・「~以上」なら ">="&セル番地、
     「~未満」なら、"<"&セル番地、
    と記号を””で囲み&でセル番地とつなぐ
    ・集計ミスがあることを想定して、全体の件数と突合する数式も作っておく


    【説明】

    以下のデータ表。
    職員(従業員)の通勤方法と所要時間の一覧です。

    画像

    このデータ表から、以下の集計表を作ります。

    画像

    前記事ではピボットテーブルで挑戦してみましたが、うまくいきませんでした。

    今回は、COUNTIFS関数を使ってやってみます。

    よろしければ、下のファイルをダウンロードして、実際に手を動かしながらやってみましょう。
    見ているだけだとよくわかりません。
    理解するなら、それが一番です。



    COUNTIFS関数で「徒歩」の人数を出す

    まず、集計表の左上スミ、「徒歩」の欄。
    とりあえず、「徒歩」の件数(人数)を出します。

    以下の数式を入れます。
    =COUNTIFS(C:C,F5)

    画像

    =COUNTIFS(C:C,F5) は
    「通勤方法」が入っているC列に、F5の「徒歩」がいくつあるか調べる数式です。

    画像

    ただし、これだと、判るのは「徒歩」の件数(人数)だけ。
    「徒歩」の全件数(全人数)である「3」が入ってしまっています。
    所要時間は、まだ反映されていません。


    「所要時間」で集計してみる


    上の数式に、所要時間の条件を追加します。
    ただし、追加する条件が複雑になるので、まずは、「所要時間」別の人数を集計してみます。
    集計表の下に「所要時間」だけを集計する表を作ってみます(上の表をコピー)。

    まずは、左上スミのG15セル。

    ここは「0分以上20分未満」の数です。
    最初に「0分以上」を出します。
    「所要時間」は全て「0分以上」なので、あえて集計しなくてもいいのですが、このセルの数式を他のセルにもコピーしたいので、数式を入れます。

    数式は以下の通りとなります。
    =COUNTIFS(D:D,">="&G12)

    画像

    =COUNTIFS(D:D,">="&G12) の式は、
    所要時間が入っているD列の数値が、G12 (=0)以上であるものの件数を調べる、というものです。

    「以上」なので「>=」となりますが、COUNIFSの場合、この条件を当てはめるには「””」で括ったうえで、&でセル(または数値)と繋ぎます。
    参照元のトレースを示すと以下の通りです。

    画像

    以上で「0分以上」の件数が集計できました(実際は全人数の「15と同じ)。


    数式内に数値は入れない


    =COUNTIFS(D:D,">="&G12) は、 G12が0なので
    =COUNTIFS(D:D,">="&0) でも同じ結果になります。
    しかし、後者だと数式をコピーした際に、数式の中を一つ一つ修正しないといけません。
    それは面倒&間違いの元です。
    数式には、基本、数値は直接入れず、セル番地を入れます。


    次は「20分未満」の条件追加

    このセルは「0分以上」「20分未満」です。
    今の=COUNTIFS(D:D,">="&G12) では「0分以上」の全ての件数(人数)が入ってしまっていますので、
    この数式に「20分未満」の条件を追加します。

    未満は「<」なので、「"<"&20」となりますが、「20」という数値ではなく、「20」が入っているセルである「G14」セルを入れて「"<"&G14」とします。

    集計するデータは、「0分以上」と同じD列ですので、
    D:D,"<"&G14 となります。

    具体的な操作方法は、
    =COUNTIFS(D:D,">="&G12) の最後のカッコを取り(バックスペース)、区切り用の「,」を入れたら、D列をクリック、再度「,」を入れたら、"<"&G14 と入れてENTER、です(閉じカッコは不要)。

    結果、以下の数式が入ります。

    =COUNTIFS(D:D,">="&G12,D:D,"<"&G14)

    画像

    「参照元のトレース」を表示してみると、「20」にも「・」が付いています(わかりづらいですけど)。
    集計結果も、15から3に変わっています。

    これで「0」分以上、「20」分未満、の件数(人数)が出ました。


    「徒歩」の数式に、「0」分以上「20」分未満の条件を足す


    後は、これを上の表の「G5」セルの数式に当てはめます。
    つまり「通勤方法」が「徒歩」である件数を出してある数式に、「0」分以上「20」分未満という条件を足すのです。

    G5セルには、以下の数式が入っています。
    =COUNTIFS(C:C,F5)
    G15には、以下の数式が入っています。
    =COUNTIFS(D:D,">="&G12,D:D,"<"&G14)

    これを合体すると・・・
    =COUNTIFS(C:C,F5,D:D,">="&G12,D:D,"<"&G14)
    となります。

    ただし、実際は「0」分(G2セル)と「20」分(G4セル)は、上の表のセルを参照するので、
    G12をG2に、G14をG4にします。
    結果、
    =COUNTIFS(C:C,F5,D:D,">="&G2,D:D,"<"&G4)
    となります。

    COUNTIFS関数は単純に
    「件数を数える範囲1、件数を数える条件1、件数を数える範囲2,件数を数える条件2,件数を数える範囲3、件数を数える条件3・・・」
    と続いていきます。
    なお、条件は1つだけでもいいので、COUNTIFは使いません。


    今回は「件数を数える範囲」は全てD列で同じですが、これは省略はできないので、全ての「件数を数える条件」の前にD列を入れます。

    実際に数式を入れる際には、
    =conと入れてから、↓ で=COUNTIFS を選んだら、Tabで確定し、
    C列(通勤方法)を選択 ⇒区切り用の「,」を入力 ⇒ F5の「徒歩」を選択 ⇒ D列(時間)を選択 ⇒ 「, 」を入力 ⇒ ">="&G2 と入力 ⇒「, 」を選択 ⇒ D列(時間)を選択 ⇒「, 」を入力 ⇒ "<"&G4 と入力 ⇒ Enter で

    =COUNTIFS(C:C,F5,D:D,">="&G2,D:D,"<"&G4) 

    という数式が入ります。

    後は、この数式を他のセルにコピーすればいいだけです。


    コピーの前に、絶対参照・相対参照を設定する


    後はコピーするだけ。
    でも・・・
    数式を横にコピーすると、集計対象であるD列も横にずれてしまいます。「徒歩」もずれています。

    画像

    また、下にコピーしても、「分」がずれてしまいます。

    画像

    これを防ぐために、「コピーする前」に「セルがずれる/ずれない」ように数式を修正します。
    コピーしてからだと、一つ一つのセルを修正する必要があり、面倒&ミスの元だからです。

    具体的には、
    =COUNTIFS(C:C,F5,D:D,">="&G2,D:D,"<"&G4) を
    =COUNTIFS($C:$C,$F5,$D:$D,">="&G$2,$D:$D,"<"&G$4) とします。

    ・C列(「通勤手段」の列)

    「通勤手段」のある「C列」は、どのセルでも集計対象です。
    どのセルにコピーしても同じでないと困ります。
    従って、「C:C」を、コピーしてもずれない「絶対参照」である「$C:$C」にします。
    具体的には、「C:C」にカーソルを当てて、F4キーを1回押すだけです(数式入力時にF4を押しても同じ)。
    「$」が付けばコピーしてもずれません。

    ・F5(「徒歩」が入っているセル)
    検索条件であるF5セルは、下にはずれてほしいのですが、横にずれると困ります。
    そのため「F5」は「$F5」にします。
    (「F5」⇒F4キーを3回押下⇒「$F5」)
    列番号は「$」が付く「絶対参照」に、
    行番号は「$」が付かない「相対参照」にするのです。

    ・D列(「所要時間」の列)
    どのセルでも、この列を集計します。
    ですので、コピーしてもずれないように「D:D」を「$D:$D」とします(F4キー1回押下)。

    ここまでの操作で
    =COUNTIFS(C:C,F5,D:D,">="&G2,D:D,"<"&G4) が
    =COUNTIFS($C:$C,$F5,$D:$D,">="’&G2,$D:$D,"<"&G4) となりました。


    ただ、このままコピーすると、今度は、所要時間が正しく出ません。

    下にコピーしたら・・・

    画像


    「0」分以上「20」分未満が、ずれてしまっているので、計算できません。


    「○分」以上、「●分未満」は、下にコピーした際に、「行」がずれてはいけませんが、横にコピーした際には「列」がずれないと困ります。

    従って、行番号(数字)には「$」を付ける「絶対参照」とする一方で、列番号(アルファベット)には「$」を付けない「相対参照」とします。

    こちらも同じく、「G2」「G4」にカーソルを当ててクリックしてから、F4キーを2回押します。
    F4キーを押すごとに「$」の付き方が変わるので、2回目で「G$2」「G$4」となったら、Enterで確定します。

    *「$」はF4を押す度に、付き方が変わります。
    G2 ⇒ F4キー押下 ⇒ $G$2 ⇒再度F4キー押下 ⇒  G$2 ⇒ 再度F4キー押下 ⇒ $G2 ⇒ 再度F4キー押下 ⇒ G2 ⇒再度F4キー押下 ⇒ 再び$G$2 ⇒ 以下繰り返し
    「間違えた」と思っても、F4キーを押していれば大丈夫です。

    上の操作で、
    =COUNTIFS($C:$C,$F5,$D:$D,">="&G$2,$D:$D,"<"&G$4)
    となったら、後はほかのセルにコピーします。
    結果は以下の通り。

    画像

    「参照元のトレース」を表示してみると、ちゃんと検索対象がずれるべきものはずれて、ずれてはいけないものはずれていないのがわかります。

    1件足りない?

    しかし・・・
    全体のデータは15件なのに、合計は14件となっています。
    これはおかしい。
    合計欄にはあらかじめ「=SUM(L5:L9)」という数式が入っています(後で、タテでもヨコでも範囲選択して、Alt+Shift+= で出してもいい)。

    これは、「80分以上~」の欄にも、以下の通り「~未満」という検索が入っているからです。

    画像
    途中にあった下の集計表はけずりました
    • 「80分以上(の全て)」の場合は、最後の「未満」の条件を削ります。
      具体的には、
      =COUNTIFS($C:$C,$F5,$D:$D,">="&K$2)
      とし、他のセルにもコピーします。

    すると・・・

    画像

    「80分以上、空白未満」が、単に「80分以上(のすべて)になり、上の表のように集計できます。


    検算セルを入れておく

    上の例のように、各セルの集計結果の合計が全体の件数に会わない場合もあります。
    どこかのセルがおかしい場合です。

    個々セルの合計≠全体の数

    これは実際よく起きます。

    全体件数がこの程度ならいいのですが、件数がたくさんあると、集計漏れに気づかない場合があります。
    それを防ぐためには、全体件数も集計しておきます。

    下図では、合計欄の下に
    =COUNTA(B:B)-1
    と入れて、「氏名」の全人数を出しています。
    具体的には「B列」にあるデータの数を、COUNTA関数で出しています。
    最後の「-1」は「人数」という項目名の分、1を引いているものです。

    画像

    この数字と、上の合計欄が合わなければ、エラーメッセージを出すようにしておけば、集計ミス防止になります。

    画像

    =IF(L10<>L11,"←エラー!","")
    という数式で、L10の計と、L11の氏名の数を突合しています。(上の例は「80分以上」のところを修正する前のもの)


    かなりくどくなってしまいましたが、以上で、COUNTIFS関数を使った集計は終わりです。

    入力済みファイルもおいておきます。


    右の欄の「平均」と「データバー」の出し方は、別記事にします。

    この手の集計には、SUMPRODUCT関数というものも使えます。
    ちょっと、とっつきづらいところもありますが、複数条件を行うクロス集計では、結構使えます。
    次回は、そのSUMPRODUCT関数を使った集計方法をお示しします。
    COUNTIFS関数を使っても、SUMPRODCT関数を使っても、答えは同じになりますので、使いやすい(理解しやすい)方を使えばいいと思います。
    *個人的には、今のところ、COUNTIFS関数の方が使いやすいですし、他の人(後任)も同じではないかと思います。
    が、選択肢(別解)といてSUMPRODUCT関数もお示ししたいと思います。



    以上、参考になれば幸いです。
    もし何か「発見」があったら、「スキ」「フォロー」「チップ」していただける嬉しいです!


    あなたへのおすすめ