☰

Excel: 日付と時刻のリストで各日付の最も早い/最新の値を見つける

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
Excel: 日付と時刻のリストで各日付の最も早い/最新の値を見つける

次のような到着時間と出発時間の連続リストがあります:

| IN/OUT | DATE/TIME             |
|--------|-----------------------|
| IN     | 1/2/2019  8:25:59 AM  |
| OUT    | 1/2/2019  11:51:59 AM |
| IN     | 1/2/2019  12:48:59 PM |
| OUT    | 1/2/2019  4:45:59 PM  |
| IN     | 1/3/2019  9:08:59 AM  |
| OUT    | 1/3/2019  12:38:59 PM |
| IN     | 1/3/2019  3:23:59 PM  |
| OUT    | 1/3/2019  4:59:59 PM  |
| IN     | 1/4/2019  8:49:59 AM  |
| OUT    | 1/4/2019  5:11:59 PM  |

このリストから抽出する必要があるのは初めそして最後各日のエントリ（平均到着時間と出発時間、所要時間などを計算するために使用されます）。

たとえば、上記の表から次の内容を抽出します。

| IN/OUT | DATE/TIME             |
|--------|-----------------------|
| IN     | 1/2/2019  8:25:59 AM  |
| OUT    | 1/2/2019  4:45:59 PM  |
| IN     | 1/3/2019  9:08:59 AM  |
| OUT    | 1/3/2019  4:59:59 PM  |
| IN     | 1/4/2019  8:49:59 AM  |
| OUT    | 1/4/2019  5:11:59 PM  |

VLOOKUP を使用してすべての「IN」または「OUT」値を取得できることはわかっていますが、問題は、リスト内のほとんどの日に複数の値があることです。最初と最後の値だけが必要なのですが、その方法がわかりません。

ヘルパー テーブルの有無にかかわらず、VLOOKUP を使用してこれを実現する方法はありますか? あるいは、別の方法の方がうまくいくかもしれませんか?

答え1

1. 質問する前に必ず検索する

複数の条件を使用して日付のリストに対して Excel で MAX() を使用する

「関連」の下をご覧ください。

関連クエリ

2. MIN/ MAXCSEレシピ

サンプル画像

MIN'IN'の場合:

=MIN(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99)))

これは配列または「CSE」数式と呼ばれるもので、数式バーに入力し、 を押してCtrl Shift Enter配列数式として入力し、下にコピーします。

Cこれは直感的にわかりますよね?がFに一致しA、Gが に一致するから最低値を取得しますB。

ただし、これは最小の IN 値しか取得しません... 1 行おきに を交換すると、MAX最大MINの OUT 値が得られます... しかし、そんな時間のある人がいるでしょうか? 単純な でこれらをまとめてみますIF:

3. 最終式

最終式

=IF(F2="IN",MIN(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99))),MAX(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99))))

もう一度、F2 にその数式を入力し、押してCtrl Shift Enter下にコピーします。

注意:

  1. 必要に応じてセル参照を調整する
  2. データ入力の形状についてはあまり明示的に述べられていませんでしたが、Excel の「テキストを列に分割」を使用して各引数を分離し、日付と時刻を個別に操作できるようにしました。
  3. エラー キャッチは含まれておらず、ソリューションでは、必要なテスト値が既に示されているように設定されていることを前提としています。入力 IN/OUT および DATE 列をコピーし、「重複の削除」を使用して一致する一意のリストを生成するのは簡単です。
  4. 列の内容には特別な書式設定はありませんC。表示されているとおり、単純なテキストです。おそらく書式ペインターを使う必要があるでしょう（そのコラムより）最終出力列に表示されます。
  5. Dこれをまとめる際に簡単なエラー チェックとして使用した列は無視してください。スクリーンショットを撮る前に削除するのを忘れました。

関連情報