Как составит отчет таблицу образец

Для анализа больших и сложных таблиц обычно используют

Сводные таблицы

. С помощью формул также можно осуществить группировку и анализ имеющихся данных. Создадим несложные отчеты с помощью формул.

В качестве исходной будем использовать

таблицу в формате EXCEL 2007

(

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

Сводные таблицы

.

В таблице имеются столбцы:

• 
  Товар
  
  – наименование партии товара, например, «
  
  Апельсины
  
  »;
• 
  Группа
  
  – группа товара, например, «
  
  Апельсины
  
  » входят в группу «
  
  Фрукты
  
  »;
• 
  Дата поставки
  
  – Дата поставки Товара Поставщиком;
• 
  Регион продажи
  
  – Регион, в котором была реализована партия Товара;
• 
  Продажи
  
  – Стоимость, по которой удалось реализовать партию Товара;
• 
  Сбыт
  
  – срок фактической реализации Товара в Регионе (в днях);
• 
  Прибыль
  
  – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через

Диспетчер имен

откорректируем

имя

таблицы на «

Исходная_таблица

» (см.

файл примера

).

С помощью формул создадим 5 несложных отчетов, которые разместим на отдельных листах.

Отчет №1 Суммарные продажи Товаров

Найдем суммарные продажи каждого Товара. Задача решается достаточно просто с помощью функции

СУММЕСЛИ()

, однако само построение отчета требует определенных навыков работы с некоторыми средствами EXCEL.

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

уникальные

значения. Это можно сделать несколькими способами: формулами (см. статью

Отбор уникальных значений

), через меню

или с помощью

Расширенного фильтра

. Если воспользоваться первым способом, то при добавлении новых Товаров в исходную таблицу, новые названия будут включаться в список автоматически. Но, здесь для простоты воспользуемся вторым способом. Для этого:

• Перейдите на лист с исходной таблицей;
• Вызовите

  Расширенный фильтр

  (

  );

• Заполните поля как показано на рисунке ниже: переключатель установите в позицию
  
  Скопировать результат в другое место
  
  ; в поле Исходный диапазон введите $A$4:$A$530; Поставьте флажок
  
  Только уникальные записи
  
  .

• Скопируйте полученный список на лист, в котором будет размещен отчет;
• Отсортируйте перечень товаров (

  ).

Должен получиться следующий список.

В ячейке

B6

введем нижеследующую формулу, затем скопируем ее

Маркером заполнения

вниз до конца списка:

=СУММЕСЛИ(Исходная_Таблица[Товар];A6;Исходная_Таблица[Продажи])

Для того, чтобы понять сруктурированные ссылки на поля в

таблицах в формате EXCEL 2007

можно почитать Справку EXCEL (клавиша

F1

) в разделе

Основные сведения о листах и таблицах Excel > Использование таблиц Excel

.

Также можно легко подсчитать количество партий каждого Товара:

=СЧЁТЕСЛИ(Исходная_Таблица[Товар];A6)

Отчет №2 Продажи Товаров по Регионам

Найдем суммарные продажи каждого Товара в Регионах. Воспользуемся перечнем Товаров, созданного для Отчета №1. Аналогичным образом получим перечень названий Регионов (в поле Исходный диапазон

Расширенного фильтра

введите $D$4:$D$530). Скопируйте полученный вертикальный диапазон в

Буфер обмена

и

транспонируйте

его в горизонтальный. Полученный диапазон, содержащий названия Регионов, разместите в заголовке отчета.

В ячейке

B

8

введем нижеследующую формулу:

=СУММЕСЛИМН(Исходная_Таблица[Продажи]; Исходная_Таблица[Товар];$A8; Исходная_Таблица[Регион продажи];B$7)

Формула вернет суммарные продажи Товара, название которого размещено в ячейке

А8

, в Регионе из ячейки

В7

. Обратите внимание на использование

смешанной адресации

(ссылки $A8 и B$7), она понадобится при копировании формулы для остальных незаполненных ячеек таблицы.

Скопировать вышеуказанную формулу в ячейки справа с помощью

Маркера заполнения

не получится (это было сделано для Отчета №1), т.к. в этом случае в ячейке

С8

формула будет выглядеть так:

=СУММЕСЛИМН(Исходная_Таблица[Сбыт, дней]; Исходная_Таблица[Группа];$A8; Исходная_Таблица[Продажи];C$7)

Ссылки, согласно правил

относительной адресации

, теперь стали указывать на другие столбцы исходной таблицы (на те, что правее), что, естественно, не правильно. Обойти это можно, скопировав формулу из ячейки

B8

, в

Буфер обмена

, затем вставить ее в диапазон

С8:

G

8

, нажав

CTRL

+

V

. В ячейки ниже формулу можно скопировать

Маркером заполнения

.

Отчет №3 Фильтрация Товаров по прибыльности

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

Создадим

Выпадающий (раскрывающийся) список

на основе

Проверки данных

со следующими значениями:

(Все); Да; Нет

. Если будет выбрано значение фильтра

(Все)

, то при расчете продаж будут учтены все записи исходной таблицы. Если будет выбрано значение фильтра «

Да»

, то будут учтены только прибыльные партии Товаров, если будет выбрано «

Нет»

, то только убыточные.

Суммарные продажи подсчитаем следующей

формулой массива

:

=СУММПРОИЗВ((Исходная_Таблица[Группа]=A8)* ЕСЛИ($B$5=”(Все)”;1;(Исходная_Таблица[Прибыль]=$B$5))* Исходная_Таблица[Продажи])

После ввода формулы не забудьте вместо простого нажатия клавиши

ENTER

нажать

CTRL

+

SHIFT

+

ENTER

.

Количество партий по каждой группе Товара, в зависимости от прибыльности, можно подсчитать аналогичной формулой.

=СУММПРОИЗВ((Исходная_Таблица[Группа]=A8)* ЕСЛИ($B$5=”(Все)”;1;(Исходная_Таблица[Прибыль]=$B$5)))

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

Выбрав в фильтре значение

Нет

(в ячейке

B

5

), сразу же получим отчет о продажах по Группам Товаров, принесших убытки.

Отчет №4 Статистика сроков сбыта Товаров

Вернемся к исходной таблице. Каждая партия Товара сбывалась определенное количество дней (см. столбец Сбыт в исходной таблице). Необходимо подготовить отчет о количестве партий, которые удалось сбыть за за период от 1 до 10 дней, 11-20 дней; 21-30 и т.д.

Вышеуказанные диапазоны сформируем нехитрыми формулами в столбце

B

.

Количество партий, сбытые за определенный период времени, будем подсчитывать с помощью формулы

ЧАСТОТА()

, которую нужно ввести как

формулу массива

:

=ЧАСТОТА(Исходная_Таблица[Сбыт, дней];A7:A12)

Для ввода формулы выделите диапазон

С6:С12

, затем в

Строке формул

введите вышеуказанную формулу и нажмите

CTRL

+

SHIFT

+

ENTER

.

Этот же результат можно получить с помощью обычной функции

СУММПРОИЗВ()

:

=СУММПРОИЗВ((Исходная_Таблица[Сбыт, дней]>A6)* (Исходная_Таблица[Сбыт, дней]<=A7))

Отчет №5 Статистика поставок Товаров

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

=МИН(Исходная_Таблица[Дата поставки])

Создадим перечень дат –

первых дней месяцев

, начиная с самой ранней даты поставки. Для этого воспользуемся формулой:

=КОНМЕСЯЦА($C$5;-1)+1

В результате получим перечень дат – первых дней месяцев:

Применив соответствующий формат ячеек, изменим отображение дат:

Формула для подсчета количества поставленных партий Товаров за месяц:

=СУММПРОИЗВ((Исходная_Таблица[Дата поставки]>=B9)* (Исходная_Таблица[Дата поставки]

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

ГОД()

.

Теперь для вывода

промежуточных итогов

по годам создадим структуру через пункт меню

:

• Выделите любую ячейку модифицированной таблицы;
• Вызовите окно

  Промежуточные итоги

  через пункт меню

  ;

• Заполните поля как показано на рисунке:

После нажатия ОК, таблица будет изменена следующим образом:

Будут созданы промежуточные итоги по годам. Нажатием маленьких кнопочек в левом верхнем углу листа можно управлять отображением данных в таблице.

Резюме

:

Отчеты, аналогичные созданным, можно сделать, естественно, с помощью

Сводных таблиц

или с применением

Фильтра

к исходной таблице или с помощью других функций

БДСУММ()

,

БИЗВЛЕЧЬ()

,

БСЧЁТ()

и др. Выбор подхода зависит конкретной ситуации.

ФОРМА ОТЧЕТА ДЛЯ КОНТРОЛЯ РАСХОДОВ В EXCEL

Создание бюджета не будет казаться сложным процессом, если использовать в работе шаблон. Главное — выбрать из огромного количества шаблонов тот, который подходит для ваших целей: создать бюджет для инвестиционного проекта, отслеживать текущие расходы предприятия, планировать предстоящий финансовый год и др. В данной статье представим наиболее информативные и доступные форматы отчетов для контроля расходов в Excel. Сделаем это на примере компании ООО «Василек» (название условное).

Ежедневно руководителю компании необходимо принимать решение, как распределить денежные средства, расставлять приоритеты по оплатам. Помочь ему в этом может Бюджет движения денежных средств (БДДС) — документ, в котором собраны все поступившие заявки на оплату и сведения об имеющихся в компании свободных денежных средствах.

Этот документ формируется в Excel. В качестве информационного материала берут данные бухгалтерского учета об остатках денежных средств на счетах на начало периода, для которого планируют составить БДДС, остатки по кассе (если имеется кассовый учет), все незакрытые обязательства как по данным бухучета (кредиторская задолженность на начало периода), так и в соответствии с заключенными договорами на оплату.

При подготовке БДДС нужно помнить, что все выплаты должны соответствовать утвержденному плановому бюджету доходов и расходов компании. Как только появляется внеплановый платеж, должно появиться сообщение, что это планом не предусмотрено.

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

В таблице 1 представлен Бюджет движения денежных средств ООО «Василек»» на ноябрь 2019 г.

 
Как видно из табл. 1, чистый поток по месяцу прогнозируется отрицательным (–2270 руб.), однако за счет начальных остатков в 6500 руб. компания в состоянии выполнить заявленный бюджет на данный месяц. При этом она наращивает дебиторскую задолженность от своих покупателей с 18 500 до 29 000 руб. и уменьшает кредиторскую задолженность перед поставщиками товара с 45 000 до 30 000 руб. В целом по месяцу картина оптимистичная. 

Конечно, есть обязательные платежи, которые никак нельзя отложить на вторую половину месяца (оплата аренды, коммунальные платежи, выплата заработной платы). Следовательно, нужен понедельный или подневной план оплат, которого компания должна четко придерживаться. Рассмотрим подневное планирование оплат ООО «Василек» на ноябрь 2019 г. (табл. 2).

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

В отчете для расчета величины расходов можно сразу увидеть доходы и расходы, причем возможна группировка по дню (табл. 3).

 
Из таблицы 2 видно, сколько нужно потратить в периоде по определенной статье затрат, из табл. 3 — какую сумму необходимо иметь на счетах на конкретную дату. 

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

Для учета расходов можно пользоваться формой, представленной в табл. 4.

 

Чтобы сформировать данную таблицу, нужно переработать огромную массу данных.

АЛГОРИТМ ПОСТРОЕНИЯ ОТЧЕТА

Представим алгоритм формирования отчета для учета и контроля расходов (см. табл. 4).

Прежде всего нужно подготовить справочники по кодам. С их помощью все расходы кодируются, а затем группируются по необходимым статьям. Расходы объединяются в единую базу данных и кодируются по следующей системе (табл. 5).

Шифры учета дают возможность собрать все данные с помощью формул в более крупные статьи. А мелкое дробление — это табл. 6, на основании которой рассчитывают консолидированный отчет по расходам, используя шифры и формулы. Данную таблицу получают в результате кропотливого труда сотрудников по шифровке ежедневных расходов. 

На основании утвержденного БДР вносят плановые значения, а по результатам отчетного периода включают данные по фактическому исполнению.

1.                  Все данные по затратам распределяют по утвержденным ЦФО. Для этого отработка осуществляется ежедневно, причем справочник кодировки затрат (см. табл. 5) — это уже более высокий уровень учета. Первоначально все расходы учитывают с подробной детализацией, как в табл. 6.

Продолжение табл. 6

На основании таких заполненных таблиц в рамках месяца контролируют исполнение бюджета расходов. Затем все детальные расходы при помощи формул консолидируют на уровень табл. 5. Причем для кодировки можно использовать не только ручной режим, но и функционал формул:

ЕСЛИ(ЕСЛИ(B57=”усл.банка”;”23р”;ЕСЛИ(B57=”ПН”;”22р”;ЕСЛИ(B57=”ЕСН”;”22р”;

ЕСЛИ(B57=”налог на дивиденды”;”23р”;ЕСЛИ(B57=”ЗП 10***”;”11р”;ЕСЛИ(B57=”ЗП 25***”;”11р”;ЕСЛИ(B57=”Субподряд”;”21р”;0)))))))).

Отметим, что лучше всего пользоваться формулами. Если использовать формулы с необходимыми и достаточными условиями, формулы по работе с массивами данных, формулы типа: =СЦЕПИТЬ(“Расшифровка расходов подразделений “;D1;” за “;Сценарий!B2), можно настроить учет и контроль так, чтобы при выполнении условия, например, расходы больше 10 000 руб. в день, срабатывала контрольная фраза «Остановить расходы».

Важный момент: с помощью настроенных формул сегодня можно заполнить все отчеты.

2. Консолидация месячных отчетов по расходам в один общий сводный отчет за год.

3. Отчет по контролю расходов — это целая книга, созданная на базе Excel. Для учета расходов первоначально формируется БДДС. Используются данные по заключенным договорам (обязательные платежи), далее — база бухгалтерского учета с кодировкой затрат. В конечном итоге получаем сводную таблицу по расходам и остаткам компании (см. табл. 4).

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

На примере отчёта о движении денежных средств (ДДС) мы покажем, как организовать отчётность в «Google Таблицах» так, чтобы настройка не занимала много времени.

Для кого инструкция

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

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

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

Начнём с основ бизнес-анализа и работы с «Google Таблицами»

Разбираться, как это сделать, будем на примере реального кейса. Рассмотрим некоторые основы формул в «Google Таблицах», организационную составляющую по работе с данными и бизнес-анализа.

Чтобы вникнуть и разобраться, потребуется примерно час.

Чтобы отчётность занимала минимум времени, нужно организовать её как модель. Модель — это статичная форма, которая меняется при изменении параметров или исходных данных. Забегая вперед, сразу покажем, как выглядит конечная цель — создание модели отчёта.

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

В качестве инструмента будем использовать «Google Таблицы». Итоговое решение будет представлять собой модель, которая зависит от исходных данных. Приводятся формулы, расчёты и ссылка на готовую таблицу.

Вводные данные

Каждая из нижеперечисленных сущностей является обособленной, за каждую отвечают отдельные пользователи.

Одно юрлицо, два расчётных счёта, касса, наличные и карта физлица.

Приводим в порядок справочники

Статьи движения денег

У нас есть два решения:

Поскольку все направления смежны, выберем первый вариант и введём единые справочники статей ДДС.

Шаг первый

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

Нормальным количеством будет считаться 30–40 самостоятельных групп. Если вы один раз за период покупали покрышки для авто, то не стоит создавать для этой операции отдельную группу, отнесите текущий расход, например, к группе «Расходы на обслуживание авто».

Постарайтесь сделать наиболее полный и достаточный список с учетом стратегии компании на год вперёд. Список статей ДДС будет определять форму отчёта и изменение его структуры исказит результаты в динамике за длительный промежуток времени.

Шаг второй

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

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

Список групп после работы с терминологией превращается в статьи движения денег. Теперь можно проектировать на их основе финансовый учёт и форму отчёта.

Статьи движения денег будут основой для отчета:

Счета

Чтобы выводить в отчёте остатки средств на дату, нужно каждой операции присвоить наименование счета или кассы. В учётных системах, которые перечислены в таблице вводных данных, они добавляются как справочники и присваиваются каждой операции.

Также можно будет анализировать отчёт о движении денег в разрезе определённого счета или кассы.

Подготовка таблицы

Теперь определим структуру таблицы, где будут отчёт и данные, на основе которых она строится. По сценарию решаемого кейса, учёт финансов двух интернет-магазинов ведётся в системе «Мой склад», а для барбершопа и салона красоты используется YClients. Есть возможность делать выгрузки данных за период.

Готовим область для данных из системы «Мой склад»

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

Вот так выглядит часть выписки из системы «Мой склад»:

Вот такой вид примет лист, содержащий данные из системы «Мой склад», который будет служить основой для модели отчетности:

Готовим область для данных из системы YClients

Аналогичные действия необходимо проделать для выгрузки из YClients. Ниже представлена часть выгрузки из системы:

Только важно учесть, что выгрузка из системы «Мой склад» была единой для двух интернет-магазинов, а в YClients для каждой точки есть свой личный кабинет и свои выгрузки. Чтобы разделять финансы разных заведений, добавляем одноименный столбец в начале таблицы:

Чтобы при загрузке данных пользователь выбирал определенное значение наименования заведения, создаём в столбце «Заведение» выпадающий список. Он нужен в первую очередь для того, чтобы исключить вероятность ошибки пользователя. Если будет ошибка в слове, то некорректно заполнится отчёт.

Шаг первый

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

Шаг второй

В меню на вкладке «Данные» выбираем пункт «Проверка данных».

Шаг третий

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

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

Шаг четвёртый

Получаем результат.

Создаём форму отчёта о движении денежных средств

В классическом варианте отчёт о движении денег показывает поступления и события по категориям за выбранный период:

В текущем кейсе мы разберём вариант реализации отчёта в динамике:

Форма отчёта рассчитана на год. В отчёте по столбцам располагаются суммы на каждый месяц года. Периоды легко сравнить между собой, поэтому все отклонения видны наглядно.

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

Самое время открыть таблицу с примером, чтобы разобраться в тонкостях. Посмотрите на вид формы отчета, она расположена на листе Cash Flow.

Теперь разберёмся в данных, которые имеются

Смотрим выгрузку из системы «Мой склад»

Открываем лист YСlients и выделяем аналогичные поля

Заполняем отчёт о движении денежных средств

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

Чтобы это реализовать, нужно разобраться в несложных формулах. И первым делом мы проведем обзор необходимых нам формул на примере упрощенных кейсов.

Формула СУММЕСЛИМН для моделей отчётов

Разберем на примере легкого кейса немного теории по «Google Таблицам». Переходите на лист «Данные», где расположен массив с информацией.

Лист «Отчет» — укрупненная модель отчёта статьи. Мы специально упростили, чтобы показать основы «Google Таблиц», которые понадобятся для реализации задачи в целом.

Лист «Данные» содержит информацию учёта финансов. Неважно, вели учет в таблице или скопировали из системы, мы будем использовать формулы, чтобы на их основе сделать отчет.

Лист «Отчет» состоит из двух таблиц, что для нас будет означать два этапа работы с формулой. Сперва разберём простой вариант, а далее усложним его.

Информация на листе «Данные» представляет собой множество финансовых операций. Каждая из них имеет поля: «Дата», «Статья ДДС», «Сумма».

Начнём с упрощённой версии отчёта и выведем суммы расходов по категориям.

Как работает формула СУММЕСЛИМН

Первым делом расскажем про её вид.

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

Дублируем ссылку на таблицу ниже:

Таблица доступна только для просмотра. Сохраните копию на свой «Google Диск»: Файл → Создать копию.

Шаг первый

Выделяем ячейку D5 таблицы отчета и вводим =СУММЕСЛИМН(.

Шаг второй

Указываем диапазон суммирования. Для этого при активной формуле перемещаемся на лист «Данные» и выделяем столбец D:

Шаг третий

Указываем диапазон критериев. В нашем случае это столбец «Статья ДДС» на листе «Данные»:

Шаг четвёртый

Указываем критерий. Чтобы сделать формулу универсальной, нужно сослаться на наименование статьи ДДС в форме отчета. Таким образом мы ищем указанное значение в таблице с данными и суммируем все значения, равные критерию.

Шаг пятый

Закрываем скобку и нажимаем на клавиатуре «Enter»:

Шаг шестой

Протягиваем формулу и проверяем полученные значения:

Шаг седьмой

Поле «Итого» заполняем формулой СУММ, которая объединяет значение из вышележащих ячеек.

Заполним отчёт посложнее

Следующая задача отличается только дополнительными критериями. Предыдщий отчет выводился в разрезе статей ДДС по всем данным. Теперь нам нужно разбить эти суммы на интервалы в виде месяцев года.

На листе «Данные» столбец В содержит даты. Наша задача — получить из ячейки с датой порядковый номер месяца. Для этого будем использовать одноименную формулу МЕСЯЦ.

Шаг первый

На листе «Данные» правее таблички добавляем столбец и называем его «Месяц».

Шаг второй

В ячейке Е4 вводим формулу =МЕСЯЦ( и в качестве единственного аргумента ссылаемся на ячейку с датой В4. Закрываем скобку, протягиваем формулу вниз и получаем искомые значения.

Теперь у нас есть столбец с критериями, а сами критерии содержатся в заголовке отчёта, можно заполнить отчёт данными.

Шаг первый

В ячейке D14 вводим формулу СУММЕСЛИМН по аналогии с простым отчётом, который мы разобрали выше:

Шаг второй

Вводим второй диапазон критериев. Выделяем столбец Е листа «Данные»:

Шаг третий

Задаём в качестве критерия ссылку на форму отчёта:

Шаг четвёртый

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

Шаг пятый

Нужно поработать с адресацией ячеек. Ссылка на ячейку имеет формат =A1, где А — координата столбца, 1 — координата строки.

Если написать в любой ячейку формулу вида =А1 и протянуть ее вниз, то можно заметить, что в нижележащих ячейках будет =А2, =А3 и так далее. Чтобы запретить изменение ссылки по строкам, нужно поставить знак $ перед координатой строки. Пример: =A$1.

Чтобы запретить изменение координаты по столбцам, нужно поставить знак $ перед координатой столбца. Пример: =$A1.

Чтобы запретить изменение координаты по строкам и столбцам, нужно поставить знак $ перед координатой строки и столбца. Пример: =$A$1.

Протягиваем формулу на всю таблицу, проверяем результат:

Шаг шестой

Заполняем поле «Итоги» и получаем искомый результат:

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

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

Шаг первый

На листе «Данные» создаем колонку F и называем её «Год».

Шаг второй

В ячейке F4 вводим формулу =ГОД(. Затем ссылаемся на ячейку с датой в той же строке и протягиваем формулу до конца таблицы:

Шаг третий

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

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

Протягиваем её на весь отчет и получаем результат:

Теперь сделаем выпадающий список в ячейке для выбора года.

Шаг первый

Выделяем ячейку, в меню сверху выбираем: «Данные» → «Проверка данных».

Шаг второй

Откроется диалоговое окно:

Шаг третий

В поле «Правила» выбираем пункт «Значения из списка»:

Шаг четвёртый

В соседнем окне вводим несколько значений через запятую и нажимаем «Сохранить»:

Шаг пятый

Теперь данные в отчёте меняются в зависимости от выбранного года:

Бонусы

Выше мы разобрали упрощенный и очень наглядный пример кейса. Для нашей исходной задачи принцип аналогичен.

Всем, кто дошёл до этого этапа, большое спасибо за внимание. Материал будет полезен, если вы составляете управленческие отчеты. Аналогичным образом можно реализовать задачи, связанные с бизнес-анализом.

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

Для закрепления материала завершите кейс самостоятельно. Инструменты и подход к решению аналогичен изложенному материалу. Только учтите, что листа с данными два. В таком случае нужно прописать формулу СУММЕСЛИМН к обоим листам, итоговая формула для отчета примет вид: =СУММЕСЛИМН1() + СУММЕСЛИМН2() … СУММЕСЛИМНN()

А также делимся записью мастер-класса, где мы пошагово рассмотрели похожую задачу.

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

Успехов!

Отчет о проделанной работе: зачем он нужен и как его составить

Еженедельный отчет сотрудника о проделанной работе — это документ, позволяющий оценить его продуктивность. Такая бумага совершенно необходима при внедрении и использовании KPI и других систем мотивации «за результат». Незаменима она и для контроля работников на удаленке.

Зачем и кому нужны

Каждому руководителю хочется знать, не напрасно ли он тратит деньги на найм и оплату труда персонала, приносит ли конкретный сотрудник пользу общему делу или только потребляет ресурсы. Сложность в том, что определить это не всегда просто, особенно когда речь идет о вспомогательных подразделениях, которые не участвуют непосредственно в получении прибыли. Если производительность рабочего на конвейере подсчитать легко, то как оценить эффективность бухгалтера или специалиста по охране труда? Оставлять их совсем без контроля нерационально, не замеченные вовремя промахи дорого обходятся компании. Да и разгребать потом «снежный ком» нерешенных проблем никому не понравится.

Немало управленцев предпочитают по старинке организовывать планерки, совещания или «вызов на ковер» конкретного сотрудника (или нескольких) с целью узнать, как они справляются со своими обязанностями. Это отнимает много времени и нервов как у руководителя, так и у его подчиненных, да и проверить достоверность полученной информации иногда не получается. Кроме того, устроить личную встречу не всегда есть возможность, если человек находится в командировке или переведен на удаленку.

Гораздо удобнее использовать такой инструмент, как отчет о проделанной работе, позволяющий за короткое время ознакомиться с результатами деятельности и оценить продуктивность исполнителя.

Использование такой отчетности оправданно в следующих ситуациях:

• внедряется или используется система KPI (ключевых показателей эффективности) или аналогичные. По сути матрица KPI и представляет собой срез информации о выполнении показателей, характеризующих деятельность;
• существуют премии «по итогам»;
• сотрудник находится в командировке или имеет разъездной характер деятельности;
• в компании трудятся так называемые удаленные сотрудники. При установлении дистанционного режима обязателен учет рабочего времени. Но потраченное время не всегда равно полученному результату, поэтому без сбора данных о выполнении поставленных задач не обойтись.

Как составить

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

Генеральному директору

ООО «Clubtk.ru»

Воронову А. В.

Отчет о проделанной работе

В период с 12 по 16 октября 2020 г. отделом кадров проделана следующая работа:

1. Подписаны трудовые договоры.
2. Произведены увольнения.
3. Подписаны дополнительные соглашения о защите персональных данных.

Начальник отдела кадров Иванов Иванов И. И.

На основании такого документа получится сделать вывод разве что о том, что сотрудникам отдела кадров нечем заняться.

Как же составить правильный документ? Руководствоваться стоит следующими принципами:

• краткость. Эпопею на 30 листах читать никому не хочется, и есть вероятность, что добравшись до конца, руководитель попросту забудет, о чем шла речь в начале;
• конкретность. Общие фразы — главный враг при составлении отчетного документа. Руководителя интересует результат, а не мастерство сотрудника в эпистолярном жанре;
• наглядность. Результат — главная часть. Если есть цифры — отлично, показываем их. Очень украшают документ диаграммы, графики или сравнительные таблицы. Если же оцифровать достижение невозможно, наиболее выигрышным представляется подход «от проблемы к решению». Он состоит из нескольких этапов:
  • обозначается проблема (задача);
  • описывается, какие шаги предприняты для решения (достижения цели), что получилось, что — нет и по каким причинам.

Отражают следующие сведения:

• Ф.И.О. работника;
• период, за который отчитываются;
• какие задачи поставлены;
• что из них выполнено;
• что из них не выполнено и почему;
• как оптимизировать деятельность;
• планы на будущий период.

Исходя из этого, примерный образец формы отчета о проделанной работе следующий:

Отчет по итогам деятельности __________________________________________________________________ (Ф.И.О. сотрудника или наименование подразделения)
Период	с _______________ по____________
Поставленные задачи:	1 _____________________________ 2 _____________________________ 3 _____________________________
Выполнено:	1 _____________________________ 2 _____________________________ 3 _____________________________
Не выполнено:	1 _____________________________ 2 _____________________________ 3 _____________________________
Причины невыполнения:	1 _____________________________ 2 _____________________________ 3 _____________________________
Планы на следующий период:	1 _____________________________ 2 _____________________________ 3 _____________________________
Предложения по оптимизации:	1 _____________________________ 2 _____________________________ 3 _____________________________
________________ _______________________ ____________________ (дата) ( подпись ответственного сотрудника) (расшифровка)

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

Вот хороший пример отчета о проделанной работе за месяц отдела кадров.

Форма и периодичность сдачи устанавливаются локальными нормативными актами организации или прописываются в трудовом договоре сотрудника. Встречаются такие варианты:

• ежедневные. Чаще всего используются в командировках и при разъездной работе;
• еженедельные. Удобны при удаленной работе, когда нет необходимости оценивать ежедневные результаты;
• ежемесячные. Употребляются при использовании систем оплаты и мотивации «за результат»;
• ежеквартальные;
• ежегодные. Используются в основном в целях финансового анализа и планирования затрат на персонал, его обучение, мотивацию и пр.

На практике сейчас чаще предоставляют отчет в excel, в виде выгрузки из CRM или других электронных документов. Но кое-где используются и бумажные экземпляры. Сколько хранить такие документы? Законодательством сроки не установлены, т. к. предоставление таких бумаг не обязательно. Организация вправе сама установить удобный период, исходя из специфики своей деятельности (обычно 1-3 года). Но это не касается отчетов, непосредственно на основании которых начисляется заработная плата или производятся другие финансовые операции. Их хранят длительно.

Можно ли не предоставлять

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

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

Каждый день появляется огромное количество нормативных актов и разъяснений. Охватить все невозможно, но самые важные и полезные изменения в законодательстве за прошедшую неделю мы собрали в одну статью. Из обзора вы узнаете:

• о правилах сдачи расчета по страховым взносам, если деятельность не велась;
• ошибках бухгалтера, за которые ответит руководитель;
• порядке расчета процентов, если ФСС опоздал с финансированием предупредительных мер.

Читать дальше

Каждый день появляется огромное количество нормативных актов и разъяснений. Охватить все невозможно, но самые важные и полезные изменения в законодательстве за прошедшую неделю мы собрали в одну статью. Из обзора вы узнаете:

• о новых правилах заполнения формы СЗВ-ТД;
• ситуациях, когда не надо платить страховые взносы и НДФЛ;
• расширении гарантий для отдельных категорий работников.

Читать дальше

Дополнительное соглашение об изменении графика работы — это документ, в котором стороны трудового договора пересматривают продолжительность рабочего дня, время его начала и окончания, время отдыха, устанавливают или отменяют его особенности (ненормированный, гибкий, порядок чередования смен, без перерыва на обед и т. д.). 

Читать дальше

Показать еще