Как предотвратить изменение размеров столбцов
Итог: узнайте, как предотвратить или отключить изменение размеров столбцов в сводной таблице при обновлении, изменении или фильтрации.
Обычно, когда мы вносим какие-либо изменения или обновления в сводную таблицу, ширина столбцов автоматически изменяется, чтобы подогнать содержимое каждой ячейки в сводной таблице.
«Обновление» включает в себя практически каждое действие, которое мы выполняем в сводной таблице, включая: добавление/удаление полей, обновление, фильтрацию с помощью выпадающего меню или среза, изменения макета и т.д. Функция автоподбора изменяет размер столбца до ширины самой широкой ячейки (с наибольшим содержанием) в каждом столбце.
Это раздражает! Особенно, когда лист содержит данные в других ячейках за пределами сводной таблицы или любых фигур (диаграммы, срезы, фигуры и т.д.).
Отключить автоподбор ширины столбца при обновлении
К счастью, есть быстрое решение. В сводной таблице есть настройка, которая позволяет нам включать/выключать эту функцию.
Вот шаги, чтобы отключить автозаполнение по ширине столбца при обновлении:
Размер столбцов НЕ будет автоматически изменяться при внесении изменений в сводную таблицу.
Я писал об этом в своем посте о том, как создать окно поиска для слайсера.
Ярлык для автоматической подгонки ширины столбцов вручную
Иногда бывают случаи, когда необходимо изменить размер столбцов после изменения сводной таблице. Если функция автоподбора по ширине отключена, сделать это довольно быстро помогут несколько сочетаний клавиш.
Убедитесь, что в сводной таблице выбрана ячейка, а затем нажмите следующие клавиши.
Эта комбинация клавиш изменяет размеры столбцов только для содержимого ячеек сводной таблицы.
Если вы хотите включить содержимое ячейки за пределы сводной таблицы, нажмите Ctrl + Пробел после Ctrl + A. Ctrl + Space — это сочетание клавиш для выбора всего столбца.
Изменить настройки сводной таблицы по умолчанию
В последней версии Excel 2016 можно изменить настройки по умолчанию для большинства параметров сводной таблицы. Это означает, что мы можем отключить ширину столбца Autofit при обновлении для всех новых сводных таблиц, которые мы создаем. Это избавит нас от необходимости вручную изменять этот параметр при каждом создании сводной таблицы в будущем.
Вот шаги, чтобы изменить настройки сводной таблицы по умолчанию. Это относится только к Excel 2016 (Office 365).
Настройки по умолчанию будут применяться ко всем НОВЫМ сводным таблицам, которые вы создаете. Я сделаю следующий пост, где более подробно объясню эту новую функцию настроек по умолчанию. Опять же, он доступен только в последней версии Excel 2016 (текущий канал Office 365).
Если вы подписаны на Office 365 ProPlus, то, возможно, вы находитесь на Отложенном канале, в котором еще нет этого обновления. Вот статья о том, как переключить текущий канал.
Макрос для отключения автозаполнения столбцов на всех сводных таблицах
Если в вашей книге уже много сводных таблиц, и вы хотите отключить автозаполнение для всех сводных таблиц, тогда мы можем использовать макрос.
Вот макрос VBA, который отключает настройку ширины столбца Autofit во всех сводных таблицах в книге. Макрос проходит по всем рабочим листам в рабочей книге и всем сводным таблицам на каждом рабочем листе, чтобы отключить настройку. Вы также можете использовать его, чтобы снова включить параметр, изменив свойство HasAutoFormat на True.
Макрос можно скопировать и вставить в модуль кода в вашей личной книге макросов и использовать в любой открытой книге.
Ознакомьтесь с моей бесплатной серией видео в Персональной книге макросов, чтобы узнать больше.
Также ознакомьтесь с моей статьей For Loop для подробного объяснения того, как эти типы циклов работают в VBA.
Обратите внимание, что макросы будут работать во всех версиях Excel.
Макрос для вывода списка столбцов автозаполнения для всех сводных таблиц. Вот макрос, который выведет текущее значение параметра ширины столбца «Автозаполнение» для всех сводных таблиц в книге. Строка Debug.Print выводит результаты в окно Immediate в редакторе VB.
Значение HasAutoFormat | Имя рабочего листа | Имя сводной таблицы
Значение HasAutoFormat будет True, если настройка включена, и False, если настройка выключена.
Дополнительные материалы по сводным таблицам и макросам
- Как работают сводные таблицы?
- Введение в сводные таблицы и панели инструментов [Видео 1 из 3]
- Как добавить окно поиска в слайсер для быстрой фильтрации сводных таблиц и диаграмм + видео
- Бесплатные видеоматериалы о начале работы с макросами и VBA
Я надеюсь, что это поможет сэкономить время, и изменение размера столбцов сводной таблицы не будет приносить разочарование.
Пожалуйста, оставьте комментарий ниже с любыми вопросами или другими советами, которые у вас есть по этому вопросу. Спасибо!
[expert_bq id=»1570″]Теперь вы сможете регулировать изменение величины ячеек при помощи тех вариантов, которые указаны выше, оперируя выбранной единицей измерения. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Рассмотренные выше шаги позволяют сделать, чтоб Эксель не округлял числа. Но бывают ситуации, когда выполнение такого шага является обязательным для пользователя. В таком случае нужно снова включить опцию одним из доступных способов. Ниже рассмотрим наиболее востребованные варианты.
Как изменить размер ячеек в Excel
- Как работают сводные таблицы?
- Введение в сводные таблицы и панели инструментов [Видео 1 из 3]
- Как добавить окно поиска в слайсер для быстрой фильтрации сводных таблиц и диаграмм + видео
- Бесплатные видеоматериалы о начале работы с макросами и VBA
Пробовали вводить в ячейку Excel большие числа? Например серийный номер номер запчасти (2927930504052004) или номер пластиковой карты (0000111100002222) — получается некрасиво как на картинке Е+ и так далее. Почему Excel округляет большие числа? Как с этим бороться и пара других хитростей в статье.
