Powerpivot használati utasítás. Power Pivot: ablakfunkciók DAX szósszal

^

Késztermékek Zn-mérlege év elején, t;

Tn - szállított, de nem fizetett termékek, év elején, t;

Tm - a piacképes termékek mennyisége a terv szerint, t;
^

Зк-év végére tervezett késztermékek mérlege, t;

Тк - a kiszállított, de ki nem fizetett termékek mennyisége az év végén, t

Nettó termelés (normatív) - ez a bruttó kibocsátás része, mínusz a gyártásra fordított termelőeszközök költsége. A dolgozók munkaerõfeszítésének eredményeként újonnan létrejött értéket jellemzi. ezt a vállalkozást vagy egy bizonyos típusú termék előállítását, és tükrözi a vállalkozás hozzájárulását a nemzeti jövedelem kialakulásához. Jelenleg a nettó termelés az egyik fő mutató, amely a vállalkozások és termelőszövetségek termelési és gazdasági tevékenységének eredményeit jellemzi, megszünteti a termelés anyagfelhasználásának növekedése iránti érdeklődést, és hozzájárul a vállalkozások és társulások tevékenységének objektív értékeléséhez.

A bányászat termékei nómenklatúra szerint több mint 200 tételt foglalnak magukban, amelyek az üzemanyag és energia, a kohászati, az érc és a nemfémes ásványi nyersanyagok csoportjában egyesülnek.

Termékeiket fogyasztóknak nagykereskedelmi áron értékesítve, becsült árakon és tarifákon végzett munkák és szolgáltatások nyújtásával a vállalkozások készpénzbevételhez jutnak, amelyen keresztül költséget térítenek és nyereséget termelnek. A B bevétel összegét a képlet határozza meg

ahol a Tso és a Csm a termékek nagykereskedelmi ára, illetve a becsült ár, UAH; Qp az eladott termékek mennyisége;

Qy1 és Qy2 - a becsült árakért és tarifákért fizetett szolgáltatások mennyisége;

ρ y a szolgáltatások díja, UAH/egység.

§ ^ 2. A profit gazdasági tartalma és keletkezésének forrásai

A profit fontos mutatója a termelés hatékonyságának és a vállalkozások gazdasági tevékenységének nyilvános értékelésének. Ez az eladott termékek mennyiségétől függ, csökkenti annak költségeit, bővíti a termékskálát és javítja azok minőségét.

A PROFIT egy gazdasági kategória, amely a vállalkozások termelési és gazdasági tevékenységének pénzügyi eredményeit fejezi ki. Az eladott termékek értékének többletét jelenti a létrehozására fordított termelési költségeken felül. Ez függ az eladott termékek mennyiségétől, költségeik csökkentésétől, a termékek körének bővítésétől és minőségének javításától.

A profit a megtakarítások, a kiterjesztett szaporodás és az anyagi ösztönzők forrása a vállalkozások kollektívái és az egyéni munkavállalók számára a munka termelékenységének és a termelés jövedelmezőségének növelése érdekében. A nyereség egy részét levonják az állam rendelkezésére, a másik része a vállalkozás rendelkezésére áll, és különösen a vállalkozás gazdasági ösztönzésére szolgáló alapok képzésére szolgál.

Tegyen különbséget a termékek értékesítéséből származó nyereség, egyenleg és elszámolás között.

^ Profit a termékértékesítésből A Pr a mérleg szerinti eredmény fő része, és a B termék értékesítéséből származó bevétel és a C teljes költség különbözete:

Pr \u003d B-S, (80)

A kitermelt ásvány értékesítéséből származó bevételt, amely a bányavállalat fő profitforrása, a következő képlet határozza meg:

B=(C-C)Q, (81)

ahol B az ásványok értékesítéséből származó bevétel időegységenként, UAH;

C-nagykereskedelmi ár 1 tonna termékre, UAH,

C – 1 tonna ásványi anyag előállítási összköltsége, UAH;

Q egy adott naptári időszakra eladott termékek mennyisége, pl.

^ mérleg szerinti eredmény a vállalkozás termelési és gazdasági tevékenysége eredményeként kapott nyereség teljes értékét jelenti. A képlet határozza meg:
^

Pb=Pr+Pdr.+Pv.-U, (82)

ahol Pdr - egyéb munkavégzésből és oldalra nyújtott szolgáltatásokból származó nyereség;

Pv - a nem üzemi tevékenységből származó tervezett nyereség (a vállalkozásnál a termelőeszközök részeként nyilvántartott többletvagyon értékesítése);

Lakásfenntartásból és kommunális szolgáltatásokból származó U-veszteség.

A fő termelési és gazdasági tevékenységből - az ásványok kitermeléséből és értékesítéséből - származó nyereségen túl a bányászati ​​vállalkozások egyéb árbevétel és nem üzemi eredmény.

Egyéb értékesítésből származó nyereség keletkezhet melléktermékek (zúzott kő, homok, agyag stb.) előállításából, értékesítéséből, a vállalkozás mérlegében szereplő járműpark üzemeltetéséből; háztartási termékek értékesítése; más vállalkozások hő-, energia-, gőz-, vízellátása.

A nem működési eredmény a korábban a vállalkozás veszteségével leírt tartozások beérkezéséből, a befolyt bírságokból és kötbérekből és egyéb forrásokból keletkezik.

^ Becsült nyereség a mérleg szerinti eredménynek azt a részét tükrözi (a kötelező kifizetések nélkül), amely a vállalkozás rendelkezésére áll. A képlet határozza meg:
^

Prasch=Pb-Pf-Pren-Pkr, (83)

P f - a termelési kifizetések összege alapok, UAH;

Előre rögzített (bérleti díjak, UAH;

Pkr-banki hitel kamatfizetése.

A nyereség az erre az ásványra meghatározott árszinttől, minőségétől, előállítási költségétől és termelési mennyiségétől függ.

A bányavállalatok termékeinek általános árszintjének változása a teljes nyereség mértékének megfelelő változásához vezet.

A nyereség növekedésében jelentős tényező a bányászott ásvány minőségének javulása, valamint a kitermelési szerkezetének javulása típusonként, minőségenként és osztályonként.

Ha az általános árszínvonal vagy az ár az ásvány minőségének javítása miatt változik, a többletnyereséget a következő képlet alapján számítjuk ki:

P=QC, (84)

Q - termelési mennyiség, t;

C-árváltozás, UAH

A nyereséget általában UAH-ban számítják ki. egy bizonyos naptári időszakra (hónap, negyedév, év). Mérete 1 tonna bányászott ásványra is meghatározható.

3. § A nyereség felosztása.

A vállalkozás mérleg szerinti eredményét a termelés további bővítése és javítása érdekében fel kell osztani.

A nyereség elosztásának két módja van. A fő a normatív módszer, amelyre, amint készen állnak, minden minisztériumnak át kell térnie. Egyes, alacsony jövedelmezőségű bányászati ​​ágazatokban ez a módszer nem alkalmazható, ezért a tervezett nyereségfelosztási módszert alkalmazzák.

A minisztériumokban, egyesületekben a terv feladatai alapján a bővített szaporítás megvalósítására, gazdasági ösztönzők és egyéb tervezett költségek megvalósításához rendelkezésükre álló (évenkénti differenciált) nyereségből történő levonások normáját megállapították, az alábbi eljárási rendet megállapított elszámolásokra a költségvetéssel a kifizetésekre től érkezett.

1. A költségvetésbe az államháztartás kiadásainak finanszírozására befizetett befizetések abszolút (garantált) összege az évek szerinti megoszlású tervekben a termelőeszközök utáni kifizetésekből, a fix (bérleti) kifizetésekből és a nyereségből történő levonásokból áll.

Amikor az egyesületek és vállalkozások olyan ellenterveket fogadnak el, amelyek túllépik a következő évi X éves terv feladatait, akkor ezt a többletet a megnövelt normák szerint vonják le a gazdasági ösztönző alapokból. Az e célokra elkülönített többletnyereség összege a megállapított norma szerint felosztandó nyereség összegéből kizárásra kerül.

A költségvetésbe történő befizetések tényleges nyereség alapján történő kiszámításakor kizárja az egyesületek és vállalkozások által az új, nagy hatékonyságú termékek és az állam által elnyert termékek értékesítéséből származó, a nagykereskedelmi árhoz kapcsolódó prémiumok összege formájában keletkező többletnyereséget. Minőségi védjegy, valamint nem tartalmazza a magasabb színvonalon a gazdasági ösztönző alapokba irányított nyereséget, az elfogadott ellentervek végrehajtásától függően. Ekkor a tényleges nyereséget a megállapított árak túllépése és a jelenlegi árképzési eljárás megsértése miatt befolyt többletbevétel összege csökkenti. Ezen túlmenően a tényleges nyereségből levonásra kerül az egyes esetekben az egyesületek és vállalkozások által az előírásoktól eltérő termékek kibocsátása miatt kapott többletnyereség összege. specifikációk, amely a költségvetés bevételéhez járul hozzá, és nem szerepel a termékértékesítési terv végrehajtásáról szóló jelentési adatok között.

A jóváhagyott eredményterv teljesülése és nem teljesítése esetén a tervben, illetve a szabványban meghatározott összegben az egyesületek, vállalkozások a megfelelő beszámolási időszakra a tervben előirányzott eredményből a költségvetésbe befizetéseket teljesítenek.

A többletnyereség a tényleges nyereség nyereséggel csökkentett különbözete, amelynek céliránya van, és speciális módon kerül felhasználásra.

Az eredményterv legfeljebb 3%-os túllépése esetén a többletnyereség 50%-a a vállalkozások, egyesületek rendelkezésére áll, 50%-a pedig a költségvetésbe kerül. Az eredményterv 3%-ot meghaladó túlteljesítése esetén a többletet képező összeg 25%-a áll rendelkezésükre, a többlet többlet fennmaradó része pedig az állami költségvetésbe kerül nemzeti rendezvények finanszírozására.

Az egyesületek, vállalkozások rendelkezésére álló többletnyereség a rendelet szerinti kiadásokra és levonásokra irányul általános álláspont a nyereségfelosztás normatív módjáról.

Ha a termelési és eredményterv a tervben foglaltnál alacsonyabb értékben teljesül, akkor a pénzeszköz-megtakarítás az egyesületek, vállalkozások rendelkezésére áll.

A nyereségből származó kifizetéseken kívül a következőket küldik a költségvetésbe:

Az egyesületek és vállalkozások által az új, rendkívül hatékony és állami minőségi védjeggyel ellátott termékek értékesítéséből származó többletnyereség egy része. Ezeket a kifizetéseket az e termékek értékesítéséből származó többletnyereség 50%-aként határozzák meg, amely azután marad, hogy annak egy részét 70%-ban elküldik a gazdasági ösztönző alapoknak;

az egyesületek és vállalkozások mérlegében szereplő leánygazdaságok tényleges nyereségének 75%-a a évi eredmények szerint;

Az anyagi javak és a fel nem szerelt berendezések többlet nem jóváírt készleteinek kifizetését a költségvetésbe kell befizetni az egyesületek és vállalkozások rendelkezésére álló nyereség terhére.

A fix (bérleti) kifizetések túltervezett törlesztőrészletei a nyereségből az állami költségvetés bevételeibe fordítandó levonások megfelelő csökkentésének terhére történnek. Ha ezeknek a hozzájárulásoknak a tényleges összege a tervezettnél alacsonyabb, akkor a költségvetési hozzájárulás alá eső nyereségből származó levonások a különbözettel nőnek.

2. A minisztériumok és az uniós ipari szövetségek által a számukra jóváhagyott szabvány szerint a termelő egyesületek és vállalkozások rendelkezésére bocsátott tervezett nyereség a következőkre irányul:

Állami tőkebefektetések finanszírozása (amortizációval és egyéb finanszírozási forrásokkal nem fedezett részben);

Banki kölcsönök visszafizetése;

kölcsön kamatának fizetése;

A saját forgótőke színvonalának növekedése;

Hozzájárulások a minisztériumban (osztályon) kialakított egységes tudomány- és technológiafejlesztési alaphoz;

Anyagi ösztönző, társadalmi és kulturális rendezvények, valamint lakásépítés, termelésfejlesztés alapokhoz való hozzájárulás;

Levonások az ösztönző alapból új technológia létrehozására, fejlesztésére és bevezetésére (részben a költségek csökkentésével);

Lakás- és kommunális szolgáltatások működtetéséből származó veszteségek, kulturális és oktatási intézmények, úttörőtáborok gazdasági fenntartási költségeinek fedezete;

Levonások a minisztérium (osztály) tartalékából anyagi segítségnyújtás céljából;

Felsőbb hatóságtól származó levonások a nyereség újraelosztásának sorrendjében (Össz-uniós ipartestületek, termelőszövetségek és vállalkozások esetében);

Egyéb tervezett költségek a hatályos jogszabályoknak megfelelően.

A minisztériumok, az uniós ipartestületek, a termelőegyesületek és a vállalkozások a rendelkezésükre álló nyereség keretein belül (az elfogadott szabvány szerint) az éves tervekben irányt változtathatnak, az állami tőkebefektetések finanszírozására biztosított források kivételével. , levonások egyetlen alapból a tudomány és technológia fejlesztésére, valamint gazdasági ösztönző alapok.

Az anyagi javak és a fel nem szerelt berendezések többlet nem jóváírt készleteinek kifizetését a költségvetésbe kell befizetni az egyesületek és vállalkozások rendelkezésére álló nyereség terhére.

A minisztérium, az szövetségi ipartestület, a termelőszövetség és a vállalkozás rendelkezésére álló többletnyereség a következőkre irányul:

Bankkölcsön kamatának fizetése olyan esetekben, amikor e kifizetések tényleges összege meghaladja a tervben előírt összegeket;

Tőkebefektetésre felvett bankhitelek törlesztése a pénzügyi tervben meghatározott összegeket meghaladó mértékben;

Saját forgótőke hiányának kompenzációja és az erre a célra felvett kölcsönök visszafizetése;

Az anyagi ösztönző alapba, a társadalmi-kulturális rendezvények és lakásépítési alapba történő további levonások, ideértve a béralap fel nem használt megtakarításainak tárgyi ösztönző alapból történő év végi levonásait a megállapított normákhoz vagy a tervezett béralaphoz képest, a termelés teljesítési tervének és a munkatermelékenység növekedésének függvényében;

További levonások az ösztönző alapból új technológia létrehozására, fejlesztésére és bevezetésére;

díjak fizetése;

Lakás- és kommunális szolgáltatások üzemeltetéséből származó veszteségek megtérítése a pénzügyi tervben előírt összegeket meghaladó összegekben;

A felsőbb szervezetek alapjaiból és tartalékaiból az előírt módon kialakított levonások;

Egyéb tervezett költségek az iparág fejlesztéséhez a vonatkozó jogszabályoknak megfelelően.

A megjelölt célokra történő irányítást követően fennmaradó többletnyereséget a minisztérium, a szövetségi ipartestület, a termelőszövetség és a vállalkozás a terv teljesítése során felmerülő többletkiadások finanszírozására fordítja.

Amennyiben az állami tőkebefektetések finanszírozására vonatkozó terv nem teljesül, úgy a tervben erre a célra előirányzott eredmény fel nem használt része a minisztérium, az szövetségi ipartestület, a termelőegyesület és a vállalkozás rendelkezésére áll. speciális bankszámlára utalva a következő évre elküldve a késés pótlására. Az egyéb nyereségirányzati tételekből származó megtakarítást a minisztérium, a Szövetségi Ipartestület, a termelőszövetség és a vállalkozás saját belátása szerint használja fel, kivéve a jóváhagyott keretet meghaladó tőkebefektetések finanszírozását, a gazdasági ösztönző levonásait. alapok és egyetlen alap a tudomány és technológia fejlesztésére.

Az említett haszon egy részét, amely az egyesület és a vállalkozás rendelkezésére áll, a felsőbb szervezetek - a Szövetségi Ipartestület és a Minisztérium - hozzájárulásukkal központosíthatják. A minisztérium hasonló módon központosíthatja az említett nyereség egy részét, amely az ipartestület rendelkezésére áll.

Ha a profitterv nem teljesül, a minisztériumok, az ipartestületek, a termelői egyesületek és a vállalkozások rendelkezésére álló nyereség megfelelő csökkenése miatt a nyereség kifizetéséből a költségvetésbe a tervben meghatározott összegű hozzájárulást kell teljesíteni.

A normatív elosztási módra át nem tért termelő társulásokban és vállalkozásokban a nyereség felosztása a következőképpen történik. A speciális módon felhasznált nyereség kizárása után a tervezett eredmény fennmaradó részéből a termelési tárgyi eszközök és a normalizált forgótőke kifizetések, a fix (bérleti) befizetések levonásra kerülnek a költségvetésbe, valamint a felhasználásért kamatot fizetnek a banknak. a kölcsön.

A nyereség egy részét anyagi ösztönzők, társadalmi és kulturális rendezvények, valamint lakásépítés alapképzésére, valamint a termelés fejlesztésére fordítják. Az eredmény másik része a tárgyévben tervezett tevékenységre ideiglenesen felhasznált tartalékkeret megtérítését szolgálja a szolgálati időre és a gazdaságösztönző alapokra, ideértve az anyagi ösztönző alap forrásait is a díjazás alapú kifizetésre szánt részben. évi munka eredményéről, valamint a minisztériumok és osztályok tartalékának kialakításáról az anyagi segítségnyújtás érdekében.

A nyereség fennmaradó részét az egyesületek és vállalkozások irányítják:

Tervezett tőkebefektetésekhez nyújtott kölcsön visszafizetésére az állami tőkebefektetések új finanszírozási eljárására való áttérés előtt;

Terven felüli (limit feletti) tőkebefektetésre kibocsátott összegek átvitt tartozásainak törlesztésére.

A nyereség ezen hitelek törlesztésére szolgál a termelésfejlesztési alap és a tárgyi eszközök teljes helyreállítására szánt értékcsökkenési leírás egy részének felhasználása után, a termelésfejlesztési alapba kerülő rész kivételével:

Állami tőkebefektetések finanszírozására;

Saját működő tőke növekedésének finanszírozására;

A minisztérium (osztály) tudomány és technológia fejlesztésére szolgáló egységes alap létrehozására;

A lakás- és kommunális szolgáltatások üzemeltetéséből származó veszteségek és a lakóépületek fűtésére és melegvíz ellátására szállított tüzelőanyag árkülönbözete, épületek, helyiségek, kulturális és oktatási intézmények építményei, kertek, parkok gazdasági fenntartásának költségei, a szakszervezeti szervezeteknek ingyenes használatra átadott úttörőtáborok, valamint a tervben meghatározott összegeken belül egyéb költségekre;

Magasabb szervezeteknek a tervben meghatározott összegeken belüli újraelosztás sorrendjében.

A teljes nyereség és az átutalt kifizetések, levonások, valamint a vállalkozás pénzügyi tervéből adódó egyéb tervezett költségek fedezésére fordított nyereség egy része közötti különbözet ​​a költségvetésbe befizetendő nyereség szabad egyenlege. .

A többletnyereség a következő felosztási tételekkel rendelkezik:

Hozzájárulások a költségvetéshez a termelési fix és normalizált forgótőke kifizetéséhez, valamint a fix kifizetésekhez, a banki kölcsön kamataihoz olyan esetekben, amikor e kifizetések tényleges nagysága meghaladja a tervben előírt összegeket;

Az eredményterv elmulasztása miatt kialakult saját forgótőke hiányának pótlása és a nyereségből finanszírozott többletkiadások megvalósítása, vagy az erre a célra kibocsátott hitelből származó tartozás idő előtti visszafizetése;

A béralap fel nem használt megtakarításainak anyagi ösztönző alapjába történő év végi levonása a megállapított normatíva vagy a tervezett béralap ellenében, a termelési terv teljesülése és a munkatermelékenység növekedése függvényében;

További levonások az anyagi ösztönző alapból, a társadalmi és kulturális rendezvények és lakásépítési alapból, a termelésfejlesztési alapból a megállapított összegekben. Ugyanakkor a tervezetten kívüli nyereség a pénztári levonásokra fordítható, mínusz a termelőeszközök kifizetése, a fix (bérleti) kifizetések, a bankhitel kamata, a saját forgótőke hiányának pótlására fordítható nyereség, valamint a termelési eszközök levonása. anyagi ösztönző alap, fel nem használt megtakarítások a bérlistán;

Prémiumok kifizetése szocialista emulációért;

A lakás- és kommunális szolgáltatások üzemeltetéséből származó veszteségek, valamint a lakóépületek fűtésére és melegvíz ellátására szállított tüzelőanyag árkülönbözete a pénzügyi tervben meghatározott összegeket meghaladó összegekben;

Az állami tőkebefektetések kerete feletti nagy hatékonyságú új termékek előállítására és az előállított termékek minőségének javítására, valamint a fogyasztási cikkek előállításának bővítésére és a lakosság fogyasztói szolgáltatásainak javítására kiadott bankhitel törlesztése , a visszafizetési költségek és a kölcsön legfeljebb két éven belüli visszafizetése mellett a jóváírt tevékenységből származó többletnyereség terhére és keretében. a költségek megtérülési idejének és a hiteltörlesztési források meghatározásakor;

A jóváírt tevékenység terhére előállított fogyasztási cikkek értékesítéséből a forgalmi adó legfeljebb 50%-át veszik figyelembe;

Új berendezések bevezetésének, gépesítésének, termelési technológia fejlesztésének költségeire kiadott bankhitel visszafizetése a termelésfejlesztési alap forráshiánya esetén - a jóváírt forrásból a tárgyévben befolyt többletnyereség vagy veszteségcsökkentésből származó megtakarítás keretein belül - ropriyatiya;

A fogyasztási cikkek előállításának bővítésének, megszervezésének, a kulturális és háztartási cikkek, valamint a háztartási cikkek előállításához kapcsolódó termelő létesítmények bővítésének, rekonstrukciójának költségeire kiadott bankhitel törlesztése a tárgyévben jóváírt tevékenységből származó többletnyereségen belül ( a termelés fejlesztéséhez szükséges források hiánya esetén);

Hozzájárulás a minisztérium vagy főosztály pénzügyi támogatás nyújtására szolgáló tartalékához.

A többletnyereség teljes összege és a meghatározott kifizetések és levonások különbözete a többlet szabad egyenlegéhez való hozzájárulás formájában kerül a költségvetésbe.

A vállalkozás tényleges nyereségből számított nyereségének szabad egyenlege az új, nagy hatékonyságú és Állami Minőségjelzéssel ellátott termékek értékesítéséből származó nyereség költségvetésbe befizetendő részével növekszik a 2008-ban elért többletnyereség után. ennek a terméknek az értékesítése a gazdasági ösztönző alapokhoz és a tudomány és a technológia fejlesztésére szolgáló egyetlen alaphoz tartozik.

Azon vállalkozások, amelyek nem biztosítják az eredményterv teljesítését, a ténylegesen befolyt nyereséget, mínusz a speciális módon felhasznált nyereséget, az alábbiak szerint osztják fel. Mindenekelőtt az előírt összegben a költségvetésbe be kell fizetni a termelési fix és normalizált forgótőkét, valamint a fix (bérleti) kifizetéseket, valamint a bankhitel kamatait. E befizetések kifizetése után az előírt módon levonásra kerül sor az anyagi ösztönző alapból, a szociális és kulturális rendezvények, valamint a lakásépítési alapból, a termelésfejlesztési alapból, valamint az intézmény pénzeszközeinek megtérítéséről. az elmúlt évben tervezett tevékenységre ideiglenesen felhasznált hosszú szolgálati tartalék és a gazdaságösztönző alapok, ideértve az anyagi ösztönző alap pénzeszközeit is a évi munkaeredmény alapján járó díjazás kifizetésére szánt részben, valamint a minisztériumok és főosztályok tartalékának kialakítása a pénzügyi segítségnyújtás céljából. Ebben az esetben a tényleges nyereségből az utolsó levonásokat a megállapított összegben elfogadják.

A nyereség fennmaradó részét, levonva a felsorolt ​​kifizetéseket és levonásokat, a vállalkozás pénzügyi tervében meghatározott arányokban a vállalkozások egyéb tervezett kiadásai és a költségvetési hozzájárulás mellett a nyereség szabad maradványa között kell felosztani.

4. § A termelés jövedelmezősége

A jövedelmezőség, valamint a nyereség a termelés hatékonyságának, jövedelmezőségének mutatója, de a nyereség és az elköltött erőforrások arányaként számítják ki. Elméletileg mind a rendelkezésre álló erőforrások, mind a termelési folyamatban elköltött erőforrások (költségek) működhetnek erőforrásként, amelyhez viszonyítva a jövedelmezőséget számítják. A jövedelmezőség számítási módszerének megválasztása a termelési és gazdasági tevékenységek eredményeinek értékelésének céljától vagy elemzésének irányától függ.

A nyereséges üzlet azt jelenti vállalkozási bevétel mindig meghaladják a termékek előállítási és értékesítési költségeit. A költségelszámolás alapfeltétele és követelménye a jövedelmezőség. Egy adott vállalkozás jövedelmezőségének tervezett vagy tényleges mutatóit vagy egy bizonyos típusú termék kibocsátását összehasonlítják a nemzetgazdaság egészére, egy iparágra, alágazatra vagy termékcsoportra vonatkozó jövedelmezőségi színvonallal. Így lehetővé válik a nemzetgazdaság különböző ágazataiban és alágazataiban a különböző típusú vállalkozások relatív hatékonyságának összehasonlítása, az egyes terméktípusok előállításának összehasonlító hatékonyságának azonosítása egy vagy több vállalkozásnál.

A közgazdasági irodalomban a jövedelmezőség számításának különféle módszereit alkalmazzák. A közgazdasági gyakorlatban leggyakrabban a következőket alkalmazzák: jövedelmezőség a termelési eszközök költségéhez viszonyítva, jövedelmezőség a költségekhez viszonyítva és jövedelmezőség a költségekhez viszonyítva, mínusz az energia, az üzemanyag, a nyersanyagok és az anyagok költsége.

A termelőeszközökhöz kapcsolódó R jövedelmezőséget úgy határozzák meg, hogy az éves mérleget vagy a becsült nyereséget elosztják a termelési eszközök átlagos éves költségével és a vállalkozás normalizált forgótőkéjével:

Ahol Fo és Fob a befektetett termelési eszközök és a normalizált forgótőke átlagos éves költsége. Ez a jövedelmezőségi mutató a termelésbe vont erőforrások felhasználásának hatékonyságának jellemzésére szolgál.

A költségekhez viszonyított jövedelmezőség egyenlő a nyereség és az eladott áruk költségének arányával:

Ezt a jövedelmezőségi mutatót az egyes termelésekre és terméktípusokra számítják ki. Jellemzi az év során elköltött pénzeszközök hatékonyságát, amely az értékesített termékekben realizálódott.

Ha a termelés mennyiségét a normatív nettó termelés mutatójaként számítjuk ki, akkor az egyes iparágak és terméktípusok esetében a jövedelmezőségi mutatót úgy határozzuk meg, hogy a nyereséget elosztjuk az önköltségi árral, mínusz az energia, az üzemanyag, a nyersanyagok és az anyagok költségével. Ennek a jövedelmezőségi mutatónak a használata érdekelheti a vállalkozást a termékek anyagi kapacitásának csökkentésében.

A figyelembe vett jövedelmezőségi mutatók mindegyike különféle célokra kerül kiszámításra, bármilyen nyereség felhasználásával: mérleg, termékértékesítésből származó nyereség és elszámolás. A jövedelmezőségi mutatókat százalékban fejezzük ki.

Vissza a

A tervezett időszak végén a raktárban lévő késztermékek egyenlegét a szerződéses kötelezettségek teljesítéséhez szükséges felhalmozási igény, az értékesítési feltételek és egyéb okok alapján határozzák meg, amelyek érvényessége a tervezett időszakon kívül esik.

Az árbevétel tervezésénél csak a vállalkozás raktárában lévő késztermékek kerülnek értékesítésre. A szállított termékek értékesítettnek minősülnek, ezért a közeljövőben ki kell fizetni. A gyakorlatban sajnos egy másik helyzet valószínűbb - a termék vásárlójának hosszú elszámolási időszaka vagy a fizetés meg nem érkezése, ami a követelések növekedéséhez vezet. E tekintetben a P (s) BU 10 „Követelések” szerint a termékek értékesítéséből származó bevételeket nem korrigálják a kétes követelések összegével. Erre a célra a vállalkozások aktuális kintlévőség nettó realizálható értéken szerepel a mérlegfőösszegben. A mérlegfordulónapi nettó realizálható érték meghatározásához kétes követelésekre képzett céltartalék kerül kiszámításra.

Tehát a WIP tartalmazza (az Orosz Föderáció adótörvénykönyve 319. cikkének 1. pontja):

Elvégzett, de a megrendelő által el nem fogadott munkák és szolgáltatások;
- a nem teljesített gyártási rendelések egyenlegei;
- saját gyártású félkész termékek maradványai;
- gyártás alatt álló anyagok és félkész termékek, feltéve, hogy azokat feldolgozták.

A szervezet a hónap végén értékeli a WIP egyenlegeket.

Ehhez a következő adatokat kell használni:

Elsődleges számviteli bizonylatok a nyersanyagok és anyagok, késztermékek mozgásáról és egyenlegéről (mennyiségi értelemben) műhelyek (gyártás és egyéb termelőegységek) szerint;
- adóelszámolás az Ön ebben a hónapban teljesített közvetlen kiadásai után.

Az aktuális hónap végi WIP-egyenlegek összegét a következő hónap közvetlen kiadásai között szerepelteti. Az adóidőszak végén az adóidőszak végi WIP egyenlegek összege a következő adóidőszak közvetlen kiadásai között szerepel.

Önállóan meghatározhatja a WIP és az aktuális hónapban gyártott termékek közvetlen költségeinek elosztási eljárását (végzett munka, nyújtott szolgáltatások) (Az Orosz Föderáció adótörvényének 319. cikke).

A következő feltételeket azonban figyelembe kell venni:

1) a felmerült költségeknek meg kell felelniük a gyártott termékeknek (elvégzett munka, nyújtott szolgáltatások). Ha lehetetlen közvetlen költségeket rendelni egy adott gyártási folyamathoz az ilyen típusú termék (munka, szolgáltatás) előállításához, akkor a számviteli politikában meg kell határoznia az ilyen költségek elosztásának mechanizmusát gazdaságilag megalapozott mutatók segítségével;
2) rögzítenie kell a megállapított eljárást a számviteli politikában;
3) ezt az eljárást legalább két adózási időszakra alkalmaznia kell.

Csakúgy, mint a WIP-egyenlegeknél, a raktárban lévő késztermékek egyenlegét az aktuális hónap végén értékeli (az Orosz Föderáció adótörvényének 319. cikkének 2. cikkelye).

Az értékeléshez a következőket kell használni:

Az elsődleges számviteli bizonylatokból származó adatok a késztermékek raktári mozgásáról és egyenlegéről (mennyiségi értelemben);
- a tárgyhavi közvetlen kiadások összege, csökkentve a WIP egyenlegéhez kapcsolódó közvetlen kiadások összegével.

[a poszt 1. részének vitatott áthelyezése kapcsán a geektimes-be (annak ellenére, hogy a 2. rész a Habrén maradt), az 1. részt visszateszem a helyére]

Az analitika és a különféle BI-eszközök monitorozása terén előbb-utóbb rábukkan a Power Pivot Excel bővítmény áttekintésére vagy említésére. Az én esetemben a Microsoft Data Day konferencián találkoztam vele.

Az eszköz nem hagyott nagy benyomást a bemutató után: Igen, ingyenes (Office licenc alatt), igen - van némi ETL funkcionalitás a különböző forrásokból (DB, csv, xls stb.) származó adatok beszerzése szempontjából, Joins ezekből a forrásokból és a RAM-ba betáplálva nagyságrendekkel nagyobb, mint 1 millió sort rögzít az Excelben. Röviden: megnéztem és elfelejtettem.

És emlékeznem kellett arra, hogy mikor vált szükségessé bizonyos jelenségek azonosítása az adatokban

A cikk megírását az a tény ihlette, hogy a RuNetben nem sok részletes információ található erről az eszközről a konkrét munkamódszerekről, egyre többet a csillagokról, ezért úgy döntöttem, hogy ezt az ismertetőt az eszköz tanulmányozása közben írom meg.

Valójában a probléma megfogalmazása (egy személytelen példával) a következő:

A csv fájl forrásadataiban:

Vannak számlasorokra részletezett kiskereskedelmi egységek, míg az azonos nevű üzletek eltérő címmel csak akkor megengedettek, ha különböző városban találhatók, de az eredeti adattömbben vannak olyan üzletek, amelyek ugyanabban a városban különböző címekkel találkoznak. annak ellenére, hogy az aljzatok neve megegyezik (az aljzat neve egyedi, azaz a hálózat egy egysége vagy különálló aljzat). Különleges esetként összesített formában:

Az adatok keresése és törlése a személyzet által iroda útján zavarja a következőket:

Adatok részletezése számla sorokhoz
Rekordok száma több millió sorban
Az sql eszközkészlet hiánya (például: Hozzáférés - nem tartozék)

Természetesen bármilyen ingyenes DBMS-t fel lehet tölteni (legalábbis az asztali verziót, akár a szervert is), de ehhez egyrészt adminisztrátori jogok kellenek, másrészt a cikk már nem a Power Pivotról szólna.

Feladat: Minden atomrekordhoz szükség van egy további számított mezőre, amely minden egyes üzletnévhez egyedi számú címet számol ugyanazon a városban. Ez a mező szükséges ahhoz, hogy gyorsan megtalálja az összes üzlet nevét olyan városban, ahol egynél több cím található.

Szerintem a legkényelmesebb iteratív módon megoldani és elmondani, azzal a feltételezéssel, hogy a DAX ismerete gyerekcipőben jár.
Ezért azt javaslom, hogy egyelőre szakadjunk el a feladattól, és vegyünk figyelembe néhány alapvető szempontot.

1. lépés: Miben különbözik egy számított oszlop a számított mértéktől?
Íme egy példa egy kiszámított oszlopra, amely az áfát beépített DAX-képletek segítségével kinyeri az ÁFA mezővel rendelkező szállítmányból:

ROUND([Szállítás áfával]*POWER(1,18;-1)*0,18;2)

Ahogy a példából is látható, a számított oszlop (nevezzük ÁFA) minden vízszintes atomi rekorddal működik.
Most adjunk hozzá egy számított mezőt az áfa nélküli darabárhoz:

ROUND([Szállítás áfával]*POWER(1,18;-1)/[Szállítási db];2)

Most összehasonlításképpen adjuk hozzá a mértékhez a darabonkénti átlagár kiszámítását:

Átlagos darabár áfa nélkül: =ROUND(AVERAGE([Mező_ár darabonként áfa nélkül]);2)

A képletből látható, hogy a mérőszám függőlegesen működik a kezdeti adatok oszlopával, ezért mindig tartalmaznia kell valamilyen függvényt, amely egy halmazzal működik (összeg, átlag, szórás stb.)

Az Excel kimutatástáblájába visszatérve így néz ki:

Felhívjuk figyelmét, hogy ha a számított áfa mezőben minden adatszinten (zöld körvonal az üzlet, a város vagy a tábla végösszeg szintjén) látható az összeg, ami alapvetően helyes, akkor a „Db. ár” számított mező árainak összege. áfa nélkül” (piros körvonal) vet fel kérdéseket.
De az "Átlagos áfa nélküli darabár" kiszámított mérőszámnak élethez való joga van ennek az elemző kockának a keretein belül.

Ebből arra a következtetésre jutunk, hogy a "Db ÁFA nélkül" számított mező egy segédeszköz az "Átlagos áfa nélküli darabár" mérték kiszámításához, és annak érdekében, hogy a felhasználót ne keverjük össze ezzel a mezővel, elrejtjük a listából. az ügyfelek pénzéből, így az átlagár mértékét.

Egy másik különbség a mérték és az oszlop között, hogy lehetővé teszi vizualizáció hozzáadását:

Például készítsünk egy KPI-t az árszórás mértékére 35%-os célhatárral úgy, hogy elosztjuk az eltérés gyökerét a számtani átlaggal.

Q_var:=STDEV.P([Mező_ár darabonként áfa nélkül])/ÁTLAG([Mező_Áfa nélkül darabár])

Ennek eredményeként egy ilyen táblázatot látunk az Excelben (mellesleg a számított segédár mező már nem található a jobb oldali elérhető mezők listájában):

A 80%-os arányra duplán kattintva látható, hogy az árak valóban az átlag körül ugrálnak:

Erősebb, mint 15%-os együtthatóval:

Hamar ezt a lépést megvizsgáltuk a PowerPivoton belüli mértékek és mezők közötti főbb különbségeket.

2. lépés Bonyolítás: Számítsa ki az egyes rekordok részesedését a teljes értékesítésből.
Íme az első példa az MS SQL Server és a DAX ablakfüggvény megközelítéseinek összehasonlítására:

Nyilvánvaló, hogy a pivot táblák keretein belül ez szó szerint 2 egérkattintással történik a billentyűzet érintése nélkül, de a megértés érdekében próbáljuk meg közvetlenül a PowerPivotban képletek segítségével.

sql-ben én így írnám (ne rúgj rá a hibákra, mert a Word nem ellenőrzi az SQL Server szintaxisát):

Kezdje Válassza ki a „t1.TT név”, „t1.város”, „t1.cím”, „t1.termék”, „t1.TTN”, „t1.TTDátum”, „t1.szállítás, db”, „t1. Szállítás áfával", "t1.Szállítás, db"/összeg("t1.Szállítás, db") több mint () mint megosztás a táblázatból t1 sorrendben "t1.Szállítás, db"/sum("t1.Szállítás , db ") leírás

Itt, amint látható, megnyílik az ablak az adatkészlet összes rekordján keresztül, próbáljunk meg egy hasonlót a PowerPivotban:

=[Szállítási db]/CALCULATE(SUM([Szállítási db]);ALL("Táblázat 1"))

Koncentráljunk a nevezőre: Fentebb már említettem, hogy a számított mező és a mérték közötti fő különbség az, hogy a mezőben a képleteket vízszintesen (egy rekordon belül), a mértékeket pedig függőlegesen (egy attribútumon belül) számolják. Itt a CALCULATE módszerrel keresztezhettünk egy mezőtulajdonságot és egy mérési tulajdonságot. És ha SQL-ben az Over()-on keresztül állítottuk be az ablak szélességét, akkor itt az All()-on keresztül.

Most próbáljunk meg ezzel a készségünk birtokában valami hasznosat tenni az adatainkkal, például emlékezve arra, hogy az átlagok körüli árszórási mutató széles tartományban változott, megpróbáljuk a 3 szigma szabályon keresztül kiemelni a statisztikai árkiugró értékeket.

Az sql ablakfüggvényei így fognak kinézni:

Válassza ki a "t1.TT név", "t1. város", "t1. cím", "t1. termék", "t1.TTN", "t1.TTDátum", "t1. szállítás, db", "t1 .szállítmány" lehetőséget. ÁFA-val", "t1.Áfa nélküli termékár", CASE WHEN ABS("t1.Tételenkénti ár áfa nélkül" - AVG("t1.Tételenkénti ár áfa nélkül") OVER()) > 3 * STDEV (" t1. ÁFA nélküli darabár") OVER() MAJD 1 MÁS 0 VÉGE, mint Outlier a táblázatból mint t1 Tovább

És itt ugyanez a DAX-ban:

If(ABS([Mező_ár áfa nélkül]-CALCULATE(AVERAGE([Mező_ár darabonként áfa nélkül]);ALL("Táblázat1")))>(3*CALCULATE(STDEV.P([Mező_ár darabonként áfa nélkül]) );all("1. táblázat")));1;0)

Amint látja, az ár kissé magas, 40,03 rubel számtani átlaggal.

3. lépés: Az ablakok szűkítése.
Most próbáljuk meg kiszámolni az egyes rekordok számított mezőjében azon rekordok számát a városban, amelyekhez ez a rekord tartozik.
MS-en SQL szerver az ablak függvényei így fognak kinézni:

Válassza ki a "t1.TT név", "t1. város", "t1. cím", "t1. termék", "t1.TTN", "t1.TTDátum", "t1. szállítás, db", "t1 .szállítmány" lehetőséget. ÁFÁ-val", "t1.Áfa nélküli darabár", count("t1.*) OVER(partíció "t1.City" szerint) mint cnt a táblázatból mint t1 Go

A DAX-ban:
=SZÁMÍTÁS(COUNTROWS("1. táblázat");ALLEXCEPT("1. táblázat","1. táblázat"[város]))

Ügyeljen az adatok táblázatbeli megjelenítési különbségére, szándékosan címeket dobtam a mérési területre, hogy kiszámítsam a számukat, és összehasonlítsam az új mezővel, amelyet a sorfejlécben az üzlet neve után jelenítettem meg.

A különbség jól látható: ha a szokásos címszámítás a város egyes pontjaira vonatkozik, és csak egy köztes összeget jelenít meg a "Város" aggregátumra, akkor az ablakfüggvények segítségével bármely cím értékét hozzárendelheti. aggregálja az egyes atomi rekordokhoz, vagy használja a számított mező néhány közbenső számításában (ahogyan fent látható).

Visszatérve az eredeti problémához
Tehát hadd emlékeztessem önöket a probléma eredeti megfogalmazására: minden atomrekordhoz szükség van egy további számított mezőre, amely az üzlet minden nevéhez egyedi számú címet számít ki ugyanazon a városban. Ne felejtsük el, hogy adatkészletünk a számla soraira részletezett, ezért az ablakon belüli címek számlálása előtt csoportosítani kell azokat.

SQL Server lekérdezés:

Az a1 as (Válassza ki a "t1.TT név", "t1. város", "t1. cím", "t1. termék", "t1.TTN", "t1.TTN dátum", "t1. szállítás, db " , "t1.Szállítás áfával", "t1.Áfa nélküli darabár", count(Különböző "t1.Cím") OVER(partíció "t1.Város", "t1.TT név" szerint) mint adrcnt a következő táblázatból t1) Válassza ki a * elemet az a1-ből, ahol adrcnt>1

Most már semmi sem akadályoz meg bennünket abban, hogy ezt tegyük a DAX-ban:

CALCULATE(DISTINCTCOUNT("1. táblázat"[cím]);ALLEXCEPT("1. táblázat";"1. táblázat"[város];"1. táblázat"[CT név]))

Ennek eredményeként lehetőségünk van olyan gyanús bejegyzéseket kiválasztani, ahol egy városon belül egy ponthoz több cím tartozik.

Természetesen a tanulmányozás során (más képletek után) világossá válik, hogy a DAX a PowerPivotban sokkal erősebb, mint az ebben a témában bemutatott, de határozottan nem lehet egyszerre befogadni a mérhetetlenséget.

Remélem érdekes volt.
A cikk folytatása

Az ingyenes PowerPivot bővítmény az Excel 2010-ben jelent meg. Hat nagyon hasznos eszközt tartalmaz, és független fejlesztők hozták létre. Az Excel 2013-ban a PowerPivot-függvények az adatmodellel együtt közvetlenül a program magjába vannak beépítve.

A tárgyalt adatmodell csak a PowerPivot egyes funkcióihoz biztosít hozzáférést, és az összes eszközhöz való hozzáféréshez magát a PowerPivot bővítményt kell telepítenie, amely az Office 2013 Professional Plus része. A bővítményt Office 365 Vállalati előfizetéssel is beszerezheti. Ha az Office 2013 otthoni és tanulói vagy normál verzióját használja, a PowerPivot motor továbbra is elérhető, de a PowerPivot ablak le van zárva, és egyes funkciók le vannak tiltva.

Jegyzet letöltése vagy formátumban, példák itt (több Excel fájlt és egy adatbázist tartalmaz txt formátumban)

A PowerPivot és az adatmodell előnyei és hátrányai

Kezdjük azzal, hogy megvizsgáljuk a PowerPivot három fő funkcióját, és elemezzük ezek megvalósítását az Excel egyes verzióiban.

Hozzon létre pivot táblákat több forrástáblából a VLOOKUP függvény használata nélkül. Ez a funkció az Excel 2013 összes verziójában elérhető. A normál verzióban nem jelenik meg a szó powerpivot(ehelyett a kifejezést használjuk adatmodell). Ha a PowerPivot bővítmény telepítve van, a táblák közötti kapcsolatok létrehozása egyszerűen elvégezhető a grafikus nézet segítségével.

Importáljon 100 millió sort egy munkafüzetbe. A PowerPivot táblák korlátlan számú sort tartalmazhatnak. Láttam már hasonló táblázatokat 100 millió sorral. Az egyetlen korlátozás a könyv fájlmérete, amely 2 GB, és a kötet véletlen hozzáférésű memória. A VertiPaq tömörítési algoritmus használatával szöveges fájl 50 MB a PowerPivot táblafájl által elfoglalt 4 MB-ra tömörítve. Ha egy ilyen táblázatban az oszlopok száma 10, akkor a sorok száma 950 millió. Ez a lehetőség az Excel 2013 összes verziójában elérhető. Az Office Standardban PowerPivot táblából importálhatunk rekordokat, és létrehozhatunk kimutatásokat, de nem tekintheti meg a rekordokat. A megtekintéséhez telepítenie kell a PowerPivot bővítményt. A pivot tábla rekordjainak megtekintésének képtelensége komoly kényelmetlenséget jelent, mivel bármilyen jelentés létrehozása előtt hasznos lenne áttekinteni a jelentésben szereplő információkat.

Hozzon létre speciális képleteket a DAX képletnyelv használatával. nem érhető el az Excel 2013 szabványos verzióiban. Az összes olyan nyelvi eszköz eléréséhez, amelyek lehetővé teszik új képletek létrehozását a PowerPivot-táblázatokban, és új számított oszlopok hozzáadását a kimutatásokhoz, telepítse a PowerPivot bővítményt. A DAX képletnyelv számos lehetőséget kínál a felhasználónak, bár nem túl könnyű megtanulni.

Az Excel összes verziójában elérhető PowerPivot adatmodell előnyei. Lent van egy sor további előnyök az adatmodell által biztosított:

• Volt egy függvény az összegek kiszámítására Különálló elemek száma ( lásd például ). Ezt a funkciót megelőzően manuálisan kellett meghatároznia a különböző elemek előfordulásának számát.
• A PivotTable összesített területe csak szűrt adatokat vagy az összes adatot tartalmazhatja. Kezdésként egy kimutatás létrehozásakor vegye fel az adatokat az adatmodellbe (1. ábra). BAN BEN Pivot tábla válassza ki a szűrőt Az első 10... Az összesített mező az első tíznek megfelelő mutatókat foglalja össze (2a. ábra). Mostantól létrehozhat egy összeget, amely a jelentés létrehozásakor kiszűrt kis rendelési mennyiségekkel rendelkező ügyfelek mérőszámait tartalmazza. Lépjen a kontextus fülre Konstruktőrés legördülő menüt Részösszegek Válassz egy lehetőséget A szűrt tételek szerepeltetése az összesítésben(2b. ábra). Vegye figyelembe, hogy ha nem hoz létre adatmodellt, ez a részösszeg opció nem lesz kiválasztható. Vegye figyelembe azt is, hogy a cím teljes összeg egy csillag (*) jelenik meg.
• A PivotTables Excel 2010 egy új csodálatos eszközzel rendelkezik - készletekkel, amelyek aszimmetrikus jelentéseket készítenek. A készletek egyetlen korlátozása az, hogy csak OLAP pivot táblákkal történő munkavégzéskor használhatók (lásd a részleteket). Ha normál táblát nyit meg a PowerPivot bővítménnyel, az automatikusan OLAP-kimutatástá alakul.
• Azok, akik hozzászoktak ahhoz, hogy a függvényt pivot táblákból gyűjtsék ki, eggyel kevesebbet tehetnek, és a pivot táblát kockaképletekre konvertálhatják. Ezeket a képleteket tetszőleges formátumban kivághatja és beillesztheti.

Rizs. 2. A korábban szűrt értékeket foglalja bele az összesítésbe: (a) az összesítés tartalmazza a 10 legnagyobb ügyfél bevételét; (b) a végösszegek tartalmazzák az összes ügyfél bevételét, bár az összefoglaló táblázatban csak a legjobb 10 szerepel

Az Excel Pro Plus PowerPivot-bővítményének előnyei. Ha az Excel Pro Plus olyan verzióját használja, amely támogatja a PowerPivot bővítményt, a következő előnyöket élvezheti:

• Egy PowerPivot tábla, amely akár 100 millió sort is tartalmazhat.
• A táblázatba rendezheti, szűrheti és számításokat adhat hozzá.
• Grafikus tervezési mód, ahol mezők húzásával hozhat létre hivatkozásokat.
• Lehetőség a mező tulajdonságainak megváltoztatására a modellben. Kiválaszthatja azokat a mezőket, amelyek megjelennek vagy nem jelennek meg a kimutatás mezőinek listájában.
• Egy elem rendezésének képessége FieldA(hónap neve) elemenként PoleB(hónapszám).
• Lehetőség a pivot tábla mezőinek megjelenítéséhez használt alapértelmezett számformátum beállítására. Ez a funkció nem érhető el egy normál kimutatásban.
• Lehetőség olyan mezők meghatározására, amelyek egy terméket, földrajzi régiót képviselnek, vagy képekre mutató hivatkozások.
• Valamiféle kulcsfontosságú teljesítménymutatók. Ezek az intézkedések könnyebben használhatók a kimutatásokban, mint az ikonkészletek.
• A Power View és a Power Map panelek elérése (lásd a részleteket).

Az adatmodell korlátai. Az adatmodell lehetővé teszi a szokásos Excel-adatok beágyazását egy OLAP-modellbe. Ennek a műveletnek vannak előnyei és hátrányai is. Az Excel 2013 fejlesztői megpróbálták kiküszöbölni az adatmodell korlátait és hiányosságait, de ezek egy része továbbra is megmaradt:

• Korlátozott számú függvénykészlet az összegek kiszámításához. Annak ellenére, hogy megjelentek az új funkciók, mint pl Különálló elemek száma, funkciók, mint pl Munka.
• Nincs csoportosítás. A PowerPivot nem tudja csoportosítani a kimutatásokat. A napi adatok nem csoportosíthatók hónap, negyedév vagy év szerint. A csoportosítást természetesen úgy is elvégezheti, hogy a megfelelő számításokat hozzáadja az eredeti adatkészlethez, de így is egyszerűbb lenne egy speciális csoportosítási funkció használata.
• Szokatlan asztalnézet. Egy klasszikus pivot tábla sorainak megtekintéséhez elég duplán kattintani a táblázat cellájára. Amikor az adatmodellel dolgozik, ez a művelet az első ezer sort jeleníti meg.
• Nincsenek kiszámított pápák vagy kiszámított tételek. Az adatmodell nem támogatja a számított mezőket vagy a számított tagokat. A PowerPivot-bővítmény telepítésekor a DAX-mérések lehetővé teszik ezen számítások elvégzését. Ha azonban nincs telepítve a PowerPivot bővítmény, akkor nem tudja alkalmazni a számított mezőket vagy a számított tagokat.
• Csak az Excel 2013 támogatása. Az adatmodellt használó munkafüzetek nem tölthetők be az Excel korábbi verzióiba.
• Rosszul végrehajtott rendezés. A szokásos pivot tábláknál a január mindig február előtt van. Az Excelben a helyes rendezés a hónapok és a hét napjainak nevére összeállított egyéni listák használatán alapul. Az adatmodell-alapú kimutatások alapértelmezés szerint nem használnak egyéni listákat. A probléma megoldásához további nyolc egérkattintásra van szüksége a kimutatás minden egyes mezőjében.

Egyesítsen több táblát az Excel 2013 adatmodelljével

A Microsoft szakértői végrehajtották legjobb eszközök PowerPivot az Excel 2013-ban. Az Office Pro Plus a teljes funkcionalitású PowerPivot, Power View és Inquire modulokkal is elérhető. Az Excel 2013 szabványos kiadásai az adatmodell segítségével valósítják meg a PowerPivot alapvető képességeit. ábrán. 1 egy párbeszédpanelt mutat be Hozzon létre egy kimutatást. Egy jelölőnégyzet jelenléte ebben az ablakban azt jelenti, hogy PowerPivottal van dolgod. További információkért több tábla egy pivotban való kombinálásáról az adatmodell használatával lásd:. Ez a funkció akkor is működik, ha telepítve van a PowerPivot, és amikor az Excel beépített szolgáltatásait használja.

Express View használata. Az adatmodell alapján létrehozott pivot tábla tetszőleges cellájának kiválasztása után megjelenik az expressz nézet ikon (3. ábra). Ez az ikon csak az Excel 2013 felhasználói számára látható, és csak akkor jelenik meg, ha a kimutatás adatmodellre épül. Az Express View célja, hogy segítse a pivotTable-t módosítani készülő felhasználót. Az ikonra kattintás után megjelenik egy ablak, amelyben tippek jelennek meg a pivot tábla megváltoztatásához.

Ebben az esetben az Express View ablakban megjelenő eszköztippek nem túl hasznosak. ábrán. A 4. ábra a jövedelmek régiónkénti lebontásának eredményét mutatja.

Rizs. 4. Az így kapott pivot tábla nem túl hasznos a vezetők számára, de sok esetben egy gyors áttekintés segít egy informatívabb pivot tábla kialakításában.

Hozzon létre egy új kimutatástáblát egy meglévő adatmodell alapján. Ez a művelet egy kicsit bonyolultabb, mint a szokásos pivot táblákkal végzett munka során:

1. Válasszon csapatot Beszúrás — Pivot tábla.
2. A párbeszédpanelen Hozzon létre egy kimutatástállítsa be a kapcsolót Használjon külső adatforrást(5a. ábra). Még ha az adatokat egy munkafüzet PowerPivot táblájában tárolja is, ne feledje, hogy a PowerPivot eredetileg egy külső bővítmény volt.
3. Kattintson a gombra Válassza ki a kapcsolatot Meglévő kapcsolatok.
4. Válassza ki a lapot táblázatok.
5. Válasszon ki egy elemet Táblázatok a Könyv adatmodellbenés kattintson Nyisd ki(5b. ábra).

Különböző elemek számának számolása. A PivotTables egyik ilyen funkciója az egyedi rekordok számának számlálása. Az Excel PivotTables képes megszámolni a szöveges értékeket. Egy ilyen pivot tábla tipikus példája látható az ábrán. 6. Ebben a táblázatban a mező Ágazat a VONALAK területen van, és a mezőkben VevőÉs Jövedelem- az ÉRTÉK területen. A kimutatástáblázat által megjelenített ügyfelek teljes száma 563, ami nem igaz. Valójában ebben az esetben 563, az ügyfeleknek megfelelő nem üres rekordról beszélünk, amelyek megismételhetők. Az ilyen átfedés a hagyományos pivot táblák korlátaihoz kapcsolódik.

Ha a kimutatástábla adatmodellre épül, kövesse az alábbi lépéseket:

1. Kattintson Jobb klikk vigye az egeret a területen lévő bármelyik cellára Az Ügyfél oszlopban található cikkek száma(B1:B9 tartomány). Válasszon ki egy elemet Értékmező beállításai.
2. Az ablakban Értékmező beállításai lapon Művelet válassza ki Különálló elemek száma(Megjegyzendő, hogy a szokásos pivot táblákhoz képest nincsenek funkciók MunkaÉs Index). Kattintson az OK gombra.
3. A pivot tábla 27 egyedi ügyfél listáját jeleníti meg, amelyek közül 11 a szektorban található Felszerelés(7. ábra).

A PowerPivot bővítmény használata az Excel 2013 Pro Plus programban

Ha az Ön Excel 2013 verziója támogatja a PowerPivot bővítményt, akkor a következőkre lesz lehetősége:

• Adatokat tölthet be a PowerPivotba különböző utak. Több adatforrás áll rendelkezésére, összekapcsolt táblázatok, adatok másolása és beillesztése, valamint előfizetések létrehozásának lehetősége.
• A PowerPivot táblákban megtekintheti, rendezheti és szűrheti az adatokat.
• Több millió sort importálhat egyetlen munkalapra egy PowerPivot-táblázatban.
• Létrehozhat DAX-képleteket táblázatban vagy egy új számított mezőként intézkedés. A DAX rövidítés jelentése Adatelemzési kifejezések(Adatelemzési kifejezések). A DAX 117 függvényt tartalmaz, amelyek kétféle számítás elvégzését teszik lehetővé. 81 standard funkció Az Excel segítségével kiszámított oszlopokat adhat hozzá egy táblázathoz egy PowerPoint ablakban. Az 54 funkcióval pedig új mértékeket hozhat létre a kimutatásokban. Ezekkel a funkciókkal a PivotTables még soha nem volt látható.
• Az Ön rendelkezésére állnak további módokon hivatkozások létrehozása, beleértve a hivatkozások megjelenítéséhez használt diagramnézetet.
• Elrejtheti vagy átnevezheti az oszlopokat.
• Kimutatás létrehozása előtt beállíthatja az oszlopok numerikus formázását.
• Mostantól kategóriákat rendelhet a mezőkhöz, pl. földrajz, URLlinktovábbkép vagy web link.
• Meghatározhat KPI-ket vagy hierarchiákat.
• Ha cégénél telepítve van a PowerPivot Server, akkor interaktív PowerPivot-jelentéseket tehet közzé egy SharePoint-webhelyen.

Ha több millió rekordot kíván feldolgozni, telepítse az Office és a PowerPivot 64 bites verzióit. Ebben az esetben a RAM hiányával kapcsolatos korlátozások továbbra is érvényben maradnak, de részben kiegyenlítődnek, mivel a PowerPivot közel 10-szeresére képes tömöríteni a PowerPivot fájlban tárolt adatokat. Az Office 2013 64 bites verziója olyan memóriához fér hozzá, amely meghaladja a Windows 32 bites verziójára beállított 4 GB-os korlátot.

Aktiválja a PowerPivot bővítményt. Ha Office 365-öt, Office 2013 Pro Plus-t, Office 2013 Enterprise-t vagy dobozos Excel 2013-at használ, akkor hozzáférhet a PowerPivot-bővítményhez. A bővítmény engedélyezéséhez kövesse az alábbi lépéseket:

1. Nyissa meg az Excel 2013-at. Látja a PowerPivot szalaglapot (8. ábra)? Ha igen, kihagyhatja a következő lépéseket.
2. Hajtsa végre a parancsot Fájl — Lehetőségek, válassza a Kiegészítők lehetőséget. Csepp Ellenőrzés az ablak alján található, válassza ki COM-bővítményekés kattintson a gombra Megy(9a. ábra).
3. Az elérhető COM-bővítmények listájában keresse meg a bővítményt Microsoft iroda PowerPivot for Excel 2013. Jelölje be a megfelelő négyzetet, és kattintson az OK gombra (9b. ábra). (Ne feledje, hogy szüksége van a PowerPivot for Excel 2013 bővítményre. Az Excel 2010-ben használt régi "PowerPivot for Excel" bővítményt az Excel 2013 nem támogatja.)
4. Ha a PowerPivot lap nem jelenik meg a szalagon, lépjen ki Excel programok 2013 és futtassa újra.

A bővítmény telepítése után a PowerPivot lap jelenik meg az Excel 2013 szalagon (8. ábra).

Szövegfájl importálása. Az eredeti táblázat 1,8 millió rekordot tartalmaz a BigDatal.txt fájlban (a fájl egy része a Jegyzettömb ablakában a 10. ábrán látható). Az oszlopfejlécek a fájl első sorában találhatók. Előfordulhat, hogy el kell távolítani a nem szabványos karakterláncokat a fájl tetejéről, hogy elkerüljük a problémákat, amikor azokat a PowerPivot bővítmény feldolgozza. Sajnos a fájlban szereplő dátumok amerikai formátumúak, ezért a jövőben hibásan dolgozzák fel azokat.

Ha 1,8 millió sort tartalmazó fájlt szeretne importálni a PowerPivotba, kövesse az alábbi lépéseket:

1. Kattintson a PowerPivot fülre. Kattintson az ikonra Ellenőrzés. A PowerPivot alkalmazás ablaka megjelenik a képernyőn, saját szalaggal (11. ábra). Az ablak közepén egy táblázat található, amely lehetővé teszi a PowerPivot-modell adatainak megtekintését. A PowerPivot ablak a következő három lapot tartalmazza: Az elejére, KonstruktőrÉs Továbbá.

Rizs. 11. Kattintson a Kezelés ikonra az Excel szalag PowerPivot lapján a PowerPivot bővítményablak megnyitásához, amely megjeleníti a saját szalagját.

2. Importálja a táblát a BigDatal.txt fájlból. Ehhez a csoport Külső adatok beszerzése kattintson a gombra Más forrásokból. Egy párbeszédpanel jelenik meg a képernyőn. Táblázat importáló varázsló.

3. Az ablakban Táblázat importáló varázsló válassza ki a legalacsonyabb elemet Szöveges fájl. Kattintson a gombra További.

4. Írja be a mezőbe Barátságos kapcsolat neve a kapcsolat neve.

5. Kattintson a gombra Felülvizsgálatés keresse meg a szövegfájlt. Ha az adatok fejléceket tartalmaznak, a PowerPivot észleli őket.

6. Ellenőrizze, hogy a vessző van-e kiválasztva elválasztóként. Csepp Oszlopelválasztó számos szabványos elválasztót jelenít meg, például vesszőt, pontosvesszőt, függőleges sávot stb.

7. Ha le szeretné állítani valamelyik oszlop importálását, törölje a megfelelő jelölőnégyzetek bejelölését. A szöveges fájl készen áll a RAM-ba való betöltésre. Ne feledje, hogy jelentősen csökkentheti a felhasznált RAM mennyiségét az extra oszlopok eltávolításával, különösen, ha hosszú szöveges értékeket tartalmaznak. Ehhez törölje a jelet a rejtett oszlopoknak megfelelő négyzetekből (12. ábra).

8. Kérjük, vegye figyelembe, hogy minden mezőben megtalálhatók a szűrők legördülő listái. Ezekkel a listákkal rendezheti és szűrheti azokat az adatkészleteket, amelyek több tíz és százezer rekordot tartalmaznak (például kizárhat bizonyos adatokat a jelentésből). Ne feledje, hogy nagy számú rekord esetén ezek a műveletek nagyon lassúak.

9. A gombra kattintás után Kész A PowerPivot elkezdi betölteni a fájlt a memóriába. Ez az aktuálisan betöltött sorok számát jeleníti meg (13. ábra).

10. A fájl importálásakor megjelenik a betöltött sorok száma. Kattintson a gombra Bezárás a PowerPivot ablakhoz való visszatéréshez.

11. A PowerPivot ablaka 1,8 millió rekordot jelenít meg. Megtekintésükhöz használhatja a függőleges görgetősávot. Ezenkívül rendezheti, módosíthatja a számformátumot vagy alkalmazhat szűrőt (14. ábra).

Rendeljen számformátumot az oszlopokhoz. A dátum mező a dátum és az idő tárolására szolgál. Ha a forrásadatok nem tartalmaznak időkomponenst, válassza ki a dátumfejlécet, és használja a legördülő listát Formátum a csoportban Formázás lapokat Az elejére PowerPivot szalagok. Válassza ki a 2001.03.14. Oszlopba Jövedelem alkalmazza a pénznem formátumát. Ha nem szeretné, hogy a pivot tábla a tizedesvessző utáni számokat jelenítse meg, csökkentse a tizedesjegyek számát nullára.

Ha jobb gombbal kattint egy oszlopra, megjelenik egy helyi menü, amely lehetővé teszi az oszlopok átnevezését, javítását és másolását. Ezzel a menüvel elrejthet egy oszlopot a kimutatás mezőlistájában az Excelben.

Bár a PowerPivot úgy néz ki, mint az Excel, valójában nem az Excel. Nem tudja megváltoztatni az egyes cellákat. Ha olyan képletet ad hozzá az E1 cellához, amely kiszámítja az összeget, akkor ez a képlet automatikusan átmásolódik az összes sorba. Ha egy értéket egy cellában formáz, akkor az oszlop összes cellája is formázva lesz.

Az oszlopok szélességének módosításához húzza az oszlopnevek közötti szegélyt (mint az Excelben).

Tehát 1,8 millió rekordja van, amelyek rendezhetők, szűrhetők és pivot táblákba kombinálhatók. Vegye figyelembe, hogy a szövegfájlból importált 1,8 millió sor az Excel-munkafüzetben van tárolva. Az .xlsx fájlt ide másolhatja új számítógép, ami után az összes sor az új számítógépen lesz. Az eredeti szövegfájl 58 MB méretű, és tömörített fájl Az Excel mindössze 4 MB méretű.

Függelék excel adatok másolás és beillesztés módszer. Példa PowerPivot táblánkban az áruházi információk a mezőre korlátozódtak Kód[bolt]. Az üzlet nevéről vagy helyéről nem volt információ. Kicsivel megoldható a probléma Excel fájl, amely az üzletkódokat nevekhez és egyéb azonosító információkhoz rendeli hozzá. Ezeket az adatokat hozzáadhatja egy új laphoz a PowerPivot ablakban. Használhatja a másolást és beillesztést az alábbiakban leírtak szerint, vagy létrehozhat egy linkelt táblázatot (lásd a következő részt), amely kényelmesebb a használata.

A másolási és beillesztési műveletek használatához kövesse az alábbi lépéseket:

1. Nyisson meg egy munkafüzetet, amely egy olyan tartományt tartalmaz, amely az üzletazonosítókat nevekhez kapcsolja (15. ábra).
2. Válassza ki az adatokat a Ctrl+Shift* billentyűkombinációval.
3. Másolja az adatokat a Ctrl+C billentyűkombináció megnyomásával.
4. Lépjen a PowerPivot lapra az Excelben.
5. Kattintson a gombra Ellenőrzés a PowerPivot ablak megnyitásához. Most már látnia kell az előre importált 1,8 millió soros adatkészletet.
6. A lap bal oldalán Az elejére A PowerPivot ablakban kattintson az ikonra Beszúrás. Egy párbeszédpanel jelenik meg a képernyőn. Nézet beszúrása.
7. Adjon az új táblának értelmesebb nevet, mint az alapértelmezett Table név, például A boltok(16. ábra). Kattintson az OK gombra.

Rizs. 16. Ablak Nézet beszúrása

A PowerPivot ablak új lapot kapott A boltok amelyeken vannak további információ az üzletekről. Kérjük, vegye figyelembe: a PowerPivot ablakának alján vannak a munkalapok fülei (17. ábra). A vágólapról beillesztett adatok az Excel adatok statikus másolatai. Ha az Excel adatok megváltoznak, másolja ki, és futtassa a PowerPivot parancsot Behelyezés cserével.

Excel adatok hozzáadása linkeléssel. Az előző részben egy táblázat került hozzáadásra A boltok másolási és beillesztési műveletek végrehajtásával. Ez valójában két másolatot hoz létre az adatokból. Az egyiket egy Excel munkalap, a másikat pedig egy PowerPivot ablakban tárolja. Ha a forráslap megváltozik, a PowerPivot ablak tartalma változatlan marad. Ha ez a helyzet nem felel meg Önnek, kapcsolja össze az Excel táblázat adatait a PowerPivot ablakban lévő adatokkal:

1. Először konvertálja az adatokat (mint a 15. ábrán) táblázattá úgy, hogy a tartomány bármely cellájára kattint, és lenyomja a Ctrl+T billentyűkombinációt.
2. Válassza ki a környezetfüggő lapot Konstruktőr. Az újonnan létrehozott tábla neve, a Table1 a szalag bal oldalán jelenik meg. Írjon be egy új nevet ebbe a mezőbe, például Store_ID.
3. Menjen a lapra powerpivotés a csoportban táblázatok kattintson Hozzáadás az adatmodellhez. Létrejön a táblázat másolata, amely megjelenik az ablakban powerpivot(18. ábra).

Rizs. 18. Új asztal be PowerPivot- Store_code

Kapcsolatok meghatározása. Két táblázat összekapcsolásának általános módja az Excelben a VLOOKUP függvény használata. A PowerPivotban ez a feladat sokkal könnyebben megoldható:

1. A PowerPivot ablakban kattintson a Az elejéreés kattintson a gombra Teljesítmény diagramok. Két táblázat jelenik meg egymás mellett (19. ábra).

2. Kattintson a mezőre Kód a fő táblázatban (BigData) húzza át a mező fölött Az üzlet kódja területeken A boltok. A létrehozott kapcsolatnak megfelelő nyilak jelennek meg.

3. A PowerPivot táblázathoz való visszatéréshez kattintson az ikonra Adatábrázolás lapon található Az elejére ablak powerpivot.

Számított oszlopok hozzáadása a DAX segítségével. A PowerPivot adatokból létrehozott kimutatások egyik hátránya, hogy nem tudják automatikusan évenként csoportosítani a napi adatokat. Ezért a kimutatás létrehozása előtt használja a DAX képletnyelvet egy új számított oszlop hozzáadásához a PowerPivot táblához.

1. Ugrás a könyvjelzőhöz powerpivot. Kattintson a gombra Ellenőrzésés a megnyíló ablakban kattintson az első munkalap fülére - Értékesítés az ablak alján található powerpivot.
2. A jobb szélső oszlop ún Oszlop hozzáadása. Kattintson az üres oszlop első cellájára.
3. Kattintson az ikonra fx a képletsor bal oldalán található. Egy párbeszédpanel jelenik meg a képernyőn. Funkció beszúrása amely számos kategóriát tartalmaz. Válasszon egy kategóriát dátum és idő. Kérjük, vegye figyelembe, hogy a listában megjelenített funkciók eltérnek a kategória funkcióitól dátum és idő Excelben.
4. Görgessen végig a listán, és válasszon ki egy funkciót ÉVés nyomja meg rendben(20. ábra). Kattintson az oszlop fejlécére rendelés dátuma. felépítmény powerpivot az =YEAR([Rendelés dátuma] képletet javasolja. Egészítse ki a képletet a záró zárójel beírásával és a gomb megnyomásával Belép. Az Excel kitölti az oszlopot a dátumhoz társított évértékekkel.
5. Kattintson jobb gombbal az oszlopra, és lépjen be helyi menü opció kiválasztása Oszlop átnevezése. Adjon meg egy új oszlopnevet, például Év(21. ábra).

PivotTable létrehozása:

1. Menjen a lapra Az elejére szalagok powerpivot. Bontsa ki a gomb alatti listát Pivot táblaés válassza ki az elemet Pivot tábla(22. ábra).
2. A megnyíló ablakban válassza ki azt a kapcsolót, amely meghatározza a pivot tábla új lapra történő beillesztését. Ismét visszatér az Excel ablakba. A PivotTable mezőlista megjeleníti a kimutatásban található összes táblát. powerpivot. Az egyes táblázatokban szereplő mezők megtekintéséhez nyissa meg a táblázatnak megfelelő listát (a "háromszögre" kattintva).
3. A mezőlistában powerpivot nyissa ki az asztalt nagy adatés válassza ki a mezőt Jövedelem. Táblázat kibontása A boltok o és válasszon ki egy mezőt benne Vidék. Az Excel létrehoz egy pivot táblát, amely régiónként jeleníti meg az értékesítéseket (23. ábra). Így az Ön rendelkezésére állt egy pivot tábla, amely 1,8 millió adatsor alapján épül fel, és virtuális hivatkozást tartalmaz egy kapcsolódó táblára.

Rizs. 22. PivotTable létrehozása ablakban powerpivot

Ezután használhatja a környezetfüggő lapok eszközeit Munkavégzés pivot táblákkal a kimutatástábla formázásához. Például alkalmazhatja a pénznemformátumot, és átnevezheti a mezőt Összeg oszloponként Bevétel, válasszon egy formátumot átlapolt vonalakkal stb.

A PowerPivot és az Excel kimutatások közötti különbségek. Ha eddig csak normál Excel-kimutatásokat hozott létre, akkor a kimutatások powerpivot kényelmetlennek tűnhet számodra. Sok probléma oka nem magához a bővítményhez kapcsolódik. powerpivot, hanem azzal, hogy a pivot table powerpivot egy OLAP PivotTable, és úgy viselkedik. Amikor pivot táblákkal dolgozik powerpivot figyelembe kell venni, hogy:

• Nincs automatikus rendezés a hét napjai szerint (hétfő, kedd, szerda stb.). A helyes rendezés végrehajtásához válassza ki a parancsot Továbbilehetőségekválogatás — Általemelkedő - Továbbá. Törölje a jelölést a jelölőnégyzetből Automatikus rendezés, bővítse ki a listát Rendezés az első kulcs szerintés válassza ki a következő sorrendet: hétfő, kedd, szerda, csütörtök, péntek, szombat, vasárnap.
• A mezők rendezéséhez a normál kimutatástáblákban a következő technikát használhatja (húzás helyett). Válasszon ki egy cellát, amely például a szót tartalmazza péntek, és írja be a szót ebbe a cellába hétfő. A gomb megnyomása után Belép oszlop adatait hétfőátkerül az új oszlopba. Kérjük, vegye figyelembe, hogy ez a technika nem alkalmazható pivot táblákkal végzett munka során. powerpivot.
• Az Excel felülettel történő képletek bevitele során a bennük szereplő cellák egérkattintással vagy a kurzorbillentyűk használatával kiválaszthatók. Valószínűleg egy kiegészítő powerpivot egérrajongók készítettek, így képletek létrehozásakor powerpivot csak az egér használható.
• A gombra kattintás után Frissítés a környezetfüggő lapon található Elemzés, az Excel frissíti az adatokat a kimutatásban. Gondolja át, mielőtt ezt megteszi az Excel 2013-ban. Ez a példa 1,8 millió sort importál újra.

Kétféle DAX-számítás

Most néztünk meg egy példát a DAX (ÉV) függvény használatával egy számított oszlop deklarálására egy ablakban megjelenített táblázatban. powerpivot. Az ilyen oszlopok létrehozásához 81 függvényt használnak, amelyek többségét közvetlenül az Excelből másolják. A DAX-függvények közül sok hasonló a megfelelő Excel-függvényekhez, néhány kivétellel, amelyeket alább tárgyalunk. A DAX segítségével új számított mezőket is létrehozhat egy kimutatásban. Ezek a függvények nem egyetlen cellaérték kiszámítására szolgálnak, hanem a pivot tábla celláihoz társított szűrt sorok értékeinek meghatározására (összesített függvények). A DAX-ban 54 ilyen funkció található. A PowerPivot igazi ereje éppen ezekben a funkciókban rejlik.

DAX függvények használata számított oszlopokban. Ezek a függvények nagyon hasonlítanak a hagyományos Excel függvényekhez, így legtöbbjük nem szorul további magyarázatra. Néhány DAX-függvény azonban eltér az Excel-függvényektől:

• A ritkán emlegetett Excel-függvény RAZNDAT átnevezték YEARFRAC-ra, és átírták a kódját. Ami azt illeti, maga a RAZDAT függvény nem szerepel az Excel súgójában. Ráadásul a függvényvarázslóban sincs benne. Ha pedig kézzel írja be a függvény nevének első betűit, az Excel nem jelenít meg utalást a legördülő listában. Ezt egészen egyszerűen magyarázzák. Ez a függvény eredetileg nem egy Excel függvény. Csak az Excel támogatja, hogy kompatibilis legyen más táblázatkezelő rendszerekkel. Az Excelben ez a funkció a Lotus 1-2-3 táblázatokból származik. További részletek megtekintése.
• Excel funkció A TEXT átnevezve a következőre: FORMAT.
• A SUMIFS funkciót a továbbfejlesztett CALCULATE funkció váltotta fel.
• A VLOOKUP függvény helyett az egyszerűbb RELATED függvényt használjuk.
• A DAX bevezeti a BLANK() függvényt. Mivel az összesítő függvények némelyike ​​az ALLN0NBLANKR0W vagy FIRSTNONBLANK függvényre alapozhatja a számításokat, a BLANK() függvény az IF() függvény argumentumaként használható, hogy kizárjon néhány sort a mértékszámításokból.
• A KIVÁLASZTÁS funkciót átnevezték SWITCH-ra. Míg a SELECT funkció 1 és 255 közötti numerikus értékeket vesz fel argumentumként, a SWITCH funkció programozható úgy, hogy más értékekkel is működjön.

A KAPCSOLÓDÓ függvény használata oszlopszámítások elvégzéséhez egy másik táblázat értékei alapján. A táblázatban szereplő számítások során powerpivot előfordulhat, hogy más cellákban lévő értékekre kell hivatkoznia powerpivot. Az Excel szabványos verziójában ilyen esetekben a VLOOKUP függvényt használják. A PowerPivot a KAPCSOLÓDÓ funkciót használja.

Ebben a példában a BigData táblát használjuk, amely tartalmazza a mezőt Kód[üzlet] és Jövedelem(napi eladások összege). Az értékesítési jelentések gyakran négyzetméterenkénti eladásokat használnak. Egy linkelt táblázatban A boltok mezők vannak Az üzlet kódjaÉs Kereskedelmi terület. Ezen mezők halmaza tartalmazza a számítások elvégzéséhez szükséges összes adatot.

Új képlet létrehozásának megkezdéséhez lépjen a táblázathoz powerpivotés kattintson egy üres oszlopcellába Oszlop hozzáadása. Írjon be egy egyenlőségjelet, és kattintson az oszlop egyik cellájára Jövedelem. Kezdje el beírni a képletet powerpivot:= [Bevétel]. Írjon be egy perjelet az osztás jelzésére. Most be kell lépnie a mezőbe Kereskedelmi terület táblázatban található A boltok. Indítsa el a szolgáltatás bejegyzését ÖSSZEFÜGGŐ(. Adja meg a táblázat nevének első néhány betűjét - Ma. Megjelenik a táblázat mezőinek listája A boltok(24. ábra). Kattintson duplán a mezőre Kereskedelmi terület. Zárja be a képletet egy záró zárójel beírásával, és nyomja meg az Enter billentyűt. Kattintson a jobb gombbal egy oszlopra, és válasszon egy lehetőséget a helyi menüből Oszlop átnevezése. Adjon hozzá új nevet, pl. SalesAkvM.

Számított oszlopok és kapcsolatok segítségével számos érdekes kimutatást hozhat létre. A számított oszlopok az alapul szolgáló adatok minden sorához kerülnek kiszámításra. Ennek eredményeként a képlet Eladó terület négyzetméterenkénti értékesítése 1,8 millió alkalommal hajtották végre a táblázatban powerpivot. A DAX-képletek használatával létrehozhat egy új számított mezőt, amely a végső kimutatás minden cellájához egyszer kerül kiszámításra.

Hozzon létre egy számított mezőt egy kimutatásban DAX-képletekkel. A DAX számított mezők számos előnnyel rendelkeznek a hagyományos számított mezőkkel szemben. Egyik előnye, hogy egy ilyen mező használatakor a számításokat egyszer hajtják végre a kapott pivot tábla minden cellájában. ábrán. A 25. ábra egy pivot táblát mutat, melynek B5:C12 celláiban számértékek találhatók. A létrehozott DAX számított mező csak 16 numerikus kimutatástábla cellához kerül kiszámításra. Ez sokkal gyorsabb, mint az 1,8 millió cellában lévő értékek kiszámítása, majd összegzése. Mert amerikai formátum dátumok be forrás fájl A BigData.txt egy dátumsorozat a modellben powerpivot hibásan jelenik meg, ami egy mezőt eredményez (üres) a dátumok vágásakor. Mielőtt elkezdené az első számított mező létrehozását, ismernie kell a szűrőket.

Rizs. 25. Modell alapú kimutatás powerpivot

Előre beépített szűrők alkalmazása a DAX számított mezőkre. A DAX számított mezők automatikusan alkalmazzák a megfelelő cellákban előre meghatározott szűrőket. Először minden szűrő kerül alkalmazásra, majd a számítások végrehajtása a DAX-mezőkön történik. Tekintsük a B6 cellát, az ábrán látható. 25. Próbáljunk meg válaszolni arra a kérdésre, hogy hány szűrő van beállítva a B6 cellában. Azt fogja mondani, hogy két szűrő van meghatározva. Azt hiszem, négy szűrő van definiálva ebben a cellában.

A normál automatikus szűrőkkel együtt a cellákban lévő adatok szeletekkel is szűrhetők. Az első szelet létrehozása után a B6 cellában a 2006-os évnek megfelelő bejegyzések jelennek meg. A Bellevue Square Managers üzletek értékesítési adatait egy második szeletelő hozzáadásával is megtekintheti. Ez a két szelet alkotja az első két szűrőt. A Szemüvegek sor fejléce egyben szűrőként is működik. Ez lesz a harmadik szűrő. És végül a negyedik szűrő Washington állam. A DAX számított mezőkben először a korábban létrehozott szűrőket alkalmazzák, majd a DAX képlet alkalmazásának eredményét számítják ki.

Hozzon létre egy számított DAX mezőt. Számított mező létrehozásához lépjen az Excel menüszalagra, és válassza ki a lapot powerpivotés futtassa a parancsot Számított mezők — Hozzon létre egy számított mezőt. Egy párbeszédpanel jelenik meg a képernyőn. Számított mező. A terepen A táblázat neveírjon be egy táblázatnevet. Adjon nevet a számított mezőnek, pl. Az üzletek száma. A képletbeviteli panelen adja meg a képletet. Funkciónevek beszúrásához kattintson az ikonra fx. A mezőnevek beírásakor kezdjen el beírni néhány karaktert a táblázat nevéből, majd használja az automatikus kiegészítést a kívánt mező kiválasztásához.

Ha befejezte a képlet beírását, kattintson a gombra Ellenőrizze a Képletet a képlet szintaxisának teszteléséhez. Figyelje meg a képletre vonatkozó tippet, amely a képletteszt eredményeit mutató sáv felett jelenik meg. Kattintson a mezőbe Leírás az eszköztipp elrejtéséhez. A képletellenőrzés eredménye megjelenik a képernyőn. Ha a képlet érvényesítése sikeres volt, egy üzenet jelenik meg A képlet nem tartalmaz hibát(26. ábra). Kattintson rendben a számított mező hozzáadásához a kimutatás mezőlistájához. asztal A boltok modellek powerpivot az új mező nem jelent meg, de a pivot táblában van (27. ábra). Csakúgy, mint egy normál pivot táblában!

Létrehozása után egy számított mező felhasználható a jövőbeni számítások elvégzésére. Számított mező Bolti értékesítés(28. ábra) a következő képletet használja:

SUMX(BigData;BigData[bevétel])/[Üzletek száma]

Rizs. 28. Mező Bolti értékesítés a korábban létrehozott mező alapján számítva Az üzletek száma

A pivot tábla szerkezetének egyszerűsítése érdekében nem jelenítheti meg a mezőket Összeg oszloponként BevételÉs Az üzletek száma, elhagyja a pályát Bolti értékesítés.

Hogyan lehet törölni a szabványos szűrést. Most fontolja meg a következő problémát. ábrán. 29 C5 cella kiemelve. Ennek a cellának a szűrők alkalmazása eredményeként az Amber nevű kereskedőnek és a 2014.06.01-i értékesítési dátumnak megfelelő rekordok jelennek meg. Ez és a következő szakaszok (a gyakorlatig) Excel fájlt használnak Szűrési példa.xlsx

Ahogy korábban említettük, a kimutatástáblázat celláira alkalmazott összes szűrőt automatikusan alkalmazza a rendszer a számított DAX-mezőre. Sok esetben ez nem elfogadható. A G6 cellában 165 dollárnyi eladott tétel látható, amely nem kapcsolódik egyetlen eladóhoz sem. Az itt alkalmazott irányelv az, hogy a 165 dolláros összeget az ugyanazon a napon eladott mennyiségből való részesedésük (százalékban) alapján más személyekhez rendelik, és Hughes értékesítő nem vesz részt az értékesítésben. Megvalósítható ez a házirend ebben a kimutatástáblában?

Vegye figyelembe, hogy a C5 cellában végzett számítások implicit módon szűrve csak a borostyánsárga eladásokat jelenítik meg. Létre kell hoznia egy DAX-képletet, amely megszünteti az adatok szűrését. Ez a képlet felülírja a borostyánsárgával egyező rekordokat megjelenítő szűrőt, és a nem borostyánsárgával kapcsolatos rekordokat. A DAX CLCULATE funkció eltávolíthatja a telepített szűrőket, és más szűrőket alkalmazhat. Ez a funkció hasonló a SUMIFS funkcióhoz, de több lehetőséget biztosít a felhasználóknak.

SZÁMÍTÁS függvény a teljes oszlop kiszámítására szolgál egy vagy több szűrő alapján. Ha a terepre Eladóállítson be egy szűrőt, lecseréli az adott mezőre korábban beállított szűrőt. Ha létrehoz egy szűrőt Eladó=Hughes, a DAX automatikusan figyelmen kívül hagyja az implicit szűrőt Eladó=Borostyán, amely megjeleníti az összes olyan rekordot, amely megfelel Hughesnek. Ha nehezen tanulja meg azonnal a DAX-képletek létrehozását, próbáljon ki egy lépésről-lépésre leírt módszert. Többszintű képletek helyett hozzon létre egy kisebb képletkészletet. A képlet, amely Hughes értékesítési volumenét számolja (30. ábra):

CALCULATE([Eladási oszlop összesen];Sls[Salesperson]= " Hughes ")

Miután definiált egy számított mezőt, használhatja más számított mezőkben is.

Állítsa be a következő számított mezőket:

Most a soron kell alkalmazni a szűrőt az oszlopra Eladó hogy elrejtse Hughes részesedését a kimutatásból. Ennek eredményeként megkapjuk a június 2-i eladások összegét, 911 dollárt (az érték a H6 cellában, 31. ábra), miközben Hughes részesedése megoszlik a többi eladó között. Hasonlítsa össze az egyes eladók értékeit és a napi végösszegeket az ábra táblázataiban. 30 és 31. Hasonló eredményt nem lehet elérni egy szabályos pivot táblában létrehozott számított mezők használatával.

GYAKORLAT: Ártartomány kiszámítása az árukhoz

A normál kimutatástáblázatban meghatározott számított mezők kiszámítása mindig az eredeti adatkészlet soraihoz viszonyítva történik. A MrExcel oldal egyik előfizetője a képlet segítségével próbálta kiszámolni az áruk ártartományát MAX (ár) – MIN (ár), normál pivot táblába állítva. Amikor kiszámított mezőt hoz létre egy kimutatásban, az Excel megvizsgálja a forrásadatok minden sorát, és kiszámítja az adott sorban a maximális értéket (32. ábra). Mivel minden sorban csak egy árérték jelenik meg, a forrásadatok minden sorában a maximális érték egyetlen árértéknek felel meg. Ugyanez mondható el a minimális értékről is. Más szavakkal, MAX - MIN = Ár - Ár, azaz 0. Ennek eredményeként a pivot táblában megjelenített minden terméknél a legmagasabb és legalacsonyabb ár közötti különbség nulla lesz. Ezt az abszolút hibás eredményt a közönséges pivot táblák számított mezőiben rejlő szabályok miatt kaptuk (33. ábra).

Használja ki a DAX erejét ennek a feladatnak a végrehajtásához.

1. Jelölje ki az eredeti adatkészletet. Nyomja meg a Ctrl+T billentyűkombinációt a kiválasztott adatkészlet táblázattá alakításához.
2. Kontextusfüggő lapkészlet használata Munka asztalokkal (tervező) adjon nevet az új táblának Értékesítés.
3. Hajtsa végre a parancsot Beszúrás — Pivot táblaés jelölje be a négyzetet Adja hozzá ezeket az adatokat az adatmodellhez.
4. Adjon hozzá egy mezőt Termék a STRINGS területre.
5. Adjon hozzá mezőt kétszer Ár az ÉRTÉKEK területre.
6. A PivotTable mezőlista alján bontsa ki az első Ár mező listáját, és válassza ki Értékmező beállításai. Módosítsa a végső funkciót erre Minimális.
7. Hasonlóan a második mezőhöz Ár válassza ki a végső funkciót Maximális.
8. A bekezdések végrehajtásakor. A 6. és 7. ábra valójában egy implicit módon meghatározott számított mezőt hoz létre. Ezeket a területeket a következő részben tárgyaljuk részletesen.
9. Hajtsa végre a parancsot powerpivot — Számított mezők — Hozzon létre egy számított mezőt számított mező létrehozásához. Adjon nevet ennek a mezőnek Hatótávolság. A képlet beviteli mezőbe írja be a táblázat nevét Értékesítés. A mezők listájában keresse meg az Értékesítés mezőt [Maximum az Ár oszlopban], és nyomja meg a gombot<Таb>. Írjon be egy mínusz jelet. Lépjen be újra az értékesítésbe. Keresse meg az Értékesítés mezőt [Minimum az Ár oszlopban], és nyomja meg a gombot<Таb>hogy beillesszük a képletbe. A Tartomány képlet mostantól =[Magas az Ár oszlopban]-[Alacsony az Ár oszlopban].
10. A képlet adatmodellhez való hozzáadásához kattintson az OK gombra. Miután visszatért az Excel ablakba, keresse meg az új mezőt Hatótávolság az asztalban Értékesítés. Kattintson erre a mezőre. Most az ártartomány helyesen van kiszámítva (34. ábra).

Számított DAX-mezők létrehozása mezők hozzáadásával az ÉRTÉKEK területhez. Egy mező hozzáadása után Ár az ÉRTÉKEK területre és a funkcióválasztásra Minimális A PowerPivot automatikusan létrehoz egy számított DAX-mezőt, amely megjeleníti az eredményt. Így nincs szükség a =MINX('Sales';'Sales"[Price]) képlet használatára. Az implicit számított mezőket a PowerPivot ablakban tekintheti meg. Futtassa a parancsot powerpivot — Ellenőrzésés a lapon Továbbá kattintson a gombra Implicit számított mezők megjelenítése. Az új mezők az ablak alján megjelenő sorokban jelennek meg (35. ábra).

Ha az egérmutatót az ablak aljára viszi, megjelenik a következő üzenet: „A PowerPivot automatikusan generálta ezt a számított mezőt azáltal, hogy hozzáadta a mezőt az Excel mezőlistájának Értékek területéhez. A mező csak olvasható, és az oszlop törlésekor automatikusan törlődik."

DAX-képlet létrehozásakor egyszerűbb ideiglenesen hozzáadni egy mezőt az értékterülethez implicit mező létrehozásához. Ez a mező más DAX-képletek létrehozásakor használható. Miután létrehozott egy új képletet egy kimutatástáblában, eltávolíthatja belőle az implicit mezőt. Ezek a mezők a PowerPivot ablakban maradnak, mert az új DAX-mértékek hivatkoznak rájuk.

Az ártartomány kiszámításához létrehozhatja a következő DAX-képletet:

Max('Eladás'; 'Eladás'[ár])-Minx('Eladás'; 'Eladás'[ár])

Időgazdálkodási funkciók használata

A DAX egy sor időgazdálkodási funkcióval rendelkezik. E függvények használatához egy speciális táblázatra van szükség, amely tartalmazza a dátumokat (naptártáblázat). Bármely táblázat tartalmazhat dátummezőt. Az eredeti adatkészletben megjelenített dátum egyetlen sorban jelenik meg a dátumtáblázatban. Ha további oszlopokra van szüksége, amelyek egy dátumot egy adott évhez, hónaphoz vagy a hét napjához tartoznak, adja hozzá ezeket az oszlopokat a naptártáblázathoz. Az ilyen táblázatok létrehozásának számos módja van, de az Excel-felhasználók általában az Excel környezetben hozzák létre őket:

1. Navigáljon az eredeti adatkészlethez. Válassza ki a dátum mezőt tartalmazó oszlopot, és másolja ki.
2. Menjen egy üres lapra, és írja be a dátumokat az A oszlopba.
3. Az A oszlopot kiválasztva futtassa a parancsot Adat — Az ismétlődések eltávolításaés kattintson a gombra rendben.
4. Ha szükséges, adjon hozzá további oszlopokat, például Év, Hétnap száma, Hétnapja, Hónap száma és Hónap (36. ábra). Az ábra tetején látható képletek az egyes oszlopok kiszámításának módját mutatják.
5. Konvertálja az adattartományt táblázattá a Ctrl+T lenyomásával.
6. A kontextus fül használata Konstruktőr, utalva a környezetfüggő lapok készletére Asztalokkal való munka, nevezze el azt a táblát, amelyet naptárnak készít.

Ettől a résztől a jegyzet végéig az Excel-fájl adatai kerülnek felhasználásra. Példa naptár.xlsx.

Adatok hozzáadása PowerPivot táblához és formázás végrehajtása. A fejezet utolsó példájában több további lépést is megtesznek a zárójelentés javítása érdekében. Kezdjük táblázatok hozzáadásával ÉrtékesítésÉs Naptár a PowerPivotban:

1. Jelöljön ki egy cellát a táblázatban Értékesítés.
2. A lapon powerpivot Excelben kattintson az ikonra Hozzáadás az adatmodellhez.
3. Jelöljön ki egy cellát a táblázatban Naptár.
4. Hozzáadás az adatmodellhez.
5. Az Excel PowerPivot lapján kattintson a ikonra Ellenőrzés; menj az ablakhoz powerpivot.

Most formázzuk meg ezt a két táblázatot az ablakban powerpivot:

1. asztal Értékesítés oszlop kiválasztása dátum.
2. Az ablakban powerpivot válasszon lapot Az elejére. Ügyeljen a csoportra Formázás. A megfelelő formátum már alapértelmezés szerint ki van választva dátum, de maga a dátum formázása hibás. Bontsa ki a listát Formátumés válassza a *14.03.2001. (Ez nem hiba, így néz ki a legördülő lista eleme.)
3. Válasszon oszlopot Értékesítés az asztalban Értékesítés.
4. A lapon Az elejére formátum kiválasztása Valuta. A tizedesjegyek törléséhez kattintson duplán az ikonra Csökkentse a tizedesjegyek számát.
5. Válassza ki újra az oszlopot dátum az asztalban Értékesítés.
6. A lapon Konstruktőr kattintson az ikonra Hozzon létre kapcsolatot.
7. A párbeszédpanelen Hozzon létre kapcsolatot az első két mező értékekkel van kitöltve ÉrtékesítésÉs dátum. Bontsa ki a listát Kapcsolódó keresési táblázatés válassza ki az elemet Naptár. Csepp Kapcsolódó keresőoszlopérték automatikusan megjelenik dátum(37. ábra). Kattintson rendben kapcsolat létrehozásához.
8. Válassza ki a munkalapot Naptár az ablakban powerpivot.
9. Jelöljön ki egy oszlopot dátum.
10. Menjen a lapra Az elejéreés módosítsa a dátumformátumot *3/14/2001-re.
11. Egy oszlop kiemelése dátum, lépjen a lapra Konstruktőr. Bontsa ki a listát Megjelölés dátumtáblázatkéntés válassza ki az elemet Megjelölés dátumtáblázatként. Egy párbeszédpanel jelenik meg a képernyőn. Jelölje meg adatlapként. A megfelelő mező már ki van választva ebben az ablakban dátum. Kattintson a gombra rendben. Ezt az akciót dátum szerinti szűrők létrehozásához szükséges a pivot tábla mezőinek listájában. Az eredmény az ábrán látható. 39.

Rizs. 37. Táblák közötti kapcsolat létrehozása ÉrtékesítésÉs Naptár

A PowerPivot nem támogatja az automatikus rendezést az egyéni listákon. Nagy rajongója vagyok powerpivot 2009 óta írok könyveket kapcsolódó témákban. És az első könyvben powerpivot Ezt többször említettem powerpivot A hónapnevek szerinti automatikus rendezés nem történik ("január, február, március…" sorrendben). A PowerPivot kimutatásokban a hónapok nevei ábécé sorrendben vannak rendezve (április, augusztus, július…), és ez a probléma még a PowerPivot for Excel 2013 alkalmazásban is fennáll.

A normál gyorsítótárat használó normál kimutatások automatikusan egyéni listák alapján rendezik az összes mezőt. Sajnos ez a rendezés nem érhető el a PowerPivot kimutatásokhoz. Ennek leküzdéséhez:

1. Az ablakban powerpivot táblázat kiválasztása Naptár.
2. Jelölje ki az egyik cellát egy oszlopban A hét napja.
3. A lapon Az elejére az ablakban powerpivot csoportban Rendezés és szűrés kattintson az ikonra Rendezés oszlopok szerint.
4. A párbeszédpanelen Rendezés oszlop szerint válassza ki, hogy a hét napját a hét napjának száma szerint rendezze (38. ábra). Kattintson rendben.
5. Válasszon ki egy cellát egy oszlopban Hónap. Ismételje meg a bekezdéseket. 3 és 4, de ezúttal válassza ki a rendezés oszlopot Hónapról hónapra szám.

Hozzon létre egy pivot táblát speciális funkciókkal. Az ablakban powerpivot táblázat kiválasztása Értékesítés. A lapon Az elejére nyissa meg a listát Pivot táblaés válassza ki az elemet Pivot tábla. Visszatér az Excelbe, és a kimutatás mezőinek listája megjeleníti a táblázat mezőit Naptár és táblázatok Értékesítés. Vigye az egérmutatót a mező fölé dátumés bővítse ki a listát. Válasszon ki egy elemet Dátumszűrők, amely lehetővé teszi, hogy dátum szerint elérje az összes olyan szűrőt, amelyet a szokásos pivot táblákban használnak (39. ábra).

Rizs. 39. Mivel a Naptár táblázat dátumtáblázatként lett deklarálva, lehetővé vált az összes szűrő dátum szerinti elérése

Kövesse az alábbi lépéseket mezők hozzáadásához a kimutatástáblázathoz:

1. Drag box A hét napja az asztaltól Naptár a COLUMNS területre.
2. Drag box Értékesítés az asztaltól Értékesítés az ÉRTÉKEK területre.
3. Válassza ki a környezetfüggő lapot Elemzésés kattintson az ikonra Paste Slice.
4. Adjon hozzá egy mezőszeletelőt A hét napja.
5. A kontextus lapon Lehetőségek környezetfüggő lapok készlete Vágó eszközök válassza ki az opciót oszlopok 7. érték.
6. A szeletelőn csak munkanapokat válasszon.

Az imént végrehajtott műveletek eredménye az ábrán látható. 40. Mivel a hét napjai számok szerint vannak rendezve, a hét napjai a szelet ablakban a megfelelő sorrendben jelennek meg.

Ha látsz egy ablakot powerpivot most először nem valószínű, hogy lenyűgözik a képességei. Ha dolgozott együtt powerpivot V előző verziók Excel, értékelheti a hét napjainak helyes rendezését.

"Intelligens" időfunkciók. Az előmunkálatok elvégzése után itt az ideje, hogy megbirkózzunk az „okos” időfunkciókkal. Kezdje az 1. ábrán látható pivot tábla átszervezésével. 40:

1. A kontextus lapon Elemzés futtassa a parancsot Egyértelmű — Mindent kitöröl.
2. Nyissa ki az asztalt Naptárés húzza a mezőt dátum a STRINGS területre.
3. Nyissa ki az asztalt Értékesítésés húzza a mezőt Értékesítés az ÉRTÉKEK területre.
4. Adjon hozzá egy mezőszeletelőt Év.
5. A szelet ablakban Év válassza ki a 2015-öt.

Az eredmény egy pivot tábla lesz, amely az ábrán látható. 41. Az eladások 2015. június 2-án 937 USD voltak.

Most DAX-számítású mezőket hozunk létre, amelyek összehasonlítják az aktuális napi eladásokat az előző év ugyanazon a napon történt értékesítéseivel. Korábban már említettük, hogy ez a feladat nem ilyen egyszerű, hiszen a C9-es cellára szűrőt alkalmaztak, amely 2015.06.02. dátumú rekordokat jelenít meg. Ezért el kell távolítania a szűrőt a dátum mezőben, majd alkalmaznia kell egy új szűrőt, amely lehetővé teszi az elmúlt év rekordjainak megjelenítését.

A dátummezőre alkalmazott szűrő eltávolításához használja a SZÁMÍTÁS funkciót. De mi van, ha olyan rekordokat szeretne megjeleníteni, amelyek megfelelnek az előző évnek? Használja ki a DAX által támogatott több mint 30 „okos” időfunkció egyikét, nevezetesen a DATEADD funkciót.

Pontosan egy évvel ezelőtti nap kiválasztásához használja a DATEADD('Calendar"[Date];–1;year) képletet. A harmadik argumentum lehet nap, hónap vagy év. Vegye figyelembe, hogy ezek az argumentumok felsorolva vannak (azaz . az Excel programozása során globális változókként vannak definiálva, amelyek a program végrehajtása során numerikus kóddá alakulnak), így nem idézőjelek, mint szöveges argumentumok.

A DATEADD függvényt számos képletben használják, amelyek időszakokat számítanak ki. Például a három hónappal ezelőtti napi eladások megtekintéséhez használja a =DATEADD('Naptár"[Dátum];–3;hónap) képletet. Ne feledje, hogy a DATEADD függvény csak egy a 34 "okos » időfüggvény közül. Például ebben a kategóriában egy másik függvényt használ a DATESMTD('Naptár'[Dátum]) képlet, amely megjeleníti az összes dátumot a hónap aktuális napjáig.

Tegyük fel, hogy az adatmodellben a dátum oszlop egy táblázatban található Értékesítésés a táblázatban Naptár. Az „intelligens” időfüggvények mindig megfelelően működnek, ha a „Naptár” [Dátum] mezőre hivatkozunk. És csak az esetek 10%-ában működnek, ha az „Eladások” [Dátum] mezőre hivatkozunk. Ha egy összetett problémán szeretne törni a fejét, próbáljon meg létrehozni egy DAX-képletet az „Eladások”[Dátum] mezőre való hivatkozással. A szintaxis ellenőrzése után a képernyőn egy vidám üzenet jelenik meg, amely szerint nincs hiba, és mégis A PivotTable hibás eredményeket jelenít meg. Nem tudom megállapítani a képlet furcsa viselkedésének okát, de ha a képletet alkalmazzuk a 'Naptár'[Dátum] mezőben, akkor a megfelelő eredményeket kapjuk.

Így az Ön rendelkezésére áll a DATEADD funkció, amely lehetővé teszi egy olyan dátum azonosítását, amely pontosan egy évre van a pivot tábla sorában jelzett dátumtól. Az adott dátumra vonatkozó eladások megjelenítéséhez használja a CALCULATE funkciót, amely felülírja a meglévő implicit szűrőket. Mivel a kimutatás 9. sora implicit szűrőt használ a 2015. június 2-nak megfelelő rekordok megjelenítéséhez, használja a következő képletet, amely tartalmazza a CALCULATE funkciót:

CALCULATE([Eladási oszlop összesen];DATEADD('Naptár"[Dátum];-1;év))

Csináld a következőt:

1. powerpivotés futtassa a parancsot Számított mezők — Hozzon létre egy számított mezőt. Megnyílik egy ablak Számított mező(42. ábra).
2. Adjon nevet a mezőnek SalesOverLastYear.
3. Adja meg a képletet
   =CALCULATE([Eladási oszlop összesen];DATEADD('Naptár"[Dátum];-1;év))
4. Csoportban Kategória válassza a Pénznem lehetőséget. Telepítés Tizedes jel 0. Válassza ki Szimbólum -$ angol (USA).
5. Kattintson a gombra Ellenőrizze a Képletet hogy megbizonyosodjon a képlet helyességéről. Ha a képletellenőrzés eredménye nem látható a képernyőn megjelenő eszköztipp miatt, kattintson a mezőbe Leírás. Kattintson rendben a számított mező létrehozásának befejezéséhez.
6. Visszatér az Excel ablakba. Kattintson ide PivotTable mezőlista az asztalban Naptár a mezőn SalesOverLastYear. A mező hozzáadódik az ÉRTÉKEK területhez.

Az elemzés befejezéséhez adjon hozzá egy második számított mezőt, amelyet a százalékos trend kiszámításához fog használni. Ehhez kövesse az alábbi lépéseket:

1. Az Excel ablakban válassza ki a lapot powerpivotés futtassa a parancsot Számított mezőket — Hozzon létre egy számított mezőt.
2. Adjon nevet a mezőnek Százalékos változás.
3. Adja meg az =[Eladási oszlop összege]/[Tavalyi értékesítés]–1 képletet.
4. Csoportban Kategória válassza a Szám lehetőséget. Telepítés Tizedes jel 1. Válassza ki Formátum – százalék.
5. Ismételje meg a bekezdéseket. Az előző utasítás 5. és 6. pontja.

Az eredményül kapott pivot táblázat az ábrán látható. 43.

Most már eltávolíthatja a mezőket a kimutatástáblából ÉrtékesítésÉs SalesOverLastYear, elhagyja a pályát Százalékos változásés adja meg az eladó nevét (mező Kereskedő) az OSZLOP területre (44. ábra). A pivot tábla minden cellája az eladások dinamikáját mutatja az előző évhez képest (furcsa, de nem láttam napi dinamikát elemezni).

Rizs. 44. Pivot tábla számított mezővel Százalékos változás

ábrán látható táblázat eléréséhez. 44, tegye a következőket:

1. A G9 cella (és néhány más) hibaüzenetet jelenít meg, amelyet az okoz, hogy az előző évben nem volt eladás (ezért elosztva 0-val). Kattintson a jobb gombbal a kimutatás bármely cellájára, és válassza ki PivotTable beállításai(45. ábra). Menjen a lapra Elrendezés és formátumés jelölje be a négyzetet A hibakijelzéshezés a megfelelő mezőbe írja be a - - karaktereket.
2. Emelje ki a számértékeket a pivot táblázatban (C9:H38 tartomány). Menjen a lapra itthon, és futtassa a parancsot Feltételes formázás — Ikonkészletekés válasszunk ki egy két háromszögből és egy téglalapból álló halmazt. Ha szeretné, módosítsa az Excel által beállított alapértelmezett formázási szabályokat.

A DAX-szal kapcsolatos további információkért keresse fel Rob Colley blogját: http://www.powerpivotpro.com/ (Megvettem Colley könyvét, úgyhogy nagyon várom a jó olvasást).

Kulcsfontosságú teljesítménymutatók használata

Kulcsteljesítménymutatók (KPI-k) hozzáadva a jelenlegi verzióhoz powerpivot. Jelenleg hibásan működnek, mert nincsenek ideálisan megvalósítva. KPI használatához létre kell hoznia legalább egy számított DAX mezőt, amelyet alapmezőként használ. Opcionálisan létrehozhat egy második DAX számított mezőt is, amely célértékként szolgál.

KPI beállítása az abszolút célértékhez. KPI-k beállítása és megjelenítése kimutatástáblában:

1. A lapon powerpivot válassz egy csapatot Kulcsfontosságú teljesítménymutatók − Hozzon létre egy KPI-t. Egy párbeszédpanel jelenik meg a képernyőn. Kulcsfontosságú teljesítménymutató.
2. Bontsa ki a listát KPI alapmező (érték)és válasszon egyet a számított DAX mértékek közül.
3. A célérték beállításához állítsa be a rádiógombot Abszolút értékés adjon meg egy értéket.
4. Válassza ki a négy állapotküszöb stílus egyikét (46. ábra 1. területe).
5. Válasszon egy ikonstílust (2. terület a 46. ábrán).
6. Vegye figyelembe, hogy a szövegmezők nem húzhatók, bár első pillantásra úgy tűnik, hogy ez a művelet lehetséges. Írjon be új értékeket a küszöbértékek feletti szövegmezőkbe. Új érték megadása és a Tab billentyű lenyomása után a szövegmező a kívánt helyre kerül.
7. Kattintson az elemre Leírások a párbeszédpanel alján található a 10. lépésben megjelenő mezőnevek módosításához.
8. Kattintson rendben.
9. Nyissa meg a Naptár táblázatot a kimutatás mezőlistájában. a mező közelében Százalékos változás egy közlekedési lámpa ikon jelenik meg.
10. Kattintson a pluszjelre három mező megjelenítéséhez: Jelentése, CélÉs Állapot. Drag box Állapot az ÉRTÉKEK területre. A PivotTable egy ikont jelenít meg. Mostantól függetlenül a párbeszédpanelen kiválasztott ikontól a PivotTable három kerek ikont jelenít meg: piros, sárga és zöld (47. ábra).

A jegyzet Jelen, Alexander könyve alapján készült. . 10. fejezet