Definiranje i stvaranje formule

2024 Autor: Geoffrey Carr | [email protected]. Zadnja promjena: 2024-01-31 12:52

U ovoj lekciji upoznat ćemo vas s osnovnim pravilima za izradu formula i korištenje funkcija. Smatramo da je jedan od najboljih načina naučavanja kroz praksu pa imamo nekoliko primjera i detaljno ih objasnimo. Teme koje ćemo obuhvatiti uključuju:

NAVIGACIJA ŠKOLE

Zašto trebate formule i funkcije?
Definiranje i stvaranje formule
Relativna i apsolutna referenca stanica i oblikovanje
Korisne funkcije koje biste trebali upoznati
Lookups, Charts, Statistics i Pivot tablice

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

Formule su mješavina "funkcija", "operatora" i "operandi". Prije nego što napišemo nekoliko formula, moramo stvoriti funkciju, ali prije nego što možemo stvoriti funkciju, najprije moramo razumjeti zapisivanje redaka i stupaca.

Redaka i stupaca

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

Redci vode vodoravno i stupci idu uspravno. Da biste zapamtili koji je to, razmišljajte o stupcu koji drži krov - stupovi se spuštaju prema dolje, a redovi idu lijevo-desno.

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

Primjer: zbroj zbroj ()

Sada ćemo pokazati kako koristiti funkciju.

Koristite funkcije tako da ih upišete izravno ili pomoću čarobnjaka za funkcije. Čarobnjak za funkcije otvara se kada odaberete funkciju iz izbornika "Formula" u "Funkcijskoj knjižnici". U suprotnom, možete upisati = u ćeliju i praktični padajući izbornik omogućit će odabir funkcije.

Čarobnjak vam govori o argumentima koje morate osigurati za svaku funkciju. Ona također pruža vezu na on-line upute ako vam je potrebna pomoć u razumijevanju funkcije i načina na koji to funkcionira. Na primjer, ako upišete = sumu u ćeliju, in-line čarobnjak će vam pokazati koji su argumenti potrebni za funkciju SUM.

Kada upišete neku funkciju, čarobnjak je u ravnini ili točno prstima. Kada odaberete funkciju iz izbornika "Formulas", čarobnjak je skočni okvir. Evo pop-up čarobnjaka za SUM () funkciju.

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

Pretpostavimo da tu proračunsku tablicu sadrži planove za planiranje proračuna za obiteljski odmor:

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

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

Ako je kursor ispod proračuna za obiteljski proračun, Excel je dovoljno pametan da zna da želite zbrojiti popis gore navedenih brojeva gdje ste postavili pokazivač, tako da ističe brojeve.

Pritisnite "enter" za prihvaćanje raspona odabranog od strane programa Excel ili pomoću kursora za promjenu odabranih ćelija.

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

U ovoj formuli Excel iznosi brojeve od B2 do B9. Imajte na umu da smo ostavili neku sobu ispod reda 5 da biste mogli dodati proračun za obiteljski odmor - cijena će zasigurno porasti kao popis djece onoga što žele i gdje žele ići dugo!

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

#IME? označava da postoji neka vrsta pogreške. To bi moglo biti bilo koji broj stvari, uključujući:

loša referenca
pomoću slova u matematičkim funkcijama
izostavljajući potrebne argumente
Ime pogrešnog naziva pogreške
ilegalne operacije matematike kao podjela prema 0

Najlakši način za odabir argumenata u proračunu je korištenje miša. Možete dodati ili ukloniti s popisa argumenata na funkciju povećanjem ili smanjivanjem okvira koji Excel izvlači kada premjestite miša ili kliknete na drugu ćeliju.

Kliknuli smo na vrh kvadrata kojeg je nacrtao Excel kako bi "proračunski putnički avion" izbacio. Možete vidjeti simbol križanja koji možete nacrtati da bi odabrani raspon bio veći ili manji.

Pritisnite "enter" za potvrdu rezultata.

Operatori obračuna

Postoje dvije vrste operatora: matematika i usporedba.

Operator matematike	definicija
+	dodatak
–	oduzimanje ili negacija, npr., 6 * -1 = -6
*	množenje
/	podjela
%	posto
^	eksponent, npr. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 =16

Postoje i drugi operateri koji nisu povezani s matematikom kao što je "&", što znači da se dva žica povezuju (pridružuju end-to-end). Na primjer, = "Excel" i "Zabava" jednako je "Excel je zabavan".

