что такое сводная таблица в excel и как ее сделать
Как сделать сводную таблицу в Excel – пошаговая инструкция для чайников
В этом руководстве вы узнаете, что такое сводная таблица, и найдете подробную инструкцию, как по шагам создавать и использовать её в Excel.
Если вы работаете с большими наборами данных в Excel, то сводная таблица очень удобна для быстрого создания интерактивного представления из множества записей. Помимо прочего, она может автоматически сортировать и фильтровать информацию, подсчитывать итоги, вычислять среднее значение, а также создавать перекрестные таблицы. Это позволяет взглянуть на ваши цифры совершенно с новой стороны.
Важно также и то, что при этом ваши исходные данные не затрагиваются – что бы вы не делали с вашей сводной таблицей. Вы просто выбираете такой способ отображения, который позволит вам увидеть новые закономерности и связи. Ваши показатели будут разделены на группы, а огромный объем информации будет представлен в понятной и доступной для анализа форме.
Что такое сводная таблица?
Это инструмент для изучения и обобщения больших объемов данных, анализа связанных итогов и представления отчетов. Они помогут вам:
Например, у вас множество записей в электронной таблице с цифрами продаж шоколада:
И каждый день сюда добавляются все новые сведения. Одним из возможных способов суммирования этого длинного списка чисел по одному или нескольким условиям является использование формул, как было продемонстрировано в руководствах по функциям СУММЕСЛИ и СУММЕСЛИМН.
Однако, когда вы хотите сравнить несколько показателей по каждому продавцу либо по отдельным товарам, использование сводных таблиц является гораздо более эффективным способом. Ведь при использовании функций вам придется писать много формул с достаточно сложными условиями. А здесь всего за несколько щелчков мыши вы можете получить гибкую и легко настраиваемую форму, которая суммирует ваши цифры как вам необходимо.
Вот посмотрите сами.
Этот скриншот демонстрирует лишь несколько из множества возможных вариантов анализа продаж. И далее мы рассмотрим примеры построения сводных таблиц в Excel 2016, 2013, 2010 и 2007.
Как создать сводную таблицу.
Многие думают, что создание отчетов при помощи сводных таблиц для «чайников» является сложным и трудоемким процессом. Но это не так! Microsoft много лет совершенствовала эту технологию, и в современных версиях Эксель они очень удобны и невероятно быстры.
Фактически, вы можете сделать это всего за пару минут. Для вас – небольшой самоучитель в виде пошаговой инструкции:
1. Организуйте свои исходные данные
Перед созданием сводного отчета организуйте свои данные в строки и столбцы, а затем преобразуйте диапазон данных в таблицу. Для этого выделите все используемые ячейки, перейдите на вкладку меню «Главная» и нажмите «Форматировать как таблицу».
Полезные советы:
2. Создаем и размещаем макет
Нажатие ОК создает пустой макет без цифр в целевом местоположении, который будет выглядеть примерно так:
Полезные советы:
Область, в которой вы работаете с полями макета, называется списком полей. Он расположен в правой части рабочего листа и разделен на заголовок и основной раздел:
Изменения, которые вы вносите в этих разделах, немедленно применяются в вашей таблице.
3. Как добавить поле
Чтобы иметь возможность добавить поле в нужную область, установите флажок рядом с его именем.
По умолчанию Microsoft Excel добавляет поля в раздел «Макет» следующим образом:
4. Как удалить поле из сводной таблицы?
Чтобы удалить любое поле, вы можете выполнить следующее:
И еще один простой и наглядный способ удаления поля. Перейдите в макет таблицы, зацепите мышкой ненужный вам элемент и перетащите его за пределы макета. Как только вы вытащите его за рамки, рядом со значком появится хатактерный крестик. Отпускайте кнопку мыши и наблюдайте, как внешний вид вашей таблицы сразу же изменится.
5. Как упорядочить поля?
Вы можете изменить расположение показателей тремя способами:
Все внесенные вами изменения применяются немедленно.
Ну а ежели спохватились, что сделали что-то не так, не забывайте, что есть «волшебная» комбинация клавиш CTRL+Z, которая отменяет сделанные вами изменения (если вы не сохранили их, нажав соответствующую клавишу).
6. Выберите функцию для значений (необязательно)
По умолчанию Microsoft Excel использует функцию «Сумма» для числовых показателей, которые вы помещаете в область «Значения». Когда вы помещаете нечисловые (текст, дата или логическое значение) или пустые значения в эту область, к ним применяется функция «Количество».
Думаю, названия операций говорят сами за себя, и дополнительные пояснения здесь не нужны. В крайнем случае, попробуйте различные варианты сами.
Здесь же вы можете изменить имя его на более приятное и понятное для вас. Ведь оно отображается в таблице, и поэтому должно выглядеть соответственно.
7. Используем различные вычисления в полях значения (необязательно)
Подсказка. Функция «Дополнительные вычисления» может оказаться особенно полезной, когда вы добавляете одно и то же поле более одного раза и показываете, как в нашем примере, общий объем продаж и объем продаж в процентах от общего количества одновременно. Согласитесь, обычными формулами делать такую таблицу придется долго. А тут – пара минут работы!
Итак, процесс создания завершен. Теперь пришло время немного поэкспериментировать, чтобы выбрать макет, наиболее подходящий для вашего набора данных.
Работа со списком показателей сводной таблицы
Чтобы изменить способ отображения вашей рабочей области, нажмите кнопку «Инструменты» и выберите предпочитаемый макет.
Вы также можете изменить размер панели по горизонтали, перетаскивая разделитель, который отделяет панель от листа.
Закрытие и открытие панели редактирования.
Закрыть список полей в сводной таблице так же просто, как нажать кнопку «Закрыть» (X) в верхнем правом углу панели. А вот как заставить его появиться снова – уже не так очевидно 🙂
Также можно нажать кнопку «Список полей» на ленте, которая находится на вкладке меню «Анализ».
Воспользуйтесь рекомендациями программы.
Как вы видите на скриншоте выше, Эксель смог предложить несколько базовых макетов для моих исходных данных, которые значительно уступают сводным таблицам, которые мы создали вручную несколько минут назад. Конечно, это только мое мнение 🙂
Давайте улучшим результат.
Теперь, когда вы знакомы с основами, вы можете перейти к вкладкам «Анализ» и «Конструктор» инструментов в Excel 2016 и 2013 ( вкладки « Параметры» и « Конструктор» в 2010 и 2007). Они появляются, как только вы щелкаете в любом месте таблицы.
Вы также можете получить доступ к параметрам и функциям, доступным для определенного элемента, щелкнув его правой кнопкой мыши (об этом мы уже говорили при создании).
После того, как вы построили таблицу на основе исходных данных, вы, возможно, захотите уточнить ее, чтобы провести более серьёзный анализ.
Чтобы улучшить дизайн, перейдите на вкладку «Конструктор», где вы найдете множество предопределенных стилей. Чтобы получить свой собственный стиль, нажмите кнопку «Создать стиль….» внизу галереи «Стили сводной таблицы».
На снимке экрана ниже показан новый дизайн и макет.
Я изменил цветовой макет, а также постарался, чтобы таблица была более компактной. Для этого поменяем параметры представления товара. Какие параметры я использовал – вы видите на скриншоте.
Думаю, стало даже лучше. 😊
Как избавиться от заголовков «Метки строк» и «Метки столбцов».
При создании сводной таблицы, Excel применяет Сжатую форму по умолчанию. Этот макет отображает «Метки строк» и «Метки столбцов» в качестве заголовков. Согласитесь, это не очень информативно, особенно для новичков.
И вот что мы получим в результате.
Показаны реальные имена, как вы видите на рисунке справа, что имеет гораздо больше смысла.
Как обновить сводную таблицу.
Хотя отчет связан с исходными данными, вы можете быть удивлены, узнав, что Excel не обновляет его автоматически. Это можно считать небольшим недостатком. Вы можете обновить его, выполнив операцию обновления вручную или же это произойдет автоматически при открытии файла.
Как обновить вручную.
Кроме того, вы можете по щелчку правой кнопки мыши выбрать пункт Обновить из появившегося контекстного меню.
Примечание. Если внешний вид вашей сводной таблицы сильно изменяется после обновления, проверьте параметры «Автоматически изменять ширину столбцов при обновлении» и « Сохранить форматирование ячейки при обновлении». Чтобы сделать это, откройте «Параметры сводной таблицы», как это показано на рисунке, и вы найдете там эти флажки.
Автоматическое обновление сводной таблицы при открытии файла.
Как переместить на новое место?
Как удалить сводную таблицу?
Если вам больше не нужен определенный сводный отчет, вы можете удалить его несколькими способами.
Примечание. Если у вас есть какая-либо диаграмма, построенная на основе свода, то описанная выше процедура удаления превратит ее в стандартную диаграмму, которую больше нельзя будет изменять или обновлять.
Надеемся, что этот самоучитель станет для вас хорошей отправной точкой. Далее нас ждут еще несколько рекомендаций, как работать со сводными таблицами. И спасибо за чтение!
Excel-plus
Пошаговые инструкции, как использовать функции, формулы и другие инструменты табличного редактора Microsoft Excel
Как построить сводную таблицу в Excel. Настройки сводной таблицы. Примеры и описание.
Как построить сводную таблицу в Excel. Настройки сводной таблицы. Примеры и описание.
Сводная таблица, это инструмент MS Excel, который используется для анализа дынных. С помощью Сводной таблицы можно анализировать огромные массивы данных. При этом пользователь настраивает результаты анализа, так как ему необходимо. Построим Сводную таблицу на примере таблицы, в которой приведены продажи пяти товаров в пяти магазинах, за пол года (январь — июнь).
Исходная таблица.
Обратите внимание, что таблицу на основе которой будет построена Сводная таблица, непосредственно перед построением необходимо отформатировать как Таблицу. Для этого необходимо выделить исходную таблицу и в закладке Вставка нажать на кнопку Таблица.
Как построить сводную таблицу в Excel.
Сначала необходимо выделить исходную таблицу или диапазон данных. После этого в закладке Вставка выбрать Сводная таблица.
Появляется диалоговое окно Создание сводной таблицы.
В поле Таблица или диапазон можно выбрать диапазон данных или таблицу, на основе которых будет построена Сводная таблица. Это необходимо сделать, если это не сделано перед началом построения Сводной таблицы.
Далее выбираем, куда поместить непосредственно Сводную таблицу. На новый лист или На существующий лист. Как правило Сводную таблицу помещают на новый лист. Если ее поместить на существующий лист, то в этом диалоговом окне, в соответствующем поле Диапазон, можно указать место куда разместить Сводную таблицу.
Открылся новый лист в правой части которого появился блок настройки Сводной таблицы — Поля сводной таблицы.
Он содержит в себе следующие элементы:
После того, как выбраны (поставлены галочки) поля для добавления в отчет, они отобразятся в блоках (полях) Фильтры, Столбцы, Строки и Значения. Далее, их необходимо перетащить в соответствующие поля исходя их того, какую Сводную таблицу необходимо получить в результате.
Пункт Отложить обновление макета, позволяет настраивать обновление Сводной таблицы. Если галочка не стоит, настройки сделанные в блоке Поля сводной таблицы отобразятся в Сводной таблице сразу. Если галочка стоит, то только после того, как нажата кнопка Обновить.
Настройки сводной таблицы в Excel.
Итак, рассмотрим настройку Сводной таблицы на нашем примере. В первую очередь выберем поля для отображения в Сводной таблице. В нашем примере галочки нужно поставить напротив всех элементов.
После того, как галочки поставлены, элементы появились в блоках (областях) Фильтры, Столбцы, Строки и Значения.
И уже будет сформирована Сводная таблица.
Продолжим настройку Сводной таблицы. Наша цель, чтобы в столбцах Сводной таблицы отражалась сумма проданных товаров, во всех пяти магазинах, помесячно (январь — июнь). А в строках отражались товары, по видам (Товар №1, Товар №2 и т.д.). На выходе мы получим суммарное количество проданных товаров, каждого вида, указные по месяцам. В качестве фильтра используем магазины. Таким образом, в случае необходимости, мы сможем отразить в Сводной таблице только нужный нам магазин (Магазин №1, Магазин №2 и т.д.).
Как это сделать.
Перетаскиваем элемент Магазин в поле Фильтры.
Наша таблица готова.
В ячейки В1 располагается фильтр. Если нажать на стрелочку фильтра в этой ячейки, появиться возможность выбрать одни или несколько магазинов, после чего в Сводной таблице отобразятся соответствующие этим магазинам продажи. Но предварительно нужно поставить галочку в пункте Выделить несколько элементов.
Самое главное при построение Сводной таблицы, исходя и задачи, правильно выбрать элементы поля для добавления в отчёт. После этого перетащить их в нужные блоки (области).
Если Поля сводной таблице закроются, вернуть их можно вызвав контекстное меню, нажав правой клавишей мыши в поле построенной Сводной таблицы и выбрав пункт: Показать список полей.
Сводная таблица в Excel. Как сделать?
Сводная таблица – это один из наиболее полезных инструментов в Excel. С ее помощью появляются широкие возможности для анализа больших массивов данных и быстрых вычислений.
Видеоурок: Как создать сводную таблицу в Excel
Что такое сводные таблицы в Excel? Пошаговая инструкция
Сводные таблицы это инструмент Excel для суммирования и анализа больших объемов данных.
Представим, что у нас есть таблица с данными продаж по клиентам за год размером в 1000 строчек:
Она содержит данные:
Теперь, представим, что наш руководитель поставил задачу вычислить:
Для поиска ответа на эти вопросы вы можете использовать различные функции и формулы. Но что, если задач по этим данным будет не три, а тридцать? Каждый раз вам придется менять формулы и функции и подстраивать под каждый тип расчета.
Ниже мы разберем, как в решении этих задач нам поможет сводная таблица.
Как сделать сводную таблицу в Excel
Для создания таблицы выполните следующие действия:
После нажатия кнопки “ОК” таблица будет создана.
После формирования таблицы, вы не увидите на листе никаких данных. Все что будет доступно, это ее имя и меню для выбора данных к отображению.
Теперь, прежде чем мы приступи к анализу данных, предлагаю разобраться что значит каждое поле и область сводной таблицы.
Области сводной таблицы в Excel
Для эффективной работы со сводными таблицами, важно знать принцип их работы.
Ниже вы узнаете подробней об областях:
Что такое кэш сводной таблицы
При создании сводной таблицы, Excel создает кэш данных, на основе которых будет построена таблица.
Когда вы осуществляете вычисления, Excel не обращается каждый раз к исходным данным, а использует информацию из кэша. Эта особенность значительно сокращает количество ресурсов системы, затрачиваемых на обработку и вычисления данных.
Кэш данных увеличивает размер Excel-файла.
Область “Значения”
Область “Значения” включает в себя числовые элементы таблицы. Представим, что мы хотим отразить объем продаж регионов по месяцам (из примера в начале статьи). Область закрашенная желтым цветом, на изображении ниже, отражает значения размещенные в области “Значения”.
На примере выше создана таблица, в которой отражены данные продаж по регионам с разбивкой по месяцам.
Область “Строки”
Заголовки таблицы, размещенные слева от значений, называются строками. В нашем примере это названия регионов. На скриншоте ниже, строки выделены красным цветом:
Область”Столбцы”
Заголовки вверху значений таблицы называются “Столбцы”.
На примере ниже красным выделены поля “Столбцы”, в нашем случае это значения месяцев.
Область “Фильтры”
Область “Фильтры” используется опционально и позволяет задать уровень детализации данных. Например, мы можем в качестве фильтра указать данные “Тип клиента” – “Продуктовый магазин” и Excel отобразит данные в таблице касающиеся только продуктовых магазинов.
Сводные таблицы в Excel. Примеры
На примерах ниже мы рассмотрим, как с помощью сводных таблиц ответить на три вопроса:
Прежде чем анализировать данные, важно решить каким образом должны выглядеть данные таблицы (какие данные разметить в колонки, строки, значения, фильтры). Например, если нам нужно отобразить данные продаж клиентов по регионам, то следует поместить названия регионов в строки, месяцы в колонки, значения продаж в поле “Значения”. Как только вы представили каким образом вы видите итоговую таблицу – начинайте её создание.
В окне “Поля сводной таблицы” размещены области и поля со значениями для размещения:
Поля создаются на основе значений исходного диапазона данных. Раздел «Области» – это место, где вы размещаете элементы таблицы.
Перенос полей из области в область представляет собой удобный интерфейс, в котором, при перемещении, данные автоматически обновляются.
Теперь, попробуем ответить на вопросы руководителя из начала этой статьи на примерах ниже.
Пример 1. Какой объем выручки у региона Север?
Для вычисления объема продаж региона Север, рекомендую разместить в таблице данные продаж по всем регионам. Для этого нам потребуется:
Получим ответ: продажи региона Север составляют 1 233 006 966 ₽:
Пример 2. ТОП пять клиентов по продажам
Для того чтобы вычислить рейтинг ТОП пяти клиентов, нам нужно:
У нас получится следующая таблица:
По-умолчанию, система Excel сортирует данные в таблице в алфавитном порядке. Для сортировки данных по объему продаж выполните следующие действия:
Как результат мы получим отсортированный список клиентов по объему выручки.
Пример 3. Какое место по выручке занимает клиент Лудников ИП в регионе Восток?
Для расчета места по объему выручки клиента Лудников ИП в регионе Восток рекомендую сформировать сводную таблицу, в которой будут отображены данные выручки по регионам и клиентам внутри этого региона.
В итоге мы получили таблицу, в которой отражены данные выручки клиентов в рамках каждого региона.
Для сортировки данных выполните следующие шаги:
В полученной таблице мы можем определить какое место занимает клиент Лудников ИП среди всех клиентов региона Восток.
Существует несколько вариантов для решения этой задачи. Вы можете перенести поле “Регион” в область “Фильтры” и в строчках разместить данные продаж клиентов, таким образом отразив данные по выручке только клиентов региона Восток.
Еще больше полезных приемов в работе со сводными таблицами Excel вы узнаете в практическом курсе “ Сводные таблицы в Excel“. Успей зарегистрироваться по ссылке!
Как сделать сводную таблицу в Excel?
Узнаем как сделать сводную таблицу в Excel, которая позволяет быстро и удобно анализировать большие объемы данных, а также научимся ее корректно обновлять.
Приветствую всех, дорогие читатели блога TutorExcel.Ru!
Предположу, что нет ни одного человека знакомого с Excel, который бы не слышал про сводные таблицы. Данный инструмент является одним из самых главных и полезных в плане умения обработать и структурировать большие объемы данных.
Давайте рассмотрим пример.
Представим, что у нас есть большая таблица с продажами небольшой фирмы, где представлены подневные данные, что и когда продали, за сколько, где именно и так далее (в принципе спектр разрезов данных может быть любым и сколь угодно большим):
При анализе данных перед нами может встать ряд вопросов, к примеру:
Но смотря на обычную таблицу мы ответов найти скорее всего не сможем, а вот сводная таблица нам в этом прекрасно поможет.
Давайте пошагово разберем как создать сводную таблицу.
Создание сводной таблицы в Excel
В данном меню мы можем настроить 2 основных момента — на основе каких данных построить таблицу и где ее разместить.
Как мы видим, Excel автоматически определил диапазон исходной таблицы с данными (для этого мы как раз и перешли в произвольную ячейку внутри таблицы). Но в целом мы также можем и самостоятельно задать диапазон.
Далее определим куда мы поместим сводную таблицу — либо она создается на новом листе, либо добавляется на каком-то из существующих. В зависимости от предпочтений выбираем подходящий вариант.
Нажимаем OK и перед нами появляется следующий конструктор:
Слева в окне Excel находится сам отчет сводной таблицы, в правой же части окна — макет для ее формирования. Т.е. работать мы будем с правой частью с полями и областями, а в левой части мы будем видеть результат наших действий.
В правой части мы видим следующие элементы (список полей и области):
В итоге мы имеем список полей и 4 области (фильтры, строки, столбцы, значения) из которых и составляется сводная таблица.
Теперь обо всем по порядку.
Список полей и области сводной таблицы
Обратите внимание, что в списке полей отобразились все столбцы исходной таблицы с данными.
Поэтому проследите, что все столбцы таблицы имеют заголовки, в противном случае Excel выдаст ошибку о недопустимости имени при попытке создать таблицу.
Также необходимо, чтобы в таблице отсутствовали пустые строки со столбцами и объединенные ячейки, в этом случае Excel не понимает структуру исходных данных и может свести данные некорректно.
Обычно в строки и столбцы помещают текстовые поля, а в значения числовые, так как именно область значений является вычисляемой и там мы можем увидеть сводный результат вычислений.
Область фильтра позволяет нам придать нашей таблице еще одну размерность, условно говоря сделать из двумерной таблицы трехмерную, так как фильтр позволяет увидеть одну и ту же таблицу в зависимости от параметра (изменяемого в фильтре).
С элементами разобрались, теперь перейдем непосредственно к построению.
Построение отчета сводной таблицы
Наша задача состоит в том, чтобы перетаскивать поля сводной таблицы в нужную нам область построения (в зависимости от поставленных задач).
Другими словами мы вначале определяем какой именно вид должна принять таблица, а потом исходя из этого распределить поля по областям. Условно говоря сначала понять что мы хотим увидеть по горизонтали и вертикали в таблице, а потом уже ее строить.
К примеру, предположим, что мы хотим проанализировать динамику продаж в штуках по наименованиям товаров в зависимости от магазина. В этом случае, чтобы создать сводную таблицу, нам нужно будет показать продаваемые фирмы и модели по вертикали, а даты по горизонтали, то соответствующие поля в первом случае (фирмы и модели) добавляем в область строк, а во втором (даты) уже в область столбцов. Так как нам нужны продажи, то соответствующее поле добавляем в область значений, а магазин в фильтры.
В итоге мы получим следующий пример сводной таблицы:
Рекомендую вам поэкспериментировать с наполнением областей, после нескольких попыток уверен, что без проблем поймете принцип формирования таблицы.
В динамике добавление полей выглядит так:
Теперь несколько видоизменим постановку задачи и сделаем анализ уже не в разрезе товаров, а в разрезе доходов магазинов по фирмам производителей.
В этом случае магазины и модели отправляются в область строк, дата в область столбцов, а продажи в деньгах — в значения. В результате получаем еще один пример сводной таблицы:
И еще один пример. Проанализируем помесячные продажи в разрезе моделей (даты отправляются в строки, а продажи в штуках и деньгах — в значения):
Как мы видим, в зависимости от поставленной задачи при анализе мы можем получать совершенно разные таблицы (как в 3 примерах выше), хотя они построены на абсолютно идентичных друг другу данных.
Сводную таблицу мы построили, но возникает вопрос что с ней делать дальше когда обновятся данные в исходной таблице.
Как обновить сводную таблицу в Excel?
Представим, что в исходные данные с продажами внесли изменения и нам нужно обновить сводную таблицу.
Однако же, если в начальные данные добавили новые строки (к примеру, появились данные за новые периоды), то такой способ уже не сработает.
Почему?
Поэтому будьте крайне внимательны при работе с таблицей, так как достаточно легко забыть обновить источник данных и не отследить количество задействованных строк и столбцов.
Эту проблему можно решить задав в качестве источника данных не фиксированный диапазон (как в примере выше мы использовали ссылку $A$1:$G$820), а целиком выделить все столбцы, не ограничивая таблицу по высоте (т.е. использовать ссылку $A:$G).
Однако такой вариант уже не подойдет, если мы захотим добавить не строку, а именно столбец в исходные данные (к примеру, у нас добавится еще 1 метрика, которую мы захотим смотреть и анализировать).
Но и для этой проблемы есть решение, давайте воспользуемся умной таблицей и несколькими полезными свойствами, которая она дает.
Применение умных таблиц
Среди стилей оформления выбираем любой понравившийся (внешний вид на функциональности не влияет), далее выбираем диапазон для таблицы (по умолчанию Excel автоматически его определяет) и нажимаем OK:
Полученная умная таблица обладает большим спектром удобных свойств, но в данном случае нам интересны 2:
Таким образом мы можем использовать имя таблицы в качестве источника данных. Вернемся к нашим сводным таблицам и в Источнике данных поменяем диапазон на умную таблицу:
Теперь при добавлении данных в исходную таблицу не нужно будет каждый раз проверять диапазон на целостность данных и смотреть сколько строк или столбцов было в таблице изначально.
Давайте подведем небольшое резюме, что в итоге нужно делать, чтобы построить сводную таблицу.
Пошаговая инструкция построения сводной таблицы в Excel
Спасибо за внимание!
Если у вас есть вопросы или мысли по теме статьи — обязательно спрашивайте и пишите в комментариях, не стесняйтесь.