Selles õpetuses tutvustame, kuidas Excelis keerukat valemit luua ja heidame pilgu ka sellele tüüpilised veadmis algajatest kasutajatest tulenevad tähelepanematuse tõttu. Kui olete Exceli uus kasutaja, soovitame kõigepealt viidata õppetunnile, kus arutasime lihtsate valemite loomist.

Kuidas Excelis keerukat valemit luua

Allpool toodud näites demonstreerime, kuidas Excel arvutab keerukad valemid toimingute teostamise järjekorra põhjal. Selles näites tahame arvutada toiduteenuste müügimaksu summa. Selleks kirjutage lahtrisse D4 järgmine avaldis: \u003d (D2 + D3) * 0,075... See valem liidab kõigi arvel olevate üksuste väärtuse ja korrutab seejärel käibemaksu summaga 7,5% (alla kirjutatud kui 0,075).

Excel järgib järjekorda ja lisab kõigepealt sulgudes olevad väärtused: (44.85+39.90)=$84.75 ... Seejärel korrutab ta selle arvu maksumääraga: $84.75*0.075 ... Arvestuse tulemus näitab, et müügimaks tuleb $6.36 .

Äärmiselt oluline on keeruliste valemite sisestamine õiges järjekorras. Vastasel juhul võivad Exceli arvutused olla ebatäpsed. Meie puhul tehakse sulgude puudumisel kõigepealt korrutamine ja tulemus on vale. Sulgudes on parim viis määrata arvutuste järjekord Excelis.

Looge keerukad valemid, kasutades toimingute järjekorda

Allpool toodud näites kasutame linke koos kvantitatiivsete andmetega keeruka valemi loomiseks, mis arvutab toidukulude kogumaksumuse. Valem arvutab iga menüüelemendi maksumuse ja liidab seejärel kõik väärtused kokku.

Lugemise hõlbustamiseks võite lisada sulgude igasse valemisse. Kuigi see ei muuda selle näite arvutamise tulemust, võime siiski korrutise sulgudesse lisada. See teeb selgeks, et see käivitatakse enne lisamist.

Excel ei hoiata alati valemivigade eest, seega peate kõik oma valemid ise kontrollima. Selle õppimiseks lugege õppetundide kontrollimise valemeid.

Kui te pole varem Exceliga töötanud, saate varsti teada, et see pole ainult arvutustabel numbrite sisestamiseks. Muidugi saate Excelis lihtsalt lugeda summad ridadesse ja veergudesse, kuid võite ka arvutada hüpoteegimakseid, lahendada matemaatika- ja inseneriprobleeme ning leida kõige soodsamad võimalused sõltuvalt antud muutuja väärtustest.

Excelis tehakse seda kõike lahtrites olevate valemite abil. Neid valemeid kasutatakse arvutustabeli ja muude toimingute tegemiseks töölehe andmetega. Valem algab alati võrdusmärgiga (\u003d), mille järel saate sisestada arvud, matemaatilised operaatorid (näiteks liitmise ja lahutamise märgid + ja -) ning sisseehitatud Exceli funktsioonid, mis laiendavad oluliselt valemite võimalusi.

Allpool on näide valemist, mis korrutab 2 3-ga ja lisab tulemuse 5, saades 11.

Allpool on näited valemitest, mida saate töölehtedel kasutada.

Exceli valemi osad

Valem võib sisaldada ka ühte või mitut elementi, näiteks funktsioon, lingid, operaatorid ja konstandid.

Toimingute järjekord valemites

Mõnel juhul võib hindamise järjekord mõjutada valemi tagastatavat väärtust, seetõttu on oluline mõista hindamise standardset järjekorda ja teada, kuidas seda soovitud tulemuste saamiseks muuta.

Funktsioonide ja sisestatud funktsioonide kasutamine Exceli valemites

Funktsioonid on eelnevalt määratletud valemid, mis teostavad antud väärtuste - argumentide - arvutusi kindlas järjekorras või mustrina. Funktsioonide abil saab teha nii lihtsaid kui ka keerukaid arvutusi. Kõik exceli funktsioonid saab näha vahekaardilt Valemid.

Linkide kasutamine Exceli valemites

Link osutab töölehe lahtrile või lahtrivahemikule ja annab sellest teada Microsoft Excel, kus asuvad valemis nõutavad väärtused või andmed. Linkide abil saate ühes valemis kasutada töölehe eri osade andmeid, samuti kasutada ühe lahtri väärtust mitmes valemis. Lisaks saate määrata lingid ühe töövihiku erinevatele lehtedele lahtritele või teiste raamatute lahtritele. Viiteid teiste raamatute lahtritele nimetatakse linkideks või xrefideks.

Nimede kasutamine Exceli valemites

Lahtritele, lahtrivahemikele, valemitele, konstantidele ja Exceli tabelitele viitamiseks saate luua konkreetsed nimed. Nimi on tähendusrikas lühike tähis, mis selgitab lahtriviite, konstandi, valemi või tabeli eesmärki, kuna seda võib olla raske mõista ühe pilguga. Järgnevalt on toodud näited nimedest ja kuidas neid saab kasutada valemite hõlpsamaks mõistmiseks.

Näide 1

Näide 2

Kopeerige näidisandmed allolevast tabelist ja kleepige see uue lahtrisse A1 exceli tööleht... Valemite tulemuste kuvamiseks valige need, vajutage klahvi F2 ja seejärel vajutage sisestusklahvi. Lisaks saate veergude laiust vastavalt nende andmetele reguleerida.

Märge: Veergude C ja D valemites asendatakse konkreetne nimi Müük viitega vahemikule A9: A13 ja nimi Müügiteave asendatakse vahemikuga A9: B13. Kui raamatul neid nimesid pole, tagastavad valemid jaotises D2: D3 vea #NIMI?

Näite tüüp	Näide, mis ei kasuta nimesid	Näide nimede kasutamisest	Valem ja tulemus nimede abil
	"\u003d SUM (A9: A13)	"\u003d SUM (müük)	SUM (müük)
	"\u003d TEXT (VLOOKUP (MAX (A9: 13), A9: B13,2, FALSE)," pp / kk / aaaa ")	"\u003d TEXT (VLOOKUP (MAX (müük), müügiteave, 2, FALSE)," pp.kk.aaaa ")	TEXT (VLOOKUP (MAX (müük), müügiteave, 2, FALSE), "pp.kk.aaaa")
	Müügikuupäev

Lisateavet leiate artiklist Nimede määratlemine ja kasutamine valemites.

Massiivivalemite ja massiivikonstandide kasutamine Excelis

Massiivvalem võib teha mitu arvutust ja seejärel tagastada ühe väärtuse või väärtuste rühma. Massiivivalem töötleb mitut väärtushulka, mida nimetatakse massiivi argumentideks. Iga massiivi argument peab sisaldama sama arvu ridu ja veerge. Massiivvalem luuakse samamoodi nagu teised valemid, selle erinevusega, et valemi sisestamiseks kasutate klahve CTRL + SHIFT + ENTER. Mõned sisseehitatud funktsioonid on massiivivalemid ja õigete tulemuste saamiseks tuleb need sisestada massiividena.

Massiivikonstande saab viidete asemel kasutada, kui te ei pea iga konstandi sisestama eraldi lahter lehel.

Massiivvalemi kasutamine ühe või mitme väärtuse arvutamiseks

Märge: Massiivivalemi sisestamisel ümbritseb Excel selle automaatselt lokkis sulgudes (ja). Kui proovite lokkisulgusid käsitsi sisestada, kuvab Excel valemi tekstina.

Massiivkonstandide kasutamine

Tavalises valemis saate sisestada viite väärtusega lahtrile või väärtusele ise, mida nimetatakse ka konstandiks. Samamoodi saate massiivi valemis sisestada viite lahtrites sisalduvale massiivile või väärtuste massiivile (mõnikord nimetatakse massiivikonstandiks). Massiivivalemid aktsepteerivad konstante samamoodi nagu teisi valemeid, kuid massiivikonstandid tuleb sisestada kindlas vormingus.

Massiivikonstandid võivad sisaldada numbreid, teksti, loogilisi väärtusi nagu TRUE või FALSE või veaväärtusi, näiteks # N / A. Üks massiivikonstant võib sisaldada erinevat tüüpi väärtusi, näiteks (1,3,4; TRUE, FALSE, TRUE). Massiivikonstandides olevad numbrid võivad olla täis-, kümnend- või eksponentsiaalvormingus. Tekst peab olema lisatud jutumärkidesse, näiteks "teisipäev".

Massiivikonstandide vormindamisel veenduge, et järgmised nõuded oleksid täidetud.

Konstandid on suletud lokkidega ( { } ).

Veerud eraldatakse komadega ( , ). Näiteks väärtuste 10, 20, 30 ja 40 tähistamiseks sisestage (10,20,30,40). See massiivikonstant on maatriks ükshaaval ja vastab ühe- ja neljaveerulisele viitele.

