Konfigurirajte Pivot tablice programa Excel 2010

Došlo je do jaza između Microsoft Excela i najviših poslovnih inteligencija (BI) platformi već dugi niz godina. Poboljšanja Pivot tablice programa Microsoft Excel 2010 zajedno s još nekoliko BI značajki učinila su to pravi konkurent za BI. Excel se tradicionalno koristio za samostalnu analizu i standardni alat koji svatko izvozi svoja završna izvješća. Profesionalna poslovna inteligencija tradicionalno je rezervirana za ljubitelje SAS-a, poslovnih objekata i SAP-a.

01 od 15

Završni rezultat

Microsoft Excel 2010 (uz Excel 2010 Pivot Table) uz SQL Server 2008 R2, SharePoint 2010 i besplatni dodatak Microsoft Excel 2010 "PowerPivot" rezultirali su vrhunskim rješenjem poslovne inteligencije i izvješćivanja.

Ovaj vodič pokriva jasan scenarij s Excel 2010 PivotTableom povezanom s bazom podataka SQL Server 2008 R2 pomoću jednostavnog SQL upita.

Nastavite čitati ispod

02 od 15

Umetni stožernu tablicu

Možete umetnuti Pivot tablicu u novu ili postojeću radnu knjigu programa Excel. Preporučujemo da razmotrite pozicioniranje pokazivača niz nekoliko redaka s vrha. To će vam dati prostor za zaglavlje ili informacije o tvrtki u slučaju da podijelite radni list ili ispišete.

• Otvorite novu ili postojeću radnu knjigu programa Excel 2010 i kliknite na ćeliju u kojoj želite da gornji lijevi kut Pivot tablice bude.
• Kliknite karticu Umetni i kliknite padajući izbornik za podešavanje tablice u odjeljku Tablice. Odaberite tablicu za podešavanje. Ovo će pokrenuti obrazac za dijalog Stvaranje tablice za zakretanje.

Nastavite čitati ispod

03 od 15

Spojite Pivot tablicu na SQL Server

Excel 2010 može dohvatiti podatke svih glavnih pružatelja RDBMS (Relational Database Management System). Vozači za SQL Server trebali bi biti dostupni za povezivanje prema zadanim postavkama. Provjerite svoje web stranice ako trebate preuzeti ODBC upravljačke programe.

U ovom priručniku povezujemo se s SQL Server 2008 R2 (besplatna inačica SQL Express).

1. Otvorite obrazac Stvaranje tablice za zakrpe. Odaberite "Upotrijebite vanjski izvor podataka" i kliknite gumb Odaberi vezu. Ostavite mjesto gdje će se postaviti stožerna tablica.
2. Otvorite obrazac Postojeće veze. Kliknite gumb Potraži za više.
3. Kliknite na gumb Novi izvor koji će pokrenuti Čarobnjak za povezivanje podataka.
4. Odaberite Microsoft SQL Server i kliknite Dalje.
5. Unesite naziv poslužitelja i vjerodajnice za prijavu. Odaberite odgovarajući način provjere autentičnosti:
   1. Koristite autentifikaciju sustava Windows: Ova metoda koristi vašu prijavu na mrežu za pristup SQL Server bazama podataka.
   2. Upotrijebite sljedeće korisničko ime i lozinku: Ova se metoda koristi kada je SQL Server konfiguriran s samostalnim korisnicima za pristup bazama podataka.
6. Zamijenite tablicu s prilagođenim SQL-om koji će pružiti točno željene podatke u našoj radnoj knjizi programa Excel:
   1. Odaberite bazu podataka na koju ćete se povezati. U ovom primjeru povezujemo se s bazom podataka AdventureWorks uzorka koje pruža Microsoft. Provjerite povezivanje s određenom tablicom i odaberite prvu tablicu. Ne zaboravite da nećemo dohvaćati podatke iz ove tablice.
   2. Kliknite Završi koji će zatvoriti čarobnjaka i vratiti vas u radnu knjigu. Izbrisat ćemo tablicu rezerviranja za naš prilagođeni SQL upit.

Vratit ćete se u obrazac Stvaranje tablice za zakretanje (A). Kliknite U redu.

04 od 15

Pivot tablica privremeno povezana sa SQL tablicom

U ovom trenutku povezali ste se sa tablicom rezerviranog mjesta i imate praznu tabličnu tablicu. Na lijevoj strani možete vidjeti poništavanje tablice, a s desne strane nalazi se popis raspoloživih polja.

Nastavite čitati ispod

05 od 15

Otvori svojstva veze

Provjerite jeste li na kartici Opcije i kliknite padajući izbornik Podatci o promjeni izvor podataka. Odaberite Svojstva veze.

Time se pojavljuje obrazac Svojstva veze. Kliknite karticu Definicija. Ovo prikazuje informacije veze za trenutačnu vezu s SQL Serverom. Dok se odnosi na datoteku veze, podaci su zapravo ugrađeni u proračunsku tablicu.

06 od 15

Ažurirajte svojstva veze uz upit

Promijenite vrstu naredbe iz tablice u SQL i prebrisajte postojeći naredbeni tekst pomoću SQL upitnika. Evo upita koji smo izradili iz baze podataka AdventureWorks uzorka:

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,Proizvodnja.Proizvoda.Name,Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,Sales.Customer.CustomerType,Proizvodnja.Produkt.ListPrice,Production.Product.ProductLine,Proizvodnja.ProdukcijaSkategorija.Name AS ProductCategoryFROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ONSales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDINNER JOIN Proizvodnja.Produkt ON Sales.SalesOrderDetail.ProductID =Proizvodnja.Proizvod.Proizvod ID INNER JOIN Sales.Customer ONSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID ANDSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOINSales.vIndividualCustomer ON Sales.Customer.CustomerID =Sales.vIndividualCustomer.CustomerID INNER JOINProizvodnja.ProizvodnjaSubkategorija ON ProizvodnjaProizvod.ProizvodiSategorija ID =Production.ProductSubcategory.ProductSubcategoryID

Kliknite U redu.

Nastavite čitati ispod

07 od 15

Primite upozorenje veze

Dobit ćete dijaloški okvir Upozorenje Microsoft Excel. To je zbog toga što smo promijenili podatke veze. Kada smo izvorno stvorili vezu, spremili smo podatke u vanjsku .ODC datoteku (ODBC podatkovna veza). Podaci u radnoj knjizi bili su isti kao i .ODC datoteke sve dok smo se u stupnju # 6 promijenili iz tablice naredbe tablice u SQL naredbu. Upozorenje vam govori da podaci više nisu sinkronizirani i uklonit će se reference na vanjsku datoteku u radnoj knjizi. Ovo je u redu. Kliknite Da.

08 od 15

Pivot tablica povezana s SQL Serverom sa upitom

Ovo se vraća u radnu knjigu programa Excel 2010 s praznim tablama. Možete vidjeti da su raspoloživa polja sada različita i odgovaraju poljima u SQL upitu. Sada možemo dodati polja u tablicu za zaokretanje.

Nastavite čitati ispod

09 od 15

Dodaj polja u tablicu zaokretanja

Na popisu polja zaokretne tablice povucite područje ProductCategory do redaka oznake, područje OrderDate do oznake stupaca i područje TotalDue to Values. Kao što vidite, polje s datumima ima pojedinačne datume, tako da je tablična tablica stvorila stupac za svaki pojedinačni datum. Excel 2010 ima neke ugrađene funkcije kako bi nam pomogli organizirati datumska polja.

10 od 15

Dodaj grupiranje za polja s datumima

Funkcija grupiranja omogućuje nam organiziranje datuma u godinama, mjesecima, četvrtinama itd. To će pomoći sažeti podatke i olakšati korisnicima interakciju s njom. Desnom tipkom miša kliknite jednu od zaglavlja stupaca datuma i odaberite Skupina koja prikazuje obrazac za grupiranje.

Nastavite čitati ispod

11 od 15

Odaberite Grupiranje po vrijednostima

Ovisno o vrsti podataka koje grupirate, obrazac će izgledati malo drugačije. Excel 2010 omogućuje grupiranje datuma, brojeva i odabranih tekstualnih podataka. Grupiranje OrderDatea u ovom vodiču tako da obrazac prikazuje opcije vezane uz grupiranje datuma.

Kliknite Mjeseci i godine i kliknite U redu.

12 od 15

Pivotna tablica grupirana po godinama i mjesecima

Podaci se grupiraju po prvi put, a zatim po mjesecu. Svaka od njih ima plus i minus znak koji vam omogućuje proširenje i kolaps, ovisno o tome kako želite vidjeti podatke.

U ovom trenutku, pivot tablica je prilično korisna. Svako od polja može se filtrirati, ali problem je da ne postoji vizualni trag o trenutnom stanju filtara. Za promjenu prikaza potrebno je nekoliko klikova.

13 od 15

Umetni Slicer (novo u programu Excel 2010)

Sliceri su novi u programu Excel 2010. Sliceri su u osnovi isti kao vizualni postavci filtara postojećih polja i stvaranje filtara za izvješća u slučaju da stavka na koju želite filtrirati nije u trenutnom prikazu tablice. Ova lijepa stvar o Slicerima postaje vrlo jednostavna za korisnika da promijeni prikaz podataka u PivotTableu, kao i pružaju vizualne pokazatelje o trenutnom stanju filtara.

Da biste umetnuli Slicere, kliknite karticu Mogućnosti i kliknite Umetni Slicer iz odjeljka Poredaj i filtriranje. Odaberite Umetni Slicer koji otvara obrazac Umetni Sliceri. Provjerite onoliko polja koliko želite imati na raspolaganju.

14 od 15

Pivotna tablica s korisničkim slicicama

Kao što možete vidjeti, Sliceri prikazuju sve podatke kao što je odabrano. Vrlo je jasno korisniku točno ono što se nalazi u trenutnom prikazu tablice za podešavanje.

15 od 15

Izaberite vrijednosti od slicera koji ažuriraju tablicu zaokretanja

Kliknite različite kombinacije vrijednosti i pogledajte kako se mijenja prikaz tablice zaobilazeće tablice. Možete upotrijebiti tipični Microsoftov klik na Slicere što znači da ako možete koristiti Control + Kliknite za odabir više vrijednosti ili Shift + Click za odabir raspona vrijednosti.

Svaki Slicer prikazuje odabrane vrijednosti što ga čini očiglednim što je stanje Pivot tablice u smislu filtara. Ako želite, možete promijeniti stilove Slicera klikom na padajući izbornik Brzi stilovi u odjeljku Slicer na kartici Mogućnosti.