Kā lietot loģiskās funkcijas programmā Excel IF, AND, OR, XOR, NOT

Loģiskās funkcijas ir dažas no populārākajām un noderīgākajām programmā Excel. Tās var pārbaudīt vērtības citās šūnās un veikt darbības, kas atkarīgas no testa rezultātiem. Tas palīdz mums automatizēt uzdevumus mūsu izklājlapās.

Kā lietot IF funkciju

IF funkcija ir Excel galvenā loģiskā funkcija, un tāpēc tā vispirms ir jāsaprot. Šajā rakstā tā parādīsies vairākas reizes.

Apskatīsim IF funkcijas struktūru un pēc tam redzēsim dažus tā izmantošanas piemērus.

IF funkcija pieņem 3 informācijas bitus:

= IF (loģiskā_testa, [vērtība_if_true], [vērtība_if_false])

• loģisks_tests: Tas ir nosacījums, lai varētu pārbaudīt funkciju.
• value_if_true: Darbība, kas jāveic, ja nosacījums ir izpildīts vai ir taisnība.
• value_if_false: Darbība, kas jāveic, ja nosacījums nav izpildīts, vai ir nepatiesa.

Salīdzināšanas operatori, kas izmantojami ar loģiskām funkcijām

Veicot loģisko testu ar šūnu vērtībām, jums ir jāzina salīdzināšanas operatori. Šo tabulu var redzēt zemāk redzamajā tabulā.

Tagad aplūkosim dažus piemērus.

IF Funkcija 1. piemērs: teksta vērtības

Šajā piemērā mēs vēlamies pārbaudīt, vai šūna ir vienāda ar konkrētu frāzi. IF funkcija nav reģistrjutīga, tāpēc tajā netiek ņemti vērā lielie un mazie burti.

C slejā izmanto šādu formulu, lai parādītu “Nē”, ja B slejā ir teksts “Pabeigts” un “Jā”, ja tajā ir kaut kas cits.

= IF (B2 = "Pabeigts", "Nē", "Jā")

Lai gan IF funkcija nav reģistrjutīga, tekstam jābūt precīzam.

IF Funkcija 2. piemērs: ciparu vērtības

IF funkcija ir lieliska arī skaitlisko vērtību salīdzināšanai.

Turpmākajā formulā mēs pārbaudām, vai B2 šūnā ir skaitlis, kas ir lielāks vai vienāds ar 75. Ja tas notiek, tad mēs parādīsim vārdu "Pass" un, ja ne, vārdu "Fail."

= IF (B2> = 75, "Pass", "Fail")

IF funkcija ir daudz vairāk nekā tikai dažādu tekstu parādīšana testa rezultātos. Mēs varam to izmantot arī dažādu aprēķinu veikšanai.

Šajā piemērā mēs vēlamies piešķirt 10% atlaidi, ja klients tērē noteiktu naudas summu. Kā piemēru izmantosim £ 3000.

= IF (B2> = 3000, B2 * 90%, B2)

Formula B2 * 90% daļa ir veids, kā jūs varat atņemt 10% no vērtības šūnā B2. Ir daudz veidu, kā to izdarīt.

Svarīgi ir tas, ka jūs varat izmantot jebkuru formulu value_if_true vai value_if_false sadaļās. Un dažādu formulu izmantošana, kas ir atkarīga no citu šūnu vērtībām, ir ļoti spēcīga prasme.

IF Funkcija 3. piemērs: Datuma vērtības

Šajā trešajā piemērā mēs izmantojam IF funkciju, lai izsekotu termiņu sarakstu. Vēlamies parādīt vārdu “nokavēts”, ja B slejā norādītais datums ir pagātnē. Bet, ja datums ir nākotnē, aprēķiniet dienu skaitu līdz termiņam.

Turpmāk minētā formula ir izmantota C ailē. Pārbaudām, vai maksājuma datums B2 šūnā ir mazāks par šodienas datumu (funkcija TODAY atgriež šodienas datumu no datora pulksteņa).

= IF (B2 
 
Kas ir ligzdotās IF formulas?
 
Iespējams, jūs esat dzirdējuši par terminu “ligzdoti IF”. Tas nozīmē, ka mēs varam rakstīt IF funkciju citā IF funkcijā. Mēs to vēlamies darīt, ja mums ir vairāk nekā divas darbības.
 
Viena IF funkcija spēj veikt divas darbības ( value_if_true un value_if_false ). Bet, ja mēs iegultu (vai ligzdojam) citu IF funkciju value_if_false sadaļā, tad mēs varam veikt citu darbību.
 
Ņemiet šo piemēru, kur mēs vēlamies parādīt vārdu “Excellent”, ja vērtība šūnā B2 ir lielāka vai vienāda ar 90, parādās “Good”, ja vērtība ir lielāka par vai vienāda ar 75, un parādīt “Poor”, ja kaut kas cits.
 
= IF (B2> = 90, "Izcils", IF (B2> = 75, "Labs", "Slikts"))
 
 
Tagad mēs esam paplašinājuši savu formulu, pārsniedzot tikai vienu IF funkciju. Ja nepieciešams, varat ievietot vairāk IF funkciju.
 
Ievērojiet divas aizvēršanas kronšteinas formulas beigās, katrai IF funkcijai.
 
Ir alternatīvas formulas, kas var būt tīrākas par šo ligzdoto IF pieeju. Viena ļoti noderīga alternatīva ir SWITCH funkcija programmā Excel.
 
AND un OR loģiskās funkcijas
 
AND un OR funkcijas tiek izmantotas, ja vēlaties veikt vairāk nekā vienu salīdzinājumu jūsu formulā. Tikai IF funkcija var apstrādāt tikai vienu stāvokli vai salīdzinājumu.
 
Veikt piemēru, kur mēs diskontējam vērtību par 10%, atkarībā no klienta tērētās summas un cik gadus viņi ir bijuši klienti.
 
AND un OR funkcijas atsevišėi atgriezīs TRUE vai FALSE vērtību.
 
AND funkcija atgriež TRUE tikai tad, ja ir izpildīts katrs nosacījums, un citādi atgriež FALSE. OR funkcija atgriež TRUE, ja ir izpildīti viens vai visi nosacījumi, un atgriež FALSE tikai tad, ja nav izpildīti nekādi nosacījumi.
 
Šīs funkcijas var pārbaudīt līdz 255 apstākļiem, tāpēc noteikti neaprobežojas tikai ar diviem nosacījumiem, kas šeit parādīti.
 
Zemāk ir UN un OR funkciju struktūra. Tie ir rakstīti vienādi. Vienkārši aizvietojiet vārdu UN VAI OR. Tas ir tikai viņu loģika, kas atšķiras.
 
= UN (loģiski1, [loģiski2]…)
 
Redzēsim abu piemēru, novērtējot divus nosacījumus.
 
UN Funkcijas piemērs
 
UN funkcija tiek izmantota tālāk, lai pārbaudītu, vai klients tērē vismaz £ 3000 un ir bijis klients vismaz trīs gadus.
 
= UN (B2> = 3000, C2> = 3)
 
 
Jūs varat redzēt, ka tas atgriež FALSE par Matt un Terry, jo, lai gan abi tie atbilst vienam no kritērijiem, viņiem ir jāatbilst gan AND, gan funkciju.
 
VAI Funkcijas piemērs
 
OR funkcija tiek izmantota tālāk, lai pārbaudītu, vai klients tērē vismaz £ 3000 vai ir bijis klients vismaz trīs gadus.
 
= OR (B2> = 3000, C2> = 3)
 
 
Šajā piemērā formula atgriež TRUE par Matt un Terry. Tikai Julie un Gillian neizpilda abus nosacījumus un atgriež FALSE vērtību.
 
Izmantojot AND un OR ar IF funkciju
 
Tā kā AND un OR funkcijas atgriež TRUE vai FALSE vērtību, ja to lieto atsevišķi, tas ir reti lietot tos paši.
 
Tā vietā jūs parasti izmantojat to ar IF funkciju vai Excel funkciju, piemēram, nosacījuma formatēšanu vai datu validāciju, lai veiktu dažas retrospektīvas darbības, ja formula novērtē TRUE.
 
Turpmāk sniegtajā formulā AND funkcija ir ievietota IF funkcijas loģiskajā testā. Ja AND funkcija atgriež TRUE, tad 10% tiek diskontēts no B slejas summas; pretējā gadījumā netiek dota atlaide un B slejas vērtība tiek atkārtota D slejā.
 
= IF (UN (B2> = 3000, C2> 3), B2 * 90%, B2)
 
 
XOR funkcija
 
Papildus OR funkcijai ir arī ekskluzīva OR funkcija. To sauc par XOR funkciju. XOR funkcija tika ieviesta ar Excel 2013 versiju.
 
Šī funkcija var veltīt dažas pūles, lai saprastu, tāpēc parādīts praktisks piemērs.
 
XOR funkcijas struktūra ir tāda pati kā OR funkcija.
 
= XOR (loģiski1, [loģiski2]…)
 
Novērtējot tikai divus nosacījumus, XOR funkcija atgriežas:
 
 
TRUE, ja kāds no šiem apstākļiem novērtē TRUE.
 
FALSE, ja abi nosacījumi ir TRUE, vai neviens no nosacījumiem nav īsts.
 
 
Tas atšķiras no OR funkcijas, jo tas atgūtu TRUE, ja abi apstākļi būtu TRUE.
 
Šī funkcija kļūst nedaudz mulsāka, ja tiek pievienoti vairāki nosacījumi. Pēc tam XOR funkcija atgriežas:
 
 
TRUE, ja nepāra nosacījumu skaits atgriežas TRUE.
 
FALSE, ja pat nosacījumu skaits ir TRUE, vai, ja viss apstākļi ir FALSE.
 
 
Apskatīsim vienkāršu XOR funkcijas piemēru.
 
Šajā piemērā pārdošanas apjomi ir sadalīti divās pusēs gadā. Ja pārdevējs abās pusēs pārdod £ 3000 vai vairāk, tad tiem tiek piešķirts zelta standarts. Tas tiek panākts ar funkciju AND un ar IF, tāpat kā iepriekš.
 
Bet, ja viņi pārdod £ 3000 vai vairāk vienā pusē, tad mēs vēlamies piešķirt viņiem sudraba statusu. Ja viņi nepārdod £ 3000 vai vairāk abās, tad nekas.
 
XOR funkcija ir ideāli piemērota šai loģikai. Turpmāk norādītā formula ir ievadīta E slejā un parāda XOR funkciju ar IF, lai parādītu “Jā” vai “Nē” tikai tad, ja kāds no nosacījumiem ir izpildīts.
 
= IF (XOR (B2> = 3000, C2> = 3000), "Jā", "Nē")
 
 
NAV funkcija
 
Galīgā loģiskā funkcija, kas jāapspriež šajā rakstā, ir NAV funkcija, un mēs esam atstājuši visvienkāršāko pēdējo. Lai gan dažreiz var būt grūti saskatīt funkcijas “reālās pasaules” izmantošanu.
 
NOT funkcija atceļ tās argumenta vērtību. Tātad, ja loģiskā vērtība ir TRUE, tad tā atgriež FALSE. Un, ja loģiskā vērtība ir FALSE, tā atgriezīsies TRUE.
 
To būs vieglāk izskaidrot ar dažiem piemēriem.
 
NOT funkcijas struktūra;
 
= NAV (loģiski)
 
NAV Funkcijas piemērs 1
 
Šajā piemērā iedomājieties, ka mums ir galvenais birojs Londonā un pēc tam daudzās citās reģionālajās vietās. Mēs vēlamies parādīt vārdu „jā”, ja vietne ir kaut kas, izņemot Londonu, un “Nē”, ja tas ir Londona.
 
NAV funkcija ir iestrādāta zemāk redzamās IF funkcijas loģiskajā pārbaudē, lai mainītu TRUE rezultātu.
 
= IF (NAV (B2 = "Londona"), "Jā", "Nē")
 
 
To var panākt arī, izmantojot NAV loģisko operatoru. Zemāk ir piemērs.
 
= IF (B2 "Londona", "Jā", "Nē")
 
NĒ Funkcijas 2. piemērs
 
NOT funkcija ir noderīga, strādājot ar informācijas funkcijām programmā Excel. Šīs ir Excel funkciju grupa, kas pārbauda kaut ko un atgriež TRUE, ja pārbaude ir veiksmīga, un FALSE, ja tā nav.
 
Piemēram, funkcija ISTEXT pārbaudīs, vai šūnā ir teksts un atgriežas TRUE, ja tas notiek, un FALSE, ja tā nav. NAV funkcija ir noderīga, jo tā var mainīt šo funkciju rezultātu.
 
Turpmāk sniegtajā piemērā mēs vēlamies maksāt pārdevējam 5% no summas, ko viņi maksā. Bet, ja viņi neko nedarīja, šūnā atrodas vārds “Neviens”, un tas radīs kļūdas formulā.
 
Funkcija ISTEXT tiek izmantota, lai pārbaudītu teksta esamību. Tas atgriež TRUE, ja ir teksts, tāpēc NOT funkcija to maina uz FALSE. Un IF veic aprēķinu.
 
= IF (NAV (ISTEXT (B2)), B2 * 5%, 0)
 
 
Loģisko funkciju apgūšana dos jums lielu priekšrocību kā Excel lietotājam. Lai varētu pārbaudīt un salīdzināt šūnu vērtības un veikt dažādas darbības, pamatojoties uz šiem rezultātiem, ir ļoti noderīga.
 
 
Šajā rakstā ir apskatītas labākās loģiskās funkcijas, ko izmanto šodien. Jaunākās Excel versijas ir parādījušas, ka šai bibliotēkai ir pievienotas vairākas funkcijas, piemēram, šajā rakstā minētā XOR funkcija. Atjauninot šos jaunos papildinājumus, jūs saglabāsiet priekšā pūļa.