【Excel】SUMIFS関数の合計範囲が文字列型の場合、数値に変換して計算結果を出したい

Question

エクセルについての質問です。

やりたいこと
・「raw」シートのF列の合計値を計算する
・ただし、同行A列の日付が条件である（2020年2月の場合のみ加算する）

条件
・「raw」シートの数値は文字列型で保存されている（変更不可）
・「計算」シートのA1セルの計算式のみで結果を表示させたい

現在、A1セルには
=SUMIFS(raw!$F:$F,raw!$A:$A,">="&EDATE(C1,-1),raw!$A:$A,"<="&EDATE(C2,-1))
が入力されており、「raw」シートの該当セルを数値に変換すれば、正しい合計値が出ます。（C1,C2はそれぞれ2020/3/1、2020/3/31）
「raw」シートのデータ型は一切変えずに、この計算結果と同じものを出力する方法はありますでしょうか？

Answer 1

かなりむりやりですが、以下の数式で1578という値を得ることが出来ました。

動作が重い版

=SUMPRODUCT(
IFERROR(VALUE(raw!F:F), 0),
N(raw!A:A>=EDATE(C1,-1)),
N(raw!A:A<=EDATE(C2,-1))
)

動作が軽い版

=SUMPRODUCT(
VALUE(raw!F2:INDEX(raw!F:F,COUNTA(raw!F:F))),
N(raw!A2:INDEX(raw!A:A,COUNTA(raw!A:A))>=EDATE(C1,-1)),
N(raw!A2:INDEX(raw!A:A,COUNTA(raw!A:A))<=EDATE(C2,-1))
)

動作が軽い版の数式は以下の前提条件がないと動作しません。

• rawシートには1行目に必ず見出しが存在する
• データは2行目からはじまる
• A列・F列ともに同じ行まで値が入っており、以降に関係の無い値が入っていない

---

以下は、数式の簡単な解説になります。

F列の文字列を数値に変換

VALUEは範囲を引数にとると、その範囲内の文字列を数値に変換した配列を返してくれます。
これによりrawシートのF列の文字列を数値に変換できます。

しかし、SUMIFSはセル範囲を引数にする必要があるため、VALUEの結果の配列を使って計算できません。

そのため、配列を使って計算のできるSUMPRODUCTを使用しています。

日付判定

Excelの数式の比較演算子は、範囲に対して行うと、それぞれの値に対して比較を行った結果のTRUE・FALSEの配列が返されます。

ここまでの内容をもとにSUMPRODUCTの各引数の状態を表にすると以下のようなイメージになります。

VALUE(raw!F2:F7)	raw!A2:A7>=EDATE(C1,-1)	raw!A2:A7<=EDATE(C2,-1)
934	TRUE	TRUE
180	TRUE	FALSE
526	TRUE	TRUE
91	TRUE	FALSE
118	TRUE	TRUE
15	TRUE	FALSE

この表の各行の列方向にかけ算を行った値を合計するのがSUMPRODUCTになります。

ただし、TRUE・FALSEのままでは数値計算に使えないため、今回はNを用いて数値に変換しています。
NはTRUEを1、FALSEを0に変換するため、日付判定の両方の式がTRUEであれば、F列の値 * 1 * 1となりそれ以外の場合は0が掛けられるので、結果的に値をフィルターできます。

重い処理と軽い処理の違い

重い処理では、セルの列全体を配列化して計算しているため遅くなっています。また、VALUEは数値に変換できないとエラーになるため、IFERRORでエラーの場合は0としています。

軽い処理では、参照範囲を必要な範囲のみに絞り込む数式を入れています。
Excelの数式の:は、両側に範囲を指定するとその矩形範囲を取得できる演算子のため、:の右辺に範囲を返す関数であるINDEXを使って範囲を動的に設定しています。

raw!F2:INDEX(raw!F:F,COUNTA(raw!F:F))
↓
「raw!F2」
のセルと
「INDEX(raw!F:F,COUNTA(raw!F:F))」
の示すセルで作られる矩形範囲

---

Excelコピペ用テーブル(必要な部分だけ抜粋)

sample					sample
2020/2/2					="934"
2020/3/3					="180"
2020/2/2					="526"
2020/3/3					="91"
2020/2/2					="118"
2020/3/3					="15"