Excel 2010: n pivot-taulukoiden määrittäminen

by Tim Perdue

01/15

Lopullinen tulos

Tämä on tämän Step by Step -opastusohjelman lopullinen tulos - Napsauta kuvaa nähdäksesi koko version.

Microsoft Excelin ja huipputason liiketoimintatiedon (BI) alustojen välillä on ollut eroja monien vuosien ajan. Microsoft Excel 2010 -pivottaulukon parannukset ja pari muuta BI-ominaisuutta ovat tehneet siitä todellisen kilpailijan Enterprise BI: lle. Excelia on perinteisesti käytetty erillisten analyysien tekemiseen ja vakiotyökaluihin, jotka kaikki vievät loppuraporttinsa. Professional business intelligence on perinteisesti varattu SAS: lle, Business Objectsille ja SAP: lle.

Microsoft Excel 2010 (Excel 2010 -pivot-taulukon kanssa) yhdessä SQL Server 2008 R2: n, SharePoint 2010: n ja ilmaisen Microsoft Excel 2010 -lisäosan kanssa "PowerPivot" on tuottanut huippuluokan liiketoimintatiedon ja raportointiratkaisun.

Tämä opetusohjelma kattaa suoraa etenemissuunnitelmaa, jossa Excel 2010 PivotTable on liitetty SQL Server 2008 R2 -tietokantaan käyttämällä yksinkertaista SQL-kyselyä. Käytän myös Slicers for visual -suodatusta, joka on uusi Excel 2010: ssä. Aion käsitellä lähitulevaisuudessa monimutkaisempia BI-tekniikoita Data Analysis Expression (DAX) avulla PowerPivot for Excel 2010 -ohjelmassa. Tämä Microsoft Excel 2010: n uusin julkaisu voi tarjota todellisen arvon käyttäjäyhteisölle.

02/15

Lisää pivottaulukko

Aseta kursori tarkalleen missä haluat pivot-pöydän ja napsauta Lisää | Pivot-taulukko.

Voit lisätä pivot-taulukon uuteen tai olemassa olevaan Excel-työkirjaan. Haluat ehkä harkita kohdistimen sijoittamista muutaman rivin yläosaan. Tämä antaa sinulle tilaa ylä- tai yritystiedot, jos jakaa laskentataulukon tai tulostaa sen.

Avaa uusi tai olemassa oleva Excel 2010 -työkirja ja napsauta solua, johon haluat pivot-pöydän vasemmassa yläkulmassa olevan.
Napsauta Lisää-välilehteä ja napsauta PivotTable-kuvaketta pudottamalla Taulukot-osiossa. Valitse PivotTable. Tämä käynnistää Create PivotTable -valintaikkunan.

03/15

Yhdistä pivot-taulukko SQL Server (tai muuhun tietokantaan)

Luo SQL-kyselysi ja muodosta sitten yhteyden SQL Server -palvelimeen liittymätiedoston merkkijonoksi Excel-laskentataulukkoon.

Excel 2010 voi hakea tietoja kaikista tärkeimmistä RDBMS (Relational Database Management System) tarjoajista. SQL Server -ohjaimet pitäisi olla käytettävissä yhteyden muodostamiseksi oletuksena. Mutta kaikki tärkeät tietokantaohjelmistot tekevät ODBC (Open Database Connectivity) -ohjaimia, joiden avulla voit muodostaa yhteyden. Tarkista verkkosivustosi, jos haluat ladata ODBC-ohjaimet.

Tässä opetusohjelmassa olen yhteydessä SQL Server 2008 R2: een (SQL Express ilmainen versio).

A - Luo pivot-lomake on ensimmäinen lomake, jolla luodaan yhteys SQL Serveriin. Valitse "Käytä ulkoista tietolähdettä" ja napsauta Valitse yhteys-painiketta. Jätä sijainti, jossa kääntötaulukko sijoitetaan, ellet halua luoda uutta laskentataulukkoa ja aseta se siihen.
B - Nykyiset yhteydet -muodossa luetellaan kaikki nykyisen työkirjan, tietokoneen ja verkon, johon olet tällä hetkellä yhteydessä. Olemassa olevat yhteydet ovat todellakin vain tekstitiedostoja, joilla on yhteystiedot, jotka tarvitaan tietyn tietolähteen käyttämiseen. Meidän tapauksessamme aiomme luoda uuden tietolähteen. Napsauta Selaa Lisää -painiketta.
C - Napsauta Uusi lähde -painiketta käynnistää ohjattu tiedonsiirtoyhteyden.
D - Valitse Microsoft SQL Server ja napsauta Seuraava.
E - Syötä Palvelimen nimi ja kirjaudu valtuutuksiin. Valitse sopiva todentamismenetelmä. Jos et ole varma, mitä menetelmää haluat käyttää, ota yhteyttä tietokannan ylläpitäjään.
- Windows-todennuksen käyttäminen: Tämä menetelmä käyttää verkkotunnuksesi kirjautumiseen SQL Server -tietokantoihin.
- Käytä seuraavaa käyttäjänimeä ja salasanaa: Tätä menetelmää käytetään, kun SQL Server on määritetty erillisten käyttäjien kanssa tietokantojen käyttämiseksi.
F - Tässä vaiheessa aiomme valita taulukon paikanvaraajaksi. Aiomme korvata taulukon mukautetulla SQL: llä, joka antaa täsmälleen haluamamme tiedot Excel-työkirjallamme.
- Valitse tietokanta, johon haluat muodostaa yhteyden. Tässä esimerkissä olemme yhteydessä Microsoftin tarjoamaan AdventureWorks-mallitietokantaan. Valitse Yhdistä tiettyyn taulukkoon ja valitse ensimmäinen taulukko. Muista, että emme aio noutaa tietoja tästä taulukosta.
- Valitse Valmis, joka sulkee ohjatun toiminnon ja palauttaa sinut työkirjaan. Jätämme muokatun SQL-kyselyn paikkamerkintätaulukon.

Palautetaan Luo PivotTable-lomake (A). Napsauta OK.

04/15

Pivot-taulukko tilapäisesti yhdistetty SQL-taulukkoon

PivotTable on liitetty SQL Serveriin paikkamerkkitaulukolla.

Tässä vaiheessa olet liittänyt paikkamerkintätaulukkoon ja sinulla on tyhjä pivottaulukko. Näet vasemmalla puolella olevan pivot-taulukon ja oikealla on käytettävissä olevien kenttien luettelo.

05/15

Avaa yhteyden ominaisuudet

Avaa yhteyden muodot.

Ennen kuin aloitamme pivot-taulukon tietojen valitsemisen, meidän on muutettava yhteys SQL-kyselyyn. Varmista, että olet Asetukset-välilehdessä ja napsauta Muuta tietolähdettä Data-osiossa. Valitse Yhteysominaisuudet.

Tämä tuo näkyviin Connection Properties -lomakkeen. Napsauta Määritelmä-välilehteä. Tämä näyttää yhteyden tiedot nykyiselle yhteydelle SQL Serverille. Vaikka se viittaa yhteystiedostoon, tieto on itse asiassa upotettu laskentataulukkoon.

06/15

Päivitä yhteysominaisuudet kyselyn avulla

Muuta taulukkoa SQL-kyselyyn.

Muuta komentotyyppi taulukosta SQL-muotoon ja korvaa olemassa olevan komentotiedoston SQL-kyselyn avulla. Tässä on kysely, jonka olen luonut AdventureWorks-mallitietokannasta:

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,
Production.Product.Name,
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Production.Product.ListPrice,
Production.Product.ProductLine,
Tuotanto.tuoteSubcategory.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 =
Production.Product.ProductID INNER JOIN Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID AND
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
Tuotanto.TuotteenAlaluokka ON Tuotanto.tuote.tuoteSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID

Napsauta OK.

07/15

Vastaanota yhteysvaroitus

Napsauta Kyllä-yhteyden varoitusta.

Saat Microsoft Excel -varoitus -valintaikkunan. Tämä johtuu siitä, että muutimme yhteystietoja. Kun alun perin luotu yhteys, se tallensi tiedot ulkoiseen .ODC-tiedostoon (ODBC Data Connection). Työkirjan tiedot olivat samoja kuin .ODC-tiedosto, kunnes muutimme taulukon komentotyypistä SQL-komentoon vaiheessa 6. Varoitus kertoo, että tiedot eivät ole enää synkronoituna ja viittaus työkirjan ulkoiseen tiedostoon poistetaan. Tämä on ok. Valitse Kyllä.

08/15

SQL-palvelimeen yhdistetty pivot-taulukko kyselyn avulla

PivotTable on valmis lisäämään tietoja.

Tämä palaa Excel 2010 -työkirjaan tyhjällä pivottavalla. Näet, että käytettävissä olevat kentät ovat nyt erilaisia ja vastaavat SQL-kyselyn kenttiä. Voimme nyt aloittaa kenttien lisäämisen pivottaulukkoon.

09/15

Lisää kentät pivottaulukkoon

Lisää kentät pivottaulukkoon.

Vedä ProductCategory -kohdassa PivotTable-kenttäluettelo Row Labels -alueelle OrderDate to Column Labels -alue ja TotalDue to Values -alue. Kuva näyttää tulokset. Kuten näet, päivämääräkentässä on yksittäiset päivämäärät, joten pivottaulukko on luonut sarakkeen jokaiselle yksilölliselle päivämäärälle. Onneksi Excel 2010: ssa on joitain toimintoja, jotka auttavat meitä järjestämään päivämääräkenttiä.

10/15

Lisää ryhmittely päivämääräkentille

Lisää ryhmittelyjä datakenttään.

Ryhmätoiminto antaa meille mahdollisuuden järjestää päivämäärät vuosille, kuukausille, vuosineljänneksille jne. Tämä auttaa tiivistämään tiedot ja helpottamaan käyttäjän vuorovaikutusta sen kanssa. Napsauta hiiren kakkospainikkeella jotain päivämäärän sarakkeen otsikoista ja valitse Ryhmittele, joka tuo ryhmän ryhmittelyn.

11/15

Valitse Ryhmittely arvojen mukaan

Valitse ryhmittelykohteet päivämääräkentälle.

Riippuen siitä, millaista tietoa ryhmitellään, lomake näyttää hieman erilaiselta. Excel 2010 -ohjelmassa voit ryhmitellä päivämäärät, numerot ja valitut tekstitiedot. Olemme ryhmittele OrderDate tässä opetusohjelmassa, joten lomakkeessa näytetään päivämääräyhdistelmiä koskevat asetukset.

Napsauta Kuukautta ja vuotta ja valitse OK.

12/15

Pivot-taulukko ryhmiteltyinä vuosia ja kuukausia

Päivämääräkentät on ryhmitelty vuosia ja kuukausia.

Kuten yllä olevassa kuvassa näkyy, tiedot ryhmitellään vuosittain ja sitten kuukausittain. Jokaisella on plus- ja miinusmerkki, jonka avulla voit laajentaa ja romahtaa riippuen siitä, miten haluat nähdä tiedot.

Tässä vaiheessa pivot-taulukko on melko hyödyllinen. Jokainen kentistä voidaan suodattaa, mutta ongelmana on, että ei ole visuaalista viittaa suodattimien nykyiseen tilaan. Myös näkymän muuttaminen vaatii useita napsautuksia.

13/15

Lisää slicer (uusi Excel 2010)

Lisää viipaleita pivottaulukkoon.

Slicers ovat uusia Excelissä 2010. Slicers ovat pohjimmiltaan vastaavan visuaalisesti nykyisten kenttien suodattimia ja luo raporttisuodattimet siinä tapauksessa, että kohde, jonka haluat suodattaa, ei ole nykyisessä pivottaulukonäkymässä. Tämä mukava asia Slicersissä on, että käyttäjä voi helposti muuttaa pivot-tilan tietojen näkymää sekä antaa visuaalisia indikaattoreita suodattimien nykyiseen tilaan.

Jos haluat lisätä viipaleita, napsauta Asetukset-välilehteä ja napsauta Lisää viipaloija Lajittelu- ja suodatin -osiosta. Valitse Insert Slicer, joka avaa Insert Slicers -lomake. Tarkista, kuinka monta kenttää haluat olla saatavilla. Esimerkissämme lisäsimme Vuosia, CountryRegionName ja ProductCategory. saatat joutua sijoittamaan Slicers, jossa haluat ne. Oletusarvoisesti kaikki arvot on valittu, joten suodattimia ei ole käytetty.

14/15

Pivot-taulukko, jossa käyttäjäystävälliset viipaleet

Liukusäätimet helpottavat käyttäjien suodattaa pivot-taulukot.

Kuten näette, Slicers näyttää kaikki valitut tiedot. Käyttäjälle on erittäin selvää, mitä tietoja on nykyisen pivottaulukon näkymässä.

15/15

Valitse arvot viipaleista, jotka päivittävät pivottaulukon

Noudata tietojen katselua valitsemalla Slicers-yhdistelmät.

Napsauta eri arvojen yhdistelmiä ja katso, miten pivottaulukon näkymä muuttuu. Voit käyttää tyypillisiä Microsoftin napsautuksia Slicerissä, mikä tarkoittaa sitä, että jos haluat valita useita arvoja Control + Klikkaamalla, voit valita arvoalueiden valitsemalla Shift + Klikkaa. Jokainen Slicer näyttää valitut arvot, mikä tekee siitä todella selville, mitä pivot-tilan tila on suodattimien suhteen. Voit muuttaa Slicers-tyylit napsauttamalla Pikaustyyppejä -vaihtoehtoa Asetukset-välilehden Slicer-osiossa.

Slicersin käyttöönotto on todella parantanut pivot-taulukkojen käytettävyyttä ja on siirtänyt Excel 2010: n paljon lähempänä ammattimaista liiketoimintatyökalua. Pivot-taulukot ovat parantuneet melko vähän Excel 2010: ssa ja kun ne yhdistetään uuden PowerPivotin kanssa, luodaan erittäin tehokas analyyttinen ympäristö.