Узнаем как сделать сводную таблицу в Excel, которая позволяет быстро и удобно анализировать большие объемы данных, а также научимся ее корректно обновлять.
Приветствую всех, дорогие читатели блога TutorExcel.Ru!
Предположу, что нет ни одного человека знакомого с Excel, который бы не слышал про сводные таблицы. Данный инструмент является одним из самых главных и полезных в плане умения обработать и структурировать большие объемы данных.
Представим, что у нас есть большая таблица с продажами небольшой фирмы, где представлены подневные данные, что и когда продали, за сколько, где именно и так далее (в принципе спектр разрезов данных может быть любым и сколь угодно большим):
При анализе данных перед нами может встать ряд вопросов, к примеру:
Но смотря на обычную таблицу мы ответов найти скорее всего не сможем, а вот сводная таблица нам в этом прекрасно поможет.
Давайте пошагово разберем как создать сводную таблицу.
[expert_bq id=»1570″]Для того, чтобы получить обновление сразу всей конструкции, требуется зайти во вкладку Анализ и выбрать строку Обновить все. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Затем указывается место для размещения. Если выбрать новый лист, то приложение создаст его и поместит таблицу, начиная с первой ячейки. Если требуемый лист уже существует, то можно самостоятельно выбрать адрес ее начала.Сводные таблицы в Excel для чайников: как сделать и для чего использовать
- Список полей;
Список всех заголовков столбцов исходной таблицы с данными. - Фильтры;
Добавление дополнительного среза для детализации данных. - Строки;
Поля таблицы вынесенные в строки. - Столбцы;
Поля таблицы вынесенные в столбцы; - Значения.
Вычисляемые числовые данные по соответствующим полям из строк и столбцов (единственный вычисляемый элемент в таблице).
Обычно в строки и столбцы помещают текстовые поля, а в значения числовые, так как именно область значений является вычисляемой и там мы можем увидеть сводный результат вычислений.
Создание
Создать сводную таблицу можно в программах различных версий (2010-2016). Рассматриваемая инструкция создана на основе Майкрософт Эксель 2013.
В запущенной программе необходимо перейти во вкладку «Вставка» и выбрать пункт «Сводная таблица».
После выполнения описанных действий на рабочем поле появится окно, которое необходимо заполнить.
В первой пустой строке необходимо указать координаты таблицы с данными, их можно прописать вручную, но проще просто выделить желаемый объект.
Обратите внимание! Данным способом невозможно консолидировать (объединить) несколько таблиц.
Если база данных хранится в другой книге, ее можно указать, выбрав раздел «Использовать внешний источник».
Последним этапом будет выбор расположения новой конструкции. Рекомендуется по возможности размещать ее на новой странице, но разрешается выбрать координаты на имеющемся листе.
После нажатия кнопки «Ок» появится новая страница, на которой располагается основа желаемого объекта и набор интсрументов для того, чтобы создавать структурированные объекты.
[expert_bq id=»1570″]В сводных таблицах операции с полями означают действия со всеми строками данного столбца, из чего, собственно, и проистекает вся мощь этого инструмента. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Шаг 2. Вставка диаграммы.Обратите внимание! Если перед этим этапом произвести выделение таблицы, то все поля заполнятся автоматически.
Так же как и в Word, вставка диаграммы в Excel осуществляется через меню Вставка (пункт Диаграмма). В открывшемся диалоговом окне вам предложат выбрать тип диаграммы. Для разных задач используются разные диаграммы. В нашем случае лучше всего подойдет круговая: она отображает долю разных значений в общей сумме.
Сводные таблицы Excel — Реальные примеры создания
Нажмите в возникшем диалоговом окне OK . В этом диалоге можно изменить диапазон нашей таблицы, который Excel отлично определяет самостоятельно, а также можно указать, куда вставлять сводную таблицу — рекомендую выбирать всегда отдельный новый лист, что и соответствует настройке по умолчанию.
Как использовать
Если какое-то из этих условий не соблюдено, данные необходимо отформатировать, иначе создать базу не получится. Чтобы наглядно показать, как в Экселе сделать сводную таблицу, используем для примера документ следующего вида:
Создаем базу Excel с помощью функции «Вставка» — «Таблица» — «Сводная таблица».
Осталось показать, как работать со сводными таблицами в Excel. Снимая и устанавливая галочки в списке полей, меняем вид отчета. Выведем сумму сделок каждого менеджера.
Как сделать вычисления
В отчет можно добавить вычисляемые поля. Для этого необходимо поставить курсор в любую ячейку Еxcel, выбрать вкладку «Анализ» — «Вычисления» — «Поля, элементы и наборы» — «Вычисляемое поле». В появившемся окне зададим имя поля и формулу для вычислений. В нашем случае зарплата составляет 5% от выручки, и формула выглядит следующим образом:
Добавлять и убирать столбцы можно, перетаскивая поля в соответствующую область.
Если данные в исходном массиве изменились, базу необходимо обновить. Добавим менеджера Самуйлову в исходные данные, поставим курсор в любую ячейку базы и обновим результат сведений с помощью вкладки «Анализ» — «Обновить данные».
Чтобы настроить автоматическое обновление данных при открытии файла, необходимо установить галочку в соответствующем месте (вкладка «Анализ» — «Параметры» — «Данные»).
Ниже списка представлены 4 области. Они отвечают за действия над данными:
Информатика: Задания / Excel данные
При составлении отчетов в созданной сводной таблице, можно использовать большое количество статистических функций и формул для того, чтобы проанализировать полученные значения и базовые входящие данные.
Момент истины
А теперь я при помощи сводной таблицы за 10 секунд повторю наши 5 шагов, на которые мы потратили минут 10.
Итак, что мы видим на главном экране управления сводной таблицой:
Секция Поля сводной таблицы — содержит не что иное, как заголовки столбцов нашей исходной таблицы. Поле или измерение — суть синонимы. Термин «поле» пришёл к нам из баз данных. В сводных таблицах операции с полями означают действия со всеми строками данного столбца, из чего, собственно, и проистекает вся мощь этого инструмента.
Раздел СТРОКИ — сюда перетаскивают поля, сгруппированные значения которых должны располагаться по вертикали. Пример:
Раздел КОЛОННЫ — сюда перетаскивают поля, значения которых должны располагаться горизонтально. Пример:
Раздел ЗНАЧЕНИЯ — сюда перетаскивают поля, которые можно суммировать. Суммировать можно только числовые поля, а вот подсчитать количество строк можно по любому полю, поэтому, если вы в данный раздел перетащите какое-либо поле измерения (например, Продавец ), то Excel автоматически поймёт, что его надо подсчитывать (count), а не суммировать (sum).
Раздел ФИЛЬТРЫ — сюда перетаскиваем поля, по которым не надо группировать значения столбцов и суммировать, но надо отфильтровать строки, чтобы в дальнейшей группировке и суммировании участвовали только строки, удовлетворяющие условию на значение фильтра. Посмотрите на примере:
[expert_bq id=»1570″]То есть редактор документа может объединять в отдельные группы информацию, которая соответствует определенному временному промежутку данные за месяц, год и прочее ;. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Задание 4б *: Нарисуйте диаграмму типа гистограмма, которой видно, какова СРЕДНЯЯ стоимость щенка каждой породы. Для этого вам понадобится сначала с помощью Итогов подсчитать среднюю стоимость по породе, а затем уже вставить гистограмму. В гистограмме добавьте подписи данных (среднее стоимость для каждого столбца).Сводная таблица в Excel — как сделать
Обычно в строки и столбцы помещают текстовые поля, а в значения числовые, так как именно область значений является вычисляемой и там мы можем увидеть сводный результат вычислений.
