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.