Excel Tips: 如何用 Power Query 提升工作效率❓ 免去重複工作 👍 實戰工作示範 ✅

#exceltricks

[@trumpbuddha1053]

非常有用啊, 多谢晒

[@KKLeung]

多謝你嘅好評，協助轉介給有需要嘅朋友，就是對本平台嘅義工們最大支持。👍

[@kcwai18]

KK, 又要請教你，如果Source data file 要用密碼才可開啟，是否不能執行Power Query ?謝謝

[@KKLeung]

@@kcwai18 Hi KC, 我未有試過。如Source 需用密碼開，Power Query 要抽取資料正常是不可以。 是否在power query 過程輸入密碼就未有試過。有時都要試。多謝你嘅提問👍

[@kcwai18]

KK, 非常多謝你迅速🙏🙏🙏

[@rmok4258]

Leung Sir, very good and a practical example. We just need to open the summary file and refresh on the data range and then on the report range. I have learned Lotus 123 long in the 80's , which is long time ago and this is amazing. Thank you.

[@KKLeung]

Thanks for watching

[@pollychua4545]

So useful sharing, thanks KK!

[@KKLeung]

Hi Polly 多謝你嘅好評，協助轉介給有需要嘅朋友，就是對本平台嘅義工們最大支持。👍

[@rmok4258]

Leung Sir, is it possible to download your source file to practice the power query. I am lack of data and it is not useful to make some meaningless data for analyst. I have watched over many of your videos and only know that you last name this morning and feeling sorry for not address you the first time. Thank you.

[@KKLeung]

Currently I cannot access the source of my data,. Update you early of next week. Thanks

[@rmok4258]

@@KKLeung Ok Leung Sir. Thanks

[@KKLeung]

Sorry again. The demo file is longer available. Due to our free one drive being pretty full, some of the files were permanently removed.

[@rmok4258]

@@KKLeung That is alright. I will try to make my own data. Thanks for trying.

[@paulyeung5628]

非常感謝k.k 老師無私無私奉獻精神❤❤🙏🙏

[@KKLeung]

Hi Paul 😀 多謝你嘅好評，協助轉介給有需要嘅朋友，就是對本平台嘅義工們最大支持。👍

[@hoiluntang1222]

KK 老師想請教吓個Summary可唔可以放返落原本嗰個workbook 度㗎？ (既開多一個tap)?

[@KKLeung]

是可以的

[@hoiluntang1222]

謝謝回覆，我試試🙏🏻

[@hoiluntang1222]

KK Sir, 我成功create power query 係同一個excel 到，但當我update 咗新資料，save 完，跟住再係power query 做好個表到click”重新整理”，但唔成功 電腦顯示: [Expression. Error] 找不到資料表的資料行 應該點處理，我在那裏出錯了呢？ 煩幫忙提點，謝謝你🙏🏻

[@tonymkleung]

👍👍👍👏👏👏

[@KKLeung]

Hi Tony 多謝你嘅好評，協助轉介給有需要嘅朋友，就是對本平台嘅義工們最大支持。👍

[@carman8881]

你好，如果錯手delete左匯出的power querry table, 怎樣可以重新再滙出table呢？

[@KKLeung]

Hi Carman, 請按下列步驟 1 Select [Data] 2 Select [Queries and connections] 3 右手面 [Queries and connections], 在相關Query [ right click] 4 Select [load to..] 5 Select table 6 Select [New worksheet] or [Existing workshee] 7 Select cell location 8 Press [OK]

[@carman8881]

多謝回覆

[@carman8881]

你好，KK, 怎樣在Power quer 只抽取文字中銀碼的數字$7778.86，thanks '面價 / STATEMENT返左數真係冇加到LOADING / STATEMENT CHANGE COST FROM $7778.86 批單放在S手上待處理'

[@carman8881]

你好，KK, 怎樣在Power quer 只抽取文字中銀碼的數字$7778.86，thanks '面價 / STATEMENT返左數真係冇加到LOADING / STATEMENT CHANGE COST FROM $7778.86 批單放在S手上待處理'

[@carman8881]

及，如何寫，if (amont) between two value (小於10 and 大於-10） in power query呢，謝謝

[@8-2100-Lucky-Ricky-Tsang]

還有我諗極都不明點解 0.1 = 02:24:00 怎樣計？是否有方程式要記？

[@8-2100-Lucky-Ricky-Tsang]

原來 0.01 = 00:14:24 ？點解 ？

[@8-2100-Lucky-Ricky-Tsang]

0.001 = 又變了 1分6秒 ？ 00:01:26

[@8-2100-Lucky-Ricky-Tsang]

由於我是將42分再乘以60集則得到為2.520分鐘，但將它計算為小時就使用計算機就計算得出為 42，但在Excel 就不知怎樣去列表的？

[@8-2100-Lucky-Ricky-Tsang]

但如果60集中全部一致的分鐘就覺得很頭痛去計算的，謝謝老師

[@KKLeung]

Hi Lucky Ricky 有關你嘅提問, 參考下面解釋 1) Cell 輸入0.1 2) 如果在cell format, 選擇定義time 3) 會變成0.1 = 02:24:00 4) 0.1 默認為日數 5) 0.1 x 24 hour = 2.4 hour 6) 0.4 x 60 minute= 24 min 7) 最終結果 02:24:00 (hh:mm:ss) 8) 0.1 day = 2小時 24分鐘0秒

[@8-2100-Lucky-Ricky-Tsang]

XLOOKUP = =XLOOKUP(lookup_value, lookup_array, return_array,[if_not found],[match_mode], [search_mode]) 這個怎樣使用老師

[@KKLeung]

Hi Lucky Ricky, 有關XLOOKUP FUNCTION, 請參考下面示範影片 kzbin.info/www/bejne/g3XbZZyahJ12oJIsi=iNhTpGBLJBWUL1ar