Logične funkcije v Excelu. Vse o uporabi logičnih funkcij v Excelu

Excel je neverjetno funkcionalen program, ki vam omogoča ne le zapisovanje podatkov v obliki tabele, temveč tudi avtomatizacijo njihove obdelave. Logične funkcije so glavni element, ki vam omogoča izvajanje takšnih operacij. Uporabljajo se v formulah in drugih funkcijah za poenostavitev vseh operacij.

Namenjeni so preverjanju, ali vrednosti ustrezajo določenim merilom. Če obstaja takšno ujemanje, se v celico, kjer je zapisano, vnese vrednost "TRUE", v primeru neskladja - "FALSE". Danes bomo podrobneje obravnavali vprašanja, kot so struktura logičnih funkcij, obseg njihove uporabe.

Seznam logičnih funkcij v Excelu

Logičnih funkcij je ogromno, a najpogosteje uporabljene so naslednje:

1. TRUE
2. LAŽI
3. IF
4. NAPAKA
5. OR
6. И
7. NE
8. EOŠIBKA
9. ISBLANK

Vse jih je mogoče uporabiti za ustvarjanje kompleksnih struktur in določanje kriterijev poljubnega reda. Skoraj vse te funkcije vključujejo posredovanje določenih parametrov. Edina izjema sta TRUE in FALSE, ki se vrneta sami. Kot parametri se pogosto uporabljajo številke, besedilo, sklice na celice, obsegi itd. Oglejmo si vse zgoraj navedene operaterje.

Operatorja TRUE in FALSE

Obema funkcijama je skupno to, da vrneta samo eno vrednost. Področje njihove uporabe je uporaba kot sestavni del drugih funkcij. Kot je razbrati iz imena operaterjev, funkcij TRUE и LAŽI povratne vrednosti TRUE и LAŽI oz.

NE operater

Ta funkcija se uporablja z enim argumentom in v celico zapiše nasprotno vrednost. Če opravite ta operater TRUE, potem se bo vrnilo LAŽI in v skladu s tem velja nasprotna trditev. Zato je rezultat obdelave podatkov s strani tega operaterja popolnoma odvisen od tega, katere parametre mu posredovati.

Sintaksa tega operatorja je naslednja: =NI (true ali false).

Operatorja IN in ALI

Ta dva operaterja sta potrebna za posredovanje razmerja med pogoji izraza med seboj. funkcija И se uporablja za označevanje, da se morata dva kriterija hkrati ujemati z isto številko ali besedilom. Ta funkcija vrne vrednost TRUE le pod pogojem, da vsi kriteriji istočasno ustvarijo to vrednost. Če vsaj en kriterij ni izpolnjen, celotno zaporedje vrne vrednost LAŽI.

Način izdelave operatorja IN je zelo preprost: =In(argument1; argument2; …). Največje število argumentov, ki jih lahko uporabi ta funkcija, je 255. Sintaksa operatorja OR podobni, vendar je mehanika dela nekoliko drugačna. Če ena od funkcij s seznama daje rezultat TRUE, potem bo to število vrnjeno kot celotno logično zaporedje.

Stavka IF in ISERROR

Ti dve funkciji imata zelo pomemben namen – neposredno določata kriterij skladnosti, s katerim je treba preveriti določen izraz. Za globlje razumevanje delovanja operaterja NAPAKA, morate najprej opisati funkcijo IF. Njegova splošna struktura je nekoliko bolj zapletena od prejšnjih: =ČE(logični_izraz, vrednost_če_true, vrednost_če_napačno).

Naloga tega operaterja je ustvariti najbolj zapletene konstrukcije. Preveri, ali so kriteriji izpolnjeni. Če da, se bo operater vrnil TRUE, če ne - LAŽI. Toda operater se pogosto uporablja v povezavi z drugimi. Na primer, če se uporablja kot argument funkcije NE, potem bo v skladu s tem skupni znesek samodejno nadomeščen z nasprotnim. To pomeni, da bo vrnjena vrednost, če obstaja ujemanje s kriterijem LAŽI. To je glavna prednost logičnih funkcij: kombinirati jih je mogoče v najbolj bizarnih oblikah.

Nadalje shema postane bolj zapletena. Če po tem kriteriju dobimo rezultat »TRUE«, potem lahko določite besedilo, število, ki bo prikazano, ali funkcijo, ki bo izračunana. Podobno lahko nastavite rezultat, ki bo prikazan, če je bil rezultat vrnjen po obdelavi podatkov. LAŽI.

Struktura operaterja NAPAKA precej podobna, a vseeno nekoliko drugačna. Vsebuje dva zahtevana argumenta:

1. Pomen. Preizkuša se izraz sam. Če se izkaže za resnično, se ta vrednost vrne.
2. Vrednost, če je napaka. To je besedilo, število ali funkcija, ki bo prikazana ali izvedena, če je bil rezultat preverjanja prvega argumenta FALSE.

Skladnja: =IFERROR(vrednost;vrednost_če_napaka).

Operatorja ISERROW in ISEMPLAND

Prva funkcija zgoraj vsebuje samo eno vrednost in ima naslednjo sintakso: =ISERROR(vrednost). Naloga tega operaterja je preveriti, kako dobro so celice zapolnjene (ena ali v celotnem obsegu). Če se izkaže, da je bilo oblazinjenje napačno, vrne pravi rezultat. Če je vse dobro – laž. Lahko se uporabi neposredno kot merilo za drugo funkcijo.

Excel lahko preveri povezave za naslednje vrste napak:

• #IME?;
• #N/A;
• #DEL/0!;
• #ŠTEVILKA!;
• #SO;
• #PRAZNO!;
• #POVEZAVA!.

funkcija ISBLANK Na splošno je neverjetno preprosto. Vsebuje samo en parameter, in sicer celico/obseg, ki ga je treba preveriti. Če je celica brez besedila, številk ali nenatisljivih znakov, se vrne rezultat TRUE. V skladu s tem, če so podatki v vseh celicah obsega, potem uporabnik prejme rezultat LAŽI.

Memo tabela “Logične funkcije v Excelu”

Če povzamemo vse zgoraj opisano, dajmo majhno tabelo, ki vsebuje informacije o vseh pogosto uporabljenih logičnih funkcijah.

Logične funkcije in primeri reševanja problemov

Logične funkcije omogočajo reševanje različnih nalog, vključno s kompleksnimi. Naj navedemo nekaj primerov, kako delujejo v praksi.

1. naloga. Recimo, da nam po določenem času prodaje ostane del blaga. Ponovno ga je treba oceniti po naslednjih pravilih: če ga v 8 mesecih ni bilo mogoče prodati, njegovo ceno delite z 2-krat. Najprej ustvarimo obseg, ki opisuje začetne podatke. Izgleda takole.

Da bo opisana naloga uspešno rešena, morate uporabiti naslednjo funkcijo.

To lahko vidite v vrstici s formulami na posnetku zaslona. Zdaj pa naredimo nekaj pojasnil. Logični izraz, ki je prikazan na posnetku (to je C2>=8) pomeni, da mora biti izdelek na zalogi do vključno 8 mesecev. Z uporabo aritmetičnih operatorjev >= definiramo pravilo večje ali enako. Ko smo zapisali ta pogoj, bo funkcija vrnila eno od dveh vrednosti: “TRUE” ali “FALSE”. Če formula izpolnjuje merilo, se vrednost po prevrednotenju zapiše v celico (no, ali pa se kot argument prenese v drugo funkcijo, vse je odvisno od parametrov, ki jih nastavi uporabnik), deljeno z dva (za to smo razdelili cena ob prevzemu v skladišče za dva) . Če se po tem ugotovi, da je bil izdelek na zalogi manj kot 8 mesecev, se vrne ista vrednost, ki je v celici.

Zdaj pa otežimo nalogo. Veljamo pogoj: lestvica popustov mora biti progresivna. Preprosto povedano, če blago leži več kot 5 mesecev, vendar manj kot 8, je treba ceno razdeliti na enkrat in pol. Če jih je več kot 8, dva. Da se ta formula ujema z vrednostjo, mora biti naslednja. Oglejte si posnetek zaslona v vrstici s formulami, da ga vidite.

Pomembno! Kot argumente je dovoljeno uporabljati ne samo številske, ampak tudi besedilne vrednosti. Zato je dopustno postaviti kriterije najrazličnejšega reda. Na primer, narediti popust na blago, prejeto januarja, in tega ne storiti, če je prispelo aprila.

