В ячейку А1 занесите текст: Введите Х.

2.2. В ячейку А3 занесите текст: Интервалы. Затем в ячейки А4 и А5 введите текст:

X>=-0,5 и X<3, X>=3 или X=-10 соответственно.

2.3. В ячейки В3 и В4 занесите формулы =И(В1>=-0,5; B1<3) и =ИЛИ(B1>=3; B1=-10) соответственно.

В ячейку В1 вводите последовательно различные значения для Х и проанализируйте полученные результаты.

Легко заметить, что логические формулы представляют собой высказывания, то есть утверждения, относительно которых можно сказать, верны они или нет при различных значениях исходных данных. Пусть в ячейки А1, В1, С1 введены три числа. Требуется определить, есть среди них хотя бы одно положительное. Логическая формула =ИЛИ(A1>0; B1>0; C1>0) позволяет проверить истинность этого высказывания.

Для описания разветвляющихся вычислительных процессов при составлении алгоритмов предназначена структура ЕСЛИ-ТО-ИНАЧЕ. На псевдокоде эта структура записывается следующим образом.

Если логическое выражение равно ИСТИНА, вычисления выполняются по формуле, следующей за фразой ЕСЛИ, а затем, когда встретилось ключевое слово ИНАЧЕ, осуществляется переход к формуле, следующей после завершающего ВСЕ-ЕСЛИ. Формула после ключевого слова ИНАЧЕ выполняется, если проверяемое логическое выражение равно ЛОЖЬ.

 

ЕСЛИ <лог. выр.>

A

ИНАЧЕ

В

ВСЕ-ЕСЛИ

Для реализации этой управляющей структуры в Excel имеется функция ЕСЛИ, которая имеет следующий синтаксис:

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

 

Например, для описанного выше примера будет справедлива формула

= ЕСЛИ(И(B2>=-0,5; B2<3); B2^2;0).

После вычисления по этой формуле будет возвращен квадрат числа, находящегося в ячейке В2, если его значение попадает в интервал от –0,5 до 3, и 0 в противном случае.

В формуле =ЕСЛИ(СУМ(А2:А23)>0;СУМ(А2:А23);””) результат будет пустая строка, если сумма числовых данных в диапазоне А2:А23 будет равна нулю или отрицательна.

Структуры ЕСЛИ-ТО-ИНАЧЕ могут быть вложенными, т.е. любая из команд после ЕСЛИ или ИНАЧЕ может содержать другую структуру ЕСЛИ-ТО-ИНАЧЕ, например, по следующей схеме:

      ЕСЛИ <лог_выр1>

     ..........

            ЕСЛИ <лог_выр2>

                   формула, если лог_выр2 ИСТИНА

            ИНАЧЕ

                   Формула, если выр_лог2 ЛОЖЬ

            ВСЕ-ЕСЛИ

     ..........

   ИНАЧЕ

           формула, если лог_выр1 ЛОЖЬ

   ВСЕ-ЕСЛИ

Рассмотрим пример программирования разветвляющегося вычислительного процесса средствами Excel. Пусть требуется вычислить значение переменной Y:

               , если x >= -8.23 и x <= 2

Y=   Ln(8 - x),           если x > 2.

  

При решении подобных задач, где отсутствует уверенность, что задача может быть решена "с ходу" и правильно, целесообразно применять метод последовательной детализации. Прежде всего, по описанным выше правилам составляются логические выражения для обоих условий. Затем производится построение алгоритма решения данной задачи, которое сводится к интерпретации элементов структуры ЕСЛИ- ТО - ИНАЧЕ применительно к условию задачи. 

В первом приближении алгоритм имеет вид:

 ПРОГРАММА Расчет Y

 ВВОД X

 ЕСЛИ x >= -8.23 AND x <= 2

Выполняется расчет значения Y по первой формуле. ВЫВОД "y=";y

 ИНАЧЕ

После проверки X > 2 или X < -8.23 либо производить расчет по

второй формуле и вывести Y, либо выдать сообщение "функция не задана"

 ВСЕ-ЕСЛИ

КОНЕЦ

 В этом алгоритме предложение после ключевого слова ЕСЛИ требует дальнейшей расшифровки. Прежде чем записать выражение для расчета значения Y, необходимо проверить область определения, чтобы избежать ошибок при выполнении арифметических выражений. В данном случае необходимо перед вычислением контролировать положительное значение подкоренного выражения. Это равносильно высказыванию:

ЕСЛИ cos(x) - sin(x) >= 0

  y =КОРЕНЬ(cos (x) - sin (x))

  ВЫВОД "Y="; y

ИНАЧЕ

  ВЫВОД "Функция не определена"

ВСЕ-ЕСЛИ

Предложение после ключевого слова ИНАЧЕ в алгоритме также требует расшифровки. Она связана с проверкой условия X > 2, и соответствующий алгоритм имеет вид

ЕСЛИ x > 2

  вычислить значение Y по второй формуле

ИНАЧЕ

  ВЫВОД "Функция не задана"

ВСЕ-ЕСЛИ

Этим заканчивается второй этап детализации алгоритма. Анализ его показывает, что все элементы алгоритма имеют соответствующий аналог на Excel, кроме предложения после строки "ЕСЛИ x > 2". Расшифровка ее предполагает анализ области определения. Очевидно, что логарифмы существуют только для положительных чисел, таким образом:

ЕСЛИ 8-x > 0

    y=L N (8-x)

    ВЫВОД "Y=";y

ИНАЧЕ

    ВЫВОД "Функция не определена"

ВСЕ-ЕСЛИ

На этом детализацию алгоритма можно закончить. Окончательно алгоритм решения данной задачи имеет вид

ПРОГРАММА Расчет Y

ВВОД x                         

ЕСЛИ x >= -8.23 AND x <=2         

ЕСЛИ cos(x) - sin(x) >= 0         

      y=КОРЕНЬ (cos(x) - sin(x))         

      ВЫВОД "Y=";y                

ИНАЧЕ                             

      ВЫВОД "Функция не определена"                    

ВСЕ-ЕСЛИ                    

ИНАЧЕ                             

ЕСЛИ x > 2                    

       ЕСЛИ x < 8                 

              y=L N(8-x)                

              ВЫВОД "Y=";y       

      ИНАЧЕ                             

             ВЫВОД "Функция не определена"                     

      ВСЕ-ЕСЛИ                    

ИНАЧЕ                              

     ВЫВОД "Функция не задана"    

ВСЕ-ЕСЛИ                    

ВСЕ-ЕСЛИ                 

Реализация этого алгоритма средствами Excel потребует использования вложенных функций ЕСЛИ

=ЕСЛИ(И(A2>=-8,2;A2<=2);ЕСЛИ(COS(A2)-SIN(A2)>=0; КОРЕНЬ(COS(A2)-SIN(A2));"Функция не определена");ЕСЛИ(A2>2; ЕСЛИ(A2<8;LN(8-A2);"Функция не определена");"Функция не задана")).

Без ошибок набрать такую формулу практически невозможно. При сообщении об ошибках их идентификация также вызывает проблемы. Поэтому целесообразно вводить формулу так, чтобы она соответствовала проекту алгоритма (рис. 6.1). Достигается это путем нажатия комбинации клавиш Alt+Enter в соответствующих точках формулы.

Всего можно использовать до семи вложений функций ЕСЛИ (это справедливо для всех функций), но при этом нельзя выходить за пределы максимальной длины значения в ячейке (255 символов). Это ограничивает возможности Excel при решении подобных задач, но для практических задач вполне достаточно.

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

ЗАДАНИЕ 3. Реализуйте приведенный выше алгоритм средствами Excel. Экран должен быть таким, как на рис. 6.1.     

Рис. 6.1. Пример реализации разветвляющегося вычислительного процесса

Функции ИСТИНА и ЛОЖЬ

Эти функции позволяют включать логические константы в выражения отношения, они не имеют аргументов и записываются следующим образом: =ИСТИНА() и =ЛОЖЬ(). Например, ячейка D2 содержит следующую формулу =И(B1>=-8,23; B1<=2). Тогда формула, размещенная в D3,

=ЕСЛИ(D2=ЛОЖЬ();”Х не принадлежит интервалу [-8,23;2]”; COS(B1)- SIN(B1) >=0)

будет выводить результат проверки только в том случае, когда введенное значение для Х не будет попадать в интервал от –8,23 до 2.

Ошибочные значения

Ошибочное значение – это результат формулы, которую Excel не может разрешить. В Excel определено семь ошибочных значений, описание которых приведено в табл.2. Тип ошибочного значения можно определить с помощью функции ТИП ОШИБКИ в категории Проверка свойств и значений Мастера функций.

Таблица 2. Расшифровка сообщений об ошибках

Ошибочное значение	Описание
# ДЕЛ/0	Попытка деления на ноль. Эта ошибка обычно связана с тем, что создана формула, в которой делитель ссылается на пустую ячейку
# ИМЯ?	В формуле используется имя, отсутствующее в списке имен диалога Присвоить имя. Сделана опечатка при вводе имени или указано имя, которое было удалено. Excel выводит это сообщение также в случае, когда строка символов не заключена в двойные кавычки
# ЗНАЧ!	Введена математическая формула, которая ссылается на текстовое значение
# ССЫЛКА!	Отсутствует диапазон ячеек, на который ссылается формула (возможно, он удален)
# Н/Д	Нет данных для вычислений. При построении модели можно ввести # Н/Д в ячейки и тем самым показать, что они зарезервированы для ожидаемых в дальнейшем данных. Любая формула, которая ссылается на ячейки, содержащие # Н/Д, возвращает значение # Н/Д
# ЧИСЛО!	Задан неправильный аргумент функции. #ЧИСЛО! может также указывать, что значение формулы слишком велико или слишком мало и не может быть представлено на листе
# ПУСТО!	В формуле указано пересечение диапазонов, но эти диапазоны не имеют общих ячеек

Три специальные логические функции ЕОШ, ЕОШИБКА и ЕНД проверяют значение аргумента или ячейки и определяют, содержат ли они ошибочные значения. Функция ЕОШ проверяет значение на все ошибки за исключением #Н/Д. ЕОШИБКА отслеживает все ошибочные значения, включая # Н/Д, и ЕНД проверяет появление только # Н/Д. Все три функции возвращают логическое значение ИСТИНА, если их аргумент – ошибочное значение, и ЛОЖЬ – в противном случае. Эти функции позволяют «перехватывать» ошибки и значения # Н/Д, предотвращая их распространение по рабочему листу. Они имеют один и тот же синтаксис написания: =ЕОШ(значение). Хотя значение может быть числом, формулой или строкой символов, но обычно это ссылка на ячейку или диапазон. Обычно эти три функции используются в качестве логических выражений в функции ЕСЛИ. Например, формула

=ЕСЛИ(ЕОШИБКА(А1/А2:”Ошибка: деление на ноль”; A1/ A2)

проверяет выражение А1/А2. Если А2 пустая или содержит ноль, то функция ЕОШИБКА возвращает логическое значение ИСТИНА и функция ЕСЛИ возвращает сообщение Ошибка: деление на ноль. Если А1/A2 не содержит ошибки, то функция ЕОШИБКА возвращает значение ЛОЖЬ и функция ЕСЛИ возвращает значение А1/A2.

Для организации проверки корректности введенных данных используются функции, приведенные в табл. 3.

Таблица 3. Функции проверки содержимого ячеек

Функция	Возвращает значение ИСТИНА, если аргумент
ЕТЕКСТ ЕНЕТЕКСТ ЕЛОГИЧ ЕЧИСЛО ЕПУСТО	Ссылка на текст Ссылка на значения, не являющиеся текстом Ссылка на логические значения Ссылка на число Ссылка на пустую ячейку

ЗАДАНИЕ 4. Сформируйте таблицу значений Y по приведенной выше формуле при различных значениях Х.

4.1. Выберите Х₀ и h такие, чтобы 21 значение Х_i, вычисленное по формуле Х_i=X_i-1+h, i=1,2,… равномерно распределялось в интервалах X<-8,23; X>=-8,23 и X<=2; X>2. Х₀ и h занесите во вспомогательную таблицу.

4.2. Составьте таблицу результатов расчета Y по заданной формуле. Таблица должна иметь следующий вид:

№ п/п	X	Y
1 2 … 22

Для контроля правильности ввода значений для Х предусмотреть проверку кор-ректности ввода. На рабочем листе разместить блок текстовых констант для вывода сообщений.

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

ЗАДАНИЕ 5. Подготовьте полученные результаты к печати, используя инструмент Предварительный просмотр печати.

5.1. Выберите ландшафтное расположение.

5.2. Уберите сетку.

5.3. Укажите в верхнем колонтитуле фамилию, имя, отчество и номер группы, а в нижнем – дату и время.

ЗАДАНИЕ 6. Выведите отредактированную таблицу с выбранным вариантом штатного расписания на печать.

Проверьте,

знаете ли вы, что такое:	умеете ли вы:
· разветвляющиеся вычислительные процессы; · выражения отношения и логические выражения; · синтаксис логических функций, используемых для описания логических операций; · синтаксис логических функций ИСТИНА и ЛОЖЬ; · структура ЕСЛИ-ТО-ИНАЧЕ и ее реализация в Excel;	· составлять формулы для описания выражений отношения и логических выражений; · использовать логические функции для проверки наличия в ячейке логического значения; составлять формулы для решения задач, содержащих выбор одной из нескольких альтернатив; использовать логические функции для “перехвата” ошибок при выполнении вычислений.

ЛЕКЦИЯ № 9