aplikasi fungsi dalam microsoft excel

fungsi vlookup, hlookup, dan lookup value

• fungsi flookup

Vlookup merupakan fungsi bantuan references. Fungsi Vlookup dipakai untuk menghasilkan nilai pada tabel secara vertikal. Penulisan : =VLOOKUP(nama_baris;tabel;kolom_pencarian;range_lookup)

misal :

solusi : Pada A8 masukkan nilai NIM terlebih dahulu yang terdapat pada tabel NIM-Nama. kemudian pada B8 ketikkan formula =LOOKUP(A8;$A$2:$B$5;2;0).

• fungsi hlookup

Hlookup merupakan fungsi bantuan references juga bedanya fungsi hlookup dipakai untuk menghasilkan nilai pada tabel secara horizontal. Penulisan : =HLOOKUP(nama_kolom;tabel;baris_pencarian;range_lookup)

misal :

solusi : Pada C6 ketikkan formula =HLOOKUP(B6;$B$1:$F$3;2;0).

• lookup value

Pada prinsipnya sama dengan Vlookup, namun pada lookup value ini memungkinkan kita untuk mengambil beberapa data dari tabel lain sabagai referensi / patokan.

misal :

Dari 2 tabel yakni tabel peminjaman dan tabel buku akan dibuat Daftar Peminjaman Buku.

solusi :

–        Nama pada cell B11 adl =VLOOKUP(A11;$A$2:$D$5;2;0)

–        Perihal pada cell D11 adl = VLOOKUP(C11;$F$2:$H$5;2;0)

–        Judul pada cell E11 adl = VLOOKUP(C11;$F$2:$H$5;3;0)

Fungsi Text Sederhana (LEFT, MIDDLE, RIGHT)

LEFT               = memilih karakter text dimulai dari kiri ke kanan dengan range tertentu
MIDDLE          =  memilih karakter text dimulai dari tengah (dari kiri ke kanan)dengan range tertentu
RIGHT             = memilih karakter text dimulai dari kanan ke kiri dengan range tertentu

contohnya :

Fungsi-fungsi text akan kita terapkan pada kolom C, D dan E

Pada sel C5 ketik formula berikut : = LEFT(B5,3)
Pada sel D5 ketik formula berikut : = MID(B5,4,3)
Pada sel E5 ketik formula berikut : = RIGHT(B5,2)

Hasilnya akan tampak seperti di bawah ini :

Fungsi IF (COUNTIF)

Keterangan table

Kolom B (kode jawaban) diambil jawaban pada lembar ujian dari siswa (tiap siswa berbeda kalau tidak saling contek)

Kolom C (kode jawaban), pasti diambil dari yang buat soal ujian

Penulisan Formula:

sel D8 (untuk menghitung benar atau salah jawaban siswa) pakai fungsi IF

sel D9 sampai D17 (formulanya dikopi dari sel D8)

sel D19 (menghitung jumlah jawaban siswa yang benar)

sel D20 (menghitung jumlah jawaban siswa yang salah)

Sekarang coba mulai membuat formula di sel D8, isi dengan formula berikut: =IF(B8=C8,("Benar"),"Salah")

Arti formula di atas adalah :

Jika nilai sel B8 sama dengan nilai sel C8, maka isi sel D8 dengan Benar

Jika nilai sel B8 tidaksama dengan nilai sel C8, maka isi sel D8 dengan Salah

Copy formula di atas ke sel D9 sampai D17

Selanjutnya isi sel D19 dengan formula : = COUNTIF(D8:D17,"Benar")

Arti formula di atas kira-kira begini: Lakukan pencacahan jika nilai sel dari range D8:D17 Benar (jika nilai dari range D8:D17 salah maka akan diabaikan)

Kemudian isi sel D20 dengan formula : = COUNTIF(D8:D17,"Salah")

Arti formula di atas kira-kira begini: Lakukan pencacahan jika nilai sel dari range D8:D17 Salah

Hasilnya bisa anda lihat di bawah ini

Fungsi IF (OR)

Fungsi IF (OR) di  microsoft excel 2003  digunakan jika kita menginginkan salah satu dari beberapa syarat terpenuhi agar logika bernilai benar.

