エクセルで、人ごとの集計を時刻ごとにする方法

質問

先ほどの質問が説明不足でありましたので、再質問させていただきます。
エクセルでデータをとっておりますが、分析するにあたっての質問です。
現在人ごとに記録してあるデータを、時刻ごとの集計にしたいです。
イ、ロ、ハ、、、のような部屋があって、誰が（ＩＤ）、何時から何時まで、どこの部屋を使ったかのデータがあります。（正確には、各ＩＤの属性と行動のデータもあります）
これを利用して、すべての時刻において、どの部屋が誰に使われていたかを一覧にする方法がありますでしょうか。
「横軸に部屋番号、縦軸に時刻を表した行列の中に、ＩＤ番号、あるいは空室を示す記号を表示する」
というようなイメージでいますが、具体的な方法がわかりません。
添付の画像の左側が現在のデータのサンプルデータ、右側が目指したい状態です。
ちなみに部屋は１２、時刻は１から２０００くらいまであります。（記録の都合上、１０秒おきの時刻で、１は１０時、２は１０時０分１０秒、、、のような形です）
特に困っているのは、途中の時刻の処理です。（VLOOKUPを使うにあたって）
例）ID３の人が時刻５から１０まで部屋イを使用した場合に、部屋イの列、時刻７のセルにも、「３」と表示させるようにする方法、ということです。
よろしくお願いします。

最終的な目的は、ある人が部屋を使い始めた時刻において、他の部屋が誰に使われていたかを調べることです。
ID３の人が時刻５で部屋イに入った瞬間、部屋ロ、ハ、、、には誰が（どんな属性の人が）入っていたOR空室だったか。
を調べ、空室か否か（その他周りの人間の属性）で使われやすさが変わるかを調べたいです。
つまり、最終的には人ごとのデータにもどり、部屋数分の列を追加して部屋が埋まってるかどうかを０１で返すようなことを考えています。（ここから先はSPSSを使う予定です。）

オフィス（エクセルとアクセス）は私のＰＣにインストールされていますので、それでできるのかどうかご教示ください。

回答No.4

bunjii · ベストアンサー

>オフィス（エクセルとアクセス）は私のＰＣにインストールされていますので、それでできるのかどうかご教示ください。
Excelでできます。
但し、VLOOKUP関数で抽出することはできないでしょう。
質問に添付画像の模擬データをそのまま使ってExcel 2013で検証しました。
左側をSheet1、右側をSheet2としてSheet2!B2へ次の数式を設定します。
=IF(SUMPRODUCT((Sheet1!$B$2:$B$4<=$A2)*(Sheet1!$C$2:$C$4>=$A2)*(Sheet1!$D$2:$F$4=RIGHT(B$1,1))*(Sheet1!$D$2:$F$4=RIGHT(B$1,1))),INDEX(Sheet1!$A:$A,SUMPRODUCT((Sheet1!$B$2:$B$4<=$A2)*(Sheet1!$C$2:$C$4>=$A2)*(Sheet1!$D$2:$F$4=RIGHT(B$1,1))*(Sheet1!$D$2:$F$4=RIGHT(B$1,1))*ROW(B$2:B$4))),"")
Sheet2!B2セルを右と下へ必要数コピーします。
但し、模擬データの範囲で数式を組み立てていますので範囲が広くなる時は行数と列数を適宜修正してください。
基本的にはSUMPRODUCT関数の引数内で論理演算を行い抽出すべき行番号を算出しています。
算出された行番号を使ってINDEX関数でSheet1のA列から目的の値を抽出します。

お礼

ありがとうございます！
思っていた計算ができました。
ところで、
SUMPRODUCTの中で、
(Sheet1!$D$2:$F$4=RIGHT(B$1,1))
を繰り返す（2乗する？）理由はなぜでしょうか？
もし可能でしたらご教示ください。

回答No.8

Nouble

済みません、謝罪します
色気を出したばかりに、空室処理が甘かったですね
修正しました
https://1drv.ms/x/s!AjviygfJDgV_kR_E2x6mEqYnWSJg

参考URL：

https://1drv.ms/x/s!AjviygfJDgV_kR_E2x6mEqYnWSJg

お礼

遅くなりました。
ありがとうございます！
使わせていただきます。丁寧に作っていただき心より感謝いたします！！

回答No.7

Nouble

お待たせしました
出来ました

此所に置きますね
https://1drv.ms/x/s!AjviygfJDgV_kRuF3Z8qbZHJlPy-
消去する可能性が、あるので
早めに、ダウンロード　くださいね

但し、ご覧のように
まずは、お試し版です

どう違うか
と、いうと
検索キーが、時間だけの　限定版です

抽出条件で、指定する行と
結果抽出対象行を
OFFSET文等で
臨機応変に、変動させれば
複数キー検索に、移行できる
と、思います

ので
そうは、難しくない
の、ですが

まあ此は、お試し版です

参考URL：

https://1drv.ms/x/s!AjviygfJDgV_kRuF3Z8qbZHJlPy-

回答No.6

Nouble

直ぐに欲しい
との事、ですが

キー値を、指定すると
該当データを表す

そんな
エクセルシート作成に
明日から、挑んでみます

失敗しても、
其の旨、必ず報告します

ので、
暫し閉め切らずに、お待ち頂けますか？

もし、完成したら
ファイルで、お渡ししますね、

回答No.5

bunjii

>SUMPRODUCTの中で、
>(Sheet1!$D$2:$F$4=RIGHT(B$1,1))
>を繰り返す（2乗する？）理由はなぜでしょうか？
単純なチェックミスです。
1つでも同じ結果になりますので省略してください。
SUMPRODUCT((Sheet1!$B$2:$B$4<=$A2)*(Sheet1!$C$2:$C$4>=$A2)*(Sheet1!$D$2:$F$4=RIGHT(B$1,1))*(Sheet1!$D$2:$F$4=RIGHT(B$1,1)))
　　　　　　　　　　↓
SUMPRODUCT((Sheet1!$B$2:$B$4<=$A2)*(Sheet1!$C$2:$C$4>=$A2)*(Sheet1!$D$2:$F$4=RIGHT(B$1,1)))

SUMPRODUCT((Sheet1!$B$2:$B$4<=$A2)*(Sheet1!$C$2:$C$4>=$A2)*(Sheet1!$D$2:$F$4=RIGHT(B$1,1))*(Sheet1!$D$2:$F$4=RIGHT(B$1,1))*ROW(B$2:B$4)))
　　　　　　　　　　↓
SUMPRODUCT((Sheet1!$B$2:$B$4<=$A2)*(Sheet1!$C$2:$C$4>=$A2)*(Sheet1!$D$2:$F$4=RIGHT(B$1,1))*ROW(B$2:B$4)))

論理演算の結果は1または0にしかなりませんので何乗しても結果は同じです。
数式が長くなったのでチェックミスが起こったようです。

お礼

ありがとうございます。

回答No.3

ushi2015

こんにちは
図の左側のデータの有るシートのシートタブを右クリックして「コードの表示」
を選択して表示されたVBE画面に
Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim tSh As Worksheet
　　Dim r As Range
　　Dim v As Variant
　　Dim s As Variant
　　Dim t As Variant

　　'上から順に入力されるとして
　　If Intersect(Target, Range("A1").CurrentRegion) Is Nothing Then Exit Sub

　　'部屋の入力の有無
　　If WorksheetFunction.CountA(Target.EntireRow.Range("D1:F1")) = 0 Then Exit Sub

　　'時間の入力の有無
　　If WorksheetFunction.CountA(Target.EntireRow.Range("B1:C1")) = 0 Then Exit Sub

　　'IDの入力の有無
　　If Target.EntireRow.Range("A1") = "" Then Exit Sub

　　Set tSh = Worksheets("Sheet2")

