Excelで空の値を含む列を使ったSUMPRODUCT関数
2024-07-27
SUMPRODUCT関数は、複数の範囲を乗算して合計を計算する関数です。通常、数値のみを含む範囲を指定しますが、論理式を含む範囲を指定することで、条件を満たす値のみを合計することができます。この機能を活用することで、空の値を含む列であっても、条件に基づいた集計を行うことが可能です。
具体的な手順
- 条件式を作成する: 空の値を除外するための条件式を作成します。ここでは、論理式
<>""を使用します。これは、セルが空でないことを意味します。 - SUMPRODUCT関数を使用する: SUMPRODUCT関数を使用して、条件式と集計対象となる範囲を指定します。
例
次の表で、列Aには売上データ、列Bには顧客属性が格納されています。列Bには空の値も含まれています。
| A | B |
|---|---|
| 100 | 顧客1 |
| 50 | 顧客2 |
| 0 | 顧客3 |
| 200 | 顧客1 |
| 0 |
顧客1の売上のみを合計するには、以下の数式を使用します。
=SUMPRODUCT((B3:B6="顧客1")*(A3:A6))
この数式は、以下の処理を実行します。
B3:B6="顧客1": 列Bの範囲を検査し、"顧客1"と一致するセルのみを1に、それ以外のセルを0に変換します。A3:A6: 列Aの範囲をそのまま使用します。(B3:B6="顧客1")*(A3:A6): 上記の2つの結果を乗算します。 "顧客1"以外の行は0になるので、合計に影響しません。SUMPRODUCT: 乗算結果を合計します。
この例では、結果は300となります。これは、顧客1の売上100円と200円の合計です。
- 上記の例では、論理式
<>""を使用しましたが、他の論理式でも同様に使用できます。例えば、特定の値よりも大きい値のみを合計したい場合は、>値などの論理式を使用します。 - SUMPRODUCT関数は、複数の条件を組み合わせることもできます。例えば、顧客1かつ売上100円以上のデータのみを合計したい場合は、以下の数式を使用します。
=SUMPRODUCT((B3:B6="顧客1")*(A3:A6>=100))
- 空の値を含む列を扱う場合は、誤った結果にならないよう、注意が必要です。例えば、列Aに売上データ、列Bに顧客属性が格納されている場合、以下の数式は誤った結果を返します。
=SUMPRODUCT(B3:B6*A3:A6)
これは、列Bのすべてのセルと列Aのすべてのセルを乗算するため、空の値を含む行も集計に含まれてしまうからです。
=SUMPRODUCT((B3:B6="顧客1")*(A3:A6))
この数式は、前述の例と同じものです。顧客1の売上データのみを合計し、結果は300となります。
顧客属性ごとの売上合計
=SUMPRODUCT((B3:B6=D2)*(A3:A6))
この数式は、セルD2に指定された顧客属性の売上データのみを合計します。D2に"顧客1"と入力すると、結果は300となります。D2に"顧客2"と入力すると、結果は50となります。
顧客属性ごとの最大売上
=MAX(IF(B3:B6=D2,A3:A6,""))
空の値を除いた顧客属性ごとの売上平均
=AVERAGE(IF(B3:B6<>"",(B3:B6=D2)*A3:A6,""))
=COUNTIFS(B3:B6,D2)
- セル参照ではなく、範囲名を指定することもできます。
- 複数の条件を組み合わせることもできます。
- 集計結果をグラフなどで可視化することもできます。
COUNTIF関数とIF関数を使用する方法：
=COUNTIF(B3:B6,"顧客1")*AVERAGE(IF(B3:B6="顧客1",A3:A6,""))
利点:
- 比較的シンプルな構文
- 理解しやすい
欠点:
- 複数の条件を組み合わせる場合、複雑になりやすい
- SUMPRODUCT関数よりも処理速度が遅い場合がある
FILTER関数
FILTER関数を使用する方法：
=AVERAGE(FILTER(A3:A6,B3:B6="顧客1"))
- 新しい関数で、比較的わかりやすい構文
- 複数の条件を組み合わせやすい
- Excel 2016以降でのみ使用可能
ピボットテーブル
ピボットテーブルを使用する方法：
- 列Aを売上データ、列Bを顧客属性としてピボットテーブルを作成します。
- 顧客属性フィールドを行ラベル領域に、売上データフィールドを値領域にドラッグします。
- ピボットテーブルのオプションで、空の値の扱いを「除外」に設定します。
- 集計結果をわかりやすく表示できる
- さまざまな集計方法を簡単に実行できる
- データソースの変更に柔軟に対応できない
- 複雑な分析には向かない
VBAマクロ
VBAマクロを使用する方法：
Sub SampleMacro()
Dim売上データ As Variant
Dim顧客属性 As Variant
Dim顧客売上 As Variant
Dim i As Long
売上データ = Range("A3:A6").Value
顧客属性 = Range("B3:B6").Value
ReDim 顧客売上(1 To UBound(顧客属性))
For i = 1 To UBound(顧客属性)
If 顧客属性(i) = "顧客1" Then
顧客売上(i) = 売上データ(i)
Else
顧客売上(i) = 0
End If
Next i
MsgBox Application.WorksheetFunction.Average(顧客売上)
End Sub
- 複雑な処理や条件分岐にも対応できる
- 自動化が可能
- VBAの知識が必要
- マクロセキュリティの設定を変更する必要がある
最適な方法の選択
どの方法が最適かは、状況によって異なります。
- 簡単な集計であれば、COUNTIF関数とIF関数を使用するのがおすすめです。
- 複数の条件を組み合わせる場合や、新しい関数を使用したい場合は、FILTER関数を使用するのがおすすめです。
- 集計結果をわかりやすく表示したい場合は、ピボットテーブルを使用するのがおすすめです。
- 複雑な分析や自動化が必要な場合は、VBAマクロを使用するのがおすすめです。
excel excel-formula sumproduct
