Showing posts with label EXCEL. Show all posts
Showing posts with label EXCEL. Show all posts

Saturday, March 8, 2014


Daripada ini blog belum ada update-an, dan daripada ini draft sampai berkarat di dashboard mending di-publish aja deh. H-hee...

Sum, Product dan SumProduct adalah beberapa fungsi yang terdapat pada program excel. Meski namanya agak-agak mirip, namun ketiganya memiliki "tugas" yang berbeda.


Apa bedanya fungsi Sum dan Product?


Secara singkatnya, Sum berarti menjumlahkan sedangkan Product berarti mengalikan. Jadi jika kita menuliskan misalnya,

=Sum(1,2,3)

Maka artinya adalah 1 + 2 + 3.

Sedang jika kita menuliskan,

=Product(1,2,3)

Maka ini berarti 1 x 2 x 3.


Lalu apa kegunaan fungsi gabungan SumProduct?


Kegunaan fungsi ini adalah untuk menjumlahkan setelah mengalikan sebuah data numerik. Jika kita memiliki beberapa barang sejumlah y dengan harga satuan z, maka hasil dari perkalian masing-masing barang tersebut bisa langsung kita jumlahkan dengan menggunakan fungsi SumProduct.

Lebih jelasnya kita bisa melihat hubungan antara Sum, Product dan SumProduct dari ilustrasi di bawah ini:

Sum, Product, SumProduct
Hubungan Sum, Product dan SumProduct

Dari ilustrasi di atas, jika nilai pada kolom E sudah diketahui, kita bisa menjumlahkan totalnya dengan menggunakan fungsi Sum.
Akan tetapi jika nilai di kolom E tidak diketahui, kita tetap bisa mendapatkan total 6.750 dengan memakai fungsi SumProduct.


Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Saturday, March 08, 2014

Sunday, March 2, 2014


Cukup lama juga blog ini tidak berbagi tentang tips-tips tentang excel. Karena itu, sebagai postingan pembuka di bulan Maret ini ada cara sederhana untuk mengerjakan penjumlahan dengan menggunakan satuan di belakangnya.

Untuk melakukan penjumlahan dengan menggunakan satuan, misalnya Pcs, biasanya kita membuat kolom lain di sebelahnya. Misalnya seperti gambar di bawah ini:

Penjumlahan dengan satuan
Penambahan kolom baru

Penambahan kolom baru itu memang kita lakukan karena jika kita langsung menambahkan satuan Pcs pada sel yang berisi angka akan berdampak angka tersebut berubah sifat menjadi text, sehingga tidak bisa dijumlahkan.

Namun, dengan sedikit tips ini, kita bisa dengan leluasa menjumlahkan sederetan angka dengan tetap menampilkan satuan di belakangnya.

Caranya sebagai berikut:

  1. Pada sel yang berisi angka, klik kanan, pilih 'Format Cells'
  2. Pada Category, pilih 'Custom' dan pada Type pilih 'General'
  3. Tambahkan satuan "Pcs" (dengan tanda kutip) di belakang kata 'General', sehingga menjadi General "Pcs"
  4. Klik 'Ok'
Nah, kita bisa mengulang caranya pada sel di bawahnya, atau melakukan dragging (geser) ke bawah.

Menggunakan format cells
Menambahkan satuan di belakang angka

Maka ketika kita mengetikkan sejumlah angka pada sel tersebut, otomatis akan langsung muncul satuan 'Pcs' di belakangnya. Dan angka ini masih bersifat number sehingga tetap bisa dijumlahkan.

Semoga bermanfaat.
Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Sunday, March 02, 2014

Thursday, November 7, 2013

ujungkelingking - Bila kita ingin menghitung berapa jumlah kata yang sudah kita tuliskan pada Ms. Word, tentu caranya sederhana saja karena sudah ada pengaturannya di sana. Kita tinggal masuk ke menu Tools kemudian pilih Word Count, maka dari jumlah kata sampai jumlah karakter atau baris langsung bisa muncul.

Menghitung jumlah kata pada Ms. Word


Menghitung jumlah kata pada Ms. Excel bisa dibilang gampang-gampang susah, sebab tidak disediakan fungsi atau rumus untuk itu. Satu-satunya fungsi yang "mirip" kegunaannya adalah fungsi LEN. Namun fungsi ini hanya menghitung jumlah karakter, bukan kata.

Sebenarnya tips kali ini tidak penting-penting amat. Apa perlunya, coba, menghitung jumlah kata pada excel? Karena itu Anda tidak perlu mempelajarinya, karena tips kali ini hanya sekedar untuk memuaskan rasa ingin tahu saya saja. Tapi tetep harus komen ya! *Maksa.com.

Untuk dapat menghitung jumlah kata yang sudah kita ketikkan pada excel, kita membutuhkan beberapa "komponen" pendukung. Selain LEN, komponen yang saya maksudkan itu adalah: SUBSTITUTE dan TRIM. Kegunaan masing-masing dari fungsi ini adalah sebagai berikut:
  • LEN, seperti yang sudah saya singgung di atas, fungsinya adalah untuk menghitung jumlah karakter. Dan yang disebut 'karakter' itu adalah setiap apa yang kita ketikkan pada papan keyboard kita.
  • SUBSTITUTE, dalam masalah kali ini digunakan untuk merubah setiap tanda baca menjadi spasi. Jadi nanti setiap ada tanda baca, (misalnya koma, titik, dsb.) akan kita ubah menjadi spasi terlebih dahulu. Sebenarnya fungsi ini tidak perlu kita gunakan bila cara kita menulis sudah benar sesuai EYD. Sebagai misal, sebelum tanda koma tidak perlu pakai spasi, setelah tanda koma harus pakai spasi, dll.
  • TRIM, fungsinya untuk membuang spasi yang berlebih. Diantara satu kata dengan kata berikutnya hanya perlu satu spasi, namun adakalanya kita keseleo jempol sehingga kita menekan spasi dua kali. Misalnya, kita menulis 'saya(spasi)(spasi)pergi', maka dengan TRIM kalimat tersebut akan berubah menjadi 'saya(spasi)pergi'.

Lalu bagaimana cara kita menerapkan ketiga fungsi ini?

Misalnya kita akan menghitung jumlah kata yang terdapat pada sel A1. Kalimat tersebut berbunyi: Maaf, apakah saya boleh pergi?. Maka pada salah satu sel masukkan fungsi ini:

=SUBSTITUTE(A1;",";" ")

Dengan fungsi ini kita mengubah tanda baca koma menjadi spasi. Lalu dilanjutkan dengan mengubah tanda baca tanda tanya menjadi spasi juga.

=SUBSTITUTE(A1;"?";" ")

Dan lakukan hal tersebut terhadap tanda baca yang lain (jika masih ada). Maka sekarang kalimat kita berbentuk seperti ini:

maaf(spasi)(spasi)apakah(spasi)saya(spasi)boleh(spasi)pergi(spasi)

Gunakan fungsi TRIM untuk membuang spasi yang tidak seharusnya ada. Dalam hal ini yang akan hilang nanti adalah spasi yang saya beri warna biru.

Akhirnya kalimat kita sudah memiliki susunan (format) baru, yaitu: 1-kata, 1-spasi, 1-kata, 1-spasi, 1-kata. Sepanjang apapun kalimat yang ada, formatnya akan tetap seperti itu, diawali dengan KATA dan diakhiri dengan KATA. Dengan kata lain, jumlah kata selisih 1 -lebih banyak- daripada jumlah spasi.

Pada keadaan ini kita masih akan kesulitan menghitung jumlah kata dalam kalimat tersebut. Hal ini dikarenakan 'karakter yang digunakan' dan 'jumlah karakter' pada tiap kata tidak tentu banyaknya. Namun, dari format baru di atas kita bisa tahu bahwa,

Jumlah Kata = Jumlah Spasi + 1

Jadi, yang kita perlukan sekarang hanyalah menghitung jumlah spasi yang ada, dan ditambah 1. Bisa kita tuliskan seperti ini:

=LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))+1

Cara bacanya: [Jumlah seluruh karakter, termasuk spasi] dikurangi [jumlah seluruh karakter tanpa spasi] akan menghasilkan [jumlah spasi]. [Jumlah spasi] ditambah [1] akan menghasilkan [jumlah kata].

Nah, sekarang ketahuan kan betapa gak pentingnya rumus ini. H-hee...

Untuk melihat tips-tips lain tentang excel, silahkan klik di sini. Semoga (yang lain) lebih bermanfaat!
Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Thursday, November 07, 2013

Wednesday, October 23, 2013

ujungkelingking - Pada artikel saya yang berjudul Download | Rumus Excel Terlengkap!, ada seorang teman yang sempat komen dan bertanya tentang cara menyelesaikan permasalahan excel yang sedang dihadapinya. Pertanyaan teman kita ini saya cuplikkan ulang saja, ya...

Saya punya database kapasitas produksi, misalnya untuk pengerjaan produk A, kapasitas harian 10 pcs, untuk produk B cuma jadi 5 pcs/hari, produk C cuma 2 pcs/hari. Untuk membuat database IF bagaimana caranya, misalkan ada order dari pelanggan 20 pcs untuk produk A, maka waktu yang dibutuhkan adalah 20/10 = 2 hari. dst.?

Secara singkat saya hanya bisa menjawab bahwa untuk menyelesaikan soal seperti itu saya lebih sreg memakai fungsi Vlookup-Hlookup daripada fungsi IF. Alasannya sederhana, dalam soal ini -karena jumlah data (produk) cuma sedikit- kita bisa saja menggunakan IF, tapi bagaimana bila jumlah produknya ratusan atau bahkan ribuan? Tentu kita akan semakin kesulitan mengaplikasikan fungsi IF ini. Karena itu, lebih aman bila kita menggunakan fungsi Vlookup-Hlookup. Lalu bagaimana penerapannya?

Untuk menyelesaikan soal ini kita membutuhkan dua filter. Filter pertama nanti akan berisi 'Jumlah Orderan', sedang untuk filter kedua akan berisi 'Jenis Produk', sehingga dari kedua filter ini bisa didapatkan hasil untuk 'Lamanya Waktu Pengerjaan'.

Lebih jelasnya perhatikan gambar berikut:

Dibutuhkan 2 filter (dok. pribadi)

Dari gambar di atas, pada sel C8 kita bisa isikan fungsi berikut:
=VLOOKUP(B8;A2:B4;2;0)

Fungsi ini sebenarnya hanya mengambil jumlah 'Kapasitas per Hari'. Karena pada sel B8 yang disebut adalah produk A, maka pada sel C8 menghasilkan 10 (10 pcs perhari).

Selanjutnya untuk menghasilkan 'Waktu yang Dibutuhkan' untuk mengerjakan perlu kita bagikan dengan 'Jumlah Orderan'. Maka di depan fungsi tadi, kita bisa tambahkan sel pembagi, yaitu sel A8, sehingga lengkapnya fungsi tersebut berbunyi,
=A8/VLOOKUP(B8;A2:B4;2;0)

Dengan demikian soal untuk excel kali ini sudah terselesaikan.

***

Lalu bagaimana bila saya memaksa untuk menggunakan fungsi IF?

Tentu boleh-boleh saja, namun seperti yang sudah saya singgung di atas bahwa rumusnya bisa bertambah panjang, tergantung jumlah 'Jenis produk'.

Sebagai contoh, dalam soal di atas kita bisa menggunakan fungsi IF sebagai berikut:
=IF(B8="A";A8/10;IF(B8="B";A8/5;IF(B8="C";A8/2;0)))
Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Wednesday, October 23, 2013

Sunday, September 8, 2013

ujungkelingking - Pernahkah Anda ketika sedang bekerja dengan fungsi-fungsi pada excel menemukan hasil seperti ini: #####, #DIV/0!, #NAME?, #REF!, #VALUE!, atau #N/A? Sebagian orang beranggapan bahwa munculnya hasil seperti itu adalah akibat dari sebuah kesalahan penerapan rumus pada excel. Padahal penyebabnya bisa berbeda-beda, tergantung dari pesan error yang muncul.

Misalnya saja jika pesan yang muncul adalah #####, ini bisa terjadi karena kolom yang kurang lebar atau format tanggal berbeda.

Sedang jika #DIV/0! (divide by zero), maka hal ini terjadi karena ada bilangan yang dibagi dengan angka Nol.

Atau #NAME? yang ada kemungkinan kita salah mengetikkan nama fungsi, misalnya fungsi SUM kita tulis dengan SUN.

Pesan error #REF! akan muncul jika excel tidak menemukan referensi yang dimaksud.

Pesan #VALUE! biasanya muncul ketika referensi yang seharusnya angka (number), tapi yang kita masukkan ternyata sebuah text.

Sedang error #N/A (not available) akan muncul ketika excel tidak menemukan nilai dari referensi yang diberikan.

Dan masih ada beberapa lagi pesan error yang saya sendiri belum begitu mengerti...

Lalu bagaimana mengatasi pesan #error seperti itu?

Meski tidak selalu sebuah kesalahan, namun tentu tidak enak melihat laporan yang banyak pesan semacam itu. Untuk mengatasinya, Anda bisa menyiapkan sebuah hasil alternatif. Maksudnya jika akan muncul pesan error seperti itu, maka yang tampil adalah alternatifnya. Hasil alternatif ini bisa berbentuk rumus lain, atau bisa juga berupa angka 0 (nol).

Baru setelah itu Anda bisa menggunakan fungsi ISERR.

Secara sederhananya bisa ditulis begini:

 =IF(ISERR(Rumus1);(Rumus2);(Rumus1)), atau

=IF(ISERR(Rumus1);0;(Rumus1))

Cara membacanya: Jika Rumus1 tidak terbaca/error, maka beralih memakai Rumus2 (atau 0). Namun jika tidak ada masalah, maka tetap memakai Rumus1.

Salam #Error.
Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Sunday, September 08, 2013

Wednesday, July 24, 2013

ujungkelingking - Anda yang kebetulan bekerja di kantor mungkin akrab mengerjakan tugas-tugas via excel.

Nah, seringkali ada tugas-tugas yang lebih mudah dikerjakan dengan rumus-rumus yang sudah ada di excel. Namun karena kita tidak tahu cara pengaplikasiannya dan merasa semakin ribet dengan itu, sehingga kita lebih memilih mengerjakannya secara manual. Padahal itu memakan waktu yang lebih lama.

Belum lagi jika tugas tersebut cukup banyak. Tanpa bantuan fungsi dan formula dari excel, waktu bekerja kita menjadi tidak efisien.

Kali ini saya ingin berbagi sebuah aplikasi gratis alias gretongan yang berisi rumus-rumus excel secara lengkap!

Dibuat oleh Kang Peter Noneley, dengan desain yang minimalis, namun dengan isi yang bikin bikin mupeng penyuka excel macam saya, hehe... #Gubrak! 

Tampilannya sederhana puoll, dengan 3 menu: Kamus Fungsi, Contoh-Contoh Fungsi, dan Kategori Fungsi

Tampilannya minimalis, isi maksimal (dok. pribadi)

Semua rumus ada di sini (dok. pribadi)

Contoh penerapannya dalam tugas

Dan meskipun disajikan menggunakan bahasa Inggris, saya pikir kita tidak akan terlalu kesulitan memahaminya karena semuanya menggunakan contoh penerapannya.

Namanya XLFDIC04. Yang tertarik bisa download di sini.


Semoga bermanfaat!
Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Wednesday, July 24, 2013

Monday, July 1, 2013

ujungkelingking - Lama saya tidak menulis tentang excel. Semoga berkenan!

Dulu, ketika saya masih di bagian Taxes, salah satu tugas saya adalah membuat Surat Setoran Pajak (SSP) setiap bulannya. Form-nya yang menggunakan excel memudahkan saya untuk menambahkan beberapa fungsi sehingga pekerjaan saya menjadi lebih cepat dan meminimalisir kesalahan.

Sumber gambar: dok. pribadi

Salah satu contohnya bisa Anda lihat pada gambar di atas. Pada sel B64 dan seterusnya ke kanan, saya harus memberi tanda silang pada kolom bulan yang sesuai dengan bulan yang ditunjukkan pada sel P58. Dengan kata lain, jika sel P58 menunjukkan bulan Januari, maka pada sel B64 -yaitu kolom bulan Januari- harus bertanda silang.

Karena itu saya kemudian memasukkan rumus bahwa jika sel P58 menunjukkan bulan Januari, maka sel B64 (sel Januari) berisi tanda silang (huruf "X"). Jika bukan terisi Januari, maka berisi kosong. Atau bila ditulis menjadi,
=IF($P$58="JANUARI";"X";"")
Formula ini kemudian diaplikasikan pada semua kolom dengan hanya mengganti nama bulannya saja.

***

Namun Jum'at kemarin seorang teman yang kini menggantikan posisi saya meminta bantuan kepada saya. Perusahaan tempat saya bekerja harus melaporkan Surat Ketetapan Pajak Kurang Bayar (SKPKB) untuk Pajak Penghasilan pasal 21. Form yang digunakan masih sama, hanya saja pada sel P58 harus terisi setahun (Januari s/d Desember), yang itu berarti pada sel B64 sampai M64 harus berisi tanda silang semuanya. Ini yang belum terakomodir pada rumus milik saya yang sebelumnya.

Bila dikerjakan secara manual -tanpa rumus- tentu mudah saja. Tapi jadinya gak seru dong, hihi... Karena itulah saya mencoba untuk membantunya. Apalagi, karena saya cukup tertantang kalau sudah berurusan dengan excel (*lebay...). Tapi apa daya, karena fungsi-fungsi pada excel yang saya ketahui cukup terbatas, jadi saya harus lebih memeras otak untuk menyelesaikan soal ini.

Langkah pertama tentu saya harus membuat sel baru untuk bulan berakhir. Jadi nanti harus ada 'bulan apa s/d bulan apa'. Kita sebut saja 'bulan pertama' s/d 'bulan kedua', atau sel P58 s/d sel Q58.

Sumber gambar: dok. pribadi

Kemudian langkah berikutnya, ini yang baru saja terpikirkan, saya harus mengubah nama-nama bulan menjadi angka. Jadi Januari disebut 1, Pebruari adalah 2..., Desember adalah 12. Hasil perubahan itu (dengan menggunakan fungsi VLookup) saya letakkan saja di sel K23 dan L23. Berarti nanti sel P58 atau 'bulan pertama' mengacu pada K23, dan Q58 atau 'bulan kedua' mengacu pada L23.

Langkah selanjutnya, saya harus memasukkan formula yang berbeda-beda pada setiap sel, dari sel B64 sampai M64. Ringkasnya seperti ini:

Pada sel B64 (bulan Januari) saya masukkan bahwa jika 'bulan pertama' adalah Januari (angka pengganti adalah 1), maka sel B64 akan terisi tanda silang. Atau jika ditulis,
=IF($K$23=1;"X";"")
Lalu pada sel C64 (bulan Pebruari) saya masukkan bahwa jika 'bulan pertama' adalah  kurang dari 3 (maksudnya sebelum Maret, yaitu Januari atau Pebruari saja) dan 'bulan kedua' lebih besar dari Januari, maka akan terisi tanda silang. Atau jika ditulis,
=IF(AND($K$23<3;$L$23>1);"X";"")
Pada sel berikutnya (bulan Maret), sama seperti di atas bahwa jika 'bulan pertama' adalah sebelum April dan 'bulan kedua' lebih besar dari Januari ataupun Pebruari, maka sel akan terisi tanda silang. Atau jika ditulis,
=IF(AND($K$23<4;$L$23>2);"X";"")
Dan seterusnya sama seperti itu sampai dengan bulan Nopember.

Lalu untuk bulan Desember formulanya sedikit ada tambahan, yaitu bahwa jika 'bulan pertama' dan 'bulan kedua' adalah Desember, atau jika 'bulan kedua' adalah Desember, maka sel akan terisi tanda silang. Atau seperti ini,
=IF(AND($K$23=12;$L$23=12);"X";IF($L$23=12;"X";""))

Demikian tips cara memberi tanda silang pada SSP yang dapat saya share. Semoga bermanfaat!
Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Monday, July 01, 2013

Thursday, November 1, 2012

ujungkelingking - Excel lagi, excel lagi... Mudah-mudahan gak bosen yah!

Kali ini kita belajar salah satu (eh, salah dua) fungsi dalam excel yang cukup menarik, yaitu COMBIN (combine) dan PERMUT (permutations). Fungsi ini akan membantu kita untuk mendapatkan berapa sebenarnya jumlah kombinasi yang dapat dihasilkan dari 2 bentuk. Ini tentu berbeda dengan menjumlah.

Sintaks untuk fungsi yang pertama adalah:

=COMBIN(banyaknya_item;kelompok_ukur)

Contoh:
Seorang pemilik rumah bermaksud untuk mengecat rumahnya. Untuk itu dia membeli 4 kaleng cat dengan macam warna yang berbeda. Namun dia ingin bahwa dalam satu dinding hanya boleh ada 2 kombinasi warna, dan tidak boleh sama dengan dinding yang lain. Nah, jika pertanyaannya adalah berapa jumlah kombinasi 2 warna yang bisa dihasilkan dari 4 macam warna cat tersebut, bagaimana kita menghitungnya?

Jawab:
Anggap saja keempat warna tersebut adalah merah, hijau, kuning dan biru. Karena dibutuhkan kombinasi 2 warna saja, maka pada excel bisa kita tuliskan:

=COMBIN(4;2)

Hasilnya, 6. Ini berarti ada 6 bentuk kombinasi 2 warna. Lebih jelasnya lihat gambar berikut:



***

Sedang sintaks untuk fungsi PERMUT adalah:

=PERMUT(banyaknya_item;jumlah_kelompok)

Lebih mudahnya untuk mengenali fungsi ini adalah pada jumlah kemungkinan kombinasi huruf pada password sekian digit.

Misalnya jika ada 4 huruf yaitu: A, B, C dan D yang akan digunakan untuk membuat password yang terdiri dari 2 digit saja, maka dengan fungsi PERMUT kita akan tahu bahwa ada 12 kemungkinan yang bisa digunakan.



Sengaja menggunakan contoh yang sama, agar kita bisa tahu perbedaannya. Harap maklum, jika istilah-istilahnya banyak yang salah.

Sekedar catatan pribadi, biar enggak lupa. Semoga berguna.
Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Thursday, November 01, 2012

Wednesday, October 24, 2012

ujungkelingking - Bila kita hendak menjadikan suatu angka menjadi sebuah angka desimal (koma nol nol), pada worksheet excel tentu sangat mudah sekali. Anda tinggal mencari increase decimal pada deretan tool bar-nya.


Namun, jika kita berharap hasil yang muncul adalah sebuah text, maka kita bisa menggunakan fungsi FIXED disini. Penulisannya adalah =fixed(number;decimal;no_commas).

  • Pada number, diisi dengan sel yang berisi data numerik
  • Pada decimal, diisi dengan berapa digit angka nol yang ada di belakang koma
  • Yang terakhir ini diisi dengan TRUE atau FALSE, namun bisa juga diisi dengan angka nol

Misalnya, data numerik tersebut berada pada sel A1, maka pada sel lain yang kosong dapat kita tuliskan,

=FIXED(A1;2;0)

maka, hasil yang muncul adalah dengan tambahan 'koma nol nol'.


Sekedar informasi saja, angka yang dihasilkan dari penggunaan fungsi FIXED adalah berupa text, dan bukan numerik lagi. Cara membuktikannya kita bisa menggunakan fungsi LEN, yang berguna untuk menghitung jumlah karakter.

Dengan mengetikkan =LEN(sel), maka pada contoh yang pertama dihasilkan 3 (artinya 3 karakter). Akan tetapi bila kita terapkan pada contoh yang kedua akan menghasilkan 6 (6 karakter), yaitu 'satu', 'dua', 'tiga', 'koma', 'nol', dan 'nol'.

Nah, semoga berguna!
Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Wednesday, October 24, 2012

Thursday, October 11, 2012

ujungkelingking - Menghitung Gaji Lembur Karyawan Dengan Excel

Dengan asumsi gaji lembur karyawan per jam adalah 20.000,-, maka buatlah sebuah tabel sederhana seperti contoh di bawah ini:


Setelah itu untuk mengetahui durasi waktu lembur, pada sel F4, ketikkan =E4-D4 (jam selesai dikurangi jam mulai). Lalu lakukan dragging (untuk meng-copy rumus) ke bawah.


Selanjutnya, kita gunakan kolom-kolom di sebelahnya untuk mengambil hitungan jam dan menitnya. Jika pada H4 kita masukkan =HOUR(F4), maka kita akan mendapatkan hasil 0, karena durasi lembur tidak sampai satu jam.

Untuk mendapatkan menit-nya, pada sel I4 kita masukkan =MINUTE(F4). Dan kita dapatkan hasil 30. Artinya 30 menit.


Maka, langkah terakhir kemudian adalah menentukan besaran gajinya. Pada J4, masukkan =(H4*$F$10)+(I4/60*$F$10). Sederhananya, kalikan jam dengan gaji lembur per jam, kalikan menit dengan gaji lembur per jam, lalu tambahkan.


Selesai.

Note: Penggunaan tanda $ pada fungsi di atas didapatkan dengan menekan tombol F4 pada keyboard Anda. Kegunaannya adalah untuk mengikat rumus (dalam hal ini sel F10) agar tidak ikut bergeser saat kita melakukan dragging ke bawah.

Semoga bermanfaat!

Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Thursday, October 11, 2012

Thursday, May 3, 2012

ujungkelingking - Melakukan excelling tanpa bantuan mouse, tentu bisa Anda lakukan. Bahkan Anda juga bisa mengoperasikan sebuah komputer dengan tanpa bantuan mouse, karena dulu pun ketika awal-awal komputer diciptakan juga tidak menggunakan mouse.

Dalam excel, Anda hanya perlu mengkombinasikan dua atau tiga tombol keyboard untuk membentuk perintah yang kita inginkan. Tentu, dengan melakukannya setiap hari Anda akan mengingat lebih cepat dari yang Anda bayangkan.

Berikut ini saya share beberapa perintah-perintah dasar dalam excel, mudah-mudahan bisa membantu Anda:


Jadi, mengerjakan excel tanpa mouse? Tentu saja bisa! Mana yang lebih cepat? Tergantung kebiasaan. Untuk saya, lebih cepat menekan "Ctrl" dan "S" ketimbang menggerakkan mouse ke icon bergambar disket.

Semoga bermanfaat!

Sumber: www.exceltips.com
Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Thursday, May 03, 2012

Wednesday, March 28, 2012

ujungkelingking - Bila Anda sering (baca: selalu) ditugaskan untuk membuat laporan berkala tiap bulan, yang dalam laporan tersebut kalimatnya selalu sama dan hanya perlu mengganti data-data yang berbeda tiap bulannya, maka mungkin menggabungkan atau menyisipkan fungsi text ke dalam teks kalimat tersebut bisa sangat membantu pekerjaan Anda.

Pada postingan sebelumnya telah dijelaskan tentang cara penggabungan dua cells atau lebih dengan menggunakan fingsi CONCATENATE, maka kali ini -sedikit berbeda- kita akan "menyisipkan" fungsi tersebut ke dalam laporan (teks) Anda.

Perhatikan contoh sederhana berikut:



Karena tiap bulan kalimat yang digunakan sama persis dan hanya perlu mengubah beberapa bagian saja, kita bisa menyisipkan fungsi text dengan terlebih dahulu mempersiapkan data yang setiap bulan berubah.

Dalam contoh di atas, pada cell A1, masukkan tanggal (karena tanggal pada laporan pasti akan berubah setiap bulannya).
Pada cell A2, Anda bisa masukkan nama perusahaan (dalam hal ini, UD Ambarawa).
Lalu pada cell A3, masukkan jumlah keuntungan.
Dan pada cell A4, bisa Anda tambahkan persentasenya.

Kemudian Anda bisa mengetikkan seperti di bawah ini,

="Pada bulan "&TEXT(A1;"mmmm yyyy")&", "&TEXT(A2;0)&" mendapatkan laba sebesar Rp "&TEXT(A3;"0.000,-")&" atau turun sekitar "&TEXT(A4;"0,0%")&" dibandingkan bulan kemarin."

Perhatikan, kata yang dicetak dengan warna biru merupakan fungsi text yang kita sisipkan dalam sebuah kalimat, sedang sisanya adalah teks biasa.

Dan hasilnya, setiap kali Anda mengubah data yang berada pada cell A1, A2, A3 atau A4, maka secara otomatis data yang berada pada cell A5 akan ikut berubah.



Demikian sedikit guna fungsi text sederhana untuk Anda terapkan. Semoga bermanfaat.

Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Wednesday, March 28, 2012

Saturday, March 24, 2012

ujungkelingking - Kali ini, penulis akan coba membahas 2 fungsi yang berbeda, namun bisa menghasilkan result yang sama. Akan tetapi, nanti, di akhir tulisan ini Anda akan bisa membedakan dalam suatu kasus tertentu harus menggunakan fungsi yang mana.

Kedua fungsi tersebut adalah: REPLACE dan SUBSTITUTE.

Keduanya bisa digunakan untuk mengganti suatu karakter pada suatu text dengan karakter yang lain. Cara penulisannya dalam rumus adalah dengan meletakkan karakter yang dimaksud ke dalam tanda petik-atas ("..."). Fungsi ini juga bisa digunakan untuk menghilangkan suatu karakter dengan cara menggantinya dengan karakter "tidak ada", yang penulisannya bisa dilakukan dengan meletakkan tanda petik-atas tanpa karakter apapun di dalamnya ("").

Sebagai contoh, Anda ingin menghilangkan sebuah tanda titik pada kata PT. Abcd. Dengan menggunakan kedua fungsi tersebut, akan di dapat hasil yang sama seperti berikut,

dengan fungsi REPLACE
dengan fungsi SUBSTITUTE

Meski hasil yang ditampilkan sama persis, akan tetapi -seperti yang Anda lihat- keduanya menggunakan format penulisan rumus yang berbeda. Format dari SUBSTITUTE adalah SUBSTITUTE(text; old_text; new_text; [instance_num]), yang dengan bahasa sederhananya adalah;
  • text, adalah text asal atau sel sumber
  • text_old, adalah karakter yang hendak Anda ganti
  • new_text, adalah karakter pengganti
  • [instance_num], urutan karakter yang akan diganti tersebut (ini, bila karakter yang akan diganti ada lebih dari satu dalam sel tersebut)
Sementara REPLACE menggunakan rumusan REPLACE(old_text; start_num; num_chars; new_text), yang terjemahannya adalah;
  • old_text, adalah karakter yang akan diganti
  • start_num, adalah posisi dari karakter tersebut
  • num_chars, banyaknya jumlah karakter yang akan diganti, dimulai dari start_num
  • new_text, karakter pengganti 
Nah, dengan ketentuan seperti yang tersebut di atas maka tentu kedua fungsi ini kemudian memiliki tugas berlainan. Dalam contoh dengan lebih banyak data akan terlihat perbedaan keduanya. Kedua kasus di bawah ini akan memperjelas keadaan.

Contoh 1: Menghilangkan tanda titik


Dalam kasus di atas, posisi tanda titik adalah tetap, yaitu selalu berada pada karakter ketiga. Karena itu, Anda bisa menggunakan fungsi REPLACE, lalu kemudian lakukan dragging ke bawah.
Dan hasilnya,



Contoh 2: Menghilangkan tanda titik


Dalam contoh kedua ini, tanda titik letaknya tidak pasti. Kadang berada di urutan kedua, kadang ketiga, dsb. Maka Anda tidak bisa menggunakan fungsi REPLACE disini. Anda, harus menggunakan SUBSTITUTE, karena yang kita sasar adalah "titik"nya dan bukan "karakter ketiga"nya.



Nah, itulah perbedaan dari keduanya. Tergantung di situasi mana Anda menggunakannya.

Semoga bermanfaat! 
Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Saturday, March 24, 2012

Thursday, March 22, 2012

ujungkelingking - Menjawab pertanyaan salah seorang Kompasianers atas postingan sebelumnya tentang Memunculkan Fungsi Terbilang Pada Excel yang penulis posting di Kompasiana*, tentang bagaimana cara menambahkan tanda kurung pada hasil fungsi tersebut.

Sebenarnya bisa saja dilakukan dengan menambahkan rumus sehingga menjadi,

=terbilang(A1)&")"

Hanya masalahnya nanti yang tampil cuma tanda kurung tutup saja (tanpa tanda kurung buka).


Nah, untuk mengatasi -atau lebih tepatnya- mensiasati hal tersebut kita masukkan saja fungsi ini: CONCATENATE.

Caranya, masukkan tanda kurung buka "(" pada sel B1 dan tanda kurung tutup ")" pada sel D1. Selanjutnya kita tinggal menggabungkan ketiga sel tersebut sehingga hasil akhirnya menjadi seperti yang tampil pada sel C2 berikut,



Nah, bagaimana? Memang sepertinya bukan jawaban yang benar, tapi -setidaknya- ini menyelesaikan masalah, bukan?

Semoga bermanfaat!


*http://teknologi.kompasiana.com/gadget/2012/03/22/tips-memunculkan-fungsi-terbilang-pada-excel/
Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Thursday, March 22, 2012
ujungkelingking - Jika Anda kebetulan bekerja di bagian Accounting, Sales atau Taxes, tentunya Anda sering mengetikkan sejumlah nominal berikut terbilangnya. Nah, jika yang diketikkan hanya beberapa saja tentu tidak begitu menyulitkan. Tapi bagaimana jika yang harus diketikkan jumlahnya puluhan -atau lebih, yang akan selalu ada kemungkinan salah ketik? Tentu akan merepotkan dan makan waktu jika Anda harus mengoreksinya satu persatu. Karena itulah kita membutuhkan fungsi Terbilang yang pastinya akan lebih memudahkan pekerjaan Anda.

Langsung saja, ya!

Langkah pertama yang harus Anda lakukan adalah download file terbilang.xla ini, lalu simpan di sembarang  folder di komputer Anda.

Selanjutnya untuk mengaktifkan file tersebut, buka lembar kerja excel Anda, lalu lakukan salah satu langkah berikut;

(a) Jika Anda pengguna windows 2003, Anda bisa langsung masuk menu Tools > Macro > Security. Pada Security Level pilih Low.


Selanjutnya masuk kembali ke menu Tools > Add-Ins. Klik tombol browse, lalu ambil file terbilang.xla yang sudah di download di atas.



(b) Jika Anda kebetulan menggunakan windows 2007, Anda bisa langsung klik kanan tombol berbentuk bulat yang berada di sebelah kiri-atas layar komputer Anda. Pilih Excel Option. Lalu cari Add-Ins, dan browse file terbilang.xla yang telah Anda download.



Selesai.

Dan untuk menggunakan fungsi ini, masukkan syntax =terbilang(sel_yang_berisi_angka).
Sebagai contoh, pada sel A1 kita masukkan sejumlah angka, maka pada sel B1 kita masukkan rumusnya,

=terbilang(A1)

lalu akan diperoleh hasil seperti ini,


Hasil dari fungsi tersebut kemungkinan berupa huruf kapital semua. Jika Anda tidak suka, Anda bisa mengkombinasikannya dengan fungsi proper.

=proper(terbilang(A1))

sehingga hasilnya akan menjadi seperti di bawah ini,




Note.
Bila ternyata di belakang hasil fungsi tersebut tidak muncul kata-kata "rupiah", Anda bisa menambahkannya sendiri sehingga menjadi seperti berikut,

=proper(terbilang(A1)&" rupiah"))


OK! Selamat mencoba, dan Anda bisa mendapatkan tips-tips yang lain di artikel-artikel berikutnya.

Salam.
Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Thursday, March 22, 2012

Wednesday, March 21, 2012

ujungkelingking - Seperti ketika Anda harus mengetikkan delapan digit terakhir dari nomor Faktur Pajak -misalnya "00000123"- Anda tentu tak harus mengetik kedelapan digit tersebut. Excel memudahkan kita sehingga cukup mengetikkan angka terakhirnya saja, dalam contoh di atas, ketikkan: 123, dan secara otomatis excel akan menambahkan sejumlah bilangan nol di depannya.

Setidaknya ada 2 cara untuk menambahkan angka nol secara otomatis tersebut.

Cara yang pertama adalah dengan menggunakan format cells.
Klik kanan sel yang berisi angka tersebut, pilih Format Cells. Pada dialog box yang muncul, pilih Custom. Selanjutnya pada kolom type, ketikkan angka nol sebanyak yang Anda mau (misalnya delapan digit).


Dan setelah Anda meng-klik tombol OK, maka setiap kali Anda mengetikkan angka pada sel tersebut, secara otomatis excel akan menambahkan sejumlah nol di depannya sehingga hasilnya akan selalu berjumlah delapan digit.



Cara yang kedua adalah dengan menggunakan fungsi text, yaitu gabungan dari fungsi TEXT dan REPT. =TEXT(sel_yg_berisi_angka;REPT(angka_yg_hendak_ditambahkan;jml_digit)).

Misalnya angka tersebut berada pada sel D1, maka pada sel lain yang kosong bisa kita tuliskan rumus,

=TEXT(D1;REPT(0;8))


Dan sama seperti cara yang pertama tadi, hasilnya adalah bilangan delapan digit.

***

Adakah penggunaan kedua cara tersebut memiliki perbedaan?

Jawabannya, ya. Meskipun hasil yang ditampilkan (sepertinya) sama, sesungguhnya keduanya memiliki perbedaan yang signifikan.

Pada cara yang pertama -dengan bantuan format cells- meskipun hasil yang muncul adalah delapan digit, tetapi sebenarnya angka-angka nol yang berada di depan itu tidak benar-benar ada! Ya, deretan angka nol yang berada di depan itu hanyalah tampilan luar saja. Sel tersebut sejatinya hanya berisi angka "123", dan bukan "00000123".

Berbeda bila Anda memakai cara yang kedua -menggunakan fungsi text gabungan, hasil yang muncul benar-benar delapan digit dan itu bukan "kelihatannya" saja.

Cara membuktikannya cukup sederhana, yaitu dengan menggunakan fungsi text yang lain: LEFT. Gunanya fungsi ini adalah untuk mengambil karakter paling kiri dari sebuah text.

***

Perbandingan:

Pada hasil yang pertama (sel A1), masukkan rumus berikut ke sel A2,

=left(A1;2)


Dengan fungsi tersebut Anda akan mengambil dua karakter paling kiri dari angka yang berada pada sel A1. Hasilnya -seharusnya- adalah "00". Tapi nyatanya tidak, bukan? Hasil yang muncul malah "12".

Hal ini membuktikan bahwa angka "00000" yang berada di depan angka "123" TIDAK dianggap sebagai suatu karakter oleh excel. Karena itu penghitungan karakter paling kiri dimulai dari angka "1".

Selanjutnya dengan rumus yang sama, lakukan juga pada sel E1 yang menggunakan cara kedua untuk menambahkan bilangan nol-nya. Hasilnya seperti ini,


Dua karakter paling kiri adalah "00".

Nah, sekarang kita sudah mengerti perbedaannya. Mau memakai cara yang mana, tergantung selera dan kebutuhan Anda.

Semoga bermanfaat! 
Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Wednesday, March 21, 2012

Monday, March 19, 2012

ujungkelingking - Anda yang sehari-harinya bekerja di dalam ruang (kantor), sedikit banyak pasti menggunakan excel untuk membantu pekerjaan Anda. Tentu tidak akan menjadi masalah bila yang menggunakan komputer tersebut hanya Anda seorang. Tapi bagaimana bila komputer tersebut digunakan secara bergantian oleh beberapa orang? Bagaimana bila worksheet Anda yang mungkin berisi rumus-rumus rumit tiba-tiba menjadi error gara-gara ada yang menghapus sebagian rumusnya? Anda juga tak mungkin meng-lock-nya karena rekan kerja Anda yang lain juga menggunakan file tersebut. Anda pastinya bakal memeras otak lagi agar dapat menggunakan file tersebut.

Berikut ini ada sedikit tips untuk Anda yang mau "mengamankan" worksheet sekaligus rumus-rumus yang ada di dalamnya. Nah, karena file ini juga digunakan oleh rekan kerja Anda, maka kita tidak perlu menguncinya dari luar. Yang akan kita lakukan adalah mengunci sebagian sel atau kolom dan tidak mengunci sebagian yang lain.

Misalnya kita ingin mengunci dan menyembunyikan rumus yang berada di kolom Total, seperti gambar di bawah ini:

Langkah pertama adalah lakukan blok semua worksheet, laku klik kanan dan pilih Format Cells, lalu pada Dialog Box pilih Protection. Pastikan kolom Locked dan Hidden tercentang. Pilih OK.
Dengan begini, nantinya semua sel akan terkunci, tidak bisa diedit.

Kemudian, lakukan blok pada kolom yang boleh diisi oleh rekan kerja Anda. Dalam contoh ini adalah kolom Jumlah. Sorot range D3:D6, sama seperti langkah di atas, pilih Format Cells > Protection. Bedanya, kali ini kosongkan tanda centang pada kolom Locked dan Hidden. Pilih OK.


Setelah itu masuk menu Tools > Protection > Protect Sheet.
Masukkan pasword Anda, ulangi sekali lagi. Lalu Save.






Dan hasilnya adalah, rumus Anda tak lagi terbaca.


Selamat mencoba, semoga bermanfaat!
Written by: Pri Enamsatutujuh
UJUNGKELINGKING, at Monday, March 19, 2012

Popular Posts

Subscribe to RSS Feed Follow me on Twitter!