Excel 2007 複数条件での件数カウント２

質問

Excel2007で作業をしており、添付のように各部署ごとに件数をカウントしたいです。
空欄のセルに件数が抽出されるような式をご教示いただけますと幸いです。

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

回答No.1

kagakusuki · ベストアンサー

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

　まず、Sheet2のC3セル（「営業」の表中において「A2,A3,A4」と入力されている箇所の直下のセル）に次の関数を入力して下さい。

=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のC3セルをコピーして、Sheet2のC3～D7の範囲に貼り付けて下さい。
　次に、Sheet2のC3～D7の範囲をコピーして、Sheet2のC10～D14の範囲、Sheet2のC17～D21の範囲、及びSheet2のC24～D28の範囲に、それぞれ貼り付けて下さい。

　以上です。

　因みに、上記の関数では、「A2,A3,A4」や「A5,A6,A7,A8」等の条件は、対象となるレベルが入力されているセルの内容を変更する事で、各部署毎に個別に変更する事が出来ます。

回答No.2

KURUMITO

作業列を作って対応します。
部署やレベルに文字列などは数値化して評価することにします。
シート1が元の表でA2セルから下方に部署名が、B2セルから下方にレベルが、C2セルから下方に点数が入力されているとして例えばE2セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,ROUNDDOWN(MAX(E$1:E1),-3)+1000,ROUNDDOWN(INDEX(E$1:E1,MATCH(A2,A:A,0)),-3))+IF(OR(B2="A2",B2="A3",B2="A4"),100,200)+C2)

シート2にお求めの表を表示させるとしてA1セルに営業、A2セルからD2セルにかけて、およびA3セルからB7セルにはお示しの文字列や数値が入力されているとします。
C3セルには次の式を入力してD3セルまで℃ラグコピーしたのちに7行目まで下方にドラッグコピーします。

=COUNTIF(Sheet1!$E:$E,">="&(ROUNDDOWN(INDEX(Sheet1!$E:$E,MATCH($A$1,Sheet1!$A:$A,0)),-3)+COLUMN(A1)*100+$A3))-COUNTIF(Sheet1!$E:$E,">="&((ROUNDDOWN(INDEX(Sheet1!$E:$E,MATCH($A$1,Sheet1!$A:$A,0)),-3)+COLUMN(A1)*100+$B3)))

その後は例えばA1セルからD7セルの範囲を選択してコピーし、A8セルに貼り付けます。
C10セルの式を次のように変更してD10セルまでドラッグコピーしたのちに14行目まで下方にもドラッグコピーします。

=COUNTIF(Sheet1!$E:$E,">="&(ROUNDDOWN(INDEX(Sheet1!$E:$E,MATCH($A$8,Sheet1!$A:$A,0)),-3)+COLUMN(A8)*100+$A10))-COUNTIF(Sheet1!$E:$E,">="&((ROUNDDOWN(INDEX(Sheet1!$E:$E,MATCH($A$8,Sheet1!$A:$A,0)),-3)+COLUMN(A8)*100+$B10)))

C3セルとC10セルへの入力の式では$A$1を$A$8に変更すればよいでしょう。
また、その表を開発にするのでしたらA8セルの文字列を開発に変更します。

同様にして他の部署の表も作成することができますね。
文字列などを数値化して評価することで表の作成を簡単にすることができますね。