Formula generik
=SUM(--(MMULT(--(criteria),TRANSPOSE(COLUMN(data)))>0))
Ringkasan
Untuk mengira baris yang mengandungi nilai tertentu, anda boleh menggunakan formula array berdasarkan fungsi MMULT, TRANSPOSE, COLUMN, dan SUM. Dalam contoh yang ditunjukkan, formula dalam G5 adalah:
(=SUM(--(MMULT(--(data=90),TRANSPOSE(COLUMN(data)))>0)))
di mana data adalah julat bernama B4: B12.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan kawalan shift enter.
Penjelasan
Berfungsi dari dalam ke luar, kriteria logik yang digunakan dalam formula ini adalah:
--(data=90)
di mana data adalah julat bernama B4: D12. Ini menghasilkan hasil BENAR / SALAH untuk setiap nilai dalam data, dan negatif dua kali ganda menyatukan nilai BENAR BENAR menjadi 1 dan 0 untuk menghasilkan susunan seperti ini:
(1,0,0;0,0,0;0,1,1;1,0,0;0,0,0;0,0,0;0,0,0;0,0,0;0,0,1)
Seperti data asal, susunan ini berukuran 9 baris dengan 3 lajur (9 x 3) dan masuk ke fungsi MMULT sebagai array1 .
Array2 diturunkan dengan:
TRANSPOSE(COLUMN(data))
Inilah bahagian rumus dan menyeronokkan dari formula ini. Fungsi COLUMN digunakan hanya untuk kemudahan sebagai cara untuk menghasilkan susunan angka dengan ukuran yang tepat. Untuk melakukan pendaraban matriks dengan MMULT, kiraan lajur dalam array1 (3) mesti sama dengan jumlah baris dalam array2 .
COLUMN mengembalikan array 3-lajur (2,3,4), dan TRANSPOSE mengubah susunan ini kepada susunan 3 baris (2; 3; 4). MMULT kemudian berjalan dan mengembalikan hasil susunan 9 x 1:
=SUM(--((2;0;7;2;0;0;0;0;4)>0))
Kami menyemak entri bukan sifar dengan> 0 dan sekali lagi memaksa TRUE FALSE menjadi 1 dan 0 dengan negatif ganda untuk mendapatkan susunan terakhir di dalam SUM:
=SUM((1;0;1;1;0;0;0;0;1))
Dalam susunan akhir ini, 1 mewakili baris di mana ujian logik (data = 90) kembali benar. Jumlah yang dikembalikan oleh SUM adalah jumlah semua baris yang mengandungi nombor 90.
Literal mengandungi
Sekiranya anda perlu memeriksa nilai teks tertentu, dengan kata lain, periksa secara harfiah untuk mengetahui apakah sel mengandungi nilai teks tertentu, anda boleh mengubah logik dalam formula di halaman ini untuk menggunakan fungsi ISNUMBER dan SEARCH. Contohnya, untuk mengira sel / baris yang mengandungi "epal" anda boleh menggunakan:
=ISNUMBER(SEARCH("apple",data))
Perincian mengenai bagaimana formula ini berfungsi di sini.
