Ms. Excel

·        Sorting

Sort adalah perintah untuk mengurutkan data berdasarkan kondisi tertentu. Cara menggunakan perintah ini adalah sebagai berikut :

Tempatkan kursor di dalam tabel data.

Klik menu Data.

Pada ribbon-bar klik Sort.

Kotak dialog Sort akan tampil seperti berikut ini.

Keterangan:

• Bagian Sort by berisikan nama field atau nama kolom yang digunakan sebagai acuan pengurutan data.

• Bagian Sort On digunakan untuk menentukan tipe data yang ingin diurutkan.

• Bagian Order digunakan untuk menentukan kondisi pengurutan data, yaitu secara Ascending (menaik) atau Descending (menurun).

• Tombol Add Level digunakan jika kita ingin mengurutkan data pada beberapa kolom atau beberapa baris sekaligus.

• Tombol Options digunakan untuk menentukan arah pengurutan data, apakah berdasarkan kolom (dari atas ke bawah) atau berdasarkan baris (dari kiri ke kanan).

• Pilihan My data has headers sebaiknya diaktifkan agar pada bagian Sort by dapat terlihat judul kolom atau judul baris yang terdapat pada tabel.

Sebagai contoh, pada tabel karyawan berikut yang telah diurutkan berdasarkan UNIT KERJA

Untuk mengurutkan nama karyawan dari A sampai ke Z, pilih fieldNAMA pada bagian Sort by lalu tekan tombol Ok.

Perhatikan tampilan data yang telah mengalami perubahan seperti berikut ini. Kolom NAMA masing-masing unit kerja telah diurutkan.

·        Data Volidation

Data validation adalah fitur pada Excel dimana kita bisa menetapkan aturan dan batasan pada suatu cell sehingga apabila terjadi pelanggaran dari aturan / batasan akan menyebabkan data menjadi tidak valid.

Namun jika terdapat banyak cell yang menerapkan data validation, dan kita ingin mencari cell-cell tersebut. Bagaimanakah caranya ? Berikut ini adalah caranya.

Langkah Pencarian

1. Pada sheet yang sedang aktif dan memiliki cell-cell data validation, klik tab Home pada ribbon.
2. Pada bagian Editing klik tombol Find & Select.

3. Dari daftar dropdown yang muncul, klik pilihan Data Validation.

4. Hasil pencarian menunjukkan cell-cell yang memiliki validasi data (data validation) dengan highlight warna abu-abu muda. Sedangkan heading kolom / baris yang memiliki data validasi ditunjukkan dengan warna orange.

5.      Selesai.

·        Filter

Filter adalah perintah untuk menyaring data dengan kriteria tertentu, dimana kriteria tersebut kita tentukan sendiri sesuai dengan yang kita butuhkan. Fasilitas ini sangat berguna, terutama jika kita bermaksud untuk mencari beberapa data saja dari sebuah data yang kompleks.

Contoh penggunaan perintah ini adalah sebagai berikut :
Buka tabel data yang akan disaring datanya, misalnya Daftar Nama Karyawan berikut : 

Klik menu Data lalu pada ribbon-bar klik Filter.


Sekarang perhatikan tabel data kita. Akan tampak tombol panah kecil di setiap nama field. Tombol panah kecil tersebut digunakan untuk menyaring data pada field yang bersangkutan (misalnya field Divisi). Pilih divisi data yang ingin Anda saring melalui tombol panah kecil tersebut (misalnya data yang akan ditampilkan Divisi Sekretaris Perusahaan saja)

Contoh hasil yang didapat sebagai berikut.

Untuk menghilangkan fasilitas Filter ini, klik kembali tombol Filter pada ribbon-bar.

·        Fungsi Vlookup Dan Hlookup Pada Ms. Excel

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.