01 ho ea ho 15
Qetello ea ho qetela
Ho bile le karohano e pakeng tsa Microsoft Excel le liforomo tsa bo-rakhoebo tse phahameng ka ho fetisisa (BI) tsa lilemo tse ngata. Matšeliso a Microsoft Excel 2010 a Pivot Table hammoho le likarolo tse ling tse peli tsa BI a entse hore e be motlatsi oa sebele oa BI ea khoebo. Excel e 'nile ea sebelisoa bakeng sa tlhahlobo ea setone le sesebelisoa se tloaelehileng seo motho e mong le e mong a se romelang litlaleho tsa bona tsa ho qetela. Litsebi tsa khoebo tsa litsebi li 'nile tsa bolokeloa ho rata SAS, Business Objects le SAP.
Microsoft Excel 2010 (e nang le Excel 2010 Pivot Table) hammoho le SQL Server 2008 R2, SharePoint 2010 le "FreePivot" ea mahala ea Microsoft Excel 2010 e entse hore ho be le tharollo e phahameng ea boitsebiso ba khoebo le tlaleho ea tlaleho.
Thupelo ena e akaretsa boemo bo hlakileng ka pele ho Excel 2010 PivotTable e hokahaneng le dintlha tsa SQL Server 2008 R2 e sebelisa SQL e bonolo lipotso. Ke boetse ke sebelisa Slicers bakeng sa ho hlophisa pono e ncha ho Excel 2010. Ke tla koahela mekhoa e mengata ea BI e sebelisang Litlhaloso tsa Data Analysis (DAX) ka PowerPivot bakeng sa Excel 2010 haufinyane. Phetoho ena ea morao-rao ea Microsoft Excel 2010 e ka fana ka bohlokoa ba sebele ho sechaba sa hau sa basebetsi.
02 ho ea ho 15
Kenya Pivot Table
U ka kenya Letlapa la Pivot bukeng e ncha kapa e teng ea Excel. U ka 'na ua batla ho nahana ka ho beha mohalaleli oa hau tlase mela e' maloa holimo. Sena se tla u fa sebaka sa tlhahisoleseding ea hlooho kapa ea k'hamphani haeba u arolelana leqephe kapa ue hatise.
- Bula buka ea mosebetsi e ncha kapa e teng ea Excel 2010 'me u tobetse seleng eo u batlang ho ea holimo ka holimo le letšehali la Letlapa la Pivot.
- Tobetsa ho kenya Taba mme u tobetse ho theoha ho PivotTable ho karolo ea Mathala. Khetha PivotTable. Sena se tla qala ho bopa fomu ea dialog ea PivotTable.
03 ho ea ho 15
Kopanya Pivot Table ho SQL Server (kapa Other Database)
Excel 2010 e ka fumana boitsebiso bo tsoang ho bohle ba baholo ba RDBMS (Relational Database Management System) . Bakhanni ba SQL Server ba lokela ho ba teng bakeng sa kgokahano ka ho sa feleng. Empa lisebelisoa tsohle tse kholo tsa database li etsa bakhanni ba ODBC (Open Database Connectivity) ho u lumella hore u kopanye. Sheba liwebsaete tsa bona haeba u hloka ho jarolla bakhanni ba ODBC.
Tabeng ea thupelo ena, ke ikamahanya le SQL Server 2008 R2 (SQL Express mahala mahala).
- A - The Forward PivotTable fomu ke mofuta oa pele ho theha khokahano ho SQL Server. Khetha "Sebelisa mohloli oa data o kantle" 'me u tobetsa konopo ea Khetho ea Khetho. Tlohela moo sebaka sa Pivot Table se tlang ho behoa teng ntle le hore u batle ho theha leqephe le lecha la mosebetsi ebe u le beha moo.
- B - The Connections e teng e thathamisa mekhoa efe kapa efe e amanang le buka ea hona joale, khomphuteng ea hau le marang-rang ao u kopanetsoeng hona joale. Likhokahanyo tse teng li hlile li mpa li ngolla melaetsa e nang le boitsebiso bo amanang le ho fumana mohloli o itseng oa data. Tabeng ea rona, re tla etsa mohloli o mocha oa boitsebiso. Tobetsa ho Browse bakeng sa konopo e eketsehileng.
- C - Tobetsa konopo ea Mohloli o Mocha o tla qala Sesebelisoa sa Tšebeliso ea Data.
- D - Khetha Microsoft SQL Server ebe u tobetsa E latelang.
- E - kenya lebitso la Seva le ho kena lits'ebetsong. Khetha mokhoa o nepahetseng oa ho netefatsa. Haeba o sa tiisehe hore na mokhoa ofe o ka o sebelisang, ikopanya le mookameli oa hau ea database.
- Sebelisa Bopaki ba Windows: Mokhoa ona o sebelisa marang-rang oa hau ho kena ho fihlela litabeng tsa SQL Server.
- Sebelisa Lebitso la Mosebedisi le Motlolososa: Mokhoa ona o sebelisoa ha SQL Server e hlophisitsoe ka basebelisi ba standalone ho fihlela boitsebiso ba marang-rang.
- F - Ka mohato ona, re tla khetha tafole e le moahi oa libaka. Re tla tlisa sebaka sa tafole ka setso sa SQL se tla fana hantle ka data eo re e batlang ho buka ea rona ea Excel.
- Khetha database eo u tla ikamahanya le eona. Mohlala ona, re ikopanya le sampuli ea semosa sa AdventureWorks e fanoang ke Microsoft. Sheba Connect ho tafoleng e khethehileng 'me u khethe tafoleng ea pele. Hopola, re ke ke ra fumana data ho tloha tafoleng ena.
- Tobetsa Qetella e tla koala wizard mme e khutlisetse bukeng ea mosebetsi. Re tla be re tlosa tafole ea batho ba sebetsang bakeng sa lipotso tsa rona tsa SQL.
U tla khutlisetsoa foromong ea PivotTable (A). Tlanya OK.
04 ea 15
Leqephe la Pivot le kopantsoe ka nakoana ho SQL Table
Hona joale, u ikopantseng tafoleng ea batho ba nang le sebaka 'me u na le PivotTable e se nang letho. U ka bona ka ho le letšehali PivotTable e tla ba teng 'me ka ho le letona ho na le lethathamo la masimo a teng.
05 ea 15
Bula Matlo a Kopano
Pele re qala ho khetha dintlha tsa PivotTable, re lokela ho fetola kgokahanyo ho potso ea SQL. Etsa bonnete ba hore u ka lebokoseng la Dikgetho mme o tobetsa ho Fetola Mohloli oa Mohloli oa theoha ho tloha Karolong ea Data. Khetha Matlo a Kopano.
Sena se hlahisa foromo ea Matlotlo a Connection. Tlanya ho Tlhaloso ea Tlhaloso. Sena se bonts'a tlhahisoleseding ya kgokahanyo bakeng sa kgokahanyo ea hona joale ho SQL Server. Ha e ntse e bua ka file ea ho kopanya, data e hlile e kenngoa ka spreadsheet.
06 ea 15
Ntlafatso ea Matlotlo a Kopano le Potso
Fetola mofuta oa litaelo ho tloha tafoleng ho ea ho SQL le ho ngola Text Text e teng le SQL ea Query ea hau. Potso ena eo ke e entseng e tsoa ho database ea sample ea 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,
Tlhahiso.Product.Name,
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Phatlalatso.Product.ListPrice,
Production.Product.ProductLine,
Tlhahiso.ProductSubcategory.Name AS ProductCategory
FROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =
Tlhahiso.Product.ProductID INNER JOIN Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID LE
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
Tlhahiso.ProductSubcategory ON Phatlalatso.Product.ProductSubcategoryID =
Phatlalatso.ProductSubcategory.ProductSubcategoryID
Tlanya OK.
07 ho ea ho 15
Amohela Temoso ea Kopano
U tla fumana lebokose la lebokose la boitsebiso la Microsoft Excel. Sena ke hobane re fetohile tlhahisoleseding ea kgokahanyo. Ha re qalile ho theha marang-rang, e bolokile tlhahisoleseding e ka ntle ho file .ODC file (ODBC Data Connection). Boitsebiso bukeng ea mosebetsi bo ne bo lekana le faele ea .ODC ho fihlela re fetohile ho tloha tafoleng ea taelo ea litaelo ho mofuta oa taelo ea SQL ho Mohato # 6. Tlhokomeliso e u bolella hore data ha e sa lumellana le ho fetisetsoa ho faele e kantle ho bukeng ea buka e tla tlosoa. Sena se lokile. Tlanya E.
08 ho ea ho 15
Letlapa la Pivot le hokahanngoa le SQL Server ka Potso
Sena se khutlela bukeng ea mosebetsi oa Excel 2010 ka PivotTable e se nang letho. U ka bona hore masimo a teng hona joale a fapane 'me a lumellana le masimo a potso ea SQL. Hona joale re ka qala ho eketsa masimo ho PivotTable.
09 ea 15
Kenya Masimo ho Pivot Table
Phaleng ea PivotTable Field, hula ProductCategory sebakeng sa Row Labels, OrderDate ho Li-Labels sebaka le TotalDue ho Maemo. Setšoantšo se bontša liphello. Joalokaha u ka bona, tšimo ea letsatsi e na le motho ea fanang ka nako e le hore PivotTable e bōpe mohala oa letsatsi ka leng le ikhethang. Ka lehlohonolo, Excel 2010 e na le ba bang ba hahiloeng mesebetsing ho re thusa ho hlophisa masimo a letsatsi.
10 ho ea ho 15
Kenyelletsa Sehlopha sa Masimo a Letsatsi
Mosebetsi oa Sehlopha o re lumella hore re hlophise matsatsi ka lilemo, likhoeli, likarolo, joalo-joalo. Sena se tla thusa ho akaretsa lintlha le ho etsa hore ho be bonolo hore motho a sebelisane le eona. Ho tobetsa ka ho toba ho e 'ngoe ea letsatsi la lihlooho tsa sehlooho ebe u khetha Sehlopha se hlahisang foromo ea Sehlopha.
11 ho ea ho 15
Khetha Kopano ka Melao ea Boitsoaro
Ho itšetlehile ka mofuta oa dintlha tseo u li hlophisitseng, mofuta ona o tla sheba o fapaneng haholo. Excel 2010 eu lumella ho qeta matsatsi, manane le lintlha tse khethiloeng tsa mongolo. Re hlophisa OrderDate thutong ena e le hore foromo e bontše dikgetho tse amanang le lihlopha tsa letsatsi.
Tobetsa ka Likhoeli le Lilemo mme o tobetsa OK.
12 ho ea ho 15
Letlapa la Pivot le Hlahisoang ke Lilemo le Likhoeli
Joalokaha u ka bona setšoantšong se ka holimo, data e arotsoe ka selemo pele le ka khoeli. E 'ngoe le e' ngoe e na le letšoao la ho eketsa le le fokolang le u lumellang ho atolosa le ho oa ho ea ka hore na u lakatsa ho bona boitsebiso joang.
Ka nako ena, PivotTable e molemo haholo. E mong le e mong oa masimo a ka tlhotliloeng empa bothata ha bo na leseli le bontšang boemo ba hona joale ba lihlopha. Hape, ho nka ho penya tse 'maloa ho fetola pono.
13 ho ea ho 15
Kenya Slicer (Ncha ho Excel 2010)
Li-slicers li ncha ho Excel 2010. Li-slicers ha e le hantle li lekanang le lihlopha tsa mahlo a li-fields tse teng le ho hlahisa Lipatlisiso tsa Tlaleho tabeng ea hore ntho eo u batlang ho e hloekisa ha e sheba pono ea PivotTable. Ntho ena e ntle ka Slicers ke ho ba bonolo haholo hore mosebeletsi a fetole pono ea data ka PivotTable hammoho le ho fana ka lits'oants'o tsa pono ho boemo ba hona joale ba lihlatso.
Ho kenya Slicers, tobetsa konthong ea Dikgetho mme o tobetsa ho kenya Slicer ho tswa ho Sehlopha sa Mofuta & Sehlopha. Khetha Insert Slicer e bulang foromo ea Insert Slicers. Sheba masimo a mangata ao u batlang ho ba le oona. Mohlala oa rona, ke kenyeletse lilemo, CountryRegionName le ProductCategory. u ka 'na ua tlameha ho beha li-Slicers moo u li batlang. Ka kakaretso, litekanyetso tsohle li khethiloe tse bolelang hore ha ho lihlatso tse sebelisitsoeng.
14 ho ea ho 15
Leqephe la Pivot le Li-Slicers tsa Motsoalle
15 ho ea ho 15
Khetha Melao e Tsoang ho Slicers Ke lintlha life tse hlahang Pivot Table
Tobetsa ka litekanyetso tse fapaneng tsa litekanyetso mme u bone kamoo pono ea PivotTable e fetohang kateng. U ka sebelisa mokhoa o tloaelehileng oa Microsoft o tobileng ho Slicers e bolelang hore haeba u ka sebelisa Control + Tobetsa ho khetha litekanyetso tse ngata kapa Shift + Tobetsa ho khetha mefuta e mengata ea litekanyetso. Slicer e 'ngoe le e' ngoe e bonts'a litekanyetso tse khethiloeng tse etsang hore e hlake hantle hore na boemo ba PivotTable bo hokae ka litlhaloso. U ka fetola mekhoa ea li-Slicers haeba u batla ka ho tobetsa ho potlakela ho sebelisa li-Quick Styles karolong ea Slicer ea Options tab.
Ho kenyelletsoa ha Slicers ho hlile ho ntlafalitse ho sebelisoa ha PivotTables 'me ho susumelitse Excel 2010 ho ba haufi haholo le ho ba setsebi sa botsebi ba khoebo. PivotTables e ntlafalitse haholo ho Excel 2010 mme ha e kopanngoa le PowerPivot e ncha e hlahisa tikoloho e phahameng haholo ea lisebelisoa.