質問: どの価格帯の売上が多いか・担当者別 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 1% のように集計したいのです。
回答: 今仮に、元データの表がsheet1に存在していて、その中の「担当」という項目名が入力されているセルがa1セルであるものとして、sheet2に「担当者毎に各売上の比率を表した表」を表示するものとします。 【方法その1】(推奨) まず、sheet2のb1セルの書式設定を[ユーザー定義]の ¥#,##0"以上" にして下さい。 次に、sheet2のb2セルの書式設定を[ユーザー定義]の ¥#,##0"未満" にして下さい。 次に、sheet2のb1~b2のセル範囲をコピーして、sheet2のc1~v2のセル範囲に貼り付けて下さい。 次に、sheet2の b1セルに 10000 b2セルに 100000 c1セルに 100000 c2セルに 200000 d1セルに 200000 d2セルに 300000 ・ ・ ・ ・ ・ ・ ・ ・ ・ ・ ・ ・ u1セルに 1900000 u2セルに 2000000 v1セルに 2000000 v2セルは空欄 という具合に入力して下さい。 次に、sheet2のa3以下に各担当者名を入力して下さい。 次に、sheet2の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),"") 次に、sheet2のb3セルをコピーして、sheet2のc3~v3のセル範囲に貼り付けて下さい。 次に、sheet2のb3~v3のセル範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。 これで、sheet2に「担当者毎に各売上の比率を表した表」が自動的に表示されます。 【方法その2】excel2007よりも前のバージョンでも可能だが、計算処理が重くなる方法 まず、sheet2のb1セルの書式設定を[ユーザー定義]の ¥#,##0"以上" にして下さい。 次に、sheet2のb2セルの書式設定を[ユーザー定義]の ¥#,##0"未満" にして下さい。 次に、sheet2のb1~b2のセル範囲をコピーして、sheet2のc1~v2のセル範囲に貼り付けて下さい。 次に、sheet2の b1セルに 10000 b2セルに 100000 c1セルに 100000 c2セルに 200000 d1セルに 200000 d2セルに 300000 ・ ・ ・ ・ ・ ・ ・ ・ ・ ・ ・ ・ u1セルに 1900000 u2セルに 2000000 v1セルに 2000000 v2セルは空欄 という具合に入力して下さい。 次に、sheet2のa3以下に各担当者名を入力して下さい。(ここまでは【方法その1】と共通) 次に、sheet2の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))"",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以下に貼付けて下さい。 次に、sheet2のb3セルに次の数式を入力して下さい。 =if(countif(sheet1!$a:$a,$a3),countif(sheet3!$a:$a,$a3&"■"&columns($b:b))/countif(sheet1!$a:$a,$a3),"") 次に、sheet2のb3セルをコピーして、sheet2のc3~v3のセル範囲に貼り付けて下さい。 次に、sheet2のb3~v3のセル範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。 これで、sheet2に「担当者毎に各売上の比率を表した表」が自動的に表示されます。