V tej vadnici vas bomo vodili skozi postopek za ustvarjanje zapletene formule v Excelu in si jo tudi ogledali tipične napake ki izhajajo iz začetnikov zaradi nepazljivosti. Če še ne poznate Excela, vam svetujemo, da se najprej obrnete na lekcijo, v kateri smo razpravljali o ustvarjanju preprostih formul.

Kako v Excelu ustvariti zapleteno formulo

V spodnjem primeru bomo prikazali, kako Excel izračuna zapletene formule glede na vrstni red izvajanja operacij. V tem primeru želimo izračunati znesek prometnega davka za prehrambene storitve. Če želite to narediti, v celico D4 zapišite naslednji izraz: = (D2 + D3) * 0,075... Ta formula bo seštela vrednost vseh postavk na računu in nato pomnožila z zneskom prometnega davka 7,5% (zapisano kot 0,075).

Excel sledi vrstnemu redu in najprej doda vrednosti v oklepajih: (44.85+39.90)=$84.75 ... Nato to številko pomnoži z davčno stopnjo: $84.75*0.075 ... Rezultat izračuna kaže, da bo prometni davek $6.36 .

Izjemno pomembno je, da zapletene formule vnesete v pravilnem vrstnem redu. V nasprotnem primeru lahko Excelovi izračuni niso točni. V našem primeru, če ni oklepajev, se najprej izvede množenje, rezultat pa bo napačen. Nosilci so najboljši način določanje vrstnega reda izračunov v Excelu.

Ustvarite zapletene formule z uporabo vrstnega reda dejanj

V spodnjem primeru bomo s povezavami skupaj s kvantitativnimi podatki ustvarili zapleteno formulo, ki izračuna skupne stroške računa za hrano. Formula bo izračunala stroške vsake postavke v meniju in nato dodala vse vrednosti skupaj.

Za lažje branje lahko kateri koli formuli dodate oklepaje. Čeprav to ne spremeni rezultata izračuna v tem primeru, lahko množenje v oklepajih še vedno zapišemo. Tako bo jasno, da se izvede pred dodajanjem.

Excel ne opozori vedno na napake formule, zato morate vse formule preveriti sami. Če želite izvedeti, kako lahko to storite, glejte Formule za preverjanje lekcije.

Če še niste delali z Excelom, boste kmalu ugotovili, da to ni le preglednica za vnos številk. Seveda lahko v Excelu samo preštejete zneske v vrsticah in stolpcih, lahko pa tudi izračunate hipotekarna plačila, rešite matematične in inženirske probleme ter poiščete najugodnejše možnosti glede na dane spremenljivke.

V Excelu se vse to naredi s pomočjo formul v celicah. Te formule se uporabljajo za izračune in druga dejanja s podatki na delovnem listu. Formula se vedno začne z enačbo (=), nakar lahko vnesete številke, matematične operatorje (na primer znaka + in - za seštevanje in odštevanje) ter vgrajene funkcije Excel, ki močno razširijo zmožnosti formul.

Spodaj je primer formule, ki pomnoži 2 s 3 in rezultatu doda 5, da dobimo 11.

Sledijo primeri formul, ki jih lahko uporabite na delovnih listih.

Deli Excelove formule

Formula lahko vsebuje tudi enega ali več elementov, kot je funkcije, povezave, operaterji in konstante.

Vrstni red dejanj v formulah

V nekaterih primerih lahko vrstni red ocenjevanja vpliva na vrednost, ki jo vrne formula, zato je pomembno razumeti standardni vrstni red ocenjevanja in vedeti, kako ga spremeniti, da dobite želene rezultate.

Uporaba funkcij in ugnezdenih funkcij v Excelovih formulah

Funkcije so vnaprej določene formule, ki izvajajo izračune za dane vrednosti - argumente - v določenem vrstnem redu ali vzorcu. Funkcije se lahko uporabljajo za izvajanje enostavnih in zapletenih izračunov. Vse Funkcije Excel lahko vidite na zavihku Formule.

Uporaba povezav v Excelovih formulah

