3 bláznivé vzorce programu Microsoft Excel, ktoré sú mimoriadne užitočné

3 bláznivé vzorce programu Microsoft Excel, ktoré sú mimoriadne užitočné

Vzorce programu Microsoft Excel môžu robiť takmer čokoľvek. V tomto článku sa s tromi užitočnými príkladmi dozviete, aké účinné môžu byť vzorce programu Microsoft Excel a podmienené formátovanie.





Zoznámte sa s programom Microsoft Excel

Pozreli sme sa na niekoľko rôznych spôsobov, ako lepšie využívať Excel, ako napr kde nájdete skvelé šablóny programu Excel na stiahnutie a ako používať Excel ako nástroj na správu projektov .





Veľká časť výkonu programu Excel sa skrýva za vzorcami a pravidlami programu Excel, ktoré vám pomôžu automaticky manipulovať s údajmi a informáciami bez ohľadu na to, aké údaje vložíte do tabuľky.





Poďme sa pozrieť na to, ako môžete používať vzorce a ďalšie nástroje na lepšie používanie programu Microsoft Excel.

Podmienené formátovanie pomocou vzorcov programu Excel

Jeden z nástrojov, ktoré ľudia nepoužívajú dostatočne často, je Podmienené formátovanie. Pomocou vzorcov, pravidiel alebo iba niekoľkých skutočne jednoduchých nastavení Excelu môžete tabuľku transformovať na automatizovaný informačný panel.



K podmienenému formátovaniu sa dostanete kliknutím na ikonu Domov kartu a kliknite na Podmienené formátovanie ikona panela s nástrojmi.

V časti Podmienené formátovanie existuje veľa možností. Väčšina z nich presahuje rámec tohto konkrétneho článku, ale väčšina z nich sa týka zvýraznenia, vyfarbenia alebo tieňovania buniek na základe údajov v tejto bunke.





Toto je pravdepodobne najbežnejšie používanie podmieneného formátovania-napríklad zafarbenie bunky na červenú pomocou vzorcov, ktoré sú menšie alebo vyššie ako. Získajte viac informácií o tom, ako používať výpisy IF v programe Excel.

Jeden z menej používaných nástrojov podmieneného formátovania je Sady ikon možnosť, ktorá ponúka skvelú sadu ikon, ktoré môžete použiť na premenu bunky údajov programu Excel na ikonu displeja na paneli.





Keď kliknete na Spravovať pravidlá , zavedie vás do Správca pravidiel podmieneného formátovania .

bezplatné stiahnutie windows xp pre pc

V závislosti od údajov, ktoré ste vybrali pred výberom sady ikon, sa v okne Správca zobrazí bunka s vybranou sadou ikon.

Keď kliknete na Upraviť pravidlo , uvidíte dialógové okno, kde sa kúzlo stane.

Tu môžete vytvoriť logický vzorec a rovnice, ktoré zobrazia požadovanú ikonu na paneli.

Tento ukážkový informačný panel bude zobrazovať čas strávený rôznymi úlohami a časovo obmedzený rozpočet. Ak prekročíte polovicu rozpočtu, zobrazí sa žlté svetlo. Ak ste úplne nad rámec rozpočtu, zmení sa na červenú.

Ako vidíte, tento informačný panel ukazuje, že časové rozpočtovanie nie je úspešné.

Takmer polovica času sa minie nad rozpočtované sumy.

Čas znova sa zamerať a lepšie hospodáriť so svojim časom!

1. Použitie funkcie VLookup

Ak chcete používať pokročilejšie funkcie programu Microsoft Excel, tu je pár, ktoré môžete vyskúšať.

Pravdepodobne ste poznali funkciu VLookup, ktorá vám umožňuje vyhľadávať v zozname pre konkrétnu položku v jednom stĺpci a vrátiť údaje z iného stĺpca v rovnakom riadku ako táto položka.

Funkcia bohužiaľ vyžaduje, aby sa položka, ktorú hľadáte v zozname, nachádzala v ľavom stĺpci a údaje, ktoré hľadáte, boli napravo, ale čo keď sú prepnuté?

V nižšie uvedenom príklade, čo keď chcem nájsť úlohu, ktorú som vykonal 25. júna 2018 z nasledujúcich údajov?

V tomto prípade hľadáte hodnoty napravo a zodpovedajúcu hodnotu chcete vrátiť vľavo.

Ak čítate pro-užívateľské fóra programu Microsoft Excel, zistíte, že veľa ľudí hovorí, že to nie je možné s VLookup. Na to musíte použiť kombináciu funkcií Index a Match. Nie je to celkom pravda.

VLookup môžete nechať fungovať týmto spôsobom tak, že doň vnoríte funkciu CHOOSE. V takom prípade bude vzorec programu Excel vyzerať takto:

'=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)'

Táto funkcia znamená, že chcete vo vyhľadávacom zozname nájsť dátum 25. 6. 2013 a potom vrátiť zodpovedajúcu hodnotu z indexu stĺpcov.

V takom prípade si všimnete, že index stĺpca je '2', ale ako vidíte, stĺpec v tabuľke vyššie je skutočne 1, nie?

To je pravda, ale to, čo robíte s ' VYBERTE SI 'funkcia manipuluje s týmito dvoma poľami.

Rozsahom údajov priraďujete referenčné „indexové“ čísla - dátumy priradíte indexu číslo 1 a úlohy indexu číslo 2.

Keď teda do funkcie VLookup zadáte „2“, v skutočnosti sa odvolávate na index číslo 2 vo funkcii CHOOSE. Super, nie?

VLookup teraz používa Dátum stĺpci a vráti údaje zo súboru Úloha stĺpec, aj keď je Úloha vľavo.

ako otvoriť súbory jar v java windows 10

Teraz, keď poznáte tento malý kúsok, predstavte si, čo ešte môžete urobiť!

Ak sa pokúšate vykonať ďalšie pokročilé úlohy vyhľadávania údajov, prečítajte si tento článok o vyhľadávanie údajov v programe Excel pomocou vyhľadávacích funkcií .

2. Vnorený vzorec na analýzu reťazcov

Tu je ešte jeden bláznivý vzorec programu Excel pre vás! Môžu nastať prípady, kedy buď importujete údaje do programu Microsoft Excel z externého zdroja pozostávajúceho z reťazca oddelených údajov.

Po vložení údajov ich chcete analyzovať do jednotlivých komponentov. Tu je príklad informácií o mene, adrese a telefónnom čísle oddelených znakom ';' charakter.

Tu je návod, ako môžete tieto informácie analyzovať pomocou vzorca v Exceli (zistíte, či sa s týmto šialenstvom dokážete aj mentálne riadiť):

V prvom poli na extrahovanie položky úplne vľavo (meno osoby) jednoducho použijete vo vzorci funkciu VĽAVO.

'=LEFT(A2,FIND(';',A2,1)-1)'

Táto logika funguje takto:

  • Hľadá textový reťazec od A2
  • Nájde ';' symbol oddeľovača
  • Odpočíta sa jedna za správne umiestnenie konca sekcie reťazca
  • Do tohto bodu zachytí text úplne vľavo

V tomto prípade je text úplne vľavo „Ryan“. Misia splnená.

3. Vnorený vzorec v programe Excel

Ale čo ostatné sekcie?

Môžu to byť jednoduchšie spôsoby, ale keďže sa chceme pokúsiť vytvoriť najbláznivejší vzorec vnoreného Excelu (ktorý skutočne funguje), použijeme jedinečný prístup.

Ak chcete extrahovať časti napravo, musíte vnoriť viacero PRAVÝCH funkcií, aby ste chytili časť textu až do začiatku ';' symbol a znova na ňom vykonajte funkciu VĽAVO. Takto to vyzerá pri extrahovaní časti adresy s číslom ulice.

'=LEFT((RIGHT(A2,LEN(A2)-FIND(';',A2))),FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2))),1)-1)'

Vyzerá to šialene, ale nie je ťažké dať sa dohromady. Jediné, čo som urobil, je prevziať túto funkciu:

RIGHT(A2,LEN(A2)-FIND(';',A2))

A vložil ho na každé miesto v priečinku VĽAVO funkcia vyššie tam, kde je 'A2'.

Správne sa extrahuje druhá časť reťazca.

ako opraviť žiadny internet zabezpečený

Každá nasledujúca časť reťazca potrebuje vytvoriť ďalšie hniezdo. Teraz všetko, čo musíte urobiť, je vziať si ' SPRÁVNY „rovnica, ktorú ste vytvorili v poslednej sekcii, a prilepte ju do nového PRAVÉHO vzorca s predchádzajúcim PRÁVNYM vzorcom, ktorý je do neho vložený, kde vidíte„ A2 “. Takto to vyzerá.

(RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2))))))

Potom musíte vziať ten vzorec a umiestniť ho do pôvodného ľavého vzorca, kdekoľvek je 'A2'.

Konečný vzorec ohýbania mysle vyzerá takto:

'=LEFT((RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2)))))),FIND(';',(RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2)))))),1)-1)'

Tento vzorec správne extrahuje „Portland, ME 04076“ z pôvodného reťazca.

Ak chcete extrahovať ďalšiu časť, zopakujte vyššie uvedený postup znova.

Vaše vzorce v Exceli môžu byť naozaj veľmi slučkové, ale robíte len to, že do seba vystrihujete a vkladáte dlhé vzorce a vytvárate dlhé hniezda, ktoré stále fungujú.

Áno, toto spĺňa požiadavku na „blázon“. Buďme však úprimní, existuje oveľa jednoduchší spôsob, ako dosiahnuť to isté pomocou jednej funkcie.

Stačí vybrať stĺpec s oddelenými údajmi a potom pod príponou Údaje položku ponuky, zvoľte Text do stĺpcov .

Zobrazí sa okno, v ktorom môžete reťazec rozdeliť podľa ľubovoľného oddeľovača. Stačí zadať ' ; “a uvidíte, že náhľad vašich vybraných údajov sa podľa toho zmení.

Niekoľkými kliknutiami môžete urobiť to isté ako vyššie uvedený bláznivý vzorec ... ale kde je v tom zábava?

Zbláznite sa zo vzorcov programu Microsoft Excel

Tak tu to máte. Vyššie uvedené vzorce dokazujú, ako prehnane sa môže človek pri vytváraní vzorcov programu Microsoft Excel na splnenie určitých úloh dostať.

Niekedy tieto vzorce programu Excel nie sú v skutočnosti najľahším (alebo najlepším) spôsobom, ako dosiahnuť veci. Väčšina programátorov vám povie, aby ste to zjednodušili, a to platí rovnako pre vzorce Excelu, ako pre čokoľvek iné.

Ak to s programom Excel chcete naozaj myslieť vážne, prečítajte si nášho sprievodcu pre začiatočníkov k používaniu programu Microsoft Excel. Má všetko, čo potrebujete na zvýšenie produktivity pomocou Excelu. Potom sa poraďte s naším cheatom základných funkcií programu Excel, kde nájdete ďalšie rady.

Obrazový kredit: kues/Depositphotos

zdieľam zdieľam Tweet E -mail Najlepších 7 finančných funkcií v programe Excel

Bez ohľadu na to, či ste účtovník alebo finančný profesionál, mali by ste tieto vzorce programu Excel ovládať.

Čítajte ďalej
Súvisiace témy
  • Produktivita
  • Tabuľkové tipy
  • Microsoft Excel
  • Microsoft Office 2016
  • Tipy pre Microsoft Office
  • Microsoft Office 2019
O autorovi Ryan Dube(942 publikovaných článkov)

Ryan má bakalársky titul z elektrotechniky. Pracoval 13 rokov v automatizačnom inžinierstve, 5 rokov v IT a teraz je Apps Engineer. Bývalý vedúci redaktor MakeUseOf vystupoval na národných konferenciách o vizualizácii údajov a bol vystupovaný v národnej televízii a rozhlase.

Viac od Ryana Dubeho

prihlásiť sa ku odberu noviniek

Pripojte sa k nášmu bulletinu a získajte technické tipy, recenzie, bezplatné elektronické knihy a exkluzívne ponuky!

Kliknutím sem sa prihlásite na odber