Formulat Excel dhe përdorimi i tyre. Krijoni formula vetë

Për një kohë të gjatë, i gjithë qarkullimi i dokumenteve kryhet duke përdorur kompjuterë dhe programe të veçanta aplikimi. Si rregull, pothuajse të gjitha organizatat, studentët, etj. përdorin paketën e softuerit të zyrës të zhvilluar nga Microsoft për të shtypur tekst ose për të kryer llogaritjet. Për të përpiluar në mënyrë cilësore tabela në të cilat vlerat do të rillogariten automatikisht sipas rregulla të caktuara, duhet të studiohen tërësisht formulatExcel me shembuj për të thjeshtuar llogaritjet.

Disa qeliza të tabelës llogariten bazuar në të dhënat statistikore që futen në qeliza të tjera. Për shembull, nëse është e nevojshme të llogaritet kostoja e disa produkteve të shitura në një faturë, sasia shumëzohet me çmimin dhe rezultati është vlera e kërkuar. ME spreadsheets Kjo është shumë më e lehtë për t'u bërë, sepse duke bërë një shabllon dhe më pas duke ndryshuar disa vlera statistikore, kryhet një rillogaritje. Në mënyrë që rezultati të jetë i saktë, duhet të dini plotësisht se si t'i futni saktë formulat, sepse në të kundërt, llogaritja do të kryhet gabimisht. Le të shohim disa shembuj të formulave të thjeshta që përdoren më shpesh, dhe ato do të tregojnë qartë korrektësinë e përbërjes së tyre.

Për më së shumti shembull i thjeshtë, le të llogarisim shumën e dy vlerave. Për ta bërë këtë, shkruani numrin 5 në qelizën A1 dhe numrin 3 në B1. Në mënyrë që shuma të llogaritet automatikisht, shkruani kombinimin e mëposhtëm të simboleve në qelizën C1, që do të jetë formula: =SUM(A1; B1).

Si rezultat, vlera e llogaritur do të shfaqet në ekran. Sigurisht, nuk është e vështirë të llogariten vlera të tilla, por nëse numri i termave është, për shembull, rreth 10, kjo është mjaft e vështirë për t'u bërë. Nëse ndryshoni një nga vlerat e futura, rezultati do të rillogaritet automatikisht.

Për të mos u ngatërruar se ku dhe çfarë formule është shkruar, pak mbi tabelë shfaqet një rresht në të cilin shkruhet vlera e qelizës (qoftë formula statistikore ose llogaritëse).

Për të komplikuar detyrën, ne do të fusim numrat thyesorë në qelizat A1 dhe B1, të cilat janë shumë më të vështira për t'u llogaritur. Si rezultat, C1 do të rillogaritet dhe do të shfaqet vlera e saktë.

Në Excel, ju mund të bëni jo vetëm mbledhje, por edhe operacione të tjera aritmetike: zbritje, shumëzim, ndarje. Shkrimi i një formule duhet të fillojë gjithmonë me simbolin "=" dhe duke treguar të ashtuquajturat koordinata të qelizave (në shembullin në shqyrtim, këto janë A1 dhe B1).

Krijoni formula vetë

Në shembullin e mësipërm, formula =SUM(A1;B1) ju lejon të shtoni dy vlera. Simboli "=" kuptohet si fillimi i një formule, SUM është një fjalë ndihmëse që tregon llogaritjen e shumës, A1 dhe B1 janë koordinatat e vlerave statike, të cilat veprojnë si terma dhe duhet të ndahen me një pikëpresje. ";".

Le të shqyrtojmë një shembull të shtimit të tre qelizave A1, B1, C1. Për ta bërë këtë, shkruhet një formulë pothuajse identike, por qeliza e tretë tregohet gjithashtu në kllapa. Si rezultat, duhet të shkruani kombinimin e mëposhtëm =SUM(A1;B1;C1) në qelizën D1 për të kryer llogaritjen.

Nëse ka shumë terma që shkojnë në një rresht (kolona), për shembull 10, formula mund të thjeshtohet duke specifikuar gamën e koordinatave. Si rezultat, formula do të duket si në mënyrën e mëposhtme: =SUM(A1:A10). Si qelizë për të shfaqur rezultatin e dëshiruar, zgjidhni qelizën B6. Si rezultat, ne do të marrim rezultatin e treguar në pamjen e ekranit.

