3.5 Использование формул для принятия решений

Логические функции ЕСЛИ, И, ИЛИ, включаемые в формулы, позволяют формировать результаты, зависящие от некоторых условий. Так, с помощью функции ЕСЛИ можно выбрать один из двух возможных вариантов решения.

Формат функции ЕСЛИ

ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь).

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

= ЕСЛИ (В45>60; B36*3;”Значение меньше допустимого”.

В ячейке, содержащей приведенную формулу, будет выводиться результат перемножения значения, содержащегося в ячейке В45 на число 3 (если в ячейке В45 находится число > 60), или сообщение «Значение меньше допустимого (если в ячейке В45 находится число < 60).

Формула =ЕСЛИ (D9<25;F6;0) в качестве значения_если_истина содержит адрес ячейки F6, а в качестве значения_если_ложь – ноль. Если требуется предотвратить вывод на экран нулевых значений, необходимо выполнить команду Сервис / Параметры и в появившемся диалоговом окне на закладке Вид убрать флажок Нулевые значения.

Избавиться от изображения нуля можно было и записав формулу следующим образом

=ЕСЛИ (D9<25;F6;””).

Более сложные условия можно задавать путем объединения нескольких логических функций в одной формуле. Например

= ЕСЛИ (И(D7>40;D7<60); C7;0)

Функция И возвращает значение ИСТИНА только в том случае, когда все ее аргументы истинны. Таким образом, когда значения в ячейке 40<D7<60, функция И вернет значение ИСТИНА, а функция ЕСЛИ вернет значение, хранящееся в ячейке С7. Т.е. функцией И удобно пользоваться при проверке принадлежности значения какому-либо диапазону.

Для сравнения одного значения с несколькими удобно применять функцию ИЛИ. Формула

= ЕСЛИ (ИЛИ(В15=42;В15= «Текст»);ОК;””)

работает следующим образом. Если в ячейке В15 находится число 42 или текст «Текст», результатом работы формулы будет текст ОК. В противном случае формула вернет ячейке пустое значение.

3.6 Обработка массивов

Массив – прямоугольный диапазон значений или формул, которые Excel обрабатывает как единую группу. Результатом выполнения формул и функций массивов может быть массив или одно число.

Применение массивов позволяет выполнять большие объемы вычислений, занимая при этом небольшие объемы памяти ПЭВМ благодаря тому, что Excel хранит в памяти массив формул как одну формулу.

Ввод формулы массива избавляет пользователя от необходимости вводить или копировать повторяющуюся формулу в каждую ячейку диапазона и в результате уменьшить трудоемкость программирования.

На рис. 21 показаны две таблицы вычислений. Первая создана с использованием обычных вычислений, для расчета стоимости в каждой ячейке диапазона E4:E9 введены отдельные формулы =C4*D4, C5*D5, …

Для формирования второй таблицы, работающей с использованием формулы массива, необходимо:

- выделить диапазон ячеек, которые рассчитываются с помощью формулы массива E14:E19;

- ввести формулу массива, с указанием используемых диапазонов ячеек

= C14:C19*D14:D19;

- для ввода формулы массива нажать клавиши <Shift> + <Ctrl> + <Enter>.

Формула, выведенная в строке формул для ячейки Е14, заключена в фигурные скобки. Это означает, что данная формула является формулой массива, а диапазон ячеек следует рассматривать как единый объект. Т.е. нельзя вставлять ячейки или столбцы в данный диапазон массива, удалять или редактировать отдельные ячейки внутри диапазона. Массив можно изменять только как единое целое.

Пример использование формулы массива для вывода массива результатов приведен на рис. 21 для вычисления стоимости в ячейках Н14:Н19. Для этого нужно было выделить соответствующий диапазон ячеек, предназначенный для хранения результатов, ввести формулу массива, = C14:C19*D14:D19, указав, в каком диапазоне она работает, и нажать клавиши <Shift> + <Ctrl> + <Enter>.

Проверить допущенные в формулах ошибки можно командой Сервис / Проверка наличия ошибок. Параметры режима проверки при необходимости следует уточнить, выполнив команду Сервис /Параметры, после чего на закладке Проверка ошибок при помощи флажков откорректировать необходимые опции.

Рис. 21

Еще одно полезное для отладки формул средство, имеющееся в Excel – графическое отображение связей участвующих в вычислениях ячеек. Команда Сервис / Зависимости формул позволяет проверить влияющие на результат ячейки, зависимые ячейки, посмотреть источник ошибок, вычислить формулу, показать окно контрольного значения и т.д.