Monday, January 8, 2018

Cara Efektif Menggeser Kolom Pada Excel

Sebuah tabel biasanya terdiri atas beberapa kolom dan urutan kolom disusun berdasarkan kondisi logis supaya memudahkan dalam analisa.  Namun adakalanya kita perlu melakukan penyusunan atau re-order ulang kolom untuk keperluan analisa yang lebih baik. Dengan kata lain kita perlu menggeser atau memindahkan kolom tertentu sehingga dapat menampilkan tabel dengan urutan kolom yang lebih sesuai untuk keperluan analisa.

Sebagai contoh, kita mungkin perlu menggeser dan menggandengkan sejumlah kolom untuk melihat data side by side. Tentu saja kita bisa meng-hide kolom tetangganya yang tidak diperlukan untuk sesaat, akan tetapi cara ini tidak selalu yang terbaik. Karena kita mungkin saja masih perlu melihat data dari sudut pandang kolom yang di-hide.

Nah, dalam kesempatan ini akan dibahas berbagai alternatif cara untuk memindahkan atau menggeser kolom. Kelihatannya mungkin sepele, tetapi yakinlah.. ini akan sangat membantu efektifitas kerja jika bisa mempraktekannya dengan tepat.

Secara garis besar, pembahasan kali ini mencakup:

  • Menggeser kolom menggunakan drag & drop.
  • Memindahkan kolom dengan cara  cut dan paste
  • Memindahkan kolom dengan cara  copy, Paste dan delete
  • Re-order kolom menggunakan macro


Cara Menggeser  Kolom Dengan Tehnik Drag & Drop


Tehnik drag & drop untuk memindahkan range mungkin bukan hal yang asing bagi sebagian besar pengguna excel. Namun cara yang umum biasanya range yang dipindahkan akan menimpa dan menghapus data pada range lokasi dropnya. 

Cara yang akan dijelaskan di sini adalah bagaimana drag & drop untuk menggeser kolom ke lokasi lainnya tanpa menghapus / menimpa data pada kolom lainnya.

Dengan menggunakan contoh tabel pada artikel sebelumnya mengenai sortir dan filter data berdasarkan warna, mari kita praktekan bagaimana cara memindahkan / menggeser kolom pada excel.

Geser Kolom Tabel Excel


Perhatikan gambar contoh tabel di atas.

Anggaplah kita ingin memindahkan kolom Kategori ke bagian awal tabel:

  • Seleksi kolom D, kemudian sambil menekan tombol SHIFT, gerakan pointer pada bagian sisi kolom yang diseleksi sampai kursor berubah menjadi tanda panah menunjuk 4 arah mata angin.
  • Klik tombol mouse kiri. Lalu sambil tetap menekan tombol SHIFT, drag kolom ke lokasi yang diinginkan. Lepaskan klik mouse pada lokasi yang sesuai, misalnya ke kolom pertama seperti contoh dibawah ini:


Carag Drag & Drop Kolom Excel


  • Sehingga hasilnya kolom Kategori bergeser menjadi kolom pertama dalam tabel seperti gambar berikut:

drag & drop kolom excel


Catatan: Penting diperhatikan bahwa proses Drag & Drop ini harus dilakukan sambil menekan tombol SHIFT. Jika tidak, maka kolom yang di geser akan menimpa / menghapus kolom lainnya. 

Tehnik ini juga dapat digunakan untuk menggeser beberapa kolom yang bersebelahan sekaligus.  Sayangnya metode ini tidak dapat digunakan untuk menggeser beberapa kolom yang tidak bersebelahan secara langsung, atau beberapa kolom yang diseleksi dengan cara klik header kolom sambil menekan tombol CTR.

Selain untuk menggeser kolom, tehnik ini juga sebenarnya bisa digunakan juga untuk menggeser baris. Silahkan dicoba sendiri.


Menggeser kolom dengan cara Cut & Paste


Jika cara drag & drop ada kendala, misalnya problem mouse, maka anda dapat menggunakan cara cara cut & paste. Tehnik ini juga akan sangat tergantung pada kebutuhan apakah memindahkan kolom tunggal atau beberapa kolom.

Memindahkan Kolom Tunggal

  • Seleksi kolom dengan cara klik pada header kolom.
  • Tekan CTRL + X untuk menjalankan command Cut pada kolom terpilih. Bisa juga dilakukan dengan cara Klik kanan, kemudian klik Cut.


tehnik memindahkan kolom excel

  • Seleksi kolom dimana anda ingin memindahkan kolom yang di-Cut ke lokasi tersebut, kemudian klik kanan dan klik Insert Cut Cells.

menggeser kolom cut and paste

  • Maka hasil akhirnya kolom berpindah sesuai order yang dikehendaki.

hasil penggeseran kolom


Jika anda merasa lebih nyaman menggunakan shortcut dan keyboard, mungkin cara berikut akan lebih anda sukai.

  • Seleksi satu sel atau lebih secara horizontal sesuai jumlah kolom yang bersebelahan yang ingin anda pindahkan, kemudian tekan CTRL + SPACE untuk menyeleksi kolom penuh.
  • Tekan CTRL + X untuk menggunting atau Cut kolom terpilih
  • Seleksi lokasi kolom lainnya sebagai lokasi pemindahan kolom, kemudian tekan CTRL + SPACE untuk menyeleksi kolom secara penuh. Bisa juga dengan hanya menyeleksi satu sel teratas saja.
  • Lalu tekan CTRL + SHIFT bersama dengan menekan tombol plus (+). Jika anda menggunakan keyboard yang ada tombol numeriknya, bisa menekan CTRL dan tanda plus (+) tanpa perlu menekan tombol SHIFT.

