Modul Spreadsheet Kelas X-1word-Fix

SEMESTER I MODUL APLIKASI PENGOLAH ANGKA SPREADSHEET for ACCOUNTING

A.

STANDAR OPERASIONAL PROSEDUR (SOP)

Perkembangan teknologi di bidang komputer sedemikian rupa pesatnya, sampai menyentuh pada kebutuhan aplikasi hampir semua jenis kebutuhan manusia. Bahkan mungkin anda akan menemukan hal-hal baru pada bahasan-bahasan mendatang, begitu mudahnya, sehingga anda anggap terlalu mudah, anda bisa melewatinya dan melanjutkan pada bahasan berikutnya. Apapun yang ada dalam pikiran anda saat ini tentang komputer maupun dengan Software spreadsheet-nya, semua akan memberikan banyak kemudahan kepada kita. Saat ini jenis software spreadsheet yang sering digunakan cukup b anyak diantaranya sebagai berikut: Nama Vendor 

Microsoft



Lotus 123



Quattro Pro



Linux

Nama Produk Produk Microsoft excel Lotus 123 Millenium (Version 9.5) Quattro Pro Open Office.org Calc

Keterangan Lisensi Lisensi Lisensi

Freeware, open source

Dari sekian banyak program spreadsheet, tak ada yang lebih mudah mengoperasikannya, tergantung kebiasaan kita menggunakannya. Adapaun modul ini akan membahas program aplikasi pengolah angka dengan basis Microsoft Excel Microsoft Excel, atau biasa disebut Excel, merupakan program spreadsheet (pengolah data) yang mempunyai kemampuan meng-olah data secara luas pada bidang akuntansi, teknik, statistik dan bidang-bidang lain yang memerlukan perhitungan dengan cepat dan teliti. Excel dikenal sebagai program spreadsheet yang lebih mudah digunakan dibandingkan program spreadsheet yang lain. Kemudahan tersebut terlihat jelas dari banyaknya fasilitas rumus siap pakai (fungsi) (fu ngsi) yang disediakan Excel. Penulis menggunakan Excel 2010 dalam penyusunan buku ini.Namun demikian, pada prinsipnya buku ini juga dapat digunakan untuk pengguna Excel 2007.Penulis tidak membahas penggunaan formula atau fungsi yang disediakan Excel secara mendalam dalam buku ini. Pembahasan buku ini lebih mengarah pada penggunaan formula dan fungsi Excel untuk menyelesaikan per-masalahan bisnis dan perkantoran 1. Formula Excel Formula merupakan fitur Excel Excel yang digunakan untuk melakukan perhitungan nilai yang dituliskan secara langsung pada formula, atau nilai yang tersimpan dalam sel. Penggunaan formula harus diawali tanda sama dengan (=), disertai kombinasi elemen: 

Nilai yang dimasukkan langsung ke dalam formula.



Referensi alamat sel/range atau a tau nama sel/range



Operator perhitungan

Fungsi. Susunan formula untuk Excel 2007 dan Excel 2010 dapat terdiri maksimal 8.192 karakter, termasuk tanda sama dengan (=), nama fungsi, tanda kurung, argumen ataupun 

Spreadsheet For Accounting

1

pemisah argumen dan operator-operatornya. Apabila Excel tidak menerima formula yang Anda ketikkan, Excel akan menampilkan nilai error. Berikut nilai error yang ditampilkan Excel apabila terjadi kesalahan penulisan formula: Pesan kesalahan

2.

Kemungkinan penyebab

#DIV/0!

Formula yang Anda masukkan menyebabkan Excel membagi dengan angka nol (0) atau membagi pada referensi sel yang kosong.

#N/A

Ada argumen yang tidak tertulis dalam formula yang Anda masukkan.

#NAME?

Dalam formula yang Anda masukkan, ada argumen atau nama fungsi yang tidak dikenal Excel.

#NUM!

Penggunaan yang salah dari sebuah bilangan, misalnya Anda menghitung akar pangkat dua dari bilangan negatif. Nilai error #NUM! juga dapat diakibatkan karena formula menghasilkan nilai terlalu besar (I1 X 10307) atau terlalu kecil (-1 xlO 307).

#NULL!

Formula menentukan perpotongan antara dua range yang tidak berpotongan.

#VALUE

Penulisan argumen tidak sebagaimana yang disya-ratkan oleh fungsi. Misalnya, referensi yang Anda masukkan dalam sebuah fungsi adalah data berupa teks padahal seharusnya memasukkan data berupa angka. Nilai error #VALUE juga dapat terjadi karena jumlah karakter yang digunakan dalam formula melebihi karakter maksimal yang diperbolehkan.

#REFF

Salah satu referensi atau nama sel/range yang digunakan dalam formula telah terhapus.

Operator Formula

Operator merupakan simbol atau tanda yang digunakan dalam formula. Excel akan melakukan perhitungan berdasarkan operator yang digunakan dalam formula. Kesalahan penggunaan operator dapat berakibat pada hasil perhitungan yang salah. a .Operator Aritmetika

Operator aritmetika digunakan untuk melakukan kalkulasi operasi matematika, seperti

penambahan,

pengurangan,

perkalian,

pem-bagian,

perpangkatan

atau

persentase. Operator aritmetika

Simbol

Contoh

Hasil

Penambahan

+

=10+5

15

Pengurangan

-

=10-5

5

Perkalian

*

=10*5

50

Pembagian

/

=10/5

2

Perpangkatan

^

=10 A5

100000

Persentase

%

=10%

0,10


2

Apabila dalam formula terdapat beberapa operator aritmetika, urutan prioritas perhitungan adalah ada lah sebagai berikut: 1. 2.

Perhitungan yang diapit d iapit tanda kurung dilakukan paling dulu. Pada level yang sama, urutan perhitungan dilakukan menurut jenis operator. Perpangkatan dilakukan lebih dulu baru kemudian perkalian, pembagian, penambahan dan pengurangan.

3.

Perhitungan pada level dan operator yang sama dilakukan dari sebelah kiri dulu. b.Operator Perbandingan

Operator perbandingan digunakan untuk membandingkan dua nilai. Ketika dua nilai tersebut dibandingkan, dihasilkan nilai logika benar (TRUE) atau salah (FALSE) Simbol

Contoh

Hasil

Sama dengan

=

=10=5

FALSE

Lebih besar

>

=10>5

TRUE

Lebih kecil

<

=10<5

FALSE

Lebih besar sama dengan

>=

=10>=5

TRUE

Lebih kecil sama dengan

<=

=10<=5

FALSE

Tidak sama dengan

<>

=10<>5

TRUE

Operator perbandingan

C.Operator Teks

Operator teks digunakan untuk menghubungkan atau mengga-bungkan dua nilai teks atau lebih sehingga menghasilkan satu gabungan nilai teks. Operator teks

Menggabungkan teks

Simbol &

Contoh

Hasil

="Aku"&" "&"takut"

Aku takut

D. Operator Referensi

Operator referensi digunakan untuk menggabungkan beberapa sel (operator range) atau sebagai pemisah argumen. Operator referensi

Simbol

Operator range Operator pemisah argument B.

Contoh

=B1:B15 , atau ;

=COUNTIF(Bl:B2;"Pria")

PROGRAM PAKET PENGOLAH ANGKA

Microsoft Excel, atau biasa disebut dengan Excel, merupakan program spreadsheet (pengolah data) yang mempunyai kemampuan mengolah data secara luas pada bidang akuntansi, teknik, statistic, dan bidang-bidang lain yang memerlukan perhitungan denga cepat dan teliti. Excel dikenal sebagai program spreadsheet yang lebih mudah digunakan dibandingkan dengan program spreadsheet yang lainnya. Kemudahan tersebut terlihat jelas dar banyaknya fasilitas rumus siap pakai (fungsi) yang disediakan excel.Maka dari itu. Modul ini kan membahas lebih rinci pada program paket pengolah agkan dengan berbasis Microsoft Excel. EXCEL adalah sebuah program spreadsheet (lembar kerja yang terdiri dari kolom dan baris) yang berfungsi untuk mengolah data statistik, membuat laporan keuangan, anggaran, database sederhana, grafik, dan lainya. EXCEL telah berkembang hingga versi 2010, namun demikian perbedaan hanya terletak pada fitur dan menu dari versi-versi sebelumnya.


3

Pembahasan dan latihan pada modul ini menggunakan office excel versi 2007. Microsoft Excel juga merupakan program aplikasi penyajian dan pengolahan data yang cepat dan akurat untuk keperluan informasi data kuantitatif, baik dalam bentuk angka, tabel maupun grafik.Berikut adalah contoh tampilan fitur office EXCEL 2007.

Quick access toolbar

Tab

Toolbar

Microsoft Office Button

Formula bar Sel

Judul baris

Judul lembar kerja

Gambar: Tampilan workhseet Microsoft Excel 2007 Berikut ini menu-menu yang terdapat di MS Office Excel 2007 1. Home

Tab Home pada Excel 2007 terdiri atas 7 group yang dipresentasikan pada masing-masing toolbar, seperti Clipboard, Font, Alignment, Number, Styles, Cells, dan editing . 2. Insert

Tab Insert pada Excel 2007 terdiri atas 5 group yang dipresentasikan pada masing-masing toolbar, seperti Tables, Illustrations, Charts, Links dan Text. 3. Page layout

Tab page layout pada Excel 2007 terdiri atas 5 group yang dipresentasikan pada masingmasing toolbar, seperti Themes, Page Setup, Scale to Fit, Sheet Options, dan Arrange .

4. Formula

Tab Formula pada Excel 2007 terdiri atas 5 group yang dipresentasikan pada masing-masing toolbar, seperti Function Library, Defines Names, Formula Auditing, Calculation, dan Solution . 5. Data

Tab Data pada Excel 2007 terdiri atas 5 group yang dipresentasikan pada masing-masing toolbar, seperti Get External Data, Connection, Sort and Filter, Data Tools dan Outline . 6. Review

Tab Review pada Excel 2007 terdiri atas 3 group yang dipresentasikan pada masing-masing toolbar, seperti Proofing, Comment, dan Changes . 7. View

Tab View pada Excel 2007 terdiri atas 5 group yang dipresentasikan pada masing-masing toolbar, seperti Workbook Views, Show/Hide, Zoom, Window, dan Macros . C.

DATA 1. Pengertian

Data dalam program aplikasi pegolah angka merupakan informasi baik berupa tulisan, angka, symbol, dan informasi lainnya yang memungkinkan untuk di entri kedalam program aplikasi pengolah angka, dalam hal ini Microsoft Excel.Setiap spreadsheet, termasuk Microsoft excel, pertemuan antara kolom dan baris disebut dengan CELL.Contoh :E5 artinya pertemuan antara kolom E dan baris ke-5. Sedangkan RANGE adalah daerah tertentu (kumpulan dari beberapa cell). Contoh: A3:G3 (dibaca A3 sampai G3). Sekarang kita akan belajar memasukkan data-data ke dalam worksheet. Sebagai contoh kita akan memasukkan data singkat dibawah ini : DATA YANG AKAN DIMASUKKAN No Nama Umur 1 21 Nia Kurniati 2

Nanda A


20

5

DATA SETELAH DIMASUKKAN

Yang anda ketik akan nampak di kotak ini, tekan Enter untuk memasukkan

2. Entry Data sesuai Karakter Sel

Untuk melakukan entry data sesuai karakter sel, maka siperlukan langkah-langkah sebagai berikut: a. Aktifkan program spreadsheet b. Letakkan pointer di A1, ketik No, tekan enter c. Penekanan enter akan membuat No melompat ke dalam worksheet (dalam contoh ini ke A1), dan pointer langsung lompat ke baris berikutnya, dalam contoh ini ke A2. Langsung ketik angka 1 d. Bawa pointer ke B1 dengan anak panah kanan, ketik Nama, tekan enter e. Setelah di enter, pointer lompat ke B2, ketik Nia Kurniati enter f. Bawa pointer ke C1 dengan anak panah kanan, ketik Umur enter g. Pointer lompat ke C2, ketik 21 enter h. dst. Contoh lain cara memasukkan data adalah sebagai berikut: Klik salah satu sel yang anda inginkan, isikan data sesuai karakter

3. Menggunakan Formula untuk Memasukkan Data Dalam Range

Microsoft Excel memberikan fasilitas kepada para pemakai untuk memasukkan, mengubah, menghapus, atau menambah data yang lain. Anda tak perlu merasa ragu untuk melakukan input data, termasuk melakukan perubahan data yang pernah dimasukkan. Beberapa jenis data yang dapat dimasukkan meliputi: data teks, angka, tanggal, waktu dan rumus dalam sembarang sel. MS Excel secara otomatis akan mengenali data yang diinputkan sebagai data teks, angka, tanggal atau waktu, termasuk rumus. Pada saat memasukkan data dalam sel worksheet, pilih sel yang akan diisi dengan data, ketik data yang anda inginkan, dan tekan Enter atau Tab untuk memasukkan data yang telah anda ketik. Gunakan Enter untuk meletakkan pointer pada baris berikutnya dan Tab untuk meletakkan pointer pada kolom berikutnya pada saat anda memasukkan data yang telah anda ketik. Untuk membatalkan input data pada saat anda memasukkan data yang keliru pada sel, anda dapat menekan tombol Esc, atau tombol Backspace untuk menghapus data yang salah ketik pada saat anda sedang melakukan proses input data. Untuk memasukkan data dan membuat daftar dalam bentuk baris, masukkan data dalam sel pada kolom pertama, dan tekan Tab untuk berpindah ke sel samping kanannya. Pada akhir baris, tekan enter untuk berpindah ke awal baris berikutnya.

Untuk memasukkan nilai tanggal yang berlaku saat ini, tekan tombol perintah Ctrl+; (titik koma). Untuk memasukkan waktu yang berlaku saat ini, tekan Ctrl+Shift+: (titik dua). Apabila anda telah memilih beberapa sel dalam suatu range, Anda d apat menghemat waktu dalam memasukkan data sel ke sebuah range sel. Sebagai contoh, ikuti langkah-langkah berikut ini: 1. Misalnya, bentuk range B6:D11. Pada sel aktif B6, ketikkan data dan kemudian tekan tombol Tab, sehingga sel aktif akan berpindah ke sel C6. Tombol Tab berfungsi untuk memindah posisi sel aktif ke bagian kanan dalam range. 2. Setelah pointer mencapai posisi sel di ujung paling kanan range, dan Anda menekan tombol Tab, maka sel aktif akan pindah ke sel pada kolom pertama dan pada baris berikutnya dalam range. 3. Setelah semua data dalam range terisi, untuk menghilangkan pengaruh range atau bentuk range, tekan sembarang tombol anak panah. Untuk memindahkan posisi sel aktif dalam suatu range, Anda dapat menggunakan perintahperintah yang tercantum pada tabel di bawah ini:

D.

Memindah sel aktif dari

Tekan tombol

Atas ke bawah

Enter

Bawah ke atas

Shift + Enter

Kiri ke kanan

Tab

Kanan ke kiri

Shift + Tab

KARAKTER DATA 1.

Jenis data pada excel a. Number

Yaitu jenis data berupa angka yang bisa dimasukkan ke dalam sel dan dapat digunakan sebagai sumber data untuk melakukan perhitungan sebuah formulasi rumus. Di dalam sel otomatis jenis data ini merapat ke kanan. Dalam Excel, sebuah angka hanya terdiri atas karakter-karakter berikut: 0 1 2 3 4 5 6 7 8 9 + - ( ) , / $ % . E e Excel akan menghilangkan penulisan awalan simbol plus (+) pada setiap penulisan tanda titik (.) ssebagai tanda pemisah desimal. Tulisan kombinasi antara angka dan karakter bukan angka oleh Excel akan dianggap sebagai data bertipe teks. Karakter-karakter lain yang dikenal sebagai data angka tergantung pada pilihan yang dapat ditentukan dalam programRegional setting dari Control Panel. a. untuk menghindari kesalahan penulisan data angka yang berbentuk pecahan sebagai data tanggal, terlebih dulu awali penulisan data pecahan dengan angka nol (0) dan tambahkan spasi sebelum anda mengetikkan angka pecahan. Contoh, ketik 0 ¼ untuk mewakili penulisan angka 0.25. b. awali penulisan angka-angka negatif dengan simbol minus (-) atau ketik angka negatif tersebut di dalam tanda kurung ( ). Contoh: -1000 atau (1000) c. Pada kondisi default, semua data angka akan rata kanan dalam sebuah sel. d. Format angka hanya diberikan untuk sel-sel tertentu. Jika anda memasukkan data angka ke dalam sel, Excel akan memberikan format angka General. Namun jika anda


7

memasukkan angka $6,500, Excel akan memberikan format Currency untuk angka yang diinput. e. Dalam sel yang memiliki format angka General, Excel menampilkan angka sebagai bilangan bulat (500), pecahan desimal (5,00), atau notasi ilmiah (5,00E+08) jika angka tersebut terlalu panjang melebihi lebar kolom sel yang ada. Format General hanya mampu menampilkan angka sampai 11 digit, termasuk titik desimal (.) dan karakterkarakter lain seperti E dan +. b. Text Yaitu jenis data yang berupa karakter huruf/teks atau gabungan teks dengan angka. Jenis data teks ini akan bersifat merapat ke kiri di dalam sel. Dalam Excel data teks merupakan kombinasi antara angka, spasi, dan karakter yang bukan bertipe numerik. Untuk menampilkan dan melipat teks yang panjang dalam bentuk baris ganda dalam sebuah sel, pilih kotak cek Wrap Text pada Tab Alignment.Untuk memisahkan penulisan teks pada baris berikutnya dalam sebuah sel, tekan kombinasi tombol Alt + Enter. Dalam Microsoft Excel, data teks merupakan kombinasi antara angak, spasi, dan karakter yang bukan bertipe numerik. Microsoft Excel menganggap data 10AA109, 127AXY, 12-20006, 208 4675 sebagai data bertipe teks. Semua data bertipe teks akan diatur rata kiri dalam sel. c. Date and Time Yaitu jenis data yang berupa tanggal, bulan, dan tahun. Format data tanggal ini bisa diubah sesuai kebutuhan.Time yaitu data yang berupa jam, menit, detik. Format data waktu ini juga bisa diubah sesuai keinginan.Pilihan yang anda pilih dalam Regional Setting program Control panel menentukan format default yang berlaku untuk data tanggal dan waktu. Pada umumnya format tanggal dan waktu ditata dalam format United stated, dengan tanda titik dua (:) sebagai tanda pemisah penulisan waktu. Contohnya: 20:30:45. Slash (/) merupakan tanda pemisah penulisan data tanggal, contoh: 09/30/2009. Microsoft Excel menganggap data input tanggal dan waktu sebagai data-data angka. Untuk menampilkan data angka ke dalam data berbentuk tanggal atau waktu dalam worksheet tergantung pada format angak yang diberikan. Ketika Anda mengetikkan data tanggal atau waktu dalam sel worksheet, Microsoft Excel secara otomatis akan mengubah format angka dari format General menjadi format Date or Time. Pada kondisi default, tanggal dan waktu akanditampilkan rata kanan. Jika Microsoft Excel tidak menampilkannya dalam format Date or Time, data tanggal dan waktu tersebut akan diterima sebagai data teks serta tampil rata kiri. Pilihan yang Anda pilih dalam regional settings program Control Panel menentukan format default yang berlaku untuk data tanggal dan waktu. Pada umumnya format tanggal dan waktu ditata dalam sistem format United States, dengan tanda titik dua (:) sebagai tanda pemisah penulisan waktu. Contohnya 20:30:45. Slash (/) merupakan tanda pemisah penulisan data tanggal. Sebagai contoh, 09/30/2000. Untuk mengetikkan data tanggal dan waktu dalam sel, pisahkanlah penulisan data tanggal dan waktu dengan spasi. Sebagai contoh, 07/30/99 20:30:45. Untuk mengetikkan data waktu berdasarkan sistem 12 jam, ketik spasi diikuti dengan teks keterangan waktu AM atau PM (A atau P) setelah data waktu tersebut.


8

2.

Sebagai contoh, 10:30:30 AM.Selain itu, Microsoft Excel dapat digunakan untuk memasukkan data waktu berdasarkan sistem 24 jam.Sebagai contoh, 23:45:30. Microsoft Excel mampu untuk mengubah semua data tanggal sebagai data angka dan semua data waktu sebagai data pecahan desimal. Pilih sel yang berisi data tanggal atau waktu, pilih perintah pada tab Number, dan kemudian pilihGeneral dalam kotak Category. Data tanggal dan waktu dapat ditambah, dikurangi, dan dimasukkan dalam perhitungan suatu rumus/formula.Untuk menggunakan tanggal dan waktu dalam formula, masukkan data tanggal dan waktu sebagai sebuah data teks yang penulisannya diapit dengan tanda petik ganda (“). Sebagai contoh, rumus pengurangan =”09/21/2000” “07/19/2000” akan menghasilkan nilai 64. Dalam Microsoft Excel for windows, penulisan data tanggal dimulai dari January 1, 1900 yang diwakili dengan angka seri 1. Melihat Hasil Input Data Berdasarkan Jenis Data Isilah lembar kerja Excel anda dengan data-data pada tabel dibawah ini. Terlihat sel berisi teks akan merapat kekiri otomatis, dan sel berupa angka merapat ke kanan. Jika anda memasukkan angka 081329002207 pada kolom No telp, angka 0 di awal nomor telpon tidak akan kelihatan karena tidak dikenali sebagai angka. Untuk mengubah data number menjadi teks tambahkan tanda kutip („) di awal data angka.

