Mis on Exceli valemid ja funktsioonid ning kuidas neid kasutada?

Exceli valemid ja funktsioonid on andmete haldamise komponendid. Vaadake, kuidas valemeid kirjutada, kopeerida / kleepida, peita. IF, COUNTIF, SUM, DATE, RANK, INDEX, FV, ROUND jne

Andmeid hakatakse kasutama alles siis, kui saate nendega tegeleda ja Excel on üks tööriist, mis pakub palju mugavust nii siis, kui peate koostama oma võrrandid või kasutama sisseehitatud. Selles artiklis saate teada, kuidas saate nende Exceli valemite reklaamifunktsioonidega tegelikult töötada.



Siin on kiire ülevaade siin käsitletud teemadest:



Mis on valem?

Üldiselt on valem tihendatud viis osa teabe tähistamiseks. Excelis on valemid avaldised, mida saab sisestada Exceli lehe lahtritesse ja nende tulemusena kuvatakse nende väljundid.

Exceli valemid võivad olla järgmist tüüpi:



Tüüp

Näide

Kirjeldus



Matemaatilised operaatorid (+, -, * jne)

Näide: = A1 + B1

Lisab väärtused A1 ja B1

Väärtused või tekst

Näide: 100 * 0,5 kordub 100 korda 0,5

Võtab väärtused ja tagastab väljundi

Lahtriviide

NÄIDE: = A1 = B1

Tagastab väärtuse TÕENE või VALE, võrreldes A1 ja B1

Töölehe funktsioonid

NÄIDE: = SUMMA (A1: B1)

Tagastab väljundi, lisades A1 ja B1 väärtused

Exceli valemite kirjutamine:

Valemi kirjutamiseks Exceli lehe lahtrisse saate teha järgmist.

  • Valige lahter, kus soovite tulemust kuvada
  • Tippige algul märk '=', et anda Excelile teada, et sisestate sellesse lahtrisse valemi
  • Pärast seda saate kas sisestada lahtriaadressid või määrata väärtused, mida kavatsete arvutada
  • Näiteks kui soovite lisada kahe lahtri väärtused, võite lahtriaadressi sisestada järgmiselt:

sisestage valem-Exceli valemid-Edureka

  • Samuti saate valida lahtrid, mille summa soovite arvutada, valides kõik vajalikud lahtrid, vajutades samal ajal klahvi Ctrl:


Valemi muutmine:

Kui soovite mõnda varem sisestatud valemit muuta, valige lihtsalt lahter, mis sisaldab sihtvalemit ja valemiribal saate teha soovitud muudatused. Eelmises näites olen arvutanud A1 ja A2 summa. Nüüd redigeerin sama ja muudan valemit, et arvutada nendes kahes lahtris olevate väärtuste korrutis:


Kui see on tehtud, vajutage soovitud väljundi nägemiseks sisestusklahvi Enter.

Valemi kopeerimine või kleepimine:

Excel on tõesti mugav, kui peate valemeid kopeerima / kleepima. Valemi kopeerimisel hoolitseb Excel automaatselt selles kohas vajalike lahtriviidete eest. Seda tehakse süsteemi kaudu, mida nimetatakse as Lahtrite suhtelised aadressid .

Valemi kopeerimiseks valige lahter, mis sisaldab algset valemit, ja lohistage see kuni selle lahtrini, mis nõuab selle valemi koopiat järgmiselt:

Nagu pildilt näha, on valem algselt kirjutatud A3-ga ja siis olen selle lohistanud mööda B3 ja C3 B1, B2 ja C1, C2 summa arvutamiseks, lahtrite aadresse ainult üles kirjutamata.

Kui ma muudan mõne lahtri väärtusi, värskendab Excel väljundit automaatselt vastavalt standardile Lahtrite suhtelised aadressid , Absoluutsed lahtriaadressid või Segatud lahtrite aadressid .

Peida valemid Excelis:

Kui soovite mõnda valemit Exceli lehe eest varjata, saate seda teha järgmiselt:

  • Valige lahtrid, mille valemit kavatsete peita
  • Avage aken Font ja valige paneel Kaitse
  • Märkige suvand Peidetud ja klõpsake nuppu OK
  • Seejärel valige lindil vahekaardilt ülevaade
  • Klõpsake nupul Kaitse leht (valemid ei tööta, kui te seda ei tee)
  • Excel palub teil paroolid sisestada, et valemid edaspidiseks kasutamiseks varjata

Exceli valemite operaatori ülimuslikkus:

Exceli valemid järgivad reegleid BODMAS (sulgudes tellimusjaotuse korrutiste liitmise lahutamine). Kui teil on sulgudes olev valem, lahendatakse sulgudes olev avaldis enne täieliku valemi mis tahes muud osa. Heitke pilk allpool olevale pildile:

Nagu näete ülaltoodud näites, on mul valem, mis koosneb sulgust. Nii et vastavalt BODMAS-reeglitele leiab Excel kõigepealt erinevuse A2 ja B1 vahel ning seejärel lisab tulemuse A1-ga.

hashmap vs hashtable javas

Mida kas Excelis on funktsioonid?

Üldiselt määratleb funktsioon valemi, mis täidetakse mõnes antud järjekorras. Excel pakub tohutult palju sisseehitatud funktsioonid mida saab kasutada erinevate valemite tulemuse arvutamiseks.

Exceli valemid on jagatud järgmistesse kategooriatesse:

KategooriaOlulised valemid

Kuupäev Kellaaeg

KUUPÄEV, PÄEV, KUU, TUND jne

Finants

ACCI, accinto, Dollard, INTRAATE jne

Math & Trig

SUM, SUMIF, PRODUCT, SIN, COS jne

Statistiline

KESKMINE, COUNT, COUNTIF, MAX, MIN jne

Otsing ja viide

VEERG, JÄLGIMINE, RIDA, VLOOKUP, VALI jne

Andmebaas

DAVERAGE, DCOUNT, DMIN, DMAX jne

Tekst

BAHTTEXT, DOLLAR, LOWER, UPPER jne

Loogiline

JA VÕI EI, KUI TÕENE, VÄÄR jne

Teave

INFO, VIGA. TÜÜP, TÜÜP, ISERROR jne

Inseneritöö

COMPLEX, CONVERT, DELTA, OCT2BIN jne

Kuup

CUBESET, CUBENUMBER, CUBEVALUE jne

Ühilduvus

PERCENTIL, RANK, VAR, MODE jne

võrk

ENCODEURL, FILTERXML, WEBSERVICE

Nüüd kontrollime, kuidas kasutada kõige olulisemaid ja sagedamini kasutatavaid Exceli valemeid.

õppida ssis samm-sammult

Olulisemad Exceli funktsioonid:

Siin on mõned kõige olulisemad Exceli funktsioonid koos nende kirjelduste ja näidetega.

KUUPÄEV:

Exceli üks olulisemaid ja laialt kasutatavaid funktsioone Kuupäev on funktsioon KUUPÄEV. Selle süntaks on järgmine:

DATE (aasta, kuu, päev)

See funktsioon tagastab numbri, mis tähistab antud kuupäeva MS Exceli kuupäeva-kellaaja vormingus. Funktsiooni DATE saab kasutada järgmiselt:

NÄIDE:

  1. = KUUPÄEV (2019,11,7)
  2. = DATE (2019,11,7) -7 (tagastab praeguse kuupäeva - seitse päeva)

PÄEV:

See funktsioon tagastab kuu päeva väärtuse (1-31). Selle süntaks on järgmine:

PÄEV (seerianumber)

Siin on järjekorranumber kuupäev, mille päeva soovite hankida. Seda saab anda mis tahes viisil, näiteks mõne muu funktsiooni DATE pakutava funktsiooni tulemuse või lahtriviite abil.

NÄIDE:

  1. = PÄEV (A7)
  2. = PÄEV (TÄNA ())
  3. = PÄEV (KUUPÄEV (2019, 11,8))

KUU:

Täpselt nagu funktsioon PÄEV, pakub Excel ka teise funktsiooni, st KUU, et tuua kuu kindla kuupäeva järgi. Süntaks on järgmine:

MONTH (seerianumber)

NÄIDE:

  1. = KUU (TÄNA ())
  2. = KUU (KUUPÄEV (2019, 11,8))

Protsent:

Nagu me kõik teame, on protsent suhe, mis arvutatakse murdarvuna 100. Seda saab tähistada järgmiselt:

Protsent = (osa / kogu) x 100

Excelis saate arvutada soovitud väärtuste protsendi. Näiteks kui teil on osades ja tervetes väärtused A1 ja A2 juures ning soovite protsendi arvutada, saate seda teha järgmiselt:

  • Valige lahter, kus soovite tulemust kuvada
  • Sisestage märk “=”
  • Seejärel sisestage valemiks A1 / A2 ja vajutage sisestusklahvi
  • Valige avalehe numbrite rühmast sümbol “%”

KUI:

IF-lause on tingimuslause, mis tagastab True, kui määratud tingimus on täidetud, ja Flase, kui tingimus pole täidetud. Excel pakub sisseehitatud funktsiooni IF, mis seda eesmärki täidab. Selle süntaks on järgmine:

IF (loogiline_test, väärtus_tõene, väärtus_tõene)

Siin, loogiline_test on tingimus, mida tuleb kontrollida

NÄIDE:

  • Sisestage võrreldavad väärtused
  • Valige lahter, mis väljundit kuvab
  • Sisestage valemiribale “= IF (A1 = A2,“ Jah ”,“ Ei ”) ja vajutage sisestusklahvi

VAATAMINE:

Seda funktsiooni kasutatakse Exceli lehelt tulbast teatud andmete otsimiseks ja toomiseks. VLOOKUPi täht V tähistab vertikaalset otsingut. See on Excelis üks olulisemaid ja laialt kasutatavaid valemeid ning selle funktsiooni kasutamiseks tuleb tabel järjestada kasvavas järjekorras. Selle funktsiooni süntaks on järgmine:

VLOOKUP (otsinguväärtus, tabeli_värv, veeru_indeks, arvude_vahemik, otsing)

kus

otsingu_väärtus on otsitava väärtus

table_array on tabel, mida tuleb otsida

col_index on veerg, kust väärtus tuleb hankida

vahemiku_otsing (valikuline) tagastab väärtuse TRUE umbes. ja FALSE täpse vaste saamiseks

NÄIDE:

Nagu näete pildil, on minu määratud väärtus 2 ja tabelivahemik jääb vahemikku A1 kuni D4. Soovin tuua töötaja nime, seetõttu olen veeru väärtuseks määranud 2 ja kuna ma tahan, et see oleks täpne vaste, kasutasin vahemiku otsimiseks valet.

Tulumaks:

Oletame, et soovite arvutada inimese tulumaksu, kelle kogupalk on 300 dollarit. Tulumaks tuleb arvutada järgmiselt:

  • Loetlege kokku töötasu, palgaarvestused, maksustatav tulu ja tulumaksu protsent
  • Määrake kogupalga, palgaarvestuste väärtused
  • Seejärel arvutage maksustatav tulu, leides kogu palga ja palga mahaarvamiste vahe
  • Lõpuks arvutage maksusumma

SUMMA:

Exceli funktsioon SUM arvutab tulemuse, lisades kõik Excelis määratud väärtused. Selle funktsiooni süntaks on järgmine:

SUM (number1, number2,…)

Lisab sellele kõik parameetrina määratud numbrid.

NÄIDE:

Kui soovite arvutada köögiviljade ostmiseks kulutatud summa summa, loetlege kõik hinnad ja seejärel kasutage valemit SUM järgmiselt:

Liitintress:

Liitintressi arvutamiseks võite kasutada ühte Exceli valemitest nimega FV. See funktsioon tagastab investeeringu tulevase väärtuse perioodiliste, püsivate intressimäärade ja maksete alusel. Selle funktsiooni süntaks on järgmine:

FV (määr, nper, pmt, pv, tüüp)

Määra arvutamiseks peate jagama aastamäära perioodide, st aastamäära / perioodide arvuga. Perioodide arv või uus arv arvutatakse, korrutades tähtaja (aastate arv) perioodide, st tähtaeg * perioodidega. pmt tähistab perioodilist makset ja võib olla mis tahes väärtus, sealhulgas null.

Vaatleme järgmist näidet:

Ülaltoodud näites olen arvutanud liitintressi 500 dollarile 10% määraga 5 aasta jooksul ja eeldades, et perioodilise makse väärtus on 0. Pange tähele, et olen kasutanud tähendust -B1, minult on võetud 500 dollarit.

Keskmine:

Keskmine, nagu me kõik teame, kujutab paljude väärtuste mediaanväärtust. Excelis saab keskmist hõlpsasti arvutada, kasutades sisseehitatud funktsiooni nimega “AVERAGE”. Selle funktsiooni süntaks on järgmine:

KESKMINE (number1, number2,…)

NÄIDE:

Kui soovite arvutada kõigi eksamite õpilaste keskmised hinded, saate lihtsalt luua tabeli ja seejärel valemi AVERAGE abil arvutada iga õpilase keskmised hinded.

Ülaltoodud näites olen arvutanud kahe õpilase keskmised hinded kahel eksamil. Kui teil on rohkem kui kaks väärtust, mille keskmine on vaja kindlaks määrata, peate lihtsalt määrama lahtrite vahemiku, kus väärtused esinevad. Näiteks:

COUNT:

Exceli loendamisfunktsioon loendab antud vahemikus numbreid sisaldavate lahtrite arvu. Selle funktsiooni süntaks on järgmine:

COUNT (väärtus1, väärtus2,…)

NÄIDE:

Kui ma tahan eelmises näites loodud tabelist arvutada lahtrite arvu, mis sisaldavad numbreid, siis pean lihtsalt valima lahtri, milles soovin tulemust kuvada, ja seejärel kasutama funktsiooni COUNT järgmiselt:

RING:

Väärtuste ümardamiseks mõne konkreetse kümnendkohani võib kasutada funktsiooni ROUND. See funktsioon tagastab arvu, ümardades selle määratud arvu kümnendkohtadeni. Selle funktsiooni süntaks on järgmine:

ROUND (arv, numbri_numbrid)

NÄIDE:

Hinne leidmine:

Hinnete leidmiseks peate Excelis kasutama pesastatud IF-lauseid. Näiteks näites Keskmine olin arvutanud õpilaste testide keskmised hinded. Nende õpilaste saavutatud klasside leidmiseks pean looma pesastatud IF-funktsiooni järgmiselt:

Nagu näete, on keskmised märgid veerus G. Hinde arvutamiseks olen kasutanud sisestatud IF valemit. Kood on järgmine:

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

Pärast seda peate valemi lihtsalt kopeerima kõikidesse lahtritesse, kus soovite hindeid kuvada.

KOHT:

Kui soovite määrata klassi õpilaste saavutatud auastme, võite kasutada ühte sisseehitatud Exceli valemit, st RANK. See funktsioon tagastab määratud vahemiku auastme, võrreldes antud vahemikku tõusvas või kahanevas järjekorras. Selle funktsiooni süntaks on järgmine:

RANK (viide, number, tellimus)

NÄIDE:

Nagu näete, olen ülaltoodud näites arvutanud õpilaste auastme funktsiooni Rank abil. Esimene parameeter on iga õpilase keskmised hinded ja massiiv kõigi teiste klassi õpilaste keskmine. Ma ei ole ühtegi järjekorda täpsustanud, seetõttu määratakse väljund kahanevas järjekorras. Tõusvas järjekorras peate määrama kõik nullist erinevad väärtused.

COUNTIF:

Lahtrite loendamiseks mõne antud tingimuse alusel saate kasutada ühte sisseehitatud Exceli valemit nimega “COUNTIF”. See funktsioon tagastab rakkude arvu, mis vastavad teatud tingimustele antud vahemikus. Selle funktsiooni süntaks on järgmine:

COUNTIF (vahemik, kriteeriumid)

NÄIDE:

Nagu näete, leidsin ülaltoodud näites lahtrite arvu, mille väärtused on suuremad kui 80. Kriteeriumiparameetrile saate anda ka tekstiväärtuse.

INDEKS:

Funktsioon INDEX tagastab väärtuse või lahtriviite teatud vahemikus kindlas asukohas. Selle funktsiooni süntaks on järgmine:

INDEX (massiiv, real_num, column_num) või

INDEX (viide,rea_arv, veeru_number, ala_arv)

Indeksfunktsioon töötab Massiiv vorm:

  • Kui esitatakse nii rea number kui ka veeru number, tagastab see ristmikulises lahtris oleva väärtuse
  • Kui rea väärtuseks määratakse null, tagastab see määratud vahemikus kogu veerus olevad väärtused
  • Kui veeru väärtuseks määratakse null, tagastab see määratud vahemikus kogu rea väärtused

Indeksfunktsioon töötab Viide vorm:

  • Tagastab lahtri viite, kus rida ja veeru väärtused lõikuvad
  • Area_num näitab, millist vahemikku kasutada juhul, kui on esitatud mitu vahemikku

NÄIDE:

sortimismeetod c ++

Nagu näete, kasutasin ülaltoodud näites funktsiooni INDEX, et määrata väärtuste vahemik A18 kuni G20 vahemikus 2. ja 4. veerus olev väärtus.

Samamoodi võite kasutada ka funktsiooni INDEX, määrates mitu viidet järgmiselt:

See viib meid Exceli valemite ja funktsioonide artikli lõpuni. Loodan, et teil on selge kõigega, mida teiega on jagatud. Harjutage kindlasti nii palju kui võimalik ja pöörake oma kogemused tagasi.

Kas teil on meile küsimus? Palun mainige seda selle Exceli valemite ja funktsioonide ajaveebi kommentaaride jaotises ning võtame teiega ühendust niipea kui võimalik.

Põhjalike teadmiste saamiseks kõigi populaarsete tehnoloogiate ja nende erinevate rakenduste kohta saate registreeruda otseülekandeks 24/7 toe ja ligipääsuga kogu eluks.