Как в экселе сделать отчет по результатам. Отчеты в MS 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% от их себестоимости и таким образом устанавливает цену продажи. Такая низкая наценка экономически обоснована плотной конкуренцией и оправдывается большим товарооборотом (как и на многих других дистрибьюторских предприятий).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Настройка сводной таблицы

Подготовка

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

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

Значения в сводной таблице

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

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

Удаление сводной таблицы

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

Теперь вы можете вставить сводную таблицу в электронную таблицу в Excel в Интернете.

Работа со списком полей сводной таблицы

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

Работа с значениями сводной таблицы

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

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

Удаление сводной таблицы

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте

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

Немного теории

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

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

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

Плюсы использования такого вида группировки данных:

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

Обучение работе со сводными таблицами в Excel не займет много времени и может основываться на видео.

Пример создания сводной таблицы Excel – алгоритм для чайников

Ознакомившись с базовыми теоретическими нюансами про сводные таблицы в Excel, давайте перейдем к применению их на деле. Для старта создания сводной таблицы в Excel 2016, 2010 или 2007 необходимо установить программное обеспечение. Как правило, если вы пользуетесь программами системы Microsoft Office, то Excel уже есть на вашем компьютере.

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

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

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

Рассмотрим детальней самостоятельное заполнение пунктов диалогового окна.

Первую строку не оставляем пустой, иначе программа выдаст ошибку. Если есть источник, с которого планируете переноситься данные, то выберите его в пункте «Использовать внешний источник данных». Под внешним источником подразумевается другая книга Excel или набор моделей данных из СУБД.

Заранее озаглавьте каждый столбик

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

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

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

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

Обратите внимание, как расположились эти данные в нижней области панели настройки.

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

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

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

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

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

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

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

Теперь мы видим, что в отдел «Аксессуары» поступило товаров на сумму 267660 рублей, при этом самый дорогостоящий имеет цену 2700 рублей.
Область «Фильтры» позволяет установить критерий отбора записей. Добавим поле «Дата поступление», просто поставив около него галочку.

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

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

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

Также можно выбрать и значения для отдела.

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

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

Обновление данных в сводной таблице в Excel

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

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

Если сводная таблица в Excel не нужна, то стоит разобраться, как её удалить. Это не составит большого труда. Выделите все составляющие вручную, или используя сочетание клавиш «CTRL+A». Далее нажмите клавишу «DELETE» и поле будет очищено.

Как в сводную таблицу Excel добавить столбец или таблицу

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

Перейдите на вкладку «Анализ» и откройте источник данных.

Excel сам все предложит.

Обновите и вы получите новый перечень полей в области настройки.

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

Как сделать сводную таблицу в Excel из нескольких листов

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

Выберите все команды.

И найдите мастер сводных таблиц Excel, кликните по нему, затем на «Добавить» и ОК.

Значок появится сверху.

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

Нам нужно несколько полей, а не одно.

На следующем этапе выделите первый диапазон и нажмите кнопку «Добавить». Затем переключитесь на другой лист (щелкните по его названию внизу) и снова «Добавить». У вас будут созданы два диапазона.

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

Щелкайте «Далее» и создавайте на новом листе.

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

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

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

Как видите, у нас одно значение в соответствующей области.

Изменение структуры отчёта

Мы поэтапно разобрали пример, как создать сводную таблицу Exce, а как получить данные другого вида расскажем далее. Для этого мы изменим макет отчета. Установив курсор на любой ячейке, переходим во вкладку «Конструктор», а следом «Макет отчета».

Вам откроются на выбор три типа для структуризации информации:

Сжатая форма

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

Структурированная форма

Все показатели подаются иерархично: от малого к большому.

Табличная форма

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

Остановив выбор на подходящем макете, вы закрепляете внесенные коррективы.

Итак, мы рассказали, как составить поля сводной таблицы MS Excel 2016 (в 2007, 2010 действуйте по аналогии). Надеемся, эта информация поможет вам осуществлять быстрый анализ консолидированных данных.

Отличного Вам дня!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Справочники

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

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

Учет доходов

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

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