月月的會計師事務所筆記

大家好，我是月月。

上一篇「#48-[其他]-事務所常用Excel技巧1/2」，講解了13項Excel的基礎操作，今天接續著要來講「進階應用」囉！

因為版面篇幅的關係，我會大概講解公式或操作的方法，但可能沒辦法把所有細節都講完；如果真的看不懂，建議大家可以多問問同儕、多Google或是再留言問我！

那麼以下就正式開始！

「進階應用」-1.公式「IF」

IF是一個Excel非常好用的邏輯判斷公式，可以幫助你快速分類、拆解資料，堪稱是最好用公式之一，以下舉例幾個常見使用方法，希望大家熟悉操作之後，以後可以自行衍生出更多使用的方法哦：

(1).確認差異：

假設藍色部分是原始資料，綠色部分是修改後資料，可以直接複製表頭之後，使用IF公式比對兩份之間資料的差異，公式為IF(舊資料=新資料，對的話顯示為0，錯的話顯示為1)，則可看到下圖黃色部分，就是兩資料有差異的地方。

(2).跳號檢查：

如果想確認資料的序號是否有缺號，可以先排序之後使用以下公式：IF(此數字減去上一個數字=1，成立的話顯示為0，不成立的話顯示為1)，如此便可以找到8的缺號。

(3).資料重複檢查：

如果想確認資料是否有重複，可以先排序之後使用以下公式：IF(此欄位=上一個欄位，成立的話顯示為0，不成立的話顯示為1)，如此便可以找到重複的欄位。

「進階應用」-2.公式「Vlookup」

好的，接下來就要進入Excel最鼎鼎有名的公式Vlookup了！

這個公式好用的地方就在於，你只要確定你要的人是誰，他在其他表格的相關資料就可以全部用公式搞定。

以下面的例子來看，左半邊是全部人的分數資料，但你只想知道小花和小李的分數，又不想用篩選功能一筆一筆挑，這時候就是要讓Vlookup功能大顯身手了。

Vlookup這個公式有4個組成要素(搭配下圖H2欄位的公式)：

要素一：你要找的人(在這裡就是小花G2)

要素二：你要找的資料範圍(在這裡就是考試分數B:E)

要素三：第幾排資料(在這裡就是第2排的國文分數)

要素四：是否要完全一致(在這裡是指，G2的小花這兩個字一定要對應到B排裡一模一樣的才可以)。

所以H2，小花的國文分數的Vlookup公式為：=VLOOKUP(G2,B:E,2,FALSE)，對Excel來說，翻成白話文就是，=VLOOKUP(我要找G2名字叫做小花的人,幫我在B:E裡找他的資料,我要B~E裡面小花資料的第2欄, 選FALSE就是要名字完全一樣哦!)。

所以依照邏輯來看，Excel就會知道你要找的人是小花，你要小花的B~E排資料，然後要給你B~E排資料的第二欄，而且名字要完全一樣叫小花才可以，哦，那就是34分啊。

在這裡有幾個重點。

(1).你要找的名字，要放在指定資料範圍的最左邊。(要素一)

意思就是說，你要找的名字是G2的小花，所以在選擇資料範圍的時候要從B排選到E排，不可以從A排選到E排，因為Excel只會就你選的第一排開始尋找誰是小花，如果選A~E排的話， A排裡面根本沒有小花，系統就會呈現找不到的結果。

(2).資料的範圍，請選一整排。(要素二)

Excel是一個吃「相對位置」資料的軟體，意思就是說，同一個公式可以因為複製而適用到移動後的相對位置，比如本來是A1*B1，往下複製一格、整個公式就會自動變成A2*B2，就是來自「相對位置」的概念。

所以在選資料的時候，我是選B~E一整排的「B:E」，而不是「B1:E10」，就是為了避免我在抓小花的資料的時候，資料範圍是「B1:E10」沒錯，但往下複製公式、來到抓第二個人、也就是小李的資料的時候，資料範圍自動往下移動到「B2:E11」。

(3).要抓第幾排資料，是從你選的資料範圍的第1排開始往右數(要素三)

以小花的國文分數=VLOOKUP(G2,B:E,2,FALSE)這個例子來看，因為公式的數字是2，所以提供的資料就是B~E的第二排，也就是C排、國文分數的資料；如果你想要下一排D排的數學分數資料，那就直接把公式改成=VLOOKUP(G2,B:E,3,FALSE)，也就是把要素三的2改成3，就會變成數學分數啦。

