Алексей работал начальником отдела продаж в крупном компьютерном магазине. Когда у него на руках появлялись актуальные прайс-листы, он почти вручную сравнивал строки, сверяя цены (в компании была установка – заказывать по самой выгодной цене: периодически руководство перепроверяло заказ).
После досконального изучения возможностей MS Excel он выработал совсем не трудный алгоритм, позволявший тратить на подобную операцию всего лишь 10 минут. Если вы хотите изучить основные возможности Excel и свести рутинную работу в программе к минимуму, то рекомендуем записаться на наш открытый онлайн-курс «Аналитика в Excel».
Предположим, есть два прайс-листа: один за 9 сентября, другой за 16 сентября. Необходимо проанализировать, как изменились цены на товары:
Для начала, Алексей объединил две таблицы в одну. Это можно сделать, скопировав необходимые строки и вставив на новый лист:
Но в получившейся после объединения таблице присутствуют одинаковые строки.
Дубликаты можно убрать и вручную, но это потребует слишком много времени, если таблица содержит сотни или тысячи строк.
Алексей решил избавиться от дубликатов автоматически. Для этого выделил весь список товаров, перешел во вкладку «Данные» на панели инструментов.
Рядом с кнопкой «Фильтрация» выбрал раздел «Дополнительно»:
В выплывшем окне Алексей заполнил ячейки следующим образом:
- Выбираем Обработку – «скопировать результат в другое место». Иначе дубликаты не пропадут.
- Выбираем, куда поместим наш результат.
- Отмечаем, что фильтровать нужно только уникальные записи.
В итоге получилось отфильтрованная таблица с уникальными наименованиями товаров:
Теперь старая объединённая таблица не нужна, и можно ее удалить!
А в новую отфильтрованную таблицу нужно добавить три столбца: «9 сентября», «16 сентября», «Разница» и заполнить их данными.
Для заполнения таблиц Алексей воспользовался формулой ВПР, которую используют, когда нужно найти элементы в таблице или диапазоне по строкам.
Формула имеет три обязательных аргумента и один необязательный:
— Искомое значение (для какого значения ищем совпадение);
— Таблица (диапазон, в котором ищут);
— Номер столбца, откуда нужно взять значение;
— Интервальный просмотр (возвращать приближенное (истина или 1) или точное (ложь или 0) соответствие — необязательный аргумент. По умолчанию 1.
Специалисту нужно было применить две формулы ВПР: для прайса от 9 числа и 16.
В качестве искомого значения в данной задаче выступало название товара:
В качестве таблицы, в которой происходит поиск Алексей выбрал прайс-лист от 9 сентября:
Так как мы ищем цену, а она находится во втором столбце выделенной таблицы, то укажем цифру 2 в качестве третьего аргумента.
Четвертый аргумент примет значение ЛОЖЬ (точное совпадение). Иначе все значения перепутаются.
Итоговая формула выглядит так:
=ВПР(B5;’9 сен 2019′!$B$4:$C$20;2;ЛОЖЬ)
Не забудьте закрепить диапазон поиска, поставив знак $, иначе при растяжении формулы на остальные ячейки диапазон тоже изменится!
Аналогичным способом заполняем столбец «16 сентября». В качестве таблицы указываем прайс лист от 16 сентября.
Запрос будет следующим:
=ВПР(B5;’16 сен 2019′!$B$6:$C$21;2;ЛОЖЬ)
В столбце «Разница» должна быть разность значений из ячеек «9 сентября» и «16 сентября:
=С5-D5
В результате заполнения таблицы специалист получил следующий результат:
Ошибка #Н/Д означает, что недостаточно значений для выполнения расчёта. Это свидетельствует о том, что в одном из прайс-листов такой позиции не было.
В итоге на такой анализ уходит не больше 10 минут!
Алексей признался, что раньше на сверку цен тратил примерно 9-12 часов в неделю, ведь приходилось сверять не только прайс-листы одного поставщика, но и между поставщиками – а это еще более сложная задача.
Благодаря такой оптимизации, Алексей стал больше времени проводить не за компьютером, а в торговом зале: уделял больше времени менеджерам, обращал внимание на их работу и делал необходимые корректировки; смог больше общаться с клиентами для того, чтобы узнать их пожелания к сервису или товарам; стал больше внимания уделять выкладке товара и порядку в магазине.
Все это благоприятно повлияло на рабочие процессы: сотрудники стали эффективнее работать; благодаря индивидуальным рекомендациям, клиенты стали чаще отзываться о вежливости обслуживания и качестве консультаций. Естественно, это привело к росту выручки и лояльности к бренду. Но это было только начало.
Следующим шагом на пути к улучшению стала система отслеживания эффективности сотрудников и внедрение KPI.
Эти задачи Алексей решил с помощью Сводных таблиц.
У него была объемная таблица, содержащая названия товаров, имена менеджеров, оформивших покупку, цены товаров, их себестоимость, наценка, прибыль и другие данные.
Обрабатывать всю информацию вручную практически невозможно (может и возможно, но уровень восприятия такой информации будет очень поверхностным), а вот такой инструмент, как Сводные таблицы – очень удобный и быстрый способ для создания отчетов и последующего анализа данных.
Как создать Сводную таблицу?
1. Во вкладке «Вставить» выберем кнопку «Сводная таблица»;
2. Во всплывающем окне указываем источник данных (в данном случае предыдущую таблицу) и место, где создадим сводную таблицу;
3. На новом листе появится поле, где вскоре появится необходимая таблица;
4. Теперь на панели управления будем выбирать, какие значения должны присутствовать в Сводной таблице.
Сводные таблицы можно создавать для анализа совершенно любых данных. Такой подход поможет эффективнее работать и не тратить ценное время. Естественно, Алексей отслеживал эффективность своих подчиненных не только по двум показателям – в случае необходимости он за два щелчка мог внести изменения в структуру Сводной таблицы и получить актуальные данные.
Благодаря этому он смог разработать KPI для сотрудников, из-за чего уровень их мотивации вырос, что принесло свои плоды – выручка продолжила расти, и руководство начало пристально следить за Алексеем: редко попадаются такие инициативные сотрудники, и за них нужно держаться!
На таких простых оптимизационных моментах Алексей не остановился и совместно со специалистами из отдела аналитики данных создал в Excel шаблон для планирования выручки.
Смысл этого шаблона заключается в том, что по реальным данным за определённые периоды можно с точностью до 99 процентов предсказать товарооборот на следующие 12 периодов. Это очень высокий показатель точности. Его удалось достичь благодаря анализу сезонности и ее влияния на будущие продажи.
Фактически, это уже совершенно иной уровень задач – модель содержит в себе элементы такого популярного сейчас направления, как Machine Learning (машинное обучение). И все это средствами Excel!
Но вернемся в отдел продаж: какую же структуру имел данный инструмент прогнозирования?
Сначала необходимо ввести реальные данные либо за 24 периода (это могут быть дни, недели, месяцы, кварталы) или за 36.
Затем автоматически рассчитываются модели прогнозирования, их точность и доверительный интервал.
Затем пользователь должен ввести в оранжевую ячейку тот прогноз, который ему больше подходит (выбор может зависеть от точности прогноза или от темпов роста).
После выбора итогового плана программа пересчитывает прогноз на 12 периодов с учетом сезонности.
Такой примерный график прогноза на 12 периодов получился с входными данными, которые мы задали.
В итоге, благодаря этой системе им удалось не только экономить силы и время на прогнозировании результатов продаж и составлении планов, но и получить отличное средство анализа данных, которое давало возможность глубже понять бизнес-процессы и перейти на новый уровень продаж.
Плановые показатели были также привязаны к KPI сотрудников, и они стали напрямую заинтересованы в увеличении продаж и выполнении планов.
Благодаря системе прогнозирования, компания, в которой работал Алексей, смогла регулировать предсказанные спады продаж с помощью специальных акций и скидок.
Уже спустя два квартала после введения в эксплуатацию системы, удалось оценить ее преимущества: прибыль увеличилась на 30%, акции и специальные предложения привлекли почти 2000 новых клиентов. Это был практически фантастический результат: до этого такого экстенсивного роста в компании не было уже очень давно.
Судьба начальника отдела по продажам тоже сложилась благополучно: начальство, увидев такую инициативность и оценив улучшенные благодаря Алексею показатели, предложило ему должность коммерческого директора.
Заключение
Как видите, знание MS Excel может сильно повлиять на производительность труда и доходы компании. В бизнесе очень трудно предсказать, с какой стороны придут деньги. Но как известно, под лежачий камень прибыль не течет!
Поэтому всегда нужно уделять должное внимание совершенствованию бизнес-процессов, навыков сотрудников и своего собственного уровня.
И самое главное, помните: компьютер и программное обеспечение – наши верные друзья на пути к оптимизации!