a. Cara memasukkan data tanggal

Untuk mengisikan data tanggal anda tinggal memasukkan tanggal dengan format dd/mm/yy . Sebelum anda memasukkan data tanggal atau waktu, ada baiknya anda mengubah terlebih dulu format Regional Setting pada Windows menjadi Regional setting Indonesia. Caranya dengan mengklik Start>Control Panel>Regional and language>Regional option>Indonesia. Untuk memasukkan data tanggal, gunakan / (slash) atau – (minus) untuk memisahkan penulisan data tanggal. Sebagai contoh, ketik: 1/28/1972 atau 28-Jan-1972. Untuk memasukkan data waktu dengan sistem waktu 12-jam, ketik data waktu dan tambahkan spasi, kemudian ketik keterangan waktu a/p atau am/pm. Sebagai contoh, ketik 7:35:15 a. Excel memasukkan data waktu tersebut dengan tampilan data AM atau PM yang menunjukkan pembagian waktu. Apabila anda menggunakan sistem waktu 24-jam, ketik data waktu tanpa menyertakan keterangan waktu a/p atau am/pm. Catatan penting untuk aplikasi:







Untuk memasukkan data dan membuat daftar dalam bentuk baris, masukkan data dalam sel pada kolom pertama, dan tekan Tab untuk berpindah ke sel samping kanannya. Pada akhir baris, tekan Enter untuk berpindah ke awal baris berikutnya. Untuk memasukkan nilai tanggal yang berlaku saat ini, tekan tombol perintah Ctrl + ; (titik koma). Untuk memasukkan waktu yang berlaku saat ini, tekan Ctrl + Shift + : (titik dua).

b. Memasukkan data dengan cepat

Untuk memasukkan data yang sama ke dalam beberapa sel pada saat yang bersamaan dapat dilakukan dengan prosedur: a.

pilih sel-sel di mana anda ingin diisi data

b. ketik data dan tekan Ctrl + Enter Sebagai contoh, bentuk range mulai dari sel A1 sampai B4, ketik teks SOLOPOS di sel A1, kemudian tekan Ctrl + Enter sehingga teks yang diketik akan diisikan pada setiap sel yang terpilih.

Gambar: Memasukkan data input secara bersamaan c. Cara merubah data kelipatan ribuan Menu Comma berguna untuk memberikan tanda titik (.) untuk memisahkan angka dengan kelipatan ribuan. Lihat gambar berikut:

E.



Isikan data tabel secara utuh tanpa tanda baca



Blok dan sorot seluruh isi data, klik tanda Comma pada menu bar.

Gambar: Menampilkan penulisan Comma KARAKTER SEL Setiap worksheet terdiri atas Sel dan Range.Sel adalah kotak yang merupakan pertemuan antara sebuah kolom dan sebuah baris. Setiap sel memiliki nama sesuai dengan alamat kolom dan alamat baris, misalnya A dan baris 1, maka dinamakan sebagai SelA1. Kolom A

Baris 1

Gambar: sel A1

Range adalah dua sel atau lebih di dalam sheet. Contoh SelA1, SelA2, selA3 disebut Range A1:A3.

Gambar: Range A1:A3 Microsoft excel memiliki tiga macam alamat sel, yaitu sel relatif, sel absolut dan sel campuran. Sel relatif adalah sel yang apabila dicopy akan menyesuaikan dengan alamat baru secara otomatis. Contoh, jika mengcopy formula yang berisi alamat selA1 ke selA2, maka secara otomatis akan menyesuaikan dari =A1 ke =A2.

Gambar: alamat sel relatif Pada gambar di atas, alamat sel C3 di copy ke bawah dan tampak kolom alamat sel tetap sedangkan baris sel berubah sesuai tempat baru. Sel absolut adalah sel yang selalu mengacu pada alamat sel tertentu.Sel absolut ditandai dengan tanda $. Alamat sel absolut yang mengandung formula jika di copy ke sel lain, maka alamat baris maupun kolomnya tidak akan berubah.

Gambar: alamat sel absolut

Gambar: alamat sel campuran Alamat Sel Aktif Alamat sel aktif merupakan sebuah kotak yang menampilkan sel yang sedang aktif.Seperti pada gambar dibawah ini, alamat sel aktif menampilkan A3 sesuai sel yang sedang ditunjuk pointer. Dengan kata lain Sel Aktif adalah Sel terpilih, dimana data yang ada pada sel bersangkutan dapat diedit.

Gambar: Alamat sel aktif


11

F.

FUNGSI DAN FORMULA MATEMATIKA

Formula atau rumus adalah persamaan yang dimasukkan untuk melakukan perhitungan terhadap nilai-nilai yang ada dalam worksheet. Sedangkan fungsi adalah suatu rangkaian rumus yang terdiri atas beberapa argumen yang tersusun sedemikian rupa dan dapat langsung digunakan sesuai dengan kategorinya. Hampir semua perhitungan yang kita gunakan dalam kegiatan sehari-hari menggunakan matematika, baik dalam bentuk perhitungan yang sederhana maupun perhitungan yang rumit.Oleh karenanya fungsi matematika yang ada pada Excel sangat penting untuk kita pahami dan kita praktekkan bagaimana menggunakannya. 1. Memanfaatkan Fungsi SUM untuk Menjumlahkan Sel di Beberapa Range Fungsi SUM biasanya adalah fungsi pertama yang digunakan pemakai Excel. Tapi tahukah Anda Bagaimana caranya agar fungsi SUM dapat digunakan menghitung beberapa range data sekaligus. Caranya adalah sebagai berikut. 1) Seperti pada gambar 1, isikan sel A4:A12 dengan data-barang yang diterima dalam suatu periode. 2) Pada sell D4:F12 isikan dengan data-data barang yang keluar pada periode yang sama. 3) Untuk menghitung berapa jumlah seluruh barang yang ada saat ini. Dituliskan formula sebagai berikut: =SUM(A4:A12;B4:B12;C4:C12)-SUM(D4-D14;E4:E12;F4:F12)

2.

4) Tekan 5) Perhatikan cara merangkai range-range yang dijumlahkan dengan fungsi SUM seperti yang digunakan pada contoh diatas. 6) Pemisah argument pada gambar diatas berupa ; (titik koma). Jika pada computer anda tidak bisa menerima, gantikan dengan koma (“,”). Demikian pula pada gambar lain yang anda jumpai di buku ini. Hal tersebut karena tidak konsistennya sistem Vista terhadap Excel 2007. Memanfaatkan Fungsi SUMIF untuk Menentukan Sales Team Fungsi SUMIF menjumlahkan isi sel-sel yang disebutkan sesuai dengan kriteria yang dibutukan. Bentuk penulisannya: =SUMIF(range,criteria,sum_range) Range adalah range dimana data-datanya akan dievaluasi. Criteria, kriteria atau syarat dari isi sel range yang harus dijumlahkan. Sel-sel didalam Sum_Range hanya akan dijumlahkan bila berhubungan dengan sel pada range yang cocok dengan kriteria yang diberikan. Jika argumen Sum_range tidak ditulis maka sel dalam range akan dijumlahkan, karena dianggap semua memenuhi kriteria. Contoh pertama penggunaan fungsi SUMIF berikut ini untuk menjumlahkan berapa jumlah keseluruhan penerimaan yang nilainya diatas 5 dan berapa jumlah pengeluaran keseluruhan yang nilainya dibawah 6. 1) Kita gunakan kembali tabel pada contoh kasus sebelumnya seperti sheet yang ada. 2) Pada sel E14 ketikkan formula berikut: =SUMIF(A4:C12;”>5”). 3) Pada sel E15 ketikkan formula berikut: =SUMIF(D4:F12;”<6”). 4) Tekan setelah mengetikkan formula.


12

5) Contoh yang kedua adalah penggunaan fugsi SUMIF untuk menjumlahkan masingmasing besarnya POIN yang diperoleh regu 1, 2 dan 3. 6) Langkahnya : 

Pada sel A2:A10 ketikkan nama-nama regu yang terdiri atas 1, 2, dan 3.



Pada sel B2:B10ketikkan nama-nama salesman.



Pada Sel C2:C10 ketikkan jumlah poin yang mereka dapatkan



Pada E2:E24 ketikkan nama regu masing-masing 1, 2, dan 3.



Bloklah sel F2:F4, kemudian ketikkan formula sebagai berikut. =SUMIF($A$2:$A$10;E2;$C$2:$C$10)



3.

Tekan .

Memanfaatkan Fungsi SUMIF untuk menghitung Biaya yang Dikeluarkan Hingga Tanggal Tertentu.

Keterangan fungsi SUMIF telah kami uraikan pada contoh sebelumnya.Berikut ini contoh pemanfaatan SUMIF untuk menghitung biaya yang dikeluarkan hingga tanggal tertentu. Langkahnya sebagai berikut : 1) Pada sel A2:A13 isikan tanggal-tanggal dimana tercatat pengeluaran biaya. 2) Pada sel B2:B13 isikan biaya yang dikeluarkan dalam jutaan Rupiah. 3) Pada Sel E1 Ketikkan tanggal yang dikehendaki sebagai batas pengeluaran biaya. 4) Pada sel E2 ketikkan formula sebagai berikut. =SUMIF(A2:A13;”<=&E1;B2:B13)

5) Tekan 4.

Memanfaatkan Fungsi COUNTIF untuk Menghitung Biaya yang Jumlahnya Rp500 Juta atau Lebih

Apabila Anda perlu menyelesaikan pekerjaan untuk menghitung d ata tetapi yang diinginkan hanya data-data tertentu yang sesuai dengan kriteria, maka fungsi COUNTIF adalah fungsi yang tepat untuk dipakai.Fungsi ini berguna untuk menghitung sel-sel yang berisi data dengan kriteria tertentu. Cara penulisannya: =COUNTIF(range,criteria)

Range adalah sebuah blok sel yang dicari berapa sel kosongnya. Criteria adalah syarat yang diberikan untuk menghitung data. Berikut contoh worksheet pada kasus sebelumnya, kali ini untuk mempraktekkan bagaimana pemanfaatan COUNTIF. 1) Pada sel A2:A13 isikan tanggal pengeluaran biaya. 2) Pada sel B2:B13 isikan jumlah biaya yang dikeluarkan dalam jutaan rupiah. 3) Pada E1 ketikkan kriteria biaya yang dikehendaki dalam jutaan Rupiah. 4) Pada E2 ketikkan formula =COUNTIF(B2:B13;”
Menggunakan COUNTIF untuk Menghitung Daftar Hadir.

Menghitung daftar hadir sangat diperlukan oleh bagian penghitung gaji karyawan. Dengan Fungsi COUNTIF Anda akan dengan mudah melakukan perhitungan kehadiran karyawan. Worksheet pada sheet 5 merupakan contoh bagaimana menghitung kehadiran karyawan serta menghitung berapa karyawan yang hadir. Langkah-langkahnya sebagai berikut.


13

1) Pada sel A2:A13 isikan tanggal-tanggal sebuah periode kerja, yaitu tanggal 31 Maret hingga 11 April 2010. 2) Pada B2:B13 isikan nama-nama hari dari tanggal kolom di A. 3) Pada C2:G13 isikan kehadiran karyawan, dimana H sebagai tanda hadir dan A sebagai tanda Absen. 4) Bloklah sel H2:H13, kemudian ketikkan formula: =COUNTIF(C2:G13;”H:), tekan Enter. 5) Copy-kan formula di C15 ke sel D15:G15. 6.

Memanfaatkan Fungsi COUNTIFS

COUNTIFS adalah fungsi baru sejak Excel 2007. Gunanya untuk menghitung jumlah data dalam satu atau beberapa range yang memenuhi satu atau beberapa kriteria. Cara penulisannya : =COUNTIFS(range1,criteria1[,range2,criteria2…])

Range1, range2, satu atau beberapa range yang akan dihitung jumlah datanya berdasarkan kriteria yang cocok. Criteria1, criteria2 adalah satu atau beberapa kriteria yang diisyaratkan untuk mencari data. Contoh penggunaan : 1) Buatlah tabel seperti contoh pada sheet 6. 2) Pada sel A9 ketikkan formula; =COUNTIFS(B3:D3;”ya”)

3) Pada sel A11 ketikkan formula: =COUNTIFS(B2:B6;”=ya”;C2:C6”=ya”)

4) Pada sel A13 ketikkan formula: =COUNTIFS(B5:D5;”ya”;B3:D3;”=ya”)

5) Setelah selesai, masing-masing tekan Enter 7.

Memanfaatkkan Fungsi SUMPRODUCT untuk Menghitung Nilai Inventory

Fungsi SUMPRODUCT berguna untuk mengalikan komponen yang cocok dengan yang diberikan pada array, lalu menjumlahkannya : Cara penulisannya: =SUMPRODUCT(array1,array2,array3, . . . ) Array1, array2, array3, adalah 2 hingga 30 array dimana kompone nnya ingin Anda kalikan, kemudian dijumlahkan . Argumen Array harus mmemiliki dimensi yang sama, sebab bila tidak akan menghasilkan nilai kesalahan #VALUE. Berikut contoh penyelesaian kasus dalam perhitungan nilai Inventory. 1) Sel A2:A13 berisi daftar harga satuan dari Inventory. 2) Sel B2:B13 berisi jumlah Inventory. 3) Untuk menghitung dengan cepat berapa nilai keseluruhan inventory pada sel B14 ketikkan formula: =SUMPRODUCT(B2:B13;A2:A13) dan tekan Pada sel D2:D13 diisikan perkalian biasa untuk memeriksa apakah perhitungan dengan SUMPRODUCT pada sel B14 hasilnya sama. Isikan formula =A2*B2 dan tekan G. FUNGSI DAN FORMULA STATISTIK Penggunaan fungsi statistik sangat penting dalam menghitung banyak hal, oleh karenanya Excel menyediakan banyak fungsi-fungsi yang dibutuhkan dalam bidang statistik.


14

1.

Memanfaatkan Fungsi MAX untuk Mencari Nilai Tertinggi dan MIN untuk mencari Nilai Terendah Suatu Range Data

Salah satu kegiatan dalam statistik adalah mencari nilai yang terbesar maupun yang terkecil. Apabila dalam sebuah tabel yang memiliki banyak data, maka anda bisa memanfaatkan fungsi MAX dan MIN untuk menyelesaikannya. Fungsi MAX untuk menghasilkan data numerik dengan nilai maximum yang terdapat dalam suatu range data. Cara penulisannya : =MAX(number1,number2, ….) Number1, number2, adalah 1 hingga 30 bilangan yang akan dihitung mana yang terbesar.

Fungsi MIN untuk menghasilkan nilai data numerik terkecil yang terdapat dalam suatu range. Cara penulisannya : =MIN(number1,number2,….) Number2, number2, adalah 1 hingga 30 bilangan yang akan dihitung mana yang terkecil

2.

Gambar pada sheet-1 Worksheet dengan satu range data dimana kita harus mencari nilai data terbesar dan terkecil. Untuk menyelesaikan kita harus mengetikkan formula sebagai berikut : Mencari data terbesar : =MAX(A3:H12) Mencari data terkecil : =MIN(A3:H12) Memanfaatkan Fungsi Index MATCH dan LARGE untuk Menentukan Salesman Terbaik dan Selisihnya dengan Terbaik ke-2

Mencari salesman terbaik dengan menampilkan jumlah penjualannya, lalu menampilkan nama orangnya. Demikian juga jumlah penjualan salesman terbaik nomor dua dan menampilkan nama orangnya. Masih ditambah lagi mencari berapa selisih hasil penjualan antara salesman terbaik dan terbaik nomor dua. Untuk pekerjaan seperti itu, kita akan menggabungkan fungsi INDEX, MATCH dan LARGE. Berikut ini cara pengerjaan dari tugas-tugas di atas sebagaimana yang dimuat pada file 6 sheet-2. 1)

Buatlah tabel A3:B11 seperti pada file tersebut.

2)

Untuk

3)

4) 3.

mencari

salesman

terbaik

pada

D

ketikkan

formula

=INDEX($A$4:$A$11;MATCH(LARGE($B$4:$B$11;1);$B$4:$B$11;0)), lalu tekan Enter Menampilkan salesman terbaik nomor 2 pada sel D6 formulanya sama dengan yang terbaik, hanya pada fungsi LARGE argument “k” -nya diganti dengan 2 (terbaik nomor 2. Jadi, penulisan formulanya sebagai berikut: =INDEX($A$4:$A$11;MATCH(LARGE($B$4:$B$11;2);$B$4:$B$11;0)) lalu tekan Enter. Untuk menampilkan nilai Deal salesman terbaik dengan formula =LARGE(B4:B11;1)LARGE(B4B11;2)

Mengkombinasikan Fungsi INDEX, MATCH, dan SMALL untuk MemilihPenawaran Paling Rendah.

Pekerjaan ini adalah kebalikan dari contoh kasus sebelumnya yang dapat diselesaikan menggunakan gabungan antara fungsi INDEX, MATCH, dan SMALL Pada worksheet di sheet-3 untuk mencari siapa supplier yang jumlah nilai penawarannya paling rendah, pada sel F16 ketikkan formula: SMALL(F4:F14;1)


15

Sedangkan untuk menawarkan siapa nama supplier dengan jumlah penawaran terendah gunakan formula sebagai berikut : =INDEX($A$4: $A$11;MATCH(LARGE($B$4: $B$11;1);$B$4:$A$11;0) 4.

Memanfaaatkan Fungsi COUNT dan COUNTA untuk menghitung Jumlah Data.

Fungsi Count berguna untuk menghitung berapa jumlah bilangan numerik yang ada dalam suatu range. Data teks dan string (alphabet) tidak akan dihitung, selain itu sel kosong yang tidak berisi data tidak dihitung. Fungsi COUNTA mirip dengan fungsi COUNT, hanya bedanya COUNTA menghitung seluruh data yang berisi numeric maupun alphabet yang terdapat pada suatu range. Sel yang kosong tidak ikut dihitung. Pada file 6 sheet 4 menunjukkan simulasi bagaimana memanfaatkan penggunaan fungsi COUNT dan COUNTA untuk menghitung jumlah data yang tersimpan dalam suatu range. Langkah-langkah pengerjaannya sebagai berikut : 1) Buatlah tabel data seperti yang terlihat pada sel B3:I16. Jumlah sel pada range tersebut 112. Isikan pada sel G18. 2) Menghitung jumlah data dengan fungsi COUNT pada sel G19 dengan formula: =COUNT (B3:I16). 3) Menghitung jumlah data dengan fungsi COUNTA pada sel G20 dengan

formula:

=COUNTA(B3:I16). 4) Menghitung jumlah sel kosong lakukan dengan menuliskan formula: =G18-G20 pada sel G21. 5.

Memanfaatkan Fungsi RANK untuk mengurutkan Ranking Murid Sesuai Jumlah Nilai Akumulatif

Fungsi RANK pada Excel akan menghasilkan ranking suatu bilangan diantara sekumpulan data. Misalnya dalam suatu kelas terdapat 14 orang siswa (Anda bisa mengubahnya menjadi 400 misalnya).Ninta nilainya 89, dengan fungsi ini, Anda dengan mudah dapat menghitung posisi Ninta pada ranking ke berapa. Apabila seluruh nilai siswa tersebut di-sort, maka RANK akan menunjukkan urutan ke berapa dalam sort tersebut. Cara penulisannya: =RANK(number,ref,order) Number adalah nilai atau angka yang akan dicari rangkingnya. Ref adalah alamat range atau array yang berisi keseluruhan data. Order adalah angka yang menyatakan bagaimana meranking bilangan. Langkah pengerjaan contoh pada gambar 6 sheet-5. 1) Buatlah tabel seperti gambar berikut. Sel A4:A16 isikan nama-nama siswa. Anda dapat mengembangkan jumlah siswa lebih banyak lagi. 2)

Pada sel B4:B16 isikan nilai akumulatif dari masing-masing siswa.

3)

Untuk menyusun ranking siswa, bloklah sel C4:C16, lalu ketikkan

formula:

=RANK(B4;$B$4:$B$16) lalu tekan Enter. Anda bisa membandingkan hasil fungsi RANK dengan cara mengurutkan menggunakan tombol sort, dimana hasilnya seperti gambar berikut.


biasa

16

6.

Memanfaatkan Fungsi MEDIAN untuk Mencari Nilai Tengah Suatu Data

Fungsi MEDIAN menghasilkan median dari sekumpulan data.Median adalah nilai tengah dari nilai-nilai yang ada pada sekumpulan data. Cara penulisannya : =MEDIAN(number1, number2, ….)

