Файл: Работа в редакторе электронных таблиц (16 ч).docx

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

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

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

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

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

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

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

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

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

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

Рисунок 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рисунок 1

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

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

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

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

Необходимо посчитать доход менеджеров. Доход состоит из оклада и бонусов, зависящих от продаж. (Файл Кейс4.xlsx)

В файле с исходными данными представлена таблица с продажами по каждому менеджеру за несколько месяцев. В ситуациях, когда в расчете используется несколько условий, удобно применять логическую функцию "ЕСЛИ". (Рисунок 1)

Рисунок 1

Ежемесячный оклад составляет 20 000 ₽

Размер бонуса зависит от суммы месячных продаж менеджера:

если продаж больше чем на 200 000 руб. - 15% от суммы продаж
если продажи больше 150 000, но меньше 200 000 руб. - 10% от суммы продаж
если продажи больше 100 000, но меньше 150 000 руб. - 5% от суммы продаж

Функция сравнивает заданное в ней значение, ссылку или формулу и возвращает заданные значения, ссылку или формулу в случае совпадения
и, аналогично, в случае несовпадения.

Возможно использование функции в связке с другими функциями, например: =ЕСЛИ(ВПР), ЕСЛИ(ЕСЛИ), ЕСЛИ(И) и т.д.

Вид формулы для расчета дохода: оклад + продажи * (%)

В нашем случае нужно использовать функцию из нескольких условий.

Схема "ЕСЛИ" для нескольких условий представлена на рисунке 2.

Рисунок 2

Начинаем заполнять таблицу "ФОТ"
Встаем на ячейку "В10" и вводим: =если(

Вводим первое логическое выражение.
При вводе формулы вручную, ее аргументы нужно отделять ";"
Указываем "значение если истина":

Аргументом для "Значение если ложь" будет еще одна функция с условием.

Поскольку остальные два условия представляют собой сравнение с двумя числами

, мы будем использовать функцию "И"

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

Рисунок 3

Указываем "Значение если истина". (Рисунок 4)

Рисунок 4

И в точности так же указываем наше последнее условие. (Рисунок 5)

Рисунок 5

Прописываем "Значение если истина". (Рисунок 6)

Рисунок 6

Прописываем "Значение если ложь". В последнем условии это уже "0" и закрываем условия скобками. Их должно быть ровно столько, сколько и открытых скобок. (Рисунок 7)