Цель работы: изучить правила записи формул; приобрести навыки по вводу формул и использованию в формулах функций; изучить методы локализации ошибок, возникающих в результате реализации арифметических и логических вычислений.
Методические указания
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. «Убрать все стрелки» - удаляет с экрана отображение всех зависимостей.
