複数の条件指定で、文字数を数える関数

質問

複数の条件を指定して記載されている文字数を数えたいのですが
条件が複雑すぎて、どのようにしてよいかわかりません。
どなたか教えて下さい。
おそらくCOUNTIFSの関数かと思いますが…。

条件は下記です（図を参照して下さい）
左のデータベースから右に個数の結果の表となります。

・左の表で10/1～10/30までの結果で担当者の成功の数、失敗数を数えます
・もし担当が二人であった場合は成功を1と数えず、担当それぞれ0.5として数えます。
・もし担当が一名の場合は成功、失敗は1と数えます。
・もし担当が同じ人が担当を２回していても成功は1と数えます。

右の表は手動で計算したものですが、関数でしたら、
青い枠の中にどのように記載したらよいものでしょうか？
ご回答お願い致します。

回答No.3

tom04 · ベストアンサー

こんばんは！
手元のExcel2003でやってみました。

結果表の氏名欄はあらかじめ入力してあるという前提です。

↓の画像のように「開始日」と「終了日」を別セルに入力します。
そして作業用の列を設けています。

作業列E2セルに
=IF(A2="","",1/COUNTA(B2:C2))
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そして、H3セルに
=IF($G3="","",SUMPRODUCT(($D$1:$D$100>=$G$1)*($D$1:$D$100<=$I$1)*($A$1:$A$100=H$2)*($B$1:$B$100=$G3),$E$1:$E$100)+SUMPRODUCT(($D$1:$D$100>=$G$1)*($D$1:$D$100<=$I$1)*($A$1:$A$100=H$2)*($C$1:$C$100=$G3),$E$1:$E$100))

という数式を入れ、列方向・行方向にオートフィルでコピーすると
画像のような感じになります。

※　COUNTIFS関数を使用する場合も作業列を使用して
COUNTIFS（B列の場合）＋COUNTIFS（C列の場合）
で対応できると思います。m(_ _)m

お礼

回答ありがとうございます。
出来ました！
シンプルで分かりやすいです。勉強になりました。
使わせて頂きます！

回答No.5

hide3803

まずワークシートに個人別に一つずつ条件を記入します。

名前のあるところを0.5　空欄は空欄の値とします。　
B12のセルを　=IF(B2="","",IF($A$10=B2,0.5,0))

判定のところでは期日内なら1それ以外なら0とします。G1に10/1　G2に10/30
D12のセルを　=IF(AND($G$1<=D2,$H$1>=D2),1,)

仮計では担当1と担当2を足して判定をかけます。
E12は　=SUM(B12:C12)*D12

計は担当1か担当2が空欄かどうか調べ空欄ならば倍にします。
F12は　=IF(OR(B12="",C12=""),E12*2,E12)

成功の欄では成功か失敗か調べ成功のみ表示
G12は　=IF($A12=G$11,$F12,"")

失敗の欄は失敗のみ表示
H12は　=IF($A12=H$11,$F12,"")

あとは項目ごとに集計です。
まどろっこしいですが1つずつできるので分かりやすいと思います。

　　　　　　　　　　　　　　　　　　以上です。

お礼

回答ありがとうございます。
分かりやすいのですが、自分以外の人も見るデータなので
これでは数字が多くあり、結論の数字が分かりにくくなってしまいます。
かなりのデータの中からの一部についての集計方法について
質問したので、あまりセルの挿入は避けたかったのです。
でも、勉強になりました。ありがとうございます。

回答No.4

tom04

No.3です！
前回の投稿で誤記がありました。

※以降の
＞COUNTIFS関数を使用する場合も作業列を使用して
＞OUNTIFS（B列の場合）＋COUNTIFS（C列の場合）

は間違いで、SUMIFS関数が正解です。

・合計対象範囲　→　E列（作業列）　として
で
（1）日付（以上）・日付（以下）・A列・B列　の各条件と一致するもの
（2）日付（以上）・日付（以下）・A列・C列　の各条件と一致するもの

（1）＋（2）で結果が表示できると思います。

何度も失礼しました。m(_ _)m

回答No.2

KURUMITO

回答No1です。
G列に入力する担当名がすべてBやC列に有る場合には先の式で問題はないのですがそうでない場合もありますので次のようにしてください。
E2セルには次の式を入力してF2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(B2="","",IF(OR($D2<LEFT($H$1,FIND("～",$H$1)-1)*1,$D2>MID($H$1,FIND("～",$H$1)+1,10)*1),"",IF(COUNTIF($G:$G,B2)=0,0,IF($A2=$H$2,1000,2000)+MATCH(B2,$G:$G,0)*10)))

H2セルには次の式を入力してI2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF($G3="","",(COUNTIFS($E:$E,COLUMN(A1)*1000+ROW()*10,$F:$F,"<>"&COLUMN(A1)*1000+ROW()*10,$F:$F,">=0")+COUNTIFS($F:$F,COLUMN(A1)*1000+ROW()*10,$E:$E,"<>"&COLUMN(A1)*1000+ROW()*10,$E:$E,">=0"))*0.5+COUNTIFS($E:$E,COLUMN(A1)*1000+ROW()*10,$F:$F,COLUMN(A1)*1000+ROW()*10)+COUNTIFS($E:$E,COLUMN(A1)*1000+ROW()*10,$F:$F,"")+COUNTIFS($F:$F,COLUMN(A1)*1000+ROW()*10,$E:$E,""))

お礼

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

E列に列を挿入してみてやってみたのですが、なぜかE3には1030と表示され
結果になるH3は1.5という結果になってしまいました。
なぜだか分からないです。

回答No.1

KURUMITO

条件が複雑ですので作業列を作って対応します。
E列の右横に列を挿入してG3セルに田中、G4セルに佐藤となるようにします。
E2セルには次の式を入力して下方にドラッグコピーします。

=IF(B2="","",IF(OR($D2<LEFT($H$1,FIND("～",$H$1)-1)*1,$D2>MID($H$1,FIND("～",$H$1)+1,10)*1),"",IF($A2=$H$2,1000,2000)+IF(COUNTIF($G:$G,B2)=0,"",MATCH(B2,$G:$G,0)*10)))

F2セルには次の式を入力して下方にドラッグコピーします。

=IF(C2="","",IF(OR($D2<LEFT($H$1,FIND("～",$H$1)-1)*1,$D2>MID($H$1,FIND("～",$H$1)+1,10)*1),"",IF($A2=$H$2,1000,2000)+IF(COUNTIF($G:$G,C2)=0,"",MATCH(C2,$G:$G,0)*10)))

お求めの表ではH3セルに次の式を入力してI3セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF($G3="","",(COUNTIFS($E:$E,COLUMN(A1)*1000+ROW()*10,$F:$F,"<>"&COLUMN(A1)*1000+ROW()*10,$F:$F,">0")+COUNTIFS($F:$F,COLUMN(A1)*1000+ROW()*10,$E:$E,"<>"&COLUMN(A1)*1000+ROW()*10,$E:$E,">0"))*0.5+COUNTIFS($E:$E,COLUMN(A1)*1000+ROW()*10,$F:$F,COLUMN(A1)*1000+ROW()*10)+COUNTIFS($E:$E,COLUMN(A1)*1000+ROW()*10,$F:$F,"")+COUNTIFS($F:$F,COLUMN(A1)*1000+ROW()*10,$E:$E,""))

H2セルには成功、I2セルには失敗などの文字列の入力とH1セルには10/1～10/30のように必ず入力してあることが必要です。