
我有這樣的連續到達和出發時間清單:
| 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./CSE食譜MIN
MAX
MIN
對於“IN”:
=MIN(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99)))
這就是他們所說的數組或“CSE”公式 - 在公式欄中輸入它,然後按Ctrl Shift Enter將其輸入為數組公式,然後向下複製。
這很直觀,對吧?從matches和matchesC
處取得最小值。F
A
G
B
不過,這只能得到最小的 IN 值...在每隔一行中進行交換會得到最大的 OUT 值...但是誰有時間這樣做呢MAX
?MIN
讓我們用一個簡單的方法將它們粉碎在一起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,然後向下複製。
注意:
- 根據需要調整儲存格引用
- 您沒有明確說明資料輸入的形狀;我使用 Excel 的「文字到列」來分隔每個參數,這樣我就可以獨立地對日期和時間進行操作。
- 不包含任何錯誤捕獲,並且該解決方案假設您已經設定如圖所示的所需測試值。這應該很容易複製您的輸入 IN/OUT 和 DATE 列,並使用「刪除重複項」來產生要匹配的唯一清單。
- 我在 column 中的內容沒有特殊的格式
C
。如圖所示,這是簡單的文字。您可能需要使用格式刷(取自該專欄)在你的最終輸出列上! - 忽略我的專欄
D
,我在將其放在一起時用作簡單的錯誤檢查,並且在截圖之前忘記刪除。