Contoh Penggunaan Rumus Fungsi Hlookup, Vlookup, dan SUM Perkalian dan Penjumlahan Pada Microsoft Excel.

Kali ini saya akan mengulas tentang cara penggunaan rumus hlookup,vlookup dan sum perkalian dan Penjumlahan pada Ms. Excel, bagi nyang belum mengetahui cara penggunaan rumus tersebut bisa di pelajari di ulasan berikut ini :

Telah di sediakan sebuah tabel Data tentang gaji perwira TNI AU SATRAD 214 seperti  gambardi bawah ini :

Diketahui pangkat dan nama anggota perwira TNI AU. disini akan mengisi kolom pada Gaji pokok, Tunjangan, Transportasi, Total Gaji, Pajak dan Gaji Bersih. telah di sediakan keterangan cara mengisi pada tabel Gaji dan Potongan pajak di bawah Tabel Daftar Gaji. untuk yang pertama kita akan mengulas cara penggunaan rumus vlookup untuk mengisi kolom gaji pokoki tunjangan transportasi. perlu di ketahui bahwa penggunaan rumus vlookup pada Ms. Excel atau pun Hlookup adalah untuk memindahkan data yang sudah ada ke data yang belum di iisi bedanya adalah jika vlookup di gunakanb pada tabel yang vertikal dan Hlookup pada tabel yang bentunya horisontal.

1. pada tabel gaji yang telah di sediakan yaitu berbentuk Vertikal maka untuk memindahkannya ke trabel daftar gaji kita menggunakan rumus vlookup seperti gambar berikut :

cara menggunkan rumus vlookup ini yaitu masukan =Vlookup(klik kolom pangkat yang pertama titik koma block semua data di tabel gaji yang tersedia di bawahdari ujung atas  kanan sampai ujung bawah kiri lalu block hasil block tabel tersebut lalu klik F4 gunanya untuk mengunci data tersebut lalu lepas block lalu titik koma 2 angka dua ini karena pada tabel gaji pokok terdapat di baris dua lalu kurung tutup) atau pada data tersebut =Vlookup(B6;$B$15:$E$17;2) lalu tekan Enter maka di kolom gaji pokok akan terpindah ke kolom gaji pokok yang belum teriisi :

untuk mengisi dibawahnya tarik kolom gaji pokok pertama lalu tarik kebawah maka akan teriisi dan terpindah semua secara otomatis. 

Selanjutnya mengiisi dan memindahkan nya di kolom Tunjangan. caranya sama, seperti gambar di atas  =Vlookup(klik kolom pangkat yang pertama titik koma block semua data di tabel gaji yang tersedia di bawah dari ujung atas  kanan sampai ujung bawah kiri lalu block hasil block tabel  tersebut lalu klik F4 gunanya untuk mengunci data tersebut lalu lepas block lalu titik koma 3 angka 3 ini karena pada tabel gaji pokok terdapat di baris  ke 3 lalu kurung tutup) atau =Vlookup(B6;$B$15:$E$17;3) lalu tekan Enter. maka data akan terpindah :

untuk mengisi di bawahnya klik kolom tunjangan pertama lalu tarik ke bawah.

Selanjutnya mengisi kolom Transportasi : 

sama caranya yaitu =Vlookup(klik kolom pangkat yang pertama titik koma block semua data di tabel gaji yang tersedia di bawah dari ujung atas  kanan sampai ujung bawah kiri lalu block hasil block tabel tersebut tersebut lalu klik F4 gunanya untuk mengunci data tersebut lalu lepas block lalu titik koma 4 angka 4 ini karena pada tabel gaji pokok terdapat di baris  ke 4 lalu kurung tutup) atau =Vlookup(B6;$B$15:$E$17;4) lalu tekan Enter maka data akan terpindah :

klik pada tabel tunjangan pertama lalu tarik kebawah untuk mengisi kolom di bawahnya.

2. Lalu mengisi kolom total Gaji yaitu dengan cara memasukan rumus SUM :

cara memasukan rumus SUM yaitu =SUM(block semua pada gaji yang akan di jumlah dari gaji pokok sampai transportasi lalu kurung tutup) atau pada tabel =SUM(D6;F6) lalu tekan Enter. 

