Kaj so formule in funkcije v Excelu in kako jih uporabiti?

Podatki se začnejo uporabljati šele, ko lahko na njih dejansko delate in Excel je eno orodje, ki zagotavlja veliko udobja, kadar morate sami oblikovati enačbe ali uporabiti vgrajene enačbe. V tem članku boste izvedeli, kako lahko dejansko delate s temi funkcijami oglasov Excel Formulas.

Tukaj je hiter pogled na teme, o katerih smo razpravljali tukaj:

načrt spremljanja in nadzora projekta

• Kaj je formula?
• Pisanje Excelovih formul
• Urejanje formule
• Kopirajte / prilepite formulo
• Skrij formule v Excelu
• Excelove formule za prednost pred operaterji
• Kaj so funkcije v Excelu?
• Najpomembnejše funkcije

Kaj je formula?

Na splošno je formula zgoščen način predstavitve nekaterih informacij v obliki simbolov. V Excelu so formule izrazi, ki jih je mogoče vnesti v celice Excelovega lista in njihovi rezultati so prikazani kot rezultat.

Excel-ove formule so lahko naslednjih vrst:

Pisanje Excelovih formul:

Če želite napisati formulo v celico lista Excel, lahko storite naslednje:

• Izberite celico, v kateri želite, da se prikaže rezultat
• Najprej vnesite znak '=', da Excel obvestite, da boste v to celico vnesli formulo
• Po tem lahko vtipkate naslove celic ali določite vrednosti, ki jih nameravate izračunati
• Če želite na primer dodati vrednosti dveh celic, lahko naslov celice vnesete na naslednji način:

• Izberete lahko tudi celice, katerih vsoto želite izračunati, tako da med pritiskom na tipko Ctrl izberete vse zahtevane celice:

Urejanje formule:

Če želite urediti neko predhodno vneseno formulo, preprosto izberite celico, ki vsebuje ciljno formulo, in v vrstici s formulami lahko naredite želene spremembe. V prejšnjem primeru sem izračunal vsoto A1 in A2. Zdaj bom uredil isto in spremenil formulo za izračun zmnožka vrednosti, prisotnih v teh dveh celicah:

Ko je to končano, pritisnite Enter, da si ogledate želeni izhod.

Kopirajte ali prilepite formulo:

Excel je zelo priročen, ko morate kopirati / prilepiti formule. Kadarkoli kopirate formulo, Excel samodejno poskrbi za sklice na celice, ki so potrebni na tem mestu. To se naredi po sistemu, imenovanem kot Relativni naslovi celic .

Če želite kopirati formulo, izberite celico, v kateri je prvotna formula, in jo nato povlecite do celice, ki zahteva kopijo te formule, kot sledi:

Kot lahko vidite na sliki, je formula prvotno napisana v A3, nato pa sem jo povlekel vzdolž B3 in C3, da sem izračunal vsoto B1, B2 in C1, C2, ne da bi zapisal izključno naslove celic.

V primeru, da spremenim vrednosti katere koli celice, bo Excel samodejno posodobil izhod v skladu z Relativni naslovi celic , Absolutni naslovi celic ali Mešani celični naslovi .

Skrij formule v Excelu:

Če želite nekaj formul skriti z Excelovega lista, lahko to storite na naslednji način:

• Izberite celice, katerih formulo nameravate skriti
• Odprite okno Pisava in izberite podokno Zaščita
• Označite možnost Skrito in kliknite V redu
• Nato na zavihku traku izberite Pregled
• Kliknite Zaščitni list (če tega ne storite, formule ne bodo delovale)
• Excel vas bo pozval, da vnesete geslo, da razkrijete formule za prihodnjo uporabo

Prednost operaterja Excelovih formul:

Excel-ove formule sledijo pravilom BODMAS (Brackets Order Division Multiplication Addition Subtraction). Če imate formulo, ki vsebuje oklepaje, bo izraz v oklepajih rešen pred katerim koli drugim delom celotne formule. Oglejte si spodnjo sliko:

Kot lahko vidite v zgornjem primeru, imam formulo, ki je sestavljena iz oklepaja. V skladu s pravili BODMAS bo Excel najprej našel razliko med A2 in B1, nato pa rezultat dodal z A1.

Kaj so ‘Funkcije’ v Excelu?

Na splošno funkcija definira formulo, ki se izvaja v določenem vrstnem redu. Excel ponuja ogromno vgrajene funkcije ki se lahko uporablja za izračun rezultata različnih formul.

Formule v Excelu so razdeljene v naslednje kategorije:

Zdaj pa si oglejmo, kako uporabiti nekatere najpomembnejše in najpogosteje uporabljene Excelove formule.

Najpomembnejše funkcije programa Excel:

Tu je nekaj najpomembnejših Excelovih funkcij, skupaj z njihovimi opisi in primeri.

DATUM:

Ena najpomembnejših in najpogosteje uporabljenih funkcij Date v Excelu je funkcija DATUM. Njegova sintaksa je naslednja:

DATUM (leto, mesec, dan)

Ta funkcija vrne številko, ki predstavlja dani datum v obliki zapisa datuma in časa MS Excel. Funkcijo DATE lahko uporabite na naslednji način:

PRIMER:

1. = DATUM (2019,11,7)
2. = DATUM (2019,11,7) -7 (vrne trenutni datum - sedem dni)

DAN:

Ta funkcija vrne dnevno vrednost meseca (1-31). Njegova sintaksa je naslednja:

DAY (serijska_številka)

Tu je serial_number datum, katerega dan želite pridobiti. Lahko je podan na kakršen koli način, na primer rezultat neke druge funkcije, ki jo zagotavlja funkcija DATE, ali sklic na celico.

PRIMER:

1. = DAN (A7)
2. = DAN (DANES ())
3. = DAN (DATUM (2019, 11,8))

MESEC:

Tako kot funkcija DAY, ima tudi Excel drugo funkcijo, tj. MONTH, da pridobi mesec od določenega datuma. Sintaksa je naslednja:

MONTH (serijska_številka)

PRIMER:

1. = MESEC (DANES ())
2. = MESEC (DATUM (2019, 11,8))

Odstotek:

Kot vsi vemo, je odstotek razmerje, izračunano kot delež 100. Označimo ga lahko na naslednji način:

Odstotek = (del / celota) x 100

V Excelu lahko izračunate odstotek poljubnih želenih vrednosti. Če imate na primer vrednosti del in celoto prisotne v A1 in A2 in želite izračunati odstotek, lahko to storite na naslednji način:

• Izberite celico, v kateri želite prikazati rezultat
• Vnesite znak '='
• Nato vnesite formulo kot A1 / A2 in pritisnite Enter
• V skupini številk domačih zavihkov izberite simbol '%'

ČE:

Stavek IF je pogojni stavek, ki vrne True, ko je določen pogoj izpolnjen, in Flase, če pogoj ni. Excel ima v ta namen vgrajeno funkcijo »IF«. Njegova sintaksa je naslednja:

IF (logic_test, value_if_true, value_if_false)

Tukaj, logični_test je pogoj, ki ga je treba preveriti

PRIMER:

• Vnesite vrednosti, ki jih želite primerjati
• Izberite celico, ki bo prikazala izhod
• V vrstico formule vnesite '= IF (A1 = A2,' Yes ',' No ')' in pritisnite Enter

POGLED:

Ta funkcija se uporablja za iskanje in pridobivanje določenih podatkov iz stolpca z Excelovega lista. 'V' v VLOOKUP pomeni vertikalno iskanje. To je ena najpomembnejših in najpogosteje uporabljenih formul v Excelu in za uporabo te funkcije je treba tabelo razvrstiti po naraščajočem vrstnem redu. Sintaksa te funkcije je naslednja:

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

kje,

lookup_value je vrednost, ki jo je treba iskati

polje_tabele je tabela, ki jo je treba iskati

col_index je stolpec, iz katerega je treba pridobiti vrednost

range_lookup (neobvezno) vrne TRUE za približno tekmo in FALSE za natančno ujemanje

PRIMER:

Kot lahko vidite na sliki, je vrednost, ki sem jo določil, 2, razpon tabel pa med A1 in D4. Želim pridobiti ime zaposlenega, zato sem dal vrednost stolpca kot 2 in ker želim, da se natančno ujema, sem za iskanje obsega uporabil False.

Davek na prihodek:

Recimo, da želite izračunati dohodnino osebe, katere skupna plača znaša 300 USD. Davek od dohodka boste morali izračunati na naslednji način:

• Naštejte skupno plačo, odbitke plač, obdavčljivi dohodek in odstotek davka od dohodka
• Navedite vrednosti skupne plače, odbitki plač
• Nato izračunajte obdavčljivi dohodek tako, da poiščete razliko med celotnimi odbitki plač in plač
• Na koncu izračunajte znesek davka

SUM:

Funkcija SUM v Excelu izračuna rezultat z dodajanjem vseh določenih vrednosti v Excelu. Sintaksa te funkcije je naslednja:

SUM (številka1, številka2,…)

Zanj doda vse številke, ki so določene kot parameter.

PRIMER:

Če želite izračunati vsoto zneska, ki ste ga porabili za nakup zelenjave, navedite vse cene in nato uporabite formulo SUM, kot sledi:

Obrestno obrestovanje:

Za izračun sestavljenih obresti lahko uporabite eno od Excelovih formul, imenovano FV. Ta funkcija bo vrnila prihodnjo vrednost naložbe na podlagi periodičnih, stalnih obrestnih mer in plačil. Sintaksa te funkcije je naslednja:

FV (stopnja, nper, pmt, pv, vrsta)

Za izračun stopnje boste morali letno stopnjo deliti s številom obdobij, tj. Letno stopnjo / obdobja. Število obdobij ali nper se izračuna tako, da pomnožimo izraz (št. Let) z obdobji, tj. Izraz * obdobja. pmt pomeni periodično plačilo in je lahko poljubne vrednosti, vključno z ničlo.

Upoštevajte naslednji primer:

V zgornjem primeru sem izračunal sestavljene obresti za 500 USD po stopnji 10% za 5 let in ob predpostavki, da je periodična vrednost plačila 0. Upoštevajte, da sem uporabil pomen -B1, 500 USD so mi vzeli.

Povprečje:

Povprečje, kot vsi vemo, prikazuje srednjo vrednost številnih vrednosti. V Excelu lahko povprečje enostavno izračunamo z vgrajeno funkcijo, imenovano 'AVERAGE'. Sintaksa te funkcije je naslednja:

POVPREČNO (številka1, številka2,…)

PRIMER:

Če želite izračunati povprečne ocene, ki so jih študentje dosegli na vseh izpitih, lahko preprosto ustvarite tabelo in nato s formulo AVERAGE izračunate povprečne ocene, ki jih doseže vsak študent.

V zgornjem primeru sem izračunal povprečne ocene za dva študenta na dveh izpitih. Če imate več kot dve vrednosti, katerih povprečje je treba določiti, morate določiti obseg celic, v katerih so vrednosti prisotne. Na primer:

ŠTEVILO:

Funkcija štetja v Excelu bo štela število celic, ki vsebujejo številke v določenem obsegu. Sintaksa te funkcije je naslednja:

COUNT (vrednost1, vrednost2,…)

PRIMER:

V primeru, da želim izračunati število celic, ki vsebujejo številke, iz tabele, ki sem jo ustvaril v prejšnjem primeru, bom moral preprosto izbrati celico, v kateri želim prikazati rezultat, in nato uporabiti funkcijo COUNT, kot sledi:

KROG:

Če želite vrednosti zaokrožiti na določena decimalna mesta, lahko uporabite funkcijo ROUND. Ta funkcija bo vrnila številko tako, da jo zaokroži na določeno število decimalnih mest. Sintaksa te funkcije je naslednja:

KROG (število, števke-številke)

PRIMER:

Iskanje ocene:

Za iskanje ocen boste morali uporabiti ugnezdene stavke IF v Excelu. Na primer, v primeru Povprečje sem izračunal povprečne ocene, ki so jih študenti dosegli na testih. Zdaj, da najdem ocene, ki so jih dosegli ti učenci, bom moral ustvariti ugnezdeno funkcijo IF, kot sledi:

Kot lahko vidite, so povprečne ocene prisotne v stolpcu G. Za izračun ocene sem uporabil ugnezdeno formulo IF. Koda je naslednja:

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

Po tem boste morali kopirati formulo v vse celice, kjer želite prikazati ocene.

RANG:

Če želite določiti uvrstitev, ki so jo dosegli učenci razreda, lahko uporabite eno od vgrajenih Excelovih formul, tj. RANK. Ta funkcija bo vrnila uvrstitev za določen obseg s primerjavo določenega obsega v naraščajočem ali padajočem vrstnem redu. Sintaksa te funkcije je naslednja:

RANK (sklic, številka, vrstni red)

PRIMER:

Kot lahko vidite, sem v zgornjem primeru izračun ranga študentov izračunal s funkcijo Rank. Tu je prvi parameter povprečna ocena, ki jo je dosegel vsak učenec, niz pa povprečje, ki so jo dosegli vsi drugi učenci razreda. Nisem določil nobenega vrstnega reda, zato bo izhod določen v padajočem vrstnem redu. Za naraščajoče vrstne razrede boste morali navesti poljubno ničelno vrednost.

ŠTEVILO:

Za štetje celic glede na določen pogoj lahko uporabite eno od vgrajenih Excelovih formul, imenovano »ŠTEVILO«. Ta funkcija bo vrnila število celic, ki izpolnjujejo neki pogoj v določenem obsegu. Sintaksa te funkcije je naslednja:

COUNTIF (obseg, merila)

PRIMER:

Kot lahko vidite, sem v zgornjem primeru našel število celic z vrednostmi, večjimi od 80. Parametru kriterijev lahko dodelite tudi nekaj besedilnih vrednosti.

KAZALO:

Funkcija INDEX vrne vrednost ali referenco celice na določenem položaju v določenem obsegu. Sintaksa te funkcije je naslednja:

INDEX (matrika, številka_ vrstice, številka_ stolpca) ali

INDEX (sklic,številka_vrstice, številka_ stolpca, številka_številke)

kakšna je razlika med git in github

Funkcija indeksa deluje za Matrika oblika:

• Če sta navedeni številka vrstice in številka stolpca, vrne vrednost, ki je prisotna v presečni celici
• Če je vrednost vrstice nastavljena na nič, bo vrnila vrednosti, ki so prisotne v celotnem stolpcu v določenem obsegu
• Če je vrednost stolpca nastavljena na nič, bo vrnil vrednosti v celotni vrstici v določenem obsegu

Funkcija indeksa deluje za Referenca oblika:

• Vrne sklic celice, kjer se sekata vrednosti vrstic in stolpcev
• Area_num bo pokazal, kateri obseg bo uporabljen, če je na voljo več obsegov

PRIMER:

Kot lahko vidite, sem v zgornjem primeru s funkcijo INDEX določil vrednost v 2. vrstici in 4. stolpcu za obseg celic med A18 in G20.

Podobno lahko uporabite tudi funkcijo INDEX, tako da določite več referenc, kot sledi:

S tem smo na koncu tega članka o Excelovih formulah in funkcijah. Upam, da ste na jasnem z vsem, kar ste delili z vami. Poskrbite, da boste čim več vadili in si povrnili izkušnje.

Imate vprašanje za nas? Prosimo, omenite ga v oddelku za komentarje tega spletnega dnevnika 'Excel Formule in funkcije', mi pa se vam bomo javili v najkrajšem možnem času.

Če želite pridobiti poglobljeno znanje o vseh trendovskih tehnologijah, skupaj z različnimi aplikacijami, se lahko prijavite v živo s 24-urno podporo in življenjskim dostopom.