查看完整版本 : 求教 有excel達人嗎

candylee123 2020-8-26 10:03 AM

求教 有excel達人嗎

如果有名 每日每次進出 時間

如何可以
按日期抽最每日最早最遲總共時間並 按日期、名分類呢

candylee123 2020-8-26 10:03 AM

[quote]原帖由 [i]candylee123[/i] 於 2020-8-26 10:03 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=523903649&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]
如果有名 每日每次進出 時間

如何可以
按日期抽最每日最早最遲總共時間並 按日期、名分類呢 [/quote]

[attach]11539733[/attach]

awongyuk 2020-8-26 11:16 AM

用conditional formatting

daisukemoi 2020-8-26 11:49 AM

[quote]原帖由 [i]candylee123[/i] 於 2020-8-26 11:03 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=523903671&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]
11539733 [/quote]

For 總共時間, we need to know the entry time and exit time per user, to calculate the duration.
It will be better to have 2 columns. One for entry time, another for exit time.
An indicator for entry/exit will also work.

最早

[attach]11540139[/attach]

[attach]11540140[/attach]

最遲

[attach]11540141[/attach]

[attach]11540142[/attach]

[[i] 本帖最後由 daisukemoi 於 2020-8-26 12:55 PM 編輯 [/i]]

Twittering 2020-8-26 01:44 PM

[quote]原帖由 [i]daisukemoi[/i] 於 2020-8-26 11:49 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=523909584&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]


For 總共時間, we need to know the entry time and exit time per user, to calculate the duration.
It will be better to have 2 columns. One for entry time, another for exit time.
An indicator for entry ... [/quote]

係咪仲有用咗named range

daisukemoi 2020-8-26 01:50 PM

[quote]原帖由 [i]Twittering[/i] 於 2020-8-26 02:44 PM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=523915436&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]

係咪仲有用咗named range [/quote]

yes, name range is easier to maintain, and hopefully can reduce manual input error

candylee123 2020-8-26 02:31 PM

[quote]原帖由 [i]awongyuk[/i] 於 2020-8-26 11:16 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=523907788&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]
用conditional formatting [/quote]
Condition formating 做唔到🤔

candylee123 2020-8-26 02:42 PM

[quote]原帖由 [i]daisukemoi[/i] 於 2020-8-26 11:49 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=523909584&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]


For 總共時間, we need to know the entry time and exit time per user, to calculate the duration.
It will be better to have 2 columns. One for entry time, another for exit time.
An indicator for entry ... [/quote]
我跟著妳formula 佢話我too few arguments😢[attach]11540608[/attach]

awongyuk 2020-8-26 02:57 PM

[quote]原帖由 [i]candylee123[/i] 於 2020-8-26 02:31 PM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=523917563&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]

Condition formating 做唔到🤔 [/quote]


data一個table, 而家要搞個inquiry,用index match 夾問邊個人,篩咗個人咪有日嘅最早最遲囉
搞幾個dimension咪分開做多幾個,冇可能一個搞掂啦
你個data table,時間果度要用mid,right,果啲function找出數值先能做到辨別大少先後

[[i] 本帖最後由 awongyuk 於 2020-8-26 03:01 PM 編輯 [/i]]

daisukemoi 2020-8-26 03:27 PM

[quote]原帖由 [i]candylee123[/i] 於 2020-8-26 03:42 PM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=523918089&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]
我跟著妳formula 佢話我too few arguments😢11540608 [/quote]

your data
Date range = A2:A2938
Last Name range = D2:D2938
First Name range = C2:C2938

formula structure

=INDEX(LastNameRange,MATCH(MIN(DateRange),DateRange,0))&", "&INDEX(FirstNameRange,MATCH(MIN(DateRange),DateRange,0))

your formula should be
=INDEX(D2:D2938,MATCH(MIN(A2:A2938),A2:A2938,0))&", "&INDEX(C2:C2938,MATCH(MIN(A2:A2938),A2:A2938,0))

+++++

Please note that the above formula can only find the name of earliest time entry and the name of latest time entry, within a single date range.

It will be a challenge if you want to find the earliest time and latest time by date value (i.e. each day).
Because
1. the source data format is M/DD/YYYY HH:MM
2. the target data format is YYYY/MM/D

I can still compare by this formula
= year(sourcedata)&month(sourcedata)&day(sourcedata) = year(targetdata)&month(targetdata)&day(targetdata)

however, this would make the lookup formula (index+match) very complicated

daisukemoi 2020-8-26 06:07 PM

[quote]原帖由 [i]candylee123[/i] 於 2020-8-26 11:03 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=523903671&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]

11539733 [/quote]

可以按日期, 抽每日, 最早, 最遲, 按日期、名分類

[attach]11541289[/attach]

[attach]11541290[/attach]

[attach]11541291[/attach]

[attach]11541292[/attach]

蜂仔 2020-8-28 12:37 AM

唔開心, 返工被人耍, 花我時間, 兜圈做嘢.

差不多每次打電話問功課, 都唔聽電話. 直接行去問, 又話自己login ID 睇唔到我D嘢. 叫我返位等. 等到Oracle- JD Edward閒置彈咗出去都未到.
9:45 上網,嘆奶茶就有時間, 問佢小小嘢就扮忙. 雖然唔同login ID, 但 user interface 一樣, 其實一樣可以show 步驟俾我睇.

Aug,2018 D voucher開始都未filing, d 文件亂七八糟, 就算大肚婆唔方便filing, 都只不過10月懷胎, E + over 10 mth 都未filing. (原因用因為3年audit 一次為藉口, e + 未audit:smile_27::smile_27:). 有D客人8月頭, 直接寄chq 來交management fee. 但到E + 仲未入機, 唔見chq 就入我數. (我24號先接手, 入AR).

總部代找數, 之後叫我用肉眼, 逐個PDF file click入去, 睇吓有冇屬於自己公司d數, print 出來做supporting. 其實PDF file name 用 batch no. + Doc no 組成. 只要用excel sort company code, 就可以得知 PDF file name.

開chq, over $30K 就hold 住用system chq. 係JD Eward有155條transaction, 又係叫我用肉眼, 逐個睇. 逐個click. 教我D先後次序倒轉, 等我番兜再做一次.
死鬼逐個睇, 只要係Amt column 打>$30K 就可以一次過select all.

Usersetting(button)都未拖出來, 就叫我自己用bath no. print report.

不斷花我時間兜圈做嘢, 佢個人根本就唔helpful.

5 days 之後, 下個week 就WFH. Friday 5點鍾先搏命講, 講到7點就走人. 管理處send email 叫佢地出reminder, 佢地按一個forward button 就叫我follow up. 請問你forward前, 有冇諗吓有冇教過我先?
起勢咁講, 開六個畫面, 快手快腳, 係咁拖視窗, 導致搬錯S: drive D file. 令到我部電腦死機. IT 以為我有異常活動, restrict 我ge access right. 搞亂我D嘢就算, 同我講Monday 重新開機就無事. Monday 我係屋企send email 話唔得, 就答我因為我係temp staff, 所以無權用其他folder.

要我做又唔願講, 又access 唔到要用D file.

做temp job 做路人甲
[url=https://www.youtube.com/watch?v=UL3rtnZB93s]https://www.youtube.com/watch?v=UL3rtnZB93s[/url]

蜂仔 2020-8-28 01:03 AM

[quote]原帖由 [i]daisukemoi[/i] 於 2020-8-26 03:27 PM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=523920500&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]


your data
Date range = A2:A2938
Last Name range = D2:D2938
First Name range = C2:C2938

formula structure

