Alulliangel: Oktober 2012

MENGAKSES DATA PADA SHEET YANG LAIN PADA FILE YANG BERBEDA

- Sabtu, 13 Oktober 2012

Jika Anda sering mengolah data pada Microsoft Excel dengan jumlah data yang banyak dan memerlukan dua atau lebih file Excel sebagai acuannya , maka Anda sebaiknya mengaktifkan Arrange Windows.

Arrange Windows pada fasilitas Excel digunakan untuk membuka beberapa jendela worksheet Excel dan untuk mengatur, mengedit dari beberapa file yang Anda kerjakan secara bersamaan sehingga Anda dapat melihat bagian yang berbeda dari lembar kerja dari masing-masing worksheet dalam satu layar pada saat Anda bekerja.

Cara menampilkan beberapa file dalam satu lembar kerja adalah:

1. Buka beberapa file Excel yang akan Anda kerjakan (dalam contoh berikut saya menggunakan dua file yang berbeda)

2. Lalu aktifkan ribbon View dan klik Arrange All

3. Maka akan tampil jendela Arrange Windows untuk menyusun jendela ditempat yang diinginkan dengan empat pilihan

Tiled untuk menampilkan pilihan file yang dibuka secara berdampingan di layar dalam urutan di mana Anda membuka file tersebut

Horizontal untuk menampilkan file yang dibuka secara horizontal dari atas hingga ke bawah.

Vertical untuk menampilkan file secara vertikal berdampingan dari kiri ke kanan

Cascade untuk menampilkan file secara tumpang tindih sehingga file yang dibelakang hanya akan terlihat judul filenya saja.

4. Lalu setelah Anda pilih salah satu diatas, Anda dapat mempergunakan semua file yang dibuka untuk mengolah, mengedit, mengcopy paste antara satu file dengan file yang lain dalam satu tampilan window.

MENGAKSES DATA PADA SHEET YANG SAMA PADA FILE YANG SAMA

Jika Anda bekerja dengan data yang kompleks, mungkin Anda akan merasa sulit dan repot untuk berpindah ke cell atau range yang jaraknya berjauhan atau bahkan berada di sheet atau workbook yang berbeda.
Untuk itu, posting saya kali ini akan membahas 4 fitur yang memudahkan Anda untuk mengatur tampilan data sehingga lebih mudah Anda tinjau.

1. Zoom Slider

Dengan Zoom Slider Anda dapat dengan mudah mengatur tampilan workbook sehingga bisa lebih jelas terlihat. Anda bebas mengatur seberapa besar presentase tampilan data.
Cara cepat mengaksesnya adalah dengan menggeser tombol pengatur pembesaran atau Plus dan Min Button pada zoom slider yang berada pada status bar sebelah kanan.

Di sebelah kiri zoom slider ini terdapat zoom level yang menunjukkan berapa besar persentase pembesaran dari workbook tersebut. Jika Zoom Level ini diklik maka akan muncul kotak dialog Zoom untuk pengaturan pembesaran lebih lanjut. Pilih Fit Selection untuk memperbesar tampilan dari range yang Anda sorot.

2. Freeze Panes

Freeze Panes digunakan untuk membekukan area tertentu pada workbook. Sehingga ketika Anda menggeser tampilan workbook maka area tersebut tidak ikut tergeser. Untuk mengaksesnya pada tab View kategori Window klik Freeze Panes.

Ada 3 kategori dalam freeze panes. Cara menggunakannya yaitu :
1. Freeze Panes
    Untuk membekukan baris dan kolom sesuai dengan posisi penunjuk yang aktif.
2. Freeze Top Row
    Untuk membekukan baris teratas pada sheet yang aktif.
3. Freeze First Column
    Untuk membekukan kolom pertama pada sheet yang aktif.
Untuk menonaktifkan Freeze Panes, pilih tab View kategori Window > Freeze Panes > Unfreeze Panes.

3. Split Windows

Split Windows digunakan untuk membagi workbook menjadi 2 atau 4 bagian yang mana ketika salah satu area tersebut digeser maka area lain tidak ikut tergeser. Cara menggunakannya adalah sebagai berikut :

Tempatkan penunjuk cell dimana tampilan workbook akan dibagi.
Pada tab View kategori Window klik Split, sehingga tampilan workbook menjadi seperti ini :

Untuk menonaktifkan Split, pilih tab View kategori Window lalu klik Split.

4. Arrange All

Arrange All digunakan untuk menampilkan data yang berada pada Sheet atau workbook yang berbeda dalam satu jendela. Untuk mengaksesnya pada tab View kategori Window, klik Arrange All, sehingga muncul kotak dialog Arrange Windows.

