[[20130325172522]]　『最終退出時間を取り出したい』（いとう）　ページの最後に飛ぶ

『最終退出時間を取り出したい』（いとう）

 よろしくお願いします。

 ある部屋に入退出にカードが必要で、そのカードの記録があります。
ID	日時
100001	2013/3/1 10:54
100002	2013/3/1 12:12
100003	2013/3/1 13:28
100004	2013/3/1 14:35
100005	2013/3/1 17:46
100006	2013/3/1 17:56
100007	2013/3/4 8:57
100004	2013/3/4 11:35
100002	2013/3/4 17:01

 こんな感じで１か月分あります。
記録の数は日付によって違います。
この中から、各日付で一番最後の時間を出したいです。

 別のシートに
2013/3/1の最終退出時間は17:56
2013/3/4の最終退出時間は17:01
とわかるようにしたいです。

 計算式ででるでしょうか？
よろしくお願いします。

 Windows 7　の　Excel2010　です。

 Sheet1のB列に日時、C列に退出時刻、1行目は見出しとして
 別シートののA1へ3/1、A2へ3/4と入力されている場合

 別シートのB1セルへ
 =IF(A1,MAX(INDEX((Sheet1!B$2:B$100=A1)*(Sheet1!C$2:C$100),)),"")
 下へコピー、かな？
(Jera)

 Jeraさん、さっそくありがとうございます。
そしてごめんなさい。
日付と時間は同じセルなんです。
書式設定が「yyyy/m/d h:mm」になっていて、実際は秒まで入っています。

 これ、別のセルに日付と時間を取り出したほうがいいでしょうか？
あ、でもこれ、見かけだけだから、区切り位置でわけられないですよね？
ちょっと調べてきます。
（いとう）

 区切り位置で分けられました！びっくりしました！区切り位置すごい。

 そしてJeraさんの数式で計算されました！ありがとうございます。
どうして掛け算で出るのかが謎なのですが、これを手掛かりに調べます。
（あと、IFの条件文のところがA1だけでOKなのもなぜ！）

 本当にありがとうございました。
（いとう）

 D列に　　D1:｛=SMALL(IF(INT($B$1:$B$200)<>INT($B$2:$B$201),$B$1:$B$200,""),ROW(A1))｝
 配列数式なので　Ctrl+Shift+Enterで確定
 書式　　「yyyy/m/d"の退出時間は" h:mm」
 下へコピー　　　　　　(NB)

 NBさん、ありがとうございます。
なんだか不思議です。
範囲が１セルずれてないとダメなんですね。
最大の整数と範囲のずれた最大の整数がイコールじゃないところの数値で一番小さいもの？
なぜそれで結果が出るのかがすごく不思議です。
明日からExcelの本をにらめっこです。

 とにもかくにも、yyyy/m/d h:mmの状態で計算できました！
ありがとうございます。
しかも書式もご指導いただいて。
こんな書式もできるんですね！＆でくっつけてましたよ。
ありがとうございました。
（いとう）

 なるほど…私がやると、結局こんな風になっちゃいましたorz
 入力セルはさっきと同じです。
 =IF(A1,MAX(INDEX((TIME(HOUR(Sheet1!B$2:B$100),MINUTE(Sheet1!B$2:B$100),SECOND(Sheet1!B$2:B$100)))*(INT(Sheet1!B$2:B$100)=A1),)),"")

 けどまぁ、時間表示を分けれるなら分けた方がいいですね。

 >どうして掛け算で出るのかが謎なのですが

 分けた表で、簡単な例で簡単に説明します。

     A      B      C      D     E     F
 1  見出1  見出2  見出3
 2         3/1    2             3/1   *
 3         3/1    8             3/2   * 
 4         3/4    9
 5         3/4    1
 6         3/4    10

 こんな表がある時にF2セルへ
 =(B$2:B$6=E2)*(C$2:C$6)として下へコピーして下さい。
 F2セルの計算結果について説明します。

 前半の(B$2:B$6=E2)の計算結果として
 {TRUE;TRUE;FALSE;FALSE;FALSE}
 という配列が返ります。

 後半の(C$2:C$6)の計算結果として
 {2;8;9;1;10}
 という配列が返ります。

 四則演算を行うとTRUE=1、FALSE=0として扱われるので、二つの配列を掛け合わせる事で
 {2;8;0;0;0}
 となります。

 これにMAX関数を合わせる事で、[E列で指定した日付]且つ[その中の時間の最大値]を求めています。

 IF関数については、第一引数が「0以外の数値」の場合は、TRUEとして扱われます。
 この場合は1じゃなくてもTRUEとして扱われるわけですが、内部事情はよく知りません。
(Jera)

 少し解説します。配列数式なので、途中の計算結果は、"データの連なり"となります。
 " 2013/3/1 10:54"は書式を「標準」とすると"41334.45416"となり、数値のデータです。
　また日付までが整数で、小数点以下が時刻を表します。
 なので=INT(B1)="2013/3/1"で日付のみになります。
 ある日付での最終時刻はある日付時刻の「日付」と次の日付時刻の「日付」が異なっている場合のその日付時刻になります。
 IF式では、条件に合っている場合はその日付時刻を返し、合わない場合は""となります。
 数値データはその条件に合致した(求める)日付時刻の連なりが得られます。この日付時刻を小さい順に求めていきます。
 作業列として
 C1:=IF(INT(B1)<>INT(B2),B1,"")  下へコピー　　で
 C列に求める　日付時効が得られます。
 配列数式は同じことを作業列なしで計算します。　　(NB)

 すごい解説に朝から挙動不審となって、先輩に心配されたいとうです。おはようございます。
お二方、ありがとうございます！
実はあの後、Excelの本を読みながら帰ったんですが、さっぱり解らなかったんです…
今日はネットで検索だなーと思っていたら。素晴らしい解説が。
夜遅くまですみません。ありがとうございます。

 Jeraさん、yyyy/m/d h:mmの状態での式をありがとうございます！
一度時間のほうを分解しちゃうんですね。こうすればよかったんですね！
そして掛け算の解説もありがとうございます。
わかりやすかったです！
配列は高校時に習ったはずですが、解説を見てやっと思い出しました。
当時はこんな役に立たないもの・・・って思っていたんですよね。実は便利だったんだなあ。

 あと、
 >IF関数については、第一引数が「0以外の数値」の場合は、TRUEとして扱われます
そうだったんですか！
つまりあの =IF(A1, は =IF(A1<>0, ということに！
これは便利ですね。
丁寧な解説、ありがとうございました！

 NBさん
 >また日付までが整数で、小数点以下が時刻を表します。
これも知りませんでした！　日付と時間が数値だってことは知ってたんですが・・・
なるほど、それでINTをつかうのですね。

 >ある日付での最終時刻はある日付時刻の「日付」と次の日付時刻の「日付」が異なっている場合のその日付時刻になります。
いわれて初めて気が付きました。すごい思い付きです！
それで各日付が１つずつが残るからSMALLで取り出すのですね。やっと解りました。
すごいスッキリ感です。

 作業列での方法もありがとうございました！
これをまとめる（？）と配列になるのですね。
考え方はよくわかりました。が、多分自分ではこのずらせばいい、ということを思いつかないと思います。
しっかりメモして応用もできるよう頑張ります。
詳しい解説、ありがとうございました！

 （いとう）

 日付+時間が昇順でならんでるなら

 E2に日付
 F2 =SMALL(B:B,COUNTIF(B:B,"<"&E2+1))

 (GobGob)

 あぁ、日付も全て自動なんですな。

 オイラの回答無視してくださいｗ。

 (GobGob)

 気が付くのが遅れまして失礼しました。
GobGobさん、ありがとうございます！

 >あぁ、日付も全て自動なんですな。
いえ、日付は最初、自分で入力してました。
例でいうならE2に3/1って入れて、F2に時間出して、G2に「2013/3/1の最終退出時間は17:56」って出してたんです。
ですので、GobGobさんのでも出ました！
E2が3/1として+1で3/2よりもちいさいものの数を数えて、その数番目の物を出すと。
日付のうちで一番大きいものを出そう、という発想ではなくて、小さいほうから何番目か、ですね。
びっくりいたしました。

 あれ、でもこれ、昇順でなきゃだめなんですか？
今回のデータは昇順ですけど、COUNTIFもSMALLも昇順じゃなくてもでますよね？

 しかし皆さんの発想が素晴らしいです。
とても思いつかないです。
しっかりとノートをとって、ぜひ今後も役立てたいです。
ありがとうございました。
（いとう）

 そのB列（？）の「日時」は手入力？
 それともどっかから取り込んだもの？

 手入力なら日付と時刻は列を分けて入力する。
 外部から取り込んだものなら区切り位置で日付と時刻を分ける。

 日付と時刻が分かれていればピボットテーブルでやるのが簡単です。
 集計方法を「最大値」にするだけ。

 最大値 / 時刻	
 日付	 　　集計
 2013/3/1	 　　17:56
 2013/3/4	 　　17:01

 日付も含めて入力作業は一切必要なし。
 マウス操作だけで一発です。

 （よみびとしらず）

 >日付+時間が昇順でならんでるなら

 二分探索での回答を考えてたときの名残りで、消し忘れｗ。
 無視してくださいな。

 (GobGob)

 E列日付入力案で。

 オイラの回答、一応、不十分なんで。

 F2 =IF(D2="","",TEXT(IF(INT(SMALL(B:B,COUNTIF(B:B,"<"&D2+1)))=D2,SMALL(B:B,COUNTIF(B:B,"<"&D2+1)),""),"yyyy/m/dの最終退出時間はh:mm"))

 (GobGob)

 またもや気が付くのが遅くてすみませんでした。

 よみびとしらずさん
データは「どっかから取り込んだもの」です。
ので、区切り位置でわけてピボット試してみました！
集計方法で「最大値」というのがあるんですね…知りませんでした。
というか、ピボットはマトリクス表をつくる機能だとばかり。
こんなこともできるんですね。勉強になりました！
これは覚えておかねば。
ありがとうございます！

 　
 GobGobさん
 >消し忘れｗ。
了解ですｗ

 >不十分なんで。
ええ？どこが？って思ったら日付がない場合に対応されてるんですね！
しかも書式設定が不要になってる！
すごすぎです、ありがとうございます。

 皆さんすごすぎです。どの方法も光ってます。わくわくします。
本当にありがとうございました！
 （いとう）

コメント返信：

[ 一覧(最新更新順) ]

[[20130325172522]] 『最終退出時間を取り出したい』（いとう） ページの最後に飛ぶ

[[20130325172522]]　『最終退出時間を取り出したい』（いとう）　ページの最後に飛ぶ