В прошлом я уже делал разбор решения транспортной задачи на примере . Теперь же давайте попробуем использовать Microsoft Excel для решения другой классической задачи из того же раздела - прокладки кратчайшего маршрута из пункта А в пункт Б через несколько промежуточных точек транспортной сети. Частным случаем такой задачи ещё называют "задачу коммивояжера", когда нам нужно по кратчайшему маршруту обойти все точки, не заходя ни в одну из них дважды, а затем вернуться обратно в пункт отправления.
При работе в Microsoft Excel иногда возникают ситуации, когда нам с вами нужно как-то особо и отдельно обрабатывать только видимые или, наоборот, только скрытые пользователем строки. Например, считать какие-то хитрые отчёты именно по видимым ячейкам, или загружать только видимые ячейки для дальнейшей обработки в Power Query и так далее. Во всех этих случаях для каждой строки нам нужно каким-то образом однозначно определять - осталась ли она видимой и отображается сейчас на экране или скрыта от глаз пользователя.
Разбираем простой способ это реализовать с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS):
И несколько сценариев применения этой методики в реальных задачах:
Подробно разбираемся с тем, как можно упростить и ускорить расчёты в Microsoft Power BI с помощью двух мощных инструментов - групп вычислений (calculation groups) и параметров полей (field parameters). Это позволит не создавать в вашей модели кучу однообразных мер:
На днях компания Microsoft анонсировала, что в следующих версиях Excel появится возможность автоматического обновления сводных таблиц. За это будет отвечать кнопка Автообновление (Auto Refresh) на вкладке Анализ сводной таблицы (PivotTable Analyze):
Разбираем механизм работы этого нового инструмента, его плюсы и минусы. А для тех, у кого пока нет последней версии, пишем аналог на VBA.
Как собрать разношёрстные названия одних и тех же объектов в группы (кластеры) по максимальной похожести?
Очень актуальная задача, если вы работаете с данными, которые вводили разные пользователи или собираются из разных источников.
На помощь приходит Power Query, а точнее специальная функция нечёткой текстовой кластеризации из встроенного в Power Query языка М.
Проверка условий и выполнение расчётов по разной логике в зависимости от выполнения или невыполнения этих условий - один из базовых навыков, который одним из первых осваивают все, кто работает в Microsoft Excel. Обычно для этой цели используют классическую функцию ЕСЛИ (IF). Если же необходимо проверить несколько критериев, то приходится вкладывать несколько функций ЕСЛИ друг в друга или использовать связки ЕСЛИ+И (ИЛИ), что уже не так просто и приводит к появлению страшноватых "матрёшек-монстров".
Однако, кроме общеизвестной подходов, на самом деле, есть ещё несколько более изящных способов выполнить подобную проверку:
В 2020 году Microsoft выпустила обновление, добавляющее в Microsoft Excel 365 новую функцию ПРОСМОТРХ (XLOOKUP), пришедшую на замену классическим функциям поиска и подстановки типа ВПР (VLOOKUP), ГПР (HLOOKUP) и связке функций ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH). В отличие от предшественников, новая функция имеет простой и понятный синтаксис, умеет искать как по вертикали, так и по горизонтали, а также может искать снизу вверх, а не сверху вниз. Базовый синтаксис и возможности функции ПРОСМОТРX я уже подробно разбирал в с сопутствующим видеоуроком.
Теперь же давайте посмотрим на более продвинутые техники использования функции ПРОСМОТРX и скрытые её возможности:
Но есть другой путь. Суть его в том, чтобы добавить на лист поле ввода ActiveX, куда мы будем вводить искомый текст, а затем написать формулу, которая будет искать и выводить все строки в таблице, где в заданных столбцах есть частичное текстовое совпадение с введёнными данными:
Предположим, с ходу видно, что внутри внесено несколько правок, но все их быстро идентифицировать и проконтролировать весьма непросто, тем более, что они могут быть неочевидными - например, изменение какой-то ячейки на этом листе может привести к пересчёту другой на соседнем и т.п. В общем, игра из серии "найди 10 отличий или премию не получишь" 
Помочь в подобной ситуации может бесплатная надстройка от Microsoft с названием Inquire, появившаяся в Microsoft Excel, начиная с 2013 года.
Все вы наверняка много раз видели в маркетплейсах блок "вместе с этим товаром обычно покупают", где к пиву вам услужливо предложат чипсы, к вину - сыр, а к мылу - верёвку. Подобный подход называют ещё кросс-продажами (cross-sell) и, при правильном использовании, он позволяет ощутимо увеличить средний чек и общую удовлетворенность клиента.
Основным инструментом анализа в таких случаях является матрица кросс-продаж (cross-selling matrix) - таблица, которая помогает визуализировать возможности взаимных продаж между различными продуктами или категориями товаров. Она показывает, какие продукты хорошо продаются вместе, а какие - нет, и стоит ли, например, клиенту, купившему товар А, рекомендовать товар Б, или поставить эти товары рядом на сайте или на полке в магазине.
Структура этой таблицы предельно проста - по строчкам и по столбцам откладываются один и тот же набор интересующих нас товаров (или категорий), а на пересечении - числовой показатель, характеризующий кросс-продажи, например, количество клиентов, купивших оба товара, отложенных по осям X и Y:
Само собой, эта квадратная матрица будет симметричной относительно диагонали, ячейки на которой обычно оставляют пустой.
Давайте разберём как можно рассчитать подобную таблицу с помощью языка DAX в Power BI или Power Pivot в Microsoft Excel.
Галочки, они же флажки, они же чекбоксы и в особо запущенных случаях даже крыжики - всё это разные названия для одного простого, но очень полезного объекта, позволяющего удобно отмечать сделанные задачи, выделять элементы различных списков, играть роль бинарного переключателя "вкл-выкл" и т.д. Все вы с ними, конечно же, знакомы.
В этой статье мы рассмотрим несколько способов создания таких флажков-галочек в Microsoft Excel - для новых и старых версий Excel, соответственно. А в конце разберем реальную задачу, где эти чекбоксы можно применить.
Скользящее среднее (Simple Moving Average = SMA) - очень популярный метод анализа данных, используемый во множестве разных областей, включая финансовую аналитику, технический анализ в трейдинге, прогнозирование погоды, обработку сигналов и т.д.
Суть метода очень проста: для каждого значения временного ряда мы берём данные за N предыдущих периодов и их усредняем. Получается эдакое "окно" размером N элементов, скользящее по исходным данным - отсюда и название этого подхода. Само-собой, чем больше размер окна, тем сильнее получается усреднение и сглаживание.
В реальных задачах этот метод используют, например, чтобы:
Думаю, многие из вас уже не один и не два раза слышали или сталкивались с диаграммой Воронка Продаж - классической визуализацией работы с клиентами в любом бизнесе, связанном с продажами.
Всех поступивших к нам в этом, например, месяце 250 потенциальных клиентов (лидов) мы берём в оборот и сначала с ними связываемся (контакт). Тем, кого мы сумели заинтересовать высылаем нашу презентацию, а затем коммерческое предложение (КП). Ну, и какая-то часть из них в итоге (ура!) соглашается на сделку. Естественно, от этапа к этапу мы теряем часть потенциальных покупателей - кто-то в процессе передумает, кому-то не понравится наши цены, кого-то не "дожмут" наши менеджеры и т.д. И вот этот отсев критически важно отслеживать в любом бизнесе, чтобы понимать свои слабые места и докручивать скрипты продаж, коммуникацию с клиентами и т.д.
В Microsoft Excel есть несколько способов построить подобную визуализацию - от встроенных и простых до навороченных динамических:
