2020-09-27
355
Ошибки формулы могут привести к ошибочным значениям, а также вызвать непредсказуемые результаты. Если формула содержит ошибку, не позволяющую выполнить вычисления или отобразить результат, MS Excel отобразит сообщение об ошибке. Каждый вид ошибки вызывается разными причинами, и разрешаются такие ошибки различными путями. В таблице 2, приведены типовые ошибки в формулах.
Таблица 2 – Типовые ошибки в формулах MS Excel
| ##### | Столбец недостаточно широк |
| #ЗНАЧ! | Используется недопустимый тип аргумента или операнда |
| #ДЕЛ/0! | Деление числа на ноль |
| #ИМЯ? | Ошибочное имя функции |
| #ССЫЛКА! | Ссылка на ячейку указана неверно |
| #ЧИСЛО! | Неправильные числовые значения в формуле или функции |
Лабораторная работа №25
Наименование работы: Работа со списками в MS Excel
Цель работы: Рассмотреть принцип создания базы данных на основе электронных таблиц MS Excel. Изучить способы управления данными списка с помощью операций сортировки и фильтрации, научиться осуществлять поиск данных по различным критериям. Формировать ОК-2, ОК-4, ОК-5, ОК-6, ПК-1.2, ПК-1.3, ПК-1.6, ПК-3.1, ПК-3.2
Литература:
1. Кунтаева Г.Х., Строева Л.Р. Пакеты прикладных программ. Учебное пособие, 2015
2. Фуфаев Э.В., Фуфаева Л.И. Пакеты прикладных программ, 2014.
Задание: В новой книге MS Excel, создайте таблицу-список, согласно варианту. Для списка выполните следующие действия:
– добавить форму;
– установить критерии отбора записей;
– сортировку списка согласно заданным параметрам;
– фильтрацию списка автофильтром и расширенным фильтром.
Вариант 1
Критерии поиска: по полю «Белки» – 2,8.
Параметры сортировки: по полю “Продукты” – по убыванию, затем по полю “Ккал” – по убыванию.
Расширенный фильтр: по полю “Жиры” – меньше 5%.
Вариант 2
Критерии поиска: по полю «Вкус» – кисло-сладкий.
Параметры сортировки: по полю “Соки” – по возрастанию, затем по «Кислотность» – по убыванию.
Расширенный фильтр: по полю “Сахаристость” – более 7 г/100см 
.
Вариант 3
Критерии поиска: по полю «Содержание нерастворимых веществ, %» - более 5%.
Параметры сортировки: по полю «Плоды и ягоды» – по возрастанию, затем по полю «Содержание растворимых веществ, %» – по возрастанию.
Расширенный фильтр: по полю «Содержание плодовой мякоти, %» – более 90%.
Вариант 4
Критерии поиска: по полю «Энергетическая ценность» – 19.
Параметры сортировки: по полю “Продукты” – по убыванию, затем по полю “Клетчатка” – по убыванию.
Расширенный фильтр: по полю “Органические кислоты” – меньше 1.
Вариант 5
Критерии поиска: по полю «Рецептура шоколада «Спорт» – меньше 300.
Параметры сортировки: по полю «Рецептура шоколада «Школьный» – по возрастанию, затем по полю «Сырье» – по возрастанию.
Расширенный фильтр: по полю «Рецептура шоколада «Школьный» – не более 100 кг.
Вариант 6
Критерии поиска: по полю «Вода» – больше 11.
Параметры сортировки: по полю “Продукт” – по возрастанию, затем по полю “Ккал” – по убыванию.
Расширенный фильтр: по полю “Жиры” – больше 20.
Вариант 7
Критерии поиска: по полю «Влага» - более 5%.
Параметры сортировки: по полю «Наименование» – по возрастанию, затем по полю «Азотистые вещества» – по возрастанию.
Расширенный фильтр: по полю «Жиры» – менее 50%.
Вариант 8
Критерии поиска: по полю «Температура хранения» - 0°С.
Параметры сортировки: по полю «Наименование продукта» – по убыванию, затем по полю «Относительная влажность» – по возрастанию.
Расширенный фильтр: по полю «Допустимый срок хранения» – 14 дней.
Вариант 9
Критерии поиска: по полю «Дата кредита»– позже 1.03.08.
Параметры сортировки: по полю «Наименование организации» – по возрастанию, затем по полю «Процентная ставка» – по убыванию.
Расширенный фильтр: по полю «Сумма возврата» – более 3000000 млн.руб.
Вариант 10
Критерии поиска: по полю «Вид шоколада» – горький.
Параметры сортировки: по полю “Марка” – по возрастанию, затем по полю “Содержание какао-продуктов” – по возрастанию.
Расширенный фильтр: по полю “Средняя цена” – более 58 руб.
Порядок выполнения работы:
1. Получить допуск к работе;
2. Запустить Microsoft Excel и выполнить задания;
3. Составить отчет;
4. Ответить на контрольные вопросы.
Содержание отчета:
1. Наименование, цель занятия, задание;
2. Выполненное задание;
3. Ответы на контрольные вопросы.
Контрольные вопросы:
1. Что называется списком в табличном процессоре MS Excel?
2. Опишите алгоритм автоматического поиска записи, по некоторому условию.
3. Для чего применяется сортировка списков?
4. Опишите алгоритм сортировки списка по убыванию.
5. Что такое фильтр? Какие виды фильтров имеются в MS Excel?
6. Опишите алгоритм использования автофильтра.
7. Для чего применяется расширенный фильтр?
8. Опишите алгоритм использования расширенного фильтра.
9. Чем расширенный фильтр отличается от автофильтра?
10. Сформулируйте правила формирования блока критериев в расширенном фильтре.
ПРИЛОЖЕНИЕ
Списком в Excel называют однотабличную базу данных. В этом списке столбцы называются полями, а строки – записями. В Excel понятия список и база данных взаимозаменяемы. Данные, представленные в виде списка можно сортировать, осуществлять выборку по условию, анализировать и представлять результаты в нужном виде.
Создание формы. Форма позволяет просматривать и редактировать по одной записи списка в специальном диалоговом окне.
Алгоритм создания формы:
1) сформировать первую строку таблицы (шапку), заполнить ячейки соответствующими записями;
2) выделить любую ячейку в заглавной строке таблицы и выполнить команду Данные → Форма;
3) В появившемся диалоговом окне ввести данные в каждое поле, согласно таблице (рисунок 1);
4) После нажатия кнопки Добавить, запись из формы будет помещена в список и можно приступать к вводу следующей записи. Для завершения ввода данных нужно нажать кнопку Закрыть.
Поиск записей по критерию. Для того чтобы выполнить автоматический поиск записи, удовлетворяющие некоторому условию нужно:
1) выделить любую ячейку в заглавной строке таблицы и выполнить команду Данные → Форма → Критерии;
2) ввести критерии поиска, по которым следует выполнить поиск;
3) после нажатия кнопки Далее, в полях будут выведены данные из первой записи, удовлетворяющей заданным критериям. Повторное нажатие кнопки Далее, выведет следующую запись удовлетворяющую заданным критериям.
Сортировка списка. Необходимость сортировки записей в списках возникает, обычно, для последующего быстрого поиска информации в списке. В таблице Excel можно выполнить сортировку списка по убыванию 
или по возрастанию 
.
В качестве примера рассмотрим таблицу «Расчет прибыли фирмы». Отсортируем таблицу по двум признакам: первичный – наименование (по алфавиту), вторичный – валовый доход (по убыванию).
Алгоритм выполнения:
1) выделить диапазон A1:G7 и выполнить команду Данные → Сортировка;
2) в появившемся окне Сортировка диапазона, в поле Сортировать по указываем столбец Наименование по возрастанию, а в поле Затем по – Валовый доход по убыванию;
3) в результате получим отсортированную таблицу.
Фильтрация списка. Фильтрация (выборка) данных в таблице позволяет отображать только те строки, содержимое ячеек которых отвечает заданному условию или нескольким условиям. В отличие от сортировки, данные при фильтрации не упорядочиваются, а лишь скрываются те записи, которые не отвечают заданным критериям выборки. Фильтрация данных может выполняться двумя способами: с помощью автофильтра или расширенного фильтра.
Автофильтр. Для использования автофильтра нужно:
– установить курсор внутри таблицы;
– выполнить команду Данные → Фильтр → Автофильтр, у каждой ячейки заголовка появится кнопка 
;
– раскрыть список столбца, по которому будет производиться выборка;
– выбрать значение или условие и задать критерий выборки в диалоговом окне Пользовательский автофильтр.
Для восстановления всех строк исходной таблицы нужно выбрать строку все в раскрывающемся списке фильтра или выбрать команду Данные ® Фильтр® Отобразить все.
Для отмены режима фильтрации нужно установить курсор внутри таблицы и повторно выбрать команду меню Данные ® Фильтр ® Автофильтр (снять флажок).
Расширенный фильтр. Расширенный фильтр позволяет формировать множественные критерии выборки и осуществлять более сложную фильтрацию данных электронной таблицы с заданием набора условий отбора по нескольким столбцам.
Алгоритм использования расширенного фильтра:
1) скопировать первую строку таблицы (шапку) и поместить ее под основной таблицей;
2) указать критерии отбора в столбце, по которому происходит выборка;
3) выполнить команду Данные → Фильтр → Расширенный фильтр;
4) в диалоговом окне Расширенный фильтр включить параметр Скопировать результат в другое место;
5) в поле Диапазон критериев, указать диапазон тех ячеек, где размещается область критериев;
6) в поле Поместить результат в диапазон, указать диапазон содержащий скопированный заголовок таблицы;
7) в результате получим отфильтрованную таблицу.
