Создание сводной таблицы для анализа данных листа
Сводная таблица — это эффективный инструмент для вычисления, сведения и анализа данных, который упрощает поиск сравнений, закономерностей и тенденций.
Создание сводной таблицы
-
Выделите ячейки, на основе которых вы хотите создать сводную таблицу.
Примечание: Ваши данные не должны содержать пустых строк или столбцов. Они должны иметь только однострочный заголовок.
-
На вкладке Вставка нажмите кнопку Сводная таблица.
-
В разделе
-
В поле Таблица или диапазон проверьте диапазон ячеек.
-
В разделе Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист, чтобы поместить сводную таблицу на новый лист. Можно также выбрать вариант На существующий лист, а затем указать место для отображения сводной таблицы.
-
Нажмите кнопку ОК.
Настройка сводной таблицы
-
Чтобы добавить поле в сводную таблицу, установите флажок рядом с именем поля в области Поля сводной таблицы.
Примечание: Выбранные поля будут добавлены в области по умолчанию: нечисловые поля — в область строк, иерархии значений дат и времени — в область столбцов, а числовые поля — в область значений.
-
Чтобы переместить поле из одной области в другую, перетащите его в целевую область.
Подготовка
-
Данные должны быть представлены в виде таблицы, в которой нет пустых строк или столбцов. Рекомендуется использовать таблицу Excel, как в примере выше.
-
Таблицы — это отличный источник данных для сводных таблиц, так как строки, добавляемые в таблицу, автоматически включаются в сводную таблицу при обновлении данных, а все новые столбцы добавляются в список
-
Все данные в столбце должны иметь один и тот же тип. Например, не следует вводить даты и текст в одном столбце.
-
Сводные таблицы применяются к моментальному снимку данных, который называется кэшем, а фактические данные не изменяются.
Создание сводной таблицы
Если у вас недостаточно опыта работы со сводными таблицами или вы не знаете, с чего начать, лучше воспользоваться рекомендуемой сводной таблицей. При использовании этой функции Excel определяет подходящий макет, сопоставляя данные с наиболее подходящими областями в сводной таблице. Это позволяет получить отправную точку для дальнейших экспериментов. После создания рекомендуемой сводной таблицы вы можете изучить различные ориентации и изменить порядок полей для получения нужных результатов.
Вы также можете скачать интерактивный учебник Создание первой сводной таблицы.
|
Рекомендуемые сводные таблицы |
Создание сводной таблицы вручную |
|
|
В области Имя поля вверху установите флажки для полей, которые вы хотите добавить в сводную таблицу. По умолчанию нечисловые поля добавляются в область строк, поля значений даты и времени — в область столбцов, а числовые поля — в область значений. Вы также можете вручную перетаскивать элементы в любые поля сводной таблицы. Если элемент больше не нужен, просто перетащите его за пределы списка полей или снимите его флажок. Возможность перестановки элементов — одна из особенностей сводной таблицы, благодаря которой можно быстро и просто изменять ее вид.
|
Список полей сводной таблицы |
Соответствующие поля в сводной таблице |
|
|
-
Суммировать по
По умолчанию поля сводной таблицы, которые находятся в области Значения, обобщаются с помощью функции
Затем измените функцию в разделе Суммировать по. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, «Сумма по полю имя_поля»), но вы можете изменить это имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Число….
Совет: Так как при изменении способа вычисления в разделе Суммировать по обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно выбрать пункт Найти (в меню «Изменить»), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.
-
Дополнительные вычисления
Значения также можно выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Вы можете настроить такие параметры в диалоговом окне Параметры поля на вкладке Дополнительные вычисления.
-
Отображение значения как результата вычисления и как процента
Просто перетащите элемент в раздел
При добавлении новых данных в источник необходимо обновить все сводные таблицы, созданные на его основе. Чтобы обновить одну сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить. При наличии нескольких сводных таблиц сначала выберите любую ячейку в любой сводной таблице, а затем на ленте откройте вкладку Анализ сводной таблицы, щелкните стрелку под кнопкой Обновить и выберите команду Обновить все.
Если вам больше не нужна сводная таблица, просто выделите ее и нажмите клавишу DELETE. Это не повлияет на другие данные и сводные таблицы или диаграммы. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.
Теперь сводную таблицу можно вставить в электронную таблицу в Excel Online.
Важно: Не рекомендуется создавать сводные таблицы и работать над ними в электронной таблице одновременно с другими пользователями.
-
Выделите таблицу или диапазон в электронной таблице.
-
На вкладке Вставка нажмите кнопку Сводная таблица.
-
В Excel появится диалоговое окно Создание сводной таблицы, в котором будет указан ваш диапазон или имя таблицы.
-
В разделе Укажите, куда следует поместить отчет сводной таблицы установите переключатель На новый лист или На существующий лист. При выборе варианта На существующий лист вам потребуется указать ячейку для вставки сводной таблицы.
Примечание: Указанная ячейка должна находиться за пределами таблицы или диапазона.
-
Нажмите кнопку ОК. Excel создаст пустую сводную таблицу и выведет список Поля сводной таблицы.
В области Поля сводной таблицы вверху установите флажки для полей, которые вы хотите добавить в сводную таблицу. По умолчанию нечисловые поля добавляются в область строк, поля значений даты и времени — в область столбцов, а числовые поля — в область значений. Вы также можете вручную перетаскивать элементы в любые поля сводной таблицы. Если элемент больше не нужен, просто перетащите его за пределы списка полей или снимите его флажок. Возможность перестановки элементов — одна из особенностей сводной таблицы, благодаря которой можно быстро и просто изменять ее вид.
|
Список полей сводной таблицы |
Соответствующие поля в сводной таблице |
|
|
-
Операция
По умолчанию поля сводной таблицы, которые находятся в области Значения, обобщаются с помощью функции СУММ. Если Excel считает данные текстом, к ним применяется функция СЧЁТ. Именно почему так важно не использовать разные типы данных для полей значений. Вы можете изменить функцию, которая по умолчанию используется для вычислений. Для этого щелкните стрелку справа от имени поля и выберите Параметры полей значений.
Затем измените функцию в разделе Операция. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, «Сумма по полю имя_поля»), но вы можете изменить имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Числовой формат.
Совет: Так как при изменении способа вычисления в разделе Операция обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно нажать кнопку Найти и заменить (CTRL+H), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.
-
Дополнительные вычисления
Значения также можно выводить в процентах от значения поля. В приведенном ниже примере мы изменили сумму расходов на % от общей суммы.
Вы можете настроить такие параметры в диалоговом окне Параметры поля значений на вкладке Дополнительные вычисления.
-
Отображение значения как результата вычисления и как процента
Просто перетащите элемент в раздел Значения дважды, а затем настройте параметры Операция и Дополнительные вычисления для каждой из копий.
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить
Если вам больше не нужна сводная таблица, просто выделите ее и нажмите клавишу DELETE. Это не повлияет на другие данные и сводные таблицы или диаграммы. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
См. также
Создание сводной диаграммы
Использование срезов для фильтрации данных сводной таблицы
Создание временной шкалы сводной таблицы для фильтрации дат
Создание сводной таблицы на основе модели данных для анализа сведений в нескольких таблицах
Упорядочение полей сводной таблицы с помощью списка полей
Изменение исходных данных сводной таблицы
Вычисление значений в сводной таблице
Удаление сводной таблицы
support.office.com
Фильтрация дат в сводной таблице или сводной диаграммы
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .
Диалоговое окно Пометить как таблицу дат отображается при нажатии кнопки Пометить как таблицу дат или выборе пункта Параметры таблицы дат на вкладке Конструктор окна Power Pivot. С помощью окна Пометить как таблицу дат вы можете определить уникальный столбец дат, позволяющий использовать дополнительные фильтры дат для данных Power Pivot в сводных отчетах Excel.
Дополнительные фильтры дат отображаются в названиях строк и столбцов сводной таблицы или сводной диаграммы при добавлении поля из таблицы дат в область «Названия строк» или «Названия столбцов» списка полей Power Pivot.
Чтобы изменить свойства дат:
-
В окне Power Pivot выберите таблицу, содержащую даты.
-
На вкладке «Конструктор» выберите Пометить как таблицу дат.
-
В открывшемся диалоговом окне выберите столбец, содержащий уникальные значения при отсутствии пустых значений.
-
Нажмите кнопку ОК.
Чтобы использовать дополнительные фильтры дат:
-
Перейдите к сводной таблице или сводной диаграмме в той же книге.
-
Добавьте столбец из таблицы дат в область Метки столбцов или Метки строк списка полей Power Pivot.
-
Нажмите на стрелку вниз рядом с областью Метки столбцов или Метки строк в сводной таблице.
-
Наведите курсор на пункт Фильтры даты и выберите фильтр из списка.
К началу страницы
support.office.com
Переименование сводной таблицы или сводной диаграммы
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
При создании сводной таблицы или сводной диаграммы Excel назначает имена по умолчанию для каждого из этих объектов с помощью следующих соглашения об именах: Сводная таблица1, сводная Таблица2, и т. д.; и диаграмма 1, 2 диаграммы и т. д. Однако вы можете изменить имя каждого объекта, чтобы сделать более понятное для вас.
Переименование сводной таблицы
-
Щелкните сводную таблицу.
-
Последовательно выберите пункты Работа со сводными таблицами > Анализ и в группе Сводная таблица щелкните текстовое поле Имя сводной таблицы.
Excel 2007 – 2010, последовательно выберите пункты Работа со сводными таблицами > Параметры и в группе Сводная таблица щелкните текстовое поле Имя сводной таблицы.
-
Введите новое имя.
-
Нажмите клавишу ВВОД.
Переименование отчета сводной диаграммы
-
Нажмите кнопку Сводная диаграмма.
-
Последовательно выберите пункты Работа со сводными диаграммами > » Анализ «, в группе » Сводная диаграмма «, щелкните текстовое поле Название диаграммы.
Excel 2007 – 2010, последовательно выберите пункты Работа со сводными диаграммами > вкладка » Макет «, в группе » Сводная диаграмма «, щелкните текстовое поле Название диаграммы.
-
Введите новое имя.
-
Нажмите клавишу ВВОД.
Переименование сводной таблицы
-
Щелкните сводную таблицу.
-
На вкладке ленты Анализ сводной таблицы щелкните текстовое поле Имя сводной таблицы.
-
Введите новое имя.
-
Нажмите клавишу ВВОД.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
См. также
Создание сводной таблицы
Использование списка полей упорядочение полей сводной таблицы
Создание сводной диаграммы
Использование срезов для фильтрации данных
Создание временной шкалы сводной таблицы для фильтрации дат
support.office.com
Вычисление значений в сводной таблице
В сводных таблицах можно рассчитывать данные разными способами. Вы узнаете о доступных методах вычислений, о влиянии типа исходных данных на вычисления и о том, как использовать формулы в сводных таблицах и на сводных диаграммах.
Доступные методы вычислений
Для вычисления значений в сводной таблице можно использовать любые из описанных ниже методов.
-
Функции сведения в полях значений. В сводной таблице в области значений отображаются сводные данные, вычисленные на основе исходных данных. Рассмотрим пример с такими исходными данными:
-
Сводная таблица и сводная диаграмма выглядят, как показано на рисунке ниже. Если создать сводную диаграмму на основе данных из сводной таблицы, то значения на диаграмме будут соответствовать вычислениям в связанной сводной таблице.
-
В сводной таблице поле столбца Месяц содержит элементы Март и Апрель. Поле строки Регион содержит элементы Север, Юг, Восток и Запад. Значение на пересечении столбца Апрель и строки Север — это общая выручка от продаж, определенная по исходным данным, для которых столбец Месяц содержит значение Апрель, а столбец Регион — значение Север.
-
На сводной диаграмме поле Регион может представлять собой поле категорий, в котором элементы Север, Юг, Восток и Запад отображаются как категории. Поле Месяц поле может быть полем рядов, в котором элементы Март, Апрель и Май отображаются как ряды, представленные в легенде. Поле значений с именем Сумма продаж может содержать маркеры данных, которые представляют общую выручку в каждом регионе за каждый месяц. Например, один маркер данных может представлять (своим положением на вертикальной оси, т. е. оси значений) сумму продаж за месяц Апрель в регионе Север.
-
Ниже перечислены функции сведения, с помощью которых можно вычислять поля значений. Эти функции доступны для всех типов исходных данных, кроме OLAP.
|
Функция |
Сведение данных |
|
Сумма |
Сумма значений. Функция по умолчанию для числовых данных. |
|
Количество |
Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых. |
|
Среднее |
Среднее арифметическое. |
|
Максимум |
Наибольшее значение. |
|
Минимум |
Наименьшее значение. |
|
Произведение |
Произведение значений. |
|
Количество чисел |
Количество числовых значений. Действует аналогично функции СЧЁТ. |
|
Стандартное отклонение |
Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. |
|
Несмещенное отклонение |
Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные. |
|
Дисперсия |
Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. |
|
Несмещенная дисперсия |
Дисперсия генеральной совокупности, которая содержит все сводимые данные. |
-
Настраиваемые вычисления. Служат для отображения значений на основе других элементов или ячеек в области данных. Например, можно отобразить значения в поле данных Сумма продаж как процент от продаж за месяц Март или как нарастающий итог по элементам в поле Месяц.
Для настраиваемых вычислений в полях значений доступны перечисленные ниже функции.
|
Функция |
Результат |
|
Без вычислений |
Значение, введенное в данное поле. |
|
% от общей суммы |
Значения в процентах от общей суммы всех значений или точек данных в отчете. |
|
% от суммы по столбцу |
Все значения в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду. |
|
% от суммы по строке |
Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории. |
|
Доля |
Значения в процентах от значения базового элемента в соответствующем базовом поле. |
|
% от суммы по родительской строке |
Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента по строкам). |
|
% от суммы по родительскому столбцу |
Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента по столбцам). |
|
% от родительской суммы |
Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента в выбранном базовом поле). |
|
Отличие |
Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле. |
|
Приведенное отличие |
Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле. |
|
С нарастающим итогом в поле |
Значение в виде нарастающего итога для последовательных элементов в базовом поле. |
|
% от суммы с нарастающим итогом в поле |
Значение в виде нарастающего итога в процентах для последовательных элементов в базовом поле. |
|
Сортировка от минимального к максимальному |
Ранг выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно. |
|
Сортировка от максимального к минимальному |
Ранг выбранных значений в определенном поле с учетом того, что наибольшему значению в поле присваивается значение 1, а каждому меньшему значению — более высокий ранг. |
|
Индекс |
Рассчитывает значения следующим образом: ((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)). |
-
Формулы. Если функции сведения и настраиваемые вычисления не дают желаемых результатов, вы можете создать собственные формулы в вычисляемых полях и вычисляемых объектах. Например, можно добавить вычисляемый объект с формулой расчета комиссионных за продажу, которые могут быть разными в различных регионах. Эти комиссионные будут автоматически включены в промежуточные и общие итоги в отчете.
Влияние типа источника данных на вычисления
Доступность вычислений и параметров в отчете зависит от того, получены ли исходные данные из базы данных OLAP.
-
Вычисления на основе исходных данных OLAP. При создании сводных таблиц на основе кубов OLAP сводные значения вычисляются на сервере OLAP еще до отображения результатов в Excel. В сводной таблице невозможно изменить способ вычисления этих значений. Например, вы не сможете выбрать другую функцию сведения для вычисления полей данных или промежуточных итогов и добавить вычисляемые поля или вычисляемые объекты.
Кроме того, если сервер OLAP предоставляет вычисляемые поля, называемые «вычисляемыми элементами», вы увидите их в списке полей сводной таблицы. Вы также увидите все вычисляемые поля и вычисляемые объекты, созданные с помощью макросов, которые написаны на языке Visual Basic для приложений (VBA) и хранятся в книге, но не сможете их изменить. Если вам нужны дополнительные типы вычислений, обратитесь к администратору базы данных OLAP.
Если исходные данные получены из базы данных OLAP, то при вычислении промежуточных и общих итогов можно включить или исключить значения для скрытых элементов.
-
Вычисления на основе исходных данных не из источника данных OLAP. В сводных таблицах, основанных на внешних данных других типов или на данных листа Excel, для вычисления полей значений, содержащих числовые данные, используется функция «Сумма», а для вычисления полей данных, содержащих текст, — функция «Количество». Для дальнейшего анализа и обработки своих данных вы можете выбрать другие функции сведения, например «Среднее», «Максимум» или «Минимум». Кроме того, можно создавать собственные формулы, в которых используются элементы отчета или другие данные листа. Для этого нужно создать вычисляемое поле или вычисляемый объект в поле.
Использование формул в сводных таблицах
Формулы можно создавать только в отчетах, которые основаны на исходных данных, полученных не из источника данных OLAP. В отчетах, основанных на базе данных OLAP, формулы не поддерживаются. При использовании формул в сводных таблицах нужно учитывать описанные ниже правила синтаксиса и поведения формул.
-
Элементы формулы сводной таблицы. В формулах, которые создаются для вычисляемых полей и вычисляемых объектов, можно использовать операторы и выражения, как и в других формулах на листе. Также можно использовать константы и ссылаться на данные из отчета, но не допускается использование ссылок на ячейки и определенных имен. Невозможно использовать функции листа, для которых нужны аргументы в виде ссылок на ячейки или определенных имен, а также формулы массива.
-
Имена полей и элементов. В Excel имена полей и элементов используются для идентификации этих элементов отчета в формулах. В приведенном ниже примере для данных в диапазоне C3:C9 используется имя поля Молоко. Для вычисляемого объекта в поле Тип, оценивающего объем продаж нового продукта на основе данных о продажах молочных продуктов, можно использовать формулу =Молоко * 115%.
Примечание: На сводной диаграмме имена полей отображаются в списке полей сводной таблицы, а имена элементов можно просмотреть в каждом раскрывающемся списке полей. Не следует путать эти имена с теми, которые отображаются в подсказках к диаграммам и соответствуют именам рядов и точек данных.
-
Формулы работают с итоговыми суммами, а не с отдельными записями. Формула для вычисляемого поля оперирует суммой исходных данных для каждого используемого поля. Например, формула вычисляемого поля =Продажи * 1,2 умножает сумму продаж для каждого типа и региона на 1,2, а не умножает каждое отдельное значение продаж на 1,2 с последующим суммированием полученных величин.
Формулы для вычисляемых объектов оперируют отдельными записями. Например, формула вычисляемого объекта =Молоко * 115% умножает каждое отдельное значение продаж молочных продуктов на 115 %, после чего полученные величины суммируются в области «Значения».
-
Пробелы, цифры и символы в именах. В имени, которое содержит два или несколько полей, их порядок не имеет значения. В примере выше ячейки C6:D6 могут называться ‘Апрель Север’ или ‘Север Апрель’. Имена, которые состоят из нескольких слов либо содержат цифры или символы, нужно заключать в одинарные кавычки.
-
Итоги. Формулы не могут ссылаться на итоговые значения (в примере выше — это Сумма за март, Сумма за апрель и Общий итог).
-
Имена полей в ссылках на элементы. Вы можете включить имя поля в ссылку на элемент. Имя элемента должно быть заключено в квадратные скобки, например: Регион[Север]. Используйте этот формат, чтобы избежать ошибок #ИМЯ?, которые возникают, если два элемента в двух разных полях отчета имеют одинаковые имена. Например, если в отчете есть два элемента с именем «Мясо» в полях «Тип» и «Категория», можно избежать появления ошибок #ИМЯ?, ссылаясь на эти элементы следующим образом: Тип[Мясо] и Категория[Мясо].
-
Ссылки на элементы по позиции. Вы можете сослаться на элемент, указав его позицию в отчете (с учетом того, какие элементы фактически отображаются и как они отсортированы в настоящий момент). Тип[1] — это Молоко, а Тип[2] — Морепродукты. Когда позиции элементов изменятся, например, если какие-то из них будут скрыты или снова отображены, такая ссылка, возможно, будет указывать на другой элемент. Скрытые элементы не учитываются в этом индексе.
Для ссылки на элементы можно использовать относительные позиции. Они определяются относительно вычисляемого объекта, содержащего формулу. Если текущим регионом является Юг, то Регион[-1] — это Север. Если текущим регионом является Север, то Регион[+1] — это Юг. Например, для вычисляемого объекта можно использовать формулу =Регион[-1] * 3%. Если позиция, которую вы указали, находится перед первым или после последнего элемента в поле, формула возвращает ошибку #ССЫЛКА!.
Использование формул в сводных диаграммах
Чтобы использовать формулы в сводной диаграмме, их нужно создать в связанной сводной таблице. Там вы увидите отдельные значения, из которых состоят данные, а затем сможете посмотреть на результаты в графическом представлении на сводной диаграмме.
Например, на этой сводной диаграмме представлены данные о продажах для каждого продавца по регионам:
Чтобы посмотреть, как будут выглядеть объемы продаж, если увеличатся на 10 %, можно создать вычисляемое поле в связанной сводной таблице и воспользоваться формулой =Продажи * 110%.
Результат сразу отображается на сводной диаграмме, как показано на этом рисунке:
Чтобы отобразить отдельный маркер данных для продаж в регионе «Север» за вычетом транспортных расходов, которые составляют 8 %, можно создать в поле «Регион» вычисляемый объект с такой формулой: =Север – (Север * 8%).
Диаграмма будет выглядеть следующим образом:
Однако вычисляемый объект, созданный в поле «Продавец», будет отображаться как ряд, представленный в легенде, и появится на диаграмме в виде точки данных в каждой категории.
support.office.com
Использование нескольких таблиц для создания сводной таблицы
Сводные таблицы удобно использовать для анализа данных и создания отчетов с ними. А если это реляционные данные (т. е. такие, которые хранятся в отдельных таблицах, но при этом их можно объединить благодаря общим значениям), вы можете всего за несколько минут создать такую сводную таблицу:
Чем примечательна эта сводная таблица? Обратите внимание: в списке полей справа отображается не одна таблица, а целая коллекция таблиц, содержащих поля, которые могут быть объединены в отдельную сводную таблицу для анализа данных в различных представлениях. Нет никакой необходимости в форматировании или подготовке данных вручную. Вы можете создать сводную таблицу, основанную на связанных таблицах, сразу после импорта данных.
Чтобы объединить несколько таблиц в списке полей сводной таблицы:
Ниже приведена процедура импорта нескольких таблиц из базы данных SQL Server.
-
Убедитесь, что вам известны имя сервера, имя базы данных и учетные данные, необходимые для подключения к SQL Server. Все необходимые сведения можно получить у администратора базы данных.
-
Щелкните Данные > Получение внешних данных > Из других источников > С сервера SQL Server.
-
В поле Имя сервера введите сетевое имя компьютера с запущенным сервером SQL Server.
-
В разделе Учетные данные входа в систему выберите команду Использовать проверку подлинности Windows, если вы подключаетесь с помощью своих учетных данных. В противном случае введите имя пользователя и пароль, предоставленные администратором базы данных.
-
Нажмите клавишу ВВОД и в разделе Выбор базы данных и таблицы выберите нужную базу данных, а затем щелкните Разрешить выбор нескольких таблиц.
-
Выберите необходимые для работы таблицы вручную, если вы знаете, какие именно нужны вам. Или же выберите одну или две, а затем щелкните Выбор связанных таблиц для автовыбора таблиц, связанных с уже указанными.
-
Если установлен флажок Импорт связи между выбранными таблицами, оставьте его, чтобы разрешить Excel воссоздать аналогичные связи таблиц в книге.
-
Нажмите Готово.
-
В диалоговом окне Импорт данных выберите элемент Отчет сводной таблицы.
-
Нажмите кнопку ОК, чтобы начать импорт и заполнить список полей.
Обратите внимание: список полей содержит несколько таблиц. Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ, СТРОКИ или СТОЛБЦЫ.
-
Перетащите числовые поля в область ЗНАЧЕНИЯ. Например, если используется образец базы данных Adventure Works, вы можете перетащить поле «ОбъемПродаж» из таблицы «ФактПродажиЧерезИнтернет».
-
Перетащите поля даты или территории в область СТРОКИ или СТОЛБЦЫ, чтобы проанализировать объем продаж по дате или территории сбыта.
-
Иногда нужно создать связь между двумя таблицами, прежде чем использовать их в сводной таблице. Если появится сообщение о необходимости такой связи между таблицами, щелкните Создать, чтобы начать работу с ними.
Работа с другими типами баз данных
-
Для использования других реляционных баз данных, например Oracle, может понадобиться установить дополнительное клиентское программное обеспечение. Обратитесь к администратору базы данных, чтобы уточнить, есть ли такая необходимость.
-
Вы можете импортировать несколько таблиц из приложения Access. Подробнее об этом можно узнать в статье Учебник. Анализ данных сводных таблиц с помощью модели данных в Excel.
-
Импорт таблиц из других источников
Помимо SQL Server, вы можете импортировать таблицы из ряда других реляционных баз данных.
Реляционные базы данных — это не единственный источник данных, который поддерживает работу с несколькими таблицами в списке полей сводной таблицы. Вы можете использовать таблицы в своей книге или импортировать каналы данных, а затем интегрировать их с другими таблицами данных в книге. Чтобы все эти несвязанные данные работали вместе, нужно каждую таблицу добавить в модель данных, а затем создать связи между ними с помощью соответствующих значений полей.
Использование модели данных для создания новой сводной таблицы
Возможно, вы создали связи между таблицами в модели данных и теперь готовы использовать эти данные для анализа. Ниже описано, как создать новую сводную таблицу или сводную диаграмму с помощью модели данных в книге.
-
Щелкните любую ячейку на листе.
-
Выберите Вставка > Сводная таблица.
-
В диалоговом окне Создание сводной таблицы в разделе Выберите данные для анализа щелкните Использовать внешний источник данных.
-
Нажмите кнопку Выбрать подключение.
-
На вкладке Таблицы в разделе Модель данных этой книги выберите Таблицы в модели данных книги.
-
Нажмите кнопку Открыть, а затем — ОК, чтобы отобразить список полей, содержащий все таблицы в модели.
Дополнительные сведения о сводных таблицах и модели данных
support.office.com
Выбор данных в сводной таблице
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .
Применять форматирование, скопируйте данные и внесите изменения в сводной таблице, можно выбрать какой-либо из отдельных ячеек и областей данных с помощью команд или с помощью мыши. Изменение вида указателя мыши в зависимости от того, что вы попытались выберите:
-
Выделите ячейки, с помощью регулярных указатель Microsoft Office Excel мыши .
-
Чтобы выбрать области данных, поместите указатель мыши в верхней или левой стороны полей и надписей, чтобы указатель мыши принял вниз стрелку или Стрелка вправо .
Примечание: Если вы не видите указатель изменить фигуру, на вкладке Параметры в группе » действия «, нажмите кнопку выбрать и затем убедитесь, что выбран Разрешить выделение .
Выбор отдельных ячеек
-
Наведите курсор на первую ячейку, которую требуется выделить, убедиться, что указатель мыши находится .
-
Щелкните одну ячейку, чтобы выбрать ее, или щелкните и перетащите указатель мыши для выбора нескольких ячеек.
-
Выберите сводную таблицу.
-
На вкладке Параметры в группе Действия нажмите кнопку Выбор и затем выберите Таблицу целиком.
Поместите указатель мыши принял вид вниз стрелку на верхнюю границу поля и нажмите кнопку.
Например, для выбора элементов «Восток» и «Запад» установите указатель мыши на верхнюю границу поля Диапазон и один раз щелкните левой кнопкой мыши.
-
Выберите элементы.
-
На вкладке Параметры в группе действия нажмите кнопку выбрать и выберите требуется выделить:
Метки и значения. оба флажка.
Значения Чтобы выбрать только значения для элементов .
Метки , чтобы выбрать только подписи элементов.
Поместите указатель мыши принял вид вниз стрелку на верхнюю границу элемента поля столбца или поместите указатель мыши принял вид стрелки вправо к левому краю элемент поля строки и нажмите кнопку.
Например, для выбора обеих строк элемента Гольф щелкните левую границу Гольф.
-
Поместите указатель мыши принял вид вниз стрелку на верхнюю границу элемента поля столбца или наведите указатель к левому краю элемента поля строки указатель примет вид стрелки вправо .
-
Щелкните левой кнопкой мыши один раз и затем еще несколько раз, делая паузы между щелчками так, чтобы они не слились в двойной щелчок.
Например, для выбора только строки Квартал1 элемента Гольф щелкните левую границу Гольф, а затем щелкните ее еще раз.
Если сводная таблица содержит несколько подписей строк, нажмите кнопку несколько раз, пока не будет выделена только элементы, которые вы хотите. Например чтобы выделить один экземпляр элемента в сводной таблице содержится четыре названия строк, можно щелкнуть четыре раза.
-
Поместите указатель мыши принял вид вниз стрелку на верхнюю границу элемента поля столбца или наведите указатель к левому краю элемента поля строки указатель примет вид стрелки вправо .
-
Щелкните столько раз при необходимости выберите один из элементов, которые вы хотите. Дополнительные сведения читайте в статье выделите один экземпляр элемента.
-
Нажмите клавишу SHIFT и щелкните левой кнопкой мыши или, удерживая нажатой клавишу CTRL, выберите в том же поле дополнительные элементы.
-
Для отмены выбора элемента щелкните элемент, удерживая нажатой клавишу CTRL.
-
Наведите указатель на верхнюю границу промежуточный итог или итог в названия столбцов пока указатель мыши принял вид вниз стрелку или точки по левому краю промежуточный итог или итоговые значения в строке подписи указатель примет вид стрелки вправо .
-
Щелкните левой кнопкой мыши, чтобы выбрать все окончательные итоги метки, затем щелкните еще раз, чтобы выбрать только текущий промежуточный или окончательный итог.
support.office.com
Сводная таблица в Excel из нескольких таблиц как сделать
Сводная таблица в Excel – удобный инструмент для анализа и представления данных. Но что делать, если данные находятся в разных источниках? Разберем, как сделать сводную таблицу из нескольких листов в Excel.
Даже если есть ERP и BI-системы, без Excel финансовому директору не обойтись. Всевозможные расчеты, сводные таблицы, удобные графики — в Excel можно сделать практически все что угодно. Но нужно знать, как это сделать.
Сводная таблица в Excel средствами Power Query
Для начала необходимо отметить, что Excel может работать с исходными таблицами различного размера. Но заголовки и шапка этих таблиц должны быть одинаковы. Это нужно для того, чтобы программа правильно интерпретировала используемые данные. В противном случае может возникать ошибка.
Допустим, необходимо создать единую сводную таблицу, данные для которой надо взять из разных листов. Такая ситуация может возникать в случае, если в компании несколько распределенных точек сбыта, складских помещений, разные заказчики одинаковых групп товаров. В таком случае отчеты будут предоставлять разные филиаы. И чтобы их адекватно проанализировать, имеет смысл создать общую для всей компании таблицу данных, на основании которой в дальнейшем будет построена сводная таблица.
В этом случае необходимо создать чистый новый лист в программе Excel.
В этом листе перейти во вкладку «Данные».
Скачайте дополнительный материал к статье:
Далее нажать «Создать запрос», в выпавшем списке выбрать «Из файла» и затем – «Из книги». Создадим сводную таблицу из нескольких листов на примере – два магазина прислали отчеты о наличии у них ящиков различного цвета на стеллажах. Данные каждого магазина сохранены на одном листе. Из этих листов сформирована «Книга1», с которой мы работаем.
В появившемся окне надо указать книгу, откуда программа должна взять данные и нажать кнопку «Импорт».
Появится окно под названием «Навигатор». В нем надо выбрать лист, из которого будут взяты данные. Указать можно любой лист.
Полезный материал про работу в Excel!
Программа покажет данные в окне предпросмотра, которые предстоит взять из указанного листа.
В нашем примере видно, что указанный лист содержит множество ячеек с данными «null». Это неверно, так как программа будет обрабатывать и эти ячейки. Чтобы сократить область обрабатываемых значений и удалить такие нулевые ячейки, необходимо исправить исходный файл. Для этого нужно перейти в исходную таблицу и нажать «Ctrl + End». Будет выделена последняя активная ячейка таблицы. Надо удалить все ячейки правее и ниже таблицы, добиваясь того, чтобы при нажатии «Ctrl + End» становилась активной нижняя правая ячейка таблицы.
После этого источник данных не будет содержать лишней информации.
Дальше необходимо отредактировать данные в разделе «Параметры запроса».
Можно удалить строчки «Навигация» и «Измененный тип». И приступить к редактированию данных в разделе «Источник». В главном окне редактора будет отображаться перечень всех листов указанной книги. В нашем случае «Лист1» и «Лист2».
Далее надо выбрать только нужную информацию. В контекстном меню колонки «Data» выбрать «Удалить другие столбцы».
Затем в строке «Data» нажать иконку с двумя стрелками, как указано на рисунке.
В появившемся окне снять галочку с пункта «Использовать исходное имя столбца как префикс». И нажать «ОК». Появится таблица в которой собраны все данные.
Далее необходимо убрать лишние заголовки – «шапки». Для этого надо нажать «Использовать первую строку в качестве заголовков».
Таблица будет перестроена. Дублирующую строку с «шапкой» можно удалить. Для этого в фильтре столбца «Склад» снять галочку с пункта «Склад» и нажать «ОК». Затем в этом же фильтре нажать «Удалить пустые». Соответствующие строки будут удалены.
Далее необходимо сохранить полученную таблицу. Нажать кнопку «Закрыть и загрузить», далее в меню – «Закрыть и загрузить в…».
В появившемся окне «Загрузить в» поставить переключатель в позицию «Только создать подключение» и нажать кнопку «Загрузить». Появится запрос, на основании которого и будет строиться сводная таблица.
Далее, чтобы построить сводную таблицу, нужно во вкладке «Вставка» нажать кнопку «Сводная таблица».
В появившемся окне установить переключатель в положение «Использовать внешний источник данных» и нажать кнопку «Выбрать подключение».
В появившемся окне выбрать имя сформированного запроса, в нашем случае – «сводная» и нажать кнопку «Открыть».
Появится конструктор сводной таблицы. Данные можно добавлять и перемещать как в обычной сводной таблице.
Сводная таблица в версиях Excel до 2016 года
В программах Excel, изданных до 2003 года включительно, сводные таблицы из разных источников создавались через опцию «построить сводную по нескольким диапазонам консолидации». Однако подобное построение таблицы не дает возможности полноценно анализировать весь объем полученной информации. Таблица, построенная таким образом, может использоваться только для самого простого анализа данных.
В версиях с 2007 года надо было добавлять специальный «Мастер сводных таблиц», где новые диапазоны данных по одному указывались через диалоговые окна. Полученные сводные таблицы также обладали достаточно ограниченной функциональностью.
Гость, уже успели прочесть в свежем номере?
fd.ru