Excel vzorce a Funkcie: Učte sa so základnými PRÍKLADMI

Obsah:

Anonim

Vzorce a funkcie sú základnými prvkami práce s číselnými údajmi v programe Excel. Tento článok vám predstaví vzorce a funkcie.

V tomto článku sa budeme venovať nasledujúcim témam.

  • Čo sú vzorce v programe Excel?
  • Chyby, ktorým sa treba vyhnúť pri práci so vzorcami v programe Excel
  • Čo je funkcia v programe Excel?
  • Dôležitosť funkcií
  • Spoločné funkcie
  • Numerické funkcie
  • Funkcie reťazca
  • Funkcie dátumu a času
  • V Funkcia vyhľadávania

Údaje o výučbe

V tomto výučbe budeme pracovať s nasledujúcimi množinami údajov.

Rozpočet na dodávky domov

S / N POLOŽKA POČET CENA SPOLU Je to dostupné?
1 Manga 9 600
2 Pomaranče 3 1200
3 Paradajky 1 2 500
4 Stolový olej 5 6500
5 Tonická voda 13 3900

Časový plán projektu výstavby domu

S / N POLOŽKA DÁTUM ZAČIATKU KONIEC DATA DURATION (DAYS)
1 Prieskum pôdy 04/02/2015 07/02/2015
2 Laický základ 10.02.2015 15/02/2015
3 Strešná krytina 27/02/2015 03.03.2015
4 Maľba 09/03/2015 21/03/2015

Čo sú vzorce v programe Excel?

FORMULAS IN EXCEL je výraz, ktorý pracuje s hodnotami v rozsahu adries buniek a operátorov. Napríklad = A1 + A2 + A3, ktorý nájde súčet rozsahu hodnôt od bunky A1 do bunky A3. Príklad vzorca zloženého z diskrétnych hodnôt ako = 6 * 3.

=A2 * D2 / 2

TU,

  • povie programu Excel, že ide o vzorec, a mal by ho vyhodnotiť.
  • "A2" * D2" odkazuje na adresy buniek A2 a D2 a potom vynásobí hodnoty nájdené v týchto adresách buniek.
  • "/" je deliaci aritmetický operátor
  • "2" je diskrétna hodnota

Praktické cvičenie vzorcov

Na výpočte medzisúčtu budeme pracovať so vzorovými údajmi pre domáci rozpočet.

  • Vytvorte nový zošit v programe Excel
  • Zadajte údaje zobrazené v rozpočte na domáce potreby vyššie.
  • Váš pracovný hárok by mal vyzerať nasledovne.

Teraz napíšeme vzorec, ktorý počíta medzisúčet

Zaostrite na bunku E4

Zadajte nasledujúci vzorec.

=C4*D4

TU,

  • "C4*D4" používa násobenie aritmetického operátora (*) na vynásobenie hodnoty adresy bunky C4 a D4.

Stlačte kláves Enter

Získate nasledujúci výsledok

Nasledujúci animovaný obrázok ukazuje, ako automaticky vybrať adresu bunky a použiť rovnaký vzorec na ďalšie riadky.

Chyby, ktorým sa treba vyhnúť pri práci so vzorcami v programe Excel

  1. Pamätajte na pravidlá v zátvorkách rozdelenia, násobenia, sčítania a odčítania (BODMAS). To znamená, že výrazy v zátvorkách sa vyhodnotia ako prvé. Pre aritmetické operátory sa najskôr vyhodnotí rozdelenie, potom násobenie, až potom sčítanie a odčítanie. Pomocou tohto pravidla môžeme vyššie uvedený vzorec prepísať na = (A2 * D2) / 2. Tým sa zabezpečí, že A2 a D2 sa najskôr vyhodnotia a potom sa vydelia dvoma.
  2. Tabuľkové vzorce programu Excel zvyčajne pracujú s číselnými údajmi; môžete využiť validáciu údajov na určenie typu údajov, ktoré by mala bunka akceptovať, tj. iba čísla.
  3. Ak sa chcete ubezpečiť, že pracujete so správnymi adresami buniek, na ktoré sa odkazuje vo vzorcoch, môžete stlačiť kláves F2 na klávesnici. Týmto zvýrazníte adresy buniek použité vo vzorci a môžete krížovou kontrolou skontrolovať, či ide o požadované adresy buniek.
  4. Ak pracujete s mnohými riadkami, môžete použiť poradové čísla pre všetky riadky a v dolnej časti hárka mať počet záznamov. Mali by ste porovnať počet sériových čísel s celkovým počtom záznamov, aby ste sa uistili, že vaše vzorce zahŕňali všetky riadky.

Vyskúšajte 10 najlepších vzorcov tabuľky Excel

Čo je funkcia v programe Excel?

FUNKCIA V EXCELE je preddefinovaný vzorec, ktorý sa používa pre konkrétne hodnoty v konkrétnom poradí. Funkcia sa používa na rýchle úlohy, ako je hľadanie súčtu, počtu, priemeru, maximálnej hodnoty a minimálnych hodnôt pre rad buniek. Napríklad bunka A3 nižšie obsahuje funkciu SUM, ktorá počíta súčet rozsahu A1: A2.

  • SUM pre súčet rozsahu čísel
  • PRIEMER na výpočet priemeru daného rozsahu čísel
  • COUNT pre spočítanie počtu položiek v danom rozsahu

Dôležitosť funkcií

Funkcie zvyšujú produktivitu používateľov pri práci s programom Excel . Povedzme, že by ste chceli získať celkový súčet za vyššie uvedený rozpočet na dodávky domov. Aby ste to zjednodušili, môžete pomocou súčtu získať celkový súčet. Pomocou vzorca by ste museli postupne odkazovať na bunky E4 až E8. Museli by ste použiť nasledujúci vzorec.

= E4 + E5 + E6 + E7 + E8

S funkciou by ste vyššie uvedený vzorec napísali ako

=SUM (E4:E8)

Ako vidíte z vyššie uvedenej funkcie použitej na získanie súčtu rozsahu buniek, je oveľa efektívnejšie použiť funkciu na získanie súčtu ako pomocou vzorca, ktorý bude musieť odkazovať na veľa buniek.

Spoločné funkcie

Pozrime sa na niektoré z najčastejšie používaných funkcií vo vzorcoch ms excel. Začneme štatistickými funkciami.

S / N FUNKCIA KATEGÓRIA POPIS POUŽITIE
01 SUM Math & Trig Pridá všetky hodnoty do rozsahu buniek = SUM (E4: E8)
02 MIN Štatistické Nájde minimálnu hodnotu v rozsahu buniek = MIN (E4: E8)
03 MAX Štatistické Nájde maximálnu hodnotu v rozsahu buniek = MAX (E4: E8)
04 PRIEMER Štatistické Vypočíta priemernú hodnotu v rozsahu buniek = PRIEMERNÝ (E4: E8)
05 COUNT Štatistické Spočítava počet buniek v rozsahu buniek = COUNT (E4: E8)
06 LEN Text Vráti počet znakov v texte reťazca = LEN (B7)
07 SUMIF Math & Trig Pridá všetky hodnoty do rozsahu buniek, ktoré zodpovedajú zadaným kritériám. = SUMIF (rozsah, kritériá, [sum_range]) = SUMIF (D4: D8, "> = 1000", C4: C8)
08 AVERAGEIF Štatistické Vypočíta priemernú hodnotu v rozsahu buniek, ktoré spĺňajú zadané kritériá. = AVERAGEIF (rozsah, kritériá, [priemerný_rozsah]) = AVERAGEIF (F4: F8, "Áno", E4: E8)
09 DNI Dátum Čas Vráti počet dní medzi dvoma dátumami = DNI (D4, C4)
10 TERAZ Dátum Čas Vráti aktuálny systémový dátum a čas = TERAZ ()

Numerické funkcie

Ako naznačuje názov, tieto funkcie pracujú s číselnými údajmi. Nasledujúca tabuľka zobrazuje niektoré z bežných numerických funkcií.

S / N FUNKCIA KATEGÓRIA POPIS POUŽITIE
1 ČÍSLO Informácie Vráti hodnotu True, ak je zadaná hodnota numerická, a hodnotu False, ak nie je numerická = ISNUMBER (A3)
2 RAND Math & Trig Generuje náhodné číslo medzi 0 a 1 = RAND ()
3 OKRÚHLY Math & Trig Zaokrúhli desatinnú hodnotu na zadaný počet desatinných miest = KOLO (3,14455,2)
4 MEDIAN Štatistické Vráti číslo v strede množiny daných čísel = STREDNÉ (3,4,5,2,5)
5 PI Math & Trig Vráti hodnotu matematickej funkcie PI (π) = PI ()
6 MOC Math & Trig Vráti výsledok čísla zvýšeného na mocninu. VÝKON (číslo, výkon) = VÝKON (2,4)
7 MOD Math & Trig Vráti Zvyšok, keď rozdelíte dve čísla = MOD (10,3)
8 RÍMSKY Math & Trig Prevedie číslo na rímske číslice = RÍM (1984)

Funkcie reťazca

Tieto základné funkcie programu Excel sa používajú na manipuláciu s textovými údajmi. V nasledujúcej tabuľke sú uvedené niektoré bežné funkcie reťazcov.

