Kaip konfigūruoti "Excel 2010" jungtines lenteles

by Timas Perdėjas

01 iš 15

Galutinis rezultatas

Tai galutinis šio žingsnio po žingsnio rezultatas - spustelėkite vaizdą, kad pamatytumėte pilno dydžio versiją.

Daugelį metų tarp "Microsoft Excel" ir aukščiausio lygio verslo žvalgybos (BI) platformų buvo atotrūkis. "Microsoft Excel 2010 Pivot Table" patobulinimai kartu su keletu kitų "BI" funkcijų tapo realiu "BI" konkurentu. "Excel" tradiciškai buvo naudojama atskirai analizei ir standartinei priemonei, kurią kiekvienas eksportuoja savo galutines ataskaitas. Profesinė verslo žvalgyba tradiciškai buvo rezervuota SAS, verslo objektų ir SAP patariams.

"Microsoft Excel 2010" (su "Excel 2010" pagrindine lentele) kartu su "SQL Server 2008 R2", "SharePoint 2010" ir nemokama "Microsoft PowerPoint 2010" papildoma programa "PowerPivot" sukūrė aukščiausio lygio verslo žvalgybos ir ataskaitų teikimo sprendimą.

Šioje pamokoje pateikiamas tiesioginis scenarijus su "Excel 2010 PivotTable", prijungtu prie "SQL Server 2008 R2" duomenų bazės, naudojant paprastą SQL užklausą. Aš taip pat naudoju "Slicers", skirtą vaizdo filtravimui, kuris yra naujas "Excel 2010". Aš apimsiu sudėtingesnius BI metodus, naudojant "Data Analysis Expressions" (DAX) "PowerPivot" programoje "Excel 2010" artimiausiu metu. Ši naujausia "Microsoft Excel 2010" versija gali suteikti tikrą vertę jūsų naudotojų bendruomenei.

02 iš 15

Įterpti šerdinę lentelę

Nustatykite žymeklį tiksliai ten, kur norite pamatyti lentelę ir spustelėkite Insert | Suvestinės lentelės.

"Pivot Table" galite įtraukti į naują arba esamą "Excel" darbo knygą. Galbūt norėsite laikyti žymeklį kelias eilutes iš viršaus. Tai suteiks vietos antraštės ar įmonės informacijai, jei jūs bendrinate darbo lapą arba atspausdinsite.

Atidarykite naują ar esamą "Excel 2010" darbo knygą ir spustelėkite langelį, kuriame norite, kad būtų rodomas viršutinis kairysis Pivot lentelės kampas.
Spustelėkite skirtuką "Įterpti" ir spustelėkite skirtuko "Lentelės" išskleidžiamąjį langelį "PivotTable". Pasirinkite PivotTable. Tai paleidžia dialogo formą Sukurti PivotTable.

03 iš 15

"Pivot Table" prisijungti prie "SQL Server" (arba kitos duomenų bazės)

Sukurkite SQL užklausą ir tada prisijungiate prie SQL Server, norėdami įterpti jungties duomenų eilutę į "Excel" skaičiuoklę.

"Excel 2010" gali nuskaityti duomenis iš visų pagrindinių RDBVS (Reliacinės duomenų bazių valdymo sistemos) teikėjų. Pagal numatytuosius nustatymus "SQL Server" tvarkyklės turėtų būti prieinamos prisijungti. Tačiau visos pagrindinės duomenų bazės programinės įrangos leidžia ODBC (Open Database Connectivity) tvarkykles, leidžiančias jums prisijungti. Patikrinkite savo svetainę, jei norite atsisiųsti ODBC tvarkykles.

Šio pamokymo atveju prisijungiu prie SQL Server 2008 R2 (nemokama versija SQL Express).

A - Sukurti PivotTable formą yra pirmoji forma kuriant ryšį su SQL Server. Pasirinkite "Naudoti išorinį duomenų šaltinį" ir spustelėkite mygtuką "Pasirinkti ryšį". Palikite vietą, kur bus sukabinimo lentelė, nebent norite sukurti naują darbalapį ir jį įdėti.
B - esamų jungčių forma nurodo bet kokias dabartinės darbo knygos, jūsų kompiuterio ir tinklo, prie kurio esate prisijungę, jungtis. Esamos jungtys yra tik tekstiniai failai su ryšio informacija, reikalinga norint pasiekti tam tikrą duomenų šaltinį. Šiuo atveju mes sukursime naują duomenų šaltinį. Spustelėkite mygtuką "Naršyti dar".
C - spustelėjus mygtuką "Naujas šaltinis", paleidžiamas duomenų prijungimo vedlys.
D - Pasirinkite Microsoft SQL Server ir spustelėkite Pirmyn.
E - įveskite serverio vardą ir įregistruokite kredencialus. Pasirinkite tinkamą autentifikavimo metodą. Jei nesate tikri, kokį metodą naudoti, susisiekite su duomenų bazės administratoriumi.
- Naudokite "Windows" autentifikavimą: šis metodas naudoja prisijungimo prie tinklo prieigą prie "SQL Server" duomenų bazių.
- Naudokite šį vartotojo vardą ir slaptažodį: šis metodas naudojamas, kai SQL Server yra sukonfigūruotas atskiriems vartotojams prieiti prie duomenų bazių.
F - Šiame žingsnyje mes pasirinksime lentelę kaip rezervuarą. Mes ketiname pakeisti lentelę pasirinktiniu SQL, kuris tiksliai duos norimus duomenis "Excel" knygoje.
- Pasirinkite duomenų bazę, prie kurios prisijungsite. Šiame pavyzdyje mes prisijungiame prie "AdventureWorks" pavyzdžių duomenų bazės, kurią pateikė "Microsoft". Patikrinkite Prisijungimas prie konkrečios lentelės ir pasirinkite pirmąją lentelę. Atminkite, kad mes nenorime išgauti duomenų iš šios lentelės.
- Spustelėkite Baigti, kuri uždarys vedlį ir grąžins jus į darbaknygę. Mes pasikeisime vietovardžių stalą pagal užsakymą SQL.

Jums bus grąžinta forma "Create PivotTable" (A). Spustelėkite Gerai.

04 iš 15

Pivot lentelė laikinai prijungta prie SQL lentelės

PivotTable yra prijungtas prie "SQL Server" su užpildymo lentelės.

Tuo metu jūs prijungėte prie rezervuaro lentelės ir turite tuščią PivotTable. Kairėje kairėje matysite PivotTable, o dešinėje yra galimų laukų sąrašas.

05 iš 15

Atidarykite jungties ypatybes

Atidarykite jungties ypatybių formą.

Prieš pradėdami rinktis PivotTable duomenis, turime pakeisti ryšį su SQL užklausa. Įsitikinkite, kad esate skirtuke Parinktys ir spustelėkite Pakeisti duomenų šaltinį išskleidžiamajame sąraše "Duomenys". Pasirinkite Ryšio ypatybės.

Tai parodo jungties ypatybių formą. Spustelėkite skirtuką Apibrėžimas. Tai parodo jungties informaciją apie dabartinį ryšį su "SQL Server". Nors ji nurodo jungties failą, duomenys iš tikrųjų yra įterpti į skaičiuoklę.

06 iš 15

Atnaujinti ryšio ypatybes su užklausa

Pakeiskite lentelę į SQL užklausą.

Pakeiskite komandų tipą iš lentelės į SQL ir perrašykite esamą komandų tekstą savo SQL užklausoje. Čia yra užklausa, sukurta iš "AdventureWorks" pavyzdžių duomenų bazės:

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,
Gamyba.Produktas.Pavadinimas
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Production.Product.ListPrice,
Production.Product.ProductLine,
Production.ProductSubcategory.Name AS ProductCategory
NO Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =
Produkcija.Produktas.ProduktasID INNERIS PRISIJUNGIMAS Pardavimai.Klientas ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID AND
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Pardavimai. Individualus klientas ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
Produkcija.ProduktasSubcategory ON gamybos.Produktas.ProduktasSubcategoryID =
Gamyba. ProduktasSubcategory.ProductSubcategoryID

Spustelėkite Gerai.

07 iš 15

Gauti ryšio įspėjimą

Paspauskite "Taip" jungties įspėjimui.

Gausite "Microsoft Excel" įspėjimo dialogo langą. Taip yra todėl, kad pakeitėme ryšio informaciją. Kai mes iš pradžių sukūrėme ryšį, ji išsaugojo informaciją išoriniame .ODC faile (ODBC duomenų ryšys). Duomenys darbo knygoje buvo tokie patys kaip ir .ODC failas, kol mes pakeitėme iš lentelės komandų tipo į SQL komandos kodą 6 žingsnyje. Įspėjimas jums sako, kad duomenys nebepasiekiami sinchroniškai, o nuoroda į išorinį failą knygoje bus pašalinta. Tai yra gerai. Spustelėkite Taip.

08 iš 15

Pivot lentelė prijungta prie SQL serverio su užklausa

PivotTable yra pasirengusi pridėti duomenis.

Tai grąžinama į "Excel 2010" darbo knygą su tuščia PivotTable. Galite matyti, kad turimi laukai dabar yra skirtingi ir atitinka laukus SQL užklausoje. Dabar mes galime pradėti įtraukti laukus į PivotTable.

09 iš 15

Pridėkite laukus prie apskaitos lentelės

Pridėti laukus į PivotTable.

PivotTable laukų sąraše vilkite "ProductCategory" į eilutės etikečių sritį, "OrderDate" į stulpelių etiketų sritį ir "TotalDue to Values" srityje. Vaizdas rodo rezultatus. Kaip matote, datos laukas turi atskiras datas, taigi PivotTable sukūrė kiekvienos unikalios datos stulpelį. Laimei, "Excel 2010" turi keletą funkcijų, kurios padeda mums tvarkyti datų laukus.

10 iš 15

Pridėti duomenų laukų grupę

Pridėti datos lauko grupes.

Grupavimo funkcija leidžia mums tvarkyti datas į metus, mėnesius, ketvirčius ir tt Tai padės apibendrinti duomenis ir palengvinti naudotojo sąveiką su ja. Dešiniuoju pelės klavišu spustelėkite vieną iš datos stulpelių antraščių ir pasirinkite "Grupė", kuri atveria grupavimo formą.

11 iš 15

Pasirinkite grupavimą pagal vertes

Pasirinkite datos lauką grupavimo elementus.

Atsižvelgiant į sugrupuotų duomenų rūšį, forma atrodys šiek tiek kitokia. "Excel 2010" leidžia grupuoti datas, skaičius ir pasirinktus teksto duomenis. Šioje pamokoje mes suskirstome OrderDate, taigi formoje bus parodytos parinktys, susijusios su datų grupėmis.

Spustelėkite Mėnesiai ir metai ir spustelėkite Gerai.

12 iš 15

Pivotinė lentelė suskirstyta pagal metus ir mėnesius

Data laukai sugrupuoti pagal metus ir mėnesius.

Kaip matote aukščiau esančiame paveikslėlyje, duomenys sugrupuojami pagal metus, o po mėnesio. Kiekvienas turi pliuso ir minuso ženklą, kuris leidžia jums išplėsti ir sutraukti priklausomai nuo to, kaip norite peržiūrėti duomenis.

Tuo metu PivotTable yra gana naudinga. Kiekvienas laukas gali būti išfiltruotas, tačiau problema yra ne vizualus raktinis žodis apie dabartinę filtrų būklę. Taip pat reikia keletą paspaudimų pakeisti rodinį.

13 iš 15

Įterpti pjaustyklę (nauja "Excel 2010")

Pridėti pjaustyklės į PivotTable.

"Slicers" yra nauji "Excel 2010.". Plytelės iš esmės yra ekvivalentiškos vizualiai nustatant esamų laukų filtrus ir kurti ataskaitų filtrus tuo atveju, jei elementas, kurį norite filtruoti, nėra dabartiniame peržiūros lentelės rodinyje. Tai malonus dalykas apie slicerius - vartotojui labai lengva keisti PivotTable duomenų vaizdus, taip pat pateikti vaizdinius rodiklius, susijusius su dabartine filtravimo būsena.

Norėdami įdėti Slicers, spustelėkite skirtuką Parinktys ir spustelėkite Insert Slicer iš skyriaus "Rūšiavimas ir filtravimas". Pasirinkite Insert Slicer, kuris atidaro formą "Insert Slicers". Patikrinkite tiek laukų, kiek norite. Pavyzdyje pridėjome metus, CountryRegionName ir ProductCategory. jums gali prireikti padėtį, jei norite juos surinkti. Pagal numatytuosius nustatymus pasirenkamos visos vertės, o tai reiškia, kad filtrai nebuvo taikomi.

14 iš 15

Pivot lentelė su patogiu pjaustytuvu

Sliceriai leidžia vartotojams lengviau filtruoti PivotTables.

Kaip matote, "Slicers" parodo visus pasirinktus duomenis. Vartotojui labai aišku, kokie duomenys yra dabartiniame PivotTable rodinyje.

15 iš 15

Pasirinkite "Vertybės iš pjaustymo lentelių, kurių atnaujinimai yra"

Pasirinkite duomenų derinimo kombinacijas, kad pakeistumėte duomenų rodinį.

Spustelėkite įvairias verčių derinius ir pažiūrėkite, kaip keičiasi PivotTable rodinys. Galite naudoti įprastą "Microsoft" spustelėjimą "Slicers", taigi, jei galite naudoti "Control + Click", norėdami pasirinkti kelias reikšmes, arba "Shift + Click", pasirinkite verčių diapazoną. Kiekvienas Slicer parodo pasirinktas reikšmes, todėl akivaizdu, kad PivotTable būklė yra filtro atžvilgiu. Jei norite, galite keisti Slicers stilių spustelėję skirtuko Parinktys skirtuko Slicer skiltyje esantį spartųjį stilių.

"Slicers" įdiegimas iš tikrųjų pagerino "PivotTables" tinkamumą naudoti ir 2010 m. "Excel 2010" tapo labiau profesionalios verslo žvalgybos įrankiu. "PivotTables" labai pagerėjo "Excel 2010", o kartu su nauja PowerPivot sukurta labai našios analizės aplinka.