Rumus / Formula Pada Excel 2007

Microsoft Excel merupakan aplikasi untuk mengolah data secara otomatis yang dapat berupa perhitungan dasar, rumus, pemakaian fungsi-fungsi, pengolahan data dan tabel, pembuatan grafik dan menajemen data.

Pemakaian rumus sendiri dapat berupa penambahan, pengurangan, perkalian dan lain sebagainya. Sedangkan pemakaian fungsi-fungsi dapat berupa pemakaian rumus yang bertujuan untuk menghitung dalam bentuk rumus matematika maupun non matematika.

 Microsoft Excel dapat juga digunakan untuk menyelesaikan berbagai keperluan administrasi, dari yang sederhana sampai dengan yang rumit. Pada pemakaian keperluan yang sederhana tersebut misalkan untuk membuat perencanaan kebutuhan suatu perusahaan, berupa perencanaan barang kebutuhan, jumlah maupun harganya.

 Sampai saat ini Microsoft excel sudah sampai pada versi 2013, sedangkan saya sendiri masih menggunakan versi 2007. Yah meskipun berbeda versi tetapi fungsi tetap sama, hanya dibedakan fitur yang pasti lebih baik dibanding versi terdahulu.

 pada Microsoft excel kita bekerja dengan system workbook, sedangkan di dalam workbook terdapat worksheet atau lembar kerja. Pada worksheet ini kita bekerja dengan menggunakan kolom dan baris yang membentuk kotakan kecil-kecil berupa sel-sel tempat kita memasukkan data.

Formula untuk Penjumlahan (Sum)

Operasi penjumlahan digunakan untuk mengetahui nilai total dari sekelopok dara. Perintah untuk operasi penjumlahan adalah =SUM (range data). Operasi penjumlahan dapat dilakukan secara vertikal maupun horizontal. Untuk lebih jelasnya, perhatikan data berikut.

Dari gambar di atas, pada sel B7, ketikkan formula =SUM(B5:B7) dan tekan enter. Secara otomatis, Excel akan langsung menjumlahkan data tersebut. Anda dapat melakukan hal yang sama pada sel C8, D8 dst. Akan tetapi, untuk mempermudahkan anda, formula tersebut dapat anda copy dan paste, atau menarik ujung kanan bawah sel B8. Secara otomatis, formula akan menjumlahkan data tersebut.

Formula untuk Mencari Rata-rata (Average)

Seperti yang anda ketahui sebelmnya, bahwa untuk mencari nilai rata-rata dari sekelompok data menggunakan formula =Average (Range data). Seperti halnya formula penjumlahan, formula untuk mencari rata-rata juga dapat digunakan secara vertikal maupun horisontal.

Formula untuk Mencari Nilai Maksimum dan Minimum

Untuk mencari nilai tertinggi atau maksimum, anda dapat menggunakan formula =MAX(range data. Adapun untuk mencari nilai terendah atau minimum dengan menggunakan formula =MIN(range data). Sebagai contoh seperti pada gambar dibawah :

Nilai maxsimum dapat dicari dengan menuliskan formula =MAX(C4:C13) dan nilai minimum dapat dicari seperti pada gambar diatas yaitu dengan menuliskan formula =MIN(C4:C13).

Formula IF

Fungsi IF digunakan untuk menguji apakah suatu syarat sudah terpenuhi (kondisi benar), atau apakah syarat tersebut tidak terpenuhi (kondisi salah).

Fungsi IF terbagi menjadi 2 jenis, yaitu fungsi IF dengan syarat atau biasa disebut IF tunggal, dan fungsi IF lebih dari dua syarat.

1. Fungsi IF dengan Dua Syarat

Fungsi  IF dengan dua syarat memiliki dua nilai kemungkinan dapat diselesaikan dengan satu fungsi. Bentuk umumnya adalah sebagai berikut.

=IF(Logika; [Nilai _jika_syarat_benar]; [Nilai_jika_syarat_salah])

•  "Logika", merupakan nilai yang menghasilkan nilai benar (TRUE) atau salah (FALSE). Misalnya, jika terdapat pertanyaan apakah nilai pada sel A1 sama dengan nilai pada sel C1, maka secara langsung anda dapat menjawab ya atau tidak, namun dalam Excel akan diganti menjadi benar (TRUE) atau salah (FALSE). Dalam pengisian logika ini antara kondisi dan syarat harus menggunakan pembanding.
• "Nilai_jika_syarat_benar", berisi nilai yang dihasilkan jika logika bernilai benar (TRUE). Untuk nilai bertipe teks, penulisan di apit dengan tanda petik ganda ("..."), sedangkan untuk tipe numerik tidak perlu diapit tanda petik ganda.
• "Nilai_jika_syarat_salah", berisi nilai yang dihasilkan apabila logika bernilai salah (FALSE). Cara penulisan sama dengan Nila-jika_syarat_benar.

Sebagai contoh, misalkan anda ingin menentukan merk mobil berdasarkan kodenya, dan tempatkan hasilnya pada kolom MEREK MOBIL dengan cara isikan sel B2 kolom MEREK MOBIL dengan pernyataan "Jika KODE adalah T, maka merk mobil diisi dengan Toyota, selain itu NAMA MOBIL diisi dengan HONDA.

• Ketikkan pernyataan gambar diatas dalam bentuk seperti berikut :

=IF(A2="T"; "TOYOTA";"HONDA")

• Keterangan :

1. =IF     : awal pernyataan jika (if)
2. A2      : alamat sel kode
3. "T"     : syarat yang harus dipenuhi
4. "TOYOTA" : nilai jika logika benar
5. "HONDA" : nilai jika logika salah

Kemudian, ketikkan rumus tersebut pada sel B2 dan tekan enter, maka akan tamoil jawaban TOYOTA, karena setelah diuji ternyata kondisi di sel A2 memenuhi syarat yaitu T, sehingga yang dijalankan adalah bagian nilai benar yaitu TOYOTA.

Selanjutnya, gunakan fasilitas fill handle yang ada di sudut kanan bawah sel B2 untuk menyalin (copy) rumus dan mengisikan sel di bawahnya, klik dan geser fill handle ke bawah hingga sel B5. Pada sel B3 akan terisi HONDA, hal ini dikarenakan setelah diuji ternyata sel A2 tidak memenuhi syarat yang ditentukan yaitu T, maka yang dijalankan adalah bagian nilai salah yaitu HONDA. Cara pembacaan yang sama juga dilakukan oleh sel berikutnya.

Dengan menggunakan fungsi IF tunggal untuk menyelesaikan 2 kondisi, maka jika terdapat kode lain yang tidak memenuhi syarat (T), akan secara otomatis dianggap sebagai nilai salah dan kolom NAMA MEREK MOBIL akan terisi dengan HONDA.

2.  Fungsi IF dengan Persyaratan Lebih dari Dua

Fungsi IF kedua yang akan dibahas di dalam artikel ini adalah fungsi IF dengan lebih dari dua syarat atau biasa disebut dengan fungsi IF bertingkat. Lebih dari dua syarat disini berarti di dalam satu rumus terdapat beberapa statement IF yang memungkinkan anda untuk mengelola beberapa kondisi sekaligus.

MS-Excel 2010 memungkinkan anda untuk menggunakan sampai dengan 64 fungsi IF. Bentuk IF dengan persyaratan lebih dari dua 

=IF(syarat1 ; [nilai_jika_syarat1_benar]; IF(syarat2; [nilai_jika_syarat2_benar]:........ ;[nilai_jika_semua_syarat_salah])

• Syarat1, syarat2, syarat3, .... diisi nilai atau exspresi yang dapat diuji untuk menghasilkan nilai TRUE atau FALSE. Dalam pengisian logika ini antara kondisi dan syarat harus menggunakan operator pembanding.
• Nilai_jika+syarat1_benar, nilai_jika_syarat2_benar, .... adalah argumumen berisi nilai yangakan dihasilkan bila logika yang diuji benar. Jika logika yang diuji bernilai benar dan argumen ini dihilangkan, nilai TRUE yang akan ditampilkan.
• Nilai_jika_syarat1_salah, nilai_jika_syarat2_salah, .... adalah argumen berisi nilai yang akan dihasilkan bila logika yang diuji bernilai salah. Jika logika yang diuji bernilai FALSE dan argumen ini dihilangkan, nilai FALSE yang akan ditampilkan.

Contoh : Menempatkan nama handphone berdasarkan kodenya, dan menempatkan hasilnya pada kolom NAMA HANDPHONE dengan cara mengisi sel B2 pada kolom NAMA HANDPHONE dengan ketentuan berikut.

• Jika KODE adalah B, maka NAMA HANDPHONE diisi dengan Blackberry
• Jika KODE adalah N, maka NAMA HANDPHONE diisi dengan Nokia.
• Jika KODE adalah SE, maka NAMA HANDPHONE diisi dengan dengan SonyEricsson
• Selain dari kode di atas maka NAMA HANDPHONE diisi dengan Samsung
• Ketikkan pernyataan diatas dalam bentuk seperti berikut:

      =IF(A2="B";"Blackberry";IF(A2="N";"Nokia";IF(A2="SE";"SonyEricsson";Samsung")))

Masukan rumus tersebut pada sel B2, kemudian tekan enter sehingga pada sel B2 akan tampil blackberry karena saat pengujian syarat 1 bernilai benar, seperti contoh gambar di atas.Selanjutnya tinggal menyalin rumus-rumus dari sel B3

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.

sumber :

http://www.belajarexcel.info/2012/04/rumus-formula-pada-excel-2007.html

Rumus Excel VLOOKUP

Syntax dari fungsi microsoft excel ini adalah : VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

Rumus Excel VLOOKUP dipakai untuk mencari nilai dalam kolom pertama dari tabel dan mengembalikan nilai dalam baris yang sama dari kolom lain dalam tabel. Supaya lebih jelas, kita pakai fungsi Microsoft Excel VLOOKUP dalam contoh berikut.

Toko Saribusana memiliki tabel Daftar Barang sbb. :

Kemudian, saya diminta membuat tabel hasil penjualan bulan Juli 2011 seperti berikut :

Untuk mengisi kolom Keterangan, saya akan ambil dari Daftar Barang Toko Saribusana berdasarkan Kode yang ada, maka rumus Excel nya adalah

=VLOOKUP(B12,$B$3:$E$6,2,FALSE)

B12 : Kode Barang di B12 adalah acuan yang dipakai untuk mengambil data di tabel Daftar Barang.

$B$3:$E$6 : range tabel yang akan kita ambil datanya, dalam hal ini adalah tabel Daftar Barang tanpa mengikutsertakan judul kolom. Dan,  jangan lupa gunakan $ sebab range tabel adalah absolut.

2 : kolom ke-2 yaitu kolom Keterangan di range tabel adalah kolom yang akan kita ambil datanya. Kolom ke-1 selalu dimulai dari kolom paling kiri.

Singkatnya, kita menyuruh Excel melihat Kode di cel B12 lalu cari di range tabel Daftar Barang sesuai Kode yang sama, setelah itu ambil nilai di kolom Keterangannya dan isikan di tabel Hasil Penjualan. Hasilnya :

Demikian juga untuk mengisi kolom Harga Penjualan di cel E12, dengan memperhatikan bahwa Harga Penjualan = Harga Pokok * (1+Markup), maka , rumus Excel nya adalah

=VLOOKUP(B12,$B$3:$E$6,3,FALSE)*(1+VLOOKUP(B12,$B$3:$E$6,4,FALSE))

Di fungsi Microsoft Excel tersebut, kita menyuruh Excel melihat Kode di B12 lalu cari kode yang sama di tabel Daftar Barang, setelah itu ambil Harga Pokoknya dan kalikan Harga Pokok dengan (1+Markup). Nilai Markup diambil dengan cara yang sama dengan cara mengambil nilai Harga Pokok. Maka kita mendapatkan Harga Penjualan untuk Kode di B12.

Oke, silakan mencoba dulu dan mengutak atik fungsi Microsoft Excel VLOOKUP. Semoga mengerti.

Oh, ya, V di VLOOKUP singkatan dari vertikal. Penggunaan VLOOKUP (bukan HLOOKUP) ketika nilai-nilai acuan Anda berada dalam kolom di sebelah kiri tabel data dan Anda mengambil data lain di sebelah kanannya. Gunakan fungsi microsoft excel HLOOKUP ketika nilai-nilai acuan Anda berada dalam baris di sebelah atas tabel data dan Anda ingin mengambil data-data di baris bawahnya.

Rumus Excel IF

Syntax dari fungsi microsoft excel ini adalah : IF(logical_test,value_if_true,value_if_false)

Maka dengan menggunakan fungsi microsoft excel IF, kita memberikan :

• suatu kondisi kepada Excel, lalu
• jika kondisi itu terpenuhi maka lakukan A,
• jika tidak terpenuhi maka lakukan B.

Oke, kita coba dalam kasus yang sama yaitu Toko Saribusana. Kembali saya diminta menambahkan sebuah kolom di Tabel Penjualan bulan Juli 2011 yang menunjukkan status barang. Jika penjualan barang lebih dari 500 unit berarti Barang Laku, jika penjualan diantara 300 dan 500 maka Cukup Laku, dan di bawah 300 berarti Ga Laku.

Rumus excel di sel G12 adalah

=IF(D12>300,IF(D12>500,”Laku Keras”,”Kurang Laku”),”Ga Laku”)

Saya meminta Excel untuk mengecek :

• Apakah unit terjual di D12 lebih dari 300  =IF(D12>300
• Jika ya maka cek lagi apakah D12 lebih dari 500, IF(D12>500
• Jika ya maka Laku Keras, “Laku Keras”
• jika tidak maka Kurang Laku.  “Kurang Laku”
• jika tidak berarti barang Ga Laku. “Ga Laku”

Di atas terlihat bahwa terjadi dua kali pengecekan, atau saya memakai 2 kali rumus excel IF. Fungsi If kedua berada di dalam fungsi If pertama. Fungsi IF dapat dipakai di dalam fungsi IF lainnya, jangan lupa fungsi IF di dalam tidak memakai tanda sama dengan [=] lagi.

Sekarang kita akan mencoba fungsi microsoft excel yang berhubungan dengan tanggal.

Fungsi Microsoft Excel Date and Time

Kedua fungsi microsoft excel ini sering juga dipakai dalam mengolah data di Excel.

Langsung saja, di atas adalah tabel Daftar Siswa Tercepat dalam Ujian IPA dari tiap kelas. Petugas administrasi memasukkan data tanggal, bulan, tahun dalam kolom-kolom terpisah supaya lebih cepat dan mudah. Nah, tugas saya mencari siapa yang tercepat dari kelima kontestan tersebut.

Pertama, saya satukan dulu tanggal, bulan dan tahun menjadi satu di sel C12 dengan fungsi microsoft excel :

=DATE(E3,D3,C3)

Fungsi microsoft excel ini mengambil data di sel E3,D3,C3 dan menjadikannya tanggal.

Lalu satukan juga jam, menit, dan detik tiap siswa di sel D12 dengan rumus :

=TIME(F3,G3,H3)

Rumus ini mengambil data di sel E3,D3,C3 dan menjadikannya waktu, untuk format time yang Anda ingini, ubah dalam menu Format Cells (klik kanan di sel, pilih Format Cells, pilih Time, pilih format yang Anda mau).

Baru saya cari siswa tercepat di sel E12 dengan fungsi microsoft excel :

=RANK(D12,$D$12:$D$16,1)

Fungsi Rank mencari urutan dalam suatu range, lalu Anda bisa pilih mau Ascending atau Descending.  Hasilnya adalah tabel berikut, selamat buat Cassey, kamu tercepat!

sumber :

http://www.umboh.net/2011/03/daftar-fungsi-excel-berdasarkan.html
http://blog-cilik.blogspot.com/2012/05/rumus-rumus-untuk-office-excel-2007.html