PartW  攻略 配列数式と関数による合計・件数を求める  その2

関数あらかると

 合計や件数を求める数式その2

 複数の明細から品名を基準とした合計と件数を求める
使用した関数は、SUMIF、SUMPRODUCT、SUM関数(配列数式)、COUNTIF関数、COUNT関数(配列数式)です。
求める結果は全て同じです。
 
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
A B C D
品名 金額
きちょうくん 3,700
カレンダー 1,000
きちょうくん 3,700
マスター道 2,000
マスター道 2,000
きちょうくん 3,700
  
  合計指標 SUMIF関数
  きちょうくん =SUMIF($A$2:$A$7,B10,$B$2:$B$7)
カレンダー =SUMIF($A$2:$A$7,B11,$B$2:$B$7)
  マスター道 =SUMIF($A$2:$A$7,B12,$B$2:$B$7)
  SUMPRODUCT関数
  =SUMPRODUCT(($A$2:$A$7=B10)*$B$2:$B$7)
  =SUMPRODUCT(($A$2:$A$7=B11)*$B$2:$B$7)
  =SUMPRODUCT(($A$2:$A$7=B12)*$B$2:$B$7)
  SUM関数(配列数式)
  {=SUM(($A$2:$A$7=B10)*$B$2:$B$7)}
  {=SUM(($A$2:$A$7=B11)*$B$2:$B$7)}
  {=SUM(($A$2:$A$7=B12)*$B$2:$B$7)}
 
件数指標 COUNTIF関数
きちょうくん =COUNTIF($A$2:$A$7,B23)
カレンダー =COUNTIF($A$2:$A$7,B24)
マスター道 =COUNTIF($A$2:$A$7,B25)
SUMPRODUCT関数
=SUMPRODUCT(($A$2:$A$7=B23)*1) 
=SUMPRODUCT(($A$2:$A$7=B24)*1)
=SUMPRODUCT(($A$2:$A$7=B25)*1)
SUM関数(配列数式)
{=SUM(($A$2:$A$7=B23)*1)}
{=SUM(($A$2:$A$7=B24)*1)}
{=SUM(($A$2:$A$7=B25)*1)}
COUNT関数(配列数式)
{=COUNT(IF(($A$2:$A$7=B23),1))}
{=COUNT(IF($A$2:$A$7=B24),1))}
{=COUNT(IF($A$2:$A$7=B25,1))}
 
セルA1からB7がデータリストです。リストから品名毎の合計と件数を求めることを目的としたサンプルです。
 合計は、合計指標セルB10からB12を範囲とする3品目(きちょうくん、カレンダー、マスター道)となります。
関数数式は、SUMIF関数、SUMPRODUCT関数、SUM関数(配列数式)の3種類を用いて同じ結果を求めています。
 件数は、件数指標セルB23からB25を範囲とする3品目(合計指標と同じ)となります。
