Nem ez az első alkalom, hogy Renat a Lifehacker vendégírója. Korábban kiváló anyagokat publikáltunk tőle az edzésterv elkészítéséhez: és online források, valamint az edzésterv elkészítéséhez.

Ez a cikk olyan egyszerű technikákat tartalmaz, amelyek lehetővé teszik. Különösen hasznosak azok számára, akik vezetői jelentéskészítéssel foglalkoznak, különféle elemző jelentéseket készítenek az 1C és más jelentések letöltése alapján, űrlapprezentációkat és diagramokat készítenek belőlük a menedzsment számára. Nem állítom, hogy abszolút újdonság lennék - ilyen vagy olyan formában ezek a technikák valószínűleg szóba kerültek a fórumokon ill.

A VLOOKUP és a HLOOKUP egyszerű alternatívái, ha a kívánt értékek nincsenek a táblázat első oszlopában: LOOKUP, INDEX + SEARCH

Eredmény:

V ebben az esetben, a CHAR funkció mellett (az idézőjel megjelenítéséhez) az IF funkciót használják, amivel a szöveget változtathatjuk attól függően, hogy van-e pozitív eladási trend, valamint a TEXT funkciót, amely lehetővé teszi a szám megjelenítését bármilyen formátumban. Szintaxisát az alábbiakban ismertetjük:

SZÖVEG ( jelentése; formátum)

A formátum idézőjelben van megadva, mintha egy egyéni formátumot írna be a Cellák formázása ablakban.

A bonyolultabb szövegek is automatizálhatók. Az én gyakorlatomban előfordult a vezetői jelentésekhez való hosszú, de rutinszerű megjegyzések automatizálása „MUTATÓ XX-al csökkent/növekedett a tervhez képest, főként az 1. TÉNYEZŐ XX-os növekedése/csökkenése, a FACTOR2 növekedése/csökkenése a tervhez képest. YY ...” változó faktorlistával. Ha gyakran írsz ilyen megjegyzéseket, és a megírásuk folyamata algoritmizálható, akkor érdemes egyszer egy rejtélyes képletet vagy makrót létrehozni, amely megspórol a munka legalább egy részét.

Hogyan tároljunk adatokat az egyes cellákban összefűzés után

A cellák egyesítésekor csak egy érték marad meg. Az Excel erre figyelmeztet a cellák egyesítésekor:

Ennek megfelelően, ha az egyes celláktól függően volt egy képlete, akkor az összevonásuk után leáll (# N / hiba a példa 3-4. sorában):

A cellák összefűzéséhez és az adatok megtartásához mindegyikben (talán van egy képlete, mint ebben az absztrakt példában; esetleg cellákat szeretne összefűzni, de az összes adatot a jövőre nézve megőrzi vagy szándékosan elrejti), fűzze össze a munkalap bármely celláját. , jelölje ki őket, majd a Format Painter paranccsal vigye át a formázást az egyesítendő cellákba:

Hogyan készítsünk pivotot több adatforrásból

Ha egyszerre több adatforrásból kell pivotot felépítenie, akkor hozzá kell adnia a szalaghoz vagy panelhez gyors hozzáférés"PivotTable and Chart Wizard", amely rendelkezik ilyen lehetőséggel.

Ezt a következőképpen teheti meg: "Fájl" → "Opciók" → "Gyorselérési eszköztár" → "Minden parancs" → "PivotTable és diagram varázsló" → "Hozzáadás":

Ezután egy megfelelő ikon jelenik meg a szalagon, amelyre kattintva ugyanazt a varázslót hívja meg:

Ha rákattint, megjelenik egy párbeszédpanel:

Ebben ki kell választania a "Több konszolidációs tartományban" elemet, és kattintson a "Tovább" gombra. A következő bekezdésben kiválaszthatja az "Egy oldalmező létrehozása" vagy az "Oldalmezők létrehozása" lehetőséget. Ha önállóan szeretne nevet adni az egyes adatforrásoknak, válassza ki a második elemet:

A következő ablakban adja hozzá az összes tartományt, amely alapján a pivot létrejön, és nevezze el őket:

Ezt követően az utolsó párbeszédpanelen adja meg, hogy a kimutatás hova kerüljön – egy meglévő vagy új lapon:

A kimutatástábla jelentés készen áll. Az „1. ​​oldal” szűrőben szükség esetén csak az egyik adatforrást választhatja ki:

Hogyan számítsuk ki az A szöveg előfordulásának számát a B szövegben ("MTS SuperMTS tarifa" - az MTS rövidítés két előfordulása)

Ebben a példában az A oszlop több szövegsort tartalmaz, és az a feladatunk, hogy megtudjuk, mindegyik hányszor tartalmazza az E1 cellában található keresett szöveget:

A probléma megoldásához használhat egy összetett képletet, amely a következő függvényekből áll:

1. DLSTR (LEN) - kiszámítja a szöveg hosszát, az egyetlen argumentum a szöveg. Példa: DLSTR ("gép") = 6.
2. SUBSTITUTE – egy adott szöveget lecserél egy szöveges karakterláncban egy másikra. Szintaxis: SUBSTITUTE ( szöveg; régi_szöveg; új_szöveg). Példa: SUBSTITUTE ("autó"; "auto"; "") = "mobil".
3. UPPER – a karakterlánc összes karakterét nagybetűre cseréli. Az egyetlen érv a szöveg. Példa: UPPER ("autó") = "CAR". Erre a függvényre szükségünk van a kis- és nagybetűket nem érzékeny keresésekhez. Végül is UPPER ("autó") = UPPER ("Gép")

Ha meg szeretné találni egy bizonyos szöveges karakterlánc előfordulását egy másikban, törölnie kell az összes előfordulását az eredetiből, és össze kell hasonlítania a kapott karakterlánc hosszát az eredetivel:

DLSTR („Tariff MTS Szuper MTS”) – DLSTR („Szuper tarifa”) = 6

Ezután elosztjuk ezt a különbséget a keresett karakterlánc hosszával:

6 / DLSTR („MTS”) = 2

Pontosan kétszer annyi, hogy az "MTS" sor szerepel az eredetiben.

Ezt az algoritmust a képletek nyelvén kell megírni (jelöljük "szöveggel" azt a szöveget, amelyben előfordulásokat keresünk, és azt, amelynek előfordulási száma érdekel):

= (DLSTR ( szöveg) -LSTR (helyettesítő (FELSŐ ( szöveg); FELSŐ ( keresett); ""))) / DLSTR ( keresett)

Példánkban a képlet így néz ki:

= (DLSTR (A2) -LSTR (HELYETTESÍTŐ (FELSŐ (A2); UPPER ($ E $ 1); ""))) / DLSTR ($ E $ 1)

Az Excel kimutatástáblái hatékony jelentéskészítő eszközt jelentenek. Különösen hasznos olyan esetekben, amikor a felhasználó nem dolgozik jól a képletekkel, és nehezen tudja önállóan elemezni az adatokat. Ebben a cikkben megvizsgáljuk, hogyan lehet helyesen létrehozni ilyen táblázatokat, és milyen lehetőségek vannak erre az Excel szerkesztőben. Ehhez nem kell fájlokat letöltenie. A képzés online elérhető.

Az első lépés egy tábla létrehozása. Kívánatos, hogy több oszlop legyen. Ebben az esetben bizonyos információkat meg kell ismételni, mivel csak ebben az esetben lehetséges a bevitt információk valamilyen elemzése.

Például vegye figyelembe ugyanazokat a pénzügyi költségeket különböző hónapokban.

Pivot táblák létrehozása

Egy ilyen asztal elkészítéséhez a következőket kell tennie.

1. Először is teljesen ki kell választania.

1. Ezután lépjen a "Beszúrás" fülre. Kattintson a "Táblázat" ikonra. A megjelenő menüben válassza ki a „Pivot Table” elemet.

1. Ennek eredményeként megjelenik egy ablak, amelyben meg kell adni több alapvető paramétert a pivot tábla felépítéséhez. Első lépésként ki kell választani azt az adatterületet, amely alapján az elemzést elvégezzük. Ha korábban kiválasztott egy táblázatot, akkor a rendszer automatikusan behelyettesíti a hivatkozást. Ellenkező esetben ki kell emelni.

1. Ezután meg kell adnia, hogy az építés pontosan hol fog megtörténni. Jobb a "Meglévő lapon" elemet választani, mivel kényelmetlen lesz az információ elemzése, ha minden több lapon szétszórva van. Ezután meg kell adnia a tartományt. Ehhez kattintson a beviteli mező melletti ikonra.

1. Közvetlenül ezután a Kimutatás varázsló kis méretűre összecsukódik. Ezen kívül a megjelenés kurzor. A bal egérgombbal kell kattintania az Ön számára megfelelő helyen.

1. Ennek eredményeként a megadott cellára mutató hivatkozás automatikusan lecserélődik. Ezután az ablak jobb oldalán található ikonra kell kattintania, hogy visszaállítsa az eredeti méretét.

1. A beállítások befejezéséhez kattintson az "OK" gombra.

1. Ennek eredményeként megjelenik egy üres sablon a pivot táblákkal való munkavégzéshez.

1. Ebben a szakaszban meg kell adnia, hogy melyik mező legyen:
   1. oszlop;
   2. húr;
   3. érték az elemzéshez.

Bármit választhatsz, amit akarsz. Minden attól függ, hogy milyen információt szeretne kapni.

1. Bármely mező hozzáadásához kattintson rá a bal gombbal, és az ujjának elengedése nélkül húzza a kívánt területre. Ebben az esetben a kurzor megjelenése megváltozik.

1. Csak akkor engedheti el az ujját, ha az áthúzott kör eltűnik. Hasonlóképpen át kell húznia a táblázatban található összes mezőt.

1. A teljes eredmény megtekintéséhez bezárhatja oldalsó panel beállítások. Ehhez egyszerűen kattintson a keresztre.

1. Ennek eredményeként a következőket fogja látni. Ezzel az eszközzel összesítheti az egyes pozíciókra vonatkozó kiadások összegét minden hónapban. Ezen túlmenően elérhető a teljes összegre vonatkozó információ.

1. Ha nem tetszik az asztal, megpróbálhatod egy kicsit másképp megépíteni. Ehhez meg kell változtatni a mezőket az építési területeken.

1. Zárja be ismét az építési segédet.

1. Ezúttal azt látjuk, hogy a pivot tábla sokkal nagyobb lett, hiszen most már nem hónapok, hanem költségkategóriák vannak az oszlopokban.

Ha nem tudja saját maga elkészíteni a táblázatot, mindig számíthat a szerkesztő segítségére. Az Excelben lehetőség van ilyen objektumok létrehozására automatikus üzemmód.

Ehhez a következőket kell tennie, de először válassza ki az összes információt a maga teljességében.

1. Kattintson a "Beszúrás" fülre. Ezután kattintson a "Táblázat" ikonra. A megjelenő menüben válassza ki a második elemet.

1. Közvetlenül ezután megjelenik egy ablak, amelyben különféle példák lesznek az építkezéshez. Hasonló lehetőségeket kínálnak több oszlop alapján. A sablonok száma közvetlenül függ a számuktól.

1. Ha az egérmutatót az egyes elemek fölé viszi, megjelenik az eredmény előnézete. Sokkal kényelmesebb így dolgozni.

1. Kiválaszthatja, hogy mi tetszik a legjobban.

1. A kiválasztott opció beillesztéséhez kattintson az "OK" gombra.

1. Ennek eredményeként a következő eredményt kapja.

Figyelem: a táblázat új lapon készült. Ez minden alkalommal megtörténik, amikor a konstruktort használják.

Miután hozzáadta (nem számít, hogyan) Pivot tábla, látni fogja az eszköztáron Új lap"Elemzés". Rengeteg különféle eszközt és funkciót tartalmaz.

Tekintsük mindegyiket részletesebben.

A képernyőképen megjelölt gombra kattintva a következőket teheti:

• változtassa meg a nevet;

• hívja a beállítások ablakot.

Az opciók ablakában sok érdekes dolgot fog látni.

Aktív mező

Ezzel az eszközzel a következőket teheti:

1. Először ki kell választania egy cellát. Ezután kattintson az "Aktív mező" gombra. A megjelenő menüben kattintson a "Mezőparaméterek" elemre.

1. Közvetlenül ezután a következő ablakot fogja látni. Itt adhatja meg a művelet típusát, amellyel a kiválasztott mezőben az adatokat egyenlíteni kell.

1. Ezenkívül személyre szabhatja a számformátumot. Ehhez kattintson a megfelelő gombra.

1. Ennek eredményeként megjelenik a "Cellák formázása" ablak.

Itt adhatja meg, hogy az információelemzés eredményét milyen formában kívánja megjeleníteni.

Ennek az eszköznek köszönhetően testreszabhatja a csoportosítást a kiválasztott értékek alapján.

Szelet beszúrása

Szerkesztő Microsoft Excel lehetővé teszi interaktív pivot táblák létrehozását. Ugyanakkor nem kell semmi bonyolultat tennie.

1. Válasszon egy oszlopot. Ezután kattintson a "Szeletelő beszúrása" gombra.
2. A megjelenő ablakban példaként válasszon egyet a javasolt mezők közül (a jövőben korlátlan számban választhatja ki). Miután kiválasztott valamit, az "OK" gomb azonnal aktiválódik. Kattintson rá.

1. Ennek eredményeként megjelenik egy kis ablak, amely bárhová áthelyezhető. Minden lehetségest felkínál egyedi értékek amelyek ezen a területen vannak. Ennek az eszköznek köszönhetően az összeget csak bizonyos hónapokra veheti fel (ebben az esetben). Alapértelmezés szerint a teljes időre vonatkozó információk jelennek meg.

1. Bármelyik elemre kattinthat. Közvetlenül ezután az Összeg mezőben lévő összes érték megváltozik.

1. Így bármilyen időtartamot kiválaszthat.

1. Minden bármikor visszaállítható az eredeti formájába. Ehhez kattintson a jobb oldalon található ikonra felső sarok ezt az ablakot.

Ebben az esetben a jelentést havi bontásban tudtuk rendezni, mivel rendelkeztünk megfelelő mezővel. De van egy erősebb eszköz a dátumokkal való munkához.

Ha rákattint a megfelelő gombra az eszköztáron, akkor valószínűleg ezt a hibát fogja látni. A tény az, hogy a táblázatunkban nincsenek olyan cellák, amelyeknek kifejezett "Dátum" adatformátuma lenne.

Példaként hozzunk létre egy kis táblázatot különböző dátumokkal.

Ezután létre kell hoznia egy pivot táblát.

Lépjen újra a "Beszúrás" fülre. Kattintson a "Táblázat" ikonra. A megjelenő almenüben válassza ki a számunkra szükséges opciót.

1. Ezután a rendszer megkér minket, hogy válasszunk ki egy értéktartományt.

1. Ehhez elegendő a teljes táblázatot kiválasztani.

1. Közvetlenül ezután a cím automatikusan lecserélődik. Itt minden nagyon egyszerű, mivel bábokhoz tervezték. Az építés befejezéséhez kattintson az "OK" gombra.

1. Az Excel szerkesztő csak egy lehetőséget kínál, mivel a táblázat nagyon egyszerű (például nem kell több).

1. Próbálja újra az "Idővonal beszúrása" ikonra kattintva (az "Elemzés" lapon található).

1. Ezúttal nem lesz hiba. A rendszer kéri, hogy válasszon ki egy mezőt a rendezéshez. Jelölje be a négyzetet, és kattintson az "OK" gombra.

1. Ennek köszönhetően megjelenik egy ablak, amelyben a csúszkával kiválaszthatja a kívánt dátumot.

1. Másik hónapot választunk, és nincs adat, mivel a táblázatban minden költség csak márciusra van feltüntetve.

Ha bármilyen változtatást hajtott végre az eredeti adatokon, és azok valamilyen oknál fogva nem jelentek meg a pivot táblában, bármikor frissítheti manuálisan. Ehhez egyszerűen kattintson a megfelelő gombra az eszköztáron.

Ha úgy dönt, hogy módosítja azokat a mezőket, amelyek alapján az építés megtörténik, akkor ezt sokkal egyszerűbb megtenni a beállításokban, és nem törölni és újra létrehozni a táblázatot az új beállítások figyelembevételével.

Ehhez kattintson az "Adatforrás" ikonra. Ezután válassza ki az azonos nevű menüpontot.

Ennek eredményeként megjelenik egy ablak, amelyben újra kiválaszthatja a szükséges mennyiségű információt.

Akciók

Ezzel az eszközzel a következőket teheti:

• tisztítsd le az asztalt;
• Kiemel;
• mozgatni.

Számítások

Ha a táblázatban szereplő számítások nem elegendőek, vagy nem felelnek meg az Ön igényeinek, bármikor módosíthatja. Az eszköz ikonjára kattintva a következő lehetőségeket láthatja.

Ezek tartalmazzák:

• számított mező;

• számított objektum;

• a számítások sorrendje (a hozzáadott képletek megjelennek a listában);

• képletek megjelenítése (nincs információ, mivel nincsenek hozzáadott képletek).

Itt létrehozhat egy kimutatásdiagramot, vagy módosíthatja az ajánlott táblázat típusát.

Ezzel az eszközzel testreszabhatja a szerkesztő munkaterületének megjelenését.

Ennek köszönhetően a következőket teheti:

• testreszabhatja az oldalsáv megjelenítését a mezők listájával;

• engedélyezze vagy tiltsa le a plusz / min gombokat;

• testreszabhatja a mezőfejlécek megjelenítését.

Amikor pivot táblákkal dolgozik, az "Elemzés" lapon kívül egy másik is megjelenik - "Tervezés". Itt a felismerhetetlenségig megváltoztathatja az objektum megjelenését az alapértelmezettről.

Testreszabhatja:

• részösszegek:
  • ne mutasd;
  • az összes összeg megjelenítése alul;
  • az összes összeg megjelenítése a fejlécben.

• általános eredmények:
  • letiltása soroknál és oszlopoknál;
  • sorok és oszlopok engedélyezése;
  • engedélyezése csak a karakterláncokhoz;
  • csak oszlopokhoz engedélyezze.

• jelentés elrendezése:
  • tömör formában mutassa meg;
  • szerkezeti formában mutatják meg;
  • táblázatos formában mutatni;
  • ismételje meg az összes tételcímkét;
  • ne ismételje meg a tételcímkéket.

• üres sorok:
  • szúrjon be egy üres sort minden elem után;
  • minden elem után távolítsa el az üres sort.

• pivot tábla stílusparaméterei (itt engedélyezheti/letilthatja az egyes elemeket):
  • sorfejlécek;
  • oszlopfejlécek;
  • váltakozó vonalak;
  • váltakozó oszlopok.

• testreszabhatja az elemek stílusát.

Ha több különböző lehetőséget szeretne látni, kattintson az eszköz jobb alsó sarkában lévő háromszögre.

Közvetlenül ezután egy hatalmas lista jelenik meg. Választhat, amit szeretne. Amikor az egérmutatót az egyes sablonok fölé viszi, a táblázat megváltozik (ez a következőhöz történik: előnézet). A változtatások mindaddig nem lépnek életbe, amíg rá nem kattint a megadott lehetőségek valamelyikére.

Ezenkívül, ha szeretné, létrehozhatja saját tervezési stílusát.

Itt módosíthatja a sorok megjelenítési sorrendjét is. Néha erre a költségelemzés kényelme érdekében van szükség. Főleg, ha a lista nagyon nagy, hiszen könnyebb megtalálni a kívánt pozíciót ábécé sorrendben, mint többször végiggörgetni a listát.

Ehhez a következőket kell tennie.

1. Kattintson a szükséges mező melletti háromszögre.
2. Ennek eredményeként a következő menü jelenik meg. Itt kiválaszthatja a kívánt rendezési opciót ("A-tól Z-ig" vagy "Z-től A-ig").

Ha a standard opció nem elegendő, kattintson a „ Extra lehetőségek válogatás ".

Ennek eredményeként a következő ablakot fogja látni. A részletesebb beállításokhoz kattintson a "Speciális" gombra.

Itt minden automatikusan be van állítva. Ha törli a jelölőnégyzetet, akkor megadhatja a szükséges kulcsot.

Pivot táblák az Excel 2003-ban

A fenti lépések a modern szerkesztők számára alkalmasak (2007, 2010, 2013 és 2016). V régi verzió minden másképp néz ki. Természetesen ott sokkal kevesebb lehetőség van.

Ahhoz, hogy az Excel 2003-ban pivot táblát hozzon létre, a következőket kell tennie.

1. Lépjen a menü "Adatok" részébe, és válassza ki a megfelelő elemet.

1. Ennek eredményeként megjelenik egy varázsló az ilyen objektumok létrehozásához.

1. A "Tovább" gombra kattintás után megnyílik egy ablak, amelyben meg kell adnia a cellák tartományát. Ezután kattintson ismét a "Tovább" gombra.

1. A beállítások befejezéséhez kattintson a "Befejezés" gombra.

1. Ennek eredményeként a következőket fogja látni. Itt át kell húznia a mezőket a megfelelő területekre.

1. Például a következő eredményt kaphatja.

Nyilvánvalóvá válik, hogy sokkal jobb ilyen jelentéseket készíteni a modern szerkesztőkben.

Következtetés

Ez a cikk bemutatta az Excel kimutatási tábláival való munka minden bonyolultságát. Ha valami nem működik az Ön számára, lehet, hogy rossz mezőket választott ki, vagy nagyon kevés van belőlük - egy ilyen objektum létrehozásához több, ismétlődő értékkel rendelkező oszlopra van szüksége.

Ha ez az oktatóanyag nem elég számodra, További információ megtalálható a Microsoft online súgójában.

Videós utasítás

Azok számára, akiknek még mindig vannak megválaszolatlan kérdéseik, az alábbiakban mellékelünk egy videót, amely megjegyzéseket fűz a fent leírt utasításokhoz.

Nézze meg a cikkhez készült videót:

Tipikus riportjaink kész megoldások moduljai Business Intelligence (BI) lehetővé teszi a legnépszerűbb felhasználói kérdések 99%-ának megválaszolását.

De ha kívánják, az elemzők vagy bármely más felhasználó elkészíthetik jelentéseiket Excelben OLAP-kockákon.

Köszönet minden üzleti intelligencia (BI) modulunk egy OLAP-kockán alapul, amelyhez csatlakozva a felhasználók könnyedén elkészíthetnek bármilyen jelentést Excelben a kimutatások segítségével.

Az Excelben OLAP kockákra épített jelentések is menthetők, és újranyitáskor automatikusan frissülnek az aktuális adatokkal vállalati rendszer Business Intelligence (BI) .

Példák az OLAP kockákra épített jelentésekre:

A jelentések leírása:

(A jelentések elkészítésének folyamatáról lásd a cikkhez tartozó videót)

1. Jelentés az eladásokról rubelben és egységben:

• időszak: a folyó évre
• mutatók: eladás ténye
• részletezve: oszlopok / negyedév, hónap
• részletezve: értékesítési vonalak / csatornák, ügyfelek, márkák, termékek

2. Jelentés az eladások szerkezetéről rubelben és egységben (szűrő):

• időszak: a folyó évre
• mutatók: eladás ténye, eladás ténye százalékban
• részletezve: oszlopok / negyedév, hónap
• részletezve: vonalak / értékesítési csatornák, márkák

3. Jelentés az értékesítési terv végrehajtásáról rubelben és egységben (szűrő):

• időszak: a folyó évre
• mutatók: értékesítési terv, értékesítési tény, terv teljesítése
• részletezve: oszlopok / negyedév, hónap
• részletezve: értékesítési vonalak / csatornák, vásárlók, kategóriák, termékek

4. Jelentés az eladások növekedéséről rubelben és egységben (szűrő):

• időszak
• mutatók
• részletezve: vonalak / szövetségi körzetek, régiók, kategóriák, termékek

5. Jelentés az eladások növekedéséről idő szerint rubelben és egységben (szűrő):

• időszak: az aktuális évre az előzővel összehasonlítva
• mutatók: előző év árbevétele, értékesítési terv, értékesítés ténye, terv teljesülése, árbevétel növekedése abszolút és relatív értékben az előző évhez képest
• részletezve: sorok / negyedév, hónap, kategóriák, termékek

6. Követelések beszámolója:

• időszak: az aktuális dátumhoz
• mutatók: bizonylat összege, összes követelés, rövid lejáratú követelés, lejárt követelés időszakonként - 15 napig, legfeljebb 30 napig, legfeljebb 60 napig, legfeljebb 90 napig, legfeljebb 180 napig, legfeljebb 365 napig, a követelések szerkezete
• részletezve: értékesítési sorok / csatornák, partner, bizonylat fizetési dátuma, bizonylat dátuma, bizonylat

7. Jelentés a vevőállomány dinamikájáról:

• időszak: a folyó évre
• mutatók: összes követelés, rövid lejáratú követelések, lejárt követelések, követelések szerkezete
• részletezve: sorok / év, hét, partner

8. Jelentse be a kifizetések beérkezését bizonylat dátuma szerint:

• időszak: a folyó évre
• mutatók: bizonylat összege, bizonylat fizetési összege, fizetési eltérések abszolút és relatív értékben
• részletezve: sorok / év, hónap, partner, bizonylat típusa, bizonylat kelte, bizonylat száma

9. Jelentse a kifizetések beérkezését a fizetés dátuma szerint:

• időszak: a folyó évre
• mutatók: kifizetés összege, kifizetés elosztási összege, eloszlási eltérések abszolút és relatív értelemben
• részletezve: sorok / év, hónap, partner, fizetés típusa, fizetés dátuma, fizetési szám

10. Leltári jelentés:

• időszak: az aktuális dátumhoz
• mutatók: az áru fennmaradó része, a fennmaradó szavatossági idő százalékban (DSO szerint), az áru fennmaradó része szavatossági idő szerint - Term 0%, Term 10-30%, Term 40-60%, Term 70-90% , Term 99%
• részletezve: sorok / kategória, cikk, állapot, raktár, átvételi bizonylat dátuma

Helló mindenki! A mai anyag azoknak szól, akik továbbra is elsajátítják az alkalmazási programokkal való munkát, és nem tudják, hogyan kell excelben pivot táblát készíteni.

Egy közös táblázat létrehozásával bármelyikben szöveges dokumentumokat, elemezheti úgy, hogy pivot táblákat készít Excelben.

A pivot Excel tábla létrehozásához bizonyos feltételeknek kell megfelelni:

1. Az adatok egy táblázatba illeszkednek oszlopokkal és listák névvel.
2. Üres nyomtatványok hiánya.
3. Rejtett tárgyak hiánya.

Hogyan készítsünk pivot táblát Excelben: lépésről lépésre

Pivot tábla létrehozásához a következőket kell tennie:

Létrejött egy üres lap, ahol a területek és mezők listája látható. A címsorok mezőkké váltak új táblázatunkban. A pivot tábla mezők húzásával jön létre.

Pipával lesznek megjelölve, és az elemzés megkönnyítése érdekében a táblázatterületeken felcseréli őket.


Elhatároztam, hogy eladók szerinti szűrőn elemzem az adatokat, hogy látható legyen, ki és mennyiért adott el havonta, milyen terméket.

Kiválasztunk egy konkrét eladót. Nyomja meg az egeret, és húzza az „Eladó” mezőt a „Jelentésszűrő”-be. Az új mező be van jelölve, és a táblázat nézete kissé megváltozik.


A "Termékek" kategóriát karakterláncok formájában helyezzük el. A kötelező mezőt átvisszük a „Vonalnevek” közé.


A legördülő lista megjelenítéséhez számít, hogy milyen sorrendben adjuk meg a nevet. Ha kezdetben a sorokban választunk a termék mellett, majd feltüntetjük az árat, akkor a termékek csak legördülő listák lesznek, és fordítva.

Az „Egységek” oszlop a főtáblázatban egy adott eladó által adott áron értékesített áruk mennyiségét jelenítette meg.


Például az egyes hónapok eladásainak megjelenítéséhez a „Dátum” mezőt az „Oszlopnevek” mezőre kell cserélni. Válassza ki a "Csoport" parancsot a dátumra kattintva.


Jelöljük a dátum és a lépés időszakait. Megerősítjük a választást.

Ilyen táblázatot látunk.


Vigyük át az „Összeg” mezőt az „Értékek” területre.


A számok megjelenítése láthatóvá vált, de pontosan a számformátumra van szükségünk


A javításhoz válassza ki a cellákat, hívja meg az ablakot az egérrel, válassza ki a "Számformátum" lehetőséget.

Kiválasztjuk a következő ablak számformátumát, és megjelöljük a "Csoportelválasztót". Erősítse meg az "OK" gombbal.

Pivot asztal kialakítása

Ha bejelöljük azt a négyzetet, amely egyszerre több objektum kiválasztását erősíti meg, akkor egyszerre több eladó adatait tudjuk feldolgozni.


A szűrő alkalmazható oszlopokra és sorokra. Az egyik termékfajtánál bejelölve megtudhatja, hogy egy vagy több eladó mennyit adott el belőle.


A mezőparaméterek külön is konfigurálhatók. A példában azt látjuk, hogy egy bizonyos roma eladó adott hónapban adott összegért adott el ingeket. Az egér kattintásával meghívjuk a menüt a "Sum by field ..." sorban, és kiválasztjuk az "Értékmezők paraméterei" lehetőséget.


Továbbá az adatok csökkentéséhez a mezőben válassza a "Mennyiség" lehetőséget. Megerősítjük a választást.

Vessen egy pillantást a táblázatra. Jól látszik, hogy az egyik hónapban az eladó 2 inget adott el.


Most megváltoztatjuk a táblázatot, és a szűrőt hónaponként működtessük. A "Dátum" mezőt átvisszük a "Jelentés szűrőjébe", ahol az "Oszlopnevek" az "Eladó" lesz. A táblázat a teljes értékesítési időszakot vagy egy adott hónapot jeleníti meg.

Ha egy kimutatásban cellákat jelöl ki, akkor megjelenik egy lap, például a kimutatáseszközök, és lesz még két lap, az Opciók és a Tervezés.


Valójában a pivot táblák beállításairól még nagyon sokáig beszélhetünk. Változtasson ízlése szerint, hogy kényelmesen használható legyen. Nyugodtan nyomkodj és kísérletezzen. Bármilyen műveletet bármikor módosíthat a Ctrl + Z billentyűparancs megnyomásával.

Remélem, elsajátította az összes anyagot, és most már tudja, hogyan készítsen pivot táblát az Excelben.

Nagy és összetett táblák elemzésére általában ezeket használják. A képletek a meglévő adatok csoportosítására és elemzésére is használhatók. Készítsünk egyszerű jelentéseket képletekkel.

Használni fogjuk ( Beszúrás / Táblázatok / Táblázat), amely információkat tartalmaz a terméktételek értékesítéséről. A táblázat sorai egy termék tétel szállítására és értékesítésére vonatkozó adatokat tartalmazzák. Hasonló táblázatot használtak a cikkben .

A táblázat oszlopokat tartalmaz:

• Termék- a küldemény neve, például " Narancs»;
• Csoport- termékcsoport, például " Narancs"A csoportba tartoznak" Gyümölcsök»;
• szállítás időpontja- Az Áruk Szállító általi leszállításának dátuma;
• Értékesítési régió- Az a régió, ahol az Áruk szállítmányát értékesítették;
• Értékesítés- Az a költség, amelyen az Áruk szállítmányát értékesíteni lehetett;
• Értékesítés- az Áruk tényleges értékesítésének időtartama a Régióban (napokban);
• Nyereség- annak jelzése, hogy az Áruk eladott tételéből nyereség keletkezett-e.

Át Névkezelő javítsd a táblázatokat a következőre Forrás_tábla"(Lásd a példafájlt).

Képletek segítségével 5 egyszerű jelentést készítünk, amelyeket külön lapokon helyezünk el.

1. számú jelentés Összes áruértékesítés

Nézzük meg az egyes termékek összértékesítését.
A feladat a SUMIF () függvény segítségével egyszerűen megoldható, azonban maga a jelentés elkészítése bizonyos készségeket igényel egyes EXCEL eszközökkel való munkavégzés során.

Tehát kezdjük. Először is létre kell hoznunk egy listát az Áruk nevéről. Mivel a forrástábla Product oszlopában a nevek ismétlődnek, akkor abból csak az értékeket kell kiválasztanunk. Ezt többféleképpen is megtehetjük: képletekkel (lásd a cikket), a menün keresztül Adatok / Adatok kezelése / Ismétlődések eltávolítása vagy használatával. Ha az első módszert használja, akkor amikor új termékeket ad hozzá az eredeti táblázathoz, az új nevek automatikusan bekerülnek a listába. De itt az egyszerűség kedvéért a második módszert használjuk. Ezért:

• Menjen az eredeti táblázatot tartalmazó lapra;
• Hívás ( Adatok / Rendezés és szűrés / Speciális);
• Töltse ki a mezőket az alábbi ábra szerint: állítsa a kapcsolót állásba Másolja az eredményt egy másik helyre; a Forrástartomány mezőbe írja be a $ A $ 4: $ A $ 530; Jelölje be a négyzetet Csak egyedi rekordok.

• Másolja a kapott listát arra a lapra, ahol a jelentést elhelyezni fogja;
• A termékek listájának rendezése ( Adatok / Rendezés és szűrés / Rendezés A-tól Z-ig).

Meg kell kapnia a következő listát.

Egy cellában B6 írja be a következő képletet, majd másolja le a lista aljára:

SUMIF (Source_Table [Termék]; A6; Forrás_Tábla [Eladások])

COUNTIF (Source_Table [termék]; A6)

2. számú jelentés Áruértékesítés régiónként

Nézzük meg az egyes termékek teljes értékesítését a régiókban.
Használjuk az 1. számú jelentéshez készített Terméklistát. Hasonlóképpen kapunk egy listát a régiónevekről (az Initial Range mezőbe írja be a $ D $ 4: $ D $ 530).
Másolja a kapott függőleges tartományt ide Vágólapés annak vízszintes. Helyezze el a Régiók nevét tartalmazó kapott tartományt a jelentés fejlécébe.

Egy cellában B 8 bevezetjük a következő képletet:

SUMIFS (Source_Table [Sales];
Source_Table [Termék], $ A8;
Source_Table [értékesítési régió]; B 7 USD)

A képlet a Termék teljes értékesítését adja vissza, amelynek neve a cellába kerül A8 , a Régióban a cellából 7-RE ... Ügyeljen a használatra (hivatkozások $ A8 és B $ 7), szükség lesz rá a képlet másolásakor a többi üres táblázatcellához.

Nem fog menni, ha a fenti képletet a jobb oldali cellákba másoljuk a segítséggel (ez az 1. számú jelentésnél történt), mivel ebben az esetben a cellában C8 a képlet így fog kinézni:

SUMIFS (Source_Table [Eladások, napok];
Source_Table [Csoport], $ A8;
Source_Table [értékesítés]; C $ 7)

3. sz. Jelentés Termékek szűrése jövedelmezőség szerint

Térjünk vissza az eredeti táblázathoz. Az Áruk minden tétele vagy nyereséget termelt, vagy nem hozott (lásd a forrástáblázatban a Profit oszlopot). Számítsuk ki az eladásokat termékcsoportonként a jövedelmezőség függvényében. Ehhez a forrástábla rekordjait a Profit mező alapján szűrjük a képletek segítségével.

GYAKORISÁG (Forrás_táblázat [Eladások, napok]; A7: A12)

Válasszon ki egy tartományt a képlet megadásához C6: C12 majd be Formula bárírja be a fenti képletet, és nyomja meg CTRL+ VÁLTÁS+ BELÉP.

Ugyanez az eredmény érhető el a szokásos SUMPRODUCT () függvény használatával:
= SUMPRODUCT ((Forrás_táblázat [Eladások, napok]> A6) *
(Forrás_táblázat [Értékesítés, napok]<=A7))

Jelentés 5. szám Áruszállítások statisztikái

Most készítsünk jelentést a havi Áruellátásról.
Először is készítsünk egy listát a hónapokról évenként. Az eredeti táblázatban a legkorábbi szállítási dátum 2009.11.07. Kiszámolhatja a következő képlet segítségével:
= MIN (Eredeti_tábla [Szállítási dátum])

Készítsünk egy listát a dátumokról – a legkorábbi szállítási dátumtól kezdve. Ehhez a következő képletet fogjuk használni:
= EON HÓNAPOK ($ C $ 5; -1) +1

Ennek eredményeként megkapjuk a dátumok listáját - a hónapok első napjait:

A megfelelő cellaformátumot alkalmazva megváltoztatjuk a dátumok megjelenítését:

Képlet a szállított árutételek havi számának kiszámításához:

SUMPRODUCT ((Eredeti_tábla [Szállítási dátum]> = B9) *
(Forrás_táblázat [Szállítási dátum]

Most adjunk hozzá sorokat az egyes évek tételeinek teljes számának kiszámításához. Ennek érdekében a táblázatot kis mértékben módosítjuk, külön oszlopban kiemelve az ÉV () funkció segítségével a szállítás évét.

Most az évek szerinti kimenethez a menüponton keresztül struktúrát hozunk létre:

• Jelölje ki a módosított tábla bármelyik celláját;
• Hívja fel az ablakot a menüponton keresztül Adatok / Struktúra / Részösszegek;
• Töltse ki a mezőket a képen látható módon:

Az OK gombra kattintás után a táblázat a következőképpen módosul:

Évenkénti részösszegek jönnek létre.
A lap bal felső sarkában található kis gombok megnyomásával szabályozható az adatok táblázatban való megjelenítése.

Összegzés:

A létrehozotthoz hasonló jelentések természetesen elkészíthetők a használatával vagy használatával Szűrő az eredeti táblához, vagy más függvények segítségével BDSUMM (), BIZVLECH (), BCOUNT () stb. A megközelítés megválasztása az adott helyzettől függ.