Përdorimi i Kritereve në Microsoft Excel

Microsoft Excel nuk është vetëm një redaktues i spreadsheet, por gjithashtu një aplikacion i fuqishëm për llogaritjet e ndryshme. Së fundmi, kjo mundësi u shfaq falë funksioneve të integruara. Me ndihmën e disa funksioneve (operatorëve), madje mund të specifikoni kushtet e llogaritjes, të cilat quhen kritere. Le të mësojmë më në detaje se si mund t'i përdorni kur punoni në Excel.

Kriteret e aplikimit

Kriteret janë kushtet në të cilat një program kryen veprime të caktuara. Ato përdoren në një numër funksionesh të ndërtuara. Emri i tyre më së shpeshti përmban shprehjen "IF". Këtij grupi të operatorëve, para së gjithash, është e nevojshme t'i atribuohet countif, SCHOTESLIMN, SUMIF, SUMIFS. Përveç operatorëve të integruar, kriteret në Excel përdoren gjithashtu për formatimin e kushtëzuar. Konsideroni përdorimin e tyre kur punoni me mjete të ndryshme të këtij procesori tryezë në mënyrë më të detajuar.

Countif

Detyra kryesore e operatorit countifqë i përkasin një grupi statistikor është numërimi i okupuar nga vlera të ndryshme të qelizave që plotësojnë një gjendje të caktuar të caktuar. Sintaksa e saj është si më poshtë:

= COUNTIF (varg; kriter)

Siç mund ta shihni, ky operator ka dy argumente. "Gama" paraqet adresën e grupit të elementeve në fletë në të cilën duhet të numërohen.

"Kriteri" - ky është një argument që përcakton gjendjen që saktësisht duhet të përmbajnë qelizat e zonës së specifikuar në mënyrë që të përfshihen në numërim. Si parametër, mund të përdoret një shprehje numerike, teksti ose lidhja me qelizën në të cilën përmbahet kriteri. Në këtë rast, për të treguar kriterin, mund të përdorni karakteret e mëposhtme: "<" ("Më pak"), ">" ("Më shumë"), "=" ("Barabartë"), "" (jo e barabartë). Për shembull, nëse specifikoni një shprehje "<50", atëherë vetëm elementët e specifikuar nga argumenti do të merren parasysh gjatë llogaritjes "Gama", në të cilat vlerat numerike janë më pak se 50. Përdorimi i këtyre shenjave për të treguar parametrat do të jetë i rëndësishëm për të gjitha opsionet e tjera, të cilat do të diskutohen në këtë mësim më poshtë.

Tani le të shohim një shembull konkret se si funksionon ky operat në praktikë.

Pra, ekziston një tabelë ku paraqiten të ardhurat nga pesë dyqane në javë. Duhet të zbulojmë numrin e ditëve për këtë periudhë në të cilën në Dyqanin 2 të ardhurat nga shitjet tejkaluan 15,000 rubla.

1. Zgjidhni elementin e fletës në të cilin operatori do të nxjerrë rezultatin e llogaritjes. Pas kësaj, klikoni në ikonën "Vendos funksionin".



2. Duke filluar Magjistarët e funksionit. Kalojmë në bllok "Statistikore". Aty gjejmë dhe nxjerrim në pah emrin "Countif". Pastaj klikoni në butonin. "OK".



3. Dritarja e argumentit të deklaratës së mësipërme është aktivizuar. Në fushë "Gama" është e nevojshme të tregohet zona e qelizave midis të cilave do të bëhet llogaritja. Në rastin tonë, duhet të theksojmë përmbajtjen e rreshtit "Dyqani 2", në të cilat vlerat e të ardhurave janë të vendosura ditën. Ne vendosëm kursorin në fushën e caktuar dhe, duke mbajtur butonin e majtë të miut, zgjedhim renditjen përkatëse në tabelë. Adresa e grupit të zgjedhur shfaqet në dritare.

   Në fushën tjetër "Kriteri" thjesht duhet të vendosni parametrin e menjëhershëm të zgjedhjes. Në rastin tonë, duhet të llogarisim vetëm ato elemente të tabelës në të cilën vlera tejkalon 15000. Prandaj, duke përdorur tastierën, drejtojmë shprehjen ">15000".

   Pasi të bëhen manipulimet e mësipërme, klikoni në butonin "OK".



4. Programi numëron dhe shfaq rezultatin në elementin e fletës që u zgjodh para aktivizimit Magjistarët e funksionit. Siç mund ta shihni, në këtë rast, rezultati është i barabartë me 5. Kjo do të thotë që në grupin e zgjedhur në pesë qeliza ka vlera më të mëdha se 15,000. Kjo do të thotë, ne mund të konkludojmë se në Dyqanin 2 në pesë ditë nga shtatë analizat, të ardhurat tejkaluan 15,000 rubla.

Mësimi: Wizard Feature Wizard

SCHOTESLIMN

Funksioni tjetër që funksionon me kriteret është SCHOTESLIMN. Ai gjithashtu i përket grupit statistikor të operatorëve. detyrë SCHOTESLIMN është duke numëruar qelizat në një grup të specifikuar që plotësojnë një grup specifik të kushteve. Shtë fakti që ju mund të specifikoni jo një, por disa parametra, dhe dalloni këtë operator nga ai i mëparshmi. Sintaksa është si më poshtë:

= COUNTIME (condition_range1; condition1; condition_range2; condition2; ...)

"Gama e gjendjes" është identik me argumentin e parë të deklaratës së mëparshme. Kjo do të thotë, është një lidhje me zonën në të cilën do të numërohen qelizat që plotësojnë kushtet e specifikuara. Ky operator ju lejon të specifikoni disa zona të tilla menjëherë.

"Gjendja" përfaqëson një kriter që përcakton se cilët elementë nga grupi i të dhënave përkatëse do të llogariten dhe cilat jo. Areado zonë e dhënë e të dhënave duhet të specifikohet veçmas, edhe nëse përputhet. Shtë e domosdoshme që të gjitha grupet e përdorura si zona me kushte të kenë të njëjtin numër rreshtash dhe kolonash.

Për të vendosur disa parametra të së njëjtës zonë të të dhënave, për shembull, për të numëruar numrin e qelizave në të cilat vlerat janë më të mëdha se një numër i caktuar, por më pak se një numër tjetër, duhet të merren si argument "Gama e gjendjes" specifikoni të njëjtën rresht disa herë. Por në të njëjtën kohë, si argumente të përshtatshme "Gjendja" duhet të tregohen kritere të ndryshme.

Duke përdorur një shembull të të njëjtës tabelë me të ardhura javore nga shitjet, le të shohim se si funksionon. Duhet të zbulojmë numrin e ditëve të javës kur të ardhurat në të gjitha dyqanet e përcaktuara me pakicë arritën standardin e vendosur për ta. Standardet e të ardhurave janë si më poshtë:

• Dyqani 1 - 14,000 rubla;
• Dyqani 2 - 15,000 rubla;
• Dyqani 3 - 24,000 rubla;
• Dyqani 4 - 11,000 rubla;
• Dyqani 5 - 32,000 rubla.

1. Për të përmbushur detyrën e mësipërme, zgjidhni elementin e fletës së punës me kursorin, ku do të shfaqet rezultati i përpunimit të të dhënave SCHOTESLIMN. Klikoni në ikonën "Vendos funksionin".



2. Shkuarja tek Wizard Featurezhvendoset përsëri në bllok "Statistikore". Lista duhet të gjejë emrin SCHOTESLIMN dhe zgjidhni atë. Pas kryerjes së veprimit të specifikuar, duhet të shtypni butonin "OK".



3. Pas ekzekutimit të algoritmit të mësipërm të veprimeve, hapet dritarja e argumentit SCHOTESLIMN.

   Në fushë "Gama e Gjendjes 1" shkruani adresën e rreshtit ku ndodhen të dhënat në të ardhurat e Store 1 për javën. Për ta bërë këtë, vendoseni kursorin në fushë dhe zgjidhni rreshtin përkatës në tabelë. Koordinatat shfaqen në dritare.

   Duke marrë parasysh që për Dyqanin 1 norma ditore e të ardhurave është 14,000 rubla, atëherë në këtë fushë "Kushti 1" shkruaj shprehjen ">14000".

   Në fusha "Gama e gjendjes 2 (3,4,5)" duhet të futen koordinatat e linjave me të ardhurat javore të Store 2, Store 3, Store 4 dhe Store 5, veprimi kryhet sipas të njëjtit algoritëm si për argumentin e parë të këtij grupi.

   Në fusha "Gjendja 2", "Kushti 3", "Uslovie4" dhe "Uslovie5" ne i vendosim vlerat në përputhje me rrethanat ">15000", ">24000", ">11000" dhe ">32000". Siç mund ta merrni me mend, këto vlera korrespondojnë me intervalin e të ardhurave që tejkalon normën për dyqanin përkatës.

   Pasi të keni futur të gjitha të dhënat e nevojshme (gjithsej 10 fusha), klikoni në butonin "OK".



4. Programi numëron dhe shfaq rezultatin në ekran. Siç mund ta shihni, është e barabartë me numrin 3. Kjo do të thotë që në tre ditë nga java e analizuar të ardhurat në të gjitha daljet tejkaluan normën e vendosur për to.

Tani le të ndryshojmë detyrën. Ne duhet të llogarisim numrin e ditëve në të cilat Shop 1 mori të ardhura më të mëdha se 14,000 rubla, por më pak se 17,000 rubla.

1. Ne e vendosim kursorin në elementin ku prodhimi do të prodhohet në fletën e rezultateve të numërimit. Klikoni në ikonën "Vendos funksionin" mbi zonën e punës të fletës.



2. Meqenëse kohët e fundit kemi aplikuar formulën SCHOTESLIMN, tani nuk do të duhet të shkosh në grup "Statistikore" Magjistarët e funksionit. Emri i këtij operatori mund të gjendet në këtë kategori "10 të përdorura kohët e fundit". Zgjidhni atë dhe klikoni në butonin. "OK".



3. Hapet dritarja e argumentimit të njohur të operatorit. SCHOTESLIMN. Vendoseni kursorin në fushë "Gama e Gjendjes 1" dhe, duke mbajtur butonin e majtë të miut, zgjidhni të gjitha qelizat që përmbajnë të ardhura deri në ditët e Dyqanit 1. Ato janë të vendosura në linjë, e cila quhet "Dyqani 1". Pas kësaj, koordinatat e zonës së specifikuar do të pasqyrohen në dritare.

   Tjetra, vendoseni kursorin në fushë "Kushti 1". Këtu duhet të tregojmë kufirin e poshtëm të vlerave në qelizat që do të marrin pjesë në llogaritjen. Specifikoni një shprehje ">14000".

   Në fushë "Gama e Gjendjes 2" shkruani të njëjtën adresë në të njëjtën mënyrë që është futur në fushë "Gama e Gjendjes 1", domethënë, përsëri hyjmë në koordinatat e qelizave me vlerat e të ardhurave për daljen e parë.

   Në fushë "Gjendja 2" tregoni kufirin e sipërm të zgjedhjes: "<17000".

   Pasi të jenë bërë të gjitha veprimet e specifikuara, klikoni në butonin "OK".



4. Programi jep rezultatin e llogaritjes. Siç mund ta shihni, vlera përfundimtare është 5. Kjo do të thotë që brenda 5 ditëve nga shtatë studimet, të ardhurat në dyqanin e parë ishin në interval nga 14,000 deri në 17,000 rubla.

SUMIF

Një tjetër operator që përdor kriteret është SUMIF. Për dallim nga funksionet e mëparshme, ai i përket bllokut matematik të operatorëve. Detyra e saj është të përmbledhë të dhënat në qeliza që korrespondojnë me një gjendje specifike. Sintaksa është si më poshtë:

= SUMMES (varg; kriter; [sum_range])

argument "Gama" tregon zonën e qelizave që do të kontrollohen për përputhjen me gjendjen. Në fakt, është vendosur nga i njëjti parim si argumenti i funksionit me të njëjtin emër countif.

"Kriteri" - është një argument i kërkuar që specifikon zgjedhjen e qelizave nga zona e specifikuar e të dhënave për t'u shtuar. Parimet e specifikimit janë të njëjta si për argumentet e ngjashme të operatorëve të mëparshëm, të cilët ne shqyrtuam më lart.

"Gama e Përmbledhjes" Ky është një argument opsional. Ai tregon zonën specifike të vargut në të cilin do të kryhet përmbledhja. Nëse e braktisni atë dhe nuk e specifikoni atë, atëherë si parazgjedhje konsiderohet se është e barabartë me vlerën e argumentit të kërkuar "Gama".

Tani, si gjithmonë, merrni parasysh aplikimin e këtij operatori në praktikë. Bazuar në të njëjtën tabelë, ne jemi përballur me detyrën e llogaritjes së sasisë së të ardhurave në Dyqanin 1 për periudhën që fillon më 11 Mars 2017.

1. Zgjidhni qelizën në të cilën do të dalë rezultati. Klikoni në ikonën. "Vendos funksionin".



