［Excel］データ集計をどう行うか？ 実際の自治体データをもとに考える⑫ 「総部屋数」と「総定員数」を出す（SUMIFS関数）
このシリーズでは、サンプルデータとして、茨城県ホームページ 「旅館業」 ページ下部の「７．旅館業法に基づく許可施設一覧」にある 旅館業法に基づく許可施設 を使い、実際に集計しながら、その方法を説明しています。
過去記事は、マガジン「実際の自治体データで集計をしてみる」でどうぞ。
前の記事「データ集計をどう行うか？ 実際の自治体データをもとに考える⑪ テキスト形式のデータを数値に直す（データクレンジング）」で、テキスト形式になっている「総部屋数」と「総定員数」のデータを数値に直しました。
これで集計に入れます。
ファイルはこちら。
読んでるだけだと「わかった気」で終わってしまいます。
実際に操作しながらなら、確実に身に着くと思います。
今回は、この表に計算式を入れます。
SUMIFS関数で条件にあったデータの計を出す
まず、「旅館・ホテル」の「総客室数」を出します。
単純な合計なら SUM関数を使えばいいのですが、
「営業の種類」が「旅館・ホテル」の「総客室数」を出すということは、
下図の赤色セルのデータの黄色セルの数値を足し上げる、ということです。
「総客室数」つまり黄色のセルの数値を足し上げてる必要があります。
こういった「一定の条件（H列が「旅館・ホテル」）のデータの数値（総客室数）だけを足し上げる、という場合は
=SUMIFS関数
を使います。
Microsoftサポートには
SUMIFS(合計対象範囲, 条件範囲 1, 条件 1, [条件範囲 2, 条件 2], ...)
と記述するとされています。
これだけだと、よくわかりませんよね？
「合計対象範囲」は合計を出したい数値が入っている範囲。
今回なら「総客室数」の列、つまりK列。
通常は、列全体の範囲指定で大丈夫です。
わざわざ「H2:H1028」と範囲を行指定する必要はありません（WEBやエクセル本では、ほぼ行指定しています。説明のためでしょうが、ムダです。）。
「条件範囲 1」は、「条件に当てはまるものが入っている範囲」。
本例なら「営業の種類」の列。つまりH列。
これも列全体の指定で構いません。
「条件 1」は、上の「条件範囲１」から括り出すための条件です。
本例では、「集計」シートの表頭（ヘッダー）の「旅館・ホテル」。
つまりD4セルになります。
SUMIF関数は使わない。SUMIFS関数を使う。
本例での条件は「営業の種類」が「旅館・ホテル」の１つだけです。
ですので、SUMIFS関数（複数の条件を設定できる）を使わなくても、
SUMIF関数（条件が一つだけの場合に対応）で集計できます。
でも、あえてSUMIF関数は使いません。
条件が１つだけでも、SUMIF関数を使うのは御法度です。
SUMIF関数は覚える必要もありません。
なぜなら、SUMIFS関数は条件が１つだけの時でも使えるからです。
そして、SUMIF関数とSUMIFS関数では、記述方法が違うからです。
SUMIF(範囲, 検索条件, 合計範囲)
SUMIFS(合計範囲, 条件範囲 , 条件 )・・・条件が１つだけの場合
詳細は別途記事にしますが、既述の順番が違います。
似た関数で記述方法が違うと、混乱が生じます。
そのため、条件が１つでも複数でも使えるSUMIFS関数だけを使います。
SUMIFS関数を実施に使ってみる
SUMIFS関数に、具体的にデータや条件を入れていきます。
ます「観光・ホテル」の「総客室数」の計算結果を表示するC6セルをクリックし、「 ＝SUMIIFS( 」と入れます（半角）。
=s と入れると関数の選択肢が出るので、そこから選んでもいいのですが、Sで始まる関数は多く、選ぶのが面倒です。
直接 「 =sumifs( 」と入れた方が早いでしょう。最後の「 ( 」 を忘れずに。
次に「DATA」シートに移り、数値の合計を出すK列を指定します。
Kの部分をクリックすればおしまいです。
表頭（ヘッダー）の項目が入っていても構いません。
わざわざデータ最後のセルまでスクロールする必要はありません。
K列を選んだら、後ろに「,」を入れてください。
これ、忘れがちです。
次に、データを選ぶ列、つまり「営業の種類」の列を選択します。
ここでも、「H」の部分をクリックするだけです。
「DATA!H:H」と入るので、その後に「,」を入れます。
最後に、条件である「旅館・ホテル」が入っている「集計」シートのD4をクリックします。
これでEnterを押せば完了です。最後の「 ) 」は入れなくて大丈夫です。
数式バーに、=SUMIFS(DATA!K:K,DATA!H:H,集計!D4) と入っているのが分かります。念のためD８セルにも表示しています。
これで「営業の種類」が「旅館・ホテル」である施設の部屋数の合計（総部屋数）が出ました。
セルにエラーが出た場合は・・・
=sumifs の綴りを間違えるとエラーが出ます。
下の例では =sumifs が =sumiff となっているために、「関数の名前がおかしいよ」という意味の「#NAME?」というエラーが表示されています。
この場合、最初からやり直していいのですが、ダブルクリック 又は F2でセルをアクティブ（修正可能）にして、直接、=sumiff の f を s に直しても構いません。（=sumifsと手入力ではなく、=s と入れてから、SUMIFSを↓で選んでTabで選択すれば、誤入力は避けられます。）
また、選択すべき列やセルが足りない、あるいは区切りの「, 」がないと、こんなエラーが出ます。↓↓↓
上の例は、「DATA!H:H」と「集計!D4」の間の「,」を入れ忘れたため、データの区切りがないと認識され、「少なすぎる引数が選択されています」と出ます。「引数」とはわかりづらい表現ですが、「数値やセルや列など」位の理解でいいでしょう。
この場合は、「OK」を押して、エラー表示を消して、「,」を入れれば大丈夫です。エラーの理由がよくわからなければ、一旦ESCで計算式を消して、最初からやるのも手です。
念のため、「トレース」機能で矢印を出してみます。
消すときは、Alt→M→A→A または上書き保存です。
「旅館・ホテル」を参照していることが分かります。
ただし、別シートである「DATA」は表示されません。
合計が、23,500余というのは、結構多いな、というのが私の感想です。
（この合計、厳密には正しいとはいえませんが、それについては別記事で）
では、「旅館・ホテル」には、最大何人が泊まれるのでしょうか？
それは「旅館・ホテル」の「総定員」を出せばわかります。
上と同じように計算式を入れていってもいいのですが、今回は上で作った計算式を「使い回し」してみます。
「楽をする」ためには「使い回し」の発想はとても重要です。
とはいえ、単純に上の計算式をコピー（Ctrl+D)すると、参照元がずれてしまいます。
こんな風にです。↓↓↓
条件である「旅館・ホテル」が、その下の施設数の792になってしまっています。これでは、正しく計算できません。（DATAシートのH列に「792」と入っている行のK列の数値を足し上げる、という計算式になっています。H列「営業の種類」に「792」はないので、当然、答えは「０」です。）
この場合、計算式を直接直しても、構いません。
でも、こんな方法もあります。
Shift + Ctrl + 7 で上のセルの計算式をそのままコピーする
Shift + Ctrl + 7 というショートカットを使うと、上のセルの計算式が「そのまま」コピーされます。計算式内のセルは変わりません。
これなら、条件である「旅館・ホテル」もずれません。
しかし、「総定員」は「DATA」シートのK列ではなく、同じく「DATA」シートのL列です。
ですので、「DATA!K:K」を「DATA!L:L」に修正します。
手入力で「K」を「L」に直してもいいのですが、私は、「DATA!K:K」を削除してから（「,」は消さない）、改めて「DATA」シートのL列をクリックする方法にしています（手入力は誤入力をつながるので。）。
これで、D7セルには、「旅館・ホテル」の「総定員」が入りました。
総部屋数の23,507に比べて、総定員が53,000余とは、少ない気もしますが、ビジネスホテルなどはシングルルームが主ですので、こういう数値になっているのかもしれません。こういった分析はまた別途考えます。
（上述の「総客室数」同様、この数値は、実は正しくありません。詳細は後の記事で。）
なお、計算式の焼き回し(コピー）の仕方は色々あります。
上の例では、「上のセルの計算式をそのままコピーして修正」という方法をとっていますが、通常は、コピーする計算式の入っているセルと、コピー先のセルの位置関係を考えて、「絶対参照」や「相対参照」を使い分けて行う場合が多いでしょう。
その方法は次回お示しします。
今回はここまでです。お役に立てば幸いです。
この記事が気に入ったらサポートをしてみませんか？