Создать таблицу в эксель для учета километража на машинах
Доброго времени суток!
Решил поделиться созданным для своей работы файлом (готовым решением)
Данный файл представляет собой форму контроля эксплуатации пяти транспортных единиц ( в моём случае это пассажирский транспорт)
В файле имеется два листа "Эксплуатация ТС" и "СВОД"
На листе "Эксплуатация ТС" ведётся контроль занятости ТС.
Пример ведения листа "Эксплуатация ТС" выложу постом ниже, в этом посте чистый файл (форма) без данных.
На листе "СВОД" сводная информация по трём группам: "Общее кол-ко часов в месяц" - строки со 2-й по 6-ю; "Среднее количество часов в смену по месяцу" - строки с 7-й по 11-ю; "Ремонт в рабочее время" - строки с 12-й по 16-ю.
Мне данный файл очень помогал тем, что при обращении ко мне одного или нескольких сотрудников нашей организации с просьбой выделить ему ТС, я мог визуально видеть какой транспорт сейчас свободен, а какой занят и если занят чрезмерно долго, то я не вспоминал кому отдал ТС, а видел это у себя в файле и вызванивал злостного покатушника. Т.е. при обращении ко мне с соответствующей просьбой, я выделяю ТС и закрепляю его на время, по возвращении сотрудника ставлю отметку (время возвращения) и ТС отображается как свободное.
Доброго времени суток!
Решил поделиться созданным для своей работы файлом (готовым решением)
Данный файл представляет собой форму контроля эксплуатации пяти транспортных единиц ( в моём случае это пассажирский транспорт)
В файле имеется два листа "Эксплуатация ТС" и "СВОД"
На листе "Эксплуатация ТС" ведётся контроль занятости ТС.
Пример ведения листа "Эксплуатация ТС" выложу постом ниже, в этом посте чистый файл (форма) без данных.
На листе "СВОД" сводная информация по трём группам: "Общее кол-ко часов в месяц" - строки со 2-й по 6-ю; "Среднее количество часов в смену по месяцу" - строки с 7-й по 11-ю; "Ремонт в рабочее время" - строки с 12-й по 16-ю.
Мне данный файл очень помогал тем, что при обращении ко мне одного или нескольких сотрудников нашей организации с просьбой выделить ему ТС, я мог визуально видеть какой транспорт сейчас свободен, а какой занят и если занят чрезмерно долго, то я не вспоминал кому отдал ТС, а видел это у себя в файле и вызванивал злостного покатушника. Т.е. при обращении ко мне с соответствующей просьбой, я выделяю ТС и закрепляю его на время, по возвращении сотрудника ставлю отметку (время возвращения) и ТС отображается как свободное. VEKTORVSFREEMAN
"Опыт - это то, что получаешь, не получив того, что хотел"
Мне данный файл очень помогал тем, что при обращении ко мне одного или нескольких сотрудников нашей организации с просьбой выделить ему ТС, я мог визуально видеть какой транспорт сейчас свободен, а какой занят и если занят чрезмерно долго, то я не вспоминал кому отдал ТС, а видел это у себя в файле и вызванивал злостного покатушника. Т.е. при обращении ко мне с соответствующей просьбой, я выделяю ТС и закрепляю его на время, по возвращении сотрудника ставлю отметку (время возвращения) и ТС отображается как свободное. Автор - VEKTORVSFREEMAN
Дата добавления - 11.03.2015 в 15:07
Прилагаю пример заполнения данной формы.
Вместо ТС 1, ТС 2 и т.д. указываем марку и гос.номер
Вместо Водитель 1 и т.д. указываем Фамилию водителя - для того, что бы на листе свод можно было посмотреть результат по месяцу у определённого водителя, вставив его фамилию в соответствующие ячейки О2:О6
Вместо сотрудник 1 и т.д. вписываете фамилию сотрудника для контроля "Кто эксплуатирует ТС"
Можно отображать ремонты, как с ТС УАЗ 456 в файле данного поста.
Для начала нового дня, вбиваем в столбец "В" поверх формулы автоматической нумерации новую дату.
По вопросам и предложениям отвечу далее в теме.
Прилагаю пример заполнения данной формы.
Вместо ТС 1, ТС 2 и т.д. указываем марку и гос.номер
Вместо Водитель 1 и т.д. указываем Фамилию водителя - для того, что бы на листе свод можно было посмотреть результат по месяцу у определённого водителя, вставив его фамилию в соответствующие ячейки О2:О6
Вместо сотрудник 1 и т.д. вписываете фамилию сотрудника для контроля "Кто эксплуатирует ТС"
Можно отображать ремонты, как с ТС УАЗ 456 в файле данного поста.
Для начала нового дня, вбиваем в столбец "В" поверх формулы автоматической нумерации новую дату.
По вопросам и предложениям отвечу далее в теме. VEKTORVSFREEMAN
"Опыт - это то, что получаешь, не получив того, что хотел"
VEKTORVSFREEMAN, привет.
Не было идеи добавить сюда контроль расхода топлива и пройденного расстояния в разрезе по машинам и отдельно по водителям? Например, если у всех в среднем расход топлива за месяц около 10 литров на 100 км, а у кого-то он вдруг 15 - у меня бы появились вопросы. Как вариант - возможна неисправность транспортного средства.
VEKTORVSFREEMAN, привет.
Не было идеи добавить сюда контроль расхода топлива и пройденного расстояния в разрезе по машинам и отдельно по водителям? Например, если у всех в среднем расход топлива за месяц около 10 литров на 100 км, а у кого-то он вдруг 15 - у меня бы появились вопросы. Как вариант - возможна неисправность транспортного средства. Rioran
Не было идеи добавить сюда контроль расхода топлива и пройденного расстояния в разрезе по машинам и отдельно по водителям? Например, если у всех в среднем расход топлива за месяц около 10 литров на 100 км, а у кого-то он вдруг 15 - у меня бы появились вопросы. Как вариант - возможна неисправность транспортного средства. Автор - Rioran
Дата добавления - 11.03.2015 в 16:08
Rioran, привет!
Нет, такой идеи не было
Основными идеями подтолкнувшими меня к созданию этого файла были:
1 идея - Визуально видеть эксплуатируется ли в настоящее время ТС (просто без учёта частенько забывал какую технику и кому отдал)
2 идея - Равномерное распределение ТС в течении одной смены (стараться эксплуатировать каждое ТС одинаковое кол-во раз и в общем по отработанному времени за смену). Этим решением я снял вопросы и претензии водителей, что кто то больше работает, а кто то попивает чай в гараже.
3 идея - Выяснить какие из ТС ломаются чаще всего, что бы по возможности не заявлять данные ТС на следующий рабочий месяц (ТС мне нужны для бесперебойной эксплуатации, а не для кол-ва).
Лист свод я добавил для предъявления водителям в случае претензий типа:
1 претензия - Кто то работает больше меня! "Посмотри сюда, это не так!
2 претензия - Почему ты подписываешь мне не весь рабочий день? "Посмотри сюда, ведь ты половину дня простоял на ремонте!"
3 претензия - Вы чрезмерно нагружаете меня поездками! " Посмотри сюда, ты отработал чистого времени лишь 3часа:15минут из восьми положенных."
И т.д.
Почему не возникло идеи заморочиться с расходом ГСМ и километрожем?
Поясню. В моей организации существует отдельное управление механизации, в нём около 100 единиц различной техники, за которой следят отдельные диспетчера и механики отвечающие за путевые листы, заправки и ремонты. Мой же отдел заявляет (заказывает на месяц) определённое количество ТС для организации работ отдела. Так что в мои обязанности входило предоставить по требованию ТС сотруднику.
Если поступит конкретное предложение доработать сей файл в каком либо ключе, то в силу своих знаний Excel попробую осуществить сей момент.
В макросах не силен, так что мой файл построен исключительно на формулах и доработки возможны с моей стороны только с формулами.
Rioran, привет!
Нет, такой идеи не было
Основными идеями подтолкнувшими меня к созданию этого файла были:
1 идея - Визуально видеть эксплуатируется ли в настоящее время ТС (просто без учёта частенько забывал какую технику и кому отдал)
2 идея - Равномерное распределение ТС в течении одной смены (стараться эксплуатировать каждое ТС одинаковое кол-во раз и в общем по отработанному времени за смену). Этим решением я снял вопросы и претензии водителей, что кто то больше работает, а кто то попивает чай в гараже.
3 идея - Выяснить какие из ТС ломаются чаще всего, что бы по возможности не заявлять данные ТС на следующий рабочий месяц (ТС мне нужны для бесперебойной эксплуатации, а не для кол-ва).
Лист свод я добавил для предъявления водителям в случае претензий типа:
1 претензия - Кто то работает больше меня! "Посмотри сюда, это не так!
2 претензия - Почему ты подписываешь мне не весь рабочий день? "Посмотри сюда, ведь ты половину дня простоял на ремонте!"
3 претензия - Вы чрезмерно нагружаете меня поездками! " Посмотри сюда, ты отработал чистого времени лишь 3часа:15минут из восьми положенных."
И т.д.
Почему не возникло идеи заморочиться с расходом ГСМ и километрожем?
Поясню. В моей организации существует отдельное управление механизации, в нём около 100 единиц различной техники, за которой следят отдельные диспетчера и механики отвечающие за путевые листы, заправки и ремонты. Мой же отдел заявляет (заказывает на месяц) определённое количество ТС для организации работ отдела. Так что в мои обязанности входило предоставить по требованию ТС сотруднику.
Если поступит конкретное предложение доработать сей файл в каком либо ключе, то в силу своих знаний Excel попробую осуществить сей момент.
В макросах не силен, так что мой файл построен исключительно на формулах и доработки возможны с моей стороны только с формулами. VEKTORVSFREEMAN
"Опыт - это то, что получаешь, не получив того, что хотел"
8 августа 2019, 12 часов
Как-то спонтанно пришла в голову идея: контролировать показания бортового компьютера. Для этого я сфотографировал все показания бортового компьютера и одометра (3 августа 2019), спустя несколько дней создал новый Excel документ из одного листа, с колонками: Дата, Пробег, Расход, Средняя скорость, Расчётный остаточный путь, Пройденный путь, Время (работы двигателя), Комментарий; и начал вести учёт :).
В общем называю это: Учёт поездок.
Что у меня подразумевается как «Поездка» — это не обязательно промежуток времени и расстояние от момента включения двигателя, до его выключения. За одну поездку может быть несколько старт/стопов двигателя. Например, поездка по магазинам или по делам неподалёку: когда я еду из точки А в точку Б (и В, Г, Д: может быть несколько промежуточных) и затем снова возвращаюсь в точку А – это я считаю одной поездкой. Главное чтобы простой в промежуточных точках не был более суток. Если же еду куда-то вдаль, в гости, например (А->Б): тогда по приезду, так сказать, завершаю «поездку», фоткаю борткомпьютер и сбрасываю показания; там может ещё катаюсь – учитываю как уже другую поездку(Б-В-Г-Б) и дорога обратно (Б->А). Но могу иногда и за один день зафиксировать две поездки, например, если куда-то ездил утром и в этот же день вечером. Тогда получается промежуток времени для разграничения поездок не обязательно сутки, а может быть и меньше. В общем – так, как я захочу детализировать :). Всё равно в итоге всё фиксируется.
Сначала я вёл таблицу на компьютере, по фоткам. Затем всё равно делал фото, но заполнял таблицу уже на телефоне (мобильный Excel), в свободное время (намного удобнее, т.к. и фотки под рукой и компьютер не нужен).
Сделать фотки – по времени занимает где-то около полуминуты. Затем сбрасываю показания бортового компьютера, чтобы в следующей поездке не париться по этому поводу. (Т.к. в первое время я сбрасывал показания прошлой поездки в начале текущей поездки, пару раз чуть не забывал сбросить значения).
И тем временем все фотки периодически (раз в полгода) сбрасываю на ПК.
Фотографии, кстати, сами подписывались датой, когда с телефона кидал оО, через менеджера загрузки с устройств (или как-то так) в Виндовсе. А то они стандартно называются IMG_*.jpg. На борткомпьютере на некоторых фото неправильное время :), если кто придерётся, уже в конечной таблице указано ДатаВремя из свойства фотографии "Дата съёмки" первой из 6 фотографий.
Так и вёл до середины августа 2020. [После 16 августа 2020 учёт поездок ведётся в другом месте, об этом позже, в следующих статьях :)]
Создайте спидометр или таблицу датчиков
Диаграмма датчика, также называемая циферблатной диаграммой или диаграммой спидометра, которая выглядит как спидометр в автомобилях, использующий иглу для отображения информации в виде показаний на циферблате, при изменении данных стрелка движется динамически, а также на скриншоте ниже.
В Excel измерительная диаграмма состоит из двух кольцевых диаграмм и круговой диаграммы, она показывает минимальное, максимальное и текущее значения на шкале. Его можно использовать для представления результатов продаж представителей или выполненной работы по сравнению с общей работой или других ситуаций с визуализацией. В этой статье я расскажу, как шаг за шагом создать шкалу или спидометр в Excel.
- Создайте диаграмму шкалы или спидометра в Excel
- Создайте в Excel таблицу манометра или спидометра с удивительной функцией
- Загрузите образец файла с таблицей датчиков или спидемометров
- Видео: создание шкалы или спидмометра в Excel
Создайте диаграмму шкалы или спидометра в Excel
Чтобы создать измерительную диаграмму, вы должны сначала подготовить некоторые диапазоны данных, как показано ниже:
Таблица 1: Эта таблица данных представляет уровни, количество и значение интервалов зависит от того, что вам нужно. В этом примере я установлю 3 интервала, которые в сумме дают 100. Не забудьте рассчитать общие значения, потому что это общее количество будет самой нижней частью полукруглой шкалы.
Таблица 2: Эта таблица используется для создания шкал от 0 до 100. Вы можете изменить ее, если хотите иметь другой диапазон шкалы.
Таблица 3: Эта таблица будет использоваться для создания круговой диаграммы для иглы:
- Текущая позиция: Положение иглы определено;
- Ширина: Ширина иглы;
- Конец: Значение оставшейся дуги круговой диаграммы, оно должно быть вычислено путем вычитания ширины и текущих точек из суммы значений уровней. В этом примере мы можем получить это по следующей формуле: =sum(B3:B6)-SUM(H3:H4)=200-82=118.
После подготовки приведенных выше данных выполните следующие действия шаг за шагом:
1. Выберите таблицу 1 и нажмите Вставить > Вставить круговую или кольцевую диаграмму > Пончик, и кольцевая диаграмма была вставлена в лист, см. скриншоты:
![]() | ![]() | ![]() |
2. Затем вы можете удалить заголовок и легенду диаграммы по мере необходимости, а затем щелкните правой кнопкой мыши кольцевую диаграмму и выберите Форматировать ряд данных из контекстного меню см. снимок экрана:
3. В Форматировать ряд данных панель, под Варианты серий значок, установите Угол первого среза в 270°, в то же время вы можете установить Размер отверстия для пончика в 50% как вам нужно, и вы получите следующую диаграмму:
4. На этом этапе вы должны скрыть нижнюю часть кольцевой диаграммы, дважды щелкните, чтобы выбрать нижнюю половину, и щелкните правой кнопкой мыши, чтобы выбрать Форматировать точку данных вариант, см. снимок экрана:
5. В Форматировать точку данных панель, под Заливка и линия значок, выберите Без заливки возможность сделать полукруг невидимым, см. снимок экрана:
6. Теперь, когда ваша первая часть датчика создана, вы можете добавить метки данных, чтобы облегчить чтение датчика. Щелкните, чтобы выбрать диаграмму, а затем щелкните Элементы диаграммы > Этикетки данных > Дополнительные параметры, см. снимок экрана:
7. В Форматирование меток данных панель, под Параметры метки значок, проверка Значение из ячеек вариант, а в выскочившем Диапазон метки данных диалоговом окне выберите метки производительности из первой таблицы, а затем нажмите OK, смотрите скриншоты:
![]() | ![]() | ![]() |
8. Метки данных были добавлены в диаграмму, а затем снимите флажок Значение вариант в Параметры метки раздел, чтобы удалить значение из меток, и вы можете отформатировать точку данных с определенными цветами в соответствии с вашими потребностями, см. снимок экрана:
9. Теперь, на этом шаге, вы должны создать вторую кольцевую диаграмму для отображения шкал, нажмите, чтобы выбрать диаграмму, а затем нажмите Выберите данные из Дизайн вкладку, см. снимок экрана:
10, Затем в Выберите источник данных диалоговое окно, нажмите Добавить кнопку, см. снимок экрана:
11. В Редактировать серию поле введите имя в поле Название серии текстовое поле, а затем в Значения серии поле, выберите данные значения из таблицы 2, см. снимок экрана:
12. Нажмите OK кнопку, и вы получите кольцевую диаграмму, как показано на скриншоте ниже:
13. И теперь вы можете отформатировать серый цвет для этой кольцевой диаграммы, чтобы сделать ее более профессиональной, щелкните, чтобы выбрать внешнюю кольцевую диаграмму, а затем нажмите Заливка формы под Формат вкладку, затем выберите серый цвет, как вам нужно, см. снимок экрана:
14. Опять же, вам нужно скрыть нижнюю половину этой кольцевой диаграммы, дважды щелкните, чтобы выбрать нижний полукруг, см. Снимок экрана:
15. А затем выберите Без заливки из Заливка формы под Формат Вкладка, теперь вы получите диаграмму, как показано на скриншоте ниже:
16. На этом этапе вы также можете добавить метки данных для ряда данных, щелкнуть диаграмму, а затем щелкнуть Элементы диаграммы > Этикетки данных > Дополнительные параметры, см. снимок экрана:
17. В Форматирование меток данных панель, под Параметры метки значок, проверка Значение из ячеек вариант, в выскочившем Диапазон метки данных диалоговом окне выберите метки шкалы из второй таблицы, а затем щелкните OK, смотрите скриншоты:
![]() | ![]() | ![]() |
18. После добавления меток шкалы снимите флажок Значение вариант в Параметры метки раздел, и метка данных была добавлена в диаграмму, см. снимок экрана:
19. После завершения двух кольцевых диаграмм на следующих шагах вы должны создать круговую диаграмму для иглы, щелкнуть диаграмму правой кнопкой мыши и затем щелкнуть Выберите данные из Дизайн в Выберите источник данных диалоговое окно, нажмите Добавить кнопку, см. снимок экрана:
20. В Редактировать серию поле введите имя в поле Название серии текстовое поле, а затем в Значения серии поле, выберите данные значения из таблицы 3, см. снимок экрана:
21, Затем нажмите OK Кнопка, вы получите диаграмму, как показано на скриншоте ниже:
22. Теперь вы должны изменить этот внешний пончик на круговую диаграмму, щелкните, чтобы выбрать внешний пончик, а затем щелкните Изменить тип диаграммы под Дизайн вкладку, см. снимок экрана:
23, Затем в Изменить тип диаграммы диалоговом окне выберите круговую диаграмму названия серии из Название серии, и выберите Круговая диаграмма из выпадающего списка см. снимок экрана:
24. А затем нажмите OK Кнопка, кольцевая диаграмма была изменена на круговую, как показано на скриншоте ниже:
25. Затем вы должны отформатировать отсутствие заливки для двух больших фрагментов, чтобы сделать их невидимыми, дважды щелкните серый фрагмент, чтобы выбрать его, а затем выберите Без заливки из Заливка формы вариант под Формат на вкладке, проделайте ту же операцию, чтобы скрыть синий кусочек, и вы получите снимок экрана, показанный ниже:
26. Скрыв два фрагмента, дважды щелкните его, чтобы выбрать крошечный фрагмент, а затем щелкните его правой кнопкой мыши и выберите Форматировать точку данных из контекстного меню см. снимок экрана:
27. В Форматировать точку данных панель, под Варианты серий значок, выполните следующие действия:
- Enter 270° в Угол первого среза коробка;
- Изменить Пирог взрыв значение для круговой диаграммы, чтобы указатель выступал из кольцевой диаграммы по своему усмотрению.
28. Теперь, таблица датчиков создана, наконец, вы должны добавить пользовательскую метку данных для иглы, для этого нажмите Вставить > текст Box > Нарисовать горизонтальное текстовое поле, см. снимок экрана:
29. Затем нарисуйте текстовое поле в середине полукольцевой диаграммы, выберите его и затем в строке формул введите знак равно и выберите ячейку с текущими значениями положения, затем нажмите Enter ключ. Смотрите скриншот:
30. Теперь калибровочная диаграмма создана успешно, наконец, вы можете переместить все метки данных в крайние углы, как вам нужно, см. Скриншоты:
![]() | ![]() | ![]() |
Создайте в Excel таблицу манометра или спидометра с удивительной функцией
Если вам больно с описанным выше сложным методом, здесь я порекомендую удобный инструмент - Таблица спидометра of Kutools for Excel, с помощью этой функции вы можете вставить диаграмму спидометра на 180 ° или 270 ° всего несколькими щелчками мыши, как показано ниже. Нажмите, чтобы загрузить Kutools for Excel для бесплатной пробной версии!
Загрузите образец файла с таблицей датчиков или спидемометров
Видео: создание шкалы или спидмометра в Excel
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Рассчитайте среднюю скорость на основе расстояния и времени с помощью формул
При вычислении средней скорости по расстоянию и времени вам может помочь следующая формула.
1. Пожалуйста, введите эту формулу: =A2/(B2*24) в пустую ячейку, где вы хотите вывести результат, а затем перетащите дескриптор заполнения вниз к ячейкам, в которых вы хотите получить все результаты, см. снимок экрана:
Внимание: В приведенной выше формуле A2 это расстояние, B2 самое время, пожалуйста, измените их по своему усмотрению.
2. Затем вы должны изменить формат результатов с формата времени на общий, нажмите Общие сформировать Формат номера выпадающий список в Число группы под Главная tab, и вы получите нужные результаты. Смотрите скриншот:
Внимание: Если у вас есть два списка времени начала и времени окончания, чтобы рассчитать среднюю скорость, как показано на следующем снимке экрана, как вы могли бы это сделать?
1. Введите эту формулу: =A2/(24 * (C2-B2)) в пустую ячейку, а затем скопируйте формулу в ячейки, в которых вы хотите получить результат, см. снимок экрана:
Внимание: В приведенной выше формуле: A2 - ячейка расстояния, B2 и C2 время начала и время окончания, измените их на свои собственные.
2. Затем отформатируйте формат ячеек формулы как общий, и вы получите результат, как показано ниже:
Предположим, что перед нами стоит классическая задача транспортной логистики: визуализировать движение некоего объекта по заданному маршруту из нескольких промежуточных точек. Для конкретики, давайте возьмем скорый фирменный поезд "Жигули", движущийся по маршруту Москва - Самара по следующему графику (взято из Яндекс.Расписаний):
Для решения задачи нам потребуется Excel 2013-2016 с установленной надстройкой Power Map. В Excel 2016 она установлена по умолчанию, для Excel 2013 можно скачать ее бесплатную превью-версию.
Этап 1. Находим координаты
Для однозначной привязки к промежуточным пунктам маршрута лучше использовать не названия населенных пунктов (они могут повторяться либо отсутствовать в принципе в нужном месте), а нормальные географические координаты. Достаточно щелкнуть по нужному месту в Яндекс-картах или Google Maps и вы увидите широту и долготу этой точки:
Добавим найденные координаты к нашей исходной таблице расписания движения поезда:
Этап 2. Дробим перегоны
Для плавного отображения движения поезда на карте нам необходимо разделить каждый перегон на несколько участков (чем их больше, тем плавнее будет анимация). Таким образом, перед нами встает задача получить примерные координаты и время для каждой промежуточной точки. Решить проблему можно формулой либо макросом.
Например, если хотим разбить каждый перегон на шесть интервалов (т.е. пять точек), то можно реализовать все одной формулой:
Но вставлять промежуточные строки, вводить и копировать формулу на все зеленые ячейки для каждого перегона придется вручную.
Другой вариант - макрос, что гораздо удобнее при большом количестве перегонов и промежуточных точек маршрута. Откройте редактор Visual Basic на вкладке Разработчик (Developer) или нажмите сочетание клавиш Alt + F11 . Вставьте в вашу книгу новый пустой модуль через меню Insert - Module и скопируйте туда этот код:
Как легко сообразить, константа MINS_IN_ONE_STEP задает количество минут в каждом шаге - можете менять ее значение по своему усмотрению. Теперь если выделить таблицу с данными или установить в нее активную ячейку, а потом запустить наш макрос сочетанием клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer - Macros) , то наша таблица будет преобразована в следующий вид:
Как видите, каждый перегон теперь делится на несколько интервалов - по 1 минуте каждый.
Этап 3. Переходим к карте
Осталось совсем чуть-чуть. Выделите полученную таблицу и на вкладке Вставка нажмите кнопку 3D-карта (Insert - 3D-map) :
Не перепутайте ее с кнопкой Карты (которая с глобусом) или Карты Bing (желтого цвета). После нажатия должно открыться окно надстройки Power Map.
В правой части окна на панели добавьте в группе Расположение (Location) поля широты и долготы и выберите напротив каждого название соответствующего столбца из нашей таблицы. Если все сделаете правильно, то на карте тут же должен отобразиться наш маршрут:
Теперь осталось выбрать в выпадающем списке Время (Time) столбец со значениями даты-времени из нашей таблицы и можно запускать анимацию с помощью кнопки воспроизведения в нижней части окна:
Дополнительно можно поиграться настройками слоя - кнопка Параметры слоя (Layer Options) в правом нижнем углу - и установить цвет, размер, прозрачность и т.д. отображаемых точек.
Если нажать на неприметную иконку с часами рядом с выпадающим списком Время, то можно поменять режим отображения и рисовать не маршрут, а сам поезд.
При щелчке левой кнопкой мыши по любой интересующей точке маршрута мы увидим ее подробные данные - координаты и время прохождения:
Этап 4. Несколько поездов сразу
Не секрет, что на самом деле по маршруту Москва-Самара курсируют два состава - в противофазе: когда один стартует из Москвы, другой примерно в то же время начинает движение ему навстречу из Самары. Утром один из них приходит в Самару, а другой, соответственно, в Москву и вечером процесс запускается заново. Расписание второго примерно отзеркаливает первый:
Что сделать, чтобы отобразить их на карте оба сразу?
Если по маршруту одновременно движется больше одного объекта, то данные по ним можно обработать аналогичным образом (Этапы 1 и 2) и просто добавить в продолжение нашей маршутной таблицы. А чтобы отличать поезда друг от друга, добавить еще один столбец с названием объекта:
Теперь, если построить по такой таблице еще одну визуализацию, мы будем видеть движение двух составов одновременно:
Ссылки по теме
Добрый день
Николай,-очень кстати данная тема, работа связана с транспортом и маршрутами,давно ждал что то подобное.Огромное Вам спасибо
Просто прекрасная и нужная инструкция. Николай, коллеги, а подскажите, пожалуйста, какой модуль к MS Office должен быть подключен для того, чтобы было доступно:
Вставка ---> нажмите кнопку 3D-карта (Insert - 3D-map) :
"Для решения задачи нам потребуется Excel 2013-2016 с установленной надстройкой Power Map. В Excel 2016 она установлена по умолчанию, для Excel 2013 можно скачать ее бесплатную превью-версию ."
Спасибо за урок! Не сочтите за докапывание, но в конце 2 этапа "Как видите, каждый перегон теперь делится на несколько интервалов - по 1 секунде каждый." - по минуте же - не?
Э.. да, конечно! Спасибо!
Очень крутая тема .
Предлагаю развить на предмет расчета расстояния.
Подскажите пожалуйста как это возможно реализовать с помощью google map например?
У меня есть вот такой макрос, который рассчитывает расстояние, маршрут и время в пути. Мне необходима только та часть которая отвечает за измерение расстояния. Самостоятельно разобрать не хватает знаний. Буду признателен за помощь.
Option Explicit
Public ActivationMark As Boolean
Public WasRequestGoogle As Boolean
Public MyDistance As Variant
Public MyDuration As Variant
'Задаем границы допустимых координат
Public Const Lat_min = -180, Lat_max = 180
Public Const Lon_min = -180, Lon_max = 180
'Скрываем заставку
Private Sub KillTheForm()
Unload Excelminsk
End Sub
Sub GetDistanceDurationGoogle(Address1 As String, Address2 As String)
Dim XMLDoc As Object
Dim Coord1NodeList As Object, Coord2NodeList As Object
Dim DistanceNodeList As Object, DurationNodeList As Object
Dim MyRequest As String
Dim Lat1 As String, Lon1 As String, Lat2 As String, Lon2 As String
On Error Resume Next
'Обнуляем переменные
MyDistance = ""
MyDuration = ""
'Ставим задержку между запросами
If (Address1 = Range("A3";) And Address2 = Range("B3";)) Then
Else
Application.Wait (Now + TimeValue("0:00:01";))
End If
'Кодируем адрес
Address1 = RussianStringToURLEncode_New(Address1)
Address2 = RussianStringToURLEncode_New(Address2)
MyRequest = "https://maps.googleapis.com/maps/api/directions/xml?origin=" & Address1 & "&destination=" & Address2 & "&mode=driving&language=ru"
'Debug.Print MyRequest
'Загружаем XML-документ
Set XMLDoc = CreateObject("Msxml2.DOMDocument";)
XMLDoc.async = False
If Not XMLDoc.Load(MyRequest) = True Then
MyDistance = "!ДАННЫЕ НЕ ЗАГРУЖЕНЫ"
MyDuration = "!ДАННЫЕ НЕ ЗАГРУЖЕНЫ"
Exit Sub
End If
'Считываем статус ответа
Select Case XMLDoc.SelectNodes("*/status";).Item(0).text
Case "OK"
Case "NOT_FOUND"
'Не нашел адрес точки
MyDistance = "!НЕ НАШЕЛ АДРЕС"
MyDuration = "!НЕ НАШЕЛ АДРЕС"
Exit Sub
Case "ZERO_RESULTS"
'Не может проложить маршрут
MyDistance = "!НЕТ ДОРОГИ"
MyDuration = "!НЕТ ДОРОГИ"
Exit Sub
Case "OVER_QUERY_LIMIT"
If WasRequestGoogle = False Then
Application.Wait (Now + TimeValue("0:00:02";))
WasRequestGoogle = True
Call GetDistanceDurationGoogle(Address1, Address2)
Exit Sub
Else
MyDistance = "!ПРЕВЫШЕНИЕ ЛИМИТА"
MyDuration = "!ПРЕВЫШЕНИЕ ЛИМИТА"
Exit Sub
End If
Case "REQUEST_DENIED"
MyDistance = "!ЗАПРОС ОТКЛОНЕН"
MyDuration = "!ЗАПРОС ОТКЛОНЕН"
Exit Sub
Case "INVALID_REQUEST"
MyDistance = "!НЕВЕРНЫЙ ЗАПРОС"
MyDuration = "!НЕВЕРНЫЙ ЗАПРОС"
Exit Sub
Case "UNKNOWN_ERROR"
MyDistance = "!НЕИЗВЕСТНАЯ ОШИБКА"
MyDuration = "!НЕИЗВЕСТНАЯ ОШИБКА"
Exit Sub
End Select
'Получаем координаты
Set Coord1NodeList = XMLDoc.SelectNodes("*//start_location";)
Lat1 = Coord1NodeList.Item(Coord1NodeList.Length - 1).FirstChild.text
Lon1 = Coord1NodeList.Item(Coord1NodeList.Length - 1).LastChild.text
Set Coord2NodeList = XMLDoc.SelectNodes("*//end_location";)
Lat2 = Coord2NodeList.Item(Coord2NodeList.Length - 1).FirstChild.text
Lon2 = Coord2NodeList.Item(Coord2NodeList.Length - 1).LastChild.text
'Debug.Print "Coord1=" & Lat1 & ", " & Lon1
'Debug.Print "Coord2=" & Lat2 & ", " & Lon2
'Проверяем ограничения для координат
If MyValue(Lat1) < Lat_min Or MyValue(Lat1) >Lat_max Or MyValue(Lon1) < Lon_min Or MyValue(Lon1) >Lon_max Or _
MyValue(Lat2) < Lat_min Or MyValue(Lat2) >Lat_max Or MyValue(Lon2) < Lon_min Or MyValue(Lon2) >Lon_max Then
MyDistance = "!ОГРАНИЧЕНИЕ ДЕМО"
MyDuration = "!ОГРАНИЧЕНИЕ ДЕМО"
Else
'Расстояние в метрах
Set DistanceNodeList = XMLDoc.SelectNodes("*//distance";)
MyDistance = Round(DistanceNodeList.Item(DistanceNodeList.Length - 1).FirstChild.text / 1000, 0)
'Debug.Print "MyDistance *//duration";)
MyDuration = CLng(DurationNodeList.Item(DurationNodeList.Length - 1).FirstChild.text) / 3600 / 24
'Debug.Print "MyDuration %" & Hex(AscW(l) \ 64 \ 64 + 224) & "%" & Hex(AscW(l) \ 64) & "%" & Hex(8 * 16 + AscW(l) Mod 64)
Case Is > 127: t = "%" & Hex(AscW(l) \ 64 + 192) & "%" & Hex(8 * 16 + AscW(l) Mod 64)
Case 32: t = "%20"
Case Else: t = l
End Select
RussianStringToURLEncode_New = RussianStringToURLEncode_New & t
Next
End Function
'Конвертируем широту и долготу из текста в число
Function MyValue(ByVal text As String) As Double
Dim MySeparator As String
'Считываем системный разделитель
MySeparator = Application.International(xlDecimalSeparator)
MyValue = (Trim(Replace(text, ".", MySeparator)) + 0)
End Function
Читайте также: