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

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

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

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

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

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

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

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

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

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

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

'---------------------------------------------------------------------------------------
' 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, для отображения текстовых значений.

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

Региональные данные

В этом примере представлены данные о продажах продуктов питания из 3 регионов.У каждого региона есть номер и название:

• 1 — Восток
• 2 — Центральный
• 3 — Запад

На основе этих данных была создана сводная таблица с городом в области строк, магазином в области столбцов и идентификатором региона в области значений.

ПРИМЕЧАНИЕ. Общие итоги отключены.

Показать номера регионов

Когда поле «Идентификатор региона» было добавлено в область «Значения», Excel автоматически установил для своей функции сводки значение «Сумма».Вместо суммы идентификационных номеров мы хотим видеть фактические идентификационные номера.

Чтобы показать это, измените функцию сводки на Макс .

• Щелкните правой кнопкой мыши одну из ячеек значений
• Во всплывающем меню щелкните Суммировать значения по, а затем щелкните Макс.

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

Изменить номера регионов на имена

Значения сводной таблицы теперь показывают правильный номер региона для каждого значения, но вместо чисел 1, 2 или 3 мы хотели бы видеть название региона — Восток, Центр или Запад.Чтобы показать текст, вы можете комбинировать условное форматирование с пользовательскими числовыми форматами.

Добавить условное форматирование вручную

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

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

• Выберите все ячейки значений в сводной таблице (B5: F8). ПРИМЕЧАНИЕ: B5 — активная ячейка, и вы можете увидеть ее адрес в NameBox
.
• На вкладке «Главная» ленты Excel щелкните «Условное форматирование».
• Затем щелкните Новое правило
• В разделе «Применить правило к» выберите 3-й вариант — Все ячейки, показывающие максимальное значение RegID для «Город» и «Магазин».Этот параметр создает гибкое условное форматирование, которое будет корректироваться при изменении макета сводной таблицы.
• Затем в разделе «Выбор типа правила» выберите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».

• В поле формулы введите формулу для идентификатора региона 1: = B5 = 1
• Нажмите кнопку «Формат», затем перейдите на вкладку «Число».
• В списке категорий щелкните Custom
• В поле Тип введите следующий формат чисел: [= 1] «Восток» ;;

  • Первая часть формата указывает Excel показывать «Восток» для любых положительных чисел, равных 1.
  • Две точки с запятой являются разделителями, и во 2-м разделе (отрицательные числа) или 3-м разделе (нули) пользовательского формата ничего нет.
• Дважды щелкните OK, чтобы закрыть окна.

Было применено правило условного форматирования для RegID 1, и в этих ячейках отображается текст «Восток» вместо числа 1. Выбрав ячейку B6, вы можете увидеть 1 в строке формул, даже если «Восток» отображается на листе.

Сделайте еще 2 правила

Затем снова выберите ячейки B5: F8 и выполните предыдущие шаги, чтобы создать еще 2 правила условного форматирования.

ID региона 2

• Формула: = B5 = 2
• Пользовательский формат чисел: [= 2] «Центральный» ;;

ID региона 3

• Формула: = B5 = 3
• Пользовательский формат чисел: [= 3] «Запад» ;;

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

При необходимости расширьте столбцы, чтобы отобразить текст.

Значения в текстовом макросе

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

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

 Option Explicit
Вариант База 1

Sub ApplyCFArrays ()
'Проф. Ли Таунсенд
'загружено с сайта context.com
'выберите ячейку сводной таблицы
'затем запустите этот макрос

Dim CFRange как строка
Dim pvt как сводная таблица
Dim CellOne как строка
Тусклое двоеточие Местоположение как целое число
Dim iLoop As Long
Тусклый регион Имена как вариант
Тусклый регион Числа как вариант
Уменьшить значение currentName как строку
Тусклая цитата как строка
regionNames = _
  Массив («Восток», «Центр», «Запад»)
regionNumbers = массив (1, 2, 3)
  
quote = Chr (34)

При ошибке Возобновить Далее
Установите pvt = ActiveCell. Сводная таблица
При ошибке GoTo 0
Если pvt - ничто, тогда
  MsgBox "Выберите сводную таблицу" _
     & vbCrLf _
     & "сотовый и попробуйте еще раз"
  Выход из подводной лодки
Конец, если

'найти расположение верхнего левого угла
DataBodyRange сводной таблицы
CFRange = pvt.DataBodyRange.Address
ColonLocation = InStr (CFRange, ":")
CellOne = Left (CFRange, двоеточиеLocation - 1)
CellOne = Replace (CellOne, "$", "")
  
'Запустить правило условного формата
CellOne = "=" & CellOne & "="

'Настроить массивы
https: // stackoverflow.com / questions
'/ 39509796 / есть-есть-путь-к
'присвоить значения массиву
'-in-vba-using-a-single-line
  ReDim Preserve regionNames (1 _
    В UBound (regionNames) + 1)
  ReDim Preserve regionNumbers (1 _
    На UBound (номера регионов) + 1)
  
'Настройте петлю
Для iLoop = от 1 до 3
  currentName = "[=" _
    & regionNumbers (iLoop) & "]" _
        & цитата и названия регионов (iLoop) _
        & цитата & ";;"
  
  С диапазоном (CFRange) .FormatConditions _
    . Add (Тип: = xlExpression, _
      Формула1: = CellOne _
      & номера регионов (iLoop))
    .NumberFormat = currentName
  Конец с
Следующий iLoop

Концевой переводник 

Загрузить образец файла

1. Без макросов : Загрузите учебное пособие по значениям сводной таблицы без макроса. Заархивированный файл имеет формат xlsx и не содержит макросов. Вы можете вручную применить условное форматирование.
2. Макрос : Загрузите рабочую книгу значений сводной таблицы с макросом для применения условного форматирования. Заархивированный файл имеет формат xlsm и содержит макросы.Не забудьте включить макросы при открытии файла, если вы хотите протестировать макросы.

Дополнительные руководства по сводным таблицам

Часто задаваемые вопросы — Сводные таблицы

Сводная таблица Введение

Защита поворотного стола

Очистить старые элементы в сводной таблице

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

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

В этой таблице вы можете создать сводную таблицу с комбинированными текстовыми значениями Ship_city и Ship_state. Для этого вам нужно использовать функцию CONCATENATE, которая позволит вам объединить столбцы вместе.

Синтаксис

= СЦЕПИТЬ (Текст1, [текст2,… текст_n])

ПРИМЕЧАНИЕ. Если вы используете Excel 2016, используйте функцию СЦЕП.

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

Объединить с пробелами

Чтобы добавить интервал между текстом соединения, введите эту команду

= СЦЕПИТЬ (C2, «», D2) в ячейке G2.

Вы также можете добавить запятую, чтобы объединенный текст был более читабельным.

= СЦЕПИТЬ (C2, «,«, D2)

Добавить сводную таблицу с объединенным столбцом адресов

Отформатируйте сводную таблицу для отображения данных в столбцах.

Перейдите в Инструменты поворота и щелкните меню дизайна. В группе макетов выберите макет отчета и выберите отображение в табличной форме. Данные будут отображаться, как показано ниже.

Объединить строки с разрывом строки

В Windows используйте CHAR (10), где 10 — это код ASCII для перевода строки.

На компьютерах Mac используйте CHAR (13), где 13 — это код ASCII для возврата каретки

В приведенном выше примере, чтобы получить комбинированный почтовый адрес клиента, используйте следующую формулу;

= B2 & «» и СИМВОЛ (10) & C2 & «,» & D2

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

После объединения текста можно перейти к созданию сводной таблицы с данными в объединенном столбце.

Вычисляемые столбцы в сводной таблице

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

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

Нажмите «Вставить сводную таблицу», в открывшемся окне выберите поля, которые вы хотите добавить в сводную таблицу.

После выбора нужных полей перейдите в меню анализа. В разделе «Расчеты» выберите поля, вкладку «Элементы и наборы», затем щелкните рассчитанные поля.

Введите значения и нажмите ОК.

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

Нравится:

Нравится Загрузка …

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

Мы можем подсчитать значения в сводной таблице , используя настройки поля значений . Это позволяет нам иметь достоверное представление о том, что у нас есть в наших данных. Например, в приведенном ниже примере для клиентов имеется счетчик , равный 16, тогда как отчетливо это только 4. Шаги ниже будут проходить через процесс подсчет значений и уникальных значений в сводной таблице.

Рисунок 1. Как подсчитать значения в сводной таблице

Настройка данных

• Мы откроем Новый лист Excel
• Мы введем данные , как показано на рис. 2 , в таблицу Excel

Рисунок 2- Настройка данных

• Мы будем щелкнуть в любом месте данных, щелкнем по вкладке Insert, и щелкнем по таблице , как показано на рис. 3

Рисунок 3- Включение данных в таблицу

• Мы нажмем на ОК в появившемся диалоговом окне

Рисунок 4. Диалоговое окно создания таблицы

Рисунок 5- Созданная таблица

• Щелкните поле под Имя таблицы под Файл в Рисунок 5 и назовем таблицу как Client_Data
• Мы будем нажать ввод после ввода имени
• Теперь мы создадим сводную таблицу с данными

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

• Мы нажмем в любом месте стола
• Щелкните вкладку Insert и щелкните сводную таблицу , как показано на рис. 3

Рисунок 6. Создание сводной таблицы

• Щелкните существующий рабочий лист и укажите местоположение, с которого будет начинаться сводная таблица (E3).Мы также проверим «Добавить эти данные в модель данных»
• Нажимаем на ОК
• Мы проверим поля (Страна, Клиент и Расходы) , которые мы хотим добавить в сводную таблицу .

Рисунок 7- Выбор полей сводной таблицы

• Мы перетащим каждое поле в соответствующее положение, как показано на рисунке 7

Рисунок 8- Созданная сводная таблица

• Мы заметим, что сводная таблица подсчитала всех клиентов и не учитывала повторение имен клиентов.Пример находится в США, у которого в качестве клиентов всего Judy Creations, M и B, и Sam Furnitures, .

Подсчет уникальных значений в сводной таблице

• Мы нажмем на любой счетчик в столбце G сводной таблицы
• Щелкните правой кнопкой мыши и выберите Параметры поля значений

Рисунок 9 — Диалоговое окно настроек поля значений

• Мы выберем уникальных счетчиков в поле « суммировать значения по» .
• Нажимаем ОК

Рисунок 10 — Подсчет уникальных значений в сводной таблице

Мгновенное соединение с экспертом через нашу службу Excelchat

В большинстве случаев задача, которую вам нужно будет решить, будет более сложной, чем простое применение формулы или функции.