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

Обновлено: 04.07.2024

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

Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Включить отображение сведений в сводной таблице

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

1. Щелкните правой кнопкой мыши любую ячейку в сводной таблице и выберите Параметры сводной таблицы из контекстного меню. Смотрите скриншот:

2. В появившемся диалоговом окне щелкните Данные вкладка, а затем проверьте Включить показывать детали вариант. Смотрите скриншот:

3. Щелкните OK, чтобы закрыть диалоговое окно. А затем перейдите в ячейку в правом углу Общий итог строка в сводной таблице. Смотрите скриншот:

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

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

Данные меняются периодически, сводная тоже. При нажатии на "Обновить все" сводная обновляется а с ней и какой-то бардак с границами возвращается. Убираю границы обратно. Снова обновляю и сновая появляются левые границы.

Из-за чего такое может быть и как побороть?

Из-за Excel 2007. Переходите на нормальные версии - 2003, 2010, 2013 Из-за Excel 2007. Переходите на нормальные версии - 2003, 2010, 2013 Serge_007 Из-за Excel 2007. Переходите на нормальные версии - 2003, 2010, 2013 Автор - Serge_007
Дата добавления - 05.11.2013 в 14:16 Serge_007, это исключительно особенность 2007й версии? какой посоветуете, поновее или постарее? Serge_007, это исключительно особенность 2007й версии? какой посоветуете, поновее или постарее? reborn Мне в этой фразе больше всего понравилось, что Серж таки-считает 2003 нормальным Офисом.[/offtop] Мне в этой фразе больше всего понравилось, что Серж таки-считает 2003 нормальным Офисом.[/offtop] Alex_ST Мне в этой фразе больше всего понравилось, что Серж таки-считает 2003 нормальным Офисом.[/offtop] Автор - Alex_ST
Дата добавления - 05.11.2013 в 14:54

Поставил 2010 не победил проблему. Снял поставил галочку в сводной таблице "Сохранять форматирование ячеек при обновлении"
Проблема решилась. Но! осталась еще одна проблема. Меняю данные которые обрабатывает сводная таблица, обновляю и сбрасываются настройки полей сводной таблицы.

Если нужно приложу файл

Если нужно приложу файл reborn

Если нужно приложу файл Автор - reborn
Дата добавления - 05.11.2013 в 21:14

Какие настройки? Автор - Serge_007
Дата добавления - 05.11.2013 в 22:43

вот у меня настроенная таблица на отображение тех полей что нужно и тех значений что нужно.

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

вот у меня настроенная таблица на отображение тех полей что нужно и тех значений что нужно.

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

при изменении значений некоторых по которым создается своданая таблица и обновлении документа получается то что я соскринил из поста выше Автор - reborn
Дата добавления - 06.11.2013 в 00:14

кажется понял в чем затык

меняю данные, которые агрегирует сводная1
по этим данным расчитывается значения блока2
и по расчитанным значениям строится сводная3

если я меняю данные для сводной1 то при обновлении документа обновляются данные сразу и сводной1 и сводной3, но сводная3 получается строится по неправильным данным
то есть по логике как-то надо чтоб сначала обновялась своданя1, потом блок2 и только уже после этого сводная3
не представляю как так сделать, может есть возможность как-то оптимизировать схему?

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

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

например я бы с удовольствием запихал расчетный блок в сводную1, только не представляю как Автор - reborn
Дата добавления - 06.11.2013 в 00:43

У меня есть 4 сводные диаграммы, которые основаны на данных, которые обновляются из соединения.

Когда я нажимаю обновить все, я теряю все настройки, которые я установил (цвета / границы / линия и полоса и выбор 2-й оси)

Я уже снял галочку Properties Follow Chart Data Point for Current Workbook .
Я также попытался щелкнуть правой кнопкой мыши Данные> Обновить для каждой таблицы данных, но у меня возникла та же проблема.
Preserve cell formatting on update отмечен для всех графиков.
Invert if negative option помечено / не помечено не имеет значения
Preserve cell formatting on update Я попытался убрать галочку, затем все в порядке, затем щелкнуть правой кнопкой мыши и снова поставить галочку, все еще не работает ..
Я сохранил формат диаграммы в качестве шаблона, а затем после обновления применяется, но форматирование все еще теряется.

