Лабораторная работа 4

Тема: Подведение итогов и отчетов по данным в базе данных MS Excel

Цели работы: Освоить применение основных команд по подведению итогов и составлению отчетов при работе с таблицами в MS Excel’2007/2010.

Содержание работы:

1. Подведение итогов в таблице.

2. Группирование и формирование структуры данных в таблице.

3. Выполнение группировки, фильтрации, осуществление анализа и формирование отчетов, используя сводные таблицы.

Задание 1. Автоматическое подведение итогов

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

1. открыть новую книгу и скопировать таблицу с листа «Сортировка» книги «Учет товара» из предыдущей работы на лист новой книги, присвоить книге имя «Итоги», листу – «Подведение итогов»;

2. выполнить многоуровневую сортировку записей таблицы по полям Организация и Дата;

3. на вкладке Данные в области Структура выбрать команду Промежуточные итоги; после чего появится диалоговое окно Промежуточные итоги (рисунок 1);

4. в раскрывающемся списке При каждом изменении в выбрать Организация, в списке Операция выбрать Сумма, в поле Добавить итоги по активизировать флажки Дебет и Кредит;

5. нажать кнопку ОК, оценить результаты на рабочем листе (рисунок2).

Слева от рабочей области листа появилась область, в которой отображаются символы трехуровневой структуры таблицы («+», «-» и «∙»):

• первый уровень структуры имеет внизу один символ («+» или «-»), который позволяет скрыть/отобразить сразу все записи таблицы; уровень содержит значение общего итога суммирования по полям Дебет и Кредит для всех записей;

• второй уровень структуры содержит символы («+» или «-») для каждой группы записей, которые позволяют скрыть/отобразить записи конкретной группы таблицы; уровень содержит значения итогов суммирования по полям Дебет и Кредит внутри каждой группы;

• третий уровень структуры содержит символ «∙», соответствующий одной записи таблицы.

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

Рисунок 1 – Диалоговое окно Промежуточные итоги

Рисунок 2 – Вид рабочего листа после подведения итогов

Рисунок 3 – Вид таблицы со скрытыми записями групп

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

1. поместить курсор в любую ячейку таблицы;

2. выполнить команду Промежуточные итоги для вызова диалогового окна;

3. в раскрывающемся списке При каждом изменении в выбрать Дата, в списке Операция выбрать Сумма, в поле Добавить итоги по активизировать флажки Дебет и Кредит; выключить значок Заменять текущие итоги, чтобы оставить результаты предыдущих итогов;

4. нажать кнопку Ok.

---

Полученная таблица итогов о продаже товара каждой организации по датам представлена на рисунке 4.

Рисунок 5 – Вид таблицы после подведения итогов о продаже товара каждой организации по датам

Задание 2. Консолидация данных

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

Предположим, есть три таблицы одинаковой структуры УЧЕТ ПРОДАЖИ ТОВАРА одной фирмы, имеющей три склада в разных точках города. Фирма осуществляет продажу товаров различным организациям.

Для дальнейшей работы необходимо создать три таблицы:

1. скопировать таблицу с листа Сортировка (см. предыдущая лабораторная работа) на новые листы;

2. задать для листов имена «Склад_1», «Склад_2», «Склад_3»;

3. изменить некоторые данные в таблицах листов «Склад_1», «Склад_2», «Склад_3», например, для организации АОЗТ «Белокуриха» изменить для товара хлеб Кол1, Кол2 или Цену.

Предположим, что необходимо подвести итоги о продаже хлеба организации АОЗТ «Белокуриха» с трех складов. Для этого:

1. упорядочить данные в трех таблицах по полю Организация, внутри каждой организации упорядочить данные по полю Наименование товара;

2. подвести итоги в каждой таблице по полю Наименование товара, суммирующие значения по полям Дебет и Кредит;

3. добавить новый лист в книгу, присвоить листу имя Консолидация;

4. перейти на лист Консолидация, установить курсор в свободную ячейку, например, А1 и выполнить команду Консолидация на вкладке Данные области Работа с данными;

5. в появившемся диалоговом окне Консолидация (рисунок 6) в раскрывающемся списке Функция выбрать Сумма;

Рисунок 6 – Диалоговое окно Консолидация

6. установить курсор в поле Ссылка;

7. активизировать лист Склад_1, выделить диапазон ячеек (J17:K17), содержащих итоговые суммы продажи хлеба АОЗТ «Белокуриха», после чего данные отобразятся в поле;

8. нажать кнопку Добавить;

9. удалить данные из поля Ссылка;

10. выполнить действия 7) - 8) для листов Склад_2, Склад_3.

11. установить флажок Создавать связи с исходными данными; если будут меняться данные исходных таблиц, автоматически будет пересчитываться и суммирующая таблица;

12. нажать кнопку OK, после чего на листе отобразятся итоговые суммы консолидации с двухуровневой структурой слева (рисунок 7).

Для того чтобы отобразить на листе Консолидация итоговые суммы по каждому складу необходимо использовать символ структуры «+» слева (рисунок 8).

Рисунок 7 – Вид листа после консолидации данных

Рисунок 8 – Вид листа с развернутыми данными консолидации

Задание 3 Работа со сводными таблицами

Сводная таблица используется для формирования итогов по конкретным полям таблиц данных. Для создания сводных таблиц в табличном процессоре Excel используется Мастер сводных таблиц.

Предположим, что требуется выяснить, на какую сумму было продано товара какой-либо организации. Для этого необходимо:

---

1. скопировать таблицу с листа Сортировка на новый лист книги Итоги, задать для листа имя – Исходная таблица.

2. выполнить команду Сводная таблица на вкладке Вставка в области Таблицы, после чего появится диалоговое окно Создание сводной таблицы (рисунок 9);

3. в поле Таблица или диапазон проверить правильность исходного диапазона, включить радиокнопку На новый лист;

4. нажать кнопку OK, после чего откроется новый лист с активизированной вкладкой Работа со сводными таблицами и окном Список полей сводной таблицы, расположенным справа (рисунок 10); задать имя листа – Сводная таблица;

Рисунок 9 – Диалоговое окно Создание сводной таблицы

Рисунок 10 – Вид документа после активизации мастера построения сводных таблиц

Для построения сводной таблицы необходимо:

1. в окне Список полей сводной таблицы справа активизировать флажок поля Организация, после чего на рабочем листе отобразятся названия организаций (рисунок 11);

Рисунок 11 – Часть сводной таблицы с добавленным полем Организация

2. активизировать флажок Наименование товара, после чего на рабочем листе отобразятся названия товаров для каждой организации (рисунок 12).

Рисунок 12 - Часть сводной таблицы с добавленными полями Организация и Наименование товара

Для того чтобы названия организаций были размещены в заголовках строк, а наименования товаров – в заголовках столбцов, необходимо:

1. в окне справа в области Названия строк захватить мышью элемент Наименование товара и перетащить в область Названия столбцов, элемент Организация оставить на месте, после чего вид сводной таблицы примет вид, отображенный на рисунке 13;

2. в окне Список полей сводной таблицы справа активизировать флажок поля Дебит, после чего в области Значения появится соответствующий элемент, сводная таблица примет вид, представленный на рисунке 14;

Рисунок 13 – Вид макета сводной таблицы

Рисунок 14 – Вид сводной таблицы, содержащей итоговые значения

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

Рисунок 15 – Контекстное меню поля Организация

Замечание 2: Для того чтобы закрыть окно Список полей сводной таблицы достаточно установить курсор вне области сводной таблицы или закрыть данное окно традиционным способом (с помощью кнопки Закрыть в верхнем правом углу окна).

Для редактирования и изменения макета сводной таблицы необходимо выполнить следующие действия:

1. скопировать сводную таблицу на новый лист, задать имя листа – Сводная таблица1;

2. установить курсор в область сводной таблицы, после чего активизируется окно Список полей сводной таблицы;

3. перетащить элемент Наименование товара в область Названия строк, после чего сводная таблица примет вид, показанный на рисунке 16.

---

Рисунок 16 – Вид сводной таблицы с измененным макетом

Для того чтобы отобразить в данной сводной таблице реализацию только определенного вида товара, например, сахара, необходимо:

1. поместить курсор в ячейку с названием любого товара;

2. раскрыть список Названия строк;

3. выполнить операцию Автофильтра;

4. оценить полученные результаты (рисунок 17).

Рисунок 17 – Вид сводной таблицы, отображающей итоги по определенному наименованию товара

Для того чтобы отобразить в сводной таблице реализацию наименований товаров по конкретной организации, например, АОЗТ «Белокуриха», необходимо поместить курсор в ячейку с названием организации и выполнить операцию автофильтра аналогично предыдущему случаю (рисунок 18).

Рисунок 18 – Вид сводной таблицы, отображающей итоги по конкретной организации

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

Полученный результат представлен на рисунке 19.

Рисунок 19 – Вид сводной таблицы

Приведите последнюю сводную таблицу к виду, представленному на рисунке 20.

Рисунок 20 – Вид сводной таблицы

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

Фильтрация сведенных данных

Для фильтрации данных в сводной таблице следует выполнить следующее:

1. открыть последнюю сводную таблицу;

2. в окне Список полей сводной таблицы элемент Организация перетащить из области Названия строк в область Фильтр отчета, после чего в ячейке А1 появится текст Организация, в ячейке В1 раскрывающийся список (Все);

3. раскрыть список (Все) и выбрать название организации, например, к/х «Восток» (рисунок 21);

4. нажать кнопку OK.

Рисунок 21 – Вид сводной таблицы

Структурирование сведенных данных

Для структурирования данных следует использовать вкладку Работа со сводными таблицами (Параметры и Конструктор), которая активизируется при перемещении курсора в область таблицы.

Необходимо сгруппировать данные последней сводной таблицы по месяцам, для чего:

1. установить курсор в ячейку с любой датой;

2. выбрать команду Группировка по полю в области Группировать вкладки Работа со сводными таблицами – Параметры, после чего появится диалоговое окно Группирование (рисунок 22);

3. в поле с шагом выделить Месяцы;

4. нажать кнопку Ok, просмотреть результаты;

5. аналогичным образом структурировать данные по кварталу и месяцам (рисунок 23);

6. с помощью фильтрации отобразить данные только за 1 квартал (2 квартал).

---

Графическое отображение данных сводной таблицы

Для графического отображения информации, представленной в сводной таблице, необходимо:

1. активизировать вкладку Работа со сводными таблицами – Параметры, выполнить команду Сводная диаграмма в области Сервис, после чего появится диалоговое окно Вставка диаграммы;

2. выбрать тип диаграммы, например, гистограмма;

3. нажать кнопку OK, после чего появится диаграмма и окно Область фильтра сводной таблицы (рисунок 24).

Рисунок 22 – Диалоговое окно Группирование

Рисунок 23 – Вид сводной таблицы после группировки данных по кварталу и месяцам

Рисунок 24 – Вид рабочего листа с вставленной диаграммой

---

Смотрите также файлы

• ITIP_zadanie1.pdf

• Курсовая электронный бизнес.pdf

• Базовое задание по УИРС.pdf

• Курсовая Проектирование и реализация базы данных ГИБДД.docx

• Финансы и кредит.docx