どの価格帯の売上が多いか・担当者別

質問

A列に担当者
B列にクライアント
C列に売上が入った、縦書きのExcel一覧表があります。バージョンは2007です。

10,000円から2,000,000円までの売上の項目の種類があるとして、Aさんは何万円くらいの売上が何パーセントなのか、Bさんはどうか、と比較したいと思います。

50,0000円刻み、または100,000円刻みで担当者毎に各売上の比率を出すには、どのようにすればよいでしょうか。

例えば
Aさん
10000～100000　　25％
100000～200000　　15％
200000～300000　　9％
・
・
・
・
1900000～2000000　１％

のように集計したいのです。

回答No.2

kagakusuki · ベストアンサー

>ところで、追加の質問なのですが、価格帯毎の担当者別の件数を出すにはどうすればよいでしょうか。

　それでしたらSheet２のB3セルに入力する数式から

/COUNTIF(Sheet1!$A:$A,$A3)

の部分を削除した、次の様な数式にされると宜しいと思います。

=IF(COUNTIF(Sheet1!$A:$A,$A3),COUNTIFS(Sheet1!$A:$A,$A3,Sheet1!$C:$C,">="&B$1,Sheet1!$C:$C,"<"&IF(ISNUMBER(B$2),B$2,9E+307)),"")

　それから、ANo.1で書き忘れておりましたが、Sheet２のB3セルのコピーを行う前に、Sheet２のB3セルの書式設定の表示形式を[パーセンテージ]にして下さい。
　尚、セルの書式設定を[パーセンテージ]とするのは、あくまでもパーセント表示が必要となるANo.1においての話で、「価格帯毎の担当者別の件数を出す」場合には、Sheet２のB3セルの書式設定の表示形式は[標準]のままで結構です。

お礼

ありがとうございます。

完全に全て解決しました。

しかし、このような複雑な数式をどうやって考え出すのでしょうか・・・。

回答No.1

kagakusuki

今仮に、元データの表がSheet1に存在していて、その中の「担当」という項目名が入力されているセルがA1セルであるものとして、Sheet２に「担当者毎に各売上の比率を表した表」を表示するものとします。

【方法その１】（推奨）
　まず、Sheet２のB1セルの書式設定を[ユーザー定義]の

¥#,##0"以上"

にして下さい。
　次に、Sheet２のB2セルの書式設定を[ユーザー定義]の

¥#,##0"未満"

にして下さい。
　次に、Sheet２のB1～B2のセル範囲をコピーして、Sheet２のC1～V2のセル範囲に貼り付けて下さい。
　次に、Sheet２の
B1セルに　　10000　　　B2セルに　　100000
C1セルに　　100000　　　C2セルに　　200000
D1セルに　　200000　　　D2セルに　　300000
　　・　　　　　　　・　　　　　　　・　　　　　　　・
　　・　　　　　　　・　　　　　　　・　　　　　　　・
　　・　　　　　　　・　　　　　　　・　　　　　　　・
U1セルに　　1900000　　　U2セルに　　2000000
V1セルに　　2000000　　　V2セルは空欄
という具合に入力して下さい。
　次に、Sheet２のA3以下に各担当者名を入力して下さい。
　次に、Sheet２のB3セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet1!$A:$A,$A3),COUNTIFS(Sheet1!$A:$A,$A3,Sheet1!$C:$C,">="&B$1,Sheet1!$C:$C,"<"&IF(ISNUMBER(B$2),B$2,9E+307))/COUNTIF(Sheet1!$A:$A,$A3),"")

　次に、Sheet２のB3セルをコピーして、Sheet２のC3～V3のセル範囲に貼り付けて下さい。
　次に、Sheet２のB3～V3のセル範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　これで、Sheet２に「担当者毎に各売上の比率を表した表」が自動的に表示されます。

【方法その2】Excel2007よりも前のバージョンでも可能だが、計算処理が重くなる方法
　まず、Sheet２のB1セルの書式設定を[ユーザー定義]の

¥#,##0"以上"

にして下さい。
　次に、Sheet２のB2セルの書式設定を[ユーザー定義]の

