Kamis, 15 Februari 2018

[belajar-excel] Digest Number 4430

15 Messages

Digest #4430
1a
Re: Penyimpanan server bermasalah by "Mr. Kid" nmkid.family@ymail.com
4b
Re: Get Extermal Data ( Excel 2016 ) by "Mr. Kid" nmkid.family@ymail.com
5a
Re: Format as Table by "Mr. Kid" nmkid.family@ymail.com
5b
Re: Format as Table by "CepWahyu" juragan_75
5c
Re: Format as Table by "Mr. Kid" nmkid.family@ymail.com
6a
Bls: [belajar-excel] kopi ke samping by "Fahmi Syaifullah" fahmisyaifullah20
6b
Re: kopi ke samping by "Mr. Kid" nmkid.family@ymail.com
6c
Re: kopi ke samping by "Sony Ario"

Messages

Wed Feb 14, 2018 1:28 am (PST) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

maksudnya diminta mengisi password atau login ke komputer server ?
> Kalau diminta mengisi password (bukan login), maka turn off password
protected sharing di komputer server (lihat di control panel network
sharing homegroup advanced sharing setting)
> Kalau diminta login, coba dalam properti sharing folder di komputer
server dipastikan berisi user yang boleh mengaksesnya (atau set share to
everyone [tidak direkomendasikan])
> Selain itu, ya pakai di-set windows credentials-nya saja di komputer
server, yaitu dengan memasukkan semua nama komputer client yang boleh
mengakses komputer server tersebut

Regards,
Kid

2018-02-14 9:29 GMT+07:00 y.roni35@yahoo.com [belajar-excel] <
belajar-excel@yahoogroups.com>:

>
>
> Dear Mr Kid,
> ini baru pertamakali, biasanya ini langsung bisa di akses tanpa login.
> cara lainnya seperti apa?
>
>
>

Wed Feb 14, 2018 1:35 am (PST) . Posted by:

"Pak Cik Lukman"

Dear Master Mr Kid

terima kasih atas bantuannya

salam
PCL

Pada 14 Februari 2018 08.09, 'Mr. Kid' mr.nmkid@gmail.com [belajar-excel] <
belajar-excel@yahoogroups.com> menulis:

>
>
> Terlampir
>
> Regards,
> Kid
>
> 2018-01-31 15:44 GMT+07:00 Pak Cik Lukman pakciklukman@gmail.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>>
>>
>> Selamat sore Bpk2 Master Excel
>>
>> saya mengalami kesulitan menghitung jumlah hari kerja, jika selesai dalam
>> hari/tanggal yg sama dianggap sbg 1 hari penyelesaian. dan mengabaikan hari
>> libur (hari libur jg dihitung/dijumlahkan). formula yang telah saya gunakan
>> adalah :
>>
>> a. dengan menambahkan angka 1 pada kolom penjumlahan/pengurangan awal
>> hari dengan akhir hari kerja..
>> b. dengan menggunakan formula workdays dan networkdays, tapi ternyata
>> formula ini juga mengurangi hari libur.
>>
>> mohon bantuannya dear master excel, berikut contoh tabel yg saya sdh buat.
>>
>>
>> salam,
>> PCL
>>
>>
>
>

Wed Feb 14, 2018 2:00 am (PST) . Posted by:

"hendrik karnadi" hendrikkarnadi

Coba download dan extract file terlampir ke suatu dir (mis D:\).Hasil extract adalah Master dan User1....untuk User2, User3, dstnya tinggal disesuaikan Macronya seperti Macro pada User1.
Salam, HK

On Wednesday, 14 February 2018, 4:09:13 PM GMT+7, Stephen Saputro stephensaputro@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote:

 

File nya saya lampirkan lagi ya Pak, hehe

Terima kasih
2018-02-14 11:21 GMT+07:00 hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

 

Filenya mana ???
Salam,HK
On Wednesday, 14 February 2018, 10:58:11 AM GMT+7, Stephen Saputro stephensaputro@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com > wrote:

 

Dear Pak Hendrik & semua pakar Excel,
Mohon kiranya dapat di bantu, dari file yang ada ini bisakah di bantu agar data yang di input di userform langsung terkirim ke file master tanpa harus transit lagi di sheet input ?

Terima kasih

2018-02-14 10:42 GMT+07:00 hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com >:

 

Hai Yusuf,Macro yang mana, dibuat seperti apa ?
Salam,HK

On Wednesday, 14 February 2018, 9:39:36 AM GMT+7, y.roni35@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com > wrote:

 
Dear Hendrik Karnadi,

Kl macro di buat seperti itu, harus dirubah bagaimana?Terimakasih

Regards,Yusuf

Wed Feb 14, 2018 3:32 am (PST) . Posted by:

"Stephen Saputro"

Terima kasih banyak Pak Hendrik untuk bantuannya,

Kalau saya boleh tanya lagi, seandainya nama yang entri di ganti ke sheet
bukan ke workbook, vba yang ThisWrokbook.Name diubah menjadi apa ya Pak?

2018-02-14 16:58 GMT+07:00 hendrik karnadi hendrikkarnadi@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> Coba download dan extract file terlampir ke suatu dir (mis D:\).
> Hasil extract adalah Master dan User1....untuk User2, User3, dstnya
> tinggal disesuaikan Macronya seperti Macro pada User1.
>
> Salam,
> HK
>
>
> On Wednesday, 14 February 2018, 4:09:13 PM GMT+7, Stephen Saputro
> stephensaputro@gmail.com [belajar-excel] <belajar-excel@yahoogroups..com>
> wrote:
>
>
>
>
> File nya saya lampirkan lagi ya Pak, hehe
>
>
> Terima kasih
>
> 2018-02-14 11:21 GMT+07:00 hendrik karnadi hendrikkarnadi@yahoo..com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
>
> Filenya mana ???
>
> Salam,
> HK
>
> On Wednesday, 14 February 2018, 10:58:11 AM GMT+7, Stephen Saputro
> stephensaputro@gmail.com [belajar-excel] <belajar-excel@yahoogroups..com >
> wrote:
>
>
>
>
> Dear Pak Hendrik & semua pakar Excel,
>
> Mohon kiranya dapat di bantu, *dari file yang ada ini* bisakah di bantu
> agar data yang di input di userform langsung terkirim ke file master tanpa
> harus transit lagi di sheet input ?
>
>
> Terima kasih
>
>
> 2018-02-14 10:42 GMT+07:00 hendrik karnadi hendrikkarnadi@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com >:
>
>
>
> Hai Yusuf,
> Macro yang mana, dibuat seperti apa ?
>
> Salam,
> HK
>
>
> On Wednesday, 14 February 2018, 9:39:36 AM GMT+7, y.roni35@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com > wrote:
>
>
>
>
> Dear Hendrik Karnadi,
>
> Kl macro di buat seperti itu, harus dirubah bagaimana?
> Terimakasih
>
>
>
> Regards,
> Yusuf
>
>
>
>
>

Wed Feb 14, 2018 8:32 am (PST) . Posted by:

"hendrik karnadi" hendrikkarnadi

Kalau pake sheet harusnya lebih mudah, yang pasti form inputnya berikut macronya harus pindah dulu ke Workbook Master bukan Workbook User1, User2 dan User3.
Silakan dicoba dulu, kalau sdh mentok bisa dishare mentoknya dimana....dengan begitu ilmu yang kita pelajari akan "melekat" dalam memori kita.
Coba tulis pseudo code ("bahasa manusia") nya dulu.
Salam,HK

Sent from Yahoo Mail on Android

On Wed, Feb 14, 2018 at 18:32, Stephen Saputro stephensaputro@gmail.com [belajar-excel]<belajar-excel@yahoogroups.com> wrote:  

Terima kasih banyak Pak Hendrik untuk bantuannya,
Kalau saya boleh tanya lagi, seandainya nama yang entri di ganti ke sheet bukan ke workbook, vba yang ThisWrokbook.Name diubah menjadi apa ya Pak?
2018-02-14 16:58 GMT+07:00 hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

 

Coba download dan extract file terlampir ke suatu dir (mis D:\).Hasil extract adalah Master dan User1....untuk User2, User3, dstnya tinggal disesuaikan Macronya seperti Macro pada User1.
Salam, HK

On Wednesday, 14 February 2018, 4:09:13 PM GMT+7, Stephen Saputro stephensaputro@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com > wrote:

 

File nya saya lampirkan lagi ya Pak, hehe

Terima kasih
2018-02-14 11:21 GMT+07:00 hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com >:

 

Filenya mana ???
Salam,HK
On Wednesday, 14 February 2018, 10:58:11 AM GMT+7, Stephen Saputro stephensaputro@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com > wrote:

 

Dear Pak Hendrik & semua pakar Excel,
Mohon kiranya dapat di bantu, dari file yang ada ini bisakah di bantu agar data yang di input di userform langsung terkirim ke file master tanpa harus transit lagi di sheet input ?

Terima kasih

2018-02-14 10:42 GMT+07:00 hendrik karnadi hendrikkarnadi@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com >:

 

Hai Yusuf,Macro yang mana, dibuat seperti apa ?
Salam,HK

On Wednesday, 14 February 2018, 9:39:36 AM GMT+7, y.roni35@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com > wrote:

 
Dear Hendrik Karnadi,

Kl macro di buat seperti itu, harus dirubah bagaimana?Terimakasih

Regards,Yusuf

#yiv3231820614 #yiv3231820614 -- #yiv3231820614ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3231820614 #yiv3231820614ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3231820614 #yiv3231820614ygrp-mkp #yiv3231820614hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3231820614 #yiv3231820614ygrp-mkp #yiv3231820614ads {margin-bottom:10px;}#yiv3231820614 #yiv3231820614ygrp-mkp .yiv3231820614ad {padding:0 0;}#yiv3231820614 #yiv3231820614ygrp-mkp .yiv3231820614ad p {margin:0;}#yiv3231820614 #yiv3231820614ygrp-mkp .yiv3231820614ad a {color:#0000ff;text-decoration:none;}#yiv3231820614 #yiv3231820614ygrp-sponsor #yiv3231820614ygrp-lc {font-family:Arial;}#yiv3231820614 #yiv3231820614ygrp-sponsor #yiv3231820614ygrp-lc #yiv3231820614hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3231820614 #yiv3231820614ygrp-sponsor #yiv3231820614ygrp-lc .yiv3231820614ad {margin-bottom:10px;padding:0 0;}#yiv3231820614 #yiv3231820614actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3231820614 #yiv3231820614activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3231820614 #yiv3231820614activity span {font-weight:700;}#yiv3231820614 #yiv3231820614activity span:first-child {text-transform:uppercase;}#yiv3231820614 #yiv3231820614activity span a {color:#5085b6;text-decoration:none;}#yiv3231820614 #yiv3231820614activity span span {color:#ff7900;}#yiv3231820614 #yiv3231820614activity span .yiv3231820614underline {text-decoration:underline;}#yiv3231820614 .yiv3231820614attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3231820614 .yiv3231820614attach div a {text-decoration:none;}#yiv3231820614 .yiv3231820614attach img {border:none;padding-right:5px;}#yiv3231820614 .yiv3231820614attach label {display:block;margin-bottom:5px;}#yiv3231820614 .yiv3231820614attach label a {text-decoration:none;}#yiv3231820614 blockquote {margin:0 0 0 4px;}#yiv3231820614 .yiv3231820614bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3231820614 .yiv3231820614bold a {text-decoration:none;}#yiv3231820614 dd.yiv3231820614last p a {font-family:Verdana;font-weight:700;}#yiv3231820614 dd.yiv3231820614last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3231820614 dd.yiv3231820614last p span.yiv3231820614yshortcuts {margin-right:0;}#yiv3231820614 div.yiv3231820614attach-table div div a {text-decoration:none;}#yiv3231820614 div.yiv3231820614attach-table {width:400px;}#yiv3231820614 div.yiv3231820614file-title a, #yiv3231820614 div.yiv3231820614file-title a:active, #yiv3231820614 div.yiv3231820614file-title a:hover, #yiv3231820614 div.yiv3231820614file-title a:visited {text-decoration:none;}#yiv3231820614 div.yiv3231820614photo-title a, #yiv3231820614 div.yiv3231820614photo-title a:active, #yiv3231820614 div.yiv3231820614photo-title a:hover, #yiv3231820614 div.yiv3231820614photo-title a:visited {text-decoration:none;}#yiv3231820614 div#yiv3231820614ygrp-mlmsg #yiv3231820614ygrp-msg p a span.yiv3231820614yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3231820614 .yiv3231820614green {color:#628c2a;}#yiv3231820614 .yiv3231820614MsoNormal {margin:0 0 0 0;}#yiv3231820614 o {font-size:0;}#yiv3231820614 #yiv3231820614photos div {float:left;width:72px;}#yiv3231820614 #yiv3231820614photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv3231820614 #yiv3231820614photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3231820614 #yiv3231820614reco-category {font-size:77%;}#yiv3231820614 #yiv3231820614reco-desc {font-size:77%;}#yiv3231820614 .yiv3231820614replbq {margin:4px;}#yiv3231820614 #yiv3231820614ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3231820614 #yiv3231820614ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3231820614 #yiv3231820614ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3231820614 #yiv3231820614ygrp-mlmsg select, #yiv3231820614 input, #yiv3231820614 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3231820614 #yiv3231820614ygrp-mlmsg pre, #yiv3231820614 code {font:115% monospace;}#yiv3231820614 #yiv3231820614ygrp-mlmsg * {line-height:1.22em;}#yiv3231820614 #yiv3231820614ygrp-mlmsg #yiv3231820614logo {padding-bottom:10px;}#yiv3231820614 #yiv3231820614ygrp-msg p a {font-family:Verdana;}#yiv3231820614 #yiv3231820614ygrp-msg p#yiv3231820614attach-count span {color:#1E66AE;font-weight:700;}#yiv3231820614 #yiv3231820614ygrp-reco #yiv3231820614reco-head {color:#ff7900;font-weight:700;}#yiv3231820614 #yiv3231820614ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3231820614 #yiv3231820614ygrp-sponsor #yiv3231820614ov li a {font-size:130%;text-decoration:none;}#yiv3231820614 #yiv3231820614ygrp-sponsor #yiv3231820614ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3231820614 #yiv3231820614ygrp-sponsor #yiv3231820614ov ul {margin:0;padding:0 0 0 8px;}#yiv3231820614 #yiv3231820614ygrp-text {font-family:Georgia;}#yiv3231820614 #yiv3231820614ygrp-text p {margin:0 0 1em 0;}#yiv3231820614 #yiv3231820614ygrp-text tt {font-size:120%;}#yiv3231820614 #yiv3231820614ygrp-vital ul li:last-child {border-right:none !important;}#yiv3231820614

Thu Feb 15, 2018 2:36 am (PST) . Posted by:

"Stephen Saputro"

Siap Pak,

Akan saya utak atik dulu, kalau udah mentok nanti tanya lagi

Terima kasih ya Pak.

On Wed, Feb 14, 2018 at 11:29 PM, hendrik karnadi hendrikkarnadi@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com> wrote:

>
>
> Kalau pake sheet harusnya lebih mudah, yang pasti form inputnya berikut
> macronya harus pindah dulu ke Workbook Master bukan Workbook User1, User2
> dan User3.
>
> Silakan dicoba dulu, kalau sdh mentok bisa dishare mentoknya
> dimana....dengan begitu ilmu yang kita pelajari akan "melekat" dalam memori
> kita.
>
> Coba tulis pseudo code ("bahasa manusia") nya dulu..
>
> Salam,
> HK
>
>
> Sent from Yahoo Mail on Android
> <https://overview..mail.yahoo.com/mobile/?.src=Android>
>
> On Wed, Feb 14, 2018 at 18:32, Stephen Saputro stephensaputro@gmail.com
> [belajar-excel]
> <belajar-excel@yahoogroups.com> wrote:
>
>
> Terima kasih banyak Pak Hendrik untuk bantuannya,
>
> Kalau saya boleh tanya lagi, seandainya nama yang entri di ganti ke sheet
> bukan ke workbook, vba yang ThisWrokbook.Name diubah menjadi apa ya Pak?
>
> 2018-02-14 16:58 GMT+07:00 hendrik karnadi hendrikkarnadi@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
>
> Coba download dan extract file terlampir ke suatu dir (mis D:\).
> Hasil extract adalah Master dan User1....untuk User2, User3, dstnya
> tinggal disesuaikan Macronya seperti Macro pada User1.
>
> Salam,
> HK
>
>
> On Wednesday, 14 February 2018, 4:09:13 PM GMT+7, Stephen Saputro
> stephensaputro@gmail.com [belajar-excel] <belajar-excel@yahoogroups..com >
> wrote:
>
>
>
>
> File nya saya lampirkan lagi ya Pak, hehe
>
>
> Terima kasih
>
> 2018-02-14 11:21 GMT+07:00 hendrik karnadi hendrikkarnadi@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com >:
>
>
>
> Filenya mana ???
>
> Salam,
> HK
>
> On Wednesday, 14 February 2018, 10:58:11 AM GMT+7, Stephen Saputro
> stephensaputro@gmail.com [belajar-excel] <belajar-excel@yahoogroups..com >
> wrote:
>
>
>
>
> Dear Pak Hendrik & semua pakar Excel,
>
> Mohon kiranya dapat di bantu, *dari file yang ada ini* bisakah di bantu
> agar data yang di input di userform langsung terkirim ke file master tanpa
> harus transit lagi di sheet input ?
>
>
> Terima kasih
>
>
> 2018-02-14 10:42 GMT+07:00 hendrik karnadi hendrikkarnadi@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com >:
>
>
>
> Hai Yusuf,
> Macro yang mana, dibuat seperti apa ?
>
> Salam,
> HK
>
>
> On Wednesday, 14 February 2018, 9:39:36 AM GMT+7, y.roni35@yahoo.com
> [belajar-excel] <belajar-excel@yahoogroups.com > wrote:
>
>
>
>
> Dear Hendrik Karnadi,
>
> Kl macro di buat seperti itu, harus dirubah bagaimana?
> Terimakasih
>
>
>
> Regards,
> Yusuf
>
>
>
>
>
>

Wed Feb 14, 2018 2:16 am (PST) . Posted by:

"Stephen Saputro"

Dear Pak Kid,

Terima kasih untuk ulasan yang sangat detail yang sudah membuka pengetahuan
saya perihal get external data dan juga power query. Dalam kasus saya ini,
bisakah di berikan langkah2nya dalam pembuatan melalui get external data
dan juga power query. Saya sudah coba mencari2 referensi baik di google
maupun di youtube, hanya saja pemahaman saya masih aja tetap cetek.

Terima kasih

2018-02-14 16:07 GMT+07:00 'Mr. Kid' mr.nmkid@gmail.com [belajar-excel] <
belajar-excel@yahoogroups.com>:

>
>
> Hai Saputro,
>
> > *Di dalam file database :*
> Ada 2 tabel, yaitu :
> * 1 buah tabel transaksi (Excel table object bernama Table1 di sheet
> 'Trial Balance Db')
> * 1 buah tabel referensi (Excel table object bernama Table3 di sheet
> 'INDEX')
>
> Beberapa kolom di Table1 berisi formula lookup ke Table3.
> Formula ini berarti sebagai pembentuk flat table. Jadi, Table1 masuk
> sebagai jenis flat table. Excel Table object dengan jenis data sebagai flat
> table.. gitu ya..
> Dari formula tersebut, dapat disimpulkan bahwa Table1 dan Table3 memiliki
> relasi : kolom [Account No] di Table1 dengan [GL] di Table3
>
> *Catatan :*
> Kolom [Account No] di Table1 ataupun kolom [GL] di Table3 memiliki
> datatype yang seragam, karena ada field bertipe text dan ada yang bertipe
> numerik.
> Ada baiknya datatype di kolom tersebut diseragamkan. Dalam kasus ini,
> menggunakan datatype Text. Jadi, akun yang berisi angka saja, sebaiknya
> dientri dengan awalan ' (petik)
>
>
> > *Di dalam file report :*
> Yang Anda gunakan adalah fitur Power Query (Get & Transform) <BUKAN fitur
> Excel Get External Data>.
> Hasil Power Query akan disimpan di dalam data model.
> Data yang tersimpan dalam data model dapat diolah dengan Power Pivot untuk
> menjadi data OLAP
> Data yang tersimpan dalam data model dapat ditampilkan ke dalam sheet
> dalam bentuk Pivot Table ataupun Pivot Chart (bisa sebagai OLAP Pivot
> ataupun Flat Pivot)
>
> imho,
> Langkah pertama, lakukan di file database :
> > Buang kolom (delete column) yang berformula lookup ke Table3
>
> Kemudian di file report, penyusunan dalam file report bisa memilih salah
> satu dari cara berikut :
> 1> Jika Anda akan menggunakan fitur Get External Data (BUKAN fitur Get &
> Transform alias Power Query ataupun fitur Power Pivot) :
> * Proses lookup dilakukan memanfaatkan klausa JOIN di query (sql
> statement), jadi file database tidak akan membesar akibat adanya formula
> pengambil data referensi
> * hasil Get External Data langsung dibentuk sebagai Pivot Table (pasti
> sebagai flat pivot)
> * jika sumber data dapat dikoneksi setiap saat, set pivot table options
> -> tab Data -> hilangkan centang dari save data with files, dan centang
> refresh when opening.
> sehingga ukuran file akan sangat kecil (sebesar banyaknya cells yang
> dipakai pivot table saja)
> atau
> 2> Jika Anda akan menggunakan fitur Get & Transform (fitur Power Query),
> maka buat 2 (dua) buah query, yaitu :
> - query 1 : koneksi ke Table1
> - query 2 : koneksi ke Table3
> * Hasil power query adalah data model berisi (hasil import, tidak
> sekadar koneksi data)
> * di Power Pivot, buatlah relationship antara hasil query 1 (koneksi ke
> Table1) kolom [Account No] dengan hasil query 2 (koneksi ke Table3) kolom
> [GL]
> * lalu buat Pivot Table dari Power Pivot (pilih Pivot Table agar
> sebagai OLAP pivot) <JANGAN pilih flattened pivot, karena ini adalah flat
> pivot>
>
>
> Kemudian,
> tentang :
>
>> Untuk rumusnya, sya masih memakai vlookup dari sheet index, tapi masih
>> menggunakan A:A dsb. kalau seandainya tidak menggunakan A:A, kira2
>> formulanya di ubah menjadi bagaimana ya Pak agar bilamana sheet index ada
>> ketambahan data baru,maka akan terupdate juga di sheet Trial Balance Db ?
>>
> > *Walaupun tidak diperlukan lagi pada kasus ini*, tetapi ada baiknya
> tahu juga.
> > Excel Table object akan mengubah luasan dirinya sendiri ketika ada
> penambahan atau pengurangan (pembuangan [hapus]) records.
> > Semua formula yang merujuk ke seluruh records Excel Table object akan
> ikut menyesuaikan diri.
> > Jadi, formula bisa seperti ini : (misal untuk kolom PIC, record ke-1)
> =IFERROR(VLOOKUP([@[ACCOUNT NO]],Table3[[GL]:[Code]],2,FALSE),"")
> atau, karena [Account No] di kolom E, maka bisa begini :
> =IFERROR(VLOOKUP($e2,Table3[[GL]:[Code]],2,FALSE),"")
> andai saja di Table3, kolom GL diletakkan sebagi kolom pertama Excel table
> object, maka formula bisa begini :
> =IFERROR(VLOOKUP($e2,Table3,2,FALSE),"")
> atau
> =IFERROR(VLOOKUP([@[ACCOUNT NO]],Table3,2,FALSE),"")
>
> tentang :
>
>> 1. pada saat file database di update dan di close, apakah file report
>> dummy tetap bisa mengambil data yang terupdate dari file database mesikpun
>> file database dalam keadaan close ?
>>
> > Yup. Syarat dan ketentuan berlaku, yaitu telah di-save
> > File yang dikoneksi (dalam hal ini adalah file database) dalam keadaan
> terbuka atau tertutup, fitur Excel Get External Data, Power Query, atupun
> Power Pivot [Get External Data] bisa melakukannya.
>
> 2 pada saat pertama kali membuka file report dummy, apakah langkah
>> pertama kita harus menghubungkan kembali koneksi yang ada ? mohon di
>> bimbing lagi untuk langkah2nya ya Pak.
>>
> > Bukan menghubungkan kembali koneksi, karena tidak ada proses pembuatan
> ulang koneksi atau meng-edit koneksi yang telah dibuat.
> > Yang dibutuhkan hanyalah me-refresh data.
> > Refresh data akan melakukan serangkaian proses, mulai dari membaca
> definisi koneksi data yang telah dibuat, lalu menghubungi sumber data,
> melakukan query data, lalu menerima data hasil query
> > Semua proses itu cukup dilakukan dengan Refresh data.
> > Refresh data bisa dilakukan melalui banyak jalan, antara lain :
> 1. ribbon Data -> grup menu Connections -> klik Refresh All
> 2. klik kanan excel object (excel pivot table, excel pivot chart, excel
> table object) -> pilih Refresh
> 3. menu tools excel object :
> di excel table object :: klik cells dalam excel table object ->
> menu tools Table Tools -> ribbon Design -> grup menu External Table Data ->
> Refresh
> di excel pivot table atau pivot chart object :: klik cells dalam
> pivot table atau pivot chart -> menu tools PivotTable Tools -> ribbon
> Analyze (xl2010 ke atas) atau Design (xl2007) -> grup menu Data -> Refresh
> 4. di dalam data model :
> via Power Query :: ribbon Home -> grup menu Query -> klik Refresh
> Preview (untuk tabel itu saja) atau pilih Refresh All untuk semua data
> via Power Pivot :: ribbon Home -> klik Refresh atau Refresh All
> > Jika ingin di-refresh-kan oleh Excel secara otomatis, maka disediakan 2
> (dua) pilihan disetiap object connections yang ada
> (lilhat ribbon Data -> grup menu Connections -> klik Connections ->
> pilih koneksi data yang ada -> Properties -> tab Usage)
> - Pilihan 1 : refresh dilakukan Excel setiap N menit (minimal 1 menit
> sekali) :: centang Refresh every dan atur jumlah menitnya.
> - Pilihan 2 : refresh dilakukan Excel setiap file dibuka :: centang
> Refresh data when opening the file
> * Anda bisa memanfaatkan kedua pilihan tersebut bersamaan.
>
> 3. saya lampirkan warning pada saat melakukan get external data. apa yang
>> harus saya buat untuk menghilangkan warning ini ? soalnya saya lihat size
>> dari database dummy hanya kecil 83kb.
>>
> > itu hanya preview-nya saja
> > jika dilihat di data view di dalam data model, maka seluruh records akan
> tampak, walaupun sebenarnya, sebatas preview saja juga sudah cukup
>
>
> Regards,
> Kid
>
>
>
> 2018-02-14 10:43 GMT+07:00 Stephen Saputro stephensaputro@gmail.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>>
>>
>> Dear Pak Kid & Pak Hendrik,
>>
>> Terima kasih banyak untuk bantuannya dan juga penjelasan yang sangat
>> detail. Setelah saya telusuri lagi, sepertinya yang membuat file jadi berat
>> adalah file addins atau yang file extention .xla.
>>
>> Untuk pengaturan databasenya saya susun seperti file terlampir Pak, Mohon
>> di bantu kira2 mana saja yang harus di ubah dari database yang ada.. Untuk
>> rumusnya, sya masih memakai vlookup dari sheet index, tapi masih
>> menggunakan A:A dsb. kalau seandainya tidak menggunakan A:A, kira2
>> formulanya di ubah menjadi bagaimana ya Pak agar bilamana sheet index ada
>> ketambahan data baru,maka akan terupdate juga di sheet Trial Balance Db ?
>>
>> Untuk file Report Dummy, sya menggunakan get external data. Mohon kiranya
>> dapat di bimbing lagi untuk get external datannya. sebab saya masih sedikit
>> bingung. pertanyaan saya :
>> 1. pada saat file database di update dan di close, apakah file report
>> dummy tetap bisa mengambil data yang terupdate dari file database mesikpun
>> file database dalam keadaan close ?
>> 2 pada saat pertama kali membuka file report dummy, apakah langkah
>> pertama kita harus menghubungkan kembali koneksi yang ada ? mohon di
>> bimbing lagi untuk langkah2nya ya Pak.
>> 3. saya lampirkan warning pada saat melakukan get external data. apa yang
>> harus saya buat untuk menghilangkan warning ini ? soalnya saya lihat size
>> dari database dummy hanya kecil 83kb.
>>
>> Terima kasih
>>
>>
>> 2018-02-12 23:22 GMT+07:00 'Mr. Kid' mr.nmkid@gmail.com [belajar-excel] <
>> belajar-excel@yahoogroups.com>:
>>
>>>
>>>
>>> Hai Saputro,
>>>
>>> Tentang :
>>>
>>>> 1. Pada saat database di buka, dan di geser ke kanan dan ke kiri,
>>>> kesannya sangat berat dan sering ngelag
>>>>
>>> > Tidak ada hubugannya dengan fitur Get External Data.
>>> > Hal ini murni karena banyak hal dalam workbook yang tidak efisien.
>>> > Lihat disini :
>>> https://groups.yahoo.com/neo/groups/belajar-excel/conversati
>>> ons/messages/43674
>>>
>>>
>>> 2. pada saat saya membuka file report, dan melakukan refresh, maka ada
>>>> pesan error yang muncul. saya lampirkan pesan errornya ( excel error 2 )
>>>>
>>> > Pesannya bukan pesan error, tetapi pesan warning...
>>> > Penyebabnya adalah data yang diambil melalui fitur get external data
>>> saat itu tidak cukup untuk dimasukkan ke dalam area memory untuk aplikasi
>>> Excel di saat itu.
>>> > Hal ini *BUKAN *malasah fitur Get External Data, tetapi :
>>> a. masalah Anda dalam menyusun sumber data (dalam kasus Anda,
>>> berarti masalah dalam cara Anda menyusun workbook yang Anda sebut file
>>> database)
>>> b. masalah Anda dalam memanfaatkan aplikasi Excel sebagai sebuah
>>> instance
>>> * Sebaiknya Anda ketahui bahwa
>>> - sebuah (satu buah) aplikasi Excel yang terbuka disediakan
>>> ruang seluas 2GB RAM untuk office 32 bit dan 4GB RAM untuk office 64 bit
>>> - sebuah aplikasi Excel terbuka tersebut bisa digunakan
>>> untuk membuka 1 (satu) atau lebih workbook memanfaatkan ruang RAM tersebut
>>> bersama-sama.
>>> - Artinya, BUKAN 1 (satu) workbook dapat jatah 2GB atau 4GB,
>>> tetapi N workbook yang dibuka dalam 1 (satu) aplikasi Excel dapat jatah 2GB
>>> atau 4GB tersebut
>>> termasuk si aplikasi Excel yang butuh sekitar 700 MB
>>> * Jadi, kalau Anda membuka 1 aplikasi Excel, lalu dalam
>>> aplikasi Excel itu membuka file database dan file report, maka jatah file
>>> report untuk me-load data dari file database menjadi berkurang..
>>>
>>> 3. Pada saat saya membuka file database, ada juga pesan error seperti
>>>> file terlampir ( excel error 1 )
>>>>
>>> > Setelah paham apa itu instance Excel application dan workbooks yang
>>> dibuka beserta hubungannya dengan ruang RAM, maka pada nomor 3 ini bisa
>>> Anda telaah sendiri.
>>> > Tetapi, perlu ditambahkan bahwa permasalahannya ditambah dengan hal
>>> yang sudah disebut di nomor 1 (link tersebut dan lihat dalam hal kalkulasi
>>> Excel yang lambat).
>>> > Singkatnya, pada umumnya hal ini disebabkan karena banyak pengguna
>>> Excel sudah memasang beribu-ribu baris formula, TETAPI belum ada datanya.
>>> * alasannya supaya tidak usah copy formula dan tinggal input saja...
>>> hadeh... sebaiknya alasan begini ditinggalkan ya... sudah ada fitur
>>> Excel Table object kan... (lihat ribbon Home -> grup menu Styles -> Format
>>> as Table)
>>>
>>>
>>> Tentang :
>>>
>>>> Mohon bantuannya apakah saya salah dalam membuat get external data ?
>>>>
>>> > Saya pikir tidak ada yang salah dalam pemanfaatan fitur Get External
>>> Data yang Anda lakukan
>>> > Masalah yang muncul disebabkan karena ketidak efisienan dalam menyusun
>>> data di file database, cara memanfaatkan aplikasi Excel,
>>> dan mungkin beberapa hal yang tidak diwaspadai yang sering dilakukan
>>> pengguna Excel yang belum berkenalan dengan Excel
>>> (ups... jadi selama ini apa dong... dah bertahun-tahun pakai Excel
>>> kok masih dibilang belum berkenalan dengan Excel...) dunno.. sok kenal
>>> mungkin
>>>
>>> 1. di file database yang sudah di set Sebagai Tabel, mempunyai row
>>>> hingga 400 row
>>>>
>>> > Jika sudah memanfaatkan Excel Table object dan jumlah record sangat
>>> sedikit (hanya 400 records doank), berarti ada masalah dalam jumlah kolom
>>> dan formulas.
>>> > Bisa jadi formulanya merujuk ke workbook lainnya lagi... (sebaiknya
>>> mengurangi penggunaan formula yang merujuk ke workbook lain)
>>>
>>> 2. waktu menggunakan get external data, saya pake yang dari file excel
>>>>
>>> > Ketika sumber data berformat Excel Files, maka pastilah fitur Get
>>> External Data akan merujuk ke Excel Files.
>>> > Tidak masalah dengan hal ini
>>>
>>>
>>> Tentang :
>>>
>>>> Harapan saya dengan menggunakan exernal data, excel lebih menjadi
>>>> enteng karena database terpisah dengan reportnya.
>>>>
>>> > Yup, fitur Get External Data akan memudahkan dan membuat pengambilan
>>> data terpilih menjadi ringan dan cepat.
>>> > Cukup dipelajari lebih lanjut tentang penyusunan query (sql statement)
>>> yang baik dan sesuai kebutuhan
>>> * Jika membuat formula saja ada kaidah JANGAN merujuk seluruh cells
>>> dalam 1 kolom seperti A:A atau A:G,
>>> maka dalam query pun demikian, usahakan ambil data seperlunya,
>>> baik dalam hal records maupun kolom dan Anda akan mendapatkan keunggulannya
>>> menggunakan sql statement..
>>> * dengan sql statement yang sesuai dengan kebutuhan, maka menyusun
>>> report dari 23GB data per bulan untuk series 24 bulan itu akan menjadi
>>> ringan.
>>> Setidaknya, sistem tersebut masih berjalan baik sampai hampir
>>> berusia 8 tahun. Sepertinya bisa awet umurnya sampai puluhan tahun deh...
>>> wakakaka...
>>> untuk sistem lain yang lebih kecil, dengan 4 workbook (xlsx [xlsx
>>> itu file yang di-zip loh, kalau baca isinya harus di-unzip dulu sama si
>>> Excel]) masing-masing saat ini baru berisi sekitar 400-500MB,
>>> sistemnya masih bisa bekerja dengan baik untuk report series 10
>>> tahun (client-nya pengolah data statistik yang butuh series panjang,
>>> desainnya sih maih mampu lah untuk series 20 tahun kalau butuh)
>>> > So, tidak perlu kuatir dengan fitur Get External Data. Fitur ini
>>> sekadar membuat koneksi data dan mengambil data dengan cara seperti
>>> pengolah data mengambil data kok.. (kaidah database)
>>> > maju terus pantang mundur.. perhatikan lagi sumber datanya secara
>>> saksama, kemudian perbaiki susunan dan buang segala yang memberatkan. Susun
>>> sql statement yang sesuai kebutuhan.
>>>
>>> fyi,
>>> Sebuah file workbook (data.xlsx) berisi 1 (satu) tabel data di sebuah
>>> sheet. File data.xlsx bisa dikoneksi oleh file report.xlsx melalui lebih
>>> dari 1 (satu) koneksi data.
>>> Jadi, report tentang k yang hanya butuh kolom tertentu dan record
>>> tertentu bisa dibuatkan sebuah koneksi tersendiri dengan fitur Get External
>>> Data. Report yang lain (misal M) juga demikian.
>>> Jadi, 1 file report.xlsx bisa berisi banyak koneksi data ke file
>>> data..xlsx, yang wajarnya setiap koneksi memiliki sql statement yang
>>> berbeda sesuai kebutuhan.
>>>
>>> Regards,
>>> Kid
>>>
>>>
>>>
>>>
>>>
>>> 2018-02-12 17:17 GMT+07:00 Stephen Saputro stephensaputro@gmail.com
>>> [belajar-excel] <belajar-excel@yahoogroups.com>:
>>>
>>>>
>>>>
>>>> Selamat sore semuanya,
>>>>
>>>> Mohon bantuannya untuk masalah saya, saya sedanng membuat file yang
>>>> terdiri dari file 1 untuk Report, dan file 2 untuk database. Saya
>>>> menggunakan pivot dengan get external data. hanya saja ada maslah yang saya
>>>> hadapi :
>>>>
>>>> 1. Pada saat database di buka, dan di geser ke kanan dan ke kiri,
>>>> kesannya sangat berat dan sering ngelag
>>>>
>>>> 2. pada saat saya membuka file report, dan melakukan refresh, maka ada
>>>> pesan error yang muncul. saya lampirkan pesan errornya ( excel error 2 )
>>>>
>>>> 3. Pada saat saya membuka file database, ada juga pesan error seperti
>>>> file terlampir ( excel error 1 )
>>>>
>>>> Mohon bantuannya apakah saya salah dalam membuat get external data ?
>>>> sebagai info tambahan :
>>>>
>>>> 1. di file database yang sudah di set Sebagai Tabel, mempunyai row
>>>> hingga 400 row
>>>>
>>>> 2. waktu menggunakan get external data, saya pake yang dari file excel
>>>>
>>>> Harapan saya dengan menggunakan exernal data, excel lebih menjadi
>>>> enteng karena database terpisah dengan reportnya.
>>>>
>>>> Terima kasih
>>>>
>>>>
>>>
>>
>
>

Wed Feb 14, 2018 2:34 pm (PST) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Hai Saputro,

A. Dengan Excel Get External Data, secara umum, langkahnya bisa dilihat
disini
<http://excel-mr-kid.blogspot.co.id/2011/06/get-external-data-04-koneksi-melalui.html>,
dan tetap fokus pada koneksi ke sebuah file.
Bagian yang spesifik untuk koneksi ke Excel files versi xl2007 ke atas :
1. data provider : pilih Microsoft Office xx.0 Access Database Engine OLE
DB Provider yang ada di komputer Anda.
* xx adalah versi library data provider yang telah ter-install, seperti
12 (xl2007 dan xl2010), 15 (xl2013), 16 (xl2016)
2. data link properties -> tab All :
a. Data source : isi dengan alamat lengkap (folder dan nama file) Excel
file yang akan dikoneksi
b. Extended Properties : isi dengan bunyi Excel 12.0;
3. import data -> bagian Select how blabla : pilih Pivot Table
4. import data -> Properties -> tab Definition :
a. Command Type : pilih SQL
b. Command Text : isi dengan query (sql statement)
* Anda bisa belajar tentang SQL Statement disini
<https://www.w3schools.com/sql/>.
* nama sheet dibagian FROM dalam SQL Statement diakhiri dengan $
seperti FROM [myData$] untuk mengambil data yang ada di sheet bernama
'myData'

*** Jika ingin mengetahui lebih detil bagaiman bunyi connection string,
bisa dilihat disini <https://www.connectionstrings.com/>. Khusus untuk
koneksi data ke Excel file, bisa dilihat dibagian ini
<https://www.connectionstrings.com/ace-oledb-12-0/>, dengan mengganti veri
data provider sesuai dengan yang ter-install di komputer setempat.

B. Dengan power query, Anda sudah pernah membuatnya, maka pasti bisa
membuat lagi hal serupa.

Perhatikan langkah-langkah pada bagian 'imho' di imil yang lalu, karena
langkah dimulai dari perbaikan file database.
Sedangkan pembuatan koneksi data dilakukan di file report, setelah
memperbaiki file database.
Perlu diingat bahwa untuk penggunaan Power Query ataupun dengan Power
Pivot, memerlukan pembuatan 2 (dua) koneksi data, yaitu koneksi ke sheet
data dan koneksi ke sheet referensi yang Anda miliki.

Pembuatan relationship di power pivot :
> di Excel -> ribbon Data -> Manage Data Model -> muncul window data model
-> di dalam data model -> ribbon Design -> grup menu Relationship -> klik
Create Relationship
-> pilih Table1 dan pilih kolom Account No
-> di sisi lainnya, pilih Table3 dan pilih kolom GL

Membuat pivot dari data model : ribbon Home -> klik Pivot Table

Regards,
Kid

2018-02-14 17:06 GMT+07:00 Stephen Saputro stephensaputro@gmail.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> Dear Pak Kid,
>
> Terima kasih untuk ulasan yang sangat detail yang sudah membuka
> pengetahuan saya perihal get external data dan juga power query. Dalam
> kasus saya ini, bisakah di berikan langkah2nya dalam pembuatan melalui get
> external data dan juga power query. Saya sudah coba mencari2 referensi baik
> di google maupun di youtube, hanya saja pemahaman saya masih aja tetap
> cetek.
>
>
> Terima kasih
>
> 2018-02-14 16:07 GMT+07:00 'Mr. Kid' mr.nmkid@gmail.com [belajar-excel] <
> belajar-excel@yahoogroups.com>:
>
>>
>>
>> Hai Saputro,
>>
>> > *Di dalam file database :*
>> Ada 2 tabel, yaitu :
>> * 1 buah tabel transaksi (Excel table object bernama Table1 di sheet
>> 'Trial Balance Db')
>> * 1 buah tabel referensi (Excel table object bernama Table3 di sheet
>> 'INDEX')
>>
>> Beberapa kolom di Table1 berisi formula lookup ke Table3.
>> Formula ini berarti sebagai pembentuk flat table. Jadi, Table1 masuk
>> sebagai jenis flat table. Excel Table object dengan jenis data sebagai flat
>> table.. gitu ya..
>> Dari formula tersebut, dapat disimpulkan bahwa Table1 dan Table3 memiliki
>> relasi : kolom [Account No] di Table1 dengan [GL] di Table3
>>
>> *Catatan :*
>> Kolom [Account No] di Table1 ataupun kolom [GL] di Table3 memiliki
>> datatype yang seragam, karena ada field bertipe text dan ada yang bertipe
>> numerik.
>> Ada baiknya datatype di kolom tersebut diseragamkan. Dalam kasus ini,
>> menggunakan datatype Text. Jadi, akun yang berisi angka saja, sebaiknya
>> dientri dengan awalan ' (petik)
>>
>>
>> > *Di dalam file report :*
>> Yang Anda gunakan adalah fitur Power Query (Get & Transform) <BUKAN fitur
>> Excel Get External Data>.
>> Hasil Power Query akan disimpan di dalam data model..
>> Data yang tersimpan dalam data model dapat diolah dengan Power Pivot
>> untuk menjadi data OLAP
>> Data yang tersimpan dalam data model dapat ditampilkan ke dalam sheet
>> dalam bentuk Pivot Table ataupun Pivot Chart (bisa sebagai OLAP Pivot
>> ataupun Flat Pivot)
>>
>> imho,
>> Langkah pertama, lakukan di file database :
>> > Buang kolom (delete column) yang berformula lookup ke Table3
>>
>> Kemudian di file report, penyusunan dalam file report bisa memilih salah
>> satu dari cara berikut :
>> 1> Jika Anda akan menggunakan fitur Get External Data (BUKAN fitur Get &
>> Transform alias Power Query ataupun fitur Power Pivot) :
>> * Proses lookup dilakukan memanfaatkan klausa JOIN di query (sql
>> statement), jadi file database tidak akan membesar akibat adanya formula
>> pengambil data referensi
>> * hasil Get External Data langsung dibentuk sebagai Pivot Table (pasti
>> sebagai flat pivot)
>> * jika sumber data dapat dikoneksi setiap saat, set pivot table
>> options -> tab Data -> hilangkan centang dari save data with files, dan
>> centang refresh when opening.
>> sehingga ukuran file akan sangat kecil (sebesar banyaknya cells yang
>> dipakai pivot table saja)
>> atau
>> 2> Jika Anda akan menggunakan fitur Get & Transform (fitur Power Query),
>> maka buat 2 (dua) buah query, yaitu :
>> - query 1 : koneksi ke Table1
>> - query 2 : koneksi ke Table3
>> * Hasil power query adalah data model berisi (hasil import, tidak
>> sekadar koneksi data)
>> * di Power Pivot, buatlah relationship antara hasil query 1 (koneksi
>> ke Table1) kolom [Account No] dengan hasil query 2 (koneksi ke Table3)
>> kolom [GL]
>> * lalu buat Pivot Table dari Power Pivot (pilih Pivot Table agar
>> sebagai OLAP pivot) <JANGAN pilih flattened pivot, karena ini adalah flat
>> pivot>
>>
>>
>> Kemudian,
>> tentang :
>>
>>> Untuk rumusnya, sya masih memakai vlookup dari sheet index, tapi masih
>>> menggunakan A:A dsb. kalau seandainya tidak menggunakan A:A, kira2
>>> formulanya di ubah menjadi bagaimana ya Pak agar bilamana sheet index ada
>>> ketambahan data baru,maka akan terupdate juga di sheet Trial Balance Db ?
>>>
>> > *Walaupun tidak diperlukan lagi pada kasus ini*, tetapi ada baiknya
>> tahu juga.
>> > Excel Table object akan mengubah luasan dirinya sendiri ketika ada
>> penambahan atau pengurangan (pembuangan [hapus]) records.
>> > Semua formula yang merujuk ke seluruh records Excel Table object akan
>> ikut menyesuaikan diri.
>> > Jadi, formula bisa seperti ini : (misal untuk kolom PIC, record ke-1)
>> =IFERROR(VLOOKUP([@[ACCOUNT NO]],Table3[[GL]:[Code]],2,FALSE),"")
>> atau, karena [Account No] di kolom E, maka bisa begini :
>> =IFERROR(VLOOKUP($e2,Table3[[GL]:[Code]],2,FALSE),"")
>> andai saja di Table3, kolom GL diletakkan sebagi kolom pertama Excel
>> table object, maka formula bisa begini :
>> =IFERROR(VLOOKUP($e2,Table3,2,FALSE),"")
>> atau
>> =IFERROR(VLOOKUP([@[ACCOUNT NO]],Table3,2,FALSE),"")
>>
>> tentang :
>>
>>> 1. pada saat file database di update dan di close, apakah file report
>>> dummy tetap bisa mengambil data yang terupdate dari file database mesikpun
>>> file database dalam keadaan close ?
>>>
>> > Yup. Syarat dan ketentuan berlaku, yaitu telah di-save
>> > File yang dikoneksi (dalam hal ini adalah file database) dalam keadaan
>> terbuka atau tertutup, fitur Excel Get External Data, Power Query, atupun
>> Power Pivot [Get External Data] bisa melakukannya.
>>
>> 2 pada saat pertama kali membuka file report dummy, apakah langkah
>>> pertama kita harus menghubungkan kembali koneksi yang ada ? mohon di
>>> bimbing lagi untuk langkah2nya ya Pak.
>>>
>> > Bukan menghubungkan kembali koneksi, karena tidak ada proses pembuatan
>> ulang koneksi atau meng-edit koneksi yang telah dibuat.
>> > Yang dibutuhkan hanyalah me-refresh data.
>> > Refresh data akan melakukan serangkaian proses, mulai dari membaca
>> definisi koneksi data yang telah dibuat, lalu menghubungi sumber data,
>> melakukan query data, lalu menerima data hasil query
>> > Semua proses itu cukup dilakukan dengan Refresh data.
>> > Refresh data bisa dilakukan melalui banyak jalan, antara lain :
>> 1. ribbon Data -> grup menu Connections -> klik Refresh All
>> 2. klik kanan excel object (excel pivot table, excel pivot chart,
>> excel table object) -> pilih Refresh
>> 3. menu tools excel object :
>> di excel table object :: klik cells dalam excel table object ->
>> menu tools Table Tools -> ribbon Design -> grup menu External Table Data ->
>> Refresh
>> di excel pivot table atau pivot chart object :: klik cells dalam
>> pivot table atau pivot chart -> menu tools PivotTable Tools -> ribbon
>> Analyze (xl2010 ke atas) atau Design (xl2007) -> grup menu Data -> Refresh
>> 4. di dalam data model :
>> via Power Query :: ribbon Home -> grup menu Query -> klik
>> Refresh Preview (untuk tabel itu saja) atau pilih Refresh All untuk semua
>> data
>> via Power Pivot :: ribbon Home -> klik Refresh atau Refresh All
>> > Jika ingin di-refresh-kan oleh Excel secara otomatis, maka disediakan 2
>> (dua) pilihan disetiap object connections yang ada
>> (lilhat ribbon Data -> grup menu Connections -> klik Connections ->
>> pilih koneksi data yang ada -> Properties -> tab Usage)
>> - Pilihan 1 : refresh dilakukan Excel setiap N menit (minimal 1 menit
>> sekali) :: centang Refresh every dan atur jumlah menitnya.
>> - Pilihan 2 : refresh dilakukan Excel setiap file dibuka :: centang
>> Refresh data when opening the file
>> * Anda bisa memanfaatkan kedua pilihan tersebut bersamaan.
>>
>> 3. saya lampirkan warning pada saat melakukan get external data. apa yang
>>> harus saya buat untuk menghilangkan warning ini ? soalnya saya lihat size
>>> dari database dummy hanya kecil 83kb.
>>>
>> > itu hanya preview-nya saja
>> > jika dilihat di data view di dalam data model, maka seluruh records
>> akan tampak, walaupun sebenarnya, sebatas preview saja juga sudah cukup
>>
>>
>> Regards,
>> Kid
>>
>>
>>
>> 2018-02-14 10:43 GMT+07:00 Stephen Saputro stephensaputro@gmail.com
>> [belajar-excel] <belajar-excel@yahoogroups.com>:
>>
>>>
>>>
>>> Dear Pak Kid & Pak Hendrik,
>>>
>>> Terima kasih banyak untuk bantuannya dan juga penjelasan yang sangat
>>> detail. Setelah saya telusuri lagi, sepertinya yang membuat file jadi berat
>>> adalah file addins atau yang file extention .xla.
>>>
>>> Untuk pengaturan databasenya saya susun seperti file terlampir Pak,
>>> Mohon di bantu kira2 mana saja yang harus di ubah dari database yang ada.
>>> Untuk rumusnya, sya masih memakai vlookup dari sheet index, tapi masih
>>> menggunakan A:A dsb. kalau seandainya tidak menggunakan A:A, kira2
>>> formulanya di ubah menjadi bagaimana ya Pak agar bilamana sheet index ada
>>> ketambahan data baru,maka akan terupdate juga di sheet Trial Balance Db ?
>>>
>>> Untuk file Report Dummy, sya menggunakan get external data. Mohon
>>> kiranya dapat di bimbing lagi untuk get external datannya. sebab saya masih
>>> sedikit bingung. pertanyaan saya :
>>> 1. pada saat file database di update dan di close, apakah file report
>>> dummy tetap bisa mengambil data yang terupdate dari file database mesikpun
>>> file database dalam keadaan close ?
>>> 2 pada saat pertama kali membuka file report dummy, apakah langkah
>>> pertama kita harus menghubungkan kembali koneksi yang ada ? mohon di
>>> bimbing lagi untuk langkah2nya ya Pak.
>>> 3. saya lampirkan warning pada saat melakukan get external data. apa
>>> yang harus saya buat untuk menghilangkan warning ini ? soalnya saya lihat
>>> size dari database dummy hanya kecil 83kb.
>>>
>>> Terima kasih
>>>
>>>
>>> 2018-02-12 23:22 GMT+07:00 'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]
>>> <belajar-excel@yahoogroups.com>:
>>>
>>>>
>>>>
>>>> Hai Saputro,
>>>>
>>>> Tentang :
>>>>
>>>>> 1. Pada saat database di buka, dan di geser ke kanan dan ke kiri,
>>>>> kesannya sangat berat dan sering ngelag
>>>>>
>>>> > Tidak ada hubugannya dengan fitur Get External Data.
>>>> > Hal ini murni karena banyak hal dalam workbook yang tidak efisien.
>>>> > Lihat disini :
>>>> https://groups.yahoo.com/neo/groups/belajar-excel/conversati
>>>> ons/messages/43674
>>>>
>>>>
>>>> 2. pada saat saya membuka file report, dan melakukan refresh, maka ada
>>>>> pesan error yang muncul. saya lampirkan pesan errornya ( excel error 2 )
>>>>>
>>>> > Pesannya bukan pesan error, tetapi pesan warning...
>>>> > Penyebabnya adalah data yang diambil melalui fitur get external data
>>>> saat itu tidak cukup untuk dimasukkan ke dalam area memory untuk aplikasi
>>>> Excel di saat itu.
>>>> > Hal ini *BUKAN *malasah fitur Get External Data, tetapi :
>>>> a. masalah Anda dalam menyusun sumber data (dalam kasus Anda,
>>>> berarti masalah dalam cara Anda menyusun workbook yang Anda sebut file
>>>> database)
>>>> b. masalah Anda dalam memanfaatkan aplikasi Excel sebagai sebuah
>>>> instance
>>>> * Sebaiknya Anda ketahui bahwa
>>>> - sebuah (satu buah) aplikasi Excel yang terbuka disediakan
>>>> ruang seluas 2GB RAM untuk office 32 bit dan 4GB RAM untuk office 64 bit
>>>> - sebuah aplikasi Excel terbuka tersebut bisa digunakan
>>>> untuk membuka 1 (satu) atau lebih workbook memanfaatkan ruang RAM tersebut
>>>> bersama-sama.
>>>> - Artinya, BUKAN 1 (satu) workbook dapat jatah 2GB atau
>>>> 4GB, tetapi N workbook yang dibuka dalam 1 (satu) aplikasi Excel dapat
>>>> jatah 2GB atau 4GB tersebut
>>>> termasuk si aplikasi Excel yang butuh sekitar 700 MB
>>>> * Jadi, kalau Anda membuka 1 aplikasi Excel, lalu dalam
>>>> aplikasi Excel itu membuka file database dan file report, maka jatah file
>>>> report untuk me-load data dari file database menjadi berkurang.
>>>>
>>>> 3. Pada saat saya membuka file database, ada juga pesan error seperti
>>>>> file terlampir ( excel error 1 )
>>>>>
>>>> > Setelah paham apa itu instance Excel application dan workbooks yang
>>>> dibuka beserta hubungannya dengan ruang RAM, maka pada nomor 3 ini bisa
>>>> Anda telaah sendiri.
>>>> > Tetapi, perlu ditambahkan bahwa permasalahannya ditambah dengan hal
>>>> yang sudah disebut di nomor 1 (link tersebut dan lihat dalam hal kalkulasi
>>>> Excel yang lambat).
>>>> > Singkatnya, pada umumnya hal ini disebabkan karena banyak pengguna
>>>> Excel sudah memasang beribu-ribu baris formula, TETAPI belum ada datanya.
>>>> * alasannya supaya tidak usah copy formula dan tinggal input saja...
>>>> hadeh... sebaiknya alasan begini ditinggalkan ya... sudah ada
>>>> fitur Excel Table object kan... (lihat ribbon Home -> grup menu Styles ->
>>>> Format as Table)
>>>>
>>>>
>>>> Tentang :
>>>>
>>>>> Mohon bantuannya apakah saya salah dalam membuat get external data ?
>>>>>
>>>> > Saya pikir tidak ada yang salah dalam pemanfaatan fitur Get External
>>>> Data yang Anda lakukan
>>>> > Masalah yang muncul disebabkan karena ketidak efisienan dalam
>>>> menyusun data di file database, cara memanfaatkan aplikasi Excel,
>>>> dan mungkin beberapa hal yang tidak diwaspadai yang sering dilakukan
>>>> pengguna Excel yang belum berkenalan dengan Excel
>>>> (ups... jadi selama ini apa dong... dah bertahun-tahun pakai Excel
>>>> kok masih dibilang belum berkenalan dengan Excel...) dunno.. sok kenal
>>>> mungkin
>>>>
>>>> 1. di file database yang sudah di set Sebagai Tabel, mempunyai row
>>>>> hingga 400 row
>>>>>
>>>> > Jika sudah memanfaatkan Excel Table object dan jumlah record sangat
>>>> sedikit (hanya 400 records doank), berarti ada masalah dalam jumlah kolom
>>>> dan formulas.
>>>> > Bisa jadi formulanya merujuk ke workbook lainnya lagi.... (sebaiknya
>>>> mengurangi penggunaan formula yang merujuk ke workbook lain)
>>>>
>>>> 2. waktu menggunakan get external data, saya pake yang dari file excel
>>>>>
>>>> > Ketika sumber data berformat Excel Files, maka pastilah fitur Get
>>>> External Data akan merujuk ke Excel Files.
>>>> > Tidak masalah dengan hal ini
>>>>
>>>>
>>>> Tentang :
>>>>
>>>>> Harapan saya dengan menggunakan exernal data, excel lebih menjadi
>>>>> enteng karena database terpisah dengan reportnya.
>>>>>
>>>> > Yup, fitur Get External Data akan memudahkan dan membuat pengambilan
>>>> data terpilih menjadi ringan dan cepat.
>>>> > Cukup dipelajari lebih lanjut tentang penyusunan query (sql
>>>> statement) yang baik dan sesuai kebutuhan
>>>> * Jika membuat formula saja ada kaidah JANGAN merujuk seluruh cells
>>>> dalam 1 kolom seperti A:A atau A:G,
>>>> maka dalam query pun demikian, usahakan ambil data seperlunya,
>>>> baik dalam hal records maupun kolom dan Anda akan mendapatkan keunggulannya
>>>> menggunakan sql statement..
>>>> * dengan sql statement yang sesuai dengan kebutuhan, maka menyusun
>>>> report dari 23GB data per bulan untuk series 24 bulan itu akan menjadi
>>>> ringan.
>>>> Setidaknya, sistem tersebut masih berjalan baik sampai hampir
>>>> berusia 8 tahun. Sepertinya bisa awet umurnya sampai puluhan tahun deh...
>>>> wakakaka...
>>>> untuk sistem lain yang lebih kecil, dengan 4 workbook (xlsx [xlsx
>>>> itu file yang di-zip loh, kalau baca isinya harus di-unzip dulu sama si
>>>> Excel]) masing-masing saat ini baru berisi sekitar 400-500MB,
>>>> sistemnya masih bisa bekerja dengan baik untuk report series 10
>>>> tahun (client-nya pengolah data statistik yang butuh series panjang,
>>>> desainnya sih maih mampu lah untuk series 20 tahun kalau butuh)
>>>> > So, tidak perlu kuatir dengan fitur Get External Data.. Fitur ini
>>>> sekadar membuat koneksi data dan mengambil data dengan cara seperti
>>>> pengolah data mengambil data kok.. (kaidah database)
>>>> > maju terus pantang mundur.. perhatikan lagi sumber datanya secara
>>>> saksama, kemudian perbaiki susunan dan buang segala yang memberatkan. Susun
>>>> sql statement yang sesuai kebutuhan.
>>>>
>>>> fyi,
>>>> Sebuah file workbook (data.xlsx) berisi 1 (satu) tabel data di sebuah
>>>> sheet. File data.xlsx bisa dikoneksi oleh file report.xlsx melalui lebih
>>>> dari 1 (satu) koneksi data.
>>>> Jadi, report tentang k yang hanya butuh kolom tertentu dan record
>>>> tertentu bisa dibuatkan sebuah koneksi tersendiri dengan fitur Get External
>>>> Data.. Report yang lain (misal M) juga demikian.
>>>> Jadi, 1 file report.xlsx bisa berisi banyak koneksi data ke file
>>>> data..xlsx, yang wajarnya setiap koneksi memiliki sql statement yang
>>>> berbeda sesuai kebutuhan.
>>>>
>>>> Regards,
>>>> Kid
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> 2018-02-12 17:17 GMT+07:00 Stephen Saputro stephensaputro@gmail.com
>>>> [belajar-excel] <belajar-excel@yahoogroups.com>:
>>>>
>>>>>
>>>>>
>>>>> Selamat sore semuanya,
>>>>>
>>>>> Mohon bantuannya untuk masalah saya, saya sedanng membuat file yang
>>>>> terdiri dari file 1 untuk Report, dan file 2 untuk database. Saya
>>>>> menggunakan pivot dengan get external data. hanya saja ada maslah yang saya
>>>>> hadapi :
>>>>>
>>>>> 1. Pada saat database di buka, dan di geser ke kanan dan ke kiri,
>>>>> kesannya sangat berat dan sering ngelag
>>>>>
>>>>> 2. pada saat saya membuka file report, dan melakukan refresh, maka ada
>>>>> pesan error yang muncul. saya lampirkan pesan errornya ( excel error 2 )
>>>>>
>>>>> 3. Pada saat saya membuka file database, ada juga pesan error seperti
>>>>> file terlampir ( excel error 1 )
>>>>>
>>>>> Mohon bantuannya apakah saya salah dalam membuat get external data ?
>>>>> sebagai info tambahan :
>>>>>
>>>>> 1. di file database yang sudah di set Sebagai Tabel, mempunyai row
>>>>> hingga 400 row
>>>>>
>>>>> 2. waktu menggunakan get external data, saya pake yang dari file excel
>>>>>
>>>>> Harapan saya dengan menggunakan exernal data, excel lebih menjadi
>>>>> enteng karena database terpisah dengan reportnya.
>>>>>
>>>>> Terima kasih
>>>>>
>>>>>
>>>>
>>>
>>
>
>

Thu Feb 15, 2018 2:36 am (PST) . Posted by:

"Stephen Saputro"

Terima kasih banyak Pak Kid untuk bantuannya.

Ijinkan saya utak atik dulu ya Pak, kalau ada kendala akan saya tanyakan
lagi. Terima kasih untuk bantuannya.

2018-02-15 5:33 GMT+07:00 'Mr. Kid' mr.nmkid@gmail.com [belajar-excel] <
belajar-excel@yahoogroups.com>:

>
>
> Hai Saputro,
>
> A. Dengan Excel Get External Data, secara umum, langkahnya bisa dilihat
> disini
> <http://excel-mr-kid.blogspot.co.id/2011/06/get-external-data-04-koneksi-melalui.html>,
> dan tetap fokus pada koneksi ke sebuah file.
> Bagian yang spesifik untuk koneksi ke Excel files versi xl2007 ke atas :
> 1. data provider : pilih Microsoft Office xx.0 Access Database Engine OLE
> DB Provider yang ada di komputer Anda.
> * xx adalah versi library data provider yang telah ter-install,
> seperti 12 (xl2007 dan xl2010), 15 (xl2013), 16 (xl2016)
> 2. data link properties -> tab All :
> a. Data source : isi dengan alamat lengkap (folder dan nama file)
> Excel file yang akan dikoneksi
> b. Extended Properties : isi dengan bunyi Excel 12.0;
> 3. import data -> bagian Select how blabla : pilih Pivot Table
> 4. import data -> Properties -> tab Definition :
> a. Command Type : pilih SQL
> b. Command Text : isi dengan query (sql statement)
> * Anda bisa belajar tentang SQL Statement disini
> <https://www.w3schools.com/sql/>.
> * nama sheet dibagian FROM dalam SQL Statement diakhiri dengan $
> seperti FROM [myData$] untuk mengambil data yang ada di sheet bernama
> 'myData'
>
> *** Jika ingin mengetahui lebih detil bagaiman bunyi connection string,
> bisa dilihat disini <https://www.connectionstrings.com/>. Khusus untuk
> koneksi data ke Excel file, bisa dilihat dibagian ini
> <https://www.connectionstrings.com/ace-oledb-12-0/>, dengan mengganti
> veri data provider sesuai dengan yang ter-install di komputer setempat..
>
> B. Dengan power query, Anda sudah pernah membuatnya, maka pasti bisa
> membuat lagi hal serupa.
>
> Perhatikan langkah-langkah pada bagian 'imho' di imil yang lalu, karena
> langkah dimulai dari perbaikan file database.
> Sedangkan pembuatan koneksi data dilakukan di file report, setelah
> memperbaiki file database.
> Perlu diingat bahwa untuk penggunaan Power Query ataupun dengan Power
> Pivot, memerlukan pembuatan 2 (dua) koneksi data, yaitu koneksi ke sheet
> data dan koneksi ke sheet referensi yang Anda miliki.
>
> Pembuatan relationship di power pivot :
> > di Excel -> ribbon Data -> Manage Data Model -> muncul window data model
> -> di dalam data model -> ribbon Design -> grup menu Relationship -> klik
> Create Relationship
> -> pilih Table1 dan pilih kolom Account No
> -> di sisi lainnya, pilih Table3 dan pilih kolom GL
>
> Membuat pivot dari data model : ribbon Home -> klik Pivot Table
>
>
> Regards,
> Kid
>
>
>
>
> 2018-02-14 17:06 GMT+07:00 Stephen Saputro stephensaputro@gmail.com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>>
>>
>> Dear Pak Kid,
>>
>> Terima kasih untuk ulasan yang sangat detail yang sudah membuka
>> pengetahuan saya perihal get external data dan juga power query. Dalam

>> kasus saya ini, bisakah di berikan langkah2nya dalam pembuatan melalui get
>> external data dan juga power query. Saya sudah coba mencari2 referensi baik
>> di google maupun di youtube, hanya saja pemahaman saya masih aja tetap
>> cetek.
>>
>>
>> Terima kasih
>>
>> 2018-02-14 16:07 GMT+07:00 'Mr.. Kid' mr.nmkid@gmail.com [belajar-excel] <
>> belajar-excel@yahoogroups..com <belajar-excel@yahoogroups.com>>:
>>
>>>
>>>
>>> Hai Saputro,
>>>
>>> > *Di dalam file database :*
>>> Ada 2 tabel, yaitu :
>>> * 1 buah tabel transaksi (Excel table object bernama Table1 di sheet
>>> 'Trial Balance Db')
>>> * 1 buah tabel referensi (Excel table object bernama Table3 di sheet
>>> 'INDEX')
>>>
>>> Beberapa kolom di Table1 berisi formula lookup ke Table3...
>>> Formula ini berarti sebagai pembentuk flat table. Jadi, Table1 masuk
>>> sebagai jenis flat table. Excel Table object dengan jenis data sebagai flat
>>> table.. gitu ya..
>>> Dari formula tersebut, dapat disimpulkan bahwa Table1 dan Table3
>>> memiliki relasi : kolom [Account No] di Table1 dengan [GL] di Table3
>>>
>>> *Catatan :*
>>> Kolom [Account No] di Table1 ataupun kolom [GL] di Table3 memiliki
>>> datatype yang seragam, karena ada field bertipe text dan ada yang bertipe
>>> numerik.
>>> Ada baiknya datatype di kolom tersebut diseragamkan. Dalam kasus ini,
>>> menggunakan datatype Text. Jadi, akun yang berisi angka saja, sebaiknya
>>> dientri dengan awalan ' (petik)
>>>
>>>
>>> > *Di dalam file report :*
>>> Yang Anda gunakan adalah fitur Power Query (Get & Transform) <BUKAN
>>> fitur Excel Get External Data>..
>>> Hasil Power Query akan disimpan di dalam data model..
>>> Data yang tersimpan dalam data model dapat diolah dengan Power Pivot
>>> untuk menjadi data OLAP
>>> Data yang tersimpan dalam data model dapat ditampilkan ke dalam sheet
>>> dalam bentuk Pivot Table ataupun Pivot Chart (bisa sebagai OLAP Pivot
>>> ataupun Flat Pivot)
>>>
>>> imho,
>>> Langkah pertama, lakukan di file database :
>>> > Buang kolom (delete column) yang berformula lookup ke Table3
>>>
>>> Kemudian di file report, penyusunan dalam file report bisa memilih salah
>>> satu dari cara berikut :
>>> 1> Jika Anda akan menggunakan fitur Get External Data (BUKAN fitur Get &
>>> Transform alias Power Query ataupun fitur Power Pivot) :
>>> * Proses lookup dilakukan memanfaatkan klausa JOIN di query (sql
>>> statement), jadi file database tidak akan membesar akibat adanya formula
>>> pengambil data referensi
>>> * hasil Get External Data langsung dibentuk sebagai Pivot Table
>>> (pasti sebagai flat pivot)
>>> * jika sumber data dapat dikoneksi setiap saat, set pivot table
>>> options -> tab Data -> hilangkan centang dari save data with files, dan
>>> centang refresh when opening.
>>> sehingga ukuran file akan sangat kecil (sebesar banyaknya cells
>>> yang dipakai pivot table saja)
>>> atau
>>> 2> Jika Anda akan menggunakan fitur Get & Transform (fitur Power Query),
>>> maka buat 2 (dua) buah query, yaitu :
>>> - query 1 : koneksi ke Table1
>>> - query 2 : koneksi ke Table3
>>> * Hasil power query adalah data model berisi (hasil import, tidak
>>> sekadar koneksi data)
>>> * di Power Pivot, buatlah relationship antara hasil query 1 (koneksi
>>> ke Table1) kolom [Account No] dengan hasil query 2 (koneksi ke Table3)
>>> kolom [GL]
>>> * lalu buat Pivot Table dari Power Pivot (pilih Pivot Table agar
>>> sebagai OLAP pivot) <JANGAN pilih flattened pivot, karena ini adalah flat
>>> pivot>
>>>
>>>
>>> Kemudian,
>>> tentang :
>>>
>>>> Untuk rumusnya, sya masih memakai vlookup dari sheet index, tapi masih
>>>> menggunakan A:A dsb. kalau seandainya tidak menggunakan A:A, kira2
>>>> formulanya di ubah menjadi bagaimana ya Pak agar bilamana sheet index ada
>>>> ketambahan data baru,maka akan terupdate juga di sheet Trial Balance Db ?
>>>>
>>> > *Walaupun tidak diperlukan lagi pada kasus ini*, tetapi ada baiknya
>>> tahu juga.
>>> > Excel Table object akan mengubah luasan dirinya sendiri ketika ada
>>> penambahan atau pengurangan (pembuangan [hapus]) records.
>>> > Semua formula yang merujuk ke seluruh records Excel Table object akan
>>> ikut menyesuaikan diri.
>>> > Jadi, formula bisa seperti ini : (misal untuk kolom PIC, record ke-1)
>>> =IFERROR(VLOOKUP([@[ACCOUNT NO]],Table3[[GL]:[Code]],2,FALSE),"")
>>> atau, karena [Account No] di kolom E, maka bisa begini :
>>> =IFERROR(VLOOKUP($e2,Table3[[GL]:[Code]],2,FALSE),"")
>>> andai saja di Table3, kolom GL diletakkan sebagi kolom pertama Excel
>>> table object, maka formula bisa begini :
>>> =IFERROR(VLOOKUP($e2,Table3,2,FALSE),"")
>>> atau
>>> =IFERROR(VLOOKUP([@[ACCOUNT NO]],Table3,2,FALSE),"")
>>>
>>> tentang :
>>>
>>>> 1. pada saat file database di update dan di close, apakah file report
>>>> dummy tetap bisa mengambil data yang terupdate dari file database mesikpun
>>>> file database dalam keadaan close ?
>>>>
>>> > Yup. Syarat dan ketentuan berlaku, yaitu telah di-save
>>> > File yang dikoneksi (dalam hal ini adalah file database) dalam keadaan
>>> terbuka atau tertutup, fitur Excel Get External Data, Power Query, atupun
>>> Power Pivot [Get External Data] bisa melakukannya.
>>>
>>> 2 pada saat pertama kali membuka file report dummy, apakah langkah
>>>> pertama kita harus menghubungkan kembali koneksi yang ada ? mohon di
>>>> bimbing lagi untuk langkah2nya ya Pak.
>>>>
>>> > Bukan menghubungkan kembali koneksi, karena tidak ada proses pembuatan
>>> ulang koneksi atau meng-edit koneksi yang telah dibuat.
>>> > Yang dibutuhkan hanyalah me-refresh data.
>>> > Refresh data akan melakukan serangkaian proses, mulai dari membaca
>>> definisi koneksi data yang telah dibuat, lalu menghubungi sumber data,
>>> melakukan query data, lalu menerima data hasil query
>>> > Semua proses itu cukup dilakukan dengan Refresh data.
>>> > Refresh data bisa dilakukan melalui banyak jalan, antara lain :
>>> 1. ribbon Data -> grup menu Connections -> klik Refresh All
>>> 2. klik kanan excel object (excel pivot table, excel pivot chart,
>>> excel table object) -> pilih Refresh
>>> 3. menu tools excel object :
>>> di excel table object :: klik cells dalam excel table object ->
>>> menu tools Table Tools -> ribbon Design -> grup menu External Table Data ->
>>> Refresh
>>> di excel pivot table atau pivot chart object :: klik cells
>>> dalam pivot table atau pivot chart -> menu tools PivotTable Tools -> ribbon
>>> Analyze (xl2010 ke atas) atau Design (xl2007) -> grup menu Data -> Refresh
>>> 4. di dalam data model :
>>> via Power Query :: ribbon Home -> grup menu Query -> klik
>>> Refresh Preview (untuk tabel itu saja) atau pilih Refresh All untuk semua
>>> data
>>> via Power Pivot :: ribbon Home -> klik Refresh atau Refresh All
>>> > Jika ingin di-refresh-kan oleh Excel secara otomatis, maka disediakan
>>> 2 (dua) pilihan disetiap object connections yang ada
>>> (lilhat ribbon Data -> grup menu Connections -> klik Connections ->
>>> pilih koneksi data yang ada -> Properties -> tab Usage)
>>> - Pilihan 1 : refresh dilakukan Excel setiap N menit (minimal 1 menit
>>> sekali) :: centang Refresh every dan atur jumlah menitnya.
>>> - Pilihan 2 : refresh dilakukan Excel setiap file dibuka :: centang
>>> Refresh data when opening the file
>>> * Anda bisa memanfaatkan kedua pilihan tersebut bersamaan.
>>>
>>> 3. saya lampirkan warning pada saat melakukan get external data. apa
>>>> yang harus saya buat untuk menghilangkan warning ini ? soalnya saya lihat
>>>> size dari database dummy hanya kecil 83kb.
>>>>
>>> > itu hanya preview-nya saja
>>> > jika dilihat di data view di dalam data model, maka seluruh records
>>> akan tampak, walaupun sebenarnya, sebatas preview saja juga sudah cukup
>>>
>>>
>>> Regards,
>>> Kid
>>>
>>>
>>>
>>> 2018-02-14 10:43 GMT+07:00 Stephen Saputro stephensaputro@gmail.com
>>> [belajar-excel] <belajar-excel@yahoogroups.com>:
>>>
>>>>
>>>>

>>>> Dear Pak Kid & Pak Hendrik,
>>>>
>>>> Terima kasih banyak untuk bantuannya dan juga penjelasan yang sangat
>>>> detail. Setelah saya telusuri lagi, sepertinya yang membuat file jadi berat
>>>> adalah file addins atau yang file extention .xla.
>>>>
>>>> Untuk pengaturan databasenya saya susun seperti file terlampir Pak,
>>>> Mohon di bantu kira2 mana saja yang harus di ubah dari database yang ada.
>>>> Untuk rumusnya, sya masih memakai vlookup dari sheet index, tapi masih
>>>> menggunakan A:A dsb. kalau seandainya tidak menggunakan A:A, kira2
>>>> formulanya di ubah menjadi bagaimana ya Pak agar bilamana sheet index ada
>>>> ketambahan data baru,maka akan terupdate juga di sheet Trial Balance Db ?
>>>>
>>>> Untuk file Report Dummy, sya menggunakan get external data.. Mohon
>>>> kiranya dapat di bimbing lagi untuk get external datannya. sebab saya masih
>>>> sedikit bingung. pertanyaan saya :
>>>> 1. pada saat file database di update dan di close, apakah file report
>>>> dummy tetap bisa mengambil data yang terupdate dari file database mesikpun
>>>> file database dalam keadaan close ?
>>>> 2 pada saat pertama kali membuka file report dummy, apakah langkah
>>>> pertama kita harus menghubungkan kembali koneksi yang ada ? mohon di
>>>> bimbing lagi untuk langkah2nya ya Pak.
>>>> 3. saya lampirkan warning pada saat melakukan get external data. apa
>>>> yang harus saya buat untuk menghilangkan warning ini ? soalnya saya lihat
>>>> size dari database dummy hanya kecil 83kb.
>>>>
>>>> Terima kasih
>>>>
>>>>
>>>> 2018-02-12 23:22 GMT+07:00 'Mr. Kid' mr.nmkid@gmail.com
>>>> [belajar-excel] <belajar-excel@yahoogroups.com>:
>>>>
>>>>>
>>>>>
>>>>> Hai Saputro,
>>>>>
>>>>> Tentang :
>>>>>
>>>>>> 1. Pada saat database di buka, dan di geser ke kanan dan ke kiri,
>>>>>> kesannya sangat berat dan sering ngelag
>>>>>>
>>>>> > Tidak ada hubugannya dengan fitur Get External Data.
>>>>> > Hal ini murni karena banyak hal dalam workbook yang tidak efisien.
>>>>> > Lihat disini :
>>>>> https://groups.yahoo.com/neo/groups/belajar-excel/conversati
>>>>> ons/messages/43674
>>>>>
>>>>>
>>>>> 2. pada saat saya membuka file report, dan melakukan refresh, maka ada

>>>>>> pesan error yang muncul. saya lampirkan pesan errornya ( excel error 2 )
>>>>>>
>>>>> > Pesannya bukan pesan error, tetapi pesan warning....
>>>>> > Penyebabnya adalah data yang diambil melalui fitur get external data
>>>>> saat itu tidak cukup untuk dimasukkan ke dalam area memory untuk aplikasi
>>>>> Excel di saat itu.
>>>>> > Hal ini *BUKAN *malasah fitur Get External Data, tetapi :
>>>>> a. masalah Anda dalam menyusun sumber data (dalam kasus Anda,
>>>>> berarti masalah dalam cara Anda menyusun workbook yang Anda sebut file
>>>>> database)
>>>>> b. masalah Anda dalam memanfaatkan aplikasi Excel sebagai sebuah
>>>>> instance
>>>>> * Sebaiknya Anda ketahui bahwa
>>>>> - sebuah (satu buah) aplikasi Excel yang terbuka
>>>>> disediakan ruang seluas 2GB RAM untuk office 32 bit dan 4GB RAM untuk
>>>>> office 64 bit
>>>>> - sebuah aplikasi Excel terbuka tersebut bisa digunakan
>>>>> untuk membuka 1 (satu) atau lebih workbook memanfaatkan ruang RAM tersebut
>>>>> bersama-sama.
>>>>> - Artinya, BUKAN 1 (satu) workbook dapat jatah 2GB atau
>>>>> 4GB, tetapi N workbook yang dibuka dalam 1 (satu) aplikasi Excel dapat
>>>>> jatah 2GB atau 4GB tersebut
>>>>> termasuk si aplikasi Excel yang butuh sekitar 700 MB
>>>>> * Jadi, kalau Anda membuka 1 aplikasi Excel, lalu dalam
>>>>> aplikasi Excel itu membuka file database dan file report, maka jatah file
>>>>> report untuk me-load data dari file database menjadi berkurang.
>>>>>
>>>>> 3. Pada saat saya membuka file database, ada juga pesan error seperti
>>>>>> file terlampir ( excel error 1 )
>>>>>>
>>>>> > Setelah paham apa itu instance Excel application dan workbooks yang
>>>>> dibuka beserta hubungannya dengan ruang RAM, maka pada nomor 3 ini bisa
>>>>> Anda telaah sendiri.
>>>>> > Tetapi, perlu ditambahkan bahwa permasalahannya ditambah dengan hal
>>>>> yang sudah disebut di nomor 1 (link tersebut dan lihat dalam hal kalkulasi
>>>>> Excel yang lambat).
>>>>> > Singkatnya, pada umumnya hal ini disebabkan karena banyak pengguna
>>>>> Excel sudah memasang beribu-ribu baris formula, TETAPI belum ada datanya.
>>>>> * alasannya supaya tidak usah copy formula dan tinggal input saja...
>>>>> hadeh... sebaiknya alasan begini ditinggalkan ya.... sudah ada
>>>>> fitur Excel Table object kan... (lihat ribbon Home -> grup menu Styles ->
>>>>> Format as Table)
>>>>>
>>>>>
>>>>> Tentang :
>>>>>
>>>>>> Mohon bantuannya apakah saya salah dalam membuat get external data ?
>>>>>>
>>>>> > Saya pikir tidak ada yang salah dalam pemanfaatan fitur Get External
>>>>> Data yang Anda lakukan
>>>>> > Masalah yang muncul disebabkan karena ketidak efisienan dalam
>>>>> menyusun data di file database, cara memanfaatkan aplikasi Excel,
>>>>> dan mungkin beberapa hal yang tidak diwaspadai yang sering
>>>>> dilakukan pengguna Excel yang belum berkenalan dengan Excel
>>>>> (ups... jadi selama ini apa dong... dah bertahun-tahun pakai Excel
>>>>> kok masih dibilang belum berkenalan dengan Excel...) dunno.. sok kenal
>>>>> mungkin
>>>>>
>>>>> 1. di file database yang sudah di set Sebagai Tabel, mempunyai row
>>>>>> hingga 400 row
>>>>>>
>>>>> > Jika sudah memanfaatkan Excel Table object dan jumlah record sangat
>>>>> sedikit (hanya 400 records doank), berarti ada masalah dalam jumlah kolom
>>>>> dan formulas.
>>>>> > Bisa jadi formulanya merujuk ke workbook lainnya lagi... (sebaiknya
>>>>> mengurangi penggunaan formula yang merujuk ke workbook lain)
>>>>>
>>>>> 2.. waktu menggunakan get external data, saya pake yang dari file excel
>>>>>>
>>>>> > Ketika sumber data berformat Excel Files, maka pastilah fitur Get

>>>>> External Data akan merujuk ke Excel Files.
>>>>> > Tidak masalah dengan hal ini
>>>>>
>>>>>
>>>>> Tentang :
>>>>>
>>>>>> Harapan saya dengan menggunakan exernal data, excel lebih menjadi
>>>>>> enteng karena database terpisah dengan reportnya.
>>>>>>
>>>>> > Yup, fitur Get External Data akan memudahkan dan membuat pengambilan
>>>>> data terpilih menjadi ringan dan cepat.
>>>>> > Cukup dipelajari lebih lanjut tentang penyusunan query (sql
>>>>> statement) yang baik dan sesuai kebutuhan
>>>>> * Jika membuat formula saja ada kaidah JANGAN merujuk seluruh cells
>>>>> dalam 1 kolom seperti A:A atau A:G,
>>>>> maka dalam query pun demikian, usahakan ambil data seperlunya,
>>>>> baik dalam hal records maupun kolom dan Anda akan mendapatkan keunggulannya
>>>>> menggunakan sql statement..
>>>>> * dengan sql statement yang sesuai dengan kebutuhan, maka menyusun
>>>>> report dari 23GB data per bulan untuk series 24 bulan itu akan menjadi
>>>>> ringan.
>>>>> Setidaknya, sistem tersebut masih berjalan baik sampai hampir
>>>>> berusia 8 tahun. Sepertinya bisa awet umurnya sampai puluhan tahun deh....
>>>>> wakakaka...
>>>>> untuk sistem lain yang lebih kecil, dengan 4 workbook (xlsx [xlsx
>>>>> itu file yang di-zip loh, kalau baca isinya harus di-unzip dulu sama si
>>>>> Excel]) masing-masing saat ini baru berisi sekitar 400-500MB,
>>>>> sistemnya masih bisa bekerja dengan baik untuk report series 10
>>>>> tahun (client-nya pengolah data statistik yang butuh series panjang,
>>>>> desainnya sih maih mampu lah untuk series 20 tahun kalau butuh)
>>>>> > So, tidak perlu kuatir dengan fitur Get External Data. Fitur ini
>>>>> sekadar membuat koneksi data dan mengambil data dengan cara seperti
>>>>> pengolah data mengambil data kok.. (kaidah database)
>>>>> > maju terus pantang mundur.. perhatikan lagi sumber datanya secara
>>>>> saksama, kemudian perbaiki susunan dan buang segala yang memberatkan. Susun
>>>>> sql statement yang sesuai kebutuhan.
>>>>>
>>>>> fyi,
>>>>> Sebuah file workbook (data.xlsx) berisi 1 (satu) tabel data di sebuah
>>>>> sheet. File data.xlsx bisa dikoneksi oleh file report.xlsx melalui lebih
>>>>> dari 1 (satu) koneksi data.
>>>>> Jadi, report tentang k yang hanya butuh kolom tertentu dan record
>>>>> tertentu bisa dibuatkan sebuah koneksi tersendiri dengan fitur Get External
>>>>> Data.. Report yang lain (misal M) juga demikian.
>>>>> Jadi, 1 file report.xlsx bisa berisi banyak koneksi data ke file
>>>>> data..xlsx, yang wajarnya setiap koneksi memiliki sql statement yang
>>>>> berbeda sesuai kebutuhan.
>>>>>
>>>>> Regards,
>>>>> Kid
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> 2018-02-12 17:17 GMT+07:00 Stephen Saputro stephensaputro@gmail.com
>>>>> [belajar-excel] <belajar-excel@yahoogroups.com>:

>>>>>
>>>>>>
>>>>>>
>>>>>> Selamat sore semuanya,
>>>>>>
>>>>>> Mohon bantuannya untuk masalah saya, saya sedanng membuat file yang
>>>>>> terdiri dari file 1 untuk Report, dan file 2 untuk database. Saya
>>>>>> menggunakan pivot dengan get external data. hanya saja ada maslah yang saya
>>>>>> hadapi :
>>>>>>
>>>>>> 1. Pada saat database di buka, dan di geser ke kanan dan ke kiri,
>>>>>> kesannya sangat berat dan sering ngelag
>>>>>>
>>>>>> 2. pada saat saya membuka file report, dan melakukan refresh, maka
>>>>>> ada pesan error yang muncul. saya lampirkan pesan errornya ( excel error 2 )
>>>>>>
>>>>>> 3. Pada saat saya membuka file database, ada juga pesan error seperti
>>>>>> file terlampir ( excel error 1 )
>>>>>>
>>>>>> Mohon bantuannya apakah saya salah dalam membuat get external data ?
>>>>>> sebagai info tambahan :
>>>>>>
>>>>>> 1. di file database yang sudah di set Sebagai Tabel, mempunyai row
>>>>>> hingga 400 row
>>>>>>
>>>>>> 2. waktu menggunakan get external data, saya pake yang dari file excel
>>>>>>
>>>>>> Harapan saya dengan menggunakan exernal data, excel lebih menjadi
>>>>>> enteng karena database terpisah dengan reportnya.
>>>>>>
>>>>>> Terima kasih
>>>>>>
>>>>>>

>>>>>
>>>>
>>>
>>
>
>

Wed Feb 14, 2018 2:44 pm (PST) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Hai Cep,

Kemungkinan :
1. Ada conditional formatting : hapus conditional formatting bila ada
2. Ada cells yang di-Format cells fill color warna putih : set cells fill
color dengan No fill

oh iya,
sebaiknya, excel table object yang Anda buat diperbaiki agar lebih
berkaidah tabel database, terutama kolom No dan kolom Var.
Perhatikan record di baris Excel 12, record tersebut tidak memiliki nilai
di kolom No dan Var. Sebaiknya diisi juga.
Minimal, dibuatkan kolom lain di paling kanan, kolom No_Lengkap dan
VAR_lengkap, yang setiap cells nya berisi nilai (tidak ada yang kosong).

Regards,
Kid

2018-02-14 13:17 GMT+07:00 'CepWahyu' cepwahyu@gmail.com [belajar-excel] <
belajar-excel@yahoogroups.com>:

>
>
> Dear be-exceler
>
> Saya mengubah format table saya denga "Format as Tabel" supaya tiap baris
> memiliki warna berbeda (selang seling) tapi ada kolom (kolom VAR) yang
> tidak mau berubah. Kenapa ya?
>
>
>
>
>
> Sebelumnya terimaksih.
>
>
>
> Best Regards
>
> CW
>
>
>
>
>

Wed Feb 14, 2018 4:29 pm (PST) . Posted by:

"CepWahyu" juragan_75

Mr Kid,

Terimakasih atas responsnya.

Saya sudah lakukan perbaikan dari kemungkinan2 yang Mr Kid sampaikan dan Alhamdulillah sudah bisa sekarang.

Kebetulan memang data ini adalah lemparan dari database dimana sebelumnya setiap cell ada isinya tp sy hapus2 karena supaya enak dibaca saja.

Best Regards

CW

From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
Sent: Kamis, 15 Februari 2018 05.44
To: BeExcel <belajar-excel@yahoogroups.com>
Subject: Re: [belajar-excel] Format as Table

Hai Cep,

Kemungkinan :

1. Ada conditional formatting : hapus conditional formatting bila ada

2. Ada cells yang di-Format cells fill color warna putih : set cells fill color dengan No fill

oh iya,

sebaiknya, excel table object yang Anda buat diperbaiki agar lebih berkaidah tabel database, terutama kolom No dan kolom Var.

Perhatikan record di baris Excel 12, record tersebut tidak memiliki nilai di kolom No dan Var. Sebaiknya diisi juga.

Minimal, dibuatkan kolom lain di paling kanan, kolom No_Lengkap dan VAR_lengkap, yang setiap cells nya berisi nilai (tidak ada yang kosong).

Regards,

Kid

2018-02-14 13:17 GMT+07:00 'CepWahyu' cepwahyu@gmail.com <mailto:cepwahyu@gmail.com> [belajar-excel] <belajar-excel@yahoogroups.com <mailto:belajar-excel@yahoogroups.com> >:

Dear be-exceler

Saya mengubah format table saya denga "Format as Tabel" supaya tiap baris memiliki warna berbeda (selang seling) tapi ada kolom (kolom VAR) yang tidak mau berubah. Kenapa ya?

Sebelumnya terimaksih.

Best Regards

CW

Wed Feb 14, 2018 4:53 pm (PST) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Oh... kalau supaya enak, data dari database yang masih asli diberi conditional formatting. Atau di pivot table kan sekalian.

Sent from my smart enough phone

> On Feb 15, 2018, at 07:29, 'CepWahyu' cepwahyu@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com> wrote:
>
> Mr Kid,
>
> Terimakasih atas responsnya.
>
> Saya sudah lakukan perbaikan dari kemungkinan2 yang Mr Kid sampaikan dan Alhamdulillah sudah bisa sekarang.
>
>
>
> Kebetulan memang data ini adalah lemparan dari database dimana sebelumnya setiap cell ada isinya tp sy hapus2 karena supaya enak dibaca saja.
>
>
>
> Best Regards
>
> CW
>
>
>
> From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com]
> Sent: Kamis, 15 Februari 2018 05.44
> To: BeExcel <belajar-excel@yahoogroups.com>
> Subject: Re: [belajar-excel] Format as Table
>
>
>
>
>
> Hai Cep,
>
> Kemungkinan :
>
> 1. Ada conditional formatting : hapus conditional formatting bila ada
>
> 2.. Ada cells yang di-Format cells fill color warna putih : set cells fill color dengan No fill
>
> oh iya,
>
> sebaiknya, excel table object yang Anda buat diperbaiki agar lebih berkaidah tabel database, terutama kolom No dan kolom Var.
>
> Perhatikan record di baris Excel 12, record tersebut tidak memiliki nilai di kolom No dan Var. Sebaiknya diisi juga.
>
> Minimal, dibuatkan kolom lain di paling kanan, kolom No_Lengkap dan VAR_lengkap, yang setiap cells nya berisi nilai (tidak ada yang kosong).
>
>
>
>
>
> Regards,
>
> Kid
>
>
>
>
>
>
>
> 2018-02-14 13:17 GMT+07:00 'CepWahyu' cepwahyu@gmail.com [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
>
> Dear be-exceler
>
> Saya mengubah format table saya denga "Format as Tabel" supaya tiap baris memiliki warna berbeda (selang seling) tapi ada kolom (kolom VAR) yang tidak mau berubah. Kenapa ya?
>
>
>
> <image001.jpg>
>
>
>
> Sebelumnya terimaksih.
>
>
>
> Best Regards
>
> CW
>
>
>
>
>
>
>

Wed Feb 14, 2018 5:33 pm (PST) . Posted by:

"Fahmi Syaifullah" fahmisyaifullah20

assalamu'alaikum wr.wb
maaf om kid baru bisa respon emailnya.terima kasih om kid atas responya.saya butuh waktu untuk mencernanya . hheheesoalnya pivot table belum biasa om, saya biasa pakai rumus soalnya.kenapa database bisa berderet kebawah bgto karna memang saya menggunakan fungsi(small if) yang merujuk ke jurnal umumnya. sehingga satu2 akun bisa masuk ke d dan k nah menghilangkan data kosongnya yang diakibatkan oleh fungsi small if itu saya pakai filter (dengan mencek blanks).masih belom kenalanan sama pivot yang lebih mendalam om. hehee
terima kasih om kid dan Beexcelerwassalamu'alaikum wr.wbfahmi


Pada Rabu, 14 Februari 2018 11:50, "'Mr. Kid' mr.nmkid@gmail.com [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:


  Wa'alaikumussalam wr wb

Hai Fahmi,

Bagaimana jika dari raw data tersebut disusun jadi tabel data berkaidah tabel database lebih dulu ?
Excel table object dengan formula bisa membantu penyusunannya.
Misal sheetnya diberi nama Raw
Setelah itu, hasil Excel table object penyusun tabel data pada kolom tertentu yang berformula, di filter lalu di-copy. Paste values ke sheet data di record baru.
Sebaiknya, tabel di sheet data juga dibentuk sebagai Excel Table object.
Kemudian, pivot table yang sudah dibuat merujuk ke excel table object di sheet data bisa di-refresh.
Contoh seperti file terlampir (tanpa otomasi).
*** Kalau sudah bisa membuat proses tanpa otomasi yang cukup efisien, barulah memikirkan otomasinya.
*** silakan diubah dan dikembangkan sesuai kebutuhan
*** bisa jadi nantinya ada kebutuhan terhadap tabel-tabel referensi

Lalu buat otomasinya berisi :
1. di sheet Raw, excel table object yang ada dibuang isinya.
2. buka file sumber data, copy data, paste values ke sheet raw di record pertama excel table object penyusun tabel data
3. copy record di kolom tertentu dari sheet raw, paste values ke baris kosong pertama di sheet data (menjadi record baru excel table object di sheet data)
4. refresh all workbook
*** silakan dibuat sendiri ya...
*** mode pelit ber-VBA ON (biar pada tambah pinter, bagi yang mau)

Kalau saya,
sheet raw adalah hasil get externnal data ke file sumber data yang lokasi (folder) dan nama filenya tetap.
Jadi, ada file bernama sumber_data.xlsx sheet bernama 'apalah gitu yang tetap' berisi data seperti sheet1 Anda.
Sheet raw dibuat data connection menggunakan fitur get external data.
Lengkapi tabel hasil koneksi data dengan formula penyusun tabel data (toh cuma sekali buat untuk selamanya).
Lalu buat sheet data dengan header yang sama dengan header kolom-kolom berformula di sheet raw tadi (ini juga sekali buat untuk selamanya)
Lalu buat sheet baru untuk template report, berisi pivot table merujuk ke excel table di sheet data (ini juga sekali buat untuk selamanya)

Setelah itu barulah buat otomasinya, yang berisi :
1. refresh query table di sheet raw (gak perlu bikin script buka workbook sumber_data.xlsx copy lalu tutup tanpa save)
2. filter sheet raw, copy hasil filter di kolom tertentu yang berisi formula tadi, paste values ke sheet data di cells kosong dibawah excel table data yang ada
3. refresh semua pivot tables (refresh all juga tidak mengapa, toh sumber_data.xlsx juga tidak besar.

*** sila kalau mau dicoba...

Wassalamu'alaikum wr wb
Kid

2018-02-14 9:56 GMT+07:00 Fahmi Syaifullah fahmisyaifullah20@yahoo.com [belajar-excel] <belajar-excel@yahoogroups.com>:

  assalamu'alaikum wr.wb
    selamat pagi BeExceler.
mau tanya saya sudah membuat cekrip seperti dibawah ini

On Error Resume Next
    Selection.Copy
    Worksheets.Add
    ActiveSheet.Name = "sheet2"
    Sheets("Sheet2").Select
        If Range("A2").Value = "" Then
            Range("A2").Select
        Else
            Range("A2").End(xlToRight). Select
            ActiveCell.Offset(0, 2).Select
        End If
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
     Sheets("sheet1").Select
     Selection.Delete Shift:=xlUp
End Sub
niatnya saya ingin mengkopi data yang di sheet 1 ke sheet 2 dengan memisahkan perbulan (ada akun yang berbeda dan bulan) menjadikan 1 tahun dengan masing2 akun.sebelumnya ini pertanyaan saya yang lanjutan kemaren dengan om kid masalah filter dan delete itu.saya coba dengan menggunakan VBA . tapi saya belom bisa untuk mempaste ke samping terus2an saya ingin mencoba attribut For Next untuk perkalian yang warna biru (angka 2) " ActiveCell.Offset(0, 2).Select "agar data selalu menyamping kearah kanan . tapi nanti yang di ulang adalah bagian selection.copynya. karna selection.copy saya sesuaikan dengan data yang ingin saya copy.sebenarnya ada kreterianya hanya saja saya belum mahir.
mohon bantuannya BeExceler. maaf mengganggu waktunya.
wassalamu'alaikum wr.wb
terima kasih.fahmi

#yiv1014958516 #yiv1014958516 -- #yiv1014958516ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1014958516 #yiv1014958516ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1014958516 #yiv1014958516ygrp-mkp #yiv1014958516hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1014958516 #yiv1014958516ygrp-mkp #yiv1014958516ads {margin-bottom:10px;}#yiv1014958516 #yiv1014958516ygrp-mkp .yiv1014958516ad {padding:0 0;}#yiv1014958516 #yiv1014958516ygrp-mkp .yiv1014958516ad p {margin:0;}#yiv1014958516 #yiv1014958516ygrp-mkp .yiv1014958516ad a {color:#0000ff;text-decoration:none;}#yiv1014958516 #yiv1014958516ygrp-sponsor #yiv1014958516ygrp-lc {font-family:Arial;}#yiv1014958516 #yiv1014958516ygrp-sponsor #yiv1014958516ygrp-lc #yiv1014958516hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1014958516 #yiv1014958516ygrp-sponsor #yiv1014958516ygrp-lc .yiv1014958516ad {margin-bottom:10px;padding:0 0;}#yiv1014958516 #yiv1014958516actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1014958516 #yiv1014958516activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1014958516 #yiv1014958516activity span {font-weight:700;}#yiv1014958516 #yiv1014958516activity span:first-child {text-transform:uppercase;}#yiv1014958516 #yiv1014958516activity span a {color:#5085b6;text-decoration:none;}#yiv1014958516 #yiv1014958516activity span span {color:#ff7900;}#yiv1014958516 #yiv1014958516activity span .yiv1014958516underline {text-decoration:underline;}#yiv1014958516 .yiv1014958516attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1014958516 .yiv1014958516attach div a {text-decoration:none;}#yiv1014958516 .yiv1014958516attach img {border:none;padding-right:5px;}#yiv1014958516 .yiv1014958516attach label {display:block;margin-bottom:5px;}#yiv1014958516 .yiv1014958516attach label a {text-decoration:none;}#yiv1014958516 blockquote {margin:0 0 0 4px;}#yiv1014958516 .yiv1014958516bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1014958516 .yiv1014958516bold a {text-decoration:none;}#yiv1014958516 dd.yiv1014958516last p a {font-family:Verdana;font-weight:700;}#yiv1014958516 dd.yiv1014958516last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1014958516 dd.yiv1014958516last p span.yiv1014958516yshortcuts {margin-right:0;}#yiv1014958516 div.yiv1014958516attach-table div div a {text-decoration:none;}#yiv1014958516 div.yiv1014958516attach-table {width:400px;}#yiv1014958516 div.yiv1014958516file-title a, #yiv1014958516 div.yiv1014958516file-title a:active, #yiv1014958516 div.yiv1014958516file-title a:hover, #yiv1014958516 div.yiv1014958516file-title a:visited {text-decoration:none;}#yiv1014958516 div.yiv1014958516photo-title a, #yiv1014958516 div.yiv1014958516photo-title a:active, #yiv1014958516 div.yiv1014958516photo-title a:hover, #yiv1014958516 div.yiv1014958516photo-title a:visited {text-decoration:none;}#yiv1014958516 div#yiv1014958516ygrp-mlmsg #yiv1014958516ygrp-msg p a span.yiv1014958516yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1014958516 .yiv1014958516green {color:#628c2a;}#yiv1014958516 .yiv1014958516MsoNormal {margin:0 0 0 0;}#yiv1014958516 o {font-size:0;}#yiv1014958516 #yiv1014958516photos div {float:left;width:72px;}#yiv1014958516 #yiv1014958516photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv1014958516 #yiv1014958516photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1014958516 #yiv1014958516reco-category {font-size:77%;}#yiv1014958516 #yiv1014958516reco-desc {font-size:77%;}#yiv1014958516 .yiv1014958516replbq {margin:4px;}#yiv1014958516 #yiv1014958516ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1014958516 #yiv1014958516ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1014958516 #yiv1014958516ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1014958516 #yiv1014958516ygrp-mlmsg select, #yiv1014958516 input, #yiv1014958516 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1014958516 #yiv1014958516ygrp-mlmsg pre, #yiv1014958516 code {font:115% monospace;}#yiv1014958516 #yiv1014958516ygrp-mlmsg * {line-height:1.22em;}#yiv1014958516 #yiv1014958516ygrp-mlmsg #yiv1014958516logo {padding-bottom:10px;}#yiv1014958516 #yiv1014958516ygrp-msg p a {font-family:Verdana;}#yiv1014958516 #yiv1014958516ygrp-msg p#yiv1014958516attach-count span {color:#1E66AE;font-weight:700;}#yiv1014958516 #yiv1014958516ygrp-reco #yiv1014958516reco-head {color:#ff7900;font-weight:700;}#yiv1014958516 #yiv1014958516ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1014958516 #yiv1014958516ygrp-sponsor #yiv1014958516ov li a {font-size:130%;text-decoration:none;}#yiv1014958516 #yiv1014958516ygrp-sponsor #yiv1014958516ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1014958516 #yiv1014958516ygrp-sponsor #yiv1014958516ov ul {margin:0;padding:0 0 0 8px;}#yiv1014958516 #yiv1014958516ygrp-text {font-family:Georgia;}#yiv1014958516 #yiv1014958516ygrp-text p {margin:0 0 1em 0;}#yiv1014958516 #yiv1014958516ygrp-text tt {font-size:120%;}#yiv1014958516 #yiv1014958516ygrp-vital ul li:last-child {border-right:none !important;}#yiv1014958516

Thu Feb 15, 2018 12:49 am (PST) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Wa'alaikumussalam wr wb

Hai Fahmi,

Semoga yang tercerna segera mendarah daging ya...

Bagaimana kalau bagian pivot table nya tidak dipikirkan dulu. Maksudnya,
seluruh output-nya tidak perlu dipikirkan dulu, karena sedang fokus mencoba
menyusun data yang baik.
Jadi, formula sederhana yang ada file lampiran tempo lalu di sheet RAW bisa
segera dipahami tujuannya bukan ?
Bagaimana kalau kemudian mencoba membuat VBA untuk proses :
> filter data di sheet RAW pada kolom terakhir, dengan kriteria yang
bernilai lebih dari 0.
* penataan agar kolom terakhir selalu menjadi acuan filter agar bisa
lebih fleksibel kedepannya ketika sumber data membutuhkan penambahan kolom,
kolom data hasil formula juga bertambah, tetapi kolom terakhir tetap
sebagai acuan filter
Bagian itu dulu deh...
Boleh di-share di milis.
Setelah itu nanti baru proses berikutnya untuk memilih area kolom yang
berformula pada hasil filter.

Wassalamu'alaikum wr wb
Kid

P.S.
Semua BeExceller boleh mencoba dan share ke milis hasil uji cobanya.

2018-02-15 8:31 GMT+07:00 Fahmi Syaifullah fahmisyaifullah20@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> assalamu'alaikum wr.wb
>
> maaf om kid baru bisa respon emailnya.
> terima kasih om kid atas responya.
> saya butuh waktu untuk mencernanya . hhehee
> soalnya pivot table belum biasa om, saya biasa pakai rumus soalnya.
> kenapa database bisa berderet kebawah bgto karna memang saya menggunakan
> fungsi
> (small if) yang merujuk ke jurnal umumnya. sehingga satu2 akun bisa masuk
> ke d dan k nah menghilangkan data kosongnya yang diakibatkan oleh fungsi
> small if itu saya pakai filter (dengan mencek blanks).
> masih belom kenalanan sama pivot yang lebih mendalam om. hehee
>
> terima kasih om kid dan Beexceler
> wassalamu'alaikum wr.wb
> fahmi
>
>
>
> Pada Rabu, 14 Februari 2018 11:50, "'Mr. Kid' mr.nmkid@gmail.com
> [belajar-excel]" <belajar-excel@yahoogroups.com> menulis:
>
>
>
> Wa'alaikumussalam wr wb
>
> Hai Fahmi,
>
> Bagaimana jika dari raw data tersebut disusun jadi tabel data berkaidah
> tabel database lebih dulu ?
> Excel table object dengan formula bisa membantu penyusunannya.
> Misal sheetnya diberi nama Raw
> Setelah itu, hasil Excel table object penyusun tabel data pada kolom
> tertentu yang berformula, di filter lalu di-copy. Paste values ke sheet
> data di record baru.
> Sebaiknya, tabel di sheet data juga dibentuk sebagai Excel Table object.
> Kemudian, pivot table yang sudah dibuat merujuk ke excel table object di
> sheet data bisa di-refresh.
> Contoh seperti file terlampir (tanpa otomasi).
> *** Kalau sudah bisa membuat proses tanpa otomasi yang cukup efisien,
> barulah memikirkan otomasinya.
> *** silakan diubah dan dikembangkan sesuai kebutuhan
> *** bisa jadi nantinya ada kebutuhan terhadap tabel-tabel referensi
>
> Lalu buat otomasinya berisi :
> 1. di sheet Raw, excel table object yang ada dibuang isinya.
> 2. buka file sumber data, copy data, paste values ke sheet raw di record
> pertama excel table object penyusun tabel data
> 3. copy record di kolom tertentu dari sheet raw, paste values ke baris
> kosong pertama di sheet data (menjadi record baru excel table object di
> sheet data)
> 4. refresh all workbook
> *** silakan dibuat sendiri ya...
> *** mode pelit ber-VBA ON (biar pada tambah pinter, bagi yang mau)
>
> Kalau saya,
> sheet raw adalah hasil get externnal data ke file sumber data yang lokasi
> (folder) dan nama filenya tetap.
> Jadi, ada file bernama sumber_data.xlsx sheet bernama 'apalah gitu yang
> tetap' berisi data seperti sheet1 Anda.
> Sheet raw dibuat data connection menggunakan fitur get external data.
> Lengkapi tabel hasil koneksi data dengan formula penyusun tabel data (toh
> cuma sekali buat untuk selamanya).
> Lalu buat sheet data dengan header yang sama dengan header kolom-kolom
> berformula di sheet raw tadi (ini juga sekali buat untuk selamanya)
> Lalu buat sheet baru untuk template report, berisi pivot table merujuk ke
> excel table di sheet data (ini juga sekali buat untuk selamanya)
>
> Setelah itu barulah buat otomasinya, yang berisi :
> 1. refresh query table di sheet raw (gak perlu bikin script buka workbook
> sumber_data.xlsx copy lalu tutup tanpa save)
> 2. filter sheet raw, copy hasil filter di kolom tertentu yang berisi
> formula tadi, paste values ke sheet data di cells kosong dibawah excel
> table data yang ada
> 3. refresh semua pivot tables (refresh all juga tidak mengapa, toh
> sumber_data.xlsx juga tidak besar.
>
> *** sila kalau mau dicoba...
>
>
> Wassalamu'alaikum wr wb
> Kid
>
>
>
> 2018-02-14 9:56 GMT+07:00 Fahmi Syaifullah fahmisyaifullah20@yahoo..com
> [belajar-excel] <belajar-excel@yahoogroups.com>:
>
>
> assalamu'alaikum wr.wb
>
> selamat pagi BeExceler.
>
> mau tanya saya sudah membuat cekrip seperti dibawah ini
>
>
> On Error Resume Next
> Selection.Copy
> Worksheets.Add
> ActiveSheet.Name = "sheet2"
> Sheets("Sheet2").Select
> If Range("A2").Value = "" Then
> Range("A2").Select
> Else
> Range("A2").End(xlToRight). Select
> ActiveCell.Offset(0, 2).Select
> End If
> ActiveSheet.Paste
> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
> Sheets("sheet1").Select
> Selection.Delete Shift:=xlUp
> End Sub
>
> niatnya saya ingin mengkopi data yang di sheet 1 ke sheet 2 dengan
> memisahkan perbulan (ada akun yang berbeda dan bulan) menjadikan 1 tahun
> dengan masing2 akun.
> sebelumnya ini pertanyaan saya yang lanjutan kemaren dengan om kid masalah
> filter dan delete itu.
> saya coba dengan menggunakan VBA . tapi saya belom bisa untuk mempaste ke
> samping terus2an
> saya ingin mencoba attribut *For Next* untuk perkalian yang warna biru
> (angka 2) " ActiveCell.Offset(0, 2).Select "
> agar data selalu menyamping kearah kanan . tapi nanti yang di ulang adalah
> bagian selection.copynya.
> karna selection.copy saya sesuaikan dengan data yang ingin saya copy.
> sebenarnya ada kreterianya hanya saja saya belum mahir.
>
> mohon bantuannya BeExceler.
> maaf mengganggu waktunya.
>
> wassalamu'alaikum wr.wb
>
> terima kasih.
> fahmi
>
>
>
>
>
>

Thu Feb 15, 2018 1:53 am (PST) . Posted by:

"Sony Ario"

Halo Fahmi,

Saya cuma bantu sekedar nya saja atas dasar email Mr. Kid.

Di file terlampir datanya saya coba rapihkan sedikit. Sehingga berkaidah
database.
Lalu dibuat Pivot Table.

Kalau sudah jadi Pivot Table, kalau memisahkan per tanggal per sheet bisa
gunakan menu "Show Report Filter Pages" dibawah menu Analyze.

Maaf kalau masih belum terjawab.

*gnl-excelinfo.blogspot.co.id <http://gnl-excelinfo.blogspot.co.id>*

2018-02-14 9:56 GMT+07:00 Fahmi Syaifullah fahmisyaifullah20@yahoo.com
[belajar-excel] <belajar-excel@yahoogroups.com>:

>
>
> assalamu'alaikum wr.wb
>
> selamat pagi BeExceler.
>
> mau tanya saya sudah membuat cekrip seperti dibawah ini
>
>
> On Error Resume Next
> Selection.Copy
> Worksheets.Add
> ActiveSheet.Name = "sheet2"
> Sheets("Sheet2").Select
> If Range("A2").Value = "" Then
> Range("A2").Select
> Else
> Range("A2").End(xlToRight).Select
> ActiveCell.Offset(0, 2).Select
> End If
> ActiveSheet.Paste
> Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
> SkipBlanks:=False, Transpose:=False
> Sheets("sheet1").Select
> Selection.Delete Shift:=xlUp
> End Sub
>
> niatnya saya ingin mengkopi data yang di sheet 1 ke sheet 2 dengan
> memisahkan perbulan (ada akun yang berbeda dan bulan) menjadikan 1 tahun
> dengan masing2 akun.
> sebelumnya ini pertanyaan saya yang lanjutan kemaren dengan om kid masalah
> filter dan delete itu.
> saya coba dengan menggunakan VBA . tapi saya belom bisa untuk mempaste ke
> samping terus2an
> saya ingin mencoba attribut *For Next* untuk perkalian yang warna biru
> (angka 2) " ActiveCell.Offset(0, 2).Select "
> agar data selalu menyamping kearah kanan . tapi nanti yang di ulang adalah
> bagian selection.copynya.
> karna selection.copy saya sesuaikan dengan data yang ingin saya copy.
> sebenarnya ada kreterianya hanya saja saya belum mahir.
>
> mohon bantuannya BeExceler.
> maaf mengganggu waktunya.
>
> wassalamu'alaikum wr.wb
>
> terima kasih.
> fahmi
>
>
>
============================================================
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
---------------------------------------------------------------------
Diposting oleh EXCEL FEED LEARNING di 02.36

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)