Cara Filter Data di Excel Berdasarkan Huruf Depannya di Excel dengan Gampang

Cara filter data di Excel adalah salah satu cara yang sangat berguna dalam mengorganisir dan menganalisis informasi. Namun, terkadang kita perlu melakukan filter berdasarkan kriteria yang spesifik, seperti hanya menampilkan data yang dimulai dengan huruf tertentu. Dalam artikel ini, kita akan membahas cara filter data di Excel berdasarkan huruf depannya.

Langkah 1: Buka File Contoh Cara Filter Data di Excel

Pertama-tama untuk cara filter data di Excel ini, buka file Excel yang berisi data yang ingin Anda filter berdasarkan huruf depannya. Pastikan Anda telah memilih lembar kerja yang sesuai. Sebagai contoh kita akan menggunakan tabel data berikut:

Langkah 2: Ketikkan Huruf Depannya

Selanjutnya, ketik huruf depan dari nama yang ingin Anda filter di sel I1. Misalnya, jika Anda ingin hanya menampilkan data yang dimulai dengan huruf “H,” ketikkan huruf “H”.

Langkah 3: Gunakan Kolom Helper

Agar lebih mudah, gunakan kolom helper yang kemudian kita isi dengan formula Array untuk menampilkan nomor urut dari nama-nama yang kita filter. Ingat ya ini formula array sekaligus, jadi sebelum mengetik rumusnya terlebih dahulu blok range yang akan kita masukkan formula yang dalam hal ini adalah range G4:G31. Dan rumusnya adalah sebagai berikut:

=IFERROR(SMALL(IF(LOWER(LEFT(C4:C31;LEN(I1)))=LOWER(I1);ROW(1:28));ROW(1:28));"")

Ingat juga, karena ini formula array maka akhiri penulisan rumusnya dengan menekan Ctrl, Shift dan Enter secara bersamaan. Dan hasilnya adalah sebagai berikut:

Penjelasan Rumus

Formula array ini adalah rumus Excel yang digunakan untuk menemukan dan menampilkan baris dari seluruh data yang memenuhi kondisi tertentu. Formula ini memiliki beberapa fungsi dan konsep yang harus kita pahami secara terpisah untuk memahaminya dengan baik. Mari kita pecah dan jelaskan bagian per bagian:

Bagian 1: =IFERROR(...)

Ini adalah fungsi IFERROR, yang digunakan untuk menangani kesalahan dalam formula. Fungsi ini menguji apakah ada kesalahan dalam formula yang dibungkus olehnya, dan jika ada, maka mengembalikan nilai alternatif (dalam hal ini, string kosong “” atau “”). Fungsi IFERROR ini digunakan di seluruh formula untuk menangani potensi kesalahan dalam perhitungan berikutnya.

Bagian 2: SMALL(...)

Fungsi SMALL digunakan untuk mengambil nilai terkecil dari suatu rentang data. Dalam konteks formula ini, kita akan mencari baris ke-kecil yang memenuhi kondisi tertentu.

Bagian 3: IF(...)

Ini adalah fungsi IF bersarang pertama dalam formula. Fungsi IF digunakan untuk menguji kondisi tertentu, dan tergantung pada apakah kondisi tersebut benar atau salah, akan mengembalikan nilai yang berbeda. Di dalam fungsi IF ini terdapat beberapa fungsi dan operasi lain:

• LOWER(LEFT(C4:C31, LEN(I1))): Ini adalah fungsi LEFT yang digunakan untuk mengambil sebagian teks dari sel dalam rentang C4:C31, dan kemudian fungsi LOWER digunakan untuk mengubahnya menjadi huruf kecil. Fungsi LEN(I1) digunakan untuk menentukan panjang teks dalam sel I1. Jadi, kita mencari karakter pertama dari setiap sel dalam rentang C4:C31 (dengan panjang yang sama dengan I1), mengubahnya menjadi huruf kecil, dan membandingkannya dengan teks dalam I1.
• ROW(1:28): Ini adalah fungsi ROW yang digunakan untuk membuat larik angka dari 1 hingga 28 (jumlah baris dalam rentang C4:C31). Ini akan memberikan nomor baris dari data yang akan dianalisis.

Bagian 4: {= ... }

Tanda kurung kurawal ini menunjukkan bahwa rumus ini adalah formula array. Formula array digunakan untuk melakukan operasi berulang-ulang pada sel atau sel dalam rentang, dan hasilnya akan mengisi beberapa sel sekaligus.

Bagian 5: ""

Ini adalah nilai yang akan ditampilkan jika tidak ada baris yang memenuhi kondisi yang ditentukan dalam formula. Dalam hal ini, jika tidak ada baris yang cocok, maka sel akan berisi string kosong (“”).

