Bạn có thể tìm dòng cuối cùng trong mỗi nhóm trong Google Sheets với nhiều mức sắp xếp. Tôi sử dụng các công thức SORT và SORTN như một sự kết hợp để trích xuất dòng cuối cùng của mỗi nhóm. Điều này hoạt động cho cả nhóm nhiều cột.
Chúng ta có thể xác định nhóm từ một cột các giá trị giống nhau. Hãy xem ví dụ dữ liệu dưới đây và chú ý đến cột B.
Trong dữ liệu mẫu này, Apple, Mango và Orange trong cột B đại diện cho ba nhóm.
Dòng cuối cùng trong mỗi nhóm của dữ liệu này là dòng số 5 cho Apple, dòng số 9 cho Mango và dòng số 14 cho Orange. Tôi chỉ muốn trích xuất những dòng này ra khỏi tập dữ liệu.
Tôi có một công thức đơn giản SORT + SORTN để tìm dòng cuối cùng trong mỗi nhóm trong Google Sheets.
Ở dòng số 15, nếu bạn có một mục mới cho Apple, công thức sẽ coi mục này là mục cuối cùng, không phải dòng số 5.
Điều đó có nghĩa là các bản ghi trong nhóm có thể không cần thiết trong các dòng liền kề. Chúng có thể ở trong các dòng xa nhau.
Công thức để tìm dòng cuối cùng trong mỗi nhóm trong Google Sheets
Tôi đã sử dụng sự kết hợp của công thức SORT và SORTN dưới đây trong ô F2 để trích xuất dòng cuối cùng từ mỗi nhóm dữ liệu trong Google Sheets.
=SORTN(SORT(B2:D,ROW(B2:B),0),COUNTA(UNIQUE(B2:B)),2,1,TRUE)
Trước khi giải thích cách viết công thức như vậy, hãy cho tôi giải thích cách chỉnh sửa công thức này để đáp ứng các yêu cầu tập dữ liệu của bạn.
Bạn có thể muốn nhóm dữ liệu dựa trên bất kỳ cột nào khác, không phải cột B.
Trong công thức của tôi, B2:B chứa tên các loại trái cây. Đây là cột đầu tiên trong phạm vi và tôi muốn nhóm cột này.
Số 1 trong công thức chỉ số cột. Bạn phải thay đổi số này dựa trên số cột nhóm trong dải dữ liệu của bạn.
Ngoài ra, phạm vi cột nhóm B2:B xuất hiện trong công thức. Hãy thay thế nó bằng phạm vi cột nhóm của bạn.
Phù hợp với đó, tinh chỉnh công thức Google Sheets của tôi để trả về dòng nhập cuối cùng trong mỗi nhóm trong bộ dữ liệu của bạn.
Bạn có thể giải thích cho tôi làm thế nào công thức này trích xuất dòng cuối cùng trong mỗi nhóm?
Tại sao không?
Dưới đây là logic đằng sau công thức tìm dòng nhập cuối cùng trong mỗi nhóm trong Google Sheets.
Bước 1
Tôi có dữ liệu hiện có trong phạm vi B2:D (tổng cộng ba cột).
Chúng ta nên sắp xếp phạm vi này dựa trên số dòng. Và thứ tự sắp xếp trong hàm SORT nên là giảm dần. Điều đó sẽ đặt các mục mới nhất ở đầu mỗi nhóm.
Tôi giả sử các mục mới nhất nằm ở cuối trang, không phải ở đầu.
Cú pháp: SORT(range, sort_column, is_ascending, [sort_column2, …], [is_ascending2, …])
Công thức: SORT(B2:D,ROW(B2:B),0)
Kết quả sắp xếp có thể chứa một số dòng trống ở đầu. Điều này sẽ không gây vấn đề nào.
Bước 2 (Công thức cuối cùng)
Sau câu truy vấn, SORTN là một trong những hàm yêu thích của tôi trong Google Sheets.
Sử dụng hàm này, chúng ta có thể tạo ra một công thức kết hợp “giết chết” trong Google Sheets để loại bỏ các giá trị trùng lặp trong các phạm vi dữ liệu.
Công thức SORTN dưới đây trả về dòng nhập cuối cùng trong mỗi nhóm trong Google Sheets.
Cú pháp: SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending], [sort_column2, …], [is_ascending2, …])
Công thức: SORTN(SORT(B2:D,ROW(B2:B),0),COUNTA(UNIQUE(B2:B)),2,1,TRUE)
Trong đó;
n: COUNTA(UNIQUE(B2:B))
Theo dữ liệu mẫu của tôi, chúng ta có ba nhóm. Vì vậy, chúng ta có thể chỉ định số 3 thay vì COUNTA trên. Nhưng đó không phải là một cách linh hoạt.
COUNTA trên trả về số lượng giá trị duy nhất trong cột nhóm. Đó bằng số dòng chúng ta muốn trong kết quả.
display_ties_mode: 2
Sử dụng chế độ này để truy xuất n bản ghi đầu tiên trong một phạm vi cụ thể sau khi loại bỏ các bản ghi trùng lặp.
Chúng tôi đã áp dụng SORTN trong một phạm vi dữ liệu được sắp xếp theo thứ tự giảm dần dựa trên số dòng.
Vì vậy, kết quả của SORTN sẽ là dòng nhập cuối cùng trong mỗi nhóm.
sort_column: 1
Công thức loại bỏ các bản ghi trùng lặp dựa trên sort_column. Vì vậy, nó phải là chỉ số cột nhóm. Trong trường hợp của chúng tôi, nó là 1.
is_ascending: TRUE
Không tạo ra nhiều sự khác biệt vì nó chỉ dùng để sắp xếp cột nhóm. Bạn cũng có thể chỉ định FALSE.
Có thể nhóm dữ liệu dựa trên nhiều cột không?
Điều đó cũng có thể. Hãy nhóm dựa trên các cột B và D trong dữ liệu mẫu trên.
Sau đó, bạn có thể sử dụng công thức sau.
=LET( srtd, SORT(B2:D,ROW(B2:B),0), LET( result, SORTN(srtd,9^9,2,CHOOSECOLS(srtd,1)&CHOOSECOLS(srtd,3),1), FILTER(result,CHOOSECOLS(result,1)<>"") ) )
Lần này, tôi sẽ giải thích những thay đổi bạn nên thực hiện để điều chỉnh công thức trên, trả về dòng nhập cuối cùng trong mỗi nhóm trong một nhóm nhiều cột.
Phần sắp xếp, tức là SORT(B2:D,ROW(B2:B),0), giống như trước (Bước 1).
Tôi đang nhóm dữ liệu dựa trên cột 1 (B2:B) và cột 3 (D2:D). Đây là cách tôi đã chỉ định chúng trong công thức.
CHOOSECOLS(srtd,1) – xác định cột nhóm đầu tiên.
CHOOSECOLS(srtd,3) – xác định cột nhóm thứ hai.
Bạn có thể sửa đổi số 1 và 3 trong các công thức này theo chỉ mục cột nhóm trong dữ liệu của bạn.
Tips:
Mục đích của biến LET là gán tên cho việc sắp xếp (công thức Bước 1), tức là srtd, và CHOOSECOLS là chọn các cột cụ thể từ kết quả đã sắp xếp.
Related:
- Tìm mục nhập cuối cùng của mỗi mục từ Ngày trong Google Sheets.
- Trả về dòng kết thúc tháng từ dữ liệu hàng ngày trong Google Sheets.
