Indlela Yokulungisa I-Excel Tables Ye-Excel 2010

by Tim Perdue

01 ngo-15

Umphumela wokugcina

Lona umphumela wokugcina walesi Sinyathelo ngesinyathelo sokufundisa - Chofoza esithombeni ukuze ubone inguqulo ephelele.

Kube khona igebe eliphakathi kwe-Microsoft Excel kanye namapulatifomu e-BI) amaningi eminyaka. Izincomo ze-Microsoft Excel 2010 Pivot Table kanye nezinye izici ze-BI zenze umncintiswano wangempela webhizinisi le-BI. I-Excel isesetshenziselwe ukuhlaziywa ngokuzenzakalelayo kanye nethuluzi elijwayelekile ukuthi wonke umuntu athumele imibiko yawo yokugcina ibe. Ukuhlakanipha kwebhizinisi okwenziwe ngomsebenzi ngokuvamile kuye kwagcinwa okuthandwa yi-SAS, Business Objects ne-SAP.

I-Microsoft Excel 2010 (ne-Excel 2010 Pivot Table) kanye ne-SQL Server 2008 R2, SharePoint 2010 kanye nokungena kwamahhala kwe-Microsoft Excel 2010 "PowerPivot" kuye kwaholela ekupheleni kwesixazululo sokuhweba kwebhizinisi kanye nokubika.

Lesi sifundo sihlanganisa isimo esiqondile ngqo nge-Excel 2010 PivotTable exhunywe ku-database ye-SQL Server 2008 R2 besebenzisa umbuzo olula we-SQL. Ngisebenzisa futhi ama-Slicers ukuhlunga okubukwayo okusha ku-Excel 2010. Ngizofaka amasu amaningi e-BI ahlukeneyo usebenzisa ukuveza idatha ye-Data Analysis (DAX) ku-PowerPivot ye-Excel 2010 esikhathini esizayo esiseduze. Lokhu kukhishwa kwakamuva kwe-Microsoft Excel 2010 kunganikeza inani langempela lomphakathi wakho womsebenzisi.

02 ngo-15

Faka i-Pivot Table

Beka isikhombisi sakho ngqo lapho ufuna ithebula lakho le-pivot bese uchofoza ku-Faka | Ithebula le-Pivot.

Ungafaka ithebula le-Pivot encwadini entsha ye-Excel noma entsha. Ungase uthande ukucabangela ukubeka isikhombisi sakho phansi imigqa embalwa kusuka phezulu. Lokhu kuzokunika isikhala se-header noma imininingwane yenkampani uma wabelana ngephepha lokusebenzela noma uliphrinta.

Vula ibhuku lokusebenzela elisha noma elikhona le-Excel 2010 bese uchofoze esitokisini lapho ufuna khona ikhoneni elingakwesokunxele lethebula le-Pivot.
Chofoza ku-Faka ithebhu futhi uchofoze phansi phansi kwe-PivotTable esigabeni seTables. Khetha i-PivotTable. Lokhu kuzosungula ifomu lokudala le-PivotTable.

03 ka-15

Xhuma i-Pivot Table kuya ku-SQL Server (noma enye i-Database)

Dala umbuzo wakho we-SQL bese uxhuma kwi-SQL Server ukuze ungenele uchungechunge lwemininingwane yedatha ku-spreadsheet ye-Excel.

I-Excel 2010 ingayithola idatha evela kuzo zonke izikhulu ezinkulu ze- RDBMS (Abaxhumanisi beDesign Management System) . Abashayeli be-SQL Server kufanele batholakale ukuxhumana ngokuzenzakalelayo. Kodwa zonke izinhlelo zokusebenza ezinkulu ze-database zenza i-ODBC (Vula i-Database Ukuxhumana) abashayela ukukuvumela ukuthi wenze uxhumano. Hlola iwebhusayithi yabo uma udinga ukulanda abashayeli be-ODBC.

Endabeni yalolu tutorial, ngiyaxhuma ku-SQL Server 2008 R2 (version samahhala ye-SQL Express).

A - Yakha ifomu le-PivotTable yindlela yokuqala ekudaleni uxhumano kwi-SQL Server. Khetha "Sebenzisa umthombo wedatha wangaphandle" bese uchofoza inkinobho ethi Khetha Ukuxhuma. Shiya indawo lapho i-Pivot Table izobekwa khona ngaphandle kokuthi ufuna ukudala ishidi elisha lomsebenzi bese ulibeka lapho.
B - I-Connections ekhona ifaka uhlu lwazo zonke izixhumanisi ebhokisini lamanje, kwikhompyutheni yakho kanye nenethiwekhi okwamanje uxhunyiwe kuyo. Ukuxhumeka okukhona kungamafayela wombhalo kuphela nolwazi lokuxhumeka oludingekayo ukuze ufinyelele umthombo othile wedatha. Esikhathini sethu, sizokwakha umthombo omusha wedatha. Chofoza ku-inkinobho ye-Browse for More.
C - Chofoza inkinobho yomthombo omusha izoqala i-Data Connection Wizard.
D - Khetha i-Microsoft SQL Server bese uchofoza Okulandelayo.
E - Faka igama leseva bese ungena kwiziqinisekiso. Khetha indlela efanele yokugunyaza. Uma ungaqiniseki ukuthi iyiphi indlela ongayisebenzisa, xhumana nomphathi wakho wedatha.
- Sebenzisa i-Windows Ukuqinisekiswa: Le ndlela isebenzisa ukungena ngemvume kwenethiwekhi ukuze ufinyelele imininingwane yolwazi lwe-SQL Server.
- Sebenzisa igama elilandelayo lomsebenzisi nephasiwedi: Le ndlela isetshenziswa lapho iSQL Server isilungiselelwe ngabasebenzisi abasebenzisayo ukuze bafinyelele yolwazi.
F - Kulesi sinyathelo, sizokhetha ithebula njengendawo yokuhlala. Sizofaka esikhundleni setafula nge-SQL yangokwezifiso ezohlinzeka ngokuqondile idatha esiyifunayo ebhukwini lethu le-Excel.
- Khetha i-database ozoyixhuma kuyo. Kulesi sibonelo, sixhuma kwisampula ye-AdventureWorks yesampula esinikezwa yi-Microsoft. Bheka i-Connect kutafula ethize bese ukhetha ithebula lokuqala. Khumbula, asiyikuthola idatha kusuka kuleli tafula.
- Chofoza Qedela okuzovala isivini bese sikubuyisela encwadini yokusebenzela. Sizobe siphumelela ithebula lendawo yokubeka umbuzo we-SQL yethu yangokwezifiso.

Uzobuyiselwa kwifomu Yakha i-PivotTable (A). Chofoza OK.

04 ka-15

Ithebula le-Pivot lixhumeke okwesikhashana ku-Ithebula le-SQL

I-PivotTable ixhunywe ku-SQL Server netafula lesibambiso.

Kuleli qophelo, uxhumekile kuthebula lezindawo futhi une-Pivotable engenalutho. Ungabona ngakwesobunxele kwakuyi-PivotTable kuyoba khona futhi ngakwesokudla kukhona uhlu lwamasimu atholakalayo.

05 ka-15

Vula izakhiwo zokuxhumeka

Vula ifomu lesakhiwo se-Connection.

Ngaphambi kokuba siqale ukukhetha idatha ye-PivotTable, kudingeka sishintshe uxhumano kumbuzo we-SQL. Qinisekisa ukuthi usethe kuthebhu Izinketho bese uchofoze ku-Shintsha idatha yomthombo wehla ngaphansi kwesigaba seDatha. Khetha izindawo zokuxhumeka.

Lokhu kuphakamisa ifomu leZakhiwo ze-Connection. Chofoza kuthebhu yokuchaza. Lokhu kukubonisa ulwazi lokuxhumeka kokuxhuma kwamanje kwi-SQL Server. Ngenkathi icacisa ifayela lokuxhumeka, idatha empeleni ishumeke kuspredishithi.

06 ka-15

Buyekeza izindawo zokuxhumeka nombuzo

Shintsha ithebula kumbuzo we-SQL.

Shintsha Uhlobo LokuLawula kusuka kuThebula kuya ku-SQL bese ubhala umbhalo okhona okhona we-Umyalo wakho we-SQL. Nansi umbuzo engiwudalile kusuka ku-database ye-AdventureWorks yesampula:

Khetha ukuthengisa.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,
I-Production.Product.Name,
I-Sales.vKuqoboI-Customer.StateProvinceName, Sales.vNye-kuphelaCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Ukukhiqiza.Ukukhiqiza.ListPrice,
Ukukhiqiza.Product.ProductLine,
I-Production.ProductSubcategory.Name njenge-ProductCategory
FROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production
I-Production.Product.ProductID INNER JOIN Sales.I-Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID NOMA
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
I-Sales.vNjaloI-Customer ON Sales.Customer.CustomerID =
I-Sales.vKuqoboCustomer.CustomerID INNER JOIN
Ukukhiqiza.UkukhiqizaUkukhiqizaUkukhiqiza.Ukukhiqiza.I-ProductivitySubcategoryID =
Ukukhiqiza.I-ProductivitySubcategory.ProductSubcategoryID

Chofoza OK.

07 kwangu-15

Thola Isexwayiso se-Connection

Chofoza ku-Yebo kusixwayiso sokuxhuma.

Uzothola ibhokisi lengxoxo ye-Microsoft Excel Warning. Lokhu kungenxa yokuthi sishintshe ulwazi loxhumano. Uma ekuqaleni sakha uxhumano, kulondolozwe ulwazi lwangaphandle .Ifayela le -ODC (ODBC Data Connection). Idatha encwadini yokusebenzela yayifana nefayela le -ODC kuze kube yilapho sishintsha kusuka kuhlobo lomyalo wetafula kuya kuhlobo lomyalo we-SQL ku-Step # 6. Isixwayiso sikutshela ukuthi idatha ayisekho ukuvumelanisa futhi ukubhekisela kwefayela langaphandle ebhukwini lokusebenzela kuzosuswa. Lokhu kulungile. Chofoza Yebo.

08 ka-15

Ithebula le-Pivot elixhunywe ku-SQL Server Ngombuzo

I-PivotTable ilungele ukuthi ungeze idatha.

Lokhu kubuyela emuva kubhuku lokusebenzela le-Excel 2010 nge-Pivotable engenalutho. Ungabona ukuthi amasimu atholakalayo manje ahlukile futhi ahambelana nezinkambu kumbuzo we-SQL. Manje singakwazi ukuqala ukwengeza amasimu ku-PivotTable.

09 ka-15

Engeza amasimu kuya ku-Pivot Table

Engeza amasimu ku-PivotTable.

Kuloluhlu lwe-PivotTable Field, dumisa uMkhiqizoCategory endaweni yendawo yamaLebhu, i-OrderDate kuya endaweni yamalebula wekholomu naku-TotalDue kuya ku-Values endaweni. Isithombe sibonisa imiphumela. Njengoba ungabona, insimu yosuku idethi ngayinye ukuze i-PivotTable idale ikholomu yosuku ngalunye oluyingqayizivele. Ngenhlanhla, i-Excel 2010 inezinye ezakhiwe emisebenzini ukuze zisisize ukuhlela amasimu wosuku.

10 kwangu-15

Engeza Ukuqoqwa Kwamasimu Wezinsuku

Engeza amaqembu wezinkambu zenkambu yosuku.

Umsebenzi weBandla uvumela ukuthi sihlele izinsuku zibe yiminyaka, izinyanga, izindlu, njll. Lokhu kuzosiza ukufingqa idatha futhi kwenze kube lula ngomsebenzisi ukuthi asebenze nayo. Chofoza ngakwesokudla kunye yamakhanda ekholomu wedethi bese ukhetha Iqembu elikhuphula ifomu lokuBamba.

11 kwangu-15

Khetha Ukuqoqa Ngezimiso

Khetha izinto zokuqoqa kwenkambu yosuku.

Kuye ngokuthi hlobo luni idatha oqoqa ngayo, ifomu lizobonakala lihlukile kancane. I-Excel 2010 ikuvumela ukuba uhlele izinsuku, izinombolo kanye nedatha ekhethiwe yombhalo. Sihlela i-OrderDate kulokhu okufundiswa ngakho ifomu lizobonisa okukhethwa kukho okuphathelene namacembu wamanje.

Chofoza ku-Months and Years bese uchofoza OK.

12 kwangu-15

Ithebula le-Pivot Elihlanganiswe Ngaminyaka Nenyanga

Izinsuku zosuku zihlanganiswe iminyaka nezinyanga.

Njengoba ungabona esithombeni ngenhla, idatha ihlanganiswe ngonyaka kuqala nangenyanga. Ngamunye unesibonakaliso sokuhlanganisa nesincane esivumela ukuthi ukwandise futhi ugole kuye ngokuthi ufisa ukubona kanjani idatha.

Kuleli phuzu, i-PivotTable iyasiza kakhulu. Yonke yamasimu ingahlungwa kodwa inkinga ayikho into ebonakalayo mayelana nesimo samanje sezinhlanzi. Futhi, kuthatha ukuchofoza okumbalwa ukuze ushintshe umbono.

13 kwangu-15

Faka i-Slicer (Okusha ku-Excel 2010)

Engeza ama-Slic to Pivotable.

I-Slicers yintsha ku-Excel 2010. I-Slicers ngokuyisisekelo iyalingana nokubeka ngokucacile izihlungi zezinkambu ezikhona bese udala izihlungi ze-Report uma ngabe into ofuna ukuyihlunga kuyo ayikho embukisweni wePivotTable wamanje. Le nto enhle mayelana ne-Slicers kuba lula kakhulu kumsebenzisi ukushintsha ukubuka kwedatha ku-PivotTable kanye nokunikeza izinkomba ezibukwayo ngokuqondene nesimo samanje sezihlungi.

Ukufaka ama-Slicers, chofoza kuthebhu Izinketho bese uchofoze ku-Faka isilawuli kusigaba soHlelo nokuhlunga. Khetha I-Insert Slicer evula ifomu le-Insert Slicers. Hlola izinsimu eziningi njengoba ufuna ukutholakala. Esikhathini sethu, ngangeze iminyaka, i-CountryRegionName ne-ProductCategory. kungase kudingeke ukuthi ubeke ama-Slicers lapho ufuna khona. Ngokuzenzakalelayo, zonke izindinganiso zikhethiwe okusho ukuthi akukho zihlungi ezisetshenzisiwe.

14 kwangu-15

Ithebula le-Pivot elinama-Friendly Slicers

I-Slicers yenza kube lula kubasebenzisi ukuhlunga i-PivotTables.

Njengoba ungabona, i-Slicers ibonisa yonke idatha njengoba ikhethiwe. Kusobala kakhulu kumsebenzisi ukuthi iyiphi idatha ekubonweni kwamanje kwe-PivotTable.

15 kwangu-15

Khetha Amanani Kusuka Kulayini Yiziphi I-Pivot Table Yokubuyekezwa

Khetha izinhlanganisela ze-Slicers ukushintsha ukubuka kwedatha.

Chofoza kumhlanganisela ahlukahlukene wezindinganiso ubone ukuthi umbono wePivotTable ushintsho kanjani. Ungasebenzisa ukuchofoza kukaMicrosoft okujwayelekile kuma-Slicers okusho ukuthi uma ungasebenzisa i-Control + Chofoza ukuze ukhethe amanani amaningi noma Shift + Chofoza ukuze ukhethe ububanzi bamanani. I-Slicer ngayinye ibonisa izindinganiso ezikhethiwe ezenza kube sobala ukuthi isimo sePivotTable sinikwe ngokuhlunga. Ungashintsha izitayela ze-Slicers uma ufuna ngokuchofoza ku-Quick Styles wehla ngaphansi kwesigaba se-Slicer kwethebhu yezinketho.

Ukwethulwa kwe-Slicers kusithuthukise ngempela ukusebenza kwe-PivotTables futhi kususwe kakhulu i-Excel 2010 ekubeni yithuluzi lobuchwepheshe bebhizinisi lobuchwepheshe. I-PivotTables ithuthuke kancane ku-Excel 2010 futhi uma ihlangene neWindowsPivot entsha idala imvelo ephezulu yokuhlaziya ukusebenza.