Ошибки в функциях и аргументах

Если формула содержит ошибку функции и/или аргумента, не позволяющую выполнить вычисления или отобразить результат, Excel отобразит сообщение об ошибке. В ячейке с ошибкой в формуле вместо результата вычисления отображается один из кодов ошибки (таблица 7), а в левом верхнем углу ячейки появляется индикатор ошибки (зеленый треугольник).

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

Таблица 7 – Коды ошибок

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

 

Трассировка связей между формулами и ячейками

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

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

Один щелчок по кнопке Влияющие ячейки отображает связи с ячейками, непосредственно определяющими результат вычисления. Если в этих ячейках также находятся формулы, то следующий щелчок отображает связи с ячейками, являющимися аргументами этих формул. И так далее (рис. 15).

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

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

 

Рис. 15. Отображение ячеек влияющих на ячейку D4

Использование встроенных функций

 

Суммирование

СУММ(А)

где A – список от 1 до 30 элементов, которые требуется суммировать. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются. Пример: =СУММ(В2:В7)

Фактически то же самое, что и =В2+В3+В4+В5+В6+В7. Однако есть и некоторые отличия. При использовании функции СУММ добавление ячеек в диапазон суммирования автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон суммирования. Аналогично формула будет изменяться и при уменьшении диапазона суммирования.

Возведение в степень

СТЕПЕНЬ(А;В)

где A – число, возводимое в степень; В – показатель степени, в которую возводится число. Пример: =СТЕПЕНЬ(2;128)

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

Извлечение квадратного корня

КОРЕНЬ(А)

где A – число, из которого извлекают квадратный корень. Пример: =КОРЕНЬ(C4)

Если же требуется извлечь, кубический корень из числа, следует воспользоваться математическим понятием, согласно которому извлечение кубического корня равнозначно возведению числа в степень 1/3. Поэтому вычисление кубического корня из 27 выполняется по формуле =27^(1/3). Этот принцип универсален: он позволяет находить корень любой степени через возведение числа в соответствующую дробную степень.

Нельзя извлекать корень из отрицательных чисел!

 

Тригонометрические вычисления

TAN(А)

где A – угол в радианах, для которого определяется тангенс. Пример: =SIN(20)

В Microsoft Excel можно выполнять тригонометрические вычисления SIN, COS, TAN, то есть, зная значение угла, находить значения тригонометрических функций. Синтаксис всех прямых тригонометрических функций одинаков.

 

Логические вычисления

ЕСЛИ(А;В;С)

где A – логическое выражение, правильность которого следует проверить; В – значение, если логическое выражение истинно; C – значение, если логическое выражение ложно. Пример: =ЕСЛИ(E3>0;"плюс";"минус")

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

Например, при расчете стоимости товара цена зависит от объема партии товара. При объеме партии более 30 цена понижается на 10%. Следовательно, при выполнении условия используется формула цена*количество*0,9, а при невыполнении условия – цена*количество

=ЕСЛИ(E3>30;" D2*E3*0,9 ";" D2* E3").

 

Задание по работе

Документы сохранять с указанными ниже именами в своей папке на диске D.

 

Задание 1

1. Создайте рабочую книгу и сохраните ее с именем Вычисления.

2. На Листе1 создайте таблицу в соответствии с образцом (таблица 8) и сохраните ее.

3. На основании данных, представленных в таблице произведите следующие расчеты (использовать формулы и встроенные функции):

4. В ячейках столбца Всего начислено должна быть формула, суммирующая для каждой фамилии содержимое ячеек столбцов Оклад, Премия, Материальная помощь;

5. В ячейках столбца Подоходный налог должна быть формула вычисляющая величину подоходного налога для каждой фамилии (величина налога 20%);

6. В ячейках столбца К выдаче должна быть формула вычисляющая сумму к выдаче каждому сотруднику, т.е. содержимое ячеек Всего начислено минус содержимое ячеек Подоходный налог;

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

Таблица 8 – Ведомость начисления заработной платы

№	Фамилия	Оклад	Премия	Мат. помощь	Всего начислено	Подоходный налог	К выдаче
	Иванов
	Петров
	Сидоров
	Семенова
	Попов
	Андреев
	Иванова
	Сидорова
Итого:
Максимальная:
Минимальная:
Средняя:

 

8. Просмотрите влияющие и зависимые ячейки в формулах.

9. Сохраните документ не закрывая его.

 

Задание 2

1. На Листе2 создайте таблицу в соответствии с образцом (таблица 9) и сохраните ее.

2. Таблица предназначена для вычисления n -го члена и суммы членов геометрической прогрессии.

Формула для вычисления n -го члена геометрической прогрессии:

b_n = b₁ * q^n-1

где b₁ – первый член геометрической прогрессии;

q – знаменатель геометрической прогрессии;

n – количество членов геометрической прогрессии.

Формула для вычисления суммы n первых членов геометрической прогрессии:

S_n = b₁(qⁿ -1)/( q -1).

 

Таблица 9 – Вычисление суммы членов геометрической прогрессии

№ п/п	bn	Sn
	Число, соответствующее варианту	Сумма одного члена геометрической прогрессии
		Сумма двух членов геометрической прогрессии
		Сумма трех членов геометрической прогрессии
…		…
…		…
…		…
		Сумма двадцати членов геометрической прогрессии

 

Таблицу заполнить следующим образом:

· в ячейку, содержащую текст – Число, соответствующее варианту, введите номер варианта;

· в остальных ячейках столбца b_n должна быть создана числовая последовательность, представляющая собой геометрическую прогрессию со знаменателем q=2;

· в ячейках столбца S_n должна быть рассчитана сумма одного, двух и т.д. до 20 членов геометрической прогрессии.

3. Сохраните документ не закрывая его.

 

Задание 3

1. На Листе3 создайте таблицу для расчета периметра и площади правильного многоугольника (столбцы a, n, P, S).

2. Формулы для вычисления:

где: a – размер стороны многоугольника;

n – количество сторон многоугольника (n>2).

3. Столбец А – число соответствующее варианту, столбец N – арифметическая прогрессия от 3 до 20 с шагом 1, столбцы P и S – формулы для расчета периметра и площади.

4. Сохраните документ не закрывая его.

 

Задание 4

1. На Листе4 создайте таблицу для расчета уравнения (столбцы X, Y, Знак).

2. Столбец X –– арифметическая прогрессия от -10 до 10 с шагом 1, столбец Y – формула для вычисления, столбец Знак – должен содержать текст «Плюс», «Минус», «Ноль» в зависимости от значения в столбце Y. Для вычисления столбца Знак использовать функцию ЕСЛИ(А;В;С).

Пример: =ЕСЛИ(B22<100;X^2;ЕСЛИ(B22>0;X^3;(X-1)^2))

3. Сохраните документ.

 

Контрольные вопросы

 

1. Формулы. Общие сведения. Вкладка Формулы.

2. Создание формул.

3. Функции. Способы ввода функций. Примеры.

4. Виды операторов. Примеры операторов.

5. Копирование и перемещение формул.

6. Ошибки в формулах.

7. Трассировка связей между формулами и ячейками.

 

ЛАБОРАТОРНАЯ РАБОТА № 4

 

Ссылки в MS Excel

Методические рекомендации

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

Ячейка однозначно идентифицируется своим адресом. Адрес ячейки состоит из имени столбца (А, В, С и т.д.) и номера строки (1, 2, 3 и т.д.), например, D1, F15 и т.д.

Ссылки позволяют использовать в формулах содержимое других ячеек, расположенных:

· в том же листе, что и формула;

· в различных листах, относящихся к одной рабочей книге;

· в листе, расположенном в другой рабочей книге.

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