Anda dapat memilih bagaimana tampilannya :

Tiled, menampilkan workbook berbentuk tile.
Horizontal, menampilkan workbook secara horizontal.
Vertical, menampilkan workbook secara vertikal.
Cascade, menampilkan workbook berbentuk lembaran.

5. View Side by Side

View Side by Side digunakan untuk menampilkan data yang berada pada Sheet atau workbook yang berbeda secara berdampingan. Berbeda dengan Arrange All, fitur ini hanya dapat menampilkan dua workbook atau worksheet saja. Cara menggunakannya yaitu :

Pada tab View kategori Window, klik View Side by Side, sehingga muncul kotak dialog Compare Side by Side.

Pada kotak dialog ini, Anda diminta untuk memilih dengan workbook manakah yang akan ditampilkan Side by Side.
Setelah Anda pilih, tampilan jendela Excel akan seperti di bawah ini, dan kedua workbook ini akan bergeser secara bersamaan ketika dilakukan scrolling.

Untuk menonaktifkan Synchronous Scrolling, pada tab View kategori Window, klik Synchronous Scrolling. Sedangkan untuk menonaktifkan View side by Side, klik kembali View Side by Side pada lokasi yang sama.

Cara Menampilkan Dua Jendela Dalam Sebuah Worksheet

3. Maka akan tampil jendela Arrange Windows untuk menyusun jendela ditempat yang diinginkan dengan empat pilihan

Tiled untuk menampilkan pilihan file yang dibuka secara berdampingan di layar dalam urutan di mana Anda membuka file tersebut

Horizontal untuk menampilkan file yang dibuka secara horizontal dari atas hingga ke bawah.

Vertical untuk menampilkan file secara vertikal berdampingan dari kiri ke kanan

Cascade untuk menampilkan file secara tumpang tindih sehingga file yang dibelakang hanya akan terlihat judul filenya saja.

VLOOK UP & HLOOK UP

Vlookup

Fungsi vlookup merupakan sebuah fungsi Ms. Excel yang kurang lebih berfungsi untuk mencari atau melihat sebuah data “look” dari suatu table secara vertical. Lihat contoh gambar table dibawah ini

Jika terdapat daftar penjualan barang dalam sebulan dengan menggunakan kode kode diatas, dan kita harus mendapatkan total pemasukan dari penjualannya, hal termudah adalah menggunakan fungsi Vlookup adapun syntaknya sebagai berikut

=VLOOKUP(lookup_value,table_array,column_index_number,[range_lookup])

Penyelesaian

Terlihat dalam gambar fungsi vlookup sebagai berikut =VLOOKUP(G2,$B$2:$E$7,3,0)

• G2 ; merupakan letak cell kode yang akan dicari (lookup value)

• $B$2:$E$7 ; merupakan lokasi ataupun daerah cell table keterangan dari kode tersebut (Table array), perhatikan tanda $, tanda tersebut merupakan pengunci cell agar saat formula di copy cell tidak bergeser

• 3 ; merupakan nomor kolom yang akan ditampilkan (column index number), kolom 3 merupakan kolom harga, kolom 2 yaitu kolom jenis, kolom 4 adalah kolom kualitas, sedangkan kolom pertama yaitu kode itu sendiri

• 0 ; merupakan range lookup, dalam point ini range lookup dapat anda isikan true “benar” kode dalam table urut atau sesuai abjad, jika tidak hasil lookup akan terdapat #N/A atau tidak sesuai, jika range lookup anda isikan false “salah” hasilnya akan sesuai meskipun table dalam kondisi tidak urut, untuk angka 0 dapat diartikan “false”

Satu contoh kasus yang lebih kompleks, jika terdapat sebuah kode, dimana kode tersebut merupakan gabungan beberapa data yang sengaja disingkat agar lebih simple, Misalkan terdapat sebuah kode di pabrik konveksi, kita disuruh menentukan harga penjualan sebuah kain berdasarkan waktu produksi, kualitas, dan warna kain

0610AR, “0610” merupakan bulan dan tahun produksi, “A” kualitas kain, dan “H” warna kain

Dapat dipastikan kita akan memiliki beberapa tabel harga ataupun potongan menurut
1. Waktu produksi kain
2. Kualitas kain
3. Warna Kain
Untuk penyelesaian menggunakan fungsi vlookup dalam Ms. Excel sebagai berikut

