![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
まず、関数の入れ子を使って下記の戻り値を求めました。
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行分の違いがあるなしでズレる原因わかりません。
長文にも関わらず意味不明な説明となっていたら大変恐縮ですが、ご教授いただければ幸いです。
![「Excelの関数で起きた現象の原因がわか」の質問画像](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/c/542933435_65bb92947b3de/M.jpg)
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(エクセル)
-
IF関数
Excel(エクセル)
-
-
4
曜日の 月 を (月) と表示させる方法について
Excel(エクセル)
-
5
エクセル詳しい方教えて下さい
Excel(エクセル)
-
6
なぜか「Nextに対応するForがありません」
Excel(エクセル)
-
7
同じ型【ハイフンと数字】をぶつけて、イコールである事を証明する関数はありますか?
Excel(エクセル)
-
8
Excel関数の質問になります
Excel(エクセル)
-
9
Excelのテーブルでmatch関数の使い方について
Excel(エクセル)
-
10
Excel 大なり小なりを表すとき、 例えば「10以上」なら、>=10 と表せますが、 この10の部
Excel(エクセル)
-
11
excelの不要な行の削除ができない!
Excel(エクセル)
-
12
式の解読のお願いm(_._)m
Excel(エクセル)
-
13
Excel 領収書発行
Excel(エクセル)
-
14
エクセルの表の参照値から円を取って数字で扱えるようにしたい
Excel(エクセル)
-
15
“丸(〇/○/◯)”に似た文字…
Excel(エクセル)
-
16
Excelで文字合成
Excel(エクセル)
-
17
数値から名前が作成できなくなっているッ!?
Excel(エクセル)
-
18
Excelでこういう年を切り替えられる簡易なカレンダーを作ったのですが、 年や月を変えると、色が付い
Excel(エクセル)
-
19
当番表の作成について
Excel(エクセル)
-
20
Excelでの判別方法
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
-1234 を (1234) と表記する由...
-
エクセルで現場ごとに機材の移...
-
他部署からもらう データで、 ...
-
Excel 1の位の数字で処理を分岐...
-
Excelのセルの色を変えた行(す...
-
エクセルの数式について教えて...
-
Excelでの記号と特殊文字につい...
-
とびとびの大量セルを選択した...
-
MACにおけるエクセルの入力トラ...
-
ある表にフィルターをかけて出...
-
Lookup関数
-
エクセルの数式バーのフォント...
-
再質問です。マクロの修正箇所...
-
エクセルで「ページレイアウト...
-
EXCELの散布図で日付が1900年に...
-
【マクロ】VLOOKUPにて参照元に...
-
exselの質問です
-
エクセルでファイルの最終更新...
-
Excel 大小比較演算子による「...
-
エクセルの問題です。絶対値の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報