PENDAHULUAN
M
icrosoft Excel atau biasa disebut Excel, merupakan program spreadsheat (pengolah data) yang mempunyai kemampuan mengolah data secara luas pada bidang akuntansi, teknik, statistik, dan bidang-bidang lain yang memerlukan perhitungan cepat dan teliti. Excel dikenal sebagai program spreadsheet yang lebih mudah digunakan dibandingkan program spreadsheet yang lain. Kemudahan tersebut terlihat jelas dari banyaknya fasilitaas rumus siap pakai (fungsi) yang disediakan Excel. Penggunaan Excell yang semakin luas saat ini menjadi tuntutan bagi pelaku bisnis atau karyawan perkantoran untk dapat menguasai aplikasi ini secara optimal. Salah satu kunci sukses untuk menguasai Excel adalah kemampuan dalam menggunakan formula dnan fungsi Excel secara efektif, efisien, dan profesional. Jika anda menguasai kemampuan tersebut. Pekerjaan anda sehari-hari tentu dapat diselesaikan dengan mudah dan cepat BAGIAN-BAGIAN MICROSOFT EXCELL Setelah aplikasi microsoft excel di jalankan maka akan muncul tampilan lembar kerja seperti gambar 1.1 di bawah ini
Gambar 1.1 Tampilan Microsoft Excel 1
Keterangan 1. Office Button terdiri atas perintah New Document, Open, Convert, Save, Save as, Print, Prepare, Send, Publish dan Close. 2. Quick Access Toolbar, terdiri dari tombol yang dapat mempercepat kerja anda. Isinya hampir sama dengan perintah-perintah di Office Button. Jika anda ingin melihatnya langsung bisa menekan tanda panah. 3. Menu Bar, terdiri dari perintah menu utama Microsoft Excel 2007 yaitu Home, Insert, Page Layout, Formulas, Data, Review, View. 4. Title Bar, berisi nama file dan program aplikasi yang sedang aktif. 5. Tool Bar, tombol-tombol bergambar yang memiliki fungsi tertentu dan digunakan untuk menjalankan suatu perintah dengan cepat dan mudah. 6. Tombol Ukuran, digunakan untuk mengatur ukuran jendela kerja. 7. Name Box, Nama sel yang sedang aktif akan ditampilkan pada Name Box. 8. Formula Bar, anda bisa mengedit data pada sebuah sel. 9. Sel Aktif, tempat menuliskan atau mengedit data dan dikelilingi oleh garis batas yang lebih tebal. 10. Column, Setiap kolom memiliki nama berdasarkan Abjad, dari kolom A hingga Z, dilanjut AA hingga XFD. 11. Baris, untuk setiap baris memiliki nama berdasarkan Angka. Dari 1 hingga 1048576. 12. Tab Worksheet, perintah dimana anda bisa berpindah ke kertas kerja lain dengan mudah. 13. Insert Worksheet, dengan menekan Insert Worksheet anda bisa membuat Worksheet baru yang letaknya berurutan dengan Worksheet sebelumnya. 14. Status Bar, Status lembar kerja anda. 15. Document Area, merupakan lembar kerja yang aktif. 16. Horizontal Scroll Bar, perintah untuk menggeser layar ke kiri atau ke kanan. 17. Vertical Scroll Bar, perintah untuk menggeser layar ke atas atau ke bawah. 18. Tampilan Layar, terdiri dari perintah untuk merubah tampilan layar. Terdiri dari Tampilan Normal, Tampilan Page Layout dan Tampilan Page Break Preview. 19. Zoom, perintah untuk memperbesar & memperkecil tampilan pada lembar kerja anda.
2
BEKERJA DENGAN EXCEL
A. LEMBAR KERJA (WORKSHEET)
W
orksheet merupakan area utama tempat anda bekerja pada aplikas Ms. Excell, karena itu penguasaan mengenai area ini sangatlah penting untuk mendukung kemudahan bekerja. Pada saat anda mengaktifkan MS. Excell, sebuah Buku Kerja (Workbook) otomatis terbuka dan siap digunakan. Buku kerja kosong secara default terdiri atas tiga Lembar Kerja (worksheet). Kalau kita ibaratkan workbook adalah sebuah lemari, maka worksheet adalah sebagai lacinya, artinya Excel telah memberikan kita berbagai kemudahan dalam mengelola data. Contoh penggunaan Lembar Kerja (worksheet) di dalam Buku Kerja (workbook) adalah, kita dapat mengelola data misalnya Data Gaji Pegawai dari Bulan Januari-Desember 2013 dalam sebuah nama file saja yaitu File Gaji Pegawai Tahun 2013.xlsx yang terdiri dari 12 worksheet (sheet Januari, Februari......., Desember). Gambar 2.1 menunjukkan penggunaan worksheet dalam Excel, Anda dapat menentukan berapa banyak lembar kerja sesuai dengan kebutuhan untuk satu buku kerja dengan cara klik Inser Worksheet
Insert Worksheet
Gambar 2.1 Penggunaan Worksheet dalam Excel
Lembar kerja Ms. Excell terdiri atas : 1. Baris (Rows) Baris (rows) dinomori dari atas ke bawah di sepanjang pinggir kiri lembar kerja. Baris I dinomori 1, baris II dinomori 2, dan seterusnya hingga baris 1048576. 2. Kolom (Coloumns) Kolom (coloumn) dilabeli dari kiri ke kanan dengan huruf-huruf. Kolom I adalah A, Kolom II adalah B, dan seterusnya hingga Z, lalu muncul AA sampai XFD.
3
3. Sel (Cells) Adalah titik pertemuan sebuah baris dan kolom, misalnya sel paling kanan atas adalah Sel A1 (Kolom A, Baris 1). Sel F6 adalah titik pertemuan kolom F baris 6. 4. Range Adalah dua sel yang saling berdekatan atau lebih. Range ditentukan oleh sel pertama (paling kiri atas) dan terakhir di sebuah baris, kolom atau daerah persegi panjang. Contoh : E3 : E10 : Berarti semua sel di Kolom E dari baris ke- 3 sampai baris ke- 10. B2 : F5 : Berarti semua sel di daerah persegi panjang yang dibatasi oleh sel B2 (kiri atas) dan sel F5 (kanan bawah). 5. Formula / Fungsi Formula merupakan fitur Excel yang digunakan untuk melakukan perhitungan nilai yang dituliskan secara langsung pada formula, atau nilai yang tersimpan dalam suatu sel. Penggunaan formula harus diawali dengan tanda sama dengan (=), disertai kombinasi elemen : 1. Nilai yang dimasukkan langsung ke dalam suatu formula 2. Referensi alamat sel/range atau nama sel/range 3. Operator perhitungan 4. Fungsi Susunan formula untuk Excel 2007 dapat terdiri maksimal 8.192 karakter termasuk tanda (=), nama fungsi, tanda kurung, argment ataupun pemisah argument dan operatoroperatornya. Apabila Excel tidak menerima formula yang anda ketikkan, Excel akan menampilkan nilai error. Penjelasan tentang Formula/Fungsi akan dibahas lebih lanjut pada bab selanjutnya. B. FORMAT KOLOM DAN BARIS 1. Merubah Ukuran Kolom Letakkan pointer di pembatas kolom yang ingin dirubah ukurannya, lalu drag sesuai dengan ukuran yang di inginkan
Tekan tahan dan geser kursor sesuai ukuran yang diinginkan
Gambar 2.2 Merubah ukuran kolom dengan drag 4
Jika ingin merubah ukuran kolom lebih dari satu kolom, maka blok kolom yang
ingin dirubah kolomnya menggunakan mouse atau menggunakan tombol keyboard. (Shift + )
Gambar 2.3 Merubah ukuran kolom dengan menu Kemudian klik tab Home dan klik icon Format Column Width, isi lebar kolom dan tekan OK.
2. Merubah Ukuran Baris Letakkan pointer di pembatas baris yang ingin dirubah ukurannya, lalu drag sesuai dengan ukuran yang di inginkan Jika ingin merubah ukuran baris lebih dari satu baris, maka blok baris yang ingin dirubah menggunakan mouse atau menggunakan tombol keyboard (Shift +)
Gambar 2.3 Merubah ukuran baris dengan menu Kemudian klik tab Home dan klik icon Format RowHeight Isi tinggi Baris dan tekan OK.
5
FORMAT TAMPILAN DATA Untuk ntuk lebih menyempurnakan hasil pengolahan data, excel menyediakan banyak fasilitas untuk memformat berbagai tampilan data seperti angka, posisi data menggunakan fasilitas tersebut Pilih Menu Home Toolbar Format Format Cells atau anda bisa langsung menekan tombol keyboard Ctrl +1 untuk menuju Format Cells, seperti ditunjukkan pada Gambar 3.1
Gambar 3.1 Format Cells A. NUMBER Fasilitas ini digunakan untuk mem-format nilai numerik yang terdiri atas beberapa tipe data angka text, nilai satuan mata uang, tanggal, waktu, persentase, dll. Excel menawarkan berbagai fasilitas untuk format tipe data. Untuk melakukan format tampilan data caranya Sorot sel atau range akan diformat, kemudian klik Menu Bar Home Toolbar Number, seperti ditunjukkan pada Gambar 3.2
6
Gambar 3.2 Format Cells - Number Excel menawarkan berbagai fasilitas untuk format angka seperti pada tabel di bawah ini : Format
Keterangan
General
Menampilkan nilai dengan format ada adanya sesuai yang anda masukkan
Number
Menampilkan angka dengan format dseimal. Angka negatif dapat ditampilkan warna merah atau diawali dengan tanda – (minus)
Date
Menampilkan format tanggal yang dapat ditampilkan dalam format hari, bulan, dan tahun (dd, mmm, yyyy)
Time
Menampilkan format waktu yang dapat ditampilkan dalam jam, menit, dan detik. Anda juga dapat menampilkan variasi antara tanggal dan waktu dengan mempilkan keterangan AM atau PM
Currency / Accounting
Menampilkan simbol mata uang Rp, $, dan sebagainya dengan memilih pada kotak pilihan symbol dan menentukan tempat desimal serta tampilan untuk angka negatif.
Percentage
Excel akan menampilkan format angka yang ditambahkan tanda % pada bagian belakang angka tersebut. Excel akan mengalikan angka pada suatu sel dengan 100 dan menampilkan hasilnya dengan tanda persen
Fraction
Gunakan format ini untuk menampilkan angka berbentuk pecahan seperti 1/3, 3/7 dan sebagainya
Scientic
Berfungsi untuk menampilkan angka dalam notasi ilmiah
Text
Berfungsi untuk menampilkan teks dan angka pada sebuah sel atau range, dan Excel akan menampilkan sesuai dengan yang anda ketik
7
Format
Keterangan
Special
Format spesial di desain khusus untuk menampilkan format kode pos, nomor telepon, dan jaminan sosial
Custom
Fasilitasi Custom dapat anda gunakan untuk memformat angka, tanggal, waktu, mata uang sesuai yang anda tentukan. Simbol # mewakili sebuah angka atau karakter dan 0 mewakili sebuah angka nol
B. ALIGNMENT Fasilitas ini digunakan untuk mengatur penjajaran data yang tersimpan pada suatu sel atau range dengan posisi rata kiri, rata kanan, ditengah sel atau kolom. Perataan tampilan data secara vertikal atau horizontal dan derajat kemiringan data/teks, seperti ditunjukkan pada Gambar 3.3
Gambar 3.3 Format Cells - Alignment 1. Text Alignment Yaitu Untuk menetapkan atau mengatur posisi teks (left, right, center dll) vertikal maupun horizontal. 2. Text Control Wrap Text Teks otomatis menurun apabila tulisan melebihi lebar sel (pelipatan teks). Shrink to Fit Digunakan untuk merapatkan teks
8
Merge Cells : untuk menggabungkan dua sel atau lebih menjadi satu sel (merger). Untuk mengaktifkannya, klik dikotak tersebut.
C. FONT Untuk memilih model, jenis dan ukuran huruf yang akan digunakan serta untuk memilih warna dan efek-efek khusus, seperti ditunjukkan pada Gambar 3.4
Gambar 3.4 Format Cells - Font D. BORDER Untuk meletakkan kotak dan garis tepi yang mengelilingi suatu sel yang dipilih. Biasanya digunakan untuk membuat garis pada tabel, dengan cara menyorot semua baris dan kolom yang akan dibuat tabel kemudian meng-klik pilihan Outline dan Inside, seperti ditunjukkan pada Gambar 3.5
Gambar 3.5 Format Cells - Border
9
E. FILL Untuk memberi warna atau pola dalam suatu sel atau lebih yang dipilih. Yaitu dengan cara menyorot sel yang akan diberi pola warna kemudian klik pilihan Color. Seperti ditunjukkan pada gambar 3.6
Gambar 3.6 Format Cells - Fill F. PROTECTION Fasilitas ini digunakan untuk mengunci/memproteksi cell, menyembunyikan formula, memproteksi worksheet. Untuk memproteksi cells, kita bisa memberi tanda check ( ) pada pilihan Locked, atau Hidden setelah itu klik OK Lalu pilih menu Review Protect Sheet untuk proteksi selanjutnya, seperti ditunjukkan pada Gambar 3.6
Gambar 3.6 Format Cells - Protection
10
BERKERJA DENGAN FORMULA / FUNGSI
F
ormula merupakan fitur Excel yang digunakan untuk melakukan perhitungan nilai yang dituliskan secara langsung pada formula, atau nilai yang tersimpan dalam suatu sel. Penggunaan formula harus diawali dengan tanda sama dengan (=), disertai kombinasi elemen : 1. Nilai yang dimasukkan langsung ke dalam suatu formula 2. Referensi alamat sel/range atau nama sel/range 3. Operator perhitungan 4. Fungsi Susunan formula untuk Excel 2007 dapat terdiri maksimal 8.192 karakter termasuk tanda (=), nama fungsi, tanda kurung, argment ataupun pemisah argument dan operator-operatornya. Apabila Excel tidak menerima formula yang anda ketikkan, Excel akan menampilkan nilai error. Berikut nilai Error yang ditampilkan Excell apabila terjadi kesalahan penulisan formula Pesan Error
Kemungkinan Penyebab
#DIV/0!
Formula yang anda masukkan menyebabkan Excel membagi dengan angka nol (0) atau membagi pada referensi sel yang kosong
#N/A
Ada argumen yang tidak tertulis dalam formula yang anda masukkan
#NAME?
Dalam formula yang anda masukkan, ada argumen atau nama fungsi yang tidak dikenal Excel
#NUM!
Penggunaan yang salah dari sebuah bilangan, misalnya anda menghitung akar pangkat dua dari bilangan negatif. Nilai error #NUM! Juga dapat diakibatkan karena formula menghasilkan nilai terlalu besar (1x10307) atau terlalu kecil (-1x10307)
#NULL!
Formula menentukan perpotongan antara dua range yang tidak berpotongan
#VALUE
Penulisan argumen tidak sebagaimana yang disyaratkan oleh fungsi, misalnya referensi yang anda masukkan dalam sebuah fungsi adalah data berupa teks padahal seharusnya memasukkan data berupa angka. Nila error #VALUE juga dapat terjadi karena jumlah karakter yang digunakan dlam formula melebihi karakteri maksimal yang diperbolehkan
#REFF
Salah satu referensi atau nama sel/range yang digunakan dalam formula terlah terhapus
11
A. OPERATOR ARITMATIKA Operator Aritmatika digunakan untuk melakukan kalkulasi operasi matematika, seperti penambahan, pengurangan, perkalian, pembagian, perpangkatan atau persentase. Operator
simbol
Penambahan
+
Pengurangan
-
Perkalian
*
Pembagian
/
Perpangkatan
^
Contoh penggunaan formula Aritmatika
Gambar 4.1. Contoh Penggunaan Operator Aritmatika Langkah-langkah Penyelesaian 1. Letakkan kursor di cells D6, lalu ketikkan =B6+C6, artinya kita melakukan perhitungan penjumlahan Bilangan A yang ada di Cells B6 dengan Bilangan B yang ada di Cells C6 kemudian tekan Enter 2. Untuk hasil penjumlahan pada baris berikutnya, kita tidak perlu menuliskan lagi rumus seperti diatas, kita tinggal copy ke bawah rumus pada baris pertama 3. Kita perhatikan rumus pada baris kedua, Alamat cells akan otomatis mengikuti lokasi cells. Rumus =B6+C6 akan berubah menjadi =B7+C7 pada baris kedua dan seterusnya 4. Lakukan hal yang sama untuk perhitungan Pengurangan, Perkalian, Pembagian, Perpangkatan, seperti ditunjukkan pada Gambar 4.1 diatas
12
B. SIFAT ALAMAT SEL Ketika Anda menggunakan suatu formula, maka disarankan untuk menggunakan nilai dalam referensi sel. Penggunaan referensi sel mempunyai kelebihan dibandingkan nilai yang dimasukkan langsung ke dalam formula. Apabila ada perubahan nilai dalam referensi sel, formula akan melakukan perhitungan ulang sehingga diperoleh hasil perhitungan baru. 1. Formula dengan Referensi Sel Relatif Alamat sel yang jika disalin akan menyesuaikan lokasi sel hasil salinan. Ciri alamat relatif adalah ketika rumus di-copy ke bawah atau kanan, rumus yang di-copy-kan mengikuti alamat sel Contoh
Gambar 4.2 Contoh Referensi Sel Relatif Sel D4 diatas (=B4*C4) jika dicopy kebawah, formula akan menyesuaikan lokasi baris baru 2. Formula dengan Referensi Sel Absolut Alamat absolut berarti sel yang kolom dan barisnya terkunci, artinya jika alamat sel ini disalin ke bawah maupun ke samping tidak akan mengalami penyesuaian karena baris dan kolomnya terkunci Alamat ini ditandai dengan $ pada posisi kolom dan barisnya. Misalnya : $A $2
Kolom A dikunci Baris 2 juga dikunci
Gambar 4.3 Contoh Referensi Sel Absolut 13
3. Formula dengan Referensi Sel Semi Absolut Sel Semi Absolut yang digunakan dalam formula jika disalin menyesuaikan baris atau kolom yang tidak terkunci. Misalnya : A$2 Absolut Baris, jika sel di copy, kolom berubah, baris tetap (terkunci), $A2 Absolut Kolom, jika sel di copy, baris berubah, kolom tetap (terkunci) Contoh.
Gambar 4.4 Contoh Referensi Sel Semi Absolut Untuk penyelesaian kasus diatas, kita cukup menuliskan satu kali fungsi saja pada sel B4, lalu kita copy kebawah lalu ke samping.
C. FUNGSI STATISTIKA Sebagai sebuah pengolah angka yang canggih, Excel 2007 menyediakan bermacam macam fungsi yang bisa digunakan untuk berbagai keperluan. Salah satunya fungsi Statistika (statistical) yang berguna untuk mengolah data statistik. FUNGSI
KEGUNAAN
PENULISAN
SUM
Menjumlahkan pada angka pada sel atau range tertentu
=SUM(angka 1, angka 2,...) =SUM(alamat range)
MAX
Menampilkan nilai terbesar dari suatu range data numerik
=MAX(angka 1, angka 2,...) =MAX(alamat range)
MIN
Menampilan nilai terkecil dari suatu range data numerik
=MIN(angka 1, angka 2,...) = MIN(alamat range) 14
AVERAGE
Menampilan rata-rata dari suatu range data numerik
=AVERAGE (angka 1, angka 2,...) =AVERAGE(alamat range)
PRODUCT
Fungsi Product digunakan untuk melakukan perkalian sekumpulan data pada suatu range
=PRODUCT (angka 1, angka 2,...) =PRODUCT (alamat range)
COUNT
Menghitung jumlah data yang bersifat numerik dari suatu range
=COUNT(angka 1, angka 2,...) =COUNT(alamat range)
SQRT
Digunakan untuk menghasilkan suatu =SQRT(number) nilai akar kwadrat dari suatu bilangan
Contoh :
Gambar 4.5. Contoh Penggunaan Operator Aritmatika
D. FUNGSI KARAKTER (TEKS) Fungsi teks yaitu fungsi yang digunakan untuk memanipulasi data teks. Fungsi Teks yang disediakan oleh Microsoft Excell antara lain : FUNGSI
KEGUNAAN
=LEFT
Digunakan untuk mengambil sejumlah karakter data dari karakter sebelah kiri Penulisan Rumus =Left(Text;[Num_Chars] Maksud dari rumus diatas adalah : Text : adalah tempat dimana data yang akan diambil karakternya, Num_Char : adalah berapa jumlah karakter yang hendak kita ambil.
15
=MID
Digunakan untuk mengambil sejumlah karakter data dari karakter ke-n sebanyak m karakter. Penulisan Rumus =Mid(Text;Start_Num;Num_Chars) Maksud dari rumus diatas adalah Text : adalah tempat dimana data yang akan diambil karakternya Star_Num : adalah karakter diambil dimulai dari karakter berapa dari kiri, Num_Char : adalah berapa jumlah karakter yang hendak kita ambil.
=RIGHT
Digunakan untuk mengambil sejumlah karakter data dari karakter sebelah kanan Penulisan Rumus =Right(Text;[num_chars] Maksud dari rumus diatas adalah : Text : adalah tempat dimana data yang akan diambil karakternya, Num_Char : adalah berapa jumlah karakter yang hendak kita ambil.
=UPPER
UPPER digunakan untuk mengubah teks menjadi huruf besar semua. Misalnya teksnya “ini adalah fungsi UPPER” akan diubah menjadi “INI ADALAH FUNGSI UPPER” = UPPER (text).
=LOWER
LOWER digunakan untuk menguah teks menjadi huruf kecil semua. Misalnya teknya “ini adalah fungsi LOWER” akan diubah menjadi “ini adalah fungsi lower” = LOWER (text)
=PROPER
PROPER digunakan untuk menguah teks menjadi huruf kecil semua dan huruf besar semua setiap awal kata. Misalnya teknya “ini adalah fungsi PROPER” akan diubah menjadi “Ini Adalah Fungsi Proper” = PROPER(text)
=CONCATE digunakan untuk menggabungkan string menjadi satu kalimat maksimal NATE 30 string, penulisannya : =CONCATENATE(text1,text2,…). =LENGTH
Digunakan untuk menghitung jumlah karakter teks yang ditujukan. Rumusnya =Len(text) Contoh : =Len(“Aku”) akan menghasilkan nilai 3
Contoh :
Gambar 4.6. Contoh Penggunaan Fungsi Left, Mid, dan Right 16
E. FUNGSI LOGIKA IF Fungsi logika IF digunakan untuk membandingkan dua atau lebih argument untuk menghasilkan nilai Benar (True) atau Salah (False) Bentuk penulisan logika IF adalah :
=IF(Logical_Test; [Value_If_True]; [Value_If_False] Keterangan : Logical_Test
: nilai atau ekspresi yang dipakai untuk mengevaluasi kondisi benar (true) atau salah (false) Value_If_True : nilai yang dihasilkan jika evaluasi Benar Value_If_False : nilai yang dihasilkan jika evaluasi Salah Sebagai pembanding antar argumen menggunakan tanda =, >, <, <=, >=, <> Contoh Kasus : IF Bentuk Tunggal Siswa dinyatakan Naik Kelas jika nilai >= 50
Gambar 4.7. Contoh Penggunaan Fungsi IF Tunggal Logical_Test B2 >= 50 Value_If_True “Naik Kelas” Value_If_False “Tinggal Kelas” Artinya jika nilai sel B2 lebih besar sama dengan (>=) 50, maka siswa tersebut dinyatakan “Naik Kelas”, sebaliknya jika nilai sel B2 kurang dari 50 maka dinyatakan “Tinggal Kelas”. Contoh Kasus : IF Bentuk Majemuk
Gambar 4.8. Contoh Penggunaan Fungsi IF Majemuk
17
Logical_Test B2 = “A” Value_If_True “Istimewa” Value_If_False “IF(B2="B";"Baik";IF(B2="C";”Cukup";"Kurang"))” Artinya jika pada sel B2 nilainya A maka hasilnya “ISTIMEWA”, jika pada sel B2 nilainya B maka hasilnya “BAIK”, jika pada sel B2 nilainya C maka hasilnya “CUKUP”, atau selain kondisi diatas (bukan A, B, dan C) maka akan menghasilkan nilai “KURANG”. F. FUNGSI AND / OR 1. FUNGSI AND Fungsi AND digunakan untuk menguji beberapa argumen (kondisi atau syarat) apakah bernilai BENAR (true) atau SALAH (false) Apabila ada salah satu argumen yang bernilai SALAH (false), maka fungsi akan bernilai SALAH (false), walaupun ada argumen lain yang bernilai BENAR (true). Fungsi akan menghasilkan nilai BENAR (true), jika semua kondisi atau syarat bernilai BENAR (true) Penulisan Rumus AND =AND(Logical1;[Logical2]....) Tabel Kebenaran Logika AND Logical 1 (P) T F T F
Logical2 (Q) F T T F
Nilai (P Q) F F T F
Fungsi AND seringkali dikombinasikan dengan fungsi IF Penulisan Rumus AND yang dikombinasikan dengan Rumus Logika IF adalah =IF(AND(LogicalTest1;[LogicalTest2]);ValueIfTrue;[ValueIfFalse]) Contoh Kasus : Dari Daftar Nilai Siswa dibawah ini, Siswa dinyatakan lulus jika Nilai UTS >= 65 dan Nilai UAS >=75 =IF(AND(C6>=65;D6>=75); "Lulus";"Gagal")
Gambar 4.9. Contoh Penggunaan Fungsi AND 18
2. FUNGSI OR Fungsi OR digunakan untuk menguji argumen (kondisi atau syarat) apakah bernilai BENAR (true) atau SALAH (false) Apabila ada salah satu argumen yang bernilai BENAR (true), maka fungsi akan bernilai BENAR (true), walaupun ada argumen lain yang bernilai SALAH (false). Fungsi akan menghasilkan nilai BENAR (true), jika salah satu kondisi atau syarat bernilai BENAR (true) Penulisan Rumus OR =OR(Logical1;[Logical2]....) Tabel Kebenaran Logika OR Logical 1 (P) T F T F
Logical2 (Q) F T T F
Nilai (P Q) T T T F
Fungsi OR seringkali dikombinasikan dengan fungsi IF Penulisan Rumus OR yang dikombinasikan dengan Rumus Logika IF adalah =IF(OR(LogicalTest1;[LogicalTest2]);ValueIfTrue;[ValueIfFalse]) Contoh Kasus : Pelamar dinyatakan lolos ujian tahap selanjutnya (wawancara) jika Pendidikan S1 atau punya pengalaman kerja minimal 2 tahun
=IF(OR(C6=“S1”;D6>=2); “Ujian Wawancara";"Gugur")
Gambar 4.10. Contoh Penggunaan Fungsi OR
19
G. FUNGSI REFERENSI TABEL Fungsi Vlookup & Hlookup pada prinsipnya memiliki fungsi dan tujuan yang sama dengan logika =if , artinya sama-sama digunakan untuk menguji suatu kondisi. Namun ketika kita dihadapkan kepada suatu kasus yang kompleks dengan banyak kondisi maka Fungsi if menjadi kurang efisien. 1. Fungsi VLOOKUP Fungsi yang digunakan untuk mencari nilai berdasarkan pembacaan pada tabel referensi (tabel bantu) yang tersusun secara vertikal. Penulisan Rumus ;
=Vlookup(Lookup_Value;Table_Array;Col_Index_Number;[True/False]) Keterangan Lookup_Value Table_Array
: Nilai Referensi dari Tabel yang akan kita uji : Range data acuan yang berfungsi sebagai pembacaan tabel referensi Col_Index_Number : Nomor urut yang menyatakan POSISI KOLOM pada tabel referensi [True/False] : Tergantung kondisi yang akan diuji True
- Nilai Lookup_Value tidak harus sama persis dengan nilai pada Table_Array, nilai terdekat dari Table_Array dan yang lebih kecil dari Lookup_Value yang akan dibaca/ditampilkan - Nilai pada Table_Array harus urut secara Ascending (A-Z, 1-10)
False
- Nilai Lookup_Value harus sama persis dengan nilai pada Table_Array, jika nilai tidak sama akan muncul pesan error #N/A - Nilai pada Table_Array tidak harus urut
2. Fungsi HLOOKUP Fungsi yang digunakan untuk mencari nilai berdasarkan pembacaan pada tabel referensi (tabel bantu) yang tersusun secara horizontal. Penulisan Rumus ;
=Hlookup(Lookup_Value;Table_Array;Row_Index_Number;[True/False]) Keterangan Lookup_Value Table_Array
: Nilai Referensi dari Tabel yang akan kita uji : Range data acuan yang berfungsi sebagai pembacaan tabel referensi Row_Index_Number : Nomor urut yang menyatakan POSISI BARIS pada tabel referensi
20
Contoh Kasus : Keterangan Kode Barang A : Mouse B : Keyboard C : Speaker D : Flashdisk E : Headset F : Card Reader
Gambar 4.11. Penyelesaian dengan Fungsi IF Penyelesaian menggunakan Fungsi Logika IF : =IF(B5="A";"Mouse";IF(B5="B";"Keyboard";IF(B5="C";"Speaker";IF(B5="D";"Flashdisk "; IF(B5="E";"Headset";"Card Reader"))))) Dari contoh kasus diatas, pemakaian fungsi IF kurang efisien karena kita harus menuliskan satu persatu logika yang akan kita uji. Coba anda bayangkan jika Kode Barang pada contoh kasus diatas mencapai puluhan bahkan ratusan…..??? Oleh karena itu Pemakaian Fungsi Vlookup / Hlookup adalah SOLUSINYA…!!! Penyelesaian menggunakan Fungsi VLOOKUP/HLOOKUP : Untuk penggunaan Fungsi Vlookup/Hlookup, kita harus terlebih dahulu membuat sebuah tabel bantu kode barang yang nantinya digunakan untuk pembacaan referensi (Table Array) kode barang pada Laporan Persediaan Barang. Tabel Bantu bisa diletakkan di dalam sheet yang aktif ataupun di sheet yang berbeda dengan Tabel Utama. Contoh dibawah ini kita akan membuat sebuah Tabel Bantu di Sheet yang sama dengan Tabel Utama
Sorot hanya isi tabel (record) lalu tekan F4
=VLOOKUP(B5;$F$5:$G$10;2;FALSE)
Karena tabel bantu berbentuk Vertikal, maka kita menggunakan fungsi Vlookup
Gambar 4.12. Contoh Penggunaan Fungsi Vlookup 21
Untuk mempermudah penulisan rumus, sebaiknya Tabel Bantu kita beri nama terlebih dahulu, dengan cara sorot isian (record) pada tabel kode barang lalu pilih menu Formula Define Name muncul menu New Name, lalu isikan nama yang kita inginkan pada kotak isian Name... seperti pada gambar di bawah ini.
Gambar 4.13. Memberi nama pada sel
Sehingga penulisannya rumusnya menjadi =VLOOKUP(B5;KODE_BRG;2;FALSE)
22
PIVOT TABLE/CHART A. PIVOT TABLE PivotTable merupakan tabel yang merangkum informasi dari field tertentu dalam sebuah basis data. Penggunaan fasilitas PivotTable memungkinkan pembuatan tabel pelengkap yang informatif dan lebih hidup sehingga memudahkan dalam menganalisis data. Contoh Kasus Ketiklah data berikut :
Gambar 5.1. Data Penjualan Toko Elektronik Agak sulit bagi kita mengambil kesimpulan dari tabel tersebut, karena data yang satu bercampur dengan data yang lain, misalnya data seorang wiraniaga bercampur dengan wiraniaga yang lain, data suatu produk juga bercampur dengan produk lain. PivotTable dapat digunakan untuk melihat tabel dari berbagai sudut pandang hingga memudahkan kita menarik kesimpulan.
23
Langkah-langkah membuat PivotTable sebagai berikut: 1. Pilih range yang akan diberikan perintah PivotTable, pada, range A5:D24. 2. Pilih Tab Insert PivotTable. Kotak dialog akan ditampilkan seperti Gambar 5.2
Gambar 5.2. Create Pivot Table 3. Pilihan New Worksheet jika hasil pivot table diletakkan di sheet yang baru, Pilihan Existing Worksheet jika hasil pivot table diletakkan di sheet yang sama dengan data. 4. Klik OK. Pivot table akan ditampilkan seperti Gambar 5.3
Gambar 5.3. Pivot Table 24
5. Di bagian kanan kotak dialog Pivot Tabel Field List, di sini kita bisa menentukan/memilih field yang akan ditampilkan di bagian Report Filter, Coloumn Labels, Row Labels, dan Values. Kita ingin agar setiap halaman menampilkan data seorang wiraniaga. Drag field Wiraniaga ke kotak Report Filter Data setiap produk akan ditampilkan dalam suatu kolom. Drag field Produk ke kotak Column Labels Setiap baris akan menampilkan data penjualan pada suatu tanggal. Drag field Tanggal ke kotak Row Labels. Data yang ingin ditampilkan adalah jumlah penjualan. Drag field Jumlah kekotak Values. 6. Hasilnya akan terlihat seperti Gambar 5.4
Gambar 5.4. Hasil Pivot Table Dari hasil Pivot Table diatas kita sudah bisa menganalisa, berapa produk yang terjual masing-masing wiraniaga dari semua total penjualan
25
B. PIVOT CHART Langkah-langkah membuat PivotChart sama seperti langkah-langkah membuat pivotTable, hanya saja setelah memilih Tab Insert, pilih PivotChart. Lakukan langkah untuk membuat PivotChart sehingga hasilnya seperti Gambar 5.4
Gambar 5.4. Hasil Pivot Chart
26
LATIHAN-LATIHAN MICROSOFT EXCEL Latihan 1
Latihan 2
27
Latihan 3
Latihan 4
28
Latihan 5
Latihan 6
Nama Kereta A1 : Argo Bromo A2 : Argo Sindoro A3 : Kaligung Mas Tujuan Kereta JKT : Jakarta BDG : Bandung SBY : Surabaya Kelas A : Executive B : Bisnis
Untuk isian No. Tempat Duduk, Kode Kereta, Kode Tujuan, Kode Kelas diinput berdasarkan No. Karcis
29
Latihan 7
Petunjuk Mengerjakan 1. Untuk input No. Urut, Prodi Pilihan, Kelas, Status Mahasiswa, Jenis Kelamin berdasarkan Nomor Pendaftaran Contoh No. Pendaftaran 001/H/P/B/L 001 : No. Urut / H : Prodi Pilihan / P : Kelas / B : Status Mahasiswa / L : Jenis Kelamin 2. Keterangan Prodi Pilihan M : Manajemen A : Akuntansi H : Hukum
Kelas P : Pagi S : Sore
Status Mahasiswa B : Baru P : Pindahan
Jenis Kelamin L : Laki-laki P : Perempuan
Grade >=80 >=70 >=60 < 60
A B C D
Besarnya SPI A 4.000.000 B 4.500.000 C 5.000.000
Keterangan : Nilai Kurang dari 60 dinyatakan Tidak Lulus 30
Pengantar Aplikasi Komputer – Microsoft Excel 2007 Prodi Manajemen Fak. Ekonomi Unikal
Latihan 8
Pelamar yang dinyatakan lolos seleksi adalah Pelamar dengan pendidikan S1, umur <= 27 tahun, dan IPK >= 3 Latihan 9
Pelamar dinyatakan Lolos Administrasi jika lulusan : - Sarjana Pertanian, - Sarjana Perkebunan, - Sarjana Kehutanan
31
Latihan 10
1. Kolom Nama Pesawat, Tujuan, Harga Tiket diisi berdasarkan Kode Pesawat Kode Kereta NAMA PESAWAT Tujuan Harga Tiket MA Merpati Airlines Jakarta 750.000 GI Garuda Indonesia Surabaya 500.000 SA Sriwijaya Air Denpasar 1.500.000 BA Batavia Air Medan 1.250.000 2. Jumlah bayar = Harga Tiket * Jumlah Tiket 3. Khusus untuk Tiket Pesawat Garuda Indonesia dan Batavia Air, diberikan discount sebesar 10% dari Jumlah Bayar 4. Pajak dikenakan sebesar 1% dari Jumlah Bayar, jika pembelian tiket pesawat Sriwijaya Air dengan jumlah tiket >= 2 5. Total Bayar = (Jumlah Bayar – Disc) + Pajak 6. Lengkapi dengan Total Penjualan, Rata-rata Penjualan, Penjualan Tertinggi, Penjualan Terendah
32
Latihan 11 Laporan Gaji Karyawan PT. ABC
Daftar Nilai Mahasiswa
Bulan September 2013
Mata Kuliah : Pengantar Aplikasi Komputer
NO
NAMA
KODE KARYAWAN
1
Agus
D
2
Asep
P
3
Budi
P
4
Hesty
K
5
Dina
A
6
Rini
K
7
Joni
P
8
Ricky
A
9
Ulfa
D
10
Asih
P
BAGIAN KARYAWAN
GAJI POKOK
Untuk input Bagian Karyawan dan Gaji Pokok berdasarkan Tabel Gaji Tabel Gaji
NO
NAMA
Nilai
1
Agus
75
2
Asep
74
3
Budi
81
4
Hesty
55
5
Dina
73
6
Rini
80
7
Joni
78
8
Ricky
64
9
Ulfa
86
10
Asih
45
GRADE
PREDIKAT
Untuk input Grade dan Predikat berdasarkan Tabel Nilai Tabel Nilai
KODE
BAGIAN
GAJI POKOK
A
Administrasi
Rp 2.000.000
D
Manager
Rp 5.000.000
K
Keuangan
Rp 4.000.000
P
Personalia
Rp 3.000.000
NILAI AKHIR
GRADE
0 60 70 80 90
E D C B A
PREDIKAT Tidak Lulus Kurang Memuaskan Memuaskan Sangat Memuaskan Dengan Pujian
33
Latihan 12 Laporan Penjualan Sepeda Motor Dealer Honda Indo Motor Semarang Bulan Januari 2014 NO.
NAMA KONSUMEN
KODE KENDARAAN
MERK KENDARAAN
HARGA KENDARAAN
BONUS
KODE EXSTRA
BONUS EXTRA
1
Bima
VT
?
?
?
SG
?
2
Albert
BT
?
?
?
VC
?
3
Dito
SC
?
?
?
SG
?
4
Umi
VX
?
?
?
AS
?
5
Utami
VT
?
?
?
SG
?
6
Edo
SC
?
?
?
VC
?
Untuk input Merk Kendaraan, Harga Kendaraan, dan Bonus berdasarkan Tabel Jenis Kendaraan Untuk input Bonus Extra berdasarkan Tabel Bonus Extra
Tabel Jenis Kendaraan
Tabel Bonus Extra
KODE KENDARAAN
MERK KENDARAAN
HARGA KENDARAAN
BONUS
VT BT SC VX
Vario Techno Beat Scoopy Vixion
17.000.000 15.000.000 16.000.000 23.000.000
Helm Jas Hujan Cover Plat Jaket
KODE EXTRA
AS
SG
VC
BONUS EXTRA
ASURANSI
SERVICE GRATIS
VOUCHER
34
Latihan 13 CV. MAJU SELALU DAFTAR PESANAN TIKET NO
NAMA
KODE TIKET
1 2 3 4 5 6 7 8 9 10
Nisa Winny Lia Audi Rinny Aryo Rohmat Tandzila Sekar Dyta
A1 B1 C1 D1 B1 C1 D1 A1 D1 C1
KOTA TUJUAN
HARGA TIKET
KODE JENIS
JENIS PESANAN
BONUS
J1 J3 J2 J1 J1 J3 J1 J1 J3 J2
Untuk Kota Tujuan, Harga Tiket diisi berdasarkan Kode Tiket, (Lihat TABEL KODE TIKET) Untuk Jenis Pesanan dan Bonus diisi berdasarkan Kode Jenis (Lihat TABEL KODE JENIS) Total Biaya adalah Harga Tiket dikalikan Jumlah Pesanan
Tabel Kode Tiket
DISCOUNT
TOTAL BAYAR
Total Jumlah Jumlah Maksimal Jumlah Minimal Jumlah Rata-rata
Tabel Kode Jenis
A1 B1 C1
KOTA TUJUAN Jakarta Bandung Surabaya
Rp Rp Rp
150.000 125.000 130.000
D1
Medan
Rp
175.000
KODE TIKET
TOTAL BIAYA
1 1 2 1 3 1 2 3 1 3
Keterangan 1. 2. 3.
JUMLAH PESANAN
HARGA TIKET
KODE JENIS JENIS PESANAN BONUS
J1 Executive Snack + Soft Drink
J2 Bisnis Soft Drink
J3 Ekonomi -
4. Discount diberikan jika penumpang membeli Tiket dengan kriteria sebagai berikut Kota Tujuan = Jakarta, Jenis Pesanan = Executive, Diskon 10 % Kota Tujuan = Medan, Jenis Pesanan = Executive, Diskon 5 % % 5. Total Bayar adalah Total Biaya dikurangi Discount
35
Latihan 14
DAFTAR PENUMPANG KERETA - STASIUN KERETA API PEKALONGAN NAMA PENUMPANG
NO. TEMPAT DUDUK
NAMA KERETA
KOTA TUJUAN
HARGA TIKET
JUMLAH TIKET
NO.
NO. TIKET
1
001/A1/JKT
Dwita Alodya
2
2
005/A1/SMG
Ilham Maulana
5
3
007/A1/SMG
Kenzie Fairus
4
4
002/A2/SMG
Ricky Rahmawan
4
5
004/A2/BDG
Raditya Davinsa
1
6
008/A2/BDG
Elena Anindia
3
7
003/A3/JKT
Prawira Duta K
3
8
006/A3/BDG
Irfan Kamaludin
2
TOTAL HARGA
DISC
TOTAL BAYAR
Jumlah Total
No. Tiket : 001 No. Tempat duduk 1. 2. 3. 4.
A1
/
/
Kode Kereta
Rekap Tiket Terjual Agro Bromo Kaligung Mas Tawang Jaya
JKT Tujuan Kereta
Rp. Rp. Rp.
Untuk Input No. Tempat Duduk, Nama Kereta, dan Harga Tiket berdasarkan No. Tiket mengacu pada Tabel Kereta Untuk Input Kota Tujuan berdasarkan No. Tiket mengacu pada Tabel Tujuan Kereta Discount hanya diberikan untuk pembelian Tiket Kereta Argobromo dan tujuan Semarang Buatlah analisa data menggunakan PIVOT CHART sehingga Perusahaan dapat mengetahui Data dan Grafik Penjualan jumlah Tiket terjual masing-masing item Kereta dan Tujuan
Tabel Kereta
Tabel Tujuan Kereta
Kode Kereta
Nama Kereta
A1 A2 A3
Argo Bromo Kaligung Mas Tawang Jaya
Semarang 75.000 40.000 45.000
Harga Tiket Jakarta 165.000 100.000 75.000
Bandung 155.000 125.000 100.000
Kode Tujuan Kota Tujuan
JKT Jakarta
SMG Semarang
BDG Bandung
36
Latihan 15 Daftar Penjualan Barang Elektronik – Bulan September 2012 ATHA ELEKTRONIK No.
Kode Transaksi
Nama Pembeli
1 2 3 4 5 6 7 8
TVPT-01 LETS-02 MCSM-01 LEPT-03 TVSM-02 MCTS-03 TVPT-02 LESM-03
Dwita Alodya Ricky Rahmawan Prawira Duta K Raditya Davinsa Ilham Maulana Irfan Kamaludin Kenzie Fairus Elena Anindia
Nama Barang
Merk Barang
Harga Barang
Bonus
Jumlah
Total Harga
Disc
Total Bayar
2 3 2 3 1 4 3 2 Total Penjualan
Keterangan : 1. Untuk Input Nama Barang dan Harga Barang berdasarkan Kode Transaksi mengacu pada Tabel Nama Barang dan Harga 2. Untuk Input Merk Barang berdasarkan Kode Transaksi mengacu pada Tabel Merk 3. Untuk Input Bonus berdasarkan Kode Transaksi mengacu pada Tabel Bonus 4. Discount hanya diberikan untuk pembelian Barang khusus merk Samsung dengan pembelian minimal 2.000.000 yaitu sebesar 25% 5. Buatlah analisa data menggunakan PIVOT CHART sehingga Perusahaan dapat mengetahui Data dan Grafik Penjualan Jumlah Barang terjual masing-masing item nama barang dan merk barang
Tabel Nama Barang dan Harga Kode Barang
Nama Barang
TV LE MC
Televisi Lemari Es Mesin Cuci
Tabel Merk Samsung 1.000.000 2.000.000 1.250.000
Harga Barang Toshiba 1.250.000 2.300.000 1.500.000
Polytron 1.500.000 1.750.000 1.000.000
Kode Merk Merk
SM Samsung
TS Toshiba
PT Polytron
01 Kipas Angin
02 VCD
03 Magc Jar
Tabel Bonus Kode Bonus Bonus
37