• Pisahkan kode tersebut menjadi tiga kode dalam cell yang berbeda menggunakan fungsi Right, Mid dan Left, Untuk penjelasan ketiga fungsi tersebut baca posting tentang “Fungsi Left, Mid dan Right pada Ms. Excel”

• Jika bentuk kode Tanggal pada table sebagai berikut,

Maka kode yang kita ambil hanya pada bulannya saja “0610” menjadi “6” dengan cara mengganti formula Left menjadi seperti gambar dibawah,

Value adalah converter dari text menjadi angka *Penjelasan Fungsi Value dapat anda baca pada “Fungsi Value pada Ms. Excel”

• Langkah berikutnya adalah menamai ketiga table tersebut pada “name box”, Kegunaan menamai table tersebut agar dalam penulisan formula kita tidak susah payah untuk blog “table_array” atau lokasi table, untuk lebih detailnya lihat gambar

Lakukan seperti hal tersebut pada table kualitas dan harga dengan nama “name box” yang berbeda.
• Cari hasil lookup dari ketiga kode tersebut sehingga mendapatkan hasil table sesuai kode

Hasil daru formula diatas adalah 0.12 kenapa bukan 12%? Saya katakana sama saja, 12% adalah persentase dari pecahan 12/100 yang merupakan decimal dari angka 0.12 “mungkin anda sudah mengerti karena di SD kelas 3 sudah di bahas dalam satu semester”, lakukan pada kedua kode selanjutnya

Coba ubah Kode pada cell B3, contoh dengan kode 1110CB atau yang lainnya, cek hasil lookup dengan table apakah sesuai atau tidak, jika tidak sesuai maka akan tertulis #N/A , untuk menghindarinya dapat anda gunakan fungsi “Iferror”,

• Langkah selanjutnya tinggal mencari harga dari kode tersebut yaitu sebagai berikut,

Didalam pelajaran sekolah mungkin kita telah mempelajari matematika dengan bab rugi laba ataupun bunga dan potongan harga, jadi dari contoh soal diatas dapat kita ambiil syntak harga sebagai berikut :

Ha = Hs – { Hs x ( Pk + Pp )}
Ha : Harga setelah diskon
Hs : Harga sebelum diskon
Pk : Diskon menurut Kualitas barang
Pp : Diskon menurut Bulan Produksi

Dalam penghitungan di Ms. Excel dapat dituliskan sebagai berikut

Jika anda sudah biasa menggunakan Excel, mungkin formula ini lebih cocok untuk anda, yaitu formula dimana pembahasan panjang lebar diatas hanya disingkat kedalam satu formula saja.

Hlookup

Hlookup adalah sebuah fungsi yang sama dengan vlookup hanya saja terdapat perbedaan pada lookup data dimana vlookup secara vertical sedangkan hlookup secara horizontal, untuk lebih jelasnya saya sertakan sampel agar lebih dapat dipahami. Dalam kasus soal diatas pada penghitungan vlookup terdapat hasil harga berdasarkan kode yang tersedia, jika terdapat table potongan harga menurut banyaknya penjualan dimana table tersebut disajikan secara horizontal seperti gambar dibawah ini,

• Pertanyaan :

Pada gambar diatas terllihat table dimana pada kilogram hanya terdapat angka kelipatan 10, bagaimana jika pembelian pada angka 45? Bagaimana penghitungan dalam formula excel untuk mendapatkan persentase pada angka tersebut jika pada lookup value tidak ada angka 45?

• Jawaban :

Kita gunakan pembulatan kebawah, 45 kita jadikan 40, 56 jadikan 50, 99 jadikan 90 walaupun angka tersebut dekat dengan angka 100. Adapun caranya menggunakan fungsi “INT” untuk penjelasan fungsi tersebut silahkan cari di kategori Excel pada blog ini

Int merupakan fungsi Excel yang berguna untuk membuat pecahan ataupun decimal ke dalam bilangan bulat dengan cara pembulatan kebawah, /10 saya gunakan agar 45 menjadi bilangan decimal yaitu 4,5 setelah itu akan di bulatkan menjadi 4 oleh fungsi INTdan akhirnya saya kalikan 10 kembali sehingga menjadi angka 40, tapi itu tak penting yang penting anda tau maksud dari penggunaan fungsi hlookup yang akan saya bahas di bawah ini

Penggunaan Hlookup pada kasus di atas agar mendapatkan potongan harga sebagai berikut,

• $C$4:$M$5 = Adalah table_array atau lokasi table yang sebenarnya penulisannya sebagai berikut C4:M5, akan tetapi terdapat tanda dolar $, tanda tersebut berfungsi untuk mengunci cell, untuk lebih jelasnya lihat “Penggunaan fungsi Ms. Excel”, sebenarnya anda dapat menggunakan cara seperti pembahasan Vlookup yaitu menamai table pada name box, tetapi akan lebih baiik jika anda memiliki pengetahuan lebih

• Lihat lingkaran merah di “row_index_num” berbeda pada formula Vlookup dimana tertulis “col_index_num”, disitulah perbedaannya jika penyajian table secara vertical ataupun kolom (column) maka kita menggunakan fungsi vlookup, jika penyajian table secara horizontal, baris atau sering disebut (row) maka kita menggunakan fungsi Hlookup

=HLOOKUP(lookup_value,table_array,row_index_number,[range_lookup])

• Lihat lingkaran kedua, #N/A berarti dalam table tidak ada angka tersebut, bukannya 230 merupakan >100? Benar, akan tetapi dalam table tidak terdapat angka 230 hanya >100. Untuk mengatasi hal tersebut maka kita perlu menggunakan fungsi “If” atau “Iferror”

Penggunaan if

“Hlookup(“>100”,$C$4:$M$5,2,0) dapat langsung anda ganti “0.5” yang merupakan hasil dari fungsi tersebut
Penggunaan iferror

Untuk penjelasan fungsi IF dan IFERROR silahkan lihat pada kategori Excel di blog ini

Setelah saya perhatikan ternyata ada beberapa kesalahan seperti kolom potongan saya isi dengan hasil pembulatan kebawah dan kolom pembayaran terisi oleh potongan harga, nah dibawah ini gambar secara lengkap yang telah direvisi serta pemadatan formula agar simple.

Dan akhirnya selesailah pembahasan tentang fungsi Hlookup dan Vlookup pada Ms. Excel, Jika ada pertanyaan dengan senang hati saya akan menjawabnya jika didalam penjelasan ini terdapan penulisan yang kurang jelas, dan jika ada masukan, saran ataupun kritik dengan lapang dada saya akan menerimanya dan memebenahinya. Semoga berguna

DATA VALIDASI

Mengatur Validasi Pengisian Data Numerik ()

Validasi data digunakan untuk mengontrol pemasukan data dalam suatu range yang dipilih. Hal ini dimaksudkan agar dalam melakukan pengisian data pada worksheet dapat seakurat mungkin. Perintah ini sangat berguna dalam memasukkan data numerik, misalnya pada tabel nilai mahasiswa kolom nilai TUGAS, UTS dan NILAI data yang dimasukan harus berupa angka dengan rentang nilai 10 sampai dengan 100. Jika di luar range tersebut akan tampil komentar validasi sampai nilai yang dimasukkan memenuhi range yang telah ditentukan.

a. Perintah tab setting

Tab setting digunakan untuk mengatur validasi data. Caranya adalah sebagai berikut.
1) Blok terlebih dahulu Kolom UAS, UTS dan NILAI
2) Klik tab menu Data, kemudian pilih subtab Data Tool.
3) Klik pada Data Validation,

Sehingga menampilkan kotak dialog Data Validation. maka,

4) Klik panah bawah pada Allow, pilih sesuai dengan kebutuhan Anda misalnya Whole Number.
4) Klik panah bawah Data, pilih sesuai dengan kebutuhan misalnya Between.
5) Isikan nilai minimum yang diperbolehkan pada kotak Minimum.
6) Isikan nilai maksimum yang diperbolehkan pada kotak Maximum. Jika Anda menginginkan validasi data menjadi hilang caranya adalah dengan klik tombol Clear All.

b. Tab Input Message

Input message digunakan untuk memberikan pesan dalam validasi data. dimana pesan akan muncul pada saat sel di klik. Caranya adalah klik Input Message, masukan Judul Pesan dan Isi Pesan

c. Error Alert

Klik Error Alert adalah Pesan Error apabila data yang di input tidak memenuhi syarat sesuai dengan kondisi yang telah ditentukan. Perhatikan gambar berikut.

Tentukan jenisnya, ada 2 macam validasi.
1) Stop : akan menampilkan pesan dengan 2 jawaban, yaitu Retry dan Cancel.
2) Warning : akan menampilkan pesan dengan 2 jawaban, yaitu Retry dan Cancel.
Keterangan:
Anda akan mendapat warning ketika Anda memasukkan data di luar validasi yang telah ditentukan.
Berikut ini adalah hasil dari setting validasi data yang telah kita buat tadi

Alulliangel

Total Tayangan Halaman

Follow