@harrymc Спасибо за ваш вклад, к сожалению, это только выиграет от отсутствия сводных таблиц и сводной диаграммы из данных и только диаграммы, но это не проблема. Matt 3 года назад 0 @harrymc Позвольте мне попробовать это сейчас Matt 3 года назад 0 Тик и убрать галочку не сработало Matt 3 года назад 0

2 ответа на вопрос

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

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

Затем выберите « Параметры сводной таблицы» в контекстном меню.

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

Отредактировано 1:

Вы можете попробовать это:

Инвертировать, если отрицательная опция должна быть проверена для опций сводной диаграммы.

Или вы можете написать этот код VBA в Immediate Window.

Примечание: лист, диаграмма и номер серии доступны для редактирования.

Отредактировано 2

Выберите область печати, щелкните правой кнопкой мыши и выберите команду « Сохранить как шаблон».

Всякий раз, когда вы теряете формат диаграммы, доходите до Excel, выберите файл Выберите график.

Щелкните правой кнопкой мыши и выберите « Изменить тип диаграммы» .

Выберите шаблон из всплывающего меню типа диаграммы.

Вы найдете все эти потерянные форматы на выбранной диаграмме, примененной ранее.

Вышеуказанный процесс может быть реализован через VBA (Macro) на графике или на всех графиках.

Это сводная диаграмма, а не сводная таблица, тем не менее, это отмечено на всем. Matt 3 года назад 0 @Matt ,, ** Сохранять форматирование при обновлении ** Настройка несколько решает проблему, так как это то, что я вижу и в моих тестах. Rajesh S 3 года назад 0 @Matt ,, ** инвертировать, если отрицательный ** параметр должен быть проверен в сводной диаграмме. Или напишите этот код VBA в ** Немедленное окно **. * Worksheets ("Sheet1"). ChartObjects ("Chart 1"). Chart.SeriesCollection (1) .InvertIfNegative = True * Rajesh S 3 года назад 0 @Matt, проверь пост, я уже отредактировал ответ, пока будет работать !! Rajesh S 3 года назад 0 Инвертировать, если отрицательный параметр не работает Matt 3 года назад 0 @ Matt, это было возможное и проверенное решение, сохраняющее формат диаграммы, иначе, я думаю, я не смогу найти решение для него !! ** Лучше, если вы попробуете с VBA Code ** Rajesh S 3 года назад 0 @ Матт, я могу предложить вам два варианта. ** 1-й - сохранить диаграмму как ШАБЛОН, а затем - после потери формата. Откройте файл, выберите диаграмму, щелкните правой кнопкой мыши и выберите команду Изменить тип диаграммы и в меню выберите ШАБЛОН. ** Rajesh S 3 года назад 0 ** Cont ,, ** 2-й для вышеописанного метода, я могу предложить вам VBA (Макро) . подтвердите, какой из них работает для вас! Rajesh S 3 года назад 0 Я сохранил формат диаграммы в качестве шаблона, а затем после обновления применяется, но форматирование все еще теряется. Matt 3 года назад 0 @Matt ,, пожалуйста, следуйте инструкциям из ** EDITED 2 **, правильно он будет нажимать, я уже проверил. Rajesh S 2 года назад 0

Это объясняет, что Excel фактически сохраняет данные форматирования в кеше со всеми другими свойствами диаграммы. Это означает, что он запоминает точное форматирование. Когда данные обновляются, Excel лишает законной силы этот кэш, так что применяется форматирование по умолчанию для диаграммы.

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

Это делается в два этапа.

Шаг 1

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

Шаг 2

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

Заключение

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

Автор также рекомендует статью Dynamic Chart с использованием Pivot Table и VBA с подходом использования VBA для создания динамических диаграмм с использованием более продвинутого подхода (слишком длинный, чтобы включать его здесь).

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