Меню

Excel сводные таблицы уровни

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

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

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

Коллекция функции

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

В кубе OLAP или в pivotTable модели данных выберите элемент в поле (например, Аксессуары в нашем примере) (например, поле Category в нашем примере).

Одновременно можно развернуть только один элемент.

Нажмите кнопку Быстрое Кнопка , которая появится в правом нижнем углу области выделения.

Кнопка

В окне Изучение выберите элемент, который вы хотите изучить, и нажмите кнопку Развернуть.

Кнопка

Теперь для этого элемента вы увидите подкатегории (в данном случае — дополнительные продукты).

Сводная таблица с детализированными элементами

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

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

Если в вашей pivotTableесть сгруппируемые элементы , вы можете детализовать имя группы так же, как и другие элементы.

Нельзя детализировать именные наборы (наборы часто используемых элементов или элементы из разных иерархий).

Детали для изучения подробных сведений

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

В иерархии, в которые вы в сверлены, выберите элемент, на который вы хотите сделать.

Нажмите кнопку Быстрое Кнопка , которая появится в правом нижнем углу области выделения.

В окне Изучение выберите элемент, который вы хотите изучить, и нажмите кнопку Drill Up (Развернуть).

Кнопка

Теперь вы видите данные более высокого уровня.

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

Вы можете одновременно отсверлить несколько уровней иерархии. Щелкните правой кнопкой мыши элемент, который вы хотите отсвернуть, выберите пункт Drill Down/Drill Upи выберите уровень, до который вы хотите сделать.

Если в вашей pivotTableесть сгруппируемые элементы, вы можете детализова до имени группы.

Нельзя детализировать наборы имен (наборы часто используемых элементов или элементы из разных иерархий).

В Excel в Интернете невозможно найти данные в #x0.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник



Как сделать сводную таблицу в Excel – пошаговая инструкция для чайников

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

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

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

  1. Что такое сводная таблица?
  2. Как создать сводную таблицу.
    • 1. Организуйте свои исходные данные
    • 2. Создаем и размещаем макет
    • 3. Как добавить поле
    • 4. Как удалить поле из сводной таблицы?
    • 5. Как упорядочить поля?
    • 6. Выберите функцию для значений (необязательно)
    • 7. Используем различные вычисления в полях значения (необязательно)
  3. Работа со списком показателей сводной таблицы
  4. Закрытие и открытие панели редактирования.
  5. Воспользуйтесь рекомендациями программы.
  6. Давайте улучшим результат.
  7. Как обновить сводную таблицу.
  8. Как переместить на новое место?
  9. Как удалить сводную таблицу?

Что такое сводная таблица?

Это инструмент для изучения и обобщения больших объемов данных, анализа связанных итогов и представления отчетов. Они помогут вам:

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

Например, у вас множество записей в электронной таблице с цифрами продаж шоколада:

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

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

Вот посмотрите сами.

Этот скриншот демонстрирует лишь несколько из множества возможных вариантов анализа продаж. И далее мы рассмотрим примеры построения сводных таблиц в Excel 2016, 2013, 2010 и 2007.

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

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

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

1. Организуйте свои исходные данные

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

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

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

2. Создаем и размещаем макет

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

Откроется окно « Создание . ». Убедитесь, что в поле Диапазон указан правильный источник данных. Затем выберите местоположение для свода:

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

Нажатие ОК создает пустой макет без цифр в целевом местоположении, который будет выглядеть примерно так:

  • В большинстве случаев имеет смысл размещать на отдельном рабочем листе. Это особенно рекомендуется для начинающих.
  • Ежели вы берете информацию из другой таблицы или рабочей книги, включите их имена, используя следующий синтаксис: [workbook_name]sheet_name!Range. Например, [Книга1.xlsx] Лист1!$A$1:$E$50. Конечно, вы можете не писать это все руками, а просто выбрать диапазон ячеек в другой книге с помощью мыши.
  • Возможно, было бы полезно построить таблицу и диаграмму одновременно. Для этого в Excel 2016 и 2013 перейдите на вкладку « Вставка», щелкните стрелку под кнопкой « Сводная диаграмма», а затем нажмите « Диаграмма и таблица». В версиях 2010 и 2007 щелкните стрелку под сводной таблицей, а затем — Сводная диаграмма.
  1. Организация макета.

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

  • Раздел «Поле» содержит названия показателей, которые вы можете добавить. Они соответствуют именам столбцов исходных данных.
  • Раздел « Макет» содержит область « Фильтры», «С толбцы», «Строки» и « Значения». Здесь вы можете расположить в нужном порядке поля.

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

3. Как добавить поле

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

По умолчанию Microsoft Excel добавляет поля в раздел « Макет» следующим образом:

  • Нечисловые добавляются в область Строки;
  • Числовые добавляются в область значений;
  • Дата и время добавляются в область С толбцы.
Читайте также:  Прямоугольная часть таблицы информатика

4. Как удалить поле из сводной таблицы?

Чтобы удалить любое поле, вы можете выполнить следующее:

  • Снимите флажок напротив него, который вы ранее установили.
  • Щелкните правой кнопкой мыши поле и выберите « Удалить……».

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

5. Как упорядочить поля?

Вы можете изменить расположение показателей тремя способами:

  1. Перетащите поле между 4 областями раздела с помощью мыши. В качестве альтернативы щелкните и удерживайте его имя в разделе « Поле», а затем перетащите в нужную область в разделе « Макет». Это приведет к удалению из текущей области и его размещению в новом месте.
  1. Щелкните правой кнопкой мыши имя в разделе « Поле» и выберите область, в которую вы хотите добавить его:
  1. Нажмите на поле в разделе « Макет», чтобы выбрать его. Это сразу отобразит доступные параметры:

Все внесенные вами изменения применяются немедленно.

Ну а ежели спохватились, что сделали что-то не так, не забывайте, что есть «волшебная» комбинация клавиш CTRL+Z, которая отменяет сделанные вами изменения (если вы не сохранили их, нажав соответствующую клавишу).

6. Выберите функцию для значений (необязательно)

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

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

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

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

В Excel 2010 и ниже опция « Суммировать значения по» также доступна на ленте — на вкладке « Параметры» в группе « Расчеты».

7. Используем различные вычисления в полях значения (необязательно)

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

Это называется « Дополнительные вычисления». Доступ к ним можно получить, открыв вкладку «Параметры . », как это описано чуть выше.

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

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

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

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

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

Вы также можете изменить размер панели по горизонтали, перетаскивая разделитель, который отделяет панель от листа.

Закрытие и открытие панели редактирования.

Закрыть список полей в сводной таблице так же просто, как нажать кнопку « Закрыть» (X) в верхнем правом углу панели. А вот как заставить его появиться снова – уже не так очевидно 🙂

Чтобы снова отобразить его, щелкните правой кнопкой мыши в любом месте таблицы и выберите « Показать . » в контекстном меню.

Также можно нажать кнопку « Список полей» на ленте, которая находится на вкладке меню « Анализ».

Воспользуйтесь рекомендациями программы.

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

  1. Нажмите любую ячейку в исходном диапазоне ячеек или таблицы.
  2. На вкладке « Вставка» выберите « Рекомендуемые сводные таблицы». Программа немедленно отобразит несколько макетов, основанных на ваших данных.
  3. Щелкните на любом макете, чтобы увидеть его предварительный просмотр.
  4. Если вас устраивает предложение, нажмите кнопку «ОК» и добавьте понравившийся вариант на новый лист.

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

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

Давайте улучшим результат.

Теперь, когда вы знакомы с основами, вы можете перейти к вкладкам « Анализ» и « Конструктор» инструментов в Excel 2016 и 2013 ( вкладки « Параметры» и « Конструктор» в 2010 и 2007). Они появляются, как только вы щелкаете в любом месте таблицы.

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

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

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

