jump to navigation

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.

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.

Ayo Buat Sendiri Shortcut Keyboard Excel-Mu Sendiri Dengan Macro March 8, 2010

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

Kita bisa buat shortcut keyboard sendiri lho di excel. Mau tahu caranya? Baca terus halaman ini. Kamu bisa buat shortcut untuk ngasih warna cell menjadi biru, terus tulisannya putih, terus ada garisnya dengan satu shortcut. Belum pernah kan ada shortcut buat paste special – value, atau paste special – formula, atau format, kamu bisa membuatnya sendiri lho. Jadi ayo buat sendiri shortcut keyboard excel-mu sendiri biar pekerjaan kita lebih mudah, efektif dan efisien.

Pertama saya buat shortcut sendiri ini pada saat saya harus membuat header dari semua tabel (yang buanyak buanget gitu) berwarna biru dan fontnya berwarna putih. Kalau ga ada shortcut berarti saya harus klik warna biru lalu klik warna font putih (apalagi kalau diminta fontnya ukurannya lebih besar berarti harus klik juga size ketik 14). Dan itu harus dilakukan berkali-kali, saya jadi inget kenapa ga bikin shortcut sendiri… Okelah kalau begitu kita buat shortcutnya. Coba lihat gambar di bawah ya.

  • Pertama blok range A2:C2 (anggap digambar itu belum biru ya, he…he…he…)
  • Lalu jangan langsung diberi warna tapi pilih dulu tools – macro – record new macro untuk mulai merekam makro.

  • Lalu muncul form untuk macro, kasih nama biru misalnya lalu yan penting di shortcut kamu ketik huruf tertentu disini saya pilih V jadi saya ketik Shift + V. Kenapa pake shift karena kalau tanpa shift kebanyakan huruf udah di pakai sama excel kaya Ctrl + c buat copy Ctrl + v buat paste jadi amannya sih pakai Shift. Lalu klik OK.

  • Langkah berikutnya baru kasih warna biru lalu fontnya anda kasih warna putih.
  • Langkah terakhir adalah matikan rekaman makronya dengan cara klik tools – macro – stop recording. Nah selesai sekarang kita punya shotcut untuk mewarnai cell menjadi biru dan fontnya putih dengan klik Ctrl + Shift + V.

Untuk membuat shortcut paste special – value only caranya juga sama:

  • copy dulu di sembarang tempat. Lalu pilih sembarang tempat yang akan dipaste special
  • rekam makro dengan klik tools – macro – record new macro.
  • kasih nama value misalnya, lalu di shortcut ketik Shift+P. Ok
  • lalu paste special – value only.
  • matikan makro klik tools – macro – stop recording.
  • kita sudah punya shortcut untuk paste special – value only deh…

Selamat mencoba. Semoga bermanfaat.

Recommended Posts
Perindah Grafik Excelmu: Yuk Masukin Gambar ke Grafik…
Download E-book Excel Berisi Tutorials, Tips, and Tricks Keren
Rumus Mengetahui Huruf Depan (Maupun Kata Baik di Depan, Tengah, atau Belakang)
Menaruh Angka 0 (Nol) di Depan Angka Dalam Excel
Merapikan Data Excel Dengan Cepat – Maksimalkan Find & Replace plus WildCard Character

Download E-book Excel Berisi Tutorials, Tips, and Tricks Keren (Download Links of Excel e-books Containing Cool Tutorials, Tips, and Tricks) February 27, 2010

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

Teman-teman beberapa hari lalu ada yang comment:

“kirmin dong….cara berlatih exel yang cepat dan akurat….trims…..and bagi semuanya doain aku agar cepat bisa”

Hi friends, few days ago someone gave a comment asking me:

“could you send me…. how to practice Excel quickly and accurately… thanks… and for all, wish me able to master soon”

Saya jawab:

I answered:

“Tipsnya: praktek + baca-baca buku + buka-buka blog excel (misalnya blog-blog excel the master yang linknya ada di blog saya) + jangan ragu tanya kalau ada kesulitan.

Tips baca buku excel: Baca daftar isi dulu kalau nemu hal yang baru dan menarik baru buka halaman terkait. Biasanya halaman awal masih perkenalan sih, baru beberapa bab berikutnya ada yang menarik. Jangan takut lupa caranya, yang penting inget tentang apa, toh nanti kalau di rumah bisa search di internet kan.

Untuk e-book saya ada beberapa tapi guedhe, ga kuat kalau ngirim, search dan download aja di gigapedia.org. Gratis koq, tapi register dulu baru bisa download-download. Kalau males nyari tunggu postingan saya berikutnya, kalau ada waktu saya cariin link downloadnya.

Ok, semoga dimudahkan belajar excelnya supaya cepat bisa.”

“The tips: keep practicing, read excel books, open excel blogs (some of which are linked on this blog), and don’t hesitate to ask whenever you get trouble.

Tips for reading excel books: See the table of content, when you find something new or interesting open that chapter. Usually the beginning of the book is just introduction. If you have been familiar with excel you can skip this part and continue to the next chapter which usually you can find something interesting in it. Don’t worry not to remember the way you do that tricks in the books. The main point is that you know what you can do or benefit with that tricks. You can always find the way later when you need it, through internet or reopening your books, right.

I have some nice e-book, but the size is too big that I can’t upload it to the internet. Just search and download it from gigapedia.org. It’s free, you just need to register to download the content. If you are reluctant to search, just wait my next post. When I have spare time I will search the download link for you.

Nah sekarang download link e-book excelnya sudah tersedia teman-teman silahkan download beberapa e-book di bawah ini. (e-book ini di dapat dari gigapedia.org, kebijakan di sana kalau tidak dizinkan oleh penerbit atau pengarangnya maka download e-booknya dihapus. Karena sampai sekarang download link masih ada maka saya anggap e-book ini tidak bermasalah. Jika ada yang komplain mengenai hak cipta dan sebagainya silahkan merujuk ke gigapedia.org):

Now, the download link of excel e-book is available, friends. Feel free to download the following e-books. (this e-book is from gigapedia link whose policy is deleting the link that is not permitted by the publisher or the writer. When I search them, it still available so I assume that there is no problem with the permission. If any one complaining on the copyright issue please feel free to contact gigapedia.org)

1. Microsoft Office Excel 2003 Bible:

Buku ini berisi hampir semua pengetahuan tentang Excel, baik yang masih dasar sampai yang sudah Advance. Jika anda menguasai semua yang ada di buku ini, dan bisa mengaplikannya, berarti anda sudah sangat menguasai Excel.

This book contains almost all about excel, from the basic to the advance. If you master anything in this book and be able to apply, it means you definitely have mastered excel.

download disini (here)

2. Learn Excel From Mr. Excel: 277 Excel Misteries Solved:

Buku ini berisi tutorials, tips, and tricks Excel dengan kelas Menengah hingga Advance yang keren-keren.

This book contains cool tutorials, tips, and tricks on excel from intermediate to advance.

download disini (here)

3. Microsoft Excel Office Chart:

Buku ini berisi tutorials, tips, and tricks tentang memanipulasi Grafik di Excel sehingga menghasilkan Grafik-grafik yang keren-keren dan bervariasi serta interaktif.

This book contains tutorials, tips, and tricks on manipulating chart on excel that result cool, varying, and interactive graphs.

download disini (here)

Ok temans… semoga e-book di atas bermanfaat bagi temans semua. Oh ya ada sumber-sumber excel lain yang dilist di excelans.wordpress.com ada yang online ada juga berupa buku, tapi bukan download link hanya informasi bukunya. Temans bisa coba cari aja e-booknya di gigapedia.org siapa tahu tersedia di sana.

Ok friends… I hope those e-book can benefit all of you. Oh, there are other source of excel knowledge that you can find in excelans.wordpress.com some of them are online source and some are books, but only the information about the books not the download links. You can try to find it in gigapedia.org. Who knows it available there.

Cara Mengunci Rumus / Formula di Excel (Klik F4 aja) January 17, 2010

Posted by Admin in Tutorials, Tips & Tricks.
Tags: , , , ,
1 comment so far
Kunci Formula sumber: akhmadguntar.com

Cara Mengunci Formula

Mengunci rumus atau formula di Excel untuk apa sih? Cara praktisnya gimana? Gampang banget, klik F4 saja. Pelajari penggunaan kunci formula selengkapnya disini ya.

Mengunci Rumus / Formula Excel

Sebagai pengguna rutin excel, kemampuan mengunci rumus / formula merupakan salah satu hal yang wajib bisa. Dengan menguasai penguncian ini maka anda bisa dengan maksimal menggunakan rumus / formula excel sefleksibel mungkin tanpa susah payah. Untuk mengunci formula (tepatnya sih mengunci lokasi dalam formula) yang diperlukan adalah $ (String), jadi jika anda melihat suatu formula di sebuah cell mengandung simbol $ (String), maka artinya rumus excel tersebut sudah dikunci, yang artinya lagi jika dicopy maka dia tidak bergeser dari cell referencenya.

Cara Praktis Menuliskan $ (String) Dalam Rumus Excel

Oh ya sebelum memulai tutorial ini saya ingin menginformasikan bahwa jika anda ingin mengunci formula anda tidak perlu mengetik manual tanda $ tersebut anda cukup meletakkan cursor diformula yang ingin anda kunci kemudian klik F4, anda bisa mengklik beberapa kali sesuai dengan jenis kuncian yang anda inginkan (karena penulis sering melihat banyak yang sudah menguasai teknik penguncian formula ini tapi melakukannya dengan manual menulis tanda $).

Penjelasan Mekanisme Kunci Rumus / Formula

Penjelasan String ($) dimulai dengan penjelasan tentang mekanisme Copy – Paste pada sebuah formula. Ketika anda membuat formula di cell A3 dengan rumus “=A1*A2” lalu anda copy ke cell misalnya C4 maka formulanya akan menjadi “=C2*C3”. Lihat gambar 1.

String ($) digunakan bila anda ingin mengunci supaya cell dalam formula tidak ikut bergerak ketika anda Copy dan Paste. Jika String ($) diletakkan di depan huruf maka akan mengunci supaya tidak bergerak ke kiri atau ke kanan tapi bila diletakan di depan angka maka akan mengunci supaya tidak bergerak ke atas atau bawah tetapi masih bisa bergerak ke kanan atau ke kiri. Untuk mengunci supaya tidak bergerak kemanapun ketika diCopy Paste maka tulis String ($) di depan huruf maupun angka. Lihat Gambar 2.

kunci formula

Dalam gambar2 tersebut jika anda tidak memberi tanda String ($) maka yang terjadi adalah seperti gambar di atas yang tentu saja salah karena harganya ikut bergerak sehingga penjualan tahun ke tiga menjadi 0. Supaya hal ini tidak terjadi bisa ditambahkan String ($) di depan huruf pada cell yang berwarna biru (B1) karena kita tidak ingin cell yang berwarna biru (B1) geser ke kanan. Sehingga hasilnya bisa dilihat di gambar3.

Terlihat untuk formula cell B1nya tetap karena sudah diberi tanda String ($). Sebagai catatan untuk memberi tanda String ($) tidak perlu diketik secara manual tapi cukup arahkan pointer di tulisan B1 lalu klik F4.

Download File Contoh dan Latihan

Untuk download file penjelasan dan latihan excelnya klik link di bawah ini:

http://www.mediafire.com/file/fkz2z3dqhy5/PTI Excel1.xls

Sebenarnya di dalam file ada satu materi tambahan yaitu data table, insyaallah akan dijelaskan pada posting selanjutnya.

Recommended Posts
Cara Membuka File Excel 2007 (.xlsx) di Excel 2003
Edu-Game Keyboard Huruf Hijaiyah
Shortcut Keyboard untuk Excel
Mencari Rata-rata Tanpa Mengikutkan Angka 0 (atau Error)
Copy Sheet ke File Lain
Program Analisis Laporan Keuangan

Recommended Posts