[[20140815221359]]　『二つの条件に合うものを数えたい。』（ぽん）　ページの最後に飛ぶ

『二つの条件に合うものを数えたい。』（ぽん）

お店の滞在時間を●で示した表があります。
下記のように列に日付、横に時間が０９時から１７時まで１分単位で入力されているものに滞在時間をしめす●が入っている表があります。（ここではうまく表示できませんが）

日付 9:00　9:01　9:02　9:03　9:04　9:05　9:06 9:07 09:08・・・・
2/10 ●　　●　　●　　●　　●　　●　　●　 ● 　●
2/10　　　　　　　 ●　　●　　●　　●
2/11 　　　　　　　 ●　　●　　　　 ● 　●
2/11 　　　　　　 ●　 ● 　　●
2/12 　　　　　　　　　　　　　　　　　　　　　　 ●　　●　　　

以下３０００行

別の表に日付ごと、９時代、１０時代と●の数を集計したいのですが、うまくいきません。
ただ同じ日付、時間に●が２つ以上ある場合は１つとします。時間帯に●がひとつでもあれば１と数えたいです。

日付は日によって数が違うため可変にしたいのですが、そこも含め自分ではできないのでよろしくお願いいたします。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

時刻データが16:59までとしてB1:RM1に1分ごとの時刻データがあると仮定して
作業列として、RP1:RW1に9~16を入力する
PR2に =SUMPRODUCT((HOUR($B$1:$RM$1)=RP$1)*ISTEXT($B2:$RM2))
式を右および下へコピー
次にRY2に2/10を入力、下へコピー
RZ1:SG1に9~16を入力
RZ2に =SUMIFS(RP$2:RP$3000,$A$2:$A$3000,$RY2)
式を右および下へコピー

（wisemac21） 2014/08/16(土) 14:27

wisemac21さま

ご回答ありがとうございます！大変助かります！
今自分でアレンジしてみましたが●はIF関数を使って表示させているからか、ISTEXTの関数のあるRP2の部分にすべて60と表示されてしまいます。

ここが改善できれば、うまくいくと思うのですが、お助けいただけますでしょうか？

よろしくお願いいたします。

（ぽん） 2014/08/18(月) 17:32

1案です。
IF関数のtrueが「●」、FALSEが「””」だとすべてTEXTなので60になると思います。
FALSEを「0」にすると数値なのでISTEXTの対象から外せます。

（wisemac21） 2014/08/18(月) 18:13

現在のままで
RP2に =COUNTIFS($B$1:$RM$1,">="&"9:00"*1,$B2:$RM2,"●*",$B$1:$RM$1,"<"&"10:00"*1)
RQ2に =COUNTIFS($B$1:$RM$1,">="&"10:00"*1,$B2:$RM2,"●",$B$1:$RM$1,"<"&"11:00"*1)
同様にして２行目の各時間帯に式を入力し、下へコピー
（wisemac21） 2014/08/18(月) 19:36

前に提示した数式だと時刻の演算誤差がでるので、数式を修正しました。
セルRP1:RX1に9:00~17:00を入力、
セルRP2に =COUNTIFS($B$1:$RM$1,">="&RP$1-0.00001,$B$1:$RM$1,"<"&RQ$1-0.00001,$B2:$RM2,"●*")
式を右へRW2までコピー、下へ行3000までコピー
『数式中の-0.00001は演算誤差対策です」

（wisemac21） 2014/08/18(月) 20:40

wisemec21さま

たくさんのアドバイスありがとうございます！！
COUNTIFS関数を入れてみました。時間帯ごとの●の数を正確に数えてくれています。
ただやはり同日のどう時間帯にある●を１とは数えてくれず、日付ごとの時間帯計算をすると６０分以上になるところがあります。

抜粋ですが、元のデータは下記のようになっています。11:00からのところはwisemac21さまのcountifsの式が入っています。たとえば14時代をそのまま足すと重なっている時間があるため、合計で60分以上になってしまいます。なので同じ日付、時間帯の中に●が1つ以上あれば１とカウントする、というような式が作れればいいのですが、自分では知識不足でできません。。。

　　　　　　入店退店滞在時間 11:00 12:00 13:00 14:00 15:00
1/14 14:05 14:25 00:20 0 0 0 20 0 0
1/14 14:09 14:15 00:06 0 0 0 6 0 0
1/14 14:16 14:56 00:40 0 0 0 40 0 0
1/14 14:22 14:34 00:12 0 0 0 12 0 0
1/14 14:27 16:00 01:33 0 0 0 33 60 0
1/14 14:30 14:56 00:26 0 0 0 26 0 0
1/14 14:49 15:00 00:11 0 0 0 11 0 0
1/14 15:07 16:10 01:03 0 0 0 0 53 10
1/14 16:23 16:30 00:07 0 0 0 0 0 7

