เนื้อหา
ฟังก์ชันการแทนที่การค้นหาและการพิมพ์แบบคลาสสิกทั้งหมด VPR (วีลุคอัพ), GPR (เอชลุคอัพ), เปิดเผยมากขึ้น (การแข่งขัน) และคุณลักษณะที่คล้ายคลึงกันมีคุณลักษณะที่สำคัญอย่างหนึ่งคือ พวกเขาค้นหาตั้งแต่ต้นจนจบ กล่าวคือ จากซ้ายไปขวาหรือบนลงล่างในข้อมูลต้นทาง ทันทีที่พบการจับคู่ครั้งแรก การค้นหาจะหยุดและพบเฉพาะการเกิดขึ้นครั้งแรกขององค์ประกอบที่เราต้องการเท่านั้น
จะทำอย่างไรถ้าเราต้องการค้นหาไม่ใช่ครั้งแรก แต่เกิดขึ้นครั้งสุดท้าย? ตัวอย่างเช่น ธุรกรรมล่าสุดสำหรับลูกค้า การชำระเงินครั้งล่าสุด คำสั่งซื้อล่าสุด ฯลฯ?
วิธีที่ 1: ค้นหาแถวสุดท้ายด้วยสูตรอาร์เรย์
หากตารางต้นฉบับไม่มีคอลัมน์ที่มีวันที่หรือหมายเลขลำดับของแถว (คำสั่งซื้อ การชำระเงิน ...) อันที่จริงแล้ว งานของเราคือค้นหาแถวสุดท้ายที่ตรงตามเงื่อนไขที่กำหนด สามารถทำได้ด้วยสูตรอาร์เรย์ต่อไปนี้:
ที่นี่:
- ฟังก์ชัน IF (ถ้า) ตรวจสอบเซลล์ทั้งหมดในคอลัมน์ทีละรายการ ลูกค้า และแสดงหมายเลขบรรทัดหากมีชื่อที่เราต้องการ หมายเลขบรรทัดบนแผ่นงานได้รับจากฟังก์ชัน สาย (แถว)แต่เนื่องจากเราต้องการหมายเลขแถวในตาราง เราจึงต้องลบ 1 เพิ่มเติม เนื่องจากเรามีส่วนหัวในตาราง
- จากนั้นฟังก์ชั่น MAX (สูงสุด) เลือกค่าสูงสุดจากชุดตัวเลขแถวที่เกิดขึ้น เช่น จำนวนบรรทัดล่าสุดของไคลเอ็นต์
- ฟังก์ชัน ดัชนี (ดัชนี) ส่งคืนเนื้อหาของเซลล์ด้วยตัวเลขสุดท้ายที่พบจากคอลัมน์ตารางที่จำเป็นอื่น ๆ (รหัสสั่งซื้อ).
ทั้งหมดนี้จะต้องป้อนเป็น สูตรอาร์เรย์, เช่น:
- ใน Office 365 ที่ติดตั้งการอัปเดตล่าสุดและรองรับอาร์เรย์ไดนามิก คุณเพียงแค่กด เข้าสู่.
- ในเวอร์ชั่นอื่น ๆ ทั้งหมดหลังจากป้อนสูตรแล้วจะต้องกดแป้นพิมพ์ลัด Ctrl+เปลี่ยน+เข้าสู่ซึ่งจะเพิ่มวงเล็บปีกกาลงในแถบสูตรโดยอัตโนมัติ
วิธีที่ 2: ค้นหาย้อนกลับด้วยฟังก์ชัน LOOKUP ใหม่
ฉันเขียนบทความยาวพร้อมวิดีโอเกี่ยวกับคุณลักษณะใหม่แล้ว ดู (เอ็กซ์ลุคอัพ)ซึ่งปรากฏใน Office เวอร์ชันล่าสุดเพื่อแทนที่ VLOOKUP . ตัวเก่า (วีลุคอัพ). ด้วยความช่วยเหลือของ BROWSE งานของเราได้รับการแก้ไขในเบื้องต้นเพราะ สำหรับฟังก์ชันนี้ (ต่างจาก VLOOKUP) คุณสามารถกำหนดทิศทางการค้นหาได้อย่างชัดเจน: จากบนลงล่างหรือล่างขึ้นบน – อาร์กิวเมนต์สุดท้าย (-1) รับผิดชอบสิ่งนี้:
วิธีที่ 3 ค้นหาสตริงที่มีวันที่ล่าสุด
หากในข้อมูลต้นฉบับ เรามีคอลัมน์ที่มีหมายเลขประจำเครื่องหรือวันที่ที่มีบทบาทคล้ายกัน งานนั้นได้รับการแก้ไข – เราจำเป็นต้องค้นหาไม่ใช่บรรทัดสุดท้าย (ต่ำสุด) ที่ตรงกัน แต่เป็นบรรทัดที่มีข้อมูลล่าสุด ( สูงสุด) วันที่
ฉันได้พูดคุยรายละเอียดเกี่ยวกับวิธีการใช้ฟังก์ชันแบบคลาสสิกแล้ว และตอนนี้ เรามาลองใช้พลังของฟังก์ชันไดนามิกอาร์เรย์ใหม่กัน เพื่อความสวยงามและสะดวกยิ่งขึ้น เรายังแปลงตารางเดิมเป็นตาราง "อัจฉริยะ" โดยใช้แป้นพิมพ์ลัด Ctrl+T หรือคำสั่ง หน้าแรก – จัดรูปแบบเป็นตาราง (หน้าแรก — รูปแบบเป็นตาราง).
ด้วยความช่วยเหลือของพวกเขา “คู่นักฆ่า” นี้แก้ปัญหาของเราได้อย่างงดงาม:
ที่นี่:
- ทำหน้าที่ก่อน FILTER (กรอง) เลือกเฉพาะแถวเหล่านั้นจากตารางของเราซึ่งอยู่ในคอลัมน์ ลูกค้า – ชื่อที่เราต้องการ
- จากนั้นฟังก์ชั่น ระดับการศึกษา (เรียงลำดับ) จัดเรียงแถวที่เลือกตามวันที่ในลำดับจากมากไปน้อย โดยดีลล่าสุดจะอยู่ที่ด้านบนสุด
- ฟังก์ชัน ดัชนี (ดัชนี) แยกแถวแรก กล่าวคือ คืนค่าการซื้อขายสุดท้ายที่เราต้องการ
- และสุดท้าย ฟังก์ชัน FILTER ภายนอกจะลบคอลัมน์ที่ 1 และ 3 พิเศษออกจากผลลัพธ์ (รหัสสั่งซื้อ и ลูกค้า) และเหลือเพียงวันที่และจำนวนเงิน สำหรับสิ่งนี้จะใช้อาร์เรย์ของค่าคงที่ {0;1;0;1}กำหนดคอลัมน์ที่เราต้องการ (1) หรือไม่ต้องการ (0) ให้แสดง
วิธีที่ 4: ค้นหาคู่สุดท้ายใน Power Query
เพื่อความสมบูรณ์ มาดูวิธีแก้ปัญหาการค้นหาย้อนกลับของเราโดยใช้ Add-in ของ Power Query ด้วยความช่วยเหลือของเธอ ทุกอย่างจะได้รับการแก้ไขอย่างรวดเร็วและสวยงาม
1. มาแปลงตารางเดิมของเราให้เป็น "อัจฉริยะ" โดยใช้แป้นพิมพ์ลัด Ctrl+T หรือคำสั่ง หน้าแรก – จัดรูปแบบเป็นตาราง (หน้าแรก — รูปแบบเป็นตาราง).
2. โหลดลงใน Power Query ด้วยปุ่ม จากตาราง/ช่วง แถบ ข้อมูล (ข้อมูล — จากตาราง/ช่วง).
3. เราจัดเรียง (ผ่านรายการดรอปดาวน์ของตัวกรองในส่วนหัว) ตารางของเราโดยเรียงจากมากไปน้อยของวันที่ เพื่อให้ธุรกรรมล่าสุดอยู่ด้านบนสุด
4… ในแท็บ การแปลง เลือกทีม จัดกลุ่มตาม (แปลง — จัดกลุ่มตาม) และตั้งค่าการจัดกลุ่มตามลูกค้า และในฐานะฟังก์ชันการรวม ให้เลือกตัวเลือก ทุกสาย (ทุกแถว). คุณสามารถตั้งชื่อคอลัมน์ใหม่อะไรก็ได้ที่คุณชอบ – ตัวอย่างเช่น รายละเอียด.
หลังจากจัดกลุ่มแล้ว เราจะได้รายชื่อลูกค้าที่ไม่ซ้ำกันและในคอลัมน์ รายละเอียด – ตารางที่มีการทำธุรกรรมทั้งหมดของแต่ละรายการ โดยที่รายการแรกจะเป็นรายการล่าสุด ซึ่งเป็นสิ่งที่เราต้องการ:
5. เพิ่มคอลัมน์จากการคำนวณใหม่ด้วยปุ่ม คอลัมน์ที่กำหนดเอง แถบ เพิ่มคอลัมน์ (เพิ่มคอลัมน์ — เพิ่มคอลัมน์กำหนดเอง)และป้อนสูตรต่อไปนี้:
Here รายละเอียด – นี่คือคอลัมน์ที่เรานำตารางโดยลูกค้าและ 0 {} คือจำนวนแถวที่เราต้องการแยก (การกำหนดหมายเลขแถวใน Power Query เริ่มจากศูนย์) เราได้รับคอลัมน์ที่มีระเบียน (บันทึก) โดยที่แต่ละรายการคือแถวแรกจากแต่ละตาราง:
มันยังคงขยายเนื้อหาของระเบียนทั้งหมดด้วยปุ่มที่มีลูกศรคู่ในส่วนหัวของคอลัมน์ ข้อตกลงล่าสุด การเลือกคอลัมน์ที่ต้องการ:
…แล้วลบคอลัมน์ที่ไม่ต้องการอีกต่อไป รายละเอียด โดยคลิกขวาที่ชื่อ - ลบคอลัมน์ (ลบคอลัมน์).
หลังจากอัพโหลดผลงานลงแผ่นผ่าน หน้าแรก — ปิดและโหลด — ปิดและโหลดใน (หน้าแรก — ปิด & โหลด — ปิด & โหลดไปที่…) เราจะได้รับตารางที่ดีพร้อมรายการธุรกรรมล่าสุด ตามที่เราต้องการ:
เมื่อคุณเปลี่ยนข้อมูลต้นฉบับ คุณต้องไม่ลืมที่จะอัปเดตผลลัพธ์โดยคลิกขวาที่ข้อมูลนั้น - คำสั่ง อัปเดตและบันทึก (รีเฟรช) หรือแป้นพิมพ์ลัด Ctrl+อื่น ๆ+F5.
- ฟังก์ชัน LOOKUP เป็นลูกหลานของ VLOOKUP
- วิธีใช้ฟังก์ชันอาร์เรย์ไดนามิกใหม่ SORT, FILTER และ UNIC
- ค้นหาเซลล์ที่ไม่ว่างเซลล์สุดท้ายในแถวหรือคอลัมน์ด้วยฟังก์ชัน LOOKUP