Индивидуальное задание №2.3 по дисциплине "Информационные технологии в менеджменте". Вариант 2. Тема: Базы данных и их использование

Работа с Excel как с базой данных. Именованные ячейки
Задание 1.
Создание книги «Учет зарплаты»

Нажатие клавиш Ctrl + Home активизирует ячейку А1 на текущем листе. Однако в электронных таблицах принято оставлять эту ячейку свободной, поэтому рекомендуется начинать работу с любой другой ячейки. На первом листе «Общая» оформить таблицу (обратите внимание на форматирование данных):

	А	В	С
1		Список постоянных величин для расчета зарплаты
2		Размер мин. зарплаты	500,00р.
3		Районный коэффициент	30%
4		Отчисления в ПФ	1%
5		Отчисления ПН	13%
6
7		Начисление на единицу изделия	0,05р.

Порядок выполнения задания
1.Набрать заголовок в ячейке В1. Объединить ячейки В1 и С1. Отформатировать заголовок.
2.Набрать строки столбца В, отформатировать.
3. Внести данные в столбец С, отформатировать в соответствующем формате (для ячеек С2 и С7 – денежный формат, для остальных ячеек – процентный).
4. Присвоить ячейке С2 имя «МинЗП», для этого:
- сделать активной ячейку С2;
- выбрать пункт меню «Вставка» → «Имя» → «Присвоить»;
- в открывшемся диалоге в строке «Имя» ввести имя ячейки «МинЗП»;
- нажать кнопку «ОК».
5. Аналогично присвоить имена ячейкам, указанным в таблице:

Адрес ячейки	Имя ячейки
С3	Рк
С4	Пф
С5	Пн
С7	ЕдИзд

Задание 2
Правее набранной таблицы на этом же листе создать еще одну таблицу:

	...	D	E	F
1			Общие отчисления с ФЗП
2			Фонд ПФ	28%
3			Фонд ОМС	3,60%
4			Фонд СС	5,40%
5

Самостоятельно присвоить имена ячейкам:

Адрес ячейки	Имя ячейки
F2	ФондПФ
F3	ФондОМС
F4	ФондСС

Задание 3.
Расчет заработной платы

Задание 3.1
На новом листе (переименовать его в «Январь») оформить таблицу:

Расчетные формулы
7 гр. = 6 гр. × Рк.
8 гр. = 6 гр. + 7 гр.
9 гр. = (5 гр. + 1) × МинЗП.
Примечание: льготы начисляются только для штатных сотрудников.
10 гр. = 6 гр. × Пф.
11 гр. = (8 гр. − 9 гр. − 10 гр.) × Пн.
12 гр. = 8 гр. − 10 гр. − 11 гр.
Порядок выполнения задания
1. В ячейки В1 и А2 внести заголовки, отформатировать.
2. В строках 3 и 4 заполнить шапку таблицы, отформатировать, при необходимости объединить ячейки.
3. В строке 5 заполнить номера граф таблицы.
4. Внести исходные данные в таблицу.
5. Сделать активной ячейку G6, начать набор формулы: =F6*.
- Выбрать пункт меню «Вставка» → «Имя» → «Вставить».
- В открывшемся диалоговом окне подсветить в списке имен «Рк», нажать кнопку «ОК».
- Нажать клавишу Enter.
6. Размножить формулу на остальных служащих.
7. Сделать активной ячейку Н6, внести формулу: =F6+G6.
8. Размножить на остальных служащих.
9. Сделать активной ячейку I6, внести формулу: =ЕСЛИ(D6="пост.";(E6+1)*МинЗП;0).
10. Размножить на остальных служащих.
11. Сделать активной ячейку J6 внести формулу: =F6*Пф.
12. Размножить на остальных служащих.
13. Сделать активной ячейку K6 внести формулу: =(H6-I6-J6)*Пн.
14. Размножить на остальных служащих.
15. Сделать активной ячейку L6 внести формулу: =H6-J6-K6.
16. Размножить на остальных служащих.
17. Отформатировать данные столбцов F–L в денежном формате.

Задание 3.2
На этом же листе ниже набрать таблицу:
 

Отформатируйте строку заголовка, не забудьте оформить границы таблицы.
Расчетные формулы
7 гр. = 6 гр. × ЕдИзд.
8 гр. = 7 гр. + 7 гр. × Рк.
9 гр. = (5 гр. + 1) × МинЗП.
Примечание: льготы начисляются только для штатных сотрудников.
10 гр. = 7 гр. × Пф.
11 гр. = (8 гр. − 9 гр. − 10 гр.) × Пн.
12 гр. = 8 гр. − 10 гр. − 11 гр.

Задание 3.3
На листе «Январь» ниже набранных таблиц оформить ведомость вида:


Порядок выполнения задания
1. В ячейку В20 ввести формулу: = СЦЕПИТЬ("Ведомость за";B1).
2. Скопировать шапку таблицы с любой созданной ранее.
3. Набрать номера (можно создать ряд значений).
4. В ячейку В22 ввести формулу: =В6.
5. Размножить ее правее в диапазон С22:D22.
6. В ячейку Е22 ввести формулу: =F6.
7. Размножить ее правее в диапазон G22:J22.
8. Размножить диапазон В22:J22 вниз на весь список служащих.
9. В ячейку В27 ввести формулу: =В15.
10. Размножить ее правее в диапазон С27:D27.
11. В ячейку Е27 ввести формулу: =F15.
12. Размножить ее правее в диапазон F27:J27.
13. Размножить диапазон В27:J27 вниз на весь список служащих.
14. В ячейку I32 ввести заголовок «Итого».
15.  Выделить диапазон J22:J31, нажать кнопку «Автосумма».

Задание 3.4
Создать таблицы по всем месяцам года (январь – декабрь), каждую на отдельном листе. Поскольку листы должны иметь одинаковую структуру, то при копировании нужно выделить созданный лист «Январь», щелкнув на ячейке пересечения всех строк и столбцов. Каждый лист имеет имя по названию месяца. На каждом листе данные столбца «Объем изготовления» для фармацевтов могут быть разными.
Примечание: для удобства рекомендуется сначала вставить необходимое количество листов, затем копировать данные.

Задание 4
Расчет квартальных отчислений
На новом листе «Отчисления» оформить таблицу:

	A	B	C	D	E
1
2	I квартал
3	ФЗП	ПФ	ФОМС	ФСС	Итого за квартал
4
5

Расчетные формулы
ФЗП = трехмерная ссылка на ячейки с данными «Итого» по месяцам.
ПФ = ФЗП × ФондПФ.
ФОМС = ФЗП × ФондОМС.
ФСС = ФЗП × Фонд СС.
Итого за квартал = ПФ + ФОМС + ФСС.

Порядок выполнения задания
1. В ячейке А2 набрать заголовок, отформатировать.
2. Набрать шапку таблицы в диапазоне A3:D3, отформатировать.
3. В ячейке А4 набрать формулу: = СУММ(.
- Переключиться на лист «Январь», щелкнуть по ячейке J32 с итоговыми данными.
- При нажатой клавише Shift переключиться на лист «Март».
- Ввести в строке формул знак «)».
- Нажать клавишу Enter.
4. В результате должно получиться следующее:
=СУММ(Январь:Март!J32).
5. В ячейке B4 набрать формулу: =А4*ФондПф.
6. В ячейке C4 набрать формулу: =А4*ФондОмс.
7. В ячейке D4 набрать формулу: =А4*ФондСС.
8. Отформатировать данные таблицы в денежном формате.
9.  В ячейке Е4 набрать формулу: =B4+C4+D4.

Задание 5
1. На листе «Отчисления» оформить друг под другом еще три таблички с отчислениями поквартально за весь год.
2. Определить среднюю величину отчислений за месяц на листе «Отчисления».
3. Определить среднюю величину заработной платы за год каждого работника аптеки. Оформить на листе «Отчисления».


Работа с Excel как с базой данных. Использование именованных диапазонов и полей списков

Задание 1.
Общая структура книги «Прейскурант»
Создадим книгу «Прейскурант» со следующей структурой:
- лист «Вещества» – список цен на вещества, входящие в состав препаратов;
- лист «Ассортимент» – список изделий, выпускаемых аптекой;
- лист «Цена ЛС» – таблички с затратами веществ на 1 лекарственное средство (ЛС);
- лист «Себестоимость ЛС» – шаблон для расчета себестоимости лекарственных средств;
- лист «Список клиентов» – перечень всех клиентов аптеки;
- лист «Учет продаж» – список записей о продажах всех лекарственных средств за год.


