materi kkpi excel

Upload: bagus-drajat-trimulyo

Post on 10-Oct-2015

89 views

Category:

Documents


9 download

TRANSCRIPT

  • 5/20/2018 Materi Kkpi Excel

    1/72

    Ketrampilan Komputer dan Pengelolaan Informasi

    Diktat ini disusun sebagai pegangan siswa untuk belajar Ketrampilan Komputer dan

    Pengelolaan Informasi (KKPI) dengan Kompetensi Dasar Mengoperasikan Perangkat Lunak

    Pengolah Angka (Spreadsheet Software)

    8/12/2014 - SMK Neger i 2 Depok Sl ema n

    Ketrampilan Komputer dan Pengelolaan Informasi - KKPI

  • 5/20/2018 Materi Kkpi Excel

    2/72

    HALAMAN SAMPUL

  • 5/20/2018 Materi Kkpi Excel

    3/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel ii

    DAFTAR ISI

    HALAMAN SAMPUL ............................................................................................................................ i

    DAFTAR ISI......................................................................................................................................... ii

    1. PENGANTAR............................................................................................................................... 1

    a. Definisi Formula ................................................................................................................... 1

    b. Definisi Fungsi ...................................................................................................................... 1

    2. MENGENAL FORMULA ............................................................................................................... 2

    a. Menggunakan Function Library ............................................................................................ 3

    b. Menuliskan Formula Secara Manual ..................................................................................... 5

    3. MENGENAL TATA CARA PENULISAN ........................................................................................... 6

    a. Tanda Sama Dengan ............................................................................................................. 6

    b. Tanda Kurung ....................................................................................................................... 6

    c. Tanda Pemisah Argumen / Variabel...................................................................................... 6

    d. Tanda Kutip / Petik ............................................................................................................... 7

    e. Gunakan Alamat Sel / Range ................................................................................................ 8

    4. MENGENAL NAMA SEL ............................................................................................................... 9

    a. Menggunakan Name Box ..................................................................................................... 9

    b. Menggunakan Name Manager ........................................................................................... 11

    c. Operator Hitung Dasar ....................................................................................................... 121) Cara Penulisan ............................................................................................................. 12

    2) Cara Penggunaan ......................................................................................................... 12

    d. Operator Pembanding ........................................................................................................ 13

    e. Auto Fill ............................................................................................................................. 14

    f. Sel Absolut ......................................................................................................................... 16

    5. Fungsi SUM .............................................................................................................................. 20

    6. Fungsi AVERAGE...................................................................................................................... 21

    7. Fungsi COUNT ......................................................................................................................... 22

    8. Fungsi MAX dan MIN................................................................................................................ 23

    9. Fungsi COUNTIF ...................................................................................................................... 24

    10. Fungsi SUMIF .......................................................................................................................... 26

    11. Penggabungan Fungsi .............................................................................................................. 27

    a. Contoh 1: Menggabungkan Fungsi SUM dengan Perkalian ................................ ................. 28

    b. Contoh 2: Menggunakan 2 Fungsi SUMIF dalam 1 Formula ................................ ................ 29

    12. Fungsi IF................................................................................................................................... 30

    13. Fungsi IF Bercabang ................................................................................................................. 32

  • 5/20/2018 Materi Kkpi Excel

    4/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel iii

    14. Fungsi OR ................................................................................................................................. 34

    15. Fungsi AND .............................................................................................................................. 36

    16. Fungsi Tanggal ......................................................................................................................... 38

    17. Fungsi Waktu ........................................................................................................................... 40

    18. Fungsi Pembulatan .................................................................................................................. 42

    19. Fungsi Teks .............................................................................................................................. 43

    20. Fungsi VLOOKUP ...................................................................................................................... 45

    21. Fungsi HLOOKUP ...................................................................................................................... 51

    22. Pesan Kesalahan ...................................................................................................................... 52

    a. ##### ................................................................................................................................. 53

    b. #REF! ................................................................................................................................. 53

    c. #VALUE! ............................................................................................................................. 53d. #DIV/0! .............................................................................................................................. 53

    e. #NULL! ............................................................................................................................... 54

    f. #NUM! ............................................................................................................................... 54

    g. #NAME? ............................................................................................................................. 54

    h. #N/A! ................................................................................................................................. 54

    23. Troubleshooting....................................................................................................................... 55

    a. Peringatan Kesalahan ......................................................................................................... 55

    b. Dokumentasi Bantuan ........................................................................................................ 55

    c. Melihat Seluruh Formula .................................................................................................... 56

    d. Melihat Hasil Fungsi pada Suatu Formula ................................ ................................ ........... 57

    e. Trace Precedents dan Trace Dependents ............................................................................ 58

    f. Evaluasi Formula ................................................................................................................ 59

    g. Error Checking ................................................................................................................... 60

    24. Tips .......................................................................................................................................... 61

    25. Contoh Penerapan ................................................................................................................... 62

    a. Contoh 1: Laporan Persentase Komisi Sales ................................ ........................................ 62

    b. Contoh 2: Menghitung Total Upah Karyawan ................................ ................................ ..... 63

    c. Contoh 3: Mengurai Struktur NIM (Nomor Induk Mahasiswa) ................................ ............ 64

    d. Contoh 4: Discount Penjualan Rokok .................................................................................. 65

    e. Contoh 5: Biaya Paket Wisata Harian.................................................................................. 66

    26. Penutup ................................................................................................................................... 68

  • 5/20/2018 Materi Kkpi Excel

    5/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 1

    1.

    PENGANTAR

    Salah satu hal yang membuat aplikasi Excel terkenal adalah kelengkapan fitur Formulas dan

    Functions yang di Indonesia mungkin lebih dikenal dengan istilah rumus. Formula dan fungsi ini

    digunakan untukmembantu Anda dalam mengerjakan beragam proses perhitungan data secara

    cepat dan semi otomatis. Formula ini bisa Anda gunakan dalam perhitungan yang sederhanahingga yang kompleks, baik untuk data berupa angka, data teks, data tanggal, data waktu, atau

    kombinasi dari data-data tersebut.

    Contoh penggunaannya misalnya semua karyawan akan mendapat bonus sebesar 5% dari

    pendapatan mereka perbulan, bagaimana cara menghitungnya? Dengan proses perhitungan

    biasa maka Anda tentunya harus menghitung data karyawan satu demi satu. Namun dengan

    menggunakan formula pada Excel, maka Anda cukup mengerjakan perhitungan untuk satu data

    karyawan saja dan data-data karyawan yang lainnya akan mengikuti. Dari contoh sederhana ini

    bisa dilihat bahwa dengan adanya formula maka pekerjaan Anda bisa diselesaikan dengan lebih

    cepat hingga bisa membuat Anda lebih produktif dan lebih efisien lagi.

    a. Definisi Formula

    Secara garis besar Formula pada aplikasi Excel adalah suatu persamaan matematika untuk

    menghitung nilai-nilai tertentu dengan tujuan untuk mendapatkan hasil yang diharapkan.

    Penulisan formula selalu diawali tanda sama dengan dan umumnya melibatkan operator

    dasar matematika. Sebagai contoh, misalkan Anda hendak menghitung 10 + 15 maka

    formulanya adalah: =10+15

    Simbol tanda kurung ataupun tanda-tanda lainnya yang umum digunakan dalam operasi

    matematika juga bisa Anda gunakan pada formula Excel, misalnya: =(25+5)*8

    Nilai dalam aplikasi Excel dimuat didalam sel atau range yang memiliki alamat tertentu, oleh

    karena itu penulisan formula pun umumnya merujuk pada alamat sel-nya dan bukan pada

    nilainya, misalnya: =(A5*A6)+(B5/C5)

    Atau dalam beberapa kondisi bisa juga Anda kombinasikan antara alamat sel dengan suatu

    nilai tertentu, misalnya: =(B4+C4)*150

    b. Definisi Fungsi

    Jika Anda bekerja dengan data-data yang kompleks, maka formula yang digunakan juga

    umumnya semakin kompleks dan tentunya akan semakin panjang. Sebagai contoh, misalkan

    saja Anda hendak menghitung nilai rata-rata penjualan dari 8 unit produk, maka formulayang digunakan adalah: =(prod1+prod2+prod3+prod4+prod5+prod6+prod7+prod8)/8

    Formula sepanjang itu hanya untuk 8 unit produk saja, bagaimana jika produknya ada 100

    unit? atau 10000 unit? Sebagai solusinya maka Excel menyediakan fitur yang dinamakan

    dengan Function atau fungsi.

    Secara garis besar fungsi ini bisa dibilang sebuah preset dari formula yang bertujuan untuk

    menyederhanakan formula hingga membuat proses perhitungan data menjadi lebih singkat

    dan tentunya relatif lebih mudah untuk dikerjakan.

    Fungsi-fungsi dalam Excel memiliki nama-nama yang unik. Jika Anda pernah mendengar kataSUM, itu adalah nama salah satu fungsi yang paling umum diterapkan pada aplikasi Excel

  • 5/20/2018 Materi Kkpi Excel

    6/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 2

    yang kegunaannya untuk mengitung data pada range tertentu. Lalu bagaimana sebuah

    fungsi bisa menyederhanakan perhitungan? dari contoh sebelumnya dimana Anda hendak

    menghitung nilai rata-rata dari 8 unit produk, maka Anda cukup menggunakan fungsi untuk

    menghitung nilai rata-rata yaitu AVERAGE, dengan demikian formula yang digunakan adalah:

    =AVERAGE(range_produk)

    Disini sudah jelas terlihat bagaimana sebuah fungsi dapat menyederhanakan sekaligus

    menyingkat sebuah formula yang panjang menjadi lebih sederhana. Dalam penerapannya,

    fungsi juga umumnya mengacu pada alamat sel. Misalkan saja data untuk 8 unit produk

    tersebut terdapat pada sel B5 hingga B12 maka formulanya akan menjadi:

    =AVERAGE(B5:B12)

    Dari penjelasan singkat diatas, dapat dilihat bahwa struktur formula pada aplikasi Excel

    tersusun dari banyak komponen, seperti misalnya data berupa nilai yang akan dihitung,

    alamat sel atau range, operator dasar matematika, operator pembanding, fungsi, serta

    simbol-simbol atau tanda pelengkap lainnya yang membantu proses perhitungan dalam

    formula tersebut. Contoh sebuah formula dengan struktur yang cukup lengkap dapat dilihatberikut ini :

    =VLOOKUP(C4;$A$12:$C$16;IF(B4

  • 5/20/2018 Materi Kkpi Excel

    7/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 3

    Formula yang Anda tuliskan bisa dilihat sekaligus dimuat dalam sebuah bar yang dinamakan

    dengan Formula Bar. Anda tentu mengenal lokasi formula bar ini yang merupakan suatu bar

    kosong dengan tulisan fx disampingnya serta terletak di bagian atas bidang kerja utama

    aplikasi Excel.

    Jika suatu sel Anda isi dengan data biasa maka isi sel ini akan sama dengan isi pada formula

    bar, namun jika suatu sel Anda isi dengan formula maka sel akan menampilkan hasil

    perhitungan dan formula bar akan memuat formulanya. Sebagai contoh, pada gambar

    berikut ini dapat dilihat bahwa sel D3 menampilkan data 25000 yang merupakan hasil

    perhitungan, sementara formula bar menampilkan =B3*C3 yang merupakan formula yang

    digunakan. Jika Anda hendak menyunting formula tersebut maka Anda tinggal meng-klik

    mouse pada formula bar atau menekan tombol F2pada keyboard.

    Untuk menuliskan formula ini, ada 2 cara yang bisa dilakukan yaitu dengan menggunakan

    Function Libraryserta cara manual. Berikut ini penjelasan singkat untuk kedua cara tersebut.

    a. Menggunakan Function Library

    Pada aplikasi Excel 2010, Function Librarybisa Anda temukan pada ribbon didalam tab

    Formulas. Isinya berupa seperangkat tombol-tombol perintah penggunaan fungsi yang

    terbagi kedalam beberapa kategori spesifik seperti misalnya fungsi yang berhubungan

    dengan akunting dan keuangan dimuat dalam kategori Financial, fungsi yang

    berhubungan dengan operasi matematika dimuat dalam kategori Math & Trig, dan

    demikian seterusnya.

    Untuk menggunakannya, klik pada sel dimana Anda ingin menuliskan hasil perhitungan

    kemudian klik salah satu tombol yang sesuai pada group Function Library tersebut.

    Berikutnya pada daftar pilihan fungsi yang ditampilkan, klik pada fungsi yang ingin Andagunakan.

  • 5/20/2018 Materi Kkpi Excel

    8/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 4

    Kotak dialog Function Argumentskemudian akan ditampilkan dan Anda tinggal mengisikan

    argumen pada kolom-kolom isian yang telah disediakan didalam kotak dialog tersebut sesuai

    dengan aturan penulisan formulanya. Argumen-argumen ini akan dibahas di bagian lain

    dalam rangkaian artikel ini.

    Selain tombol-tombol perintah yang spesifik untuk kategori fungsi tertentu, dalam group

    Function Library ini terdapat beberapa tombol perintah lain yang bisa Anda gunakan untuk

    mempermudah penulisan formula, yaitu:

    Insert Functions:Jika Anda klik maka akan ditampilkan kotak dialog Insert Functionyang

    berisikan seluruh fungsi yang ada berikut kategorinya. Anda tinggal memilih fungsi yang

    sesuai untuk langsung menggunakannya. Fasilitas pencarian juga disediakan dalam kotak

    dialog ini untuk membantu Anda mencari fungsi tertentu secara cepat.

    AutoSum: Tombol ini bisa Anda gunakan untuk menuliskan fungsi-fungsi dasar yang

    umum digunakan.

    Recently Used:Berisikan daftar fungsi-fungsi terakhir yang pernah Anda kerjakan.

  • 5/20/2018 Materi Kkpi Excel

    9/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 5

    b. Menuliskan Formula Secara Manual

    Selain dengan bantuan Function Library, Anda juga dapat menuliskan formula berikut

    argumen-argumen yang menyertainya secara manual. Cara ini mungkin terlihat lebih sulittapi pada penerapannya kadang malah lebih mudah dengan cara manual ini dibandingkan

    dengan menggunakan Function Library. Banyak para praktisi Excel yang malah selalu

    menuliskan formula dengan cara manual ini, alasannya terutama karena penulisan

    argumen formula bisa dikustomisasi / divariasikan dengan lebih bebas. Keuntungan

    lainnya adalah Anda akan lebih mengenal lagi argumen untuk suatu formula tertentu

    hingga Anda bisa menuliskannya pada sembarang versi Excel tanpa harus mencari tahu

    terlebih dahulu dimana letak dan bagaimana cara penggunaan tombol-tombol perintah

    Function Library pada tiap-tiap versi Excel.

    Mengingat banyaknya pengguna Excel yang lebih memilih penulisan formula secara

    manual maka pada beberapa versi Excel yang terakhir (2007 / 2010 / 2013), Microsoftpun memberikan fitur-fitur bantuan untuk mempermudah penulisan formula secara

    manual ini. Fitur-fitur bantuan ini diantaranya adalah:

    Intellisense: Fitur ini merupakan sebuah fitur AutoComplete yang akan menjaga

    penulisan nama fungsi dari kesalahan ketik sekaligus mempercepat Anda dalam

    menuliskan nama fungsi yang akan digunakan. Sebagai contoh misalkan Anda hendak

    menggunakan fungsi SUM, maka didalam sel Anda tinggal mengetikan tanda =

    diikuti huruf S. Perhatikan bahwa Excel akan menampilkan daftar semua nama

    fungsi yang berawalan huruf S. Berikutnya dari daftar tersebut Anda tinggal klik-

    ganda pada nama fungsi SUMuntuk memuatnya pada formula bar.

    Keterangan Penulisan Argumen: Setelah Anda memilih nama fungsi, berikutnya

    Excel akan memberikan bantuan informasi tentang argumen-argumen untuk fungsi

    tersebut. Sebagai contoh pada gambar berikut ini, dapat dilihat argumen-argumen

    yang digunakan pada fungsi VLOOKUP, yaitu lookup value, table_array, dst.

  • 5/20/2018 Materi Kkpi Excel

    10/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 6

    3.

    MENGENAL TATA CARA PENULISAN

    Ada beberapa aturan penulisan formula yang harus diketahui dan diperhatikan, terutama

    jika Anda hendak menuliskan formula dengan cara manual. Berikut ini adalah aturan-aturan

    penulisan tersebut.

    a. Tanda Sama Dengan

    Tanda sama dengan atau = merupakan tanda awal dari penulisan formula yang mutlak

    harus dituliskan jika Anda hendak menggunakan formula apapun. Tanda ini juga bisa

    digunakan sebagai rujukan ke data pada lokasi lain. Sebagai contoh misalkan sel A1

    berisikan data 10. Pada sel A5, jika Anda ketikan tanda = (tanpa tanda kutip) yang

    diikuti dengan penekanan tombol Entermaka sel A5juga akan berisi data 10.

    b. Tanda Kurung

    Tanda kurung atau ( ) digunakan untuk memisah bagian-bagian yang dianggap perlu

    dalam suatu formula. Penggunaan tanda ini secara garis besar sama dengan

    penggunaan dalam operasi matematika biasa, tentunya agar perhitungan menjadi lebih

    mudah untuk dibaca dan dianalisa.

    Dalam kaitannya dengan penulisan fungsi, maka tanda kurung umum digunakan setelah

    menuliskan nama fungsi-nya, selain itu juga umum digunakan untuk memisahkan satu

    fungsi dengan fungsi lainnya jika Anda menggunakan multiple fungsi dalam suatu

    perhitungan.

    Berikut ini contoh beberapa penggunaan tanda kurung dalam penulisan formula pada

    aplikasi Excel:

    =(A1+A2)*(B1+B2)

    =SUM(A1:A7)

    =A1*(SUM(B1:B10))

    dst

    c. Tanda Pemisah Argumen / Variabel

    Dalam penerapan formula lanjutan, Anda akan banyak menggunakan banyak argumen

    ataupun variabel dalam suatu formula. Untuk memisahkan variabel-variabel ini ada dua

    tanda yang digunakan yaitu tanda koma , atau titik-koma ;. Tanda mana yang harus

    Anda gunakan? Ini disesuaikan dengan seting regional yang Anda gunakan pada

    Windows dan Excel:

    Untuk seting regional Indonesia, maka tanda pemisah variabel yang digunakan

    adalah titik-komaatau ;

    Untuk seting regional selain Indonesia, maka tanda pemisah variabel yang

    digunakan adalah komaatau ,

  • 5/20/2018 Materi Kkpi Excel

    11/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 7

    Cara memeriksa seting regional ini pada Windows 7 adalah sebagai berikut:

    Pastikan aplikasi Excel dalam kondisi tertutup.

    Start Menu > Control Panel > Region & Language.

    Pada jendela Region and Languageyang ditampilkan, pilih seting regional yang ingin

    Anda gunakan dengan memilihnya di bagian Format dan klik tombol OK untukmenyetujuinya.

    d.

    Tanda Kutip / Petik

    Tanda kutip atau digunakan jika Anda hendak menggunakan isi atau data pada

    sebuah sel untuk digunakan pada formula. Sebagai contoh pada tabel dibawah ini

    menunjukan formula untuk menghitung jumlah data Jeruk yaitu dengan menggunakan

    fungsi COUNTIF(penggunaan fungsi ini lebih lanjut dijelaskan pada tulisan yang lain).

    Jika formula dituliskan tanpa tanda kutip yaitu: =COUNTIF(B2:B9;Jeruk)

  • 5/20/2018 Materi Kkpi Excel

    12/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 8

    Maka hasilnya adalah 0karena Excel jelas tidak mengenali data Jeruk tersebut. Namun

    jika data Jeruk pada formula tersebut kita tambahkan tanda kutip seperti berikut ini:

    =COUNTIF(B2:B9;Jeruk)

    Hasilnya akan keluar yaitu 3karena dengan ditambahkannya tanda kutip tersebut makaaplikasi Excel akan mengenali data Jeruk tersebut.

    Penggunaan tanda kutip ini akan banyak Anda temukan dalam artikel yang lain pada

    rangkaian artikel ini yang membahas tentang contoh-contoh penggunaan formula

    secara spesifik.

    e. Gunakan Alamat Sel / Range

    Penulisan suatu formula hendaknya selalu merujuk pada alamat sel / range dan jangan

    merujuk langsung pada isi sel atau datanya, karena hal ini akan membuat formula

    menjadi statis dan terkunci hanya untuk data tersebut saja. Agar lebih jelas, perhatikancontoh berikut ini.

    Pada sel D2akan diisikan formula untuk menjumlahkan Data 1dan Data 2. Jika formula

    langsung merujuk pada isi sel-nya, maka formulanya adalah:

    =10+7

    Hasilnya tentu saja benar yaitu 17, namun jika Anda salin formula tersebut untuk

    menjumlahkan baris berikutnya, maka hasilnya tetap 17. Hal ini disebabkan formula

    menjadi statis atau terkunci hanya untuk menghitung baris yang pertama saja.

    Namun jika Anda merujuk pada alamat selnya, yaitu B2dan C2, maka formulanya akan

    menjadi:

    =B2+C2

  • 5/20/2018 Materi Kkpi Excel

    13/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 9

    Hasilnya tetap 17namun keuntungannya formula akan bersifat dinamis hingga jika Anda

    salin formula ke baris berikutnya maka alamat sel akan mengikuti dan menghasilkan

    nilai yang benar yaitu 11yang didapat dari formula baru yaitu:

    =B3+C3

    Pahami baik-baik beragam aturan penulisan formula ini karena salah sedikit saja dalam

    menempatkan simbol atau mungkin tanda tertentu pada formula akan menyebabkan

    formula tidak akan berfungsi sebagaimana mestinya.

    4.

    MENGENAL NAMA SEL

    Formula pada aplikasi Excel tentunya akan sangat tergantung pada alamat sel termasuk range

    yang merupakan gabungan dari beberapa sel sekaligus. Dalam sebuah formula yang kompleks,

    sering terdapat kesalahan dalam penulisan alamat sel atau range ini, hal ini rasanya wajar

    mengingat alamat sel atau range tersebut yang merupakan kombinasi huruf dan angka dalam

    jumlah banyak.

    Untuk mempermudah penulisan, maka Anda bisa memberi Namapada alamat sel atau range

    tersebut, nama ini tentunya bebas dan disesuaikan dengan keinginan Anda sendiri, namuntentunya disarankan agar Anda menggunakan penamaan yang mudah untuk diingat.

    a. Menggunakan Name Box

    Cara menggunakan penamaan untuk alamat sel atau range ini sangat mudah. Perhatikan

    contoh formula sederhana berikut ini.

  • 5/20/2018 Materi Kkpi Excel

    14/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 10

    Formula untuk menghitung Totalpada sel C10adalah :=SUM(C4:C8)

    Untuk mempermudah, maka range C4:C8akan diberi nama, misalnya saja DATA1. Caranya,

    sorot atau blok sel C4sampai dengan C8. Lalu perhatikan disudut kiri atas, terdapat kolom

    kosong yang memuat keterangan alamat sel, kolom kosong ini disebut dengan Name Box.

    Dalam kondisi sel yang masih tersorot, ketikan nama yang Anda inginkan pada Name Box

    tersebut, dalam contoh ini yaitu DATA1dan akhiri dengan menekan Enterpada keyboard.

    Sampai tahapan ini, range C4:C8 telah diberi nama yaitu DATA1. Hingga jika kita hendak

    menggunakan formula pada range tersebut, jika formula yang sebelumnya adalah

    =SUM(C4:C8)

    Dan setelah disederhanakan alamat range-nya menjadi nama DATA1, maka formulanya kini

    menjadi =SUM(DATA1)

  • 5/20/2018 Materi Kkpi Excel

    15/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 11

    b. Menggunakan Name Manager

    Dalam sebuah workbook yang kompleks yang berisikan banyak nama, maka dipastikan

    Anda akan kesulitan untuk mengingat nama apa saja yang telah Anda buat dan nama

    tersebut Anda definisikan untuk alamat sel atau range apa. Untuk membantu pengaturan

    nama-nama ini, maka Anda dapat menggunakan fitur Name Manager. Fitur ini terdapat

    pada tab Formulas, didalam group Defined Names. Cobalah untuk melakukan klik mouse

    pada tombol Name Managertersebut untuk menampilkan kotak dialog Name Manager.

    Pada kotak dialog Name Manager tersebut, dengan mudah Anda dapat melihat daftar

    nama-nama yang telah Anda buat berikut alamat sel atau range-nya. Anda juga dapat

    menghapusnya, merubahnya, ataupun membuat nama yang baru pada kotak dialog ini.

  • 5/20/2018 Materi Kkpi Excel

    16/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 12

    c. Operator Hitung Dasar

    Kegunaan utama dari formula pada aplikasi Excel tentunya untuk membantu proses

    penghitungan agar lebih cepat dan mudah. Urusan menghitung ini jelas tidak lepas dari

    peran Operator Hitung Dasar Matematikayang tentunya telah Anda kenal dengan baik,

    yaitu penambahan, pengurangan, perkalian, dan pembagian. Sebelum masuk ke

    penggunaan formula yang sesungguhnya, penggunaan operator hitung dasar ini harus

    dikuasai dengan baik.

    1) Cara Penulisan

    Pada dasarnya cara penulisan operator hitung dasar dalam aplikasi Excel sama

    dengan operator hitung dasar matematika, hanya saja mungkin ada sedikit

    perbedaan untuk beberapa operator seperti misalnya perkalian dan pembagian.

    Agar lebih jelas, tabel berikut ini menjelaskan daftar operator hitung dasar dalam

    aplikasi Excel.

    2) Cara Penggunaan

    Cara menggunakan operator hitung dasar ini pada prinsipnya sama dengan

    perhitungan matematika, hanya saja sesuai dengan aturan penulisan formula yangtelah dibahas sebelumnya, usahakan agar data yang akan dihitung selalu merujuk

  • 5/20/2018 Materi Kkpi Excel

    17/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 13

    pada alamat sel. Sebagai contoh, pada tabel berikut ini akan dihitung nilai Total

    untuk data Januarihingga Maretpada tiap-tiap tahun.

    Maka secara matematika, untuk tahun 2010Anda akan menambahkan nilai 53 + 26

    + 32. Dan pada Excel, Anda tinggal mengganti nilai-nilai tersebut dengan alamat sel-

    nya, yaitu C3, D3, dan E3. Langkah pengerjaan lengkapnya adalah sebagai berikut:

    Klik sel F3, lalu ketikan tanda =

    Klik sel C3, lalu ketikan tanda +

    Klik sel D3, lalu ketikan tanda +

    Klik sel E3, tekan tombol Enter

    Hasilnya akan langsung terlihat pada sel F3.

    Dengan cara yang sama, cobalah untuk menghitung data untuk tahun 2011 dan 2012.

    Sampai tahapan ini, diharapkan Anda sudah bisa memahami bagaimana cara kerja dasar

    dari penggunaan formula pada aplikasi Excel. Dalam penggunaan lanjutan, Anda juga dapat

    menggunakan bantuan tanda kurung untuk mempermudah Anda dalam menghitungseperti halnya proses penghitungan pada operasi matematika biasa.

    d. Operator Pembanding

    Dalam penerapan formula lanjutan, terutama pada formula yang menyertakan fungsi-

    fungsi logika, Anda mungkin harus menggunakan Operator Pembanding. Sama halnya

    dengan operator hitung dasar, operator pembanding juga pada dasarnya sama dengan

    operator pembanding matematika biasa yang telah Anda kenal. Ada 6 operator

    pembanding dalam aplikasi Excel yang harus Anda ketahui, terutama dari cara penulisan

    serta istilah operator tersebut dalam bahasa Inggris. Berikut ini selengkapnya.

    Jika ada 2 buah nilai yang dibandingkan dengan operator-operator pembanding tersebut,

    maka hasilnya adalah sebuah nilai logikaTRUE(nilai yang dianggap benar) atau FALSE(nilaiyang dianggap salah).

  • 5/20/2018 Materi Kkpi Excel

    18/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 14

    Contoh penggunaan operator pembanding ini dalam kaitannya dengan fungsi-fungsi logika

    akan dibahas kemudian pada artikel selanjutnya yang khusus membahas tentang dasar

    penggunaan fungsi logika.

    e. Auto Fill

    Auto Fill adalah fitur pada aplikasi Excel untuk mengisi data pada sel berdasarkan pola

    tertentu dari data pada sel lain. Fitur ini umumnya sering digunakan oleh para pengguna

    Excel, misalnya saja untuk membuat penomoran yang berurutan. Cara penggunaannya

    juga relatif sangat mudah, Anda bisa menggunakan tombol Auto Filldalam group Editing

    pada tab Homeatau menggunakan drag dengan mouse. Namun tampaknya para pengguna

    Excel lebih menyukai Auto Fill dengan drag mouse karena merupakan cara yang paling

    mudah untuk dilakukan. Agar lebih jelas berikut ini contoh penggunaan Auto Fill.

    Pada tabel berikut ini dapat dilihat bahwa sel B2 hingga B4 telah berisikan data berupa

    nomor 1hingga 3. Jika Anda hendak melanjutkan penomoran tersebut ke sel berikutnya,

    maka Anda tinggal menyorot sel B2hingga B4. Kemudian dalam kondisi sel masih tersorot,

    dekatkan mouse ke simbol kotak kecil yang terletak di sudut kanan bawah hingga pointer

    mouse berubah menjadi simbol tanda plus berwarna hitam.

    Berikutnya klik dan tahan lalu drag kearah bawah hingga melewati beberapa sel danlepaskan tombol mouse.

    Hasilnya dapat dilihat pada gambar berikut ini dimana sel-sel berikutnya secara otomatis

    telah terisi dengan nomor yang berurutan.

  • 5/20/2018 Materi Kkpi Excel

    19/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 15

    Dalam kaitannya dengan penggunaan formula, Auto Fill ini bisa Anda gunakan untuk

    menyalin formula dari satu sel ke sel-sel lainnya secara cepat. Prinsipnya sama dengan

    contoh Auto Fill diatas, yaitu selalu mengikuti pola yang sudah terbentuk pada sel yang

    menjadi acuan. Agar lebih jelas, perhatikan contoh berikut ini.

    Pada tabel dibawah ini, tentunya dengan mudah Anda bisa menghitung hasil penjumlahan

    di sel D3dengan menggunakan Operator Hitung Dasar. Formulanya adalah =B3+C3

    Berikutnya, bagaimana cara untuk menghitung hasil penjumlahan pada sel-sel berikutnya,

    yaitu sel D4dan D5? Dengan cara manual jelas bisa, namun bayangkan seandainya data

    yang harus Anda hitung jumlahnya sangat banyak, maka cara manual alias dihitung satu-

    persatu jelas bukan cara yang efisien.

    Agar lebih cepat sekaligus mempermudah pekerjaan Anda maka Anda bisa menggunakan

    Auto Fill. Caranya sama seperti contoh Auto Fill sebelumnya. Anda tinggal memilih sel D3

    yang telah berisikan formula perhitungan, lalu mendekatkan pointer mouse ke sudut kanan

    bawah dan kemudian klik, tahan, lalu drag ke arah bawah.

    Setelah sampai di sel D5, lepaskan tombol mouse lalu perhatikan hasilnya. Secara otomatis

    sel D4dan D5telah berisikan formula dengan mengacu pada formula didalam sel D3.

  • 5/20/2018 Materi Kkpi Excel

    20/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 16

    Jika dianalisa, dari contoh tersebut dapat Anda lihat bahwa proses Auto Fill pada sel yang

    berisikan formula ini akan selalu menambahkan alamat sel dengan sel berikutnya,

    tergantung kearah mana Anda lakukan proses Auto Fill tersebut:

    Jika arahnya kebawah maka nomor baris yang akan selalu bertambah, contohnya 1,

    2, 3, 4, 5, dst...

    Jika arahnya kekanan maka nama kolom yang akan selalu bertambah, contohnya A,

    B, C, D, E, dst...

    Pada contoh diatas, arah proses Auto Fill adalah kebawah, hingga nomor baris yang akan

    selalu bertambah sementara nama kolomnya tetap. Dan proses ini tentunya menghasilkan

    formula yang benar pada sel D4dan D5

    =B3+C3

    =B4+C4

    =B5+C5

    Dari analisa tersebut maka proses Auto Fill ini bisa Anda jalankan pada sebuah tabel yangbentuknya statis seperti tabel pada contoh diatas. Namun untuk tabel yang bentuknya

    dinamis atau kompleks, misalnya saja data yang akan dihitung berada pada alamat sel yang

    tidak tentu, maka Auto Fill ini tidak dapat Anda gunakan kecuali dengan bantuan Sel

    Absolut yang dibahas pada artikel yang lain dalam rangkaian artikel ini.

    Pelajari baik-baik konsep penggunaan Auto Fill ini serta arah maupun cara pergerakannya,

    karena dalam aplikasi Excel, proses Auto Fill ini merupakan salah satu bantuan utama yang

    dapat mempercepat pekerjaan Anda saat harus menggunakan formula pada sebuah tabel

    dengan data yang jumlahnya banyak.

    f. Sel Absolut

    Sel Absolutadalah istilah untuk sel yang alamatnya berada dalam kondisi terkunci. Tujuan

    dari penguncian ini adalah agar alamat sel tidak mengalami perubahan saat dilakukan

    proses Auto Fill yang selalu menambahkan alamat sel ke sel berikutnya. Dengan adanya Sel

    Absolut ini maka Auto Fill tetap bisa digunakan pada sebuah tabel data yang semi konsisten

    atau pada formula lanjutan yang menggunakan tabel referensi.

    Cara menggunakan Sel Absolut sangat mudah, yaitu dengan menambahkan tanda $

    didepan nama kolom atau nomor barisnya. Untuk menambahkan tanda $ ini Anda dapat

    mengetikannya secara manual atau menggunakan tombol F4pada keyboard Anda. Aturan

    yang berlaku untuk Sel Absolut ini adalah sebagai berikut.

  • 5/20/2018 Materi Kkpi Excel

    21/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 17

    Jika tanda $ terletak didepan nama kolom, maka saat digunakan Auto Fill, nama

    kolom tersebut akan terkunci namun nomor barisnya terus bertambah. Contohnya

    $A1.

    Jika tanda $ terletak didepan nomor baris, maka saat digunakan Auto Fill, nomor

    baris tersebut akan terkunci namun nama kolomnya terus bertambah. Contohnya

    A$1. Jika tanda $ terletak didepan nama kolom dan nomor baris, maka saat digunakan

    Auto Fill, alamat sel tidak akan berubah alias selalu tetap. Contohnya $A$1.

    Agar lebih jelas dalam memahami Sel Absolut ini, perhatikan tabel berikut ini. Misalkan

    saja Anda akan menghitung persentase dari jumlah Data 1dan Data 2. Maka formula yang

    digunakan pada D3adalah D3=(B3+C3)*B8

    Namun jika digunakan Auto Fill untuk mengisi sel selanjutnya yaitu sel D4 dan D5, maka

    hasilnya akan salah, karena formulanya akan menjadi:

    D4=(B4+C4)*B9

    D5=(B5+C5)*B10

    Sel B4, C4, B5, dan C5 sudah benar, yaitu merupakan penambahan alamat dari sel

    sebelumnya, namun sel B9 dan B10 jelas salah karena seharusnya sel tersebut tidak

    berubah yaitu alamatnya tetap B8yang berisikan nilai persentase. Pada sel B8inilah Anda

    bisa menggunakan Sel Absolut untuk mengunci alamat sel tersebut.

    Karena arah Auto Fill bergerak kebawah yang mengakibatkan penambahan nomor baris,

    maka Anda cukup menambahkan tanda $didepan nomor barisnya saja. Dengan demikian,

    formula pada D3yang seharusnya adalah: D3=(B3+C3)*B$8

    Jika digunakan Auto Fill, maka formula pada sel D4dan D5akan menjadi:

    D4=(B4+C4)*B$8

    D5=(B5+C5)*B$8

    Dan formula ini tentunya akan menghasilkan nilai yang benar.

  • 5/20/2018 Materi Kkpi Excel

    22/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 18

    Sebagai latihan sekaligus agar Anda lebih memahami cara kerja dan penggunaan Sel

    Absolut ini, Anda bisa melihat sebuah tabel perkalian yang umum dimiliki oleh anak

    sekolah dasar seperti berikut ini.

    Pada tabel perkalian tersebut, perhitungannya secara garis besar adalah data pada kolom B

    dikalikan dengan data pada baris 2. Sebagai contoh, pada sel isian pertama yaitu sel C3,

    formulanya adalah: C3=B3*C2

  • 5/20/2018 Materi Kkpi Excel

    23/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 19

    Karena perhitungan selalu menggunakan nama kolom yang sama yaitu kolom B serta

    nomor baris yang sama yaitu baris 2, maka Anda dapat menambahkan tanda $ didepan

    kolom B dan nomor baris 2 untuk membuat sel tersebut menjadi Absolut. Hingga

    formulanya menjadi seperti berikut ini: C3=$B3*C$2

    Berikutnya Anda tinggal memilih sel C3 tersebut lalu melakukan proses Auto Fill kearah

    kanan.

    Dalam kondisi sel C3:L3masih tersorot, lakukan Auto Fill sekali lagi kearah bawah.

  • 5/20/2018 Materi Kkpi Excel

    24/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 20

    Hasilnya Anda sudah mendapatkan sebuah tabel perkalian yang lengkap yang dikerjakan

    hanya dalam waktu singkat saja, tentunya dengan menggunakan bantuan Sel Absolut dan

    Auto Fill.

    5.

    Fungsi SUM

    Pada beberapa artikel terdahulu telah dibahas konsep-konsep dasar yang harus Anda

    ketahui tentang formula pada aplikasi Excel. Mulai artikel ini, akan dibahas beberapa fungsi

    dasar yang umum digunakan dalam formula Excel untuk membantu proses penghitungan

    data pada aktifitas pekerjaan sehari-hari.

    Fungsi yang pertama dan bisa dibilang merupakan salah satu fungsi yang paling banyak

    diketahui dan digunakan setiap pengguna aplikasi Excel adalah fungsi SUM. Fungsi SUM

    digunakan untuk menjumlahkan data dari beberapa sel. Aturan penulisannya sebagai

    berikut: =SUM(sel awal:sel akhir)

    Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan

    penulisan fungsi SUM juga bisa dituliskan sebagai berikut: =SUM(range)

    Sebagai contoh, pada tabel berikut ini akan dihitung nilai Total Penjualanpada sel C7untuk

    data bulan Januari hingga bulan April, yaitu data pada sel C3hingga C6.

    Sesuai dengan aturan penulisan formula yang telah dibahas pada artikel sebelumnya, maka

    cara penulisan fungsi SUM untuk menghitung nilai Total Penjualan tersebut adalah sebagai

    berikut:

    Pilih sel C7

    Ketikan tanda = Ketikan SUMatau gunakan fitur Intellisense

    Ketikan tanda (

    Klik sel C3lalu drag ke sel C6

    Ketikan tanda )

  • 5/20/2018 Materi Kkpi Excel

    25/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 21

    Tekan Enteruntuk melihat hasilnya

    Dan hasil penjumlahan akan ditampilkan pada sel C7seperti tampak pada gambar berikut

    ini.

    Dengan demikian, formula yang telah Anda gunakan untuk menghitung nilai Total tersebut

    adalah: C7=SUM(C3:C6)

    Untuk memeriksa hasil dari penulisan formula ini, cobalah untuk merubah data pada sel C3

    hingga C6 dan lihatlah apakah hasil perhitungan pada sel C7 telah berubah. Jika berubah,

    maka penulisan formula sudah sesuai.

    Penulisan range pada formula ini memang lebih mudah dilakukan dengan menggunakan klik

    dan drag mouse, namun untuk range yang terlalu jauh, tentunya Anda pun dapat

    mengetikan alamat range tersebut secara manual. Atau untuk kemudahan perhitungandikemudian hari.

    6.

    Fungsi AVERAGE

    Fungsi AVERAGE digunakan untuk menghitung nilai rata-rata dari sejumlah data pada

    beberapa sel. Aturan penulisannya sebagai berikut: =AVERAGE(sel awal:sel akhir)

    Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan

    penulisan fungsi AVERAGE juga bisa dituliskan sebagai berikut: =AVERAGE(range)

    Sebagai contoh, pada tabel berikut ini akan dihitung nilai rata-rata Penjualan per Tahunpada sel C7untuk data tahun 2009 hingga 2012, yaitu data pada sel C3hingga C6.

  • 5/20/2018 Materi Kkpi Excel

    26/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 22

    Maka formula yang digunakan adalah: C7=AVERAGE(C3:C6)

    Dan hasilnya akan ditampilkan pada sel C7seperti tampak pada gambar berikut ini.

    7.

    Fungsi COUNT

    Banyak pengguna aplikasi Excel yang menganggap fungsi COUNT ini sama dengan fungsi

    SUM, padahal kegunaannya jelas berbeda. Jika fungsi SUM digunakan untuk menghitung

    jumlah datamaka fungsi COUNTdigunakan untuk menghitung banyaknya pemilik data. Olehkarenanya berhati-hatilah saat Anda harus memutuskan fungsi mana yang akan Anda

    gunakan, apakah SUMatau COUNT.

    Aturan penulisan fungsi COUNT sebagai berikut: =COUNT(sel awal:sel akhir)

    Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan

    penulisan fungsi COUNTjuga bisa dituliskan sebagai berikut: =COUNT(range)

    Agar lebih jelas dalam memahami fungsi COUNTini juga agar tidak tertukar penggunaannya

    dengan fungsi SUM, perhatikan contoh berikut ini. Pada tabel yang ditanyakan adalah

    jumlah materi training yang ada dan bukan jumlah nilainya.

  • 5/20/2018 Materi Kkpi Excel

    27/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 23

    Maka formula yang digunakan adalah: C7=COUNT(C3:C6)

    Dan hasilnya akan ditampilkan pada sel C7seperti tampak pada gambar berikut ini.

    Perhatikan bahwa walaupun yang ditanyakan adalah jumlah pemilik data, dalam hal ini

    Materi Training, namun alamat range yang dimasukan dalam formula tetap range untuk

    datanya yaitu C3:C6dan bukan B3:B6.

    8.

    Fungsi MAX dan MIN

    Fungsi MAX digunakan untuk mengetahui nilai maksimum dari sejumlah data dan

    fungsi MIN untuk mengetahui nilai minimum juga dari sejumlah data. Aturan penulisan

    fungsi MAX dan MIN sama saja, yaitu sebagai berikut:

    =MAX(sel awal:sel akhir) atau =MIN(sel awal:sel akhir)

    Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan

    penulisan fungsi MAX / MIN juga bisa dituliskan sebagai berikut:

    =MAX(range) atau =MIN(range)

    Sebagai contoh, pada tabel berikut ini ditanyakan berapa nilai maksimum dan minimum

    untuk semua nilai yang ada, yaitu data nilai pada sel C3hingga C6.

  • 5/20/2018 Materi Kkpi Excel

    28/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 24

    Maka formula yang digunakan untuk melihat nilai maksimum adalah: C7=MAX(C3:C6)

    Dan formula yang digunakan untuk melihat nilai minimum adalah: C8=MIN(C3:C6)

    Hasilnya akan ditampilkan pada sel C7dan C8seperti tampak pada gambar berikut ini.

    9.

    Fungsi COUNTIF

    Fungsi dalam aplikasi Excel dikelompokan berdasarkan beberapa kategori tertentu, salah

    satunya yaitu kategori fungsi Logika (Logic Functions).

    Fungsi yang terdapat dalam kategori ini digunakan untuk menghitung atau menganalisa

    data yang bersandar pada perbandingan suatu kondisi atau kriteria tertentu hingga fungsi-

    fungsi yang termasuk dalam kategori ini lazim disebut juga dengan Condit ional Functions.

    Ada banyak fungsi yang terdapat pada kategori ini dimana hampir sebagian besar nama

    fungsinya memiliki akhiran IF yang jika diartikan secara harfiah adalah "jika".

    Fungsi COUNTIF termasuk kedalam kategori Logic Functions tersebut. Fungsi ini pada

    dasarnya memiliki kegunaan yang sama dengan fungsi COUNT yaitu untuk menghitung

    banyaknya data. Namun pada fungsi COUNTIF, banyaknya data ini hanya akan dihitung jika

    sesuai dengan kriteria atau kondisi tertentu. Aturan penulisannya sebagai berikut:

    =COUNTIF(Range Data;Kondisi)

  • 5/20/2018 Materi Kkpi Excel

    29/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 25

    Dari aturan penulisan tersebut dapat Anda lihat bahwa fungsi COUNTIF ini terdiri dari 2

    argumen dimana tiap-tiap argumen dipisahkan dengan tanda pemisah tertentu. Hal ini

    berbeda dengan beberapa fungsi yang telah dibahas sebelumnya yang masih menggunakan

    argumen tunggal yang sangat sederhana.

    Agar lebih jelas dalam memahami penggunaan fungsi COUNTIF ini, perhatikan contoh tabel

    berikut ini. Tiap-tiap siswa memiliki status kelulusan yang berbeda dimana ada yang LULUS

    dan ada yang GAGAL. Sementara yang ditanyakan adalah berapakah jumlah siswa yang

    LULUS?

    Berdasarkan aturan penulisan COUNTIF, status LULUSmerupakan kondisi yang dikehendaki,

    dan range yang memuat status LULUS tersebut merupakan range data yang akan dihitung

    oleh Excel yaitu sel C3hingga C6. Dengan demikian formula untuk menghitung banyaknya

    siswa yang lulus tersebut adalah sebagai berikut:

    C7=COUNTIF(C3:C6;LULUS)

    Namun berdasarkan aturan penulisan formula, LULUS adalah variabel yang tidak dikenali

    oleh Excel sehingga formula diatas jelas akan menghasilkan pesan kesalahan jika dijalankan.

    Agar kata LULUS tersebut dikenali, maka kita berikan tanda kutip diantaranya, sehingga

    formulanya menjadi:

    C7=COUNTIF(C3:C6;"LULUS")

    Dan sekarang formula ini akan menghasilkan nilai yang benar seperti tampak pada gambar

    berikut ini.

  • 5/20/2018 Materi Kkpi Excel

    30/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 26

    Pelajari baik-baik penggunaan formula dengan multi argumen ini karena dalam penerapan

    Excel lebih lanjut Anda akan banyak menjumpainya.

    10.Fungsi SUMIF

    Selain COUNTIF, fungsi logika lainnya yang umum digunakan adalah fungsi SUMIF. Sesuai

    dengan fungsi dasarnya yaitu SUM, fungsi SUMIF juga digunakan untuk menjumlahkan data

    pada range tertentu, hanya saja data yang akan dijumlahkan mengacu pada kondisi

    tertentu.

    Aturan penulisan fungsi SUMIF ini sebagai berikut:

    =SUMIF(range kondisi;kondisi;range data)

    Agar lebih jelas perhatikan contoh pada tabel berikut ini. Yang ditanyakan pada tabel

    tersebut bukanlah total laba keseluruhan melainkan total laba hanya untuk barang yang

    bagus saja serta total laba hanya untuk barang yang rusak saja.

    Pada kasus ini, kata BAGUSdan RUSAKmerupakan sebuah kondisi yang berada pada range

    C3 hingga C6. Sementara data yang akan dijumlahkan berada pada range D3 hingga D6.

    Dengan demikian, sesuai dengan aturan penulisan SUMIF, maka formula untuk menghitung

    Total Laba Barang Bagusdi sel D9adalah sebagai berikut:

    D9=SUMIF(C3:C6;BAGUS;D3:D6)

    Sesuai dengan aturan penulisan formula, kata BAGUS bukan merupakan variabel yang

    dikenali Excel, maka kita perlu menambahkan tanda kutip untuk mengapit kata tersebut,

    hingga formulanya akan menjadi:

  • 5/20/2018 Materi Kkpi Excel

    31/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 27

    D9=SUMIF(C3:C6;"BAGUS";D3:D6)

    Lalu untuk menghitung Total Laba Barang Rusak di sel D10, tentunya Anda tinggal

    mengganti kata BAGUS menjadi RUSAK, hingga formulanya menjadi:

    D10=SUMIF(C3:C6;"RUSAK";D3:D6)

    Dan contoh hasilnya dapat dilihat pada gambar berikut ini.

    11.

    Penggabungan Fungsi

    Pada artikel sebelumnya telah dibahas contoh beberapa fungsi yang umum digunakan.

    Banyak para pengguna aplikasi Excel yang mengira bahwa dalam satu sel dan satu formula

    hanya boleh digunakan satu fungsi saja.

    Opini ini tentunya salah besar, karena dalam penggunaannya, Anda dapat menggabungkan

    beberapa fungsi sekaligus untuk menghitung nilai yang dicari. Dan justru karena adanya

    penggabungan beberapa fungsi ini maka aplikasi Excel menjadi semakin powerfull namuntetap efisien. Salah satu hal yang mutlak harus diperhatikan dalam penggabungan beberapa

    fungsi sekaligus dalam satu formula adalah penggunaan tanda kurung serta tanda pemisah

    argumen.

    Tanda kurung ini berfungsi untuk memisahkan antara fungsi yang satu dengan fungsi yang

    lainnya. Kesalahan penempatan tanda kurung ini tentunya akan mengakibatkan formula

    menjadi error hingga Excel akan menampilkan pesan kesalahan. Jika Anda mengalami

    kesulitan dalam menempatkan tanda kurung ini, misalnya saja karena terlalu banyaknya

    fungsi yang akan digabungkan, akan lebih baik jika Anda menuliskannya dulu di kertas dalam

    bentuk coretan kasar. Gunakan saja prinsip matematika dasar dimana tiap-tiap perhitungan

    dalam suatu perhitungan yang kompleks selalu dipisahkan dengan tanda kurung.

  • 5/20/2018 Materi Kkpi Excel

    32/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 28

    Mulai Excel 2007, Microsoft juga telah menambahkan fitur berupa warna yang berbeda

    untuk tiap-tiap tanda kurung yang saling berhubungan. Fitur ini tentunya bisa Anda gunakan

    juga sebagai bantuan.

    Berikut ini beberapa contoh untuk penggabungan fungsi ini.

    a.

    Contoh 1: Menggabungkan Fungsi SUM dengan Perkalian

    Pada tabel berikut ini Anda diminta untuk menghitung Total Labapada sel C10.

    Secara matematika, Total Laba tentunya diperoleh dengan menambahkan semua unit

    yang terjual kemudian dikalikan dengan Laba per Unit. Namun pada tabel tersebut tidak

    terdapat sel khusus untuk menghitung jumlah keseluruhan Unit Terjual. Dengan

    demikian solusinya adalah Anda harus menggabungkan fungsi SUM untuk menghitung

    jumlah Unit Terjual yang kemudian dikalikan dengan Laba per Unit. Formulanyasebagai berikut ini C10=SUM(C3:C6)*C8

    Atau agar sesuai dengan konsep dasar matematika mengingat ada 2 perhitungan yang

    digunakan maka Anda juga dapat menuliskan formulanya sebagai berikut:

    C10=(SUM(C3:C6))*C8

    Contoh hasil akhirnya dapat dilihat pada gambar berikut ini.

  • 5/20/2018 Materi Kkpi Excel

    33/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 29

    b. Contoh 2: Menggunakan 2 Fungsi SUMIF dalam 1 Formula

    Pada tabel berikut ini Anda diminta untuk menghitung stok Barang Baguspada sel D9

    serta stok Barang Rusak pada sel D10. Data stok barang tersebut diambil dari tabel

    Laporan Stok Gudang.

    Jika data hanya diambil dari satu gudang saja, misal Gudang 1 maka dengan mudah

    Anda dapat menghitung stok yang diminta yaitu dengan menggunakan formula SUMIF

    yang telah Anda pelajari sebelumnya. Namun dalam kasus ini gudangnya ada 2, maka

    solusinya Anda tinggal gabungkan saja formula SUMIF untuk-tiap gudang dalam 1

    formula.

    Formula untuk menghitung stok barang bagus dari Gudang 1adalah:

    =SUMIF(C4:C6;"BAGUS";D4:D6)

    Dan formula untuk menghitung stok barang bagus dari Gudang 2adalah:

    =SUMIF(C4:C6;"BAGUS";E4:E6)

    Dengan demikian formula akhirnya pada sel D9adalah sebagai berikut:

    D9=SUMIF(C4:C6;"BAGUS";D4:D6)+SUMIF(C4:C6;"BAGUS";E4:E6)

    Untuk menghitung stok barang rusak tentunya Anda bisa menggunakan fitur Auto Fill.

    Dan contoh hasil akhirnya bisa Anda lihat pada gambar berikut ini.

  • 5/20/2018 Materi Kkpi Excel

    34/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 30

    12.

    Fungsi IF

    Dari semua fungsi logika yang terdapat dalam aplikasi Excel, fungsi IFadalah fungsi logika

    yang paling banyak diaplikasikan, terutama dalam penerapan formula lanjutan yang

    melibatkan banyak data dan keputusan bercabang. Kegunaan dari fungsi IF ini sebetulnya

    sangat sederhana, fungsi ini akan mengambil suatu kondisi tertentu kemudian menentukannilai TRUE atau FALSE. Nilai TRUE adalah nilai dimana kondisi tersebut terpenuhi dan nilai

    FALSE adalah nilai untuk kondisi yang tidak terpenuhi.

    Aturan penulisan fungsi IF ini adalah sebagai berikut, =IF(kondisi;nilai_TRUE;nilai_FALSE)

    kondisimerupakan kriteria acuan yang dijadikan pembanding

    nilai_TRUEmerupakan nilai untuk kondisi yang terpenuhi

    nilai_FALSEmerupakan nilai untuk kondisi yang tidak terpenuhi

    Agar lebih jelas dalam memahami penggunaan fungsi IF ini, perhatikan contoh pada tabel

    berikut ini.

    Pada tabel tersebut kolom Statusakan diisi dengan kata LULUSatau GAGAL. Namun untuk

    mengisinya ada kondisi yang harus dipenuhi, misalkan saja siswa akan lulus jika nilainya

    diatas 60. Dari pernyataan ini maka didapat beberapa point, yaitu:

    Kondisi: Nilai diatas 60. Nilai ini berada pada sel C3 hingga C6. Dan karena ada nilai

    acuan yaitu 60 sebagai pembanding maka Anda harus menggunakan Operator

    Pembandinguntuk melengkapi argumen pada kondisi ini. Operator pembanding yang

    digunakan tentunya tanda lebih besar ">".

    Kondisi 1: Jika nilai diatas 60, maka siswa Lulus. Kondisi ini merupakan nilai untuk

    kondisi TRUEyaitu kondisi yang terpenuhi Kondisi 2: Jika nilai dibawah 60, artinya tidak diatas 60, maka siswa Gagal. Kondisi ini

    adalah nilai untuk kondisi FALSEyaitu kondisi yang tidak terpenuhi.

    Berikutnya Anda tinggal menerapkan point-point tersebut pada fungsi IF disesuaikan dengan

    aturan penulisannya. Dan karena data tiap-tiap siswa bervariasi, maka tabel akan diisi untuk

    siswa yang pertama dulu yaitu Siti Nurhaliza. Formulanya sebagai berikut:

    D3=IF(C3>60;"LULUS";"GAGAL")

    Arti dari formula tersebut jika diterjemahkan dalam kalimat biasa adalah "Jika nilai dalam sel

    C3 lebih besar dari 60, maka tuliskan kata LULUS pada sel D3. Namun jika nilainya ternyatatidak lebih besar dari 60 maka tuliskan kata GAGAL pada sel D3".

  • 5/20/2018 Materi Kkpi Excel

    35/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 31

    Contoh hasilnya dapat Anda lihat pada gambar berikut ini:

    Untuk memeriksa apakah formula yang dituliskan sudah benar, cobalah untuk mengganti

    nilai pada sel C3, misalnya menjadi 40, lalu perhatikan hasilnya. Jika tertulis GAGAL maka

    artinya formula yang digunakan sudah benar.

    Lalu bagaimana mengisikan baris-baris selanjutnya? Menuliskan formula secara manual satu

    persatu jelas bisa, namun hal ini akan sangat menyita waktu. Cara cepatnya, gunakan saja

    fitur Auto Fillyang telah dibahas pada artikel sebelumnya.

    Dan hasilnya semua kolom Status kini telah terisi dengan nilai yang benar.

  • 5/20/2018 Materi Kkpi Excel

    36/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 32

    13.

    Fungsi IF Bercabang

    Fungsi logika IFyang telah dibahas pada artikel sebelumnya adalah salah satu fungsi yang

    relatif banyak digunakan, terutama di lingkungan bisnis / perusahaan. Namun fungsi IF ini

    memiliki keterbatasan karena kondisi yang menjadi acuan hanya satu saja. Bagaimana jika

    data Anda menggunakan beberapa kondisi sekaligus?

    Seperti yang telah dibahas pada artikel Penggabungan Fungsi, penerapan fungsi pada

    aplikasi Excel bisa digabungkan untuk memenuhi satu tujuan atau hasil tertentu. Dan dalam

    kasus ini, dimana data memiliki beberapa kondisi yang harus dibandingkan, maka fungsi

    yang digunakan tetap fungsi IF, namun bukan hanya satu IF (IF tunggal) melainkan beberapa

    IF sekaligus. Dalam aplikasi Excel, penggunaan beberapa IF (Multiple IF) dalam satu formula

    ini lazim disebut dengan fungsi IF Bercabangatau Nested IF.

    Pada dasarnya aturan penulisan fungsi IF bercabang ini sama saja dengan IF tunggal, yaitu:

    =IF(kondisi;nilai_TRUE;nilai_FALSE)

    kondisimerupakan kriteria acuan yang dijadikan pembanding

    nilai_TRUEmerupakan nilai untuk kondisi yang terpenuhi

    nilai_FALSEmerupakan nilai untuk kondisi yang tidak terpenuhi

    Karena kondisi-nya lebih dari satu, maka untuk nilai_FALSE digunakan kembali fungsi IF

    berikutnya. Misalnya jika kondisi-nya ada 2, maka aturan penulisannya menjadi:

    =IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;nilai_FALSE)))

    Jika dijabarkan, maka formula tersebut berarti:

    Lihat kondisi_1, jika terpenuhi maka gunakan nilai_TRUE1.

    Jika kondisi_1tidak terpenuhi, maka lihat kondisi_2.

    Jika kondisi_2terpenuhi, maka gunakan nilai_TRUE2.

    Jika kondisi_2tidak terpenuhi, maka gunakan nilai_FALSE.

    Fungsi IF bercabang ini tentunya tidak hanya untuk 2 kondisi saja, misalnya jika ada 3 kondisi

    maka aturan penulisannya menjadi:

    =IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;(IF(kondisi_3;nilai_TRUE3;nilai_FALSE

    )))))

    Atau jika kondisinya ada 4 maka aturan penulisannya menjadi:

    =IF(kondisi_1;nilai_TRUE1;(IF(kondisi_2;nilai_TRUE2;(IF(kondisi_3;nilai_TRUE3;(IF(kondisi_

    4;nilai_TRUE4;nilai_FALSE)))))))

    Dan demikian seterusnya.

  • 5/20/2018 Materi Kkpi Excel

    37/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 33

    Agar penggunaan fungsi IF bercabang ini lebih jelas, perhatikan contoh tabel berikut ini.

    Misalkan saja pada tabel daftar barang tersebut Anda diminta untuk mengisi nilai diskon

    pada sel C3 hingga C6, dengan kondisi bahwa produk Mouse dan produk Keyboard akan

    mendapatkan diskon sebesar 5%. Diluar produk tersebut tidak diberikan diskon.

    Analisa dari kasus tersebut adalah ada 2 kondisi yang menjadi acuan, yaitu Mouse diskon 5%

    serta Keyboard diskon 5%. Kedua kondisi ini tentunya harus dimasukan kedalam formula IF

    satu persatu, boleh diawali dengan kondisi untuk Mouse ataupun untuk Keyboard. Sebagai

    contoh diambil kondisi untuk Mouse terlebih dahulu. Untuk memperjelas pembahasan,

    berikut ini dituliskan kembali aturan penulisan fungsi IF.

    C3=IF(kondisi;nilai_TRUE;nilai_FALSE)

    Kondisi pertama adalah Mouse dengan diskon 5%. Mouse serta produk-produk lainnya

    berada pada kolom 3 dan diawali dengan alamat sel B3. Maka formulanya akan menjadi:

    C3=IF(B3="Mouse";nilai_TRUE;nilai_FALSE)

    Nilai jika B3adalah Mousemerupakan nilai_TRUEyaitu 5%, maka formulanya menjadi:

    C3=IF(B3="Mouse";5%;nilai_FALSE)

    Nilai_FALSEdigunakan jika ternyata isi sel bukan Mouse, artinya produk lainnya. Dan karena

    ada satu kondisi lagi yaitu Keyboard maka kita masukan kondisi tersebut sebagai kondisi

    pada fungsi IF berikutnya:

    C3=IF(B3="Mouse";5%;(IF(B3="Keyboard";nilai_TRUE2;nilai_FALSE)))

    Jika B3 adalah Keyboard maka nilai_TRUE2 adalah 5% karena kondisinya terpenuhi.

    Sehingga formulanya akan menjadi:

    C3=IF(B3="Mouse";5%;(IF(B3="Keyboard";5%;nilai_FALSE)))

    Jika ternyata B3tidak berisikan Mouseataupun Keyboard, maka tidak diberikan diskon sama

    sekali atau diskon sama dengan 0. Nilai ini tentunya merupakan nilai_FALSEuntuk semua

    kondisi yang telah dibandingkan. Dengan demikian formula akhirnya adalah sebagai berikut:

    C3=IF(B3="Mouse";5%;(IF(B3="Keyboard";5%;0)))

  • 5/20/2018 Materi Kkpi Excel

    38/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 34

    Dan formula tersebut jika dijalankan pada sel C3 tentunya akan menghasilkan nilai yang

    benar seperti tampak pada gambar berikut ini.

    Dan untuk mengisi sel-sel berikutnya seperti biasa Anda dapat menggunakan Auto Fill.

    Pelajari baik-baik penggunaan formula IF bercabang ini karena dalam penerapan aplikasi

    Excel lanjutan Anda akan banyak menggunakan formula ini. Hati-hati dengan penggunaan

    tanda kurung karena semakin banyak IF yang Anda gunakan maka Anda akan menggunakan

    tanda kurung yang banyak juga.

    14.

    Fungsi OR

    Fungsi ORjuga termasuk kedalam fungsi logika. Kegunaannya adalah untuk membandingkan

    2 atau lebih kondisi dan menghasilkan nilai TRUE atau FALSE. Nilai TRUEakan digunakan jika

    salah satu kondisi atau semua kondisi terpenuhi. Sedangkan nilai FALSEakan digunakan jika

    semua kondisi tidak terpenuhi.

    Agar lebih jelas, tabel berikut ini memperlihatkan contoh nilai OR yang dihasilkan untuk 2

    buah kondisi:

  • 5/20/2018 Materi Kkpi Excel

    39/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 35

    Dan tabel berikut ini memperlihatkan contoh nilai OR yang dihasilkan untuk 3 buah kondisi:

    Aturan penulisan fungsi OR pada dasarnya sangat sederhana yaitu hanya memuat

    kondisinya saja sebagai berikut:

    =OR(kondisi_1;kondisi_2;kondisi_3;......;kondisi_n)

    Jika formula tersebut dijalankan, maka nilai yang akan dihasilkan adalah TRUE atau FALSE.

    Agar TRUE atau FALSE tersebut ada nilainya, maka fungsi OR umum digabungkan

    penggunaannya dengan IFhingga fungsi ini juga kemudian lazim disebut dengan fungsi IF-

    OR. Aturan penulisannya adalah sebagai berikut:

    =IF(OR(kondisi_1;kondisi_2);nilai_TRUE;nilai_FALSE)

    Dalam penerapannya, fungsi OR dapat Anda gunakan untuk menyederhanakan fungsi IF

    Bercabang. Sebagai contoh pada artikel IF Bercabang yang telah dibahas sebelumnya,

    terdapat tabel diskon sebagai berikut, dimana ada kondisi untuk produk Mousedan produkKeyboardyang akan mendapatkan diskon sebesar 5%.

    Kondisi yang diinginkan tersebut jika dianalogikan dalam kalimat akan menjadi "Jika produk

    Mouse ATAU Keyboard, maka berikan diskon sebesar 5%, namun jika bukan maka tidak ada

    diskon". Dari kalimat ini didapatkan beberapa point penting, yaitu:

    kondisi_1: B3=Mouse

    kondisi_2: B3=Keyboard

    nilai_TRUE: 5%

    nilai_FALSE: 0

  • 5/20/2018 Materi Kkpi Excel

    40/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 36

    Dan point-point ini tentunya tinggal Anda masukan kedalam fungsi IF-OR. Diawali dengan

    aturan penulisannya terlebih dahulu:

    C3=IF(OR(kondisi_1;kondisi_2);nilai_TRUE;nilai_FALSE)

    Kemudian tinggal Anda ganti tiap-tiap argumen dengan nilai yang sesuai. Dengan demikianformula akhirnya adalah sebagai berikut:

    C3=IF(OR(B3="Mouse";B3="Keyboard");5%;0)

    Dan formula ini jika Anda jalankan tentunya akan menghasilkan nilai yang benar seperti

    tampak pada gambar berikut ini.

    Kemudian seperti biasanya untuk mengisi sel-sel berikutnya Anda tinggal mengunakan Auto

    Fill.

    Dari contoh tersebut, Anda dapat melihat bahwa fungsi IF-OR bisa Anda gunakan untuk

    menyederhanakan penggunaan formula IF Bercabang.

    15. Fungsi AND

    Fungsi AND adalah kebalikan dari fungsi OR. Kegunaannya sama yaitu untuk

    membandingkan 2 atau lebih kondisi dan menghasilkan nilai TRUE atau FALSE. Perbedaan

    AND dengan OR adalah nilai untuk kondisi TRUE dan FALSE-nya. Pada fungsi AND, nilai TRUE

    akan digunakan jika semua kondisi terpenuhi, dan nilai FALSEakan digunakan jika salah satuatau semua kondisi tidak terpenuhi.

  • 5/20/2018 Materi Kkpi Excel

    41/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 37

    Agar lebih jelas, tabel berikut ini memperlihatkan contoh nilai AND yang dihasilkan untuk 2

    buah kondisi:

    Dan tabel berikut ini memperlihatkan contoh nilai AND yang dihasilkan untuk 3 buah

    kondisi:

    Aturan penulisan fungsi AND juga sama dengan OR yaitu hanya memuat kondisinya saja, dan

    agar kondisi TRUE dan FALSE ada nilainya maka fungsi AND juga umum digabungkan

    penggunaannya dengan IF hingga kemudian lazim disebut dengan fungsi IF-AND dengan

    aturan penulisan sebagai berikut:

    =IF(AND(kondisi_1;kondisi_2);nilai_TRUE;nilai_FALSE)

    Contoh penggunaan fungsi AND ini dapat dilihat pada tabel berikut.

    Misalkan pada kolom Statusakan diisi dengan kata LULUSatau GAGAL. Status LULUS akan

    didapatkan Siswa jika kedua nilai yaitu nilai Bahasa dan nilai Matematika diatas 60. Jika

    salah satu nilai saja dibawah 60 maka siswa dinyatakan gagal.

    Kondisi yang diinginkan tersebut jika dianalogikan dalam kalimat akan menjadi "Jika nilai

    Bahasa DAN Matematika diatas 60 maka siswa akan Lulus. Namun jika tidak maka siswa

    akan Gagal". Dari kalimat ini didapatkan beberapa point penting, yaitu:

  • 5/20/2018 Materi Kkpi Excel

    42/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 38

    kondisi_1: C3>60

    kondisi_2: D3>60

    nilai_TRUE: LULUS

    nilai_FALSE: GAGAL

    Dan point-point ini tentunya tinggal Anda masukan kedalam fungsi IF-AND. Diawali denganaturan penulisannya terlebih dahulu:

    E3=IF(AND(kondisi_1;kondisi_2);nilai_TRUE;nilai_FALSE)

    Berikutnya Anda tinggal mengganti argumen-argumen tersebut dengan nilai yang sesuai.

    Dengan demikian formula akhirnya adalah sebagai berikut:

    E3=IF(AND(C3>60;D3>60);"LULUS";"GAGAL")

    Formula ini jika Anda jalankan tentunya akan menghasilkan nilai yang benar seperti tampak

    pada gambar berikut ini.

    Kemudian seperti biasanya untuk mengisi sel-sel berikutnya Anda tinggal mengunakan Auto

    Fill.

    16.

    Fungsi Tanggal

    Perhitungan dalam aplikasi Excel adakalanya melibatkan sistem penanggalan, misalnya saja

    untuk menghitung lama waktu cicilan atau tanggal jatuh tempo. Untuk mempermudah

    perhitungan tersebut maka Anda dapat menggunakan beberapa Fungsi Tanggal yang

    disediakan oleh Excel.

  • 5/20/2018 Materi Kkpi Excel

    43/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 39

    Beberapa fungsi tanggal yang umum digunakan berikut aturan penulisannya dapat dilihat

    pada tabel berikut ini.

    Contoh penerapan fungsi penanggalan ini misalnya saja untuk menghitung tanggal jatuh

    tempo seperti tabel berikut ini.

    Karena yang ditanyakan adalah tanggal Jatuh Tempo, maka fungsi yang digunakan adalah

    fungsi DATE dimana fungsi DATE ini bisa digunakan untuk memasukan format tanggal

    tertentu. Dari tabel diatas dapat dilihat bahwa aturan penulisan fungsi DATE ini adalah

    sebagai berikut D3=DATE(YEAR;MONTH;DAY)

    Tahun, Bulan, dan Tanggal diambil dari Tanggal Kredit yaitu sel B3. Hingga formulanya

    menjadi D3=DATE(YEAR(B3);MONTH(B3);DAY(B3))

    Karena lama cicilan adalah 10 bulan yang terletak di sel C3, maka Anda tinggal

    menambahkan MONTH(B3) dengan C3. Dan formula akhirnya adalah sebagai berikut

    D3=DATE(YEAR(B3);MONTH(B3)+C3;DAY(B3))

    Contoh penerapan lain untuk fungsi tanggal ini misalnya untuk menghitung durasi

    berdasarkan tanggal awal dan akhir seperti tabel berikut ini.

  • 5/20/2018 Materi Kkpi Excel

    44/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 40

    Yang ditanyakan dari tabel tersebut adalah Lama Project dalam satuan Bulan. Untuk

    menghitungnya maka Anda tinggal mengurangi data pada sel C3 dengan sel B3. Atau jika

    dituliskan formula dasarnya sebagai berikut D3=C3-B3

    Karena yang ditanyakan Bulan, maka data yang diambil jelas hanya data Tahundan Bulan

    saja. Kemudian Excel juga memiliki fungsi YEARdan MONTHuntuk mengambil data Tahun

    dan Bulan dari sel tertentu (lihat tabel aturan penulisan diatas), maka formulanya menjadi:

    D3=(YEAR(C3)-YEAR(B3))+(MONTH(C3)-MONTH(B3))

    Kemudian karena dalam 1 tahun ada 12 bulan, maka tentunya hasil pengurangan data tahun

    harus dikalikan dengan 12. Hingga formula akhirnya adalah sebagai berikut:

    D3=(YEAR(C3)-YEAR(B3))*12+(MONTH(C3)-MONTH(B3))

    Saat menggunakan fungsi-fungsi penanggalan ini, perhatikan selalu format sel yang

    digunakan. Hasil yang diharapkan akan meleset jika format sel yang Anda gunakan tidak

    sesuai.

    17.

    Fungsi Waktu

    Selain Fungsi Tanggal, Excel juga menyediakan beragam fungsi untuk menghitung waktu

    atau jam. Beberapa Fungsi Waktuyang umum digunakan berikut aturan penulisannya dapat

    dilihat pada tabel berikut ini.

  • 5/20/2018 Materi Kkpi Excel

    45/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 41

    Cara menggunakan fungsi waktu tersebut sama saja dengan fungsi tanggal yang telah

    dibahas sebelumnya, hanya saja yang berbeda adalah satuannya. Jika fungsi tanggal

    menggunakan 3 satuan utama YEAR; MONTH; dan DAY, maka fungsi waktu menggunakan 3

    satuan utama HOUR; MINUTE; dan SECOND.

    Kemudian jika Anda akan menghitung total waktu, baik dalam bentuk jam, menit, ataupundetik maka total waktu yang didapat dari hasil perhitungan harus dikalikan dengan bilangan

    yang sesuai dengan satuannya. Aturannya bilangan pengali ini adalah sebagai berikut:

    Jam: 24

    Menit: 1440

    Detik: 86400

    Agar lebih jelas, perhatikan contoh tabel berikut ini.

    Yang ditanyakan dari tabel tersebut adalah Total Jam, Total Menit, serta Total Detik. Untuk

    menghitungnya sangatlah mudah. Anda tinggal mengurangi data Jam Keluar pada sel C3dengan data Jam Masuk pada sel B3 dan kemudian mengalikannya denan bilangan yang

    sesuai.

    Misalnya untuk menghitung Total Jam, maka formulanya sebagai berikut: C5=(C3-B3)*24

    Kemudian untuk menghitung Total Menit Anda tinggal mengganti bilangan pengalinya

    dengan 1440 dan untuk menghitung Total Detik maka bilangan pengali yang digunakan

    adalah 86400. Berikut ini formula akhirnya.

    C6=(C3-B3)*1440

    C7=(C3-B3)*86400

  • 5/20/2018 Materi Kkpi Excel

    46/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 42

    Sama halnya dengan fungsi penanggalan ini, perhatikan selalu format sel yang digunakan.

    Hasil yang diharapkan akan meleset jika format sel yang Anda gunakan tidak sesuai.

    18.

    Fungsi Pembulatan

    Untuk menyederhanakan hasil perhitungan, Excel juga menyediakan Fungsi Pembulatanyang bisa Anda gunakan untuk membulatkan angka tertentu berdasarkan kriteria

    pembulatan yang bisa Anda pilih sesuai kebutuhan.

    Ada 4 varian fungsi pembulatan yang umum digunakan, yaitu INT, ROUND, ROUNDUP, dan

    ROUNDDOWN. Perbedaan fungsi-fungsi tersebut berikut aturan penulisannya bisa dilihat

    pada tabel berikut ini.

    Pada aturan penulisan tersebut, angkamerupakan angka yang akan Anda bulatkan dan digit

    mengatur jumlah bilangan dibelakang koma. Agar lebih jelas dalam memahami fungsi

    pembulatan ini, misalkan saja Anda hendak membulatkan angka 8,762.

    Jika Anda menggunakan fungsi INTmaka formulanya adalah =INT(8,762)

    Hasil dari formula tersebut adalah 8karena INT akan membulatkan suatu angka kebawah

    sekaligus menghilangkan angka dibelakang koma.

    Jika Anda menggunakan fungsi ROUND dan menginginkan ada 2 angka dibelakang koma,

    maka formulanya adalah =ROUND(8,762;2)

    Hasilnya adalah 8,76 karena fungsi ROUND ini akan membulatkan angka sesuai kaidah

    matematika.

    Jika Anda menggunakan fungsi ROUNDUPdan menginginkan ada 2angka dibelakang koma,

    maka formulanya adalah =ROUNDUP(8,762;2)

    Hasilnya adalah 8,77karena fungsi ROUNDUPini akan membulatkan angka keatas.

    Jika Anda menggunakan fungsi ROUNDDOWN dan menginginkan ada 2 angka dibelakang

    koma, maka formulanya adalah =ROUNDDOWN(8,762;2)

    Hasilnya adalah 8,76karena fungsi ROUNDDOWNini akan membulatkan angka kebawah.

    Dalam penerapannya, tentu saja angka yang hendak dibulatkan tidak selalu berbentuk angka

    melainkan bisa merujuk pada data angka di sel tertentu ataupun hasil dari suatu

    perhitungan. Sebagai contoh pada tabel berikut ini, fungsi ROUNDDOWNdigunakan untuk

    membulatkan hasil penjumlahan yang didapat dari formula SUM.

  • 5/20/2018 Materi Kkpi Excel

    47/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 43

    19.

    Fungsi Teks

    Formula pada aplikasi Excel tidak hanya diperuntukan bagi data berupa angka, karena Excel

    menyediakan juga beberapa fungsi untuk mengolah data berupa teks, diantaranya yang

    sering digunakan adalah fungsi LEFT; MID; dan RIGHT. Ketiga fungsi tersebut memiliki

    kegunaan yang sama yaitu untuk mengambil beberapa karakter dari suatu teks. Sementara

    perbedaannya terletak pada posisi atau arah pengambilan karakternya. Penjelasan serta

    aturan penulisan untuk ketiga fungsi tersebut adalah sebagai berikut.

    Fungsi LEFT akan mengambil sejumlah karakter dari arah kiri. Jumlah karakter yang akan

    diambil ditentukan pada argumen jumlah_karakter. Aturan penulisan selengkapnya adalah

    sebagai berikut:

    =LEFT(teks;jumlah_karakter)

    Sebagai contoh jika pada sel B2Anda ketikan formula berikut ini:

    =LEFT("KOMPUTER";3)

    Maka hasilnya sel B2tersebut akan menampilkan kata KOM, yaitu 3 karakter pertama dari

    arah kiri pada kata KOMPUTER seperti tampak pada gambar berikut ini.

    Fungsi MID akan mengambil sejumlah karakter dari arah kiri. Namun awal pengambilan

    karakter tersebut bisa Anda tentukan sendiri pada argumen awal. Dan jumlah karakter yang

    akan diambil tetap ditentukan pada argumen jumlah_karakter. Aturan penulisan

    selengkapnya adalah sebagai berikut:

    =MID(teks;awal;jumlah_karakter)

  • 5/20/2018 Materi Kkpi Excel

    48/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 44

    Sebagai contoh jika pada sel B2Anda ketikan formula berikut ini:

    =MID("KOMPUTER";4;2)

    Maka hasilnya sel B2 tersebut akan menampilkan kata PU, yaitu 2 karakter yang dihitung

    mulai karakter ke-4 pada kata KOMPUTER seperti tampak pada gambar berikut ini.

    Fungsi RIGHTakan mengambil sejumlah karakter dari arah kanan. Jumlah karakter yang akan

    diambil ditentukan pada argumen jumlah_karakter. Aturan penulisan selengkapnya adalah

    sebagai berikut:

    =RIGHT(teks;jumlah_karakter)

    Sebagai contoh jika pada sel B2Anda ketikan formula berikut ini:

    =RIGHT("KOMPUTER";3)

    Maka hasilnya sel B2 tersebut akan menampilkan kata TER, yaitu 3 karakter pertama dari

    arah kanan pada kata KOMPUTER seperti tampak pada gambar berikut ini.

    Seperti halnya formula lainnya, penggunaan fungsi teks ini tentu saja dapat merujuk ke

    alamat sel tertentu. Sebagai contoh perhatikan tabel berikut ini. Pada sel C2 digunakan

    formula MIDdengan teks yang merujuk pada sel B2.

  • 5/20/2018 Materi Kkpi Excel

    49/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 45

    20.

    Fungsi VLOOKUP

    VLOOKUPtermasuk kedalam salah satu fungsi yang paling banyak digunakan dalam aplikasi

    Excel. Sesuai dengan nama dasarnya yaitu lookup yang bisa diartikan melihat atau mencari,

    maka fungsi ini akan menghasilkan suatu formula untuk mengisi data pada tabel

    berdasarkan data pada tabel lainnya atau tabel referensi tertentu dengan menggunakansuatu nilai kunci yang spesifik. Awalan huruf V didepan kata lookup merupakan singkatan

    dari kata Vertical. Istilah vertical ini merujuk pada bentuk tabel referensi yang digunakan,

    dimana judul kolomnya terletak dibagian atas dan data-datanya tersusun kebawah secara

    vertikal. Contoh sebuah tabel vertical ini bisa dilihat pada gambar berikut ini.

    Aturan penulisan VLOOKUP mungkin bisa dibilang agak rumit bagi yang baru saja

    mempelajari penggunaan formula pada Excel. Namun jika sudah terbiasa, aturan ini

    sebetulnya bisa dibilang sederhana, yaitu:

    =VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)

    Penjelasan aturan penulisan tersebut sebagai berikut:

    nilai_kunci: Adalah nilai yang dijadikan acuan untuk membaca tabel referensi. Nilai

    ini harus ada baik pada tabel yang akan diisi maupun pada tabel referensi.

    range_tabel_referensi : Adalah range dari tabel yang berisikan data referensi untuk

    mengisi hasil yang diharapkan. Pastikan bahwa range yang Anda pilih tidak

    menyertakan judul kolomnya.

    no_index-kolom: Adalah nomor urut data dalam tabel referensi yang akan dituliskan

    hasilnya. Dimulai dari kolom paling kiri pada tabel referensi tersebut dengan nomor

    index 1, dan seterusnya. tipe_data: Ada 2 jenis tipe data yaitu TRUEdan FALSE. Nilai TRUE Anda gunakan jika

    nilai datanya tidak pasti atau berada pada range tertentu dan nilai FALSE Anda

    gunakan jika nilai data berharga pasti.

    Agar lebih jelas bagaimana cara menggunakan fungsi VLOOKUP ini, perhatikan contoh

    berikut ini.

  • 5/20/2018 Materi Kkpi Excel

    50/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 46

    Pada contoh tersebut terdapat 2 buah tabel. Data pada tabel Laporan Penjualanjelas akan

    selalu bertambah tiap harinya. Setiap kali ada pembeli maka Anda tinggal mengisikan data

    tanggal, nama pembeli serta kode voucher-nya. Sementara data Voucherserta Hargaakan

    terisi secara otomatis tiap kali Anda mengisikan data pada kolom Kode. Pengisian data

    secara otomatis ini bisa dicapai dengan melihat tabel referensi yaitu tabel Stok Gudangdan

    tentunya dengan menggunakan formula VLOOKUP.

    Langkah pertama akan diisi dahulu data pada tabel Voucheryaitu data pada sel E12. Untukmempermudah pembahasan, aturan penulisan VLOOKUP kembali dituliskan sebagai berikut:

    E12=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)

    nilai_kunciyang digunakan adalah data pada kolom Kodedalam tabel Laporan Penjualan,

    yaitu sel D12. Alasannya karena Voucherakan bisa terisi dengan bersandar pada data dalam

    kolom Kodetersebut.

  • 5/20/2018 Materi Kkpi Excel

    51/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 47

    Dengan demikian formulanya menjadi:

    E12=VLOOKUP(D12;range_tabel_referensi;no_index_kolom;tipe_data)

    Kemudian range_tabel_referensi jelas adalah range data pada tabel Stok Gudang yaitu

    B4:D7.

    Dengan demikian formulanya menjadi:

    E12=VLOOKUP(D12;B4:D7;no_index_kolom;tipe_data)

    no_index_kolomAnda tentukan dengan melihat didalam range_tabel_referensi . Dalam hal

    ini Anda akan mengisi data pada kolom Voucher maka Anda lihat data untuk Vouchertersebut didalam range_tabel_referensi berada pada kolom keberapa?

    Dari gambar diatas dapat dilihat bahwa data yang akan diambil berada pada kolom ke-2,

    maka no_index_kolomyang digunakan adalah 2. Dengan demikian formulanya menjadi:

    E12=VLOOKUP(D12;B4:D7;2;tipe_data)

  • 5/20/2018 Materi Kkpi Excel

    52/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 48

    Yang terakhir adalah tipe_data. Disini yang akan digunakan adalah FALSE. Penjelasannya

    adalah karena data tersebut bersifat pasti, misalnya kode S sudah pasti untuk Simpati, kode

    E sudah pasti untuk Esia, dan demikian seterusnya. Dengan demikian formula lengkap untuk

    mengisi data pada kolom Voucher tersebut adalah:

    E12=VLOOKUP(D12;B4:D7;2;FALSE)

    Untuk mengisi data pada sel selanjutnya yaitu sel E13dan E14seperti biasanya Anda dapat

    menggunakan Auto Fill. Namun perhatikan bahwa tabel referensi yang menjadi acuan

    memiliki range alamat sel yang statis alias tetap. Oleh karenanya, sebelum Anda

    menjalankan proses Auto Fill maka Anda harus mengunci terlebih dahulu range tabel

    referensi tersebut agar menjadi sel yang absolut. Hingga formulanya akan menjadi:

    E12=VLOOKUP(D12;$B$4:$D$7;2;FALSE)

    Dan proses Auto Fill kini dapat dijalankan untuk mengisi data pada sel-sel berikutnya.

  • 5/20/2018 Materi Kkpi Excel

    53/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 49

    Berikutnya, bagaimana mengisi data pada kolom Hargayaitu sel F12hingga F14? Formula

    yang digunakan tentunya tetap sama hanya saja no_index_kolom yang berbeda yaitu 3

    karena data yang akan diisikan diambil dari kolom ke-3 pada tabel referensi.

    Dan berikut ini formula yang digunakan setelah menggunakan sel absolut untuk range tabel

    referensinya.

    F12=VLOOKUP(D12;$B$4:$D$7;3;FALSE)

    Pada contoh diatas Anda telah melihat penggunaan tipe data FALSE yaitu tipe data yang

    pasti. Untuk tipe data TRUEaturan penulisan formula-nya tetap sama, hanya saja data yang

    akan diisikan adalah data yang tidak pasti atau berada pada range tertentu. Contoh untuk

    penggunaan tipe data TRUE ini bisa dilihat pada tabel berikut ini.

  • 5/20/2018 Materi Kkpi Excel

    54/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 50

    Anda harus mengisikan formula VLOOKUPpada kolom Nilaidalam tabel Daftar Nilai Siswa,

    yaitu sel E13hingga sel E15. Nilai yang akan diisikan tersebut mengacu pada range tertentu

    dalam tabel referensi, yaitu:

    Nilai E: Score 0 s/d 39

    Nilai D: Score 40 s/d 59

    Nilai C: Score 60 s/d 79

    Nilai B: Score 80 s/d 89

    Nilai A: Score 90 s/d 59

    Kondisi ini jelas menggambarkan sebuah data yang tidak pasti oleh karenanya tipe data yangdigunakan adalah TRUE. Dan formula akhir yang digunakan adalah sebagai berikut,

    =VLOOKUP(D13;$B$4:$C$8;2;TRUE)

    Yang harus Anda perhatikan untuk tipe data TRUE ini adalah urutan data pada tabel

    referensi harus menaik atau tersusun dari data terkecil hingga data terbesar.

  • 5/20/2018 Materi Kkpi Excel

    55/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 51

    21.

    Fungsi HLOOKUP

    HLOOKUP adalah varian lain dari fungsi VLOOKUP yang telah dibahas pada artikel

    sebelumnya. Kegunaannya juga sama yaitu untuk mengisi data pada tabel berdasarkan data

    pada tabel lainnya atau tabel referensi tertentu dengan menggunakan suatu nilai kunci yang

    spesifik. Perbedaannya dengan VLOOKUP ditunjukan oleh awalan huruf H yang berartiHorizontal, artinya tabel referensi yang digunakan berbentuk horisontal, dimana judul

    kolomnya terletak dibagian kiri dan data-datanya tersusun kekanan dalam arah horisontal.

    Contoh sebuah tabel horisontal bisa dilihat pada gambar berikut ini.

    Aturan penulisan HLOOKUP juga sama dengan VLOOKUP, namun ada sedikit perbedaan

    yaitu pada no index-nya. Jika pada VLOOKUP no index mengacu kepada kolom

    (no_index_kolom) maka pada HLOOKUP mengacu pada baris (no_index_baris), hal ini

    dikarenakan datanya yang memang tersusun dalam suatu baris.

    Berikut ini aturan penulisannya:

    =HLOOKUP(nilai_kunci;range_tabel_referensi;no_index_baris;tipe_data)

    Penjelasan aturan penulisan tersebut sebagai berikut:

    nilai_kunci: Adalah nilai yang dijadikan acuan untuk membaca tabel referensi. Nilai

    ini harus ada baik pada tabel yang akan diisi maupun pada tabel referensi.

    range_tabel_referensi : Adalah range dari tabel yang berisikan data referensi untuk

    mengisi hasil yang diharapkan. Pastikan bahwa range yang Anda pilih tidak

    menyertakan judul barisnya.

  • 5/20/2018 Materi Kkpi Excel

    56/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 52

    no_index_baris: Adalah nomor urut data dalam tabel referensi yang akan dituliskan

    hasilnya. Dimulai dari baris paling atas pada tabel referensi tersebut dengan nomor

    index 1, dan seterusnya.

    tipe_data: Ada 2 jenis tipe data yaitu TRUE dan FALSE. Nilai TRUE Anda gunakan jika

    nilai datanya tidak pasti atau berada pada range tertentu dan nilai FALSE Anda

    gunakan jika nilai data berharga pasti.

    Sebagai contoh untuk penggunaan HLOOKUP ini, perhatikan tabel berikut.

    Pada sel C8dalam tabel Cek Stok, digunakan formula HLOOKUPagar jika Anda ketikan nama

    Produkpada sel B8maka otomatis ditampilkan jumlah Stokyang tersedia dengan mengacu

    pada tabel referensi Stok Gudang. Dengan demikian maka formula yang digunakan adalah

    sebagai berikut.

    C8=HLOOKUP(B8;C3:E4;2;FALSE)

    Penjelasan dari formula tersebut adalah:

    B8 adalah nilai_kunci yang digunakan karena jumlah Stok akan ditampilkan jika

    nama Produkdalam sel B8tersebut diisi.

    C3:E4adalah range_tabel_referensiyang digunakan sebagai acuan pengisian jumlah

    Stokpada tabel Cek Stok.

    no_index_baris adalah 2 karena yang akan dituliskan pada tabel Cek Stokdiambil

    dari baris kedua pada range_tabel_referensi .

    Tipe data yang digunakan adalah FALSEkarena datanya bersifat pasti.

    22. Pesan Kesalahan

    Saat bekerja dengan formula pada aplikasi Excel, mungkin saja ada kesalahan yang Anda

    lakukan. Kesalahan ini akan ditampilkan oleh aplikasi Excel dalam bentuk kode atau pesan

    kesalahan tertentu. Dengan mengetahui arti dari pesan-pesan kesalahan tersebut

    diharapkan Anda bisa segera mencari solusi atau memperbaikinya. Berikut ini beberapa

    pesan kesalahan yang akan ditampilkan oleh Excel tersebut:

  • 5/20/2018 Materi Kkpi Excel

    57/72

    SMK Negeri 2 Depok Sleman - Diktat Pelajaran KKPI - Excel 53

    a. #####

    Pesan kesalahan ini umum disebut dengan istilah "Railroad tracks. Beberapa penyebab

    munculnya pesan kesalahan ini diantaranya adalah:

    Anda menuliskan data yang panjang namun lebar kolomnya terlalu sempit hingga seltidak dapat menampilkan data tersebut.

    Anda mengisi angka atau bilangan negatif pada suatu sel dimana sel tersebut

    memiliki format sel Date (tanggal) atau Time (waktu).

    b. #REF!

    REF bisa diartikan dengan REFERENCE atau referensi, hingga pesan kesalahan ini

    umumnya berhubungan dengan kesalahan pada formula yang melibatkan penggunaan

    tabel referensi sebagai acuan. Beberapa penyebab munculnya pesan kesalahan ini

    diantaranya adalah:

    Anda membuat suatu formula yang terhubung dengan suatu tabel referensi namun

    kemudian tabel referensi tersebut terhapus.

    Pada fungsi lookup atau referensi seperti misalnya VLOOKUP dan HLOOKUP, Anda

    menggunakan nomor index kolom atau baris yang lebih besar dari jumlah kolom

    atau baris pada tabel referensinya.

    Data rujukan yang digunakan dalam formula dipindahkan ke tempat lain.

    c. #VALUE!

    VALUE bisa diartikan dengan nilai, hingga pesan kesalahan ini umumnya berhubungan

    dengan kesalahan nilai yang digunakan pada formula. Beberapa penyebab munculnyapesan kesalahan ini diantaranya adalah:

    Anda menggunakan parameter teks pada argumen suatu fungsi yang seharusnya

    menggunakan parameter berupa angka atau bilangan. Misalnya seperti

    =HLOOKUP(B8;C3:E4;"dua";FALSE).