Rumus Excel untuk Fungsi Perhitungan

By | May 19, 2016

Fungsi perhitungan adalah fungsi yang hampir selalu pernah digunakan oleh Anda yang sering menggunakan Rumus Excel untuk memantain atau mengolah data. Meskipun Anda pernah menggunakannya, tidak selamanya Anda dapat mengingat semua fungsi perhitungan dalam Excel. Terkadang jika kita lupa kita perlu me-review kembali fungsi dari rumus dan fungsi Excel yang digunakan.

Berikut ini beberapa fungsi kritikal yang sering dipakai ketika kita menggunakan Excel dalam perhitungan serta tujuan pemakaian nya :

 

Rumus Excel untuk Perhitungan Statistik Dasar

 

Menghitung Jumlah Sel Yang Mengandung Angka

Untuk menghitung jumlah sel yang mengandung angka, gunakan fungsi  :

COUNT(data_table_array);

C2=COUNT(A1:A9);

Rumus-Excel-Perhitungan

Menghitung Jumlah Sel Yang Tidak Kosong

Untuk menghitung jumlah sel yang tidak kosong, gunakan fungsi :

COUNTA(data_table_array);

C3=COUNTA(A1:A9);

Rumus-Perhitungan-Excel-2

Terlihat pada contoh di atas pada sel A1:A9 ada 7 sel yang terisi dan ada 2 sel yang kosong.

Menghitung Jumlah Sel Yang Kosong

Untuk menghitung jumlah sel yang kosong, gunakan fungsi :

COUNTBLANK(data_table_array);

C4=COUNTBLANK(A1:A9);

Rumus-Perhitungan-Excel-3

Pada contoh di atas terlihat ada 2 sel yang kosong dan ada 7 sel yang terisi pada sel A1:A9.

Menghitung Jumlah Sel Pada Kondisi Tertentu dengan COUNTIF

Untuk menghitung jumlah sel dengan kondisi tertentu, gunakan fungsi :

COUNTIF(data_table_array;kriteria);

G2 =COUNTIF(E2:E7;”>2″);

Rumus-Perhitungan-Excel-4

Menghitung Jumlah Sel Pada beberapa Kondisi Tertentu dengan COUNTIFS

Untuk menghitung jumlah sel dengan beberapa kondisi, gunakan fungsi dan rumus excel :

COUNTIFS(data_array;kriteria;data_array1;kriteria1;data_array2;kriteria2;data_array_n;kriteria_n);

G9 =COUNTIFS(E9:E15;”Pensil”;F9:F15;”biru”);

Rumus-Perhitungan-Excel-5

Menjumlahkan Data pada Sel dengan SUM

Untuk menghitung jumlah data pada sejumlah sel, gunakan fungsi :

SUM(data_table_array);

G3 =SUM(E2:E7);

Rumus-Perhitungan-Excel-6

Menjumlahkan Data Sel pada Kondisi Tertentu dengan SUMIF

Untuk menghitung jumlah sel dengan kondisi tertentu, gunakan fungsi dan rumus excel :

SUMIF(data_array;kriteria;[data_array_penjumlahan]);

O2= SUMIF(L2:L9;”Excel”;M2:M9);

Rumus-Perhitungan-Excel-7

Catatan:

data_array_penjumlahan merupakan pilihan (ada atau tidak). Jika data_array berupa angka dan kriteria diambil dari data_array maka data_array_penjumlahan tidak diperlukan. Pada contoh di atas diperlukan data_array_penjumlahan karena data_array berupa teks yang merupakan kondisi dari penjumlahan (“Excel”).

Menjumlahkan Data Sel pada beberapa Kondisi dengan SUMIFS

Untuk menghitung jumlah sel dengan beberapa kondisi, gunakan fungsi dan rumus excel :

SUMIFS(data_array_penjumlahan;data_array1;kriteria1;data_array2;kriteria2;data_array_n;kriteria_n);

O4= SUMIFS(M2:M9;K2:K9;”Komputer”;L2:L9;”Excel”);

Rumus-Perhitungan-Excel-8

Catatan:

data_array bisa berupa baris atau kolom.

Merata-ratakan Data Sel dengan AVERAGE

Untuk menghitung rata-rata beberapa sel, gunakan fungsi :

AVERAGE(data_table_array);

T2=AVERAGE(R2:R4)

Rumus-Perhitungan-Excel-9

Merata-ratakan Data Sel pada Kondisi Tertentu dengan AVERAGEIF

Untuk menghitung rata-rata beberapa sel, gunakan fungsi dan rumus excel :

AVERAGEIF(data_array;kriteria;[data_array_ratarata]);

T3=AVERAGEIF(R2:R6;”>3″);

Rumus-Perhitungan-Excel-10

Atau

T4=AVERAGEIF(Q2:Q6;”Pensil”;R2:R6).

Rumus-Perhitungan-Excel-11

Merata-ratakan Data Sel pada beberapa Kondisi dengan AVERAGEIFS

Untuk menghitung rata-rata sel dengan beberapa kondisi, gunakan fungsi dan rumus excel :

AVERAGEIFS(data_array_rata_rata;data_array1;kriteria1;data_array2;kriteria2;data_array3;kriteria3;data_array_n;kriteria_n);

O5 =AVERAGEIFS(M2:M9;K2:K9;”Komputer”;L2:L9;”Excel”);

rumus excel

Catatan:

Data array bisa berupa baris atau kolom.

Mengalikan Array Data dengan PRODUCT

Untuk mengalikan beberapa sel, gunakan fungsi dan rumus excel :

PRODUCT(data_table_array);

M12=PRODUCT(J12:K14)

Rumus-Perhitungan-Excel-13

Catatan :

Data yang dikalikan dari J12:K14, J12*K12*J13*K13*J14*K14=2*2*2*3*1*1=24

Mengalikan Array Data dan Menjumlahkannya dengan SUMPRODUCT

Untuk mengalikan beberapa sel, gunakan fungsi dan rumus excel :

SUMPRODUCT(data_array1;data_array2;..);

M13=SUMPRODUCT(J12:J14;K12:K14)

Rumus-Perhitungan-Excel-14

Catatan :

Pada contoh di atas data_array1 (J12:J14) dikalikan dengan data_array2 (K12:K14) kemudian dijumlahkan, hasilnya : J12*K12+J13*K13+J14*K14=2*2+2*3+1*1=4+6+1=11.

Agar dapat dikalikan dan dijumlahkan, jumlah data pada data_array1 dan data_array2 harus sama.

Menentukan Nilai Terkecil dan Terbesar pada Deretan Suatu Sel

Misal pada sel A1 sampai dengan A5 terdapat deretan angka dengan nilai :

A1=3,A2=2,A3=6,A4=10,A5=3.

Dari deretan angka di atas kita dapat menentukan nilai maksimum dan minimum dengan rumus :

Nilai maksimum :

Max (range_nilai);

Nilai maksimum dari range nilai di atas adalah :

=Max (A1:A5)

= 6

Nilai minimum :

Min (range nilai);

Nilai minimum dari range nilai di atas adalah :

=Min (A1:A5)

= 2

 

Rumus Excel untuk Melakukan Pembulatan

 

Fungsi pembulatan dalam Excel sangat membantu dalam mengolah data. Jika Anda potongan kue misal sebanyak 10 potong yang akan dibagikan

kepada 3 orang rekan Anda secara merata, apakah Anda akan membagikan nya dengan 10/3=3,3333 potong ? Tentu ini akan menyulitkan.

Anda perlu angka yang lebih sederhana untuk perhitungan pembagian kue tersebut. Untuk itulah diperlukan pembulatan dalam perhitungan

termasuk dalam rumus excel pembulatan ini.

Berikut ini fungsi-fungsi yang dapat digunakan dalam melakukan pembulatan dalam perhitungan Excel :

Pembulatan ke angka terdekat

ROUND(angka,jumlah_digit)

angka : Merupakan angka yang akan dibulatkan

jumlah_digit : jumlah digit yang diinginkan (misal : 1 akan menjadi 0.a, 2 akan menjadi 0.aa, 3 akan menjadi 0.aaa)

Jika :

jumlah_digit>0 : akan membulatkan ke nilai terdekat sesuai jumlah desimal yang dituliskan

jumlah_digit=0 : akan membulatkan angka ke angka integer terdekat

jumlah_digit<0 : akan membulatkan angka yang berada di sebelah kiri desimal ke nilai terdekat sesuai dengan jumlah_digit yang dimasukkan.

Contoh penggunaan Rumus Excel untuk Fungsi Round :

= ROUND(11.2367,3).

Akan menghasilkan nilai 11,237

= ROUND(11.2367,4)

Akan menghasilkan nilai 11.2367.

= ROUND(11.2367,0)

Akan menghasilkan nilai 11.

= ROUND(11.2367,-1)

Akan menghasilkan nilai 10.

Pembulatan satu arah ke atas

ROUNDUP(angka,jumlah_digit)

angka : Merupakan angka yang akan dibulatkan

jumlah_digit : jumlah digit yang diinginkan (misal : 1 akan menjadi 0.a, 2 akan menjadi 0.aa, 3 akan menjadi 0.aaa) dengan pembulatan ke atas

Contoh penggunaan Rumus Excel untuk Fungsi RoundUp :

= ROUNDUP(11.2367,3).

Akan menghasilkan nilai 11,237

= ROUNDUP(11.2367,4)

Akan menghasilkan nilai 11.2367.

= ROUNDUP(11.2367,0)

Akan menghasilkan nilai 12.

= ROUNDUP(11.2367,-1)

Akan menghasilkan nilai 20.

Pembulatan satu arah ke bawah

ROUNDDOWN(angka,jumlah_digit)

angka : Merupakan angka yang akan dibulatkan

jumlah_digit : jumlah digit yang diinginkan (misal : 1 akan menjadi 0.a, 2 akan menjadi 0.aa, 3 akan menjadi 0.aaa) dengan pembulatan ke ke bawah

Contoh penggunaan Rumus Excel untuk Fungsi RoundDown :

= ROUNDDOWN(11.2367,3).

Akan menghasilkan nilai 11,236

= ROUNDDOWN (11.2367,4)

Akan menghasilkan nilai 11.2367.

= ROUNDDOWN (11.2367,0)

Akan menghasilkan nilai 11.

= ROUNDDOWN (11.2367,-1)

Akan menghasilkan nilai 10.

Pembulatan dengan digit yang lebih detail dengan fungsi FLOOR dan CEILING

Fungsi Floor dan Ceiling dapat melakukan pembulatan yang lebih detail dibandingkan dengan fungsi sebelumnya yang sudah kita bahas di atas yaitu ROUND, ROUNDDOWN, ROUNDUP.

Jika sebagai contoh fungsi ROUNDOWN akan melakukan pembulatan angka :

= ROUNDDOWN(11.2367,3) yang akan menghasilkan nilai 11,236 maka fungsi FLOOR dapat melakukan pembulatan ini secara lebih detail sesuai kelipatan yang diinginkan menjadi = FLOOR(11.2367,0.0005) menghasilkan 11.2365 tergantung kelipatan yang diinginkan.

Fungsi Floor ini digunakan untuk melakukan pembulatan ke bawah sedangkan fungsi Ceiling sebaliknya akan melakukan pembulatan ke atas.

Fungi umum FLOOR dapat dituliskan dengan Rumus :

FLOOR(angka,nilai_kelipatan)

Contoh penggunaan Rumus Excel untuk Fungsi FLOOR :

= FLOOR(11.2367,0.0005).

Akan menghasilkan nilai 11,2365

= FLOOR(11.2367,0.1)

Akan menghasilkan nilai 11.2

= FLOOR(-11.2367,-0.1)

Akan menghasilkan nilai –11.2

Fungi umum Ceiling dapat dituliskan dengan Rumus :

CEILING(angka,nilai_kelipatan)

Contoh penggunaan Rumus Excel untuk Fungsi CEILING :

= CEILING(11.2367,0.0005).

Akan menghasilkan nilai 11,237

= CEILING(11.2367,0.1)

Akan menghasilkan nilai 11.3

= CEILING (-11.2367,-0.1)

Akan menghasilkan nilai –11.3

Pembulatan Angka ke Atas Menjadi Genap atau Ganjil

Pada Excel terdapat fungsi untuk membulatkan angka ke atas menjadi genap atau ganjil. Fungsi yang dapat digunakan untuk melakukan pembulatan ini adalah fungsi EVEN dan fungsi ODD.

ODD akan membulatkan angka ke atas menjadi angka integer ganjil. Sedangkan EVEN akan membulatkan angka ke atas menjadi angka integer genap.

Penulisan umum Rumus Excel nya adalah :

ODD(angka) ;

EVEN(angka);

Dimana angka merupakan bilangan yang akan dibulatkan.

Contoh penggunaan fungsi ODD dan EVEN :

=EVEN(7)

Akan menghasilkan angka 8.

=EVEN(6)

Akan menghasilkan angka 6.

=EVEN(4.02)

Akan menghasilkan angka 8.

=EVEN(-5.5)

Akan menghasilkan angka -6.

=ODD(5)

Akan menghasilkan angka 5.

=ODD(6)

Akan menghasilkan angka 7.

=ODD(3.02)

Akan menghasilkan angka 5.

=ODD(-5.5)

Akan menghasilkan angka 7.

Penggunaan Fungsi dan Rumus Excel EVEN sangat membantu dalam melakukan pembagian dengan bilangan 2. Sebagai contoh Anda sedang menjadi panitia perjalanan untuk wisata sekolah. Anda memerlukan informasi mengenai berapa banyak bangku yang diperlukan untuk perjalanan wisata ini untuk setiap kelas di sekolah. Setiap bangku di bis dapat memuat 2 orang anak. Jika setiap kelas pada sekolah tersebut mempunyai anggota sebanyak 21 anak (bilangan ganjil) Anda tetap dapat menghitung berapa tempat duduk yang diperlukan meskipun tempat duduk terakhir hanya diisi oleh 1 orang anak.

Katakanlah jumlah anak dalam satu kelas ada 21 orang. Anda dapat menggunakan Rumus EVEN(21)/2 untuk mendapatkan jumlah bangku yang diperlukan, di mana hasilnya adalah 11.

Menghilangkan Angka Desimal dengan Fungsi INT dan Fungsi TRUNC

Fungsi INT akan membulatkan angka yang memiliki nilai desimal menjadi nilai Integer kebawah terdekat dengan menghilangkan desimal (angka di belakang koma). Sedangkan fungsi TRUNC berguna untuk menghilangkan atau pembulatan sejumlah angka yang dinyatakan di dalam perumusan. Penulisan Rumus umum INT dan TRUNC adalah :

INT(angka);

TRUNC(angka,jumlah_digit);

Contoh penggunaan Rumus Excel fungsi INT dan TRUNC :

=INT(65.25)

Akan menghasilkan angka 25.

=INT(65.75)

Akan menghasilkan angka 65.

=INT(‐65.25)

Akan menghasilkan angka 65.

=INT(‐65.75)

Akan menghasilkan angka 65.

=TRUNC(412.65, 2)

Akan menghasilkan angka 412.65.

=TRUNC(412.65, 1)

Akan menghasilkan angka 412.6.

=TRUNC(412.65, 0)

Akan menghasilkan angka 412.

=TRUNC(412.65, ‐1)

Akan menghasilkan angka 410.

=TRUNC(412.65, ‐2)

Akan menghasilkan angka 400.

 

NILAI NEGATIF DAN POSITIF BILANGAN

 

Mencari tanda positif atau negative dalam hasil suatu perhitungan bilangan dengan Fungsi SIGN

Jika Anda memerlukan informasi apakah hasil dari suatu perhitungan positif atau negative Anda dapat menggunakan fungsi SIGN. SIGN akan menghasilkan hanya 3 jenis bilangan, yaitu :

1 jika hasil suatu perhitungan positif, -1 jika hasil suatu perhitungan negative, dan 0 jika suatu perhitungan menghasilkan nilai nol.

Fungsi SIGN dirumuskan dengan :
SIGN(angka);

Contoh penggunaan SIGN :

=SIGN(6) menghasilkan 1.

= SIGN (‐6) menghasilkan –1.

= SIGN (0) menghasilkan 0.

= SIGN (5-10) menghasilkan -1

= SIGN (2+2) menghasilkan 1.

= SIGN (5-5) menghasilkan 0.

Sebagai contoh Fungsi SIGN ini dapat digunakan jika dalam suatu transaksi bisnis Anda mengalami kerugian atau keuntungan. Fungsi SIGN juga dapat dikombinasikan dengan fungsi lainnya dalam suatu perumusan Excel.

Mengabaikan Tanda Negatif dalam Perihitungan dengan fungsi ABS (ABSOLUTE)

Jika Anda ingin mengabaikan tanda negative dalam suatu perhitungan Anda dapat menggunakan fungsi ABS. Fungsi ABS akan membuat semua hasil perhitungan atau angka menjadi positif.

Fungsi Abolute dirumuskan dengan :
ABS(angka);

Contoh penggunaan ABS:

=ABS(6) menghasilkan 6.

= ABS (‐6) menghasilkan 6.

= ABS (0) menghasilkan 0.

= ABS (5-10) menghasilkan 5

= ABS (2+2) menghasilkan 4.

= ABS (5-5) menghasilkan 0.

Fungsi ABS ini dapat digunakan dalam perhitungan cashflow atau jika Anda ingin melakukan perhitungan di mana Anda tidak mengetahui hasil nya positif atau negatif namun Anda menginginkan Angka nya positif.

Demikianlah penggunaan fungsi excel untuk perhitungan, semoga rumus excel prhitungan yang disajikan secara cukup lengkap di atas dapat bermanfaat.

 
Share

Leave a Reply

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