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

109613 18.11.2012 Скачать пример

Видео

Лирическое вступление или мотивация

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

В ней каждая отдельная строка содержит полную информацию об одной отгрузке (сделке, партии):

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

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

• Сколько и каких товаров продали в каждом месяце? Какова сезонность продаж?  
• Кто из менеджеров сколько заказов заключил и на какую сумму? Кому из менеджеров сколько премиальных полагается? 
• Кто входит в пятерку наших самых крупных заказчиков? 

… и т.д.

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

Поехали…

Если у вас Excel 2003 или старше

Ставим активную ячейку в таблицу с данными (в любое место списка) и жмем в меню Данные — Сводная таблица (Data — PivotTable and PivotChartReport).

Запускается трехшаговый Мастер сводных таблиц (Pivot Table Wizard).

Пройдем по его шагам с помощью кнопок Далее (Next) и Назад (Back) и в конце получим желаемое.

Шаг 1. Откуда данные и что надо на выходе?

На этом шаге необходимо выбрать откуда будут взяты данные для сводной таблицы. В нашем с Вами случае думать нечего — «в списке или базе данных Microsoft Excel». Но. В принципе, данные можно загружать из внешнего источника (например, корпоративной базы данных на SQL или Oracle). Причем Excel «понимает» практически все существующие типы баз данных, поэтому с совместимостью больших проблем скорее всего не будет. Вариант В нескольких диапазонах консолидации (Multiple consolidation ranges) применяется, когда список, по которому строится сводная таблица, разбит на несколько подтаблиц, и их надо сначала объединить (консолидировать) в одно целое. Четвертый вариант «в другой сводной таблице…» нужен только для того, чтобы строить несколько различных отчетов по одному списку и не загружать при этом список в оперативную память каждый раз.

Вид отчета — на Ваш вкус — только таблица или таблица сразу с диаграммой.

Шаг 2. Выделите исходные данные, если нужно

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

Шаг 3. Куда поместить сводную таблицу?

На третьем последнем шаге нужно только выбрать местоположение для будущей сводной таблицы. Лучше для этого выбирать отдельный лист — тогда нет риска что сводная таблица «перехлестнется» с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку Готово (Finish) и переходим к самому интересному — этапу конструирования нашего отчета.

Работа с макетом

То, что Вы увидите далее, называется макетом (layout) сводной таблицы.

Работать с ним несложно — надо перетаскивать мышью названия столбцов (полей) из окна Списка полей сводной таблицы (Pivot Table Field List) в области строк (Rows), столбцов (Columns), страниц (Pages) и данных (Data Items) макета.

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

Останется его только достойно отформатировать:

Если у вас Excel 2007 или новее

В последних версиях Microsoft Excel 2007-2010 процедура построения сводной таблицы заметно упростилась. Поставьте активную ячейку в таблицу с исходными данными и нажмите кнопку Сводная таблица (Pivot Table) на вкладке Вставка (Insert). Вместо 3-х шагового Мастера из прошлых версий отобразится одно компактное окно с теми же настройками:

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

• Названия строк (Row labels)
• Названия столбцов (Column labels)
• Значения (Values) — раньше это была область элементов данных — тут происходят вычисления.
• Фильтр отчета (Report Filter) — раньше она называлась Страницы (Pages), смысл тот же.

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

P.S

Единственный относительный недостаток сводных таблиц — отсутствие автоматического обновления (пересчета) при изменении данных в исходном списке. Для выполнения такого пересчета необходимо щелкнуть по сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду Обновить (Refresh).

Ссылки по теме

Источник: https://www.planetaexcel.ru/techniques/8/130/

Сводная таблица Excel: как создать и работать? Работа со сводными таблицами Excel

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

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

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

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

Был значительно обновлен модуль защиты документа.

Что это такое и для чего она нужна?

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

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

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

Создаем нужный документ

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

• Время продажи.
• Проданный товар.
• Стоимость всего сбыта.

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

Форматирование таблицы

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

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

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

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

Зажав при нажатой левой кнопке мыши поле «Товар», отправляем его в «Название строк», а пункт «Сумма всех продаж» таким же образом переправляем в «Значения» (в левой части листа). Вот так можно получить сумму продаж за весь анализируемый период.

Создание и группировка временных рядов

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

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

Используемые формулы

• В «Годовой» вставляем формулу вида: «=ГОД» (ссылаясь при этом на дату).
• Месяц нужно дополнить выражением: «=МЕСЯЦ» (также со ссылкой на дату).
• В третий столбик вставляем формулу вида: «=СЦЕПИТЬ» (ссылаясь при этом на год и на месяц).

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

Как проводить анализ продаж по годам?

Снова зажимаем левой клавишей мыши пункт «Год», после чего тащим его в «Название столбцов», после чего сводная таблица начинает отображать результаты продажи конкретного вида товара по всем годам. А если необходимо так же проанализировать месячный сбыт? Точно так же зажимаем ЛКМ «Месяц», перетаскивая его под годовой столбец.

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

• Сумма продаж конкретного товара за весь год.
• Динамику продаж каждого из них по годам.

Убираем данные из выдачи

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

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

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

Рассчитываем прогнозы

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

Чтобы сделать это, ставим курсор в поле «Общий итог», после чего кликаем по кнопке «Удалить общий итог». После этого выполняем автоматическое обновление сводной таблицы Excel способом, описанным нами выше (жмем на правую клавишу мыши, выбираем «Обновить»).

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

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

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

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

