ความลับทั้งหมดของฟังก์ชัน VLOOKUP ของ Excel สำหรับการค้นหาข้อมูลในตารางและแยกข้อมูลไปยังอีกตารางหนึ่ง

2024 ผู้เขียน: Malcolm Clapton | [email protected]. แก้ไขล่าสุด: 2023-12-17 04:12

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

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

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

VLOOKUP เป็นตัวย่อของ วี แนวตั้ง NS การตรวจสอบ. ในทำนองเดียวกัน VLOOKUP - การค้นหาแนวตั้ง ชื่อของฟังก์ชันบ่งบอกว่าเราค้นหาในแถวของตาราง (ในแนวตั้ง - วนซ้ำแถวและแก้ไขคอลัมน์) และไม่ใช่ในคอลัมน์ (ในแนวนอน - วนซ้ำคอลัมน์และแก้ไขแถว) ควรสังเกตว่า VLOOKUP มีน้องสาว - ลูกเป็ดขี้เหร่ที่จะไม่มีวันกลายเป็นหงส์ - นี่คือฟังก์ชัน HLOOKUP HLOOKUP ตรงกันข้ามกับ VLOOKUP จะทำการค้นหาในแนวนอน แต่แนวคิดของ Excel (และจริงๆ แล้วคือแนวคิดของการจัดระเบียบข้อมูล) บอกเป็นนัยว่าตารางของคุณมีคอลัมน์น้อยกว่าและมีแถวมากกว่า นั่นคือเหตุผลที่เราต้องค้นหาตามแถวบ่อยกว่าคอลัมน์หลายเท่า ถ้าคุณใช้ฟังก์ชัน HLO ใน Excel บ่อยเกินไป เป็นไปได้ว่าคุณไม่เข้าใจบางอย่างในชีวิตนี้

ไวยากรณ์

ฟังก์ชัน VLOOKUP มีสี่พารามิเตอร์:

= VLOOKUP (;; [;]), ที่นี่:

- ค่าที่ต้องการ (ไม่ค่อย) หรือการอ้างอิงไปยังเซลล์ที่มีค่าที่ต้องการ (กรณีส่วนใหญ่)

- อ้างอิงถึงช่วงของเซลล์ (อาร์เรย์สองมิติ) ในคอลัมน์ FIRST (!) ที่จะค้นหาค่าพารามิเตอร์

- หมายเลขคอลัมน์ในช่วงที่จะคืนค่า;

- นี่เป็นพารามิเตอร์ที่สำคัญมากที่จะตอบคำถามว่าคอลัมน์แรกของช่วงมีการเรียงลำดับจากน้อยไปมากหรือไม่ หากอาร์เรย์ถูกจัดเรียง เราจะระบุค่า TRUE หรือ 1 มิฉะนั้น - FALSE หรือ 0 หากละเว้นพารามิเตอร์นี้ ค่าดีฟอลต์คือ 1

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

ตอนนี้คุณต้องเครียดและอ่านย่อหน้าถัดไปหลาย ๆ ครั้งจนกว่าคุณจะรู้สึกถึงความหมายของสิ่งที่พูดไปจนจบ ทุกคำมีความสำคัญที่นั่น ตัวอย่างจะช่วยให้คุณคิดออก

สูตร VLOOKUP ทำงานอย่างไร?

• สูตรประเภท I. หากละเว้นหรือระบุพารามิเตอร์สุดท้ายเท่ากับ 1 ดังนั้น VLOOKUP จะถือว่าคอลัมน์แรกถูกเรียงลำดับจากน้อยไปมาก ดังนั้นการค้นหาจะหยุดที่แถวที่ นำหน้าบรรทัดที่มีค่ามากกว่าค่าที่ต้องการทันที … หากไม่พบสตริงดังกล่าว แถวสุดท้ายของช่วงจะถูกส่งคืน

  

• สูตร II. หากระบุพารามิเตอร์สุดท้ายเป็น 0 VLOOKUP จะสแกนคอลัมน์แรกของอาร์เรย์ตามลำดับและหยุดการค้นหาทันทีเมื่อพบการจับคู่ที่ตรงกันทุกประการกับพารามิเตอร์แรก มิฉะนั้นรหัสข้อผิดพลาด # N / A (# N / A) จะถูกส่งกลับ

  

เวิร์กโฟลว์สูตร

VPR ประเภท I

VPR ประเภท II

ข้อพิสูจน์สำหรับสูตรของแบบฟอร์ม I

1. สามารถใช้สูตรเพื่อกระจายค่าต่างๆ ข้ามช่วงได้
2. หากคอลัมน์แรกมีค่าที่ซ้ำกันและจัดเรียงอย่างถูกต้อง แถวสุดท้ายที่มีค่าที่ซ้ำกันจะถูกส่งกลับ
3. หากคุณค้นหาค่าที่มากกว่าคอลัมน์แรกอย่างเห็นได้ชัด คุณจะสามารถค้นหาแถวสุดท้ายของตารางได้อย่างง่ายดาย ซึ่งมีค่ามากทีเดียว
4. มุมมองนี้จะส่งคืนข้อผิดพลาด # N / A เฉพาะเมื่อไม่พบค่าที่น้อยกว่าหรือเท่ากับค่าที่ต้องการ
5. เป็นการยากที่จะเข้าใจว่าสูตรส่งกลับค่าที่ไม่ถูกต้องหากอาร์เรย์ของคุณไม่ได้จัดเรียง

ข้อพิสูจน์สำหรับสูตรประเภท II

หากค่าที่ต้องการเกิดขึ้นหลายครั้งในคอลัมน์แรกของอาร์เรย์ สูตรจะเลือกแถวแรกสำหรับการดึงข้อมูลในภายหลัง

ประสิทธิภาพของ VLOOKUP

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

• ฉันต้องการคอมพิวเตอร์ที่ทรงพลังกว่านี้
• ฉันต้องการสูตรที่เร็วกว่า เช่น หลายคนรู้จัก INDEX + MATCH ซึ่งน่าจะเร็วกว่าประมาณ 5-10%

และมีเพียงไม่กี่คนที่คิดว่าทันทีที่คุณเริ่มใช้ VLOOKUP ประเภท I และตรวจดูให้แน่ใจว่าคอลัมน์แรกถูกจัดเรียงด้วยวิธีการใดๆ ความเร็วของ VLOOKUP จะเพิ่มขึ้น 57 เท่า ฉันกำลังเขียนด้วยคำพูด - ห้าสิบเจ็ดครั้ง! ไม่ใช่ 57% แต่เป็น 5,700% ฉันตรวจสอบข้อเท็จจริงนี้ค่อนข้างน่าเชื่อถือ

ความลับของการทำงานที่รวดเร็วนั้นอยู่ที่ข้อเท็จจริงที่ว่าอัลกอริธึมการค้นหาที่มีประสิทธิภาพสูงสุดสามารถนำไปใช้กับอาร์เรย์ที่จัดเรียงได้ ซึ่งเรียกว่าการค้นหาแบบไบนารี (วิธีแบ่งครึ่ง วิธีแบ่งขั้ว) ดังนั้น VLOOKUP ของประเภทที่ 1 จะใช้มัน และ VLOOKUP ของประเภท II จะค้นหาโดยไม่มีการปรับให้เหมาะสมเลย เช่นเดียวกับฟังก์ชัน MATCH ซึ่งมีพารามิเตอร์ที่คล้ายกัน และฟังก์ชัน LOOKUP ซึ่งใช้งานได้กับอาร์เรย์ที่เรียงลำดับเท่านั้น และรวมอยู่ใน Excel เพื่อให้เข้ากันได้กับ Lotus 1-2-3

ข้อเสียของสูตร

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

บางแง่มุมของการนำสูตรไปใช้ในชีวิตจริง

ค้นหาช่วง

ภาพประกอบคลาสสิกของการค้นหาตามช่วงคืองานในการกำหนดส่วนลดตามขนาดคำสั่งซื้อ

ค้นหาสตริงข้อความ

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

พื้นที่ต่อสู้

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

รูปแบบข้อมูลต่างๆ

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

= VLOOKUP (−− D7; Products! $ A $ 2: $ C $ 5; 3; 0) - ถ้า D7 มีข้อความและตารางมีตัวเลข

= VLOOKUP (D7 & ""); สินค้า! $ A $ 2: $ C $ 5; 3; 0) - และในทางกลับกัน

อีกอย่าง คุณสามารถแปลข้อความเป็นตัวเลขได้หลายวิธีพร้อมกัน โดยเลือก:

• การปฏิเสธสองครั้ง -D7
• คูณด้วยหนึ่ง D7 * 1
• ศูนย์เพิ่ม D7 + 0
• ยกกำลังแรก D7 ^ 1

การแปลงตัวเลขเป็นข้อความทำได้โดยการต่อกับสตริงว่าง ซึ่งบังคับให้ Excel แปลงประเภทข้อมูล

วิธีระงับ # N / A

ซึ่งสะดวกมากในการใช้ฟังก์ชัน IFERROR

ตัวอย่างเช่น: = IFERROR (VLOOKUP (D7; Products! $ A $ 2: $ C $ 5; 3; 0); "")

หาก VLOOKUP ส่งคืนรหัสข้อผิดพลาด # N / A ดังนั้น IFERROR จะสกัดกั้นและแทนที่พารามิเตอร์ 2 (ในกรณีนี้คือสตริงว่าง) และหากไม่มีข้อผิดพลาดเกิดขึ้น ฟังก์ชันนี้จะแสร้งทำเป็นว่าไม่มีอยู่จริง แต่ มีเพียง VLOOKUP ที่ส่งคืนผลลัพธ์ปกติ

Array

บ่อยครั้งที่พวกเขาลืมที่จะอ้างอิงอาร์เรย์แบบสัมบูรณ์และเมื่อขยายอาร์เรย์ "floats" อย่าลืมใช้ $ A $ 2: $ C $ 5 แทน A2: C5

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

ความคิดที่ดียิ่งขึ้นคือการประกาศอาร์เรย์นี้เป็นช่วงที่มีชื่อ

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

เกือบจะเป็นอัจฉริยะแล้ว - เพื่อจัดเรียงอาร์เรย์ในรูปแบบ

การใช้ฟังก์ชัน COLUMN เพื่อระบุคอลัมน์ที่จะแยก

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

การสร้างคีย์ผสมด้วย & "|" &

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

นี่เป็นบทความแรกของฉันสำหรับ Lifehacker หากคุณชอบฉันขอเชิญคุณเยี่ยมชมและยินดีที่จะอ่านความคิดเห็นเกี่ยวกับเคล็ดลับในการใช้ฟังก์ชัน VLOOKUP และสิ่งที่คล้ายกัน ขอบคุณ.:)