職場上常常需要分析一大堆有的沒的資料或數據,這時候就非常需要EXCEL這個大幫手!
解說搭配截圖,希望讓大家能簡單迅速的立刻上手,話不多說,快來聽古吧
〈一〉 V-LOOK-UP
- 使用時機:要將A資料的某數值對應到B資料,前提必須AB兩份資料有相同的對照欄位。
- 例子與步驟:
兩份資料同時都有人名的欄位,若想把右邊的Class資料整合到左邊,找出每個人的班級,就可以使用V-LOOK-UP,而不用一筆一筆輸入。
在左邊資料新增一欄位D,滑鼠點在D2之後按上面的fx,或直接輸入=VLOOKUP()
跳出公式視窗
Lookup_value:選擇兩份資料都有的對照資料。現在滑鼠點在D2,所以此欄位要填入A2 (或直接點該欄)
Table_array:你要從哪支表抓取資料?
此例中,要從右邊的表格抓資料,所以直接把右邊表格整個框起來,就是G1:I6
Col_index_num:你要抓取的資料是對照資料向右邊數去第幾個欄位?
此例中,要抓取的是Class,而對照資料Name為第一欄,以此欄為基準向右,Class是第二欄,所以這邊要填入2。
Range_lookup:這邊只有0或1的選擇,0代表只回傳完全符合的數值;1的話會找出最接近的值。
完成之後,將D2下拉把公式填滿,如下圖,會發現Helen和Coco顯示為#N/A,是因為右邊的資料並沒有這兩個人。
簡單幾的步驟就可以把不同的資料對照在一起囉!
- 小提醒:
(1) Col_index_num,你要抓取的資料是對照資料向右邊數去第幾個欄位?切記,對照資料一定要在左,要被選取的資料一定要在對照資料的右方。同樣以上面的例子說明,把右邊資料的Name和Class互換一下位置(如下圖),樞紐就會失敗。
(2) 公式會造成檔案過大,建議可將資料複製後右鍵貼上「值」。
〈二〉 樞紐
1.使用時機:想分析大量、筆數驚人的資料時。
2.例子與步驟:
若想要分析下表各業務每月的銷售業績,就可以使用樞鈕分析。
將滑鼠點在空白處,選擇「插入」中的「樞紐分析表」。
之後出現如下圖,第一行空白是要”選取資料範圍”,直接用滑鼠把A1到D15框起來(手動輸入也可以);第二行空白是這個樞紐表格你要放在哪裡?不更改的話新的表格就會在一開始點擊的空白處,如果希望分析表格在另外一個分頁,就直接點選該分頁。
確定之後會出現下圖,接下來從右邊的選單選擇你要的資料
這次是要分析每個業務各月份的銷售額,所以以業務為主把業務放在左下(列標籤)
各月份是第二主角,所以放在右上(欄標籤),最後要觀看的銷售額,所以放在值的欄位
不同的欄位設定會產生不同的報表,就依照您當時的需求囉,這邊示範幾種例子
例1:
例2:
樞紐都會自動小計,如果不想要表格太亂,可以在”銷售月份”點右鍵,把「小計-銷售月份」取消,資料看起來會比較清爽。(個人偏好囉)
3. 小提醒:
右邊樞鈕欄位清單中的「值」預設是”加總”,可以按右上方的倒三角選擇不同的計算方式(如:計算個數)和值的表現方式(如:$)。依此例,如果想把樞紐資料照銷售額大小排序,按下第一個欄位的倒三角選擇「更多排序選項」→遞減(金額),樞紐表格就會依照金額大小排列。
〈三〉 向下填滿上方字元 (“到”的利用F5)
1.使用時機:系統匯出的報表常常會省略下方相同的資料,導致無法篩選或分析資料,如何將空格填滿,就需要利用「到」的功能囉。
2.例子與步驟:
如下表,月份只列出第一筆,其他都空白,要把3-6列填滿7、8-11列填滿8、14-15列填滿10,先把A1到A15選起來,之後按F5,或是上方
分頁常用的望遠鏡「尋找與選取」,接著出現一個小視窗,點選”特殊”
之後跳出「特殊目標」視窗,選擇”空格”
確定之後,表格會變成下圖,(注意!不要按到滑鼠喔)此時欄位已經設定在A1到A15的第一個空格,就是A3。
此時,直接輸入=A2,之後按ctrl+Enter
江江~空格就會被填滿囉!
〈四〉 擷取並回傳字串的特定字元 (MID)
1.使用時機:想要拆解冗長的料號時
2.例子與步驟:想要分析料號的系列別,在下表新增一欄位E,選擇MID公式,料號在C欄位,所以第一個數值是C2;此例中的料號前5碼是系列別,後面是規格,我只要系列別,所以是字串的第1到第5個字元。
完成之後,E欄位就會顯示C欄位前五碼的字元囉。
小提醒:使用MID通常被回傳的字元必須有固定的編排邏輯,例如公司名稱字數不一,xx有限公司、xxxxx股份有限公司,這樣就無法限定要回傳的字串是第幾到第幾。
以上說明希望大家聽得懂,下台一鞠躬
留言列表