Реализация арифметических и логических вычислений

Цель работы: изучить правила записи формул; приобрести навыки по вводу формул и использованию в формулах функций; изучить методы локализации ошибок, возникающих в результате реализации арифметических и логических вычислений.

Методические указания

MS Excel оперирует с двумя основными типами данных - формулы и константы. К константам относятся числовые и текстовые значения, логические значения, а также значения даты и времени. Формула может содержать различные типы констант, встроенные или пользовательские функции и знаки арифметических, текстовых, адресных операций и операций сравнения.

Формула в MS Excel – это начинающееся со знака равно «=» выражение, состоящее из разного типа констант и (или) встроенных функций MS Excel, а также знаков арифметических, текстовых и логических операций. В формулах можно использовать следующие знаки операций (арифметические операторы в табл. 4.1 расположены в порядке возрастания приоритета).

 

Таблица 4.1

Виды и приоритет операций

Виды операций	Знаки
Арифметические	+ (сложение)
	- (вычитание)
	* (умножение)
	/ (деление)
	% (процент)
	^ (возведение в степень)
	–Унарный минус (изменение знака)
Текстовые	& (конкатенация)
Сравнения	= (равно)
	< (меньше)
	> (больше)
	>= (больше или равно)
	<= (меньше или равно)
	<> (не равно)
Адресные	: (двоеточие)
	; (точка с запятой)
	() (пробел)

 

Ввод формул в MS Excel отличается от ввода констант. Формулы в MS Excel начинаются с символа " = ". Отсутствие лидирующего символа " = " приводит к заданию не формулы, а текстового выражения, которое не подлежит вычислению. В качестве операндов арифметических выражений можно использовать константы, ссылки (адреса ячеек), функции.

Пример 1. Вычислим значение выражения , при .

Решение. В результате вычислений выражение будет равно 1 (рис.4.1).

 

Рис. 4.1. Ввод исходных данных и расчетные формулы для примера 1

 

Приоритет унарного минуса выше приоритета возведения в степень. Поэтому правильной должна быть реализация формулы в виде .

Пример 2. Необходимо вычислить значение арифметического выражения .

Решение. Возведение в степень имеет более высокий приоритет по сравнению с делением, а деление — по сравнению со сложением, то для корректного вычисления результата следует приоритет операторов задать с помощью скобок (рис.4.2).

 

Рис. 4.2. Ввод исходных данных и расчетные формулы для примера 2

 

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

Для реализации логических вычислений вложенная функция, используемая в качестве аргумента, должна возвращать соответствующий этому аргументу тип данных. Например, если аргумент должен быть логическим, то есть иметь значение либо ИСТИНА, либо ЛОЖЬ, то вложенная функция в результате вычислений тоже должна давать логическое значение либо ИСТИНА, либо ЛОЖЬ. Иначе появится сообщение об ошибке «#ЗНАЧ!».

Пример 3. Вычислить значение функции , если значение аргумента располагается в ячейке , тогда

 

 

Решение. Для того, чтобы вычислить значения при разных условиях, следует воспользоваться функцией ЕСЛИ (рис.4.3). Эта функция имеет три аргумента. Первый аргумент – логическое выражение, задающее условие; второй аргумент – выражение, которое выполняется в том случае, если логическое выражение приобретает значение «истина»; третий аргумент – выражение, которое выполняется в том случае, когда логическое выражение приобретает значение «ложь».

Введенная формула будет иметь следующий вид:

 

 

 

Рис. 4.3. Ввод исходных данных и расчетные формулы для примера 3

 

В формулах можно использовать до семи уровней вложения функций. Когда функция Б является аргументом функции А, функция Б находится на втором уровне вложенности.

При использовании Мастера функций скобки при её аргументах расставляются автоматически, т.е. автоматически контролируется их баланс. Отредактировать формулу, содержащую функцию, можно как вручную, так и с использованием Мастера функции. Можно повысить читабельность сложных формул, разбивая их на логические блоки и размещая в строке формул в несколько строк (ALT–ENTER).

Список всех доступных функций, а также соответствие между русскими и английскими их названиями можно увидеть, загрузив файл FUNCS.XLS.

Переключение режимов отображения на экране результатов вычислений или формул осуществляется сбросом или установкой флажка Сервис – Параметры… – Вид – Параметры окна – формулы.

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

При создании формул в MS Excel пользователь может сталкиваться со стандартными ошибками (рис. 4.4).

 

 

Рис. 4.4. Примеры некоторых часто встречающихся ошибок

 

1. Значки ### - ошибки как таковой нет, просто текст не вмещается в ячейку, требуется изменить её ширину.

2. Деление на нуль #ДЕЛ/0.

3. Ссылка на ячейки с неопределенным значением: #Н/Д.

4. Excel не распознаёт имя или адрес ячейки: #ИМЯ? Либо собственное имя ячейки написано неверно или ячейки с таким именем не существует.

5. Наличие пересекающихся областей: #ПУСТО! Задано пересечение двух диапазонов, которые не имеют общих ячеек.

6. Числовой аргумент, находящийся за пределами допустимых значений или функция не может получить результат при заданных аргументах: # Число!

7. Неверная ссылка на ячейку: #ССЫЛКА! Ячейки, на которые ссылается формула, были удалены или на их место помещено содержимое скопированных ячеек.

8. Значение не того типа, что ожидается: #ЗНАЧ! Например, мы ссылаемся на ячейку, в которой вместо числовых данных находится текст.

Локализацию ошибок можно осуществлять следующим образом:

1. Выделить ячейку с ошибкой.

2. В строке формул выделить вызывающий сомнение фрагмент.

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

4. Клавиша «Esc» – отказ от сделанных изменений, возврат к исходной формуле и исправление ошибки.

При локализации ошибок в формулах можно пользоваться командой трассировки ошибок.

Чтобы проследить зависимость в формулах от адресов ячеек, можно использовать команды, которые находятся: Сервис – Зависимости формул.

1. «Влияющие ячейки» - стрелками будут показаны ячейки, содержимое которых влияет на значение текущей ячейки. Вторичное использование этой команды укажет ячейки, оказывающие косвенное влияние на содержимое текущей ячейки.

2. «Зависимые ячейки» - устанавливает стрелки, указывающие на ячейки, на содержимое которых влияет текущая ячейка.

3. «Источник ошибки» - стрелками указываются ячейки, которые могут быть потенциальными источниками ошибок.

4. «Убрать все стрелки» - удаляет с экрана отображение всех зависимостей.