Excel 技巧分享

數據整理(五) – Append

去到第五篇都諗緊教大家咩野好,既然上次教過大家用Merge去將兩個或者以上既表格合拼,今次都想教大家利用Append 去延伸表格。 用返上次既銷售資料去做例子,銷售資料由3月去到5月,如果呢個時候有另一份同一格式既銷售資料,係由6月去到8月份, 咁我地點樣可以合併佢做一份由3月去到8月既銷售資料呢? 我特意將6至8月份既銷售表改成另一個形式,去顯示返Append既強大之處。 我只係有invoice number同amount 兩欄。 我地先用返Get dataà from file àfrom workbook去載入返兩份資料,之後係Query入面既Combine搵到Append 我地會以3月至5月既table作為主軸(即係first table),而6月至8月會做second table。 順帶一提,其實可以append多過2個table,只要揀選three or more tables就可以。 Excel會自動揀選最適合既column去做延伸,所以你唔會見到Amount去左第2個column, 而係去返正確既column,確保你資料唔會出錯。 最後close&load就完成。 比起你copy & paste更加方便,而且資料錯既機會大大減少。 如果有咩想知或者有咩疑難,都歡迎電郵比我討論下。carlos@cafedecarlos.com

數據整理(四) – conditional formatting

呢一次想講下當有大量data既時候,例如一堆銷售單資料,你想睇下中間資料有冇出錯,好似出現重覆單號碼咁,大家會點樣去檢查呢? 最低級︰肉眼搵(相信呢個年代唔會有人咁做啦) 中級︰利用match函數,搵有相同既單號碼。都係唔夠快 高級版︰去片 我係度故意加插左一個重覆既單號碼係度,然後我利用styles上面既conditional formatting,10秒唔需要就可以highlight出重覆既單號碼 而其實同樣地,conditional formatting仲可以幫我地篩選指定條件既資料出黎,例如我想搵高於平均值既銷售,我地可以利用 conditional formatting , top/bottom rules, above average去highlight高於平均值既銷售,再作出篩選, 大大節省你用formula去計算平均值,再用filter去篩選高於平均值既銷售。 再講多一個用途,conditional formatting 仲可以幫我地既資料用簡單既圖形表示。例如上文提到既銷售資料, 我想利用bar chart等用家一眼就睇到高額銷售,或者低額銷售,我地都可以用到conditional formatting 去做, 今次我用影片介紹 利用data bar,可以將簡單數字圖形化,當然,處理到更多數據既時候,data bar未必適合,呢個會留返之後數據分析同表示既時候再講深入少少。 如果有咩想知或者有咩疑難,都歡迎電郵比我討論下。carlos@cafedecarlos.com

數據整理(三)

唔知大家有冇試過,係一個Excel入面有唔同既工作表,裝住可能差唔多格式既數據,你需要整合做一個Master既總表,將唔同工作表既數加起既時間, 都曾經都試過用sum 將不同工作表既數指黎指去得出個加總呢? 其實Excel有個更加好既功能,做到以上既工作,又唔會驚set formula時搞到自己亂哂龍架。 功能介紹 Consolidate功能是Excel中的一個非常有用的工具,佢可以將多個工作表中的數據合併到一個新的工作表中。 這個功能可以幫助大家快速地將數據進行統計,從而更好地管理和分析數據。 Consolidate功能通常用於以下情況: 以下是Consolidate功能的應用示範: 1:合併不同工作表中的數據 假設有三個工作表,分別包含三間公司的采購數據。現在需要將呢三個工作表中的數據合併到一個新的工作表中,以便更好地進行分析。 2:將同一工作表中的數據合併 假設有一個工作表,其中包含了每月的采購資料。現在需要將這些資料按照月份進行合併,以便更好地進行分析。 實際使用情景 Consolidate功能在日常工作中非常有用。以下是一些實際使用情景: 總之,Consolidate功能是Excel中非常有用的工具,佢可以幫各位快速地將資料進行合併和匯總,從而更好地管理和分析資料。各位可以根據自己的需求和實際情況,靈活地使用Consolidate功能,以便更好地完成工作任務。 如果有問題都可以電郵比我討論下。carlos@cafedecarlos.com

