Thông qua bài viết này, chúng ta sẽ tìm hiểu về quá trình thiết lập lại trung bình trượt khi danh mục thay đổi, sử dụng một công thức mảng trong Google Sheets.
Trung bình trượt là gì?
Trung bình trượt là trung bình di động dùng để tính toán xu hướng trong khoảng thời gian ngắn, ví dụ như một tuần, một tháng, mỗi ba tháng và v.v. Nó liên quan đến việc tạo ra một chuỗi trung bình trên các khung thời gian khác nhau trong một tập dữ liệu.
Trong bài hướng dẫn trước đó của tôi có tiêu đề “Tính trung bình N chu kỳ trượt trong Google Sheets”, tôi đã chia sẻ một công thức mảng. Ở đây, chúng ta sẽ sửa đổi công thức đó để tích hợp tính năng “thiết lập lại” và quá trình đó rất đơn giản.
Để bắt đầu việc thiết lập lại trung bình trượt, chúng ta cần có một tập dữ liệu mẫu trong Google Sheets. Vui lòng xem hình ảnh sau đây về dữ liệu mẫu và bạn cũng có thể sử dụng bảng mẫu của tôi bằng cách nhấp vào nút “Bảng mẫu” bên dưới.
Trong ví dụ trên, trong cột D, bạn có thể thấy rằng trung bình trượt được tính toán cho một khoảng thời gian 3 tháng và được thiết lập lại khi danh mục (vị trí) thay đổi trong cột B.
Công thức để thiết lập lại trung bình trượt trong Google Sheets
Đây là công thức mà tôi đã sử dụng trong ô D2 trong ví dụ trên:
=Công_thức_mảng(NẾU( SỐPHẢNỨNG(B2:B, B2:B, DÒNG(B2:B), "<="&DÒNG(B2:B)))<3,, TẠO_MẢNG(SỐHÀNG(C2:C), 1, hàm(m, n, NẾU_LỖI(TRUNG_BÌNH(CHOSE_DÒNG(C2:C, CHUỖISỐ(3, 1, m-3+1))))) ) ))
Làm thế nào để điều chỉnh khung thời gian cho trung bình trượt trong công thức trên?
Thay thế số 3, xuất hiện ba lần, bằng số mong muốn. Để có được trung bình trượt vào mỗi khung thời gian 4 tháng và cũng thiết lập lại khi đổi danh mục, thay tất cả các số 3 bằng số 4. Đó là tất cả.
Giải thích chi tiết công thức
Giải thích chi tiết về công thức này sẽ được trình bày sau phần giải thích về logic công thức dưới đây.
Logic trong việc thiết lập lại trung bình trượt trong Google Sheets
Để tính trung bình trượt 3 tháng cho mỗi vị trí, và trung bình trượt sẽ thiết lập lại khi khu vực thay đổi từ DXB thành ABD, làm theo ba bước chính sau:
- Lấy số lượng chạy của cột danh mục (vị trí), xác định khi trung bình trượt thiết lập lại.
- Tạo trung bình trượt bằng một công thức mảng.
- Áp dụng câu lệnh logic IF để thiết lập lại trung bình trượt dựa trên số lượng chạy.
Chúng ta sẽ kết hợp ba công thức này thành một và đây không phải là một nhiệm vụ phức tạp.
Logic của công thức ở đây là chúng ta sẽ loại bỏ trung bình trượt từ các hàng mà số lượng chạy trả về các số từ 1 đến 3, tương đương với việc thiết lập lại.
Nếu khung thời gian là 4 tháng, bạn nên loại bỏ các giá trị trong các hàng mà số lượng chạy trả về các số từ 1 đến 4.
Dưới đây là hướng dẫn từng bước để viết một công thức tạo trung bình trượt và thiết lập lại trung bình trượt theo danh mục trong Google Sheets.
Số lượng chạy của Vị trí
Để tạo ra số lượng chạy của các vị trí, nhập công thức mảng sau đây vào ô F2:
=Công_thức_mảng(SỐHÀNG(B2:B), 1, hàm(m, n, SỐPHẢNỨNG(B2:B, B2:B, DÒNG(B2:B), "<="&DÒNG(B2:B))))
Công thức này sẽ trả về các số tuần tự cho mỗi vị trí và số lượng sẽ bắt đầu lại khi danh mục (vị trí) thay đổi.
Cấu trúc công thức COUNTIFS
Công thức COUNTIFS có cú pháp như sau:
COUNTIFS(dải_điều_kiện1, điều_kiện1, [dải_điều_kiện2, ...], [điều_kiện2, ...])
Hàm COUNTIFS trong Google Sheets đếm số lượng ô trong một dải thỏa mãn nhiều điều kiện.
dải_điều_kiện1: B2:Bđiều_kiện1: B2:B
Nó kiểm tra mỗi ô trong cột B (từ B2 đến cuối cùng của cột) với chính nó.
Nếu bạn chỉ sử dụng =Công_thức_mảng(SỐPHẢNỨNG(B2:B, B2:B)), nó sẽ trả về số lượng các danh mục DXB và ABD trong mỗi hàng, tức là 12.
Để tạo ra số lượng chạy, cần có một tập hợp điều kiện thứ hai:
dải_điều_kiện2: DÒNG(B2:B)điều_kiện2: "<="&DÒNG(B2:B)
Đây là nơi mọi thứ thú vị xảy ra. Phần “<=”&DÒNG(B2:B) kiểm tra xem số hàng của mỗi ô có nhỏ hơn hoặc bằng số hàng của chính nó hay không.
Ràng buộc này đảm bảo bộ điều kiện đầu tiên trả về tổng số lượng cho đến hàng hiện tại, không phải đến hàng cuối cùng và đó là số lượng chạy.
Trung bình trượt của Số lượng
Thêm công thức mảng sau đây vào ô E2 để lấy trung bình trượt 3 tháng của số lượng trong cột C:
=TẠO_MẢNG(SỐHÀNG(C2:C), 1, hàm(m, n, NẾU_LỖI(TRUNG_BÌNH(CHOSE_DÒNG(C2:C, CHUỖISỐ(3, 1, m-3+1))))))
Tôi đã giải thích công thức đặc biệt này trong hướng dẫn trước đây của mình. Để tìm liên kết, vui lòng cuộn lên phần đầu nơi liên kết có sẵn trong đoạn văn thứ ba.
Dưới đây là giải thích ngắn gọn trước khi chúng tôi thiết lập lại các trung bình trượt này theo danh mục trong Google Sheets:
Hàm CHOSE_DÒNG trích xuất 3 hàng bắt đầu từ ô C2 và hàm TRUNG_BÌNH tính trung bình của các giá trị này.
=TRUNG_BÌNH(CHOSE_DÒNG(C2:C, {1,2,3}))
Chúng ta có thể thay thế {1,2,3} bằng SEQUENCE(3, 1, 1):
=TRUNG_BÌNH(CHOSE_DÒNG(C2:C, SEQUENCE(3, 1, <strong>1</strong>)))
Dãy số bắt đầu từ 1 và trả về 3 số, tức là {1, 2, 3}. Bằng cách thay đổi số cuối cùng 1 (đối số bắt đầu) trong SEQUENCE bằng 2, dãy số sẽ bắt đầu từ 2 và trả về 3 số, tức là {2, 3, 4}.
Để tự động hóa quá trình này, bạn có thể sử dụng một cách tiếp cận linh hoạt trong chính hàm SEQUENCE. Thay vì lập trình giá trị bắt đầu, chúng ta có thể sử dụng hàm MAKEARRAY.
Nói cách khác, MAKEARRAY sau đây trả về các số tuần tự -1, 0, 1, 2, 3, 4 cho n, lên đến ô cuối cùng trong bảng:
=TẠO_MẢNG(SỐHÀNG(C2:C), 1, hàm(m, n, m-3+1))
Trong công thức này, m-3+1 là công_thức_biểu_thức. Chúng tôi kết hợp công thức TRUNG_BÌNH đã đề cập ở trên và thay thế giá trị bắt đầu trong SEQUENCE bằng m-3+1.
Hàm CHOSE_DÒNG sẽ trả về các lỗi trong hai ô đầu tiên do -1 và 0 được trả về bởi MAKEARRAY. Hàm IFERROR được sử dụng để xử lý và loại bỏ các lỗi này.
Vai trò của câu lệnh IF trong việc thiết lập lại trung bình trượt trong Google Sheets
Chúng tôi sẽ sử dụng công thức chung sau đây để thiết lập lại trung bình trượt trong Google Sheets:
=Công_thức_mảng(NẾU(số_lượng_chạy_của_danh_mục,<3,, trung_bình_trượt))
Nó chủ yếu bao gồm một kiểm tra logic IF kết hợp số lượng chạy và trung bình trượt.
Dưới đây là công thức tương ứng:
=Công_thức_mảng(NẾU(SỐPHẢNỨNG(B2:B, B2:B, DÒNG(B2:B), "<="&DÒNG(B2:B))<3,,TẠO_MẢNG(SỐHÀNG(C2:C), 1, hàm(m, n, NẾU_LỖI(TRUNG_BÌNH(CHOSE_DÒNG(C2:C, CHUỖISỐ(3, 1, m-3+1))))))))
Trong đó:
số_lượng_chạy_của_danh_mục: SỐPHẢNỨNG(B2:B, B2:B, DÒNG(B2:B), "<="&DÒNG(B2:B))trung_bình_trượt: TẠO_MẢNG(SỐHÀNG(C2:C), 1, hàm(m, n, NẾU_LỖI(TRUNG_BÌNH(CHOSE_DÒNG(C2:C, CHUỖISỐ(3, 1, m-3+1))))))
Kết luận
Chúng tôi đã khám phá công thức để thiết lập lại trung bình trượt theo danh mục trong Google Sheets.
Bạn có thể cải thiện công thức nếu bạn có các danh mục và danh mục phụ trong hai cột bằng cách kết hợp chúng trong số lượng chạy.
Tôi tin rằng đây là một loại công thức mảng mà bạn có thể không quen thuộc. Vui lòng để lại phản hồi của bạn quý giá trong phần bình luận dưới đây.
Cảm ơn bạn đã theo dõi. Chúc bạn vui!