Функция слияния в поздних версиях «Офиса» практически не изменилась в сравнении с Office 2003. Тем не менее запросы к базам данных для выбора источника данных могут выполняться с применением Microsoft Query. Чтобы получить данные сводной таблицы Excel, необходимо наличие двух объектов:

• Основной документ, содержащий нужную вам информацию, а также поля, указывающие программе Excel, какие данные следует вставлять в документ. Основная таблица является стандартным документом табличного процессора, однако необходимо указать в окне диалога Слияние (Mail Merge Helper), что именно он должен использоваться как основной.
• Источник данных, содержащий ту информацию, которая необходима для составления прогноза. Можно создать новую таблицу тем способом, который мы указывали выше.

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

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

Используемые категории

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

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

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

Источник: https://autogear.ru/article/137/691/svodnaya-tablitsa-excel-kak-sozdat-i-rabotat-rabota-so-svodnyimi-tablitsami-excel/

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

Хитрости » 28 Июль 2013       Дмитрий       35216 просмотров

Скачать файл с исходными данными, используемый в видеоуроке:

  БД.xlsx (30,9 KiB, 1 323 скачиваний)

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

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

А вот польза при анализе информации просто неоценима.

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

А теперь представим как это будет выглядеть формулами:
-сначала надо получить список месяцев;

-затем список уникальных наименований филиалов;

-далее создать листы с рыбой таблиц, в которые надо будет собрать данные из исходной таблицы при помощи СУММЕСЛИ, СУММПРОИЗВ и им подобным.
При должном опыте можно уложиться минут в 15-20. С использованием сводных это можно сделать за две минуты.

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

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

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

• Но если изменить исходные данные, то изменение данных не будет автоматически отражено внутри сводных таблиц — для этого надо будет принудительно обновить отчет сводной таблицы:
• Выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить(Refresh) или вкладка Данные(Data) →Обновить все(Refresh all) →Обновить(Refresh).
• СОЗДАНИЕ СВОДНОЙ ТАБЛИЦЫ

1. Выделить любую ячейку исходной таблицы
2. Вкладка Вставка(Insert)→группа Таблица(Table)→Сводная таблица(PivotTable)
3. В диалоговом окне Создание сводной таблицы(Create PivotTable) проверить правильность выделения диапазона данных (или установить новый источник данных), определить место размещения Сводной таблицы:
   • На новый лист (New Worksheet)
   • На существующий лист (Existing Worksheet)
4. нажать OK

СВОДНАЯ ТАБЛИЦА СОСТОИТ ИЗ ЧЕТЫРЕХ ОБЛАСТЕЙ:
Область данных – основная область сводной таблицы, в которой производятся расчеты. Содержит основные итоговые данные по числовым полям. В область данных можно поместить одно и тоже поле, но с разными вычислениями (например одно Сумма по полю, другое Количество по полю).
Основные вычислительные функции области данных:

 Сумма (Sum)
 Количество (Count)
 Среднее (Average)
 Максимум (Max)
 Минимум (Min)
 Произведение (Product)

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

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

Для выполнения обновления необходимо выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить(Refresh) или вкладка Данные(Data)→Обновить(Refresh).

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

Статья помогла? Поделись ссылкой с друзьями!

Источник: https://www.excel-vba.ru/chto-umeet-excel/obshhie-svedeniya-o-svodnyx-tablicax/

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

Вопросы, рассмотренные в материале:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Теперь о требованиях, которых необходимо придерживаться при ее создании в Эксель. Исходные данные должны быть представлены в виде таблицы, у которой должно быть название для столбцов, то есть шапка. Для этих целей отлично подойдет создание умной таблицы в Excel. Также она не должна содержать пустые строки, столбцы и ячейки. Не должно быть скрытых строк/столбцов и объединенных ячеек.

• Как создать
• Как расставить поля
• Как работать с данными

Как создать

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

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

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

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

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

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

Как расставить поля

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

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

В качестве строк выберем «Товары». Аналогичным образом перетягиваем нужное поле в область «Названия строк».

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

В область «Названия столбцов» перетянем поле «Дата». Чтобы отобразить продажи не за каждый день, а, к примеру, по месяцам, кликните по любой дате правой кнопкой мыши и выберите из меню «Группировать».

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

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

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

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

Как работать с данными

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

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

В области «Значения» можно настроить параметры для поля. В примере выводится сумма значений: Рома в феврале продал рубашек на сумму 1 800.00. давайте посмотрим, сколько это штук. Кликаем левой кнопкой мышки по строке «Сумма по полю…» и выбираем из меню «Параметры полей значений».

В следующем окне из списка выбираем «Количество», выберите то, что подходит конкретно в вашем случае, и нажимаем «ОК».

Теперь, смотря на значения, можно понять, что Рома в феврале продал две рубашки.

Теперь сделаем, чтобы общий фильтр для таблицы был по месяцам. Меняем области: в «Фильтр отчета» перетаскиваем поле «Дата», в «Названия столбцов» – «Продавец».

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

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

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

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

Надеюсь, теперь Вы разобрались, как сделать сводную таблицу в Excel.

(1

Источник: http://comp-profi.com/kak-sdelat-svodnuyu-tablicu-v-excel/