［Excel］データ集計をどう行うか？ 実際の自治体データをもとに考える⑦ 許可の種類別の件数を集計する（COUNTIFS関数）
実際の自治体データ（茨城県の旅館業の許可状況）を元に集計をしてみるこのシリーズの過去記事は、マガジン「実際の自治体データで集計をしてみる」でどうぞ。
過去記事「データ集計をどう行うか？ 実際の自治体データをもとに考え⑥ 集計表の「枠」を作る」で、集計表の「枠」を作りました。
こちらがそのファイルです。
最初のゴールは、H列にある「営業の種類」別の数を出すことです。
上のデータ一覧から、下の表を出します。
前記事で、集計の「枠」ができました。
今回、ようやく、集計です。
まずは、「営業の種類」別の件数を出します。
COUNTIFS関数で件数を出す。COUNTIF関数は使わない。
件数を出すには、COUNTIFS関数を使います。
条件（IFS)に合ったものを数える（＝COUNTする)関数です。
Microsoft サポートによると、
COUNTIFS (条件範囲 1, 検索条件 1, [条件範囲 2, 検索条件 2],…) と記述します。
「範囲」の中に「検索条件」と合致するデータがいくつあるか、を数えて表示する（返す）関数です。
IFSと複数になっているとおり、複数の条件を設定できます。
条件が２つの場合は、「１の範囲の中に検索対象があって、かつ、２の範囲の中に検索対象があるもの」の数を出します。
なお、兄弟関数として、COUNTIF関数がありますが、こちらは条件が１つの時しか使えません。
このCOUNTIF関数は覚える必要はありません。使う必要もありません。
理由は、単一条件でも、COUNTIFS関数が使えるからです。
詳細は別記事で説明します。
COUNTIFS関数を入れてみる
集計表にCOUNTIFS関数を入れてみます。
D4「旅館・ホテル」の下のD5セルに =count と入れると（小文字でOK)、関数の候補が出ます。↓ で「COUNTIFS」を選んで、Tabで決定します（Enterではないので注意）。
＊私は、直接「=countifs( 」と打ち込んでいます。候補が多いと、見つけるのが面倒なので。
Tabで決定すると、以下の通りとなります。
「（」の後には、まず「検索条件範囲」を入れます。
「検索条件範囲」とは、後で設定する条件に合うデータがいくつあるかを調べる範囲ということです。
「DATA」シートのタブをクリックして、同シートに移動し、H列「営業の種類」を選択します（「H」の部分をクリックします。）。
行番号を入れる必要はありません。↓↓↓
列のみの指定で、行番号は指定されていません。
そうしたら、「集計」シートに戻ります。
D5セルには以下のように入っているはずです。
「！」はシート名とセル番号を区切る記号です。
「！」の前がシート名になります。
同一シート内なら、シート名は削除可能ですが、
とりあえずこのままにしておきます。
続いて、「旅館・ホテル」と入っているD4セルをクリックします。
後は、Enter を押します（最後の「）」は自動で入ります。）。
これで、「DATA」シートのH列内に、いくつ「旅館・ホテル」というデータがあるか、その数が出ます。
この例では、792件あることが分かります。↓↓↓
同じものをD5の下に表示してみました。
このD5が、どのセルを参照しているか、計算式のトレースで見てみます。
Alt→M（数式）→P（参照元のトレース）で出ます。
Alt→M→A→A（または保存）で消えます。
青矢印により、「旅館・ホテル」が入っているD4セルが計算式に入っている（＝参照している）ことが分かります。
左上から斜めに伸びている点線矢印は、「別シートのデータが計算式に入っている（＝参照している）」という意味です。ここでは「DATA」シートのH列が入っている（＝参照している）のですが、トレースでは、「どのシートのどれ」までは分かりません。
他のセルには計算式をコピーする
同じ計算式を「簡易宿所」「下宿」にも入れます。
「旅館・ホテル」と同じように =count から始めてもいいのですが、それは手間です。
既に出来ている「旅館・ホテル」の計算式「=COUNTIFS(DATA!H:H,集計!D4)」を使い回す（コピーする）方が簡単です。
検索対象の列は絶対参照にしておく
ただし、そのまま横に一つずらすと、計算式内のセルも１つ横にずれてしまいます。そうすると、正しく集計できません。
「DATA」シートのH列がI列に、
「集計」シートのD4がE4にずれてしまいます。
「DATA」シートのI列は「指令許可番号」が入っており、
検索対象であるE4の「簡易宿所」はないため、
「０」という結果になっています。
集計する元のデータは、「DATA」シートのH列のままでないといけません。
そのためには、D5セルをコピーする前に、このH列の部分を「絶対参照」にしておきます。「絶対参照」とはコピーしても、元のセルが変わらない、というものです。
やり方は、①セルを選択したらF2をクリック（またはセルをダブルクリック）し、②「H:H」のところへカーソルを持っていき、③F4を押します。
「H:H」が「$H:$H」になれば、絶対参照です。
数式バーを見ると、「=COUNTIFS(DATA!$H:$H,集計!D4)」となっています。
なお、「集計!D4)」は「旅館・ホテル」のセルですが、このセルは、右にずらした際に、「簡易宿所」に変わるように、そのままにしておきます。この「＄」が付いていないものを「相対参照」といいます。）
＊列と行、それぞれに「＄」を付けることができます。F４を押す度に、付き方が変わります。
これでコピーの準備ができました。
後は、D5セルを右の二つ（E4とF４）のセルに張り付けます。
普通のコピー（Ctrl+C からの Ctrl+V）でも構いませんが、
D5セルを含んでG5セルまで範囲指定して、Ctrl+Rでもコピーできます。
マウスを握っているのなら、セルの右下に「＋」が出たら右にドラッグ、でもコピーできます。
D4セルの右下に「＋」が出たら右にドラッグします。
これで、それぞれの許可の種類の件数が出ました。
ちょっとごちゃごちゃしちゃいますが、計算式のトレースと、計算式そのものを、セルの下に表示してみます。↓↓↓
一方、検索対象は、各セルごと違っています。
合計は Shift + Alt + = のショートカットで一瞬で出す
「許可の種類」別の件数は出ましたが、全体の件数が出ていません。
これを出すには、
３つのセルを単純に＋で繋いでいってもいいのですが・・・ ↓↓↓
手入力でSUM関数を入れてもいいのですが・・・ ↓↓↓
（最後の「（」は入れなくてもEtnerで自動に入ります。）ると合計が出ます。
セル数が少ない場合は、これらの方法でも、まぁ、いいでしょう。
でも、セル数が多いと、結構面倒。
特に最初の方法だと、セルが抜け落ちるおそれがあります。
このような単純な合計は、ショーカットで簡単に出せます。
Shift + Alt + = です。
Shift + Alt + - でも同じです（Shift+-で＝なので）。
やり方は簡単。
合計欄を選択し、↓↓↓
下の方法でやってみてください。
あるいは、合計を出したいセル全部と合計を入れたいセルを範囲指定し、↓↓↓
Shift と Alt と ＝（または - ）を同時に押すだけ。
これで、SUM関数が入ります。↓↓↓
上の状態から、Enterを押すと、下のように確定します。
これで、「営業の種類」別の件数、そして全体の件数が出ました。
なお、表全体を範囲指定して（Ctrl＋A）、カンマ入れをしておきます（Alt → H → K または Shift + Ctrl＋１）。
＊日付等がある場合は、日付部分は範囲指定しないでください。
念のため、「DATA」シートのH列を選択して、「データの個数」を見てみると、「1027」と出ています。
これには、表頭（ヘッダー）も入っているので、実際のデータの件数は「1027」から１を引いた「1026」です。
この「1026」は、集計表の「計」と同じですので、集計が正しく出来ていることが分かります。
以上で、基本的な集計は（やっと）終了です。
長々と説明してきましたが、実施に集計表を作るには、慣れれば10分かからないでしょう。
この記事の集計だけなら、３分程度でも十分できるでしょう。
とはいえ、急いでやって間違えても意味はないので、慌ててやる必要はありません。操作を間違えたら、Ctrl ＋ Z でやり直せばいいんです。
ただし、一つ気になることが・・・。
最後の部分で、集計表が正しいか、「DATA」シートのH列を範囲指定してデータの個数を見て、集計表の計と合っているか、確認しました。
この作業、データが追加される度（月次/年次等）にやるのは面倒です。
忘れることもあります。
このような都度確認をしなくても、集計表で出したデータが合っているか、常に自動で検算できるようにすることが理想です。
その方法は、次回、説明します。
今回作ったファイルをいかにアップしておきます。ご参考にどうぞ。
この記事が気に入ったらサポートをしてみませんか？