Če tega še Excel za splet, boste kmalu našli, da je to več kot le mreža, v katero v stolpce ali vrstice vnašate številke. Da, s funkcijo Excel za splet lahko poiščete vsote za stolpec ali vrstico številk, lahko pa izračunate tudi plačilo hipoteke, rešite matematične ali inženirske težave ali pa poiščete najboljši primer na podlagi spremenljivk, ki jih vnesete.
Excel za splet to naredite s formulami v celicah. Formula podatke na delovnem listu izračuna ali pa izpelje druga dejanja. Formula se vedno začne z enačajem (=), temu pa lahko sledijo številke, matematični operatorji (na primer znak za plus ali minus) in funkcije, ki lahko znatno razširijo zmogljivost formule.
Ta formula na primer pomnoži 2 in 3, nato pa rezultatu prišteje 5, tako da znaša končni rezultat 11.
=2*3+5
Ta formula s funkcijo PMT izračuna odplačilo hipoteke (1.073,64 €), za katero velja 5-odstotna obrestna mera (5 % deljeno z 12 meseci predstavlja mesečno obrestno mero) v 30-letnem obdobju odplačevanja (360 mesecev) za posojilo v znesku 200.000 €:
=PMT(0,05/12; 360; 200000)
Tu so dodatni primer formul, ki jih lahko vnesete na delovni list.
-
=A1+A2+A3 Sešteje vrednosti v celicah A1, A2 in A3.
-
=SQRT(A1) S funkcijo SQRT vrne kvadratni koren vrednosti v celici A1.
-
=TODAY() Vrne trenutni datum.
-
=UPPER("živijo") Besedilo »živijo« pretvori v »ŽIVIJO« s funkcijo delovnega lista UPPER .
-
=IF(A1>0) Preskusi celico A1, da določi, ali vsebuje vrednost, večjo od 0.
Deli formule
Formula lahko vsebuje tudi enega ali vse od teh elementov: funkcije, sklici, operatorji in konstante.
1. Funkcije: S funkcijo PI() dobite vrednost za pi: 3,142 ...
2. Sklici: Z A2 dobite vrednost v celici A2.
3. Konstante: Števila ali besedilne vrednosti, vnesene neposredno v formulo, kot je na primer 2.
4. Operatorji: Z operatorjem ^ (strešico) povečate število na določeno potenco, medtem ko z operatorjem * (zvezdico) pomnožite števila.
Uporaba konstant v formulah
Konstanta je vrednost, ki ni izračunana; vedno je enaka. Datum 10/9/2008, število 210 in besedilo »Quarterly Earnings« so konstante. izraz ali vrednost, ki nastane iz izraza, ni konstanta. Če konstante uporabite v formuli namesto sklicev na celice (na primer =30+70+110), se rezultat spremeni le, če spremenite formulo.
Uporaba računskih operatorjev v formulah
Operatorji določajo vrsto izračuna, ki ga želite izvesti v elementih formule. Vrstni red izračunov je privzet (v skladu s splošnimi matematičnimi pravili), vendar ga lahko spremenite z oklepaji.
Vrste operatorjev
Obstajajo štiri različne vrste računskih operatorjev: aritmetični operatorji, operatorji primerjave, besedilni operatorji spajanja in operatorji sklicev.
Aritmetični operatorji
Za izvajanje osnovnih matematičnih operacij, kot je na primer seštevanje, odštevanje, množenje ali deljenje, sestavljanje številskih vrednosti in ustvarjanje številskih rezultatov, uporabljajte te aritmetične operatorje.
|
Aritmetični operator |
Pomen |
Primer |
|
+ (znak plus) |
Seštevanje |
3+3 |
|
- (znak minus) |
Odštevanje |
3–1 |
|
* (zvezdica) |
Množenje |
3*3 |
|
/ (poševnica naprej) |
Deljenje |
3/3 |
|
% (znak za odstotke) |
Odstotek |
20 % |
|
^ (strešica) |
Potenciranje |
3^2 |
Operatorji primerjave
S temi operatorji lahko primerjate dve vrednosti. Ko ti dve vrednosti primerjate s temi operatorji, je rezultat logična vrednost – TRUE ali FALSE.
|
Operator primerjave |
Pomen |
Primer |
|
= (enačaj) |
Enak kot |
A1=B1 |
|
> (znak večji od) |
Večji kot |
A1>B1 |
|
< (znak manjši od) |
Manjši kot |
A1<B1 |
|
>= (znak večji ali enak) |
Večje od ali enako |
A1>=B1 |
|
<= (znak manjši od ali enak) |
Manjše od ali enako |
A1<=B1 |
|
<> (znak ni enako) |
Ni enako |
A1<>B1 |
Besedilni operator spajanja
Uporabljajte znak »and« (&) za spajanje (združevanje) enega ali več besedilnih nizov, da ustvarite enotno besedilo.
|
Besedilni operator |
Pomen |
Primer |
|
& (znak »in«) |
Poveže ali spoji dve vrednosti in ustvari eno povezano besedilno vrednost |
Iz besed »severni«&»veter« nastane izraz »severni veter« |
Operatorji sklicev
Sestavljajo obsege celic za izračune z naslednjimi operatorji.
|
Operator sklicev |
Pomen |
Primer |
|
: (dvopičje) |
Operator obsega, ki ustvari en sklic za vse celice med dvema sklicema, vključno med tema dvema sklicema. |
B5:B15 |
|
, (vejica) |
Operator unije, ki združi več sklicev v en sklic |
SUM(B5:B15; D5:D15) |
|
(presledek) |
Operator preseka, ki ustvari en sklic na celice, ki so skupne tema dvema sklicema |
B7:D7 C6:C8 |
Vrstni red, v Excel za splet izvaja operacije v formulah
V nekaterih primerih lahko vrstni red, po katerem se izvaja izračun, vpliva na vrnjeno vrednost formule, zato morate razumeti, kako je vrstni red določen in kako lahko spremenite ta vrstni red, da dobite želene rezultate.
Vrstni red izračunavanja
Formule vrednosti računajo po določenem vrstnem redu. V programu se formula vedno prične z enačajem (=). Excel za splet znake, ki sledijo enačaju, tolmači kot formulo. Za enačajem so elementi, ki bodo izračunani (operandi), na primer konstante in sklici na celice. Ločeni so z računskimi operatorji. Excel za splet izračuna formulo od leve proti desni glede na določen vrstni red posameznega operatorja v formuli.
Vrstni red, v katerem si sledijo operatorji
Če v eno formulo vključite več operatorjev, Excel za splet izvaja operacije v vrstnem redu, ki je prikazan v spodnji tabeli. Če so v formuli operatorji z enakim vrstnim redom – če je na primer v formuli tako operator množenja kot tudi operator deljenja – Excel za splet izračuna operatorje od leve proti desni.
|
Operator |
Opis |
|
: (dvopičje) (presledek) ; (podpičje) |
Operatorji sklicev |
|
– |
Negacija (kot pri -1) |
|
% |
Odstotek |
|
^ |
Potenciranje |
|
* in / |
Množenje in deljenje |
|
+ in – |
Seštevanje in odštevanje |
|
& |
Združi dva niza besedila (povezava) |
|
= |
Primerjava |
Uporaba oklepajev
Če želite spremeniti vrstni red preračunavanja, postavite del formule, ki naj se najprej izračuna, v oklepaje. Spodnja formula na primer izračuna vrednost 11, Excel za splet izvede množenje pred seštevanjem. Formula zmnoži 2 in 3 in nato rezultatu doda 5.
=5+2*3
Če pa sintakso spremenite z oklepaji, Excel za splet sešteje 5 in 2 in nato rezultat pomnoži s 3, da dobite 21.
=(5+2)*3
V tem primeru je treba oklepaje, ki obdajajo prvi del formule, prisiliti, da najprej izračuna B4+25 Excel za splet nato rezultat deli z vsoto vrednosti v celicah D5, E5 in F5.
=(B4+25)/SUM(D5:F5)
Uporaba funkcij in ugnezdenih funkcij v formulah
Funkcije so vnaprej določene formule za izvajanje izračunov z določenimi vrednostmi, imenovanimi argumenti, v posebnem vrstnem redu ali strukturi. Funkcije lahko uporabite za izvajanje preprostih ali zapletenih izračunov.
Sintaksa funkcij
Spodnji primer uporabe funkcije ROUND, ki zaokroži število v celici A10, prikazuje sintakso funkcije.
1. Struktura. Struktura funkcije se začne z enačajem (=), ki mu sledi ime funkcije, oklepaj, argumenti za funkcijo, ločeni z vejicami, in zaklepaj.
2. Ime funkcije. Če želite prikazati seznam funkcij, ki so na voljo, kliknite celico in pritisnite tipki SHIFT+F3.
3. Argumenti. Argumenti so lahko števila, besedilo, logične vrednosti, kot sta TRUE ali FALSE, polja, vrednosti napak, kot je #N/V, ali sklici na celice. Argument, ki ga določite, mora zagotoviti veljavno vrednost za ta argument. Argumenti so lahko tudi konstante, formule ali druge funkcije.
4. Opis argumentov. Opis s sintakso in argumenti se prikaže, ko vnašate formulo. Vnesite na primer =ROUND( in prikazal se bo opis. Opisi so prikazani le za vgrajene funkcije.
Vnašanje funkcij
Ko ustvarite formulo s funkcijo, uporabite pogovorno okno Vstavljanje funkcije za pomoč pri vnosu funkcij delovnega zvezka. Ko vnesete funkcijo v formulo, se v pogovornem oknu Vstavljanje funkcije prikaže ime funkcije, vsi argumenti, opis funkcije in argumenti, trenutni rezultat funkcije in trenutni rezultat celotne formule.
Za preprostejše ustvarjanje in urejanje formul ter za zmanjšanje možnosti črkovalnih napak in napak v sintaksi uporabite funkcijo »Samodokončanje formule«. Ko vnesete = (enačaj) in začetnice ali sprožilec prikaza, Excel za splet pod celico prikaže dinamični spustni seznam veljavnih funkcij, argumentov in imen, ki se ujemajo s črkami ali sprožilcem. Nato lahko vnesete element s spustnega seznama v formulo.
Ugnezdene funkcije
V določenih primerih boste morda morali uporabiti funkcijo kot enega od argumentov druge funkcije. Ta formula na primer uporablja ugnezdeno funkcijo AVERAGE in primerja rezultate z vrednostjo 50.
1. Funkciji AVERAGE in SUM sta ugnezdeni v funkciji IF.
Veljavni vračili Če je ugnezdena funkcija uporabljena kot argument, mora ugnezdena funkcija vrniti isto vrsto vrednosti, ki jo uporablja argument. Če argument na primer vrne vrednost TRUE ali FALSE, mora ugnezdena funkcija vrniti vrednost TRUE ali FALSE. Če funkcija ni prikazana, se Excel za splet prikaže #VALUE! .
Omejitve ravni gnezdenja Formula lahko vsebuje do sedem ugnezdenih ravni funkcij. Ko je ena funkcija (imenovali jo bomo funkcija B) uporabljena kot argument v drugi funkciji (imenovali jo bomo funkcija A), funkcija B deluje kot funkcija druge ravni. Funkciji AVERAGE in SUM sta na primer obe funkciji druge ravni, če sta uporabljeni kot argumenta funkcije IF. Funkcija, ki je ugnezdena v ugnezdeni funkciji AVERAGE je nato funkcija tretje ravni in tako naprej.
Uporaba sklicev v formulah
Sklic identificira celico ali obseg celic na delovnem listu in pove, Excel za splet kje iskati vrednosti ali podatke, ki jih želite uporabiti v formuli. S sklici lahko podatke, ki so v različnih delih delovnega lista, uporabite v eni formuli ali pa uporabite vrednost ene celice v več formulah. Prav tako se lahko sklicujete na celice na drugih listih istega delovnega zvezka in na druge delovne zvezke. Sklici na celice v drugih delovnih zvezkih se imenujejo povezave ali zunanji sklici.
Slog sklicevanja A1
Privzeti slog sklicevanja Privzeto Excel za splet slog sklicevanja A1, ki se sklicuje na stolpce s črkami (od A do XFD za skupno 16.384 stolpcev) in se sklicuje na vrstice s številkami (od 1 do 1.048.576). Te črke in številke so imenovane naslovi vrstic in stolpcev. Če se želite sklicevati na celico, vnesite črko stolpca, ki ji sledi številka vrstice. B2 se sklicuje na primer na celico v presečišču stolpca B in vrstice 2.
|
Za sklicevanje na |
Uporabi |
|
Celico v stolpcu A in vrstici 10 |
A10 |
|
Obseg celic v stolpcu A in vrsticah od 10 do 20 |
A10:A20 |
|
Obseg celic v vrstici 15 in stolpcih od B do E |
B15:E15 |
|
Vse celice v vrstici 5 |
5:5 |
|
Vse celice v vrsticah od 5 do 10 |
5:10 |
|
Vse celice v stolpcu H |
H:H |
|
Vse celice v stolpcih od H do J |
H:J |
|
Obseg celic v stolpcih od A do E in vrsticah od 10 do 20 |
A10:E20 |
Sklicevanje na drug delovni list V naslednjem primeru funkcija delovnega lista AVERAGE izračuna povprečno vrednost za obseg B1:B10 na delovnem listu imenovanem »Trženje« v istem delovnem zvezku.
1. Sklicuje se na delovni list »Trženje«
2. Sklicuje se na obseg celic od B1 do vključno B10
3. Loči sklic na delovni list od sklica na obseg celice
Razlika med absolutnimi, relativnimi in mešanimi sklici
Relativni sklici Relativni sklic v formuli, kot je A1, temelji na relativnem položaju celice, ki vsebuje formulo in celico, na katero se sklic nanaša. Če se mesto celice, ki vsebuje formulo, spremeni, se spremeni tudi sklic. Če formulo kopirate ali zapolnite prek vrstic ali stolpcev, bo sklic samodejno prilagojen. Nove formule privzeto uporabljajo relativni sklic. Če na primer kopirate ali zapolnite relativni sklic celice B2 v B3, bo samodejno prilagojen iz =A1 v =A2.
Absolutni sklici Absolutni sklic na celico v formuli, kot je $A$1, se vedno sklicuje na celico na določenem mestu. Če se mesto celice, ki vsebuje formulo, spremeni, absolutni sklic ostane nespremenjen. Če formulo kopirate ali zapolnite prek vrstic ali stolpcev, absolutni sklic ne bo prilagojen. Nove formule privzeto uporabljajo relativni sklic, zato jih boste morda morali zamenjati z absolutnim sklicem. Če na primer kopirate ali zapolnite absolutni sklic celice B2 v B3, ostane v obeh celicah nespremenjen =$A$1.
Mešani sklici Mešani sklic vsebuje absolutni stolpec in relativno vrstico ali absolutno vrstico in relativen stolpec. Sklic na absolutni stolpec uporablja oblike $A1, $B1 itd. Sklic na absolutno vrstico uporablja oblike A$1, B$1 itd. Če se mesto celice, ki vsebuje formulo, spremeni, se relativni sklic spremeni, medtem ko se absolutni sklic ne. Če formulo kopirate ali zapolnite prek vrstic in stolpcev, bo relativni sklic samodejno prilagojen, absolutni pa ne. Če na primer kopirate ali zapolnite mešani sklic iz celice A2 v B3, bo prilagojen iz =A$1 v =B$1.
3-D slog sklicevanja
Preprosto sklicevanje na več delovnih listov Če želite analizirati podatke v isti celici ali obsegu celic na več delovnih listih v delovnem zvezku, uporabite sklic 3-D. 3-D sklic vključuje sklic celice ali obsega, pred katerim je zapisan obseg imen delovnih listov. Excel za splet uporablja vse delovne liste, shranjene med začetnim in končnimi imeni sklica. =SUM(List2:List13!B5) na primer sešteje vse vrednosti v celici B5 na vseh delovnih listih med in na List2 in List13.
-
3-D sklice lahko uporabljate za sklicevanje na celice na drugih delovnih listih, določanje imen in ustvarjanje formul s temi funkcijami: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA, and VARPA.
-
3-D sklicev ni mogoče uporabiti v matričnih formulah.
-
3-D sklicev ne morete uporabljati z operatorjem preseka (presledek) ali v formulah z implicitnim presekom.
Kaj se zgodi, ko premikate, kopirate, vstavljate ali brišete delovne liste Naslednji primeri pojasnjujejo, kaj se zgodi, ko premikate, kopirate, vstavljate ali brišete delovne liste, ki so vključeni v 3-D sklic. V primerih je uporabljena formula za seštevanje celic od A2 do A5 na delovnih listih od List2 do List6 [=SUM(List2:List6!A2:A5)].
-
Vstavljanje ali kopiranje Če vstavite ali kopirate liste med listi List2 in List6 (končni točki v tem primeru), Excel za splet vključi vse vrednosti v celicah od A2 do A5 z dodanih listov v izračune.
-
Brisanje Če izbrišete liste med List2 in List6, Excel za splet odstrani njihove vrednosti iz izračuna.
-
Premikanje Če premaknete liste iz listov med List2 in List6 na mesto, ki ni v obsegu sklica, Excel za splet odstrani njihove vrednosti iz izračuna.
-
Premikanje končne točke Če premaknete List2 ali List6 na drugo mesto v istem delovnem zvezku, Excel za splet izračun prilagodil nov obseg listov med njima.
-
Brisanje končne točke Če izbrišete List2 ali List6, Excel za splet izračun prilagodil obseg listov med njima.
Slog sklicevanja R1C1
Prav tako lahko uporabljate slog sklicevanja, kjer so na delovnem listu oštevilčene tako vrstice kot stolpci. Slog sklicevanja R1C1 je uporaben za računanje postavitve vrstice in stolpca v makrih. V slogu R1C1 Excel za splet označuje mesto celice s številko »R«, ki ji sledi številka vrstice, in »C«, ki ji sledi številka stolpca.
|
Sklic |
Pomen |
|
R[-2]C |
relativni sklic na celico, ki je dve vrstici višje v istem stolpcu kot celica s formulo |
|
R[2]C[2] |
Relativni sklic na celico, ki je dve vrstici navzdol in dva stolpca v desno kot celica s formulo |
|
R2C2 |
Absolutni sklic na celico v drugi vrstici in v drugem stolpcu |
|
R[-1] |
Relativni sklic na celotno vrstico nad aktivno celico |
|
R |
Absolutni sklic na vrstico, v kateri je celica s formulo |
Ko posnamete makro, Excel za splet nekaj ukazov s slogom sklicevanja R1C1. Če na primer posnamete ukaz, kot je klik gumba Samodejna vsota, da vstavite formulo, ki sešteje obseg celic, Excel za splet posname formulo s sklici sloga R1C1 in ne sloga A1.
Uporaba imen v formulah
Ustvarite lahko definirana imena, ki predstavljajo celice, obseg celic, formule, konstante ali Excel za splet tabele. Ime je pomenska okrajšava, ki omogoči lažje razumevanje namena sklica na celico, konstante, formule ali tabele, kar je na prvi pogled težko ugotoviti. Te informacije prikazujejo pogoste primere imen in kako njihova uporaba v formulah lahko izboljša jasnost ter naredi formule bolj razumljive.
|
Vrsta primera |
Primer, v katerem so namesto imen uporabljeni obsegi |
Primer, v katerem so uporabljena imena |
|
Sklic |
=SUM(A16:A20) |
=SUM(Prodaja) |
|
Konstanta |
=PRODUCT(A12; 9,5 %) |
=PRODUCT(Cena,Stopnja davkov – Kansas) |
|
Formula |
=TEXT(VLOOKUP(MAX(A16; A20); A16:B20; 2; FALSE); "m/dd/yyyy") |
=TEXT(VLOOKUP(MAX(Prodaja),Informacije o prodaji,2,FALSE),"m/dd/yyyy") |
|
Tabela |
A22:B25 |
=PRODUCT(Cena,Tabela1[@Stopnja davkov]) |
Vrste imen
Obstaja več vrst imen, ki jih lahko ustvarite in uporabite
Določeno ime Ime, ki predstavlja celico, obseg celic, formulo ali konstantno vrednost. Ustvarite lahko lastno opredeljeno ime. Prav Excel za splet včasih ustvari določeno ime namesto vas, na primer, ko nastavite območje tiskanja.
Ime tabele Ime za tabelo Excel za splet, ki je zbirka podatkov o določenem predmetu, ki so shranjeni v zapisih (vrsticah) in poljih (stolpcih). Excel za splet ustvarite privzeto ime tabele Excel za splet »Tabela1«, »Tabela2« in tako naprej, vsakič ko vstavite tabelo Excel za splet, vendar lahko spremenite ta imena, da bodo bolj smiselna.
Ustvarjanje in vnašanje imen
Ime ustvarite tako, da uporabite možnost Ustvari ime iz izbora. Imena lahko preprosto ustvarite iz obstoječih oznak vrstic in stolpcev, tako da uporabite izbrane celice na delovnem listu.
Opomba: Imena privzeto uporabljajo absolutne sklice na celice.
Ime vnesete tako, da:
-
Tipkanje Vnesite ime, na primer kot argument formule.
-
Uporaba funkcije »Samodokončanje formul« Uporabite spustni seznam »Samodokončanje formul«, kjer so veljavna imena samodejno navedena.
Uporaba formul in konstant polja
Excel za splet ne podpira ustvarjanja formul polja. Ogledate si lahko rezultate formule s polji, ustvarjene v namiznem programu Excel, vendar jih ne morete urejati ali preračunati. Če imate v računalniku nameščen Excelov namizni program, za delo s polji kliknite Odpri v Excelu.
Ta primer polja izračuna skupno vrednost polja cen delnic in obveznic, brez uporabe vrstice celic za izračun in prikaz posameznih vrednosti delnic.
Ko vnesete formulo ={SUM(B2:D2*B3:D3)} kot formulo polja, ta zmnoži polji »Delež« in »Cena« za vsako delnico in nato sešteje rezultate teh izračunov.
Izračun več rezultatov Nekatere funkcije delovnih listov vrnejo matriko vrednosti ali pa potrebujejo matriko vrednosti kot argument. Za izračun več rezultatov s formulo polja morate vnesti matriko v obseg celic, ki ima enako število vrstic in stolpcev kot argumenti matrike.
Če imate na primer tri obsege prodaje (v stolpcu B) za obdobje treh mesecev (v stolpcu A), s funkcijo TREND določite linearne vrednosti za te tri obsege prodaje. Če želite prikazati vse rezultate, ki jih dobite s formulo, jo vnesite v vse tri celice v stolpcu C (C1:C3).
Ko vnesete formulo =TREND(B1:B3;A1:A3) kot formulo polja, ta vrne tri ločene rezultate (22.196, 17.079 in 11.962), ki temeljijo na treh obsegih prodaje in treh mesecih.
Uporaba konstant polja
V navadno formulo lahko vnesete sklic na celico, ki vsebuje vrednost, ali pa vnesete vrednost, ki se imenuje konstanta. Podobno lahko v formulo polja vnesete sklic na matriko ali pa vnesete matriko vrednosti, ki so v celicah, ki se imenujejo konstante polja. Formule polja sprejmejo konstante enako kot formule, ki niso formule polja, vendar morate vnesti konstante polja v določeni obliki zapisa.
V konstantah polja so lahko števila, besedilo, logične vrednosti (TRUE ali FALSE) ali vrednosti napak, na primer #N/V. Različne vrste vrednosti so lahko v isti konstanti polja – na primer {1,3,4;TRUE,FALSE,TRUE}. Števila v konstantah polja so lahko v celoštevilski, decimalni ali znanstveni obliki. Besedilo mora biti navedeno v dvojnih narekovajih, na primer "torek".
Konstante polja ne smejo vsebovati sklicev na celice, stolpcev ali vrstic neenakih dolžin, formul, posebnih znakov $ (znak za dolar), oklepajev ali % (znak za odstotek).
Ko oblikujete konstante polja, se prepričajte, da:
-
jih vnesete med zavite oklepaje ({ } ).
-
vrednosti v različnih stolpcih ločite s podpičjem (;). Tako na primer za predstavitev vrednosti 10, 20, 30 in 40 vnesite {10; 20; 30; 40}. Taka konstanta polja je matrika velikosti 1 krat 4 in je enakovredna sklicu na obseg razsežnosti 1 vrstica krat 4 stolpci.
-
boste vrednosti v različnih vrsticah ločili s podpičji (;). Tako na primer za predstavitev vrednosti 10, 20, 30 in 40 v eni vrstici ter 50, 60, 70 in 80 v vrstici, ki je takoj pod njo, vnesite konstanto polja 2 krat 4: {10; 20; 30; 40; 50; 60; 70; 80}.
