практическая работа № 5. ФОС_Практическая_работа_№_5_Работа_с_электроннымы_таблицами. Работа в редакторе электронных таблиц (16 ч)

• Название: Работа в редакторе электронных таблиц (16 ч)
• Анкор: практическая работа № 5
• Дата: 20.02.2023
• Размер: 3.61 Mb.
• Имя файла: ФОС_Практическая_работа_№_5_Работа_с_электроннымы_таблицами.docx
• Тип: Практическая работа; #946983
• страница: 3 из 9

Задание 2. Вычисления с помощью формул в электронной таблице

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

Необходимо дополнить таблицу "Статистика по заявкам" данными об ответственных и оценке, имея следующие данные:



Рисунок 1. Статистика по заявкам




Рисунок 2. Заявки Рисунок 3. Оценки

Таблица "Заявки" содержит информацию о заявках и ответственных за заявки. В таблице со статистикой так же есть поле "id заявки". Значит нам нужно найти "id заявки" в таблице "Заявки" и взять значение из соседней ячейки.

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

2. 
   Ставим курсор на ячейку "F2" таблицы "Статистика по заявкам". Открываем Мастер функций и находим в категории "Ссылки и массивы" функцию "ВПР".
   
3. 
   Предварительно отсортируем данные листа «Заявки» по возрастанию поля "id заявки" 
   
4. 
   В поле "Искомое значение" вводим полностью столбец "А" листа "Заявки".
   
5. 
   В поле "Таблица" вводим столбцы "А" и "В" листа "Заявки" целиком
   
6. 
   В поле "Номер столбца" вводим цифру "2". Это порядковой номер столбца из таблицы "Заявки", в котором содержится информация об ответственных.
   
7. 
   В поле "Интервальный просмотр" ставим значение "0"
   
8. 
   Заполнить аналогично столбец "Оценка" - в этом случае необходимо сопоставлять данные по полю "Телефон клиента"
   
9. 
   Сохранить задание в файл с именем Фамилия_ПР_5_2.xlsx
   

Задание 3. Вычисления с помощью формул в электронной таблице

1. 
   Рассмотрим ситуацию оценки отдела продаж. Имеется информация о продажах каждого менеджера за период, данные представлены в детализации по продажам каждого менеджера до уровня дня. (Файл Кейс2.xlsx)
   

Формат таблицы показан на рисунке 1:



Рисунок 1

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

Для получения конечного результата нам нужно суммировать продажи по полю "Сумма" для каждого менеджера.

Для начала получим список всех менеджеров отдела продаж.

2. 
   Копируем столбец "В" на соседний лист и, не снимая выделения с диапазона, переходим на вкладку "Работа с данными" панели инструментов. Выбираем "Удалить дубликаты":
   

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

Следующим этапом будем искать соответствия с этим списком в исходной таблице и суммировать их.

Для этих целей предусмотрена функция "СУММЕСЛИ"

4. 
   Приступаем к суммированию результатов каждого менеджера. Заполняем таблицу:
   
5. 
   Ставим курсор в ячейку "В". Открываем "Мастер функций" и находим в категории "Математические" функцию "СУММЕСЛИ"
   
6. 
   Для поля "Диапазон" выделяем столбец "В" и "С" с данными для проверки и суммирования.
   
7. 
   Для поля "Критерий" выделяем столбец "А" в новой таблице с уникальным списком менеджеров.
   
8. 
   Нажимаем "ОК" и протягиваем формулу до конца таблицы, получаем результат.
   
9. 
   Сохранить задание в файл с именем Фамилия_ПР 5_3.xlsx
   

Задание 4. Вычисления с помощью формул в электронной таблице

Имеем те же вводные данные, что и в задании 3. Произведем оценку продаж каждого менеджера в динамике. (Файл Кейс3.xlsx)

1. 
   Группируем данные по месяцам:
   

• 
  Добавляем поле "Месяц" в заголовок таблицы
  
• 
  Ставим курсор в ячейку "D2", открываем "Мастер функций" и находим в категории "Дата и время" функцию "Месяц"
  
• 
  В диалоговом окне вводим положение ячейки "А2" нажав на нее мышкой
  
• 
  Нажимаем "ОК" и протягиваем до конца таблицы
  

Функция МЕСЯЦ возвращает порядковый номер месяца. Аргументом функции является дата.

2. 
   На соседнем листе формируем уникальный список значений столбца "Менеджеры". Делаем это через инструмент "Удалить дубликаты" как в предыдущем примере.
   
3. 
   Формируем таблицу с заголовками как на рисунке 1 ниже:
   

Рисунок 1

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

В этом случае нам пригодится функция "СУММЕСЛИМН"

4. 
   Активируем ячейку "В2", открываем "Мастер функций" и находим в категории "Математические" функцию "СУММЕСЛИМН"
   
5. 
   Для поля "Диапазон суммирования вводим столбец "С" листа с исходными данными. При этом нужно зафиксировать координаты столбца "С" - для этого перед каждой буквой "С" ставим знак "$".
   
6. 
   Для поля "Диапазон условия указываем столбец "В" исходных данных и так же фиксируем по столбцам знаком "$".
   
7. 
   "Условие 1" заполняем значениями столбца "А" из новой таблицы и фиксируем координаты столбца.
   
8. 
   Заполняем поле "Диапазон условия 2", выбираем столбец "D" исходных данных. Фиксируем координаты столбцов.
   
9. 
   Для поля "Условие 2" выделяем ячейки с порядковыми номерами месяцев новой таблицы. Выделяем ячейки с "В1" по "D1". Фиксируем изменение координат по строкам и столбцам. Ставим знак "$" до и после буквы-номера столбца.
   
10. 
    Нажимаем "ОК" и растягиваем формулу на всю таблицу.
    
11. 
    Сохранить задание в файл с именем Фамилия_ПР_5_4.xlsx