Trik menyembunyikan karakter dengan Custom Format

Assalamualaikum.

Pernah tau kenapa walaupun tertulis "ini data teks" tapi tiap kolom dari sekumpulan data diatas tetap bisa dijumlahkan tanpa ada error ? Pernah tau bahwa data secara default data yang bertipe numerik selalu berposisi rata kanan ?

Jawaban nya adalah : karena isi cell dari tiap-tiap kolom pada data diatas sudah di akalin :). Yes.. soal akal-meng akali memang akan banyak dibahas. Nama keren nya tips, trik, hack atau apalah. Nah untuk trik kali ini akan dibahas mengenai bagaimana Custom Format dapat menyembunyikan isi dari sebuah Cell.

Pada bahasan kali ini juga menunjukan bahwa Format sebetulnya ada berada "dilapisan atas" pada isi dari sebuah Cell. Dengan kata lain, Format hanyalah sebuah "Tabir" yang menutupi. Nah tabir inilah yang akan kita gunakan melalui cara Custom Formatting.

Ok langsung saja ya ? pertama, siapkan dulu data set nya. Seperti pada contoh gambar animasi diatas ada pada Range E3:K16. Pada data set ini terdapat nilai 0 (Nol) yang akan kita tutupi dengan Custom Format tadi. Terlihat pada gambar dibawah adalah data set sebelum dilakukan Custom Format. Nilai 0 terlihat sudah di highlight dengan warna pink.

Data asli sebelum menggunakan Custom Format

Langkah selanjutnya adalah blok semua data set nya (Range E3:K16), klik kanan dan pilih Format Cell atau shortcut Ctrl + 1, pilih Custom Format. Dan pada kotak Type : tuliskan [=0]"ini data teks";General seperti pada contoh dibawah dan klik OK. Dan lihat hasil nya :D

Semoga bermanfaat.

Wassalam.

Tips Data - Rumus Sum yang dinamis

Assalamualaikum.

Pernah mengalami laporan yang kita buat, setelah di edit / ditambah data baru, angka nya tidak terupdate karena kita lupa mengupdate rumus Sum / Penjumlahan nya seperti pada contoh Gbr 1 dan Gbr 2 dibawah ? 

Gbr 1 - Rumus Sum menjumlahkan isi Range B2:B5

Gbr 2 - Data ditambahkan, rumus Sum tidak berubah

Ternyata masalah seperti ini bisa di "akali" dengan menggunakan Named Range / Range yang diberi nama. Kalau rumus nya pada contoh Gbr 1 diatas adalah =Sum(B2:B5), maka yang perlu di "akali" hanyalah bagian B5 nya saja.

Pertama, buka Name Manager (shortcut : Ctrl + F3). Berikan nama pada kotak Name:. Dan pada kotak Refers to klik dan pilih cell paling ujung pada kolom B yang akan dijumlahkan, dalam contoh Gbr 3 dibawah merujuk pada Cell B6. 

Gbr 3 - Name Manager

Jangan lupa hilangkan tanda $. Karena yang kita butuhkan adalah rumus yang dinamis bukan statis. Kalau sudah, klik OK. Selanjutnya rumus Sum diganti dari =Sum(B2:B5) ke =Sum(B2:Nama Range nya) seperti pada contoh Gbr 4 dibawah.

Dan Insya Allah sekarang rumus kita menjadi rumus Sum yang dinamis :). Berapapun data yang ditambahkan kita tidak perlu khawatir angka nya tidak berubah.

Semoga bermanfaat.

Wassalam.

Gbr 4 - Rumus dinamis dengan mengganti sesuai Named Range

Dasar Macro - menggunakan Tanggal sebagai kriteria (Advanced Filter bag. 3)

Bismillah..

Setelah sebelumnya membahas mengenai dasar penggunaan Macro Advanced Filter disini dan disini, kali ini saya akan coba bahas bagaimana menggunakan Macro dan Advanced Filter untuk memfilter data bertipe tanggal.

Untuk diperhatikan bahwa menggunakan Advanced Filter (juga Data Filter) dan bekerja dengan tanggal biasanya akan sedikit tricky. Artinya keberhasilan skrip yang kita buat akan sangat banyak tergantung dengan format data nya dan setting format tanggal pada komputer yang digunakan. Pada saat file contoh dibuat setting penanggalan pada komputer saya adalah dd/mm/yyyy. Silahkan download 2 file contohnya disini dan disini.

Sekedar untuk menyegarkan, Syntax Advanced Filter beserta argumen nya adalah sebagai berikut :

expression.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)
expression	Berupa Range, dapat juga berupa Variabel yang merujuk ke obyek Range
Action	Argumen ini wajib disertakan. Argumen ini terdiri atas 2 pilihan yaitu : 1.        xlFilterCopy untuk memfilter dan memindahkan hasilnya ke tempat lain 2.        xlFilterInPlace untuk memfilter data ditempat datanya (tanpa memindahkan hasilnya)
CriteriaRange	Range Kriteria / Parameter filter. Bersifat Optional. Dapat digabungkan dengan karakter Wildcard seperti *, ? dan lain-lain
CopyToRange	Adalah Range tujuan apabila argumen Action menggunakan pilihan xlFilterCopy
Unique	Argumen ini dipakai untuk memfilter hanya record yang unik saja. Default dari argumen ini adalah False
Contoh : Range("Database").AdvancedFilter Action:=xlFilterCopy, _     CriteriaRange:="Criteria", CopyToRange:=Sheets("FilteredList").Range("A1"), _     unique:=False

Hal yang paling penting untuk diperhatikan dari syntax diatas saat menggunakan Advanced Fiter untuk memfilter data bertipe tanggal adalah Format kriteria pada saat penulisan kriteria nya pada argumen CriteriaRange. Format penulisan kriteria haruslah mm-dd-yyyy (Format penanggalan Amerika). Hal ini berkaitan dengan paragraf 2 diatas.

Untuk sebuah data dengan format tanggal yang “tidak lazim” seperti dibawah ini sebisa mungkin lakukan proses format terlebih dahulu.

Pada file contoh pertama di contohkan bagaimana parameter Kriteria ditulis langsung disebelah tabel. Pada file kedua parameter dituliskan melalui sebuah TextBox pada sebuah Userform dan hasil filter ditampilkan melalui sebuah Listbox.

Training Dasar Excel Macro - Privat dan Corporate

Bismillah..

Kepada rekan-rekan yang butuh pelatihan dasar Excel Macro, baik Privat dan Korporasi / Perusahan yang ingin agar karyawan nya dapat bekerja dengan lebih efisien dengan Excel, Grids and Logic dapat memberikan sesi pelatihan dasar Excel Macro. Dengan materi yang dikemas sedemikian rupa maka untuk individu yang tidak mempunyai latar belakang pemrograman sekalipun dapat dengan mudah memahami nya.

Durasi pelatihan biasa nya memakan waktu 5 - 8 Jam perhari. Tergantung kapasitas pemahaman peserta. Untuk privat diadakan pada hari Sabtu dan Minggu di kantor kami :

Gedung Edugate

Lantai 1, suite 1B

Jl. RS Fatmawati, No. 99

Cilandak, Jakarta Selatan

Jakarta

Pemilihan hari pelatihan sangat fleksibel. Bisa diatur sesuai calon peserta. Baik Privat maupun Perusahaan.

Apa yang anda dapatkan selain Ilmu setelah mengikuti pelatihan dengan kami ? Anda gratis mendapatkan layanan Tanya - Jawab melalui email, SMS, telp mengenai materi pelatihan selama 1 Bulan.

Untuk Informasi lebih lanjut silahkan hubungi kami :

• Telepon : 021 759 11734
• Hp : 0811 980 362
• Email : gridsnlogic@gmail.com

Dasar Macro - Menggunakan Advanced Filter dengan multi kriteria (Advanced Filter 2)

Melanjutkan pembahasan mengenai Advanced Filter sebelumnya disini, dikesempatan kali ini akan dijelaskan dasar menggunakan Advanced Filter dengan menggunakan multi kriteria dengan operator AND atau OR.

Silahkan download file contoh nya disini dan disini.

Untuk menggunakan multi kriteria dengan operator AND atau OR yang paling harus diperhatikan adalah penulisan susunan range kriteria nya. Seperti contoh gambar dibawah ini terlihat untuk operator AND maka susuan items kriteria / criterion nya dalam 1 field disusun menurun kebawah.

Penyusunan Kriteria Logika AND dan OR

Range Kriteria untuk Logika AND pada file Contoh

Sedangkan untuk operator OR maka susunan kriteria nya dibuat melebar kesamping. Karena operator OR selalu melibatkan lebih dari 1 pilihan, maka Field yang menjadi syarat berikut nya HARUS ditulis kembali disebelah kanan nya dengan kriteria berikutnya menempati baris berikutnya.

Range Kriteria untuk Logika AND pada file Contoh

Untuk diingat kembali bahwa penulisan Field yang menjadi Syarat / Kriteria harus sama persis dengan yang ada pada tabel / database.

Pada contoh file terlampir juga ditunjukan bagaimana caranya untuk menampilkan semua record seperti semula dengan metode ActiveSheet.ShowAllData. Metode ShowAllData ini dapat digunakan baik pada Advanced Filter maupun Auto Filter.

Semoga penjelasan ini dapat dipahami dengan mudah :D

Dasar Macro - Menggunakan Advanced Filter ( 1 )