各時間でお客様との接客時間を知りたいので重なっている時間は１分としたいのです。
たとえば下記の例だと１４時代は３７分接客していたというように。
01/06　09：00には前回教えていただいた=SUMIFS(RP$2:RP$3000,$A$2:$A$3000,$RY2) が入力されています。

	09:00	10:00	11:00	12:00	13:00	14:00	15:00
1/14	1	7	12	0	2	165	113

よろしくお願いいたします。

（ぽん　） 2014/08/19(火) 17:31

よくわからないこと
＞ただやはり同日の同時間帯にある●を１とは数えてくれず、
具体的にデータを提示し、説明してください。
＞入店時間、退店時間、滞在時間のデータ
当初データとは違うのでは、後出しでいろいろデータするのはどういう訳ですか？
＞下記の例だと１４時代は３７分接客していたというように
37分とはどのような計算で求めているのか？

これでは無駄な時間ばかり浪費してしまいます。
（wisemac21） 2014/08/19(火) 23:30

wisemac２１様

説明不足ですみません。
毎回必要な部分だけ抜粋していて当初データと違ってきてしまい混乱させてしまい申し訳ありません。それから14時台37分は私のうち間違いです。

データの最初ですが、下記のようになっています。
行 ___A___ ___B__ __C__ ____D____ ___E__ __F__ __G__ __H__ __I__ __J__ __K__ __L__ __M__

   1 日付___    入店__  退店 _ 滞在時間 _  09:00  09:01 09:02  09:03  09:04 09:05 09:06 09:07 09:08　--17:00まで
   2 1月14日 09:00 09:05 0:05   ______     ●        ● 　　●      ●       ●　   ●　
   3 1月14日 09:01 09:06 0:05   ______     ●　　  ●       ●      ●　    ●  　 ●
   4 1月14日 09:07 09:10 0:03                                                                   ●　　●       
   5 1月14日 14:22 14:34 0:12                                                               
   6 1月14日 14:27 16:00 1:33                                                               
   7 1月14日 14:30 14:56 0:26                                                               
   8 1月14日 14:49 15:00 0:11                                                               
   9 1月14日 15:07 16:10 1:03                                                               
  10 1月14日 16:23 16:30                                                                    
  11 2月10日 9:01  9:05　00:04    ______     ●        ● 　　●      ●       ●　   ●
  12 2月10日 9:04  9:08　00:04                                                  ●        ● 　　●      ●       ●　   ●
以下3000行

求めたいデータ
下記のように1時間のうちに何分お店にいたのかを求めたいです。
（下記の分数合計は上記のデータは抜粋のため合致いたしません。）

	09:00	10:00	11:00	12:00	13:00	14:00	15:00
1/14	9	7	12	0	2	50	
2/10     10
（ぽん） 2014/08/20(水) 22:32

＞ただやはり同日の同時間帯にある●を１とは数えてくれず、
データの中で同日同時間帯にある●はどこですか
同日で時間が重なり合う部分は1とカウントするという意味ですか？

滞在時間が0:05だと●の数は6になりますが、滞在時間は●の数より1少ないということですか？
11行目の2月10日の滞在時間は0:04ですが、●が6ありますが間違いですか？
（wisemac21） 2014/08/21(木) 07:29

同日同時刻の重なり合う部分は１とカウントする場合

A3005に日付（ここでは1月14日）を入力
A3006に日付（ココでは2月10日）を入力

F3005に =IF(COUNTIFS($A$2:$A$3000,$A3005,F$2:F$3000,"●")>=1,1,"")
RQ3005まで右へコピー

RT3004からSB3004に9:00～17:00を入力
RT3005に =COUNTIFS($F$1:$RQ$1,">="&RT$3004-0.00001,$F$1:$RQ$1,"<"&RU$3004-0.00001,$F3005:$RQ3005,1)
右、下へコピー

結果を見やすくするために日付をコピーする
RS3005に＝A3005
下へコピー

（wisemac21） 2014/08/21(木) 08:54

wisemac21様

ありがとうございます！必要なデータが出ました！
貴重なお時間を割いて考えてくださり本当にありがとうございました！
（ぽん　） 2014/08/21(木) 18:35

コメント返信：

[ 一覧(最新更新順) ]

[[20140815221359]] 『二つの条件に合うものを数えたい。』（ぽん） ページの最後に飛ぶ

[[20140815221359]]　『二つの条件に合うものを数えたい。』（ぽん）　ページの最後に飛ぶ