Definiranje i stvaranje formule

U ovoj lekciji upoznajemo vas s osnovnim pravilima za izradu formula i korištenjem funkcija. Osjećamo se kao jedan od najboljih načina učenja kroz praksu, tako da pružamo nekoliko primjera i detaljno ih objašnjavamo. Teme koje ćemo pokriti uključuju:

ŠKOLSKA NAVIGACIJA

Zašto trebate formule i funkcije?
Definiranje i stvaranje formule
Relativna i apsolutna referenca ćelije i oblikovanje
Korisne funkcije koje trebate upoznati
Pretraživanja, grafikoni, statistika i stožerne tablice

redaka i stupaca
primjer matematičke funkcije: SUM ()
operatori
prednost operatora
primjer financijske funkcije: PMT (), plaćanje kredita
pomoću funkcije "string" ("string" je skraćenica za "niz teksta") unutar funkcije formule i gniježđenja

Formule su mješavina "funkcija", "operatora" i "operanada". Prije nego napišemo nekoliko formula, moramo stvoriti funkciju, ali prije nego što možemo stvoriti funkciju, prvo trebamo razumjeti notaciju retka i stupca.

Redovi i stupci

Da biste razumjeli kako napisati formule i funkcije, morate znati o redovima i stupcima.

Redovi se prikazuju vodoravno, a stupci se izvode okomito. Da zapamtite što je to, pomislite na stup koji drži krov - stupovi idu gore-dolje i tako redovi idu lijevo-desno.

Stupci su označeni slovima; redaka po brojevima. Prva ćelija u proračunskoj tablici je A1 značenje A, redak 1. Stupci su označeni s A-Z. Kada abeceda ponestane, Excel postavlja još jedno slovo ispred: AA, AB, AC… AZ, BA, BC, BC, itd..

Primjer: zbroj funkcija ()

Sada ćemo pokazati kako koristiti funkciju.

Funkcije koristite tako da ih izravno utipkate u čarobnjak za funkcije ili pomoću čarobnjaka za funkcije. Čarobnjak za funkcije otvara se kada odaberete funkciju iz izbornika "Formule" iz "Library Library". U suprotnom, možete unijeti = u ćeliju, a zgodan padajući izbornik omogućit će vam odabir funkcije.

Čarobnjak će vam reći koje argumente trebate dati za svaku funkciju. On također pruža vezu do online uputa ako vam je potrebna pomoć u razumijevanju što funkcija funkcionira i kako je koristiti. Na primjer, ako upišete = sum u ćeliju, in-line čarobnjak će vam pokazati koji su argumenti potrebni za funkciju SUM.

Kada upišete funkciju, čarobnjak je u redu ili pravo na prstima. Kada odaberete funkciju iz izbornika "Formule", čarobnjak je skočni okvir. Slijedi pop-up čarobnjak za funkciju SUM ().

Za našu prvu funkciju upotrijebimo SUM (), koji dodaje popis brojeva.

Pretpostavimo da imamo ovu proračunsku tablicu koja sadrži planove za proračun za obiteljski odmor:

Za izračun ukupnih troškova možete napisati = b2 + b3 + b4 + b5, ali je lakše koristiti funkciju SUM ().

U Excelu potražite simbol Σ u gornjem lijevom kutu ekrana u Excelu kako biste pronašli gumb AutoSum (matematičari koriste grčko slovo Σ za dodavanje niza brojeva).

Ako je pokazivač ispod brojeva obiteljskog proračuna, Excel je dovoljno pametan da zna da želite zbrojiti popis brojeva iznad mjesta na kojem ste postavili pokazivač, tako da ističe brojeve.

Pritisnite “enter” za prihvaćanje raspona odabranog u Excelu ili pomoću kursora za promjenu odabranih ćelija.

Ako pogledate što je Excel stavio u proračunsku tablicu možete vidjeti da je napisao ovu funkciju:

U ovoj formuli Excel zbraja brojeve od B2 do B9. Imajte na umu da smo ostavili neku sobu ispod retka 5 kako biste mogli dodati proračun za obiteljski odmor - cijena će sigurno porasti jer popis dječjih onoga što oni žele raditi i gdje žele ići dalje raste!

Funkcije matematike ne rade s slovima, pa ako stavite slova u stupac rezultat se prikazuje kao "#NAME?" Kao što je prikazano u nastavku.

#IME? pokazuje da postoji neka vrsta pogreške. To može biti bilo koji broj stvari uključujući:

referenca loše ćelije
pomoću slova u matematičkim funkcijama
izostavljanje traženih argumenata
pogrešno ime funkcije pravopisa
ilegalne matematičke operacije poput podjele na 0

Najlakši način da odaberete argumente u izračunu je da koristite miš. Možete dodati ili ukloniti s popisa argumenata funkciji povećanjem ili smanjivanjem okvira koji Excel crta kada pomičete miša ili kliknete na drugu ćeliju.

Kliknuli smo na vrh kvadrata uvučen od strane Excela da uzmemo “avionske karte” iz proračuna. Možete vidjeti simbol križa koji možete nacrtati da bi odabrani raspon bio veći ili manji.

Pritisnite “enter” za potvrdu rezultata.

Operatori proračuna

Postoje dvije vrste operatora: matematika i usporedba.

Matematički operater	definicija
+	dodatak
-	oduzimanje, ili negiranje, npr. 6 * -1 = -6
*	množenje
/	podjela
%	posto
^	eksponent, npr. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Postoje i drugi operatori koji nisu povezani s matematikom poput "&" što znači spojiti (spojiti od kraja do kraja) dva niza. Na primjer = "Excel" i "je zabavno" jednako "Excel je zabavno".

Sada gledamo operatore usporedbe.

Operator usporedbe	definicija
=	jednako, npr., 2 = 4 ili "b" = "b"
>	veći od, npr. 4> 2 ili "b"> "a"
<	manje od, npr < 4 or “a” < “b”
> =	veći ili jednak - drugi način za razmišljanje je> = znači ili > ili =.
<=	manje ili jednako.
	nije jednako, npr. 46

Kao što možete vidjeti gore, operateri usporedbe rade s brojevima i tekstom.

Imajte na umu, ako unesete = "a"> "b" u ćeliju, reći će se "FALSE" jer "a" nije veći od "b". "B" dolazi nakon "a" u abecedi, tako da "a" > "B" ili "B"> "a."

Prednost naloga naručitelja

Prednost reda je ideja iz matematike. Excel mora slijediti ista pravila kao i matematika. Ova tema je složenija, zato uzmite dah i zaronimo.

Redoslijed naloga je redoslijed kojim računalo izračunava odgovor. Kao što smo objasnili u Lekciji 1, područje kruga je πr², koja je ista kao π * r * r. to je ne (Πr)².

Dakle, morate razumjeti redoslijed naloga kada pišete formulu.

Općenito možete to reći:

Excel najprije procjenjuje stavke u zagradama koje rade iznutra prema van.
Zatim koristi pravila matematike s redoslijedom reda.
Kada dvije stavke imaju istu prednost, Excel radi slijeva nadesno.

Prednost matematičkih operatera prikazana je u nastavku, u silaznom redoslijedu.

(i)	Kada se koriste zagrade, one nadjačavaju uobičajena pravila prvenstva. To znači da će Excel najprije izvršiti ovaj izračun. To ćemo objasniti u nastavku.
-	Negacija, npr. -1. To je isto kao i množenje broja za -1. -4 = 4 * (-1)
%	Postotak, znači pomnoženo sa 100. Na primjer, 0,003 = 0,3%.
^	Eksponent, npr. 10 ^ 2 = 100
* i /	Pomnožite i podijelite. Kako dva operatera mogu imati istu prednost? To samo znači da ako formula ima dva operatora s istim prioritetom, izračun se vrši s lijeva na desno.
+ i -	Dodavanje i oduzimanje.

Postoje i druga pravila prvenstva koja se odnose na nizove i referentne operatore. Za sada ćemo se samo držati onoga što smo upravo pokrili. Pogledajmo sada neke primjere.

Primjer: Izračunavanje područja kruga

Područje kruga je= PI () * radijus ^ 2.

