Dengan waktu kurang dari
satu jam, Anda bisa menyusun laporan PivotTable di Excel yang menggabungkan
data dari beberapa tabel. Bagian pertama langkah tutorial ini menjelaskan
tentang impor dan eksplorasi data. Di pertengahan, Anda akan menggunakan add-in
Power Pivot untuk memperbaiki model data yang ada di dalam laporan, mempelajari
cara menambahkan perhitungan, hierarki, dan optimalisasi untuk pelaporan Power
View.
Mari mulai dengan mengimpor beberapa data.
1.
Unduh contoh data
(ContosoV2) untuk tutorial ini. Lihat Mendapatkan contoh data untuk DAX dan Model Data untuk penjelasan
selengkapnya. Ekstrak dan simpan file data di lokasi yang mudah diakses,
seperti Unduhan atau Dokumen Saya.
2.
Di Excel, buka buku kerja kosong.
3.
Klik Data > Dapatkan Data
Eksternal > Dari Access.
4.
Masuk ke folder yang berisi file contoh data dan pilih
ContosoSales.
5.
Klik Buka.
Karena Anda tersambung ke file database yang berisi beberapa tabel, dialog Pilih Tabel akan muncul sehingga
Anda bisa memilih tabel mana yang akan diimpor.
6.
Di Pilih Tabel, centang Aktifkan pilihan
beberapa tabel.
7.
Pilih semua tabel dan klik OK.
8.
Di Impor Data, klik Laporan
PivotTable dan
klik OK.
CATATAN Anda mungkin belum
menyadarinya, tapi Anda baru saja membuat model data. Model merupakan lapisan
integrasi data yang dibuat secara otomatis ketika Anda mengimpor atau bekerja
dengan beberapa tabel secara bersamaan dalam laporan PivotTable yang sama.
Model sebagian besar transparan dalam Excel,
tapi Anda bisa menampilkan dan mengubahnya langsung menggunakan add-in Power
Pivot . Dalam Excel, kehadiran model data nampak jelas ketika Anda melihat
koleksi tabel dalam daftar Bidang PivotTable. Ada beberapa cara untuk membuat
model. Lihat Membuat Model Data di
Excel untuk
selengkapnya.
Menjelajahi data menggunakan PivotTable
Menjelajahi data itu mudah ketika Anda menyeret
bidang ke area Nilai, Kolom, dan Baris pada daftar bidang
PivotTable.
1.
Di daftar bidang, gulir ke bawah hingga Anda menemukan tabel
FactSales.
2.
Klik SalesAmount. Karena merupakan data numerik, Excel
secara otomatis menempatkannya SalesAmount di area Nilai.
3.
Di DimDate, seret CalendarYear ke kolom.
4.
Di DimProductSubcategory, seret ProductSubcategoryName
ke Baris.
5.
Di DimProduct, seret BrandName ke Baris, tempatkan di
bawah subkategori.
PivotTable Anda akan terlihat sama dengan layar
berikut ini.
Dengan usaha minimal, Anda sekarang memiliki
dasar PivotTable yang mencakup bidang dari empat tabel yang berbeda. Apa yang
membuat tugas ini begitu sederhana adalah hubungan yang telah ada sebelumnya di
antara tabel. Karena hubungan tabel sudah ada dalam sumber, dan karena Anda
telah mengimpor semua tabel dalam operasi tunggal, Excel bisa membuat ulang
hubungan di dalam model.
Tapi bagaimana jika data asli berasal dari
sumber yang berbeda, atau apakah nantinya akan diimpor? Biasanya, Anda bisa
menggabungkan data baru dengan membuat hubungan berdasarkan kolom yang cocok.
Dalam langkah berikutnya, Anda akan mengimpor tabel tambahan dan mempelajari
persyaratan dan langkah-langkah untuk membuat hubungan baru.
Menambahkan beberapa tabel
Untuk mempelajari cara mempersiapkan hubungan
tabel Anda harus memiliki beberapa tabel tambahan yang tidak tersambung untuk
digunakan. Pada langkah ini, Anda akan mendapatkan data yang tersisa yang
digunakan dalam tutorial ini dengan mengimpor satu file database tambahan dan
menempelkan data dari dua buku kerja lainnya.
Menambahkan
kategori produk
1.
Di buku kerja, bukalah lembar baru. Anda akan
menggunakannya untuk menyimpan data tambahan.
2.
Klik Data > Dapatkan Data
Eksternal > Dari Access.
3.
Buka folder yang berisi file contoh data dan pilih
ProductCategories. Klik Buka.
4.
Di Impor Data, pilih Tabel,
dan klik OK.
Menambahkan
data geografi
1.
Menyisipkan lembar lainnya.
2.
Dari file contoh data, buka Geography.xlsx, tempatkan
kursor pada A1, lalu tekan Ctrl-Shift-End untuk memilih semua data.
3.
Salin data ke clipboard.
4.
Tempelkan data ke dalam lembar kosong yang baru Anda
tambahkan.
5.
Klik Format
sebagai Tabel, dengan memilih gaya apa pun. Memformat data sebagai tabel
memungkinkan Anda menamainya, yang akan berguna ketika Anda menetapkan hubungan
dalam langkah berikutnya.
6.
Di Format Sebagai Tabel, pastikan bahwa Tabel Saya
memiliki header telah
dipilih. Klik OK.
7.
Namai tabel Geography. Dalam Alat Tabel > Desain,
ketikkan Geography dalam Nama Tabel.
8.
Tutup Geography.xlsx untuk mengosongkannya dari ruang
kerja Anda.
Menambahkan
data tersimpan
·
Ulangi langkah sebelumnya untuk file
Stores.xlsx, tempelkan kontennya ke dalam lembar kosong. Namai tabel Stores.
Anda sekarang seharusnya memiliki empat lembar.
Sheet1 berisi PivotTable, Sheet2 berisi ProductCategories,
Sheet3 berisi Geography, dan Sheet4 berisi Stores. Karena Anda telah menamai
setiap tabel, langkah berikutnya, membuat hubungan, akan lebih mudah.
Menggunakan bidang dari tabel yang baru diimpor
Anda bisa memulai secepatnya menggunakan bidang
dari tabel yang baru Anda impor. Jika Excel tidak bisa menentukan cara
menggabungkan bidang ke dalam laporan PivotTable, Anda akan diminta untuk
membuat hubungan tabel yang mengaitkan tabel baru dengan tabel telah menjadi
bagian dari model.
1.
Di sebelah atas Bidang PivotTable, klik Semua untuk menampilkan
daftar lengkap dari tabel yang tersedia.
2.
Gulir ke bagian bawah daftar. Di sanalah Anda akan menemukan
tabel baru yang baru saja Anda tambahkan.
3.
Perluas Stores.
4.
Seret StoreName ke area Filter.
5.
Perhatikan bahwa Excel meminta Anda untuk membuat
hubungan. Pemberitahuan ini muncul karena Anda menggunakan bidang dari tabel
yang tidak terkait ke model.
6.
Klik Buat untuk membuka dialog
Buat Hubungan.
7.
Di Tabel, pilih FactSales. Dalam contoh data yang Anda
gunakan, FactSales berisi detail informasi penjualan dan biaya mengenai bisnis
Contoso, serta tombol ke tabel lain, termasuk kode toko yang juga disajikan dalam
file Stores.xlsx yang Anda impor dalam langkah sebelumnya.
8.
Dalam Kolom (Asing), pilih StoreKey.
9.
Dalam Tabel Terkait, pilih Stores.
10. Dalam
Kolom Terkait (Utama), pilih StoreKey.
11. Klik OK.
Di balik layar, Excel menyusun Model Data yang
bisa digunakan di seluruh buku kerja dalam jumlah laporan PivotTable,
PivotChart, atau Power View. Hal yang penting untuk model ini adalah bahwa
hubungan tabel menentukan navigasi dan perhitungan jalur yang digunakan dalam
laporan PivotTable. Dalam tugas berikutnya, Anda akan membuat hubungan secara
manual untuk menyambungkan data yang baru saja Anda impor.
Menambahkan hubungan
Anda bisa membuat hubungan tabel secara
sistematis untuk semua tabel baru yang telah Anda impor. Jika Anda berbagi buku
kerja dengan kolega Anda, memiliki hubungan yang sudah ditentukan sebelumnya
akan lebih baik jika mereka tidak mengetahui datanya sebaik Anda.
Ketika membuat hubungan secara manual, Anda akan
bekerja dengan dua tabel sekaligus. Untuk setiap tabel, Anda akan memilih kolom
yang memberi tahu Excel cara mencari baris terkait di dalam tabel lainnya.
Menghubungkan
ProductSubcategory ke ProductCategory
1.
Di Excel, klik Data > Hubungan > Baru.
2.
Di Tabel, pilih DimProductSubcategory.
3.
Di Kolom (Asing), pilih ProductCategoryKey.
4.
Di Tabel Terkait, pilih Table_ProductCategory.accdb.
5.
Di Kolom Terkait (Utama), pilih ProductCategoryKey.
6.
Klik OK.
7.
Tutup dialog Kelola
Hubungan.
Menambahkan
kategori ke PivotTable
Walaupun Model Data telah diperbarui untuk
menyertakan tabel tambahan dan hubungan, PivotTable belum menggunakannya. Dalam
tugas ini, Anda akan menambahkan ProductCategory ke daftar Bidang PivotTable.
1.
Di Bidang PivotTable, klik Semua untuk memperlihatkan
tabel yang sudah ada dalam Model Data.
2.
Gulir ke bagian bawah daftar.
3.
Di area Baris, hapus BrandName.
4.
Perluas Table_DimProductCategories.accdb.
5.
Seret ProductCategoryName ke area Baris, tempatkan di
atas ProductSubcategory.
6.
Dalam Bidang PivotTable, klik Aktif untuk memverifikasi
bahwa tabel yang baru saja Anda gunakan sekarang bisa digunakan secara aktif
dalam PivotTable.
Checkpoint: Tinjau apa yang telah Anda pelajari
Anda sekarang memiliki PivotTable yang mencakup
data dari beberapa tabel, beberapa di antaranya Anda impor dalam langkah
selanjutnya. Untuk menggabungkan data ini sekaligus, Anda harus membuat
hubungan tabel yang akan digunakan Excel untuk mengkorelasikan baris. Anda
telah mengetahui bahwa memiliki kolom yang menyediakan data yang cocok sangat
penting untuk mencari baris terkait. Dalam file contoh data, semua tabel
meliputi kolom yang bisa digunakan untuk tujuan ini.
Walaupun PivotTable fungsional, Anda mungkin
telah menyadari ada beberapa hal yang bisa ditingkatkan. Daftar Bidang
PivotTable tampaknya memiliki tabel tambahan (DimEntity) dan kolom (ETLLoadID)
yang tidak terkait dengan bisnis Contoso. Dan kami masih belum mengintegrasikan
data Geography.
Berikutnya: Menampilkan dan memperluas model Anda dengan Power Pivot
Dalam seri tugas berikutnya, Anda akan
menggunakan add-in PowerPivot di Microsoft Excel 2013 untuk memperluas model.
Anda akan menemukan bahwa Anda bisa membuat hubungan lebih mudah dengan
menggunakan add-in Tampilan Diagram yang sediakan. Anda juga bisa menggunakan
add-in untuk membuat perhitungan dan hierarki, menyembunyikan item yang tidak
seharusnya muncul dalam daftar bidang, dan mengoptimalkan data untuk laporan
tambahan.
CATATAN Add-in PowerPivot di
Microsoft Excel 2013 tersedia di Office Professional Plus. LihatPowerPivot
di Microsoft Excel 2013 Add-in untuk
informasi selengkapnya.
Tambahkan Power
Pivot ke
pita Excel dengan mengaktifkan add-in Power Pivot.
1.
Buka File > Opsi > Add-In.
2.
Dalam kotak Kelola,
klik COM
Add-in> Ke.
Di pita sekarang ada tab Power Pivot.
Menambahkan hubungan menggunakan Tampilan Diagram di Power Pivot
1.
Di Excel, klik Sheet3 untuk menjadikannya sebagai
lembar aktif. Sheet3 berisi tabel Geography yang telah Anda impor sebelumnya.
2.
Pada pita, klik Power
Pivot > Tambahkan ke Model
Data. Langkah ini menambahkan tabel Geography ke model. Ini juga membuka
add-in Power Pivot, yang Anda akan gunakan untuk melakukan langkah selanjutnya
dalam tugas ini.
3.
Perhatikan bahwa jendela Power Pivot memperlihatkan
semua tabel dalam model, termasuk Geography . Klik melalui beberapa tabel. Di
add-in, Anda bisa menampilkan semua data yang disertakan model Anda.
4.
Di jendela Power Pivot, di bagian Tampilan, klik Tampilan Diagram
5.
Gunakan bilah slide untuk mengubah ukuran diagram
sehingga Anda bisa melihat semua objek dalam diagram. Perhatikan ada dua tabel
yang tidak terkait dengan diagram lainnya: DimEntity dan Geography.
6.
Klik kanan DimEntity dan klik Hapus. Tabel
ini merupakan artefak dari database asli dan tidak diperlukan dalam model ini.
7.
Perbesar Geography sehingga Anda bisa menampilkan semua
bidangnya. Anda bisa menggunakan slider untuk membuat diagram tabel lebih
besar.
8.
Perhatikan bahwa Geography memiliki GeographyKey. Kolom
ini berisi nilai unik yang mengidentifikasi setiap baris dalam tabel Geography.
Mari cari tahu apakah tabel lain dalam model tersebut juga gunakan tombol ini.
Jika ya, kita bisa membuat hubungan yang menyambungkan Geography ke model
lainnya.
9.
Klik Temukan.
10. Dalam
Temukan Metadata, ketik GeographyKey.
11. Klik Temukan Berikutnya beberapa kali. Anda
akan melihat GeographyKey muncul di tabel Geography dan di tabel Stores.
12. Posisikan
ulang tabel Geography sehingga tabel ini berada di samping Stores.
13. Seret
kolom GeographyKey di Stores ke kolom GeographyKey di Geography. Power Pivot
menggambar garis di antara dua kolom, yang menunjukkan hubungan.
Dalam tugas ini, Anda akan mempelajari teknik
baru untuk menambahkan tabel dan membuat hubungan. Anda sekarang memiliki model
yang benar-benar terintegrasi, dengan semua tabel tersambung dan tersedia untuk
PivotTable di Sheet1.
TIPS
Dalam Tampilan Diagram, beberapa diagram tabel diperluas sepenuhnya,
memperlihatkan kolom seperti ETLLoadID, LoadDate, dan UpdateDate. Bidang
tertentu ini merupakan artefak dari gudang data asli Contoso, ditambahkan untuk
mendukung ekstraksi data dan operasi pemuatan. Anda tidak memerlukannya dalam
model Anda. Untuk membuangnya, soroti dan klik kanan bidang, dan klik Hapus .
Membuat kolom terhitung
Dalam Power Pivot, Anda bisa menggunakan Data
Analysis Expressions (DAX) untuk menambahkan perhitungan. Dalam tugas ini, Anda
akan menghitung total laba, dan menambahkan kolom terhitung yang menjadi
referensi nilai data dari tabel lain. Nanti, Anda akan melihat bagaimana cara
menggunakan kolom yang direferensikan untuk menyederhanakan model Anda.
1.
Dalam jendela Power Pivot, kembali ke Tampilan Data.
2.
Ganti nama tabel Table_ProductCategories accdb ke nama
yang lebih mudah dikenal. Anda akan merujuk tabel ini dalam langkah berikutnya
dan nama yang lebih singkat akan membuat perhitungan lebih mudah dibaca. Klik
kanan nama tabel, klik Ganti
Nama, ketikkan ProductCategories, lalu tekan Enter.
3.
Pilih tabel FactSales.
4.
Klik Desain > Kolom > Tambahkan.
5.
Dalam bilah rumus di sebelah atas tabel, ketikkan rumus
berikut ini. LengkapiOtomatis membantu Anda mengetikkan nama kolom dan tabel
yang benar-benar memenuhi syarat, dan daftar fungsi yang tersedia. Anda juga
bisa mengklik kolom dan Power Pivot akan menambahkan nama kolom ke rumus.
= [SalesAmount] - [TotalCost] - [ReturnAmount]
6.
Ketika Anda telah selesai menyusun rumus, tekan Enter
untuk menerima rumus tersebut.
Nilai digabungkan untuk semua baris dalam kolom
terhitung. Jika Anda menggulir ke bawah menelusuri tabel, Anda akan melihat
baris bisa memiliki nilai yang berbeda untuk kolom ini, berdasarkan data yang
ada di dalam setiap baris.
7.
Ganti nama kolom dengan mengklik kanan
CalculatedColumn1 dan pilih Ganti
Nama Kolom. Ketik Laba lalu tekan Enter.
8.
Sekarang pilih tabel DimProduct.
9.
Klik Desain > Kolom > Tambahkan.
10. Dalam
bilah rumus di sebelah atas tabel, ketikkan rumus berikut ini.
=
RELATED(ProductCategories[ProductCategoryName])
Fungsi RELATED mengembalikan nilai dari tabel
terkait. Dalam kasus ini, tabel ProductCategories menyertakan nama kategori
produk, yang akan berguna untuk dimiliki dalam tabel DimProduct ketika Anda
menyusun hierarki yang menyertakan informasi kategori. Untuk informasi
selengkapnya tentang fungsi ini, lihat Fungsi RELATED (DAX).
11. Ketika
Anda telah selesai menyusun rumus, tekan Enter untuk menerima rumus tersebut.
Nilai digabungkan untuk semua baris dalam kolom
terhitung. Jika Anda menggulir ke bawah menelusuri tabel, Anda akan melihat
setiap baris sekarang memiliki Nama Kategori Produk.
12. Ganti
nama kolom dengan mengklik kanan CalculatedColumn1 dan pilih Ganti Nama Kolom.
Ketik ProductCategory, lalu tekan Enter.
13. Klik Desain > Kolom > Tambahkan.
14. Di
bilah rumus di atas tabel, ketikkan rumus berikut ini, lalu tekan Enter untuk
menerima rumus.
=
RELATED(DimProductSubcategory[ProductSubcategoryName])
15. Ganti
nama kolom dengan mengklik kanan CalculatedColumn1 dan pilih Ganti Nama Kolom.
Ketik ProductSubcategory, lalu tekan Enter.
Membuat hierarki
Sebagian besar model menyertakan data yang
mewarisi hierarki. Contoh umum yang menyertakan data kalender, data geografi,
dan kategori produk. Membuat hierarki sangat berguna karena Anda bisa menyeret
satu item (hierarki) ke laporan dan bukannya harus memasang dan memesan bidang
yang sama berulang kali.
1.
Dalam Power Pivot, alihkan ke Tampilan Diagram. Perluas
tabel DimDate sehingga Anda bisa lebih mudah melihat semua bidang ini.
2.
Tekan dan tahan Ctrl dan klik kolom CalendarYear,
CalendarQuarter dan CalendarMonth (Anda harus menggulir ke bagian bawah tabel).
3.
Dengan tiga kolom yang telah dipilih, klik kanan salah
satunya dan klik Buat
Hierarki. Node hierarki induk, Hierarki 1, dibuat di bagian bawah tabel,
dan kolom yang dipilih akan disalin di bagian bawah hierarki sebagai node
turunan.
4.
Ketik Tanggal sebagai nama untuk hierarki baru Anda.
5.
Tambahkan kolom FullDateLabel ke hierarki. Klik kanan
FullDateLabel dan pilih Tambahkan
Hierarki. Pilih Tanggal. FullDateLabel berisi tanggal lengkap, termasuk
tahun, bulan dan hari. Pastikan bahwa FullDateLabel muncul terakhir dalam
hierarki. Anda sekarang memiliki hierarki multitingkat yang menyertakan tahun,
kuartal, dan hari kalender individual.
6.
Masih di Tampilan Diagram, arahkan ke tabel DimProduct,
lalu klik tombol Buat
Hierarki pada
header tabel. Node hierarki induk kosong muncul di bagian bawah tabel.
7.
Ketik Kategori Produk sebagai nama hierarki baru Anda.
8.
Untuk membuat node hierarki turunan, seret
ProductCategory dan ProductSubcategory ke hierarki.
9.
Klik kanan ProductName dan pilih Tambahkan ke
Hierarki. Pilih Kategori Produk.
Sekarang Anda mengetahui beberapa cara berbeda
untuk membuat hierarki, mari kita gunakan cara ini dalam PivotTable.
1.
Kembali ke Excel.
2.
Di Sheet1 (lembar yang berisi PivotTable), hapus bidang
dalam area Baris.
3.
Ganti bidang dengan hierarki baru Kategori Produk dalam
DimProduct.
4.
Dengan cara yang sama, ganti CalendarYear dalam area
Kolom dengan Hierarki Tanggal dalam DimDate.
Ketika Anda menjelajahi data saat ini, akan
mudah untuk melihat manfaat menggunakan hierarki. Anda bisa memperluas dan
menutup secara independen area PivotTable yang berbeda, menyediakan lebih
banyak kontrol atas cara penggunaan ruang yang tersedia. Selanjutnya, dengan
menambahkan hierarki tunggal ke Baris dan Kolom, Anda bisa mendapatkan
penelusuran yang kaya dan cepat, tanpa harus menyusun beberapa bidang untuk
mendapatkan efek yang serupa.
Menyembunyikan kolom
Sekarang Anda telah membuat hierarki Kategori
Produk dan menempatkannya dalam DimProduct, Anda tidak lagi memerlukan
DimProductCategory atau DimProductSubcategory dalam daftar Bidang PivotTable.
Dalam tugas ini, Anda akan mempelajari cara sembunyikan tabel dan kolom yang
tidak relevan yang mengambil ruang dalam daftar Bidang PivotTable. Dengan
menyembunyikan tabel dan kolom, Anda meningkatkan pengalaman pelaporan tanpa
mempengaruhi model yang menyediakan hubungan dan perhitungan data.
Anda bisa menyembunyikan kolom individual,
rentang kolom, atau seluruh tabel. Nama tabel dan kolom berwarna abu-abu untuk
mencerminkan bahwa tabel tersebut disembunyikan pada pelaporan klien yang
menggunakan model tersebut. Kolom yang disembunyikan berwarna abu-abu dalam
model untuk menunjukkan statusnya, tetapi tetap terlihat dalam Tampilan Data
sehingga Anda bisa terus bekerja dengan data tersebut.
1.
Di Power Pivot, pastikan Tampilan Data telah dipilih.
2.
Dalam tab di bagian bawah, klik kanan
DimProductSubcategory dan pilih Sembunyikan
dari Alat Klien.
3.
Ulangi untuk ProductCategories.
4.
Buka DimProduct.
5.
Klik kanan kolom berikut ini dan klik Sembunyikan dari
Alat Klien:
o ProductKey
o ProductLabel
o ProductSubcategory
6.
Pilih beberapa kolom yang berdekatan. Mulai dengan
ClassID dan lanjutkan ke ProductSubcategory di bagian akhir. Klik kanan untuk
menyembunyikannya.
7.
Ulangi untuk tabel lain, dengan menghapus ID, tombol,
atau detail lain yang tidak akan Anda gunakan dalam laporan.
Kembali ke Excel ke Sheet 1 dengan daftar Bidang
PivotTable untuk melihat perbedaannya. Jumlah tabel dikurangi dan DimProduct
hanya menyertakan item yang kemungkinan akan Anda gunakan ketika menganalisis
penjualan
Tidak ada komentar:
Posting Komentar