RUMUS EXCEL

Shortcut di Excel yang sering digunakan

Shortcut pada excel, pengoperasian menu, toolbar atau ribbon (excel 2007) akan lebih cepat jika kita sudah hapal shortcut nya. Adapun shortcut yang sering dipakai yaitu :

Action	Shotcut
Pindah cell ke kanan satu per satu	Tab
Pindah cell ke bawah satu per satu	Enter
Menghapus data cell terpilih	Backspace
Kembali ke row awal	Home
Insert tanggal	Ctr + ; (semicolon)
Insert Jam	Ctr + Shift + ; (semicolon)
Memulai menulis rumus	=
Spelling check	F7
Menanpilkan Style dialog	Alt + ' (apostrophe)
Menampilkan Format Cells dialog	Ctr + 1
Mengubah format ke general number	Ctr + Shift + ~
Mengubah angka ke format dollar	Ctr + Shift + $
Mengubah angka ke format persen	Ctr + Shift + %
Membuat border	Ctr + Shift + &

Referensi :

Rumus di Excel adalah rumus matematika yang digunakan untuk menghitung nilai. Dalam penulisannya rumus di Excel harus dimulai dengan tanda sama dengan (=). Rumus excel dalam bahasa inggrisnya excel formula adalah salah satu fitur yang paling berguna dari program excel. Rumus dapat yang sederhana seperti menambah dua angka atau yang lebih kompleks. Setelah Anda mempelajari dasar format membuat rumus, Excel akan melakukan perhitungan semua untuk Anda.

Fungsi di Excel adalah sebuah rumus yang sudah ada di Excel. Seperti rumus, fungsi excel dalam bahasa inggrisnya excel function, juga penulisannya dimulai dengan tanda sama dengan (=). Sebagai contoh fungsi yang paling mudah dipakai adalah fungsi SUM yang sintaknya adalah =SUM (number1,[number2], …). Dengan kata lain, fungsi excel adalah sekumpulan rumus yang sudah diprogram di dalam excel, kita tinggal memanggil fungsi tersebut dengan menuliskan fungsi tersebut.

Dalam penulisan rumus di excel, bisa terdiri dari fungsi, referensi, operator dan konstan.

1. Fungsi PI(), yaitu angka 3.142…
2. Mengambil referensi dari cell A2
3. Konstan dengan nilai 2

Operator dalam contoh di atas memakai * (kali) dan ^ (pangkat)

Fungsi excel VLookup “=VLOOKUP()”

Pada bagian ini kami akan mengulas secara detail fungsi excel VLOOKUP. kelebihan fungsi excel VLOOKUP dibanding dengan LOOKUP yaitu, pengambilan data dengan VLOOKUP bisa disetting mutlak (Nilai Range_Lookup diisi false), artinya jika sumber data tidak ada maka vlookup akan menghasilkan nilai #N/A yang artinya “no value is available” tidak ada nilai yang tepat. Hal ini sangat menolong kita supaya tidak terjadi kesalahan dalam pengambilan data. Lagi pula fungsi excel VLOOKUP dapat mengambil data kolom ke n yang sebaris dengan mensettingg Col_index_num.

• Lookup_value
  Nilai apa yang akan kita cari, contoh di bawah, nilai dengan No Induk 99999
• Table_array
  Table dimana tempat data yang akan dicari
• Col_index_num
  Kolom yang keberapa yang akan dicari
  Nilai dari Col_index_num tidak boleh lebih dari jumlah kolom dari data Table_array, jika lebih akan menghasilkan nilai #REF
• Range_lookup
  Diisi dengan true(1) atau false(0)
  Jika diisi dengan true(1), Table_array harus disusun secara urut, oleh sebab itu sebagai amannya menghindari salah mencari data pakai nilai false(0)
  Contoh di atas memakai nilai false, jika nilai yang kita cari tidak menemukan dari Table_array, nilai hasil akan menjadi #N/A

Gambar 01

Contoh Penggunaan VLookup #Kolom ke N

Pada contoh di bawah ini bagaimana cara mengambil data pada kolom ke n setelah No Induk. Pada gambar 02 menunjukkan pengambilan data dengan kunci No Induk dari data sebelah kiri. No Induk “90123" akan menghasilkan Nama “Uban” jika diambil kolom ke 2, begitu juga jika pengambilan data untuk kolom ke 3 akan mengambil nilai dari Kelamin. Untuk setting pengambilan data kolom ke n tersebut bisa dilihat pada gambar 03.

Gambar 02

Gambar 03

