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

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

К счастью, сравнительно недавно появился MS Office 2010-2013, который не только включает в себя ряд обновленных программ для обработки текстовых файлов, таблиц, баз данных и презентаций, но и позволяет работать с ними одновременно нескольким работникам, что в корпоративной среде просто бесценно.

Почему именно новые версии?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как проводить по годам?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Также здесь можно использовать категорию ASK, что значит «запросить», и FILLIN, что значит «заполнить», NEXT и NEXTIF, ELSE. Также можно произвести настройку параметров тех пунктов прогноза, которые будут отображены в готовой таблице. Найти необходимую запись можно при помощи специального фильтра или же путем простой сортировки данных, о чем мы уже говорили выше.

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

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

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

Обрабатывать большие объемы информации и составлять сложные многоуровневые отчеты достаточно непросто без использования средств автоматизации. Excel 2010 как раз и является инструментом, позволяющим упростить эти задачи, путем создания сводных (перекрестных) таблиц данных (Pivot table).

Сводная таблица в Excel 2010 используется для:

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

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

Способ 1. Создание сводных таблиц, используя стандартный инструмент Excel 2010 «Сводная таблица»

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

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

2. Откройте вкладку «Вставка» и выберите из раздела «Таблицы» инструмент «Сводная таблица».

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

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

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

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

В правой половине окна создается панель основных инструментов управления — «Список полей сводной таблицы». Все поля (заголовки столбцов в таблице исходных данных) будут перечислены в области «Выберите поля для добавления в отчет». Отметьте необходимые пункты и отчет сводной таблицы с выбранными полями будет создан.

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

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

Способ 2. Создание сводной таблицы с использованием инструмента «Мастер сводных таблиц и диаграмм»

Чтобы применить этот способ, придется сделать доступным инструмент, который по умолчанию на ленте не отображается. Откройте вкладку «Файл» — «Параметры» — «Панель быстрого доступа». В списке «Выбрать команды из» отметьте пункт «Команды на ленте». А ниже, из перечня команд, выберите «Мастер сводных таблиц и диаграмм». Нажмите кнопку «Добавить». Иконка мастера появится вверху, на панели быстрого доступа.

Мастер сводных таблиц в Excel 2010 совсем не многим отличается от аналогичного инструмента в Excel 2007. Для создания сводных таблиц с его помощью выполните следующее.

1. Кликните по иконке мастера в панели быстрого допуска. В диалоговом окне поставьте переключатель на нужный вам пункт списка источников данных:

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

2. После этого выбирается вид создаваемого отчета – «сводная таблица» или «сводная диаграмма (с таблицей)».

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

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

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

• При выборе внешнего источника данных используется приложение Microsoft Query, входящее в комплект поставки Excel 2010 или, если требуется подключиться к данным Office, используются опции вкладки «Данные».
• Если в документе уже присутствует отчет сводной таблицы или сводная диаграмма — в качестве источника можно использовать их. Для этого достаточно указать их расположение и выбрать нужный диапазон данных, после чего будет создана новая сводная таблица.

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

В этой статье вы узнаете:

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

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

Сводные таблицы MS Excel – это инструмент для аналитики и представления данных в удобном и легко понятном виде. Их используют в следующих случаях:

1. Большой объем исходных данных. Небольшое количество данных можно просчитать и проанализировать вручную. Однако если в распоряжении имеется сотни и тысячи записей, ошибки неизбежны и их будет много. В разы увеличится и время, необходимое для обработки.
2. Необходимо выявить тенденции и динамику в данных. В сводных таблицах можно легко преобразовать вывод информации, не нужно сортировать ее вручную.
3. Чтобы создать диаграмму или построить график на основе отформатированных данных.
4. Нужно делать промежуточный «срез» итогов, которые часто меняются, для последующего их сравнения. В этом случае достаточно обновить данные, Excel пересчитает все автоматически.
5. Для анализа и поиска экстремальных значений – минимумы и максимумы данных.
6. Для сведения разрозненных данных из разных источников. При необходимости в исходные данные можно добавить необходимую строку или столбец и обновить итоговое отображение отчета.

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

Как выглядит сводная таблица

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

• значения;
• строки;
• столбцы;
• фильтр отчета.

Сводная таблица файла.xlsx имеет следующие ограничения:

• максимальное количество полей строк – 1 048 576 или ограничено размером оперативной памяти;
• максимальное количество полей столбцов – 16 384;
• максимальное количество полей страниц – 16 384;
• максимальное количество полей данных – 16 384.

Возьмем в качестве примера следующие данные.

Сводная таблица для этих данных будет выглядеть так:

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

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

Это сегмент строк. Здесь может быть одно или несколько значений. В нашем случае – это фамилии менеджеров. Напротив фамилий в соответствующих строках – сводный итог работы.

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

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

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

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

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

Появится новое окно:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ВИДЕО: Как работать со сводными таблицами

1. Мы имеем базу данных по сотрудникам компании "Транс". Она содержит большой объём информации по финансовым операциям, который занимает более 150 строк. Наша задача - сформировать отчет о затратах только по зарплате и страховым взносам по каждому отделу. Чтобы сделать это вручную, понадобится затратить очень много сил и времени, поэтому мы поступим по-другому. Встав на любую ячейку базы, приступим к созданию Сводной таблицы: Вставка - Сводная таблица .

2. В появившемся окне "Создание сводной таблицы", не внося никаких изменений, нажимаем ОК.

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

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

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

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

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

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

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

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

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

Как применять продвинутые приёмы сводных таблицы в Excel (короткое видео)

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

5 продвинутых приёмов сводных таблиц в Excel

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

1. Срезы

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

В этом примере я вставил срез для типа Item . После того, как я нажимаю на Backpack , сводная таблица показывает только этот параметр в таблице.

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

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

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

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

2. Временная шкала

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

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

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

Чтобы добавить временную шкалу, убедитесь, что вы выбрали сводную таблицу (щелкните внутри неё), а затем нажмите кнопку Анализ > Вставить временную шкалу на ленте Excel. Во всплывающем окне отметьте столбец даты (или несколько столбцов) и нажмите OK , чтобы создать временную шкалу.

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

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

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

3. Табличный вид

Стандартный вид сводных таблиц в Excel выглядит как водопад; при перетаскивании большего количества полей в строки, Excel создает больше «слоев» в данных.

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

Выберите Конструктор > Макет отчета > Показать в табличной форме , чтобы работать с вашей сводной таблицей в виде обычной таблицы.

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

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

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

4. Вычисляемые поля

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

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

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

Используйте Анализ > Поля, элементы и наборы > Вычисляемое поле , чтобы вставить вычисленное поле в вашу сводную таблицу.

В новом всплывающем окне начните с присвоения вычисленному полю имени. в моем случае я назову его Total Order . Общая стоимость заказа это количество, умноженное на цену каждой единицы. Затем я дважды кликнул по первому полю названия (quantity) в списке полей в этом окне.

Чтобы вычислить общую стоимость заказа, я умножил существующее поле цены за единицу (Unit Price ) на поле количества (Quantity ).

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

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

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

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

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

Перейдите в Вставка > , чтобы попробовать эту функцию.

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

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

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

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

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

Повторяйте и продолжайте обучение (с ещё бо́льшими уроками по Excel)

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

Эти уроки вам продвинуть ваши навыки с Excel и сводным таблицам на следующий уровень. Проверь их:

• ExcelZoo имеет большой обзор приёмов сводной таблицы в их статье, 10 уроков для освоения сводных таблиц (на английском).
• Мы в Envato Tuts+ рассмотрели сводные таблицы с помощью урока для новичков .
• Для более простого введения в Microsoft Excel ознакомьтесь с нашей учебной серией .

Что вы всё ещё хотите узнать о сводных таблицах? Сообщите мне ваши идеи или вопросы в комментариях ниже этого урока.