Рисунок 1 – Схема связей листов книги «Прейскурант»

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

Задание 2.
Построение исходных таблиц

Задание 2.1
Оформить на листе «Вещества» таблицу цен, по которым аптека закупает вещества для производства различного вида ЛС. Столбцы В и С оформить как именованные диапазоны: «Наименование_вещества» и «Наименование_Цена_вещества».
Порядок выполнения задания
1. В ячейку В1 внести заголовок, отформатировать.
2. В ячейку С1 внести заголовок, отформатировать.
3. Набрать данные таблицы.
4. Выделить весь столбец В, выбрать пункт меню «Формулы» → «Определенные имена» → «Присвоить имя». Так как диапазон включает в себя один столбец, причем первая ячейка столбца содержит заголовок, то Excel автоматически берет это имя в качестве имени диапазона. Подтвердить создание диапазона можно нажатием клавиши «ОК».
5. Выделить полностью столбцы В и С, выбрать пункт меню «Формулы» → «Определенные имена» → «Присвоить имя». Имя диапазона нужно ввести вручную, так как он включает в себя два столбца данных.

Задание 2.2
Самостоятельно на листе «Ассортимент» оформить в отдельной таблице перечень ЛС, выпускаемых аптекой. Таблицу разместить в столбце В и весь столбец оформить как именованный диапазон: «Перечень_ЛС»:

Задание 3.
Расчет стоимости единицы ЛС

Задание 3.1
На новом листе «Цена ЛС» необходимо расположить небольшие таблички с расчетными формулами для определения цены единицы лекарственного средства:
Порядок выполнения задания
1. В ячейку А2 ввести текст «Цена единицы изделия». Он займет по ширине также столбец В. Уменьшить немного ширину столбца А (он вспомогательный), а ширину столбца В установить в соответствии с длиной текста. Ячейки не объединять!
2.  В столбце В создать поле со списком, источник данных – именованный диапазон «Наименование_вещества». Для создания поля со списком необходимо:
- выделить диапазон ячеек, которые будут содержать поля со списком;
- выбрать пункт меню «Данные» → «Проверка данных» (область «Работа с данными»);
- в открывшемся диалоге на вкладке «Параметры» указать:
тип данных – список;
источник – вставить имя диапазона, выбрав «Формулы» → «Определенные имена» → «Использовать в формуле»;
- нажать кнопку «ОК».
3.В столбце С создать поле со списком, источник данных – именованный диапазон «Перечень_ЛС».
4.В ячейке С1 набрать заголовок «Наименование ЛС», ширину столбца С уменьшить, а ширину столбца D увеличить по длине заголовка. Ячейки не объединять!
5.В ячейку Е1 ввести название «Цена (руб.)», скопировать в ячейку Е3.
6.В ячейке С2 выбрать из списка первое наименование выпускаемого изделия.
7.В столбце В начиная с ячейки В4 выбрать из списка необходимые продукты, норму расхода ввести вручную.
8. В столбце Е рассчитать цену, которая определяется как произведение нормы расхода на цену соответствующего продукта. Для поиска цены продукта воспользоваться стандартной функцией ВПР:
=ВПР(B4;Наименование_Цена_вещества;2;0)*D4.
9. Скопировать в диапазон Е4:Е11.
10. Эта формула фактически связала листы «Вещества» и «Цена ЛС», поэтому при изменении цены вещества, указанной на листе «Вещества», автоматически пересчитается цена изделия на листе «Цена ЛС».
11.В ячейку Е2 поместить итоговую цену единицы изделия, рассчитываемую по формуле: =СУММ(E4:E11)
12.Аналогичным образом создать еще три таблицы для лекарственных средств: Парацетамола, Таблеток от кашля и Зиртека. Известно, что в состав Парацетамола входят парацетамол – 0,5 г, крахмал картофельный – 0,0280 г, стеариновая кислота – 0,0011 г, повидон низкомолекулярный – 0,0165 г, магния стеарат – 0,0044 г. В состав Таблеток от кашля входят термопсиса ланцетного травы порошок – 6,7 мг, натрия гидрокарбонат – 250,0 мг, крахмал картофельный – 27,9 мг; тальк – 5,4 мг. В состав лекарственного средства Зиртек входят цетиризина дигидрохлорид – 10,00 мг, глицерол – 294,10 мг, пропиленгликоль – 350,00 мг, натрия сахаринат – 10,00 мг, метилпарагидроксибензоат – 1,35 мг, пропилпарагидроксибензоат – 0,15 мг, натрия ацетат – 10,00 мг, уксусная кислота ледяная – 0,53 мг, вода очищенная – до 1,00 мл.

Задание 3.2
На листе «Цена ЛС» указаны цены на все лекарственные средства, выпускаемые аптекой. Однако они занимают слишком много места и не помещаются на один экран. Чтобы видеть все цены одновременно, проведем группировку таблиц по строкам. Второй уровень образуют строки таблиц, содержащих норму расхода. Например, для первой таблицы это строки с 3-й по 9-ю включительно.
Порядок выполнения задания
1. Выделить строки с 3-й по 9-ю включительно (строки выделять полностью).
2. Выполнить группирование данных:
- выделить данные одного уровня группирования;
- выбрать пункт меню «Данные» → область «Структура» → «Группировать».
3. Свернуть группу.
4. Повторить для каждого лекарственного средства в отдельности.
Результат представлен на рисунке ниже.


Задание 3.3
Создать именованный диапазон «Цена_ед_ЛС», включающий в себя столбцы C, D, E.


Задание 4
Расчет себестоимости изделия

Задание 4.1
При расчете себестоимости произведенного лекарственного средства нужно учесть не только стоимость затраченных веществ, но и издержки, связанные с амортизацией оборудования, зарплатой фармацевтов и т. д. Издержки, зависящие от цены исходных веществ и объема выпуска лекарственных средств, называют переменными, а амортизационные и другие издержки – постоянными.
На новом листе «Себестоимость» создать следующую таблицу:
Расчетные формулы
Себестоимость единицы изделия = (Цена + Итоговые постоянные издержки) ÷ Объем производства.
Цена = Объем производства × Цена единицы изделия.
Цена продажи = Себестоимость × (1 + Рент).
Выручка от реализации = Цена продажи × Объем производства.
Прибыль = Выручка от реализации − Итоговые постоянные издержки − Цена
Итого = сумме всех постоянных издержек.

Порядок выполнения задания
1. В ячейку F1 ввести заголовок.
2. В ячейку G1 ввести значение 20% и присвоить ей имя «Рент».
3. Ввести заголовки в ячейки А2 и F2.
4. В столбце В создать поле со списком, в качестве источника использовать диапазон «Перечень_ЛС».
5. Объем производства вводится вручную с клавиатуры.
6. Цена в столбце D рассчитывается по формуле: =C4*ВПР(B4;Цена_ед_ЛС;3;0).
7. Размножить формулу на весь диапазон изделий.
8. Внести данные в таблицу постоянных издержек, отформатировать числовые данные в денежном формате.
9. Итоговые постоянные издержки рассчитать, используя функцию «Автосумма».
10. Оформить ячейку D21 с итоговыми издержками и присвоить ей имя «Пост_издержки».
11. В ячейку F4 внести формулу: =(D4+Пост_издержки) ÷ С4.
12. Размножить ниже на остальные изделия.
13. В ячейку G4 внести формулу: =F4*(1+Рент)
14. Размножить формулу.
15. В ячейку Н4 внести формулу: =G4*C4.
16. Формулу размножить.
17. В ячейку I4 внести формулу: = Н4-Пост_издержки-D4.
18. Формулу размножить.
19. Диапазон В3:G20 оформить как именованный: «Цена_продажи».

Задание 5
Аптека решила выпускать новые лекарственные средства, необходимо рассчитать их себестоимость. Исходные данные приведены в таблицах ниже:


Будьте внимательны! В формуле расчета стоимости единицы ЛС изменяется диапазон.
На новом листе «Список клиентов» сформировать список клиентов аптеки, оформить как диапазон под именем «Клиент».


На новом листе «Учет продаж» создать таблицу учета продаж изделий. Пример представлен ниже.


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


Работа с Excel как с базой данных. Анализ данных в Excel

Задание 1
Структура книги Анализ данных
Для работы скопируем данные с листа «Учет продаж» книги «Прейскурант» в новую книгу «Анализ данных».
Примечание: при копировании использовать команду «Специальная вставка (значения)».
Рассмотрим процессы поиска и отбора данных в базе, а также разные способы их объединения.
Структура книги следующая:

- Лист «Учет продаж» содержит данные по всем продажам за год и связан с листом «Учет продаж» книги «Прейскурант».
- Лист «Учет зарплаты» содержит данные по зарплате работников аптеки за весь календарный год и связан с листами «Январь» – «Декабрь» книги «Учет зарплаты».
- Листы «Бюджет», «Консолидация», «Своды по продажам» и «Своды по зарплате» содержат данные, структурированные для анализа.

Задание 1.1
Определение продаж по кварталам.
Порядок выполнения задания.
1.Открыть книгу «Прейскурант» на листе «Учет продаж».
2.Создать новую книгу под именем «Анализ данных».
3. Вывести обе книги на экран, выбрав пункт меню «Окно» → «Расположить рядом».
4. В книге «Анализ данных» начиная с ячейки А1 набрать заголовки таблицы:

	A	B	C	D	E
1	Дата	Наименование ЛС	Клиент	Количество	Сумма покупки

5. В ячейку А2 ввести начало формулы: =
6. Перейти в книгу «Прейскурант», щелкнуть по ячейке А2, формула изменит вид:
='[Прейскурант.xls]Учет продаж'!$А$2,
тем самым устанавливается связь с листом «Учет продаж» книги «Прейскурант».
7. Сразу изменить ссылку на относительную (убрать знак $ из формулы).
8. Нажать клавишу Enter.
9. Размножить формулу на всю таблицу.
10. Если в столбце «Дата» вместо даты получается число, необходимо выделить столбец и применить к нему формат даты («Главная» → область «Число» → «Краткий формат даты»).
11.Если в столбце «Количество» вместо числа получается дата, необходимо выделить столбец и применить к нему числовой формат (Главная → область Число → числовой).
12. Закрыть книгу «Прейскурант», а окно с книгой «Анализ данных» развернуть на весь экран.
13. Переименовать лист как «Учет продаж».
14. Вставить новый столбец перед первым столбцом А и назвать его «Квартал».
15. Выделить столбец А и задать формат «Общий» (пункт меню «Формат» → «Ячейка» → «Число»).
16. Задать формулу:
=ЕСЛИ(МЕСЯЦ(B2)<4;1;ЕСЛИ(МЕСЯЦ(B2)<7;2;ЕСЛИ(МЕСЯЦ(B2)<10;3;4))), то есть переводим даты в кварталы.
17. Размножить формулу на всю таблицу.

Задание 1.2
На новом листе «Учет зарплаты» создать таблицу следующего вида:

Месяц

ФИО

Должность

Форма работы

Начислено

Начислено+РК

Льготы по ПН

Отчисления ПФ

Отчисления ПН

К выдаче

Данные самостоятельно скопировать из книги «Учет зарплаты» со всех листов. В столбец «Месяц» внести значения вручную, согласно исходным данным.

Задание 2.
Поиск данных в таблице продаж на листе «Учет продаж»

Задание 2.1
Найти все записи продаж, относящиеся к клиенту «Квета Добрыньевна Белоярова».
Порядок выполнения задания
1. Установить фильтр.
2. Щелкнуть по стрелочке фильтра, относящегося к столбцу «Клиент», и выбрать из списка фильтра значение «Квета Добрыньевна Белоярова».
В исходной базе останутся только записи, относящиеся к Квете Добрыньевне Белояровой.

Задание 2.2
Определить, кому из клиентов было продано более 52 упаковок Парацетамола.
Порядок выполнения задания
1. Установить фильтр.
2. Щелкнуть по стрелочке фильтра, относящегося к столбцу «Наименование ЛС» и выбрать из списка фильтра значение «Парацетамол».
3. Щелкнуть по стрелочке фильтра, относящегося к столбцу Количество, выбрать «Числовые фильтры» → «Больше». В открывшемся диалоговом окне указать 52.
В исходной базе останутся только искомые записи.

