jump to navigation

Excel Tips: Membandingkan Dua Sheets Sekaligus (Comparing Two Sheets at Once) July 28, 2010

Posted by Admin in Tutorials, Tips & Tricks.
Tags: , , , ,
3 comments

Beberapa hari yang lalu, jurus yang sudah lama saya simpan ini akhirnya terpakai juga. Kadang kita perlu untuk membandingkan dua sheets di excel sekaligus, baik sheets tersebut terletak di dua file yang berbeda atau malah di satu file yang sama. Kalau letaknya di dua file yang berbeda kita masih bisa mengakalinya dengan mengatur lebar window excel secara manual lalu meletakkannya satu di kiri, dan satu di kanan. Tapi bila yang akan dibandingkan dua sheets dalam satu file lebih repot lagi (bagi yang belum tahu tipsnya) karena by default windownya cuma satu.

Few days ago, the old tricks that I had kept for a long time finally used. Sometimes we need to compare two sheets in excel at once. The sheets could be located in two different files or in the same file. When we found it in the different files we still can handle it by resize the excel window manually and place it in the left and right of our screen. But when they are located in the same file it will raise difficulty (for the one who don’t know this tips) because by default there is only one window.

Misalnya pada gambar di atas kita ingin membandingkan data di Sheets Jakarta dan Surabaya. Bila tampilannya seperti ini maka kita harus klik Sheets Jakarta, cek datanya, lalu klik Sheets Surabaya, cek data lagi, balik lagi ke Jakarta, dan seterusnya. Tentu akan sangat merepotkan. Jadi kita akan gunakan tips ini untuk membuat tampilannya lebih mudah dilihat. Klik Window – New Window. Maka jadilah yang tadinya hanya ada satu window excel sekarang ada dua. Tinggal teman-teman atur yang satu buka Sheets Jakarta dan yang satu buka Sheets Surabaya. Langkah terakhir klik Window – Compare Side by Side With… Nah jadi deh tampilannya enak dilihat seperti gambar di bawah ini.

For example, in the figure above, we want to compare the data in sheets Jakarta and Surabaya. If the view is just like that figure, it will be very troublesome. We need to click sheets Jakarta, check the data, click sheets Surabaya, and check the data again, click sheets Jakarta again, and again, and again. So we need this tips to make the view more comfortable. Click Window – New Window and you will find that now there are two excel window. In the first window open sheets Jakarta and the second window sheets Surabaya. The last step click Window – Compare Side by Side With… And the window become easier to look at just like the figure below.

Lebih gampang bukan ngelihatnya. Selamat mencoba. Semoga bermanfaat.

The view is nicer, isn’t it. Have a nice try. Hopefully useful.

Tips & Tricks Excel: Bikin Batas Baris dan Kolom di Excel (Customizing The End of Row and Column in Excel) July 12, 2010

Posted by Admin in Tutorials, Tips & Tricks.
Tags: , , , , , , ,
10 comments

Hi temans, sorry nih udah lama ga nulis. Minggu-minggu kemarin sibuk banget ngerjain thesis sama beberapa kerjaan yang lagi deadline. Jadi baru sekarang nambah postingan lagi di sini, ini pun cuma postingan pendek, tapi semoga bermanfaat.

Hi Friends, I’m sorry It has been a long time not posting anything in this blog. Last few weeks was a very busy weeks. I need to finish my thesis writing and some works that will be in the deadline shortly. So I can post my new material today. It is the short one, but hopefully useful for us.

Pernah lihat sheets seperti ini? Yang batas baris sama kolom udah di set sendiri?

Have you ever seen Excel screen below? Which the limits of Row and Column are customized?

Menarik kan? Ternyata meskipun trik ini tidak sulit masih banyak orang yang belum tahu. Jadi ini saya bagikan triknya supaya tampilan excel kita semakin bagus dan menarik. Caranya cukup mudah:

  1. Untuk membuat batas di baris (di gambar hanya sampai baris ke 13) blok baris ke 14 lalu klik Ctrl + Shift + panah bawah. Maka baris ke 14 ke bawah akan terblok. Setelah itu klik kanan – lalu pilih hide.
  2. Untuk membuat batas di kolom (di gambar hanya sampai kolom E) blok F lalu klik Ctrl + Shift + panah kanan. Maka kolom F ke kanan akan terblok. Setelah itu klik kanan – lalu pilih hide.

It is interesting, isn’t it? Even though this is a not difficult trick, not everyone know it. So in this post I will share how to do it. I hope It will make our Excel view is getting better and more interesting. The steps is quite simple:

  1. To make the Row limit (only until Row 13 in the picture) select Row 14 and then press Ctrl + Shift + down arrow. Row 14 to the end will be selected. After that, right click your mouse on the selected row and choose hide.
  2. To make the Column limit (only until Column E in the picture) select Column F and then press Ctrl + Shift + right arrow. Column F to the end will be selected. After that, right click your mouse on the selected column and choose hide.

Nah sudah selesai. Mudah bukan, selamat mencoba. Semoga bermanfaat.

It is finished now. It’s simple, isn’t it. Have a nice try. Hopefully useful.

Tips Membuat Data Berulang di Excel: Manfaatkan Fungsi Index() dan Row() June 3, 2010

Posted by Admin in Tutorials, Tips & Tricks.
Tags: , , , , , ,
9 comments

Teman-teman kemarin ada salah seorang teman yang punya masalah seperti gambar di bawah ini. Jadi dia punya list data seperti tabel yang ada di kiri, lalu dia pengin mendapatkan tabel seperti yang ada di kanan, yaitu sama seperti tabel yang ada di kiri, hanya saja untuk setiap kode dia perlu untuk mengulangnya beberapa kali (dalam contoh diulang 3 kali). Kalau datanya cuma 5 tentu tidak masalah tinggal dicopy2 manual juga bisa. Masalahnya itukan data saham yang jumlahnya ratusan (sekitar 400) jadi teman kita ini perlu rumus yang bisa otomatis menghasilkan tabel yang ada di kanan.

Sebenernya pertanyaan temen kita tadi sudah saya jawab, tapi biar seru gimana kalau tips ini kita jadiin kuissssss. Jadi mungkin ada rekan-rekan yang mau mencoba, bisa menshare caranya masing-masing. Jadi kita punya banyak solusi. Gimana setuju ndak….. Oke deh setuju…. Jadi jawabannya saya pending sampai Senin ya 7 Juni 2010. Sementara kalau ada yang ingin mencoba menshare bisa di kolom comment, nanti kita pilih yang paling keren dan paling inovatif …. Selamat mencoba….

Jawabannya adalah….

Nah karena udah senin, seperti janji saya kemarin saya tulis solusinya di sini. Rumus yang dipakai di E3 adalah:

=INDEX($B$3:$B$7,(ROW()-0)/3)

Penjelasannya begini, fungsi INDEX() itu untuk mengambil salah satu nilai pada suatu baris atau kolom dari suatu range tertentu. Pada rumus di atas berarti range yang akan kita ambil nilainya adalah range B3:B7 (data sumber) lalu yang kita ambil adalah baris ke (ROW()-0)/3. Angka 3 itu karena cells yang akan kita isi kan loncat tiga-tiga, kalau loncatnya lima-lima ya berarti /5. Angka 0 itu untuk penyesuaian, kalau misalnya kita mulai di E5 (bukan di E3) maka harus begini isinya (ROW()-2)/3 karena dari rumus itu kan kita pengin dapet angka 1, kalau di E5 kan ROW() menghasilkan angka 5 jadi biar bisa di bagi 3 ya kurangi dulu dengan 2. Kerena kebetulan itu cells pertama di E3 maka dikurangi 0 aja, kenapa tetep saya tampilkan ya buat pembelajaran aja.

trus rumus di E4 tinggal =E3, trus rumus E5 ya =E4 (atau kalau banyak tinggal copy yang di E4 itu). Nah lalu ini kan cuma dapet yang baris pertama aja tiga kali (ANTM), supaya bisa semua tinggal blok tiga cells tersebut (E3:E4) lalu blok ke bawah sebanyak 3 (berapa kali mau ditampilkan) kali 5 (sejumlah baris di tabel pertama) jadi blok 15 baris. Setelah itu tinggal dipaste. Langkah ini penting banget soalnya kalau yang di blok hanya 14 baris ke bawah atau kelebihan 16 baris (bukan kelipatan 3) ke bawah maka akan gagal.

Di atas adalah cara yang saya pakai, kelihatannya langkah-langkahnya banyak ya, tapi buat saya lebih cepet soalnya rumus di E3nya lebih simple. Tapi ada juga lho cara yang langkahnya lebih gampang tinggal sekali tulis rumus di E3 alalu copy aja ke bawah, tapi ya rumusnya lebih rumit. Begini nih rumusnya:

=INDEX($B$3:$B$7,ROUNDDOWN((ROW()-0)/3,0))

Penjelasannya sama dengan di atas, cuma biar ga perlu langkah ribet kita bikin ROUNDDOWN() jadi biar di E3, E4, maupun E5 fungsi (ROW()-0)/3 akan sama-sama menghasilkan angka 1. Dengan begini tidak perlu repot-repot kaya langkah di atas cukup copy cell E3 yang ada rumusnya tadi lalu copy ke bawah secukupnya. Kalau begini ga harus tepat di blok 15 baris ke bawah, kalaupun salah tinggal menghapus atau menambah.

Eh ternyata setelah percobaan-percobaan, rumus INDEX() itu sudah otomatis kalau angka yang dimasukkan pecahan akan membulatkan ke bawah jadilah rumus di atas cukup ditulis:

=INDEX($B$3:$B$7,(ROW()-0)/3)

Ternyata rumus dan langkahnya malah lebih mudah ya dari yang alternatif pertama. He…he… namanya juga sama-sama belajar. Semoga bermanfaat.

Mencari/Menghitung Selisih (Pengurangan) 2 (dua) Tanggal Dalam Satuan Tahun, Bulan, Hari: Gunakan Datedif May 5, 2010

Posted by Admin in Tutorials, Tips & Tricks.
Tags: , , , ,
15 comments

Pertanyaan:

tanya dong, bagaimana rumusnya jika saya ingin mengetahui hasil pengurangan dalam bentuk tanggal, misal : 01/01/2010 dikurangi 01/01/1999 hasilnya dalam jumlah bulan atau hari Bagaimanakah rumusnya, thx atas jawabannya

Jawaban:

Bisa pakai rumus datedif. =datedif(tanggal1, tanggal2, kode)

tanggal1 adalah tanggal yang lebih awal dari tanggal2.

Kode untuk menentukan output apa yang dikeluarkan.

  • y untuk menentukan beda tahun
  • m untuk menentukan beda bulan
  • d untuk menentukan beda hari
  • ym untuk menentukan beda bulan (tanpa menghitung tahun)
  • yd untuk menentukan beda hari (tanpa menghitung tahun)
  • md untuk menentukan beda hari (tanpa menghitung tahun dan bulan)

kadang hasilnya berformat tanggal, jadi aneh, sebenarnya itu hanya masalah format, hasilnya sudah benar, tinggal diganti aja formatnya menjadi general atau angka.

Apa beda d sama yd atau md?

  • contoh =datedif(1 april 2010, 16 mei 2010,”d”) hasilnya 45 (dihitung benar-benar beda berapa hari)
  • contoh =datedif(1 maret 2010, 16 mei 2010,”md”) hasilnya 15 (cuma dihitung selisih tanggal aja 16 – 1 bulan tidak diperhatikan)
  • contoh =datedif(16 april 2009, 16 mei 2010,”yd”) hasilnya 30 (cuma dihitung selisih tanggal aja 16 mei – 16 april tahun tidak diperhatikan)

Jadi mungkin kalau mau lebih bagus tampilannya di beri tambahan begini

=datedif(1 april 2010, 16 mei 2010,”m”)&” bulan”&datedif(1 april 2010, 16 mei 2010,”md”)&” hari”

hasilnya “1 bulan 15 hari”

Untuk penjelasan lebih lengkap dan contoh lebih banyak bisa dilihat di link di bawah ini

http://www.cpearson.com/excel/datedif.aspx

Semoga bermanfaat.

Gunakan Conditional Formatting dan Validation Untuk Mempercantik Form Isian Excel Anda (Using Conditional Formatting and Validation to Make Your Excel Looks More Interesting) March 30, 2010

Posted by Admin in Tutorials, Tips & Tricks.
Tags: , , , , ,
8 comments

Kali ini kita akan belajar menggunakan Conditional Formatting dan Validation untuk mempercantik form atau template isian di Excel. Salah satu pekerjaan saya saat ini di lembaga konsultan adalah membuat materi untuk training atau workshop. Tentunya materi dengan tampilan menarik akan semakin menambah minat peserta dan mengurangi kebosanan, apalagi jika dihadapkan pada isian form atau template yang banyak. Nah alhamdulillah Excel menyediakan feature Conditional Formatting dan Validation ini yang bisa kita manfaatkan.

Now, we are going to learn how to use conditional formatting and validation to make our Excel form and input template more interesting. One of my recent jobs in a consulting agency is preparing training and workshop material. The more interesting the material, the more interested the participant on the training. And it can prevent the participant from boredom as well, especially when there are so many materials to finish. Alhamdulillah, Excel has provided Conditional Formatting and Validation feature that we can utilize.

Pada gambar di atas terlihat ada isian untuk melihat kinerja. Jika belum diisi maka warna cells isiannya putih, tapi ketika diisi kinerja jelek, misalnya 1 atau 2 warnanya berubah menjadi merah dan apabila diisi bahwa kinerja bagus, misalnya angka 3 atau 4 warna cells otomatis berubah menjadi hijau. Kita dapat membuat yang seperti ini menggunakan Conditional Formatting. Oh ya kalau teman-teman perhatikan juga, untuk mengisi cells tersebut teman-teman bisa mengetik angka langsung tapi bisa juga meng-klik dari pilihan dropdown. Untuk membuatnya diperlukan Validation dengan type yang list.

The above figure shows us a form to evaluate performance. When there is no input, the cells color will be white, but when it contains bad performance, for example 1 or 2 scale, the color change to red. When we fill it with good performance (3 or 4 scale) the cells color automatically will change to green. We can make such form using Conditional Formatting. If we pay attention on the way we can fill the cells, we will see that we can either, manually type the number or just choose from the drop down menu. To make that drop down menu we need to use Validation and choose “list” type.

Yang perlu temen-temen lakukan adalah:

  1. Blok Range C4:C8. Lalu pilih Format – Conditional Formatting
  2. Kita akan membuat supaya kalau nilainya 3 atau 4 berwarna hijau. Di condition 1 ganti yang “between” dengan “greater than or equal to” lalu masukkan angka 3. Setelah itu klik Format lalu masuk ke Pattern dan pilih warna hijau lalu OK.
  3. Setelah itu kita buat kondisi kedua, yaitu berwarna merah jika nilainya 1 atau 2. Pilih Add>> terlebih dahulu.
  4. Setelah itu tampil condition 2. “Between” tidak usah diganti tinggal dimasukkan angka 1 dan 2 lalu klik Format masuk ke Pattern pilih warna merah.
  5. Nah Conditional Formatting kita sudah selesai klik Ok.
  6. Selanjutnya kita akan membuat Validation aga bisa muncul dropdown yang bisa diklik. Caranya pilih Data – Validation. Dibagian Allow: pilih “list” lalu akan muncul input untuk Source.
  7. Di Source anda blok range yang berisi angka 1 sampai 4 (oh iya sebelumnya ketik dulu 1 lalu di bawahnya 2, 3 dan 4 di suatu tempat sebagai bantuan) klik Ok.
  8. Nah Validation pun selesai.

What we need to do are:

  1. Select range C4:C8, and then choose Format – Conditional Formatting.
  2. We will try to make the cells color automatically change to green when we fill it with 3 or 4. In “Condition 1” change “between” option to “greater than or equal to”, and then fill it with 3. It means we want that the following format will be applied when the cells is filled with 3 or greater number. And then click Format – Pattern and choose Green, OK.
  3. After that we will make condition when the cells contain 1 or to the color will be red. First, click Add>>.
  4. After condition 2 appear, keep the “between” option and fill it with 1 and 2 (what happen if we choose “less then or equal to”? just try it). And then click Format – Pattern and choose red.
  5. At this step our Conditional Formatting is ready. Next, we are going to make Validation feature.
  6. First type 1 in any empty unused cells, and then type 2, 3, and 4 below it respectively.
  7. Select C4:C8 again, and then choose Data – Validation. In the “Allow” option choose “list”. The “source” input will appear, select range contained 1 to 4 from previous step, and click OK.
  8. And we have just finished the Validation.

Selamat mempercantik tampilan worksheet anda. Semoga Bermanfaat.

Have a nice try to make your sheets more interesting. Hopefully helpful.