SMALL関数について

質問

SMALL関数について

すみません！ファイルがうまく添付できなかったので、再度投稿させていただきます。

売上集計表（表1）
A列 B列 C列
担当者名 合計
件数 ああ 5
金額 ああ ￥1,234,000
件数 いい 8
金額 いい ￥3,547,000
件数 うう 6
金額 うう ￥2,531,000
件数 ええ 6
金額 ええ ￥2,689,000
件数 おお 2
金額 おお ￥1,003,500
件数 かか 0
金額 かか ￥0
件数 きき 0
金額 きき ￥0
件数 くく 3
金額 くく ￥1,983,400
件数 けけ 7
金額 けけ ￥3,065,100
件数 ここ 4
金額 ここ ￥1,064,200

上記の売上集計表を基に、下記の売上金額ワースト5の表を作りたいです。

売上金額ワースト5 （表2）
G列 H列 I列
順位 金額 担当者名
1
2
3
4
5

金額がゼロを除いたワースト表にしたいのですが、どうしてもゼロが出てしまいます。
どうしたらゼロ除外の表になるのでしょうか？

今は
H3=SUMPRODUCT(SMALL(($A$3:$A$22="金額")*($C$3:$C$22>0),G3))
という式を当てはめました。
また、
H3=SUMPRODUCT(SMALL(($A$3:$A$22="金額")*($C$3:$C$22),COUNTIF($C$3:$C$22,0)+G3))
という式も考えたのですが、だめでした。

教えて下さい。よろしくお願いします。

ちなみに担当者名欄（I列）は、H列を参照して式を作りますので考えていただかなくて大丈夫です。

回答No.1

mt2008 · ベストアンサー

SAMLLよりLARGEの方が式が多少短くなるので、LARGEを使って見ました。
まず、0より大きい金額の数を求めます。
これは、
=SUMPRODUCT((A3:A22="金額")*(C3:C22>0))
で、出せます。
この式を何処かのワーク用セル（例：I1）に入れてH3に入れる式を↓にします。
=SUMPRODUCT(LARGE(($A$3:$A$22="金額")*($C$3:$C$22),$I$1-G3+1))

ワーク用セルを使いたくないなら、↓の様にする事で可能です。
=SUMPRODUCT(LARGE(($A$3:$A$22="金額")*($C$3:$C$22),SUMPRODUCT(($A$3:$A$22="金額")*($C$3:$C$22>0))-G3+1))

お礼

なるほど・・・という感じです。助かりました。
作業列が使えないので、下の式を活用させていただきます。
ありがとうございました。

回答No.3

mu2011

一例です。
以下の数式を入力完了時にshift+ctrl+enterキーを同時押下して下さい。
=SMALL(IF(($A$3:$A$22="金額")*($C$3:$C$22>0),$C$3:$C$22,10^15),G3)

お礼

勉強になりました。
まだまだ勉強不足で配列数式がよくわからないので、
今回はANo.1さんの回答を参考にさせていただきました。
ありがとうございました。

回答No.2

keithin

H2に
=SMALL(IF(($A$2:$A$30="金額")*($C$1:$C$29>0),$C$2:$C$30),ROW(H1))
を記入してCtrl+Shift+Enterで入力など。　

参考：
数量を立てているのに金額が未計上なものを拾うため，1:29と2:30の参照を併用しているのを間違えないように。
数量を全く気にしないなら全て1:30で通しても構いません。

お礼

勉強になりました！
勉強不足で、配列数式がまだよく分からないので、
今回はANo.1さんの回答を参考にさせていただきました。
ありがとうございました。