KD 3.4. Menerapkan logika dan operasi perhitungan data

 

KD 3.4 Menerapkan logika dan operasi perhitungan data

A.       Perangkat Lunak Pengolah Angka

Microsoft Excel atau Microsoft Office Excel adalah sebuah program aplikasi lembar kerja spreadsheet yang dibuat dan didistribusikan oleh Microsoft Corporation untuk sistem operasi Microsoft Windows dan Mac OS. Aplikasi ini memiliki fitur kalkulasi dan pembuatan grafik. Dengan menggunakan strategi marketing Microsoft yang agresif, menjadikan Microsoft Excel sebagai salah satu program komputer yang populer digunakan di dalam komputer mikro hingga saat ini.

Microsoft Excel merupakan aplikasi yang berorientasi pada pengolahan angka. Microsoft Excel mampu mengolah angka untuk fungsi matematika, fungsi statistika, fungsi keuangan, dan fungsi logika. Bahkan, perhitungan gaji karyawan, baik dengan neraca maupun laporan keuangan dapat dilakukan dengan Microsoft Excel. Pada pembelajaran ini, Anda akan mempelajari Microsoft Excel 2016. Adapun tampilan awal Microsoft Excel 2016 sebagai berikut.

 

 

Nama Fitur	Fungsi
Quick Access Toolbar	Kumpulan tombol pintas yang sering digunakan Ribbon untuk perintah umum. Bisa ditambah maupun dikurangi, tetapi secara default terdiri dari Save, Undo, dan Redo
Ribbon	Berisi kumpulan tab yang sudah disusun berdasarkan kategori tertentu, misalnya Home, Insert Page Layout dan Formulas. Setiap tab berisi beberapa kelompok perintah, misalnya Font, Alignment, dan Number.
Cell	Sel adalah setiap persegi panjang di dalam workbook yang merupakan persimpangan antara kolom dan baris
Column	Kolom adalah sekumpulan sel yang bergerak dari atas ke bawah halaman, yang diidentifikasi dengan huruf
Formula bar	Lokasi untuk memasukkan atau edit data, formula, atau fungsi yang ada pada sel tertentu.
Scroll between worksheet	Digunakan untuk menggeser worksheet. Lembar kerja (worksheet) atau disebut juga buku kerja (workbook). Setiap lembar kerja memiliki satu atau lebih lembar kerja, misalkan sheet1, sheet2,...., dan seterusnya. Klik Kanan untuk pilihan lainnya dan klik Tab untuk beralih di antara mereka.
Click to add new worksheet	Digunakan untuk menambahkan worksheet baru
Normal view	Merupakan tampilan mode standar menunjukkan jumlah sel dan kolom yang tidak terbatas.
Page layout view	Tampilan ini akan membagi worksheet menjadi halaman
Page break view	Pilihan untuk menampilkan overview worksheet, khususnya sangat membantu ketika ditambahkan page break.
Scroll bar	Berada di bagian bawah dan bagian kanan yang berfungsi untuk menggeser tampilan ke atas bawah Atau kanan kiri.

 

1.      Operasi Perhitungan MS Excel 

Salah satu fitur yang paling hebat di Excel adalah informasi numerik menggunakan rumus (formula). Sama seperti kalkulator, Excel dapat menambah, mengurangi, mengalikan, dan membagi. Program Excel memiliki empat jenis operasi yang berbeda, yaitu aritmatika, perbandingan, penggabungan teks, dan acuan. Semua rumus harus dimulai dengan tanda sama dengan (=). Hal ini karena sel berisi rumus dan nilai yang akan dihitung. 

a.    Operasi Aritmatika

Operasi aritmatika ini digunakan untuk membentuk operasi-operasi dasar seperti penambahan, pengurangan, pembagian, perkalian, eksponensial, persentase, dan penggabungan bilangan dan atau menghasilkan nilai numerik.

Tanda operasi arimatika dalam excel sebagai berikut

b.    Operasi Perbandingan

Operasi perbandingan digunakan untuk membandingkan dua buah nilai. Hasil perbandingan tersebut berupa nilai logika, yaitu true atau false. Berikut ini adalah tanda operasi perbandingan dalam Excel.

