Tóm lược
Để tính toán dự báo so với phương sai thực tế dựa trên một tập hợp dữ liệu, bạn có thể sử dụng hàm SUMIFS để thu thập tổng và các công thức cơ bản khác để tính phương sai và tỷ lệ phần trăm phương sai. Trong ví dụ được hiển thị, công thức trong G5 là:
=SUMIFS(amount,type,G$4,group,$F5)
trong đó số tiền là dải ô được đặt tên C5: C14 và loại là dải ô được đặt tên D5: D14 và nhóm là dải ô được đặt tên B5: B14.
Giải trình
Đây là cách sử dụng khá chuẩn của hàm SUMIFS. Trong trường hợp này, chúng ta cần tính tổng số tiền dựa trên hai tiêu chí: loại (dự báo hoặc thực tế) và nhóm. Để tính tổng theo loại, cặp phạm vi / tiêu chí là:
type,G$4
trong đó loại là phạm vi được đặt tên D5: D14 và G4 là tham chiếu hỗn hợp với hàng bị khóa để khớp với tiêu đề cột trong hàng 4 khi công thức được sao chép xuống.
Để tổng hợp theo nhóm, cặp phạm vi / tiêu chí là:
group,$F5
trong đó nhóm là phạm vi được đặt tên B5: B14 và F5 là tham chiếu hỗn hợp với cột bị khóa để khớp với tên nhóm trong cột F khi công thức được sao chép qua.
Công thức phương sai
Công thức phương sai trong cột tôi chỉ trừ dự báo từ thực tế:
=G5-H5
Công thức phần trăm phương sai trong cột J là:
=(G5-H5)/H5
với định dạng số phần trăm được áp dụng.
Ghi chú
- Dữ liệu được hiển thị ở đây sẽ hoạt động tốt trong Bảng Excel, Bảng này sẽ tự động mở rộng để bao gồm dữ liệu mới. Chúng tôi đang sử dụng các phạm vi được đặt tên ở đây để giữ các công thức đơn giản nhất có thể.
- Bảng tổng hợp cũng có thể được sử dụng để tính toán phương sai. Các công thức cung cấp tính linh hoạt và khả năng kiểm soát cao hơn với chi phí phức tạp hơn.
