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

Обновлено: 04.07.2024

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

Постановка задачи

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

Именованный диапазон данных в комбинации с функцией СМЕЩ

Функция СМЕЩ возвращает ссылку на диапазон заданного размера (высота и ширина), отстоящий от стартовой ячейки на заданное число строк и столбцов.

=СМЕЩ (ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина])

Аргументы функции:

Ссылка (обязательный аргумент) — ссылка, от которой вычисляется смещение, аргумент должен быть ссылкой на ячейку или на диапазон смежных ячеек, в противном случае функция возвращает значение ошибки ЗНАЧ! ;
Смещ_по_строкам (обязательный аргумент) — количество строк, которые требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку;
Смещ_по_столбцам (обязательный аргумент) — количество столбцов, которые требуется отсчитать влево или вправо, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку;
Высота (не обязательный аргумент) — высота (число строк) возвращаемой ссылки, значение должно быть положительным числом.
Ширина (не обязательный аргумент) — ширина (число столбцов) возвращаемой ссылки, значение должно быть положительным числом.

Теперь, посмотрим, как происходит захват диапазона:

Из ячейки C2 опускаемся ниже на две строки (первый аргумент). Затем, двигаемся вправо по столбцам на четыре ячейки до столбца G4 (второй аргумент). Диапазон захвата таблицы с данными 3х3 , последние два аргумента. Справа построена Сводная таблица, на основе захваченного диапазона.

Перейдем к решению исходной задачи. Создадим именованный диапазон, Формулы ► Диспетчер имен ► Создать :

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

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

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

Установка заданной ширины столбца

Выберите столбец или столбцы, ширину которых требуется изменить.

На вкладке Главная в группе Ячейки нажмите кнопку Формат.

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

Введите значение в поле Ширина столбцов.

Совет: Чтобы быстро установить ширину одного столбца, щелкните его правой кнопкой мыши, выберите "Ширина столбца", введите нужное значение и нажмите кнопку "ОК".

Автоматическое изменение ширины столбца в соответствии с содержимым ячеек (автоподбор)

Выберите столбец или столбцы, ширину которых требуется изменить.

На вкладке Главная в группе Ячейки нажмите кнопку Формат.

В разделе Размер ячейки выберите пункт Автоподбор ширины столбца.

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

Изменение ширины столбца в соответствии с шириной другого столбца

Выделите ячейку, которая имеет нужную ширину.

Нажмите сочетание клавиш CTRL+C или на вкладке Главная в группе Буфер обмена нажмите кнопку Копировать.

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

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

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

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

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

На вкладке Главная в группе Ячейки нажмите кнопку Формат.

В разделе Размер ячейки выберите пункт Ширина по умолчанию.

В поле Ширина столбца введите новое значение, а затем нажмите кнопку ОК.

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

Вы можете в любой момент нажать кнопку Обновить, чтобы обновить данные в сводных таблицах в книге. Можно обновлять значения в сводных таблицах, подключенных к внешним данным, таким как базы данных (SQL Server, Oracle, Access и другие), куб служб Analysis Services, веб-каналы данных и многие другие источники. Вы также можете обновлять данные из исходной таблицы в той же или другой книге. Кроме того, можно настроить автоматическое обновление данных сводной таблицы при открытии книги.

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

Обновление вручную

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

На вкладке Анализ выберите команду Обновить или нажмите ALT+F5.

Совет: Чтобы одновременно обновить все стебли в книге, нажмите кнопку Анализ > Обновить все.

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

Чтобы отменить обновление, нажмите Отменить обновление.

Блокировка изменения ширины столбцов и форматирования ячеек

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

На вкладке Анализ нажмите кнопку Параметры.

На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.

Автоматическое обновление данных при открытии книги

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

На вкладке Анализ нажмите кнопку Параметры.

На вкладке Данные проверьте поле Обновлять данные при открытии файла.

Обновление вручную

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

Отобразит вкладку "Параметры" и "Конструктор" в области "Инструменты для работы со таблицами".

На вкладке Параметры в группе Данные сделайте следующее:

Чтобы обновить данные в соответствие с источником данных, нажмите кнопку Обновить или нажмите клавиши ALT+F5.

Вы также можете щелкнуть правой кнопкой мыши и нажать кнопку Обновить.

Чтобы обновить все стебли в книге, нажмите кнопку Обновить и выберите обновить все.

Если обновление занимает больше времени, чем вы ожидаете, щелкните Параметры > обновить > Состояние обновления, чтобы проверить состояние обновления.

Чтобы отменить обновление, нажмите Отменить обновление.

Блокировка изменения ширины столбцов и форматирования ячеек

Щелкните Параметры > параметры.

Автоматическое обновление данных в pivottable при открытии книги

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

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

В диалоговом окне Параметры таблицы на вкладке Данные выберите параметр Обновлять данные при открытии файла.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В последней версии 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

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

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

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

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

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

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

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

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

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

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

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

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

Использование собственных полей из исходных данных для разных групп дат даст вам контроль над форматированием чисел поля в сводной таблице. Вы также можете создать таблицу календаря с группировками, если вы используете 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)

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

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

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

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

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

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

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

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

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

Читайте также: