Wah, aq nemu artikel yg sangat berguna ni utk kalian yg pengen belajar
rumus-rumus dari Ms.excel 2007, ini artikel aq copas dari blognya saddamzikri, ok langsung aja , Cekidot---->
Beberapa arithmetic operator dalam
Ms. Excel 2007 yang sering digunakan adalah:
- + (plus) digunakan untuk menghitung penjumlahan
- – (minus) digunakan untuk menghitung pengurangan
- * (kali) digunakan untuk menghitung perkalian
- / (bagi) digunakan untuk menghitung pembagian
- % (persen) digunakan untuk mengitung persentase
- ^ (pangkat) digunakan untuk menghitung pangkat
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;
- Aktif di cell tempat hasil penjumlahan akan ditampilkan
- Ketikkan rumus =SUM(sorot area angka yang hendak di jumlahkan) contoh =SUM(B3:B10)
Artinya ingin menjumlahkan nilai dari cell B3 hingga cell B10
- Fungsi sum juga dapat diakses dengan aktif ke tab formulas >
insert functions > pilih SUM (atau dengan mengetikkan SUM pada isian
search for a function
- 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:
- 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
- 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.
- Rumus IF juga dapat ditampilkan dengan aktif di tab formulas
> insert function > ketikkan IF pada seach for a formula > OK.
- 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:
- Sebelum menggunakan rumus VLOOKUP, data sumber/ table arraynya di urutkan terlebih dahulu secara ascending/ sort a> z
- 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.
- 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
- 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:
- 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.
- Jika ingin menjumlahkan data yang ada maka ketikkan =COUNT(sel awal:sel akhir)
- Jika ingin mengetahui nilai maksimum dari suatu table =MAX(sel awal:sel akhir)
- Jika ingin mengetahui nilai minimum dari suatu table =MIN(sel awal:sel akhir)
- 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:
- Rate (tingkat suku bunga)
- Nper (jumlah periode angsuran)
- Pmt (besar angsuran yang dibayarakan)
- PV (nilai saat ini yang akan dihitung nilainya di masa depan)
- 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:
- Tentukan cell tempat menampilkan hasil perhitungan FV
- Aktif di tab formulas > insert functions > ketikkan FV pada serch for a formula > OK
- Isilah Rate dengan 0.08 (karena inflasi 8%)
- Isilah Nper dengan 20 (karena 20 tahun)
- Isilah Pmt dengan 0 (karena tidak dilakukan cicilan)
- Isilah PV dengan -50.000.000 (nilai 50 juta minus karena diasumsikan 50 juta dikeluarkan saat ini)
- 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:
- Tentukan cell tempat menampilkan hasil perhitungan PMT
- Aktif di tab formulas > insert functions > ketikkan PMT pada serch for a formula > OK
- Isilah Rate dengan 0.975/12 (karena suku bunga 9.75% untuk 1 tahun, sehingga untuk tiap bulannya dibagi dengan 12)
- Isilah Nper dengan 5*12 (karena periode cicilan selama 5 tahun dengan total sama dengan 60 bulan)
- Isilah FV dengan 0 (karena tidak diketahui nilai masa depannya, melainkan harga rumah saat ini)
- Isilah PV dengan -200.000.000 (nilai 200 juta minus karena diasumsikan 200 juta dikeluarkan saat ini)
- Isilah Type dengan 0 (pembayaran pada akhir bulan)
- 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:
- Cost (harga perolehan barang tersebut/ harga beli/ harga beli+biaya perolehan)
- Salvage (nilai/ harga jual yang masih bisa dicapai oleh barang tersebut setelah dipakai Selama periode tertentu)
- 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:
- Tentukan cell tempat menampilkan hasil perhitungan SLN
- Aktif di tab formulas > insert functions > ketikkan SLN pada serch for a formula > OK
- Isilah cost dengan 119000000 (karena harga beli mobil 119 juta)
- Isilah salvage dengan 99000000 (karena harga jual mobil 99 juta)
- Isilah life dengan 4 (karena mobil hanya digunakan selama 4 tahun)
- 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:
- Semua nama dimulai dengan huruf, backslash ( \ ), atau underscore ( _ )
- Angka boleh dipakai
- Spasi tidak diperbolehkan
- Bentuk referensi terhadap sel tidak boleh dipakai (contoh B5, $A1)
- Nama bisa berupa 1 huruf (kecuali R dan C)
- 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:
- Klik kanan> format cell
- 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.
- 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
