エクセル／横軸の条件を見つけ・広範囲の合計範囲の値を合計する

エクセル集計方法にて、以下のような複数条件の合計値を算出したく、
適切な方法にお心当たりある方ご教示ください！


＜例＞以下の「※集計希望」の「商品●」と同様の商品名を「▼シート1」の横軸から検索のうえ、
「※集計希望」の「数量」部分に合計値を算出希望です。

＝SUMIFS(シート1!$D:$Z,シート1!$1:$1,商品名A)　

のようなイメージにて集計可能かと思ったのですが
うまくいきませんでした。

どなたかご教示くださいませ。



※集計希望
-------------------------------------------------------
　商品A　　　　　　商品B　　　　　　　　　商品C　　　・・・・・・・・・・
数量　売上　　　　数量　売上　　　　　　数量　売上
20　\2000　　　55　\2,200,000　　　40　\600000
-------------------------------------------------------

▼シート1
-------------------------------------------------------
顧客ID　　　　商品A　　商品B　　商品C　　　商品D　　商品E　　商品F　・・・・・・・・・・
1234567　　　　1　　　　5　　　　　　　　　　　　　　　　　　　1
1234568　　　　3　　　　　　　　　　　　　　1　
1234569
1234570　　　　1
・
・
・
・

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

• 正式な集計希望としては、
  商品名ほか、条件1～10の各条件にて商品毎の「数量」算出したく思っています。

  No.3の回答に寄せられた補足コメントです。 補足日時：2019/04/12 11:27

No.4 回答者： 銀鱗 回答日時： 2019/04/12 11:48

ごめんよ。

2019/04/12 11:27 の補足を確認したけど、No.1で回答した内容をそのまま使えるじゃない。
ちょっと応用するだけです。
条件の出し渋りをしているようですので、応用については自身で考えてみてください。


・・・余談・・・

回答で示した数式が何をしているのかを確認もせず新たな要求をするってことから、
　「代わりに作ってください。参照するセルを変えて使います」
という「作業依頼」と受け取って良いでしょうか？

ここはQ&Aサイトなので、分からない事を自力で解決することができるようにアドバイスはしますが、
作業依頼は受けられません。

中には勘違いしている回答者さんが「自分はこんなことができるんだ」と自己満足目的で請け負うこともありますけどね。
数式とその数式を使ったスクリーンショットを貼り付ける人に多いですね。
そういう人にどういう手順で数式を作ったのかを聞いても答えてくれませんよ。


・・・独白・・・

最近、数式を作る手順を示しながらアドバイスする機会が減ったなあ。

No.2の回答者さんがとても良いことを言っていますね。
そもそもデータを後から処理することを考えていない今の作りが悪いんです。
根本的な解決を望むのであれば、全体の見直しをしよう。
1万件程度のデータなら半日かけてでも作り直すことを勧めたい。

この回答へのお礼

いろいろとご親切にありがとうございます。

大変恐れ入ります。
ご説明不足や失礼があり申し訳ございませんでした。

お礼日時：2019/04/12 12:07

No.3 回答者： nouble1 回答日時： 2019/04/12 08:51

確かめてないけど、

検索は　MATCH構文で、
https://qiita.com/Hansy/items/d4defa2985e030b01f24
どの列かを　見極めさせて、

取得すべき量は　COUNTA構文で、
http://www.excel-list.com/counta.html
特定して、

実際の　データ取得は、
OFFSET構文で、
http://officetanaka.net/excel/function/function/ …
OFFSET(顧客IDのセル位置,MATCH(探す商品名, 顧客IDの一列,1)-1,0,COUNTA(顧客IDの一列)-1,1)
と　して、

必要なら、
INDEX構文で　囲って、

SUMPRDUCT構文で　集計して、

結果を　&で、
繋げば、
良いのじゃ　ないかな。

この回答へのお礼

ありがとうございます。


説明不足＆安易に考えすぎており失礼いたしました。
正式な質問を別途あげさせていただきます。

可能でしたら引き続きご教示くださいませ。

お礼日時：2019/04/12 10:57

No.2 回答者： ママチャリ 回答日時： 2019/04/12 08:02

いまさらなのですが・・・。

そもそもシート１のレイアウトに問題があると思います。
シート１って、基礎データですよね？このデータを使って、いろいろな形の集計表を作るのであれば、今の様式は使い勝手が悪いです。こんな感じの表にすると後々、楽ですよ。

顧客ID　　商品名　数量
1234567　商品A　１
1234567　商品B　5
1234568　商品A　3
・
・
・

No.1 回答者： 銀鱗 回答日時： 2019/04/12 01:49

(´・ω・`)

そもそも使い方がおかしいぜ

SUMIFS関数は、
　＝SUMIFS(検索範囲 ,条件1,合計範囲1 ,条件2,合計範囲2 ・・・)
と指定するんだ。
合計範囲に「商品名A」が来るのは「商品名A」が範囲名であると解釈することで納得はできるが、
条件が「1行目」ってナニ？


・・・本題・・・

これってさ、列単位で合計を出せばいい話だよね。
SUMIFS関数どころかSUMIF関数も使わないよ。

商品名に重複が無ければ、MATCH関数とOFFSET関数の組み合わせでSUM関数の合計範囲を指定するだけで良い。
　＝SUM(OFFSET(A1,0,MATCH("商品A",B1:Z1)100,1))
こんなかんじになる。
（このままでは使えません。あくまでもイメージです）

まずはMATCH関数が何をしているのか、
つぎにOFFSET関数は何をしているのかを考えてください。

この回答へのお礼

大変恐れ入ります。

ありがとうございます。
正式な集計条件には、複数の集計条件あるため補足させてください。

可能でしたら引き続きご教示いただけますと幸いです

お礼日時：2019/04/12 10:58