 |
Тема: Информационные технологии табличных процессоров. Работа с базой данных в EXCEL. Сортировка, Фильтры.
|
|
|
|
Цель работы:
- изучить возможности для организации данных в Excel в виде списка или базы данных;
- освоить технологию обработки cписков в Excel;
- научиться извлекать определенные записи и поля из баз данных.
Теоретические сведения
Базы данных как способ хранения и обработки различной информации играют в настоящее время огромную роль. В базах данных хранят сведения о клиентах, заказах, справочники адресов и телефонов, различного рода информацию о туристических агентствах и предлагаемых услугах и т. д.
Для учета данных о сотрудниках на предприятиях используют самые разнообразные методы. В одних организациях существуют журналы учета, куда информация вносится вручную, в других применяются классические базы данных для учета кадров, в третьих используются СУБД Access. Но в большинстве случаев на небольших предприятиях учет данных о сотрудниках ведется в электронных таблицах Microsoft Excel.
Приложение Microsoft Excel обладает богатыми встроенными средствами для обработки и анализа данных. Аналогом простой базы данных в Excel служит список.
Список- это группа строк таблицы, содержащая связанные данные. Отличительной особенностью списка является то, что каждый его столбец содержит однотипные данные, например, перечень цехов, продукции и т. д. (рисунок 8.1).
Рисунок 8.1 База данных. Пищевая фабрика
Если провести аналогию между списком и табличной базой данных, то столбцы списка являются полями базы данных, а его строки - записями. Считается, что первая строка списка является его заголовком и содержит названия столбцов списка. Заголовок должен иметь на листе электронных таблиц горизонтальную ориентацию. Заголовки применяются Excel при составлении отчетов, а также при поиске и организации данных. Шрифт, размер шрифта, выравнивание и другие параметры форматирования, присвоенные заголовкам столбцов списка, должны отличаться от параметров, назначенных для строк данных. В списке не должно быть пустых строк и столбцов.
|
|
|
Проверка данных при вводе. Если с файлом работает сразу несколько пользователей, желательно контролировать тип вводимой ими информации и свести к минимуму ошибки ввода. В Excel выполнение подобных условий проверяется при помощи средства, которое называется проверкой ввода. Для этого надо:
- выделить ячейки столбца, для которого устанавливается проверка ввода;
- на ленте Данные в группе Работа с данными выбрать команду Проверка данных;
- на вкладке Параметры в области Условие проверки выбрать Тип данных: Любое значение (используется для отмены проверки ввода), Целое число, Действительное, Список, Дата, Время, Длина текста и Другой (формат, для которого можно задать собственную формулу, например,"м"or"ж"). При выборе значения внизу окна появляются дополнительные поля для ввода условий или ограничений – например, минимального и максимального допустимого значения;
- на вкладке Сообщение для ввода можно установить флажок Отображать подсказку, если ячейка является текущей и ввести сообщение, чтобы оно появлялось на экране при выделении ячеек;
- на вкладке Сообщение об ошибке можно установить флажок Выводить сообщение об ошибке, чтобы задать тип сообщения об ошибке, которое появится при вводе в ячейку недопустимого значения.
Сортировка данных. Команда Сортировка позволяет переставить записи в другом порядке на основании значений одного или нескольких столбцов. Записи сортируются возрастанию/убыванию или по выбранному пользователю порядке (например, по месяцам).
Чтобы отсортировать список надо:
- установить курсор в ячейку списка;
|
|
|
- выполнить команду Сортировка на ленте Данные в группе Сортировка и Фильтр;
- в диалоговом окне Сортировка выбрать поле, по которому будет происходить сортировка; тип сортировки (по значению, цвет ячейки, цвет шрифта, значок ячейки) и порядок (по возрастанию, убыванию, настраиваемый).
Выбор настраиваемого порядка позволяет задать нестандартный порядок сортировки. Для этого надо в диалоговом окне Списки выбрать НОВЫЙ СПИСОК, в поле Элементы списка ввести значения, образующие пользовательский порядок сортировки, после чего последовательно выбрать кнопки Добавить и ОК (рисунок 8.2).
Рисунок 8.2 Создать свой список сортировки
Промежуточные итоги в БД. Для организации списков используют команду Промежуточные итоги на ленте Данные в группе Структура, которая позволяет:
- упорядочить список посредством группировки записей с выводом промежуточных итогов, средних значений или другой вспомогательной информации;
- выводить итоговую сумму;
- отображать список в виде структуры, что позволяет разворачивать и сворачивать разделы с помощью щелчка мыши.
Перед вызовом команды Итоги список обязательно надо отсортировать по полю, которое будет использоваться для группировки.
Режим структуры, в котором оказывается список после выполнения команды Итоги, позволяет просматривать различные части списка с помощью кнопок, расположенных на левом поле (рисунок 8.3).
Рисунок 8.3 Просмотр списка в режиме структуры.
Кнопки, расположенные в верхнем левом углу, определяют количество выводимых уровней данных. Кнопки со значками "+" и "-" предназначены для свертывания \ развертывания отельных групп.
Чтобы удалить промежуточный и окончательные итоги, надо повторно выполнить команду Промежуточные итоги, а затем щелкнуть по кнопке Убрать все.
Автофильтр. Отфильтровать список - значит показать только те записи, которые удовлетворяют заданному критерию.Чтобы установить или убрать автофильтр надо на ленте Данные в группе Сортировка и фильтр выбрать команду Фильтр. После этого нажать кнопку со стрелкой возле названия какого-либо поля, чтобы раскрыть список его элементов и выбрать отображаемые значения или задать условие отбора. На экране появятся только те записи, которые отвечают заданному условию. В случае необходимости можно продолжить фильтрацию, нажимая кнопки со стрелками на других полях.
|
|
|
Показать все записи по отфильтрованному полю, не убирая фильтр, можно выбрав в списке фильтра критерий Снять фильтр с….
Показать все записи по всем полям, не убирая фильтр, команда Очистить.
Для данных разного типа существуют дополнительные автофильтры, которые находятся в списке критериев Текстовые фильтры, Числовые фильтры, Фильтры по дате и т.д.
Если выделить какое-то числовое поле (например, Цена), а в списке критериев выбрать Числовые фильтры, то появится список дополнительных фильтров (рисунок 8.4), которые позволяют:
- задать критерий в виде неравенства – критерии равно, не равно, больше, больше или равно, меньше, меньше или равно, между;
- вывести первые N значений – критерий Первые 10: после выбора в списке Числовых фильтров команду Первые 10…, необходимо в появившемся окне указать число значений (N), а также способ вычисления: количество элементов списка, % от количества элементов;
- определить условие по среднему значению в указанном столбце – критерии Выше среднего, Ниже среднего;
- самостоятельно задаваемый фильтр – критерий Настраиваемый фильтр.
Рисунок 8.4. Дополнительные числовые фильтры
Настраиваемый фильтр позволяет задать критерии из одного или двух условий. Простое условие состоит: из имени поля (атрибута); варианта условия (равно, не равно, больше, меньше, больше или равно, меньше или равно; начинается с, не начинается с, заканчивается на или не заканчивается на; содержит, не содержит); слова или числа для сравнения. Сложное условие состоит из двух простых, соединенных союзами И или ИЛИ. При написании значений в условиях сравнения в фильтрах можно использовать подстановочные знаки (Таблица 8.1).
Таблица 8.1 Подстановочные знаки.
| Знак | Значение |
| ? | один любой символ |
| * | любое количество символов |
| ~ | используют, когда в тексте надо найти подстановочные знаки (символы «?», «*» или «~») |
Расширенный фильтр. Расширенный фильтр позволяет сформировать более сложные условия, в том числе состоящие из более, чем двух условий. Перед вызовом команды Расширенный фильтр, необходимо сформировать критерии. Для удобства лучше формировать критерии на отдельном листе (можно дать ему имя, например, Критерии) и давать критериям имена Кр1, Кр2 и т.д. Основное правило: если критерии связаны между собой операцией И, то они должны располагаться в одной строке, а если ИЛИ, то в разных. После формирования критерия, вызывают расширенный фильтр: на ленте Данные в группе Сортировка и фильтр команда Дополнительно. Восстановить исходный список можно выбрав на ленте Данные в группе Сортировка и фильтр команду Очистить.
|
|
|
Задание 1. Создать базу данных (рисунок 8.5).
Рисунок 8.5 База данных
Задание 2. Выполнить задания по проверке данных (рисунок 8.6).
Рисунок 8.6. Задания. Проверка ввода данных
Задание 3. Создать базу данных о работе пищевой фабрики за один месяц в соответствии с таблицей 8.2:
Таблица 8.2 База данных. Пищевая фабрика
| Цех | Продукция | Цена | Количество по плану | Фактическое количество |
| Макаронный | Спагетти | 2,2 | ||
| Кондитерский | Торт «Сказка» | 7,5 | ||
| Консервный | Зеленый горошек | 2,3 | ||
| Консервный | Кетчуп | 2,2 | ||
| Макаронный | Лапша | 2,1 | 2,5 | |
| Макаронный | Макароны | 1,8 | ||
| Консервный | Майонез | 1,4 | ||
| Кондитерский | Бисквит «Лето» | 4,1 | ||
| Кондитерский | Зефир | 5,5 | ||
| Макаронный | Рожки | 1,6 | ||
| Консервный | Паштет печеночный | 1,8 |
Задание 4. Выполнить задания в соответствии с вариантом, выданным преподавателем (таблица 8.3).
Таблица 8.3
| Номер варианта | Номера заданий |
| 1,2,3,6,7,8,9,11,13,15 | |
| 1,2,4,6,7,8,9,12,14,16 | |
| 1,2,5,6,7,8,9,11,15,17 | |
| 1,2,3,6,7,8,9,12,16,18 | |
| 1,2,4,6,7,8,9,11,17,19 | |
| 1,2,5,6,7,8,9,12,18,20 | |
| 1,2,3,6,7,8,9,11,19,13 | |
| 1,2,4,6,7,8,9,12,20,14 | |
| 1,2,5,6,7,8,9,11,13,15 | |
| 1,2,3,6,7,8,9,12,14,16 |
Задания:
1. Подсчитать процент выполнения плана.
2. Подсчитать стоимость изготовленной продукции каждого наименования.
3. Отсортировать сведения о работе пищевой фабрики в алфавитном порядке цехов, внутри каждого цеха в порядке возрастания цены продукции.
4. Отсортировать сведения о работе пищевой фабрики в алфавитном порядке цехов, внутри каждого цеха в порядке убывания цены продукции.
5. Отсортировать сведения о работе пищевой фабрики в алфавитном порядке цехов, внутри каждого цеха в алфавитном порядке продукции.
6. Подсчитать суммарную стоимость изготовленной продукции
7. Найти максимальное количество по плану.
8. Найти минимальную цену за единицу продукции.
9. Подсчитать средний процент выполнения плана.
|
|
|
10. Используя автофильтр вывести на экран сведения о цехах, процент выполнения плана в которых больше 100% и меньше 130%.
11. Используя автофильтр вывести на экран сведения о трех цехах, план выполнения которых наибольший.
12. Используя автофильтр вывести на экран сведения о двух цехах, значение выполнения плана которых было наименьшим.
13. Используя расширенный фильтр, выдать сведения о той продукции макаронного цеха, план производства которой был перевыполнен, а стоимость была меньше 100 (цех, продукция, процент выполнения плана, стоимость изготовленной продукции).
14. Используя расширенный фильтр, выдать сведения о той продукции консервного цеха, план производства которой был недовыполнен или цена продукции меньше 2 (цех, продукция, процент выполнения плана, цена продукции).
15. Используя расширенный фильтр, выдать сведения о той продукции консервного цеха, план производства которой был недовыполнен или цена единицы продукции была больше 3 (цех, продукция, процент выполнения плана, цена продукции).
16. Используя расширенный фильтр, выдать сведения о той продукции цехов, план производства которой был недовыполнен или цена единицы продукции была больше 2,5 (цех, продукция, процент выполнения плана, цена продукции).
17. Используя расширенный фильтр, выдать сведения о той продукции макаронного цеха, план производства которой был перевыполнен или фактическое количество больше 200 (цех, продукция, процент выполнения плана, фактическое количество).
18. Используя расширенный фильтр, выдать сведения о той продукции кондитерского цеха, план производства которой был перевыполнен или фактическое количество больше 150 (цех, продукция, процент выполнения плана, фактическое количество).
19. Используя расширенный фильтр, выдать сведения о той продукции консервного цеха, план производства которой был перевыполнен или количество по плану больше 450 (цех, продукция, процент выполнения плана, количество по плану).
20. Используя расширенный фильтр, выдать сведения о той продукции консервного цеха, план производства которой был недовыполнен или цена продукции меньше 2 (цех, продукция, процент выполнения плана, цена продукции).
Задание 5. Создать таблицу 8.4.
Таблица 8.4.
| Дата | Номер магазина | Фамилия товароведа | Наименование товара | Цена единицы товара | Количество товара (единиц) | Общая цена товара |
| 08.04.99 | Семенова И.И. | Пальто мужское | ||||
| 07.04.99 | Семенова И.И. | Сапоги женские | ||||
| 05.04.99 | Чебик Ф.А. | Платье женское | ||||
| 03.04.99 | Петренко А.И. | Детские колготки | ||||
| 03.04.99 | Петренко А.И. | Пальто мужское | ||||
| 03.04.99 | Петренко А.И. | Пальто мужское | ||||
| 05.04.99 | Чебик Ф.А. | Платье женское | ||||
| 08.04.99 | Костенко К.И. | Плащ женский | ||||
| 09.04.99 | Корбунова Н.Н. | Сапоги женские | ||||
| 04.04.99 | Огурцов П.Н. | Туфли мужские | ||||
| 04.04.99 | Огурцов П.Н. | Туфли мужские | ||||
| 04.04.99 | Огурцов П.Н. | Туфли мужские | ||||
| 03.04.99 | Петренко А.И. | Пальто мужское | ||||
| 08.04.99 | Костенко К.И. | Детские колготки | ||||
| 08.04.99 | Костенко К.И. | Плащ женский | ||||
| 09.04.99 | Корбунова Н.Н. | Плащ женский | ||||
| 04.04.99 | Огурцов П.Н. | Детские колготки | ||||
| 04.04.99 | Чебик Ф.А. | Зонт женский | ||||
| 05.04.99 | Чебик Ф.А. | Детские колготки | ||||
| 05.04.99 | Чебик Ф.А. | Платье женское | ||||
| 03.04.99 | Семенова И.И. | Плащ женский | ||||
| 09.04.99 | Корбунова Н.Н. | Зонт женский | ||||
| 09.04.99 | Корбунова Н.Н. | Сапоги женские | ||||
| 06.04.99 | Семенова И.И. | Детские колготки |
Задание 6. Выполнить задания по сортировке значений таблицы 8.4.
1. Отсортировать по фамилии товароведа по возрастанию сортировать по, по наименованию товара по возрастанию затем по, по количеству товара по убыванию в последнюю очередь по
2. Отсортировать по дате по убыванию сортировать по, по номеру магазина по возрастанию затем по, по наименованию товара по убыванию в последнюю очередь по.
3. Отсортировать по номеру магазина по убыванию сортировать по, по фамилии товароведа по возрастанию затем по, по цене единицы товара по убыванию в последнюю очередь по.
4. Отсортировать по наименованию товара по убыванию сортировать по, по дате по возрастанию затем по, по общей цене товара по убыванию в последнюю очередь по.
5. Отсортировать по фамилии товароведа по алфавиту, затем по цене единицы товара по убыванию, в последнюю очередь по дате по возрастанию.
6. Отсортировать по номеру магазина по возрастанию, затем по наименованию товара по алфавиту, в последнюю очередь по количеству товара по убыванию.
7. Отсортировать по цене единицы товара по возрастанию, затем по фамилии товароведа по алфавиту в последнюю очередь по дате по убыванию.
Контрольные вопросы.
1. Для каких целей применяются электронные таблицы?
2. Для чего необходимы базы данных?
3. Какие возможности предоставляет программа Microsoft Excel для работы с базами данных?
4. Что такое запись?
5. Что такое поле?
6. Что такое тип данных? Зачем необходимо указывать типы полей?
7. Данные каких типов могут быть записаны в ячейку?
8. Как установить проверку вводимых в список значений?
9. Как записываются абсолютные и относительные адреса ячеек?
10. Что такое сортировка? Как отсортировать список по двум и более ключам?
11. Что такое фильтр?
12. Какие виды фильтров вы знаете?
13. В чем отличие сортировки списка от фильтрации списка?
14. Что такое расширенный фильтр?
15. Как сформировать критерий для расширенного фильтра?
Литература[3, 7, 8].
Лабораторная работа №9
Тема: Информационные технологии табличных процессоров. Анализ данных в EXCEL. Моделирование расчетов «Что-если…».
Цель работы:
- научиться осуществлять в Excel анализ данных с использованием аппарата прогнозирования развитие некоторых ситуаций;
- моделировать расчеты “Что-Если”с использованием команды Подбора параметров.
Теоретические сведения
Команда Подбор параметра используется, когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата. Ячейка с известным результатом, в которой находится формула, называется целевой ячейкой. Исходное значение, которое нужно найти, называется параметром.
Для вызова команды Подбор параметра нужно сделать активной вкладку Данные, в группе команд Работа с данными нажать на кнопку Анализ “что-если” 
, а затем в списке этой кнопки выбрать пункт Подбор параметра.
Задание 1. В новой книге на Листе1 в интервале ячеек А1:Н20 создайте таблицу 1 (рисунок 9.1).
Рисунок 9.1 Сведения о работе рыбообрабатывающего завода
Задание 2. На Листе 2 в интервале ячеек А1:F16 создайте Таблицу 2 (рисунок 9.2).
Рисунок 9.2 Сведения о работниках рыбообрабатывающего завода
Подсчитайте итоговую сумму окладов по всему заводу.
Задание 3. Необходимо рассчитать ра з мер квартальной премии, квартальной заработной платы, отчислений в пенсионный фонд за квартал по каждому работнику завода, а также по всему заводу.
Условием начисления премии является перевыполнение плана по выпуску продукции за квартал. Причем рабочим премия начисляется в размере 20% от суммы окладов за квартал, остальным работающим - 10%. Отчисления в пенсионный фонд за квартал - 2% от квартальной заработной платы.
Для выполнения задания проделайте следующие действия:
- на Листе 2 в ячейке G2 задайте текст - Квартальный оклад;
- в ячейке G3 формулу: =3*D3;
- формулу скопируйте в интервал ячеек G4:G16;
- в ячейке Н2 задайте текст – Премия;
- задайте текст в ячейке А19 - Процент начисления премии, в ячейке А20 – Рабочим, в ячейке В20 – Всем служащим;
- введите значения: в ячейку А21 – 20%, в ячейку В21 - 10%;
- в ячейку С19 введите текст - % отчисления в пенсионный фонд, объедините ячейку С19 и С20;
- в ячейку С21 задайте значение 2%;
- ячейке Н3 задайте с помощью Мастера функция формулу для расчета премии:
=ЕСЛИ(Лист1!$K$21>=100%;ЕСЛИ(C3="Рабочий";G3*$A$21;G3*$B$21);0)
В этой формуле:
- первый аргумент: Лист1!$K$21>=100% - проверка выполнения квартального плана;
- второй аргумент – если план перевыполнен, то начисляется премия. Начисление премии производится по формуле: ЕСЛИ(C3="Рабочий";G3*$A$21;G3*$B$21);
Здесь при начислении премии используется вложенная функция Если, так как премия рабочим и другим служащим начисляется с разным процентом.
- третий аргумент - 0, так как, если план не выполнен, то премия не начисляется.
Диалоговое окно Мастера функций при задании аргументов вложенной в формулу функции Если будет иметь следующий вид (рисунок 9.3):
Рисунок 9.3 Диалоговое окно Мастера функций Если
Диалоговое окно Мастера функций при задании аргументов первой функции Если будет иметь следующий вид (рисунок 9.4):
Рисунок 9.4 Диалоговое окно Мастера функций Если
- в ячейке I2 задайте текст - Квартальная зарплата;
- в ячейку I3 введите формулу: =G3+H3, которую скопируйте в интервал ячеек I4:I16;
- в ячейке J2 задайте текст - Отчисления в пенсионный фонд;
- в ячейку J3 введите формулу: =I3*$C$21, которую скопируйте в интервал ячеек J4:J16;
- подсчитайте итоговые значения по квартальным окладам, премии, квартальной зарплате, отчислениям в пенсионный фонд;
Задание 4. На рыбообрабатывающем заводе необходимо уменьшить фонд квартальной заработной платы на 1000 гривен, уменьшив общую сумму месячных окладов, оставив неизменными проценты начисления премии.
Задача сводится к применению инструмента Подбор параметра. Длявыполнениязадания проделайте следующие действия:
- скопируйте интервал ячеек А2:I16 на новый лист, начиная с ячейки А2;
- переименуйте новый лист, дав ему имя Подбор параметра;
- скопируйте интервал ячеек А19:В21 листа 2 в соответствующий интервал ячеек листа Подбор параметра;
- в соответствующие ячейки листа Подбор параметра введите поясняющий текст (рисунок 9.5):
Рисунок 9.5 Описание вычислений подбора параметра
- в ячейки В27:D27 занесите соответственно значения сумм окладов рабочих, служащих и общую сумму. Для этого:
1) выделите базу данных (интервал ячеек A2:I16);
2) используя команду Автофильтр, отберите записи базы данных, относящиеся к рабочим;
3) для отобранных записей в ячейке D17 подсчитайте сумму месячных окладов рабочих. Значение этой суммы сумму занесите в ячейку В27;
4) отобразите все записи, а затем отберите записи, относящиеся к остальным служащим (работников, не относящихся к рабочим – то есть не рабочие);
5) для отобранных записей в ячейке D17 подсчитайте значение суммы месячных окладов служащих. Эту сумму занесите в ячейку C27;
6) снимите действие фильтра;
7) в ячейке D17 будет находиться итоговое значение суммы месячных окладов рабочих и служащих. Занесите это значение в ячейку D27;
8) в ячейке I17 будет находиться итоговое значение суммы квартальной зарплаты рабочих и служащих.
- так как премия для рабочих и всех остальных служащих начисляется с разным процентом, то вклад премии в общий фонд зарплаты для рабочих и служащих будет разный. Так как премия рассчитывается от окладов, определим долю окладов рабочих и долю окладов служащих в общей сумме окладов. Для этого:
1) в ячейке B28 задайте формулу для подсчета доли суммы месячных окладов рабочих в общей сумме: =B27/$D27;
2) скопируйте формулу из ячейки B28 в интервал ячеек C28:D28.
- отформатируйте информацию в ячейках В28:D28, задав процентный формат;
- в ячейку А33 (эта ячейка будет целевой) занесите формулу для подсчета квартального фонда зарплаты:
=3*B33+3*B33*(A21*B28+B21*C28)
- ячейка В33 будет являться ячейкой параметра. Целевая ячейка связана с ячейкой параметра формулой;
- сделайте активной вкладку Данные. В группе команд Работа с данными из списка кнопки Анализ “что-если” выберите пункт Подбор параметра;
- в диалоговом окне Подбор параметра задайте адрес целевой ячейки, ее значение, а также адрес ячейки параметра (рисунок 9.6):
Рисунок 9.6 Подбор параметра
В ячейке В33 будет рассчитано значение параметра - сумма месячных окладов. Отформатируйте это значение, оставив два знака после запятой (рисунок 9.7):
Рисунок 9.7 Вычисления подбора параметра
Контрольные вопросы.
1. Назначение команды Подбор параметра? Привести пример.
2. Что такое целевая ячейка при использовании команды Подбор параметра?
3. Что такое ячейкапараметра при использовании команды Подбор параметра?
4. Как должны быть связаны между собой целевая ячейка и ячейка параметра?
Литература[3, 7, 8]
Лабораторная работа №10
|
|
|
