VLOOKUP ใน Excel, ตอนที่ 2 การใช้ VLOOKUP โดยไม่มีฐานข้อมูล

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

หากคุณยังไม่ได้ดำเนินการโปรดอ่านบทความ VLOOKUP แรก - บทความนี้จะถือว่าแนวคิดหลายประการที่อธิบายในบทความนั้นเป็นที่รู้จักของผู้อ่านแล้ว.

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

เราจะแสดงบทความนี้พร้อมตัวอย่างจริง - การคำนวณค่าคอมมิชชั่นที่สร้างขึ้นในชุดตัวเลขยอดขาย เราจะเริ่มต้นด้วยสถานการณ์ที่ง่ายมากและจากนั้นทำให้มันมีความซับซ้อนมากขึ้นเรื่อย ๆ จนกระทั่งทางออกเดียวของปัญหาคือการใช้ VLOOKUP สถานการณ์เริ่มต้นใน บริษัท ที่เป็นตำนานของเราทำงานเช่นนี้: หากพนักงานขายสร้างยอดขายมากกว่า $ 30,000 ในปีที่กำหนดค่าคอมมิชชั่นที่พวกเขาได้รับจากการขายเหล่านั้นคือ 30% มิฉะนั้นค่าคอมมิชชั่นของพวกเขาเพียง 20% จนถึงตอนนี้มันเป็นแผ่นงานที่เรียบง่าย:

ในการใช้แผ่นงานนี้พนักงานขายจะป้อนตัวเลขยอดขายในเซลล์ B1 และสูตรในเซลล์ B2 คำนวณอัตราค่าคอมมิชชั่นที่ถูกต้องที่พวกเขามีสิทธิ์รับซึ่งใช้ในเซลล์ B3 เพื่อคำนวณค่าคอมมิชชั่นรวมที่พนักงานขายเป็นหนี้ (ซึ่ง เป็นการคูณแบบง่ายของ B1 และ B2).

เซลล์ B2 มีเพียงส่วนที่น่าสนใจของแผ่นงานนี้ - สูตรสำหรับการตัดสินใจว่าจะใช้อัตราค่านายหน้าใด: ส่วนหนึ่ง ด้านล่าง เกณฑ์ของ $ 30,000 หรือหนึ่ง ข้างบน เกณฑ์ สูตรนี้ใช้ฟังก์ชัน Excel ที่เรียกว่า ถ้า. สำหรับผู้อ่านที่ไม่คุ้นเคยกับ IF จะทำงานดังนี้:

ถ้า(เงื่อนไขค่าถ้าเป็นจริงค่าถ้าเป็นเท็จ)

ที่ไหน เงื่อนไข เป็นนิพจน์ที่ประเมินค่าอย่างใดอย่างหนึ่ง จริง หรือ เท็จ. ในตัวอย่างด้านบน เงื่อนไข คือการแสดงออก B1, ซึ่งสามารถอ่านได้ในชื่อ“ B1 น้อยกว่า B5 หรือไม่” หรืออีกวิธีหนึ่งคือ“ ยอดขายทั้งหมดน้อยกว่าเกณฑ์” หากคำตอบสำหรับคำถามนี้คือ“ ใช่” (จริง) จากนั้นเราจะใช้ คุณค่าถ้าเป็นจริง พารามิเตอร์ของฟังก์ชั่นคือ B6 ในกรณีนี้ - อัตราค่านายหน้าหากยอดขายรวมเป็น ด้านล่าง เกณฑ์ หากคำตอบของคำถามคือ“ ไม่” (เท็จ) เราจะใช้ ค่าถ้าเป็นเท็จ พารามิเตอร์ของฟังก์ชั่นคือ B7 ในกรณีนี้ - อัตราค่านายหน้าหากยอดขายรวมเป็น ข้างบน เกณฑ์.

อย่างที่คุณเห็นการใช้ยอดขายรวม $ 20,000 ให้อัตราค่าคอมมิชชั่น 20% ในเซลล์ B2 หากเราป้อนค่า $ 40,000 เราจะได้รับอัตราคอมมิชชั่นที่แตกต่างกัน:

ดังนั้นสเปรดชีตของเราทำงาน.

มาทำให้มันซับซ้อนกว่ากัน ขอแนะนำเกณฑ์ที่สอง: หากพนักงานขายมีรายได้มากกว่า $ 40,000 อัตราค่าคอมมิชชั่นจะเพิ่มขึ้นเป็น 40%:

เข้าใจง่ายในโลกแห่งความเป็นจริง แต่ในเซลล์ B2 สูตรของเรามีความซับซ้อนมากขึ้น หากคุณดูสูตรอย่างใกล้ชิดคุณจะเห็นว่าพารามิเตอร์ที่สามของฟังก์ชัน IF ดั้งเดิม ( ค่าถ้าเป็นเท็จ) ตอนนี้เป็นฟังก์ชัน IF ทั้งหมดในสิทธิของตนเอง สิ่งนี้เรียกว่า ฟังก์ชั่นที่ซ้อนกัน (ฟังก์ชั่นภายในฟังก์ชั่น) มันใช้ได้อย่างสมบูรณ์ใน Excel (แม้ใช้งานได้!) แต่อ่านและเข้าใจได้ยากขึ้น.

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

อย่างไรก็ตามมันเลวร้ายลง! เมื่อเราตัดสินใจว่าถ้าพวกเขามีรายได้มากกว่า $ 50,000 พวกเขาจะได้รับค่าคอมมิชชั่น 50% และถ้าพวกเขาได้รับมากกว่า $ 60,000 พวกเขาจะได้รับค่าคอมมิชชั่น 60%?

ตอนนี้สูตรในเซลล์ B2 ในขณะที่ถูกต้องได้กลายเป็นอ่านไม่ได้จริง ไม่ควรมีใครเขียนสูตรที่ฟังก์ชั่นซ้อนกันสี่ระดับ! แน่นอนว่าต้องมีวิธีที่ง่ายกว่า?

มีอยู่อย่างแน่นอน VLOOKUP เพื่อช่วยเหลือ!

ลองออกแบบแผ่นงานใหม่สักหน่อย เราจะรักษาตัวเลขที่เหมือนกันทั้งหมด แต่จัดระเบียบในวิธีใหม่ให้มากขึ้น แบน วิธีการ:

ใช้เวลาสักครู่และยืนยันด้วยตัวคุณเองว่าสิ่งใหม่ ตารางอัตรา ทำงานเหมือนกับชุดของเกณฑ์ด้านบน.

แนวคิดสิ่งที่เรากำลังจะทำคือใช้ VLOOKUP เพื่อค้นหายอดขายรวมของพนักงานขาย (จาก B1) ในตารางอัตราและกลับมาให้เราในอัตราค่าคอมมิชชั่นที่สอดคล้องกัน โปรดทราบว่าพนักงานขายอาจสร้างยอดขายอย่างแน่นอน ไม่ หนึ่งในห้าค่าในตารางอัตรา ($ 0, $ 30,000, $ 40,000, $ 50,000 หรือ $ 60,000) พวกเขาอาจสร้างยอดขายได้ $ 34,988 เป็นเรื่องสำคัญที่จะต้องทราบว่ามีมูลค่า $ 34,988 ไม่ ปรากฏในตารางอัตรา ลองดูว่า VLOOKUP สามารถแก้ปัญหาของเราได้หรือไม่ ...

เราเลือกเซลล์ B2 (ตำแหน่งที่เราต้องการใส่สูตรของเรา) แล้วใส่ฟังก์ชัน VLOOKUP จาก สูตร แท็บ:

อาร์กิวเมนต์ของฟังก์ชัน กล่องสำหรับ VLOOKUP ปรากฏขึ้น เราเติมอาร์กิวเมนต์ (พารามิเตอร์) ทีละรายการโดยเริ่มจาก lookup_value, ซึ่งในกรณีนี้ยอดขายจากเซลล์ B1 เราวางเคอร์เซอร์ใน lookup_value ฟิลด์จากนั้นคลิกหนึ่งครั้งในเซลล์ B1:

