贊贊小屋

Excel出貨單價檢查(下):格式化條件及色彩篩選

Excel執行報表分析時,經常是異常項目追踪管理。本文介紹出貨報表彙總之後,再以複製值貼上、格式化條件、儲存格色彩篩選等方式整理出異常單價明細。

上一節提到如何檢查出貨單價,樞紐分析表以目測法標示異常單價,即使樞紐彙總了,依照一般中等企業的出貨量,還是可能多到難以目測的程度,這篇文章介紹如何用Excel小技巧,直接將異常單價「提取」出來:

一、設定差異重大性金額門檻

上一節編製好的樞紐分析表,不為零的部份,表示相同客戶和相同料號,卻有不同的出貨單價。這裡基於重大性考量,只取正負差異大於等於3的部份,打底黃色標註異常。

Excel出貨單價檢查(下):格式化條件及色彩篩選 實務應用 第1張

二、單純複製值去除樞紐框架

選取A2到F15的範圍,複製,直接在新的工作表貼上,右下角有個檔案圖示的「貼上選項」,通常單純取值的情形,會選擇「值與來源格式設定」。不過在這裡,只要選取樞紐分析表不含第一列的範圍,貼上之後,應該便是只有值及格式。這麼做的主要作用是將樞紐架構去除,方便進一步數據整理。

Excel出貨單價檢查(下):格式化條件及色彩篩選 實務應用 第2張

三、新增格式化條件

選取差異值的範圍,也就是F欄,然後在上方功能區移到「常用」頁籤:「設定格式化的條件」、「新增規則」。

Excel出貨單價檢查(下):格式化條件及色彩篩選 實務應用 第3張

四、設定規則計算式

跳出「新增格式化規則」視窗,「選取規則類型」為「只格式化包含下列的儲存格」,下面的「儲存格值」設定為「大於或等於」「3」,然後點擊「格式」。

Excel出貨單價檢查(下):格式化條件及色彩篩選 實務應用 第4張

五、提示異常方式

跳出「儲存格格式」視窗,移到「填滿」頁籤,設置黃色。

Excel出貨單價檢查(下):格式化條件及色彩篩選 實務應用 第5張

六、報表自動提示異常

F欄中,大於等於3的都填滿了黃色。重複步驟三到五,規則設定為小於或等於-3,便是一開始所想要達到的效果。

Excel出貨單價檢查(下):格式化條件及色彩篩選 實務應用 第6張

七、依儲存格色彩篩選

先選取整個報表(A1:F14),在上方功能區移到「資料」頁籤,點擊「篩選」,第一列會出現篩選三角形,點擊「F1」儲存格的三角形,跳出來的視窗移到「依色彩篩選」,在「依儲存格色彩篩選」的子視窗中,選擇黃色。

Excel出貨單價檢查(下):格式化條件及色彩篩選 實務應用 第7張

八、異常出貨單明細

大功告成,所有異常項目除了標註黃色,而且還篩選出來了,如果有需要,可以將這些異常項目另外貼上,方便後續追踪管理。

Excel出貨單價檢查(下):格式化條件及色彩篩選 實務應用 第8張

從原始資料深層挖掘出追踪管理重點的Excel應用

這一節文章,主要將格式化條件和色彩篩選結合在一起,從而在報表呈現上,只顯示異常項目,由於非異常項目沒有用處,所以這裡整理出來的資料,很乾淨而且切合管理使用。最後特別提醒,如此經過層層操作,習慣上要養成將公式函數保留在原始明細,異常的結果直接以值貼上,繼續往下做,否則容易造成錯亂,這個如果讀者有實際遇過,應該會有深切的領悟。