Cara Mengambil Data dengan Pengecualian Kata Kunci di Excel

Excel adalah salah satu alat yang paling serbaguna dalam dunia bisnis dan analisis data. Terkadang, Anda mungkin perlu mengambil data dari spreadsheet yang besar, tetapi hanya ingin memasukkan data yang memenuhi kriteria tertentu. Salah satu tugas yang umum adalah mengambil data dengan pengecualian kata kunci. Dalam artikel ini, kami akan menjelaskan cara melakukan ini dengan beberapa metode yang berbeda.

Anda dapat mengambil data dari Excel dengan pengecualian kata kunci menggunakan berbagai cara, tergantung pada struktur data dan preferensi Anda. Berikut adalah beberapa metode yang mungkin Anda gunakan:

Filter Data untuk mengambil data dengan pengecualian kata kunci

Cara yang pertama untuk mengambil data dengan pengecualian kata kunci adalah dengan menggunakan fitur “filter”. Dan inilah langkah-langkahnya:

• Ini adalah cara paling sederhana. Anda dapat menggunakan fitur filter di Excel untuk menampilkan atau menyembunyikan baris yang memenuhi atau tidak memenuhi kriteria tertentu.
• Pilih kolom yang mengandung kata kunci, lalu gunakan fitur filter (biasanya ada di tab “Data”) untuk mengatur filter sesuai dengan kata kunci yang ingin Anda eksklusifkan.

Fungsi IF dan VLOOKUP untuk mengambil data dengan pengecualian kata kunci

Misalnya kita punya data tabel seperti di bawah dan kita ingin mengambil data tanpa menyertakan kalimat yang mengandung kata kunci “Penjualan”

Maka yang perlu kita lakukan adalah sebagai berikut:

• Anda dapat menggunakan fungsi-fungsi Excel seperti IF dan VLOOKUP untuk mengambil data dengan pengecualian kata kunci. Misalnya, Anda dapat mengggunakan rumus IF untuk mengambil data hanya jika kata kunci tidak ada dalam sel.
• Contoh rumus: =IF(ISNUMBER(SEARCH($G$2;$B3));"";A3) akan mengambil data dari sel A3 hanya jika kata kunci yang ada di sel G2 tidak ada dalam sel tersebut.

Dan ketika rumus tersebut di drag ke samping dan ke bawah maka hasilnya sebagai berikut:

PivotTable

• Jika Anda memiliki data yang lebih kompleks, PivotTable dapat menjadi solusi yang kuat. Anda dapat menggunakan PivotTable untuk mengelompokkan, menghitung, dan menyaring data sesuai kebutuhan Anda. Anda dapat menggunakan filter PivotTable untuk mengecualikan kata kunci.

Menggunakan Macro VBA

• Jika Anda memiliki pengetahuan dalam pemrograman VBA (Visual Basic for Applications), Anda dapat membuat macro yang disesuaikan untuk mengambil data dengan pengecualian kata kunci. Ini akan memungkinkan Anda untuk melakukan operasi yang lebih canggih dalam memanipulasi data.

Menggunakan Power Query

• Power Query adalah alat yang kuat di Excel untuk mengambil dan mengubah data. Anda dapat menggunakannya untuk mengambil data dari sumber eksternal dan menerapkan berbagai operasi transformasi, termasuk pengecualian kata kunci.

Fungsi FILTER (Excel 365 dan Excel Online)

• Fungsi FILTER adalah fungsi dinamis yang memungkinkan Anda menyaring data berdasarkan kriteria tertentu. Anda dapat menggunakan fungsi ini untuk mengambil data dengan pengecualian kata kunci.

Setiap metode memiliki kelebihan dan kekurangan sendiri, tergantung pada skenario dan preferensi Anda. Pilih metode yang paling sesuai dengan kebutuhan Anda dan tingkat keterampilan Excel Anda.

Menggunakan Formula Array

