『最終退出時間を取り出したい』（いとう） よろしくお願いします。 ある部屋に入退出にカードが必要で、そのカードの記録があります。 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さん >消し忘れｗ。 了解ですｗ >不十分なんで。 ええ？どこが？って思ったら日付がない場合に対応されてるんですね！ しかも書式設定が不要になってる！ すごすぎです、ありがとうございます。 皆さんすごすぎです。どの方法も光ってます。わくわくします。 本当にありがとうございました！ （いとう）