01 dari 15
Hasil Akhir
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.
Microsoft Excel 2010 (dengan Tabel Pivot Excel 2010) bersama dengan SQL Server 2008 R2, SharePoint 2010 dan add-on Microsoft Excel 2010 gratis "PowerPivot" telah menghasilkan solusi intelijen dan pelaporan bisnis high-end.
Tutorial ini mencakup skenario lurus ke depan dengan Excel 2010 PivotTable yang terhubung ke database SQL Server 2008 R2 menggunakan kueri SQL sederhana. Saya juga menggunakan Slicers untuk penyaringan visual yang baru di Excel 2010. Saya akan membahas teknik BI yang lebih kompleks menggunakan Data Analysis Expressions (DAX) di PowerPivot untuk Excel 2010 dalam waktu dekat. Rilis terbaru dari Microsoft Excel 2010 ini dapat memberikan nilai nyata bagi komunitas pengguna Anda.
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.
03 dari 15
Hubungkan Tabel Pivot ke SQL Server (atau Basis Data Lain)
Excel 2010 dapat mengambil data dari semua penyedia RDBMS (Relational Database Management System) utama. Driver SQL Server harus tersedia untuk koneksi secara default. Tetapi semua perangkat lunak basis data utama membuat driver ODBC (Open Database Connectivity) untuk memungkinkan Anda melakukan koneksi. Periksa situs web mereka jika Anda perlu mengunduh driver ODBC.
Dalam kasus tutorial ini, saya menghubungkan ke SQL Server 2008 R2 (SQL Express versi gratis).
- A - Form Create PivotTable adalah bentuk pertama dalam membuat koneksi ke SQL Server. Pilih "Gunakan sumber data eksternal" dan klik tombol Pilih Koneksi. Tinggalkan lokasi di mana Tabel Pivot akan ditempatkan kecuali Anda ingin membuat lembar kerja baru dan letakkan di sana.
- B - Formulir Koneksi yang Ada mencantumkan koneksi apa pun dalam buku kerja saat ini, di komputer Anda dan jaringan yang saat ini Anda sambungkan. Koneksi yang ada benar-benar hanya file teks dengan informasi koneksi yang diperlukan untuk mengakses sumber data tertentu. Dalam kasus kami, kami akan membuat sumber data baru. Klik pada tombol Browse for More.
- C - Klik pada tombol New Source akan meluncurkan Wizard Koneksi Data.
- D - Pilih Microsoft SQL Server dan klik Next.
- E - Masukkan nama Server dan login kredensial. Pilih metode autentikasi yang sesuai. Jika Anda tidak yakin metode mana yang digunakan, hubungi administrator basis data Anda.
- Gunakan Windows Authentication: Metode ini menggunakan login jaringan Anda untuk mengakses database SQL Server.
- Gunakan Nama Pengguna dan Kata Sandi berikut: Metode ini digunakan ketika SQL Server telah dikonfigurasi dengan pengguna yang berdiri sendiri untuk mengakses basis data.
- F - Pada langkah ini, kita akan memilih meja sebagai placeholder. Kami akan mengganti tabel dengan SQL khusus yang akan menyediakan data persis yang kami inginkan dalam buku kerja Excel kami.
- 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.
- 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 adalah PivotTable akan dan di sebelah kanan ada daftar bidang yang tersedia.
05 dari 15
Buka Properti Koneksi
Sebelum kita mulai memilih data untuk PivotTable, kita perlu mengubah koneksi ke query SQL. 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 saya 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 ProductCategory
DARI Sales.SalesOrderDetail INNER GABUNG Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =
Production.Product.ProductID INNER JOIN Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID AND
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
Production.ProductSubcategory ON Production.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID
Klik OK.
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.
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. Gambar menunjukkan hasil. Seperti yang Anda lihat, bidang tanggal memiliki tanggal tertentu sehingga PivotTable telah membuat kolom untuk setiap tanggal unik. Untungnya, Excel 2010 memiliki beberapa fungsi yang dibangun 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.
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
Seperti yang Anda lihat pada gambar di atas, 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. Juga, dibutuhkan 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. Dalam contoh kami, saya menambahkan Tahun, CountryRegionName dan ProductCategory. Anda mungkin harus memposisikan Slicers di mana Anda menginginkannya. Secara default, semua nilai dipilih yang berarti tidak ada filter yang diterapkan.
14 dari 15
Tabel Pivot Dengan User Friendly Slicers
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.
Pengenalan Slicers telah benar-benar meningkatkan kegunaan PivotTable dan telah memindahkan Excel 2010 lebih dekat menjadi alat intelijen bisnis profesional. PivotTable telah meningkat sedikit di Excel 2010 dan ketika digabungkan dengan PowerPivot yang baru menciptakan lingkungan analitik kinerja yang sangat tinggi.