Сложная сводная таблица в excel как сделать

Построение отчета сводной таблицы

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

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

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

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

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

В динамике добавление полей выглядит так:

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

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

И еще один пример. Проанализируем помесячные продажи в разрезе моделей (даты отправляются в строки, а продажи в штуках и деньгах — в значения):

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

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

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

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

Итак, запускаем Excel… и загружаем такой список…

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

Выделите любую ячейку из этого списка:

Затем на вкладке Insert (Вставка) выберите команду PivotTable (Сводная таблица):

Появится диалоговое окно Create PivotTable (Создание сводной таблицы) с двумя вопросами для Вас:

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

Так как на предыдущем шаге мы уже выбрали одну из ячеек списка, то для создания сводной таблицы будет выделен весь список автоматически. Заметьте, что мы можем выбрать другой диапазон, другую таблицу и даже какой-нибудь внешний источник данных, например, таблицу базы данных Access или MS-SQL. К тому же нам необходимо выбрать, где разместить новую сводную таблицу: на новом листе или на одном из существующих. В данном примере мы выберем вариант – New Worksheet (На новый лист):

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

Как только мы кликнем по любой ячейке в сводной таблице, появится ещё одно диалоговое окно: PivotTable Field List (Поля сводной таблицы).

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

Область Values (Значения), вероятно, самая важная из четырёх. То, какой заголовок помещён в эту область, определяет, по каким данным будут подводиться итоги (сумма, среднее, максимум, минимум и т.д.) Это, почти всегда, численные значения. Отличный кандидат на место в этой области – данные под заголовком Amount (Стоимость) нашей исходной таблицы. Перетащим этот заголовок в область Values (Значения):

Обратите внимание, что заголовок Amount теперь отмечен галочкой, а в области Values (Значения) появилась запись Sum of Amount (Сумма по полю Amount), указывающая на то, что столбец Amount просуммирован. Если мы посмотрим на саму сводную таблицу, то увидим сумму всех значений из столбца Amount исходной таблицы

Если мы посмотрим на саму сводную таблицу, то увидим сумму всех значений из столбца Amount исходной таблицы.

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

Обратимся к исходным данным и попробуем определить один или несколько столбцов, которые можно использовать, чтобы раздробить эту сумму. Например, мы можем сформировать нашу сводную таблицу таким образом, чтобы итоговая сумма продаж подсчитывалась для каждого продавца по отдельности. Т.е. в нашу сводную таблицу добавятся строки с именем каждого продавца компании и его итоговой суммой продаж. Чтобы достичь такого результата, достаточно перетащить заголовок Salesperson (Торговый представитель) в область Row Labels (Строки):

Становится интересней! Наша сводная таблица начинает обретать форму…

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

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

Где применять

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

Расчет KPI

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

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

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

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

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

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

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

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

http://support.office.com/ru-ru/article/%D0%9F%D1%80%D0%B5%D0%BE%D0%B1%D1%80%D0%B0%D0%B7%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D0%B5-%D1%8F%D1%87%D0%B5%D0%B5%D0%BA-%D1%81%D0%B2%D0%BE%D0%B4%D0%BD%D0%BE%D0%B9-%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D1%8B-%D0%B2-%D1%84%D0%BE%D1%80%D0%BC%D1%83%D0%BB%D1%8B-%D0%BB%D0%B8%D1%81%D1%82%D0%B0-4ad90af4-20d4-4ad3-9b87-b3748bcf268d

http://clubtk.ru/forms/deloproizvodstvo/kak-ispolzovat-svodnye-tablitsy-excel-v-kdp

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

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

Нажмите на сводную таблицу.
Нажмите вкладку ANALYZE на ленте.
Нажмите Обновить в группе данных.

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

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

Обновить — Получить последние данные из источника, подключенного к активной ячейке.

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

Свойства соединения — установка свойств обновления для соединений книги.

Что такое сводная таблица?

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

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

Например, у вас множество записей в электронной таблице с цифрами продаж шоколада:

И каждый день сюда добавляются все новые сведения. Одним из возможных способов суммирования этого длинного списка чисел по одному или нескольким условиям является использование формул, как было продемонстрировано в руководствах по функциям СУММЕСЛИ и СУММЕСЛИМН.

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

Вот посмотрите сами.

Этот скриншот демонстрирует лишь несколько из множества возможных вариантов анализа продаж. И далее мы рассмотрим примеры построения сводных таблиц в Excel 2016, 2013, 2010 и 2007.

Основы: Как Добавить Ссылку между Листами в Excel

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

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

Может вы будете использовать один лист в документе для всех ваших формул, которые обрабатывают данные, а данные будете распределять по отдельным листам.

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

1. Создайте в Excel новую Формулу

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

Я открываю запись знаком =, и затем щелкаю по первой ячейке на текущем листе, чтобы создать первую часть формулы. Затем, я напечатаю знак +, чтобы добавить значение из второй ячейки в этой формуле.

Пока не завершайте ввод формулы и не жмите Enter! Вам нужно оставить формулу открытой перед тем как вы переключитесь на другой Лист.

2. Переключитесь между Листами в Excel

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

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

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

Заметьте на картинке ниже, что для ссылки на ячейку на другом Листе, Excel добавил надпись «Sheet2!B3», которая ссылается на ячейку В3 на листе с названием Sheet2. Вы можете написать это вручную, но когда в щелкаете по ячейке, Excel делает эту надпись автоматически за вас.

3. Закройте Excel формулу

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

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

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

От сводной таблицы больше пользы, если она динамическая. То есть при внесении новых данных в исходный диапазон поля отчета можно обновить. Как это сделать?

Когда данные внесены в исходную таблицу, переходим на лист со сводным отчетом и щелкаем в любом его месте правой кнопкой мыши. В открывшемся меню выбираем «Обновить».
Активизируем нужное поле сводного отчета – становится доступен инструмент «Работа со сводными таблицами». Открываем вкладку «Параметры». В группе «Данные» нажимаем кнопку «Обновить».
Выделить сводную таблицу или отдельное поле, нажать сочетание клавиш Alt + F5.

Как настроить автоматическое обновление сводной таблицы в Excel:

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

Закрыть окно, нажав кнопку ОК.

Еще один вариант:

Открыть лист со сводным отчетом. На вкладке «Разработчик» нажать кнопку «Запись макроса».
Выполнить вручную обновление сводной таблицы – остановить запись. Нажать на кнопку «Макросы». Выбрать из доступных макросов записанный – «выполнить».

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

Как создать новый лист в Excel

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

Как создать лист в Эксель из строки состояния

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

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

Щелкните по кнопке «+». Произойдет создание листа в Эксель. По умолчанию, новый лист добавляется в конец вкладок и получает название «Лист n».

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

Как вставить новый лист в Excel из контекстного меню

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

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

Поместить курсор на ярлычок существующего листа, перед которым необходимо вставить новый лист.
Вызвать контекстное меню правой кнопкой мыши, выбрать строку «Вставить…».
В окне «Вставка» выбрать закладку «Общие», в том случае, когда необходимо вставить пустую таблицу, график, диаграмму и прочее. Если требуется создание таблицы на основе шаблона Excel, то надо открыть вкладку «Решение». Нам нужно вставить новый лист в Excel, поэтому во вкладке «Общие», выбираем «Лист».
Нажимаем кнопку «ОК» и видим, что добавился новый лист с названием «Лист2».

Как в Экселе добавить лист из ленты

Разберемся как в Экселе добавить лист из ленты.

Переходим во вкладку «Главная». Здесь нас интересует блок с названием «Ячейки».
Нажимаем кнопку «Вставить» и выбираем пункт «Вставить лист».

Таким образом, можно в Эксель добавить новый лист

Обратите внимание, что лист будет добавлен перед листом, который открыт в данный момент

Как создать лист в Excel горячими клавишами

На последок оставил мой самый любимый способ создать лист в Excel.

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

«Shift» + «F11»

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

Одновременная вставка нескольких листов

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

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

Сколько было выделено листов, столько новых листов и будет добавлено в книгу Excel. Поэтому, если нужно одновременно вставить 10 листов, а у нас по умолчанию имеется 3 созданных, выделяем 3 имеющихся, добавляем. Теперь у нас 6 листов. Далее, выделяем 4 листа и снова добавляем.

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

Об источнике данных

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

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

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