　　If Target.Column >= 4 Then
　　　　v = Application.Match("部屋" & Target, tSh.Rows(1), 0)
　　　　If IsError(v) Then MsgBox "該当の部屋が有りません", vbExclamation: Exit Sub
　　　　s = Application.Match(Target.EntireRow.Range("B1"), tSh.Columns(1), 0)
　　　　If IsError(s) Then MsgBox "該当の開始時間が有りません", vbExclamation: Exit Sub
　　　　t = Application.Match(Target.EntireRow.Range("C1"), tSh.Columns(1), 0)
　　　　If IsError(t) Then
　　　　　　If Target.EntireRow.Range("C1") = "" Then
　　　　　　　　t = s
　　　　　　Else
　　　　　　　　MsgBox "該当の終了時間が有りません", vbExclamation: Exit Sub
　　　　　　End If
　　　　End If
　　　　With tSh
　　　　　　.Range(.Cells(s, v), .Cells(t, v)).Value = Target.EntireRow.Range("A1")
　　　　End With
　　End If
End Sub
をコピーし貼り付けて、
Set tSh = Worksheets("Sheet2")
のシート名を図の右側のデータが有るシートの名前に変更してから
データを入力してみて下さい。
時間の端数の部分の事が良く分からないですが、適当に修正して下さい。
１は１０時、２は１０時０分１０秒？？？
時間では無くて整数で入力してあるという事で、途中の時刻とは10秒未満の時間
の事ですか？

お礼

遅くなりました。上から頑張って解読していました。
丁寧に作っていただき、本当にありがとうございます。
データがない場合の処理をいれていただき、スムーズに変換ができそうです。
ありがとうございました！

回答No.2

imogasi

質問し直しをしていただいたようですが、私から言うと、ほとんど前質問と変わっていない。
時刻を時間（刻）帯（範囲）で整数コード化したいというのが主目的ですか？
1単位の時間幅＝小数で割って、適当に丸めると、整数化できないかな。
私は質問の意味が十分分からず、的確な回答はできませんが、続く他の回答者のために補足しておいたらどうでしょう。
ーー
（現状あるデータのセルの値について）
それに時刻は、エクセルでは標準では、日付時刻シリアル値になるのが標準ですが、そうなってますか。セルに入っているのは、文字列ではないでしょうね？＜－－最大の私の疑問点
この辺をはっきり認識していないエクセルの質問者が非常に多いようだ。
確認方法は、
テストデータとして、
Ａ１セルに０:10:30と（半角で）入れた場合、０時１０分３０秒の意味になり、
ＶＢＡで標準モジュールに
Sub test01()
MsgBox Cells(1, "A").Value
End Sub
と入れて実行すると、Ａ１セルの「値」（セル＝Ｒａｎｇｅの属性のうちの１つである「値」）が取れます。
（またはセルの書式を数値に適用すしてもわかる。）
関数での計算は　=10/(24*60)+30/(24*60*60)の式を入れても良い。
これでどうなりますか。数値が表示されますか？
ーー
質問のデータは、物理や化学の実験の測定データを（むつかしい分野部分だけを）一部状況説明を変えて作ったのでしょうか？普通ビジネスの会議室の使用時間などでは、秒単位で考えることはないと思うので不思議。質問のデータの由来状況も回答のヒントで重要と思うので、換骨奪胎したなら注記ぐらいしないと読者は混乱するように思う。

補足

度々のご指摘ありがとうございます。

質問時点での主目的は、任意の時刻における場の状況(各部屋が、誰に使われているのか、空室なのか)を抽出するためのデータ(時刻を縦軸とする表)を作成することです。

また元データは、対象地を10秒置きに撮影した写真を観察したときの記録です。現在時刻に整数値が入力されているのは、全ての写真に1から連番をつけたときの、写真の名前を入力したからです。

データを記録する際、変化(入室、退室、移動)のおきた時点だけ記録してあります。

整数値を実際に時刻に治すことは容易ですが、後の分析(回帰分析等)に使うとき10進数であったほうが簡易であるためそのまま利用しています。
混乱を招く説明で失礼いたしました。

回答No.1

ushi2015

こんにちは
添付図を見ると同じIDの人が同じ時間帯に二つの部屋を使っていますが
そのようなケースが有るのですか？
また一つの部屋を同じ時間帯に二人以上で使用する事は無いのですか？

補足

言葉足らずですみません、補足させて頂きます。
質問にそのまま答えると、前者はあり、後者はありません。
一つのIDで使える部屋数は1以上、部屋に入る人数は1(または0)です。