- Home »
- エクセル関数の技 »
- COUNTIF関数で飛び飛びのセル範囲を指定したい？
- 条件付きでカウントしたいときにはCOUNTIF関数を使うことが多いと思います。ところが対象のセルが飛び飛びであるとちょっと面倒・・・というかCOUNTIFではセル範囲に飛び飛びのセル範囲を指定することができません。
ここではSUMPRODUCT関数を利用して計算をする例を示します。
更新:2024/5/4;作成:2016/4/20
- ここで使用するデータを下表に示します。
コピーしてお使いください。
A,C,E列(奇数列)とB,D,F列(偶数列)に分けて計算を行います。
ここでの課題は○は2点、△は１点と換算して合計を求めるという計算式を作成することです
なお、ここで使っている○は記号の○で漢数字の〇ではなありませんのでご注意ください。
| ○ |
○ |
△ |
× |
× |
△ |
| ○ |
○ |
○ |
△ |
△ |
○ |
| ○ |
○ |
○ |
× |
× |
△ |
| △ |
△ |
△ |
△ |
△ |
× |
| △ |
△ |
△ |
○ |
× |
△ |
| △ |
△ |
○ |
△ |
○ |
× |
| △ |
△ |
○ |
× |
× |
△ |
| × |
○ |
△ |
× |
× |
△ |
| × |
△ |
○ |
△ |
△ |
× |
| × |
△ |
× |
× |
× |
△ |
| × |
× |
△ |
△ |
△ |
○ |
| × |
× |
△ |
○ |
× |
△ |
| × |
× |
○ |
△ |
○ |
× |
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="△")))
- 上記の数式がどのような計算過程なのかを検証したいと思います。
- 数式が入力されているセルを選択します。
- [数式]タブのワークシート分析グループの[数式の検証]を実行します。
- 入力されている数式のアンダーラインのついた部分の検証が行われます。
[検証]ボタンをクリックすると、一段階ごとに検証が進みます。
- COLUMN(A1:F1) → {1,2,3,4,5,6} と列番号が配列に入っているのがわかります。
- MOD({1,2,3,4,5,6},2) → {1,0,1,0,1,0} と列番号を2で割った余りが配列に入っています。
- {1,0,1,0,1,0}=1 → {TRUE,FALSE,TRUE,FALSE,TRUE,FALSE} と1と等しい部分はTRUE、そうでない部分はFALSEとなっています。
- (A1:F1="○"） → {TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,} とA1からF1を"○"と等しいか否かの結果が配列に入っています。
- {TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,}*2 の部分を検証します。
- {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となっています。
- (A1:F1="△"） → {FALSE,FALSE,TRUE,FALSE,FALSE,TRUE} とA1からF1を"△"と等しいか否かの結果が配列に入っています。
- {2,2,0,0,0,0}+{FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,} を計算します。
- {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 となっています。
- {TRUE,FALSE,TRUE,FALSE,TRUE,FALSE,}*{2,2,1,0,0,1} の計算の検証を行います。
- {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 となっています。
- SUMPRODUCT({2,0,1,0,0,0}) → 3 と計算されました。配列内が合計されて3となっています。
- 上記の数式を手軽に検証する方法です。数式の一部分の計算状態を簡単に調べることができます。
- 数式バーで数式の一部を選択します。
- [F9]キーを押すと、選択した部分の計算結果が表示されます。
- ほかの部分も同様に選択した後[F9]キーを押すと下図のようになります。
選択した部分の計算状態を確認することができます。
元の数式に戻すには[Esc]キーを押します。
- また、下図のように計算結果を検証することもできます。上記とは選択範囲が異なります。
関連するページ
スポンサードリンク
よねさんのWordとExcelの小部屋 »
エクセル関数の技 » COUNTIF関数で飛び飛びのセル範囲を指定したい？
PageViewCounter
Since2006/2/27
