Excelで困っています

エクセルで入退出をしようとしています
基のデータが下記の様になっているのをタイムカード形式に条件を付けて取り出しをしたいのですが上手くできませんでした。
ご教授お願いします。
【元データ】
　   A　　　　　　　　　B　
1 日時                            ID
2 2010/10/30 21:34    000-0000001
3 2010/10/30 7:47      000-0000006
4 2010/10/30 7:38      000-0000001
5 2010/10/30 0:43      000-0000001
6 2010/10/29 19:22    000-0000001
7 2010/10/29 19:00    000-0000001
8 2010/10/29 18:32    000-0000008
9 2010/10/29 18:31    000-0000007
これをID毎に下記の様に（000-0000001の例）別シートに表示させようとしてもうまくできません。
A　　　　　　　B　　　　　C
1 　日付　　入室時間　　退室時間
2 10月30日     7:47       　  　0:43
3 10月29日    19:00　　　　19:22
※同じIDで同じ日付の入退出が複数ある場合は一番小さい（最初に入室した）時間と一番大きい（最後に退出した）時間をタイムカードの様に並べてその間のではいりは必要ありません。
どうか宜しくお願いします。

No.5 ベストアンサー 回答者： MackyNo1 回答日時： 2011/01/07 02:10

>#VALUE!が出ます。

INT(Sheet2!$A$2:$A$1000)=$A2の計算式で#VALUE!が出ています。
原因はブランクのセルです。

本当に空白セルなら（文字列セルが含まれていないなら）、セル範囲を大きめに設定しても#VALUEエラーはでないはずです。

もしA列のデータが通常の入力データではなく、空白セル部分に空白文字列が入力されている（例えば数式の空白文字列「""」を値に変換している）のなら、A列を選択して「データ」「区切り位置」で「完了」してください。

データ範囲に文字列が入っていても、そのまま無視して計算するなら、以下のような数式にする必要があります。

=MAX((IF(ISNUMBER(Sheet2!$A$2:$A$1000),INT(Sheet2!$A$2:$A$1000),)=$A2)*(Sheet2!$B$2:$B$1000=$D$1)*IF(ISNUMBER(Sheet2!$A$2:$A$1000),MOD(Sheet2!$A$2:$A$1000,1),))

この回答へのお礼

ありがとうございます(^-^)
成功しました！

お礼日時：2011/01/09 13:31

No.4 回答者： tom04 回答日時： 2011/01/06 21:15

こんばんは！

横からお邪魔します。

外していたらごめんなさい。

質問文ではA列に日時とB列にIDが羅列してあるだけだと思いますので、

同じIDが奇数回の場合は「入室中」ということになり、退室していないと考えるのが普通だと思いますが、
一応それは無視してその日の最小時刻・最大時刻を表示する場合の一例です。

IDが000-0000001の人の場合10月30日の入室時間（一番早い時刻）は　0：43
退室時間（一番遅い時刻）は　21：34　になってしまうのですが・・・

↓の画像のように作業用の列を3列使ってしまいました。
（この作業列が目障りであれば遠く離れた列に作成するか、非表示にしてください）

Sheet2のA1セルにIDを入力するとします。
作業列E2セルに
=IF(COUNTIF(Sheet1!$B$1:$B$1000,$A$1)<ROW(A1),"",INDEX(Sheet1!$A$1:$A$1000,SMALL(IF(Sheet1!$B$1:$B$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1))))
これは配列数式になってしまいますので、この画面からSheet2のE2セルに貼り付け後数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定してください。
数式の前後に｛　｝マークが入り配列数式になります。

F2セルに（←配列数式ではありません。）
=IF(E2="","",INT(E2))

G2セルに（←配列数式ではありません。）
=IF(OR(F2="",COUNTIF($F$2:F2,F2)<>1),"",ROW())
E2～G2セルを範囲指定し、G2セルのフィルハンドルでオートフィルで下へずぃ～～～！っとコピーしておきます。

そしてA3セルは（←表示形式は「日付」にしておきます）
=IF(COUNT(G:G)<ROW(A1),"",INDEX(F:F,SMALL(G:G,ROW(A1))))
（配列数式ではありません）

