【第4回】グラフ作成― 集計・見える化・IF関数 ―｜ハムすけのスプシ教室

この記事は👇️こちらの記事を学習済みであることを前提としています

【第3回】表を整える― 見やすく・探せて・ミスに気づける表作り ―｜ハムすけのスプシ教室

はじめに

スプレッドシートに少し慣れてくると、

👤 「入力はできるけど、この表をどう活かせばいいの？」

👤 「集計やグラフって難しそう…」

👤 「予算オーバーしているか、ぱっと見でわかるようにしたい」

と、「入力の次の一歩」で止まりやすいです。

でも実は、ここでやることも少しずつ順番に進めれば大丈夫です。

カテゴリごとに集計する、グラフで見える化する、予算と比べる、条件で判定する。

この流れがつながるだけで、表は「記録するだけの表」から「判断できる表」に変わっていきます。

この記事では、スプレッドシート初心者の方向けに

・SUMIFでカテゴリ別に集計する
・円グラフで支出の割合を見える化する
・予算との差額を計算する
​IF関数で状態をわかりやすく表示する

という流れを、1つずつ手を動かしながら進めていきます。

「関数やグラフはまだ少し苦手…」という方でも大丈夫です。

今回は、家計管理表をもとに、表を少しずつ育てながら学べる内容に絞ってまとめました。

この記事を読むとできるようになること

配布資料は👈️こちら

STEP1｜グラフ用の補助表を作る

🎯ねらい

 元データをカテゴリごとに集計して、グラフの元になる表を作る。

✏️やること

①H列にカテゴリ名を並べる
②I列にSUMIF関数を入れる
③数式を下までコピー（オートフィル）するときに、参照の動き方を学ぶ

・数式をコピーすると、参照先は自動で動く

参照とは？

今回は、H列のカテゴリ名を見ながら集計したいので、条件に使うセルは下にコピーしたときに

 H7 → H8 → H9 

と動いてほしいです。

一方で、元データのカテゴリ列や金額列は毎回同じ場所を見るので、固定して使います。この「動いてほしい」「固定したい」の違いが、参照の考え方です。

たとえば I7 に入れる式は、こういう形です

この式だと、

・$C$3:$C は固定
・H7 は下にコピーしたら H8 H9 に変わる
・$E$3:$E も固定

になります。

STEP2｜円グラフを作る

🎯ねらい

STEP1で作った補助表をもとに、カテゴリごとの支出割合を見える化する。「何にどれくらいお金を使っているか」を、表だけでなくグラフでも把握できるようにする。

✏️やること

①STEP1で作った補助表（カテゴリ名と合計金額）を選択する
②挿入メニューからグラフを作成する
③グラフの種類は円グラフを選択する
④タイトルや表示を見やすく整える

まず伝えたいこと

・円グラフは、割合を見るのに向いている
・今回は「どのカテゴリにどれくらい使っているか」を見るため、円グラフと相性がよい
・元データから直接作るのではなく、補助表から作ると見やすい

進め方の流れ

① 補助表を選択する
② グラフを挿入する
メニュー　→　挿入　→　グラフ

③ 円グラフにする
④ 見やすく整える

・タイトルをつける　→　例：3月の支出割合
・凡例の位置を必要に応じて調整する
・小さすぎて見づらい場合は、グラフのサイズや位置を整える

✅チェック

◻️補助表をもとに円グラフが作成できた
◻️カテゴリごとの支出割合が見える
◻️「何にお金を多く使っているか」が一目でわかる

STEP3｜予算との差額を計算する

🎯ねらい

家計管理表を「記録するだけ」で終わらせず、予算と比べて判断できる表に進化させる。今回は、実績と予算を見比べるために、まずは引き算で「差額」を出していく。

※動画の中で、差額の出し方が間違っていますが、次の動画で訂正しています🙇

✏️やること

①予算を書き込む列を作る
②実績の横に予算を並べる
③引き算で差額を計算する

まず伝えたいこと

家計簿は、入力して終わりにするだけだともったいないです。「今いくら使ったか」だけでなく、「予算と比べてどうなのか」まで見えるようになると、表がぐっと実用的になります。

そのために今回は、

・実績
・予算
・差額

を並べて見られる形にしていきます。

ここで大事なのは、いきなり IF関数で判定するのではなく、まずは引き算で差額を出すことです。

進め方

① 予算を入力する列を作る
カテゴリ別集計表の右側に、「予算」の列を追加します。たとえば、次のような形です。

H列：カテゴリ
I列：実績
J列：予算
K列：差額

J列には、それぞれのカテゴリごとに予算を入力します。

たとえば、

食費：15000
交通費：5000
娯楽：3000

のように入れていけばOKです。

② 実績と予算を横に並べる

ここで見てほしいのは、「いくら使ったか」だけでなく、「いくらまで使ってよい予定だったか」も並んで見えることです。

③ 差額を引き算で出す

次に、K列に差額を出します。差額は、

のように入力します。

これは、

という意味です。

たとえば、

・予算が15000円
・実績が18810円
→15000-18810=-3810

となるので、3810円オーバーしていることがわかります。

逆に、

・予算が15000円
・実績が12000円
→15000-12000=3000

となるので、まだ3000円余裕があるとわかります。

✅チェック

◻️予算の列を作成できた
◻️実績と予算が横に並んだ
◻️差額を引き算で表示できた

⚠️つまずきやすいところ

① 数字ではなく文字として入力してしまう予算欄に「15000円」と入れると、うまく計算できないことがあります。まずは数字だけを入力し、必要なら表示形式で円に整えましょう。

STEP4｜IF関数で判定を表示する

🎯ねらい

STEP3で計算した差額をもとに、予算の状態をひと目でわかるようにする今回はIF関数を使って、「余裕あり」「注意」「予算オーバー」の3段階で表示していく

✏️やること

・差額を見て、どんな判定を出したいか決める
・IF関数で2段階の判定を試す
・IF関数の入れ子で3段階の判定にする

IF関数の基本

IF関数は、「もし◯◯なら、△△を表示する。そうでなければ✕✕を表示する」という考え方の関数です。

まずはシンプルに、2段階で考えてみます。

たとえば差額がマイナスなら予算オーバーなので、次のように書けます。

この式は、

K3が0より小さければ「予算オーバー」
→そうでなければ「OK」

という意味です。

でも今回は3段階で表示したい

今回は「OK」だけでまとめるのではなく、もう少し細かく分けてみます。

・差額が10000以上 → 余裕あり
・差額が0以上10000未満 → 注意
・差額が0未満 → 予算オーバー

このように、結果を3つに分けたいときは、IF関数を1つだけでは足りません。

そこで使うのが、IF関数の入れ子です。

入れ子とは、IF関数の中に、もう1つIF関数を入れることです。

つまり、最初の条件に当てはまらなかったときに、さらに次の条件を確認するイメージです。

進め方

① 判定列を作る

差額の右側に「判定」の列を追加します。
たとえば、L列を判定列にすると見やすいです。

K列：差額
L列：判定

② まずは2段階のIFを試す

まずは先ほどと同じような数式を入れてます

③ IF関数の入れ子で3段階にする

次に、Bをさらに2つに分けます。

B = IF(K3>=0,"注意","予算オーバー")
※中学の数学の授業の「代入」を思い出してください！笑

この式は、順番にこう判断しています。

①まず、差額が10000以上かを見る
②10000以上なら「余裕あり」
③そうでなければ、次に0以上かを見る
④0以上なら「注意」
⑤どちらでもなければ「予算オーバー」

つまり、上から順番に条件をチェックしている形です。

✅チェック

◻️判定列を作成できた
◻️IF関数で2段階の判定を試せた
◻️IF関数の入れ子で3段階の判定ができた
◻️「余裕あり」「注意」「予算オーバー」が自動で表示されるようになった

⚠️つまずきやすいところ

①ダブルクォーテーションを忘れる

表示したい文字は、ダブルクォーテーションで囲みます。

たとえば、

　"余裕あり"
　"注意"
　"予算オーバー"

のように書きます。

② かっこの閉じ忘れ

入れ子にすると、かっこが増えます。
入力したあとに、開きかっこと閉じかっこの数が合っているか確認しましょう。

プラスアルファ1｜AVERAGE関数で平均を見る

🎯ねらい

合計だけではなく、平均という見方にも触れて、数字の読み取り方を1つ増やす。
今回は AVERAGE関数を使って、1回あたりの支出が平均でいくらなのかを見ていく。

✏️やること

・金額が入力されている範囲を確認する
・AVERAGE関数で平均支出を出す
・合計と平均の違いを見比べる

AVERAGE関数とは

AVERAGE関数は、選んだ範囲の平均値を出す関数です。

たとえば、金額が E列に入っているなら、次のように書きます。

この式は、
 E3から下までの数字の平均を出す という意味です。

✅チェック

◻️金額が入っている範囲を選べた
◻️AVERAGE関数を入力できた
◻️平均支出額を表示できた

プラスアルファ2｜COUNT関数で件数を見る

🎯ねらい

合計金額だけでなく、「何件入力されているか」という見方もできるようにする。今回は COUNT関数を使って、数字が入っているセルの件数を数えてみます。

✏️やること

・数えたい範囲を確認する
・COUNT関数で件数を数える

COUNT関数とは

COUNT関数は、選んだ範囲の中にある数字の件数を数える関数です。
たとえば、金額が E列に入っているなら、次のように書きます。

この式は、
 E3からE22までの中で、数字が入っているセルを数える という意味です。

✅チェック

