Nuoseklus „Excel“ kūrimas, duomenų darbas, pašalinimas

„Excel“ yra gana galingas įrankis, leidžiantis dirbti su didelėmis duomenų masyvais, atliekant su jais daugybę operacijų. Galima teigti, kad „Excel“ lentelės yra reliacinės duomenų bazės, su kuriomis galite dirbti neturėdami programuotojo įgūdžių. Bet jei programa naudojama profesionaliai, pagrindinių funkcijų nepakanka, kad būtų galima generuoti skirtingas struktūras. Šiems tikslams planšetininkų procesorius turi funkcionalesnį ir galingesnį įrankį - suvestinės lentelės.

Kodėl „Excel“ reikia suvestinių lentelių

Originale šis terminas skamba kaip „Pivot“ lentelė, o frazės vertimas yra šiek tiek miglotas: „Lentelė, kurią galima pakeisti, susukti, parodyti skirtingose ​​prognozėse“. Ką tai reiškia? Lentelių duomenų aiškinimas - užduotis nėra lengva. Paprastai tai atliekama naudojant formules, gautus galutinius rezultatus lentelės pabaigoje.

Jei jums reikia kurti sudėtingas ataskaitas naudojant filtrus ir pakeisti šaltinio duomenis tinkama seka, padarykite tai naudodami standartines „Excel“ priemones, jei įmanoma, labai sunku, tai labai sunku. Tačiau konsoliduotų lentelių dėka tokias ataskaitas yra daug lengviau sukurti, pavyzdžiui, įvairios diagramos, leidžiančios tik naudojant pelę grupėms ir stulpeliams, keisti jas vietose, naudoti įvairius valdiklius.

Daugelis „Excel“ nuolatinių vartotojų net nežino apie tokio galingo įrankio buvimą. Dėl aiškumo pateikiame nedidelį ir orientacinį „Excel“ konsoliduotų lentelių „manekenams“ naudojimo pavyzdį pavyzdį “.

Tarkime, kad esate įmonės vadovas ir esate atsakingas už pardavimo dokumentų tvarkymą. Visos operacijos įvedamos į lentelę, kurioje nurodoma visa informacija (pardavėjas, pirkėjas, kiekis, suma, pardavimo data, pirkėjo adresas ir kt. D.). Vadovas prašo pranešimo apie pardavimą pagal regioną.

Kaip mes tai darome? Mes sukuriame naujos lentelės išdėstymą, kurio dangteliu nurodome visą parduodamų prekių asortimentą ir eilutes - regionų pavadinimus. Norėdami tai padaryti, mes nukopijuojame iš šaltinio lentelės stulpelio su prekėmis, ištrindami dublikatus. Naudodamiesi specialaus įdėklo režimu, mes perkeliame šį stulpelį į konsoliduoto puslapio skrybėlę. Mes ateiname tuo pačiu būdu su regionais, tačiau kadangi jie yra konsoliduotoje lentelėje vertikaliai, mes tiesiog kopijuojame neperduodant.

Kiekvienoje kameroje mes apibendriname pajamas, naudodamiesi paraiškos funkcija. Galiausiai pridėkite galutinius rezultatus į apačią ir atsiųskite ataskaitą vadovui. Vadovas prašo regionų susitarti viršuje, o prekės - ant linijų. Jūs pateikiate naują ataskaitą per 5 minutes ir gaunate naują užduotį: pateikite pelno duomenis, o ne pajamas, tai yra, atsižvelgiant į išlaidas. Na, mes šiek tiek keičiame formulę ir vėlgi rezultatas yra paruoštas per kelias minutes. Galiausiai yra prašymas paruošti tokias ataskaitas kiekvienam pardavėjui, siekiant įvertinti jų efektyvumą. Ir tai nėra suvestinės lentelės problema, jei žinote, kur yra būtini pradiniai duomenys!

Tuo tarpu bet kuris „Excel“ vartotojas žino, kaip sunku atlikti tokias manipuliacijas šaltinio lentelės sistemoje.

Sukurti ir dirbti su konsoliduotomis lentelėmis „Excel“

Tarkime, kad turime pardavimo šaltinio lentelę:

Apsvarstykite, kaip sukurti konsoliduotą lentelę remiantis šiais lentelės duomenimis. Norėdami tai padaryti, pasirinkite bet kurį langelį, po kurio pagrindiniame skydelyje pasirinksime skirtuką „Įterpti“. Atsiras du papildomi skirtukai: „Summaro lentelė“ ir kairėje - „Rekomenduojamos suvestinės lentelės“. Jei esate pradedantysis ir nelabai suprantate, kaip galite sutvarkyti šaltinio duomenis, rekomenduojame naudoti antrą mygtuką. Tai meistras, kuris pasiūlys jums parengtas ataskaitų parinktis. Jums tereikia pasirinkti tinkamą parinktį ir gauti savo žinioje esančią baigtą konsoliduotą lentelę. Lieka ją ištaisyti tik tuo atveju, jei pagal kai kuriuos kriterijus ji jūsų netenkina.

Antrasis, pagrindinis konsoliduotų lentelių kūrimo būdas yra „nuo nulio“, kuriam paspaudžiame pirmą mygtuką.

Atidaromas naujas langas, kuriame reikia nustatyti du parametrus: pradinį langelio diapazoną ir ST vietą. Kadangi prieš sukuriant „Pivot“ lentelę pasirinkome langelį, diapazonas bus nustatytas automatiškai, numatytoji antrojo parametro vertė yra naujas lapas. Taigi mes nieko nekeitame ir spustelėjame gerai. Dėl to atsidarys naujas lapas su neišsamiu išdėstymu.

Norėdami jį sukonfigūruoti, naudokite dešinįjį skydą pavadinimu „Santraukos lentelės laukas“. Viršutiniame įrenginyje sąrašas galimų laukų, atitinkančių šaltinio lentelės stulpelį, sąrašas. Galite pridėti laukų dviem būdais: uždėdami varnelę priešais norimą vertę, ir tada jis automatiškai pateks į išdėstymą. Bet toli gražu ne visada reikalinga vieta. Tokiais atvejais jums tereikia vilkti lauką į reikiamą išdėstymo padėtį. Panašiai pašalinamas išdėstymo padėtis, pašalinant varnelę arba tempiant dešiniajame skydelyje.

Jame yra keturi regionai žemiau, pagal kuriuos bus sudaryta suvestinės lentelė. Apsvarstykite juos išsamiau:

• Vertybių sritis yra pagrindinė ST dalis, nes būtent čia nurodoma, kaip apdoroti stulpelius ir linijas. Paprastai tai yra apibendrinimas - šis agregacijos metodas bus nustatytas pagal numatytuosius nustatymus, su sąlyga, kad visos diapazono langeliai priima skaitmenines reikšmes. Esant bent vieną tuščią langelį arba su duomenimis tokiu formatu, kuris skiriasi nuo skaitinio, o ne ląstelės verčių sumos, bus apskaičiuotas jų skaičius, todėl į šį faktą reikia atsižvelgti. Mūsų pavyzdyje apibendrinimas bus atliktas kiekvienam produktui atitinkamame regione. Be apibendrinimo, galite nustatyti bet kokį kitą skaičiavimų metodą, turintį apie dvi dešimtis (apskaičiuoti vidutinę vertę, dalytis ir tt.). Lengviausias būdas tai padaryti yra tiesiogiai suvestinės lentelėje, spustelėjus bet kurią langelį PKM ir pasirenkant būtiną skaičiavimo būdą;
• Linijų plotas - susideda iš unikalių ląstelių, esančių kairiajame stulpelyje. Jei čia įterpiate kelis laukus, suvestinės lentelė nebebus plokščios, o daugialypės lygio;
• Stulpelio srityje taip pat yra lauko verčių, tačiau jau palyginti su pradinės lentelės viršutinė linija;
• Filtro sritis leidžia nustatyti apribojimus ląstelių verčių apskaičiavimo sąlygoms. Paprastai čia dedamas laukas, pagal kurį mėginys, nustatytas dar trijose srityse, turėtų būti filtruojamas.

Dėl šių sričių buvimo galite sukonfigūruoti pavyzdį iš beveik bet kokio sudėtingumo pradinės lentelės, praleisdami kelias minutes.

Kad tai būtų aiškiau, paaiškiname aukščiau pateiktą pavyzdį, aprašytą ankstesniame skyriuje.

Taigi, žingsnis -Py -step instrukcijos, kaip padaryti konsoliduotą lentelę „Excel“:

• Iš Laukų sąrašo suvestinės lentelės sąrašo mes vilkime lauką „Pajamos“ į vertybių sritį;
• Laukas „regionas“ (pirkėjo regionas) yra tempiamas į linijas;
• Lauko „produktas“, įdėtas į stulpelio sritį.

Tik trys veiksmai - ir suvestinė lentelė, tinkama siųsti valdžios institucijoms, yra parengtas! Mums tai prireikė maždaug 10 sekundžių.

Kaip įvykdyti kitus režisieriaus norus?

Norint gauti pelno, o ne pajamas, pakanka atitinkamos srities išdėstyti verčių srityje, o ne pajamas, atitinkami skaičiavimai suvestinėje bus automatiškai atlikti.

Antroji užduotis taip pat atliekama sekundėmis - norint pakeisti regionus ir prekes, tiesiog vilkite juos iš vienos srities į kitą (pakeisti vietas).

Norint gauti kiekvieno vadovo rezultatus, pakanka lauko „tvarkyklės“ filtro srityje, tada selektorius pasirodys viršuje, kuriame reikia nurodyti norimą pavardę, kad gautumėte ataskaitą. Yra galimybė sujungti ataskaitą apie kelis pardavėjus.

Žinoma, mes nepateikėme pačių sunkiausių, bet labai rodomų pavyzdžių, leidžiančių suprasti, kaip sukurti suvestinės lenteles „Excel“. Verčių apskaičiavimo ląstelėse metodas gali būti sudėtingesnis, jums gali tekti naudoti apskaičiuotus laukus, taip pat pritaikyti sąlyginį formatavimą - šiuo klausimu „Excel“ galimybės yra įspūdingos.

Apie duomenų šaltinį

Mes jau minėjome, kad pradiniams duomenims, naudojamiems tinkamai sukurti konsoliduotas lenteles „Excel“ programoje, yra nustatyti tam tikri reikalavimai. Išvardinkime juos:

• Pagrindinis apribojimas yra susijęs su privalomu vardų buvimu, palyginti su skaičiavimuose dalyvaujančiais stulpeliais. Tokie identifikatoriai yra būtini formuojant gautas ataskaitas - pridedant naujų įrašų (eilučių) prie pradinės lentelės, formatas nebūtinas norint pakeisti ST formatą, o rezultatai bus automatiškai perskaičiuoti;
• Įsitikinkite, kad mėginyje dalyvaujančių linijų ir stulpelių ląstelėse įvedami skaitiniai parametrai. Jei jos tuščios arba jose yra teksto vertės, šios eilutės iškris iš skaičiavimų, kurie iškreipia skaičiavimų rezultatus;
• Laikykitės eilučių formatų ir ląstelių turinio korespondencijos. Jei jis apibrėžiamas kaip data, tada visos stulpelio vertės turi turėti tą patį formatą, kitaip filtravimas ir klaidingas apskaičiavimas bus neteisingas.

Jei laikysitės šių taisyklių, tada neturėtų kilti suvestinės lentelės problemų.

Duomenų atnaujinimas „Excel Set“ lentelėje

Nors mes kalbėjome apie automatinį konsoliduotų lentelių perskaičiavimą atlikdami šaltinio duomenis ar pačios ST struktūrą, tiesą sakant, dėl kompiuterio išteklių išsaugojimo, turite atlikti tam tikrus veiksmus.

Duomenis galite atnaujinti mūsų ekspozicijos lentelėje „Excel“ dviem būdais:

• Spustelėjus tinkamą pelės mygtuką bet kuriame langelyje ir pasirinkus elementą „Atnaujinti“ kontekstiniame meniu;
• Naudojant skirtuką „Duomenys“ ir mygtuką „Atnaujinkite viską“.

Stalų talpykla yra ypač pateisinama, jei jos yra didelės, tokiu atveju programos greitis žymiai padidės, ypač jei reguliariai ir aukšto dažnio įvesite naujas linijas ir aukštą dažnį.

Stulpelio ar eilutės pridėjimas prie „Excel“

Apskritai, operacija yra nereikšminga, jei norimas parametras yra šaltinio lentelėje. Jums tereikia vilkti dešinįjį lauką į tinkamą plotą. Jei pradinėje lentelėje nėra eilutės ar stulpelio, pridėkite ją, jei reikia, įveskite, jei reikia. Tada spustelėkite skirtuką „Analizė“ ir atidarykite mūsų pakeistą lentelę. Lieka tik atnaujinti duomenis (žr. Ankstesnis skyrius), dėl to laukų sąrašas pasikeis dešiniajame ST skydelyje - prie jo bus pridėtas naujas parametras.

Dažnai suvestinės lentelėje nėra visai patogios vizualizacijos rūšių. Pavyzdžiui, kai eilutėse yra keli laukai. Tada vienas iš jų rodomas pilnai, o kito vertės turi būti nurodytos viršutinėje lentelės dalyje. Jei tokių verčių yra nedaug (tarkime, mūsų pardavimo regionų pavyzdyje tik 6), tada prasminga pridėti „Pivot“ lentelės stulpelio „regionus“ į atitinkamą plotą, perkeliant ją iš jos srities srities linijos.

Taip pat lengva pasiekti stulpelių sekimo procedūros pakeitimą, tiesiog perkelkite stulpelio antraštę į norimą padėtį su pele.

Santraukos lentelės formavimas iš kelių failų (lentelių) „Excel“

Tai sudėtingesnė, bet gana išsami užduotis. Naudokite „Master ST“, kurį reikia pridėti prie greitos prieigos skydelio.

Taigi, kairėje, šalia elemento „failo“, spustelėkite nukreiptą rodyklę, pasirinkite elementą „Kitos komandos“. Atidarys naują langą, viduriniame skydelyje spustelėjame parinktis „Visos komandos“. Sąraše, kuris atidaromas, pasirinkite jį, pasirinkite jį ir spustelėkite mygtuką „Pridėti“ ir spustelėkite mygtuką „Pridėti“ sąraše ir spustelėkite mygtuką „Pridėti“. Atitinkama piktograma pasirodys viršutiniame skydelyje.

Mes paleidžiame meistrą. Tarkime, kad turime derinti duomenis iš dviejų lapų, gegužės ir birželio mėn. Pagrindinė sąlyga yra ta, kad jie turi sutapti su struktūra. Jei taip, tada pirmajame pagrindinio lango lange pasirenkame parinktį „medžiagos lentelė“ ir kitą parametrą - „Keli konsolidavimo diapazonai“.

Antrame etape mes nurodome meistrui, kad mums reikia kelių laukų. Trečiajame - pasirinkite langelių diapazoną, skirtą apibendrinti pirmąjį lapą, spustelėkite „Pridėti“ ir padarykite tą patį, kad antrąjį lapą „Excel“. Dabar jums reikia duoti vardus dviem laukams. Pirmiausia tai padarome pirma. Tokiu atveju reikės nurodyti atitinkamos linijos diapazonų sąraše, pirmą ar antrą.

Spustelėjus mygtuką „Parengti“, atsidaro naujas langas, kuriame mes nurodome „Naujas lapas“ ir dar kartą paspauskite mygtuką „Parengti“.

Dėmesys. Daugialypių lentelių gavimas yra nepatogus tuo, kad sunku naršyti su daugybe laukų, ir reikia atlikti būtinus skaičiavimus, kiekvieną kartą nurodant reikiamas vertes iš viršutinės lentelės ploto.

Kaip pašalinti meną

Paprasčiausias atvejis - kai sukūrėte konsoliduotą lentelę, išsiuntė rezultatus virėjui, o jums to nebereikia. Jei esate tikri dėl to, tiesiog pasirinkite lentelę ir spustelėkite klavišą „Delete“. Paprasta ir efektyviai.

Bet staiga ateityje gali prireikti lentelės struktūros? „Excel“ turi galimybę ištrinti tik rezultatus arba duomenis iš langelių. Apsvarstykite, kaip tai daroma.

Norėdami ištrinti skaičiavimo rezultatus, atliekame šiuos veiksmus:

• Mes pažymime bet kurią ST ląstelę;
• Viršutiniame skydelyje pasirinkite skirtuką „Analizė“;
• Galimų veiksmų sąraše pasirinkite „Išvalyti“;
• Spustelėkite tašką „Išvalykite viską“.

Bet ką daryti, jei norite išsaugoti rezultatus, tačiau jums nereikia duomenų, tai yra, norite atlaisvinti lentelę? Ši padėtis dažnai iškyla, jei vadovybei reikia tik rezultatų. Veiksmų algoritmas:

• Dar kartą pasirinkite bet kurią langelį, spustelėkite skirtuką „Analizė“;
• Pasirinkite meniu „Veiksmas“, spustelėkite „Pasirinkite“, pažymėkite visą konsoliduotą lentelę su pele;
• Spustelėkite PCM pasirinktos srities viduje;
• Kontekstiniame meniu pasirinkite elementą „Kopijuoti“;
• Mes einame į skirtuką „Pagrindinis“, dar kartą spustelėkite PKM ir pasirinkite „Įterpti“;
• Pasirinkite skirtuką „Įterpti vertę“, jame atkreipiame dėmesį į parametrą „Įterpti kaip vertę“.

Dėl to santraukos lentelė bus ištrinta išlaikant rezultatus.

Patarimas. Galite pagreitinti procedūrą naudodami raktų derinį. Norėdami pasirinkti lentelę, naudokite Ctrl + A, kopijavimui - CTRL + C. Tada paspauskite Alt + E, Alt + S, Alt + V ir atlikite procedūrą paspausdami ENTER.

Norėdami pašalinti konsoliduotas lenteles „Excel 2007/2010“, turite naudoti kitą algoritmą:

• pasirinkite ST, skirta pašalinti;
• Spustelėkite skirtuką „Parametrai“;
• „Veiksmų grupėje“ pasirinkite elementą „Išvalyti“, tada - „Išvalykite viską“.

Jei jūsų viršininkas mėgsta duomenų vizualizaciją, akivaizdu, kad turėsite naudoti konsoliduotas diagramas. Kadangi jie užima daug vietos stale, po to, kai jie dažniausiai pašalinami.

Senosiose programos versijose, tam reikia pasirinkti schemą, spustelėkite skirtuką „Analizė“, pasirinkite duomenų grupę ir spustelėkite nuosekliai „išvalyti“ ir „išvalyti viską“.

Be to, jei schema yra susieta su suvestinės lentelės suvestine, po jos ištrynimo prarasite visus lentelės nustatymus, jos laukus ir formatavimą.

Jei norite, kad senesnės nei „Excel 2010“ versijos turi pasirinkti schemą, skirtuke „Analizė“ ir spustelėkite „Išvalyti“ ir „išvalyti viską“. Rezultatas bus panašus.

Tikimės, kad mūsų konsoliduotų lentelių pamokos leis jums atrasti šią pakankamai galingą funkcionalumą. Jei vis dar turite klausimų, užduokite juos komentaruose, pabandysime jiems atsakyti.