Šta ćete učiniti ako trebate kreirati konsolidovanu tablicu i svoje podatke o različitim listovima? Uz instalirani Excel 2013 imate jednostavan način za ovo. Postoji tehnologija koja se zove model podataka i koristi odnose s podacima kao što čini bazu podataka.
U ovoj lekciji pokazat ću vam sve da stvorite tablicu okreta u Excelu 2013 iz podataka o više listova pomoću modela podataka.
Video
Ako želite pratiti lekciju pomoću vlastitog Excel datoteke, možete to učiniti. Ili preuzmite ZIP datoteku na ovu lekciju, koja sadrži uzorku knjigu. Pivot consolidat.xlsx.
Učenje podataka
Ova knjiga ima tri radna lima: informacije o klijentu, informacije o narudžbi, narudžbi i informacije o plaćanju.
Kliknite na list Informacije o kupcima I pobrinite se da sadrži brojeve narudžbe, kao i ime i stanje kupaca.
Lista info kupca
Kliknite na list Podaci o narudžbi. I pogledajte da sadrži narudžbe, kao i polja za mjesec, naručenu robu i da li su ovi proizvodi organski.
Naručite informacije listoviKliknite karticu Informacije o plaćanju i provjerite da li sadrži narudžbe, iznos u dolarima svake prodaje, načina plaćanja i redoslijed plasmana naloga s novim ili postojećim klijentom.
Podaci o plaćanju listPovezivanjem svih ovih listova u području zadataka zadataka, možemo odabrati podatke sa svakog lista. Budući da brojevi narudžbe postoje na sva tri lista, oni će postati priključni točke. To je ono što bi baza podataka uzrokuje primarni ključ. Napominjemo: Nije potrebno imati primarni ključ, ali smanjuje vjerojatnost pogreške.
Kreirajte imenovane tablice
Prije stvaranja tablice okreta, napravimo tablicu sa svakog lista.
Kliknite nazad B. Stol za kupce, a zatim kliknite bilo gdje u području podataka. Kliknite karticu Umetanje. Na trakastoj ploči i kliknite ikonu Tablica.
Pretvorite podatke na listu odabirom Umetanje\u003e TabelaDijaloški okvir CREATE tabela pravilno određuje područje tablice. Potvrdni okvir u nastavku također bi trebao identificirati da je prvi niz tablice namijenjen zaglavljama. (Ako ne, odaberite ovu opciju.)
Kreirajte dijalog tablice mora pravilno pogađati područje podataka.Kliknuti UREDU.A sada imate stol sa naizmjeničnim udarima i tipkama filtra. Možete kliknuti iznutra da biste uklonili odabir ako želite da ga bolje razmislite (jednostavno ne pritisnete izvan tablice). TAPENICA SE takođe prikazuje karticu Dizajn. Za stol. Na lijevoj strani vrpce na terenu Naziv tabele. Prikazuje se privremeno ime tablice1. Izbrišite ga i imenujte. Kupac_info. (Koristite simbol zatvaranja umjesto razmaka). Kliknuti Ući.
Primijenite ime u svaku tablicuPonovite ove korake sa podacima o narudžbi i platne listove. Naziv tablice Red_info. i Informacije o plaćanju..
Sada smo spremni ubaciti pivottable.
Kutija pivottable.
Provjerite je li na listu za informacije o plaćanju kursor negdje u tablici. Povratak na karticu Umetanje. Vrpce i kliknite ikonu Pivottable (Ovo je prva ikona).
Pomoću kursora unutar jedne tablice odaberite Umetanje\u003e PivotTableU dijaloškom okviru koji se pojavljuje, morate ispravno definirati tablicu i odabrati taj okretni potezi na novi radni list. Ispod, odaberite potvrdni okvir Dodajte ove podatke u model podataka. Kliknuti UREDU..
Dodavanje podataka u model podataka je ono što omogućava veze za radSada ćete se pitili na novom listu, traka zadataka će biti na desnoj strani ekrana, a na traci će se pojaviti kartica analize.
Traka zadataka prikazuje tablicu i polje aktivnog lista, pa pritisnite Sve.Da biste vidjeli svu tablicu koju ste stvorili. Ali prije nego što ih možemo koristiti, moramo ih povezati jedni s drugima, a to znači stvaranje odnosa. Pritisnite tipku Odnosi. Na ploči za vrpcu.
Postavljanje odnosa tablice
Pritiskom na ovo dugme prikazuje dijaloški okvir Upravljanje odnosima. Pritisnite tipku Novo A pojavljuje se dijaloški okvir CREATE veze. Stvorit ćemo dva odnosa pomoću polja # # kao konektor.
Na padajuće liste odaberite Informacije o plaćanju. Za tablicu i pored njega odaberite Naručite # Na padajućoj listi stupca. U drugom retku odaberite Kupac_info. Sa padajuće tablice povezane sa liste, a pored njega odaberite Naručite # Iz padajućeg stupca povezanog sa liste.
Postoje tri stola, zato stvorite dva odnosa.To znači da su tablice za plaćanje_info i klijent_info povezane sa slučajem broja narudžbi.
Pritisnite tipku UREDU. I vidjet ćemo ta veza navedena u prozoru upravljanja vezama.
Ponovite ovaj postupak da biste stvorili vezu između plaćanja_info i narudžbe_info, takođe pomoću polja # #. Sada izgled veze u upravljanju odnosima:
U dijaloškom okviru MANAGE ODNOSI su prikazani odnosi koje ste stvorili.Imajte na umu da nema potrebe za stvaranjem odnosa između tablica za naručivanje_info i klijenta_info, jer se automatski povezuju putem tablice za plaćanje_info.
Pritisnite tipku Zatvoriti Na dnu prozora. Sada konačno možemo povući polja u Pivottableu.
Umetanje polja u pivottable
U svim odjeljku na traci zadataka kliknite na male strelice na, rasporedite tri tablice, pogledajte njihova polja. Povlačenje polja na području pivottable na sljedeći način:
- Stanje i Mesec. u žicama
- Proizvod u koloni
- $ Prodaja. U vrijednosti
- Status. U filtrima
Sada ga možete koristiti i promijeniti, kao i bilo koji drugi pivottable.
Zaključak
Upotreba novih funkcija modela podataka objekta u Excelu 2013, možete odabrati ružičaste polja nekoliko listova da biste stvorili jednu piva. Imajte na umu da su linije svakog stola moraju biti nekako povezane jedno s drugim. Imate više šansi za uspjeh, kada tablice imaju zajedničko polje s jedinstvenim vrijednostima.
Ako tražite dobre načine predaje svoje podatke, Envato tržište ima dobar izbor, kao i pretvoriti Excel podatke na web formati i obrnuto.
Iz ovog članka naučit ćete kako brzo kombinirati podatke iz dva Excel tablice kada nema tačnih slučajnosti u ključnim stupovima. Na primjer, kada je jedinstveni identifikator iz prve tablice prvih pet likova identifikatora iz druge tablice. Sva rješenja koja se nude u ovom članku testiraju me u Excelu 2013, 2010 i 2007.
Dakle, postoje dva Excel lista koja je potrebno kombinirati za daljnju analizu podataka. Pretpostavimo, u istoj tabeli sadrži cijene (stupac, cijena) i opisi proizvoda (pivski stupac) koji prodajete, a u drugom odražavate podatke o dostupnosti robe na zalihi (stupac na skladištu). Ako ste vi ili vaši kolege obračunali i tabele prema direktoriju, tada bi se barem jedan ključni stupac s jedinstvenim identifikatorima trebao biti prisutan na oba puta. Opis ili cijena proizvoda mogu se razlikovati, ali jedinstveni identifikator uvijek ostaje nepromijenjen.
Poteškoće počinju kada dobijete neke tablice proizvođača ili iz drugih odjela kompanije. Slučaj se može čak i postati složeniji ako se uvede novi format jedinstvenih identifikatora ili se mijenjaju nomenklara za skladištenje (SKU) (SKU). I prije nego što postoji zadatak za spajanje u Excelu novom i starom tablicom s podacima. Na ovaj ili onaj način, postoji situacija kada se u ključnim stupcima pojavljuje samo djelomična utakmica evidencija, na primjer, " 12345 "I" 12345-new_suffix.". Jasno ste da je to isto SKU, ali računar nije tako budan! To ne postoji tačno slučajnost nemoguće je koristiti uobičajene Excel formule za kombiniranje podataka iz dvije tablice.
I to je jako loše - usklađenost možda uopće nije i " Neko društvo"U jednoj tabeli se može pretvoriti u" CJSC "Neko društvo""U drugom stolu i" Nova kompanija (bivša kompanija)"I" Stara kompanija"Takođe će biti zapis iste kompanije. Poznato vam je, ali kako objasniti Excel?
Komentar: Rješenja opisana u ovom članku su univerzalna. Možete ih prilagoditi za daljnju upotrebu sa bilo kojim standardnim formulama, poput Prst (Vlookup), Pretražiti se (Utakmica), GPR (Hlookup) i tako dalje.
Odaberite odgovarajući primjer da biste odmah prešli na željeno rješenje:
Ključni stupac u jednom od tablica sadrži dodatne znakove.
Razmotrite dvije tablice. Stupci za prve tablice sadrže broj nomenklature (SKU), naziv piva (pivo) i njenu cijenu (cijenu). Druga tablica zabilježila je SKU i broj boca u skladištu (na lageru). Umjesto piva može biti bilo koji proizvod, a broj stupaca u stvarnom životu može biti mnogo više.
U tabeli sa dodatnim simbolima stvorite pomoćni stupac. Možete ga dodati na kraj stola, ali najbolje je umetnuti pored desne strane nakon što se ključ stupac bude na vidiku.
Ključ u tabeli u našem primjeru je kolona SVEDOK JOVANOVIĆ - ODGOVOR: Sa podacima SKU i morate ukloniti prvih 5 znakova iz njega. Dodajte pomoćni stupac i nazovite je SKU Helper:
Spremni! Sada imamo ključne stupce s tačnom slučajnošću vrijednosti - stupac SKU Helper U glavnom stolu i stupcu SKU. U tabeli u kojoj će se izvršiti pretraga.
Sada sa funkcijom Prst (Vlookup) dobit ćemo željeni rezultat:
Ostale formule
- Izdvojiti prvo H. Simboli s desne strane: Na primjer, 6 znakova s \u200b\u200bdesne strane unosa "DSFH-164900". Formula će izgledati ovako:
Prorakterv (A2; 6)
\u003d Desno (A2,6) - Preskočite prvo H. znakovi izdvajaju sljedeće Y. Simboli: Na primjer, morate ukloniti "0123" iz snimanja "prefix_0123_suff". Ovdje trebamo preskočiti prvih 8 znakova i izvući sljedeća 4 znaka. Formula će izgledati ovako:
Pstr (a2; 8; 4)
\u003d MID (A2.8,4) - Izdvojite sve znakove prije separatora, dužina rezultirajućeg slijeda može biti drugačija. Na primjer, morate izdvojiti "123456" i "0123" iz zapisa "123456-sufiks" i "0123----sufiks", respektivno. Formula će izgledati ovako:
Levsimv (A2; Pronađi ("-"; A2) -1)
\u003d Lijevo (A2, pronađite ("-", A2) -1)
Ukratko, možete koristiti Excel funkcije poput Levsimv (Levo), Pratseim (Desno) Pst (Sredina), NAĆI (Pronađite) za izdvajanje bilo kojeg dijela kompozitnog indeksa. Ako su se pojavile poteškoće -, potrudit ćemo sve od sebe da vam pomognemo.
Podaci iz stupca ključa u prvoj tablici podijeljeni su u dvije ili više stupaca u drugom stolu.
Pretpostavimo da se tablica u kojoj se vrši pretraga sadrži stupac s identifikatorima. U ćelijama ovog stupca sadrže Xxxx-yyyy.gde Xxxx - Ovo je oznaka koda grupe robe (mobilni telefoni, televizori, kamkorderi, kamere) i Yyyy. - Ovo je kod robe unutar grupe. Glavna tablica sastoji se od dva stupca: u jednom sadrže kodove robnih grupa (grupe), sekunde se bilježe u drugom (ID). Ne možemo jednostavno odbaciti kodekse grupa proizvoda, jer isti kôd proizvoda može se ponoviti u različitim grupama.
U ćeliji C2. Pišemo takvu formulu:
Ulov (A2; "-"; B2)
\u003d Spajanje (A2, "-", B2)
Ovdje A2. - Ovo je adresa ćelije koja sadrži grupni kod; Simbol " – "Je separator; B2. - Ovo je adresa ćelije koja sadrži kod proizvoda. Kopirajte formulu u preostale linije.
Sada kombiniraju podatke iz naših dva tablica neće biti teško. Uporedit ćemo stupac Potpuni ID prva tablica sa stupom Id drugi stol. Kada se zaključivanje slučajnode, snimaju iz stupaca Opis. i Cijena Drugi stol bit će dodan u prvu tablicu.
Podaci u ključnim stupcima se ne podudaraju
Evo primjera: Vi ste vlasnik male trgovine, nabavite robu iz jednog ili više dobavljača. Svaki od njih ima svoju nomenklaturu koja se razlikuje od vašeg. Kao rezultat toga, postoje situacije u kojima vaš "CASE-IP4S-01" "odgovara unosu" SPK-A1403 "u datoteci Excel primljenog od dobavljača. Takve razlike se javljaju nasumično i ne postoji zajedničko pravilo za automatsko pretvaranje "SPK-A1403" u "CASE-IP4S-01".
Loše vijesti: Podaci sadržani u ova dva tablica Excel morat će se ručno rukovati tako da je u budućnosti moguće kombinirati.
Dobre vijesti: To će morati učiniti samo jednom, a rezultirajući pomoćni tabl može se sačuvati na dalju upotrebu. Dalje možete automatski kombinirati ove tablice i uštedjeti tako
1. Napravite tablicu podružnice za pretraživanje.
Stvorite novi list Excela i nazovite je SKU pretvarač.. Kopirajte cijeli stupac Naš.sku. Od lima Čuvati. Za novi list uklanjamo duplikate i ostavljamo samo jedinstvene vrijednosti u njemu.
U blizini Dodaj stupac Supp.sku. i ručno tražite sukladnost između vrijednosti stupca Naš.sku. i Supp.sku. (Ovo će nam pomoći u opisima iz kolone Opis.). Ovo je dosadan radnik, neka ideju da ga morate ispuniti samo jednom :-).
Kao rezultat toga, imamo takav stol:
2. Ažuriramo glavnu tablicu pomoću podataka iz tablice za pretraživanje.
Do glavne tablice (prodavnica lima) umetnite novi stupac Supp.sku..
Sljedeći sa funkcijom Prst (Vlookup) Uporedite liste Čuvati. i SKU pretvarač. Naš.sku.i za ažurirane podatke - stupac Supp.sku..
Stupac Supp.sku. Ispunjen originalnim kodovima proizvođača.
Komentar: Ako u koloni Supp.sku. Bilo je praznih ćelija, a zatim trebate poduzeti sve kodove SKU.što odgovara tim praznim ćelijama dodaju ih u tablicu SKU pretvarač. i pronađite odgovarajući kod iz tablice dobavljača. Nakon toga ponovite korak 2.
3. Prenesite podatke sa tablice za pretraživanje na glavnu tablicu
U našoj glavnoj tabeli nalazi se ključni stupac s tačnom slučajnošću s elementima tablice za pretraživanje, tako da sada ovaj zadatak neće uzrokovati poteškoće
Korištenje funkcije Prst (VLookup) kombiniraju podatke o listu Čuvati. Sa podacima o listu Veleprodajnog dobavljača 1.Koristeći stupac za traženje utakmice Supp.sku..
Evo primjera ažuriranih podataka u koloni Veleprodajna cijena:
Sve je jednostavno, zar ne? Postavite svoja pitanja u komentarima članku, pokušat ću odgovoriti što je prije moguće.
Konsolidovane tablice su prikladne za upotrebu podataka i stvaranje izvještaja za analizu podataka. I ako su ovi relacijski podaci (I.E., oni pohranjeni u pojedinim tablicama, ali istovremeno se mogu kombinirati zbog općih vrijednosti), možete stvoriti takvu konsolidovanu tablicu u samo nekoliko minuta:
Šta je drugačije ovo okretno stolo? Imajte na umu da na popisu polja s desne strane ne prikaže se jedna tablica, već cijeli skup tablica. Svaka od ovih tablica sadrži polja koja se mogu kombinirati u jednu konsolidovanu tablicu za dobivanje različitih odjeljaka podataka. Nije potrebno ručno oblikovanje i priprema podataka. Odmah nakon uvoza podataka možete stvoriti sažetak tablicu na osnovu povezanih tablica.
Da biste kombinirali više tablica na popisu polja tablice okreta:
Ispod je postupak uvoza više tablica iz baze podataka SQL Server.
Napomena: Popis polja sadrži nekoliko tablica. Sve su to tablice koje ste odabrali tokom uvoza. Svaki se tablica može rasporediti i urušiti za prikaz svojih polja. Budući da su tablice povezane, možete kreirati konsolidiranu tablicu, što ste bacili polja iz bilo kojeg stola do područja Vrijednosti, Žice ili Stubovi.
Uvozi tablice iz drugih izvora
Pored SQL Servera, možete uvesti tablice iz niza drugih relacijskih baza podataka.
Relacijske baze podataka nisu jedini izvor podataka koji podržava rad sa više tablica na popisu polja okretnog tablica. Možete koristiti tablice u svojim knjigama ili uvoznim kanalima podataka, a zatim ih integrirati s drugim tablicama podataka u knjizi. Da bi se svi ti nepovezani podaci radili zajedno, svaka tablica dodaje se modelu podataka, a zatim kreirajte veze između njih koristeći odgovarajuće vrijednosti polja.
Pomoću modela podataka za kreiranje novog tablice okretnog pivota
Možda ste stvorili linkove između tablica u modelu podataka i sada su spremni koristiti ove podatke za analizu. Opisano je sljedeće kako stvoriti novi sažetak tablice ili sažetak dijagrama pomoću modela podataka u knjizi.
Vrlo često, prilikom generiranja izvještaja (analitičara) velikih količina podataka, morate pokrenuti informacije iz nekoliko listova knjige "Excel" u jednu tablicu.
U idealnom slučaju, za prikupljanje i analitiku informacija iz različitih listova "Excel", preporučljivo je napraviti sažetak tablice, koji će uzeti podatke iz više izvora (knjižični listovi).
Takav tablica može se izvesti na dva različita načina.
Razmotrite prvi način.
Stvaranje tablice okretnog tablice iz različitih listova pomoću standardnih funkcija i alata.
Prvi korak.
Morate dodati tipku na alat alata na alat za alat i grafikone na kasetu.
Da biste to učinili, kliknite Desnim klikom na vrpcu (alatne trake) i odaberite "Podešavanje vrpce" iz padajućeg izbornika
ili unesite karticu
"Datoteka" \u003d\u003e "Parametri" \u003d\u003e "Podešavanje trake".
Zatim, u postavkama vrpce sa padajuće liste u okviru "Select Commerds" INPlikacija odaberite "Sve naredbe". Prozor ispod prikazat će sve moguće naredbe koje se mogu postaviti na ploču po abecednom redu.
Sa liste odaberite "Master of Režetni stolovi i grafikoni"
U pravom prozoru pomoću gumba "Create Group" stvorite novu grupu alata. Za grupu možete odabrati praktično ime za vas. Na primjer, "njena grupa". Možete odabrati na kojoj će grupi kartice stvoriti. U mom primjeru odabrao sam karticu "Početna".
Kada se grupa kreira, istaknite ga sa kursorom, odaberite kursor "Rezime i grafikone" u lijevom prozoru i kliknite na dugme Dodaj \u003e\u003e.
Nakon klikne "U redu".
Sada je na glavnoj kartici alatne trake "sažetak i grafikoni".
Drugog koraka. Izgradite tablicu okretanja iz više izvora podataka.
Drugi način.
Stvaranje ciljane tablice podataka iz više listova pomoću upita formiranog u dodatku za napajanje.
Ova metoda je korištenje zahtjeva za dodatak upita za napajanje.
Izrada upita za energiju za više stranica knjige u jednom stolu.
Prvi korak.
Morate stvoriti dva zahtjeva, od kojih svaki će uzeti informacije iz zasebnog tablice.
Drugi korak.
Da biste to učinili, na kartici Power upit kliknite gumb "Sa tablice" i odredite raspon izvora podataka u prozoru koji se pojavljuje. Nakon tih kliknite "u redu."
Korak tri.
Kada je kreiran drugi zahtjev, morate kliknuti na gumb "Spajanje upita" na kartici Power Query i konfigurirati prikaz rezultirajuće zajedničke tablice u prozoru koji se pojavljuje.
Izvor podataka za konsolidovanu tablicu služi kao lista podataka, gdje, u pravilu, svaki stupac djeluje kao polje konsolidovane tablice. Ali šta ako ste došli k tebi, samo sa konsolidovanim, tablicom (formatirano je i izvana podsjeća na to, ali nemoguće je koristiti radne alate s konsolidovanim tablicama). I morate ga pretvoriti u listu podataka, I.E. Izvršite obrnutu operaciju. U ovom postu naučit ćete kako pretvoriti konsolidovanu tablicu s dvije varijable na listu podataka.
Na slici se prikazuje princip koji sam opisao. Oni. U asortimanu A2: E5 postoji izvorna rezime tablica koja se pretvara u listu podataka (H2: J14 raspona). Druga tablica predstavlja isti skup podataka, samo u drugom uglu. Svaka vrijednost konsolidovane tablice izvora izgleda kao niz koji se sastoji od polja u polju reda, polja stupca i odgovarajuće vrijednosti. Ovaj prikaz podataka koristan je kada je potrebno sortirati i manipulirati tim drugim metodama.
Da bismo shvatili mogućnost stvaranja takve liste, koristit ćemo alate tablice okreta. Dodajte dugme Majstor tablica za okretanje U HubBaru koji nam nije dostupan na vrpci, već je ostao kao rugiment iz starijih verzija Excela.
Slijedite karticu Datoteka -\u003e Parametri. U dijaloškom okviru koji se pojavljuje dijalog ParametriExcel,na kartici Brza pristupna pločau lijevom polju pronađite predmet Majstor sažetak tablica i grafikonai dodajte ga udesno. Kliknite U redu.
Sada na ploči prečaca imate novu ikonu.
Kliknite na ovu karticu da biste pokrenuli Majstor tablica za okretanje.
U prvom koraku čarobnjaka morate odabrati vrstu izvora podataka izvora podataka. Instalirajte prekidač U nekoliko raspona konsolidacije i zhmem. Dalje.
U koraku 2a navedite kako kreirati polja stranica. Stavite prekidač Kreirajte polja stranice -\u003e Dalje.
U koraku 2b, na terenu Domet Odaberite raspon koji sadrži podatke i kliknite Dodati.U našem slučaju ovo će biti lokacija izvornog sažetka tablice A1: E4.
U trećem koraku potrebno je odrediti gdje je potrebno postaviti tablicu za pljačke i kliknite Spremni.
Excel će stvoriti konsolidovanu tablicu s podacima. Na lijevoj strani ekrana vidjet ćete područje Lista polja tablice okreta.Uklonite sve stavke iz polja redaka i stupaca. Više detalja o pisao sam u prethodnom članku.
Imat ćete mali sažeti stol koji se sastoji od jedne ćelije, koja sadrži zbroj svih vrijednosti izvorne tablice.
Dvaput kliknite ovu ćeliju. Excel će stvoriti novi list koji će sadržavati tablicu s popisom vrijednosti.
Naslovi ove tablice su opće informacije, vjerovatno želite da ih učinite više informativnih.
