Konzisztens Excel létrehozás, adatmunka, eltávolítás

Az Excel egy meglehetősen hatékony eszköz, amely lehetővé teszi, hogy nagy adatgyűjtővel dolgozzon, és velük számos műveletet hajtson végre. Vitatható, hogy az Excel táblák relációs adatbázisok, amelyekkel a programozó képességei nélkül dolgozhat. De ha a programot professzionálisan használják, az alapfunkciók nem elegendőek a különböző struktúrákról szóló jelentések készítéséhez. Ilyen célokra a tabletta processzor funkcionálisabb és hatékonyabb eszközzel rendelkezik - összefoglaló táblák.

Miért van szükség az Excelnek összefoglaló táblákra?

Az eredetiben ez a kifejezés úgy hangzik, mint a pivot táblázat, és a mondat fordítása kissé ködös: "Egy táblázat, amelyet megváltoztathatunk, csavart, különböző vetületekben mutatnak be". Mit is jelent ez? A táblázatos adatok értelmezése - A feladat nem könnyű. Ezt általában képletek felhasználásával végzik, és a táblázat végén kapják meg a végső eredményeket.

Ha összetett jelentéseket kell létrehoznia a szűrőkkel és a forrásadatok megváltoztatásának a megfelelő sorrendben történő megváltoztatásával, akkor ezt a szokásos Excel eszközökkel készítse el, ha lehetséges, nagyon nehéz. De a konszolidált táblázatoknak köszönhetően az ilyen jelentéseket sokkal könnyebben készíthetik, mint a különféle ábrák, csak egér segítségével a csoportvonalakhoz és oszlopokhoz, helyeken cseréljék őket, különféle kezelőszerveket használnak.

Az Excel rendszeres felhasználók többsége még nem is ismeri egy ilyen hatékony eszköz jelenlétét. Az érthetőség kedvéért egy kicsi és indikatív példát adunk az Excel konszolidált táblák "a bábukhoz" használatáról.

Tegyük fel, hogy Ön vállalati menedzser vagy, és felelős az értékesítési dokumentáció fenntartásáért. Az összes tranzakciót a táblázat tartalmazza, amely jelzi az összes részletet (eladó, vevő, mennyiség, összeg, eladási dátum, vevő cím stb. D.). A vezető jelentést kér a régiónkénti eladásokról.

Hogyan csináljuk? Készítünk egy új táblázat elrendezését, amelynek sapkájában jelöljük az eladott áruk teljes skáláját, és a sorokban - a régiók nevét. Ehhez a forrásasztalból egy oszlopból másolunk, a másolatokat töröljük. Egy speciális betét módját használjuk, ezt az oszlopot áthelyezzük a konszolidált oldal kalapjába. Ugyanúgy érkezünk a régiókkal, de mivel ezek függőlegesen a konszolidált táblázatban helyezkednek el, egyszerűen másolnánk az átültetés nélkül.

Minden cellában összefoglaljuk a bevételt a nyilvántartás funkciójával. Végül adja hozzá a végső eredményeket az aljára, és küldje el a jelentést a menedzsernek. A vezető arra kéri a régiókat, hogy rendezzék a tetejét, és az áruk - a vonalakon. 5 perc alatt készít egy új jelentést, és új feladatot kap: Biztosítson profit adatot, nem pedig a bevételt, vagyis a költségeket figyelembe véve. Nos, kissé megváltoztatjuk a képletet, és az eredmény ismét készen áll a percek alatt. Végül, kérést kapnak az ilyen jelentések elkészítésére minden eladó számára, hogy értékeljék azok hatékonyságát. És ez nem jelent problémát az összefoglaló táblán, ha tudja, hol található a szükséges kezdeti adatok!

Eközben minden Excel felhasználó tudja, milyen nehéz az ilyen manipulációk elvégzése a forrás táblázat keretein belül.

Létrehozás és munka az Excel konszolidált asztalokkal

Tegyük fel, hogy megvan az értékesítés forrás táblája:

Fontolja meg, hogyan lehet létrehozni egy konszolidált táblázatot ezen táblázatos adatok alapján. Ehhez válassza ki az esetleges cellát, amely után a főpanelben a "Insert" fület válassza ki. Két további lap jelenik meg: "Összefoglaló táblázat" és balra - "Ajánlott összefoglaló táblák". Ha kezdő vagy, és nem igazán érti, hogyan lehet megszervezni a forrásadatokat, javasoljuk a második gomb használatát. Ez egy mester, aki készen áll a kész jelentési lehetőségekre. Csak ki kell választania a megfelelő lehetőséget, és a rendelkezésre álló konszolidált táblázatot kell kapnia. Csak akkor marad kijavítani, ha bizonyos kritériumok szerint nem felel meg.

A konszolidált táblák létrehozásának második, fő módja a "From Scratch", amelyre megnyomjuk az első gombot.

Megnyílik egy új ablak, amelyben két paraméter beállításához szükséges: a cella kezdeti tartománya és az ST helye. Mivel egy cellát választottunk egy pivot táblázat létrehozása előtt, a tartomány automatikusan meghatározza, a második paraméter alapértelmezett értéke egy új lap. Tehát nem változtatunk semmit, és kattintson az OK gombra. Ennek eredményeként egy új, hiányos elrendezésű lap nyílik meg.

A konfiguráláshoz használja a "Összefoglaló táblázat mezőjének" címet. Itt a felső egységben felsorolva van a rendelkezésre álló mezők listája, amelyek megfelelnek a forrás táblázat oszlopának. Kétféle módon adhat hozzá mezőket: a kívánt értékkel ellentétes pilóta felvételével, majd automatikusan bekerül az elrendezésbe. De messze mindig attól a helytől, amelyre szüksége van. Ilyen esetekben csak az elrendezés szükséges helyzetébe kell húznia a mezőt. Az elrendezésben a helyzet eltávolítását hasonló módon hajtják végre, a pipa eltávolítását vagy a jobb oldali panelen húzva.

Az alábbiakban négy régiót tartalmaz, amelyek alapján összefoglaló táblázat alakul ki. Fontolja meg őket részletesebben:

• Az értékek területe az ST fő része, mivel itt jelzik, hogyan kell feldolgozni az oszlopokat és a vonalakat. Általában ez egy összegzés - ezt az aggregációs módszert alapértelmezés szerint beállítják, feltéve, hogy a tartomány minden cellája elfogadja a numerikus értékeket. Legalább egy üres cella vagy a numerikus formátumú adatok jelenlétében, a cellák összege helyett a numerikus. Példánkra az összegzést a megfelelő régió minden termékére elvégezzük. Az összegzés mellett beállíthat bármilyen más számítási módszert, amelyek körülbelül két tucat (az átlagos értéket, a részvényt stb. Számítják, stb.). Ennek legegyszerűbb módja közvetlenül az összefoglaló táblázatban, a PKM -re kattintva bármely cellához és a számítások szükséges módszerének kiválasztása;
• A vonalak területe - a bal oldali oszlopban található cellák egyedi értékeiből áll. Ha több mezőt illeszt be ide, akkor az összefoglaló táblázat már nem lesz lapos, hanem többszintű;
• Az oszlopterület mezőértékeket is tartalmaz, de már a kiindulási táblázat felső vonalához viszonyítva;
• A szűrőterület lehetővé teszi, hogy korlátozásokat írjon be a cellák értékeinek kiszámításának feltételeire. Általában egy mezőt helyeznek ide, amely szerint a három másik területen beállított mintát szűrni kell.

Ezen területek jelenlétének köszönhetően a mintát szinte bármilyen bonyolultság kiindulási táblázatából konfigurálhatja, néhány percet eltöltve.

Annak világosabbá tétele érdekében a fentieket az előző szakaszban ismertetett példával magyarázzuk.

Tehát, lépés -lépcső -utasítások az Excel konszolidált táblázatának elkészítéséhez:

• Az összefoglaló táblázatban szereplő mezők listájából a "bevételt" az értékek területére húzzuk;
• A "régió" (a vevő régiója) mezőt a vonalakba húzzák;
• A "termék" mezőbe helyezi az oszlopterületet.

Csak három művelet - és egy összefoglaló táblázat, amely alkalmas a hatóságoknak való elküldésre, készen áll! Körülbelül 10 másodpercbe telt, hogy összeállítsuk.

Hogyan lehet teljesíteni a rendező többi kívánságát?

Annak érdekében, hogy profitot szerezzenek a bevétel helyett, elég, hogy a megfelelő mezőt az értékek területére helyezzük a bevétel helyett, az összefoglalóban szereplő megfelelő számításokat automatikusan elkészítjük.

A második feladatot másodpercek alatt is elvégzik - a régiók és az áruk megváltoztatásához, csak húzza őket egyik területről a másikra (helyek cseréje).

Az egyes menedzserek eredményeinek eléréséhez elég, hogy a "Manager" mezőt a szűrő területére helyezzük, majd a választó megjelenik a tetején, amelyen meg kell határoznia a kívánt vezetéknevet a jelentés fogadásához. Lehetőség van arra, hogy több eladót kombináljanak.

Természetesen nem hoztuk be a legnehezebb, hanem egy nagyon indikatív példát, amely lehetővé teszi, hogy megértse, hogyan lehet az összefoglaló táblákat az Excel -ben létrehozni. A sejtekben lévő értékek kiszámításának módszere bonyolultabb lehet, lehet, hogy a kiszámított mezőket kell használnia, valamint feltételes formázást kell alkalmaznia - az Excel e tekintetében a lehetőségek lenyűgözőek.

