Sabtu, 12 Oktober 2019

SUMIF vs SUM ARRAY, MANA YANG LEBIH BAIK?

Anda mungkin sudah terbiasa menggunakan formula SUMIF atau SUMIFS, tahukah Anda bila ada formula yang lebih canggih dibanding dengan SUMIF atau SUMIFS? yaitu SUM ARRAY.

"Lebih canggihnya dimana?" mungkin pertanyaan tersebut yang akan ditanyakan.
Disini saya akan menampilkan perbandingan antara SUMIF dengan SUM ARRAY, mana sih sebetulnya yang lebih baik.
Saya tekankan sekali lagi, saya hanya menampilkan perbandingan tentunya ini tergantung dari kompleksitas data dan kebutuhan pekerjaan Anda sendiri mana yang lebih cocok.

Dalam range A2:F8 saya memiliki data seperti gambar contoh. Kali ini saya akan membandingkan dengan 4 kasus, masing-masing kasus jika terpenuhi akan mendapatkan point 10

Kasus Pertama
Menjumlahkan kolom DATA1 dengan dua kriteria, yaitu kriteria1 = aa dan kriteria2 = Hijau.

Perbandingan Pertama

Dari gambar perbandingan pertama SUMIFS dapat melakukan dan SUM ARRAY pun dapat melakukannya, sehingga masing-masing mendapatkan point 10.

Kasus Kedua
Menjumlahkan kolom DATA1 dengan dua kriteria, yaitu kriteria1 = aa dan kriteria2 = Hi (dua karakter dari depan, Hi dari Hijau).

Perbandingan Kedua

Dari gambar perbandingan pertama SUMIFS menghasilkan nilai nol yang artinya tidak dapat melakukan sedangkan SUM ARRAY menghasilkan nilai yang artinya dapat melakukannya (Anda tidak percaya dengan total nilainya? coba saja hitng sendiri), dengan demikian SUMIF tidak mendapatkan point sedangakn SUM ARRAY mendapatkan point 10.

Kasus Ketiga
Menjumlahkan semua kolom DATA dari DATA1 sampai dengan DATA4 dengan dua kriteria, yaitu kriteria1 = aa dan kriteria2 = Hijau.

Perbandingan Ketiga

Dari gambar perbandingan pertama SUMIFS menghasilkan nilai #VALUE! yang artinya tidak dapat melakukan sedangkan SUM ARRAY menghasilkan nilai yang artinya dapat melakukannya (Anda tidak percaya dengan total nilainya? coba saja hitng sendiri), dengan demikian SUMIF tidak mendapatkan point sedangakn SUM ARRAY mendapatkan point 10.

Kasus Keempat
Menjumlahkan semua kolom DATA dari DATA1 sampai dengan DATA4 dengan dua kriteria, yaitu kriteria1 = aa dan kriteria2 = Hi (dua karakter dari depan, Hi dari Hijau).

Perbandingan Keempat

Dari gambar perbandingan pertama SUMIFS menghasilkan nilai #VALUE! yang artinya tidak dapat melakukan sedangkan SUM ARRAY menghasilkan nilai yang artinya dapat melakukannya, dengan demikian SUMIF tidak mendapatkan point sedangakn SUM ARRAY mendapatkan point 10.

Demikian hasil ahir dari perbandingan antara SUMIFS dengan SUM ARRAY, SUMIFS mendapatkan 10 point dan SUM ARRAY mendapatkan 40 point.
Dengan demikian telah kita ketahui bersama bahwa SUM ARRAY lebih canggih dibandingkan dengan SUMIFS.
Namun demikian SUM ARRAY bukan tidak memiliki kekurangan, kekurangan SUM ARRAY ini adalah dia memakan banyak memori, sehingga jika Anda bekerja menggunakan ribuan data dan menggunakan formula SUM ARRAY ini maka proses calculating-nya akan memakan waktu lama atau bahkan bisa jadi membuat excel Anda crash/menutup sendiri.

Pilihan di tangan Anda, semoga perbandingan ini bermanfaat. Jika ada pertanyaan atau komentar silahkan berikan komentar Anda dibawah.

CARA MENGETAHUI ADA ATAU TIDAKNYA SUATU DATA PADA RANGE DATA DI EXCEL

Kasus Excel

cosimpulan 18.43 0

Tanya:
Jika saya memiliki suatu range data (contoh: dari B2:E6), dan saya ingin mengetahui apakah nilai data di cell H3 saya ada di dalam range data tadi, bagaimana caranya?

Jawab:
Anda dapat menggunakan kombinasi formula yaitu formula SMALL, TYPE (atau bisa juga menggunakan ISNUMBER) dan IF.
Formula SMALL digunakan untuk mengetahui di kolom atau baris berapa data tersebut ada
Formula TYPE digunakan untuk menguji formula didalamnya benar atau salah, jika menggunakan formula ISNUMBER untuk menguji nilai formula didalamnya berupa nomer atau bukan
Formula IF digunakan untuk menentukan ada tidaknya data yang di cari di dalam range data.
Karena data tersebut berupa array, maka untuk mengeksekusi formula tersebut Anda harus menekan tombol CTRL+SHIFT+ENTER jika anda hanya menggunakan tombol Enter saja untuk mengeksekusinya maka akan hasilnya akan error.

Syntax/cara penulisan formula
Jika menggunakan formula TYPE
=IF(TYPE(SMALL(IF($B$3:$E$6=H3,COLUMN($B:$E),""),1))<>16,"ada","tidak ada")
Jika menggunakan formula ISNUMBER
=IF(ISNUMBER(SMALL(IF($B$3:$E$6=H3,COLUMN($B:$E),""),1)),"ada","tidak ada")

Jika diterjemahkan maka formula tersebut akan bermakna sebagai berikut:

Menggunakan TYPE: Jika formula TYPE menghasilkan nilai yang benar maka data tersebut ada di dalam range data maka akan menampilkan kata "ada" dan jika salah maka akan menampilkan kata "tidak ada". Sedangkan formula small sendiri berguna untuk menampilkan nilai terkecil dalam range data.
Menggunakan ISNUMBER: Jika formula ISNUMBER menghasilkan angka maka data tersebut ada di dalam range data maka akan menampilkan kata "ada" dan jika bukan angka maka akan menampilkan kata "tidak ada". Sedangkan formula ISNUMBER sendiri berfungsi untuk menentukan apakah formula atau cell didalam formula ISNUMBER tersbut berupa angka atau bukan.

Demikian cara mengetahui ada atau tidaknya suatu data pada range data, semoga bermanfaat.

Apabila ada pertanyaan silahkan gunakan komen dibawah, insyaAllah saya akan membantu.

LEBIH CEPAT DAN MUDAH MERUBAH DATA TEXT MENJADI TANGGAL

Kasus Excel

cosimpulan 08.18 2

Tutorial berikut pada dasarnya sama dengan CARA MERUBAH DATA TEXT MENJADI TANGGAL DI EXCEL DAN OPENOFFICE, hanya saja pada tutorial tersebut mungkin untuk sebagian orang dirasa terlalu rumit/njlimet.

Nah, pada tutorial kali ini saya akan memberikan cara cepat dan mudah untuk merubah data text menjadi data tanggal.

Formula untuk dapat merubah data text menjadi data tanggal dengan menggunakan =DATEVALUE()

Cara Penggunaan Formula
Cara meggunakan formula ini sangat sederhana, pada cell yg Anda kehendaki tulislah formula sebagai berikut:
=DATEVALUE(Cell_data_text)

Cell_data_text maksudnya adalah cell data text yang akan akan kita rubah menjadi data tanggal, lihat screenshot berikut:

Selesai, semoga bermanfaat.

CARA MERUBAH DATA TEXT MENJADI TANGGAL DI EXCEL DAN OPENOFFICE

Kasus Excel

cosimpulan 17.15 1

Gambar 1. Contoh Kasus.

Kasus: Om Admin, saya mau minta tolong. Saya punya data dimana dalam data tersebut terdapat sebuah kolom yang isinya data tanggal, namun masalahnya isi dari data tanggal tersebut masih berupa text karena hasil download dari software lain. Pertanyaannya, bagaimana cara saya untuk merubah/mengganti data tersebut menjadi data tanggal dengan format tanggal pula (contohnya sesuai dengan gamar terlampir)?

Jawab:

Sebetulnya untuk menjawab pertanyaan ini ada 2 pilihan yang bisa kita lakukan, yaitu cara manual atau menggunakan formula.

Jika data yang kita miliki jumlahnya sedikit maka pilihan pertama (cara manual: mengganti langsung data yang ada dengan penulisan format tanggal yang sesuai) bisa menjadi pilihan utama karena toh tidak memakan waktu yang lama juga, namun jika data yang kita miliki jumlahnya ratusan atau bahkan ribuan baris/data maka cara manual tidak saya sarankan.

Lho, emang kenapa gan? yeee... ya iya lah gan, bayangin aja kalau agan punya 15ribu data/baris yang harus agan ganti secara manual, bisa gempor kali gan, selain itu tingkat akurasinya juga diragukan, tingkat kesalahan penulisan juga tinggi.

Terus gimana dong gan?

Makanya sabar dulu, ikuti dulu penjelasannya.

Baik lah, kita lanjut lagi.

Untuk mengatasi hal ini saya sarankan agan-agan yang mengalami masalah ini untuk menggunakan formula. Formulanya apa dan gimana gan admin? simple kok, cukup menggunakan formula "DATE", tapi didalemnya ada tambahan formula lagi sih, yaitu MID, LEFT dan HLOOKUP atau VLOOKUP (tergantung data bantu yang kita buat apakah sceara vertikal atau horisontal), atau dengan kata lain dengan kombinasi formula.

Kita asumsikan bahwa datanya sama seperti pada kasus ini, data tambahan lainnya yaitu tahun datanya hanya tahun 2017 saja.

Untuk lebih jelasnya silahkan pantau langkah-langkah berikut ini:

1. Kali ini saya akan membuat data bantu diatas data yang akan saya rubah sehingga akan memudahkan saya dalam mengambil datanya ketika menggunakan formula. Jika kita memiliki contoh data seperti pada gambar, dimana data bantu ini akan memuat nama bulan dan urutan bulan, contoh: Jan = 1, feb = 2 dan seterusnya. Lihat contoh gambar.

Gambar 2. Data Bantu.

2. Ambil contoh kita akan menempatkan/menuliskan formula pada cell G7, maka tulislah formula di cell G7 sebagai berikut:

=DATE(2017,HLOOKUP(MID(D7,4,3),$C$1:$G$,2,0),LEFT(D7,2))

Dimana formula utamanya adalah DATE (yang paling luar), sedangkan formula lainnya adalah formula-formula bantu. Untuk penjelasan formulanya mungkin tidak perlu lagi ya, karena saya yakin agan-agan sudah faham untuk masing-masing formula yang digunakan diatas.

Gambar 3. Cara Penulisan Formula.

3. Kemudian jika cell G7 tadi format tampilannya belum kita format sesuai tanggal maka langkah kali ini ialah merubahnya, yaitu dengan cara klik kanan pada cell G7 (atau shortcutnya: Ctrl+1 [bukan angka satu di numpad ya]), kemudian pilih Format Cell (lihat contoh gambar)

Gambar 4. Format Cell (Ctrl+1).

4. Kemudian klik pada Tab Number (langkah 1 pada gambar), biasanya tab ini defaultnya sudah terbuka dari awal. Lalu dalam kolom Category, pilih Date (langkah 2 pada gambar), kemudian di kolom Locale (location), pilih format negara penulisan (langkah 3 pada gambar), dan di kolom Type pilih lah tipe penulisan sesuai yang dikehendaki (langkah 4 pada gambar), dalam hal ini saya akan menggunakan format tampilan tanggal, bulan lalu tahun.

Gambar 5. Cara Format Cell Tanggal.

5. Hasilnya akan tampak seperti pada gambar berikut:

Gambar 6. Hasil Format Cell.

6. Langkah selanjutnya ialah mengcopy formula yang ada di cell G7 sampai ke bawah atau ahir baris yang akan kita rubah datanya, seperti pada gambar

Gambar 7. Mecopy value & format.

7. Setelah itu, blok semua cell/range yang tadi kita pastekan formulanya, kemudian copy dan pastekan ke kolom data asli yang akan kita rubah, tapi ingat, hanya mempastekan nilainya saja, bukan semuanya. Caranya yaitu, setelah kita copy range tadi, di cell D7 kita klik kanan, pilih Paste Special.

Gambar 8. Paste Hasil

8. Setelah muncul jendela Paste Special, pilih Values (langkah 1 pada gambar), kemudian klik OK.

Gambar 9. Paste Value dari hasil formula.

9. Pada langkah ke 8 kita hanya mempastekan isinya saja, namun jika kita ingin mempastekan format tampilannya juga, jangan tekan tombol Exc atau enter, masih pada mode Copy, lakukan seperti langkah 8 tadi namun kali ini kita pilih "Formats" (langkah 1 pada gambar), kemudian klik OK.

Gambar 10. Paste Formats dari hasil formula.

Selesaiii... semua data yang tadinya berupa text kini telah berubah menjadi format tanggal.

Jika ada penjelasan saya yang kurang difahami silahkan jangan sungkan untuk bertanya. Semoga turotial ini bisa membantu.

Autofill Data Kosong Menggunakan Formula IF

Kasus Excel

cosimpulan 12.12 0

Pertanyaan:

Data Hasil tarikan dari Accurate

"Saya memiliki data excel dari hasil tarikan dari suatu software, sebut saja Accurate. Namun dari data hasil tarikan tersebut bolong-bolong, bagaimana cara memperbaikai/mengisi data yang bolong tersebut (contoh seperti berikut)

secara cepat, karena jika saya copas akan memakan waktu yang lama jika datanya ribuan, mohon solusinya. Terimakasih"

Jawab:
Untuk menangani hal seperti ini bisa menggunakan formula IF, untuk lebih jelasnya silahkan ikuti lengkah-langkah berikut:

1. Jika kita mengacu pada data contoh gambar tersebut, maka langkah pertama tempatkan kursor di cell mana saja yang sejajar dengan data paling atas yang akan kita isi yang memiliki data awal, dalam hal ini kita tempat pada baris ke enam. Sebagai contoh tempatkan kursor di cell D6 seperti terlihat di contoh, kemudian ketikan formula/rumus berikut: =IF(A6<>"",A6,D5)

Gambar 1. Buat formula menggunakan fungsi IF

Formula tersebut jika diterjemahkan ialah sebagai berikut:
Jika cell A6 datanya tidak kosong atau jika di cell A6 memiliki data, maka nilai yang dimunculkan adalah sama dengan nilai yang ada di cell A6, tapi jika di A6 datanya kosong, maka data yang diambil/dimunculkan sama dengan data yang ada diatasnya, demikian seterusnya jika formula ini dicopykan ke bawah.

2. Langkah berikutnya adalah copy-pastekan formula yang sudah kita buat tadi ke ahir baris dari data tersebut, seperti terlihat dalam contoh gambar. Kemudian blok range dari formula yang tadi telah kita copy-pastekan (dalam contoh ini Range D6:D38), setelah di blok, copy formula tadi dan ikuti langkah ke-3 dibawah ini.

Gambar 2. Copykan formula sampai ke ahir data

3. Setelah di copy, kemudian pastekan di cell A6 mengunakan paste>special>value, seperti terlihat dalam gambar 3 dan 4 dibawah ini

Gambar 3. Paste special di cell A6

Gambar 4. Pilih Value.

4. Selesai, jika langkah-langkah diikuti dengan benar maka hasilnya akan tampak sama dengan gambar 5.

Gambar 5. Kolom customer yang kosong sudah terisi semua

Semoga tutorial ini bermanfaat, silahkan baca artikel terkait lainnya.

Mudah Pengisian SSP dengan Excel

Excel Software

cosimpulan 19.24 0

Bila Anda memerlukan software untuk mengisi SSP namun terbentur dengan budget, berikut file untuk pengisian SSP dengan menggunakan VBA Macro Excel yang saya buat dan dapat Anda manfaatkan.

Dengan File ini Anda dapat mengisi SSP dengan menggunakan printer dot matrix dan tidak usah lagi menggunakan mesin tik (mang dikelurahan :p)

Didalam file ini terdapat pula petunjuk penggunaan, dan bila Anda memerlukan bantuan untuk menyeting ulang, anda dapat menghubungi saya melalui email berikut (mohon jangan SPAM): cosim_da@yahoo.com

File ini masih memiliki versi 1 namun sudah bisa Anda manfaatkan, saya sedang menyempurnakan file tersebut. Saya segera mengupload versi berikutnya setelah selesai.

Untuk File versi 1-nya dapat Anda unduh (download) pada link berikut:
http://rapidshare.com/files/196759520/Form_SSP_ver1.xls.html