有時候，我們會用vlookup來比對兩邊資料的完整性，這時候可以直接把要素三改成1，也就是直接比對小花的名字有沒有出現在半邊的分數資料庫裡。

「進階應用」-3.公式「SUMIF」

某些資料會重複出現，比如說產品銷售清單，同一個客戶可能會重複買同一個或不同的產品，所以需要把同類別的加總計算時，就可以使用SUMIF。

比如，以下圖舉例，總共有甲乙丙丁四種產品，分別有ABCD四位客戶會重複購買，則若想知道貨品甲的總訂購數量，則使用SUNIF公式的各要素如下：

要素一：標的資料範圍(在這裡就是C排，所以選C:C)

要素二：你的標的(在這裡就是貨品甲H2)

要素三：要加總的資料範圍(在這裡就是D排，所以選D:D)

所以對Excel來說，公式=SUMIF(C:C,H2,D:D)，就是在C排裡面，找到你要求的「甲」，把對應甲的所有D排數量加總，就會得到你要的貨品甲的總訂購量。

同理，如果想要計算出A客戶的總銷售金額，則可使用公式=SUMIF(B:B,H8,F:F)。

是在B排裡面，找到你要求的「A」，把對應A的所有D排數量加總，就會得到你要的A客戶的總銷售金額。

「進階應用」-4.資料剖析

資料剖析是Excel內建的功能，可以幫助切割儲存格內的資料，以下圖為例，假設我們想要把以下資料分別整理為部門別、姓名、email三個欄位：

首先請先檢視你的資料，雖然資料都在同一格內，但是格式很整齊，適合使用資料剖析，則可以將資料整欄選起來，選「資料」→中間的「資料剖析」→選「固定寬度」→「下一步」，然後手動增加下圖紅色框框的箭頭，告訴Excel你要分割的位置，再按「完成」

Excel自動執行欄位切割，出來就是以下的樣子啦，再依據你要的資料加以整理即可。

另外一種是使用資料剖析的「分隔符號」，來自以下範例，比如說部門的名字和人名長短不一，沒辦法用上面的例子直接作固定寬度的切割時，就來使用分隔符號加以區隔：

在這個例子裡，資料剖析就要選「分隔符號」→在「其他」裡輸入「-」→「完成」。

完成後，資料就會自動以「-」為區隔，分拆成兩格，接著，再作一次以「<」為區隔的資料剖析，就可以把人名和Email分拆了。

「進階應用」-5.樞紐分析

樞紐分析像SUMIF一樣用來處理多筆重複資料，比如產品銷售清單，但樞紐分析是Excel內建的功能，而且比SUMIF單純加總還要更強大，所以建議大家一定學會使用。

在坊間，樞紐分析是可以單獨開成一門課的，但以下只簡單入門介紹，有興趣的話請大家務必自行深入學習哦！

承接客戶銷售單的例子，總共有甲乙丙丁四種產品，分別有A~F的客戶會重複購買，若想知道每個客戶的訂單筆數，則可直接使用樞紐分析的計數功能：

選「插入」→「樞紐分析表」→設定資料範圍在A~F→「確定」

這時就會出現樞紐分析表欄位，紅框處的「列」，就是你想要直直呈現的資料，以這題的範例來說，就是客戶別。紅框處的「值」，就是可以透過Excel自動計算出來的資料，以範例來說，就是筆數。

這時，把客戶拖曳到「列」，把訂購數量拖曳到「值」，就會出現每個客戶的訂購貨品計數，也就是我們要的每個客戶的訂單筆數了。

如果我們要的是每個客戶訂購的貨品總數量，則可以把「值」內的「計數」功能修改為「加總」。

點選「值」裡面的「計數-訂購貨品」→「值欄位設定」→把原本設定的「項目個數」改為「加總」→確定，就會出現每個客戶購買貨品的總數量了。

「進階應用」-6.自動填入

當資料有固定規則可循的時候，可以直接使用自動填入功能。

比如以下方資料為例，一樣是想分別整理出部門別、姓名和Email，則可以像下圖一樣，先把第一筆的規則建立起來：

