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

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

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

Возможности сводных таблиц

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

• Структурирование данных.
• Группировка и категоризация.
• Фильтрация и сортировка.
• Изменение структуры.
• Расчет итогов.
• Разворачивание данных.
• Публикация и представление.

Требования к сводным таблицам

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

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

Узнай, какие ИТ - профессии
входят в ТОП-30 с доходом
от 210 000 ₽/мес

Павел Симонов

Исполнительный директор Geekbrains

Команда GeekBrains совместно с международными специалистами по развитию карьеры подготовили материалы, которые помогут вам начать путь к профессии мечты.

Подборка содержит только самые востребованные и высокооплачиваемые специальности и направления в IT-сфере. 86% наших учеников с помощью данных материалов определились с карьерной целью на ближайшее будущее!

Скачивайте и используйте уже сегодня:

Павел Симонов

Исполнительный директор Geekbrains

Топ-30 самых востребованных и высокооплачиваемых профессий 2023

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

Подборка 50+ бесплатных нейросетей для упрощения работы и увеличения заработка

Только проверенные нейросети с доступом из России и свободным использованием

ТОП-100 площадок для поиска работы от GeekBrains

Список проверенных ресурсов реальных вакансий с доходом от 210 000 ₽

Получить подборку бесплатно

pdf 3,7mb

doc 1,7mb

Уже скачали 31988

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

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

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

• Убедитесь, что информацию можно представить в виде таблицы, где строки представляют собой записи или наблюдения, а столбцы — разные их атрибуты/характеристики.
• Выделите все используемые ячейки данных и перейдите на вкладку «Главная» в меню программы, затем выберите «Форматировать как таблицу»
• Использование «умной таблицы» в качестве исходного материала предоставляет вам значительное преимущество. Этот тип таблицы является динамическим, он автоматически расширяется или уменьшается при добавлении или удалении записей. Это гарантирует, что сводный отчет всегда будет содержать самые актуальные данные.
• Добавьте уникальные и информативные заголовки в каждый столбец. Они будут использоваться в сводном отчете в качестве имен полей.
• Предварительно убедитесь, что исходная таблица не содержит пустых строк или столбцов, а также избавьтесь от промежуточных итогов, которые могут исказить результаты.
• Для упрощения работы и обращения к первичным данным дайте таблице уникальное имя, которое можно ввести в поле «Имя» в верхнем правом углу.

Создание макета

Для создания макета сводной таблицы в Excel следуйте этим шагам:

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

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

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

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

Читайте также!

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

Подробнее

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

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

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

Для вставки необходимого поля в сводную таблицу нужно выполнить следующий шаг:

• Отметьте флажок рядом с именем поля, которое вы хотите добавить.

Microsoft Excel по умолчанию размещает их следующим образом:

• Нечисловые данные ставятся в область «Строки».
• Числовые – в область «Значения».
• Даты и время – в область «Столбцы».

Дарим скидку от 60%
на курсы от GeekBrains до 24 ноября

Уже через 9 месяцев сможете устроиться на работу с доходом от 150 000 рублей

Забронировать скидку

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

Есть несколько способов это сделать:

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

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

Можно изменить порядок отображения полей в сводной таблице тремя различными способами:

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

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

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

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

• Выделите любую ячейку в сводной таблице.
• Перейдите в меню «Работа со сводными таблицами».
• В выпадающем меню выберите «Параметры».
• Перейдите в раздел «Данные».
• Нажмите на пункт «Обновить».

Только до 28.11

Скачай подборку материалов, чтобы гарантированно найти работу в IT за 14 дней

Список документов:

ТОП-100 площадок для поиска работы от GeekBrains

20 профессий 2023 года, с доходом от 150 000 рублей

Чек-лист «Как успешно пройти собеседование»

Чтобы получить файл, укажите e-mail:

Введите e-mail, чтобы получить доступ к документам

Подтвердите, что вы не робот,
указав номер телефона:

Введите телефон, чтобы получить доступ к документам

Скачать подборку бесплатно pdf 2,5mb

Уже скачали 52300

Я подтверждаю согласие на обработку персональных данных.

Вы также можете воспользоваться контекстным меню для обновления:

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

Полезные функции сводной таблицы

Функции для значений

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

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

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

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

Различные вычисления в полях значения

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

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

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

• Представьте, что сводные таблицы в Excel применяются для анализа продаж товаров в разные регионы. Вы можете узнать, какие товары приносят максимальную выручку, какие категории завоевали наибольшую популярность, и что именно привлекает внимание клиентов.
• А еще сводные таблицы в Microsoft Excel помогут в анализе данных о клиентах. Группируя их по разным параметрам, таким как возраст, пол или местоположение, можно получить четкое представление о целевой аудитории. Это позволит лучше понимать потребности клиентов и разрабатывать более точные маркетинговые стратегии.
• Но это еще не всё. Сводные таблицы в Excel также могут быть применены для анализа данных о производстве. С их помощью легко подытожить информацию о производственных процессах, группировать ее по периодам или этапам, и таким образом определить наиболее эффективные или, наоборот, требующие улучшений процессы.

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

Часто задаваемые вопросы о сводных таблицах в Excel

Какие задачи решает сводная таблица?

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

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

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

Можно ли добавлять дополнительные вычисления в сводную таблицу?

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

Что значит недопустимое имя поля сводной таблицы?

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

Читайте также!

Сортировка в Еxcel: все возможные способы

Подробнее

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

Если вам больше не нужен определенный сводный отчет, убрать его легко несколькими способами. Вы можете удалить лист, на котором находится таблица, если она установлена отдельно. Когда сводный реестр представлен среди других данных на листе, выделите его и нажмите «Delete». Также можно щелкнуть по таблице, перейти на вкладку «Анализ», выбрать «Действия» и «Вся сводная таблица», затем нажать «Удалить».

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

Автор статьи:

Редакция сайта GeekBrains Шеф-редактор раздела Программирование