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

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

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

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

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

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

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

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

Таблица 3

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

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

При возникновении ошибок другой категории – циклических ссылок – программа 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 среднее значение и пятое наибольшее значение.