Tóm tắt: trong hướng dẫn này, bạn sẽ học cách sử dụng công thức Excel để tìm lần xuất hiện cuối cùng của một mục trong danh sách.
Gần đây, tôi đang làm việc trong một chương trình họp.
Tôi có một danh sách trong Excel với danh sách những người và ngày họ là "chủ tọa cuộc họp".
Tôi cũng cần biết thời điểm một người là "chủ tọa cuộc họp" do có sự trùng lặp trong danh sách (có nghĩa là một người đã làm chủ tọa cuộc họp nhiều lần).
Điều này là do tôi phải đảm bảo rằng chủ tịch gần đây nhất không được chỉ định lại.
Vì vậy, tôi quyết định sử dụng một số phép thuật của hàm Excel để thực hiện công việc.
Dưới đây là kết quả cuối cùng, tôi có thể chọn một tên từ menu thả xuống và nó cho tôi ngày xuất hiện lần cuối cùng của tên đó trong danh sách.
Nếu bạn đã hiểu rõ về các hàm Excel, bạn sẽ biết rằng không có hàm Excel nào có thể làm được điều này.Ở đây chúng tôi làm cho điều kỳ diệu xảy ra.
Trong hướng dẫn này, tôi sẽ chỉ cho bạn ba cách để thực hiện việc này.
Nội dung
Tìm lần xuất hiện cuối cùng - sử dụng hàm MAX
Đây là công thức Excel sẽ trả về giá trị cuối cùng trong danh sách:
=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
Đây là cách công thức này hoạt động:
- Hàm MAX được sử dụng để tìm số dòng của tên trùng khớp cuối cùng.Ví dụ: nếu tên là Glen, nó sẽ trả về 11 vì nó nằm trên dòng 11.Vì danh sách của chúng ta bắt đầu ở hàng thứ hai, nên 1 sẽ bị trừ.Do đó, lần xuất hiện cuối cùng của Glen là 10 trong danh sách của chúng tôi.
- SUMPRODUCT được sử dụng để đảm bảo bạn không phải sử dụng Control + Shift + Enter vì SUMPRODUCT có thể xử lý các công thức mảng.
- Hàm INDEX hiện được sử dụng để tìm ngày của tên trùng khớp cuối cùng.
Tìm lần xuất hiện cuối cùng - sử dụng hàm LOOKUP
Đây là một công thức khác thực hiện công việc tương tự:
=LOOKUP(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
Đây là cách công thức này hoạt động:
- Giá trị tra cứu là 2 (bạn sẽ thấy tại sao .. hãy tiếp tục đọc)
- Phạm vi tra cứu là 1 / ($ A $ 2: $ A $ 14 = $ D $ 3) - nó trả về 1 khi tìm thấy tên phù hợp và nếu không thì sẽ xảy ra lỗi.Vì vậy, bạn kết thúc với một mảng.例如，查找值為 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!}。
- Tham số thứ ba ([result_vector]) là phạm vi mà nó cho kết quả, trong trường hợp này là ngày.
Lý do công thức này hoạt động là do hàm LOOKUP sử dụng kỹ thuật so khớp gần đúng.Điều này có nghĩa là nếu nó có thể tìm thấy một kết quả phù hợp chính xác, nó sẽ trả về, nhưng nếu không thể, nó sẽ quét toàn bộ mảng cho đến khi kết thúc và trả về giá trị lớn nhất tiếp theo nhỏ hơn giá trị tra cứu.
Trong trường hợp này, giá trị tra cứu là 2 và trong mảng của chúng tôi, chúng tôi chỉ nhận được 1 hoặc lỗi.Vì vậy, nó sẽ quét toàn bộ mảng và trả về vị trí của 1 cuối cùng - giá trị khớp cuối cùng cho tên.
Tìm lần xuất hiện cuối cùng - Sử dụng chức năng tùy chỉnh (VBA)
Hãy để tôi chỉ cho bạn một cách khác.
Chúng ta có thể tạo các hàm tùy chỉnh (còn được gọi là các hàm do người dùng xác định) bằng VBA.
Lợi ích của việc tạo các chức năng tùy chỉnh là dễ sử dụng.Bạn không phải lo lắng về việc tạo các công thức phức tạp mỗi lần vì hầu hết công việc diễn ra trong phần phụ trợ VBA.
Tôi đã tạo một công thức đơn giản (giống như công thức VLOOKUP).
Để tạo các chức năng tùy chỉnh, bạn cần mã VBA trong trình soạn thảo VB.Tôi sẽ cung cấp cho bạn mã và các bước để đưa nó vào trình soạn thảo VB sau, nhưng hãy để tôi chỉ cho bạn cách nó hoạt động trước:
Đây là công thức sẽ cung cấp cho bạn kết quả:
= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)
Công thức có ba tham số:
- Tìm giá trị (đây sẽ là tên trong ô D3)
- Tìm phạm vi (đây sẽ là phạm vi có tên và ngày - A2: B14)
- số cột (đây là cột mà chúng tôi muốn kết quả)
Sau khi bạn tạo công thức và đặt mã vào trình soạn thảo VB, bạn có thể sử dụng nó giống như bất kỳ hàm trang tính Excel thông thường nào khác.
Đây là mã cho công thức:
Hàm 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 If Lookupvalue = LookupRange.Cells (i, 1) Then LastItemLookup = LookupRange.Cells (i, ColumnNumber) Thoát Chức năng Kết thúc Nếu Tiếp theo Tôi Kết thúc Chức năng
Dưới đây là các bước để đưa mã này vào trình soạn thảo VB:
- Chuyển đến tab nhà phát triển.
- Bấm Tùy chọn Visual Basic.Thao tác này sẽ mở trình soạn thảo VB trong chương trình phụ trợ.
- Trong ngăn Project Explorer của trình soạn thảo VB, bấm chuột phải vào bất kỳ đối tượng nào trong sổ làm việc mà bạn muốn chèn mã.Nếu bạn không thấy Project Explorer, hãy chuyển đến tab View và nhấp vào Project Explorer.
- Đi tới Chèn và nhấp vào Mô-đun.Thao tác này sẽ chèn một đối tượng mô-đun cho sổ làm việc của bạn.
- Sao chép và dán mã vào cửa sổ mô-đun.
Bây giờ công thức sẽ có sẵn trong tất cả các trang tính của sổ làm việc.
Lưu ý rằng bạn sẽ cần lưu sổ làm việc ở định dạng .XLSM vì nó chứa macro.Ngoài ra, nếu bạn muốn công thức này có sẵn trong tất cả các sổ làm việc bạn sử dụng, bạn có thể lưu nó vào sổ làm việc macro cá nhân của mình hoặc tạo một bổ trợ từ nó.