Povezava kaže na celico ali obseg celic na delovnem listu in obvešča Microsoft Excel, kjer se nahajajo vrednosti ali podatki, ki jih zahteva formula. S pomočjo povezav lahko podatke v različnih delih delovnega lista uporabite v eni formuli in uporabite vrednost ene celice v več formulah. Poleg tega lahko navedete sklice na celice v različnih listih enega delovnega zvezka ali na celice iz drugih knjig. Sklici na celice v drugih knjigah se imenujejo povezave ali xrefs.

Uporaba imen v Excelovih formulah

Ustvarite lahko posebna imena, ki se nanašajo na celice, obsege celic, formule, konstante in Excelove tabele. Ime je pomenljiva kratka oznaka, ki pojasnjuje namen sklica na celico, konstante, formule ali tabele, saj jo je na hitro težko razumeti. Sledijo primeri imen in kako jih lahko uporabimo za lažje razumevanje formul.

Primer 1

2. primer

Kopirajte vzorčne podatke iz spodnje tabele in jih prilepite v celico A1 novega Excelov delovni list... Če želite prikazati rezultate formul, jih izberite in pritisnite F2, nato pa pritisnite Enter. Poleg tega lahko prilagodite širino stolpcev glede na podatke, ki jih vsebujejo.

Opomba: V formulah v stolpcih C in D se določeno ime Prodaja nadomesti s sklicevanjem na obseg A9: A13, ime Informacije o prodaji pa se nadomesti s obsegom A9: B13. Če knjiga nima teh imen, bodo formule v D2: D3 vrnile napako #NAME?

