[[20221024155225]]　『日ごとのデータから月ごとのデータへの抽出』（panda）　ページの最後に飛ぶ

『日ごとのデータから月ごとのデータへの抽出』（panda）

ファイル@1に日ごとのデータがあり、ひと月のうち計測した日にのみ数値が入っています。その他の日は＃N/Aが入っています。

　　A　　 B　　　　C　　　　D
1　日付　地点1　　地点2　　地点3　・・・
2　4/1　＃N/A　　＃N/A　　＃N/A　・・・
3　4/2　＃N/A　　＃N/A　　＃N/A　・・・　
4　4/3　 2.0　　　 3.0　　　　 -　　・・・
5　4/4　＃N/A　　＃N/A　　＃N/A　・・・　　
6　4/5　＃N/A　　＃N/A　　＃N/A 　・・・　
･　　･　　･　　　　･　　　　･
･　　･　　･　　　　･　　　　･
･　　･　　･　　　　･　　　　･

ファイル@2に月ごとのデータとして抽出したいので、

　　A　　B　　　　C　　　　D
1　　　月　　　　地点1　　地点2
2　4/1　4/30
3　5/1　5/31
4　6/1　6/30
5　7/1　7/31
6　8/1　8/31
･　　･　　･
･　　･　　･
･　　･　　･

ファイル@2のC2セルに
=sum((@1!$A$2:$A:$5000>=$A2)*(@1!$A$2:$A:$5000<=$B2)*IFERROR(INDEX(@1!$B$2:$Z$5000,0,MATCH(C$1,@1!$B$2:$Z$2,0)),0))
という式を入れ、C列はうまくいったのですが、D列は「-」が入っている為エラーになってしまいました。
ここまででも色々調べた上でたどり着いたので、手詰まりになってしまいました。
アドバイスよろしくお願いいたします。

＜使用 Excel：Office365、使用 OS：Windows10 ＞

を　#N/A に置き換えておけば良いんでない？

何が手詰まりなのか不明ですが。
一応置き換えてみた結果、=ISNA(A1)でTrueになりました。
（置き換え） 2022/10/27(木) 15:02:34

分かりづらくて申し訳ありません。
・は続きがあるという意味で、セルに「・」が入っている訳ではありません。
上の例でいくと、「-」が入っている地点3の計算がエラーになってしまうのを直せないのが手詰まりです。
他の人も使用してるデータの為、「＃N/A」、「-」は他のものに置き換えられません。
ご検討よろしくお願いします。

（panda） 2022/10/27(木) 15:09:39

 こんにちは

 そのエラー以外を条件に指定したらどうでしょうか？

 "<>#N/A" イメージを入れるってことです。
 とか...IFERROR の位置を変更するとか

 ↓この辺が参考になるかもです。

 参考 : エラー以外で合計する（SUM関数がエラーになる時）
https://spread-sheets.com/leverage/sum_exclusion_error/#:~:text=1.%20%E5%90%88%E8%A8%88%E5%80%A4%E3%82%92%E8%A1%A8%E7%A4%BA,%E3%82%BB%E3%83%AB%E3%82%92%E9%81%B8%E6%8A%9E%E3%81%97%E3%81%BE%E3%81%99%E3%80%82&text=2.%20%3DSUMIF(A1%3A,%22)%20%E3%81%A8%E5%85%A5%E5%8A%9B%E3%81%97%E3%81%BE%E3%81%99%E3%80%82&text=%E6%9D%A1%E4%BB%B6%E3%81%AB%E6%8C%87%E5%AE%9A%E3%81%97%E3%81%9F%E3%80%8C%3E0,%E3%81%99%E3%82%8B%E3%81%93%E3%81%A8%E3%81%8C%E3%81%A7%E3%81%8D%E3%81%BE%E3%81%99%E3%80%82

（あみな） 2022/10/27(木) 15:44:20

 ああ、文字化けしてましたか。

 -を　#N/A に置き換えておけば良いんでない？

 半角スペース無しに頭に-をつけると表示されないんですね。

 #N/Aで駄目なら、普通に消すとか？