Cara Memindahkan Beberapa Kolom Pada Excel


Tehnik cut/paste hanya dapat bekerja pada satu kolom saja atau beberapa kolom yang bersebelahan. Jika tehnik ini dicoba untuk beberapa kolom yang tidak bersebelahan langsung makan cara ini akan berakhir dengan error.

Untuk rerorder beberapa kolom kita dapat melakukan salah satu cara atau kombinasi berikut:

Drag & Drop kolom satu  persatu seperti dijelaskan pada bagian awal artikel ini. Cara ini sepertinya paling cepat.

Cut dan Paste masing – masing kolom. Cara ini mungkin bukan pilihan terbaik jika bekerja dengan banyak kolom yang harus di geser. Tetapi mungkin akan menjadi pilihan terbaik jika posisi kolom yang akan digeser berjauhan dengan posisi barunya sehingga tidak nampak dalam satu layar komputer.

Copy, Paste dan Delete: cara ini mirip dengan cut dan paste, hanya saja sedikit lebih panjang prosesnya karena kolom awal tidak terhapus, sehingga harus di delete kolom lagi. Caranya dijelaskan di bawah ini.

Menggeser Kolom Dengan Cara Copy, Paste dan Delete.


Jika dalam kondisi tertentu tehnik drag kolom menggunakan mouse tidak bekerja, maka anda bisa mencoba penyusunan kolom menggunakan cara ini.

Seleksi kolom yang ingin di pindahkan dengan cara mengklik header kolom, atau  dengan cara seleksi salah satu sel pada kolom yang ingin dipindahkan, kemudian tekan CTRL + SPACE.

Copy kolom terpilih dengan cara klik kanan dilanjutkan klik copy.  Atau bisa juga dengan menekan tombol short cut CTRL + C.

menggeser kolom copy dan paste

  • Lalu klik kanan pada kolom tujuan, dan klik Insert Copied Cells.

tehnik copy paste memindahkan kolom


  • Maka akan muncul duplikat dari kolom yang dicopy tadi.



copy paste geser kolom

Selanjutnya kita bisa menghapus (delete) kolom awal yang tidak diperlukan.

Perlu diperhatikan: Hasil dari tehnik copy dan insert copied cells mungkin akan memberikan hasil yang yang tidak diiharapkan misalnya bisa saja data pada kolom hasil copy tidak sama dengan data pada kolom awalnya, atau bahkan error. Ini terjadi biasanya jika kolom yang dicopy berisi formula. 


Merubah Susunan Kolom Menggunakan Macro / VBA


Jika ada banyak sekali tabel serupa namun terletak dalam banyak sheet atau workbook yang berbeda, kemudian kita perlu merubah susunan kolom semua tabel tersebut. Cara apa yang paling efektif?. 
Mengedit satu persatu tabel dengan cara drag & drop, cut/paste maupun copy/paste/delete nampaknya hanya akan menghabiskan hari-hari anda. 

Dalam hal ini Macro mengambil peranannya. Tidak perlu pusing meskipun anda belum memahami bahasa vba. Karena disini kita cukup melakukan record macro untuk proses geser kolom  pada tabel pertama. Sedangkan untuk tabel berikutnya cukup jalankan makro.

  • Masuk ke salah satu sheet yang berisi tabel untuk diatur susunan kolomnnya
  • Masuk ke tab Developer, kemudian klik tombol Record Macro.
  • Maka akan muncul Kotak Dialog Record Macro
  • Pada kotak isian Macro Name, ketik nama macro yang anda kehendaki, misalnya: AturKolom
  • Tentukan shortcut key, misalnya CTRL + q ,  saya pilih huruf “q”  dengan pertimbangan kemudahan untuk mengaksesnya menggunakan jari tangan kiri.
  • Store Macro In, menentukan lokasi file penyimpanan code macro, saya pilih Personal Macro Workbook.
  • Klik tombol OK.
  • Bagan alur untuk memulai record Macro digambarkan sebagai berikut:


record macro geser kolom

  • Selanjutnya lakukan tahapan menggeser kolom dengan tehnik yang sudah dijelaskan sebelumnya. Anda bisa menggunakan salah  satu  atau gabungan dari tehnik drag & drop, cut & paste, maupun copy, paste & delete.
  • Jika pengaturan kolom sudah selesai, maka klik tombol Stop Recording.


tombol record macro

Sekarang anda sudah memiliki macro AturKolom yang siap digunakan untuk mengatur  susunan kolom tabel-tabel lainnya yang serupa.

Cara menggunakannya simple saja, cukup masuk tiap-tiap sheet yang berisi tabel serupa, kemudian tekan CTRL + q  (atau shortcut lainnya sesuai pengaturan yang anda buat), maka macro AturKolom() akan bekerja untuk anda.


Mau lebih cepat lagi?


Jika anda tidak mau membuka sheet satu persatu untuk menjalankan makro AturKolom(), maka dengan sedikit code VBA, anda bisa melakukannya sekaligus terhadap semua sheet dalam satu file.

Kode vba berikut akan bekerja dengan asumsi semua sheet dalam workbook berisi tabel yang sama susunan kolomnya dan akan dirubah menjadi susunan tertentu.

Sub AturKolomSekaligus()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    AturKolom
Next
End Sub

Jika tidak semua sheet berisi tabel yang sama, dan anda hanya ingin mengedit sebagian sheet saja yang berisi tabel, maka anda harus menamai sheet dimaksud secara terstruktur.

Misalnya: semua sheet berisi tabel harus diberinama sheet dengan awalan tabel seperti: tabel1, tabel2, tabel3, tabel4, dan seterusnya.

Dan contoh code VBA yang bisa digunakan untuk kasus ini adalah sebagai berikut:

Sub AturKolomSekaligus()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If Left(ws.Name, 5) = "tabel" Then
        ws.Activate
        AturKolom
    End If
Next
End Sub

Supaya bisa digunakan, code tersebut harus diketik atau di copy di module yang sama dengan lokasi code makro AturKolom() yang sudah kita buat  menggunakan record macro sebelumnya.

Caranya :

  • Klik tombol Visual Basic yang ada pada Tab Developer, atau tekan key short cut ALT + F11.  Langkah ini akan membawa kita pada jendela Visual Basic Editor
  • Pada bagian project explorer cari module yang merupakan lokasi penempatan code. Dalam hal ini module terdapat dalam workbook PERSONAL.XLSB, module1.
  • Double klik module1 tersebut untuk masuk ke jendela kodenya.
  • Di jendela kode, kita akan melihat kode AturKolom() yang ter-generate secara otomatis sesuai langkah-langkah yang kita record sebelumnya. (Dalam contoh yang ditampilkan codenya singkat dan sederhana, dalam aktualnya mungkin lebih panjang tergantung berapa banyak proses drag & drop kolom atau copy/cut paste yang di-record.
  • Ketik atau copy code macro AturKolomSekaligus() pada module tersebut.
  • Perhatikan gambar berikut untuk lebih jelasnya.


module vba atur susunan kolom



Setelah code AturKolomSekaligus() di copy atau diketik di dalam module VBA, maka tahap selanjutnya adalah membuat shorcut supaya code dapat lebih mudah diakses dan cepat dijalankan.
  • Keluar dari jendela VBA dan kembali ke spreadsheet.
  • Dari tab Developer, klik Macros, maka selanjutnya akan muncul kotak dialog Macro.
  • Pada List Macro Name, pilih PERSONAL.XLSB!AturKolomSekaligus, nama ini sesuai nama file dimana code macro tersimpan, dan nama macronya itu sendiri.
  • Klik tombol Options
  • Pada jendela Macro Options, tentukan key shortcut yang diinginkan. Contoh CTRL + SHIFT + C . lagi-lagi shortcut ini saya buat dengan pertimbangan kemudahan akses oleh jari tangan kiri. Tanda shift akan muncul otomatis jika kita mengetikan huruf kapital pada kotak isian key shortcut.
  • Klik tombol OK
  • Selanjutnya Close dari  kotak dialog macro.

Perhatikan kembali ilustrasi langkah-langkah pembuatan shortcut macro berikut:


cara membuat jalan pintas macro


Setelah langkah – langkah diatas dilakukan tanpa kecuali, maka kita sudah bisa menjalankan macro AturKolomSekaligus() untuk merubah susunan kolom semua tabel dalam sebuah file.

Caranya sangat mudah. Cukup buka file yang terdiri atas sheet  berisi tabel, dimana nama sheet sudah diatur secara terstruktur. Dalam contoh ini tabel1, tabel2, tabel3  dst,

Lalu tekan key shortcut CTRL + SHIFT + C atau shortcut lain sesuai pengaturan yang anda sukai. Maka selanjutnya macro AturKolomSekaligus() yang akan menyelesaikan tugas anda dengan cepat.

Demikian pembahasan mengenai beberapa tehnik pemindahan dan penyusunan kolom pada tabel dalam microsoft excel. Semoga bermanfaat.

Baca juga catatan pelajaran excel lainnya:
Dan mohon dicek juga semua artikel dalam blog ini melalui Daftar Isi.

Referensi:

Monday, January 1, 2018

Sortir & Filter Berdasarkan Warna Cell, Font dan Icon

Pada artikel terdahulu, sudah dibahas bagaimana menghitung sel dengan kriteria warna tertentu. Kali ini kita akan kembali bermain-main dengan warna, yaitu bagaimana menyortir dan memfilter data berdasarkan warna background sel dan warna font. Sebagai pelengkap, juga akan dibahas sedikit mengenai bagaimana sortir data berdasarkan icon yang dibuat melalui conditional formating.

Pembahasan mencakup:


  • Cara sortir berdasarkan warna background cell.
  • Cara sortir berdasarkan warna font
  • Cara sortir berdasarkan icon
  • Cara filter berdasarkan warna pada excel
  • Cara filter 2 warna atau lebih


Sortir Data Berdasarkan Warna Sel 


Mulai diperkenalkan pada excel versi 2007, tugas menyortir data berdasarkan warna dapat dilakukan dengan sangat mudah. Tidak perlu code VBA ataupun formula, tetapi cukup hanya dengan menggunakan fitur Custom Sort.

Seleksi tabel atau range sel.
Pada tab Home, pada bagian Editing Group, klik Sort & Filter, kemudian klik Custom Sort…

excel how to custom sort

Setelah muncul kotak dialog Sort, tentukan setting berikut dari kiri ke kanan
  • Column : Merupakan Kolom yang yang ingin anda sortir (contoh : Kategori)
  • Sort on : Cell Color
  • Order : Pilih Warna yang ingin diposisikan bagian atas
  • Pilih On Top Position.


Perhatikan screen shot dibawah, dan pastikan setting dibuat seperti bagian dalam kotak coklat

cara sortir berdasarkan warna


  • Selanjutnya klik tombol Copy Level untuk menambahkan satu atau beberapa level dengan setting yang sama seperti yang sudah dibuat pertama kali. Kemudian dibawah order, seleksi warna kedua level sortir.
  • Ulangi langkah di atas untuk menambahkan level sortir sesuai keperluan
  • Screenshot berikut menggambarkan setting sortir sampai dengan 4 level warna.

cara sortir berdasarkan warna sel

  • Langkah berikutnya : Klik Ok dan verifikasi jika baris data sudah di sortir dengan benar.

Setelah melakukan langkah di atas maka kita sudah berhasil mensortir data berdasarkan warna sel seperti gambar berikut:


hasil sortir bersarkan warna sel


Catatan: Sortir hanya berdasarkan warna kolom yang ditentukan dalam tabel, jadi tidak perlu khawatir meskipun warna sel antara kolom yang satu dengan yang lainnya beraneka ragam. Kita tidak perlu menentukan rule di setiap kolom, tetapi cukup mengatur rule sortir pada kolom yang diperlukan saja. Dalam contoh di atas, misalnya kita bisa mengatur rule pada kolom NIS, Nama, Nilai Ujian, atau Kategori saja.

Jika kita menginginkan sortir hanya berdasarkan satu warna saja, maka kita dapat melakukannya dengan lebih cepat. Carannya cukup menggunakan autofilter.

Untuk memunculkan autofilter, seleksi range tabel atau satu sel dalam tabel dan tekan shortcut CTR + SHIFT + L, kemudian klik tanda segitiga kecil pada judul kolom, lalu  klik Sort by Color dan selanjutnya pilih warna. Warna terpilih akan otomatis berada pada level paling atas.

autofilter sortir berdasarkan warna


Sortir Data Berdasarlan Warna Font

Sortir berdasarkan warna font, prinsipnya sama dengan cara sortir berdasarkan warna background cell seperti yang sudah dijelaskan di atas. Hanya satu hal yang  membedakannya, yaitu pada bagian Sort on. Pada bagian ini pastikan dipilih Font Color, seperti ditunjukan dalam screenshot berikut:

sortir berdasarkan warna font

Jika hanya perlu mengecek satu warna saja pada bagian paling atas,  maka kita bisa menggunakan fitur autofilter. Untuk memunculkan autofilter: seleksi tabel, kemudian tekan CTR+Shift+L. klik tanda segitiga kecil pada header tabel, kemudian klik Sort by Font Color  dan pilih warna yang diinginkan.

cara autofilter untuk sortir berdasarkan warna font

Selain sortir berdasarkan cell color maupun font color, kita juga dapat melakukan tehnik sortir lainnya berdasarkan skenario yang sesuai.

Sortir Data Berdasarkan Icon


Misalnya kita menerapkan conditional formating icon bedasarkan nilai ujian masing masing siswa seperti terlihat pada gambar berikut:

cara sortir berdasarkan icon

Dapat kita lihat  pada kolom nilai dibagi menjadi 3  bagian, nilai yang tinggi ditandai icon warna hijau, bagian pertengahan ditandai icon kuning dan bagian nilai bawah ditandai icon merah. Karena belum dilakukan sortir, maka pengelompokan berdasarkan icon belum tersusun dalam order yang sesuai.

Cara melakukan sortir icon ini tidak jauh berbeda dengan 2 cara yang sudah dibahas sebelumnya. Yang membedakan adalah pengaturan pada bagian sort order, harus dipilih Cell Icon.
Kemudian pada bagian Order pilih atau tentukan icon mana yang akan disusun sesuai urutan levelnya.

Perhatikan screenshot berikut:

cara sortir dengan icon

Dan hasil akhir setelah dilakukan sortir maka data akan tersusun  berdasarkan icon seperti diperlihatkan dalam screenshot contoh dibawah ini.

Hasil Sortir Icon

Cara Filter Berdasarkan Warna Pada Excel


Jika kita ingin memfilter baris data dalam tabel berdasarkan warna pada kolom tertentu, maka kita dapat melakukannya dengan sangat mudah menggunakan fitur AutoFilter >> Filter By Color.
Kemudian kita bisa memilih jenis filternya apakah berdasarkan warna sel (Filter by Cell Color) atau Warna font (Filter by Font Color).

Pilihan warna cell atau warna font akan muncul secara otomatis tergantung warna yang tersedia dalam kolom terpilih.

Autofilter by font color


Cara Filter Beberapa Warna Sekaligus


Sayangnya fitur Filter by Color ini masih memiliki keterbatasan yaitu hanya mengizinkan filter 1 warna saja. Sebagai solusinya kita bisa menggunakan User Defined Function (UDF) sederhana untuk mengambil data numerik warna dan kolom bantu sehingga dapat melakukan filter terhadap dua warna atau lebih.

Berikut contoh code VBA super singkat dan sederhana untuk membuat UDF yang saya berinama WarnaSel()

Function WarnaSel(sel)
WarnaSel = sel.Interior.Color
End Function

Supaya UDF ter sebut bisa digunakan maka Copy code tersebut harus di copy atau diketik pada module standar VBA.

Caranya:

Tekan ALT + F11 untuk masuk ke jendela VBA  , kemudian klik menu Insert  >> Module , lalu ketik atau copy code diatas seperti dicontohkan pada gambar berikut:


vba untuk ambil warna sel


Kembali ke tabel yang akan difilter, tambahkan kolom bantu dengan nama  header kolom Filter Warna. Kemudian tekan dua kali shortcut CTR+SHIFT+L untuk menambahkan autofilter pada kolom bantu tersebut.

Masukan formula menggunakan fungsi WarnaSel pada kolom bantu. Dalam contoh berikut pada sel E2 masukan rumus =WarnaSel(D2).  Rumus tersebut akan menghasilkan bilangan yang menunjukan code warna sel D2. Lalu copy rumus tersebut sampai baris akhir tabel.


cara menggunakan vba untuk ambil warna cell


Kemudian lakukan filter dengan cara klik tanda segitiga kecil pada kolom Filter Warna. Selanjutnya pilih code warna tertentu yang akan difilter, kemudian tekan tombol OK. Perhatikan kembali langkah-langkah tersebut seperti dicontohkan dalam gambar di bawah ini.

auto filter beberapa warna

Maka hasilnya adalah tabel di filter hanya memunculkan warna terpilih. Dalam contoh kita dapat melihat tabel hanya menampilkan dua warna terpilih.

Hasil  filter beberapa warna sekaligus

Dengan cara yang serupa selanjutnya kita juga bisa memfilter data berdasarkan warna font. Tentu saja dengan menggunakan bantuan UDF. Berikut code VBA-nya.

Function WarnaFont(sel)
WarnaFont = sel.Font.Color
End Function

--------

Demikian panduan singkat bagaimana cara sortir dan filter data berdasarkan warna sel, warna font, dan icon. Semoga bermanfaat.

Terimakasih.

Artikel terkait:

Referensi:

Monday, December 18, 2017

Merubah Warna Sel Berdasarkan Value dan Formula.

Merubah warna sel? Saya rasa, hal ini bukanlah hal yang asing bagi setiap pengguna excel. Selain untuk  estetika, pewarnaan sel juga dapat membantu mempermudah membaca, menemukan dan menganalisa data tertentu. Cukup dengan seleksi sel atau range tertentu, kemudian dengan menggunakan tombol fill color”, ataupun dengan memilih style tertentu maka kita sudah bisa menghiasi spreadsheet dengan aneka warna.

Kalau sudah tahu bahwa mewarnai sel bukanlah hal yang asing, jadi kenapa artikel ini masih membahas perihal merubah warna sel?

Nah makanya artikel ini dibuat yaitu untuk membahas beberapa tehnik mewarnai cell dengan cara yang agak jarang digunakan oleh kebanyakan user.

Adapun pembahasan difokuskan pada 2 hal:

  1. Merubah warna sesuai nilai bilangan dalam sel 
  2. Merubah warna sel kosong dan sel error.

Dan pada masing masing 2 hal tersebut dibagi lagi 2 pembahasan yaitu :

  • Tehnik conditional formating yang menghasilkan perubahan warna yang bersifat dinamis artinya warna akan berubah secara otomatis mengikuti kriteria atau kondisi yang ditetapkan.
  • Tehnik pencarian dan seleksi sel khusus sehingga dapat dilakukan formating secara sekaligus pada seleksi sel dengan kriteria atau kondisi tertentu. Formating dengan tehnik ini bersifat statis, artinya warna sel tidak bisa berubah secara otomatis jika ada perubahan kondisi pada nilai sel.

Baiklah, mari kita bahas satu per satu.

Bagaimana merubah warna sel berdasarkan value dalam sel.


Conditional Formating : Merubah Warna Secara Dinamis Berdasarkan Cell Values.


Misalnya kita memiliki sebuah tabel berisikan sejumlah data dan kita ingin mewarnai atau meng-highlight sel berdasarkan kriteria nilai atau kondisi tertentu. Sayangnya data tersebut bisa berubah sewaktu-waktu dan kita tidak ingin repot merubah warna secara manual setiap kali ada perubahan data

Anggaplah tabel tersebut berupa data jumlah penjualan produk tertentu di lokasi pemasaran yang dibagi menjadi beberapa area. Selanjutnya kita ingin meng-highlight sel berisi data penjualan yang lebih dari 250 unit.

Di sinilah conditional formating memainkan perannya.

Perhatikan screenshot berikut:

Merubah Warna Cell Cepat Sesuai Warna


Catatan: screenshots dalam artikel ini diambil menggunakan excel 2010, akan tetapi tombol, dialog box dan settingnya hampir saja baik menggunakan excel 2010, 2013 maupun 2016.

Lakukan langkah - langkah berikut untuk membuat rule conditional formating merubah warna sesuai value.

Seleksi tabel atau range dimana kita ingin merubah warna background sel. Dalam hal contoh yang kita gunakan dalam pembahasan ini, lakukanlah seleksi pada range B2:H8 (baris pertama yang berisi nama kolom dan kolom pertama yang berisi nama area tidak termasuk area yang diseleksi).

Masuk ke tab Home, kemudian grup Style dan pilih Conditional FormatingNew Rule


Cara Menggunakan Conditional Formating Excel

  • Di dalam kotak dialog New Formating Rule, pada bagian “Select a Rule Type” pilih “Format only cells that contain” 
  • Kemudian lanjutkan ke bagian bawah dialog box pada bagian Edit Rule DescriptionFormat Only Cells With dimana terdapat 3 field yang perlu dipilih atau diisi. 
    • Pada bagian paling kiri Pilih Cell Value
    • Pada bagian tengah pilih greater than 
    • Dan pada bagian paling kanan  ketikan angka 250
  • Kemudian klik tombol  format untuk memilih warna background yang akan diaplikasikan ketika kondisi terpenuhi.
  • Perhatikan langkah-langkah nya dalam screenshot di bawah ini.


Menggunakan Conditional Formating Cell Value

Di dalam kotak dialog format cells, masuk ke tab Fill dan kemudian pilih warna yang anda inginkan, misalnya hijau, dan kemudian klik OK.

set warna conditional formating

Setelah menekan tombol OK, maka kita akan kembali melihat jendela Edit Formating Rule untuk melihat preview dari format yang sudah dibuat. Jika dirasa sudah oke, maka klik tombol OK.


Cara Conditional Formating

Dan hasilnya adalah seperti terlihat seperti screenshot di bawah ini. Semua sel berisi value lebih dari 250 akan berwarna hijau atau warna lain sesuai pengaturan pada langkah sebelumnya. Warna hijau tersebut akan kembali menjadi putih jika kita merubah value sel menjadi 250 atau kurang. Dengan kata lain warna sel bersifat dinamis.


Trik Conditional Formating


Seandainya ada lebih dari satu kondisi yang harus ditampilkan dalam format berbeda, maka kita dapat menambahkan format lainnya dengan cara yang sama dengan langkah sebelumnya yaitu dari tab Home, kemudian klik Conditional Formating.

Pada dialog box Edit Formating Rule pilih Format Only Cells That Contain, dan pda bagian Format Only Cells With pilih Cell Value , dan Less Than Or Equal to, kemudian ketikan angka misalnya 150. Rule ini untuk memberikan format pada sel yang berisi angka 150 atau kurang.


Tips Conditional Formating


Setelah menekan tombol OK, maka kita akan mendapatkan tabel dengan warna sel berwarna merah muda untuk data penjualan dengan nilai 150 atau kurang.


Conditional Formating Untuk Warna Dinamis


Dengan cara yang sama, kita juga dapat menambah dan mengatur format yang diinginkan. Untuk memprcantik tampilan data, selain merubah warna background sel, kita juga dapat mengatur format lainnya seperti font, warna font, border dan sebagainya.

Cara Menemukan dan Menyeleksi Semua Sel Berisi Nilai Tertentu dan Kemudian Mewarnainya.


Tehnik ini mencoba memberikan alternative lain seleksi sel tertentu menggunakan fitur Find & Select. 

Fitur ini sebenarnya sudah cukup populer digunakan untuk mencari data tertentu dalam spreadsheet.

Namun langkah menyeleksi sel dengan kriteria tertentu menggunakan fitur ini masih sering luput dari perhatian. Hal ini wajar saja karena kebanyakan user lebih senang menyeleksi range atau sel tertentu satu persatu menggunakan mouse kemudian merubah format sesuai yang diinginkan. 

Sedangkan dengan fitur yang akan dijelaskan ini, kita bisa bisa mencari nilai dengan kriteria tertentu dalam range  data, kemudian menerapkan format secara bersamaan. Penerapannya Ada beberapa skenario tergantung jenis data atau nilai yang kita cari.

Misalnya kita perlu menemukani sel dengan nilai tertentu, anggaplah 100 Maka cukup masuk ke tab Home, Editing group dan klik Find & Select → Find…


Menggunakan Find and Select


Masukan nilai yang diinginkan dan kemudian klik tombol Find All.


Menggunakan Find and Replace


Tip: Klik tombol Options  untuk mendapatkan opsi pencarian yang lebih advanced seperti “Match Case”, “Match entire cell contents”. 

Untuk mendapatkan hasil pencarian dengan scope yang lebih lebar, kita bisa menggunakan karakter wildcard seperti asterisk (*) untuk menemukan beberapa karakter tertentu atau tanda tanya (?) untuk menemukan karakter tunggal. Karakter wildcard tersebut diketikan pada field Find What bersamaan dengan angka kunci.

Misalnya kita ingin mencari dan menyeleksi sel berisi nilai 200 atau lebih tetapi kurang dari 300.

  • Seleksi range B2:H8
  • Dari Tab Home, kemudian klik tombol Find & Select, klik Find.  Atau bisa juga menggunakan shorcut CTR + F
  • Pada kotak isian Find What, ketik 2??
  • Klik Tombol Find All
  • Tekan keyboard CTR + A untuk menyeleksi semua sel yang memenuhi kritera pencarian
  • Close jendela Find & Replace
  • Maka kita akan mendapatkan semua nilai yang memenuhi kriteria nilai 200 lebih tetapi kurang dari 300 yang sudah terseleksi.
  • Lakukan edit format seperti biasa, misalnya dengan fitur autofill atau dengan klik kanan Format Cell atau melalui command-command lainnya yang kita sukai.


Find and Select Untuk Mewarnai Sel

Merubah Warna Background Sel Khusus (Blanks dan Formula Error)


Seperti pada contoh sebelumnya pada section ini juga akan dibahas bagaimana merubah warna secara dinamis dan secara statis. Conditional Formating untuk merubah warna secara dinamis. Sedangkan untuk perubahan warna secara statis, kita gunakan bantuan alat Go to Special untuk menyeleksi sel dengan kriteria yang kita inginkan dan kemudian merubah formatnya dengan cara-cara yang sudah kita ketahui.

Conditional Formating : Menggunakan Formula Untuk Merubah Warna  Sel Kosong dan Sel Error.


Metode ini bisa menjadi solusi yang kemungkinan besar sangat anda perlukan dan banyak kasus. 
Kita akan menggunakan table nilai penjualan di beberapa area dan beberapa bulan seperti contoh sebelumnya, dengan perubahan beberapa kasus data dimana terdapat bebeapa sel kosong. Perhatikan bagaimana kita bisa mendeteksi semua sel kosong tersebut dan merubah warnanya secara bersamaan. 

Contoh Tabel Untuk Conditional Formating


  • Seleksi range sel yang akan kita atur warnanya sesuai kondisi blank atau error, misalnya range B2:H8
  • Masuk ke Tab Home, kemudian pada grup Style, klik Conditional FormmatingNew Rule
  • Kemudian di dalam kotak dialog “New Formating Rule”, pilih option “Use a formula to determine which cells to format”.  Lalu masukan salah satu formula berikut di dalam field “Format falues where this formula is true” 
    • Untuk merubah warna sel kosong (blank cells) rumusnya =ISBLANK()
    • Untuk merubah warna sel yang mengandung nilai error =ISERROR() 
  • Anggaplah kita lebih tertarik untuk merubah warna sel kosong maka masukan formula =ISBLANK(), kemudian tempatkan kursor di antara tandak kurung dan dengan menggunakan mouse, klik sel paling pojok kiri atas dari range yang diseleksi sebelumnya, kemudian delete tanda dolarnya, sehingga rumus menjadi ISBLANK(B2). 
  • Kita juga dapat mengetikan referensi B2 secara langsung sebagai parameter fungsi ISBLANK()
  • Kemudian tekan tombol format, dan atur formatnya pada bagian Fill, kemudian tekan tombol OK, dan Tekan tombol Ok lagi.

Untuk lebih jelasnya perhatikan langkah – langkah berikut berikut:

Membuat Formula Untuk Conditional Formating


Perlu diperhatikan, bahwa B2 merupakan alamat sel pertama atau sel yang paling kiri dan paling atas dari range yang diseleksi. Penulisan referensi alamat sel tidak menggunakan tanda dolar artinya alamat tersebut bersifat relatif agar formula pada conditional formating setiap sel mengambil referensi dari sel itu sendiri. Jika referensi ditulis $B$1 maka perubahan pada sel B1 akan berdampak pada format seluruh sel dalam lingkup conditional formating.

Screenshot berikut menunjukan hasil dari conditonal formating menggunakan formula untuk mewarnai sel kosong atau blank cells

Contoh Tabel Conditional Formating


Dengan tehnik serupa kita juga bisa mewarnai sel yang mengandung formula dengan nilai error. Caranya  cukup dengan mengganti rumus ISBLANK(B2) menjadi ISERROR(B2). Silahkan dicoba.

Cara Menyeleksi Seleksi Seluruh Cell Kosong atau Cell Error Dan Kemudian Mewarnainya.

Selain menggunakan conditional formating yang dapat merubah warna sel secara dinamis mengikuti kondisi blank atau nilai error, kita juga dapat menyeleksi sel kosong ataupun error menggunakan alat Go To Special dan kemudian mewarnainya dangan fill color untuk menghasilkan format warna yang statis.

Caranya cukup mudah

  • Seleksi range yang akan dicari sel kosong atau error nya, misalnya range B2:H8
  • Kemudian dari Tab Home, grup Editing, klip Tombol Find & Select dan klik Go To Special.. ini untuk memunculkan kotak dialog Go To Special.


Menggunakan Fitur Go To Special


Tips: Selain menggunakan cara di atas, kotak dialog Go To Special juga dapat dimunculkan dengan menggunakan shortcut CTR+G   atau F5.


Go To Special Dialog Blox


  • Untuk menyeleksi sel kosong, pilih opsi Blanks
  • Untuk menyeleksi formula error, pilih opsi Formula, kemudian aktifkan centang pada cek box Error, dan cek box lainnya non aktifkan.
  • Klik tombol Ok
  • Terakhir : Warnai menggunakan Fill Color ataupun format sel lainnya.
  • Selesai.

----
Sampai disini pembahasan mudah mudahan bermanfaat. Yaitu Mewarnai sel secara dinamis menggunakan conditional formating. Juga mewarnai sel secara statis dengan cara-cara yang umum dilakukan, namun cara menyeleksi sel yang sedikit berbeda sehingga dapat melakukan formating secara sekaligus terhadap sel yang memenuhii kriteria.

Silahkan di cek juga catatan pelajaran excel lainnya:

Referensi:


Friday, December 15, 2017

Cara Cepat Isi Sel Kosong

Mengisi sel kosong, mengapa perlu dilakukan?

Jika kita terbiasa menggunakan fitur PivotTable, Autofilter, Data Short, Subtotal dan beberapa fungsi/rumus seperti SUMIF/SUMIFS, COUNTIF/COUNTIFS, SUMPRODUCT, Rumus Array dan Fungsi Database, pasti kita akan menyadari betapa pentingnya bagaimana data disusun atau diorganisir dalam sebuah tabel. 

Salah satu syarat sebuah tabel yang baik (tabel yang mudah diolah lebih lanjut) adalah semua baris terisi data secara kontinyu, atau tidak ada sel kosong pada kolom-kolom kunci.

Dalam prakteknya bisa jadi kita dihadapkan pada data yang tidak kontinyu, yaitu baris data diselingi baris cell yang kosong yang diasumsikan data tersebut sama dengan data di atasnya.

Sebelum melanjutkan, boleh donk, dilirik sebentar video ini, maaf videonya masih amatiran...:-)



Jika belum cukup faham dengan video diatas, mari kita lanjutkan pembahasan nya.

Perhatikan screenshot berikut yang menunjukan sebuah tabel berisi data nama kabupaten, kecamatan dan desa.

Mengisi Sel Kosong Pada Excel


Dapat kita lihat pada sceenshot di atas, beberapa sel di bawah nama kabupaten dan kecamatan dibiarkan kosong. Sel kosong tersebut sebenarnya mengindikasikan nama kabupaten dan kecamatan yang sama dengan di atasnya.

Anggaplah anda ingin menggunakan fungsi COUNTIF, SUMPRODUCT, Rumus Array, atau fungsi lainnya untuk menghitung jumlah desa yang merupakan bagian dari kabupaten Ciamis. 

Berapa nilai yang akan didapat?

Ya, Maka anda hanya akan mendapatkan nilai 1 karena baris yang berisi data lengkap pada semua kolom hanya ada satu baris.

Selain itu, kita juga tidak dapat menerapkan fitur excel lainnya seperti pivottable, autofilter, dan data sort terhadap model tabel seperti contoh di atas.

Tugas kita sekarang adalah bagaimana mengisi sel kosong tersebut dengan mengambil data dari atasnya, sehingga tabel tersebut dapat diolah lebih lanjut dengan lebih mudah.

Bisa saja kita mengcopy data satu persatu secara manual untuk memenuhi semua sel. Tetapi jika kita memiliki ribuan baris data, tentu saja cara tersebut akan sangat menguras waktu dan tenaga.

Lalu bagaimana cara mengisi sel kosong dengan cepat? Sepanjang pengalaman saya, setidaknya ada tiga metode yang dapat dilakukan. Jika pembaca punya cara lain, silahkan ditambahkan.

  1. Mengisi sel kosong dengan menggunakan rumus IF dan kolom baru.
  2. Mengisi sel kosong dengan bantuan alat Go To Special
  3. Mengisi sel kosong dengan VBA/Macro

Mari kita gali lebih lanjut satu per satu.


Cara 1: Mengisi sel kosong menggunakan rumus IF 


Untuk mengisi sel kosong menggunakan rumus IF, kita perlu menyisipkan kolom baru yang nantinya akan menggantikan kolom yang ada sel kosongnya.

  • Dengan menggunakan contoh sesuai screen shot diatas, sisipkan 2 kolom (pada kolom A:B).
  • Kemudian pada sel A2 ketikan rumus =IF(C2="",A1,C2)
  • Copy rumus tersebut pada kolom A dan B sampai dengan baris akhir data.

Rumus IF Untuk Mengisi Sel Kosong Excel

  • Supaya rumus menjadi value, maka data di kolom A dan B perlu di konversi menjadi value dengan cara copy paste value (Seleksi kolom A dan B, kemudian klik kanan, pilih copy, lanjutkan klik kanan lagi, paste special, pilih value dan kemudian tekan OK)
  • Langkah terakhir : delete kolom C dan D,  maka kita akan mendapatkan sebuah tabel data dimana semua baris sel terisi sehingga lebih mudah untuk di olah lebih lanjut.

