Как сделать сводную таблицу в Excel по менеджерам?

Если это разовая процедура, то вы сначала создадите табл. 2, а потом на её основе создадите табл. 3. Но как быть, если вы хотите поддерживать табл. 3 в актуальном состоянии при изменении исходных данных?

Мне не известны стандартные методы в Excel, позволяющие это сделать. К сожалению, опция в мастере сводных таблиц[1] «создать таблицу на основе данных, находящихся… в другой сводной таблице», не подходит:

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

• Стандартным образом создайте сводную таблицу на основе этих данных, а затем запустите мастер сводных таблиц, выберите опцию «создать таблицу на основе данных, находящихся… в другой сводной таблице», нажмите «Далее»:
• На шаге 2 выберите на основе, какой сводной таблицы вы создадите новую таблицу:
• Видно, что вторая сводная «опирается» на те же данные, что и первая:
• Можете поэкспериментировать, и убедиться, что набор данных второй сводной таблицы не зависит от вида первой сводной таблицы (то есть от того, какие поля и как мы выбрали в первой таблице), а зависит только от исходных данных (колонки А–D).

Фактически мы создали копию первой сводной таблицы. Так что стандартные методы Excel для решения нашей задачи не подходят. Применим маленькие хитрости.

Пример приведен в Excel-файле на листе «хитрость». Для начала создадим именованный динамический диапазон на основе исходных данных – «исх1» с использованием функции СМЕЩ (как это делать можно посмотреть здесь). Именованный диапазон  избавит нас от проблем при добавлении исходных данных, и позволит актуализировать все сводные  таблицы простым нажатием кнопки «Обновить»:

1. Создавая сводную таблицу, укажите, что исходные данные – это диапазон с именем «исх1»:
2. Для сводной таблицы отключите общие итоги:
3. Создайте именованный диапазон для сводной таблице, также с помощью функции СМЕЩ:

Вот зачем мы отключили итоги – чтобы они «не лезли» в этот диапазон!

Запустите мастер сводных таблиц (из панели быстрого доступа) и выберите «Создать таблицу на основе данных, находящихся… в списке или базе данных MS Excel», нажмите «Далее»:

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

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

Источник: http://baguzin.ru/wp/excel-svodnaya-tablitsa-na-osnove-drugoj-svo/

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

Если вы ещё не знакомы со сводными таблицами, то начните с этой статьи.

Проблема

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

Это легко может сделать MS Access, а в Excel для этого приходилось всегда использовать формулы типа ВПР (VLOOKUP).

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

Пример

В нашем примере мы располагаем 4-мя таблицами: Заказы, Строки заказов, Товары, Клиенты.

Таблица заказов:

Таблица Строк заказов:

Таблица Товаров

Таблица Клиентов

Исходные таблицы оформлены в виде умных таблиц: Orders, OrderLines, Goods и Clients.

Вполне очевидно, что таблицы Orders и OrderLines могут быть связаны по полю ID_Заказа, таблицы Orders и Clients — по полю ID_клиента, таблицы OrderLines и Goods — по полю ID_товара.

Создание модели данных

Создадим сводную таблицу на основе любой из имеющихся таблиц.

1. Выбираем в меню Вставка пункт Сводная таблица. В указанном диалоговом окне мы видим опцию Добавить эти данные в модель данных. Мы могли бы её выбрать, но я рекомендую другой, более удобный способ. Просто нажмите OK.
2. В появившейся панеле Поля сводной таблицы вы видите надпись ДРУГИЕ ТАБЛИЦЫ…
3. Нажмём её. Появится такой вопрос:
4. Отвечаем Да и видим, что в список полей добавились все наши таблицы:
5. Если вы начнёте выбирать поля, то через некоторое время в списке полей появится кнопка СОЗДАТЬ…
6. Нажмём её и создадим связи между нашими таблицами. Так создаётся связь между таблицей Orders и OrderLines. Обратите внимание, что Excel умеет создавать связь типа «один к одному» или «один ко многим». Причём первой надо указывать таблицу, где «много», в противном случае Excel ругается и предлагает поменять их местами.
7. Аналогично создаём другие связи.
8. В диалоговое окно Управление связями можно попасть через ленту АНАЛИЗ команда Отношения
9. Чтобы видеть больше полей на панеле Поля сводной таблицы, можно через кнопку Сервис (в виде шестерёнки) выбрать это представление:
10. Результат будет таким:
11. В результате все наши таблицы теперь связаны и вы можете сформировать, к примеру, такой отчёт:

