エクセル関数について質問です。

質問

何回かヤフー知恵袋をつかっておりヤフーでも同じ質問をしたのですが、使い勝手が悪くこちらを利用していこうかと思っています。

という事で質問なのですが、
言葉で上手く表現できないので検索でも思うよう引っかからず
質問させていただきました。

画像の表がベースだとします。
作りたい内容はB列のどの大きさの商品が一番売れているかという順位表です。
例えば 25cmの売上合計は？という指定ではなく
その関数を使えば直で
20cm ￥5500
25cm ￥5190
80cm ￥300
という感じに出てくる関数です。
大きさは小数点以下もあり大変な数になっていますので
1個1個指定したSUMIF関数では駄目だと思い、何かいい感じの関数があれば教えていただきたいです。
ちなみに品名はもう関係なくなっており同じサイズのものを
上記のようにまとめて表示させたい感じです。
なので、知りたいサイズの項目は3種類しかないので3種類を自動で表示。なおかつ合計金額も表示する。
同じ項目を、個別に項目を指定せずに列指定で纏めて合計金額を出すという感じが知りたいです。
順位は特に質問の対象ではありません。
ヤフー知恵袋で質問した時も伝わっていなかったようで
口説くなってしまいましたが、わかりにくかったら申し訳ございません。
よろしくお願いします。

回答No.6

_Kyle · ベストアンサー

もしかして
「サイズの種類が多い上に変動するのでSUMIFの検索条件を全て書き出すのが困難」
といったハナシでしょうか？？

もしそうであれば、
「B列のデータを、重複を許さず漏れなく数式で書き出す」
ことができれば解決するのでは？

例えば、

１．G列の1行目を
　=IF(ROW()>=SUMPRODUCT(--(MATCH(B$1:B$999&"",B$1:B$999&"",0)=ROW(B$1:B$999))),"",INDEX(B:B,1/LARGE(INDEX((MATCH(B$1:B$999&"",B$1:B$999&"",0)=ROW(B$1:B$999))/ROW(B$1:B$999),),ROW())))

２．H列の1行目を
　=IF(G1="","",SUMPRODUCT(--(B$1:B$999=G1),D$1:D$999,E$1:E$999))

として、下方に十分な数だけフィルしておくとか…。

ご参考まで。

お礼

ご回答ありがとうございます。
まさにこの数式で解決できそうです。
ありがとうございました。

回答No.7

tom04

こんばんは！
質問を読む限りにおいては内容が把握出来ていないかもしれません。

結局、商品？の大きさによって単価が変動し、その組み合わせが多いので
困っているという事でしょうか？
もしそうであれば、
↓の画像のようにとりあえず商品と大きさの一覧表を作成して
その行と列の組み合わせで合計金額を算出する方法はどうでしょうか？

当方で勝手に作った表の
D2セル　=IF(COUNTBLANK(A2:C2)>0,"",INDEX($G$4:$K$7,MATCH(A2,$F$4:$F$7,0),MATCH(B2,$G$1:$K$1,1))*C2)
という数式が入っています。
それをオートフィルで下にコピーしています。

以上、参考になれば幸いですが、
的外れの回答なら無視してくださいね。m(__)m

お礼

ご回答ありがとうございます。
知りたい内容はご察しの通りです。
ありがとうございました。

回答No.5

imogasi

＃４ですが
あまりもう言いたくないが、補足していただいても、何がしたいかわかりません。
回答者が何を考えている（考えた）か汲み取って、それに応じて説明や可否を説明や指摘できないようでは質問しても仕方がない。
私の疑問に補足していただいた点が少ないからです。
＞物を作っています
出来ているのなら、質問は要らないのでは。
＞分析結果とグラフ
グラフは、別データで作って、そこに別データを貼り付けると、そのデータでの、グラフになるのではないですか？疑問点は。
こういう疑問点はたくさん盛り込まず、質問を別に分けたほうが良い。
分析とは具体的にどういった表を作るのか？
一番メインの問題は、現データがどんなもので、作りたい表がどんなものかを、回答者の前に示すことです。私の例に対しても、当たっているとも、間違っているとも、なんとも反応がない。
＞列を指定するだけで
20cm ￥5500
25cm ￥5190
80cm ￥300
を繰り返すだけ。
列を指定すると
20cm ￥5500
25cm ￥5190
80cm ￥300
と表示させるとして、指定だけで出るはずがない。何か条件を指定するのでしょうが、そうなると列の指定だけでは無かろう。
他の商品に
20cm ￥5500
25cm ￥5190
80cm ￥300
はないのか、も書いてない。有るならA商品２０ｃｍ＋B商品２０ｃｈの売り上げ高が出ても意味ないでしょう。御社ではそれが意味有るなら注記をすべきです。
A商品２０ｃｍ＋B商品２０ｃｈの売り上げ高が出ればよいなら私の例の２条件が１条件になるだけで、却って易しくなる。
SUMIFで済むかもしれない。
バージョンの記述の件も書いたが、補足してない。これは回答者全員に関係することと思うが。
私は、質問には細かく読んだつもりだが、質問者は、私の回答に対し面と向かってくれてないとおもう。
返事はもう期待しませんが、文章での説明や質問については、判りやすくするにはどうするか良く考えてください

回答No.4

imogasi

