Excel 2019 сводные таблицы: Вставка сводной таблицы

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

Excel в Интернете

Excel в Интернете

Excel в Интернете

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

• Знакомство с Excel в Интернете
  Статья
• Создание формул
  Статья
• Вставка сводной таблицы
  Статья
• Условное форматирование
  Статья
• Мгновенное заполнение
  Статья
• Анализ данных для получения полезной информации
  Статья
• Совместная работа
  Статья

Далее: PowerPoint в Интернете

Excel для Интернета Еще. ..Меньше

Проверьте, как это работает!

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

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

2. На вкладке Вставка нажмите кнопку Сводная таблица.

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

4. В поле Таблица/диапазон проверьте диапазон ячеек.

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

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

6. Нажмите кнопку ОК.

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

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

   значений.

8. Чтобы переместить поле из одной области в другую, перетащите его в целевую область.

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

Хотите узнать больше?

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

Обзор сводных таблиц и сводных диаграмм

Справка и обучение по Excel

Быстрое начало работы с Office в Интернете

Excel.

Часть 3. Сводные таблицы

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

Зачем нужны сводные таблицы?

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

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

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

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

• Это руководство подходит для работы в Google Spreadsheets.

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

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

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

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

Простая сводная таблица с суммой значений

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

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

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

Сортировка по убыванию

Фильтры в сводной таблице

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

Фильтрация по значению

Процентное отображение значений

Данные в сводной таблице могут быть отображены в разных форматах. Сейчас количество усыновленных отображено в абсолютных числах, но мы можем показать их и в процентах. Для этого надо в поле «Значения» в параметре «Всего усыновлено» выбрать «Показывать как» – «% от итоговой суммы». Тогда мы увидим, например, что на Италию пришлось 68% детей из усыновленных за рубеж в 2019 году.

% от итоговой суммы

Добавление нескольких значений

В сводной таблице можно добавлять сразу несколько значений. Например, рядом с общим количеством усыновленных в страны показать, сколько из них – детей с инвалидностью. Для этого добавляем еще одного «Значение» – «Усыновлено детей с инвалидностью», а в окне «Суммировать по» – параметр SUM. Можно добавить еще одно значение с возрастом. Например, узнать, сколько в эти страны было усыновлено детей в возрасте до 1 года. Добавляем еще одно «Значение» – «до 1 года», а в окне «Суммировать по» – параметр SUM. Тогда мы увидим, что иностранные усыновители не брали в свои семьи младенцев – по крайней мере в 2019 году.

Несколько значений

Перемена строк

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

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

Если нас интересует, сколько детей уехало из какого-то конкретного региона, мы снова можем воспользоваться «Фильтрами». В поле «Фильтр» нажимаем «Добавить» – параметр «Регион». Если нажать на выпадающее меню под надписью «Статус», мы увидим, что сейчас там выделены все регионы. Нажмем «Очистить» и введем в поле только один регион, например, «Москва». В появившейся сводной таблице мы увидим, что в 2019 году иностранцы усыновили всего двух детей из Москвы.

Отображение одного значения

Суммирование значений разными способами

До этого момента все значения мы суммировали с помощью функции SUM, но в выпадающем меню есть и другие варианты, позволяющие производить расчеты с данными: среднее, минимум, максимум и другие. Давайте воспользуемся функцией COUNTUNIQUE, которая подсчитывает количество уникальных значений или диапазонов в наборе данных. Например, мы хотим узнать, сколько всего регионов участвуют в международном усыновлении. Для этого очистим таблицу и создадим новую. В «Строки» добавим «Страна». В «Значения» – «Регион» с параметром COUNTUNIQUE. В строке «Итого» сводной таблицы мы увидим, что всего 30 регионов участвовали в усыновлении.

Эта же таблица показывает нам, из какого количества регионов каждая страна усыновляла детей (функция COUNTUNIQUE посчитала количество уникальных пар «Страна» – «Регион»). Если мы добавим рядом еще одно «Значение» – «Всего усыновлено», мы увидим, что, например, все усыновленные Аргентиной дети – а их было 23 – родом из одного региона.

Функция COUNTUNIQUE

Чтобы посмотреть, из какого, мы можем добавить еще одну «Строку» – «Регион». Такой вид таблицы позволяет нам увидеть, какие страны усыновляли детей из каких регионов. В ней мы мы увидим, что Аргентина усыновила всех 23 детей из Пермского края. Возможно, за этим фактом, найденным в данных, скрывается какая-то интересная журналистская история.

