贊贊小屋

Excel如何整理不規則資料

先前寫過一篇分享文,是關於成本結算異常訊息的資料剖析,當時的範例很簡單,只要一次剖析、或者套用一次函數,效果就出來了。不過,範例往往簡單,真實世界往往複雜,資料有些相同的特性,我們可以歸納統計,才能一個招式用到底。當系統跑出來的報表或是別人給的資料,不是那麼完美,沒有明顯統一的規則可循的時候,的確是有點麻煩,因為Excel函數需要的是規則,我們唯一能做的,就是儘量在其中找到規則,也許不是統一適用的規則,是雜七雜八的、東一塊西一塊的,這也行,找到一塊是一塊,畢竟只要能夠省時省力,就值得一試。

一、到手的資料很令人無言,看起來很亂,想要分門別類先行整理,但似乎用剖析或函數都沒辦法一次解決。

到手的資料很令人無言

二、通常,我拿到這種東西第一個直覺反射:「排序」。管它有多亂,先排序看看!

「排序」

三、按下「排序」指令,跳出來的視窗很容易理解,以「異常狀況」這一欄為基準,依照字母順序重新排列,注意到右上角有個「我的資料有標題」。

依照字母順序重新排列

四、當我們的選取範例第一行是標題,就可以像剛才那樣排序,如果資料本身沒有標題列,例如我們只是選取報表中間一段的範例,這時候就要把「我的資料有標題」的勾勾點掉,排序方式就會便成Excel欄位,注意到「排序對象」的值預設為儲存格內容,但其實也有其它選項,一般用到最多的還是預設的「值」,但不排除其它選項有用到的情況,多知道一點總是好的。

不排除其它選項有用到的情況

五、果然排序之後,資料開始有點模樣出來了,上面開頭都是料號,統一為七碼,這個就很容易剖析了,下面是文字開頭,不過也正因為如此,剛好已經依照成本結算的錯誤類型歸類了。

上面開頭都是料號,統一為七碼

六、有些時候我們沒有要剖析,只是想在一堆混雜之中,找出具有特定內容的資料,這時候「篩選」是我們最好的朋友。

「篩選」

七、隨著微軟持續改版Excel,「篩選」這個老指示越來越強大,除了傳統的「文字篩選」,還可以「依色彩篩選」,在這裡我們想要找出某種類型的錯誤訊息,所以用「包含」。

還可以「依色彩篩選」

八、包含「工單已入庫」,注意到這裡可以多重條件,「且」、「或」,Excel還很貼心說明了「?」(單一萬用字元)和「*」(萬用字元)。

「?」(單一萬用字元)和「*」(萬用字元)

九、不錯吧,輕輕鬆鬆,Excel管家幫我拎出我要的東西了。

Excel管家幫我拎出我要的東西了

十、像成本異常檢核表這種東西,每個月都要來一次,資料很多很雜,但基本上大同小異,就是那幾種,所以如果能夠找到大致上的規則,也是很方便用函數處理的,如圖所示:「=MID(B2,8,2)」,一次解決!

「=MID(B2,8,2)」

十一、工具不嫌多,再多介紹一個:「=SEARCH(“工單已入庫”,B2)」。「SEARCH」這個函數的官方說明:「在某個文字字串內找到另一個文字字串,並傳回該文字字串在第一個文字字串中的起始位置。」,在我們的例子裡,顯示結果是「8」,表示「工單已入庫」出現在「B2」的第8個位置。找不到這個字串時,會顯示「#VALUE!」。

「=SEARCH("工單已入庫",B2)」

十二、像這種情況,還可以搭配「IFERROR」函數:「=IFERROR(SEARCH(“工單已入庫”,B2),SEARCH(“結案工單依轉出”,B2))」,表示如果第一個找不到,就找第二個,這樣是兩層的IF邏輯結構,還可以再繼續套,三層、四層,端視實務上遇到的情況囉。

搭配「IFERROR」函數

本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1