А вот теперь пришло время для того, чтобы сделать нашу книгу чем-то отличающейся от ее аналогов. Всё-таки ничего необычного в том, что автолюбители учитывают расходы на бензин и пробег в Excel нет. А давайте мы переведем бездушные цифры пробега за месяц в конкретные маршруты, которые можно увидеть, например, на карте.
Для этого нам понадобится таблица с расстояниями между городами. Их на просторах интернета огромное количество. Выглядят примерно вот так:
Пусть нас интересует вполне конкретный город, от которого мы и будем проводить измерение расстояний. Я живу в Иркутске. Однако у нас в Сибири просторы такие, что населенные пункты встречаются не так и часто. Поэтому возьмем, для примера, Москву. Скопируем для этого города расстояние на отдельный лист.
Если у вас Offiсe 365, то последовательность столбцов не важна, поскольку функция ПРОСМОТРХ, которая нам понадобится для выведения расстояния, работает с любой очередностью столбцов. Если же у вас более ранние версии, то нам необходимо, чтобы столбец с наименованиями городов находился правее от расстояний.
В первые столбцы этого листа с помощью ссылок на ячейки листа с пробегом (см. часть 1 нашей работы) получаем значения одометра на каждый день поездки. А в столбце С получаем номер месяца, в котором была совершена поездка (рис. 3).
Для получения номера месяца используем функцию =Месяц(). Однако чтобы Excel не выдавал нам ошибку из-за отсутствующих значений, вводим эту функцию уже стандартным для нас способом через сочетание функций ЕСЛИ и ЕОШИБКА. Смысл состоит в том, что если ячейка в столбце А пустая, то и в столбце С ячейка тоже будет пустой. Если же ячейка в столбце А заполнена, то соответствующая ей ячейка в столбце С выведет порядковый номер месяца.
Получив номер месяца, мы легко можем разграничить месяцы друг с другом и нарастающим итогом определить пробег за месяц (рис. 4).
Смысл функции в столбце D состоит в том, что если значение месяца для строки равно значению месяца для предыдущей строки, то пробег на предыдущую дата нарастающим итогом нужно суммировать с пробегом за текущую дату. Если же индексы месяцев не равны, то отсчет пробега начинается заново и с предыдущим значением он не суммируется.
И вот теперь самое сложное и одновременно самое интересное. Мы должны найти город, расстояние до которого из Москвы примерно соответствует нашему пробегу внутри каждого месяца. Суть подхода в том, что мы должны найти минимальное значение разности между текущим пробегом нарастающим итогом и расстоянием от Москвы до соответствующего города. Для этого нам потребуется занести формулу как формулу массива (для этого используется словосочетание клавиш CTRL+SHIFT+ENTER). Сама же формула будет выглядеть следующим образом (рис. 5).
Для большей наглядности приведу функцию в текстовом виде:
=ЕСЛИ(ЕОШИБКА(ИНДЕКС(G6:G824;ПОИСКПОЗ(МИН(ABS(G6:G824-D6));ABS(G6:G824-D6);0)));"";ИНДЕКС(G6:G824;ПОИСКПОЗ(МИН(ABS(G6:G824-D6));ABS(G6:G824-D6);0)))
Еще раз подчеркну, что формула вводится как функция массива, в итоге она заключается в квадратные скобки и копируется на весь столбец. В чем ее смысл. Вот эта ее часть:
МИН(ABS(G6:G824-D6))
возвращает нам минимальное значение из разности между всеми ячейками диапазона G6:G824 и ячейкой D6.
Вот эта часть:
ПОИСКПОЗ(МИН(ABS(G6:G824-D6));ABS(G6:G824-D6)
ищет место найденного минимального значения в диапазоне разностей ABS(G6:G824-D6). То есть, значение, обеспечивающее минимальную разность может находиться в 30-й строке просматриваемого диапазона. А вот затем уже часть
ИНДЕКС(G6:G824;ПОИСКПОЗ...
возвращает нам ближайший километраж к пройденному нами расстоянию.
Ну а дальше уже всё просто. С использованием знаменитой функции ВПР мы получаем название города, соответствующее найденному ближайшему километражу (рис. 6).
Таким образом, на каждый день внутри месяца мы можем получить город, до которого ы виртуально добрались в этом периоде (рис. 7).
Легко заметить, что до 17 строки расстояние нарастающим итогом растет, а вот затем наш счетчик обнуляется, а отсчет расстояний начинается заново.
Ну и как? Не сложно? Или нужно объяснять подробнее алгоритм работы функций? Спасибо за то, что дочитали