Cara Konfigurasi Excel 2010 Pivot Tables

by Tim Perdue

01 dari 15

Keputusan akhir

Ini adalah hasil akhir Tutorial Langkah demi Langkah ini - Klik pada imej untuk melihat versi bersaiz penuh.

Terdapat jurang antara Microsoft Excel dan platform perisikan perniagaan peringkat tinggi (BI) selama bertahun-tahun. Peningkatan Microsoft Excel 2010 Pivot Table bersama-sama dengan beberapa ciri BI yang lain menjadikannya pesaing sebenar untuk BI perusahaan. Excel secara tradisinya digunakan untuk analisis mandiri dan alat standard yang semua orang mengeksport laporan akhir mereka ke dalam. Perisikan perniagaan profesional secara tradisi telah disediakan untuk orang-orang seperti SAS, Objek Perniagaan dan SAP.

Microsoft Excel 2010 (dengan Excel Pivot Table 2010) bersama dengan SQL Server 2008 R2, SharePoint 2010 dan tambahan Microsoft Excel 2010 "PowerPivot" telah menghasilkan penyelesaian kecerdasan dan pelaporan perniagaan yang tinggi.

Tutorial ini merangkumi senario lurus ke hadapan dengan Excel 2010 PivotTable disambungkan ke pangkalan data SQL Server 2008 R2 menggunakan pertanyaan SQL mudah. Saya juga menggunakan Slicers untuk penapisan visual yang baru di Excel 2010. Saya akan meliputi teknik BI yang lebih kompleks menggunakan Ekspresi Analisis Data (DAX) dalam PowerPivot untuk Excel 2010 dalam masa terdekat. Pembebasan terkini Microsoft Excel 2010 ini boleh memberikan nilai sebenar untuk komuniti pengguna anda.

02 dari 15

Masukkan Jadual Pangsi

Posisi kursor anda dengan tepat di mana anda mahu jadual pangsi anda dan klik pada Sisipkan | Jadual Pivot.

Anda boleh memasukkan Jadual Pivot dalam buku kerja Excel baru atau sedia ada. Anda mungkin ingin mempertimbangkan kedudukan kursor anda ke beberapa baris dari bahagian atas. Ini akan memberi anda ruang untuk maklumat header atau syarikat sekiranya anda berkongsi lembaran kerja atau mencetaknya.

Buka buku kerja Excel baru atau sedia ada dan klik pada sel di mana anda ingin sudut kiri atas Jadual Pivot menjadi.
Klik pada tab Sisip dan klik pada drop PivotTable di bahagian Tables. Pilih Jadual Pangsi. Ini akan melancarkan borang dialog Buat PivotTable.

03 dari 15

Sambung Jadual Pivot ke SQL Server (atau Pangkalan Data Lain)

Buat pertanyaan SQL anda dan kemudian sambungkan ke SQL Server untuk membenamkan rentetan data sambungan ke hamparan Excel.

Excel 2010 boleh mendapatkan data dari semua penyedia RDBMS (Sistem Pengurusan Pangkalan Data Relasi) utama. Pemacu SQL Server sepatutnya tersedia untuk sambungan secara lalai. Tetapi semua perisian pangkalan data utama membuat ODBC (Open Database Connectivity) untuk membolehkan anda membuat sambungan. Semak laman web mereka jika anda perlu memuat turun pemacu ODBC.

Dalam kes tutorial ini, saya menyambung ke SQL Server 2008 R2 (versi percuma Express SQL).

