tinhon

我試緊呢段excel

=IF(COUNTIFS($B$1:$B$7,"<="&C1,$A$1:$A$7,A1)+COUNTIFS($C$1:$C$7,">="&B1,$A$1:$A$7,A1)>2,TRUE,FALSE)

s84292

呢個問題,最後我係用微軟teams個排更功能,貪佢可以匯出excel

excel 都係唔太適合呢個用法

bongbong3481

回覆 5# Aÿ


學到野，吾寫vba，原來 sorting 完先易解決

Aÿ

回覆  Aÿ


學到野，吾寫vba，原來 sorting 完先易解決
bongbong3481 發表於 2023-5-27 19:28

當然，根本system 跑個report出黎無sorting已經係病態
raw data出左csv/excel唔俾執都係病態

sort完 A同B column 之後，淨對兩個cell就check到overlap，例如AND(A2=A1,B2>C1)
當然，作為識寫program嘅人，又或者唔想人搞你formula/code嘅人，是但啦，點做無所謂

dipsy

假設以上為返工時間，好明顯第5列嘅AA 開始時間撞咗第2列嘅AA，有無得寫條formula去檢查呢啲相撞呢？ ...
tinhon 發表於 2023-5-23 23:08

我問 Bing AI, 佢咁答, 你試吓:
To check overlapping check-in and check-out times in an Excel table, you can use a formula that identifies overlapping dates and times. Here's how:

1. Create a new column next to your check-in and check-out times.
2. In the first cell of the new column, enter the following formula: =SUMPRODUCT(($B$2:$B$10<=B2)*($C$2:$C$10>=C2))>1
3. Copy the formula down to the other cells in the column.
4. The cells with "TRUE" values indicate overlapping check-in and check-out times.

This formula checks if each start date is less than any of the end dates in the table AND if each end date is greater than any of the start dates in the table¹.

I hope this helps! Let me know if you have any other questions.

來源: 與 Bing 的交談， 30/5/2023
(1) Identify overlapping dates and times in Excel - My Online Training Hub. https://www.myonlinetraininghub. ... and-times-in-excel.
(2) Identify Overlapping Dates and Times in Excel - EASY Formula. https://www.youtube.com/watch?v=V9jQ_Mitd-s.
(3) Grouping overlapping times in MS Excel - Super User. https://superuser.com/questions/ ... -times-in-ms-excel.

tragamer

(開始時間)係唔係包含日期?
開新頁排順序開始時間,
用maxifs/minifs符合相同(代號)加减時間,
可以試下

bobopig

樓主問完沒回覆，答VBA點寫都沒意思吧
何況VBA識寫嘅人就識寫
唔識嘅人，叫佢用VBA，學嘅時間可能仲長過慢 ...
Aÿ 發表於 2023-5-25 17:22

    如果唔用 VBA, 用 FORMULA 會唔會較好?

bobopig

我問 Bing AI, 佢咁答, 你試吓:
To check overlapping check-in and check-out times in an Excel table, ...
dipsy 發表於 2023-5-30 14:41

    BING 唔知點用, 我有時都係問 POE, 好似我另一個POST 咁問. 但佢覆我D詞語就未必明.

tinhon

事緣編完後唔可以sorting(真嗰個無example嗰個咁簡單)，因為會亂到7彩。
我嘗試下用formula整，唔得嘅話再諗下計寫VBA。
謝謝咁多位師兄