（置き換え） 2022/10/27(木) 16:06:01

 エラー値を無視するといえば、AGGREGATE関数が思いつくわけですがいかがでしょう
 あと、FILTER関数も使えるかも
（´・ω・`） 2022/10/27(木) 16:21:55

 地点1,地点2,地点3…等をMATCHでしないでもできそうな気がするのだが

 C2セルの式=SUMIFS('@1'!B$2:B$5000,'@1'!$A$2:$A$5000,">="&$A2,'@1'!$A$2:$A$5000,"<="&$B2,'@1'!B$2:B$5000,"<>#N/A")

 こんなんだとどうなるでしょうか？

（あみな） 2022/10/27(木) 16:36:37

あみな様
ご返信が遅くなり申し訳ありません。
紹介いただいたサイトを参照し、SUMをSUMIFにして、
=sumif(@1!$B$2:$Z$5000,<>"-",(@1!$A$2:$A:$5000>=$A2)*(@1!$A$2:$A:$5000<=$B2)*IFERROR(INDEX(@1!$B$2:$Z$5000,0,MATCH(C$1,@1!$B$2:$Z$2,0)),0))
としましたが、数式に問題がありますとのアラートが出てしまいました。
また、地点数が100近くあり、シートや並びがバラバラなので、出来ればmatchで拾っていきたいと思っています。（シートが異なる場合は、参照のシート名だけ変更する予定です。）
アドバイスありましたらよろしくお願いいたします。

（panda） 2022/11/02(水) 10:47:03

置き換え様
ご返信が遅くなり申し訳ありません。
このデータを作成している方に空欄にしていいか聞いたところ、「-」のままが良いと言われてしまいまして。。。空欄にするとエラーにならないので、できればそうしたいところなのですが。。。
（panda） 2022/11/02(水) 10:50:20

（´・ω・`）様
ご返信が遅くなり申し訳ありません。
aggregateでエラーを無視でやってみましたが、結果は同じでした。
fifilter関数を使ったことがないのですが、filterで数値のみを抽出してから合計する感じでしょうか？
（panda） 2022/11/02(水) 11:00:21

 すみません
 AGGREGATE関数で、集計方法がSUMの場合、配列ではだめのようですね
（´・ω・`） 2022/11/02(水) 11:48:53

 >＃N/Aが入っています。 
 そこには数式が入っているんですか。
 何故エラー表示をしておくのでしょうか。
（???） 2022/11/02(水) 14:48:39

???様
他の方が作ったものなので定かではないですが、グラフに反映しているデータなので、＃N/Aにしてあるのだと思います。
観測の期間が決まっているので、先の日付まで入れて、全ての欄に＃N/Aをいれておき、観測した日のみ、数値を入力しているのだと思います。
そうすると、グラフに観測期間最終日まで表示していても、未観測の箇所はグラフが表示されないからです。
数式は入っていません。
（panda） 2022/11/02(水) 15:40:04

そうでしたか。
失礼しました。
（???） 2022/11/02(水) 16:31:30

 ＞Office365 
 Let関数が使えるならば、

 C2セル =LET(r,XLOOKUP(C$1,'1'!$B$2:$Z$2,'1'!$B$3:$Z$5000,""),d,'1'!$A$3:$A$5000,SUMIFS(r,r,">-10e15",d,">="&$A2,d,"<="&$B2))

（半平太） 2022/11/02(水) 16:43:32

半平太様

他の仕事の合間に調べながら進めているもので、お返事が遅れ大変失礼いたしました。
教えていただいた式でできました！！！
let関数は使ったことがなかったので、調べて、実際使用してみて、感覚がつかめました。
おかげさまで解決いたしました。
ありがとうございます。
（panda） 2022/11/08(火) 10:07:57

コメントいただいた皆様、お力添えありがとうございました。
無事解決いたしました。
（panda） 2022/11/08(火) 10:08:45

コメント返信：

[ 一覧(最新更新順) ]

[[20221024155225]] 『日ごとのデータから月ごとのデータへの抽出』（panda） ページの最後に飛ぶ

[[20221024155225]]　『日ごとのデータから月ごとのデータへの抽出』（panda）　ページの最後に飛ぶ