Лабораторная работа 6. Сводные таблицы

 

Цель работы: познакомиться со способами подведения промежуточных итогов в базах данных Excel.

 

Сводная таблица – это еще один инструмент Excel для обработки больших списков с данными.

Сводная таблица обслуживается Мастером сводных таблиц (Данные | Сводная таблица), позволяющим сразу подводить итоги, выполнять сортировку и фильтрацию списков. Построение сводной таблицы осуществляется за четыре шага. На первом шаге указывается источник данных, на втором шаге – диапазон ячеек, где находятся данные. Третий шаг – самый основной. Здесь формируется требуемый вид сводной таблицы, исходя из условия поставленной задачи (рис. 6.1).

 

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

На четвертом шаге указывается, где поместить сводную таблицу. Полученную сводную таблицу всегда можно скорректировать, щелкнув по ней правой кнопкой мыши и выбрав пункт меню Мастер.

Примечание. В Excel 2000 построение осуществляется за 3 шага. На третьем шаге с помощью кнопки Макет происходит переход к диалоговому окну того же вида, что на рис 6.1.

 

Задание 1. Заполнить таблицу 6.1, занеся недостающие данные, рассчитав процент удержания по следующему правилу: при количестве иждивенцев более трех – 0%, при трех – 5%, при двух – 10%, при одном – 12%, если нет – 14%. Расчет оформить с помощью вложенных функций ЕСЛИ.

Таблица 6.1 – Ведомость

Ф.И.О.	Отдел	Количество иждивенцев	Всего начислено, руб.	% удержания	Всего удержано, руб.	Сумма к выдаче, руб.
1. Петухова К.И.
2. Безенчук П.Ф.
3. Воробьянинов И.М.
4. Востриков Ф.О.
5. Коробейников В.А.
6. Грицацуева В. С.
7. Гаврилин З.С.
8. Треухов Т.И.
9. Изнуренков А.В.
10. Щукина Э.Е.

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

 

Промежуточные итоги по различным группам можно подводить и без создания сводных таблиц, используя опцию Excel Итоги.Предварительно таблица сортируется по тому полю (столбцу), по которому необходимо подвести промежуточные итоги. Затем выбирается команда Данные | Итоги. В появившемся диалоговом окне (рис. 6.2) в поле При каждом изменении в: задается классификационная категория, в поле Операция: задается функция, необходимая для подведения итогов, в поле Добавить итоги по: выделяются названия столбцов, где находятся итожимые данные.

 

 

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

 

 

Задание 3. Получить промежуточные итоги во всех отделах по позициям Количество иждивенцев, Всего начислено, Всего удержано и Сумма к выдаче.Изучите и опишите структуру полученной таблицы.

 

Лабораторная работа 7. Консолидация данных (связь таблиц)

 

Цель работы: научиться составлять итоговые таблицы, связывая данные из других источников.

 

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

Связь данных можно осуществлять различными способами.

 

Способ 1. Формула связи. Связь между листами можно задать путем введения в один лист формулы связи со ссылкой на ячейку в другом листе =НазвЛиста!АдрЯч(восклицательный знак обязателен).

Связь между файлами можно задать путем введения в один файл формулы связи со ссылкой на ячейку в другом файле, указав полный путь к этому файлу, например,