[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『COUNTIFSでデータの取得をしたいのですが・・・。』（まぁぼ〜）
=IF(A2="","",COUNTIFS(O:O,A2,R:R,"",AQ:AT,"*<>"))
=IF(A2="","",COUNTIFS(O:O,A2,R:R,"",AT:AZ,"*<>"))
のようにしたいのですが、うまくいきません。
A2は取得したい日付があります。
O:Oにはデータ元の日付
R:Rにはデータ元の休みのフラグ（休み以外はNULL）
AQ:AYには以下のような出勤の時間があります。
・・ O ・・ R ・・ AQ AR AS AT AU AV AY AZ ・・ BK 1 ・・ 05/01 ・・ ・・ 9 10 11 12 13 14 15 16 ・・ 2 ・・ 05/01 ・・ ・・ 10 11 12 13 14 15 16 ・・ 3 ・・ 05/01 ・・ ・・ 16 ・・ 29 4 ・・ 05/01 ・・ ・・ 9 10 11 12 13 14 15 16 ・・ 5 ・・ 05/01 ・・ 休み ・・
05/01の9時から12時までにいる人数などを取得したいのですがうまくいきません。
どなたかご教授お願い致します。
＜ 使用 Excel：Excel2013、使用 OS：unknown ＞
以下ヘルプの引用から。 >各追加範囲の列数と行数は "条件範囲 1" 引数と同じである必要があります。範囲どうしは隣接していなくてもかまいません。 つまり条件範囲はすべて同じ多い差でなくてはならない。
（ねむねむ） 2018/05/15(火) 08:45
また、条件で<,>,=,<>,<=,>=は先頭にない場合は比較演算子として扱わず単なる文字列となる。 なので*<>は文字列の終わりが<>の物という条件になる。 （ねむねむ） 2018/05/15(火) 08:47
=SUMPRODUCT((O:O=A2)*(R:R="")*(AQ:AT<>"")) ではどうか。 もし、式が重くなるようであれば行範囲も指定してみてくれ、 （ねむねむ） 2018/05/15(火) 08:50
9時から12時までにいる人数・・の定義が判らないですが、 例示の表では2人が正解でしょうか？3人が正解でしょうか？ それとも別でしょうか？
9時から12時まで通しでいた人数なら下記でいいかと思います。 =COUNTIFS(O:O,A2,R:R,"",AQ:AQ,"<>",AT:AT,"<>") （ろっくん） 2018/05/15(火) 09:03
私の式の場合は11人となる。 (9時で2人、10時で3人、11時・12時もそれぞれ3人で合計して11人） 最初に書かれていた式から考えていたが処理を考えるとろっくんさんの考え方のようにも思える。 （ねむねむ） 2018/05/15(火) 09:31
考えてみると休みの日は出勤時間はないはずなのでR列のチェックは必要なのだろうか？ （ねむねむ） 2018/05/15(火) 09:39
表の場合ですが、
5/1の9時から12時までにいる人数は3名
これは例ですが
5/1の13時から16時までにいる人数は4名
となるように考えております。
あくまで上記の場合に起こりうる重複は考えなくて大丈夫です。
ねむねむがおっしゃる通り、R列のチェックは不必要かもですね。
（まぁぼ〜） 2018/05/15(火) 13:25
そうであれば （ねむねむ） 2018/05/15(火) 08:50 の式で。 あと （ねむねむ） 2018/05/15(火) 08:45 の >同じ多い差 は 同じ大きさ で。 （ねむねむ） 2018/05/15(火) 13:36
=IF(A2="","",COUNTIFS(O:O,A2,R:R,"",AQ:AT,"*<>")) の代わりに =SUMPRODUCT((O:O=A2)*(R:R="")*(AQ:AT<>"")) ということだが。 ああ、 =IF(A2="","",SUMPRODUCT((O:O=A2)*(R:R="")*(AQ:AT<>""))) になるか。 （ねむねむ） 2018/05/15(火) 14:07
例で書いた
5/1の13時にいる14時にいる15時にいる16時にいるは「3人」
5/1の16時にいるは「1人」
合計で「4人」
このように取得したいです。
説明がわかりづらくて申し訳ございません。
（まぁぼ〜） 2018/05/15(火) 14:19
こちらこそ勘違いしていてすまない。 ちょっと考えてみる。 （ねむねむ） 2018/05/15(火) 14:41
=IF(A2="","",SUMPRODUCT((O:O=A2)*(COUNTIF(INDIRECT("AQ"&ROW(A:A)&":AT"&ROW(A:A)),"<>")>0)))
ではどうか。
重い場合は
=IF(A2="","",SUMPRODUCT((O1:O10000=A2)*(COUNTIF(INDIRECT("AQ"&ROW(1:10000)&":AT"&ROW(1:10000)),"<>")>0)))
のように行範囲を指定してみてくれ。
（ねむねむ） 2018/05/15(火) 14:55
今いろいろいじってますが、初めから書けばよかったのですが、実際はGOOGLEのスプレッドシートで使用したいのと、違うシートから読み取りたいのですが、そこがうまくいかなくて四苦八苦しております。
=IF(A2="","",SUMPRODUCT((シート2!O:O=A2)*(COUNTIF(INDIRECT("シート2'!AQ"&ROW(A:A)&":シート2'!AT"&ROW(A:A)),"<>")>0)))
でEXCEL2010で「＃REF!」になってしまいます。
スプレッドシートだと、数字がとれるのですがなぜか「5」になってしまいます。
（まぁぼ〜） 2018/05/15(火) 17:54
別シート参照の場合は
INDIRECT("シート2'!AQ"&ROW(A:A)&":シート2'!AT"&ROW(A:A))
を
INDIRECT("シート2'!AQ"&ROW(A:A)&":AT"&ROW(A:A))
としてくれ。
（ねむねむ） 2018/05/16(水) 08:49
すまない。
INDIRECT("'シート2'!AQ"&ROW(A:A)&":AT"&ROW(A:A))
としてくれ。
（頭の'が抜けていた）
それとGOOGLEスプレッドシートは触ったことがないのでそちらについては助言できない。
（ねむねむ） 2018/05/16(水) 08:53
ありがとうございます。
GOOGLEスプレッドシートですと、挙動が違うってのもおかしいですよね？
ちなみにクレクレ君的になってしまいますが、
=IF(A2="","",SUMPRODUCT((シート2!O:O=A2)*(COUNTIF(INDIRECT("'シート2'!AQ"&ROW(A:A)&":AT"&ROW(A:A)),"<>")>0)))
以外の式って何か考えられますかね？
正直結構重たいので、軽くできたらと思い・・・。
「1:10000」とかにしてもあまり重さが変わらないので。
（まぁぼ〜） 2018/05/16(水) 13:00
作業列を使う。 例えばBL列に =COUNTIF(AQ2:AT2,"<>") と入れておき =IF(A2="","",COUNTIFS(O:O,A2,BL:BL,"<>")) とする。 （ねむねむ） 2018/05/16(水) 13:07
13時から16時も集計したければBM列に =COUNTIF(AU2:AZ2,"<>") として同じように。 （ねむねむ） 2018/05/16(水) 13:08
すまない。 =IF(A2="","",COUNTIFS(O:O,A2,BL:BL,"<>")) は =IF(A2="","",COUNTIFS(O:O,A2,BL:BL,">0")) としてくれ。 （ねむねむ） 2018/05/16(水) 14:29
Dim c As Range, r As Range
'A2 =日付
'A3 =from時(例えば9)
'A4 =to時間(例えば12)
'元シートをアクティブにした状態で実行
For Each c In Intersect(ActiveSheet.Range("O:O"), ActiveSheet.UsedRange)
If c.Value = Range("A2").Value Then
If r Is Nothing Then
Set r = c
Else
Set r = Union(r, c)
End If
End If
Next c
If r Is Nothing Then MsgBox "該当日なし": Exit Sub
For Each c In r.EntireRow
For Each r In Intersect(c, ActiveSheet.Range("AQ:BK"))
If r.Value >= Range("A3").Value And r.Value <= Range("A4").Value Then ctr = ctr + 1: Exit For
Next r
Next c
MsgBox Format(Range("A2").Value, "yyyy/m/d") & "の" & Range("A3").Value & "時から" & Range("A4").Value & "時までは" & ctr & "名"
End Sub
（mm） 2018/05/16(水) 15:14
遠回りせずに作業行で行いました。
（まぁぼ〜） 2018/05/17(木) 23:08
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.