При обновлении сводной таблицы меняется ширина столбцов в excel
Как сохранить форматирование после обновления сводной таблицы?
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Сохранить форматирование после обновления сводной таблицы
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Чтобы сохранить форматирование ячеек при обновлении сводной таблицы, выполните следующие действия:
1. Выберите любую ячейку в сводной таблице и щелкните правой кнопкой мыши.
2. Потом выберите Параметры сводной таблицы из контекстного меню см. снимок экрана:
3. В Параметры сводной таблицы диалоговое окно, нажмите Макет и формат вкладку, а затем проверьте Сохранить форматирование ячеек при обновлении пункт под Формат раздел, см. снимок экрана:
4, Затем нажмите OK чтобы закрыть это диалоговое окно, и теперь, когда вы отформатируете сводную таблицу и обновите ее, форматирование больше не исчезнет.
Статьи по теме:
Лучшие инструменты для работы в офисе
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы . Предотвращение дублирования ячеек; Сравнить диапазоны .
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
- Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
- Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF . Pivot Table Grouping by week number, day of week and more. Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name . -->
- Более 300 мощных функций . Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Я использую Excel 2013, и сохранение форматирования не работает. Электронная таблица, которую я использую, изначально была создана в Excel 2007.
как можно заморозить условное форматирование в сводной таблице во время обновления листа, потому что, когда я обновляю лист, все условные сбросы
Я пробовал это несколько раз, и это не работает для меня. Какую версию ты используешь? Я использую MS Office 2016. Я пытался управлять датами в формате короткой даты. Очень неприятно выбирать столбцы даты и переформатировать каждый раз, когда данные обновляются, чтобы ВРЕМЯ не отображалось.
ОК - я только что нашел решение. Используйте условное форматирование столбца.
Я выбрал все столбцы даты; ЛЕНТА — условное форматирование. Условием является любая дата больше 1; Выбрал формат даты, который хочу использовать; Протестированы изменения сводных данных, обновлено, форматирование сохранено.
Бесполезный путеводитель. Параметр «сохранять форматирование при обновлении» буквально ничего не делает.
Это не работает, когда я пытаюсь сохранить выравнивание текста ячейки. Например, я хочу, чтобы текст был выровнен по левому краю в определенном столбце. После обновления формат сбрасывается.
Примечание для тех, кто предлагает отладить (если какая-то добрая душа достаточно смелая): у меня часто есть несколько полей в разделе столбца.
Он работает, как только вы снимаете галочку, делаете все свое форматирование и снова ставите галочку.
У меня было такое же разочарование, как и у многих из вас, когда форматирование не сохраняется, даже если вы устанавливаете флажок «Сохранить форматирование ячейки при обновлении». Я нашел 2 вещи, которые помогли мне.
- выделите большую часть таблицы и отформатируйте эту более широкую область, чтобы определить форматирование для ячеек, которые не поддерживают желаемое форматирование. Затем, после обновления, вернитесь и измените определенные ячейки, чтобы настроить форматирование.
- Идея Дарлин относительно условного форматирования великолепна и хорошо работает. Единственное, чего вам будет не хватать, так это выравнивания и переноса текста.
- После всего этого сохраните и закройте файл. Через мгновение снова откройте файл и повторите попытку. Я обнаружил, что по какой-то причине закрытие файла и его повторное открытие позволит системе запечатлеть эти изменения.
Вы можете в любой момент нажать кнопку Обновить, чтобы обновить данные в сводных таблицах в книге. Можно обновлять значения в сводных таблицах, подключенных к внешним данным, таким как базы данных (SQL Server, Oracle, Access и другие), куб служб Analysis Services, веб-каналы данных и многие другие источники. Вы также можете обновлять данные из исходной таблицы в той же или другой книге. Кроме того, можно настроить автоматическое обновление данных сводной таблицы при открытии книги.
По умолчанию с помощью этой ссылки вы можете указать, что при ее автоматическом обновлении в книге, которая содержит ее, с помощью стеблицы.
Обновление вручную
Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Анализ выберите команду Обновить или нажмите ALT+F5.
Совет: Чтобы одновременно обновить все стебли в книге, нажмите кнопку Анализ > Обновить все.
Если таблица обновляется слишком долго, на вкладке Анализ нажмите стрелку рядом с кнопкой Обновить и выберите пункт Состояние обновления. Так вы сможете проверить состояние обновления.
Чтобы отменить обновление, нажмите Отменить обновление.
Блокировка изменения ширины столбцов и форматирования ячеек
Если при обновлении сводной таблицы изменяется ширина столбцов и форматирование данных, но вы не хотите, чтобы это происходило, установите вот эти флажки.
На вкладке Анализ нажмите кнопку Параметры.
На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.
Автоматическое обновление данных при открытии книги
Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Анализ нажмите кнопку Параметры.
На вкладке Данные проверьте поле Обновлять данные при открытии файла.
Обновление вручную
Щелкните в любом месте сводной таблицы.
Отобразит вкладку "Параметры" и "Конструктор" в области "Инструменты для работы со таблицами".
На вкладке Параметры в группе Данные сделайте следующее:
Чтобы обновить данные в соответствие с источником данных, нажмите кнопку Обновить или нажмите клавиши ALT+F5.
Вы также можете щелкнуть правой кнопкой мыши и нажать кнопку Обновить.
Чтобы обновить все стебли в книге, нажмите кнопку Обновить и выберите обновить все.
Если обновление занимает больше времени, чем вы ожидаете, щелкните Параметры > обновить > Состояние обновления, чтобы проверить состояние обновления.
Чтобы отменить обновление, нажмите Отменить обновление.
Блокировка изменения ширины столбцов и форматирования ячеек
Если при обновлении сводной таблицы изменяется ширина столбцов и форматирование данных, но вы не хотите, чтобы это происходило, установите вот эти флажки.
Щелкните Параметры > параметры.
На вкладке Макет и формат установите флажки Автоматически изменять ширину столбцов при обновлении и Сохранять форматирование ячеек при обновлении.
Автоматическое обновление данных в pivottable при открытии книги
Щелкните в любом месте сводной таблицы.
На вкладке Параметры в группе Сводная таблица нажмите кнопку Параметры.
В диалоговом окне Параметры таблицы на вкладке Данные выберите параметр Обновлять данные при открытии файла.
При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Итог: узнайте, как предотвратить или отключить изменение размеров столбцов в сводной таблице при обновлении, изменении или фильтрации.
Уровень мастерства: Начинающий
Обычно, когда мы вносим какие-либо изменения или обновления в сводную таблицу, ширина столбцов автоматически изменяется, чтобы подогнать содержимое каждой ячейки в сводной таблице.
«Обновление» включает в себя практически каждое действие, которое мы выполняем в сводной таблице, включая: добавление/удаление полей, обновление, фильтрацию с помощью выпадающего меню или среза, изменения макета и т.д. Функция автоподбора изменяет размер столбца до ширины самой широкой ячейки (с наибольшим содержанием) в каждом столбце.
Это раздражает! Особенно, когда лист содержит данные в других ячейках за пределами сводной таблицы или любых фигур (диаграммы, срезы, фигуры и т.д.).
Отключить автоподбор ширины столбца при обновлении
К счастью, есть быстрое решение. В сводной таблице есть настройка, которая позволяет нам включать/выключать эту функцию.
Вот шаги, чтобы отключить автозаполнение по ширине столбца при обновлении:
Размер столбцов НЕ будет автоматически изменяться при внесении изменений в сводную таблицу.
Я писал об этом в своем посте о том, как создать окно поиска для слайсера.
Ярлык для автоматической подгонки ширины столбцов вручную
Иногда бывают случаи, когда необходимо изменить размер столбцов после изменения сводной таблице. Если функция автоподбора по ширине отключена, сделать это довольно быстро помогут несколько сочетаний клавиш.
Убедитесь, что в сводной таблице выбрана ячейка, а затем нажмите следующие клавиши.
Эта комбинация клавиш изменяет размеры столбцов только для содержимого ячеек сводной таблицы.
Если вы хотите включить содержимое ячейки за пределы сводной таблицы, нажмите 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
Я надеюсь, что это поможет сэкономить время, и изменение размера столбцов сводной таблицы не будет приносить разочарование.
Пожалуйста, оставьте комментарий ниже с любыми вопросами или другими советами, которые у вас есть по этому вопросу. Спасибо!
Итог: узнаете, как изменить форматирование даты для сгруппированного поля в сводной таблице.
Уровень мастерства: Средний
Изменение формата для дат не работает
Когда мы группируем поле «Дата» в сводной таблице с помощью функции «Группировать», форматирование чисел для поля «День» фиксируется. Он имеет следующий формат «день-месяц» или «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)
Макрос форматирования поля «Дни»
Как работает макрос
Макрос сначала зацикливает элементы сводки в поле Дни, чтобы восстановить имя элемента сводки в его исходное имя по умолчанию. Два разных элемента не могут иметь одинаковое имя. Так что это должно предотвратить любые ошибки при изменении или попытке разных форматов.
Второй цикл меняет каждый элемент сводки на новый формат. Он использует функцию DateValue для изменения названия элемента сводки «1-Jan» на дату. Затем он использует функцию «Формат», чтобы изменить форматирование даты на текст. По умолчанию используется формат «m / d». Это может быть изменено на другой формат с месяцем и днем. Каждый элемент должен быть уникальным, поэтому вы можете использовать месяц и день в названии элемента.
Возможно, вы могли бы разделить этот макрос на два макроса и запускать только первый цикл сброса по мере необходимости. Макрос занимает около 15 секунд для запуска на моем компьютере из-за всех циклов. Но это не тот, который вам придется часто бегать.
Также важно упомянуть, что это выполняется на элементах сводки, а не на сводном кеше. Таким образом, только что названные элементы будут изменены только в сводной таблице, на которой вы запускаете макрос.
Макрос форматирования сгруппированных элементов
Был задан большой вопрос в комментариях об изменении форматирования для сгруппированных чисел. Это та же проблема, что и для групп дат. Имена групп — это текст, а не цифры. Тем не менее, мы можем использовать макрос, чтобы изменить их.
Вот код макроса. Вам просто нужно изменить значение константы sGroupField вверху на имя вашего сгруппированного поля. При необходимости вы также можете изменить формат чисел в sNumberFormat.
Я надеюсь, что информация была полезной. Я бы предложил использовать решение № 1, если вы не хотите использовать функцию группы.
Пожалуйста, оставьте комментарий ниже с любыми вопросами или предложениями о том, как мы можем улучшить это. Спасибо! 🙂
Как заблокировать ширину столбца в сводной таблице?
Когда вы создаете сводную таблицу и меняете поле снова и снова, заметили ли вы, что ширина столбца изменяется одновременно? Но если вы хотите установить статическую ширину столбца в сводной таблице, есть ли у вас идея заблокировать ширину столбца сводной таблицы в Excel?
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Заблокировать ширину столбца в сводной таблице
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
После создания сводной таблицы вам легко заблокировать ширину столбца сводной таблицы, сделайте следующее:
1. Щелкните ячейку в сводной таблице.
2. Затем щелкните правой кнопкой мыши и выберите Параметры сводной таблицы из контекстного меню см. снимок экрана:
3. В Параметры сводной таблицы диалога под Макет и формат вкладка, снимите флажок Автоматически подогнать ширину столбцов при обновлении вариант, см. снимок экрана:
4, Затем нажмите OK, отныне ширина столбца не будет изменяться при выполнении ваших операций.
Читайте также: