Bạn muốn lọc ngày tháng nằm trong quý hiện tại hoặc quý trước trong Google Sheets để tóm tắt báo cáo doanh số bán hàng.
Làm sao để làm điều này trong Google Sheets?
Nếu đây là điều bạn muốn hỏi, tôi có giải pháp cho bạn.
Loại bỏ việc lọc dữ liệu này có thể đơn giản nếu bạn tuân thủ năm dương lịch (từ ngày 1 tháng 1 đến ngày 31 tháng 12) vì chúng ta có thể sử dụng hàm QUARTER() trong QUERY.
Nhưng nếu bạn tuân theo năm tài chính, ví dụ từ ngày 1 tháng 4 đến ngày 31 tháng 3, hàm QUARTER() trong QUERY sẽ không giúp bạn.
Vì vậy, giải pháp để lọc quý hiện tại hoặc quý trước là tạo một cột quý động được điền bằng số quý dựa trên năm tài chính bạn chọn.
Trước khi tiếp tục với công thức động có thể hoạt động bất kể năm dương lịch hay năm tài chính, dưới đây là các công thức Query để lọc quý hiện tại hoặc quý trước trong năm dương lịch trong Google Sheets.
Query để lọc ngày tháng thuộc Quý Hiện Tại trong Google Sheets (Năm Dương Lịch)
Dữ liệu mẫu của tôi nằm trong phạm vi A1:C.
Trong đó, các ô A1, B1 và C1 chứa tên cột “Ngày”, “Sản phẩm” và “Số lượng” tương ứng, và các dữ liệu tương ứng dưới đó.
=query(A1:C,"Select A,B,C where quarter(A)=quarter(now()) and year(A)=year(now())",1)
Chúng ta có thể chèn công thức QUERY trên vào hàng đầu tiên của bất kỳ cột trống nào để lấy kết quả ba cột.
Trước tiên, hãy làm trống cột E1:G, nếu bạn sẽ chèn vào ô E1.
Lưu ý:
- Quý hiện tại trong kết quả trên được tính toán dựa trên ngày hệ thống của tôi khi viết bài này.
- Nếu bạn đang đọc hướng dẫn này vào một ngày trong tương lai, bạn có thể nhận được kết quả khác dựa trên quý hiện tại vào ngày đó.
Chúng ta có thể sử dụng hàm QUERY như trên để lọc dữ liệu trong năm dương lịch cho quý hiện tại trong Google Sheets.
Mục tiêu cuối cùng có lẽ là tóm tắt dữ liệu bán hàng trên.
Nếu đúng vậy, hãy sử dụng kết quả trên như dữ liệu trong một QUERY khác.
Tóm tắt:
=query(query(A1:C,"Select A,B,C where quarter(A)=quarter(now()) and year(A)=year(now())",1),"Select Col2,sum(Col3) group by Col2",1)
Query để lọc ngày tháng thuộc Quý Trước trong Google Sheets (Năm Dương Lịch)
Ở đây, bạn nên thay thế quarter(now()) trong các công thức bằng quarter(now())-1.
Áp dụng cho cả hai công thức trên.
Tôi không bao gồm ảnh chụp màn hình, vì vào thời điểm viết bài này, ngày hôm nay thuộc quý đầu tiên của năm dương lịch.
Vì vậy, không có dữ liệu để lọc trong quý trước.
Hãy thực hiện theo hướng dẫn trên để lọc ngày tháng trong quý trước trong năm dương lịch trong Google Sheets.
Tính toán Quý Hiện Tại và Quý Trước – Công thức động
Trong báo cáo tài chính, nếu bạn không tuân theo năm dương lịch (từ ngày 1 tháng 1 đến ngày 31 tháng 12), thì đừng sử dụng các công thức trên.
Chúng ta nên tìm cách khác để lọc ngày tháng thuộc quý hiện tại hoặc quý trước trong Google Sheets.
Chuẩn bị Dữ liệu
Chúng ta cần tìm cách gán các quý một cách “động” dựa trên năm tài chính hiện thời trong công ty của chúng ta.
Bạn hiểu ý nghĩa của thuật ngữ “động” ở ngữ cảnh trên như thế nào?
Một cách không động, chúng ta trước tiên gán các quý dựa trên năm dương lịch. Sau đó, chuyển chúng sang năm tài chính, ví dụ, thay thế 1 (Q1) bằng 4 (Q4) nếu năm tài chính là từ tháng 4 đến tháng 3.)
Bạn có thể tìm thấy các chi tiết đó ở đây – Hàm Truy vấn Quý trong Dữ liệu Năm Tài Chính Không Theo Lịch Dương (Google Sheets).
Dưới đây là một giải pháp tốt hơn (động) mà chúng ta sẽ sử dụng.
Trong đó, bạn chỉ cần chỉ định tên tháng kết thúc năm tài chính (F1) và năm (G1). Công thức trong ô D1 sẽ lo chuyện còn lại.
=ArrayFormula({"Quarter";ifna(vlookup(eomonth(A2:A,0),{eomonth(EDATE(eomonth(edate(eomonth(date(G1,month(F1&1),1),0),-12),0)+1,SEQUENCE(12,1,0,1)),0),value(flatten(SUBSTITUTE(sequence(4,1)," ","",SEQUENCE(1,3))))},2,0))})
Chúng ta có thể sử dụng cột trợ giúp D để lọc dữ liệu dựa trên quý hiện tại hoặc quý trước trong Google Sheets.
Để có giải thích về công thức D1, vui lòng kiểm tra hướng dẫn này – Chuyển Đổi Ngày Thành Quý Tài Chính trong Google Sheets.
Nếu bạn nhập tháng 3 năm 2022 vào (F1:G1), năm tài chính sẽ là từ ngày 1 tháng 4 năm 2021 đến ngày 31 tháng 3 năm 2022.
Nếu bạn nhập tháng 12 năm 2022 vào (F1:G1), năm tài chính sẽ là năm dương lịch từ ngày 1 tháng 1 năm 2022 đến ngày 31 tháng 12 năm 2022.
Nhập bất kỳ tháng và năm nào vào ô F1 và G1. Công thức sẽ lo việc các quý trong cột D.
Hai Công thức Lọc
Quý Hiện Tại
Chúng ta đã có một cột quý. Làm thế nào để lọc phạm vi dữ liệu A1:C dựa trên quý hiện tại trong Google Sheets?
Ở đây, chúng ta sẽ sử dụng hàm FILTER thay vì QUERY.
=FILTER(A2:C,((D2:D>0)*(D2:D=vlookup(eomonth(today(),0),{eomonth(A2:A,0),D2:D},2,0))))
Công thức này hoạt động như thế nào?
Tôi đã sử dụng hai điều kiện trong FILTER.
Điều kiện 1 – D2:D>0
Điều kiện 2 – D2:D=vlookup(eomonth(today(),0),{eomonth(A2:A,0),D2:D},2,0)
Ở đây, VLOOKUP tìm kiếm ngày cuối tháng hiện tại trong A2:A (trong cột này, chuyển đổi tất cả các ngày thành ngày cuối tháng) và trả về số quý tương ứng từ cột D.
Đó sẽ là số quý của tháng hiện tại.
Tóm tắt:
Để tóm tắt doanh số bán hàng, bạn có thể sử dụng một Query khác như trước đây.
=query(filter(A2:C,((D2:D>0)*(D2:D=vlookup(eomonth(today(),0),{eomonth(A2:A,0),D2:D},2,0)))),"Select Col2,sum(Col3) group by Col2",0)
Ở đây có một thay đổi, đó là trong tiêu đề Query.
Gía trị 0 (không) ở đây vì kết quả bộ lọc không có hàng tiêu đề.
Vậy làm sao để lọc quý trước trong năm tài chính trong Google Sheets?
Quý Trước
Ở đây, bạn nên đặt -1 trong điều kiện hai như sau D2:D=vlookup(eomonth(today(),0),{eomonth(A2:A,0),D2:D},2,0)-1.
Thực hiện theo hướng dẫn trên để lọc ngày tháng trong quý trước trong năm tài chính.
Vậy là tất cả. Cảm ơn bạn đã đọc. Chúc bạn có những giây phút vui vẻ!
