
[Excel]データ集計をどう行うか? 実際の自治体データをもとに考える⑮ COUNTIFS関数で、数値が入っているデータだけの件数を数える。
このシリーズでは、サンプルデータとして、茨城県ホームページ 「旅館業」 ページ下部の「7.旅館業法に基づく許可施設一覧」にある 旅館業法に基づく許可施設 を使い、実際に集計しながら、その方法を説明しています。
過去記事は、マガジン「実際の自治体データで集計をしてみる」でどうぞ。
前の記事「実際の自治体データをもとに考える⑭ SUMIFS関数で出した数を検算する」で、データによっては「総客室数」と「総定員」に空欄があることが分かりました。
そのため、「総客室数」と「総定員」それぞれについて、集計元となるデータの数を出す必要があります。
空欄があるデータの数は集計分母から抜く、ということです。
前回は、その数値を入れる欄を作り、こんな感じになりました。

ファイルはこちらです。実際にファイルを弄りながらの方が理解が進むと思います。特に今回は、少しややこしいので、読んでいるだけだと、チンプンカンプンかも?
この「(対象施設 件)」に、「総客室数」「総定員」に数値が入っているデータの件数を出します。
件数を数えるので、まず、既に件数が出ているD5の計算式を
「数式」だけ貼り付けます。

数式だけを張り付ける方法4種
D5セルをクリック → Ctrl+C → 「数式」のみ貼り付け で、出来ます。
① Ctrl+V → Ctrl → F
「数式」のみ貼り付けは、
Ctrl+V の後に、Ctrl → F が一般的でしょうか。
Ctrl+V で貼り付けます。
この段階では、式や書式も張り付きますが、右下に表示が出ます。

「Ctrl」と出ているとおり、Ctrlキーを押すと、小さなアイコンメニューが出ます。
この後、
F を押すと計算式のみの貼り付けになります。
流れ的には、Ctrl+V(同時押し) → Ctrl → F となります。
② Ctrl+V → fxを選択 → Enter
なお、セルの右下に小さなアイコンメニューが出た状態で、
矢印キーで「fx」とあるアイコンを選択 → Enter
でも式のみの貼り付けになります。

「fx」とあるアイコンをマウスで選択した場合は「数式」と出てきます。

このアイコンを押せば、「数式」のみのコピーとなります。
ただし、このアイコン、どれがどれだかわかりづらいのが難点。
なので、私は使っていません。
一般的には、上述の通り、
Ctrl+C でコピーしてから、
Ctrl+V(貼り付け)→ Ctrl(小アイコンが出る)→ F
がストレスないでしょう。
ただし、ちょっと操作回数が多い気もします。
また、最初の2回はCtrlの同時押しですが、最後は、Ctrlを単体で押した後に F。ちょっとリズムが悪いかもしれません。
③ Shift + Alt + V → F
セルをコピーしたら
Shift + Alt + V で「書式を選択して貼り付け」を出して、F(数式)を選んで「OK」の方という方法もあります。

ただし、過去記事でも触れているとおり、3キー同時押しは面倒なので、私は次の方法でやっています。
④ Alt → E → S → F(数式) → OK(個人的おすすめ)
Ctrl+C でコピーしたら、
Alt → E → S → F(数式)→ OK で数式のみ張り付きます(古参兵式)。
同時押し出ないので、指もつらず、運指もスムースです。
画面的には、上の③と同じです。
COUNTIFS関数に条件を追加する
コピーされた計算式は以下の通りです。
計算式と、トレース矢印を出してみます。

計算元は「旅館・ホテル」だとわかります。
この計算式は上と同じですから「旅館・ホテル」のデータ全てになります。
出したいのは「総客室数」に数値が入っているデータの数です。
つまり、
H列が「旅館・ホテル」で、「総客室数」が0より大きい(=数値が入っている)のデータの数です。

条件付き書式についてはいずれ。
H列が「旅館・ホテル」(H列)という条件は、既に、
=COUNTIFS(DATA!H:H,集計!D$4)
で出されています。
後は、「総客室数」(K列)が0より大きい、という条件を加えます。
すなわち、
K列>0 つまり
DATA!K:K>0 という条件になります。
=COUNTIFS(DATA!H:H,集計!D$4,DATA!K:K,">"&0)
COUTNIFS関数の記述方法は、MicroSoftサポートによると
=COUNTIFS (条件範囲 1, 検索条件 1,条件範囲 2, 検索条件 2)
です。
条件1は既に入っていますので、
=COUNTIFS(DATA!H:H,集計!D$4,条件範囲 2, 検索条件 2)になります。
「条件範囲2」は、「総客室数」(DATAシートのK列)なので、
=COUNTIFS(DATA!H:H,集計!D$4,DATA!:K:K, 検索条件 2)
となります。
「検索条件 2」は、「K列が0より大」なので、
=COUNTIFS(DATA!H:H,集計!D$4,DATA!:K:K, >0)
でよさそうですが、これではダメなのです。
「>」が記号のため「””」で括り、「&」で「0」と繋げます。
つまり、
=COUNTIFS(DATA!H:H,集計!D$4,DATA!:K:K, ">"&0)
となります。

数値ではない「>」が入るので、「数値ではないですよ」とエクセルに知らせるために「””」で括るのです。
これは、こういうものだ、と捉えてください。
数値でなければ「””」で括って「&」で繋ぐ、と覚えるしかありません。
計算結果を見ると・・・

「旅館・ホテル」の件数792に対し、総客室数の対象件数は791と出ました。
つまり「旅館・ホテル」のうち1件は「送客室数」が空欄ということです。
これが正しいか、確かめてみます。
フィルターで「旅館・ホテル」のみとしたうえで、「総客室数」の「空欄」を出します。(フィルターのやり方は「フィルター は、 Alt→A→T より Alt→D→F→F がおすすめかもをどうぞ。)

表示してみると・・・

「総客室数」が空欄のデータが1つありました(黄色は、目立つように手動でつけたものです。)。
これで、計算式で出した791が正しいことが分かります。
この式を、絶対参照や相対参照を考えて修正し、他のセルにもコピーします。
「旅館・ホテル」の式を「簡易宿所」「下宿」にコピーするためには、あらかじめ、
=COUNTIFS(DATA!$H:$H,集計!D$4,DATA!K:K,">"&0)
を
=COUNTIFS(DATA!$H:$H,集計!D$4,DATA!$K:$K,">"&0)
に直しておきます。違い、分りますか?
横にずらしても、検索対象である「DATA!K:K」が変わらないように、「DATA!K:K」を「DATA!$K:$K,」と絶対参照にしました(K:Kにカーソルを当ててF4を押す)。
この後、コピー元とコピー先を範囲指定して・・・

Ctrl+R(右へコピーのショートカット)を押すと、計算式がコピーされます。
(普通に、Ctrl+C → Ctrl+V or Enter でも構いません。)

「合計」は上のセルをコピーします(Ctrl + D)。
これで、「総客室数」の「(対象件数 件)」の欄が出ました(「合計」欄は後ほど)。
次は、「総定員数」の「(対象件数 件)」です。
まず、「総客室数」の「(対象件数 件)」欄をコピーします。

ただし、[DATA!$K:$K」は「総客室数」を示すので、これを「DATA!$L:$L」(「総定員」が入っている列)に直します(F2でセルを修正可能にして、直接入力 又は 一旦削除して、DATAシートのL列をクリック)。

後は横にコピーすれば完成です。

合計欄は、全部同じ計算式なので、一番上から下まで範囲指定して、Ctrl+D(上のセルを貼り付け)で入ります。
ただし、この数値、ちょっと目立ちすぎ。
「(対象施設 件)」がカッコに入っているように、算出した件数もカッコに入れ、そして、少し文字サイズを小さくして、目立たなくします。
Ctrlを押しながら、該当のセルを範囲指定し、Ctrl+1で「セルの書式設定を開いたら・・・

「ユーザー定義」の「種類」の欄に
「(#,##0)」と入れます。
これで、数値がカッコに入ります。

そのまま右クリックで文字の大きさも変えます。(11から9に)

「対象施設」の件数が控えめになりました。
項目も「(対象件数 件)」も9ポイントにしておきます。
ついでに、(対象施設数)の数が入るセルの上の罫線も消しておきます。

これで、参考数値である「対象施設」の項目と件数の両方が控えめになりました。すべてが同じ大きさだと、目が迷ってしまいますからね。
罫線も刑したことで数値の結びつきもはっきりします。
この「対象施設」の欄が合っているかは、「対象件数」の計算式を「検算」欄にコピーします。

その上で、上のグレーの部分を消します。
そうすると、K列(総客室数)が空欄でない(0より大きい)データの数が出ます(「営業の種類」が何であるかは問いません。)。


上の「判定」式をコピーして「OK」と出れば問題ありません(「合計」と「検算」を見比べれば、すぐにわかりますけど。)
「総定員」も同じことをします。
(「総定員」の欄をコピーします。「総客室数」をコピーすると、対象列が違ってしまいます。)

これで出来上がりです。
全部「OK」がでているので、計算式が間違っていなことが分かります。
お疲れさまでした。
COUNTIFS関数は複数の条件を設定できるため、とても重宝します。
ただし、セルが特定の数値より大きい(または、以上、以下、より小さい)の場合はちょっと面倒です。
「”記号”&数値」というパターンを忘れてしまうと、行き詰まります。
忘れたら、調べられるようにしておけばいいだけですが。
今回のファイルはこちらです。