Logikai függvények az Excelben. Mindent a logikai függvények alkalmazásáról az Excelben

Az Excel egy hihetetlenül funkcionális program, amely lehetővé teszi nemcsak az adatok táblázatos formában történő rögzítését, hanem a feldolgozás automatizálását is. A logikai függvények a fő elem, amely lehetővé teszi az ilyen jellegű műveletek végrehajtását. Képletekben és egyéb függvényekben használatosak az összes művelet egyszerűsítése érdekében.

Úgy tervezték, hogy ellenőrizzék, hogy az értékek megfelelnek-e a megadott kritériumoknak. Ha van ilyen egyezés, abba a cellába, ahova írják, az „IGAZ” értéket kell beírni, eltérés esetén „HAMIS”. Ma részletesebben megvizsgáljuk az olyan kérdéseket, mint a logikai függvények felépítése, használatuk köre.

Az Excel logikai függvényeinek listája

Nagyon sok logikai függvény létezik, de a leggyakrabban használtak a következők:

1. TRUE
2. FEKVŐ
3. IF
4. IFROROR
5. OR
6. И
7. NEM
8. EOSHIBKA
9. ÜRES

Mindegyik felhasználható összetett struktúrák létrehozására és bármilyen sorrendű kritérium megadására. Szinte mindegyik funkció magában foglalja bizonyos paraméterek átadását. Az egyetlen kivétel az IGAZ és a HAMIS, amelyek visszaadják magukat. Számokat, szöveget, cellahivatkozásokat, tartományokat stb. gyakran használnak paraméterként. Vessünk egy pillantást a fenti operátorok mindegyikére.

Operátorok IGAZ és HAMIS

A két függvényben az a közös, hogy csak egy értéket adnak vissza. Felhasználási körük más funkciók összetevőjeként történő felhasználás. Amint az az operátorok nevéből kiolvasható, a függvények TRUE и FEKVŐ értékeket adja vissza TRUE и FEKVŐ illetőleg.

NEM operátor

Ezt a függvényt egy argumentummal használják, és az ellenkező értéket írják a cellába. Ha átlépi ezt az operátort TRUE, akkor visszatér FEKVŐ és ennek megfelelően az ellenkező állítás igaz. Ezért az operátor által végzett adatfeldolgozás eredménye teljesen attól függ, hogy milyen paramétereket kell átadni neki.

Ennek az operátornak a szintaxisa a következő: =NEM (igaz vagy hamis).

Operátorok ÉS és VAGY

Ez a két operátor szükséges ahhoz, hogy egy kifejezés feltételeinek egymáshoz való viszonyát közvetítsük. Funkció И azt jelzi, hogy két feltételnek egyidejűleg meg kell egyeznie ugyanannak a számnak vagy szövegnek. Ez a függvény értéket ad vissza TRUE csak azzal a feltétellel, hogy minden kritérium egyszerre adja ezt az értéket. Ha legalább egy feltétel meghiúsul, a teljes sorozat egy értéket ad vissza FEKVŐ.

Az ÉS operátor felépítése nagyon egyszerű: =És(argumentum1; argumentum2; …). A függvény által használható argumentumok maximális száma 255. Kezelői szintaxis OR hasonló, de a munka mechanikája kissé eltér. Ha a függvénylista valamelyike ​​eredményt ad TRUE, akkor ez a szám egy teljes logikai sorozatként kerül visszaadásra.

IF és ISERROR utasítások

Ennek a két függvénynek nagyon fontos célja van – közvetlenül meghatározzák azt a kritériumot, amelynek betartását egy bizonyos kifejezést ellenőrizni kell. A kezelő működésének mélyebb megértéséhez IFROROR, először le kell írnia a függvényt IF. Általános felépítése kicsit bonyolultabb, mint az előzőek: =HA(logikai_kifejezés, érték_ha_igaz, érték_ha_hamis).

Ennek az operátornak a feladata a legbonyolultabb konstrukciók létrehozása. Ellenőrzi, hogy a feltételek teljesülnek-e. Ha igen, akkor az operátor visszatér TRUE, ha nem - FEKVŐ. De az operátort gyakran másokkal együtt használják. Például, ha függvényargumentumként használják NEM, akkor ennek megfelelően a végösszeg automatikusan az ellenkezőjére cserélődik. Azaz, ha van egyezés a feltétellel, akkor az értéket a rendszer visszaadja FEKVŐ. Ez a logikai függvények fő előnye: a legfurcsább formákban kombinálhatók.

