指定した月から月までの合計をarryformulaを使って算出したい。

Question

前提・実現したいこと

指定した月②から月①までの番号ごとの合計をarryformulaを使って算出したい。

例）①今月の月をプルダウンで選択する－2020/09
②自動で2か月前の日付が抽出される－2020/07
2020/07～2020/09 までのSKUごとの合計値が算出される
※イメージの画像を張り付けております。

試したこと

まずは、範囲を求めるところまでやってみましたが、結果できませんでした。
①別シートにその月のセル列名を別シート（プルダウン）に保存させる
②そのセル列名をVLOOKUPで引っ張り、範囲を数式で求める

⇒この結果が文字列（G4:G）で、範囲が戻ってきてしまいました。
結果として望ましいのは、C5のセルに、1000という結果が返ってくるようにしたいです。

他にもoffsetやmatch関数など駆使してみましたが、方法がわかりかねている状況でございます。

方法をご存じの方ぜひご教示いただければ幸いです。

Comments

[sawa]

質問のタイトルが「arryformulaを使って」ですが、使わずに シンプルに match,offset,sum でやった方が、4行目や C2の2か月前の月の自動表示なくシンプルに計算できそうです。 arryformula にこだわる理由はありますか？

[Nozomi0302]

sawaさん！お世話になります！
arryformula にこだわる理由としては、今作成中のシートを運用するのが関数などの知識が比較的少ないメンバーが利用をするため、数式の追加をなるべくしない仕組みにしたいと考えております。
また、現状関数の入ったセルを誤って消してしまう方が多いため、arryformulaにしその数式行を非表示にできればと思った次第です。
もし方法があれば、ご教示いただきたいです。何卒よろしくお願いいたします！

Answer 1

普通なら MATCHとOFFSETで該当範囲を取得してSUMで合計を出すのがシンプルなので、
C5セルに 以下の式を入れて、あとは下にコピペで。

=SUM(OFFSET($G5,0,MATCH($B$1,$G$1:$N$1,0)-3,1,3))

C5以降に式を入れたくない（ARRAYFORMULA的なことがしたい）ということなら、
残念ながら MATCHやOFFSET、SUMとARRAYFORMULA は相性悪いので、違うアプローチが必要。
MMULTを使った式になります。C4セルに以下の式を入れる。（C4は 0が表示される）
※A列が入力されている行が計算対象、間飛び考慮なし

=MMULT(ARRAYFORMULA(N(OFFSET($G4,0,MATCH($B$1,$G$1:$N$1,0)-3,COUNTA(A4:A)+1,3))),SEQUENCE(3,1,1,0))

後々他の人がメンテする等を考慮すると、理解しやすい上の式の方がいいと思います。

Comments

[Nozomi0302]

sawaさん！ありがとうございます！
２つの式を試してみました！
想定通りの挙動となり、大変ありがたいです！！！
どちらを使うかは、担当者と相談し、取り決めたいと思います。
本当にありがとうございます！