Gledajući na gornju tablicu vidimo da eksponati dolaze prije množenja. Tako računalo prvo izračunava radijus ^ 2, a zatim ga višestruko rezultira Pi.

Primjer: Izračunavanje povećanja plaće

Recimo da vaš šef odluči da radite sjajan posao i on ili ona će vam dati povišicu od 10%! Kako biste izračunali svoju novu plaću?

Prvo, zapamtite da množenje dolazi prije dodatka.

Je li to = plaća + plaća * 10% ili je = plaća + (plaća * 10%)?

Pretpostavimo da je vaša plaća 100 dolara. Uz povišicu od 10%, vaša nova plaća bit će:

= 100 + 100 * 10% = 100 + 10 = 110

Možete ga napisati i ovako:

= 100 + (100x10%) = 100 + 10 = 110

U drugom slučaju, izričiti smo redoslijedom prioriteta pomoću zagrada. Zapamtite da se zagrade procjenjuju prije bilo koje druge operacije.

Usput, lakši način pisanja je = plaća * 110%

Zagrade se mogu ugnijezditi jedna u drugu. Dakle, kad pišemo (3 + (4 * 2)), radeći iznutra prema van, prvo izračunamo 4 * 2 = 8, a zatim dodamo 3 + 8 da dobijemo 11.

Još nekoliko primjera

Evo još jednog primjera: = 4 * 3 / 2. Koji je odgovor?

Vidimo iz pravila u tablici da * i / imaju jednak prioritet. Tako Excel radi s lijeva na desno, prvo 4 * 3 = 12, a zatim dijeli to s 2 da bi dobio 6.

Ponovno možete to izričito učiniti pisanjem = (4 * 3) / 2

Što je s = 4 + 3 * 2?

Računalo vidi operatore * i +. Tako slijedeći pravila prvenstva (množenje dolazi prije dodavanja) prvo izračunava 3 * 2 = 6, a zatim dodaje 4 da bi dobilo 10.

Ako ste htjeli promijeniti redoslijed prioriteta, napisali biste = (4 + 3) * 2 = 14.

Što je s ovom = -1 ^ 3?

Tada je odgovor -3 jer je računalo izračunato = (-1) ^ 3 = -1 * -1 * -1 = -1.

Zapamtite da je negativno vrijeme negativno pozitivno, a negativno vrijeme pozitivno negativno. Ovo možete vidjeti ovako (-1 * -1) * -1 = 1 * -1 = -1.

Dakle, postoji nekoliko primjera matematičkog reda i prioriteta, nadamo se da će vam pomoći razjasniti nekoliko stvari o tome kako Excel izvršava kalkulacije (a to je vjerojatno dovoljno matematike da traje cijeli život za neke od vas).

Primjer: Funkcionalno plaćanje kredita (PMT)

Pogledajmo primjer za izračunavanje plaćanja kredita.

Počnite izradom novog radnog lista.

Formatirajte brojeve znakovima dolara i upotrijebite nula decimalnih mjesta jer nas trenutno ne zanimaju centi, jer oni nisu bitni kada govorite o dolarima (u sljedećem poglavlju detaljno ćemo istražiti kako formatirati brojeve). Na primjer, da biste formatirali kamatnu stopu, desnom tipkom miša kliknite ćeliju i kliknite "format ćelije". Odaberite postotak i upotrijebite 2 decimalna mjesta.

Isto tako, formatirajte ostale ćelije za “valutu” umjesto postotka i odaberite “broj” za pojam kredita.

Sada imamo:

Dodajte funkciju SUM () u "ukupne" mjesečne troškove.

Napomena, hipoteka ćelija nije uključena u ukupan broj. Excel ne zna da želite uključiti taj broj, jer tamo nema vrijednosti. Zato budite oprezni i proširite funkciju SUM () na vrh pomoću pokazivača ili tipkanja E2 gdje piše E3 kako biste uključili hipoteku u sumu.

Postavite pokazivač u ćeliju plaćanja (B4).

U izborniku Formule odaberite padajući izbornik "Financial" i odaberite funkciju PMT. Čarobnjak će se pojaviti:

Koristite kursor za odabir "rate.", "Nper" (pojam kredita), "Pv" ("sadašnja vrijednost" ili iznos kredita). Primijetite da kamatnu stopu morate podijeliti za 12, jer se kamata obračunava mjesečno. Također trebate umnožiti zajam pojam u godinama za 12 da biste dobili zajam pojam u mjesecima. Pritisnite “OK” za spremanje rezultata u proračunsku tablicu.

Primijetite da je plaćanje prikazano kao negativni broj: -1013.37062. Da bi bio pozitivan i dodan mjesečnim troškovima, pokažite na stanicu hipoteke (E2). Upišite "= -", a zatim pomoću pokazivača pokažite na polje plaćanja. Dobivena formula je = -B4.

Sada proračunska tablica izgleda ovako:

Vaši mjesečni troškovi su $ 1,863 - Ouch!

Primjer: Funkcija teksta

Ovdje ćemo pokazati kako koristiti funkcije unutar funkcija formule i teksta.

Pretpostavimo da imate popis studenata kao što je prikazano u nastavku. Ime i prezime se nalaze u jednom polju odvojenom zarezom. Moramo staviti posljednja i čvrsta imena u zasebne ćelije. Kako ćemo to učiniti?

Da biste riješili ovaj problem, morate koristiti algoritam, tj. Korak po korak za to.

Na primjer, pogledajte "Washington, George". Postupak razdvajanja u dvije riječi bio bi:

Izračunajte duljinu niza.
Pronađite položaj zareza (to pokazuje gdje završava jedna riječ, a druga počinje).
Kopirajte lijevu stranu niza do zareza.
Kopirajte desnu stranu niza od zareza do kraja.

Razgovarajmo o tome kako to učiniti s "Georgeom Washingtonom" korak po korak u Excelu.

Izračunajte duljinu niza s funkcijom = LEN (A3) - rezultat je 18.
Zatim pronađite položaj zareza unoseći tu funkciju = FIND (“,”, A3 ”) - rezultat je 11.
Sada uzmite lijevu stranu niza do zareza i stvorite tu ugniježđenu formulu koristeći rezultat iz koraka 1: = LIJEVO (A3, FIND (“,”, A3) -1). Napominjemo da moramo oduzeti 1 od duljine jer FIND daje položaj zareza.

Evo kako sve to izgleda kada su sve funkcije smještene zajedno u formulu. U ćeliji B3 možete vidjeti da ova formula uzima sve podatke iz ćelije A3 i unosi u nju “Washington”.

Dakle, imamo "Washington", sada moramo dobiti "Georgea". Kako ćemo to učiniti?

Imajte na umu da smo mogli sačuvati rezultat iz 1. koraka u ćeliji samoj, recimo, B6, a zatim napisati jednostavniju formulu = LIJEVO (A3, B6-1). Ali to koristi jednu ćeliju za povremeni korak.

Zapamtite položaj zareza ili je ponovno izračunajte.
Izračunajte duljinu niza.
Broji znakove od kraja niza do zareza.

Uzmite broj znakova iz 3. koraka i oduzmite jedan da biste izostavili zarez i razmak.

Učinimo to korak po korak.

Odozgo, to je = FIND (“,”, A3 ”)
Duljina niza je = LEN (A3)
Morat ćete upotrijebiti matematiku da biste pronašli broj znakova: = LEN (A3) - FIND (“,”, A3) - 1
Desna strana niza koji želimo je = DESNO (A3, LEN (A3) - FIND (“,”, A3) - 1)

Vaša proračunska tablica sada bi trebala izgledati slično slici zaslona u nastavku. Formule smo kopirali kao tekst na dno proračunske tablice kako bismo ih lakše čitali i vidjeli.

To je bilo malo teško, ali samo trebate pisati ove formule jednom.

Slijedi…

To zaključuje našu lekciju za danas. Sada biste trebali imati prilično čvrsto razumijevanje formula i funkcija, redaka i stupaca, i način na koji se sve to može iskoristiti kroz nekoliko određenih primjera..

Sljedeće u lekciji 3, govorit ćemo o referencama i oblikovanju ćelija, kao i pomicanju i kopiranju formula kako ne biste morali ponovno pisati svaku formulu!