Deprecated: The each() function is deprecated. This message will be suppressed on further calls in /home/zhenxiangba/zhenxiangba.com/public_html/phproxy-improved-master/index.php on line 456
COUNTIF関数で飛び飛びのセル範囲を指定したい？:Excel関数の技

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

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

スポンサードリンク

更新:2024/5/4;作成:2016/4/20

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

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

ここで使用するデータを下表に示します。
コピーしてお使いください。
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="△")))

数式を検証する　　 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へ

関連するページ

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

数式を検証する　　 topへ

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

○	○	△	×	×	△
○	○	○	△	△	○
○	○	○	×	×	△
△	△	△	△	△	×
△	△	△	○	×	△
△	△	○	△	○	×
△	△	○	×	×	△
×	○	△	×	×	△
×	△	○	△	△	×
×	△	×	×	×	△
×	×	△	△	△	○
×	×	△	○	×	△
×	×	○	△	○	×