COUNTIFの数式を下へコピーしたら、
2行目から件数が合わなくなった。
そんなときは、数える範囲まで
一緒に動いていないか確かめてみましょう。
この記事では、Microsoftの公式説明をもとに、
範囲を「$」で固定する方法を紹介します。
最初の数式とコピー後の数式を比べれば、
どこを直すか判断しやすくなります。
まずF2とF3の数式を比べる
COUNTIFは、指定した範囲から
条件に合うセルの数を数える関数です。
基本の形は「COUNTIF(範囲,検索条件)」。
検索条件にはセルも指定できます。
Microsoft公式のCOUNTIFの説明で確認できます。
ここからは説明用の架空の作業一覧です。
C2〜C11に、作業の状態が入っているとします。
| セル | 入れる内容 |
|---|---|
| C1 | 状態 |
| C2〜C11 | 未着手・作業中・完了のいずれか |
| E1 | 集計する状態 |
| E2 | 未着手 |
| E3 | 作業中 |
| E4 | 完了 |
| F1 | 件数 |
F2に次の数式を入力したとします。
=COUNTIF(C2:C11,E2)これを1行下のF3へコピーすると、
数式は次の形になります。
=COUNTIF(C3:C12,E3)条件がE2からE3へ動くのは、
「未着手」から「作業中」へ変えたいので適切です。
一方、数える範囲もC3〜C12へ動いています。
C2が外れ、C12が入るため、
同じ10件を集計する目的には合いません。
表示された件数がたまたま合っていても、
範囲が正しいとは限りません。
F2、F3を順に選び、画面上部の数式バーで
カッコ内の最初の範囲を見比べましょう。
数える範囲だけを$で固定する
F2の数式を、次の形へ直します。
記号は半角で入力してください。
=COUNTIF($C$2:$C$11,E2)「$C$2:$C$11」が、数える範囲です。
両端のセルについて、列のCと行番号の前に
それぞれ「$」を付けています。
数式をコピーしたときに参照先が動く方式を
「相対参照」、固定する方式を
「絶対参照」と呼びます。
今回は、範囲を絶対参照にしています。
Microsoft公式の参照の説明も参考になります。
ショートカットを覚えなくても、
数式バーで「$」を手入力すれば直せます。
末尾のE2には付けず、そのままにします。
修正後はEnterで確定し、
F2のセルをコピーしてF3、F4へ貼り付けます。
元の表で試す前に、練習用のコピーで
確認すると落ち着いて見比べられます。
コピー後は「固定」と「変化」を確認する
F3とF4の数式は、次の形になる想定です。
F3：作業中を数える
=COUNTIF($C$2:$C$11,E3)F4：完了を数える
=COUNTIF($C$2:$C$11,E4)確認するのは、次の2点です。
- 範囲は、どちらも「$C$2:$C$11」のまま
- 条件は、E3、E4と行に合わせて変わる
もし条件も「$E$2」と固定すると、
下へコピーしても「未着手」を数え続けます。
全部に「$」を付けるのではなく、
変えたくない場所を決めるのがポイントです。
最後に、状態を1種類だけ選び、
C2〜C11を目で数えた結果と照合します。
合わなければ、範囲に加えて
状態名の表記や余分な空白も確認しましょう。
COUNTIF自体の入力から確かめたい方は、
状態別の件数を数える基本手順も参照できます。
$を付けても追加した行は自動では含まれない
今回の数式が数えるのはC2〜C11です。
C12以下へ新しい作業を追記しただけでは、
その作業は集計範囲に入りません。
たとえばC15まで数えたい場合は、
F2の範囲を「$C$2:$C$15」へ変更し、
F3、F4にも数式をコピーし直します。
「コピーしても範囲を動かさないこと」と
「新しいデータを範囲へ加えること」は、
別々に確認する必要があります。
データを増やした日は、最終行まで
範囲に入っているかも見直しましょう。
まとめ：件数より先に数式の範囲を見る
COUNTIFをコピーして件数がずれたら、
まず最初のセルと、その下のセルを比べます。
同じ一覧を数えるなら範囲を「$」で固定し、
集計する状態のセルは行に合わせて変えます。
まずはF2とF3を選び、
数える範囲が同じか確認してみてください。
