|
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 + & |
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.
- Fungsi PI(), yaitu angka 3.142…
- Mengambil referensi dari cell A2
- Konstan dengan nilai 2
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.
=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. 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. 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. |
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”)
Fungsi SUM juga bisa dipakai lebih dari satu array.
Gambar 02 menggunakan rumus =SUM(B3:C6)
arti : B3+C3+B4+C4+…+C6
Fungsi SUM dengan menggunakan 2 array yang terpisah.
Gambar 03 menggunakan fungsi =SUM(B3:B6,D3:D6)
arti : B3+B4+…+B6+D3+D4+…+D6
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.
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?
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 :
Sedangkan untuk mencari Harga/kg rumus excel yang digunakan :
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. |
saya mau tanya. kalau membuat rumus excel dengan pengali banyak bnget..
BalasHapuscontoh nya :
harga kolom tanggal 1-31(periode 1 bln)
123.456 =brp
123.678 =brp
123.897 =brp
dan kolom nya lebih dari 3 kolom. dan kl satu per satu pasti ribet harus manual kolom A1 * kolom B2 misalkan.dan hasil pengaliannya dijumlahkan.
tlng dibantu y bos..!
tx
aku butuh sum product disatukan dengan formula countifs, bisa gak ya...
BalasHapusharusnya bisa, tp index untuk menyatakan nilai dari hasil sum product itu blm diketahui,
jika ada jalan, mohon di share
TQ
contoh nya
BalasHapus=SUMPRODUCT(--(LEFT('Pekerjaan Tahun 2012'!D4:D417,1)=B4)[ini yg belum diketahui, index]countifs'Pekerjaan Tahun 2012'!H4:H417,"rutin",'Pekerjaan Tahun 2012'!R4:R417,"received")