01 ngo-15
Isiphumo sokugqibela
Kukho ikhefu phakathi kweMicrosoft Excel kunye neeplani ze-intelligence zebhanki eziphezulu (BI) iminyaka emininzi. Ukuphucula i-Pivot Table ye-Microsoft Excel 2010 kunye nezinye iimpawu zeBI zenze umncintiswano wangempela kwi-BI yoshishino. I-Excel isetyenziselwe ukuhlalutya kwe-standalone kunye nesixhobo esisisigxina sokuba wonke umntu athumele iingxelo zawo zokugqibela. Ubungcali boshishino bezobugcisa bugcinwe ngokusesikweni kwi-SAS, Business Objects kunye neSAP.
I-Microsoft Excel 2010 (kunye ne-Excel 2010 ye-Pivot Table) kunye ne-SQL Server 2008 R2, SharePoint 2010 kunye ne-Microsoft Excel 2010 eyongezelelweyo "ene-PowerPivot" ibangele isicombululo esiphambili sezobugcisa kunye nesisombululo sokunika ingxelo.
Le tutorial iquka imeko ehambelana ngqo ne-Excel 2010 PivotTable exhunywe kwiziko leSQL Server 2008 R2 isebenzisa umbuzo olula we-SQL. Ndisebenzisa ii-Slicers ukuhlunga okubonakalayo entsha kwi-Excel 2010. Ndiya kubandakanya iindlela ezininzi ze-BI ezisetyenziswayo usebenzisa ii-Data Analysis Expressions (DAX) kwi-PowerPivot ye-Excel 2010 kungekudala. Ukukhululwa kweMicrosoft Excel 2010 kwangoku kunokubonelela ngexabiso langempela kuluntu lwakho lomsebenzisi.
02 we-15
Faka iTable Table
Ungayifaka Ithebhile yePivot kwincwadi entsha ye-Excel. Unokufuna ukuqwalasela ukubeka isikhombisi sakho phantsi kwemigca embalwa ukusuka phezulu. Oku kuya kukunika indawo yenkcazelo yekhanda okanye inkampani xa ubelana ngeshidi lomsebenzi okanye uyiprinte.
- Vula incwadi entsha ye-Excel 2010 kwaye ucofe kwiseli apho ufuna ikhoneni eliphezulu lasekhohlo lePivot Table.
- Cofa kwi-Insert ithebhu kwaye nqakraza kwehla kwePivotTable kwiCandelo leTables. Khetha i-PivotTable. Oku kuza kuqulunqa ifayile yokudala ifayile yePivotTable.
03 we-15
Xhuma i-Pivot Table ukuya kwi-SQL Server (okanye enye i-Database)
I-Excel 2010 inokufumana idatha kwii- RDBMS ezinkulu (Abaxhasi beZiko loLawulo lweDatabase) . Abaqhubi be-SQL Server kufuneka bafumane ukuxhunywa ngokungafi. Kodwa zonke i-software ezinkulu ze-database zenza i-ODBC (Vula i-Database Connection) ukuba bakuvumele ukuba wenze uxhumano. Hlola iwebhusayithi yabo ukuba ufuna ukukhupha abaqhubi be-ODBC.
Kwimeko yolu qeqesho, ndidibanisa neSQL Server 2008 R2 (i-SQL Express ezamahala version).
- A - Yakha ifom yePivotTable yindlela yokuqala ekudaleni uxhulumaniso kwiSQL Server. Khetha "Sebenzisa umthombo wedatha wangaphandle" kwaye nqakraza kwiChotho yokuQhagamshelana. Shiya indawo apho iPivot Table iya kufakwa ngaphandle kokuba ufuna ukudala iphepha lokusebenzela elitsha kwaye ulibeke apho.
- B - Uxhumo olukhoyo lu dwelisa nayiphi na inxibelelwano kwincwadi yokusebenza yangoku, kwikhompyutheni yakho kunye nenethiwekhi okhoyo ngoku. Ukudibanisa okukhoyo kunene nje iifayile zetekisi kunye nolwazi lokuxhamla olufunekayo ukufikelela kumthombo othile wedatha. Kwimeko yethu, siya kudala umthombo omtsha wedatha. Cofa kwi-Browse for More inkinobho.
- C - Cofa kwiNqakraza yomthombo omtsha uza kuqalisa iWeb Connection Wizard.
- D - Khetha iMicrosoft SQL Server kwaye nqakraza Okulandelayo.
- E-Faka igama lomncedisi kwaye ungene kwiziqinisekiso. Khetha indlela efanelekileyo yokuqinisekisa. Ukuba awuqinisekanga yindlela oyisebenzisayo, qhagamshelana nomlawuli wakho wedatha.
- Sebenzisa i-Windows Ukuqinisekiswa: Le ndlela isebenzisa inethiwekhi yakho ukungena ngemvume ukufikelela kwi-SQL Server yolwazi.
- Sebenzisa igama elilandelayo lomsebenzisi kunye nephasiwedi: Le ndlela isetyenziswa xa i-SQL Server iqulunqwe ngabasebenzisi be-standalone ukufikelela kwiinkcukacha zolwazi.
- F - Kule nyathelo, siya kukhetha itafile njengendawo yokubeka indawo. Siya kutshintsha itafile ngeSQL yesiqhelo esiza kubonelela ngqo idatha esiyifunayo kwiincwadi zethu ze-Excel.
- Khetha isiseko oza kudibana kuyo. Kulo mzekelo, sidibanisa kwisampula ye-AdventureWorks yesampula enikezelwe yiMicrosoft. Hlola ukuxhuma kwitafile ethile uze ukhethe itafile yokuqala. Khumbula, asiyi kubuyisa idatha esuka kule tafile.
- Cofa uqhafaza oya kuvalwa idilesi uze ubuyele kule ncwadi. Siza kuthungula itafile yendawo yokugcina umbuzo wethu wesiqhelo SQL.
Uya kubuyiselwa kwifom yePivotTable (A). Chofoza OK.
04 we-15
I-Pivot Table Idibaniswe ngokukhawuleza kwi-Table ye-SQL
Ngeli nqanaba, udibanise kwitafile yendawo yokuhlala kwaye unayo i-PivotTable engenanto. Unokubona ngakwesobunxele kwakuyi-PivotTable kwaye kuya kunene kukho uluhlu lwemimandla ekhoyo.
05 we-15
Vula iimpahla zokuxhuma
Ngaphambi kokuba siqale ukukhetha idatha yePivotTable, kufuneka sitshintshele uxhulumaniso kwiSQL. Qinisekisa ukuba ukhetha kwiThayikheto lokukhetha kwaye qhosha kwiShintsho loLwazi lweMithombo phantsi kweCandelo leDatha. Khetha iipropati zokuXhumana.
Oku kuvelisa ifom yeZakhiwo zeConnection. Cofa kwiTebhayibhile ithebhu. Oku kukubonisa ulwazi loxhumano lwenxibelelwano olukhoyo kwiSQL Server. Nangona ikhupha ifayile yokuxhuma, idatha imene ifakwe kwispredishithi.
06 we-15
Ukuhlaziywa kweZakhiwo zokuXhuma kunye nombuzo
Shintsha Uhlobo loLawulo ukusuka kwiThebula ukuya kwi-SQL uze ubhale ngaphezulu i-Text Text ekhoyo kunye ne-SQL yakho. Nantsi imbuzo endiyidalwe kwi-database ye-AdventureWorks yesampula:
SELECT Sales.SalesOrderHeader.SalesOrderID,
Ukuthengisa.SalesOrderHeader.OrderDate,
Sales.SalesOrderHeader.ShipDate,
Sales.SalesOrderHeader.Status,
Ukuthengisa.SalesOrderHeader.SubTotal,
Iintengiso.IziXhoboOrderHeader.TaxAmt,
Ukuthengisa.SalesOrderHeader.Freight,
I-SalesSalesOrderHeader.TotalDue,
Ukuthengisa.SalesOrderDetail.SalesOrderDetailID,
Iintengiso.
Iintengiso.IiNkcukachaDawuloDawulo.UnxibelelwanoKu
I-SalesSalesOrderDetail.LineTotal,
Ukuvelisa.Imveliso.Name,
Ukuthengisa.KuMntu oMnyeUkuCwangcisa.
Ukuthengisa.Ikhamera.CustomerType,
Ukuvelisa.Ukuvelisa.ListPrice,
Ukuvelisa.Ukuvelisa.ProductLine,
Ukuvelisa.I-ProductivitySubcategory.Name AS ProductCategory
UKUSUKA KWE-Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Ukuthengisa.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Ukuveliswa.Kuvelisa kwi-Sales.SalesOrderDetail.ProductID =
Ukuvelisa.I-Productivity.I -ProductID INNER JOIN Sales.I-Factory ON
Ukuthengisa.SalesOrderHeader.CustomerID = Ukuthengisa.I-Factor.CustomerID NO
I-Sales.SalesOrderHeader.CustomerID = I-Sales.Customer.CustomerID INNER JOIN
Ukuthengisa.KuMntu ngamnyeUkuthengisa kwi-Sales.Customer.CustomerID =
Ukuthengisa.KuMntu ngamnyeCustomer.CustomerID INNER JOIN
Ukuvelisa.I-ProductivitySubcategory KWI-Production.Product.ProductSubcategoryID =
Ukuvelisa.I-ProductivitySubcategory.ProductSubcategoryID
Chofoza OK.
07 we-15
Fumana isixwayiso soxhumo
Uya kufumana ibhokisi yencoko yokubaluleka yeMicrosoft Excel. Oku kungenxa yokuba sitshintshe ulwazi loxhumo. Xa sasiqalile ukudala uxhumano, silondoloze ulwazi lwangaphandle .Fayile yeFODC (i-ODBC Data Connection). Idatha ebhokisini yomsebenzi yayifana nefayili ye .ODC de satshintshile ukusuka kwindlela yolawulo lwetafile kuhlobo lwemiyalelo ye-SQL kwiNqanaba # 6. Isilumkiso sikuxelela ukuba idatha ayisekho ukuvumelanisa kwaye ukubhekisela kwifayile yangaphandle kwincwadi yokusebenza iya kususwa. Oku kulungile. Cofa Ewe.
08 we-15
Ithebula lePivot elixhunywe kwiSQL Server kunye nombuzo
Oku kubuyela kwi-Excel 2010 yencwadi kunye neVivotable engenanto. Uyabona ukuba imimandla ekhoyo iyahlukileyo kwaye iyahambelana nemimandla kwisicelo se-SQL. Ngoku sinokuqala ukufaka amasimi kwi-PivotTable.
09 we-15
Yongeza iMida ukuya kwiPivot Table
KwisiHlabathi seNgqungquthela ye-PivotTable, dibisa uMkhiqizoCategory kwindawo yoLebhanzo yendawo, Hlaziya kwiNgingqi yeeyileyile kunye ne-TotalDue to Values endaweni. Umfanekiso ubonisa iziphumo. Njengoko uyakubona, intsimi yosuku inomnye imihla ukuze i-PivotTable idale ikholomu yosuku olulodwa. Ngethamsanqa, i-Excel 2010 inamanye akhiwe kwimisebenzi ukuze asincede siququzelele iindawo zomhla.
10 we-15
Yongeza Ubuthelelo Lwemihlaba Yomhla
Umsebenzi wokuBamba uvumela ukuba sihlele imihla ibe yiminyaka, iinyanga, iigota, njl. Oku kuya kusinceda ukufingqa idatha kunye nokwenza kube lula ukuba umsebenzisi asebenzisane nayo. Ukucofa ngakwesokudla kwimihla yamaphepha ekholomu uze ukhethe Iqela elikhupha ifomu lokuBamba.
11 we-15
Khetha Ukwahlula ngeMilinganiselo
Ngokuxhomekeke kuhlobo lwenkcukacha oluhlulayo, ifom iya kubonakala ihlukile. Excel 2010 ikuvumela ukuba uqoke imihla, amanani kunye nedatha ekhethiweyo yombhalo. Siqulunqa i-OrderDate kule khokelo ukuze le fomu ibonise iinketho ezinxulumene namaqela omhla.
Cofa kwiMyanga kunye neMinyaka uze uqakraze.
12 kwi-15
I-Pivot Itafile ehlanganiswe yiminyaka kunye neenyanga
Njengoko unokubona kumfanekiso ongentla, idatha iqela ngonyaka kuqala kwaye ngenyanga. Ngamnye unombonakaliso wokudibanisa kunye nokunciphisa okuvumela ukuba ukwandise kwaye udibane ngokuxhomekeka kwindlela ofuna ukuyijonga ngayo idatha.
Kule ngongoma, i-PivotTable iluncedo kakhulu. Ngamanye amacandelo angacatshungulwa kodwa ingxaki ayikho into ebonakalayo ngokubhekiselele kwimeko yangoku yefayile. Kwakhona, kuthatha iqhosha elincinane ukutshintsha umbono.
13 we-15
Faka iSlicer (entsha kwi-Excel 2010)
Iifayile zintsha kwi-Excel 2010. Iifayile zilingana nezicwangciso zokubeka izihlungi zeenkalo ezikhoyo kunye nokudala i-Report Filters kwimeko apho into ofuna ukuyicoca kuyo ayikho kwimbono ekhoyo yangoku. Le nto enhle malunga neSlicers kuba lula kakhulu kumsebenzisi ukutshintsha umbono we-data kwi-PivotTable kwakunye nokubonelela izibonakaliso ezibonakalayo malunga nelo xesha lendawo yokuhlunga.
Ukufaka ii-Slicers, nqakraza kwiKhoto lokukhethwa kwaye nqakraza kwi-Faka iSlicer kwiCandelo loHlelo & Uhluzi. Khetha Ifayile yokufaka ifayile evula ifom ye-Insert Slicers. Khangela amaninzi emasimini njengoko ufuna ukufumaneka. Ngokomzekelo wethu, ndongeze iminyaka, iRegionRegionName kunye neMvelisoCategory. unokufuneka ubeke iiSlicers apho ufuna khona. Ngokungagqibekanga, zonke ixabiso likhethiweyo elithetha ukuba akukho zihlungi ezisetyenzisiweyo.
14 we-15
I-Pivot Table kunye neeSlicers Friendly Slicers
15 we-15
Khetha Iimilinganiselo ezivela kwiiSlicers zeziphi iziHlomelo zePivot Table
Cofa kwiintlobo ezahlukeneyo zexabiso kwaye ubone indlela ukujonga ngayo iinguqulelo zePivotTable. Ungasebenzisa iMicrosoft ngokukrokra kwiiSlicers zithetha ukuba ukuba ungasebenzisa Ulawulo + Cofa ukukhetha amanani amaninzi okanye Shift + Cofa ukuze ukhethe uluhlu lwexabiso. I-Slicer nganye ibonisa ixabiso elikhethiweyo elenza kucacile ukuba yintoni imeko yePivotTable ngokwemiqathango yokuhlunga. Ungatshintsha izitayela zeeSlic if ufuna ngokuchofoza kwiStayile zeKhuseleko phantsi kweCandelo leSlicer kwiTy Options.
Ukuqaliswa kwee-Slicers kuye kwaphucula ngokwenene ukusetyenziswa kwePivotTables kwaye kuye kwasusa i-Excel 2010 ngokusondele ngakumbi ekubeni yintsimbi yezobugcisa zobungcali. I-PivotTables iphucule kakhulu kwi-Excel 2010 kwaye xa idibaniswe ne-PowerPivot entsha idala indawo ephezulu yokuhlaziywa komsebenzi.