Primer primera	Primer, ki ne uporablja imen	Primer uporabe imen	Formula in rezultat z uporabo imen
	"= SUM (A9: A13)	"= SUM (prodaja)	SUM (prodaja)
	"= BESEDILO (POGLED (MAX (A9: 13), A9: B13,2, FALSE)," dd / mm / llll ")	"= BESEDILO (VLOOKUP (MAX (prodaja), informacije o prodaji, 2, FALSE)," dd.mm.llll) "	BESEDILO (VLOOKUP (MAX (prodaja), informacije o prodaji, 2, FALSE), "dd.mm.llll")
	Datum prodaje

Za več informacij glejte članek Določanje in uporaba imen v formulah.

Uporaba matričnih formul in konstant matrike v Excelu

Formula matrike lahko izvede več izračunov in nato vrne eno vrednost ali skupino vrednosti. Formula matrike obdeluje več nizov vrednosti, imenovanih argumenti matrike. Vsak argument matrike mora vsebovati enako število vrstic in stolpcev. Formula matrike se ustvari na enak način kot druge formule, s to razliko, da za vnos formule uporabite CTRL + SHIFT + ENTER. Nekatere vgrajene funkcije so formule matrike in jih je treba vnesti kot nize, da dobite pravilne rezultate.

Namesto sklicev lahko uporabite konstante matrike, če vam ni treba vnesti vsake konstante v ločena celica na listu.

Uporaba formule matrike za izračun ene ali več vrednosti

Opomba: Ko vnesete matrično formulo, jo Excel samodejno zapre v zavite oklepaje (in). Če poskušate ročno vnašati zavite oklepaje, bo Excel prikazal formulo kot besedilo.

Uporaba konstant matrike

V običajno formulo lahko vnesete sklic na celico z vrednostjo ali na samo vrednost, imenovano tudi konstanta. Podobno lahko v formulo matrike vnesete sklic na matriko ali niz vrednosti, ki jih vsebujejo celice (včasih imenovane konstanta matrike). Formule matrike sprejemajo konstante na enak način kot druge formule, vendar je treba konstante matrike vnesti v določeni obliki.

Konstante matrike lahko vsebujejo številke, besedilo, logične vrednosti, na primer TRUE ali FALSE, ali vrednosti napak, kot je # N / A. Ena konstanta matrike lahko vsebuje vrednosti različnih vrst, na primer (1,3,4; TRUE, FALSE, TRUE). Števila v konstantah matrike so lahko celoštevilska, decimalna ali eksponentna oblika. Besedilo mora biti priloženo z dvojnimi narekovaji, na primer "Torek".

Prepričajte se, da so pri formatiranju konstant matrike izpolnjene naslednje zahteve.

Konstante so zaprte v zavite oklepaje ( { } ).

Stolpci so ločeni z vejicami ( , ). Če želite na primer predstaviti vrednosti 10, 20, 30 in 40, vnesite (10,20,30,40). Ta konstanta matrike je matrika 1-do-4 in ustreza sklicu na eno vrstico in štiri stolpce.

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 celicah pod seboj, lahko ustvarite konstanto matrike 2 na 4: (10,20,30, 40; 50, 60,70,80).

Brisanje formule

Skupaj s formulo se izbrišejo tudi rezultati njenega izračuna. Lahko pa formulo izbrišete sami in rezultat njenega izračuna pustite kot vrednost v celici.

Debelo črevo ( : ), sklici na prvo in zadnjo celico v obsegu so ločeni. Na primer: A1: A5.

Navedeni zahtevani argumenti

Funkcija ima lahko zahtevane in neobvezne argumente (slednji so v sintaksi navedeni v oglatih oklepajih). Vnesti je treba vse zahtevane argumente. Poskusite tudi, da ne vnašate preveč argumentov.

Formula vsebuje največ 64 stopenj ugnezdenja funkcij

Ne more biti več kot 64 ravni gnezdenja.

Imena knjig in listov so v enojnih narekovajih

Če imena listov ali delovnih zvezkov, ki jih sklicujete na celice ali vrednosti, vsebujejo neabecedne znake, jih morate priložiti v enojne narekovaje ( " ).

Določena je pot do zunanjih knjig

Številke, vnesene brez oblikovanja

Pri vnosu števil v formulo ne uporabljajte znakov za dolar, ker se z njimi označujejo absolutne reference. Na primer, namesto vrednosti $1000 vstopiti 1000 .

Pomembno: Izračunani rezultati formul in nekaterih funkcij Excelovega delovnega lista se lahko nekoliko razlikujejo v računalnikih z operacijskim sistemom x86 ali x86-64 z Windows in računalniki z operacijskim sistemom Windows RT z operacijskim sistemom ARM.

Tako kot v večini objav v blogu so tudi v tej objavi Excelove tabele tiste, ki jih imenujem obsegi celic, na katere se uporablja ukaz Polje - Mize - Tabela ... Več o namenu in koristih takšnega postopka si lahko preberete v. Če tega prispevka niste prebrali, boste težje vzeli tukaj predstavljene informacije.

Zdaj pa si podrobneje poglejmo, kako formule delujejo v tabelah.

Vrstica skupaj

Za izračun vsote imajo tabele vrstico s seštevki. Če ga želite prikazati, izberite katero koli celico v tabeli in potrdite polje na traku Konstruktor - Možnosti sloga tabele - Skupna vrstica ... Vrstica "Skupaj" se bo prikazala pod tabelo. V tej vrstici izberite katero koli celico za izračun skupnih vrednosti stolpcev. Na spustnem seznamu izberite formulo, ki jo bo program uporabil za izračun vsote. Na primer Vsota, Najmanj, Povprečje itd. Kliknite "Več funkcij ...", da izberete drugo funkcijo.

Skupaj vrstica v Excelu

Formulo za izračun vsote lahko napišete tudi sami, napisana je enako kot običajne formule, le da se namesto naslovov celic prikažejo njihova imena iz glav tabel. Navedite sklice na celice z miško, program bo sam nadomestil njihova imena.

Ne pozabite, da vrstica s seštevki obdeluje celice tabele, ki so vidne na zaslonu. Če ste filter uporabili za nekatere vrednosti tabele, vsote bodo preračunane samo za izbrane podatke.

Zapisovanje formul v stolpce

Če morate izračunati v stolpcih tabele, je to še lažje kot v običajnem obsegu celic. Ustvari nov stolpec v tabeli. Kazalec postavite v katero koli celico v tem stolpcu in zapišite formulo. Uporabite metodo usmerjanja miške na sklice na celice. Program bo namesto v formulah prikazal imena stolpcev, kar bo olajšalo zaznavanje formule. Pritisnite Enter in bodite presenečeni! Excel ne bo samo izračunal rezultata, temveč tudi kopiral formulo v vse celice v stolpcu. Formule vam niti ni treba ročno kopirati!

Excelove formule

Ta funkcija ni vedno uporabna, zato jo lahko onemogočite. Če želite to narediti, pri vstavljanju formule kliknite pametno oznako in izberite » Ne ustvarjaj samodejno izračunanih stolpcev "

Prilagajanje formul Excel tabel

Sklicevanje na tabelo po imenu

Na vsebino tabele se lahko sklicujete po imenu. Če želite določiti ime tabele, ga vnesite v polje na traku: Konstruktor - Lastnosti - Ime tabele .

Zdaj se lahko za izvajanje nekaterih operacij s podatki v tabeli sklicujete nanje po imenu. Naša tabela se na primer imenuje Ljudje:

• Formula = Vsote (Ljudje) bodo poskušali sešteti vse celice tabele
• Formula = vsota (People [Sales_Sum]) bo določil vsoto podatkov v stolpcu "Količina_prodaje" v tabeli "Ljudje"
• = Ljudje [@Sales_Sum] / 100- bo delil s 100 prodaj iz iste vrstice

Če se številke vrstic celice s formulo in celice v tabeli ne ujemata, bo Excel prikazal tradicionalni sklic s koordinatami.

Uporaba formul v tabelah je zelo avtomatizirana in intuitivna. Medtem ko jih bodo mnogi sprva težko uporabljali, 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 urejevalnik preglednic v Excelu prvotno zasnovan za matematične, algebrske in druge izračune, za katere se uporabljajo vnosi posebnih formul. Glede na to, kako napisati formulo v Excelu, je treba upoštevati, da se večina vnosnih operacij bistveno razlikuje od vsega, kar je običajno uporabljati v vsakdanjem življenju. Se pravi, da imajo same formule nekoliko drugačno obliko in uporabljajo popolnoma drugačne operatorje, ki se uporabljajo pri pisanju standardnih izračunov.

Razmislimo o vprašanju, kako napisati formulo v Excelu z nekaj preprostimi primeri, ne da bi se dotaknili zapletenih operacij, za katere morate program natančno preučiti, da ga razumete. 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 koncepti

Torej se vnos vrednosti formul v program nekoliko razlikuje od običajnih operacij, uporabljenih simbolov in uporabljenih operaterjev. Pri reševanju problema, kako napisati formulo v Excelu, je treba graditi na osnovnih konceptih, ki se uporabljajo v skoraj vseh računalniških sistemih.

Dejstvo je, da stroj ne razume vnosa kombinacije, kot je "2 x 2", ali postavljanja skupne komponente zunaj oklepajev ("2 + 2) 5"). Za to je na voljo več vrst simbolov, ki so predstavljeni v spodnji tabeli, ne da bi šteli logične operatorje.

V tem primeru se prednost izvajanja operacij začne od stopnje in konča z dodajanjem in odštevanjem. Poleg tega, čeprav je Excel mogoče uporabljati kot običajni kalkulator, morate praviloma določiti številke celic ali obsege celic za izračune. Samoumevno je, da mora biti format podatkov v kateri koli taki celici nastavljen na ustrezen (vsaj številski).

Vsota in razlika

Kako napisati formulo vsote ali razlike v Excelu? Začnimo torej z najpreprostejšim, ko morate izračunati znesek. Znak enačbe najprej vnesemo v vrstico s formulami (poleg tega za vse operacije), nato pa vnesemo zahtevano formulo. V primeru običajnega kalkulatorja lahko za nameščeno celico določite "= 2 + 2".

Če se seštevanje izvede za vrednosti, vnesene neposredno v druge celice (na primer A1 in A2), formula postane "= A1 + A2". Nenavadna je uporaba dodatnih operaterjev, ki vsoto priložijo v oklepaje. Za razliko - ista stvar, le z minusom namesto s plusom.

Ko morate določiti število celic ali njihov obseg, lahko uporabite poseben ukaz sum (v ruski različici "SUM", v angleščini - SUM). Ko določite več celic, je videti tako: "= SUM (A1; A2)", za obseg - "SUM (A1: A10)", pod pogojem, da morate izračunati vsoto vseh števil v celicah od prve do deseti. Če načeloma nastavite aktivno celico, ki se nahaja takoj za zadnjo v stolpcu z začetnimi vrednostmi, formule ni treba vnašati, ampak preprosto kliknite gumb za samodejno seštevanje.

Množenje, deljenje in stopnjevanje

Zdaj pa poglejmo, kako v Excelu napišemo formulo množenja ali deljenja. Vrstni red je enak kot pri vnosu vsote ali razlike, le operaterji se razlikujejo.

Za delo se uporablja oblika "= A1 * A2", za količnik - "A1 / A2". Mimogrede, gre za popolnoma enake ukaze, ki jih lahko najdemo pri uporabi običajnega kalkulatorja sistema Windows.

Simbol "^" se uporablja za stopnjevanje. Za vrednost v celici A1, ki jo je na primer treba na kvadrat, se uporabi formula "= A1 ^ 2".

Odstotni izračuni

Če se ne dotaknete zapletenih izračunov, je vse tudi preprosto. Kako napisati formulo z odstotki v Excelu?

Dovolj je, da vnesete formulo oblike "= A1 * 5%", po kateri boste v tisti celici dobili prav tistih pet odstotkov vrednosti.

Uporaba vnosa formule, ki temelji na izbiri celice

Toda vse to je bilo povezano z ročno dodelitvijo ali tako imenovanim neposrednim vnosom formul (neposredni ali neposredni vnos). Pravzaprav je včasih koristno uporabiti miško in tipko Ctrl.

Medtem ko držite tipko miške, lahko preprosto izberete želene celice, že prej pa vnesete zahtevani izračun v vrstico s formulami. Celice bodo dodane neposredno v vrstico s formulami. Toda glede na vrsto formule morate včasih oklepaje postaviti ročno.

Absolutni, relativni in mešani tipi celic

Ločeno je treba omeniti, da program lahko uporablja več vrst celic, da ne omenjam podatkov, ki jih vsebujejo.

Absolutna celica je nespremenjena in je označena kot $ A $ 1, relativno je sklic na običajno lokacijo (A1), mešano - obstaja kombinacija sklicev na absolutne in relativne celice ($ A1 ali A $ 1). Običajno se takšni formati uporabljajo pri ustvarjanju formul, kadar so podatki vključeni v različne liste knjige ali celo v različne datoteke.

Formule VLOOKUP

Na koncu poglejmo, kako v Excelu napišemo formulo VLOOKUP. Ta tehnika omogoča vstavljanje podatkov iz enega obsega v drugega. V tem primeru je metoda nekoliko podobna tisti, ki se uporablja za reševanje problema, kako v Excelu napisati formulo "Pogoj", ki uporablja simbole, prikazane v zgornji tabeli.

Takšni izračuni so na splošno kot preprost filter, uporabljen za stolpce, kjer želite filtrirati samo natančne vrednosti, ne pa približnih vrednosti.

Pri tej možnosti se najprej prek "Čarovnika za funkcije" uporabi obseg vrednosti prvotne (prve) tabele, v polju "Tabela" je prikazan drugi obseg s popravljanjem vsebine (F4), nato stolpec je prikazana številka, v polju za prikaz intervala pa je nastavljena vrednost "FALSE", če dejansko pri filtriranju res potrebujete le natančne, ne pa približne vrednosti. Takšne formule se praviloma uporabljajo bolj v skladišču ali računovodstvu, kadar ni mogoče namestiti nobenih specializiranih programskih izdelkov.

Zaključek

Treba je še povedati, da tukaj niso opisane vse formule, ki jih je mogoče uporabiti v Excelovem urejevalniku preglednic. To je tako rekoč le osnova. Pravzaprav, če se poglobite v trigonometrijo ali računate logaritme, matrike ali celo tenzorske enačbe, je vse videti veliko bolj zapleteno. Da pa bi vse to preučili, je treba temeljito preučiti priročnik za sam urejevalnik. In tu ne gre za to, 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 nič skupnega z urejevalnikom preglednic, a so jo navdušenci reproducirali na svojem področju v Excelu.

V preostalem je treba jasno razumeti, da bo po preučevanju primitivnih formul ali dejanj s podatki mogoče zlahka obvladati bolj zapletene izračune, na primer z ustvarjanjem navzkrižnih sklicev, uporabo različnih vrst skriptov ali skripte VB itd. Vse to zahteva čas, zato se boste, če želite program in vse njegove možnosti maksimalno preučiti, morali potiti nad teoretičnim delom.