Formula umum
=IF(A2=A1,"",SUMIF(A:A,A2,B:B))
Ringkasan
Untuk subtotal data menurut grup atau label, secara langsung dalam tabel, Anda bisa menggunakan rumus berdasarkan fungsi SUMIF.
Dalam contoh yang ditampilkan, rumus di D5 adalah:
=IF(B5=B4,"",SUMIF(B:B,B5,C:C))
Catatan: data harus diurutkan berdasarkan kolom pengelompokan untuk mendapatkan hasil yang masuk akal.
Penjelasan
Kerangka rumus ini didasarkan pada IF, yang menguji setiap nilai di kolom B untuk melihat apakah nilainya sama dengan nilai di "sel di atas". Jika nilai cocok, rumus tidak mengembalikan apa pun (""). Ketika nilainya berbeda, fungsi IF memanggil SUMIF:
SUMIF(B:B,B5,C:C)
Di setiap baris di mana SUMIF dipicu oleh IF, SUMIF menghitung jumlah semua baris yang cocok di kolom C (C: C). Kriteria yang digunakan oleh SUMIF adalah nilai baris kolom B (B5) saat ini, diuji terhadap semua kolom B (B: B).
Referensi kolom lengkap seperti ini keren dan elegan, karena Anda tidak perlu khawatir tentang awal dan akhir data, tetapi Anda perlu memastikan tidak ada data tambahan di atas atau di bawah tabel yang mungkin tertangkap oleh SUMIF.
Performa
Mungkin terlihat seperti menggunakan referensi kolom lengkap adalah ide yang buruk, karena versi Excel saat ini berisi lebih dari 1m baris. Namun, pengujian menunjukkan bahwa Excel hanya mengevaluasi data dalam "rentang yang digunakan" (A1 ke alamat perpotongan kolom yang terakhir digunakan dan baris yang terakhir digunakan) dengan jenis rumus ini.
Charles Williams di Fast Excel memiliki artikel bagus tentang topik ini, dengan hasil pengaturan waktu yang lengkap.
Mengapa dengan Tabel Pivot?
Contoh ini dimaksudkan untuk menunjukkan bagaimana referensi kolom lengkap bekerja, dan bagaimana referensi tersebut dapat digunakan secara langsung dalam tabel data. Tabel pivot tetap menjadi cara terbaik untuk mengelompokkan dan meringkas data.