Задание 2.3
Найти все продажи клиентам Милонегу Миролюбовичу Светозарову и Завиде Мстиславовне Вишне.
Порядок выполнения задания
1. Установить фильтр.
2. Щелкнуть по стрелочке фильтра, относящегося к столбцу «Клиент», и выбрать из списка фильтра значение «Текстовые фильтры».
3. Установить в открывшемся диалоге:
- первый оператор сравнения «равно»;
- значение «Милонег Миролюбович Светозаров»;
- флажок «ИЛИ»;
- второй оператор сравнения «равно»;
- значение «Завида Мстиславовна Вишня»;
- нажать кнопку «ОК».
В исходной базе останутся только записи, относящиеся к клиентам Милонегу Миролюбовичу Светозарову и Завиде Мстиславовне Вишне.

Задание 2.4
Определить, каким клиентам были осуществлены продажи 16 марта.

Задание 2.5
Определить продажи, составившие сумму более 50 руб.

Задание 2.6
Определить, были ли продажи лекарственного препарата Небилет клиенту Доброжиру Путиславовичу Ярополкову.

Задание 2.7
Определить, были ли продажи лекарственного препарата Зиртек Дарене Ратиборовне Тихомировой или Ждану Неждановичу Хотенову.

Задание 2.8
Определить, каким клиентам за I квартал объем покупок составил не менее 100 штук.

Задание 3.
Расчет итоговых продаж за год

Для создания сводной таблицы в Excel предусмотрен инструмент «Мастер сводных таблиц», который вызывается при выборе пункта меню «Вставка» → «Сводная таблица».
1. Появится первое окно мастера, где нужно установить флажок, который соответствует расположению исходных данных, и нажать на кнопку «Далее».
2. Появится второе окно, в котором указывается диапазон данных. Если перед началом работы курсор находился в исходной таблице данных, то именно она указывается в диапазоне данных. Если предложенный диапазон не захватил необходимые данные, то щелчком (по кнопке свертывания диалогового окна) в правой части поля «Диапазон» нужно свернуть окно диалога до размеров строки и, выделив необходимый диапазон, вернуться в диалоговое окно (нажав ту же кнопку) и нажать кнопку «Далее».
3.  Откроется третье диалоговое окно, позволяющее создать макет будущей таблицы. Мышкой нужно перетащить кнопки с заголовками в нужные области таблицы, задав строки, столбцы, а также значения в ячейках сводной таблицы. Здесь же можно выбрать ту функцию, которая будет применена как итог над данными. Нажать кнопку «Далее».
4. В последнем окне выбирается место расположения сводной таблицы, если перед построением курсор находился в пустой ячейке, то именно ее адрес берется в качестве начала сводной таблицы.
5. Нажать на кнопку «Готово».
Обновить сводную таблицу при изменении исходных данных можно, подсветив таблицу и выбрав пункт меню «Данные → «Обновить данные».
Структуру сводной таблицы можно модифицировать и после ее создания. Для этого нужно:
- выделить любую ячейку в сводной таблице, после чего автоматически откроется окно «Мастер сводных таблиц»;
- перетащить мышкой изменяемую кнопку в нужную область и нажать кнопку «Готово».
Того же результата можно достичь, просто перетаскивая поля прямо по сводной таблице.

Задание 3.1
Определить общие суммы продаж по всем клиентам.
Порядок выполнения задания
1.  Переключиться на чистый новый лист, назвать его «Консолидация», затем набрать заголовки:

	А1	В1	С1
1	Клиент	Количество	Сумма покупки

2.  Выделить ячейку А2 и выбрать пункт меню «Данные» → «Консолидация».
3.  Выполнить нужные установки: «Функция» – «Сумма». Щелкните в поле «Ссылка», перейдите на лист «Учет продаж». Выделите все данные из столбцов «Клиент», «Количество», «Сумма покупки». Отметьте галочкой пункты «Значения левого столбца» и «Создавать связи с исходными данными». Нажмите «ОК».
4. Программа выведет суммарные данные всех клиентов, организовав их в два уровня (маленькие кнопки с цифрами 1 или 2 в левом верхнем углу листа). Первый уровень показывает перечень всех клиентов аптеки, второй уровень можно просмотреть, нажав кнопку 2.
5. Чтобы отразить все исходные данные по продажам конкретного клиента, можно просто щелкнуть небольшую кнопку со знаком «плюс» слева от ячейки с именем клиента.

Задание 3.2
Определить средние суммы продаж по всем клиентам.

Задание 3.3
Создать сводную таблицу о продажах каждого лекарственного средства по кварталам.
Порядок выполнения задания
1. Для создания сводной таблицы в Excel предусмотрен инструмент «Мастер сводных таблиц». На новом листе выбрать пункт меню «Вставка» → «Сводная таблица».
- Появится первое окно мастера, где нужно установить флажок, который соответствует расположению исходных данных, и нажать на кнопку «Далее».
- Появится второе окно, в котором указывается диапазон данных. Если перед началом работы курсор находился в исходной таблице данных, то именно она указывается в диапазоне данных. Если предложенный диапазон не захватил необходимые данные, то щелчком (по кнопке свертывания диалогового окна) в правой части поля «Диапазон» нужно свернуть окно диалога до размеров строки и, выделив необходимый диапазон, вернуться в диалоговое окно (нажав ту же кнопку) и нажать кнопку «Далее».
- Откроется третье диалоговое окно, позволяющее создать макет будущей таблицы. Мышкой нужно перетащить кнопки с заголовками в нужные области таблицы, задав строки, столбцы, а также значения в ячейках сводной таблицы. Здесь же можно выбрать ту функцию, которая будет применена как итог над данными. Нажать кнопку «Далее».
- В последнем окне выбирается место расположения сводной таблицы. Если перед построением курсор находился в пустой ячейке, то именно ее адрес берется в качестве начала сводной таблицы.
- Нажать на кнопку «Готово».
2. Выполнить последовательность шагов «Мастера сводных таблиц», сделать необходимые установки и дойти до создания макета сводной таблицы.
3. Перетащить кнопку с заголовком «Наименование ЛС» в область «Строка», кнопку «Квартал» – в область «Столбец», кнопку «Сумма покупки» – в область «Значения».
Теперь в сводной таблице будут находиться результирующие значения всего предлагаемого лекарственного средства для каждого клиента поквартально, как показано ниже (численные данные будут отличаться!).
Сравнить данные, полученные в сводных таблицах, с консолидированными данными.

Задание 4
Изменить структуру сводной таблицы, добавив в область «Страница» поле «Клиент».
Порядок выполнения задания
1. Сделать активной любую ячейку в сводной таблице.
2. Перетащить кнопку «Клиент» в область «Строки».
В сводной таблице появилось новое поле, в котором отражается информация о клиентах.

Задание 5
Построить круговую диаграмму для итоговых значений продаж за год по каждому лекарственному средству, гистограмму – для итоговых продаж поквартально.

Задание 6
На новом листе «Бюджет» рассчитать итоговые данные за год.

	A	B	C	D	E	F
1		1-й квартал	2-й квартал	3-й квартал	4-й квартал	Итого
2	Продажи
3
4	Торговые издержки
5	Затраты на маркетинг
6	Накладные расходы
7	Общая сумма расходов
8
9	Чистая прибыль

Данные для строки «Продажи» взять из сводной таблицы.
Торговые издержки = 30% × Продажи.
Затраты на маркетинг = 10% × Продажи.
Накладные расходы = 20% × Продажи.
Общая сумма расходов = торговые издержки + затраты на маркетинг + накладные расходы.
Чистая прибыль = Продажи − Общая сумма расходов.

Задание 7
Создать следующие диаграммы:
1)  круговую – для общей суммы;
2)  гистограмму – по затратам на маркетинг;
3)  график для чистой прибыли по кварталам.

Задание 8
Построить сводную таблицу по данным о зарплате работников за январь. Таблица должна иметь такую структуру, чтобы можно было выделить данные по постоянным работникам и совместителям.
Примечание: для выполнения задания необходимо скопировать исходные данные из книги «Учет зарплаты».