Чтобы настроить макет определенного поля, щелкните на нем, затем нажмите кнопку « Параметры» на вкладке « Анализ» в Excel 2016 и 2013 (вкладка « Параметры» в 2010 и 2007). Также вы можете щелкнуть правой кнопкой мыши поле и выбрать « Параметры . » в контекстном меню.

На снимке экрана ниже показан новый дизайн и макет.

Источник

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

Russian (Pусский) translation by Ellen Nelson (you can also view the original English article)

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

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

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

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

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

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

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

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

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

1. Срезы

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

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

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

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

Insert Slicers in an advanced Excel PivotTableInsert Slicers in an advanced Excel PivotTable Insert Slicers in an advanced Excel PivotTableОтметьте несколько полей, чтобы включить эти столбцы, каждый из которых будет являться срезом.

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

Читайте также:  Таблицы для детей почтой

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Calculated Field with Numeric value in ExcelCalculated Field with Numeric value in Excel Calculated Field with Numeric value in ExcelЯ умножил общую стоимость заказа на 1.05 для расчета стоимости, включая налог с продаж; вы можете использовать числовые значения наряду с существующими полями.

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

5. Рекомендуемые сводные таблицы

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

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

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

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

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

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

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

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

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

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

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

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

Источник

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

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

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

Коллекция функции

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

В кубе OLAP или в pivotTable модели данных выберите элемент в поле (например, Аксессуары в нашем примере) (например, поле Category в нашем примере).

Одновременно можно развернуть только один элемент.

Нажмите кнопку Быстрое Кнопка , которая появится в правом нижнем углу области выделения.

Кнопка

В окне Изучение выберите элемент, который вы хотите изучить, и нажмите кнопку Развернуть.

Кнопка

Теперь для этого элемента вы увидите подкатегории (в данном случае — дополнительные продукты).

Сводная таблица с детализированными элементами

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

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

Если в вашей pivotTableесть сгруппируемые элементы , вы можете детализовать имя группы так же, как и другие элементы.

Нельзя детализировать именные наборы (наборы часто используемых элементов или элементы из разных иерархий).

Детали для изучения подробных сведений

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

В иерархии, в которые вы в сверлены, выберите элемент, на который вы хотите сделать.

Нажмите кнопку Быстрое Кнопка , которая появится в правом нижнем углу области выделения.

В окне Изучение выберите элемент, который вы хотите изучить, и нажмите кнопку Drill Up (Развернуть).

Читайте также:  Научные статьи анализ таблиц

Кнопка

Теперь вы видите данные более высокого уровня.

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

Вы можете одновременно отсверлить несколько уровней иерархии. Щелкните правой кнопкой мыши элемент, который вы хотите отсвернуть, выберите пункт Drill Down/Drill Upи выберите уровень, до который вы хотите сделать.

Если в вашей pivotTableесть сгруппируемые элементы, вы можете детализова до имени группы.

Нельзя детализировать наборы имен (наборы часто используемых элементов или элементы из разных иерархий).

В Excel в Интернете невозможно найти данные в #x0.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Как сделать сводную таблицу в Excel: пошаговая инструкция

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

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

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

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

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

Шапка сводной таблицы

Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.

Сведение данных с помощью формулы

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

— Можно ли отчет сделать не по выручке, а по прибыли?

— Можно ли товары показать по строкам, а регионы по столбцам?

— Можно ли такие таблицы делать для каждого менеджера в отдельности?

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

Рассмотрим, как создать сводную таблицу в Excel.

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

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

Кнопки построения сводной таблицы на ленте

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

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

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

Диалоговое окно создания сводной таблицы

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

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

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

Панель управления полями сводной таблицы

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

Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.

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

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

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

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

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

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

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

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

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

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

На ее построение потребовалось буквально 5-10 секунд.

Работа со сводными таблицами в Excel

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

Заменим выручку на прибыль.

Товары и области меняются местами также перетягиванием мыши.

Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.

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

Источник данных сводной таблицы Excel

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

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

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

3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.

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

В целом требований немного, но их следует знать.

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

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

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

или
через команду во вкладке Данные – Обновить все.

Обновить все

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

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

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

Источник

Adblock
detector