1. Berikan contoh aplikasi penggunaan fungsi HLOOKUP dan VLOOKUP.

Contoh Penggunaan HLOOKUP dan VLOOKUP pada Ms. Excel

1. Buka program aplikasi openoffice calc2. Buat keterangan tabel seperti di bawah ini

3. Untuk Golongan, berikan formula seperti di bawah ini :

=LEFT(C2;1)

Logika :

jika formula ini di jalankan maka cell pada D2 akan menampilkan karakter pertama dari isi cell C2.

4. Untuk Tahun, berikan formula seperti di bawah ini :

=MID(C2;4;2)

Logika :

Jika formula ini di jalankan maka pada cell E2 akan menampilkan 2 karakter dimulai dari karakter ke-4 dari isi cell C2

5. Untuk Gaji Pokok, berikan formula dan aturan HLOOKUP seperti di bawah ini :

Buat data HLOOKUP dengan cara memblok cell A9-F10

Setelah itu masuk ke menubar insert lalu pilih Names, setelah itu klik Define. Jika sudah masuk, buat nama untuk hlookupnya. Misalnya Gapok lalu klik OK.

Setelah dibuat, masukan formula seperti di bawah ini :

=HLOOKUP(D2;Gapok;2)

Logika :

Jika formula ini di jalankan maka komputer akan mencocokkan data dari cell D2 dengan data define names yang telah dibuat. Apabila ada yang cocok nilainya, maka pada cell E2 akan menampilkan nilainya.

6. Untuk Tunjangan, berikan formula dan aturan VLOOKUP seperti di bawah ini :

Buat data VLOOKUP dengan cara memblok cell A15-E20

Setelah itu masuk ke menubar insert lalu pilih Names, setelah itu klik Define. Jika sudah masuk, buat nama untuk vlookupnya. Misalnya Tunjangan lalu klik OK.

Setelah dibuat, masukan formula seperti di bawah ini :

=IF(G2=”Menikah”;VLOOKUP(D2;Tunjangan;2);VLOOKUP(D2;Tunjangan;3))

Logika :

Jika formula ini dijalankan maka komputer akan menjalankan perintah kondisi dimana ;

-          jika nilai dari cell G2 adalah Menikah maka komputer akan mencocokan data tersebut dengan data names tunjangan kolom ke 2 yang telah dibuat sebelumnya. Setelah mencocokan data, maka pada cell H2 akan ditampilkan nilainya.

-          Selain dari Menikah (Tidak Menikah) maka komputer akan mencocokan data tersebut dengan data names tunjangan kolom ke 3 yang telah dibuat sebelumnya. Setelah mencocokan data, maka pada cell H2 akan ditampilkan nilainya.

7. Untuk Jumlah Gaji, masukan formula seperti di bawah ini :

=SUM(F2:H2)

Logika :

Nilai dari cell J2 adalah penjumlahan dari nilai pada cell F2 dengan cell H2

8. Untuk Potongan, masukan formula seperti di bawah ini :

=(J2*15%)

Logika :

Nilai dari cell K2 adalah nilai dari cell J2 x dengan 15 %

9. Untuk Gaji Total, masukan formula seperti di bawah ini :

=J2-K2

Logika :

Nilai dari cell L2 adalah nilai dari cell J2 dikurang dengan cell K2

Fungsi Pembacaan Tabel pada Excel ( Hlookup dan Vlookup)

Excel menyediakan fungsi if untuk melakukan pengujian atau biasa juga disebut dengan test

kondisi,fungsi if biasanya digunakan untuk melakukan pengujian dimana ketentuannya dibuat dalam bentuk kalimat. Bentuk dari fungsi if jika hanya dua kondisi adalah =if(test_kondisi,nilai_bernar,nilai_salah), tetapi jika lebih dari dua kondisi bentuknya bisa digunakan =if(text_kondisi,nilai_benar,if(test_kondisi,nilai_benar,nilai_salah)), dengan kata lain jika kondisi banyak maka harus diatur nilai salahnya menjadi test kondisi lagi.Sedangkan jika ketentuan sudah dalam bentuk tabel atau sudah disusun berdasarkan kolom atau baris maka excel menyedian fungsi pembacaan tabel, memang tidak mungkin melakukan atau mengerjakannya menggunakan fungsi If tetapi sudah merupakan suatu pekerjaan yang sangat sia-sia karena membutuhkan banyak waktu. contohnya dalam pengerjaan latihan dibawah ini,

