07.12.2014

Ведем домашнюю бухгалтерию с помощью Google Таблиц и Форм


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

Справедливости ради замечу, что такой учет домашних финансов я делал для себя и он меня абсолютно устраивает. Здесь нет навороченных мультивалютных счетов, транзакций, кучи кошельков и дебиторско-кредиторской задолженности. И все это я могу объяснить одним - я не банк и не какое-нибудь иное финансовое учреждение, а значит меня интересует только одно: сколько и на что я потратил и сколько при этом заработал.
Тем не менее, и в этом и заключается прелесть данного решения, вы можете легко и быстро добавить любой анализ расходов/доходов при помощи сотен формул и функций доступных каждому в Google Таблицах.

Предисловие

Немножко текста в тему того как работает мое решение:
  1. Решение состоит из двух равнозначно важных частей: Google Формы и Google Таблицы
  2. Данные, которые вы заносите "на ходу" через Google Форму, попадают прямиком в таблицу, где и происходит магия
  3. Статьи расходов и доходов автоматически обновляются в форме, на основании данных Google Таблицы
  4. В таблице прописаны формулы для последующего анализа данных, полученных из Google Формы
Впринципе - это основные важные элементы моего решения. Можем приступать к настройке.

Копируем таблицу

Для начала - скопируем эту таблицу.


Как видно - таблица состоит из четырех листов:
1. Ответы на форму - сюда попадает информация из Google Формы


2. Расчеты - общий анализ бюджета по каждому месяцу + графики (мне одного достаточно)


3. Долги - самый неприятный, но от того не менее необходимый лист с учетом личной задолженности


4. Типы - здесь находятся все типы расходов, счетов и прочее. Так же здесь я учитываю расход/приход по каждой категории


Настраиваем форму

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


Для начала - откроем форму для редактирования. Это можно сделать несколькими способами, но самый простой, не покидая таблицы, выбрать из меню Форма -- Изменить форму.


Для чего мы меняем форму, спросите вы, а я не сразу найду что вам ответить. Дело в том, что, вопреки логике, когда вы копируете таблицу с привязанной к ней формой (то-есть с формой, которая уже заносила данные в эту таблицу) форма копируется, но перестает заносить данные в таблицу (фейс палм). По-этому мы вынуждены создавать новый лист в существующей таблице с привязкой к этой форме. Блин... это даже писать тяжело...
Тем не менее - зададим форме нашу таблицу и создадим в ней новый лист для сбора данных из формы.
Для этого откроем пункт меню Ответы -- Сохранять ответы


Затем выберем пункт Новый лист в существующей таблице и нажмем


В открывшемся окне находим таблицу, которую вы скопировали ранее и выбираем ее.


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


Так же нам необходимо создать колонку G с названием Дата, в которой находится формула, преобразующая отметку времени из колонки А в формат даты, с которой впоследствии будут работать формулы анализа данных. Формула, которую мы будем использовать в ячейке G2:
=ARRAYFORMULA(TO_DATE(A2:A))
Впринципе колонка G дублирует колонку A и приобразовать дату можно (и наверное нужно) скриптом, но иногда мне проще скрыть столбец, если он мешает, и решить задачу формулой. Что я, собственно, и сделал.
После того, как вы создали колонку Дата и скопировали формулу из ячейки G2"старого" листа Ответы на форму - его (старый лист, который уже был в таблице не момент ее копирования) можно смело удалять.

Настраиваем таблицу

Мы настроили форму и теперь необходимо проделать несколько нехитрых манипуляций с таблицей, для того, что бы заранее подготовленные формулы отрабатывали корректно.
Воспользуемся мощным и недооцененным инструментом таблиц - Именованные диапазоны. По сути функция ничем не отличается от таковой в Excel. Нам необходимо задать именованный диапазон для всех данных, которые мы получаем из формы, для того, что бы быстро и просто ссылаться на него из формул анализа.
Создадим диапазон Форма из массива ячеек A2:G на листе Ответы на форму. Для этого открываем пункт меню Данные -- Именованные диапазоны.


Создадим новый диапазон. Для этого в боковой панели нажимаем + Добавить диапазон, задаем имя Форма (ВАЖНО, что бы вы задали именно это имя, поскольку на именованный диапазон именно с таким именем ссылаются все формулы, прописанные в таблице) и диапазон A2:G. Поскольку именованный диапазон присваивается только массиву с четкими размерами - ссылка на ячейки автоматически подстроится под количество строк в таблице. В нашем случае ссылка на диапазон будет иметь вид:
'Ответы на форму (4)'!A2:G1007


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

Настраиваем обновление списков в форме из таблицы

В таблице установлен скрипт (google apps script), который берет данные из листа Типы (колонки Счет и Операции) и формирует из них два списка. Каждая строка со значениями = отдельный пункт списка. Зачем это нужно? Представьте, что вы часто меняете статьи расходов/доходов, или частенько появляются новые/пропадают старые. Представьте, что каждый раз вам нужно заносить их вручную в редакторе форм, заносить в таблицу и в формулы для анализа расходов по каждой статье. Представили? Теперь становится ясно, что если этот процесс автоматизировать - настройка домашней бухгалтерии будет одноразовой и относительно простой. Почему относительно - сейчас покажу.

Итак - открываем редактор скриптов. Для этого в таблице (лист не имеет значения) открываем пункт меню Инструменты -- Редактор скриптов.


Откроется редактор скриптов - инструмент автоматизации задач в экосистеме продуктов Google. Как видите - уже создан и написан скрипт, который, собственно, и подтягивает значения из таблицы в списки формы. Поскольку он жестко привязывается как к таблице, так и к форме - нам необходимо поменять ссылку на саму форму, поскольку после копирования - форме был присвоен новый ключ. Если вкратце - все документы, таблицы, формы и прочее в Google Диске имеют свой уникальный идентификатор, именуемый "ключом". Вот его нам и надо изменить.
Что бы найти ключ - откройте форму для просмотра. В самом адресе ссылки на форму вы и найдете ключ, например:
https://docs.google.com/forms/d/ЗДЕСЬ-ДЛИННЫЙ-КОД-ЭТО-КЛЮЧ-ФОРМЫ/viewform
Когда ключ найден - возвращаемся в редактор скриптов и меняем в строке кода №48 (номера строк вы без труда найдете слева от самого кода) и меняем длинный код (выделен красным шрифтом в редакторе):


В итоге вы должны поменять всего одну строку:
var form = FormApp.openById('ЗДЕСЬ-ВАШ-КЛЮЧ-ФОРМЫ'); 

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

Хорошее начало

Я создал всего-лишь базис для развития идеи ведения семейного бюджета с использованием сервисов Google. Возможно мое решение кому то покажется излишне сложным на этапе настройки, кому то не хватит функционала, но в итоге - я дал основу, которую можно расширять и настраивать под себя, что, согласитесь, не плохо.
Буду рад услышать пожелания по усовершенствованию, конструктивную критику и вообще отзывы. Надеюсь вы найдете этот пост полезным.