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 stephensapu...@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.nm...@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 stephensapu...@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
>>>
>>>
>>
> 
>
  • [belajar-excel] G... Stephen Saputro stephensapu...@gmail.com [belajar-excel]
    • Re: [belajar... hendrik karnadi hendrikkarn...@yahoo.com [belajar-excel]
    • Re: [belajar... 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
      • Re: [bel... 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
        • Re: ... Stephen Saputro stephensapu...@gmail.com [belajar-excel]
          • ... 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
            • ... Stephen Saputro stephensapu...@gmail.com [belajar-excel]
              • ... Stephen Saputro stephensapu...@gmail.com [belajar-excel]
                • ... 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
                • ... Stephen Saputro stephensapu...@gmail.com [belajar-excel]
                • ... 'Mr. Kid' mr.nm...@gmail.com [belajar-excel]
                • ... Zainul Ulum zainul_u...@yahoo.com [belajar-excel]
                • ... Stephen Saputro stephensapu...@gmail.com [belajar-excel]

Kirim email ke