Formula generik
SUMPRODUCT(--(A:A=A1))
Ringkasan
Kata Pengantar
Ini adalah pengenalan yang sangat panjang, tetapi konteksnya penting, maaf!
Sekiranya anda cuba mengira nombor yang sangat panjang (16+ digit) dalam julat dengan COUNTIF, anda mungkin akan melihat hasil yang salah, kerana terdapat bug pada bagaimana fungsi tertentu menangani nombor panjang, walaupun nombor tersebut disimpan sebagai teks. Pertimbangkan skrin di bawah. Semua kiraan di lajur D tidak betul-walaupun setiap nombor di lajur B adalah unik, kiraan yang dikembalikan oleh COUNTIF menunjukkan nombor ini adalah pendua.
=COUNTIF(data,B5)
Masalah ini berkaitan dengan cara Excel menangani nombor. Excel hanya dapat menangani 15 digit penting, dan jika anda memasukkan nombor dengan lebih dari 15 digit di Excel, anda akan melihat digit yang tertinggal diam-diam ditukar menjadi sifar. Masalah pengiraan yang disebutkan di atas timbul dari had ini.
Biasanya, anda dapat mengelakkan had ini dengan memasukkan angka panjang sebagai teks, baik dengan memulai angka dengan satu petikan ('99999999999999999999) atau dengan memformat sel sebagai Teks sebelum memasukkan. Selagi anda tidak perlu melakukan operasi matematik pada nombor, ini adalah penyelesaian yang baik, dan ini membolehkan anda memasukkan nombor yang panjang untuk perkara seperti nombor kad kredit dan nombor siri tanpa kehilangan nombor.
Walau bagaimanapun, jika anda cuba menggunakan COUNTIF untuk mengira nombor dengan lebih dari 15 digit (walaupun disimpan sebagai teks), anda mungkin akan melihat hasil yang tidak boleh dipercayai. Ini berlaku kerana COUNTIF secara dalaman menukar nilai panjang menjadi nombor pada suatu ketika semasa memproses, mencetuskan had 15 digit yang dijelaskan di atas. Tanpa semua digit ada, beberapa nombor boleh dikira seperti pendua apabila dihitung dengan COUNTIF.
Penyelesaian
Salah satu penyelesaiannya ialah menggantikan formula COUNTIF dengan formula yang menggunakan SUM atau SUMPRODUCT. Dalam contoh yang ditunjukkan, formula di E5 kelihatan seperti ini:
=SUMPRODUCT(--(data=B5))
Rumus menggunakan julat bernama "data" (B5: B9) dan menghasilkan kiraan yang betul untuk setiap nombor dengan SUMPRODUCT.
Penjelasan
Pertama, ungkapan di dalam SUMPRODUCT membandingkan semua nilai dalam julat bernama "data" dengan nilai dari lajur B pada baris semasa. Ini menghasilkan pelbagai hasil yang BENAR / SALAH.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Seterusnya, negatif berganda membentuk nilai BENAR / SALAH kepada nilai 1/0.
=SUMPRODUCT((1;0;0;0;0))
Akhirnya, SUMPRODUCT hanya menjumlahkan item dalam array dan mengembalikan hasilnya.
Varian formula array
Anda juga boleh menggunakan fungsi SUM dan bukannya SUMPRODUCT, tetapi ini adalah formula array dan mesti dimasukkan dengan kawalan + shift + enter:
(=SUM(--(B:B=B5)))
Fungsi lain dengan masalah ini
Saya belum mengesahkannya sendiri, tetapi nampaknya beberapa fungsi mempunyai masalah yang sama, termasuk SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, dan AVERAGEIFS.
