Sanksi Pelanggaran Pasal 113

Undang-Undang Nomor 28 Tahun 2014

tentang Hak Cipta

1. Setiap Orang yang dengan tanpa hak melakukan pelanggaran hak ekonomi

sebagaimana dimaksud dalam Pasal 9 ayat (1) huruf i untuk Penggunaan

Secara Komersial dipidana dengan pidana penjara paling lama 1 (satu)

tahun dan/atau pidana denda paling banyak Rp100.000.000 (seratus juta

rupiah).

2. Setiap Orang yang dengan tanpa hak dan/atau tanpa izin Pencipta atau

pemegang Hak Cipta melakukan pelanggaran hak ekonomi Pencipta

sebagaimana dimaksud dalam Pasal 9 ayat (1) huruf c, huruf d, huruf f,

dan/atau huruf h untuk Penggunaan Secara Komersial dipidana dengan

pidana penjara paling lama 3 (tiga) tahun dan/atau pidana denda paling

banyak Rp500.000.000,00 (lima ratus juta rupiah).

3. Setiap Orang yang dengan tanpa hak dan/atau tanpa izin Pencipta atau

pemegang Hak Cipta melakukan pelanggaran hak ekonomi Pencipta

sebagaimana dimaksud dalam Pasal 9 ayat (1) huruf a, huruf b, huruf e,

dan/atau huruf g untuk Penggunaan Secara Komersial dipidana dengan

pidana penjara paling lama 4 (empat) tahun dan/atau pidana denda paling

banyak Rp1.000.000.000,00 (satu miliar rupiah).

4. Setiap Orang yang memenuhi unsur sebagaimana dimaksud pada ayat (3)

yang dilakukan dalam bentuk pembajakan, dipidana dengan pidana

penjara paling lama 10 (sepuluh) tahun dan/atau pidana denda paling

banyak Rp4.000.000.000,00 (empat miliar rupiah).

Adi Kusrianto

Dhani Yudhiantoro

PENERBIT PT ELEX MEDIA KOMPUTINDO

Pemanfaatan Fungsi Lookup Excel

Adi Kusrianto dan Dhani Yudhiantoro

©2019 PT Elex Media Komputindo

Hak cipta dilindungi undang-undang

Diterbitkan pertama kali oleh

Penerbit PT Elex Media Komputindo

Kelompok Gramedia, Anggota IKAPI, Jakarta 2019

okti@elexmedia.id

719050262

ISBN: 978-602-04-9093-9

978-602-04-9094-6 (Digital)

Dilarang keras menerjemahkan, memfotokopi, atau memperbanyak sebagian

atau seluruh isi buku tanpa izin tertulis dari penerbit.

Dicetak oleh Percetakan PT. Gramedia, Jakarta

Isi di luar tanggung jawab percetakan

DAFTAR ISI

Prakata .................................................................................................................. v

Daftar Isi ............................................................................................................... xi

Pendahuluan ....................................................................................................... xv

BAB 1 FUNGSI LOOKUP DAN REFERENCE ............................................................... 1

Menggunakan Alamat Sel sebagai Masukan Data .......................................... 3

Memberikan Nama Sel atau Range ................................................................ 4

Memasukkan Nama Sel ke dalam Formula .................................................... 7

BAB 2 LATIHAN MENGGUNAKAN FUNGSI VLOOKUP .............................................. 9

Menampilkan Data untuk Mengisi Invoice ................................................... 10

Mencari Harga Sparepart Tertentu dengan Fungsi VLOOKUP ...................... 11

Menggantikan Pesan Error dengan Pemberitahuan .................................... 15

Membuat Data Validasi untuk Menghindarkan Salah Ketik ......................... 16

Membuat Bentuk Tabel pada Formulir Invoice ............................................ 19

Tabel pada Invoice Tanpa Menampilkan Harga Satuan ............................... 22

Nilai Appoximate .......................................................................................... 24

Fungsi VLOOKUP untuk Membandingkan Penawaran

dari Beberapa Supplier .............................................................................. 27

BAB 3 Menggunakan Fungsi HLOOKUP pada Tabel .............................................. 29

Penulisan Formula HLOOKUP ....................................................................... 29

Contoh Umum Penggunaan Fungsi HLOOKUP ............................................. 30

Menghitung Ongkos Kirim Berdasar Data pada Tabel Mendatar ................. 31

Mencari Nilai Exact maupun Approximate pada Tabel Horizontal ............... 33

Mencari Data pada Tabel dengan Banyak Kolom ......................................... 36

Fungsi HLOOKUP untuk Mengetahui Jumlah Penjualan

dan Biaya Penjualan Suatu Tim ................................................................. 38

Fungsi HLOOKUP untuk Menampilkan Nilai Penjualan

pada Tanggal Tertentu .............................................................................. 39

Contoh Lain Pencarian pada Tabel Mendatar Menggunakan

Fungsi HLOOKUP ....................................................................................... 41

BAB 4 MEMANFAATKAN FUNGSI LOOKUP ........................................................... 43

Data yang Dicari Terletak di sebelah Kiri Kolom Kunci ................................. 46

Menggunakan Fungsi LOOKUP untuk Menampilkan Indikator

Suhu Ruangan ........................................................................................... 48

BAB 5 FUNGSI CHOOSE ........................................................................................ 51

Bentuk Penulisan .......................................................................................... 51

Contoh Penggunaan ..................................................................................... 52

Bentuk Penulisan .......................................................................................... 60

BAB 6 MENGGUNAKAN FUNGSI INDEX DAN MATCH ............................................ 63

Fungsi INDEX ................................................................................................ 63

Menggunakan Fungsi INDEX untuk Daftar Ganda ........................................ 68

Menggunakan Fungsi MATCH ...................................................................... 71

Menggunakan Fungsi INDEX untuk Mencari Data pada Daftar

yang Telah Diurutkan Nilainya .................................................................. 77

BAB 7 Memanfaatkan Fungsi OFFSET dan INDIRECT............................................. 79

Bentuk Penulisan .......................................................................................... 79

Menggunakan Fungsi INDIRECT ................................................................... 90

Bentuk Penulisan .......................................................................................... 91

Menampilkan Data Mendekati Tepat dengan Fungsi INDIRECT, ROW,

dan MATCH ............................................................................................... 95

BAB 8 MENAMPILKAN DATA DENGAN FUNGSI LAIN ............................................ 97

Memanfaatkan Fungsi IF .............................................................................. 97

Kegunaan Fungsi AND ................................................................................ 103

Kegunaan Fungsi OR ................................................................................... 103

Menggunakan ADDRESS, MAX dan MATCH untuk Mencari Nilai

Paling Besar ............................................................................................. 104

Menggunakan ADDRESS, MATCH, dan MAX untuk Mencari Nilai

Paling Kecil .............................................................................................. 106

Menggunakan ADDRESS, MATCH, dan TODAY untuk Menjumlahkan

Hasil Penjualan sampai Hari Ini ............................................................... 108

BAB 9 MENAMPILKAN INFORMASI DENGAN FILTER ........................................... 111

Menggunakan Filter (pada Excel 2007 ke atas) .......................................... 111

Filter yang Tersedia .................................................................................... 111

BAB 1

FUNGSI LOOKUP DAN REFERENCE

Pada Excel, fungsi-fungsi dikelompokkan menurut jenis

kegunaannya. Anda dapat melihat ini pada Tab Formula

seperti gambar berikut ini.

Salah satu kelompok Fungsi adalah Lookup & Reference. Fungsi-

fungsi Lookup digunakan untuk menampilkan (mencari dan

menampilkan) data dari sebuah tabel, sedangkan fungsi-fungsi

Referensi untuk menampilkan alamat sel yang dicari.

Untuk melihat fungsi apa sajakah yang ada dalam kelompok ini, kita

bisa klik tombol segitiga kecil pada icon Lookup & Reference,

sehingga tampil daftar nama fungsi yang ada dalam kelompok ini,

dari fungsi ADDRESS hingga VLOOKUP.

Dalam proses pencarian dan menampilkan data, kita tidak terbatas

hanya menggunakan fungsi-fungsi pada kelompok “Lookup &

Reference” saja, melainkan bisa menggabungkan dengan bantuan fungsi lain di luar kelompok ini.

Fungsi-fungsi yang sering digunakan saat menyusun formula Lookup.

• CHOOSE Menghasilkan nilai tertentu dari tabel

berdasarkan argumen-argumen yang ada, yang jumlahnya

bisa mencapai 1 hingga 29 buah.

• HLOOKUP Pencarian secara menyamping pada sebuah

tabel yang berbentuk horizontal.

• IF Menghasilkan nilai tertentu jika kondisi yang

disyaratkan terpenuhi.

• IFERROR* Jika argumen pertama menghasilkan ERROR,

argumen kedua akan mengevaluasi kembali dan

menampilkan hasilnya.

• INDEX Menghasilkan nilai dari sebuah tabel atau

suatu range data.

• LOOKUP Menghasilkan nilai dari suatu baris atau kolom

yang terdapat dalam sebuah range. Selain itu, dapat juga

bekerja seperti pada fungsi VLOOKUP, hanya tidak bisa

menghasilkan nilai dari kolom terakhir.

• MATCH Menghasilkan posisi relatif dari suatu item

dalam suatu range yang cocok dengan suatu nilai tertentu.

• OFFSET Menghasilkan alamat pada suatu range, di

mana alamat yang disebutkan itu berupa baris ke berapa atau

kolom ke berapa dari suatu sel yang dicari.

• VLOOKUP Untuk pencarian informasi dalam data secara

vertikal.

Menggunakan Alamat Sel sebagai Masukan Data

Sebuah data dapat berupa referensi atau alamat suatu sel atau

range. Artinya, data tersebut mengacu pada nilai yang terkandung

pada sel lain. Sebagai contoh, sel AI berisi angka 100, kemudian

pada sel D3 Anda ketikkan entry data =Al, ketika Anda menekan

Enter akan menghasilkan angka 100, sementara pada formula bar

ditampilkan =AI.

Seandainya contoh di atas terjadi pada Sheet 1, Anda dapat

menuliskan referensi sel yang letaknya pada sheet lain. Misalnya saat

ini Anda berpindah ke Sheet 2, kemudian pada sheet 2 sel D3 akan

diisi dengan data yang bersumber pada sel AI dari Sheet 1. Caranya,

pada sel D3 sheet 2 cukup ketikkan = (untuk menambatkan posisi sel

aktif), kemudian klik tab sheet I sehingga sheet I terbuka. Klik sel AI

dan tekan Enter. Hasilnya, isi sel AI sheet I tampil pada sel D3 sheet 2.

Bukan saja pada sheet yang berbeda pada worksheet yang sama,

Anda juga dapat menggunakan referensi pada workbook yang

berbeda. Caranya sama seperti apa yang Anda lakukan pada sheet

yang berbeda. Bedanya, untuk berpindah ke buku kerja lain, buku

kerja itu harus sudah terbuka, dan untuk berpindah ke buku kerja

itu, Anda dapat menggunakan menu Window dan pilih nama buku

kerja itu. Referensi antarbuku kerja ini disebut "link". Setiap kali Anda

membuka file yang mengandung referensi dengan file (buku kerja)

lain akan diingatkan, apakah referensi dengan file lain (file sumber)

yang saat ini mungkin sedang tidak terbuka perlu diperbarui?

Memberikan Nama Sel atau Range

Sebuah sel maupun suatu Range sel dapat diberi nama. Misalnya sel

yang berisi harga pokok sebuah bahan diberi nama “Harga_ Pokok_Bahan”. Sel yang berisi jumlah biaya produksi diberi nama

“Biaya_Produksi”. Dengan demikian, ketika Anda menggunakan

formula, argument dalam formula itu mudah dimengerti.

Jika nama sel itu digunakan dalam sebuah formula akan seperti ini:

=(Bahan_Pokok_Bahan + Biaya_Produksi)

Untuk menamai sebuah sel maupun suatu Range Sel, langkahnya

sebagai berikut:

Pada Excel 2007, 2013, dan seterusnya

1. Pilihlah sel yang dikehendaki, atau bila berupa range, pilihlah

range yang dikehendaki.

2. Klik tombol pada tab Formula sehingga

muncul kotak dialog New Name. Ketikkan nama sel/range

pada kotak Name.

Penulisan nama memiliki persyaratan sebagai berikut:

Nama sel diawali dengan karakter maupun underscore.

Jangan menggunakan spasi. Jika diperlukan pemisah

antara satu kata dengan kata lain gunakanlah

underscore ( _ ) di antara dua kata.

Penggunaan jumlah karakter pada nama Cell tidak

terbatas, tetapi untuk kemudahan sebaiknya cukup

simple saja agar dapat terlihat ketika nama itu

ditampilkan pada formula bar.

Pada Excel 2003 dan sebelumnya

Gunakan menu Insert Name > Define sehingga ditampilkan

kotak dialog yang sama seperti berikut

Memasukkan Nama Sel ke dalam Formula

Setelah Anda memiliki sel yang telah diberi nama, nama sel tersebut

dapat digunakan ketika Anda sedang menulis sebuah formula. Cara

memasukkan nama sel dapat dilakukan melalui dua cara, sebagai

berikut:

Dengan cara mengetikkan langsung pada saat Anda menulis

formula pada formula bar. Namun, Anda harus menuliskan dengan

benar dan tepat nama sel tersebut. Apabila tidak tepat, misalnya

salah ejaannya, salah penggunaan huruf besar kecil maka Excel

tidak dapat menerimanya.

Menggunakan tombol Use in Formula pada group Named Cells di

tab Formulas. Ketika Anda klik tombol ini akan muncul nama-nama

sel. Pilihlah salah satu yang dikehendaki, nama itu akan masuk pada

posisi titik sisip ketika Anda menulis formula. Selain itu, dapat juga

dipilih Paste untuk menampilkan kotak dialog Paste Name. Pilih,

kemudian klik OK.

***

BAB 2 LATIHAN MENGGUNAKAN

FUNGSI VLOOKUP

Di antara ketiga Fungsi Lookup, fungsi inilah yang paling

sering digunakan. Fungsi VLOOKUP gunanya untuk

menemukan data yang sesuai dengan Lookup_Value

(data yang diketahui) yang terletak pada kolom paling

kiri, selanjutnya menampilkan isi field dari suatu kolom

yang Anda sebutkan nomornya. Fungsi ini berguna untuk

mencari data pada tabel yang berbentuk horizontal

(mendatar ke arah baris).

Untuk mencapai hasil yang lebih maksimal, saat kita mencari/

menampilkan data, fungsi VLOOKUP sering dilengkapi fungsi-fungsi

lainnya.

Field_ Kunci Harus Paling Kiri

Lookup_Value harus terletak pada kolom paling kiri dari

Range_tabel. Keterangan ini sering membuat bingung. Lalu

bagaimana jika Lookup_Value alias data yang diketahui tidak terletak

pada kolom paling kiri? Jawabnya mudah. Jadikanlah kolom yang

ditempati Lookup_Value menjadi kolom paling kiri. Caranya, ketika

mendefinisikan Range_Tabel, mulailah range tersebut dari kolom di

mana Lookup_Value berada.

Contoh 1

Menampilkan Data untuk Mengisi Invoice

Latihan awal ini, kita akan berlatih menampilkan data saat hendak

membuat sebuah Invoice. Pada pembuatan invoice paling sederhana

ini, datanya hanya memuat nama barang dan harga barang tersebut.

Sebelum membuat formulir invoice yang sebenarnya, kami ajak

Anda untuk memahami beberapa logika dan cara menampilkan data

yang kita kehendaki.

Sebagai latihan, kita buat formulir pencarian data. Bayangkan bahwa

gudang sparepart Anda memiliki ribuan item barang dan juga ribuan

harga satuan masing-masing barang. Namun untuk memudahkan,

kita mencoba dengan tabel data yang hanya berisi 8 item barang.

Formulir pencarian kita bentuknya seperti ini.

TENTANG PENULIS

Adi Kusrianto, seorang penulis senior yang telah banyak

menulis tentang Excel maupun topik lainnya di Elex Media

Komputindo. Saat ini profesinya selain sebagai penulis dan

pengelola Literary Agent, juga sebagai pengajar di

Universitas Ciputra (2010-2017), Surabaya dan LaSalle

College, Surabaya (2015 hingga kini). Memberikan

pelatihan Excel, juga di bidang entrepreneurship melalui Dinas Koperasi

Propinsi Jawa Timur, Dinas Sosial Propinsi Jawa Timur. Beberapa tulisannya

dimuat di blognya: https://adikusrianto.wordpress.com;

https://blogs.uc.ac.id/adi.kusrianto/;

Penulis dapat dihubungi melalui adikusrianto@gmail.com.

***

Dhani Yudhiantoro adalah seorang praktisi IT yang saat

ini bertugas sebagai Data Analis di sebuah perusahaan

multinasional. Kesukaannya menggunakan Excel

membuat ia memiliki banyak pengalaman ketika harus

mengajarkan bagaimana memanfaatkan Excel untuk

mengolah data maupun membuat perhitungan dengan

memanfaatkan rumus-rumus Excel. Alumni Fakultas Teknik Informatika

Universitas Surabaya ini pertama kali menulis buku Excel dengan judul Trik

dan Teknik Menggunakan Microsoft Excel, diterbitkan Penerbit Andi,

Yogyakarta dan cetak ulang 4 kali. Beberapa kali membagikan ilmunya

melalui pelatihan-pelatihan, baik inhouse training di kalangan karyawan

maupun mahasiswa peserta beasiswa Jarum di kampus ITS, Surabaya.

***

Kedua penulis merupakan tim dalam memberikan “Pelatihan Inhouse

Training Excel untuk meningkatkan produktifitas kerja” di seluruh Indonesia. Dapat dihubungi melalui nomor telepon atau WA 08165457187.