Прочие функции для итоговых вычислений выбираются обычным образом, с помощью раскрывающегося списка в строке формул или с использованием мастера функций. В число таких функций входят, например, функции ДИСП (вычисляет дисперсию), МАКС (максимальное число в диапазоне), СРЗНАЧ (среднее арифметическое значение чисел диапазона), СЧЕТ (подсчет ячеек с числами в диапазоне) и другие. Перечисленные функции относятся к категории Статистические.
Функции, предназначенные для выполнения итоговых вычислений, часто применяют при использовании таблицы 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 среднее значение и пятое наибольшее значение.
13. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и количество в списке аргументов.
14. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и количество элементов больше среднего.
15. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и количество элементов меньше среднего.
16. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и второе наибольшее значение.
17. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и второе наименьшее значение.
18. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и третье наибольшее значение.
19. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и третье наименьшее значение.
20. Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 1 и переименуйте его как Задание 2. В ячейку С1 введите текст Нарастающий итог. Заполните ячейки с С2 по С21 нарастающими значениями соответствующих величин, введенных ранее. Найдите сумму заполненных ячеек столбца В и сравните результат с ячейкой С21. В свободных ячейках столбца В, используя статистические функции, найдите для диапазона В2:В21 среднее значение и четвертое наибольшее значение.