=INDEX(LastNameRange,MATCH(MIN(DateRange),DateRange,0))&", "&INDEX(FirstNameRange, ... [/quote]
唔開心:(,無心情:smile_24:, 既然唔welcome我, 就做到天日last day
唔開心:(,暫時寫兩種寫法

一樣受到每日日期限制, 要手動設定範圍
=[color=#4169e1]XLOOKUP[/color](H2&[color=#4169e1]MIN[/color](B2:B4),A2:A4&B2:B4,C2:C4,0)
=[color=#4169e1]XLOOKUP[/color](H2&[color=#4169e1]MAX[/color](B2:B4),A2:A4&B2:B4,C2:C4,0)
=[color=#4169e1]FILTER[/color](C5:C7,(A5:A7=H3)*(B5:B7=[color=#4169e1]MAX[/color](B5:B7)),"No data")
=[color=#4169e1]FILTER[/color](C5:C7,(A5:A7=H3)*(B5:B7=[color=#4169e1]MIN[/color](B5:B7)),"No data")

如果覺得formula 麻煩,有無諗過用pivot table?
or 有無諗過用MAXIFS, MINIFS + XLOOKUP
簡單易明, 可以做到效果, 唔受每日日期限制, PERFECT
諗吓,天日傾吓

無私者, 無私share
[url=https://www.youtube.com/watch?v=QYAdNZJF6f4]https://www.youtube.com/watch?v=QYAdNZJF6f4[/url]

daisukemoi 2020-8-28 01:30 AM

[quote]原帖由 [i]蜂仔[/i] 於 2020-8-28 02:03 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=524003085&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]

唔開心:(,無心情:smile_24:, 既然唔welcome我, 就做到天日last day
唔開心:(,暫時寫兩種寫法

一樣受到每日日期限制, 要手動設定範圍
=XLOOKUP(H2&MIN(B2:B4),A2:A4&B2:B4,C2:C4,0)
=XLOOKUP(H2&MAX(B2:B4),A2:A4&B2:B4,C2:C4,0)
=FILTER(C5:C7,(A5:A7= ... [/quote]

thanks for sharing
xlookup, maxifs, minifs, are available for microsoft 365
these functions are not available for earlier versions, e.g. office 2016 or earlier

pivot table is useful

candylee123 2020-8-28 10:55 AM

[quote]原帖由 [i]蜂仔[/i] 於 2020-8-28 01:03 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=524003085&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]

唔開心:(,無心情:smile_24:, 既然唔welcome我, 就做到天日last day
唔開心:(,暫時寫兩種寫法

一樣受到每日日期限制, 要手動設定範圍
=XLOOKUP(H2&MIN(B2:B4),A2:A4&B2:B4,C2:C4,0)
=XLOOKUP(H2&MAX(B2:B4),A2:A4&B2:B4,C2:C4,0)
=FILTER(C5:C7,(A5:A7= ... [/quote]
做咩辭職啊!!

littlewcm 2020-8-28 12:24 PM

點解唔用埋"table"? 就算不停加行數都唔會出錯

Pensis 2020-8-28 11:12 PM

[quote]原帖由 [i]蜂仔[/i] 於 2020-8-28 01:03 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=524003085&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]

唔開心:(,無心情:smile_24:, 既然唔welcome我, 就做到天日last day
唔開心:(,暫時寫兩種寫法

一樣受到每日日期限制, 要手動設定範圍
=XLOOKUP(H2&MIN(B2:B4),A2:A4&B2:B4,C2:C4,0)
=XLOOKUP(H2&MAX(B2:B4),A2:A4&B2:B4,C2:C4,0)
=FILTER(C5:C7,(A5:A7= ... [/quote]
Ching你叻仔黎既
東家唔打打西家

蜂仔 2020-8-29 12:48 AM

BB Bee beeson今日Last day
[url=https://youtu.be/gZrFCeLF3vk]https://youtu.be/gZrFCeLF3vk[/url]

[[i] 本帖最後由 蜂仔 於 2020-8-29 12:50 AM 編輯 [/i]]

山上滴滴希望 2020-8-30 10:25 PM

*** 作者被禁止或刪除 內容自動屏蔽 ***

蜂仔 2020-8-31 02:27 PM

[quote]原帖由 [i]candylee123[/i] 於 2020-8-28 10:55 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=524015299&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]

做咩辭職啊!! [/quote]

做物管D數好簡單, 通常都係full set 做晒. 所以4 mth Temp job 都需要full set.

其實無咩難度, 因為叫做豪宅盤, 單位數量比較少. 唔似上車盤, 1梯8伙,成交次數多. 而且一般AP果part, 管理處已經mark coding, 打晒invoice資料. A/C同事, 只係做checking & payment.

做3盤數其實好易, transaction volume少, 又唔需要太多拆數. Team head已經好好, 將大肚婆3盤數, 分俾3個officer做checking, 而我做daily operation. 其實佢地增加workload 唔係好大. 但都唔願意花多D時間, 甚至無講點做, 就叫follow up. 講多少少JD Eward都好似教咗咩絕世秘笈, 自己蝕底咗.

但聽見我可以WFH, 5點鍾先起勢講. Team head以為我有jetso, 但又唔知道,佢地D email 叫我follow up, 而唔係FYI.

管理處email問業主月頭開出D chq, 到E+仲未入機, 佢又唔回覆人. 到月尾就交個波俾我. 一接手D chq, 就已經知道起碼有3張chq missing. 一陣唔見chq又入我數.

無謂同佢地糾纏, 係office問佢嘢都咁困難, 更何況WFH. Team head & HR問點解, 我都唔想講太多. 求其搵個藉口, WFH 模式唔適合我. 但避免以為我整唔見D chq, 結果講講吓越講越多

蜂仔 2020-8-31 02:30 PM

[quote]原帖由 [i]山上滴滴希望[/i] 於 2020-8-30 10:25 PM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=524147620&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]
我也有問題急需請教。 [/quote]

急就講出來, 睇吓大家幫唔幫到手:smile_41:

hillgiantex 2020-9-10 02:14 PM

[quote]原帖由 [i]candylee123[/i] 於 2020-8-26 10:03 AM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=523903671&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]


11539733 [/quote]

樓上已經有師兄解答了, 是運用一連串formula的。佩服佩服。
小弟剛剛才看到這個post, 覺得formula 太繁覆, 所以試試用excel 內設的power query。結果在不運用formula也能做到相同結果, 共有5個steps, 給大家參考參考:
Step 1: Click Insert tab  > insert table:
[attach]11596396[/attach]
Step 2: Click data tab > from table/range
[attach]11596397[/attach]
Power query editor will pop out.
Step 3: In power query editor, click insert column (新增資料行) on the top tab, then choose date as below
Drag Date column next to Date and time column (for presentation only)
[attach]11596398[/attach]
Step 4: Click General (常用) tab, choose group by (分組依據), a small window will pop out, choose advance (進階) and choose the following criteria, and click ok
[attach]11596399[/attach]
[attach]11596400[/attach]
Step 5: earliest and latest 時間已經計算好了。Click save and load, 便完成
[attach]11596401[/attach]

我最後試試在table 加dummy data, 在Data tab 按 refresh
[attach]11596402[/attach]
[attach]11596403[/attach]
也沒有問題, 資料也會隨之update!

[[i] 本帖最後由 hillgiantex 於 2020-9-10 02:17 PM 編輯 [/i]]

蜂仔 2020-9-11 01:16 AM

[quote]原帖由 [i]hillgiantex[/i] 於 2020-9-10 02:14 PM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=524705364&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]


樓上已經有師兄解答了, 是運用一連串formula的。佩服佩服。
小弟剛剛才看到這個post, 覺得formula 太繁覆, 所以試試用excel 內設的power query。結果在不運用formula也能做到相同結果, 共有5個steps, 給大家參考參考:
Step 1: Click Insert tab  > insert table:
11596396
Step 2:  ... [/quote]

各施各法:loveliness:
Power Query:smile_o12:


但如果需要計每個人In & Out 之間Total hour, 我先會打算用Power Query--> Subtract--> Total Hour-->round up decimal place 1

E+問題只需要知道每日最早返&最遲走係邊個人, 無需要每個人個別最早&最遲, 所以認為pivot table 已足夠.

Power Query仲有好多玩法, 大家一齊研究吓.:smile_53:

瞓唔著, 考慮緊天日返唔返份part time job (1week 3 day, hourly basis, 人工更底, 但要求做埋income statement& b/s). 經濟唔好, 連Temp job 都無得做.

最怕承諾咗去返, 之後有別一份去interview

[url=https://www.youtube.com/watch?v=WQzU0zVG4hg]https://www.youtube.com/watch?v=WQzU0zVG4hg[/url]

coria 2020-9-11 08:31 AM

[quote]原帖由 [i]蜂仔[/i] 於 2020-8-31 02:27 PM 發表 [url=https://www.discuss.com.hk/redirect.php?goto=findpost&pid=524179161&ptid=29342327][img]https://www.discuss.com.hk/images/common/back.gif[/img][/url]


做物管D數好簡單, 通常都係full set 做晒. 所以4 mth Temp job 都需要full set.

其實無咩難度, 因為叫做豪宅盤, 單位數量比較少. 唔似上車盤, 1梯8伙,成交次數多. 而且一般AP果part, 管理處已經mark coding, 打晒invoice資料. A/C同事, 只係做checking & payment.

做3盤數其實好易, tra ... [/quote]


物管accounting 只需做payment 及埋村數,好簡單,不過冇前途,行頭窄,去第二個field機會低

優點係唔會裁員,安安分分可以做到退休
頁: [1]
查看完整版本: 求教 有excel達人嗎