Kamis, 26 Maret 2015

[belajar-excel] Digest Number 3360

2 Messages

Digest #3360

Messages

Wed Mar 25, 2015 5:29 am (PDT) . Posted by:

"Fatania Latifa"

Dear all,

Saya ingin bertanya, apakah bisa VBA digunakan untuk menghitung
formula-formula tetapi sumbernya dari 3 file yang berlainan. Jadi total ada
4 file. Tidak bisa dijadikan 1 file karena datanya terlalu besar. Mohon
solusinya

Wed Mar 25, 2015 7:34 pm (PDT) . Posted by:

"Zulfikar N" zulfikar_route66

Hehehe.. Terima kasih banyak atas pencerahannya.

SalamZulfikar.

Pada Jumat, 20 Maret 2015 19:08, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  hmmm...

formulanya lemot karena karena diminta bekerja terhadap seluruh cells dalam suatu kolom.
=IFERROR(INDEX(BOSNET!$AM:$AM,SMALL(IF((BOSNET!$A:$A='db Retur'!$E$2)*(BOSNET!$B:$B='db Retur'!$E$3),ROW(BOSNET!$AM:$AM)-ROW(BOSNET!$AM$2)+2),ROWS($A$2:$A2))),"")

Kesalahan utama dalam menggunakan array formula, terutama pada inputan berupa array adalah seperti yang dikuning di atas, yaitu merujuk ke semua cells dalam kolom tersebut.

Pada array formula atau formula yang diberi inputan array, berapapun jumlah cells nya akan dikomputasi.
Pada formula di atas, jika menggunakan Excel 2003 yang berisi 65000 baris, maka formula di atas akan mengkomputasi 65000 baris di setiap kolom yang dikuning, walau cells yang terisi data baru 1 bijik record saja.

Bagaimana dengan Excel 2007 ke atas yang ada 1juta baris ?

Tentu Excel akan terkesan lemot dalam bekerja. dan berulang kali, selalu ditekankan bahwa yang lemot bukan Excel-nya, tapi user yang membuat formula tersebut.

Bagaimana supaya menjadi lebih cepat ?
1. ganti semua rujukan yang dikuning agar merujuk ke baris yang ada datanya saja. Jika data mulai baris 2 dan baru ada 3 data, berarti rujukannya dari baris 2 sampai baris 4 saja. bunyi rujukan $am:$am akan menjadi $am$2:$am$4.
     Kalau recordnya berubah menjadi sampai baris 10 Excel bagaimana ?
     >> ya tentu saja ubah lagi formulanya agar merujuk sampai baris 10

     Gak praktis dong.
     >> iya, memang gak praktis kalau begitu terus menerus. Kalau mau lebih praktis, buatlah nama range yang merujuk secara dinamis.

2. ganti rujukan yang dikuning agar merujuk ke suatu nama range yang dinamis.
    Misal untuk kolom AM dibuat nama range bernama KolomAM yang dirujuk dengan formula =$AM$2:index( $AM:$AM , counta( $a:$a ) )
    dengan asumsi, bahwa kolom A selalu terisi alias kolom kunci yang tidak boleh kosong disetiap record data.
    Jadi nanti di array formula, semua bunyi yang merujuk ke range AM:AM diubah menjadi KolomAM (nama range yang dirujuk dinamis alias nama range yang dinamis)

    Wah... nama range-nya harus dibuat dulu dong per kolom yang dipakai. Ada banyak sheet dan per sheet ada banyak kolom nih... Jadi capek bikin nama range-nya. Mana gak mudeng pula...
    >> hmmm... capek juga ngasih saran solusinya kellezz...

3. Pakailah fitur List (xl2003) atau Excel Table (xl2007 ke atas) supaya rujukan formula bisa dinamis.
>> gak pakai bikin nama range dinamis lagi kan... jadi gak ada alasan bakal capek bikin nama range...
>> alasannya apa lagi ?

4. Walau sudah dirujuk gak sekolom (alias ke baris berdata saja) atau sudah pakai nama range dinamis ataupun pakai object excel table, suatu saat array formula tersebut akan membuat kesan Excel bekerja lemot lagi, yaitu ketika record datanya sangat banyak. Cobalah manfaatkan kolom-kolom bantu atau tabel-tabel bantu yang disusun dengan formula sederhana.

5. solusi nomor 4 itu pun suatu saat akan membuat Excel terkesan lemot lagi... Kalau bisa, tabel bantu atau kolom bantu nya pakai fitur Pivot Table.

>> yaaaa..... harus belajar lagi... capek ah belajarnya
>> hehehe... capek juga ngajak belajarnya

6. suatu saat, tabel bantu atau kolom bantu pakai pivot table menjadi gak efisien dan Excel terkesan ada lag output... Andai memungkinkan, ubah sedikit layout outputnya supaya bisa tanpa tabel bantu dan langsung disusun dengan pivot table.

7. solusi 6 akan ada maksimumnya, yaitu ketika datanya sangat banyak dan pivot tablenya mulai terasa berat dalam loading data. Mulailah memanfaatkan fitur Get External Data untuk memilah data yang pokok dan membentuknya menjadi tabel sumber data pivot table yang siap pakai dengan jumlah record yang khusus per layout output.

8. solusi 7 masih bisa menjadi lambat suatu saat nanti, maka mulailah memindahkan data ke database, seperti ke Access, MySQL, Oracle, MS SQL Server, PostgreSQL, dsb, lalu manfaatkan fitur Get External Data lagi. Tapi kali ini yang disuruh kerja keras adalah si database dan bukan Excel lagi.

9. solusi 8 masih bisa lemot lagi (percaya deh), maka mulailah membuat sistem database pengolahan data. Excel bisa dijadikan area output jadinya saja.

10. kalau nomor 9 ini masih lemot juga, maka ganti developer yang membangun sistem databasenya, karena ndak memberi peringatan akan keseimbangan antara infrastruktur dan data yang harus diolah.

11. kalau nomor 10 gagal juga. duduk manis di meja kerja dan nikmatilah masa menunggu setiap proses kerja yang dilakukan. Alihkan waktu menunggu dengan sesuatu yang lebih bermanfaat, seperti chating dengan gadget, online di sosmed, atau belajar.... (prioritasnya mengikuti kebanyakan manusia loh ya, jadi jangan protes). Tapi kayanya lebih bagus kalau prioritasnya ndak begitu.

12. sudah capek ngetiknya dan kayanya ndak ada pokok benang merahnya wakakkakaka....

ups... VBA nya dimana ya ? jangan-jangan terselip n jatuh entah dimana...
Barang Siapa yang menemukannya, mohon dipasang di nomor 1 sampai 9 sebagai pelengkap solusi setiap nomornya.

;)

jangan diambil ati ya...
tulisan di atas itu cuman iseng manasin jari malem-malem di temperatur 9 derajat  :]

Wassalam,
Kid

On Fri, Mar 20, 2015 at 9:35 PM, Zulfikar N zulfikar_route66@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote:

  Dear Excel Master,Mohon bantuannya, apakah ada fungsi lain untuk menggatikan fungsi INDEX pada formula berikut:
{=IFERROR(INDEX(BOSNET!$AM:$AM,SMALL(IF((BOSNET!$A:$A='db Retur'!$E$2)*(BOSNET!$B:$B='db Retur'!$E$3),ROW(BOSNET!$AM:$AM)-ROW(BOSNET!$AM$2)+2),ROWS($A$2:$A2))),"")}

SalamZulfikarPalopo, Sulsel


#yiv9126707347 #yiv9126707347 -- #yiv9126707347ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv9126707347 #yiv9126707347ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv9126707347 #yiv9126707347ygrp-mkp #yiv9126707347hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv9126707347 #yiv9126707347ygrp-mkp #yiv9126707347ads {margin-bottom:10px;}#yiv9126707347 #yiv9126707347ygrp-mkp .yiv9126707347ad {padding:0 0;}#yiv9126707347 #yiv9126707347ygrp-mkp .yiv9126707347ad p {margin:0;}#yiv9126707347 #yiv9126707347ygrp-mkp .yiv9126707347ad a {color:#0000ff;text-decoration:none;}#yiv9126707347 #yiv9126707347ygrp-sponsor #yiv9126707347ygrp-lc {font-family:Arial;}#yiv9126707347 #yiv9126707347ygrp-sponsor #yiv9126707347ygrp-lc #yiv9126707347hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv9126707347 #yiv9126707347ygrp-sponsor #yiv9126707347ygrp-lc .yiv9126707347ad {margin-bottom:10px;padding:0 0;}#yiv9126707347 #yiv9126707347actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv9126707347 #yiv9126707347activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv9126707347 #yiv9126707347activity span {font-weight:700;}#yiv9126707347 #yiv9126707347activity span:first-child {text-transform:uppercase;}#yiv9126707347 #yiv9126707347activity span a {color:#5085b6;text-decoration:none;}#yiv9126707347 #yiv9126707347activity span span {color:#ff7900;}#yiv9126707347 #yiv9126707347activity span .yiv9126707347underline {text-decoration:underline;}#yiv9126707347 .yiv9126707347attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv9126707347 .yiv9126707347attach div a {text-decoration:none;}#yiv9126707347 .yiv9126707347attach img {border:none;padding-right:5px;}#yiv9126707347 .yiv9126707347attach label {display:block;margin-bottom:5px;}#yiv9126707347 .yiv9126707347attach label a {text-decoration:none;}#yiv9126707347 blockquote {margin:0 0 0 4px;}#yiv9126707347 .yiv9126707347bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv9126707347 .yiv9126707347bold a {text-decoration:none;}#yiv9126707347 dd.yiv9126707347last p a {font-family:Verdana;font-weight:700;}#yiv9126707347 dd.yiv9126707347last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv9126707347 dd.yiv9126707347last p span.yiv9126707347yshortcuts {margin-right:0;}#yiv9126707347 div.yiv9126707347attach-table div div a {text-decoration:none;}#yiv9126707347 div.yiv9126707347attach-table {width:400px;}#yiv9126707347 div.yiv9126707347file-title a, #yiv9126707347 div.yiv9126707347file-title a:active, #yiv9126707347 div.yiv9126707347file-title a:hover, #yiv9126707347 div.yiv9126707347file-title a:visited {text-decoration:none;}#yiv9126707347 div.yiv9126707347photo-title a, #yiv9126707347 div.yiv9126707347photo-title a:active, #yiv9126707347 div.yiv9126707347photo-title a:hover, #yiv9126707347 div.yiv9126707347photo-title a:visited {text-decoration:none;}#yiv9126707347 div#yiv9126707347ygrp-mlmsg #yiv9126707347ygrp-msg p a span.yiv9126707347yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv9126707347 .yiv9126707347green {color:#628c2a;}#yiv9126707347 .yiv9126707347MsoNormal {margin:0 0 0 0;}#yiv9126707347 o {font-size:0;}#yiv9126707347 #yiv9126707347photos div {float:left;width:72px;}#yiv9126707347 #yiv9126707347photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv9126707347 #yiv9126707347photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv9126707347 #yiv9126707347reco-category {font-size:77%;}#yiv9126707347 #yiv9126707347reco-desc {font-size:77%;}#yiv9126707347 .yiv9126707347replbq {margin:4px;}#yiv9126707347 #yiv9126707347ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv9126707347 #yiv9126707347ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv9126707347 #yiv9126707347ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv9126707347 #yiv9126707347ygrp-mlmsg select, #yiv9126707347 input, #yiv9126707347 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv9126707347 #yiv9126707347ygrp-mlmsg pre, #yiv9126707347 code {font:115% monospace;}#yiv9126707347 #yiv9126707347ygrp-mlmsg * {line-height:1.22em;}#yiv9126707347 #yiv9126707347ygrp-mlmsg #yiv9126707347logo {padding-bottom:10px;}#yiv9126707347 #yiv9126707347ygrp-msg p a {font-family:Verdana;}#yiv9126707347 #yiv9126707347ygrp-msg p#yiv9126707347attach-count span {color:#1E66AE;font-weight:700;}#yiv9126707347 #yiv9126707347ygrp-reco #yiv9126707347reco-head {color:#ff7900;font-weight:700;}#yiv9126707347 #yiv9126707347ygrp-reco {margin-bottom:20px;padding:0px;}#yiv9126707347 #yiv9126707347ygrp-sponsor #yiv9126707347ov li a {font-size:130%;text-decoration:none;}#yiv9126707347 #yiv9126707347ygrp-sponsor #yiv9126707347ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv9126707347 #yiv9126707347ygrp-sponsor #yiv9126707347ov ul {margin:0;padding:0 0 0 8px;}#yiv9126707347 #yiv9126707347ygrp-text {font-family:Georgia;}#yiv9126707347 #yiv9126707347ygrp-text p {margin:0 0 1em 0;}#yiv9126707347 #yiv9126707347ygrp-text tt {font-size:120%;}#yiv9126707347 #yiv9126707347ygrp-vital ul li:last-child {border-right:none !important;}#yiv9126707347

============================================================
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com

posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com

berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages

melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/

menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com

keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------

Tidak ada komentar:

Posting Komentar