Što su formule i funkcije u Excelu i kako ih koristiti?

Podaci se koriste samo kada na njima zapravo možete raditi i Excel jedan je alat koji pruža veliku količinu pogodnosti i kada morate formulirati vlastite jednadžbe ili koristiti ugrađene. U ovom ćete članku naučiti kako zapravo možete raditi s ovim funkcijama oglasa Excel Formulas.

Evo kratkog pregleda tema o kojima se ovdje raspravlja:

• Što je formula?
• Pisanje Excel formula
• Uređivanje formule
• Kopirajte / zalijepite formulu
• Sakrij formule u Excelu
• Excel-ove formule prvenstva operatora
• Što su funkcije u Excelu?
• Najvažnije funkcije

Što je formula?

Općenito, formula je sažeti način predstavljanja nekih informacija u smislu simbola. U Excelu su formule izrazi koji se mogu unijeti u ćelije Excel lista i njihovi se rezultati prikazuju kao rezultat.

Excel formule mogu biti sljedećih vrsta:

Pisanje Excel formula:

Da biste napisali formulu u Excel ćeliju lista, možete učiniti sljedeće:

• Odaberite ćeliju u kojoj želite da se prikaže rezultat
• Prvo upišite znak '=' kako biste Excel znali da ćete unijeti formulu u tu ćeliju
• Nakon toga možete upisati adrese ćelija ili odrediti vrijednosti koje namjeravate izračunati
• Na primjer, ako želite dodati vrijednosti dviju ćelija, možete unijeti adresu ćelije na sljedeći način:

• Također možete odabrati ćelije čiji zbroj želite izračunati odabirom svih potrebnih ćelija dok pritiskate tipku Ctrl:

Uređivanje formule:

U slučaju da želite urediti neku prethodno unesenu formulu, jednostavno odaberite ćeliju koja sadrži ciljanu formulu i na traci s formulama možete napraviti željene promjene. U prethodnom primjeru izračunao sam zbroj A1 i A2. Sada ću to isto urediti i promijeniti formulu za izračun umnoška vrijednosti prisutnih u ove dvije ćelije:

Kada je to gotovo, pritisnite Enter da biste vidjeli željeni izlaz.

Kopirajte ili zalijepite formulu:

Excel vam jako dobro dođe kada morate kopirati / zalijepiti formule. Kad god kopirate formulu, Excel se automatski brine o referencama ćelija koje su potrebne na tom mjestu. To se radi kroz sustav koji se naziva kao Relativne adrese stanica .

Da biste kopirali formulu, odaberite ćeliju koja sadrži izvornu formulu, a zatim je povucite do ćelije koja zahtijeva kopiju te formule kako slijedi:

Kao što možete vidjeti na slici, formula je izvorno napisana u A3, a zatim sam je povukao prema B3 i C3 kako bih izračunao zbroj B1, B2 i C1, C2, a da nisam isključivo zapisao adrese ćelija.

U slučaju da promijenim vrijednosti bilo koje od ćelija, Excel će automatski ažurirati izlaz u skladu s Relativne adrese stanica , Apsolutne adrese stanica ili Mješovite ćelijske adrese .

Sakrij formule u Excelu:

U slučaju da želite sakriti neku formulu s excel lista, to možete učiniti na sljedeći način:

• Odaberite ćelije čiju formulu namjeravate sakriti
• Otvorite prozor Font i odaberite okno Protection
• Označite Skriveno i kliknite U redu
• Zatim na kartici vrpce odaberite Pregled
• Kliknite zaštitni list (Formule neće raditi ako to ne učinite)
• Excel će zatražiti da unesete lozinku kako biste otkrili formule za buduću upotrebu

Prednost operatora Excel formula:

Excel formule slijede pravila BODMAS (Bracket Order Division Multiplication Addition Subtraction). Ako imate formulu koja sadrži zagrade, izraz unutar zagrada bit će riješen prije bilo kojeg drugog dijela cjelovite formule. Pogledajte donju sliku:

Kao što možete vidjeti u gornjem primjeru, imam formulu koja se sastoji od zagrade. Dakle, u skladu s BODMAS pravilima, Excel će prvo pronaći razliku između A2 i B1, a zatim zbraja rezultat s A1.

Što su ‘Funkcije’ u Excelu?

Općenito, funkcija definira formulu koja se izvršava u nekom zadanom redoslijedu. Excel nudi ogroman broj ugrađene funkcije koji se mogu koristiti za izračunavanje rezultata različitih formula.

Formule u Excelu podijeljene su u sljedeće kategorije:

Pogledajmo sada kako iskoristiti neke od najvažnijih i najčešće korištenih Excel formula.

Najvažnije Excel funkcije:

Evo nekoliko najvažnijih Excel funkcija zajedno s njihovim opisima i primjerima.

DATUM:

Jedna od najvažnijih i najčešće korištenih funkcija Date u Excelu je funkcija DATUM. Sintaksa je sljedeća:

DATUM (godina, mjesec, dan)

Ova funkcija vraća broj koji predstavlja zadani datum u MS Excel formatu datuma i vremena. Funkcija DATE može se koristiti na sljedeći način:

PRIMJER:

1. = DATUM (2019,11,7)
2. = DATUM (2019,11,7) -7 (vraća trenutni datum - sedam dana)

DAN:

Ova funkcija vraća dnevnu vrijednost mjeseca (1-31). Sintaksa je sljedeća:

DAY (serijski_broj)

Ovdje je serijski_broj datum čiji dan želite dohvatiti. Može se dati na bilo koji način, kao što je rezultat neke druge funkcije koju daje funkcija DATE ili referenca na ćeliju.

PRIMJER:

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

MJESEC:

Baš kao i funkcija DAY, Excel pruža još jednu funkciju, tj. MJESEC, kako bi dohvatio mjesec od određenog datuma. Sintaksa je sljedeća:

MONTH (serijski_broj)

PRIMJER:

1. = MJESEC (DANAS ())
2. = MJESEC (DATUM (2019, 11,8))

Postotak:

Kao što svi znamo, postotak je omjer izračunat kao udio 100. Može se označiti na sljedeći način:

Postotak = (dio / cjelina) x 100

U Excelu možete izračunati postotak svih željenih vrijednosti. Na primjer, ako imate vrijednosti dijela i cjeline prisutne u A1 i A2 i želite izračunati postotak, to možete učiniti na sljedeći način:

• Odaberite ćeliju u kojoj želite prikazati rezultat
• Upišite znak '='
• Zatim unesite formulu kao A1 / A2 i pritisnite Enter
• Iz grupe brojeva početnih kartica odaberite simbol '%'

AKO:

IF izraz je uvjetni izraz koji vraća True ako je navedeni uvjet zadovoljen i Flase kada uvjet nije. Excel pruža ugrađenu funkciju 'IF' koja služi u tu svrhu. Njegova sintaksa je sljedeća:

IF (logički_test, value_if_true, value_if_false)

Ovdje, logički_test je uvjet koji treba provjeriti

PRIMJER:

• Unesite vrijednosti za usporedbu
• Odaberite ćeliju koja će prikazivati ​​izlaz
• Upišite u traku s formulama '= IF (A1 = A2,' Yes ',' No ') i pritisnite Enter

PREGLED:

Ova se funkcija koristi za traženje i preuzimanje određenih podataka iz stupca s Excel lista. 'V' u VLOOKUP-u označava vertikalno traženje. To je jedna od najvažnijih i najčešće korištenih formula u Excelu, a da bi se mogla koristiti ova funkcija, tablica mora biti sortirana uzlaznim redoslijedom. Sintaksa ove funkcije je sljedeća:

VLOOKUP (vrijednost_traženja, niz_tablica, col_index, opseg_brojeva, traženje)

gdje,

lookup_value je vrijednost koju treba pretražiti

niz_tabela je tablica koju treba pretražiti

col_index je stupac iz kojeg se treba dohvatiti vrijednost

range_lookup (nije obavezno) vraća vrijednost TRUE za cca. i FALSE za točno podudaranje

PRIMJER:

Kao što možete vidjeti na slici, vrijednost koju sam naveo je 2, a raspon tablice je između A1 i D4. Želim dohvatiti ime zaposlenika, stoga sam dao vrijednost stupca kao 2, a budući da želim da se točno podudara, koristio sam False za traženje raspona.

Porez na dohodak:

Pretpostavimo da želite izračunati porez na dohodak osobe čija ukupna plaća iznosi 300 USD. Porez na dobit morat ćete izračunati na sljedeći način:

• Navedite ukupnu plaću, odbitke od plaće, oporezivi dohodak i postotak poreza na dohodak
• Navedite vrijednosti ukupne plaće, odbitaka plaće
• Zatim izračunajte oporezivi dohodak pronalazeći razliku između ukupnih odbitka na plaću i odbitka na plaću
• Na kraju, izračunajte iznos poreza

IZNOS:

Funkcija SUM u Excelu izračunava rezultat dodavanjem svih navedenih vrijednosti u Excelu. Sintaksa ove funkcije je sljedeća:

ZBIR (broj1, broj2,…)

Tome dodaje sve brojeve koji su navedeni kao parametar.

PRIMJER:

U slučaju da želite izračunati zbroj iznosa koji ste potrošili u kupnji povrća, navedite sve cijene, a zatim upotrijebite SUM formulu kako slijedi:

Zajednički interes:

Da biste izračunali složene kamate, možete koristiti jednu od Excel formula nazvanu FV. Ova funkcija vratit će buduću vrijednost ulaganja na temelju povremenih, stalnih kamatnih stopa i plaćanja. Sintaksa ove funkcije je sljedeća:

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

Da biste izračunali stopu, morat ćete podijeliti godišnju stopu s brojem razdoblja, tj. Godišnju stopu / razdoblja. Broj razdoblja ili nper izračunava se množenjem pojma (broja godina) s razdobljima, tj. Razdoblja * razdoblja. pmt znači povremeno plaćanje i može biti bilo koje vrijednosti, uključujući nulu.

Razmotrimo sljedeći primjer:

U gornjem primjeru izračunao sam složenu kamatu za 500 USD po stopi od 10% tijekom 5 godina i pretpostavljajući da je periodična vrijednost plaćanja 0. Imajte na umu da sam upotrijebio -B1 što znači da mi je oduzeto 500 USD.

Prosjek:

Prosjek, kao što svi znamo, prikazuje srednju vrijednost određenog broja vrijednosti. U Excelu se prosjek može lako izračunati pomoću ugrađene funkcije nazvane „PROSJEK“. Sintaksa ove funkcije je sljedeća:

PROSJEČNO (broj1, broj2,…)

marioneta vs kuhar vs jenkins

PRIMJER:

U slučaju da želite izračunati prosječne ocjene koje su studenti postigli na svim ispitima, jednostavno možete stvoriti tablicu, a zatim pomoću prosječne formule izračunati prosječne ocjene koje je postigao svaki student.

U gornjem primjeru izračunao sam prosječne ocjene za dva studenta na dva ispita. U slučaju da imate više od dvije vrijednosti čiji prosjek treba odrediti, samo trebate odrediti raspon ćelija u kojima su vrijednosti prisutne. Na primjer:

RAČUNATI:

Funkcija brojanja u programu Excel računat će broj ćelija koje sadrže brojeve u određenom rasponu. Sintaksa ove funkcije je sljedeća:

COUNT (vrijednost1, vrijednost2,…)

PRIMJER:

U slučaju da želim izračunati broj ćelija koje sadrže brojeve iz tablice koju sam stvorio u prethodnom primjeru, jednostavno ću morati odabrati ćeliju u kojoj želim prikazati rezultat, a zatim iskoristiti funkciju COUNT na sljedeći način:

KRUG:

Da biste zaokružili vrijednosti na neke određene decimalne točke, možete koristiti funkciju ROUND. Ova će funkcija vratiti broj zaokruživanjem na navedeni broj decimalnih mjesta. Sintaksa ove funkcije je sljedeća:

KRUG (broj, broj_znamenki)

PRIMJER:

Pronalaženje ocjene:

Da biste pronašli ocjene, morat ćete se poslužiti ugniježđenim IF izrazima u Excelu. Na primjer, u primjeru Prosjek izračunao sam prosječne ocjene koje su studenti postigli na testovima. Sada, da bih pronašao ocjene koje su postigli ovi studenti, morat ću stvoriti ugniježđenu IF funkciju kako slijedi:

Kao što vidite, prosječne ocjene su prisutne u stupcu G. Da bih izračunao ocjenu, koristio sam ugniježđenu formulu IF. Kôd je sljedeći:

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

Nakon što to napravite, morat ćete kopirati formulu u sve ćelije u kojima želite prikazati ocjene.

RANG:

U slučaju da želite odrediti rang koji su postigli učenici razreda, možete koristiti jednu od ugrađenih Excel formula, tj. RANK. Ova će funkcija vratiti rang za navedeni raspon uspoređujući zadani raspon u rastućem ili silaznom redoslijedu. Sintaksa ove funkcije je sljedeća:

RANG (ref, broj, redoslijed)

PRIMJER:

Kao što vidite, u gornjem sam primjeru izračunao rang učenika pomoću funkcije Rangiranje. Ovdje je prvi parametar prosječna ocjena koju je postigao svaki učenik, a niz prosjek koju su postigli svi ostali učenici razreda. Nisam odredio nijedan redoslijed, stoga će se izlaz odrediti prema silaznom redoslijedu. Za rastuće redoslijede redoslijeda morat ćete navesti bilo koju vrijednost koja nije nula.

COUNTIF:

Da biste brojali stanice na temelju određenog stanja, možete koristiti jednu od ugrađenih Excel formula nazvanu „COUNTIF“. Ova će funkcija vratiti broj stanica koje zadovoljavaju neki uvjet u danom rasponu. Sintaksa ove funkcije je sljedeća:

COUNTIF (raspon, kriteriji)

PRIMJER:

Kao što vidite, u gornjem sam primjeru pronašao broj ćelija koje imaju vrijednosti veće od 80. Parametru kriterija možete dati i neku tekstualnu vrijednost.

INDEKS:

Funkcija INDEX vraća vrijednost ili referencu na ćeliju na nekom određenom položaju u navedenom rasponu. Sintaksa ove funkcije je sljedeća:

INDEX (niz, broj_reda, broj_stupca) ili

INDEKS (referenca,broj_reda, broj_stupca, broj_podrucja)

Funkcija indeksa radi kako slijedi za Niz oblik:

• Ako su navedeni i broj retka i broj stupca, vraća vrijednost koja je prisutna u presječnoj ćeliji
• Ako je vrijednost retka postavljena na nulu, vratit će vrijednosti prisutne u cijelom stupcu u navedenom rasponu
• Ako je vrijednost stupca postavljena na nulu, vratit će vrijednosti prisutne u cijelom retku u navedenom rasponu

Funkcija indeksa radi kako slijedi za Referenca oblik:

• Vraća referencu ćelije u kojoj se presijecaju vrijednosti retka i stupca
• Area_num naznačit će koji će se raspon koristiti u slučaju da je dostavljeno više raspona

PRIMJER:

Kao što vidite, u gornjem sam primjeru pomoću funkcije INDEX odredio vrijednost prisutnu u 2. retku i 4. stupcu za raspon ćelija između A18 i G20.

Slično tome, možete koristiti i funkciju INDEX tako što ćete navesti više referenci kako slijedi:

Ovo nas dovodi do kraja ovog članka o Excel formulama i funkcijama. Nadam se da vam je jasno sa svime što je s vama podijeljeno. Obavezno vježbajte što je više moguće i vratite svoje iskustvo.

Imate pitanje za nas? Molimo vas da ga spominjete u odjeljku za komentare ovog bloga 'Excel formule i funkcije', a mi ćemo vam se javiti u najkraćem mogućem roku.

Da biste stekli detaljno znanje o bilo kojim trendovskim tehnologijama, zajedno s raznim aplikacijama, možete se prijaviti uživo s 24/7 podrškom i doživotnim pristupom.