Ringkasan
Untuk menghitung perkiraan versus varian aktual berdasarkan sekumpulan data, Anda dapat menggunakan fungsi SUMIFS untuk mengumpulkan total, dan rumus dasar lainnya untuk menghitung persentase varians dan varians. Pada contoh yang ditunjukkan, rumus di G5 adalah:
=SUMIFS(amount,type,G$4,group,$F5)
di mana jumlah adalah rentang bernama C5: C14, dan jenis adalah rentang bernama D5: D14, dan grup adalah rentang bernama B5: B14.
Penjelasan
Ini adalah penggunaan yang cukup standar dari fungsi SUMIFS. Dalam hal ini, kita perlu menjumlahkan jumlah berdasarkan dua kriteria: jenis (perkiraan atau aktual) dan kelompok. Untuk menjumlahkan berdasarkan jenis, pasangan rentang / kriteria adalah:
type,G$4
di mana tipe adalah rentang bernama D5: D14, dan G4 adalah referensi campuran dengan baris dikunci agar sesuai dengan tajuk kolom di baris 4 saat rumus disalin.
Untuk dijumlahkan berdasarkan kelompok, pasangan rentang / kriteria adalah:
group,$F5
di mana grup adalah rentang bernama B5: B14, dan F5 adalah referensi campuran dengan kolom terkunci untuk mencocokkan nama grup di kolom F ketika rumus disalin.
Rumus varians
Rumus varians di kolom I hanya mengurangi perkiraan dari aktual:
=G5-H5
Rumus persentase varians di kolom J adalah:
=(G5-H5)/H5
dengan format angka persentase diterapkan.
Catatan
- Data yang diperlihatkan di sini akan bekerja dengan baik dalam Tabel Excel, yang secara otomatis akan meluas untuk menyertakan data baru. Kami menggunakan rentang bernama di sini untuk membuat rumusnya sesederhana mungkin.
- Tabel pivot juga dapat digunakan untuk menghitung varians. Rumus memberikan lebih banyak fleksibilitas dan kontrol dengan biaya lebih kompleks.
