1. Home » 
2. エクセル関数の技 » 
3. COUNTIF関数で飛び飛びセルを範囲指定したい？

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

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

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

• 下図のようなデータがあります。A,C,E列とB,D,F列に分けて計算を行います。
  ここでの課題は○は2点、△は１点と換算して合計を求めるという計算式を作成することです。
  • 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]キーを押します。
     
• また、下図のように計算結果を検証することもできます。上記とは選択範囲が異なります。
  • 
  • 

関連するページ

• カウント(COUNT)の関数の使い方
• 条件に一致するセルをカウントする(COUNTIF関数,COUNTIFS関数)
• SUMPRODUCT関数の使い方

スポンサードリンク

　PageViewCounter
　
　Since2006/2/27