まず、関数の入れ子を使って下記の戻り値を求めました。
SUM関数のセル範囲をINDIRECT、ADDRESS、MATCHを使うことで、SUM関数内をいじらずに指定しているセルから検索してセル範囲をある程度自在に動かす。
下記はMATCHが参照しているExcel内の表です。
(実際のExce画面を添付してます)
A列 │B列 │
日付 │データ1│
2024/1/1│1 │
2024/1/2│2 │
・
・
・
2024/1/31│31 │
F2 │G2 │
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ベストアンサー
- 回答日時:
こんばんは
原因は
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列)が順序だって並んでいなくても計算は可能です。
No.2
- 回答日時:
=SUM(INDIRECT("データ!"&ADDRESS(MATCH(F2,データ!A:A,),2,4)):INDIRECT("データ!"&ADDRESS(MATCH(G2,データ!A:A,),2,4)))
こうですかね?
MATCH、ADRESSで別シート(データシート)でヒットするセルの位置(B3とか)はとれていますが、INDIRECTの時にB3をそのまま使ってしまっているので、本シートの位置として取り扱ってしまっているんだと思います。
なので、B3ではなくデータ!B3の形になればいいかと。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) エクセルのマクロを教えてください 4 2022/02/04 08:05
- Excel(エクセル) Excelでなぜこのような式をつかっているのでしょうか、行に1,2,3と連番を振るだけなのに 5 2023/04/08 20:00
- Excel(エクセル) エクセルの数式について ブック内の別シートの値の含まれたセルの個数を集計したい 全シート一覧のシート 1 2022/07/21 19:28
- Excel(エクセル) [Excel] 2つの表のセル同士の突合方法について質問です。 添付の表で、 {=AND(EXACT 4 2023/09/06 22:35
- Excel(エクセル) エクセルでエラーを無視して一番左側のセルの値を返したい 2 2023/07/27 13:06
- Visual Basic(VBA) 最終列の右へSUM関数を作成するため下記コードを実行しましたが、最終列「10月28日」が上書きされて 3 2022/12/05 20:32
- Excel(エクセル) エクセル 条件に合う日付に入力された時間数の合計したい 4 2022/06/17 22:18
- Excel(エクセル) 条件に合った数値の合計を表示させたい関数と条件指定の方法 3 2023/05/13 16:07
- Visual Basic(VBA) VBA コード 4 2023/12/12 16:24
- Excel(エクセル) エクセルで値ではなく関数を参照する方法 6 2023/03/19 00:50
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
Excelファイルが閉じられい!
Excel(エクセル)
-
エクセルのセル統合について
Excel(エクセル)
-
曜日の 月 を (月) と表示させる方法について
Excel(エクセル)
-
-
4
IF関数
Excel(エクセル)
-
5
エクセル詳しい方教えて下さい
Excel(エクセル)
-
6
なぜか「Nextに対応するForがありません」
Excel(エクセル)
-
7
同じ型【ハイフンと数字】をぶつけて、イコールである事を証明する関数はありますか?
Excel(エクセル)
-
8
Excel関数の質問になります
Excel(エクセル)
-
9
Excelのテーブルでmatch関数の使い方について
Excel(エクセル)
-
10
Excel 領収書発行
Excel(エクセル)
-
11
エクセルの表の参照値から円を取って数字で扱えるようにしたい
Excel(エクセル)
-
12
“丸(〇/○/◯)”に似た文字…
Excel(エクセル)
-
13
excelの不要な行の削除ができない!
Excel(エクセル)
-
14
Excel 大なり小なりを表すとき、 例えば「10以上」なら、>=10 と表せますが、 この10の部
Excel(エクセル)
-
15
式の解読のお願いm(_._)m
Excel(エクセル)
-
16
数値から名前が作成できなくなっているッ!?
Excel(エクセル)
-
17
Excelで文字合成
Excel(エクセル)
-
18
Excelでこういう年を切り替えられる簡易なカレンダーを作ったのですが、 年や月を変えると、色が付い
Excel(エクセル)
-
19
当番表の作成について
Excel(エクセル)
-
20
りんご"と"みかん"というシートがあります。 "りんご"というシートの B列にて、B2から40行お"
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelデータをコピペして、ペー...
-
スプレッドシート、Excelでの数...
-
エクセルの関数について教えて...
-
スプレッドシートで使う数式を...
-
Microsoft Officeの中古は信用...
-
エクセルで会社の従業員のデー...
-
Excelで50個のセルに同じ文字を...
-
エクセルの表で1年間の曜日を...
-
エクセルでの特別な文字を上に...
-
エクセルで不等号記号(≠)が上に...
-
エクセルでB列でフィルターをか...
-
エクセルでセルに標準で入力さ...
-
Excelで、項目の種類ごとにカウ...
-
【マクロ】アクティブセルにブ...
-
EXCELの質問です 119から足した...
-
【マクロ】アクティブセルの、...
-
【マクロ】アクティブセルの行...
-
エクセルの空欄をつめて、次の...
-
Excelの数式について教えてくだ...
-
非表示列の再表示に失敗
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報