Yadda za a daidaita Shirye-shiryen Pivot na 2010

01 daga 15

Sakamakon karshe

An sami rata tsakanin Microsoft Excel da kuma manyan kamfanonin kasuwanci (BI) na shekaru masu yawa. Abubuwan haɓaka kayan haɓaka na Microsoft Excel 2010 na Pivot tare da wasu wasu siffofin BI sun sanya shi ainihin gasa ga BI. An yi amfani da Excel ta hanyar al'ada don bincike mai tsafta da kuma kayan aiki na kowa da kowa ke fitar da rahotanni na karshe. Ma'aikatar kasuwanci ta sana'a ta tanada ta al'ada don irin SAS, Kasuwancin Kasuwanci da SAP.

Microsoft Excel 2010 (tare da Siffar Pivot na Excel 2010) tare da SQL Server 2008 R2, SharePoint 2010 da kuma Microsoft Excel 2010 da aka ƙaddara a kan "PowerPivot" ya haifar da kyakkyawan bayani ga harkokin kasuwancin da ke bada rahoto.

Wannan koyaswar yana rufe wani labari na gaba mai sauƙi tare da Excel 2010 PivotTable da aka haɗa zuwa SQL Server 2008 R2 database ta yin amfani da sauki tambaya na SQL. Har ila yau ina amfani da Slicers don dubawa na gani wanda yake sabo a Excel 2010. Zan rufe fasaha na BI masu ƙwarewa ta amfani da Ma'anar Tattaunawa na Data (DAX) a PowerPivot don Excel 2010 a nan gaba. Wannan sabuntawa na Microsoft Excel 2010 na iya samar da ƙimar gaske ga maɓallin mai amfani.

02 na 15

Saka Rubutun Pivot

Zaka iya saka Rubutun Maɓallin cikin sabon littafi na Excel. Kuna so su duba matsayinka na siginanka zuwa wasu layuka daga saman. Wannan zai ba ka sarari don shugabanci ko bayanin kamfanin idan ka raba aikin aiki ko buga shi.

• Bude sabon littafi na Excel 2010 wanda ya kasance a yanzu kuma danna kan tantanin salula inda kake son saman hagu na kusurwar Pivot Table don zama.
• Danna kan Saka shafin kuma danna maɓallin PivotTable saukewa a cikin Sassan Tables. Zabi PivotTable. Wannan zai kaddamar da nau'in maganganu na PivotTable.

03 na 15

Haɗa Pivot Table zuwa SQL Server (ko Sauran Database)

Excel 2010 na iya dawo da bayanan daga duk manyan kamfanonin RDBMS (Relational Database Management System) . Dole ne direbobi na SQL Server su kasance suna samuwa don haɗin ta hanyar tsoho. Amma duk manyan software na ƙaddamarwa suna sa ODBC (Open Database Connectivity) direbobi su ba ka damar yin haɗin. Duba shafin yanar gizon su idan kana buƙatar sauke direbobi ODBC.

A cikin yanayin wannan koyawa, Ina haɗawa zuwa SQL Server 2008 R2 (SQL Express kyauta).

• A - Da Halitta PivotTable siffan shine farkon tsari a samar da haɗin zuwa SQL Server. Zaɓi "Yi amfani da tushen bayanan bayanan" kuma danna kan Maɓallin Hanya. Ka bar wurin da za a sanya Table na Pivot sai dai idan kana so ka ƙirƙiri sabon takardun aiki ka ajiye shi a can.
• B - Harkokin da ake ciki sun tsara jerin duk wani haɗi a cikin littafi na yanzu, a kwamfutarka da kuma cibiyar sadarwar da kake haɗuwa a yanzu. Abubuwan da ke gudana sun kasance ainihin fayilolin rubutu tare da bayanin haɗin da ake buƙata don samun dama ga tushen bayanai. A halinmu, za mu kirkiro wani sabon bayanan bayanai. Danna kan Maɓallin Bincike don Ƙari.
• C - Danna kan maɓallin Sabuwar Source zai kaddamar da Wizard Wizard na Data.
• D - Zabi Microsoft SQL Server kuma danna Next.
• E - Shigar da sunan Sunan kuma shiga cikin takardun shaidarka. Zaɓi hanyar da aka dace ta ingantattun bayanai. Idan kun kasance m abin da hanya za ku yi amfani da ita, tuntuɓi mai sarrafa fayil din ku.
  • Yi amfani da Amincin Intanet: Wannan hanya yana amfani da hanyar sadarwar ku don samun dama ga bayanai na SQL Server.
  • Yi amfani da wadannan Sunan mai amfani da Kalmar wucewa: Wannan hanya ana amfani da lokacin da aka saita SQL Server tare da masu amfani standalone don samun damar bayanai.
• F - A wannan mataki, za mu zabi tebur a matsayin mai sanya wurin. Za mu maye gurbin tebur tare da al'ada na SQL wanda zai samar da cikakkun bayanai da muke so a littafin mu na Excel.
  • Zaži bayanan da za a hada da ku. A cikin wannan misali, muna haɗuwa da asusun samfurin AdventureWorks wanda Microsoft ya samar. Duba Haɗuwa zuwa wani tebur na musamman kuma zaɓi teburin farko. Ka tuna, ba za mu dawo da bayanai daga wannan tebur ba.
  • Danna Ƙare wanda zai rufe maye kuma ya mayar da ku zuwa littafin. Za mu fitar da ɗakunan mai ɗaukar hoto don ƙwaƙwalwarmu ta al'ada na SQL.

Za a mayar da ku zuwa Halitta PivotTable (A). Danna Ya yi.

04 na 15

Rubutun Maɓallin Maimaita Haɗi zuwa Haɗakar daftarin SQL

A wannan lokaci, kun haɗawa da teburin mahimmanci kuma kuna da matsala mara kyau PivotTable. Kuna iya gani a hagu ne PivotTable zai kasance kuma a dama akwai jerin samfuran da aka samo.

05 na 15

Bude Properties Connection

Kafin mu fara zaɓar bayanai don PivotTable, muna buƙatar canza haɗin zuwa ga tambayar SQL. Tabbatar cewa kun kasance a kan Zaɓuka Zabuka kuma danna kan Canza Bayanan Bayanan da ke saukewa daga Sashen bayanai. Zabi Properties Properties.

Wannan yana haifar da kamfanonin Connection. Danna kan Maɓallin Shafi. Wannan yana nuna maka bayanin haɗi don haɗuwa na yanzu zuwa SQL Server. Yayinda yake nuni da fayil din haɗi, an sanya bayanai a cikin rubutun.

06 na 15

Sabunta Properties Connection Tare da Tambaya

Canja Dokar Umurnin daga Table zuwa SQL kuma sake rubuta rubutun Umurni na yanzu tare da SQL Query. A nan ne tambayar da na halitta daga AdventureWorks samfurin bayanai:

Sake 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
DAGA 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 DA
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

Danna Ya yi.

07 na 15

Samun Gargaɗi na Haɗi

Za ku sami akwatin maganganu na Warning na Microsoft. Wannan saboda mun canza bayanin haɗin. Lokacin da muka fara haɗin haɗin, ya ajiye bayanin a cikin wani waje na .ODC (ODBC Data Connection). Bayanan da ke cikin littafin aiki daidai yake da fayil na .ODC har sai mun sauya daga nau'in umurni na tebur zuwa nau'i na umurnin SQL a mataki na 6. Gargaɗi yana gaya muku cewa bayanan ba a aiki tare ba kuma ana cirewa zuwa ga fayil na waje a cikin littafin. Wannan shi ne OK. Danna Ee.

08 na 15

Pivot Table da aka haɗa zuwa SQL Server Tare da Tambaya

Wannan yana komawa cikin littafin littafin Excel na 2010 tare da PivotTable maras kyau. Zaka iya ganin cewa filayen da aka samo yanzu sun bambanta kuma sun dace da filayen a cikin tambayar SQL. Zamu iya fara ƙara filayen zuwa PivotTable.

09 na 15

Ƙara Rukunai zuwa Farin Rubutun

A cikin Ƙungiyar Lissafin PivotTable, ja ProductCategory zuwa Yanki na Layi na Layi, Yankin Ƙaƙidar Lissafi zuwa Yanki da TotalDue to Values ​​area. Hoton yana nuna sakamakon. Kamar yadda kake gani, kwanan wata yana da takardun mutum don haka PivotTable ya kirkiro shafi don kowane kwanan wata. Abin farin cikin, Excel 2010 yana da wasu ayyuka a cikin ayyuka don taimakawa mu tsara wurare na kwanan wata.

10 daga 15

Ƙara Rukunin don Kwanan wata

Ayyukan rukuni na ƙyale mu tsara kwanakin cikin shekaru, watanni, bariki, da dai sauransu. Wannan zai taimaka wajen taƙaita bayanan kuma ya sa ya fi sauƙi ga mai amfani don yin hulɗa tare da shi. Danna dama a kan ɗaya daga cikin jigogi na kwanan wata kuma zaɓi Ƙungiya wanda ya kawo nau'in Ƙungiya.

11 daga 15

Zabi Ƙirguwa ta Ƙididdiga

Dangane da irin bayanan da kake haɗuwa, nau'in zai duba kadan. Excel 2010 yana baka dama ka tsara kwanakin, lambobi da bayanan rubutu. Muna haɗaka OrderDate a cikin wannan darasi don haka tsari zai nuna zaɓuɓɓuka da suka shafi rukunin kwanan wata.

Danna Watanni da Kwanni kuma danna Ya yi.

12 daga 15

Ƙungiyar Pivot Kungiya ta Ƙarshe da Watanni

Kamar yadda kake gani a cikin hoton da ke sama, ana tattara rahotannin a shekara ta farko sannan sannan a wata. Kowace yana da alamar da ƙananan alamar da ke ba ka damar fadadawa da rushe dangane da yadda kake son ganin bayanan.

A wannan lokaci, PivotTable yana da amfani sosai. Kowace gonaki za a iya tace amma matsalar bata da wani ra'ayi na gani game da halin yanzu na filtata. Har ila yau, yana daukan dama don danna ra'ayi.

13 daga 15

Saka Slicer (Sabo a Excel 2010)

Slicers ne sabon a cikin Excel 2010. Masu slicer suna da mahimmanci da zazzage saiti na filfofi na filayen da ke ciki da kuma samar da Fitaccen Fassara a cikin yanayin cewa abun da kake so ka dagewa ba a cikin ra'ayin PivotTable na yanzu ba. Wannan abu mai kyau game da Slicers ya zama mai sauƙi ga mai amfani ya canza ra'ayi na bayanan a cikin PivotTable da kuma samar da alamun gani na halin yanzu na filtata.

Don saka Slicers, danna kan Zabuka shafin kuma danna Saka Slicer daga Sashen & Filter. Zaži Sa Slicer wanda ya buɗe da Saka Slicers. Bincika yawancin filayen kamar yadda kake son samuwa. A misali, Na kara da shekaru, CountryRegionName da ProductCategory. zaka iya sanya Slicers inda kake son su. Ta hanyar tsoho, ana zaɓar dukkanin dabi'un wanda ba'a nufin amfani da filtata ba.

14 daga 15

Rubutun Magana tare da Slicers Masu amfani

Kamar yadda kake gani, Slicers nuna duk bayanan da aka zaba. Ya tabbata ga mai amfani daidai abin da bayanai ke cikin ra'ayin yanzu game da PivotTable.

15 daga 15

Zaɓi Ƙimar Daga Slicers Wanne Shafuka Masu Gyara Ɗaukaka

Danna kan abubuwa daban-daban na dabi'u kuma ga yadda ra'ayi na gyaran PivotTable. Zaka iya amfani da maɓallin Microsoft na musamman a cikin Slicers yana nufin cewa idan zaka iya amfani da Control + Click don zaɓar dabi'u mai yawa ko Shift + Danna don zaɓar iyakar dabi'un. Kowace Slicer yana nuna abubuwan da aka zaɓa wanda ya sa ya zama ainihin abin da yanayin PivotTable yake a cikin sharuddan filtura. Za ka iya canza tsarin Slicers idan kana so ta danna kan Quick Styles sauke a Slicer sashe na Zabuka shafin.

Gabatarwa da Slicers ya inganta ingantaccen amfani da PivotTables kuma ya inganta Excel 2010 mafi kusa da kasancewa kayan aiki na sana'a. PivotTables sun inganta sosai a Excel 2010 kuma yayin da aka hade tare da sabon PowerPivot ya haifar da kyakkyawar yanayin nazari.