Formula generik
=IF(A2=A1,"",SUMIF(A:A,A2,B:B))
Ringkasan
Untuk subtotal data mengikut kumpulan atau label, secara langsung dalam jadual, anda boleh menggunakan formula berdasarkan fungsi SUMIF.
Dalam contoh yang ditunjukkan, formula dalam D5 adalah:
=IF(B5=B4,"",SUMIF(B:B,B5,C:C))
Catatan: data mesti disusun mengikut kolum pengelompokan untuk mendapatkan hasil yang masuk akal.
Penjelasan
Kerangka formula ini didasarkan pada IF, yang menguji setiap nilai di lajur B untuk melihat apakah sama dengan nilai di "sel di atas". Apabila nilai sepadan, formula tidak mengembalikan apa-apa (""). Apabila nilai berbeza, fungsi IF memanggil SUMIF:
SUMIF(B:B,B5,C:C)
Pada setiap baris di mana SUMIF dipicu oleh IF, SUMIF menghitung jumlah semua baris yang sesuai di lajur C (C: C). Kriteria yang digunakan oleh SUMIF adalah nilai baris semasa lajur B (B5), diuji terhadap semua lajur B (B: B).
Rujukan lajur penuh seperti ini keren dan elegan, kerana anda tidak perlu bimbang di mana data bermula dan berakhir, tetapi anda perlu memastikan tidak ada data tambahan di atas atau di bawah jadual yang mungkin terperangkap oleh SUMIF.
Persembahan
Nampaknya menggunakan rujukan lajur penuh adalah idea yang tidak baik, kerana versi Excel semasa mengandungi lebih dari 1 juta baris. Walau bagaimanapun, pengujian menunjukkan bahawa Excel hanya menilai data dalam "julat terpakai" (A1 ke alamat persimpangan lajur yang terakhir digunakan dan baris terakhir yang digunakan) dengan jenis formula ini.
Charles Williams di Fast Excel mempunyai artikel yang baik mengenai topik ini, dengan set keputusan masa yang lengkap.
Mengapa mengenai Jadual Pangsi?
Contoh ini dimaksudkan menunjukkan bagaimana rujukan lajur penuh berfungsi, dan bagaimana mereka dapat digunakan secara langsung dalam tabel data. Jadual pangsi tetap merupakan kaedah terbaik untuk mengumpulkan dan meringkaskan data.