Sada gledamo operatore za usporedbu.

Operator usporedbe	definicija
=	jednako, npr., 2 = 4 ili "b" = "b"
>	veći od npr. 4> 2 ili "b"> "a"
<	manje od, npr., 2 <4 ili "a" <"b"
>=	veći ili jednak - drugi način da to mislimo je> = znači ili > ili =.
<=	manje ili jednako.
	nije jednak, npr. 4 <> 6

Kao što možete vidjeti gore, operatori za usporedbu rade s brojevima i tekstom.

Ako unesete = "a"> "b" u ćeliju, reći će "FALSE" jer "a" nije veći od "b". "B" dolazi nakon "a" u abecedi, pa "a" > "B" ili "B"> "a."

Prioritet operatora

Redoslijed redoslijeda je ideja iz matematike. Excel mora slijediti ista pravila kao i matematika. Ova je tema složenija, pa uzmite dah i ronimo se.

Redoslijed redoslijeda znači redoslijed kojim računalo izračunava odgovor. Kao što smo objasnili u lekciji 1, područje kruga je πr², što je isto kao i π * r * r. to je ne (Πr)².

Dakle, morate razumjeti redoslijed kada pišete formulu.

Općenito, možete reći ovo:

Excel prvo procjenjuje stavke u zagradama koji rade izvan nje.
Zatim koristi pravila reda prednosti matematike.
Kada dvije stavke imaju isti prioritet, Excel radi slijeva udesno.

Prednost operatera matematike prikazana je dolje, silazno.

(i)	Kada se koriste zagrade, one nadjačavaju uobičajena pravila prednosti. To znači da će Excel prvo napraviti ovaj izračun. Ovo ćemo dalje objasniti.
–	Negacija, npr. -1. To je isto kao i umnožavanje broja za -1. -4 = 4 * (-1)
%	Postotak, sredstvo se pomnoži sa 100. Primjerice, 0,003 = 0,3%.
^	Eksponent, npr. 10 ^ 2 = 100
* i /	Pomnožite i podijelite. Kako dva operatera imaju isti prioritet? To samo znači da ako formula ima još dva operatora s istom prednošću, tada se izračun obavlja lijevo desno.
+ i -	Dodatak i oduzimanje.

Postoje i druga pravila prednosti koja se odnose na žice i referentne operatore. Trenutačno ćemo se držati samo onoga što smo upravo pokrivali. Pogledajmo sada neke primjere.

Primjer: Izračunavanje područja kruga

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

Gledajući gornju tablicu vidimo da eksponenti dolaze prije množenja. Dakle, računalo prvo izračunava radijus ^ 2, a zatim višekratnike koje 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 novu plaću?

Prvo, zapamtite da umnožavanje dolazi prije dodavanja.

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

Pretpostavimo da vaša plaća iznosi 100 USD. Uz povišicu od 10%, vaša će nova plaća biti:

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

Možete je i zapisati ovako:

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

U drugom slučaju, redoslijed prioriteta izrađen je pomoću zagrada. Zapamtite da se zagrade izrađuju prije bilo koje druge operacije.

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

Rodene se mogu ugniježđivati jedna u drugoj. Dakle, kada pišemo (3 + (4 * 2)), radeći iznutra prema van, prvo izračunava 4 * 2 = 8, a zatim dodajte 3 + 8 da biste dobili 11.

Još nekoliko primjera

Evo još jedan primjer: = 4 * 3 / 2. Koji je odgovor?

Iz gore navedenih pravila vidimo da * i / imaju jednaku prednost. Tako Excel radi s lijeva na desno, najprije 4 * 3 = 12, a zatim dijeli za 2 da biste dobili 6.

Opet možete to učiniti eksplicitnim ako pišete = (4 * 3) / 2

Što je s = 4 + 3 * 2?

Računalo vidi operatore * i +. Dakle, slijedom pravila prvenstva (množenje dolazi prije dodavanja) najprije izračunava 3 * 2 = 6, a zatim 4 doda 10.

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

Što je s ovom jednom = -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, pozitivno je negativno. Možete vidjeti ovo ovako (-1 * -1) * -1 = 1 * -1 = -1.

Znači, postoji nekoliko primjera matematičkog poretka i prednosti, nadamo se da to pomaže razjasniti nekoliko stvari o tome kako Excel izvodi izračune (i to je vjerojatno dovoljno matematika za trajanje života za neke od vas).

