Kas yra „Excel“ formulės ir funkcijos ir kaip jas naudoti?



„Excel“ formulės ir funkcijos yra duomenų valdymo elementai. Sužinokite, kaip rašyti, kopijuoti / įklijuoti, slėpti formules. IF, COUNTIF, SUM, DATE, RANK, INDEX, FV, ROUND ir kt.

Duomenys naudojami tik tada, kai jūs iš tikrųjų galite dirbti su jais ir „Excel“ yra vienas įrankis, suteikiantis daug patogumo tiek tada, kai reikia suformuluoti savo lygtis, tiek pasinaudoti įmontuotomis. Šiame straipsnyje sužinosite, kaip iš tikrųjų galite dirbti su šiomis „Excel“ formulių skelbimų funkcijomis.

Čia trumpai apžvelgiamos čia aptariamos temos:





Kas yra formulė?

Apskritai formulė yra sutrumpintas būdas pateikti tam tikrą informaciją simboliais. Programoje „Excel“ formulės yra išraiškos, kurias galima įvesti į „Excel“ lapo langelius, o jų rezultatai rodomi kaip rezultatas.

„Excel“ formulės gali būti šių tipų:



Tipas

Pavyzdys

apibūdinimas



Matematiniai operatoriai (+, -, * ir kt.)

Pavyzdys: = A1 + B1

Pridedamos A1 ir B1 vertės

Vertybės ar tekstas

Pavyzdys: 100 * 0,5 kartojasi 100 kartų 0,5

Paima reikšmes ir grąžina išvestį

Langelio nuoroda

PAVYZDYS: = A1 = B1

Pateikia TRUE arba FALSE, lygindami A1 ir B1

Darbalapio funkcijos

PAVYZDYS: = SUMA (A1: B1)

Grąžina išvestį pridedant reikšmes, esančias A1 ir B1

„Excel“ formulių rašymas:

Norėdami parašyti formulę į „Excel“ lapo langelį, galite padaryti taip:

  • Pasirinkite langelį, kuriame norite rodyti rezultatą
  • Iš pradžių įveskite „=“ ženklą, kad „Excel“ žinotų, kad į tą langelį įvesite formulę
  • Po to galite įvesti langelių adresus arba nurodyti vertes, kurias ketinate apskaičiuoti
  • Pvz., Jei norite pridėti dviejų langelių reikšmes, galite įvesti langelio adresą taip:

įveskite formulę-„Excel“ formules-„Edureka“

  • Taip pat galite pasirinkti langelius, kurių sumą norite apskaičiuoti, pasirinkdami visus reikalingus langelius ir paspausdami klavišą Ctrl:


Formulės redagavimas:

Jei norite redaguoti kai kurias anksčiau įvestas formules, tiesiog pasirinkite langelį, kuriame yra tikslinė formulė, ir formulės juostoje galite atlikti norimus pakeitimus. Ankstesniame pavyzdyje apskaičiavau A1 ir A2 sumą. Dabar aš redaguosiu tą patį ir pakeisiu formulę, kad apskaičiuočiau šių dviejų langelių verčių sandaugą:


Kai tai bus padaryta, paspauskite Enter, kad pamatytumėte norimą išvestį.

Nukopijuokite arba įklijuokite formulę:

„Excel“ yra labai naudinga, kai turite nukopijuoti / įklijuoti formules. Kai kopijuojate formulę, „Excel“ automatiškai pasirūpina langelių nuorodomis, kurių reikia toje vietoje. Tai daroma per sistemą, vadinamą kaip Santykiniai langelių adresai .

Norėdami nukopijuoti formulę, pasirinkite langelį, kuriame yra pradinė formulė, ir vilkite jį iki tos langelio, kuriam reikalinga šios formulės kopija, taip:

Kaip matote paveikslėlyje, formulė iš pradžių parašyta A3 formatu, tada aš ją tempiau žemyn palei B3 ir C3, kad apskaičiuočiau B1, B2 ir C1, C2 sumą, neužrašydamas vien langelių adresų.

Jei pakeisiu bet kurios langelio vertes, 'Excel' išvestį automatiškai atnaujins pagal Santykiniai langelių adresai , Absoliutūs langelių adresai arba Mišrių langelių adresai .

Slėpti „Excel“ formules:

Jei norite paslėpti kokią nors formulę iš „Excel“ lapo, galite tai padaryti taip:

  • Pasirinkite langelius, kurių formulę ketinate slėpti
  • Atidarykite langą Šriftas ir pasirinkite langą Apsauga
  • Pažymėkite parinktį Paslėptas ir spustelėkite Gerai
  • Tada juostos skirtuke pasirinkite Peržiūrėti
  • Spustelėkite Apsaugoti lapą (jei to nepadarysite, formulės neveiks)
  • „Excel“ paprašys įvesti slaptažodį, kad ateityje nebeslėptų formulių

„Excel“ formulių operatoriaus pirmenybė:

„Excel“ formulėse laikomasi BODMAS (skliausteliuose eilės skilties daugybos pridėjimo atimties) taisyklių. Jei turite formulę, kurioje yra skliausteliuose, skliaustuose esanti išraiška bus išspręsta prieš bet kurią kitą visos formulės dalį. Pažvelkite į žemiau esantį vaizdą:

Kaip matote aukščiau pateiktame pavyzdyje, turiu formulę, kurią sudaro skliaustas. Taigi pagal BODMAS taisykles „Excel“ pirmiausia ras skirtumą tarp A2 ir B1, o tada pridės rezultatą su A1.

ar „funkcijos“ yra „Excel“?

Apskritai funkcija apibrėžia formulę, kuri vykdoma tam tikra tvarka. „Excel“ teikia didžiulį skaičių įmontuotos funkcijos kuri gali būti naudojama apskaičiuojant įvairių formulių rezultatą.

„Excel“ formulės yra suskirstytos į šias kategorijas:

KategorijaSvarbios formulės

Data ir laikas

DATA, diena, mėnuo, valanda ir kt

Finansinis

ACCI, accinto, Dollard, INTRAATE ir kt

Matematika ir Trig

SUM, SUMIF, PRODUCT, SIN, COS ir kt

Statistiniai

AVERAGE, COUNT, COUNTIF, MAX, MIN ir kt

Ieškoti ir nuoroda

COLUMN, HLOOKUP, ROW, VLOOKUP, CHOOSE ir kt

Duomenų bazė

DAVERAGE, DCOUNT, DMIN, DMAX ir kt

Tekstas

BAHTTEXT, DOLLAR, LOWER, UPPER ir kt

Logiška

IR ARBA NE, JEIGA, TIKRA, NETIESA ir pan

Informacija

INFO, KLAIDA. TIPAS, TIPAS, KLAIDA IR kt

Inžinerija

COMPLEX, CONVERT, DELTA, OCT2BIN ir kt

kubas

CUBESET, CUBENUMBER, CUBEVALUE ir kt

Suderinamumas

PROCENTILAS, RANKAS, VAR, MODE ir kt

Žiniatinklis

egzemplioriaus kintamasis Java pavyzdyje
ENCODEURL, FILTERXML, WEBSERVICE

Dabar patikrinkime, kaip pasinaudoti kai kuriomis svarbiausiomis ir dažniausiai naudojamomis „Excel“ formulėmis.

Svarbiausios „Excel“ funkcijos:

Čia pateikiamos kelios svarbiausios „Excel“ funkcijos, jų aprašymai ir pavyzdžiai.

DATA:

Viena iš svarbiausių ir plačiausiai naudojamų „Excel“ datos funkcijų yra funkcija DATA. Jo sintaksė yra tokia:

DATA (metai, mėnuo, diena)

Ši funkcija pateikia skaičių, kuris nurodo nurodytą datą MS Excel datos ir laiko formatu. Funkciją DATA galima naudoti taip:

PAVYZDYS:

  1. = DATA (2019,11,7)
  2. = DATA (2019,11,7) -7 (pateikia dabartinę datą - septynios dienos)

DIENA:

Ši funkcija grąžina mėnesio dienos vertę (1-31). Jo sintaksė yra tokia:

DAY (serijos numeris)

Čia serial_number yra data, kurios dieną norite gauti. Jis gali būti pateiktas bet kokiu būdu, pavyzdžiui, kokios nors kitos funkcijos, kurią teikia funkcija DATE, rezultatu, arba langelio nuoroda.

PAVYZDYS:

  1. = DIENA (A7)
  2. = DIENA (ŠIANDIEN ())
  3. = DIENA (DATA (2019 m., 11,8))

MĖNESIS:

Kaip ir „DAY“ funkcija, „Excel“ teikia dar vieną funkciją, t. Y. MĖNESIO funkciją, kad būtų galima gauti mėnesį nuo konkrečios datos. Sintaksė yra tokia:

MONTH (serijos numeris)

PAVYZDYS:

  1. = MĖNESIS (ŠIANDIEN ())
  2. = MĖNESIS (DATA (2019 m., 11,8))

Procentai:

Kaip mes visi žinome, procentas yra santykis, apskaičiuotas kaip 100 dalis. Tai galima pažymėti taip:

Procentas = (dalis / visa) x 100

Programoje „Excel“ galite apskaičiuoti norimų reikšmių procentą. Pvz., Jei dalis ir visa reikšmė yra A1 ir A2 ir norite apskaičiuoti procentą, galite tai padaryti taip:

  • Pasirinkite langelį, kuriame norite rodyti rezultatą
  • Įveskite ženklą „=“
  • Tada įveskite formulę kaip A1 / A2 ir paspauskite Enter
  • Pagrindinio skirtuko numerių grupėje pasirinkite simbolį „%“

JEI:

IF sakinys yra sąlyginis sakinys, kuris grąžina „True“, kai įvykdoma nurodyta sąlyga, ir „Flase“, kai sąlyga nėra. „Excel“ teikia įmontuotą „IF“ funkciją, kuri tarnauja šiam tikslui. Jo sintaksė yra tokia:

IF (loginis_testas, reikšmės_tikroji_vertė, reikšmė_klaidinga)

Čia loginis_testas yra sąlyga, kurią reikia patikrinti

PAVYZDYS:

  • Įveskite lygintinas vertes
  • Pasirinkite langelį, kuriame bus rodoma išvestis
  • Į formulės juostą įveskite „= IF (A1 = A2,„ Taip “,„ Ne “) ir paspauskite„ Enter “

PERŽIŪRA:

Ši funkcija naudojama norint ieškoti ir gauti tam tikrus duomenis iš stulpelio iš „Excel“ lapo. VLOOKUP raidė „V“ reiškia vertikalią paiešką. Tai yra viena iš svarbiausių ir plačiausiai naudojamų „Excel“ formulių ir norint naudoti šią funkciją, lentelė turi būti rūšiuojama didėjimo tvarka. Šios funkcijos sintaksė yra tokia:

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

kur,

look_value yra vertė, kurios reikia ieškoti

table_array yra lentelė, kurios reikia ieškoti

rūšiavimo funkcija c ++

col_index yra stulpelis, iš kurio reikia gauti vertę

range_lookup (pasirenkama) grąžina TRUE maždaug. rungtynių ir FALSE tikslioms rungtynėms

PAVYZDYS:

Kaip matote paveikslėlyje, mano nurodyta vertė yra 2, o lentelės diapazonas yra tarp A1 ir D4. Noriu gauti darbuotojo vardą, todėl stulpelio vertę nurodiau kaip 2 ir kadangi noriu, kad tai būtų tiksli atitiktis, diapazono paieškai naudojau „False“.

Pajamų mokestis:

Tarkime, kad norite apskaičiuoti asmens, kurio bendras atlyginimas yra 300 USD, pajamų mokestį. Pelno mokestį turėsite apskaičiuoti taip:

  • Surašykite bendrą atlyginimą, atskaitymus iš atlyginimų, apmokestinamąsias pajamas ir pajamų mokesčio procentą
  • Nurodykite „Bendras atlyginimas“, „Atlyginimų atskaitymai“ vertes
  • Tada apskaičiuokite apmokestinamąsias pajamas, surasdami skirtumą tarp visų atlyginimų ir atlyginimų atskaitymų
  • Galiausiai apskaičiuokite mokesčio sumą

SUMA:

„Excel“ funkcija SUM apskaičiuoja rezultatą pridėdama visas nurodytas „Excel“ reikšmes. Šios funkcijos sintaksė yra tokia:

SUM (skaičius1, skaičius2,…)

Prie jo pridedami visi skaičiai, kurie nurodyti kaip parametras.

PAVYZDYS:

Jei norite apskaičiuoti sumą, kurią išleidote pirkdami daržoves, surašykite visas kainas ir tada naudokite SUM formulę taip:

Sudėtinės palūkanos:

Norėdami apskaičiuoti sudėtines palūkanas, galite naudoti vieną iš „Excel“ formulių, vadinamą FV. Ši funkcija grąžins būsimą investicijos vertę pagal periodines, pastovias palūkanų normas ir mokėjimus. Šios funkcijos sintaksė yra tokia:

FV (norma, nper, pmt, pv, tipas)

Norėdami apskaičiuoti normą, turėsite padalyti metinę normą iš laikotarpių skaičiaus, ty metinio tarifo / laikotarpių. Periodų skaičius arba nper apskaičiuojamas padauginus terminą (metų skaičių) su laikotarpiais, ty terminu * laikotarpiais. pmt reiškia periodinį mokėjimą ir gali būti bet kokia vertė, įskaitant nulį.

Apsvarstykite šį pavyzdį:

Pirmiau pateiktame pavyzdyje aš apskaičiavau 500 USD sudėtines palūkanas 10% norma 5 metus ir darau prielaidą, kad periodinio mokėjimo vertė yra 0. Atkreipkite dėmesį, kad aš naudoju reikšmę -B1, iš manęs paimta 500 USD.

Vidutinis:

Vidurkis, kaip mes visi žinome, parodo vidutinę daugelio verčių vertę. Programoje „Excel“ vidurkį galima lengvai apskaičiuoti naudojant vidinę funkciją, vadinamą „AVERAGE“. Šios funkcijos sintaksė yra tokia:

AVERAGE (skaičius1, skaičius2,…)

PAVYZDYS:

Jei norite apskaičiuoti vidutinius visų egzaminų pažymius, kuriuos pasiekė studentai, galite tiesiog sukurti lentelę ir tada naudodami AVERAGE formulę apskaičiuoti vidutinius kiekvieno studento įvertinimus.

Aukščiau pateiktame pavyzdyje aš apskaičiavau dviejų mokinių dviejų egzaminų vidurkius. Jei turite daugiau nei dvi reikšmes, kurių vidurkį reikia nustatyti, tiesiog turite nurodyti langelių diapazoną, kuriame yra šios vertės. Pavyzdžiui:

SKAIČIUS:

„Excel“ skaičiavimo funkcija suskaičiuos ląstelių, kuriose yra skaičių tam tikrame diapazone, skaičių. Šios funkcijos sintaksė yra tokia:

COUNT (reikšmė1, vertė2,…)

PAVYZDYS:

Jei norėčiau apskaičiuoti langelių, turinčių skaičius, skaičių iš lentelės, kurią sukūriau ankstesniame pavyzdyje, turėsiu tiesiog pasirinkti langelį, kuriame noriu parodyti rezultatą, ir pasinaudoti funkcija COUNT taip:

TURAS:

Norėdami suapvalinti vertes iki tam tikrų skaičių po kablelio, galite naudoti funkciją ROUND. Ši funkcija grąžins skaičių apvalindama jį iki nurodyto skaičiaus po kablelio. Šios funkcijos sintaksė yra tokia:

TURAS (skaičius, skaitmenų skaitmenys)

PAVYZDYS:

Įvertinimo paieška:

Norėdami rasti pažymius, turėsite naudoti įdėtus IF sakinius „Excel“. Pavyzdžiui, pavyzdyje „Vidutinis“ aš apskaičiavau mokinių vidutinius testų įvertinimus. Dabar, norėdamas rasti šių studentų įvertinimus, turėsiu sukurti taip įdėtą IF funkciją:

Kaip matote, vidutiniai pažymiai yra G stulpelyje. Norėdamas apskaičiuoti pažymį, naudojau įdėtą IF formulę. Kodas yra toks:

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

Tai padarę, jūs tiesiog turėsite nukopijuoti formulę į visus langelius, kuriuose norite rodyti pažymius.

RANKAS:

Jei norite nustatyti klasės mokinių įvertinimą, galite pasinaudoti viena iš integruotų „Excel“ formulių, t. Y. RANK. Ši funkcija grąžins reitingą nurodytam diapazonui, lygindama nurodytą diapazoną didėjimo ar mažėjimo tvarka. Šios funkcijos sintaksė yra tokia:

RANK (nuoroda, numeris, užsakymas)

PAVYZDYS:

Kaip matote, aukščiau pateiktame pavyzdyje aš apskaičiavau studentų reitingą naudodamas reitingo funkciją. Čia pirmas parametras yra vidutiniai kiekvieno mokinio pažymiai, o masyvas - visų kitų klasės mokinių vidurkis. Nenurodžiau jokios tvarkos, todėl produkcija bus nustatyta mažėjančia tvarka. Didėjančios eilės reitingams turėsite nurodyti bet kokią nulinę vertę.

COUNTIF:

Norėdami suskaičiuoti langelius pagal tam tikras sąlygas, galite naudoti vieną iš integruotų „Excel“ formulių, vadinamą „COUNTIF“. Ši funkcija grąžins ląstelių, kurios tenkina tam tikras sąlygas tam tikrame diapazone, skaičių. Šios funkcijos sintaksė yra tokia:

COUNTIF (diapazonas, kriterijai)

PAVYZDYS:

Kaip matote, aukščiau pateiktame pavyzdyje radau langelių, kurių vertės yra didesnės nei 80, skaičių. Taip pat galite suteikti tam tikrą teksto vertę kriterijų parametrui.

INDEKSAS:

Funkcija INDEX grąžina vertę arba langelio nuorodą tam tikroje pozicijoje nurodytame diapazone. Šios funkcijos sintaksė yra tokia:

INDEX (masyvas, row_num, column_num) arba

INDEX (nuoroda,eilutės_skaita, stulpelio_skaita, ploto_numa)

Rodyklės funkcija veikia taip Masyvas forma:

  • Jei pateikiamas ir eilutės numeris, ir stulpelio numeris, jis grąžina sankirtos langelyje esančią vertę
  • Jei eilutės vertė nustatyta į nulį, ji grąžins vertes, esančias visame stulpelyje nurodytame diapazone
  • Jei stulpelio vertė nustatyta į nulį, ji grąžins vertes, esančias nurodytame diapazone visoje eilutėje

Rodyklės funkcija veikia taip Nuoroda forma:

  • Grąžina langelio, kuriame susikerta eilutės ir stulpelio vertės, nuorodą
  • „Area_num“ nurodys, kuris diapazonas bus naudojamas tuo atveju, jei buvo pateikti keli diapazonai

PAVYZDYS:

Kaip matote, aukščiau pateiktame pavyzdyje aš naudoju INDEX funkciją, norėdamas nustatyti vertę, esančią 2-oje eilutėje ir 4-oje stulpelyje, ląstelių diapazone nuo A18 iki G20.

Panašiai galite naudoti funkciją INDEX, nurodydami kelias nuorodas taip:

Tai atveda prie šio straipsnio apie „Excel“ formules ir funkcijas pabaigos. Tikiuosi, kad jums aišku viskas, kas su jumis pasidalinta. Įsitikinkite, kad praktikuojate kuo daugiau ir grąžinkite savo patirtį.

Turite mums klausimą? Prašau tai paminėti šio „Excel formulių ir funkcijų“ tinklaraščio komentarų skyriuje, ir mes kuo greičiau susisieksime su jumis.

Norėdami gauti išsamių žinių apie bet kokias populiarias technologijas ir įvairias jų programas, galite užsiregistruoti tiesiogiai su parą visą parą ir visą gyvenimą.