Salah satu trik (Gambar 04) yang sering kami pakai dalam pengambilan sebuah data yang banyak dengan menggunakan rumus excel vlookup. Pada saat mengambil data, kita juga perlu cross check apakah data itu semua terambil. Contoh kasus pada gambar (Gambar 01) di bawah, data bedara di sebelah kiri, pengambilan data sebelah kanan. Pada bagian sebelah kiri bagian data tersebut, kami buatkan juga rumus excel VLookup mengambil data dari sebelah kanan. Jika hasil pengambilan dari sebelah kanan tida ada yang sesuai, maka kolom check (sebelah kiri) akan menghasilkan nilai #N/A.

Gambar 04

Fungsi excel Lookup “=LOOKUP()”

Kapan harus memakai fungsi excel LOOKUP, VLOOKUP dan HLOOKUP. Dan bagaimana caranya pemakaiannya. Dalam artikel ini kami akan menerangkan secara detail mengenai fungsi LOOKUP, VLOOKUP dan HLOOKUP. Jika kita sudah paham akan teori dasar LOOKUP, VLOOKUP & HLOOKUP, nantinya supaya tidak akan lagi ada kesalahan kecil yang mengakibatkan data salah pengambilan data.

Gambar 01

Perhatikan contoh di atas (Gambar 01), yang warna merah adalah hasil yang tidak sesuai dengan harapan, No Induk “99999? menghasilkan nama “Semprul”, sedangkan nama “Telo” muncul 3 kali. Untuk No Induk “10000? menghasilkan nilai “#N/A” hal itu wajar karena No Induk “10000? tidak ada yang sesuai dari data sebelah kiri. Kenapa bisa terjadi demikian? Lihat penjelasan di bawah.

Gambar 02

Pemakaian fungsi LOOKUP (Gambar 02) mengharuskan data asal (sebelah kiri) harus diurutkan dahulu, tapi walaupun demikian tetap saja pengambilan data masih kurang sreg. Sebagai contoh No Induk “99999? meghasilkan nilai “Uban” jelas salah, tapi memang begitu fungsi lookup menghitungmya, karena jika tidak menemukan dan nilai data yang diambil lebih besar, maka lookup akan mengambil data dari nilai yang paling besar (Nila No Induk paling besar “90123? lebih kecil dari dari “99999?. Sedangkan No Induk “10000? menghasilkan nilai “#N/A” hal itu benar karena nilai “10000? tidak ada dalam array (table sebelah kiri).

Gambar 03

Bagaimana kalau fungsi LOOKUP (Gambar 03) yang data diambil jumlah kolom lebih banyak dari row atau kolom dan row sama banyaknya? Contoh di atas adalah penjelasannya, pada contoh ini kami meletakkan data yang diambil dibagian sebelah kanan. Ketentuannya adalah sbb :

• Jika jumlah row lebih banyak dari kolom, oleh sebab itu, fungsi LOOKUP akan mencari kolom pertama
• Jika jumlah kolom lebih banyak dari row, fungsi LOOKUP akan mencari row pertama
• Dan jika row sama dengan kolom, maka fungsi LOOKUP akan cari kolom pertama

Berikut adalah pemakaian lookup (Gambar 04) yang tanpa takut menghasilkan nilai yang salah. Contoh di bawah memakai kelemahan fungsi tersebut, karena jika kita mencari data yang lebih besar dari yang ada, maka lookup akan mencari nilai paling akhir. Contoh di bawah adalah mencari delivery terakhir.

Gambar 04

Fungsi excel HLookup “=HLOOKUP()”

• Lookup_value
  Nilai apa yang akan kita cari, contoh di atas, nilai dengan No Induk E40, dalam hal ini cell E40 adalah mempunyai nilai 99999
• Table_array
  Table dimana tempat data yang akan dicari
• Row_index_num
  Baris yang keberapa yang akan dicari
  Nilai dari Row_index_num tidak boleh lebih dari jumlah kolom dari data Table_array, jika lebih akan menghasilkan nilai #REF
• Range_lookup
  Diisi dengan true(1) atau false(0)
  Jika diisi dengan true(1), Table_array harus disusun secara urut, oleh sebab itu sebagai amannya menghindari salah mencari data pakai nila false(0)
  Contoh di atas memakai nilai false, jika nilai yang kita cari tidak menemukan dari Table_array, nilai hasil akan menjadi #N/A

Gambar 01

Lihat contoh di atas (Gambar 01) Item tahu sebagai contoh, Lookup_value adalah tanggal delivery yaitu 5, dan mengambil data dari table delivery bulan Maret (Table_Array). Dan Row_index_num adalah Tahu yaitu baris ke 3. Dan Range_Lookup kami ambil angka 0, karena untuk menghindari kesalahan dalam pengambilan data.

Menghitung umur dengan DATEDIF

Download Contoh Excel menghitung umur dengan DATEDIFl Cara menghitung umur dengan fungsi excel DATEIF, bisa menghitung sampai ke harinya. Contoh di bawah menghitung umur dihitung per tanggal 2 Mei l 2009. Menghitung umur dengan DATEDIF Y, Menghitung selisih tahun dengan pembulatan tahun ke bawah. Contoh Tanggal lahir 2 April 2001, maka akan menghasilkan angka 8 (tahun) Tanggal lahir 2 Juni 2001, maka akan menghasilkan angka 7 (tahun), karena belum genap 8 tahun M, Menghitung selisih bulan dengan pembulatan bulan ke bawah, cocok untuk menghitung umur bayi. Contoh Tanggal lahir 2 April 2008, maka akan menghasilkan angka 13 (bulan) Tanggal lahir 2 Juni 2008, maka akan menghasilkan angka 11 (bulan) D, Menghitung selisih hari, terlalu detail jika untuk menghitung orang tua, Contoh Tanggal lahir 2 April 2009, maka akan menghasilkan angka 30 (hari) Tanggal lahir 2 Maret 2009, maka akan menghasilkan angka 61 (hari) YM, menghitung bulan setelah dikurangi pembulatan tahun, tidak pernah menghasilkan angka sama atau lebih besar dari 12, Contoh Tanggal lahir 2 April 2001, maka akan menghasilkan angka 1 (bulan) Tanggal lahir 2 Juni 2001, maka akan menghasilkan angka 11 (bulan) YD, menghitung hari setelah dikurangi pembulatan hari dalam 1 tahun, maximal menghasilkan angka 365 (hari), Contoh Tanggal lahir 3 May 2008, maka akan menghasilkan angka 364 (hari) Tanggal lahir 1 May 2008, maka akan menghasilkan angka 1 (hari), karena sudah melewati 1 tahun Tanggal lahir 2 May 2008, maka akan menghasilkan angka 0 (hari), karena tepat 1 tahun MD, menghitung hari setelah dikurangi pembulatan tahun dan pembulatan bulan, Contoh Tanggal lahir 12 April 2009, maka akan menghasilkan angka 20 (hari) Tanggal lahir 12 Maret 2009, maka akan menghasilkan angka 20 (hari) juga, arti 1 bulan 20 hari Untuk menghitung Umur sampar ke harinya dengan kombinasi parameter yang kami warnai biru tebal, Y, YM, MD, sehinggan menghasilkan rumus dengan fungsi sbb : =DATEDIF(tgl lahir,sekarang,"Y")&" Tahun, "&DATEDIF(tgl lahir,sekarang,"YM")&" Bulan, " & DATEDIF(tgl lahir,sekarang,"MD")&" Hari" Untuk lebih jelas dan mudah dimegerti download saja contoh file excelnya dengan link paling atas.

Menghitung dan mengurutkan berdasar ranking

Download Contoh Excel Menghitung dan mengurutkan berdasar ranking Untuk mencari ranking nilai ujian, Si A menduduki urutan ke berapa di dalam excel bisa dengan mudahmencarinya karena sudah ada fungsi RANK(). Contoh (Gambar 01) di bawah, jika hanya mencari rankingnyamemakai fungsi excel RANK() saja sudah cukup. Permasalahannya contoh di bawah menghasilkan ranking dengan nilai yang sama, yaitu nilai rangking 2 muncul 2 kali. Gambar 01 Menghitung ranking Untuk menjadikanhasil RANK nya menjadi unik (hanya muncul sekali), rumus di atas harus dikombinasikan dengan fungsi COUNTIF mencari berapa kali nilai tersebut muncul, fungsi COUNTIF di jika hanya 1 angka yang ada akan menghasilkan nilai 1, oleh sebab itu harus dikurangi dengan angka 1 di bagian akhir. Contoh (Gambar 02) di bawah sudah bisa menghasilkan hanya ada 1 ranking yang muncul, dengan asumsi urutan pertama akan mendapatkan rangking lebih atas. Warna biru gambar di bawah sudah menjadi angka yang unik, tidak muncul 2 kali. Gambar 02 Menghitung ranking yang unik saja Hasil akhir dari perhitungan di atas sudah bisa untuk menentukan urutan nilai ranking dari yang paling besar ke kecil. Pertama menentukan nilai ranking tiap nama, selanjutnya bisa untuk menentukan nilai ranking secara urut dengan gabungan fungsi excel INDEX & MATCH. Untuk lebih jelasnya silakan download contoh excel yang kami buat dari link di atas. Gambar 03 Mengurutkan Ranking

SUM, SUMIF, SUMPRODUCT

Download eBook SUM, SUMIF dan SUMPRODUCT dan Contoh Excel

Fungsi excel SUM pengganti penjumlahan, SUMIF digunakan untuk penjumlahan terkondisi, sedankan SUMPRODUCT gabungan dari pemjumlahan, perkalian dan array.

• Fungsi : SUM(number1, [number2], [number3], [number4], …)
• Fungsi : SUMIF(range, criteria, [sum_range])

Arti yang didalam kurung [] adalah optional, bisa diisi atau dikosaongkan.
Contoh pada gambar 01

• Fungsi yang dipakai =SUM(C3:C6)
  bisa juga fungsi tersebut diartikan sebagai =C3+C4+C5+6
• Fungsi yang dipakai =SUMIF(B3:B6,”Senin”,C3:C6)
  artinya jumlahkan antara C3 s/d C6 jika sebaris dengan B3 s/d B6 dengan nilai “Senin”
  Penulisan “Senin” harus dengan tanda petik, jika tanpa petik akan menghasilkan nilai 0.
  Penulisan “Senin” bisa dengan huruf besar, kecil, besar kecil (”SENIN” atau “senin” atau “SeNin”)

Gambar 01

Fungsi SUM juga bisa dipakai lebih dari satu array.
Gambar 02 menggunakan rumus =SUM(B3:C6)
arti : B3+C3+B4+C4+…+C6

Gambar 02

Fungsi SUM dengan menggunakan 2 array yang terpisah.
Gambar 03 menggunakan fungsi =SUM(B3:B6,D3:D6)
arti : B3+B4+…+B6+D3+D4+…+D6

Gambar 03

Berbeda dengan Fungsi SUM, SUMPRODUCT adalah kombinasi penjumlahan dan perkalian.

• Fungsi : SUMPRODUCT(array1,[array2],[array3], …)

Contoh pada gambar 04, fungsi yang dipakai =SUMPRODUCT(B$1:C$1,B4:C4)
Artinya B1 X B4+C1 X C4, dengan menggunakan SUMPRODUCT pengganti perkalian dalam bentuk Matrik.

Gambar 04

Mengitung bilangan positive/negtive saja dengan SUMIF & COUNTIF

Download Contoh Excel Download Contoh Excel SUMIF dan COUNTIF

Ada kalanya kita harus mengitung berapa jumlah bilangan yang positive saja dan berapa jumlah bilangan yang negative saja. Jika bertemu dengan masalah tersebut, kita gunakan saja rumus excel

• SUMIF(range, criteria, [sum_range])
  [sum_range] di dalam kurung [] berarti optional, boleh tidak diisi.
  fungsi di bawah adalah =SUMIF(A2:A21,”>0?)
  Artinya Jumlahkan array dari A2 sampai A21 yang nilainya lebih besar (>) dari 0
  tanda >0 harus didalam tanda petik, yaitu “>0?
  Untuk contoh lebih kecil (<) dari 0, cukup mengganti criteria dengan “<0?
• COUNTIF(range, criteria)
  Pemakaian COUNTIF sama dengan contoh SUMIF di atas
  Untuk mencari nilai di atas kosong (0), isi criteria dengan “>0?
  Begitu juga dengan dibawah kosong, criteria denganti dengan “<0?

Gambar 01

VLOOKUP row tidak sejajar, memakai INDEX MATCH

Download Contoh Excel VLOOKUP row tidak sejajar Definisi fungsi VLOOKUP dari Microsoft Office Online menyebutkan bahwa VLOOKUP untuk mencari nilai mulai dari kolom pertama dari sebuah table array dan menghasilkan nilai dari row yang sama dari kolom yang lain pada table array. Artinya (V vertikal) VLOOKUP harus dipakai untuk mencari dari row yang sejajar yang berada di sebelah kanannya. Bagaimana jika ROW tidak sejajar? Solusinya memakai fungsi INDEX & MATCH. Perhatikan kasus di bawah. Contoh di atas, Dengan code akan mencari nana Item dan dengan code akan mencari Harga/kg. Contoh pertama sebanarnya bisa dicari dengan VLOOKUP, tapi kali ini kami akan memakai fungsi Index dan MATCH yang bertujuan nanti akan dimodifikasi ke dalam bentuk rumus excel untuk mencari dengan row yang tidak sejajar. Untuk mencari nama Item rumus yang digunakan : =INDEX(A2:B15,MATCH(D2,A2:A15,0),2) Sedangkan untuk mencari Harga/kg rumus excel yang digunakan : =INDEX(A2:B15,MATCH(D5,A2:A15,0)+1,2) Perhatikan pada rumus excel yang kedua angka satu yang kami beri tanda tebal dan warna biru tua, untuk mengambil data 1 timgkat di bawah dari row kami tambahkan angka 1, jika 2 tingkat dari row yang ada tambahkan angka 2 dst.