Excelでの集計 複数条件があります

質問

Excel 2007で作業していますが、下記のようにいくつか条件付きで件数、%、平均を算出する必要があります。
条件が複数あり、例えば営業部門且つ点数が9.1以上の件数は？、IT部門且つ点数が7.5以上9未満は%存在しているか？、経理部門且つA05,A6,A07,A08レベルの平均点は？

という具合です。

黄色ハイライト？に式がそれぞれ入ると思いますが、ご教示いただけますと幸いです。

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

回答No.3

kagakusuki · ベストアンサー

今仮に、「担当部署」と入力されているセルがSheet1のA1セルで、Sheet2において「営業」と入力されているのがA1セルであるものとします。
　又、「未満」欄の最上段に10と入力されていますが、10未満としたのでは、点数が10.0の場合はカウントされない事になります。
　ですから、質問欄の添付画像中では10と入力されているセルを空欄とした上で、「未満」欄が空欄の場合には、その左隣りの「～以上の」条件に合致するものは全てカウントする事が出来る様に、工夫するものとします。

　まず、各部署別の表ごとに、表中の最下段の行のA列のセル（A8セル、A16セル、A24セル、A32セル）に、「計」或いは「合計」等の様に「計」の字を含んでいる文字列を、必ず入力して下さい。
　次に、各部署別の表ごとに、表の枠で囲まれた部分の内の最上段の行のA列のセル（A2セル、A10セル、A18セル、A26セル）に、「以上」等の何らかの文字列を、必ず入力して下さい。
　次に、Sheet2のC3セル（「営業」の表中において「A-,A02,A03,A04」と入力されている箇所の直下のセル）に次の関数を入力して下さい。

=IF(COUNT($A3,$B3),SUMPRODUCT(COUNTIFS(Sheet1!$A:$A,INDEX($A:$A,MATCH("*?",$A$1:$A3,-1)-1),Sheet1!$B:$B,REPLACE(LEFT(","&INDEX(C:C,MATCH("*?",$A$1:$A3,-1)),FIND(CHAR(1),SUBSTITUTE(INDEX(C:C,MATCH("*?",$A$1:$A3,-1))&",",",",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(INDEX(C:C,MATCH("*?",$A$1:$A3,-1)))-LEN(SUBSTITUTE(INDEX(C:C,MATCH("*?",$A$1:$A3,-1)),",",))+1))))),1,FIND(CHAR(1),SUBSTITUTE(","&INDEX(C:C,MATCH("*?",$A$1:$A3,-1)),",",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(INDEX(C:C,MATCH("*?",$A$1:$A3,-1)))-LEN(SUBSTITUTE(INDEX(C:C,MATCH("*?",$A$1:$A3,-1)),",",))+1)))),),Sheet1!$C:$C,IF(ISNUMBER($A3),">="&$A3,"<>"),Sheet1!$C:$C,IF(ISNUMBER($B3),"<"&$B3,"<>"))*1),"")

　次に、Sheet2のD3セルに次の関数を入力して下さい。

=IF(ISNUMBER(C3),IF(C3=0,0,C3/INDEX(C:C,MATCH("*計*",INDEX($A:$A,ROW()):INDEX($A:$A,MATCH("*?",$A:$A,-1)),0)+ROW()-1)),"")
C8=SUM(INDEX(C:C,MATCH("*?",$A$1:INDEX($A:$A,ROW()-1),-1)+1):INDEX(C:C,ROW()-1))
D8=IF(C8=0,"-",SUM(INDEX(D:D,MATCH("*?",$A$1:INDEX($A:$A,ROW()-1),-1)+1):INDEX(D:D,ROW()-1)))
D1=IF(ISNUMBER(1/INDEX(C:C,MATCH("*計*",INDEX($A:$A,ROW()):INDEX($A:$A,MATCH("*?",$A:$A,-1)),0)+ROW()-1)),SUMPRODUCT(SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,INDEX($A:$A,MATCH("*?",$A$1:$A3,-1)-1),Sheet1!$B:$B,REPLACE(LEFT(","&C2,FIND(CHAR(1),SUBSTITUTE(C2&",",",",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(C2)-LEN(SUBSTITUTE(C2,",",))+1))))),1,FIND(CHAR(1),SUBSTITUTE(","&C2,",",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(C2)-LEN(SUBSTITUTE(C2,",",))+1)))),))*1)/INDEX(C:C,MATCH("*計*",INDEX($A:$A,ROW()):INDEX($A:$A,MATCH("*?",$A:$A,-1)),0)+ROW()-1),"-")

　次に、Sheet2のD3セルの書式設定の表示形式の分類を[パーセンテージ]とし、小数点以下の桁数を[2]にして下さい。
　次に、Sheet2のC3～D3の範囲をコピーして、Sheet2のC4～D7の範囲に貼り付けて下さい。

　次に、Sheet2のC8セルに次の関数を入力して下さい。

=SUM(INDEX(C:C,MATCH("*?",$A$1:INDEX($A:$A,ROW()-1),-1)+1):INDEX(C:C,ROW()-1))

　次に、Sheet2のD8セルに次の関数を入力して下さい。

=IF(C8=0,"-",SUM(INDEX(D:D,MATCH("*?",$A$1:INDEX($A:$A,ROW()-1),-1)+1):INDEX(D:D,ROW()-1)))

　次に、Sheet2のD1セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/INDEX(C:C,MATCH("*計*",INDEX($A:$A,ROW()):INDEX($A:$A,MATCH("*?",$A:$A,-1)),0)+ROW()-1)),SUMPRODUCT(SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,INDEX($A:$A,MATCH("*?",$A$1:$A3,-1)-1),Sheet1!$B:$B,REPLACE(LEFT(","&C2,FIND(CHAR(1),SUBSTITUTE(C2&",",",",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(C2)-LEN(SUBSTITUTE(C2,",",))+1))))),1,FIND(CHAR(1),SUBSTITUTE(","&C2,",",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(C2)-LEN(SUBSTITUTE(C2,",",))+1)))),))*1)/INDEX(C:C,MATCH("*計*",INDEX($A:$A,ROW()):INDEX($A:$A,MATCH("*?",$A:$A,-1)),0)+ROW()-1),"-")

　次に、Sheet2のD1セルの書式設定の表示形式の分類を[数値]とし、小数点以下の桁数を[3]にして下さい。

　次に、Sheet2のC1～D8の範囲をコピーして、Sheet2のE1～F8の範囲に貼り付けて下さい。
　次に、Sheet2のC2セルとE2セルに、それぞれ集計対象となるレベルの名称を、「,」（半角のカンマ）で区切って入力して下さい。
　次に、Sheet2のA3～B7の各セルに、条件となる点数の範囲の上限値と下限値を入力して下さい。
　但し、「未満」欄の部署内で最上段の行にあるセルだけは、数値を入力しないで、空欄のままとして下さい。
　又、「以上」欄の部署内で最下段の行にあるセルには、必ずしも数値を入れなくとも構いません。

　次に、Sheet2のA1～F8の範囲をコピーして、その下に、順次貼り付けて下さい。（各票の間隔は、8行毎でなくとも構いません）
　そして最後に、各表の「以上」と入力されているセルの1つ上のセルに、各々の部署名を入力して下さい。

　以上です。

　因みに、集計対象となるレベルの名称や、条件となる点数の範囲の上限値と下限値は、それらの値が入力されている各セルの値を入力しなおす事で、各部署ごとに個別に設定を変更する事も出来ますし、条件範囲の行数や、集計対象となるレベルの個数も、個別に変更する事が出来ます。

お礼

kagakusuki様、

ご回答いただきありがとうございました。
実際のファイルはデータの項目も量も多かったのでいただいたFomulaにもかかわらず作成に手間取り、その後出張などで返答が遅くなてしまい誠に申し訳ございませんでした。

肝心のTaskに関しては、アドバイスを基に無事に完成いたしました。

改めましてありがとうございました。

回答No.2

tom04

こんにちは！
昨日投稿した内容より結構ややこしくなっていますね！

とりあえずやってみました。
今回はオートフィルで！というよりそれぞれの表に数式を入れていく必要があると思います。

↓の画像で元データ（左側がSheet1）をSheet2に表示するようにしてみました。
画像通りの配置とします。

Sheet2のC4セルに
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A$1)*(ISNUMBER(FIND(Sheet1!$B$1:$B$100,$C$2)))*(Sheet1!$C$1:$C$100>=$A4)*(Sheet1!$C$1:$C$100<$B4))

D4セルに
=C4/COUNTIF(Sheet1!A:A,A$1)

E4セルに
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A$1)*(ISNUMBER(FIND(Sheet1!$B$1:$B$100,$E$2)))*(Sheet1!$C$1:$C$100>=$A4)*(Sheet1!$C$1:$C$100<$B4))

F4セルに
=E4/COUNTIF(Sheet1!A:A,$A$1)

とそれぞれ入力し、C4～F4セルを範囲指定　→　F4セルのフィルハンドルで下へコピー！

D1セルは
=SUMPRODUCT((Sheet1!A1:A100=A1)*(ISNUMBER(FIND(Sheet1!B1:B100,C2))),Sheet1!C1:C100)/SUM(C4:C8)

F1セルは
=SUMPRODUCT((Sheet1!A1:A100=A1)*(ISNUMBER(FIND(Sheet1!B1:B100,E2))),Sheet1!C1:C100)/SUM(E4:E8)

という数式を入れると、「営業」の表は完成です。

次に「開発」の表の数式だけを載せておきます。
C13セルに
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A$10)*(ISNUMBER(FIND(Sheet1!$B$1:$B$100,$C$11)))*(Sheet1!$C$1:$C$100>=$A13)*(Sheet1!$C$1:$C$100<$B13))

D13セルに
=C13/COUNTIF(Sheet1!A:A,A$10)

E13セルに
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A$10)*(ISNUMBER(FIND(Sheet1!$B$1:$B$100,$E$11)))*(Sheet1!$C$1:$C$100>=$A13)*(Sheet1!$C$1:$C$100<$B13))

F13セルに
=E13/COUNTIF(Sheet1!A:A,$A$10)

D10セルに
=SUMPRODUCT((Sheet1!A1:A100=A10)*(ISNUMBER(FIND(Sheet1!B1:B100,C11))),Sheet1!C1:C100)/SUM(C13:C17)

F10セルに
=SUMPRODUCT((Sheet1!A1:A100=A10)*(ISNUMBER(FIND(Sheet1!B1:B100,E11))),Sheet1!C1:C100)/SUM(E13:E17)

といった感じです。

この操作を各部署ごとに行う必要があります。

長々と書きましたが、参考になりますかね？m（_ _）m

回答No.1

K Kazz（@JazzCorp）

そのラベルのテ～ギはナンダァ～？？
ダシオシミしてねぇ～で自分のオツムで考えた式くらい書いとけ！！