Работать со сводными таблицами Excel приходится в разных сферах. Можно быстро обрабатывать большие объемы информации, сравнивать, группировать данные. Это значительно облегчает труд менеджеров, продавцов, руководителей, маркетологов, социологов и т.д.
Сводные таблицы позволяют быстро сформировать различные отчеты по одним и тем же данным. Кроме того, эти отчеты можно гибко настраивать, изменять, обновлять и детализировать.
У нас есть тренировочная таблица с данными:
Каждая строка дает нам исчерпывающую информацию об одной сделке:
Если это огромная сеть магазинов и продажи идут, то в течение одного квартала размер таблицы станет ужасающим. Проанализировать данные в сотне строк будет очень сложно. А на составление отчета уйдет не один день. В такой ситуации сводная таблица просто необходима.
Создам отчет с помощью мастера сводных таблиц. В новых версиях Excel он почему-то спрятано глубоко в настройках:
Теперь инструмент находится в панели быстрого доступа, а значит всегда под рукой.
Готовый отчет можно форматировать, изменять.
Это можно сделать вручную и автоматически.
Настройка автоматического обновления при изменении данных:
Теперь каждый раз при открытии файла с измененными данными будет происходить автоматическое обновление сводной таблицы.
Когда мы сводим в отчет большой объем данных, для выводов и принятия каких-то решения может понадобиться группировка. Допустим, нам нужно увидеть итоги за месяц или квартал.
Группировка по дате в сводной таблице Excel:
Получаем отчет, в котором четко видны суммы продаж по месяцам. Поэкспериментируем и установим шаг – «Кварталы». Результат – сводная таблица вида:
Если фамилия продавцов для анализа деятельности сети магазинов не важна, можем сформировать отчет с поквартальной прибылью.
Чтобы убрать результаты группировки, необходимо щелкнуть по ячейке с данными правой кнопкой мыши и нажать разгруппировать. Либо выбрать данный параметр в меню «Структура».
У нас есть сводный отчет такого вида:
Видны итоги по месяцам (сделано «Группировкой») и по наименованиям товаров. Сделаем отчет более удобным для изучения.
Как в сводной таблице сделать итоги сверху:
Уже нет той перегруженности, которая затрудняла восприятие информации.
Как удалить промежуточные итоги? Просто на вкладке макет выбираем «Не показывать промежуточные суммы»:
Получим отчет без дополнительных сумм:
Огромные сводные таблицы, которые составляются на основании «чужих» таблиц, периодически нуждаются в детализации. Мы не знаем, откуда взялась сумма в конкретной ячейке Excel. Но можно это выяснить, если разбить сводную таблицу на несколько листов.
Мы можем переместить всю сводную таблицу на новый лист, выбрав на вкладке «Действия» кнопку «Переместить».
По умолчанию в сводную таблицу помещается абсолютно вся информация из того столбца, который мы добавляем в отчет.
В нашем примере – ВСЕ товары, ВСЕ даты, ВСЕ суммы и магазины. Возможно, пользователю не нужны некоторые элементы. Они просто загромождают отчет и мешают сосредоточиться на главном. Уберем ненужные элементы.
Жмем ОК – сводная таблица меняется.
Системы Excel-таблиц
с удобной аналитикой
Постановка начального (управленческого) учета - это создание инструментов для получения информации о фактическом состоянии дел в бизнесе. Чаще всего это система таблиц и отчетов на их основе в Excel. Они отражают удобную ежедневную аналитику о реальных прибылях и убытках, движении денег, задолженности по зарплате, расчетах с поставщиками или покупателями, себестоимости и др. Опыт показывает, что предприятию малого бизнеса достаточно системы из 4-6 простых для заполнения таблиц.
Специалисты компании «Мой финансовый директор» вникают в детали вашего бизнеса и формируют оптимальную систему управленческого учета, отчетности, планирования, экономических расчетов на основе наиболее доступных программ (обычно Excel и 1С).
Сама работа состоит из внесения исходных данных в таблицы и занимает не более 1-2 часов в день. Для ее выполнения достаточно 1-2 уже имеющихся у вас штатных специалистов не имеющих бухгалтерских навыков.
Систему таблиц можно организовать с разделением доступа к информации. Общую картину и секретную часть данных будет видеть только директор (владелец) бизнеса, а исполнители - каждый свою часть.
Получаемые в результате автоматические отчеты дают картинку с нужной степенью детализации: себестоимость и рентабельность раздельно по товарным линейкам, своды затрат по группам расходов, отчет о прибылях и убытках, о движении денежных средств, управленческий баланс и др. Вы принимаете решения на основе обоснованной, точной и оперативной управленческой информации.
В разделе "Вопрос-ответ" Вы найдете примеры плана движения денежных средств и системы учета движения денежных средств с сопутствующими отчетами.
ВАЖНО! Вы получаете услуги на уровне опытного финансового директора по ставке обычного экономиста.
Мы обязательно обучаем ваших сотрудников самостоятельной работе с таблицами. Если у вас некому поручить эту работу, мы готовы вести ваш учет в режиме аутсорсинга. Это в 2-3 раза дешевле, чем нанять и содержать отдельного человека.
На выполненную работу распространяется гарантия. Система начального учета поддерживается в рабочем состоянии до тех пор, пока вы ей пользуетесь. При желании вы получаете все необходимые консультации и разъяснения.
Если вы захотите что-то изменить или добавить, специалисты сделают необходимые доработки независимо от давности оказания услуги. Обращайтесь, служба поддержки работает в режиме 24/7.
Позвоните +7 950 222 29 59
, чтобы задать любые вопросы и получить дополнительную информацию.
Архив файлов примеров к статьям сайта по выполнению различных задач в Excel: анализы, отчеты, бланки документов, таблицы с формулами и вычислениями, графики и диаграммы.
Шаблон телефонного справочника.
Интерактивный шаблон справочника контактов для бизнеса. Удобное управление большой базой контактов.
Складской учет в Excel скачать бесплатно.
Программа для складского учета создана исключительно с помощью функций и стандартных инструментов. Без использования макросов и программирования.
Формула рентабельности собственного капитала «ROE».
Формула, которая отображает Экономический смысл финансового показателя «ROE».
Эффективный инструмент для оценки инвестиционной привлекательности предприятия.
График спроса и предложения.
График, который отображает зависимость между двумя главными финансовыми величинами: спрос и предложения. А так же формулы для поиска эластичности спроса и предложения.
Полный инвестиционный проект.
Готовый детальный анализ инвестиционного проекта, в который включены все аспекты: финансовая модель, расчет экономической эффективности, сроки окупаемости, рентабельность инвестиций, моделирование рисков.
Сокращенный инвестиционный проект.
Базовый инвестиционный проект, в который включены только основные показатели для анализа: сроки окупаемости, рентабельность инвестиций, риски.
Анализ инвестиционного проекта.
Полноценный расчет и анализ доходности инвестиционного проекта с возможностью моделирования рисков.
График формулы Гордона.
Построение графика с экспоненциальной линией тренда по модели Гордона для анализа доходности инвестиций от дивидендов.
График модели Бертрана.
Готовое решение построение графика модели Бертрана, по которому можно анализировать зависимость спроса и предложения в условиях демпинга цен на дуопольных рынках.
Алгоритм расшифровки ИНН.
Формула для расшифровки Индикационного налогового номера для: России, Украины и Беларуси.
Поддерживаются все виды ИНН (10-ти и 12-ти значные номера) физических и юридических лиц, а также личный номер.
Факторный анализ отклонений.
Факторный анализ отклонений в маржинальном доходе предприятия с учетом показателей: материальные затраты, выручка, маржинальный доход, цена-фактор.
Табель учета рабочего времени.
Скачать табель учета рабочего времени в Excel с формулами для автозаполнения таблицы + ведение справочников для удобства работы.
Прогноз по продажам с учетом сезонности.
Составленный готовый прогноз по продажам на следующий год на основе показателей продаж предыдущего года с учетом сезонности. Графики прогноза и сезонности — прилагаются.
Прогноз показателей деятельности предприятия.
Бланк прогноза деятельности предприятия с формулами и показателями: выручка, материальные затраты, маржинальный доход, накладные расходы, прибыль, рентабельность продаж (ROS) %.
Баланс рабочего времени.
Отчет по планированию рабочего времени работников предприятия по таким временным показателям как: «календарное время», «табельное», «максимально возможное», «явочное», «фактическое».
Чувствительности инвестиционного проекта.
Анализ динамики изменений результатов в соотношении с изменениями ключевых параметров является чувствительностью инвестиционного проекта.
Расчет точки безубыточности магазина.
Практический пример вычисления сроков выхода на безубыточности магазина или других видов розничной точки.
Таблица для проведения финансового анализа.
Программный инструмент выполнен в Excel предназначен для выполнения финансовых анализов предприятий.
Система анализа предприятий.
Информативный финансовый анализ предприятие легко провести с помощью аналитической системы от профессиональных специалистов в области экономики и финансов.
Пример финансового анализа рентабельности бизнеса.
Таблица с формулами и функциями для анализа рентабельности бизнеса по финансовым показателям предприятия.
Управленческий учет предназначен для представления фактического состояния дел на предприятии и, соответственно, принятия на основе данных управленческих решений. Это система таблиц и отчетов с удобной ежедневной аналитикой о движении денежных средств, прибылях и убытках, расчетах с поставщиками и покупателями, себестоимости продукции и т.п.
Каждая фирма сама выбирает способ ведения управленческого учета и нужные для аналитики данные. Чаще всего таблицы составляются в программе Excel.
Основные финансовые документы предприятия – отчет о движении денежных средств и баланс. Первый показывает уровень продаж, затраты на производство и реализацию товаров за определенный промежуток времени. Второй – активы и пассивы фирмы, собственный капитал. Сопоставляя эти отчеты, руководитель замечает положительные и отрицательные тенденции и принимает управленческие решения.
Опишем учет работы в кафе. Предприятие реализует продукцию собственного производства и покупные товары. Имеют место внереализационные доходы и расходы.
Для автоматизации введения данных применяется таблица управленческого учета Excel. Рекомендуется так же составить справочники и журналы с исходными значениями.
Если экономист (бухгалтер, аналитик) планирует расписывать по статьям и доходы, то такой же справочник можно создать для них.
Не нужно все цифры по работе кафе вмещать в один отчет.
Пусть это будут отдельные таблицы. Причем каждая занимает одну страницу. Рекомендуется широко использовать такие инструменты, как «Выпадающие списки», «Группировка». Рассмотрим пример таблиц управленческого учета ресторана-кафе в Excel.
Присмотримся поближе.
Результирующие показатели найдены с помощью формул (применены обычные математические операторы). Заполнение таблицы автоматизировано с помощью выпадающих списков.
При создании списка (Данные – Проверка данных) ссылаемся на созданный для доходов Справочник.
Для заполнения отчета применили те же приемы.
Чаще всего в целях управленческого учета используется отчет о прибылях и убытках, а не отдельные отчеты по доходам и расходам. Данное положение не нормируется. Поэтому каждое предприятие выбирает самостоятельно.
В созданном отчете для подсчета результатов используются формулы, автозаполнение статей с помощью выпадающих списков (ссылки на Справочники) и группировка данных.
Источник информации для анализа – актив Баланса (1 и 2 разделы).
Для лучшего восприятия информации составим диаграмму:
Как показывает таблица и рисунок, основную долю в структуре имущества анализируемого кафе занимают внеоборотные активы.
Скачать пример управленческого учета в Excel
По такому же принципу анализируется пассив Баланса. Это источники ресурсов, за счет которых кафе осуществляет свою деятельность.
Итак нам нужен бюджет проекта, который состоит из статей затрат. Для начала сформируем в Micfosoft Project 2016 список этих самых статей затрат.
Будем использовать настраиваемые поля для. Формируем таблицу подстановки настраиваемого поля типа Текст для таблицы Ресурсы, например как на этом рисунке (конечно у вас будут свои статьи затрат, данный перечень только как пример):
Рис. 1. Формирование списка статей затрат
Работа с настраиваемыми полями была описана в Учебном пособии по управлению проектами в Microsoft Project 2016 (см. раздел 5.1.2 Веха). Для удобства поле можно переименовать в Статьи Затрат. После формирования списка статей затрат их необходимо присвоить ресурсам. Для этого добавим поле Статьи Затрат в представление Ресурсы и каждому ресурсу присвоим свою статью затрат (см.
Рис. 2. Присвоение статей затрат ресурсам
Возможности Microsoft Project 2016 позволяют присваивать только одну статью затрат на ресурс. Это нужно учитывать при формировании списка статей затрат. Например, если создать две статьи затрат (1.Зарплата, 2.Отчисления на соцстрах) то их невозможно будет присвоить одному сотруднику. Поэтому рекомендуется группировать статьи затрат таким образом, чтобы одну статью можно было назначить на один ресурс. В нашем примере можно сформировать одну статью затрат — ФОТ.
Для визуализации бюджета в разрезе статьей затрат и временных периодов хорошо подходит представление Использование ресурсов, которое необходимо немного модифицировать следующим образом:
1. Создать группировку по статьям затрат (см. Учебное пособие по управлению проектами в Microsoft Project 2016, раздел 2.5 Использование группировок)
Рис. 3. Создание группировки Статьи Затрат
2. В левой части представления вместо поля Трудозатраты вывести поле Затраты.
3. В правой части представления вместо поля Трудозатраты вывести поле Затраты (щелкнув в правой части на правую кнопку мыши):
Рис. 4. Выбор полей в правой части представления Использование ресурсов
4. Установить удобный масштаб для правой части, например, по месяцам. Для этого необходимо щелкнуть правой кнопкой мыши в заголовке таблицы правой части.
5. Пример бюджета проекта
В результате этих нехитрых действий получаем в Microsoft Project 2016 бюджет проекта в разрезе заданных статей затрат и временных периодов. При необходимости можно детализировать каждую статью затрат до конкретных ресурсов и задач просто нажав на треугольник в левой части поля Название ресурса.
Рис. 6. Детализация затрат по проекту
Для графического отображения изменения затрат во времени принято использовать кривую затрат проекта. Форма кривой затрат типична для большинства проектов и напоминает букву S, поэтому её еще называют S-кривой проекта.
S-кривая показывает зависимость суммы затрат от сроков проекта. Так, если работы начинаются «Как можно раньше» S-кривая смещается к началу проекта, а если работы начинаются «Как можно позже» соответственно к окончанию проекта.
Рис. 7. Кривая затрат проекта в зависимости от сроков задач
Планируя задачи «Как можно раньше» (это установлено в Microsoft Project 2016 автоматически при планировании от начала проекта) мы снижаем риски нарушения сроков, но при этом необходимо понимать график финансирования проекта, иначе на проекте может быть кассовый разрыв. Т.е. затраты на наши задачи превысят доступные финансовые ресурсы, что грозит рисками остановки работ на проекте.
Планируя задачи «Как можно позже» (это установлено в Microsoft Project 2016 автоматически при планировании от окончания проекта) мы подвергаем проект большим рискам срыва сроков.
Исходя из этого, руководитель должен найти «золотую середину», другими словами, некий баланс между рисками нарушения сроков и рисками наступления кассового разрыва проекта.
Рис. 8. Кривая затрат проекта в MS-Excel путем выгрузки информации из MS-Project
Бюджет на очередной год формируется с учетом функционирования предприятия: продажи, закупка, производство, хранение, учет и т.п. Планирование бюджета – это продолжительный и сложный процесс, ведь он охватывает большую часть среды функционирования организаций.
Для наглядного примера рассмотрим дистрибьюторскую фирму и составим для нее простой бюджет предприятия с примером в Excel (пример бюджета можно скачать по ссылке под статьей).
В бюджете можно планировать расходы на бонусные скидки для клиентов. Он позволяет моделировать различные программы лояльности и при этом контролировать расходы.
Наша фирма обслуживает около 80-ти клиентов. Ассортимент товаров составляет около 120-ти позиций в прайсе. Она делает наценку на товары 15% от их себестоимости и таким образом устанавливает цену продажи. Такая низкая наценка экономически обоснована плотной конкуренцией и оправдывается большим товарооборотом (как и на многих других дистрибьюторских предприятий).
Для клиентов предлагается бонусная система вознаграждений. Процент скидки на закупку для крупных клиентов и ресселеров.
Условия и размер процентной ставки бонусной системы определяется двумя параметрами:
В годовом бюджете бонусы относятся к разделу «планирование продаж», поэтому они влияют на важный показатель фирмы – маржу (показатель прибыли в процентном соотношении от общего дохода). Поэтому важной задачей является возможность устанавливать несколько вариантов бонусов с разными границами на уровнях реализации и соответствующих им % бонусов. Нужно чтобы маржа удерживалась в определенных границах (например, не меньше 7% или 8%, вед это же прибыль фирмы). А клиенты смогут выбирать себе несколько вариантов бонусных скидок.
Наша модель бюджета с бонусами будет достаточно проста, но эффективная. Но сначала составим отчет движения средств по конкретному клиенту, чтобы определить можно ли давать ему скидки. Обратите внимание на формулы, которые ссылаются на другой лист пред тем как посчитать скидку в процентах в Excel.
Проект бюджета в Excel состоит из двух листов:
Структура таблицы «Продажи за 2015 год по клиенту:» на листе «продажи»:
На втором листе устанавливаем границы для достижения бонусов соответствующие им проценты скидок.
Следующая таблица – это базовая форма бюджета доходов и расходов в Excel с общими финансовыми показателями фирмы за годовой период.
Структура таблицы «Условия бонусной системы» на листе «результаты»:
Структура таблицы «Общий отчет по обороту фирмы» на листе «результаты»:
И так у нас есть готовая модель бюджета предприятия в Excel, которая является динамической. Если граничная планка бонусов находится на уровне 200, а бонусная скидка составляет 3%. Это значит, что в прошлом году клиент приобрел товара в количестве 200шт. А в конце года получит за это бонус скидку 3% от стоимости. А если клиент приобрел 400шт определенного товара, значит, он преодолел вторую граничную планку бонусов и получает скидку уже 6%.
При таких условиях изменится показатель «Маржа 2», то есть чистая прибыль дистрибьютора!
Задача руководителя дистрибьюторской фирмы выбрать самые оптимальные уровни граничных планок для предоставления клиентам скидки. Выбирать нужно так чтобы показатель «Маржа 2» находился хотя бы в приделах 7%-8%.
Скачать бюджет предприятия-бонус (образец в Excel).
Чтобы не искать лучшее решение методом тыка, и не делать ошибок рекомендуем прочитать следующею статью. Там описано как сделать в Excel простой и эффективный инструмент: Таблица данных в Excel и матрица чисел. С помощью «таблицы данных» можно в автоматическом режиме визуализировать самые оптимальные условия для клиента и дистрибьютора.
Ренат уже не в первый раз выступает гостевым автором на Лайфхакере. Ранее мы публиковали отличный материал от него о том, как составить план тренировок: и онлайн-ресурсы, а также создания тренировочного плана.
В этой статье собраны несложные приёмы, позволяющие . Особенно они пригодятся тем, кто занимается управленческой отчётностью, готовит разнообразные аналитические отчёты, основанные на выгрузках из 1С и других отчётах, формирует из них презентации и диаграммы для руководства. Не претендую на абсолютную новизну - в том или ином виде эти приёмы наверняка обсуждались на форумах или .
Результат:
В данном случае, кроме функции СИМВОЛ (CHAR) (для отображения кавычек) используется функция ЕСЛИ (IF), позволяющая изменять текст в зависимости от того, наблюдается ли положительная динамика продаж, и функция ТЕКСТ (TEXT), позволяющая отобразить число в любом формате. Её синтаксис описан ниже:
ТЕКСТ (значение; формат )
Формат указывается в кавычках точно так же, как если бы вы вводили пользовательский формат в окне «Формат ячеек».
Автоматизировать можно и более сложные тексты. В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчётности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать - стоит один раз озадачиться созданием формулы или макроса, которые избавят вас хотя бы от части работы.
При объединении ячеек сохраняется только одно значение. Excel предупреждает об этом при попытке объединить ячейки:
Соответственно, если у вас была формула, зависящая от каждой ячейки, она перестанет работать после их объединения (ошибка #Н/Д в строках 3–4 примера):
Чтобы объединить ячейки и при этом сохранить данные в каждой из них (возможно, у вас есть формула, как в этом абстрактном примере; возможно, вы хотите объединить ячейки, но сохранить все данные на будущее или скрыть их намеренно), объедините любые ячейки на листе, выделите их, а затем с помощью команды «Формат по образцу» перенесите форматирование на те ячейки, которые вам и нужно объединить:
Если вам нужно построить сводную сразу из нескольких источников данных, придётся добавить на ленту или панель быстрого доступа «Мастер сводных таблиц и диаграмм», в котором есть такая опция.
Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить»:
После этого на ленте появится соответствующая иконка, нажатие на которую вызывает того самого мастера:
При щелчке на неё появляется диалоговое окно:
В нём вам необходимо выбрать пункт «В нескольких диапазонах консолидации» и нажать «Далее». В следующем пункте можно выбрать «Создать одно поле страницы» или «Создать поля страницы». Если вы хотите самостоятельно придумать имя для каждого из источников данных - выберите второй пункт:
В следующем окне добавьте все диапазоны, на основании которых будет строиться сводная, и задайте им наименования:
После этого в последнем диалоговом окне укажите, где будет размещаться отчёт сводной таблицы - на существующем или новом листе:
Отчёт сводной таблицы готов. В фильтре «Страница 1» вы можете выбрать только один из источников данных, если это необходимо:
В данном примере в столбце A есть несколько текстовых строк, и наша задача - выяснить, сколько раз в каждой из них встречается искомый текст, расположенный в ячейке E1:
Для решения этой задачи можно воспользоваться сложной формулой, состоящей из следующих функций:
Чтобы найти вхождение определённой текстовой строки в другую, нужно удалить все её вхождения в исходную и сравнить длину полученной строки с исходной:
ДЛСТР(“Тариф МТС Супер МТС”) – ДЛСТР(“Тариф Супер”) = 6
А затем разделить эту разницу на длину той строки, которую мы искали:
6 / ДЛСТР (“МТС”) = 2
Именно два раза строка «МТС» входит в исходную.
Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» - тот, число вхождений которого нас интересует):
=(ДЛСТР(текст )-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(текст );ПРОПИСН(искомый );“”)))/ДЛСТР(искомый )
В нашем примере формула выглядит следующим образом:
=(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A2);ПРОПИСН($E$1);“”)))/ДЛСТР($E$1)
Я работаю обычным аналитиком и, так получилось, что летом 2014 года, участвуя в одном e-commerce проекте, на коленке за 3 недели сделал управленческий учет в MS Excel. Давно планировал и наконец-то решил выложить на Хабр. Думаю, будет полезно малым предпринимателям, понимающим важность управления финансовыми потоками, но не желающим тратить значительное количество времени и средств на ведение управленческого учета. Не претендую на истину в последней инстанции и буду рад иным решениям, предложенным участниками сообщества.
Бизнес, к которому я летом имел отношение, был обычным интернет-магазином одежды премиум и выше сегмента с оборотом около 1 млн рублей в месяц. Бизнес работал, не сказать, чтобы очень успешно, но работал и продолжает работать. Собственник понимал необходимость ведения управленческого учета и, с этим пониманием, взял меня в качестве финансового директора (аналитика/менеджера...), так как предыдущий ушел из бизнеса за 3 месяца до моего прихода. Собственно, дыра такой же продолжительности была и в ведении управленческого учета. Забегая вперед скажу, что дыру не устранил (решили не ворошить прошлое), но создал систему, которая успешно работает при минимальных трудозатратах и по сей день.
Мой предшественник вёл управленку в Финграде , который оказался весьма мощным инструментом. Например, он позволял автоматически грузить информацию из 1С и выписок разных банк-клиентов, создавая проводки по заранее сформулированным правилам. Вещь, безусловно полезная, однако, при соблюдении системы двойной записи увеличивала время работы в разы. Чтобы избежать увеличения работы этот инструмент позволял генерировать «зависимые проводки». В создании этих дополнительных проводок и была зарыта собака. И тут выяснилось, что за всей мощью Финграда крылась уникальность, обусловившая полное отсутствие экспертизы в свободном доступе. Обычным пользователям (платившим, кстати, 3000 рублей в месяц за доступ к системе) были доступны лишь «Руководство пользователя» на официальном сайте, да 6 видео-уроков там же. Youtube, дававший доступ к ещё паре десятков видеоуроков, также не сильно помогал. Форумов с информацией «how to...» не было в принципе. Поддержка, на конкретные вопросы о правилах создания «зависимых проводок» и просьбах помочь именно в моем случае - морозилась фразами «у нас с вами не заключен договор на поддержку, поэтому на такие специфические вопросы мы не готовы отвечать». Хотя казалось бы - чего специфического в таких просьбах, да ещё и со скриншотами с моей стороны? Понятно, что все можно бить руками, но спрашивается, а зачем тогда вообще платить за инструмент, который сильно увеличивает время, необходимое на ведение управленки и не дает никаких преимуществ для малого бизнеса?
Убедив собственника в нецелесообразности использования «Финграда» при таких объемах бизнеса и выгрузив всю информацию из системы, я поставил на нем БОЛЬШОЙ и жирный крест. При этом решение уйти именно в MS Excel было не спонтанным. Хорошенько загуглив на тему ведения управленческого учета находил монстров, похожих на «Финград» , либо ссылки на веб-приложения для ведения личных финансов, в то время как основными требованиями к системе были:
Возможность ведения БДДС и БДР на основе изменяемого плана счетов;
- простота в дальнейшем ведении управленческого учета (в том числе силами «финансово-неграмотных» пользователей);
- гибкость (возможность на ходу расширять/убирать функционал);
- отсутствие перегруженности инструмента/интерфейса.
Для начала проясним термины: будучи не финансистом, под БДДС понимаю «Баланс Движения Денежных Средств», БДР - «Бюджет Доходов и Расходов». БДДС считаем кассовым методом (днем совершения операции - колонка «Дата операции») и используем для операционного day-to-day планирования, а БДР методом начисления (колонка «Период начисления») для стратегического, в рамках года и более.
Итак, как все устроено и как оно работает (в идеале):
1. Управленческий учет собирается на основе информации вводимой конечными пользователями при помощи формы в Google Docs. Красным помечены названия полей и кодировки вариантов в конечном файле управленческого учета - своего рода мапинг полей.
2. В итоге выглядит оно так (зеленым залито то, что перенесено в итоговый файл управленки).
3. Управленческий учет построен на базе.xls выгрузки из Финграда (отсюда странные для сторонних пользователей названия и, в целом избыточное количество колонок). Убедительная просьба не воспринимать всерьез значения колонок «Приход», «Расход» - многое рандомно изменено.
Механизм заполнения прост: аккуратно переносим во вкладку «Общая книга» из формы Google Docs и банковских выписок. Красным выделены строки, используемые для формирования БДР, зеленым - БДДС., которые представляют собой сводные таблицы и строятся на основе промежуточных вкладок с говорящими названиями. Единственные колонки, информация в которых не связана с иными источниками: «Исходный ID» (уникальные значения строк) и «Дата создания» (=ТДАТА(), а затем копируем и вставляем как значение)
4. Статьи ДДС (движения денежных средств) располагаются на отдельной вкладке «ПС_служебный» и вполне могут регулярно пересматриваться в зависимости от конкретных потребностей (не забываем обновлять формулы на листах «Данные_БДДС», «Данные_БДР»).
5. На картинке образец БДДС, в формате по умолчанию, свернутый до понедельной «актуальности».
6. Образец БДС (помесячный). Обратите внимание на уже упоминавшийся выше тезис об использовании строк из «Общей книги»: Бюджет и Факт для БДР, План и Факт - для БДДС.
7. Работа с БДДС подразумевает поддержание строк «План» в максимально актуальном состоянии. Я достаточно педантичен в работе с первичной информацией и комментарии сделанные мной сохраняли всю историю изменений. Как будет у Вас - вопрос к Вам. Мой подход позволил мне отлавливать примерно 1 существенную ошибку в неделю, грозившую расхождениями на десятки-сотни тысяч рублей. Время, кстати, съедалось немного.
8. Собственно сам
Сводная таблица - это эффективный инструмент для вычисления, сведения и анализа данных, который упрощает поиск сравнений, закономерностей и тенденций.
Подготовка
Если у вас недостаточно опыта работы со сводными таблицами или вы не знаете, с чего начать, лучше воспользоваться рекомендуемой сводной таблицей . При использовании этой функции Excel определяет подходящий макет, сопоставляя данные с наиболее подходящими областями в сводной таблице. Это позволяет получить отправную точку для дальнейших экспериментов. После создания рекомендуемой сводной таблицы вы можете изучить различные ориентации и изменить порядок полей для получения нужных результатов.
В области Имя поля строк столбцов значений
Значения в сводной таблице
Обновление сводных таблиц
При добавлении новых данных в источник необходимо обновить все сводные таблицы, созданные на его основе. Чтобы обновить одну сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить . При наличии нескольких сводных таблиц сначала выберите любую ячейку в любой сводной таблице, а затем на ленте откройте вкладку Анализ сводной таблицы , щелкните стрелку под кнопкой Обновить и выберите команду Обновить все .
Удаление сводной таблицы
Если вам больше не нужна сводная таблица, просто выделите ее и нажмите клавишу DELETE . Это не повлияет на другие данные и сводные таблицы или диаграммы. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.
Теперь сводную таблицу можно вставить в электронную таблицу в Excel Online.
Работа со списком полей сводной таблицы
В области Поля сводной таблицы вверху установите флажки для полей, которые вы хотите добавить в сводную таблицу. По умолчанию нечисловые поля добавляются в область строк , поля значений даты и времени - в область столбцов , а числовые поля - в область значений . Вы также можете вручную перетаскивать элементы в любые поля сводной таблицы. Если элемент больше не нужен, просто перетащите его за пределы списка полей или снимите его флажок. Возможность перестановки элементов - одна из особенностей сводной таблицы, благодаря которой можно быстро и просто изменять ее вид.
Работа с значениями сводной таблицы
Обновление сводных таблиц
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить
Удаление сводной таблицы
Если вам больше не нужна сводная таблица, просто выделите ее и нажмите клавишу DELETE. Это не повлияет на другие данные и сводные таблицы или диаграммы. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.
Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте
