V tej lekciji si bomo ogledali, kako ustvariti zapleteno formulo v Excelu, in si ogledali tudi tipične napake, ki jih začetniki naredijo zaradi nepazljivosti. Če ste pred kratkim delali v Excelu, vam priporočamo, da se najprej obrnete na lekcijo, kjer smo razpravljali o ustvarjanju preprostih formul.
Kako ustvariti zapleteno formulo v Excelu
V spodnjem primeru bomo prikazali, kako Excel izračuna kompleksne formule na podlagi vrstnega reda operacij. IN v tem primeruŽelimo izračunati prometni davek za gostinske storitve. Če želite to narediti, v celico D4 napišite naslednji izraz: =(D2+D3)*0,075. Ta formula bo seštela stroške vseh postavk na računu in jih nato pomnožila z zneskom prometnega davka 7,5% (zapisano kot 0,075).
Excel sledi temu vrstnemu redu in najprej doda vrednosti v oklepajih: (44.85+39.90)=$84.75 . Nato to število pomnožite z davčno stopnjo: $84.75*0.075 . Rezultat izračuna pokaže, da bo prometni davek $6.36 .
Zelo pomembno je, da kompleksne formule vnesete v pravilnem vrstnem redu. V nasprotnem primeru Excel izračuni se lahko izkaže za netočno. V našem primeru, če ni oklepaja, se najprej izvede množenje in rezultat bo napačen. Oklepaji so najboljši način določitev vrstnega reda izračunov v Excelu.
Ustvarite zapletene formule s potekom dela
V spodnjem primeru bomo uporabili povezave in kvantitativne podatke za ustvarjanje zapletene formule, ki bo izračunala skupne stroške računa za hrano. Formula bo izračunala stroške vsakega elementa menija in nato seštela vse vrednosti.
Za lažje razumevanje lahko vsaki formuli dodate oklepaje. Čeprav to ne bo spremenilo rezultata izračuna v tem primeru, lahko še vedno množenje zapišemo v oklepaje. To bo pojasnilo, da se izvaja pred dodajanjem.
Excel vas ne opozori vedno na napake v formuli, zato morate vse svoje formule preveriti sami. Če želite izvedeti, kako to storiti, si oglejte lekcijo Preverjanje formul.
Program Microsoft Excel Office ima številne priročne funkcije, s katerimi se izognete dolgim izračunom pred vnosom podatkov v tabele ali grafikone. Za izračun nekaterih vrednosti, kot so seštevanje, odštevanje, razmerje in druge, lahko uporabite prvotno določene formule. Lahko jih dodate sami ali pa izberete s seznama obstoječih. V tem članku se naučite korak za korakom vstaviti formulo.
Odprite Excel. Pred vami bo delovni prostor, preverite, ali je zavihek »Domov« odprt. V prazni vrstici, ki se nahaja med glavo programa in njegovim delovnim področjem, boste videli polje za vnos formule, ki jo boste potrebovali po vnosu argumentov, torej številk, ki jih je treba izračunati.
Zdaj natančno veste, kako ustvariti formule v Excelu in si ogledate njihovo dekodiranje v čarovniku za funkcije.
Če še niste delali z Excelom, boste kmalu izvedeli, da to ni le preglednica za vnos številk. Seveda lahko v Excelu preprosto izračunate zneske v vrsticah in stolpcih, lahko pa tudi izračunate hipotekarna plačila, rešite matematične in inženirske probleme ter poiščete najboljše možnosti na podlagi danih vrednosti spremenljivk.
V Excelu se vse to izvaja s pomočjo formul v celicah. Te formule se uporabljajo za izvajanje izračunov in drugih dejanj s podatki na listu. Formula se vedno začne z enačajem (=), za katerim lahko vnašate številke, matematične operaterje (kot sta znaka + in - za seštevanje in odštevanje) in vgrajene Excelove funkcije, ki močno izboljšajo zmožnosti formul.
Spodaj je primer formule, ki pomnoži 2 s 3 in rezultatu doda 5, da dobi 11.
Spodaj so primeri formul, ki jih lahko uporabite na delovnih listih.
Deli Excelove formule
Formula lahko vsebuje tudi enega ali več elementov, kot npr funkcije, povezave, operaterji in konstante.
Vrstni red dejanj v formulah
V nekaterih primerih lahko vrstni red izračuna vpliva na vrednost, ki jo vrne formula, zato je pomembno razumeti standardni vrstni red izračunov in kako ga lahko spremenite, da dobite želene rezultate.
Uporaba funkcij in ugnezdenih funkcij v Excelovih formulah
Funkcije so vnaprej določene formule, ki izvajajo izračune na danih vrednostih - argumentih - v določenem vrstnem redu ali po določenem vzorcu. S funkcijami lahko izvajate preproste in zapletene izračune. Vse Excelove funkcije si lahko ogledate na zavihku "Formule".
Uporaba povezav v Excelovih formulah
Referenca kaže na celico ali obseg celic na delovnem listu in Microsoft Excelu pove, kje se nahajajo vrednosti ali podatki, ki jih zahteva formula. S pomočjo povezav lahko v eni formuli uporabite podatke, ki se nahajajo na različnih delih lista, vrednost ene celice pa lahko uporabite tudi v več formulah. Poleg tega lahko nastavite sklice na celice iz različnih listov istega delovnega zvezka ali na celice iz drugih delovnih zvezkov. Povezave do celic v drugih delovnih zvezkih se imenujejo povezave ali zunanje povezave.
Uporaba imen v Excelovih formulah
Ustvarite lahko posebna imena za identifikacijo celic, obsegov celic, formul, konstant in Excelovih tabel. Ime je smiselna okrajšava, ki pojasnjuje namen sklicevanja na celico, konstanto, formulo ali tabelo, saj je težko razumeti njihovo bistvo na prvi pogled. Spodaj so primeri imen in kako njihova uporaba olajša razumevanje formul.
Primer 1
Primer 2
Kopirajte vzorčne podatke iz spodnje tabele in jih prilepite v celico A1 nove Excel list. Če želite prikazati rezultate formul, jih izberite in pritisnite F2, nato pritisnite Enter. Poleg tega lahko prilagodite širino stolpcev glede na podatke, ki jih vsebujejo.
Opomba: V formulah v stolpcih C in D je določeno ime »Prodaja« nadomeščeno s sklicevanjem na obseg A9:A13, ime »Informacije o prodaji« pa z obsegom A9:B13. Če delovni zvezek ne vsebuje teh imen, bodo formule v D2:D3 vrnile napako #NAME?
|
Primer vrste |
Primer, ki ne uporablja imen |
Primer uporabe imen |
Formula in rezultat z uporabo imen |
|
"=SUM(A9:A13) |
"=SUM(Prodaja) |
SUM (prodaja) |
|
|
"=BESEDILO(VLOOKUP(MAX(A9:13),A9:B13,2,FALSE),,"dd/mm/llll") |
"=BESEDILO(VLOOKUP(MAX(Prodaja),Podatki o prodaji,2,FALSE),"dd.mm.llll") |
TEXT(VLOOKUP(MAX(Prodaja),Podatki o prodaji,2,FALSE),"dd.mm.llll") |
|
|
Datum prodaje |
|||
Za več informacij glejte Definiranje in uporaba imen v formulah.
Uporaba matričnih formul in matričnih konstant v Excelu
Matrična formula lahko izvede več izračunov in nato vrne eno vrednost ali skupino vrednosti. Matrična formula obdeluje več nizov vrednosti, imenovanih argumenti polja. Vsak argument matrike mora imeti enako število vrstic in stolpcev. Matrično formulo ustvarimo na enak način kot druge formule, s to razliko, da se za vnos take formule uporabljajo tipke CTRL+SHIFT+ENTER. Nekatere vgrajene funkcije so matrične formule in jih je treba vnesti kot nize, da dobite pravilne rezultate.
Konstante polja lahko uporabite namesto referenc, če vam ni treba vnesti vsake vrednosti konstante v ločeno celico na delovnem listu.
Za izračun ene ali več vrednosti uporabite matrično formulo
Opomba: Ko vnesete matrično formulo, jo Excel samodejno zapre v zavite oklepaje ( in ). Če poskušate ročno vnesti zavite oklepaje, Excel prikaže formulo kot besedilo.
Uporaba matričnih konstant
V običajno formulo lahko vnesete sklic na celico z vrednostjo ali na samo vrednost, imenovano tudi konstanta. Podobno lahko v matrično formulo vnesete sklic na matriko ali matriko vrednosti v celicah (včasih imenovano konstanta matrike). Matrične formule sprejemajo konstante tako kot druge formule, vendar morajo biti matrične konstante vnesene v določeni obliki.
Konstante polja lahko vsebujejo števila, besedilo, logične vrednosti, kot sta TRUE ali FALSE, ali vrednosti napak, kot je #N/A. Ena matrična konstanta lahko vsebuje vrednosti različne vrste, na primer (1,3,4;TRUE,FALSE,TRUE). Številke v matričnih konstantah so lahko cela, decimalna ali eksponentna. Besedilo mora biti priloženo dvojni narekovaji, na primer "torek".
Pri formatiranju matričnih konstant zagotovite, da so izpolnjene naslednje zahteve:
Konstante so v zavitih oklepajih ( { } ).
Stolpci ločeni z vejicami ( , ). Če želite na primer predstaviti vrednosti 10, 20, 30 in 40, vnesite (10,20,30,40). Ta matrična konstanta je matrika 1 krat 4 in ustreza sklicu v eni vrstici in štirih stolpcih.
Vrednosti celic iz različnih vrstic so ločene s podpičji ( ; ). Če želite na primer predstaviti vrednosti 10, 20, 30, 40 in 50, 60, 70, 80 v zloženih celicah, lahko ustvarite matrično konstanto 2 krat 4: (10,20,30,40,50 , 60,70,80).
Odstranjevanje formule
Skupaj s formulo se izbrišejo tudi rezultati njenega izračuna. Lahko pa samo formulo izbrišete, rezultat njenega izračuna pa pustite kot vrednost v celici.
Uporaba dvopičja ( : ) loči sklice na prvo in zadnjo celico v obsegu. Na primer: A1:A5.
Določeni zahtevani argumenti
Funkcija ima lahko obvezne in neobvezne argumente (slednji so v sintaksi označeni z oglatimi oklepaji). Vnesti je treba vse zahtevane argumente. Poskusite tudi ne vnašati preveč argumentov.
Formula ne vsebuje več kot 64 ravni gnezdenja funkcij
Ne sme biti več kot 64 ravni gnezdenja.
Imena delovnih zvezkov in listov so v enojnih narekovajih
Če imena listov ali delovnih zvezkov, v katerih se sklicujete na celice ali vrednosti, vsebujejo neabecedne znake, morajo biti v enojnih narekovajih ( " ).
Pot do zunanjih knjig je določena
Številke vnesene brez oblikovanja
Ko v formulo vnašate številke, ne morete vključiti znakov za dolar, ker se uporabljajo za označevanje absolutnih sklicev. Na primer, namesto vrednosti $1000 treba vstopiti 1000 .
Pomembno: Izračunani rezultati formul in nekaterih funkcij Excelovega delovnega lista se lahko nekoliko razlikujejo v računalnikih z operacijskim sistemom Windows z arhitekturo x86 ali x86-64 in računalnikih z operacijskim sistemom Windows RT z arhitekturo ARM.
V tej objavi, tako kot v večini člankov v spletnem dnevniku, Excelove tabele imenujem obseg celic, za katere je uporabljen ukaz Vstavi – Tabele – Tabela . Več o namenu in prednostih tega postopka si lahko preberete v. Če te objave niste prebrali, boste težje razumeli tukaj predstavljene informacije.
Zdaj pa si podrobneje oglejmo, kako formule delujejo v tabelah.
Skupna vrstica
Za izračun vsot imajo tabele vrstico s vsotami. Če ga želite prikazati, izberite katero koli celico v tabeli in potrdite polje na traku Oblikovalec – Možnosti sloga tabele – Skupna vrstica . Pod tabelo se prikaže vrstica »Skupaj«. Izberite katero koli celico v tej vrstici, da izračunate vsote stolpcev. V spustnem seznamu izberite formulo, po kateri bo program izračunal rezultate. Na primer vsota, najmanj, povprečje itd. Kliknite »Druge funkcije ...«, da izberete drugo funkcijo.
Skupna vrstica v Excelu
Formulo za izračun seštevkov lahko napišete tudi sami, napiše se na enak način kot običajne formule, le da se namesto naslovov celic izpišejo njihova imena iz glav tabel. Z miško določite povezave do celic, program bo sam zamenjal njihova imena.
Ne pozabite, da skupna vrstica obdela tiste celice tabele, ki so vidne na zaslonu. Če ste uporabili filter po nekaterih vrednostih tabele, vsote bodo preračunane samo za izbrane podatke.
Pisanje formul v stolpce
Če morate računati v stolpcih tabele, je to še lažje kot v običajnem obsegu celic. Ustvarite nov stolpec v tabeli. Kazalec postavite v katero koli celico v tem stolpcu in zapišite formulo. Uporabite metodo kazanja z miško, da pokažete na sklice na celice. Program bo v formulah prikazal imena stolpcev namesto , kar bo olajšalo razumevanje formule. Pritisnite Enter in bodite presenečeni! Excel ne bo samo izračunal rezultata, ampak tudi kopiral formulo v vse celice stolpca. Sploh vam ni treba ročno kopirati formul!
Formule v Excelovih tabelah
Ta funkcija ni vedno uporabna, zato jo lahko onemogočite. Če želite to narediti, pri vstavljanju formule kliknite na pametno oznako in izberite » Ne ustvari samodejno izračunanih stolpcev"
Nastavitev formul tabele Excel
Sklicevanje na tabelo po imenu
Na vsebino tabele se lahko sklicujete po imenu. Če želite nastaviti ime tabele, ga vnesite v polje na traku: Oblikovalec – Lastnosti – Ime tabele .
Zdaj, če želite izvesti nekatere operacije s podatki tabele, lahko do njih dostopate po imenu. Na primer, naša tabela se imenuje "Ljudje":
- Formula =sum(People) bo poskušala sešteti vse celice v tabeli
- Formula =vsota(Ljudje[količina_prodaje]) bo določil količino podatkov v stolpcu »Sales_Amount« v tabeli »People«.
- =Ljudje[@Sales_Amount]/100– bo prodajo iz iste linije delil s 100
Če se številki vrstice celice s formulo in celice v tabeli ne ujemata, bo Excel prikazal tradicionalno povezavo s koordinatami.
Uporaba formul v tabelah je popolnoma avtomatizirana in intuitivna. Čeprav bo mnogim sprva težko uporabljati, se boste po nekaj dneh navadili in cenili vse prednosti, ki jih ponujajo Excelove preglednice.
Hvala, ker ste prebrali to objavo. In v naslednjem članku vam bom povedal. Se vidimo na straneh bloga.
Kot veste, je bil pisarniški urejevalnik preglednic Excel prvotno zasnovan za izvajanje matematičnih, algebrskih in drugih izračunov, za katere uporabite vnos posebnih formul. Toda glede na to, kako napisati formulo v Excelu, je treba upoštevati, da se večina vnosnih operacij bistveno razlikuje od vsega, kar se običajno uporablja v običajno življenje. To pomeni, da imajo same formule nekoliko drugačen videz in uporabljajo popolnoma drugačne operaterje od tistih, ki se uporabljajo pri pisanju standardnih izračunov.
Oglejmo si vprašanje, kako napisati formulo v Excelu z nekaj preprostimi primeri, ne da bi se dotaknili zapletenih operacij, da bi razumeli, kar morate preučiti program precej globoko. Toda tudi predhodno znanje bo vsakemu uporabniku omogočilo razumevanje osnovnih načel uporabe formul za različne primere.
Kako napisati formulo v Excelu: osnovni pojmi
Torej se vnos vrednosti formule v program nekoliko razlikuje od standardnih operacij, uporabljenih simbolov in uporabljenih operaterjev. Pri reševanju problema, kako napisati formulo v Excelu, morate začeti z osnovnimi koncepti, ki se uporabljajo v skoraj vseh računalniških sistemih.
Dejstvo je, da stroj ne razume vnosa kombinacije, kot je "2 x 2" ali dajanja skupne komponente iz oklepaja ("2+2)5"). Če želite to narediti, je mogoče uporabiti več vrst simbolov, ki so predstavljeni v spodnji tabeli, ne da bi upoštevali logične operaterje.
V tem primeru se prioriteta operacij začne od stopnje in konča z seštevanjem in odštevanjem. Poleg tega, čeprav je mogoče Excel uporabljati kot običajen kalkulator, morate za izračune običajno določiti številke celic ali obsege celic. Samoumevno je, da mora biti format podatkov v vsaki taki celici nastavljen na ustrezen (vsaj številski).
Vsota in razlika
Kako napisati formulo vsote ali razlike v Excelu? Torej, začnimo z najpreprostejšo stvarjo, ko morate izračunati znesek. V vrstici s formulo (in pri vseh operacijah) se najprej vpiše enačaj, za njim pa se vnese želena formula. V primeru običajnega kalkulatorja lahko za nameščeno celico določite »=2+2«.
Če se seštevek izvede za vrednosti, vnesene neposredno v druge celice (na primer A1 in A2), ima formula obliko "=A1+A2". Za uporabo dodatnih operatorjev je lahko vsota pogosto v oklepajih. Za razliko - isto, le z minusom namesto plusa.
Ko morate določiti številke celic ali njihov obseg, lahko uporabite poseben ukaz za vsoto (v ruščini "SUMM", v angleščini - SUM). Pri določanju več celic je videti takole: »=SUM(A1;A2)«, za obseg - »SUM(A1:A10)«, pod pogojem, da morate izračunati vsoto vseh števil, ki se nahajajo v celicah od prvega do desetega. V bistvu, če namestite aktivna celica, ki se nahaja takoj za zadnjim v stolpcu s prvotnimi vrednostmi, vam formule ni treba vnesti, ampak preprosto kliknete gumb za samodejno seštevanje.
Množenje, deljenje in potenciranje
Zdaj pa poglejmo, kako napisati formulo za množenje ali deljenje v Excelu. Postopek je enak kot pri vnosu vsote ali razlike, razlikujejo se le operatorji.
Za produkt se uporablja oblika »=A1*A2«, za količnik - »A1/A2«. Mimogrede, to so popolnoma enaki ukazi, ki jih lahko najdete pri uporabi standardnega kalkulatorja Windows.
Za potenciranje uporabite simbol »^«. Za vrednost v celici A1, ki jo je treba na primer kvadrirati, se uporabi formula »=A1^2«.
Izračuni v odstotkih
Z odstotki, če se ne dotikate zapletenih izračunov, je vse preprosto. Kako napisati formulo z odstotki v Excelu?
Dovolj je, da vnesete formulo v obliki »=A1*5%«, po kateri boste prejeli istih pet odstotkov vrednosti v ustrezni celici.
Uporaba vnosa formule na podlagi izbire celic
A vse to je bilo povezano z ročnim dodeljevanjem oziroma tako imenovanim neposrednim vnosom formul (direkten ali neposreden vnos). Pravzaprav je včasih koristno uporabiti miško in tipko Ctrl.
Medtem ko držite tipko miške, lahko preprosto izberete želene celice, tako da najprej vnesete zahtevani izračun v vrstico s formulami. Celice bodo dodane neposredno v vrstico s formulami. Toda glede na vrsto formule boste včasih morali ročno dodati oklepaje.
Absolutni, relativni in mešani tipi celic
Omeniti velja tudi, da lahko program uporablja več vrst celic, da ne omenjamo podatkov, ki jih vsebujejo.
Absolutna celica je nespremenljiva in je označena kot $A$1, relativna celica je sklic na skupno lokacijo (A1), mešana celica je kombinacija sklicev na absolutno in relativno celico ($A1 ali A$1) . Običajno se takšni formati uporabljajo pri ustvarjanju, ko formule vključujejo podatke, ki se nahajajo na različne liste knjigah ali celo v različnih datotekah. 
Formule VLOOKUP
Na koncu si poglejmo, kako napisati formulo VLOOKUP v Excelu. Ta tehnika vam omogoča vstavljanje podatkov iz enega obsega v drugega. V tem primeru je metoda nekoliko podobna tisti, ki se uporablja pri reševanju problema, kako napisati formulo »Pogoj« v Excelu, ki uporablja simbole, podane v zgornji tabeli.
V splošnem so takšni izračuni podobni preprostemu filtru, ki se uporablja za stolpce, ko želite filtrirati samo točne vrednosti in ne približnih vrednosti.
Pri tej možnosti se najprej prek »Čarovnika za funkcije« uporabi obseg vrednosti izvirne (prve) tabele, v polju »Tabela« je drugi obseg označen s fiksno vsebino (F4), nato pa Navedena je številka stolpca, v polju za intervalni ogled pa je vrednost nastavljena na »FALSE«, če res morate pri filtriranju dobiti samo točne, ne približne vrednosti. Praviloma se takšne formule bolj uporabljajo v skladišču ali računovodstvu, ko ni mogoče namestiti nobenih specializiranih programskih izdelkov.
Zaključek
Omeniti velja še, da tukaj niso opisane vse formule, ki jih je mogoče uporabiti v urejevalniku preglednic Excel. To je tako rekoč le osnova. Pravzaprav, če se poglobite v trigonometrijo ali računanje logaritmov, matrik ali celo tenzorskih enačb, je vse videti veliko bolj zapleteno. Toda, da bi vse to preučili, morate temeljito preučiti priročnik za sam urejevalnik. In ne gre za dejstvo, da lahko v Excelu na podlagi spreminjanja podatkov ustvarite celo najpreprostejše logične igre. Kot primer lahko navedemo isto "kačo", ki sprva ni imela nobene zveze z urejevalnikom preglednic, ampak so jo navdušenci svoje obrti reproducirali v Excelu.
V nasprotnem primeru bi morali jasno razumeti, da lahko po preučevanju primitivnih formul ali operacij s podatki zlahka obvladate bolj zapletene izračune, na primer z ustvarjanjem navzkrižnih sklicev, uporabo različnih vrst skriptov ali skriptov VB itd. Vse to zahteva čas, tako da, če želite maksimalno preučiti program in vse njegove zmožnosti, se boste morali na teoretičnem delu precej potruditi.
