Excel如何檢查出貨單價(下):異常篩選




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

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

先前文章編製好的樞紐分析表

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

值與來源格式設定

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

設定格式化的條件」、「新增規則」

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

只格式化包含下列的儲存格

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

移到「填滿」頁籤,設置黃色

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

重複步驟三到五,規則設定為小於或等於-3

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

依儲存格色彩篩選

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

所有異常項目除了標註黃色,而且還篩選出來了

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






當前文章延伸閱讀:
經典函數