[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『COUNTIFS関数の設定について』（右近）
お尋ねいたします。
COUNTIFS関数の設定方法について、教えてください。
以下のように、開始日と停止日の列を作っています。
開始日には日付を入れていて、停止になった場合に停止日を入れます。
以上の前提で、C列に基準日を入力すると、基準日現在の件数をD列に表示させるため、COUNTIFS関数を設定したいと思っています。
B6の停止日は、基準日よりも後ですので、カウントに加えています。基準日が4/10又はそれ以降の場合は、3件としたいです。
=COUNTIFS($A$2:$A$6,"<="&$C$2,$B$2:$B$6,">="&$C$2)
A B C D 1 開始日 停止日 基準日 件数 2 2021/4/1 2021/4/9 4 3 2021/4/2 2021/4/5 4 2021/4/5 5 2021/4/6 6 2021/4/6 2021/4/10 ・ ・ ・
よろしくお願いいたします。
＜ 使用 Excel：Excel2019、使用 OS：Windows10 ＞
=COUNTIFS($A$2:$A$6,"<="&$C$2,$B$2:$B$6,">="&$C$2)+COUNTIFS($A$2:$A$6,"<="&$C$2,$B$2:$B$6,"") あるいは =SUMPRODUCT(($A$2:$A$6<=$C$2)*(($B$2:$B$6>=$C$2)+($B$2:$B$6=""))) ではどうだろうか？ （ねむねむ） 2021/04/09(金) 16:37
A列の計算範囲に空白のセルがあるだろうか？ COUNTIFS関数のほうでは空白セルは無視されるがSUMPRODUCT関数では空白セルを0として扱うためA列が空白のセルも数えてしまうようだ。 一応対策としては式を =SUMPRODUCT(($A$2:$A$10<=$C$2)*($A$2:$A$10<>"")*(($B$2:$B$10>=$C$2)+($B$2:$B$10=""))) としてみてくれ。
（ねむねむ） 2021/04/09(金) 19:46
>別の検索条件を追加する場合 AND条件であれば ($A$2:$A$10<=$C$2)*($A$2:$A$10<>"") のように*でつなげてみてくれ。 （ねむねむ） 2021/04/09(金) 19:55
E2=SUMPRODUCT(($A$2:$A$10<=$D$2)*($A$2:$A$10<>"")*(($B$2:$B$10>=$D$2)+($B$2:$B$10=""))*COUNTIF($C$2:$C$10,"*"&LEFT($E2,1)&"*"))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ところが、結果は「0」となってしまいます。実際にはF2の値は「4」となるはずなのですが…。
D〜F列は作業列になります。
ご指導のほど、どうぞよろしくお願いいたします。
A B C D E F 1 開始日 停止日 利用日 基準日 曜日 件数 2 2021/4/1 月水金 2021/4/9 月曜日 4 3 2021/4/2 2021/4/5 月火木土 火曜日 1 4 2021/4/5 月木 水曜日 2 5 2021/4/6 金土 木曜日 3 6 2021/4/6 2021/4/10 月木金 金曜日 3 7 2021/4/9 月火水木 土曜日 1 ・ ・ （右近） 2021/04/15(木) 11:28
端折って言うと配列数式で行ごとの数値を求めたい場合にCOUNTIF関数をそのまま使うことは出来ない。
で、このようにしてみてくれ。
=SUMPRODUCT(($A$2:$A$10<=$D$2)*($A$2:$A$10<>"")*(($B$2:$B$10>=$D$2)+($B$2:$B$10=""))*COUNTIF(INDIRECT("C"&ROW($2:$10)),"*"&LEFT($E2,1)&"*"))
（ねむねむ） 2021/04/15(木) 11:57
~~ ~~
開始日<=基準日 停止日>=基準日
のはずですよね？なぜか同じ日だと変化しないのは、時間も関わっているのでしょうか？
今一度、よろしくお願いいたします。
（右近） 2021/04/15(木) 12:39
=SUMPRODUCT(($A$2:$A$10<=$C$2)*($A$2:$A$10<>"")*(($B$2:$B$10>=$C$2)+($B$2:$B$10=""))) の式でこちらでは基準日と停止日が同じ場合にもカウントしている。 基準日、停止日は手入力だろうか？ もし、式で求めているようであればその式を書いてみてくれないか？ （ねむねむ） 2021/04/15(木) 12:54
ではすまないがデータを１行だけにして基準日と停止日を同じにし、式を入力したセルにカーソルを合わせて 数式バー上の式の($A$2:$A$10<=$C$2)部分を選択した状態でF9キーを押してみて(選択部分の結果を表示させるキー) その結果を書いてくれ。
（ねむねむ） 2021/04/15(木) 13:08
同じように($B$2:$B$10>=$C$2)を選択してF9キーを押した結果も。 なお、F9キーを押した後はEscキーで戻ってくれ。 （ねむねむ） 2021/04/15(木) 13:09
選択個所を間違えていないだろうか？
{TRUE,TRUE,FALSE,…}
のような結果になると思うのだが。
（ねむねむ） 2021/04/15(木) 13:52
($A$2:$A$10<=$C$2)部分{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
($B$2:$B$10>=$C$2)部分
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
（右近） 2021/04/15(木) 14:00
その結果だとB列(停止日)はすべて基準日未満と判断されているようだ。 (空白セルは0と判断されるので構わないが停止日が入力されているセルも未満と判断されている) それでは基準日のセル、および停止日のセルの表示形式をG/標準にするとそれぞれなんと表示されるだろうか？
（ねむねむ） 2021/04/15(木) 14:09
44302とだけ表示されるのではないか？ 4月16日はどこに表示されるのだろうか？
あと開いているセルに =C2=B2 B2は停止日が入力されているセルで。 ではなんと表示されるだろうか？ （ねむねむ） 2021/04/15(木) 14:28
少なくともこちらでは正しい値を示しているのとこちらの質問への回答もないようなので私には回答が難しい。 別の回答者を待ってくれ。 （ねむねむ） 2021/04/16(金) 15:00
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.