Kā lietot vairākus IF paziņojumus programmā Microsoft Excel

IF priekšraksts programmā Excel izpilda loģisku pārbaudi, kas atgriež vienu vērtību, ja nosacījums ir izpildīts, un citu vērtību, ja tas nav izpildīts. Izmantojot vienu Excel IF priekšrakstu, var veikt tikai pamata darbības, bet, ja ligzdot vairākus IF priekšrakstus, varat veikt sarežģītus uzdevumus. Vienīgais mīnuss ir tas, ka, veidojot formulu, jābūt uzmanīgiem, lai izvairītos no kļūdām. Alternatīvas Excel funkcijas var veikt to pašu darbu ar mazāku kļūdu risku. Apgūstiet ligzdoto IF priekšrakstu pamatus programmā Excel un alternatīvas, lai atrastu savām vajadzībām vislabāko pieeju.

Funkcija IF programmā Excel būtībā saka: "ja tā ir patiesa, atgriež x vērtību, bet, ja tā nav, atgriež y vērtību." Piemēram, ja jūs pārbaudītu, vai jūsu uzņēmumā ir dažādas nodaļas bija zem budžeta vai pārsniedza budžetu, varat salīdzināt viņu izdevumus ar viņu budžetiem, izmantojot funkciju un atgriezt “Pārsniedz budžetu”, ja nodaļa iztērēja pārāk daudz, vai “Budžeta ietvaros”, ja tā nebija. Vispārīgi runājot, jūs izmantojat salīdzināšanas operatorus, lai izveidotu paziņojumu, piemēram:

• = (vienāds ar)

• (varenāks, kā)

• = (lielāks par vai vienāds ar)

• < (mazāks par)
• <= (mazāks par vai vienāds ar)
• <> (nav vienāds ar)

Izpratne par Excel IF priekšraksta sintakse ir būtisks solis, lai iemācītos to pareizi lietot. Funkcijas formāts ir IF(loģisks_tests, vērtība_ja_patiesa, [vērtība_ja_false]) kur kvadrātiekavas norāda, ka pēdējais arguments nav obligāts. Tos ir viegli interpretēt: logical_test nozīmē apgalvojumu, kuru vēlaties pārbaudīt, vērtība_ja_patiesa ir vieta, kur vēlaties, lai programma Excel parādītu, ja tests ir patiess, un [vērtība_ja_false] ir izvēles vieta tam, ko vēlaties rādīt programmā Excel, ja tests ir nepatiess.

Tas ir pietiekami, lai jūs varētu izmantot Excel formulu ja-tad priekšrakstiem, ko var demonstrēt, izmantojot piemēru "Pārsniedz budžetu/budžeta ietvaros". Iedomājieties, ka jums B slejā ir dažādu departamentu iztērētā summa un C slejā budžetā paredzētā summa ar vērtībām, kas sākas otrajā rindā. Šūnā D2 ir jāievada =IF(B2>C2, "Pārsniedz budžetu", "Budžeta ietvaros") lai atgrieztu meklēto rezultātu (ņemot vērā, ka ir jāizmanto pēdiņas, lai tas atgrieztu tekstu). Pirmajā daļā teikts "ja vērtība B2 ir lielāka par vērtību C2" (ja izdevumi ir lielāki par budžetu), otrajā daļā teikts "tad atgrieziet tekstu Pārsniedz budžetu un trešā daļa saka "ja nē, tad atgriezieties Budžeta ietvaros."

Excel IF priekšrakstu rīka maksimāla izmantošana nozīmē iespēju apvienot vairākus IF priekšrakstus, kad jums ir tam piemērots uzdevums. Sākotnējā IF paziņojumā varat iekļaut otru IF priekšrakstu, tajā varat iekļaut citu IF paziņojumu un tā tālāk. Tas ir īpaši noderīgi, ja vēlaties kaut ko iedalīt vienā no vairākām grupām, izmantojot vienu formulu. Piemēram, varat izmantot ligzdotos IF priekšrakstus, lai studentu iegūtos rezultātus pārbaudē pārvērstu vērtējumā.

Lai pareizi izmantotu vairākus IF priekšrakstus, padomājiet par to, kādu loģiku prasāt programmai Excel. Būtībā pēc nosacījuma (pirmais funkcijas arguments) varat pievienot vēl vienu IF priekšrakstu vērtība_ja_patiesa vai vērtība_ja_false atstarpes, lai norādītu programmai Excel, kas jādara tālāk. Padomājiet par to kā par to, ka programma Excel pārbauda šūnas atbilstību otrajam kritērijam, ja attiecīgā šūna to nedara atbilst pirmajam kritērijam (vai tiešām, ja tas atbilst pirmajam), un jūs varat turpināt šo procesu ilgi laiks. Citiem vārdiem sakot, jūs pārvēršat vārdu "ja tas, tad tas" par "ja tas un šis, tad tas" vai "ja ne šis, bet šis, tad tas".

Iedomājieties, ka esat uzdevis studentiem testu, kurā 85 procenti vai augstāks rezultāts ir A — no 70 līdz 85 procentiem. ir B, no 55 līdz 70 procentiem ir C, no 40 procentiem līdz 55 procentiem ir D un viss zemāks ir Neizdevās. Rezultāti ir B kolonnā no 2. līdz 11. rindai. Varat izmantot rūpīgi izveidotu Excel IF funkciju ar vairākiem nosacījumiem, lai klasificētu studentu rezultātus.

Pirmā daļa ir vienkārša (šajā piemērā, lai iegūtu punktu skaitu B2): =IF(B2>=85, "A") liek programmai Excel atgriezt A, ja skolēns ieguvis 85 vai augstākus punktus. Ligzdošana ļauj pievienot citas atzīmes, izmantojot trešo argumentu (ko darīt, ja nosacījums ir nepatiess), lai pievienotu papildu nosacījumus: =IF(B2>=85, "A", IF(B2>=70, "B")), kas liek programmai Excel atgriezt A, ja rezultāts ir 85 vai vairāk, bet, ja nē, atgriezt B, ja rezultāts ir lielāks par 70. Turpiniet šādā veidā pabeigt formulu, katru reizi izmantojot trešo argumentu jaunam IF priekšrakstam: =IF(B2>=85, "A", IF(B2>=70, "B", IF(B2>=55, "C", IF(B2>=40, "D", "Neizdevās"))) ))

Tas izskatās sarežģīti, tāpēc to sadalīšanai vajadzētu nostiprināt ideju. Pirmie divi argumenti saka: "ja rezultāts B2 ir 85 vai vairāk, tad skolēns saņem A", bet trešais saka: "pretējā gadījumā pārbaudiet šādu nosacījumu." The Otrais IF noved pie šī apgalvojuma: "(skolēniem, kuru vērtējums ir mazāks par 85), ja B2 rezultāts ir 70 vai augstāks, students saņem B, un, ja ne, pārbaudiet Trešais IF saka: "(skolēniem, kuru vērtējums ir mazāks par 70), ja B2 rezultāts ir 55 vai augstāks, students, kas ieguvis punktus, saņem C, un, ja nē, pārbaudiet šāds nosacījums" un ceturtais saka: "(skolēniem, kuru vērtējums ir mazāks par 55), ja B2 vērtējums ir lielāks par 40, students saņem D, ja nē, students neizdodas."

Ja pareizi ievadāt IF priekšraksta formulu, tas atgriezīs vienu no programmā Excel sniegtajām opcijām. Tomēr, ja pieļaujat kļūdu, formula var atgriezt a 0 vai #NAME? šūnā, kurā ir jūsu formula.

A 0 kļūda nozīmē, ka vai nu vērtība_ja_patiesa vai vērtība_ja_false arguments ir tukšs. Novērsiet šo problēmu, nodrošinot, ka vērtība tiek ievadīta abos argumentos vai vismaz vērtība_ja_patiesa arguments. The vērtība_ja_false argumentu var pilnībā noņemt, un tas atgriezīs FALSE, ja neliekat otru komatu aiz vērtība_ja_patiesa lauks.

A #NAME? kļūda norāda, ka, iespējams, esat kaut ko nepareizi uzrakstījis formulā. To varētu būt nedaudz grūtāk novērst, jo tā var būt jebkura formulas daļa, taču vislabākais padoms ir vēlreiz pārbaudīt visu vai vēlreiz izrakstīt formulu.

Iepriekšējā piemērā ligzdotās IF funkcijas formula kļuva diezgan gara, kas ir viens no galvenajiem negatīvajiem aspektiem, lietojot vairākus IF priekšrakstus programmā Excel. Tie var kļūt sarežģīti, un ir viegli kļūdīties, jo jums rūpīgi jāievēro loģika, lai pārliecinātos, ka viss darbojas tā, kā jūs plānojat. Microsoft norāda, ka nepareizi ligzdotas formulas var pat sniegt pareizo rezultātu lielāko daļu laika, bet nepareizus rezultātus retos gadījumos. Tos ir grūti pamanīt.

Nosacījumu nesakārtošana pareizajā secībā ir izplatīta kļūda. Novērtēto testu piemērā, ja sākāt ar zemāku punktu skaitu =IF(B2>=40, "D",…" un turpināja uzskaitīt pārējos augošā secībā, pat 100 punktu skaits atgrieztu D. Tas notiek tāpēc, ka vispirms tiek pārbaudīts pirmais nosacījums, tādēļ, ja sākāt ar "ja šūna B2 ir 40 vai lielāka, atgrieziet D", tas klasificētu jebkuru punktu skaitu, kas ir 40 vai vairāk, kā D, nepārbaudot neko citu, jo tas atbilst pirmajam stāvokli. Šis ir lielisks piemērs tam, kāpēc jums ir jāievēro formulas loģika.

Jums arī jāpārliecinās, ka vairāku IF priekšrakstu beigās pievienojat pareizo iekavu skaitu, lai pareizi aizvērtu formulu. Programma Excel palīdz to paveikt, darba laikā koordinējot krāsas un izceļot iekavas.

Tehniski vienā formulā var ievietot līdz 64 IF funkcijām, taču Microsoft neiesaka izmantot šādu funkciju iepriekš minēto iemeslu dēļ. Lietas ātri kļūst sarežģītas, un pastāv iespēja, ka, ja mēģināt ligzdot vairāk nekā 10 IF priekšrakstus, iespējams, Excel jau ir iekļauts labāks rīks darbam.

Ērtākā IF funkcijas alternatīva ir IFS funkcija, kas ir versija funkcija, kas veic tādu pašu uzdevumu kā ligzdošana vienā funkcijā un spēj apstrādāt līdz pat 127 nosacījumiem. IFS funkcijai ir vienkāršāka sintakse: =IFS(loģisks_tests1, vērtība_ja_patiesa1, [loģiskais_tests2, vērtība_ja_patiesa2], [loģiskais_tests3, vērtība_ja_patiesa3]…) un tā tālāk. Būtībā tas darbojas kā ligzdotie IF priekšraksti, izņemot tos, ka nākamie IF priekšraksti aizņem vietu aiz "vērtība, ja patiesa", bez nepieciešamības vēlreiz parādīt funkciju.

Pārbaudes atzīmes piemērā formulu var uzrakstīt daudz vienkāršāk ar funkciju IFS. Tā vietā:

=IF(B2>=85, "A", IF(B2>=70, "B", IF(B2>=55, "C", IF(B2>=40, "D", "Neizdevās"))) ))

vienkārši raksti

=IFS(B2>=85, "A", B2> = 70, "B", B2> = 55, "C", B2> = 40, "D", PATIESA, "Neizdevās")

The PATIESA vietā pirms pēdējās teikts: "ja neviena no pārējām vērtībām nav izpildīta, atgrieziet nākamo vērtību."

Šis līdzeklis ir pieejams tikai programmā Excel 2019 vai Excel darbam ar Office 365.

Funkcija VLOOKUP programmā Excel bieži ir vienkāršāka alternatīva vairākiem IF priekšrakstiem, un tā ir pieejama jebkurā Excel versijā, sākot no 2007. gada. Tas nav ideāli piemērots visām situācijām, jo ​​tas vai nu atgriež rezultātus precīzai atbilstībai, vai arī tuvākais numuru, tāpēc to nevarēja izmantot tādām atzīmēm kā iepriekšējā piemērā. Tomēr daudzās situācijās tas var ietaupīt jums daudz darba, uzrakstot formulu, lai gan jums ir jāveido tabula ar iespējamām vērtībām vienā sleju, piemēram, preces, ko pārdod jūsu veikals, un to, ko vēlaties, lai funkcija atgrieztu, piemēram, produkta cena, citā slejā, piemērs.

VLOOKUP sintakse ir =VLOOKUP(uzmeklēšanas_vērtība, tabulas_masīvs, kolonnas_indeksa_numurs, [diapazona_uzmeklēšana]) un katra komponenta apgūšana parāda, kā to izmantot. The uzmeklēšanas_vērtība arguments ir vērtība, kuru vēlaties pārbaudīt, un tai ir jābūt šūnai tajā pašā kolonnā, kurā atrodas tās tabulas sākums, pret kuru vēlaties to pārbaudīt, vai nu virs, vai zem tās. The tabula_masīvs ir šūnu diapazons, kurā ir jūsu informācijas tabula, piemēram, produkti un to atbilstošās cenas, un col_index_num ir kolonna, kurā Excel ir jāmeklē, lai atrastu vērtību, ar kuru jāatgriež 1 norādot galējo kreiso kolonnu, 2 norādot nākamo un tā tālāk. Visbeidzot, [diapazona_uzmeklēšana] ir neobligāts, bet ievadiet "TRUE", ja vēlaties, lai Excel balstītu atbildi uz tuvāko vērtību, vai "FALSE", ja vēlaties tikai precīzu atbilstību.