Как изменить формат даты в сводной таблице excel - Word и Excel

На чтение 7 мин. Просмотров 36.4k.

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

Уровень мастерства: Средний

Изменение формата для дат не работает

Когда мы группируем поле «Дата» в сводной таблице с помощью функции «Группировать», форматирование чисел для поля «День» фиксируется. Он имеет следующий формат «день-месяц» или «d-ммм».

Если мы попытаемся изменить числовой формат поля День/Дата, это не сработает. Ничего не меняется, когда мы заходим в Настройки поля> Числовой формат и меняем числовой формат на пользовательский или формат даты.

Почему?

Форматирование чисел не работает, потому что элемент сводки
— это фактически текст, а НЕ дата.

Когда мы группируем поля, функция группирования создает
элемент Дни для каждого дня одного года. Он сохраняет название месяца в именах
полей Day, и фактически это группа номеров дней (1-31) для каждого месяца.

На самом деле можно увидеть этот список текстовых элементов в файле pivotCacheDefinition.xml. Чтобы увидеть, что вы можете изменить расширение файла Excel на .zip и перейти к папке PivotCache.

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

Решение № 1 — Не используйте группы дат

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

Я подробно объясняю это в своей статье «Группировка дат в сводной таблице». Источник данных.

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

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

Автоматическая группировка полей даты

Если вы используете Excel 2016 (Office 365), то поле даты
автоматически группируется при добавлении его в сводную таблицу.

Разгруппировать поле даты:

Выберите ячейку внутри сводной таблицы в одном
из полей даты.
Нажмите кнопку «Разгруппировать» на вкладке
«Анализ» ленты.

Автоматическая группировка является настройкой по умолчанию, которую можно изменить. См. мою статью «Даты группирования в сводной таблице». Даты группирования VERSUS приведены в исходных данных, чтобы узнать больше.

Как только поле даты будет разгруппировано, вы можете изменить форматирование поля.

Чтобы изменить форматирование чисел в несгруппированном поле
Дата:

Щелкните правой кнопкой мыши ячейку в поле даты
сводной таблицы.
Выберите настройки поля …
Нажмите кнопку «Числовой формат».
Измените форматирование даты в окне «Формат
ячеек».
Нажмите ОК и ОК.

Опять же, это работает только для полей, которые НЕ
сгруппированы. Если вы снова сгруппируете поле после изменения форматирования,
форматирование элементов в поле «Дни» изменится на «1 января».

Решение №2. Изменение имен элементов сводки с помощью VBA

Если вы действительно хотите использовать функцию Group Field, то мы можем использовать макрос для изменения имен элементов сводки. Создается впечатление, что изменилось форматирование даты, но на самом деле меняется текст в каждом названии элемента сводки.

Следующий макрос перебирает все сводные элементы сгруппированного поля «Дни» и изменяет форматирование чисел на пользовательский формат. По умолчанию я установил «m/d», но вы можете изменить его на любой формат даты для месяца и дня. Просто помните, что элемент НЕ будет содержать год, так как элемент не является фактической датой.

Скачать файл

Загрузите
файл Excel, который содержит макрос.

Pivot Table Date Field Group Number Formatting Macro.xlsm (54.2 KB)

Макрос форматирования поля «Дни»

Sub Change_Days_Field_Formatting()
' Измените форматирование чисел в поле Дни
' для поля даты сгруппированной сводной таблицы.

Dim pt As PivotTable
Dim pi As PivotItem


' ВАЖНО: Измените следующее имя
' Сгруппированное поле Дни. Обычно это дни или название
' вашего поля даты.

Const sDaysField As String = "Days"

  ' Установить ссылку на первую сводную таблицу на листе
  ' Это может быть изменено для ссылки на имя сводной таблицы
  'Set pt = ActiveSheet.PivotTables("PivotTable1")
  
  Set pt = ActiveSheet.PivotTables(1)

  ' Установите имена обратно к их имени источника по умолчанию
  For Each pi In pt.PivotFields(sDaysField).PivotItems
    ' Обходит первый и последний пункты "<01.01.2015" ...
    If Left(pi.Name, 1) <> "<" And Left(pi.Name, 1) <> ">" Then
      pi.Name = pi.SourceName
    End If
  Next pi

  ' Установите имена в произвольном формате чисел
  For Each pi In pt.PivotFields(sDaysField).PivotItems
    If Left(pi.Name, 1) <> "<" And Left(pi.Name, 1) <> ">" Then
      ' Измените приведенный ниже формат «m / d» на пользовательский числовой формат.
      ' 2020 год используется для високосного года.
      pi.Name = Format(DateValue(pi.SourceName & "-2020"), "m/d")
    End If
  Next pi

End Sub

Как работает макрос

Макрос сначала зацикливает элементы сводки в поле Дни, чтобы
восстановить имя элемента сводки в его исходное имя по умолчанию. Два разных
элемента не могут иметь одинаковое имя. Так что это должно предотвратить любые
ошибки при изменении или попытке разных форматов.

Второй цикл меняет каждый элемент сводки на новый формат. Он
использует функцию DateValue для изменения названия элемента сводки «1-Jan» на
дату. Затем он использует функцию «Формат», чтобы изменить форматирование даты
на текст. По умолчанию используется формат «m / d». Это может быть изменено на
другой формат с месяцем и днем. Каждый элемент должен быть уникальным, поэтому
вы можете использовать месяц и день в названии элемента.

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

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

Макрос форматирования сгруппированных элементов

Был задан большой вопрос в комментариях об изменении форматирования для сгруппированных чисел. Это та же проблема, что и для групп дат. Имена групп — это текст, а не цифры. Тем не менее, мы можем использовать макрос, чтобы изменить их.

Вот код макроса. Вам просто нужно изменить значение
константы sGroupField вверху на имя вашего сгруппированного поля. При
необходимости вы также можете изменить формат чисел в sNumberFormat.

Sub Change_Grouped_Field_Number_Formatting()
' Изменить форматирование номера поля сгруппированных номеров

Dim pt As PivotTable
Dim pi As PivotItem
Dim sGroup() As String
Dim sGroupName As String


' ВАЖНО: Измените следующее имя
' поле сгруппированного номера в области строк или столбцов.

Const sGroupedField As String = "Quantity"
Const sNumberFormat As String = "$#,###"

  ' Установить ссылку на первую сводную таблицу на листе
  ' Это может быть изменено для ссылки на имя сводной таблицы
  'Set pt = ActiveSheet.PivotTables("PivotTable1")
  
  Set pt = ActiveSheet.PivotTables(1)

  ' Установите имена обратно к их имени источника по умолчанию
  For Each pi In pt.PivotFields(sGroupedField).PivotItems
    ' Обходит первый и последний пункты "<01.01.2015" ...
    If Left(pi.Name, 1) <> "<" And Left(pi.Name, 1) <> ">" Then
      pi.Name = pi.SourceName
    End If
  Next pi

  ' Установите имена в произвольном формате чисел
  For Each pi In pt.PivotFields(sGroupedField).PivotItems
  
    If Left(pi.Name, 1) = "<" Or Left(pi.Name, 1) = ">" Then
      sGroupName = "<" & Format(Mid(pi.Name, 2, Len(pi.Name)), sNumberFormat)
    Else
      sGroup = Split(pi.Name, "-")
      sGroupName = Format(sGroup(0), sNumberFormat) & " - " & Format(sGroup(1), sNumberFormat)
    End If
    
    If sGroupName <> "" Then pi.Name = sGroupName
      
  Next pi

End Sub

Окончательный вердикт

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

Пожалуйста, оставьте комментарий ниже с любыми вопросами или
предложениями о том, как мы можем улучшить это. Спасибо! 🙂

Источник

alvevo

Пользователь

Сообщений: 18
Регистрация: 15.05.2013

Поискал по форуму, но ответа не нашел. Проблема следующая:
Имеется исходная таблица с полем даты в формате 01.01.2013
На базе таблицы делаю сводную, где поле даты группирую по годам, месяцам и датам.
В итоге все корректно группируется, но в ячейках с датами формат отображается как 01.янв
Никакие попытки изменить этот формат не приводят к успеху, а хотелось бы, чтобы даты отображались в таком же виде, как в исходной таблице.
Подскажите, есть ли решение?

Изменено: alvevo — 15.05.2013 20:26:46

Пользователь

Сообщений: 6111
Регистрация: 21.12.2012

Win 10, MSO 2013 SP1

15.05.2013 12:49:15

Цитата
alvevo пишет: Подскажите, есть ли решение?

Сделайте, как вариант, в данных дубль поля с датами — по одному группировка, по второму — нет…

«Ctrl+S» — достойное завершение ваших гениальных мыслей!..

alvevo Пользователь Сообщений: 18 Регистрация: 15.05.2013	Это как-то не комильфо. Да и в сводной в итоге глупость получится — две колонки с датами в разных форматах. Хотелось бы более элегантного решения. Изменено: alvevo — 15.05.2013 20:27:26

	При группировке по датам изменить формат отображения невозможно.

alvevo Пользователь Сообщений: 18 Регистрация: 15.05.2013

CfifNfyz

Пользователь

Сообщений: 4
Регистрация: 20.12.2016

20.12.2016 11:13:37

Цитата
Михаил С. написал: При группировке по датам изменить формат отображения невозможно.

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

Изменено: CfifNfyz — 20.12.2016 11:14:14

Андрей VG

Пользователь

Сообщений: 11878
Регистрация: 22.12.2012

Excel 2016, 365

20.12.2016 12:02:09

Доброе время суток.

Цитата
CfifNfyz написал: изменить формат даты в итоговой строке

Что значит формат даты в итоговой строке? А в общем, решение простое, создать в таблице столбец(ы) с требумым форматом даты для последующей группировки. Возможно, при этом потребуется создать список(и) для указания правильной сортировки в сводной (подобно списку названий месяцев).

CfifNfyz

Пользователь

Сообщений: 4
Регистрация: 20.12.2016

Проблема в следующем: изначально был формат даты типа 01.01.2015. Соответственно итоги выглядели как «01.01.2015 итого»
Формат изменила на «Январь 2015», а в итоговой строке остался прежний формат «01.01.2015 итого».

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

Андрей VG

Пользователь

Сообщений: 11878
Регистрация: 22.12.2012

Excel 2016, 365

20.12.2016 12:30:01

Цитата
CfifNfyz написал: В одной таблице… а в другой таблице….

Можете продемострировать в файле-примере?

CfifNfyz

Пользователь

Сообщений: 4
Регистрация: 20.12.2016

Файл Excel с примером загрузить не могу, ругается на объем больше 100 кб, хотя уменьшала как могла.
Поэтому покажу на принскринах:
В одном случае дает изменить настройку поля и поменять формат, а в другом случае нет, хотя сами поля в таблицах идентичны.

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

Прикрепленные файлы

11.jpg (35.64 КБ)
22.jpg (60.41 КБ)

karinana Пользователь Сообщений: 1 Регистрация: 06.06.2017	Правой кнопкой по столбцу сводной таблицы — Ungroup.

guard1988 Пользователь Сообщений: 2 Регистрация: 22.12.2018	karinana, Вы — гений, спасибо!

Янек Пользователь Сообщений: 1 Регистрация: 10.05.2018	karinana, спасибо большое!!!

freddemo Пользователь Сообщений: 6 Регистрация: 10.03.2019

RoboFish Пользователь Сообщений: 12 Регистрация: 11.10.2019	karinana, спасибо большое!

Добрый день.
Если в сводной таблице навести на список дат (или сгруппированных периодов) нажать правой кнопкой, то можно выбрать Группировать / Разгруппировать.
В большинстве случаев это помогает.
Бывает, что даты выгружаются из баз данных в апострофом ( ‘ ). В этом случае их перевести в другой вид можно =—«ссылка на ячейку с датой». Обычно это ошибку исправляет и дает возможность группировать и разгруппировывать периоды.

abc4987

Пользователь

Сообщений: 2
Регистрация: 02.02.2018

#18

13.03.2022 13:52:42

Цитата
написал: Подскажите, есть ли решение?

Параметры поля > Числовой формат > Формат ячеек

https://disk.yandex.ru/d/VKD-SMj54baYjg

Изменено: abc4987 — 13.03.2022 13:57:21

mitox Пользователь Сообщений: 298 Регистрация: 20.01.2020	karinana, спасибо большое

dend88 Пользователь Сообщений: 12 Регистрация: 29.04.2019	#20 24.03.2023 16:04:17 karinana , Вы — золотце, Дай бог вам здоровья!

Источник

Отображение дат в сводной таблице

nikitan95

Дата: Воскресенье, 11.02.2018, 19:25 |
Сообщение № 1

Группа: Проверенные

Ранг: Обитатель

Сообщений: 410

Репутация:
0
±

Замечаний:
0% ±

2016

Доброго всем вечера!
Как изменить отображение дат по столбцам в сводной таблице? Изменения как в формате ячеек, так и через «Числовое поле» ничего не дали..

К сообщению приложен файл:

4320376.xlsx
(18.5 Kb)

Ответить

Che79

Дата: Воскресенье, 11.02.2018, 20:12 |
Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 1649

Репутация:
306
±

Замечаний:
0% ±

2013 Win, 365 Mac

nikitan95, здравствуйте.
Вариант навскидку. Вдруг подойдет. См. Лист 1

Делай нормально и будет нормально!

Ответить

abtextime

Дата: Воскресенье, 11.02.2018, 20:32 |
Сообщение № 3

Группа: Проверенные

Ранг: Ветеран

Сообщений: 828

Репутация:
117
±

Замечаний:
0% ±

Excel 2010

Дополнительный столбец в исходной таблице и ЛЕВБ() — не подойдет?

Код

=ЛЕВБ(ТЕКСТ(C2;»ДД.ММ.ГГ»);2)

Сообщение отредактировал abtextime — Воскресенье, 11.02.2018, 20:43

Ответить

nikitan95

Дата: Воскресенье, 11.02.2018, 20:46 |
Сообщение № 4

Группа: Проверенные

Ранг: Обитатель

Сообщений: 410

Репутация:
0
±

Замечаний:
0% ±

2016

Che79, abtextime, спасибо, оба варианта об одном же. Я пробовал это. Хотелось без доп.столбцов. Значит есть какие-то ограничения в сводных таблицах насчёт отображения дат?

Сообщение отредактировал nikitan95 — Воскресенье, 11.02.2018, 20:53

Ответить

abtextime

Дата: Воскресенье, 11.02.2018, 20:55 |
Сообщение № 5

Группа: Проверенные

Ранг: Ветеран

Сообщений: 828

Репутация:
117
±

Замечаний:
0% ±

Excel 2010

Похоже на то …
А чем конкретно Вас смущает скрытый столбец? Кому-то мешает разве? Лично меня это никогда не останавливало
Всегда в исходной таблице пихаешь всё. что можно, а потом в сводной уже всегда можно выключить )

Ответить

nikitan95

Дата: Воскресенье, 11.02.2018, 21:02 |
Сообщение № 6

Группа: Проверенные

Ранг: Обитатель

Сообщений: 410

Репутация:
0
±

Замечаний:
0% ±

2016

abtextime, ох как смущает) в исходном файле, при выборе месяца в фильтре, даты автоматически раскидаются по столбцам, а в предложенном примере, насколько я понял, придётся вручную выбирать даты. Канительно получается очень..

Сообщение отредактировал nikitan95 — Воскресенье, 11.02.2018, 21:04

Ответить

bmv98rus

Дата: Воскресенье, 11.02.2018, 21:05 |
Сообщение № 7

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4009

Репутация:
760
±

Замечаний:
0% ±

Excel 2013/2016

Такой вариант подойдет?

Правда там MS Query как источник для сводной.

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Сообщение отредактировал bmv98rus — Воскресенье, 11.02.2018, 22:01

Ответить

abtextime

Дата: Воскресенье, 11.02.2018, 21:08 |
Сообщение № 8

Группа: Проверенные

Ранг: Ветеран

Сообщений: 828

Репутация:
117
±

Замечаний:
0% ±

Excel 2010

nikitan95, не понимаю я Вас … о какой ручной выборке дат Вы ведете речь? Разве я что-то, кроме одной вышеприведенной формулы вводил?
что значит — «даты раскидываются по столбцам»? где? в сводной? так и у меня тоже «раскидываются»
Точно так же выбираете февраль в фильтре и получаете ведомость по февралю

Ответить

Karataev

Дата: Воскресенье, 11.02.2018, 21:13 |
Сообщение № 9

Группа: Проверенные

Ранг: Старожил

Сообщений: 1330

Репутация:
528
±

Замечаний:
0% ±

Excel

nikitan95, щелкните по ячейке «B4» правой кнопкой мыши — Разгруппировать.

Киви-кошелек: 9166309108

Ответить

nikitan95

Дата: Воскресенье, 11.02.2018, 21:19 |
Сообщение № 10

Группа: Проверенные

Ранг: Обитатель

Сообщений: 410

Репутация:
0
±

Замечаний:
0% ±

2016

bmv98rus, не знаю что за черт этот MS Query, но работает. Спасибо.
abtextime, может второпях у меня что-то не получилось с тем примером. Завтра с трезвой головой посмотрю и отпишусь. Спасибо

Ответить

nikitan95

Дата: Воскресенье, 11.02.2018, 21:23 |
Сообщение № 11

Группа: Проверенные

Ранг: Обитатель

Сообщений: 410

Репутация:
0
±

Замечаний:
0% ±

2016

Karataev, вот оно гениальное решение!! Салют! Ура!!! Вы очень приятно подытожили мой день. Спасибо, мой друг!

Ответить

Karataev

Дата: Воскресенье, 11.02.2018, 21:24 |
Сообщение № 12

Группа: Проверенные

Ранг: Старожил

Сообщений: 1330

Репутация:
528
±

Замечаний:
0% ±

Excel

nikitan95, нет, то не решение. В этом случае фильтр удаляется. Я сразу это не понял.

Киви-кошелек: 9166309108

Ответить

nikitan95

Дата: Воскресенье, 11.02.2018, 21:33 |
Сообщение № 13

Группа: Проверенные

Ранг: Обитатель

Сообщений: 410

Репутация:
0
±

Замечаний:
0% ±

2016

