วิธีใช้ VLOOKUP ใน Excel

VLOOKUP เป็นหนึ่งในฟังก์ชันที่มีประโยชน์มากที่สุดของ Excel และยังเป็นหนึ่งในฟังก์ชันที่เข้าใจน้อยที่สุด ในบทความนี้เราทำให้ VLOOKUP เข้าใจผิดโดยใช้ตัวอย่างในชีวิตจริง เราจะสร้างเทมเพลตใบแจ้งหนี้ที่ใช้งานได้สำหรับ บริษัท สมมติ

VLOOKUP Excel เป็นฟังก์ชั่น บทความนี้จะถือว่าผู้อ่านมีความเข้าใจเกี่ยวกับฟังก์ชัน Excel อยู่แล้วและสามารถใช้ฟังก์ชันพื้นฐานเช่น SUM, AVERAGE และ TODAY ได้ ในการใช้งานทั่วไป VLOOKUP เป็นฟังก์ชันฐานข้อมูลซึ่งหมายความว่าทำงานกับตารางฐานข้อมูลหรือมากกว่านั้นก็คือรายการสิ่งต่างๆในแผ่นงาน Excel ประเภทของอะไร? ดีใด ๆเรียงลำดับของสิ่ง คุณอาจมีแผ่นงานที่มีรายชื่อพนักงานหรือผลิตภัณฑ์หรือลูกค้าหรือซีดีในคอลเลคชันซีดีของคุณหรือดวงดาวบนท้องฟ้ายามค่ำคืน มันไม่สำคัญจริงๆ

นี่คือตัวอย่างรายการหรือฐานข้อมูล ในกรณีนี้เป็นรายการผลิตภัณฑ์ที่ บริษัท สมมติของเราจำหน่าย:

โดยปกติรายการเช่นนี้จะมีตัวระบุที่ไม่ซ้ำกันสำหรับแต่ละรายการในรายการ ในกรณีนี้ตัวระบุที่ไม่ซ้ำกันจะอยู่ในคอลัมน์ "รหัสสินค้า" หมายเหตุ: สำหรับฟังก์ชัน VLOOKUP ในการทำงานกับฐานข้อมูล / รายการรายการที่ต้องมีคอลัมน์ที่มีตัวบ่งชี้เฉพาะ (หรือ“สำคัญ” หรือ“รหัส”) และคอลัมน์ที่จะต้องเป็นคอลัมน์แรกในตาราง ฐานข้อมูลตัวอย่างของเราข้างต้นตรงตามเกณฑ์นี้

ส่วนที่ยากที่สุดในการใช้ VLOOKUP คือการทำความเข้าใจว่ามีไว้เพื่ออะไร ลองดูว่าเราจะได้ความชัดเจนก่อนหรือไม่:

VLOOKUP ดึงข้อมูลจากฐานข้อมูล / รายการตามอินสแตนซ์ที่ระบุของตัวระบุเฉพาะ

ในตัวอย่างข้างต้นคุณจะแทรกฟังก์ชัน VLOOKUP ลงในสเปรดชีตอื่นพร้อมรหัสสินค้าและจะส่งกลับให้คุณทั้งคำอธิบายของสินค้าราคาหรือความพร้อมจำหน่ายสินค้า (ปริมาณ "ในสต็อก") ตามที่อธิบายไว้ในต้นฉบับ รายการ. ข้อมูลใดต่อไปนี้ที่จะส่งต่อคุณกลับไป คุณจะต้องตัดสินใจเรื่องนี้เมื่อคุณสร้างสูตร

หากสิ่งที่คุณต้องการคือข้อมูลชิ้นเดียวจากฐานข้อมูลการไปสร้างสูตรด้วยฟังก์ชัน VLOOKUP จะเป็นปัญหามาก โดยทั่วไปคุณจะใช้ฟังก์ชันประเภทนี้ในสเปรดชีตที่ใช้ซ้ำได้เช่นเทมเพลต ทุกครั้งที่มีคนป้อนรหัสรายการที่ถูกต้องระบบจะดึงข้อมูลที่จำเป็นทั้งหมดเกี่ยวกับรายการที่เกี่ยวข้อง

ลองสร้างตัวอย่างนี้: เทมเพลตใบแจ้งหนี้ที่เราสามารถนำมาใช้ซ้ำได้ใน บริษัท ที่สมมติขึ้นของเรา

ก่อนอื่นเราเริ่ม Excel และเราสร้างใบแจ้งหนี้เปล่า:

นี่คือวิธีการทำงาน: ผู้ที่ใช้เทมเพลตใบแจ้งหนี้จะกรอกชุดรหัสสินค้าในคอลัมน์“ A” จากนั้นระบบจะดึงคำอธิบายและราคาของสินค้าแต่ละรายการจากฐานข้อมูลผลิตภัณฑ์ของเรา ข้อมูลดังกล่าวจะใช้ในการคำนวณผลรวมของรายการสำหรับแต่ละรายการ

เพื่อวัตถุประสงค์ในการรักษาตัวอย่างนี้ให้เรียบง่ายเราจะค้นหาฐานข้อมูลผลิตภัณฑ์ในแผ่นงานแยกต่างหากในสมุดงานเดียวกัน:

ในความเป็นจริงฐานข้อมูลผลิตภัณฑ์จะอยู่ในสมุดงานแยกต่างหาก มันสร้างความแตกต่างเล็กน้อยกับฟังก์ชัน VLOOKUP ซึ่งไม่สนใจว่าฐานข้อมูลจะอยู่ในแผ่นงานเดียวกันแผ่นงานอื่นหรือสมุดงานที่แตกต่างกันโดยสิ้นเชิง

ดังนั้นเราจึงได้สร้างฐานข้อมูลผลิตภัณฑ์ของเราซึ่งมีลักษณะดังนี้:

ในการทดสอบสูตร VLOOKUP ที่เรากำลังจะเขียนอันดับแรกเราต้องป้อนรหัสสินค้าที่ถูกต้องในเซลล์ A11 ของใบแจ้งหนี้เปล่าของเรา:

ต่อไปเราจะย้ายเซลล์ที่ใช้งานไปยังเซลล์ที่เราต้องการให้เก็บข้อมูลที่ดึงมาจากฐานข้อมูลโดย VLOOKUP ที่น่าสนใจนี่คือขั้นตอนที่คนส่วนใหญ่เข้าใจผิด เพื่ออธิบายเพิ่มเติม: เรากำลังจะสร้างสูตร VLOOKUP ที่จะดึงคำอธิบายที่สอดคล้องกับรหัสรายการในเซลล์ A11 เราต้องการให้คำอธิบายนี้วางไว้ที่ไหนเมื่อเราได้รับ ในเซลล์ B11 แน่นอน นั่นคือที่ที่เราเขียนสูตร VLOOKUP: ในเซลล์ B11 เลือกเซลล์ B11 ทันที

เราจำเป็นต้องค้นหารายการฟังก์ชันที่มีอยู่ทั้งหมดที่ Excel มีให้เพื่อให้เราสามารถเลือก VLOOKUP และขอความช่วยเหลือในการกรอกสูตรได้ พบได้โดยคลิกแท็บสูตรก่อนจากนั้นคลิกแทรกฟังก์ชัน :

กล่องจะปรากฏขึ้นเพื่อให้เราสามารถเลือกฟังก์ชันใด ๆ ที่มีอยู่ใน Excel

หากต้องการค้นหาสิ่งที่เรากำลังมองหาเราสามารถพิมพ์คำค้นหาเช่น "lookup" (เนื่องจากฟังก์ชันที่เราสนใจคือฟังก์ชันการค้นหา ) ระบบจะส่งรายการฟังก์ชันที่เกี่ยวข้องกับการค้นหาทั้งหมดใน Excel กลับมาให้เรา  VLOOKUPเป็นรายการที่สองในรายการ เลือกคลิกตกลง

