[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『最安値、次に安い価格、およびそれぞれの合計数を求めたいです。』(くるみ)
商品名-価格-個数
りんご-100円-3個
みかん-80円-1個
りんご-70円-2個
りんご-70円-1個
ぶどう-80円-2個
ぶどう-90円-2個
このようなデータベースがあります。同じ商品でも価格が違うことがあります。
そして下記のような表があり、
商品名
りんご
みかん
ぶどう
関数を使って下記のような表を完成させたいです。
商品名-1番安い価格-その合計数-2番目に安い価格-その合計数-3番目に安い価格-その合計数
りんご-70円-3個-100円-3個
みかん80円-1個
ぶどう80円-2個-90円-2個
分かりにくかったらすみません。
ピボットテーブルでできないかやってみたのですが、実際はデータがこの100倍くらいあるので、ちょっとできませんでした。
私のほしい表を関数を使って何とか作ることはできますでしょうか?
よろしくお願いいたします。
< 使用 Excel:Excel2016、使用 OS:Windows10 >
例表はセル位置が分かるように書いてください。
元表がA列〜C列だとして
E F G H I J K 1 2 りんご 70 3 100 3 3 みかん 80 1 4 ぶどう 80 2 90 2
F2 =IFERROR(SMALL(IF($A$2:$A$100=E2,$B$2:$B$100),1),"")
H2 =IFERROR(SMALL(IF(($A$2:$A$100=E2)*($B$2:$B$100>F2),$B$2:$B$100),1),"")
J2 =IFERROR(SMALL(IF(($A$2:$A$100=E2)*($B$2:$B$100>H2),$B$2:$B$100),1),"")
すべて Ctrl+Shift+Enter で確定し、下コピー
合計数は SUMIFS で
G2 =IF(F2="","",SUMIFS($C$2:$C$100,$A$2:$A$100,$E2,$B$2:$B$100,F2))
のように(もちろん普通に Enter で確定)
以上、参考まで (笑) 2018/10/10(水) 10:16
2列目から数値が入力されていると仮定して
作業列を後半2行付け足します
D列に=IF(SUMPRODUCT((($A$2:A2=A2)*($B$2:B2=B2)))=1,B2,"")
E列に=SUMPRODUCT((A:A=A1)*(D:D<D1))+1+IF(A1="りんご",0,IF(A1="みかん",9999,IF(A1="ぶどう",99999)))
これでE列に安い順のランクが出るので、
あとは
商品名(F列) 1(G列)一位個数(H列)2(I列)二位個数(J列)
F列は りんご みかん ぶどう を入力
G列は =IFERROR(INDEX($B:$B,MATCH(G1,$E:$E,0)),"") 【※G1に1を入力しておいて下さい】
=IFERROR(INDEX($B:$B,MATCH(G1+9999,$E:$E,0)),"")
=IFERROR(INDEX($B:$B,MATCH(G1+99999,$E:$E,0)),"")
H列は =SUMIFS($C:$C,$A:$A,$F2,$B:$B,G2)
=SUMIFS($C:$C,$A:$A,$F3,$B:$B,G3)
=SUMIFS($C:$C,$A:$A,$F4,$B:$B,G4)
I列とJ列以降は GH列をコピーして下さい 順位の列の表題は数値で入力するのを忘れずに
私はCtrl+Shift+Enterが扱いづらく苦手なので省いて出来るように考えました。
これで望む表が作れるかと思います。
真横にくっつける形で例を制作しましたが、実際に作成したい場所に合わせて自由に中身は変えて下さい。
既に回答されていますが、折角途中まで記載していたのでご参考までに載せときます
(nokko) 2018/10/10(水) 10:24
2016ということは、もしかして MINIFS 関数が使えます?
以上、確認だけ (笑) 2018/10/10(水) 10:27
金額同じで個数が違うとどちらを「安い」と判断するの? どっちゃでもOK? (GobGob) 2018/10/10(水) 11:50
あー、個数は合計するんすね。。。 勘違いでした。 (GobGob) 2018/10/10(水) 11:51
(笑)さん表をお借りしまして、Enterのみ確定案
F2 =IFERROR(AGGREGATE(15,6,B$2:B$7/(A$2:A$7=E2),1),"") G2 =IF(F2="","",SUMIFS($C:$C,$A:$A,$E2,$B:$B,F2)) H2 =IFERROR(AGGREGATE(15,6,$B$2:$B$7/(($A$2:$A$7=$E2)*($B$2:$B$7>F2)),1),"")
G2:H2を右へコピー。
2行目を下へコピー。 (GobGob) 2018/10/10(水) 12:10
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.