エクセル 結合セルの1行下の数値を集計したいのですが・・

本日8時間以上格闘してますが解決出来ず、どなたかさまにお知恵を頂きたく。

図のように、「結合セル」の1行下に「小計」があり、その合計数値を、客先毎に抜き出したいというものです。図ではシンプルですが、実際には100枚のシートに分かれており、INDEX関数とMATCH関数を組み合せようとしています（100枚のシート名を書き出すにはVBAを作成済みです）。

①の数式ですと、結合セルの最上段のみを引っ張ってきてしまいNG。
③は、客先Bの小計を正しく引っ張ろうとして、結合セルの範囲を数式に入れたものですが、実際には100通りのパターンがあり（シート毎にバラバラ）、実用的ではありません。

②は、苦し紛れにトライしたものですが、NGなので無視して下さい。

上記の通り、既にVBAは使っているため複雑にしたくない為、「エクセル関数」による解決策をご教示頂きたく、宜しくお願い申し上げます。

質問者からの補足コメント

• 補足ですが、同じお客さんの同様の資料で他の分析をした際は、セル結合を解除し、空欄になった行に客先明をコピーし、客先と小計を文字結合し、VLOOKUP で集計しました（実際には客先・商品名・小計の3つを結合、かつ、小計の列をずらしました）。
  その時はシートが1枚だけだったのでそれで用は済みましたが、今回はシートが100枚ゆえ無理と判断し、格闘の末に断念し、お知恵を頂きたく存じます。

    補足日時：2021/02/11 18:29

No.1 回答者： 銀鱗 回答日時： 2021/02/11 17:59

基本的に結合したセルを含んだ表での計算はやっちゃダメ。

余程自身がない限り基本に忠実にやりましょう。

そんなわけで、元の表を作り直しましょう。
悩んだ時間が無駄になることはありません。
次からは横着せずに問題解決の方法を考えることができるようになりますよ。

この回答へのお礼

銀鱗さま
ありがとうございます。
もともとの表は、お客さんが作成したものを、当方が分析しようとしているものです（小生も結合は使わないことにしています）。
もとの表は100シートあり、（質問では簡略的に示した）A・Bの項目も４項目以上あります。

因みに、100枚のシート名を、分析のシートでは INDIRECT関数で計算しようとしており、VBAは避けたい（エクセル関数で解決したい）と考えております。

引き続きどなた様でも良いので、ご教示頂けますと幸いです。

お礼日時：2021/02/11 18:14

No.2 回答者： banzaiA 回答日時： 2021/02/11 22:30

いまいち理解できていませんが、

Ｅｘｃｅｌの［データ］［小計］
機能を使用すれば出来そうにも思いますが。
［小計］機能を使用すれば、添付されている図では
小計の上の行に「Ａ　小計」などと表示されるので、
それをINDEX関数とMATCH関数を組み合せて持ってくればいい。

この回答へのお礼

banzaiA さま

ご説明有難うございます。
質問のポイントが分かりにくく済みません。
質問でサンプルとして示した表の、D6, D11 の数字を、別の「まとめシート」で集計したいという事です。
もし可能であれば、サンプルのシート名をsheet1として、sheet2 で2,300 と2,600 が示せる式を教えて頂けますでしょうか？
是非宜しくお願い致します。

お礼日時：2021/02/11 23:48

No.3 回答者： nouble1 回答日時： 2021/02/12 10:39

其の前に、

各所からの　開示認可を、
取りましたか？


もし、

未取得なら、
首では　済みませんよ？


所で、

値の　記載位置が、
定型な　同ブック内なら、
くし刺し集計を　SUM構文で、

他ブックに　跨がるなら、
VLOOK構文で、

しましょうね。


さて、

在り来たりに　「客先・商品名・小計」の、
各々を　感知させて、
感知外　側値を、
0に　換算させて、
SUMPROUD構文か、SUM構文で、
集計すれば　いいだけでは？


で、

複数への感知は、
論理演算しか　足し算ですよね？


更には、

結合しようと　普通に、
集計できてますよ？

この回答へのお礼

nouble 1さま
最初の投稿で使用した表は、質問用にシンプルにモデル化した情報なので、開示認可の問題はありません。
「SUMPROUD構文」を調べてみましたがヒットしませんでした（SUMPRODUCT関数はありましたが、掛け算では解決出来ないように思いました）。
今回は、ピンポイントのテクニックのお知恵を知りたかったものですが、（他の方に対しても）もう少し全体像を説明しないと、分かりにくい質問になってしまい（かと言ってnouble 1さまのご指摘のように開示認可のリスクもあり、あまり詳細な説明も控えつつ）、このような場での情報交換の難しさも理解しました。今回はこれにて File Close とさせて頂きます。有難うございました。

お礼日時：2021/02/12 18:14

No.4 ベストアンサー 回答者： nouble1 回答日時： 2021/02/12 18:25

サムプラウド構文の、

機能と　扱い方は、
掛け算は　主に、
切り捨て　用いず、
集計に　用いるのだよ？


詳しくは、

配列数式の　解説を、
読んでね。

この回答へのお礼

nouble 1さま
追加でご教示有難うございました。
少しネットで調べてみましたが、もう少し時間を掛けて読み込みたいと思います。実際の仕事では、関数などを使ったスマートな方法ではなく、コピペを多めに使って何とか終わらせましたので、今後の為に配列数式につき読み込ませて頂きます。有難うございました。

お礼日時：2021/02/12 18:33