Contoh: 
Dalam rangka hari ibu nasional, maka tiket kereta api akan memberikan diskon kepada penumpang

a.Jika jenis kelamin wanita atau anak-anak umur <=10 tahun, diskon tiket 40%

b.Selain di atas diskon 10%.

Untuk membuat formulanya di excel 2003, lakukan prosedur berikut:
1. Buat tabel seperti di bawah ini

2.Ketik formula

a.Di sel F4 = IF(OR(C4="W",D4<=10),E4*0.4,E4*0.1)

b.Di sel G4 = E4-F4

Copy formula hingga baris 9

Hasilnya seperti di bawah ini

Fungsi if (AND)

Penggabungan fungsi IF (AND) excel 2003 digunakan jika menginginkan minimal 2 syarat pernyataan atau lebih untuk logika bernilai benar.

Sebagai referensi:

Jika Jenis kelamin laki-laki dan usia >=40, maka THR = 10% dari gaji

Jika Jenis kelamin laki-laki dan usia >=32, maka THR = 15% dari gaji

Selain di atas THR=2% dari gaji

Untuk membuat formulanya, bisa menggunakan fungsi IF(AND)

1. Buat tabel seperti di bawah ini

2. Masukkan formula

a.Sel E4 = IF(AND(B4="L",C4>=40),D4*10%,IF(AND(B4="W",C4>=35),D4*15%,D4*2%))

b.Sel F4 = D4+E4

Hasilnya akan terlihat seperti di bawah ini

Fungsi if , date, if(fungsi text)

a. Biaya CD untuk keping cd /vcd (film atau program) , 2500/hari untuk keping dvd (film atau program) , 5000/hari

b. Biaya denda keterlambatan pengembalian CD untuk keping cd /vcd (film atau program) , 1000/hari untuk keping dvd (film atau program) , 2000/hari

c. Kode CD

CF cd film

CP cd program

DF dvd film

DP dvd program

d. limit sewa (batas sewa cd) jika cd yang dipinjam  4 atau lebih maka limit 3 hari
jika cd yang dipinjam  3 maka limit 2 hari jika cd yang dipinjam  1 atau 2 maka limit 1 hari 

Untuk lebih jelasnya bisa buat tabel berikut:

1. Tabel data

kolom d,g,h,i,j ,k akan diisi formula

kolom e dan f dalam format date (english) anda bisa mengubah ke dalam format tanggal Indonesia

kolom g diformat ke general atau number

2. Formula

Ketik formula berikut

a. sel D4 = IF(C4>3,3,IF(C4>2,2,1))

b. sel G4 = F4-E4

catatan: sel G4 d format ke dalam format number atau general blok sel G4 sampai G7 pilih format - cell - number – general

c. sel H4 = IF(G4>D4,"denda","tidak")

d. sel I4 = IF(LEFT(B4,1)="C",C4*2500,C4*5000)

e. sel J4 = IF(LEFT(B4,1)="C",(G4-D4)*1000*C4,(G4-D4)*2000*C4)

f. sel K4 = I4+J4

Hasilnya seperti di bawah ini

tutorial mail merge

. TUTORIAL MAIL MERGE .

Mail merge membantu kita untuk membuat sebuah dokumen (misalnya surat) yang isinya sama untuk penerima yang berbeda secara cepat dan mudah. Fitur ini sangat membantu terutama bila penerimanya sangat banyak. Mail merge juga dapat digunakan untuk membuat sertifikat, label, dan amplop.


Untuk membuat mail merge terdiri dari 2 dokumen, yaitu:

       Dokumen Master, dokumen yang isinya sama untuk semua penerima.

       Data Source, yaitu dokumen yang isinya khusus untuk masing-masing penerima, misalnya nama dan alamat penerima. Data source dapat berupa data di Excel, Access, Contact pada Outlook dan sebagainya. Pada tutorial ini, data source menggunakan Excel, karena cara ini lebih cepat. Selain itu:

       Lebih mudah mengontrol dan menambah data baru.

       Lebih mudah membaca data yang ditampilkan di lembar kerja Excel terutama bila kita ingin memperbaiki kekeliruan seperti salah pengejaan dan sebagainya.

       Juga mempermudah menyalin data source pada dokumen Excel ke lembar kerja yang lain.