Дааа, очень было простым, чтобы быть верным(.. Меня удивляет, почему так? В чем подвох тут?

Ответить

Nic70y

Дата: Воскресенье, 11.02.2018, 21:39 |
Сообщение № 14

Группа: Друзья

Ранг: Экселист

Сообщений: 8136

Репутация:
1999
±

Замечаний:
0% ±

Excel 2010

Вдруг правильно
Ctrl+h
.янв — Alt0160
.фев — Alt0160Alt0160
и т.д.

ЮMoney 41001841029809

Сообщение отредактировал Nic70y — Воскресенье, 11.02.2018, 21:39

Ответить

nikitan95

Дата: Воскресенье, 11.02.2018, 21:51 |
Сообщение № 15

Группа: Проверенные

Ранг: Обитатель

Сообщений: 410

Репутация:
0
±

Замечаний:
0% ±

2016

Nic70y, работает. Пока более оптимальным считаю этот. Спасибо

Ответить

Karataev

Дата: Понедельник, 12.02.2018, 09:26 |
Сообщение № 16

Группа: Проверенные

Ранг: Старожил

Сообщений: 1330

Репутация:
528
±

Замечаний:
0% ±

Excel

Меня удивляет, почему так? В чем подвох тут?

Подвох в том, что Excel подставляет в строку 4 не даты, а текст вида «число.месяц». А числовой формат работает только с числами и датами.
Если сделать разгруппировку, то в строке 4 оказываются даты, к которым может быть применен числовой формат.

Киви-кошелек: 9166309108

Ответить

nikitan95

Дата: Понедельник, 12.02.2018, 11:00 |
Сообщение № 17

Группа: Проверенные

Ранг: Обитатель

Сообщений: 410

Репутация:
0
±

Замечаний:
0% ±

2016

Karataev, спасибо за разъяснение. Остановился на варианте abtextime

Ответить

mechanix85

Дата: Понедельник, 12.02.2018, 20:31 |
Сообщение № 18

Группа: Друзья

Ранг: Форумчанин

Сообщений: 240

Репутация:
34
±

Замечаний:
0% ±

nikitan95, нужно создать календарь и связать его с базой данных, это даст возможность выводить разные форматы дат

Ответить

nikitan95

Дата: Понедельник, 19.02.2018, 09:02 |
Сообщение № 19

Группа: Проверенные

Ранг: Обитатель

Сообщений: 410

Репутация:
0
±

Замечаний:
0% ±

2016

нужно создать календарь и связать его с базой данных

не подскажете как?

Ответить

Источник

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

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

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

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

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

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Конструктор в группе Макет нажмите кнопку Макет отчета и выберите один из указанных ниже вариантов.
- Чтобы связанные данные не выходили по горизонтали за пределы экрана (это позволяет меньше пользоваться прокруткой), выберите команду Показать в сжатой форме.
  
  В сжатой форме поля находятся в одном столбце и отображаются с отступами, чтобы показать вложенность столбцов.
- Чтобы отобразить структуру данных в классическом стиле сводной таблицы, выберите команду Показать в форме структуры.
- Для просмотра всех данных в традиционном формате таблицы, в котором можно легко копировать ячейки на другие листы, выберите команду Показать в табличной форме.

В сводной таблице выберите поле строки.

На ленте откроется вкладка Работа со сводными таблицами.

Также в форме структуры или табличной форме можно дважды щелкнуть поле строки и перейти к действию 3.
На вкладке Анализ или Параметры в группе Активное поле нажмите кнопку Параметры поля.
В диалоговом окне Параметры поля откройте вкладку Разметка и печать и в разделе Макет выполните одно из указанных ниже действий.
- Чтобы элементы полей отображались в форме структуры, установите переключатель в виде структуры.
- Чтобы отобразить или скрыть подписи следующего поля в этом же столбце в сжатой форме, щелкните переключатель в виде структуры и установите флажок Отобразить подписи из следующего поля в том же столбце (сжатая форма).
- Чтобы элементы полей отображались в табличной форме, установите переключатель в виде таблицы.

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

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

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

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

Выполните одно или несколько из указанных ниже действий.

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

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

В сводной таблице, основанной на данных с листа Excel или внешних данных из источника, не относящегося к OLAP, можно добавить в область Значения несколько копий одного поля, чтобы отображать разные вычисления с помощью возможности Дополнительные вычисления. Например, можно сравнить вычисления (такие как коэффициенты валовой и чистой прибыли, минимальные и максимальные показатели продаж или количество клиентов и процент от их общего числа) расположив их рядом. Дополнительные сведения см. в статье Отображение различных вычислений в полях значений сводной таблицы.

Щелкните имя поля в разделе полей и, удерживая нажатой кнопку мыши, перетащите его в область Значения в разделе макета.
Повторите действие 1 столько раз, сколько нужно копий поля.
В каждом скопированном поле измените нужным образом функцию сведения или настраиваемое вычисление.
Примечания:
- При добавлении в область значений двух или нескольких полей (как копий одного поля, так и разных полей) список полей автоматически добавляет в область Значения название столбца значений. С помощью этого поля можно перемещать поле вверх и вниз в области Значения. Название столбца значений можно даже переместить в область Названия столбцов или Названия строк. Однако переместить название столбца значений в область фильтров отчетов нельзя.
- Поле можно добавить в область Фильтр отчета, Названия строк или Названия столбцов только один раз как для числовых, так и для нечисловых типов данных. При попытке добавить одно поле несколько раз (например, в области Названия строк и Названия столбцов в разделе макета) поле автоматически удаляется из исходной области и перемещается в новую.
- Кроме того, добавить копию поля в область Значения можно с помощью формулы (также называемой вычисляемым столбцом), в которой используется это поле.
- Одно и то же поле невозможно добавить несколько раз в сводную таблицу, созданную на основе источника данных OLAP.

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

Отчет сводной таблицы	Описание	Сводная диаграмма	Описание
Значения	Используются для вывода итоговых числовых данных.	Значения	Используются для вывода итоговых числовых данных.
Названия строк	Используются для отображения полей в виде строк сбоку от отчета. Строка, расположенная ниже, вложена в строку, непосредственно предшествующую ей.	Поля осей (категории)	Используются для отображения полей в качестве осей диаграммы.
Названия столбцов	Используются для отображения полей в виде столбцов сверху отчета. Столбец, расположенный ниже, вложен в столбец, непосредственно предшествующий ему.	Названия полей легенды (рядов)	Используются для отображения полей в легенде диаграммы.
Фильтр отчета	Используется для фильтрации всего отчета на основе выбранного элемента фильтра отчета.	Фильтр отчета	Используется для фильтрации всего отчета на основе выбранного элемента фильтра отчета.

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

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

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

Щелкните сводную таблицу.

На ленте откроется вкладка Работа со сводными таблицами.
Если нужно отобразить список полей сводной таблицы, на вкладке Анализ или Параметры в группе Показать нажмите кнопку Список полей. Вы также можете щелкнуть правой кнопкой мыши сводную таблицу и выбрать Показать список полей.
Чтобы удалить поле, в списке полей сводной таблицы выполните одно из следующих действий.
- В списке полей сводной таблицы снимите флажок рядом с именем поля.
  
  Примечание: Снятие флажка в списке полей приводит к удалению из отчета всех экземпляров поля.
- В области макета щелкните имя поля и выберите пункт Удалить поле.
- Щелкните имя поля в разделе макета и, удерживая нажатой кнопку мыши, перетащите его из списка полей сводной таблицы.

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

Включение и отключение заголовков полей столбцов и строк

Щелкните сводную таблицу.

На ленте откроется вкладка Работа со сводными таблицами.
Чтобы переключаться между режимами отображения и скрытия заголовков полей, на вкладке Анализ или Параметры в группе Показать нажмите кнопку Заголовки полей.

Отображение промежуточных итогов выше или ниже их строк

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

На ленте откроется вкладка Работа со сводными таблицами.

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

Примечание: Если выбрать вариант Нет, промежуточные итоги будут отключены.
На вкладке Разметка и печать в группе Макет выберите вариант В виде структуры и выполните одно из следующих действий.
- Чтобы промежуточные итоги отображались над суммируемыми строками, установите флажок Промежуточные итоги в заголовке группы. Этот вариант выбран по умолчанию.
- Чтобы промежуточные итоги отображались под суммируемыми строками, снимите флажок Промежуточные итоги в заголовке группы.

Изменение порядка элементов строк и столбцов

Выполните любое из следующих действий.

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

Настройка ширины столбцов при обновлении.

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры сводной таблицы на вкладке Макет и формат в группе Формат выполните одно из указанных ниже действий.
- Чтобы столбцы сводной таблицы автоматически изменялись по размеру самого широкого текста или числового значения, установите флажок Автоматически изменять ширину столбцов при обновлении.
- Чтобы сохранить текущую ширину столбца сводной таблицы, снимите флажок Автоматически изменять ширину столбцов при обновлении.

Перемещение столбца в область названий строк или строки в область названий столбцов

Для оптимизации структуры и удобочитаемости сводной таблицы может потребоваться переместить поле столбца в область названий строк или поле строки в область названий столбцов. При перемещении столбца в строку или строки в столбец выполняется транспонирование вертикальной или горизонтальной ориентации поля. Эта операция также называется «поворотом» строки или столбца.

Используйте команду правой кнопки мыши

Выполните любое из следующих действий.

Щелкните поле строки правой кнопкой мыши, наведите указатель на команду Переместить <имя поля> и выберите пункт Переместить <имя поля> в столбцы.
Щелкните поле столбца правой кнопкой мыши и выберите команду Переместить <имя поля> в строки.

Используйте перетаскивание

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

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

А. Щелкните поле столбца	б) Прокрутите списки Избранное и Контактные лица для веб-поддержки. Перетащите его в область строки	В. Поле «Спорт» станет полем строки, как и «Регион»

