Featured Post

Cara Menggunakan Rumus untuk Data Validation

Bagaimana cara menggunakan rumus untuk validasi data di Excel? Dengan rumus, kita bisa melakukan kustomisasi data validation. Yuk kita lihat...

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 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:






Friday, June 2, 2017

Konversi Excel Menjadi Image atau Gambar.

Konversi range excel menjadi gambar atau image biasanya diperlukan dalam beberapa situasi. Misalnya ketika sedang membuat tutorial dan presentasi. Hal ini juga dibutuhkan untuk membuat ilustrasi ketika sedang berdiskusi online sehingga peserta diskusi dapat lebih memahami topik yang sedang dibahas.

Nah, belajar excel kali ini akan membahas beberapa cara untuk mengkoversi range data excel menjadi image atau gambar, baik dengan menggunakan tools yang tersedia di window  maupun menggunakan tools gratis yang tersedia secara online.





Berikut pilihan cara yang dapat dilakukan.

  • Konversi excel menjadi image menggunakan fitur print screen
  • Konversi excel menjadi gambar menggunakan Snipping Tool
  • Menyimpan excel sebagai JPG dengan cara copy paste
  • Konversi obyek excel menjadi image dinamis menggunakan camera tool
  • Konversi obyek excel menjadi image dinamis dengan cara copy paste linked picture
  • Konversi obyek excel menjadi image dinamis pada excel 2003
  • Konverter online Excel to JPG

Langsung saja, mari kita bahas satu persatu.

Konversi excel menjadi JPG menggunakan Fitur Print Screen


Metode ini merupakan cara terpopuler untuk mengkonversi tampilan layar monitor komputer menjadi file gambar. Cara melakukannya pun cukup sederhana.

  1. Sesuaikan atau atur zoom worksheet sehingga semua data atau object yang diperlukan dapat terdisplay jelas di layar monitor.
  2. Tekan tombol Prt Sc pada keyword sambil menahan tombol Alt
  3. Buka aplikasi pengedit gambar, misalnya Microsoft Paint
  4. Paste screen dengan cara klik  tombol Paste atau dengan shortcut Ctr+V
  5. Lakukan crop untuk memilah bagian mana yang dikehendaki
  6. Tekan Ctr+S untuk menyimpan file.
  7. Pada dialog box Save As, ketik nama file yang dikehandaki, dan pilih format yang dikehendaki (JPEG, GIF, PNG)
  8. Klik Save


Berikut contoh ilustrasi print screen excel yang di copy paste ke applikasi Microsoft Paint untuk selanjutnya di crop dan dapat disimpan sebagai file gambar (image), misalnya dengan format .jpeg, .gif atau .png.

konversi excel to jpg


Konversi xls menjadi .jpeg menggunakan Snipping Tool


Cara lainnya yang sangat sederhana untuk mendapatkan gambar dari lembar excel adalah menggunakan Snipping Tool. Apliksi ini  biasanya sudah tersedia bersama insatalasi windows 7 s.d Window 10.

Untuk menemukan aplikasi Snipping Tool di komputer, klik Start dan ketik snipping tool pada kotak search. Selanjutnya kita akan menemukan snipping tool dalam list hasil pencarian. Klik untuk membuka dan memulai aplikasi.

Berbeda dari Print Screen, Snipping Tool menyediakan beberapa pilihan pada saat meng-capture screen, seperti terlihat dalam snapshot berikut:

Snipping Tool Excel to JPG

  • Free-form Snip – Meng-capture bagian screen dengan bentuk bebas.
  • Rectangular Snip – merupakan opsi default yaitu untuk menangkap bagian screen dalam bentuk kotak (rectangular)
  • Window Snip – untuk meng-capture keseluruhan dialog box atau jendela yang sedang aktif.
  • Full-Screen Snip – Menangkap keseluruhan screen, fungsi ini sama seperti fitur print screen keyboard.

Untuk mengkonversi lembar excel menjadi JPG atau format image lainnnya menggunakan snipping tool, lakukan  3 langkah berikut.

  1. Pilih jenis snip atau potongan screen yang anda inginkan. Caranya : klik tanda segitiga kecil pada comand New, dan pilih salah satu mode dari empat type yang sudah dijelaskan sebelumnya..
  2. Seleksi area yang ingin anda capture, dan begitu jari anda melepas mouse. Maka otomatis area yang diseleksi tadi akan dicopy ke jendela snipping tool. 
  3. Lalu simpan image tersebut dengan cara Klik tombol save, ketik nama file, dan pilih format yang dikehendaki (.jpg, .png atau .gif) kemudian tekan OK
