Čo sú vzorce a funkcie v programe Excel a ako ich používať?



Vzorce a funkcie programu Excel sú stavebnými kameňmi na správu údajov. Naučte sa, ako písať, kopírovať / vkladať, skryť vzorce. IF, COUNTIF, SUM, DATE, RANK, INDEX, FV, ROUND atď.

Údaje sa dajú použiť, iba ak na nich skutočne môžete pracovať a Excel je jeden nástroj, ktorý poskytuje veľké množstvo pohodlia, keď potrebujete formulovať vlastné rovnice alebo používať vstavané rovnice. V tomto článku sa dozviete, ako môžete s týmito funkciami reklamy so vzorcami programu Excel skutočne pracovať.

Tu je rýchly prehľad tém, o ktorých sa tu diskutuje:





Čo je vzorec?

Všeobecne je vzorec zhustený spôsob predstavovania niektorých informácií v podobe symbolov. V programe Excel sú vzorce výrazy, ktoré je možné zadať do buniek hárka programu Excel, a ako výsledok sa zobrazia ich výstupy.

Vzorce programu Excel môžu byť z nasledujúcich typov:



Typ

Príklad

Popis



Matematické operátory (+, -, * atď.)

Príklad: = A1 + B1

Sčíta hodnoty A1 a B1

Hodnoty alebo text

Príklad: 100 * 0,5 násobky 100 krát 0,5

Berie hodnoty a vracia výstup

Odkaz na bunku

ako napísať skener v

PRÍKLAD: = A1 = B1

Vráti hodnotu TRUE alebo FALSE porovnaním skupín A1 a B1

Funkcie pracovného hárka

PRÍKLAD: = SUMA (A1: B1)

Vráti výstup pridaním hodnôt prítomných v A1 a B1

Písanie vzorcov programu Excel:

Ak chcete napísať vzorec do bunky hárka programu Excel, môžete postupovať takto:

  • Vyberte bunku, kde chcete zobraziť výsledok
  • Najprv napíšte znak „=“, aby program Excel vedel, že do tejto bunky zadáte vzorec
  • Potom môžete zadať adresy buniek alebo určiť hodnoty, ktoré chcete vypočítať
  • Napríklad ak chcete pridať hodnoty dvoch buniek, môžete zadať adresu bunky takto:

zadajte vzorec-Excel vzorce-Edureka

  • Môžete tiež vybrať bunky, ktorých súčet chcete vypočítať, a to tak, že vyberiete všetky požadované bunky a súčasne stlačte kláves Ctrl:


Úprava vzorca:

Ak chcete upraviť niektorý predtým zadaný vzorec, jednoducho vyberte bunku, ktorá obsahuje cieľový vzorec, a na paneli vzorcov môžete vykonať požadované zmeny. V predchádzajúcom príklade som vypočítal Súčet A1 a A2. Teraz upravím to isté a zmením vzorec na výpočet súčinu hodnôt prítomných v týchto dvoch bunkách:


Po dokončení stlačte požadovaný kláves Enter a zobrazte požadovaný výstup.

Kopírovanie alebo vloženie vzorca:

Excel je skutočne užitočný, keď musíte kopírovať / vkladať vzorce. Kedykoľvek kopírujete vzorec, Excel sa automaticky postará o odkazy na bunky, ktoré sú na danom mieste požadované. To sa deje prostredníctvom systému s názvom ako Relatívne adresy buniek .

Ak chcete skopírovať vzorec, vyberte bunku, ktorá obsahuje pôvodný vzorec, a potom ju presuňte do bunky, ktorá vyžaduje kópiu tohto vzorca, a to nasledovne:

Ako vidíte na obrázku, vzorec je napísaný pôvodne v A3 a potom som ho pretiahol pozdĺž B3 a C3, aby som vypočítal súčet B1, B2 a C1, C2 bez výhradného zapísania adries buniek.