¥#,##0"未満"

にして下さい。
　次に、Sheet２のB1～B2のセル範囲をコピーして、Sheet２のC1～V2のセル範囲に貼り付けて下さい。
　次に、Sheet２の
B1セルに　　10000　　　B2セルに　　100000
C1セルに　　100000　　　C2セルに　　200000
D1セルに　　200000　　　D2セルに　　300000
　　・　　　　　　　・　　　　　　　・　　　　　　　・
　　・　　　　　　　・　　　　　　　・　　　　　　　・
　　・　　　　　　　・　　　　　　　・　　　　　　　・
U1セルに　　1900000　　　U2セルに　　2000000
V1セルに　　2000000　　　V2セルは空欄
という具合に入力して下さい。
　次に、Sheet２のA3以下に各担当者名を入力して下さい。（ここまでは【方法その１】と共通）
　次に、Sheet２のB3セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet1!$A:$A,$A3),SUMPRODUCT((Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9E+307,Sheet1!$C:$C))=$A3)*(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9E+307,Sheet1!$C:$C))>=B$1)*(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9E+307,Sheet1!$C:$C))<IF(ISNUMBER(B$2),B$2,9E+307)))/COUNTIF(Sheet1!$A:$A,$A3),"")

　次に、Sheet２のB3セルをコピーして、Sheet２のC3～V3のセル範囲に貼り付けて下さい。
　次に、Sheet２のB3～V3のセル範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　これで、Sheet２に「担当者毎に各売上の比率を表した表」が自動的に表示されます。

【方法その3】Excel2007よりも前のバージョンでも可能だが、作業列が必要となる方法
　まず、Sheet２のB1セルの書式設定を[ユーザー定義]の

¥#,##0"以上"

にして下さい。
　次に、Sheet２のB2セルの書式設定を[ユーザー定義]の

¥#,##0"未満"

にして下さい。
　次に、Sheet２のB1～B2のセル範囲をコピーして、Sheet２のC1～V2のセル範囲に貼り付けて下さい。
　次に、Sheet２の
B1セルに　　10000　　　B2セルに　　100000
C1セルに　　100000　　　C2セルに　　200000
D1セルに　　200000　　　D2セルに　　300000
　　・　　　　　　　・　　　　　　　・　　　　　　　・
　　・　　　　　　　・　　　　　　　・　　　　　　　・
　　・　　　　　　　・　　　　　　　・　　　　　　　・
U1セルに　　1900000　　　U2セルに　　2000000
V1セルに　　2000000　　　V2セルは空欄
という具合に入力して下さい。
　次に、Sheet２のA3以下に各担当者名を入力して下さい。（ここまでは【方法その１】と共通）

　次に、適当な使用していないシート（ここでは仮にSheet3とします）のA2セルに次の数式を入力して下さい。

=IF(AND(INDEX(Sheet1!$A:$A,ROW())<>"",INDEX(Sheet1!$C:$C,ROW())>=Sheet2!$B$1),INDEX(Sheet1!$A:$A,ROW())&"■"&MATCH(INDEX(Sheet1!$C:$C,ROW()),Sheet2!$B$1:$V$1),"")

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼付けて下さい。

　次に、Sheet２のB3セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet1!$A:$A,$A3),COUNTIF(Sheet3!$A:$A,$A3&"■"&COLUMNS($B:B))/COUNTIF(Sheet1!$A:$A,$A3),"")

　次に、Sheet２のB3セルをコピーして、Sheet２のC3～V3のセル範囲に貼り付けて下さい。
　次に、Sheet２のB3～V3のセル範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　これで、Sheet２に「担当者毎に各売上の比率を表した表」が自動的に表示されます。

補足

御回答ありがとうございます。

お示しいただいた通りで実行できました。
とても助かりました。

ところで、追加の質問なのですが、価格帯毎の担当者別の件数を出すにはどうすればよいでしょうか。

　　　　10000～100000　100000～200000　200000～300000

Ａさん　　　5件　　　　　　2件　　　　　　10件

というような感じです。

よろしくお願いいたします。