Источник: http://perfect-excel.ru/publ/excel/svodnye_tablicy/svodnaja_tablica_na_osnove_dvukh_i_bolee_svjazannykh_tablic/5-1-0-67

Урок 46. Как создать сводную таблицу в Excel?

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

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

Еще кое-что: статья написана не только для тех, кто занимается интернет-маркетингом, но и для людей, занимающимся другими сферами деятельности. Поэтому этот урок я разделю на две части:

1. Выгрузка отчета из Метрики;
2. Создание сводной таблицы;

Тем, кто не интересуется интернет-маркетингом первую часть можно не читать!

Выгрузка отчета из Метрики

Итак. Давайте выгрузим отчет о расходах рекламных кампаний Директ из Яндекс.Метрики в XLSX-файл. Для этого перейдите в отчет “Директ-расходы”: 

Затем нажмите на небольшую кнопку “Экспорт” в правом верхнем углу отчета и выберите “XLSX” из графы “Данные из таблицы”: 

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

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

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

• Убираем все объединенные ячейки;
• Убираем пустые ячейки;

После всех подготовок исходная таблица примет вид: 

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

Ну-с начнем создавать:

1. Перейдите во вкладку “Вставка”: 
2. Во вкладке “Вставка” найдите кнопку “Сводная таблица” (крайняя с лева). Нажмите на нее: 
3. В появившемся окошке укажите куда поместить отчет: на новый лист или на существующий. Нажмите “ОК”: 

И вот перед вами конструктор сводной таблицы. Наша задача – перетащите все необходимые данные таблицы в определенные поля сводной таблицы. Делается это в области с права: 

Как видите здесь четыре области:

• Фильтр отчета. Предназначается для фильтрации данных;
• Названия строк. Сюда переносятся анализируемые поля. Например, рекламная кампания, объявление и ключевая фраза;
• Названия столбцов. Здесь указываются значения. Подставлять ничего не нужно, данные из поля значения перенесутся автоматически;
• Значения. Сюда переносятся данные, с помощью которых анализируются поля, перенесенные в область “Названия строк”;
• Кажется доходчиво объяснил.
• Итак, вот пример моей сводной таблицы, где я просматриваю расходы, переходы по каждой кампании, по каждому объявлению, по каждой фразе: 
• В области “Названия строк” сохранена изначальная иерархия, то есть сперва кампании, потом объявления, затем ключевые фразы. Благодаря этому сводная таблица принимает следующий вид: 
• Как видите, все данные вложены по порядку. А вся таблица выглядит так: 
• Как создать сводную таблицу в Excel вы теперь знаете, осталось только сделать ее восприятие проще.

Упрощаем восприятие данных таблицы

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

Сделаем это следующим образом:

1. Во вкладке “Конструктор” выберите любой, понравившийся вам, стиль оформления таблицы:
2. Затем переименуйте столбцы. Дважды щелкните по столбцу и дайте название;
3. Присвойте денежный формат столбцу с расходами. Нажмите правой кнопкой мыши на ячейке с данными о расходах и выберите “Числовой формат”, затем в списке с лева найдите пункт “Денежный формат”:
4. Указываем гистограмму в каждой ячейке столбца “Расходы”. Выделите ячейки столбца “Расходы” и на вкладке “Главная” найдите кнопку “Условную форматирование”, нажмите на нее. В выпадающем списке выберите пункт “Гистограмма”:

Результат:

Теперь вы наглядно видите на какую кампанию больше всего тратите средств. В данном примере на кампанию “Котлы – Поиск” больше всего тратится денежных средств. Это может означать только одно – необходимо оптимизировать цену клика посредством увеличения CTR. Этот процесс называется ведение рекламных кампаний.

Источник: https://context-up.ru/kak-sozdat-svodnuy-tablicu-v-excel/

Глава 7. Сводные таблицы

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

Скачайте файл svodnie-tablici. На листе данные этого файла находятся  двести записей о продажах товаров (на практике число анализируемых записей обычно на один-два порядка больше). Каждая запись представляет собой строчку в таблице и содержит информацию:

• Дата совершения продажи;
• Наименование товара;
• Наименование покупателя товара;
• Сумма сделки.

Относительно этих данных может возникнуть множество вопросов:

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

На все эти вопросы помогают ответить сводные таблицы.

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

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

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

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

Поля сводной таблицы

• На новом листе слева появилась заготовка сводной таблицы, справа окно со списком полей и четырьмя окошками: фильтр отчёта, названия столбцов, названия строк, суммарные значения.

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

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

Можно немного доработать сводную таблицу. Выделите финансовые данные таблицы (диапазон B5:E9), задайте этим ячейкам финансовый формат, суммы стали нагляднее.

Выделите ячейку Е5 (общий итог – покупатель Автоматика), нажмите меню Параметры, в разделе Сортировка – большую кнопку Сортировка, в открывшемся окне – Параметры сортировки – По убыванию, ОК. Теперь и производители, и товары отсортированы по убыванию, ответы на первые три вопроса получены.

Как правило, данные анализируются за определённый период или несколько периодов. Структурируем данные по временным периодам. Например, можно узнать динамику продаж продуктов по годам.

Для этого нужно отметить поле Год и перенести это поле в окошко Названия строк, убрав оттуда поле Покупатель обратно в окно списка полей.

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

Одно окошко было пока обойдено вниманием: Фильтр отчёта. Перенесите туда поле Покупатель. В ячейках А1-А2 появился фильтр выбора значений этого поля, это полезно для более детального анализа. Добавив простую диаграмму-график на основе данных сводной таблицы, получаем хороший аналитический инструмент: выбирая покупателя, можно смотреть динамику продаж по каждому товару.

Источник: http://finexcel.ru/glavnaia/uchebnik-excel/svodnye-tablicy/

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

Сводная таблица MS Excel — это мощный инструмент анализа данных.

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

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

Требования к исходным данным.

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

Давайте разберем в чем, собственно говоря, «правильность» этой таблицы? Правильность заключается в том, что:

• В каждом столбце содержатся только однотипные данные, в столбце А только Даты, в столбце В только Документы, в столбце С только Клиенты, Деньги в Деньгах, Поставщики в Поставщиках, Категории Товаров в Категориях и так далее… В такой таблице мы можем очень легко использовать Фильтр;
• Столбцы с однотипными данными не повторяются;
• В таблице нет никаких итоговых строк, только «чистые данные»;
• В таблице нет пустых ячеек в текстовых данных, в каждой строчке есть название и Клиента, и Товара, и Поставщика, и Менеджера и так далее…

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

А вот пример «неправильной таблицы», из которой, хоть ты тресни, а Сводная таблица не построится, а если и построится, то работать с данными будет совершенно невозможно… Такими таблицами нас обычно «радуют» наши «любимые» учетные системы, которые выдают нам их в виде отчетов, совершенно не приспособленных для дальнейшего анализа:

В чем ее «неправильность»? Правильно, в одном столбце и Клиенты, и Товарные категории, и Наименование товара… Чтобы из такой таблицы построить Сводную, нужно еще помучиться, а именно растащить по разным столбцам разнотипные данные… Как это сделать читайте в статье «Как быстро построить сводную таблицу из отчета 1C или SAP?».

Вот еще один пример «Неправильного массива»:

Тут целый букет «неправильностей»:

• Во-первых, столбцы «Поставщик» и «Категория» имеют пустые ячейки, соответственно мы не можем воспользоваться фильтром;
• Во-вторых, практически в каждом столбце есть строки «Итого…», они совершенно не нужны для построения Сводной таблицы, более того они будут только мешать;
• В-третьих, столбец «Общий итог» так же не нужен;
• В-четвертых, однотипные данные, а именно «Деньги», стоят аж в трех столбцах: «янв», «фев» и «мар», что значительно затруднит построение Сводной таблицы, а значит, нам придется с ними, что-то делать…

Но это все для отдельного разговора, если вы хотите научиться быстро, преобразовывать такие «кривые таблицы» в «правильные массивы» читайте статьи: «Как быстро построить сводную таблицу из отчета 1C или SAP?» и «Как быстро преобразовать таблицу в массив для сводной таблицы?»

