Как употреблять сводные таблицы Excel в КДП

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

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

Узнаем общую сумму продаж по каждой группы.

Проверим остатки по каждой группы продукта и так дальше.

Как употреблять

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

• в нем нет объединенных ячеек;
• нет пустых строк и столбцов;
• все столбцы имеют заглавия.

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

Создаем базу Excel с помощью функции «Вставка» — «Таблица» — «Сводная таблица».

Получим последующий итог:

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

А сейчас добавим типы продаж.

Как создать вычисления

В отчет можно добавить вычисляемые поля. Для этого нужно поставить курсор в всякую ячейку Еxcel, избрать вкладку «Анализ» — «Вычисления» — «Поля, элементы и наборы» — «Вычисляемое поле». В показавшемся окне зададим имя поля и формулу для вычислений. В нашем случае заработная плата составляет 5% от выручки, и формула смотрится последующим образом:

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

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

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

Удаляем базу, выделив ее и нажав кнопку Delete.

Где использовать

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

Расчет KPI

Современные CRM-системы разрешают выгрузить все нужные отчеты в готовом виде. Но что созодать тем, кто спец софт не употребляет? Остается возможность как в Экселе создать сводную таблицу, так и посчитать нужные характеристики в ручном режиме. 2-ой метод кажется проще, но он не постоянно комфортен. Если начальные данные представлены в виде перечня подобного вида, употреблять объединенные реестры полностью уместно, потому что это существенно упрощает следующую работу.

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

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

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

Отчет по персоналу

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

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

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

Работа со сводными таблицами в Excel на примерах

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

Начальный материал – таблица с несколькими десятками и сотками строк, несколько таблиц в одной книжке, несколько файлов. Напомним порядок сотворения: «Вставка» – «Таблицы» – «Сводная таблица».

А в данной статье мы разглядим, как работать со сводными таблицами в Excel.

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

1-ый шаг – выгрузить информацию в программку Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заглавия столбцам, убираем пустые строчки и т.п.).

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

Мы просто создаем сводный отчет на базе данных в нескольких спектрах консолидации.

Еще труднее создать сводную таблицу на базе различных по структуре начальных таблиц. К примеру, таковых:

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

Мастер сводных таблиц при таковых начальных параметрах выдаст ошибку. Потому что нарушено одно из основных критерий консолидации – однообразные наименования столбцов.

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

1. В ячейке-мишени (там, куда будет переноситься таблица) ставим курсор. Пишем = — перебегаем на лист с переносимыми данными – выделяем первую ячейку столбца, который копируем. Ввод. «Размножаем» формулу, протягивая вниз за правый нижний угол ячейки.
2. По такому же принципу переносим остальные данные. В итоге из 2-ух таблиц получаем одну общую.
3. Сейчас сделаем сводный отчет. Вставка – сводная таблица – указываем спектр и пространство – ОК.

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

Покажем, например, количество проданного продукта.

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

Детализация инфы в сводных таблицах

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

Как обновить данные в сводной таблице Excel?

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

Курсор должен стоять в хоть какой ячейке сводного отчета.

Правая клавиша мыши – обновить.

Чтоб настроить автоматическое обновление сводной таблицы при изменении данных, делаем по аннотации:

1. Курсор стоит в любом месте отчета. Работа со сводными таблицами – Характеристики – Сводная таблица.
2. Характеристики.
3. В открывшемся диалоге – Данные – Обновить при открытии файла – ОК.

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

Добавим в сводную таблицу новейшие поля:

1. На листе с начальными данными вставляем столбец «Реализации». Тут мы отразим, какую выручку получит магазин от реализации продукта. Воспользуемся формулой – стоимость за 1 * количество проданных единиц.
2. Перебегаем на лист с отчетом. Работа со сводными таблицами – характеристики – поменять источник данных. Расширяем спектр инфы, которая обязана войти в сводную таблицу.

Если б мы добавили столбцы снутри начальной таблицы, довольно было обновить сводную таблицу.

Опосля конфигурации спектра в сводке возникло поле «Реализации».

Как добавить в сводную таблицу вычисляемое поле?

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

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

{Инструкция} по добавлению пользовательского поля:

1. Определяемся, какие функции будет делать виртуальный столбец. На какие данные сводной таблицы вычисляемое поле обязано ссылаться. Допустим, нам необходимы остатки по группам продуктов.
2. Работа со сводными таблицами – Характеристики – Формулы – Вычисляемое поле.
3. В открывшемся меню вводим заглавие поля. Ставим курсор в строчку «Формула». Инструмент «Вычисляемое поле» не реагирует на спектры. Потому выделять ячейки в сводной таблице не имеет смысла. Из предполагаемого перечня избираем группы, которые необходимы в расчете. Избрали – «Добавить поле». Дописываем формулу подходящими арифметическими действиями.
4. Нажимаем ОК. Возникли Остатки.

Группировка данных в сводном отчете

Для примера посчитаем расходы на продукт в различные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel производится последующим образом. Для примера создадим ординарную сводную по дате поставки и сумме.

Щелкаем правой клавишей мыши по хоть какой дате. Избираем команду «Группировать».

В открывшемся диалоге задаем характеристики группировки. Исходная и конечная дата спектра выводятся автоматом. Избираем шаг – «Годы».

Получаем суммы заказов по годам.

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

Делаем сводную таблица в Excel — пошаговая {инструкция}

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

Подготовка

Чтоб сделать сводную таблицу, следует осознавать, что она представляет собой инструмент для работы с данными. Для начальных данных нужно сделать отдельную таблицу в Microsoft excel.

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

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

Создание

Сделать сводную таблицу можно в программках разных версий (2010-2016). Рассматриваемая {инструкция} сотворена на базе Майкрософт Эксель 2013.

В запущенной программке нужно перейти во вкладку «Вставка» и избрать пункт «Сводная таблица».

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

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

Направьте внимание! Данным методом нереально консолидировать (соединить) несколько таблиц.

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

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

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

Направьте внимание! Если перед сиим шагом произвести выделение таблицы, то все поля заполнятся автоматом.

Наполнение

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

Таблица может содержать хоть какое количество данных. Отмечать желаемые пункты следует в графе «Поля для прибавления».

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

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

В показавшемся окне можно избрать хотимый тип операции.

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

Трудности при работе

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

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

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

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

Для того, чтоб получить обновление сходу всей конструкции, требуется зайти во вкладку «Анализ» и избрать строчку «Обновить все».

Добавление строк и столбцов

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

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

Система собранная с нескольких листов

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

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

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

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

Опосля рекомендуется избрать пункт «Сделать одно поле».

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

Крайний шаг, выбор расположения новейшего объекта.

Опосля проделанной работы на новейшей страничке покажется непростая сводка.

Удаление

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

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

Поделись с друзьями!