Erinevatelt joontelt lahtrite väärtused eraldatakse semikoolonitega ( ; ). Näiteks väärtuste 10, 20, 30, 40 ja 50, 60, 70, 80 esitamiseks üksteise all olevates lahtrites võite luua massiivi 2x4 järjestuse: (10,20,30, 40; 50, 60,70,80).

Valemi kustutamine

Koos valemiga kustutatakse ka selle arvutamise tulemused. Kuid võite valemi ise kustutada ja jätta selle arvutuse tulemus lahtrisse väärtuseks.

Koolon ( : ), viited vahemiku esimesele ja viimasele lahtrile on eraldatud. Näiteks: A1: A5.

Nõutavad argumendid on määratud

Funktsioonil võivad olla kohustuslikud ja valikulised argumendid (viimased on süntaksis märgitud nurksulgudega). Kõik nõutavad argumendid tuleb sisestada. Samuti proovige mitte sisestada liiga palju argumente.

Valem sisaldab mitte rohkem kui 64 funktsioonide pesitsemise taset

Pesitsemistase ei tohi olla üle 64.

Raamatu ja lehtede nimed on lisatud ühte jutumärki

Kui viidatud lahtrite või töövihikute lahtrid või väärtused sisaldavad tähtedeta tähti, peate need lisama üksikute jutumärkidesse ( " ).

Väliste raamatute tee on täpsustatud

Numbrid sisestati ilma vormindamiseta

Ärge kasutage valemis numbrite sisestamisel dollarimärke, sest neid kasutatakse absoluutsete viidete tähistamiseks. Näiteks väärtuse asemel $1000 vaja sisestada 1000 .

Tähtis: Valemite ja mõne Exceli töölehe funktsioonide arvutatud tulemused võivad x86 või x86-64 Windowsi arvutites ja ARM-põhistes Windows RT arvutites veidi erineda.

Selles postituses, nagu ka enamikus ajaveebipostitustes, nimetan Exceli tabeleid lahtrivahemikeks, millele käsku rakendatakse Kast - tabelid - tabel ... Lisateavet sellise protseduuri eesmärgi ja eeliste kohta leiate lehelt. Kui te pole seda postitust lugenud, on teil siin esitatud teavet raskem seedida.

Vaatame nüüd lähemalt, kuidas valemid tabelites toimivad.

Rida kokku

Kogusummade arvutamiseks on tabelitel rida Kogusummad. Selle kuvamiseks valige tabeli sees mõni lahter ja märkige lindil ruut Konstruktor - tabeli stiilivalikud - rida kokku ... Rida "Kokku" ilmub tabeli alla. Veerusummade arvutamiseks valige ükskõik milline lahter selles reas. Valige rippmenüüst valem, mida programm kasutab kogusummade arvutamiseks. Näiteks summa, miinimum, keskmine jne. Erineva funktsiooni valimiseks klõpsake nuppu "Rohkem funktsioone ...".

Exceli rida kokku

Kogusummade arvutamiseks võite ka ise kirjutada valemi, see on kirjutatud samamoodi nagu tavalised valemid, ainult lahtrite aadresside asemel kuvatakse nende nimed tabeli päistest. Määrake hiirega viited lahtritele, programm asendab nende nimed ise.

Pidage meeles, et rida Kogusummad töötlevad ekraanil nähtavaid tabelirakke. Kui olete mõne tabeli väärtuse järgi filtri rakendanud, kogusummad arvutatakse ümber ainult valitud andmete puhul.

Valemite kirjutamine veergudesse

Kui peate arvutusi tegema tabeli veergudes, on see veelgi lihtsam kui tavalises lahtrivahemikus. Looge tabelis uus veerg. Asetage kursor selle veeru suvalisse lahtrisse ja kirjutage valem üles. Kasutage hiire suunamist lahtriviidetele. Programm kuvab valemite asemel veerunimed, see hõlbustab valemi tajumist. Vajutage sisestusklahvi ja imestage! Excel mitte ainult ei arvuta tulemust, vaid kopeerib valemi ka veeru kõikidesse lahtritesse. Te ei pea isegi valemeid käsitsi kopeerima!

Exceli valemid

See funktsioon pole alati kasulik, nii et saate selle keelata. Selleks klõpsake valemi sisestamisel nutisildil ja valige “ Ära looge arvutatud veerge automaatselt "

Exceli tabelivalemite kohandamine

Tabelile viitamine nime järgi

Tabeli sisule saate viidata nime järgi. Tabeli nime määramiseks sisestage see lindil olevale väljale: Konstruktor - atribuudid - tabeli nimi .

Nüüd saate mõne toimingu tegemiseks tabeli andmetega sellele viidata nime järgi. Näiteks nimetatakse meie tabelit inimesed:

• Vormel \u003d summad (inimesed) püüab summeerida kõik tabeli lahtrid
• Valem \u003d summa (inimesed [müügi_summa]) määrab tabeli "Inimesed" veerus "Summa_müük" olevate andmete summa
• \u003d Inimesed [@Sales_Sum] / 100 - jagatakse sama rea \u200b\u200b100 müügiga

Kui valemiga lahtri ja tabeli lahtri reanumbrid ei ühti, kuvab Excel traditsioonilise viite koos koordinaatidega.

Valemite kasutamine tabelites on väga automatiseeritud ja intuitiivne. Kuigi paljudel on nende kasutamine alguses keeruline, siis mõne päeva pärast harjute ja hindate kõiki eeliseid, mida Exceli arvutustabelid pakuvad.

Täname, et lugesite seda postitust. Ja järgmises artiklis ma ütlen teile. Kohtumiseni blogilehtedel.

Nagu teate, oli Exceli arvutustabeli kontoriredaktor algselt mõeldud matemaatiliste, algebraliste ja muude arvutuste tegemiseks, mille jaoks kasutatakse spetsiaalsete valemite sisestust. Valemi Excelisse kirjutamise valguses tuleks siiski meeles pidada, et enamik sisendoperatsioone erinevad põhimõtteliselt kõigest, mida on tavaelus kombeks kasutada. See tähendab, et valemid ise on veidi erineva kujuga ja kasutavad täiesti erinevaid operaatoreid, mida kasutatakse standardsete arvutuste kirjutamisel.

Mõelgem küsimusele, kuidas Excelis valemit kirjutada, kasutades mõnda lihtsat näidet, puudutamata keerukaid toiminguid, mille mõistmiseks peate programmi piisavalt põhjalikult uurima. Kuid isegi eelnevad teadmised annavad igale kasutajale arusaamise valemite kasutamise erinevatest juhtumitest.

Valemi kirjutamine Excelis: põhimõisted

Seega on valemiväärtuste sisestamine programmis mõnevõrra erinev tavalistest toimingutest, kasutatavatest sümbolitest ja kasutatavatest operaatoritest. Valemi Excelisse kirjutamise probleemi lahendamisel tuleb lähtuda peamistest mõistetest, mida kasutatakse peaaegu kõigis arvutisüsteemides.

Fakt on see, et masin ei saa aru sellise kombinatsiooni nagu "2 x 2" sisestamisest või ühise komponendi sulgudest väljapoole panemisest ("2 + 2) 5"). Selleks pakutakse mitut tüüpi sümboleid, mis on esitatud allolevas tabelis, arvestamata loogilisi operaatoreid.

Sel juhul algab toimingute sooritamise prioriteet astmest ja lõpeb liitmise ja lahutamisega. Lisaks, kuigi Exceli saab kasutada nagu tavalist kalkulaatorit, peate reeglina arvutuste jaoks määrama lahtrite numbrid või lahtrivahemikud. On ütlematagi selge, et igas sellises lahtris tuleb andmevorming seadistada sobivaks (vähemalt numbriliseks).

Summa ja erinevus

Kuidas Excelis summa või vahe valem kirjutada? Alustame siis kõige lihtsamast, kui peate summa arvutama. Valemireale (pealegi kõigi toimingute korral) sisestatakse kõigepealt võrdusmärk, mille järel sisestatakse soovitud valem. Tavapärase kalkulaatori korral saate installitud lahtrile määrata "\u003d 2 + 2".

Kui liitmine toimub otse muudesse lahtritesse sisestatud väärtuste jaoks (näiteks A1 ja A2), saab valemist "\u003d A1 + A2". Pole harvad juhud, kui sulge kasutatakse täiendavate operaatorite kasutamiseks. Vahe poolest - sama asi, ainult plussiga miinusega.

Kui peate määrama lahtrite arvu või nende vahemiku, saab kasutada spetsiaalset käsku summa (venekeelses versioonis "SUM", inglise keeles - SUM). Mitme lahtri määramisel näeb see välja järgmine: "\u003d SUM (A1; A2)", vahemiku jaoks - "SUM (A1: A10)", tingimusel et peate arvutama kõigi lahtrites olevate arvude summa esimesest kuni kümnes. Põhimõtteliselt, kui määrate aktiivse lahtri, mis asub kohe pärast algväärtustega veerus viimast, ei pea te valemit sisestama, vaid klõpsake lihtsalt automaatse summeerimise nuppu.

Korrutamine, jagamine ja astendamine

Nüüd vaatame, kuidas Excelis korrutamis- või jagamisvalemit kirjutada. Järjekord on sama mis summa või vahe sisestamisel, erinevad ainult operaatorid.

Teose puhul kasutatakse vormi "\u003d A1 * A2", jagatis - "A1 / A2". Need on muide täpselt samad käsud, mida võib leida tavalise Windowsi kalkulaatori kasutamisel.

Eksponentimiseks kasutatakse sümbolit "^". Lahtri A1 väärtuse jaoks, mis tuleb näiteks ruutu panna, rakendatakse valemit \u003d \u003d A1 ^ 2.

Protsendi arvutused

Kui te ei puuduta keerukaid arvutusi, on kõik huvitatud ka sellest, et kõik on lihtne. Kuidas Excelis protsentidega valem kirjutada?

Piisab sisestada valem kujul "\u003d A1 * 5%", mille järel saate need väga viis protsenti väärtusest vastavas lahtris.

Lahtrivalikul põhineva valemisisendi kasutamine

Kuid kõik see oli seotud käsitsi määramise või nn otsese valemite sisestamisega (otsene või otsene sisend). Tegelikult on mõnikord kasulik kasutada hiirt ja klahvi Ctrl.

Hiirenuppu all hoides saate lihtsalt soovitud lahtrid valida, olles eelnevalt valemiribale sisestanud vajaliku arvutuse. Lahtrid lisatakse otse valemiribale. Kuid sõltuvalt valemi tüübist tuleb mõnikord sulgud sisestada käsitsi.

Absoluutsed, suhtelised ja segatud rakutüübid

Eraldi tuleb märkida, et programm võib kasutada mitut tüüpi lahtrid, rääkimata nendes sisalduvatest andmetest.

Absoluutne lahtrit ei muudeta ja tähistatakse kui $ A $ 1, suhteline on viide tavalisele asukohale (A1), segatud - siin on kombinatsioon viidetest nii absoluutsele kui ka suhtelisele lahtrile ($ A1 või A $ 1). Tavaliselt kasutatakse selliseid vorminguid valemite loomisel, kui andmed on seotud raamatu erinevatel lehtedel või isegi erinevates failides.

VLOOKUP valemid

Lõpuks vaatame, kuidas VLOOKUPi valem Excelisse kirjutada. See tehnika võimaldab teil sisestada andmeid ühest vahemikust teise. Sel juhul on meetod mõnevõrra sarnane sellele, mida kasutatakse Exceli valemi "Tingimus" kirjutamise probleemi lahendamiseks, mis kasutab ülaltoodud tabelis näidatud sümboleid.

Üldiselt on sellised arvutused nagu veergudele rakendatud lihtne filter, kui soovite filtreerida ainult täpseid väärtusi, mitte ligikaudseid väärtusi.

Selles valikus kasutatakse kõigepealt funktsiooni viisardi kaudu algse (esimese) tabeli väärtuste vahemikku, teine \u200b\u200bvahemik koos sisu fikseerimisega (F4) määratakse väljale "Tabel", seejärel veerg number on näidatud ja väärtus „FALSE“ määratakse intervallivaate väljale, kui tegelikult peate filtreerimisel saama ainult täpseid, mitte ligikaudseid väärtusi. Reeglina kasutatakse selliseid valemeid rohkem laos või raamatupidamises, kui spetsiaalseid tarkvaratooteid pole võimalik installida.

Järeldus

Jääb öelda, et kõiki valemeid, mida saab Exceli arvutustabeli redaktoris kasutada, pole siin kirjeldatud. Nii-öelda on see lihtsalt põhitõed. Tegelikult, kui kaevate edasi trigonomeetriasse või arvutate logaritme, maatriksit või isegi tensorvõrrandit, tundub kõik palju keerulisem. Kuid selle kõige uurimiseks on vaja toimetaja enda käsiraamatut põhjalikult uurida. Ja see pole seotud asjaoluga, et Excelis saate andmete muutmise põhjal luua ka kõige lihtsamaid loogikamänge. Näitena võime tuua sama "mao", millel polnud algselt midagi pistmist arvutustabeli redaktoriga, kuid selle paljundasid oma ala entusiastid Excelis.

Ülejäänud osas tuleks selgelt mõista, et olles uurinud primitiivseid valemeid või toiminguid andmetega, on võimalik keerukamaid arvutusi hõlpsasti hallata, näiteks ristviidete loomisega, mitmesuguste skriptide kasutamisega või VB skriptid jne. Kõik see võtab aega, nii et kui soovite programmi ja kõiki selle võimalusi maksimaalselt uurida, peate teoreetilise osa üle higistama.