haku1569　Excel でらくらく データ分析！

ExcelのSUMIF関数を使って予算表を見える化してみました。

例えば、年の初めに今年の主な出費の予定を立ててみました。
で、実施する月に“〇”を付けた時に下に金額が表示されるようにしようと思います。
もちろん、7月の様に複数実施する場合は合計金額が表示されるようにしようと思います。


なんで、こんな表を作るかというと。。。
普通、こんな表を「クロス集計表」と言います。縦横から交わった（クロスした）値を集計するみたいな意味なんでしょうきっと、で、それってあくまで集計結果を見やすく表示する手段なわけなんですが、今回作ろうとしている表は、データを作るための表なんです。
どういうことかつーと、
普通まず初めにこんな出費の予定表を作ります。


で、それを元にクロス集計表を作るのですが、こんな場合はと言うか、たいていの場合ピボットテーブルを使うとそれが簡単にできます。
「ピボットテーブル」を挿入します。


で、行ラベルに「項目」、列ラベルに「実施月」、値に「予算」を設定します。


すると、こんなクロス集計表が自動的に出来上がります。


なんですが、最初の出費の予定表を作る時に項目の脇に実施月を入力するのはちょっとやり辛いというか、全体が見え辛いと思いませんか？
できれば、横軸に1月〜12月の1年があって、その中から月の出費金額を確認しながら、出来れば出費が偏らないようにとか、ボーナス月に合わせたりとか、季節商品の場合はその時期を見計らったりとか、、、で、“〇”を付け替えるとリアルタイムに月の出費金額が更新される様な表があると予定を立て易いと思うんです。でしょ？
つまり、見える化です。

というわけで、お待たせ致しました。ようやく本題です。
お急ぎの方は、前段の部分を飛ばしてここから読み始めてください！？

最初の表の月の合計の欄に“SUMIF”関数を挿入します。


範囲は1月の列の範囲


検索条件は“〇”。つまり、“〇”が入力されているセルを探します。
合計範囲は予算の列範囲にします。


で、この1月の合計のセルに入力した式を12月までコピーするの合計範囲（予算の列範囲）は“$”マークを付けて絶対参照範囲にしておきます。


で、1月は何も実施予定がありません（“〇”がない）ので“0”です。


で、これを12月までコピーするとこの様に各月の予算の実施金額の合計が表示されました。
〇を付け替えると自動的に金額が集計しなおされます。


これで、予算立てがかなりやりやすくなりました！

・・・？

あれっ、間違った！
珈琲メーカーが3月と8月にダブって“〇”が付いちゃってます。
んで、車検が抜けっちゃってます。
8月に実施予定の車検の“〇”が間違って珈琲メーカーに付いてしまってました。。。

ありがちな間違いですよねぇ〜。。。人間だから。。。こう言うの“ヒューマンエラー”って言うんでしょ？
こんな家庭の出費予定なら、珈琲メーカーは2台は購入しないのでよく確認すれば気が付きますが、会社の予算なんかだと、同じ物を年に複数回実施する場合もあるので、この様なミスが発見しづらくなします。

予算と実際に“〇”を付けた合計金額が合っているかどうかをチェックするようにしてみましょう。

まず、一番右端の列にチェックさせる式を入力します。
“COUNTA関数”を使って、範囲内の空白でないセルの個数、つまりここでは“〇”の数を数えます。


と、「パソコン買替え」は2月に“〇”が付いているだけなので“1”になります。


次に、それに予算の金額“\100,000”をかけます。
すると、1*100,000 で“\100,000”となりました。


で、今度はその結果と予算の金額が合っていたら“OK”、そうでなかったら“NG”と“IF関数”を使って表示させます。


すると、このように、“珈琲メーカー”と“車検”が“NG”と表示されました！


このままでは分かり辛いので“NG”を目立つようにしたいと思います。
チェックのセル範囲を選択して、「条件付き書式」を選択します。


「セルの強調表示ルール」で「指定の値に等しい」を選択します。


“NG”の書式をこの様に（既定値がこうなってます）します。
注）NGはダブルクォーテーション（“ ”）は要りません。


するとこのように目立つようになりました。


で、修正しました。



めでたし、めでたし！

関連ページ
SUMIF関数で条件に一致したセルの合計を求めてみた
クロス集計表を簡単に作成する

で、問題の珈琲メーカーなんだけど、先日実際にこれを購入しました。
ミル付きの珈琲メーカーはたくさんありますが、それっていうのはミルが外付けされているような構造で、粉にした珈琲を手動でドリッパーに供給しなければなりません。
それに対し、全自動珈琲メーカーと言うのは豆を入れてスイッチを入れるだけで自動で粉がドリッパーに落ちて珈琲がはいるもので、国産だとパナソニックと最近出てきた象印の2機種位ではないでしょか。
購入したのは珈琲メーカーでは古くから作っているパナソニックの NC-A56-K
豆を挽く音と漂う香り、いい。。。

NC-A56-K【税込】 パナソニック 沸騰浄水コーヒーメーカー　ブラック Panasonic [NCA56K]【返品種別A】【送料無料】【RCP】 価格：23440円（税込、送料無料) (2017/7/23時点)