［Excel］データ集計をどう行うか？ 実際の自治体データをもとに考える⑭ SUMIFS関数で出した数を検算する
このシリーズでは、サンプルデータとして、茨城県ホームページ 「旅館業」 ページ下部の「７．旅館業法に基づく許可施設一覧」にある 旅館業法に基づく許可施設 を使い、実際に集計しながら、その方法を説明しています。
過去記事は、マガジン「実際の自治体データで集計をしてみる」でどうぞ。
前の記事「実際の自治体データをもとに考える⑬ SUMIFS関数を使い回す(相対参照と絶対参照）」で、「営業の種類」の「旅館・ホテル」以外の「簡易宿舎」「下宿」についても「総客数」と「総定員」を、SUMIFS関数を使って出しました。
こんな感じです。グレーのところが、前回追加した集計です。
今回はグレーの部分、つまり、「簡易宿所」「下宿」の「総客室数」「総定員」が、ちゃんと計算されているかを検証（検算）してみます。
ファイルはこちら。
読んでるだけだと「わかった気」で終わってしまいます。
実際に操作しながらなら、確実に身に着くと思います。
前回までで、「営業の種類別」の「総客室数」「総定員」を出しました。
この集計結果が合っているか、どう確認すればよいでしょうか？
といっても簡単です。
表内の「営業の種類」の「総客室数」「総定員」の合計が、「DATA]シートの「総客室数」「総定員」の合計と合っていれば、問題ありません。
表内の「営業の種類」の「総客室数」「総定員」の合計は、もう出ています。
下のグレーの部分です。
では、「DATA]」シートの「総客室数」「総定員」の合計はどうやって出すか？
これも簡単です。
「検算用」欄に計算式を入れてみます。
=SUM(DATA!K:K) と、「DATA」シートのK列の合計を出すだけです。
=sum( と打ったら、「DATA」シートに移って、列の「K」の部分をクリックし（K列が点線で囲まれる）、後は、Enter。以上。
表頭（１行目）が入っても構いません。
わざわざデータの入っている範囲を行指定する必要はありません。
それはムダな作業です。
同じことを「総定員数」でも行います。
ここは、上の計算式をコピーするより、最初から計算式を手入力した方が早いでしょう。（コピーすると集計列がずれるので。）
=SUM(DATA!L:L) と入れます。
これで出来上がり。
次に、「判定」セルの計算式を 下の２つにもコピーします（Ctrl+D)。
計算式、そして、計算式のトレースも表示してみます。
H6の「判定」欄の計算式は =IF(G6=H6,"OK","相違!!")
この詳細は「データ集計をどう行うか？ 実際の自治体データをもとに考える⑧ 検算で集計ミスがないか確認する」でどうぞ。
結果、みんな「OK」が出ているから、集計は合っている。以上で終わり。
・
・
・
・
・
・
・
と言いたいけれど・・・、トラップあり
なんと、この一覧表、「総客室数」と「総定員数」に空欄のデータがあるんです！
「空欄トラップ」です。
フィルターを掛けて「総客室数」の「空白セル」だけ抽出してみると・・・
グレーのところが空欄です。
「総客室」が空欄である一方、「総定員数」には数値が入っているデータもあります。
フィルターに関しては「基本のショートカット フィルター は、 Alt→A→T より Alt→D→F→F がおすすめかも」をどうぞ。
私は、Alt → D → F → F で出た「▼」をクリックしたら、（すべて選択）をクリックして全部の☑を消し、一番下までスクロールして空欄セルだけ☑を付けてOK、です。
「総定員数」で同じことをやってみると・・・
やはり、空欄があります。
しかも「総客室数」には数値が入っている一方、「総定員数」だけが空欄のものもあります。
さて、困りました。
空欄があるのに、果たして「全データ」の「合計」といっていいのか？
そもそも、行政が出すデータとして空欄があってよいのか、と思いますが、ここではそこには触れません。
とりあえず、目の前にあるデータを元に、どう処理するか、を考えます。
この場合、「空欄」があるデータを、全体件数に入れてしまうのは不適当と考えます。
つまり、「総客室数」または「総定員数」に数値が入っているデータだけを集計対象として件数を出します。
簡単にいえば、「データは100件あるけど、数値不明が40件あるので、「合計」欄の数値は100件の合計ではなく、有意な数値が入っている60件の合計です」と明示するのです。
そうでないと、合計欄は、「100件の数値の計」であると誤解されてしまいますので。
では、どうすればいいのでしょうか？
難しくありません。
「総客室数」欄、「総定員数」欄、それぞれに数が入っているデータの数を数えればいいだけです。
計算の前に、計算結果を入れる欄を作っておきます。
こんな感じです。
枠の作り方は省略しますが、２つのセルにまたがっている「対象施設 件）に、「セルの結合」は使っていません。「選択範囲で中央」にしています（ここはまたいずれ。）。
ここに計算式を入れます。
件数を出す計算式は
「実際の自治体データをもとに考える⑦ 許可の種類別の件数を集計する（COUNTIFS関数）」で説明した通りです。
「どうやるんだっけ？」と思ったら、上の記事を再読したうえで、以下を見ることをお勧めします。
今回は、全体の件数ではなく、「総客室数」「総定員数」の欄にデータ入っているものの件数だけを出す必要があります。
計算式は、ちょっと複雑な説明になりますので、次回に回します。
引っ張ってすみませんが、この計算ができると、かなり応用が利くようになります。
この記事が気に入ったらサポートをしてみませんか？