S / N FUNKCIA KATEGÓRIA POPIS POUŽITIE KOMENTÁRE
1 VĽAVO Text Vráti počet zadaných znakov od začiatku (ľavej strany) reťazca = DOĽAVA („GURU99“, 4) Vľavo 4 znaky „GURU99“
2 SPRÁVNY Text Vráti počet zadaných znakov od konca (pravej strany) reťazca = VPRAVO ("GURU99", 2) Pravé 2 znaky „GURU99“
3 MID Text Načíta počet znakov zo stredu reťazca zo zadanej počiatočnej polohy a dĺžky. = MID (text, start_num, num_chars) = MID ("GURU99", 2,3) Načítanie znakov 2 až 5
4 ISTEXT Informácie Vráti hodnotu True, ak je zadaným parametrom Text = ISTEXT (hodnota) hodnota - hodnota, ktorá sa má skontrolovať.
5 NÁJSŤ Text Vráti začiatočnú pozíciu textového reťazca v rámci iného textového reťazca. Táto funkcia rozlišuje veľké a malé písmená. = HĽADAŤ (find_text, within_text, [start_num]) = NÁJDI ("oo", "strešná krytina", 1) Vyhľadajte oo v sekcii Zastrešenie, výsledok je 2
6 VYMENIŤ Text Nahradí časť reťazca iným zadaným reťazcom. = REPLACE (old_text, start_num, num_chars, new_text) = REPLACE ("Strešná krytina", 2,2, "xx") Nahradiť „oo“ výrazom „xx“

Funkcie dátumu a času

Tieto funkcie sa používajú na manipuláciu s hodnotami dátumu. V nasledujúcej tabuľke sú uvedené niektoré z bežných funkcií dátumu

S / N FUNKCIA KATEGÓRIA POPIS POUŽITIE
1 DÁTUM Dátum Čas Vráti číslo, ktoré predstavuje dátum v kóde programu Excel = DÁTUM (2015,2,4)
2 DNI Dátum Čas Nájdite počet dní medzi dvoma dátumami = DNI (D6, C6)
3 MESIAC Dátum Čas Vráti mesiac z hodnoty dátumu = MESIAC („2. 4. 2015“)
4 MINUTA Dátum Čas Vráti minúty z časovej hodnoty = MINUTE ("12:31")
5 ROK Dátum Čas Vráti rok z hodnoty dátumu = YEAR („04/02/2015“)

Funkcia VLOOKUP

Funkcia VLOOKUP sa používa na vertikálne vyhľadanie v ľavom stĺpci a na vrátenie hodnoty v rovnakom riadku zo zadaného stĺpca. Poďme si to vysvetliť laicky. Rozpočet domácich potrieb má stĺpec s poradovým číslom, ktorý jednoznačne identifikuje každú položku v rozpočte. Predpokladajme, že máte sériové číslo položky a chcete vedieť jej popis, môžete použiť funkciu VLOOKUP. Takto by fungovala funkcia VLOOKUP.

=VLOOKUP (C12, A4:B8, 2, FALSE)

TU,

  • "=VLOOKUP" volá funkciu vertikálneho vyhľadávania
  • "C12" určuje hodnotu, ktorá sa má vyhľadať v stĺpci úplne vľavo
  • "A4:B8" určuje pole tabuľky s údajmi
  • "2" určuje číslo stĺpca s hodnotou riadku, ktorá sa má vrátiť funkciou VLOOKUP
  • "FALSE," povie funkcii VLOOKUP, že hľadáme presnú zhodu dodanej vyhľadávacej hodnoty

Animovaný obrázok uvedený nižšie to ukazuje v akcii

Stiahnite si vyššie uvedený kód Excel

Zhrnutie

Excel umožňuje manipulovať s údajmi pomocou vzorcov a / alebo funkcií. Funkcie sú vo všeobecnosti produktívnejšie v porovnaní s písaním vzorcov. Funkcie sú tiež presnejšie v porovnaní so vzorcami, pretože miera chybovosti je veľmi minimálna.

Tu je zoznam dôležitých vzorcov a funkcií programu Excel

  • Funkcia SUM = =SUM(E4:E8)
  • Funkcia MIN = =MIN(E4:E8)
  • Funkcia MAX = =MAX(E4:E8)
  • PRIEMERNÁ funkcia = =AVERAGE(E4:E8)
  • COUNT funkcia = =COUNT(E4:E8)
  • Funkcia DAYS = =DAYS(D4,C4)
  • Funkcia VLOOKUP = =VLOOKUP (C12, A4:B8, 2, FALSE)
  • Funkcia DATE = =DATE(2020,2,4)