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.nm...@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 stephensapu...@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.nm...@gmail.com [belajar-excel] < >> belajar-ex...@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 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 >>>>>> >>>>>> >>>>> >>>> >>> >> > >