再把你要完成的資料範圍，連同第一筆的範例「人力資源部」一起選起來，然後按「Ctrl+E」。

然後Excel就會自動判斷你抓取資料的規則，直接帶入其他你要的欄位裡：

「進階應用」-7.填補空格

有時候客戶提供的報表資料裡，為了閱讀方便和美觀，會把重複的資料使用「合併儲存格功能」，但是這個功能非常不方便篩選，一但篩選之後就無法辨識全部的資料欄位，如下圖黃色處，所以這裡要教大家如何快速填補空格。

所以目前的目標就是保留藍色的部分，把黃底部分依藍色格子的內容往下填補空格，操作如下：

(1).把要填補的部分圈選起來

(2).按「Ctrl+G」→「特殊」→「空格」→「確定」，就會發現圈選範圍自動變成空格處，也就是黃底部分

(3).輸入公式「=↑」→「Ctrl+Enter」全部套用，就完成啦!

PS.請注意，這裡的黃底是使用公式帶出的，所以記得重新複製後貼成「值」。

「進階應用」-8.修剪空格：公式「TRIM」

有時候拿到客戶提供的資料，會發現儲存格內最前面或最後面有許多連續空格：

這時候可以使用trim公式，trim就是英文修剪、剪花草等等的意思，所以公式如下=TRIM(A2)即可！

「進階應用」-9.公式鎖定某格

假如今天所有產品的銷售的價格都要使用成本加上固定比率的成本加價率，而且成本加價率可能會隨著市價變動，則這時就要使用到公式鎖定的功能。

比如以下方例子來說，各產品的售價=成本價*(1+成本加價率)，所以每一筆售價計算出來再把公式往下拉，卻發現很多「#VALUE!」，顯示錯誤的情形：

對Excel來說，公式在往下套用到下一格儲存格的時候，所有公式都會連帶變動相對的位置，也就是說，對於E6這一格來說，他的成本加價率不是E1的「3%」、而是E3的「售價」，所以文字根本無法進行數字的運算，就會發生計算錯誤。

所以調整的方法，就是強制讓所有的售價公式裡的「成本加價率」鎖定在E1，那麼不論公式怎麼移動或複製，都會使用E1的正確數字進行運算，鎖定方法如 下：

游標移到E4這一格的公式裡，對要鎖定的E1點一下，然後按下「F4」，就會看到E1這一格的E和1前面都出現了一個「$」的金錢符號，就代表E1這一格已經被完全鎖定了！

之後的公式不論複製到哪一排，成本加價率都會永遠鎖定在E1的數字。

「進階應用」-10.文字型數字快速切換

有時候拿到客戶的資料，你看起來是數字的欄位竟然沒辦法加總，就代表那一排數字的格式是文字，而文字是無法進行加減運算的，需要另外調整。

這時有兩種調整方法：

(1).用公式帶2個負號：

使用兩個負號之後，文字型的數字就變成數字型的數字，就可以進行最後的小計了！

(2)使用公式VALUE

第二種方法則是使用公式VALUE，也可以讓文字型的數字變成數字型的數字！

好的，講到這裡，我所想到的「進階應用」就講完了，Excel的操作終於全部講完啦~完全用圖文表達真的是不容易啊，希望可以幫助大家精進Excel的熟悉度，增進一步增加工作的效率、減少加班的時間！

J

另外值得一提的是，Excel還有另外一個強大的功能：VBA巨集。

巨集是一個可以把一連串Excel操作全部紀錄下來、然後重複套用到新資料的功能，可以透過自己寫程式或是用「錄製巨集」的功能告訴Excel，讓Excel以後可以對新的資料重新自動操作預定的步驟，非常的省時。

不過因為我本人實在是不太熟悉，所以這裡就先講個改念簡單帶過，等我以後真的有上課、有新的理解之後，再來考慮寫成文章吧哈~

往後定期(頻率約當是每兩週一篇)更新「其他」段的文章，希望透過更深入的文字，把我在事務所的經歷一點點寫成文字留下紀錄並幫助大家~

如果你有什麼想進一步了解的事務所相關內容，歡迎留言給我讓我知道，我會視情形排入我的寫作規劃中。

如果對事務所的工作或故事有興趣，歡迎到我的「部落格概述」裡頭尋找其他我已經寫過的文章哦！

那麼，我們下回見啦！

2019/7/24

月月