maka data akan terkalkulasi, tarik kebawah untuk mengisi data di bawahnya seperti biasa :

3. Selanjutnya mengisi kolom Pajak pada tabel. karena data yang sudah tersedia isinya berbentuk horisontal maka kita gunakan rumus Hlookup :

cara menggunakannya sama persis seperti Vlookup sebelumnya yaitu =Hlookup(klik kolom pangkat yang pertama titik koma block semua data di tabel potongan pajak yang tersedia di bawah dari ujung atas  kanan sampai ujung bawah kiri lalu block hasil block tabel tersebut lalu klik F4 gunanya untuk mengunci data tersebut lalu lepas block lalu titik koma 2 angka 2 ini karena pada tabel gaji pokok terdapat di kolom  ke 2 lalu kurung tutup) maka data akan terpindah :

klik pada kolom pertama pajak lalu tarik kebawah untuk mengisi di bawahnya.

4. Selanjutnya yaitu Gaji Bersih, cara mengisi kolom gaji bersih yaitu dengan mengalikan totalo gaji dengan pajak. maka dari itu kita menggunakan rumus SUM perkalian : 

cara memasukan rumusnya yaitu =SUM(block pada kolom pertama total hasil gaji lalu masukan tanda bintang * yang dalam Ms. Excel artinya tanda kali lalu klik pada hasil kolom pajak pertama lalu kurung tutup) atau pada tabel =SUM(G6*H6) lalu tekan enter. maka hasil gaji bersih akan ter kalkulasi : 

untuk mengisi data di bawahnya klik pada kolom hasil gaji bersih pertama lalu tarik kebawah, maka semua akan terisi dan terkalkulasi secara otomatis.

5. Terakhir yaitu kolom Total Gaji Bersih, yaitu dengan menggunakan rumus SUM penjumlahan : 

cara memasukan rumus SUM penjumlahan yaitu =SUM(block semua hasil gaji bersih dari atas sampai bawah lalu kurung tutup) atau pada tabel=SUM(I6:I11) lalu tekan enter . maka data di kolom total akan terakumulasi : 

Sekian ulasan saya tentang contoh penggunaan rumus fungsi Hlookup, Vlookup dan SUM perkalian, semoga bisa bermanfaat untuk anda :)

Contoh Penggunaan Rumus Fungsi If dengan And, Sum, Min, Max dan Average Pada Microsoft Excel.

Kali ini saya akan mengulas tentang pembuatan fungsi If dengan And, Sum, Min, Max Dan Average pada microsoft excel. bagi yang ingin mempelajarinya bisa di lihat ulasan di bawah ini :

Contoh Data :

Telah di sediakan data seperti gambar di bawah ini :

Di sediakan nilai per grade kategori musik drum di IMC, dengan keterangan nilai rata-rata >=  90 baik sekali, >=80 baik dan ,80 cukup.

Disini telah di sajikan jumlah nilai ,rata-rata nilai, keterangan, nilai tertinggi dan terendah yang belum diisi, untuk itu kita akan mengulas yang pertama dari cara mengisi di kolom jumlah.
1. Untuk mengisi kolom jumlah kita harus menggunakan rumus SUM seperti gambar di bawah ini :

rumusnya adalah =SUM(block semua nilai yang akan di jumlah di baris pertama dari kolom grade 1 sampai 4 lalu di akhiri kurung tutup ) atau =SUM(C9:F9) lalu tekan Enter dengan begitu akan tampil hasil nilai jumlahnya, seperti gambar berikut :

untuk mengisi semua kolom di bawahnya kita bisa menggunakan rumus yang sama seperti pertama dengan memasukan nilai yang akan di jumlah selanjutnya, atau bisa di klik hasil jumlah pertama, lalu tarik kebawah di tanda seperti titik di pojok bawah samping kiri maka akan terjumlah semua datanya seperti sebelumnya.

2. Selanjutnya untuk mengisi kolom rata-rata. Yaitu dengan cara memasukan fungsi rumus AVERAGE dengan cara seperti gambar di bawah ini : 

