Занятие 2. Сводные таблицы , консолидация
Скопируйте на рабочий диск файл Задание9(учет).xls
Откройте таблицу «Задание9 (Учет).xls.
Создайте две копии листа Соискатель, назовите листы Фильтры и Итоги.
На листе Итоги подведите промежуточные итоги общей суммы отработанного времени и заработной платы по отделам.
На листе Фильтры с помощью автофильтра выберите всех сотрудников, имеющих н/з высшее образование.
С помощью автофильтра выберите сотрудников моложе 40 лет.
На листе Фильтры с помощью расширенного фильтра выведите всех сотрудников 1 отдела, имеющих разряд выше 13-го в таблицу с заголовками: Фамилия, Разряд, Зарплата.
С помощью расширенного фильтра выберите экономистов, получивших зарплату больше 12000 в таблицу Фамилия, Отдел, Разряд, Отработано часов, Зарплата.
Создайте сводную таблицу для анализа общей суммы заработной платы и отработанного времени по всем отделам и разрядам.
Перед построением сводной таблицы необходимо убрать все ранее созданные промежуточные итоги и наложенные фильтры. Далее установите курсор в любую ячейку таблицы и выберите команду Данные - Сводная таблица. Для построения сводной таблицы выполните следующую последовательность действий:
Шаг 1. В открывшемся диалоговом окне Мастер сводных таблиц отметьте опцию в списке или базе данных Microsoft Excel, а также вид отчета.
Шаг 2. Далее определите диапазон, с которым будет работать Мастер сводных таблиц, включая заголовки столбцов. Щелкните на кнопке Далее.
Шаг 3. Укажите, куда поместить сводную таблицу (при необходимости можно также изменить Параметры) и щелкните по кнопке Макет для вызова пустого макета (рис. 7 ).
Макет содержит 4 зоны.
Страница – фильтрует данные по значениям выбранных полей. В эту зону помещается кнопка поля, по которому предполагается фильтровать данные.
Строка, Столбец – группируют строки и столбцы для вычисления итогов.
Данные – содержит данные, для которых требуется подведение итогов.
Поля в этой области можно переименовать.
Рис. 7. Макет сводной таблицы
Шаг 4. Определяется лист для размещения сводной таблицы, который переименовывается в лист «Сводная таблица».
Определите, значения каких полей списка будут использоваться в качестве заголовков строк (зона Строка), каких - в качестве заголовков столбцов (зона Столбец) и каких - в качестве данных (зона Данные), по которым следует подвести необходимые итоги. (По умолчанию предлагается просуммировать значения выбранного поля). Для того, чтобы изменить способ обработки данных, по этому полю необходимо дважды щелкнуть по образовавшемуся в зоне Данные полю и выбрать нужную операцию). В каждой зоне может быть несколько кнопок. Для того, чтобы в новой таблице получить только итоговые значения следует все зоны, кроме зоны Данные оставить пустыми.
Далее определяется место, в которое будет помещена разработанная сводная таблица (например, ячейка нового листа). Там же определяются параметры сводной таблицы: следует ли выводить общие итоги по строкам и по столбцам.
После нажатия на кнопку Готово в указанном месте появляется таблица со сводными данными. В левом верхнем углу таблицы располагается кнопка с полем, помещенным в зону Страница. По умолчанию в таблице отображается вся информация по этому полю. Используя выпадающий список значений данного поля в соседней справа ячейке) можно указать значение для фильтрации.
Создайте сводную таблицу для анализа средней, максимальной и минимальной заработной платы по должностям.
Создайте сводную таблицу для анализа средней оплаты за час по отделам и образованию.
Создать рабочую книгу «Консолидация»
На 1 листе создать таблицу «Данные о наличии товаров на складе» по представленному образцу (Рис.8)
Произвести консолидацию данных о наличии товаров на двух складах фирмы по всем полям (суммарная стоимость одноименных товаров);
Произвести консолидацию данных о наличии товаров на двух складах фирмы по полям «Поставщик», «Дата поставки», «Стоимость» (суммарная стоимость товаров от одного поставщика);
Произвести консолидацию данных о наличии товаров на двух складах фирмы по полям «Дата поставки», «Стоимость» (суммарная стоимость товаров, поставленных в одном месяце).
Данные о наличии товаров на складах фирмы
Склад 1 |
| Склад 2 | ||||||
Наименование товара | Поставщик | Дата поставки | Стоимость (тыс. руб) |
| Наименование товара | Поставщик | Дата поставки | Стоимость (тыс. руб) |
Книги | Киев | мар | 55,0 |
| Видео | Киев | мар | 781,0 |
Видео | С.-П. | янв | 425,0 |
| Книга | С.-П. | апр | 59,0 |
Книги | Киев | фев | 16,5 |
| Книга | Москва | май | 122,0 |
Аудио | Москва | мар | 148,0 |
| Видео | Киев | июн | 477,0 |
Видео | С.-П. | апр | 520,0 |
| Аудио | Москва | мар | 356,0 |
Аудио | С.-П. | май | 623,0 |
| Аудио | Москва | апр | 533,0 |
Книги | Москва | июн | 58,0 |
| Видео | С.-П. | мар | 699,0 |
Аудио | Москва | янв | 132,7 |
|
|
|
|
|
Видео | Москва | фев | 455,0 |
|
|
|
|
|
Видео | Москва | апр | 400,0 |
|
|
|
|
|
Рис. 8. Консолдидация. Исходные данные
- Информационные системы в экономике Практкум
- Информационные системы в экономике Практикум
- Введение
- Финансовые вычисления в Excel
- 1.1. Основные понятия
- 1.2. Финансовые функции Excel
- 1.2.1.1. Функция бс
- 1.2.1.2. Функция кпер
- 1.2.1.3. Функция ставка
- 1.2.1.4. Функция пс
- 1.2.1.5. Функция плт
- Практические задания
- 1.3.1. Знятие 1.
- Знятие 2.
- 1.4. Контрольные вопросы
- Анализ данных в Excel
- Средства работы со списками в Excel
- 2.2.1.Сортировка записей
- 2.2.2. Промежуточные итоги
- 2.2.3. Фильтрация
- 2.2.3.1. Автофильтр
- 2.2.3.2. Расширенный фильтр
- 2.2.3.3. Сводные таблицы
- Консолидация
- Практические задания
- Занятие 1. Фильтрация,подведение итогов, таблицы подстановки
- Занятие 2. Сводные таблицы , консолидация
- Создать таблицу «Расход» по образцу Табл. 2.
- Работа с базами данных в Microsoft Access.
- Основные понятия
- Понятия базы данных
- Основные понятие реляционной базы данных
- Основы работы в Microsoft Access
- 3.1.3.1. Таблицы
- 3.1.3.4. Отчеты
- Создать отчет в субд Access можно различными способами:
- 3.1.3.5. Макросы
- Литература
- Интернет источники
- Приложение 1.