ПРОЕКТ ОТ РАЗРАБОТЧИКОВ HELPEXCEL

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

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

Создание управленческой таблицы
ПРОЕКТ ОТ РАЗРАБОТЧИКОВ HELPEXCEL

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

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

Инструментом ведения учета в рамках примера будут Гугл Таблицы.

Ввод данных

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:

Производим аналогичные вычисления для других объектов. Получаем отчет следующего вида:

Считаем сумму, складывая данные бюджета каждого объекта:

Получаем отчет по всем объектам деятельности в разрезе статей бюджета на каждый месяц года:

Подводя итоги

В данном кейсе рассмотрена укрупненная схема организации управленческого учета компании. Информация на листе «Данные» может представлять собой данные в системе учета или данные которые формируются вручную из разных источников. Лист отчет представляет собой отчет о прибылях и убытках. Отчет можно отнести к данным для первичного анализа. По разбросу его значений можно выявить положительные или отрицательные факторы и при необходимости искать причины в других источниках информации.

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

Хотите самостоятельно разбираться в Гугл таблицах?
Быстро. Грамотно. Полезно.
Пройдите новый усовершенствованный курс по Гугл таблицам от наших разработчиков
Вам будет интересно: