Eksel lako obavlja sumarizaciju podataka. Kada se podaci čuvaju u obliku stila za crosstab, korisnici Ekcela moraju potrošiti malo vremena za pripremu podataka za upotrebu. Postoji mnogo načina za ostvarivanje skoro bilo kog Ekcel zadatka, ali u ovom postu ću pokazati kako brzo da se ne spasu podaci.
Pre nego što uđemo u način rada, budimo sigurni da smo jasno o formatima podataka i našem cilju.
Evo primera ravnih, tabelarnih podataka:
Evo primera istih podataka sačuvanih u formatu stila crosstab:
Mi lako možemo pretvoriti tabelarne podatke u format crosstab koristeći PivotTable. Ali ovde, želimo da uradimo suprotno. Želimo da ne spasimo podatke, pretvorimo ih iz crosstab formata u tabelarni format.
Napomena: imajte na umu da neostvarivanje podataka nije isto što i transponovanje. Prenošenje podataka postavilo bi se odjeljenja u redove i račune u kolone. Ako želite da prenesete umesto nečistoće, pogledajte ovaj post Ekcel Universiti blog.
Evo još jednog primera koji pokazuje studente i putovanja kojima su prisustvovali.
I još jedan primer koji prati ko je dodeljen raznim zadacima.
Sada kada imamo svoje ležajeve i vidno možemo da vidimo naš cilj, da prođemo kroz detalje.
Unpivot tabele
Komanda unpivot je dostupna bez ikakvih dodatnih preuzimanja u programu Ekcel 2016 za Vindovs. Ako koristite drugu verziju, možda ćete prvo morati da preuzmete besplatni dodatak Pover Kueri sa Microsoft lokacije. U trenutku kada pišem ovo, to je dostupno na linku ispod.
Četiri jednostavna koraka koja ćemo iskoristiti da iskoristimo podatke o crosstab-u su:
- Snimiti podatke iz unakrsne tabele,
- Preuzmi i transformiši podatke iz tabele,
- uradi Unpivot date tabele i
- Učitaj rezutat.
A sada uradimo to.
Snimanje podataka iz unakrsne tabele
Prvo, moramo osigurati da se naši podaci sačuvaju u radnu tabelu eksela. Ako možete preskočite ovaj korak. Naši podaci nisu uskladišteni u tablici, i trenutno izgleda ovako.
Da ga pretvorimo u tabelu, selektujemo bilo koju ćeliju u opsegu podataka i kliknite na komandu Insert> Table. Sada se čuva u stolu i izgleda ovako.
Hej, to je bilo prilično lako. Pređimo na sledeći korak.
Preuzmi i transformiši podatke iz tabele
Sledeći korak je korištenje naredbe Get & Transform From Table. Opet, ovo je ugrađeno početak sa Ekcel 2016 za Vindovs. Ako imate drugačiju verziju, želite da preuzmete i instalirate Pover Kueri koristeći dolenavedeni link i obratite pažnju da se navigacija može malo razlikovati od prikazanih ekrana prikazanih ispod.
Prvo, izaberemo bilo koju ćeliju u tabeli. Zatim kliknite na sledeću naredbu Ribbon koja se nalazi u grupi Get & Transform.
Podaci> Iz tabele
Otvara se Query Editor, kao što je prikazano u nastavku.
Hej, to je bilo prilično lako … Hajde da napravimo sledeći korak.
Uradi Unpivot date tabele
Pre nego što upotrebimo komandu unpivot, prvo moramo da kažemo Ekcelu koje kolone želimo da ne spavamo. Da biste to uradili, možemo izabrati prvu kolonu koju želimo da ne spavamo, zadržite taster Shift, a zatim kliknite na poslednju kolonu. Rezultati su prikazani u nastavku.
Napomena: ako želite da poništite korak u editoru upita, kliknite na k u polju za popunjavane korake.
Sada kada smo identifikovali koje kolone ne bi trebalo da iskopamo, možemo koristiti komandu unpivot. Komanda unpivot se nalazi na kartici Transform. Pošto se traka dinamički seli na osnovu veličine dijaloga, možete videti komandu unpivot stupnja sa tekstualnom etiketom, ovako:
Ili se može pojaviti bez tekstualne naljepnice, ovako:
Samo kliknite ikonu komande, a bam, Ekcel unpotots data, kao što je prikazano ispod.
Hej, to je bilo prilično lako! Poslednji korak je učitavanje podataka u našu radnu svesku.
Učitaj rezutat (Load)
Sa nepovezanim podacima, samo je potrebno vratiti u našu radnu svesku. Da bi to učinili, koristimo komandu Zatvori i učitaj na kartici Početak. Ekcel isprazne neprevaziđene podatke u našu radnu svesku, kao što je prikazano u nastavku.
To je lako!
Komanda unpivot je samo jedna od mnogih moćnih mogućnosti u dijalogu Kveri Editor. Ako preuzmete podatke iz spoljnih izvora i često vršite transformaciju podataka, sigurno ćete želeti da istražite dodatne komande.
Ovo je prevod članka Džefa Leninga.[:en]Excel easily summarizes flat, tabular data. When data is stored in a crosstab style format instead, Excel users have to spend a bit of time preparing the data for use. There are many ways to accomplish just about any Excel task, but in this post, I’ll demonstrate how to quickly unpivot the data. Thanks to Patrick who submitted this question.
Objective
Before we dig into the mechanics, let’s be sure we are clear about the data formats and our objective.
Here is an example of flat, tabular data:
Here is an example of the same data stored in a crosstab style format:
We can easily convert tabular data into a crosstab format using a PivotTable. But here, we want to do the opposite. We want to unpivot the data, converting it from a crosstab format into a tabular format.
Note: please note that unpivoting the data is not the same as transposing it. Transposing the data would place departments in rows and accounts in columns. If you need to transpose instead of unpivot, check out this Excel University blog post instead.
Here is another example that shows students and the trips they have attended.
And one more example that tracks who is assigned to various tasks.
Now that we have our bearings and can visually see our objective, let’s work through the details.
Unpivot
The unpivot command is available without any additional downloads in Excel 2016 for Windows. If you are using a different version, you may need to first download the free Power Query add-in from the Microsoft site. At the time I’m writing this, it is available from the link below.
The four easy steps we’ll use to unpivot our crosstab data are:
- Store the crosstab data in a table
- Get & Transform From Table
- Unpivot
- Load
We’ll take them one at a time.
Store the crosstab data in a table
First, we need to ensure that our crosstab data is stored in a table. If it already is, you can skip this step. Our data isn’t stored in a table, and it currently looks like this.
To convert it into a table, we select any cell in the data range and click the Insert > Table command. Now, it is stored in a table and looks like this.
Hey, that was pretty easy. Let’s move to the next step.
Get & Transform From Table
The next step is to use the Get & Transform From Table command. Again, this is built-in beginning with Excel 2016 for Windows. If you have a different version, you’ll want to download and install Power Query using the link below, and note that the navigation may be slightly different from the screenshots presented below.
First, we select any cell in the table. Then, we click the following Ribbon command located in the Get & Transform group.
- Data > From Table
This opens the Query Editor, as shown below.
Hey, that was pretty easy…let’s take the next step.
Unpivot
Before we use the unpivot command, we first need to tell Excel which columns we’d like to unpivot. To do this, we can select the first column we want to unpivot, hold down the Shift key, and then click the last column. The results are shown below.
Note: if you would like to undo a step in the Query Editor, you click the x in the Applied Steps list box.
Now that we have identified which columns to unpivot, we can use the unpivot command. The unpivot command is located on the Transform tab. Since the Ribbon dynamically sizes itself based on the dialog size, you may see the unpivot columns command with a text label, like this:
Or, it may appear without a text label, like this:
Just click the command icon, and bam, Excel unpivots the data, as shown below.
Hey, that was pretty easy! The final step is to load the data back into our workbook.
Load
With the data unpivoted, we just need to get it back to our workbook. To do so, we use the Close & Load command on the Home tab. Excel drops the unpivoted data back into our workbook, as shown below.
That was easy!
The unpivot command is but one of the many powerful capabilities in the Query Editor dialog. If you retrieve data from external sources and perform data transformations often, you’ll definitely want to investigate the additional commands.[:]