Katakan bahawa anda ingin dapat menghitung item unik dari senarai, tetapi dengan kelainan. Dan katakan anda bekerja dengan lembaran kerja ini:
Lajur D menghitung bilangan baris di setiap bahagian dari lajur B, dan lajur C menghitung bilangan bahagian unik berdasarkan lima aksara pertama lajur A untuk bahagian itu. Sel B2: B11 mengandungi ARG, dan anda boleh mengira lapan item unik dalam lima watak pertama A2: A11 kerana masing-masing A7: A9 mengandungi 11158, jadi dua pendua tidak dihitung. Begitu juga, angka 5 dalam D12 memberitahu anda terdapat lima baris untuk BRD, tetapi dalam baris 12:16, terdapat tiga item unik dari lima watak pertama, kerana 11145 diulang dan 11173 diulang.
Tetapi bagaimana anda memberitahu Excel untuk melakukan ini? Dan formula apa yang boleh anda gunakan dalam C2 yang dapat disalin ke C12 dan C17?
Rumus pengiraan mudah dalam D2,, =COUNTIF(B:B,B2)mengira berapa kali B2 (ARG) wujud di lajur B.
Anda menggunakan lajur pembantu untuk mengasingkan lima watak pertama lajur A, seperti dalam gambar ini:
Seterusnya, anda perlu menunjukkan bahawa untuk ARG, anda hanya berminat dengan sel F2: F11 untuk mencari bilangan item unik. Secara amnya, anda akan mendapat nilai ini dengan menggunakan formula array yang ditunjukkan dalam gambar ini:
Anda menggunakan sel C3 buat sementara waktu hanya untuk menunjukkan formula; anda dapat melihat bahawa ia tidak terdapat dalam C3 dalam angka sebelumnya. (Anda akan belajar sebentar bagaimana formula ini berfungsi.)
Jadi apa formula dalam C2, C12, dan C17? Jawapan yang mengejutkan (dan keren) ditunjukkan dalam gambar ini:
Wah! Bagaimana ianya berfungsi?
Lihatlah Jawapan dalam nama yang ditentukan dalam gambar ini:
Ini adalah formula yang sama dari angka sebelumnya, tetapi bukannya menggunakan julat F2: F11, ia menggunakan julat bernama Rg. Juga, formula itu adalah formula array, tetapi formula bernama diperlakukan seolah-olah mereka adalah formula array! Maksudnya, =Answertidak dimasukkan dengan Ctrl + Shift + Enter tetapi hanya dimasukkan seperti biasa.
Jadi bagaimana Rg ditakrifkan? Sekiranya sel C1 dipilih (yang merupakan langkah penting untuk memahami muslihat ini), maka ditentukan seperti dalam gambar ini:
Itulah =OFFSET(Loan_Details!$F$1,MATCH(Loan_Details!$B1,Loan_Details!$B:$B,0)-1,0,COUNTIF(Loan_Details!$B:$B,Loan_Details!$B1),1).
Loan_Details adalah nama helaian, tetapi anda boleh melihat formula ini tanpa nama helaian panjang. Cara mudah untuk melakukan ini adalah dengan menamakan helaian buat sementara waktu sesuatu yang mudah, seperti x, dan kemudian melihat kembali nama yang ditentukan:
Formula ini lebih senang dibaca!
Anda dapat melihat bahawa formula ini sepadan dengan $ B1 (perhatikan rujukan relatif pada baris semasa) dengan semua lajur B dan tolak 1. Anda mengurangkan 1 kerana anda menggunakan OFFSET dari F1. Sekarang setelah anda mengetahui mengenai formula untuk C, lihatlah formula C2:
Yang MATCH($B2,$B:$B,0)sebahagian daripada formula adalah 2, jadi formula (tanpa merujuk kepada nama helaian) ialah:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
atau:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
atau:
=OFFSET($F$1,1,0,10,1)
Kerana COUNTIF($B:$B,$B2)10, ada 10 ARG. Ini adalah julat F2: F11. Sebenarnya, jika sel C2 dipilih dan anda menekan F5 untuk pergi ke Rg, anda melihat ini:
Sekiranya sel permulaan adalah C12, menekan F5 untuk pergi ke Rg menghasilkan ini:
Jadi sekarang, dengan Jawapan ditakrifkan sebagai =SUM(1/COUNTIF(rg,rg)), anda sudah selesai!
Mari lihat dengan lebih teliti bagaimana formula ini berfungsi, menggunakan contoh yang lebih mudah. Biasanya, sintaks untuk COUNTIF adalah =COUNTIF(range,criteria), seperti =COUNTIF(C1:C10, "b")dalam rajah ini:
Ini akan memberi 2 sebagai bilangan b dalam julat. Tetapi melewati julat itu sendiri sebagai kriteria menggunakan setiap item dalam julat sebagai kriteria. Sekiranya anda menyerlahkan bahagian formula ini:
dan tekan F9, anda melihat:
Setiap item dalam julat dinilai, dan rangkaian nombor ini bermaksud ada satu a dan ada dua b, tiga c, dan empat d. Nombor-nombor ini dibahagikan kepada 1, memberikan 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, seperti yang anda lihat di sini:
Oleh itu, anda mempunyai 2 bahagian, 3 pertiga, 4 perempat, dan 1 keseluruhan, dan menambahkannya menghasilkan 4. Jika item diulang 7 kali, maka anda akan mempunyai 7 ketujuh dan seterusnya. Cukup sejuk! (Bercakap dengan David Hager kerana menemui / mencipta formula ini.)
Tetapi tunggu sebentar. Seperti sekarang, anda hanya perlu memasukkan formula ini di C2, C12, dan C17. Bukankah lebih baik jika anda memasukkannya ke dalam C2 dan mengisi dan hanya menunjukkannya di sel yang betul? Sebenarnya, anda boleh melakukan ini. Anda boleh mengubah formula dalam C2 =IF(B1B2,Answer,""), dan apabila anda mengisinya, ia akan berfungsi:
Tetapi mengapa berhenti di sini? Mengapa tidak menjadikan formula menjadi formula bernama, seperti yang ditunjukkan di sini:
Agar ini berfungsi, sel C2 mestilah sel aktif (atau formula mesti berbeza). Sekarang anda boleh mengganti formula lajur C dengan =Answer2:
Anda dapat melihat bahawa C3 mempunyai =Answer2, seperti juga semua sel di lajur C. Mengapa tidak meneruskannya di lajur D? Rumus dalam D2, setelah menerapkan perbandingan dengan B1 dan B2, ditunjukkan di sini:
Oleh itu, jika anda memilih sel D2 dan menentukan formula lain, katakan Jawapan3:
maka anda boleh memasukkan =Answer3sel D2 dan mengisi:
Inilah bahagian paling atas lembaran kerja, dengan formula menunjukkan, diikuti dengan tangkapan skrin yang sama dengan nilai yang menunjukkan:
Apabila orang lain cuba mengetahuinya, mereka mungkin menggaru kepala pada mulanya!
Artikel tetamu ini adalah dari Excel MVP Bob Umlas. Ini adalah dari buku, Lebih Banyak Excel Di Luar Kotak. Untuk melihat topik lain dalam buku, klik di sini.