Për të shumëzuar të gjitha vlerat e specifikuara në një kolonë, duhet të shkruani PRODUCT në formulë në vend të SUM.

Shënim: Nëse përdoruesi duhet të kryejë shtesa ose të tjera veprim aritmetik me disa kolona dhe rreshta, mund të specifikoni koordinatat diagonalisht të kundërta dhe do të llogaritet një grup vlerash (për të llogaritur produktin e vlerave të 10 rreshtave në kolonat A, B, C, formula do të duket si kjo: = PRODUCT(A1:C10)).

Formulat e Kombinimit

Spreadsheets janë në gjendje të kryejnë jo vetëm llogaritje të thjeshta aritmetike, por edhe llogaritje komplekse matematikore.

Për shembull, le të llogarisim shumën e vlerave dhe ta shumëzojmë atë me një faktor 1.4 nëse, kur mbledhim, vlera ishte më e vogël se 90. Nëse rezultati është më shumë se 90, atëherë duhet të shumëzojmë me 1.5. Në këtë rast, formula tashmë përmban disa operatorë dhe duket shumë më e ndërlikuar: =IF(SUM(A1:C1)<90;СУММ(А1:С1)*1,4;СУММ(А1:С1)*1,5).

Këtu tashmë janë përdorur dy funksione në vend të një: IF dhe SUM. Komanda e parë është një deklaratë e kushtëzuar që funksionon si kjo:

• nëse shuma e tre vlerave (A1:C1) është më e madhe se 90, shumëzimi kryhet me 1.4, domethënë me koeficientin e parë që tregohet pas kushtit;
• nëse (A1:C1) është më pak se 90 (si në shembullin tonë), domethënë kushti nuk plotësohet, kjo vlerë shumëzohet me koeficientin e dytë.

Në shembullin tonë ka vetëm dy funksione, por mund të ketë shumë më tepër prej tyre; programi është praktikisht i pakufizuar në këtë drejtim.

Funksionet e integruara në spreadsheets

Për të mos futur fjalë të veçanta, mund të përdorni opsionin special për të instaluar një funksion pa e shtypur. Kjo do të thotë, për të llogaritur shumën e vlerave, mund të përdorni këtë veçori dhe të zgjidhni një veprim specifik për gamën e caktuar të vlerave. Ekziston një buton i veçantë për këtë, i treguar në pamjen e ekranit.

Vlen të përmendet se ka mjaft funksione dhe shumë prej tyre mund të mos jenë të nevojshme, por disa prej tyre do të përdoren rregullisht.

Absolutisht të gjitha funksionet ndahen në kategori për ta bërë më të lehtë gjetjen e asaj që ju nevojitet.

Funksioni VLOOKUP

Nëse tabela përbëhet nga disa qindra rreshta dhe nuk është gjithmonë e mundur të gjesh pozicionin e dëshiruar, mund të përdorni funksionin VLOOKUP, i cili do të kryejë një kërkim dhe do të shfaqë rezultatin në qelizën në të cilën është shkruar formula.

Për shembull, nëse duhet të gjejmë një mbiemër që i është caktuar numri serial 007, duhet të ndjekim hapat që tregohen qartë në pamjen e ekranit.

Argumenti i parë është vlera me të cilën kryhet kërkimi. Argumenti i dytë është diapazoni i vlerave në të cilat kërkohen. Numri 2 tregon se kjo qelizë duhet të shfaqë vlerën nga kolona e dytë.

Si rezultat, merret rezultati i mëposhtëm.

Rrumbullakimi i vlerave

Në Excel, mund të kryeni rrumbullakim të saktë për të thjeshtuar llogaritjet. Për këtë qëllim ekziston një funksion i posaçëm "RRUMULL UP". Për shembull, nëse duhet të rrumbullakosim numrin e specifikuar në qelizën A1, futim në atë ngjitur formulën = ROUNDUP(A1;0), ku argumenti i dytë tregon se në cilën renditje të numrave dhjetorë duhet të rrumbullakosim (nëse është specifikuar zero , vlera përfundimtare do të jetë një numër i plotë). Si rezultat, merret rezultati i mëposhtëm.

