Домашняя страница Undo Do Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Статьи об Excel

МЕНЮ САЙТА
  • 1
  • 2
  • 3

КАТЕГОРИИ РАЗДЕЛА

ОПРОСЫ
Какой версией Excel Вы пользуетесь?
Всего ответов: 57051
Главная » Статьи » Эффективная работа в Excel » Приёмы работы с формулами

Если у вас слишком много ЕСЛИ)

Для примера возьмем некую таблицу премирования условного продавца/токаря/менеджера - на ваш выбор

 

1. План\факт и итоговый % премирования:

 

2. Шкала % премирования в зависимости от % выполнения плана:

 

Как мы видим, условия довольно просты: каждые 10% выполнения плана соответствуют своему % премирования, который не является линейным:

 

Исходные данные определены, переходим к главному вопросу: Как написать формулу с 26-ю условиями?

Помимо функции ЕСЛИ() нам понадобится ещё одна функция из категории логических - функция И(). Начинаем с первой строки: 

=ЕСЛИ(И(C2>=F2;C2<G2);H2)

На человеческом языке эта формула значит следующее: Если % выполнения плана (С2) больше или равен нулю (F2) и, в то же время, % выполнения плана (С2) меньше 10 (G2) , то результат равен 1% (H2).

Теперь не составит труда написать аналогичную формулу для следующей строки:

=ЕСЛИ(И(C2>=F3;C2<G3);H3)

и вложить одну формулу в другую:

=ЕСЛИ(И(C2>=F2;C2<G2);H2;ЕСЛИ(И(C2>=F3;C2<G3);H3)

Далее мы "тиражируем" уже написанную часть формулы 12 раз (для каждой строки) и вкладываем их друг в друга. В итоге получается такая формула:

=ЕСЛИ(И(C2>=F2;C2<G2);H2;ЕСЛИ(И(C2>=F3;C2<G3);H3;ЕСЛИ(И(C2>=F4;C2<G4);H4;ЕСЛИ(И(C2>=F5;C2<G5);H5;ЕСЛИ(И(C2>=F6;C2<G6);H6;ЕСЛИ(И(C2>=F7;C2<G7);H7;ЕСЛИ(И(C2>=F8;C2<G8);H8;ЕСЛИ(И(C2>=F9;C2<G9);H9;ЕСЛИ(И(C2>=F10;C2<G10);H10;ЕСЛИ(И(C2>=F11;C2<G11);H11;ЕСЛИ(И(C2>=F12;C2<G12);H12;ЕСЛИ(И(C2>=F13;C2<G13);H13;ЕСЛИ(И(C2>=F14;C2<G14);H14)))))))))))))  

Примечание: Если Вы работает с файлами, имеющими трехбуквенные расширениями (например .xls), то на седьмом вложении Вы увидите следующее сообщение:

Что-бы избежать появления этого сообщения, необходимо сохранить файл с четырехбуквенным расширением (я, обычно, использую расширение .xlsb), закрыть файл, открыть заново и продолжить писать формулу. Количество вложений функций в четырехбуквенных форматах составляет 128 (для Excel 2007 - 64).

 

Итак, формула написана и даже работает)

Однако на её написание ушло около трех минут и она, при этом, получилась громоздкой (339 знаков) и неудобной для редактирования, поскольку вложенные если визуально сложно отделять друг от друга. Конечно, можно записать формулу с переводом строк (см. Как сделать визуально наглядной большую формулу?) и это добавит "читаемости":

=ЕСЛИ(И(C2>=F2;C2<G2);H2;
ЕСЛИ(И(C2>=F3;C2<G3);H3;
ЕСЛИ(И(C2>=F4;C2<G4);H4;
ЕСЛИ(И(C2>=F5;C2<G5);H5;
ЕСЛИ(И(C2>=F6;C2<G6);H6;
ЕСЛИ(И(C2>=F7;C2<G7);H7;
ЕСЛИ(И(C2>=F8;C2<G8);H8;
ЕСЛИ(И(C2>=F9;C2<G9);H9;
ЕСЛИ(И(C2>=F10;C2<G10);H10;
ЕСЛИ(И(C2>=F11;C2<G11);H11;
ЕСЛИ(И(C2>=F12;C2<G12);H12;
ЕСЛИ(И(C2>=F13;C2<G13);H13;
ЕСЛИ(И(C2>=F14;C2<G14);H14
))))))))))))) 

Но, к сожалению, такая запись не добавит функциональности. Например, в том случае, если прибавятся новые условия, то их необходимо будет вручную прописывать в формуле. А если таблица содержит не 26 условий, а в десять раз больше, то и на написание такой формулы будет уходить не три минуты, а все полчаса, что, разумеется, непродуктивно и, значит, самое время задуматься об альтернативе.

Самый нетрудоемкий и простой вариант - это использование функции ВПР(). Формула с этой функцией будет выглядеть так:

=ВПР(C2;F2:H14;3)

Как ни странно, такая формула, которую можно написать за несколько секунд для ЛЮБОГО количества условий (вплоть до миллиона, умещающегося на листе Excel) будет делать ровно тоже самое, как и любое количество вложенных ЕСЛИ() в нашем примере. Причем формула с функцией ВПР() содержит всего 16 знаков.

 

Как это работает?

Всё довольно просто: функция ВПР(), при опущенном своем аргументе 4 - Интервальный_просмотр ищет поиском нестрогое совпадение своего аргумента 1 - Искомое_значение в первом столбце диапазона, заданном в аргументе 2 - Таблица и возвращает значение из столбца указанного в аргументе 3  - Номер_столбца. Звучит немного устрашающе, но на практике, пользоваться этой функцией легко.

 

Разберем как функция ВПР() производит вычисления на нашем примере:

% выполнения плана 1 = 100. В таблице условий премирования это значение находится в ячейке F12 (двенадцатая строка). В диапазоне 2 F2:H14  третий номер столбца 3  - это столбец H. На двенадцатой строке в третьем столбце значение 57,76. Именно его и возвратит формула при 100% выполнения плана.

 

Но, 100% выполнения плана есть в таблице, а как быть, если % выполнения плана, скажем, 99?

Вот тут-то и начинает играть роль аргумент 4 - Интервальный_просмотр. Если этот аргумент оставить пустым, написать в нем цифру 1 или ввести логическое (булево) значение ИСТИНА (все эти три варианта абсолютно равнозначны), то в этом случае функция будет искать в первом столбце таблицы, внимание (!), НАИБОЛЬШЕЕ ЗНАЧЕНИЕ, КОТОРОЕ МЕНЬШЕ ИЛИ РАВНО ИСКОМОМУ. Таким образом, функция ВПР() остановит свой поиск на ячейке F11, поскольку значение 90%, которое находится в этой ячейке, является наибольшим значением, которое меньше искомого 1 99%. Ну а далее - мы уже знаем. Найденная ячейка F11 находится на одиннадцатой строке, В диапазоне 2 F2:H14  третий номер столбца 3  - это столбец H. На одиннадцатой строке в третьем столбце диапазона значение 38,44. Именно его и возвратит формула при 99% выполнения плана. 

 

Ценность формулы с функцией ВПР(), помимо краткости записи, заключается ещё и в том, что при добавлении условий в таблицу достаточно просто увеличить диапазон аргумента 2 - Таблица. Например, если мы изменим исходные данные с 26 до 260 условий, то формула из такой:

=ВПР(C2;F2:H14;3)

изменится на такую:

=ВПР(C2;F2:H140;3)

При этом на изменения в формуле уйдет не полчаса, как в случае со вложенными ЕСЛИ(), а всего пара-тройка секунд. Так же хочу обратить внимание, на то, что при использовании формулы с функцией ВПР() второй столбец таблицы становится ненужным (поиск идет только по первому столбцу), его можно просто скрыть, очистить или даже удалить (но в этом случае в аргументе 3  - Номер_столбца надо будет изменить значение 3 на 2, т. к в таблице останутся только два столбца).

 

В Excel многие задачи можно решить разными способами. И далеко не всегда этих способов один-два. И данный случай - не исключение. Выше я уже привел два варианта решения, но есть и другие. Приведу ещё два варианта решения формулами:

=ПРОСМОТР(C2;F2:F14;H2:H14)
=ИНДЕКС(H2:H14;ПОИСКПОЗ(C2;F2:F14))

Разбирать в этой статье мы их не будем, они, фактически, работают по тому же принципу, что и вариант формулы с функцией ВПР().

 

Категория: Приёмы работы с формулами | Добавил: Serge_007 (05.09.2023) W
Просмотров: 707 | Рейтинг: 4.5/2


Всего комментариев: 0
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!