Operatori logjik IF në Excel përdoret për të regjistruar kushte të caktuara. Numrat dhe/ose teksti, funksionet, formulat, etj. përputhen. Kur vlerat plotësojnë parametrat e specifikuar, atëherë shfaqet një rekord. Mos u përgjigj - një tjetër.
Funksionet logjike janë një mjet shumë i thjeshtë dhe efektiv që përdoret shpesh në praktikë. Le t'i hedhim një vështrim më të afërt shembujve.
Sintaksa për funksionin IF me një kusht
Sintaksa e një operatori në Excel është struktura e një funksioni, të dhënat e nevojshme për funksionimin e tij.
IF (shprehja_boolean; vlera_nëse_e vërtetë; vlera_nëse_false)
Le të analizojmë sintaksën e funksionit:
Shprehja_Boolean- ÇFARË kontrollon operatori (tekst ose të dhëna numerike të qelizës).
Vlera_nëse_e vërtetë- ÇFARË do të shfaqet në qelizë kur teksti ose numri plotëson kushtin e specifikuar (e vërtetë).
Vlera if_false- ÇFARË do të shfaqet në grafik kur teksti ose numri NUK plotëson kushtin e specifikuar (false).
Shembull:
Operatori kontrollon qelizën A1 dhe e krahason atë me 20. Kjo është shprehja_boolean. Kur përmbajtja e kolonës është më shumë se 20, shfaqet mbishkrimi i vërtetë "më i madh se 20". Jo - "më pak se ose e barabartë me 20".
Kujdes! Fjalët në formulë duhet të mbyllen në thonjëza. Që Excel të kuptojë se duhet të nxjerrë vlerat e tekstit.
Një shembull më shumë. Për t'u pranuar në provim, studentët e grupit duhet ta kalojnë me sukses testin. Rezultatet do t'i vendosim në një tabelë me kolona: lista e studentëve, kreditet, provimi.
Ju lutemi vini re: deklarata IF nuk duhet të kontrollojë një lloj të dhënash dixhitale, por një tekst. Prandaj, ne kemi përshkruar B2 = "kredite" në formulë. E vendosim në thonjëza në mënyrë që programi ta njohë saktë tekstin.
Funksioni IF në Excel me kushte të shumta
Shpesh, në praktikë, një kusht për një funksion logjik nuk është i mjaftueshëm. Kur është e nevojshme të merren parasysh disa opsione për marrjen e vendimeve, ne vendosim operatorët IF në njëri-tjetrin. Kështu, ne marrim disa funksione IF në Excel.
Sintaksa do të duket si kjo:
IF (shprehje_boolean; vlera_nëse_e vërtetë; IF (shprehje_boolean; vlera_nëse_vërtetë; vlera_nëse_false))
Këtu operatori kontrollon dy parametra. Nëse kushti i parë është i vërtetë, atëherë formula kthen argumentin e parë, i cili është i vërtetë. False - operatori kontrollon kushtin e dytë.
Shembuj të kushteve të shumta për funksionin IF në Excel:
Tabela e analizës së performancës. Studenti mori 5 pikë - "shkëlqyeshëm". 4 - "mirë". 3 - "të kënaqshme". Operatori IF teston 2 kushte: barazinë e vlerës në qelizat 5 dhe 4.
Zgjerimi i funksionalitetit duke përdorur operatorët "AND" dhe "OR"
Kur duhet të kontrolloni disa kushte të vërteta, përdoret funksioni AND.Thelbi është si vijon: NËSE a = 1 DHE a = 2 ATHERE vlera në ELSE është vlera e c.
Funksioni OR teston kushtin 1 ose kushtin 2. Sapo të paktën një kusht të jetë i vërtetë, rezultati do të jetë i vërtetë. Përfundimi është: NËSE a = 1 OSE a = 2 ATHERE vlera në vlerën TJETER c.
Funksionet AND dhe OR mund të testojnë deri në 30 kushte.
Një shembull i përdorimit të operatorit AND:
Një shembull i përdorimit të funksionit OR:
Si të krahasoni të dhënat në dy tabela
Përdoruesit shpesh duhet të krahasojnë dy tabela në Excel për ndeshje. Shembuj nga "jeta reale": krahasoni çmimet e mallrave në dërgesa të ndryshme, krahasoni bilancet (raportet e kontabilitetit) për disa muaj, përparimin e nxënësve (nxënësve) në klasa të ndryshme, në tremujorë të ndryshëm, etj.
Për të krahasuar 2 tabela në Excel, mund të përdorni operatorin COUNTIF. Le të shohim rendin në të cilin zbatohet funksioni.
Për shembull, le të marrim dy tabela me karakteristikat teknike të përpunuesve të ndryshëm të ushqimit. Kemi konceptuar të evidentojmë dallimet me ngjyrën. Formatimi i kushtëzuar e zgjidh këtë problem në Excel.
Të dhënat fillestare (tabelat me të cilat do të punojmë):
Zgjidhni tabelën e parë. Formatimi i kushtëzuar - Krijo rregull - Përdor një formulë për të përcaktuar qelizat për formatim:
Në shiritin e formulës shkruajmë: = COUNTIF (vargu i krahasuar; qeliza e parë e tabelës së parë) = 0. Gama që krahasohet është tabela e dytë.
Për të futur një gamë në formulë, thjesht zgjidhni qelizën e parë dhe të fundit. "= 0" do të thotë një komandë për të gjetur vlera të sakta (jo të përafërta).
Ne zgjedhim një format dhe vendosim se si do të ndryshojnë qelizat kur të respektohet formula. Më mirë ta mbushni me ngjyra.
Zgjidhni tabelën e dytë. Formatimi i kushtëzuar - Krijo rregull - Përdor formulën. Ne aplikojmë të njëjtin operator (COUNTIF).
Këtu, në vend të qelizës së parë dhe të fundit në rangun, ne kemi futur emrin e kolonës që i kemi caktuar paraprakisht. Ju mund ta plotësoni formulën në cilëndo nga mënyrat. Por emri është më i lehtë.
Funksioni në ExcelËshtë një formulë e paracaktuar që kryen llogaritjet duke përdorur vlerat e specifikuara në një rend të caktuar. Excel përdor shumë funksione të ndryshme për të gjitha rastet. Funksionet mund të përdoren për të shpejtuar detyrat, për të thjeshtuar formulat dhe për të zbatuar llogaritjet që nuk do të ishin të mundura pa i përdorur ato. Në këtë mësim, do të mësoni se çfarë është një funksion, cilat janë sintaksa dhe argumentet e tij.
Excel përmban një numër funksionesh të thjeshta që mund të jenë të dobishme për gjetjen e shpejtë të shumave, mesatareve, vlerave maksimale dhe minimale, si dhe për llogaritjen e të dhënave. Për të përdorur funksionet në mënyrë korrekte, duhet të kuptoni sintaksën e tyre, d.m.th. shkruaj rregull.
Sintaksa e funksionit Excel
Për të punuar si duhet, funksioni duhet të shkruhet në një sekuencë specifike, e cila quhet sintaksë. Sintaksa bazë për një funksion është një shenjë e barabartë (=), një emër funksioni (siç është SUM) dhe një ose më shumë argumente. Argumentet përmbajnë informacion për t'u llogaritur. Funksioni i shembullit të mëposhtëm përmbledh vlerat në rangun A1: A20.
Ka funksione në Excel që nuk përmbajnë asnjë argument. Për shembull, funksioni SOT () kthen datën aktuale nga koha e sistemit të kompjuterit tuaj.
Puna me argumente
Argumentet mund t'u referohen qelizave individuale dhe gamës së qelizave dhe duhet të vendosen në kllapa. Funksionet e Excel mund të përdorin një argument ose më shumë, në varësi të sintaksës.
Për shembull, funksioni = MESATAR (B1: B9) do të llogarisë mesataren në intervalin e qelizave B1: B9. Ky funksion përmban vetëm një argument.
Argumente të shumta duhet të ndahen me pikëpresje. Për shembull, funksioni = SUM (A1: A3; C1: C2; E2) përmbledh vlerat e të gjitha qelizave në tre argumente.
Funksionet paraqesin varësinë e një elementi (rezultati) nga elementët e tjerë (argumentet, .. ato brenda :-)). Është disi e kuptueshme. Për të përdorur ndonjë funksion në, duhet ta futni atë si formulë (përshkruhen nuancat) ose si pjesë e një formule në një qelizë të një flete pune.
Sekuenca në të cilën duhet të shfaqen simbolet dhe argumentet e përdorura në formulë quhet sintaksa e funksionit... Të gjitha funksionet përdorin të njëjtat rregulla sintaksore. Nëse shkelni këto rregulla, Excel do të shfaqë një mesazh se ka një gabim në formulë dhe nuk do të jetë miq me ju. Por më besoni, në funksionet Excel gjithçka është krejt e njëjtë, dhe pasi ta kuptoni, në një ose dy funksione, në raste të tjera gjithçka do të jetë mjaft e thjeshtë.
Rregullat sintaksore për shkrimin e funksioneve
Nëse një funksion shfaqet në fillim të një formule, duhet të paraprihet nga një shenjë e barabartë, siç është rasti në fillim të çdo formule. Unë kam folur tashmë për këtë në artikujt e mëparshëm, por nuk është mëkat ta përsërisësh.
Pas kësaj, futet emri i funksionit, i ndjekur menjëherë nga lista e argumenteve në kllapa. Argumentet ndahen nga njëri-tjetri me një pikëpresje ";". Kllapat lejojnë Excel të tregojë se ku fillon dhe mbaron lista e argumenteve.
Vini re se hyrja e funksionit duhet të përmbajë kllapa hapëse dhe mbyllëse dhe nuk mund të futni hapësira midis emrit të funksionit dhe kllapave. Përndryshe, Excel do të shfaqë një mesazh gabimi.
Ju mund të përdorni numra, tekst, booleans, vargje, vlera gabimi ose referenca si argumente. Në këtë rast, parametrat e specifikuar nga përdoruesi duhet të kenë vlera të vlefshme për argumentin e dhënë.
Për shembull, formula më poshtë përmbledh vlerat në qelizat B2, B3, B4, B5 dhe E7, me disa nga qelizat nga B2 në B5 të përfaqësuara si një interval i vazhdueshëm.
Merrni parasysh funksionimin e funksionit ROUND (arg1; arg2), i cili kthen një numër të rrumbullakosur në numrin e caktuar të numrave dhjetorë dhe ka dy argumente:
arg1 është adresa e qelizës me numrin (ose vetë numrin) që do të rrumbullakoset;
arg2 - numri i shifrave pas pikës dhjetore në numrin pas rrumbullakimit.
Për të rrumbullakosur numrin 2,71828 në qelizën A1 në një, dy ose tre shifra dhjetore dhe për të shkruar rezultatet e llogaritjeve në qelizat B1, C1 dhe D1, përkatësisht, veproni si më poshtë.
- Futni numrin 2.71828 në qelizën A1.
- Futni formulat e mëposhtme në qelizat B1, C1 dhe D1:
RRUGULL (A1; 1)
RRUGULL (A1, 2)
RRUGULL (A1, 3)
Argumentet mund të jenë konstante dhe funksione. Funksionet që janë argumente për një funksion tjetër thirren folezuar.
Për shembull, le të përmbledhim vlerat e qelizave A1 dhe A2, duke i rrumbullakosur më parë këto vlera në dy shifra dhjetore:
SHUMË (RRUGULL (A1,2); RRUGULL (A2,2))
Këtu funksioni ROUND është i futur dy herë, por kjo është në rregull, në formulat Excel mund të përdorni deri në shtatë nivele foleje funksione.
Vlen të përmendet se ka funksione në Excel që nuk kanë argumente. Shembuj të funksioneve të tilla janë PI (kthen vlerën e π të rrumbullakosura në 15 shifra) ose SOT (kthimin e datës aktuale). Kur përdorni funksione të tilla, vendosni në shiritin e formulës, menjëherë pas emrit të funksionit kllapa bosh pa argumente... Me fjalë të tjera, për të marrë vlerën e numrit p ose datën aktuale në qeliza, duhet të futni formulat e formës së mëposhtme:
SOT ()
Llojet e funksioneve të Excel
Për lehtësinë e përdoruesit gjatë ndërtimit të formulave, funksionet në Excel ndahen në kategori: funksionet e menaxhimit të bazës së të dhënave dhe listës, funksionet e datës dhe kohës, financiare, statistikore, tekstuale, matematikore, logjike.
Funksionet e tekstit përdoren për përpunimin e tekstit, përkatësisht: kërkimi i karaktereve të dëshiruara, shkrimi i karaktereve në një vend të përcaktuar rreptësisht në tekst, etj.
Nëpërmjet Funksionet e datës dhe orës ju mund të zgjidhni pothuajse çdo problem që lidhet me datat ose orët kalendarike (për shembull, llogaritni numrin e ditëve të punës për çdo periudhë kohore).
Funksionet logjike përdoren për të krijuar formula komplekse që në varësi të plotësimit të kushteve të caktuara do të zbatojnë lloje të ndryshme të përpunimit të të dhënave. Ato janë veçanërisht interesante, dhe ne do të flasim për to në një artikull të veçantë.
Excel është i përfaqësuar gjerësisht Funksionet e matematikës dhe unë kam dhënë tashmë disa në shembuj.
Përdoruesi gjithashtu ka në dispozicion një bibliotekë. Funksionet statistikore, me të cilin mund të kërkoni vlerën mesatare, elementet maksimale dhe minimale, etj.
ArgumentetËshtë informacion që një funksion përdor për të llogaritur një vlerë të re ose për të kryer një veprim. Argumentet janë gjithmonë në të djathtë të emrit të funksionit dhe janë të mbyllura në kllapa. Shumica e argumenteve janë të një lloji specifik. Në fakt, argumenti që jepni duhet të jetë ose i një lloji të përshtatshëm, ose një lloj që Microsoft Excel mund ta konvertojë në një lloj të përshtatshëm.
Argumentet e funksionit mund të jenë vlera numerike, referenca qelizash, vargje, emra, vargje teksti dhe funksione të ndërlidhura.
Nëse, gjatë përshkrimit të sintaksës së një funksioni, një argument pasohet nga një elipsë (...), kjo do të thotë se mund të ketë disa argumente të të njëjtit lloj. Disa funksione mund të marrin deri në 30 argumente, me kusht që numri i përgjithshëm i karaktereve në formulë të mos kalojë 1024. Për shembull, sintaksa për funksionin MAX është si më poshtë:
MAX (numri 1; numri 2; ...)
Një nga formulat e mëposhtme është e vlefshme:
MAX (26; 31; 29)
Funksionet me kllapa boshe pas emrit nuk kërkojnë argumente, megjithatë, duhet t'i përfshini ato kllapa boshe në formulën që Microsoft Excel të njohë funksionin.
Shumë nga emrat e argumenteve në përshkrimin sintaksor të një funksioni tregojnë se çfarë informacioni duhet të jepet si vlera aktuale e argumentit. Për shembull, funksioni ROUND (numër; num_shifra) duhet të ketë argumentin e parë një numër dhe i dyti duhet të jetë gjithashtu një numër.
Po kështu, fjalë të tilla si numër, referencë, flamur, tekst, grup, nëse përdoren si emër argumenti, tregojnë se argumenti duhet të jetë i llojit të duhur. fjalë Kuptimi nënkupton që argumenti mund të jetë çdo gjë që është një vlerë e vetme. Kjo do të thotë, vlera mund të jetë një numër, tekst, vlerë boolean ose gabim.
Duke përdorur argumente
Argumenti mund të jetë çdo gjë që jep një vlerë të llojit të kërkuar. Për shembull, një funksion SUM që përmbledh argumentet e tij mund të pranojë 1 deri në 30 argumente. Funksionit SUM mund t'i jepen argumente të cilitdo prej katër llojeve të mëposhtme, për sa kohë që ato japin një numër ose numra:
- një vlerë që është një numër, për shembull: SUM (1; 10; 100);
- një formulë që rezulton në një numër, për shembull: SHUMË (0,5 + 0,5; MESATAR (5; 5); 10 ^ 2)... Funksionet që përdoren si argumente për funksionet e tjera, si në shembullin e mëparshëm, quhen funksione të mbivendosur. Në këtë shembull, funksioni MESATARështë një argument funksioni SHUMË... Niveli i foleve të funksioneve në formula mund të jetë deri në shtatë;
SHUMË(A1; A2)
SUM (A1: A5)
Shembulli i dytë është i barabartë me formulën SUM (A1; A2; A3; A4; A5)... Avantazhi i përdorimit të një intervali është se argumenti A1: A5 llogaritet si një argument, ndërsa A1, A2, A3, A4, A5 numërohen si pesë argumente. Nëse keni nevojë të shtoni më shumë se 30 numra, atëherë do të duhet të përdorni intervale, sepse funksioni nuk mund të ketë më shumë se 30 argumente;
- një emër që i referohet një vlere, formule, qelize ose gamë qelizash që përmbajnë numra ose formula që japin numra, për shembull: SUM (Bazë; Rritje).
Llojet e argumenteve. Argumentet e funksionit mund të jenë cilido nga objektet e mëposhtme.
Numrat. Shembuj të numrave janë 5.003, 0, 150.286 dhe -30.05. Numrat pa presje dhjetore quhen numra të plotë. Shembuj të numrave të plotë janë 5, 0, 150 dhe -30. Numrat mund të kenë deri në 15 shifra të rëndësishme.
Teksti. Shembuj të teksteve janë "a", "Fjala", "shenja / pikësimi" dhe "" (tekst bosh). Vlerat e tekstit të përdorura në formula duhet të mbyllen në thonjëza të dyfishta. Nëse vetë teksti përmban thonjëza të dyfishta, atëherë ato duhet të dyfishohen. Për shembull, për të përcaktuar gjatësinë (në karaktere) të tekstit "në kohët e mira" të vjetra, mund të përdorni formulën:
DLSTR ("në" "mirë" "kohë të vjetër")
Vlerat e tekstit mund të jenë deri në 32,000 karaktere të gjata, duke numëruar thonjëza të dyfishta. Një konstante teksti që nuk përmban karaktere shkruhet kështu: "" dhe quhet "tekst bosh".
shënim . Nëse teksti i përdorur si argument nuk është i mbyllur në thonjëza të dyfishta, atëherë Microsoft Excel supozon se ky është një emër dhe përpiqet të zëvendësojë vlerën të cilës i referohet emri. Nëse teksti i pacituar nuk është një emër dhe për këtë arsye nuk ka rëndësi, Microsoft Excel do të kthejë një vlerë gabimi #EMRI?.
Vlerat Boolean. Vlerat Boolean janë TRUE dhe FALSE. Argumentet Boolean mund të jenë gjithashtu shprehje të tilla si B10> 20, vlerat e të cilave janë TRUE ose FALSE.
Vlerat e gabimit. Vlerat e gabimit janë # DIV / 0 !, # N / A, # EMRI ?, # BASHKË !, # NUMRI !, # LIDHJE! dhe #VLERË!.
Lidhjet. Shembuj të lidhjeve janë $ A 10 $, A10, $ A10, A $ 10, R1C1 ose RC [-10]... Referencat mund të tregojnë qeliza të vetme, varg qelizash ose zgjedhje të shumëfishta qelizash dhe mund të jenë relative, absolute ose të përziera. Nëse lidhja përdoret si argument, i cili duhet të jetë një numër, tekst, boolean ose vlerë gabimi, atëherë argumenti aktual është përmbajtja e qelizës së specifikuar nga lidhja.
SUM ((E5: E8; E10: E18); MESATAR (A1: A5))
Vargjeve. Vargjet ju lejojnë të kontrolloni se si argumentet dhe funksionet futen në qeliza. Përdorimi i vargjeve e bën më të lehtë dizajnimin e disa formulave të fletës së punës dhe kursen memorie. Microsoft Excel përcakton dy lloje vargjesh: vargjet e grupeve dhe vargjet konstante. Një varg grupi është një varg i lidhur qelizash që përdorin një formulë të përbashkët; një varg konstante është një koleksion konstantesh të përdorura si argumente për funksionet.
Përdorimi i pikëpresjes në listat e argumenteve
Argumentet individuale duhet të ndahen me pikëpresje, por nuk duhet të ketë pikëpresje shtesë. Nëse një pikëpresje përdoret vetëm për të shënuar vendndodhjen e një argumenti dhe vetë argumenti nuk është specifikuar, atëherë Microsoft Excel zëvendëson një vlerë të paracaktuar për atë argument, përveç nëse kërkohet argumenti. Për shembull, nëse futni (; arg2; arg3) si listën e argumenteve për një funksion me tre argumente, Microsoft Excel zëvendëson vlerën e duhur për arg1. Nëse futni (arg1 ;;), atëherë vlerat e përshtatshme do të zëvendësohen për arg2 dhe arg3.
Për funksionet që numërojnë numrin e argumenteve përpara se të vlerësohen, pikëpresje shtesë do të numërohen gjatë numërimit të numrit të argumenteve dhe për këtë arsye do të ndikojnë në mënyrën se si llogaritet vlera e funksionit. Për shembull, MESATAR (1; 2; 3; 4; 5)është e barabartë me 3 por MESATAR (;; 1; 2; 3; 4; 5)është 2.14.
Për shumicën e argumenteve, vlera e zëvendësuar për një argument të hequr është 0, FALSE ose "" (tekst bosh), në varësi të llojit të argumentit. Për një argument referencë të lënë pas dore, parazgjedhja është zakonisht qeliza ose përzgjedhja aktive.
Funksioni IF është më i popullarizuari midis funksioneve të Excel-it, ai kthen një vlerë nëse kushti i specifikuar rezulton në TRUE dhe një vlerë tjetër nëse kushti rezulton në FALSE.
Përshkrimi i funksionit IF
Funksioni IF kthen një vlerë nëse kushti i specifikuar vlerësohet në TRUE dhe një vlerë tjetër nëse kushti vlerësohet në FALSE. Për shembull, formula = IF (A1> 10, "Më shumë se 10", "10 ose më pak")
kthen vargun Më të madh se 10 nëse vlera në qelizën A1 është më e madhe se 10 dhe 10 ose më pak nëse është më e vogël ose e barabartë me 10.
Sintaksë
= IF (testi_logjik, [vlera_nëse_e vërtetë], [vlera_nëse_false])Argumentet
log_shprehja vlera_nëse_e vërtetëvlera_nëse_false
Argumenti i kërkuar.Çdo vlerë ose shprehje që rezulton në TRUE ose FALSE. Për shembull, A10 = 100 është një shprehje boolean; nëse vlera në qelizën A10 është 100, kjo shprehje vlerësohet në TRUE; përndryshe, vlerësohet në FALSE. Çdo operator krahasimi mund të përdoret në këtë argument.
Argument opsional. Vlera që kthehet nëse testi logjik është TRUE. Për shembull, nëse argumenti i dhënë është vargu "Brenda buxhetit" dhe argumenti log_test është TRUE, funksioni IF kthen tekstin "Brenda buxhetit". Nëse testi_boolean është TRUE dhe vlera_if_true hiqet (d.m.th. ka vetëm një presje pas log_testit), kthehet vlera 0. Për të shfaqur fjalën TRUE, përdorni si argument Boolean TRUE.
Argument opsional. Vlera që kthehet nëse log_test është FALSE. Për shembull, nëse argumenti i dhënë është vargu "Over buxhet" dhe log_test është FALSE, funksioni IF kthen tekstin "Mbi buxhet". Nëse testi_boolean është FALSE dhe vlera_if_false hiqet (d.m.th., nuk ka presje pas vlerës_if_true), funksioni IF kthen vlerën boolean FALSE. Nëse testi_boolean është FALSE dhe vlera_if_false është bosh (d.m.th. ka vetëm një presje pas value_if_true), IF kthen 0 (zero).
Vërejtje
- Mund të përdorni deri në 64 funksione të ndërlidhura IF për të krijuar kontrolle më komplekse për argumentet value_if_true dhe value_if_false (shih shembullin 3 për përdorimin e funksioneve të ndërlidhura IF). Përveç kësaj, LOOKUP, VLOOKUP, HLOOKUP dhe SELECT mund të përdoren për të kontrolluar kushte të shumta (shih shembullin 4 për funksionin VIEW).
- Nëse ndonjë nga argumentet e funksionit IF është një grup, funksioni IF vlerëson të gjithë elementët e grupit.
- Excel ofron funksione shtesë që mund t'i përdorni për të analizuar të dhënat duke përdorur kushte. Për shembull, për të numëruar numrin e shfaqjeve të një vargu teksti ose numri në një varg qelizash, përdorni funksionet e fletës së punës COUNTIF dhe COUNTIFS. Përdorni funksionet SUMIF dhe SUMIFS për të llogaritur shumën e vlerave që bien brenda intervalit të specifikuar nga një varg teksti ose numra.