Catatan: Kita juga dapat membiarkan rumus IF tanpa merubahnya menjadi value. Hanya saja kita tidak bisa menggunakan firtur data sort jika kolom berisi formula, dan kita tidak bisa men-delete kolom awal nya karena jika di delete maka rumus IF akan error #REF.  Cara ini sering saya lakukan jika tidak ingin merubah struktur tabel data aslinya, tetapi data tetap dapat diolah lebih lanjut menggunakan pivotTable.

Cara 2: Mengisi sel kosong menggunakan rumus dan alat Goto Special.


Cara ke-2 ini memanfaatkan rumus sederhana =SelAtas, dengan terlebih dahulu menyeleksi sel kosong dengan bantuan fitur Goto Special.

  • Seleksi range sel yang akan diisi sel kosongnya, kemudian tekan shortcut F5  atau CTR + G untuk memunculkan jendela Go To.
  • Klik tombol Special...
  • Pilih opsi Blanks
  • Klik tombol Ok
  • Ketik =
  • Tekan tanda panah atas ↑
  • Tekan CTR + Enter

Berikut screenshot langkah-langkah mengisi sel kosong menggunakan Goto Special.

Goto Special Blanks Untuk Isi Sel Kosong
Rumus Isi Sel Kosong Goto Special



  • Langkah terakhir: Jika diperlukan, lakukan konversi rumus menjadi value dengan cara copy paste value seperti cara pertama tadi.


