Menggunakan Data Validation pada Ms Excel

Dalam pembuatan data dengan Microsoft Excel terkadang kita ingin cell tertentu hanya bisa di isi angka saja misalnya antara 100 s/d 1000 atau mungkin ingin membuat list dengan mudah, untuk semua itu kita bisa memanfaatkan fungsi dari menu Data Validation pada Microsoft Excel. Berikut cara menggunakannya.

1. Buka Tab Data > Pilih Perintah Data Validation.

2. Akan muncul jendela Data Validaton seperti di bawah.

• Pada window atau jendela Data Validation terdapat 3 tab:

1. Settings: Digunakan untuk menentukan pengaturan kriteria Data Validation.
2. Input Message: Memberikan pesan ketika cell yang di validasi di klik atau dipilih.
3. Error Alert: Pesan peringatan yang akan ditampilkan ketika user memasukkan data yang tidak sesuai dengan kriteria Data Validation.

• Pada tab Settings terdapat Validation Criteria, terdapat beberapa List Validation Criteria yaitu:

1. Any Value: Semua value (text maupun number) bisa di masukkan dalam cell atau sama artinya dengan tidak ada kriteria khusus pada Data Validationnya.
2. Whole Number: Hanya Format Number saja yang bisa di masukkan dalam cell, ada beberapa pilihan data yang bisa di pakai seperti between (antara), equal (sama dengan), grether than (lebih besar) atau less then (lebih kecil ) dan lain-lain.
3. Decimal: Sama seperti pilihan Whole Number hanya bedanya bisa dimasukkan tanda desimal.
4. List: bisa digunakan untuk membuat List atau daftar.
5. Date: Membuat data validation dengan kriteria Date atau penanggalan.
6. Time:  Membuat data validation dengan kriteria Time atau waktu.
7. Text Lenght: Membatasi jumlah panjangnya karakter Text.
8. Custom: Menentukan sendiri kriteria data validation dengan memasukkan rumus atau formula.

Contoh : Saya ingin membuat agar cell memiliki menu list untuk gender, saya tinggal memilih List pada Validation Criteria dan mengisi source dengan 'L' untuk laki-laki dan 'P' untuk perempuan kemudian memilih OK. Selain langsung memasukkan data source, anda juga dapat memilih data dari cell lain dengan memilih tombol yang ada di samping kotak Source.

 Selamat Mencoba.

Set Print Area di Microsoft Excel

Tidak seperti Ms Word, Ms Excel tidak memiliki layout kertas langsung yang dapat kita cetak. Ms Excel memiliki layout berupa cell yang di gabungkan, untuk mencetak data yang ada di Ms Excel, pertama kita harus memilih bagian cell yang akan dicetak. Pada hari ini saya akan membagikan cara menentukan print area pada Ms Excel, berikut caranya.

1. Buka Tab View > Pilih perintah Page Break Preview

2. Excel akan secara otomatis membagi cell menjadi format page yang dapat kita print.

3. Selanjutnya Block area yang ingin kita cetak, Buka Tab Page Layout > Pilih perintah Print Area > Set Print Area.

4. Selanjutnya anda dapat menggerakkan garis biru untuk mengatur layout yang akan di cetak pada halaman tertentu.

5. Selanjutnya anda tinggal melanjutkan proses mencetak seperti Ms Word, bila ingin menambah atau mengurangi wilayah yang ingin di cetak anda tinggal menggerakkan garis biru yang ada.

Selamat Mencoba.

Menggunakan Pivot Table pada Microsoft Excel.

Pivot Table adalah sebuah table khusus yang marangkum informasi dari beberapa kolom tertentu dari sebuah sumber data. Data yang akan disajikan dalam Pivot Table akan lebih sederhana, sehingga informasi yang dulunya kompleks dengan menggunakan Pivot Table akan menjadi lebih sederhana. Pada minggu ini saya akan membagikan cara membuat Pivot Table pada Microsoft Excel, berikut caranya.

1.Buka Tab Insert > PivotTable

2.Jendela baru untuk memilih table data dan tempat Pivot Table akan terbuka.

3.Bila telah selesai klik OK dan akan muncul jendela bagian untuk mengedit Pivot Table.

4.Untuk memilih data yang ingin dimasukkan kita hanya perlu mencentangnya dan mendragnya ke bagian yang kita inginkan.

• Filter akan berada di bagian paling atas karena akan memfilter data yang akan ditampilkan.
• Rows menampilkan data secara kebawah dan sesuai dengan urutan yang kita buat.
• Columns memiliki fungsi yang sama dengan Rows, akan tetapi data yang di tampilkan akan menyamping.
• Values adalah bagian yang menunjukkan data yang biasanya berupa angka seperti jumlah barang atau total harga.

•  Pada bagian bawah jendela PivotTabel terdapat pilihan Defer Layout Update, bila pilihan ini dicentang maka perubahan yang kita buat tidak akan langsung diaplikasikan ke Pivot Table dan kita harus mengklik tombol Update untuk mengaplikasikan perubahan yang anda buat.
  
  
  

Sebagai referensi saya akan memberikan file contoh yang saya gunakan di sini.
Selamat Mencoba.

Mencari dan menghitung gaji pegawai pada Microsoft Excel.

Pada kesempatan kali ini saya akan membagikan cara mencari man menghitung gaji pegawai. langsung saja sebagai berikut.

1. Biasanya untuk memanajemen gaji pegawai kita akan mendapatkan daftar gaji dan data pegawai seperti di bawah ini. Untuk memasukkan data dari daftar gaji kedalam data pegawai kita akan menggunakan formula VLOOKUP.

 Keterangan:

lookup_value	:	nilai yang akan dicari di kolom pertama tabel atau rentang.
table_array	:	tabel referensi yang ingin dicari datanya yang kolom pertamanya digunakan sebagai acuan untuk nilai lookup_value
col_index_num	:	nomor kolom pada table_array yang ingin di cari datanya.
range_lookup	:	pilihan untuk menentukan nilai yang persis atau yang mendekati.
		TRUE = untuk mencari nilai yang mendekati.
		FALSE = untuk mencari nilai yang sama persis

2. Untuk Tunjangan dan Transport kita hanya perlu mengganti col_index_num menjadi 3 untuk Tunjangan dan 4 untuk Transport.

3. Bila ada penambahan tunjangan dengan syarat tertentu dapat menggunakan formula IF.
Contoh: bila statusnya sudah menikah maka mendapat tunjangan sebesar 10% Gaji Pokok maka akan terlihat seperti ini.

 4. Untuk menjumlahkan seluruh penghasilan dapat menggunakan formula SUM.

5. Untuk membuat gaji bulanan menjadi satu tahun maka tinggal kalikan Total Gaji dengan 12.

6. Untuk PTKP (Penghasilan tidak kena pajak) isi sesuai ketentuan yang ada. Selanjutnya kurangkan Total Gaji Setahun dengan PTKP untuk mendapatkan Penghasilan Kena Pajak.

7. Untuk menghitung Pajak penghasilan dapat menggunakan formula IF sesuai dengan ketentuan perpajakan. untuk rumus di atas saya mengikuti ketentuan PPh Pasal 21. 8. Untuk mendapatkan jumlah pajak untuk satu bulan tinggal memabagi 12 pajak setahun. Selamat Mencoba.

Membuat NIP pada Microsoft Excel.

NIP merupakan Nomor Induk Pegawai Negeri Sipil. NIP terdiri atas 18 digit angka.
contoh : 198503302003121002

• 8 Digit Pertama adalah Tahun, Bulan & Tanggal Lahir, pada NIP Contoh diatas 19850330 berarti Tahun 1985, Bulan 03 (Maret), Tanggal 30;
• 6 Digit berikutnya adalah Tahun & Bulan Pengangkatan CPNS (Calon Pegawai Negeri Sipil), pada NIP Contoh diatas 200312 berarti diangkat sebagai CPNS pada tahun 2003, bulan 12 (desember);
• 1 Digit berikutnya adalah angka pengenal untuk menunjukkan Jenis kelamin (angka 1 untuk Laki-laki, angka 2 untuk perempuan), pada NIP Contoh diatas 1 berati Jenis kelamin pemilik NIP tersebut adalah laki-laki;
• 3 Digit terakhir adalah angka pengenal yang menunjukkan Nomor Urut CPNS/PNS, dari contoh NIP diatas 002, berarti NIP tersebut diberikan Nomor Urut 2.