Number1, number2, adalah 1 hingga 30 bilangan yang akan dicari mediannya. Untuk mempraktekan pemanfaatan fungsi ini, lakukan sebagai berikut. 1) Buatlah tabel data pada sel B2:I18 dengan data seperti contoh atau Anda isikan data numeric Anda Sendiri.

H.

2)

Pada sel I20 tuliskan formula: =MEDIAN(B2:I18)

3)

Pada sel I21 tuliskan formula: =MAX(B2:I18).

4)

Pada sel I22 tuliskan formula: =MIN(B2:I18)

5)

Pada sel I23 tuliskan formula: =AVERAGE(B2:I18).

6)

Padas el I23 tuliskan formula: =COUNT(B2:I18).

7)

Sedangkan pada sel I25 formulanya: =Sum(B2:I18)

FORMULA DAN FUNGSI FINANSIAL 1. Memanfaatkan Fungsi SYD untuk Menghitung Nilai Depresiasi

Apabila kita akan menghitung nilai Depresiasi suatu investasi, maka yang kita perlukan adalah fungsi financial SYD. Fungsi SYD berguna untuk menghasilkan jumlah seluruh nilai depresiasi atau penyusutan suatu aset. Cara penulisan fungsi SYD: =SYD(cost, salvage, life, per ) C o s t adalah harga beli aset yang yang akan disusutkan nilainya. Salvage adalah nilai pada akhir (nilai sisa) setelah penyusutan. L i f e , jumlah periode selama aset disusutkan nilainya.Kadang-kadang juga disebut dengan

istilah “usia pakai aset”. Per adalah periode penyusutan dimana satuan waktu yang dipakai harus sama dengan

argument life. Pada sheet 1 terdapat contoh kasus pada worksheet yang pengerjaannya sebagai berikut: 1) Pada sel C3 isikan harga pembelian aset akan dihitung depresiasinya. 2) Sel C4 isikan beberapa tahun aset tersebut akan didepresiasi. 3) Sel C5 dengan nilai yang anda kehendaki setelah selesai periode depresiasi a. Pada sel C7 untuk menghitung berapa nilai aset setelah depresiasi tahun yang ke-5, digunakan formula sbb: =SYD($C$5,$C$4,5) 4) Tekan 5) Pada sel A10:A17 isikan hingga 8, yaitu tahun-tahun pada periode depresiasi. Masukkan berupa nilai numerik, jadi anda tidak bisa mengisikan misalnya “Tahun1”,Tahun2”, … dan seterusnya. 6) Pada sel B10 isikan nilai awal aset, yaitu sama seperti yang tertulis pada sel C3. Selanjutnya bloklah sel B11:B17 isikan formula =B10-C10 lalu tekan . 7) Bloklah sel C10:C17, lalu ketikkan formula: 8) Tekan .


17

9) Bloklah sel D10:D17, lalu ketikkan formula =B10-C10 dan tekan . 2. Memanfaatkan Fungsi SLN untuk Menghitung Nilai Straight Line Depreciation

Cara lain yang lazim untuk melakukan perhitungan depresiasi ialah dengan sistem Straight Line Depreciation, yaitu nilai penyusutan sepanjang tahun dan sepanjang periode depresiasi. Untuk itu Excel menyediakan fungsi SLN untuk menghasilkan nilai penyusutan suatu aset secara garis lurus dalam suatu periode. Cara penulisan fungsi SLN: =SLN(cost, salvage, live) C o s t adalah harga beli aset yang akan disusutkan nilainya. Salvage adalah nilai pada akhir penyusutan. L i v e adalah jumlah periode selama aset disusutkan nilainya. Kadang-kadang juga disebut

dengan istilah “usia pakai aset” Perbedaan yang dihasilkan fungsi SYD dan SLN, jika dengan fungsi SYD dan SLN dengan fungsi SYD besarnya nilai depresiasi semakin menurun setiap tahun, sementara pada SLN nilai Depresiasi sejak awal hingga akhir sama. Demikian pula pada worksheet pada sheet 2 cara penyelesaiannya sama, yang berbeda adalah formula yang dipakai pada sel C7 dan pada blok C10:C17 adalah =SLN($C$3,$C$5,$C$4) 3. Memanfaatkan Fungsi PV untuk Menetukan Nilai Investasi

Fungsi PV berguna untuk menghasilkan present value atau nilai kini dari suatu investasi.Nilai kini adalah jumlah nilai pinjaman modal yang anda terima.Baik atau buruknya nilai ini ditentukan dengan mengurangi pendapatan yang diterima dari laba investasi yang kembali ditanam.Jika nilai pendapatan itu ternyata lebih besar dari biaya investasi ini dinyatakan baik. Cara penulisan fungsi PV sebagai berikut: =PV(rate, nper, pmt, fv, type) Rate adalah besarnya prtesentase bunga setiap periode. Nper adalah jumlah periode pengembalian dalam tahun. Pm t , banyaknya periode angsuran per periode. Fv , besarnya nilai pada akhir jangka waktu pinjaman. Jika tidak diisi akan dianggap nilainya 0. Pada pinjaman ke bank Fv nilainya 0. T y p e , terdiri atas 0 untuk in arear, 1 untuk in advance.

Pada sheet-3 adalah worksheet contoh kasus untuk menghitung nilai investasi saat ini yang diselesaikan menggunakan fungsi PN. Adapun langkah-langkah pengerjaannya sebagai berikut: 1) Tetapkan besarnya target laba yang diinginkan setiap tahun dan isikan di sel C3. 2) Pada sel C4 isikan banyaknya periode tahun (lamanya pinjaman modal) 3) Pada C5 isikan besarnya bunga modal. 4) Pada sel C6 isikan formula =PV(C5,C4,C3) lalu tekan enter. 4. Memanfaatkan Fungsi PMT untuk Menentukan Pembayaran Suatu Pinjaman

Apabila anda berencana mengambil kredit suatu aset, kemudian anda ingin menghitung berapa besarnya angsuran bila lamanya pinjaman selama periode tertentu dan dengan nilai bunga tertentu pula. Untuk keperluan tersebut Excel mempunyai fungsi PMT yang sangat berguna untuk menghasilkan besarnya angsuran yang harus dibayar setiap bulan


18

berdasarkan metode angsuran tetap (nilainya sama) dengan angsuran bunga sesuai besarnya pinjaman. Formula ini menghasilkan angka negatif, karena dianggap mengurangi besarnya pinjaman. Cara penulisan fungsi PMT: =PMT(rate,nper,pv,fv,type) Rate adalah besarnya prosentase bunga setiap periode (tahun). Nper adalah jumlah kali pembayaran angsuran dalam satu tahun. PV adalah besarnya nilai pinjaman awal. FV adalah besarnya nilai pada akhir jangka waktu pinjaman. Jika argumen ini tidak diisi maka Excel akan menganggap FV nilainya=0. Pada pinjaman ke bank, FV nilainya=0 Type adalah angka 0 atau 1 dimana 1 untuk angsuran yang langsung pada saat pinjaman diterima (dikenal dengan in advance) dan 0 untuk angsuran yang dibayar satu periode setelah pinjaman diterima (dikenal dengan in arear), pilihan terakhir ini tidak dapat diisi. Uraian dalam menyelesaikan worksheet ada pada gambar . 1)

2)

Hitunglah besarnya angsuran per tahun menggunakan formula: =ABS(PMT($C$2/12;$C$3;C1) Penggunaan fungsi ABS didepan fungsi PMT agar hasil formula menampilkan bilangan positif. PMT menghasilkan nilai negative karena fungsinya untuk mengurangi kas (pengeluaran) Tabel dibawah untuk mencocokkan bagaimana logika kerja perhitungan PMT untuk menghasilkan pembayaran angsuran, dimana pada sel B9 diisikan =C1 yang merupakan nilai pinjaman yang akan diangsur. Pada sel E9 diisi dengan =$C$4.

3)

Bloklah sel B10:B19, lalu isikan dengan formula =B9-C10 kemudian tekan .

4)

Bloklah sel C10:C19, lalu isikan formula =E10-D10, kemudian tekan .

5)

Bloklah sel D10:D19 lalu ketikkan formula =(B9*$C$2)/12, kemudian tekan .

6) Copy-kan sel E9 ke sel E10:E19 Untuk meyakinkan lakukan cek-in dengan membuat kolom cek. Bloklah sel G10:G19 lalu isikan formula =C10+D10 lalu tekan . Untuk menghitung besarnya angsuran per bulan tinggal membagi angsuran per tahun dengan 12. 5. Memanfaatkan Fungsi FV untuk Menghitung Nilai yang Akan Datang Suatu Investasi. Jika kita memiliki suatu investasi atau pinjaman bank, kemudian kita ingin menghitung berapakah nilai investasi kita setelah 5 tahun yang akan datang, maka kita dapat menghitung dengan bantuan fungsi FV fungsi ini dipergunakan untuk menghitung nilai yang akan datang dari suatu investasi atau pinjaman bank pada suatu periode, apabila diperhitungkan dengan angsuran dan dengan bunga tetap. Cara penulisan funggsi ini: =FV(rate,nper,pmt,pv,type) Rate adalah besarnya prosentase suku bunga setiap periode. Nper adalah jumlah berapa kali pembayaran angsuran dalam satu tahun. PMT adalah besarnya angsuran setiap periode. PV, pada fungsi ini PV berarti besarnya nilai sekarang.(Present Value). Jika Argumen Pv tidak ditulis maka akan dianggap bernilai 0.


19

Type adalah angka 0 atau 1 dimana untuk angsuran yang langsung pada saat pembayaran diterima (dikenal dengan in advance) dan 0 untuk angsuran yang dibayar satu periode setelah pinjaman diterima (dikenal dengan in arear), pilihan terakhir ini tidak dapat diisi. Penyelesaian contoh kasus pada sheet 5 seperti berikut ini. 1) Untuk menghitung nilai yang akan datang pada sel B4 ketikkan formula =FV(B1/12;B2;B3) kemudian tekan Enter. 2) Untuk melakukan cross cek buatlah tabel seperti yang terlihat pada sel A6:D11, dimana pada B7 isikan =$B$3, sel C7 isikan =B7*($B$1/12) dan isikan =B7+C7. 3) Pada sel B8 ketikkan =D7+$B$3, lalu copy-kan kebawah hingga A11. 4) Copy-kan ke C7 kebawah hingga B10.

I.

5) Copy-kan ke C7 kebawah hingga D10. Setelah langkah tersebut diatas anda akan memperoleh nilai pada sel B11 cocok untuk dengan hasil perhitungan dengan fungsi FV di sel B4. FUNGSI DAN FORMULA DATE-TIME Excel menggunakan perhitungan tanggal dan waktu berdasarkan hari. Setiap hari diwakili dengan angka 1 hingga 2,958,465.Nilai 1 mewakili tanggal 1 Januari 1900 dan angka 2,958,465 mewakili tanggal 31 Desember 9999. Apabila anda memasukkan tanggal dal;am format yang dapaat diterima oleh Excel, maka tanggal tersebut akan direkam sebagai nilai seri yang mewakili tanggal tersebut adalah hari keberapa bila dihitung dari tanggal 1 Januari 1900. Sebagai contoh, nilai 100,000 yang berarti 100,000 hari dari 0 Januari 1990, yaitu tanggal 14 Oktober 2173. Dalam sehari terdapat 24 jam yang diperhitungkan dengan nilai desimal yang mewakili bagian antara jam 12:00 tengah malam hingga12:00 malam berikutnya. Jam 12:00 siang dihitung sebagai 0,5, karena dapat dianggap setengah hari perjalanan hari. Jam 10:15:03 AM memiliki format general angka desimal 0,427118055555556, ddan tanggal 1 Januari 2000 jam 07.30.00 memiliki format general 36526.3125. dengan cara itulah Excel melakukan perhitungan aritmatik terhadap tanggal dan waktu. Bagaimana memasukkan data tanggal dan waktu agar dapat diterima excel? Ketikkanlah data dengan cara seperti yang ditulis pada kolom sebelah kiri daftar berikut ini pada sel maupun formula bar. 3/4

Bulan/tanggal

3/4/09

Bulan/tanggal/tahun

03/04/09

Bulan dua digit/tanggal dua digit/tahun dua digit

4-Mar

Tanggal – bulan dalam tiga karakter

4-Mar-09

Tanggal – bulan dalam tiga karakter

04-Mar-09

Tanggal dua digit – bulan dalam tiga karakter – tahun dua digit

Mar-09

Bulan dalam tiga karakter – tahun dua digit

March-09 March 4,2010 3/4/09 1:30 PM

Bulan ditulis lengkap – tahun dua digit Bulan ditulis lengkap, tanggal, tahun empat digit Bulan/tanggal/tahun dua digit – jam:menit AM/PM

3/4/09 13:30

Bulan/tanggal/tahun dua digit – jam:menit dengan penulisan secara langsung tanpa menyebut AM/PM Untuk format penulisan waktu sebagai berikut. 13:30


Jam dan menit dengan penyebutan 24 jam

20

1:30 PM

Jam, menit dan keterangan waktu.

13:30:55 30:55:7

Jam, menit, detik dengan penyebutan 24 jam. Bentuk format penulisan banyaknya waktu 30 jam, 55 menit, dan 7 detik. 3/4/09 1:30 PM Bulan, Tanggal, Tahun, Jam dan Menit disertai kerangan waktu. 3/4/09 13:30 Bulan, tanggal, tahun, jam dengan penyebutan 24 jam, menit 1. Mengombinasikan Data Penanggalan Menggunakan Fungsi Date Apabila anda memiliki data-data bagian penanggalan yang tersimpan pada kolom-kolom yang berbeda, maka hal ini dapat disatukan menggunakan fungsi DATE. Sebagaimana susunan penulisan pada fungsi DATE, yaitu = DATE (tahun, bulan, tanggal), maka susunan data pada tabel terdiri atas tahun, bulan (dalam angka) dan tanggal. CATATAN : Argumen tahun dapat diisikan 1 hingga 4 digit, dengan dasar bahwa Micrisoft Excel for Windows menggunakan sistem waktu 1900. Artinya, jika anda memasukkan argumen tahun dengan angka 7, maka akan dianggap masukan untuk tahun 1907. Jika anda memasukkan argumen dengan anggka 10 maka akan dianggap masukkan tahun 2099, karena 1900 ditambah 199 = 2099. Argumen Bulan harus diisikan bilangan yang mewakili bulan, yaitu 1 untuk Januari, 2 untuk Februari dan seterusnya. Excel akan menerima masukkan nilai 13 untuk bulan pertama tahun berikutnya, dan seterusnya. Argumen Tanggal adalah bilangan yang mewakili tanggal dalam suatu bulan. Apabila bilangan tersebut melebihi jumlah tanggal dalam suatu bulan dalam suatu bulan, maka ia akan diperhitungkan ke tanggal berikut pada bulan berikutnya. Misalnya anda mengetikkan tanggal dengan nilai 31 pada bulan April (padahal bulan April hanya sampai tanggal 30), maka akan dianggap sebagai tanggal 1 Mei. Demikian seterusnya. Untuk menggabungkan elemen tanggal pada masing-masing kolom bloklah sel D4:D16 kemudian isikan formula = DATE(A4,B4,C4) lalu tekan




Perhatikan sel yang diberi warna abu-abu. C8 diisikan nilai 31, maka pada hasil perhitungan ditampilkan 1 Mei 2003, yaitu satu hari setelah tanggal 30 April 2003. 

B13 tidak diisi nilai nilai Bulan, tetapi dalam perhitungan ditampilkan 13 Desember 2007. Hal ini karena sel kosong diberi nilai kurang dari 1 sehingga dihitung sebagai satu bulan sebelum tahun 2008, yaitu Desember 2007. 2. Mengurai Data Tanggal Berformat Text Menjadi Berformat Tanggal dengan Fungsi LEFT, 

MID, RIGHT

Apabila anda mengimpor data dari sebuah database, sehingga data tanggal ditampilkan dalam format teks. Untuk mengubah ke format tanggal, kita dapat memanfaatkan fungsi LEFT, MID dan RIGHT. Dalam hal ini penggunaan masing-masing fungsi Excel sebagai berikut (berdasarkan worksheet pada file ). Untuk memasukkan formula dan fungsi tersebut, langkahnya sebagai berikut. 1) Bloklah sel B5:B10 lalu letakkan formula: =DATE(LEFT(A4,4),MID(A4,6,2),RIGHT(A4,2)) 2) Tekan


21

Keterangan penggunaan Fungsi: LEFT untuk mengambil angka digit pertama hingga digit keempat. LEFT(A4,4) menghasilkan 2004. Data ini difungsikan sebagai argumen tahun pada fungsi DATE. MID untuk mengambil angka mulai digit ke-6 sebanyak 2 digit. =MID(A4,6,2) menghasilkan nilai 11 dan dijadikan sebagai argumen Bulan dari Fungsi DATE. RIGHT untuk mengambil dua digit dari kanan. =RIGHT(A4,2) menghasilkan nilai 21 dan dijadikan argumen Tanggal pada fungsi DATE: Apabila tampilan format tanggal disel B4:B10 kurang sesuai dengan keinginan a nda, maka dapat diubah dengan menekan Ctrl+1 lalu buatlah setting. 3. Menggunakan Fungsi Text Untuk Mencari Bagian dari tanggal Kasus contoh berikut ini mirip dengan contoh nomor sebelumnya, hanya susunan bilangan pada data tanggal berformat teksnya berbeda, maka pengambilan digit angka dilakukan dari sebelah kanan. Kali ini fungsi yang dipakai justru TEXT dulu baru DATE sedangkan untuk mengambil masing-masing angka dalam argumen DATE digunakan fungsi RIGHT dan MID dua kali, tanpa menggunakan fungsi LEFT seperti contoh sebelumnya. 





Langkahnya sebagai berikut. 1) Buatlah worksheet seperti sheet 3. 2) Bloklah sel B4:B12, kemudian ketikkan formula: =TEXT(DATE(RIGHT(A4,4),MID(A4,3,2),MID(A4,1,2)), “DD-MMM-YYYY”). 3) Tekan . Penjelasan penulisan formula. Fungsi TEXT, untuk mengubah suatu bilangan berformat teks ke suatu format tertentu yang dikehendaki. Cara penulisannya =TEXT(nilai,,bentuk format yang dikehendaki). Pada contoh ini kita akan menuangkan formatnya dalam bentuk “DD-MMM-YYYY. Fungsi DATE, cara dasar penulisan tanggal, bulan, tahun agar dapat diterima oleh Excel cara penulisannya: =DATE(tahun,bulan,tanggal). Susunan argumen tersebut menjadi syarat masukan agara data dapat diterima Excel. Selanjutnya setelah dapat diterima, kita dapat mengubah format tampilannya sesuai kehendak kita menggunakan format yang tersedia.







Fungsi LEFT dan MID seperti yang telah diuraikan pada kasus latihan sebelumnya.

4. Menggunakan Fungsi Text Untuk Berbagai Macam Kasus

Fungsi ini digunakan untuk menuliskan tanggal dengan format tahun, bulan, hari. Excel sebenarnya telah dilengkapi dengan berbagai format tanggal secara otomatis, namun jika pembacaan tanggal tersebut mengacu pada suatu nilai atau sel tertentu, anda dapat menggunakan fungsi ini. Berikut adalah penggunaan berbagai fungsi tanggal dan waktu: 1) Fungsi DATE:

=DATE(2008,9,21)

=DATEVALUE("8/7/2008")


22

2) Fungsi DAY: BENTUK PENULISAN FUNGSI

=DAY(SERIAL_NUMBER)

>>> TANGGAL

HASI

Rumus

12 Agustus 2008

12

=DAY(B6 )

3) Fungsi DAYS360

Menghasilkan jumlah hari dari dua tanggal yang dihitung, dengan asumsi setahun = 360 hari BENTUK PENULISAN FUNGSI =DAYS360(START_DATE;END_DATE;METHOD)

>>>

TGL AWAL

TGL AKHIR

START_DATE

END_DATE

01 Januari 2008

31 Agustus 2008

JUMLAH HARI

BULAN

240

8

=DAYS360(B8;C8)

=D7/30

4) Fungsi EOMONTH:

Digunakan untuk mencari suatu tanggal akhir bulan berdasarkan tanggal awal yang telah ditetapkan dengan interval waktu (satuan bulan) ke depan atau ke belakang. BENTUK PENULISAN FUNGSI

=EOMONTH(START_DATE;MONTHS)

>>> TANGGAL AWAL (START_DATE) BULAN (MONTHS) HASIL PENULISAN FUNGSI

01 Januari 2008

31-Agust-08

12

20

31 Januari 2009

30 April 2010

=EOMONTH(D6,D7)

=EOMONTH(E6,E7)

5) Fungsi HOUR:

Menghasilkan angka (bilangan bulat) dari suatu jam (waktu) tanpa menyebut menit dan detik. Angka yang dihasilkan mulai dari 0 sampai dengan 23. BENTUK PENULISAN FUNGSI

=HOUR(SERIAL_NUMBER)