V prípade, že zmením hodnoty ktorejkoľvek z buniek, bude výstup automaticky aktualizovaný programom Excel v súlade s Relatívne adresy buniek , Absolútne adresy buniek alebo Zmiešané adresy buniek .

Skryť vzorce v programe Excel:

Ak chcete skryť nejaký vzorec z hárka programu Excel, môžete to urobiť nasledovne:

  • Vyberte bunky, ktorých vzorec chcete skryť
  • Otvorte okno Písmo a vyberte podokno Ochrana
  • Začiarknite políčko Skryté a kliknite na tlačidlo OK
  • Potom na karte s páskou vyberte možnosť Skontrolovať
  • Kliknite na hárok Chrániť (ak to neurobíte, vzorce nebudú fungovať)
  • Excel vás požiada o zadanie hesla, aby sa vzorce pre ďalšie použitie odkryli

Prednosť operátora vzorcov programu Excel:

Vzorce programu Excel sa riadia pravidlami BODMAS (Oddelenia násobenia sčítania v zátvorkách). Ak máte vzorec, ktorý obsahuje zátvorky, bude výraz v zátvorkách vyriešený pred akoukoľvek inou časťou úplného vzorca. Pozrite sa na obrázok nižšie:

Ako vidíte na vyššie uvedenom príklade, mám vzorec, ktorý sa skladá zo zátvorky. Takže v súlade s pravidlami BODMAS Excel najskôr nájde rozdiel medzi A2 a B1 a potom pridá výsledok s A1.

Čo sú „Funkcie“ v programe Excel?

Funkcia vo všeobecnosti definuje vzorec, ktorý sa vykoná v danom poradí. Excel poskytuje obrovské množstvo vstavané funkcie ktoré možno použiť na výpočet výsledku rôznych vzorcov.

Vzorce v programe Excel sú rozdelené do nasledujúcich kategórií:

KategórieDôležité vzorce

Dátum Čas

DÁTUM, DEŇ, MESIAC, HODINA atď

Finančné

ACCI, accinto, Dollard, INTRAATE atď

Math & Trig

SUM, SUMIF, PRODUKT, SIN, COS atď

Štatistické

PRIEMER, COUNT, COUNTIF, MAX, MIN atď

Vyhľadanie a referencia

COLUMN, HLOOKUP, ROW, VLOOKUP, CHOOSE atď

Databáza

DAVERAGE, DCOUNT, DMIN, DMAX atď

Text

BAHTTEXT, DOLÁR, DOLNÝ, HORNÝ atď

Logické

AND, OR, NOT, IF, TRUE, FALSE, etc.

Informácie

INFO, CHYBA.TYP, TYP, ISERROR atď

Strojárstvo

KOMPLEXNÝ, PREVOD, DELTA, OCT2BIN atď

Kocka

CUBESET, CUBENUMBER, CUBEVALUE atď

Kompatibilita

PERCENTILNÉ, PORADNÉ, VARNÉ, REŽIM atď

Web

ENCODEURL, FILTERXML, WEBOVÝ SERVIS

Teraz sa pozrime, ako využiť niektoré z najdôležitejších a najčastejšie používaných vzorcov programu Excel.

Najdôležitejšie funkcie programu Excel:

Tu uvádzame niektoré z najdôležitejších funkcií Excelu spolu s ich popismi a príkladmi.

DÁTUM:

Jednou z najdôležitejších a najbežnejšie používaných funkcií dátumu v programe Excel je funkcia DATE. Jeho syntax je nasledovná:

DATE (rok, mesiac, deň)

Táto funkcia vráti číslo, ktoré predstavuje daný dátum vo formáte dátumu a času v programe MS Excel. Funkciu DATE je možné použiť nasledovne:

PRÍKLAD:

  1. = DÁTUM (2019,11,7)
  2. = DATE (2019,11,7) -7 (vráti aktuálny dátum - sedem dní)

DEŇ:

