Home »
エクセル関数の技 »
COUNTIF関数で飛び飛びのセル範囲を指定したい?

COUNTIF関数で飛び飛びのセル範囲を指定したい?:Excel関数の技

条件付きでカウントしたいときにはCOUNTIF関数を使うことが多いと思います。ところが対象のセルが飛び飛びであるとちょっと面倒・・・というかCOUNTIFではセル範囲に飛び飛びのセル範囲を指定することができません。
ここではSUMPRODUCT関数を利用して計算をする例を示します。

スポンサードリンク

(追記) (追記ここまで) (追記) (追記ここまで)

更新:2024年5月4日;作成:2016年4月20日

飛び飛びのセル範囲で計算する数式を検証する

数式を手軽に検証する

飛び飛びのセル範囲で計算をしたい topへ

ここで使用するデータを下表に示します。
コピーしてお使いください。
A,C,E列(奇数列)とB,D,F列(偶数列)に分けて計算を行います。
ここでの課題は○しろまるは2点、△しろさんかくは1点と換算して合計を求めるという計算式を作成することです
なお、ここで使っている○しろまるは記号の○しろまるで漢数字の〇ではなありませんのでご注意ください。

○しろまる ○しろまる △しろさんかく ×ばつ ×ばつ △しろさんかく

○しろまる ○しろまる ○しろまる △しろさんかく △しろさんかく ○しろまる

○しろまる ○しろまる ○しろまる ×ばつ ×ばつ △しろさんかく

△しろさんかく △しろさんかく △しろさんかく △しろさんかく △しろさんかく ×ばつ

△しろさんかく △しろさんかく △しろさんかく ○しろまる ×ばつ △しろさんかく

△しろさんかく △しろさんかく ○しろまる △しろさんかく ○しろまる ×ばつ

△しろさんかく △しろさんかく ○しろまる ×ばつ ×ばつ △しろさんかく

×ばつ ○しろまる △しろさんかく ×ばつ ×ばつ △しろさんかく

×ばつ △しろさんかく ○しろまる △しろさんかく △しろさんかく ×ばつ

×ばつ △しろさんかく ×ばつ ×ばつ ×ばつ △しろさんかく

×ばつ ×ばつ △しろさんかく △しろさんかく △しろさんかく ○しろまる

×ばつ ×ばつ △しろさんかく ○しろまる ×ばつ △しろさんかく

×ばつ ×ばつ ○しろまる △しろさんかく ○しろまる ×ばつ

COUNTIF関数で計算する

COUNTIF関数でとびとびのセル範囲を計算します。
とてもセンスがあるとは言えないものになってしまいます。
奇数列の各セルが○しろまるなら*2、△しろさんかくなら1と計算します。
=COUNTIF(A1,"○しろまる")*2+COUNTIF(A1,"△しろさんかく")
+COUNTIF(C1,"○しろまる")*2+COUNTIF(C1,"△しろさんかく")
+COUNTIF(E1,"○しろまる")*2+COUNTIF(E1,"△しろさんかく")
という数式が考えられます。
偶数列なら
=COUNTIF(B1,"○しろまる")*2+COUNTIF(B1,"△しろさんかく")
+COUNTIF(D1,"○しろまる")*2+COUNTIF(D1,"△しろさんかく")
+COUNTIF(F1,"○しろまる")*2+COUNTIF(F1,"△しろさんかく")
となります。
奇数列の合計だけを求めるなら、
=COUNTIF(A1:A13,"○しろまる")*2+COUNTIF(A1:A13,"△しろさんかく")
+COUNTIF(C1:C13,"○しろまる")*2+COUNTIF(C1:C13,"△しろさんかく")
+COUNTIF(E1:E13,"○しろまる")*2+COUNTIF(E1:E13,"△しろさんかく")
となります。
偶数列の合計は
=COUNTIF(B1:B13,"○しろまる")*2+COUNTIF(B1:B13,"△しろさんかく")
+COUNTIF(D1:D13,"○しろまる")*2+COUNTIF(D1:D13,"△しろさんかく")
+COUNTIF(F1:F13,"○しろまる")*2+COUNTIF(F1:F13,"△しろさんかく")
となります。

SUMPRODUCT関数で計算する

SUMPRODUCT関数を使うとスマートな感じの数式となります。
=(奇数列であるか)*(○しろまる*2)*(△しろさんかく*1) といった感じの数式を作成します。
- G1セルの数式
  =SUMPRODUCT((MOD(COLUMN(A1:F1),2)=1)
  *((A1:F1="○しろまる")*2+(A1:F1="△しろさんかく")))
- H1セルの数式
  =SUMPRODUCT((MOD(COLUMN(A1:F1),2)=0)
  *((A1:F1="○しろまる")*2+(A1:F1="△しろさんかく")))
- G14セルの数式
  =SUMPRODUCT((MOD(COLUMN(A1:F13),2)=1)
  *((A1:F13="○しろまる")*2+(A1:F13="△しろさんかく")))
- H14セルの数式
  =SUMPRODUCT((MOD(COLUMN(A1:F13),2)=0)
  *((A1:F13="○しろまる")*2+(A1:F13="△しろさんかく")))
