Ez a cikk kissé elavult. Azt javaslom, hogy ismerkedjen meg vele új verzió során szerzett új tapasztalatokat tartalmazó Tavaly munka. Új cikk.
Munkánk során nap mint nap sok olyan jelentést látunk, amelyek gyerekesnek tűnnek vagy rossz minőségűek. Annak érdekében, hogy jelentései professzionális megjelenésűek legyenek, felajánlom az egyik lehetőséget az ilyen jelentések elkészítésére. Eredetileg egy nagyvállalat pénzügyi kimutatásainak elemzésére fejlesztették ki - kiderült, hogy körülbelül 30 oldalnyi szöveg, diagramok és diagramok. A vezetőségnek nagyon tetszett a jelentés.
Azt javaslom, hogy nézzen meg egy példát egy ilyen jelentésre az ezen a linken található csatolt fájlban. Hogyan kell elkészíteni Microsoft Excel anélkül, hogy drága szoftver, mint például az Adobe InDesign.
Ehhez néhány dolgot figyelembe kell vennünk: (1) nézet "Oldalelrendezés" módban (a szalagra kattintva kapcsolhatja be Kilátás a gombhoz Oldal elrendezés szakaszból Munkafüzet nézetek, erről a típusról és a jelentés nyomtatásra való optimalizálásáról lásd ezt a cikket), (2) tudományos megközelítés.
Ha az első ponttal minden világos, akkor a második némi magyarázatot igényel.
Általános tippek
A jelentés elkészítését nem információfeldolgozással és nem vázlatokkal kell kezdeni, hanem egy teljesen más oldalról - nevezetesen egy jelentésprezentációs sablon kidolgozásával. A jelentés csak akkor néz ki professzionálisan, ha szakaszonként következetes. Ezért van szüksége egy sablonra, amelyet új lapként másol egy új szakaszhoz, és kitölti.
A sablon nem lehet ügyetlen. Azt javaslom, hogy használja az alapértelmezett Microsoft Excel 2007-2010 témaszíneket. Nagyon kényelmes a felhasználó számára, hogy észlelje a jelentését: legyen az Ön menedzsere, pénzügyi igazgatója, ügyvezető igazgatója, részvényesei vagy a vállalat ügyfelei. Én például kéket használtam. A zöld is nagyon jól néz ki. Ha van olyan szakasz, amellyel foglalkozni szeretne Speciális figyelem, piros színben is elkészítheted, a lényeg, hogy ne elektromos pirosat válassz, hanem kényelmes pirosat.
A grafikonok gradienseit a vezetőség nagyon pozitívan értékeli. Ez általában egy mesterfogás a menedzsment számára, miközben szinte semmilyen erőfeszítést nem igényel tőled.
Egy ilyen jelentés (főleg, ha ötven oldalból áll) nagyon jól fog kinézni színes nyomtatón mindkét oldalán nyomtatva és bekötve. Ellenőrünknek tucatnyi ilyen jelentése volt, amelyeket az értekezletek során kiosztott az érdeklődőknek.
Ha elektronikusan kívánja terjeszteni, akkor le KELL fordítania pdf formátumban. Sokkal könnyebben olvasható, bár elveszti néhány funkcióját. Véleményem szerint a legkényelmesebb módja annak, hogy ebbe a formátumba konvertáljon, nem a pdf nyomtatók használata, hanem a Microsoft Excel 2007-2010 beépített funkciója (Fájl => Mentés másként => pdf formátum kiválasztása).
Számos pontra szeretném felhívni a figyelmet.
(1) A sablonlapon a jobb szélső oszlop nagyon keskeny (szó szerint néhány képpont). Okkal van ott. Ha nem adja hozzá, és grafikonokat és szövegmezőket szúr be a cellák szélére „húzva”, akkor a jobb oldaliak a szegélyükkel (még ha átlátszó is) „felmásznak” a szomszédos oldalra, üres oldal nyomtatására kényszerítve az Excelt.
(2) Húzza a szövegdobozokat és a grafikákat a cellák széléhez. Ezután mindegyik szigorúan függőlegesen lesz egymás alatt. A grafikon és a vele párhuzamos mező mérete egyszerre változtatható, ha megnyújtjuk a vonalat, amelyhez kapcsolódnak. Ahhoz, hogy egy objektum méretét a cella széléhez „ragassza”, az objektum méretének módosításakor kattintson a Alt a billentyűzeten. Egyébként ha megnyomod Váltás, akkor a méretei szigorúan egy irányban változnak - függőlegesen vagy vízszintesen.
Utószó
A jelentés elkészítését nagyrészt a KPMG által készített kiadványok formátuma ihlette. Megtekintheti őket a hivatalos weboldalukon. Nem tudom, miből készítik, de a G4-ek közül a legmodernebb és legprofibbnak tűnő riportjaik vannak.
Adat Excel lap lista formátumban kell lennie, az oszlop fejlécével az első sorban. A fennmaradó soroknak hasonló objektumokat kell tartalmazniuk ugyanabban az oszlopban, és nem lehetnek üres sorok vagy oszlopok az adattartományon belül.
- A megnyitott munkalap ablakban jelölje ki az adattartomány bármelyik celláját.
- Lépjen a "Beszúrás" fülre, és a "Táblázatok" csoportban bontsa ki a "Pivot Table" gomb menüjét.
- A parancsok listájában válassza a „Pivot Table” pontot (5.59. ábra).
- A „Pivot tábla létrehozása” ablakban (5.60. ábra) a „Táblázat vagy tartomány” oszlopban az elemzett adatok kiválasztott tartományának címe jelenik meg.
- A „PivotTable jelentés helyének megadása” csoportban válassza ki a következőket:
- „Új lapra” – a kimutatástábla-jelentés elhelyezése egy további lapon a munkafüzetben.
- „Meglévő lapra” – a kimutatásjelentés elhelyezése ugyanarra a lapra, ahol a forrásadatok találhatók.
- Zárja be az ablakot az "OK" gombbal.
- Megjelenik a lapon egy üres pivot tábla (új vagy meglévő) elrendezési vázlat formájában, és a lap jobb oldalán megnyílik a „Pivot Table Fields” terület a jelentés elkészítéséhez (5.61. ábra). . Bele lehet helyezni Pivot táblaúj mezőket, hozzon létre egy elrendezést, és szabja testre a jelentést.
- A Field List ablakban válassza ki az oszlopneveket a pivot tábla létrehozásához.
Ha módosítania kell a tartományt, válassza ki a lapon új sorozat a „Pivot Table létrehozása” ablak bezárása nélkül. A munkafüzet másik lapján vagy egy másik munkafüzetben található cellatartomány a következőképpen kerül megadásra: ([könyvnév]listanév!tartomány).
Ennek az elhelyezési helynek a kiválasztásakor önállóan meg kell adnia a jelentés celláját a lapon a „Tartomány” menüpontban.
A „Mezőlista” ablak elrejtéséhez kattintson a lap bármelyik szabad cellájára. Vagy az „Opciók” lap „Megjelenítés vagy elrejtés” csoportjában tiltsa le a „Mezőlista” elemet.
A gazdasági tervezési osztály munkájának fontos eleme a cégvezetés és a tulajdonosok felé történő beszámolás. A jelentéseknek világosnak és tömörnek kell lenniük, a referenciaértékeket pedig részletes elemzésben kell bemutatni. Ha féléves jelentést kell készítenie az ilyen követelmények figyelembevételével, javasoljuk, hogy olvassa el a cikkben található anyagot. Megtanulja, hogyan készíthet féléves jelentéseket grafikus formában Excelben, és kiemelheti bennük a legfontosabb mutatókat.
FÉLÉVES BESZÁMOLÁS KÉSZÍTÉSE EXCEL-BEN
A féléves jelentések elkészítésekor a szakemberek a következő problémákkal szembesülnek:
- nagy számú pozíció a jelentésben;
- terjedelmes jelentések - minden mutatót havi dinamikában kell bemutatni;
- indikátorértékek szórása, eltérő számsorrend.
E problémák sikeres megbirkózása és a vezetőség számára világos és érthető féléves jelentések elkészítése érdekében javasoljuk a grafikus űrlap használatát. Terjedelmes táblázatok és részletes elemzések esetén informatív a sparkline-ok és a feltételes formázás a kompakt táblázatokhoz és a kiválasztott kulcsmutatókhoz, optimális a diagramok használata.
Sparklines beExcel
A féléves jelentésben a mutatók havi dinamikáját szokás bemutatni. Olyan mutatókról beszélünk, mint a bevétel, az értékesítési volumen, a költségek, a vásárlások és a nyereség mennyisége, a jövedelmezőség. A gyakorlatban a nómenklatúra listája mindig széles. Ebben az esetben kényelmes grafikonokat közvetlenül beszúrni a mutatókkal ellátott táblázatba, amely lehetővé teszi nemcsak az egyes mutatók értékelését, hanem azok dinamikáját is, és összehasonlíthatja az eredményeket. Erre a mutatócsoportra célszerű alkalmazni szikrázik(1. ábra).
Vizualizáljuk az adatokat a táblázatban. 1 „Értékesítési elemzés sparklines segítségével” az alábbi lépések végrehajtásával:
- Az 1. táblázatban hozzon létre egy oszlopot vagy sort, amely a következő grafikus információkat tartalmazza:
- „Dinamika az év első felében”;
- „Bevételelemzés termékcsoportok szerint”;
- „Jövedelmezőségi elemzés termékcsoportok szerint.”
2. Végrehajtjuk a parancsot: Beszúrás → Sparklines → Menetrend(lásd 1. ábra). Az ablakban" Sparklines létrehozása» állítsa be az adattartományt és a helytartományt (2. ábra).
3. Másolja a grafikus elemet a kívánt számú cellára. Ha szükséges, szerkessze a grafikont a Tervező segítségével (3. ábra). Például ellenőrizzük a maximális pontok vagy markerek jelölését, megváltoztatjuk a grafikon színét.
4. A „Bevételelemzés termékcsoportok szerint” és a „Jövedelmezőségi elemzés termékcsoportok szerint” esetében az „Oszlop” sparkline típust használjuk.
Táblázat csillagokkal kiegészítve. 1 az „Űrlapszolgáltatásban” (hozzáférési kód – a 119. oldalon) látható. A táblázat azt mutatja, hogy csak a festékekből származó bevétel csökken, és csökkenő tendenciát mutat a sparkline grafikon. A többi termék és a vállalat egésze esetében a bevétel növekszik ("Dinamika az első félévben" oszlop).
A „Bevétel elemzése termékcsoportok szerint” sor azt mutatja, hogy a vállalat a legnagyobb bevételt a szabványos sima téglák értékesítéséből kapja - 4003,9 a 42 068,82 ezer rubelből.
A „Jövedelmezőség elemzése termékcsoportok szerint” sorban lévő oszlopos táblázat azt mutatja, hogy a texturált tégla maximális jövedelmezősége - 40-42,9%. A jövedelmezőség megugrása áprilisban következett be (az „Az első félévi dinamika” rovat grafikonján látható). Áprilisban a standard téglák esetében negatív, a magas pigmenttartalmú sima téglák esetében pedig csökkent (11,1%-os) jövedelmezőséget regisztráltunk.
Fontos pont: A jelentésben fel kell tüntetni a látható változások okát. Lehetséges ok változások a szóban forgó ügyben: az értékesítési szezon kezdetén (tavasz) promóciós kedvezményeket biztosítottak az értékesítési volumen serkentése és az új ügyfelek vonzása érdekében.
JEGYZET
Sparklines táblázat másolásakor Excelből ide Word dokumentum előfordulhat, hogy a grafikonok nem láthatók. Ebben az esetben használjon képformátumú betétet.
Feltételes formázás
Amikor a kiadásokról van szó, a menedzsment gyakran részletes elemzést igényel. Ez lehet különféle területek kiadási költségvetése, részletes jelentés a béralapról, beszállítói kifizetések. A jelentésekben ki kell emelni fontos információés nem szabványos értékek. Az adatok érzékelése javulni fog, ha hisztogramokat, színskálákat és ikonkészleteket használ.
Készítsünk asztalt. 2 „Az első félévi gyári rezsiköltségvetés (tényleges)” feltételes formázási funkciókkal a vezetőség felé benyújtásra:
- A táblázatban kiválasztunk egy tartományt, amelynek információit pontosabban kell megjeleníteni (a példában január-március oszlopok). Beállítjuk: itthon → Feltételes formázás → Színskálák. Válasszunk egy informatív színskálát „zöld-fehér” (4. ábra). A felhasználók kiválaszthatják a kívánt színskálát.
A magas költségek szemet gyönyörködtető zöld színt kölcsönöznek:
- a béralap költségei levonásokkal - 250, 240 és 236 ezer rubel;
- vendéglátási költségek márciusban - 300 ezer rubel.
Minél magasabb a kiadások összege, annál telítettebb a cella színe.
- Kiegészítjük a táblázatot. 2 végső mutatók megjelenítése költségtételek szerint. Jelöljük ki a számokat a tény oszlopban a fél évre, és hajtsuk végre a következő lépéseket: itthon → Feltételes formázás → Ikonkészletek → Egyéb szabályok. Végezzünk egyedi beállításokat (5. ábra).
Válassza az „Összes cella formázása értékeik alapján” lehetőséget, majd a megfelelő „Ikont”. Esetünkben ezek hisztogramok. Határozza meg az „Értéket” és a „Típust”:
- kitöltött hisztogram (négy rész) - több mint 500 ezer rubel érték esetén;
- hisztogramok három felosztással - 100-500 ezer rubel;
- hisztogram egy osztással - minden érték kevesebb, mint 100 ezer rubel.
A közgazdász a paramétereket azok szórása, átlagértékei és a vállalat által elfogadott szabályok alapján állítja be.
A gyári rezsiköltségvetés a vezetőségnek történő küldéshez a következő (2. táblázat).
Az „Időszak végén esedékes számlák” táblázat példáján átgondolunk egy másik módot a mutatók kényelmes bemutatására.
E. S. Panchenko, üzleti tanácsadó
Az anyagot részben publikáljuk. A magazinban teljes egészében elolvashatja
Nézze meg a cikkhez készült videót:
Szabványos jelentések a mi kész Business Intelligence (BI) megoldási modulok lehetővé teszi a legnépszerűbb felhasználói kérések 99%-ának megválaszolását.
De ha szükséges, az elemzők vagy bármely más felhasználó elkészítheti jelentését Excelben OLAP-kockák használatával.
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 újbóli megnyitáskor automatikusan frissülnek az aktuális adatokkal vállalati rendszer Üzleti elemzők (BI) .
Példák az OLAP kockákra épített jelentésekre:
A jelentések leírása:
(A jelentések létrehozásának folyamatáról lásd a cikkhez tartozó videót)
1. Értékesítési jelentés rubelben és mértékegységben:
- időszak: a folyó évre
- mutatók: eladás ténye
- részletezve: oszlopok/negyed, hónap
- részletezve: értékesítési vonalak/csatornák, vásárlók, márkák, termékek
2. Jelentés az értékesítési szerkezetrő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, hónap
- részletezve: értékesítési vonalak/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és ténye, a terv megvalósítása
- részletezve: oszlopok/negyed, 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 időbeli növekedéséről rubelben és egységben (szűrő):
- időszak: a tárgyévre az előzőhöz képest
- mutatók: előző év árbevétele, értékesítési terv, tényleges értékesítés, terv megvalósítása, árbevétel növekedés 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, 30 napig, 60 napig, 90 napig, 180 napig, legfeljebb 365 napig, követelések szerkezete
- részletezve: sorok/értékesítési csatornák, szerződő fél, bizonylat fizetési dátuma, bizonylat dátuma, bizonylat
7. Követelések dinamikai jelentése:
- időszak: a folyó évre
- mutatók: összes követelés, rövid lejáratú követelés, lejárt követelés, követelés szerkezet
- részletezve: sorok/év, hét, szerződő fél
8. Befizetési beérkezési jelentés bizonylat dátuma szerint:
- időszak: a folyó évre
- mutatók: bizonylat összege, bizonylat kifizeté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 beérkezett kifizetéseket a fizetés időpontja szerint:
- időszak: a folyó évre
- mutatók: kifizetés összege, kifizetési elosztási összeg, elosztási eltérések abszolút és relatív értékben
- 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: fennmaradó termék, fennmaradó eltarthatósági idő százalékban (DSO szerint), fennmaradó termék eltarthatósági idő szerint - 0% lejárati idő, 10-30% lejárati idő, 40-60% lejárati idő, 70-90% lejárati idő, 99% lejárati dátum
- részletezve: sorok/kategória, termék, állapot, raktár, átvételi bizonylat dátuma
Sziasztok! 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 készítsenek pivot táblát az Excelben.
Egy általános táblázat létrehozása után bármelyikben szöveges dokumentumok, elemezheti úgy, hogy pivot táblákat készít Excelben.
Az Excel pivot tábla létrehozásához bizonyos feltételeknek teljesülniük kell:
- Az adatok egy táblázatba illeszkednek oszlopokkal és listák nevekkel.
- Nincsenek kitöltetlen űrlapok.
- Nincsenek rejtett tárgyak.
Hogyan készítsünk pivot táblát Excelben: lépésről lépésre
Pivot tábla létrehozásához szüksége lesz:
Létrejött egy üres lap, ahol a területek és mezők listája látható. A fejlécek mezőkké váltak az ú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 felcseréli őket a táblázatterületeken. 
Elhatároztam, hogy az adatelemzést eladó szerinti szűrőn keresztül fogom elvégezni, így egyértelművé válik, hogy ki és mennyiért adott el minden hónapban, és milyen terméket.
Konkrét eladót választunk. Tartsa lenyomva az egeret, és helyezze át 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 sorok formájában helyezzük el. A szükséges mezőt áthelyezzük a „Sorcímek” közé. 
A legördülő lista megjelenítéséhez számít, hogy milyen sorrendben adjuk meg a nevet. Ha kezdetben egy termék mellett választunk a sorokban, majd feltüntetjük az árat, akkor a termékek 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 „Oszlopok neveire” kell cserélni. Válassza ki a "Csoport" parancsot a dátumra kattintva. 
Adja meg a dátum periódusait és lépéseit. Erősítse meg választását. 
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 szükségünk van a számformátumra 
Ennek kijavításához jelölje ki a cellákat úgy, hogy az egérrel előhívja az ablakot, és kiválasztja a „Számformátum” lehetőséget.
Kiválasztjuk a következő ablak számformátumát, és bejelöljük a „Számjegycsoport elválasztó” pontot. Erősítse meg az „OK” gombbal. 
Pivot Table tervezése
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 is feldolgozhatjuk. 
A szűrő alkalmazható oszlopokra és sorokra. Az egyik terméktípus melletti négyzet bejelölésével 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án keresztül azt látjuk, hogy egy bizonyos roma eladó egy adott hónapban adott összegért adott el ingeket. Az egér kattintásával a „Sum by field...” sorban előhívjuk a menüt és kiválasztjuk az „Értékmező paraméterei” pontot. 
Ezután a mezőben lévő adatok összegzéséhez válassza a „Mennyiség” lehetőséget. Erősítse meg választását. 
Nézz az asztalra. Jól látszik, hogy egy hónap alatt 2 inget adott el az eladó. 
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ésszűrőbe”, ahol az „Oszlopnevek” van, ott az „Eladó” lesz. A táblázat a teljes értékesítési időszakot vagy egy adott hónapot jeleníti meg.
Ha kijelöli a cellákat egy kimutatásban, akkor megjelenik a „Munka a kimutatástáblákkal” lap, és lesz még két lap: „Opciók” és „Tervező”. 
Valójában a pivot táblák beállításairól nagyon sokáig lehet beszélni. Változtasson ízlése szerint, hogy kényelmesen használható legyen. Ne féljen erőltetni és kísérletezni. Bármilyen műveletet bármikor módosíthat a Ctrl+Z billentyűkombináció megnyomásával.
Remélem, megtanulta az összes anyagot, és most már tudja, hogyan készítsen pivot táblát az Excelben.
Betöltés...