Tipe Perbandingan	Simbol	Contoh	Hasil
Sama dengan	=	= 5 = 8	False
Lebih besar dari	>	= 9 > 4	True
Lebih kecil dari	<	= 3 < 2	False
Lebih besar sama dengan	>=	= 4 * 3 >= 12	True
Lebih kecil sama dengan	<=	= 12 – 3 <= 5	False
Tidak sama dengan	<>	= 2 <> 3	True

 Untuk menambah pemahaman Anda perhatikan contoh berikut. Lakukan operasi berikut ini dengan menggunakan Microsoft Excel 2016.

1) 10 +15

2) 7 x 4

3) 50 : 2

 

Untuk menyelesaikan operasi hitung di atas, lakukan langkah-langkah berikut.

1) Tulis bilangan pertama pada kolom A dan bilangan kedua pada kolom B.

2) Ketik tanda sama dengan (=) di kolom C.

3) Klik sel yang akan dihitung pada kolom A, misalnya sel A1.

4) Gunakan simbol yang sesuai pada operator yang digunakan.

5) Klik sel yang akan dihitung pada kolom B, misalnya sel B1.

6) Tekan Enter.

 Langkah-langkah melakukan operasi hitung di atas dapat dilihat pada gambar berikut.

Jika terdapat perubahan pada salah satu sel masukkan, hasilnya pun akan berubah sesuai dengan masukan. Hal ini karena pengaruh referensi sel (cell references), yang akan memperbaharui data dalam kerja tanpa harus menulis ulang rumus.

Perhatikan contoh berikut 

 

Pada contoh diatas, sel A1 dimodifikasi dengan cara mengubah nilai dari 10 menjadi 85. Ketika ditekan tombol enter, rumus Cell C1 secara otomatis akan menghitung ulang dan menampilkan nilai baru, dari nilai 25 menjadi 100. Jika hasil dari formula terlalu besar, akan tampil pada sel tersebut sebagai tanda pound (#######) bukannya nilai. Ini berarti kolom tidak cukup lebar untuk menampilkan isi sel. Cukup lebarkan kolom untuk menunjukkan isi sel tersebut.

 c.    Operasi penggabungan

Operasi penggabungan teks digunakan untuk menggabungkan dua data yang bertipe teks (string). Operasi penggabungan teks memungkinkan kita menggabungkan dua teks dari dua kolom yang berbeda ke dalam satu kolom. Operator penggabungan teks menggunakan teks simbol ampersand (&). Jika digunakan dalam rumus yaitu = A1 & B1.

 2.      Menggunakan rumus

a.    Operator Acuan

Operator acuan berfungsi untuk menggabungkan selang sel yang terlibat dalam penghitungan. Excel mempunyai 3 buah operator acuan, yaitu titik dua (:),koma, dan spasi. Beberapa simbol operator acuan sebagai berikut.

Simbol	Arti	Contoh
Titik dua(:)	Operator jangkauan menghasilkan satu acuan dari sel diantara dua acuan,termasuk kedua acuan tersebut	A7 : A20
Koma (,)	Operator penyatuan menggabungkan banyak acuan menjadi satu acuan.	SUM(B5:B15),D%:D15
(spasi)	Operator perpotongan mengacu pada sel yang menjadi perpotongan antara dua acuan	(A2:C5,E3:E6)

b.    Mengatur operasi di rumus Excel

Excel memiliki tingkatan operasi yang lebih didahulukan operasinya, jika kita menggunakan beberapa operasi dalam satu rumus. Exo akan melakukan operasi terlebih dahulu pada operator yang tingkatnya lebih tinggi. Tingkatan operasi tersebut adalah sebagai berikut 

Operator	Keterangan
Titik dua (: ), Spasi tunggal(), Koma(,)	Operator Acuan
-	Tanda Negatif
%	Presentase
^	Pemangkatan
* dan/	Perkalian dan pembagian
+ dan -	Penambahan dan pengurangan
&	Menggabungkan dua string

 c.    Menggunakan tanda kurung

Tanda kurung ini digunakan untuk memberitahukan kepada Excel bahwa urutan perhitungannya harus mendahulukan bilangan-bilangan operasi yang berada di dalam kurung tersebut terlebih dahulu baru kemudian operasi yang lain.

 d.    Menggunakan acuan

Acuan mengidentifikasi sebuah sel atau Kumpulan sel dari sebuah lembar kerja dan mengatakan kepada operator di mana mencari nilai-nilai atau data yang digunakan dalam rumus. Macam-macam acuan sebagai berikut. 

Set Acuan	Rumusan
Sel di kolom B baris 4	B4
Selang sel di kolom E baris 1 sampai 15	E1:E15
Selang sel di baris 6 dan kolom A sampai D	A6:D6
Semua sel di baris 8	8:8
Semua sel di bari 2 sampai 12	2:12
Semua sel di kolom K	K:K
Semua sel di kolom D sampai H	D:H
Semua sel di kolom B sampai E dan baris 3 sampai 9	B3 : E9

 e.    Mengcopy rumus

Agar menghemat energi dan fikiran, excel memberikan fasilitas copy rumus. Hal ini memudahkan Anda Menghitung data dalam jumlah yang banyak. Perhatikan gambar berikut ini.

Untuk menulis hasil nomor 1 dan seterusnya, tuliskan rumus pada baris pertama saja (baris kelima), kemudian copykan ke baris di bawahnya. Caranya adalah klik sel yang akan di-copy kemudian Letakkan pointer pada pojok kanan bawah sel tersebut dengan drag lalu tarik ke bawah sampai baris terakhir (baris ke-9)

Perhatikan gambar berikut 

f.    Membedakan Relatif Dan Absolut Cell Reference

Referensi relatif (relatif) dan referensi mutlak (absolute) berbeda cara kerjanya ketika disalin dan diisi dengan sel lainnya. Referensi relatif akan berubah ketika formula disalin ke sel lain. Sebaliknya, referensi mutlak akan tetap konstan tidak peduli Dimana mereka akan disalin.

1)      Referensi relatif

Secara default, semua referensi sel adalah referensi relatif. Ketika disalin di beberapa sel, mereka berubah berdasarkan posisi relatif baris atau kolom. Referensi relatif sangat baik digunakan jika ingin mengulang perhitungan yang sama di beberapa sel baris atau kolom.

2)   Referensi mutlak

Kadangkala kita tidak menginginkan berubahnya referensi sel ketika disalin ke sel lain. Gunakan referensi mutlak untuk menjaga baris dan/ atau kolom tetap konstan. Referensi mutlak digunakan dengan penambahan tanda dolar ($). Tanda ini dapat diletakkan sebelum referensi kolom, sebelum referensi baris, atau keduanya

g.     Menemukan Kesalahan dalam Perhitungan

Perhitungan matematika baik penjumlahan, pengurangan, perkalian, maupun pembagian angka merupakan hal yang paling sering dilakukan di Microsoft Excel. Tidak jarang formula yang kita masukkan tidak menghasilkan nilai yang sesuai, alias muncul pesan error. Padahal rumus yang kita gunakan sudah sesuai dengan aturan yang ada. Cara untuk mencegah kesalahan yaitu dengan menandai sel-sel yang digunakan dalam perhitungan.

Proses pengujian perhitungan akan menunjukkan adanya kesalahan yang terjadi. Proses pengujian kesalahan sebuah worksheet dalam formula disebut dengan auditing.

 Berikut ini adalah tipe-tipe kesalahan penulisan formula.

 

No.	Kesalahan	Keterangan	Solusi Pemecahannya
1	######	Rumus benar, tetapi ukuran kolom/baris kurang lebar	Atur lebar kolom atau tinggi barisnya
2	#NAME?	Salah penulisan rumus atau rumus tidak dikenal	Betulkan nama rumusnya
3	#N/A	Rumus benar, namun tidak ada nilai yang tersedia	Ganti dengan nilai yang benar
4	#DIV/01	Rumus sedang mencoba membagi dengan nol	Jika nilai pembagi benar benar 0, maka perlu diubah nilainya
5	#VALUE	Rumus berisi tipe data (argument) yang salah	Betulkan tipe datanya
6	#NULL!	Dua nilai yang ditetapkan tidak memiliki kesesuaian	Betulkan salah satu nilai agar terjadi kesesuaian antara keduanya
7	#NUM!	Angka/nomor tidak sah untuk rumus	Betulkan nomor tersebut

 

3.      Pemakaian Fungsi pada Microsoft Excel

Workbook pada Microsoft Excel adalah buku kerja tempat melakukan manajemen data yang dapat menyimpan lebih dari satu worksheet. Fungsi workbook pada Excel berkaitan dengan seluruh perintah yang ada pada Microsoft Excel Worksheet adalah lembar kerja elektronik tempat data-data tersimpan, worksheet terdiri dari cellisel yang didentifikasi dalam baris dan kolom pada Microsoft Excel. Setiap cellsel dapat memuat data baik berupa string (tulisan), angka atau formula excel.