>>> WAKTU (SERIAL_NUMBER)

HASIL (JAM)

11:45:00 AM

11

=HOUR(B8)

2:15:00 PM

14

=HOUR(B9)

12:00:00 PM

12

=HOUR(B10)

10:25:00 PM

22

=HOUR(B11)

12

=HOUR("12:15:30")


FUNGSI

23

6) Fungsi NOW:

Menghasilkan Tanggal dan Waktu saat ini dengan format tanggal dan waktu. BENTUK PENULISAN FUNGSI

=NOW()

>>> NOW()

HASIL

05/09/2009

=NOW()

10:43

7) Fungsi TODAY

Menghasilkan Tanggal saat ini dengan format tanggal BENTUK PENULISAN FUNGSI

=TODAY()

>>> NOW()

HASIL

=TODAY()

05/09/2009

8) Fungsi YEAR:

Menghasilkan angka yang menunjukkan tahun (tanpa bulan dan tanggal) dari serangkaian nomor seri atau tanggal tertentu. BENTUK PENULISAN FUNGSI =YEAR(SERIAL_NUMBER)

>>>

J.

TANGGAL

HASIL

PENULISAN

NOMOR

HASIL

PENULISAN

SERIAL_NUMBE

(TAHUN)

FUNGSI

SERIAL_NUMBE

(TAHUN)

FUNGSI

17/09/2008

2008

=YEAR(C8)

49873

2036

=YEAR(G8)

10 Juni 2015

2015

=YEAR(C9)

2008

=YEAR(39656)

FUNGSI GRAFIK

Untuk membuat tampilan dalam penyajian laporan Excel menjadi lebih baik dan menarik, Anda dapat menyajikan data dalam bentuk tabel dengan format yang menarik, sehingga mudah dianalisis dan dipahami oleh orang lain. Laporan tidak saja bisa ditampilkan dan disajikan dalam bentuk tabel, dalam spreadsheet juga bisa dikombinasikan laporan dalam bentuk tabel dan grafik. 1. Menyajikan Data Dalam Bentuk Grafik/Diagram Grafik bisa menampilkan data secara visual sehingga memudahkan untuk melihat perbandingan, pola maupun trend yang berlaku pada data tersebut. Sebagai contoh, daripada harus menganalisis laporan yang ditulis dalam beberapa kolom, akan lebih mudah untuk menentukan apakah terjadi kenaikan atau penurunan penjualan dalam periode tertentu bila laporan ditampilkan dalam bentuk grafik. Data yang ditampilkan dalam grafik bisa berasal dari sheet yang berbeda. Hal ini akan memudahkan pengambilan data yang ingin ditampilkan dalam bentuk grafik.


24

MS Excel menyediakan berbagai macam tipe grafik sehingga kita bisa memilih tipe yang cocok dan sesuai dengan keinginan.Data dan grafik yang dibuat juga dapat dicopy dan ditampilkan melalui MS Word ataupun MS Power Point. Grafik adalah penampakan secara visual dari sebuah data.Kita bisa melihat perbandingan, pola dan trend data pada grafik.Beberapa istilah atau pengetahuan teori tentang grafik dapat diikuti berikut ini. Data Marker 

Data marker adalah sebuah batang, titik, potongan atau simbol lain dalam grafik yang mempresentasikan masing-masing data atau nilai yang berasal dari sel worksheet. 

Judul



Judul adalah teks deskrtif yang terletak di atas, bawah axis X dan samping axis Y. Rangkaian Data Titik data yang berhubungan yang digambarkan dalam grafik.Masing-masing rangkaian data mempunyai warna atau pola tertentu yang ditampilkan dalam tulisan (legend) grafik.





Grid (garis)

Garis yang ditambahkan ke sebuah grafik untuk membuat grafik tersebut mudah di lihat dan di evaluasi.Sebuah garis melintang dalam daerah gambar dimulai dari titik yang menampilkan angka/nilai. Gridline (Mayor) Nilai poros (axis value) yang ditampilkan dari data worksheet.



Gridline (Minor)



Garis minor menandai interval dalam interval garis mayor. Nama Kategori Excel menggunakan heading kolom atau baris dalam data worksheet untuk nama kategori.

2. Komponen Diagram Excel

Sebelum membuat diagram atau grafik terlebih dulu Anda siapkan tabel data sebagai sumber diagrafm. Tabel data tersebut terdiri dari data yang disusun dalam bentuk baris dan kolom.Perhatikan gambar dibawah ini.

Dari data tersebut di atas, terlihat data dalam seri baris yang menunjukkan jumlah penjualan dari barang-barang diantaranya hardisk, monitor, printer, dan soundcard.Selain itu data tersebut juga menjelaskan penjualan dari masing-masing barang per taun antara tahun 2005 sampai tahun 2009.Dengan data dalam bentuk baris dan kolom tersebut, maka Anda


25

dapat membuat diagram dalam bentuk tampilan sesuai dengan seri baris dan kolom tersebut.Berikut adalah contoh gambar grafik. Walls

Chart Area

Plot Area

Legend

Value axis

Category Axis

Series axis

Dari gambar di atas, terlihat beberapa bagian antara lain: 1. Chart Area adalah area diagram yang dibentuk yang terdiri dari judul diagram (chart title), plot area, legend (keterangan gambar) dan lain-lain 2. Axis adalah sumbu diagram (grafik), yang terdiri dari sumbu X yang posisinya tegak dan sumbu Y yang posisinya datar. Pada diagram 3 Dimensi terdapat sumbu Z. 3. Data seri adalah sekumpulan data yang tersusun dalam baris atau kolom. 4. Data point adalah nilai data sebagai unsur terbentuknya diagram. 5. Legend adalah keterangan dari simbol (gambar) diagram yang menggambarkan bagian data yang sama 6. Plot Area adalah sebuah area yang berbentuk kotak yang berisi sumbu X dan sumbu Y 7. Walls adalah dinding yang metarbelakangi diagram dan hanya ada pada diagram 3 dimensi. 3. Letak Diagram Excel Pada saat proses pembuatan grafik, Anda dapat meletakkan diagram tersebut pada lembar tersendiri (chart sheet), sebagai wadah diaktifkannya diagram. Dengan demikian akan memudahkan Anda dalam memilih dan melakukan perubahan, karena tidak akan terganggu dengan tampilan lainnya. Disamping itu cara meletakkan diagram juga dapat dilakukan pada lembar kerja yang sama dengan yang ditempati data. Artinya pada sheet tabel data juga berisi tampilan diagram. Cara seperti ini disebut dengan embedded chart, yaitu menempelkan objek diagram pada lembar kerja.


26

4. Memilih Tipe-tipe grafik

Tipe-tipe grafik bisa dipilih melalui menu bar pilih menu insert, kemudian pilih Chart. Klik drop down pada tombol tersebut sehingga muncul pilihan tipe grafik. Drag tipe-tipe tersebut untuk menjadikannya toolbar seperti gambar berikut:

Gambar: toolbar chart type Klik grafik yang akan anda ubah tipenya kemudian klik salah satu tombol pada Chart type tersebut maka grafik yang akan anda sorot akan langsung berubah sesuai tipe pada tombol yang anda tekan. Sebelum membuat diagram (grafik) terlebih dulu anda siapkan tabel data sebagai sumber (source) diagram. Tabel data tersebut terdiri dari data yang disusun dalam bentuk baris dan kolom, seperti gambar di bawah ini. Langkah-langkah untuk membuat grafik adalah sebagai berikut: 



Aktifkan data table Blok data mulai dari A6 SAMPAI E10


27



Klik Insert kemudian pilih Chart pada standar Toolbar



Pada bagian Chart type, pilih Column, pilih All Chart Type

Gambar: Tampilan Chart Type

K.



Pilih salah satu,



Hasil input data ke dalam Data range adalah berikut ini:

FUNGSI DAN FORMULA SEMI ABSOLUT, ABSOLUT, DAN LOGIKA 1.

Menggunakan Fungsi AND untuk Membandingkan Data di Dua Kolom

Sebuah data terdiri atas dua kolom.Kedua data pada kedua kolom ini perlu dievaluasi, apakah kolom pertama nilainya lebih besar dari 25, dan kolom kedua lebih besar dari 35. Jika kedua syarat tersebut benar maka akan diahasilkan TRUE, sedangkan jika tidak akan dihasilkan FALSE. Untuk pekerjaan ini digunakan fungsi AND yang cara penulisannya sebagai berikut : =AND(logika1, logika2, ...) Logika1, logika2, ... argumennya 1 sampai 30 kondisi yang ingin anda tes, apakah hasilnya TRUE atau FALSE. Argumennya harus berisi nilai logika atau Array, atau referensi yang berisi nilai logika. Untuk mengerjakan latihan pada gambar 4, langkahnya sebagai berikut : 1) Isikan pada kolom A dan B nilai secara acak antara A dan B nilai secara acak antara 1 hingga 100. 



2) Paste sel C3:C14 tuliskan formula tersebut sebagai berikut : =AND(A3>25,B3>35) 3) Tekan

28

Contoh aplikasi fungsi-fungsi:

Masukkan data di bawah ini ke dalam lembar kerja Anda:

Masukkan formula rumus berikut ini ke dalam sel D3: =AND(A3>0;B3>0;C3>0). Hasil dari rumus ini adalah FALSE . Sebab, nilai pada salah satu kondisi salah, yaitu pada sel C3 yang lebih kecil dari 0. Sedangkan syaratnya >0.

Jika kita ganti rumusnya menjadi: =AND(A3>0;B3>0;C3<0) maka nilainya menjadi TRUE.

2.

Menggunakan Fungsi OR Untuk Memeriksa Sel yang Berisi Teks Contoh kasus kali ini untuk memeriksa apakah data teks pada kolom A berisi “Excel 2007”. Jika benar menghasilkan TRUE dan jika salah akan menghasilkan FALSE.

Sekalipun data yang akan diperiksa berupa teks, kita juga bisa menggunakan fungsi AND, namun kali ini kita akan memanfaatkan fungsi OR. Fungsi ini dipergunakan untuk mengambil suatu keputusan diantara dua argumen yang diberilkan. Fungis ini akan meghasilkan TRUE (atau menjalankan pilihan) bila hasilnya benar, sebaiknya akan menghasilkan FALSE (atau tidak menjalankan pilihan) bila hasilnya salah. Cara penulisannya:=OR(logical1,logical2, ...) Logikal1, logikal2, ... argumennya 1 sampai 30 kondisi yang ingin anda tes, apakah hasilnya TRUE atau FALSE. 

