Tutorial này sẽ hướng dẫn cách tìm giá trị trùng cuối cùng trong Google Sheets bằng cách sử dụng các hàm LOOKUP và XLOOKUP (mới). Những mẹo này áp dụng cho cả dữ liệu đã được sắp xếp và chưa được sắp xếp.
Bạn có thể sử dụng kỹ năng tìm kiếm này trong nhiều tình huống thực tế, chẳng hạn như tìm thời điểm cuối cùng giá của một mặt hàng thay đổi, hoặc lần cuối cùng khách hàng mua hàng.
Ví dụ về giá trị trùng cuối cùng:
Bảng trên đây cho thấy sự thay đổi giá của một mặt hàng trong 10 ngày qua.
Tôi muốn tìm kiếm xuống cột “Giá” (cột B) để tìm sự xuất hiện cuối cùng của giá trị 236 và trả về ngày tương ứng từ cột A. Điều này sẽ giúp tôi hiểu khi nào giá bị thay đổi gần đây nhất.
Nói một cách khác, tôi muốn tìm giá trị trùng cuối cùng trong Google Sheets. Có hai cách giải quyết dễ dàng bằng cách sử dụng hàm LOOKUP và bạn sẽ chọn cách giải quyết phù hợp với cách bạn sắp xếp dữ liệu của mình.
Giải pháp 1 dành cho dữ liệu chưa được sắp xếp và giải pháp 2 dành cho dữ liệu đã được sắp xếp. Trong ví dụ trên, cột chứa giá trị không được sắp xếp. Do đó, chúng ta có thể sử dụng giải pháp 1 ở đây.
Nếu bạn sử dụng XLOOKUP, bạn không cần phải lo lắng về việc sắp xếp. Chúng ta sẽ thấy điều đó trong phần sau của bài hướng dẫn này. Chúng ta sẽ bắt đầu với LOOKUP.
Trước khi viết công thức, tôi cần giải thích một số mẹo cần thiết về LOOKUP.
LOOKUP Function: Mẹo quan trọng cho Google Sheets
Hàm LOOKUP có lợi thế hơn VLOOKUP và HLOOKUP vì nó có thể hoạt động trên cả bộ dữ liệu theo chiều ngang và theo chiều dọc, và nó có thể trả về giá trị trùng cuối cùng trong quá trình tra cứu.
Cú pháp của hàm LOOKUP như sau:
LOOKUP(giá_trị_tìm_kiếm, dải_tìm_kiếm, dải_kết_quả)
Ví dụ:
=LOOKUP(3, {1,3,3,4}, {“a”,”b”,”c”,”d”})
Kết quả: “c”
Điều đó có nghĩa là nếu giá trị tìm kiếm được tìm thấy, nó sẽ trả về giá trị trùng cuối cùng. Xem xem sẽ có gì xảy ra khi không có giá trị tìm kiếm.
=LOOKUP(6, {1,3,3,4}, {“a”,”b”,”c”,”d”})
Kết quả: “d”
Ở đây, giá trị tìm kiếm 6 không có trong phạm vi tra cứu. Do đó, giá trị nhỏ hơn ngay lập tức so với 6 và có mặt trong phạm vi tra cứu sẽ được xem xét.
Tìm giá trị trùng cuối cùng trong dữ liệu chưa được sắp xếp trong Google Sheets (Giải pháp 1)
Hàm LOOKUP là một trong những giải pháp hiệu quả nhất để tìm giá trị trùng cuối cùng trong Google Sheets. XLOOKUP là một hàm mới hơn cũng rất hiệu quả.
Tôi biết rằng chức năng trước chỉ hoạt động hiệu quả với dữ liệu đã được sắp xếp, nhưng dữ liệu của chúng tôi chưa được sắp xếp. Tuy nhiên, chúng ta có thể xử lý điều này bằng cách sử dụng hai phương pháp.
Phương pháp 1: Bằng cách sử dụng 1 và #DIV/0! trong phạm vi tìm kiếm
Dựa vào ví dụ của chúng tôi trong Hình 1 ở trên, chúng ta muốn tìm hàng cuối cùng mà giá trị 236 được tìm thấy trong phạm vi B2:B11 và trả về ngày từ phạm vi A2:A11 từ hàng đó.
Công thức LOOKUP sau đây có một điều kiện được cung cấp sẵn:
=ARRAYFORMULA(LOOKUP(2,1/(B2:B11=236),A2:A11))
Để sử dụng tham chiếu ô cho điều kiện, ví dụ: ô E6, nhập giá trị điều kiện, 236, vào ô E4 và sử dụng công thức LOOKUP sau đây:
=ARRAYFORMULA(LOOKUP(2,1/(B2:B11=E4),A2:A11))
Kết quả của công thức là 08/07/2018, đây là ngày mà giá trị 236 được tìm thấy cuối cùng trong cột giá (cột B).
Xem lại công thức và chú ý đặc biệt đến phần được tô màu xanh lá cây, đó là 1/(B2:B11=236).
Trong đó, công thức B2:B11=236 trả về TRUE hoặc FALSE tuỳ thuộc vào việc giá trị 236 có được tìm thấy trong phạm vi B2:B11 hay không. Bằng cách chia cho 1, chúng ta có 1 cho TRUE và #DIV/0! cho FALSE.
{1;1;1;1;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!}
Đó là phạm vi tìm kiếm (search_range) trong LOOKUP. Trong công thức LOOKUP của tôi, bạn có thể thấy rằng giá trị tìm kiếm là 2. Vì khóa này không có sẵn trong phạm vi tra cứu, LOOKUP sẽ tìm giá trị trùng cuối cùng, tức là 1 trong hàng thứ 8 của phạm vi tra cứu, và trả về ngày từ phạm vi kết quả.
=LOOKUP(2, {1;1;1;1;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!}, {"01/07/2018","02/07/2018","03/07/2018","04/07/2018","05/07/2018","06/07/2018","07/07/2018","08/07/2018","09/07/2018","10/07/2018"})
Lưu ý: Các ngày trong công thức giải thích này dựa trên định dạng ngày mặc định trong bảng của tôi.
Phương pháp 2: Bằng cách sắp xếp ảo phạm vi tìm kiếm và phạm vi kết quả tìm kiếm
Đây là một phương pháp khác để tìm giá trị trùng cuối cùng trong một phạm vi trong Google Sheets. Đó là bằng cách sắp xếp ảo phạm vi tìm kiếm và phạm vi kết quả tìm kiếm.
Công thức là:
=IF(MATCH(E4,B2:B11,0),TO_DATE(<strong>LOOKUP(E4,SORT(B2:B11),SORT(A2:A11,B2:B11,1))</strong>))
Cách hoạt động của công thức:
Công thức SORT sắp xếp phạm vi theo thứ tự tăng dần theo mặc định. Vì vậy, công thức sau đây sẽ sắp xếp phạm vi B2:B11 theo thứ tự tăng dần:
SORT(B2:B11)
Cú pháp: SORT(phạm_vi, cột_sắp_xếp, is_ascending, [cột_sắp_xếp2, …], [is_ascending2, …])
Chúng ta phải sắp xếp phạm vi A2:A11 theo phạm vi B2:B11.
Để làm điều này, chúng ta phải chỉ định phạm vi cột_sắp_xếp và is_ascending trong công thức. Điều này cho biết công thức sắp xếp phạm vi A2:A11 theo giá trị trong phạm vi B2:B11 theo thứ tự tăng dần.
SORT(A2:A11,B2:B11,1)
Các phần còn lại của công thức là tùy chọn. Đó là TO_DATE và IF + MATCH. Công thức đầu tiên định dạng lại kết quả, thường là một giá trị ngày, thành một ngày hợp lệ, trong khi công thức sau giúp thực thi công thức nếu có sự trùng khớp chính xác của khóa tìm kiếm có sẵn trong phạm vi tra cứu.
Tìm giá trị trùng cuối cùng trong dữ liệu đã được sắp xếp trong Google Sheets (Giải pháp 2)
Bạn đã học phần phức tạp hơn trước tiên. Vì vậy, ở đây, mọi thứ đơn giản hơn.
Ở đây, B2:B11, phạm vi tra cứu, đã được sắp xếp. Vì vậy, chúng ta có thể sử dụng công thức LOOKUP sau đây để tìm giá trị trùng cuối cùng trong một phạm vi đã được sắp xếp trong Google Sheets.
=IF(MATCH(E4,B2:B11,0),<strong>LOOKUP(E4,B2:B11,A2:A11)</strong>)
Tôi đã giải thích vai trò của IF + MATCH trong ví dụ trước. Nó giúp thực thi công thức LOOKUP nếu khóa tìm kiếm có sẵn trong phạm vi tìm kiếm.
XLOOKUP: Hàm mới trong Google Sheets để tìm giá trị trùng cuối cùng
XLOOKUP không có sẵn khi tôi viết bài hướng dẫn này lần đầu. Do đó, tôi đã sử dụng hàm LOOKUP để tìm giá trị trùng cuối cùng.
Tất cả các công thức trên đều hoạt động tốt. Tuy nhiên, XLOOKUP đơn giản hơn tất cả chúng.
Cú pháp:
XLOOKUP(giá_trị_tìm_kiếm, phạm_vi_tra_cứu, phạm_vi_kết_quả, [giá_trị_thiếu], [chế_độ_khớp], [chế_độ_tìm_kiếm])Nó không gặp bất kỳ vấn đề nào khi tìm giá trị trùng cuối cùng trong một phạm vi đã được sắp xếp hoặc chưa sắp xếp. Bạn có thể sử dụng công thức sau đây cho dù dữ liệu của bạn đã được sắp xếp hoặc chưa:
=XLOOKUP(E4,B2:B11,A2:A11,"Missing",0,-1)
Trong công thức này, bạn có thể thay thế từ “Missing” bằng bất kỳ văn bản tùy chỉnh nào.
Liên quan: Lấy giá trị gần đây nhất không trống theo ngày trong Google Sheets.
