Как сделать сводную таблицу в Excel
Сводные таблицы в Excel позволяют проанализировать данные, которые находятся в одном большом диапазоне. Причем исходная таблица, которая будет использоваться для анализа, может быть создана как в Excel, так и в другой базе данных, или другом документе.
Все что потребуется, это выбрать необходимые заголовки для строк и столбцов из уже существующих. Также Вы сможете применять фильтр ко всей сводной таблице в целом, или только для значений, которые указаны в названии строк и столбцов.
Теперь о требованиях, которых необходимо придерживаться при ее создании в Эксель. Исходные данные должны быть представлены в виде таблицы, у которой должно быть название для столбцов, то есть шапка. Для этих целей отлично подойдет создание умной таблицы в Excel. Также она не должна содержать пустые строки, столбцы и ячейки. Не должно быть скрытых строк/столбцов и объединенных ячеек.
Создание сводных таблиц в Excel | Exceltip
Сводные таблицы в Excel позволяют экспериментировать с итоговыми данными в динамическом режиме. Кроме того, можно воспользоваться графическим образом сводной таблицы — сводной диаграммой, которая позволяет увидеть результаты анализа данных.
Подготовка многомерных данных
Вообще, наличие предопределенного расположения даты в данном примере – главный недостаток таблицы. Расположив даты по столбцам, мы как бы предопределили измерение этой таблицы, таким образом, лишив себя возможности использовать анализ с помощью сводных таблиц.
Во-первых, надо избавиться от этого недостатка – т.е. убрать предопределенное расположение одного из измерений исходных данных. Пример корректной таблицы – лист Продажи.
Как строить сводные таблицы для бизнеса в Excel | Бизнес-школа LABA (ЛАБА)
Чаще всего анализируемые данные хранятся в базе данных рабочего листа, который называется списком . База данных, хранящаяся в рабочем листе, не может иметь больше 65 535 записей и 256 полей. В первой строке базы данных должны содержаться названия полей. Данные могут состоять из чисел, текста или формул.
Создание сводной таблицы вручную
Чтобы создать сводную таблицу вручную на основе листа с анализируемыми данными, установите курсор ячейки в одной из ячеек списка данных и щелкните на кнопке Сводная таблица, находящейся на вкладке ленты Вставка.
Чтобы завершить создание сводной таблицы, осталось назначить поля, перечисленные в списке, различным частям таблицы. Эта задача осуществляется путем перетаскивания имени поля из списка в соответствующую зону нижней части панели задач Поля сводной таблицы.
- ФИЛЬТРЫ. Здесь содержатся поля, позволяющие фильтровать данные таблицы. Так, например, если перетащить сюда поле года, то можно будет отображать в таблице сводные данные для каждого конкретного года, представленного в списке данных.
- СТОЛБЦЫ. Здесь содержатся поля, определяющие данные, которые отображаются в столбцах сводной таблицы.
- СТРОКИ. Здесь находятся поля, определяющие данные, которые отображаются в строках сводной таблицы.
- ЗНАЧЕНИЯ. Здесь содержатся поля, определяющие, какие данные будут отображаться в ячейках таблицы, т.е. значения, консолидируемые в последнем столбце (по умолчанию суммируемые).
Чтобы лучше понять, как эти зоны связаны со сводной таблицей, посмотрите на готовую таблицу, показанную скриншоте ниже.
В этой сводной таблице в качестве фильтра из списка данных было выбрано поле Дата. Поле Категория я назначил меткам строк. В качестве значений были выбраны поля Расход и Доход.
В результате выполненного выбора в сводной таблице теперь отображается сумма расходов и доходов по категориям с возможностью выбора конкретной даты.
- Список полей. Служит для сокрытия и отображения списка полей на панели задач в правой части области рабочего листа.
- +/- Кнопки. Используется для сокрытия и отображения кнопок сворачивания (-) и разворачивания (+) конкретных строк и столбцов, позволяющих временно удалять и отображать в сводной таблице конкретные значения.
- Заголовки полей. Служит для сокрытия и отображения полей, назначаемых меткам строк и столбцов сводной таблицы.
Как должны выглядеть источники данных
- Макет. Позволяет добавить промежуточные и окончательные итоги, а также изменить общую компоновку сводной таблицы.
- Параметры сводной таблицы. Позволяет изменить стиль, выбранный в коллекции стилей сводных таблиц.
- Стили сводной таблицы. Содержит коллекцию стилей, которые можно применить к активной сводной таблице, щелкнув на соответствующей пиктограмме.
В Excel можно создать макет сводной таблицы непосредственно на рабочем листе, используя для этого инструменты панели Сводные таблицы . Этот способ очень похож на вышеописанный, так как и здесь для создания макета надо просто поместить нужные поля базы данных в нужные области сводной таблицы.
Определение строк сводной таблицы.
На этом этапе важно определить уровни группировки данных (по Вашему желанию или желанию Вашего руководства).
Как видим, здесь в группу «Препарат» попали все виды упаковок данного лекарства.
На этом этапе определяем фильтры, по которым макрос создаст сводную таблицу, но подкорректировать эти фильтры возможно будет и в ручную… Excel для этого предоставляет удобные возможности.
А сейчас формируем положительный фильтр по этим нескольким значениям
Или отрицательный фильтр (результат показан на Рис.4)
Конечно, фильтр, допускающий только одно значение выглядит проще…
А это фильтр, который по умолчанию фильтром не является, так как пропускает любые значения…, но все же есть смысл его добавить к сводной таблице, чтобы иногда… (в некоторых трудно прогнозируемых случаях), можно было его подстроить вручную…
В списке с полями для добавления в отчет, ставим галки напротив основных показателей
Определение полей сводной таблицы с Итоговыми данными (здесь суммами значений).
Для настройки отображения какого-либо поля нужно дважды щелкнуть на нем. Можно указать для конкретного поля функцию подведения итогов — подсчет количества значений или какую-либо другую. Можно также определить, какие элементы поля не показывать или пропустить. Однако эти настройки можно сделать и после создания сводной таблицы.
