シートが違う件数の集計等

質問

閲覧いただきましてありがとうございます。

非常に複雑なエクセル関数になります（少なくとも私には難しいです）
分かる所だけでも良いので教えて下さると助かります。

表が上手く添付出来ていれば良いのですが、見ていただければわかりますが、上部に営業件数や約束数、契約件数などをまとめた表があり、その下には氏名毎にそれぞれが集計されたものを自動で計算できるようにしたいのです。

Ａ表では氏名の順番は決まっていない為、関数を用いるようになるかと思います。
Ｂ表では氏名は固定になっています。
また、Ａ表の契約件数は同じファイルの違うシートのセルに入っています。
分かり辛いかもしれませんが、☆印の下の表のようになっています。
契約出来た担当名だけを入れるようになっていて、契約が出来なかった場合はいれません。

Ａ表の契約件数（E4～）に別シートのＦ２４が自動的にＥ５の山田に1件加算される・・・と言う感じにしたいのです。

またＢ表も、Ａ表にそれぞれ件数を入力したら自動的に加算されるようにしたいのです。

わかるかた教えていただけますか？

よろしくお願いします。

回答No.1

keithin · ベストアンサー

Excel2007以降を使っていればCOUNTIFS関数で一発ですが、そうではないので重いのを承知で次のようにします。

シート２に活動実績一覧を記入する
Ｅ列に契約担当者をＡ表Ｂ表と同じ記載内容で（山田なら山田と。山田太郎じゃなく。）記入する
暫定的に、最大２０００行まで記入する。
（もうちょっとは増やせますが、野放図に増やすと重くなってツカエないシートになるので、常識の範囲で程々にすること。）

シート１にＡ表を作成する
日付、氏名、営業件数、約定数は質問外で、Ｅ４を求める
=SUMPRODUCT((Sheet2!$A$1:$A$2000=A4)*(Sheet2!$F$1:$F$2000=B4))
と記入、下向けにコピーする

シート３にＢ表を作成する
Ｂ列に氏名として、１行目にタイトル行として
Ｃ２に
=SUMIF(Sheet1!$B:$B,B2,Sheet1!C:C)
と記入、右に下にコピーして完成。

回答No.2

kagakusuki

今仮に、A表において「日付」と入力されているセルがSheet2のA3セル、B表において「日付」と入力されているセルがSheet2のG3セル、C表において「氏名」と入力されているセルがSheet1のA3セルであるものとします。
　又、Sheet3のA列～E列を作業列として使用するものとします。

　まず、Sheet3のA4セルに次の関数を入力して下さい。

=IF(ISNUMBER($B4),COUNTIF($B:$B,"<"&$B4)+COUNTIF($B$4:$B4,$B4),"")

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

=IF(AND(ISNUMBER(INDEX(Sheet1!$A:$A,ROW())),OR(INDEX(Sheet1!$D:$D,ROW())="可",INDEX(Sheet1!$D:$D,ROW())="約束")),INDEX(Sheet1!$A:$A,ROW()),"")

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

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$C:$C,ROW())="",INDEX(Sheet1!$D:$D,ROW())=""),"",INDEX(Sheet1!$A:$A,ROW())&"■"&INDEX(Sheet1!$C:$C,ROW())&"◆"&INDEX(Sheet1!$D:$D,ROW()))

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

=IF(ROWS($4:4)>COUNT($A:$A),"",VLOOKUP(ROWS($4:4),$A:$C,3,FALSE))

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

=IF(ISERROR(1/(COUNTIF($D$4:$D4,LEFT($D4,FIND("◆",$D4))&"*")=1)),"",ROWS($4:4))

　次に、Sheet3のA4～E4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　次に、Sheet2の

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

=IF(ROWS($4:4)>COUNT(Sheet3!$E:$E),"",VLOOKUP(SMALL(Sheet3!$E:$E,ROWS($4:4)),Sheet3!$A:$B,2,FALSE))

　次に、Sheet2の

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

=IF(ROWS($4:4)>COUNT(Sheet3!$E:$E),"",INDEX(Sheet1!$C:$C,MATCH(SMALL(Sheet3!$E:$E,ROWS($4:4)),Sheet3!$A:$A,0)))

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

=IF($A4="","",COUNTIF(Sheet3!$C:$C,$A4&"■"&$B4&"◆*"))

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

=IF($A4="","",COUNTIF(Sheet3!$C:$C,$A4&"■"&$B4&"◆約束"))

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

=IF($A4="","",COUNTIF(Sheet3!$C:$C,$A4&"■"&$B4&"◆可"))

　次に、Sheet2のA4～E4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。
　次に、Sheet2のH4セルに次の関数を入力して下さい。

=IF($G4="","",COUNTIF(Sheet3!$C:$C,"*■"&$G4&"◆*"))

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

=IF($G4="","",COUNTIF(Sheet3!$C:$C,"*■"&$G4&"◆約束"))

　次に、Sheet2の

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

=IF($G4="","",COUNTIF(Sheet3!$C:$C,"*■"&$G4&"◆可"))

　次に、Sheet2のH4～J4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　次に、Sheet2のG4以下に各担当者の氏名を入力して下さい。

　以上です。