Cara Cepat Belajar Fungsi dan Rumus Microsoft Excel

By | September 12, 2016

Jika Anda banyak bekerja mengolah data dalam Excel, tentunya Anda tidak dapat menghindari penggunaan Fungsi dan Rumus Microsoft Excel. Rumus dalam Excel merupakan hasil kombinasi operator matematika, fungsi dan angka untuk mendapatkan nilai tertentu yang diinginkan di dalam Excel. Sedangkan Fungsi dalam Excel merupakan bentuk rumus jadi atau formulasi yang bertujuan untuk mendapatkan nilai tertentu berupa angka atau format tertentu dalam teks dengan maksud untuk memudahkan pengguna excel dalam melakukan pengolahan angka atau teks. Jadi di dalam rumus, biasanya juga terkandung fungsi excel serta terdapat operator matematika untuk mencari nilai atau hasil tertentu, sedangkan dalam fungsi, biasanya sudah tidak banyak diperlukan lagi operator matematika karena excel sudah menyederhanakannya serta memformulasikannya di dalam fungsi yang bersangkutan. Sering pula fungsi ini disebut rumus, karena di dalamnya terkandung formulasi yang sudah disederhanakan. Agar Anda tidak bingung langsung saja lihat cara pembuatannya di dalam excel.

Sebagai contoh cara penulisan rumus dan fungsi :

beda-rumus-dan-fungsi-excel

Rumus :

A1=2+3+5

Hasilnya 10

Contoh di atas adalah contoh penulisan rumus dengan menggunakan operator matematika + .

Berikut contoh fungsi excel untuk mengolah angka :

Fungsi :

A2=Sum(2,3,5)

Hasilnya 10, di mana :

Sum merupakan Fungsi Excel, yang tugasnya melakukan penjumlahan di dalam excel.

Sedangkan fungsi untuk pengolahan teks dapat dilihat pada contoh berikut ini :

A3=LEFT(“DKI Jakarta”,3)

Hasilnya DKI.

Contoh penggabungan rumus dan fungsi :

A4=SUM(2,3,5)+90

Hasilnya adalah 100

Bagaimana Menuliskan Rumus Microsoft Excel

 

Penulisan rumus dan fungsi ini di dalam excel kita lakukan dengan dimulai tanda = (sama dengan). Rumus ini dapat kita masukkan ke dalam salah satu sel di Excel.

Sebagai contoh Anda dapat menuliskan rumus di sel A1 dengan cara :

Klik kiri mouse atau pastikan kursor Anda berada pada sel A1, lalu pada kolom formula ketikkan operator = (sama dengan).

cara-menuliskan-rumus-microsoft-excel

Masukkan fungsi atau rumus yang diinginkan, lalu setelah selesai tekan ENTER.

A1=2+3+5 atau A2=Sum (2,3,5).

Jika dilihat contoh di atas, kita melihat di sini menulis dengan fungsi excel akan mempermudah kita dalam penggunakan operator matematika, sebagai contoh kita tidak perlu menuliskan tanda + (tambah berulangkali), cukup dengan menggunakan fungsi Sum kemudian kita masukkan angka atau range penjumlahan (mengenai range penjumlahan ini akan dipelajari kemudian).

Bagaimana Mengedit Rumus Microsoft Excel

 

Hampir sama dengan cara memasukkan rumus dalam microsoft excel, Anda hanya perlu mengklik kembali sel di mana letak rumus berada. Ubah rumus sesuai dengan yang diinginkan, dan ingat bahwa tanda atau operator = (sama dengan) harus tetap ada pada permulaan rumus atau formula.

mengedit-rumus-microsoft-excel

Operasi Rumus Microsoft Excel sesuai dengan Kaidah Matematika

 

Bagi Anda yang sudah memahami penggunaan operator matematika, tentu Anda tidak akan mengalami kesulitan dalam menuliskan rumus dalam Excel. Excel melakukan operasi matematika sesuai dengan kaidah yang berlaku dalam matematika. Misalkan ada percampuran antara perkalian dan penjumlahan, maka excel akan mendahulukan operasi perkalian terlebih dahulu lalu menjumlahkannya. Excel juga akan mendahulukan operasi yang menggunakan tanda kurung di dalamnya.

rumus-matematika

Contoh :

A5=3*4+2

Maka hasilnya adalah 14

Contoh penggunaan tanda kurung :

A6=3*(4+2)

Maka hailnya adalah 18

A7=8/(2+2)

Maka hailnya adalah 2

Bagaimana Melakukan Copy Paste Rumus dalam Excel

 

Anda dapat melakukan copy dan paste suatu rumus microsoft excel tanpa mengikutsertakan data di dalamnya. Dalam melakukan Copy dan paste rumus, mula-mula Anda arahkan kursor pada sel yang berisi rumus atau formula excel kemudian lakukan perintah copy pada sel tersebut (copy rumus pada sel A4).

copy-rumus

Selanjutnya pindahkan kursor pada sel lainnya yang akan dituliskan rumus yang sudah dicopy tadi, lalu lakukan perintah paste dengan cara mengklik kanan pada mouse pilih perintah paste formulas (pindahkan rumus sel A4 ke sel E4).

paste-rumus

hasil-copy-rumus-excel

Memasukkan Fungsi Excel

 

Setiap fungsi di dalam excel memiliki prinsip dasar penulisan yang sama. Angka atau teks di dalam fungsi dapat digantikan oleh variable nama sel atau nama range. Antara variable satu dengan lainnya dapat dipisahkan dengan koma atau titik koma.

Berikut ini langkah mudah yang bisa Anda lakukan untuk meng-input atau memasukkan Fungsi Excel :

  • Tentukan sel yang akan dimasukkan rumus dengan mengklik sel tertentu
  • Ketikkan fungsi pada formula bar dengan dimulai dengan tanda “=” (sama dengan)
  • Lanjutkan dengan mengetikkan fungsi yang diperlukan, lalu masukkan perumusannya sesuai dengan petunjuk yang diberikan excel, seperti pada gambar di bawah :

input-fungsi-excel

  • Jika Anda masih ragu untuk memilih rumus apa yang digunakan, Anda dapat memunculkan jendela fungsi, yang akan menampilkan fungsi beserta kategori penggunaannya, caranya :

Sebelum mengetikkan teks pada formula bar, klik pada icon formula di sebelah kiri kolom formula bar sehingga muncul jendela Insert Funtion :input-fungsi-new

Anda dapat memilih fungsi mana yang akan digunakan. Untuk melihat fungsi lainnya berdasarkan kategori, Anda dapat mengklik drop down Or select a category. Anda juga dapat melihat kegunaan fungsi yang Anda pilih pada bagian bawah kolom pilihan fungsi, seperti pada gambar dibawah :

input-fungsi-info

Selanjutnya Anda tinggal mengikuti perintah yang diberikan sesuai dengan fungsi yang dipilih.

  • Penulisan Rumus pada fungsi harus lengkap dan tidak boleh kurang 1 tanda pun yang dapat mengakibatkan rumus tidak berjalan atau menjadi error. Contoh penulisan rumus fungsi yang benar : A2=SUM(2,3,5). Dimana fungsi ini selalu disertai dengan tanda buka kurung “(“ dan tutup kurung “)”.

Penggunaan Operator Dalam Rumus Microsoft Excel

 

Penggunaan Operator Matematika dalam Excel

no Operator Aritmatika Deskripsi Contoh
1 + (tanda tambah) penambahan 4+2 (hasilnya 6)
2 – (tanda minus) pengurangan 4-2 (hasilnya 2)
3 * (tanda asterik) perkalian 4*2 (hasilnya 8)
4 / (garis miring) pembagian 4/2 (hasilnya 2)
5 ^ (eksponensial) pemangkatan 4^2 (hasilnya 16) atau 8^(1/3) hasilnya 2

Penggunaan Operator Perbandingan dalam Excel

no Operator Perbandingan Deskripsi Contoh
1 = (tanda sama) sama dengan B1=C1
2 > (tanda lebih besar) lebih besar dari B1>C2
3 < (tanda lebih kecil) lebih kecil C2<B1
4 >= (tanda lebih besar sama dengan) lebih besar atau sama dengan A2>=A1
5 <> (tidak sama dengan) membandingkan dua data (hal) yang tidak sama A1<>A2

Penggunaan Operator Penggabungan pada Teks

no Operator Perbandingan Deskripsi Contoh dalam Excel
1 & (tanda dan / ampersand) menggabungkan dua rangkaian teks sehingga menjadi satu rangkaian “DKI” & “Jakarta” menjadi “DKI Jakarta”

Penggunaan Operator Referensi

no Operator Referensi Arti Contoh dalam Excel
1 : (titik dua) Merupakan operator yang mengindikasikan area yang dibatasi antara sel pertama (sebelah kiri titik dua) sampai dengan sel terakhir (sebelah kanan titik dua) sesuai dengan yang diisikan C2:C15
2 (tanda koma atau titik koma) Merupakan operator yang menggabungkan beberapa area SUM(C2:C15;D5:D10)
3 (spasi) Operator irisan yang hasilnya didapat dengan cara mengambil irisan beberapa area SUM(B2:E2 D1:D5) dimana hasil irisannya adalah di D2 sehingga penjumlahan yang dihasilkan adalah angka di D2.

Pada poin no 2 Jika Anda bekerja dengan regional seting komputer dalam bahasa Indonesia maka pemisahan antar variable dilakukan dengan menggunakan tanda titik koma (contoh : SUM(C2:C15;D5:D10)). Sedangkan jika regional seting komputer menggunakan bahasa Inggris pemisah yang digunakan adalah dengan menggunakan tanda koma (contoh : SUM(C2:C15,D5:D10)).

Sekarang, jika Anda sudah paham mengenai penggunaan operator di atas, jika operator di atas dituliskan secara bersamaan dalam perumusan di excel..pertanyaannya.. mana yang terlebih dahulu diekseskusi oleh excel ?

Berikut ini dapat Anda jadikan referensi mengenai urutan kerja excel dalam mengeksekusi formulanya :

no Operator Deskripsi
1 titik dua ; tanda koma; spasi Operator referensi
2 Operator negasi Seperti tanda minus pada -1
3 % Persen
4 ^ Pangkat
5 * dan / Perkalian dan pebagian
6 + dan - Penjumlahan dan pengurangan
7 & Penghubung antara 2 teks
8 = < > <= >= <> Perbandingan

Seperti dalam matematika, jika Anda perlu memprioritaskan operasi tertentu dalam rumus microsoft excel (yang bukan dalam urutan prioritas) maka Anda perlu menggunakan tanda kurung pada bagian operasi tertentu tersebut.

Misalkan :

A8= 2+3*2 hasilnya adalah 8 dan akan berbeda hasilnya jika Anda menginginkan operasi penambahan dijalankan terlebih dahulu yaitu dengan penambahan tanda kurung :

A9=(2+3)*2 dimana hasilnya adalah 10.

operasi-rumus-excel

Penggabungan Fungsi di dalam Fungsi pada Rumus Micrososft Excel

 

Di atas telah dipelajari bagaimana cara memasukkan fungsi dalam suatu sel excel, yaitu dengan dimulai dengan tanda sama dengan (=) lalu diikuti dengan nama fungsinya. Karena kita perlu membuat rumus sesederhana mungkin, maka jika ada rumus atau operasi matematika yang bisa dibuat ke dalam fungsi, akan lebih baik lagi karena penulisannya menjadi lebih sederhana.

Misalkan : A1=2+3+5 dapat dibuat menjadi SUM(2,3,5)

Selanjutnya bisakah suatu fungsi dibuat di dalam suatu fungsi excel ? Jawabannya.. tentu saja bisa!!

Misalkan :

Fungsi akar dalam excel dapat dituliskan dengan SQRT, contoh :

A10=SQRT(16) hasilnya adalah 4.

Contoh penggabungan fungsi di dalam fungsi :

A11= SUM(SQRT(16)/2) hasilnya adalah 2.

penggabungan-fungsi-rumus

Anda dapat berlatih dengan menggunakan fungsi excel lainnya.

Penggunaan Referensi dalam Rumus Microsoft Excel

 

Ketika Anda bekerja dengan formula excel, jika dimungkinkan, adalah lebih baik jika Anda menggunakan referensial dalam menuliskan rumus tertentu ketimbang memasukkan angka secara langsung ke dalam Rumus Microsoft Excel. Hal ini dimaksudkan agar ketika ada perubahan angka atau nilai, Anda tidak perlu merubah pada rumusnya tetapi Anda dapat mengubahnya secara langsung hanya pada sel excel yang bersangkutan.

Sebagai contoh :

Kita kembali pada rumus sederhana :

A2=SUM(2,3,5) adalah lebih baik jika Anda mengubah angka tersebut menjadi referensi ke dalam sejumlah sel. Misalkan :

A12=SUM(G1,G2,G3). Anda tinggal memasukkan nilai yang ingin dijumlahkan pada sel G1=2,G2=3 dan G3=5.

referensi-dalam-rumus-excel

Jika Anda perlu mengubah nilai / angka nya, Anda tidak perlu mengubah angka pada rumus fungsi SUM tetapi cukup pada nilai yang tertera pada sel yang telah direferensikan pada rumus fungsi SUM.

Secara default excel menggunakan referensial abjad untuk kolom seperti A, B, C di mana A s/d sampai XFD menggambarkan kolom yang ada pada excel yaitu sebanyak 16,384 kolom dan menggunakan angka untuk baris dari 1 sampai 1.048.576 baris. Sebagai contoh jika dituliskan alamat sel C3 berarti sel tersebut merefer ke kolom C baris ke 3.

Berikut Contoh penulisan Referensi Sel pada Excel

no Penjelasan Referensi Penulisan
1 Sel dalam kolom C dan baris ke 8 C8
2 Range / Area kolom C pada baris ke 10 sampai 20 C10:C20
3 Range / Area kolom C pada baris ke 10 sampai kolom F baris ke 20 C10:F20
4 Semua sel dalam baris ke 8 8:8
5 Semua sel dalam baris ke 8 sampai 15 8:15
6 Semua sel dalam kolom H H:H
7 Semua sel dalam kolom H sampai K H:K

Selain referensi sel pada satu worksheet yang sama, Anda juga dapat menjumlahkan sel dari worksheet yang berbeda.

Contoh :

A13=SUM(ContohData!D3:D4)

referensi-dalam-rumus-excel-2

Penjumlahan di atas menggunakan data dari worksheet yang berbeda dengan nama ContohData.

Sedangkan D3:D4 merupakan sel di mana data dijumlahkan. Perhatikan cara penulisannya nama worksheet dan referensi sel dipisahkan dengan tanda ! (tanda seru).

Pada penggunaan referensi dalam Rumus Microsoft Excel, Anda perlu mengetahui apakah itu referensi Relatif, referensi Absolut dan referensi Campuran :

Referensi Relatif

Jika Anda menuliskan referensi sel dengan penulisan abjad dan kolom pada rumus atau fungsi excel, misalkan dengan penulisan A3:B3 maka cara penulisan ini disebut dengan penulisan cara relatif. Contoh SUM(G1:G3). Jika Anda meng-copy pastekan rumus ini pada sel lainnya maka secara otomatis excel akan merubah referensi sel G1:G3 tergantung posisi sel yang di-copy kan rumusnya. Sebagai contoh, saya memiliki rumus pada sel A14=SUM(G1:G3), lalu rumus ini saya copy pastekan pada A15 dan secara otomatis excel mengubahnya menjadi A15= SUM(G2:G4).

referensi-dalam-rumus-excel-3

Referensi Absolut

Selain cara penulisan referensi sel secara relative seperti pembahasan kita sebelumnya, excel juga memberikan kita fitur untuk mengunci referensi sel agar tidak berubah ketika kita memindahkan atau meng-copy rumus tersebut ke sel lainnya. Cara penulisannya yaitu dengan menambahkan tanda $ di belakang abjad atau nomor baris pada referensi sel. Kita kembali ke contoh penulisan G1:G3 pada rumus SUM(G1:G3). Agar referensi sel pada rumus tersebut tidak berubah kita mengubah penulisannya menjadi A16=SUM($G$1:$G$3). Dengan penambahan tanda $ ini, jika Anda meng-copy rumus ini pada sel lainnya (A17) referensi range/area pada rumus tersebut tidak akan berubah.

referensi-dalam-rumus-excel-4

Referensi Mix (Relatif-Absolut)

Selain menggunakan referensi di atas (relatif atau absolut), Anda juga dapat menggunakan referensi sel dengan kombinasi antara relatif dan absolut. Dengan kombinasi ini nantinya Anda dapat menentukan kolom atau baris apa yang perlu berubah jika di-copykan sesuai dengan posisi sel yang di-copykan. Contoh pada rumus microsoft excel untuk penjumlahan sel dari G1 sampai G3, SUM(G1:G3), dapat dituliskan dengan SUM(G$1:G$3) atau SUM($G1:$G3). Artinya jika didahului dengan tanda $ maka kolom atau baris tersebut tidak akan berubah ketika di-copykan ke sel lainnya, sementara yang tidak diberi tanda $ akan berubah sesuai dengan posisi sel yang di-copykan.

Referensi dengan metode 3-D

Pada referensi metode 3-D ini digunakan dengan cara menuliskan alamat worksheet yang bersangkutan dari beberapa worksheet. Hal ini dilakukan jika data yang diambil pada sel memiliki alamat yang sama namun dengan worksheet yang berbeda. Dari pada panjang lebar dijelaskan, lebih baik Anda ikuti contoh berikut agar lebih mudah mengerti :

C1=SUM(Sheet1:Sheet15!B5). Arti dari rumus ini adalah pada C1 akan dijumlahkan sel pada B5 yang akan diambil dari Worksheet dengan nama Sheet1, Sheet2, Sheet3…dst sampai Sheet15.

Sebagai catatan, penerapan metode 3-D ini dapat digunakan dengan batasan sebagai berikut :

  • Perumusan hanya dapat digunakan pada fungsi : AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, SUM, VAR.P, VAR.S, VARA, and VARPA.
  • Metode 3-D ini tidak dapat digunakan pada Rumus Array pada Excel (akan dijelaskan kemudian)
  • Metode 3-D tidak dapat digunakan pada operator irisan atau intersection pada excel

Namun, jika sheet yang dijadikan referensi di atas dipindahkan, dihapus, di-copy atau dibuat seheet baru apa akibatnya bagi hasil perumusan yang menggunakan sheet tersebut ?

  • Jika Anda membuat sheet baru yang berada di antara Sheet1 sampai Sheet15, excel akan ikut melakukan penjumlahan kolom B5 pada Sheet baru tersebut
  • Jika Sheet yang berada diantara Sheet1 dan Sheet15 ada yang dihapus, maka excel akan mengeluarkan hasil perhitungan sheet yang dihapus tersebut.
  • Jika Anda memindahkan Sheet1 atau Sheet15 sebagai titik acuan, excel akan melakukan penyesuaian perhitungan kembali sesuai dengan posisi Sheet1 atau Sheet15 yang baru.
  • Jika sheet yang menjadi acuan titik awal dan akhir ada yang dihapus (missal Sheet1 atau Sheet15 dihapus), maka excel akan melakukan penyesuaian perhitungan untuk mengakomodasi range sheet yang baru.

Menggunakan Rumus Array

Sebagai ilustrasi untuk memudahkan pengertian Rumus Array, perhatikan penggunaannya sebagai berikut :

Rumus array =SUM(F20:F27*G20:G27) sama dengan: =SUM(F20*G20,F21*G21,F22*G22,F23*G23,F24*G24,F25*G25,F26*G26,F27*G27).

Membuat rumus array cukup mudah, yaitu Anda tuliskan rumusnya seperti =SUM(A2:A10*B2:B10), lalu jika sudah selesai tekan Ctrl+Shift+Enter setiap kali Anda selesai memasukkan atau mengedit rumus array.

penggunaan-rumus-array

Penggunaan rumus Array lebih lanjut dapat dipelajari pada artikel berikut : Panduan dan Contoh Rumus Array.

Menggunakan Nama Referensi pada Fungsi dan Rumus Microsoft Excel

Nama referensi adalah pemberian nama pada satu atau sekelompok sel (area tertentu) pada worksheet excel dengan tujuan untuk memudahkan penulisan area  atau sekelompok sel pada perumusan Excel terutama jika digunakan berulangkali. Sebagai ilustrasi Anda memiliki referensi sel : A1:E15 untuk dijumlahkan. Sehingga Anda akan menuliskan rumus penjumlahannya sebagai SUM (A1:E15). Namun, jika Anda bekerja dengan banyak data, dengan cara ini Anda berpotensi untuk lupa data yang diambil ini data untuk apa dan berpotensi memungkinkan terjadinya typo atau salah ketik sehingga mengakibatkan error.

Namun jika Anda dapat menamakan range A1:E15 dengan nama tertentu misalkan dengan nama DataPenjualan anda akan lebih mudah dalam menuliskan serta dalam mengidentifikasi data apa yang digunakan. JIka penamaan ini sudah dilakukan Anda dapat menuliskan kembali rumus nya menjadi SUM (DataPenjualan).

Anda dapat dengan mudah melakukan penamaan range atau area pada excel yaitu :

Cara I :

  • Pilih terlebih dahulu range sel yang akan Anda namai
  • Selanjutkan ketikkan nama range yang sudah Anda pilih pada Name Box seperti pada gambar :

menggunakan-nama-referensi

Cara II :

  • Pilih terlebih dahulu range sel yang akan Anda namai
  • Selanjutkan pilih tab Formulas > klik menu Name Manager > klik tombol New lalu isikan namanya sesuai yang diinginkan seperti pada gambar. Jika sudah selesai klik tombol OK.

menggunakan-nama-referensi-2

Contoh Penerapan Fungsi dan Rumus Microsoft Excel

 

LEN

Fungsi LEN digunakan untuk menghitung jumlah karakter dalam suatu kata atau kalimat dalam sel excel.

Contoh penggunaan fungsi LEN dengan rumus :

D29=LEN(A29)

rumus-fungsi-len

LEFT

Fungsi Left digunakan untuk mengambil sejumlah karakter tertentu yang pengambilannya dimulai dari sebelah kiri karakter.

Contoh penggunaan fungsi Left :

D31= LEFT(A31,5)

rumus-fungsi-left

MID

Fungsi MID digunakan untuk mengambil sejumlah karakter tertentu yang dimulai dari posisi karakter yang ditentukan pada rumus.

Contoh penggunaan fungsi Mid :

D34 = MID(A34,7,6)

rumus-fungsi-mid-1

Contoh penggabungan fungsi Mid dan Find dalam Rumus Microsoft Excel untuk mencari/menampilkan karakter tertentu setelah tanda “-“ :

rumus-fungsi-mid-2

NETWORKDAYS

Fungsi networkdays adalah fungsi untuk mencari jumlah hari kerja (5 hari dalam seminggu) diantara dua buah tanggal.

Contoh penggunaan fungsi Networkdays :

D42=NETWORKDAYS(A42,B42)

rumus-micrososft-excel-fungsi-networkdays

PROPER

Fungsi Proper digunakan untuk menampilkan tampilan baku dari penulisan suatu nama yang memerlukan huruf besar pada setiap awal kata.

Contoh penggunaan fungsi Proper :

D44 = PROPER(A44)

rumus-fungsi-proper

DATEDIF

Fungsi Datedif digunakan untuk melihat perbedaan dalam hari, bulan atau tahun dari dua tanggal yang

berbeda.

Contoh penggunaan fungsi Datedif (dalam tahun) :

D47=DATEDIF(A47,B47,”y”)

rumus-fungsi-datedif

SUMIF

Fungsi Sumif digunakan untuk melakukan penjumlahan serangkaian angka dengan syarat atau kondisi tertentu.

Contoh penggunaan fungsi Sumif :

D50=SUMIF(B50:B56,”>20″)

rumus microsoft excel sumif

TRIM

Fungsi Trim digunakan untuk menghilangkan tanda spasi (“ “) yang terdapat dalam serangkaian kata atau kalimat excel.

Contoh penggunaan fungsi Trim :

D58 =TRIM(A58)

rumus-fungsi-trim

Demikianlah cara mudah menggunakan rumus dan fungsi dalam excel. Semakin Anda berlatih semakin Anda lancar menggunakannya.

File excel yang digunakan dalam contoh juga dapat Anda download di sini : file-rumus-excel.

Semoga bermanfaat bagi Anda yang sedang belajar excel secara mandiri terutama dalam hal pengolahan data excel.

Baca juga artikel : rumus excel untuk fungsi perhitungan

 
Share

Leave a Reply

Your email address will not be published. Required fields are marked *