IF функция – вложени формули и избягване на капани

Забележки

Въпреки че Excel ще ви позволи да вложите до 64 различни функции IF, изобщо не е препоръчително да го правите. Защо?

• Използването на няколко инструкции IF изисква изключително добро обмисляне, за да ги създадете правилно и да сте сигурни, че тяхната логика е способна да изчисли правилно всяко едно условие от началото до края. Ако формулата ви не е вложена 100% правилно, тя може да работи правилно в 75% от времето, но в 25% от времето да връща неочаквани резултати. За съжаление, шансовете да откриете тези 25% са незначителни.

• Множеството инструкции IF може да станат изключително трудни за поддръжка, особено когато след време се върнете към тях и се опитате да разберете какво сте искали да направите, а още по-лошо – ако ги е писал някой друг.

Ако установите, че сте с инструкция IF, която изглежда, че расте без край, е време да оставите мишката и да преосмислите стратегията си.

Нека да видим как правилно да създадете сложна вложена инструкция IF, като използвате няколко IFs, и кога да разберете, че е време да използвате друг инструмент в арсенала на Excel.

Примери

Следва пример за една относително стандартна вложена инструкция IF за конвертиране на резултатите от тестовете на ученици или студенти в еквивалентните им буквени оценки.

• =IF(D2>89;"A";IF(D2>79;"B";IF(D2>69;"C";IF(D2>59;"D";"F"))))

  Тази сложни вложена инструкция IF следва ясна логика:

1. Ако резултатът от теста (в клетка D2) е по-голям от 89, учащият получава оценка A

2. Ако резултатът от теста е по-голям от 79, учащият получава оценка B

3. Ако резултатът от теста е по-голям от 69, учащият получава оценка C

4. Ако резултатът от теста е по-голям от 59, учащият получава оценка D

5. В противен случай учащият получава оценка F

Този конкретен пример е сравнително безопасен, защото не е вероятно връзката между оценките от тестовете и буквените оценки да се промени, така че няма да изисква много поддръжка. Но ето една мисъл – какво ще стане, ако трябва да разделите оценките между A+, A и A- (и т.ч.)? Сега вашата инструкция IF с 4 условия трябва да се напише отново, така че да има 12 условия! Ето как би изглеждала формулата сега:

• =IF(B2>97;"A+";IF(B2>93;"A";IF(B2>89;"A-";IF(B2>87;"B+";IF(B2>83;"B";IF(B2>79;"B-"; IF(B2>77;"C+";IF(B2>73;"C";IF(B2>69;"C-";IF(B2>57;"D+";IF(B2>53;"D";IF(B2>49;"D-";"F"))))))))))))

Той все още е функционално точен и ще работи според очакванията, но е необходимо много време, за да се пише и по-дълго време да се тества, за да се уверите, че прави това, което искате. Друг похвален проблем е, че трябва да въведете резултатите и еквивалентните буквени оценки на ръка. Какви са шансовете случайно да имате правописна грешка? А сега си представете, че трябва да направите това 64 пъти с по-сложни условия! Разбира се, възможно е, но наистина ли искате да се подложите на този вид усилия и вероятни грешки, които ще бъдат наистина трудни за откриване?

Още примери

Следното е много често срещан пример за изчисляване на комисионата за продажби на база нивата на реализираните приходи.

• =IF(C9>15000;20%;IF(C9>12500;17,5%;IF(C9>10000;15%;IF(C9>7500;12,5%;IF(C9>5000;10%;0)))))

Тази формула казва: АКО (C9 е по-голямо от 15 000, тогава върни 20%, АКО (C9 е по-голямо от 12 500, тогава върни 17,5% и т.н.

Въпреки че е много подобна на предишния пример с оценките, тази формула е чудесен пример колко трудно може да бъде поддържането на големи инструкции IF – какво ще трябва да направите, ако вашата организация реши да добави нови нива на възнагражденията и дори да промени съществуващите стойности в долари или проценти? Ще имате много работа по ръцете си!

Ето един пример със сценарий за комисиона с невярна логика:

Виждате ли какво не е наред? Сравнете последователността на сравненията на приходите с предишния пример. В каква посока върви тази последователност? Точно така, от долу нагоре (от 5000 лв. до 15 000 лв.), а не обратно. Защо това е толкова голям проблем? Голяма работа е, защото формулата не може да премине първата оценка за никаква стойност над 5000 лв. Да речем, че имате приходи от 12 500 лв. – инструкцията IF ще върне 10%, защото е по-голяма от 5000 лв. и ще спре дотук. Това може да е невероятно проблемно, защото в много ситуации тези типове грешки не се отлагат, докато не окажат отрицателно въздействие. След като знаете, че сложните вложени инструкции IF крият сериозни опасности, какво можете да направите? В повечето случаи можете да използвате функцията VLOOKUP, вместо да създавате сложна формула с функцията IF. Когато използвате VLOOKUP, трябва първо да създадете референтна таблица:

• =VLOOKUP(C2;C5:D17;2;TRUE)

Тази формула търси стойността в C2 в диапазона C5:C17. Ако стойността бъде намерена, връща съответната стойността от същия ред в колона D.

• =VLOOKUP(B9,B2:C6,2,TRUE)

Подобно на горното, тази формула търси стойността в клетка B9 в диапазона B2:B22. Ако стойността бъде намерена, връща съответната стойността от същия ред в колона C.

Функцията VLOOKUP е описана много по-подробно тук, но със сигурност е много по-лесна от сложната вложена инструкция IF на 12 нива! Има и други, по-малко очевидни предимства:

• Референтните таблици на VLOOKUP са достъпни и лесно се виждат.

• Стойностите в таблицата лесно могат да се актуализират и никога няма да се налага да променяте формулата, ако условията ви се променят.

• Ако не искате хората да виждат или да пречат на вашата референтна таблица, просто я поставете в друг работен лист.

Знаете ли, че...?

Вече има и функция IFS, която може да замени множество вложени инструкции IF с една-единствена функция. Така че вместо първия пример за оценките, който има 4 вложени функции IF:

• =IF(D2>89;"A";IF(D2>79;"B";IF(D2>69;"C";IF(D2>59;"D";"F"))))

Нещата могат значително да се опростят с една-единствена функция IFS:

• =IFS(D2>89;"A";D2>79;"B";D2>69;"C";D2>59;"D";TRUE;"F")

Функцията IFS е чудесна, защото не е нужно да се безпокоите за всички тези инструкции IF и скоби.

Имате нужда от още помощ?

Винаги можете да попитате експерт в техническата общност на Excel или да получите поддръжка в Общността за отговори от.