Несколько строк

Использование столбцов

Такую же таблицу можно было бы отобразить в другом виде, более привычном для взгляда, где строками являются регионы, а столбцами – страны. Для этого надо удалить. лишние значения: из «Строк» удалить «Страна», из «Значений» – «Регионы». И добавить в поле «Столбцы» – «Страна».

Столбцы, строки и значения

Сохранение результатов

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

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

Excel 2019 All-in-One Для чайников

Изучите книгу Купить на Amazon

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

1. Выберите все данные (включая заголовки столбцов) в списке данных в виде диапазона ячеек на листе.

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

2. Щелкните инструмент «Быстрый анализ», который появляется прямо под нижним правым углом текущего выбора ячейки.

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

3. Перейдите на вкладку «Таблицы» в верхней части палитры параметров быстрого анализа.

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

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

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

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

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

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

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

Эта статья взята из книги:

• Excel 2019 All-in-One For Dummies,

Об авторе книги:

Грег Харви, доктор философии, опытный преподаватель информатики, работающий со времен DOS и Лотос 1-2-3. Он преподавал курсы по управлению электронными таблицами и базами данных в Университете Золотых Ворот и написал десятки книг, в том числе многие в

Для чайников серия.

Эту статью можно найти в категории:

• Excel ,

Новые возможности сводных таблиц в Excel 2019 и Office 365

>> ЗАГРУЗИТЕ БЕСПЛАТНО ИДЕАЛЬНОЕ РУКОВОДСТВО ПО СВОДНЫМ ТАБЛИЦАМ!

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

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

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

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

  Шаг 1: Перейдите к файлу > Опции

  Шаг 2: Выбрать Данные> Варианты данных> Редактировать макет по умолчанию

  . ! Попробуйте следующее:

  • Промежуточные итоги — Показать все промежуточные итоги внизу группы
  • Макет отчета — Показать в виде таблицы
  • Пустые строки — Отметить «Вставить пустую строку после каждого элемента»

  Дополнительные параметры можно найти внутри Параметры сводной таблицы . Нажмите ОК.

  ШАГ 4: Давайте вставим новую сводную таблицу, чтобы увидеть наш новый макет по умолчанию!

  Откройте нашу таблицу данных. Перейдите к Вставка> Таблицы> Pivottable

  Шаг 5: Нажмите OK

  Шаг 6: Настройка Следующее:

  • .0026
  • Строки — Клиент, Продукты
  • Значения — Продажи

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

   

  Автоматическое определение взаимосвязи

  У нас есть 3 таблицы данных, которые связаны друг с другом: Product , Customer и Sales

  Что соединяет эти таблицы вместе, являются связанными столбцами, вы увидите следующее:

  • Продукт Таблица — Ключ продукта
  • Клиент Таблица — Номер клиента
  • Продажи Таблица — продукт. Ключ, номер клиента

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

  ШАГ 1: Самое интересное, что как только мы добавим это в нашу модель данных, Excel сможет автоматически определять отношения, когда мы работаем со сводной таблицей.

  Выберите любое место в одной из наших таблиц данных. Перейдите к Вставка > Таблицы > Сводная таблица

  Убедитесь, что Добавить эти данные в модель данных отмечен флажком. Нажмите OK

  ШАГ 2: Вы увидите, что все таблицы добавлены в модель данных. Выберите Все , и вы увидите, что там перечислены все 3 таблицы.

  ШАГ 3: Давайте посмотрим, как Excel творит чудеса! Настройка следующее:

  • Столбцы- Название продукта
  • Значения- Сумма продажи

  Click Auto Detect

  Шаг 4: SELECT Управление отношениями

  Нажмите  Редактировать

  ШАГ 5: Это круто, так как связь между Продажи и Продукт таблицы верна! Excel может связать эти таблицы вместе через столбцы Product Key . Нажмите ОК.

  ШАГ 6: Теперь настройте следующее:

  • Строки – Страна

  Нажмите0020 Шаг 7: Выберите Управление отношениями , чтобы увидеть, что было создано автоматически

  Нажмите РЕДАКТИРОВАТЬ

  Шаг 8: Это круто, как взаимосвязь между продаж и Клиент . правильно! Excel может связать эти таблицы вместе через столбцы Customer Number . Нажмите ОК.

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

   

  Автоматическая группировка по времени

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