B3セル（←配列数式になります。Shift＋Ctrlキー＋Enterキーで確定）※表示形式は「時刻」に！
=IF(A3="","",MIN(IF($F$1:$F$1000=A3,$E$1:$E$1000)))
C3セル（←これも配列数式になります）　※表示形式は「時刻」に！
=IF(A3="","",MAX(IF($F$1:$F$1000=A3,$E$1:$E$1000)))
という数式を入れ、A3～C3セルを範囲指定し、C3セルのフィルハンドルで下へコピーすると
画像のような感じになります。

以上、参考になれば良いのですが
的外れならごめんなさいね。m(__)m

この回答へのお礼

有難うございます(^-^)
とても参考になりました。

お礼日時：2011/01/09 13:37

No.3 回答者： MackyNo1 回答日時： 2011/01/06 17:47

>試してみると日付の比較でエラーが出ているようでうまく計算できません。

確認ですが、入力後Ctrl+Shift+Enterの操作を行っているのでしょうか？

また、具体的にどのようなエラーが出るのでしょうか？

＞表示形式は通常の日付になっています。
＞いろいろ形式を変えてみましたがダメでした。

時刻形式にすると、具体的にどのように表示されるのでしょうか？

上記の数式で、基本的にこちらではうまくいくことを確認していますので、Sheet2に同じレイアウトのデータをコピーして、提示した数式をコピーしてCtrl+Shift+Enterで確定してうまく表示できないか調べてみてください。

この回答への補足

#VALUE!が出ます。
INT(Sheet2!$A$2:$A$1000)=$A2の計算式で#VALUE!が出ています。
原因はブランクのセルです。
シート2のA３４２まで値があった場合、A343が計算範囲に指定されるとうまく抽出できませんでした。
宜しくお願いします。

補足日時：2011/01/06 19:41

No.2 回答者： MackyNo1 回答日時： 2011/01/06 09:12

元データがSheet2にあり、抽出用シートのA2セル以下に抽出したい日付が入力されていて、E1セルに抽出したいIDが入力されている場合、以下の数式で特定のIDに対する、それぞれの日付の最も早い入室時間と遅い退出時間を表示することができます。

B2セル
=MIN(IF((INT(Sheet2!$A$2:$A$1000)=$A2)*(Sheet2!$B$2:$B$1000=$E$1),MOD(Sheet2!$A$2:$A$1000,1),""))

C2セル
=MAX((INT(Sheet2!$A$2:$A$1000)=$A2)*(Sheet2!$B$2:$B$1000=$E$1)*MOD(Sheet2!$A$2:$A$1000,1))

上記の数式は配列数式ですので、入力後Ctrl+Shift+Enterで確定して下方向にオートフィルしてください。
また、セルの書式は表示形式を通常の「時刻」にしてください。

ただし、上記の数式は多用するとシートの動きが重くなるので、D1セルのIDを入力する欄は入力規則のリストで選択させるようにして、この1枚のシートで対応するようにした方が良いと思います。

この回答への補足

計算式、ありがとうございます。
試してみると日付の比較でエラーが出ているようでうまく計算できません。
表示形式は通常の日付になっています。
いろいろ形式を変えてみましたがダメでした。
宜しくお願いします。

補足日時：2011/01/06 13:20

No.1 回答者： MackyNo1 回答日時： 2011/01/06 01:54

質問内容にいくつかの不明点があります。

IDごとの集計で10月30日のデータは、退室時間よりも入室時間の方が早いのですがこれでOKですか？

単純に、特定のID番号で同じ日付の最も早い時間と最も遅い時間を抽出することは、比較的簡単な配列数式で表示できますが、上記の0：43は前日のデータに変換する場合はかなり複雑な数式を駆使する必要があります。
その場合も当日と前日の判断基準（例えば5：00はどちら？）を明示しないと計算できません。

ちなみに日付の欄も元データの日時から重複のない日付データを自動的に取得したいのでしょうか（ちなみに、この数式だけもかなり複雑な数式となります）。

この回答への補足

申し訳ありません。
10月30日の入室時間と退出時間、間違えていました。
入室時間が0:43 退出が21:34です。
特定のIDで抽出で0:43の変換、日付の取得は必要ありません。
宜しくお願いします。

補足日時：2011/01/06 06:51