Siç mund ta shihni nga pamja e ekranit, ka ndodhur rrumbullakimi. Për të kryer më të vogla, ekziston funksioni “ROUND BOTTOM”.

Dhe megjithëse në shikim të parë duket se fletët e llogaritura Excel janë mjaft të vështira për t'u kuptuar, ju duhet të punoni me to vetëm për një kohë të shkurtër dhe të mendoni se kjo deklaratë nuk është plotësisht e saktë. Për më tepër, përfitimet e tyre janë të vështira për t'u mbivlerësuar.

Microsoft Excel Office ka shumë funksione të përshtatshme që ju ndihmojnë të shmangni llogaritjet e gjata përpara se të vendosni të dhëna në tabela ose grafika. Për të llogaritur disa vlera, si shtimi, zbritja, raporti dhe të tjera, mund të përdorni formulat e specifikuara fillimisht. Mund t'i shtoni vetë ose të zgjidhni nga lista e atyre ekzistuese. Mësoni se si të futni një formulë hap pas hapi në këtë artikull.

Tani ju e dini saktësisht se si të krijoni formula në Excel dhe të shihni dekodimin e tyre në Function Wizard.

Në këtë mësim do të shikojmë se si të krijojmë një formulë komplekse në Excel, dhe gjithashtu të shohim gabimet tipike që përdoruesit fillestarë bëjnë për shkak të pavëmendjes. Nëse sapo keni punuar në Excel, ju rekomandojmë që së pari t'i referoheni mësimit ku diskutuam krijimin e formulave të thjeshta.

Si të krijoni një formulë komplekse në Excel

Në shembullin e mëposhtëm, ne do të demonstrojmë se si Excel llogarit formulat komplekse bazuar në rendin e operacioneve. Në këtë shembull, ne duam të llogarisim taksën e shitjes për shërbimet ushqimore. Për ta bërë këtë, shkruani shprehjen e mëposhtme në qelizën D4: =(D2+D3)*0,075. Kjo formulë do të mbledhë koston e të gjithë artikujve të faturës dhe më pas do të shumëzohet me shumën e taksës së shitjes 7,5% (shkruar si 0.075).

Excel ndjek këtë renditje dhe së pari shton vlerat në kllapa: (44.85+39.90)=$84.75 . Pastaj shumëzon këtë numër me normën e taksës: $84.75*0.075 . Rezultati i llogaritjes tregon se taksa e shitjes do të jetë $6.36 .

Është jashtëzakonisht e rëndësishme të futni formula komplekse në rendin e duhur. Përndryshe, llogaritjet e Excel mund të jenë të pasakta. Në rastin tonë, nëse nuk ka kllapa, së pari kryhet shumëzimi dhe rezultati do të jetë i pasaktë. Kllapat janë mënyra më e mirë për të përcaktuar rendin e llogaritjeve në Excel.

Krijoni formula komplekse duke përdorur rrjedhën e punës

Në shembullin e mëposhtëm, ne do të përdorim lidhje së bashku me të dhënat sasiore për të krijuar një formulë komplekse që do të llogarisë koston totale të një faturë ushqimore. Formula do të llogarisë koston e çdo artikulli të menusë dhe më pas do të shtojë të gjitha vlerat së bashku.

Ju mund të shtoni kllapa në çdo formulë për ta bërë më të lehtë për t'u kuptuar. Edhe pse kjo nuk do të ndryshojë rezultatin e llogaritjes në këtë shembull, ne ende mund ta mbyllim shumëzimin në kllapa. Kjo do të sqarojë se është kryer para shtimit.

Excel nuk ju paralajmëron gjithmonë për gabimet në një formulë, kështu që ju duhet të kontrolloni vetë të gjitha formulat tuaja. Për të mësuar se si ta bëni këtë, shikoni mësimin Kontrollimi i formulave.

Nëse nuk keni punuar më parë me Excel, së shpejti do të mësoni se nuk është thjesht një tabelë për futjen e numrave. Sigurisht, në Excel thjesht mund të llogaritni shumat në rreshta dhe kolona, ​​por gjithashtu mund të llogaritni pagesat e hipotekës, të zgjidhni problemet matematikore dhe inxhinierike dhe të gjeni opsionet më të mira bazuar në vlerat e variablave të dhëna.

