Лабораторная работа 9 Использование форм и Консолидация данных
Цель работы:
научиться создать формы для ввод данных в список Excel, изучить механизмы подведения итогов посредством объединения (консолидирования) данных разных источников.
Задание 9.1.
Создайте на листе 1 базу данных, содержащую сведения о клиентах фирмы, предлагаемых товарах и выполненных заказах (рис. 9.1) и сохраните ее в своей папке под именем Лабораторная работа 9.
Рис. 9.1. Данные таблицы Клиенты
Рекомендации по выполнению:
1. Присвойте листу 1 имя Клиенты.
2. Выполнить команду: Файл → Параметры → Панель быстрого доступа. В поле Выбрать команды из выбрать Команды не на ленте, в нижней части поля найти и выделить Форма…, нажать кнопку экрана Добавить (рис. 9.2). Сохранить изменения, нажав кнопку ОК.
Рис. 9.2. Настройка панели быстрого доступа
После этого на панели быстрого доступа появиться кнопка создания форм (рис. 9.3).
Рис. 9.3. Вид панели быстрого доступа и кнопки Форма
3. Введите данные, представленные на рис. 9.1.
4. Выделите ячейки A1:E4.
5. На панели быстрого доступа нажмите кнопку Форма (рис. 9.4).
Рис. 9.4. Форма Клиенты
6. В диалоговом окне Клиенты, используя кнопку Добавить, введите дополнительную информацию (рис. 9.5).
Рис. 9.5. Данные таблицы Клиенты
После ввода последней записи нажмите кнопку Закрыть.
7. Отформатируйте таблицу. Для шапки установите выравнивание по центру и полужирный шрифт, фон – светло-желтый, цвет шрифта – синий.
8. Отсортируйте данные по наименованиям организаций в алфавитном порядке.
Задание 9.2.
Создайте на новом листе таблицу, используя форму, и присвойте листу 2 имя Товары (рис. 9.6).
Рис. 9.6. Данные таблицы Товары
Отформатируйте таблицу аналогичным образом.
Задание 9.3.
Перейдите на новый лист и присвойте ему имя Заказы. Сформируйте таблицу Заказы.
Рекомендации по выполнению:
1. Введите заголовки столбцов в таблицу и отформатируйте их по образцу (рис. 9.7). Для первой строки установите светло-желтый фон и синий цвет шрифта.
Рис. 9.7. Шапка таблицы Заказы
2. Присвойте столбцам A-G следующие имена:
А |
B |
C |
D |
E |
F |
G |
Дата |
Заказ |
НомерЗ |
ТоварЗ |
Количество |
ЦенаЗ |
Сумма |
Для этого:
выделить столбец А на листе заказы;
выполнить команду Формулы (Определенные имена) → Присвоить имя;
указать значения в полях (рис. 9.8):
Имя – Дата;
Область – Книга;
Диапазон – Заказы!$A:$A.
Рис. 9.8. Присвоение диапазону ячеек имени
Аналогично присвоить имена оставшимся столбцам из таблицы.
При необходимости удалить имя диапазона или внести изменения используется команда: Формулы (Определенные имена) → Диспетчер имен.
3. Перейдите на лист Товары и присвойте столбцам следующие имена:
А |
B |
C |
Номер |
Товар |
Цена |
4. Перейдите на лист Клиенты и присвойте столбцу А имя Фирма.
5. Перейдите на лист Заказы.
6. Введите в ячейку D2 формулу, обеспечивающую автоматическое заполнение поля Наименование товара при вводе его кода в поле Номер товара. Используйте следующую формулу:
=ЕСЛИ($C2="";"";ПРОСМОТР($C2;Номер;Товар))
Введите в ячейку F2 формулу, обеспечивающую автоматическое заполнение поля Цена при вводе его кода в поле Номер товара. Используйте следующую формулу:
=ЕСЛИ($C2="";"";ПРОСМОТР($C2;Номер;Цена))
Введите в ячейку G2 формулу, обеспечивающую автоматическое заполнение поля Сумма при вводе количества в поле Количество. Используйте следующую формулу:
=ЕСЛИ(ИЛИ($E2="";$F2="");"";E2*F2)
7. Скопируйте формулы в ячейках D2, F2 и G2 вниз.
Заполните таблицу следующей информацией, вводя данные только о дате, номере заказа, номере товара и его количестве. Если формулы были введены правильно, то информация на экране должна соответствовать приведенной ниже:
Рис. 9.9. Результат формирования таблицы Заказы
Примечание: для значений столбца В установить формат ячеек – Текстовый.
Задание 9.4.
Создайте сводную таблицу для анализа данных заказов, используя Мастер сводных таблиц.
Рекомендации по выполнению:
1. Создайте лист и присвойте ему имя Таблица.
2. В меню Вставка выберите команду Сводная таблица.
3. Укажите диапазон ячеек для анализа. Для этого активизируйте поле Таблица или диапазон, перейдите на лист Заказы и выделите диапазон А1:G11.
4. Выберите расположение На существующий лист и нажмите ОК.
5. Создание структуры сводной таблицы:
выделите на панели Список полей сводной таблицы элемент Номер заказа, щелкните на нем левой клавишей и выберите из выпадающего списка пункт Добавить в названия столбцов;
выделите поочередно на панели Список полей сводной таблицы элементы Наименование товара и Количество, затем выберите из выпадающего списка пункт Добавить в названия строк;
выделите на панели Список полей сводной таблицы элемент Сумма, затем выберите из выпадающего списка пункт Добавить в значения.
Рис. 9.10. Сводная таблица
6. Создайте диаграмму реализации товаров по заказам:
подготовьте данные сводной таблицы для создания диаграмм, отражающих объем продаж того или иного товара по заказам. Так как сводная таблица содержит и промежуточные итоги, и некоторые данные, которые не нужно выводить на диаграмму, их необходимо скрыть. Для этого сверните все значения в сводной таблице, нажав на кнопки ;
выделите диапазон ячеек A2:E10;
вставьте объемную гистограмму (рис. 9.11).
Рис. 9.11. Диаграмма реализации товаров по заказам
Задание 9.5.
Консолидация данных по расположению.
Создать новую книгу и сохранить ее под именем Лабораторная работа 9-Консолидация. Сформировать и заполнить таблицу Ведомость оплаты труда (рис. 9.12).
Рис. 9.12. Таблица Ведомость оплаты труда
В ячейки E4, F4, G4, H4, I4, J4, K4 вставить формулы (рис. 9.13):
Надбавка: Оклад * 0,1;
Премия: Оклад *0,25;
Начислено: Оклад + Надбавка + Премия;
Подоходный налог: Начислено * 0,12;
ФСЗН: Начислено * 0,01;
Удержано: Подоходный налог + ФСЗН;
К выдаче, тыс. руб.: Начислено – Удержано.
Рис. 9.13. Формулы для вычислений по оплате труда
Скопировать формулы диапазона E4: K4 на остальные ячейки списков.
Переименовать лист 1 в Ведомость.
Сохранить введенные данные.
Рекомендации по выполнению:
1. В качестве таблицы источника выберем лист Ведомость.
2. Скопировать шапку таблицы на лист 2 и лист 3. Листы назвать соответственно Октябрь и Ноябрь.
3. Вернуться на лист Ведомость. Выделить шапку таблицы (диапазон А3:К3). Используя команду Главная (Редактирование) → Сортировка и фильтр → Фильтр выбрать из таблицы записи, относящиеся к месяцу октябрю (рис. 9.14).
Рис. 9.14. Выбор записей из таблицы
4. Выделить отфильтрованные записи (диапазон А5:К8) и скопировать их на лист Октябрь в соответствующие ячейки.
5. Аналогично сформировать значения для месяца ноября.
6. В таблицу Ведомость за ноябрь добавить одну запись для сотрудника Потапова А.П., чтобы число их в каждой таблице было одинаковое.
7. Из таблиц на листах Октябрь и Ноябрь удалить столбец Стаж работы и Месяц.
8. Внести изменения в названия таблиц:
на листе Октябрь – Ведомость за октябрь (рис. 9.15);
Рис. 9.15. Ведомость за октябрь
на листе Ноябрь – Ведомость за ноябрь (рис. 9.16).
Рис. 9.16. Ведомость за ноябрь
9. Выполнить консолидацию данных по расположению:
добавить в книгу новый лист Консол. по распол.;
активизировать ячейку А1 листа Консолидация и выполнить команду Данные (Работа с данными) → Консолидация;
в открывшемся окне Консолидация заполняются поля (рис. 9.17):
Функция – автоматически отобразиться значение Сумма, которое при необходимости можно изменить;
Ссылка – перейти на лист Октябрь и выделить диапазон ячеек, занимаемый первым списком – A3:I7. Нажать кнопку Добавить;
далее перейти на лист Ноябрь и выделить диапазон, занимаемый вторым списком, A3:I7. Нажать кнопку Добавить;
установим флажки на опциях: подписи верхней строки и значения левого столбца. Нажать ОК.
Рис. 9.17. Параметры консолидации
10. Получили таблицу консолидированных данных. Как видно, структура таблицы не изменилась, а значения в ячейках просумми- рованы (рис. 9.18).
Рис. 9.18. Консолидация данных по расположению
11. Сохранить файл под именем Лаб. работа 9-Консол. по распол.
Задание 9.6.
Консолидация по категориям.
Для консолидации данных по категориям использовать файл Лабораторная работа 9-Консолидация.
Рекомендации по выполнению:
1. Открыть файл Лабораторная работа 9-Консолидация. В качестве таблицы источника выберем лист Ведомость.
2. Скопировать шапку таблицы на лист 2, лист 3, лист 3. Листы назвать соответственно Сентябрь, Октябрь и Ноябрь.
3. Вернуться на лист Ведомость. Выделить шапку таблицы (диапазон А3:К3). Используя команду Главная (Редактирование) → Сортировка и фильтр → Фильтр выбрать из таблицы последовательно записи, относящиеся к месяцам сентябрь, октябрь и ноябрь и скопировать отфильтрованные записи на листы Сентябрь, Октябрь и Ноябрь в соответствующие ячейки.
4. Из таблиц на листах Сентябрь, Октябрь и Ноябрь удалить столбец Стаж работы и Месяц.
5. Внести изменения в названия таблиц:
на листе Сентябрь – Ведомость за сентябрь;
на листе Октябрь – Ведомость за октябрь;
на листе Ноябрь – Ведомость за ноябрь.
Данные в источниках дополняться не будут. Количество записей в каждой из таблиц отличается.
6. Добавить в книгу новый лист Консол. по категор. Выполнить консолидацию всех списков по описанной выше технологии. Получим таблицу консолидированных данных (рис. 9.19).
Рис. 9.19. Консолидация данных по категориям
7. Сохранить файл под именем Лаб. работа 9-Консол. по категор.
Задание 9.7.
Консолидация списков с разным числом полей.
При консолидации данных из списков с разным числом полей, дополнительные поля присоединяются справа, например структура списков источников.
Рекомендации по выполнению:
1. Открыть файл Лабораторная работа 9-Консол. по категор.
2. Сохранить файл под именем Лаб. работа 9-Консол. по полям.
3. На листах Октябрь и Ноябрь удалить в таблицах столбцы Подоходный налог, ФСЗН, Удержано, К выдаче, тыс. руб.
4. Добавить в книгу новый лист Консол. по полям. Выполнить консолидацию всех списков по описанной выше технологии. Для консолидации выбрать данные:
на листе Сентябрь выбрать диапазон A3:I4;
на листе Октябрь выбрать диапазон A3:E7;
на листе Ноябрь выбрать диапазон A3:E6.
5. Получим таблицу консолидированных данных (рис. 9.20).
Рис. 9.20. Консолидация данных с разным числом полей