Ezenkívül a rendszer bonyolultabbá válik. Ha ezzel a kritériummal az „IGAZ” eredményt kapjuk, akkor megadhatja a szöveget, a megjelenítendő számot vagy a kiszámítandó függvényt. Hasonlóképpen beállíthatja azt az eredményt, amely akkor jelenjen meg, ha az eredmény az adatok feldolgozása után került visszaadásra. FEKVŐ.

Az operátor felépítése IFROROR nagyon hasonló, de mégis kicsit más. Két kötelező argumentumot tartalmaz:

1. Jelentése. Magát a kifejezést tesztelik. Ha igaznak bizonyul, akkor ez az érték kerül visszaadásra.
2. Az érték, ha hiba. Ez az a szöveg, szám vagy függvény, amely megjelenik vagy végrehajtódik, ha az első argumentum ellenőrzésének eredménye HAMIS.

Syntax: =IFERROR(érték;érték_ha_hiba).

ISERROW és ISEMPLAND operátorok

A fentiek első függvénye csak egy értéket tartalmaz, és a következő szintaxissal rendelkezik: =HIBA(érték). Ennek az operátornak az a feladata, hogy ellenőrizze, mennyire vannak kitöltve a cellák (egy vagy a teljes tartományban). Ha kiderül, hogy a párnázás rossz, akkor a valódi eredményt adja vissza. Ha minden jó – hamis. Közvetlenül egy másik funkció kritériumaként is alkalmazható.

Az Excel a következő típusú hibákat tudja ellenőrizni a hivatkozásokon:

• #NÉV?;
• #N/A;
• #DEL/0!;
• #SZÁM!;
• #ÍGY;
• #ÜRES!;
• #LINK!.

Funkció ÜRES Összességében hihetetlenül egyszerű. Csak egy paramétert tartalmaz, ez az ellenőrizendő cella/tartomány. Ha van olyan cella, amelyben nincs sem szöveg, sem szám, sem nem nyomtatható karakter, akkor az eredményt a rendszer visszaadja. TRUE. Ennek megfelelően, ha a tartomány összes cellájában van adat, akkor a felhasználó megkapja az eredményt FEKVŐ.

Jegyzettáblázat „Logikai függvények az Excelben”

A fent leírtak összegzésére adjunk egy kis táblázatot, amely információkat tartalmaz az összes gyakran használt logikai függvényről.

Logikai függvények és példák problémamegoldásra

A logikai függvények sokféle feladat megoldását teszik lehetővé, beleértve az összetetteket is. Nézzünk néhány példát a gyakorlati működésre.

Feladat 1. Tegyük fel, hogy egy bizonyos értékesítési idő után megmaradt az áru egy része. Át kell értékelni a következő szabályok szerint: ha 8 hónap alatt nem lehetett eladni, akkor az árát el kell osztani 2-szeresével. Először hozzunk létre egy tartományt, amely leírja a kezdeti adatokat. Ez így néz ki.

A leírt feladat sikeres megoldásához a következő funkciót kell használni.

A képernyőképen a képletsávban láthatja. Most tegyünk néhány pontosítást. A képernyőképen látható logikai kifejezés (azaz C2>=8) azt jelenti, hogy a terméknek 8 hónapig készleten kell lennie. A >= aritmetikai operátorok segítségével meghatározzuk a nagyobb vagy egyenlő szabályt. Miután felírtuk ezt a feltételt, a függvény két érték egyikét adja vissza: „TRUE” vagy „FALSE”. Ha a képlet megfelel a feltételnek, akkor az átértékelés utáni értéket a cellába írjuk (jól, vagy argumentumként átadjuk egy másik függvénynek, minden a felhasználó által beállított paraméterektől függ), elosztjuk kettővel (ehhez elosztottuk a raktári átvételkori ár kettővel) . Ha ezt követően kiderül, hogy a termék 8 hónapnál rövidebb ideig volt raktáron, akkor ugyanazt az értéket adjuk vissza, amely a cellában található.

Most nehezítsük meg a feladatot. Alkalmazzuk a feltételt: a kedvezmények mértékének progresszívnek kell lennie. Egyszerűen fogalmazva, ha az áru több mint 5 hónapig fekszik, de kevesebb, mint 8, akkor az árat el kell osztani másfélszeresével. Ha több mint 8, akkor kettő. Ahhoz, hogy ez a képlet megfeleljen az értéknek, a következőnek kell lennie. Nézze meg a képletsorban lévő képernyőképet.

Fontos! Argumentumként nem csak numerikus, hanem szöveges értékek is használhatók. Ezért megengedett a legkülönbözőbb sorrendű kritériumok meghatározása. Például leengedni a januárban kapott árukat, és nem, ha áprilisban érkezett.

Feladat 2. Alkalmazzuk ezt a kritériumot egy raktáron lévő termékre. Tegyük fel, hogy ha a fenti leértékelés után az értéke 300 rubel alá csökken, vagy ha több mint 10 hónapja értékesítés nélkül áll, akkor egyszerűen kivonják az értékesítésből. A képlet a következő.

Elemezzük. A függvényt használtuk kritériumként OR. Szükség van egy ilyen villa biztosítására. Ha a D2 cellában a 10-es szám szerepel, akkor az E oszlop megfelelő sorában automatikusan a „leírva” érték jelenik meg. Ugyanez vonatkozik a másik feltételre is. Ha egyik sem teljesül, akkor egyszerűen egy üres cellát ad vissza.

Feladat 3. Tegyük fel, hogy van egy mintánk olyan tanulókból, akik középiskolába próbálnak bejutni. Ehhez több tárgyból is le kell vizsgázniuk, az alábbi képernyőképen látható. Ahhoz, hogy jogosultak legyenek ebbe az oktatási intézménybe, összesen 12 pontot kell elérniük. Ugyanakkor fontos feltétel, hogy a matematika pontszáma ne legyen kevesebb 4 pontnál. A feladat ezen adatok feldolgozásának automatizálása, valamint egy jelentés összeállítása arról, hogy a tanulók melyiket írták be és melyik nem. Ehhez készítünk egy ilyen táblázatot.

Tehát az a feladatunk, hogy a program kiszámítsa, hány pont lesz összesen, megnézzük az áthaladási eredményt és összehasonlítást végezzünk. Ezen műveletek után a függvénynek az eredményt abba a cellába kell helyeznie, amelybe illeszkedik. Két lehetőség van: „elfogadva” vagy „nem”. A feladat végrehajtásához írjon be egy hasonló képletet (csak illessze be az értékeket): =ЕСЛИ(И(B3>=4;СУММ(B3:D3)>=$B$1);»принят»;»нет»).

Logikai függvénnyel И ellenőrizhetjük, hogy két feltétel egyszerre teljesül-e. Ebben az esetben a függvényt használtuk ÖSSZEG az összpontszám kiszámításához. Első feltételként (az ÉS függvény első argumentumában) a B3>=4 képletet adtuk meg. Ez az oszlop matematikai pontszámot tartalmaz, amely nem lehet alacsonyabb 4 pontnál.

Látjuk a funkció széleskörű alkalmazását IF amikor táblázatokkal dolgozik. Ezért ez a legnépszerűbb logikai függvény, amelyet először meg kell ismernie.

Erősen ajánlott a teszttáblázaton gyakorolni, mielőtt ezeket a készségeket valódi munkában használná. Ezzel sok időt takaríthatunk meg.

4. feladat Az a feladat áll előttünk, hogy meghatározzuk az áruk összköltségét a leárazás után. Követelmény – a termék költségének magasabbnak vagy átlagosnak kell lennie. Ha ez a feltétel nem teljesül, az árut le kell írni. Ebben a példában látni fogjuk, hogyan működik egy csomó aritmetikai és statisztikai függvény.

Használjuk a már megrajzolt táblázatot. A probléma megoldásához feltételként kell beállítani azt a szabályt, hogy a D2 cellának kisebbnek kell lennie, mint a teljes árukör számtani középértéke. Ha a szabály megerősítést nyer, akkor abban a cellában, ahol ez a képlet van írva, a „leírva” érték kerül beállításra. Ha a feltétel nem teljesül, akkor üres érték kerül beállításra. A számtani átlag visszaadásához van egy függvény ÁTLAGOS.

5. feladat Tegyük fel, hogy ki kell számolnunk a különböző termékek átlagos eladásait ugyanazon márka különböző üzleteiben. Készítsünk egy ilyen táblázatot.

Feladatunk, hogy minden értékre meghatározzuk azt az átlagot, amely bizonyos jellemzőkre illeszkedik. Ehhez egy speciális függvényt használunk, amely nem szerepelt a fenti listában. Lehetővé teszi két funkció kombinálását ÁTLAGOS и HA. És hívott SZÍVTELEN. Három érvet tartalmaz:

1. Az ellenőrizendő tartomány.
2. Az ellenőrizendő állapot.
3. Tartományátlagolás.

Ennek eredményeként a következő képletet kapjuk (a képernyőképen).

Látjuk, hogy a logikai függvények alkalmazási köre egyszerűen hatalmas. És a listájuk valójában sokkal nagyobb, mint a fent leírtak. Csak felsoroltuk a legnépszerűbbeket, de bemutattunk egy példát egy másik függvényre is, amely a statisztikai és a logikai kombinációja. Vannak más hasonló hibridek is, amelyek külön figyelmet érdemelnek.