Dosledno ustvarjanje Excela, podatkovno delo, odstranjevanje

Excel je dokaj močno orodje, ki vam omogoča delo z velikimi podatkovnimi nizi in z njimi izvaja več operacij. Trdimo lahko, da so tabele Excela relacijske baze podatkov, s katerimi lahko delate, ne da bi imeli spretnosti programerja. Če pa se program profesionalno uporablja, osnovne funkcije niso dovolj za ustvarjanje poročil o različnih strukturah. Za te namene ima tablistični procesor bolj funkcionalno in močno orodje - povzetek tabel.

Zakaj Excel potrebuje povzete tabele

V izvirniku se ta izraz zveni kot vrtilna tabela, prevod besedne zveze pa je nekoliko meglen: "Tabela, ki jo je mogoče spremeniti, zviti, pokazati v različnih projekcijah". Kaj to pomeni? Interpretacija tabelarnih podatkov - naloga ni enostavna. To se ponavadi naredi s formulami, ki prejemajo končne rezultate na koncu tabele.

Če morate ustvariti zapletena poročila s pomočjo filtrov in spreminjanje izvornih podatkov v pravi zaporedju, to naredite s standardnimi sredstvi Excel, če je mogoče, je zelo težko. Toda zahvaljujoč konsolidiranim tabelam je takšna poročila veliko lažje ustvariti, kot različni diagrami, ki omogočajo samo s pomočjo miške v skupinske črte in stolpce, jih na mestih spreminjajo, uporabljajo različne kontrole.

Večina rednih uporabnikov Excela se sploh ne zaveda prisotnosti tako močnega orodja. Zaradi jasnosti dajemo majhen in indikativen primer uporabe konsolidiranih tabel Excela "za lutke".

Recimo, da ste vodja podjetja in ste odgovorni za vzdrževanje prodajne dokumentacije. Vse transakcije so vnesene v tabelo, ki označujejo vse podrobnosti (prodajalec, kupec, količina, znesek, datum prodaje, naslov kupca itd. D.). Vodja prosi za poročilo o prodaji po regiji.

Kako delamo? Ustvarimo postavitev nove tabele, v kapi, katere navajamo celoten obseg prodanega blaga in v linijah - imena regij. Če želite to narediti, kopiramo iz izvorne tabele A stolpca z blagom in brišemo dvojnika. Z uporabo načina posebnega vložka ta stolpec prenesemo v klobuk konsolidirane strani. Z regijami pridemo na enak način, a ker se nahajajo v konsolidirani tabeli navpično, preprosto kopiramo brez prenosa.

V vsaki celici povzamemo prihodke s funkcijo povzetka. Končno dodajte končne rezultate na dno in pošljite poročilo upravitelju. Vodja prosi regije, naj se dogovorijo na vrhu, in blago - na progah. Novo poročilo naredite v 5 minutah in dobite novo nalogo: zagotovite podatke o dobičku, ne prihodkov, torej ob upoštevanju stroškov. No, formulo malo spremenimo in spet je rezultat pripravljen v nekaj minutah. Končno obstaja zahteva za pripravo takšnih poročil za vsakega prodajalca, da oceni njihovo učinkovitost. In to ni problem za povzetek tabele, če veste, kje se nahajajo potrebni začetni podatki!

Medtem vsak uporabnik Excela ve, kako težko je izvajati takšne manipulacije v okviru izvorne tabele.

Ustvarjanje in delo s konsolidiranimi mizami v Excelu

Recimo, da imamo izvorno tabelo prodaje:

Razmislite, kako ustvariti konsolidirano tabelo na podlagi teh tabelarnih podatkov. Če želite to narediti, izberite katero koli celico, po kateri na glavni plošči izberemo zavihek »Vstavi«. Prikaže se dva dodatna zavihka: "Povzetek tabele" in levo - "Priporočene povzetke tabele". Če ste začetnik in ne razumete povsem, kako lahko organizirate izvorne podatke, priporočamo z drugim gumbom. To je mojster, ki vam bo ponudil pripravljene možnosti poročil. Izbrati morate samo ustrezno možnost in na razpolago dokončano konsolidirano mizo. Ostaja le, da jo popravi, če vas po nekaterih merilih ne zadovolji.

Drugi, glavni način za ustvarjanje konsolidiranih miz je "iz nič", za katerega pritisnemo prvi gumb.

Odpre se novo okno, v katerem je treba nastaviti dva parametra: začetni razpon celice in lokacijo st. Ker smo izbrali celico, preden smo ustvarili vrtilno tabelo, bo razpon določeno samodejno, privzeta vrednost za drugi parameter je nov list. Torej ničesar ne spremenimo in kliknite V redu. Kot rezultat, se odpre nov list z nepopolno postavitvijo.

Če ga želite konfigurirati, uporabite desno ploščo z naslovom "Polje povzetka tabele". Tu je v zgornji enoti seznam razpoložljivih polj, ki ustrezajo stolpcu izvorne tabele. Polja lahko dodate na dva načina: tako. Toda daleč od vedno do kraja, ki ga potrebujete. V takih primerih morate polje povleči v potreben položaj postavitve. Odstranjevanje položaja v postavitvi se izvede na podoben način, odstranite preverjanje ali vlečete na desno ploščo.

Vsebuje štiri regije spodaj, na podlagi katerih bo oblikovana povzetek tabele. Podrobneje upoštevajte:

• Področje vrednosti je glavni del ST, saj je tukaj navedeno, kako obdelati stolpce in črte. Običajno je to povzetek - ta metoda združevanja bo privzeto nastavljena, pod pogojem, da vse celice območja sprejemajo številčne vrednosti. V prisotnosti vsaj ene prazne celice ali s podatki v obliki, ki se razlikuje od številčne, namesto vsote vrednosti celic, bo njihovo število izračunano, zato je treba to dejstvo upoštevati. Za naš primer bo povzetek izvedeno za vsak izdelek v ustrezni regiji. Poleg seštevanja lahko nastavite tudi kateri koli drugi način izračuna, ki ima približno dva ducata (izračunavanje povprečne vrednosti, delež itd.). Najlažji način za to je neposredno v povzetku tabele, kliknete PKM za katero koli celico in izberete potrebno metodo izračunov;
• Območje vrstic - sestavljeno iz edinstvenih vrednosti celic, ki se nahajajo v levem stolpcu. Če tukaj vstavite več polj, povzetek tabele ne bo več ravna, ampak več -ravni;
• Območje stolpca vsebuje tudi vrednosti polja, vendar že glede na zgornjo črto začetne tabele;
• Področje filtra vam omogoča, da naložite omejitve glede izračuna vrednosti celic. Običajno je tu postavljeno polje, v skladu s katerim je treba filtrirati vzorec, nastavljen na treh drugih območjih.

Zahvaljujoč prisotnosti teh območij lahko vzorec konfigurirate iz izhodiščne tabele skoraj vsake zapletenosti in porabite nekaj minut.

Da bi bilo bolj jasno, razložimo zgoraj navedeno s primerom, opisanim v prejšnjem razdelku.

Torej, korak -v koraku navodila o tem, kako narediti konsolidirano mizo v Excelu:

• S seznama polj v povzetku tabele povlečemo polje "prihodke" v območje vrednosti;
• Polje "regija" (regija kupca) se vleče v črte;
• Polja "izdelek", postavljen v območje stolpca.

Samo tri dejanja - in povzetek tabele, primerne za pošiljanje organom, so pripravljene! Trajalo nam je približno 10 sekund, da smo ga sestavili.

Kako izpolniti druge želje direktorja?

Da bi dobili dobiček namesto prihodkov, je dovolj, da ustrezno polje postavite na območje vrednosti namesto prihodkov, bodo ustrezni izračuni v povzetku samodejno izvedeni.

Druga naloga se izvaja tudi v nekaj sekundah - spremeniti regije in blago, jih le povlecite z enega območja na drugo (menjava mesta).

Če želite doseči rezultate za vsakega upravitelja, je dovolj, da postavite polje "upravitelja" na območje filtra, nato pa se bo na vrhu prikazal izbirnik, na katerem morate določiti želeni priimek za prejemanje poročila. Obstaja priložnost za združevanje poročila o več prodajalcih.

Seveda nismo prinesli najtežjega, ampak zelo kazalnega primera, ki vam omogoča razumevanje, kako ustvariti povzete tabele v Excelu. Način izračuna vrednosti v celicah je lahko bolj zapleten, morda boste morali uporabiti izračunana polja in uporabiti pogojno oblikovanje - možnosti v tem pogledu v Excelu so impresivne.

O viru podatkov

Že smo že omenili, da so določene zahteve nalagane na začetne podatke, ki se uporabljajo za pravilno ustvarjanje konsolidiranih tabel v programu Excel. Naštejmo jih:

• Glavna omejitev je povezana z obvezno prisotnostjo imen nad stolpci, ki so vključeni v izračune. Takšni identifikatorji so potrebni za oblikovanje nastalih poročil - pri dodajanju novih zapisov (nizov) v izhodiščno tabelo ni potreben format za spremembo oblike ST, rezultati pa bodo samodejno preračunani;
• Prepričajte se, da se v celicah vrstic in stolpcev, ki sodelujejo v vzorcu, uvedejo numerični parametri. Če so prazne ali vsebujejo besedilne vrednosti, bodo te vrstice izpadle iz izračunov, kar bo izkrivilo rezultate izračunov;
• Sledite dopisovanju formatov vrstic in vsebini celic. Če je opredeljen kot datum, morajo imeti vse vrednosti v stolpcu isto obliko, sicer bo filtriranje in napačne izračune napačne.

Če se držite teh pravil, potem se z gradnjo povzetka tabele ne bi smele pojaviti.

Posodabljanje podatkov v tabeli Excel Set

Čeprav smo govorili o samodejnem preračunu konsolidiranih tabel pri prilagajanju izvornih podatkov ali strukturi samega ST, pravzaprav zaradi varčevanja računalniških virov morate izvesti določena dejanja.

Podatke lahko posodobite v naši tabeli izpostavljenosti Excel na dva načina:

• Kliknite z desnim gumbom miške v kateri koli celici in v kontekstnem meniju izberete element "Posodobitev";
• Uporaba zavihkov "Data" in gumba "Posodobi vse" ".

Predpomnjenje miz je še posebej upravičeno, če so velike velikosti, v tem primeru se bo hitrost programa znatno povečala, še posebej, če redno vstopate v nove linije in z visoko frekvenco.

Dodajanje stolpca ali vrstice v Excel

Na splošno je operacija nepomembna, če je želeni parameter v izvorni tabeli. Pravo polje morate samo povleči v pravo območje. Če v začetni tabeli ni niza ali stolpca, ga dodajte, po potrebi vnesite vrednosti. Nato kliknite zavihek "Analiza" in odprite našo spremenjeno tabelo. Ostaja samo za posodobitev podatkov (glej. Prejšnji razdelek) Posledično se bo seznam polj spremenil v desno ploščo ST - dodan bo nov parameter.

Pogosto povzetka tabela nima povsem priročne vrste za vizualizacijo. Na primer, ko je v vrsticah več polj. Potem je ena od njih prikazana v celoti, vrednosti drugega pa morajo biti navedene v zgornjem delu tabele. Če je malo takšnih vrednosti (recimo, v našem primeru prodajnih regij samo 6), je smiselno, da v ustrezno območje dodate stolpec "regije" vrtilne tabele in jo prenesete z območja črte.

Spreminjanje postopka za sledenje stolpcev je tudi enostavno doseči, preprosto prenos stolpca v želeni položaj z miško.

Oblikovanje povzetka tabele iz več datotek (tabel) Excel

To je bolj zapletena, a precej popolna naloga. Uporabite Master St, ki ga je treba dodati na ploščo za hitri dostop.

Torej, na levi, blizu elementa "Datoteka", kliknite puščico, usmerjeno navzdol, izberite element "Drugi ukazi". Odpre se novo okno, na srednji plošči kliknemo možnosti "Vse ekipe". Na seznamu "Master of konsolidiranih tabel" najdemo na seznamu, ki se odpre, izberite in kliknete gumb »Dodaj«. Ustrezna ikona se prikaže na zgornji plošči.

Zaženemo mojstra. Recimo, da moramo kombinirati podatke iz dveh listov, maja in junija. Glavni pogoj je, da morajo sovpadati v strukturi. Če je tako, potem v prvem oknu glavnega izberemo možnost "Tabela snovi" in naslednji parameter - "več konsolidacijskih razponov".

Na drugem koraku povemo mojsterju, da potrebujemo več polj. Na tretjem - izberite obseg celic za povzetek prvega lista, kliknite "Dodaj" in storite enako za drugi listni Excel. Zdaj morate imena dati dvema poljima. To naredimo najprej za prvo polje (kliknite izbirnik "1", v stolpcu "1 polja" vpišemo "lahko"), nato po analogiji za drugo. V tem primeru bo treba na seznamu razponov ustrezne vrstice navesti, prvo ali drugo.

Po kliku gumba "Ready" se odpre novo okno, v katerem označimo "nov list" in spet pritisnemo gumb "Ready".

Pozornost. Prejemanje večdimenzionalnih tabel je neprijetno, saj je težko krmariti z velikim številom polj, zato je treba izvesti potrebne izračune, pri čemer vsakič kažejo potrebne vrednosti iz zgornjega območja tabele.

Kako odstraniti umetnost

Najlažji primer - ko ste ustvarili konsolidirano mizo, je rezultate poslal kuharju in ga ne potrebujete več. Če ste prepričani v to, samo izberite tabelo in kliknite tipko Delete. Preprosto in učinkovito.

Toda nenadoma bo v prihodnosti morda potrebna struktura mize? Excel ima možnost izbrisati samo rezultate ali podatke iz celic. Razmislite, kako je to storjeno.

Za brisanje rezultatov izračuna izvajamo naslednje korake:

• Označujemo katero koli celico St;
• na zgornji plošči izberite zavihek »Analiza«;
• Na seznamu razpoložljivih dejanj izberite "Počisti";
• Kliknite na točko "Počisti vse".

Kaj pa storiti, če želite shraniti rezultate, vendar sami ne potrebujete podatkov, to je, da želite osvoboditi tabelo? Ta situacija se pogosto pojavi, če vodstvo potrebuje le rezultate. Algoritem dejanj:

• Ponovno izberite katero koli celico, kliknite na zavihek "Analiza";
• Izberite element menija "Action", kliknite "Izberi", z miško označite celotno konsolidirano tabelo;
• Kliknite PCM znotraj izbranega območja;
• V kontekstnem meniju izberite element "Kopiraj";
• Gremo na zavihek "Domači", znova kliknite PKM in izberite "Vstavi";
• Izberite zavihek »Vstavi vrednost«, v njem upoštevamo parameter "vstavljanje kot vrednost".

Kot rezultat, se bo povzetek tabela izbrisala, hkrati pa ohranjala rezultate.

Nasvet. Postopek lahko pospešite s kombinacijo tipk. Če želite izbrati tabelo, uporabite Ctrl + A, za kopiranje - Ctrl + C. Nato pritisnite Alt + E, ALT + S, ALT + V in zaključite postopek s pritiskom na Enter.

Če želite odstraniti konsolidirane tabele v Excelu 2007/2010, morate uporabiti drug algoritem:

• Izberite ST, zasnovano za odstranitev;
• Kliknite zavihek "Parametri";
• V "Skupini dejanj" izberite "čisti" izdelek in nato - "Očistite vse".

Če vaš šef ljubi vizualizacijo podatkov, je očitno, da boste morali uporabiti konsolidirane diagrame. Ker zavzemajo veliko prostora v mizi, jih po uporabi običajno odstranijo.

V starih različicah programa morate za to izbrati diagram, kliknite na zavihek "Analiza", izberite podatkovno skupino in zaporedno kliknite "Clean" in "Clean Everything".

Poleg tega, če je diagram povezan z največ povzetkom tabele, boste po izbrisu izgubili vse nastavitve tabele, njena polja in oblikovanje.

Za različice, starejše od Excela 2010, morate izbrati diagram, na zavihku "Analysis" izbrati "dejanja" in kliknite "čisto" in "Clean Everything". Rezultat bo podoben.

Upamo, da vam bodo naše lekcije o konsolidiranih tabelah omogočile odkrivanje te dovolj močne funkcionalnosti. Če imate še vedno vprašanja, jih vprašajte v komentarjih, jim bomo poskušali odgovoriti.