Разное

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

Содержание

Сводная таблица с текстовыми полями в области значений

 

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

ИНСТРУКЦИЯ ПО ДОБАВЛЕНИЮ ТЕКСТОВЫХ ПОЛЕЙ В СВОДНУЮ ТАБЛИЦУ

Чтобы с помощью надстройки Power Pivot добавить текст в сводную таблицу, нужно выполнить последовательно следующие действия:

 

Шаг 1. Включаем надстройку Power Pivot.

Надстройка Power Pivot входит в стандартный комплект Excel 2013, 2016 и Excel 365 для Windows. Она подключается одной галочкой в окне надстроек:

ФайлПараметрыНадстройкиНадстройки COMMicrosoft Power Pivot.

 

Шаг 2. Загружаем исходную таблицу Exсel в модель данных Power Pivot и создаем сводную таблицу с подключением к модели. В качестве примера возьмем таблицу (рис. 1), в которой ведутся остатки ассортимента в магазине обуви.

 

Исходную таблицу с информацией об остатках обуви загружаем в модель данных Power Pivot, как показано на рис. 2.

Выполнив загрузку, выходим из окна Power Pivot, создаем новый лист, через команду «Вставка» строим сводную таблицу.

Важный момент: в случае с Power Pivot для создания сводной таблицы выделять исходную таблицу не нужно.

 

Шаг 3. Добавим в область строк сводной таблицы следующие поля: Тип обуви, Полное наименование, Размеры, Цвет (рис. 3).

Для начала подсчитаем количество уникальных цветов для выбранной группировки.

По аналогии с вычисляемыми полями сводной таблицы в Power Pivot есть меры, которые создают такие же вычисляемые поля.

В отличие от классических вычисляемых полей меры более функциональны и интуитивно понятны.

Чтобы создать меру, нужно на вкладке Power Pivot выбрать «Меры» и «Создать меру» (см. рис. 3).

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

Цвета:=

COUNTROWS(VALUES(‘Остатки'[Цвет])).

 

Функция VALUES() создает таблицу из одного столбца с уникальными значениями (в нашем случае это «коричневый», «синий», «черный»).

Функция COUNTROWS() подсчитывает в таблице количество строк.

К мере применяются фильтры сводной таблицы (учитываются ее разрезы).

Рассмотрим, как считает написанная мера.

В группировке Ботильонов и следующего наименования обуви указано 3 — максимальное количество уникальных цветов. Все три цвета повторяются в первом по порядку размере 38, поэтому у данного размера тоже 3. В размере 39 только один цвет, поэтому стоит 1 (рис. 5).

 

Если функция VALUES() имеет единственное значение, то в значения сводной таблицы его можно добавить текстом, а не только рассчитать количество таких значений, чем ограничивается стандартный Excel.

Предположим, нами создана мера, которая выглядит следующим образом:

Цвета:=

VALUES(‘Остатки'[Цвет]).

Мы получим сообщение об ошибке, так как в строках, где уникальных значений больше одного, результатом функции

VALUES() также является более одного значения (рис 6).

 

Чтобы обойти данную ошибку, нужно определить, когда VALUES() возвращает одно или несколько значений. Для этого воспользуемся функцией HASONEVALUE(). Она возвращает 1 (ИСТИНА), если в ней столбец с одним значением, и 0 (ЛОЖЬ), если столбец с несколькими значениями.

П. М. Чеглаков, ведущий аналитик АО «Тандер»

Как перейти к редактированию исходных данных прямо из сводной таблицы?

Хитрости » 19 Июнь 2020       Дмитрий       2410 просмотров

Несомненно, основная задача работы со сводными таблица — анализ данных. А раз мы что-то анализируем, значит может потребоваться и что-то изменить в случае нахождения каких-то расхождений. И самое печальное здесь то, что нельзя изменять значения непосредственно внутри сводной таблицы(OLAP не в счет :)). Зато мы можем посмотреть из каких строк исходной таблицы состоит конкретное значение.

Например, у нас есть таблица реализации следующего вида:

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

В итогах у нас значения по прибыли, а красным выделены отрицательные значения, т.к. именно к таким нам следует присмотреться в первую очередь. Чтобы понять из каких строк исходной таблицы получилась сумма -1155 мы можем выделить эту ячейку внутри сводной таблицы -правая кнопка мыши —Показать детали(Show Details):

В итоге будет создан новый лист с таблицей, содержащей только те строки исходных данных, на основании которых сформировано выделенное нами значение:

Да, мы теперь можем целенаправленно и точечно посмотреть, изучить только нужные данные и принять решение. Но тут другая проблема: если нам надо что-то изменить, то это ни на что не повлияет. Т.к. показ деталей из сводной никак не связан уже ни с исходными данными, ни с самой сводной таблицей. Как же быть? Можно попробовать вернуться в лист с исходными данными и отфильтровать последовательно каждый столбец до нужных значений. Но это явно не самый быстрый и точный путь. Поэтому его даже не рассматриваем. Я хочу предложить путь быстрее и эффективнее. После того как отобразили детали — ничего с этим листом пока не делать.

Переходим на лист с исходными данными -вкладка Данные(Data) -группа Сортировка и фильтр(Sort & Filter)Дополнительно(Advanced). В появившейся форме указываем следующие данные:

Исходные диапазон: $A$1:$H$4777 (у меня эти ячейки на листе Data. Указываем обязательно
с заголовками
)
Диапазон условий: Таблица2[#Все] (это как раз наша таблица деталей, которую мы отобразили из сводной таблицы. Обращаю особое внимание на то, что должно быть именно Таблица2[#Все], т.е. с заголовками)
Обязательно оставляем отмеченным пункт Фильтровать список на месте. Нажимаем Ок.
В итоге у нас в исходной таблице отфильтруются ровно те строки, которые были отображены в деталях:

Краткое видео процесса:

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

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

Но даже при всем этом: как-то это все долго и не очень удобно. Поэтому я решил пойти дальше и сделать все необходимое при помощи макросов(Visual Basic for Applications). Придется в них чуть-чуть вникнуть, но оно того стоит, т.к. для полного удобства мы сделаем вот что:

  • по двойному клину на ячейке сводной таблицы автоматически отфильтруем данные в исходной таблице и перейдем в неё
  • после изменений в исходной таблице и возврата в сводную — автоматически обновим эту сводную таблицу
  • для большего удобства мы еще создадим в меню правого клика сводной таблицы свой собственный пункт меню «Edit Source», который будет делать то же самое, что и двойной клик

Т. е. можно сказать полностью заменим стандартный пункт «Показать детали».

Для этого создаем стандартный модуль (переходим в редактор VBA(Alt+F11) —InsertModule) и вставляем в него код:

'---------------------------------------------------------------------------------------
' Author : Щербаков Дмитрий(The_Prist)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
'          https://www.excel-vba.ru
'          [email protected]
' Purpose:
'---------------------------------------------------------------------------------------
Option Explicit
 
Sub EditPivotSource()
    Dim pt As PivotTable
    Dim wsDetails As Worksheet
    Dim rSource As Range, rDetails As Range
    Dim lAppCalc As Long
 
    Application.DisplayAlerts = False
    lAppCalc = Application.Calculation 'запоминаем установленный режим пересчета формул
    Application.Calculation = xlCalculationManual
    Application.
ScreenUpdating = False On Error GoTo END_   'определяем сводную таблицу и её исходные данные Set pt = ActiveCell.PivotTable Set rSource = Application.Evaluate(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1)) 'отображаем все данные в листе с исходными данными rSource.EntireRow.Hidden = False 'разрешаем отображение деталей, если запрещено настройками ' Параметры сводной таблицы -Данные -Разрешить отображение деталей If Not pt.EnableDrilldown Then pt.EnableDrilldown = True End If 'показываем лист с данными по выделенной области Selection.ShowDetail = True 'запоминаем лист с деталями - потом надо будет удалить Set wsDetails = ActiveSheet Set rDetails = ActiveSheet.UsedRange   rSource.AdvancedFilter xlFilterInPlace, rDetails 'удаляем лист деталей - он больше не нужен wsDetails.Delete 'активируем лист с исходными данными - теперь там отображены только нужные строки rSource.Parent.Activate END_: If Err.
Number <> 0 Then MsgBox "Выделите ячейку данных для редактирования", vbInformation, "www.excel-vba.ru" End If 'возвращаем измененные настройки приложения в прежние значения Application.DisplayAlerts = True Application.Calculation = lAppCalc Application.ScreenUpdating = True End Sub

‘————————————————————————————— ‘ Author : Щербаков Дмитрий(The_Prist) ‘ Профессиональная разработка приложений для MS Office любой сложности ‘ Проведение тренингов по MS Excel ‘ https://www.excel-vba.ru ‘ [email protected] ‘ Purpose: ‘————————————————————————————— Option Explicit Sub EditPivotSource() Dim pt As PivotTable Dim wsDetails As Worksheet Dim rSource As Range, rDetails As Range Dim lAppCalc As Long Application. DisplayAlerts = False lAppCalc = Application.Calculation ‘запоминаем установленный режим пересчета формул Application.Calculation = xlCalculationManual Application.ScreenUpdating = False On Error GoTo END_ ‘определяем сводную таблицу и её исходные данные Set pt = ActiveCell.PivotTable Set rSource = Application.Evaluate(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1)) ‘отображаем все данные в листе с исходными данными rSource.EntireRow.Hidden = False ‘разрешаем отображение деталей, если запрещено настройками ‘ Параметры сводной таблицы -Данные -Разрешить отображение деталей If Not pt.EnableDrilldown Then pt.EnableDrilldown = True End If ‘показываем лист с данными по выделенной области Selection.ShowDetail = True ‘запоминаем лист с деталями — потом надо будет удалить Set wsDetails = ActiveSheet Set rDetails = ActiveSheet. UsedRange rSource.AdvancedFilter xlFilterInPlace, rDetails ‘удаляем лист деталей — он больше не нужен wsDetails.Delete ‘активируем лист с исходными данными — теперь там отображены только нужные строки rSource.Parent.Activate END_: If Err.Number <> 0 Then MsgBox «Выделите ячейку данных для редактирования», vbInformation, «www.excel-vba.ru» End If ‘возвращаем измененные настройки приложения в прежние значения Application.DisplayAlerts = True Application.Calculation = lAppCalc Application.ScreenUpdating = True End Sub

Это основной код фильтрации данных в источнике данных на основании выделенной в сводной таблице ячейке.
Далее все в том же редакторе VBA переходим в модуль ЭтаКнига(ThisWorkbook) и вставляем туда следующий код:

'---------------------------------------------------------------------------------------
' Author : Щербаков Дмитрий(The_Prist)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
'          https://www. excel-vba.ru
'          [email protected]
' Purpose: Обработка двойного клика мыши в сводной таблице
'          и переход к сводной после редактирования источника данных
'
'          Так же при открытии книги создается пункт в меню правой кнопки мыши сводной - Edit Source
'          и удаляется перед закрытием этой книги
'---------------------------------------------------------------------------------------
Option Explicit
 
'при активации листа со сводной таблицей - обновляем все сводные
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim pt As PivotTable
    'обновляем все сводные таблицы на листе, на который перешли
    For Each pt In Sh.PivotTables
        pt.PivotCache.Refresh
    Next
End Sub
 
'обрабатываем двойной клик мыши внутри сводной таблицы
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim rcPT As PivotTable
    'проверяем, является ли ячейка,
    'на которой дважды щелкнули мышью
    'ячейкой внутри сводной таблицы
    On Error Resume Next
    Set rcPT = Target. PivotTable
    On Error GoTo 0
    'если это ячейка сводной
    If Not rcPT Is Nothing Then
        'вызываем процедуру фильтрации источника данных
        EditPivotSource
        Cancel = True
    End If
End Sub
 
'================================================================================
'              СОЗДАНИЕ И УДАЛЕНИЕ ПУНКТА МЕНЮ В СВОДНОЙ
'
'добавляем в меню сводных таблиц пункт "Edit Source",
'который будет отбирать данные непосредственно в источнике данных
Private 

Удалить сводную таблицу в excel

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

​Смотрите также​ таблицу. Очень часто​   ​ области​ одной из сторон​Названия столбцов​

​ в иерархии разных​

​дважды, а затем​ в процентах от​, иерархии значений дат​ нажмите кнопку​Дополнительные вычисления​ языке) .​    Выберите для отображения подсказок,​ объемами продаж. Для​ продажи. Для этого​

​ реализована партия Товара;​Если вам больше не​ в столбце ФИО​Чтобы выбрать функцию, выберите​

  1. ​Значения​ окна Excel и​только один раз​ измерений, иерархий и​​ настройте параметры​​ значения поля. В​

  2. ​ и времени — в​​Найти​​и​​Средство проверки совместимости обнаружило​​ значение, строку или​​ этого:​​ добавим поле Регион​

  3. ​Продажи – Стоимость, по​

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

Дополнительные сведения об использовании сводных таблиц в Excel
  • ​ область​, чтобы найти сводные​Без вычислений​

  • ​ одну или несколько​ столбец сведения отображались​

  • ​Выделите все ячейки содержащие​ продажи, поставив соответствующую​

  • ​ которой удалось реализовать​ полностью выделите ее​ в сводную таблицу​

  • ​Итоги​

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

​столбцов​ таблицы, содержащий именованный​). ​ проблем совместимости, связанных​ на значение поля​ значения продаж;​ галочку в Списке​​ партию Товара;​​ и нажмите клавишу​

Дополнительные сведения об использовании сводных таблиц в Excel Online

  • ​ попадают, но и​

  • ​вариант​ нужным образом функцию​ отменить закрепление списка​

  • ​ нечисловых типов данных.​и свертывания кнопок​

  • ​Дополнительные вычисления​ расходов на​

support.office.com>

Сводные таблицы в MS Excel

​, а числовые поля —​ набор свойств, которые​Книга содержит пользовательские внедренные​ в сводные

Редактирование сводных таблиц

Пользователь может менять формат сводной таблицы, переименовывать поля или элементы, добавлять новые строки или столбцы, а также делать другие преобразования с внешним видом сводной таблицы.

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

Упражнение. Изменить текст заголовка, отражаемого в ячейке А3 по умолчанию, на «Сумма продаж», для этого:

Для формирования новой сводной таблицы достаточно изменить макет существующей.

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

  • Установить курсор в любую ячейку созданной сводной таблицы. Если при этом на экран не выводится окно Список полей сводной таблицы, то необходимо на вкладке Параметры в группе Показать или скрыть нажать кнопку Список полей.

  • Для очистки макета оттащить поля, расположенные в нижней части окна Список полей сводной таблицы в любое место окна.

  • При помощи мыши перетащить поле «Наименование магазина» (4-е сверху) в область Названия строк, поле «Сумма» — в область Значения и сделать щелчок левой кнопкой мыши по данному полю. В открывшемся окне выбрать команду Параметры полей значений…, затем в опции Пользовательское имя ввести с клавиатуры «Сумма продаж» и нажать ОК.

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

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

  • Установить курсор в любую ячейку созданной сводной таблицы.

  • Для очистки макета оттащить поля в любое место окна.

  • При помощи мыши перетащить поле «Наименование магазина» в область Названия столбцов, поле «Вид продукции» — в область Названия строк, поле «Сумма» — в область Значения и сделать щелчок левой кнопкой мыши по кнопке данного поля. В открывшемся окне выбрать команду Параметры полей значений…, затем в открывшемся окне на вкладке Операция выбрать Среднее и нажать ОК.

  • Установить курсор в сводную таблицу.

  • Выбрать вкладку Параметры, в группе Сводные таблицы нажать кнопку Параметры.

  • В открывшемся окне Параметры сводной таблицы на вкладке Итоги и фильтры удалить флажки Показывать общие итоги для столбцов и Показывать общие итоги для строк. Нажать ОК.

Групповые операции в сводных таблицах.

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

Упражнение. Создать сводную таблицу с данными о количестве проданных товаров в определенных диапазонах цен, для этого:

  • Установить курсор в любую ячейку созданной сводной таблицы.

  • Для очистки макета оттащить поля в любое место окна.

  • При помощи мыши перетащить поле «Цена» в область Названия строк, поле «Количество» — в область Значения.

  • Установить курсор в ячейку А4 и щелкнуть правой кнопкой мыши для вызова контекстного меню.

  • В контекстном меню выбрать команду Группировать.

  • В открывшемся окне Группирование в опции С_шагом ввести c клавиатуры 300 (минимальная и максимальная цена товара — 50 и 800-устанавливается автоматически из списка) и нажать ОК.

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

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

Введение

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

Доступны и другие функции сводки, такие как Среднее, Макс. И Мин., Но в сводных таблицах Excel нет функций «Первая» и «Последняя», которые есть в Access, для отображения текстовых значений.

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