วันศุกร์ที่ 10 สิงหาคม พ.ศ. 2555

การใช้ Vlookup ข้าม WorkSheet

เราสามารถใช้ ฟังก์ชั่น Vlookup เพื่อดึงข้อมูลข้าม worksheet ได้ แต่เงื่อนไขเหมือนเดิม คือ ข้อมูลที่เราต้องการดึงมาใช้งานจำเป็นต้อง มีการจัดเรียงคอลัมท์แรก จากน้อยไปมากเสมอ (sort data from A to Z)
เราจะใช้โจทย์จาก วิธีใช้ Vlookup
Excel worksheet for using Vlookup cross sheet

แต่แทนที่เราจะสร้าง ฐานข้อมูลนี้ใน Worksheet เดียวกัน เราสร้าง Worksheet ข้อมูลใช้ชื่อว่า "data" ส่วนอีก Worksheet ที่ต้องการดึงข้อมูลมา ให้ชื่อว่า "record" ตามรูปด้านบน


= vlookup (lookup_value,table_array,col_index_num,[range_lookup])

ซึ่งวิธีทำ Vlookup ข้าม WorkSheet มีอยู่ 2 วิธี

  1. ไม่ได้ตั้งชื่อให้พื้นที่เซลส์ (Named Range)

    • เมื่อตอนแทนค่าของ Table_array ให้ใส่ comma "," แล้วไปคลิกที่ tab sheet ของ แผ่นงาน จากนั้นให้ เลือกคลุมพื้นที่ ที่ต้องการใน Worksheet data


  2. กรณีที่เราตั้งชื่อให้พื้นที่เซลส์แล้ว และสมมุติให้ Named Range คือ "Petrol"

    • ให้ใช้คำว่า "Petrol" ตอนแทนค่า Table_array ในสูตรได้เลย

ตัวอย่างการใช้งาน Vlookup ข้าม Worksheet

ตารางด้านล่างคือผลลัพท์จาก "record" sheet , และใน Column K จะแสดงรายละเอียดของฟังก์ชั่นที่ถูกใช้งานใน Column J
RowColumn
H
I
J
K
หาราคาน้ำมันLookup_valueผลลัพท์
ราคา
การใช้ฟังก์ชั่นใน Column J
5 Gasohol 91ESSO
35.08
=VLOOKUP(I5,data!B5:E13,3,FALSE)
6DieselPETRONAS
29.83
=VLOOKUP(I6,data!B5:E13,4,FALSE)
7Gasohol 95PT
36.83
=VLOOKUP(I7,data!B5:E13,2,FALSE)
8 Gasohol 91SHELL
35.08
=VLOOKUP(I8,data!B5:E13,3,FALSE)
9 Gasohol 95PURE
37.23
=VLOOKUP(I9,Petrol,2,FALSE)
10 DieselBCP
29.53
=VLOOKUP(I10,Petrol,4,FALSE)
11 GasoholSUSCO
42.63
=VLOOKUP(I11,Petrol,2,FALSE)
12 Gasohol 95CALTEX
37.23
=VLOOKUP(I12,Petrol,2,FALSE)
13 DieselPTT
29.53
=VLOOKUP(I13,data!B5:E13,4,FALSE)

สิ่งที่ต่างกันในฟังก์ชั่นที่แสดงไว้ที่ Column J คือค่าของ Table_array เท่านั้น นอกนั้นเหมือนกันหมด และ ตามตัวอย่างนี้ยังไม่ได้กำหนดค่า "$" ไว้ที่ Table_array  ซึ่งหากข้อมูลที่ต้องการหา ถูก input ในคอลัมท์เดียวกัน การใช้ค่า "$" แล้ว copy formula ลงมาเลย งานจะรวดเร็วขึ้นมาก ดูวิธีใช้ $

Reactions:

6 ความคิดเห็น:

  1. ขอบคุณมากเลยค่ะ ดีมากเลย

    ตอบลบ
  2. ไม่ระบุชื่อ6/27/2558 3:37 หลังเที่ยง

    =VLOOKUP(A69,Sheet1!$A$2:$B$102,2,FALSE)
    สูตรนี้มันผิดตรงไหนค่ะ มันไม่แสดงค่า ที่ค้นหา แต่มันขึ้นแบบนี้เลย เสมือนว่าพิมพ์ตัวอักษร ไม่ใช่สูตร

    ตอบลบ
  3. ถ้า Format ของเซลล์ที่ลงสูตร เป็น TEXT จะไม่แสดงผลลัพท์ ควรเปลี่ยนเป็น General ก่อน

    ตอบลบ
  4. ถ้าต้องการใช้Vlookupหา 2 lookup value ได้ไหมครับ เช่นการหา ชื่อพนักงาน จากตำแหน่ง และอายุ

    ตอบลบ
    คำตอบ
    1. ต้องเพิ่ม 1 คอลัมภ์ แล้ว เชื่อมข้อมูล ตำแหน่ง และ อายุ ด้วย concatenate เช่น ตำแหน่ง อยู่ที่ C อายุ อยู่ D ให้ใช้ =C2&D2 ใส่ในคอลัมภ์ที่เพิ่มใหม่ สมมุติว่าเป็น คอลัมภ์ A แล้วค่อยใช้ vlookup

      ลบ
    2. หรือใช้ ฟังก์ชัน index&match สมมุติว่า
      B2:B5=ชื่อพนักงาน
      C2:C5=ตำแหน่ง
      D2:D5=อายุ
      E และ F เป็นเซลล์ที่ใส่เงื่อนไขที่ต้องการค้นหา คือ ตำแหน่ง กับอายุ
      เมื่อพิมพ์สูตรเสร็จต้องกด CTRL+SHIFT+ENTER

      ={INDEX(B2:B5,MATCH(E7&F7,C2:C5&D2:D5,0))}

      ลบ