วิธีค้นหาการเกิดขึ้นล่าสุดของรายการในรายการโดยใช้สูตร Excel

สรุป: ในบทช่วยสอนนี้ คุณจะได้เรียนรู้วิธีใช้สูตร Excel เพื่อค้นหารายการสุดท้ายในรายการ

เมื่อเร็ว ๆ นี้ฉันกำลังทำงานในวาระการประชุม

ฉันมีรายชื่อใน Excel พร้อมรายชื่อบุคคลและวันที่ที่พวกเขาเป็น "ประธานการประชุม"

ฉันยังจำเป็นต้องรู้ด้วยว่าเมื่อใดที่บุคคลนั้นเป็น "ประธานการประชุม" ครั้งสุดท้ายเนื่องจากการซ้ำซ้อนในรายการ (ซึ่งหมายความว่าบุคคลนั้นเป็นประธานการประชุมหลายครั้ง)

นี่เป็นเพราะว่าฉันต้องตรวจสอบให้แน่ใจว่าประธานคนล่าสุดไม่ได้รับการมอบหมายใหม่

ดังนั้นฉันจึงตัดสินใจใช้เวทย์มนตร์ของฟังก์ชัน Excel เพื่อทำงาน

ด้านล่างนี้คือผลลัพธ์สุดท้าย ฉันสามารถเลือกชื่อจากเมนูแบบเลื่อนลง และให้วันที่ที่เกิดครั้งสุดท้ายของชื่อนั้นในรายการ

หากคุณมีความเข้าใจฟังก์ชัน Excel เป็นอย่างดี คุณจะรู้ว่าไม่มีฟังก์ชัน Excel ที่สามารถทำได้ที่นี่เราทำให้ปาฏิหาริย์เกิดขึ้น

ในบทช่วยสอนนี้ ฉันจะแสดงให้คุณเห็นสามวิธีในการทำเช่นนี้

ค้นหาเหตุการณ์ล่าสุด - โดยใช้ฟังก์ชัน MAX

นี่คือสูตร Excel ที่จะคืนค่าสุดท้ายในรายการ:

=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))

นี่คือวิธีการทำงานของสูตรนี้:

• ฟังก์ชัน MAX ใช้เพื่อค้นหาหมายเลขบรรทัดของชื่อที่ตรงกันล่าสุดตัวอย่างเช่น ถ้าชื่อคือ Glen จะส่งกลับ 11 เพราะอยู่ในบรรทัดที่ 11เนื่องจากรายการของเราเริ่มต้นในแถวที่สอง 1 จะถูกลบดังนั้นการเกิดขึ้นครั้งสุดท้ายของ Glen คือ 10 ในรายการของเรา
• SUMPRODUCT ใช้เพื่อให้แน่ใจว่าคุณไม่จำเป็นต้องใช้ Control + Shift + Enter เนื่องจาก SUMPRODUCT สามารถจัดการสูตรอาร์เรย์ได้
• ขณะนี้ใช้ฟังก์ชัน INDEX เพื่อค้นหาวันที่ของชื่อที่ตรงกันล่าสุด

ค้นหาเหตุการณ์ล่าสุด - โดยใช้ฟังก์ชัน LOOKUP

นี่เป็นอีกสูตรหนึ่งที่ทำงานเหมือนกัน:

=LOOKUP(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)

นี่คือวิธีการทำงานของสูตรนี้:

• ค่าการค้นหาคือ 2 (คุณจะเห็นว่าทำไม .. อ่านต่อ)
• ช่วงการค้นหาคือ 1/($A$2:$A$14=$D$3) - จะคืนค่า 1 เมื่อพบชื่อที่ตรงกัน มิฉะนั้นจะเกิดข้อผิดพลาดดังนั้นคุณจึงลงเอยด้วยอาร์เรย์例如，查找值為 Glen，數組將為 {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}。
• พารามิเตอร์ที่สาม ([result_vector]) คือช่วงที่ให้ผลลัพธ์ ในกรณีนี้คือวันที่

เหตุผลที่สูตรนี้ทำงานก็คือฟังก์ชัน LOOKUP ใช้เทคนิคการจับคู่โดยประมาณซึ่งหมายความว่าหากพบการจับคู่แบบตรงทั้งหมด ระบบจะส่งคืน แต่ถ้าไม่พบ ระบบจะสแกนอาร์เรย์ทั้งหมดจนถึงจุดสิ้นสุดและคืนค่าที่มากที่สุดรองลงมาซึ่งน้อยกว่าค่าการค้นหา

ในกรณีนี้ ค่าการค้นหาคือ 2 และในอาร์เรย์ของเรา เราเพิ่งได้รับ 1 หรือข้อผิดพลาดดังนั้นมันจึงสแกนอาร์เรย์ทั้งหมดและส่งคืนตำแหน่งของ 1 สุดท้าย - ค่าที่ตรงกันล่าสุดสำหรับชื่อ

ค้นหาการเกิดขึ้นล่าสุด - การใช้ฟังก์ชันที่กำหนดเอง (VBA)

ให้ฉันแสดงให้คุณเห็นวิธีอื่นด้วย

เราสามารถสร้างฟังก์ชันแบบกำหนดเอง (หรือที่เรียกว่าฟังก์ชันที่ผู้ใช้กำหนดเอง) โดยใช้ VBA

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

ฉันสร้างสูตรง่ายๆ (เหมือนกับสูตร VLOOKUP)

ในการสร้างฟังก์ชันแบบกำหนดเอง คุณต้องมีโค้ด VBA ในตัวแก้ไข VBฉันจะให้รหัสและขั้นตอนในการใส่ลงในโปรแกรมแก้ไข VB ในภายหลัง แต่ให้ฉันแสดงวิธีการทำงานก่อน:

นี่คือสูตรที่จะให้ผลลัพธ์:

=LastItemLookup($D$3,$A$2:$B$14,2)

สูตรนี้ใช้พารามิเตอร์สามตัว:

• ค้นหาค่า (ซึ่งจะเป็นชื่อในเซลล์ D3)
• ค้นหาช่วง (ซึ่งจะเป็นช่วงที่มีชื่อและวันที่ – A2:B14)
• หมายเลขคอลัมน์ (นี่คือคอลัมน์ที่เราต้องการผลลัพธ์)

หลังจากที่คุณสร้างสูตรและใส่โค้ดในโปรแกรมแก้ไข VB แล้ว คุณสามารถใช้สูตรนี้ได้เหมือนกับฟังก์ชันเวิร์กชีต Excel ทั่วไปอื่นๆ

นี่คือรหัสสำหรับสูตร:

ฟังก์ชัน LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long For i = LookupRange.Columns(1).Cells.Count To 1 Step -1 ถ้า Lookupvalue = LookupRange.Cells(i, 1) จากนั้น LastItemLookup = LookupRange.Cells (i, ColumnNumber) ออกจากฟังก์ชัน End หาก Next i สิ้นสุดฟังก์ชัน

ต่อไปนี้เป็นขั้นตอนในการใส่โค้ดนี้ลงในโปรแกรมแก้ไข VB:

1. ไปที่แท็บนักพัฒนา
2. คลิกตัวเลือก Visual Basicซึ่งจะเป็นการเปิดตัวแก้ไข VB ในแบ็กเอนด์
3. ในบานหน้าต่าง Project Explorer ของตัวแก้ไข VB ให้คลิกขวาที่วัตถุใดๆ ในเวิร์กบุ๊กที่คุณต้องการแทรกโค้ดหากคุณไม่เห็น Project Explorer ให้ไปที่แท็บ มุมมอง แล้วคลิก Project Explorer
4. ไปที่ แทรก และคลิกที่ โมดูลการดำเนินการนี้จะแทรกอ็อบเจ็กต์โมดูลสำหรับเวิร์กบุ๊กของคุณ
5. คัดลอกและวางรหัสลงในหน้าต่างโมดูล

ตอนนี้สูตรจะพร้อมใช้งานในทุกแผ่นงานของสมุดงาน

โปรดทราบว่าคุณจะต้องบันทึกเวิร์กบุ๊กในรูปแบบ .XLSM เนื่องจากมีมาโครนอกจากนี้ ถ้าคุณต้องการให้สูตรนี้พร้อมใช้งานในเวิร์กบุ๊กทั้งหมดที่คุณใช้ คุณสามารถบันทึกสูตรนี้ลงในเวิร์กบุ๊กแมโครส่วนตัวของคุณหรือสร้าง Add-in จากสูตรนี้ได้