上司から過去5年間の都道府県別の住宅着工棟数の一覧表を作ってください。って依頼があった場合あなたならどうしますか？
5分でできる方法があります。
まずは国土交通省のホームページにアクセスして過去5年間の都道府県別のデーターをダウンロード。
そしてこのデーターベースを元にSUMIFS関数を使って表作成。
次年度からはこのデーターベースに追加をすれば将来にわたって使えるエクセルです。
SUMIFSって
最後にSがついています。これは複数の条件を処理するってことです。
複数の条件にマッチするところの合計を計算しなさいっていう関数です。
【用途】：複数の検索条件に一致したセルを検索し、見つかったセルと同じ行や列にある合計範囲のセルの数値の合計を求めることができる。
【書式】：=SUMIFS(合計対象範囲 , 検索範囲1 , 条件1, 検索範囲2 , 条件2 , …）
目次
データーベースの作成
シート名をｄｂ
国土交通省>報道・広報>報道発表資料>建築着工統計調査報告
各年の1月末日頃の発表に前年1年間の資料あり
私がダウンロードした内容はここからです。
平成28年1月～12月分着工新設住宅戸数：利用関係別・都道府県別表（単位：戸、％）
平成27年1月～12月分着工新設住宅戸数：利用関係別・都道府県別表（単位：戸、％）
平成26年1月～12月分着工新設住宅戸数：利用関係別・都道府県別表（単位：戸、％）
平成25年1月～12月分着工新設住宅戸数：利用関係別・都道府県別表（単位：戸、％）
平成24年1月～12月分着工新設住宅戸数：利用関係別・都道府県別表（単位：戸、％）
それぞれの国土交通省からのエクセルデーターを値貼り付けでＢ列からＰ列のＤＢを作ります。
A列には集計年（平成28年なら28を入力）
ここで注意がひとつあります。
都道府県の北海道と地域の北海道と北海道がＢ列に2つあるので地域の北海道を北海道（地域）に書き換えます。
なぜかというと都道府県の北海道と地域の北海道を両方たしてSUMIFSが計算するからです。
また51行目の合 計もわかりやすいように全国に書き換えています。
同じ作業を27年、26年、25年、24年と書き換えます。
29年が発表になれば最下位から追加します。
集計表の作成
シート名を表とします。
横軸に用途
縦軸に集計年
Ｚ軸（A2セル）に都道府県・地域
ｄｂの総戸数、持家、貸家、給与、分譲、分譲マンション、分譲一戸建ての集計をするのでこのタイトルをC3からI3に入力
B4からB8までに集計年を入力
ここは将来変更してもいいです。
C1からI1までのC、E、G、I、K、M、Oはシートｄｂにある列の表示です。
つまり持家はシートｄｂのE列にあるよ。
分譲一戸建てはシートｄｂのO列にあるよといった内容です。
A2のセルは計算する都道府県及び地域を入力するセルです（黄色で色付けしています）
試しに東京といれておきましょう。
準備はこれで完了
C4セルにsumifs関数で入力します。
合計対象範囲：シートｄｂのC4からC1000（絶対参照）
（現在のデータは314行までですが、将来データが追加されてもいいように1000行までを選んでいます）
検索範囲1：シートｄｂのA4からA1000（絶対参照）
条件1：B4（相対参照）
検索範囲2：シートｄｂのB4からB1000（絶対参照）
条件2：A2（絶対参照）
C4セルには
=SUMIFS(ｄｂ!$C$4:$C$1000,ｄｂ!$A$4:$A$1000,$B4,ｄｂ!$B$4:$B$1000,$A$2)
の計算式が入って答えは140,862となります。
C4をコピー元にしてD4からI4まで貼り付けます。
同じ答えになりますね。
D4セル
=SUMIFS(ｄｂ!$C$4:$C$1000,ｄｂ!$A$4:$A$1000,$B4,ｄｂ!$B$4:$B$1000,$A$2)を
=SUMIFS(ｄｂ!$e$4:$e$1000 以下は変更なし）
合計対象範囲をC列からE列に変更
E4セル
=SUMIFS(ｄｂ!$C$4:$C$1000,ｄｂ!$A$4:$A$1000,$B4,ｄｂ!$B$4:$B$1000,$A$2)を
=SUMIFS(ｄｂ!$g$4:$g$1000 以下は変更なし）
合計対象範囲をC列からG列に変更
F4セル
=SUMIFS(ｄｂ!$C$4:$C$1000,ｄｂ!$A$4:$A$1000,$B4,ｄｂ!$B$4:$B$1000,$A$2)を
=SUMIFS(ｄｂ!$i$4:$i$1000 以下は変更なし）
合計対象範囲をC列からI列に変更
G4セル
=SUMIFS(ｄｂ!$C$4:$C$1000,ｄｂ!$A$4:$A$1000,$B4,ｄｂ!$B$4:$B$1000,$A$2)を
=SUMIFS(ｄｂ!$k$4:$k$1000 以下は変更なし）
合計対象範囲をC列からK列に変更
H4セル
=SUMIFS(ｄｂ!$C$4:$C$1000,ｄｂ!$A$4:$A$1000,$B4,ｄｂ!$B$4:$B$1000,$A$2)を
=SUMIFS(ｄｂ!$m$4:$m$1000 以下は変更なし）
合計対象範囲をC列からM列に変更
I4セル
=SUMIFS(ｄｂ!$C$4:$C$1000,ｄｂ!$A$4:$A$1000,$B4,ｄｂ!$B$4:$B$1000,$A$2)を
=SUMIFS(ｄｂ!$o$4:$o$1000 以下は変更なし）
合計対象範囲をC列からO列に変更
C4からI4をコピー元にして8行まで引っ張って貼り付け完了
A2セルを全国、関東、埼玉、大阪とか変更してみるとそれぞれの地域あるいは都道府県での表かできます。
一度作っておくと将来依頼があった場合でも計算元となるデーターベースを追加するだけで5分以内に報告が可能です。
罫線やグラフ作成して見やすい表を作成してください。
コメントを残す