Jumat, 22 April 2011

Formula filter data (filtering formula)


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.
    Hasilnya adalah array nilai nomor index data untuk yang sesuai kriteria, dan FALSE untuk yang tidak sesuai kriteria.


  • SMALL(IF(RIGHT($B$2:$B$9,3)=$I$9,ROW($B$2:$B$9)-ROW($B$1)
  • ),
      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.


  • ROW(1:1)
      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!.
  • Gambar berikut ini adalah proses kalkulasi yang terjadi pada cell I13.
    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 :
    1. 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.
    2. 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")
    3. 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).
    Seluruh pengubahan pada bagian array formula pada kasus pemberian nilai default ini, termasuk proses filter dengan formula menggunakan banyak kriteria.


    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)
    ),ROW(1:1))diubah pada bagian :
      RIGHT($B$2:$B$9,3)=$I$9
    dengan menambahkan kriteria berdasar jenis produk berupa :
      --MID($B$2:$B$9,3,3)=$I$7
    dalam hubungan 'dan' yang bisa diwakili oleh karakter asterik (*), sehingga susunannya menjadi :
      (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)
    ),ROW(1: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 :



    43 komentar:

    1. Sangat Mencerahkan....
      Contoh Filenya Mr.Kid bisa dilampirkan? biar lebih mudah di cernah penjelasan di atas

      Tks

      BalasHapus
      Balasan
      1. Contoh filenya sudah disediakan di bagian File(s). Silakan diikuti link unduh-nya.
        :)

        Hapus
    2. @Arman : File sudah tersedia untuk diunduh. Link untuk mengunduh file ada di blok File(s) sebelum blok Coretan terkait.
      Silakan diklik link dengan teks :
      Array formula filtering pada sheet filtering
      dia ats

      BalasHapus
    3. Balasan
      1. Sama-sama. Moga-moga bermanfaat dan dibagikan ke sekitarnya ya...

        :)

        Hapus
    4. mantabs gan bener2 bikin cerah. (Y)

      BalasHapus
      Balasan
      1. Makasih..
        Moga-moga yang tercerahkan bisa menyebarkan manfaatnya ke sekitarnya ya...

        :)

        Hapus
    5. 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.

      BalasHapus
      Balasan
      1. Coretan 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.

        Coba 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.

        Hapus
      2. Misalkan saya punya gambar seperti ini :

        https://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))

        Hapus
      3. Hai,

        Bagian 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

        Hapus
    6. pengen nanya masbro... gmana kalau pengen penulisan fungsi IF menjadi blank(kosong) ketika sumber data dari rumus tersebut tidak ada

      BalasHapus
      Balasan
      1. Sebuah cells berformula tidak bisa menghasilkan blank cells alias NULL. Sebuah formula minimal menghasilkan NULLSTRING yang sering dinyatakan dengan bunyi "" dalam suatu formula.

        =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.




        Hapus
      2. tengkiu bgt masbro.. ats penjelasannya... ohya pngen nanya lagi... apakah fungsi IF bisa digunakan berulang dalam satu sel, bagaimana contoh penulisan formulanya..

        Hapus
      3. untuk 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.

        Hapus
      4. Fungsi average akan mengabaikan seluruh nilai berdatatype text. Jadi average akan bekerja baik walau dalam data ada nilai NULLSTRING.
        Yang perlu diingat, NULLSTRING berbeda dengan NULL. NULL alias blank bukan NOL.

        Hapus
    7. 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..

      BalasHapus
      Balasan
      1. Pada cell hasil (selain cell F9 maupun cell F10) diberi formula :
        =if( f9="",f10,f9 )

        Hapus
      2. Mantapz masbro formulanya.. trimsz bnget atas ilmunya... senang berkunjung keblog ini...

        Hapus
    8. Mas bro mau tanya untuk kasus seperti ini... saya punya database

      Buah 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

      BalasHapus
      Balasan
      1. Hai ADR,

        Katakan 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.




        Hapus
    9. Sangat membantu.
      O 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?

      BalasHapus
      Balasan
      1. Hai Topani Sahara,

        Pada 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.

        Hapus
      2. Thanks gan respon nya

        ".... 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.

        Hapus
    10. thanks buat infonya sob,, sangat bermanfaat sekali

      BalasHapus
    11. Mas, mau tanya nih, misal saya punya data
      6 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?

      BalasHapus
      Balasan
      1. Hai,

        Khusus 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)))




        Hapus
    12. Mau nanya bang..

      Misalkan 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..

      BalasHapus
      Balasan
      1. Pak unknown saya rasa penjelasan om kid sudah sperti pertanyaan bapak deh.
        Hanya saja bagian yg logikanya (if) dirubah berdasarkan tahun yg dicari. Fungsi laiinnya sama sperti yg di posting master kid.

        Kira-kira sperti itu :)1

        Hapus
      2. Betul mas Fahmi.
        Demikianlah.

        Hapus
    13. ane coba download tapi gak bisa gan, tolong diperbaiki link nya semoga blog agan makin rame

      BalasHapus
      Balasan
      1. Hai,

        Link sudah dicoba ulang dan masih tetap baik seperti sediakala.

        Terimakasih.

        Regards,
        Kid

        Hapus
    14. terimakasih banyak pak, semoga ilmunya menjadi berkahhh

      aminnnnnnnnnnnnnnnn

      BalasHapus
      Balasan
      1. Sama-sama. Semoga bermanfaat. Aamin.

        Hapus
      2. Mr. Kid difile yang di unduh pada bagian inputan Price range dan Quantity range kenapa tidak ada perubahan ya?
        saya isi berapapun ga berubah padahal kalo dilihat secara manual harusnya berubah..

        Hapus
      3. 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.

        Hapus
    15. pak mau tanya ,, saat saya cek rumus bapak kenapa ada tand { sebelum = , bagaimana caranya untuk menampilkan itu ?

      BalasHapus
      Balasan
      1. Itu tanda array.

        Sila lihat di :
        http://excel-mr-kid.blogspot.com/2011/03/array-formula-kenalan-yuuuk.html

        Hapus
    16. thanks bro, ijin unduh buat belajar.

      BalasHapus
    17. Bro Ijin unduh buat nambah ilmu... thk

      BalasHapus
    18. Mas ijin bertanya semoga bisa di bantu.
      Saya 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

      BalasHapus
    19. mas mau tanya, klo rumus filter berakhiran angka gimna ya?
      contoh: 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.

      BalasHapus