SUMIFで大文字と小文字を判別できない場合の解決策
はじめに
Google SpreadsheetのSUMIFは、英字の大文字・小文字を判別できません。
判別したいときは別の方法を使う必要があります。
ググって見つけた解決法がとてもシンプルで良かったので紹介します。
加えて、その仕組みを調べてみます。
SUMIFを使う場合
まずはSUMIFですが、以下のように使います。
=SUMIF(対象範囲, 対象文字列, 合計範囲)
ただ、これでは「Aa」と「aa」を同じものと認識して集計してしまい、望む結果が得られません。
SUMPRODUCT + EXACTで大文字・小文字を区別して集計
以下の書き方で大文字・小文字を区別したSUMIFの結果を得られます。
=SUMPRODUCT(EXACT(参照範囲, 検索キー)*取得する値の範囲)
以降では、構成する関数を読み解きながら、
なぜこのような結果が得られるのかを考えてみます。
が、先に結論を言うと、具体的な計算ロジックはわかりませんでした。
もし分かる方がいらしたらご教示いただけると幸いです。
SUMPRODUCT関数
構文
SUMPRODUCT(配列1, [配列2,…])
説明
2つの同サイズの配列または範囲にある対応する要素の積を計算し、その合計を返します。
解説
まず、配列1、配列2はいわゆる「範囲」のことです。
行数・列数は任意ですが、配列1と配列2はサイズが同じである必要があります。
例えば、配列1が2×2なら配列2も2×2となるように指定します。
同サイズの配列を指定すると、対応する要素の積を計算し、それらを合計します。
配列1の1行目1列目×配列2の1行目1列目
+配列1の1行目2列目×配列2の1行目2列目
+配列1の2行目1列目×配列2の2行目1列目
+配列1の2行目2列目×配列2の2行目2列目
EXACT関数
構文
EXACT(文字列1, 文字列2)
説明
2つの文字列が同一であるかを検証します。
解説
今回見ていただけると分かるように、文字列1には範囲を指定することができます。
その場合、
- 範囲の中で文字列2と完全一致する文字列が
- 文字列2と同じ行にある
場合のみ、結果がTRUEになります。
…なのですが、それがなぜ、
=SUMPRODUCT(EXACT(参照範囲, 検索キー)*取得する値の範囲)
と書いたときに、このような結果になるのかまでは理解が及びませんでした。
ちなみに、
=EXACT(参照範囲, 検索キー)
とセルA1に書いたとして、
=SUMPRODUCT(A1*取得する値の範囲)
と書いても、想定した結果になりません。これも謎。
終わりに
紹介しているやり方は便利なのですが、
なぜそうなるかの理屈が理解できておらずモヤモヤしています…
理解できたらまた改めて解説記事を書きます。