2. Shkuarja tek Wizard Feature në bllok "Matematike" gjeni dhe nxjerrë në pah emrin "SUMIF". Klikoni në butonin "OK".



3. Fillon dritarja e argumentit të funksionit SUMIF. Ka tre fusha që korrespondojnë me argumentet e operatorit të specifikuar.

   Në fushë "Gama" shkruani zonën e tabelës në të cilën do të vendosen vlerat që do të kontrollohen për përputhjen me kushtet. Në rastin tonë, do të jetë një varg datash. Vendoseni kursorin në këtë fushë dhe zgjidhni të gjitha qelizat që përmbajnë datat.

   Meqenëse duhet të shtojmë vetëm të ardhurat duke filluar nga 11 Mars, në terren "Kriteri" ngas vlerën ">10.03.2017".

   Në fushë "Gama e Përmbledhjes" duhet të specifikoni zonën, vlerat e së cilës plotësojnë kriteret e specifikuara do të përmblidhen. Në rastin tonë, këto janë vlerat e të ardhurave të linjës "Magazin1". Zgjidhni grupin përkatës të elementeve të fletës.

   Pasi të jenë futur të gjitha të dhënat e specifikuara, klikoni në butonin "OK".



4. Pas kësaj, rezultati i përpunimit të të dhënave nga funksioni do të shfaqet në elementin e specifikuar më parë të fletës së punës. SUMIF. Në rastin tonë, është e barabartë me 47921.53. Kjo do të thotë që duke filluar nga 11 Mars 2017, dhe deri në fund të periudhës së analizuar, të ardhurat totale për Shop 1 arritën në 47.921.53 rubla.

SUMIFS

Ne përfundojmë studimin e operatorëve që përdorin kritere, duke u përqëndruar në funksione SUMIFS. Qëllimi i këtij funksioni matematikor është të përmbledhë vlerat e zonave të treguara të tabelës, të zgjedhura sipas disa parametrave. Sintaksa e operatorit të specifikuar është si më poshtë:

= SUMMER (sum_range; condition_range1; condition1; condition_range2; condition2; ...)

"Gama e Përmbledhjes" - ky është argumenti, i cili është adresa e grupit në të cilin do të shtohen qelizat që plotësojnë një kriter të caktuar.

"Gama e gjendjes" - një argument, i cili është një grup i të dhënave, i kontrolluar për pajtueshmërinë me gjendjen;

"Gjendja" - një argument që përfaqëson një kriter përzgjedhjeje për shtesë.

Ky funksion nënkupton operacione me disa grupe të operatorëve të ngjashëm menjëherë.

Le të shohim se si ky operator është i zbatueshëm për zgjidhjen e problemeve në kontekstin e tabelës sonë të të ardhurave nga shitjet në dyqanet me pakicë. Do të duhet të llogarisim të ardhurat që solli Shop 1 për periudhën nga 09 Mars deri në 13 Mars 2017. Në këtë rast, kur përmblidhen të ardhurat, duhet të merren parasysh vetëm ato ditë, në të cilat të ardhurat tejkaluan 14,000 rubla.

1. Përsëri, zgjidhni qelizën për të shfaqur totalin dhe klikoni në ikonën "Vendos funksionin".



2. Magjistar i funksionitPara së gjithash, ne kalojmë në bllok "Matematike", dhe atje zgjedhim një artikull të quajtur "SUMIFS". Klikoni në butonin. "OK".



3. Hapet dritarja e argumenteve të operatorit, emri i së cilës u tregua më lart.

   Vendoseni kursorin në fushë "Gama e Përmbledhjes". Për dallim nga argumentet e mëposhtëm, kjo një lloj tregon edhe një varg vlerash ku do të përmblidhen të dhënat që përshtaten me kriteret e specifikuara. Pastaj zgjidhni zonën e rreshtit "Magazin1", në të cilat janë vendosur vlerat e të ardhurave për daljen përkatëse.

   Pasi adresa të shfaqet në dritare, shkoni në fushë "Gama e Gjendjes 1". Këtu do të duhet të shfaqim koordinatat e vargut me datat. Mbërthen butonin e majtë të miut dhe zgjidhni të gjitha datat në tabelë.

   Vendoseni kursorin në fushë "Kushti 1". Kushti i parë është që ne do të përmbledhim të dhënat jo më parë se 09 Mars. Prandaj, shkruani vlerën ">08.03.2017".

   Ne kalojmë në argument "Gama e Gjendjes 2". Këtu ju duhet të vendosni të njëjtat koordinata që janë regjistruar në terren "Gama e Gjendjes 1". Ne e bëjmë këtë në të njëjtën mënyrë, domethënë duke theksuar rreshtin me datat.

   Vendoseni kursorin në fushë "Gjendja 2". Kushti i dytë është që ditët për të cilat do të shtohen të ardhurat duhet të jenë jo më vonë se 13 Mars. Prandaj, ne shkruajmë shprehjen e mëposhtme: "<14.03.2017".

   Shkoni në fushë "Gama e Gjendjes 2". Në këtë rast, ne kemi nevojë për të zgjedhur të njëjtën grup, adresa e të cilit ishte futur si një grup përmbledhjes.

   Pasi të shfaqet në dritare adresa e grupit të specifikuar, shkoni në fushë "Kushti 3". Duke marrë parasysh që vetëm vlerat, vlera e të cilave tejkalon 14,000 rubla do të marrin pjesë në përmbledhje, ne bëjmë një hyrje të natyrës së mëposhtme: ">14000".

   Pasi të keni kryer veprimin e fundit, klikoni në butonin "OK".



4. Programi tregon rezultatin në një fletë. Shtë e barabartë me 62491,38. Kjo do të thotë që për periudhën nga 9 Mars deri në 13 Mars 2017, shuma e të ardhurave kur i shtoni ato për ditë në të cilat tejkalon 14,000 rubla arriti në 62,491,38 rubla.

Formatimi i kushtëzuar

Mjeti i fundit që përshkruam, kur punojmë me kritere, është formatimi i kushtëzuar. Kryen llojin e caktuar të qelizave formatuese që plotësojnë kushtet e specifikuara. Shikoni një shembull të punës me formatimin e kushtëzuar.

Ne zgjedhim ato qeliza në tabelë në blu, ku vlerat ditore tejkalojnë 14,000 rubla.

1. Ne zgjedhim të gjithë grupin e elementeve në tabelë, i cili tregon të ardhurat e dyqaneve për ditë.



2. Kaloni në skedë "Home". Klikoni në ikonën Formatimi i kushtëzuartë vendosura në bllok "Styles" në shirit. Hapet një listë veprimesh. Klikoni mbi të në pozicion "Krijoni një rregull ...".



3. Aktivizohet dritarja për gjenerimin e rregullës së formatimit. Në zonën e zgjedhjes së llojit të rregullës, zgjidhni emrin "Formato vetëm qelizat që përmbajnë". Në fushën e parë të bllokut të gjendjes, nga lista e opsioneve të mundshme, zgjidhni "Vlera e qelizave". Në fushën tjetër, zgjidhni pozicionin "Më shumë". Në të fundit - specifikoni vetë vlerën, më shumë sesa që dëshironi të formatoni elementët e tabelës. Ne e kemi atë 14000. Për të zgjedhur llojin e formatimit, klikoni në butonin "Formati ...".



4. Dritarja e formatimit aktivizohet. Kaloni në skedë "Mbushja". Nga opsionet e propozuara për mbushjen e ngjyrave, zgjidhni blu duke klikuar majtas mbi të. Pasi të shfaqet ngjyra e zgjedhur në zonë "Mostra"klikoni në butonin "OK".



5. Dritarja e gjenerimit të rregullave të formatimit kthehet automatikisht. Në të edhe në fushë "Mostra" afishohet ngjyra blu. Këtu duhet të bëjmë një veprim të vetëm: klikoni në butonin "OK".



6. Pas veprimit të fundit, të gjitha qelizat e grupit të zgjedhur, i cili përmban një numër më të madh se 14000, do të mbushen me blu.

Më shumë informacion në lidhje me aftësitë e formatimit të kushteve është diskutuar në një artikull të veçantë.

Mësimi: Formatimi i kushtëzuar në Excel

Siç mund ta shihni, duke përdorur mjete që përdorin kritere në punën e tyre, Excel mund të zgjidhë probleme mjaft të ndryshme. Kjo mund të jetë, si llogaritja e shumave dhe vlerave, dhe formatimi, si dhe zbatimi i shumë detyrave të tjera. Mjetet kryesore që punojnë në këtë program me kritere, d.m.th., me kushte të caktuara nën të cilat aktivizohet ky veprim, është një seri funksionesh të ndërtuara, si dhe formatimi i kushtëzuar.