Misalnya dalam membuat sertifikat pelatihan yang pesertanya lebih kurang sama, maka akan lebih mudah memilih nama pada lembar kerja tersebut dan menyalinnya ke dokumen (data source) baru.

Langkah-langkah membuat mail merge adalah sebagai berikut:

Tahap I Membuat Data Source

Buat data source di Excel seperti contoh di bawah ini dan simpan dengan nama Data Surat.

Tahap II Membuat Dokumen Master

Buat dokumen master surat di Word seperti contoh di bawah ini dan simpan dengan nama Master Surat.
Beri jarak untuk menaruh nama dan alamat. Dokumen ini jangan ditutup karena kita akan lanjutkan ke langkah berikut: menggabungkan Data Source dengan Dokumen Master.

Tahap III Menggabungkan Data Source dengan Dokumen Master

Pada Mailings tab, grup Start Mail Merge, klik Start Mail Merge.

1.     Selanjutnya kita akan memilih data source.

2.     Pada Mailings tab, grup Start Mail Merge, klik Select Recipients.

3.     Pilih Use Existing List, dan cari lokasi dokumen (Data Surat) pada kotak dialog Select Data Source. Kemudian klik Open untuk menampilkan kotak dialog Select Table.

4.     Pada kotak dialog Select Table, klik lembar kerja yang berisi sumber data dan pastikan kotakFirst row of data contains column headers dicentang. Kemudian klik OK.

5.     Sekarang kita akan menempatkan data pada masing-masing tempatnya.

6.     Taruh di kursor di bawah tulisan Kepada Yth., kemudian pada Mailings tab, grup Write & Insert Fields, klik Insert Merge Field dan pilih Nama.

7.     Lakukan juga hal yang sama pada Alamat dan sekarang tampilan dokumen akan seperti contoh di bawah ini. Data ditampilkan dalam tanda kurung siku dua <<data field>>.

Tips:

• Di sini kita dapat mengatur format surat, seperti memberi cetak tebal pada Nama dan sebagainya.
• Bila hanya data tertentu saja yang ingin dipilih, lakukan cara berikut:
1. Pada Mailings tab, grup Start Mail Merge, klik Edit Recipient List.
2. Di kotak dialog Mail Merge Recipients, kosongkan kotak pada daftar seperti contoh di bawah ini.
• Bila ada penambahan data baru di Excel, klik nama dokumen pada Data Source dan klik tombol Refresh. Data yang baru akan ditampilkan di daftar.

Untuk melihat hasil penggabungan, klik tombol Preview Results. Surat akan ditampilkan per data. Klik tanda panah untuk berpindah antar data. Jika masih ada yang ingin diubah, klik lagi tombol PreviewResults dan lakukan perubahan yang diinginkan. 

Tahap IV Menyimpan dan Mencetak Mail Merge

Pada tahap ini, klik tombol Finish & Merge dan pilih Print Documents, untuk mencetak surat.
Ada 3 pilihan:

       All untuk mencetak semua surat.

       Current record untuk mencetak surat pada halaman yang aktif.

       Isi nomor urut data pada kotak From dan To, untuk mencetak surat yang dipilih saja.

Edit Individual Documents, untuk menyimpan hasil penggabungan ke dokumen baru.
Pilihan sama dengan di atas (All, Current record dan nomor urut tertentu).
Pada dokumen baru, setiap surat akan ditampilkan per halaman. Jangan lupa menyimpan dokumen baru tersebut.

Seperti yang sudah diketahui oleh pengguna microsoft office 2007, banyak sekali perubahan tampilan toolbar pada microsoft word 2007. Bagi mereka yang agak malas atau mungkin tidak punya banyak waktu untuk belajar memahami word 2007 mungkin akan lebih baik kembali ke microsoft word versi klasik yang dirasa toolbarnya sudah familliar dengan mereka. Cara membuat data dengan fasilitas mail merge pada microsoft word 2007 langkahnya tidak jauh berbeda jika dibandingkan dengan microsoft word versi klasik, seperti yang telah disebutkan di atas tadi, microsoft word 2007 toolbarnya sangat berbeda dengan versi klasik. jadi di sini akan dipandu menemukan toolbar-toolbar baru untuk penggunaan fasilitas mail merge. mari kita mulai belajar bagaimana penggunaan mail mergei pada microsoft word 2007.