Tapi jika Anda ingin rumus yang lebih kompleks, Anda bisa menggunakan formula array. Misalnya tabel awalnya seperti ini:

Maka formula array yang bisa kita gunakan adalah sebagai berikut:

=IF(ROWS(E$6:E6)>$F$3;””;INDEX(A$3:A$20;SMALL(IF(ISERROR(SEARCH($G$2;$B$3:$B$20));
ROW($B$3:$B$20)-ROW($B$3)+1);ROWS(E$6:E6))))

Ini adalah rumus array, yang berarti rumus ini akan mengembalikan lebih dari satu nilai (array) dalam satu sel, tergantung pada kondisi yang diberikan. Cara penulisan rumusnya untuk menampilkan tanda kurung kurawal ini adalah dengan cara mengakhiri penulisan rumus dengan menekan Ctrl + Shift dan Enter secara bersamaan.

Mari kita terjemahkan bagian per bagian:

=IF(ROWS(E$6:E6)>$F$3;"";...)

• Ini adalah fungsi IF yang digunakan untuk menguji kondisi.
• ROWS(E$6:E6) menghitung jumlah baris dari baris 6 hingga baris saat ini (baris yang sedang dievaluasi), dengan menggunakan referensi relatif dan absolut.
• $F$3 adalah batas maksimum jumlah hasil yang ingin ditampilkan.
• Jadi, jika jumlah baris yang telah dihasilkan oleh rumus ini melebihi nilai di sel F3, maka rumus ini akan mengembalikan teks kosong ("").

INDEX(A$3:A$20; ...)

• Ini adalah fungsi INDEX yang digunakan untuk mengambil nilai dari rentang A3:A20 (dalam hal ini, mungkin beberapa nilai) berdasarkan indeks yang diberikan.

SMALL(IF(ISERROR(SEARCH($G$2;$B$3:$B$20)); ...))

• Ini adalah fungsi SMALL yang digunakan untuk mengambil nilai terkecil dari sebuah array.
• IF(ISERROR(SEARCH($G$2;$B$3:$B$20)) mencari apakah teks dalam sel G2 ada dalam rentang B3:B20. Fungsi SEARCH mengembalikan nilai kesalahan jika teks tidak ditemukan, dan ISERROR digunakan untuk menguji kesalahan ini.
• Jika teks G2 tidak ada dalam B3:B20, maka rumus ini akan mengembalikan indeks dari baris yang sesuai dengan sel B yang cocok. Ini dilakukan dengan menggunakan ROW($B$3:$B$20)-ROW($B$3)+1, yang menghasilkan indeks baris dalam rentang B3:B20 yang cocok.
• Jadi, jika Anda memiliki beberapa kesalahan atau nilai yang cocok, ini akan menghasilkan beberapa indeks dalam array.

ROWS(E$6:E6)

• Ini adalah bagian yang digunakan untuk menghitung baris saat ini yang telah dievaluasi oleh rumus sejauh ini. Ini akan memastikan bahwa kita hanya mengambil indeks yang sesuai dengan baris yang belum diproses.

Jadi, secara keseluruhan, rumus ini digunakan untuk menghasilkan serangkaian nilai dari rentang A3:A20 berdasarkan pencarian teks dari sel G2 dalam rentang B3:B20. Jumlah nilai yang dikembalikan tergantung pada jumlah baris yang telah dievaluasi oleh rumus dan dibatasi oleh nilai di sel F3. Rumus ini akan terus menghasilkan nilai-nilai ini seiring dengan penambahan baris ke sel E6:E yang dievaluasi.

Dan setelah rumus tersebut di drag ke samping dan ke bawah maka hasilnya adalah sebagai berikut:

Setiap metode memiliki kelebihan dan kekurangan sendiri, tergantung pada skenario dan preferensi Anda. Pilih metode yang paling sesuai dengan kebutuhan Anda dan tingkat keterampilan Excel Anda.

Jika Anda membutuhkan file contoh ini, file Excel bisa didownload di SINI.