Jadi, keseluruhan formula berfungsi untuk mengambil baris-baris dalam rentang C4:C31 yang karakter pertamanya cocok dengan karakter pertama yang ada di sel I1, kemudian mengurutkannya dari yang terkecil (berdasarkan nomor baris), dan mengembalikan nomor-nomor baris ini dalam bentuk formula array. Jika tidak ada yang cocok, formula akan menghasilkan string kosong. Hasil dari rumus ini adalah larik nomor baris yang sesuai dengan kondisi yang ditentukan.

Langkah 4: Masukkan Rumus untuk Mengambil Data

Langkah berikutnya untuk cara filter data di Excel adalah masukkan rumus di sel H4 yang gunanya untuk mengambil nilai dari sel berdasarkan kondisi tertentu. Rumusnya adalah sebagai berikut:

=IF(ISNUMBER($G4);OFFSET(B$3;$G4;0);"")

Dan inilah hasilnya setelah rumus dicopy-paste ke sel di samping dan di bawahnya:

Penjelasan Rumus

Rumus Excel ini adalah rumus yang digunakan untuk mengambil nilai dari sel berdasarkan kondisi tertentu. Mari kita bahas setiap bagian rumus ini secara rinci:

Bagian 1: =IF(...)

Ini adalah fungsi IF Excel, yang digunakan untuk melakukan pengujian logis. Fungsi IF menguji suatu kondisi, dan berdasarkan apakah kondisi tersebut benar (TRUE) atau salah (FALSE), mengembalikan nilai yang sesuai.

Bagian 2: ISNUMBER($G4)

Dalam rumus ini, kita menggunakan fungsi ISNUMBER untuk memeriksa apakah nilai dalam sel $G4 adalah angka.

• $G4: Ini adalah referensi ke sel G4, yang akan dianalisis untuk setiap baris yang berbeda ketika rumus diterapkan pada sel yang berbeda.

Jika ISNUMBER($G4) mengembalikan TRUE, berarti sel $G4 berisi angka, dan kita akan menjalankan rumus untuk mengambil nilai dari sel lain.

Bagian 3: OFFSET(...)

Fungsi OFFSET digunakan untuk mengambil nilai dari sel yang berada pada jarak tertentu dari sel referensi awal. Dalam rumus ini, kita akan mengambil nilai dari sel di kolom B, tetapi barisnya akan ditentukan oleh nilai di sel $G4.

• B$3: Ini adalah sel referensi awal yang akan digunakan sebagai titik awal untuk perhitungan OFFSET. Sel B3 digunakan sebagai referensi baris, dan kita akan mengambil nilai dari sel dalam kolom B.
• $G4: Ini adalah nilai yang akan digunakan sebagai jarak (offset) dari sel referensi awal. Jika $G4 adalah 1, maka kita akan mengambil nilai dari sel yang berada satu baris di bawah B3. Jika $G4 adalah 2, maka kita akan mengambil nilai dari sel yang berada dua baris di bawah B3, dan seterusnya.

Bagian 4: ""

Ini adalah nilai yang akan ditampilkan jika kondisi dalam IF mengembalikan FALSE. Dalam konteks ini, jika ISNUMBER($G4) mengembalikan FALSE, maka sel akan berisi string kosong (“”). Ini berarti bahwa jika sel $G4 bukan angka, maka sel hasil akan kosong.

Jadi, keseluruhan rumus ini berfungsi sebagai berikut:

• Jika nilai di sel $G4 adalah angka (ISNUMBER($G4) mengembalikan TRUE), maka rumus akan mengambil nilai dari sel yang terletak dalam kolom B, dengan baris yang ditentukan oleh nilai di sel $G4.
• Jika nilai di sel $G4 bukan angka (ISNUMBER($G4) mengembalikan FALSE), maka sel hasil akan kosong.

Rumus ini berguna ketika Anda ingin mengambil nilai dari sel berdasarkan suatu kondisi tertentu. Jika kondisi tersebut benar, maka nilai akan diambil; jika tidak, sel akan tetap kosong.

Langkah 5: Border Menggunakan Conditional Formatting

Sampai disini sebenarnya cara filter data di Excel sudah selesai dibuat dan bisa digunakan. Tapi untuk kerapihan tabel, langkah berikutnya adalah membuat border dinamis menggunakan conditional formatting. Berikut adalah gambar contohnya:

Penutup

Cara filter data di Excel ini memungkinkan Anda untuk dengan mudah mengidentifikasi dan menampilkan data yang dimulai dengan huruf tertentu dalam Excel. Ini sangat berguna ketika Anda bekerja dengan kumpulan data besar dan ingin fokus pada data yang relevan sesuai dengan kriteria yang Anda tentukan.

Dengan menggunakan filter ini, Anda dapat menghemat waktu dan menjadikan proses analisis data Anda lebih efisien. Semoga artikel ini membantu Anda dalam menguasai cara melakukan filter data berdasarkan huruf depannya di Excel. Teruslah menjelajahi dan memanfaatkan fitur-fitur Excel lainnya untuk meningkatkan produktivitas Anda dalam pengolahan data.

Dan yang terakhir, file Excel cara filter data di Excel ini bisa diunduh melalui tautan di bawah ini: