[EXCEL] セルを行番号で範囲指定してはいけない（SUMIFS/COUNTIF/XLOOKUP関数等）
▶MAP▶PDF版▶表示▶関数▶ショトカ▶操作/コピー▶実務▶NG▶検索
関連記事：XLOOKUP関数、SUMIF・SUMIFS関数、COUNTIF・COUNTIFS関数、やってはいけない
【まとめ】
・SUMIFS/COUNTIFS/XLOOKUP関数等は行番号で範囲指定しない。
・列全体を範囲指定する。
・行番号で範囲指定すると、誤りを招く、かつ、面倒である。
・列全体を範囲指定すれば、簡単で誤りが減る。
＊テーブル化は非想定
＊場合によってはファイルが重くなるため、行番号で指定した方がいい場合もある。
【説明】
先日、SUMIFやCOUNTIF、VLOOKUP関数は使ってはいけないという記事を書きました。
[EXCEL]使ってはいけない関数３選 ①VLOOKUP、②SUMIF、③COUNTIF 初心者向けエクセル情報を鵜呑みにするなかれ
使うべき関数として SUMIFSやCOUNTIFS、XLOOKUP を挙げましたが、これら関数の記述について、面倒な方法を説明しているものを散見します。
具体的にいうと、【まとめ】のとおりですが、わざわざ「行番号で範囲指定している」ということ。
どいういうことかというと、例えば以下の表。
SUMIFS関数を使って、左の表の「項目」別の「数値」の計を、右の表に出しています。
G７セルの場合、=SUMIFS($C$3:$C$12,$B$3:$B$12,F7)
つまり、C３セルからC12セルの数値のうち、B３セルからB12セルが、F7（お）と同じものを足し上げる（SUM）、という意味です。
この計算式自体は問題ありません。
G３セルに入れた計算式を下のセルにコピーしています（スピルはあえて使っていません）。
でも、こういう記述、してはいけません。
なぜか？
理由は「まちがえやすいから」。しかも２パターンで。
そして、「面倒」だから。
間違えやすいパターン その１（行範囲の指定ミス）
合計を出すセルの範囲と、検索対象があるセルの範囲を間違ってしまうおそれがあります。
下の表をご覧ください。
G列の「計」がエラーになっています。
その理由、わかりますか？
G7セルの計算式は
'=SUMIFS($C$3:$C$12,$B$3:$B$11,F7)
です。
このどこがいけないのか？
パッと見てわかる人は、あんまりいないと思います（わかる人は、多分、そもそもこういう記述をしないでしょう）。
この計算式に誤りがある、とは思わず、データの方に誤りがあるのか、と考える人もいるかもしれません（そして迷宮に）。
誤りは、参照元のトレースで見ると、すぐにわかります。
わかりましたか？
そう、C列とB列では、選択している範囲（行範囲）が違うのです。
足し上げる範囲（C列）と、対象となる項目が記載されている範囲（B列）は同じでなくてはいけません。
それがずれていると計算できず、エラーになります。
このように、行番号で範囲選択すると範囲設定を誤るおそれが高くなります。
そもそも、データ数が多い表の場合、終わりの行まで指定するのは面倒です。
間違えやすいパターン その２（絶対参照忘れ）
下の表をご覧ください。
一見、正しそうです。
でも・・・計算式をよく見てみると・・・
合計する範囲、項目を検索する範囲がずれているのがわかります。
これは、G３セルに
=SUMIFS(C3:C12,B3:B12,F3)
という計算式を入れたときに、
合計する範囲である「C3:C12」と、項目を検索する範囲である「B3:B12」の行番号を絶対参照にしておくべきなのに、
相対参照のままにしてしまったためです。
これにより、下にコピーした際、合計する範囲と項目を検索する範囲が下にずれてしまったのです。
F４押下で行番号に「$」を付ける絶対参照にしておけば、セルをコピーしても、ずれることはありません（今回の場合、列は同じなので、列には「＄」が付かなくてもかまいません）。
なお、上の例では、計算式の範囲が、本来計算すべき範囲とは異なってしまっていますが、「たまたま」間違いにはなっていません。
これはあくまで「たまたま」です。
もし計算式の範囲外に該当の項目があれば、間違った結果となってしまいます。
こんなものを資料として出したら、アウトです。
誤りを防ぐ＆楽をするためには、列で指定する
上記２パターンのような誤り（ミス）を防ぐのは簡単です。
しかも、操作が楽になります。
行番号を指定しないで、列だけ指定すればいいのです。
どういうことか？
下の表をご覧ください。
上の表と同じですが、計算式はスッキリしています。
G7セルの計算式は =SUMIFS(C:C,B:B,F7) となっていて、計算式の中に行番号がありません（F7は除く）。
参照元のトレースで見てみると・・・
足し上げる範囲としてC列全体を、
検索する項目がある範囲としてB列全体を範囲指定していることがわかります。
これにより、
・行番号の範囲指定ミスがなくなる
・セルをコピーする際の行範囲ずれがおきない
ことになります。
しかも、列で指定するので、記述方法も楽です。
列の指定は、列のアルファベットのところをクリックするだけ。
とても簡単です。
列で指定すれば済むSUMIIF/SUMIFS関数やCOUNT/COUNTIFS関数、あるいはVLOOKUP/XLOOKUP関数などの説明で、行番号を指定している説明を見ると、いつものパターンですが、「実務やってんのかな？」と思ってしまいます。
「関数の基本」の説明としては正しいのですが、面倒かつ間違いを起こしやすい説明は避けた方がいいかと思います（特に初心者向け）。
紙面に制限のある書籍ならともかく、補足説明がいくらでもできるWEBなら、なおさらです。単にWEB本をコピーしているなら、そこまで考えが及ばないのかもしれませんが・・・。
注意 列指定だとファイルが重くなる？
なお、列全体を範囲指定する点での注意事項としては、
ファイルが重くなる（サイズが大きくなる）というものが挙げられます。
計算の範囲が広くなる分、パソコンに負荷がかかる、というわけです。
ただし、実務をやっていて、影響を感じたことはほとんどありません。
相当大きな（＝データ数が多い）ファイルなら別だと思いますが、数千件レベルなら、あまり影響ないと思います（パソコンの性能にもよる）。
ただし、「条件付き書式」を多用するなど、他の設定との掛け合わせにより、ファイルが重くなる場合もあるようです。
この場合は、行番号で設定することで、ファイルが軽くなるかもしれません。
テーブル化も有効化と思いますが、私は、ほぼテーブル化を使わないので、詳細は不明です。
おまけ（スピルの功罪）
私は通常、一番上のセルに計算式を入れて、それを下にコピーしています（CTRL＋D 又はマウスをセル右下に当てて「＋」が出たらダブルクリック。フラッシュフィルという。）。
一方、一番上のセルに計算式を入れるだけで、下のセルに自動で計算式が入る方法もあります。
下の図をご覧ください。
G3セルには、 =SUMIFS(C:C,B:B,F3:F7) と入っています。
これは、Ｃ列の数値の計を出すが、Ｂ列の項目がＦ３からＦ７のそれぞれに合致するものについて計算する、というものです。
数式の順番での説明なのでわかりづらいですが、
言い換えれば、B列の項目のうち、F3からF7の各セルと同じ項目について、C列の数値の計を出す、ということです。
このように記述すると、Ｇ３セルだけでなく、Ｇ３セルからＧ７セルまで、一気に数値が入ります。
ただし・・・
G７セルに着目してみると、参照元のトレースでは、特に問題なく見えます。
でも、数式バーを見ると・・・
何も入っていま・・・、いや、失礼しました。
うっすらと計算式が入っています。私なら見過ごす薄さです。
これは、「スピル」という機能によるものです。
一番上のセルに入れた計算式が、下のセルにも「こぼれる」（スピル）、つまり同じものが入っていく、というエクセルの新しい機能です。
便利ですが、引き継いだとき、このスピル機能を知らないと、下のセルには計算式がちゃんと入っていないと勘違いして、手計算＆手入力してしまうかもしれません。
そんなことはないと思いますが、少し焦らせてしまうかも。
それだけなら、大した問題ではないかもしれません。
問題は・・・
右の表の項目を追加したとき。
例えば、下の例。「か」を追加すると・・・
G8セルに計算式を入れようと思うのですが、CTRL＋D（上のセルをコピーのショートカット）では・・・
何も変わりません。上の計算式が入らないのです。
上のG7セルは、G3セルから計算式がこぼれてきている（スピルしている）ので、G7セル自体には計算式が入っていないので、CTRL＋Dではコピーされないのです。
では、式が入っているG３セルから範囲指定して、CTRL＋Dとしてみると・・・
「＃スピル」という表示しかでません。エラー表示です。
これを直すには、G3のセルを
=SUMIFS(C:C,B:B,F3:F7) から
=SUMIFS(C:C,B:B,F3:F8) に直す必要があります。
（ただし、上のように「＃スピル」と出てから直しても反応しません。CTRL＋Zでやり直ししてからの修正が必要です）。
これにより、G8セルも左の表が「か」の項目の数値の計が出ました。
これでｇ８セルにも計が出ました。
「こぼれる」（スピルする）範囲が広がった、ということです。
でも、計算式の中のセルを直接いじるのは面倒です。間違う恐れもあります。
だったら、最初からスピルににしないで、単に一番上のセルに、スピルでない式を入れて、下にコピーした方が、後々、楽かな、と思います。
これは、私が「スピル慣れ」していないからかもしれませんが、おそらく同じく「スピルなれ」していないだろう後任も、その方が理解しやすく、かつ、メンテしやすいでしょう。
というわけで、スピルの部分はちょっとおまけでしたけれど、関数によっては、行番号を指定しないで列ごと範囲指定することで、楽かつミスのない集計を行うことができます。
＊SUM関数も範囲指定可能。ただし、「計」欄があると、それまで計算してしまうので注意。
その点、SUMIFS関数は、足し上げる条件を設定できるので便利。
以上、参考になれば幸いです。
もし、ご覧になって、何か発見があったなら、スキまたはフォローしていただけると嬉しいです。
2025/4/22追記
列選択は便利ですが、セル結合がされているとうまくできませんので、要注意です。
詳しくは下の記事をご覧ください。
[ダメEXCEL]セル結合してはいけないもう一つの理由（SUMIFS/COUNTIFS/XLOOKUP関数等における列指定の際のトラップ）
（作業 １日 ２ｈ）
