Järjepidev Exceli loomine, andmetöö, eemaldamine

Excel on üsna võimas tööriist, mis võimaldab teil töötada suurte andmemassiividega, tehes nendega mitmeid toiminguid. Võib väita, et Exceli tabelid on relatsioonilised andmebaasid, millega saate töötada ilma programmeerija oskusi omamata. Kuid kui programmi kasutatakse professionaalselt, ei piisa põhifunktsioonidest erinevate struktuuride aruannete genereerimiseks. Nendel eesmärkidel on tablisti protsessoril funktsionaalsem ja võimsam tööriist - kokkuvõtlikud tabelid.

Miks vajab Excel kokkuvõtlikke tabeleid

Originaalis kõlab see termin nagu pöördelaud ja fraasi tõlge on mõnevõrra udune: "Tabel, mida saab muuta, väänata, demonstreerida erinevates projektsioonides". Mida see tähendab? Tabeli andmete tõlgendamine - ülesanne pole lihtne. Tavaliselt tehakse seda valemite abil, saades lõpptulemused tabeli lõpus.

Kui peate filtrite abil looma keerukaid aruandeid ja muutma lähteandmeid õiges järjestuses, tehke see võimaluse korral tavaliste exceli vahenditega, see on väga keeruline. Kuid tänu konsolideeritud tabelitele on selliseid aruandeid palju lihtsam luua, nagu erinevad diagrammid, võimaldades ainult hiire abiga rühmitusi ja veerge.

Enamik Exceli tavakasutajaid pole isegi nii võimsa tööriista olemasolust teadlikud. Selguse huvides toome väikese ja soovitusliku näite Exceli konsolideeritud tabelite kasutamisest "mannekeenide jaoks".

Oletame, et olete ettevõtte juht ja vastutate müügitokumentide säilitamise eest. Kõik tehingud sisestatakse tabelis, mis näitab kõiki üksikasju (müüja, ostja, kogus, summa, müügikuupäev, ostja aadress jne. D.). Juht küsib regioonide müügi kohta aruannet.

Kuidas me seda teeme? Loome uue tabeli paigutuse, mille korki tähistame kogu müüdud kaupade valikut, ja piirkondade nimesid - piirkondade nimed. Selleks kopeerime lähtetabelist A veeru kaupadega, kustutades duplikaadid. Kasutades spetsiaalse sisetüki režiimi, ületame selle veeru konsolideeritud lehe mütsiks. Tuleme piirkondadega samamoodi, kuid kuna need asuvad konsolideeritud tabelis vertikaalselt, kopeerime lihtsalt ilma ülevõtmiseta.

Igas lahtris võtame tulu kokkuvõtte funktsiooni abil. Lõpuks lisage lõplikud tulemused põhja ja saatke aruanne haldurile. Juht palub piirkondadel korraldada tippu ja kaupu - liinidel. Teete uue aruande 5 minutiga ja saate uue ülesande: pakkuda kasumiandmeid, mitte tulu, see tähendab, et võttes arvesse kulusid. Noh, me muudame valemit pisut ja jälle on tulemus minutites valmis. Lõpuks on olemas taotlus iga müüja jaoks selliste aruannete koostamiseks, et hinnata nende tõhusust. Ja see pole kokkuvõtliku tabeli probleem, kui teate, kus asuvad vajalikud algsed andmed!

Vahepeal teab iga Exceli kasutaja, kui keeruline on selliste manipuleerimiste tegemine lähtetabeli raames.

Loomine ja töö konsolideeritud laudadega Excelis

Oletame, et meil on lähtetabeli müügi tabel:

Mõelge, kuidas luua konsolideeritud tabel nende tabeli andmete põhjal. Selleks valige suvaline lahter, mille järel valime peapaneeli vahekaardi "INSERT". Ilmub kaks täiendavat vahelehte: "kokkuvõtlik tabel" ja vasakule - "soovitatud kokkuvõtlikud tabelid". Kui olete algaja ja ei saa päris täpselt aru, kuidas saate lähteandmeid korraldada, soovitame kasutada teist nuppu. See on meister, kes pakub teile valmis reportaažide valikuid. Peate lihtsalt valima sobiva võimaluse ja hankima teie käsutatud valmis konsolideeritud tabeli. Jääb teda ainult parandama, kui ta mõne kriteeriumi kohaselt ei rahulda teid.

Teine, peamine viis konsolideeritud tabelite loomiseks on "nullist", mille jaoks vajutame esimest nuppu.

Avaneb uus aken, milles on vaja seada kaks parameetrit: lahtri algvahemik ja ST asukoht. Kuna valisime lahtri enne pöördetabeli loomist, määratakse vahemik automaatselt, teise parameetri vaikeväärtus on uus leht. Nii et me ei muuda midagi ja klõpsake nuppu OK. Selle tulemusel avaneb uus mittetäieliku paigutusega leht.

Selle konfigureerimiseks kasutage paremat paneeli pealkirjaga "Kokkuvõtliku tabeli väli". Ülemises seadmes on loetletud saadaolevate väljade loend, mis vastab lähtetabeli veerule. Välju saate lisada kahel viisil: pannes soovitud väärtuse vastas oleva märgistus ja seejärel satub see automaatselt paigutusse. Kuid kaugeltki alati vajalik kohta. Sellistel juhtudel peate lihtsalt välja lohistama paigutuse vajalikku asendit. Asetise paigutuse eemaldamine viiakse läbi sarnaselt.

See sisaldab allpool nelja piirkonda, mille põhjal moodustatakse kokkuvõtlik tabel. Mõelge neile üksikasjalikumalt:

• Väärtuste pindala on ST peamine osa, kuna just siin on näidatud, kuidas töödelda veerge ja ridu. Tavaliselt on see kokkuvõte - see agregatsioonimeetod seatakse vaikimisi, kui kõik vahemiku lahtrid aktsepteerivad numbrilisi väärtusi. Vähemalt ühe tühja lahtri olemasolul või andmetega vormingus, mis erineb numbrilisest, arvutatakse nende arvu asemel nende arv, seega tuleb seda fakti arvestada. Meie näites viiakse kokku summeerimine iga toote jaoks vastavas piirkonnas. Lisaks summeerimisele saate seada mis tahes muu arvutusmeetodi, millel on umbes kaks tosinat (arvutades keskmise väärtuse, osa jne.). Lihtsaim viis selleks on otse kokkuvõtlik tabelis, klõpsates mis tahes raku jaoks PKM -i ja valides vajaliku arvutusmeetodi;
• Liinide pindala - koosneb vasakpoolses veerus asuvate rakkude ainulaadsetest väärtustest. Kui sisestate siia mitu välja, ei ole kokkuvõtlik tabel enam tasane, vaid mitmetasandiline;
• Kolonni piirkond sisaldab ka välja väärtusi, kuid juba lähtetabeli ülemise rea suhtes;
• Filtripiirkond võimaldab teil kehtestada lahtrite väärtuste arvutamise tingimustele piiranguid. Tavaliselt pannakse siia väli, mille kohaselt tuleks filtreerida veel kolmes piirkonnas seatud proov.

Tänu nende piirkondade olemasolule saate konfigureerida proovi peaaegu igasuguse keerukusega, kulutades mõni minut.

Selle selgemaks muutmiseks selgitame ülaltoodu, kasutades eelmises jaotises kirjeldatud näidet.

Niisiis, samm -sammult juhised, kuidas teha konsolideeritud tabel Excelis:

• Kokkuvõtliku tabeli väljade loendist lohistame välja "tulu" väärtuste valdkonda;
• Väli "piirkond" (ostja piirkond) lohistatakse liinidesse;
• Põllu "Toode" paneb veeru piirkonda.

Ainult kolm toimingut - ja võimudele saatmiseks sobiv kokkuvõtlik tabel on valmis! Selle kompileerimiseks kulus meil umbes 10 sekundit.

Kuidas täita direktori muid soove?

Tulude asemel kasumi teenimiseks piisab vastava välja valimisest tulude asemel väärtuste valdkonda, kokkuvõtte vastavad arvutused tehakse automaatselt.

Teine ülesanne viiakse läbi ka sekunditega - piirkondade ja kaupade vahetamiseks lohistage need lihtsalt ühest piirkonnast teise (vahetage kohti).

Iga halduri tulemuste saamiseks on piisav väljale "Manager" filtripiirkonnale, seejärel ilmub ülaosas valija, milles peate aruande saamiseks täpsustama soovitud perekonnanime. On võimalus ühendada aruanne mitme müüja kohta.

Muidugi, me pole toonud kõige raskemat, vaid väga soovituslikku näidet, mis võimaldab teil mõista, kuidas Excelis kokkuvõtlikke tabeleid luua. Rakkude väärtuste arvutamise meetod võib olla keerukam, peate võib -olla kasutama arvutatud väljasid, samuti tingimuslikku vormindamist - võimalused selles osas Excelis on muljetavaldavad.

Andmeallika kohta

Oleme juba maininud, et Exceli programmi konsolideeritud tabelite õigeks loomiseks kasutatud esialgsetele andmetele kehtestatakse teatud nõuded. Loetleme need:

• Peamine piirang on seotud nimede kohustusliku esinemisega arvutustes osalevate veergudega. Sellised identifikaatorid on vajalikud saadud aruannete moodustamiseks - uute kirjete (stringide) algtabelisse lisamisel pole vorming ST -vormingu muutmiseks vajalik ja tulemused arvutatakse automaatselt ümber;
• Veenduge, et proovis osalevate ridade ja veergude lahtrites tutvustatakse numbrilisi parameetreid. Kui need on tühjad või sisaldavad teksti väärtusi, langevad need read arvutustest välja, mis moonutab arvutuste tulemusi;
• Järgige liinide vormingute ja lahtrite sisu kirjavahetust. Kui see on määratletud kuupäevana, peavad kõigil veeru väärtustel olema sama vorming, vastasel juhul on filtreerimine ja valearvestused valed.

Kui järgite neid reegleid, ei tohiks kokkuvõtlike lauaprobleemide koostamisega tekkida.

Andmete värskendamine Exceli seadistabelis

Kuigi rääkisime konsolideeritud tabelite automaatsest ümberarvutamisest, kui tehke kohandamist lähteandmete või ST enda struktuuriga, tegelikult arvutiressursside säästmise huvides peate tegema teatud toiminguid.

Andmeid saate värskendada meie kokkupuutetabelis Excelil kahel viisil:

• Klõpsake mis tahes lahtris hiire parema nupuga ja valige kontekstimenüüst "Värskendus";
• Kasutades vahekaarti "Andmed" ja nuppu "Värskendage kõike".

Tabelite vahemällu salvestamine on eriti õigustatud, kui need on suured, sel juhul suureneb programmi kiirus märkimisväärselt, eriti kui sisestate regulaarselt uusi liine ja kõrge sagedusega.

Veeru või rea lisamine Excelile

Üldiselt on toiming triviaalne, kui soovitud parameeter on lähtetabelis. Peate lihtsalt õige välja õigesse piirkonda lohistama. Kui algtabelis pole stringi ega veergu, lisage see, sisestage vajadusel väärtused. Järgmisena klõpsake vahekaardil "Analüüs" ja avage meie muudetud tabel. Jääb alles andmete värskendamine (vt. Eelmine jaotis) selle tulemusel muutub väljade loetelu ST paremas paneelis - sellele lisatakse uus parameeter.

Sageli pole kokkuvõtlikul tabelis visualiseerimiseks päris mugavaid liike. Näiteks kui liinidel on mitu välja. Siis kuvatakse üks neist täielikult ja teise väärtused tuleb näidata tabeli ülaosas. Kui selliseid väärtusi on vähe (näiteks meie müügipiirkondade näites 6), siis on mõistlik lisada vastavasse piirkonda pöördetabeli veerg "piirkonnad", kandes selle selle piirkonnast jooned.

Ka veergude järgimise protseduuri muutmine on lihtne saavutada, lihtsalt ülekandmine hiirega soovitud asendisse.

Mitme faili (tabelite) Exceli kokkuvõtliku tabeli moodustamine

See on keerulisem, kuid üsna täielik ülesanne. Kasutage Master ST, mis tuleb lisada kiire juurdepääsu paneelile.

Niisiis, vasakul, üksuse „faili” lähedal, klõpsake alla suunatud noolt, valige üksus “Muud käsud”. Avatakse uus aken, keskmises paneelis klõpsame suvanditel "Kõik meeskonnad". Leiame loendis „Konsolideeritud tabelite kapten” loendist, mis avaneb, valige see ja klõpsake nuppu „Lisa”. Vastav ikoon kuvatakse ülemises paneelis.

Me käivitame meistri. Oletame, et peame ühendama andmed kahest lehest, mai ja juunis. Peamine tingimus on see, et nad peavad kokku struktuuris. Kui jah, siis valime meistri esimeses aknas suvandi „aine tabel” ja järgmine parameeter - “mitu konsolideerimisvahemikku”.

Teises etapis osutame meistrile, et vajame mitu välja. Kolmandal - valige esimese lehe kokkuvõtmiseks lahtrite vahemik, klõpsake nuppu "Lisa" ja tehke sama ka teise lehe Exceli jaoks. Nüüd peate andma nimed kahele väljale. Teeme selle esimese välja jaoks kõigepealt (klõpsake nuppu valija "1", veerul 1 välja ", mille sisestame" mai "), siis analoogia abil teise jaoks. Sel juhul on vaja märkida vastava rea, esimene või teine.

Pärast nupul "Valmis" klõpsamist avaneb uus aken, milles tähistame "uut lehte" ja vajutame uuesti nuppu "Valmis".

Tähelepanu. Mitmemõõtmeliste tabelite kättesaamine on ebamugav, kuna suure hulga väljadega on keeruline navigeerida ja tuleb läbi viia vajalikud arvutused, iga kord, kui tabeli ülaosast on vaja vajalikke väärtusi.

Kuidas kunsti eemaldada

Kõige lihtsam juhtum - kui loosite konsolideeritud tabeli, saatis tulemused kokale ja te ei vaja seda enam. Kui olete selles kindel, valige lihtsalt tabel ja klõpsake nuppu Kustuta klahvi. Lihtne ja tulemuslikult.

Kuid äkki võib tulevikus olla vajalik laua struktuur? Excelil on võimalus kustutada ainult tulemused või andmed lahtritest. Mõelge, kuidas seda tehakse.

Arvutustulemuste kustutamiseks viime läbi järgmised sammud:

• Märgistame mis tahes ST raku;
• Valige ülemisel paneelil vahekaart "Analüüs";
• Valige saadaolevate toimingute loendist "CLEAR";
• Klõpsake punktil "Kustuta kõik".

Aga mida teha, kui soovite tulemusi salvestada, kuid te ei vaja ise andmeid, see tähendab, et soovite tabeli vabastada? See olukord tekib sageli, kui juhtkond vajab ainult tulemusi. Toimingute algoritm:

• Valige uuesti mis tahes lahter, klõpsake vahekaarti "Analüüs";
• Valige menüüelement "Toiming", klõpsake nuppu "Vali", märkige hiirega kogu konsolideeritud tabel;
• Klõpsake valitud piirkonna sees asuval PCM -is;
• Valige kontekstimenüüst üksus "Kopeeri";
• Me läheme vahekaardile "Kodu", klõpsake uuesti PKM -i ja valime "INSERT";
• Valige vahekaart "Sisesta väärtus", selles märgime parameetri "sisestage väärtus".

Selle tulemusel kustutatakse kokkuvõtlik tabel, säilitades samal ajal tulemused.

NÕUANNE. Saate protseduuri kiirendada, kasutades võtmekombinatsiooni. Tabeli valimiseks kasutage kopeerimiseks Ctrl + A. Seejärel vajutage Alt + E, Alt + S, Alt + V ja täitke protseduur, vajutades sisestusega.

Konsolideeritud tabelite eemaldamiseks Excel 2007/2010 peate kasutama mõnda muud algoritmi:

• Valige eemaldamiseks mõeldud St;
• Klõpsake vahekaarti "Parameetrid";
• Valige "Toimingute rühmas" üksus "Puhas" ja siis - "Puhastage kõik".

Kui teie ülemus armastab andmete visualiseerimist, on ilmne, et peate kasutama konsolideeritud diagramme. Kuna nad hõivavad lauale palju ruumi, eemaldatakse need pärast kasutamist tavaliselt.

Programmi vanades versioonides peate valima diagrammi vahekaardil "Analüüs", valige andmerühm ja klõpsake klõpsake järjestikku "Puhastage" ja "puhastage kõik".

Veelgi enam, kui diagramm on seotud kõige kokkuvõtlikuma tabeliga, kaotate pärast selle kustutamist kõik tabeli sätted, selle väljad ja vormindamise.

Excel 2010 -st vanemate versioonide jaoks peate valima diagrammi, valima vahekaardil "Analüüs" ja klõpsake nuppu "Puhastage" ja "Puhastage kõik". Tulemus on sarnane.

Loodame, et meie konsolideeritud tabelite õppetunnid võimaldavad teil avastada selle piisavalt võimsa funktsionaalsuse. Kui teil on endiselt küsimusi, küsige neid kommentaarides, proovime neile vastata.