Связь и консолидация листов

MS Excel предоставляет возможность объединять и связывать рабочие листы или файлы электронных таблиц таким образом, чтобы в результате ввода значения в одну таблицу, изменялось содержимое другой таблицы.

Упражнение 3

Задание 1

Вставить 3 листа, переименовать их соответственно: Январь, Февраль и Март.

На листе Январь сформировать таблицу:

 

Январь
Город	Выручка за январь
Москва	40 000 000,00р.
Ст.- Петербург	50 000 000,00р.
Новосибирск	35 000 000,00р.
Киев	30 000 000,00р.
Минск	20 000 000,00р.
Итого:	175 000 000,00р.

 

Задать столбцу Выручка за январь денежный формати найти сумму, используя кнопку Автосумма .

На листе Февраль сформировать таблицу:

Февраль
Город	Выручка за февраль
Москва	25 000 000,00р.
Ст.- Петербург	55 000 000,00р.
Новосибирск	40 000 000,00р.
Киев	35 000 000,00р.
Минск	25 000 000,00р.
Итого:	180 000 000,00р.

 

На листе Март сформировать таблицу:

 

Март
Город	Выручка за март
Москва	55 000 000,00р.
Ст.- Петербург	55 000 000,00р.
Новосибирск	40 000 000,00р.
Киев	35 000 000,00р.
Минск	25 000 000,00р.
Итого:	210 000 000,00р.

 

Перейти на следующий лист, переименовать его Квартал_1. Скопировать итоговую выручку за январь месяц (Ctrl+C) и через Специальную вставку/Вставить связь сформируйте таблицу Итоги за квартал.

 

Итоги за квартал

 

Квартал 1	Выручка за квартал
Месяц
январь	175 000 000,00р.
февраль	180 000 000,00р.
март	210 000 000,00р.
Итого за квартал	565 000 000,00р.

 

Измените в любой таблице величину выручки, посмотрите, что произошло в итоговой таблице Итоги за квартал.

 

Упражнение 4

Консолидация данных в электронных таблицах

Консолидация позволяет объединять данные из областей источников и выводить их в область назначения. При консолидации данных могут использовать различные функции: суммирования, расчета среднего арифметического, подсчета максимального и минимального и.т.д.

 

Задание 1

Вставить лист и поименовать его Консолидация.

Используя данные трех рабочих листах, на листе Консолидация получить общие данные за квартал в виде следующей таблицы, применяя консолидацию рабочих листов Данные/Консолидация добавить в диалог все ссылки на консолидируемые данные (выделять в таблице диапазон до итогов).

Выбрать функцию суммирования и установит флажок Создавать связи с исходными значениями. Изучить структуру полученной таблицы. Свернуть и развернуть структуру полученной таблицы. Снять флажок символы структуры Сервис/Параметры/Вид, установить флажок вновь.

Изменить в любой исходной таблицы значение выручки и посмотреть, что произошло в итоговой таблице.

 

Город
Москва	120 000 000,00р.
Ст. - Петербург	160 000 000,00р.
Новосибирск	115 000 000,00р.
Киев	100 000 000,00р.
Минск	70 000 000,00р.
Сумма	565 000 000,00р.

 

Упражнение 5

Сводные таблицы

Сводная таблица — это таблица, которая используется для быстрого подведения итогов или объединения больших объемов данных. Меняя местами строки и столбцы, можно создать новые итоги исходных данных; отображая разные страницы можно осуществить фильтрацию данных, а также отобразить детальные данные области.

В сводной таблице можно создавать динамические перекрестные данные в строках и столбцах, показывать данные в отсортированном виде, скрывая детали или, наоборот, со всеми подробностями.

Сводные таблицы создаются с помощью Мастера сводных таблиц.

Задание 1

На основе базы данных Кадры получим информацию о суммарных окладах по отделам. Выделить ячейку списка (БД), далее Данные/Сводные таблицы. Запускается Мастер сводных таблиц. На первом шаге устанавливаем источник данных, на втором шаге диапазон, содержащий исходные данные, на третьем шаге определяем структуру таблицы, щелчок на кнопке Макет, перетащить поле Отдел в область строк, а поле Оклад в область данных, далее выбирается лист куда будет помещена сводная таблица.

Отформатировать таблицу, на числа наложить формат с двумя цифрами после запятой. Установить белый фон в ячейках заголовков. Снимите флажок у отдела КБ и посмотрите как изменилась таблица, установите вновь флажок Изменить вид таблицы, для этого поле Отдел перетащить над ячейкой Всего.

Задание 2

Обновление Сводной таблицы.

Изменить один из окладов в исходном списке. Сводная таблица не изменится, чтобы она изменилась необходимо выделить ячейку таблицы и команда Данные/ Обновить данные или нажать на восклицательныйзнак ПИ Сводная таблица (СТ).

 

Задание 3

Изменение итоговой функции. Выделить ячейку в области данных сводной таблицы, для которой меняется функция, далее на ПИ СТ щелкнуть на кнопке Параметры поля в диалоге Вычисление поля сводной таблицы в окне операции выбрать" максимум "

 

Задание 4

Получить информацию о количестве мужчин и женщин отделах.

На стадии макета в область строк помещается Отдел, в область столбцов Пол, в раздел данных поместить поле Отдел. После того как получили сводную таблицу, измените структуру ее. Переместите поле Пол в область строк и поменяйте местами очередность полей Пол и Отдел. Измените параметры сводной таблицы: выделить ячейку в сводной таблице и в контекстном меню Параметры таблицы снять флажки " Общая сумма по столбцам "и " Общая сумма по строкам ".Проанализировать изменения и восстановить параметры.

 

Задание 5

Скрытие и показ деталей в таблице. Выделить название отделов, выбрать в контекстном меню Группа и структура/Скрыть детали. Выделить две нижние строки Группа и структура /Показать детали. Таблица приобретет прежний вид.

 

Контрольные вопросы

 

1. Что означает в MS Excel понятие список или база данных?
2. Какие средства имеются в Excel для работы с базами данных?
3. Как заполняется база данных в MS Excel.
4. Что представляет собой диапазон критериев? Для чего он создается?
5. Как провести сортировку полей списка(БД)по возрастанию(по убыванию?
6. Как используют форму для поиска данных в списке?
7. Какие фильтры используются в MS Excel?
8. Какими возможностями обладает автофильтр?
9. Назовите недостатки автофильтра?
10. Можно ли при использовании автофильтра задать условия связанные одновременно логическими операциями И и ИЛИ?
11. Назовите особенности использования расширенного фильтра.
12. Как воспользоваться пользовательским автофильтром? Его особенности.
13. Можно ли использовать вычисляемые условия в расширенном фильтре.
14. Как задать условия отбора используя логическую операцию И в расширенном фильтре?
15. Как задать условия отбора используя логическую операцию ИЛИ в расширенном фильтре?
16. Можно ли при использовании расширенного фильтра задать условия,связанные одновременно логическими операциями И и ИЛИ?
17. Можно ли использовать символы шаблонов «*» и «?» при задании условий отбора, применяя расширенный фильтр?
18. Назовите известные функции баз данных.
19. Что такое сводная таблица?
20. Для чего в Excel используют сводные таблицы?
21. Структура сводной таблицы?
22. Каково назначение структуры электронной таблицы? Как создать структуру и работать со структурой?
23. Как установить трехмерные связи?
24. Что такое консолидация данных? Какими способами можно консолидировать данные электронной таблицы?
25. Чем список (БД) отличается от электронной таблицы?
26. Как устанавливаются связи с данными, находящимися вне текущего листа или книги?

 

 

Лабораторная работа №7