Расчёты в электронных таблицах Ms Excel

установить курсор внутри таблицы «Кадры»;

на ленте Вставка на панели Таблицы нажать кнопку Сводная таблица, появится диалоговое окно Создание сводной таблицы (рис.27), в появившемся окне в поле Выбрать таблицу или диапазон автоматически выберется вся таблица «Кадры», далее нажать ОК; откроется новый лист, в котором и будут формироваться отчеты Сводной таблицы. В левой части листа формируется сам отчет, а в правой список полей Сводной таблицы и параметры формирования отчета;

Рис.27 `Выбор диапазона данных для создания сводной таблицы

в списке полей (Рис.28.) надо выбрать те, по которым нужен отчет, а в нижней части мышкой поместите поля в нужные области в нужном порядке (в начале можно поэкспериментировать с полями, перетаскивая их в разные области и следя за изменением отчета в левой части экрана (Рис.29));

форматирование начнем с того, что удалим две верхние строки рабочего листа, чтобы таблица начиналась с первой строки;

Рис.28 Список полей Сводной таблицы и параметры формирования отчета

Рис.29 Сводный отчет с суммой по полю Оклад по отделам

обычным способом (нажатие клавиши F2 и редактирование) поменяем названия в ячейках А1 и А2, для чисел установим формат с двумя знаками после точки, для ячейки В1 установим белый фон, зададим для ячеек границы и получим отформатированную таблицу (рис.30).

Рис.30 Сводная таблица после форматирования

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

установить курсор внутри таблицы «Кадры»;

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

ВНИМАНИЕ! Разместим сводную таблицу не на новом листе, а на том же, на котором уже есть созданная только что таблица, для этого надо:

в диалоговом окне Создание сводной таблицы выбрать пункт На существующий лист и мышкой указать диапазон, достаточно выделить одну ячейку, пусть это будет А9 (под первой сводной таблицей) - рис.31, нажать ОК;

Рис.31 Выбор диапазона для размещения сводной таблицы

Далее выбираем поля, для этого надо:

мышкой поместить поле Отдел в область строк, поле Пол - в область столбцов, в область значений поместим текстовое поле, пусть это будет Отдел, сводная таблица готова, осталось ее немного отформатировать (рис.32).

Рис.32 Выбор диапазона для размещения сводной таблицы

4. Создайте в MS Excel новую рабочую книгу. По умолчанию в этой книге три листа. Создайте еще один. Переименуйте листы: дайте им названия «январь», «февраль», «март», «1-й квартал».

Далее внесите схожие данные одновременно в несколько листов, для этого надо:

выделить все листы с названиями месяцев, щелкая по ярлычкам и придерживая клавишу Ctrl, при этом активным останется лист «январь»;

ввести в ячейку А1 «Фамилия И.О.», в ячейку В1 «сделки», в С1 «Объем»;

в диапазон А2:А5 введите фамилии и инициалы согласно данным рис.33 (названия месяцев не вводить, они на ярлычках листов).

Щелкните по ярлычку листа «февраль», выделение с нескольких листов будет снято. Значения для столбцов «Сделки» и «Объем» надо вводить отдельно на каждом листе.

Консолидация:

прежде всего нужно выделить ячейку, которая будет служить верхней левой ячейкой для блока с результатами консолидации, для этого надо перейти на лист «1-й квартал» и выделить ячейкуА1;

на ленте Данные во вкладке Работа с данными щелкнуть по кнопке Консолидация, появится диалоговое окно Консолидация (рис.34); в выпадающем списке «Функция» выставлено «Сумма», т.е. выбранные данные будут суммироваться, что нас устраивает;

С помощью следующего поля «Ссылка» можно добавить диапазоны для консолидации, для этого надо:

щелкнуть мышью в поле «Ссылка»;

щелкнуть по ярлычку листа «январь» и выделить мышкой блок А1:С5 (в поле ввода диалогового окна появится «январь!$A$1:$C$4», а вокруг блока бегущая пунктирная рамка;

щелкнуть кнопку «Добавить» - адрес диапазона окажется в поле «Список диапазонов».

Аналогично добавьте диапазоны «февраль!$A$1:$C$4», «март!$A$1:$C$4».

Рис.33 Заполненные таблицы на листах «январь», «февраль», и «март»

Рис.34 Диалоговое окно «Консолидация»

в диалоговом окне имеется блок «Использовать в качестве имен»из двух флажков «подписи верхней строки» и значения левого столбца». Установим оба флажка; нажимаем ОК, появится таблица (рис.35).

Внимание! Флажок «Создавать связи с исходными данными» устанавливается в случае, когда консолидируются данные из разных рабочих книг.

Рис.35 Результаты консолидации

Если внести изменения в один из диапазонов консолидации, то таблица на листе «1-й квартал» не изменится, но если выполнить команду Данные-Работа_с_данными-Консолидация, в диалоговом окне ничего не менять, только щелкнуть ОК, то произойдет обновление таблицы.

Вопросы для самоконтроля

1. Что такое сводные таблицы?

2. Чем удобна консолидация при обобщении и анализе данных?

Задания по вариантам к лабораторной работе №5

В соответствии с вариантом из табл. 3.1 вами был создан список, содержащий 20-30 записей. Задания по созданию сводных таблиц выполняются с теми же данными, что и в лаб/р №4, формулировка заданий по вариантам приведена в табл.4

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

СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ

Microsoft Office 2007. Все в одном. / Грег Пери. - Издательский дом Вильямс, 2008.

Лавренов С.М. Excel: Сборник примеров и задач. - М.: Финансы и статистика, 2006. - 336 c.

Практический курс по электронным таблицам MS Excel: Учебное пособие для вузов. / Л.В. Маликова, А.Н. Пылькин, С.Ю. Жулёва. - 2-е изд. испр. и доп. - М.: Горячая линия - Телеком, 2006. - 256 с.

Осипова Т.Т. Информатика. Раздел: Работа с MS Office 2007. Лабораторный практикум для студентов всех специальностей и направлений / Т.Т. Осипова, С.В. Белишева. - СПб.: Изд-во СПбГУСЭ, 2010. - 47 с.

Размещено на Allbest.ru