関数式は、COUNTIF関数、SUMPRODUCT関数、SUM関数(配列数式)、COUNT関数(配列数式)の4種類を用いて同じ結果を求めています。


  1. 合計を求める
    • SUMIF関数
      関数式  =SUMIF(範囲,検索条件,合計範囲)

      指定された検索条件に一致するセルの値を合計します。


      セルC10の関数式です、 =SUMIF($A$2:$A$7,B10,$B$2:$B$7)  $A$2:$A$7 検索条件の範囲です。 セルB10は検索条件です。 セルB10の値は、「きちょうくん」ですので 検索範囲 $A$2:$A$7 から「きちょうくん」と一致する名前を検索します。一致する場合は金額範囲 $B$2:$B$7 の対象行の値を抽出し合計した結果を返します。
      一番シンプルな式がこの方法です。
      セルC11,12は、指標値のちがいで内容はセルC10と同様です。
       
      求める値は、11,100 となります。

    • SUMPRODUCT関数
      関数式  =SUMPRODUCT(配列1,配列2,配列3,...)
       引数として指定した配列の対応する要素間の積をまず計算し、さらにその和を返します。配列は2個から30個まで指定することができます。指定方法は違いますが考え方は、配列数式と同じです。

      セルC14の関数式です、 =SUMPRODUCT(($A$2:$A$7=B10)*$B$2:$B$7)  ($A$2:$A$7=B10) 検索条件の範囲です。 SUMIFと同じ条件なのですがSUMIFは、範囲、検索条件を引数として指定できますがSUMPRODUCT関数では配列1.....のように引数の指定方法が違います。 ($A$2:$A$7=B10) は検索条件を配列化したことになります。 $B$2:$B$7 は金額の配列です。 もちろん検索指標の配列と同じでなければなりません。* アスタリスクは積を求めます。SUMIF関数での図の仕組みと同じですが仕組み的には下図のようになります。 
       


      ($A$2:$A$7=B10)条件式は、TRUEの時 1 FALSEの時 0 が返値となるので条件の一致した配列位置の金額の加算の命令になります。求める値は、11,100 となります。
      セルC15,16は、指標値のちがいで内容はセルC14と同様です。

    • SUM関数(配列数式)
      関数式  =SUM(数値1,数値2,...)
      セル範囲に含まれる数値をすべて合計します。
      配列数式とはいうものの通常のSUM関数と違いはありません。違いとすれば、数式の入力後に Ctrl キーと Shift キーを押しながら Enter キーを押下し配列数式の宣言を行うこと。

      セルC18の数式です。 {=SUM(($A$2:$A$7=B10)*$B$2:$B$7)} 何度も記述していますが{}はキー入力ではありません。数式の入力後に Ctrl キーと Shift キーを押しながら Enter キーを押下することによりExcelが自動で数式の前後に付します。留意ください。内容は、SUMPRODUCT関数と同様です。検索条件に一致する配列位置同士の積を計算しSUM関数で合計した値が返されます。
      セルC19,20は、指標値のちがいで内容はセルC18と同様です。


  2. 件数を求める 
    • COUNTIF関数
      関数式  =COUNTIF(範囲,検索条件)

      指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数を返します。


      セルC23の関数式です、 =COUNTIF($A$2:$A$7,B23)  $A$2:$A$7 検索条件の範囲です。 セルB23は検索条件です。 セルB23の値は、「きちょうくん」ですので 検索範囲 $A$2:$A$7 から「きちょうくん」と一致する名前を検索します。一致する場合は個数をカウントし合計した結果を返します。
      一番シンプルな式がこの方法です。
      セルC24,25は、指標値のちがいで内容はセルC23と同様です。

    • SUMPRODUCT関数
      セルC27の関数式です、 =SUMPRODUCT(($A$2:$A$7=B23)*1)  合計を求める時は引数「配列2」の位置に金額の配列を指定していました。件数では定数 1 です。「全ての配列に 1 を引用しなさい」となります。  ($A$2:$A$7=B23)  この検索条件式の返値をもう一度考えます。TRUEの時 1 、そしてFALSEの時 0 でしたね。SUMPRODUCTは配列位置同士の積を求めてから加算ですのでTRUEの配列位置であれば 1*1=1 、FALSEの配列位置であれば 0*1=0 求められる値は 0 か 1 となります。その積の値を合計すれば検索条件に一致する件数を求められることになります。下図を参照し理解を深めて下さい。
       

      求める値は、3件となります。
      定数 1 を ($A$2:$A$7=B23) にしても同じ結果が求められます。=SUMPRODUCT(($A$2:$A$7=B23)*($A$2:$A$7=B23)) 

    • SUM関数(配列数式)
      SUM関数は値の合計を求める関数ですが、配列数式を使うと件数を求めることができます。

      セルC31の数式です。 {=SUM(($A$2:$A$7=B23)*1)} 内容は、SUMPRODUCT関数と同様です。検索条件に一致する配列位置同士の積を計算しSUM関数で合計した値が返されます。

    • COUNT関数(配列数式)
      関数式  =COUNT(数値1,数値2,...)
      範囲内で数値が入力されているセルの個数を調べることができます。

      セルC35の数式です。 {=COUNT(IF(($A$2:$A$7=B23),1))} SUMPRODUCT関数やSUM関数のように検索条件 ($A$2:$A$7=B23) IF関数と組み合わせて判断しています。IF関数で真の場合 定数 1 を返し、COUNT関数で真の条件の時の件数を求めます。考え方は、SUMPRODUCT関数と同様です。


  3. 配列数式やSUMPRODUCT関数、COUNTIF関数、SUMIF関数を知らなかったことを前提とした場合
    みなさんならどうしますか?  配列を使わないSUM関数とCOUNT関数で定義してみます。
     
    WorkCellを用意してIF関数で判断し検索指標と一致している条件行の金額を代入します。代入した金額をSUM関数で合計します。また、COUNT関数で件数を求めます。

    下図は、WorkCellの関数式を展開したものです。

    この方式から皆さんはどう感じましたか? どしどしご意見下さい。

 複合条件を指定して合計を求める
月と品名を検索条件として一致する条件行の金額を合計します。
 
10
11
12
13
14
15
16
17
18
19
20
A B C D
品名 金額
1 マスター道 2,000
1 マスター道 2,000
1 きちょうくん 3,700
2 きちょうくん 3,700
2 きちょうくん 3,700
2 マスター道 3,700
2 マスター道 3,700
2 きちょうくん 2,000
 
  月指標 商品指標 SUMPRODUCT関数
1 マスター道 =SUMPRODUCT(($A$2:$A$9=B12)*($B$2:$B$9=C12)*$C$2:$C$9)
1 きちょうくん =SUMPRODUCT(($A$2:$A$9=B13)*($B$2:$B$9=C13)*$C$2:$C$9)
2 マスター道 =SUMPRODUCT(($A$2:$A$9=B14)*($B$2:$B$9=C14)*$C$2:$C$9)
2 きちょうくん =SUMPRODUCT(($A$2:$A$9=B15)*($B$2:$B$9=C15)*$C$2:$C$9)
SUM関数(配列数式)
{=SUM(($A$2:$A$9=B12)*($B$2:$B$9=C12)*$C$2:$C$9)}
{=SUM(($A$2:$A$9=B13)*($B$2:$B$9=C13)*$C$2:$C$9)}
{=SUM(($A$2:$A$9=B14)*($B$2:$B$9=C14)*$C$2:$C$9)}
{=SUM(($A$2:$A$9=B15)*($B$2:$B$9=C15)*$C$2:$C$9)}
 
 ● 一風変わったSUMPRODUCT関数の指定
=SUMPRODUCT(ISNUMBER(FIND(B12,$A$2:$A$9))*ISNUMBER(FIND(C12,$B$2:$B$9))*$C$2:$C$9)

この式も同じ結果を求めることができます。ここでは、検索条件式をISNUMBER関数とFIND関数を組み合わせて定義しています。
 
上図は、解剖した関数表です。 指標欄のセルE,F列が月の条件判断です。セルE列 FIND(B12,$A$2:$A$9) は検索値がヒットすれば 1 を返し、 ノーヒットの時は #VALUE! を返します。
次のF列 =ISNUMBER(E3)は、FIND関数の返値が数値か否かをISNUMBER関数で判断しています。次の指標値は品名です。指標欄のセルH,I列が品名の条件判断です。セルH列 =FIND(G3,$B$3:$B$10)は検索値がヒットすれば 1 を返し、 ノーヒットの時は #VALUE! を返します。
次のI列 =ISNUMBER(H3)は、FIND関数の返値が数値か否かをISNUMBER関数で判断しています。
以上から紹介したSUMPRODUCT関数の引数 ISNUMBER(FIND(B12,$A$2:$A$9)) とISNUMBER(FIND(C12,$B$2:$B$9))は、ISNUMBER関数とFIND関数を組み合わせ 0 か 1 を一度に結果を求めていることになります。
次に図のAND関数欄ですがAND関数はそもそも乗算の機能と同じになります。SUMPROMPT関数の * はAND関数と同義語になります。セルJ列のAND関数のTRUE は 1 が返値となりますので 1 * 金額の式が成立します。FALSEの場合は 0 が返値となりますので 0 * 金額の式が成り立ち答えは 0 です。
ちょっと難しく感じますが、この方法を基礎として認識していれば、いろいろなシーンで簡単に応用することが可能です。 マスターして関数の視野を広げましょう。


合計と件数を基準にお送りしたPartV、W。 いかがでしたか。
特に、SUMPRODUCT関数と配列数式は、複数条件での計算には必要不可欠と思っています。
是非覚えて下さい。