ใครขี้เกียจหรือไม่มีเวลาอ่าน - ดูวิดีโอ รายละเอียดและความแตกต่างอยู่ในข้อความด้านล่าง
การกำหนดปัญหา
ดังนั้นเราจึงมีสองโต๊ะ - ตารางการสั่งซื้อ и รายการราคา:
งานคือการแทนที่ราคาจากรายการราคาลงในตารางคำสั่งซื้อโดยอัตโนมัติ โดยเน้นที่ชื่อของผลิตภัณฑ์เพื่อให้คุณสามารถคำนวณต้นทุนได้ในภายหลัง
Solution
ในชุดฟังก์ชัน Excel ภายใต้หมวดหมู่ การอ้างอิงและอาร์เรย์ (ค้นหาและอ้างอิง) มีฟังก์ชั่น VPR (วีลุคอัพ).ฟังก์ชันนี้จะค้นหาค่าที่กำหนด (ในตัวอย่างของเราคือคำว่า "Apples") ในคอลัมน์ซ้ายสุดของตารางที่ระบุ (รายการราคา) ที่ย้ายจากบนลงล่างและเมื่อพบแล้ว จะแสดงเนื้อหาของเซลล์ที่อยู่ติดกัน (23 รูเบิล) .แผนผังการทำงานของฟังก์ชันนี้สามารถแสดงได้ดังนี้:
เพื่อความสะดวกในการใช้งานฟังก์ชันต่อไป ให้ทำสิ่งหนึ่งพร้อมกัน – กำหนดช่วงของเซลล์ในรายการราคาเป็นชื่อของคุณเอง ในการดำเนินการนี้ ให้เลือกเซลล์ทั้งหมดของรายการราคายกเว้น "ส่วนหัว" (G3: H19) เลือกจากเมนู แทรก – ชื่อ – มอบหมาย (แทรก — ชื่อ — กำหนด) หรือกด CTRL + F3 แล้วใส่ชื่ออะไรก็ได้ (ไม่เว้นวรรค) เช่น ราคา… ตอนนี้ ในอนาคต คุณสามารถใช้ชื่อนี้เพื่อเชื่อมโยงไปยังรายการราคา
ตอนนี้เราใช้ฟังก์ชัน VPR… เลือกเซลล์ที่จะป้อน (D3) และเปิดแท็บ สูตร – การแทรกฟังก์ชัน (สูตร — ฟังก์ชันแทรก)… ในหมวด การอ้างอิงและอาร์เรย์ (ค้นหาและอ้างอิง) หาฟังก์ชัน VPR (วีลุคอัพ) และกด OK… หน้าต่างสำหรับป้อนอาร์กิวเมนต์สำหรับฟังก์ชันจะปรากฏขึ้น:
เรากรอกในทางกลับกัน:
- ค่าที่ต้องการ (ค่าการค้นหา) – ชื่อของผลิตภัณฑ์ที่ฟังก์ชันควรพบในคอลัมน์ซ้ายสุดของรายการราคา ในกรณีของเรา คำว่า "Apples" จากเซลล์ B3
- ตาราง (อาร์เรย์ตาราง) – ตารางที่ค่าที่ต้องการ uXNUMXbuXNUMX ถ่ายออกมานั่นคือรายการราคาของเรา สำหรับการอ้างอิง เราใช้ชื่อของเราเองว่า “ราคา” ที่ให้ไว้ก่อนหน้านี้ ถ้าไม่ระบุชื่อก็เลือกตารางได้ แต่ อย่าลืมกดปุ่ม F4เพื่อปักหมุดลิงค์ด้วยเครื่องหมายดอลลาร์เพราะไม่เช่นนั้น สูตรจะเลื่อนลงมาเมื่อคัดลอกสูตรของเราลงไปที่เซลล์ที่เหลือในคอลัมน์ D3:D30
- คอลัมน์_หมายเลข (หมายเลขดัชนีคอลัมน์) – หมายเลขซีเรียล (ไม่ใช่ตัวอักษร!) ของคอลัมน์ในรายการราคาที่เราจะใช้ค่าราคา คอลัมน์แรกของรายการราคาที่มีชื่อคือหมายเลข 1 ดังนั้นเราจึงต้องการราคาจากคอลัมน์หมายเลข 2
- ช่วง_lookup (การค้นหาช่วง) – สามารถป้อนได้เพียงสองค่าในฟิลด์นี้: FALSE หรือ TRUE:
- หากป้อนค่า 0 or โกหก (เท็จ)อันที่จริงแล้วนี่หมายความว่าอนุญาตเฉพาะการค้นหาเท่านั้น คู่ที่เหมาะสมกล่าวคือ หากฟังก์ชันไม่พบรายการที่ไม่เป็นไปตามมาตรฐานที่ระบุไว้ในตารางคำสั่งซื้อในรายการราคา (เช่น หากป้อน "มะพร้าว") จะทำให้เกิดข้อผิดพลาด #N/A (ไม่มีข้อมูล)
- หากป้อนค่า 1 or TRUE (จริง)หมายความว่าคุณอนุญาตให้ค้นหาไม่ตรงทั้งหมด แต่ การแข่งขันโดยประมาณกล่าวคือ ในกรณีของ “มะพร้าว” ฟังก์ชันจะพยายามค้นหาผลิตภัณฑ์ที่มีชื่อใกล้เคียงกับ “มะพร้าว” มากที่สุดและส่งคืนราคาสำหรับชื่อนี้ ในกรณีส่วนใหญ่ การแทนที่โดยประมาณดังกล่าวสามารถเล่นกลกับผู้ใช้ได้โดยการแทนที่มูลค่าของผลิตภัณฑ์ที่ไม่ถูกต้องซึ่งอยู่ตรงนั้นจริงๆ! ดังนั้นสำหรับปัญหาทางธุรกิจที่แท้จริงส่วนใหญ่ การค้นหาโดยประมาณจึงไม่ควรอนุญาต ข้อยกเว้นคือเมื่อเรากำลังมองหาตัวเลขและไม่ใช่ข้อความ – ตัวอย่างเช่น เมื่อคำนวณส่วนลดขั้นตอน
ทุกอย่าง! มันยังคงกด OK และคัดลอกฟังก์ชันที่ป้อนไปยังทั้งคอลัมน์
# N / A ข้อผิดพลาดและการปราบปราม
ฟังก์ชัน VPR (วีลุคอัพ) ส่งกลับข้อผิดพลาด #N/A (#ไม่มี) ถ้า:
- เปิดใช้งานการค้นหาที่แน่นอน (argument มุมมองช่วงเวลา = 0) และชื่อที่ต้องการไม่อยู่ใน ตาราง.
- รวมการค้นหาแบบหยาบ (มุมมองช่วงเวลา = 1) แต่ ตารางซึ่งการค้นหากำลังดำเนินการไม่ได้เรียงลำดับจากน้อยไปหามาก
- รูปแบบของเซลล์ที่ค่าที่ต้องการของชื่อมาจาก (เช่น B3 ในกรณีของเรา) และรูปแบบของเซลล์ของคอลัมน์แรก (F3: F19) ของตารางจะแตกต่างกัน (เช่น ตัวเลขและข้อความ ). กรณีนี้เป็นเรื่องปกติโดยเฉพาะอย่างยิ่งเมื่อใช้รหัสตัวเลข (หมายเลขบัญชี ตัวระบุ วันที่ ฯลฯ) แทนชื่อข้อความ ในกรณีนี้ คุณสามารถใช้ฟังก์ชัน Ч и TEXT เพื่อแปลงรูปแบบข้อมูล มันจะมีลักษณะดังนี้:
=VLOOKUP(TEXT(B3),ราคา,0)
คุณสามารถอ่านเพิ่มเติมเกี่ยวกับเรื่องนี้ได้ที่นี่
- ฟังก์ชันไม่พบค่าที่ต้องการเนื่องจากโค้ดมีช่องว่างหรืออักขระที่ไม่สามารถพิมพ์ได้ซึ่งมองไม่เห็น (ตัวแบ่งบรรทัด ฯลฯ) ในกรณีนี้ คุณสามารถใช้ฟังก์ชันข้อความ TRIM (ทริม) и พิมพ์(ทำความสะอาด) เพื่อลบออก:
=VLOOKUP(TRIMSPACES(สะอาด(B3)),ราคา,0)
=VLOOKUP(TRIM(CLEAN(B3));ราคา;0)
เพื่อระงับข้อความแสดงข้อผิดพลาด # N / A (#ไม่มี) ในกรณีที่ฟังก์ชันไม่พบการจับคู่แบบตรงทั้งหมด คุณสามารถใช้ฟังก์ชัน การอ้างอิง (ไอเฟอร์เรอร์)… ตัวอย่างเช่น โครงสร้างนี้ดักจับข้อผิดพลาดที่สร้างโดย VLOOKUP และแทนที่ด้วยศูนย์:
= IFERROR (VLOOKUP (B3, ราคา, 2, 0), 0)
= IFERROR (VLOOKUP (B3; ราคา; 2; 0); 0)
PS
หากคุณต้องการแยกค่าไม่ใช่หนึ่งค่า แต่ให้รวมทั้งชุดในคราวเดียว (หากมีหลายค่าที่แตกต่างกัน) คุณจะต้องเปลี่ยนสูตรด้วยสูตรอาร์เรย์ หรือใช้ฟีเจอร์ XLOOKUP ใหม่จาก Office 365
- เวอร์ชันปรับปรุงของฟังก์ชัน VLOOKUP (VLOOKUP 2)
- การคำนวณส่วนลดขั้นตอน (ช่วง) อย่างรวดเร็วโดยใช้ฟังก์ชัน VLOOKUP
- วิธีสร้าง “ซ้าย VLOOKUP” โดยใช้ฟังก์ชัน INDEX และ MATCH
- วิธีใช้ฟังก์ชัน VLOOKUP เพื่อกรอกแบบฟอร์มด้วยข้อมูลจากรายการ
- วิธีดึงออกไม่ใช่ครั้งแรก แต่ค่าทั้งหมดจากตารางในครั้งเดียว
- ฟังก์ชัน VLOOKUP2 และ VLOOKUP3 จาก PLEX add-on