Dosljedno stvaranje Excela, rad podataka, uklanjanje

Excel je prilično moćan alat koji vam omogućuje rad s velikim podatkovnim nizovima, izvodeći brojne operacije s njima. Može se tvrditi da su Excel tablice relacijske baze podataka s kojima možete raditi bez posjedovanja vještina programera. Ali ako se program koristi profesionalno, osnovne funkcije nisu dovoljne za generiranje izvještaja o različitim strukturama. U te svrhe, parnistički procesor ima funkcionalniji i moćniji alat - sažetak tablica.

Zašto Excel trebaju sažetke stolova

U originalu, ovaj izraz zvuči kao tablica za okretanje, a prijevod fraze je pomalo Foggy: "Tablica koja se može promijeniti, uvrnuti, prikazana u različitim projekcijama". Što to znači? Tumačenje tabelarnih podataka - zadatak nije lak. To se obično radi pomoću formula, primajući konačne rezultate na kraju tablice.

Ako trebate stvoriti složena izvješća koristeći filtre i mijenjate izvorne podatke u pravom slijedu, napravite to sa standardnim sredstvima Excel, ako je moguće, vrlo je teško. Ali zahvaljujući konsolidiranim tablicama, takva je izvješća mnogo lakše stvoriti, poput različitih dijagrama, dopuštajući samo uz pomoć miša da grupira crte i stupce, promijenite ih na mjestima, koristite razne kontrole.

Većina Excel -ovih redovnih korisnika nije ni svjesna prisutnosti tako moćnog alata. Radi jasnoće, dajemo mali i indikativne primjer upotrebe Excel konsolidiranih tablica "za lutke".

Pretpostavimo da ste menadžer tvrtke i odgovorni ste za održavanje prodajne dokumentacije. Sve su transakcije unesene u tablicu koja označava sve detalje (prodavatelj, kupac, količina, iznos, datum prodaje, adresa kupca itd. D.). Vođa traži izvještaj o prodaji po regiji.

Kako radimo? Stvorimo izgled nove tablice, u kapici na kojem naznačimo cijeli asortiman prodane robe i u linijama - imena regija. Da bismo to učinili, iz izvorne tablice kopiramo stupac s robom, brišući duplikate. Koristeći način posebnog umetka, ovaj stupac prenosimo u šešir konsolidirane stranice. Dolazimo na isti način s regijama, ali budući da se nalaze u konsolidiranoj tablici okomito, jednostavno kopiramo bez transponiranja.

U svakoj ćeliji sažeti prihod koristeći funkciju samostala. Konačno, dodajte konačne rezultate na dno i pošaljite izvještaj upravitelju. Vođa traži od regija da se organiziraju na vrhu, a roba - na linijama. Novo izvješće napravite za 5 minuta i dobivate novi zadatak: pružite podatke o profitu, a ne prihod, odnosno uzimajući u obzir troškove. Pa, malo mijenjamo formulu, a rezultat je opet spreman za nekoliko minuta. Konačno, postoji zahtjev za pripremu takvih izvještaja za svakog prodavatelja kako bi se procijenila njihova učinkovitost. A to nije problem za sažetak tablice, ako znate gdje se nalaze potrebni početni podaci!

U međuvremenu, svaki korisnik Excela zna koliko je teško izvesti takve manipulacije u okviru izvorne tablice.

Stvaranje i rad s konsolidiranim tablicama u Excelu

Pretpostavimo da imamo izvornu tablicu prodaje:

Razmislite kako stvoriti konsolidiranu tablicu na temelju ovih tabelarnih podataka. Da biste to učinili, odaberite bilo koju ćeliju, nakon čega odaberemo karticu "Umetni" na glavnu ploču. Pojavit će se dvije dodatne kartice: "Sažetak tablice" i s lijeve strane - "Preporučene sažetke tablice". Ako ste početnik i ne razumijete sasvim kako možete organizirati izvorne podatke, preporučujemo da koristite drugi gumb. Ovo je majstor koji će vam ponuditi spreman -izrađuju mogućnosti izvještaja. Jednostavno morate odabrati odgovarajuću opciju i na raspolaganju dobiti gotovu konsolidiranu tablicu. Ostaje je samo ispraviti ako vas, prema nekim kriterijima.

Drugi, glavni način stvaranja konsolidiranih tablica je "ispočetka", za koji pritiskamo prvi gumb.

Otvara se novi prozor u kojem je potrebno postaviti dva parametra: početni raspon ćelije i mjesto ST. Budući da smo odabrali ćeliju prije izrade okretne tablice, raspon će se automatski odrediti, zadana vrijednost za drugi parametar je novi list. Dakle, ne mijenjamo ništa i kliknemo u redu. Kao rezultat toga, otvorit će se novi list s nepotpunim izgledom.

Da biste je konfigurirali, koristite desnu ploču pod nazivom "Polje tablice sažetka". Ovdje je u gornjoj jedinici popis navedenih popisa dostupnih polja koja odgovaraju stupcu izvorne tablice. Polja možete dodati na dva načina: stavljanjem kvačice nasuprot željenoj vrijednosti, a zatim će se automatski ući u izgled. Ali daleko od uvijek do mjesta koje vam treba. U takvim slučajevima samo trebate povući polje u potreban položaj izgleda. Uklanjanje položaja u izgledu provodi se na sličan način, uklanjanje oznake ili povlačenje na desnoj ploči.

Sadrži četiri regije u nastavku, na temelju kojih će se formirati sažetak tablice. Razmotrite ih detaljnije:

• Područje vrijednosti je glavni dio ST -a, jer je ovdje naznačeno kako obraditi stupce i crte. Obično je ovo zbroj - ova će metoda združivanja biti postavljena prema zadanim postavkama, pod uvjetom da sve stanice raspona prihvaćaju numeričke vrijednosti. U prisutnosti najmanje jedne prazne ćelije ili s podacima u formatu koji se razlikuje od numeričkog, umjesto zbroja vrijednosti ćelija, njihov će se broj izračunati, tako da se ta činjenica mora uzeti u obzir. Za naš primjer, zbroj će se provesti za svaki proizvod u odgovarajućem području. Osim zbrajanja, možete postaviti bilo koju drugu metodu izračuna koji imaju oko dvije desetine (izračunavanje prosječne vrijednosti, udio itd.). Najlakši način za to je izravno u tablici sažetka, klikom na PKM za bilo koju ćeliju i odabir potrebne metode izračuna;
• Područje linija - sastoji se od jedinstvenih vrijednosti ćelija smještenih u lijevom stupcu. Ako ovdje umetnete nekoliko polja, tablica sažetka više neće biti ravna, već na više nivoa;
• Područje stupca također sadrži vrijednosti polja, ali već u odnosu na gornju liniju početne tablice;
• Područje filtra omogućuje vam nametanje ograničenja uvjetima izračuna vrijednosti stanica. Obično se ovdje postavlja polje, prema kojem treba filtrirati uzorak postavljen u tri druga područja.

Zahvaljujući prisutnosti ovih područja, možete konfigurirati uzorak iz početne tablice gotovo bilo koje složenosti, trošiti nekoliko minuta.

Da bismo to učinili jasnijim, navedeno objašnjavamo primjerom opisanog u prethodnom odjeljku.

Dakle, koraka -Bep Upute o tome kako napraviti konsolidiranu tablicu u Excelu:

• S popisa polja u tablici sažetka povučemo polje "prihod" u područje vrijednosti;
• Polje "regija" (regija kupca) povlači se u linije;
• Polje "proizvod" stavite u područje stupca.

Samo su tri radnje - i sažetak tablice prikladna za slanje vlastima, spremne su! Trebalo nam je oko 10 sekundi da ga sastavimo.

Kako ispuniti ostale želje redatelja?

Da bi se ostvario profit umjesto prihoda, dovoljno je postaviti odgovarajuće polje u područje vrijednosti umjesto prihoda, odgovarajući proračuni u sažetku automatski će se izraditi.

Drugi se zadatak obavlja i u sekundi - da biste promijenili regije i robu, samo ih povucite iz jednog područja na drugo (Promjena mjesta).

Da biste dobili rezultate za svakog upravitelja, dovoljno je staviti polje "Manager" u područje filtra, a zatim će se pojaviti selektor na vrhu u kojem trebate odrediti željeno prezime da biste dobili izvješće. Postoji prilika za kombiniranje izvještaja o nekoliko prodavača.

Naravno, nismo donijeli najteži, ali vrlo indikativni primjer, koji vam omogućuje razumijevanje kako stvoriti sažetke tablice u Excelu. Metoda izračunavanja vrijednosti u ćelijama može biti zamršenija, možda ćete morati koristiti izračunata polja, kao i primijeniti uvjetno oblikovanje - mogućnosti u tom pogledu u Excelu su impresivne.

O izvoru podataka

Već smo spomenuli da su određeni zahtjevi nametnuti početnim podacima koji se koriste za pravilno stvaranje konsolidiranih tablica u programu Excel. Navedimo ih:

• Glavno ograničenje povezano je s obveznom prisutnošću imena u stupcima koji su uključeni u proračune. Takvi su identifikatori potrebni za formiranje rezultirajućih izvještaja - Prilikom dodavanja novih zapisa (niza) u početnu tablicu, format nije potreban za promjenu ST formata, a rezultati će se automatski preračunati;
• Provjerite jesu li u ćelijama linija i stupaca koji sudjeluju u uzorku unositi numerički parametri. Ako su prazne ili sadrže vrijednosti teksta, ove će linije ispasti iz izračuna, što će iskriviti rezultate izračuna;
• Slijedite korespondenciju formata linija i sadržaj stanica. Ako je definirano kao datum, tada sve vrijednosti u stupcu moraju imati isti format, inače će filtriranje i pogrešne izračuna biti pogrešne.

Ako se pridržavate ovih pravila, tada se ne bi trebalo pojaviti s konstrukcijom sažetog problema s tablicama.

Ažuriranje podataka u tablici Excel Set

Iako smo razgovarali o automatskoj preračunavanju konsolidiranih tablica prilikom prilagođavanja izvornim podacima ili strukturi samog ST -a, u stvari, iz razloga štednje računalnih resursa, morate izvesti određene radnje.

Podatke možete ažurirati u našoj tablici izloženosti Excel na dva načina:

• Klik s desnom gumbom miša na bilo kojoj ćeliji i odabirom stavke "UPDATE" u kontekstnom izborniku;
• Korištenje kartice "Data" i gumb "UPDATE SVE".

Predmemoriranje tablica posebno je opravdano ako su velike veličine, u ovom će se slučaju brzina programa značajno povećati, pogotovo ako redovito ulazite u nove linije i s visokom frekvencijom.

Dodavanje stupca ili retka u Excel

Općenito, operacija je trivijalna, ako je željeni parametar u izvornoj tablici. Jednostavno morate povući pravo polje u pravo područje. Ako u početnoj tablici nema niza ili stupca, dodajte ga, unesite, ako je potrebno, vrijednosti. Zatim kliknite karticu "Analiza" i otvorite našu promijenjenu tablicu. Ostaje samo ažuriranje podataka (vidi. Prethodni odjeljak), kao rezultat, popis polja promijenit će se na desnoj ploči ST - dodat će mu se novi parametar.

Često sažetak tablice nema baš povoljne vrste za vizualizaciju. Na primjer, kada u linijama ima nekoliko polja. Tada je jedan od njih prikazan u cijelosti, a vrijednosti drugog moraju biti naznačene u gornjem dijelu tablice. Ako je malo takvih vrijednosti (recimo, u našem primjeru prodajnih regija samo 6), tada ima smisla dodati stupac "regije" tablice okretne površine u odgovarajuće područje, prenoseći ga s područja crta.

Promjena postupka za praćenje stupaca također je lako postići, jednostavno prenošenje stupca u željeni položaj s mišem.

Formiranje sažetke tablice iz nekoliko datoteka (tablice) excel

Ovo je složeniji, ali sasvim cjelovit zadatak. Koristite Master ST, koji treba dodati na ploču s brzim pristupom.

Dakle, s lijeve strane, blizu stavke "Datoteka", kliknite na usmjerenu strelicu, odaberite stavku "Ostale naredbe". Otvorit će se novi prozor, na srednjoj ploči kliknemo na opcije "Svi timovi". Na popisu "Master of Consolidited Tables" nalazimo na popisu na popisu koji se otvara, odaberite i kliknite gumb "Dodaj". Odgovarajuća ikona pojavit će se na gornjoj ploči.

Pokrećemo majstora. Pretpostavimo da trebamo kombinirati podatke s dva listova, svibnja i u lipnju. Glavni je uvjet da se oni moraju poklapati u strukturi. Ako je tako, tada u prvom prozoru glavnog prozora odaberemo opciju "Tablica tvari" i sljedeći parametar - "Nekoliko raspona konsolidacije".

U drugom koraku, gospodaru ukazujemo da nam treba nekoliko polja. Na trećem - odaberite raspon ćelija za zbrajanje prvog lista, kliknite "Dodaj" i učinite isto za drugi list Excel. Sada trebate dati imena na dva polja. To radimo prvo za prvo polje (kliknite selektor "1", u stupcu "1 polja" ulazimo "svibanj"), a zatim, po analogiji, za drugo. U ovom će slučaju biti potrebno na popisu raspona navesti odgovarajuću liniju, prvu ili drugu.

Nakon što kliknete gumb "Ready", otvara se novi prozor u kojem naznačimo "novi list" i ponovo pritisnite gumb "Ready".

PAŽNJA. Primanje višedimenzionalnih tablica nezgodno je jer je teško kretati se s velikim brojem polja, a moraju se provesti potrebni proračuni, svaki put što ukazuje na potrebne vrijednosti iz gornjeg područja tablice.

Kako ukloniti umjetnost

Najlakši slučaj - kada ste stvorili konsolidiranu tablicu, poslali su rezultate kuharu, a više vam ne treba. Ako ste sigurni u to, samo odaberite tablicu i kliknite tipku Izbriši. Jednostavno i učinkovito.

Ali odjednom će ubuduće biti potrebna struktura tablice? Excel ima priliku izbrisati samo rezultate ili podatke iz stanica. Razmislite kako se to radi.

Da bismo izbrisali rezultate izračuna, izvodimo sljedeće korake:

• Označavamo bilo koju ćeliju ST;
• Odaberite karticu "Analiza" na gornjoj ploči;
• Na popisu dostupnih radnji odaberite "Clear";
• Kliknite na točku "Očistite sve".

Ali što učiniti ako želite spremiti rezultate, ali podaci vam ne trebaju sami, to jest, želite osloboditi tablicu? Ova se situacija često pojavljuje ako vodstvu trebaju samo rezultati. Algoritam djelovanja:

• Opet odaberite bilo koju ćeliju, kliknite karticu "Analiza";
• Odaberite stavku izbornika "Action", kliknite na "Odaberite", označite cijelu konsolidiranu tablicu s mišem;
• Kliknite PCM unutar odabranog područja;
• Iz kontekstnog izbornika odaberite stavku "Kopiraj";
• Idemo na karticu "Početna", ponovno kliknite PKM i odaberite "Umetni";
• Odaberite karticu "Umetni vrijednost", u njemu bilježimo parametar "Umetnite kao vrijednost".

Kao rezultat, sažetak će se izbrisati prilikom održavanja rezultata.

SAVJET. Postupak možete ubrzati pomoću kombinacije tipki. Za odabir tablice koristite ctrl + a, za kopiranje - ctrl + c. Zatim pritisnite alt + e, alt + s, alt + v i dovršite postupak pritiskom na Enter.

Da biste uklonili konsolidirane tablice u Excel 2007/2010, morate koristiti drugi algoritam:

• Odaberite ST dizajniran za uklanjanje;
• Kliknite karticu "Parametri";
• U "grupi radnji" odaberite "čistu" stavku, a zatim - "Očistite sve".

Ako vaš šef voli vizualizaciju podataka, očito je da ćete morati koristiti konsolidirane dijagrame. Budući da zauzimaju puno prostora u stolu, nakon upotrebe obično se uklanjaju.

U starim verzijama programa, za to morate odabrati dijagram, kliknite na karticu "Analiza", odaberite grupu podataka i kliknite uzastopno "Čisto" i "Očistite sve".

Nadalje, ako je dijagram povezan s tablicom sa najotpornim, nakon brisanja izgubit ćete sve postavke tablice, njegovih polja i oblikovanja.

Za verzije starije od Excel 2010, morate odabrati dijagram, odabrati "Akcije" na kartici "Analiza" i kliknite "Čisto" i "Čisti sve". Rezultat će biti sličan.

Nadamo se da će naše lekcije o konsolidiranim tablicama omogućiti da otkrijete ovu dovoljno snažnu funkcionalnost. Ako još uvijek imate pitanja, postavite ih u komentarima, pokušat ćemo im odgovoriti.