#ExcelWorldChamp 3-4 последнее взрывоопасное задание

С достаточно большим перерывом, который связан с завершением курса Excel от новичка до профессионала, публикуем решение последнего задания третьего тура мирового чемпионата по Excel #ExcelWorldChamp.

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

Задание третьего тура на умение работать с формулами оказалось самым сложным и необычным, несмотря на то, что само задание выглядит вполне стандартно: ответить на пять вопросов.

Начальные условия

Есть дорожная карта и небольшое пояснение к ней.

image

Зеленые участки – это стандартные участки с обозначением времени в минутах, которое необходимо для их преодоления. Если на преодоление участка затрачивается 10 минут или менее, то он считается свободным и закрашивается зелены.

Красные участки – также, как и зеленые открыты для проезда, но, поскольку на их преодоление требуется больше 10 минут они считаются загруженными.

Черные участки "999" – означает, что проезд временно закрыт и проезд, в данный момент, невозможен. Однако, в будущем данный участок может открыться.

Синие участки с буквами – обозначение пунктов.

Заштрихованные участки – зона, запрещенная к использованию и проезд по ней не возможен ни сейчас, ни в будущем.

Суть задания

Необходимо ответить на пять вопросов, все ответы должны рассчитываться формулами и пересчитываться, если начальные условия поменяются. Как и в предыдущих турах, можно использовать вспомогательную область справа, для промежуточных расчетов:

  1. Сколько участков закрыты?
  2. Какой процент открытых загруженных участков?
  3. Какое среднее время проезда по свободным участкам?
  4. Укажите наиболее быстрый путь в минутах из пункта "А" в пункт "F" (возможен транзитный проезд по другим пунктам).
  5. Какие промежуточные пункты будут посещены на пути из пункта "A" в пункт "F" с наименьшим временем движения (см. вопрос 4)? Ответ должен состоять из букв, например, если самый короткий путь из "А" в "F" через пункты "С", "E", то ответ: "ACEF".
Решение

Сколько участков закрыты?

Ответить на первый вопрос проще простого, поскольку закрытые участки обозначены числом "999", то для построения формулы будет достаточно одной функции СЧЁТЕСЛИ с полным выделением всего диапазона.

=СЧЁТЕСЛИ(F38:J44;999)

Какой процент открытых загруженных участков?

Ответ на второй вопрос не сильно сложнее первого, т.к. на опять нужно провести подсчет с условиями, только ответ перевести в проценты. Главное не забыть, что процент нужно посчитать не среди всех участков (их всего 35), а только среди открытых, т.е. зеленых и красных.

=СЧЁТЕСЛИМН(F38:J44;">10";F38:J44;"<999")/СЧЁТЕСЛИ(F38:J44;"<999")

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

Какое среднее время проезда по свободным участкам?

Последний простейший вопрос, ответ на который можно получить, используя единственную функцию СРЗНАЧЕСЛИ.

=СРЗНАЧЕСЛИ(F38:J44;"<=10";F38:J44)

Первым аргументом мы ищем свободные участки, вторым указываем условие, а в третьим – указываем что усреднять, в нашем случае диапазоны в первом и третьем аргументе функции совпадают.

Укажите наиболее быстрый путь в минутах из пункта "А" в пункт "F" (возможен транзитный проезд по другим пунктам).

Быстрое нахождение ответов на первые вопросы подталкивало на мысль, что где-то должен быть подвох, ведь третий тур не мог так просто сдастся. И, действительно, ответ на четвертый вопрос потребовал ни много, ни мало, а практически целый рабочий день.

Трудности возникали практически на каждом шагу: начиная от того, что не понятно каким образом искать минимальный путь, или сколько всего путей может быть, и заканчивая тем, что нужно учитывать возможность открытия временно закрытых участков (с числом "999").

Различных вариантов и подходов к решению задачи было множество, всех уже и не вспомнить, представим только те, которые привели к решению поставленной задачи.

Во-первых, все возможные пути следует разбить на пути между двумя пунктами (AC, AB, AF и т.д.), а конечный маршрут считать как сумму простых путей. Для реализации необходимо построить матрицу путей между пунктами, учитывая то факт, что между двумя пунктами может быть два пути.

image

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

На следующем этапе необходимо построить всевозможные варианты прохождения из пункта "A" в пункт "F", учитывая тот факт, что из любого пункта можно добраться в любой пункт общее количество путей будет равняться сумме количества перестановок 4 по 4, 4 по 4, 4 по 2, 4 по 1 и плюс прямой вариант "AF".

\sum = A_{4}^{4}+A_{4}^{3}+A_{4}^{2}+A_{4}^{1}+1

что равняется:

\sum =\frac{4!}{(4-4)!}+\frac{4!}{(4-3)!}+\frac{4!}{(4-2)!}+\frac{4!}{(4-1)!}+1=65

Всего получается 65 возможных вариантов маршрута добраться из пункта "A" в пункт "F", их необходимо расписать, потом из нашей таблицы найти время проезда, из это времени найти минимальный.

image

Варианты маршрута нумеруются и записываются исходя из возможных вариантов перестановок (см. формулы выше), далее записывается вспомогательный диапазон с числами, его предназначение указать сдвиг для выбора нужного числа из матрицы для будущей формулы расчета стоимости между пунктами. Столбцов 5, а не 6, т.к. маршрута "А-А" быть не может.

image

И, собственно, сам расчет стоимости по 65 маршрутам.

image

Далее просто находится сумма по строкам и среди них находится минимум с помощью банальной функции МИН.

В случае неизменности первоначальных данных минимум равняется 74.

Какие промежуточные пункты будут посещены на пути из пункта "A" в пункт "F" с наименьшим временем движения.

Несмотря на то, что ответ на последний вопрос достаточно прозаичный, поскольку маршрут с минимальным временем уже найден, несколько расчетов, все же, придется сделать.

На первом шаге следует вычислить номер строки, которая содержит кратчайший путь. Это можно легко сделать с помощью связки ИНДЕКС и ПОИСКПОЗ, т.к. столбец с кратчайшим путем находится с права от диапазона с пунктами.

image

Итоговая строка выводится простой ВПР, единственное, нужно предусмотреть вариант, когда букв будет меньше 6, чтобы не было "0".

=ЕСЛИ(ВПР($J$6;$I$11:$O$75;K7;ЛОЖЬ)<>0;ВПР($J$6;$I$11:$O$75;K7;ЛОЖЬ);"")

image

И в завершение выводим итоговую строку с маршрутом, делается это простой функцией СЦЕП или ОБЪЕДИНИТЬ.

=СЦЕП(K8:P8)

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

Кто хочет попробовать свои силы, вот ссылки на файлы:
Исходники
Решенные