[Belajar Excel] Menggunakan COUNTIFS

Beberapa waktu yang lalu, saya disibukkan dengan agenda mempersiapkan dokumen untuk keperluan akreditasi prodi di kampus saya. Salah satu dokumen yang harus disiapkan adalah merekap jumlah pengunjung per prodi dan dipisahkan antara laki-laki dengan perempuan. Dahulu kala dengan jumlah mahasiswa yang tidak banyak saya mengolah data yang ada menggunakan excel namun manual :), manual gimana maksudnya? ya manual tanpa menggunakan rumus dan fungsi. Hmm ga usah dijabarkanlah jadi malu nanti. Nah pada kesempatan akreditasi ini data yang diolah cukup banyak dan ada 4 prodi, puyeng dong apalagi diambil 3 tahun terakhir.

Setelah berpikir, googling sana sini, baca buku excel sana sini dengan didukung oleh Power of Kepepet akhirnya paham juga saya bagaimana harus mengolah data tersebut. Secara sederhana seperti gambar di bawah iniNah saya ingin menghitung jumlah mahasiswa akuntansi secara rinci laki-laki dan perempuan berapa orang, demikian juga mahasiswa manajemen laki-laki dan perempuan ada berapa orang. Nah ketika jumlah data masih sedikit mudah untuk mengolahnya, bagaimana bila sudah mencapai ratusan? bahkan ribuan? Dari contoh gambar di atas saya ingin hasilnya seperti ini

Untuk mendapatkan hasil seperti pada gambar diatas, kita dapat memanfaatkan fungsi COUNTIFS pada excel. Ada 2 cara dalam menggunakan fungsi COUNTIFS ini.

fungsi countifs sendiri bentuknya adalah =COUNTIFS(range1,kriteria1,range2,kriteria2,…) pada contoh yang saya berikan kriteria pertama itu adalah akan memisahkan laki-laki dengan perempuan. Laki-laki dan perempuan terletak pada Cel C1 s.d. Cel C8 sementara C10 adalah Tabel dimana huruf “L” yang digunakan sebagai simbol laki-laki berada, sedangkan Cel B1 s.d. Cel B8 adalah letak prodi manajemen dan akuntansi berada. B11 merujuk pada Manajemen.

Dengan pola fungsi yang sama dengan mengganti letak Cel untuk ‘P” dan “Akuntansi” maka kita akan dapat memperoleh hasil yang diinginkan.

Variasi lain dalam menggunakan fungsi ini adalah :

Cel C10 kita ganti dengan huruf “L” sedangkan Cel B11 kita ganti dengan kata “Manajemen”.Dengan cara yang sama dan disesuaikan dengan kebutuhan maka dapat dihitung jumlah mahasiswa manajemen berkelamin perempuan serta mahasiswa akuntansi berjenis kelamin laki-laki dan perempuan. Susunan range 1, kriteria1 dengan range2,kriteria2 juga dapat dibolak-balik yang penting sesuai dengan kaidah penulisan fungsi countifs yang telah ditentukan.

Untuk mempermudah dalam menyalin fungsi ada baiknya Column Letter dan Row Number yang digunakan diberi tanda $. Tanda $ akan membuat sebuah cel dikunci dan ketika akan disalin ke posisi cel lain tidak akan berubah. Setelah range1 yang diikuti oleh kriteria 1, range 2 .. kriteria 2 dapat dipisahkan dengan tanda koma (,) atau titik koma (;) ini tergantung dengan settingan komputer (khususnya area dan bahasa: apakah menggunakan Indonesia, Amerika, Inggris dlsb) dan versi excel yang digunakan.

Selamat mencoba! Bila tidak bisa ya dicoba terus sampai bisa, karena saya tidak bisa excel maka saya bisanya ya karena learning by doing, coba gagal coba gagal akhirnya bisa dan paham oh gini to caranya, tibak’e gampang 🙂

[Belajar Excel] Menggunakan fungsi IF untuk Formula

Kesekian kalinya saya menulis lagi mengenai excel, tulisan ini juga karena hasil pengalaman yang bikin ruwet. Intinya adalah saya harus menggolongkan atau memberikan predikat kepada suatu nilai/hasil dengan predikat-predikat seperti kurang, cukup, baik, baik sekali dengan ketentuan yang sudah ditetapkan. Kepala cukup pusing dan menjadi bingung, namun berkat mbah google akhirnya menemukan cara yang dibutuhkan kala menggunakan excel.

Contohnya seperti gambar excel di bawah ini

Dari sheet excel diatas, setiap nama dengan hasil skor TOEIC diberi suatu predikat/keterangan dengan ketentuan sebagai berikut:

Jika nilai TOEIC kurang dari 50 maka Predikat Kurang

Jika nilai TOEIC antara 51 s.d. 70 maka Predikat Cukup

Jika nilai TOEIC antara 71 s.d. 85 maka Predikat baik

Jika nilai TOEIC di atas 85 maka Predikat Baik Sekali

Hasilnya yang diharapkan adalah seperti gambar berikut ini:

Untuk memecahkan masalah ini, maka digunakanlah fungsi IF yang tentunya dikembangkan menurut kaidah di excel agar dapat berjalan. Fungsi if ini digunakan dalam rangkaian formula. Maka pemecahan masalahnya adalah sebagai berikut

=IF(B2<=50,”Kurang”,IF(AND(B2>=51,B2<=70),”Cukup”,IF(AND(B2>=71,B2<85),”Baik”,”Baik Sekali”)))

B2 disini adalah posisi cell dimana nilai yang akan diberikan sebuah predikat berada. Cukup membuat sebuah formula kemudian tinggal dicopypastekan ke baris selanjutnya dari sebuah kolom predikat. Tanda koma (,) yang digunakan mungkin pada beberapa komputer harus diganti menjadi titik koma atau semicolon (;) agar formula dapat berjalan dengan baik.

[Belajar Excel] Angka Nol di depan Angka pada Cell

Saya sebenarnya tidak terlalu bisa atau menguasai excel karena terus terang sangat jarang menggunakannya. Beberapa waktu yang lalu ketika menuliskan isian angka yang didepannya terdapat angka nol (0) bila dituliskan ke cel maka angka tersebut menjadi hilang. Sebenarnya ada penyelesaiannya yaitu format cell dirubah menjadi text namun saya kurang yakin bila berupa text apakah bisa dijumlahkan?

Setelah mencari jawaban ke sana kemari akhirnya didapat jawaban seperti gambar berikut:

maksud hati adalah ingin terdapat angka nol seperti pada gambar khususnya di cell NIM, sebagai solusi adalah silahkan menuju

1. Home tab, menuju Cell grup pilih Format kemudian pilih Format cell

2. Pilih Custom kemudian ketikkan angka nol (nol) sebanyak yang diinginkan misalkan NIM pada contoh terdapat ada 11 angka maka isikan angka nol (0) sebanyak 11 kali seperti pada gambar berikut

Bila telah dimasukkan maka tinggal tekan Ok maka bila kita entry angka yang didepannya terdapat angka nol (0) maka angka nol tersebut tidak akan menghilang 🙂

selamat mencoba