Primjer: Funkcija plaćanja zajma (PMT)

Pogledajmo primjer kako bismo izračunali isplatu kredita.

Započnite s izradom novog radnog lista.

Oblikujte brojeve sa dolarskim znakovima i upotrijebite nula decimalna mjesta jer trenutno nismo zainteresirani za stope jer nisu važni kada govorite o dolarima (u sljedećem ćemo poglavlju istražiti kako detaljno oblikovati brojeve). Na primjer, za formatiranje kamatne stope kliknite desnom tipkom miša na ćeliju i kliknite "ćelije formata". Izaberite postotak i koristite 2 decimalna mjesta.

Slično tome, formatirajte druge ćelije za "valutu" umjesto postotka i odaberite "broj" za termin kredita.

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

Napomena, hipoteka stanica nije uključena u ukupnu. Excel ne zna da želite uključiti taj broj, jer tu nema vrijednosti. Stoga budite pažljivi da proširite funkciju SUM () na vrh bilo pomoću kursora ili upisivanjem E2 gdje piše E3 da uključi hipoteku u zbroj.

Postavite pokazivač u ćeliju za plaćanje (B4).

Na izborniku Formule odaberite padajući izbornik "Financial" i zatim odaberite PMT funkciju. Čarobnjak se pojavljuje:

Koristite kursor za odabir "rate", "nper" (pojam zajma), "Pv" ("sadašnja vrijednost" ili iznos zajma). Imajte na umu da kamatnu stopu morate podijeliti za 12, budući da se kamata mjesečno obračunava. Također morate umnožiti zajam pojam u godinama za 12 da biste dobili zajam mandat u mjesecima. Pritisnite "OK" za spremanje rezultata u proračunsku tablicu.

Napominjemo da je uplata prikazana kao negativni broj: -1013.37062. Da biste ga pozitivno i dodali mjesečnim troškovima, pokažite na hipoteku (E2). Upišite "= -", a zatim upotrijebite pokazivač da biste uputili na polje za plaćanje. Dobivena formula je = -B4.

Tvoj mjesečni trošak iznosi 1.863 dolara.

Primjer: funkcija teksta

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

Pretpostavimo da imate popis studenata kako je prikazano u nastavku. Ime i prezime su u jednom polju razdvojeni zarezom. Trebamo staviti posljednja i čvrsta imena u zasebne ćelije. Kako da to učinimo?

Da biste riješili taj problem, trebate upotrijebiti algoritam - tj. Korak-po-korak postupak za to.

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

Izračunajte duljinu niza.
Pronađite položaj zareze (to pokazuje gdje jedna riječ završava, a druga počinje).
Kopirajte lijevu stranu niza do zarez.
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 pomoću funkcije = LEN (A3) - rezultat je 18.
Sada pronađite položaj zarezu unosom ove funkcije = FIND (",", A3 ") - rezultat je 11.
Sada uzmite lijevu stranu niza do zarez i stvorite ovu ugniježđenu formulu pomoću rezultata iz koraka 1: = LEFT (A3, FIND (",", A3) -1). Napominjemo, moramo oduzeti 1 od duljine jer FIND daje položaj zareze.

Evo kako izgleda sve kada se sve funkcije stave zajedno u formulu. U ćeliji B3 možete vidjeti da ova formula preuzima sve podatke iz ćelije A3 i ulazi u njega "Washington".

Dakle, imamo "Washington", sada trebamo dobiti "George". Kako da to učinimo?

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

Sjetite se položaja zareze ili ga ponovno izračunajte.
Izračunajte duljinu niza.
Označite znakove od kraja niza do zarez.

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

Učinimo to korak po korak.

Odozgo, to je = NALAZI (",", A3 ")
Dužina niza je = LEN (A3)
Morat ćete upotrijebiti matematiku kako biste pronašli broj znakova koje treba poduzeti: = LEN (A3) - PRONAĐI (",", A3) - 1
Desna strana žice je = DESNO (A3, LEN (A3) - PRONAĐI (",", A3) - 1)

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

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

Slijedi …

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

Dolazite li do sljedećeg u lekciji 3, raspravljat ćemo o referencama i formatu ćelija, kao i premještanju i kopiranju formula, tako da ne morate ponovo napisati svaku formulu!