Pada kesempatan kali ini saya akan memberikan cara untuk mebuat NIP dari data pegawai melalui Microsoft Excel. Berikut caranya.

1. Setelah memiliki data para pegawai, pertama kita harus mengganti format data tersebut menjadi angka. Berikut caranya.

a. Tanggal
Untuk tanggal kita dapat menggunakan formula Date&Time. Untuk tahun kita bisa menggunakan formula "=Year(serial_number)"

Untuk bulan kita akan menggunakan kombinasi formula IF, MONTH dan CONCATENATE karena kita memerlukan 2 digit angka untuk bulan.

dengan menggunakan rumus diatas setiap angka bulan yang di bawah 10 akan ditambah angka 0 di depannya.

Untuk hari kita akan menggunakan kombinasi yang sama seperti bulan, akan tetapi formula MONTH diganti dengan DAY.

Lakukan hal yang sama dengan tanggal pengangkatan, akan tetapi ambil bulan dan tahun saja tanpa hari.

b.Untuk jenis kelamin kita hanya perlu menggunakan formula IF.

  

c.Untuk nomor urut kita memerlukan format 3 angka jadi kita akan menggunakan kombinasi formula IF, LEN dan CONCATENATE.

 

dengan begitu setiap nomor urut yang terdiri atas satu angka akan ditambah "00" dan yang berisi dua angka akan ditambah "0" yang akan membuat format menjadi tiga angka. 

2. Setelah mendapatkan seluruh format angka kita tinggal menggabungkan semuanya menggunakan formula CONCATENATE.

Selamat Mencoba.

Formula sederhana pada Microsoft Excel.

Dalam kesempatan kali ini saya akan membagikan beberapa formula sederhana yang umum akan kita gunakan dalam pengolahan data pada Excel. Berikut formula tersebut.

1. SUM

Digunakan untuk mencari jumlah isi data pada range tertentu. Rumusnya adalah "=SUM(number1, number2, ...)" Anda juga dapat memblok range yang ingin di jumlah kemudian menekan Enter.

2. MIN

Digunakan untuk mencari nilai terendah dari suatu range. Rumusnya adalah "=MIN(number1, number2, ...)" Anda juga dapat memblok range yang ingin dicari nilai terendah.

3. MAX

Digunakan untuk mencari nilai tertinggi dari suatu range. Rumusnya adalah "=MAX(number1, number2, ...)" Anda juga dapat memblok range yang ingin dicari nilai tertinggi.

4. AVERAGE

Digunakan untuk mencari nilai rata-rata dari suatu range. Rumusnya adalah "=AVERAGE(number1, number2, ...)" Anda juga dapat memblok range yang ingin dicari nilai rata-rata.

5. COUNTA

Digunakan untuk menghitung jumlah cell yang tidak kosong. Rumusnya adalah "=COUNTA(value1, value2, ...)"  Anda juga dapat memblok range yang ingin dihitung jumlah cell yang tidak berisi.

6. COUNTIF

Digunakan untuk menghitung jumlah data dalam suatu range yang memiliki kriteria tertentu. Rumusnya adalah "=COUNTIF(range, criteria)" Kriteria dapat berupa angka, huruf atau kata. 

7. SUMIF

Digunakan untuk menjumlahkan data dalam range yang memenuhi kriteria tertentu. Rumusnya adalah "=SUMIF(range, criteria, [sum_range])"

Keterangan.

range = merupakan area dari kriteria yang akan kita uji

Criteria = merupakan variabel kriteria yang akan kita ambil sebagai ketentuan penjumlahan, bisa berupa angka, text atau cell

sum_range = merupakan area nilai angka yang hendak kita jumlahkan berdasarkan kriteria yang telah kita tentukan nilainya berupa angka

Penggunaan Cell Absolute ($) pada Microsoft Excel.

Cell absolut merupakan alamat cell yang keberadaannya dalam kondisi terkunci. Alamat cell dikunci dengan tujuan dan maksud agar alamat cell tidak mengalami perubahan saat dilakukan proses Auto Fill baik dalam penggunaan nomor otomatis, atau penerapan rumus. Proses Autofill ini biasanya akan menambahkan alamat cell ke cell berikutnya. Artinya cell tersebut harus terkunci agar tidak berubah dan istilah ini dinamakan cell Absolut.

A. Cara Penggunaan.

Cell Absolut sangat mudah sekali menggunakannya yaitu hanya dengan menambahkan symbol $ di depan nama kolom atau nomor barisnya atau dengan cara otomatis yaitu dengan menggunakan tombol formula F4.

B. Jenis Cell Absolute.

Ada 3 Jenis Cell Absolute yang dapat kita gunakan yaitu sebagai berikut.

1. $A1 merupakan alamat cell Absolut dimana symbol $ terletak di depan kolom, ini menandakan apabila saat menggunakan Auto Fill nama kolom tersebut akan terkunci namun nomor barisnya terus bertambah
2. A$1 merupakan alamat cell Absolut dimana symbol $ berada di tengah atau di depan nomor baris, maka apabila proses auto fill digunakan nomor baris tersebut akan terkunci namun nama kolomnya akan selalu tetap dan tidak berubah.
3.  $A$1 aturan yang ke-3 alamat cell Absolut dimana peletakan symbol berada pada keduanya yaitu didepan kolom dan baris ini menandakan apabila ketika penggunaan auto fill alamat kolom dan baris tersebut tidak akan berubah dan akan terkunci. 

C. Contoh
Perhatikan gambar di bawah ini saya akan mengganti harga dollar menjadi rupiah dengan kurs yang telah tersedia.

 

Jika di enter maka hasilnya akan muncul dengan benar.

Bagai mana bila saya auto fill? Hasil yang ditampilkan akan salah karena cell yang berisi harga kurs yang seharusnya tetap di cell C3 juga bergerak bersama dengan sistem auto fill.

Cara memperbaikinya adalah dengan membuat C3 sebagai cell absolut di dalam rumus. Kita hanya perlu menambahkan tanda absolute ($) atau memencet f4 satu kali untuk mengunci kolom dan baris yang berisi kurs mata uang dan mengulang proses auto fill.

Membuat rumus matematika dan cara menghitungnya.

A. Membuat Rumus.
Microsoft Excel memiliki perintah khusus untuk membuat beberapa simbol dan rumus matematika. Dengan menggunakan perintah tersebut kita dapat membuat berbagai kombinasi rumus dari simbol dalam matematika. Berikut caranya.

1.Buka tab Insert > Pilih Equation

2. Kemudian akan muncul simbol dan struktur rumus yang bisa kita pakai.

3. Terakhir kita tinggal membuat sesuai dengan kebutuhan.

B. Cara Menghitung.

Untuk melakukan perhitungan yang simpel dapat menggunakan beberapa lambang seperti berikut.

 

Proses perhitungan akan dilakukan sesuai dengan derajat urutan operator sebagai berikut:

Proses Pertama ^

Proses Kedua * atau /

Proses Ketiga + atau –

Rumus yang diapit dengan tanda kurung () akan diproses terlebih dahulu. Menulis Rumus selalu diawali dengan lambang sama dengan (=) .Setiap penulisan rumus diawali dengan tanda ’sama dengan’ (=) diakhiri dengan menekan Enter

Misal :

Penjumlahan    :          =A1+B1 atau +A1+B1.

Pengurangan    :          =A1-B1 atau +A1-B1.

Perkalian      :          =A1*B1 atau +A1*B1.

Pembagian      :          =A1/B1 atau +A1/B1.

Pangkat 2      :          = A1^2 atau A1^2.

Sedangkan untuk penghitungan yang lebih sulit dapat ditambahkan dengan formula Math & Trigonometry. Caranya adalah Buka Tab Formulas > Math & Trig, kemudian akan muncul menu berisi formula yang dapat kita pakai.

 

 

Gunakan sesuai dengan fungsi dan kebutuhannya.