Arte Sheets学ぶ・試す・理解する

条件付き集計

SUMIF関数でカテゴリ別予算を集計する(マイナス返金を含む)

SUMIF関数を使って特定の費用カテゴリを集計し、マイナスの返金行を正しく反映させながら総合計と照合します。

購入と返品を1つのルールで一貫して記録する

地域の共同菜園で、Seeds 24、Tools 60、Seeds 18、Tools -12、Compost 35 という5件の入出金記録をつけているとします。Tools のマイナス金額は返品による返金であり、工具にかかった実質支出を減らす意味を持ちます。A2:A6 にカテゴリ、B2:B6 に符号付きの金額を入力します。

集計したいカテゴリとして D2 に Tools と入力します。金額はすべて同一通貨で記録されており、この演習では通貨換算や消費税計算は扱いません。

行カテゴリ金額
2Seeds(種)24
3Tools(工具)60
4Seeds(種)18
5Tools(工具)-12
6Compost(堆肥)35

カテゴリで行を絞り込み、対応する金額を合計する

SUMIF 関数はまず A2:A6 の各セルを D2 のカテゴリと照合し、一致した行に対応する B2:B6 の数値を合計します。Tools の場合、対象となる金額は 60 と -12 なので、結果は 48 になります。

返品のマイナス行は範囲から除外すべき例外データではありません。マイナスの符号によって支出の減少を正しく表現しています。もし正の金額だけを抽出してしまうと、「返品差し引き前の総購入額」という別の問いに答えることになってしまいます。

=SUMIF(A2:A6,D2,B2:B6)

数式を書き換えずに集計対象を切り替える

D2 の Tools を Seeds に書き換えると、24 と 18 が合計されて 42 になります。Compost に変えれば 35 です。条件を別セルに切り出しておけば、数式内の文字列を毎回編集するよりもはるかに確認しやすくなります。

数式内に直接 "Tools" と記述することも可能ですが、セル参照のつもりで "D2" のように二重引用符を付けないでください。"D2" と書くと、「D2 という名前のカテゴリ」を探してしまいます。

合計が「0」だからといって該当データなしとは限らない

記録に存在しない Water を D2 に入力すると、SUMIF は 0 を返します。次に D2 を Tools に戻し、B5 の返金額を -60 に変更してみてください。2件の行が一致しているにもかかわらず、合計は同じく 0 になります。

つまり合計の 0 には、「一致する行が0件」「金額がすべて0」「プラスの支出とマイナスの返金が相殺された」という複数の可能性があります。合計 0 を機械的に「購入実績なし」と表示する前に、元の明細行や該当件数を必ず確認してください。

集計表をコピーするときは元データの範囲を固定する

D2、D3、D4 に Tools、Seeds、Compost を並べて一覧集計するとします。その横へ数式をコピーする前に、下の数式のように元データの範囲を絶対参照で固定しておきましょう。条件セルの D2 は相対参照のままにしておくことで、次の行では自動的に D3 が参照されます。

また、条件範囲と合計範囲の行位置は必ず揃えてください。A5 のカテゴリに対応する金額は B5 であり、B4 ではありません。明細行を追加したときは、両方の範囲を同じ行数だけ広げます(なお、演習パネルは数式の評価を行いますが、フィルハンドルのドラッグ操作までは再現しません)。

=SUMIF($A$2:$A$6,D2,$B$2:$B$6)

条件が増えたらSUMIFSへステップアップする

カテゴリ別の合計だけなら条件は1つで足りますが、「特定の月に購入した Tools の合計」のように日付条件も加わる場合は、日付列を追加して SUMIFS 関数を使うのが適切です。

その際、引数の順番が変わる点に注意してください。SUMIF では合計範囲を3番目に指定しますが、SUMIFS では最初に指定します。関数名に S を足しただけで引数の順番をそのままにしないよう気をつけましょう。複数条件の集計は次のレッスンで詳しく扱います。

符号やカテゴリを意図的に変えて検算する

元のデータにリセットして Tools を選んだ状態で、B5 を -12 から 12 に変更してみましょう。結果は 48 から 72 に増えます。数式自体は正常に動いており、符号を変えたことで「返品」が「追加購入」という意味に変わったためです。

続いて B5 を -12 に戻し、A5 のカテゴリを Seeds に変更すると、返品分が別カテゴリに移るため Tools の合計は 60 になります。この2つのテストで、カテゴリの一致判定と符号付き計算の両方が正しく機能していることを確認できます。

サンプルを試す

初期値のまま計算するか、値を変更して動作を確認できます。

参考リンク