Filter Excel Tingkat Lanjut

1. Mempersiapkan data sebagai database Baris pertama (A1:D1) adalah judul data. Baris-baris berikutnya berisi data. Tidak boleh ada baris yang kosong diantara data yg ada dalam database. Baris kosong pada akhir data dan kolom paling kanan.

2. Mempersiapkan Range untuk Kriteria Dalam range yang kita tetapkan untuk kriteria, kita memasukkan aturan data yang akan ditampilkan apabila kita mem-filter data. Kita dapat menggunakan satu atau lebih kriteria. Dalam contoh ini, sel F1:F2 adalah range untuk kriteria. Judul kita letakkan pada sel F1. Dan sel F2 berisi kriterianya. Tanda (operator) > (lebih besar dari) dipergunakan bersama-sama dengan angka 500. Setelah diaplikasikan, maka hanya pesanan yang memiliki total penjualan lebih besar dari Rp 500 akan ditampilkan. Operator-operator lainnya adalah: < lebih kecil dari <= lebih kecil dan sama dengan >= Lebih besar dan sama dengan <> tidak sama dengan

3. Mempersiapkan Range untuk hasil pengaplikasian filter (ekstrak data) Jika anda ingin mengcopykan data ke lokasi lainnya, maka anda harus menetapkan kolom-kolom yang akan anda ekstrak. Jika anda ingin seluruh kolom, maka anda harus mengosongkan range tersebut.. Pilih sel di bagian kiri atas range untuk data yang akan diambil. Ketik judul untuk kolom yang ingin Anda ekstrak. Harus sesuai dengan kolom judul yang sama, dalam ejaan dan tanda baca. Urutan Kolom bolehberbeda, dan salah satu atau semua kolom dapat diikutsertakan.

4. Mengaplikasikan Filter Pilih sebuah sel dalam database. Dari Menu Data menu, pilih Filter, Advanced Filter. (Dalam Excel 2007, klik tab Data pada Ribbon, kemudian klik Advanced Filter.) Anda boleh mem filter daftar di data asli atau copy hasilnya ke lokasi lain. Excel seharusnya otomatis mendeteksi range data. Jika tidak, anda dapat memilih sel-sel yang akan diikutkan dari worksheet. Pilih range kriteria pada worksheet Jika anda mengcopykan ke lokasi lain, maka pilih sel awal untuk menempatkan data copy anda Catatan: Jika anda memilih copy ke lokasi lain (copy to another location), maka seluruh sel dibawa range yang diextrak akan dihapus jika menggunakan Advanced Filter. klik OK

Menjumlahkan 5 Angka terbesar

Untuk menjumlahkan 5 angka terbesar sebuah data yang terletak dikolom tertentu maka kita dapat menggunakan gabungan antara fungsi SUM dengan fungsi LARGE. Sebagai contoh lihat tabel dibawah berikut ini. Rumusyang digunakan adalah

=SUM(LARGE(A:A,{1,2,3,4,5}))

Yang artinya jumlahkan data di kolom A hanya 5 angka terbesar saja.

Filter dengan Kriteria

Membuat Databasenya

Contoh yng digunakan dalam artikel ini dapat pada gambar 1. Databasenya ada dikolom A:D dan range kriteria mulai dari sel F1.

Catatan: Jika anda memakai rumus sebagai kriteria, maka kolom judul dikosongkan, atau gunakan judul yang tidak sama dengan judul tabel manapun.

Gambar 1

Meng-Extract lima record tertinggi

F1: Judul Kriteria – Biarkan Kosong
F2: Kriteria: =D2>=LARGE($D$2:$D$28,5)

Fungsi LARGE akan menghasilkan lima nilai tertinggi dari range D2:D28.
Referensi absolut diberlakukan untuk range — $D$2:$D$28.

Sel D2, sel pertama dalam kolom total, adalah sebagai pembanding awal. Referensi relatif dipergunakan untuk sel D2 ini. Baris-baris dengan total yang lebih besar dan sama dengan lima angka terbesar akan lolos terhadap filter ini. (Catatan: Jika ada yang sama maka hasilnya akan lebih dari lima data)

Gambar 2

Filter untuk data yang sama dalam dua kolom F1: Judul Kriteria – Biarkan Kosong F2: kriteria: =C2=D2 Untuk setiap record, tanggal Order dibandingkan dengan tanggal Pengiriman. Referensi sel keduannya relatif — C2 and D2. Baris tanggal yang sama akan lolos dari filter ini.

Filter untuk data yang tidak sama dari dua kolom F1: Judul Kriteria – Biarkan Kosong F2: kriteria: =C2<>D2 Untuk setiap record, jumlah Invoice dibandingkan dengan jumlah Pembayaran. Dalam filter ini dipergunakan referensi relatif — C2 dan D2. Baris-baris yang jumlahnya tidak sama akan ditampilkan. Catatan: Anda dapat menggunakan judul kolom sebagai rumus kriteria, sebagai ganti dari referensi sel. Formula akan menampilkan #NAME? atau #VALUE!, akan tetapi filter ini akan berfungsi dengan benar. Sebagai contoh: F2: Kriteria: =Invoice<>Paid

Temukan angka-angka dari sel yang berisi angka (nilai) F1: Judul Kriteria – Biarkan Kosong F2: Kriteria: =ISNUMBER(FIND(“8”,C2)) Fungsi FIND akan mencari string “8” dalam sel C2. Jika ditemukan angka tersebut, naja hasilnya aalah lokasi yang mengandung angka 8. Jika tidak ditemukan maka hasilnya adalah Error.

Filter Baris-baris dengan sel yang Kosong F1: Judul Kriteria – Biarkan Kosong F2: Kriteria: =C2=”” Untuk setiap record, nilai dalam kolom C akan diperiksa. Jika ada yang kosong, maka rekord akan lolos dari filter.

Filter Item dalam sebuah Daftar (List) Kolom H: Masukkan daftar nama item yang akan dicari F1: Judul Kriteria – Biarkan Kosong F2: Kriteria: =COUNTIF(H:H,C2) Untuk setiap record, nilai dalam kolom C akan diperiksa. Jika ditemukan data yang sesuai dengan kolom H, maka record lolos dari filter. Untuk File yang dipergunakan dapat di ambil dari workbook ini, klik disini.

Diterjemahkan dari Excel Tips http://www.contextures.com/