こんな質問の調子では何のことだか。
＞何回かヤフー知恵袋をつかっておりヤ・・・質問させていただきました。
ここに書く必要なし。質問文は出来るだけ短く。回答に影響なし。
ーー
＞商品が一番売れているかという順位表
順位は
（１）ソート（並べかえ）
（２）RANK関数
などで判る。質問者にはとりあえず（１）
ーー
今のデータのままで、D列＊E列の計数で並べ替えても思うものが出ないのだろう。それで結局
A列＋B列＋ｃ列の全部か、列A＋B列かA列＋C列
でまとめて、売上高を出せば、その（合計された）計数で、並べれば良いのだろう。
ーー
こういう分類集計は、EXCELでは本来、
ピボットテーブル
集計
などが便利で、そのためにわざわざ作ってある。
こんな便利なものを知らない初心者が、関数関数と言って質問する。
それらを今後は勉強すること。
ーーー
分類をどうする（どの列の項目でやるのか質問には余分なことが書いてあっても、ズバリ書いてない。
ーー
結局条件付き（すなわち分類）集計は、
２００３まではSUMPRODUCT
２００７ではSUMIFSでやるのが定石。
（質問にEXCELバージョンも書いてない。質問の必須項目）
それに添付表は集計後なのか生データなのかはっきりしない。
ーーー
例データ
商品 サイズ 属性 単価 販売
パン S HD 50 13
ケーキ S SF 90 3
パン S SF 50 15 *
饅頭 ｍ SD 80 5
ケーキ L ST 100 8
パン ｍ MD 65 20
饅頭 ｍ SD 80 12
パン L SF 70 17 *
ケーキ ｍ SF 90 9
パン S SF 60 20 *

パン SF 3140（F15セル）
F15の式　=SUMPRODUCT((A2:A11=$A$15)*(C2:C11=$C$15)*(D2:D11)*(E2:E11))
上表の＊の行の集計に当たる。
検算 3140＝=50*15+70*17+60*20
ーー
このほかに、必要なことは、いつも回答で言っているが、
パン　　SF
などの組み合わせを、重複せずに、もれなく、人間がリストアップできるなら（例えば商品種類一覧などが別表に有る場合）は良いが、無ければ、多数ならばこれを導出する方法はわかるのかな。
とりあえず、データーフィルタオプションの「重複するデータは無視する」で出せないか勉強のこと。この場合複数列になるが。

お礼

説明不足と不手際だらけで申し訳ございませんでした。
私が実際にやりたい事は

添付したようなデータを指定した場所に貼り付けるだけで
→分析結果とグラフがフォーマットに自動ではめ込まれて完成
という物を作っています。

ちなにみ知りたいサイズ順位の項目は10種類20種類程度ではなく、
100種類以上あります。（20cm,25cmとか書きましたが実際には1.5mm 2mm 2.7mm 3.2mm....という感じで100種類以上あります。）
ですので、現在集計で使っているSUMIF関数でも可能ですが、
かなり量になってしまいますので、サイズの項目をセル指定でもなく
直接サイズ入力指定でもなく、列を指定するだけで
20cm ￥5500
25cm ￥5190
80cm ￥300
このように表示させる方法があるのか知りたくて質問しました。

ご意見は参考になりました。
ありがとうございました。

回答No.3

KURUMITO

例えばＧ1セルに２０ｃｍ、Ｇ2セルに25ｃｍ、Ｇ3セルに80ｃｍとＢ列と同じすスタイルの文字列を入力します。
Ｈ1セルには次の式を入力してＨ3セルまでオートフィルドラッグします。
=SUMPRODUCT((B$1:B$4=G1)*D$1:D$4*E$1:E$4)
個々のセンチサイズでの合計金額がそれぞれＨ1からＨ3セルに表示されます。
H4セルには=SUM(H1:H3)とすれば総合計が表示されます。

お礼

私の説明不足で申し訳ございませんでした。
データを貼り付けただけで自動で必要なデータを分析するシートを作っていました。
こちらで質問させてもらったサイズ分析以外は項目数が少ないのでSUMIF関数で普通にできたのですが、サイズ分析に関しては小数点以下もあり種類が相当な数になっています。
しかも今後も種類が増えていく予定です。
教えていただいた関数を試してみたのですが、サイズが何種類あるか把握する必要があるような感じでしたので使えませんでした。
一つ勉強になりました。ありがとうございました。

回答No.2

mshr1962

20cm=SUMPRODUCT(($B$1:$B$4="20cm")*1,$D$1:$D$4,$E$1:$E$4)
"20cm"の所をセル参照すれば簡単に集計できます。

お礼

私の説明不足で申し訳ございませんでした。
データを貼り付けただけで自動で必要なデータを分析するシートを作っていました。
こちらで質問させてもらったサイズ分析以外は項目数が少ないのでSUMIF関数で普通にできたのですが、サイズ分析に関しては小数点以下もあり種類が相当な数になっています。
しかも今後も種類が増えていく予定です。
教えていただいた関数では全ての種類を参照しなくてはならないので無理がありました。
ありがとうございました。

回答No.1

sobsob

各項目に小計欄を作ったら駄目なんですか？F列にD*Eで行の小計を出せば例えば別シートに調べたい大きさの3項目を用意しておいてSUMIF関数で一発だと思うのですが・・・

お礼

私の説明不足で申し訳ございませんでした。
データを貼り付けただけで自動で必要なデータを分析するシートを作っていました。
こちらで質問させてもらったサイズ分析以外は項目数が少ないのでSUMIF関数で普通にできたのですが、サイズ分析に関しては小数点以下もあり種類が相当な数になっています。
しかも今後も種類が増えていく予定です。
サイズ分析以外の項目はsobsobさんのアドバイスと同じ感じで集計しています。
ありがとうございました。