Ekstrak data dari power pivot ke excel

Jika Anda ingin mendapatkan data dari model data Power Pivot, Anda tidak memiliki terlalu banyak opsi untuk melakukannya. Cara paling langsung adalah dengan menggunakan Tabel Pivot, tetapi sering kali tidak sesuai dengan permintaan Anda. Dalam situasi ini Anda dapat menggunakan fungsi CUBEVALUE

Dalam contoh ini kita akan menggunakan sampel data penjualan ini.  

Kami ingin mendapatkan total pendapatan untuk hari Senin (dan kami sudah memiliki ukuran yang dihitung Total pendapatan)

Mari kita ke sheet (dalam file yang sama dengan model data) dan tulis fungsi ini

• =CUBEVALUE("ThisWorkbookDataModel";"[Total pendapatan]";"[Hari Kerja]. [Jumat]]")

Penjelasan

• Cubevalue adalah nama fungsi
• "ThisWorkbookDataModel" adalah nama koneksi. Jika Anda tidak yakin, periksa di Data / Connections.  
• "[Total pendapatan]" adalah nama ukuran, yang telah digunakan dalam Model Data (tidak berfungsi untuk kolom ringkasan umum - Anda harus membuat ukuran eksplisit klasik).  
• "[Hari kerja]. [Jumat]]" karena kami tidak menginginkan semua pendapatan - kami hanya menginginkan hari Jumat.  

Argumen dari fungsi ini dapat diambil dari sel - perlu diingat bahwa Anda harus menghubungkan semuanya bersama-sama dengan CONCATENATE atau ampersand.  

Jika Anda menginginkan data untuk Power Pivot, lakukan dengan cara kedua, bahkan tanpa Excel mengetahuinya. Ini karena Anda hanya akan memuat data sekali, dalam format yang sangat terkompresi. Untuk memahami besarnya perbedaan, misalkan Anda memuat data ke Excel dengan menambahkannya terlebih dahulu ke Model Data, ukuran file katakanlah 10 MB

Jika Anda memuat data ke PowerPivot, dan karenanya ke Model Data melewatkan langkah ekstra Excel, ukuran file Anda bisa kurang dari 1 MB saja

Sumber Data Didukung oleh Power Pivot

Anda dapat mengimpor data ke Model Data Power Pivot dari berbagai sumber data atau membuat koneksi dan/atau menggunakan koneksi yang sudah ada. Power Pivot mendukung sumber data berikut −

• Database relasional SQL Server

• basis data Microsoft Access

• Layanan Analisis SQL Server

• Layanan Pelaporan SQL Server (SQL 2008 R2)

• Umpan data ATOM

• File teks

• Microsoft SQLAzure

• Peramal

• Teradata

• Sybase

• Informix

• IBM DB2

• Penautan Objek dan Penyematan Basis Data/Buka Konektivitas Basis Data

• (OLEDB/ODBC) sumber

• Berkas Microsoft Excel

• Berkas Teks

Memuat Data Langsung ke PowerPivot

Untuk memuat data langsung ke Power Pivot, lakukan hal berikut −

• Buka buku kerja baru

• Klik pada tab POWERPIVOT pada pita

• Klik Kelola di grup Model Data

Jendela PowerPivot terbuka. Sekarang Anda memiliki dua jendela − jendela buku kerja Excel dan jendela PowerPivot untuk Excel yang tersambung ke buku kerja Anda

• Klik tab Beranda di jendela PowerPivot

• Klik Dari Database di grup Dapatkan Data Eksternal

• Pilih Dari Akses

Panduan Impor Tabel muncul

• Telusuri ke file database Access

• Berikan nama koneksi Ramah

• Jika database dilindungi kata sandi, isi juga detailnya

Klik tombol Berikutnya →. Panduan Impor Tabel menampilkan opsi untuk memilih cara mengimpor data

Klik Pilih dari daftar tabel dan tampilan untuk memilih data yang akan diimpor

Klik tombol Berikutnya →. Panduan Impor Tabel menampilkan tabel dan tampilan di database Access yang telah Anda pilih

Centang kotak Medali

Seperti yang dapat Anda amati, Anda dapat memilih tabel dengan mencentang kotak, mempratinjau dan memfilter tabel sebelum menambahkan ke Tabel Pivot dan/atau memilih tabel terkait

Klik tombol Pratinjau & Filter

Seperti yang Anda lihat, Anda dapat memilih kolom tertentu dengan mencentang kotak di label kolom, memfilter kolom dengan mengklik panah dropdown di label kolom untuk memilih nilai yang akan disertakan

• Klik Oke

• Klik tombol Pilih Tabel Terkait

• Power Pivot memeriksa tabel lain apa yang terkait dengan tabel Medali yang dipilih, jika ada relasi

Anda dapat melihat bahwa Power Pivot menemukan bahwa tabel Disiplin berkaitan dengan tabel Medali dan memilihnya. Klik Selesai

Table Import Wizard menampilkan − Mengimpor dan menampilkan status impor. Ini akan memakan waktu beberapa menit dan Anda dapat menghentikan impor dengan mengklik tombol Hentikan Impor

Setelah data diimpor, Wizard Impor Tabel menampilkan – Sukses dan menunjukkan hasil impor seperti yang ditunjukkan pada tangkapan layar di bawah ini. Klik Tutup

Power Pivot menampilkan dua tabel yang diimpor dalam dua tab

Anda dapat menelusuri catatan (baris tabel) menggunakan panah Rekam di bawah tab

Panduan Impor Tabel

Di bagian sebelumnya, Anda telah mempelajari cara mengimpor data dari Access melalui Panduan Impor Tabel

Perhatikan bahwa opsi Panduan Impor Tabel berubah sesuai sumber data yang dipilih untuk dihubungkan. Anda mungkin ingin mengetahui sumber data apa yang dapat Anda pilih

Klik Dari Sumber Lain di jendela Power Pivot

Panduan Impor Tabel – Sambungkan ke Sumber Data muncul. Anda bisa membuat koneksi ke sumber data atau Anda bisa menggunakan yang sudah ada

Anda dapat menelusuri daftar koneksi di Panduan Impor Tabel untuk mengetahui koneksi data yang kompatibel ke Power Pivot

• Gulir ke bawah ke File Teks

• Pilih File Excel

• Klik tombol Berikutnya →. Panduan Impor Tabel menampilkan – Sambungkan ke File Microsoft Excel

• Jelajahi file Excel di kotak Excel File Path

• Centang kotak – Gunakan baris pertama sebagai tajuk kolom

• Klik tombol Berikutnya →. Tampilan Wizard Impor Tabel − Pilih Tabel dan Tampilan

• Centang kotak Katalog Produk$. Klik tombol Selesai

Anda akan melihat pesan Sukses berikut. Klik Tutup

Anda telah mengimpor satu tabel, dan Anda juga telah membuat koneksi ke file Excel yang berisi beberapa tabel lainnya

Membuka Koneksi yang Ada

Setelah Anda membuat koneksi ke sumber data, Anda bisa membukanya nanti

Klik Koneksi yang Ada di jendela PowerPivot

Kotak dialog Existing Connections akan muncul. Pilih Data Penjualan Excel dari daftar

Klik tombol Buka. Wizard Impor Tabel muncul menampilkan tabel dan tampilan

Pilih tabel yang ingin Anda impor dan klik Selesai

Lima tabel yang dipilih akan diimpor. Klik Tutup

Anda dapat melihat bahwa lima tabel ditambahkan ke Power Pivot, masing-masing di tab baru

Membuat Tabel Tertaut

Tabel tertaut adalah tautan langsung antara tabel di Excel dan tabel di Model Data. Pembaruan tabel di Excel secara otomatis memperbarui data di tabel data dalam model

Anda dapat menautkan tabel Excel ke Power Pivot dalam beberapa langkah sebagai berikut −

• Buat tabel Excel dengan data

• Klik tab POWERPIVOT pada Ribbon

• Klik Tambahkan ke Model Data di grup Tabel

Tabel Excel ditautkan ke Tabel Data terkait di PowerPivot

Anda dapat melihat bahwa Alat Tabel dengan tab - Tabel Tertaut ditambahkan ke jendela Power Pivot. Jika Anda mengklik Buka Tabel Excel, Anda akan beralih ke lembar kerja Excel. Jika Anda mengklik Kelola, Anda akan beralih kembali ke tabel tertaut di jendela Power Pivot

Anda dapat memperbarui tabel tertaut secara otomatis atau manual

Perhatikan bahwa Anda bisa menautkan tabel Excel hanya jika ada di buku kerja dengan Power Pivot. Jika Anda memiliki tabel Excel di buku kerja terpisah, Anda harus memuatnya seperti yang dijelaskan di bagian selanjutnya

Memuat dari File Excel

Jika Anda ingin memuat data dari buku kerja Excel, ingatlah hal berikut −

• Power Pivot menganggap buku kerja Excel lainnya sebagai database dan hanya lembar kerja yang diimpor

• Power Pivot memuat setiap lembar kerja sebagai tabel

• Power Pivot tidak dapat mengenali tabel tunggal. Oleh karena itu, Power Pivot tidak dapat mengenali jika ada beberapa tabel di lembar kerja

• Power Pivot tidak dapat mengenali informasi tambahan apa pun selain tabel di lembar kerja

Oleh karena itu, simpan setiap tabel dalam lembar kerja yang terpisah

Setelah data Anda di buku kerja siap, Anda dapat mengimpor data sebagai berikut –

• Klik Dari Sumber Lain di grup Dapatkan Data Eksternal di jendela Power Pivot

• Lanjutkan seperti yang diberikan di bagian − Tabel Impor Wizard

Berikut ini adalah perbedaan antara tabel Excel yang ditautkan dan tabel Excel yang diimpor −

• Tabel tertaut harus berada dalam buku kerja Excel yang sama dengan tempat database Power Pivot disimpan. Jika data sudah ada di buku kerja Excel lainnya, tidak ada gunanya menggunakan fitur ini

• Fitur impor Excel memungkinkan Anda memuat data dari buku kerja Excel yang berbeda

• Memuat data dari buku kerja Excel tidak membuat link antara dua file. Power Pivot hanya membuat salinan data, saat mengimpor

• Saat file Excel asli diperbarui, data di Power Pivot tidak akan di-refresh. Anda perlu menyetel mode pembaruan ke otomatis atau memperbarui data secara manual, di tab Tabel Tertaut di jendela Power Pivot

Memuat dari File Teks

Salah satu gaya representasi data yang populer adalah dengan format yang dikenal sebagai nilai dipisahkan koma (csv). Setiap baris data/record diwakili oleh baris teks, dimana kolom/field dipisahkan dengan koma. Banyak database menyediakan opsi untuk menyimpan ke file berformat csv

Jika Anda ingin memuat file csv ke Power Pivot, Anda harus menggunakan opsi File Teks. Misalkan Anda memiliki file teks berikut dengan format csv −

• Klik tab PowerPivot

• Klik tab Beranda di jendela PowerPivot

• Klik Dari Sumber Lain di grup Dapatkan Data Eksternal. Panduan Impor Tabel muncul

• Gulir ke bawah ke File Teks

• Klik File Teks

• Klik tombol Berikutnya →. Table Import Wizard muncul dengan tampilan − Connect to Flat File

• Telusuri ke file teks di kotak File Path. File csv biasanya memiliki baris pertama yang mewakili tajuk kolom

• Centang kotak Gunakan baris pertama sebagai tajuk kolom, jika baris pertama memiliki tajuk

• Dalam kotak Pemisah Kolom, defaultnya adalah Koma (,), tetapi jika file teks Anda memiliki operator lain seperti Tab, Titik Koma, Spasi, Titik Dua atau Bilah Vertikal, maka pilih operator tersebut

Seperti yang dapat Anda amati, ada pratinjau tabel data Anda. Klik Selesai

Power Pivot membuat tabel data dalam Model Data

Memuat dari Clipboard

Misalnya, Anda memiliki data dalam aplikasi yang tidak dikenali oleh Power Pivot sebagai sumber data. Untuk memuat data ini ke Power Pivot, Anda memiliki dua opsi −

• Salin data ke file Excel dan gunakan file Excel sebagai sumber data untuk Power Pivot

• Salin data, agar ada di papan klip, dan tempelkan ke Power Pivot

Anda telah mempelajari opsi pertama di bagian sebelumnya. Dan ini lebih disukai daripada opsi kedua, seperti yang akan Anda temukan di akhir bagian ini. Namun, Anda harus mengetahui cara menyalin data dari clipboard ke Power Pivot

Misalkan Anda memiliki data dalam dokumen kata sebagai berikut –

Word bukan sumber data untuk Power Pivot. Oleh karena itu, lakukan hal berikut –

• Pilih tabel di dokumen Word

• Salin dan Tempel di jendela PowerPivot

Kotak dialog Tempel Pratinjau muncul

• Beri nama sebagai tabel Word-Employee

• Centang kotak Gunakan baris pertama sebagai tajuk kolom dan klik OK

Data yang disalin ke clipboard akan ditempelkan ke tabel data baru di Power Pivot, dengan tab − tabel Word-Employee

Misalnya, Anda ingin mengganti tabel ini dengan konten baru

• Salin tabel dari Word

• Klik Tempel Ganti

Kotak dialog Tempel Pratinjau muncul. Verifikasi konten yang Anda gunakan untuk mengganti

Klik Oke

Seperti yang dapat Anda amati, konten tabel data di Power Pivot diganti dengan konten di clipboard

Misalkan Anda ingin menambahkan dua baris data baru ke tabel data. Dalam tabel di dokumen Word, Anda memiliki dua baris berita

• Pilih dua baris baru

• Klik Salin

• Klik Tempel Tambahkan di jendela Power Pivot. Kotak dialog Tempel Pratinjau muncul

• Verifikasi konten yang Anda gunakan untuk ditambahkan

Klik OK untuk melanjutkan

Seperti yang dapat Anda amati, konten tabel data di Power Pivot ditambahkan dengan konten di clipboard

Di awal bagian ini, kami telah mengatakan bahwa menyalin data ke file excel dan menggunakan tabel tertaut lebih baik daripada menyalin dari clipboard

Ini karena alasan berikut -

• Jika Anda menggunakan tabel tertaut, Anda tahu sumber datanya. Di sisi lain, Anda tidak akan mengetahui sumber datanya nanti atau jika digunakan oleh orang yang berbeda

• Anda memiliki informasi pelacakan di file Word, seperti saat data diganti dan saat data ditambahkan. Namun, tidak ada cara untuk menyalin informasi tersebut ke Power Pivot. Jika Anda menyalin data terlebih dahulu ke file excel, Anda dapat menyimpan informasi tersebut untuk digunakan nanti

• Saat menyalin dari clipboard, jika Anda ingin menambahkan beberapa komentar, Anda tidak dapat melakukannya. Jika Anda menyalin ke file Excel terlebih dahulu, Anda bisa menyisipkan komentar di tabel Excel Anda yang akan ditautkan ke Power Pivot

• Tidak ada cara untuk me-refresh data yang disalin dari clipboard. Jika data berasal dari tabel tertaut, Anda selalu dapat memastikan bahwa data tersebut diperbarui

  Bagaimana cara mengubah tabel pivot menjadi data mentah?

  Untuk mengonversi tabel pivot ini menjadi tabel data biasa, cukup pilih seluruh tabel pivot (dalam hal ini, kami memilih rentang E1. I6) dan tekan Ctrl+C untuk menyalin data. Kemudian klik kanan sel tempat Anda ingin menempelkan data (kami akan memilih sel E8) dan klik opsi berjudul Tempel Nilai. Apa ini?

  Bisakah saya mengubah tabel pivot yang ada menjadi model data?

  Tambahkan data yang sudah ada dan tidak terkait ke Model Data . Gunakan salah satu pendekatan ini untuk menambahkan data Anda. Klik Power Pivot > Tambahkan ke Model Data . Klik Sisipkan > PivotTable, lalu centang Tambahkan data ini ke Model Data dalam kotak dialog Buat PivotTable.