Tip: Untuk membuat beberapa edit terhadap gambar dapat menggunakan tools Pen, Highlighter dan Eraser   seperti terlihat dalam screenshot berikut:

Snipping Tool Excel menjadi Image

Konversi range excel menjadi image (.jpg, .png atau .gif) dengan cara copy paste.


Kedua cara yang dijelaskan diatas cukup mudah dilakukan, namun sayangnya masih memiliki keterbatasan. Kedua cara tersebut hanya dapat mencapture bagian yang kelihatan (visible) saja di layar komputer.

Jika kita ingin mengambil data tabel yang terdiri atas ribuan baris dimana tidak semua bagian tabel nampak di layar monitor, maka kedua cara diatas tidak akan cukup membantu. Untuk mengatasi keterbatasan ini maka kita bisa menggunakan tehnik copy/paste untuk mengambil bagian tabel excel dan mengolahnya kemudian menggunakan aplikasi edit grafik seperti Microsoft Paint.

Untuk menyimpan excel sebagai .jpg, lakukan langkah berikut:

  1. Dengan menggunakan mouse, seleksi diagram, shape atau object excel lainnya  yang ingin disimpan sebagai image. Untuk  menyeleksi  tabel yang besar, bisa dilakukan lebih cepat dengan cara menyeleksi sel pertama, kemudiakn tekan Ctr + Shift + End untuk menyeleksi keseluruhan sel yang digunakan.
  2. Tekan Ctr+C untuk mengcopy seleksi ke clipboard
  3. Buka aplikasi Microsoft paint atau aplikasi image editing lainnya.
  4. Ctr + V untuk mengcopy ke aplikasi tersebut
  5. Simpan gambar sebagai picture dengan extension .jpg, .png, .gif atau extension lainnya sesuai fitur yang tersedia pada aplikasi editor gambar.

Selain copy paste data excel ke program aplikasi graphic editor dan menyimpannya sebagai file gambar. Kita juga dapat mengkonversi data excel menjadi image didalam file excel itu sendiri, caranya cukup sederhana

1. Seleksi bagian range excel yang akan di konversi
2. Pada tab home, klik segitiga kecil pada tombol copy, klik copy as picture.

Excel Copy As Picture Image


3. Kemudian akan muncul dialog box copy picture, tekan OK

xls copy picture

4. Seleksi sel dimana akan dilakukan paste, dan tekan Ctr V, dan duplikat tabel data excel dalam bentuk image pun akan muncul.


Konversi Range Excel menjadi image Dinamis Menggunakan Camera Tool





Salah satu cara untuk mengambil snapshot secara cepat adalah menggunakan camera tool. Fitur ini sangat bagus dan dapat membuat sebuah image dinamis yang tersinkron kepada dataset original sehingga image akan terupdate secara otomatis jika ada perubahan pada dataset ori-nya.

Untuk menggunakan camera tool, kita harus menambahkannya ke ribbon atau quick access secara manual karena alat ini biasanya tidak muncul secara dafault.

Untuk menambahkan command camera ke quick access, lakukan langkah-langkah berikut:

  • Klik tombol drop down di sebelah pojok kiri atas  jendela excel. Pilih more comands dan untuk mebuka dialog box options.
  • Pane sebelah kanan, dibawah choose commands from, pilih commant Not In the Ribbon.
  • Scroll ke bawah list sampai dijumpai command cammera.  Pilih dan klik untuk menambahkan button dan pindahkan ke seksi sebelah kanan.
  • Klik OK

excel camera tools


Maka sekarang kita memiliki tombol kamera pada quick access toolbar.

excel camera tool quick access

Menyimpan data tabel atau obect excel lainnya sebagai image menggunakn gunakan camera tool cukup mudah dilakukan. Ikuti langkah langkah – langkah berikut:

  1. Seleksi range sel yang ingin anda capture. Untuk membuat foto dari diagram atau grafik, seleksi range sel sekeliling diagram itu.
  2. Klik camera
  3. Klik sebuah sel dalam worksheet untuk menempatkan snapshot.
  4. Sekarang kita dapat membuat gambar menjadi lebih besar atau lebih kecil dengan cara drag atau mengaplikasikan Picture Styles menggunakan Picture Tools yang otomatis muncul seketika kita menyeleksi image.

Konversi Range Excel Menjadi Image Dinamis Dengan Cara Copy Paste Linked Picture.

Selain menggunakan camera tools, sebenarnya kita juga dapat membuat image yang dinamis dan link ke obyek excel dengan cara sopy paste spesial linked picture. Cara melakukannya sangat mudah.

  1. Seleksi range sel yang ingin anda capture. Untuk membuat foto dari diagram atau grafik, seleksi range sel sekeliling diagram itu.
  2. Pilih sel dimana image akan ditempatkan.
  3. Kemudian dari tab Home, klik segitiga kecil dibawah tombol Paste, pada bagian Other Paste Options, klik linked picture. 
  4. Dan image atau gambar dinamis yang link ke data set pun akan muncul. Perhaikan shapshot berikut menggambarkan tombol paste option untuk linked picture.

exce, copy paste special picture


Konversi Range Excel menjadi image Dinamis Pada Excel 2003

Jika anda masih menggunakan excel 2003 atau versi lawas, image dinamis juga dapat dibuat dengan cara paste picture link

Langkah-langkahnya :

  1. Seleksi range bagian yang akan di konversi menjadi image
  2. Pilih sel untuk menempatkan image
  3. Sambil menekan tombol keyboard Shift,  klik menu Edit, kemudian klik Paste Picture Link


excel paste picture link


Konverter Online Excel to JPG 


Jika kita memiliki banyak file excel yang akan diexport menjadi image, maka kita dapat menghewat waktu dengan memanfaatkan converter excel to jpg yang tersedia secara online. Berikut bebarapa converter yang tersedia di web.

Convertio – Mengkoversi XLS menjadi JPG online gratis

Online converter ini mendukung banyak variasi format dokumen dan image termasuk excel dan JPG
Bagaimana cara melakukannya?


  1. Seleksi sumber data yaitu file .xls atau .xlsx dari komputer, google drive, dropbox atau drag file tersebut ke halaman menggunakan mouse.
  2. Pilih “to JPEG, PNG, TIFF, BMP, GIF, dll)
  3. Download hasil konversi JPG file atau save secara langsung ke dropbox atau google drive (keunggulan dari konverter ini adalah mereka tidak meminta alamat email untuk mengirim output hasil konversi)
convertio converter excel to jpg


Catatan: untuk versi gratis Convertino, ukuran file maksimum yang dapat dikonversi adalah 100 MB.

Anda tertarik untuk mencoba, silahkan coba di link ini: Convertio Excel to JPG.


Zamzar – Free online Excel to JPG Conversions.

Zamzar merupakan salah satu layanan online gratis yang cukup popular. Layanan ini memberikan beberapa pilihan untuk mengkonversi file, diantaranya konversi excel menjadi JPG. Dengan menggunakan Zamzar kita harus menggunakan email untuk menerima output hasil konversi. Hal ini mungkin kurang disukai sebagian orang. Namun demikian layanan ini tetap gratis 

zamzar converter excel to jpg
Untuk mencoba zamzar, silahkan ikuti link berikut: : Zamzar XLS to JPG.

Selain kedua konverter online di atas, sebenarnya masih banyak converter lainnya yang dapat ditemukan di internet. Kita dapat menemukannya dengan menggunakan mesin pencari google. Cukup ketikan kata kunci untuk mencarinya misalnya “convert xls to jpg online”, “xlsx to jpg online” atau “Konversi Excel ke JPG Online”

---

Demikian pembahasan singkat perihal cara konversi excel menjadi image atau gambar. Semoga bermanfaat.


Silahkan di cek juga artikel belajar excel lainnya:







Saturday, January 28, 2017

Rumus Konversi Huruf Kolom Menjadi Angka

Formula Koversi Huruf Kolom Menjadi Nomor
Di Excel kita bisa membuat rumus untuk mengkonversi huruf kolom menjadi angka nomor urut kolom dalam spreadsheet.

Contohnya :

  • A menjadi 1
  • B menjadi 2
  • C menjadi 3
  • Z menjadi 26
  • AA menjadi 27
  • … dan seterusnya.


Adapun cara atau formula yang dapat digunakan adalah dengan memanfaatkan kombinasi fungsi COLUMN dan INDIRECT

Contoh Rumus Untuk Mengkonversi Huruf Kolom Menjadi Angka


Untuk lebih jelasnya mari kita lihat contoh rumus berikut. Silahkan copy tabel berikut ke dalam spreadsheet sel A1 sehingga kita bisa melihat hasilnya.





A
B
C
2
Contoh Rumus
Penjelasan
3
=COLUMN(INDIRECT("B1"))
Mengkonversi huruf B menjadi angka. Nomor urut kolom B adalah 2
4
=COLUMN(INDIRECT(B9&1))
Mengkonversi text kolom pada sel B9 (AA) menjadi angka. Nomor urut kolom AA adalah 27
5
=COLUMN(INDIRECT("XYZ1"))
Mencoba mengkonversi huruf XYZ menjadi nomor. Menghasilkan error #REF! karena ZYZ melebihi batas maksimuk kolom pada excel (kolom maksimal excel versi 2007 s.d 2016 adalah XFD=16384)
6
=COLUMN(INDIRECT(B10&1))
Mencoba mengkonversi huruf yang ada pada sel B10 yaitu ZZZ menjadi nomor. Menghasilkan error #REF! karena ZZZ melebihi batas maksimum kolom pada excel (kolom maksimal excel versi 2007 s.d 2016 adalah XFD=16384)
7


8
Test Ref:

9
AA

10
ZZZ



Penjelasan Cara Kerja Rumus Untuk Mengkonversi Huruf Kolom Menjadi Angka (Nomor Urut Kolom)


Pertama :

Fungsi INDIRECT digunakan untuk mendapatkan referensi sel sesuai text referensi yang diberikan.

Misalnya jika text yang diumpankan adalah "B1", maka INDIRECT akan mengarah ke sel referensi B1.

Contoh:

  • Rumus INDIRECT("C2") akan mengarahkan ke referensi C2 dan karena sel C2 berisi text "Penjelasan" maka rumus tersebut akan menghasilkan text "Penjelasan"
  • Rumus INDIRECT("B2") akan mengarahkan ke referensi B2 dan karena sel B2 berisi text "Contoh Rumus" maka rumus tersebut akan menghasilkan text "Contoh Rumus"


Kedua:

Fungsi COLUMN berguna untuk mendapatkan nomor urut kolom dari referensi yang diberikan.

Contoh:

  • Rumus COLUMN(B1) akan mendapatkan angka nomor kolom dari sel B1 yaitu 2
  • Rumus COLUMN(A1) akan mendapatkan angka nomor kolom dari sel A1 yaitu 1


Dari penjelasan cara kerja masing-masing fungsi di atas, kemudian dapat dijelaskan alur kerja formula excel untuk mengkonversi huruf kolom menjadi nomor sesuai contoh berikut :

  • Rumus masih lengkap
    • =COLUMN(INDIRECT("B1"))
  • Referensi Text “B1” sudah dirubah menjadi referensi B1 oleh fungsi INDIRECT
    • =COLUMN(B1)
  • Fungsi COLUMN menghasilkan angka 2 yang merupakan nomor urut kolom B 