Собственно, построение Сводной таблицы:

Берем «правильный массив», курсором встаем в любую ячейку массива, в главном меню выбираем вкладку «Вставка», в левом углу, в разделе «Таблицы» нажимаем кнопку «Сводная таблица»:

В открывшемся диалоговом окне «Создание сводной таблицы» нажимаем «ОК»:

MS Excel создаст новый лист, на котором обозначит место вставки Сводной таблицы, а справа выведет окно настройки полей Сводной таблицы, в котором вы увидите все названия столбцов своего массива:

Начинаем настройку Сводной таблицы. Какой разрез мы хотим получить? Товарные категории в разрезе Менеджеров — пожалуйста. Наводим мышку на поле «Категория», нажимаем на нем левой кнопкой мышки и тащим его в поле «СТРОКИ». Поле «Менеджер» тащим в «КОЛОННЫ», «Сумму» тащим в «ЗНАЧЕНИЯ»:

Источник: https://e-xcel.ru/index.php/svodnye-tablitsy/kak-postroit-svodnuyu-tablitsu

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

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

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

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

На помощь приходит один из самых мощных инструментов Excel 2007 — сводная таблица.

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

У Вас появится новый лист Excel, а в меню появится два новых пункта: Конструктор и Параметры. Справа Вы увидите окно, в котором вверху будут прописаны названия столбцов таблицы, а внизу четыре поля: Фильтр отчета, Названия столбцов, Названия строк и Значения.

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

По умолчанию сортировки идет по фамилиям менежденов. Если Вы хотите сделать сортировку по продажам, нажмите на раскрывающийся список левого заголовка, и выберите пункт Дополнительные параметры сортировки. Укажите параметры сортировки, например, по убыванию по полю Сумма по полю продано, и нажмите ОК.

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

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

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

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

Рассмотрим теперь возможность фильтрации данных в сводной таблице Excel. Поставьте галочку напротив строки Склад. И перетащите эту строку из области Названия строк в область Фильтр отчета. Вверху в сводной таблице Excel появится строка Склад, в которой будет возможность выбора: все, склад 001 или склад 002.

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

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

Источник: http://pro444.ru/azbuka/sozdanie-svodnyx-tablic-v-excel-2007.html

Как создать сводную таблицу в Excel: подробно об эффективном инструменте для вычислений

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

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

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

Для чего нужна сводная таблица

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задумываясь над тем, как создать сводную таблицу в Excel, ознакомьтесь с пошаговой инструкцией:

Выделите ячейки, содержащие исходные данные, которые лягут в основу сводной таблицы.

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

1. В разделе «Вставка» выберете значение «Сводная таблица».
2. Далее в категории «Выберите данные для анализа» поставьте галочку около «Выбрать таблицу или диапазон».
3. В поле Таблица или диапазон установите диапазон ячеек.
4. В пункте «Укажите, куда следует поместить отчет сводной таблицы» выберете «На новый лист». В этом случае таблица откроется в новом документе. Вариант «На существующий лист» предполагает, что месторасположение таблицы будет в ранее созданном документе.
5. «ОК».

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

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

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

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

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

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

Процесс объединения нескольких таблиц в одну не вызовет затруднений, если делать все поэтапно, следуя инструкции:

1. Для начала находим «Мастер сводных таблиц и диаграмм»: Панель быстрого доступа – затем «Другие команды» → далее «Настройки» → и наконец «Мастер сводных таблиц и диаграмм».
2. В первой таблице нажимаем «Мастер сводных таблиц и диаграмм», далее отмечаем, что хотим создать таблицу в «нескольких диапазонах консолидации», что означает, что мы будем объединять информацию, находящуюся в разных местах. Выбираем вид «Сводная таблица», жмем «Далее».
3. На следующем этапе используем команду «Создать поля» и идем «Далее».
4. Указываем диапазон данных, на основе которых будет строиться таблица, нажимаем «Добавить», проделываем то же самое со вторым диапазоном.
5. Далее в списке выбираем первый диапазон данных и ставим галочку у единицы. Так мы обозначаем первое поле сводной таблицы и озаглавливаем его «Магазин 1». То же самое проделываем со вторым диапазоном данных, только ставим галочку напротив цифры «2» и даем название полю «Магазин 2». Жмем «Далее».
6. Выбираем, где будет располагаться сводная таблица, на новом листе или существующем.

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

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

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

