Excel для автоматического путевого листа

Добрый день уважаемые знатоки Excel.  

  Помогите в решении такой задачи.    
Создал файл, который предназначен для заполнения бланков путевых листов. Есть лист «База для АВТО» — представляет собой базу, в которую вносятся необходимые даннные, которые затем попадают на лист «Легковые АВТО» (данные попадают в бланк при помощи «x» — см. статью на сайте Заполнение бланков данными из списка (базы данных) ).  

  Все хорошо, но для того чтобы было еще лучше мне как то нужно автоматизировать процесс печати бланков. На данный момент для того чтобы распечатать бланк, мне нужно сначала поставить «x» напротив выбранной позиции на листе «База для АВТО», а затем при помощи макроса Печать, который я привязал к специальной кнопке на листе — распечатать бланк путевого листа.  

  Что хотелось бы видеть в идеале: для того чтобы не ставить каждый раз «х» на каждой строке и не нажимать кнопку Печать, хотелось бы один раз поставить «x» напротив той строчки, с которой нужно начать печать (например ячейка А3), затем нажать кнопку печать. после этого макрос сам автоматически менял положение x (переводил х на ячейку А4) —  и так далее по порядку (соответственно после смены положения х данные в бланке будут меняться на новые) и распечатывался новый бланк.    

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

  Всем спасибо.

Уровень сложности
Средний

Время на прочтение
6 мин

Количество просмотров 2.4K

Путевой лист — это такой документ, который является основанием для списания горючего в состав расходов в бухгалтерском и налоговом учёте. А ещё путевые листы нужны, чтобы обосновать необходимость аренды или лизинга автотранспорта. 

Однако путевой лист достаточно запутанный первичный документ и на его заполнение можно потратить много времени. И это время нужно тратить регулярно. Эта статья — моя попытка сделать универсальный шаблон в гугл таблицах с возможностью полного автоматического заполнения буквально в два клика.  

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

При этом заполнение происходит рандомно сгенерированными данными на основании всего нескольких достоверных начальных параметров: начального и конечного показания одометра автомобиля и задания точного района, где эксплуатируется этот автомобиль. 

Новый путевой лист с 2023 года

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

• Информацию о водителе

• Данные о транспортном средстве

• Данные о ТС

• Срок действия путевого листа

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

Как работает таблица

Моя форма путевого листа доступна по ссылке. По ссылке откроется запрос на создание вашей копии. При согласии будут также созданы копии прикрепленного файла Apps Script и относящихся к нему функций генерации адресов.

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

1. Информация о владельце транспортного средства: наименование, ИНН, ОГРН, адрес, телефон;

2. Информация об автомобиле: модель, государственный регистрационный знак, идентификационный номер (VIN);

3. Информация о водителе: фамилия имя отчество, номер водительского удостоверения.

Автоматизация начинается с первой даты выезда — ячейки B24 — как только вы введете эту дату таблица сама заполнит оставшиеся даты этого столбца рабочими днями — там прописана формула.

Нижняя часть путевого листа содержит очень важные исходные данные для автоматизации:

1. Начальный километраж по одометру.

2. Конечный километраж по одометру.

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

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

Самые нижние строчки, связанные с остатком топлива в баке и количеством заправленного топлива и на автоматизацию не влияют.

Как настроить случайное место назначения в нужном районе

После того как даты и времена заезда и выезда, а ещё показаниями одометра на каждый день заполнены переходим к случайным адресам. Здесь пригодятся функции Apps Script. 

В файл скриптов, привязанных к этой таблице, уже внесён следующий код обратного геокодирования (вычисления адреса по координатам) :

/**
 * Use Reverse Geocoding to get the address of
 * a point location (latitude, longitude) on Google Maps.
 *
 * =GOOGLEMAPS_REVERSEGEOCODE(latitude, longitude)
 * 
 * https://www.labnol.org/google-maps-sheets-200817
 * 
 * https://developers.google.com/apps-script/reference/maps/geocoder#setlanguagelanguage
 *
 * @param {String} latitude The latitude to lookup.
 * @param {String} longitude The longitude to lookup.
 * @return {String} The postal address of the point.
 * @customFunction
 */

const GOOGLEMAPS_REVERSEGEOCODE = (latitude, longitude) => {
    const {
        results: [data = {}] = []
    } = Maps.newGeocoder().setLanguage('ru').reverseGeocode(latitude, longitude);
    return data.formatted_address;
};

Скрипт позволит запускать формулу вида =GOOGLEMAPS_REVERSEGEOCODE(58,0205391879819; 56,2652395772995) которая возвращает адрес по координатам из гугл карт. Например, для этого запроса возвращается адрес Краснокамская ул., 20, Пермь, Пермский край, Россия, 614060.

Существует ограничение на количество запросов геокодирования, но несколько листов в день можно заполнить без проблем. Если нужно, больше, то надо уже получать ключ API сервиса.

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

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

/**
 * Get random geo point [latitude, longitude] within some distance from specified geo point (lat, long)
 *
 * Points will be uniformly-distributed on multiple calls.
 *
 * @param lat Latitude in degrees
 * @param lng Longitude in degrees
 * @param distance Distance in meters to limit point distribution to.
 *                 If negative value is provided, points will be generated on exact distance (e.g. on circle border).
 * @returns {*[]} Array with [latitude, longitude]
 */

var getRandomLocation = function(lat, lng, distance = 10000) {
    // Convert to radians
    lat *= Math.PI / 180;
    lng *= Math.PI / 180;

    var radius;

    // Distance should be set in meters, negative for exact distance
    if (distance < 0) {
        // Exact distance
        radius = Math.abs(distance);
    } else {
        // Get uniformly-random distribution within peovided distance
        // http://stackoverflow.com/questions/5837572/generate-a-random-point-within-a-circle-uniformly
        radius = Math.random() + Math.random();
        radius = radius > 1 ? 2 - radius : radius;
        radius *= distance ? distance : 10000; // multiply by distance meters
    }

    // Convert radius from meters to degrees to radians
    // 111319.9 meters = one degree along the equator
    radius /= 111319.9;
    // Correction for the actual distance from equator is NOT needed here
    // radius *= Math.cos(lat);
    // Convert to radians
    radius *= Math.PI / 180;

    // Random angle
    var angle = Math.random() * Math.PI * 2;

    // Get a point {nLat,nLng} in a distance={radius} out on the {angle} radial from point {lat,lng}
    // From Aviation Formulary V1.46 By Ed Williams:
    // → http://williams.best.vwh.net/avform.htm#LL
    // → ftp://ftp.bartol.udel.edu/anita/amir/My_thesis/Figures4Thesis/CRC_plots/Aviation%20Formulary%20V1.46.pdf
    // → https://github.com/arildj78/AvCalc/blob/master/avform.txt
    // [section "Lat/lon given radial and distance"]
    var nLng,
        nLat = Math.asin(Math.sin(lat) * Math.cos(radius) + Math.cos(lat) * Math.sin(radius) * Math.cos(angle));
    if (Math.cos(nLat) == 0) {
        nLng = lng;
    } else {
        nLng = (lng - Math.asin(Math.sin(angle) * Math.sin(radius) / Math.cos(nLat)) + Math.PI) % (Math.PI * 2) - Math.PI
    }

    // Convert to degrees
    nLat *= 180 / Math.PI;
    nLng *= 180 / Math.PI;

Пошаговое руководство по заполнению

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

Далее перейдите в гугл скрипты — для этого выбирайте пункт меню Apps Script.

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

При первом запуске скрипта надо разрешить выполнение скрипта от своего аккаунта.

После выполнения скрипта в появившемся журнале выполнения можно будет увидеть информацию об успешной генерации случайных адресов:

После этого все места назначения на указанной вкладке будут заполнены случайными координатами, которые при помощи формулы обратного геокодирования будут сразу преобразованы в адреса.

Что делать после заполнения данных

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

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

Итог

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

• начального и конечного показания одометра автомобиля;

• задания точного района, где эксплуатируется этот автомобиль.

Когда начальные данные уже заданы, то следующий месяц можно полностью заполнить данными меньше чем за минуту.

Автор: Михаил Шардин,

17 января 2023 г.

Здесь можно получить ответы на вопросы по Microsoft Excel 57731 472245

13 Окт 2018 13:13:39

44519 357828

29 Янв 2017 17:28:40

Лучшие избранные темы с основного форума 14 80

28 Июн 2018 15:25:11

Если вы — счастливый обладатель Mac ? 217 1059

21 Сен 2018 06:21:34

Раздел для размещения платных вопросов, проектов и задач и поиска исполнителей для них. 2104 13408

13 Окт 2018 13:19:33

Если Вы скачали или приобрели надстройку PLEX для Microsoft Excel и у Вас есть вопросы или пожелания — Вам сюда. 310 1587

9 Окт 2018 15:21:02

810 11708

13 Окт 2018 10:54:50

Обсуждение функционала, правил и т.д. 269 3467

10 Окт 2018 12:27:11

Сейчас на форуме (гостей: 1170, пользователей: 11, из них скрытых: 1) , , , , , , , , ,

Сегодня отмечают день рождения (45), (30), (40), (29)

Всего зарегистрированных пользователей: 83198

Приняло участие в обсуждении: 31878

Всего тем: 105973

Путевой лист легкового автомобиля – документ, который ежедневно оформляется водителем для учета движения транспортного средства организации. Он необходим для отслеживания маршрута движения машины, учета расходов на обслуживание транспорта и закупку ГСМ, расчета зарплаты водителя. Для заполнения путевого листа используется форма 3.

Бланк путевого листа легкового автомобиля 2018 года

Предлагаем бесплатно скачать новую форму 3 для акта путевого листа легкового автомобиля, действительную в 2018 году.

Как заполнить путевой лист легкового автомобиля?

В путевой лист по форме 3 вносятся следующие сведения:

Хватит тратить время на заполнение шаблонов и бланков

Сервис КУБ помогает выставлять счета за 20 секунд и готовить другие документы без единой ошибки, за счет полной автоматизации заполнения шаблонов.

КУБ – новый стандарт выставления и отправки счетов покупателям.

Образец заполнения формы 3 для путевого листа легкового автомобиля

Правильно оформить путевой лист легкового автомобиля по форме 3 2018 года вам помогут образы заполненных бланков, которые представлены для бесплатного скачивания в разных форматах.

Если вы часто оформляете путевые листы и выписываете другие документы, зарегистрируйтесь в сервисе КУБ. Он позволяет заполнять документацию в автоматическом режиме и отправлять ее контрагентам по e–mail.

Хватит тратить время на заполнение шаблонов и бланков

Сервис КУБ помогает выставлять счета за 20 секунд и готовить другие документы без единой ошибки, за счет полной автоматизации заполнения шаблонов.

КУБ – новый стандарт выставления и отправки счетов покупателям.

узнайте больше про куб сейчас

Автоматизация
выставления счета

Автозаполнение реквизитов покупателя по ИНН

Автопроверка правильности реквизитов банка по БИКу

Правильно настроенные формулы, в том числе по расчету НДС и Итого счета

Автоматическое формирование суммы прописью по итоговым цифрам суммы счета

Выгоды

Подготовка счета за 20 секунд

Отсутствие ошибок в счетах

Меньше времени на выставление счетов, и больше на бизнес

Увеличение скорости оплаты счетов

Автоматизация
отправки счета

Автоматическое прикрепление вашего логотипа в счет

Автоматическое прикрепление скана вашей подписи в счет

Автоматическое прикрепление в счет скана печати вашей компании

Отправка счета на e-mail покупателя

Выгоды

Отправка счета в 2 клика

Красивые и профессиональные счета

Можно выставлять счета с компьютера, планшета, смартфона

Не нужно распечатывать счет, ставить подпись, печать и сканировать перед отправкой

почему куб удобнее

Удобное выставление счетов онлайн

Моментальная отправка счетов на e-mail вашему покупателю

Контроль задолженности по каждому покупателю

Управленческая отчетность

Упорядоченное хранение всех ваших документов

Скидка 20% на бухгалтерское обслуживание от вашего бухгалтера

Хватит тратить время на заполнение шаблонов и бланков

Сервис КУБ помогает выставлять счета за 20 секунд и готовить другие документы без единой ошибки, за счет полной автоматизации заполнения шаблонов.

КУБ – новый стандарт выставления и отправки счетов покупателям.

Нужна помощь по заполнению документов или консультация?

Получите помощь экспертов бухгалтеров по подготовке документов

(

Рейтинг, среднее:

из 5)

Продолжаю выкладывать печатные формы с кратким описанием первичных и иных документов и их версии в Excel. На очереди путевой лист.  Постановлением 78 Госкомстата РФ от 28.11.1997 г. установлено, что если вы управляете механическим транспортным средством, то вы обязаны иметь при себе путевой лист. В статье привел краткое описание этого документа и ссылку на путевой лист в Excel.

Путевой лист, он же «путевка», на самом деле, довольно важный документ и его необходимо использовать/заполнять всем организациям, использующим автотранспорт в коммерческих целях. Являясь документом первичной отчетности его может запросить ФНС, а в случаях грузовых перевозок путевка не менее важна, чем транспортная накладная и зачастую они хранятся вместе.

Путевой лист, для чего он нужен?

Жесткие требования к наличию путевого листа предъявляются только к автотранспортным компаниям. При помощи путевого листа вы подтверждаете свои затраты на ГСМ, при помощи них может рассчитываться ЗП водителя и прочие затраты на автомобиль или технику. Т.е. без подтверждения путевых листов ФНС вам могут доначислить налог на прибыль (не приняв затраты на транспорт).

Водитель обязан иметь при себе путевой лист при выполнении коммерческой перевози и по требованию сотрудников полиции передавать его им для проверки. Хотя в КОАП нет прямого указания на путевой лист и нарушения по его оформлению или отсутствию, специалисты часто ссылаются на статью 12.31.1, как связанную с путевкой.

Приказ Минтранса России от 18 сентября 2008 г. № 152 обязует хранить отработанные путевые листы в течение 5 (пяти) лет.

В путевом листе так же отмечаются данные о медицинском осмотре водителя.

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

Какие бывают?

— Форма 3-спец. Для спец. техники

— Форма 4. Для такси

— Форма 4-с. Для грузовых автомобилей на сдельной оплате труда

— Форма 4-п. Для грузовых автомобилей на повременной оплате труда

— Форма 6. Для автобусов

Что должно быть в путевом листе?

В принципе можно скорректировать и создать удобную для организации форму путевого листа, но наличие обязательных полей унифицированной формы нарушать нельзя(не забудьте указать это в учетной политике). А именно — номер документа; ОГРИП и ОГРН владельца авто; дату выдачи; период действия ПЛ; данные о водителе; данные о самом ТС; данные о прохождении технического осмотра перед рейсом. Регламентируются п. 3 Приказа Минтранса от 18.09.2008 № 152.

С 18.01.2017 (Приказ Минтранса № 17) путевка не обязана содержать печать лица. Но проведение предрейсового осмотра проводить необходимо, для этого обязательно к заполнению:

Путевой лист в Excel и Word

Все тем же постановлением 78 Госкомстата РФ форма путевого листа стандартизирована. Выше по ссылкам доступны формы путевой лист в Excel в разных вариациях. Если нужен будет Word — пишите пожалуйста в комментарии.

Поделитесь нашей статьей в ваших соцсетях:

(Visited 73 times, 2 visits today)

Did you find apk for android? You can find new Free Android Games and apps.