Лабораторная работа 1. Тема: «Изучить возможности консолидации данных в MS Excel»
1) Создайте три однотипные таблицы по образцу на одном листе или на разных листах MS Excel (рис. 1).
Рис. 1 Образец таблиц для консолидации
2) Подсчитайте средний балл студента по текущей аттестации.
3) Проведите консолидацию 3-х таблиц аттестации в одну с вычислением среднего балла по каждому предмету и разместите консолидированную таблицу на листе Консолидация, для чего:
· перейдите на чистый лист в книге и установите маркер мыши в левый верхний угол будущей таблицы;
· на панели «Данные» выберите «Консолидация»;
· в окне «Консолидация» (рис. 2) установите значение «Функции» – «Среднее»;
Рис. 2 Диалоговое окно Консолидация
· перейдите в строку «Ссылка», затем выделите на листе «Данные» для консолидации всю область 1 таблицы, включая заголовки таблицы (но не название таблицы). Нажмите кнопку «Добавить». Затем выделите таблицу 2 и снова нажмите кнопку «Добавить». Затем таблицу 3 – «Добавить». Установите флажки в окнах «Подписи верхней строки», «Значение верхнего столбца», чтобы использовать подписи таблиц в качестве подписей к новой консолидированной таблице. Поставьте флажок в окне «Создавать связи с исходными данными» для того, чтобы любое изменение в исходных таблицах приводило к автоматическому пересчёту в консолидированной таблице. Нажмите «ОК»;
· Обратите внимание на кнопки сворачивания и разворачивания структуры, которые появились слева от таблицы.
· Настройте представление числовых данных в консолидированной таблице. Установите «Числовой формат» с одним знаком после запятой. (панель Главная-Число)
· Примените параметры форматирования к таблице (установите границы таблицы, оформите шапку) (рис. 3).
Рис. 3 Консолидированная таблица
4. Сформируйте отчет по примеру в Яндекс Диске.
Лабораторная работа 2. «Структурирование консолидированных данных»
! Внимание. Данная лабораторная работа является продолжением лабораторной работы 1. Т.е необходимо сдать первую лабораторную работу 1, только потом приступать к лабораторной работе 2.
1. Постройте круговую диаграмму по средним баллам студентов за сессию консолидированной таблицы.
2. Сохраните результаты проделанной работы в своей папке.
! Внимание. Далее у каждого из вас будут разные данные. Необходимо каждому из вас найти отчетность по рынку, на ваше усмотрение (например, ноутбуки в России за 2014 год) и взять именно эти данные, они не должны пересекаться между студентами (т. е. Вами). Ссылку откуда брали отчетность указать в «Ходе выполнения работ». Далее будет рассмотрен пример на основе показателя выпуск молочной продукции в 2006 году.
3. Выполнение проекта: «Подготовка отчета «Показатели выпуска молочной продукции» средствами MS Excel». Проведите консолидацию данных показателей выпуска молочной продукции за год по кварталам в одной таблице.
4. Создайте новый документ Excel «Молочный комбинат». На листе 1 создайте таблицу «Выпуск молочной продукции за 2006 год» (в литрах) (рис. 1).
Рис. 1. Выпуск молочной продукции за 2006 год
5. На листе 2 создайте Выпуск молочной продукции за 2007 год (рис. 2). Так как заголовки столбцов и строк объединяемых таблиц должны полностью совпадать, то лучше скопировать структуру таблицы с первого листа на второй лист и заполнить эту структуру новыми данными.
Рис. 2. Выпуск молочной продукции за 2007 год
6. На листе 3 создайте «Прайс-лист продукции молочного комбината» (рис. 3.). (тут вы указываете примерные цены, товаров аналогов, например, ноутбук, компьютер, моноблок, макбук и тд.)
Рис. 3. Прайс-лист продукции молочного комбината
7. При помощи функции «Сумм» посчитайте строки «Итого за год» для каждого вида продукции на 1 и 2 листах.
8. Используя данные прайс-листа подсчитайте столбец «На сумму» в обеих таблицах. Для этого установите курсов в пустую ячейку столбца «На сумму», создайте формулу «= Итого за год * Цена за 1 литр.»
Например, для ячейки «G3» первой таблицы нужно ввести формулу «= F3*Лист3. B3». (формула вводится при помощи мыши). Готовую формулу можно скопировать для оставшихся строк таблицы.
9. На листе 4 посчитайте «Средние показатели выпуска продукции за 2006-2007 годы. Для чего проведите консолидацию данных первых двух таблиц.
10. Измените количество выпуска молока в 1 квартале 2006 года на 0. Проверьте, как изменились расчёты в итоговой таблице.
11. Постройте гистограмму и круговую диаграммы по итоговой таблице. Вставьте название в область диаграммы, легенду. В гистограмме подпишите оси координат.
12. Сформируйте и сдайте отчет о проделанной работе преподавателю.