2015年10月5日 星期一

Excel-分離資料到對應的表格儲存格中(FIND,LEFT,MID)

學科小老師調查了購買相關補充資料的名單給某個老師,長的像下圖左的內容。老師問說可以轉換為如下圖右的資料表,以方便計算購買數量及分發補充資料。
這是個常見的問題,觀察下圖左,這個小老師也算整理的很工整(格式對齊),其中「全」表示三種補充資料都要採購。現在就來看看如何轉換為表格式的資料。
Excel-分離資料到對應的表格儲存格中(FIND,LEFT,MID)

【公式設計與解析】
1. 取出座號
儲存格C2:=LEFT(A2,2)
2. 取出姓名
儲存格D2:=MID(A2,4,3)
3. 取出購買項目
儲存格E2:
=IFERROR(IF(FIND(E$1,$A2),"V",""),"")&IFERROR(IF(FIND("全",$A2),"V",""),"")
FIND(E$1,$A2):利用 FIND 函數找尋儲存格A2中是否包含儲存格E1的內容,若找的到,則會傳回一個數字(位置,第幾個字);若找不到,則傳回錯誤訊息。
IF(FIND(E$1,$A2),"V",""):將 FIND 函數的傳回值:數字/錯誤訊息),轉換為:V/空白。若 FIND 函數的傳回值是數字,在 IF 函數中視為 TRUE,則顯示「V」;若為「錯誤訊息」,則顯示空白。
IFERROR(IF(FIND(E$1,$A2),"V",""),""):將上式傳回的錯誤訊息顯示為空白。
IFERROR(IF(FIND("全",$A2),"V",""),""):同理,利用 FIND 函數找尋儲存格A2中是否包含「全」這個字,若為「是」,則顯示「V」;若為「否」,則傳回錯誤訊息。
你可能會問,為何要將以上二式以「&」運算子串接?因為這兩個式子永遠只會有一個成立,而不成立者只會顯示空白,相當於沒有作用。
最後,複製儲存格E2,貼至儲存格E2:G2。複製儲存格E2:G2,貼至儲存格E2:G18。

沒有留言:

張貼留言

檢視其他文章

好康東東