數據整理(二)

唔知大家熟唔熟識Vlookup呢?相信好多打工仔一開始做野既時候,一用Excel好多時都會用到呢個功能, 如果係5年前10年前,你要為數據增加相關既資料,佢可以好快幫到你,但今日Excel已經有其他功能去代替你再去慢慢打個Fomula去整個數據出黎,我會慢慢介紹一下。 為免有人唔知咩係Vlookup,我都簡單講一下︰當你需要在 Excel 試算表中查找特定數據時,VLOOKUP 函數是一個非常有用的函數。 它可以幫助你在一個範圍內搜索一個值,並返回相應的值。VLOOKUP(代表 “垂直查找”)的用途是從一個表格中查找一個值,然後返回與該值相關的另一個值。 今日要介紹,就係Excel入面Data Query既功能。用返上一篇既例子去做介紹,假設我地手上有兩份資料,包括上一篇見到既銷售資料 而另一份係vendor既資料 當老闆想要求可以係同一份資料上面顯示出銷售資料同vendor既名同limit,可能你會係銷售資料上面利用vlookup去查找需要既資料。 但vlookup有佢既限制,第一set formula 始終有機會出錯,特別係唔算太熟悉formula操作既朋友,第二如果我要一個資料,就set一次formula, 但要10個資料,就要set 10次,太費時。 以下落黎,我會每一步介紹點樣利用data query去將兩個工作表合併,同時抽取你想要既資料。 首先,係一張空白既工作表上面,按Get Data> Data Query 再視乎你需要合併既檔案格式去選擇,我以Excel file為例,選擇From file>From Workbook 選擇好檔案之後就揀import,會見到咁既情況 我地會用Get data>Combine Queries>Merge去將兩個表格合併,會見到咁既選項 我會以銷售資料作為主軸,所以上面我會揀table (我第1個載入既資料),而下面就會揀table 2。 呢個時候,我地要話比Excel知究竟Excel基於咩去合併兩個表格呢? 呢度大家都好清楚,兩個表格共同既資料,就係Vendor code,所以我地只需要點選上下兩個table既vendor code欄就可以啦。 Excel仲會顯示有幾多資料符合要求。若果點選其他資料會出現咩情況? 例如我點選amount和vendor limit去做配對,結果當然沒有一條符合啦。揀選好就可以揀”OK”。 之後我地可以按工作表右上角既logo去揀選我地想要既資料,今次我就揀vendor同vendor limit 最後,我地可以更改新增兩欄既heading,冇其他更改就可收左上角close and load 最後,我將成個過程用短片形式示範一次,等大家容易理解。 如果有問題都可以電郵比我討論下。carlos@cafedecarlos.com

數據整理(一)

第一個想介紹,當然絕對唔會係sum,sumif,vlookup咁簡單又老土既技巧,先從整理data既技巧講起,第一個想介紹既就係Flash Fill(快速填入) Flash Fill是一種在Excel中自動填充數據的功能。這種功能可以根據現有的數據自動填充相關的數據,而無需使用公式或複製粘貼等繁瑣的操作。 Flash Fill功能係Data 入面 假設你有一堆以同樣排列方式既單號碼,你需要抽取佢部份資料作分析或其他用途,可能你會使用RIGHT函數去抽取加邊3個字體去填寫vendor code, 但其實Excel唔再需要利用函數去話佢知點抽取一個cell入面既資料,我地可以利用Flash Fill快速填入。 先係B2填上第一個資料,等Excel可以理解到你需要資料既模式,例如呢度我會填上ABE。之後係B3開始就唔需要再填寫啦,直接按快捷鍵Ctrl+E, Excel就會自動由B3填到B11,就會係你想要既資料。 至於其他資料,包括年份、月份同單號碼,用同一方法就可以順利生成,比利用函數更快。當遇上多達千條或者萬條既記錄時,所慳到既時間將會更多, 當然我亦建議完成前檢查一次有沒有資料出錯啦。

Scroll to Top