Секреты создания отчетов в эксель. Подготовка полугодовой отчетности в excel. Обучение или ведение

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

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

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

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

Основные требования

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

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

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

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

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

Инструкция по созданию

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

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

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

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

Также необходимо указать, где пользователь желает создать сводный отчёт:

• на том же самом листе, где имеются основные показатели;
• на новом листе.

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

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

В завершение остаётся сделать последнюю область сводного отчёта «Значения». В неё переносится поле, являющееся ключевым для анализа, например, общая масса, общее количество.

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

Использование сводных отчётов

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

Применение фильтра

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

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

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

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

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

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

ПОДГОТОВКА ПОЛУГОДОВОЙ ОТЧЕТНОСТИ В EXCEL

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

• большое количество позиций в отчете;
• объемные отчеты — каждый показатель нужно представить в помесячной динамике;
• разброс значений показателей, разный порядок цифр.

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

Спарклайны в Excel

В полугодовом отчете принято демонстрировать помесячную динамику показателей. Речь идет о таких показателях, как выручка, объем реализации, себестоимость, объем закупок и прибыли, рентабельность. Перечень номенклатуры на практике всегда широкий. В таком случае удобно непосредственно в таблицу с показателями вставить графики, что позволит оценить не только отдельные показатели, но и их динамику, сопоставить результаты. Для указанной группы показателей целесообразно применить спарклайны (рис. 1).

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

1. В таблице 1 создаем столбец или строку для размещения следующей графической информации:

• «Динамика за I полугодие»;
• «Анализ выручки по продуктовым группам»;
• «Анализ рентабельности по продуктовым группам».

2. Выполняем команду: Вставка → Спарклайны → График (см. рис. 1). В окне «Создание спарклайнов » задаем диапазон данных и диапазон расположения (рис. 2).

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

4. Для «Анализа выручки по продуктовым группам» и «Анализа рентабельности по продуктовым группам» используем тип спарклайна «Столбец».

Дополненная спарклайнами табл. 1 представлена в «Сервисе форм» (код доступа — на с. 119). Из таблицы видно, что только выручка по красителям снижается, имеет нисходящую динамику графика-спарклайна. По остальной продукции и в целом по компании выручка растет (графа «Динамика за первое полугодие»).

Строка «Анализ выручки по продуктовым группам» показывает, что наибольший объем выручки компания получает от реализации кирпича стандартного гладкого — 4003,9 из 42 068,82 тыс. руб.

Столбцовый спраклайн в строке «Анализ рентабельности по продуктовым группам» показывает, что максимальную рентабельность имеет кирпич фактурный — от 40 до 42,9 %. Скачек рентабельности произошел в апреле (видно на графике в столбце «Динамика за I полугодие»). В апреле зафиксирована отрицательная рентабельность по кирпичу стандартному и сниженная (11,1 %) по кирпичу гладкому с повышенным содержанием пигментов.

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

ОБРАТИТЕ ВНИМАНИЕ

При копировании таблицы со спарклайнами из Excel в документ Word графики могут быть не видны. В таком случае используют вставку в формате рисунка.

Условное форматирование

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

Подготовим табл. 2 «Бюджет общезаводских расходов за I полугодие (факт)» к сдаче руководству с помощью функций условного форматирования:

1. Выделяем в таблице диапазон, информацию которого нужно представить более наглядно (в примере — столбцы январь-март). Задаем: Главная → Условное форматирование → Цветовые шкалы . Выберем информативную цветовую шкалу «зеленый-белый» (рис. 4). Пользователи могут выбрать шкалу цвета на свое усмотрение.

Высокие расходы окрасились в привлекающий внимание зеленый цвет:

• расходы на фонд оплаты труда с отчислениями — 250, 240 и 236 тыс. руб.;
• представительские расходы в марте — 300 тыс. руб.

Чем выше сумма расходов, тем насыщеннее цвет в ячейке.

1. Дополняем табл. 2 визуализацией итоговых показателей по статьям расходов. Выделим цифры в столбце факта за полугодие и выполним действия: Главная → Условное форматирование → Наборы значков → Другие правила . Сделаем индивидуальные настройки (рис. 5).

Выбираем «Форматировать все ячейки на основании их значений», а затем подходящий «Значок». В нашем случае это гистограммы. Определяем «Значение» и «Тип»:

• заполненная гистограмма (четыре деления) — для значений более 500 тыс. руб.;
• гистограммы с тремя делениями — от 100 до 500 тыс. руб.;
• гистограмма с одним делением — все значения менее 100 тыс. руб.

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

Бюджет общезаводских расходов для отправки руководству выглядит следующим образом (табл. 2).

На примере таблицы «Кредиторская задолженность на конец периода» рассмотрим еще один способ удобно представить показатели.

Е. С. Панченко, бизнес-консультант

Материал публикуется частично. Полностью его можно прочитать в журнале

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

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

Использование описательной статистики

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

В Экселе существует отдельный инструмент, входящий в «Пакет анализа», с помощью которого можно провести данный вид обработки данных. Он так и называется «Описательная статистика». Среди критериев, которые высчитывает данный инструмент следующие показатели:

• Медиана;
• Мода;
• Дисперсия;
• Среднее;
• Стандартное отклонение;
• Стандартная ошибка;
• Асимметричность и др.

Рассмотрим, как работает данный инструмент на примере Excel 2010, хотя данный алгоритм применим также в Excel 2007 и в более поздних версиях данной программы.

Подключение «Пакета анализа»

Как уже было сказано выше, инструмент «Описательная статистика» входит в более широкий набор функций, который принято называть Пакет анализа. Но дело в том, что по умолчанию данная надстройка в Экселе отключена. Поэтому, если вы до сих пор её не включили, то для использования возможностей описательной статистики, придется это сделать.

1. Переходим во вкладку «Файл». Далее производим перемещение в пункт «Параметры».
2. В активировавшемся окне параметров перемещаемся в подраздел «Надстройки». В самой нижней части окна находится поле «Управление». Нужно в нем переставить переключатель в позицию «Надстройки Excel», если он находится в другом положении. Вслед за этим жмем на кнопку «Перейти…».
3. Запускается окно стандартных надстроек Excel. Около наименования «Пакет анализа» ставим флажок. Затем жмем на кнопку «OK».

После вышеуказанных действий надстройка Пакет анализа будет активирована и станет доступной во вкладке «Данные» Эксель. Теперь мы сможем использовать на практике инструменты описательной статистики.

Применение инструмента «Описательная статистика»

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

1. Переходим во вкладку «Данные» и выполняем щелчок по кнопке «Анализ данных», которая размещена на ленте в блоке инструментов «Анализ».
2. Открывается список инструментов, представленных в Пакете анализа. Ищем наименование «Описательная статистика», выделяем его и щелкаем по кнопке «OK».
3. После выполнения данных действий непосредственно запускается окно «Описательная статистика».

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

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

   • Выходной интервал;
   • Новый рабочий лист;
   • Новая рабочая книга.

   В первом случае нужно указать конкретный диапазон на текущем листе или его верхнюю левую ячейку, куда будет выводиться обработанная информация. Во втором случае следует указать название конкретного листа данной книги, где будет отображаться результат обработки. Если листа с таким наименованием в данный момент нет, то он будет создан автоматически после того, как вы нажмете на кнопку «OK». В третьем случае никаких дополнительных параметров указывать не нужно, так как данные будут выводиться в отдельном файле Excel (книге). Мы выбираем вывод результатов на новом рабочем листе под названием «Итоги».

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

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

   После того, как все указанные данные внесены, жмем на кнопку «OK».

4. После выполнения этих действий таблица с описательной статистикой выводится на отдельном листе, который был нами назван «Итоги». Как видим, данные представлены сумбурно, поэтому их следует отредактировать, расширив соответствующие колонки для более удобного просмотра.
5. После того, как данные «причесаны» можно приступать к их непосредственному анализу. Как видим, при помощи инструмента описательной статистики были рассчитаны следующие показатели:
   • Асимметричность;
   • Интервал;
   • Минимум;
   • Стандартное отклонение;
   • Дисперсия выборки;
   • Максимум;
   • Сумма;
   • Эксцесс;
   • Среднее;
   • Стандартная ошибка;
   • Медиана;
   • Мода;
   • Счет.

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

Урок: Статистические функции в Excel

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

Мы рады, что смогли помочь Вам в решении проблемы.

Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

Здравствуйте, друзья. Как часто Вам приходится обобщать большие массивы данных? Получать промежуточные итоги? Если часто, значит сводные таблицы Excel – это то, что Вам нужно срочно! Создание сводной таблицы занимает всего пару минут, а результат – как будто работали целую неделю. Заманчиво? Читаем!

Сводная таблица – это мощный инструмент Microsoft Excel, решающий многие задачи, а главное – отвечающий на многие вопросы о процессах, описанных цифрами в Вашем файле. Приведу пример. На изображении ниже – список продаж торговых точек различных регионов с детализацией по дням в течение года:

Правда же, эта таблица мало информативна и в таком виде не представляет пользы? А вот сводная таблица, сформированная из этих данных:

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

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

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

Чтобы сделать сводную таблицу на основании своих данных – выполните такую последовательность действий:

1. Установите курсор в любую ячейку таблицы
2. Нажмите на ленте: Вставка – Сводная таблица
3. Укажите расположение будущей сводной таблицы. Чтобы поместить ее на новый лист – установите галку «На новый лист». Чтобы выбрать расположение на существующих листах – выберите «На существующий лист» и в поле «Диапазон» укажите расположение верней левой ячейки сводной таблицы;

1. Нажмите Ок

Откроется пустая область сводной таблицы и меню компоновки данных. Последнее состоит из пяти окон:

1. «Выберите поля для добавления в отчет» - это заголовки всех столбцов, которые есть в таблице. Этими данными мы будем заполнять следующие 4 блока
2. Фильтры – список полей, по которым будет применяться фильтр. Эти поля появляются над сводной таблицей
3. Колонны – область, где задается, что будет содержаться в столбцах
4. Строки – область, где указывается, что будет содержаться в строках
5. Значения – задаем то, что будет отображаться или рассчитываться на пересечении строк или столбцов. То есть, основное тело таблицы

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

Если теперь мы захотим, чтобы в столбцах данные были разбиты по группам товаров. Перетянем поле «Группа товара» в «Колонны», получаем результат:

А если вдруг мы решили, что нужны данные только по первому региону, добавим поле «Регион» и в «Фильтры», над сводной таблицей появится область фильтров. Открываем раскрывающийся список в этой области и выбираем только первый регион.

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

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

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

Вы можете настраивать макет сводной таблицы в части логики построения. Выделите любую ее ячейку и найдите на ленте Конструктор – Макет. Здесь можно сделать настройки по четырем пунктам:

1. Промежуточные итоги – включить или отключить итоги для промежуточных групп внутри таблицы
2. Общие итоги – настроить расчет общих итогов по всей таблице
3. Макет отчета – способ компоновки данных для наибольшего удобства
4. Пустые строки – вставить или удалить пустые строки в конце каждой категории для улучшения восприятия данных.

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

1. Настраивать форматы данных
2. Изменять внешний вид ячеек, применять стили
3. Применять условное форматирование

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

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

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

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

Как всегда, жду Ваших вопросов и комментариев, будем становиться профессионалами вместе!

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

Формирование анализов отчетов с примерами

Расчет KPI в Excel примеры и формулы.

KPI ключевые показатели эффективности оценивают результат выполняемой работы. На основе индексов часто строится система оплаты труда. Формулы, пример таблицы и матрицы KPI.

Коэффициент парной корреляции в Excel.

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

Системы Excel-таблиц
с удобной аналитикой

Постановка начального (управленческого) учета - это создание инструментов для получения информации о фактическом состоянии дел в бизнесе. Чаще всего это система таблиц и отчетов на их основе в Excel. Они отражают удобную ежедневную аналитику о реальных прибылях и убытках, движении денег, задолженности по зарплате, расчетах с поставщиками или покупателями, себестоимости и др. Опыт показывает, что предприятию малого бизнеса достаточно системы из 4-6 простых для заполнения таблиц.

Как это работает

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

Сама работа состоит из внесения исходных данных в таблицы и занимает не более 1-2 часов в день. Для ее выполнения достаточно 1-2 уже имеющихся у вас штатных специалистов не имеющих бухгалтерских навыков.

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

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

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

ВАЖНО! Вы получаете услуги на уровне опытного финансового директора по ставке обычного экономиста.

Обучение или ведение

Мы обязательно обучаем ваших сотрудников самостоятельной работе с таблицами. Если у вас некому поручить эту работу, мы готовы вести ваш учет в режиме аутсорсинга. Это в 2-3 раза дешевле, чем нанять и содержать отдельного человека.

Гарантийная поддержка и сопровождение 24/7

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

Если вы захотите что-то изменить или добавить, специалисты сделают необходимые доработки независимо от давности оказания услуги. Обращайтесь, служба поддержки работает в режиме 24/7.

С чего начать

Позвоните +7 950 222 29 59 , чтобы задать любые вопросы и получить дополнительную информацию.

Скачать анализы и отчеты в формате Excel

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

Скачать примеры анализов и отчетов

Шаблон телефонного справочника.
Интерактивный шаблон справочника контактов для бизнеса. Удобное управление большой базой контактов.

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

Формула рентабельности собственного капитала «ROE».
Формула, которая отображает Экономический смысл финансового показателя «ROE».

Управленческий учет на предприятии — примеры таблицы Excel

Эффективный инструмент для оценки инвестиционной привлекательности предприятия.

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

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

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

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

График формулы Гордона.
Построение графика с экспоненциальной линией тренда по модели Гордона для анализа доходности инвестиций от дивидендов.

График модели Бертрана.
Готовое решение построение графика модели Бертрана, по которому можно анализировать зависимость спроса и предложения в условиях демпинга цен на дуопольных рынках.

Алгоритм расшифровки ИНН.
Формула для расшифровки Индикационного налогового номера для: России, Украины и Беларуси.

Поддерживаются все виды ИНН (10-ти и 12-ти значные номера) физических и юридических лиц, а также личный номер.

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

Табель учета рабочего времени.
Скачать табель учета рабочего времени в Excel с формулами для автозаполнения таблицы + ведение справочников для удобства работы.

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

Прогноз показателей деятельности предприятия.
Бланк прогноза деятельности предприятия с формулами и показателями: выручка, материальные затраты, маржинальный доход, накладные расходы, прибыль, рентабельность продаж (ROS) %.

Баланс рабочего времени.
Отчет по планированию рабочего времени работников предприятия по таким временным показателям как: «календарное время», «табельное», «максимально возможное», «явочное», «фактическое».

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

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

Таблица для проведения финансового анализа.
Программный инструмент выполнен в Excel предназначен для выполнения финансовых анализов предприятий.

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

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

Пример как вести управленческий учет в Excel

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

Каждая фирма сама выбирает способ ведения управленческого учета и нужные для аналитики данные. Чаще всего таблицы составляются в программе Excel.

Примеры управленческого учета в Excel

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

Справочники

Опишем учет работы в кафе. Предприятие реализует продукцию собственного производства и покупные товары. Имеют место внереализационные доходы и расходы.

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

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

Удобные и понятные отчеты

Не нужно все цифры по работе кафе вмещать в один отчет.

Пусть это будут отдельные таблицы. Причем каждая занимает одну страницу. Рекомендуется широко использовать такие инструменты, как «Выпадающие списки», «Группировка». Рассмотрим пример таблиц управленческого учета ресторана-кафе в Excel.

Учет доходов

Присмотримся поближе.

учет, отчеты и планирование в Excel

Результирующие показатели найдены с помощью формул (применены обычные математические операторы). Заполнение таблицы автоматизировано с помощью выпадающих списков.

При создании списка (Данные – Проверка данных) ссылаемся на созданный для доходов Справочник.

Учет расходов

Для заполнения отчета применили те же приемы.

Отчет о прибылях и убытках

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

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

Анализ структуры имущества кафе

Источник информации для анализа – актив Баланса (1 и 2 разделы).

Для лучшего восприятия информации составим диаграмму:

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

Скачать пример управленческого учета в Excel

По такому же принципу анализируется пассив Баланса. Это источники ресурсов, за счет которых кафе осуществляет свою деятельность.

Cтатьи затрат

Итак нам нужен бюджет проекта, который состоит из статей затрат. Для начала сформируем в Micfosoft Project 2016 список этих самых статей затрат.

Будем использовать настраиваемые поля для. Формируем таблицу подстановки настраиваемого поля типа Текст для таблицы Ресурсы, например как на этом рисунке (конечно у вас будут свои статьи затрат, данный перечень только как пример):

Рис. 1. Формирование списка статей затрат

Работа с настраиваемыми полями была описана в Учебном пособии по управлению проектами в Microsoft Project 2016 (см. раздел 5.1.2 Веха). Для удобства поле можно переименовать в Статьи Затрат. После формирования списка статей затрат их необходимо присвоить ресурсам. Для этого добавим поле Статьи Затрат в представление Ресурсы и каждому ресурсу присвоим свою статью затрат (см.

Управленческий учет на предприятии: пример таблицы Excel

Рис. 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-кривая показывает зависимость суммы затрат от сроков проекта. Так, если работы начинаются «Как можно раньше» S-кривая смещается к началу проекта, а если работы начинаются «Как можно позже» соответственно к окончанию проекта.

Рис. 7. Кривая затрат проекта в зависимости от сроков задач

Планируя задачи «Как можно раньше» (это установлено в Microsoft Project 2016 автоматически при планировании от начала проекта) мы снижаем риски нарушения сроков, но при этом необходимо понимать график финансирования проекта, иначе на проекте может быть кассовый разрыв. Т.е. затраты на наши задачи превысят доступные финансовые ресурсы, что грозит рисками остановки работ на проекте.

Планируя задачи «Как можно позже» (это установлено в Microsoft Project 2016 автоматически при планировании от окончания проекта) мы подвергаем проект большим рискам срыва сроков.

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

Рис. 8. Кривая затрат проекта в MS-Excel путем выгрузки информации из MS-Project

Составление бюджета предприятия в Excel с учетом скидок

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

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

Управленческий учет на предприятии с использованием таблиц Excel

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

Данные для составления бюджета доходов и расходов

Наша фирма обслуживает около 80-ти клиентов. Ассортимент товаров составляет около 120-ти позиций в прайсе. Она делает наценку на товары 15% от их себестоимости и таким образом устанавливает цену продажи. Такая низкая наценка экономически обоснована плотной конкуренцией и оправдывается большим товарооборотом (как и на многих других дистрибьюторских предприятий).

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

Условия и размер процентной ставки бонусной системы определяется двумя параметрами:

1. Количественная граница. Количество приобретенного конкретного товара, которое дает клиенту возможность получить определенную скидку.
2. Процентная скидка. Размер скидки – это процент, что вычисляется от суммы, на которую приобрел клиент при преодолении количественной границы (планки). Размер скидки зависит от размера количественной границы. Чем больше товара приобретено, тем больше скидка.

В годовом бюджете бонусы относятся к разделу «планирование продаж», поэтому они влияют на важный показатель фирмы – маржу (показатель прибыли в процентном соотношении от общего дохода). Поэтому важной задачей является возможность устанавливать несколько вариантов бонусов с разными границами на уровнях реализации и соответствующих им % бонусов. Нужно чтобы маржа удерживалась в определенных границах (например, не меньше 7% или 8%, вед это же прибыль фирмы). А клиенты смогут выбирать себе несколько вариантов бонусных скидок.

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

Составление бюджетов предприятия в Excel с учетом лояльности

Проект бюджета в Excel состоит из двух листов:

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

Движение денежных средств по клиентам

Структура таблицы «Продажи за 2015 год по клиенту:» на листе «продажи»:

Модель бюджета предприятия

На втором листе устанавливаем границы для достижения бонусов соответствующие им проценты скидок.

Следующая таблица – это базовая форма бюджета доходов и расходов в Excel с общими финансовыми показателями фирмы за годовой период.

Структура таблицы «Условия бонусной системы» на листе «результаты»:

1. Граница бонусной планки 1. Место для установки уровня граничной планки по количеству.
2. Бонус % 1. Место для установки скидки при преодолении первой границы. Как рассчитывается скидка для первой границы? Хорошо видно на листе «продажи». С помощью функции =ЕСЛИ(Количество > граница 1 бонусной планки[количество]; Объем продаж * процент 1 бонусной скидки; 0).
3. Граница бонусной планки 2. Более высокая граница по сравнению с предыдущей границей, которая дает возможность получить большую скидку.
4. Бонус % 2 –скидка для второй границы. Рассчитывается с помощью функции =ЕСЛИ(Количество > граница 2 бонусной планки[количество]; Объем продаж * процент 2 бонусной скидки; 0).

Структура таблицы «Общий отчет по обороту фирмы» на листе «результаты»:

Готовый шаблон бюджета предприятия в Excel

И так у нас есть готовая модель бюджета предприятия в Excel, которая является динамической. Если граничная планка бонусов находится на уровне 200, а бонусная скидка составляет 3%. Это значит, что в прошлом году клиент приобрел товара в количестве 200шт. А в конце года получит за это бонус скидку 3% от стоимости. А если клиент приобрел 400шт определенного товара, значит, он преодолел вторую граничную планку бонусов и получает скидку уже 6%.

При таких условиях изменится показатель «Маржа 2», то есть чистая прибыль дистрибьютора!

Задача руководителя дистрибьюторской фирмы выбрать самые оптимальные уровни граничных планок для предоставления клиентам скидки. Выбирать нужно так чтобы показатель «Маржа 2» находился хотя бы в приделах 7%-8%.

Скачать бюджет предприятия-бонус (образец в Excel).

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

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

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

Например, рассмотрим одни и те же финансовые расходы в разных месяцах.

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

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

1. Для начала ее необходимо полностью выделить.

1. Затем перейдите на вкладку «Вставка». Нажмите на иконку «Таблица». В появившемся меню выберите пункт «Сводная таблица».

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

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

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

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

1. Для завершения настроек нужно нажать на кнопку «OK».

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

1. На этом этапе необходимо указать, какое поле будет:
   1. столбцом;
   2. строкой;
   3. значением для анализа.

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

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

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

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

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

1. Если таблица вам не понравилась, можно попробовать построить ее немного по-другому. Для этого нужно поменять поля в областях построения.

1. Снова закрываем помощник для построения.

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

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

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

1. Перейдите на вкладку «Вставка». Затем нажмите на иконку «Таблица». В появившемся меню выберите второй пункт.

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

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

1. Можно выбрать то, что нравится больше всего.

1. Для вставки выбранного варианта достаточно нажать на кнопку «OK».

1. В итоге вы получите следующий результат.

Обратите внимание: таблица создалась на новом листе. Это будет происходить каждый раз при использовании конструктора.

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

Рассмотрим каждую из них более детально.

Нажав на кнопку, отмеченную на скриншоте, вы сможете сделать следующие действия:

• изменить имя;

• вызвать окно настроек.

В окне параметров вы увидите много чего интересного.

Активное поле

При помощи этого инструмента можно сделать следующее:

1. Для начала нужно выделить какую-нибудь ячейку. Затем нажмите на кнопку «Активное поле». В появившемся меню кликните на пункт «Параметры поля».

1. Сразу после этого вы увидите следующее окно. Здесь можно указать тип операции, которую следует использовать для сведения данных в выбранном поле.

1. Помимо этого, можно настроить числовой формат. Для этого нужно нажать на соответствующую кнопку.

1. В результате появится окно «Формат ячеек».

Здесь вы сможете указать, в каком именно виде нужно выводить результат анализа информации.

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

Вставить срез

Редактор Microsoft Excel позволяет создавать интерактивные сводные таблицы. При этом ничего сложного делать не нужно.

1. Выделите какой-нибудь столбец. Затем нажмите на кнопку «Вставить срез».
2. В появившемся окне, в качестве примера, выберите одно из предложенных полей (в будущем вы можете выделять их в неограниченном количестве). После того как что-нибудь будет выбрано, сразу же активируется кнопка «OK». Нажмите на неё.

1. В результате появится небольшое окошко, которое можно перемещать куда угодно. В нем будут предложены все возможные уникальные значения, которые есть в данном поле. Благодаря этому инструменту вы сможете выводить сумму лишь за определенные месяцы (в данном случае). По умолчанию выводится информация за всё время.

1. Можно кликнуть на любой из пунктов. Сразу после этого в поле сумма изменятся все значения.

1. Таким образом получится выбрать любой промежуток времени.

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

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

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

В качестве примера создадим небольшую таблицу с различными датами.

Затем нужно будет построить сводную таблицу.

Снова переходим на вкладку «Вставка». Кликаем на иконку «Таблица». В появившемся подменю выбираем нужный нам вариант.

1. Затем нас попросят выбрать диапазон значений.

1. Для этого достаточно выделить всю таблицу целиком.

1. Сразу после этого адрес подставится автоматически. Здесь всё очень просто, поскольку рассчитано для чайников. Для завершения построения нажмите на кнопку «OK».

1. Редактор Excel предложит нам всего один вариант, поскольку таблица очень простая (для примера больше и не нужно).

1. Попробуйте снова нажать на иконку «Вставить временную шкалу» (она расположена на вкладке «Анализ»).

1. На этот раз никаких ошибок не будет. Вам предложат выбрать поле для сортировки. Поставьте галочку и нажмите на кнопку «OK».

1. Благодаря этому появится окошко, в котором можно будет выбирать нужную дату при помощи бегунка.

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

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

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

Для этого нужно нажать на иконку «Источник данных». Затем выбрать одноименный пункт меню.

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

Действия

При помощи этого инструмента вы сможете:

• очистить таблицу;
• выделить;
• переместить её.

Вычисления

Если расчетов в таблице недостаточно или они не отвечают вашим потребностям, вы всегда можете внести свои изменения. Нажав на иконку этого инструмента, вы увидите следующие варианты.

К ним относятся:

• вычисляемое поле;

• вычисляемый объект;

• порядок вычислений (в списке отображаются добавленные формулы);

• вывести формулы (информации нет, так как нет добавленных формул).

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

При помощи этого инструмента можно настроить внешний вид рабочего пространства редактора.

Благодаря этому вы сможете:

• настроить отображение боковой панели со списком полей;

• включить или выключить кнопки «плюс/мину»с;

• настроить отображение заголовков полей.

При работе со сводными таблицами помимо вкладки «Анализ» также появится еще одна – «Конструктор». Здесь вы сможете изменить внешний вид вашего объекта вплоть до неузнаваемости по сравнению с вариантом по умолчанию.

Можно настроить:

• промежуточные итоги:
  • не показывать;
  • показывать все итоги в нижней части;
  • показывать все итоги в заголовке.

• общие итоги:
  • отключить для строк и столбцов;
  • включить для строк и столбцов;
  • включить только для строк;
  • включить только для столбцов.

• макет отчета:
  • показать в сжатой форме;
  • показать в форме структуры;
  • показать в табличной форме;
  • повторять все подписи элементов;
  • не повторять подписи элементов.

• пустые строки:
  • вставить пустую строку после каждого элемента;
  • удалить пустую строку после каждого элемента.

• параметры стилей сводной таблицы (здесь можно включить/выключить каждый пункт):
  • заголовки строк;
  • заголовки столбцов;
  • чередующиеся строки;
  • чередующиеся столбцы.

• настроить стиль оформления элементов.

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

Сразу после этого появится огромный список. Можете выбрать что угодно. При наведении на каждый из шаблонов ваша таблица будет меняться (это сделано для предварительного просмотра). Изменения не вступят в силу, пока вы не кликните на что-нибудь из предложенных вариантов.

Помимо этого, при желании, вы можете создать свой собственный стиль оформления.

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

Для этого нужно сделать следующее.

1. Кликните на треугольник около нужного поля.
2. В результате этого вы увидите следующее меню. Здесь вы можете выбрать нужный вариант сортировки («от А до Я» или «от Я до А»).

Если стандартного варианта недостаточно, вы можете в этом же меню кликнуть на пункт «Дополнительные параметры сортировки».

В результате этого вы увидите следующее окно. Для более детальной настройки нужно нажать на кнопку «Дополнительно».

Здесь всё настроено в автоматическом режиме. Если вы уберете эту галочку, то сможете указать необходимый вам ключ.

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

Описанные выше действия подходят для современных редакторов (2007, 2010, 2013 и 2016 года). В старой версии всё выглядит иначе. Возможностей, разумеется, там намного меньше.

Для того чтобы создать сводную таблицу в Экселе 2003 года, нужно сделать следующее.

1. Перейти в раздел меню «Данные» и выбрать соответствующий пункт.

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

1. После нажатия на кнопку «Далее» откроется окно, в котором нужно указать диапазон ячеек. Затем снова нажимаем на «Далее».

1. Для завершения настроек жмем на «Готово».

1. В результате этого вы увидите следующее. Здесь нужно перетащить поля в соответствующие области.

1. К примеру, может получиться вот такой результат.

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

Заключение

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

Если данного самоучителя вам недостаточно, дополнительную информацию можно найти в онлайн справке компании Microsoft.

Видеоинструкция

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