Использование фильтров в сводной таблице.

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

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

Со временем возникает необходимость вносить изменения в исходные данные. Нажмите правую кнопку мышки и выберете «Обновить».

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

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

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

Кеш сводной таблицы Excel 2016: достоинства и недостатки

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

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

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

Метод борьбы с этой проблемой довольно прост – нужно самостоятельно копировать и вставлять данные.

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

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

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

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

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

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

Источник: https://rdv-it.ru/news/sozdat-svodnuyu-tablitsu-excel/

Финансы в Excel

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

Термины многомерного анализа данных

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

Многомерные данные, измерения

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

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

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

• виды или группы товаров;
• бренды или категории товаров;
• периоды (месяц, квартал, год);
• покупатели или группы покупателей;
• регионы продаж
• и т.п.

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

• объем продаж;
• цена продажи;
• индивидуальная скидка
• и т.п.

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

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

OLAP

Аббревиатура OLAP (online analytical processing) в дословном переводе звучит как «аналитическая обработка в реальном времени». Определение не очень конкретное, под него можно подвести практически любой отчет любого программного продукта.

По смыслу OLAP подразумевает технологию работы со специальными отчетами, включая программное обеспечение, для получения и анализа как раз многомерных структурированных данных. Одним из популярных программных продуктов, реализующих OLAP-технологии, является SQL Server Analysis Server.

Некоторые даже ошибочно считают его единственным представителем программной реализации данной концепции.

Виртуальный куб данных

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

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

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

Сводная таблица

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

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

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

Начиная с версии 2000 (9.0), Excel поддерживает также графическую форму отображения многомерных данных – сводная диаграмма (Pivot Chart).

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

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

Еще раз хочется обратить внимание, что сводная таблица Excel предназначена исключительно для анализа данных без возможности редактирования информации. Ближе по смыслу было бы повсеместное употребление термина «сводный отчет» (Pivot Report), и именно так этот интерфейс и назывался до 2000го года. Но почему-то в последующих версиях разработчики от него отказались.

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

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

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

Подготовка многомерных данных

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

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

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

Кроме того, используя автофильтр можно попробовать просуммировать данные по сочетаниям одного или нескольких ключевых критериев. Совершенно отсутствует информация о брендах, категориях и типах. Нет возможности сгруппировать данные с автоматическим суммированием по определенному ключу (например, по покупателям).Лист Пример содержит стандартное средство анализа данных – автофильтр. Глядя на пример заполнения таблицы, очевидно, что нормальному анализу поддаются данные о продажах по датам (они расположены по столбцам).

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

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

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

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

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

От автофильтра к сводному отчету

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

В нормальном режиме создания сводной таблицы для исходных данных Excel не позволяет связывать данные нескольких таблиц по определенным полям. Обойти это ограничение можно программными средствами – см. пример-дополнение к данной статье на нашем сайте.

Чтобы не прибегать к программным методам обработки информации (тем более, что они и не универсальны), следует добавить дополнительные характеристики непосредственно в форму ввода журнала – см. лист ПродажиАнализ.

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

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

Вторая и третья таблицы созданы через копирование и последующую настройку. Источник данных для всех таблиц один и тот же. Можете это проверить, изменив исходные данные, затем надо обновить данные сводных отчетов.Первая таблица анализа построена через интерфейс Excel 2007 Лента Вставка Сводная таблица (в Excel 2000-2003 меню Данные Сводная таблица).

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

Свойства и форматирование

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

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

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

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

После выделения можно изменять вид через ленту, контекстное меню или вызывать стандартный диалог формата ячейки:

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

Кроме того, в Excel 2007 появилось множество предопределенных стилей отображения сводной таблицы:

Сводная диаграмма

Нажав кнопку на ленте «Сводная диаграмма», можно сформировать специальный тип диаграммы, отображающей данные сводной таблицы:

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

Доступ к внешним данным

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

Excel поддерживает множество типов источников внешних данных:

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

Источник: https://www.excelfin.ru/index.php/articles/96