Топ-3 неочевидных формулы, которые нужно знать, если вы ведёте управленческий учёт на google-таблицах
Для построения системы управленческого учёта не нужно знать много формул. Их порядка десяти, и большинство из них очень простые. Ошибки в отчётах появляются потому что: формула протянута не до конца, операция внесена в один отчёт и забыта в другом, статья написана с лишним пробелом и не попала в сумм, появилась новая статья в справочнике, в отчет ее забыли вынести, забыли поставить скобку и т.д.
Существует методика сборки через базу данных, которая практически исключает человеческий фактор. Все операции лежат в одной Базе Данных, один столбец, один признак, а отчёты считаются из неё либо сводными либо формулами. Ниже три формулы, которые в этой методике делают основную работу: упрощают сбор данных и убирают ручные действия, в которых человек ошибается.
1️⃣ QUERY: собирает базу из разных источников одной формулой
Источников у базы данных может несколько: выписка по счёту, касса, карта, данные по начислениям от подразделений, выгрузка из 1С. Каждый источник живёт на своём листе в одинаковой структуре. QUERY склеивает их в одну базу и берёт только строки, где заполнена статья:
Что это даёт: новый источник добавляется одной строкой в формулу, а строка без статьи в отчёты не попадёт, пока её не разнесли. Ничего не копируется руками из листа в лист, значит ничего не теряется по дороге.
2️⃣ ARRAYFORMULA: формула сама растёт вместе с базой
Обычную формулу нужно протягивать вниз на каждую новую строку. Здесь могут быть простые ошибки: протянули не до конца, протянули не в тот столбец, при вставке строк формула сдвинулась. ARRAYFORMULA позволяет таких ошибок избежать, она пишется один раз в первой ячейке столбца и работает на весь столбец, сколько бы строк ни добавили:
Так в базе считаются месяц и год операции, сумма без НДС и другие служебные столбцы. Человек заполняет только дату, сумму, описание и статью, остальное таблица дописывает сама, и в базе не бывает строки с пустым месяцем.
3️⃣ SUMIFS: строит отчёт из базы по нескольким условиям
Каждая ячейка ОДДС, ОПиУ и баланса считается одной формулой из базы. ОПиУ, например, так:
Для построения системы управленческого учёта не нужно знать много формул. Их порядка десяти, и большинство из них очень простые. Ошибки в отчётах появляются потому что: формула протянута не до конца, операция внесена в один отчёт и забыта в другом, статья написана с лишним пробелом и не попала в сумм, появилась новая статья в справочнике, в отчет ее забыли вынести, забыли поставить скобку и т.д.
Существует методика сборки через базу данных, которая практически исключает человеческий фактор. Все операции лежат в одной Базе Данных, один столбец, один признак, а отчёты считаются из неё либо сводными либо формулами. Ниже три формулы, которые в этой методике делают основную работу: упрощают сбор данных и убирают ручные действия, в которых человек ошибается.
1️⃣ QUERY: собирает базу из разных источников одной формулой
Источников у базы данных может несколько: выписка по счёту, касса, карта, данные по начислениям от подразделений, выгрузка из 1С. Каждый источник живёт на своём листе в одинаковой структуре. QUERY склеивает их в одну базу и берёт только строки, где заполнена статья:
=QUERY({
FILTER('Выписка'!A2:AC, 'Выписка'!K2:K"");
FILTER('Касса'!A3:AC, 'Касса'!K3:K"");
FILTER('Начисления'!A3:AC, 'Начисления'!K3:K"")
}, "select * where Col11 is not null", 1)
Что это даёт: новый источник добавляется одной строкой в формулу, а строка без статьи в отчёты не попадёт, пока её не разнесли. Ничего не копируется руками из листа в лист, значит ничего не теряется по дороге.
2️⃣ ARRAYFORMULA: формула сама растёт вместе с базой
Обычную формулу нужно протягивать вниз на каждую новую строку. Здесь могут быть простые ошибки: протянули не до конца, протянули не в тот столбец, при вставке строк формула сдвинулась. ARRAYFORMULA позволяет таких ошибок избежать, она пишется один раз в первой ячейке столбца и работает на весь столбец, сколько бы строк ни добавили:
=ARRAYFORMULA(IF(ISBLANK(C3:C), "", MONTH(C3:C)))
Так в базе считаются месяц и год операции, сумма без НДС и другие служебные столбцы. Человек заполняет только дату, сумму, описание и статью, остальное таблица дописывает сама, и в базе не бывает строки с пустым месяцем.
3️⃣ SUMIFS: строит отчёт из базы по нескольким условиям
Каждая ячейка ОДДС, ОПиУ и баланса считается одной формулой из базы. ОПиУ, например, так:
=SUMIFS(Сумма_без_НДС,
Статья_ОПиУ, $B3,
Дата_начисления, ">="&C$1,
Дата_начисления, "