Muatan :
- Kilasan tentang memfilter data dengan formula
- Konsep filtering dengan formula
- Cerita tentang sebuah kasus
- Filtering satu kriteria
- Memberi nilai default pada kriteria yang blank
- Filtering banyak kriteria
Kilasan
Sebuah laporan umumnya adalah cuplikan data tertentu, yaitu berdasarkan suatu kriteria. Kadang kala, yang dibutuhkan adalah sebuah tabel hasil cuplikan yang memberi keleluasaan dalam memilih kriteria cuplikan data. Umumnya, bagian ini berada pada sisi penyusun kriteria.Penyusun kriteria bisa bervariasi tergantung luasan laporan yang bisa diperoleh dari suatu data. Bagian penyusunan ini umumnya berisi nilai-nilai keterangan dari kode-kode yang menjadi suatu dimensi data, seperti nama produk ketimbang kode produk. Tidak jarang nilai kode-kode suatu dimensi tetap digunakan sebagai nilai kriteria yang harus diinput user untuk memperoleh cuplikan data, seperti kode jenis kelamin (L/P) ketimbang nilai laki-laki atau perempuan.
Output hasil filtering umumnya diletakkan di bawah area penyusunan kriteria. Hal ini tidaklah mengikat, karena seluruhnya sangat tergantung kebutuhan. Ada kalanya susunan kriteria perlu disembunyikan, misalkan karena bersifat tetap.
Konsep filtering dengan formula
Filtering data pada dasarnya adalah memilih data yang sesuai kriteria. Jadi proses utamanya adalah membaca seluruh data pada kolom-kolom yang menjadi kriteria, kemudian membandingkannya dengan nilai kriteria. Jika sesuai dengan seluruh kriteria, maka record data tersebut akan diambil.Ketika bekerja dengan formula, maka yang dibentuk adalah sebuah rangkaian relasi antara data dengan lokasi hasil. Oleh sebab itu, yang menjadi pokok utama dalam filtering dengan formula adalah mendapatkan posisi atau nilai rujukan suatu kolom yang bisa mewakili masing-masing record yang sesuai dengan seluruh kriteria. Misalnya adalah nomor index record data, yaitu posisi data dalam tabel data.
Formula yang sering digunakan adalah array formula yang berupa kombinasi fungsi SMALL atau LARGE dengan fungsi IF. Fungsi IF bertugas sebagai pemilah data berdasar seluruh kriteria, sekaligus pengambil nomor record data. Tentu saja posisi data yang sesuai kriteria tidak pasti berurutan membentuk suatu sequence, malah lebih sering berlompatan dari record tertentu ke record lain yang tak pasti jarak lompatnya. Fungsi Small atau Large bertugas sebagai penyusun data agar menjadi rapat, sehingga didapatkan output yang rapi dan padat. Fungsi Small akan menghasilkan output dari record dengan nomor index record terendah ke tertinggi, sedangkan fungsi Large adalah sebaliknya. Jadi, urutan output tetaplah berdasar urutan data, karena tidak ada proses pengurutan data.
Cerita tentang sebuah kasus
Suatu data order akan dijelajahi berdasar berbagai kriteria yang bersifat dinamis dalam penyusunan kombinasinya. Item kriteria adalah berupa nilai data (bukan nilai kode data), sehingga user dapat dengan mudah memilih sesuai kebutuhannya.Dari hasrat yang dijabarkan diatas dengan lugas dan simple tersebut, dapat disimpulkan bahwa dibutuhkan data berupa data order. Data tersebut adalah sebagai berikut :
Penyusunan bagian kriteria membutuhkan data referensi kode-kode dalam data order. Data referensi tersebut adalah :
Kemudian disusun bagian kriteria dan bagian result dari output proses filtering. Pada bagian kriteria, seluruh ruang input user yang berkenaan dengan dimensi utama data memanfaatkan fitur Data Validaion List untuk menampilkan item-item dimensi tersebut. Contohnya seperti dimensi Line Produksi pada gambar di bawah ini.
Data order tidak menyimpan data item, yang ada dalam pilihan di bagian kriteria, secara langsung. Oleh sebab itu dibutuhkan proses lookup ke data referensi terkait untuk mencari kodenya. Hasil lookup disimpan pada kolom helper bagian kriteria.
Untuk kriteria interval quantity dan interval price, diberi Data Validation untuk menjaga agar nilai pada field dari, pada kondisi yang lebih rendah atau sama dengan nilai pada field sampai. Pada field sampai juga diberi Data Validation agar nilainya pada kondisi yang lebih dari atau sama dengan nilai pada field dari.
Bagian output filtering terletak dibagian bawahnya, dan juga memiliki kolom helper. Kolom helper bagian output berisi posisi data yang sesuai kriteria pilihan user di data order. Selain itu, ketika seluruh kriteria masih kosong, akan menampilkan seluruh data order.
- Pemberitahuan !!!
- Untuk bahan belajar, maka output filtering diletakkan satu sheet bersama data order. Pada implementasinya, peletakan output filtering yang ideal adalah pada sheet lain. Bahkan, untuk menciptakan aplikasi multi tier, output filtering diletakkan pada workbook lain yang ada pada layer berikutnya.
Filtering satu kriteria
Agar mudah memahami konsep formula filtering, maka dimulai dengan proses filter berdasar satu kriteria saja, dan kriteria yang lainnya diabaikan. Dalam hal ini, akan digunakan kriteria warna, yang pilihannya jatuh pada warna Natural. Jadi, user telah memilih item Natural pada bagian kriteria warna di cell K9. Hasil filtering adalah sebagai berikut : Kriteria warna Natural memiliki kode NON yang tampak pada kolom helper bagian kriteria. Nilai pada kolom helper inilah yang akan dijadikan nilai kriteria dalam pencarian di data order.
Nomor index data yang sesuai kriteria didapatkan menggunakan array formula :
=SMALL(IF(RIGHT($B$2:$B$9,3)=$I$9,ROW($B$2:$B$9)-ROW($B$1)),ROW(1:1))
dengan bagian yang di-italic adalah dilihat dari sisi hasil filter (dari bagian kriteria maupun data set bagian result).
Bagian dari array formula :
- RIGHT($B$2:$B$9,3)=$I$9
- Proses perbandingan setiap item data order kolom product_id bagian warna, yaitu 3 karakter terkanan dari nilai data product_id, dengan nilai kriteria yang terkait secara langsung dengan data order, yaitu kode warna di cell I9. Hasilnya adalah array nilai TRUE untuk yang hasilnya benar sama, dan nilai FALSE untuk hasil perbandingan yang salah atau tidak sama.
- ROW($B$2:$B$9)-ROW($B$1)
- Proses penyusunan nomor index data di data order, yaitu nomor baris Excel data order tersebut dikurangi nomor baris header data order, sehingga didapat index record, dimulai dengan baris pertama data adalah record ber-index 1. Hasilnya adalah array nomor index seluruh data order
- IF(RIGHT($B$2:$B$9,3)=$I$9,ROW($B$2:$B$9)-ROW($B$1)
- Proses pengambilan nomor index data order yang sesuai kriteria. Bahasa manusia dari kalimat formula di atas adalah :
- jika sebuah data order bagian warna dari product_id adalah sama dengan warna di bagian kriteria, maka akan diambil nomor index datanya. Jika tidak sesuai kriteria, maka diisi dengan nilai default fungsi IF pada kondisi salah, yaitu nilai FALSE.
- Proses pengurutan array hasil bagian IF yang berupa nilai-nilai nomor index data untuk yang sesuai kriteria atau nilai FALSE untuk yang tidak sesuai kriteria. Pengurutan oleh fungsi SMALL dilakukan hanya pada data numerik dari yang terendah sampai tertinggi, dan nilai FALSE tidak ikut dalam proses pengurutan karena bukan data numerik. Hasil dari bagian ini adalah array nilai-nilai index data saja, dan telah terurut dari yang terendah sampai tertinggi.
- Proses pengambilan data yang telah diurutkan oleh bagian awal dari fungsi Small. Bagian ini adalah nomor urutan ke-sekian yang akan diambil sebagai output fungsi Small secara utuh. Jika bagian ini berupa array, maka hasil fungsi Small akan berupa array. Jika bagian ini adalah nilai tunggal, maka hasil fungsi small adalah nilai tunggal pada urutan ke-sekian sesuai nilai bagian ini. Fungsi Row digunakan untuk menghasilkan seri angka berurutan dari angka 1 yang akan memiliki arti data ke-1 pada bagian ini. Dengan relatifnya range dalam fungsi row (tanpa adanya karakter $) akan membuat range rujukan dalam fungsi row berubah ketika di-copy ke baris berikutnya, dan tersusunlah urutan data yang akan diambil dari hasil Small mulai dari urutan ke-1 (terendah) sampai urutan tertinggi. Ketika nomor urut hasil Row lebih banyak dari jumlah elemen array hasil fungsi Small bagian awal, maka hasil fungsi Small secara keseluruhan adalah error value #NUM!.
Pada cell I16, nilai Row adalah Row(4:4), dan data ke-4 pada hasil fungsi small bagian awal (step5) bernilai error value #NUM!, sehingga hasil di cell I16 adalah error value #NUM!. Error value ini bisa dijadikan tanda bahwa seluruh data yang harus diambil telah selesai diambil.
Berdasar hasil array formuka pada kolom helper ini, maka dapat diperoleh nilai-nilai data order yang menjadi output filtering, karena nilai di kolom helper adalah nomor index data order yang sesuai kriteria. Pada proses pengambilan data, perlu pengecekan nilai kolom helper berdasar status isi kolom helper apakah berupa data numerik atau bukan. Jika nilai kolom helper adalah data numerik, maka proses pengambilan data dapat dilakukan, misal dengan formula Index( Match ). Penyusunan nomor urut hasil filtering juga dapat dilakukan berdasar status nilai kolom helper tersebut. Berikut ini adalah formula penyusunan data hasil filtering untuk kolom No dan Inv_id. Kolom yang lain adalah penyesuaian rujukan range data pada sisi fungsi Index.
- Formula nomor urut : (pada cell H13)
=IF(ISNUMBER(I13),N(H12)+1,"")- dengan bagian yang di-bold adalah fungsi untuk mendapatkan nilai dari suatu cell. Karena header result berupa text (string), maka penjumlahan dengan angka 1 (+1) akan menghasilkan error value. Fungsi N akan mengambil nilai numerik dari suatu cell. Ketika cell berisi data text (string), maka hasil fungsi N adalah nilai 0.
- Formula ambil data order kolom Inv_id : (pada cell J13)
=IF(ISNUMBER($I13),INDEX(A$2:A$9,$I13),"")- dengan bagian yang di-bold adalah rujukan ke kolom terkait pada data order. Untuk mendapatkan nilai Inv_id, maka bagian ini merujuk ke kolom Inv_id data order yang berada di kolom A tabel order_detail. Pengaturan absolut reference (karakter $) akan memudahkan proses peng-copy-an formula ke cell hasil filter yang lainnya. bagian yang di-italic adalah rujukan ke nilai kolom helper pada baris tersebut, dengan referensi sisi kolom yang absolut. Hal ini akan menjaga ketepatan rujukan selalu ke kolom helper ketika formula di-copy ke cell hasil filter yang lain.
Memberi nilai default pada kriteria yang blank
Bagian kriteria yang memungkinkan terisi blank. Pemberian nilai default pada bagian kriteria, dapat dilakukan pada : - formula kolom helper bagian kriteria
- penambahan kriteria terkait pada bagian IF array formula filtering di kolom helper bagian hasil filter
Nilai blank dapat diartikan dengan banyak hal, diantaranya :
- Tidak ada hasil filter
- Jika data order bagian terkait dengan kriteria tersebut tidak ada yang blank, maka tidak perlu ada perubahan pada formula-formula kolom helper di seluruh bagian.
- Jika ada nilai blank pada data order di bagian terkait dengan kriteria, dan tidak akan ditampilkan, maka perubahan dapat dilakukan pada : (pilih salah satu)
- formula kolom helper bagian kriteria yang diubah menjadi menghasilkan nilai tertentu yang tidak ada di data order. Misal untuk krtieria warna, data order mungkin ada yang blank dan kriteria bernilai blank tidak akan menampilkan hasil filter, maka formula I9 yang awalnya adalah :
=IF(LEN(K9),INDEX(OFFSET(dtWarna,0,-1),MATCH(K9,dtWarna,0)),"")
maka bagian "" diganti suatu nilai yang tidak mungkin ada di data order, misalkan "XXX". - array formula kolom helper hasil pada bagian perbandingan kriteria, ditambahkan kriteria baru memanfaatkan jumlah karakter nilai kriteria. Misal pada kriteria warna, yang tidak mungkin menampilkan data order yang berwarna blank (karena dimisalkan ada warna blank di data order), maka bagian perbandingan kriteria array formula filtering yang awalnya :
RIGHT($B$2:$B$9,3)=$I$9
diubah menjadi :
(RIGHT($B$2:$B$9,3)=$I$9)*(LEN($I$9)>0)
yang berarti bahwa data order bagian warna sama dengan nilai kriteria warna dan kriteria tidak blank. Ketika kriteria bernilai blank, maka seluruh hasil bagian IF akan bernilai FALSE dan hasil fungsi Small bagian awal (yang mengurutkan) akan bernilai error value #NUM! seluruhnya, yang berarti tidak ada data yang hasil.
- formula kolom helper bagian kriteria yang diubah menjadi menghasilkan nilai tertentu yang tidak ada di data order. Misal untuk krtieria warna, data order mungkin ada yang blank dan kriteria bernilai blank tidak akan menampilkan hasil filter, maka formula I9 yang awalnya adalah :
- Menghasilkan seperti difilter dengan satu kriteria tertentu
- Maka dapat dilakukan pada : (pilih salah satu)
- formula kolom helper bagian kriteria agar menghasilkan nilai tertentu tersebut. Misal pada kriteria warna, jika kriteria bernilai blank maka setara dengan memfilter dengan warna coklat (Brown) yang memiliki kode warna BRO, maka pada formula cell I9 bagian "" diganti dengan "BRO".
- formula kolom helper hasil bagian perbandingan data, agar merujuk ke suatu nilai hasil kondisi yang jika nilai kriteria adalah blank akan menghasilkan nilai kriteria 'BRO', dan jika tidak blank menghasilkan nilai kriteria yang di-entry oleh user. Misal untuk kriteria warna, maka bagian :
RIGHT($B$2:$B$9,3)=$I$9
diubah menjadi :
RIGHT($B$2:$B$9,3)=IF(LEN($I$9),$I$9,"BRO")
- Menghasilkan seluruh warna, yang mirip dengan status diabaikan
- Maka pengubahan hanya bisa dilakukan pada array formula hasil bagian perbandingan kriteria, dengan menambah kriteria baru memanfaatkan jumlah karakter kriteria dalam hubungan 'ATAU ~ OR ~ +', sehingga bagian tersebut menjadi : (RIGHT($B$2:$B$9,3)=$I$9)+(LEN($I$9)=0) sehingga ketika kriteria bernilai blank, maka bagian yang ditambahkan pasti akan bernilai TRUE dan dalam hubungan OR dengan tanda + akan membuat apapun hasil (RIGHT($B$2:$B$9,3)=$I$9) tidak akan berpengaruh. Ketika kriteria bukan blank, maka yang berpengaruh hanya bagian (RIGHT($B$2:$B$9,3)=$I$9).
Filtering banyak kriteria
Untuk lebih jelasnya tentang filter berdasar banyak kriteria, maka contoh kasus di atas akan ditambah filternya berdasar Jenis Produk. Nilai kriteria kode jenis produk ada di cell I7, dan bagian product_id di data order yang menunjukkan jenis produk adalah pada karakter ke-3 sampai karakter ke-5 (3 karakter). Kode jenis produk sejatinya adalah bertipe numerik.Array formula filtering :
- =SMALL(IF(RIGHT($B$2:$B$9,3)=$I$9,ROW($B$2:$B$9)-ROW($B$1)
- RIGHT($B$2:$B$9,3)=$I$9
- --MID($B$2:$B$9,3,3)=$I$7
- (RIGHT($B$2:$B$9,3)=$I$9)*(--MID($B$2:$B$9,3,3)=$I$7)
Array formula pada kolom helper bagian hasil selengkapnya akan menjadi :
- =SMALL(IF((RIGHT($B$2:$B$9,3)=$I$9)*(--MID($B$2:$B$9,3,3)=$I$7),ROW($B$2:$B$9)-ROW($B$1)
Penggunaan karakter -- pada perbandingan kriteria jenis produk adalah untuk mengkonversi tipe data nilai bagian jenis produk dari data order hasil fungsi MID yang masih bertipe text (string) menjadi bertipe numerik, sehingga tipe datanya akan sama dengan tipe data nilai kriteria.
Closing :
Formula filtering memiliki titik berat pada penyusunan kriteria dan penentuan nomor index data dalam fungsi IF. Penyusunan kriteria dilakukan per kriteria yang dibutuhkan secara utuh, dengan mengatur hubungan antar kriteria dan kondisi nilai kriteria.Formula filtering tidak selalu harus mendapatkan nomor index data saja. Kadang kala dibutuhkan hasil berupa nilai composite key yang didalamnya tetap memiliki nomor index data.
File(s) :
Coretan terkait :
- Array Formulas. Kenalan yuuk !
- LookUp ke kiri atau ke kanan untuk satu kriteria
- LookUp banyak kriteria
Sangat Mencerahkan....
BalasHapusContoh Filenya Mr.Kid bisa dilampirkan? biar lebih mudah di cernah penjelasan di atas
Tks
Contoh filenya sudah disediakan di bagian File(s). Silakan diikuti link unduh-nya.
Hapus:)
@Arman : File sudah tersedia untuk diunduh. Link untuk mengunduh file ada di blok File(s) sebelum blok Coretan terkait.
BalasHapusSilakan diklik link dengan teks :
Array formula filtering pada sheet filtering
dia ats
super Mr. KID
BalasHapusmakasih :D
Sama-sama. Moga-moga bermanfaat dan dibagikan ke sekitarnya ya...
Hapus:)
mantabs gan bener2 bikin cerah. (Y)
BalasHapusMakasih..
HapusMoga-moga yang tercerahkan bisa menyebarkan manfaatnya ke sekitarnya ya...
:)
Gan saya mau tanya, gimana mau menampilkan data dengan satu kriteria, misalkan saya mau menampilkan daftar tagihan si A..jadi, bila kriteria A muncul maka akan menampilkan Semua data yg berisikan tentang si A. Mohon bantuan gan.
BalasHapusCoretan diatas sudah menjelaskan cara untuk menyusun sebuah tabel baru yang menampilkan records dengan kriteria tertentu saja yang diinginkan user. Syarat utamanya adalah susunan tabel sumber data yang berkaidah tabel database, seperti tidak ada cell kosong bahkan baris kosong, tidak ada kolom kosong, dan sebagainya. Selain itu juga dibutuhkan kesesuaian penulisan kriteria oleh user yang sama dengan apa yang ada dalam tabel sumber data.
HapusCoba file contoh yang disediakan diunduh lebih dulu. Bandingkan bentuk tabel dari sumber data di file contoh dengan tabel data milik Anda. Bisa jadi jumlah kolom dan barisnya berbeda, tetapi pada file contoh tidak ada cell kosong di kolom-kolom yang menjadi kunci kriteria.
Misalkan saya punya gambar seperti ini :
Hapushttps://lh3.googleusercontent.com/-QDmkQblXloc/Vh-emDxb78I/AAAAAAAACGA/ZmYjk3JL6CI/s912-Ic42/Excel%252520tanya.jpg
Cara merubahnya biar bisa memfilter dengan kriteria BULAN saja bagaimana? (laporan ada di sheet lain), coz saya masih bingung dengan rumus berikut (saya lihat rumus ini hasil gabungan semua kriteria yang ada di sheet ref, apa harus seperti itu?)
=SMALL(IF(((INT($A$2:$A$9/1000)=$I$5)+($I$5=""))*((LEFT($B$2:$B$9;2)=$I$6)+($I$6=""))*((--MID($B$2:$B$9;3;3)=$I$7)+($I$7=""))*((MID($B$2:$B$9;6;2)=$I$8)+($I$8=""))*((RIGHT($B$2:$B$9;3)=$I$9)+($I$9=""));ROW($A$2:$A$9)-ROW($A$1));ROW(1:1))
Hai,
HapusBagian IF diisi dengan kriteria yang dibutuhkan. Jika hanya ada 1 kriteria yang dibutuhkan, maka bagian IF berisi 1 kriteria saja. Lihat bagian Filtering satu kriteria. Jika dibutuhkan banyak kriteria, maka bagian IF akan berisi banyak kriteria sesuai yang dibutuhkan. Lihat bagian Filtering banyak kriteria.
Jika Anda membutuhkan 1 kriteria filter berupa bulan tertentu, maka cukup Anda susun bunyi kriteria berdasar bulan tersebut. Bisa jadi Anda perlu berkreatif ria memanfaatkan fungsi-fungsi Excel yang berkaitan dengan pengolahan bulan tertentu tersebut. Misalnya fungsi Month dan Year atau Text dan sebagainya.
Regard,
Kid
pengen nanya masbro... gmana kalau pengen penulisan fungsi IF menjadi blank(kosong) ketika sumber data dari rumus tersebut tidak ada
BalasHapusSebuah cells berformula tidak bisa menghasilkan blank cells alias NULL. Sebuah formula minimal menghasilkan NULLSTRING yang sering dinyatakan dengan bunyi "" dalam suatu formula.
Hapus=IF( kondisi , nilai_terpenuhi , "" )
jika kondisi terpenuhi, maka cells tersebut akan berisi nilai_terpenuhi
jika kondisi tidak terpenuhi, maka cells tersebut akan berisi NULLSTRING, dan cells tersebut tidaklah blank.
Fitur Excel seperti Filter dan Find akan menyatakan bahwa cells berisi NULLSTRING termasuk blank cells. Begitu juga dengan fungsi CountBlank akan menyatakan cells berisi NULLSTRING termasuk blank cells.
tengkiu bgt masbro.. ats penjelasannya... ohya pngen nanya lagi... apakah fungsi IF bisa digunakan berulang dalam satu sel, bagaimana contoh penulisan formulanya..
Hapusuntuk formula AVERAGE kondisi NULLSTRING sepertinya tidak dianggap blank cells sehingga perhitungan average jd tdk valid(menjadi lebih kecil),. apakah ada formula yg bisa digunakan/disematkan untuk mengatasi hal tersebut... Mohon pencerahannya.
HapusFungsi average akan mengabaikan seluruh nilai berdatatype text. Jadi average akan bekerja baik walau dalam data ada nilai NULLSTRING.
HapusYang perlu diingat, NULLSTRING berbeda dengan NULL. NULL alias blank bukan NOL.
saya punya contoh kasus masbro, smoga berkenan berbagi, : jika data pada sel yang ditentukan tidak ada (blank), maka mengambil data dari sel lain yang ditentukan.... misal : jika data di F9 tdk ada maka mengambil data dari F10,.. kira-kira penulisan formula yang cocok bagaimana masbro..
BalasHapusPada cell hasil (selain cell F9 maupun cell F10) diberi formula :
Hapus=if( f9="",f10,f9 )
Mantapz masbro formulanya.. trimsz bnget atas ilmunya... senang berkunjung keblog ini...
HapusMas bro mau tanya untuk kasus seperti ini... saya punya database
BalasHapusBuah Warna
Apel Merah
Mangga Hijau
Anggur Ungu
Kemudian saya berada di sheet lain, misalkan di kolom A2 saya ketik APEL, maka di B2 otomatis muncul MERAH... pertanyaan saya, bagaimana caranya ketika saya ubah warna MERAH mejadi BIRU di kolom B2, secara otomtis database warna apel yang berada di sheet lain akan berubah juga yang tadinya apel berwarna MERAH menjadi BIRU.
terima kasih
Hai ADR,
HapusKatakan di sheet Data berisi data 2 kolom hasil input
Katakan juga di sheet Tampil cell A2 berisi teks nama buah inputan user seperti kata Apel. Cell B2 menggunakan formula vLookUp dan menghasilkan kata Merah.
Jika kata Merah milik buah Apel di sheet Data diubah menjadi Biru, maka di sheet Tampil cell B2 akan berubah juga menjadi kata Biru ketika setting kalkulasi adalah Automatic. Artinya, formula akan menampilkan hasil komputasinya.
Ketika di sheet Tampil cell B2 yang sudah menjadi Biru (akibat di sheet Data telah diubah menjadi Biru) diisi manual (diketik oleh user) dengan kata Hijau, maka formula di cell B2 sheet Tampil akan hilang relasinya ke sheet Data. Artinya, sheet Tampil tidak punya relasi apapun dengan sheet Data, sehingga pengubahan di sheet Data tidak akan membuat sheet Tampil menampilkan nilai data sesuai sheet Data. Ini adalah karakteristik formula.
Jika penulisan di cell B2 sheet Tampil ingin membuat pengubahan di sheet Data kemudian pengubahan di sheet Data secara langsung juga tetap bisa tampil dengan baik di sheet Tampil, maka gunakanlah otomasi dengan VBA. Silakan mempelajari VBA yang banyak sekali di internet. Salah satunya adalah di blog milis Belajar-Excel yang dimulai dari sini :
http://b-excel.blogspot.com/2013/06/belajarvba-000-kenalan.html
atau coba tanyakan ke milis-milis Excel tentang VBA untuk update data. Salah satu milis tetang Excel adalah milis Belajar-Excel. Cara subscribe dan informasi lain bisa didapat disini :
https://groups.yahoo.com/neo/groups/belajar-excel/info
Wassalam,
Kid.
Sangat membantu.
BalasHapusO ya, saya punya Data Pemilih, Kasusnya adalah bagaimana cara mengambil data yang berumur 17 th atau sudah menikah berhak adalah yang berhak memilih. Bagaimana Rumusnya Gan?
Hai Topani Sahara,
HapusPada array formula filtering, pengubahan sering dilakukan pada bagian penyusun kriterianya, yaitu pada bagian pengkondisian dalam fungsi IF. Secara umum, susunannya adalah :
=Small( IF( (kriteria1)*(kriteria2)*()*(kriteriaN) , nilai_yang_diurutkan ) , nomor_record_hasil_yang_diambil )
Notasi * melambangkan operator AND alias harus terpenuhi.
Notasi * bisa diganti menjadi + jika kondisinya adalah cukup minimal satu kriteria terpenuhi dari sekian banyak kriteria yang ada
Pada contoh kasus di atas (kasus filter dengan 1 kriteria), bagian ini adalah bagian yang berbunyi :
RIGHT($B$2:$B$9,3)=$I$9
Pada kasus Anda, kriterianya adalah :
1. berumur 17 th (ke atas)
2. sudah menikah
Dengan kondisi : salah satu dari kedua kriteria tersebut terpenuhi
Notasi yang sesuai : +
Bunyi kriteria :
(kriteria1) + (kriteria2)
berarti :
( data_umur >= 17 ) + ( data_status_pernikahan = nilai_status_menikah )
.... bagaimana dengan yang sudah pernah menikah tapi belum berumur 17 tahun ya ....
;)
Wassalam,
Kid.
Thanks gan respon nya
Hapus".... bagaimana dengan yang sudah pernah menikah tapi belum berumur 17 tahun ya ...."
Untuk yang belum berumur 17 tahun tapi sudah menikah ya tetap memilih,
Bisa minta contoh filenya gan? soalnya saya agak kurang memahami,
Saya tunggu ya gan. Thanks.
thanks buat infonya sob,, sangat bermanfaat sekali
BalasHapusMas, mau tanya nih, misal saya punya data
BalasHapus6 7 5 6 4 3 6 9 3 1 kemudian ingin saya ubah hNy dari kanan ke kiri tpi tidak merubah besar ke kecil hanya di balik saja, sperti 1 3 9 6 3 4 6 5 7 6 seperti itu, bagaimana caranya ya?
Hai,
HapusKhusus untuk nilai data berisi angka saja.
Misal cells A1 berisi angka 123456789012345
Array formula cells B1 :
=IFERROR(RIGHT(REPT(0,10)&SUM(IFERROR(MID(A1,ROW($11:$20),1)*10^(ROW($1:$10)-1),"")),MIN(10,LEN(A1)-10)),"")
& RIGHT(REPT(0,10)&SUM(IFERROR(MID(A1,ROW($1:$10),1)*10^(ROW($1:$10)-1),"")),MIN(10,LEN(A1)))
Mau nanya bang..
BalasHapusMisalkan ada suatu tabel yang berisi nama dan tahun kelahiran. Bagaimana melakukan filter untuk mengumpulkan nama2 dg kelahiran tahun tertentu dg menggunakan rumus? Bkn menggunakan filter manual.
Makasih..
Pak unknown saya rasa penjelasan om kid sudah sperti pertanyaan bapak deh.
HapusHanya saja bagian yg logikanya (if) dirubah berdasarkan tahun yg dicari. Fungsi laiinnya sama sperti yg di posting master kid.
Kira-kira sperti itu :)1
Betul mas Fahmi.
HapusDemikianlah.
ane coba download tapi gak bisa gan, tolong diperbaiki link nya semoga blog agan makin rame
BalasHapusHai,
HapusLink sudah dicoba ulang dan masih tetap baik seperti sediakala.
Terimakasih.
Regards,
Kid
terimakasih banyak pak, semoga ilmunya menjadi berkahhh
BalasHapusaminnnnnnnnnnnnnnnn
Sama-sama. Semoga bermanfaat. Aamin.
HapusMr. Kid difile yang di unduh pada bagian inputan Price range dan Quantity range kenapa tidak ada perubahan ya?
Hapussaya isi berapapun ga berubah padahal kalo dilihat secara manual harusnya berubah..
Oh bagian kriteria berdasar interval nilai tersebut tidak dibahas di coretan ini. Jadi formula dalam file tidak ada yang merujuk ke area range interval nilai.
Hapuspak mau tanya ,, saat saya cek rumus bapak kenapa ada tand { sebelum = , bagaimana caranya untuk menampilkan itu ?
BalasHapusItu tanda array.
HapusSila lihat di :
http://excel-mr-kid.blogspot.com/2011/03/array-formula-kenalan-yuuuk.html
thanks bro, ijin unduh buat belajar.
BalasHapusBro Ijin unduh buat nambah ilmu... thk
BalasHapusMana linknya ya bro ?
BalasHapusMas ijin bertanya semoga bisa di bantu.
BalasHapusSaya punya kasus 2 data validation dalam bentuk list.
List pertama jenis (Alat tulis;buku).
List kedua pilihan (Pulpen;pensil;buku tulis;novel;komik).
Bagaimana caranya jika saya klik alat tulis di list pertama yg muncul di list kedua cuma pulpen dan pensil jadi buku tulis novel dan komik tidak muncul.
Apakah di excel bisa seperti itu mas?
Mohon bantuannya. Trimakasih
mas mau tanya, klo rumus filter berakhiran angka gimna ya?
BalasHapuscontoh: dalam kolom A terdapat angka 102, 101, 242, 333, 352 dst, kemudian saya mau memfilter nomor yg berakhiran angka 2 seeperti: 102, 242, 352 dst yg berakhiran 2.