2. naloga. Uporabimo ta kriterij za izdelek, ki je na zalogi. Recimo, če je po zgornjem znižanju njegova vrednost padla na manj kot 300 rubljev ali če je bila brez prodaje več kot 10 mesecev, potem je preprosto odstranjena iz prodaje. Formula je naslednja.

Analizirajmo ga. Kot merilo smo uporabili funkcijo OR. Takšne vilice je treba zagotoviti. Če celica D2 vsebuje številko 10, se vrednost »odpisano« samodejno izpiše v ustrezni vrstici stolpca E. Enako velja za drugi pogoj. Če nobena od njih ni izpolnjena, se preprosto vrne prazna celica.

3. naloga. Recimo, da imamo vzorec dijakov, ki se poskušajo vpisati v srednjo šolo. Za to morajo opraviti izpite iz več predmetov, prikazanih na spodnjem posnetku zaslona. Da bi bili upravičeni do vpisa v to izobraževalno ustanovo, morajo doseči skupno 12 točk. Hkrati je pomemben pogoj, da ocena pri matematiki ne sme biti nižja od 4 točk. Naloga je avtomatizirati obdelavo teh podatkov, pa tudi sestaviti poročilo o tem, kateri študenti so vpisani in kateri ne. Da bi to naredili, bomo naredili takšno tabelo.

Naša naloga je torej, da program izračuna, koliko bo skupno točk, pogleda prehodni rezultat in izvede primerjavo. Po teh operacijah mora funkcija dati rezultat v celico, v katero se prilega. Možni sta dve možnosti: »sprejeto« ali »ne«. Za izvedbo te naloge vnesite podobno formulo (samo vstavite svoje vrednosti): =ЕСЛИ(И(B3>=4;СУММ(B3:D3)>=$B$1);»принят»;»нет»).

Z logično funkcijo И lahko preverimo, ali sta izpolnjena dva pogoja hkrati. V tem primeru smo uporabili funkcijo SUM za izračun skupnega rezultata. Kot prvi pogoj (v prvem argumentu funkcije IN) smo podali formulo B3>=4. Ta stolpec vsebuje oceno pri matematiki, ki ne sme biti nižja od 4 točk.

Vidimo široko uporabo funkcije IF pri delu s preglednicami. Zato je to najbolj priljubljena logična funkcija, ki jo morate najprej poznati.

Zelo priporočljivo je, da vadite na testni tabeli, preden te veščine uporabite v resničnem delu. To bo pomagalo prihraniti veliko časa.

4. naloga. Soočeni smo z nalogo določitve skupnih stroškov blaga po znižanju vrednosti. Zahteva – cena izdelka mora biti višja ali povprečna. Če ta pogoj ni izpolnjen, je treba blago odpisati. V tem primeru bomo videli, kako deluje kup aritmetičnih in statističnih funkcij.

Uporabimo tabelo, ki smo jo že narisali. Za rešitev tega problema je potrebno kot pogoj postaviti pravilo, da mora biti celica D2 manjša od aritmetične sredine celotnega asortimana blaga. Če je pravilo potrjeno, je v celici, kjer je zapisana ta formula, nastavljena vrednost »odpisano«. Če kriterij ni izpolnjen, se nastavi prazna vrednost. Za vrnitev aritmetične sredine obstaja funkcija POVPREČNA.

5. naloga. Recimo, da moramo izračunati povprečno prodajo različnih izdelkov v različnih trgovinah iste blagovne znamke. Naredimo takšno mizo.

Naša naloga je, da za vse vrednosti določimo povprečje, ki ustreza določenim karakteristikam. Za to uporabimo posebno funkcijo, ki je ni bilo na zgornjem seznamu. Omogoča združevanje dveh funkcij POVPREČNA и ČE. In je poklicala BREZSRČNI. Vsebuje tri argumente:

1. Razpon za preverjanje.
2. Pogoj, ki ga je treba preveriti.
3. Povprečenje obsega.

Kot rezultat dobimo naslednjo formulo (na posnetku zaslona).

Vidimo, da je obseg uporabe logičnih funkcij preprosto ogromen. In njihov seznam je dejansko veliko večji od zgoraj opisanega. Našteli smo le najbolj priljubljene med njimi, opisali pa smo tudi primer druge funkcije, ki je kombinacija statistične in logične. Obstajajo tudi drugi podobni hibridi, ki si zaslužijo ločeno obravnavo.