untuk mencari nama gerbong, harga tiket, tujuan/jurusan,jam pergi, jenis tiket kalau digunakan menggunakan if maka rumus if yang dipakai akan panjang karena ada 15 kondisi. untuk menyingkatnya digunakanlah fungsi pembacaan tabel dimana rumusnya sebagai berikut :=vlookup(Nilai_patokan_pencarian,sumber_tabel,nomor_kolom_yang_dicari,Match)=Hlookup(Nilai_patokan_pencarian,sumber_tabel,nomor_baris_yang_dicari,Match)dimana match bernilai true/1 atau false/0, jka tidak diberikan maka standarnya adalah 1Untuk contoh Kasus diatas digunakan rumusmencari nama Gerbong : =VLOOKUP(B9,$B$25:$E$39,2,0)mencari harga tiket : =VLOOKUP(B9,$B$25:$E$39,3,0)mencari tujuan/jurusan : =VLOOKUP(B9,$B$25:$E$39,4,0)jika anda perhatikan di rumus tersebut terdapat $B$25:$E$39 yang maksudnya sama dengan B25:E39 hanya saja jika kamu ketikkan rumus tersebut pada kotak c9 maka rumus tersebut jika dicopykan ke baris dibawahnya maka tidak akan manjur sehingga perlu diberi tanda dollar dimana tanda dollar tersebut menandakan absolut atau tidak akan berubah.

2. Berikan contoh aplikasi penggunaan table pivot.

Tabel Pivot dan contohnya

Tabel pivot adalah cara membuat laporan atau meringkas informasi tabel atau daftar data dalam excel sehingga diperoleh informasi yang ringkas dengan analisis informasi yang diperoleh dari kombinasi kolom data dalam tabel.

Dengan Pivot Table Excel kita dapat menganalisis, meringkas dan memanipulasi data sehingga kita memperoleh informasi untuk mengambil keputusan.

Contoh Excel I – Penggunaan Pivot Table

Pivot table digunakan untuk melakukan rekapan perhitungan berdasar data yang ada.

Contoh Soal

Silahkan anda mengetikkan data berikut

Kalau anda ditanya berapa pengeluaran jumlah pengeluaran tiap bulan bagaimana anda menyelesaikannya? Contoh lain berapa pengeluaran per jenis aktivitas selama satu tahun, berapa

beban kerja penanggung jawab per bulan dan lain lain. Nah disinilah fungsi pivot table akan digunakan. Apalagi kalau jumlah datanya sangat banyak, ribuan data, maka fungsi pivot tabel akan sangat berguna bagi anda.

Penggunaan Pivot Table

Untuk menggunakan pivot table caranya silahkan klik menu insert pivot table seperti pada gambar berikut

Setelah anda mengklik pivot tabel akan muncul tampilan

Klik Select a table or range, klik dibawah kanannya, kemudian sorot data yang ingin dibuat pivot table seperti pada gambar berikut

Kemudian pilih lokasi hasil pivot table di existing worksheet seperti pada gambar berikut

Lalu klik OK. Kalau tahap anda benar, maka akan muncul menu pivot tabel seperti pada gambar berikut

Untuk membuat pengeluaran per bulan, anda cukup memasukkan bulan di row label dan nilai di value,

Perhatikan, pivot tabel sudah menghitungkan untuk anda jumlah pengeluaran tiap bulan

Kalau kombinasi misalnya pengeluaran per bulan per jenis aktivitas, maka anda tinggal memasukkan jenis aktivitas di column labels seperti pada gambar berikut

Untuk merapikan hasil, gunakan format cell. Berikut contoh hasil perhitungan yang sudah dirapikan

Menggunakan Tabel Pivot untuk Meringkas Data

Misalnya anda memiliki data penjualan yang isinya 50.000 baris seperti terlihat pada Gambar 1

Dengan data itu anda ingin membuat laporan ringkas yang menunjukkan penjualan per wilayah dan produk (region and product).

Gambar 1. Ringkas tabel data berikut.

Caranya: Untukmeringkas data di atas, anda dapat menggunakan pivot table. Pivot tables sngat cocok untuk analisis data seperti di atas.

Peringatan: Pivot tables di Excel 2007 sangat berubah dari versi

Excel 97-2003. Untuk membuat ringkasan pendapatan berdasarkan wilayah dan produk membutuhkan klik mouse sebanyak 4 kali dan satu kali tahan tarik mouse (drag):

Pastikan bahwa data yang ada dalam daftar memiliki judul (pada baris paling atas) yang berbeda satu dengan lainnya (unik).

Pilih salah satu sel dalam database (data anda). Pilih Menu Insert – Pivot Table.

IntelliSense Excel (sensor cerdas Excel) akan menduga range data anda. Dan biasanya benar seperti pada Gambar 2. Dan Klik OK.

Dengan menekan tombok OK, anda akan melihat tabel pivot yang masih kosong dan dua toolbar tabel pivot baru, dan sebuah dialog daftar PivotTable baru. Dialog Excel versi 2007 memperlihatkan daftar filed pada bagian atas dan empat tempat meletakkan (Drop Zone) field pada bagian bawah dialog, seperti terlihat pada Gambar

Sedangkan pada Excel versi sebelumnya, maka anda harus menarik field dari Dialog Field

List langsung ke pivot table. Proses ini sangat menyulitkan bagi orang yang masih baru mengenal pivot tables. Dalam Excel 2007, anda menarik field dari dialog field list bagian atas ke zona yang yang cocok pada bagian bawah dialog Field List. Biasanya dengan hanya meng-klik field yang ada di dialog Field List akan menempatkan field tersebut pada zona yang tepat. Untuk kasus yang kita miliki ini, maka kita mencoba meletakkan produk merupakan daftar yang ke bawah (judul baris) sedangkan wilayah merupakan judul mendatar (judul kolom).

Gambar 3. Dialog PivotTable Field List di bagian atas dan Drop Zone di Bagian Bawah

Klik Check box Produk di Dialog Field List bagian atas. Excel akan secara otomatis memasukkan ke dalam Row Labels (Label baris) pada drop zone. Pivot table akan menunjukkan daftar produk satu per satu (tidak ada duplikasi) pada Kolom A (lihat Gambar 4).

Gambar 4. Klik sebuah field text, dan Excel memasukkan field itu ke area Baris.

Klik check box Pendapatan yang ada di dialog Field List bagian atas. Dan karena field tersebut adalah numeric maka Excel akan menambahkan pada bagian Values (nilai) dari pivot table.

Jika anda meng-klik check box Wilayah, maka Excel akan meletakkan field tersebut di area row pada pivot table. Karena anda ingin wilayah menjadi judul kolom maka, klik mouse, tahan dan

tariklah field Wilayah dari dialog Field List dan letakkan di drop zone Column Labels yang terletak di bagian bawah dialog Field List.

Hasilnya adalah ringkasan data penjualan berdasarkan produk dan wilayah, seperti terlihat dalam Gambar 5.

Gambar 5. Pivot table membuat anda mudah untuk meringkas laporan.

Tingkat Lanjut: Pivot table memberikan banyak opsi (pilihan) yang sangat berguna.

Untuk Pemakai Versi Sebelumnya: Jika anda menyukai pivot table pro pada Excel versi sebelumnya, maka anda akan dengan cepat menggunakan pivot table

Excel 2007. Drop zones telah diubah namanya. Drop Zone Row Area sekarang bernama Row Labels. Sedangkan drop zone Column Area sekarang menjadi Column Labels. Drop Page Field menjadi Report Filter. Dan drop zone Data Area menjadi ? Values (walaupun saya menyebutnya drop zone Values, dengan mengabaikan symbol ?).

Agar Lebih Mudah: Padaawal pembuatan sebuah Pivot Table, maka dialog PivotTable Field List akan berada di layar sebelah kanan. Anda dapat memindahkan ke tengah dengan cara klik pada Judul Dialog dan tarik ke worksheet agar terlihat mengambang di atas worksheet dan mempermudah anda dalam mengatur data. Dalam membuat artikel ini saya menggeser ke tengah agar tampak dalam gambar yang disajikan sebagai contoh.

Bentuk Drop Zone: Anda dapat mengatur bentuk dialog PivotTable Field List dengan mengklik dropdown pada bagian atas. Bentuk yang ditawarkan untuk tampilan ini ada lima (5). Tiga diantaranya tidak menyertakan daftar field atau drop zones. Jika tampilan dialog box anda tidak menampilkan salah satu bagian tersebut, maka gunakan panah dropdown untuk mengembalikan

tampilan ke Fields Section and Areas Section Stacked. Ada juga yang menampilkan field dan drop zone secara berdampingan.

Kesimpulan: PerintahInsert – Pivot Table dapat meringkas ribuan baris data secara cepat. Excel tidak membutuhkan anda mengerti rumusnya. Anda hanya harus dapat meletakkan field ke dala tata letak (layout) laporan.

Perintah Di Menu: Insert – Pivot Table