Excel如何vlookup作前十大排行分析




會計在統計資料時,基於重大性原則,都會先作個排行,抓出前幾大的項目,然後再針對其中差異較大者,分析說明,有時候排行的依據不同,為了避免前後不一致或重工,最好有個機制,可以自動篩選出已分析過的項目,以下分享實際作法:

一、如圖所示,「本月生產彙總-生產數量排行」,依照完工數量排行的前十大料號,後面有兩個月的成本單價,針對單價差異10元以上者,分析說明。

本月生產彙總-生產數量排行

二、除了生產數量,還有依照生產金額的排行榜,一樣,先抓出前十大,針對兩個月的成本單價,差異10元以上者,分析說明。這邊有個問題,「A80R8DB」這個料號在前數量排行已經上榜,而且分析過了,除了直接引用,毋須再分析之外,也要注意兩個資料金額要一致,不然就穿幫了。

依照生產金額的排行榜

三、檢查方式其中之一,是把除了差異以外的數量金額單價都加起來,也就是「SUM(C17:F17)」,然後再和另外一種排行的相同料號相減:「VLOOKUP(B17,$B$3:$I$12,8,0)」,如果不是零,表示有不一致,例如圖中的「A80R0GB」。

VLOOKUP(B17,$B$3:$I$12,8,0)

四、除了一致性檢查,還應該把先前已分析過的項目找出來,直接複製貼上即可:「=VLOOKUP(B17,$B$3:$H$12,7,0)」。顯示為「0」,表示這個料號先前的排行也有,不用分析,顯示文字,表示先前分析過了,顯示「#N/A」,表示先前排行沒有這個料號,當然,一定是沒有分析過。

=VLOOKUP(B17,$B$3:$H$12,7,0)

五、除了簡單VLOOKUP,還可以加一些判斷式,直接在說明欄位上呈現想要的結果:「=IF(OR(ISBLANK(VLOOKUP(B18,$B$3:$H$12,7,0)),ISERROR(VLOOKUP(B18,$B$3:$H$12,7,0))),””,VLOOKUP(B18,$B$3:$H$12,7,0))」,這函數看起來噁心,但其實意思很直觀,如果先前排行沒有此料號或者先前有料號但不用分析,就顯示空白,否則引用先前的說明。

除了簡單VLOOKUP,還可以加一些判斷式

六、如果是兩種排行十個料號,不一定要函數,稍微看一下便可完成,但實際上,有可能還要其它方式排行,例如單價,而且也許要更多的料號,這時候,沒有函數幫助,眼睛一個一個看,必定花掉,而且容易出錯,所以還是讓計算機輔助,可以把其它排行VLOOKUP列出來,一目瞭然,也可以函數再噁心一點:「=IF(OR(ISBLANK(VLOOKUP(B31,$B$3:$H$26,7,0)),ISERROR(VLOOKUP(B31,$B$3:$H$26,7,0))),””,VLOOKUP(B31,$B$3:$H$26,7,0))」總之,善用Excel避免錯誤及提高效率,才是聰明的會計人。

把其它排行VLOOKUP列出來,一目瞭然






當前文章延伸閱讀:
VLOOKUP