Bạn có biết rằng, sử dụng chức năng Query trong Google Sheets, chúng ta có thể trích xuất tất cả các hàng từ tháng trước không? Điều đó có nghĩa là, không cần biết tháng hiện tại là tháng mấy, bạn vẫn có thể lọc dữ liệu theo tháng và trích xuất các bản ghi từ tháng trước. Thật tuyệt vời phải không nào?
Vấn đề khi làm việc với tháng trước
Giả sử tháng hiện tại là tháng 12 năm 2019. Bạn muốn lấy tất cả các hàng từ tháng trước, tức là tháng 11 năm 2019. Vậy làm thế nào để làm điều đó?
Sử dụng chức năng Query trong Google Sheets
Query trong Google Sheets sẽ giúp bạn làm việc đó một cách dễ dàng. Dựa vào các hàm như month(), year() và now(), bạn có thể lọc dữ liệu theo tháng và trích xuất các bản ghi từ tháng trước.
Query để lấy các hàng từ tháng trước
Đây là công thức đơn giản nhất để lấy tất cả các hàng từ tháng trước (các hàng chứa các ngày trong tháng trước) sử dụng chức năng Query trong Google Sheets:
=query(SOA!A1:F,"Select * where month(B)=month(now())-1",1)Dưới đây là giải thích về cú pháp của công thức trên:
Select *trả về tất cả các cột. Nếu bạn chỉ muốn lấy 1-2 cột nào đó, bạn có thể sử dụng cú phápSelect A,Eđể lấy cột INV # và BALANCE chẳng hạn.- Để lấy tháng trước trong Query, bạn có thể sử dụng cú pháp
month(B)=month(now())-1trong mệnh đề WHERE. - Con số 1 ở phần cuối là để trả về hàng tiêu đề kèm với kết quả. Nếu bạn không muốn hàng tiêu đề, bạn có thể thay đổi phạm vi thành SOA!A2:F và đặt 1 thành 0.
Đã giải quyết vấn đề của tháng 1
Trong trường hợp tháng hiện tại là tháng 1, công thức trên không hoạt động. Vậy làm sao để xử lý vấn đề trích xuất các hàng từ tháng trước khi tháng hiện tại là tháng 1?
Query để lấy các hàng từ tháng trước và năm hiện tại
Đối với các hàng chứa cùng tháng nhưng khác năm trong dữ liệu lớn, chúng ta cần bổ sung thêm hàm year() vào công thức Query để giải quyết vấn đề trên. Thường ta nghĩ rằng ta có thể sử dụng công thức sau đây:
=query(SOA!A1:F,"Select * where month(B)=month(now())-1 and year(B)=year(now())",1)Nhưng nếu tháng hiện tại là tháng 1, thì công thức trên sẽ trả về các kết quả không chính xác. Bạn có thể giải thích lí do tại sao không? Hãy đọc phần dưới đây.
Google Sheets Query để trích xuất tất cả các hàng từ tháng trước khi tháng hiện tại là tháng 1
Nếu tháng hiện tại là tháng 1, phần year(B)=year(now()) sẽ không khớp. Thay vào đó, nó phải là year(B)=year(now())-1.
Trong tất cả các tháng khác, chúng ta có thể sử dụng so sánh trên, tức là year(B)=year(now()).
Tương tự, phần month(now())-1 cũng không hoạt động.
Vậy làm thế nào để giải quyết vấn đề về việc lọc theo tháng và năm của tháng và năm trước một cách linh hoạt?
Sử dụng lệnh IF trong công thức Query của Google Sheets
Chúng ta có thể sử dụng câu lệnh IF trong công thức Query để giải quyết vấn đề trên. Dưới đây là công thức IF mà chúng ta có thể sử dụng trong công thức Query:
=if(month(today())<>1,"month(B)=month(now())-1 and year(B)=year(now())","month(B)=11 and year(B)=year(now())-1")Chúng ta có thể sử dụng câu lệnh IF trên trong công thức Query như sau:
=query(SOA!A1:F,"Select * where "&if(month(today())<>1,"month(B)=month(now())-1 and year(B)=year(now())","month(B)=11 and year(B)=year(now())-1"),1)Với công thức trên, chúng ta có thể trích xuất tất cả các hàng từ tháng trước một cách linh hoạt.
Tính tổng các bản ghi tháng trước
Đây chỉ là một gợi ý bổ sung.
Giả sử bạn muốn tính tổng số tiền ghi có trong cột credit (cột D trong SOA).
Công thức sau đây sẽ giúp bạn tính tổng số tiền ghi có trong tháng trước với sự tính toán theo tháng và năm:
=sum(query(SOA!A1:F,"Select D where "&if(month(today())<>1,"month(B)=month(now())-1 and year(B)=year(now())","month(B)=11 and year(B)=year(now())-1"),1))Hoặc bạn có thể sử dụng công thức sau đây:
=sum(query(SOA!A1:F,"Select D where month(B)="&month(eomonth(today(),-1))-1&" and year(B)="&year(eomonth(today(),-1))))Quá đơn giản, phải không?
Đó là những bí quyết juiciest mà tôi chia sẻ với bạn về cách sử dụng Google Sheets Query để trích xuất tất cả các hàng từ tháng trước và thực hiện tính toán trên dữ liệu. Chúc bạn thành công trong việc áp dụng chúng vào công việc hàng ngày của mình!
Nếu bạn muốn biết thêm về cách sử dụng Google Sheets và các tính năng hữu ích khác, hãy ghé thăm website Crawlan.com.
