Konfigurasi Tabel Pivot Excel 2010

Ada celah antara Microsoft Excel dan platform business intelligence (BI) tingkat atas selama bertahun-tahun. Peningkatan Tabel Pivot Microsoft Excel 2010 bersama dengan beberapa fitur BI lainnya telah menjadikannya pesaing nyata untuk perusahaan BI. Excel secara tradisional telah digunakan untuk analisis mandiri dan alat standar yang digunakan setiap orang untuk mengekspor laporan akhir mereka. Kecerdasan bisnis profesional secara tradisional telah disediakan untuk orang-orang seperti SAS, Business Objects, dan SAP.

01 dari 15

Hasil Akhir

Microsoft Excel 2010 (dengan Excel 2010 Pivot Table) bersama dengan SQL Server 2008 R2, SharePoint 2010 dan add-on Microsoft Excel 2010 gratis "PowerPivot" telah menghasilkan solusi intelijen dan pelaporan bisnis canggih.

Tutorial ini mencakup skenario langsung dengan Excel 2010 PivotTable yang terhubung ke database SQL Server 2008 R2 menggunakan kueri SQL sederhana.

Lanjutkan Membaca Di Bawah Ini

02 dari 15

Masukkan Pivot Table

Anda dapat memasukkan Tabel Pivot di buku kerja Excel baru atau yang sudah ada. Anda mungkin ingin mempertimbangkan untuk memposisikan kursor ke beberapa baris dari atas. Ini akan memberi Anda ruang untuk header atau informasi perusahaan jika Anda berbagi lembar kerja atau mencetaknya.

• Buka buku kerja Excel 2010 atau baru dan klik pada sel di mana Anda ingin sudut kiri atas Tabel Pivot menjadi.
• Klik pada tab Sisipkan dan klik pada drop-down PivotTable di bagian Tabel. Pilih PivotTable. Ini akan meluncurkan bentuk dialog Create PivotTable.

Lanjutkan Membaca Di Bawah Ini

03 dari 15

Hubungkan Pivot Table ke SQL Server

Excel 2010 dapat mengambil data dari semua penyedia RDBMS (Relational Database Management System) utama. Driver SQL Server harus tersedia untuk koneksi secara default. Periksa situs web mereka jika Anda perlu mengunduh driver ODBC.

Dalam kasus tutorial ini, kami menghubungkan ke SQL Server 2008 R2 (SQL Express versi gratis).

1. Buka formulir Buat PivotTable. Pilih "Gunakan sumber data eksternal" dan klik tombol Pilih Koneksi. Tinggalkan lokasi di mana Tabel Pivot akan ditempatkan.
2. Buka formulir Koneksi yang Ada. Klik pada tombol Browse for More.
3. Klik pada tombol Sumber Baru akan meluncurkan Wizard Koneksi Data.
4. Pilih Microsoft SQL Server dan klik Berikutnya.
5. Masukkan nama Server dan kredensial masuk. Pilih metode autentikasi yang sesuai:
   1. Gunakan Windows Authentication: Metode ini menggunakan login jaringan Anda untuk mengakses database SQL Server.
   2. Gunakan Nama Pengguna dan Kata Sandi berikut: Metode ini digunakan ketika SQL Server telah dikonfigurasi dengan pengguna yang berdiri sendiri untuk mengakses basis data.
6. Ganti tabel dengan SQL khusus yang akan memberikan data persis yang kita inginkan dalam buku kerja Excel kami:
   1. Pilih database yang akan Anda hubungkan. Dalam contoh ini, kami terhubung ke database contoh AdventureWorks yang disediakan oleh Microsoft. Periksa Connect ke tabel spesifik dan pilih tabel pertama. Ingat, kami tidak akan mengambil data dari tabel ini.
   2. Klik Selesai yang akan menutup wizard dan mengembalikan Anda ke buku kerja. Kami akan menukar tabel placeholder untuk permintaan SQL kustom kami.

Anda akan dikembalikan ke bentuk Create PivotTable (A). Klik OK.

04 dari 15

Tabel Pivot Sementara Terhubung ke Tabel SQL

Pada titik ini, Anda telah terhubung ke tabel placeholder dan Anda memiliki PivotTable kosong. Anda dapat melihat di sebelah kiri di mana PivotTable akan berada, dan di sebelah kanan, ada daftar bidang yang tersedia.

Lanjutkan Membaca Di Bawah Ini

05 dari 15

Buka Properti Koneksi

Pastikan Anda berada di tab Opsi dan klik pada tarik-turun Ubah Sumber Data dari bagian Data. Pilih Properti Koneksi.

Ini menampilkan form Properties Koneksi. Klik pada tab Definisi. Ini menunjukkan kepada Anda informasi koneksi untuk koneksi saat ini ke SQL Server. Ketika referensi file koneksi, data sebenarnya tertanam dalam spreadsheet.

06 dari 15

Perbarui Properti Koneksi Dengan Query

Ubah Jenis Perintah dari Tabel ke SQL dan timpa Teks Perintah yang ada dengan SQL Query Anda. Berikut adalah kueri yang kami buat dari database contoh AdventureWorks:

PILIH Sales.SalesOrderHeader.SalesOrderID,Sales.SalesOrderHeader.OrderDate,Sales.SalesOrderHeader.ShipDate,Sales.SalesOrderHeader.Status,Sales.SalesOrderHeader.SubTotal,Sales.SalesOrderHeader.TaxAmt,Sales.SalesOrderHeader.Freight,Sales.SalesOrderHeader.TotalDue,Sales.SalesOrderDetail.SalesOrderDetailID,Sales.SalesOrderDetail.OrderQty,Sales.SalesOrderDetail.UnitPrice,Sales.SalesOrderDetail.LineTotal,Production.Product.Name,Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,Sales.Customer.CustomerType,Production.Product.ListPrice,Production.Product.ProductLine,Production.ProductSubcategory.Name AS ProductCategoryDARI Sales.SalesOrderDetail INNER GABUNG Sales.SalesOrderHeader ONSales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDINNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =Production.Product.ProductID INNER JOIN Sales.Customer ONSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID ANDSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOINSales.vIndividualCustomer ON Sales.Customer.CustomerID =Sales.vIndividualCustomer.CustomerID INNER JOINProduction.ProductSubcategory ON Production.Product.ProductSubcategoryID =Production.ProductSubcategory.ProductSubcategoryID

Klik OK.

Lanjutkan Membaca Di Bawah Ini

07 dari 15

Terima Peringatan Koneksi

Anda akan menerima kotak dialog Peringatan Microsoft Excel. Ini karena kami mengubah informasi koneksi. Ketika kami awalnya menciptakan koneksi, itu menyimpan informasi dalam file .ODC eksternal (ODBC Data Connection). Data dalam buku kerja adalah sama dengan file .ODC sampai kita berubah dari jenis perintah tabel ke tipe perintah SQL pada Langkah # 6. Peringatan memberi tahu Anda bahwa data tidak lagi sinkron dan referensi ke file eksternal di buku kerja akan dihapus. Ini bagus. Klik Ya.

08 dari 15

Pivot Table Terhubung ke SQL Server Dengan Query

Ini mengambil kembali ke buku kerja Excel 2010 dengan PivotTable kosong. Anda dapat melihat bahwa bidang yang tersedia sekarang berbeda dan sesuai dengan bidang dalam kueri SQL. Kami sekarang dapat mulai menambahkan bidang ke PivotTable.

Lanjutkan Membaca Di Bawah Ini

09 dari 15

Tambahkan Bidang ke Tabel Pivot

Di Daftar Bidang PivotTable, seret Kategori Produk ke area Label Baris, area Tanggal Pesanan ke Kolom Label, dan TotalTentang ke Nilai. Seperti yang Anda lihat, bidang tanggal memiliki tanggal tertentu sehingga PivotTable telah membuat kolom untuk setiap tanggal unik. Excel 2010 memiliki beberapa fungsi built-in untuk membantu kami mengatur bidang tanggal.

10 dari 15

Tambahkan Pengelompokan untuk Bidang Tanggal

Fungsi Pengelompokan memungkinkan kami mengatur tanggal ke tahun, bulan, kuartal, dll. Ini akan membantu meringkas data dan memudahkan pengguna untuk berinteraksi dengannya. Klik kanan pada salah satu tajuk kolom tanggal dan pilih Grup yang menampilkan formulir Pengelompokan.

Lanjutkan Membaca Di Bawah Ini

11 dari 15

Pilih Pengelompokan Berdasarkan Nilai

Bergantung pada jenis data yang Anda kelompokkan, formulir akan terlihat sedikit berbeda. Excel 2010 memungkinkan Anda untuk mengelompokkan tanggal, angka dan data teks yang dipilih. Kami mengelompokkan OrderDate dalam tutorial ini sehingga formulir akan menampilkan opsi yang terkait dengan pengelompokan tanggal.

Klik Bulan dan Tahun dan klik OK.

12 dari 15

Pivot Table dikelompokkan berdasarkan Tahun dan Bulan

Data dikelompokkan berdasarkan tahun pertama dan kemudian berdasarkan bulan. Masing-masing memiliki tanda plus dan minus yang memungkinkan Anda untuk memperluas dan runtuh tergantung pada bagaimana Anda ingin melihat data.

Pada titik ini, PivotTable sangat berguna. Masing-masing bidang dapat difilter tetapi masalahnya adalah tidak ada petunjuk visual mengenai status filter saat ini. Diperlukan beberapa klik untuk mengubah tampilan.

13 dari 15

Masukkan Slicer (Baru di Excel 2010)

Slicers masih baru di Excel 2010. Slicers pada dasarnya sama dengan filter pengaturan visual bidang yang ada dan membuat Filter Laporan dalam hal item yang ingin Anda filter tidak ada dalam tampilan PivotTable saat ini. Hal yang baik tentang Slicers ini menjadi sangat mudah bagi pengguna untuk mengubah tampilan data di PivotTable serta menyediakan indikator visual untuk status filter saat ini.

Untuk memasukkan Slicers, klik pada tab Options dan klik Insert Slicer dari bagian Sort & Filter. Pilih Insert Slicer yang membuka form Slicers Insert. Periksa sebanyak mungkin bidang yang Anda inginkan.

14 dari 15

Tabel Pivot Dengan User Friendly Slicers

Seperti yang Anda lihat, Slicers menunjukkan semua data sebagai terpilih. Sangat jelas bagi pengguna apa tepatnya data dalam tampilan PivotTable saat ini.

15 dari 15

Pilih Nilai Dari Slicers Yang Pembaruan Tabel Pivot

Klik berbagai kombinasi nilai dan lihat bagaimana tampilan PivotTable berubah. Anda dapat menggunakan tipikal Microsoft mengklik di Slicers yang berarti bahwa jika Anda dapat menggunakan Control + Klik untuk memilih beberapa nilai atau Shift + Klik untuk memilih berbagai nilai.

Setiap Slicer menampilkan nilai yang dipilih yang membuatnya sangat jelas apa status PivotTable dalam hal filter. Anda dapat mengubah style dari Slicers jika Anda mau dengan mengklik pada drop-down Gaya Cepat di bagian Slicer pada tab Options.