Bismillah..

Sebelumnya silahkan download dulu file contoh nya disini

Salah satu kelebihan Advanced Filter apabila dibandingkan dengan Autofilter adalah kemampuan Advanced Filter untuk menggunakan kriteria yang lebih kompleks (Kata Advanced atau lebih maju memang digunakan untuk menunjukan hal ini). Advanced Filter ada dibawah Obyek Range.

Dengan menggunakan Macro, Penggunaan Advanced Filter akan menjadi sangat bermanfaat khusus nya apabila kita bekerja dengan sebuah Tabel / Database Excel.

Dalam syntax Advanced Filter, secara garis besar ada 5 komponen yang harus dipahami. Komponen-komponen tersebut adalah :

1.      Range List / Tabel / Database sebagai sumber data

2.    Action : Jenis filter apa yang akan diterapkan. Filter langsung pada Database  atau copy hasil filter ke tempat lain.

3.     Kriteria / CriteriaRange

4.  Range Tujuan / CopyToRange : berkaitan dengan Nomer 2 apabila kita  memilih untuk mengcopy hasil filter ke tempat lain

5.     Unique : apabila kita menghendaki data yang di filter hanya yang Unik saja.

Bentuk Syntax secara keseluruhan :

Range.AdvancedFilter (Action, Kriteria, RangeTujuan, Unique)

1.      Range List / Tabel / Database

Seperti yang dijelaskan diatas, Range disini adalah sumber data yang akan kita filter. Usahakan List / Tabel ini haruslah berkaidah Database (Baris awal diformat Bold sebagai Header, Baris berikutnya adalah data, Tidak ada baris dan kolom kosong, Tidak ada Header dan data yang di Merge / digabung, dst – akan dibahas di kesempatan lain).

2.      Action

Jenis Filter yang akan diterapkan. Ada 2 jenis filter yaitu : xlFilterInPlace dan xlFilterCopy. xlFilterInPlace berfungsi memfilter data langsung pada Database nya. Sedangkan xlFilterCopy mengcopy hasil filter ke tempat lain.

3.      Kriteria / CriteriaRange

Range yang akan digunakan sebagai petunjuk bagi Excel, parameter data yang akan di filter. Range yang digunakan sebagai Kriteria ini HARUS persis sama dengan Header dari Database kita. Untuk lebih mudah nya, copy saja kolom-kolom Header nya yang akan kita gunakan sebagai kriteria. Urutan Kolom-kolom nya tidak harus sama persis. Pada contoh gambar dibawah terlihat urutan kriteria nya Nama, Jkel dan Jurusan mulai dari B1 sampai dengan D2.

4.      Range Tujuan / CopyToRange

Pilihan ini hanya berlaku apabila kita menggunakan pilihan Action xlFilterCopy. Karena apabila kita memilih untuk menggunakan Action xlFilterInPlace. Kecuali setelah difilter menggunakan xlFilterInPlace data yang sudah difilter akan dipindahkan ke tempat lain. Masalah nya jadi kerja dua kali kan ? :D

5.      Unique

Pilihan untuk yang digunakan untuk memilih data yang unik saja atau tanpa duplikat.

Pengaturan Tabel, Kriteria dan Tujuan Advanced Filter

Maka apabila melihat contoh gambar, penulisan skrip nya menjadi :

Range("A4:E14").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range("B1:D2"), _ CopyToRange:=Range("G4:K4"), _ Unique:=False Hasil setelah skrip lengkap di jalankan

Tips - mengembalikan format sebuah Tabel ke range normal

Bismillah.

Memformat data menjadi sebuah Tabel akan banyak membantu. Salah satu nya apabila kita bekerja dengan Pivot Table. Dengan memformat data kita ke format Tabel maka sumber data dari Pivot Table kita akan otomatis manjadi dinamis menyesuaikan banyak nya data.

Tapi ada saat nya kita tidak memerlukan lagi format Table tersebut. Dan ingin mengembalikan data kita ke format normal. Di Excel 2010 dan 2013 ada 2 (untuk Excel 2007 lakukan cara kedua) cara untuk mengembalikan nya seperti cell biasa :

Cara pertama :

Melalui menu Design | Tools | klik Convert To Range. Excel akan menampilkan pesan "Do you want to convert the Table to a normal Range ?". Pilih Yes.

Cara kedua :

Klik kanan di sembarang tempat didalam Tabel, Menu Table, pilih Convert To Range. Sama seperti cara pertama, Excel akan menampilkan pesan konfirmasi, maka pilih Yes.

Setelah melakukan salah satu cara tersebut diatas, langkah selanjutnya tinggal menghapus format cell nya melalui menu Home | Cell Styles, dan pilih Normal.

Semoga bermanfaat.