1.     bukalah dulu program atau aplikasi microsoft word 2007, Klik tombol Start, pilih All Program, kemudian pilih Microsoft Office, kemudian klik pada Microsoft Office 2007.

2.  setelah Program Microsoft word 2007 terbuka, sebagai latihan ketiklah contoh data seperti di bawah ini.  Untuk teks yang saya beri tanda kurung seperti  <nama>, <alamat>, dan <kota>   itu nantinya sebagai tempat kode mail merge. jadi dikosongkan saja, tidak usah diketik.

3.  Sehingga hasilnya seperti gambar di bawah ini.

4.    Setelah data di atas selesai diketik, sekarang saatnya membuat data mail Merge.  Klik pada Menu Mailings, Kemudian klik pada Toolbar   Start maili Merge, lalu klik pada  Step by step Mail Merge Wizard…

5.  Kemudian perhatikan pada bagian kanan jendela Microsoft word 2007,  akan muncul jendela Mail Merge.  Pada Select document type pilih letters,  kemudian klik pada  Next: Starting document ,

6. Tahap selanjutnya pada  Select starting document  pilih Use the current document, kemudian pilih Next: select recipients

7.  Tahap selanjutnya pada   Select recipents pilih pada Type a New list,  lalu klik pada Create …

8.   setelah kamu klik tombol create, Jendela  New Address list akan terbuka seperi gambar di bawah ini. karena pada latihan ini kita hanya menggunakan tiga address yaitu <nama>, <alamat>, dan <kota>, maka kita buat ketiga address tersebut secara manual, dengan cara klik pada tombol customize Columns

9.  Setelah kamu klik Customize Columns, akan terbuka jendela  Customize addres list seperti gambar di bawah ini. hapuslah semua yang ada pada Field names dengan cara Klik pada tombol Delete,

10. jika nanti muncul pertanyaan seperti gambar di bawah ini, klik saja Yes.

11.  Setelah kamu menghapus semua Field names , klik pada Add.. , untuk menambahkan Field baru, kemudian ketikkan nama Field dan klik tombol OK. (nama field pada latihan ini  “nama”, “alamat”, dan “kota” )

12.  Sehingga hasilnya seperti gambar di bawah ini.  kemudian klik tombol OK

13.  Address list yang baru pun akan tampak seperti gambar di bawah ini,  dan  Klik pada tombol OK

14.  Setelah kamu klik tombol Ok, akan muncul jendela penyimpanan file. berikan nama file nya pada kolom file name (pemberian nama filenya bisa sesukamu), kemudian klik tombol Save.

15.  Sekarang Data mail Merge telah selesai, kemudian klik pada tombol Ok

16. sekarang saatnya meletakkan kode mail merge pada ruang kosong yang telah kita sediakan tadi, yaitu untuk ruang <nama>, <alamat>, dan <kota>.  kita mulai dari <alamat> dulu.  Letakkan kursor pada ruangan kosong untuk alamat yaitu di bawah  tulisan  Yth. Bp/Ibu/Sdr.

17.  Setelah kamu letakkan kursor di situ, klik pada menu  Insert Merge Field, sehingga akan muncul daftar Fieldnya, pilih klik pada field nama. lakukan langkah yang sama untuk menampilkan alamat, dan Kota.

18.  Sehingga hasilnya akan tampak seperti gambar di bawah ini.

19.  Untuk merubah kode  <nama>, <alamat>, dan <kota> menjadi data yang sebenarnya klik pada Icon  Preview Result

20. Dan hasilnya akan tampak seperti gambar di bawah ini.

21. Sedangkan untuk menampilkan data urutan kedua, ketiga dan seterusnya, klik pada tanda panah ke kanan, sedangkan untuk kembali klik pada tanda panah ke kiri.