Неверные формулы

Контроль логических ошибок

Другие функции итоговых вычислений

Прочие функции для итоговых вычислений выбираются обычным образом, с помощью раскрывающегося списка в строке формул или с использованием мастера функций. В число таких функций входят, например, функции ДИСП (вычисляет дисперсию), МАКС (максимальное число в диапазоне), СРЗНАЧ (среднее арифметическое значение чисел диапазона), СЧЕТ (подсчет ячеек с числами в диапазоне) и другие. Перечисленные функции относятся к категории Статистические.

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

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

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

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

Таблица 3

Стандартные сообщения программы об ошибках

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

Циклические ссылки

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

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

Циклическая ссылка – это случай, когда значение в ячейке зависит от нее самой. Простейший вариант – ячейка содержит ссылку на саму себя. Такая ситуация иногда случайно возникает при задании диапазона для итоговых вычислений. В более сложных случаях найти циклическую ссылку может оказаться не так легко, поскольку цепочка от ячейки к ней же самой может идти через большое число промежуточных зависимых ячеек.

Если программа обнаруживает в электронной таблице циклические ссылки, она немедленно выдает предупреждающее сообщение и открывает панель инструментов Циклические ссылки. Все ячейки, содержащие циклические ссылки, помечаются голубым кружком, а в строке состояния появляется слово Цикл и список таких ячеек.

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

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

Последовательность выполнения

1. Запустите программу Excel (Пуск ►Программы ►Microsoft Office ► Microsoft Excel).

2. Откройте рабочую книгу Лабораторные работы по Excel.xls.

3. Выберите рабочий лист Дополнительные расходы, созданный в предыдущей работе.

4. В ячейку С1 введите текст Нарастающий итог.

5. Сделайте текущей ячейку С2. Введите в нее = В2.

6. Щелкните на ячейке С3. Введите знак =. Щелкните на ячейке В3. Убедитесь, что ссылка на эту ячейку помещена в строку формул. Нажмите клавишу +. Щелкните на ячейке С2. Нажмите клавишу ENTER.

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

8. Щелкните на одной из ячеек столбца С и посмотрите, какая формула в ней записана. Убедитесь, что все формулы были скорректированы по принципу относительной адресации.

9. Сделайте текущей первую свободную ячейку в столбце В (В26).

10. Щелкните на кнопке Автосумма на стандартной панели инструментов.

11. Убедитесь, что программа автоматически подставила в формулу функцию СУММ и правильно выбрала диапазон ячеек для суммирования. Нажмите клавишу ENTER.

12. Проверьте правильность вычислений, сравнив значения в ячейках В26 и С25.

13. Сделайте текущей следующую свободную ячейку в столбце В.

14. Щелкните на кнопке Вставка функции на стандартной панели инструментов.

15. В списке Категория выберите пункт Статистические.

16. В списке Функция выберите функцию СРЗНАЧ и щелкните на кнопке ОК.

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

18. Сохраните рабочую книгу.

Содержание отчета

1. Выполнение всех пунктов задания.

2. Выполнение задания для самостоятельной работы.

3. Ответы на контрольные вопросы.

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

1. С чего начинается запись формулы в электронной таблице? Какие элементы может содержать формула?

2. Что такое ссылка? Какими способами вводятся ссылки в электронную таблицу?

3. Что такое относительная и абсолютная адресация, чем они отличаются?

4. Как выполняется автозаполнение формулами?

5. Каким образом можно вставить функцию в формулу?

6. Перечислите основные категории функций, применяемых в Excel?

7. Как можно вводить параметры в палитру формул?

8. Как можно редактировать формулу?

9. Что такое итоговые функции? В чем заключаются их особенности?

10. Что такое циклическая ссылка, как она устраняется?

Задания для самостоятельной работы

1. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее и минимальное значение.

2. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее и максимальное значение.

3. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и количество в списке аргументов.

4. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и количество элементов больше среднего.

5. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и количество элементов меньше среднего.

6. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и второе наибольшее значение.

7. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и второе наименьшее значение.

8. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и третье наибольшее значение.

9. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и третье наименьшее значение.

10. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и четвертое наибольшее значение.

11. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и четвертое наименьшее значение.

12. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и пятое наибольшее значение.

Дата добавления: 2014-10-15