Microsoft Office Excel

PENGANTAR APLIKASI KOMPUTER

Microsoft Office Excel

Oleh : Ari Muhardono, S.Kom., M.Kom

FAKULTAS EKONOMI UNIVERSITAS PEKALONGAN

Pengantar Aplikasi Komputer – Microsoft Office Excel Fakultas Ekonomi Universitas Pekalongan

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

U

ntuk lebih menyempurnakan hasil pengolahan data, excel menyediakan banyak fasilitas untuk memformat berbagai tampilan data seperti angka, posisi data pada suatu sel/range, jenis, ukuran dan efek cetakan huruf dan lain-lain. Untuk 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  Merge Cells : untuk menggabungkan dua sel atau lebih menjadi satu sel (merger). Untuk mengaktifkannya, klik dikotak tersebut. 8


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 NATE

digunakan untuk menggabungkan string menjadi satu kalimat maksimal 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 (Format Cells I)

Latihan 2 (Format Cells II)

27


Latihan 3 (Fungsi Teks)

Latihan 4 (Fungsi Logika IF I)

28


Latihan 5 (Fungsi Logika IF II)

Latihan 6 (Kombinasi Fungsi Logika IF & Teks I) Nama Kereta A1 : Argo Bromo A2 : Argo Sindoro A3 : Kaligung Mas Tujuan Kereta JKT : Jakarta BDG : Bandung SBY : Surabaya Jenis Kelas A : Executive B : Bisnis

Untuk isian Nama Kereta, Tujuan Kereta, Jenis Kelas diinput berdasarkan No. Karcis

29


Latihan 7 (Kombinasi Fungsi Logika IF & Teks II)

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 E : Ekstensi

Status Mahasiswa B : Baru P : Pindahan

Keterangan : Nilai Kurang dari 60 dinyatakan Tidak Lulus

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

30


Latihan 8 (Fungsi Boolean And/OR I)

Pelamar yang dinyatakan lolos seleksi adalah Pelamar dengan pendidikan S1, umur <= 27 tahun, dan IPK >= 3 Latihan 9 (Fungsi Boolean And/OR II)

Pelamar dinyatakan Lolos Administrasi jika lulusan : - Sarjana Pertanian, - Sarjana Perkebunan, - Sarjana Kehutanan

31


Latihan 10 (Kombinasi Fungsi Logika IF & Teks & And/OR)

32


Latihan 11 (Fungsi Vlookup/Hlookup I) 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 (Fungsi Vlookup/Hlookup II) LAPORAN PENJUALAN SEPEDA MOTOR DEALER HONDA INDO MOTOR SEMARANG BULAN JANUARI 2014 NAMA KONSUMEN

NO.

 

KODE KENDARAAN

MERK KENDARAAN

HARGA KENDARAAN

BONUS

KODE EXSTRA

1

Bima

VT

SG

2

Albert

BT

VC

3

Dito

SC

SG

4

Umi

VX

AS

5

Utami

VT

SG

6

Edo

SC

VC

BONUS EXTRA

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 (Fungsi Vlookup/Hlookup + IF And/OR)

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

CV. TRAVELING ON THE WAY DAFTAR PESANAN TIKET KODE MOBIL

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

KOTA TUJUAN Jakarta

Rp

150.000

B1 C1

Bandung Surabaya

Rp Rp

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 MOBIL BONUS

J1 Shutle Snack + Soft Drink

J2 Inova Soft Drink

J3 Avanza -

4. Discount diberikan jika penumpang membeli Tiket dengan kriteria sebagai berikut  Kota Tujuan = Jakarta, Jenis Mobil = Shutle, Diskon 10 %  Kota Tujuan = Medan, Jenis Mobil = Shutle, 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 sebesasr 5% 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

Samsung 1.000.000 2.000.000 1.250.000

Harga Barang Toshiba 1.250.000 2.300.000 1.500.000

Tabel Merk 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

Microsoft Office Excel

Recommend Documents