Az adatforrásról

Már megemlítettük, hogy bizonyos követelményeket az Excel programban a konszolidált táblák helyes létrehozásához használt kezdeti adatokra vezetnek be. Soroljuk fel őket:

• A fő korlátozást a nevek kötelező jelenlétével társítják a számításokban részt vevő oszlopok felett. Ilyen azonosítók szükségesek a kapott jelentések kialakításához - amikor új rekordokat (karakterláncokat) adunk a kiindulási táblához, a formátum nem szükséges az ST formátum megváltoztatásához, és az eredményeket automatikusan újraszámolják;
• Győződjön meg arról, hogy a mintában részt vevő vonalak és oszlopok sejtjeiben numerikus paramétereket vezetnek be. Ha üresek vagy szöveges értékeket tartalmaznak, ezek a vonalak kiesnek a számításokból, amelyek torzítják a számítások eredményeit;
• Kövesse a vonalak formátumainak és a cellák tartalmának levelezését. Ha dátumként definiálják, akkor az oszlopban szereplő összes értéknek ugyanolyan formátumúnak kell lennie, az egyébként a szűrés és a téves számítások helytelenek lesznek.

Ha betartja ezeket a szabályokat, akkor az összefoglaló táblázatok felépítésével nem merülhet fel.

Adatok frissítése az Excel Set táblában

Bár beszéltünk a konszolidált táblák automatikus újraszámításáról, amikor a forrás adatait vagy az ST szerkezetét kiigazítják, valójában a számítógépes erőforrások megtakarítása miatt, bizonyos műveleteket kell végrehajtania.

Az Excel expozíciós táblázatban szereplő adatokat kétféleképpen frissítheti:

• Kattintson a jobb egérgombbal bármely cellán, és válassza ki a Környegység menüben a "Frissítés" elemet;
• Az "Data" fül és a "Minden frissítése" gomb használatával.

A táblák gyorsítótárazása különösen akkor indokolt, ha nagy méretűek, ebben az esetben a program sebessége jelentősen növekszik, különösen, ha rendszeresen és magas frekvenciájú új sorokat ír be.

Oszlop vagy vonal hozzáadása az Excelhez

Általában véve a művelet triviális, ha a kívánt paraméter a forrás táblázatban van. Csak a jobb mezőt kell húznia a megfelelő területre. Ha a kiindulási táblázatban nincs karakterlánc vagy oszlop, adja hozzá, adja meg, ha szükséges, az értékeket írja be. Ezután kattintson az "Elemzés" fülre, és nyissa meg a megváltozott táblánkat. Csak az adatok frissítése marad (lásd. Az előző szakasz), ennek eredményeként a mezők listája megváltozik az ST jobb oldalán - új paraméter kerül hozzáadásra.

Az összefoglaló táblázat gyakran nem rendelkezik elég kényelmes fajokkal a megjelenítéshez. Például, ha a sorokban több mező található. Ezután az egyiket teljes egészében megjelenítik, a másik értékeit a táblázat felső részén kell feltüntetni. Ha kevés ilyen érték van (mondjuk, csak az értékesítési régiók példájában 6), akkor érdemes a pivot táblázat oszlopának „régióit” hozzáadni a megfelelő területhez, átadva azt a területről a területről. vonal.

Az oszlopok követésének eljárásának megváltoztatása szintén könnyen elérhető, egyszerűen az egérrel a kívánt helyzetbe az egérrel való átadása az egérrel.

Összefoglaló táblázat kialakítása több fájlból (táblákból) Excel

Ez egy bonyolultabb, de meglehetősen teljes feladat. Használja a Master ST -t, amelyet hozzá kell adni a Fast Access Panelhez.

Tehát a bal oldalon, a „Fájl” elem közelében kattintson a lefelé irányított nyílra, válassza ki az „Egyéb parancsok” elemet. Új ablak nyílik meg, a középső panelen a "Minden csapat" lehetőségekre kattintunk. Megtaláljuk a megnyitó listában szereplő „Konszolidált táblák mestere” listájában, válassza ki, és kattintson a „Hozzáadás” gombra. A megfelelő ikon megjelenik a felső panelen.

Elindítjuk a Mestert. Tegyük fel, hogy kombinálnunk kell a két lapból származó adatot, májusban és júniusban. A fő állapot az, hogy a szerkezetben egybe kell egybeesniük. Ha igen, akkor a Mester első ablakában kiválasztjuk a „Szövetségi Táblázat” lehetőséget és a következő paramétert - „Több konszolidációs tartomány”.

A második lépésben a Mesternek jelezzük, hogy több mezőre van szükségünk. A harmadik oldalon - válassza ki az első lap összegzéséhez szükséges cellák tartományát, kattintson az "Add" gombra, és tegye ugyanezt a második Leaf Excelnél. Most a neveket két mezőre kell adnia. Ezt először az első mezőhöz csináljuk (kattintson az "1" választóra, az "1 mezőbe" beírjuk "), majd analóg módon, a másodikra. Ebben az esetben a megfelelő vonal, az első vagy a második tartományok listájában kell megjelölni.

Miután rákattintott a "Kész" gombra, egy új ablak nyílik meg, amelyben jelöljük az "Új Leaf" -t, és nyomja meg újra a "Kész" gombot.

FIGYELEM. A többdimenziós táblák kézhezvétele kényelmetlen, mivel nehéz navigálni számos mezővel, és a szükséges számításokat elvégezni kell, minden alkalommal, amely jelzi a táblázat felső részének szükséges értékeit.

Hogyan lehet eltávolítani a művészetet

A legegyszerűbb eset - amikor egy konszolidált táblázatot készített, az eredményeket elküldte a séfnek, és már nincs rá szüksége. Ha biztos benne, csak válassza ki a táblázatot, és kattintson a Törlés gombra. Egyszerű és hatékonyan.

De hirtelen szükség lehet az asztal szerkezetére a jövőben? Az Excelnek lehetősége van arra, hogy csak az eredményeket vagy az adatokat törölje a cellákból. Fontolja meg, hogyan történik ez.

A számítási eredmények törléséhez a következő lépéseket hajtjuk végre:

• Jelöljük az ST bármely celláját;
• Válassza ki az "Elemzés" fület a felső panelen;
• A rendelkezésre álló műveletek listájából válassza a "Törlés" lehetőséget;
• Kattintson a "mindent törölje" pontra.

De mit kell tennie, ha meg akarja menteni az eredményeket, de önmagában nincs szüksége adatokra, azaz fel akarja szabadítani a táblázatot? Ez a helyzet gyakran akkor merül fel, ha a vezetésnek csak eredményekre van szüksége. A műveletek algoritmusa:

• Ismét válassza ki a cellát, kattintson az "Elemzés" fülre;
• Válassza ki a "Művelet" menüpontot, kattintson a "Válassza" elemre, jelölje meg a teljes konszolidált táblázatot az egérrel;
• Kattintson a PCM -re a kiválasztott területen belül;
• A helyi menüből válassza ki a "Másolás" tételt;
• A "Home" fülre lépünk, kattintson újra a PKM -re, és válassza a "Beszúrás" lehetőséget;
• Válassza ki az "Érték beszúrása" fület, benne megjegyezzük a "beszúrás értékként" paramétert.

Ennek eredményeként az összefoglaló táblázat törlődik, miközben megőrzi az eredményeket.

TANÁCS. Felgyorsíthatja az eljárást a kulcskombináció használatával. A táblázat kiválasztásához használja a Ctrl + A -t, a másoláshoz - Ctrl + C. Ezután nyomja meg az alt + e, alt + s, alt + v gombot, és fejezze be az eljárást az Enter megnyomásával.

A konszolidált táblák eltávolításához az Excel 2007/2010 -en egy másik algoritmust kell használnia:

• Válassza ki az eltávolításra tervezett ST -t;
• Kattintson a "Paraméterek" fülre;
• A "Műveletek csoportjában" válassza ki a "Tiszta" elemet, majd - "Tisztítsa meg mindent".

Ha a főnöke szereti az adatok megjelenítését, nyilvánvaló, hogy konszolidált diagramokat kell használnia. Mivel sok helyet foglalnak el a táblázatban, használat után általában eltávolítják.

A program régi verzióiban, ehhez válasszon egy diagramot, kattintson az "Elemzés" fülre, válassza ki az adatcsoportot, majd kattintson a "Tisztítás" és a "Tisztítsa meg mindent" kattintson a "Tisztítsa meg" gombra.

Sőt, ha a diagram a legtöbb összefoglaló táblához kapcsolódik, a törlése után elveszíti a táblázat összes beállítását, a mezőket és a formázást.

Az Excel 2010 -nél régebbi verziókhoz válasszon egy diagramot, válassza a „Műveletek” lehetőséget az „Elemzés” lapon, majd kattintson a „Tiszta” és a „Tisztítsa meg mindent” gombra. Az eredmény hasonló lesz.

Reméljük, hogy a konszolidált táblákkal kapcsolatos tanulságaink lehetővé teszik, hogy felfedezzék ezt a kellően hatékony funkcionalitást. Ha még mindig vannak kérdései, tedd fel őket a megjegyzésekben, megpróbáljuk megválaszolni őket.