Ringkasan
Untuk mengira ramalan berbanding varians sebenar berdasarkan sekumpulan data, anda dapat menggunakan dapat menggunakan fungsi SUMIFS untuk mengumpulkan jumlah, dan formula asas lain untuk menghitung peratusan varians dan varians. Dalam contoh yang ditunjukkan, formula dalam G5 adalah:
=SUMIFS(amount,type,G$4,group,$F5)
di mana jumlahnya adalah julat bernama C5: C14, dan jenisnya adalah julat bernama D5: D14, dan kumpulan adalah julat bernama B5: B14.
Penjelasan
Ini adalah penggunaan fungsi SUMIFS yang cukup standard. Dalam kes ini, kita perlu menjumlahkan jumlah berdasarkan dua kriteria: jenis (ramalan atau sebenar) dan kumpulan. Ringkasnya mengikut jenis, pasangan julat / kriteria adalah:
type,G$4
di mana jenis adalah julat bernama D5: D14, dan G4 adalah rujukan campuran dengan baris terkunci agar sesuai dengan tajuk lajur di baris 4 ketika formula disalin ke bawah.
Ringkasnya mengikut kumpulan, pasangan julat / kriteria adalah:
group,$F5
di mana kumpulan adalah julat bernama B5: B14, dan F5 adalah rujukan campuran dengan lajur terkunci agar sesuai dengan nama kumpulan di lajur F ketika formula disalin di seberang.
Rumusan varians
Rumus varians dalam lajur I hanya mengurangkan ramalan dari yang sebenarnya:
=G5-H5
Formula peratusan varians pada lajur J ialah:
=(G5-H5)/H5
dengan format nombor peratusan digunakan.
Catatan
- Data yang ditunjukkan di sini akan berfungsi dengan baik dalam Jadual Excel, yang akan berkembang secara automatik untuk memasukkan data baru. Kami menggunakan julat bernama di sini untuk memastikan formula sesederhana mungkin.
- Jadual pangsi juga boleh digunakan untuk mengira varians. Rumusan memberikan lebih banyak fleksibiliti dan kawalan dengan kos yang lebih kompleks.