◻️数えたい範囲を確認できた
◻️COUNT関数を入力できた
◻️金額が入力されている件数を表示できた
◻️合計金額と件数を見比べられた

⚠️つまずきやすいところ

①COUNTは数字だけを数える 
COUNT関数は、文字は数えません。 今回は金額欄を数えるので使いやすいですが、文字が入っている列では思った結果にならないことがあります。

②見出しまで範囲に入れてしまう
 「金額」という見出しは数えなくてよいので、数字が入っている範囲だけを選びましょう。

③空欄は数えられない 
空欄のセルは件数に入りません。 そのため、数字が入っているセルだけが対象になります。

プラスアルファ3｜VLOOKUPで予算を自動取得する

🎯 ねらい

別シートに用意した予算表から、カテゴリごとの予算を自動で取り出せるようにする

✏️ やること

・別シートに「カテゴリ」と「予算」の表を作ります
・集計表の予算欄に VLOOKUP関数を入力します
・予算表の数字を変えて、差額や判定が連動して変わるか確認します

VLOOKUP関数とは

表の左端にある項目を手がかりにして、右側にある情報を取り出す関数です。

たとえば、別シートに
食費　15000
交通費　5000
娯楽　3000

のような予算表があるとします。

このとき、集計表で「食費」というカテゴリを見つけたら、
その右にある「15000」という予算を自動で持ってくることができます。

つまりVLOOKUP関数は、
別の表から、必要な情報を探して持ってくる関数
と考えるとわかりやすいです。

✏️ 手順

新規でシートを作成し、カテゴリと予算を作成
→もともとのシートでVLOOKUIP関数を入力する

VLOOKUP関数はどんなときに使う🤔？

① 商品コードから商品名や価格を表示するとき

たとえば注文表に「商品コード」だけ入力して、
別の商品マスタ表から

・商品名
・単価
・在庫数

を自動で表示する使い方です。

例）

注文表に A001
商品マスタで A001 → ノート、120円、在庫50

このとき、商品コードを手がかりにして、商品名や価格を持ってきます。

②社員番号から氏名や部署名を表示するとき

たとえば勤怠表や申請表で、社員番号を入力すると、
社員マスタから

・氏名
・部署
・役職

を自動表示する使い方です。

例）

入力：1023
社員マスタ：1023 → 田中さん、営業部

毎回名前を手入力しなくてよくなるので、入力ミス防止にも役立ちます。

③顧客コードから顧客情報を表示するとき

営業管理や顧客管理では、顧客コードをもとに

・会社名
・担当者名
・住所
・電話番号

を別表から呼び出すことがあります。

✅ チェック

◻️ 別シートにカテゴリ別の予算表を作成できた
◻️ 集計表の予算欄に、カテゴリに対応した予算が自動表示された
◻️ 予算表の数字を変更すると、差額や判定も自動で変わった

🪄 小技

テーブルに変換してから範囲指定すると、データが増えても自動で反映✨

まとめ

ここまで進めたあなたは、スプレッドシートで「入力するだけ」から一歩進んで、集計して、見える化して、判断する土台ができています。

最初は少し難しく感じるかもしれませんが、表はこうして少しずつ育てていけば大丈夫です。

今回できるようになったことは、家計管理表だけでなく、

・売上管理
・在庫管理
・タスク管理
・予算管理

など、いろいろな表にもつながっていきます。

大切なのは、関数を丸暗記することではなく、

「何を知りたいから、この表を作るのか」 を意識することです。

集計したい、比べたい、注意したい。

その目的に合わせて表を整えていくと、スプレッドシートはぐっと使いやすくなります。

今日できるようになったこと（振り返り）

✅️ SUMIF関数でカテゴリ別の集計表を作れるようになった
✅️ 補助表をもとに円グラフを作り、支出の割合を見える化できた
✅️ 予算と実績を並べて、差額を引き算で計算できた
✅️ IF関数で「余裕あり」「注意」「予算オーバー」を表示できた
✅️ AVERAGE関数で平均を見る考え方に触れられた
✅️ COUNT関数で件数を見ることができた
✅️ VLOOKUP関数で別表から予算を自動取得する考え方を知れた

ここまでできれば、第4回としてかなり良い積み上げです。

次は、「別の表から自動で持ってくる」「条件に応じて処理を分ける」といった考え方を、少しずつ他の場面でも使ってみてください。

おわりに

✅️ この記事が少しでも役に立ったと感じたら、つぶやきでシェア、コメントやいいね、ブックマークをよろしくお願いします✨️次の記事を作る励みになります🐹✨️

最後まで読んでいただきありがとうございました♪

何かわからないことなどありましたらお気軽にコメントやDMください📥️✨️

🎬 第5回

まだ未定です🙇

関数について勉強したいやこういうシートを作りたいという要望がありましたら、コメントかDMでリクエストしてください🙌