［Excel］データ集計をどう行うか？ 実際の自治体データをもとに考える⑬ SUMIFS関数を使い回す(相対参照と絶対参照）
このシリーズでは、サンプルデータとして、茨城県ホームページ 「旅館業」 ページ下部の「７．旅館業法に基づく許可施設一覧」にある 旅館業法に基づく許可施設 を使い、実際に集計しながら、その方法を説明しています。
過去記事は、マガジン「実際の自治体データで集計をしてみる」でどうぞ。
前の記事「実際の自治体データをもとに考える⑫ 「総部屋数」と「総定員数」を出す（SUMIFS関数）」で、「営業の種類」の「旅館・ホテル」について、「総客数」と「総定員」を、SUMIFS関数を使って出しました。
こんな感じです。トレース矢印を表示してあります。
今度は、「簡易宿所」「下宿」の「総客室数」「総定員」を出します。
ファイルはこちら。
読んでるだけだと「わかった気」で終わってしまいます。
実際に操作しながらなら、確実に身に着くと思います。
それぞれのセルに、一から計算式を入れるのは面倒です。
間違える恐れもあります。
計算式は使い回す
既に「旅館・ホテル」の「総客室数」「総定員」を出す計算式が入っていますので、これを使い回し(コピー）します。
エクセルでは、この「使い回し」(コピー）がとても重要です（エクセル以外も、ですが。）。効率化の「キモ」といえます。
といっても、単純に「旅館・ホテル」の計算式を右にコピー（Ctrl+R)してもダメです。
単純にコピーすると「簡易宿所」の「総客室数」の計算式は次のようになってしまいます。
計算式を見てみます（F2で計算式が表示されます。）。
この図では、計算式が見やすいように、左寄せにしています。
具体的に何がいけないのかというと・・・
見づらくてすみません。
計算式を右に１つコピーしたので、計算式の中身の列やセルも右に１つずつずれてしまっています。
しかし、この中で
「DATA」シートの「総客室数」の数値が入っているK列と、
「DATA」シートの「営業の種類」が入っているｈ列は、変わってはいけません。
計算が出来なくなります。
一方、検索条件を指定する最後のセルは「旅館・ホテル」から「簡易宿所」に変わって構いません（変わらないといけません。）。
コピーする計算式を修正してから、コピーする
コピーした後の計算式を修正してもいいのですが、それだ、右の「下宿」でも同じように、それぞれの計算式の修正が必要です。
それは面倒です。
ですので、「コピーする前の計算式」を修正します。
複数のセルにコピーしてもおかしくならないように修正しておくことで、コピーしたセルを一つ一つ修正する手間を省くのです。
コピー元のD6セルの計算式は次のとおりです。
=SUMIFS(DATA!K:K,DATA!H:H,集計!D4)
このうち、「DATA!K:K」は、足し上げるデータが入っている「総客室数」の列ですので、変わってはいけません。
「DATA!H:H」は、検索対象となる「営業の種類」であり、ここに「旅館・ホテル」「簡易宿所」「下宿」が入っているかを調べる列なので、ここも、変わってはいけません。
最後の「集計!D4」は、検索する種類ですが、これは「旅館・ホテル」から「簡易宿所」また「下宿」に変わらないといけません。
整理してみると・・
絶対参照と相対参照の付け方
変わってはいけないものには「＄」を付けて「絶対参照」に、
変わってもいいもの「＄」を付けないで「相対参照」にします。
D6の計算式
=SUMIFS(DATA!K:K,DATA!H:H,集計!D4)
↓↓↓
=SUMIFS(DATA!$K:$K,DATA!$H:$H,集計!D4)
「＄」は、変えたいところにカーソルを合わせて、F4を押します。
F4を押す度に、「＄」が付いたり、消えたりします。
列と行を指定している場合、
F4を押す → 行・列ともに＄が付く 例： $D$4
もう一度F4を押す → 行にのみ＄が付く 例： D$4
もう一度F4を押す → 列にのみ＄が付く 例： $D4
もう一度F4を押す → 何も＄が付かない 例： D4
となり、もう一度F4を押すと、行・列ともに＄が付きます。
F4を押すたびに、この繰り返しになります。
列だけ指定いる場合（本例）は、F4を押すたちに、＄が付いたり消えたりします。
修正するとこうなります。
修正した計算式を右にコピー（Ctrl+R)すると・・・
「下宿」の「総客室数」欄であるF6も正しい計算式になっています。
計算式のトレースを出してみると・・・
「DATA」シートの参照元は分かりませんが、表頭は正しく反映されていることが分かります。
続いて、「総定員」の計算式も
「旅館・ホテル」のD6セルを修正します。
D6セル
=SUMIFS(DATA!L:L,DATA!H:H,集計!D4)
↓↓↓
=SUMIFS(DATA!$L:$L,DATA!$H:$H,集計!D4)
セルをダブルクリックして（or F2)、「L:L」にカーソルを当てて、F4を押して、「$L:$L」にし、「H:H」にカーソルを当てて「$H:$H」にします。
（シート名のところにカーソルを当てても「＄」は付きません。）
同じように、右コピーすれば計算式が入ります。
合計欄は、左３つの単純計なので、上の「合計」欄を下にコピーします。
「施設数」の「合計欄」を含んで下セル２つを範囲指定して・・・
Ctrl + D で一番上のセルの計算式がコピーされます。
以上で集計表が出来上がりです。
なお、計算式の「絶対参照」は、式を作る際に入れることも可能です。
計算式をコピーすることが分かっていれば、その方が楽です。
ただし、混み入った計算式を作っているときに、「絶対参照にすべきか否か」まで考えていると混乱します。
ですので、慣れないうちは、計算式は計算式として作り（普通に作ると全て相対参照になる）、後から必要に応じて絶対参照にするといいでしょう。
修正が少なくて済む方法を考える
今回の集計表は以下の通り、８つのセルに計算式を入れる必要があります。
「え」「け」は上のセルの計算式をそのままコピーすればいいのですが、
「あ」～「く」は、どれをどうコピーするのが一番楽か、あらかじめ考えておくのが得策です。
主に２つの方法があります。
まずは、上述の方法で、先に下にコピーする方法です。
具体的には・・・
１「あ」の計算式を作成（その上とは関数が違うのでコピーはしない）
２「あ」を修正（絶対参照を入れる。計算式を作りながら入れてもいい。）
３「か」へコピー（①）
４「か」を修正（集計対象の列を「総客室数」から「総定員」に変える）
５「あ」を「い」「う」に右コピー（②） Ctrl + R
６「か」を「き」「く」に右コピー（③） Ctrl + R
７「え」「け」に上の計算式を下コピー（④）Ctrl + D
７工程になります。
５と６は同時に出来るので（コピー元を含めて、コピー先を範囲指定して、右コピー）、実質６工程です。
2つ目は、先に横にコピーする方法です。
具体的には・・・
１「あ」の計算式を作成（その上とは関数が違うのでコピーはしない）
２「あ」を修正（絶対参照を入れる。計算式を作りながら入れてもいい。）
３「あ」を「い」「う」に右コピー（①） Ctrl + R
４「あ」を「か」に下コピー（②） Ctrl + D
５「か」を修正（集計対象の列を「総客室数」から「総定員」に変える）
６「い」を「き」に下コピー（③） Ctrl + D
７「き」を修正（集計対象の列を「総客室数」から「総定員」に変える）
８「う」を「く」に下コピー（④） Ctrl + D
９「く」を修正（集計対象の列を「総客室数」から「総定員」に変える）
９「か」を修正（集計対象の列を「総客数」から「総定員」に変える）
10「え」「け」に上の計算式を下コピー（⑤）Ctrl + D
10工数にかかります。
コピーの回数も多いのですが、それよりも、計算式の修正が、上の２回に対して、下は４回も必要です。これ、結構手間です。
コピーはショートカットやオートフィル（「＋」が出たら右クリック×２）で簡単に出来ますが、計算式の修正は、間違いも出てきます。
ですので、なるべく、計算式を修正しなくていいようににします。
これはつまり、コピーの回数を少なくすることにつながります。
コピーの回数が少なくなれば、コピー先の計算式を直す回数も減ります。
今回の「絶対参照」と「相対参照」は、おそらく、多くの方あ躓くところかもしれません。
「絶対参照」「相対参照」自体は理解できても、計算式を使い回すときに、どこをどちらにしたらいいか、で迷うことが多いでしょう。
ここら辺は、理屈を理解したうえで実践あるのみ、ですが、大事なのは、計算式が正しいか確認するということです。
計算結果が出ても、それが正しいとは限りません。
常に「間違っているかも」と思いながら、検算をしていく必要があります。
（特に「慣れてきた頃」が一番危険です。）
では、上の表、どう検算すればいいでしょうか？
これは次回。
この記事が気に入ったらサポートをしてみませんか？