Dùng công thức SORTN hoặc VLOOKUP, chúng ta có thể viết một công thức để trả về các hàng cuối tháng từ một bảng dữ liệu hàng ngày trong Google Sheets.
Nói cách khác, chúng ta có thể sử dụng hàm SORTN hoặc VLOOKUP trong Google Sheets để trả về các hàng chứa ngày cuối cùng của mỗi tháng từ một bảng dữ liệu.
Ở ví dụ dưới đây, dữ liệu mẫu trong phạm vi A1:B chứa các mục hàng ngày.
Kết quả dự kiến trong phạm vi D2:F chỉ chứa các hàng cuối tháng từ dữ liệu hàng ngày trong A1:B.
Nếu có nhiều mục hàng ngày rơi vào ngày cuối tháng, công thức chỉ trả về mục cuối cùng dựa trên vị trí hàng.
Hãy xem kết quả và dữ liệu mẫu để hiểu rõ hơn về điều này.
Dưới đây là các hướng dẫn từng bước để viết một công thức mảng trả về các hàng cuối tháng từ dữ liệu hàng ngày trong Google Sheets.
Sử dụng SORTN để Trả về Các Hàng Cuối Tháng từ Bảng Dữ Liệu Hàng Ngày
Trước khi bắt đầu, hãy đảm bảo rằng dữ liệu được sắp xếp theo thứ tự tuần tự dựa trên cột ngày.
Dưới đây là các bước để trả về ngày cuối cùng của mỗi tháng từ một bảng trong Google Sheets.
Chúng ta sẽ bắt đầu viết công thức cho một phạm vi đóng A2:B20, không phải A2:B. Điều này sẽ dễ học hơn. Sau đó, chúng ta có thể dễ dàng sửa đổi để áp dụng cho một phạm vi mở.
1. Thêm Cột Ngày và Năm với Dữ Liệu
Logic: Sử dụng SORTN, chúng ta sẽ loại bỏ các mục trùng lặp trong dữ liệu dựa trên một cột ngày cuối tháng. Vì vậy, chúng ta cần một cột như vậy với bảng hiện có. Công thức dưới đây thực hiện điều này.
Bước 1 – Công thức:
=ArrayFormula({eomonth(A2:A20,0),A2:B20})Chọn D2:D20, điều hướng đến menu Format > Number > Custom number format, và nhập mmmm-yyy vào trường.
Lưu ý: D2:D20 được sử dụng như một cột tháng và năm.
Giải thích công thức:
Hàm EOMONTH trả về một cột chứa các ngày cuối tháng.
Để kết hợp dữ liệu mẫu với cột này, tôi đã sử dụng Dấu Ngoặc Nửa Duỗi (Curly Brackets).
Tôi đã sử dụng ARRAYFORMULA vì hàm EOMONTH không phải là một hàm mảng trong công thức.
2. Sắp Xếp Theo Tháng và Năm (Cột Ngày Cuối Tháng) và Sau Đó Theo Số Hàng
Giả sử chúng ta sắp xếp lại kết quả trên bằng SORTN. Nếu như vậy, chúng ta sẽ có một bảng chứa mục đầu tiên của mỗi tháng.
Vì vậy, chúng ta cần sắp xếp dữ liệu này theo tháng và năm (cột ngày cuối tháng) tăng dần và sau đó theo số hàng từ 2 đến 20 giảm dần.
Với sự trợ giúp của các hàm SORT và ROW, chúng ta có thể đáp ứng yêu cầu của mình.
Công thức chung: Sort(Step_1_Formula,1,TRUE,row(A2:A20),FALSE)
Bước 2 – Công thức (dựa trên công thức chung trên):
=sort({eomonth(A2:A20,0),A2:B20},1,TRUE,row(A2:A20),FALSE)Lưu ý: Chúng ta có thể loại bỏ ARRAYFORMULA trong SORT. Vì vậy, bạn không thấy nó ở đây với Step_1_Formula.
Nó đặt các hàng cần trích xuất lên đầu của mỗi nhóm tháng. Bây giờ chúng ta có thể trích xuất các hàng cuối tháng từ dữ liệu hàng ngày.
Trong bước 3, bạn có thể tìm hiểu về việc sử dụng SORTN với bảng trên.
3. Sử dụng SORTN để Trích Xuất Các Hàng Cuối Tháng từ Dữ Liệu Hàng Ngày trong Google Sheets
Trong số bốn chế độ ràng buộc từ 0 đến 3, chúng ta có thể sử dụng chế độ ràng buộc 2 trong SORTN ở đây. Nó để loại bỏ các hàng trùng lặp dựa trên một cột được chọn.
Công thức chung: =sortn(step_2_formula,9^9,2,1,TRUE)
Giải thích công thức chung:
9^9 – Theo dữ liệu hiện tại, có 4 hàng trong kết quả.
Chúng ta có thể sử dụng 4 thay cho 9^9 trong công thức. Nhưng khi chúng ta không chắc chắn về số hàng trong kết quả, việc sử dụng một số cố định như 4 không an toàn.
Vì vậy, việc sử dụng 9^9 (một số ngẫu nhiên lớn) là an toàn để trả về tất cả các hàng trong kết quả.
2 – Chế độ ràng buộc để xóa các hàng trùng lặp.
1 – Vị trí của cột, dựa trên đó, chúng ta muốn loại bỏ các bảng duy nhất.
TRUE – Sắp xếp cột 1 theo thứ tự tăng dần.
Dưới đây là công thức dựa trên công thức chung bước 3 trên.
=sortn(sort({eomonth(A2:A20,0),A2:B20},1,TRUE,row(A2:A20),FALSE),9^9,2,1,TRUE)Nó sẽ trích xuất các hàng cuối tháng từ bảng dữ liệu hàng ngày trong Google Sheets. Vui lòng xem hình ảnh #1 ở trên để xem kết quả.
Kết quả sẽ có ba cột. Chúng là cột tháng và năm (ngày cuối tháng), cột ngày cuối tháng và cột số tiền.
Bạn có thể giới hạn số cột trong kết quả bằng cách sử dụng QUERY như dưới đây.
Công thức chung: =query(Final_Formula,”Select Col1,Col2,Col3″)
Trong mệnh đề SELECT, chỉ bao gồm các cột cần thiết. Ví dụ, sử dụng công thức dưới đây để trả về cột thứ 2 và thứ 3.
=query(sortn(sort({eomonth(A2:A20,0),A2:B20},1,TRUE,row(A2:A20),FALSE),9^9,2,1,TRUE),"Select Col2,Col3")Làm Thế Nào Để Áp Dụng Công Thức cho Phạm Vi Mở?
Có hai bước.
-
Làm tất cả các phạm vi trở thành mở trong Final_formula.
-
Thay thế eomonth(A2:A,0) bằng iferror(eomonth(datevalue(A2:A),0))
Vì vậy, công thức cho một phạm vi mở sẽ là:
=sortn(sort({iferror(eomonth(datevalue(A2:A),0)),A2:B},1,TRUE,row(A2:A),FALSE),9^9,2,1,TRUE)Lưu ý: Ở đây, bạn cũng có thể bọc công thức này bằng QUERY để chỉ chọn các cột bạn muốn.
Đây là một phương án thay thế cho công thức SORTN ở trên.
Mãi đến bước 2, không có thay đổi. Điều đó có nghĩa chúng ta cần công thức Step_2 ở đây.
Chúng ta sẽ sử dụng công thức Step_2 làm phạm vi VLOOKUP để trả về các hàng cuối tháng từ một bảng dữ liệu hàng ngày trong Google Sheets.
Hãy cho bạn biết về công thức chung trước.
=arrayformula(ifna(vlookup(search_keys,step_2_formula,3,0)))Chúng ta chỉ cần các khóa tìm kiếm để sử dụng trong VLOOKUP. Các khóa tìm kiếm chỉ đơn giản là các giá trị duy nhất của tháng và năm (ngày cuối tháng).
Khóa tìm kiếm:
=unique(ArrayFormula(iferror(eomonth(datevalue(A2:A),0))))Bây giờ hãy viết công thức theo công thức chung trên.
=arrayformula(ifna(vlookup(D2:D,sort({iferror(eomonth(datevalue(A2:A),0)),A2:B},1,TRUE,row(A2:A),FALSE),3,0)))Đó là tất cả.
Cảm ơn vì đã đọc. Chúc bạn vui vẻ!
