Tampilkan postingan dengan label Excel 2010. Tampilkan semua postingan
Tampilkan postingan dengan label Excel 2010. Tampilkan semua postingan

Mengelola Name Range Pada Excel 2007 / 2010


Named Range adalah suatu fasilitas di Excel untuk menamakan range data dengan satu nama tertentu

Keuntungan memakai Named Range adalah kita dapat menggunakannya dalam fungsi / formula, dan jika terjadi perubahan terhadap cakupan data tersebut maka fungsi yang menggunakan definisi tersebut akan konsisten mengambil cakupan data yang baru.

Nah, jika tidak menggunakan penamaan dengan Named Range kita mungkin harus merubah setiap fungsi yang terlibat, merepotkan bukan?




Pada Excel 2007 / 2010, Anda dapat melihat ribbon yang khusus untuk menangani named range ini pada bagian tab Formula, group Defined Names.








Group Defined Names pada tab Formula


Pada ribbon ini, Anda dapat mendefinisikan, merubah, dan menghapus nama-nama range yang diinginkan. Selain itu untuk mendefinisikan nama Anda juga dapat menggunakan Name Box yang terletak di sebelah kotak formula.








Area Name Box yang terletak di sebelah Formula Bar

Mendefinisikan Named Range dengan Name Box

Berikut ini adalah contoh penggunaan praktis penggunaan named range :
Download dan buka file contoh dari http://goo.gl/LTQ1NN.
Klik tombol Enable Editing jika muncul peringatan pada toolbar Excel 2010.


Pilih range A3:C14.


Pada kotak Name Box, masukkan nama "DaftarArtikel" dan tekan tombol Enter.


Cobalah arahkan cell ke tempat lain, misalkan ke D4.
Klik panah bawah pada Name Box, klik pilihan DaftarArtikel. Terlihat range telah dipilih kembali.
Definisi nama range Anda telah siap untuk digunakan pada berbagai formula. Sebagai contoh, coba masukkan formula berikut pada cell E3 :

=VLOOKUP(6,DaftarArtikel,2)

Hasil yang didapatkan harusnya : Penggunaan Rumus.


Menghapus Named Range

Pada ribbon, pilih tab Formulas, dan pada group Defined Names, klik Name Manager.


Pada dialog yang muncul, pilih Delete dan lakukan konfirmasi untuk penghapusan.


Pada dialog tersebut, Anda juga bisa melakukan penambahan atau perubahan nama range. Silahkan bereksperimen dengan fitur ini sesuai kebutuhan Anda.
Selesai.
More about → Mengelola Name Range Pada Excel 2007 / 2010

Tips : Menggunakan Auto Fit pada Excel 2010

Pernahkan Anda menemukan data-data pada Excel yang sepertinya diatur dengan sangat "sembrono" dan tidak rapi seperti gambar berikut ?


Ini dikarenakan kolom-kolom tersebut sama panjangnya, harusnya diatur sesuai isi masing-masing. Mari kita perbaiki dan rapikan dengan langkah-langkah berikut ini.

  1. Download contoh file dari link https://belajar-excel.googlecode.com/files/ms_cabang.xls.
  2. Buka file tersebut dengan aplikasi Excel 2010.
  3. Klik tombol "Enable Editing" pada peringatan yang muncul pada Excel 2010, ini dikarenakan security yang memproteksi Excel dari file-file yang diambil dari internet.


  4. Pilih semua cell yang akan Anda lakukan auto fit, atau tekan tombol CTRL + SHIFT + *.


  5. Pada ribbon Home, pilih menu Format | Autofit Column Width.


  6. Kolom-kolom pada table Anda sudah rapi sekarang.


  7. Selesai.
More about → Tips : Menggunakan Auto Fit pada Excel 2010

Contoh Kasus : Pencarian Multi Variable dengan Match dan Index

Pertanyaan

Saya lagi cari rumus utk bisa mengembalikan nilai dari pencarian yang menggunakan 3 variabel.. misalnya saya punya sheet Daftar Harga Barang dgn kolom sbb:
  • Kolom 1 : Berupa Kode/ID
  • Kolom 2 : Berupa tipe konsumen (reseller/langsung)
  • Kolom 3 : Harga barang.
di sheet yang lain saya punya rincian transaksi penjualan yang disusun perhari. Pada sheet ini sy punya kolom:
  • Kolom 1: Tanggal
  • Kolom 2: Kode/ID
  • Kolom 3: Tipe konsumen
Nah saya menginginkan pada Kolom 4 dapat muncul harga barang tersebut utk Kode dan tipe konsumen yg sudah saya catat sebelumnya.

Bagaimana caranya? function apa yg perlu sy gunakan dan gimana rumusnya.

Jawab

Solusi untuk masalah ini adalah fungsi yang digunakan adalah Index dan Match, tetapi dengan pendekatan Array Formula. Berikut adalah rincian jawabannya.

Untuk menjawab pertanyaan ini, kami menggunakan file yang dapat Anda download pada alamat http://goo.gl/VSZL9. Workbooknya akan terlihat sebagai berikut.

Workbook ini memiliki sheet "data" dan "master_harga" (klik pada tab dengan nama sheet tersebut untuk melihat rincian dari tiap sheet).



Sekarang kita akan mengisi kolom Harga yang didapatkan dari pencarian kombinasi kolom Kode / ID dan Tipe ke sheet master_harga. (klik gambar untuk memperbesar)


Berikut adalah langkah-langkah untuk melakukan kombinasi pencarian tersebut :
  1. Jalankan aplikasi Microsoft Excel 2007 atau ke atas.
  2. Buka file yang Anda download tersebut.
  3. Pada sheet data, klik cell pada alamat D2 dan masukkan rumus di bawah ini. Perhatikan bahwa kita menggunakan operator dan ( & ) untuk menggabungkan cell B dan C (kolom Kode / ID dan Tipe).

    =INDEX(master_harga!$C$2:$C$11,MATCH(B2&C2,master_harga!$A$2:$A$11&master_harga!$B$2:$B$11,0))

    Tekan CTRL + SHIFT + ENTER untuk menyelesaikan formula tersebut (bukan Enter seperti biasanya).

  4. Hasilnya akan tampak seperti pada gambar screenshot sebagai berikut. Perhatikan bahwa rumus yang kita masukkan diapit oleh kurung kurawal {}. Ini pertanda bahwa formula tersebut adalah array formula.
  5. Copy rumus tersebut ke kolom Harga yang masih kosong.

  6. Selesai.

Referensi

More about → Contoh Kasus : Pencarian Multi Variable dengan Match dan Index

Penggunaan ISBLANK pada Excel

Fungsi ISBLANK digunakan untuk melakukan pengecekan apakah suatu cell berisi nilai kosong atau null. Jika memang kosong atau null, maka akan mengembalikan nilai TRUE (BENAR), sebaliknya mengembalikan FALSE (SALAH).

Sebagai contoh, pada Gambar.1 berikut cell C3 berisi rumus =ISBLANK(B3). Rumus ini digunakan untuk melakukan pengecekan apakah cell B3 kosong. Ternyata cell B3 berisi teks "Satu", dengan demikian tidak kosong dan fungsi ISBLANK(B3) akan mengembalikan nilai FALSE.

Gambar.1 Contoh Pengecekan ISBLANK yang mengembalikan nilai FALSE


Sedangkan pada Gambar.2 berikut cell C4 berisi rumus =ISBLANK(B4). Rumus ini digunakan untuk melakukan pengecekan apakah cell B4 kosong. Dan ternyata hasilnya memang kosong. Dengan demikian fungsi ISBLANK(B4) akan mengembalikan nilai TRUE.

Gambar.2 Contoh Pengecekan ISBLANK yang mengembalikan nilai TRUE

Demikian contoh penggunaan sederhana dari ISBLANK. Semoga bisa bermanfaat bagi para pengunjung sekalian. Saran, kritik dan komentar apapun dengan senang hati kami terima demi kemajuan situs ini. Terima kasih.

More about → Penggunaan ISBLANK pada Excel

Tanda $ (Absolute Reference) pada Excel

Simbol $ digunakan sebagai referensi absolut dari alamat cell (kolom ataupun baris)
Bagi kita yang baru bekerja dengan Excel, tanda $ yang terdapat pada rumus-rumus Excel terasa membingungkan. Kenapa simbol ini kadang muncul, kadang malah tidak sama sekali.

Artikel tutorial berikut ini mencoba menjawab hal tersebut denan memberikan contoh secara langsung, langkah demi langkah, apa dan bagaimana tanda $ atau absolute reference digunakan di dalam Excel 2007.

Download contoh file dari http://belajar-excel.googlecode.com/files/penggunaan_simbol_dollar.xlsx.
  1. Jalankan program Microsoft Excel 2007 dan bukalah file penggunaan_simbol_dollar.xlsx yang telah Anda download tersebut.
  2. Tempatkan cell pada alamat E2, ketikkan rumus = C2 * D2, dan tekan Enter. Hasil perkalian yang didapatkan adalah nilai 1,500,000.
  3. Tempatkan cell kembali pada alamat E2, klik fill handle pada cell tersebut dan tarik (drag) sampai ke E4.



    Hasilnya adalah perkalian yang dinamis, dimana rumus yang di-copy dengan cara penarikan fill handle ke cell E3 dan E4 bukan berisi = C2 * D2, melainkan = C3 * D3 (pada cell E3) dan = C4 * D4 (pada cell E4).

    Ini menunjukkan bahwa Excel akan menyesuaikan alamat cell, dimana setiap perpindahan baris akibat dari drag fill handle akan mengakibatkan perpindahan nomor baris alamat pada rumus cell.
  4. Sekarang tempatkan cell pada alamat F2, ketikkan rumus = C$2 * D$2, dan tekan Enter.



    Perhatikan kita memasukkan tanda $ pada alamat baris. Jika susah mengetik tanda dollar tersebut, tekan F4 dua kali sehingga tanda dollar akan berada pada posisi yang tepat.
  5. Klik fill handle pada cell F2 dan tarik (drag) sampai ke F4.



    Terlihat bahwa semua cell (F3 dan F4) mengandung rumus perkalian dengan alamat baris yang tidak berubah (C2 dan D2). Ini dimungkinkan karena adanya tanda dollar pada alamat baris tersebut, sehingga ketika kita copy formula tersebut dengan fill handle, maka perpindahan baris tidak menghasilkan perubahan.
  6. Selesai.
Berikut adalah keterangan sekaligus kesimpulan dari praktek di atas :
  1. Tanpa penggunaan tanda dollar ($) pada alamat cell, maka duplikasi cell yang mengandung rumus dan alamat akan disesuaikan dengan perubahan baris (ataupun kolom).

    Penurunan 1 baris akan mengakibatkan penambahan alamat baris sebesar 1 cell, penurunan 2 baris akan mengakibatkan penambahan 2 cell, dan seterusnya.
  2. Dengan penggunaan awalan tanda $ pada alamat cell, maka duplikasi cell tidak akan mengakibatkan perubahan alamat cell. Berikut adalah 3 contoh variasi penulisan prefix dan penjelasannya :
    • A$1 : alamat kolom A bisa berubah sesuai duplikasi, tetapi alamat baris 1 akan tetap (absolut).
    • $A1 : alamat kolom A tidak bisa berubah (absolut) tetapi alamat baris 1 bisa berubah.
    • $A$1 : alamat kolom A maupun baris 1 tidak akan mengalami perubahan ketika diduplikasi ke cell lain.
Demikian artikel tutorial singkat tapi cukup padat mengenai penggunaan tanda $. Semoga bisa bermanfaat bagi kita semua.



More about → Tanda $ (Absolute Reference) pada Excel

Memahami Berbagai Jenis Copy Paste pada Excel


Jika Anda perhatikan pada Excel, operasi copy paste tidak hanya menyalin nilai ataupun rumus dari suatu cell / range ke cell / range lain sebagaimana umumnya pada aplikasi lain.

Selain kedua duplikasi / penyalinan tersebut, terdapat juga penyalinan format, comment, dan lain-lain. Semuanya terangkum pada Paste Special.

Tabel di bawah ini menunjukkan daftar beberapa jenis paste special tersebut. Disertakan juga keterangan dan hasil dari operasi paste dilengkapi dengan screenshot.

Cell yang dicopy ditunjukkan pada gambar berikut, memiliki formula =A3*B3, dengan nilai  6312,  memiliki warna latar kuning, garis batas (border), dan comment.



File Latihan



No.Jenis PasteDeskripsiScreenshot Hasil
1
FormulasMenyalin Rumus / Formula
2
ValuesMenyalin Nilai Saja
3
FormatsMenyalin Format
4
CommentsMenyalin Comment / Komentar
5
ValidationMenyalin aturan validasi. Pada screenshot di samping ditunjukkan hasil salinan formula, kemudian dilanjutkan penyalinan validasi dan hasil pengecekan.
6
All using Source themeMenyalin formula, value, format, comment, border, dan validation dengan menggunakan theme yang terdapat pada sumber (source).
7
All except bordersMenyalin formula, value, format, comment, dan validation. Namun tidak menyertakan garis batas (border).
8
Column widthsMenyalin Lebar Kolom
9
Formulas and number formatsMenyalin Formula dan Formatnya
10
Values and number formatsMenyalin Nilai dan Format
More about → Memahami Berbagai Jenis Copy Paste pada Excel

Menggunakan Sparklines pada Excel 2010