Вариант 1. Сравнительный анализ зарплаты
Используются данные книги «Учет зарплаты».
Задание
1.  На листе «Отчисления» (из книги «Учет зарплаты») создайте новую таблицу ниже существующих:

A	B	C	D
Должность	Средняя зарплата за год	Мин. зарплата за год	Макс. зарплата за год

2. В столбец A внесите уникальные названия должностей (провизор, фармацевт, уборщица и т. д.), взяв их из листов за каждый месяц.
3. Для расчета столбца B используйте функцию СРЗНАЧ и трехмерные ссылки (как в задании 4 основной части). Вам необходимо собрать данные по конкретному сотруднику за все 12 месяцев, а затем вычислить среднее значение. Подсказка: сначала можно собрать все данные по сотруднику за год на отдельном листе через связи.
4. Для столбцов C и D используйте функции МИН и МАКС аналогичным образом.
5. Примените функцию автофильтра к полученной таблице, чтобы отфильтровать должности со средней зарплатой выше 20 000 руб.
6. Постройте гистограмму по данным столбцов A и B.

Вариант 2. Группировка продаж по сезонам
Используются данные книги «Анализ данных», листа «Учет продаж».
Задание
1. На листе «Учет продаж» книги «Анализ данных» добавьте новый столбец перед столбцом «Дата» и назовите его «Сезон».
2.  В этот столбец введите формулу с использованием вложенных функций ЕСЛИ (как в задании 1.1 для кварталов), которая определяет сезон года:
- Зима: декабрь (12), январь (1), февраль (2).
- Весна: март (3), апрель (4), май (5).
- Лето: июнь (6), июль (7), август (8).
- Осень: сентябрь (9), октябрь (10), ноябрь (11).
Пример: =ЕСЛИ(ИЛИ(МЕСЯЦ(B2)=12;МЕСЯЦ(B2)=1;МЕСЯЦ(B2)=2);"Зима";ЕСЛИ(ИЛИ(МЕСЯЦ(B2)=3;МЕСЯЦ(B2)=4;МЕСЯЦ(B2)=5);"Весна";...)).
3. Размножьте формулу на все строки таблицы.
4. Создайте сводную таблицу на новом листе «Сезонные продажи».
- Строки: Сезон.
- Столбцы: Наименование ЛС (по желанию).
- Значения: Сумма покупки (сумма) и Количество (количество).
5. С помощью группировки (как в задании 3.2 подраздела 3) сверните данные, чтобы оставить только итоги по сезонам.
6. Постройте круговую диаграмму, показывающую долю каждого сезона в общей выручке за год.

Вариант 3. Рейтинг клиентов и товаров
Используются данные книги «Анализ данных», листа «Учет продаж».
Задание
1. На новом листе «Рейтинги» создайте две отдельные таблицы:
Таблица 1. Топ-5 клиентов

A	B	C
№	Клиент	Общая сумма покупок

Таблица 2. Топ-5 товаров

E	F	G	H
№	Наименование ЛС	Общее количество	Общая сумма

2. Для заполнения таблиц используйте инструмент «Консолидация» (как в задании 3.1).
- Для Таблицы 1: выделите диапазон с клиентами и суммой покупок с листа «Учет продаж».
- Для Таблицы 2: выделите диапазон с наименованиями ЛС, количеством и суммой покупок.
3. После консолидации у вас появятся итоговые суммы по каждому клиенту и каждому товару.
4.  Отсортируйте полученные списки:
- Таблицу 1 – по убыванию столбца C.
- Таблицу 2 – по убыванию столбца G (количество).
5. Оставьте в каждой таблице только первые 5 строк (топ-5).
6.  Используя функцию ВПР, укажите в Таблице 2 (товары) напротив каждого наименования его себестоимость, взяв данные листа «Себестоимость ЛС» книги «Прейскурант». Примечание: если нет прямой связи, можно вручную внести примерные значения для наглядности.
7. В столбце H (после себестоимости) рассчитайте прибыль:
Общая сумма − (Себестоимость × Общее количество).
8. Постройте две гистограммы рядом:
- для клиентов: сумма покупок;
- для товаров: прибыль.