Excel life hacks pre tých, ktorí sa podieľajú na reportovaní a spracovaní údajov

2024 Autor: Malcolm Clapton | [email protected]. Naposledy zmenené: 2024-01-13 02:52

V tomto príspevku Renat Shagabutdinov, zástupkyňa generálneho riaditeľa vydavateľstva Mann, Ivanov a Ferber, zdieľa niekoľko skvelých životných hackov Excelu. Tieto tipy budú užitočné pre každého, kto sa zaoberá rôznymi reportingmi, spracovaním údajov a tvorbou prezentácií.

Tento článok obsahuje jednoduché techniky na zjednodušenie práce v Exceli. Sú užitočné najmä pre tých, ktorí sa zaoberajú správou manažmentu, pripravujú rôzne analytické správy na základe stiahnutí z 1C a iných správ, vytvárajú z nich prezentácie a diagramy pre manažment. Nepredstieram, že som absolútna novinka – v tej či onej forme sa o týchto technikách pravdepodobne diskutovalo na fórach alebo sa spomínalo v článkoch.

Jednoduché alternatívy k VLOOKUP a HLOOKUP, ak požadované hodnoty nie sú v prvom stĺpci tabuľky: VYHĽADAŤ, INDEX + HĽADAŤ

Funkcie VLOOKUP a HLOOKUP fungujú iba vtedy, ak sú požadované hodnoty v prvom stĺpci alebo riadku tabuľky, z ktorej plánujete získať údaje.

V opačnom prípade sú dve možnosti:

1. Použite funkciu LOOKUP.

   Má nasledujúcu syntax: LOOKUP (hodnota_vyhľadania; vektor_vyhľadania; vektor_výsledku). Ale aby to fungovalo správne, hodnoty rozsahu view_vector musia byť zoradené vo vzostupnom poradí:

   

2. Použite kombináciu funkcií MATCH a INDEX.

   Funkcia MATCH vráti poradové číslo prvku v poli (s jej pomocou zistíte, v ktorom riadku tabuľky sa hľadaný prvok nachádza) a funkcia INDEX vráti prvok poľa s daným číslom (ktoré zistíme pomocou funkcie MATCH).

   

   Syntax funkcie:

   • HĽADAŤ (hodnota_hľadania; pole_hľadania; typ_zhody) – pre náš prípad potrebujeme typ zhody „presná zhoda“, zodpovedá číslu 0.

   • INDEX (pole; číslo_riadku; [číslo_stĺpca]). V tomto prípade nemusíte zadávať číslo stĺpca, pretože pole pozostáva z jedného riadka.

Ako rýchlo vyplniť prázdne bunky v zozname

Úlohou je vyplniť bunky v stĺpci hodnotami v hornej časti (tak, aby téma bola v každom riadku tabuľky, a nie iba v prvom riadku bloku kníh na danú tému):

Vyberte stĺpec „Predmet“, kliknite na pásku v skupine „Domov“, tlačidlo „Nájsť a vybrať“→ „Vyberte skupinu buniek“→ „Prázdne bunky“a začnite zadávať vzorec (to znamená, že znamienko) a prejdite na bunku v hornej časti jednoducho kliknutím na šípku nahor na klávesnici. Potom stlačte Ctrl + Enter. Potom môžete prijaté údaje uložiť ako hodnoty, pretože vzorce už nie sú potrebné:

Ako nájsť chyby vo vzorci

Výpočet samostatnej časti vzorca

Aby ste pochopili zložitý vzorec (v ktorom sa ako argumenty funkcie používajú iné funkcie, to znamená, že niektoré funkcie sú vnorené do iných) alebo aby ste v ňom našli zdroj chýb, musíte často vypočítať jeho časť. Existujú dva jednoduché spôsoby:

1. Ak chcete vypočítať časť vzorca priamo na paneli vzorcov, vyberte túto časť a stlačte F9:

   

   V tomto príklade sa vyskytol problém s funkciou HĽADAŤ – boli v nej prehodené argumenty. Je dôležité si zapamätať, že ak nezrušíte výpočet časti funkcie a stlačíte Enter, tak vypočítaná časť zostane číslom.

2. Kliknite na tlačidlo Vypočítať vzorec v skupine Vzorce na páse s nástrojmi:

   

   V okne, ktoré sa zobrazí, môžete krok za krokom vypočítať vzorec a určiť, v ktorej fáze a v ktorej funkcii sa vyskytne chyba (ak existuje):

   

Ako určiť, od čoho vzorec závisí alebo na čo odkazuje

Ak chcete určiť, od ktorých buniek vzorec závisí, v skupine Vzorce na páse s nástrojmi kliknite na tlačidlo Ovplyvnenie buniek:

Zobrazia sa šípky, ktoré označujú, od čoho závisí výsledok výpočtu.

Ak sa zobrazí symbol zvýraznený na obrázku červenou farbou, vzorec závisí od buniek na iných hárkoch alebo v iných knihách:

Kliknutím naň môžeme presne vidieť, kde sa nachádzajú ovplyvňujúce bunky alebo rozsahy:

Vedľa tlačidla "Ovplyvnenie buniek" je tlačidlo "Závislé bunky", ktoré funguje rovnakým spôsobom: zobrazuje šípky z aktívnej bunky so vzorcom k bunkám, ktoré sú od nej závislé.

Tlačidlo „Odstrániť šípky“, ktoré sa nachádza v tom istom bloku, vám umožňuje odstrániť šípky do ovplyvňujúcich buniek, šípky do závislých buniek alebo oba typy šípok naraz:

Ako nájsť súčet (počet, priemer) hodnôt buniek z viacerých listov

Povedzme, že máte niekoľko hárkov rovnakého typu s údajmi, ktoré chcete pridať, spočítať alebo spracovať iným spôsobom:

Ak to chcete urobiť, do bunky, v ktorej chcete vidieť výsledok, zadajte štandardný vzorec, napríklad SUM (SUM), a zadajte názov prvého a posledného hárka zo zoznamu hárkov, ktoré potrebujete spracovať. argument oddelený dvojbodkou:

Dostanete súčet buniek s adresou B3 z listov "Data1", "Data2", "Data3":

Toto adresovanie funguje pre umiestnené listy dôsledne … Syntax je nasledovná: = FUNCTION (prvý_zoznam: posledný_zoznam! Odkaz na rozsah).

Ako automaticky vytvárať šablónové frázy

Pomocou základných princípov práce s textom v Exceli a niekoľkých jednoduchých funkcií si môžete pripraviť šablónové frázy pre zostavy. Niekoľko zásad práce s textom:

• Text zreťazíme pomocou znaku & (môžete ho nahradiť funkciou CONCATENATE, ale to nedáva veľký zmysel).
• Text je vždy písaný v úvodzovkách, odkazy na bunky s textom sú vždy bez.
• Ak chcete získať servisný znak „úvodzovky“, použite funkciu CHAR s argumentom 32.

Príklad vytvorenia frázy šablóny pomocou vzorcov:

výsledok:

V tomto prípade sa okrem funkcie CHAR (na zobrazenie úvodzoviek) používa funkcia IF, ktorá umožňuje meniť text v závislosti od pozitívneho trendu predaja a funkcia TEXT, ktorá umožňuje zobraziť číslo v akomkoľvek formáte. Jeho syntax je opísaná nižšie:

TEXT (hodnota; formát)

Formát je zadaný v úvodzovkách, rovnako ako keby ste zadávali vlastný formát v okne Formát buniek.

Zložitejšie texty môžu byť tiež automatizované. V mojej praxi došlo k automatizácii dlhých, no rutinných komentárov k manažérskemu reportingu vo formáte „INDIKÁTOR klesol / vzrástol o XX oproti plánu, a to najmä z dôvodu rastu / poklesu FAKTORA1 o XX, rastu / poklesu FAKTORA2 o XX. YY …“s meniacim sa zoznamom faktorov. Ak takéto komentáre píšete často a proces ich písania sa dá algoritmizovať, oplatí sa raz zamotať hlavu a vytvoriť vzorec alebo makro, ktoré vám ušetrí aspoň časť práce.

Ako uložiť údaje do každej bunky po zreťazení

Keď bunky zlúčite, zachová sa iba jedna hodnota. Excel na to upozorňuje pri pokuse o zlúčenie buniek:

Ak ste teda mali vzorec závislý od každej bunky, po ich skombinovaní prestane fungovať (chyba # N / A v riadkoch 3-4 príkladu):

Ak chcete zlúčiť bunky a zároveň zachovať údaje v každej z nich (možno máte vzorec ako v tomto abstraktnom príklade; možno chcete zlúčiť bunky, ale všetky údaje si ponechať pre budúcnosť alebo ich zámerne skryť), zlúčte všetky bunky na hárku, vyberte ich a potom pomocou príkazu Format Painter preneste formátovanie do buniek, ktoré potrebujete skombinovať:

Ako zostaviť pivot z viacerých zdrojov údajov

Ak potrebujete zostaviť pivot z viacerých zdrojov údajov naraz, budete musieť na pás s nástrojmi alebo panel rýchleho prístupu pridať „Sprievodcu kontingenčnou tabuľkou a grafom“, ktorý takúto možnosť má.

Môžete to urobiť takto: „Súbor“→ „Možnosti“→ „Panel s nástrojmi Rýchly prístup“→ „Všetky príkazy“→ „Sprievodca kontingenčnou tabuľkou a grafom“→ „Pridať“:

Potom sa na páse s nástrojmi zobrazí príslušná ikona, kliknutím na ňu sa vyvolá rovnaký sprievodca:

Keď naň kliknete, zobrazí sa dialógové okno:

V ňom musíte vybrať položku „V niekoľkých rozsahoch konsolidácie“a kliknúť na „Ďalej“. V ďalšom kroku môžete vybrať "Vytvoriť jedno pole stránky" alebo "Vytvoriť polia stránky". Ak chcete nezávisle prísť s názvom pre každý zo zdrojov údajov, vyberte druhú položku:

V ďalšom okne pridajte všetky rozsahy, na základe ktorých bude pivot zostavený, a pomenujte ich:

Potom v poslednom dialógovom okne zadajte, kde sa umiestni zostava kontingenčnej tabuľky - na existujúcom alebo novom hárku:

Prehľad kontingenčnej tabuľky je pripravený. Vo filtri „Stránka 1“môžete v prípade potreby vybrať iba jeden zo zdrojov údajov:

Ako vypočítať počet výskytov textu A v texte B ("tarifa MTS SuperMTS" - dva výskyty skratky MTS)

V tomto príklade stĺpec A obsahuje niekoľko textových riadkov a našou úlohou je zistiť, koľkokrát každý z nich obsahuje hľadaný text umiestnený v bunke E1:

Na vyriešenie tohto problému môžete použiť zložitý vzorec pozostávajúci z nasledujúcich funkcií:

1. DLSTR (LEN) - vypočíta dĺžku textu, jediným argumentom je text. Príklad: DLSTR ("stroj") = 6.
2. SUBSTITUTE - nahradí konkrétny text v textovom reťazci iným. Syntax: SUBSTITUTE (text; starý_text; nový_text). Príklad: SUBSTITUTE („auto“; „auto“; „“) = „mobil“.
3. UPPER - nahradí všetky znaky v reťazci veľkými písmenami. Jediným argumentom je text. Príklad: UPPER (“stroj”) = “AUTO”. Túto funkciu potrebujeme na vyhľadávanie bez ohľadu na veľkosť písmen. Koniec koncov, HORNÉ ("auto") = HORNÉ ("Stroj")

Ak chcete nájsť výskyt určitého textového reťazca v inom, musíte vymazať všetky jeho výskyty v pôvodnom a porovnať dĺžku výsledného reťazca s pôvodným:

DLSTR (“tarifa MTS Super MTS”) – DLSTR (“tarifa super”) = 6

A potom vydeľte tento rozdiel dĺžkou reťazca, ktorý sme hľadali:

6 / DLSTR („MTS“) = 2

Je to presne dvakrát, čo je riadok „MTS“zaradený do pôvodného.

Zostáva napísať tento algoritmus v jazyku vzorcov (označme „textom“text, v ktorom hľadáme výskyty, a výrazom „hľadaný“- ten, ktorého počet výskytov nás zaujíma):

= (DLSTR (text) -LSTR (NÁHRADIE (HORNÉ (text); UPPER (vyhľadávanie), ""))) / DLSTR (vyhľadávanie)

V našom príklade vyzerá vzorec takto:

= (DLSTR (A2) -LSTR (NÁHRADIE (HORNÉ (A2), HORNÉ ($ E $ 1), „“))) / DLSTR ($ E $ 1)