Carane Ngatur Excel 2010 Pivot Tables

by Tim Perdue

01 saka 15

Final Result

Iki minangka asil final langkah iki dening tutorial Langkah - Klik ing gambar kanggo ndeleng versi ukuran lengkap.

Ana kesenjangan antarane Microsoft Excel lan platform Intelligence Business Intelligence (BI) ndhuwur kanggo akeh taun. Penambahan Microsoft Excel 2010 Pivot Table bebarengan karo sawetara fitur BI liya wis ndadekake pesaing nyata kanggo perusahaan BI. Excel wis sacara tradisional digunakake kanggo analisis mandiri lan alat standar sing saben wong ngekspor laporan final menyang. Profesional bisnis Intelligence wis tradisional wis undhang kanggo seneng SAS, Business Objects lan SAP.

Microsoft Excel 2010 (karo Excel 2010 Pivot Table) bebarengan karo SQL Server 2008 R2, SharePoint 2010 lan gratis Microsoft Excel 2010 nambah-ing "PowerPivot" wis ngasilake dhuwur bisnis Intelligence lan nglaporake solusi.

Tutorial iki nyakup skenario maju sakcara karo Excel 2010 PivotTable disambungake menyang basis data SQL Server 2008 R2 nggunakake query SQL prasaja. Aku uga nggunakake Slicers kanggo nyaring visual sing anyar ing Excel 2010. Aku bakal nutupi teknik BI luwih rumit nggunakake Data Analysis Expressions (DAX) ing PowerPivot kanggo Excel 2010 ing mangsa ngarep. Release paling anyar iki saka Microsoft Excel 2010 bisa nyedhiyakake nilai nyata kanggo komunitas panganggo sampeyan.

02 saka 15

Pasang Pivot Table

Posisi kursor sampeyan persis ing ngendi sampeyan pengin pivot table lan klik ing Pasang | Tabel Pivot.

Sampeyan bisa masang Tabel Pivot ing buku kerja Excel utawa anyar. Sampeyan bisa uga pengin nimbang posisi kursor mudhun sawetara baris saka ndhuwur. Iki bakal menehi ruang kanggo informasi header utawa perusahaan yen sampeyan nuduhake lembar kerja utawa nyetak.

Bukak workbook Excel utawa anyar 2010 lan klik ing sel ing ngendi sampeyan pengin sudhut kiwa ndhuwur Tabel Pivot dadi.
Klik ing tab Ketik lan klik ing PivotTable nyelehake mudhun ing bagean Tables. Pilih PivotTable. Iki bakal mbukak wangun dialog Create PivotTable.

03 saka 15

Sambungake Tabel Pivot kanggo SQL Server (utawa Database Liyane)

Nggawe query SQL banjur sambungake menyang SQL Server kanggo nampilake tali data sambungan menyang spreadsheet Excel.

Excel 2010 bisa njupuk data saka kabeh panyedhiya RDBMS (Relational Database Management System) . Pembalap SQL Server kudu kasedhiya kanggo sambungan kasebut kanthi standar. Nanging kabeh basis data utama nggawe ODBC (Open Database Connectivity) driver kanggo ngijini sambungan. Priksa situs web sing yen sampeyan pengin ngundhuh driver ODBC.

Ing kasus tutorial iki, aku nyambungake menyang SQL Server 2008 R2 (versi gratis Express SQL).

