Создание управленческой таблицы

Деятельность любой организации связана с движением денежных средств, и данные операционного учета являются одной из самых важных составляющей точности планирования деятельности. По сути не так важно каким образом осуществляется регистрация первичных данных - это может быть crm, erp система или просто табличка в Экселе.

НОВЫЙ ПРОЕКТ ОТ РАЗРАБОТЧИКОВ HELPEXCEL
Рассмотрим очень простую, но правильную структуру организации учета.
Предположим, что у нас несколько источников доходов - могут быть как отдельные виды деятельности, так и филиалы одной сети.

Инструментом ведения учета в рамках примера будут Гугл таблицы
Ввод данных
1. Создаем таблицу в которую будут записываться основные данные
создание гугл таблицы
Таблица выглядит как реестр операций. В нее будут записываться все операции прихода и расхода на каждом из объектов деятельности.
Каждую операцию будем относить к наличному или безналичному расчету, поэтому для удобства анализа все операции группируем по статьям бюджета. Необходимо выделить основные направления движения денежных средств организации.
2. Сделаем ввод данных максимально удобным и стандартизированным
(это очень важно!)

Дату можно вводить выбором из выпадающего календаря. Для этого выделяем столбец с датой, выбираем пункт меню «Данные» на панели управления и в выпадающем меню выбираем пункт «Проверка данных»
выпадающий календарь в гугл таблицах
В поле выбора правил проверки выбираем значение «Дата» из выпадающего списка
дата в правилах проверки данных
Нажимаем кнопку «Сохранить» . Теперь двойным мыши нажатием в столбце ввода даты появляется календарь:
как сделать календарь в столбце таблицы
  • Организуем выбор объекта из выпадающего списка. Для этого есть два варианта:
Вариант 1: Выделяем столбец «Объект», выбираем пункт меню «Данные», «Проверка данных».
В поле выбора правил выбираем «Значение из списка» и вписываем через запятую наименования объектов
как выбрать объект из выпадающего списка
Получаем выпадающий список с введенными значениями:
выпадающий список
Вариант 2: Список объектов хранится в таблице на любом из листов. В примере это будет лист «Справочник»
как в таблицах сделать список объектов в выпадающем списке
Выделяем столбец «Объект» на листе «Данные». Переходим в пункт меню «Данные», выбираем «Проверка данных» и в поле правила выбираем «Значение из диапазона» .
значение из диапазона
Далее указываем диапазон, содержащий нужные нам значения наименований объектов
как выбрать диапазон
Далее создаем аналогичным образом выпадающий список для столбца «Тип операции» и «Тип платежа». В рамках примера предполагаются операции прихода и расхода, а платежи наличные и безналичные.

По очереди выделяем столбцы, выбираем «Проверка данных» в пункте меню «Данные», выбираем правило «Значение из списка» и вводим нужные значения через запятую. Получаем выпадающие списки:
как сделать выпадающий список
выпадающий список для столбца
Статьи бюджета удобнее вывести в справочник. Там их будет удобнее редактировать и дополнять. Выделяем столбец «Статьи бюджета». Переходим в меню «Данные», «Проверка данных». Выбираем «Значения из диапазона» и указываем диапазон.
как выбрать значения из диапазона
Теперь осталось только привести в порядок таблицу, чтобы она имела презентабельный вид. Сперва нужно удалить проверку с заголовка таблицы. Выделяем всю строку заголовка, выбираем пункт меню «Данные», «Проверка данных» и нажимаем «Удалить проверку» в появившемся диалоговом окне.
удаляем проверку данных
Далее задаем внешний вид:
как правильно задать внешний вил таблице
Ввод данных и разграничение прав пользователей
Теперь в таблицу можно вводить данные. Гугл таблицы удобны тем, что ими может пользоваться одновременно почти неограниченное число пользователей, для которых администратор распределяет права доступа.
В правом верхнем углу располагается пункт меню «Настройки доступа».
Пользователь вводит логин аккаунта Gmail и определяет права
предоставить доступ
В расширенных настройках можно определить полномочия делегируемых прав
настройки доступа к таблице
Можно включить доступ по ссылке, который в свою очередь можно предоставить с правом редактирования и без редактирования
включить доступ по ссылке
просмотр у кого есть ссылка
Просмотр истории изменений и откат таблицы
В верхней части таблицы отображается информация о времени последнего изменения в таблице и аккаунте, который их произвел.
последнее изменение данных
В Гугл таблицах предусмотрена возможность просмотра истории изменений. Область просмотра истории активируется нажатием на строку с записью о статусе сохранения документа.
сохранение изменений на диске
Перед пользователем открывается таблица, в которой цветом выделены изменяемые области, а в окне справа отображены время изменения и пользователь, который внес редакцию.
пример управленческой таблицы
Анализ данных и отчетность
Рассмотрим распространенный вариант анализа данных операционной деятельности компании. Сформируем таблицу по основным статьям бюджета в разрезе каждого месяца по каждому объекту в отдельности:
анализ данных и отчетность
Верхняя таблица содержит суммарные результаты всех нижних таблиц, в которых должна отображаться деятельность каждого из объектов.

