Membuat filter data berdasarkan kriteria di Excel itu sebenarnya simpel saja. Yang perlu dilakukan hanyalah memastikan adanya data yang bisa dijadikan acuan.
Apalagi dalam pekerjaan sehari-hari, seringkali kita perlu memilah-milah dan memfilter data dalam lembar kerja Excel berdasarkan kriteria tertentu. Excel menyediakan berbagai rumus yang memungkinkan kita untuk membuat filter data berdasarkan kriteria di excel dengan mudah dan efisien.
Dan di sini, kita akan membuat rumus Excel yang berguna untuk memfilter data berdasarkan kriteria tertentu seperti yang kita jelaskan di awal. Fungsi Excel yang akan kita gunakan adalah fungsi SMALL dan IF yang akan mengambil data acuan dan OFFSET untuk memanggil nama-nama yang terfilter.
Kegunaan Filter Data Berdasarkan Kriteria
Filter data berdasarkan kriteria memiliki banyak kegunaan dalam analisis data dan pengelolaan informasi. Berikut adalah beberapa kegunaan utama dari filter data berdasarkan kriteria:
- Mencari dan menampilkan data yang relevan: Dengan menggunakan filter data, Anda dapat mencari data yang memenuhi kriteria tertentu dan menampilkan hanya data yang relevan.
- Menganalisis data dengan fokus: Dengan memfilter data berdasarkan kriteria, Anda dapat fokus pada subset data yang ingin Anda analisis. Ini membantu Anda menghilangkan kebisingan dan informasi yang tidak perlu, sehingga memudahkan identifikasi pola, tren, atau anomali yang lebih jelas.
- Menyaring data yang tidak diperlukan: Filter data memungkinkan Anda untuk menyaring data yang tidak diperlukan dalam tampilan saat ini. Misalnya, jika Anda bekerja dengan lembar kerja Excel yang berisi banyak kolom, Anda dapat mengabaikan data yang tidak relevan untuk mengurangi kebingungan dan fokus pada informasi yang penting.
Membuat filter data berdasarkan kriteria di Excel merupakan alat yang kuat dalam pengelolaan dan analisis data. Dengan menggunakan filter, Anda dapat dengan cepat menemukan, menyaring, dan menganalisis subset data yang spesifik untuk mendapatkan wawasan yang lebih mendalam dan membuat keputusan yang lebih baik.
Membuat Filter Data Berdasarkan Kriteria di Excel
Hal pertama yang akan kita lakukan untuk Membuat filter data berdasarkan kriteria di Excel adalah membuat tabel data awal sebagai database. Sebagai contoh, inilah tabel awal yang kita buat:
Filter Nomor Urut
Setelah tabelnya kita buat maka langkah pertama yang perlu dilakukan adalah memasukkan rumus di sel F5. Dan karena kita akan membuat rumusnya menggunakan Formula Array yang sekaligus memasukkan rumus dalam satu range maka langkahnya adalah blok dulu range F5 sampai F7 kemudian ketik rumus di formula bar sebagai berikut:
=SMALL(IF(C3:C11=G2;B3:B11);B3:B11)
Jika sudah, karena ini formula array maka Anda harus mengakhiri penulisan rumus dengan menekan kombinasi tombol Ctrl + Shift + Enter. Ini akan menandakan bahwa rumus tersebut adalah rumus array dan Excel akan secara otomatis memasukkan kurung kurawal di sekitar rumus.
Untuk hasil akhirnya lihat tabel berikut:
Penjelasan Rumus
Mari kita pecah rumus Membuat filter data berdasarkan kriteria di Excel ini menjadi beberapa bagian untuk memahami fungsinya:
- IF(C3:C11=G2;B3:B11) : Ruang lingkup ini adalah IF statement dalam Excel. IF digunakan untuk menguji suatu kondisi dan mengembalikan nilai jika kondisi tersebut benar. Dalam hal ini, kondisi yang diuji adalah C3:C11 = G2, yang berarti mencocokkan nilai di sel C3 hingga C11 dengan nilai di sel G2. Jika kondisi ini benar, ini akan mengembalikan kumpulan nilai dari sel B3 hingga B11 jika nilai di sel C3 hingga C11 sama dengan nilai di sel G2.
- SMALL(array;k) : Ruang lingkup ini adalah fungsi SMALL dalam Excel. Fungsi SMALL digunakan untuk mengembalikan nilai terkecil ke-k dari suatu kumpulan nilai. Dalam rumus ini, array yang digunakan sebagai argumen pertama adalah hasil dari fungsi IF di atas (IF(C3:C11=G2;B3:B11)), yang berarti kita mengambil nilai-nilai yang cocok dari sel B3 hingga B11 berdasarkan kondisi yang ditentukan. Argumen kedua, k, adalah angka yang menentukan urutan kecil dari nilai yang akan dikembalikan.
- B3:B11 : Ini digunakan sebagai argumen kedua dalam fungsi SMALL. Ini adalah rentang nilai dari sel B3 hingga B11 yang akan digunakan untuk menentukan urutan kecil dalam fungsi SMALL.
- {} : Simbol kurung kurawal yang muncul secara otomatis mengapit rumus adalah sebagai penanda bahwa ini adalah formula array.
Jadi, keseluruhan rumus tersebut mengembalikan nilai terkecil ke-k (tergantung pada nilai k yang diberikan) dari sel B3 hingga B11 di mana nilai di sel C3 hingga C11 sama dengan nilai di sel G2.
Filter Tabel Keterangan
Kita telah selesai membuat filtering untuk nomor urut, jadi untuk mengambil data di kolom keterangan menjadi lebih mudah. Kita cukup menggunakan rumus dasar yakni OFFSET. Caranya ketik fungsi OFFSET ini di sel G5:
=OFFSET($D$2;F5;0)
Copy dan paste rumus tersebut ke sel di bawahnya. Jika benar, maka hasilnya akan seperti berikut ini:
Penjelasan Rumus
Rumus Excel yang yang kita input adalah OFFSET($D$2;F5;0). Mari kita pecah rumus ini untuk memahami fungsinya:
- $D$2 : Ini adalah referensi sel tetap dalam rumus. Tanda dollar ($) digunakan untuk mengunci referensi sel sehingga saat rumus disalin ke sel lain, referensi tetap pada sel yang sama. Dalam hal ini, sel D2 adalah referensi tetap yang akan digunakan sebagai titik awal pergeseran.
- F5 : Ini adalah argumen baris dalam fungsi OFFSET. Ini adalah angka yang menentukan jumlah baris yang akan digeser dari titik awal referensi sel. Dalam hal ini, nilai di sel F5 digunakan untuk menentukan jumlah baris yang akan digeser dari sel D2.
- 0 : Ini adalah argumen kolom dalam fungsi OFFSET. Ini adalah angka yang menentukan jumlah kolom yang akan digeser dari titik awal referensi sel. Dalam hal ini, nilai 0 menunjukkan bahwa tidak ada pergeseran kolom yang terjadi dari sel D2.
Jadi, secara keseluruhan, rumus OFFSET($D$2;F5;0) akan mengembalikan nilai dari sel yang dihasilkan dengan melakukan pergeseran sebanyak F5 baris di bawah sel D2. Dalam konteks ini, data di sel F5 berfungsi sebagai variabel yang menentukan pergeseran baris yang dinamis dalam rumus.
Penutup
Kita telah membuat rumus Excel yang berguna untuk memfilter data berdasarkan kriteria tertentu. Excel menyediakan berbagai fungsi yang memungkinkan kita untuk melakukan tugas ini dengan efisiensi tinggi. Dengan memahami dan menguasai rumus ini, kita dapat dengan mudah menganalisis dan mengelola data dalam lembar kerja Excel sesuai dengan kebutuhan kita.
Semoga artikel ini bermanfaat dan membantu Anda dalam bekerja dengan Excel. Dan jika Anda membutuhkan file contoh ini, file bisa didownload dengan klik tautan di bawah ini:
0 Komentar
Trackbacks/Pingbacks