อาร์กิวเมนต์ฟังก์ชั่นจะปรากฏขึ้นกระตุ้นให้เราสำหรับทุกข้อโต้แย้ง (หรือพารามิเตอร์ ) ที่จำเป็นเพื่อให้สมบูรณ์ฟังก์ชัน VLOOKUP คุณสามารถคิดว่ากล่องนี้เป็นฟังก์ชันที่ถามคำถามต่อไปนี้:

1. คุณกำลังค้นหาตัวระบุเฉพาะใดในฐานข้อมูล
2. ฐานข้อมูลอยู่ที่ไหน
3. คุณต้องการดึงข้อมูลส่วนใดจากฐานข้อมูลที่เชื่อมโยงกับตัวระบุที่ไม่ซ้ำกันให้คุณ

อาร์กิวเมนต์สามตัวแรกจะแสดงเป็นตัวหนาแสดงว่าเป็นอาร์กิวเมนต์บังคับ (ฟังก์ชัน VLOOKUP ไม่สมบูรณ์หากไม่มีอาร์กิวเมนต์และจะไม่ส่งคืนค่าที่ถูกต้อง) อาร์กิวเมนต์ที่สี่ไม่เป็นตัวหนาหมายความว่าเป็นทางเลือก:

เราจะกรอกอาร์กิวเมนต์ตามลำดับจากบนลงล่าง

อาร์กิวเมนต์แรกที่เราต้องทำให้เสร็จสมบูรณ์คืออาร์กิวเมนต์Lookup_value ฟังก์ชันต้องการให้เราบอกว่าจะค้นหาตัวระบุที่ไม่ซ้ำกันได้ที่ไหน ( รหัสรายการในกรณีนี้) ที่ควรส่งคืนคำอธิบาย เราต้องเลือกรหัสรายการที่เราป้อนก่อนหน้านี้ (ใน A11)

คลิกที่ไอคอนตัวเลือกทางด้านขวาของอาร์กิวเมนต์แรก:

จากนั้นคลิกหนึ่งครั้งบนเซลล์ที่มีรหัสรายการ (A11) แล้วกดEnter :

ค่าของ“ A11” ถูกแทรกลงในอาร์กิวเมนต์แรก

ตอนนี้เราต้องป้อนค่าสำหรับอาร์กิวเมนต์Table_array กล่าวอีกนัยหนึ่งเราต้องบอก VLOOKUP ว่าจะหาฐานข้อมูล / รายการได้ที่ไหน คลิกที่ไอคอนตัวเลือกถัดจากอาร์กิวเมนต์ที่สอง:

ตอนนี้ค้นหาฐานข้อมูล / รายการและเลือกรายการทั้งหมด - ไม่รวมบรรทัดส่วนหัว ในตัวอย่างของเราฐานข้อมูลจะอยู่ในแผ่นงานแยกต่างหากดังนั้นเราจึงคลิกที่แท็บแผ่นงานก่อน

ต่อไปเราจะเลือกฐานข้อมูลทั้งหมดโดยไม่รวมบรรทัดส่วนหัว:

... และกดEnter ช่วงของเซลล์ที่แสดงถึงฐานข้อมูล (ในกรณีนี้คือ“ 'ฐานข้อมูลผลิตภัณฑ์'! A2: D7”) จะถูกป้อนโดยอัตโนมัติสำหรับเราในอาร์กิวเมนต์ที่สอง

ตอนนี้เราจำเป็นต้องใส่อาร์กิวเมนต์ที่สาม, col_index_numเราใช้อาร์กิวเมนต์นี้เพื่อระบุกับ VLOOKUP ว่าข้อมูลส่วนใดจากฐานข้อมูลเชื่อมโยงกับรหัสรายการของเราใน A11 เราต้องการส่งคืนให้เรา ในตัวอย่างนี้เราต้องการให้คำอธิบายของรายการนั้นกลับมาหาเรา หากคุณดูในแผ่นงานฐานข้อมูลคุณจะสังเกตเห็นว่าคอลัมน์“ คำอธิบาย” เป็นคอลัมน์ที่สองในฐานข้อมูล ซึ่งหมายความว่าเราต้องป้อนค่า“ 2” ในช่องCol_index_num :

โปรดทราบว่าเราไม่ได้ป้อน“ 2” ที่นี่เนื่องจากคอลัมน์“ Description” อยู่ในคอลัมน์Bบนแผ่นงานนั้น หากฐานข้อมูลเริ่มต้นในคอลัมน์Kของแผ่นงานเราจะยังคงป้อน“ 2” ในช่องนี้เนื่องจากคอลัมน์“ Description” เป็นคอลัมน์ที่สองในชุดเซลล์ที่เราเลือกเมื่อระบุ“ Table_array”

สุดท้ายเราต้องตัดสินใจว่าจะใส่ค่าลงในอาร์กิวเมนต์ VLOOKUP สุดท้ายrange_lookup อาร์กิวเมนต์นี้ต้องการค่าจริงหรือเท็จหรือควรเว้นว่างไว้ เมื่อใช้ VLOOKUP กับฐานข้อมูล (ตามความเป็นจริง 90% ของเวลา) วิธีการตัดสินใจว่าจะใส่อะไรในอาร์กิวเมนต์นี้สามารถคิดได้ดังนี้:

ถ้าคอลัมน์แรกของฐานข้อมูล (คอลัมน์ที่มีตัวระบุที่ไม่ซ้ำกัน) เรียงตามตัวอักษร / ตัวเลขจากน้อยไปหามากก็เป็นไปได้ที่จะป้อนค่าจริงลงในอาร์กิวเมนต์นี้หรือเว้นว่างไว้

หากคอลัมน์แรกของฐานข้อมูลไม่ได้เรียงลำดับหรือเรียงจากมากไปหาน้อยคุณต้องป้อนค่าเท็จลงในอาร์กิวเมนต์นี้

เนื่องจากคอลัมน์แรกของฐานข้อมูลของเราไม่ได้ถูกจัดเรียงเราจึงใส่เท็จลงในอาร์กิวเมนต์นี้:

แค่นั้นแหละ! เราได้ป้อนข้อมูลทั้งหมดที่จำเป็นสำหรับ VLOOKUP เพื่อส่งคืนค่าที่เราต้องการ คลิกปุ่มตกลงและสังเกตว่าคำอธิบายที่ตรงกับรหัสสินค้า“ R99245” ถูกป้อนอย่างถูกต้องในเซลล์ B11:

สูตรที่สร้างขึ้นสำหรับเรามีลักษณะดังนี้:

ถ้าเราใส่ที่แตกต่างกันรหัสสินค้าเข้าไปในเซลล์ A11 เราจะเริ่มเห็นอำนาจของฟังก์ชั่น VLOOKUP นี้: การเปลี่ยนแปลงของเซลล์คำอธิบายเพื่อให้ตรงกับรหัสรายการใหม่:

เราสามารถดำเนินการชุดขั้นตอนที่คล้ายกันเพื่อรับราคาของสินค้าคืนในเซลล์ E11 โปรดทราบว่าต้องสร้างสูตรใหม่ในเซลล์ E11 ผลลัพธ์จะเป็นดังนี้:

…และสูตรจะมีลักษณะดังนี้:

โปรดทราบว่าความแตกต่างเพียงอย่างเดียวระหว่างสองสูตรคืออาร์กิวเมนต์ที่สาม ( Col_index_num ) ได้เปลี่ยนจาก "2" เป็น "3" (เนื่องจากเราต้องการข้อมูลที่ดึงมาจากคอลัมน์ที่ 3 ในฐานข้อมูล)

หากเราตัดสินใจซื้อ 2 รายการนี้เราจะป้อน“ 2” ลงในเซลล์ D11 จากนั้นเราจะป้อนสูตรง่ายๆลงในเซลล์ F11 เพื่อรับผลรวมของบรรทัด:

= D11 * E1

…ซึ่งมีลักษณะดังนี้…

การกรอกเทมเพลตใบแจ้งหนี้

เราได้เรียนรู้มากมายเกี่ยวกับ VLOOKUP จนถึงตอนนี้ อันที่จริงเราได้เรียนรู้ทั้งหมดที่เราจะเรียนรู้ในบทความนี้ สิ่งสำคัญที่ควรทราบคือ VLOOKUP สามารถใช้ในสถานการณ์อื่น ๆ นอกเหนือจากฐานข้อมูลได้ นี่เป็นเรื่องปกติน้อยกว่าและอาจกล่าวถึงในบทความ How-To Geek ในอนาคต

เทมเพลตใบแจ้งหนี้ของเรายังไม่สมบูรณ์ ในการดำเนินการให้เสร็จสมบูรณ์เราจะดำเนินการดังต่อไปนี้:

1. เราจะลบรหัสสินค้าตัวอย่างออกจากเซลล์ A11 และ“ 2” จากเซลล์ D11 สิ่งนี้จะทำให้สูตร VLOOKUP ที่สร้างขึ้นใหม่ของเราแสดงข้อความแสดงข้อผิดพลาด:

   
   

   
   เราสามารถแก้ไขได้โดยใช้ฟังก์ชันIF ()และISBLANK ()ของ Excel อย่างรอบคอบ เราเปลี่ยนสูตรของเราจากสิ่งนี้…      = VLOOKUP (A11, 'Product Database'! A2: D7,2, FALSE) …เป็น… = IF (ISBLANK (A11),””, VLOOKUP (A11, 'Product Database'! A2 : D7,2, FALSE))

   
   

2. เราจะคัดลอกสูตรในเซลล์ B11, E11 และ F11 ลงไปที่ส่วนที่เหลือของแถวสินค้าของใบแจ้งหนี้ โปรดทราบว่าหากเราทำเช่นนี้สูตรที่ได้จะอ้างถึงตารางฐานข้อมูลไม่ถูกต้องอีกต่อไป เราสามารถแก้ไขปัญหานี้ได้โดยเปลี่ยนการอ้างอิงเซลล์สำหรับฐานข้อมูลเป็นการอ้างอิงเซลล์แบบสัมบูรณ์ อีกทางเลือกหนึ่ง - และดีกว่านั้น - เราสามารถสร้างชื่อช่วงสำหรับฐานข้อมูลผลิตภัณฑ์ทั้งหมด (เช่น "ผลิตภัณฑ์") และใช้ชื่อช่วงนี้แทนการอ้างอิงเซลล์ สูตรจะเปลี่ยนจากนี้…      = IF (ISBLANK (A11),””, VLOOKUP (A11, 'Product Database'! A2: D7,2, FALSE)) ... เป็น…       = IF (ISBLANK (A11),”” , VLOOKUP (A11, ผลิตภัณฑ์, 2, FALSE)) ... แล้ว คัดลอกสูตรลงไปที่ส่วนที่เหลือของแถวรายการใบแจ้งหนี้
3. เราก็อาจจะ“ล็อค” เซลล์ที่มีสูตรของเรา (หรือมากกว่าปลดล็อคอื่น ๆเซลล์) แล้วป้องกันแผ่นงานในการสั่งซื้อเพื่อให้แน่ใจว่าสูตรที่สร้างขึ้นอย่างรอบคอบของเราจะไม่ถูกเขียนทับโดยบังเอิญเมื่อมีคนมากรอกข้อมูลลงในใบแจ้งหนี้
4. เราจะบันทึกไฟล์เป็นเทมเพลตเพื่อให้ทุกคนใน บริษัท ของเราสามารถใช้ซ้ำได้

หากเรารู้สึกฉลาดจริงๆเราจะสร้างฐานข้อมูลของลูกค้าทั้งหมดในแผ่นงานอื่นจากนั้นใช้รหัสลูกค้าที่ป้อนในเซลล์ F5 เพื่อกรอกชื่อและที่อยู่ของลูกค้าในเซลล์ B6, B7 และ B8 โดยอัตโนมัติ

หากคุณต้องการฝึกฝนกับ VLOOKUP หรือเพียงแค่ดูเทมเพลตใบแจ้งหนี้ที่เป็นผลลัพธ์ของเราสามารถดาวน์โหลดได้จากที่นี่