ค้นหาหมายเลขที่ใกล้ที่สุด – อาหารเพื่อสุขภาพใกล้ตัวฉัน

เนื้อหา

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

การคำนวณส่วนลดขึ้นอยู่กับปริมาณ
การคำนวณจำนวนโบนัสขึ้นอยู่กับการดำเนินการตามแผน
การคำนวณอัตราค่าจัดส่งขึ้นอยู่กับระยะทาง
การเลือกภาชนะที่เหมาะสมกับสินค้า ฯลฯ

นอกจากนี้ อาจต้องปัดเศษขึ้นและลง ขึ้นอยู่กับสถานการณ์

มีหลายวิธี - ที่ชัดเจนและไม่ชัดเจนนัก - ในการแก้ปัญหาดังกล่าว ลองดูที่พวกเขาตามลำดับ

ในการเริ่มต้น ให้ลองนึกภาพซัพพลายเออร์ที่ให้ส่วนลดสำหรับการขายส่ง และเปอร์เซ็นต์ของส่วนลดจะขึ้นอยู่กับปริมาณของสินค้าที่ซื้อ ตัวอย่างเช่น เมื่อซื้อมากกว่า 5 ชิ้น จะได้รับส่วนลด 2% และเมื่อซื้อตั้งแต่ 20 ชิ้นขึ้นไป – แล้ว 6% เป็นต้น

วิธีการคำนวณเปอร์เซ็นต์ส่วนลดอย่างรวดเร็วและสวยงามเมื่อป้อนปริมาณของสินค้าที่ซื้อ?

วิธีที่ 1: IFs ที่ซ้อนกัน

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

การหาจำนวนที่ใกล้ที่สุด

ฉันคิดว่ามันชัดเจนว่าการดีบักเช่น "ตุ๊กตาสัตว์ประหลาด" หรือพยายามเพิ่มเงื่อนไขใหม่สองสามอย่างหลังจากผ่านไประยะหนึ่งเป็นเรื่องสนุก

นอกจากนี้ Microsoft Excel ยังมีขีดจำกัดการซ้อนสำหรับฟังก์ชัน IF – 7 ครั้งในเวอร์ชันเก่าและ 64 ครั้งในเวอร์ชันใหม่กว่า ถ้าคุณต้องการมากขึ้น?

วิธีที่ 2. VLOOKUP พร้อมการดูช่วงเวลา

วิธีนี้กระชับกว่ามาก ในการคำนวณเปอร์เซ็นต์ส่วนลด ให้ใช้ฟังก์ชันระดับตำนาน VPR (วีลุคอัพ) ในโหมดการค้นหาโดยประมาณ:

การหาจำนวนที่ใกล้ที่สุด

ที่ไหน

B4 – มูลค่าของปริมาณสินค้าในการทำธุรกรรมครั้งแรกที่เรากำลังมองหาส่วนลด
$G$4:$H$8 – ลิงก์ไปยังตารางส่วนลด – ไม่มี “ส่วนหัว” และที่อยู่แก้ไขด้วยเครื่องหมาย $
2 — เลขลำดับของคอลัมน์ในตารางส่วนลดที่เราอยากได้ค่าส่วนลด
TRUE - นี่คือที่ฝัง "สุนัข" ถ้าเป็นอาร์กิวเมนต์ฟังก์ชันสุดท้าย VPR ระบุ โกหก (เท็จ) หรือ 0จากนั้นฟังก์ชันจะมองหา การแข่งขันที่เข้มงวด ในคอลัมน์ปริมาณ (และในกรณีของเราจะมีข้อผิดพลาด #N/A เนื่องจากไม่มีค่า 49 ในตารางส่วนลด) แต่ถ้าแทน โกหก เขียน TRUE (จริง) หรือ 1จากนั้นฟังก์ชันจะค้นหาไม่แน่นอน แต่ เล็กที่สุดที่ใกล้ที่สุด มูลค่าและจะให้เปอร์เซ็นต์ส่วนลดที่เราต้องการ

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

การหาจำนวนที่ใกล้ที่สุด

ดังนั้น วิธีนี้สามารถใช้เพื่อค้นหาค่าที่น้อยที่สุดที่ใกล้ที่สุดเท่านั้น หากคุณต้องการค้นหาที่ใหญ่ที่สุดที่ใกล้ที่สุด คุณต้องใช้วิธีอื่น

วิธีที่ 3. การหาค่าที่ใกล้ที่สุดโดยใช้ฟังก์ชัน INDEX และ MATCH

ทีนี้มาดูปัญหาของเราจากอีกด้านหนึ่ง สมมติว่าเราขายปั๊มอุตสาหกรรมหลายรุ่นที่มีความจุต่างกัน ตารางการขายทางด้านซ้ายแสดงกำลังที่ลูกค้าต้องการ เราจำเป็นต้องเลือกปั๊มที่มีกำลังสูงสุดหรือเท่ากัน แต่ไม่น้อยกว่าที่โครงการกำหนด

ฟังก์ชัน VLOOKUP จะไม่ช่วยอะไรที่นี่ ดังนั้น คุณจะต้องใช้ฟังก์ชันอะนาล็อก - ฟังก์ชัน INDEX จำนวนมาก (ดัชนี) และเปิดเผยมากขึ้น (การแข่งขัน):

การหาจำนวนที่ใกล้ที่สุด

ที่นี่ ฟังก์ชัน MATCH ที่มีอาร์กิวเมนต์สุดท้าย -1 ทำงานในโหมดการค้นหาค่าที่ใหญ่ที่สุดที่ใกล้ที่สุด และฟังก์ชัน INDEX จะแยกชื่อรุ่นที่เราต้องการจากคอลัมน์ที่อยู่ติดกัน

วิธีที่ 4. ฟังก์ชันใหม่ VIEW (XLOOKUP)

หากคุณมี Office 365 เวอร์ชันที่ติดตั้งการอัปเดตทั้งหมด ให้แทนที่ VLOOKUP (วีลุคอัพ) คุณสามารถใช้แอนะล็อกได้ – ฟังก์ชัน VIEW (เอ็กซ์ลุคอัพ)ซึ่งฉันได้วิเคราะห์โดยละเอียดแล้ว:

การหาจำนวนที่ใกล้ที่สุด

ที่นี่:

B4 – ค่าเริ่มต้นของปริมาณของผลิตภัณฑ์ที่เรากำลังมองหาส่วนลด
$G$4:$G$8 – ช่วงที่เรากำลังมองหาแมตช์
$H$4:$H$8 – ช่วงผลลัพธ์ที่คุณต้องการคืนส่วนลด
อาร์กิวเมนต์ที่สี่ (-1) รวมการค้นหาตัวเลขที่น้อยที่สุดที่ใกล้ที่สุดที่เราต้องการแทนการจับคู่แบบตรงทั้งหมด

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

แต่น่าเสียดายที่ไม่ใช่ทุกคนที่มีฟีเจอร์นี้ – มีเพียงเจ้าของ Office 365 ที่มีความสุขเท่านั้น

วิธีที่ 5. Power Query

ถ้าคุณยังไม่คุ้นเคยกับ Add-in ของ Power Query ที่มีประสิทธิภาพและฟรีสำหรับ Excel แสดงว่าคุณอยู่ที่นี่ หากคุณคุ้นเคยแล้วลองใช้มันเพื่อแก้ปัญหาของเรา

มาทำงานเตรียมการกันก่อน:

มาแปลงตารางต้นฉบับของเราเป็นไดนามิก (อัจฉริยะ) โดยใช้แป้นพิมพ์ลัด Ctrl+T หรือทีม หน้าแรก – จัดรูปแบบเป็นตาราง (หน้าแรก — รูปแบบเป็นตาราง).
เพื่อความชัดเจน ให้ตั้งชื่อพวกเขา การขาย и ราคาพิเศษสุด แถบ นวกรรมิก (ออกแบบ).
โหลดแต่ละตารางลงใน Power Query โดยใช้ปุ่ม จากตาราง/ช่วง แถบ ข้อมูล (ข้อมูล — จากตาราง/ช่วง). ใน Excel เวอร์ชันล่าสุด ปุ่มนี้ถูกเปลี่ยนชื่อเป็น มีใบ (จากแผ่นงาน).
ถ้าตารางมีชื่อคอลัมน์ที่แตกต่างกันพร้อมปริมาณ ดังในตัวอย่างของเรา (“ปริมาณสินค้า” และ “ปริมาณจาก…”) จะต้องเปลี่ยนชื่อใน Power Query และตั้งชื่อให้เหมือนกัน
หลังจากนั้น คุณสามารถกลับไปที่ Excel โดยเลือกคำสั่งในหน้าต่างตัวแก้ไข Power Query หน้าแรก — ปิดและโหลด — ปิดและโหลดใน… (หน้าแรก — ปิด&โหลด — ปิด&โหลดไปที่…) แล้วก็ตัวเลือก เพียงแค่สร้างการเชื่อมต่อ (สร้างการเชื่อมต่อเท่านั้น).
จากนั้นสิ่งที่น่าสนใจที่สุดก็เริ่มต้นขึ้น หากคุณมีประสบการณ์ใน Power Query ฉันคิดว่าแนวความคิดเพิ่มเติมควรเป็นไปในทิศทางของการผสานสองตารางนี้เข้ากับแบบสอบถามแบบรวม (ผสาน) a la la VLOOKUP เช่นเดียวกับในวิธีการก่อนหน้านี้ อันที่จริง เราจะต้องผสานในโหมดเพิ่ม ซึ่งดูไม่ชัดเจนเลยในแวบแรก เลือกในแท็บ Excel ข้อมูล – รับข้อมูล – รวมคำขอ – เพิ่ม (ข้อมูล — รับข้อมูล — รวมการสืบค้น — ผนวก) แล้วก็โต๊ะของเรา การขาย и ราคาพิเศษสุด ในหน้าต่างที่ปรากฏขึ้น:
หลังจากคลิกที่ OK โต๊ะของเราจะถูกรวมเป็นหนึ่งเดียว - ใต้กันและกัน โปรดทราบว่าคอลัมน์ที่มีปริมาณสินค้าในตารางเหล่านี้อยู่ภายใต้กันและกันเพราะ พวกเขามีชื่อเดียวกัน:
หากลำดับของแถวเดิมในตารางการขายมีความสำคัญต่อคุณ ดังนั้นหลังจากการแปลงที่ตามมาทั้งหมด คุณสามารถกู้คืนได้ ให้เพิ่มคอลัมน์ที่มีตัวเลขลงในตารางของเราโดยใช้คำสั่ง การเพิ่มคอลัมน์ – คอลัมน์ดัชนี (เพิ่มคอลัมน์ — คอลัมน์ดัชนี). หากลำดับของบรรทัดไม่สำคัญสำหรับคุณ คุณสามารถข้ามขั้นตอนนี้ได้
ตอนนี้ ใช้รายการดรอปดาวน์ในส่วนหัวของตาราง จัดเรียงตามคอลัมน์ จำนวน จากน้อยไปมาก:
และเคล็ดลับหลัก: คลิกขวาที่ส่วนหัวของคอลัมน์ ส่วนลด เลือกทีม เติม-ลง (เติม - ลง). เซลล์ว่างด้วย โมฆะ กรอกค่าส่วนลดก่อนหน้าโดยอัตโนมัติ:
มันยังคงเรียกคืนลำดับเดิมของแถวโดยเรียงลำดับตามคอลัมน์ ดัชนี (คุณสามารถลบออกได้อย่างปลอดภัยในภายหลัง) และกำจัดบรรทัดที่ไม่จำเป็นด้วยตัวกรอง โมฆะ ตามคอลัมน์ รหัสธุรกรรม: