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

Во втором аргументе записывается выражение, которое бу­дет вычислено, если лог_выражение возвращает значение ИСТИНА, а в третьем аргументе — выражение, вычисляемое, если лог_выражение возвращает ЛОЖЬ. В языках программи­рования высокого уровня этой функции соответствует оператор

еслилогвыражениетодействие 1иначедействие 2

ПРИМЕР 2.10.

1. Введем в ячейку В8 формулу, которая возвращает z +1, если z >1, и 2 в противном случае: = ЕСЛИ(z>1,z+1,z). (В Мас­тере функций ЕСЛИ находится в категории "Логические", так же как функции И, ИЛИ, НЕ.);

2. Если z > 60, то в ячейке В9 выводить сообщение "Превы­шено пороговое значение", в противном случае выводить z:

=ЕСЛИ(z>60,"Превышено пороговое значение",z)

3. Если z Î [10,25], то возвращать z, если z <10, то возвращать 10, если z > 25, то возвращать 25. Сконструируем выражение (одно из возможных):

если z < 10 то 10 иначе (если z < 25 то z иначе 25).

Запишем формулу в С9:

=ЕСЛИ( z < 10, 10, ЕСЛИ( z <= 25, z, 25)) >

Упражнение 2.8. Что вернет функция ЕСЛИ, когда опущен третий аргумент функции, а условие в первом аргументе ложно? А когда опущен и второй аргумент?

ПРИМЕР 2.11.

Распространение ошибки в цепочке формул.На рабочем листе нередко располагаются цепочки формул: результат, воз­вращаемый одной формулой, является аргументом для другой формулы. Если исходные данные для первой формулы неподхо­дящие, то можно вывести в ячейке текстовое сообщение, но следующая формула может интерпретировать текстовую строку как 0, что не всегда нам подходит. Пусть в ячейку А31 пользо­ватель вводит число, которое не должно быть меньше 10, а в В31 любое число (допустим, 3). Формула в ячейке С31 возво­дит это число из A31 в квадрат:

= ЕСЛИ(А31>=10,А31^л2,"неверное число").

Формула в СЗЗ складывает содержимое В31 и С31:

=СУММ(В31,С31).

Если пользователь введет в А31 число 5, то в СЗЗ появится результат 3. Дело в том, что функция СУММ, вычисляющая сумму чисел, расположенных в блоке ячеек, считает текстовые значения равными 0.

Удобнее, чтобы формула возвращала ошибочное значение #Н/Д (Недоступно), если исходные данные для формулы не­подходящие. Тогда все формулы, использующие этот результат в качестве аргумента, также будут возвращать значение #Н/Д.

Исправить формулу в С31 можно двумя способами: исполь­зовать в качестве третьего аргумента функции ЕСЛИ функцию НД() (без аргументов), которая возвращает значение #Н/Д, либо прямо подставить в третий аргумент #Н/Д:

= ЕСЛИ(А31>=10,А31^Л2, #Н/Д).

Теперь формула в СЗЗ вернет значение #Н/Д. Если же вве­сти в А31 число 12, то в СЗЗ будет выведено 147. >

ПРИМЕР 2.12.

Запрет ввода недопустимого числа.В Excel 97 появилось новое средство для недопущения ввода пользователем "запре­щенных значений". Пусть в ячейку А41 пользователь может ввести число, большее или равное 10. Если он введет число, меньшее 10, должно последовать сообщение об ошибке.

Выделите ячейку А41. Выберите в меню команду "Данные/ Проверка". Появится диалоговое окно с тремя вкладками. На первой вкладке "Параметры" выберите "Тип данных" - "Дейст­вительные", "Значение" - "больше или равно", "Минимум" -10. На второй вкладке "Сообщение для ввода" установите фла­жок "Отображать подсказку, если ячейка является текущей", дайте "Заголовок:" - "Ввод числа" и "Сообщение:" - "Введите число, не меньшее 10". На вкладке "Сообщение об ошибке" ус­тановите флажок "Выводить сообщение об ошибке", укажите тип действий при ошибке "Вид:" - "Останов", введите "Заголо­вок:" - "Ввод числа", "Сообщение:" - "Вводимое число долж­но быть больше или равно 10!!!". Попробуйте ввести допусти­мое и недопустимое число. >

Указание. Перед вызовом меню "Данные/ Проверка" выделите диапазон А25:Е25.

Итак, мы изучили арифметические и логические выражения.

Пример проектирования расчетов на рабочем листе

Теперь мы достаточно подготовлены к решению конкретной задачи: нужно спроектировать рабочую книгу, которая будет вычислять различные элементы треугольника по трем его сто­ронам. Задачу будем решать поэтапно, исправляя некоторые не­удачные решения, как это обычно и происходит на практике. На этом примере мы освоим много возможностей Excel.

Специалистов, использующих Excel, можно условно разде­лить на два типа: проектировщик рабочей книги и пользователь рабочей книги. "Условно", потому что чаще всего проектиров­щик и пользователь — одно и то же лицо. Но даже в этом случае проектировщик должен думать об удобстве использования элек­тронной таблицы, о возможности ее дальнейшего развития, мо­дификации. Для этого нужно позаботиться об удобном распо­ложении исходных данных и результатов, о выдаче понятных сообщений в случае возникновения при расчетах "нештатных ситуаций". На следующем довольно громоздком примере мы постараемся проиллюстрировать эти положения.

ПРИМЕР 2.13.ВЫПОЛНИТЬ!

Вычислениеэлементов треугольника. Даны три стороны треугольника а, b, с. Требуется вычислить его площадь по фор­муле Герона , где р — полупериметр: , а также радиус вписанной окружности и радиус описанной окружности .

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

Переименуйте рабочий лист, дайте ему имя "Треугольник". Введите данные, как показано на рис. 2.7. В ячейку В6 введите формулу =(В2+ВЗ+В4)/2.

Рис. 2.7

Использование имен. В ячейку В8 нужно ввести формулу Герона. Чтобы упростить ввод, дадим имена ячейкам В2, ВЗ, В4, В6. Выделите блок А2:В6, выберите в меню команду "Формулы/ Определенные имена/ Создать из выделенного фрагмента".

Excel предложит вариант "в столбце слева", т.е. взять в качестве имен для ячеек В2, ВЗ, В4, В6 тек­стовые строки (в нашем случае однобуквенные), хранящиеся в ячейках А2, A3, А4, Аб. Нажмите "ОК". Теперь, выделяя ячейку В2, в окошке слева от строки ввода Вы увидите не адрес В2, а имя а. Для ячейки В4 имя не с, как можно было ожидать, а с_. Это связано с тем, что имена с и r в Excel зарезервированы (с - column - столбец, т - row - строка). Поэтому Excel ввел в имя символ подчеркивания.

Введите в В8 формулу =корень(р*(р-а)*(р-Ь)*(р-с_)). После нажатия Enter (или щелчка по зеленой галочке слева от строки ввода) название функции будет отображено прописными буква­ми. Это означает, что мы правильно набрали имя функции. Если бы не введенные имена, нам пришлось бы набрать формулу

=КОРЕНЬ(В6*(В6-В2)* (В6-ВЗ)* (В6-В4)),

что намного труднее для восприятия.

Форматирование ячеек. Итак, пользуясь таблицей, можно вычислить площадь треугольника. Но хотелось бы придать таб­лице более "читабельный" вид.

Выровняем названия величин по правому краю. Выделите блок А2:А8 и на панели "Форматирование" нажмите кнопку "По правому краю".

Введите длину стороны а, равную 2. Тогда S = 3.799671. Предположим, нам нужна точность три знака после точки. Для этого выделите В8 и несколько раз нажмите кнопку "Умень­шить разрядность" , пока число не приобретет нужный формат 3.800. Важно понимать, что "внутренние" вычисления выпол­няются с прежней точностью, но число, отображаемое в ячейке, округлено до трех десятичных знаков. Отмените форматирова­ние (Ctrl+Z) и испытайте другой способ: выберите в меню "Главная/ Ячейки/ Формат/ Формат ячеек… " (Ctrl+1), в диалоговом окне – вкладку "Чис­ло", в списке "Числовые форматы:" - "Числовой". Далее само­стоятельно разберитесь, как задать нужное количество разрядов.

"Развитие" таблицы. Дополним таблицу вычислением ра­диусов вписанной и описанной окружностей.

Создайте для ячейки В8 имя, взятое из соседней ячейки А8 (т.е. ячейка В8 должна получить имя S). Можно воспользовать­ся ранее освоенным приемом (выделить А8:В8 и "Формулы/ Определенные имена/ Создать из выделенного фрагмента"), но так как здесь всего одно имя, проще поступить так: выделите В8 и в окне имен над столбцом А (там сейчас ото­бражается адрес В8) введите имя S, нажмите Enter.

В ячейки D10 и F10 введите r и R, а в Е10 и G10 – соот­ветствующие формулы. Наложите на эти ячейки такие же фор­маты, как и ранее. Для этого воспользуйтесь кнопкой "Формат по образцу" (на ней изображена кисть). Например, выделите А8, нажмите кнопку и "покрасьте" кистью Е10.

У Вас должен получиться следующий результат (рис. 2.8).

Рис. 2.8

Исследование зависимостей. Выделите G10 и выберите в меню пункт "Формулы/ Зависимости формул/ Влияющие ячейки". На эк­ране протянутся синие стрелки от ячеек, содержащих длины сторон и площадь треугольника, к ячейке G10. Исследуйте зависимости и для других ячеек. Уберите стрелки соответствую­щей командой меню.

Задайте длину стороны а, равную 10. В ячейках с результа­тами появится сообщение об ошибке #ЧИСЛО!. Дело в том, что стороны 10, 4, 5 не образуют треугольника. При вычислении площади под корнем получается отрицательное число. Выдели­те ячейку G10 и выберите "Формулы/ Зависимости формул/ Источник ошибки"

Вы наглядно увидите, за счет каких влияющих ячеек по­лучен неверный результат. Уберите с экрана стрелки.

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

Будем вычислять отдельно подкоренное выражение р*(р-а)*(р-b)*(р-с_) и определять его знак. Если оно положительно, вычисляем S, R и r. Если же нет, то в ячейке В8 выведем тексто­вую строку "Это не треугольник!", а в ячейках Е10 и G10выве­дем пустые строки.

Перетащите мышью содержимое В8 в В7. Отредактируйте В7, убрав КОРЕНЬ. В ячейке останется формула =р*(р-а)*(р-b)*(p-c_).

В В8 разместим формулу

=ЕСЛИ(В7>0,КОРЕНЬ(В7),"Это не треугольник!").

В Е10 разместим формулу

=ЕСЛИ(В7>0,S/р,"").

Аналогично измените формулу в G10.

Скрытие строк. В 6-й и 7-й строках расположены результа­ты промежуточных вычислений, видеть которые пользователю таблицы ни к чему. Выделите на левой адресной полосе строки 6 и 7 и в контекстном меню выберите "Скрыть". Если Вы захо­тите вернуть эти строки на экран, выделите 5-ю и 8-ю строки и в контекстном меню выберите "Отобразить".

Аналогично можно скрывать и показывать столбцы. Поэкс­периментируйте.

Защита листа. Чтобы предохранить таблицу от непредна­меренной порчи неопытным пользователем (вдруг он попытает­ся задать радиус вписанной окружности и при этом уничтожит формулу), нужно защитить рабочий лист. Но сначала нужно "объявить беззащитными" ячейки с исходными данными.

Выделите ячейки, содержащие длины сторон (В2:В4), на­жмите Ctrl+1,выберите вкладку "Защита" и снимите флажок "Защищаемая ячейка". Выберите в меню команду "Рецензирование/ Изменения/ Защитить лист". Попробуйте теперь ввести данные вне диапазона В2:В4 и посмотрите реакцию Excel. Снимите защиту: " Рецензирование/ Изменения / Снять защиту листа".

Ограничение ввода. Разрешите пользова­телю вводить только положительные длины сторон треугольни­ка (пункт меню "Данные/ Работа с данными/Проверка данных").

Имитация печати. Выберите в меню пункт "Кнопка Office/Печать/Предва­рительный просмотр". Изучите назначение кнопок в окне пред­варительного просмотра. Нажмите кнопку "Закрыть". Рабочий лист разбит пунктирными линиями на прямоугольники, соот­ветствующие листам формата А4.

Подбор параметра. Итак, мы вычислили радиус описанной окружности R по трем сторонам треугольника а, b, с. Если за­фиксировать длины сторон b и с (пусть а = 2, b = 4, с = 5), то можно считать, что мы вычисляем R как функцию а. Но Excel дает нам возможность решить обратную задачу: по заданному R вычислить а. При этом не нужно решать вручную громоздкую задачу отыскания а как функции R. Формул на рабочем листе для этой цели вполне достаточно. Например, мы хотим опреде­лить величину а при R = 3. Выделим ячейку G10, в которой вы­числяется R. В меню выберем "Данные/Анализ «что-если»/Подбор параметра". Вы­водится диалоговое окно "Подбор параметра". Поле "Устано­вить в ячейке:" уже содержит адрес выделенной ячейки G10. Нажатием Tab перемещаемся в поле "Значение:" и вводим 3. Еще раз нажимаем Tab и в поле "Изменяя значение ячейки:" вводим адрес ячейки В2, содержащей величину стороны а (если мы щелкнем мышью по этой ячейке, то в поле ввода окажется адрес $В$2 — пока не обращайте внимания на знаки доллара, в позже мы узнаем, что они означают).

Щелкаем кнопку "ОК". Выводится новое окно "Результаты подбора параметра". Разберитесь с его содержимым самостоятельно. Если увеличить разрядность числа в ячейке G10, то Вы увидите, что R достигло значения 2.9999172. При этом а = 1.515753.

Единственное ли значение а соответствует R = 3? На этот вопрос изложенный метод подбора параметра не дает ответа.

Упражнение 2.10. Какое значение а соответствует R = 2? Дайте геометрическую интерпретацию полученному результату.

Упражнение 2.11. Какое значение а соответствует радиусу вписанной окружности r = 0.2?