金額を手で合計するのをやめる

01練習の準備

題材は架空の補助金交付台帳18件です。集計のために台帳を並べ替えたり加工したりせず、別シートに合計表を作ります。途中の金額訂正も、手元の練習ブックで行います。

練習ブックをダウンロード XLSX

• 練習ブックをMicrosoft Excelで開き、「使い方」を読みます。作業用の名前で手元に保存し、配布元は上書きしません。
• 「補助金台帳」を開きます。1行目が見出し、2〜19行目が18件の記録です。A列は交付番号、B列は地区、C列は事業、D列は交付額。金額の単位は円です。
• 「地区別集計」を開きます。A3:A5に北部・南部・中央が用意され、金額と検算のセルは空欄です。

完成ブック・スライド・困ったときカードには答えが載っています。自力課題の答え合わせまで開かず、まず練習ブックと本文で進めます。研修では自力課題は22〜26分、完成見本は26分の答え合わせで開く設計です。

02北部の交付額をSUMIFSで合計する

COUNTIFは条件に合うセルを数えます。SUMIFSは、条件に合う行の金額を足します。式を入れる前に、合計する金額の範囲・条件を探す列・条件のセルを指してみましょう。

図を大きく見る画像を別画面で開く

合計するのは台帳のD2:D19、条件を探すのは地区のB2:B19、条件は「地区別集計」のA3「北部」です。二つの範囲の先頭行と最終行をそろえます。

1. B3へSUMIFSの式を入力する

• 「地区別集計」のB3を選び、半角で =SUMIFS( と入力します。
• 「補助金台帳」のD2:D19を選び、参照を入力している間にF4を1回押して $D$2:$D$19 にします。半角のカンマ , を入力します。
• 同じ台帳のB2:B19を選び、F4を1回押して $B$2:$B$19 にします。カンマを入力します。
• 「地区別集計」へ戻ってA3をクリックします。台帳のA3ではありません。半角の ) を入力してEnterで確定します。

2. 金額と数式バーを確かめる

• B3を選び直します。結果が680,000円で、数式バーが次の式になっていることを確認します。

=SUMIFS(補助金台帳!$D$2:$D$19,補助金台帳!$B$2:$B$19,地区別集計!A3)

図を大きく見る画像を別画面で開く

北部7行の交付額が合計されています。SUMIFSは合計する金額の範囲を最初に置き、その後に条件範囲と条件を並べます。

03下へコピーし、式で確かめる

まだ操作せず、B3の式をB4へコピーすると、条件のA3はどのセルを指すか予測してください。

1. 下へコピーし、地区別の金額を確かめる

• 予測したらB3を選び、右下の小さな四角（フィルハンドル）をB5まで下へドラッグします。
• B3:B5が北部680,000円・南部800,000円・中央550,000円になったか確認します。

2. コピー先の式と参照を確かめる

• B4を選び直し、数式バーで二つの範囲と条件セルを見ます。

=SUMIFS(補助金台帳!$D$2:$D$19,補助金台帳!$B$2:$B$19,地区別集計!A4)

図を大きく見る画像を別画面で開く

$D$2:$D$19 と $B$2:$B$19 は固定され、条件だけがA3→A4→A5へ動きます。A4は南部です。シート名なしのA4でも同じセルを指していれば正解です。

この台帳では、範囲がずれても金額が正しく見えることがあります。結果の額だけでなく、コピー先の式も確認しましょう。

04総額を照合し、台帳で確かめる

1. 表の総計・元台帳総額・差額を出す

「地区別集計」で、次の順に式を入力し、Enterで確定します。

• B6に =SUM(B3:B5)。地区別の合計表の総計を求めます。
• D6に =SUM(補助金台帳!$D$2:$D$19)。元台帳の金額を別に合計します。
• F6に =B6-D6。二つの総額の差を求めます。

図を大きく見る画像を別画面で開く

2. 差額が0でも、最終行と入力内容を見る

一致は指定範囲内の区分漏れや式のずれを点検する材料です。ただし、両方の式が同じように短い範囲を使っていたら、集計漏れがあっても一致します。範囲外の追加行や文字として入った金額、元記録の入力誤りも、差額0円だけでは分かりません。

• 「補助金台帳」に戻り、最終行が19行目で、20行目以降に記録がないことを確認します。
• D2を選び、数式バーの中身が 120000 で、「円」などの文字が付いていないことを見ます。この時点では金額を変更しません。

図を大きく見る画像を別画面で開く

セルに「120,000円」と表示されることと、「120000円」という文字を入力することは違います。数字だけの表示や右寄せだけでも数値とは断定できません。気になる行は元記録と照合します。

05金額を訂正し、自動反映と復元を確かめる

1. 金額を訂正し、集計への反映を確かめる

手元の練習ブックの「補助金台帳」で、G001の金額を訂正します。台帳シートは複製しません。まず、G001の地区を見て、どの地区の合計が変わるか予測してください。

• 「補助金台帳」のA2がG001であることを確認し、D2を選びます。
• 150000 と数字だけ入力し、Enterを押します。「円」は入力しません。集計式は編集しません。
• 「地区別集計」に戻り、北部が710,000円、南部800,000円・中央550,000円は変わらないことを確認します。
• B6とD6がともに2,060,000円へ変わり、F6は0円のままであることを確認します。

図を大きく見る画像を別画面で開く

G001を30,000円増やすと、北部の合計と二つの総額が30,000円増えます。既存行の訂正が反映される体験です。固定範囲の外へ追加した行まで自動で集計される説明にはなりません。

2. 元の金額へ戻し、自力課題の準備をする

• 「補助金台帳」のD2へ 120000 と数字だけ入力してEnterを押します。
• 「地区別集計」で、北部680,000円、B6とD62,030,000円、F60円へ戻ったことを確認します。南部・中央も初期の額のままです。

図を大きく見る画像を別画面で開く

復元してから次へ進みます。動画のG002例の金額や結果を、今回のG001の確認値に使わないでください。

06同じ台帳を、事業別に集計してみる

G001と地区別の結果が初期状態へ戻ったことを確認します。ここからは地区別の手順・図を見ずに、同じ台帳の集計軸を事業へ変えます。先に答え・完成ブック・カードを開かずに取り組みましょう。

図を大きく見る画像を別画面で開く

自力課題の答えを見る

条件を探すのはC列（事業）です。合計する金額はD列のまま。「事業別集計」のB3は次の式になります。

=SUMIFS(補助金台帳!$D$2:$D$19,補助金台帳!$C$2:$C$19,事業別集計!A3)

B4では条件が 事業別集計!A4 に変わります。シート名なしのA3・A4も、同じ集計シートの条件セルを指していれば正解です。

自力課題の答え（円）

確認するセル	金額
B3 防災	1,200,000
B4 地域活動	830,000
B5 表の総計	2,030,000
D5 元台帳総額	2,030,000
F5 差額	0

B5は =SUM(B3:B4)、D5は =SUM(補助金台帳!$D$2:$D$19)、F5は =B5-D5 です。

図を大きく見る画像を別画面で開く

図を大きく見る画像を別画面で開く

同じ台帳18行を全区分で集計しているため、地区別でも事業別でも総計は同じです。台帳D2は120000、最終行は19行目のまま。元台帳を演習前の状態へ戻して終えます。

完成ブックで確認する XLSX

余裕があれば、条件を二つにする

主課題と検算を終えた人向けの追加問題です。北部かつ防災の合計は =SUMIFS(補助金台帳!$D$2:$D$19,補助金台帳!$B$2:$B$19,"北部",補助金台帳!$C$2:$C$19,"防災") で350,000円。地区が「西部」の行はないため、その合計は0円です。