1行目で求めたい結果は A,C,E列はそれぞれ○しろまる△しろさんかく×ばつなので 2+1+0=3 となり、B,D,F列は○しろまる×ばつ△しろさんかくなので 2+0+1=3 となります。
2行目のA,C,E列はそれぞれ○しろまる○しろまる△しろさんかくなので 2+2+1=5 となり、B,D,F列は○しろまる△しろさんかく○しろまるなので 2+1+2=5 となります。
5行目のA,C,E列はそれぞれ△しろさんかく△しろさんかく×ばつなので 1+1+0=2 となり、B,D,F列は△しろさんかく○しろまる△しろさんかくなので 1+2+1=4 となります。
A,C,E列の計算結果はG列に、B,D,F列の計算結果はH列に表示しています。
奇数の列、偶数の列を判定する
- 奇数の列を判定するには列番号が2で割って余りが1の時と考えることができます。
  数式は MOD(COLUMN(A1:F1),2)=1 となります。
セルの値が "○しろまる" であるか否か?
- (A1:F1)="○しろまる" と判定します。
  ○しろまるは2点なので *2 とすれば換算ができます。
  数式は ((A1:F1)="○しろまる")*2 となります。
セルの値が "△しろさんかく" であるか否か?
- (A1:F1)="△しろさんかく" と判定します。
  △しろさんかくは1点なので *1 としたいところですが、+1と演算をする必要はないので省略ができます。
  (Excelが内部で TRUE=1、FALSE=0 と処理するため)
  数式は (A1:F1)="△しろさんかく" となります。
これらをSUMPRODUCT関数で処理をします。
- =SUMPRODUCT((MOD(COLUMN(A1:F1),2)=1)*((A1:F1="○しろまる")*2+(A1:F1="△しろさんかく")))
- 偶数列の場合は
  =SUMPRODUCT((MOD(COLUMN(A1:F1),2)=0)*((A1:F1="○しろまる")*2+(A1:F1="△しろさんかく")))

(追記) (追記ここまで)

数式を検証する topへ

上記の数式がどのような計算過程なのかを検証したいと思います。
1. 数式が入力されているセルを選択します。
2. [数式]タブのワークシート分析グループの[数式の検証]を実行します。
3. 入力されている数式のアンダーラインのついた部分の検証が行われます。
  [検証]ボタンをクリックすると、一段階ごとに検証が進みます。
4. COLUMN(A1:F1) → {1,2,3,4,5,6} と列番号が配列に入っているのがわかります。
5. MOD({1,2,3,4,5,6},2) → {1,0,1,0,1,0} と列番号を2で割った余りが配列に入っています。
6. {1,0,1,0,1,0}=1 → {TRUE,FALSE,TRUE,FALSE,TRUE,FALSE} と1と等しい部分はTRUE、そうでない部分はFALSEとなっています。
7. (A1:F1="○しろまる") → {TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,} とA1からF1を"○しろまる"と等しいか否かの結果が配列に入っています。
8. {TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,}*2 の部分を検証します。
9. {TRUE,TRUE,FALSE,FALSE,FALSE,FALSE}*2 → [2,2,0,0,0,0} と計算されているのがわかります。
  TRUE=1、FALSE=0 として演算がされています。TRUE*2=1*2=2 FALSE*2=0*2=0となっています。
10. (A1:F1="△しろさんかく") → {FALSE,FALSE,TRUE,FALSE,FALSE,TRUE} とA1からF1を"△しろさんかく"と等しいか否かの結果が配列に入っています。
11. {2,2,0,0,0,0}+{FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,} を計算します。
12. {2,2,0,0,0,0}+{FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,} → {2,2,1,0,0,1} と計算されました。
  各部分の計算は 2+FALSE=2+0=2 2+FALSE=2+0=2 0+TRUE=0+1=1 0+FALSE=0+0=0 0+FALSE=0+0=0 0+TRUE=0+1=1 となっています。
13. {TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,}*{2,2,1,0,0,1} の計算の検証を行います。
14. {TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,}*{2,2,1,0,0,1} → {2,0,1,0,0,0} となりました。
  各部分の計算は TRUE*2=1*2=2 FALSE*0=0*0=0 TRUE*1=1*1=1 FALSE*0=0*0=0 TRUE*0=1*0=0 FALSE*0=0*0=0 となっています。
15. SUMPRODUCT({2,0,1,0,0,0}) → 3 と計算されました。配列内が合計されて3となっています。

数式を手軽に検証する topへ

上記の数式を手軽に検証する方法です。数式の一部分の計算状態を簡単に調べることができます。
1. 数式バーで数式の一部を選択します。
2. [F9]キーを押すと、選択した部分の計算結果が表示されます。
3. ほかの部分も同様に選択した後[F9]キーを押すと下図のようになります。
  選択した部分の計算状態を確認することができます。
  元の数式に戻すには[Esc]キーを押します。
また、下図のように計算結果を検証することもできます。上記とは選択範囲が異なります。

COUNTIF関数で飛び飛びのセル範囲を指定したい?:Excel関数の技

飛び飛びのセル範囲で計算をしたい topへ

COUNTIF関数で計算する

SUMPRODUCT関数で計算する

数式を検証する topへ

数式を手軽に検証する topへ

関連するページ