Cara3: Mengisi Sel Kosong Menggunakan Makro / VBA


Jika kita sering berulang kali harus melakukan isi sel kosong, maka alangkah baiknya - dan sangat disarankan - untuk menggunakan makro / VBA sehingga dapat lebih cepat setiap kali kita perlu melakukan langkah tersebut.

  • Masuk ke VBA editor dengan menggunakan shortcut ALT + F11, atau melalui Ribbon Tab Developer dan klik Visual Basic.

Memunculkan Jendela VBA Isi Sel Kosong

  • Di dalam jendela VBA Editor, klik menu Insert, kemudian klik Module. Langkah ini untuk membuat module standar baru sebagai tempat untuk menempatkan code VBA.

Insert Module Untuk Kode IsiSelKosong

  • Ketik atau copy code berikut di dalam module, ini merupakan contoh code VBA sederhana untuk mengisi sel kosong dengan data dari sel dari sebelah atasnya.
Sub isiSelKosong()
Dim sel As Range
For Each sel In Selection
    If sel = "" Then sel = sel.Offset(-1, 0)
Next
End Sub

Setelah code tersebut di-copy ke dalam module standar, maka perintah isiSelKosong sudah siap membantu anda setiap kali anda harus mengisi sel kosong pada data excel.

Untuk menjalankan macro tersebut sangatlah mudah:

  • Seleksi range sel yang akan diisi sel kosong, 
  • Masuk ke tab Developer
  • Klik Macro
  • Pilih macro isiSelKosong dan klik Run.

Untuk lebih jelasnya, perhatikan langkah-langkah sesuai arah anak panah dalam screenshot berikut:

Macro Untuk Isi Sel Kosong
Isi Sel Kosong Dengan VBA Excel


Demikian penjelasan 3 cara cepat untuk mengisi sel kosong dengan mengambil data dari sel tidak kosong di atasnya. Mudah mudahan bermanfaat.

Terimakasih.

Baca juga tips excel lainnya:


PANDUAN LENGKAP PENJUMLAHAN DI EXCEL

Fungsi SUM dan Rumus Penjumlahan Pada Excel

Penjelasan Lengkap Rumus Penjumlahan Pada Excel Ada banyak cara untuk melakukan penjumlahan pada Microsoft Excel. Cara yang paling sederhan...