Kako napisati formule u excelu. Cjelovite informacije o formulama u Excelu
Microsoftov program Excel Office ima brojne prikladne značajke koje vam pomažu da ne radite duge izračune prije unosa podataka u tablice ili grafikone. Da biste izračunali neke vrijednosti, na primjer zbrajanje, oduzimanje, omjer i druge, možete upotrijebiti izvorno navedene formule. Možete ih dodati sami ili odabrati s popisa postojećih. U ovom članku naučite umetati formulu korak po korak.
Otvorite Excel. Imat ćete radni prostor ispred sebe, provjerite je li otvorena kartica "Početna". U praznom retku smještenom između zaglavlja programa i njegovog radnog područja vidjet ćete polje za unos formule, trebat će vam nakon unosa argumenata, odnosno brojeva koje treba izračunati.
Sada točno znate kako stvoriti formulu u Excelu i vidjeti njihovo objašnjenje u čarobnjaku za funkcije.
Ako prije niste radili s Excelom, uskoro ćete saznati da ovo nije samo proračunska tablica za unos brojeva. Naravno, u Excelu možete samo brojati iznose u retke i stupce, ali također možete izračunati uplate hipoteke, riješiti matematičke i inženjerske probleme i pronaći najpovoljnije opcije ovisno o danim varijablama.
U Excelu se sve to radi pomoću formula u stanicama. Te se formule koriste za izračun i druge radnje s podacima na radnom listu. Formula uvijek započinje znakom jednakosti (\u003d), nakon čega možete unijeti brojeve, matematičke operatore (poput znakova + i - za zbrajanje i oduzimanje) i ugrađene Excel funkcije koje uvelike proširuju mogućnosti formula.
Ispod je primjer formule koja množi 2 s 3 i dodaje 5 rezultatu da bi se dobilo 11.
Slijede primjeri formula koje se mogu koristiti na radnim listovima.
Dijelovi Excel formule
Formula također može sadržavati jedan ili više elemenata kao što su funkcije, poveznice, operatora i konstante.
Redoslijed radnji u formulama
U nekim slučajevima redoslijed ocjenjivanja može utjecati na vrijednost vraćenu formulom, stoga je važno razumjeti standardni redoslijed ocjenjivanja i znati ga promijeniti kako biste dobili željene rezultate.
Korištenje funkcija i ugniježđenih funkcija u Excel formulama
Funkcije su unaprijed definirane formule koje izvode izračune na zadane vrijednosti - argumente - u određenom redoslijedu ili obrascu. Funkcije se mogu koristiti za izvođenje jednostavnih i složenih izračuna. svi excel funkcije može se vidjeti na kartici Formule.
Korištenje veza u Excel formulama
Veza pokazuje na ćeliju ili raspon stanica na radnom listu i govori Microsoft Excel, gdje se nalaze vrijednosti ili podaci potrebni u formuli. Pomoću veza možete koristiti podatke s različitih dijelova radnog lista u jednoj formuli, kao i vrijednost jedne ćelije u više formula. Osim toga, možete postaviti veze do ćelija na različitim listovima jedne radne knjige ili do ćelija iz drugih knjiga. Upute na stanice u drugim knjigama nazivaju se veze ili xrefs.
Korištenje imena u Excel formulama
Za označavanje stanica, raspona stanica, formula, konstanti i excel proračunske tablice možete stvoriti određena imena. Naziv je suvisla kratka oznaka koja objašnjava svrhu reference stanice, konstante, formule ili tablice, jer na prvi pogled može biti teško razumjeti njihovu suštinu. Slijede primjeri imena i kako se mogu koristiti za lakše razumijevanje formula.
Primjer 1
Primjer 2
Kopirajte uzorke podataka iz donje tablice i zalijepite ih u ćeliju A1 novog excel radni list... Da biste prikazali rezultate formula, odaberite ih i pritisnite F2, a zatim pritisnite Enter. Osim toga, možete prilagoditi širinu stupaca prema podacima koje sadrže.
Bilješka: U formulama u stupcima C i D, specifični naziv Prodaja zamjenjuje se referencom na raspon A9: A13, a naziv Podaci o prodaji zamjenjuje se rasponom A9: B13. Ako knjiga nema ova imena, formule u D2: D3 vratit će pogrešku #NAME?
|
Primjer vrste |
Primjer koji ne koristi imena |
Primjer korištenja imena |
Formula i rezultat pomoću imena |
|
"\u003d ZBOR (A9: A13) |
"\u003d SUM (prodaja) |
SUM (prodaja) |
|
|
"\u003d TEKST (PREGLED (MAX (A9: 13), A9: B13,2, FALSE)," dd / mm / yyyy ") |
"\u003d TEKST (VLOOKUP (MAX (prodaja), informacije o prodaji, 2, FALSE)," dd.mm.gggg) " |
TEKST (VLOOKUP (MAX (prodaja), informacije o prodaji, 2, FALSE), "dd.mm.gggg") |
|
|
Datum prodaje |
|||
Za više informacija pogledajte članak Definiranje i korištenje imena u formulama.
Korištenje formula niza i konstanti niza u Excelu
Formula niza može izvesti višestruke izračune, a zatim vratiti jednu vrijednost ili skupinu vrijednosti. Formula niza obrađuje više skupova vrijednosti koji se nazivaju argumenti niza. Svaki argument polja mora sadržavati jednak broj redaka i stupaca. Formula niza kreira se na isti način kao i ostale formule, s tom razlikom što za unos formule koristite CTRL + SHIFT + ENTER. Neke ugrađene funkcije su formule polja i moraju se unijeti kao nizovi radi ispravnih rezultata.
Konstante niza mogu se koristiti umjesto referenci ako ne trebate unijeti svaku konstantu u zasebna ćelija na listu.
Korištenje formule niza za izračunavanje jedne ili više vrijednosti
Bilješka: Kada unesete formulu polja, Excel će je automatski zatvoriti u zagrade (i). Ako pokušate ručno unijeti kovrčave zagrade, Excel će prikazati formulu u obliku teksta.
Korištenje konstanti niza
U redovnu formulu možete unijeti referencu na ćeliju s vrijednošću ili na samu vrijednost, koja se naziva i konstanta. Isto tako, u formulu polja možete unijeti referencu na niz ili niz vrijednosti sadržanih u stanicama (ponekad nazvanu konstanta niza). Formule polja prihvaćaju konstante na isti način kao i ostale formule, međutim, konstante polja moraju se unijeti u određenom formatu.
Konstante niza mogu sadržavati brojeve, tekst, logičke vrijednosti poput TRUE ili FALSE ili vrijednosti pogreške poput # N / A. Jedna konstanta niza može sadržavati vrijednosti različitih vrsta, na primjer (1,3,4; TRUE, FALSE, TRUE). Brojevi u konstantama niza mogu biti cjelobrojni, decimalni ili eksponencijalni. Tekst mora biti priložen dvostrukim navodnicima, na primjer "utorak".
Provjerite jesu li ispunjeni sljedeći zahtjevi prilikom formatiranja konstanti niza.
Konstante su zatvorene u vitičaste zagrade ( { } ).
Stupci su odvojeni zarezima ( , ). Na primjer, za predstavljanje vrijednosti 10, 20, 30 i 40 unesite (10,20,30,40). Ova konstanta niza je matrica 1 prema 4 i odgovara referenci od jednog retka i četiri stupca.
Vrijednosti ćelija iz različitih redaka odvojene su zarezom ( ; ). Na primjer, da biste prikazali vrijednosti 10, 20, 30, 40 i 50, 60, 70, 80 u ćelijama jedna ispod druge, možete stvoriti konstantu niza 2 na 4: (10,20,30, 40; 50, 60,70,80).
Brisanje formule
Zajedno s formulom brišu se i rezultati njezinog izračuna. Međutim, možete izbrisati samu formulu i rezultat njezina izračunavanja ostaviti kao vrijednost u ćeliji.
Korištenje dvotočke ( : ), reference na prvu i zadnju ćeliju u rasponu su odvojene. Na primjer: A1: A5.
Navedeni potrebni argumenti
Funkcija može imati tražene i neobavezne argumente (oni su u sintaksi označeni uglastim zagradama). Moraju se unijeti svi potrebni argumenti. Također, pokušajte ne unositi previše argumenata.
Formula ne sadrži više od 64 razine ugniježđenja funkcija
Razine gniježđenja ne smiju prelaziti 64.
Nazivi knjiga i listova zatvoreni su u pojedinačne citate
Ako imena listova ili radnih knjiga na koje referencirate ćelije ili vrijednosti sadrže neabecedne znakove, morate ih priložiti u pojedinačne navodnike ( " ).
Naveden je put do vanjskih knjiga
Brojevi uneseni bez formatiranja
Znakove dolara ne možete koristiti prilikom unosa brojeva u formulu jer se oni koriste za označavanje apsolutnih referenci. Na primjer, umjesto vrijednosti $1000 treba ući 1000 .
Važno: Izračunati rezultati iz formula i nekih funkcija Excel-ovog radnog lista mogu se malo razlikovati na x86 ili x86-64 Windows računalima i Windows RT računalima sa ARM-om.
Dugo se čitav protok dokumenata odvijao pomoću računala i posebnih aplikativnih programa. U pravilu gotovo sve organizacije, studenti itd. Za tipkanje ili izvršavanje izračuna koriste uredski programski paket koji je razvio Microsoft. Da biste napravili visokokvalitetne tablice u kojima će se vrijednosti automatski preračunavati prema određenim pravilima, morate temeljito proučiti formuleExcel s primjerimaradi pojednostavljenja izračuna.
Neke stanice u tablici izračunavaju se na temelju statistike koja se unosi u druge stanice. Primjerice, ako je potrebno izračunati trošak nekoliko proizvoda prodanih na računu, količina se pomnoži s cijenom, a rezultat je željena vrijednost. IZ proračunske tablice to je puno lakše učiniti, jer se izradom jednog predloška, \u200b\u200ba zatim promjenom nekih statističkih vrijednosti, vrši ponovni izračun. Da bi rezultat bio točan, morate temeljito znati pravilno unijeti formule, jer će u suprotnom izračun biti netočan. Razmotrimo nekoliko primjera jednostavnih formula koje se najčešće koriste, dok će one jasno pokazati ispravnost njihove kompilacije.
Za najjednostavniji primjer izračunajmo zbroj dviju vrijednosti. Da biste to učinili, u ćeliju A1 napišite broj 5, a u B1 broj 3. Da bi se zbroj izračunao automatski, u ćeliju C1 napišite sljedeću kombinaciju znakova koja će biti formula: \u003d SUM (A1; B1).
Kao rezultat, izračunata vrijednost prikazat će se na zaslonu. Naravno, nije teško izračunati takve vrijednosti, ali ako je broj pojmova, na primjer, oko 10, prilično je teško to učiniti. Kada promijenite jednu od unesenih vrijednosti, rezultat će se automatski ponovno izračunati.
Da se ne bi zbunilo gdje je i koja je formula napisana, red se prikazuje odmah iznad tablice u kojoj je zapisana vrijednost ćelije (bilo statistička ili izračunska formula).
Kako bismo si zakomplicirali zadatak, u ćelije A1 i B1 uvodimo razlomljene brojeve koje je puno teže izračunati. Kao rezultat, C1 će se ponovno izračunati i prikazati ispravna vrijednost.
U programu Excel ne možete raditi samo zbrajanje, već i druge računske operacije: oduzimanje, množenje, dijeljenje. Formula uvijek treba počinjati simbolom "\u003d" i ukazivati \u200b\u200bna takozvane koordinate ćelija (u ovom primjeru to su A1 i B1).
Stvorite formule sami
U gornjem primjeru, formula \u003d SUM (A1; B1) omogućuje vam dodavanje dvije vrijednosti. Simbol "\u003d" podrazumijeva se kao početak formule, SUM je servisna riječ koja označava izračun zbroja, A1 i B1 su koordinate statičkih vrijednosti koje djeluju kao pojmovi i moraju biti odvojene zarezom ";".
Razmotrimo primjer dodavanja tri ćelije A1, B1, C1. Za to je napisana gotovo identična formula, ali treća ćelija je dodatno naznačena u zagradama. Kao rezultat, potrebno je upisati sljedeću kombinaciju \u003d SUM (A1; B1; C1) u ćeliju D1 kako bi se izveo izračun.
Ako postoji dosta pojmova koji idu u jedan redak (stupac), na primjer 10, formula se može pojednostaviti određivanjem raspona koordinata. Kao rezultat, formula će izgledati ovako: \u003d SUM (A1: A10). Kao ćeliju za prikaz željenog rezultata odaberite ćeliju B6. Kao rezultat, dobit ćemo rezultat prikazan na snimci zaslona.
Da biste pomnožili sve naznačene vrijednosti u stupcu, u formulu je umjesto SUM potrebno napisati PROIZVOD.
Napomena: ako korisnik treba izvršiti zbrajanje ili drugu aritmetičku operaciju s nekoliko stupaca i redaka, možete odrediti koordinate dijagonalno nasuprot i izračunavat će se niz vrijednosti (za izračun umnoška vrijednosti 10 redaka u stupci A, B, C, formula će izgledati ovako: \u003d PROIZVOD (A1: C10)).
Kombinirane formule
Proračunske tablice mogu izvoditi ne samo jednostavne aritmetičke izračune, već i složene matematičke izračune.
Na primjer, izračunajmo zbroj vrijednosti i pomnožimo ga s faktorom 1,4 ako je vrijednost tijekom zbrajanja bila manja od 90. Ako je rezultat veći od 90, treba ga pomnožiti s 1,5. U ovom slučaju, formula već sadrži nekoliko operatora i izgleda puno složenije: \u003d IF (SUM (A1: C1)<90;СУММ(А1:С1)*1,4;СУММ(А1:С1)*1,5).
Ovdje su već korištene dvije funkcije umjesto jedne: IF i SUM. Prva naredba je uvjetna izjava koja radi ovako:
- ako je zbroj triju vrijednosti (A1: C1) veći od 90, izvodi se množenje sa 1,4, odnosno s prvim koeficijentom koji je naveden nakon uvjeta;
- ako je (A1: C1) manje od 90 (kao u našem danom primjeru), odnosno uvjet nije zadovoljen, ta se vrijednost množi s drugim koeficijentom.
U našem primjeru postoje samo dvije funkcije, ali mogu biti i puno više, program je u tome praktički neograničen.
Ugrađene funkcije u proračunskim tablicama
Da ne biste propisivali službene riječi, možete upotrijebiti posebnu sposobnost za instaliranje funkcije bez biranja. Odnosno, za izračunavanje zbroja vrijednosti možete koristiti ovu značajku i odabrati određenu radnju za navedeni raspon vrijednosti. Za to postoji poseban gumb, prikazan na snimci zaslona.
Vrijedno je napomenuti da tamo postoji poprilično funkcija i mnoge od njih možda neće trebati, ali neke od njih redovito će se koristiti.
Sve su funkcije kategorizirane kako bi lakše pronašli onu koju želite.
VLOOKUP funkcija
Ako se tablica sastoji od nekoliko stotina redaka i nije uvijek moguće pronaći željeni položaj, možete koristiti funkciju VLOOKUP koja će pretražiti i vratiti rezultat u ćeliju u kojoj je napisana formula.
Na primjer, ako trebamo pronaći prezime kojem je dodijeljen serijski broj 007, moramo slijediti korake koji su jasno prikazani na snimci zaslona.
Vrijednost za koju se traži odabire se kao prvi argument. Opseg vrijednosti za traženje odabran je kao drugi argument. Broj 2 označava da bi se vrijednost iz drugog stupca trebala prikazati u ovoj ćeliji.
Rezultat je sljedeći rezultat.
Zaokruživanje vrijednosti
U Excelu možete točno zaokružiti kako biste pojednostavili izračune. Za to postoji posebna funkcija "ROUNDUP". Na primjer, ako trebamo zaokružiti broj naveden u ćeliji A1, unesite u susjednu formulu \u003d ROUNDUP (A1; 0), gdje drugi argument pokazuje kojim redom decimalnih mjesta je zamorno zaokruživanje (ako navedete nulu, ukupna vrijednost bit će cijeli broj). Rezultat je sljedeći rezultat.
Kao što možete vidjeti sa snimke zaslona, \u200b\u200bdošlo je do zaokruživanja. Da bi se izvodilo u manjem, postoji funkcija "OKRUGLO".
I premda se na prvi pogled čini da je Excel proračunske tablice prilično teško razumjeti, s njima trebate raditi samo kratko vrijeme i smatrati da ta izjava nije u potpunosti točna. Osim toga, dobrobiti od njih teško je precijeniti.
Kao što znate, Excel urednik proračunskih tablica izvorno je bio namijenjen izvođenju matematičkih, algebarskih i drugih izračuna, za što se koristi unos posebnih formula. Međutim, u svjetlu kako napisati formulu u Excelu, mora se imati na umu da se većina operacija unosa radikalno razlikuje od svega što je uobičajeno koristiti u svakodnevnom životu. Odnosno, same formule imaju malo drugačiji oblik i koriste potpuno različite operatore koji se koriste pri pisanju standardnih izračuna.
Razmotrimo pitanje kako napisati formulu u Excelu, koristeći nekoliko jednostavnih primjera, ne dotičući se složenih operacija, za koje trebate proučiti program dovoljno duboko da biste ga razumjeli. No čak i prethodno znanje pružit će svakom korisniku razumijevanje osnovnih principa korištenja formula za različite slučajeve.
Kako napisati formulu u Excelu: osnovni pojmovi
Dakle, unos vrijednosti formule u program nešto se razlikuje od standardnih operacija, korištenih simbola i korištenih operatora. Pri rješavanju problema kako napisati formulu u Excelu, potrebno je nadograditi osnovne pojmove koji se koriste u gotovo svim računalnim sustavima.
Činjenica je da stroj ne razumije unos kombinacije poput "2 x 2" ili stavljanje zajedničke komponente izvan zagrada ("2 + 2) 5"). Za to je osigurano nekoliko vrsta simbola koji su predstavljeni u donjoj tablici, ne računajući logičke operatore.
U ovom slučaju prioritet izvođenja operacija započinje od stupnja i završava zbrajanjem i oduzimanjem. Osim toga, iako se Excel može koristiti kao obični kalkulator, za izračune u pravilu morate navesti brojeve stanica ili raspone stanica. Podrazumijeva se da format podataka u bilo kojoj takvoj ćeliji mora biti postavljen na odgovarajući (barem numerički).
Zbroj i razlika
Kako napisati formulu zbroja ili razlike u Excelu? Dakle, krenimo s najjednostavnijim, kada trebate izračunati iznos. U redak formula (štoviše, za sve operacije) prvo se unosi znak jednakosti, nakon čega se unosi željena formula. U slučaju konvencionalnog kalkulatora, možete odrediti za instaliranu ćeliju "\u003d 2 + 2".
Ako se zbrajanje izvodi za vrijednosti unesene izravno u druge ćelije (na primjer, A1 i A2), formula postaje "\u003d A1 + A2". Nerijetko se zagrade koriste za upotrebu dodatnih operatora. Za razliku - ista stvar, samo s minusom umjesto plusa.
Kada trebate odrediti brojeve ćelija ili njihov raspon, može se koristiti posebna naredba zbroja (u ruskoj verziji "SUM", na engleskom - SUM). Kada specificirate nekoliko ćelija, to izgleda ovako: "\u003d SUM (A1; A2)", za raspon - "SUM (A1: A10)", pod uvjetom da trebate izračunati zbroj svih brojeva u ćelijama od prvog do deseti. U principu, ako postavite aktivnu ćeliju koja se nalazi odmah iza posljednje u stupcu s početnim vrijednostima, ne morate unijeti formulu, već jednostavno kliknite gumb za automatsko zbrajanje.
Množenje, dijeljenje i potenciranje
Sada ćemo vidjeti kako napisati formulu za množenje ili dijeljenje u Excelu. Redoslijed je isti kao kod unosa zbroja ili razlike, samo se operateri razlikuju.
Za proizvod se koristi obrazac "\u003d A1 * A2", za količnik - "A1 / A2". Inače, to su potpuno iste naredbe koje se mogu pronaći prilikom korištenja standardnog Windows kalkulatora.
Simbol "^" koristi se za potenciranje. Za vrijednost u ćeliji A1, koju na primjer treba kvadrat, primjenjuje se formula "\u003d A1 ^ 2".
Postotni izračuni
Sa zanimanjem, ako se ne dotaknete složenih izračuna, sve je također jednostavno. Kako napisati formulu s postocima u Excelu?
Dovoljno je unijeti formulu oblika "\u003d A1 * 5%", nakon čega ćete dobiti upravo tih pet posto vrijednosti u odgovarajućoj ćeliji.
Korištenje unosa formule na temelju odabira ćelije
Ali sve se to odnosilo na ručno dodjeljivanje ili takozvani izravni unos formula (izravni ili izravni unos). Zapravo je ponekad korisno koristiti miš i tipku Ctrl.
Držeći pritisnutu tipku miša, jednostavno možete odabrati željene stanice tako da prvo unesete traženi izračun u traku s formulama. Stanice će se dodati izravno na traku s formulama. No, ovisno o vrsti formule, ponekad se zagrade moraju unijeti ručno.
Apsolutni, relativni i mješoviti tipovi stanica
Treba posebno napomenuti da program može koristiti nekoliko vrsta stanica, a da ne spominjemo podatke koje sadrže.
Apsolutna ćelija je nepromijenjena i označava se kao $ A $ 1, relativna je referenca na uobičajeno mjesto (A1), mješovito - postoji kombinacija referenci i na apsolutne i na relativne ćelije ($ A1 ili A $ 1). Tipični se formati obično koriste prilikom stvaranja formula kada su podaci uključeni u različite listove knjige ili čak u različite datoteke. 
VLOOKUP formule
Napokon, pogledajmo kako napisati VLOOKUP formulu u Excelu. Ova tehnika omogućuje vam umetanje podataka iz jednog raspona u drugi. U ovom je slučaju metoda donekle slična onoj koja se koristi za rješavanje problema kako napisati formulu "Stanje" u Excelu, koja koristi simbole prikazane u gornjoj tablici.
Općenito, takvi su izračuni poput jednostavnog filtra primijenjenog na stupce, gdje želite filtrirati samo točne vrijednosti, a ne približne vrijednosti.
U ovoj se opciji, najprije kroz "Čarobnjak za funkcije", koristi raspon vrijednosti izvorne (prve) tablice, drugi raspon s fiksiranjem sadržaja (F4) naveden je u polju "Tablica", a zatim stupac označen je broj, a vrijednost "FALSE" postavljena je u polju prikaza intervala ako zapravo prilikom filtriranja stvarno trebate dobiti samo točne, a ne približne vrijednosti. U pravilu se takve formule više koriste u skladištu ili u računovodstvu kada nije moguće instalirati nikakve specijalizirane softverske proizvode.
Zaključak
Ostaje reći da ovdje nisu opisane sve formule koje se mogu koristiti u Excelovom uređivaču proračunskih tablica. To je tako reći samo osnova. Ustvari, ako se dublje bavite trigonometrijom ili izračunavanjem logaritama, matrica ili čak tenzorskih jednadžbi, sve izgleda puno složenije. No, da bi se sve to proučilo, potrebno je temeljito proučiti priručnik za sam urednik. I to više nije pitanje činjenice da se i najjednostavnije logičke igre mogu stvoriti u programu Excel na temelju promjene podataka. Kao primjer možemo navesti istu "zmiju", koja u početku nije imala nikakve veze s uređivačem proračunskih tablica, ali su je entuzijasti reproducirali u svom polju u Excelu.
U ostalom, treba jasno razumjeti da će, nakon proučavanja primitivnih formula ili radnji s podacima, biti moguće lako svladati složenije izračune, recimo, stvaranjem unakrsnih referenci, upotrebom raznih vrsta skripti ili VB skripte itd. Za sve to treba vremena, pa ako želite maksimalno proučiti program i sve njegove mogućnosti, morat ćete se oznojiti teoretskim dijelom.