贊贊小屋

Excel分割報表欄位,資料剖析和巢狀函數公式組合

Excel資料分析時有基本的報表格式要求,欄位必須分開。工作中可能遇到供應商報表將代碼和名稱合在一起的情況,本文介紹資料剖析及文字函數分割欄位。

一、供應商欄位

ERP系統跑出來應付報表,供應商代碼和供應商名稱在同一欄位。

Excel分割報表欄位,資料剖析和巢狀函數公式組合 經典指令 第1張

二、資料剖析

上方功能區「資料>資料工具」,執行「資料剖析」。

Excel分割報表欄位,資料剖析和巢狀函數公式組合 經典指令 第2張

三、原始資料類型

步驟3之1。「原始資料類型」有「分隔符號」和「固定寛度」兩個選項,這裡因為供應商代碼固定為六碼且在前,其實兩個選項皆可,在此選擇預設的「分隔符號」。

Excel分割報表欄位,資料剖析和巢狀函數公式組合 經典指令 第3張

四、分隔符號

步驟3之2。「分隔符號」選擇「空格」,範例資料裡有供應商代碼和供應商名稱中間有兩個連續空格,因此「連續分隔符號視為單一處理」維持預設的勾選,下方「預覽分隔結果」如所期待。

Excel分割報表欄位,資料剖析和巢狀函數公式組合 經典指令 第4張

五、欄位的資料格式

步驟3之3。範例資料型態相對簡單,最後一個步驟在「欄位的資料格式」維持預設的「一般」,「目標儲存格」可以選擇在哪生成分割後的資料,在此沒有特別需要,同樣維持「$A$1」即可。

Excel分割報表欄位,資料剖析和巢狀函數公式組合 經典指令 第5張

六、剖析後資料

原本A欄中的供應商名稱果然依照規則被剖析到B欄。

Excel分割報表欄位,資料剖析和巢狀函數公式組合 經典指令 第6張

七、函數資料剖析

依照資料規則設計函數,「LEFT(A3),6)」作用是取左側前六碼,正是供應商代碼。由於供應商名稱長度不一,先以「LEN(A3)」確定整個文字串長度,再以「=RIGHT(A3,LEN(A3)-8)」,因為供應商代碼為六位,中間兩個空格,全部長度減掉9即為供應商名稱字數,以RIGHT函數從右側取此長度即為供應商名稱。

Excel分割報表欄位,資料剖析和巢狀函數公式組合 經典指令 第7張

資料剖析指令與文字函數分割

本文範例以指令和函數執行都可以完成任務,贊贊小屋偏好函數,因為工作上很多例行性報表是每周每月定期編製,只要把編製流程依照規則設計好函數,公式貼到系統報表完成了資料整理。贊贊小屋以前曾經每個星期都要編製資金預估表,每個流程都設計好函數公式,本來至少一個小時完成的工作,簡化成每周五下午花個十分鐘完事交差,輕輕鬆鬆準時下班,那時候真正體會到Excel的強大之處。

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