Chào các bạn! Bạn có một tập dữ liệu và hàng tiêu đề của nó chứa các ngày như nhãn trường (tên cột). Dưới hàng tiêu đề đó, bạn có một số giá trị. Trong trường hợp này, làm thế nào để tổng số của 7 ngày gần đây, 30 ngày gần đây, 60 ngày gần đây hoặc n ngày gần đây từ ngày hôm nay trong mỗi hàng trong Google Sheets.
Nếu bạn quen thuộc với các hàm SUMIFS và TODAY, bạn có thể tính tổng số của n ngày gần đây trong mỗi hàng trong Google Sheets.
Khi bạn sử dụng một công thức như vậy để tính tổng số của 7 ngày gần đây, 30 ngày gần đây và 60 ngày gần đây, bạn có thể cần sao chép nó xuống dòng vì công thức sẽ không được mở rộng.
Hãy nhanh chóng xem một ví dụ để hiểu dữ liệu và kết quả dự kiến của tôi.
Công thức kéo xuống để tính tổng 7, 30 và 60 ngày gần đây từ hôm nay
Công thức SUMIFS sau đây trong ô A3 tính tổng số 7 ngày gần đây cho hàng 3, tức là phạm vi D3:3. Như tôi đã đề cập ở đoạn văn đầu tiên, các ngày nằm trong D2:D, tức là hàng tiêu đề.
=sumifs(D3:3,$D$2:$2,">"&today()-7,$D$2:$2,"<="&today())
Chú ý: Để điền các ngày trong khoảng D2:M2 phù hợp để kiểm tra, bạn có thể sử dụng công thức =sequence(1,10,today()-9) trong ô D2 (D2:M2 rỗng đầu tiên). Sau đó, chọn D2:M2 và định dạng (menu) nó thành Number > Date.
Công thức SUMIFS này yêu cầu một hành động thủ công để điền kết quả vào các hàng phía dưới. Điều đó có nghĩa là bạn nên kéo xử lý điền trong ô A2 xuống.
Bạn có thể sử dụng cùng một công thức SUMIFS để trả về tổng số của 7, 30 và 60 ngày gần đây tính từ hôm nay trong Google Sheets. Làm thế nào?
Tôi đã sử dụng -7 trong công thức để trả về tổng số 7 ngày gần đây tính từ hôm nay. Hãy thay thế con số đó bằng -30, -60 hoặc -90 tương ứng với yêu cầu của bạn.
SUMIFS không phải là công thức duy nhất mà chúng ta có thể sử dụng trong Google Sheets cho mục đích đã nêu. Chúng ta cũng có thể sử dụng combo SUM và FILTER như dưới đây.
Combo SUM + FILTER dưới đây áp dụng cho ô A2. Đây lại là một công thức kéo xuống.
=sum(filter(D3:3,$D$2:$2>today()-7,$D$2:$2<=today()))
Nhưng một trong những rào cản mà bạn có thể gặp trong tình huống trên là mở rộng tự động công thức từ một hàng sang các hàng phía dưới. Tôi cũng có một giải pháp cho vấn đề đó.
Bạn có thể thích: Lọc Dữ liệu Dựa trên Tuần Này, Tuần Trước, 30 Ngày Trước Trong Google Sheets.
Công thức mảng để lấy tổng số của 7, 30 và 60 ngày gần đây trong mỗi hàng trong Google Sheets
Một số công thức Google Sheets sẽ không mở rộng. SUMIFS là một trong số đó. Combo FILTER và SUM ở trên là một ví dụ khác về điều này.
Vậy cái gì là các lựa chọn có sẵn trước mắt chúng ta để tính tổng các giá trị của 7/30/60/90 ngày gần đây trong mỗi hàng trong Google Sheets?
Các lựa chọn mà tôi nghĩ tới là MMULT và DSUM. Ở đây, chúng ta sẽ sử dụng cái thứ hai.
Nhưng điều đó chưa đủ. Với DSUM, chúng ta có thể cần sử dụng một số hàm khác nữa. Đây là công thức mạnh mẽ đó!
Làm trống phạm vi A3:A và chèn DSUM sau đây của tôi vào ô A3.
=ArrayFormula(dsum(transpose({C2:C7,filter({column(D2:M2)*0;D3:M7},D2:M2>today()-7,D2:M2<=today())}),sequence(rows(C3:C7),1,2),{"Item";0}))
Sử dụng công thức mảng trên, bạn có thể lấy tổng số của 7, 30, 60 và 90 ngày gần đây trong mỗi hàng trong Google Sheets. Bạn chỉ cần thay đổi -7 thành số ngày yêu cầu.
Công thức trên chỉ bao gồm một số cột cụ thể. Trong phần cuối của bài hướng dẫn này, tôi sẽ giúp bạn mở rộng nó cho cả cột trong bảng tính.
Cách thức công thức hoạt động và giải thích
Cú pháp: DSUM(database, field, criteria)
Trong DSUM ở trên, chúng ta đã sử dụng các công thức kết hợp (biểu thức) như cơ sở dữ liệu, trường và tiêu chí.
Cơ sở dữ liệu (Phạm vi lọc chuyển vị)
FILTER sau đây trong công thức trên lọc các cột rơi vào phạm vi ngày cuối cùng 7, 30, 60, 90 hoặc n ngày mà bạn chỉ định. Nó (một phần) hoạt động như cơ sở dữ liệu trong DSUM.
={C2:C7,filter({column(D2:M2)*0;D3:M7},D2:M2>today()-7,D2:M2<=today())}
Công thức lọc phạm vi D3:M7 nằm trong khoảng ngày đã cho.
Công thức column(D2:M2)*0 (được đánh dấu ở màu xanh nước biển) chèn một hàng trên kết quả chứa giá trị 0. Ngoài ra, thêm phạm vi cột C2:C7 (được đánh dấu ở màu vàng).
Hàng và cột được thêm ở trên cần thiết trong DSUM để sử dụng như đối số hàm trường và tiêu chí. Bạn sẽ hiểu rõ hơn sau.
Chuyển vị kết quả đã lọc ở trên, và đó là cơ sở dữ liệu (đúng) để sử dụng trong DSUM.
=transpose({C2:C7,filter({column(D2:M2)*0;D3:M7},D2:M2>today()-7,D2:M2<=today())})
Cơ sở dữ liệu:
Logic:
Chúng ta đã chuyển vị dữ liệu đã lọc vì DSUM chỉ có khả năng trả về tổng cột dưới dạng mảng. Chúng ta muốn tổng hàng dưới dạng mảng.
Vì vậy, để tính tổng số của 7, 60 và 90 ngày gần đây trong mỗi hàng, chúng ta đã chuyển vị dữ liệu đã lọc. Sau đó chúng ta sẽ tính tổng cột, điều đó sẽ bằng tổng theo hàng.
Trong hướng dẫn của tôi “Cách Sử dụng Hàm Sumif theo Chiều Ngang trong Google Sheets”, tôi đã thảo luận về cùng vấn đề dưới tiêu đề phụ “Công thức Mảng Thay thế (Công thức DSUM)”.
Trường (Số thứ tự tuần tự)
Nếu bạn kiểm tra cơ sở dữ liệu ở trên, bạn có thể hiểu rằng chúng ta cần tổng của các cột “A”, “B”, “C”, “D” và “E”, và đây là các cột từ cột thứ 2 đến 6 trong cơ sở dữ liệu.
Vì vậy, trường mà chúng ta nên sử dụng là mảng ={2;3;4;5;6}. Dưới đây là công thức SEQUENCE thực hiện phần đó.
=SEQUENCE(rows(C3:C7),1,2)
Tiêu chí (Nhãn trường và Điều kiện)
Trong DSUM hoặc các hàm cơ sở dữ liệu tương tự khác, cột đầu tiên sẽ là cột tiêu chí.
Dựa trên cơ sở dữ liệu của chúng ta, nhãn trường của cột tiêu chí là “Item”, và tiêu chí là 0 vì không có giá trị khác trong cột đó.
{"Item";0}
Tôi hy vọng rằng bạn có thể hiểu cách công thức tính tổng số của 7, 30 và 60 ngày gần đây trong mỗi hàng trong Google Sheets.
Tổng số của N ngày gần đây trong mỗi hàng từ hôm nay và các cột vô hạn
Mục đích của công thức là tổng số các ngày gần đây 7, 30, 60, 90 dựa trên các ngày ngang.
Cho nên, có thể bạn sẽ có thêm ngày trong hàng tiêu đề trong tương lai mà sẽ mở rộng phạm vi. Hiện tại, chỉ lên tới cột M.
Chúng ta có thể thay đổi công thức cẩn thận để bao gồm cột và hàng vô hạn lên đến 1000 như sau.
=ArrayFormula(dsum(transpose({C2:C1000,filter({column(D2:2)*0;D3:1000},D2:2>today()-7,D2:2<=today())}),sequence(rows(C3:C1000),1,2),{"Item";0}))
Dưới đây là những thay đổi mà tôi đã thực hiện.
- C2:C7 trở thành C2:C1000.
- D2:M2 trở thành D2:2.
- D3:M7 trở thành D3:1000.
Công thức trên sẽ để lại các giá trị 0 trong các hàng trống xuống cột A như một lần thử. Để tránh điều đó, chúng ta có thể bao gồm một kiểm tra IF như sau.
=ArrayFormula(if(C3:C="",,dsum(transpose({C2:C1000,filter({column(D2:2)*0;D3:1000},D2:2>today()-7,D2:2<=today())}),sequence(rows(C3:C1000),1,2),{"Item";0})))
Đó là tất cả. Cám ơn đã đọc. Chúc bạn vui vẻ!
Sample_Sheet_300521
