Rumus / Formula Pada Excel 2007

Untuk menguasai Excel, tentunya tidak mungkin jika kita tidak memahami kumpulan rumus (formula) yang terdapat pada Excel.

Kami coba membantu Anda memahami apa yang dinamakan rumus, komponennya, bagaimana memasukkan rumus, disertai beberapa  link contoh untuk penggunaan yang mudah.

Jika bukan halaman ini yang Anda cari tetapi ingin belajar Excel dari awal, silahkan kunjungi halaman http://www.belajarexcel.info/2012/10/belajar-excel-bagi-pemula.html.

Rumus atau formula adalah suatu gabungan dari beberapa elemen yang akan menghasilkan suatu nilai kembalian.

Elemen-elemen tersebut adalah :

• fungsi
• operator 
• referensi cell
• nilai konstan berupa angka atau teks

Contoh rumus dan hubungan dari tiap elemen tersebut ditunjukkan dengan ilustrasi berikut.

Penulisan Rumus pada Excel selalu dimulai dengan tanda sama dengan ( = )

Gambar Animasi : Memasukkan Rumus di Cell

Daftar Operator yang Dapat Digunakan

Selain fungsi, banyak juga yang menanyakan bagaimana penggunaan operator seperti tanda +, -, *, /, dan lain-lain.

Berikut adalah kategori dan daftar operator yang ada pada Microsoft Excel 2007 dan keterangan untuk membantu menjelaskan penggunaannya :

• Operator Aritmatika
• + (tanda plus) : digunakan untuk menjumlahkan dua nilai.
• - (tanda minus)  : digunakan untuk mengurangi dua nilai, atau digunakan sebagai tanda negasi pada suatu angka konstanta.
• * (tanda bintang)  : digunakan untuk mengalikan dua nilai.
• / (tanda garis miring)  : digunakan untuk membagi suatu nilai dengan nilai lainnya.
• % (tanda persen) : digunakan untuk merepresentasikan nilai persentase.
• ^ (tanda sisipan) : digunakan sebagai tanda pangkat.

Gambar 1 : Contoh Penggunaan Operator Aritmatika

• Operator Perbandingan Nilai
• = (tanda sama dengan)  : menunjukkan apakah nilai yang satu sama dengan nilai lainnya.
• > (tanda lebih besar)  : menunjukkan apakah nilai yang satu lebih besar dengan nilai lainnya.
• < (tanda lebih kecil)  : menunjukkan apakah nilai yang satu lebih kecil dengan nilai lainnya.
• >= (tanda lebih besar sama dengan)  : menunjukkan apakah nilai yang satu lebih besar atau sama dengan nilai lainnya.
• <= (tanda lebih kecil sama dengan)  : menunjukkan apakah nilai yang satu lebih kecil atau sama dengan nilai lainnya.
• <> (tanda lebih kecil dan lebih besar) : menunjukkan ketidaksamaan antara dua nilai.

Gambar 2. Contoh Penggunaan  Operator Perbandingan Nilai

• Operator Penggabungan Teks
• & (tanda dan / ampersand) : digunakan untuk menggabungkan dua atau lebih teks.

Gambar 3. Contoh Penggunaan Operator Penggabungan Teks

• Operator Referensi
• : (tanda titik dua) : operator range (rentang nilai) yang mengambil dua referensi sel sebagai awal dan akhir rentang nilai.
• , (tanda koma) : operator union yang digunakan untuk menggabungkan dua atau lebih referensi menjadi satu referensi sel.
•   (tanda spasi) : operator irisan yang digunakan untuk menghasilkan perpotongan dari dua referensi atau lebih menjadi satu referensi sel.

Gambar 4. Sumber Data

Contoh 5. Contoh Penggunaan Operator Referensi (dibantu dengan fungsi SUM)

Demikian para pengunjung BelajarExcel.info, semoga artikel ini bisa bermanfaat.

Baru!!: Buat  para pengunjung yang ingin menguji kemampuan Anda dengan berbagai soal Excel, silahkan kunjungi situs kami di http://soal-latihan.belajarexcel.info. Setiap soal akan dikirimkan skornya langsung ke email Anda.

Kita juga memiliki ruang diskusi di Facebook yang sangat aktif. Jika berminat untuk diskusi dan sharing lebih lanjut join yuk di

• https://www.facebook.com/groups/belajarexcelinfo. 

Menggunakan Animasi pada MS PowerPoint 2007

Pendahuluan

Pada MS PowerPoint 2007 kita dapat menambahkan efek animasi pada item-item berikut :

• Text / Object
• SmartArt Graphic
• Slide (perpindahan antar slide)

Beberapa kategori event atau kejadian yang kita dapat buat pada item tersebut antara lain :

• Entrance : Efek yang terjadi pada saat kemunculan item.
• Emphasis : Efek yang terjadi setelah item-item di load semua dan tergantung event pada item tersebut, biasanya mouse click.
• Exit : Efek item menghilang.
• Motion Paths :  Efek animasi dengan mengikuti suatu path / jalur yang ditentukan.

Tiap kategori di atas memiliki item efek seperti Blinds, Box, Fade, Fly In, Wedge, dll.

Predefined Animation untuk Single Slide

Berikut adalah langkah-langkah untuk membuat animasi teks sederhana untuk single slide :

1. Buatlah satu slide powerpoint pada dokumen baru yang tampak seperti pada gambar berikut. Slide tersebut memiliki title "Formula Excel" dan isi slide dengan berupa 3 "poin" teks yaitu "SUM", "SUMIF", dan "CONCATENATE".
   
   
2. Klik tab "Animations" pada menu ribbon.
   
   
3. Klik dropdown list pada pilihan  "Animate: ". Sebagai contoh, terlihat pada gambar dari menu tersebut kita dapatkan tiga efek (Fade, Wipe , dan Fly In) dengan tujuan "All At Once" (keseluruhan item poin) atau "By 1st Level Paragraphs" (per tiap poin / paragraph level 1 - dimana efek berlaku untuk tiap poin sampai terjadi klik).
   
   
4. Pilih salah satu, misalkan "Fade" | "By 1st Level Paragraphs", jalankan Slide Show (F5) dan perhatikan efek animasi yang terjadi.
   
   
5. Selesai.

Custom Animation untuk Single Slide

Langkah berikutnya adalah kita akan mencoba custom animation :

1. Tambahkan satu slide powerpoint pada dokumen tersebut seperti tampak pada gambar berikut.
   
   
2. Pada tab "Animation" pada ribbon, klik tombol "Custom Animations".
   
   
3. Akan muncul panel "Custom Animation" pada bagian kanan dokumen.
   
   
4. Pada panel tersebut, klik "Add Effect" | "Entrance" | "Box".
   
   
5. Panel tersebut kemudian akan terisi efek dengan tulisan "1. Content..", klik tombol panah bawah untuk membuka isi dari placeholder tersebut. Terlihat ada dua item efek dengan petunjuk objek yang menjadi target dari efek tersebut.
   
   
6. Nomor yang terlihat tersebut akan terlihat juga pada item slide kita.
   
   
7. Jalankan Slide Show (F5) dan lihat hasil efek yang terjadi pada slide Anda. Setiap efek akan terjadi ketika Anda mengklik slide Anda.
8. Kembali ke panel, coba klik pada item no. 2 sehingga kotak oranye akan berada di samping text item.
   
   
9. Klik menu "Change" | "Entrance" | "6. Fly In".
   
   
10. Sekarang perhatikan bahwa kedua item tersebut memiliki icon efek yang berbeda.
    
    
11. Jalankan Slide Show (F5) dan perhatikan efek yang ditimbulkan. Menarik bukan ?
12. Selesai.

Penggunaan Slide Transition

Sekarang kita memiliki 2 slide pada dokumen kita yang masing-masing memiliki efek animasi pada item-itemnya. Untuk perpindahan slide sendiri  terasa agak sedikit "hambar" karena belum ada efek apapun.

Untuk praktek selanjutnya di bawah, kita akan menambahkan efek tersebut dan sangat mudah :

1. Pada panel "Slides" pilih slide kedua.
   
   
   
2. Pada bagian "Transition to This Slide" pada tab "Animations" terdapat beberapa icon mewakili efek yang bisa kita gunakan untuk transisi slide. Saat ini pilihan tersebut adalah "No Transition".
   
   
   
3. Coba klik pada salah satu efek, misalkan "Dissolve" dan perhatikan preview efeknya pada slide.
   
   
   
4. Jalankan Slide Show (F5) dan lihat keseluruhan efek animasi yang dihasilkan.
5. Selesai.

ADDRESS: Mengambil Teks Alamat Cell berdasarkan Kolom dan Baris

Fungsi ADDRESS

Address adalah function Excel yang digunakan untuk mengambil representasi teks dari referensi alamat cell berdasarkan posisi kolom dan baris. Tipe data dari nilai kolom dan baris adalah angka / integer.

Syntax dari fungsi Address adalah sebagai berikut :

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

dimana :

• row_num : adalah posisi baris (harus diisi)
• column_num : adalah posisi kolom (harus diisi)
• abs_num : adalah isian yang menentukan tipe pengembalian alamat - nilai-nilainya adalah sebagai berikut :
• 1 : alamat dalam format absolut (sama dengan jika nilai abs_num tidak diisi)
• 2 : alamat dalam format absolut untuk baris / row, sedangkan kolom / column tetap relatif
• 3 : alamat dalam format absolut untuk kolom / column, sedangkan baris / row tetap relatif
• 4 : alamat dalam format relatif
• a1 : jika TRUE maka akan mengembalikan format kolom berdasarkan alfabet (A, B, C... AA, ...) dan baris berdasarkan angka (1,2,3,...). Sedangkan jika FALSE maka akan mengembalikan berdasarkan angka namun dengan prefix R untuk baris dan prefix C untuk kolom.
• sheet_text : penambahan teks di depan alamat

Contoh Penggunaan

Berikut adalah screenshot contoh penggunaan dari fungsi Address. Dokumen Excel contoh tersebut dapat didownload pada bagian akhir artikel ini.

Contoh Formula menggunakan Fungsi Address

Artikel Terkait

• Rumus / Formula Pada Excel 2007

Sumber Referensi

• http://office.microsoft.com/en-us/excel-help/address-function-HP010062407.aspx

~~ Selesai ~~

Indirect : Mengambil Nilai berdasarkan Teks Alamat

Pendahuluan

Indirect adalah fungsi pada Excel yang digunakan untuk mengambil nilai dari sel dengan menggunakan teks dari alamat dari suatu sel.

Syntax dari indirect adalah sebagai berikut :

INDIRECT(alamat_cell)

Contoh format alamat_cell adalah sebagai berikut :

• "A2"
• "H122"
• dan lain-lain

Contoh Penggunaan

Berikut adalah screenshot contoh penggunaan indirect. Dokumen Excel tersebut dapat juga Anda download pada bagian akhir dari artikel ini.

Contoh Penggunaan Indirect

Artikel Terkait

• Rumus / Formula Pada Excel 2007
• ADDRESS: Mengambil Teks Alamat Cell berdasarkan Kolom dan Baris

Sumber Referensi

• http://support.microsoft.com/kb/213933
• http://office.microsoft.com/en-us/excel-help/indirect-function-HP010062413.aspx

~~ Selesai ~~

Penggunaan Fungsi Index dan Match pada Excel 2007

Pasangan Match dan Index sering digunakan untuk mencari data secara dinamis

Apa itu Fungsi Index dan Match ?

Dalam keseharian pengolahan data dalam Excel hampir dipastikan Anda akan bekerja dengan dua pola berikut:

• Melakukan pencarian data dari cell lain, biasanya dari suatu nilai terhadap suatu referensi range data tertentu.
• Pencarian tersebut harus fleksibel, dalam arti dapat mencari dan mengambil data dari kolom ataupun baris manapun yang kita tentukan.

Untuk keperluan tersebut, Anda dapat melakukannya dengan mudah menggunakan dua fungsi Excel yaitu INDEX dan MATCH.

Fungsi INDEX

Fungsi INDEX adalah fungsi yang cukup sederhana, digunakan untuk mendapatkan nilai dari suatu cell berdasarkan pencarian pada suatu definisi table / data range worksheet kita.

Pencarian digunakan berdasarkan informasi posisi kolom dan baris, dengan acuan berupa kolom dan baris pertama table / data range tersebut.

Syntax fungsi INDEX adalah sebagai berikut :

INDEX(array, row_num, [column_num])

Keterangan :

• array : adalah table / range data yang terdiri dari satu atau beberapa kolom dan baris.
• row_num : adalah angka yang menunjukkan posisi baris dengan acuan dari cell pertama ( kolom / baris ujung kiri atas ) dari array.
• column_num :  adalah angka yang menunjukkan posisi kolom dengan acuan dari kolom / baris pertama dari array. Argumen ini bersifat opsional (boleh digunakan atau tidak).

Penjelasan mengenai fungsi INDEX ini dapat diilustrasikan pada Gambar 1 di bawah ini.

Gambar 1. Ilustrasi Penggunaan Fungsi Index

Dengan formula "=INDEX(B3:B8, 4, 2)", kita mengambil jarak data dari posisi acuan B3 sebesar 2 kolom ke kanan dan 4 baris ke bawah. Kenapa kita mengambil B3 sebagai acuan ? Karena B3 adalah  cell pertama dari array / data range B3:D4.

Hasil dari "navigasi cell" pada fungsi ini adalah nilai dari cell C6, yaitu angka 20.

Gambar berikutnya (gambar 2) menunjukkan hasil penggunaan fungsi INDEX pada worksheet Excel.

Gambar 2. Contoh Penggunaan Fungsi Index (klik untuk memperbesar)

Fungsi MATCH

Fungsi MATCH adalah fungsi yang digunakan untuk mencari suatu nilai dari suatu range yang terdapat pada suatu kolom atau baris, tapi tidak kedua-duanya.

Syntax fungsi MATCH adalah sebagai berikut :

MATCH(lookup_value, lookup_array, [match_type])

Keterangan :

• lookup_value : adalah nilai yang ingin dicari pada lookup_array.
• lookup_array : adalah range data dari suatu kolom ataupun baris.
• match_type :  adalah angka yang menunjukkan tipe pencocokan sebagai berikut :
• 1 : jenis pencocokan dimana lookup_array dalam keadaan terurut secara ascending (kecil ke besar). Pencocokan dilakukan dengan mengambil nilai terbesar dari range data yang lebih kecil atau sama dari lookup_value.
• 0 :  jenis pencocokan dimana pada lookup_array dicari data yang sama persis dengan lookup_value. Urutan data tidak menjadi masalah. Jika diketemukan lebih dari satu data yang sama, maka akan diambil data yang pertama kali diketemukan secara sekuensial.
• -1 :  jenis pencocokan dimana lookup_array dalam keadaan terurut secara descending (besar ke kecil).  Pencocokan dilakukan dengan mengambil nilai terkecil dari range data yang lebih besar atau sama dari lookup_value.

Untuk kejelasan apa yang dimaksud dengan  match_type ini, perhatikan ilustrasi pada Gambar 3 di bawah ini.

Pada contoh tersebut nilai lookup adalah 3, yang kemudian dicari pada data array dengan tiga kelompok susunan data seperti tampak pada gambar.

Gambar 3. Ilustrasi Contoh Penggunaan Match Type - Skema Pertama

Terlihat dengan pilhan match_type mulai dengan nilai  -1, 0 dan 1 didapatkan hasil posisi yang berbeda dari fungsi match, masing-masing yaitu 5, 2, dan 4.


Contoh lainnya terlihat pada Gambar 4 di bawah ini. Pada kasus ini nilai lookupnya adalah 4 yang dicari pada data array dengan nilai 1, 2, 3, 3, 5, 5 dan 6 (sama dengan contoh sebelumnya). Dengan pilhan tiap  tipe mulai dari -1, 0 dan 1 didapatkan posisi dari fungsi match masing-masing adalah 3, NA (Not Available / Tidak Ditemukan), dan 4.

Gambar 4. Ilustrasi Contoh Penggunaan Match Type - Skema Kedua

Screenshot berikut menunjukkan beberapa contoh lebih lanjut penggunaan match pada Excel 2007 (klik pada gambar untuk memperbesar).

Gambar 5. Contoh Penggunaan Match pada Excel (1)

Gambar 6. Contoh Penggunaan Match pada Excel 2007 (2)

Gambar 7. Contoh Penggunaan Match pada Excel 2007 (3)

Penggunaan dari Gabungan Fungsi INDEX dan MATCH

Seperti dijelaskan sebelumnya, penggabungan fungsi INDEX dan MATCH akan menghasilkan solusi pencarian data yang cukup powerful dimana kita dapat mencari dari referensi berdasarkan kolom / baris yang kita inginkan.

Syntax dari penggabungan fungsi ini tampak seperti berikut :

INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), column_num)

jika yang dicari adalah data range baris pada suatu kolom,

atau...

INDEX(array, row_num, MATCH(lookup_value, lookup_array, [match_type]))

jika yang dicari adalah data range kolom pada suatu baris.

Sekilas solusi ini mirip dengan fungsi VLOOKUP yang telah kita bahas sebelumnya. Namun dengan fungsi VLOOKUP kita terbatas pada pencarian pada kolom pertama pada data range referensi dan harus terurut, sedangkan dengan penggabungan fungsi ini kita bisa mencari dari kolom manapun dan tidak perlu dalam keadaan terurut (sesuai match_type tentunya).

Berikut adalah dua gambar contoh penggunaan dari gabungan kedua fungsi INDEX dan MATCH.

Gambar 8. Contoh Penggunaan Index dan Match (1)

Gambar 9. Contoh Penggunaan Index dan Match (2)

Kesimpulan

Fungsi MATCH dan INDEX masing-masing merupakan fungsi untuk melakukan pencarian dan navigasi dari suatu table / data range. Bedanya fungsi MATCH mengembalikan nilai posisi sedangkan fungsi INDEX mengembalikan nilai dari suatu posisi cell.

Penggabungan kedua fungsi tersebut menjadi solusi yang sangat baik sebagai alternatif dari fungsi VLOOKUP yang telah dikenali sebagai fungsi untuk mencari / lookup suatu nilai referensi.

Update

Contoh file Excel yang dibahas disini dapat didownload di URL berikut : https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/belajar-excel/Contoh%20Fungsi%20Index%20dan%20Match.zip.

Sumber Referensi

• http://office.microsoft.com/en-us/excel-help/index-function-HP010069831.aspx
• http://office.microsoft.com/en-us/excel-help/match-function-HP010062414.aspx

Menggabungkan Teks di Excel

Pendahuluan

Penggabungan teks di Excel sangat sering dilakukan, misalkan untuk tujuan pengkodean. Ada dua cara sederhana untuk menggabungkan teks yaitu dengan menggunakan operator & dan fungsi CONCATENATE.

Sebagai contoh, kita menggunakan worksheet yang tampak sebagai berikut :

Kita akan menggabungkan cell A2, B2, C2, D2, dan E2 dengan kedua metode tersebut.

Catatan : Klik pada gambar untuk memperjelas jika Anda rasakan terlalu kecil dan tidak jelas.

Menggunakan Operator &

1. Arahkan kursor Anda ke F2.
2. Ketik formula berikut dan tekan Enter.
   
   =A1 & B2 & C2 & D2 & E2 & F2
   
3. Hasilnya akan tampak sebagai berikut.
   
   
4. Selesai

Menggunakan Fungsi CONCATENATE

1. Arahkan kursor Anda ke G2.
2. Ketik formula berikut dan tekan Enter.
   
   =CONCATENATE(A2,B2,C2,D2,E2)
   
   
3. Hasilnya akan tampak seperti pada gambar berikut.
   
   
4. Selesai.

Contoh Penggunaan Fungsi SumIF

Pendahuluan

SUMIF adalah sebuah fungsi untuk menjumlahkan nilai-nilai yang terdapat pada suatu range data, namun range yang diambil didasarkan pada suatu pengkondisian atau filter yang ditentukan oleh kita.

Syntax dari fungsi SUMIF adalah sebagai berikut :

SUMIF(range, criteria, [sum_range])

Keterangan :

• range : adalah range dari cell-cell yang ingin kita evaluasi berdasarkan kriteria yang akan kita tentukan.
• criteria : kondisi atau aturan filter yang ingin kita gunakan.
• sum_range : jika ini disebutkan maka range ini yang akan jadi acuan untuk mengambil nilai, tetapi posisinya sesuai dengan kolom - baris dari range yang terfilter.

Contoh Penggunaan

1. Sebagai contoh, kita memiliki data range seperti terlihat di bawah ini  (A2:D10). Yang ingin kita lakukan adalah mengambil total dari Kacang saja kemudian ditempatkan hasilnya pada D11.
   
   
2. Tempatkan cursor pada cell D11 dan ketikkan formula berikut :
   
   =SUMIF(D3:D10,"=Kacang",C3:C10)
   
   ini artinya dari range kriteria dari D3:D10, kita mengambil yang nilainya Kacang saja. Setelah itu ambil range nilai yang akan dijumlah yaitu C1:C10.
   
   
3. Hasilnya akan terlihat sebagai berikut. Nilai 15 didapatkan sebagai total jumlah untuk Kacang saja.
   
   
4. Selesai

Download Contoh

Klik disini untuk mendownload contoh worksheet yang berisi penggunaan SUMIF pada artikel ini.

Artikel Terkait

• Rumus / Formula Pada Excel 2007

Sumber Referensi

• http://office.microsoft.com/en-gb/excel-help/sumif-HP005209292.aspx