[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関数は複数の条件を設定できるため、とても重宝します。
ただし、セルが特定の数値より大きい（または、以上、以下、より小さい）の場合はちょっと面倒です。
「”記号”&数値」というパターンを忘れてしまうと、行き詰まります。
忘れたら、調べられるようにしておけばいいだけですが。

今回のファイルはこちらです。