たとえば検索条件と一致する行の値を合計する場合はSUMIFまたはSUMIFS関数を使用するが、検索条件の範囲に文字列「030」があった場合は数値30、文字列「3E1」があった場合は指数表記とみなされ3×10の1乗=30に自動変換されます。
つまり、「030」と「3E1」は同じ値として認識されてしまいます。
今回は検索条件の範囲が文字列で入力されているのにSUMIF関数など特定の関数では勝手に数値に変換して誤認識されてしまうのを回避する方法を紹介します。
SUMIF関数で意図しない値が合計されてしまう事例
検索範囲の文字列が数字の場合はSUMIF関数内で数値に変換されます。
例:文字列「030」→数値の30に変換される
検索範囲の文字列が数字かつ「E」が含まれている場合は指数表記とみなされ数値に変換されます。
例:文字列「3E1」→数値の30に変換される (3×10の1乗=30)

上記の例では、検索条件「030」の合計に「3E1」が含まれていることが確認できます。
検索範囲の数字や指数表記を文字列として扱う方法
方法1:SUMPRODUCT関数を使用する
まず1つ目はSUMPRODUCT関数を使用する方法です。
SUMPRODUCT(($B$3:$B$11=$E3)*($C$3:$C$11))
SUMPRODUCT((検索範囲=検索条件)*(合計範囲))

SUMPRODUCT関数では検索条件の範囲に数字が含まれていても勝手に数値に変換されることなく、正常に合計が算出されました。
SUMPRODUCT関数とは
配列の積の合計を算出します。また、応用として配列の加算、減算、さらに条件を指定して合計を算出することもできます。
方法2:TEXT関数を使用する
もっとも分かりやすい方法が、TEXT関数を使って検索条件の範囲を文字列で指定することです。
※Excel2007など古いバージョンでは以下の数式を利用できません
SUMIF(TEXT($B$3:$B$11, "000"), $E3, $C$3:$C$11)
上記数式では、第一引数(検索条件の範囲)にTEXT関数をネストして文字列を指定しています。
方法3:文字列先頭にアポストロフィーを追加する
検索条件の範囲に入力されている文字列先頭に「’」(アポストロフィー)を追加すると、数字もすべて文字列として扱われます。
最後に
今回は知っていればいずれ役に立つかもしれないちょっとしたテクニックを紹介しました。
次回は少し難易度が上がりますが、配列数式を活用した実用テクニックを紹介します。
コメント