Для каждого объекта будем подтягивать данные из листа «Данные» формулой «СУММЕСЛИМН».
Функция СУММЕСЛИМН суммирует ячейки, удовлетворяющие заданному набору условий.
Вид функции: =СУММЕСЛИМН(Диапазон суммирования; Диапазон условия 1; Условие 1; Диапазон условия 2; Условие 2;…)

Поехали.

Сперва добавим на лист "Данные" столбец с названием «Месяц» и выведем формулой порядковый номер месяца из значения даты.
Встаем курсором в ячейку H2 и вводим простую формулу: =МЕСЯЦ(A2).
Протягиваем ее до конца таблицы:
суммеслимн
Далее приступаем к самому главному. Перемещаемся на лист отчета.

Встаем в ячейку D10 и вводим в строке формул «=СУММЕСЛИМН(…»

Выделяем на листе «Данные» столбец F с суммами и ставим точку с запятой «;» и фиксируем перемещение по столбцам знаком $

=СУММЕСЛИМН( ' Данные ' ! $F : $F;

Далее выделяем столбец с критериями для суммирования Е – статьи бюджета и ставим точку с запятой «;» и фиксируем перемещение по столбцам знаком $

=СУММЕСЛИМН( ' Данные ' ! $F : $F; 'Данные ' ! $E : $E;

Выделяем условие отбора критериев – ячейка С10 и ставим точку с запятой «;» и фиксируем перемещение по столбцам знаком $

=СУММЕСЛИМН( ' Данные ' ! $F : $F; 'Данные ' ! $E : $E;$C10;

Далее указываем второй столбец с критериями – столбец В с наименованиями объектов и ставим точку с запятой «;» и фиксируем перемещение по столбцам знаком $

=СУММЕСЛИМН( ' Данные ' ! $F : $F; ' Данные ' ! $E : $E;$C10; ' Данные ' ! $B : $B;

И указываем наименование объекта в ячейке В10 в качестве критерия и фиксируем перемещение по столбцам и строкам знаком $

=СУММЕСЛИМН( ' Данные ' ! $F : $F ; ' Данные ' ! $E : $E;$C10; ' Данные ' ! $B : $B; $B$10;

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

Получаем формулу:
=СУММЕСЛИМН( ' Данные ' ! $F : $F ; ' Данные ' ! $E : $E;$C10; ' Данные ' ! $B : $B;$B$10;
' Данные ' ! $H : $H;D$2)

Протягиваем формулу на все статьи бюджета и каждый месяц в таблице объекта 1
получение формулы суммеслимн
=СРЗНАЧ(D10:O10)
Аналогичным образом считаем итоги, используя функцию «СУММ». Аргументом формулы является набор или массив данных.

Получаем заполненную расчетами таблицу:
анализ данных
Данные в таблице имеют разный формат. Приведем все к одному виду, оставив в таблице только целые числа. Выделяем всю область значений таблицы, переходим в пункт меню «Другие форматы» и выбираем «Число»
оставляем в таблице только целые числа
Нажимаем два раза на кнопку «Уменьшить число знаков после запятой»
Получаем отчет по объекту 1:
Производим аналогичные вычисления для других объектов. Получаем отчет следующего вида:
Считаем сумму, складывая данные бюджета каждого объекта:
Получаем отчет по всем объектам деятельности в разрезе статей бюджета на каждый месяц года
Подводя итоги
В данном кейсе рассмотрена укрупненная схема организации управленческого учета компании. Информация на листе «Данные» может представлять собой данные в системе учета или данные которые формируются вручную из разных источников. Лист отчет представляет собой отчет о прибылях и убытках. Отчет можно отнести к данным для первичного анализа. По разбросу его значений можно выявить положительные или отрицательные факторы и при необходимости искать причины в других источниках информации.

В отчет можно добавить сравнение с аналогичными данными прошедших периодов, сравнение с плановыми значениями, а так же расчеты таких показателей как рентабельность, валовая прибыль и т.д..
Made on
Tilda