Argumennya harus berisi nilai logika atau array, atau referensi yang berisi nilai logika. Pada contoh diatas, fungsi OR pada teks membandingkan dua data dan keduanya harus benar agar memperoleh hasil FALSE. Sekarang kita bandingkan bagaimana bila fungsi OR bagaimana bila fungsi OR digunakan untuk menguji data yang berupa angka. Pada contoh berikut ini kita gunakan data pada kasus yang pertama dan fungsi penguji akan diganti dengan OR. Ternyata dari sheet-2 kita melihat bahwa dengan fungsi OR akan menghasilkan nilai TRUE sekalipun data yang memenuhi hanya satu. Itulah maksud dari Fungsi OR, logika1 atau logika 2, sedanngkan fungai AND menguji Logika dan logika 2 kedua-duanya harus selesai. 


29

Contoh aplikasi:

Formula = OR (TRUE) akan menghasilkan nilai TRUE karena satu argumen dari formula bernilai TRUE. Formula =OR(2+2=5,4+4=6) akan menghasilkan nilai FALSE karena semua argumen dari formula bernilai FALSE. Formula =OR(TRUE,FALSE,TRUE) akan menghasilkan nilai TRUE karena salah satu argumen bernilai TRUE. Untuk mencoba fungsi OR, Anda bisa mengisikan formula rumus berikut ini ke dalam sel E3: =OR(A3>0;B3>0;C3>0). Terlihat, walaupun nilai sel C3>0 salah, namun karena salah satu kondisi benar (True) maka nilai formula rumus tersebut adalah TRUE.

3.

Menggunakan Fungsi IF untuk Membandingkan Data pada Dua Kolom untuk Mendapatkan Hasil tertentu.

Jika pada fungsi AND dan OR Excel menghasilkan nilai TRUE atau FALSE, maka fungsi IF dapat pula digunakan untuk melakukan perbandingan seperti itu. Fungsi IF merupakan fungsi logika yang paling banyak dipergunakan. Contoh kasus berikut ini penggunaan fungsi IF untuk mengambil keputusan kelas A atau kelas B yang menjadi pemenang, dimana hal yang sama tidak bisa digantikan langsung oleh fungsi AND dan OR. 1) 2)

Kolom A berisi nilai kelas A, kolom B berisi nilai kelas B. Bloklah sel C4:C11, lalu ketikkan formula yang akan menguji siapa pemenangnya sebagai berikut: =IF(A4>=B4,”Pemenangnya Kelas A”,”Pemenangnya Kelas B”

3) Tekan Bagaimana jika fungsi IF digunakan untuk melakukan perbandingan empat tingkat. Misalnya seperti contoh kasus berikut ini. 

Jika nilai lebih dari 89 peringkat A.



Jika nilai lebih besar dari 80 peringkat B.

Jika nilai lebih besar dari 70 peringkat C. Jika nilai lebih besar dari 60 peringkat D, kalau tidak lebih besar dari 60 nilainya E. Penyusunan formula diawali dengan syarat nilai tertinggi lalu berturut-turut yang lebih rendah.  

1) Sel A17:A23 isikan nama pemain. 2) Sel B17:B23 isikan nilai antara 1 hingga 100 3) Bloklah sel C17:C23 lalu ketikkan formula sebagai berikut: =IF(B17>89,”A”,IF(B17>80,”B”,IF(B17>70,”C”,IF(B17>60,”D”,”E”)))) Contoh penggunaan fungsi IF:

Ketikkan data-data yang terdapat tabel berikut ini ke dalam lembar kerja Excel Anda.


30

Untuk mengisikan secara otomatis nama-nama hardware ke dalam kolom E serta mengisikan secara otomatis harganya ke dalam kolom F berdasarkan kode-kode hardware yang terdapat pada kolom C (Kode Hardware), ikuti langkah berikut: Yang diinginkan adalah jika: Kode Hardware A, Nama Hardware: Mouse, Harga:120.000, Kode Hardware B, Nama Hardware: Monitor, Harga:800.000, Kode Hardware C, Nama Hardware: Printer, Harga:500.000, Kode Hardware D, Nama Hardware: DVDROOM, Harga:300.000, Ketikkan rumus berikut ini pada formula bar untuk mengisi secara otomatis kolom E dengan nama hardware berdasarkan kode pada kolom Kode Hardware (kolom C): =IF(C8=”A”;”Mouse”;IF(C8=”B”;”Monitor”;IF(C8=”C”;”Printer”;”DVDROOM”)))

Copy rumus ini ke bawah hingga sel E14.

Untuk mengisi otomatis kolom Harga (Kolom F) berdasarkan Kod e Hardware di kolom C, isikan rumus berikut di kolom F: =IF(C8=”A”;120000;IF(C8=”B”;800000;IF(C8=”C”;500000;500000)))

4.

FUNGSI VLOOKUP Vlookup digunakan untuk pengolahan tabel secara vertical. Fungsi VLOOKUP

digunakan untuk mencari nilai di kolom kunci pembacaan (paling kiri) dan memberi fungsi


31

VLOOKUP besar nilai di baris yang sama dari sebuah kolom yang anda tentukan dalam sebuah tabel. Huruf V dalam VLOOKUP berarti Vertikal. Bentuk persamannya adalah: =VLookup(nilai kunci;nama table;nomor

Atau: =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Keterangan: Lookup_value: nilai sebagai dasar pembacaan tabel Table_array: range tabel data yang dibaca Col_index_num (nomor indeks kolom): nomor kolom di tabel yang harus menghasilkan nilai yang sesuai. Nomor indeks kolom bernilai 1 haruslah merupakan nilai di kolom pertama tabel.Nomor indeks kolom 2 menunjukkan nilai kolom kedua dari tabel. Range_lookup: range yang akan dijadikan acuan atau merupakan nilai logic yang berupa TRUE (apabila tabel data diurutkan secara menaik) dan FALSE (jika tabel data tidak diurutkan). Fungsi HLookup dan VLookup ini sebenarnya tidak jauh berbeda dengan menggunakan fungsi formula IF. Hanya saja, jika data yang dimasukkan telalu banyak, lebih mudah dan lebih praktis menggunakan fungsi HLookup dan VLookup.Berikut adalah contoh penggunaan VLOOKUP: Berikut adalah contoh input nilai siswa berdasarkan perolehan semesteran. Kita akan mengisi kolom D dan kolom E dengan fungsi VLOOKUP.

Rumus untuk masing-masing sel adalah sebagai berikut: Sel D3 : =Vlookup(C3;$A$11:$C$15;2) Sel D4 : =Vlookup(C4;$A$11:$C$15;2) Sel D5 : =Vlookup(C5;$A$11:$C$15;2) Sel D6 : =Vlookup(C6;$A$11:$C$15;2) Sel D7 : =Vlookup(C7;$A$11:$C$15;2)

Sel E3 : =Vlookup(C3;$A$11:$C$15;3) Sel E4 : =Vlookup(C4;$A$11:$C$15;3)

Sel E5 : =Vlookup(C5;$A$11:$C$15;3) Sel E6 : =Vlookup(C6;$A$11:$C$15;3) Sel E7 : =Vlookup(C7;$A$11:$C$15;3)

Hasil akhir input fungsi Vlookup adalah berikut ini:

5.

Fungsi HLOOKUP Hlookup digunakan untuk pengolahan tabel secara horizontal. Fungsi Hlookup

digunakan untuk mencari nilai di baris yang menjadi kunci pembacaan (paling atas) dan memberi fungsi HLOOKUP besar nilai dari kolom yang sama dari sebuah baris yang anda tentukan dalam formula. Huruf H dalam HLOOKUP berarti HORIZONTAL. Bentuk persamaannya adalah: =HLookup(nilai kunci;nama table;nomor

Atau: =VLOOKUP(lookup_value,table_array,row_index_num,range_lookup) Keterangan: Lookup_value: nilai sebagai dasar pembacaan table Table_array: range tabel data yang dibaca Row_index_num: (nomor indeks baris): nomor baris di tabel yang harus menghasilkan nilai yang sesuai. Nomor indeks baris bernilai 1 haruslah merupakan nilai di baris pertama tabel. Nomor indeks baris 2 menunjukkan nilai baris kedua dari tabel. Range_lookup: range yang akan dijadikan acuan atau merupakan nilai logic yang berupa TRUE (apabila tabel data diurutkan secara menaik) dan FALSE (jika tabel data tidak diurutkan). Rumus untuk masing-maing sel adalah sebagai berikut: Sel D3 : =Hlookup(C3;$B$10:$F$12;2) Sel D4 : =Hlookup(C4;$B$10:$F$12;2) Sel D5 : =Hlookup(C5;$B$10:$F$12;2) Sel D6 : =Hlookup(C6;$B$10:$F$12;2) Sel D7 : =Hlookup(C7;$B$10:$F$12;2)

Sel E3 : =Hlookup(C3;$B$10:$C$12;3) Sel E4 : =Hlookup(C4;$B$10:$C$12;3) Sel E5 : =Hlookup(C5;$B$10:$C$12;3) Sel E6 : =Hlookup(C6;$B$10:$C$12;3) Sel E7 : =Hlookup(C7;$B$10:$C$12;3)

Hasil akhir input fungsi Hlookup adalah berikut ini:

6.

Fungsi LOOKUP

Fungsi LOOKUP mempunyai dua bentuk persamaan umum, yaitu vektor dan array. Bentuk persamaan vektor dari fungsi LOOKUP adalah sebagai berikut: =LOOKUP(lookup_value,lookup_vector,result_vector) Keterangan: Lookup_value: nilai sebagai dasar pembacaan tabel Table_vektor : range tabel data yang hanya berisi satu baris atau satu kolom. Nilai Lookup_vector : harus berada dalam urutan yang makin naik:…, -2,-1,0,1,2,…A-

Z,FALSE,TRUE; urutan yang menurun akan memberikan hasil yang tidak benar. Result_vector : range yang hanya berisi satu baris atau satu kolom. Jumlahnya harus sama dengan lookup_vector. Contoh aplikasi penggunaan fungsi LOOKUP adalah berikut ini: Berikut adalah daftar kalori yang dibutuhkan untuk mnyelesaikan suatu aktivitas atau pekerjaan: FORMULA1=LOOKUP(1,A3:A9,B3:B9) FORMULA2=LOOKUP(4,A3:A9,B3:B9) FORMULA3=LOOKUP(7.5,A3:A9,B3:B9) FORMULA4=LOOKUP(0,A3:A9,B3:B9) Formula 1 akan melihat nilai 1 di kolom A dan memberi hasil perhitungan akhir dengan memberi nilai dari kolom B baris yang sama, yaitu; MENGAJAR.

Modul Spreadsheet Kelas X-1word-Fix

Recommend Documents