PENGERTIAN FORMULA PADA MICROSOFT EXCEL

Formula dalam Excel atau biasa juga di sebut rumus merupakan fitur yang di gunakan untuk melakukan perhitungan dalam suatu sel . Suatu formula harus di awali dengan tanda sama dengan ( = )

Susunan sebuah formula dapat terdiri maksimal 1024 karakter ( Excel 2003 ) atau 8000 karakter ( Excel 2007 ) termasuk tanda sama dengan ( = ), nama fungsi, tanda kurung, argumen ataupun pemisah argumen dan operator - operatornya. Apabila Excel tidak menerima formula yang anda ketikkan, maka excel akan menampilkan nilai error.

Secara garis besar Formula pada aplikasi Excel adalah suatu persamaan matematika untuk menghitung nilai-nilai tertentu dengan tujuan untuk mendapatkan hasil yang diharapkan. Penulisan formula selalu diawali tanda sama dengan dan umumnya melibatkan operator dasar matematika. 

MACAM MACAM FORMULA PADA MICROSOFT  EXCEL

Ø  =SUM

Ø  =AVERAGE

Ø  =IF

Ø  =COUNT

Ø  =MAX

Ø  =MIN

Ø  =VLOOKUP

Ø  =HLOOKUP

FUNGSI DARI FORMULA MICROSOFT EXCEL

1. SUM
 

Fungsi yang pertama dan bisa dibilang merupakan salah satu fungsi yang paling banyak diketahui dan digunakan setiap pengguna aplikasi Excel adalah fungsi SUM. Fungsi SUM digunakan untuk menjumlahkan data dari beberapa sel.

=SUM(sel awal:sel akhir)

Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan penulisan fungsi SUM juga bisa dituliskan sebagai berikut:

=SUM(range)

Contoh : tabel ini akan dihitung pada kolom Total terletak pada sel f8, yaitu data ada pada sel C8 hingga E8

 Cara penulisan fungsi SUM :

·         Pilih sel f8

·         Ketikan =SUM(

·         Klik sel C8 lalu drag sampai sel E8.

·         Lalu tutup dengan tanda )

·         Kemudian tekan Enter

Hasil penjumlahan akan seperti berikut :

2. AVERAGE

Fungsi AVERAGE digunakan untuk menghitung nilai rata-rata dari sejumlah data pada beberapa sel. Aturan penulisannya sebagai berikut:

=AVERAGE(sel awal:sel akhir)

Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan penulisan fungsi AVERAGE juga bisa dituliskan sebagai berikut:

=AVERAGE(range)

Sebagai contoh, pada tabel berikut ini akan dihitung gaji rata-rata pada sel C17, yaitu data pada sel F8 hingga F14 .

Formula yang digunakan adalah :
C17 =AVERAGE(F8:F14)

Dan hasil penjumlahan nilai rata rata akan seperti berikut:

3.  IF

Dari semua fungsi logika yang terdapat dalam aplikasi Excel, fungsi IF adalah fungsi logika yang paling banyak diaplikasikan, terutama dalam penerapan formula lanjutan yang melibatkan banyak data dan keputusan bercabang. Kegunaan dari fungsi IF ini sebetulnya sangat sederhana, fungsi ini akan mengambil suatu kondisi tertentu kemudian menentukan nilai TRUE atau FALSE. Nilai TRUE adalah nilai dimana kondisi tersebut terpenuhi dan nilai FALSE adalah nilai untuk kondisi yang tidak terpenuhi.

Aturan penulisan fungsi IF ini adalah sebagai berikut:

=IF(kondisi;nilai_TRUE;nilai_FALSE)

• kondisi merupakan kriteria acuan yang dijadikan pembanding
• nilai_TRUE merupakan nilai untuk kondisi yang terpenuhi
• nilai_FALSE merupakan nilai untuk kondisi yang tidak terpenuhi.

Agar lebih jelas liat contoh berikut : 

Pada tabel tersebut kolom Keterangan akan diisi dengan kata LULUS atau TIDAK LULUS. Namun untuk mengisinya ada kondisi yang harus dipenuhi, misalkan saja siswa akan lulus jika nilainya diatas 65. Dari pernyataan ini maka didapat beberapa point, yaitu:

• Kondisi: Nilai diatas 65. Nilai ini berada pada sel C3 hingga C7. Dan karena ada nilai acuan yaitu 65 sebagai pembanding maka Anda harus menggunakan Operator Pembanding untuk melengkapi argumen pada kondisi ini. Operator pembanding yang digunakan tentunya tanda lebih besar ">".
• Kondisi 1: Jika nilai diatas 65, maka siswa Lulus. Kondisi ini merupakan nilai untuk kondisi TRUE yaitu kondisi yang terpenuhi
• Kondisi 2: Jika nilai dibawah 65, artinya tidak diatas 65, maka siswa Tidak Lulus. Kondisi ini adalah nilai untuk kondisi FALSE yaitu kondisi yang tidak terpenuhi.

Berikutnya Anda tinggal menerapkan point-point tersebut pada fungsi IF disesuaikan dengan aturan penulisannya. Dan karena data tiap-tiap siswa bervariasi, maka tabel akan diisi untuk siswa yang pertama dulu yaitu Agus Kuncoro. Formulanya sebagai berikut:

D3=IF(C3>65;"LULUS";"TIDAK LULUS")

Arti dari formula tersebut jika diterjemahkan dalam kalimat biasa adalah "Jika nilai dalam sel C3 lebih besar dari 65, maka tuliskan kata LULUS pada sel D3. Namun jika nilainya ternyata tidak lebih besar dari 65 maka tuliskan kata TIDAK LULUS pada sel D3".

Untuk memeriksa apakah formula yang dituliskan sudah benar, cobalah untuk mengganti nilai pada sel C3, misalnya menjadi 50, lalu perhatikan hasilnya. Jika tertulis TIDAK LULUS maka artinya formula yang digunakan sudah benar.

Lalu bagaimana mengisikan baris-baris selanjutnya? Menuliskan formula secara manual satu persatu jelas bisa, namun hal ini akan sangat menyita waktu. Cara cepatnya, gunakan saja fitur Auto Fill.

Dan hasilnya semua kolom Keterangan kini telah terisi dengan nilai yang benar

4. COUNT

Banyak pengguna aplikasi Excel yang menganggap fungsi COUNT ini sama dengan fungsi SUM, padahal kegunaannya jelas berbeda. Jika fungsi SUM digunakan untuk menghitung jumlah data maka fungsi COUNT digunakan untukmenghitung banyaknya pemilik data. Oleh karenanya berhati-hatilah saat Anda harus memutuskan fungsi mana yang akan Anda gunakan, apakah SUM atau COUNT.

Aturan penulisan fungsi COUNT sebagai berikut:

=COUNT(sel awal:sel akhir)

Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan penulisan fungsi COUNT juga bisa dituliskan sebagai berikut:

=COUNT(range)

Agar lebih jelas dalam memahami fungsi COUNT ini juga agar tidak tertukar penggunaannya dengan fungsi SUM, perhatikan contoh berikut ini.

Maka formula yang digunakan adalah:

E17=COUNT(F8:F14)

Dan hasilnya akan ditampilkan pada sel E17 seperti tampak pada gambar berikut ini.

5. MAX

Fungsi MAX digunakan untuk mengetahui nilai maksimum dari sejumlah data .

Aturan penulisan fungsi MAX

=MAX(sel awal:sel akhir)

Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan penulisan fungsi MAX juga bisa dituliskan sebagai berikut:

=MAX(range)

Sebagai contoh, pada tabel berikut ini ditanyakan berapa GAJI TERBESAR (nilai maksimum) yaitu data nilai pada sel F8 hingga F14.

Dan hasil penghitungan terlihat pada sel C18

6. MIN

fungsi MIN untuk mengetahui nilai minimum juga dari sejumlah data.

Aturan penulisan fungsi MIN:

=MIN(sel awal:sel akhir)

Alamat sel awal hingga sel akhir merupakan sebuah range, dengan demikian aturan penulisan fungsi MIN bisa dituliskan sebagai berikut:

=MIN(range)

Sebagai contoh, pada tabel berikut ini ditanyakan berapa GAJI TERKECIL (nilai minimum) yaitu data nilai pada sel F8 hingga F14.

Formula yang digunakan adalah: =MIN(F8:F14)

Dan hasil penghitungan GAJI TERKECIL nampak pada gambar berikut

7. VLOOKUP

VLOOKUP termasuk kedalam salah satu fungsi yang paling banyak digunakan dalam aplikasi Excel. Sesuai dengan nama dasarnya yaitu lookup yang bisa diartikan melihat atau mencari, maka fungsi ini akan menghasilkan suatu formula untuk mengisi data pada tabel berdasarkan data pada tabel lainnya atau tabel referensi tertentu dengan menggunakan suatu nilai kunci yang spesifik. Awalan huruf V didepan kata lookup merupakan singkatan dari kata Vertical. Istilah vertical ini merujuk pada bentuk tabel referensi yang digunakan, dimana judul kolomnya terletak dibagian atas dan data-datanya tersusun kebawah secara vertikal. 

Contoh sebuah tabel vertikal ini bisa dilihat pada gambar berikut ini.

Aturan penulisan VLOOKUP mungkin bisa dibilang agak rumit bagi yang baru saja mempelajari penggunaan formula pada Excel. Namun jika sudah terbiasa, aturan ini sebetulnya bisa dibilang sederhana, yaitu:

=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)

Penjelasan aturan penulisan tersebut sebagai berikut:

• nilai_kunci: Adalah nilai yang dijadikan acuan untuk membaca tabel referensi. Nilai ini harus ada baik pada tabel yang akan diisi maupun pada tabel referensi.
• range_tabel_referensi: Adalah range dari tabel yang berisikan data referensi untuk mengisi hasil yang diharapkan. Pastikan bahwa range yang Anda pilih tidak menyertakan judul kolomnya.
• no_index-kolom: Adalah nomor urut data dalam tabel referensi yang akan dituliskan hasilnya. Dimulai dari kolom paling kiri pada tabel referensi tersebut dengan nomor index 1, dan seterusnya.
• tipe_data: Ada 2 jenis tipe data yaitu TRUE dan FALSE. Nilai TRUE Anda gunakan jika nilai datanya tidak pastiatau berada pada range tertentu dan nilai FALSE Anda gunakan jika nilai data berharga pasti.

Agar lebih jelas bagaimana cara menggunakan fungsi VLOOKUP ini, lihatlah contoh tabel berikut.

Pada contoh tersebut terdapat 2 buah tabel. Data pada tabel Laporan Penjualan jelas akan selalu bertambah tiap harinya. Setiap kali ada pembeli maka Anda tinggal mengisikan data tanggal, nama pembeli serta kode voucher-nya. Sementara data Voucher serta Harga akan terisi secara otomatis tiap kali Anda mengisikan data pada kolom Kode. Pengisian data secara otomatis ini bisa dicapai dengan melihat tabel referensi yaitu tabel Stok Gudang dan tentunya dengan menggunakan formula VLOOKUP.

Langkah pertama akan diisi dahulu data pada tabel Voucher yaitu data pada sel E12. Untuk mempermudah pembahasan, aturan penulisan VLOOKUP kembali dituliskan sebagai berikut:

E12=VLOOKUP(nilai_kunci;range_tabel_referensi;no_index_kolom;tipe_data)

nilai_kunci yang digunakan adalah data pada kolom Kode dalam tabel Laporan Penjualan, yaitu sel D12. Alasannya karena Voucher akan bisa terisi dengan bersandar pada data dalam kolom Kode tersebut.

Dengan demikian formulanya menjadi:

E12=VLOOKUP(D12;range_tabel_referensi;no_index_kolom;tipe_data)

Kemudian range_tabel_referensi jelas adalah range data pada tabel Stok Gudang yaitu B4:D7.

Dengan demikian formulanya menjadi:

E12=VLOOKUP(D12;B4:D7;no_index_kolom;tipe_data)

no_index_kolom Anda tentukan dengan melihat didalam range_tabel_referensi. Dalam hal ini Anda akan mengisi data pada kolom Voucher maka Anda lihat data untuk Voucher tersebut didalam range_tabel_referensi berada pada kolom keberapa?

Dari gambar diatas dapat dilihat bahwa data yang akan diambil berada pada kolom ke-2, maka no_index_kolom yang digunakan adalah 2. Dengan demikian formulanya menjadi:

E12=VLOOKUP(D12;B4:D7;2;tipe_data)

Yang terakhir adalah tipe_data. Disini yang akan digunakan adalah FALSE. Penjelasannya adalah karena data tersebut bersifat pasti, misalnya kode S sudah pasti untuk Simpati, kode E sudah pasti untuk Esia, dan demikian seterusnya. Dengan demikian formula lengkap untuk mengisi data pada kolom Voucher tersebut adalah:

E12=VLOOKUP(D12;B4:D7;2;FALSE)

Untuk mengisi data pada sel selanjutnya yaitu sel E13 dan E14 seperti biasanya Anda dapat menggunakan Auto Fill. Namun perhatikan bahwa tabel referensi yang menjadi acuan memiliki range alamat sel yang statis alias tetap. Oleh karenanya, sebelum Anda menjalankan proses Auto Fill maka Anda harus mengunci terlebih dahulu range tabel referensi tersebut agar menjadi sel yang absolut. Hingga formulanya akan menjadi:

E12=VLOOKUP(D12;$B$4:$D$7;2;FALSE) 

Dan proses Auto Fill kini dapat dijalankan untuk mengisi data pada sel-sel berikutnya.

Berikutnya, bagaimana mengisi data pada kolom Harga yaitu sel F12 hingga F14? Formula yang digunakan tentunya tetap sama hanya saja no_index_kolom yang berbeda yaitu 3 karena data yang akan diisikan diambil dari kolom ke-3 pada tabel referensi.

Dan berikut ini formula yang digunakan setelah menggunakan sel absolut untuk range tabel referensinya.

F12=VLOOKUP(D12;$B$4:$D$7;3;FALSE)

8. HLOOKUP

HLOOKUP adalah varian lain dari fungsi VLOOKUP yang telah dibahas pada artikel sebelumnya. Kegunaannya juga sama yaitu untuk mengisi data pada tabel berdasarkan data pada tabel lainnya atau tabel referensi tertentu dengan menggunakan suatu nilai kunci yang spesifik. Perbedaannya dengan VLOOKUP ditunjukan oleh awalan huruf H yang berarti Horizontal, artinya tabel referensi yang digunakan berbentuk horisontal, dimana judul kolomnya terletak dibagian kiri dan data-datanya tersusun kekanan dalam arah horisontal.

Contoh sebuah tabel horisontal bisa dilihat pada gambar berikut ini.

Aturan penulisan HLOOKUP juga sama dengan VLOOKUP, namun ada sedikit perbedaan yaitu pada no index-nya. Jika pada VLOOKUP no index mengacu kepada kolom (no_index_kolom) maka pada HLOOKUP mengacu pada baris (no_index_baris), hal ini dikarenakan datanya yang memang tersusun dalam suatu baris.

Berikut ini aturan penulisannya:

=HLOOKUP(nilai_kunci;range_tabel_referensi;no_index_baris;tipe_data)

Penjelasan aturan penulisan tersebut sebagai berikut:

• nilai_kunci: Adalah nilai yang dijadikan acuan untuk membaca tabel referensi. Nilai ini harus ada baik pada tabel yang akan diisi maupun pada tabel referensi.
• range_tabel_referensi: Adalah range dari tabel yang berisikan data referensi untuk mengisi hasil yang diharapkan. Pastikan bahwa range yang Anda pilih tidak menyertakan judul barisnya.
• no_index_baris: Adalah nomor urut data dalam tabel referensi yang akan dituliskan hasilnya. Dimulai dari baris paling atas pada tabel referensi tersebut dengan nomor index 1, dan seterusnya.
• tipe_data: Ada 2 jenis tipe data yaitu TRUE dan FALSE. Nilai TRUE Anda gunakan jika nilai datanya tidak pasti atau berada pada range tertentu dan nilai FALSE Anda gunakan jika nilai data berharga pasti.

Sebagai contoh untuk penggunaan HLOOKUP ini, perhatikan tabel berikut.

Pada sel C8 dalam tabel Cek Stok, digunakan formula HLOOKUP agar jika Anda ketikan nama Produk pada sel B8maka otomatis ditampilkan jumlah Stok yang tersedia dengan mengacu pada tabel referensi Stok Gudang. 

Dengan demikian maka formula yang digunakan adalah sebagai berikut.

C8=HLOOKUP(B8;C3:E4;2;FALSE)

Penjelasan dari formula tersebut adalah:

• B8 adalah nilai_kunci yang digunakan karena jumlah Stok akan ditampilkan jika nama Produk dalam sel B8tersebut diisi.
• C3:E4 adalah range_tabel_referensi yang digunakan sebagai acuan pengisian jumlah Stok pada tabel Cek Stok.
• no_index_baris adalah 2 karena yang akan dituliskan pada tabel Cek Stok diambil dari baris kedua padarange_tabel_referensi.
• Tipe data yang digunakan adalah FALSE karena datanya bersifat pasti.

Semoga bermanfaat :)