Enjang.com

Situs Informasi dan Teknologi

Menu

Mengenal dan memahami Formula pada excel

5 Juni 2014 | Office

Mengenal dan memahami Formula pada excel. Mengingat pentingnya formula pada excel, kali ini saya akan melanjutkan menulis untuk memahami formula dan fungsi pada microsoft excel 2007. Tujuan saya agar pembaca lebih paham, dengan begitu akan lebih mudah untuk menggunakannya.

Jenis Data pada Excel

Sebuah sel pada lembar kerja Excel dapat diisi data hingga maksimum 32000 karakter. Secara garis besar, sel-sel Microsoft Excel dapat diisi dua kelompok data, yakni data berupa konstanta (constant) dan rumus (formula).

Jenis Konstanta

Data konstanta adalah data riil yang diketikkan pada suatu sel. Data ini terdiri atas tiga macam, yaitu:

  1. Nilai numerik atau bilangan yang bernilai. Ciri utama dari konstanta ini ialah dapat dihitung.
  2. Nilai teks atau label, disebut juga nilai string. Terdiri atas teks alfabet, alfa numerik, maupun karakter-karakter khusus. Yang disebut alfabet adalah huruf A s.d. Z, baik yang tersusun dalam bentuk kata maupun individu. Alfa numerik adalah bilangan yang tidak memiliki nilai, seperti nomor, kode, dan sebagainya. Sedangkan karakter khusus adalah semua karakter ASCII maupun ANSI selain huruf dan angka, seperti ~ ! @ # dan sebagainya.
  3. Nilai tanggal dan waktu. Konstanta jenis ini dapat dihitung dan memiliki bentuk atau format penulisan bermacammacam.

Selain ketiga jenis tersebut, Excel juga mengenal dua jenis konstanta khusus, yaitu:

  1. Nilai Logika (Logical Value). Nilai ini memiliki dua kemungkinan, yaitu benar dan salah (TRUE atau FALSE). Dengan nilai logika ini, Excel akan dapat menerima perintah dengan syarat.
  2. Nilai Kesalahan (Error Value). Nilai ini akan tampil sebagai reaksi kesalahan dari suatu perintah perhitungan.

Mengenal Formula

Formula atau dalam bahasa Indonesia rumus adalah alat yang memungkinkan Anda untuk melakukan kalkulasi terhadap nilai nilai pada sel itu sendiri (sel yang ditempati oleh formula itu) maupun nilai yang tersimpan pada sel-sel yang lain.

Bagaimana cara kerja formula. Sebuah formula dapat membantu Anda menganalisa data pada worksheet. Anda dapat melakukan perhitungan seperti penambahan, perkalian, dan bahkan pembandingan nilai-nilai yang terdapat pada worksheet. Anda dapat menggunakan formula apabila perlu memasukkan nilai kalkulasi pada worksheet Anda. Sebuah formula dapat terdiri atas elemen-elemen berikut ini:

  • operator perhitungan
  • referensi alamat suatu sel
  • nilai dari angka-angka secara langsung
  • fungsi-fungsi Excel
  • dan juga nama sel/nama range

Untuk memasukkan suatu formula pada suatu sel, ketikkan kombinasi dari elemen elemen di atas, dan ketikan itu akan muncul langsung pada sel yang ditempati dan juga pada formula bar. Sebuah formula harus selalu diawali = (sama dengan).

Berikut ini contoh-contoh yang sederhana:

1. Pada sel A I terisi formula =500+50. Ketika Anda menekan Enter, pada sel tersebut akan muncul angka 550, namun pada formula bar menampilkan isi formula itu. Hal ini karena formula yang diketikkan pada sel A1 berarti 500 ditambah 500, maka menghasilkan angka 550.

Mengenal dan memahami formula pada excel

2. Selanjutnya bila pada sel A2 angka 1000, dan sel A3 dituliskan formula =AI+A2, maka ketika Anda menekan Enter, pada sel A3 akan muncul nilai 6500, yang terdiri atas 5500 (nilai sel A1) dan 1000 (nilai sel A2).

Mengenal dan memahami formula pada excel

Memasukkan Formula dengan  Cara Klik  pada Selnya

Selain dengan menuliskan alamat sel ketika Anda menyusun formula secara manual, Anda juga dapat memasukkan alamat sel tersebut dengan cara mengklik sel yang bersangkutan.

Contoh:

Untuk membuat formula =A10+B13+C15 Anda dapat melakukan dengan mengetikkan = kemudian klik sel A10, ketikkan +, klik sel B13, ketikkan + lalu klik sel C15.

Mengenal  Macam-Macam Operator

Yang dimaksud dengan operator adalah tanda perhitungan yang kita berikan sehingga Excel mengetahui bagaimana data akan dihitung atau diperlakukan. Ada empat macam operator, yaitu Operator Aritmetika, Operator Teks “&”, Operator Perbandingan, dan Operator Reference.

1. Operator Aritmetika

Mengenal dan memahami formula pada excel

Urutan dalam Mengerjakan Perhitungan Aritmetika

Apabila dalam suatu formula terdapat beberapa operator aritmetika, maka di dalam memproses perhitungan tersebut, Excel membuat urutan prioritas sebagai berikut:

  1. Perhitungan yang diapit dalam tanda kurung dilakukan paling dulu.
  2. Pada level yang sama, urutan pengerjaan dilakukan menurut operatornya, di mana perkalian dan pembagian dilakukan lebih dulu, baru penambahan dan pengurangan.
  3. Perhitungan dengan level yang sama dilakukan dari sebelah kiri dulu.

2. Operator Teks “&”

Data yang berupa teks tidak dapat menggunakan operator aritmetik karena akan menampilkan pesan error VALUE, artinya operator ini memerlukan nilai untuk dapat diperhitungkan. Khusus untuk teks terdapat operator khusus, yaitu “&” yang berfungsi untuk menggabungkan dua buah atau lebih teks menjadi satu dalam suatu sel. Sebagai contoh lihat pada di bawah di mana terdapat sel-sel dengan isinya sebagai berikut:

  • A2 berisi adi
  • B2 berisi kusrianto
  • C2 berisi penulis buku ini

Dari data-data tersebut dibuat beberapa formula sebagai berikut:

  • =A2+132 menghasilkan nilai kesalahan VALUE
  • =A2&B2 menghasilkan “adikusrianto” tanpa spasi di antaranya
  • =A2&””&B2 menghasilkan “adi kusrianto” dengan spasi antara
  • =C6&””&C2 menghasilkan “adi kusrianto penulis buku ini”

Mengenal dan memahami formula pada excel

Untuk memberikan spasi di antara dua data dipergunakan dua buah tanda kutip dengan satu spasi kosong di antaranya. Apabila dalam penulisan data diperlukan untuk menampilkan simbol “&” maupun teks yang diketikkan langsung pada formula, maka simbol atau teks tersebut harus diapit dengan tanda kutip.

Mengenal dan memahami formula pada excel

3. Operator Perbandingan

Operator perbandingan dipergunakan untuk membandingkan suatu data dengan data lain sehingga Excel akan memberikan hasilnya.

Operator Artinya

  • = sama dengan
  • < lebih kecil dari
  • > lebih besar dari
  • <= lebih kecil sama dengan
  • >= lebih besar sama dengan
  • <> tidak sama dengan

4. Operator Reference

Operator reference digunakan untuk menulis Formula maupun Fungsi.

Mengenal dan memahami formula pada excel

Cara menggunakan referensi sel

Sebuah data dapat berupa referensi. Artinya data tersebut mengacu pada nilai yang terkandung pada sel lain. Sebagai contoh, sel AI berisi angka 100, kemudian pada sel D3 Anda ketikkan entry data =Al, maka ketika Anda menekan Enter akan menghasilkan angka 100, sementara pada formula bar ditampilkan =A I.

Mengenal dan memahami formula pada excel

Seandainya contoh di atas terjadi pada sheet 1, Anda dapat menuliskan referensi sel yang letaknya pada sheet yang berbeda.

Misalnya saat ini Anda berpindah pada sheet 2, kemudian pada D3 ingin diisi dengan data yang terdapat pada sel AI dari sheet 1.

Caranya, pada sel D3 sheet 2 cukup ketikkan = untuk menambatkan posisi sel aktif, kemudian klik tab sheet I sehingga sheet I terbuka. Klik sel AI dan tekan Enter. Hasilnya isi sel AI sheet I tampil pada sel D3 sheet 2.

Mengenal dan memahami formula pada excel

Bukan saja pada sheet yang berbeda pada worksheet yang sama, Anda juga dapat menggunakan referensi pada workbook yang berbeda.

Caranya sama seperti apa yang Anda lakukan pada sheet yang berbeda, hanya bedanya untuk berpindah ke buku kerja lain, buku kerja itu harus sudah terbuka, dan untuk berpindah ke buku kerja itu, Anda dapat menggunakan menu Window dan pilih nama buku kerja itu.

Referensi antarbuku kerja ini disebut “link”, di mana setiap kali Anda membuka file yang mengandung referensi dengan file (buku kerja) lain akan diingatkan, apakah referensi dengan file lain (file sumber) yang saat ini mungkin sedang tidak terbuka perlu diperbarui?

Jika Anda Menyusun Suatu Formula, yakinkan bahwa Anda membuat kurung buka dan kurung tutup yang seimbang (berpasangan). Apabila formula yang Anda tulis mengandung tanda kurung yang tidak seimbang, Excel akan menampilkan pesan yang simpatik seperti diperlihatkan pada Gambar 2-4 berikut ini.

Jika Anda setuju Excel melakukan perbaikan secara otomatis, tekanlah Enter, sedang jika Anda akan memperbaiki sendiri, klik No.

Mengenal dan memahami formula pada excel

Apabila menggunakan fungsi untuk menghasilkan suatu array, yakinkan bahwa Anda telah memilih sel-sel yang cukup untuk menampilkan hasilnya. Pembahasan lebih jelas mengenai array dan formula array akan Anda temukan pada Bab 3 yang khusus membahas formula array.

Mengenal Fungsi (Excel Function)

Fungsi adalah jenis formula khusus siap pakai yang disediakan oleh Excel. Fungsi ini akan menangkap nilai-nilai argumen yang Anda masukkan untuk diproses kemudian menghasilkan suatu hasil perhitungan yang diinginkan.

Fungsi dibuat untuk menyederhanakan perhitungan yang panjang dan kadang-kadang rumit.
Sebagai contoh:

  1. Formula yang dibuat sendiri =A1+A2+A3+A4+A5 dapat ditulis menggunakan fungsi =SUM(AI:A5).
  2. Formula yang dibuat sendiri =A1*A2*A3*A4*A5 dapat dihitung menggunakan fungsi =PRODUCT(A1:A5).

Selain itu, banyak sekali fungsi-fungsi yang tidak dapat diwakili oleh formula buatan sendiri.

Cara Menggunakan Fungsi

Anda dapat menggunakan fungsi dengan memasukkannya ke dalam formula, baik pada lembar kerja secara langsung maupun pada macro sheet. Susunan penulisan fungsi biasa disebut Syntax. Semua fungsi memiliki bentuk penulisan dasar yang sama, di mana aturannya harus Anda ikuti. Apabila cara atau susunan penulisan tersebut keliru, Excel akan menampilkan pesan error.

Berikut ini contoh bentuk penulisan dasar suatu fungsi.

Tanda = (sama dengan) harus diberikan bila fungsi terdapat pada awal formula. Jika fungsi terdapat di tengah formula, tidak perlu dengan awalan =.

Mengenal dan memahami formula pada excel

Aturan Penulisan Fungsi dalam Formula

1. Apabila suatu fungsi terletak di depan suatu formula, maka harus diawali dengan simbol = (sama dengan) sebagai syarat awal penulisan formula.

2. Tanda kurung buka dan kurung tutup (parentheses) untuk memberitahukan Excel di mana argumen dimulai dan diakhiri. Penulisan kurung buka dan kurung tutup tanpa diawali dan diakhiri dengan spasi.

3. Setiap argumen dari suatu fungsi harus ditulis di antara dua tanda kurung. Argumen dapat terdiri atas bilangan, teks, nilai logika, array, maupun referensi dan nama sel/range. Argumen selain berupa unsur-unsur di atas juga dapat berupa hasil suatu fungsi yang lain. Penggunaan fungsi secara berganda ini juga disebut sebagai nested function.

Berikut ini contohnya. Microsoft Excel dapat memuat hingga tujuh buah fungsi  berganda dalam sebuah formula.

Formula pada excel 2007 dan 2003

4. Susunan sebuah formula dapat terdiri atas paling banyak 1024 karakter termasuk tanda =, nama fungsi, tanda kurung, dan seluruh argumen maupun pemisah argumen dan operator-operatornya.

Jika Anda perlu menuliskan karakter lebih dari batasan tersebut, maka pecahlah menjadi beberapa formula yang terletak dalam sel-sel yang berbeda, kemudian jadikan referensi pada formula lain yang Anda buat. Sebuah formula panjang dalam satu sel apabila dijadikan referensi dari formula lain, cukup ditulis dengan menyebut alamat sel di mana formula itu berada.

5. Excel melakukan penulisan nama formula menggunakan huruf kapital. Apabila Anda menggunakan huruf kecil, maka setelah menekan Enter, secara otomatis Excel akan mengubahnya menjadi huruf kapital.

Apabila temyata ketikan huruf kecil itu tidak dikonversi menjadi kapital, artinya Excel tidak menerima fungsi yang Anda ketikkan karena dianggap ada kesalahan ketik. Untuk itu periksa kembali pengetikan nama fungsinya.

6. Jika Excel tidak dapat menerima formula yang Anda ketikkan dan menampilkan nilai kesalahan, maka kemungkinannya adalah: x Ada tanda kurung yang tidak seimbang. x Ada argumen yang tidak tertulis, atau argumen kurang lengkap. x Penulisan argumen tidak sebagaimana yang disyaratkan oleh fungsi, misal data yang seharusnya berupa bilangan, tetapi tertulis sebagai teks dan kesalahan yang sejenis.

Nilai Kesalahan

Nilai kesalahan adalah salah satu bentuk kode yang dipergunakan Excel ketika ia tidak dapat memahami sebuah formula yang Anda masukkan. Tampilnya nilai kesalahan belum tentu disebabkan kesalahan yang terjadi pada sel di mana formula itu ditulis. Kemungkinan ada kaitannya dengan nilai-nilai yang tertulis atau nilai yang hilang dari sel-sel lain yang menjadi referensi dari formula tersebut.

Untuk lebih jelasnya, berikut ini macam-macam nilai kesalahan yang ditampilkan bila ada kesalahan atau sesuatu yang tidak dimengerti oleh Excel.

Mengenal dan memahami Formula pada excel

Fungsi yang Hanya Memiliki Numerik, atau Argumen yang Berupa Alamat Sel Tunggal Excel memiliki fungsi-fungsi yang hanya memerlukan satu nilai numerik maupun satu argumen yang berupa alamat sel tunggal.

Contoh fungsi-fungsi jenis ini adalah:

1. =ABS(F14) menghasilkan nilai absolut dari angka yang terkandung pada sel F14 dengan mengabaikan simbol minus (negatif) bila ada, misalnya 100, menghasilkan angka 100.

