Создание сводных таблиц Excel

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

 

3.7.1. Создание сводной таблицы Excel на основе данных списка

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

 

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

 

Ход выполнения работы:

Щелкнуть мышью в любой ячейке внутри списка, выполнить команду меню Данные – Сводная таблица. В появившемся диалоговом окне Мастера сводных таблиц и диаграмм установить переключатель в списке или базе данных Microsoft Excel и щелкнуть по кнопке Далее. В следующем диалоговом окне убедиться, что в строке Диапазон правильно указан диапазон исходных данных (в нашем случае ячейки А1:F29). Если диапазон указан неверно, следует изменить его, выделив мышью блок ячеек с нужными данными, включая заголовки столбцов. Нажать кнопку Далее. В следующем диалоговом окне установить переключатель выбора места расположения сводной таблицы (новый лист или существующий лист), затем нажать кнопку Макет…

Появится диалоговое окно вида:

 

 

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

Поскольку необходимо создать сводную таблицу средних оценок по студенческим группам и учебным дисциплинам, “перетаскиваем” мышью в область Строка поле номер группы, а в область Столбец – поле предмет (можно разместить их наоборот). В область Данные “перетаскиваем” поле оценка и столбцов (предмет).

Примечание: в макете можно “перетаскиванием” при необходимости поменять местами поля или удалить поле из макета – перемещением его за область построения.

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

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

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

В составленной сводной таблице, выполнив двойной щелчок мыши в любой ячейке со средней оценкой, можно просмотреть данные, по которым было получено это значение. Например, дважды щелкнув в ячейке со значением 2,00, можем просмотреть список всех студентов 12-й группы с оценками по экономике, а дважды щелкнув в ячейке со значением 3,57 просмотрим оценки по математике студентов всех групп.

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

 

Задание 2. Для списка студентов Сессия (п. 3.7.1) составить сводную таблицу, как и в Задании 1, обобщающую итоги сессии, но с выводом данных по каждому студенту.

Ход выполнения работы:

Предварительно произведем сортировку списка студентов по номерам студенческих групп. В Задании 1 это было необязательным, т.к. данные по каждому студенту не выводились; а для выполнения Задания 2 необходимо, чтобы данные по студентам одной группы располагались в смежных ячейках, последовательно (выполнение сортировки – см. п.3.2).

Повторяем все действия, перечисленные при выполнении Задания 1, до момента формирования макета сводной таблицы.

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

В результате получим сводную таблицу:

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

Ход выполнения работы:

Можно изменить созданную в Задании 2 своднуютаблицу так, чтобы каждая студенческая группа рассматривалась отдельно; это имеет смысл в случае больших списков, просматривать и анализировать которые удобнее по частям.

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

В макете сводной таблицы переместим поле номер группы из области Строка в область Страница (в этом случае каждая студенческая группа будет располагаться на отдельной странице). Нажав кнопки ОК и Готово, подтвердим наш выбор и получим:

Щелкнув по значку в раскрывающемся списке номер группы, можно выбрать интересующую нас студенческую группу, причем если выбрать пункт (Все), отображены будут данные по всем группам. Так же можно варьировать набор учебных дисциплин, выбирая их из раскрывающегося списка предмет.

 

3.7.2. Создание сводной таблицы Excel на основе данных, находящихся в разных списках

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

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

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

Ход выполнения работы:

Выполнить команду меню Данные – Сводная таблица. В появившемся диалоговом окне Мастера сводных таблиц и диаграмм установить переключатель в нескольких диапазонах консолидации и щелкнуть по кнопке Далее. В следующем диалоговом окне установить переключатель создать одно поле страницы и щелкнуть по кнопке Далее. Затем следует указать диапазоны данных для консолидации, включая названия столбцов. В нашем случае нужно последовательно выделять диапазоны В1:Е6, В8:Е13, В15:Е19 и после каждого выделения щелкать по кнопке Добавить. (Примечание: ячейки с фамилиями студентов не выделяются, т.к. в сводную таблицу нужно поместить только обобщенные данные по студенческим группам). Убедиться, что в Список д иапазонов включены все три нужных диапазона данных, щелкнуть по кнопке Далее и в следующем окне выбрать место расположения сводной таблицы; затем нажать кнопку Макет…

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

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

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

Ниже приведены два варианта сводной таблицы, построенной по одним и тем же исходным данным

Практическое задание 1. Cоздание и заполнение таблиц Excel данными

1. Создать книгу (файл Excel) Сотрудники, поместив ее в личную папку (в качестве имени папки использовать свою фамилию и инициалы).

Создание рабочей книги Excel – см. п.2.1.1.

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

Переименование листа рабочей книги – см. п.2.1.1

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

ФИО

Отдел

Должность

Дата поступления на работу

Стаж работы

Оклад

Премия

Надбавка

Всего начислено

Пенсионный фонд

Налогооблагаемая база

Налог

К выплате

…

…

…

…

…

…

…

…

…

…

…

…

…

1. Столбцам ФИО, Отдел, Должность присвоить формат текстовый; столбцу Дата поступления на работу присвоить формат даты; столбцу Стаж работы присвоить формат числовой (с округлением до 1 знака после запятой); а остальным столбцам присвоить формат денежный.
2. Заполнить 1–й, 2-й, 3-й, 4–й и 6–й столбцы произвольными данными (10-15 записей).

Ввод данных в ячейки таблицы – см. п.2.1.3

Вставка, удаление строк, столбцов таблицы – см. п.2.1.2

Форматирование данных в ячейках таблицы – см. п.2.1.3

6. Выполнить форматирование таблицы: выше таблицы ввести ее название (формулировку произвести самостоятельно); выровнять ширину столбцов таблицы; заголовки столбцов выделить жирным шрифтом.

Вставка новых строк, столбцов таблицы – см. п.2.1.2

Форматирование данных в ячейках таблицы – см. п.2.1.3

1. Выше шапки таблицы поместить перечисленные константы:

Текущая дата	Надбавка, %	Процент отчисления в пенсионный фонд	Минимальный стаж работы для начисления надбавки	Процент подоходного налогообложения
…	5%	1%	…	13%

Для формирования текущей даты использовать функцию СЕГОДНЯ, ячейке присвоить формат дата. Ячейкам процент отчисления в пенсионный фонд; надбавка (%) и процент подоходного налогообложения присвоить формат процентный. Ячейке минимальный стаж работы для начисления надбавки присвоить формат числовой, значение установить самостоятельно.

Использование функции Сегодня – см. п.2.2.7.5

Вставка новых строк, столбцов таблицы – см. п.2.1.2

Ввод данных в ячейки таблицы – см. п.2.1.3

Форматирование данных в ячейках таблицы – см. п.2.1.3

Использование функций Excel в формулах – см. п.2.2.7.1.5

1. Сохранить рабочую книгу (файл) Сотрудники на рабочем диске в личной папке.

Сохранение изменений в рабочей книги Excel – см. п.2.1.1

Практическое задание 2. Заполнение вычисляемых столбцов таблицы Excel формулами

Уровень 1

В рабочей книге Сотрудники на листе Зарплата (операции с рабочей книгой Excel – см. п.2.1.1) выполнить следующие действия:

1. Произвести заполнение вычисляемых столбцов, используя, если необходимо, встроенные функции Excel, следующим образом:

Стаж работы = Текущая дата – Дата поступления на работу;

Премия = 10% от Оклада; (вычисление % от числа – см. п. 2.2.4);

Надбавка = Процент надбавки от Оклада если Стаж работы не меньше Минимального стажа работы для начисления надбавки, в противном случае 0 (использовать функцию ЕСЛИ, см. п. 2.2.7.2);

Всего начислено = Оклад + Надбавка + Премия;

Пенсионный фонд = Процент отчисления в пенсионный фонд от Всего начислено;

Налогооблагаемая база = Всего начислено – Пенсионный фонд;

Налог = Процент подоходного налогообложения от Налогооблагаемой базы;

К выплате = Всего начислено – Пенсионный фонд – Налог;

Использование формул в таблицах Excel – см. п.2.2

Использование функций Excel в формулах – см. п.2.2.7.1

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

1. Сохранить рабочую книгу (файл) Сотрудники на рабочем диске в личной папке.

. Сохранение изменений в рабочей книги Excel – см. п.2.1.1

Уровень 2

В рабочей книге Сотрудники на листе Зарплата (операции с рабочей книгой Excel – см. п.2.1.1) выполнить следующие действия:

1. В таблице базы данных по сотрудниками между 5-м и 6-м столбцами вставить столбцы: Льготы по налогообложению; Количество детей (до 18 лет). Заполнить эти столбцы произвольными данными, установив для льгот следующие обозначения: 0 (если льгот нет), 1 или 2 (льготы 1-й или 2-й категории соответственно).
2. Перед столбцом Всего начислено вставить столбцы Вычет из налогооблагаемой базы по льготе и Вычет из налогооблагаемой базы по детям.

Вставка, удаление строк, столбцов таблицы – см. п.2.1.2

1. Произвести заполнение вычисляемых столбцов, используя, если необходимо, функции Excel, следующим образом:

Стаж работы = Текущая дата – Дата поступления на работу;

Премия = 10% от Оклада; (вычисление % от числа – см. п. 2.2.4);

Надбавка = Процент надбавки от Оклада если Стаж работы не меньше Минимального стажа работы для начисления надбавки, в противном случае 0 (использовать функцию ЕСЛИ, см. п. 2.2.7.2);

Сумма дохода = Оклад + Надбавка + Премия;

Пенсионный фонд = Процент отчисления в пенсионный фонд от Всего начислено;

Вычет из налогооблагаемой базы по льготе = 400 руб., если льгот по налогообложению нет; 500 руб., если льготы 1-й категориии 1000 руб., если льготы 2-й категории (использовать вложенные функции ЕСЛИ, см. п. 2.2.7.4);

Вычет из налогооблагаемой базы по детям = Количество детей, умноженное на 300 руб.

Налогооблагаемая база = Всего начислено – Пенсионный фонд - Вычет из налогооблагаемой базы по льготе - Вычет из налогооблагаемой базы по детям;

Налог = Процент подоходного налогообложения от Налогооблагаемой базы ;

К выплате = Всего начислено – Пенсионный фонд – Налог;

Использование формул в таблицах Excel – см. п.2.2

Использование функций Excel в формулах – см. п.2.2.7

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

1. Сохранить рабочую книгу (файл) Сотрудники на рабочем диске в личной папке.

Сохранение изменений в рабочей книги Excel – см. п.2.1.1

Практическое задание 3. Построение, редактирование и форматирование диаграммы

Уровень 1

1. В рабочей книге Сотрудники скопировать таблицу базы данных на новый лист, дать листу название Диаграмма.

Переключение между листами рабочей книги, переименование листа, добавление нового листа – см. п.2.1.1

Копирование ячеек таблицы – см. п.2.1.4

1. Построить диаграмму (тип – Гистограмма, вид – 1-й или 2-й) со следующими столбиками диаграммы: оклад, налог, к выплате для сотрудников одного отдела (отдел выбрать самостоятельно, количество сотрудников в нем – не менее 4-х).

Построение диаграммы по данным таблицы – см. п.2.3.1

1. Произвести форматирование диаграммы: при необходимости изменить размеры области диаграммы, ввести название диаграммы, поместить на диаграмме поясняющую Легенду.

Форматирование диаграммы – см п.2.3.3

1. Сохранить рабочую книгу (файл) Сотрудники на рабочем диске в личной папке.

Сохранение изменений в рабочей книги Excel – см. п.2.1.1

Уровень 2

1. Выполнить задание Уровня 1.
2. Скопировать таблицу данных и построенную диаграмму на новый лист, дать листу название Трехмерная диаграмма. Отредактировать полученную копию, внеся в нее следующие изменения:

Ø Изменить вид гистограммы на трехмерный;

Ø Изменить расположение рядов диаграммы (вывести в таком порядке: налог, оклад, к выплате);

Ø Добавить в диаграмму данные по сотрудникам еще одного отдела.

Редактирование диаграммы – см п.2.3.2

Форматирование диаграммы – см п.2.3.3

Практическое задание 4. Сортировка данных в списке

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

Переключение между листами рабочей книги, переименование листа – см. п.2.1.1

Копирование ячеек таблицы – см. п.2.1.4.

2. Выполнить сортировку по:

A). Отделам;

B). Отделам и фамилиям;

C). Отделам и зарплатам;

D). Отделам, должностям и фамилиям;

E). Отделам, должностям и зарплатам.

Выполнение сортировки данных таблицы по одному столбцу – см. п.3.2.1.

Выполнение сортировки данных таблицы по двум и трем столбцам – см. п.3.2.2.

3. Сохранить рабочую книгу (файл) Сотрудники на рабочем диске в личной папке.

Сохранение изменений в рабочей книги Excel – см. п.2.1.1

Практическое задание 5. Фильтрация данных с помощью автофильтра

1. В рабочей книге Сотрудники скопировать таблицу базы данных на новый лист, дать листу название Автофильтр.

Переключение между листами рабочей книги, переименование листа, добавление нового листа – см. п.2.1.1.

Копирование ячеек таблицы – см. п.2.1.4.

2. Выполнить фильтрацию данных таблицы с помощью автофильтра (заданные значения устанавливать самостоятельно):

A). Сотрудники с фамилией на заданную букву;

B). Сотрудники с окладом больше заданного;

C). Сотрудники с заданной должностью и стажем работы в заданном диапазоне.

Выполнение фильтрации данных с помощью автофильтра – см. п.3.3.1

3. Сохранить рабочую книгу (файл) Сотрудники на рабочем диске в личной папке.

Сохранение изменений в рабочей книги Excel – см. п.2.1.1

Практическое задание 6. Фильтрация данных с помощью расширенного фильтра

Уровень 1

1. В рабочей книге Сотрудники скопировать таблицу базы данных на новый лист, дать листу название Расширенный фильтр.

Переключение между листами рабочей книги, переименование листа, добавление нового листа – см. п.2.1.1

Копирование ячеек таблицы – см. п.2.1.4.

2. Выполнить фильтрацию данных таблицы с помощью расширенного фильтра (заданные значения устанавливать самостоятельно):

A). Сотрудники с окладом в заданном диапазоне;

B). Сотрудники со стажем работы не менее заданного, окладом не менее заданного

C). Сотрудники со стажем работы в заданном диапазоне, окладом в заданном диапазоне;

D). Сотрудники двух заданных отделов, имеющие стаж работы более заданного.

Выполнение фильтрации данных с помощью расширенного фильтра – см. п.3.6.2

3. Сохранить рабочую книгу (файл) Сотрудники на рабочем диске в личной папке.

Сохранение изменений в рабочей книги Excel – см. п.2.1.1

Уровень 2

1. Выполнить задание Уровня 1.

2. Выполнить фильтрацию данных таблицы с помощью расширенного фильтра: сотрудники с окладом больше среднего значения

Выполнение фильтрации данных с помощью расширенного фильтра с использованием вычисляемого критерием – см. п.3.3.3

 

Практическое задание 7. Группировка данных в списке

Уровень 1

1. В рабочей книге Сотрудники скопировать таблицу базы данных на новый лист, дать листу название Группировка.

Переключение между листами рабочей книги, переименование листа, добавление нового листа – см. п.2.1.1.

Копирование ячеек таблицы – см. п.2.1.4.

2. Выполнить группировку сотрудников по отделам.

Выполнение группировки – см. примеры п.3.4

Уровень 2

1. Выполнить задание Уровня 1.

2. Выполнить группировку сотрудников по должностям (внутри отделов).

Выполнение группировки на двух иерархических уровнях (внешнем и внутреннем) – см. п.3.4

Практическое задание 8. Подведение общих и промежуточных итогов

Уровень 1

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

Переключение между листами рабочей книги, переименование листа, добавление нового листа – см. п.2.1.1.

Копирование ячеек таблицы – см. п.2.1.4.

2. Выполнить подведение промежуточных итогов по отделам с вычислением: среднего оклада; среднего стажа работы; общей суммы начислено; общей суммы налогов; общей суммы к выплате.

Выполнение операции подведения общих и промежуточных итогов – см.п.3.5

Уровень 2

1. Выполнить задание Уровня 1.

2. Добавить промежуточные итоги по: количеству сотрудников в отделах; среднему количеству детей у сотрудников по отделам.

Выполнение операции подведения общих и промежуточных итогов – см.п.3.5

 

Практическое задание 9. Консолидация данных

Уровень 1

1. В рабочей книге Сотрудники скопировать из таблицы базы данных на новый лист записи для сотрудников одного отдела, дать листу название Консолидация (по расположению).
2. При необходимости добавить в получившуюся таблицу записи (до общего количества 4-5). Сделать на этом же листе еще две копии полученной таблицы. Каждую из трех имеющихся таблиц назвать соответственно: Ведомость зарплаты за январь (февраль, март). Внести изменения в столбцы Оклад, Премия, Надбавка, произвольным образом изменив данные в каждой из трех таблиц.

Переключение между листами рабочей книги, переименование листа, добавление нового листа – см. п.2.1.1.

Копирование ячеек таблицы – см. п.2.1.4.

1. Создать консолидирующую таблицу Квартальная ведомость со следующей структурой:

ФИО	Отдел	Должность	Сумма дохода за квартал	Суммарный п/налог за квартал	Всего выплачено в квартале	Средний доход (руб./мес.)	Средний п/налог (руб./мес.)	Средняя выплата (руб./мес.)
…	…	…	…	…	…	…	…	…

Выполнение консолидации по расположению – см. п.3.6.1

Уровень 2

1. Выполнить задание Уровня 1.
2. Из листа рабочей книги Консолидация (по расположению) скопировать три таблицы-ведомости за месяцы на новый лист, дать листу название Консолидация (по категориям).

Переключение между листами рабочей книги, переименование листа, добавление нового листа – см. п.2.1.1.

Копирование ячеек таблицы – см. п.2.1.4.

1. Из таблицы-ведомости за февраль удалить столбец Премия, а из таблицы-ведомости за март удалить столбец Надбавка. После удаления этих столбцов внести необходимые исправления в вычисляемые столбцы.

Удаление столбцов таблицы – см.п.2.1.2

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

Выполнение консолидации по категориям – см. п.3.6.2

Практическое задание 10. Создание сводной таблицы

Уровень 1

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

Переключение между листами рабочей книги, переименование листа, добавление нового листа – см. п.2.1.1.

Копирование ячеек таблицы – см. п.2.1.4.

1. Создать сводную таблицу со следующей структурой:

отдел
…	Количество сотрудников
-“-	Средний оклад
-“-	Минимальный оклад
-“-	Максимальный оклад

Создание сводной таблицы – см. п. 3.7.1, Задание 1

1. Внести изменения в таблицу базы данных (добавить или удалить несколько сотрудников). Выполнить перерасчет с учетом измененных данных.

Обновление данных сводной таблицы после внесения изменений в основную таблицу – см п.3.7.1, Задание 1

Уровень 2

1. Выполнить задание Уровня 1.
2. Для анализа количества детей у сотрудников по отделам создать новую сводную таблицу со следующей структурой:

отдел	Количество детей
				…
…	Кол-во сотрудников	Кол-во сотрудников	Кол-во сотрудников	Кол-во сотрудников	Кол-во сотрудников

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

отдел	Льготная категория
…	Кол-во сотрудников	Кол-во сотрудников	Кол-во сотрудников

Создание сводной таблицы – см. п. 3.7.1, Задание 1

Обновление данных сводной таблицы после внесения изменений в основную таблицу – см п.3.7.1, Задание 1

Внесение изменений в структуру сводной таблицы – см. п.3.7.1, Задание 2

Практическое задание 11. Создание таблицы подстановки

1. Создать новую книгу (файл Excel) Прогноз, поместив ее в личную папку (в качестве имени папки использовать свою фамилию и инициалы).

Создание рабочей книги Excel – см. п.2.1.1.

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

Переименование листа рабочей книги – см. п.2.1.1

1. Создать таблицу подстановки для расчета вычета из налогооблагаемой базы в зависимости от категории льготы сотрудника и количества у него детей (рассмотреть варианты от 0 до 6 детей).

Вычет = Вычет по льготе + Вычет по детям

Вычет по льготе = 400 руб., если льгот нет; 500 руб., если льготы 1-й категории; 1000 руб., если льготы 2-й категории (в формуле использовать вложенные функции ЕСЛИ, см. п.2.2.7.4).

Вычет по детям = Количество детей, умноженное на 300 руб.

Создание таблицы подстановки с двумя переменными – см. п.2.4.2

 

Практическое задание 12. Построение тренда

1. В рабочей книге Прогноз скопировать таблицу подстановки, полученную при выполнении практического задания 11, на новый лист, дать листу название Тренд. Построить плоскую гистограмму по данным таблицы.

Построение диаграммы по данным таблицы – см. п.2.3.1

1. На произвольно выбранном ряду данных построить тренды разных аппроксимирующих функций (рассмотреть 3-4 вида функций). Выбрать наиболее подходящий тренд из рассмотренных.

Построение тренда – см. п.2.3.4

Практическое задание 13. Поиск оптимального решения

Уровень 1

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

Создание рабочей книги Excel – см. п.2.1.1.

1. Присвоить листу рабочей книги имя Поиск решения.

Переименование листа рабочей книги – см. п.2.1.1

1. Произвести поиск оптимального решения (нахождение максимума прибыли) для решения следующей задачи:

Задача. На выпуск 1 тыс. изделий А затрачивается 2 т. металла и 3 тыс. кВтч электроэнергии, а на выпуск 1 тыс. изделий В затрачивается 1 т. металла и 3 тыс. кВтч электроэнергии. План реализации не менее 2 тыс. изделий А и не менее 3 тыс. изделий В. От реализации 1 тыс. изделий А фирма получает прибыль 500 тыс. руб., а от реализации 1 тыс. изделий В – прибыль 700 тыс. руб. Выделенные ресурсы на производство всех видов изделий: 32 т металла и 54 тыс. кВтч электроэнергии. Определить, выпуск какого количества изделий А и В обеспечит максимум прибыли при выполнении оговоренных ограничений.

Поиск оптимального решения – см. п.2.5

Уровень 2

1. Выполнить задание Уровня 1.
2. Произвести оптимизацию для решения следующей задачи:

Задача. Возможно расширить ассортимент выпускаемой продукции – к товарам А и В добавить товары С и D. На выпуск 1 тыс. изделий С требуется 1,5 т. металла и 4 тыс. кВтч электроэнергии, а на выпуск 1 тыс. изделий D требуется 0,5 т. металла и 4 тыс. кВтч электроэнергии. На рынке можно реализовать не более 5 тыс. изделий С, получив с каждого изделия 1200 руб. прибыли и не более 4 тыс. изделий D, получив с каждого изделия 1000 руб. прибыли. Расширение ассортимента потребует дополнительных затрат на сумму 800 тыс. руб., которые будут возмещаться из прибыли. Целесообразно ли расширение ассортимента, т.е. возможно ли при выпуске четырех товаров получить прибыль большую, чем при выпуске только товаров А и В?

Поиск оптимального решения – см. п.2.5