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.

Mengunci Panjang Karakter di Excel (Limiting The Length of Character in Excel) February 9, 2010

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

Pas lihat dashboard wordpress, ternyata ada yang search di google “mengunci panjang karakter di excel” lalu sampailah ke blog ini. Pertama saya mohon maaf buat yang search barusan karena anda tidak bisa mendapatkan apa yang anda cari di sini. Selanjutnya untuk menebus kekurangan tersebut, berikut saya paparkan cara untuk mengunci panjang karakter di excel. Dan terima kasih buat yang search tadi sehingga ada tulisan baru yang bisa saya post yang semoga berguna buat temen-temen yang lain.

When I saw my wordpress dashboard, I found that someone had searched from google the following phrase “limiting the length of characters in excel” and came into my blog. First, I want to apologize to the searcher that he could not find what he needed. So as compensation, the following post will explain how to limit the length of characters in excel. And thanks to the searcher that I can write this new post that I hope could benefit others.

Untuk mengunci panjang karakter pertama blok dulu cell yang ingin anda kunci kemudian anda klik Data – Validation. Di bagian Allow: Anda pilih Text Length, maka akan muncul isian untuk Minimum dan Maximum. Nah anda bisa membatasi panjang karakter dengan mengetikkan suatu angka pada input Maximum. Setelah itu klik OK. Dengan validasi ini maka jika ada yang mengetikkan lebih dari jumlah karakter Maximum tadi Excel akan menampilkan pesan error.

To limit the length of characters in excel, select the cells that you want to limit and click Data – Validation. In Allow: section choose Text Length. There will appear Minimum and Maximum input. To limit the length of the cells fill the Maximum input and click OK. With this validation whenever someone fill the cells exceed the maximum character that you have set, the error message will appear.

Nah supaya lebih bagus sebelum OK tadi anda bisa membuka tab Input Message dan Error Alert. Input Message berfungsi untuk memberi petunjuk kepada orang yang akan mengisi, jadi ketika cell yang anda kunci tadi di klik maka akan muncul pesan anda ini. Misalkan pesannya berbunyi “Masukkan kata dengan panjang tidak lebih dari 7 karakter”. Error Alert berfungsi untuk memberitahu jika isiannya melebihi batas tadi. Misalnya kalau pengisi tetap kekeuh mengisi kata-kata sebanyak 10 karakter maka ketika di enter maka akan tampil pesan anda, misalnya “Maaf jumlah karakter yang anda masukkan terlalu banyak. Masukkan kata dengan panjang tidak lebih dari 7 karakter”.

To make it more interesting, you can add customized message. Open Input Message and Error Alert. Input Message is used to give clue to someone who will fill it, so whenever he is going to fill the cell your message will appear. For example you can give the following message, “Fill a word which is less than 7 characters”. Error Alert is used to inform when the word filled is more than the limit. For example when people fill a word with 10 characters and click enter, your message will appear, “Sorry you have entered a word with too many characters. Fill a word with no more than 7 characters”.

Selamat Mencoba. Semoga bermanfaat.

Have a nice try. Hopefully useful.