Hal yang sama berlaku juga jika kita menggunakan referensi sel untuk menempatkan text referensi (perhatikan contoh pada tabel di atas baris ke-4
  • Rumus masih lengkap
    • =COLUMN(INDIRECT(B9&1))
  • Sel B9 berisi text "AA", dan jika digabung dengan angka 1 (rumus B9&1), maka hasilnya "AA1"
    • =COLUMN(INDIRECT("AA1"))
  • Referensi Text “AA1” kemudian dirubah menjadi referensi AA1 oleh fungsi INDIRECT, yang selanjutnya diolah oleh fungsi COLUMN
    • =COLUMN(AA1)
  • Fungsi COLUMN menghasilkan angka 27 yang merupakan nomor urut kolom AA dalam lembar kerja excel.

Perlu diperhatikan: Dalam contoh – contoh formula diatas kita menggunakan index baris 1, misalnya B1, XYZ1. Sebenarnya angka tersebut bisa diubah dengan angka berapa saja sepanjang masih dalam lingkup baris yang dibatasi dalam lembar kerja excel.

Kenapa Error:

Error terjadi karena penggunaan text kolom yang diluar batas jumlah kolom yang disediakan oleh excel.

Dalam contoh diatas jika kita menggunakan kolom XYZ atau ZZZ maka rumus akan menghasilkan nilai error #REF! karena XYZ dan ZZZ tidak tersedia di excel.

Kolom maksimal pada excel versi 2007. 1010, 2013 dan 2016 adalah XFD atau kolom ke-16384. Sedangkan untuk excel versi 2003, kolom maksimalnya adalah kolom IV atau kolom ke-256

Demikian tips singkat mengenai cara membuat rumus excel untuk mengkonversi huruf kolom menjadi angka nomor urut kolom.

Salam..

Baca juga, artikel tutorial belajar excel lainnya:






Sunday, January 22, 2017

3 Alternatif Rumus Cari Data Banyak Kriteria

rumus cari data banyak kriteria
Masih dalam lingkup topik pencarian data di excel. Catatan pelajaran excel kali ini akan membahas mengenai cara mencari data dengan banyak kriteria.

Pada postingan beberapa waktu yang lalu mengenai fungsi VLOOKUP sebenarnya sudah dibahas mengenai cara pencarian data dengan dua kriteria. Namun dalam contoh tersebut diperlukan kolom bantu yang berisikan data gabungan (concatenate) dari kriteria yang ditetapkan.

Nah… sedangkan dalam kesempatan ini kita akan menggunakan rumus pencari data banyak kriteria tanpa harus menggunakan kolom bantu.

Ada beberapa alternative formula yang dapat digunakan. Berikut 3 diantaranya:

  1. INDEX MATCH
  2. OFFSET MATCH
  3. INDIRECT-ADDRESS-MATCH-ROW-COLUMN

Ketiga formula tersebut harus dibuat dalam bentuk rumus array yaitu dengan cara menekan CTR+SHIFT+ENTER setiap kali selesai mengetik atau mengedit rumus.

Baiklah kita lanjutkan dengan Studi Kasus.






Studi Kasus Pencarian Data Dengan Banyak Kriteria di Excel


Anggaplah kita memiliki tabel data harga buah-buahan dari berbagai supplier. Label kolom tabel dari kiri ke kanan adalah:

  • Kolom A = No
  • Kolom B = Buah
  • Kolom C = Supplier
  • Kolom D = Harga

Selanjutnya misalnya kita harus mencari data harga buah dari suplier tertentu.

Dengan kata lain, ada dua kriteria yang harus diperhatikan dalam tugas pencarian data ini, yaitu:


  • Kriteria 1 = nama buah
  • Kriteria 2 = nama suplier


Pertanyaannya: Bagaimana rumusnya untuk mendapatkan data harga buah tersebut secara cepat sehingga tidak harus melihat satu persatu baris dalam tabel .

Contoh kasus dan pertanyaan tersebut dapat digambarkan sebagai berikut:

apa rumus untuk cari data dengan banyak kriteria

Maaf, gambar tersebut hanyalah sebagai contoh saja, dengan baris data yang sangat sedikit. Manfaat sebenarnya akan terasa jika bekerja dengan baris data yang besar yang menyulitkan pencarian data dengan mata langsung.

Selain itu, contoh yang ditampilkan juga hanya menggunakan 2 kriteria, namun pada dasarnya prinsip kerjanya adalah sama, baik menggunakan 2 kriteria, 3 kriteria atau lebih banyak lagi.

Selanjutnya mari kita bahas satu persatu.

Perlu di catat: Contoh-Contoh rumus yang ditampilkan dalam pembahasan ini menggunakan rumus array, sehingga ketika  selesai mengetik atau mengedit rumus, maka kita harus menekan CTR+SHIFT+ENTER.


Contoh Rumus Cari Data Dengan Dua Kritera : INDEX-MATCH


Salah satu rumus terpopuler yang biasa digunakan untuk pencarian data adalah kombinasi fungsi INDEX dan MATCH.

Dalam hal contoh kasus di atas, kita bisa menggunakan rumus berikut untuk mencari harga buah tertentu dari supplier tertentu:

{=INDEX(D2:D8,MATCH(G1&G2,B2:B8&C2:C8,0))}

Rumus INDEX MATCH cari data


Cara Kerja Rumus INDEX MATCH

  • Operator ampersand (&) digunakan untuk menggabungkan kriteria nama buah (G1) dan supplier (G2) serta menggabungkan masing-masing sel sejajar pada kolom  kolom buah (B2:B8) dan supplier (C 2:C8)
  • Fungsi MATCH berguna untuk mencari nomor index baris data yang dimana kolom B harus berisi nama buah yang sama dengan nama buah di sel G1, serta kolom C harus berisi nama buah yang sama dengan sel G2. Dalam contoh yang ditampilkan, rumus ini menghasilkan angka 3 yaitu posisi baris yang yang berisi buah pisang dan suplier CV Subur.
  • Angka yang diperoleh dari fungsi MATCH kemudian digunakan oleh fungsi INDEX sebagai row_index dalam range D2:D8. 
  • Dan sel baris ke-3 dalam range D2:D8 adalah sel D4, sementara itu sel D4 berisi nilai 8000.
  • Sehingga hasil akhir adalah 8000.



Contoh Rumus Cari Data Dengan Dua Kritera : OFFSET-MATCH





Fungsi OFFSET berguna untuk mendapatkan referensi sel sesuai jarak berapa kolom dan berapa baris dari sel acuan.
Dengan menggabungkannya dengan fungsi MATCH, maka kita akan menentukan berapa  baris jarak sel yang dicari dari sel acuan, kemudian mendapatkan nilai dari sel yang dicari tersebut.

{=OFFSET(D1,MATCH(G1&G2,B2:B8&C2:C8,0),0)}


Rumus OFFSET MATCH cari data banyak kriteria


Cara Kerja Rumus OFFSET-MATCH

  • Fungsi MATCH berperan sama seperti halnya dalam rumus INDEX MATCH, dimana fungsi MATCH  dalam contoh tersebut menghasilkan angka 3.
  • Angka 3 terebut kemudian dijadikan sebagai argumen rows oleh fungsi OFFSET, atau sederhana dapat dituliskan =OFFSET(D1,3,0) . Rumus berfungsi untuk mendapatkan nilai dari sel yang jarak nya 3 baris dan 0 kolom dari sel D1.
  • Dan sel dimaksud dengan kriteria tersebut adalah sel D4
  • Sementara itu sel D4 berisi nilai 8.000.   Bilangan inilah yang merupakan hasil akhirnya.

Contoh Rumus Cari Data Dengan Dua Kritera : INDIRECT-ADDRESS-MATCH-ROW-COLUMN


Fungsi INDIRECT berguna untuk mendapatkan nilai secara tidak langsung dari string yang mereferensikan sebuah alamat sel tertentu.

Sedangkan fungsi ADDRESS digunakan untuk membuat string alamat sel sesuai baris yang diperoleh dari fungsi MATCH dan kolom yang diperoleh dari fungsi COLUMN.

{=INDIRECT(ADDRESS(MATCH(G1&G2,B2:B8&C2:C8,0)+ROW(D1),COLUMN(D2:D8)))}

Contoh Rumus INDIRECT ADDRESS Cari data


Cara Kerja Rumus INDIRECT-ADDRESS-MATCH-ROW-COLUMN

  • Fungsi MATCH berguna untuk mendapatkan nomor urut kolom dalam range B2:B8 dan C2:C8 dimana sel yang sejajar berisi masing masing kriteria yang ditetapkan. Hasil dari proses tersebut ditambahkan dengan index ROW sel D1 untuk mendapatkan index baris dalam spreadsheet. Dalam contoh, fungsi match menghasilkan angka  3. Kemudian angka tersebut dikurangi 1 (posisi baris sel D1) sehingga mendapatkan angka 4.
  • Index Kolom dalam spreadsheet didapatkan menggunakan bantuan fungsi COLUMN,  dan kolom D2:D8 adalah 4 (kolom D)
  • Ringkasnya rumus tersebut dapat dikonvert menjadi =INDIRECT(ADDRESS(4,4))
  • ADDRESS(4,4) menghasilkan string referensi sel "$D$4"
  • Kemudian fungsi INDIRECT mengambil tugasnya untuk mendapatkan nilai dalam sel $D$4
  • Dan hasilnya adalah 8.000
  
Ringkasan.
Cari data dengan 2 kriteria atau lebih dapat dilakukan tanpa menggunakan kolom bantu, yaitu menggunakan rumus array, kombinasi INDEX-MATCH, OFFSET-MATCH, dan INDIRECT-ADDRESS-MATCH-ROW-COLUMN. Dari ketiga rumus tersebut ada persamaan peranan fungsi MATCH untuk mencari nomor urut baris yang memenuhi kriteria, yang selanjutnya dapat digunakan sesuai kebutuhan fungsi lainnya.
Demikian semoga bermanfaat.

Salam

Silahkan dibaca juga artikel Belajar Excel Lainnya: