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

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

Уровень мастерства: Начинающий

How to Prevent Columns from Resizing in Pivot Table

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

«Обновление» включает в себя практически каждое действие, которое мы выполняем в сводной таблице, включая: добавление/удаление полей, обновление, фильтрацию с помощью выпадающего меню или среза, изменения макета и т.д. Функция автоподбора изменяет размер столбца до ширины самой широкой ячейки (с наибольшим содержанием) в каждом столбце.

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

Отключить автоподбор ширины столбца при обновлении

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

Вот шаги, чтобы отключить автозаполнение по ширине столбца при обновлении:

  1. Щелкните правой кнопкой мыши ячейку внутри сводной таблицы.
  2. Выберите «Параметры сводной таблицы…» в меню.
  3. На вкладке «Макет и формат» снимите флажок «Автоподбор по ширине столбца при обновлении».
  4. Нажмите ОК
Excel Pivot Table Options Right-Click Menu
Turn off Autofit column widths on update on 2nd pivot table

Размер столбцов НЕ будет автоматически изменяться при внесении изменений в сводную таблицу.

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

Ярлык для автоматической подгонки ширины столбцов вручную

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

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

  1. Ctrl + A, чтобы выбрать диапазон сводной таблицы.
  2. Alt, h, o, i для ширины столбца.
AutoFit Column Widths Pivot Table Keyboard Shortcut

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

Если вы хотите включить содержимое ячейки за пределы сводной таблицы, нажмите Ctrl + Пробел после Ctrl + A. Ctrl + Space — это сочетание клавиш для выбора всего столбца.

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

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

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

  1. Перейдите в Файл > Параметры.
  2. Выберите меню данных на левой боковой панели.
  3. Нажмите кнопку Изменить макет по умолчанию.
  4. Нажмите кнопку Параметры сводной таблицы….
  5. Снимите флажок Ширина столбца автозаполнения при обновлении.
  6. Нажмите OK 3 раза, чтобы сохранить и закрыть меню параметров Excel.
Excel Options Pivot Table Layout Excel 2016
Pivot Table Options - Edit Default Layout in Excel

Настройки по умолчанию будут применяться ко всем НОВЫМ сводным таблицам, которые вы создаете. Я сделаю следующий пост, где более подробно объясню эту новую функцию настроек по умолчанию. Опять же, он доступен только в последней версии Excel 2016 (текущий канал Office 365).

Если вы подписаны на Office 365 ProPlus, то, возможно, вы находитесь на Отложенном канале, в котором еще нет этого обновления. Вот статья о том, как переключить текущий канал.

Макрос для отключения автозаполнения столбцов на всех сводных таблицах

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

Вот макрос VBA, который отключает настройку ширины столбца Autofit во всех сводных таблицах в книге. Макрос проходит по всем рабочим листам в рабочей книге и всем сводным таблицам на каждом рабочем листе, чтобы отключить настройку. Вы также можете использовать его, чтобы снова включить параметр, изменив свойство HasAutoFormat на True.

Sub Autofit_Column_Width_All_Pivots()
' Отключить автоподбор ширины столбца при обновлении
' на всех сводных таблицах в активной рабочей книге.

Dim ws As Worksheet
Dim pt As PivotTable

  ' Цикл каждого листа в активной рабочей тетради
  For Each ws In ActiveWorkbook.Worksheets
  
    ' Проходить по каждой сводной таблице на листе
    For Each pt In ws.PivotTables
    
      ' Автоподбор ширины столбца при обновлении
      ' изменить на True, чтобы включить
      pt.HasAutoFormat = False
    
    Next pt
  Next ws

End Sub

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

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

Также ознакомьтесь с моей статьей For Loop для подробного объяснения того, как эти типы циклов работают в VBA.

Обратите внимание, что макросы будут работать во всех версиях Excel.

Макрос для вывода списка столбцов автозаполнения для всех сводных таблиц. Вот макрос, который выведет текущее значение параметра ширины столбца «Автозаполнение» для всех сводных таблиц в книге. Строка Debug.Print выводит результаты в окно Immediate в редакторе VB.

Sub List_Pivot_Autofit_Setting()
' Создать список текущей ширины столбца автозаполнения 
' настройка для каждой сводной таблицы в активной книге. 
' Список распечатывается в непосредственном окне (Ctrl + G)

Dim ws As Worksheet
Dim pt As PivotTable

    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
            Debug.Print pt.HasAutoFormat & " | " & ws.Name & " | " & pt.Name  
        Next pt
    Next ws

End Sub

Структура вывода:

Значение HasAutoFormat | Имя рабочего листа | Имя сводной таблицы

Значение HasAutoFormat будет True, если настройка включена, и False, если настройка выключена.

Output of the macro to list pivot table autofit setting

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

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

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

Оцените статью
Добавить комментарий