Në Excel, e gjithë kjo zbatohet duke përdorur formula në qeliza. Këto formula përdoren për të kryer llogaritjet dhe veprimet e tjera me të dhënat në fletë. Një formulë fillon gjithmonë me një shenjë të barabartë (=), pas së cilës mund të futni numra, operatorë matematikorë (siç janë shenjat + dhe - për mbledhje dhe zbritje) dhe funksione të integruara në Excel që përmirësojnë shumë aftësitë e formulave.

Më poshtë është një shembull i një formule që shumëzon 2 me 3 dhe i shton 5 rezultatit për të marrë 11.

Më poshtë janë shembuj të formulave që mund të përdoren në fletët e punës.

Pjesë të një formule Excel

Një formulë mund të përmbajë gjithashtu një ose më shumë elementë si p.sh funksione, lidhjet, operatorët Dhe konstante.

Rendi i veprimeve në formula

Në disa raste, rendi i llogaritjes mund të ndikojë në vlerën e kthyer nga një formulë, kështu që është e rëndësishme të kuptoni rendin standard të llogaritjeve dhe si mund ta ndryshoni atë për të marrë rezultatet që dëshironi.

Përdorimi i funksioneve dhe funksioneve të mbivendosur në formulat e Excel

Funksionet janë formula të paracaktuara që kryejnë llogaritjet mbi vlerat e dhëna - argumentet - në një renditje të caktuar ose sipas një modeli të caktuar. Ju mund të përdorni funksione për të kryer llogaritje të thjeshta dhe komplekse. Të gjitha funksionet e Excel mund të shihen në skedën Formulat.

Përdorimi i lidhjeve në formulat Excel

Referenca tregon një qelizë ose varg qelizash në një fletë pune dhe i tregon Microsoft Excel se ku ndodhen vlerat ose të dhënat e nevojshme nga formula. Duke përdorur lidhjet, mund të përdorni të dhënat e vendosura në pjesë të ndryshme të fletës në një formulë, dhe gjithashtu mund të përdorni vlerën e një qelize në disa formula. Përveç kësaj, mund të vendosni referenca për qelizat nga fletë të ndryshme të të njëjtit libër pune ose për qelizat nga librat e tjerë të punës. Lidhjet me qelizat në librat e tjerë të punës quhen lidhje ose lidhje të jashtme.

Përdorimi i emrave në formulat e Excel

Ju mund të krijoni emra specifikë për të identifikuar qelizat, diapazonin e qelizave, formulat, konstantat dhe tabelat e Excel-it. Një emër është një stenografi kuptimplotë që shpjegon qëllimin e një referimi në një qelizë, konstante, formulë ose tabelë, pasi mund të jetë e vështirë të kuptohet thelbi i tyre me një shikim. Më poshtë janë shembuj të emrave dhe se si përdorimi i tyre i bën formulat më të lehta për t'u kuptuar.

Shembulli 1

Shembulli 2

Kopjoni të dhënat e mostrës nga tabela më poshtë dhe ngjisni në qelizën A1 të një flete pune të re Excel. Për të shfaqur rezultatet e formulave, zgjidhni ato dhe shtypni F2, më pas shtypni Enter. Për më tepër, ju mund të rregulloni gjerësinë e kolonave sipas të dhënave që ato përmbajnë.

Shënim: Në formulat në kolonat C dhe D, emri specifik "Shitje" zëvendësohet me një referencë për diapazonin A9:A13 dhe emri "SalesInfo" zëvendësohet nga diapazoni A9:B13. Nëse libri i punës nuk i përmban këta emra, formulat në D2:D3 do të kthejnë gabimin #NAME?.

Për më shumë informacion, shihni Përcaktoni dhe përdorni emrat në formula.

Përdorimi i formulave të grupeve dhe konstantave të grupeve në Excel

Një formulë grupi mund të kryejë disa llogaritje dhe më pas të kthejë një vlerë të vetme ose një grup vlerash. Një formulë grupi përpunon grupe të shumta vlerash, të quajtura argumente të grupit. Çdo argument i grupit duhet të ketë të njëjtin numër rreshtash dhe kolonash. Një formulë grupi krijohet në të njëjtën mënyrë si formulat e tjera, me ndryshimin se çelësat CTRL+SHIFT+ENTER përdoren për të futur një formulë të tillë. Disa funksione të integruara janë formula të grupeve dhe duhet të futen si vargje për të marrë rezultate të sakta.

