売上金額ワースト5の作成方法について
- 売上集計表を基に、売上金額ワースト5の表を作成する方法について教えてください。
- 現在の式ではゼロを除外することができず、問題が発生しています。
- 担当者名欄（I列）は、H列を参照して式を作成するため、考える必要はありません。
- ベストアンサー
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列を参照して式を作りますので考えていただかなくて大丈夫です。
- yo-mas
- お礼率87% (29/33)
- オフィス系ソフト
- 回答数3
- ありがとう数13
- みんなの回答 （3）
- 専門家の回答
質問者が選んだベストアンサー
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))
その他の回答 (2)
- mu2011
- ベストアンサー率38% (1910/4994)
一例です。 以下の数式を入力完了時にshift+ctrl+enterキーを同時押下して下さい。 =SMALL(IF(($A$3:$A$22="金額")*($C$3:$C$22>0),$C$3:$C$22,10^15),G3)
お礼
勉強になりました。 まだまだ勉強不足で配列数式がよくわからないので、 今回はANo.1さんの回答を参考にさせていただきました。 ありがとうございました。
- keithin
- ベストアンサー率66% (5278/7940)
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さんの回答を参考にさせていただきました。 ありがとうございました。
関連するQ&A
- SMALL関数について
SMALL関数について 下記添付のような表があります。 左の売上集計表を基に、右の売上金額ワースト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列を参照して式を作りますので考えていただかなくて大丈夫です。
- ベストアンサー
- オフィス系ソフト
- エクセルの関数（検索・累計の算出）
例えば A列 B列 C列 日付 売上 氏名 7/1 10000 ○男 7/1 15000 △美 7/1 10000 ○男 7/2 20000 ○男 7/2 10000 ○男 と、いう表があるとします。 そこで、以下の２点を行いたいのです。 １．7/1の○男の売上件数 ２．7/1の○男の売上金額 ・・・２つの条件に一致する件数と金額の合計を出したいのです。 COUNTIFの１つの条件のやり方しか知りません。 どなたか、知って見える方がいらっしゃったら早急に教えてください!!! (;_;)( ;_)( ;)( )(; )(_; )(;_;)(T-T)
- ベストアンサー
- オフィス系ソフト
- エクセル関数SUMPRODUCTについて
エクセル2000です。 A列とB列の和にC列を乗じたものの合計は、 =SUMPRODUCT(A1:A10,$C$1:$C$10)+SUMPRODUCT(B1:B10,$C$1:$C$10) または =SUM(SUMPRODUCT(A1:A10,$C$1:$C$10),SUMPRODUCT(B1:B10,$C$1:$C$10)) のような長ったらしいものになるのでしょうか？ =SUMPRODUCT((A1:B10)*C1:C10) は、表中に文字列が入る場合があるのでエラーになります。 ご教示いただければ幸いです。
- ベストアンサー
- オフィス系ソフト
- 1列おきのワースト平均3を計算する関数
よろしくお願いします。 下記の様な表でＬ列の求め方は分かりますがＭ列の求め方は次の様な関数を使うとAVERAGE(SMALL(B3:J3,{1,2,3}))とするとC,E,G,I,Kの人数まで計算されます。点数だけのワースト3を求める方法をどうか教えて下さい。 A B C D E F G H I J K L M 1 国語 英語 数学 社会 物理 平均 ワースト3 2 クラス 点 人 点 人 点 人 点 人 点 人 点 平均 3 a組 70 33 75 31 74 32 73 32 70 30 4 b組 80 30 79 28 76 33 75 31 72 32 5 c組 85 31 82 30 75 32 77 30 74 31 6 d組 90 32 80 30 77 31 79 29 73 31
- ベストアンサー
- オフィス系ソフト
- OpenOffice.orgCalcでの関数
会社のPCで売上の集計ファイルを作成しているのですがエクセルではなくOpenOffice.orgCalcを使用しています。そこで２つの条件を満たす場合の値の合計を出した集計表を作りたいのですが式が作れません。分かる方いらっしゃいましたら教えてください。 （例） A B C 1 担当 区分 金額 2 佐藤 １ 10000 3 佐藤 ２ 5000 4 佐藤 １ 10000 5 鈴木 １ 10000 こういった場合の、「担当が佐藤の時、区分が1の場合の合計」を出すといった式になるのですが・・・ ちなみにSUMIFSは使えません。 エクセルの方では=SUMPRODUCT((A:2A5="佐藤")*1,(B2:B5=1)*1,C2:C5)で出すことが出来たのですがOpenOffice.orgCalcだとうまくいきませんでした。=と*の使い方が違うのでしょうか？それともそもそもOpenOffice.orgCalcでは使えない式なのでしょうか？ 関数の知識はあまりありません。上記の方法でなく別なやり方もあるのであれば教えていただけるとありがたいです。 拙い説明ですが組みとっていただければ助かります。 よろしくお願い致します。
- ベストアンサー
- オフィス系ソフト
- 関数でできるでしょうか？
エクセルの関数で次のようなことができるでしょうか？ A B C D E F G H 1 26 ←年齢 2 20 21 22 23 24 25 26 27 ←年齢表 3 25 25 28 29 30 33 33 36 ←金額 A1セルを見て、2行目から合致する年齢の列を見つけて、3行目のA列から合致した年齢の列（この例の場合G列）までを足算（この場合２０３）したいのですが関数だけでできますでしょうか？ すいませんが、よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- SUMPRODUCT関数について
こんにちわ！ 以前にSUMPRODUCT関数を教えていただいたのですが、またわからないポイントが出てきてしまいましてどなたかお教えください。 【例】 A列 B列 C列 1 担当者 オーダー日 カテゴリ 2 A子 2007/9/5 b a 3 B美 2007/9/6 ｃｄ 4 C男 2007/9/7 ｃｇ 5 D輔 2007/9/7 ｃｖ 6 E司 2007/9/5 ｃa 7 A子 2007/9/7 ｃｖ 8 ・ ・ ・ 9 ・ ・ ・ そして別の場所＜Ａ12＞に 昨日のオーダー ＝TODAY()-1 という計算用の式を作成し、 次のような式を作成しました。 【式1】 SUMPRODUCT(($A$3:$A$10="A子")*((TEXT($B$3:$B$10,"ymd")=TEXT($A$12,"ymd"))*($C$3:$C$10="ｃｖ"))) さらに検索したい項目があり、最初次のように式をまず作成してみました。 【式2】 ＝SUMPRODUCT(($A$3:$A$10=""A子"")*((TEXT($B$3:$B$10,""ymd"")=TEXT($A$12,""ymd""))*($C$3:$C$10=""ｃｖ""))) +SUMPRODUCT(($A$3:$A$10=""A子"")*((TEXT($B$3:$B$10,""ymd"")=TEXT($A$12,""ymd""))*($C$3:$C$10=""ca""))) +SUMPRODUCT(($A$3:$A$10=""A子"")*((TEXT($B$3:$B$10,""ymd"")=TEXT($A$12,""ymd""))*($C$3:$C$10=""ba"")))" 『長い！』と思い、この式を短くしようと、 【式3】 SUMPRODUCT(($A$3:$A$10="A子")*((TEXT($B$3:$B$10,"ymd")=TEXT($A$12,"ymd"))*(OR(($C$3:$C$10="ｃｖ"),($C$3:$C$10="ｃa"),($C$3:$C$10="ba"))))) という式を作成したところ全然ダメでした。 【式2】を簡素化することは可能でしょうか？ どうぞ宜しくお願いします。 余談として・・・ 更に、もし可能であれば、他の関数と組み合わせるにはどこに気を付けたらよいのでしょうか？たとえばＡＮＤ・ＩＦ・ＶＬＯＯＫＵＰなど ・・・こちらはただの興味ですのでご返答は無くてもかまいません。
- ベストアンサー
- オフィス系ソフト
- ExcelのSMALL関数について
ExcelのB列（B2:B50）にフラグ「０」または「１」が入力されています。 フラグ「０」が入力されている行番号を関数を使用して取得したいと思っています。 B列のフラグは順次更新されており、フラグ「０」の個数は複数あります。 下記の関数式（以下多めにオートフィル）で上記のことができるようになったのですが、 =IF(COUNTIF(B$2:B$50,0)<ROW(A1),"",SMALL(INDEX((B$2:B$50<>0)*10^5+ROW(B$2:B$50),),ROW(A1))) SMALL関数式の部分↓↓の意味がよくわかりません。 SMALL(INDEX((B$2:B$50<>0)*10^5+ROW(B$2:B$50),),ROW(A1)) どなたかご教授いただけないでしょうか。
- ベストアンサー
- その他(業務ソフトウェア)
- エクセルの関数を教えて下さい。
顧客管理表を作成しており、全顧客のうち、実際に売上につながっている顧客数が知りたいです。 A列に顧客名（重複している顧客複数あり） B列に対象サービス C列に売上 A列 B列 C列 顧客a eサービス 3000円 顧客b fサービス 2000円 顧客c gサービス 0円 顧客d hサービス 1000円 顧客a fサービス 2000円 顧客a gサービス 1000円
- 締切済み
- Excel（エクセル）
お礼
なるほど・・・という感じです。助かりました。 作業列が使えないので、下の式を活用させていただきます。 ありがとうございました。