A - Form Create PivotTable yaiku wangun pisanan nggawe sambungan menyang SQL Server. Pilih "Gunakake sumber data eksternal" lan klik tombol Pilih Koneksi. Ninggalake lokasi ing ngendi Tabel Pivot bakal dilebokake kajaba sampeyan pengin nggawe lembaran kerja anyar lan panggonan ing kana.
B - Sambungan sing Ana mbentuk dhaptar sambungan ing buku tugas saiki, ing komputer lan jaringan sing lagi nyambung. Sambungan sing saiki pancene mung file teks mawa informasi sambungan sing dibutuhake kanggo ngakses sumber data tartamtu. Ing kasus kita, kita bakal nggawe sumber data anyar. Klik ing tombol Jlajah kanggo More.
C - Klik tombol New Source bakal miwiti Wisaya Koneksi Data.
D - Pilih Microsoft SQL Server lan klik Sabanjure.
E - Ketik jeneng server lan log on credentials. Pilih cara otentifikasi sing tepat. Yen sampeyan ora ngerti cara kang digunakake, hubungi administrator database sampeyan.
- Gunakake Windows Authentication: Cara iki nggunakake login jaringan kanggo ngakses database SQL Server.
- Gunakake Jeneng Pangguna lan Sandi ing ngisor iki : Metode iki digunakake nalika SQL Server wis dikonfigurasi karo pangguna mandiri kanggo ngakses database.
F - Ing langkah iki, kita bakal milih tabel minangka placeholder. Kita bakal ngganti tabel karo adat SQL sing bakal nyedhiyani persis data sing kita arep ing workbook Excel kita.
- Pilih basis data sing bakal disambungake. Ing conto iki, kita nyambungake menyang basis sampel database AdventureWorks sing disedhiyakake dening Microsoft. Priksa Sambungake menyang tabel tartamtu lan pilih tabel pisanan. Elingi, kita ora bakal njupuk data saka tabel iki.
- Klik Finish sing bakal nutup tuntunan lan bali menyang workbook. Kita bakal ngganti tabel kanggo placeholder kanggo query SQL adat kita.

Sampeyan bakal bali menyang formulir Create PivotTable (A). Klik OK.

04 saka 15

Tabel Pangsi Tabel Sementara Terus menyang Tabel SQL

PivotTable disambungake menyang SQL Server karo tabel placeholder.

Ing titik iki, sampeyan wis nyambung menyang tabel placeholder lan sampeyan duwe PivotTable kosong. Sampeyan bisa ndeleng ing sisih kiwa iku PivotTable bakal lan ing sisih tengen ana dhaptar lapangan sing kasedhiya.

05 saka 15

Open Connection Properties

Wangun Konektivitas Open Connection.

Sadurunge kita miwiti milih data kanggo PivotTable, kita kudu ngganti sambungan menyang query SQL. Priksa manawa sampeyan ana ing tab Pilihan lan klik Ganti Sumber Data tarik mudhun saka bagean Data. Pilih Properti Sambungan.

Iki bakal mbentuk wangun Sambungan Properties. Klik ing tab Definisi. Iki nuduhake sampeyan informasi sambungan kanggo sambungan saiki kanggo SQL server. Nalika referensi file sambungan, data kasebut bener-bener ditempelake ing spreadsheet.

06 saka 15

Nganyari Sambungan Properties Kanthi Bisa Nggoleki

Ngganti tabel kanggo query SQL.

Ngganti Tipe Command saka Tabel menyang SQL lan Timpa Tipe Command sing ana karo SQL Query. Punika pitakonan sing digawe saka database sampel AdventureWorks:

SELECT 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,
Produksi.Product.Name,
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Produksi.Product.ListPrice,
Produksi.Product.ProductLine,
Produksi.ProductSubcategory.Name AS ProductCategory
DARIPADA Sales.SalesOrderDetail INNER JOIN 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 =
Produksi.ProductSubcategory.ProductSubcategoryID

Klik OK.

07 saka 15

Nampa Warning Sambungan

Klik ing Ya kanggo Sambungan Peringatan.

Sampeyan bakal nampa kothak dialog Microsoft Excel Warning. Iki amarga kita ngganti informasi sambungan. Nalika kita wiwitan nggawe sambungan kasebut, informasi disimpen ing njaba .File ISO (ODBC Data Connection). Data ing buku kasebut padha karo file .ODC nganti kita ganti saka tipe command table menyang tipe perintah SQL ing Langkah # 6. Bebaya kasebut nuduhake yen data ora ono maneh lan referensi kanggo file eksternal ing buku kasebut bakal dibusak. Iki OK. Klik Ya.

08 saka 15

Tabel Pivot Disambungake menyang SQL Server Kanthi Query

PivotTable siap kanggo sampeyan nambahake data.

Iki bakal bali menyang Excel workbook kanthi PivotTable kosong. Sampeyan bisa ndeleng manawa kolom sing wis ana saiki beda lan cocog karo kolom ing query SQL. Saiki kita bisa miwiti nambahake kolom menyang PivotTable.

09 saka 15

Tambah Fields menyang Pivot Table

Tambah lapangan menyang Pangsi.

Ing List Field PivotTable, seret ProductCategory menyang Row Label area, OrderDate to Column Labels area lan TotalDue to Values area. Gambar nuduhake asil. Minangka sampeyan bisa ndeleng, kolom tanggal nduweni tanggal individu supaya PivotTable wis nggawe kolom kanggo saben tanggal sing unik. Muga-muga, Excel 2010 nduweni fungsi sing digawé kanggo mbantu kita ngorganisasi tanggal.

10 saka 15

Nambah Pengelompokan kanggo Bidang Tanggal

Nambah Penggabungan kanggo lapangan tanggal.

Fungsi Grouping ngidini kita ngatur tanggal dadi taun, wulan, kuartal, dll. Iki bakal ngringkes data lan ndadekake gampang kanggo pangguna. Klik tengen ing salah siji saka kolom asli header lan milih Group sing ndadekke wangun Grouping.

11 saka 15

Pilih Grouping Miturut Nilai

Milih item pengelompokan kanggo tanggal lapangan.

Gumantung ing jenis data sing diklompokake, wangun bakal katon beda sing beda. Excel 2010 ngidini sampeyan gabungke tanggal, nomer lan data teks sing dipilih. We are grouping OrderDate ing tutorial iki supaya wangun bakal nuduhake opsi sing cocog karo klompok tanggal.

Klik Sabanjure lan Taun lan klik OK.

12 saka 15

Tabel Pivot Dipangku dening Taun lan Sasi

Tanggal lapangan dikelompokake miturut taun lan sasi.

Minangka sampeyan bisa ndeleng ing gambar ndhuwur, data diklompokake miturut taun pisanan lan banjur sasi. Saben duwe tandha plus lan minus sing ngidini sampeyan nggedhekake lan ambruk gumantung carane sampeyan pengin ndeleng data.

Ing titik iki, PivotTable cukup migunani. Saben lapangan bisa disaring, nanging masalah iku ora ana pitunjuk visual babagan kondisi saringan saiki. Uga, njupuk sawetara klik kanggo ngganti tampilan.

13 saka 15

Pasang Slicer (Anyar ing Excel 2010)

Tambah Slicers menyang PivotTable.

Sliker anyar ing Excel 2010. Sliker sing sejatine cocog karo filter panyaring visual sing wis ana lan nggawe Filter Filter Report sajrone item sing pengin disaring ora ana ing tampilan PivotTable saiki. Bab iki becik babagan Slicers dadi gampang banget kanggo pangguna kanggo ngganti tampilan data ing PivotTable uga nyediakake pratondho visual minangka status filter saiki.

Kanggo masang Slicers, klik tab Options lan klik Insert Slicer saka bagian Urut & Filter. Pilih Insert Slicer sing mbukak wangun Insert Slicers. Priksa kabeh kolom sing pengin sampeyan wis kasedhiya. Ing conto liyane, aku nambah Taun, CountryRegionName lan ProductCategory. sampeyan kudu nyetel Slicers ing ngendi sampeyan pengin. Secara default, kabeh nilai wis dipilih sing tegese ora ana filter sing wis diterapake.

14 saka 15

Tabel Pivot Kanthi Anggota Pangguna Ramah

Slicer nggawe kui gampang kanggo pengguna Nyaring PivotTables.

Minangka sampeyan bisa ndeleng, Slicers nuduhake kabeh data sing dipilih. Iku banget cetha kanggo pangguna persis apa data ing tampilan saiki PivotTable.

15 saka 15

Pilih Nilai-nilai Saka Slicers Sing Anyar Pivot Tabel

Pilih kombinasi Slicers kanggo ngganti tampilan data.

Klik ing macem-macem kombinasi nilai lan delengen ndeleng owahan tampilan PivotTable. Sampeyan bisa migunakake Microsoft ngeklik ing Slicers tegese yen sampeyan bisa nggunakake Control + Klik kanggo milih pirang-pirang nilai utawa Shift + Klik kanggo milih sawetara nilai. Saben Slicer nampilake angka-angka sing wis dipilih sing ndadekake tenan nyathet apa kondisi PivotTable ing saringan. Sampeyan bisa ngganti gaya Slicers yen pengin kanthi klik ing Gaya Cepet nyelehake mudhun ing bagean Slicer saka tab Options.

Perkenalan Slicers wis nggedhekake kegunaan PivotTables lan wis mindhahake Excel 2010 luwih nyedhaki dadi alat bisnis intelijen profesional. PivotTables wis cukup apik ing Excel 2010 lan nalika digabungake karo PowerPivot anyar nggawe lingkungan analisis kinerja sing dhuwur banget.