Что такое свободная таблица
Создание сводной таблицы для анализа данных листа
Сводная таблица — это эффективный инструмент для вычисления, сведения и анализа данных, который упрощает поиск сравнений, закономерностей и тенденций.
Работа с помощью стеблей немного отличается в зависимости от того, какую платформу вы используете для Excel.
Создание в Excel для Windows
Выделите ячейки, на основе которых вы хотите создать сводную таблицу.
Примечание: Ваши данные не должны содержать пустых строк или столбцов. Они должны иметь только однострочный заголовок.
На вкладке Вставка нажмите кнопку Сводная таблица.
В разделе Выберите данные для анализа установите переключатель Выбрать таблицу или диапазон.
В поле Таблица или диапазон проверьте диапазон ячеек.
В разделе Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист, чтобы поместить сводную таблицу на новый лист. Можно также выбрать вариант На существующий лист, а затем указать место для отображения сводной таблицы.
Настройка сводной таблицы
Чтобы добавить поле в сводную таблицу, установите флажок рядом с именем поля в области Поля сводной таблицы.
Примечание: Выбранные поля будут добавлены в области по умолчанию: нечисловые поля — в область строк, иерархии значений дат и времени — в область столбцов, а числовые поля — в область значений.
Чтобы переместить поле из одной области в другую, перетащите его в целевую область.
Данные должны быть представлены в виде таблицы, в которой нет пустых строк или столбцов. Рекомендуется использовать таблицу Excel, как в примере выше.
Таблицы — это отличный источник данных для сводных таблиц, так как строки, добавляемые в таблицу, автоматически включаются в сводную таблицу при обновлении данных, а все новые столбцы добавляются в список Поля сводной таблицы. В противном случае вам потребуется либо изменить исходные данные для pivotTable,либо использовать формулу динамического именоваемого диапазона.
Все данные в столбце должны иметь один и тот же тип. Например, не следует вводить даты и текст в одном столбце.
Сводные таблицы применяются к моментальному снимку данных, который называется кэшем, а фактические данные не изменяются.
Если у вас недостаточно опыта работы со сводными таблицами или вы не знаете, с чего начать, лучше воспользоваться рекомендуемой сводной таблицей. При этом Excel определяет подходящий макет, сопоставляя данные с наиболее подходящими областями в сводной таблице. Это позволяет получить отправную точку для дальнейших экспериментов. После создания рекомендуемой сводной таблицы вы можете изучить различные ориентации и изменить порядок полей для получения нужных результатов.
Вы также можете скачать интерактивный учебник Создание первой сводной таблицы.
1. Щелкните ячейку в диапазоне исходных данных или таблицы.
2. Перейдите на вкладку > рекомендуемая ст.
«Рекомендуемые сводные таблицы» для автоматического создания сводной таблицы» loading=»lazy»>
3. Excel данные анализируются и представлены несколько вариантов, например в этом примере с использованием данных о расходах семьи.
4. Выберите наиболее подбираемую для вас сетовую и нажмите кнопку ОК. Excel создаст сводную таблицу на новом листе и выведет список Поля сводной таблицы.
1. Щелкните ячейку в диапазоне исходных данных или таблицы.
2. Перейдите в > Вставить.
Если вы используете Excel для Mac 2011 или более ранней версии, кнопка «Сводная таблица» находится на вкладке Данные в группе Анализ.
3. Excel отобразит диалоговое окно Создание таблицы с выбранным диапазоном или именем таблицы. В этом случае мы используем таблицу «таблица_СемейныеРасходы».
4. В разделе Выберите, куда следует поместить отчет таблицы выберите новый или существующий. При выборе варианта На существующий лист вам потребуется указать ячейку для вставки сводной таблицы.
5. Нажмитекнопку ОК, Excel создаст пустую стебли и отобразит список полей.
Список полей сводной таблицы
В области Имя поля в верхней части выберите любое поле, которое вы хотите добавить в свою сетную. По умолчанию не числовые поля добавляются в область строк, поля даты и времени — в область Столбец, а числовые — в область значений. Вы также можете вручную перетащить любой доступный элемент в любое поле или, если элемент в ней больше не нужен, просто перетащите его из списка поля или снимите. Возможность переустановить элементы полей — одна из функций, которые упрощают быстрое изменение внешнего вида.
Список полей сводной таблицы
По умолчанию поля в области значений отображаются как СУММ. Если Excel данные интерпретируются как текст, они отображаются как счёт. Именно поэтому так важно не смешивать типы данных для полей значений. Вы можете изменить вычисление по умолчанию, щелкнув стрелку справа от имени поля и выбрав Параметры полей.
Совет: Так как при изменении способа вычисления в разделе Суммировать по обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно выбрать пункт Найти ( в меню «Изменить»), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.
Значения также можно выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Вы можете настроить такие параметры в диалоговом окне Параметры поля на вкладке Дополнительные вычисления.
Отображение значения как результата вычисления и как процента
Просто перетащите элемент в раздел Значения дважды, щелкните значение правой кнопкой мыши и выберите команду Параметры поля, а затем настройте параметры Суммировать по и Дополнительные вычисления для каждой из копий.
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить одну сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить. При наличии нескольких сводных таблиц сначала выберите любую ячейку в любой сводной таблице, а затем на ленте откройте вкладку Анализ сводной таблицы, щелкните стрелку под кнопкой Обновить и выберите команду Обновить все.
Если вы создали и решили, что вам больше не нужна, вы можете просто выбрать весь диапазон, а затем нажать кнопку УДАЛИТЬ. Она не влияет на другие данные, с ее стебли или диаграммы. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.
Примечание: Мы постоянно работаем над улучшением работы с Excel для Интернета. Новые изменения внося постепенно, поэтому если действия, которые вы в этой статье, возможно, не полностью соответствуют вашему опыту. Все обновления в конечном итоге будут обновлены.
Выберите таблицу или диапазон данных на листе, а затем выберите Вставить >, чтобы открыть область Вставка таблицы.
Вы можете вручную создать собственную или рекомендованную советку. Выполните одно из следующих действий:
Примечание: Рекомендуемые стеблицы доступны только Microsoft 365 подписчикам.
На карточке Создание собственной таблицы выберите Новый лист или Существующий лист, чтобы выбрать место назначения для этой таблицы.
В рекомендуемой pivottable выберите Новый лист или Существующий лист, чтобы выбрать место назначения для этой таблицы.
Обзор сводных таблиц и сводных диаграмм
С помощью сводной таблицы можно создавать сводки, анализировать, изучать и представлять сводные данные. Сводные диаграммы дополняют сводные таблицы, позволяя визуализировать содержащиеся в них данные. Кроме того, с помощью сводных диаграмм можно легко отображать сравнения, шаблоны и тенденции. И сводные таблицы, и сводные диаграммы помогают принимать взвешенные решения на основании критически важных данных вашей организации. Вы также можете подключаться к внешним источникам данных, например к таблицам SQL Server, кубам служб SQL Server Analysis Services, Azure Marketplace, файлам подключения к данным Office (ODC-файлам), XML-файлам, базам данных Access и текстовым файлам, чтобы создавать сводные таблицы, или создавать на основе сводных таблиц другие таблицы.
Примечание: Представленные в этой статье снимки экрана созданы в Excel 2016. Если вы используете другую версию, то в ней может быть немного другой интерфейс, но если не указано иное, функции будут такими же.
Сведения о сводных таблицах
Сводная таблица — это интерактивный способ быстрого обобщения больших объемов данных. Вы можете использовать ее для подробного анализа числовых данных и для получения ответов на разнообразные вопросы, связанные с данными. Сводные таблицы предназначены для выполнения следующих задач:
запрос больших объемов данных различными понятными способами;
подведение промежуточных итогов и вычисление числовых данных, обобщение данных по категориям и подкатегориям, создание пользовательских вычислений и формул;
развертывание и свертывание уровней представления данных для получения точных сведений о результатах, детализация итоговых данных по интересующим вопросам;
перемещение строк в столбцы или столбцов в строки («сведение») для просмотра различных сводок на основе исходных данных;
фильтрация, сортировка, группировка и условное форматирование наиболее важных и часто используемых подмножеств данных для привлечения внимания к нужным сведениям;
представление кратких, наглядных отчетов с примечаниями на веб-страницах или в напечатанном виде.
Например, слева показан простой список расходов семьи, а справа — созданная на его основе сводная таблица.
Данные о расходах семьи
Соответствующая сводная таблица
После того как вы создадите сводную таблицу, выбрав для нее источник данных, упорядочив поля в списке полей сводной таблицы и выбрав исходный макет, вы сможете при работе с ней выполнять перечисленные ниже задачи.
Изучать данные. Вот какие действия доступны.
Развертывание и свертывание данных, а также отображение базовых сведений, которые относятся к значениям.
Сортировка, фильтрация и группировка полей и элементов.
Изменение функций сведения и добавление настраиваемых вычислений и формул.
Изменять макет формы и расположения полей. Вот какие действия доступны.
Изменение формы сводной таблицы: сжатая форма, структура или табличная форма.
Добавление, изменение порядка и удаление полей.
Изменение порядка полей или элементов.
Изменение макета столбцов, строк и промежуточных итогов Вот какие действия доступны.
Включение и отключение заголовков полей строк и столбцов, а также отображение или скрытие пустых строк.
Отображение промежуточных итогов выше или ниже их строк.
Настройка ширины колонок при обновлении.
Перемещение поля столбца в область строк или поля строки в область столбцов.
Объединение или отмена объединения ячеек для внешних элементов строки и столбца.
Изменять способы отображения пустых значений и ошибок. Вот какие действия доступны.
Изменение способа отображения ошибок и пустых ячеек.
Изменение способа отображения элементов и подписей без данных.
Отображение и скрытие пустых строк.
Изменять формат. Вот какие действия доступны.
Ручное и условное форматирование ячеек и диапазонов.
Изменение стиля общего формата сводной таблицы.
Изменение числового формата для полей.
Включение форматирования сервера OLAP.
Сводные диаграммы служат для графического представления данных, содержащихся в сопоставленных с ними сводных таблицах. Как и сводные таблицы, сводные диаграммы — интерактивные объекты. При создании сводной диаграммы на экране отображается область фильтра сводной диаграммы. С помощью этой области вы можете сортировать и фильтровать исходные данные, используемые в сводной диаграмме. Изменения макета и данных в связанной сводной таблице немедленно отражаются в макете и данных в сводной диаграмме и наоборот.
Как и в обычных диаграммах, в сводных диаграммах отображаются ряды данных, категории, маркеры данных и оси. Кроме того, вы можете изменить тип диаграммы и другие параметры, например названия, расположение условных обозначений, метки данных, расположение диаграммы и т. д.
Вот сводная диаграмма, основанная на приведенном выше примере сводной таблицы.
Дополнительные сведения см. в статье Создание сводной диаграммы.
Если вы знакомы со стандартными диаграммами, то работа со сводными диаграммами не будет для вас проблемой, так как большинство действий для этих двух типов диаграмм полностью совпадают. Тем не менее существуют и некоторые отличия.
Ориентация строк и столбцов В отличие от обычной диаграммы в сводной диаграмме вам не удастся изменить ориентацию строк и столбцов с помощью диалогового окна Выбор источника данных. Вместо этого вы можете изменить ориентацию меток строк и столбцов в связанной сводной таблице.
Типы диаграмм Для сводной диаграммы вы можете выбрать любой тип диаграммы, кроме точечной, биржевой и пузырьковой.
Исходные данные Обычные диаграммы связаны непосредственно с ячейками листа. Сводные диаграммы основаны на источнике данных сопоставленных с ними сводных таблиц. В отличие от обычной диаграммы в сводной диаграмме вам не удастся изменить диапазон данных диаграммы с помощью диалогового окна Выбор источника данных.
Форматирование Большинство параметров форматирования, включая добавленные вами элементы диаграммы, макет и стиль, сохраняются при обновлении сводной диаграммы. Тем не менее линии трендов, метки данных, пределы погрешностей и другие изменения, вносимые в наборы данных, не сохраняются. В обычных диаграммах после применения такого форматирования оно не исчезает.
Несмотря на то что вам не удастся напрямую изменить размер меток данных в сводной диаграмме, вы можете увеличить размер шрифта и таким образом изменить размер меток.
В качестве основы для сводной таблицы или сводной диаграммы вы можете использовать данные листа Excel. Данные должны представлять собой список, в первой строке которого содержатся метки столбцов. Приложение Excel использует эти метки в качестве имен полей. Каждая ячейка в последующих строках должна содержать данные, соответствующие заголовку столбца. Не следует использовать данные различных типов в одном и том же столбце. Например, в одном и том же столбце не следует использовать денежные значения и даты. Кроме того, в выбранном вами диапазоне данных не должно быть пустых строк или столбцов.
Таблицы Excel Таблиц Excel изначально представлены в виде списка и отлично подходят в качестве исходных данных для сводной таблицы. При обновлении сводной таблицы новые и обновленные данные из таблицы Excel автоматически включаются в операцию обновления.
Использование динамического именованного диапазона Чтобы было проще обновлять сводную таблицу, вы можете создать динамический именованный диапазон и использовать его имя в качестве источника данных для сводной таблицы. Если вы расширите именованный диапазон, чтобы включить в него дополнительные данные, просто обновите сводную таблицу, и в нее будут включены эти новые данные.
Включение итогов Excel автоматически создает промежуточные и общие итоги в сводной таблице. Если источник данных содержит автоматически вычисляемые промежуточные и общие итоги, которые вы создали с помощью команды Промежуточный итог в группе Структура на вкладке Данные, то с помощью этой же команды удалите промежуточные и общие итоги, прежде чем создавать сводную таблицу.
Вы можете получить данные из внешнего источника данных, например базы данных, куба OLAP или текстового файла. Например, у вас может быть база данных со сведениями о продажах, для которых вы хотите создать сводку и которые вам необходимо проанализировать.
Файлы подключения к данным Office Если вы используете файл подключения к данным Office (ODC-файл) для получения внешних данных для сводной таблицы, вы можете вводить данные непосредственно в нее. Рекомендуется получать внешние данные для отчетов с помощью ODC-файлов.
Источник данных OLAP Когда вы получаете исходные данные из базы данных OLAP или файла куба, данные возвращаются в Excel только в виде сводной таблицы либо сводной таблицы, преобразованной в функции листа. Дополнительные сведения см. в статье Преобразование ячеек сводной таблицы в формулы листа.
Источник данных, не являющийся источником данных OLAP Это базовые данные для сводной таблицы или сводной диаграммы, поступающие из источника, отличного от базы данных OLAP. Например, это могут быть данные из реляционных баз данных или текстовых файлов.
Кэш сводной таблицы Каждый раз при создании новой сводной таблицы или сводной диаграммы Excel сохраняет копию данных для отчета в памяти, а также сохраняет эту область хранения данных в файле книги — это называется кэш сводной таблицы. Для каждой новой сводной таблицы требуется дополнительный объем памяти и место на диске. Однако при использовании существующей сводной таблицы в качестве источника данных для новой таблицы в той же книге обе таблицы будут использовать один и тот же кэш. Благодаря повторному использованию кэша снижается размер книги и сокращается объем данных, хранящихся в памяти.
Требования к расположению Чтобы использовать одну сводную таблицу в качестве источника данных для другой сводной таблицы, обе они должны находиться в одной и той же рабочей книге. Если исходная сводная таблица находится в другой рабочей книге, скопируйте источник в рабочую книгу, в которой вы хотите создать другую сводную таблицу. Сводные таблицы и сводные диаграммы, находящиеся в разных рабочих книгах, — это отдельные объекты, для каждого из которых имеется собственная копия данных в памяти и в рабочих книгах.
Изменения влияют на обе сводные таблицы Когда вы обновляете данные в новой сводной таблице, Excel также обновляет данные в исходной сводной таблице и наоборот. Когда вы группируете элементы или отменяете их группировку либо создаете вычисляемые поля или вычисляемые элементы в одной таблице, изменения будут внесены в обе таблицы. Если вам необходимо, чтобы одна сводная таблица не зависела от другой, создайте сводную таблицу на основе исходного источника данных, а не копируйте исходную сводную таблицу. Просто имейте в виду, что если выполнять такие операции слишком часто, это может сказаться на объеме свободной памяти.
Сводные диаграммы Вы можете создать сводную таблицу или сводную диаграмму на основе другой сводной таблицы, но вам не удастся создать сводную диаграмму непосредственно на основе другой сводной диаграммы. Изменения, вносимые в сводную диаграмму, влияют на связанную сводную таблицу и наоборот.
Изменение исходных данных может привести к тому, что для анализа будут доступны другие данные. Например, вам может быть необходимо удобно переключаться из тестовой базы данных в рабочую базу данных. Переопределив исходные данные, вы можете обновить сводную таблицу или сводную диаграмму, используя данные, структура которых похожа на структуру информации, получаемой из исходного подключения к данным. Если эти данные значительно отличаются, и в них имеется большое количество новых или дополнительных полей, возможно, будет проще создать другую сводную таблицу или сводную диаграмму.
Отображение данных, полученных при обновлении При обновлении сводной таблицы могут быть изменены данные, доступные для отображения. Для сводных таблиц, основанных на данных листа, Excel получает новые поля, имеющиеся в указанном вами исходном диапазоне или именованном диапазоне. Для отчетов, основанных на внешних данных, Excel получает новые данные, которые соответствуют критериям для базового запроса или данных, которые становятся доступными в кубе OLAP. Вы можете просматривать все новые поля в списке полей и добавлять необходимые поля в отчет.
Изменение создаваемых кубов OLAP У отчетов, основанных на данных OLAP, всегда есть доступ ко всем данным в кубе. Если вы создали автономный куб, который содержит подмножество данных, имеющихся в кубе сервера, с помощью команды Автономный режим OLAP вы можете изменить файл куба так, чтобы он содержал другие данные с сервера.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Как сводные таблицы Excel помогут сэкономить ваше время?
Научитесь использовать все прикладные инструменты из функционала MS Excel.
Порой у многих специалистов в экономике, бухгалтерии, анализе и финансах встает вопрос: «Как работать с таблицами, в которых десятки или сотни строк?» или «Как структурировать и анализировать большие таблицы?».
На помощь профессионалам пришел очень полезный инструмент — Сводные таблицы. Благодаря ему можно придавать презентабельный вид любым таблицам с нескончаемыми столбцами и строками: группировать, перемещать, показывать нужные и скрывать те, что не пригодятся, делать различные расчеты по встроенным и созданным вами формулам, фильтровать, — в общем, смотреть на данные с разных сторон в очень презентабельном виде.
Именно благодаря своим широким возможностям Сводные таблицы — просто незаменимый инструмент для людей, работающих с большим количеством информации, которую периодически нужно структурировать и анализировать. Рекомендуем записаться на наш открытый онлайн-курс «Аналитика в Excel», если хотите узнать о других полезных возможностях программы.
В чем полезность сводных таблиц
Многие специалисты используют Microsoft Excel для вышеперечисленных целей, потому что программа сильно экономит время профессионала, забирая всю трудоемкую работу с данными на себя.
Еще отличительной чертой, из-за которой Excel стал популярным — создание достаточно нетривиальных средств анализа данных (Сводные таблицы входят в их число) не требует особых знаний по программированию.
Но перед тем, как начать пользоваться всеми «профитами» от Сводных таблиц Excel, нужно приложить немало усилий, чтобы хорошо освоить довольно сложную технику создания таблиц и работы с ними. Но в итоге, эффект от приобретения нового навыка, вот увидите, окупит потраченное время, энергию и силы.
В этой статье мы решили рассказать об основных технологиях создания Сводных таблиц в Excel и отметить некоторые особенности методов, которые могут Вам пригодиться в будущей работе.
Сначала предлагаем разобраться в том, что же такое Сводная таблица и «с чем ее едят».
Сводные таблицы — это перечень, содержащий выборочные данные из каких-либо источников (Базы Данных, таблицы Excel и другие), который нужен специалисту для дальнейшего анализа данных, изменения структуры таблицы и так далее.
Сводные таблицы позволяют менять визуальное представление данных, представляя их с новой точки зрения, не меняя сам источник.
Создание сводной таблицы в Excel
Прежде чем создавать Сводную таблицу необходимо подготовить таблицу с исходными данными для дальнейшего анализа:
Требования очень похожи на те, что выдвигают при проектировании реляционных баз данных, поэтому специалистам (или хотя бы тем, кто имеет базовое представление) в этой области не сложно будет провести параллель и освоить новый программный инструмент.
Вот мы и добрались до главного вопроса, который нас интересовал – создание Сводной таблицы в Excel. Начнем разбираться.
В статье мы будем приводить примеры на учебной таблице, где отображена информация о закупках товара, предположим, магазином одежды.
Сначала откроем таблицу, убедимся, что она удовлетворяет всем требованиям и со спокойной душой перейдем к следующему шагу. Найдем вкладку «Вставка» на панели быстрого доступа. Перед нами появится лента с доступным функционалом. Мы создаем Сводную таблицу, поэтому ищем одноименную кнопку. Она будет самой первой на панели.
«Кликнем» на значок, и перед нами появится диалоговое окно с перечнем необходимых параметров:
Выбрав все параметры, нажимаем ОК, и на новом листе выделяется область, где будет располагаться Сводная таблица, а справа появится панель управления, с помощью которой мы сможем превратить обычную таблицу в информативный отчет.
Полдела сделано — Сводная таблица создана, осталось заполнить ее данными. Для этого обратимся к окошку справа «Поля сводной таблицы».
Сверху мы видим область, где перечислены все атрибуты исходной таблицы: «Дата поступления», «Наименование товара», «Отдел» и так далее.
Теперь можно крутить, вертеть данные как угодно, придумывая концепции для Сводной таблицы.
Для начала попробуем объединить данные по отделам и высчитать суммарную стоимость товаров, пришедших в каждый отдел.
Для добавления поля в таблицу, нужно поставить галочку напротив названия атрибутов в списке полей в верхнем правом углу. В нашем случае — «Отдел» и «Стоимость товара в партии».
В окошке, отведенном под Сводную таблицу, появились три строки с названиями отделов (левый столбик) и стоимостью товара по каждому отделу. Помимо суммы можно считать максимум, минимум, среднее значение и делать другие математические операции. Итоговый результат по всем трем отделам указывается в самом низу таблицы.
Посмотрите, какие места заняли данные в нижней части панели настроек: поле «Отдел», содержащее текстовые значения, переместилось в область «Строки», а численные данные — в «Значения».
То же самое произойдет, если мы добавим в таблицу любое числовое поле (например, «Количество»): у текущей таблицы появится столбец, и в области «Значения» — «Сумма по полю Количество».
Теперь в нашей таблице отображается информация не только о том, на какую сумму пришло товара в каждый отдел, но и в каком количестве.
Если нас не устраивает такая ориентация таблицы, то из области «Строки» можно перетащить «Отдел» в область «Столбцы»: передвиньте курсор, удерживая кнопку мышки в нажатом положении.
Таблица стала намного компактнее: в строчке отображается результат вычислений, а по столбцам – отделы, которым соответствуют данные.
Такое расположение может быть удобным при маленьком количестве отделов (как в примере), но если их много, то мы получим очень вытянутую таблицу, которую невозможно будет охватить взглядом и в которой трудно будет найти необходимые данные. Такая таблица нам точно не нужна.
Как мы уже отметили, сумма — это далеко не единственная операция, применимая к данным. Посмотреть, какие формулы нам доступны, можно, нажав на название поля в области «Значения» и выбрав «Параметры полей значений».
Появится окно со списком операций, которые можно применять к данным. Среди них: количество, среднее, максимум, минимум, отклонение, дисперсия и другие, — все, что может понадобиться для качественного анализа.
Для каждой строчки в поле «Значения» можно применять разные формулы. Например, добавим в таблицу поле «Цена» и отобразим максимальные значения по каждому отделу.
Итак, какие выводы мы можем сделать по получившейся таблице? В магазин больше всего закупили Аксессуаров (на сумму 267660 руб.), причем самый дорогой товар стоил 2700 руб.
Как Вы могли заметить, мы еще ни разу не пользовались областью «Фильтры» на панели параметров. Этот раздел нужен для того, чтобы можно было сортировать данные по заданным критериям.
К примеру, добавим в Сводную таблицу данные, чтобы можно было отфильтровать записи по «Дате поступления». Для этого поставим напротив поля «Дата поступления» галочку. Это поле сразу переместиться в область «Строки».
Данные добавили, теперь нужно создать фильтр. Для этого просто перетаскиваем «Дату поступления» в область «Фильтры», как мы это делали раньше со столбцами и строками. Получим вот что:
Над всеми строками появилось еще одно поле — «Дата поступления», в котором мы можем выбрать дату, нажав на треугольник в конце строчки (Все).
Теперь легко узнать на какую суммарную стоимость пришел товар, сколько стоил самый дорогой в определенный день. Например, за 04.05.2017.
Помимо выставления определенной даты, можно выбирать название отделов. То есть можно сделать Сводную таблицу, где будет содержаться информация о том, на какую сумму 04.05.2017 поступило Аксессуаров и какова цена самого дорогостоящего товара.
Как обновлять таблицу
У многих пользователей, научившихся создавать Сводные таблицы по известному алгоритму, часто появляется затруднение: они меняют данные в источнике (у нас таблица Excel), а в сводной таблице при этом никаких модификаций не происходит.
Решение этой проблемы занимает не больше пары секунд: достаточно нажать на одну кнопку, но не все знают о ее существовании и месторасположении. Поэтому мы считаем нужным рассказать о том, как обновлять данные в Сводных таблицах.
Если изменения происходят с данными только в одном столбце, то достаточно нажать правой кнопкой мыши на ячейки в этом столбце и найти функцию «Обновить». После того, как Вы нажмете «Обновить», таблица преобразуется.
Если же изменения произошли больше, чем в одном столбце, то выделим произвольную зону таблицы и на панели зайдем в раздел «Анализ», в котором тоже есть функция «Обновить». Можно выбрать «Обновить», тогда функция применится к выделенной зоне или «Обновить все». Выбирайте то, что Вам нужно, и Сводная таблица сразу изменится.
Удаление Сводной таблицы
Раз уж мы поняли, как создавать и изменять Сводные таблице, давайте посмотрим, как их удалять.
Эта операция тоже не требует большой последовательности действия: выделим область, где находится таблица (чтобы сэкономить немного времени воспользуемся клавишами Ctrl + A — выделить все), и нажмем delete (или на правую кнопку мыши, а затем на «Удалить» в появившемся списке)
Если нужно удалить столбик в Сводной таблице, то достаточно убрать галочку напротив названия в списке полей таблицы.
Добавление новых столбцов/таблиц
Выше мы описали, как обновлять данные в Сводной таблице при том условии, что эти данные находятся в указанном при создании таблицы диапазоне.
А что делать, когда в Сводную таблицу требуется добавить дополнительную колонку с данными?
Для начала новый столбец нужно вставить в исходную таблицу (источник данных), а потом увеличить диапазон для Сводной таблицы. Если нужно добавлять целую таблицу, то сперва ее нужно объединить с исходной, и потом изменять диапазон.
Вот мы добавили к источнику столбик «Цена с НДС». Теперь заходим в раздел «Анализ» и открываем «Источник данных».
Программа сама переведет Вас на тот лист, где находится источник, и в диалоговом окне попросит указать диапазон значений. Выделяем пополненную данными исходную таблицу, жмем ОК. После этого необходимо обновить Сводную таблицу, как мы показывали выше.
После того, как Вы обновите таблицу, в перечне полей появится новое — «Цена с НДС». Теперь мы сможем использовать значения этого поля в анализе для изучения ситуации с новых ракурсов.
Заключение
Как вы видите, работать со Сводными таблицами не сложно и даже интересно. Достаточно понять технологию создания и основные принципы.
Поэтому, прочитав нашу статью и повторив все действия со своими данными, вы уже сможете записать умение работать со Сводными таблицами Excel в свое CV.
И действительно, Сводные таблицы зачастую являются достойным аналогом использования реляционных баз данных. Операции, которые в Excel можно делать простым «пертаскиванием», в программировании реализуют с помощью сложных запросов.
Конечно, полезная информация по этой теме на нашей статье не заканчивается. Есть еще очень много нюансов, которые невозможно изложить в одной статье.
Но нашей задачей было ввести в курс дела тех, кто еще не знаком с таким мощным инструментом анализа, как Сводные таблицы, рассказать о многочисленных возможностях, которые предоставляет Microsoft Excel, напомнить опытным пользователем моменты, которые могли забыться. И кажется, нам это удалось.
Рекомендуем скачать бесплатный гайд с горячими клавишами, чтобы ваша работа в Excel стала еще продуктивнее!
Если осталось что-то, на чем мы не заострили внимание, а вам бы очень хотелось это узнать, пишите нам об этом, и мы не оставим ваш интерес неудовлетворенным.
Научитесь использовать все прикладные инструменты из функционала MS Excel.