เนื้อหา
เมื่อเร็ว ๆ นี้เราได้ทุ่มเทบทความให้กับหนึ่งในฟังก์ชัน Excel ที่มีประโยชน์ที่สุดที่เรียกว่า VPR และแสดงให้เห็นว่าสามารถใช้ดึงข้อมูลที่ต้องการจากฐานข้อมูลลงในเซลล์เวิร์กชีตได้อย่างไร เรายังกล่าวอีกว่ามีสองกรณีการใช้งานสำหรับฟังก์ชัน VPR และมีเพียงหนึ่งในนั้นเท่านั้นที่เกี่ยวข้องกับการสืบค้นฐานข้อมูล ในบทความนี้ คุณจะได้เรียนรู้วิธีใช้งานฟังก์ชันอื่นที่ไม่ค่อยมีใครรู้จัก VPR ใน Excel
หากคุณยังไม่ได้ดำเนินการ โปรดอ่านบทความล่าสุดเกี่ยวกับฟังก์ชัน VPRเนื่องจากข้อมูลทั้งหมดด้านล่างนี้ถือว่าคุณคุ้นเคยกับหลักการที่อธิบายไว้ในบทความแรกแล้ว
เมื่อทำงานกับฐานข้อมูล ฟังก์ชัน VPR ตัวระบุเฉพาะจะถูกส่งต่อ ซึ่งใช้เพื่อระบุข้อมูลที่เราต้องการค้นหา (เช่น รหัสผลิตภัณฑ์หรือหมายเลขประจำตัวลูกค้า) รหัสเฉพาะนี้ต้องมีอยู่ในฐานข้อมูล มิฉะนั้น VPR จะรายงานข้อผิดพลาด ในบทความนี้เราจะมาดูวิธีการใช้ฟังก์ชันนี้กัน VPRเมื่อไม่มี id ในฐานข้อมูลเลย ราวกับว่าฟังก์ชั่น VPR เปลี่ยนไปใช้โหมดโดยประมาณ และเลือกข้อมูลที่จะให้เราทราบเมื่อต้องการค้นหาบางอย่าง ในบางสถานการณ์ นี่คือสิ่งที่จำเป็นจริงๆ
ตัวอย่างจากชีวิต เราตั้งค่าภารกิจ
มาอธิบายบทความนี้ด้วยตัวอย่างในชีวิตจริง – การคำนวณค่าคอมมิชชันตามเมตริกการขายที่หลากหลาย เราจะเริ่มต้นด้วยตัวเลือกที่ง่ายมาก จากนั้นเราจะค่อยๆ ทำให้มันซับซ้อน จนกว่าวิธีแก้ปัญหาที่มีเหตุผลเพียงอย่างเดียวของปัญหาคือการใช้ฟังก์ชัน VPR. สถานการณ์เริ่มต้นสำหรับงานที่สมมติขึ้นของเรามีดังนี้: หากพนักงานขายทำยอดขายได้มากกว่า $30000 ในหนึ่งปี ค่าคอมมิชชันของเขาคือ 30% มิฉะนั้น ค่าคอมมิชชั่นเพียง 20% มาวางในรูปแบบของตาราง:
ผู้ขายป้อนข้อมูลการขายในเซลล์ B1 และสูตรในเซลล์ B2 จะกำหนดอัตราค่าคอมมิชชันที่ถูกต้องซึ่งผู้ขายสามารถคาดหวังได้ ในทางกลับกัน อัตราผลลัพธ์จะใช้ในเซลล์ B3 เพื่อคำนวณค่าคอมมิชชันทั้งหมดที่ผู้ขายควรได้รับ (เพียงคูณเซลล์ B1 และ B2)
ส่วนที่น่าสนใจที่สุดของตารางอยู่ในเซลล์ B2 ซึ่งเป็นสูตรสำหรับกำหนดอัตราค่าคอมมิชชัน สูตรนี้มีฟังก์ชัน Excel ที่เรียกว่า IF (ถ้า). สำหรับผู้อ่านที่ไม่คุ้นเคยกับฟังก์ชันนี้ ฉันจะอธิบายวิธีการทำงาน:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
เงื่อนไข เป็นอาร์กิวเมนต์ของฟังก์ชันที่รับค่าของทั้ง ทรูโค้ด (TRUE) หรือ FALSE (เท็จ). ในตัวอย่างข้างต้น นิพจน์ B1
B1 น้อยกว่า B5 จริงหรือไม่?
หรือคุณสามารถพูดอย่างอื่น:
ยอดรวมของยอดขายทั้งปีน้อยกว่ามูลค่าเกณฑ์จริงหรือ?
ถ้าเราตอบคำถามนี้ YES (TRUE) จากนั้นฟังก์ชันจะส่งกลับ ค่าถ้าเป็นจริง (ค่าถ้าเป็น TRUE) ในกรณีของเรา นี่จะเป็นค่าของเซลล์ B6 กล่าวคือ อัตราค่าคอมมิชชันเมื่อยอดขายรวมต่ำกว่าเกณฑ์ ถ้าเราตอบคำถาม NO (FALSE) แล้วส่งคืน ค่าถ้าเป็นเท็จ (ค่าถ้า FALSE) ในกรณีของเรา นี่คือค่าของเซลล์ B7 กล่าวคือ อัตราค่าคอมมิชชันเมื่อยอดขายรวมสูงกว่าเกณฑ์
อย่างที่คุณเห็น ถ้าเรานำยอดขายรวม $20000 เราจะได้รับอัตราค่าคอมมิชชัน 2% ในเซลล์ B20 หากเราป้อนมูลค่า $40000 อัตราค่าคอมมิชชันจะเปลี่ยนไป 30%:
นี่คือการทำงานของตารางของเรา
เราทำให้งานซับซ้อน
มาทำให้เรื่องยากขึ้นหน่อย มาตั้งค่าเกณฑ์อื่นกัน: หากผู้ขายมีรายได้มากกว่า $40000 อัตราค่าคอมมิชชันจะเพิ่มขึ้นเป็น 40%:
ทุกอย่างดูเหมือนจะเรียบง่ายและชัดเจน แต่สูตรของเราในเซลล์ B2 นั้นซับซ้อนกว่าอย่างเห็นได้ชัด หากคุณดูสูตรอย่างใกล้ชิด คุณจะเห็นว่าอาร์กิวเมนต์ที่สามของฟังก์ชัน IF (IF) กลายเป็นอีกหนึ่งฟังก์ชั่นที่เต็มเปี่ยม IF (ถ้า). โครงสร้างนี้เรียกว่าการซ้อนหน้าที่ซึ่งกันและกัน Excel ยอมให้โครงสร้างเหล่านี้มีความสุขและใช้งานได้ แต่อ่านและเข้าใจยากกว่ามาก
เราจะไม่เจาะลึกรายละเอียดทางเทคนิค – สาเหตุและวิธีการทำงาน และเราจะไม่ลงลึกถึงความแตกต่างของการเขียนฟังก์ชันที่ซ้อนกัน ท้ายที่สุดนี่คือบทความที่อุทิศให้กับฟังก์ชั่น VPRไม่ใช่คำแนะนำที่สมบูรณ์สำหรับ Excel
ไม่ว่ากรณีใด สูตรจะซับซ้อนยิ่งขึ้น! จะเกิดอะไรขึ้นหากเราแนะนำตัวเลือกอื่นสำหรับอัตราค่าคอมมิชชัน 50% สำหรับผู้ขายที่ทำยอดขายได้มากกว่า $50000 และถ้ามีคนขายมากกว่า $60000 พวกเขาจะจ่ายค่าคอมมิชชั่น 60% หรือไม่?
ตอนนี้สูตรในเซลล์ B2 แม้ว่าจะเขียนโดยไม่มีข้อผิดพลาด แต่ก็ไม่สามารถอ่านได้อย่างสมบูรณ์ ฉันคิดว่ามีไม่กี่คนที่ต้องการใช้สูตรที่มีการซ้อน 4 ระดับในโครงการของพวกเขา จะต้องมีวิธีที่ง่ายกว่านี้!
และมีวิธีดังกล่าว! ฟังก์ชั่นจะช่วยเรา VPR.
เราใช้ฟังก์ชัน VLOOKUP เพื่อแก้ปัญหา
มาเปลี่ยนการออกแบบโต๊ะกันสักหน่อย เราจะเก็บฟิลด์และข้อมูลเดียวกันทั้งหมด แต่จัดเรียงใหม่ในรูปแบบที่กะทัดรัดยิ่งขึ้น:
ใช้เวลาสักครู่และตรวจสอบตารางใหม่ ตารางอัตรา รวมข้อมูลเดียวกันกับตารางเกณฑ์ก่อนหน้า
แนวคิดหลักคือการใช้ฟังก์ชัน VPR เพื่อกำหนดอัตราภาษีที่ต้องการตามตาราง ตารางอัตรา ขึ้นอยู่กับปริมาณการขาย โปรดทราบว่าผู้ขายสามารถขายสินค้าในจำนวนที่ไม่เท่ากับหนึ่งในห้าเกณฑ์ในตาราง ตัวอย่างเช่น เขาสามารถขายได้ในราคา $34988 แต่ไม่มีจำนวนดังกล่าว มาดูวิธีการทำงาน VPR สามารถจัดการกับสถานการณ์ดังกล่าวได้
การแทรกฟังก์ชัน VLOOKUP
เลือกเซลล์ B2 (ตำแหน่งที่เราต้องการแทรกสูตรของเรา) และค้นหา วีลุคอัพ (VLOOKUP) ในไลบรารีฟังก์ชันของ Excel: สูตร (สูตร) > ไลบรารีฟังก์ชัน (ไลบรารีฟังก์ชัน) > ค้นหาและอ้างอิง (อ้างอิงและอาร์เรย์).
กล่องโต้ตอบปรากฏขึ้น อาร์กิวเมนต์ของฟังก์ชัน (อาร์กิวเมนต์ของฟังก์ชัน) เรากรอกค่าของอาร์กิวเมนต์ทีละตัวโดยเริ่มจาก lookup_value (lookup_value). ในตัวอย่างนี้ นี่คือยอดขายทั้งหมดจากเซลล์ B1 วางเคอร์เซอร์ในฟิลด์ lookup_value (lookup_value) และเลือกเซลล์ B1
ถัดไป คุณต้องระบุฟังก์ชัน VPRจะหาข้อมูลได้ที่ไหน ในตัวอย่างของเรา นี่คือตาราง ตารางอัตรา. วางเคอร์เซอร์ในฟิลด์ Table_array (ตาราง) แล้วเลือกทั้งโต๊ะ ตารางอัตรายกเว้นส่วนหัว
ต่อไป เราต้องระบุคอลัมน์ที่จะดึงข้อมูลโดยใช้สูตรของเรา เราสนใจอัตราค่าคอมมิชชั่นซึ่งอยู่ในคอลัมน์ที่สองของตาราง ดังนั้น สำหรับการโต้แย้ง Col_index_num (Column_number) ป้อนค่า 2
และสุดท้าย เราขอแนะนำอาร์กิวเมนต์สุดท้าย – ช่วง_lookup (Interval_lookup)
สำคัญ: มันคือการใช้อาร์กิวเมนต์นี้ที่สร้างความแตกต่างระหว่างสองวิธีในการใช้ฟังก์ชัน VPR. เมื่อทำงานกับฐานข้อมูล อาร์กิวเมนต์ ช่วง_lookup (range_lookup) ต้องมีค่าเสมอ FALSE (FALSE) เพื่อค้นหาการจับคู่แบบตรงทั้งหมด ในการใช้งานฟังก์ชั่นของเรา VPRเราต้องเว้นฟิลด์นี้ว่างไว้ หรือป้อนค่า ทรูโค้ด (จริง). การเลือกตัวเลือกนี้อย่างถูกต้องเป็นสิ่งสำคัญอย่างยิ่ง
เพื่อให้ชัดเจนยิ่งขึ้นเราจะมาแนะนำ ทรูโค้ด (TRUE) ภาคสนาม ช่วง_lookup (Interval_lookup). แม้ว่าหากคุณเว้นฟิลด์ว่างไว้ สิ่งนี้ก็จะไม่เกิดข้อผิดพลาด เนื่องจาก ทรูโค้ด เป็นค่าเริ่มต้น:
เราได้กรอกพารามิเตอร์ทั้งหมดแล้ว ตอนนี้เรากด OKและ Excel สร้างสูตรให้เราด้วยฟังก์ชัน VPR.
หากเราทดสอบด้วยมูลค่าที่แตกต่างกันหลายค่าสำหรับยอดขายรวม เราจะตรวจสอบให้แน่ใจว่าสูตรทำงานอย่างถูกต้อง
สรุป
เมื่อฟังก์ชั่น VPR ทำงานร่วมกับฐานข้อมูล, อาร์กิวเมนต์ ช่วง_lookup (range_lookup) ต้องยอมรับ FALSE (เท็จ). และค่าที่ป้อนเป็น lookup_value (lookup_value) ต้องมีอยู่ในฐานข้อมูล กล่าวคือกำลังมองหาการจับคู่แบบตรงทั้งหมด
ในตัวอย่างที่เราได้ดูในบทความนี้ ไม่จำเป็นต้องมีการจับคู่แบบตรงทั้งหมด นี่เป็นกรณีที่ฟังก์ชัน VPR ต้องเปลี่ยนเป็นโหมดโดยประมาณเพื่อส่งคืนผลลัพธ์ที่ต้องการ
ตัวอย่างเช่น: เราต้องการกำหนดอัตราที่จะใช้ในการคำนวณค่าคอมมิชชั่นสำหรับพนักงานขายที่มีปริมาณการขาย $34988 การทำงาน VPR คืนค่าให้เราเป็น 30% ซึ่งถูกต้องอย่างแน่นอน แต่ทำไมสูตรถึงเลือกแถวที่มี 30% และไม่ใช่ 20% หรือ 40%? การค้นหาโดยประมาณหมายความว่าอย่างไร ให้ชัดเจน
เมื่อเกิดการโต้เถียง ช่วง_lookup (interval_lookup) มีค่า ทรูโค้ด (TRUE) หรือละเว้น, ฟังก์ชัน VPR วนซ้ำในคอลัมน์แรกและเลือกค่าที่ใหญ่ที่สุดที่ไม่เกินค่าการค้นหา
จุดสำคัญ: เพื่อให้โครงร่างนี้ทำงานได้ คอลัมน์แรกของตารางจะต้องเรียงลำดับจากน้อยไปมาก