Táto funkcia vracia hodnotu dňa v mesiaci (1 - 31). Jeho syntax je nasledovná:

DEŇ (poradové_číslo)

Poradové_číslo je dátum, ktorého deň chcete načítať. Môže byť daná akýmkoľvek spôsobom, napríklad výsledkom nejakej inej funkcie dodanej funkciou DATE alebo odkazom na bunku.

PRÍKLAD:

  1. = DEŇ (A7)
  2. = DEŇ (DNES ())
  3. = DEŇ (DATE (2019; 11,8))

MESIAC:

Rovnako ako funkcia DEN, program Excel poskytuje aj inú funkciu, t. J. Funkciu MESIAC, ktorá umožňuje načítať mesiac od určitého dátumu. Syntax je nasledovná:

MONTH (poradové_číslo)

PRÍKLAD:

  1. = MESIAC (DNES ())
  2. = MESIAC (DÁTUM (2019; 11,8))

Percento:

Ako všetci vieme, percento je pomer vypočítaný ako zlomok 100. Môže byť označený takto:

Percento = (časť / celé) x 100

V programe Excel môžete vypočítať percento všetkých požadovaných hodnôt. Napríklad ak máte časti a celé hodnoty prítomné v A1 a A2 a chcete vypočítať percento, môžete to urobiť nasledovne:

  • Vyberte bunku, kde chcete zobraziť výsledok
  • Zadajte znak „=“
  • Potom zadajte vzorec ako A1 / A2 a stlačte kláves Enter
  • V skupine čísel domovskej karty vyberte symbol „%“

AK:

Príkaz IF je podmienený príkaz, ktorý vráti True, ak je zadaná podmienka splnená, a Flase, ak podmienka nie je. Excel poskytuje zabudovanú funkciu „IF“, ktorá slúži tomuto účelu. Jeho syntax je nasledovná:

AK (logický_test, hodnota_k_pravdivosti, hodnota_k_false)

Tu, logický_test je podmienka, ktorá sa má skontrolovať

PRÍKLAD:

  • Zadajte hodnoty, ktoré sa majú porovnať
  • Vyberte bunku, ktorá bude zobrazovať výstup
  • Zadajte do riadku vzorcov „= IF (A1 = A2,„ Áno “,„ Nie “)“ a stlačte kláves Enter

ZOBRAZENIE:

Táto funkcia sa používa na vyhľadanie a načítanie konkrétnych údajov zo stĺpca z hárka programu Excel. „V“ vo VLOOKUP znamená vertikálne vyhľadávanie. Je to jeden z najdôležitejších a najbežnejšie používaných vzorcov v programe Excel a aby bolo možné túto funkciu použiť, musí byť tabuľka zoradená vzostupne. Syntax tejto funkcie je nasledovná:

VLOOKUP (lookup_value, table_array, col_index, num_range, lookup)

kde,

vyhľadávacia_hodnota je hodnota, ktorá sa má vyhľadať

table_array je tabuľka, ktorá sa má prehľadať

col_index je stĺpec, z ktorého sa má hodnota načítať

range_lookup (voliteľné) vráti TRUE asi a FALSE pre presnú zhodu

PRÍKLAD:

Ako vidíte na obrázku, hodnota, ktorú som zadal, je 2 a rozsah tabuľky je medzi A1 a D4. Chcem načítať meno zamestnanca, preto som dal hodnotu stĺpca ako 2 a keďže chcem, aby to bola presná zhoda, použil som False na vyhľadanie rozsahu.

Daň z príjmu:

Predpokladajme, že chcete vypočítať daň z príjmu osoby, ktorej celková mzda je 300 dolárov. Daň z príjmu budete musieť vypočítať takto:

  • Uveďte celkový plat, odpočty platov, zdaniteľný príjem a percento dane z príjmu
  • Zadajte hodnoty celkového platu, odpočtov platu
  • Potom vypočítajte zdaniteľný príjem nájdením rozdielu medzi celkovým platom a odpočítaním platu
  • Na záver vypočítajte sumu dane

SUM:

Funkcia SUM v programe Excel počíta výsledok pridaním všetkých určených hodnôt v programe Excel. Syntax tejto funkcie je nasledovná:

SUMA (číslo1, číslo2, ...)

Pridá k nej všetky čísla, ktoré sú určené ako parameter.

PRÍKLAD:

Ak chcete vypočítať súčet sumy, ktorú ste minuli na nákup zeleniny, uveďte všetky ceny a potom použite nasledujúci vzorec SUM:

Zložený úrok:

Na výpočet zloženého úroku môžete použiť jeden z excelových vzorcov s názvom FV. Táto funkcia vráti budúcu hodnotu investície na základe pravidelných, konštantných úrokových sadzieb a platieb. Syntax tejto funkcie je nasledovná:

FV (sadzba, nper, pmt, pv, typ)

Aby ste mohli vypočítať sadzbu, budete musieť ročnú sadzbu vydeliť počtom období, t. J. Ročnou sadzbou / obdobiami. Počet období alebo nper sa počíta vynásobením termínu (počtu rokov) obdobiami, tj. Termínom * obdobia. pmt znamená periodické platby a môže mať akúkoľvek hodnotu vrátane nuly.

Uvažujme o nasledujúcom príklade:

Vo vyššie uvedenom príklade som vypočítal zložený úrok na 500 dolárov so sadzbou 10% na 5 rokov a za predpokladu, že hodnota pravidelnej platby je 0. Upozorňujeme, že som použil -B1, čo znamená, že mi bolo odobratých 500 dolárov.

Priemer:

Priemer, ako všetci vieme, predstavuje strednú hodnotu niekoľkých hodnôt. V programe Excel možno priemer ľahko vypočítať pomocou vstavanej funkcie zvanej „PRIEMER“. Syntax tejto funkcie je nasledovná:

PRIEMER (číslo1, číslo2, ...)

PRÍKLAD:

Ak chcete vypočítať priemerné známky, ktoré dosiahli študenti pri všetkých skúškach, môžete jednoducho vytvoriť tabuľku a potom pomocou vzorca PRIEMER vypočítať priemerné známky, ktoré dosiahol každý študent.

V uvedenom príklade som vypočítal priemerné známky pre dvoch študentov na dvoch skúškach. Ak máte viac ako dve hodnoty, ktorých priemer je potrebné určiť, stačí zadať rozsah buniek, v ktorých sú hodnoty prítomné. Napríklad:

COUNT:

Funkcia počítania v programe Excel spočíta počet buniek obsahujúcich čísla v danom rozsahu. Syntax tejto funkcie je nasledovná:

COUNT (hodnota1, hodnota2, ...)

PRÍKLAD:

V prípade, že chcem vypočítať počet buniek obsahujúcich čísla z tabuľky, ktorú som vytvoril v predchádzajúcom príklade, budem musieť jednoducho vybrať bunku, v ktorej chcem zobraziť výsledok, a potom nasledovne použiť funkciu COUNT:

OKRÚHLY:

Na zaokrúhlenie hodnôt na niektoré konkrétne desatinné miesta môžete použiť funkciu ROUND. Táto funkcia vráti číslo zaokrúhlením na zadaný počet desatinných miest. Syntax tejto funkcie je nasledovná:

KOLO (počet, počet číslic)

PRÍKLAD:

Nájdenie stupňa:

Ak chcete nájsť známky, budete musieť vnorené príkazy IF použiť v programe Excel. Napríklad v príklade Priemer som vypočítal priemerné známky, ktoré dosiahli študenti v testoch. Teraz, aby som našiel známky, ktoré dosiahli títo študenti, budem musieť vytvoriť vnorenú funkciu IF nasledovne:

Ako vidíte, priemerné známky sa nachádzajú v stĺpci G. Na výpočet známky som použil vnorený vzorec IF. Kód je nasledovný:

= IF (G19> 90, „A“, (IF (G19> 75, „B“, IF (G19> 60, „C“, IF (G19> 40, „D“, „F“)))))

Potom to budete musieť skopírovať vzorec do všetkých buniek, kde chcete zobraziť známky.

PORADIE:

Ak chcete určiť hodnotenie dosiahnuté študentmi triedy, môžete použiť jeden zo vstavaných vzorcov programu Excel, tj. RANK. Táto funkcia vráti hodnotenie pre zadaný rozsah porovnaním daného rozsahu vo vzostupnom alebo zostupnom poradí. Syntax tejto funkcie je nasledovná:

RANK (ref, number, order)

PRÍKLAD:

Ako vidíte, vo vyššie uvedenom príklade som pomocou funkcie Rank vypočítal Rank študentov. Tu je prvým parametrom priemerná známka, ktorú dosiahol každý študent, a pole je priemernou známkou, ktorú dosiahli všetci ostatní študenti triedy. Nezadal som žiadne poradie, preto bude výstup určený v zostupnom poradí. Pri vzostupnom poradí budete musieť zadať nenulovú hodnotu.

COUNTIF:

Ak chcete počítať bunky na základe danej podmienky, môžete použiť jeden zo vstavaných vzorcov programu Excel s názvom „COUNTIF“. Táto funkcia vráti počet buniek, ktoré vyhovujú niektorej podmienke v danom rozsahu. Syntax tejto funkcie je nasledovná:

COUNTIF (rozsah, kritériá)

PRÍKLAD:

Ako vidíte, vo vyššie uvedenom príklade som našiel počet buniek s hodnotami, ktoré sú väčšie ako 80. Parametrom kritéria môžete dať aj určitú textovú hodnotu.

INDEX:

Funkcia INDEX vráti hodnotu alebo odkaz na bunku na určitej konkrétnej pozícii v určenom rozsahu. Syntax tejto funkcie je nasledovná:

INDEX (pole, číslo_riadku, číslo_stĺpca) alebo

INDEX (referencia,číslo riadku, číslo stĺpca, číslo oblasti)

čo je nemenný objekt v jave

Funkcia indexu funguje nasledovne pre Pole forma:

  • Ak je zadané číslo riadku aj číslo stĺpca, vráti hodnotu, ktorá je prítomná v bunke križovatky
  • Ak je hodnota riadku nastavená na nulu, vráti hodnoty prítomné v celom stĺpci v určenom rozsahu
  • Ak je hodnota stĺpca nastavená na nulu, vráti hodnoty prítomné v celom riadku v určenom rozsahu

Funkcia indexu funguje nasledovne pre Odkaz forma:

  • Vráti odkaz na bunku, kde sa pretínajú hodnoty riadku a stĺpca
  • Area_num bude označovať, ktorý rozsah sa má použiť v prípade, že bolo zadaných viac rozsahov

PRÍKLAD:

Ako vidíte, vo vyššie uvedenom príklade som pomocou funkcie INDEX určil hodnotu prítomnú v 2. riadku a 4. stĺpci pre rozsah buniek medzi A18 až G20.

Podobne môžete tiež použiť funkciu INDEX zadaním viacerých odkazov nasledovne:

Týmto sa dostávame na koniec tohto článku o vzorcoch a funkciách programu Excel. Dúfam, že máte jasno vo všetkom, čo bolo s vami zdieľané. Určite cvičte čo najviac a obráťte sa na svoje skúsenosti.

Máte na nás otázku? Uveďte to prosím v sekcii komentárov v tomto blogu „Vzorce a funkcie programu Excel“ a my sa vám ozveme čo najskôr.

Ak sa chcete dozvedieť viac o akýchkoľvek trendových technológiách a ich rôznych aplikáciách, môžete sa zaregistrovať naživo s nepretržitou podporou a doživotným prístupom.