Mengupas Kedahsyatan Fungsi TEXT Microsoft Excel

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).

Mengupas Kedahsyatan Fungsi TEXT Microsoft Excel

Johar Arifin

PENERBIT PT ELEX MEDIA KOMPUTINDO

Mengupas Kedahsyatan Fungsi TEXT Microsoft Excel Johar Arifin 2018 PT Elex Media Komputindo, Jakarta Hak cipta dilindungi undang-undang Diterbitkan pertama kali oleh Penerbit PT Elex Media Komputindo Kelompok Gramedia, Anggota IKAPI, Jakarta 2018

nadhia@elexmedia.id

718051380

ISBN 9786020479835

Dilarang keras menerjemahkan, memfotokopi, atau memperbanyak sebagian atau seluruh isi buku ini tanpa izin tertulis dari penerbit.

Dicetak oleh Percetakan PT Gramedia, Jakarta

Isi di luar tanggung jawab percetakan

vii

Daftar Isi Prakata............................................................................................. v Daftar Isi .......................................................................................... vii Bab 1. Eksplorasi Teks ....................................................................... 1

1.1 Pengantar ............................................................................... 1 1.2 Eksplorasi Teks ........................................................................ 2 1.3 Tentang Buku Ini ..................................................................... 3 1.4 Mengoptimalkan Materi Pembahasan ................................... 4 1.5 Mengunduh File ...................................................................... 4

Bab 2. Fungsi Terapan ...................................................................... 5

2.1 Pengantar ............................................................................... 5 2.2 Fungsi Terapan ....................................................................... 6

2.2.1 Fungsi IF .................................................................... 6 2.2.2 Fungsi OR .................................................................. 7 2.2.3 Fungsi AND ................................................................ 8 2.2.4 Fungsi ADDRESS ........................................................ 10 2.2.5 Fungsi COLUMN ........................................................ 11 2.2.6 Fungsi ROW ............................................................... 12 2.2.7 Fungsi VLOOKUP ....................................................... 13 2.2.8 Fungsi HLOOKUP ....................................................... 14 2.2.9 Fungsi FORMULATEX ................................................. 15 2.2.10 Fungsi INDEX ............................................................. 16 2.2.11 Fungsi MATCH ........................................................... 17 2.2.12 Fungsi Left ................................................................. 19 2.2.13 Fungsi MID ................................................................ 20 2.2.14 Fungsi RIGHT ............................................................. 20 2.2.15 Fungsi LOWER ........................................................... 21 2.2.16 Fungsi UPPER ............................................................ 21 2.2.17 Fungsi TEXT ............................................................... 22

1

Bab 1

Eksplorasi Teks

Buku yang baik dalam pandangan penulis adalah buku yang mudah

dipelajari dan dirancang sesuai kebutuhan.

1.1 PENGANTAR Program aplikasi Microsoft Office dipastikan terpasang pada sebagian

besar populasi komputer di dunia dan salah satu diantaranya yang

banyak dioperasikan adalah Microsoft Excel. Sebagai program pengolah

angka, keandalan dan kecanggihan program aplikasi Microsoft Excel

(dalam buku ini kadang disebut dengan Excel saja) sudah terbukti dan

teruji dalam rentang waktu yang cukup panjang. Beragam fasilitas dan

fungsi yang disediakan semakin mudah digunakan dan memanjakan

pengguna. Microsoft Excel menawarkan begitu banyak fasilitas yang siap

digunakan untuk menyelesaikan berbagai persoalan dari yang sederhana

hingga yang rumit sekalipun. Persoalannya kembali kepada pengguna

apakah mampu mengoptimalkan fasilitas yang telah disediakan tersebut.

Istilah Excel mudah dioperasikan barangkali telah merasuk ke sebagian

pengguna sehingga kadang kita abaikan karena sudah merasa mampu

mengoperasikan tanpa perlu belajar lebih lanjut. Padahal eksplorasi

fungsi dan fasilitas program ini tidak ada habisnya dan barangkali kita

mungkin baru mengenal dan menggunakan dalam kisaran 5% hingga

10% dari semua yang disediakan Excel.

2

Seringkali tanpa kita sadari, pengetahuan kita tentang Excel akan

semakin tertinggal jika tanpa memperbarui pengetahuan tentang

fasilitas yang ditambahkan pada versi yang lebih baru dari yang selama

ini dipakai. Tidak ada kata terlambat untuk memulai belajar dan

mengeksplorasi fasilitas Excel, jadikan saat ini sebagai titik awal, apa

yang dipelajari saat ini menjadi investasi pengetahuan yang berharga di

masa mendatang.

1.2 EKSPLORASI TEKS Microsoft Excel dilengkapi begitu banyak kotak dialog atau jendela

sebagai sarana komunikasi tertulis dengan pengguna. Program aplikasi

diciptakan untuk membantu memecahkan masalah yang dihadapi

pengguna sehingga memerlukan komunikasi. Sebagai contoh sederhana,

ketika kita meminta tolong pihak lain untuk membelikan spidol untuk

digunakan pada papan tulis misalnya, harus menyebut warna, permanen

atau yang mudah dihapus, berapa banyak, dan seterusnya. Begitu juga

ketika kita menggunakan fungsi IF misalnya, tentu kita harus

menjelaskan posisi data yang akan dibaca, persyaratannya apa, jika

persyaratan terpenuhi, apa yang harus dilakukan dan jika tidak

terpenuhi, Excel harus bagaimana. Tentu saja pada setiap kotak dialog

terdapat penjelasan yang harus dipahami oleh pengguna.

Memahami informasi yang terdapat dalam kotak dialog mutlak

diperlukan agar komunikasi menjadi lebih efektif dan efisien. Tidak

berlebihan jika dikatakan pengguna yang mampu berkomunikasi dengan

baik melalui kotak dialog akan lebih mudah mengeksplorasi kemampuan

program aplikasi ini. Sering kali hasil olah data dengan Excel ditujukan

kepada pihak lain sehingga bisa dikatakan pengguna menjembatani

kebutuhan pihak lain dengan bantuan Excel. Komunikasi melibatkan tiga

pihak, yaitu Excel dengan pengolah data (pengguna) dan pengguna

dengan pihak yang memerlukan hasil olah data.

3

Hasil menjadi optimal jika pengguna dapat berkomunikasi efektif dengan

Excel dan pihak yang memerlukan olah data.

Komunikasi antara Excel dengan pengguna tentu sudah terjalin ketika

kita mengoperasikan dan mengolah data, semakin kita sering

menggunakannya, bekerja menjadi lebih efektif, dan waktu proses olah

data menjadi lebih cepat. Bagi yang merasa susah untuk menggunakan

fasilitas atau fungsi tertentu, tidak ada cara lain harus mengubah cara

berkomunikasinya. Buku ini hadir mengupas cara berkomunikasi dengan

Excel melalui kotak dialog sehingga lebih mudah untuk dioperasikan.

Lebih dari itu, materi buku secara khusus mengupas penggunaan fungsi

Text dan menambahkan kalimat (teks) sehingga lebih mudah membaca

dan memahami hasil olah data.

1.3 TENTANG BUKU INI Buku ini secara khusus mengupas penggunaan fungsi Text untuk

menyusun kalimat sebagai penjelasan hasil olah data. Paling tidak

terdapat tiga persyaratan yang harus dipenuhi, pertama cara

pengoperasian program dan fungsi aplikasi Excel. Kedua, pengetahuan

tentang materi yang dibahas seperti keuangan, akuntansi, olahraga, dan

sebagainya. Ketiga, merangkai kata menjadi kalimat yang komunikatif

untuk menjelaskan hasil.

Persyaratan pertama tentang fungsi dikupas secara rinci dalam buku ini,

persyaratan kedua tentu dapat dipelajari sesuai kasus yang akan dibuat.

Menyusun kalimat yang efektif dan komunikatif sebagai persyaratan

ketiga dipengaruhi kebiasaan pengguna dalam berkomunikasi lisan yang

selanjutnya diubah menjadi bentuk tulisan. Hasil optimal dapat dicapai

melalui proses belajar dan berlatih terus-menerus; buku ini menjadi

sarana yang pas untuk mengasah potensi dan memperluas wawasan

pembaca. Mempelajari pilihan materi buku ini tentu berpulang sesuai

kebutuhan pembaca, silakan pilih topik sesuai kebutuhan.

4

1.4 MENGOPTIMALKAN MATERI PEMBAHASAN Materi pembahasan dalam buku ini menggunakan beberapa fungsi

tingkat lanjut yang dikenal dengan fungsi Analysis ToolPak. Fungsi

tingkat lanjut ini sering kali belum aktif pada saat menginstal program.

Pembaca dapat memeriksa fungsi tingkat lanjut melalui menu dengan

klik tombol Microsoft Office (Excel 2007) atau klik tab File (Excel

2010/2013/2016) > Options, pilih kategori Add-Ins dalam jendela Excel

Options, klik tombol Go, jendela Add-Ins ditampilkan. Jika kotak periksa

Analysis ToolPak ditandai, berarti fungsi tingkat lanjut telah ada dalam

komputer, jika belum ditandai, klik hingga kotak periksa Analysis ToolPak

dan fasilitas lain seperti Solver ditandai, klik tombol perintah OK.

Setting atau setelan dalam Region and Language adalah Indonesia,

sehingga tanda pemisah argumen pada fungsi adalah titik koma bukan

koma. Perbedaan setelan pada komputer pada saat menggunakan fungsi

bisa jadi tidak masalah, karena Excel akan menyesuaikan pemisah

argumen dalam titik koma atau koma. Masalahnya adalah pada

penggunaan fungsi TEXT untuk penulisan angka, penulis menggunakan

koma untuk desimal dan titik untuk pemisah kelipatan ribuan sesuai

dengan aturan yang berlaku dalam pilihan setelan Indonesia. Jika

pembaca menggunakan setelah (setting) lain, silakan sesuaikan tanda

koma dan titik dalam format angka.

1.5 MENGUNDUH FILE File materi pembahasan buku ini dapat pembaca unduh di website

http://static.elexmedia.id/download dengan prosedur:

1. Buka http://static.elexmedia.id/download.

2. Akan terlihat sejumlah cover buku. Pilih buku Mengupas Kedahsyatan

Fungsi TEXT Microsoft Excel. Saat Anda klik cover buku tersebut, akan

keluar kotak dialog untuk men-download, kemudian lakukan proses

download file pendukung buku ini dengan meng-klik tombol OK.

3. Ekstrak dan simpan file tersebut ke komputer yang digunakan.

5

Bab 2

Fungsi Terapan

Belajar fungsi pada program aplikasi Microsoft Excel secara efektif dan

efisien dilakukan dengan memahami isi argumen dan penggunaan logika.

2.1 PENGANTAR

Program aplikasi Microsoft Excel dilengkapi lebih dari 400 fungsi dalam

berbagai kategori untuk pemecahan studi kasus yang dibutuhkan pengguna.

Materi buku ini menggunakan 54 dari kategori logika, lookup dan referensi,

teks, tanggal dan waktu, finansial, matematika dan trigonometri, statistik,

dan yang terakhir kategori teknik. Pembahasan materi dalam bab ini

didasarkan pada kategori tersebut dan kemiripan penggunaan fungsi; bukan

pada urutan berdasarkan abjad. Beberapa fungsi juga dikupas singkat pada

bab lain dengan pertimbangan agar pembaca tidak terpecah konsentrasi

belajar dengan bolak-balik membaca penjelasan dari bab yang berbeda.

Sebagian pembaca dipastikan sudah terbiasa menggunakan program

aplikasi Micrsoft Excel dan tidak asing dengan sebagian fungsi yang dikupas

dalam bab ini. Materi pembahasan ini ditujukan kepada pembaca yang

masih memerlukan penjelasan mendasar tentang fungsi yang digunakan

dalam merancang tabel data dalam buku ini.

Fungsi apa saja yang harus dipelajari tentu berpulang kepada kebutuhan

pembaca, silakan pilih bahasan materi sesuai kebutuhan.

6

Memahami fungsi dapat dilakukan secara efektif dan efisien dengan

mengetahui lebih dulu untuk apa fungsi dibuat, penjelasan argumen fungsi

dan penggunaan logika. Selanjutnya berlatih fungsi berulang-ulang

menggunakan berbagai studi kasus diawali dari yang sederhana. Setelah

dirasa mampu, pembaca dapat mengembangkan pengetahuan tersebut

dengan memecahkan studi kasus yang cukup rumit bahkan cenderung rumit

secara bertahap.

2.2 FUNGSI TERAPAN

Materi pembahasan fungsi terapan tersimpan dalam file BAB02 dan media

untuk berlatih tersimpan dalam file BAB02L dengan isi nama sheet sesuai

nama fungsi. Silakan mempelajari atau berlatih dengan mengaktifkan

program aplikasi Microsoft Excel dan file tersebut. Bagian pertama

mengupas kategori fungsi logika If, And, dan Or. Pengguna program aplikasi

Microsoft Excel pasti pernah mendengar fungsi dalam kategori logika atau

fungsi logika. Sekadar pengingat, fungsi logika yang banyak dikenal adalah If,

selanjutnya Or, dan And walaupun ada fungsi lain dalam kategori ini.

Bagaimana cara belajar fungsi logika yang praktis? Mudah, perlakukanlah

komputer (dalam hal ini Excel) layaknya pengguna sedang berkomunikasi

dengan sesama manusia. Excel dibuat untuk membantu tugas manusia,

bahasa sederhananya disuruh atau dimintai tolong melakukan sesuatu oleh

pengguna. Sebagai alat yang diciptakan untuk bekerja sama, Excel

dilengkapi kotak dialog atau jendela untuk berkomunikasi sebelum

menjalankan perintah.

2.2.1 FUNGSI IF

Penggunaan program aplikasi Excel hampir pasti menggunakan fungsi If

dengan bentuk penulisan:

=IF(logical_test,value_if_true,value_if _false), atau

7

=IF(tes_logika;nilai_jika_benar;nilai_jika_salah)

Penerapan contoh sederhana dapat dilihat dalam tab sheet IF seperti

ditunjukkan melalui Gambar 2.1. Ilustrasi menggambarkan perolehan nilai

ujian yang menghasilkan keterangan lulus atau gagal.

Gambar 2.1. Penjelasan dan penerapan fungsi IF

Berdasarkan nilai perolehan ujian dalam setiap sel pada range L4:L23 dapat

ditentukan (lihat kolom Hasil) seseorang dinyatakan Lulus atau Gagal

berdasarkan persyaratan pada alamat sel F5 (nilai minimum kelulusan).

Untuk memahami fungsi IF, lihat penjelasan pengisian argumen sehingga

pada alamat sel M4 disusun formula =IF(L4<F$5;"Gagal";"Lulus") atau

=IF(L4>=F$5;"Lulus”;Gagal"). Artinya, jika pada alamat sel L4 (nilai ujian) <

dari isian pada alamat sel F5 (nilai minimum kelulusan), hasil adalah Gagal,

jika kondisi sebaliknya berarti Lulus. Atau, dengan bahasa lain, jika pada

alamat sel L4 (nilai ujian) ≥ dari isian pada alamat sel F5 (nilai minimum

kelulusan), hasil adalah Lulus, jika kondisi sebaliknya berarti Gagal.

2.2.2 FUNGSI OR

Fungsi Or, secara harafiah berarti ATAU, fungsi ini biasanya menjadi bagian

fungsi lain; untuk memeriksa apakah salah satu argumen atau logika BENAR

untuk menghasilkan BENAR atau SALAH.

8

Klik tab sheet OR dan perhatikan tampilan tabel data melalui Gambar 2.2.

Ilustrasi terdapat sembilan warna yang tersimpan dalam range H8:I16

dengan nama Warna. Selanjutnya pilih dua warna yang dikehendaki dengan

klik tombol scroll-bar, silakan ketik atau isi warna pada setiap sel dalam

range B8:B19. Penerapan contoh fungsi If dengan Or dapat dilihat pada

setiap sel dalam range C8:C19.

Perhatikan pada Gambar 2.2 di bawah ini. Formula yang disusun pada

alamat sel C8 =IF(OR(B8=$E$4;B8=$E$5);"Ya"; "Tidak") yang selanjutnya

disalin dan ditempatkan dalam range C9:C19. Arti formula, jika pada alamat

sel B8 berisi warna seperti pada alamat sel E4, yaitu merah atau alamat sel

E5, yaitu biru diisi pilihan Ya. Jika kondisi sebaliknya (bukan merah atau biru)

pilihan diisi Tidak.

Gambar 2.2. Penjelasan dan penerapan fungsi OR

2.2.3 FUNGSI AND

And artinya adalah DAN, fungsi ini dalam penerapan biasanya dikom-

binasikan dengan fungsi lain.

9

Fungsi ini untuk memeriksa apakah semua argumen atau logika benar, baru

menghasilkan pernyataan TRUE atau BENAR yang berarti semua syarat

harus terpenuhi, baru menghasilkan pernyataan TRUE atau BENAR. Ilustrasi

contoh seorang akan membeli kaus dengan warna dan ukuran tertentu yang

diinginkan. Silakan klik tab sheet AND, pilih warna dan ukuran kaus dengan

klik tombol scroll-bar, contoh tampilan tabel data dapat dilihat melalui

Gambar 2.3. berikut ini.

Gambar 2.3. Penjelasan dan penerapan fungsi AND

Formula yang disusun pada alamat sel D10 =IF(AND(B10=D$6;C10=D$7);

"Ya";"Tidak") yang selanjutnya disalin dan ditempatkan dalam range

D11:D21. Arti formula, jika pada alamat sel B10 berisi warna seperti pada

alamat sel D6, yaitu merah dan ukuran sesuai isian alamat sel D7 yaitu XL,

diisi pilihan Ya. Jika kondisi sebaliknya (bukan merah dan ukuran XL) pilihan

diisi Tidak.

10

Cara praktis untuk memahami ketiga fungsi If, Or, dan And tersebut adalah

dengan pendekatan logika, yaitu jika syarat terpenuhi apa yang harus

dilakukan dan jika syarat tidak terpenuhi harus melakukan atau memilih

apa. Silakan ubah data dengan klik tombol scroll-bar yang terdapat dalam

tiga tab sheet, amati setiap perubahan hasil, lakukan ber-ulang-ulang

sampai memahaminya. Penjelasan mendasar ketiga fungsi tersebut akan

dikupas lebih mendalam melalui pembahasan Bab 6 – Penjelasan Fungsi.

Bagian kedua mengupas delapan fungsi, yaitu Address, Column, Row,

Vlookup, Hlookup, Formulatext, Index, dan Match yang termasuk dalam

kategori Lookup and Reference atau pencarian dan referensi. Pencarian

data dari sebuah formula pada dasarnya mengembalikan bentuk nilai tabel

dengan melihat nilai yang lain. Analogi yang sederhana adalah ketika kita

mencari nomor telepon seseorang, hal pertama yang dilakukan adalah

mencari nama orang yang dikehendaki dan selanjutnya menggunakan

nomor telepon tersebut. Cara sederhana seperti inilah yang dapat

menggambarkan cara kerja fungsi pencarian atau lookup dan referensi.

Penerapan semacam lazim untuk mencari informasi dalam sebuah tabel

data untuk dicocokkan dengan data yang terdapat dalam suatu tabel atau

array.

2.2.4 FUNGSI ADDRESS

Fungsi ADDRESS digunakan untuk menampilkan alamat sel dalam bentuk

teks dengan menyebut posisi nama kolom dan nomor baris, relatif/absolut

kolom/baris dan nama sheet. Bentuk penulisan fungsi adalah:

=ADDRESS (row_num;column_num;abs_num;a1;sheet_text)

Argumen row_num diisi nomor baris dari suatu alamat sel yang akan dicari.

Column_num diisi nomor kolom dari suatu alamat sel yang akan dicari dan

argumen abs_num diisi nomor yang menyatakan jenis referensi alamat sel.

405

Tentang Penulis:

Johar Arifin, kelahiran Bumiayu-Brebes,

alumnusMagister Akuntansi Universitas Diponegoro

Semarang dan pernah mengikuti pelatihan di Small

Business Training Institute (SBTI), Ansan City, Korea

Selatan. Saat buku ini ditulis, tercatat sebagai pengajar

pada Program Vokasi Universitas Indonesia dan STIKES

Banten. Penulis buku bisnis terapan dengan Microsoft Excel terkemuka di

Indonesia yang sangat produktif, buku ini merupakan karya yang ke-87.

Catatan:

Untuk melakukan pemesanan buku, hubungi

Layanan Langsung PT Elex Media Komputindo:

Gramedia Direct

Jl. Palmerah Barat No. 29-37, Jakarta 10270

Telemarketing/CS: 021-53650110/111

ext: 3901/3902/3292