Konstantet e grupit mund të përdoren në vend të referencave nëse nuk keni nevojë të vendosni secilën vlerë konstante në një qelizë të veçantë në fletën e punës.

Përdorni një formulë grupi për të llogaritur një ose më shumë vlera

Shënim: Kur futni një formulë grupi, Excel automatikisht e mbyll atë në kllapa kaçurrelë ( dhe ). Nëse përpiqeni të futni manualisht kllapat kaçurrelë, Excel e shfaq formulën si tekst.

Përdorimi i konstantave të vargjeve

Në një formulë të rregullt, mund të futni një referencë për një qelizë me një vlerë ose për vetë vlerën, e quajtur gjithashtu një konstante. Në mënyrë të ngjashme, në një formulë grupi, mund të futni një referencë për një grup ose një grup vlerash të përmbajtura në qeliza (nganjëherë quhet një konstante grupi). Formulat e vargjeve pranojnë konstante ashtu si formulat e tjera, por konstantet e vargjeve duhet të futen në një format specifik.

Konstantet e grupit mund të përmbajnë numra, tekst, vlera Boolean si TRUE ose FALSE, ose vlera gabimi të tilla si #N/A. Një konstante e grupit mund të përmbajë vlera të llojeve të ndryshme, për shembull (1,3,4; E VËRTETË, E FALSE, E TRUE). Numrat në konstantet e vargjeve mund të jenë numra të plotë, dhjetorë ose format eksponencial. Teksti duhet të mbyllet në thonjëza të dyfishta, për shembull "e martë".

Kur formatoni konstantet e grupit, sigurohuni që të plotësohen kërkesat e mëposhtme:

Konstantet janë të mbyllura në mbajtëse kaçurrelë ( { } ).

Kolonat e ndara me presje ( , ). Për shembull, për të përfaqësuar vlerat 10, 20, 30 dhe 40, futni (10,20,30,40). Kjo konstante e grupit është një matricë 1 me 4 dhe korrespondon me një referencë me një rresht, me katër kolona.

Vlerat e qelizave nga rreshta të ndryshëm ndahen me pikëpresje ( ; ). Për shembull, për të përfaqësuar vlerat 10, 20, 30, 40 dhe 50, 60, 70, 80 në qelizat e grumbulluara, mund të krijoni një konstante grupi 2 me 4: (10,20,30,40,50 , 60,70,80).

Heqja e një formule

Së bashku me formulën, fshihen edhe rezultatet e llogaritjes së saj. Megjithatë, ju mund ta fshini vetë formulën duke lënë rezultatin e llogaritjes së saj si vlerë në qelizë.

Duke përdorur një dy pika ( : ) ndan referencat për qelizat e para dhe të fundit në interval. Për shembull: A1: A5.

Argumentet e kërkuara të specifikuara

Një funksion mund të ketë argumente të kërkuara dhe opsionale (këto të fundit tregohen në sintaksë me kllapa katrore). Të gjitha argumentet e kërkuara duhet të futen. Gjithashtu përpiquni të mos futni shumë argumente.

Formula përmban jo më shumë se 64 nivele të foleve të funksioneve

Nuk mund të ketë më shumë se 64 nivele foleje.

Emrat e fletëve të punës dhe të fletëve janë të mbyllura në thonjëza të vetme

Nëse emrat e fletëve ose librave të punës në të cilat u referoheni qelizave ose vlerave përmbajnë karaktere jo alfabetike, ato duhet të mbyllen në thonjëza të vetme ( " ).

Është specifikuar rruga drejt librave të jashtëm

Numrat e futur pa formatim

Kur futni numra në një formulë, nuk mund të përfshini shenja të dollarit sepse ato përdoren për të treguar referenca absolute. Për shembull, në vend të vlerës $1000 duhet të futet 1000 .

E rëndësishme: Rezultatet e llogaritura të formulave dhe disa funksioneve të fletës së punës Excel mund të ndryshojnë pak në kompjuterët që përdorin Windows me arkitekturë x86 ose x86-64 dhe kompjuterët që përdorin Windows RT me arkitekturë ARM.