[[20230117131947]]　『合計行最上部、空白までの合計を関数で処理したい』（やす）　ページの最後に飛ぶ

『合計行最上部、空白までの合計を関数で処理したい』（やす）

やりたいことは、B1だとすると「自分より下(B2以降)の、A列にある空白までの、B列の合計」を、関数で処理したいのです。
合計値は、データが増えるごとに変わっていきます。
SUBTOTALとINDEXとMATCHとOFFSETを駆使すればなんとかなるのかなと思って試行錯誤しているのですが、試行錯誤がうまく行かなすぎて困っています。

1行目を行コピーして、10行目に貼り付けたら、式も自動的に対応してくれることが一番理想です。

教えていただけますでしょうか。

　　　A　　　　 B　　　C　　　D

1　1月合計　　14:00　　5:25　　0:30
2　 230104　　1:00
3　 230105　　　　　　1:35
4　 230111　　2:00
5　 230114　　　　　　　　　　 0:30
6　 230118
7　 230120　　　　　　3:50
8　 230121　　11:00
9　(空白行)
10 2月合計
11　230201
・
・
・

月の合計を先頭行に設定し、以降に発生した情報を入力していく。
各月の間には、少なくとも1行の空白行がある(空白までが「今月」)。
A列は日付(yymmdd)。B、C、D列は時刻([h]:mm)。

＜使用 Excel：unknown、使用 OS：unknown ＞

やす　です。
Excelはwindows365、OSはwindows10です。
選択したつもりでしたが、反映されておりませんでした。
（やす） 2023/01/17(火) 13:48:32

 A列の最終行が300行目「未満」だとして　　※A300は空白セル

 B1 =SUM(B2:INDEX(B2:B$300,XMATCH("",$A2:$A$300&"")))
 表示形式　[h]:mm

 以上
（笑） 2023/01/17(火) 14:39:49

笑さん

当てはめてみたところ、やりたいような結果になりました！！！
考えすぎて煮詰まりすぎて、衝撃的に長い式にして、自分でも解読できなくなったり・・・
なのに、こんなに簡潔な式で解決できるとは。。。

ありがとうございました！
（やす） 2023/01/17(火) 15:15:35

 こんなんでも

 B1 =SUM(OFFSET(B2,0,0,MATCH(TRUE,INDEX(A2:A300="",0),0),1))

（通りすがり） 2023/01/17(火) 15:29:51

やすさん、横からすみません。
笑さん、もしくは分かる方であればどなたでもいいのですが、以下について教えてください。

笑さんの提示された以下の式

 B1 =SUM(B2:INDEX(B2:B$300,XMATCH("",$A2:$A$300&"")))

例題の場合は、簡単にいうと

 B1 =SUM(B2:B9)となると思うのですが、なぜこの B9 の部分が
 INDEX(B2:B$300,XMATCH("",$A2:$A$300&"")) に置き換えて、数式が成立するのでしょうか？

申し訳ありませんが、解説いただけると幸いです。
よろしくお願いいたします。

（教えてくん） 2023/01/18(水) 09:54:39

 INDEX(B2:B$300,XMATCH("",$A2:$A$300&""))
 =INDEX(B2:B$300,8)
 =B9（セル参照）
 だからじゃないかな。

 INDEXの戻り値って、「セルの値」ではなくて「セル参照」なので
 =CELL("address",INDEX(A1:B10,1,2))
 ってやると、$B$1が帰ってくるんですよ
https://support.microsoft.com/ja-jp/office/index-%E9%96%A2%E6%95%B0-a5dcf0dd-996d-40a4-a822-b56b061328bd
（稲葉） 2023/01/18(水) 10:13:35

 新しい XLOOKUPも「セル参照」を返しますので、これでもいいです。

 B1セル =SUM(B2:XLOOKUP("",$A2:$A$300&"",B2:B$300,0))

（半平太） 2023/01/18(水) 10:45:18

稲葉さん、半平太さん

お教えいただいて有難うございます。
完璧ではないですが大体理解できました。

これまで、INDEXのセル参照（セル範囲形式）を使ったことがなかったので、ピンと来ていませんでした。
XLOOKUPも含め、この辺りをもっと習得したいと思います。
有難うございました。

（教えてくん） 2023/01/18(水) 13:29:45

コメント返信：

[ 一覧(最新更新順) ]

[[20230117131947]] 『合計行最上部、空白までの合計を関数で処理したい』（やす） ページの最後に飛ぶ

[[20230117131947]]　『合計行最上部、空白までの合計を関数で処理したい』（やす）　ページの最後に飛ぶ