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