Excel life hacks pro ty, kteří se zabývají reportováním a zpracováním dat

2024 Autor: Malcolm Clapton | [email protected]. Naposledy změněno: 2024-01-13 01:00

V tomto příspěvku Renat Shagabutdinov, náměstek generálního ředitele nakladatelství Mann, Ivanov a Ferber, sdílí několik skvělých životních hacků Excelu. Tyto tipy budou užitečné pro každého, kdo se zabývá různými reportingy, zpracováním dat a tvorbou prezentací.

Tento článek obsahuje jednoduché techniky pro zjednodušení práce v Excelu. Jsou užitečné zejména pro ty, kteří se zabývají manažerským reportingem, připravují různé analytické reporty na základě stahování z 1C a dalších reportů, tvoří z nich prezentace a diagramy pro management. Nepředstírám absolutní novinku - v té či oné podobě byly tyto techniky pravděpodobně diskutovány na fórech nebo zmíněny v článcích.

Jednoduché alternativy k VLOOKUP a HLOOKUP, pokud požadované hodnoty nejsou v prvním sloupci tabulky: VYHLEDAT, INDEX + HLEDAT

Funkce SVYHLEDAT a VVYHLEDAT fungují pouze v případě, že jsou požadované hodnoty v prvním sloupci nebo řádku tabulky, ze které chcete získat data.

Jinak jsou dvě možnosti:

1. Použijte funkci LOOKUP.

   Má následující syntaxi: LOOKUP (hodnota_hledání; vektor_vyhledání; vektor_výsledku). Ale aby to fungovalo správně, hodnoty rozsahu view_vector musí být seřazeny ve vzestupném pořadí:

   

2. Použijte kombinaci funkcí MATCH a INDEX.

   Funkce MATCH vrátí pořadové číslo prvku v poli (s její pomocí zjistíte, ve kterém řádku tabulky se hledaný prvek nachází) a funkce INDEX vrátí prvek pole s daným číslem (které zjistíme pomocí funkce MATCH).

   

   Syntaxe funkce:

   • SEARCH (hledaná_hodnota; vyhledávací_pole; typ_shody) – pro náš případ potřebujeme typ shody „přesná shoda“, odpovídá číslu 0.

   • INDEX (pole; číslo_řádku; [číslo_sloupce]). V tomto případě nemusíte zadávat číslo sloupce, protože pole se skládá z jednoho řádku.

Jak rychle vyplnit prázdné buňky v seznamu

Úkolem je vyplnit buňky ve sloupci hodnotami nahoře (tak, aby téma bylo v každém řádku tabulky, a ne pouze v prvním řádku bloku knih na dané téma):

Vyberte sloupec „Předmět“, klikněte na pás karet ve skupině „Domů“, tlačítko „Najít a vybrat“→ „Vyberte skupinu buněk“→ „Prázdné buňky“a začněte zadávat vzorec (tj. znaménko) a přejděte na buňku nahoře jednoduše kliknutím na šipku nahoru na klávesnici. Poté stiskněte Ctrl + Enter. Poté můžete přijatá data uložit jako hodnoty, protože vzorce již nejsou potřeba:

Jak najít chyby ve vzorci

Výpočet samostatné části vzorce

Chcete-li porozumět složitému vzorci (ve kterém jsou jako argumenty funkce použity jiné funkce, to znamená, že některé funkce jsou vnořeny do jiných) nebo v něm najít zdroj chyb, musíte často vypočítat jeho část. Existují dva snadné způsoby:

1. Chcete-li vypočítat část vzorce přímo v řádku vzorců, vyberte tuto část a stiskněte F9:

   

   V tomto příkladu byl problém s funkcí SEARCH - byly v ní prohozeny argumenty. Je důležité si uvědomit, že pokud nezrušíte výpočet části funkce a stisknete Enter, pak vypočítaná část zůstane číslem.

2. Klikněte na tlačítko Vypočítat vzorec ve skupině Vzorce na pásu karet:

   

   V okně, které se objeví, můžete vypočítat vzorec krok za krokem a určit, v jaké fázi a ve které funkci dojde k chybě (pokud existuje):

   

Jak určit, na čem vzorec závisí nebo na co odkazuje

Chcete-li určit, na kterých buňkách vzorec závisí, ve skupině Vzorce na pásu karet klikněte na tlačítko Ovlivnění buněk:

Objeví se šipky označující, na čem závisí výsledek výpočtu.

Pokud je zobrazen symbol zvýrazněný na obrázku červeně, pak vzorec závisí na buňkách na jiných listech nebo v jiných knihách:

Kliknutím na něj můžeme přesně vidět, kde se nacházejí ovlivňující buňky nebo rozsahy:

Vedle tlačítka "Ovlivňování buněk" je tlačítko "Závislé buňky", které funguje stejně: zobrazuje šipky z aktivní buňky se vzorcem k buňkám, které jsou na ní závislé.

Tlačítko "Odebrat šipky", umístěné ve stejném bloku, umožňuje odstranit šipky k ovlivňujícím buňkám, šipky k závislým buňkám nebo oba typy šipek najednou:

Jak najít součet (počet, průměr) hodnot buněk z více listů

Řekněme, že máte několik listů stejného typu s daty, která chcete přidat, spočítat nebo zpracovat jiným způsobem:

Chcete-li to provést, zadejte do buňky, ve které chcete vidět výsledek, standardní vzorec, například SUM (SUM), a zadejte název prvního a posledního listu ze seznamu listů, které potřebujete zpracovat. argument oddělený dvojtečkou:

Obdržíte součet buněk s adresou B3 z listů "Data1", "Data2", "Data3":

Toto adresování funguje pro listy umístěné důsledně … Syntaxe je následující: = FUNCTION (první_seznam: poslední_seznam! Odkaz na rozsah).

Jak automaticky vytvářet šablony frází

Pomocí základních principů práce s textem v Excelu a několika jednoduchých funkcí můžete připravit šablonové fráze pro sestavy. Několik zásad práce s textem:

• Text zřetězíme pomocí znaku & (můžete nahradit funkcí CONCATENATE, ale to nedává moc smysl).
• Text je vždy psán v uvozovkách, odkazy na buňky s textem jsou vždy bez.
• Chcete-li získat servisní znak "uvozovky", použijte funkci CHAR s argumentem 32.

Příklad vytvoření šablony fráze pomocí vzorců:

Výsledek:

V tomto případě se kromě funkce CHAR (pro zobrazení nabídek) používá funkce IF, která umožňuje měnit text podle toho, zda je pozitivní trend prodeje, a funkce TEXT, která umožňuje zobrazit číslo v libovolném formátu. Jeho syntaxe je popsána níže:

TEXT (hodnota; formát)

Formát je uveden v uvozovkách, stejně jako kdybyste zadávali vlastní formát v okně Formát buněk.

Složitější texty lze také automatizovat. V mé praxi docházelo k automatizaci dlouhých, ale rutinních komentářů k manažerskému reportingu ve formátu „INDIKÁTOR poklesl / stoupl o XX oproti plánu, zejména kvůli růstu / poklesu FAKTORU1 o XX, růstu / poklesu FAKTORU2 o XX. YY …“s měnícím se seznamem faktorů. Pokud takové komentáře píšete často a proces jejich psaní lze algoritmizovat, vyplatí se jednou zamotat hlavu a vytvořit vzorec nebo makro, které vám ušetří alespoň část práce.

Jak uložit data do každé buňky po zřetězení

Při sloučení buněk zůstane zachována pouze jedna hodnota. Excel na to upozorňuje při pokusu o sloučení buněk:

Pokud jste tedy měli vzorec závislý na každé buňce, přestane po jejich zkombinování fungovat (chyba # N / A v řádcích 3-4 příkladu):

Chcete-li sloučit buňky a přesto zachovat data v každé z nich (možná máte vzorec jako v tomto abstraktním příkladu; možná chcete sloučit buňky, ale ponechat si všechna data pro budoucnost nebo je záměrně skrýt), sloučte všechny buňky na listu, vyberte je a poté pomocí příkazu Format Painter přeneste formátování do buněk, které potřebujete zkombinovat:

Jak sestavit pivot z více zdrojů dat

Pokud potřebujete sestavit pivot z více zdrojů dat najednou, budete muset na pás karet nebo panel rychlého přístupu přidat „Průvodce kontingenční tabulkou a grafem“, který takovou možnost má.

Můžete to udělat následovně: „Soubor“→ „Možnosti“→ „Panel nástrojů Rychlý přístup“→ „Všechny příkazy“→ „Průvodce kontingenční tabulkou a grafem“→ „Přidat“:

Poté se na pásu karet zobrazí odpovídající ikona, kliknutím na ni vyvoláte stejného průvodce:

Když na něj kliknete, objeví se dialogové okno:

V něm musíte vybrat položku „V několika rozsazích konsolidace“a kliknout na „Další“. V dalším kroku můžete vybrat "Vytvořit jedno pole stránky" nebo "Vytvořit pole stránky". Pokud chcete nezávisle vymyslet název pro každý ze zdrojů dat, vyberte druhou položku:

V dalším okně přidejte všechny rozsahy, na jejichž základě bude pivot postaven, a pojmenujte je:

Poté v posledním dialogovém okně určete, kam bude sestava kontingenční tabulky umístěna - na existující nebo nový list:

Zpráva kontingenční tabulky je připravena. Ve filtru „Stránka 1“můžete v případě potřeby vybrat pouze jeden ze zdrojů dat:

Jak vypočítat počet výskytů textu A v textu B ("tarif MTS SuperMTS" - dva výskyty zkratky MTS)

V tomto příkladu obsahuje sloupec A několik textových řádků a naším úkolem je zjistit, kolikrát každý z nich obsahuje hledaný text umístěný v buňce E1:

Chcete-li tento problém vyřešit, můžete použít složitý vzorec skládající se z následujících funkcí:

1. DLSTR (LEN) - vypočítá délku textu, jediným argumentem je text. Příklad: DLSTR ("stroj") = 6.
2. SUBSTITUTE - nahradí určitý text v textovém řetězci jiným. Syntaxe: SUBSTITUTE (text; starý_text; nový_text). Příklad: SUBSTITUTE („auto“; „auto“; „“) = „mobil“.
3. UPPER - nahradí všechny znaky v řetězci velkými písmeny. Jediným argumentem je text. Příklad: UPPER (“stroj”) = “CAR”. Tuto funkci potřebujeme k vyhledávání bez ohledu na velikost písmen. Koneckonců, HORNÍ ("auto") = HORNÍ ("Stroj")

Chcete-li najít výskyt určitého textového řetězce v jiném, musíte odstranit všechny jeho výskyty v původním a porovnat délku výsledného řetězce s původním:

DLSTR („Tarif MTS Super MTS“) - DLSTR („Tarif Super“) = 6

A pak vydělte tento rozdíl délkou řetězce, který jsme hledali:

6 / DLSTR („MTS“) = 2

Je to přesně dvakrát, co je řádek "MTS" zahrnut v původním.

Zbývá napsat tento algoritmus v jazyce vzorců (označme „textem“text, ve kterém hledáme výskyty, a „hledaným“- ten, jehož počet výskytů nás zajímá:

= (DLSTR (text) -LSTR (NAHRADIT (HORNÍ (text); UPPER (hledat), ""))) / DLSTR (hledat)

V našem příkladu vypadá vzorec takto:

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