Microsoft 2013: Fungsi SUMIFS (Menjumlah dengan beberapa syarat/kriteria)

Tutorial sebelumnya telah kita bahas fungsi =SUMIF() tuk menjumlah angka dengan 1 syarat (1 criteria), ada pertanyaan yang muncul dari pembaca. Bagaimana kalo menjumlahkan angka dengan beberapa syarat, mampukah fungsi =SUMIF() digunakan?
 

Gambar 1. Contoh Kasus

Tentu tidak! karena fungsi tersebut digunakan untuk penjumlahan dengan satu criteria.

Gambar 2. Data Awal yang Diinput

Wah klo cuman satu syarat sih gampang, mungkin user akan berfikir gitu, tapi jangan putus asa dulu karena kayaknya pihak pembuat Microsoft Office sudah menerka bahwa pertanyaan ini bakal muncul setelah seseorang mempelajari fungsi =SUMIF()

 Mereka telah menyiapkan fungsi yang lain yaitu fungsi =SUMIFS() untuk menyelesaikan penjumlahan dengan beberapa syarat. Bagaimana syntaxnya, ikuti tutorial berikut ini!

Syntax:
=SUMIFS(sum_range; criteria_range1; criteria1; [criteria_range2; criteria2]; ...)

Keterangan:
Sum_range (harus ada)
Barisan cell yang akan dijumlah
Criteria_range1 (harus ada)   
Barisan cell yang akan diuji dengan Criteria1
Criteria1 (harus ada)      
Syarat/criteria untuk menguji "Criteria_range1" yang akan dijumlah.
Criteria dapat diinput sebagai 32, ">32", B4, "jeruk", or "32"

Gambar 3. Laporan yang Diinginkan

Misalkan di perusahaan Anda memiliki bentuk inputan seperti Gambar 2., kemudian Anda diminta membuat laporan seperti Gambar 3.. Apa yang harus Anda lakukan?

Akankah Anda input ulang, jika data yang diinput hanya sedikit tentu tidak membutuhkan waktu yang lama, akan tetapi jika ada ratusan input pastilah akan menghabiskan waktu kerja Anda.

Pada saat itulah Anda wajib berfikir praktis & efisien, fungsi =SUMIFS lah yang akan menjadi penolong bagi Anda.






Solusi kasus di atas bisa di lihat seperti Gambar 4. 

Gambar 4. Rumus Yang Diinput

LKS :  
=SUMIFS($D$6:$D$17;$C$6:$C$17;$G$6;$B$6:$B$17;H6)

PG :     
=SUMIFS($D$6:$D$17;$C$6:$C$17;$G$10;$B$6:$B$17;H10)

KORAN : 
=SUMIFS($D$6:$D$17;$C$6:$C$17;$G$14;$B$6:$B$17;H14)


PENJELASAN RUMUS:

$D$6:$D$17
Barisan Cell yang akan dijumlahkan dengan beberapa criteria

$C$6:$C$17
Barisan cell yang akan diuji dengan Criteria1

$G$6
Criteria yang pertama

$B$6:$B$17
Barisan cell yang akan diuji dengan Criteria2

H6
Criteria yang kedua

Catatan:
H6 tidak diapit dengan tanda $ agar saat di copi ke cell dibawahnya akan berubah menjadi H7, H8, dst.
Pelajari juga cara mengunci cell, cara mengunci baris, maupun cara mengunci kolom


Selamat Mencoba Semoga Bermanfaat

Microsoft 2013: Fungsi SUMIF (Menjumlah dengan syarat/kriteria)

Ada suatu kasus yang cukup menggelitik di sebuah perusahaan antah brantah "tapi ini rahasia lho" gak boleh disebar luasin. Ini ada kaitan dengan penggunaan rumus di Microsoft Excel, klo penulis pikir ini bukan karena excelnya yang bodo lho, tetapi karena mungkin user yang udah merasa jago menggunakan excel. Kadang kita udah merasa ahli menggunakan program buatan microsoft ini meski sebenarnya masih banyak fungsi yang belum kita fahami secara detail.

Gambar 1. Laporan Piutang CV. Amburadul Bae

 

Misal kita memiliki laporan piutang seperti gambar 1 "Silahkan anda buat laporan di microsoft excel seperti yang terlihat pada gambar tersebut" sebagai latihan  untuk aplikasi tutorial kali ini. Perhatikan juga gambar 2, pada gambar tersebut terlihat rumus yang harus anda input di cell-cell yang ada.

Gambar 2. Rumus yang harus diinput tiap cell

Kalo rumus kayak gitu buat admin dan accounting atau bagian pajak mah pasti udah di luar kepala. Sekarang, coba perhatikan gambar 1 (cell N13) terlihat angka 7.185.000, Kalo diperhatikan sih kayaknya wajar-wajar aja tetapi kalo orang accounting atau pajak mesti melihat ketidakakuratan data "itu mungkin lho". Hal ini terjadi karena ada angka minus (-500.000 dan -111.000) alias surplus mungkin mereka bilang. Seharusnya angka minus itu harus diperlakukan sebagai hutang bukan lagi piutang "itu juga mungkin lho, kan penulis juga cuman ngawur".

Nah, ini yang terjadi di perusahaan antah brantah "katanya sih ada perintah untuk menghapus angka minus tersebut biar jumlah angkanya jadi bener "bener-bener mencerminkan angka piutang real". Lha trus angka-angka itu dikemanain? mungkin angka nolnya minta bantuan Pak Tarno tuk menghilangkannya dengan jampi-jampi "mohon dibantu ya (wakakaka)".

Oke kita kepokok permasalahan sesungguhnya, coba kembali perhatikan cell N13 disitu terlihat rumus =SUM(N7:N12) otomatis microsoft menjumlah seluruh angka yang ada termasuk minusnya, sehingga hasil angkanya jadi salah "miss kata para profesional dadakan"


Kali ini penulis akan mencoba menggunakan fungsi SUMIF untuk memecahkan kasus menghilangnya angka minus di atas, siapa tau ini jadi solusi pengganti pak tarno ":)". Oke langsung aja kita belajar syntaxnya biar tidak ghibah terlalu lama.

RUMUS:
=SUMIF(range;criteria;[sum_range])

KETERANGAN:
Range - Merupakan sejumlah cell yang akan dianalisa menggunakan kriteria. Cell harus diisi dengan angka, atau nama, ataupun array, bisa juga data tanggal

Criteria -  Kriteria merupakan syarat yang harus terpenuhi. Kriteria bisa seperti ">32", nama cell tertentu (B5), "32",  "imam", atau fungsi TODAY()

Sum_Range - Cell aktual yang akan dijumlahkan. Sum_Range bisa di cell yang sama bisa juga bisa juga di tempat/cell yang berbeda, sebagai contoh kasus di atas maka sum_range nya sama dengan range karena yang akan kita jumlah hanya yang lebih besar dari nol ">0".

Maka rumus yang akan kita ketik pada cell N13, seperti ini:
=SUMIF(N7:N12;">0";N7:N12)

Gambar 3. Hasil akhir yang dikehendaki

Selamat mencoba, semoga gak butuh pak tarno lagi tuk menghilangkan angka :)
Coba juga seandainya kita ingin menjumlah cell N7:N12 dengan kriteria area pemasaran/nama sales.
Jika belum jelas bisa twit ke @zahidbookstore atau
email ke zahidonlinestore@gmail.com
see u next tutorial....

Cara Menghitung Masa Kerja dengan Excel 2010

Tutorial kali ini, penulis ingin berbagi ilmu yang simple tetapi mungkin tidak lazim digunakan oleh user Microsoft Excel yang bekerja di bagian pemasaran atau penjualan bahkan accounting. Fungsi ini sering berkaitan dengan bagian HRD.

Seorang karyawan yang bekerja di bagian HRD membutuhkan fungsi perhitungan masa kerja untuk penetapan atau penentuan gaji, tunjangan, fasilitas, promosi dan mungkin juga dalam penentuan besarnya bonus yang akan diterima karyawan.

Ilustrasi Data Karyawan

Asumsi "Nindita Arum Sari bekerja di PT. Harapan Maju & Berkembang sejak tanggal 1 November 2004, Hari ini staf HRD ingin menghitung masa kerja karyawan tersebut." Haruskah kita menghitung manual? Kalo seperti ini, pastilah membutuhkan waktu dan tenaga yang cukup banyak. Apalagi kalo pimpinan meminta kita untuk menghitung masa kerja seluruh karyawan. Bayangkan kalau karyawan perusahaan ada 500 orang, berapa waktu yang dibutuhkan jika satu karyawan kita butuh 5 menit?

Microsoft Excel telah menyediakan rumus yang simple tetapi sangat membantu user untuk menghitung masa kerja, sehingga staf HRD tidak perlu lagi menghitung secara manual

D6:
=DATEDIF(C6;TODAY();"Y") 

E6:
=DATEDIF(C6;TODAY();"YM") 

F6:
=DATEDIF(C6;TODAY();"MD")


Keterangan:
C6               : berisi tanggal masuk atau gabung dengan perusahaan
TODAY()    : fungsi excel untuk mengambil tanggal hari ini dari komputer
"Y"              : untuk menghitung tahun
"YM"           : untuk menghitung bulan
"MD"           : untuk menghitung hari

Untuk menghasilkan angka : 11 Tahun 3 Bulan 15 Hari, gunakan fungsi CONCATENATE lihat tutorial "Microsoft Office 2007: Menggunakan Fungsi Concatenate"  

D23:
=CONCATENATE(DATEDIF(C6;TODAY();"Y");" Tahun ";DATEDIF(C6;TODAY();"YM");" Bulan ";DATEDIF(C6;TODAY();"MD");" Hari ")

Semoga bermanfaat...

Microsoft Excel 2007: Fungsi ROUNDUP

Tabel Hasil yang Diinginkan

Pagi ini ada selembar kertas pamflet pinjaman modal dari BRI tergeletak di meja kantorku, sesaat aku tertegun dengan angka-angkanya. Ada sesuatu yang aneh terlihat bagiku.

Iseng-iseng aja aku buka Microsoft Excel 2007, dan kubuat tabel seperti di samping, setelah rumus aku masukan hasilnya seperti tabel bagian atas (sebelum pembulatan).

Aku kembali berpikir gimana caranya ya biar tampilannya seperti tabel bagian bawah (sesudah pembulatan). Sebelumnya yang aku pahami pembulatan di belakang koma memakai fungsi ROUND, kalau ini kan di depan koma (decimal) pake apa ya?

Kubuka fasilitas help (F1) di aplikasi Microsoft Excel dan kutemui beberapa fungsi pembulatan, sebagai berikut: ROUND, ROUNDUP, ROUNDDOWN, MROUND.

Rumus ROUNDUP

Kali ini aku ingin mencoba menerapkan fungsi ROUNDUP untuk membulatkan angka 93.583 menjadi 93.600. Silahkan perhatikan gambar di samping agar lebih jelas.

Syntax Fungsi ROUNDUP:
ROUNDUP(number,num_digits)

Aplikasi Fungsi ROUNDUP:
=ROUNDUP(93583;-2) untuk pembulatan ratusan
hasil yang diperoleh 93600

=ROUNDUP(93583;-3) untuk pembulatan ribuan
hasil yang diperoleh 94000

Semoga bermanfaat
Terima kasih telah membaca :)

Microsoft excel 2007 : Menghapus recent document

Selamat bertemu kembali dengan zahidonlinestore, tak terasa kita telah berada di ujung tahun 2011. Di akhir tahu ini saya ingin memberikan sebuah tutorial yang mudah dan sederhana , meski demikian kadang ini terasa sulit bagi mereka yang tidak mengetahuinya. Seorang sahabat saya dating ke ruangan kerja saya untuk menanyakan cara menghapus recent document di Microsoft Excel 2007.

Sesungguhnya Microsoft Excel 2007 memberikan fasilitas ini untuk mempermudah user dalam mencari file yang sering dia buka “dokumen terakhir” yang dibuka oleh user. Biasanya default yang diberikan oleh Microsoft Office adalah 17 file terakhir yang kita buka. Fasilitas yang seharusnya memudahkan ini  kadang tidak dibutuhkan oleh user karena khawatir kalau file yang dia buka dapat dengan mudah dibuka oleh user lain yang memakai komputernya.

Singkat cerita, saya harus membuatkan tutorial cara menghapus recent document. Selamat mempraktekan langkah-langkahnya, moga aja rekan-rekan semua tidak kesulitan mengikutinya

Download tutorial cara menghapus recent document klik disini

Microsoft Office 2007: Menggunakan Fungsi Floor

Tak terasa Tabungan Rencana Qurban (TRQ) yang diadakan DarulQolam harus dibagikan pada anggota yang ikut menabung, moga semangat berqurban dari rekan anggota DarulQolam untuk berkurban tiap tahun dapat terlaksana.

Wah, kayak aplikasi gaji tiap bulan nih!! Aku jadi bingung karena tidak pernah menggaji karena aku juga bukan seorang HRD tapi hanya sekedar IT dadakan, tak ayal lagi aku harus browsing.

Kira-kira pake fungsi apa ya biar dapat membagi uang para anggota DarulQolam dengan lebih mudah, alias aku memerlukan uang 100.000, 50.000, 20.000, 10.000, 5.000 dan 1.000 (masing-masing berapa lembar).

Akhirnya aku temui fungsi FLOOR di Microsoft Excel 2007, untuk lebih jelasnya ikuti langkah berikut:

1. Letakkan kursor di cell kolom I4 dan ketik rumus =FLOOR($D4;100000);
2. Letakkan kursor di cell kolom J4 dan ketik rumus =FLOOR($D4-SUM($I4);50000)
3. Letakkan kursor di cell kolom K4 dan ketik rumus =FLOOR($D4-SUM($I4;$J4);20000)
4. Letakkan kursor di cell kolom L4 dan ketik rumus =FLOOR($D4-SUM($I4;$J4;$K4);10000)
5. Letakkan kursor di cell kolom M4 dan ketik rumus =FLOOR($D4-SUM($I4;$J4;$K4;$L4);5000)
6. Letakkan kursor di cell kolom N4 dan ketik rumus =FLOOR($D4-SUM($I4;$J4;$K4;$L4;$M4);1000)

Penjelasan:
Langkah no 1. 2, 3, 4, 5, 6 merupakan rumus yang digunakan untuk mencari berapa jumlah uang yang dibutuhkan dengan pecahan ratusan ribu, lima puluh ribu, dua puluh ribu, sepuluh ribu, lima ribu dan seribu.

Jika uang sebesar 370.000 (D5), maka dibutuhkan:
1. 300.000 : 100.000 = 3 lbr
2.  50.000 :  50.000 = 1 lbr
3.  20.000 :  20.000 = 1lbr

Selamat mencoba, moga sedikit yang saya sampaikan ini ada manfaatnya buat kita semua.....

Microsoft Office 2007: Menggunakan Fungsi Concatenate

Berikut akan kita lanjutkan tutorial kita mengenai fungsi yang berhubungan dengan "text". Sebelumnya kita telah membahas fungsi Left, Right & Mid, kali ini kita akan membahas fungsi Concatenate.

Formulanya :
=concatenate(text1;text2;...)

Fungsinya:
Menggabungkan berberapa text menjadi satu text baru, biasanya digunakan untuk pengkodean.

Misal ada tabel seperti di bawah ini:


untuk mendapatkan hasil seperti pada kolom G4, G5 dan G6 ketik formula berikut:
=concatenate(D4;"-";E4;"-";F4)
=concatenate(D5;"-";E5;"-";F5)
=concatenate(D6;"-";E6;"-";F6)