2. =U*4T(F15) menghasilkan nilai utuh dan bukan desimal dari angka yang terkandung pada sel F15. Apabila misalnya sel F15 berisi angka 5.77 maka yang ditampilkan adalah angka 5 saja.

3. =CHAR(163) menghasilkan karakter yang terkandung pada kode ASCII yang ke-163, yaitu £ (simbol mata uang poundsterling).

Fungsi yang Memiliki Argumen Berupa

Range atau Kumpulan Sel Fungsi-fungsi yang menggunakan argumen berupa range biasanya untuk menghitung data yang terdapat pada range tersebut dengan berbagai macam perhitungan. Contoh fungsifungsi jenis ini adalah:

  1. =SUM(Bl4:EI4) untuk menjumlahkan data yang terdapat pada seI B14 hingga EI4.
  2. =AVG(Bl4:El4) untuk menghitung nilai rata-rata dari data yang terdapat pada sel B14 hingga E14.
  3. =MAX(B14:E14) untuk menampilkan data dengan nilai paling besar yang terletak pada sel B14 hingga E14.

Fungsi yang Memerlukan Lebih dari Satu  Argumen

Fungsi-fungsi yang memerlukan lebih dari satu argumen, memerlukan operator pemisah argumen. Dengan kondisi seperti ini, fungsi tersebut melakukan perhitungan bertingkat, di mana tiap argumen merupakan langkah-langkah yang berpengaruh pada langkah berikutnya.

Contoh fungsi-fungsi jenis ini di antaranya adalah:

  1. =ROUND(AIO,2) fungsi ini untuk membulatkan penulisan angka pecahan (desimal) di mana argumen pertama menyebut sel berisi angka yang akan dibulatkan, argumen kedua menerangkan bagaimana pembulatan itu dilakukan.
  2. =IF(C3>6,”LuIus”, ITidak Lulus”) fungsi ini memiliki tiga buah argumen, di mana argumen pertama berisi syarat, argumen kedua proses yang harus dilakukan bila data sesuai dengan syarat, argumen ketiga bila data tidak sesuai syarat.
  3. =VLOOKUP(DH-16,A2:F24,2,0) fungsi yang memiliki empat argumen ini untuk menampilkan data dalam suatu tabel atau daftar di mana argumen pertama berguna sebagai field kunci, argumen kedua berupa range dari daftar yang akan dibaca, argumen ketiga berupa nomor atau nama field dari data yang dicari, argumen keempat merupakan syarat ketepatan data.

Menggunakan lebih dari satu fungsi dalam sebuah argumen dalam sebuah formula tidak terbatas hanya menggunakan satu fungsi, tetapi dapat menggunakan beberapa fungsi. Penggunaan fungsi berganda ini juga dikenal dengan istilah “Nested Function”.

Maksud penggunaan fungsi berganda ini adalah untuk menggabungkan kemampuan atau fasilitas yang dimiliki oleh masing-masing fungsi guna menghasilkan perhitungan yang dikehendaki.

Berikut ini contoh beberapa formula dengan kombinasi beberapa fungsi untuk menghasilkan suatu proses:

1. =SQRT(ABS( 8))

2. =VLOOKUP(RIGHT(B3,3),’Tabel3′!$A$2:$B$5,2,FALSE)

3. =SMALL(E5:E20,ROW(E5:E20) INDEX(ROW(E5:E20),l)+I)

Mengedit Formula

Boleh jadi setelah Anda mengenter sebuah formula, mungkin Anda ingin mengubahnya. Anda dapat mengubah statemen dalam formula dengan cara yang sama ketika Anda menyusun formula tersebut. Berikut ini uraian singkat mengenai apa saja langkah yang memungkinkan untuk mengedit sebuah formula:

  1. Anda dapat memilih sel yang formulanya ingin diedit, kemudian ketikkan ulang sesuai dengan statemen yang benar, lalu tekan Enter.
  2. Anda dapat mengklik ganda sel yang akan diedit, atau setelah sel dipilih, tekan F2, lalu lakukan pengeditan. Untuk menggerakkan titik sisip, gunakan tombol panah.
  3. Anda dapat klik sel yang akan diedit, lalu klik pada formula bar di tempat yang akan diedit.
  4. Untuk mengedit semua pengeditan yang telah dilakukan, tekan Enter. Jika formula yang diedit berupa formula array, tekanlah Ctrl+Shift+Enter.
  5. Untuk membatalkan pengeditan sebelum Anda menekan Enter, tekanlah tombol Esc.
  6. Untuk membatalkan pengeditan setelah Anda telanjur menekan Enter, tekanlah Ctrl+Z atau gunakan menu Edit, Undo.

Memberikan Nama Sel  Maupun Range

Sebuah sel maupun suatu range sel dapat diberi nama. Misalnya sel yang berisi harga pokok sebuah bahan diberi nama dengan “Harga Pokok Bahan”. Sel yang berisi jumlah biaya produksi diberi nama “Biaya Produksi”. Dengan demikian, ketika Anda menggunakan formula, argumen dalam formula itu mudah dimengerti.

Dengan demikian, jika nama sel itu dipergunakan dalam sebuah formula akan seperti ini:
=(Bahan Pokok Bahan + Biaya Produksi)

Untuk menamai sebuah sel maupun suatu Range Sel, langkahnya sebagai berikut.
Pada Excel 2007:

1. Pilihlah sel yang dikehendaki, atau bila berupa range pilihlah range yang dikehendaki.

2. Klik tombol Name a Range pada tab Formula sehingga muncul kotak dialog New Name. Ketikkan nama sel/range pada kotak Name.

Formula pada excel 2007 dan 2003

3. Penulisan nama memiliki persyaratan sebagai berikut.

a. Nama sel diawali dengan karakter maupun underscore.

b. Jangan menggunakan spasi. Jika diperlukan pemisah antara satu kata dengan kata lain gunakanlah underscore ( _ ) di antara dua kata.

c. Penggunaan jumlah karakter pada nama Cell tidak terbatas, tetapi untuk kemudahan sebaiknya cukup simpel saja agar dapat terlihat ketika nama itu ditampilkan pada formula bar.

Mengenal dan memahami Formula pada excel

Pada Excel 2003 ke belakang:

1. Gunakan menu Insert Name > Define sehingga ditampilkan kotak dialog yang sama seperti berikut.

Mengenal dan memahami Formula pada excel

Mengenal dan memahami Formula pada excel

Memasukkan Nama  Sel ke dalam Formula

Setelah Anda memiliki sel yang telah diberi nama, selanjutnya nama sel tersebut dapat dipergunakan ketika Anda sedang menulis sebuah formula. Cara memasukkan nama sel dapat dilakukan melalui dua cara, yaitu:

  1. Dengan cara mengetikkan langsung pada saat Anda menulis formula pada formula bar. Namun Anda harus menuliskan dengan benar dan tepat nama sel tersebut karena apabila tidak tepat, misalnya salah ejaannya, salah penggunaan huruf besar kecil, maka Excel tidak dapat menerimanya.
  2. Menggunakan tombol Use in Formula pada group Named Cells di tab Formulas. Ketika Anda klik tombol ini, akan muncul nama-nama sel. Pilihlah salah satu yang dikehendaki, maka nama itu akan masuk pada posisi titik sisip ketika Anda menulis formula. Selain itu, juga dapat dipilih Paste untuk menampilkan kotak dialog Paste Name. Pilih dan klik OK.

Mengenal dan memahami Formula pada excel

Hemmm… panjang banget… capek nulisnya, saya kira cukup sekian dulu semoga saja membantu.

Related For Mengenal dan memahami Formula pada excel