[[20170505110613]] 『時間帯の集計』(クマ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『時間帯の集計』(クマ)

時間帯の型の集計をしたいと考えています
データに日時、品名、開始時間、終了時間、型があり20行くらいあります。
そのデータを元に終了時間当日13時以降から翌日13時頃までの型の集計をF列に出したいのですがなかなかうまくいかず皆様のお知恵をお借りしたいと思います。
宜しくお願いします。

 A       B         C         D          E          F
日時     品名      開始時間    終了時間       型

 4/14      マグロ        3:00            5:00           C          型A  2
           イワシ        5:00            7:00           C          型D  3
           サンマ        7:00           12:00           B          型E  2
           サケ           12:00           15:00           E
           カツオ       15:00           22:00           A
           クロダイ      22:00            1:00           D
 4/15      マグロ        1:00            5:00           E 
           タコ          5:00            8:00           D
           イカ          8:00           11:00           A
           マグロ       11:00           15:00           D

< 使用 Excel:Excel2013、使用 OS:Windows10 >


 	A	B	C	D	E	F	G
 1	日時	品名 開始時間 終了時間	型	A	
 2	4月14日	マグロ	3:00	5:00	C	D	
 3		イワシ	5:00	7:00	C	E	
 4		サンマ	7:00	12:00	B		
 5		サケ	12:00	15:00	E		
 6		カツオ	15:00	22:00	A		
 7	       クロダイ	22:00	1:00	D		
 8	4月15日	マグロ	1:00	5:00	E		
 9		タコ	5:00	8:00	D		
 10		イカ	8:00	11:00	A		
 11		マグロ	11:00	15:00	D		

 G1:=SUMPRODUCT((E$2:E$11=F1)*((D$2:D$11>="13:00")+(D$2:D$11+1<="13:00")))
 下にコピー

 ということでしょうか?
(bi) 2017/05/05(金) 11:57

 こんにちわ。

 この表は常に当日と翌日までのデータしか無いんですか?

 当日と言うのは今日の日付では無いですよね?
 じゃないと翌日のカウントなんて出来ないので。
 昨日から今日にかけてですか?

(sy) 2017/05/05(金) 12:28


当日と翌日までのデーターが曜日ごとに別シートで管理しています。

(クマ) 2017/05/05(金) 13:17


biさんありがとうございます。
ですがエラーとなります。
(クマ) 2017/05/05(金) 13:19

 >当日と翌日までのデーターが曜日ごとに別シートで管理しています。 
 よく分かりませんが?
 取り敢えず計算はレイアウトのように当日と翌日だけがあると言う事ですか?

 A〜E列のデータは別シートから数式などで参照してるんですか?
 それとも手入力のデータですか?

 biさんのレイアウトのようにF列の検索条件を「型A」ではなく「A」と言うデータにしていますか?
 ただ今のレイアウトなら以下のような式じゃないと正しく計算されないと思います。
=SUMPRODUCT((E$2:E$11=F1)*((D$2:D$11+(ROW(D$2:D$11)>2)*(D$1:D$10>D$2:D$11)+(ROW(D$2:D$11)>=MATCH(10^7,A:A)))>=1*"13:00")*((D$2:D$11+(D$1:D$10>D$2:D$11)+(ROW(D$2:D$11)>=MATCH(10^7,A:A)))<=1+"13:00"))
 下にフィルコピー

 作業列を使って良いなら簡単です。
     |[A]    |[B]     |[C]     |[D]     |[E]|[F]|[G]|[H]
 [1] |日時   |品名    |開始時間|終了時間|型 |   |A  |  2
 [2] |4月14日|マグロ  |3:00    |5:00    |A  |  0|D  |  1
 [3] |       |イワシ  |5:00    |7:00    |D  |  0|E  |  3
 [4] |       |サンマ  |7:00    |12:00   |B  |  0|   |   
 [5] |       |サケ    |12:00   |15:00   |E  |  0|   |   
 [6] |       |カツオ  |15:00   |22:00   |A  |  0|   |   
 [7] |       |クロダイ|22:00   |1:00    |E  |  1|   |   
 [8] |4月15日|マグロ  |1:00    |5:00    |E  |  1|   |   
 [9] |       |タコ    |5:00    |8:00    |D  |  1|   |   
 [10]|       |イカ    |8:00    |11:00   |A  |  1|   |   
 [11]|       |マグロ  |11:00   |15:00   |D  |  1|   |   
 F2 0を入力
 F3 =(D2>D3)+F2
 下にフィルコピー
 H1 =COUNTIFS(E:E,G1,F:F,0,D:D,">=13:00")+COUNTIFS(E:E,G1,F:F,1,D:D,"<=13:00")
 下にフィルコピー
(sy) 2017/05/05(金) 13:51

syさんありがとうございます。
Hの値が0となるのはA〜E列のデータは別シートから数式で参照しているからでしょうか?
(クマ) 2017/05/05(金) 15:54

 実際がどう言うレイアウトで、中のデータも分からないので、お答えできません。
 取り敢えず各列の式を全て提示して下さい。

 それとどの式を使ってるのでしょうか?
 時間データに日付情報を持っていないなど、データ集計には最も不向きなレイアウトなので、
 一発で求めるような式ではちょっとした事で不具合が起きても不思議ではありません。

 作業列を使うのを強くお勧めします。

(sy) 2017/05/05(金) 18:48


 >Hの値が0
 と言う事は作業列の式を使用してるんですね?

 D列の実際のデータは時刻型になっていますか?
 元のデータが書式設定で文字列になっていたりしませんか?

 後E列はアルファベットのみですか?
 G列の条件はE列と同じ文字で無ければいけません。

 今の情報だけで考えられる原因はこれくらいでしょうか。

(sy) 2017/05/05(金) 20:04


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.