Указания к выполнению лабораторной работы

Сортировка списков

Сортировка списков предназначена для упорядочивания данных в строках и столбцах таблицы по определенным параметрам. При обработке больших объемов данных это облегчает работу со списками. В MS Excel имеется возможность сортировки данных по одному, двум, трем и четырем столбцам (строкам); по возрастанию и убыванию; по месяцам и дням недели.

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

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

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

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

В MS Excel фильтрация данных реализуется двумя командами – автофильтром и расширенным фильтром.

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

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

С подробной информацией о сортировке и фильтрации можно ознакомиться в справке MS Excel.

Задание к работе

1) Построить таблицу по образцу табл. 4.1.

2) Отсортировать табл. 4.1 по графе «Образование», организовав пользовательский список: «неполное высшее», «высшее», «среднее», а затем – по социальной группе и возрасту.

3) Отсортировать табл. 4.1 по графе «Социальная группа» по алфавиту.

4) Выбрать записи, относящиеся к предпринимателям, которые могут тратить от 4 000 до 8 000 руб.

5) Выбрать записи, относящиеся к данным о пенсионерах, готовых тратить больше 1000 руб., и о студентах, готовых тратить больше 2000 руб.

6) Выбрать записи, относящиеся к мужчинам от 18 до 25 лет и к женщинам от 26 до 35 лет.

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

Таблица 3.1

Информация о потребителях рынка одежды

Пол	Возраст	Социальная группа	Покупательные возможности, руб.	Образование	Средний срок носки выходной одежды	Средний срок носки повсед-невной одежды	Покуп-ка в «секонд- хэде»
1	2	3	4	5	6	7	8
М	18 – 25	Студент	1 400	Неполное высшее	1	1	Нет
М	26 – 35	Предприниматель	6 000	Высшее	1	Менее года	Нет
Ж	18 – 25	Студент	1 600	Среднее	1	1	Да
Ж	26 – 35	Безработный	500	Среднее	3	2	Да
М	18 – 25	Предприниматель	13 000	Среднее	1	1	Нет
М	46 – 56	Рабочий	3 500	Среднее	3	Более трех лет	Да
М	36 – 45	Предприниматель	13 000	Непоное высшее	Менее года	Менее года	Да
Ж	56 – …	Пенсионер	1 300	Среднее	1	Более трех лет	Да
Ж	26 – 35	Служащий	3 500	Высшее	2	1	Да
М	26 – 35	Предприниматель	8 000	Высшее	1	1	Нет
Ж	36 – 45	Безработный	500	Высшее	3	3	Да
М	36 – 45	Рабочий	2 600	Среднее	3	3	Да
М	18 – 25	Рабочий	2 400	Высшее	3	3	Да
Ж	26 – 35	Служащий	3 300	Высшее	2	2	Да
Ж	26 – 35	Служащий	3 600	Высшее	1	2	Нет
М	36 – 45	Госслужащий	2 200	Высшее	2	2	Да
Ж	26 – 35	Предприниматель	4 600	Неполное высшее	1	3	Нет
М	46 – 56	Безработный	200	Неполное высшее	3	Более трех лет	Да
Ж	18 – 25	Студент	2 100	Среднее	1	2	Нет
М	26 – 35	Безработный	800	Среднее	2	Более трех лет	Да

Контрольные вопросы

1) Сортировка каких данных возможна в MS Excel?

2) В чем особенность применения автофильтра?

3) В каких случаях целесообразно применение расширенного фильтра?

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

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

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

Указания к выполнению лабораторной работы

Часто при решении различных экономических задач возникает необходимость дублирования таблиц с идентичными в них данными либо использования одних и тех же столбцов (строк) в структурно отличающихся друг от друга таблицах. Нередки случаи, когда требуется создание итоговой таблицы по определенному временному периоду на основе нескольких таблиц, например, построение таблицы о результатах прибыли предприятия за год на основе расчетных таблиц по месяцам. В MS Excel имеется ряд инструментов для реализации названных целей.

Связывание таблиц

Простейшим способом связывания диапазонов данных в разных таблицах является метод копирования из таблицы с данными нужного диапазона в буфер обмена, а затем вставки в другую таблицу, которую необходимо связать с искомой. В большинстве случаев рекомендуется осуществлять копирование в связываемую таблицу не данных, а лишь ссылки на ячейки той таблицы, где эти данные находятся. Реализуется данная операция в MS Excel следующим образом: на вкладке Главная в группе Буфер обмена необходимо нажать кнопку со стрелкой под надписью Вставить, далее выберите команду Специальная вставка – Вставить связь.

Консолидация данных

При консолидации данных объединяются значения из нескольких диапазонов данных.

В MS Excel существует несколько способов консолидации данных: использование трехмерных формул, по положению и по категории.

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

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

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

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

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

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

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

Задание к работе

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

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

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

Таблица 4.1

Оборот продажи книг за 2016 г.

Название книги	Цена книги, руб.	Количество проданных книг, шт.	Сумма продажи, руб.
Архитектура компьютера	716	5 000	3 580 000,00р.
Microsoft Windows Server 2012. Полное руководство	2789	9 000	25 101 000,00р.
HTML5, CSS3 и JavaScript.	1660	4 000	6 640 000,00р.
Современные операционные системы	864	3 000	2 592 000,00р.
PHP. Объекты, шаблоны и методики программирования	1045	11 000	11 495 000,00р.

Таблица 4.2

Доход фирмы «Орион» за месяц _______

Город	Розничная продажа	Оптовая продажа	Продажа в кредит	Суммарный доход
Москва
Омск
Екатеринбург
Тюмень

4) На пятом листе книги просуммировать доходы фирмы за все месяцы по каждой статье, но с помощью консолидации по положению.

5) Создать и заполнить базу данных по образцу табл. 5.3. На другом листе создать сводную таблицу со следующими полями:

- поля «год», «продавец» оформить областью Фильтр отчета;

- поле «объем» оформить областью Названия строк;

- поле «район» оформить областью Названия столбцов;

- поле «товар» оформить областью Значения.

Таблица 4.3

Объем продаж фирмы за отчетный период

Месяц	Год	Продавец	Товар	Район	Объем	Сбыт
1	2	3	4	5	6	7
Январь	2016	Иванов	Детектив	Северный	13	106
Февраль	2017	Петров	Детектив	Южный	14	109
Март	2015	Сидоров	Детектив	Западный	12	203
Январь	2015	Иванов	Поэзия	Западный	15	125
Апрель	2016	Иванов	Поэзия	Восточный	14	206
Январь	2017	Петров	Детектив	Северный	12	188
Июнь	2015	Сидоров	Классика	Южный	11	153
Март	2017	Иванов	Поэзия	Южный	17	186
Июль	2017	Сидоров	Детектив	Западный	21	124
Август	2016	Иванов	Классика	Южный	16	120
Апрель	2017	Петров	Поэзия	Южный	12	181
Май	2015	Петров	Детектив	Западный	11	147
Июнь	2016	Иванов	Детектив	Северный	15	133
Июль	2016	Сидоров	Детектив	Северный	16	169
Август	2015	Иванов	Детектив	Восточный	19	175
Сентябрь	2016	Сидоров	Классика	Южный	20	153
Октябрь	2015	Иванов	Классика	Северный	14	160
Июнь	2017	Петров	Классика	Восточный	12	193

6) Создать таблицу по образцу табл. 4.4.

7) Подсчитать количество сотрудников в каждом отделе.

8) Определить количество иждивенцев в каждом отделе.

Таблица 4.4

Информация о сотрудниках фирмы

Номер п/п	Табельный номер	Фамилия	Имя	Отчество	Дата рождения	Отдел	Должность	Дата приема на работу	Дата увольнения	Пол	Кол-во иждивенцев
1	1	Иванов	Иван	Иванович	28.10.1966	Плановый	Экономист	10.01.2010		М	2
2	454	Иваненко	Иван	Петрович	21.01.1959	Бухгалтерия	Бухгалтер	10.04.2006		М	1
3	1234	Петров	Петр	Петрович	26.08.1994	Плановый	Секретарь	21.07.2012		М	0
4	12312	Петренко	Петр	Иванович	14.11.1984	Маркетинга	Менеджер	10.10.2008		М	1
5	12345	Сидоров	Сидор	Сидорович	02.02.1985	Снабжения	Менеджер	10.01.2009	10.10.2012	М	0
6	23456	Седов	Федор	Фомич	23.04.1985	Плановый	Экономист	12.04.2013		М	3
7	34567	Фомин	Фома	Фомич	12.07.1989	Плановый	Экономист	26.07.2012		М	1
8	45454	Фоменко	Сидор	Кузьмич	30.09.1995	Бухгалтерия	Бухгалтер	10.11.2008		М	1
10	45678	Макова	Алина	Игоревна	08.03.1976	Снабжения	Менеджер	10.04.2014		Ж	1
11	56565	Сушкина	Алла	Вадимовна	17.12.1970	Плановый	Экономист	10.07.2009	12.12.2013	Ж	1
12	56786	Кротова	Инна	Павловна	21.01.1996	Снабжения	Секретарь	21.10.2013		Ж	0
13	56789	Бойцов	Семен	Семенович	26.08.1984	Бухгалтерия	Начальник	10.01.2007		М	1
14	67890	Гайдай	Иван	Фомич	14.11.1984	Бухгалтерия	Бухгалтер	30.04.2010		М	1
15	78787	Краснов	Павел	Павлович	02.02.1985	Плановый	Начальник	10.07.2010		М	3
16	78901	Рябов	Олег	Евгеньевич	23.05.1984	Снабжения	Начальник	13.10.2011		М	1
17	89012	Белова	Софья	Петровна	12.07.1984	Плановый	Экономист	10.01.2006		Ж	2
18	90123	Чернова	Зоя	Богдановна	30.07.1984	Маркетинга	Начальник	10.04.2008		Ж	2
19	98989	Родионов	Иван	Вадимович	19.12.1984	Маркетинга	Секретарь	10.07.2003	30.01.2011	М	0
20	99999	Хрустов	Юрий	Юрьевич	08.03.1985	Маркетинга	Менеджер	10.10.2009		М	0