Dalam Excel; perintah-perintah yang digunakan untuk mengolah data disimpan dalam sebuah fungsi yang dikelompokkan dalam berbagai macam kategori. Adapun jenis kategori fungsi tersebut yaitu fungsi antmatika, fungsi statistik, fungsi text, fungsi date and time, dan fungsi logika..

a.    Fungsi Aritmatik

Fungsi aritmatika merupakan kumpulan fungsi yang berisi perintah-perintah untuk mengolah data yang berupa numerik (angka). Dengan demikian jika Anda menggunakan fungsi ini untuk mengolah data selain angka, maka hasilnya tidak akan seperti yang diharapkan. Beberapa jenis fungsi antmatika sebagai berikut.

 1)  Fungsi SUM, digunakan untuk menjumlahkan angka-angka dari beberapa sel.

Formulanya    =SUM(sel_awal:sel_akhir)

2)   Fungsi Sudut, digunakan untuk mencari nilai dari radian atau sudut.

Formulanya    =Fungsi_sudut(Besar_sudut)

3)    Fungsi MOD, digunakan untuk mencari sisa pembagian.

Formulanya    =MOD(nilai, pembagi).

4)   Fungsi SQRT, digunakan untuk mencari nilai akar.

Formulanya    =SQRT(nilai).

5)    Fungsi INT, digunakan untuk mengabaikan nilai desimal suatu bilangan.

Formulanya   =INT(nilai).

 b.    Fungsi Bantu Statistik

Fungsi statistik adalah fungsi yang digunakan untuk mengolah data yang berupa numerik (angka), hanya saja operasi ini dilakukan dengan statistik. Adapun jenis dari fungsi ini sebagai berikut.

1)   Fungsi MAX, digunakan untuk mencari nilai tertinggiterbesar dari suatu range.

Formulanya =MAX(Range).

2)   Fungsi MIN, digunakan untuk mencari nilai terendah terkecil dari suatu range.

Formulanya =MIN(Range).

3)   Fungsi AVERAGE, digunakan untuk menghitung rata-rata suatu range.

Formulanya =AVERAGE(Range)

4)  Fungsi COUNT, digunakan untuk menghitung atau mencari banyaknya data suatu range. Formulanya =COUNT(Range).

 

c.     Fungsi Kelompok Text

Fungsi text adalah kumpulan fungsi yang berisi perintah-perintah yang digunakan untuk mengolah data yang berupa text atau string Jenis fungsi teks sebagai berikut

1)  LEFT, merupakan fungsi yang digunakan untuk mengambil teks dari arah kiri. Fungsi ini memiliki sintak dan argumen =LEFT(teks, jumlah karakter). Teks dapat diganti dengan alamat sel.

2) MID, merupakan fungsi yang digunakan untuk mengambil teks dari tengah. Fungsi ini memiliki sintaks dan argumen =MID(teks, mulal, jumlah karakter)

3)  RIGHT, merupakan fungsi yang digunakan untuk mengambil teks dari arah kanan Fungsi ini memiliki sintaks dan argumen =RIGHT(teks, jumlah karakter)

4)  UPPER, merupakan fungsi untuk membuat teks menjadi huruf besar semua, mempunyai sintaks =UPPER(teks).

5)  LOWER, merupakan fungsi untuk membuat teks menjadi huruf kecil semua, mempunyai sintaks =LOWER(teks).

6)  VALUE merupakan fungsi untuk membuat teks menjadi angka, mempunyal sintaks   dan argument =VALUE(teks)

7)  LEN, merupakan fungsi untuk menghitung jumlah karakter pada sebuah leks, mempunyai sintaks = LEN(teks)

 d.    Fungsi Bantu Logika

Fungsi logika merupakan sekumpulan fungsi yang berisi perintah-perintah untuk mengoperasikan data secara logika. Data yang akan diolah dengan fungsi logika ini tidak terbatas pada numerik, fext, ataupun date/time Jenis fungsi logika yang akan dibahas dalam uraian berikut adalah fungsi IF, AND, dan OR.

1)  Fungsi IF Terdapat dua macam fungsi IF, yaitu fungsi IF tunggal dan fungsi IF ganda. Struktur fungsi IF sebagai berikut.

=IF(nilai yang dicek, nilai jika kondisi benar, nilai jika kondisi salah)

Perhatikan contoh berikut.

Hasil dari fungsi IF sbb:

 

Adakalanya permasalahan tidak cukup diselesaikan dengan satu fungsi saja, tetapi harus diselesaikan dengan beberapa fungsi IF. Bentuk umum fungsi IF ganda: =IF(test1;(test2;perintah3;perintah4),perintah2

contoh:

2)      Fungsi AND

Fungsi ini digunakan untuk menguji kebenaran persyaratan semua isi sel lembar kerja ketik bernilai true jika semua syarat terpenuhi dan bernilai false jika salah satu syarat tidak terpenuhi. Bentuk umumnya sebagai berikut.

=AND (syarat1;syarat2;syarat3;...;syarat-N)

3)      Fungsi OR

Fungsi ini digunakan untuk menguji kebenaran persyaratan Salah satu isi sel lembar kerja. Bernilai true jika salah satu syarat terpenuhi dan bernilai false jika semua syarat tidak terpenuhi. Fungsi or adalah kebalikan dari fungsi n bentuk umumnya sebagai berikut.

=OR (syarat 1; syarat2; syarat;.... Syarat -N)

e.    Langkah-langkah Penggunaan VLOOKUP dan HLOOKUP

Pada Microsoft Excel, kita dapat menyajikan tabel secara vertikal maupun horizontal. Fungsi VLOOKUP dan HLOOKUP merupakan fungsi yang digunakan untuk menemukan informasi.

 1)      Fungs VLOOKUP

Fungsi VLOOKUP adalah salah satu fungsi pencarian dalam Microsoft Excel. VLOOKUP adalah singkatan dari Vertical Lookup, sehingga dapat diartikan sebagal suatu fungsi yang ditujukan untuk melakukan pencarian demi kolom. Adapun rumus bakunya sebagai berikut. =VLOOKUP(nilai kunci; range data; offset kolom) data secara vertikal kolom

Keterangan:

a)     Nilai kunci atau look up value adalah nilai yang akan dicari untuk dicocokkan dengan data tabel sumber.

b)     Range data atau tabel array adalah kumpulan data sel yang akan dicocokkan dengan data tabel sumber sesuai dengan nilai kuncinya

c)     Offset kolom atau col index num adalah nomor kolom dalam tabel sumber yang akan dibaca.

Untuk mempermudah, terlebih dahulu kita dapat memberi nama range data table sumber. Langkahnya sebagai berikut.

a)       Blok tabel sumber, kecuali kepala kolomnya

b)      Klik kanan, pilih, dan klik Define a Name

c)       Muncul kotak dialog New Name.

d)      Ketik nama range-nya pada bagian Name

e)      Klik OK

Untuk lebih jelasnya mari kita buat tabel seperti berikut.

Langkah pertama adalah membuat nama range data sumber sebagai berikut.

a)       Blok sel A4 sampai C6

b)      Klik Kanan, pilih dan klik Define a Name

c)       Dari kotak dialog New Name, pada bagian Name ketik Gaji.

d)       Klik OK

Selanjutnya, pada kolom Gaji Pokok masing-masing pegawai, diisi dengan perintah berikut.

=VLOOKUP(B13;Gaji,2)

Sedangkan untuk mengisi kolom gaji tunjangan formulanya sbb:

=VLOOKUP(B13;Gaji;3)

 

2)      Fungsi HLOOKUP

Fungsi HLOOKUP adalah salah satu fungsi pencarian dalam Microsoft Excel. HLOOKUP adalah singkatan dan Horizontal Lookup, sehingga dapat diartikan sebagai suatu fungsi yang ditujukan untuk melakukan pencarian data secara horizontal baris demi baris. Adapun rumus bakunya sebagai berikut

=HLOOKUP (nilai kunci; range data; offset baris)

Keterangan

a)   Nilai kunci atau look up value adalah nilai yang akan dicari untuk dicocokkan yang akan dicocokkan dengan data tabel sumber

b)   Range data atau tabel array adalah kumpulan data sel dengan data tabel sumber sesuai dengan nilai kuncinya.

c)   Offset baris atau row index num adalah nomor baris dalam tabel sumber yang akan dibaca.

Buat nama range data sumber. Perhatikan tabel sumber dengan yang di atas, formatnya berbeda. Untuk itu, pembuatannya sebagai berikut.

a)       Blok sel B3 sampai D5.

b)      Klik kanan, pilih dan klik Name a Range.

c)       Dan kotak dialog New Name, pada bagian Name ketik Gaji2.

d)      Klik OK

 

Formula gaji pokok sebagai berikut.

=HLOOKUP(B13,Gaji2,2)

Formula gaji tunjangan sebagai berikut.

=HLOOKUP(B13,Gaji2,3)