Rumus Fungsi Microsoft Excel

Materi Excel I
Fungsi Matematika Dan Statistik Sederhana
 

   Seperti yang sudah dijelaskan , Excel dapat digunakan untuk mengolah data, karena excel memiiki fasilitas untuk mengerjakan fungsi-fungsi perhitungan dan logika.

Beberapa fungsi matematika dan statistika sederhana yang sering digunakan antara lain : penjumlahan, pengurangan, pembagian, perkalian, mencari nilai tertinggi, mencari nilai terrendah dan mencari rata-rata.

OPERASI ARITMATIKA	OPERASI KOMPUTER	FUNGSI
+ - : X Yn >  <  ≥ ≤ = ≠	+ - / * ^ >  <  >= <= <>	Penjumlahan Pengurangan Pembagian Perkalian Pangkat Lebih besar Lebih kecil Lebih besar sama dengan Lebih kecil sama dengan Sama dengan Tidak sama dengan

Untuk memasukan rumus operasi komputer harus diawali tanda sama dengan ( = )

1.1          PENJUMLAHAN

Contoh soal :

Rumus :

1.                   =angka+angka

Penyelesaian :

Ø    Pada sel D2, ketik rumus:=10+8 à Enter

2.                   =alamat sel+alamat sel

Penyelesaian :

Ø    Pada sel D2, ketik rumus :=B2+C2 à Enter

3.                   =SUM(alamat sel awal:alamat sel akhir)

Penyelesaian :

Ø    Pada sel D2, ketik rumus :=sum(b2:c2) à Enter

o   Untuk mengisi sel D3 dan D4 : klik sel D2 à arahkan pointer mouse ke fill handle sel D2 à kliik dan tarik ke sel D3 s/d D5.

o   Jadi untuk penyelesaian jumlah pada sel B6, ketik rumus : =10+7+5+4 atau =B2+B3+B4+B5 atau =sum(b2:b5)

o   Untuk mengisis sel C6 dan D6 : klik sel B6 à arahkan pointer mouse kefill handle sel B6 à klikdan tarik ke sel C6 dan D6

1.2          PENGURANGAN

Contoh soal :

Rumus:

1.                   =angka+angka

Penyelesaian :

Ø    Pada sel D2, ketik rumus:=96-6 à Enter

2.                   =alamat sel+alamat sel

Penyelesaian :

Ø    Pada sel D2, ketik rumus :=B2-C2 à Enter

o   Untuk mengisi sel D3 dan D4 : klik sel D2 à arahkan pointer mouse ke fill handle sel D2 à kliik dan tarik ke sel D3 s/d D4.

1.3          PERKALIAN

Contoh soal :

Rumus:

1.                   =angka+angka

Penyelesaian :

Ø    Pada sel D2, ketik rumus:=7*5 à Enter

2.                   =alamat sel+alamat sel

Penyelesaian :

Ø    Pada sel D2, ketik rumus :=B2*C2  à Enter

o   Untuk mengisi sel D3 dan D4 : klik sel D2 à arahkan pointer mouse ke fill handle sel D2 à kliik dan tarik ke sel D3 dan D4.

1.4          PEMBAGIAN

Contoh soaal:

     

Rumus:

1.                   =angka+angka

Penyelesaian :

Ø    Pada sel D2, ketik rumus:

=100/2,9 à Enter

2.                   =alamat sel+alamat sel

Penyelesaian :

Ø    Pada sel D2, ketik rumus :=B2/C2  à Enter

o   Untuk mengisi sel D3, D4 dan D5 : klik sel D2 à arahkan pointer mouse ke fill handle sel D2 à kliik dan tarik ke sel D3 dan D4.

1.5          NILAI TERTINGGI, NILAI TERENDAH DAN RATA-RATA

Contoh soal :

Rumus :

1.                   Tertinggi              à           = MAX(alamat sel awal:alamat sel akhir)

2.                   Terrendah           à            = MIN(alamat sel awal:alamat sel akhir)

3.                   Rata-rata             à            = AVERAGE(alamat sel awal:alamat sel akhir)

Penyelesaian :

Ø    Tertinggi , pada sel B9, ketik rumus :        = max(B3:B8)

Ø    Terendah, pada sel B10, ketik rumus :       = min(B3:B8)

Ø    Rata-rata,pada sel B11,ketik rumus :         = average(B3:B8)

1.6          PANGKAT BILANGAN

Contoh soal :

Penyelesain :

Ø    Pada sel B2, ketik rumus = 3^2   atau       =A2^2

Ø    Pada sel C2, ketik rumus = 3^3   atau       =A2^3

Ø    Pada sel D2, ketik rumus = 3^4   atau       =A2^4

Ø    Untuk mengisi sel B3 sd D7 : blok sel B2 : D2  à arahkan pointer mouse ke fill handle sel D2 à klik dan tarik sampai ke sel D7.

1.7       AKARKUADRAT (SORT)

Contoh soal :

Penyelesaian:

Ø    Pada sel B2, ketik rumus =SQRT(144)      ATAU    =SQRT(A2)

Ø    Untuk mengisi sel B3 sd B9 : blok sel B2 à arahkan pointer mouse ke fill handle sel B2 à klik dan tarik sampai ke sel B9.

1.8                CELL ABSOLUTE

Cell absolute adalah alamat sel tertentu yang dkunciagar alamat sel tersebttidak mengalami perubahan pada saat dicopy ke sel lain.

Contoh soal :

Penyelesaian:                                                                                                                                                          

Ø    Pada sel C2 tulis rumus : =B2*B7. Sel B7 harus diabsolutkan dengan menekan tombol F4 pada keyboard agar pada saat dicopy alamat sel B7 tidak berubah sehingga menjadi :               =B2*$B$7

Ø    Untuk mengisi sel C3 sd C5 : klik sel C2 à arahkan pointer mouse ke fill handle sel C2 àklik dan tarik sampai ke sel C5

1.9                FUNGSI LOGIKA IF ( if function)

Fungsi  IF digunakan untuk mencari nilai yang benar dari suatu syarat tertentu.

Rumus :                =IF(SEL SYARAT;JIKA BENAR;JIKA SALAH)

KETERANGAN :

Jika benar, sama arinya memenuhi syarat yang ditentukan.

Jika salah, sama artinya tidak memenuhi syarat yang ditentukan.

Contoh soal:

Berdasarkan soal diatas setiap karyawan berhak mendapatkan bonus sebesar 1000000 jika target produksi lebih dari 500.

Penjabaran :

Syarat   : target produksi>500 atau B2>500

Benar    : 1000000

Salah     : 0

Penyelesaian :

Ø    Pada sel C2, ketik rumus : =IF(B2>500;1000000;0)

Ø    Untuk mengisi sel C3 s.d C5 : kliik sel C2 à arahkan pointer mouse ke fill handle sel C2 à klik dan tarik ke sel C5

1.10            FUNGSI IF.....OR,IF....AND

Rumus :

=IF(OR(SEL SYARAT1,SEL SYARAT2);JIKA BENAR;JIKA SALAH)

=IF(AND(SEL SYARAT1,SEL SYARAT2,SYARATn);JIKA BENAR;JIKA SALAH)

contoh soal if(OR)dan if(AND) :

Ketentuan1 :

Kolom keterangan diisi dengan ketentuan sbb :

Jika niali IPA atau MAT lebih dari 6à  LULUS

Penyelesaian :

Ø    Pada sel D2,ketik rumus : =IF(OR(B2>6);”LULUS”;”TIDAK”)

Ø    Untuk mengisi sel C3 sd C5 : klik sel C2 à arahkan pointer mouse ke fill handle sel C2 à klik dan tarik ke sel C5.

Ketentuan 2:

Soal sama dengan diatas, kolom keterangan diisi dengan ketentuan sbb :

Jika niali IPA atau MAT lebih dari 6 à  LULUS

Penyelesaian :

Ø    Pada sel D2,ketik rumus : =IF(AND(B2>6);”LULUS”;”TIDAK”)

Ø    Untuk mengisi sel C3 sd C5 : klik sel C2 à arahkan pointer mouse ke fill handle sel C2 à klik dan tarik ke sel C5.

Untuk memasukan kondisi benar atau salah yang berupa teks harus diawali dan diakhiri tanda petik dua ( “ )

1.11            VLOOKUP dan HLOOKUP

FUNGSI VLOOKUP DAN FUNGSI HLOOKUP digunakan untuk mengisi data berdasarkan data lain yang dijadikan acuan. Pada dasarnya kedua fungsi tersebut sama, yang membedakan keduanya adalah jenis data acuannya.

§  Vlookup data acuan berbentuk vertikal

§  Hlookup data acuan berbentuk horizontal

Untuk menggunakan fasilitas tersebut ada 2 tabel yang harus dinuat, antara lain :

1.  Tabel utama ; artinya tabel yang berisi kolom-kolom kosong yang akan diisi

2.         Tabel acuan ; artinya tabel yang berisi data lengkap sebagai acuan pengisian pada kolom tabel utama. Tabel acuan ini bisa dibuat dalam satu sheet atau terpisah dengan tabel utama.

Rumus :

=VLOOKUP(SEL UTAMA; TABEL ACUAN; NO KOLOM; TRUE or FALSE)

=HLOOKUP(SEL UTAMA; TABEL ACUAN; NO BARIS; TRUE or FALSE)

Keterangan :

Sel utama                            : sel yang berada ditabel utama dan biasanya berisi kode   
                                             dan berhhubungan dengan tabel acuan (misal kode 
                                             barang, kode jabatan, dll)

Tabel acuan                        : range / pengeblokan seluruh data pada tabel acuan à 
                                              kemudian tekan F4

               

(cat : selain rumus dimasukan , blok seluruh tabel acuan  pada name blok diklik, lalu ketik nama untuk tabel acuan , miasal : Gaji à Enter)

No kolom / baris               : no urut kolom / baris pada tabel acuan yang sama dengan 
                                            kolom tabel utama yang sedang diisi.

True                                   : apabila data acuan dibuat urut

False                                  : apabila data acuan dibuat tidak urut

Contoh soal VLOOKUP :

Diminta : lengkapilah tabel utama dengan menggunakan data pada tabel acuan.

Penyelesaian :

Nama Barang :

Pada sel B4, tulis rumus : =VLOOKUP(A4;$A$15;$C$21;2;FALSE)

A4                           = sel yang berisi kode barang

A15:c21                  = range tabel acuan  (dibuat dengan cara diblok) kemudian 
                                   dkunci / diabsolutkan dengan menekan F4

2                              = nomor urut kolom HARGA SATUAN pada tabel acuan

FALSE                    = karena range tabel acuan tidak urut

Harga Satuan :

Pada sel C4, tulis rumus : =VLOOKUP(A4;$A$15;$C$21;3;FALSE)

A4                           = sel yang berisi kode barang

A15:c21                  = range tabel acuan  (dibuat dengan cara diblok) kemudian   
                                   dikunci / diabsolutkan dengan menekan F4

3                              = nomor urut kolom HARGA SATUAN pada tabel acuan

FALSE                    = karena range tabel acuan tidak urut

Harga Total

Pada sel E4, tulis rumus : =C4*D4

Contoh soal HLOOKUP:

Penyelesaian :

Bagian :

Pada sel C4, tulis rumus : =HLOOKUP(B4;$A$14;$E$17;2;FALSE)

B4                           = sel kunci yang berisi kode karyawan

E17                         = range tabel acuan  (dibuat dengan cara diblok) kemudian  
                                  dkunci / diabsolutkan dengan menekan F4

2                              = nomor urut kolom BAGIAN pada tabel acuan

FALSE                    = karena range tabel acuan tidak urut

Gaji Pokok :

Pada sel C4, tulis rumus : =HLOOKUP(B4;$A$14;$E$17;3;FALSE)

A4                           = sel KUNCI yang berisi kode KARYAWAN

A14:E17                 = range tabel acuan  (dibuat dengan cara diblok) kemudian 
                                  dkunci/ diabsolutkan dengan menekan F4

3                              = nomor urut kolom GAJI POKOK pada tabel acuan

FALSE                    = karena range tabel acuan tidak urut

Fasilitas :

Pada sel C4, tulis rumus : =HLOOKUP(B4;$A$14;$E$17;4;FALSE)

A4                           = sel KUNCI  yang berisi kode KARYAWAN

A14:E17                 = range tabel acuan  (dibuat dengan cara diblok) kemudian         
                                  dikunci / diabsolutkan dengan menekan F4

4                              = nomor urut kolom HARGA SATUAN pada tabel acuan

FALSE                    = karena range tabel acuan tidak urut

1.12            CONCATENATE, LEFT, RIGHT, dan MID

CONCATENATE, LEFT, RIGHT, dan MID merupakan fungsi-fungsi excell yang berkaitan dengan teks.

ü  FUNGSI CONCATENATE

Digunakan bila kita akan menggabungkan isi dari beberapa sel ke dalam satu sel.

Rumus : =CONCATENATE(SEL 1;SEL 2;SEL N)

CATATAN : bila akan memasukan spasi atau karakter teks lainnya ke dalam hasil penggabungan , maka sebelum dan sesudah spasi diberi tanda petik.

                  =CONCATENATE(SEL 1;” ”;SEL 2; “ ”;SEL N)

Contoh :

PENYELESAIAN :

Untuk mengisi sel C2 tulis rumus sbb:

=concatenate(a2;b2) à jadi sel c2 akan teriisi gula pasir

Agar disel c2 terisi gula pasir( ada spasinya ),maka dalam rumus juga harus diberi tanda spasi, sehingga sel c2 tulis rumus sbb :

=concatenate(a2;” “;b2 )  à  jadi sel c2 akan terisis gula pasir

Contoh soal untuk left, right, mid

ü  FUNGSI LEFT

Digunakan bila kita ingin mengambil isi suatu sel dari arah sebelah kiri sejumlah karakter tertentu.

Rumus : =LEFT(ALAMAT SEL;N)

KETERANGAN : 
alamat sel    : alamat sel yang akan diambil isisnya

N                 : jumlah karakter yang akan diambil

Penyelesaian sel  B2:

=LEFT(A2;3)       
 artinya mengambil dari sebelah kiri pada sel A2 sebanyak 3 karakter , sehingga sel 
B2 akan terisis JAK

ü  FUNGSI RIGHT

Digunakan bila kita ingin mengambil suatu sel dari arah sebelah kanan sejumlah karakter tertentu.

Rumus : =RIGHT(ALAMAT SEL;N)

KETERANGAN : alamat sel            : alamat sel yang akan diambil isisnya

N                                                        : jumlah karakter yang akan diambil

Penyelesaian sel  C2:

=LEFT(A2;3)         
artinya mengambil dari sebelah kiri pada sel A2 sebanyak 4 karakter , sehingga sel 
C2 akan terisis ARTA

ü  FUNGSI MID

Digunakan bila kita ingin mengambil isi suatu sel dari bagian tengah,  sejumlah karakter tertentu.

Rumus : =MID(ALAMAT SEL;N)

KETERANGAN : 
alamat sel                            : alamat sel yang akan diambil isisnya

Keterangan awal                 : awal pengambilan paling tidak karakter ke-2

N                                         : jumlah karakter yang akan  diambil

Penyelesaian sel  D2:

=MID(A2;3;3)      
artinya mengambil dari sebelah kiri pada sel A2 sebanyak 3 karakter ,3 KARAKTER
sehingga sel B2 akan terisis KAR