sama seperti rumus SUM yang pertama masukan rumus =AVERAGE (Block nilai yang akan di rata-rata dari baris grade 1 sampai 4 lalu di akhiri kurung tutup ) atau =AVERAGE(C9:F9) lalu tekan Enter
maka akan terkalkulasi nilai rata-ratanya seperti gambar di bawah ini :

untuk mencari nlai rata-rata di bawahnya tinggal klik nilai rata-rata yang pertama lalu tarik kebawah maka semua nilai rata-ratanya akan terhitung secara otomatis seperti cara SUM sebelumnya.

3. selanjutnya mengisi kolom Keterangan. Cara mengisi kolom ini yaitu dengan cara rumus fungsi IF dengan AND seperti gambar di bawah ini : 

masukan sesuai keterangan yang sudah di sediakan yaitu jika >= 90 baik sekali, >=80 Baik, lalu <80 Cukup maka rumusnya =IF(klik pada kolom nilai rata-rata pertama lalu masukan >=90 titik koma "Baik Sekali" titik koma IF(klik kolom nilai rata rata pertama lagi >=80 titik koma "Baik" titik koma "Cukup" )) atau =IF(H9>=90;"Baik Sekali";IF(H9>=80;"Baik";"Cukup))  lalu tekan Enter. IF ada dua karena keterangan nilai ada dua 90 dan 80 atau di sebut "IF dengan AND" jika hanya satu misal >=90 maka IF hanya satu. dengan begitu  maka keterangan akan teriisi sesuai ketentuan :

untuk mengisi kolom di bawahnya seperti biasa klik kolom pertama hasil keterangan lalu tarik ke bawah, maka akan teriisi semuanya.

4. Lalu mengisi kolom Nilai Tertinggi yaitu dengan menggunakan rumus fungsi MAX :

cara memasukannya =MAX(Block semua hasil nilai rata-rata) atau =MAX(H9:H16) lalu tekan Enter, maka akan terkalkulasi nilai tertingginya.



 



5. Terakhir di contoh data tabel di bawah ini adalah mengisi kolom nilai terendah yaitu dengan menggunakan rumus fungsi MIN :

sama seperti rumus MAX cara memasukannya =MIN( block semua nilai rata rata dari atas sampai bawah ) atau =MIN(H9:H16) lalu klik Enter maka akan terkalkulasi seperti gambar di bawah ini :

Dengan begitu Semua nya telah teriisi dan terkalkulasi hasilnya.

Demikian ulasan saya tentang Rumus fungsi IF dengan AND, SUM, MAX, dan MIN. Semoga bisa Bermanfaat :)

validasi data microsoft excel 2010

validasi data pada microsoft excel 2010

Membuat daftar entri yang diizinkan dalam sel

Anda dapat membuat daftar entri yang Anda akan menerima untuk sel pada lembar kerja. Anda kemudian dapat membatasi sel untuk menerima hanya entri diambil dari Daftar dengan menggunakan fitur validasi data. 

Untuk membuat drop-down Daftar dan membatasi nilai dalam sel untuk entri ini, ikuti langkah berikut: 

1. Pilih sel A1.
2. Pada Data menu, klik Validasi.
3. Pada Tataan tab, klik Daftar dalam Memungkinkan daftar drop-down.
4. Secara default, Mengabaikan kosong dan Dropdown di sel kotak centang dipilih. Jangan mengubah mereka.
5. Dalam Sumber kotak, jenis a, b, c. KlikOke.
   
   CATATAN: 
   Anda juga dapat memasukkan kisaran bernama atau sel referensi jika itu berisi daftar nilai-nilai. Keduanya harus didahului dengan tanda sama dengan.
   Ada batasan 255 karakter untuk dialog ini.
6. Sel A1 sekarang memiliki daftar drop-down di sebelah itu dan Anda dapat menggunakan daftar ini untuk memilih nilai untuk masuk ke dalam sel.
7. Klik daftar drop-down dan kemudian klik salah satu item berisi.

Nilai ini akan dimasukkan dalam sel. 

CATATAN: Anda dapat memasukkan "a", "b", atau "c", (tanpa kutipan menandai) dalam sel; Anda tidak harus memilih dari daftar. Jika Anda mencoba untuk memasukkan apa pun yang lain nilai-nilai ini, pesan stop muncul dan Anda tidak mampu menyimpan nilai dalam sel ini. Pilihan Anda hanya Coba lagi atau Batalkan.

Buat pesan Prompt menjelaskan jenis Data yang diizinkan dalam sel

Anda dapat membuat pesan yang menjelaskan jenis data yang dapat dimasukkan dalam sel. Ketika Anda memilih sel, muncul dekat sel. Anda dapat memindahkan pesan ini, jika perlu, dan tetap sampai Anda pindah ke lain sel atau tekan ESC. Jika kantor asisten terlihat, pesan muncul dalam sebuah balon di atas kantor asisten. 

Melanjutkan dengan contoh sebelumnya, ikuti langkah berikut: 

1. Pilih sel A1.
2. Pada Data menu, klik Validasi kemudian klik Pesan masukan tab.
   
   CATATAN: Pastikan Menunjukkan masukan pesan ketika sel dipilih kotak centang dipilih.
3. Dalam Judul kotak, jenis Daftar untuk sel A1.
   
   Ini adalah judul untuk kotak pesan yang muncul. Ini adalah dalam huruf tebal.
4. Dalam Pesan masukan kotak, jenisSilakan pilih salah satu b, atau c dari drop-down Daftar.
   
   Ini adalah badan pesan yang muncul.
5. Klik Oke.
   
   Muncul karena sel A1 dipilih.
6. Pilih sel A10.
   
   Daftar drop-down di sebelah sel A1 dan pesan untuk sel A1 kedua menghilang.
7. Pilih sel A1.
   
   Daftar drop-down dan prompt pesan muncul di sebelah sel A1. 
   
   CATATAN: Jika kantor asisten terlihat, muncul pesan cepat balon di atas kantor asisten.
8. Memindahkan pesan ke lokasi lain di lembar kerja.
   
   Ketika Anda memilih sel lain dan kemudian pilih sel A1 lagi, pesan muncul di lokasi baru. 
   
   Catatan: Tidak ada cara untuk mengubah format untuk pesan ini.

Buat pesan yang muncul ketika Data yang salah masuk

Gaya pesan kesalahan Microsoft Excel menampilkan ketika Anda tipe data tidak benar menentukan apakah larangan diberlakukan. Kamu bisa menampilkan pesan yang menghentikan Anda dari memasuki nilai data kecuali mereka ditentukan. Anda juga dapat menampilkan jelas dan pesan peringatan yang memberitahu Anda Masukkan keluar kisaran data, atau Anda dapat menetapkan batas pada data tetapi tidak menampilkan apapun pesan. 

Melanjutkan dengan contoh sebelumnya, ikuti langkah berikut: 

1. Pilih sel A1.
2. Pada Data menu, klik Validasi kemudian klik Kesalahan Alert tab. 
   
   CATATAN: Pastikan Menunjukkan kesalahan waspada setelah data yang tidak sah masuk kotak centang dipilih. 
   

   Membuat pesan peringatan berhenti

   Jika Anda membuat pesan berhenti Alert dan masukkan data yang tidak sah di sel, satu-satunya pilihan adalah Coba lagiatau Batalkan; Anda tidak diperbolehkan untuk memasukkan data yang tidak sah dalam sel. 
   
   
   1. Dalam Gaya Daftar, klik Stop.
   2. Dalam Judul kotak, jenis Peringatan untuk sel A1. Ini adalah judul untuk kotak pesan yang muncul.
   3. Dalam Pesan Galat mengedit kotak, masukkan Nilai-nilai hanya diizinkan untuk sel A1 adalah, b, atau c. Ini adalah badan pesan yang muncul dan terbatas 225 karakter.
   4. Klik Oke.
3. Memasukkan t, dalam sel A1.
   
   Berhenti Alert Anda dibuat muncul dan pilihan Anda hanyaCoba lagi atau Batalkan.
4. Klik Batalkan pada Berhenti Alert kotak pesan.
5. Pilih sel A1.
6. Pada Data menu, klik Validasi kemudian klik Kesalahan Alert tab. 
   
   CATATAN: Pastikan Menunjukkan kesalahan waspada setelah data yang tidak sah masuk kotak centang dipilih.
   

   Membuat pesan peringatan peringatan

   Jika Anda membuat pesan peringatan dan masukkan data yang tidak sah di sel, pilihan Anda sedikit lebih fleksibel daripada untuk pesan berhenti Alert. A Peringatan pesan peringatan memiliki tiga pilihan: Ya, untuk memungkinkan masuknya data yang tidak sah; Tidak, untuk memberi Anda kesempatan untuk memasukkan data yang benar; danBatalkan, untuk menghapus entri yang tidak valid.
   
   
   1. Dalam Gaya Daftar, klik Warning.
   2. Dalam Judul kotak, jenis Peringatan untuk sel A1. Ini adalah judul untuk kotak pesan yang muncul.
   3. Dalam Pesan kesalahan kotak, jenis Nilai-nilai hanya diizinkan untuk sel A1, b, atau c. Ini adalah badan pesan yang muncul dan terbatas 225 karakter.
   4. Klik Oke.
7. Memasukkan j dalam sel A1.
   
   Muncul pesan peringatan yang Anda buat meminta jika Anda ingin Lanjutkan.
8. Klik Ya. 
   
   Nilai tidak sah "j" yang dimasukkan dalam sel A1.
9. Pilih sel A1.
10. Pada Data menu, klik Validasi kemudian klik Kesalahan Alert tab. 
    
    CATATAN: Pastikan Menunjukkan kesalahan waspada setelah data yang tidak sah masuk kotak centang dipilih.
    

    Membuat pesan peringatan informasi

    Jika Anda membuat sebuah informasi pesan dan masukkan data yang tidak sah di sel, pilihan yang paling fleksibel. Ketika pesan peringatan informasi muncul, Anda dapat klik Oke untuk menerima nilai tidak sah atau Anda dapat klikBatalkan untuk menolak itu.
    
    
    1. Dalam Gaya Daftar, klik Informasi.
    2. Dalam Judul kotak, jenis Peringatan untuk sel A1. Ini adalah judul untuk kotak pesan yang muncul.
    3. Dalam Pesan kesalahan kotak, jenis Nilai hanya diizinkan untuk sel A1 yang, b, atau c. Ini adalah badan pesan yang muncul dan terbatas 225 karakter.
    4. Klik Oke.
11. Memasukkan p dalam sel A1.
    
    Pesan peringatan informasi yang Anda buat muncul meminta jika Anda ingin untuk menerima atau menolak value yang kamu masukkan.
12. Klik Oke. 
    
    Nilai tidak sah "p" yang dimasukkan dalam sel A1.

Memeriksa entri yang salah menggunakan Toolbar audit

Setelah data Anda masuk, Anda dapat mencari entri yang di luar batas-batas yang Anda tetapkan. Ketika Anda mengklikLingkaran Data yang tidak sah pada Audit toolbar, lingkaran muncul di sekitar sel-sel yang mengandung salah entri. Jika Anda mengoreksi entri tidak sah, lingkaran menghilang. 

Melanjutkan dengan contoh sebelumnya, ikuti langkah berikut: 

1. Pada Alat menu, klik Menyesuaikan.
2. Dalam Menyesuaikan kotak dialog, klik Toolbar tab.
3. Dalam Toolbar Daftar, klik untuk memilih Audit Periksa kotak (jika belum dipilih) dan kemudian klik Tutup.
4. Pada Audit toolbar, klik Lingkaran Data yang tidak sah kontrol. Sel A1 berputar.
5. Pilih sel A1.
6. Klik tanda panah drop-down di sebelah sel A1 dan kemudian klik salah satu entri dalam daftar. Lingkaran menghilang.

Mengatur rentang numerik nilai-nilai yang dapat dimasukkan dalam sel

Anda dapat menempatkan batas pada data yang dapat dimasukkan dalam sel, Anda dapat mengatur minimum dan ukuran maksimum atau periksa efek entri mungkin memiliki pada sel lain. 

1. Pilih sel A5.
2. Pada Data menu, klik Validasi dan klik Tataan tab.
3. Dalam Memungkinkan Daftar, klik Jumlah seluruh.
4. Dalam Data Daftar, klik antara.
5. Dalam Minimal kotak, masukkan 1.
6. Dalam Maksimum kotak, masukkan 10. 
   
   CATATAN: Anda dapat menggunakan sel referensi untuk langkah 5 dan 6 untuk menentukan sel yang berisi nilai-nilai minimum dan maksimum.
7. Klik Oke.
8. Masukkan nilai 3 dalam sel A5. Nilai yang dimasukkan tanpa kesalahan.
9. Masukkan nilai 33 dalam sel A5. 
   
   Karena data pengaturan validasi yang Anda buat untuk sel A1 (informasi Alert) tidak berlaku untuk orang-orang untuk sel A5, Anda menerima pesan Stop Alert (yang default nilai) dan pilihan Anda hanya untuk mengklik Coba lagi atauBatalkan.
10. Klik Batalkan. Nilai 3 muncul dalam sel.

Menentukan apakah entri berlaku berdasarkan perhitungan dalam sel lain

Di kotak dialog validasi data, Anda dapat menggunakan rumus, ekspresi atau referensi untuk perhitungan dalam sel lain untuk menentukan jika Catatan Anda membuat ini berlaku. 

1. Masukkan nilai 1 dalam sel B10.
2. Pilih sel A10.
3. Pada Data menu, klik Validasi dan klik Tataan tab.
4. Dalam Memungkinkan Daftar, klik Custom.
5. Dalam Rumus kotak Masukkan rumus berikut: 
   
   =IF(A10>B10,TRUE,FALSE) 
   
   CATATAN: Formula yang Anda masukkan harus dimulai dengan tanda sama dengan dan harus mengevaluasi untuk true atau false. Hal ini tidak terbatas pada fungsi jika.
6. Klik Oke.
7. Masukkan nilai-1 dalam sel A10. 
   
   Anda menerima Berhenti pesan peringatan yang menyatakan nilai tidak sah.
8. Klik Coba lagi.
9. Masukkan nilai 3 dalam sel A10. 
   
   Anda tidak menerima pesan galat karena nilai ini lebih besar dari nilai Anda masuk dalam sel B10.

Validasi Data Di Microsoft Excel

Jika anda adalah pengguna Microsoft Excel, tentu salah satu tugas kita sebagai pembuat project-nya tentu kita akan membuat project tersebut berisi dengan data-data yang akurat. Tetapi sayangnya secara default, Excel tidak menjaga keakuratan data anda tersebut. Dan hal itu akan berpengaruh pada hasil analisa laporan anda karena banyak data input yang tidak sesuai yang anda harapkan. Oya, saya mencoba tip ini di Microsoft Excel 2007 tetapi anda bisa menggunakan cara yang sama pada versi-versi sebelumnya.

Tetapi jangan kuatir, Excel juga telah menyediakan cara untuk anda. Fitur tersebut bernama Data Validation. Untuk penggunaannya sebagai berikut:

1. Setelah Microsoft Excel anda terbuka, ketikan daftar isian yang anda inginkan.
   
2. Arahkan ke cell dimana anda ingin isian dilakukan.
3. Klik menu Data -> Data Validation.
4. Di tab Settings, beri combo Allow: nilai List,  klik isian Source lalu blok data yang sudah anda buat di langkah 1.
   
5. Klik tab Error Alert. Pastikan radio check Show error after invalid data is entered dalam keadaan tercentang. Untuk Style pilih Stop lalu tulis Titlekotak peringatan dan Error Message: dengan isi kesalahan. Lalu klik tombolOK.
   
6. Nah, sekarang jika kita akan memasukan nilai di cell tersebut, akan muncul tombol anak panah kebawah yang jika di klik akan muncul nilai-nilai yang sudah di tentukan.
   
7. Dan jika anda memaksa mengisi dengan nilai di luar data tersebut, kotak peringatan yang telah anda konfigurasi tersebut akan muncul.
   

Dengan demikian data yang dimasukan bisa valid dan sesuai dengan yang anda harapkan. Oya, untuk jelasnya anda juga dapat mengambil contoh file untuk artikel ini.

sumber : http://support.microsoft.com/kb/211485/id-id

sumber : http://www.edisusanto.com/validasi-data-di-microsoft-ex