[Excel]データ集計をどう行うか？ 実際の自治体データをもとに考える⑮ COUNTIFS関数で、数値が入っているデータだけの件数を数える。
このシリーズでは、サンプルデータとして、茨城県ホームページ 「旅館業」 ページ下部の「７．旅館業法に基づく許可施設一覧」にある 旅館業法に基づく許可施設 を使い、実際に集計しながら、その方法を説明しています。
過去記事は、マガジン「実際の自治体データで集計をしてみる」でどうぞ。
前の記事「実際の自治体データをもとに考える⑭ SUMIFS関数で出した数を検算する」で、データによっては「総客室数」と「総定員」に空欄があることが分かりました。
そのため、「総客室数」と「総定員」それぞれについて、集計元となるデータの数を出す必要があります。
空欄があるデータの数は集計分母から抜く、ということです。
前回は、その数値を入れる欄を作り、こんな感じになりました。
ファイルはこちらです。実際にファイルを弄りながらの方が理解が進むと思います。特に今回は、少しややこしいので、読んでいるだけだと、チンプンカンプンかも？
この「（対象施設 件）」に、「送客指数」「総定員」に数値が入っているデータの件数を出します。
件数を数えるので、まず、既に件数が出ているＤ５の計算式を
「数式」だけ貼り付けます。
数式だけを張り付ける方法４種
Ｄ５セルをクリック → 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
がストレスないでしょう。
ただし、ちょっと操作回数が多い気もします。
また、最初の２回はCtrlの同時押しですが、最後は、Ctrlを単体で押した後に F。ちょっとリズムが悪いかもしれません。
③ Shift ＋ Alt ＋ V → F
セルをコピーしたら
Shift ＋ Alt ＋ V で「書式を選択して貼り付け」を出して、F（数式）を選んで「OK」の方という方法もあります。
ただし、過去記事でも触れているとおり、３キー同時押しは面倒なので、私は次の方法でやっています。
④ Alt → E → S → F(数式） → OK（個人的おすすめ）
Ctrl+C でコピーしたら、
Alt → E → S → F(数式）→ OK で数式のみ張り付きます（古参兵式）。
同時押し出ないので、指もつらず、運指もスムースです。
画面的には、上の③と同じです。
COUNTIFS関数に条件を追加する
コピーされた計算式は以下の通りです。
計算式と、トレース矢印を出してみます。
計算元は「旅館・ホテル」だとわかります。
この計算式は上と同じですから「旅館・ホテル」のデータ全てになります。
出したいのは「総客室数」に数値が入っているデータの数です。
つまり、
H列が「旅館・ホテル」で、「総客室数」が０より大きい（＝数値が入っている）のデータの数です。
条件付き書式についてはいずれ。
H列が「旅館・ホテル」（H列）という条件は、既に、
=COUNTIFS(DATA!H:H,集計!D$4)
で出されています。
後は、「総客室数」（K列）が０より大きい、という条件を加えます。
すなわち、
K列＞０ つまり
DATA!K:K>0 という条件になります。
=COUNTIFS(DATA!H:H,集計!D$4,DATA!K:K,">"&0)
COUTNIFS関数の記述方法は、MicroSoftサポートによると
=COUNTIFS (条件範囲 1, 検索条件 1,条件範囲 2, 検索条件 2)
です。
条件１は既に入っていますので、
=COUNTIFS(DATA!H:H,集計!D$4,条件範囲 2, 検索条件 2)になります。
「条件範囲２」は、「総客室数」（DATAシートのK列）なので、
=COUNTIFS(DATA!H:H,集計!D$4,DATA!:K:K, 検索条件 2)
となります。
「検索条件 2」は、「K列が０より大」なので、
=COUNTIFS(DATA!H:H,集計!D$4,DATA!:K:K, >0)
でよさそうですが、これではダメなのです。
「＞」が記号のため「””」で括り、「＆」で「０」と繋げます。
つまり、
=COUNTIFS(DATA!H:H,集計!D$4,DATA!:K:K, ">"&0)
となります。
数値ではない「＞」が入るので、「数値ではないですよ」とエクセルに知らせるために「””」で括るのです。
これは、こういうものだ、と捉えてください。
数値でなければ「””」で括って「＆」で繋ぐ、と覚えるしかありません。
計算結果を見ると・・・
「旅館・ホテル」の件数792に対し、総客室数の対象件数は791と出ました。
つまり「旅館・ホテル」のうち１件は「送客室数」が空欄ということです。
これが正しいか、確かめてみます。
フィルターで「旅館・ホテル」のみとしたうえで、「総客室数」の「空欄」を出します。（フィルターのやり方は「フィルター は、 Alt→A→T より Alt→D→F→F がおすすめかもをどうぞ。）
表示してみると・・・
「総客室数」が空欄のデータが１つありました（黄色は、目立つように手動でつけたものです。）。
これで、計算式で出した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＋１で「セルの書式設定を開いたら・・・
「ユーザー定義」の「種類」の欄に
「(#,##0)」と入れます。
これで、数値がカッコに入ります。
そのまま右クリックで文字の大きさも変えます。(11から9に）
「対象施設」の件数が控えめになりました。
項目も「(対象件数 件）」も９ポイントにしておきます。
ついでに、(対象施設数）の数が入るセルの上の罫線も消しておきます。
これで、参考数値である「対象施設」の項目と件数の両方が控えめになりました。すべてが同じ大きさだと、目が迷ってしまいますからね。
罫線も刑したことで数値の結びつきもはっきりします。
この「対象施設」の欄が合っているかは、「対象件数」の計算式を「検算」欄にコピーします。
その上で、上のグレーの部分を消します。
そうすると、K列（総客室数）が空欄でない（０より大きい）データの数が出ます（「営業の種類」が何であるかは問いません。）。
上の「判定」式をコピーして「OK」と出れば問題ありません（「合計」と「検算」を見比べれば、すぐにわかりますけど。）
「総定員」も同じことをします。
（「総定員」の欄をコピーします。「総客室数」をコピーすると、対象列が違ってしまいます。）
これで出来上がりです。
全部「OK」がでているので、計算式が間違っていなことが分かります。
お疲れさまでした。
COUNTIFS関数は複数の条件を設定できるため、とても重宝します。
ただし、セルが特定の数値より大きい（または、以上、以下、より小さい）の場合はちょっと面倒です。
「”記号”&数値」というパターンを忘れてしまうと、行き詰まります。
忘れたら、調べられるようにしておけばいいだけですが。
今回のファイルはこちらです。
この記事が気に入ったらサポートをしてみませんか？