Книга Excel для учета расходов на авто. Часть 2. Лист "Топливо"

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

Сегодня давайте поработаем над структурой листа с топливом. Назовем его буквой "Т" вместо бездушного "Лист1". Кстати, настоятельно советую длинные имена листам в файле Excel не давать. Это существенно осложнит процесс написания и восприятия формул, которые ссылаются на ячейки с разных листов.

Рис. 1

Итак, пусть у нас существует некая начальная точка отсчета, которую мы обозначили датой 17.12.2022. На этот день (пусть он предшествует первому дню начала учета расходов на автомобиль в нашей Книге) мы вносим начальный пробег - 50000 км. А в столбце "Пробег за день, км" также "руками" вносим значение 0.

Первый день реальной эксплуатации нашего автомобиля - 18.12.2022. В этот день пробег на одометре составил 50030 км. Это значение занесено во 2-й столбец. Естественно, что в столбце С мы теперь получаем возможность рассчитать пробег за этот день:

Рис. 2

Естественно, что в столбце F также необходимо рассчитать объем залитого топлива.

Рис. 3

А вот дальше мы начинаем расходовать бензин, "накатываем" определенное количество километров. Но, ведь заправляемся мы не каждый день! А потому, если формулу, содержащуюся в столбце F мы "растянем" на весь столбец, то получим вот такую ужасную картину:

Рис. 4

Ведь очевидно, что если 19.12.2022-21.12.2022 мы не заправлялись, то в этом случае при определении объема заправленного топлива и будет осуществляться деление на 0, что мы и видим на рис. 4. Честно скажу, что я такие ошибки терпеть не могу. Вот это сразу "2" моим студентам, если я такое увижу. А значит формулу нужно исправить. Например, вот так:

Рис. 5

Смысл формулы из ячейки G4 такой. Если значение в ячейке Е4 отсутствует (равно пустоте - символ "" - означает пустоту в Excel), то и в ячейке F4 должно быть выведено значение "". Если же Е4 не пустая, то в F4 выводится отношение D4/E4. Таким образом, если следующая заправка у нас происходит 22.12.2022, то только в этой строке в столбцах D, E, F появляются соответствующие значения.

И вот теперь мы можем рассчитать среднее значение расхода топлива на пройденный пробег. Очевидно, что для этого необходимо объем топлива, залитый во время последней заправки, которым мы как бы восстанавливали истраченные ГСМ, разделить за пройденную за это время дистанцию. Соответственно, первая задача, которая встает перед нами, это определить пробег между соседними заправками. При этом мы должны договориться об одной условности - давайте условно примем, что заправляемся в начале каждого дня заправки. То есть пробег дня заправки мы будем проезжать уже на сегодняшнем же заправленном топливе. Можно принять и противоположное решение, то есть считать, что мы заправляемся в конце дня. Это повлияет на формулы, но общего подхода к решению не изменит. Но просто я всегда заправляюсь утром, поэтому в моей схеме более правильно будет именно описанное выше допущение.

Итак, в ячейку С3 мы просто вставляем ссылку на пробег за первый день эксплуатации авто. Эта первая строка во многих местах будет отличаться от остальных. Соответственно, нам возвращается значение пробега за 1-й день - 30 км (рис. 6)

Рис. 6

В следующей строке в ячейке G7 будет уже гораздо более сложная формула:

Рис. 7

Давайте разбираться, в чём смысл этой формулы. Если в ячейке F4 пустота, то есть заправки в этот день не было, то значение ячейки предыдущей строки G3 (там у нас значение 30 км) суммируется со значением пробега за текущий день - С4. То есть 30+60 = 90. Если бы значение в ячейке F4 оказалось не пустым, то есть заправка в этот день была, то пробег который копился в предыдущих строках должен обнулиться, а накопление пробега должно со следующей заправки пойти заново. Это и происходит в строке 7 (рис. 8)

Рис. 8

Как мы видим, ячейка F7 содержит количество приобретенного топлива в этот день, а соответственно, в ячейку G7 будет выведено значение из ячейки С7, а накопление пробега начнется заново (рис. 9)

Рис. 9

Мы видим, что в ячейках G7 и G11 накопление пробега начинается заново. Ну а дальше всё уже совсем просто: зная накопленный к моменту заправки пробег (это будет значение из предыдущей ячейки сверху), а также количество вновь залитого топлива (значение из ячейки слева) и условно считая, что каждый раз мы восстанавливаем запасы топлива примерно до 1 уровня (например, "под горлышко"), мы легко можем посчитать расход топлива за пройденное расстояние между соседними заправками (рис. 10).

Рис. 10

Однако простая формула =F7/G6 в ячейке H7 выдаст необходимое значение для строки 7, но опять-таки значение с ошибками для предыдущих строк, где заправок не было. То есть "пустота" из ячейки F6 будет делиться на некое значение пробега из ячейки G5. Естественно, что осуществить соответствующий расчет Excel не может, а значит ему опять нужно подсказать, чтобы он "не мучился" (рис. 11)

Рис. 11

Теперь, если значение заправленного топлива пустое, то и расчет расхода топлива в соответствующей ячейке не производится. Но... Понятно, что в реальной жизни мы не всегда заправляемся, что называется, до одного уровня. Бывает, что до полного бака мы не дольем литра 3-4. Естественно, что на коротких пробегах между заправками это будет очень сильно влиять на результат. Вот в следующей публикации мы и поищем выход из этой ситуации.

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