Как вести общие расходы в таблице: рабочий шаблон
Таблицы достаточно, чтобы делить расходы, если знать, какие колонки в ней завести. Вот готовый шаблон, формула балансов и то место, где он всё-таки сдаёт.
6 мин чтения
Прежде чем искать приложение, стоит задать себе честный вопрос: а таблицы не хватит? Часто хватает. Выходные вчетвером, общий подарок, два месяца съёмной квартиры на двоих: общий файл справится, бесплатно и без регистрации кого бы то ни было.
Но собрать его надо правильно. Большинство самодельных таблиц для общих расходов проваливаются по одной и той же причине: в них перечислены траты, но нигде не сказано, на ком они лежат, и вывести из этого баланс уже невозможно. Вот шаблон, который работает, и точное место, где он сдаёт.
Четыре колонки, которых достаточно
Один лист, одна строка на расход и четыре обязательные колонки:
| Дата | Что | Сумма | Кто платил |
|---|---|---|---|
| 12/07 | Продукты в супермаркете | 84,30 | Лея |
| 12/07 | Бензин | 62,00 | Сэм |
| 13/07 | Аренда дома | 420,00 | Лея |
Это минимум, и многие таблицы на этом и останавливаются. Это ошибка: известно, что было оплачено, но неизвестно, кто сколько должен.
Не хватает колонки на каждого участника, которая говорит, касается его этот расход или нет. Достаточно простых 1 и 0, когда все делят поровну:
| Дата | Что | Сумма | Кто платил | Лея | Сэм | Ноэ | Кенза |
|---|---|---|---|---|---|---|---|
| 12/07 | Продукты | 84,30 | Лея | 1 | 1 | 1 | 1 |
| 12/07 | Бензин | 62,00 | Сэм | 1 | 1 | 1 | 0 |
| 13/07 | Дом | 420,00 | Лея | 1 | 1 | 1 | 1 |
Кенза приехала на поезде: бензин она не делит. Без этой колонки она бы всё равно за него заплатила, и никто бы этого не заметил до самого конца поездки.
Формула балансов
Баланс человека читается всегда одинаково: сколько он выложил вперёд, минус то, что приходится на него. Две формулы, по одной на каждую половину.
Сколько выложила Лея, если плательщики в колонке D, а суммы в колонке C:
=СУММЕСЛИ(D:D; "Лея"; C:C)
Чтобы посчитать, сколько приходится на неё, нужна ещё одна колонка, I, под названием Доля. Она делит сумму строки на число её участников и остаётся нулём в строке без участников:
=ЕСЛИ(СУММ(E2:H2)=0; 0; C2/СУММ(E2:H2))
Протяните её вниз. Затем, если колонка участия Леи это E:
=СУММПРОИЗВ(E2:E100; I2:I100)
Вторая формула учитывает долю строки, только если человек в неё входит. Именно это позволяет Кензе не платить за бензин, не ломая остальную таблицу. А колонка Доля делает пустые строки безвредными: деление на число участников внутри одной формулы даёт #ДЕЛ/0!, как только диапазон заходит ниже последнего расхода.
Баланс — это разница между двумя величинами: выложил - приходится на него.
Положительный баланс означает, что деньги должны вам. Отрицательный — что должны вы.
Сумма всех балансов всегда должна быть ровно ноль. Это единственная проверка, которая имеет значение: если она не сходится, где-то строка введена неверно, и идти дальше бессмысленно.
Момент, который почти все упускают: переводы
Знать балансы — ещё не значит знать, кто кому переводит. Наивный способ — прогнать всех через одного человека, но так количество переводов и комиссий только растёт.
Правильный подход укладывается в одно правило: самый крупный должник платит самому крупному кредитору, закрывается меньшая из двух сумм, и всё повторяется с остатком.
Возьмём четыре баланса: Лея +357,56, Сэм -84,74, Ноэ -146,74, Кенза -126,08.
- Ноэ (самый крупный должник) переводит Лее 146,74 €. Ноэ в нуле, Лея опускается до +210,82.
- Кенза переводит Лее 126,08 €. Кенза в нуле, Лея опускается до +84,74.
- Сэм переводит Лее 84,74 €. Все в нуле.
Три перевода на четверых. Это максимум, который даёт это правило: в худшем случае на один перевод меньше, чем участников. Таблица, которая предлагает больше, отнимает у всех время и деньги.
Три момента, когда таблица сдаёт
На расчётах она не сдаёт никогда. Она сдаёт на привычках, и всегда в одних и тех же местах.
Ввод на ходу. Это и есть настоящее ограничение, и оно решает всё. Открыть таблицу на телефоне, найти нужную строку, вбить сумму в нужную ячейку, когда одна рука занята, на выходе из супермаркета: этого не делает никто. Значит, записывают вечером, значит, забывают, значит, таблица становится неверной, а неверная таблица хуже, чем никакой, потому что ей доверяют.
Единственный владелец. Файл принадлежит тому, кто его создал. Он же напоминает, исправляет, пересчитывает и становится бухгалтером компании, хотя об этом не просил. Через шесть недель ему это надоедает, и таблица умирает.
Доли, которые не равны. Шаблон выше держится, пока делят поровну или не делят вовсе. Как только расход распределяется пропорционально доходам, площади комнат или числу проведённых ночей, колонок с 1 и 0 уже не хватает, и появляется второй лист с коэффициентами, в котором через месяц никто не разберётся.
Когда остаться в таблице, а когда из неё выйти
Оставайтесь, если в компании два-три человека, если срок короткий, если расходов немного и если таблицами все и так пользуются. В этом случае приложение не даст ничего сверх общей вкладки.
Выходите, как только одно из этих четырёх условий отваливается. На практике: съёмное жильё на долгий срок, поездка больше чем вчетвером или общие расходы, растянутые на месяцы.
Это ровно тот момент, когда эстафету принимает Kotisso. Расход записывается с телефона за десять секунд, каждый видит свой баланс, ни у кого не спрашивая, повторяющиеся ежемесячные траты вводятся один раз, а самый короткий план взаиморасчётов считается сам. Сумма балансов группы всегда равна нулю, и это можно проверить: расчёт выгружается в таблицу, с колонкой на каждого человека, ровно как выше.
Всё, что касается расчётов, бесплатно, и никакой банковский счёт подключать не нужно.
Частые вопросы
Какую таблицу выбрать? Ту, которой компания уже пользуется. У Google Таблиц есть плюс: файл открывается по ссылке и правится несколькими людьми одновременно, что частично снимает проблему единственного владельца. Excel и LibreOffice подойдут, если файл лежит в общем хранилище.
Как быть с расходом, оплаченным вдвоём? Заведите две строки, по одной на каждого плательщика, с суммой, которую каждый реально выложил. Одна строка с двумя именами в колонке плательщика ломает формулу.
А если кто-то возвращает деньги по ходу дела? Запишите его как обычную строку: платит тот, кто возвращает, с 1 под тем, кто получает, и 0 во всех остальных колонках. Баланс вернувшего растёт на эту сумму, баланс получателя уменьшается на столько же, а остальных это не касается. Именно так их считает шаблон для скачивания.
Округлять ли? Нет. Оставляйте копейки и позвольте формуле их нести: трое, которые делят 10 €, дают 3,34 и дважды по 3,33, и это единственное деление, которое сходится. Округление до 3,33 каждому теряет одну копейку, а таблица, которая не сходится к нулю, заставляет сомневаться во всём остальном. Шаблон для скачивания делает ровно это: каждая сумма делится с точностью до цента, а лишний цент достаётся первым участникам строки. Поэтому его балансы могут отличаться на цент от посчитанных выше вручную (+357,55 € у Леи), а их сумма всегда равна нулю.