2 Messages
Digest #4716
Messages
Sat Feb 23, 2019 4:38 am (PST) . Posted by:
"Agus"
Assalamu'alaikum warahmatullah
Dear member B-Excel,
saya membuat sebuah nama pada sebuah file excel (Define Name)
Nama tersebut merujuk pada sebuah range pada file yang lain.
Nama tersebut dapat saya gunakan dengan baik pada formula excel,
misalnya menggunakan INDEX(Nama,1,1)
Namun akan bermasalah jika digunakan di Pivot, dengan pesan " Data
Reference is not valid"
Apakah ada kesalahan pembuatan Nama tersebut? ataukah memang pivot tidak
dapat menggunakan nama dengan referensi file lain?
Saya membutuhkan referensi yang dinamis sesuai pesan Mas Kid di sini
<http://excel-mr-kid.blogspot.com/2013/05/pivot-table-2-menata-data-source.html>.
Jika saya menggunakan Eksternal data dapat berjalan dengan baik, Namun
apakah Eksternal data dengan file excel (xlsx) bersifat dinamis?
Terima kasih
Agus
Dear member B-Excel,
saya membuat sebuah nama pada sebuah file excel (Define Name)
Nama tersebut merujuk pada sebuah range pada file yang lain.
Nama tersebut dapat saya gunakan dengan baik pada formula excel,
misalnya menggunakan INDEX(Nama,1,1)
Namun akan bermasalah jika digunakan di Pivot, dengan pesan " Data
Reference is not valid"
Apakah ada kesalahan pembuatan Nama tersebut? ataukah memang pivot tidak
dapat menggunakan nama dengan referensi file lain?
Saya membutuhkan referensi yang dinamis sesuai pesan Mas Kid di sini
<http://excel-mr-kid.blogspot.com/2013/05/pivot-table-2-menata-data-source.html>.
Jika saya menggunakan Eksternal data dapat berjalan dengan baik, Namun
apakah Eksternal data dengan file excel (xlsx) bersifat dinamis?
Terima kasih
Agus
Sat Feb 23, 2019 7:56 am (PST) . Posted by:
"Mr. Kid" nmkid.family@ymail.com
Wa'alaikumussalam wr wb
Hai mas Nang,
#jawaban singkat dari beberapa kalimat yang tampak membutuhkan jawaban :
Apakah ada kesalahan pembuatan Nama tersebut?
> Bisa jadi ya, bisa jadi tidak, tetapi ada atau tidaknya kesalahan, tidak
ada hubungannya dengan masalah pivot table yang merujuk ke workbook lain.
ataukah memang pivot tidak dapat menggunakan nama dengan referensi file
> lain?
> Ya.
> Jadi, pivot table tidak bekerja seperti formula.
Saya membutuhkan referensi yang dinamis
> Pada jaman excel modern sekarang ini, fitur Excel Table menjadi
rekomendasi utama.
Jika saya menggunakan Eksternal data dapat berjalan dengan baik, Namun
> apakah Eksternal data dengan file excel (xlsx) bersifat dinamis?
> Jika yang dimaksud dinamis adalah mengikuti perkembangan data pada sheet
yang dirujuk, maka jawabannya adalah YA.
> Jika yang dimaksud dinamis adalah mengikuti rujukan nama range yang
dinamis, jawabannya TIDAK, karena fitur Get External Data tidak bisa
merujuk nama range yang dinamis.
> Jika get external data yang digunakan adalah versi lawas fitur tersebut
(Legacy), maka yang paling dinamis adalah merujuk ke sheet, sehingga header
sebaiknya di baris 1.
> Jika get external data yang digunakan adalah versi baru (berbasis fitur
PowerQuery), maka yang paling dinamis adalah merujuk fitur Excel Table yang
ada di sumber data.
#obrolan malem minggu tentang Get External Data yang akan menjadi
PivotTable data source :
1. Contoh file sumber data (sisi kiri) dan file milik user yang menggunakan
Get External Data untuk mengambil data (sisi kanan)
[image: xl038_ged02_files.gif]
*** masing-masing file terbuka di instansi Excel yang berbeda (lihat di
Switch Windows [ribbon View] yang hanya menampilkan sebuah workbook)
*** artinya, tidak mungkin membuat formula dari workbook kiri merujuk ke
workbook kanan (maupun sebaliknya) secara langsung dengan menulis = lalu
tunjuk cells di workbook lainnya
2. Fitur Get External Data
> xl2003 ke bawah : Import Data
> xl2007 sampai xl2013 : Get External Data
> xl2010 ke atas : Power Query (xl2010 : berupa addin yang perlu
di-install; xl2013 : sudah ter-install tapi perlu diaktifkan; xl2016 ke
atas : siap pakai sebagai fitur bawaan)
*** mulai xl2016, Get External Data bawaan dari xl2007 akan disebut sebagai
Legacy.
(di xl2016 ke atas : yang kiri adalah Get External Data (Legacy) bawaan
xl2007, yang kanan adalah power query; keuanya ada di ribbon Data -> grup
menu Get External Data)
[image: xl038_ged01_legacy_n_pq.gif]
> step dalam Get External Data (Legacy) [catatan : penyusunan query untuk
menata data yang dibutuhkan, dilakukan melalui properties koneksi data yang
terbentuk]
[image: xl038_ged04_legacy_steps.gif]
3. Contoh data di workbook sumber data (pada nomor 2 di atas, isi dari
workbook sisi kiri)
[image: xl038_ged03_sheets_n_xltable.gif]
*** Sisi kiri : sheet bernama Order_Detail berisi sebuah dataset di suatu
area range
*** Sisi kanan : sheet bernama xlTable berisi sebuah dataset sebagai sebuah
Excel Table (lihat ribbon Home -> Format as table), dengan nama Excel Table
adalah _tblORDER_
*** Excel Table akan secara dinamis berubah luasannya mengikuti jumlah
record data maupun kolom data
4. Nama range yang sengaja dibuat untuk menjadi bahan obrolan :
[image: xl038_ged05_named_range.gif]
*** Perhatikan icon-nya : _tblORDER_ adalah fitur Excel Table, selainnya
adalah defined named range (Nama range)
*** Nama range berawalan _rng_xlTable_ : merujuk ke Excel Table _tblORDER_
yang ada di sheet xlTable (lihat bagian refers to)
*** Nama range berawalan rngOrderDetail_ : merujuk ke range yang ada di
sheet Order_Detail
*** Nama range dinamis menggunakan fungsi Offset (bisa juga menggunakan
fungsi lain seperti Index ataupun Indirect)
*** *SELAIN *nama range rngOrderDetail_STATIS : bersifat dinamis
(mengikuti luasan data yang ada di sheet yang dirujuk)
5. (obrolannya) Dataset yang bisa digunakan dalam fitur Get External Data :
(kiri -> Legacy Get External Data; kanan -> PowerQuery)
[image: xl038_ged06_dataset_con.gif]*** Sisi kiri (pilihan dalam Legacy Get
External Data) :
1. Icon-nya sama (sulit membedakan dataset berdasar icon)
2. nama sheet ditandai dengan akhiran $ -> artinya, merujuk ke seluruh
baris dan kolom berisi data yang ada di sebuah sheet
3. nama range yang bisa menjadi dataset hasil koneksi data (hasil Get
External Data) *HANYA *yang berupa rujukan statis
4. sifat dinamis hasil koneksi data mengikuti records dan kolom dalam
dataset yang dirujuk (jadi, merujuk ke rngOrderDetail_STATIS akan
menghasilkan koneksi data yang statis juga)
*** Sisi kanan (pilihan dalam PowerPivot) :
1. Icon-nya menunjukkan jenis dataset (lihat icon Excel Table [seperti
_tblORDER_], icon sheet [seperti Order_Detail], icon nama range [seperti
rngOrderDetail_STATIS])
2. nama range yang bisa menjadi dataset hasil koneksi data (hasil Get
External Data) *HANYA *yang berupa rujukan statis
3. sifat dinamis hasil koneksi data mengikuti records dan kolom dalam
dataset yang dirujuk (jadi, merujuk ke rngOrderDetail_STATIS akan
menghasilkan koneksi data yang statis juga)
4. ada opsi untuk Load ke area default Excel setempat, Load to sesuai
kebutuhan user, atau Edit dataset untuk proses pemilihan dan penataan data
yang akan diambil (penyusunan query)
*** PowerQuery sejak versi 2 sudah jauh lebih baik dan direkomendasikan
untuk dimanfaatkan sebesar-besar kesejahteraan user.
6. Import Data yang sudah dipengaruhi PowerPivot :
> hasil dari Get External Data berujung pada proses import data
[image: xl038_ged07_import_data.gif]
*** Pada Excel yang telah ter-install fitur PowerPivot (virtual analysis
service pembentuk OLAP data), maka akan muncul opsi 'Add this data to the
Data Model'
*** Jika akan bekerja pada beberapa dataset sekaligus, direkomendasikan
untuk memanfaatkan fitur PowerPivot, terutama versi 2 ke atas.
Kira-kira demikianlah obrolan malem minggu kali ini...
Wassalamu'alaikum wr wb
Kid
On Sat, Feb 23, 2019 at 7:38 PM Agus nangagus@gmail.com [belajar-excel] <
belajar-excel@yahoogroups.com> wrote:
>
>
> Assalamu'alaikum warahmatullah
>
> Dear member B-Excel,
>
> saya membuat sebuah nama pada sebuah file excel (Define Name)
>
> Nama tersebut merujuk pada sebuah range pada file yang lain.
>
> Nama tersebut dapat saya gunakan dengan baik pada formula excel, misalnya
> menggunakan INDEX(Nama,1,1)
>
> Namun akan bermasalah jika digunakan di Pivot, dengan pesan " Data
> Reference is not valid"
>
> Apakah ada kesalahan pembuatan Nama tersebut? ataukah memang pivot tidak
> dapat menggunakan nama dengan referensi file lain?
>
> Saya membutuhkan referensi yang dinamis sesuai pesan Mas Kid di sini
> <http://excel-mr-kid.blogspot.com/2013/05/pivot-table-2-menata-data-source.html>
> .
>
> Jika saya menggunakan Eksternal data dapat berjalan dengan baik, Namun
> apakah Eksternal data dengan file excel (xlsx) bersifat dinamis?
>
> Terima kasih
>
> Agus
>
>
>
>
>
Hai mas Nang,
#jawaban singkat dari beberapa kalimat yang tampak membutuhkan jawaban :
Apakah ada kesalahan pembuatan Nama tersebut?
> Bisa jadi ya, bisa jadi tidak, tetapi ada atau tidaknya kesalahan, tidak
ada hubungannya dengan masalah pivot table yang merujuk ke workbook lain.
ataukah memang pivot tidak dapat menggunakan nama dengan referensi file
> lain?
> Ya.
> Jadi, pivot table tidak bekerja seperti formula.
Saya membutuhkan referensi yang dinamis
> Pada jaman excel modern sekarang ini, fitur Excel Table menjadi
rekomendasi utama.
Jika saya menggunakan Eksternal data dapat berjalan dengan baik, Namun
> apakah Eksternal data dengan file excel (xlsx) bersifat dinamis?
> Jika yang dimaksud dinamis adalah mengikuti perkembangan data pada sheet
yang dirujuk, maka jawabannya adalah YA.
> Jika yang dimaksud dinamis adalah mengikuti rujukan nama range yang
dinamis, jawabannya TIDAK, karena fitur Get External Data tidak bisa
merujuk nama range yang dinamis.
> Jika get external data yang digunakan adalah versi lawas fitur tersebut
(Legacy), maka yang paling dinamis adalah merujuk ke sheet, sehingga header
sebaiknya di baris 1.
> Jika get external data yang digunakan adalah versi baru (berbasis fitur
PowerQuery), maka yang paling dinamis adalah merujuk fitur Excel Table yang
ada di sumber data.
#obrolan malem minggu tentang Get External Data yang akan menjadi
PivotTable data source :
1. Contoh file sumber data (sisi kiri) dan file milik user yang menggunakan
Get External Data untuk mengambil data (sisi kanan)
[image: xl038_ged02_files.gif]
*** masing-masing file terbuka di instansi Excel yang berbeda (lihat di
Switch Windows [ribbon View] yang hanya menampilkan sebuah workbook)
*** artinya, tidak mungkin membuat formula dari workbook kiri merujuk ke
workbook kanan (maupun sebaliknya) secara langsung dengan menulis = lalu
tunjuk cells di workbook lainnya
2. Fitur Get External Data
> xl2003 ke bawah : Import Data
> xl2007 sampai xl2013 : Get External Data
> xl2010 ke atas : Power Query (xl2010 : berupa addin yang perlu
di-install; xl2013 : sudah ter-install tapi perlu diaktifkan; xl2016 ke
atas : siap pakai sebagai fitur bawaan)
*** mulai xl2016, Get External Data bawaan dari xl2007 akan disebut sebagai
Legacy.
(di xl2016 ke atas : yang kiri adalah Get External Data (Legacy) bawaan
xl2007, yang kanan adalah power query; keuanya ada di ribbon Data -> grup
menu Get External Data)
[image: xl038_ged01_legacy_n_pq.gif]
> step dalam Get External Data (Legacy) [catatan : penyusunan query untuk
menata data yang dibutuhkan, dilakukan melalui properties koneksi data yang
terbentuk]
[image: xl038_ged04_legacy_steps.gif]
3. Contoh data di workbook sumber data (pada nomor 2 di atas, isi dari
workbook sisi kiri)
[image: xl038_ged03_sheets_n_xltable.gif]
*** Sisi kiri : sheet bernama Order_Detail berisi sebuah dataset di suatu
area range
*** Sisi kanan : sheet bernama xlTable berisi sebuah dataset sebagai sebuah
Excel Table (lihat ribbon Home -> Format as table), dengan nama Excel Table
adalah _tblORDER_
*** Excel Table akan secara dinamis berubah luasannya mengikuti jumlah
record data maupun kolom data
4. Nama range yang sengaja dibuat untuk menjadi bahan obrolan :
[image: xl038_ged05_named_range.gif]
*** Perhatikan icon-nya : _tblORDER_ adalah fitur Excel Table, selainnya
adalah defined named range (Nama range)
*** Nama range berawalan _rng_xlTable_ : merujuk ke Excel Table _tblORDER_
yang ada di sheet xlTable (lihat bagian refers to)
*** Nama range berawalan rngOrderDetail_ : merujuk ke range yang ada di
sheet Order_Detail
*** Nama range dinamis menggunakan fungsi Offset (bisa juga menggunakan
fungsi lain seperti Index ataupun Indirect)
*** *SELAIN *nama range rngOrderDetail_STATIS : bersifat dinamis
(mengikuti luasan data yang ada di sheet yang dirujuk)
5. (obrolannya) Dataset yang bisa digunakan dalam fitur Get External Data :
(kiri -> Legacy Get External Data; kanan -> PowerQuery)
[image: xl038_ged06_dataset_con.gif]*** Sisi kiri (pilihan dalam Legacy Get
External Data) :
1. Icon-nya sama (sulit membedakan dataset berdasar icon)
2. nama sheet ditandai dengan akhiran $ -> artinya, merujuk ke seluruh
baris dan kolom berisi data yang ada di sebuah sheet
3. nama range yang bisa menjadi dataset hasil koneksi data (hasil Get
External Data) *HANYA *yang berupa rujukan statis
4. sifat dinamis hasil koneksi data mengikuti records dan kolom dalam
dataset yang dirujuk (jadi, merujuk ke rngOrderDetail_STATIS akan
menghasilkan koneksi data yang statis juga)
*** Sisi kanan (pilihan dalam PowerPivot) :
1. Icon-nya menunjukkan jenis dataset (lihat icon Excel Table [seperti
_tblORDER_], icon sheet [seperti Order_Detail], icon nama range [seperti
rngOrderDetail_STATIS])
2. nama range yang bisa menjadi dataset hasil koneksi data (hasil Get
External Data) *HANYA *yang berupa rujukan statis
3. sifat dinamis hasil koneksi data mengikuti records dan kolom dalam
dataset yang dirujuk (jadi, merujuk ke rngOrderDetail_STATIS akan
menghasilkan koneksi data yang statis juga)
4. ada opsi untuk Load ke area default Excel setempat, Load to sesuai
kebutuhan user, atau Edit dataset untuk proses pemilihan dan penataan data
yang akan diambil (penyusunan query)
*** PowerQuery sejak versi 2 sudah jauh lebih baik dan direkomendasikan
untuk dimanfaatkan sebesar-besar kesejahteraan user.
6. Import Data yang sudah dipengaruhi PowerPivot :
> hasil dari Get External Data berujung pada proses import data
[image: xl038_ged07_import_data.gif]
*** Pada Excel yang telah ter-install fitur PowerPivot (virtual analysis
service pembentuk OLAP data), maka akan muncul opsi 'Add this data to the
Data Model'
*** Jika akan bekerja pada beberapa dataset sekaligus, direkomendasikan
untuk memanfaatkan fitur PowerPivot, terutama versi 2 ke atas.
Kira-kira demikianlah obrolan malem minggu kali ini...
Wassalamu'alaikum wr wb
Kid
On Sat, Feb 23, 2019 at 7:38 PM Agus nangagus@gmail.com [belajar-excel] <
belajar-excel@yahoogroups.com> wrote:
>
>
> Assalamu'alaikum warahmatullah
>
> Dear member B-Excel,
>
> saya membuat sebuah nama pada sebuah file excel (Define Name)
>
> Nama tersebut merujuk pada sebuah range pada file yang lain.
>
> Nama tersebut dapat saya gunakan dengan baik pada formula excel, misalnya
> menggunakan INDEX(Nama,1,1)
>
> Namun akan bermasalah jika digunakan di Pivot, dengan pesan " Data
> Reference is not valid"
>
> Apakah ada kesalahan pembuatan Nama tersebut? ataukah memang pivot tidak
> dapat menggunakan nama dengan referensi file lain?
>
> Saya membutuhkan referensi yang dinamis sesuai pesan Mas Kid di sini
> <http://excel-mr-kid.blogspot.com/2013/05/pivot-table-2-menata-data-source.html>
> .
>
> Jika saya menggunakan Eksternal data dapat berjalan dengan baik, Namun
> apakah Eksternal data dengan file excel (xlsx) bersifat dinamis?
>
> Terima kasih
>
> Agus
>
>
>
>
>
============================================================
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
---------------------------------------------------------------------
Pojok Lowongan Kerja yang disediakan milis :
http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html
*** Posting lowongan kerja : ke link tersebut dan klik New Topic
============================================================
bergabung ke milis (subscribe), kirim mail kosong ke: belajar-excel-subscribe@yahoogroups.com
posting ke milis, kirimkan ke: belajar-excel@yahoogroups.com
berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages
melihat file archive / mendownload lampiran : http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/
menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com
keluar dari membership milis (UnSubscribe), kirim mail kosong ke : belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------
Tidak ada komentar:
Posting Komentar