3. Для выполнения задания созданную таблицу копируем четыре раза.

       На  первой таблице для сортировки всех данных таблицы в алфавитном порядке (по колонке «Пункт назначения») жмем на выпадающий список в ячейке C3 (Рисунок 7). Выбираем пункт «по возрастанию», после чего программа автоматически сортирует все строки таблицы по названию пункта назначения в алфавитном порядке (Риунок 8). 

Рисунок 7. 

 

Рисунок 8.

 

     Во  второй скопированной таблице сортируем  все данные по дате отправления. Для этого жмем на выпадающий список в ячейке A18. Выбираем пункт «по возрастанию» (Рисунок 10).

     

Рисунок 10.

     В третьей скопированной таблице  выполняем сортировку по значениям  двух полей. Аналогично предыдущему  случаю, сортируем данные  таблицы  по значению столбца «дата отправления», выбрав в меню сортировки «по возрастанию». В этой же таблице включаем сортировку по полю «цена», применив числовой фильтр «от минимума к максимуму» (Рисунок 11).

     

Рисунок 11.

     Результат сортировки по двум столбцам представлен  на рисунке 12. 

     

 

Рисунок 12.

     В четвертой таблице производим сортировку столбцов по значениям заголовков. Для этого выделяем всю таблицу, и выбираем команду Данные – Сортировка. В диалоговом окне «Параметры» указываем  сортировать столбцы диапазона. Далее в окне «Сортировка» указываем  сортировать по «Строка 1» (значения заголовков, как указано в задании). Сортировку производим по значениям, порядок  сортировки «по возрастанию» (Рисунок 13). 

 

Рисунок 13. 

     Результат сортировки столбцов представлен на рисунке 14.

 

     

Рисунок 14.

4. Производим выборку данных из таблицы с использованием фильтров.

 

     4.1.1. Отобразим в таблице данные  только по количеству требующихся билетов, используя автофильтр. Используем в качестве условий точную форму (константу) выбора. Задаем критерий типа "ИЛИ" для поиска данных в столбце «количество» (Рисунок 15). 

     

Рисунок 15.

     Результат представлен на Рисунке 16. 

     

Рисунок 16

2. Отобразим в таблице все рейсы, с пунктом отправления Киев, используя текст выбора (шаблон).
3. Отобразим в таблице все рейсы, цена билетов на которые находится в пределах от 84$ до 200$, используя формулу выбора (условие). Задаем критерий типа "И" для поиска данных в столбце «цена» (Рисунок 18) Результат представим на рисунке 19.

 

 

Рисунок 18.

     

 

Рисунок 19. 

     4.2. Расширенный фильтр. Создаём на  рабочем листе таблицу критериев.  Задаем сложное условие для  попадания значений текстового поля «класс поездки» в диапазон  (критерий И). Для значений числового поля «количество» реализуем условие ИЛИ (Рисунок 20). Результат представлен на Рисунке 21. 

Рисунок 20.

 

Рисунок 21.

Задание №3

Цель  работы

 

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

Постановка  задачи

 
 

1. Выбрать  вариант.

       2. Перенести на рабочий лист  таблицу 3.1. Добавить в таблицу  пустые столбцы с заголовками  «Коэф_A», «Коэф_В», «Зарплата». Присвоить  таблице имя «Сотрудники»

       3. Создать на этом же рабочем  листе таблицу, содержащую поля: «Должность», «Коэф_А», «Коэф_В». Заполнить таблицу в соответствии с вариантом задания (таблица 3.2). В поле «Должность» ввести значения: Кассир, Продавец, Ст. Продавец. Присвоить таблице имя «Коэффициенты».

       4. В свободную ячейку рабочего  листа занести значение минимальной  заработной платы в соответствии  с вариантом (таблица 3.2). Присвоить  ячейке имя «Мин_зарплата».

       5. Заполнить поля «Коэф_A», «Коэф_В», «Зарплата» таблицы «Сотрудники»: поля «Коэф_A» и «Коэф_В» заполнить  на основании таблицы «Коэффициенты»  с помощью функции ВПР, используя  в качестве искомого значения  должность сотрудника. Зарплата  сотрудника считается по формуле  Зарплата=Коэф_А*Мин_зарплата+Коэф_В.

       6. Вычислить Фонд заработной платы,  используя для поля «Зарплата»  таблицы сотрудники итоговую  функцию СУММ.  Используя режим  «Подбор параметра» определить  минимальную заработную плату  для заданного вариантом фонда  заработной платы. В качестве  изменяемой ячейки выбрать ячейку  с именем «Мин_зарплата».

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

Решение

 
     

1. Выбираем  вариант:

     i=N mod 15=9, 

 где N=78 – две последние цифры зачетной книжки. Выбираем вариант №3.

2. Переносим на рабочий лист таблицу 3.1. Добавляем в таблицу пустые столбцы с заголовками «Коэф_A», «Коэф_В», «Зарплата». Присваиваем таблице имя «Сотрудники».
3. Создаем на этом же рабочем листе таблицу, содержащую поля: «Должность», «Коэф_А», «Коэф_В». Заполняем таблицу в соответствии с вариантом №3 (таблица 3.2 методических указаний). В поле «Должность» вводим значения: Кассир, Продавец, Ст. продавец. Присваиваем таблице имя «Коэффициенты».

 
       

4. В свободную  ячейку рабочего листа заносим значение минимальной заработной платы в соответствии с вариантом  №3 (таблица 3.2 методических указаний). Присваиваем ячейке имя «Мин_зарплата».

 
 
       

5.  Заполняем  поля «Коэф_A», «Коэф_В», «Зарплата»  таблицы «Сотрудники». Поле «Коэф_A»  заполняем с помощью функции ВПР, введя в ячейку D3 формулу

                                             =ВПР(C3;коэффициент;2;ЛОЖЬ)

     Затем протягиваем указанную формулу  до конца столбца 4. Поле «Коэф_В» также  заполняем с помощью функции ВПР, введя в ячейку E3 формулу

                                             =ВПР(C3;коэффициент;3;ЛОЖЬ)

     Затем протягиваем указанную формулу  до конца столбца 5. Зарплату сотрудников (столбец 6) вычисляем, введя в ячейку F3 формулу

                                             =D3*$E$23+E3

     Затем протягиваем указанную формулу  до конца столбца 6. Готовая таблица  приведена на рисунке 23. 

     

Рисунок 23. 

       

6. Вычисляем Фонд заработной платы, используя для  поля «Зарплата» таблицы сотрудники итоговую функцию СУММ.3

 

=СУММ(F3:F17)

         Запускаем режим «Подбор параметра» (Рисунок 24), определяем  минимальную  заработную плату для заданного  вариантом фонда заработной платы (13 000).

Рисунок 24.

 

Рисунок 25. 

       В качестве изменяемой ячейки  выбрать ячейку с именем «Мин_зарплата».  Искомое значение Минимальной  зарплаты равно 357,598784  (Рисунок 26). 

Рисунок 26.

7. Построим сводную таблицу. Выделим таблицу «Сотрудники» и вызовем команду Данные – Сводная таблица (Рисунок 27).

 

 

Рисунок 27.

         На основании сводной таблицы  определим среднюю заработную  плату сотрудников по магазинам и общую зарплату по должностям. В диалоговом окне «Список полей сводной таблицы» выбираем для добавления в отчет поля «№ магазина» и «Зарплата» (Рисунок 28). 

Рисунок 28.

     Далее вызываем контекстное меню ячейки столбца  «Суммарная зарплата», выбираем команду  Итоги по – Среднее (Рисунок 29). 

       

Рисунок 29.

     Получаем  отчет о средней заработной плате  сотрудников по магазинам (Рисунок 30).

Рисунок 30.

     Аналогичным образом составляем отчет об общей  заработной плате сотрудников по должностям (Рисунок 31). 

Рисунок 31. 
 
 
 
 

Выводы  по работе 
 

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