Funksioni IF në Excel. Shembuj (me kushte të shumta)

Një nga avantazhet kryesore të spreadsheets Excel është aftësia për të programuar funksionalitetin e një dokumenti të caktuar. Siç e dinë shumica e njerëzve nga mësimet e shkencave kompjuterike në shkollë, një nga komponentët kryesorë që ju lejon ta zbatoni këtë në praktikë janë operatorët logjikë. Një prej tyre është operatori IF, i cili parashikon ekzekutimin e veprimeve të caktuara kur plotësohen disa kushte. 

Për shembull, nëse vlera përputhet me një të caktuar, atëherë një etiketë shfaqet në qelizë. Nëse jo, është ndryshe. Le ta shohim këtë mjet efektiv në më shumë detaje në praktikë.

Funksioni IF në Excel (informacion i përgjithshëm)

Çdo program, edhe nëse është i vogël, domosdoshmërisht përmban një sekuencë veprimesh, e cila quhet algoritëm. Mund të duket kështu:

1. Kontrolloni të gjithë kolonën A për numra çift.
2. Nëse gjendet një numër çift, shtoni këto dhe ato vlera.
3. Nëse një numër çift nuk gjendet, atëherë shfaqni mbishkrimin "nuk u gjet".
4. Kontrolloni nëse numri që rezulton është çift. 
5. Nëse po, atëherë shtojeni te të gjithë numrat çift të zgjedhur në paragrafin 1.

Dhe edhe nëse kjo është vetëm një situatë hipotetike, e cila nuk ka gjasa të jetë e nevojshme në jetën reale, ekzekutimi i çdo detyre nënkupton domosdoshmërisht praninë e një algoritmi të ngjashëm. Përpara përdorimit të funksionit NËSE, ju duhet të keni një ide të qartë në kokën tuaj se çfarë rezultati dëshironi të arrini. 

Sintaksa e funksionit IF me një kusht

Çdo funksion në Excel kryhet duke përdorur një formulë. Modeli me të cilin të dhënat duhet t'i kalohen një funksioni quhet sintaksë. Në rastin e operatorit IF, formula do të jetë në këtë format.

=IF (shprehja_logjike, vlera_nëse_e vërtetë, vlera_nëse_false)

Le të shohim sintaksën në më shumë detaje:

1. Shprehje boolean. Ky është vetë kushti, përputhshmërinë ose mospërputhjen e të cilit kontrollon Excel. Informacioni numerik dhe tekstual mund të kontrollohet.
2. Vlera_nëse_e vërtetë. Rezultati që do të shfaqet në qelizë nëse të dhënat që kontrollohen plotësojnë kriteret e specifikuara.
3. vlera_nëse_false. Rezultati që shfaqet në qelizë nëse të dhënat që kontrollohen nuk përputhen me kushtin.

Këtu është një shembull për qartësi.

Këtu funksioni krahason qelizën A1 me numrin 20. Ky është paragrafi i parë i sintaksës. Nëse përmbajtja është më e madhe se kjo vlerë, vlera "më e madhe se 20" shfaqet në qelizën ku është shkruar formula. Nëse situata nuk korrespondon me këtë kusht - "më pak se ose e barabartë me 20".

Nëse dëshironi të shfaqni një vlerë teksti në një qelizë, duhet ta vendosni atë në thonjëza.

Këtu është një situatë tjetër. Për t'u kualifikuar për të marrë pjesë në një seancë provimi, studentët duhet të kalojnë një seancë testimi. Studentët arritën të fitojnë kredite në të gjitha lëndët dhe tashmë ka mbetur e fundit, e cila ka rezultuar vendimtare. Detyra jonë është të përcaktojmë se cilët nga studentët pranohen në provime dhe cilët jo.

Meqenëse ne duam të kontrollojmë për tekst dhe jo një numër, argumenti i parë është B2 = "kundër.".

Sintaksa e funksionit IF me kushte të shumta

Shpesh, një kriter nuk mjafton për të kontrolluar vlerën. Nëse keni nevojë të merrni në konsideratë më shumë se një opsion, mund të vendosni funksione fole IF njëri në tjetrin. Do të ketë disa funksione të mbivendosura.

Për ta bërë më të qartë, këtu është sintaksa.

=IF(shprehja_logjike, vlera_nëse_e vërtetë, IF (shprehja_logjike, vlera_nëse_e vërtetë, vlera_nëse_e gabuar))

Në këtë rast, funksioni do të kontrollojë dy kritere njëherësh. Nëse kushti i parë është i vërtetë, vlera e marrë si rezultat i operacionit në argumentin e parë kthehet. Nëse jo, kriteri i dytë kontrollohet për pajtueshmëri.

Ja një shembull.

Dhe me ndihmën e një formule të tillë (treguar në pamjen e mëposhtme të ekranit), mund të analizoni performancën e secilit student.

Siç mund ta shihni, këtu është shtuar edhe një kusht, por parimi nuk ka ndryshuar. Kështu që ju mund të kontrolloni disa kritere në të njëjtën kohë.

Si të zgjeroni funksionalitetin IF duke përdorur operatorët AND dhe OR

Herë pas here ka një situatë për të kontrolluar menjëherë përputhshmërinë me disa kritere dhe të mos përdorni operatorë logjikë të mbivendosur, si në shembullin e mëparshëm. Për ta bërë këtë, përdorni një nga funksionet И ose funksion OR varësisht nëse duhet të plotësoni disa kritere njëherësh ose të paktën njërin prej tyre. Le t'i hedhim një vështrim më të afërt këtyre kritereve.

IF funksion me AND kusht

Ndonjëherë ju duhet të kontrolloni një shprehje për kushte të shumta në të njëjtën kohë. Për këtë përdoret funksioni AND i shkruar në argumentin e parë të funksionit IF. Punon kështu: nëse a është e barabartë me një dhe a është e barabartë me 2, vlera do të jetë c.

Funksioni IF me kusht "OR".

Funksioni OR funksionon në mënyrë të ngjashme, por në këtë rast, vetëm një nga kushtet është e vërtetë. Për aq sa është e mundur, në këtë mënyrë mund të kontrollohen deri në 30 kushte. 

Këtu janë disa mënyra për të përdorur funksionet И и OR si argument funksioni IF.

Krahasimi i të dhënave në dy tabela

Herë pas here është e mundur të krahasohen dy tabela të ngjashme. Për shembull, një person punon si kontabilist dhe duhet të krahasojë dy raporte. Ka edhe detyra të tjera të ngjashme, si krahasimi i kostos së mallrave të grupeve të ndryshme, më pas, vlerësimet e studentëve për periudha të ndryshme, e kështu me radhë.

Për të krahasuar dy tabela, përdorni funksionin countif. Le ta shohim më në detaje.

Le të themi se kemi dy tabela që përmbajnë specifikat e dy përpunuesve të ushqimit. Dhe ne duhet t'i krahasojmë ato dhe të nxjerrim në pah ndryshimet me ngjyrën. Kjo mund të bëhet duke përdorur formatimin e kushtëzuar dhe funksionin countif. 

Tabela jonë duket kështu.

Ne zgjedhim gamën që korrespondon me karakteristikat teknike të përpunuesit të parë të ushqimit.

Pas kësaj, klikoni në menutë e mëposhtme: Formatimi i kushtëzuar - krijoni një rregull - përdorni një formulë për të përcaktuar qelizat e formatuara.

Në formën e një formule për formatim, ne shkruajmë funksionin = COUNTIF (varg për krahasim; qeliza e parë e tabelës së parë)=0. Tabela me veçoritë e përpunuesit të dytë të ushqimit përdoret si një gamë krahasimi.

Duhet të siguroheni që adresat të jenë absolute (me një shenjë dollari përpara emrave të rreshtave dhe kolonave). Shtoni =0 pas formulës në mënyrë që Excel të kërkojë vlera të sakta.

Pas kësaj, duhet të vendosni formatimin e qelizave. Për ta bërë këtë, pranë mostrës, duhet të klikoni në butonin "Format". Në rastin tonë, ne përdorim mbushjen, sepse është më e përshtatshme për këtë qëllim. Por ju mund të zgjidhni çdo format që dëshironi.

Ne kemi caktuar një emër kolone si varg. Kjo është shumë më e përshtatshme sesa futja manuale e gamës.

Funksioni SUMIF në Excel

Tani le të kalojmë te funksionet IF, e cila do të ndihmojë në zëvendësimin e dy pikave të algoritmit menjëherë. E para është SUMMESLEY, i cili mbledh dy numra që plotësojnë një kusht të caktuar. Për shembull, ne përballemi me detyrën për të përcaktuar se sa para duhet të paguhen në muaj për të gjithë shitësit. Për këtë është e nevojshme.

1. Shtoni një rresht me të ardhurat totale të të gjithë shitësve dhe klikoni në qelizën që do të përmbajë rezultatin pasi të futni formulën. 
2. Gjejmë butonin fx, i cili ndodhet pranë rreshtit për formulat. Më pas, do të shfaqet një dritare ku mund të gjeni funksionin e nevojshëm përmes kërkimit. Pasi të zgjidhni operatorin, duhet të klikoni butonin "OK". Por futja manuale është gjithmonë e mundur.
   

   

3. Më pas, do të shfaqet një dritare për futjen e argumenteve të funksionit. Të gjitha vlerat mund të specifikohen në fushat përkatëse, dhe diapazoni mund të futet përmes butonit pranë tyre.
   

   

4. Argumenti i parë është një varg. Këtu futni qelizat që dëshironi të kontrolloni për pajtueshmërinë me kriteret. Nëse flasim për ne, këto janë pozicionet e punonjësve. Futni diapazonin D4:D18. Ose thjesht zgjidhni qelizat me interes.
5. Në fushën "Kriteret", vendosni pozicionin. Në rastin e nesh - "shitësi". Si diapazoni i përmbledhjes, ne tregojmë ato qeliza ku renditen pagat e punonjësve (kjo bëhet si me dorë, ashtu edhe zgjidhni ato me miun). Klikoni "OK" dhe marrim pagat e përfunduara të llogaritura të të gjithë punonjësve që janë shitës.

Pajtohem që është shumë i përshtatshëm. A nuk është ajo?

Funksioni SUMIFS në Excel

Ky funksion ju lejon të përcaktoni shumën e vlerave që plotësojnë kushte të shumta. Për shembull, na u dha detyra për të përcaktuar pagën totale të të gjithë drejtuesve që punojnë në degën jugore të kompanisë.

Shtoni një rresht ku do të jetë rezultati përfundimtar dhe futni formulën në qelizën e dëshiruar. Për ta bërë këtë, klikoni në ikonën e funksionit. Do të shfaqet një dritare në të cilën duhet të gjeni funksionin SUMMESLIMN. Më pas, zgjidhni atë nga lista dhe hapet dritarja e njohur me argumente. Por numri i këtyre argumenteve tani është i ndryshëm. Kjo formulë bën të mundur përdorimin e një numri të pafund kriteresh, por numri minimal i argumenteve është pesë. 

Vetëm pesë mund të specifikohen përmes dialogut të hyrjes së argumentit. Nëse keni nevojë për më shumë kritere, atëherë ato do të duhet të futen manualisht sipas të njëjtës logjikë si dy të parat.

Le të shohim më në detaje argumentet kryesore:

1. Gama e përmbledhjes. Qelizat për t'u përmbledhur.
2. Gama e gjendjes 1 – diapazoni që do të kontrollohet për pajtueshmërinë me një kriter të caktuar. 
3. Kushti 1 është vetë gjendja.
4. Gama e kritereve 2 është diapazoni i dytë që do të kontrollohet kundrejt kriterit.
5. Kushti 2 është kushti i dytë.

Më tej logjika është e ngjashme. Si rezultat, ne përcaktuam pagat e të gjithë drejtuesve të Degës Jugore.

Funksioni COUNTIF në Excel

Nëse keni nevojë të përcaktoni se sa qeliza bien nën një kriter të caktuar, përdorni funksionin COUNTIF. Le të themi se duhet të kuptojmë se sa shitës punojnë në këtë organizatë:

1. Së pari, shtoni një rresht që përmban numrin e shitësve. Pas kësaj, duhet të klikoni në qelizën ku do të shfaqet rezultati.
2. Pas kësaj, duhet të klikoni në butonin "Fut Funksionin", i cili mund të gjendet në skedën "Formulat". Do të shfaqet një dritare me një listë kategorish. Duhet të zgjedhim artikullin "Lista e plotë alfabetike". Në listë, ne jemi të interesuar për formulën COUNTIF. Pasi ta zgjedhim, duhet të klikojmë butonin "OK".
   

   

3. Pas kësaj kemi numrin e shitësve të punësuar në këtë organizatë. Është marrë duke numëruar numrin e qelizave në të cilat është shkruar fjala "shitës". Gjithçka është e thjeshtë. 

Funksioni COUNTSLIM në Excel

Ngjashëm me formulën SUMMESLIMN, kjo formulë numëron numrin e qelizave që përputhen me kushte të shumta. Sintaksa është e ngjashme, por paksa e ndryshme nga formula SUMMESLIMN:

1. Gama e kushteve 1. Ky është diapazoni që do të testohet sipas kriterit të parë.
2. Kushti 1. Direkt kriteri i parë.
3. Gama e kushteve 2. Ky është diapazoni që do të testohet kundrejt kriterit të dytë. 
4. Kushti 2.
5. Kushtet e gamës 3.

Dhe kështu me radhë.

Pra funksioni IF në Excel - jo i vetmi, ka disa lloje të tjera të tij që kryejnë automatikisht veprimet më të zakonshme, gjë që thjeshton shumë jetën e një personi. 

Kryesisht për shkak të funksionit IF Tabelat e Excel-it konsiderohen të programueshme. Është më shumë se një kalkulator i thjeshtë. Nëse mendoni për këtë, atëherë funksioni IF është një gur themeli në çdo lloj programimi.

Pra, nëse mësoni se si të punoni me sasi të mëdha të dhënash në Excel, do të jetë shumë më e lehtë të mësoni programimin. Falë operatorëve logjikë, këto zona kanë vërtet shumë të përbashkëta, megjithëse Excel përdoret më shpesh nga kontabilistët. Por mekanizmi për të punuar me të dhëna është kryesisht i njëjtë. 

Funksiononi në duart e duhura IF dhe variacionet e tij ju lejojnë të ktheni një fletë Excel në një program të plotë që mund të veprojë në algoritme komplekse. Kuptimi se si funksionon funksioni IF është hapi i parë drejt mësimit të makrove - hapi tjetër në punë më fleksibël me fletëllogaritëse. Por ky është tashmë një nivel më profesional.