Mengenal Operasi Hitung
Beberapa arithmetic operator dalam Ms. Excel 2007 yang
sering digunakan adalah:
1.
+ (plus) digunakan untuk menghitung
penjumlahan
2.
– (minus) digunakan untuk menghitung
pengurangan
3.
* (kali) digunakan untuk menghitung
perkalian
4.
/ (bagi) digunakan untuk menghitung
pembagian
5.
% (persen) digunakan untuk mengitung persentase
Fungsi SUM
Untuk menghemat waktu pengerjaan
dengan menjumlahkan satu per satu cell, excel menyediakan fasilitas menjumlah
yang lebih cepat dengan rumus SUM.
Langkah-langkahnya adalah;
1.
Aktif di cell tempat hasil penjumlahan
akan ditampilkan
2.
Ketikkan rumus =SUM(sorot area angka
yang hendak di jumlahkan) contoh =SUM(B3:B10)
Artinya ingin menjumlahkan nilai
dari cell B3 hingga cell B10
1.
Fungsi sum juga dapat diakses dengan
aktif ke tab formulas > insert functions > pilih SUM (atau dengan
mengetikkan SUM pada isian search for a function
2.
Selain dengan akses ke tab formula
> insert function > SUM maupun langsung mengetikkan rumusnyab pada
formula bar, penjumlahan juga dapat dilakukan dengan aktif ke tab formula
> auto SUM atau dengan memilih symbol ∑ pada tab home (sama dengan
auto sum)
Fungsi LOGIKA IF
Fungsi logika IF digunakan untuk
menampilkan beberapa kemungkinan nilai/ hasil sesuai dengan kondisi tertentu.
Bentuk Umum dari fungsi IF adalah
=IF(logical_test,[value if
true],[value if false])
Langkah-langkah pengerjaan
perhitungan dengan menggunakan rumus IF adalah:
1.
Tentukan kategori/ batasan untuk suatu
kondisi dengan beberapa alternative kondisi. Contoh jika peserta mempunyai
nilai > nilai x mendapat hadiah XXX, jika mempunyai nilai > nilai y
mendapat hadiah YYY, jika tidak mencapai maka tidak mendapat hadiah
2.
Ketikkan formula IF pada formula bar.
contoh
=IF(B3>=90,”hadiah
kamera”,IF(B3>=80,”hadiah foto studio + make up”,IF(B3>=70,”hadiah
kenang-kenangan,” “))) kemudian enter
Artinya jika seorang peserta yang
memiliki nilai di cell B3 diatas atau sama dengan 90 mendapat hadiah kamera,
jika nilainya 80-89 mendapat hadiah foto studio + make up, jika nilainya 70-79
mendapat hadiah kenang-kenangan, jika nilainya lebih kecil atau sama dengan 69
tidak mendapat apapun.
1.
Rumus IF juga dapat ditampilkan dengan
aktif di tab formulas > insert function > ketikkan IF pada seach for a
formula > OK.
2.
Isikan Logical tes dengan B3>=80,
value_if_true dengan “hadiah kamera”, value _if_false dengan
IF(B3>=80,”hadiah foto studio + make up”,IF(B3>=70,”hadiah
kenang-kenangan,” “)) kemudian klik OK
Rumus IF dapat juga digabung
dengan beberapa rumus logika lainnya seperti AND, OR, NOT, dan sebagainya.
Berikut contoh penggunaan IF dengan penggabungan logika AND
Fungsi VLOOKUP
Fungsi Vlookup berguna untuk
membaca data secara vertical pada suatu cell atau range.
Bentuk umum fungsi VLOOKUP adalah:
=VLOOKUP(lookup_value,table_array,col_index_num)
Langkah – langkah untuk
menggunakan fungsi VLOOKUP adalah:
1.
Sebelum menggunakan rumus VLOOKUP,
data sumber/ table arraynya di urutkan terlebih dahulu secara ascending/ sort
a> z
2.
Aktiflah pada cell dimana hasil
VLOOKUP akan ditampilkan, kemudian ketikkan rumus VLOOKUP di formula bar,
misalnya: =VLOOKUP(A3,$A$12:$C$16,2). Artinya kita akan mengisi misalnya cell
F3 (cell yang dipilih untuk tampil hasil fungsi) dengan nilai dari table
referensi A12 hingga C16 dengan hasil adalah kolom ke dua dari table referensi
=nilai dari cell B12:B16. Table array diberikan symbol $ agar referensinya
tidak bergeser saat formula yang sama di copy ke baris berikutnya. Symbol $
dapat diketik manual atau dengan menggunakan short cut F4.
3.
Selain dengan menggetikkan rumus
tersebut pada formula bar, fungsi VLOOKUP dapat dieksekusi dengan aktif di tab
formulas > insert functions > ketikkan VLOOKUP pada search for a formula
> OK
4.
Setelah aktif window formula VLOOKUP,
isilah lookup value dengan A3, table array dengan mensorot cell A12 hingga C16,
col_index_num isilah dengan 2 > OK
Berikut contoh lain penggunaan
VLOOKUP dengan penggunaan table referensi pada sheet yang berbeda dengan sheet
aktif.
Artinya table referensi ada di
sheet table registrasi peserta dengan data terdapat pada cell B3 hingga C17,
dengan nilai yang akan ditampilkan pada kolom C di sheet hasil festival adalah
dari kolom ke 2 dari table referensi tersebut (C3:C17)
Rumus VLOOKUP juga dapat
digabungkan dengan beberapa rumus lain. Contohnya VLOOKUP digabung dengan
SUM,digunakan untuk menjumlahkan nilai dari hasil lookup dengan nilai
konstanta/nilai tertentu untuk keseluruhan nilai kolom dalam table tersebut.
Contoh rumusnya
=SUM(D4*VLOOKUP(B4,$B$12:$D$17,3))
Fungsi Statistik
1. Max
(Range)
: mencari nilai terbesar dari suatu range.
2. Min
(Range)
: mencari nilai
terkecil dari suatu range.
3. Sum (Range)
: mencari jumlah dari isi data yang terdapat pada suatu range.
4. Average (Range)
: mencari nilai rata-rata dari suatu range.
5. Count
(Range) : mencari jumlah data yang
terdapat pada suatu range.
Langkah untuk menggunakan fungsi
statistic adalah:
1.
Pastikan semua data yang ada pada
table yang akan dihitung dengan fungsi statistic telah benar dan tidak ada data
dengan symbol mata uang/ pemisah antara ribu,juta ataupun satuan tertentu yang
ditulis secara manual. Aktiflah pada cell yang dikehendaki untuk menampilkan
hasil dari fungsi statistic tersebut.
2.
Jika ingin menjumlahkan data yang ada
maka ketikkan =COUNT(sel awal:sel akhir)
3.
Jika ingin mengetahui nilai maksimum
dari suatu table =MAX(sel awal:sel akhir)
4.
Jika ingin mengetahui nilai minimum
dari suatu table =MIN(sel awal:sel akhir)
5.
Langkah diatas juga dapat dilakukan
dengan aktif ke tab formulas > insert functions > pilih jenis operasi
statistic yang dikehendaki kemudian OK. Untuk isian kolom numer_1 sorotlah
range data yang akan dihitung nilai statistiknya > OK
Fungsi LOGIKA COUNTIF
Fungsi logika COUNTIF digunakan
untuk melakukan perhitungan COUNT dengan criteria tertentu. Contoh penggunaan
COUNTIF adalah =COUNTIF(F31:F42,”>=95”)
Artinya ingin mengetahui jumlah
data yang nilainya lebih besar atau sama dengan 95 dari sel F31 hingga sel F42.
Fungsi Finansial
Fungsi financial adalah kumpulan
fungsi yang berguna untuk menghitung data-data keuangan. Aplikasi fungsi
financial yang sering dipakai antara lain: menghitung nilai investasi pada
bank, menghitung penurunan harga, dan perhitungan pembayaran cicilan.
Fungsi FV (Future Value)
Beberapa elemen dalam fungsi FV
adalah:
1.
Rate (tingkat suku bunga)
2.
Nper (jumlah periode angsuran)
3.
Pmt (besar angsuran yang dibayarakan)
4.
PV (nilai saat ini yang akan dihitung
nilainya di masa depan)
5.
Type (jatuh tempo pembayaran angsuran)
jika nilainya 1 maka pembayaran dilakukan di awal periode, jika nilainya 0 maka
pembayaran dilakukan di akhir periode.
Bentuk umum fungsi FV adalah:
=FV(rate;nper;pmt;pv;type)
Contoh: misalkan untuk membeli
sebuah rumah tipe 21 diperlukan biaya Rp.50.000.000,00. Berapa uang uang yang
diperlukan untuk membeli rumah tersebut 20 tahun mendatang, jika diasumsikan
inflasinya=8%.
Langkah pengerjaannya:
1.
Tentukan cell tempat menampilkan hasil
perhitungan FV
2.
Aktif di tab formulas > insert
functions > ketikkan FV pada serch for a formula > OK
3.
Isilah Rate dengan 0.08 (karena
inflasi 8%)
4.
Isilah Nper dengan 20 (karena 20
tahun)
5.
Isilah Pmt dengan 0 (karena tidak
dilakukan cicilan)
6.
Isilah PV dengan -50.000.000 (nilai 50
juta minus karena diasumsikan 50 juta dikeluarkan saat ini)
7.
Dari hasil perhitungan tersebut akan
dihasilkan nominal Rp. 233.047.857,19
Fungsi PMT
Fungsi Pmt berguna untuk
menghitung besar angsuran pinjaman/ cicilan. Elemen yang ada dalam rumus Pmt
sama dengan elemen dalam rumus FV yakni rate, nper, pmt, PV,FV dan type.
Bentuk umum rumus PMTadalah:
=PMT(rate;nper;pmt;pv;fv;type)
Contoh: jika seseorang ingin
membeli rumah seharga Rp. 200.000.000,00, maka berapa cicilan perbulannya, jika
suku bunga = 9.75%, dan cicilan akan dilakukan selama 5 tahun.
Langkah pengerjaan:
1.
Tentukan cell tempat menampilkan hasil
perhitungan PMT
2.
Aktif di tab formulas > insert
functions > ketikkan PMT pada serch for a formula > OK
3.
Isilah Rate dengan 0.975/12 (karena
suku bunga 9.75% untuk 1 tahun, sehingga untuk tiap bulannya dibagi dengan 12)
4.
Isilah Nper dengan 5*12 (karena
periode cicilan selama 5 tahun dengan total sama dengan 60 bulan)
5.
Isilah FV dengan 0 (karena tidak
diketahui nilai masa depannya, melainkan harga rumah saat ini)
6.
Isilah PV dengan -200.000.000 (nilai
200 juta minus karena diasumsikan 200 juta dikeluarkan saat ini)
7.
Isilah Type dengan 0 (pembayaran pada
akhir bulan)
8.
Dari hasil perhitungan tersebut akan
dihasilkan nominal Rp. 4.224.848,73. Besar cicilan yang harus dibayarakan
perbulannya adalah 4.224.848,73.
Fungsi SLN
Fungsi SLN berguna untuk
menghitung depresiasi dari suatu barang. Elemen-elemen yang terdapat dalam
formula SLN adalah:
1.
Cost (harga perolehan barang tersebut/
harga beli/ harga beli+biaya perolehan)
2.
Salvage (nilai/ harga jual yang masih
bisa dicapai oleh barang tersebut setelah dipakai Selama periode tertentu)
3.
Life (lama pemakaian barang tersebut)
Bentuk umum fungsi SLN adalah:
=SLN(cost;salvage;life) adam
Contoh: jika seserang membeli
mobil dengan harga Rp. 119.000.000,00, setelah 4 tahun dijual dengan harga jual
Rp. 99.000.000,00, maka berapa depresiasi mobil tersebut?
Langlah pengerjaannya:
1.
Tentukan cell tempat menampilkan hasil
perhitungan SLN
2.
Aktif di tab formulas > insert
functions > ketikkan SLN pada serch for a formula > OK
3.
Isilah cost dengan 119000000 (karena
harga beli mobil 119 juta)
4.
Isilah salvage dengan 99000000 (karena
harga jual mobil 99 juta)
5.
Isilah life dengan 4 (karena mobil
hanya digunakan selama 4 tahun)
6.
Diperoleh hasil Rp. 3.333.333,33
(nilai depresiasi dari mobil)
THREE-DIMENSIONAL REFERENCE
Reference bisa dipakai untuk melakukan
kalkulasi terhadap sel yang tersebar dalam banyak worksheet. Semisal data
penjualan dicatat menggunakan Excel. Untuk tiap bulan digunakan satu worksheet.
Misalkan di dalam tiap worksheet total penjualan dicatat dalam sel A100.
Jika ingin diketahui total
penjualan dalam satu tahun digunakan formula berikut :
=SUM(Sheet1:Sheet12!A100)
NAME
Sekelompok sel bisa diberi nama.
Peraturan untuk pemberian nama adalah sebagai berikut:
1.
Semua nama dimulai dengan huruf,
backslash ( \ ), atau underscore ( _ )
2.
Angka boleh dipakai
3.
Spasi tidak diperbolehkan
4.
Bentuk referensi terhadap sel tidak
boleh dipakai (contoh B5, $A1)
5.
Nama bisa berupa 1 huruf (kecuali R
dan C)
6.
Untuk mendefinisikan Name dapat
dilakuan dengan shortcut Ctrl +F3
Jika ingin menambahkan nilai data
yang sebelumnya telah diberikan nama, maka penjumlahan dapat dilakukan dengan:
=SUM(nama range tersebut)
Format Cell
Format cell digunakan untuk
mengatur jenis data yang ada dalam suatu cell. Beberapa jenis pengaturan yang
tersedia dalam format cell adalah: general, number, currency, accounting, date,
time, percentage, fraction, scientific, text, special,custom. Selain mengatur
jenis dari data yang ada pada suatu cell, format cell juga memiliki fitur untuk
mengatur alignment, font, border, fill, dan protection.
Langkah yang dapat dilakukan untuk
memformat jenis data dalam suatu cell:
1.
Klik kanan> format cell
2.
Pilih jenis data yang sesuai pada menu
number. Jika data berupa tanggal, maka pilihlah date, kemudian pada kolom type
yang ada disebelah kanan list pilihan, pilihlah jenis format tanggal yang
dikehendaki. Jika data berupa number dan ingin diberikan nilai desimalnya, maka
pilihlah number dan pada decimal place, tentukan jumlah angka di belakang koma
yang dikehendaki. Untuk data yang berupa nilai mata uang, maka pilihlah
currency dengan symbol sesuaikan dengan symbol mata uang yang dikehendaki.
3.
Jika pilihan tidak ada maka dapat
dibuat dengan menggunakan custom. Contoh jika ingin menampilkan satuan “orang”
pada kehadiran rapat kepengurusan maka pada custom ketikkan 0” orang” demikian
pula untuk jenis satuan yang lain yang belum tersedia
Beberapa short cut tambahan:
Untuk memasukkan data tanggal
sekarang Ctrl+;
Untuk memasukkan data waktu
sekarang Ctrl+Shift+:
Untuk berpindah dari format
tanggal ke angka serial, gunakan Ctrl+Shift+~
Untuk mengembalikan ke format
tanggal klik format cell > date
Amazing
BalasHapus