Katakanlah Anda ingin bisa menghitung item unik dari daftar, tetapi dengan twist. Dan katakanlah Anda sedang mengerjakan lembar kerja ini:
Kolom D menghitung jumlah baris di setiap bagian dari kolom B, dan kolom C menghitung jumlah bagian unik berdasarkan lima karakter pertama kolom A untuk bagian itu. Sel B2: B11 berisi ARG, dan Anda bisa menghitung delapan item unik dalam lima karakter pertama A2: A11 karena A7: A9 masing-masing berisi 11158, jadi dua duplikat tidak dihitung. Demikian pula, 5 di D12 memberi tahu Anda bahwa ada lima baris untuk BRD, tetapi dalam baris 12:16, ada tiga item unik dari lima karakter pertama, karena 11145 diulang dan 11173 diulang.
Tapi bagaimana Anda memberi tahu Excel untuk melakukan ini? Dan rumus apa yang bisa Anda gunakan di C2 yang bisa disalin ke C12 dan C17?
Rumus penghitungan sederhana di D2`` =COUNTIF(B:B,B2)menghitung berapa kali B2 (ARG) ada di kolom B.
Anda menggunakan kolom pembantu untuk mengisolasi lima karakter pertama kolom A, seperti pada gambar ini:
Selanjutnya, Anda perlu menunjukkan bahwa untuk ARG, Anda hanya tertarik pada sel F2: F11 untuk menemukan jumlah item unik. Secara umum, Anda akan menemukan nilai ini dengan menggunakan rumus array yang ditunjukkan pada gambar ini:
Anda menggunakan sel C3 sementara hanya untuk memperlihatkan rumus; Anda dapat melihat bahwa itu tidak ada di C3 pada gambar sebelumnya. (Anda akan segera mempelajari cara kerja rumus ini.)
Jadi apa rumus di C2, C12, dan C17? Jawaban yang mengejutkan (dan keren) ditunjukkan pada gambar ini:
Wah! Bagaimana cara kerjanya?
Lihatlah Jawaban dalam nama yang ditentukan dalam gambar ini:
Ini adalah rumus yang sama dari gambar sebelumnya, tetapi alih-alih menggunakan rentang F2: F11, ia menggunakan rentang bernama Rg. Selain itu, rumusnya adalah rumus array, tetapi rumus bernama diperlakukan seolah-olah rumus itu adalah rumus array! Artinya, =Answertidak dimasukkan dengan Ctrl + Shift + Enter tetapi hanya dimasukkan seperti biasa.
Jadi bagaimana Rg didefinisikan? Jika sel C1 dipilih (yang merupakan langkah penting untuk memahami trik ini), maka itu didefinisikan seperti pada gambar ini:
Begitulah =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 sheet, tetapi Anda dapat melihat rumus ini tanpa nama sheet yang panjang. Cara mudah untuk melakukannya adalah dengan memberi nama sementara pada sheet dengan sesuatu yang sederhana, seperti x, lalu lihat lagi pada nama yang ditentukan:
Rumus ini lebih mudah dibaca!
Anda dapat melihat bahwa rumus ini cocok dengan $ B1 (perhatikan referensi relatif ke baris saat ini) dengan semua kolom B dan pengurangan 1. Anda mengurangi 1 karena Anda menggunakan OFFSET dari F1. Sekarang setelah Anda mengetahui tentang rumus untuk C, lihat rumus untuk C2:
Bagian MATCH($B2,$B:$B,0)dari rumusnya adalah 2, jadi rumusnya (tanpa mengacu pada nama sheet) adalah:
=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)
Karena COUNTIF($B:$B,$B2)10, ada 10 ARG. Ini adalah kisaran F2: F11. Faktanya, jika sel C2 dipilih dan Anda menekan F5 untuk menuju ke Rg, Anda akan melihat ini:
Jika sel awal adalah C12, menekan F5 untuk menuju ke Rg menghasilkan ini:
Jadi sekarang, dengan Answer didefinisikan sebagai =SUM(1/COUNTIF(rg,rg)), Anda sudah selesai!
Mari kita lihat lebih dekat cara kerja rumus ini, menggunakan contoh yang jauh lebih sederhana. Biasanya, sintaks COUNTIF adalah =COUNTIF(range,criteria), seperti =COUNTIF(C1:C10, "b")pada gambar ini:
Ini akan memberikan 2 sebagai jumlah b dalam rentang tersebut. Namun meneruskan rentang itu sendiri sebagai kriteria menggunakan setiap item dalam rentang sebagai kriteria. Jika Anda menyoroti bagian rumus ini:
dan tekan F9, Anda akan melihat:
Setiap item dalam rentang dievaluasi, dan rangkaian angka ini berarti ada satu a dan ada dua b, tiga c, dan empat d. Angka-angka ini dibagi menjadi 1, memberikan 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, seperti yang Anda lihat di sini:
Jadi Anda memiliki 2 bagian, 3 pertiga, 4 perempat, dan 1 utuh, dan menjumlahkannya menghasilkan 4. Jika sebuah item diulang 7 kali, maka Anda akan memiliki 7 ketujuh dan seterusnya. Sangat keren! (Angkat topi untuk David Hager karena menemukan / menemukan formula ini.)
Tapi tunggu sebentar. Seperti berdiri, Anda hanya perlu memasukkan rumus ini di C2, C12, dan C17. Bukankah lebih baik jika Anda bisa memasukkannya di C2 dan mengisi dan hanya menampilkannya di sel yang benar? Nyatanya, Anda bisa melakukan ini. Anda dapat mengubah rumus di C2 menjadi =IF(B1B2,Answer,""), dan saat Anda mengisinya, rumus tersebut berfungsi :
Tapi kenapa berhenti disini? Mengapa tidak membuat rumus tersebut menjadi rumus bernama, seperti yang diperlihatkan di sini:
Agar ini berfungsi, sel C2 harus merupakan sel aktif (atau rumusnya harus berbeda). Sekarang Anda dapat mengganti rumus kolom C dengan =Answer2:
Anda dapat melihat bahwa C3 memiliki =Answer2, seperti halnya semua sel di kolom C. Mengapa tidak melanjutkan ini di kolom D? Rumus di D2, setelah juga menerapkan perbandingan ke B1 dan B2, ditampilkan di sini:
Jadi jika Anda membiarkan sel D2 dipilih dan menentukan rumus lain, katakan Answer3:
lalu Anda bisa masuk =Answer3di sel D2 dan mengisi:
Inilah bagian atas lembar kerja, dengan rumus yang ditampilkan, diikuti oleh tangkapan layar yang sama dengan nilai yang ditampilkan:
Ketika orang lain mencoba memikirkan hal ini, mereka mungkin akan menggaruk kepalanya terlebih dahulu!
Artikel tamu ini berasal dari Excel MVP Bob Umlas. Ini dari buku, More Excel Outside the Box. Untuk melihat topik lain di buku, klik di sini.
