Сводная таблица с текстовыми полями в области значений
Данная статья содержит информацию о том, как с помощью надстройки Power Pivot в Excel добавлять текстовые поля в область значений сводной таблицы. Это может быть полезно, когда необходимо перечислить сущности, не добавляя их в группировку сводной таблицы. Такими сущностями могут быть номера договоров, перечень ассортимента, различные комментарии и другие характеристики.
Чтобы с помощью надстройки Power Pivot добавить текст в сводную таблицу, нужно выполнить последовательно следующие действия:
Шаг 1. Включаем надстройку Power Pivot.
Надстройка Power Pivot входит в стандартный комплект Excel 2013, 2016 и Excel 365 для Windows. Она подключается одной галочкой в окне надстроек:
Файл → Параметры → Надстройки → Надстройки COM → Microsoft Power Pivot.
Шаг 2. Загружаем исходную таблицу Exсel в модель данных Power Pivot и создаем сводную таблицу с подключением к модели. В качестве примера возьмем таблицу (рис. 1), в которой ведутся остатки ассортимента в магазине обуви.
Исходную таблицу с информацией об остатках обуви загружаем в модель данных Power Pivot, как показано на рис. 2.
Выполнив загрузку, выходим из окна Power Pivot, создаем новый лист, через команду «Вставка» строим сводную таблицу.
Важный момент: в случае с Power Pivot для создания сводной таблицы выделять исходную таблицу не нужно.
Шаг 3. Добавим в область строк сводной таблицы следующие поля: Тип обуви, Полное наименование, Размеры, Цвет (рис. 3).
Для начала подсчитаем количество уникальных цветов для выбранной группировки.
По аналогии с вычисляемыми полями сводной таблицы в Power Pivot есть меры, которые создают такие же вычисляемые поля. В отличие от классических вычисляемых полей меры более функциональны и интуитивно понятны.
Чтобы создать меру, нужно на вкладке Power Pivot выбрать «Меры» и «Создать меру» (см. рис. 3).
В окне создания меры необходимо указать таблицу, в которой данная мера располагается, имя меры и формулу. По желанию можно указать формат и описание (рис. 4). Одним из вариантов определения количества уникальных цветов будет мера, записанная следующим образом:
Функция VALUES() создает таблицу из одного столбца с уникальными значениями (в нашем случае это «коричневый», «синий», «черный»).
Функция COUNTROWS() подсчитывает в таблице количество строк.
К мере применяются фильтры сводной таблицы (учитываются ее разрезы).
В группировке Ботильонов и следующего наименования обуви указано 3 — максимальное количество уникальных цветов. Все три цвета повторяются в первом по порядку размере 38, поэтому у данного размера тоже 3. В размере 39 только один цвет, поэтому стоит 1 (рис. 5).
Если функция VALUES() имеет единственное значение, то в значения сводной таблицы его можно добавить текстом, а не только рассчитать количество таких значений, чем ограничивается стандартный Excel.
Предположим, нами создана мера, которая выглядит следующим образом:
Мы получим сообщение об ошибке, так как в строках, где уникальных значений больше одного, результатом функции VALUES() также является более одного значения (рис 6).
Чтобы обойти данную ошибку, нужно определить, когда VALUES() возвращает одно или несколько значений. Для этого воспользуемся функцией HASONEVALUE(). Она возвращает 1 (ИСТИНА), если в ней столбец с одним значением, и 0 (ЛОЖЬ), если столбец с несколькими значениями.
Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 10, 2018.
Как сделать сводную таблицу в Excel — способы создания
В сводных таблицах можно рассчитывать данные разными способами. Вы узнаете о доступных методах вычислений, о влиянии типа исходных данных на вычисления и о том, как использовать формулы в сводных таблицах и на сводных диаграммах.
Создание сводной таблицы в Excel
Для начала встанем в любую ячейку нашей таблицы с данными. Далее в панели вкладок перейдем Вставка -> Сводная таблица:
В данном меню мы можем настроить 2 основных момента — на основе каких данных построить таблицу и где ее разместить.
Как мы видим, Excel автоматически определил диапазон исходной таблицы с данными (для этого мы как раз и перешли в произвольную ячейку внутри таблицы). Но в целом мы также можем и самостоятельно задать диапазон.
Далее определим куда мы поместим сводную таблицу — либо она создается на новом листе, либо добавляется на каком-то из существующих. В зависимости от предпочтений выбираем подходящий вариант.
Нажимаем OK и перед нами появляется следующий конструктор:
Слева в окне Excel находится сам отчет сводной таблицы, в правой же части окна — макет для ее формирования. Т.е. работать мы будем с правой частью с полями и областями, а в левой части мы будем видеть результат наших действий.
В правой части мы видим следующие элементы (список полей и области):
- Список полей;
Список всех заголовков столбцов исходной таблицы с данными. - Фильтры;
Добавление дополнительного среза для детализации данных. - Строки;
Поля таблицы вынесенные в строки. - Столбцы;
Поля таблицы вынесенные в столбцы; - Значения.
Вычисляемые числовые данные по соответствующим полям из строк и столбцов (единственный вычисляемый элемент в таблице).
В итоге мы имеем список полей и 4 области (фильтры, строки, столбцы, значения) из которых и составляется сводная таблица.
Как вставить столбец в сводную таблицу excel
- Умная таблица получает имя (в примере выше Таблица1, его можно легко поменять), которое можно использовать для определения диапазона в качестве источника для сводной таблицы;
- Умная таблица автоматически изменяет размер при добавлении или удалении новых строк или столбцов.
Другими словами мы вначале определяем какой именно вид должна принять таблица, а потом исходя из этого распределить поля по областям. Условно говоря сначала понять что мы хотим увидеть по горизонтали и вертикали в таблице, а потом уже ее строить.
Группировка по числам в сводной таблице
Немного видоизменим нашу сводную таблицу и в строки вместо дат добавим числовой код товара:
Алгоритм действий точно такой же как и в варианте с датами, щелкаем по любой ячейке с числами правой кнопкой мыши и выбираем команду Группировать:
Мы также можем настроить начальную и конечную точку для группировки чисел, а также задать шаг по которому числа будут делиться на группы:
С числами тоже все оказалось не так сложно, перейдем к последнему варианту.
Как рассчитать процентное изменение с помощью сводных таблиц в Excel.
В появляющемся на четвертом шаге диалоговом окне (рис. 7.3 рис. 7.3 ) можно выбрать место расположения сводной таблицы, установив переключатель новый лист или существующий лист, для которого необходимо задать диапазон размещения. После нажатия кнопки Готово будет сформирована сводная таблица со стандартным именем.
Формулы в сводных таблицах Excel
Сначала составим сводный отчет, где итоги будут представлены не только суммой. Начнем работу с нуля, с пустой таблицы. За одно узнаем как в сводной таблице добавить столбец.
Научимся прописывать формулы в сводной таблице. Щелкаем по любой ячейке отчета, чтобы активизировать инструмент «Работа со сводными таблицами». На вкладке «Параметры» выбираем «Формулы» — «Вычисляемое поле».
Жмем – открывается диалоговое окно. Вводим имя вычисляемого поля и формулу для нахождения значений.
Получаем добавленный дополнительный столбец с результатом вычислений по формуле.
Экспериментируйте: инструменты сводной таблицы – благодатная почва. Если что-то не получится, всегда можно удалить неудачный вариант и переделать.
Как сделать сводную таблицу в Excel?.
Допустим, вы менеджер компании и отвечаете за ведение документации по продажам. Все сделки заносятся в таблицу с указанием всех деталей (продавец, покупатель, количество, сумма, дата продажи, адрес покупателя и т. д.). Руководитель просит отчёт о продажах по регионам.
Форматирование диапазона в виде таблицы
Если ваш диапазон данных еще не отформатирован в виде таблицы, мы рекомендуем вам сделать это. Данные, хранящиеся в таблицах, имеют множество преимуществ по сравнению с данными в диапазонах ячеек на листе, особенно при использовании сводных таблиц (узнать больше о преимуществах использования таблиц ).
Чтобы отформатировать диапазон в виде таблицы, выберите диапазон ячеек и нажмите «Вставить»> «Таблица».
Убедитесь, что диапазон правильный, что у вас есть заголовки в первой строке этого диапазона, а затем нажмите «ОК».
Диапазон теперь отформатирован как таблица. Присвоение имени таблице упростит обращение к ней в будущем при создании сводных таблиц, диаграмм и формул.
Щелкните вкладку «Дизайн» в разделе «Работа с таблицами» и введите имя в поле в начале ленты. Эта таблица получила название «Продажи».
Вы также можете изменить здесь стиль таблицы, если хотите.
Группировка данных в сводной таблице в Excel.
Сводная таблица и сводная диаграмма выглядят, как показано на рисунке ниже. Если создать сводную диаграмму на основе данных из сводной таблицы, то значения на диаграмме будут соответствовать вычислениям в связанной сводной таблице.
Как удалить СТ
Самый простой случай – когда вы создали сводную таблицу, отослали результаты шефу, и она вам больше не нужна. Если вы в этом уверены, просто выбираем таблицу и жмём клавишу Delete. Просто и эффективно.
Но вдруг структура таблицы может вам понадобиться в будущем? В Excel имеется возможность удалить только результаты, или данные ячеек. Рассмотрим, как это делается.
Для удаления результатов вычислений выполняем следующие шаги:
Но как поступить, если вы хотите сохранить результаты, но сами данные вам не нужны, то есть вы хотите освободить стол? Такая ситуация часто возникает, если руководству нужны только итоги. Алгоритм действий:
- снова выбираем любую ячейку, кликаем на вкладке «Анализ»;
- выбираем пункт меню «Действия», кликаем на «Выбрать», отмечаем мышкой всю сводную таблицу;
- щёлкаем ПКМ внутри выделенной области;
- из контекстного меню выбираем пункт «Скопировать»;
- переходим к вкладке «Главная», снова щёлкаем ПКМ и выбираем «Вставить»;
- выбираем вкладку «Вставить значение», в ней отмечаем параметр «Вставить как значение».
В итоге сводная таблица будет стёрта с сохранением результатов.
СОВЕТ. Ускорить процедуру можно посредством использования комбинации клавиш. Для выделения таблицы применяйте Ctrl + A, для копирования – Ctrl + C. Затем жмём ALT + E, ALT + S, ALT + V и завершаем процедуру нажатием Enter.
Для удаления сводных таблиц в Excel 2007/2010 нужно использовать другой алгоритм:
Если ваш начальник любит визуализацию данных, очевидно, что вам придётся использовать сводные диаграммы. Поскольку они занимают много места в таблице, после использования их обычно удаляют.
В старых версиях программы для этого нужно выделить диаграмму, щёлкнуть на вкладке «Анализ», выбрать группу данных и нажать последовательно «Очистить» и «Очистить всё».
При этом, если диаграмма связана с самой сводной таблицей, после её удаления вы потеряете все настройки таблицы, её поля и форматирование.
Для версий старше Excel 2010 нужно выбрать диаграмму, на вкладке «Анализ» выбрать пункт «Действия» и нажать «Очистить» и «Очистить всё». Результат будет аналогичным.
Надеемся, что наши уроки по сводным таблицам позволят вам открыть для себя этот достаточно мощный функционал. Если у вас остались вопросы, задавайте их в комментариях, мы постараемся на них ответить.
НОУ ИНТУИТ | Лекция | Сводные таблицы
- главное ограничение связано с обязательным наличием названий над столбцами, участвующими в вычислениях. Такие идентификаторы необходимы для формирования результирующих отчётов – при добавлении в исходную таблицу новых записей (строк) формат СТ менять не нужно, а результаты будут пересчитаны автоматически;
- убедитесь, что в ячейках строк и столбцов, участвующих в выборке, введены числовые параметры. Если они будут пустыми или содержать текстовые значения, эти строки выпадут из расчётов, что исказит результаты вычислений;
- следите за соответствием форматов строк и содержимым ячеек. Если она определена как дата, то все значения в столбце должны иметь такой же формат, иначе фильтрация и просчеты будут неправильными.
Пробелы, цифры и символы в именах. В имени, которое содержит два или несколько полей, их порядок не имеет значения. В примере выше ячейки C6:D6 могут называться ‘Апрель Север’ или ‘Север Апрель’. Имена, которые состоят из нескольких слов либо содержат цифры или символы, нужно заключать в одинарные кавычки.
ЗАДАНИЕ
Для таблицы своего варианта из лабораторной работы № 4 Списки в Excel . Сортировка и фильтрация данных построить две сводные таблицы. Поля, помещаемые в области строк, столбцов и данных выбрать самостоятельно.
Результаты (две сводные таблицы) сохранить на дискете или другом носителе.
Добавьте поля значений в сводную таблицу
- использование списка (базы данных Excel );
- использование внешнего источника данных;
- использование нескольких диапазонов консолидации;
- использование данных из другой сводной таблицы.
Файл → Параметры → Надстройки → Надстройки COM → Microsoft Power Pivot.
| Функция | Результат |
|---|---|
| Отличие | Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в списках, поле и элемент |
| Доля | Значения ячеек области данных отображаются в процентах к заданному элементу, указанному в списках поле и элементам. |
| Приведенное отличие | Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в стеках поле и элемент, нормированной к значению этого элемента |
| С нарастающим итогом в поле | Значения ячеек области данных отображаются в виде нарастающего итога для последовательных элементов. Следует выбрать поле, элементы которого будут отображаться в нарастающем итоге |
| Доля от суммы по строке | Значения ячеек области данных отображаются в Процентах от итога строки |
| Доля от суммы по столбцу | Значения ячеек области данных отображаются в Процентах от итога столбца |
| Доля от общей суммы | Значения ячеек области данных отображаются в процентах от общего итога сводной таблицы |
| Индекс | При определении значений ячеек области данных используется следующий алгоритм: ((Значение в ячейке) * (Общий итог))/((Итог строки) *(Итог столбца) |
