ExcelでSUMIF関数をコピーすると、合計範囲や条件の参照先までずれてしまい、意図しない集計結果になることがあります。
このようなときは、$を使った絶対参照または複合参照を設定すると、集計表を正しくオートフィルできます。
特に売上表、商品別集計、担当者別集計、月別の請求額管理では、範囲を固定しながら条件だけを動かす考え方が重要です。
SUMIF関数で$を使う主な目的は、検索する範囲と合計する範囲をコピー時に固定し、条件セルだけを必要に応じて移動させることです。
この記事では、SUMIF関数の基本構文から、$を入れる位置、横方向と縦方向にコピーする数式、エラーを避ける確認方法まで順番に解説します。
SUMIF関数で範囲を固定する絶対参照
それではまず、SUMIF関数で範囲を絶対参照にして合計する基本の方法について解説していきます。
| A列 商品名 | B列 区分 | C列 売上 | E列 集計区分 | F列 合計 |
|---|---|---|---|---|
| りんご | 果物 | 1200 | 果物 | 3700 |
| にんじん | 野菜 | 800 | 野菜 | 2000 |
| みかん | 果物 | 2500 |
基本数式と$を付ける場所
SUMIF関数は、指定した条件に一致するデータだけを合計する関数です。
=SUMIF($B$2:$B$10,E2,$C$2:$C$10)
この数式では、B2からB10が条件を調べる範囲、E2が探したい区分、C2からC10が合計対象の範囲です。
$B$2:$B$10と$C$2:$C$10は、行と列の両方を固定する絶対参照です。
F2の数式をF3へコピーしても、検索範囲はB2からB10のまま、売上範囲はC2からC10のまま維持されます。
一方でE2には$を付けないため、F3へコピーすると条件は自然にE3へ変化します。
【操作のポイント】集計元の表は固定し、集計表に入力した条件セルだけを移動させる設計にします。
F4キーで絶対参照へ切り替える操作
数式を入力中に参照範囲をクリックし、キーボードのF4キーを押すと参照形式を切り替えられます。
Windows版Excelでは、B2を選択した状態でF4キーを1回押すと$B$2になり、もう一度押すとB$2、さらに押すと$B2、最後にB2へ戻ります。
範囲全体を固定したい場合は、開始セルと終了セルの両方を絶対参照にすることが大切です。
数式バーで直接$を入力しても構いませんが、F4キーを使うと入力ミスを減らせます。
ノートパソコンではFnキーとF4キーを同時に押す必要がある機種もあります。
【操作のポイント】F4キーを押したあと、$が開始セルと終了セルの両方に付いているか数式バーで確認します。
コピー後の数式を確認する方法
F2の数式を下方向へコピーしたあと、F3以降のセルをクリックして数式バーを確認しましょう。
正しい状態では、$B$2:$B$10と$C$2:$C$10は変わらず、中央の条件だけがE3、E4のように変化します。
合計が同じ値ばかりになる場合は、条件セルまで固定していないかを確認する場面です。
正しい例は =SUMIF($B$2:$B$10,E3,$C$2:$C$10) です。
条件も固定する =SUMIF($B$2:$B$10,$E$2,$C$2:$C$10) は、すべて同じ区分を集計したい場合だけに使います。
【操作のポイント】コピー後のセルを一つ選び、動くべき参照と動かない参照を分けて確認します。
条件セルだけを動かす複合参照
続いては、SUMIF関数を横方向や縦方向へコピーする際の複合参照を確認していきます。
| A列 日付 | B列 担当者 | C列 売上 | E列 田中 | F列 佐藤 |
|---|---|---|---|---|
| 4月1日 | 田中 | 15000 | 15000 | 23000 |
| 4月2日 | 佐藤 | 23000 |
横方向へコピーする場合の列固定
担当者名をE1、F1、G1のように横並びで配置し、その下に集計結果を表示する場合を考えます。
=SUMIF($B$2:$B$20,E$1,$C$2:$C$20)
このE$1は、行番号1だけを固定する複合参照です。
右方向へコピーするとE列はF列、G列へ変わりますが、担当者名を置いた1行目は固定されます。
横へコピーする集計表では、見出し行を参照するセルの行番号に$を付ける考え方が便利です。
月別の列見出し、部署別の列見出し、担当者別の列見出しにも同じ構造を使えます。
【操作のポイント】右へオートフィルする場合は、列記号を動かし、行番号を固定するE$1を基本形にします。
縦方向へコピーする場合の行固定
商品名や区分をA列に並べ、各行の集計結果をB列へ表示する場合は、条件セルの列を固定することがあります。
=SUMIF($E$2:$E$20,$A2,$F$2:$F$20)
$A2は列Aを固定し、行番号だけをコピー先に合わせて変化させる複合参照です。
右方向へ数式を展開しても条件は常にA列を参照するため、行見出しに沿った集計を維持できます。
縦の見出しを基準に複数の計算列を作るときは、$A2が有効です。
ただし、結果を単純に下へコピーするだけならA2でも問題ないことがあります。
【操作のポイント】縦見出しを常にA列から参照したいときだけ、列記号の前に$を付けます。
絶対参照と複合参照の使い分け
参照先を一切動かしたくないなら$A$1、行だけ固定するならA$1、列だけ固定するなら$A1、自由に動かすならA1を使います。
$A$1は行と列を固定する参照です。
A$1は横コピーに向く参照です。
$A1は縦見出しを使う表や横コピーに向く参照です。
数式をどの方向にコピーするかを先に決めると、$を付ける場所を判断しやすくなります。
実務では、元データの検索範囲と合計範囲は絶対参照、集計見出しは複合参照または相対参照にする構成が多くなります。
【操作のポイント】数式の完成後ではなく、コピーする方向を考えてから参照形式を設定します。
SUMIF数式をオートフィルする操作画面
続いては、絶対参照を含むSUMIF数式を入力し、オートフィルで展開する具体的な操作を解説していきます。
| 商品 | 分類 | 金額 | 分類 | 合計 |
|---|---|---|---|---|
| りんご | 果物 | 1200 | 果物 | 3700 |
| みかん | 果物 | 2500 | 野菜 | 2000 |
先頭セルへの数式入力
集計結果を表示する先頭セルF2を選択し、SUMIF関数を入力します。
=SUMIF($B$2:$B$10,E2,$C$2:$C$10)
入力後にEnterキーを押すと、E2にある果物を条件として売上が合計されます。
数式バーには$付きの範囲が表示されるため、入力直後に固定状態を確認できます。
表のデータ件数が増える可能性がある場合は、範囲を必要な最大行数まで確保するか、テーブル機能の利用も検討しましょう。
【操作のポイント】先頭セルで結果が正しいことを確認してから、数式をコピーします。
フィルハンドルによる下方向コピー
F2を選択すると、セル右下に小さな四角形のフィルハンドルが表示されます。
この部分をF3以降までドラッグすると、数式が連続してコピーされます。
オートフィル後は条件参照がE2からE3へ変わり、固定範囲は変化しない状態が正常です。
隣接列にデータが並んでいる場合は、フィルハンドルをダブルクリックして自動入力することもできます。
【操作のポイント】コピー先に不要な値があるとダブルクリックの範囲が想定と異なるため、ドラッグ操作で確認します。
数式表示による参照先の点検
計算結果だけでは間違いに気付きにくい場合、数式タブの数式の表示を使うとセル内の数式を一覧で確認できます。
ショートカットキーではCtrlキーとShiftキーと@キーの組み合わせでも表示を切り替えられます。
表示された数式で、$B$2:$B$10と$C$2:$C$10がすべての行で同じなら、範囲固定は成功しています。
数式を見比べる作業は、集計表の検算や引き継ぎ前の確認にも役立ちます。
【操作のポイント】値ではなく数式を表示し、固定範囲と条件セルの変化を目視で点検します。
SUMIF関数の範囲指定と条件指定
続いては、SUMIF関数で固定すべき範囲と条件の役割を確認していきます。
| 引数 | 役割 | 参照例 |
|---|---|---|
| 範囲 | 条件を確認する列 | $B$2:$B$10 |
| 検索条件 | 一致させる文字や数値 | E2 |
| 合計範囲 | 足し合わせる数値列 | $C$2:$C$10 |
範囲と合計範囲の行数
SUMIF関数では、範囲と合計範囲を同じ大きさにそろえると、意図が明確で管理しやすくなります。
たとえばB2からB10を条件範囲にするなら、C2からC10を合計範囲に指定します。
開始行や終了行がずれると、見た目は計算できても別の行の金額を集計する原因になります。
ヘッダーが1行目にある表では、データ範囲を2行目から始めることも重要です。
【操作のポイント】条件範囲と合計範囲は、同じデータ行に対応しているか確認します。
文字列条件と数値条件の扱い
条件には果物のような文字列だけでなく、1000以上のような数値条件も指定できます。
=SUMIF($C$2:$C$10,”>=1000″,$C$2:$C$10)
この場合、1000以上の売上だけを合計します。
セル参照と比較演算子を組み合わせる場合は、=”>=”&E2のように指定します。
比較記号を含む条件は、文字列として扱う部分とセル参照を連結する部分を区別します。
【操作のポイント】数値条件を入力するときは、演算子を二重引用符で囲む書式を確認します。
空白と余分なスペースの影響
条件セルと元データの文字が同じに見えても、末尾のスペースや全角半角の違いにより一致しないことがあります。
結果が0になる場合は、条件の文字列、入力規則、元データの空白を確認しましょう。
SUMIF関数の不一致は、$の設定ではなく文字データの揺れが原因である場合も少なくありません。
必要に応じてTRIM関数で余分な空白を取り除く、表記ルールを統一するといった対策が有効です。
【操作のポイント】数式を直す前に、条件セルと元データをコピーして比較し、文字の差を確認します。
SUMIF関数で$を使う際のエラー対策
続いては、SUMIF関数の絶対参照で起こりやすいミスと対処法を解説していきます。
| 症状 | 主な原因 | 確認箇所 |
|---|---|---|
| コピー後に値が0 | 範囲がずれた | $の有無 |
| すべて同じ値 | 条件を固定した | $E$2の設定 |
| 計算が合わない | 範囲の対応ずれ | 開始行と終了行 |
範囲を相対参照のままコピーした場合
=SUMIF(B2:B10,E2,C2:C10)を下へコピーすると、次の行ではB3:B11とC3:C11に変わります。
これでは最初のデータが集計対象から外れ、末尾の余分な行が含まれるため、結果が正しくなりません。
集計元の一覧表を繰り返し参照する式では、まず範囲を絶対参照にする習慣が有効です。
【操作のポイント】コピー前に、検索範囲と合計範囲へ$を設定してから結果を確認します。
条件セルまで固定した場合
=$SUMIF($B$2:$B$10,$E$2,$C$2:$C$10)のように条件セルまで固定すると、下へコピーしてもすべてE2の条件で計算されます。
同じ合計値が連続する場合は、条件参照に不要な$が入っていないかを見直しましょう。
ただし、同じ条件を複数の計算式で共有したい場合には、条件セルの絶対参照が適しています。
【操作のポイント】条件を変えながら集計するのか、同一条件を共有するのかを先に決めます。
SUMIFS関数へ応用する考え方
複数条件で集計するSUMIFS関数でも、絶対参照の考え方はSUMIF関数と同じです。
=SUMIFS($D$2:$D$100,$B$2:$B$100,$G2,$C$2:$C$100,H$1)
この例では、合計範囲と二つの条件範囲を固定し、行見出しのG2と列見出しのH1を複合参照にしています。
集計表が縦横に広がるほど、絶対参照と複合参照の設計が結果の正確さを左右します。
【操作のポイント】SUMIFS関数では、各条件範囲と条件セルの組み合わせを一組ずつ確認します。
まとめ SUMIF関数で絶対参照を使う方法
SUMIF関数でコピー後も同じ元データを集計したい場合は、条件範囲と合計範囲を$B$2:$B$10のような絶対参照に設定します。
縦方向へコピーするだけなら条件セルはE2のような相対参照にし、横方向へコピーする場合はE$1、縦見出しを固定したい場合は$A2の複合参照を使い分けましょう。
数式を入力したあとにオートフィルを実行し、コピー先で固定範囲が変わっていないか数式バーで確認することが大切です。
SUMIF関数の結果が0になる、同じ値が並ぶ、集計値が合わないといった場合も、$の位置、範囲の行数、条件データの表記を順に確認すると原因を見つけやすくなります。
絶対参照を使いこなせば、商品別、担当者別、月別などの集計表を短時間で正確に作成できるでしょう。
