Rumus Exel

RUMUS – RUMUS LOGIKA

NO BENTUK UMUM RUMUS FUNGSI
1. =LEFT ( Cell uji, Jumlah ambil ) Untuk pengambilan digit ( karakter ) dari kiri/depan.
2. =RIGHT ( Cell uji, Jumlah ambil ) Untuk pengambilan digit ( karakter ) dari kanan/belakang
3. =MID ( Cell uji, mulai ambil, Jumlah ambil ) Untuk pengambilan digit ( karakter ), jika ada digit ( karakter ) di kiri ( depan ) dan kanan ( belakang ) yang tidak diambil.
4. =SUM ( Range ) Untuk menjumlahkan suatu range ( kumpulan sel ).
5. =MAX ( Range ) Untuk mencari nilai/angkatertinggi suatu range.
6. =MIN ( Range ) Untuk mencari nilai/angka terendah dari suatu range.
7. =AVERAGE ( Range ) Untuk mencari nilai/angka rata – rata dari suatu range.
8. =ROUND ( Range ) Untuk pembulatan angka pecahan decimal.
9. =ABS ( Range ) Untuk menghilangkan tanda minus pada suatu bilangan/angka.
10. =UPPER ( Cell ) Untuk membuat huruf kapital dari suatu digit yang terletak pada sel.
11. =LOWER ( Lower ) Untuk membuat huruf kecil dari suatu digit yang terletak pada sel.
12. =VALUE ( Cell atau Rumus ) Untuk menjadikan tipe data menjadi numerik ( angka sesungguhnya )
13. & Untuk menggabungkan lebih dari satu rumus.
Catatan : –  Cell ( sel ) adalah pertemuan antara kolom ( disimbolkan huruf ) dengan baris ( disimbolkan angka ).

-    Range adalah kumpulan dari sel –  sel.

Contoh Cell :  A1 berarti kolom A baris ke 1

A23 berarti kolom A baris ke 23

Contoh Range :  A5 .. B7 atau A5 : B7 berarti kumpulan dari sel A5, A6, A7, B5, B6, dan B7.

ARRAY FORMULA MICROSOFT EXCEL

Pada Microsoft Excel, array formula adalah formula yang dapat melaksanakan beberapa perhitungan sekaligus untuk menghasilkan satu atau beberapa hasil. Array formula dapat melakukan perhitungan pada satu atau sekelompok nilai yang disebut array argument. Masing-masing array argument harus mempunyai jumlah baris dan kolom yang sama. Penulisan array formula sama seperti penulisan formula yang biasa hanya saja setelah penulisan tidak diakhiri dengan ENTER melainkan CTRL+SHIFT+ENTER. Array formula yang dimasukkan akan tampak diapit tanda kurung kurawal {..}.

Di bawah ini akan diberikan contoh untuk memperjelas bagaimana menggunakan array formula.

Misalkan saja, kita mempunyai tabel daftar pegawai disertai keterangan departemen dan besar gaji yang diterima masing-masing seperti pada gambar di atas. Di bawahnya, misalkan perhitungan-perhitungan yang akan dilaksanakan terhadap tabel tersebut.

Perhitungan pertama, misalnya mencari besarnya gaji yang diterima pegawai bernama “Anton” yang berada pada departemen “B”. Jika saja terdapat satu kolom lagi misalnya NIP yang berbeda antara pegawai yang satu dengan yang lain dan terletak dikolom paling kiri, akan lebih mudah dengan menggunakan fungsi VLOOKUP(). Tetapi pada contoh di atas, kolom tersebut tidak ada, sedang nama pegawai yang bernama Anton ada dua namun berbeda departemen. Anggap saja pada setiap departemen nama pegawaianya berbeda, maka kita dapat menggunakan array formula untuk menghitungnya. Pilih cell yang akan menampung hasil perhitungan, dan masukkan formula berikut:

=SUM((A2:A11=”Anto”)*(B2:B11=”B”)*(C2:C11))Setelah menuliskan formula tersebut akhiri dengan CTRL+SHIFT+ENTER, sehingga saat kita seleksi lagi cell tersebut, formula tersebut akan tampak diapit tanda kurung kurwal.

{=SUM((A2:A11=”Anto”)*(B2:B11=”B”)*(C2:C11))}Perhitungan tersebut menghasilkan nilai 500.000 yang dilakukan dengan mencari kolom A2:A11 yang bernilai “Anto” dan kolom B2:B11 yang bernilai “B” dan hasilnya mengambil dari kolom C2:C11. Seandainya terdapat dua pegawai bernama Anton pada departemen B maka hasil adalah penjumlahan gaji dua “Anton” tersebut.

Jika kita mencari jumlah pegawai yang mempunya gaji di atas 800.000, kita akan dapat mencarinya dengan menggunakan fungsi COUNTIF.

=COUNTIF(C2:C11;”>800000″)Tetapi bagaimana kalau kriterianya ditambah lagi, misalnya jumlah pegawai yang mempunyai gaji di atas 800.000 tetapi dibawah 1.700.000? Kita dapat menggunakan array formula berikut:

=SUM((C2:C11>800000)*(C2:C11<1700000)*1) akhiri dengan CTRL+SHIFT+ENTER sehingga tampak menjadi

{=SUM((C2:C11>800000)*(C2:C11<1700000)*1)}

Kalau mencari jumlah gaji yang diterima oleh pegawai yang mempunyai gaji di atas 800.000 tetapi dibawah 1.700.000, kita masukkan array formula

{=SUM((C2:C11>800000)*(C2:C11<1700000)*(C2:C11))}

Selanjutnya kita akan mencari jumlah pegawai yang ada pada departemen A dan B, array formula yang kita masukkan adalah

{=SUM((B2:B11=”A”)*1)+SUM((B2:B11=”B”)*1)}dan jumlah gaji yang diterima oleh semua pegawai pada departemen A dan B dapat diperoleh dengan memasukkan array formula

{=SUM((B2:B11=”A”)*(C2:C11))+SUM((B2:B11=”B”)*(C2:C11))}

Formula untuk kriteria seperti C2:C11>800000 atau B2:B11=”A” akan menghasilkan nilai boolean TRUE atau FALSE, dan dalam operasi tersebut untuk TRUE akan menghasilkan nilai 1 dan FALSE menghasilkan nilai 0. Pada pencarian besar gaji di atas 800.000 tetapi dibawah 1.700.000, setiap kriteria dikalikan dengan kriteria yang lain selanjutnya dikalikan dengan kolom hasil yang akan dijumlah, karena kriteria tersebut bersifat kumulatif, harus memenuhi kedua kriteria yang ada. Sedang pada pencarian besar gaji pada departemen A dan B, setiap kriteria dikalikan dulu dengan kolom hasil yang akan dijumlah baru ditambahkan dengan perkalian kriteria lainnya dengan kolom hasil yang akan dijumlah, karena pada perhitungan ini kita cukup memenuhi satu kriteria saja yaitu departemen A atau departemen B.

Dan yang perlu diperhatikan selanjutnya, jika kita akan mengedit array formula yang telah dimasukkan, maka tanda kurung kurawal {..} akan hilang. Dan untuk menyudahi pengeditan tersebut maka penekanan CTRL+SHIFT+ENTER adalah keharusan.

Semoga benang merah dari apa yang saya tuliskan dapat diperoleh, sehingga kemudian dapat menerapkannya pada perhitungan yang lebih komplek yang sulit dilakukan dengan fungsi-fungsi yang kita kenal sebelumnya.

FUNGSI IF PADA MICROSOFT EXCEL

Contoh klasik misalnya pada saat membuat raport. Kita bisa bikin beberapa kolom berisi nilai per mata pelajaran, lalu dibuat rata-ratanya. Nah, sekarang menentukan apakah pemilik raport ini akan naik kelas atau tidak.

Aturannya, jika rata-rata di atas 6, maka naik kelas, di bawah 6 maka tidak naik kelas. Tentu mudah membuat rumusnya dengan excel. Misalkan nilai rata-rata ada di sel A10, maka rumusnya:

=IF(A10>=6,”Naik”,”Tidak Naik”)

Kita lihat lagi rumus di dalam kurung. A10>6 maksudnya syarat bahwa sel A10 lebih besar dari 6. Berikutnya, setelah koma, kita masukkan kategori “Naik”, jika sel A10 memang lebih besar dari 6. Perhatikan bahwa untuk membuat pernyataan dalam bentuk teks, gunakan tanda petik. Kemudian, setelah tanda koma, kita berikan kategori “Tidak Naik”. Kategori ini akan muncul bila ternyata sel A10 nilainya kurang dari 6.

Contoh ini mungkin terlalu sederhana. Bagaimana jika ada syarat lain, misalnya nilai pelajaran Agama harus di atas 7? Jangan khawatir, excel masih bisa menulis rumus untuk ini.

Katakanlah nilai pelajaran agama ada di sel A2. Maka kita paksa Excel berpikir: sel A10 harus di atas 6. Kalau ya, lihat set A2, harus di atas 7. Kalau ya, berarti naik, kalau tidak berarti tidak naik. Nah, begini cara kita mengajari Excel:

=IF(A10>=6,(IF(A2>=7,”Naik”,”Tidak Naik”)),”Tidak Naik”)

Ribet? Ya, memang. Bagaimana kalau syaratnya diperbanyak menjadi 3 atau lebih? Misalnya, selain pelajaran Agama di sel A2, pelajaran Bahasa Indonesia (sel A3) juga harus lebih besar dari 6.

Ada cara lain yang lebih mudah dibaca, pakai operator AND. Perhatikan contoh berikut:

=IF(AND(A2>=7,A3>=6,A10>=6),”Naik”,”Tidak Naik”)

Masih memakai fungsi IF, hanya sekarang kita pakai tambahan AND. Semua syarat di dalam tanda kurung () setelah AND, harus terpenuhi semua, baru dinyatakan Naik, jika salah satu saja tidak terpenuhi, akan dinyatakan Tidak Naik.

FUNGSI  HLOOKUP & VLOOKUP

Excel menyediakan fungsi if untuk melakukan pengujian atau biasa juga disebut dengan test kondisi,fungsi if biasanya digunakan untuk melakukan pengujian dimana ketentuannya dibuat dalam bentuk kalimat. Bentuk dari fungsi if jika hanya dua kondisi adalah =if(test_kondisi,nilai_bernar,nilai_salah), tetapi jika lebih dari dua kondisi bentuknya bisa digunakan =if(text_kondisi,nilai_benar,if(test_kondisi,nilai_benar,nilai_salah)), dengan kata lain jika kondisi banyak maka harus diatur nilai salahnya menjadi test kondisi lagi.Sedangkan jika ketentuan sudah dalam bentuk tabel atau sudah disusun berdasarkan kolom atau baris maka excel menyedian fungsi pembacaan tabel, memang tidak mungkin melakukan atau mengerjakannya menggunakan fungsi If tetapi sudah merupakan suatu pekerjaan yang sangat sia-sia karena membutuhkan banyak waktu. contohnya dalam pengerjaan latihan dibawah ini, ( Gambar diatas)

untuk mencari nama gerbong, harga tiket, tujuan/jurusan,jam pergi, jenis tiket kalau digunakan menggunakan if maka rumus if yang dipakai akan panjang karena ada 15 kondisi. untuk menyingkatnya digunakanlah fungsi pembacaan tabel dimana rumusnya sebagai berikut :=vlookup(Nilai_patokan_pencarian,sumber_tabel,nomor_kolom_yang_dicari,Match)=Hlookup(Nilai_patokan_pencarian,sumber_tabel,nomor_baris_yang_dicari,Match)dimana match bernilai true/1 atau false/0, jka tidak diberikan maka standarnya adalah 1

Untuk contoh Kasus diatas digunakan rumusmencari nama Gerbong : =VLOOKUP(B9,$B$25:$E$39,2,0)mencari harga tiket : =VLOOKUP(B9,$B$25:$E$39,3,0)mencari tujuan/jurusan : =VLOOKUP(B9,$B$25:$E$39,4,0)

jika anda perhatikan di rumus tersebut terdapat $B$25:$E$39 yang maksudnya sama dengan B25:E39 hanya saja jika kamu ketikkan rumus tersebut pada kotak c9 maka rumus tersebut jika dicopykan ke baris dibawahnya maka tidak akan manjur sehingga perlu diberi tanda dollar dimana tanda dollar tersebut menandakan absolut atau tidak akan berubah.

contohnya :

  1. Pada kotak c9 anda mengetikkan rumus =$b9*250 maka rumus tersebut jika dikopikan ke kotak d9 maka rumus yang terpoki adalah $b9 sedangkan jika dikopi pada kotak c10 maka hasilnya adalah $b10 karena $ didepan b menandakan bahwa kolom b-nya tidak akan berubah ketika rumus dikopi ketempat lain
  2. Pada kotak c9 anda mengetikkan rumus =b$9*250 maka rumus tersebut jika dikopi pada kotak d9 maka rumus tersebut akan berubah menjadi =c$9*250 sedangkan jika dikopi pada kotak c10 maka rumus tersebut akan berubah menjadi =b$9*250

atau bentuk sederhananya saya gambarkan menggunakan kolom dibawah ini

rumus sebelumnya rumus dicopi rumus dicopy

rumus sebelumnya      rumus dicopi       rumus dicopy

pada kotak            ke kotak                 ke kotak

c10                     c11                         d10

===========================================================

=a10*b10              =a11*b11              =b10*c10

=$a10*$b10          =$a11*$b11          =$a10*$b10

=a$10*b$10          =a$10*b$10          =a$10*b$10

=$a$10*$b$10         =$a$10*$b$10     =$a$10*$b$10

Bila dicopy satu kolom kekanan maka cell pada rumus didalamnya pun akan bergeser satu kolom kekanan. Demikian pula bila mengcopy satu baris ke bawah, maka semua cell pada rumus akan berganti pula satu baris kebawah. Inilah yang disebut cell relative

Sedangkan cell absolut yaitu cell dalam rumus yang tidak akan berubah-ubah meski pun di copy kemana-mana. Caranya hanya menempatkan karakter ” $ ” di awal penulisan pada kolom dan baris cell. perhatikan hal berikut :

1. $A$1 : kolom dan baris absolut dan tidak akan berubah meski dipindah

2. $A1 : kolom absolut sedangkan baris tidak.

3. A$1 : kolom dapat berubah-ubah sedangkan baris tidak

4. A1 : kolom dan baris dapat berubah (cell relatif)

  • fungsi flookup

Fungsi vlookup merupakan fungsi bantuan references. Fungsi Vlookup dipakai untuk menghasilkan nilai pada tabel secara vertikal.

Penulisan :

=VLOOKUP(nama_baris;tabel;kolom_pencarian;range_lookup)

MISAL :

solusi :

Pada A8 masukkan nilai NIM terlebih dahulu yang terdapat pada tabel NIM-Nama. kemudian pada B8 ketikkan formula =LOOKUP(A8;$A$2:$B$5;2;0).

•  Fungsi hlookup

Fungsi Hlookup merupakan fungsi bantuan references juga. bedanya Fungsi Hlookup dipakai untuk menghasilkan nilai pada tabel secara horizontal.

Penulisan :

=HLOOKUP(nama_kolom;tabel;baris_pencarian;range_lookup)

MISAL :

solusi :

– Nama pada cell B11 adl =VLOOKUP(A11;$A$2:$D$5;2;0)

– Perihal pada cell D11 adl = VLOOKUP(C11;$F$2:$H$5;2;0)

– Judul pada cell E11 adl = VLOOKUP(C11;$F$2:$H$5;3;0)

Leave a response and help improve reader response. All your responses matter, so say whatever you want. But please refrain from spamming and shameless plugs, as well as excessive use of vulgar language.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.