A - Borang Buat PivotTable adalah bentuk pertama dalam membuat sambungan ke SQL Server. Pilih "Gunakan sumber data luaran" dan klik pada butang Pilih Sambungan. Biarkan lokasi di mana Jadual Pivot akan diletakkan melainkan anda ingin mencipta lembaran kerja baru dan letakkan di sana.
B - Borang Sambungan Sedia ada menyenaraikan sebarang sambungan dalam buku kerja semasa, pada komputer anda dan rangkaian yang sedang anda sambungkan. Sambungan sedia ada adalah benar-benar hanya fail teks dengan maklumat sambungan yang diperlukan untuk mengakses sumber data tertentu. Dalam kes kita, kita akan mencipta sumber data baru. Klik pada butang Browse for More.
C - Klik pada butang Sumber Baru akan melancarkan Wizard Sambungan Data.
D - Pilih Microsoft SQL Server dan klik Next.
E - Masukkan nama Pelayan dan log kelayakan. Pilih kaedah pengesahan yang sesuai. Jika anda tidak pasti kaedah mana yang hendak digunakan, hubungi pentadbir pangkalan data anda.
- Gunakan Windows Authentication: Kaedah ini menggunakan log masuk rangkaian anda untuk mengakses pangkalan data SQL Server.
- Gunakan Nama Pengguna dan Kata Laluan yang berikut: Kaedah ini digunakan apabila SQL Server telah dikonfigurasi dengan pengguna yang berdiri sendiri untuk mengakses pangkalan data.
F - Dalam langkah ini, kita akan memilih jadual sebagai pemegang tempat. Kami akan menggantikan jadual dengan SQL tersuai yang akan memberikan tepat data yang kami kehendaki dalam buku kerja Excel kami.
- Pilih pangkalan data yang akan anda sambungkan. Dalam contoh ini, kami menyambung ke pangkalan data sampel AdventureWorks yang disediakan oleh Microsoft. Semak Sambungkan ke jadual tertentu dan pilih jadual pertama. Ingat, kami tidak akan mengambil data dari jadual ini.
- Klik Selesai yang akan menutup wizard dan mengembalikan anda ke buku kerja. Kami akan menukar jadual pemegang tempat untuk pertanyaan SQL tersuai kami.

Anda akan dikembalikan ke borang Cipta PivotTable (A). Klik OK.

04 dari 15

Pivot Table Temporarily Connected to Table SQL

PivotTable disambungkan kepada SQL Server dengan jadual pemegang tempat.

Pada ketika ini, anda telah disambungkan ke jadual pemegang tempat dan anda mempunyai PivotTable kosong. Anda boleh melihat di sebelah kiri adalah PivotTable akan dan di sebelah kanan terdapat senarai medan yang tersedia.

05 dari 15

Open Connection Properties

Borang Sambungan Terbuka.

Sebelum kita mula memilih data untuk PivotTable, kita perlu menukar sambungan ke pertanyaan SQL. Pastikan anda berada di tab Opsyen dan klik pada Tukar Ubah Sumber Data dari seksyen Data. Pilih Properties Sambungan.

Ini membawa borang Sambungan Properties. Klik pada tab Definisi. Ini menunjukkan anda maklumat sambungan untuk sambungan semasa ke SQL Server. Walaupun merujuk fail sambungan, data itu sebenarnya tertanam dalam spreadsheet.

06 dari 15

Kemas kini Hartanah Sambungan Dengan Pertanyaan

Tukar jadual ke pertanyaan SQL.

Tukar Jenis Perintah dari Jadual ke SQL dan tulis ganti Teks Perintah yang ada dengan Query SQL anda. Inilah pertanyaan yang saya buat daripada pangkalan data 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,
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 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 =
Production.ProductSubcategory.ProductSubcategoryID

Klik OK.

07 dari 15

Terima Amaran Sambungan

Klik pada Ya untuk Amaran Sambungan.

Anda akan menerima kotak dialog Peringatan Microsoft Excel. Ini kerana kami mengubah maklumat sambungan. Apabila kita mula-mula membuat sambungan, ia menyimpan maklumat dalam fail luar .ODC (Sambungan Data ODBC). Data dalam buku kerja adalah sama dengan fail .ODC sehingga kami berubah dari jenis perintah jadual ke jenis perintah SQL dalam Langkah # 6. Amaran ini memberitahu anda bahawa data tidak lagi disegerakkan dan rujukan kepada fail luaran dalam buku kerja akan dikeluarkan. Ini ok. Klik Ya.

08 dari 15

Jadual Pivot Disambungkan ke SQL Server Dengan Pertanyaan

PivotTable sedia untuk anda menambah data.

Ini mengambil kembali ke Excelbook workbook dengan PivotTable kosong. Anda dapat melihat bahawa medan yang ada sekarang berbeza dan sesuai dengan bidang dalam query SQL. Sekarang kita boleh mula menambah medan ke PivotTable.

09 dari 15

Tambah Fields to Table Pivot

Tambah medan ke PivotTable.

Dalam Senarai Medan PivotTable, seret ProductCategory ke kawasan Label Barisan, OrderDate hingga Label Label kawasan dan TotalDue ke Kawasan Nilai. Imej menunjukkan hasilnya. Seperti yang dapat anda lihat, medan tarikh mempunyai tarikh individu sehingga PivotTable telah membuat lajur untuk setiap tarikh yang unik. Untungnya, Excel 2010 mempunyai beberapa fungsi yang dibina untuk membantu kami mengatur bidang tarikh.

10 daripada 15

Tambah Pengumpulan untuk Bidang Tarikh

Tambah Penggredan untuk medan tarikh.

Fungsi Penggabungan membolehkan kami mengatur tarikh ke tahun, bulan, suku, dan lain-lain. Ini akan membantu meringkaskan data dan memudahkan pengguna untuk berinteraksi dengannya. Klik kanan pada salah satu tajuk lajur tarikh dan pilih Kumpulan yang membawa borang Pengkelasan.

11 daripada 15

Pilih Kumpulan Mengikut Nilai

Pilih item pengumpulan untuk bidang tarikh.

Bergantung pada jenis data yang dikumpulkan, borang akan kelihatan sedikit berbeza. Excel 2010 membolehkan anda mengumpulkan tarikh, nombor dan data teks terpilih. Kami mengumpul OrderDate dalam tutorial ini supaya borang tersebut akan menunjukkan pilihan yang berkaitan dengan pengelasan tarikh.

Klik pada Bulan dan Tahun dan klik OK.

12 daripada 15

Jadual Pivot dikelompokkan mengikut Tahun dan Bulan

Bidang tarikh dikumpulkan mengikut tahun dan bulan.

Seperti yang dapat anda lihat dalam imej di atas, data dikumpulkan mengikut tahun pertama dan kemudian mengikut bulan. Setiap mempunyai tanda tambah dan tolak yang membolehkan anda mengembangkan dan runtuh bergantung kepada cara anda ingin melihat data.

Pada ketika ini, PivotTable sangat berguna. Setiap bidang boleh ditapis tetapi masalahnya adalah tidak ada petunjuk visual tentang keadaan semasa penapis. Juga, diperlukan beberapa klik untuk menukar pandangan.

13 dari 15

Masukkan Slicer (Baru di Excel 2010)

Tambah Slicers ke PivotTable.

Slicers baru dalam Excel 2010. Slicers pada dasarnya sama dengan penapis visual yang setanding dengan medan sedia ada dan mencipta Penapis Laporan dalam hal item yang anda ingin penyaring tidak dalam pandangan PivotTable saat ini. Perkara yang menarik tentang Slicers adalah menjadi sangat mudah bagi pengguna untuk menukar pandangan data dalam PivotTable serta menyediakan petunjuk visual mengenai keadaan semasa penapis.

Untuk memasukkan Slicers, klik pada tab Opsyen dan klik pada Masukkan Slicer dari bahagian Urus & Penapis. Pilih Insert Slicer yang membuka borang Insert Slicers. Semak seberapa banyak medan yang anda ingin ada. Dalam contoh kami, saya menambah Tahun, CountryRegionName dan ProductCategory. anda mungkin perlu meletakkan Slicers di mana anda mahu mereka. Secara lalai, semua nilai dipilih yang bermaksud tiada penapis telah digunakan.

14 daripada 15

Pivot Table With Slicers Friendly User

Slicers memudahkan pengguna untuk menapis PivotTables.

Seperti yang dapat anda lihat, Slicers menunjukkan semua data yang dipilih. Ia sangat jelas kepada pengguna dengan tepat apakah data dalam pandangan semasa PivotTable.

15 dari 15

Pilih Nilai Dari Slicers Yang Jadual Pembaharuan Pivot

Pilih kombinasi Slicer untuk menukar paparan data.

Klik pada pelbagai kombinasi nilai dan lihat bagaimana pandangan perubahan PivotTable. Anda boleh menggunakan mengklik Microsoft biasa dalam Slicers yang bermaksud bahawa jika anda boleh menggunakan Control + Click untuk memilih berbilang nilai atau Shift + Klik untuk memilih pelbagai nilai. Setiap Slicer memaparkan nilai-nilai yang dipilih yang menjadikannya benar-benar jelas apa keadaan PivotTable adalah dari segi penapis. Anda boleh menukar gaya Slicers jika anda mahu dengan mengklik pada Gaya Pantas drop down di bahagian Slicer pada tab Opsyen.

Pengenalan Slicer benar-benar meningkatkan keupayaan PivotTables dan telah memindahkan Excel 2010 lebih dekat menjadi alat perisikan perniagaan profesional. PivotTables telah bertambah baik sedikit di Excel 2010 dan apabila digabungkan dengan PowerPivot baru mencipta persekitaran analisis prestasi yang sangat tinggi.