1 / 29

查閱與參照

查閱與參照. 蕭世斌 3/21/ 2011. 查表. 我們經常會碰到查表問題. VLOOKUP. 搜尋欄. 結果欄. 2. 3. 1. 4. 5. 6. 7. 8. 找到. 傳回. VLOOKUP 函數 及參數. = VLOOKUP ( lookup_value , table_array , col_index_num , range_lookup ). VLOOKUP. 範圍搜尋 ( 大約符合 ) 參數設置： True 第一欄 必須排序 (A->Z) 正確值搜尋 ( 完全符合 ) 參數設置： False 第一欄 不用排序

vladimir-reyes
Download Presentation

查閱與參照

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 查閱與參照 蕭世斌 3/21/ 2011

  2. 查表 我們經常會碰到查表問題

  3. VLOOKUP 搜尋欄 結果欄 2 3 1 4 5 6 7 8 找到 傳回

  4. VLOOKUP函數及參數 =VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

  5. VLOOKUP • 範圍搜尋(大約符合) • 參數設置：True • 第一欄必須排序(A->Z) • 正確值搜尋(完全符合) • 參數設置： False • 第一欄不用排序 • 找不到傳回#N/A • 第一欄若為文字，允許使用?及* • ~?=>?，~* => *

  6. 範圍搜尋 只要輸入範圍的最小值，排列必須由小到大 0 ~ 110 0 2.10 111 3.02 111 ~ 330 非營業夏季電費 0~110度，每度2.10元 111~330度，每度3.02元 331~500度，每度4.05元 501~700度，每度4.51元 701度以上，每度5.10元 331 ~ 500 331 4.05 501 4.51 501 ~ 700 701 ~ ∞ 701 5.10

  7. 正確值搜尋(完全符合) 不需要排列，找不到傳回#N/A US 33.2 JPY 0.275 匯率表 US 33.2 JPY 0.275 EU 44.3 GBP 49.3 NT 1 EU 44.3 GBP 49.3 NT 1

  8. VLOOKUP注意事項 • 第一欄為文字時 • 是否有空格隱藏在文字頭尾 • 第一欄為數字時 • 格式是否為文字

  9. HLOOKUP 除了欄、列互換外，其他和VLOOKUP一樣 搜尋列 找到 1 2 3 4 結果列 5 傳回 6

  10. LOOKUP(向量型態) 搜尋欄不須要第一欄，可以任意指定範圍 只提供範圍搜尋，所以一定要由小至大排列 搜尋範圍 找到 結果範圍 傳回

  11. LOOKUP(向量型態)

  12. LOOKUP(lookup_value, array) 不要使用陣列形式的LOOKUP

  13. MATCH函數 MATCH(lookup_value, lookup_array, [match_type]) 表格1 1 =MATCH(18, 表格1, 0) =3 1 9 13 2 3 18 4 8 65 5 找到值為18在第3列 32 6

  14. MATCH有三種搜尋方式 • 範圍搜尋(1) • 必須排列(由小至大) • 填入範圍最小值 • 預設值 • 正確值搜尋(0) • 不必搜尋，沒找到回#N/A • 可以用”?”、”*”符號 • 範圍搜尋(-1) • 必須排列(由大至小) • 填入範圍最大值 match_type 參數

  15. Match Type = 1 大 20 (4) 11 (3) 6 (2) 小 1(1)

  16. Match Type = -1 100 500 750 1000

  17. Match Type Type =1 (小於) Type = -1 (大於) 550 (1) 0 (1) 399 (2) 101 (2) 250 250 99 (3) 401 (3) 20 (4) 551 (4)

  18. INDEX函數 =INDEX(array, row_num, column_num) 至少要有一個參數 表格1 2 3 1 4 5 6 7 8 1 2 3 23 4 =Index(表格1, 3, 5) =23 5 6

  19. INDEX函數 =INDEX(array, row_num, column_num) 表格1 1 1 9 只有一欄，可省略欄位 13 2 =Index(表格1, 3) =18 3 18 4 8 65 5 32 6

  20. INDEX函數 =INDEX(array, row_num, column_num) 只有一列，可省略列位 表格1 1 4 5 2 6 3 9 13 8 65 32 18 =Index(表格1, , 3) =18

  21. INDEX函數 =INDEX(array, row_num, 0) 欄位設 0，傳回整列 表格1 2 3 1 4 5 6 7 8 1 2 2 5 3 3 4 1 1 1 3 4 =Sum(Index(表格1, 3, 0)) =20 5 6

  22. 多重函數 將index回傳陣列的值加總 index的回傳值當SUM參數 =sum(index(表格1, 3, 0)) 最外層函數優先

  23. INDEX函數 =INDEX(array, 0, column_num) 列位設 0，傳回整欄 表格1 2 3 1 4 5 6 7 8 1 2 2 5 4 3 1 4 1 5 1 6 =Sum(Index(表格1 , 0, 5)) =14

  24. INDEX與MATCH合併使用 array2 array1 1 US 32.5 2 EU 48 3 GBP 50 4 JPY 0.32 5 NT 1 =INDEX(array2,MATCH(“GBP”, array1, 0))

  25. INDIRECT函數 = INDIRECT(ref_text, A1)

  26. INDIRECT函數 =INDIRECT(C2) = 25 =INDIRECT("F3") = 25 A B C D E F G H 1 2 “F3” 3 25 4 5

  27. INDIRECT函數 =SUM(INDIRECT(C2)) = 65 =SUM(INDIRECT("F3:G4")) = 65 A B C D E F G H 1 2 “F3:G4” 3 25 20 4 15 5 5

  28. 不同經銷商適用不同費率表 =VLOOKUP([數量],INDIRECT(VLOOKUP([經銷類別],費率分配表,2,FALSE)),2,TRUE) 1) 先找出適用之費率表 2) 用數量找出單價

  29. 謝謝 網站：http://www.masterhsiao.com.tw Email: stanley@masterhsiao.com.tw

More Related