Excelの関数で起きた現象の原因がわかりません。

まず、関数の入れ子を使って下記の戻り値を求めました。

SUM関数のセル範囲をINDIRECT、ADDRESS、MATCHを使うことで、SUM関数内をいじらずに指定しているセルから検索してセル範囲をある程度自在に動かす。

下記はMATCHが参照しているExcel内の表です。
（実際のExce画面を添付してます）

Ａ列　　　│Ｂ列　　│
日付　　　│データ1│
2024/1/1│1　　　　│
2024/1/2│2　　　　│
・
・
・
2024/1/31│31　　　│

Ｆ2　　　│Ｇ2　　　│
2024/1/1│2024/1/31│

続いては、上の表を使って入力した関数です。
=SUM(INDIRECT(ADDRESS(MATCH(F2,A:A,),2,4)):INDIRECT(ADDRESS(MATCH(G2,A:A,),2,4)))
この関数からの戻り値は「496」となり、ここまでは問題なく動作しました。
本番の環境は別シートにある数値を集計する必要があるため、下記のように入力しました。

=SUM(INDIRECT(ADDRESS(MATCH(F2,データ!A:A,),2,4)):INDIRECT(ADDRESS(MATCH(G2,データ!A:A,),2,4)))
戻り値は「495」となり、どうやらセル範囲が一つずれているようでした。
試しに検索値を「2024/1/10」「2024/1/20」と入力したら「176」となり、やはりセル範囲がずれて「2024/1/11」～「024/1/21」の範囲を集計していました。

この現象は別シートが起因だと思われますが、最初作った表との差異はデータ1行分下にあることです。
ただ、MATCHの範囲指定はA:Aとしているため、1行分の違いがあるなしでズレる原因わかりません。
長文にも関わらず意味不明な説明となっていたら大変恐縮ですが、ご教授いただければ幸いです。

No.3 ベストアンサー 回答者： fujillin 回答日時： 2024/02/01 23:38

こんばんは

原因は
ADDRESS(MATCH(F2,データ!A:A,),2,4)
部分を計算させてみればわかると思いますが、この結果はセルアドレスだけになります。（シート名は付きません）
ですので、ご提示の2番目の式の実際の参照先は、A列はデータシートを参照しているけれども、SUMの対象となるB列は自シートを参照しているということではないかと推測します。
ふたつのシートでデータの位置が1行分ずれているのではないでしょうか？
あるいは、自シートのB列をクリアしたら結果が変わりませんか？

実は、ご提示の式をエクセルがどのようにして解釈してくれるのか不思議ですけれど、最初にご提示の式は論理的に考えると
=SUM(INDIRECT(ADDRESS(MATCH(F2,A:A,),2,4) & ":" & ADDRESS(MATCH(G2,A:A,),2,4)))
となるのではないでしょうか？


とは言え、アドレスを作成してINDIRECT関数に頼るというのはあまり感心できる方法とは思えません。
『F2、G2セルの日付の間にあるB列の和を求めたい』ということだろうと推測しますので、素直に
　=SUMIFS(B:B,A:A,">="&F2,A:A,"<="&G2)
ではダメなのでしょうか？
こちらの関数式であれば、元データ（A,B列）が順序だって並んでいなくても計算は可能です。

この回答へのお礼

お礼日時：2024/02/03 14:24

No.2 回答者： hyter 回答日時： 2024/02/01 23:28

=SUM(INDIRECT("データ!"&ADDRESS(MATCH(F2,データ!A:A,),2,4)):INDIRECT("データ!"&ADDRESS(MATCH(G2,データ!A:A,),2,4)))

こうですかね？

MATCH、ADRESSで別シート（データシート）でヒットするセルの位置（B3とか）はとれていますが、INDIRECTの時にB3をそのまま使ってしまっているので、本シートの位置として取り扱ってしまっているんだと思います。
なので、B3ではなくデータ!B3の形になればいいかと。