複数の条件を指定して記載されている文字数を数えたいのですが
条件が複雑すぎて、どのようにしてよいかわかりません。
どなたか教えて下さい。
おそらくCOUNTIFSの関数かと思いますが…。
条件は下記です(図を参照して下さい)
左のデータベースから右に個数の結果の表となります。
・左の表で10/1~10/30までの結果で担当者の成功の数、失敗数を数えます
・もし担当が二人であった場合は成功を1と数えず、担当それぞれ0.5として数えます。
・もし担当が一名の場合は成功、失敗は1と数えます。
・もし担当が同じ人が担当を2回していても成功は1と数えます。
右の表は手動で計算したものですが、関数でしたら、
青い枠の中にどのように記載したらよいものでしょうか?
ご回答お願い致します。
No.3ベストアンサー
- 回答日時:
こんばんは!
手元の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
- 回答日時:
まずワークシートに個人別に一つずつ条件を記入します。
名前のあるところを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
- 回答日時:
No.3です!
前回の投稿で誤記がありました。
※以降の
>COUNTIFS関数を使用する場合も作業列を使用して
>OUNTIFS(B列の場合)+COUNTIFS(C列の場合)
は間違いで、SUMIFS関数が正解です。
・合計対象範囲 → E列(作業列) として
で
(1)日付(以上)・日付(以下)・A列・B列 の各条件と一致するもの
(2)日付(以上)・日付(以下)・A列・C列 の各条件と一致するもの
(1)+(2)で結果が表示できると思います。
何度も失礼しました。m(_ _)m
No.2
- 回答日時:
回答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
- 回答日時:
条件が複雑ですので作業列を作って対応します。
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のように必ず入力してあることが必要です。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel(エクセル)でフィルター抽出後、非表示の行を計算しないで、合計を算出する方法 【内容】 添 4 2023/01/30 17:17
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Visual Basic(VBA) 日付を重複させずに数えたい 4 2022/12/04 16:26
- Excel(エクセル) エクセルで当番表を作成したいです。 1 2023/08/09 19:53
- Excel(エクセル) エクセルVBA VLOOKUPを使ってのカウント作業 2 2023/02/19 09:03
- その他(メールソフト・メールサービス) クリック率とは? 1 2023/06/18 11:53
- Access(アクセス) アクセス where句を使用して複数条件抽出をするには 2 2022/08/29 13:24
- Excel(エクセル) Excelでの複数条件のカウントについて 1 2022/09/25 07:40
- Excel(エクセル) 【Excel質問】 「本日の日付」から指定条件を満たす営業日経過後の日数を表示させる関数式 3 2022/06/06 23:28
- Visual Basic(VBA) VBA エクセル 条件の設定 1 2022/03/28 10:24
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
【Excel VBA】PDFを作成して,...
-
Excel テーブル内の空白行の削除
-
エクセルでXLOOKUP関数...
-
Microsoft Formsの「個人情報や...
-
マイクロソフト 一時使用コード...
-
Excel2019と365、2021
-
VLOOKUP関数について
-
Microsoft Officeを2台目のPCに...
-
Excelのセルの重複チェックが出...
-
会社PCのメールが更新されない
-
PCを買い換えました。 今使って...
-
office2016のパソコン2台インス...
-
【スプレッドシート】白色のセ...
-
大学のレポート A4で1枚レポー...
-
Excel VBA 日程表からスケジュ...
-
時間の平均値を計算する方法を...
-
Microsoft365で写真をアルバム...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
マイクロソフト 一時使用コード...
-
英数字のみ全角から半角に変換
-
Office2021を別のPCにインスト...
-
Microsoft Formsの「個人情報や...
-
officeビジネス型のワードやエ...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
Microsoft Officeを2台目のPCに...
-
何このステータスバー
-
2つのシートの一致する行のセ...
-
会社のTeamsのことで相談です。...
-
エクセルにリンクされるのをし...
-
Windows 11で、IME言語バー(IM...
-
office2010とoffice365の共存で...
-
Microsoftのパソコンです。 エ...
-
エクセルでXLOOKUP関数...
-
Excel関数について質問ですm(__)m
-
VBA
-
自分の専門分野の仕事。初見で...
おすすめ情報