Pada Excel 2010 ada satu tipe visualisasi yang sangat menarik dan biasanya menjadi fitur digital dashboard, yaitu Sparkline.


Sparkline adalah suatu line chart dalam format ukuran yang sangat kecil sehingga bisa dimuat dalam satu cell Excel. Karena berukuran mini, data dan indikator yang ditampilkan bisa sangat banyak dan membuat kita memahami data secara satu kesatuan dengan lebih baik.

Penggagas atau penemu sparkline adalah Edward Tufte, seorang pakar di bidang statistik dan visualisasi data yang memiliki kredibilitas tinggi.

Artikel berikut akan mencoba menjelaskan cara pembuatan, melakukan format, dan cara menghilangkan sparkline. Semoga bermanfaat.

File Latihan


Menambahkan Sparkline

  1. Jalankan aplikasi Microsoft Excel 2010, dan buka file data-sparklines.xlsx yang telah Anda download di atas.
  2. File akan terbuka, tetapi pada bagian atas workbook ini akan muncul suatu panel warna kuning berupa peringatan bahwa file ini tidak bisa diedit karena berasal dari Internet (lihat gambar). Klik tombol Enable Editing sehingga kita bisa lanjutkan latihan kita.

  3. File ini berisi data periodik bulanan untuk jumlah item terjual berdasarkan kategori produk.

  4. Tempatkan cursor pada cell di  bawah kolom Sparklines 1, atau pada alamat cell N2.

  5. Pilih range data N2:N41, dimana kita akan menempatkan semua sparkline.
  6. Pada menu ribbon, pilih tab Insert, pada group Sparklines klik Line.

  7. Pada dialog Create Sparklines, masukkan data range B2:M41, klik tombol OK.

  8. Di bawah kolom Sparklines 1, akan muncul line chart kecil yang muat ke dalam satu cell, inilah yang disebut dengan Sparklines.

  9. Kita akan tambahkan jenis sparklines lain. Di bawah kolom Sparklines 2, pilih range O2:O41.
  10. Pada menu ribbon, pilih tab Insert, pada group Sparklines klik Column.

  11. Pada dialog Create Sparklines, masukkan data range B2:M41, klik tombol OK.
  12. Sebagai hasilnya, di bawah kolom Sparklines 2 akan muncul sparklines kedua bertipe column.

  13. Untuk selanjutnya kita akan melakukan format sparkline ini lebih lanjut.

Format Sparkline

  1. Tempatkan cell pada alamat N2, pada menu ribbon klik tab Design pada Sparkline Tools yang muncul ketika cell kita arahkan pada sparklines.

  2. Masih pada ribbon, grouping Show, centang pilihan High Point dan Low Point.

  3. Perhatikan perubahan pada sparklines kita, terdapat dua titik pada tiap sparkline yang menandai nilai minimum dan maksimum.

  4. Rubah warna untuk titik maksimum dengan cara klik Marker Color, High Point dan pilih warna yang kita inginkan.

  5. Hasil akhir sparkline akan tampak sebagai berikut.

  6. Selesai.

Menghilangkan Sparkline

  1. Kita akan hilangkan sparklines yang ada di bawah kolom Sparklines 2. Arahkan cell ke alamat O2.
  2. Pada menu ribbon Sparkline Tools, tab Design, klik tombol Clear, Clear Selected Sparkline Group pada grouping Group.

  3. Terlihat sparkline bertipe column telah dihilangkan dari worksheet kita.

  4. Selesai.
More about → Menggunakan Sparklines pada Excel 2010

Mengurutkan Data (Sorting) pada Excel 2007 (Heading Kolom)

Pengurutan (Sorting) Data pada Excel adalah aktivitas dasar yang hampir kita lakukan setiap kali bekerja dengan worksheet. Artikel berikut akan menunjukkan langkah-langkah pengurutan data berdasarkan kolom pada Excel 2007.

File Latihan


Langkah-langkah melakukan pengurutan / sorting data :
  1. Jalankan program Excel 2007.
  2. Buka file transaksi.xlsx yang telah Anda download tadi.
  3. Pada tab transaksi, pilih semua data sebagai range atau tekan tombol CTRL + SHIFT + *.
  4. Pada menu ribbon pilih tab Data, klik menu Sort.

  5. Pada panel dialog Sort, pilih nilai_jual sebagai pilihan field Column. Klik tombol OK.

  6. Pada sheet kita, data akan terurut berdasarkan nilai_jual terkecil sampai dengan terbesar.

  7. Selesai.

More about → Mengurutkan Data (Sorting) pada Excel 2007 (Heading Kolom)