Vzorce a funkce Excelu: Učte se se základními PŘÍKLADY
Charlotte Millerová
Vzorce a funkce jsou stavebními kameny práce s číselnými daty v Excelu. Tento článek vám představí vzorce a funkce.
Data výukových programů
V tomto tutoriálu budeme pracovat s následujícími datovými sadami.
Rozpočet domácích potřeb
| S / N | ITEM | KS | CENA | SUBTOTAL | Je to cenově dostupné? |
|---|---|---|---|---|---|
| 1 | Mango | 9 | 600 | ||
| 2 | Pomeranče | 3 | 1200 | ||
| 3 | rajčata | 1 | 2500 | ||
| 4 | Stolní olej | 5 | 6500 | ||
| 5 | Toniková voda | 13 | 3900 |
Harmonogram projektu stavby domu
| S / N | ITEM | POČÁTEČNÍ DATUM | DATUM UKONČENÍ | TRVÁNÍ (DNY) |
|---|---|---|---|---|
| 1 | Průzkumný pozemek | 04/02/2015 | 07/02/2015 | |
| 2 | Položit Foundation | 10/02/2015 | 15/02/2015 | |
| 3 | Střešní krytina | 27/02/2015 | 03/03/2015 | |
| 4 | Malba | 09/03/2015 | 21/03/2015 |
Co jsou vzorce v Excelu?
VZORCE V EXCELU je výraz, který pracuje s hodnotami v rozsahu adres buněk a operátorů. Například =A1+A2+A3, který najde součet rozsahu hodnot z 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,
"="říká Excelu, že se jedná o vzorec, a měl by ho 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 aritmetický operátor dělení"2"je diskrétní hodnota
Praktické cvičení vzorců
Pro výpočet mezisoučtu budeme pracovat se vzorovými údaji pro domácí rozpočet.
- Vytvořte nový sešit v aplikaci Excel
- Výše zadejte údaje uvedené v rozpočtu domácích potřeb.
- Váš pracovní list by měl vypadat následovně.
Nyní napíšeme vzorec, který vypočítá mezisoučet
Nastavte fokus na buňku E4
Zadejte následující vzorec.
=C4*D4
TADY,
"C4*D4"používá násobení aritmetickým operátorem (*) 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ých se vyvarovat při práci se vzorci v Excelu
- Pamatujte na pravidla Brackets dělení, násobení, sčítání a odčítání (BODMAS). To znamená, že výrazy jsou závorky jsou vyhodnoceny jako první. U aritmetických operátorů se nejprve vyhodnotí dělení, následuje násobení, pak sčítání a odčítání je posledním vyhodnocovaným. Pomocí tohoto pravidla můžeme přepsat výše uvedený vzorec jako =(A2 * D2) / 2. Tím zajistíme, že nejprve budou vyhodnoceny A2 a D2 a poté rozděleny dvěma.
- Vzorce tabulkového procesoru Excel obvykle pracují s číselnými daty; můžete využít ověřování dat k určení typu dat, která by měla být buňkou přijímána, tj. pouze čísla.
- Chcete-li se ujistit, že pracujete se správnými adresami buněk, na které se ve vzorcích odkazuje, můžete stisknout klávesu F2 na klávesnici. Tím se zvýrazní adresy buněk použité ve vzorci a můžete křížovou kontrolou zajistit, že se jedná o požadované adresy buněk.
- Když pracujete s mnoha řádky, můžete použít sériová čí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 zajistili, že vaše vzorce zahrnují všechny řádky.
Odjezd
Top 10 tabulkových vzorců Excelu
Co je funkce v Excelu?
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í hodnoty pro rozsah buněk. Například buňka A3 níže obsahuje funkci SUM, která vypočítá součet rozsahu A1:A2.
- SOUČET pro sčítání řady čísel
- PRŮMĚRNÝ pro výpočet průměru daného rozsahu čísel
- COUNT pro počítání počtu položek v daném rozsahu
Význam funkcí
Funkce zvyšují produktivitu uživatelů při práci s excelem. Řekněme, že byste chtěli získat celkový součet výše uvedeného rozpočtu na domácí potřeby. Chcete-li to zjednodušit, můžete použít vzorec k získání celkového součtu. Pomocí vzorce byste museli odkazovat na buňky E4 až E8 jednu po druhé. Museli byste použít následující vzorec.
= E4 + E5 + E6 + E7 + E8
S funkcí byste výše uvedený vzorec napsali jako
=SUM (E4:E8)
Jak můžete vidět z výše uvedené funkce použité k získání součtu rozsahu buněk, je mnohem efektivnější použít k získání součtu funkci než použití 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 ms excel. Začneme statistickými funkcemi.
| S / N | FUNKCE | KATEGORIE | POPIS | POUŽITÍ |
|---|---|---|---|---|
| 01 | SOUČET | Math & Trig | Sečte všechny hodnoty v rozsahu buněk | = SUM (E4: E8) |
| 02 | MIN | Statistický | Vyhledá minimální hodnotu v rozsahu buněk | =MIN(E4:E8) |
| 03 | MAX | Statistický | Vyhledá 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ĚRNÝ (E4: E8) |
| 05 | COUNT | Statistický | Spočítá počet buněk v rozsahu buněk | = COUNT (E4: E8) |
| 06 | LEN | Text | Vrátí počet znaků v textovém řetězci | = LEN (B7) |
| 07 | SUMIF | Math & Trig |
Přidá všechny hodnoty v rozsahu buněk, které splňují zadaná kritéria. =SUMIF(rozsah,kritérium,[rozsah_součtu]) |
=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érium,[průměrný_rozsah]) |
=AVERAGEIF(F4:F8,”Ano”,E4:E8) |
| 09 | DNY | Datum Čas | Vrátí počet dní mezi dvěma daty | =DAYS(D4,C4) |
| 10 | KUP TEĎ! | Datum Čas | Vrátí aktuální systémové datum a čas | = NYNÍ () |
Numerické funkce
Jak název napovídá, tyto funkce pracují na číselných datech. Následující tabulka ukazuje některé běžné numerické funkce.
| S / N | FUNKCE | KATEGORIE | POPIS | POUŽITÍ |
|---|---|---|---|---|
| 1 | ČÍSLO | Informace | Vrátí True, pokud je zadaná hodnota číselná, a False, pokud není číselná | = ISNUMBER (A3) |
| 2 | RAND | Math & Trig | Vygeneruje náhodné číslo mezi 0 a 1 | = RAND () |
| 3 | KOLO | Math & Trig | Zaokrouhlí desetinnou hodnotu na zadaný počet desetinných míst | = KOLA (3.14455,2) |
| 4 | MEDIAN | Statistický | Vrátí číslo uprostřed množiny daných čísel | =MEDIAN(3,4,5,2,5;XNUMX;XNUMX;XNUMX;XNUMX) |
| 5 | PI | Math & Trig | Vrátí hodnotu matematické funkce PI(π) | =PI() |
| 6 | POWER | Math & Trig |
Vrátí výsledek čísla umocněného na mocninu. POWER( číslo, síla ) |
=POWER(2,4;XNUMX) |
| 7 | MOD | Math & Trig | Při dělení dvou čísel vrátí zbytek | =MOD(10,3;XNUMX) |
| 8 | ROMAN | Math & Trig | Převede číslo na římské číslice | =ROMAN(1984) |
Řetězcové funkce
Tyto základní funkce Excelu se používají k manipulaci s textovými daty. Následující tabulka ukazuje některé běžné funkce řetězce.
| S / N | FUNKCE | KATEGORIE | POPIS | POUŽITÍ | JAK |
|---|---|---|---|---|---|
| 1 | LEFT | Text | Vrátí počet zadaných znaků od začátku (levé strany) řetězce | =LEFT(“GURU99”;4) | Levé 4 znaky „GURU99“ |
| 2 | PRÁVO | Text | Vrátí počet zadaných znaků z konce (pravé strany) řetězce | =RIGHT(“GURU99”;2) | Pravé 2 znaky „GURU99“ |
| 3 | MID | Text |
Načte počet znaků ze středu řetězce ze zadané počáteční pozice a délky. =MID (text, start_num, num_chars) |
=MID(“GURU99“;2,3;XNUMX) | Načítání znaků 2 až 5 |
| 4 | ISTEXT | Informace | Vrátí hodnotu True, pokud je zadaný parametr Text | =ISTEXT(hodnota) | hodnota – Hodnota, která se má zkontrolovat. |
| 5 | HLEDAT | Text |
Vrátí počáteční pozici textového řetězce v rámci jiného textového řetězce. Tato funkce rozlišuje malá a velká písmena. =FIND(najít_text, v_textu, [počáteční_číslo]) |
=FIND("oo","Střešní krytina",1) | Najděte oo v „Střešní krytina“, výsledek je 2 |
| 6 | VÝMĚNA | Text |
Nahradí část řetězce jiným zadaným řetězcem. =REPLACE (starý_text, start_num, num_chars, new_text) |
=REPLACE(“Střešní krytina”,2,2,”xx”) | Nahraďte „oo“ za „xx“ |
Funkce data a času
Tyto funkce se používají k manipulaci s hodnotami data. Následující tabulka ukazuje některé běžné funkce data
| S / N | FUNKCE | KATEGORIE | POPIS | POUŽITÍ |
|---|---|---|---|---|
| 1 | DATA | Datum Čas | Vrátí číslo, které představuje datum v kódu aplikace Excel | = DATUM (2015,2,4) |
| 2 | DNY | Datum Čas | Najděte počet dní mezi dvěma daty | =DAYS(D6,C6) |
| 3 | MĚSÍC | Datum Čas | Vrátí měsíc z hodnoty data | =MONTH(“4/2/2015”) |
| 4 | MINUTE | Datum Čas | Vrátí minuty z časové hodnoty | =MINUTE(“12:31”) |
| 5 | ROK | Datum Čas | Vrátí rok z hodnoty data | =YEAR(“04/02/2015”) |
Funkce VLOOKUP
Jedno Funkce VLOOKUP se používá k provedení vertikálního vyhledávání ve sloupci nejvíce vlevo a vrácení hodnoty ve stejném řádku ze sloupce, který zadáte. Pojďme si to vysvětlit laickým jazykem. 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. Zde je návod, jak 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, kterou má vrátit funkce SVYHLEDAT"FALSE,"říká funkci VLOOKUP, že hledáme přesnou shodu zadané hodnoty vyhledávání
Animovaný obrázek níže to ukazuje v akci
Stáhněte si výše uvedený kód Excel
Shrnutí
Excel umožňuje manipulovat s daty pomocí vzorců a/nebo funkcí. Funkce jsou obecně produktivnější ve srovnání s psaním vzorců. Funkce jsou také přesnější ve srovnání se vzorci, 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)
- Funkce PRŮMĚR =
=AVERAGE(E4:E8)
- Funkce COUNT =
=COUNT(E4:E8)
- Funkce DAYS =
=DAYS(D4,C4)
- Funkce VLOOKUP =
=VLOOKUP (C12, A4:B8, 2, FALSE)
- Funkce DATE =
=DATE(2020,2,4)