Объединение и отмена объединения ячеек для элементов внешних строк и столбцов

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

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры сводной таблицы откройте вкладку Макет и формат, а затем в группе Макет установите или снимите флажок Объединить и выровнять по центру ячейки с подписями.

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

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

Изменение способа отображения ошибок и пустых ячеек

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры сводной таблицы откройте вкладку Макет и формат, а затем в группе Формат выполните одно или несколько из следующих действий.
- Чтобы изменить способ отображения ошибок, установите флажок Для ошибок отображать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
- Чтобы изменить способ отображения пустых ячеек, установите флажок Для пустых ячеек отображать и введите в текстовом поле значение, которое будет отображаться в пустых ячейках.
  
  Совет: Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.

Отображение и скрытие пустых строк после строк или элементов

Для строк выполните следующие действия.

В сводной таблице выберите поле строки.

На ленте откроется вкладка Работа со сводными таблицами.

Совет: В форме структуры или табличной форме также можно дважды щелкнуть поле строки и перейти к действию 3.
На вкладке Анализ или Параметры в группе Активное поле нажмите кнопку Параметры поля.
В диалоговом окне Параметры поля на вкладке Разметка и печать в группе Макет установите или снимите флажок Пустая строка после каждой подписи.

Для элементов выполните следующие действия.

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

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Конструктор в группе Макет нажмите кнопку Пустые строки и установите флажок Вставить пустую строку после каждого элемента или Удалить пустую строку после каждого элемента.

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

Изменение способа отображения элементов и подписей без данных

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.
На вкладке Вывод в группе Вывод выполните одно или несколько из следующих действий.
- Чтобы отображать или скрывать элементы без значений в строках, установите или снимите флажок Показывать элементы без данных в строках.
  
  Примечание: Этот параметр доступен только для источника данных OLAP.
- Чтобы отображать или скрывать элементы без значений в столбцах, установите или снимите флажок Показывать элементы без данных в столбцах.
  
  Примечание: Этот параметр доступен только для источников данных OLAP.
- Чтобы отображать или скрывать подписи элементов при отсутствии полей в области значений, установите или снимите флажок Показывать подписи элементов при отсутствии полей в области значений.
  
  Примечание: Этот флажок применим только к сводным таблицам, созданным с использованием версий Excel, предшествующих Office Excel 2007.

Можно выбрать стиль из большой коллекции стилей сводных таблиц. Кроме того, можно управлять чередованием в отчете. Быстрым способом применения единого формата во всем отчете является изменение числового формата поля. Также можно добавлять чередование (темного и светлого фона) строк и столбцов. Чередование может упростить восприятие и поиск данных.

Применение стиля для форматирования сводной таблицы

Быстро изменить внешний вид и формат сводной таблицы можно с помощью одного из готовых стилей сводных таблиц (или экспресс-стилей).

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Конструктор в группе Стили сводной таблицы выполните любое из следующих действий.
- Щелкните отображаемый стиль сводной таблицы или прокрутите коллекцию, чтобы увидеть другие стили.
- Чтобы посмотреть все доступные стили, нажмите кнопку Дополнительные параметры внизу панели прокрутки.
  
  Если нужно создать собственный стиль сводной таблицы, выберите команду Создать стиль сводной таблицы внизу коллекции, чтобы открыть диалоговое окно Создание стиля сводной таблицы.

Применение чередования для изменения формата сводной таблицы

Щелкните в любом месте сводной таблицы.

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

Удаление стиля или формата чередования из сводной таблицы

Щелкните в любом месте сводной таблицы.

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

Условное форматирование данных в сводной таблице

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

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

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

Дополнительные сведения см. в статье Применение условного форматирования.

Изменение числового формата поля

Выделите в сводной таблице нужное поле.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Активное поле нажмите кнопку Параметры поля.

В диалоговом окне Параметры поля отображаются названия и фильтры отчета; в диалоговом окне Параметры поля значений отображаются значения.
В нижней части диалогового окна нажмите кнопку Числовой формат.
В диалоговом окне Формат ячеек в списке Категории выберите нужный числовой формат.
Укажите нужные параметры и дважды нажмите кнопку ОК.

Можно также щелкнуть поле значения правой кнопкой мыши и выбрать параметр Числовой формат.

Включение форматирования сервера OLAP

Если вы подключены к базе данных Microsoft SQL Server Analysis Services Online Analytical Processing (OLAP), вы можете указать, какие форматы сервера OLAP следует извлекать и отображать вместе с данными.

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Данные нажмите кнопку Изменить источник данных и выберите пункт Свойства подключения.
В диалоговом окне Свойства подключения на вкладке Использование в разделе Форматирование сервера OLAP выполните одно из следующих действий.
- Чтобы включить или отключить форматирование чисел, таких как денежные значения, даты и время, установите или снимите флажок Числовой формат.
- Чтобы включить или отключить стили шрифта, такие как полужирный, курсив, подчеркнутый и зачеркнутый, установите или снимите флажок Стиль шрифта.
- Чтобы включить или отключить цвета заливки, установите или снимите флажок Цвет заливки.
- Чтобы включить или отключить цвета текста, установите или снимите флажок Цвет текста.

Сохранение и игнорирование форматирования

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.
На вкладке Макет и формат в группе Формат выполните одно из указанных ниже действий.
- Чтобы сохранить макет и формат сводной таблицы для применения при каждом выполнении операции со сводной таблицей, установите флажок Сохранять форматирование ячеек при обновлении.
- Чтобы игнорировать макет и формат сводной таблицы и возвращаться к стандартному макету и формату при каждом выполнении операции со сводной таблицей, снимите флажок Сохранять форматирование ячеек при обновлении.
Примечание: Хотя этот параметр также влияет на форматирование сводных диаграмм, линии тренда, подписи данных, пределы погрешностей и другие изменения определенных рядов данных не сохраняются.

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

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

Примечание: Раздел «Срез» отображается только в том случае, если к вашей сводной таблице подключен срез.

Чтобы показать общие итоги

Установите или снимите флажок Строки, Столбцы или оба.

Чтобы показать промежуточные итоги

Выберите Не показать, чтобы скрыть промежуточные итоги.
Выберите Сверху, чтобы отобразить их над значениями, которые они суммируют.
Выберите Внизу, чтобы отобразить их под значениями, которые они суммируют.

Размещение полей из области строк

Выберите Отдельные столбцы, чтобы предоставить отдельные фильтры для каждого поля строк, или Один столбец, чтобы объединить поля строк в один фильтр.

Чтобы показать или скрыть метки элементов

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

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

Выберите Показать или Не показывать.

Автоподгонка ширины столбцов при обновлении

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

Отображение кнопок развертывания и свертывания

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

Чтобы показать значения ошибок

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

Чтобы показать пустые ячейки

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

Чтобы сохранить исходные данные с файлом

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

Чтобы обновить данные при открытии файла

Выберите этот параметр, чтобы при каждом открытии файла Excel обновлял данные сводной таблицы.

Чтобы добавить заголовок

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

Чтобы добавить описание

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

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

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

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

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Конструктор в группе Макет нажмите кнопку Макет отчета и выберите один из указанных ниже вариантов.
- Чтобы связанные данные не выходили по горизонтали за пределы экрана (это позволяет меньше пользоваться прокруткой), выберите команду Показать в сжатой форме.
  
  В сжатой форме поля находятся в одном столбце и отображаются с отступами, чтобы показать вложенность столбцов.
- Чтобы отобразить структуру данных в классическом стиле сводной таблицы, выберите команду Показать в форме структуры.
- Для просмотра всех данных в традиционном формате таблицы, в котором можно легко копировать ячейки на другие листы, выберите команду Показать в табличной форме.

Выполните одно или несколько из указанных ниже действий.

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

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

Щелкните имя поля в разделе полей и, удерживая нажатой кнопку мыши, перетащите его в область Значения в разделе макета.
Повторите действие 1 столько раз, сколько нужно копий поля.
В каждом скопированном поле измените нужным образом функцию сведения или настраиваемое вычисление.
Примечания:
- При добавлении в область значений двух или нескольких полей (как копий одного поля, так и разных полей) список полей автоматически добавляет в область Значения название столбца значений. С помощью этого поля можно перемещать поле вверх и вниз в области Значения. Название столбца значений можно даже переместить в область Названия столбцов или Названия строк. Однако переместить название столбца значений в область фильтров отчетов нельзя.
- Поле можно добавить в область Фильтр отчета, Названия строк или Названия столбцов только один раз как для числовых, так и для нечисловых типов данных. При попытке добавить одно поле несколько раз (например, в области Названия строк и Названия столбцов в разделе макета) поле автоматически удаляется из исходной области и перемещается в новую.
- Кроме того, добавить копию поля в область Значения можно с помощью формулы (также называемой вычисляемым столбцом), в которой используется это поле.
- Одно и то же поле невозможно добавить несколько раз в сводную таблицу, созданную на основе источника данных OLAP.

Отчет сводной таблицы	Описание	Сводная диаграмма	Описание
Значения	Используются для вывода итоговых числовых данных.	Значения	Используются для вывода итоговых числовых данных.
Названия строк	Используются для отображения полей в виде строк сбоку от отчета. Строка, расположенная ниже, вложена в строку, непосредственно предшествующую ей.	Поля осей (категории)	Используются для отображения полей в качестве осей диаграммы.
Названия столбцов	Используются для отображения полей в виде столбцов сверху отчета. Столбец, расположенный ниже, вложен в столбец, непосредственно предшествующий ему.	Названия полей легенды (рядов)	Используются для отображения полей в легенде диаграммы.
Фильтр отчета	Используется для фильтрации всего отчета на основе выбранного элемента фильтра отчета.	Фильтр отчета	Используется для фильтрации всего отчета на основе выбранного элемента фильтра отчета.

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

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

Щелкните сводную таблицу.

На ленте откроется вкладка Работа со сводными таблицами.
Если нужно отобразить список полей сводной таблицы, на вкладке Анализ или Параметры в группе Показать нажмите кнопку Список полей. Вы также можете щелкнуть правой кнопкой мыши сводную таблицу и выбрать Показать список полей.
Чтобы удалить поле, в списке полей сводной таблицы выполните одно из следующих действий.
- В списке полей сводной таблицы снимите флажок рядом с именем поля.
  
  Примечание: Снятие флажка в списке полей приводит к удалению из отчета всех экземпляров поля.
- В области макета щелкните имя поля и выберите пункт Удалить поле.
- Щелкните имя поля в разделе макета и, удерживая нажатой кнопку мыши, перетащите его из списка полей сводной таблицы.

Включение и отключение заголовков полей столбцов и строк

Щелкните сводную таблицу.

На ленте откроется вкладка Работа со сводными таблицами.
Чтобы переключаться между режимами отображения и скрытия заголовков полей, на вкладке Анализ или Параметры в группе Показать нажмите кнопку Заголовки полей.

Отображение промежуточных итогов выше или ниже их строк

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

На ленте откроется вкладка Работа со сводными таблицами.

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

Примечание: Если выбрать вариант Нет, промежуточные итоги будут отключены.
На вкладке Разметка и печать в группе Макет выберите вариант В виде структуры и выполните одно из следующих действий.
- Чтобы промежуточные итоги отображались над суммируемыми строками, установите флажок Промежуточные итоги в заголовке группы. Этот вариант выбран по умолчанию.
- Чтобы промежуточные итоги отображались под суммируемыми строками, снимите флажок Промежуточные итоги в заголовке группы.

Изменение порядка элементов строк и столбцов

Выполните любое из следующих действий.

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

Настройка ширины столбцов при обновлении.

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры сводной таблицы на вкладке Макет и формат в группе Формат выполните одно из указанных ниже действий.
- Чтобы столбцы сводной таблицы автоматически изменялись по размеру самого широкого текста или числового значения, установите флажок Автоматически изменять ширину столбцов при обновлении.
- Чтобы сохранить текущую ширину столбца сводной таблицы, снимите флажок Автоматически изменять ширину столбцов при обновлении.

Перемещение столбца в область названий строк или строки в область названий столбцов

Выполните любое из следующих действий.

Щелкните поле строки правой кнопкой мыши, наведите указатель на команду Переместить <имя поля> и выберите пункт Переместить <имя поля> в столбцы.
Щелкните поле столбца правой кнопкой мыши и выберите команду Переместить <имя поля> в строки.
Перетащите поле строки или столбца в другую область. На рисунке ниже показано, как переместить поле столбца в область названий строк.

1. Щелкните поле столбца

2. Перетащите его в область строк

3. Поле «Спорт» станет полем строки, как и «Регион»

Объединение и отмена объединения ячеек для элементов внешних строк и столбцов

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры сводной таблицы откройте вкладку Макет и формат, а затем в группе Макет установите или снимите флажок Объединить и выровнять по центру ячейки с подписями.

Изменение способа отображения ошибок и пустых ячеек

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры сводной таблицы щелкните вкладку Макет и формат, а затем в разделе Формат выполните одно или несколько из следующих действий:
- Чтобы изменить способ отображения ошибок, установите флажок Для ошибок отображать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
- Чтобы изменить способ отображения пустых ячеек, установите флажок Для пустых ячеек отображать и введите в текстовом поле значение, которое будет отображаться в пустых ячейках.
  
  Совет: Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.

Изменение способа отображения элементов и подписей без данных

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.
На вкладке Вывод в группе Вывод выполните одно или несколько из следующих действий.
- Чтобы отображать или скрывать элементы без значений в строках, установите или снимите флажок Показывать элементы без данных в строках.
  
  Примечание: Этот параметр доступен только для источника данных OLAP.
- Чтобы отображать или скрывать элементы без значений в столбцах, установите или снимите флажок Показывать элементы без данных в столбцах.
  
  Примечание: Этот параметр доступен только для источников данных OLAP.

Применение стиля для форматирования сводной таблицы

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Конструктор в группе Стили сводной таблицы выполните любое из следующих действий.
- Щелкните отображаемый стиль сводной таблицы или прокрутите коллекцию, чтобы увидеть другие стили.
- Чтобы посмотреть все доступные стили, нажмите кнопку Дополнительные параметры внизу панели прокрутки.
  
  Если нужно создать собственный стиль сводной таблицы, выберите команду Создать стиль сводной таблицы внизу коллекции, чтобы открыть диалоговое окно Создание стиля сводной таблицы.

Применение чередования для изменения формата сводной таблицы

Щелкните в любом месте сводной таблицы.

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

Удаление стиля или формата чередования из сводной таблицы

Щелкните в любом месте сводной таблицы.

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

Условное форматирование данных в сводной таблице

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

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

Дополнительные сведения см. в статье Применение условного форматирования.

Включение форматирования сервера OLAP

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Данные нажмите кнопку Изменить источник данных и выберите пункт Свойства подключения.
В диалоговом окне Свойства подключения на вкладке Использование в разделе Форматирование сервера OLAP выполните одно из следующих действий.
- Чтобы включить или отключить форматирование чисел, таких как денежные значения, даты и время, установите или снимите флажок Числовой формат.
- Чтобы включить или отключить стили шрифта, такие как полужирный, курсив, подчеркнутый и зачеркнутый, установите или снимите флажок Стиль шрифта.
- Чтобы включить или отключить цвета заливки, установите или снимите флажок Цвет заливки.
- Чтобы включить или отключить цвета текста, установите или снимите флажок Цвет текста.

Сохранение и игнорирование форматирования

Щелкните в любом месте сводной таблицы.

На ленте откроется вкладка Работа со сводными таблицами.
На вкладке Анализ или Параметры в группе Сводная таблица нажмите кнопку Параметры.
На вкладке Макет и формат в группе Формат выполните одно из указанных ниже действий.
- Чтобы сохранить макет и формат сводной таблицы для применения при каждом выполнении операции со сводной таблицей, установите флажок Сохранять форматирование ячеек при обновлении.
- Чтобы игнорировать макет и формат сводной таблицы и возвращаться к стандартному макету и формату при каждом выполнении операции со сводной таблицей, снимите флажок Сохранять форматирование ячеек при обновлении.
Примечание: Хотя этот параметр также влияет на форматирование сводных диаграмм, линии тренда, подписи данных, пределы погрешностей и другие изменения определенных рядов данных не сохраняются.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Источник

Я работаю в Excel 2010

Допустим, у вас есть допустимое поле даты в ваших необработанных данных с такими датами, как 1/1/2015. Затем вы создаете сводную таблицу с полем даты в метках строк.

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

Теперь ваши данные отображаются в месяцах в формате Ммм. Как бы вы поменяли формат Мммм? Или что, если вы хотите отобразить его как число (например, Ян отображается как 1)

Как насчет дат, которые имеют отметки времени и группируются по дням. В сводной таблице дата будет отображаться как Д-Ммм. Что, если я хочу ММ / ДД / ГГГГ?

Да, я уже пробовал изменить его, щелкнув правой кнопкой мыши -> настройки поля -> формат числа. Это не сработало.

9 ответов

Лучший ответ

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

Обходной путь — создать новое поле / столбец в исходном файле данных с желаемым форматом и использовать его в сводной таблице.

ChrisG
23 Май 2018 в 14:00

Я выбрал один из заголовков столбца и нажал кнопку «Разгруппировать». Моя проблема заключалась в том, что в сводной таблице были заголовки mmm, и я хотел, чтобы m / d / yyyy соответствовали моим необработанным данным.

-3

Stu Holden
7 Фев 2017 в 16:11

Я пробовал две вещи:

Я просто как обычно поменял формат ячеек.
Выделено более одного элемента при группировании (с помощью Control + щелчок по окнам или Command + щелчок по Mac) источник

Ps: Я указал, как выбрать более одного элемента, потому что это не очень интуитивно понятно.

-2

jpfreire
30 Июл 2016 в 11:35

Мне удалось решить эту проблему, манипулируя внутренним OOXML (Office Open XML) документа Excel. Если вы не знаете, как открыть документ Excel (для просмотра документов xml, из которых он состоит внутри), обратитесь к одной из этих ссылок:

«Лучший способ» Используйте расширение Visual Studio (редактор пакетов OOXML) для непосредственного открытия / редактирования файла Excel: http://www.ericwhite.com/blog/open-xml-installation-center/
Или используйте «более простое» расширение для Chrome (перетащите на него файл Excel): https://chrome.google.com/webstore/detail/ooxml-tools/bjmmjfdegplhkefakjkccocjanekbapn?hl=en (не забудьте загрузить новый файл после сохранения в нем изменений)

Или «наименее рекомендованный» способ

1.)  Change your filename from "MyExcelFile.xlsx" to "MyExcelFile.zip"
2.)  Unzip the file
3.)  Change the format (as explained below)
4.)  Zip it all back up and change extension to .xlsx
5.)  Warning, This method often corrupts my files, but not always, so I use OOXML Package editor in Visual Studio instead.

Независимо от вашего метода, теперь перейдите к «PivotCache» для вашей сводной таблицы. В моем «распакованном файле excel» путь к файлу: Root> xl> pivotCache> pivotCacheDefinition1.xml

Моя копия содержит около 800 строк xml, и я полагаю, что это может быть намного больше, но сосредоточьтесь только на той части, которую вы хотите переформатировать. (убедитесь, что вы преобразили свой xml, чтобы это не одна длинная сжатая строка, если вы используете Visual Studio Ctrl + K, Ctrl + D)

Найдите атрибут: groupBy = «months»

Мой образец находит

    <cacheField name="Months" numFmtId="0" databaseField="0">
      <fieldGroup base="2">
        <rangePr groupBy="months" startDate="2019-01-02T00:00:00" endDate="2019-12-27T00:00:00"/>
        <groupItems count="14">
          <s v="&lt;1/2/2019"/>
          <s v="Jan"/>
          <s v="Feb"/>
          <s v="Mar"/>
          <s v="Apr"/>
          <s v="May"/>
          <s v="Jun"/>
          <s v="Jul"/>
          <s v="Aug"/>
          <s v="Sep"/>
          <s v="Oct"/>
          <s v="Nov"/>
          <s v="Dec"/>
          <s v="&gt;12/27/2019"/>
        </groupItems>
      </fieldGroup>
    </cacheField>
  </cacheFields>

… И все, что вам нужно сделать, это заменить «Янв» на «1» или любое другое строковое представление для каждого месяца!

Сделать еще один шаг вперед, но все же ответить на вопрос о том, как отформатировать группу по столбцу, — отформатировать groupBy = «days», что приведет к

    <fieldGroup par="5" base="2">
    <rangePr groupBy="days" startDate="2019-01-02T00:00:00" endDate="2019-12-27T00:00:00"/>
    <groupItems count="368">
    <s v="(blank)"/>
    <s v="1-Jan"/>
    <s v="2-Jan"/>
    <s v="3-Jan"/>...

Список можно продолжать и продолжать, если быть точным, то 368 элементов. 365 дней, плюс первый и последний элементы, включая 29 февраля. Если вы пытаетесь отформатировать строку, сгруппированную по дням, это элементы, которые нужно изменить. то есть:

    <s v="1-Jan"/>

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

Так что «1 января» «2 января» может стать

<s v="1月1日"/>
<s v="1月2日"/>...

Вы относитесь к ним точно так же, как я показывал ранее для Месяцев. Дни, однако, утомительнее делать вручную, так как вам нужен элемент на каждый день, поэтому я создал функцию C #, которую вы можете изменить, чтобы быстро выплевывать элементы в файл (для копирования и вставки в ваш .xml)

        public void GenerateExcelGroupedDateFormatPivotTableElements()
        {
            int year = 2019;
            var sb = new StringBuilder();
            for (int i = 1; i <= 12; i++)
            {
                var daysInMonth = DateTime.DaysInMonth(year, i);
                for (int j = 1; j <= daysInMonth; j++)
                {
                    //Modify template below as you wish, (uses String Interpolation $) and (Verbatim @ symbol)
                    
                    //Full Month Name: ie January-day
                    string monthName = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(i);
                    sb.AppendLine($@"<s v=""{monthName}-{j}""/>");

                    //or Chinese sample
                    //sb.AppendLine($@"<s v=""{i}月{j}日""/>");
                }
            }
            File.WriteAllText("_ExcelPivotTableGroupedDateHelper.txt",sb.ToString());
        }

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

Кажется, есть новая опция «Групповое поле» (по какой-то причине помеченная «7». Нажмите на нее, и вы получите варианты переключателя для Месяца / Дня / Часа и т. Д. Выберите свои предпочтения и нажмите OK. Надеюсь, это сработает для вас тоже!

Будем признательны за то, что можно и чего нельзя делать.

Удачного кодирования!

CANDIMAN
23 Июн 2020 в 03:36

Srikrishna Vadrevu
24 Фев 2020 в 01:19

Я просто переформатировал поле даты и времени, которое было источником данных для сводной таблицы (которое было дд / мм / ггг чч: мм: сс) в текст (ddddd.ttttt), а затем, когда я обновил сводную таблицу, оно появилось в формат даты и времени, который я выбрал.

Browny
2 Ноя 2017 в 03:25

Ответ Бариса Сари частично верен. Вам нужно разгруппировать даты, что затем позволит вам установить любой формат даты, который вы хотите.

Выберите все даты в сводной таблице, затем щелкните правой кнопкой мыши и выберите «Разгруппировать». Затем щелкните правой кнопкой мыши еще раз по своему выбору и нажмите «Форматировать ячейки». Первая вкладка в открывшемся окне относится к числам.

Это работало в Excel 2016 и 2010.

Von Pittman
23 Фев 2017 в 14:33

Если вы выберете столбцы, в которых разбита дата (внутри сводной таблицы — годы, квартал и т. Д.), И перейдите к разгруппировке данных. Это приведет вас к формату даты.

nina
5 Июл 2017 в 14:00

У меня была такая же проблема. Я щелкнул правой кнопкой мыши значение, для которого я хочу изменить представление сводной таблицы, и выбрал опцию «Разгруппировать». Это возвращает формат просмотра регулятора. Я надеюсь это тебе поможет.

Baris Sari
5 Дек 2016 в 15:59

Источник

Изменение формата для дат не работает

Дополнительные сведения

9 ответов

А вот еще интересные статьи: