Vzorce a funkce jsou stavebními kameny pro práci s číselnými daty v aplikaci Excel. Tento článek vás seznámí se vzorci a funkcemi.
V tomto článku se budeme zabývat následujícími tématy.
- Co jsou vzorce v aplikaci Excel?
- Chyby, kterým je třeba se vyvarovat při práci se vzorci v aplikaci Excel
- Co je funkce v aplikaci Excel?
- Důležitost funkcí
- Společné funkce
- Numerické funkce
- Řetězcové funkce
- Funkce Datum a čas
- V Funkce vyhledávání
Data výukových programů
V tomto kurzu budeme pracovat s následujícími datovými sadami.
Rozpočet na dodávky domů
| S / N | POLOŽKA | POČET | CENA | Mezisoučet | Je to dostupné? |
|---|---|---|---|---|---|
| 1 | Manga | 9 | 600 | ||
| 2 | Pomeranče | 3 | 1200 | ||
| 3 | Rajčata | 1 | 2500 | ||
| 4 | Stolní olej | 5 | 6500 | ||
| 5 | Tonická voda | 13 | 3900 |
Časový plán projektu výstavby domu
| S / N | POLOŽKA | DATUM ZAHÁJENÍ | DATUM UKONČENÍ | DURATION (DAYS) |
|---|---|---|---|---|
| 1 | Země průzkumu | 04/02/2015 | 07/02/2015 | |
| 2 | Laický základ | 10/02/2015 | 15/02/2015 | |
| 3 | Zastřešení | 27/02/2015 | 3. 3. 2015 | |
| 4 | Malování | 09/03/2015 | 21/03/2015 |
Co jsou vzorce v aplikaci Excel?
FORMULAS IN EXCEL je výraz, který pracuje s hodnotami v řadě adres buněk a operátorů. Například = A1 + A2 + A3, který vyhledá součet rozsahu hodnot od buňky A1 do buňky A3. Příklad vzorce složeného z diskrétních hodnot jako = 6 * 3.
=A2 * D2 / 2
TADY,
řekne aplikaci Excel, že se jedná o vzorec, a měl by ji vyhodnotit."A2" * D2"odkazuje na adresy buněk A2 a D2 a poté vynásobí hodnoty nalezené v těchto adresách buněk."/"je dělicí aritmetický operátor"2"je diskrétní hodnota
Praktické cvičení vzorců
Budeme pracovat s ukázkovými daty pro domácí rozpočet, abychom vypočítali mezisoučet.
- Vytvořte nový sešit v aplikaci Excel
- Zadejte data uvedená výše v rozpočtu na domácí potřeby.
- Váš list by měl vypadat následovně.
Nyní napíšeme vzorec, který počítá mezisoučet
Zaostřete na buňku E4
Zadejte následující vzorec.
=C4*D4
TADY,
"C4*D4"používá násobení aritmetického operátoru (*) k vynásobení hodnoty adresy buňky C4 a D4.
Stiskněte klávesu Enter
Získáte následující výsledek
Následující animovaný obrázek ukazuje, jak automaticky vybrat adresu buňky a použít stejný vzorec na další řádky.
Chyby, kterým je třeba se vyvarovat při práci se vzorci v aplikaci Excel
- Pamatujte na pravidla hranatých závorek rozdělení, násobení, sčítání a odčítání (BODMAS). To znamená, že výrazy v závorkách jsou vyhodnoceny jako první. U aritmetických operátorů se nejdříve vyhodnotí dělení, po kterém následuje násobení, pak sčítání a odčítání je poslední, která se má vyhodnotit. Pomocí tohoto pravidla můžeme přepsat výše uvedený vzorec jako = (A2 * D2) / 2. Tím zajistíme, že A2 a D2 budou nejprve vyhodnoceny a poté vyděleny dvěma.
- Tabulkové vzorce aplikace Excel obvykle pracují s číselnými údaji; můžete využít ověření dat k určení typu dat, která mají být přijata buňkou, tj. pouze čísla.
- Abyste se ujistili, že pracujete se správnými adresami buněk, na které se odkazuje ve vzorcích, můžete stisknout klávesu F2 na klávesnici. Tím se zvýrazní adresy buněk použité ve vzorci a můžete zkontrolovat, zda jsou požadované adresy buněk.
- Když pracujete s mnoha řádky, můžete použít pořadová čísla pro všechny řádky a mít počet záznamů ve spodní části listu. Měli byste porovnat počet sériových čísel s celkovým počtem záznamů, abyste se ujistili, že vaše vzorce zahrnovaly všechny řádky.
Podívejte se na 10 nejlepších vzorových tabulek aplikace Excel
Co je funkce v aplikaci Excel?
FUNKCE V EXCELU je předdefinovaný vzorec, který se používá pro konkrétní hodnoty v určitém pořadí. Funkce se používá pro rychlé úkoly, jako je nalezení součtu, počtu, průměru, maximální hodnoty a minimálních hodnot pro řadu buněk. Například buňka A3 níže obsahuje funkci SUM, která vypočítává součet rozsahu A1: A2.
- SUM pro součet rozsahu čísel
- PRŮMĚR pro výpočet průměru daného rozsahu čísel
- COUNT pro počítání počtu položek v daném rozsahu
Důležitost funkcí
Funkce zvyšují produktivitu uživatelů při práci s aplikací Excel . Řekněme, že byste chtěli získat celkový součet za výše uvedený rozpočet na dodávky domů. Chcete-li to zjednodušit, můžete pomocí součtu získat celkový součet. Pomocí vzorce byste museli odkazovat na buňky E4 až E8 jeden po druhém. Budete muset použít následující vzorec.
= E4 + E5 + E6 + E7 + E8
S funkcí byste výše uvedený vzorec napsali jako
=SUM (E4:E8)
Jak vidíte z výše uvedené funkce použité k získání součtu rozsahu buněk, je mnohem efektivnější použít funkci k získání součtu než pomocí vzorce, který bude muset odkazovat na mnoho buněk.
Společné funkce
Podívejme se na některé z nejčastěji používaných funkcí ve vzorcích aplikace Excel. Začneme statistickými funkcemi.
| S / N | FUNKCE | KATEGORIE | POPIS | POUŽÍVÁNÍ |
|---|---|---|---|---|
| 01 | SOUČET | Math & Trig | Přidá všechny hodnoty do rozsahu buněk | = SUM (E4: E8) |
| 02 | MIN | Statistický | Najde minimální hodnotu v rozsahu buněk | = MIN (E4: E8) |
| 03 | MAX | Statistický | Najde maximální hodnotu v rozsahu buněk | = MAX (E4: E8) |
| 04 | PRŮMĚRNÝ | Statistický | Vypočítá průměrnou hodnotu v rozsahu buněk | = PRŮMĚR (E4: E8) |
| 05 | POČET | Statistický | Spočítá počet buněk v rozsahu buněk | = POČET (E4: E8) |
| 06 | LEN | Text | Vrátí počet znaků v textu řetězce | = LEN (B7) |
| 07 | SUMIF | Math & Trig | Přidá všechny hodnoty do rozsahu buněk, které splňují zadaná kritéria. = SUMIF (rozsah, kritéria, [sum_range]) | = SUMIF (D4: D8, "> = 1000", C4: C8) |
| 08 | AVERAGEIF | Statistický | Vypočítá průměrnou hodnotu v rozsahu buněk, které splňují zadaná kritéria. = AVERAGEIF (rozsah, kritéria, [průměrný_rozsah]) | = AVERAGEIF (F4: F8, "Ano", E4: E8) |
| 09 | DNY | Čas schůzky | Vrátí počet dní mezi dvěma daty | = DNY (D4, C4) |
| 10 | NYNÍ | Čas schůzky | Vrátí aktuální systémové datum a čas | = NYNÍ () |
Numerické funkce
Jak název napovídá, tyto funkce fungují na číselných datech. V následující tabulce jsou uvedeny některé běžné numerické funkce.
| S / N | FUNKCE | KATEGORIE | POPIS | POUŽÍVÁNÍ |
|---|---|---|---|---|
| 1 | ČÍSLO | Informace | Vrátí True, pokud je zadaná hodnota číselná a False, pokud není číselná | = ISNUMBER (A3) |
| 2 | RAND | Math & Trig | Generuje náhodné číslo mezi 0 a 1 | = RAND () |
| 3 | KOLO | Math & Trig | Zaokrouhlí desetinnou hodnotu na zadaný počet desetinných míst | = KOLO (3,14455,2) |
| 4 | MEDIÁN | Statistický | Vrátí číslo uprostřed sady daných čísel | = STŘEDNÍ (3,4,5,2,5) |
| 5 | PI | Math & Trig | Vrátí hodnotu matematické funkce PI (π) | = PI () |
| 6 | NAPÁJENÍ | Math & Trig | Vrátí výsledek čísla zvýšeného na mocninu. NAPÁJENÍ (číslo, síla) | = VÝKON (2,4) |
| 7 | MOD | Math & Trig | Vrátí Remainder, když rozdělíte dvě čísla | = MOD (10,3) |
| 8 | ŘÍMSKÝ | Math & Trig | Převede číslo na římské číslice | = ROMAN (1984) |
Řetězcové funkce
Tyto základní funkce aplikace Excel se používají k manipulaci s textovými daty. V následující tabulce jsou uvedeny některé běžné funkce řetězců.
| S / N | FUNKCE | KATEGORIE | POPIS | POUŽÍVÁNÍ | KOMENTÁŘ |
|---|---|---|---|---|---|
| 1 | VLEVO, ODJET | Text | Vrátí počet zadaných znaků od začátku (levé strany) řetězce | = VLEVO ("GURU99", 4) | Vlevo 4 znaky „GURU99“ |
| 2 | ŽE JO | Text | Vrátí počet zadaných znaků od konce (pravé strany) řetězce | = DOPRAVA ("GURU99", 2) | Vpravo 2 znaky „GURU99“ |
| 3 | STŘEDNÍ | Text | Načte počet znaků ze středu řetězce ze zadané počáteční polohy a délky. = MID (text, start_num, num_chars) | = MID ("GURU99", 2,3) | Načítání znaků 2 až 5 |
| 4 | ISTEXT | Informace | Vrátí True, pokud je zadaným parametrem Text | = ISTEXT (hodnota) | value - Hodnota ke kontrole. |
| 5 | NALÉZT | Text | Vrátí počáteční pozici textového řetězce v jiném textovém řetězci. Tato funkce rozlišuje velká a malá písmena. = FIND (find_text, within_text, [start_num]) | = FIND ("oo", "zastřešení", 1) | Najděte oo v části „Zastřešení“, výsledek je 2 |
| 6 | NAHRADIT | Text | Nahradí část řetězce jiným zadaným řetězcem. = REPLACE (old_text, start_num, num_chars, new_text) | = REPLACE ("Střešní krytina", 2,2, "xx") | Nahradit „oo“ výrazem „xx“ |
Funkce data a času
Tyto funkce se používají k manipulaci s hodnotami data. V následující tabulce jsou uvedeny některé běžné funkce data
| S / N | FUNKCE | KATEGORIE | POPIS | POUŽÍVÁNÍ |
|---|---|---|---|---|
| 1 | DATUM | Čas schůzky | Vrátí číslo, které představuje datum v kódu aplikace Excel | = DATUM (2015,2,4) |
| 2 | DNY | Čas schůzky | Najděte počet dní mezi dvěma daty | = DNY (D6, C6) |
| 3 | MĚSÍC | Čas schůzky | Vrátí měsíc z hodnoty data | = MĚSÍC („2. 4. 2015“) |
| 4 | MINUTA | Čas schůzky | Vrátí minuty z časové hodnoty | = MINUTE ("12:31") |
| 5 | ROK | Čas schůzky | Vrátí rok z hodnoty data | = YEAR („04/02/2015“) |
Funkce VLOOKUP
Funkce VLOOKUP slouží k provedení vertikálního vyhledávání ve sloupci nejvíce vlevo a vrácení hodnoty ve stejném řádku ze zadaného sloupce. Vysvětlíme to laicky. Rozpočet domácích potřeb má sloupec se sériovým číslem, který jednoznačně identifikuje každou položku v rozpočtu. Předpokládejme, že máte sériové číslo položky a chcete znát popis položky, můžete použít funkci VLOOKUP. Takto by fungovala funkce VLOOKUP.
=VLOOKUP (C12, A4:B8, 2, FALSE)
TADY,
"=VLOOKUP"volá funkci vertikálního vyhledávání"C12"určuje hodnotu, která se má vyhledat ve sloupci nejvíce vlevo"A4:B8"určuje pole tabulky s daty"2"Určuje číslo sloupce s hodnotou řádku, která má být vrácena funkcí VLOOKUP"FALSE,"říká funkci VLOOKUP, že hledáme přesnou shodu dodané vyhledávací hodnoty
Animovaný obrázek níže to ukazuje v akci
Stáhněte si výše uvedený kód Excel
souhrn
Excel umožňuje manipulovat s daty pomocí vzorců nebo funkcí. Funkce jsou obecně produktivnější než psaní vzorců. Funkce jsou ve srovnání s vzorci také přesnější, protože prostor pro chyby je velmi minimální.
Zde je seznam důležitých vzorců a funkcí aplikace Excel
- Funkce SUM =
=SUM(E4:E8)
- Funkce MIN =
=MIN(E4:E8)
- Funkce MAX =
=MAX(E4:E8)
- PRŮMĚRNÁ funkce =
=AVERAGE(E4:E8)
- COUNT funkce =
=COUNT(E4:E8)
- DAYS funkce =
=DAYS(D4,C4)
- Funkce VLOOKUP =
=VLOOKUP (C12, A4:B8, 2, FALSE)
- Funkce DATE =
=DATE(2020,2,4)