Диагностика ошибок в формулах Excel
Если Excel не может выполнить обработку формулы в ячейке и вывести результат, то он генерирует сообщение об ошибке и выводит его в данной ячейке (вместо самой формулы или ее результата). Сообщение об ошибке всегда начинается со знака #.
Сообщения об ошибках в Excel могут принадлежать к одному из восьми типов: ######; #ЗНАЧ!; #ДЕЛ/0!; #ИМЯ?; #Н/Д; #ССЫЛКА!; #ЧИСЛО!; #ПУСТО!.
Как правило, ошибка ###### возникает, когда полученный результат не умещается в ячейке. В этом случае необходимо увеличить ширину столбца.
Ошибка #ЗНАЧ! возникает, когда используется недопустимый тип аргумента, например пользователь пытается сложить текстовое и числовое значение.
Ошибка #ДЕЛ/0! появляется, когда в формуле делается попытка деления на ноль.
Сообщение об ошибке типа #ИМЯ? появляется, когда Excel не может найти имя, используемое в формуле. Например, такая ситуация возникнет, если:
■ при наборе имени произошла опечатка;
■ текст ошибочно не был заключен в двойные кавычки;
■ в ссылке на диапазон ячеек пропущен знак двоеточия (:).
Ошибка #Н/Д является сокращением термина "неопределенные данные".
Ошибка #ССЫЛКА! появляется, когда при ссылке на ячейку указывается недопустимый адрес.
Сообщение об ошибке вида #ЧИСЛО! возникает в том случае, когда в формуле задан неприемлемый аргумент для функции.
Сообщение об ошибке типа #ПУСТО! появляется, когда используется ошибочная ссылка на ячейку или диапазон, например задано пересечение двух областей, которые не имеют общих ячеек.
|
|
|
Блоки ячеек
Блок – прямоугольная область ячеек рабочего листа произвольной конфигурации (рис. 10).
При работе со списками, построении формул, форматировании и выделении ячеек рабочего листа удобно использовать блоки ячеек, к которым обращаются по имени.
На рабочем листе может находиться несколько блоков (блок 1 – блок 3). Блоки могут быть перекрывающимися (начало блока 2 и блок 3), состоять из несмежных ячеек (блок 2 имеет два диапазона ячеек). Для выделения несмежных ячеек блока одного листа используется клавиша Ctrl.
Рисунок 10 - Примеры блоков ячеек на рабочем листе
Объемные блоки содержат ячейки различных листов одной рабочей книги, имеют одинаковую конфигурацию ячеек на каждом листе (число строк и столбцов в каждом диапазоне).
Имя блока – непрерывная последовательность символов, соответствующая следующим ограничениям:
■ первый символ имени блока – буква или символ подчеркивания;
■ имя блока отличается от ссылок на ячейки рабочего листа;
■ запрещены пробелы и специальные символы в имени блока;
■ максимальная длина имени – 255 символов;
■ строчные и прописные буквы в именах блоков не различаются.
|
|
|
Неразрешенными именами блоков являются, например:
■ А12 (имя блока совпадает с адресом ячейки на текущем листе);
■ Лист!$А$12 (имя блока совпадает с адресом ячейки на другом листе рабочей книги);
■ 12ASD (имя блока начинается не с буквы);
■ ASD% (имя блока содержит один из специальных символов); и др.
Создать именованный блок ячеек одного листа можно при помощи Диспетчера имен панели инструментов Формулы тремя способами (рис. 11).
Способ 1 (без использования команды меню):
- 1) выделить диапазон ячеек;
- 2) ввести имя блока в окне имен (слева от символа ▼ в строке формул);
Рисунок 11 - Панель инструментов Формулы
3) нажать клавишу ENTER.
Способ 2 (предварительное выделение диапазона ячеек):
- 1) выделить диапазон ячеек;
- 2) выполнить команду Присвоить имя;
- 3) ввести имя создаваемого блока ячеек в поле ввода Имя;
- 4) нажать кнопку ОК.
Способ 3 (блок ячеек только одного столбца или только одной строки):
- 1) выделить диапазон ячеек столбца (строки), включая в выделение ячейку, содержащую название столбца (строки);
- 2) выполнить команду Создать из выделенного фрагмента;
- 3) указать в окне По тексту: для выделенного столбца – "в строке выше/ниже"; для выделенной строки – "в столбце слева/справа".
- 4) нажать кнопку ОК.
Для создания объемного блока ячеек, расположенных на разных рабочих листах книг, после нажатия кнопки в поле ввода Формула следует:
|
|
|
- 1) выделить курсором ярлык первого листа диапазона ячеек объемного блока;
- 2) нажать клавишу Shift и не отпускать ее;
- 3) выделить ярлык последнего листа диапазона ячеек объемного блока (при нажатой клавише Shift);
- 4) отпустить клавишу Shift;
- 5) выделить диапазон ячеек блока на первом листе и нажать кнопку для возврата в основное окно.
Для удаления определения блока ячеек требуется:
- 1) выполнить команду Присвоить имя;
- 2) выбрать в списке имен имя удаляемого блока;
- 3) нажать кнопку Удалить.
При удалении имен блоков содержимое ячеек электронной таблицы, входящих в блок, не стирается. При выделении блока и нажатии клавиши Del удаляется содержимое незащищенных ячеек.
Для сохранения определения блока следует корректно изменять его структуру. При удалении строк/столбцов именованного блока или добавлении новых строк/столбцов в пределах его границ определение блока сохраняется, при этом блок сжимается или растягивается по вертикали или горизонтали. При вставке строк перед первой или после последней ячейки блока не происходит автоматического изменения границ блока.
|
|
|
Форматирование таблиц
Вводимая в ячейки Excel информация может быть отображена на экране различными способами. Для изменения формы отображения и доступа к информации используются средства форматирования и защиты.
Неправильный формат представления данных может вызвать значительные проблемы, особенно, если у пользователя отсутствует достаточный опыт. Например, если пользователь введет число 0.9, но в качестве десятичного разделителя в настройках компьютера используется запятая, то введенные данные будут восприниматься как текст. Опытный пользователь сразу это заметит, так как обычно по умолчанию текст выравнивается по левому краю, а числа – по правому краю ячейки.
Форматирование в Excel включает в себя решение следующих вопросов:
■ изменение шрифта, размеров, начертания и цвета символов;
■ выравнивание и изменение ориентации текста и чисел в ячейках;
■ форматирование чисел, дат и времени;
■ форматирование строк и столбцов;
■ создание и использование пользовательских форматов;
■ условное форматирование;
■ защита ячеек, листов и рабочих книг;
■ использование стилей при форматировании;
■ применение автоформатов.
Защита данных в Excel
Среда Excel обеспечивает санкционированный доступ к электронным таблицам, защиту от непреднамеренных искажений значений ячеек, структуры данных листов и рабочих книг. Существуют различные способы защиты информации в Excel.
1. Защита файла рабочей книги от НСД. Пароль имеет длину до 15 символов, строчные и прописные буквы различаются. Команда Сохранить как кнопка Сервис и пункт Общие параметры выводит диалоговое окно Параметры сохранения для задания паролей: для открытия файла – запрет на открытие книги без пароля; разрешения записи – запрет на открытие, изменение и сохранение книги без пароля (рис. 12).
Рисунок 12 - Защита файла рабочей книги от НСД
Можно указать режим только для чтения. В этом случае модификации исходной рабочей книги сохраняются как файл с новым именем.
- 2. Защита от структурных изменений рабочей книги. Команда панели Рецензирование выводит окно для выбора параметров защиты рабочей книги и ввода пароля. При помощи нее обеспечивается защита рабочей книги от удаления, переноса, скрытия, открытия, переименования и вставки новых листов, перемещения, изменения размеров, скрытия, показа и закрытия окон. Пароль может содержать до 255 символов, различаются строчные и прописные буквы. При вводе пароля в его диалоговом окне выводится символ * (звездочка) в количестве, равном числу введенных символов пароля.
- 3. Защита от структурных изменений листов рабочей книги. При помощи нее обеспечивается защита листа рабочей книги от изменения содержимого ячеек и элементов диаграмм, удаления, переноса, изменения значений или размеров графических объектов, изменения сценариев листа. Пароль может содержать до 255 символов, различаются строчные и прописные буквы.
- 4. Защита содержимого ячеек, скрытие формул. Для выделенного блока ячеек устанавливается или снимается защита от изменения содержимого ячеек, имеется возможность скрыть или показывать формулы в ячейках. Защита ячеек устанавливается только после защиты листа или рабочей книги.
Сортировка записей списка
Сортировка записей таблицы – это упорядочение строк таблицы по какому-либо признаку, например упорядочение списка фамилий по алфавиту, цехов – по их номерам и т.д.
Признак сортировки: фамилия, номер цеха называется ключом сортировки. Как правило, выполняется стандартная сортировка в соответствии с возрастанием (убыванием) кодов символов ключей сортировки. В некоторых случаях необходимо произвести нестандартную сортировку, например по названиям месяцев или дней недели.
При стандартной сортировке курсор устанавливается в любую ячейку списка, выполняется команда панели Данные, Сортировка. В диалоговом окне Сортировка диапазона задаются несколько ключей сортировки с указанием "по возрастанию" или "по убыванию". В качестве ключей сортировки используются имена полей списка (рис. 13).
Сортировка по возрастанию осуществляется в следующем порядке:
■ числа – упорядочивание от наименьшего отрицательного до наибольшего положительного
■ дата и время – упорядочивание от наиболее ранней до самой поздней даты;
■ текст – сначала числа, введенные в виде текста, затем обычный текст (по возрастанию значений кодов символов);
■ логические – сначала ЛОЖЬ, затем ИСТИНА.
Сортировка по убыванию выполняется в обратном порядке, пустые ячейки выводятся в конце списка.
Если необходимо изменить прядок сортировки на нестандартный (пользовательский), нажимается кнопка Параметры, в диалоговом окне выбирается список для сортировки, указывается необходимость учета регистра (строчные/прописные буквы). Например, с помощью пользовательского порядка сортировки могут быть отсортированы месяцы: январь, февраль, март, апрель и т.д. (в противном случае – сортировка в алфавитном порядке).
Упорядоченный нестандартный список сортировки создается командой Сервис, Параметры, вкладка Списки, данные вводятся в поле Элементы списка, а затем нажимается кнопка
Рисунок 13 - Параметры сортировки списка
Добавить. Можно импортировать подготовленный список сортировки из ячеек – кнопка Импорт списка из ячеек.
При выполнении сортировки скрытые строки или столбцы таблицы не перемещаются. Чтобы отсортировать список по четырем и более ключам, сначала список сортируется по трем младшим ключам, а затем по остальным ключам в порядке их значимости.
Для быстрого перехода к сортировке на панели инструментов Стандартная находятся кнопки Сортировка по возрастанию и Сортировка по убыванию. Ключом сортировки в этом случае является столбец с текущей ячейкой.
Фильтрация записей таблицы
Фильтрация – это отбор записей списков по заданным условиям.
Различают автофильтрацию для простейших условий отбора записей и фильтрацию с помощью расширенного фильтра для сложных условий отбора. Условия фильтрации используются встроенными функциями для работы с базой данных Excel.
Автофильтрация
Курсор устанавливается в область списка, по команде меню Данные,
Фильтр, Автофильтр имена полей списка преобразуются в элемент управления "поле со списком". Список содержит до 999 уникальных значений ячеек поля и следующие опции отбора записей:
■ Все – снять фильтр, вывод всех записей списка без ограничений;
■ Первые 10 – ограничение на число выводимых записей списка;
■ Условие – логическое выражение, задающее условие вывода записей списка;
■ Точное значение – выбор записей, содержащих только указанное значение.
Выбор опций производится после нажатия кнопки ▼ в заголовках столбцов.
Условия фильтрации задаются в виде точного значения или условного выражения (рис. 14). Точное значение для сравнения выбирается из раскрывающегося списка поля. Условие состоит из одного или двух предложений, связанных логической связкой И или ИЛИ.
Рисунок 14 - Задание условий для пользовательского автофильтра
Расширенный фильтр
Расширенный фильтр задается командой меню Данные, Фильтр, Расширенный фильтр. Вне основного списка на листе списка или другом листе той же самой или иной рабочей книги формируется диапазон условий, который включает в себя: имена столбцов условий, строки условий для отбора записей.
Состав полей столбцов списка в диапазоне условий указывается в произвольном порядке. Например, на рис. 15 приведен диапазон условий расширенного фильтра для компьютеров, у которых емкость жесткого диска составляет не менее 4,3 Гбайт, стоимость – не выше 400 долл., а также компьютеров с видеокартой типа 4 Mb AGP.
Рисунок 15 - Диапазон условий расширенного фильтра
Условия фильтрации, размещенные в одной строке диапазона, объединяются логической функцией И; условия, заданные в разных строках, – функцией ИЛИ. Пустые строки условий не допускаются.
При вызове команды меню Данные, Фильтр, Дополнительно выводится диалоговое окно Расширенный фильтр (рис. 16).
Рисунок - 16. Диалоговое окно команды Расширенный фильтр
В этом случае указывается местоположение результата фильтрации списка: Фильтровать список на месте или скопировать в другую область рабочего листа, или другой лист текущей или другой открытой рабочей книги – Скопировать результат в другое место. В поле Исходный диапазон указывается диапазон ячеек списка (имя блока), в поле Диапазон условий – диапазон ячеек области критериев (имя блока). В поле Поместить результат в диапазон дается ссылка на верхнюю левую ячейку выходного диапазона. Если требуется отобрать записи с неповторяющимися значениями фильтруемых полей, устанавливается флажок Только уникальные записи.
Вычисляемые условия
Диапазон условий может содержать вычисляемые критерии. В этом случае он заполняется в соответствии со следующими правилами:
■ заголовок столбца вычисляемого критерия диапазона условий не должен совпадать с именами полей списка (может не заполняться);
■ в строке условия вводится формула, которая вычисляет логическую константу (ИСТИНА, ЛОЖЬ) по отношению к первой записи списка;
■ формула содержит ссылки на ячейки списка. В левой части формулы (до знака сравнения) ссылки на ячейки списка должны быть относительными, в правой части формулы – абсолютными. Ссылки на ячейки вне списка должны быть всегда абсолютными.
Например, для компьютеров, цены которых не выше средней, сниженной на заданный процент, имя столбца ЦЕНА1 не совпадает с именами полей списка. Формула фильтрации имеет логический вид:
=G2<=CP3HA4 ($G$2:$G$11)*(1 – $N$4).
Левая часть формулы (G2) – относительная ссылка на первую ячейку столбца Цена. Правая часть формулы использует встроенную функцию СРЗНАЧ по диапазону ячеек столбца Цена. $G$2:$G$11 – абсолютные ссылки на диапазон ячеек блока. Процент снижения цены находится в ячейке $N$4 – абсолютная ссылка, так как ячейка находится вне списка.
Общий вид вычисляемого критерия представлен на рис. 17.
Рисунок 17 - Общий вид вычисляемого критерия
Обратная связь: выполненные задания, вопросы отправляем в комментариях или личные сообщения преподавателю или на электронную почту колледжа dktidistanc@mail.ru
Дата добавления: 2021-11-30; просмотров: 24; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!