Trik Dahsyat Formula Excel Formula merupakan fitur yang digunakan untuk melakukan perhitungan dalam sebuah sel. Penggunaan formula harus diawali tanda sama dengan (=), disertai kombinasi elemen: Nilai yang dimasukkan langsung ke dalam formula. Referensi alamat sel/range atau nama sel/range. Operator perhitungan. Fungsi. Susunan formula dapat terdiri maksimal 8.192 karakter, termasuk tanda sama dengan (=), nama fungsi, tanda kurung, argumen ataupun 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
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.
1
#NAME?
Dalam formula yang Anda masukkan, ada argumen atau 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 (1x10307) atau terlalu kecil (-1x10307).
#NULL!
Formula yang Anda masukkan telah menentukan perpotongan antara dua range yang tidak berpotongan.
#VALUE
Penulisan argumen tidak sebagaimana yang disyaratkan 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 (8.192).
#REFF
Salah satu referensi atau argumen yang digunakan dalam formula telah terhapus.
1.1 Operator Formula . Kesalahan penggunaan operator dapat berakibat pada hasil perhitungan yang tidak sesuai.
1.1.1 Operator Aritmetika
Operator Aritmetika
Pengurangan
2
Simbol
Contoh
Hasil
+
10+5
15
-
10-5
5
*
10*5
50
Pembagian
/
10/5
2
Perpangkatan
^
10^5
100000
Persentase
%
10%
0,10
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.
.2 Operator Perbandingan
TRUE) atau salah (FALSE). Simbol
Contoh
Hasil
=
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
erbandingan
3
3 Operator Teks
Teks
Simbol
Contoh
Hasil
&
"Aku"&" "&"takut"
Aku takut
4 Operator Referensi
Referensi Operator range Operator pemisah argumen
Simbol : , atau ;
Contoh B1:B15 COUNTIF(B1:B2;”Pria”)
1.2 Mengubah Penggunaan Simbol Desimal dan Ribuan Indonesia menggunakan simbol koma untuk memisahkan nilai desimal, sedangkan simbol titik digunakan untuk memisahkan nilai ribuan. Pengaturan penggunaan simbol koma (,) dan titik (.) juga berpengaruh pada penggunaan titik dua (:) dan titik koma (;) dalam penulisan formula dan fungsi Excel. File dialog Excel Options.
Options Muncul kotak
2. Pilih opsi Advanced system separators
Use
4
Gambar 1.1 Mengubah penggunaan simbol desimal dan ribuan.
Jika Anda ingin mengubah penggunaan simbol koma dan titik yang berbeda dengan sistem dalam setting komputer, hilangkan tanda contreng pada pilihan Use system separators. Pada kotak isian Decimal separator, isikan simbol pemisah desimal (titik atau koma). Ketikkan simbol pemisah ribuan pada kotak isian Thousands separator. Klik tombol OK
3 Penggunaan Formula
Ctrl+N eperti terlihat pada Gambar 1.2. 5
2 Contoh data perhitungan.
B3
=
B1 *
B2
Gambar 1.3 Bentuk penulisan formula.
Enter Hasil perhitungan akan ditampilkan pada sel B3, sedangkan bentuk formula ditampilkan dalam Formula Bar.
6
4 Hasil perhitungan menggunakan formula.
=2500*10
B4
Enter B2
20
B3
B4 B4
=B1+B2
F2
Enter
Gambar 1.5 Hasil perhitungan pada sel B3 akan menyesuaikan.
7
4 Formula dengan Referensi Sel Relatif tidak terkunci, ditandai tidak adanya simbol $ di depan kolom dan baris. 1. Buka file Formula Dengan Referensi Sel Relatif.xlsx CD buku ini.
Gambar 1.6 Data penjualan komponen komputer.
=B3*C3
D3
Enter
12.000.000 D3 Ctrl+C Copy D4:D7 Paste
Ctrl+V
Gambar 1.7 Hasil salinan formula dengan referensi sel relatif.
drop down Formulas (F)
Gambar 1.8 Memilih opsi penyalinan formula.
=B3*C3 =B7*C7
copy paste
Untuk memasukkan formula ke dalam beberapa sel (range) sekaligus, dimensi kolom atau baris range yang digunakan dalam formula harus sama dengan dimensi range formula. 1. Blok range . Pastikan sel D3 dalam keadaan aktif. Jika belum aktif, tekan tombol Tab D3 =B3*C3
D3
Ctrl+Enter
5 Formula dengan Referensi Sel Semi Absolut
$A4
*
F4 B$3
Gambar 1.9 Bentuk formula dengan referensi sel semi absolut.
10
B3
Enter 100 B4 Ctrl+C
Copy C4:E4 Paste
Ctrl+V B4:E4 Ctrl+C B5:E13 Paste
Copy Ctrl+V
Gambar 1.10 Tabel harga fotokopi yang sudah dilengkapi.
=$A13*E$3
$A $3
6 Formula dengan Referensi Sel Absolut
Formula Dengan Referensi Sel Absolut.xlsx D4 E4
F4 $E$4
= *
C4
Gambar 1.11 Bentuk formula dengan referensi sel absolut.
Enter 25.000 D4 Ctrl+C Copy D5:D11 Paste
Ctrl+V =$E$4*C11
$E$4
Gambar 1.12 Formula dengan referensi sel absolut.
1.7 Formula dengan Nama Sel/Range
underscore (_) dan titik (.). Nama sel/range harus dimulai dari huruf atau tanda (_), bukan angka atau karakter lainnya. Nama sel/range yang terdiri atas dua kata atau lebih tidak boleh dipisahkan dengan spasi. Anda dapat menggunakan tanda (_) atau titik (.) untuk menggantikan spasi. Misalnya, nama sel Harga Barang Harga_Barang Harga.Barang
Formula Dengan Nama Range.xlsx
¾
E4 Harga Enter
melalui Name Box.
¾
Define Name Harga Workbook =Data!$E$4
Gambar 1.14 Kotak dialog New Name.
collapse dialog
Gambar 1.15 Collapse dialog New Name – Refers to:.
E4 OK
¾
C4:C11 Jumlah
Enter
Gambar 1.16 Membuat nama range Jumlah melalui Name Box.
¾
Define Name Jumlah Workbook
=Data!$C$4:$C$11 OK
¾
C3:C11 Create from Selection
Ctrl+Shift+F3 Top row OK
Gambar 1.17 Kotak dialog Create Names from Selection.
=
F3
D4
Use in Formula Paste Names...
Harga
Gambar 1.18 Kotak dialog Paste Name.
OK
*
B4 Enter
Gambar 1.19 Formula dengan nama sel.
D4 Ctrl+C Copy D5:D11 Paste
Ctrl+V
D4:D11 Use in Formula Harga * Use in Formula Jumlah
Gambar 1.20 Nama sel Harga dan nama range Jumlah.
Ctrl+Enter
1.8 Mengelola Nama Range Name Manager
Gambar 1.21 Kotak dialog Name Manager.
New...
Edit....
Delete....
OK Cancel
Gambar 1.22 Kotak pesan konfirmasi.
error #NAME?.
dengan nama sel/range yang dihapus.
9 Menyeleksi Sel Berisi Formula
Find & Go
Select To.... Ctrl+G
Gambar 1.24 Kotak dialog Go To.
Special….
Gambar 1.25 Kotak dialog Go To Special.
Formulas Numbers Text Logicals
Errors
OK
Gambar 1.26 Seluruh sel berisi formula terseleksi.
0 Menampilkan Bentuk Formula
Menampilkan Bentuk Formula.xlsx Show Formulas
Show Formulas
Ctrl+`
Gambar 1.27 Menampilkan bentuk formula.
Proteksi Formula.xlsx
Ctrl+A+A
Gambar 1.28 Klik perpotongan baris dan kolom.
Format Format Cells… Ctrl+1
Gambar 1.29 Kotak dialog Format Cells.
Locked
Protection Hidden Ctrl+G
OK
Special… Formulas Numbers Text Logicals Errors Ctrl+1 Protection
OK
Locked OK Protect Sheet
Gambar 1.30 Kotak dialog Protect Sheet.
Elex1234
OK Elex1234 OK
Hidden
Gambar 1.31 Kotak dialog Confirm Password.
Gambar 1.32 Kotak pesan peringatan formula terproteksi.
Ctrl+`
Show
Formulas
Unprotect Sheet
Elex1234
Gambar 1.33 Kotak dialog Unprotect Sheet.
OK
Error Checking
Kotak dialog Excel Options – opsi Formulas.
2.
Enable background error checking Indicate errors using this color:.
Gambar 1.35 Memilih warna background indikator error.
¾ Cells containing formulas that result in an error
¾ Inconsistent calculated column formula in tables
¾ Cells containing years represented as 2 digits
¾ Numbers formatted as text or preceded by an apostrophe
¾ Formulas inconsistent with other formulas in the region
¾ Formulas which omit cells in a region
¾ Unlocked cells containing formulas
¾ Formulas referring to empty cells
¾ Data entered in a table is invalid
OK
Gambar 1.36 Tombol pilihan pada sel berisi nilai error.
¾ ¾ Help on this error
Gambar 1.37 Fasilitas bantuan untuk menangani error.
¾ Show Calculation Steps… Evaluate Step In Step Out
Gambar 1.38 Kotak dialog Evaluate Formula.
¾ Ignore Error ¾ Edit in Formula Bar ¾ Error Checking Options…
Task Pane Watch Window Task pane Watch Window digunakan untuk menampilkan detail informasi sel meliputi nama workbook, nama worksheet, nama sel/range, alamat referensi sel, nilai dan bentuk formula. 1. Buka file dalam bonus CD buku. 2.
yang disertakan
Klik tombol dalam tab Formulas group Formula Auditing. Muncul task pane Watch Window.
Task pane Watch Window.
Add Watch…
Gambar 1.40 Collapse dialog Add Watch.
B4:E13 Add
Task Pane Options Size
30
Gambar 1.41 Detail informasi sel yang ditampilkan.
Ctrl Delete Watch
Menghapus Perhitungan.xlsx
Formula D4
F9
Setelah
Melakukan
=E4*C4
Gambar 1.42 Menghapus formula setelah melakukan perhitungan.
Enter D4
Perhitungan
Cepat
Tanpa
Menggunakan
Formula.xlsx
Gambar 1.43 Memilih perhitungan yang diinginkan.
D4:D11
Memasukkan Komentar dalam Formula
<spasi>N(”Komentar”) Memasukkan Komentar Dalam Formula.xlsx
Tono")
D4
=E4*C4+ N("Total penjualan pembeli Enter
Gambar 1.45 Memasukkan komentar dalam formula.
***