ต่อไปเราต้องระบุให้ VLOOKUP ว่าจะค้นหาข้อมูลนี้ในตารางใดในตัวอย่างนี้เป็นตารางอัตราแน่นอน เราวางเคอร์เซอร์ใน table_array ฟิลด์จากนั้นไฮไลต์ตารางอัตราทั้งหมด - ไม่รวมส่วนหัว:

ต่อไปเราจะต้องระบุคอลัมน์ในตารางที่มีข้อมูลที่เราต้องการให้สูตรของเรากลับมาหาเรา ในกรณีนี้เราต้องการอัตราค่าคอมมิชชั่นซึ่งพบได้ในคอลัมน์ที่สองในตารางดังนั้นเราจึงป้อน 2 เข้าไปใน col_index_num สนาม:

ในที่สุดเราก็ใส่ค่าลงใน range_lookup สนาม.

สำคัญ: มันคือการใช้งานของเขตข้อมูลนี้ที่แตกต่างสองวิธีในการใช้ VLOOKUP ในการใช้ VLOOKUP กับฐานข้อมูลพารามิเตอร์สุดท้ายนี้, range_lookup, จะต้องตั้งค่าเป็น FALSE, แต่ด้วยการใช้ VLOOKUP อื่นเราต้องปล่อยให้มันว่างเปล่าหรือใส่ค่า TRUE. เมื่อใช้ VLOOKUP จำเป็นอย่างยิ่งที่คุณต้องเลือกตัวเลือกที่ถูกต้องสำหรับพารามิเตอร์สุดท้ายนี้.

เพื่อความชัดเจนเราจะป้อนค่าเป็น จริง ใน range_lookup สนาม มันจะดีถ้าปล่อยว่างไว้เนื่องจากนี่เป็นค่าเริ่มต้น:

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

หากเราทดสอบด้วยยอดขายที่แตกต่างกันไม่กี่อย่างเราก็สามารถสร้างความพึงพอใจให้กับตัวเองได้ว่าสูตรทำงาน.

ข้อสรุป

ในเวอร์ชัน "ฐานข้อมูล" ของ VLOOKUP โดยที่ range_lookup พารามิเตอร์คือ FALSE, ค่าที่ส่งผ่านในพารามิเตอร์แรก (lookup_value) ต้อง ปรากฏในฐานข้อมูล กล่าวอีกนัยหนึ่งเรากำลังมองหาคู่ที่ตรงกัน.

แต่ในการใช้ VLOOKUP อื่น ๆ เราไม่จำเป็นต้องมองหาคู่ที่ตรงกัน ในกรณีนี้“ ใกล้พอจะดีพอ” แต่เราหมายถึงอะไร“ ใกล้พอ” ลองใช้ตัวอย่าง: เมื่อค้นหาอัตราค่าคอมมิชชั่นจากยอดขายรวม $ 34,988 สูตร VLOOKUP ของเราจะส่งคืนค่า 30% ซึ่งเป็นคำตอบที่ถูกต้อง ทำไมถึงเลือกแถวในตารางที่มี 30% อันที่จริง“ ใกล้พอ” หมายถึงอะไรในกรณีนี้ ให้แม่นยำ:

เมื่อ range_lookup ถูกตั้งค่าเป็น TRUE (หรือตัดออก) VLOOKUP จะดูในคอลัมน์ 1 และจับคู่ ค่าสูงสุดที่ไม่มากกว่า lookup_value พารามิเตอร์.

สิ่งสำคัญคือต้องทราบว่าระบบนี้ใช้งานได้, ตารางจะต้องเรียงลำดับจากน้อยไปมากในคอลัมน์ 1!

หากคุณต้องการฝึกกับ VLOOKUP ไฟล์ตัวอย่างที่แสดงในบทความนี้สามารถดาวน์โหลดได้จากที่นี่.