Khi bạn muốn lọc các hàng thay đổi trạng thái cuối cùng, không có hàm độc lập nào mà bạn có thể phụ thuộc vào trong Google Sheets. Vậy thì làm sao?
Chúng ta phải sử dụng một công thức kết hợp, và lựa chọn của tôi là công thức dựa trên Filter và Sortn.
Tôi có một công thức linh hoạt và chúng ta có thể dễ dàng chuyển đổi nó để tìm thay đổi trong bất kỳ cột nào trong một hàng và các hàng tiếp theo.
Vui lòng xem hai ảnh chụp màn hình dưới đây để hiểu ý tôi khi nói về các hàng thay đổi trạng thái.
Thay đổi trạng thái trong một cột (ví dụ # 1):
Trong ví dụ này, trạng thái của nhân viên “Rosa” và “Silvia” đã thay đổi vào tháng Ba.
Đối với “Ben” và “Gary”, không có thay đổi trong trạng thái của họ.
Thay đổi trạng thái trong nhiều cột (ví dụ # 2):
Ở đây, công thức đánh giá sự thay đổi trạng thái trong hai cột, tức là trong các cột C và D.
Kết quả tương tự như trên, trừ nhân viên Ben.
Vui lòng xem cột cuối cùng cho quốc gia.
Quan trọng:
Trước tiên và quan trọng nhất, hãy chắc chắn rằng bạn đã sắp xếp dữ liệu theo tên (cột B) và sau đó theo ngày (cột A) theo thứ tự tăng dần.
Điều này áp dụng cho cả ví dụ về thay đổi trạng thái trong một cột và nhiều cột.
Lọc các hàng thay đổi trạng thái cuối cùng trong một cột
Hãy để tôi giới thiệu các bước trước để bạn có thể hiểu công thức sau đó.
Chúng ta sẽ bắt đầu với ví dụ số 1, trong đó chỉ có một cột. Sau đó, việc bao gồm nhiều cột sẽ dễ dàng hơn cho chúng ta.
Hãy nhập dữ liệu mẫu vào ô A1: C17 theo ảnh chụp màn hình số 1 hoặc cuộn xuống cuối bài hướng dẫn này và sao chép nó từ bảng mẫu của tôi.
Khi hoàn thành, bạn có thể tập trung vào các bước dưới đây để hiểu cách lọc các hàng thay đổi trạng thái cuối cùng trong Google Sheets.
Kết hợp các phạm vi (Một điểm quan trọng)
Như tôi đã đề cập, Sortn sẽ là công thức chính ngoài Filter.
Trong Sortn, chúng ta có thể cần kết hợp các phạm vi cột.
Thường, để kết hợp phạm vi cột B2: B và C2: C, chúng ta sẽ sử dụng B2: B & C2: C (trong ArrayFormula).
Dấu và ở giữa sẽ làm nhiệm vụ.
Phương pháp thay thế khác để kết hợp các phạm vi này là phương pháp kết hợp TRANSPOSE và QUERY, đó là transpose (query (transpose (B2: C),, 9 ^ 9)).
Chúng ta sẽ tuân theo phương pháp này trong công thức của mình, không phải là dấu và chủ yếu là sự linh hoạt và tăng cường hiệu suất.
Các bước để lọc các hàng thay đổi trạng thái cuối cùng
Bước 1 – Loại bỏ các bản sao dựa trên Tên và Trạng thái
Trong bước đầu tiên, chúng ta sẽ loại bỏ các hàng trùng lặp.
Chúng ta sẽ xem xét cột B và C để xác định các bản sao.
Hãy xem phần được làm nổi bật, tức là kết hợp các phạm vi cột, trong công thức và các giải thích công thức sau đó.
=sort(sortn({A2:C,row(A2:A)},9^9,2,transpose(query(transpose(B2:C),,9^9)),1),4,0,2,1)Hãy bắt đầu với SORTN.
Cú pháp: SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending])
- range – {A2:C, hàng (A2: A)} – thêm số hàng với phạm vi (làm cột cuối cùng).
- n – 9 ^ 9 (số lớn tùy ý)
- display_ties_mode – 2 (để loại bỏ các bản sao)
- sort_column – transpose (query (transpose (B2: C),, 9 ^ 9)) – kết hợp các phạm vi cột B2: B và C2: C.
Có bốn cột trong kết quả đầu ra. Vui lòng xem hình ảnh ở trên.
SORT ngoài cùng sắp xếp đầu ra theo thứ tự 4,0,2,1. Điều đó có nghĩa là cột 4 giảm dần và cột 2 tăng dần.
Mục đích của việc sắp xếp này là đưa các bản ghi mới nhất lên đầu bằng số hàng và sau đó theo tên.
Chúng ta đã hoàn thành bước đầu tiên để lọc các hàng thay đổi trạng thái cuối cùng trong Google Sheets.
Bước 2 – Loại bỏ các bản sao dựa trên Tên
Vui lòng xem ảnh chụp màn hình số 3 ở trên cho kết quả của Sortn.
Chúng ta sẽ sử dụng đó là phạm vi trong Sortn khác.
=array_constrain(sortn(sort(sortn({A2:C,row(A2:A)},9^9,2,transpose(query(transpose(B2:C),,9^9)),1),4,0,2,1),9^9,2,2,1),9^9,3)Lần này, công thức loại bỏ các bản sao dựa trên cột 2, tức là Tên.
Vì vậy, chúng ta sẽ có một bản ghi duy nhất từ mỗi nhóm.
Mục đích của Array_Constrain là loại bỏ cột cuối cùng, tức là số hàng.
Bước 3 – Xác định các hàng thay đổi trạng thái cuối cùng
Với các bước 1 và 2, chúng ta có một bảng bây giờ chứa một hàng duy nhất từ mỗi nhóm.
Lưu ý: Có bốn tên duy nhất trong phạm vi nguồn B2: B, điều đó có nghĩa là có bốn nhóm.
Nếu có sự thay đổi trạng thái trong một nhóm, chúng ta sẽ có hàng đó. Nếu không, chúng ta sẽ có một hàng duy nhất từ nhóm đó.
Chúng ta chỉ muốn các hàng thay đổi trạng thái. Làm thế nào để tìm được nó?
Trước tiên, hãy gán đầu ra trên cho các hàng tương ứng bằng VLOOKUP.
Cú pháp: VLOOKUP(search_key, range, index, [is_sorted])
Công thức chung: Vlookup (combined_column_range_A2: C, combined_column_range_of_step_2_oupu, 1,0)
Công thức:
=ArrayFormula(IFNA(vlookup(transpose(query(transpose(A2:C),,9^9)),transpose(query(transpose(array_constrain(sortn(sort(sortn({A2:C,row(A2:A)},9^9,2,transpose(query(transpose(B2:C),,9^9)),1),4,0,2,1),9^9,2,2,1),9^9,3)),,9^9)),1,0)))IFNA hỗ trợ Vlookup để loại bỏ lỗi N/A (không có sẵn).
Vui lòng tham khảo cột Q.
Với sự giúp đỡ của ISTEXT, hãy chuyển các giá trị đã được phân bổ thành 1 và tất cả các ô trống thành 0 (không).
Vui lòng tham khảo cột R trong hình ảnh trên. Tôi có công thức sau đây trong R2.
=ArrayFormula(-ISTEXT(IFNA(vlookup(transpose(query(transpose(A2:C),,9^9)),transpose(query(transpose(array_constrain(sortn(sort(sortn({A2:C,row(A2:A)},9^9,2,transpose(query(transpose(B2:C),,9^9)),1),4,0,2,1),9^9,2,2,1),9^9,3)),,9^9)),1,0))))Trong S2, tôi đã chèn công thức mảng đếm chạy sau để trả về chuỗi chạy của các tên trong B2: B.
=ArrayFormula(countifs(row(A2:A),"<="&row(A2:A),B2:B,B2:B))Điều này sẽ giúp chúng ta tìm các hàng thay đổi trạng thái cuối cùng. Bạn có thể tìm thấy chi tiết bên dưới.
Bước cuối cùng – Lọc các hàng thay đổi trạng thái cuối cùng trong Google Sheets
Trước tiên, hãy sử dụng công thức FILTER dưới đây.
=filter(A2:C,R2:R*S2:S>1)Thay thế R2: R và S2: S bằng các công thức tương ứng ở trên.
Lưu ý: Khi làm như vậy, tôi đã bỏ qua các hàm Array_Formula vì không cần thiết trong Filter.
=filter(A2:C,-istext(IFNA(vlookup(transpose(query(transpose(A2:C),,9^9)),transpose(query(transpose(array_constrain(sortn(sort(sortn({A2:C,row(A2:A)},9^9,2,transpose(query(transpose(B2:C),,9^9)),1),4,0,2,1),9^9,2,2,1),9^9,3)),,9^9)),1,0)))*countifs(row(A2:A),"<="&row(A2:A),B2:B,B2:B)>1)Cách này, chúng ta có thể lọc các hàng thay đổi trạng thái cuối cùng. Tôi đã sử dụng công thức này trong ô E2 (vui lòng cuộn lên và tham khảo ảnh chụp màn hình số 1).
Vui lòng tham khảo dữ liệu mẫu trong ảnh chụp màn hình số 2.
Trong ví dụ đầu tiên, dữ liệu ở A1:C17. Ở đây, nó là A1:D17.
Chúng ta muốn tìm các thay đổi trạng thái trong các cột C và D.
Bạn chỉ cần thay đổi nhỏ trong công thức E2 ở trên để có được điều này. Điều chỉnh nào?
- Thay đổi A2:C thành A2:D.
- B2:C trở thành B2:D.
- Sắp xếp cột 4 trở thành 5 (đậm và được làm nổi bật màu hồng nhạt trong công thức dưới đây).
- Giới hạn 4 cột thay vì 3 (đậm và được làm nổi bật màu xanh lá cây nhạt trong công thức dưới đây).
=filter(A2:D,-istext(IFNA(vlookup(transpose(query(transpose(A2:D),,9^9)),transpose(query(transpose(array_constrain(sortn(sort(sortn({A2:D,row(A2:A)},9^9,2,transpose(query(transpose(B2:D),,9^9)),1),5,0,2,1),9^9,2,2,1),9^9,4)),,9^9)),1,0)))*countifs(row(A2:A),"<="&row(A2:A),B2:B,B2:B)>1)Đây là công thức G2 theo ảnh chụp màn hình số 2 ở trên.
Bây giờ tôi đoán bạn đã biết những thay đổi mà bạn cần phải thực hiện để bao gồm các cột trạng thái khác. Nếu không, hãy đặt câu hỏi trong phần bình luận.
Cuối cùng, chúng ta có thể giải quyết hai vấn đề trên bằng cách sử dụng logic khác nhau!
Bạn có thể tìm thấy những giải pháp đó trong các ô ‘test 1’! I2 và ô ‘test 2’! L2 trong bảng mẫu của tôi dưới đây.
Tôi sẽ giải thích những công thức đó trong một bài hướng dẫn sau. Đó là tất cả. Cảm ơn đã lưu lại. Thưởng thức!
