Excel life hackek a jelentéskészítésben és adatfeldolgozásban részt vevők számára

2024 Szerző: Malcolm Clapton | [email protected]. Utoljára módosítva: 2024-01-13 01:50

Ebben a bejegyzésben Renat Shagabutdinov, a Mann, Ivanov és Ferber Kiadó vezérigazgató-helyettese néhány remek Excel-életrajzot oszt meg. Ezek a tippek hasznosak lesznek mindazok számára, akik különféle jelentéskészítésben, adatfeldolgozásban és prezentációkészítésben vesznek részt.

Ez a cikk egyszerű technikákat tartalmaz az Excelben végzett munka egyszerűsítésére. Kifejezetten hasznosak azok számára, akik vezetői jelentéskészítéssel foglalkoznak, az 1C-ből letöltött és egyéb jelentések alapján különféle elemző jelentéseket készítenek, belőlük prezentációkat és diagramokat készítenek a menedzsment számára. Nem állítom, hogy abszolút újdonság lennék - ilyen vagy olyan formában ezek a technikák valószínűleg szóba kerültek a fórumokon, vagy megemlítették cikkekben.

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

A VLOOKUP és a HLOOKUP funkciók csak akkor működnek, ha a kívánt értékek annak a táblázatnak az első oszlopában vagy sorában vannak, amelyből adatokat szeretne beszerezni.

Ellenkező esetben két lehetőség van:

1. Használja a LOOKUP funkciót.

   A következő szintaxissal rendelkezik: LOOKUP (keresési_érték; keresési_vektor; eredményvektor). De ahhoz, hogy megfelelően működjön, a view_vector tartomány értékeit növekvő sorrendben kell rendezni:

   

2. Használja a MATCH és INDEX függvények kombinációját.

   A MATCH függvény a tömb egy elemének sorszámát adja vissza (segítségével megtudhatja, hogy a tábla melyik sorában van a keresett elem), az INDEX függvény pedig egy adott számú tömbelemet (amit megtudunk a MATCH funkció használatával).

   

   Függvény szintaxis:

   • SEARCH (search_value; search_array; match_type) - esetünkben szükségünk van egy "pontos egyezés" egyező típusra, ez a 0 számnak felel meg.

   • INDEX (tömb; sorszám; [oszlopszám]). Ebben az esetben nem kell megadni az oszlop számát, mivel a tömb egy sorból áll.

Hogyan lehet gyorsan kitölteni üres cellákat egy listában

A feladat az oszlop celláinak kitöltése a tetején lévő értékekkel (úgy, hogy a téma a táblázat minden sorában szerepeljen, és ne csak a témával foglalkozó könyvblokk első sorában):

Válassza ki a "Tárgy" oszlopot, kattintson a szalagra a "Kezdőlap" csoportban, kattintson a "Keresés és kijelölés" gombra → "Cellacsoport kiválasztása" → "Üres cellák" és kezdje el beírni a képletet (vagyis tegyen egyenlőt jel), és hivatkozzon a felül lévő cellára, egyszerűen kattintson a felfelé mutató nyílra a billentyűzeten. Ezután nyomja meg a Ctrl + Enter billentyűket. Ezt követően a kapott adatokat elmentheti értékként, mivel a képletekre már nincs szükség:

Hogyan keressünk hibákat egy képletben

Egy képlet külön részének kiszámítása

Egy összetett képlet megértéséhez (amelyben más függvények függvényargumentumokként szerepelnek, azaz egyes függvények másokba vannak beágyazva), vagy a hibák forrásának megtalálásához gyakran ki kell számítanunk egy részét. Két egyszerű módja van:

1. Ha egy képlet egy részét közvetlenül a képletsorban szeretné kiszámítani, jelölje ki azt a részt, és nyomja meg az F9 billentyűt:

   

   Ebben a példában a SEARCH függvénnyel volt probléma – az argumentumok felcserélődtek benne. Fontos megjegyezni, hogy ha nem szakítja meg a függvény részének kiszámítását és nyomja meg az Enter billentyűt, akkor a számított rész szám marad.

2. Kattintson a Képlet kiszámítása gombra a szalag Képletek csoportjában:

   

   A megjelenő ablakban lépésről lépésre kiszámíthatja a képletet, és meghatározhatja, hogy melyik szakaszban és melyik függvényben fordul elő hiba (ha van):

   

Hogyan határozható meg, hogy egy képlet mitől függ vagy mire utal

Ha meg szeretné határozni, hogy a képlet mely celláktól függ, a szalag Képletek csoportjában kattintson a Cellákat befolyásoló gombra:

A nyilak jelzik, hogy mitől függ a számítás eredménye.

Ha a képen pirossal kiemelt szimbólum jelenik meg, akkor a képlet a többi lapon vagy más könyvben lévő celláktól függ:

Rákattintva láthatjuk, hogy pontosan hol találhatók a befolyásoló cellák vagy tartományok:

A "Cellák befolyásolása" gomb mellett található a "Függő cellák" gomb, amely ugyanúgy működik: az aktív cellából egy képlettel jeleníti meg a nyilakat a tőle függő cellák felé.

Az ugyanabban a blokkban található "Nyilak eltávolítása" gombbal egyszerre távolíthat el nyilakat a befolyásoló cellákhoz, nyilakat a függő cellákhoz, vagy mindkét típusú nyilat egyszerre:

Hogyan találjuk meg a cellaértékek összegét (számát, átlagát) több lapról

Tegyük fel, hogy több azonos típusú lapja van adatokkal, amelyeket hozzá szeretne adni, számolni vagy más módon feldolgozni:

Ehhez abban a cellában, amelyben látni szeretné az eredményt, írjon be egy szabványos képletet, például SZUM (SZUM), és adja meg az első és az utolsó munkalap nevét azon lapok listájából, amelyeket fel kell dolgoznia. az argumentum kettősponttal elválasztva:

A "Data1", "Data2", "Data3" lapokról kapja meg a B3 címmel rendelkező cellák összegét:

Ez a címzés az elhelyezett lapoknál működik következetesen … A szintaxis a következő: = FUNCTION (first_list: last_list! Tartomány hivatkozás).

Sablonkifejezések automatikus létrehozása

Az Excelben a szöveggel való munka alapelvei és néhány egyszerű funkció segítségével sablonkifejezéseket készíthet jelentésekhez. A szöveggel való munka néhány alapelve:

• A szöveget a & jel segítségével fűzzük össze (helyettesítheti a CONCATENATE függvénnyel, de ennek nincs sok értelme).
• A szöveget mindig idézőjelbe írjuk, a szöveges cellákra való hivatkozásokat mindig anélkül.
• Az "idézőjel" szolgáltatáskarakter megszerzéséhez használja a CHAR függvényt 32-es argumentummal.

Példa sablonkifejezés létrehozására képletekkel:

Eredmény:

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

SZÖVEG (érték; formátum)

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

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

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

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

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

A cellák egyesítéséhez és az adatok megőrzéséhez mindegyikben (talán van egy képlete, mint ebben az absztrakt példában; lehet, hogy egyesíteni szeretné a cellákat, de az összes adatot megőrzi vagy szándékosan elrejti), egyesítse a munkalap bármely celláját., jelölje ki őket, majd a Format Painter paranccsal vigye át a formázást az egyesítendő cellákba:

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

Ha egyszerre több adatforrásból kell pivotot felépíteni, akkor a "PivotTable and Chart Wizard"-ot hozzá kell adni a szalaghoz vagy a gyorselérési panelhez, amelyen van ilyen lehetőség.

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

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

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

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

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

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

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

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

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

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

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

Egy bizonyos szöveges karakterlánc előfordulásának megtalálásához törölnie kell az összes előfordulását az eredetiben, és össze kell hasonlítania a kapott karakterlánc hosszát az eredetivel:

DLSTR ("Tariff MTS Super MTS") - DLSTR ("Szuper tarifa") = 6

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

6 / DLSTR („MTS”) = 2

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

Ezt az algoritmust a képletek nyelvén kell megírni (jelöljük "szöveggel" azt a szöveget, amelyben előfordulásokat keresünk, és "keresett" -vel azt, amelynek előfordulásaira kíváncsiak vagyunk):

= (DLSTR (szöveg) -LSTR (SUBSTITUTE (FELSŐ (szöveg); UPPER (keresés), ""))) / DLSTR (keresés)

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

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