Công thức truy vấn để lọc điều kiện cuối cùng n hàng trong Google Sheets
Theo sử dụng công thức Query, chúng ta có thể lọc điều kiện cuối cùng n hàng trong một tập dữ liệu (dải) trong Google Sheets. Với mục đích này, chúng ta có thể sử dụng các mệnh đề WHERE và OFFSET trong công thức Query trong Sheets.
Mệnh đề WHERE cho phép chúng ta áp dụng điều kiện cần thiết để lọc dữ liệu. Sau đó, với sự giúp đỡ của mệnh đề OFFSET, chúng ta có thể lùi một số hàng từ phía trước để lấy ra n hàng cuối cùng.
Thực tế, mục đích của OFFSET trong Query là lùi n hàng. Nhưng chúng ta có thể sử dụng mệnh đề này một cách linh hoạt để lọc ra n hàng cuối cùng. Đúng vậy! Đó là mẹo mà tôi sẽ chia sẻ với bạn trong bài viết này.
Trước khi đến với công thức, hãy để tôi cho bạn biết nơi mà kiểu lọc này được sử dụng. Tôi có nghĩa là ứng dụng thực tế của việc lọc điều kiện cuối cùng n hàng trong một bộ dữ liệu trong Google Sheets.
Hãy giả sử bạn có một danh sách các đội và điểm số của họ trong 10 giải đấu gần đây. Để tìm tổng, trung bình, giá trị lớn nhất và giá trị nhỏ nhất của n kết quả cuối cùng (điểm số) của một đội, bạn có thể sử dụng công thức lọc n hàng cuối cùng của tôi, cũng như có điều kiện.
Sử dụng công thức Query để áp dụng điều kiện lọc cuối cùng n hàng trong Google Sheets
Như thường lệ, hãy bắt đầu với bảng dữ liệu mẫu. Chỉ cần gõ các giá trị trên vào cột B và C vào bảng của bạn.
Sau đó, gõ điều kiện/mức tiêu chí lọc vào ô E2 và n hàng cuối cùng mà bạn muốn lọc vào ô F2.
Xong? Sau đó, hãy bắt đầu tìm hiểu cách lọc điều kiện cuối cùng n hàng trong Google Sheets.
Áp dụng điều kiện sử dụng WHERE trong Query
Việc lọc điều kiện chỉ là một trò chơi trẻ em nếu bạn biết cách sử dụng Query. Để nắm bắt điều này, tôi đề nghị bạn đọc hướng dẫn sau đây khi bạn có thời gian rảnh – Examples to the Use of Literals in Query in Google Sheets.
Hãy tìm thời gian để tìm hiểu AND, OR, NOT trong Query, Substring Match Using Contains, Like, Matches và Date Criteria Use in Query.
Ở đây, nhiệm vụ của chúng ta đơn giản chỉ là lọc dữ liệu trong dải B2:C, nếu tên trong B2:B trùng khớp với tiêu chí trong ô E2, tức là “John”.
Dưới đây là công thức cần thiết cho điều đó.
Tiêu chí trong công thức; =query(B2:C,”Chọn where B=’John'”)
Tiêu chí là tham chiếu ô; =query(B2:C,”Chọn where B='”&E2&”‘”)
Kết quả:
COUNTIF trong Query OFFSET để Lọc Cuối cùng n hàng trong Google Sheets
Chúng ta đã hoàn thành phần đầu tiên, tức là lọc một tập dữ liệu theo điều kiện. Bây giờ chúng ta muốn lọc n hàng cuối cùng từ đầu ra này.
Không có mệnh đề nào trong cú pháp Query để lọc hoặc lọc điều kiện cuối cùng n hàng trong Google Sheets. Chúng ta cần viết một công thức cho điều đó.
Chúng ta có thể sử dụng mệnh đề OFFSET theo một cách nhất định để làm điều này. Mục đích của mệnh đề này là lùi một số hàng nhất định cho dù dữ liệu được lọc hay không.
Để lọc điều kiện cuối cùng n hàng, chúng ta có thể sử dụng logic dưới đây.
=countif(B2:B,E2)
Kết quả: 5
COUNTIF ở trên sẽ trả về số lượng điều kiện, có nghĩa là số hàng chứa tiêu chí “John” trong cột B.
Để tìm số hàng để lùi, hãy sử dụng công thức trên – n.
=countif(B2:B,E2)-F2
Nếu giá trị trong F2 = 1, công thức Countif ở trên sẽ trả về 4. Điều đó có nghĩa là lùi 4 hàng và trả lại 1 hàng cuối cùng.
Câu hỏi tiếp theo là làm thế nào để kết nối Countif trên với Query. Đối với điều đó, chúng ta có thể tuân theo phương pháp dưới đây.
Chúng ta có thể sử dụng công thức Query ngay trên làm dữ liệu trong một Query khác và lùi hàng sử dụng công thức Countif ở trên.
Lọc điều kiện cuối cùng n hàng trong Google Sheets – Cú pháp
=query( Công thức Query, “Chọn * lùi “& Công thức Countif)
Vậy đây là công thức để lọc điều kiện cuối cùng n hàng trong Google Sheets.
=query(query(B2:C,”Chọn where B='”&E2&”‘”),”Chọn lùi “&countif(B2:B,E2)-F2)
Lỗi
Công thức Query trên sẽ trả về lỗi #VALUE! trong hai trường hợp.
-
Khi điều kiện lọc không có trong dải dữ liệu. Tức là nếu Query bên trong trả về #N/A!, công thức sẽ trả về lỗi như đã nêu.
-
‘n’ cuối cùng lớn hơn số hàng trong đầu ra đã lọc.
Công thức sẽ trả về lỗi #N/A!, nếu ‘n’ cuối cùng là 0 hoặc trống.
Đó là tất cả những gì cần biết về cách lọc điều kiện cuối cùng n hàng bằng công thức Query trong Google Sheets.
Tổng/Trung bình/Giá trị lớn nhất/Giá trị nhỏ nhất điều kiện cuối cùng n kết quả
Dưới đây là một số ứng dụng thực tế của kiểu lọc n hàng cuối cùng theo cách trên. Các ví dụ sau đây dựa trên dữ liệu đã cung cấp.
Làm thế nào để Tổng hợp n Giá trị Cuối cùng theo Điều kiện trong Google Sheets?
Công thức:
=SUM(query(query(B2:C,”Chọn * where B='”&E2&”‘”),”Chọn Col2 lùi “&countif(B2:B,E2)-F2))
Làm thế nào để Tìm Giá trị Lớn nhất/Giá trị nhỏ nhất Cuối cùng theo Điều kiện trong Google Sheets?
Tương tự như trên. Điểm khác biệt duy nhất ở đây là việc sử dụng MAX/MIN thay vì SUM.
Công thức:
=MAX(query(query(B2:C,”Chọn * where B='”&E2&”‘”),”Chọn Col2 lùi “&countif(B2:B,E2)-F2))
Làm thế nào để Tìm Giá trị Trung bình Cuối cùng theo Điều kiện trong Google Sheets?
Đối với trung bình có điều kiện của n giá trị cuối cùng, thay đổi MAX thành AVERAGE.
Công thức:
=AVERAGE(query(query(B2:C,”Chọn * where B='”&E2&”‘”),”Chọn Col2 lùi “&countif(B2:B,E2)-F2))
Tài nguyên bổ sung
- Tìm trung bình của n giá trị cuối cùng trong Google Sheets.
- Địa chỉ của ô cuối cùng không rỗng, bỏ qua các ô trống trong Excel (Sheets cũng vậy).
- Làm thế nào để tìm hàng cuối cùng trong mỗi nhóm trong Google Sheets.
- Làm thế nào để tìm giá trị cuối cùng khớp trong Google Sheets.
- Tra cứu hàng cuối cùng một phần trong một danh sách trong Google Sheets.
- Tra cứu để tìm sự xuất hiện cuối cùng của nhiều tiêu chí trong Google Sheets.
- Vlookup dữ liệu cuối cùng trong mỗi nhóm trong Google Sheets.
- Làm thế nào để tìm giá trị cuối cùng trong mỗi hàng trong Google Sheets.
- Tổng hợp chỉ hàng cuối cùng trong nhiều bảng trong Google Sheets.
- Tìm cột cuối cùng không rỗng trong một hàng trong Google Sheets.
- Đếm từ ô không rỗng đầu tiên đến ô không rỗng cuối cùng trong một hàng trong Google Sheets.
