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.

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

Mencari Rata-rata Tanpa Mengikutkan Angka 0 (atau Error) December 1, 2009

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

Kemarin ada yang nanya gimana nih mau nyari rata-rata tapi 0 (nol) ga diikutin (dihitung) soalnya kalo ikut kan jadi kecil rata-ratanya, bisa sih manual bikin kolom baru tapi kan lama.

Bisa koq dengan memanfaatkan formula untuk array. Jadi biasanya kan begini =average(A1:A5) tapi rumus diatas otomatis ikut menghitung angka 0 (nol). Supaya bisa mengeluarkan angka 0 (nol) dari perhitungan tulis rumus berikut:

=average(if(A1:A5=0,””,A1:A5))

Nah tapi karena menggunakan formula untuk array maka setelah rumus diatas selesai tekan Ctrl+Shift+Enter. Nah anda juga bisa menggunakan fungsi yang lain seperti sum, min, max, stdev, dsb.

Dengan logika yang sama maka kita juga bisa menggunakannya untuk mengatasi data yang error. Bila kita mencari rata-rata lalu salah satu datanya error (#N/A, #DIV/0!, #REF, dsb) maka hasil rata-ratanya error juga. Nah untuk mengatasinya anda juga bisa menggunakan formula array di atas dengan sedikit perubahan.

=average(if(iserror(A1:A5),””,A1:A5)) jangan lupa Ctrl+Shift+Enter

Dengan rumus tersebut maka data error akan diabaikan dalam penghitungan rata-rata.

Semoga bermanfaat.