Как растянуть формулу в сводной таблице Excel на весь столбец

Обновлено: 21.11.2024

Эта страница представляет собой поддерживаемый рекламодателями отрывок из книги Power Excel 2010–2013 от MrExcel — 567 разгаданных тайн Excel. Если вам нравится эта тема, подумайте о покупке всей электронной книги.

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

Стратегия. Это началось в Excel 2002. Это очень раздражает. Я называю это ошибкой GetPivotData.

Вот как это происходит. На рисунке ниже вы уже сгруппировали ежедневные даты по месяцам и годам. Обычно вы можете добавить вычисляемый элемент для расчета темпов роста как (2015/2014)-1, но вычисляемые элементы не разрешены в сгруппированных сводных таблицах. Итак, вы перешли к ячейке D5, ввели знак равенства, щелкнули C5, ввели косую черту, щелкнули B5 и нажали клавишу ввода. 14,54% — правильный темп роста. Итак, вы затем скопировали формулу вниз. Каким-то образом темп роста для каждого месяца одинаков.

    Продажи в марте 2015 года снизились по сравнению с мартом 2014 года, рост на 14,54 % не соответствует действительности.

Выберите D5 и посмотрите в строке формул. Формула =GETPIVOTDATA("Доход", 3$A$,"Дата" ,1,"Годы", 2015)/GETPIVOTDATA("Доход",3$A$,"Дата" ,1, «Годы», 2014)-1. Не может быть, чтобы вы напечатали что-либо из этого. Вы просто использовали мышь при построении формулы.

Это также происходит, если вы используете клавиши со стрелками. Знак равенства, Влево, Косая черта, Влево, Влево, Минус, Один, Ввод обычно создают формулу =C5/B5-1, но в этом случае вы получаете формулу с двумя формулами GETPIVOTDATA.

Что такое GETPIVOTDATA и как они попали на ваш лист? Команда Excel надеется, что вы увидите GETPIVOTDATA, а затем узнаете, что это такое, и научитесь любить его и использовать его все время. Но так никогда не бывает. Вместо этого людей это раздражает.

Кроме того, я восемь лет ненавидел GETPIVOTDATA, но теперь я понимаю его и иногда даже использую. См. «Могу ли я сохранить форматирование в шаблоне» для примера того, когда вы захотите его использовать.

Большой вопрос заключается в том, как ввести формулу, не получая GETPIVOTDATA. Одним из быстрых и простых способов является ввод формулы без использования мыши или клавиш со стрелками. Просто введите =C5/B5-1. Это создает формулу, которая будет копироваться.

Другой способ – навсегда отключить функцию генерации GETPIVOTDATA. Для этого выберите «Файл», «Параметры», «Формулы». Существует флажок «Использовать функции GetPivotData для ссылок на сводные таблицы». Отключите это.

Дополнительные сведения: другая распространенная проблема с формулами за пределами сводных таблиц заключается в том, что они плохо справляются с изменяющимся размером сводных таблиц. В первом квартале найдено шесть кодов причин, поэтому формула % от общей суммы указывает на G$11.

Это больше не работает в Q2, когда найдено только три кода причины.

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

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

Уровень навыков: начальный

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

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

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

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

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

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

  1. Щелкните правой кнопкой мыши ячейку внутри сводной таблицы.
  2. Выберите в меню "Параметры сводной таблицы...".
  3. На вкладке "Макет и формат" снимите флажок "Автоподбор ширины столбца при обновлении".
  4. Нажмите "ОК".

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

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

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

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

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

  1. Ctrl+A, чтобы выбрать диапазон тела сводной таблицы.
  2. Alt,h,o,i для автоматического подбора ширины столбца.

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

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

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

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

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

  1. Выберите «Файл» > «Параметры».
  2. Выберите меню «Данные» на левой боковой панели.
  3. Нажмите кнопку "Изменить макет по умолчанию".
  4. Нажмите кнопку "Параметры сводной таблицы...".
  5. Снимите флажок Автоподбор ширины столбца при обновлении настроек.
  6. Нажмите OK 3 раза, чтобы сохранить и закрыть меню параметров Excel.

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

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

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

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

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

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

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

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

Макрос для отображения списка параметров автоподбора столбцов для всех сводных таблиц

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

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

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

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

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

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

Можно ли построить сводную таблицу с текстом в области значений? У Сьюзен из Мельбурна, Флорида, есть текстовое поле, и она хочет увидеть текст до и после этого текста.

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

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

Однако, если вы используете модель данных, вы можете написать новое вычисляемое поле на языке DAX, которое будет отображать текст в качестве результата.

    Убедитесь, что ваши данные отформатированы как таблица, выбрав одну ячейку в данных и нажав Ctrl + T . Запишите имя таблицы, как показано на вкладке «Работа с таблицами» на ленте.

Вставка, сводная таблица. Выберите «Добавить эти данные в модель данных» при создании сводной таблицы.

Перетащите поля в строки и столбцы сводной таблицы.

Чтобы добавить текст в область значений, необходимо создать новый специальный тип вычисляемого поля, называемый мерой. Найдите имя таблицы в верхней части списка полей сводной таблицы. Щелкните правой кнопкой мыши имя таблицы и выберите "Добавить меру".

Если у вас нет этого параметра, значит, вы не выбрали «Добавить эти данные в модель данных» на шаге 2.

Нажмите Проверить формулу DAX, чтобы убедиться в отсутствии опечаток

Нажмите "ОК". Новая мера появится в списке полей.

При перетаскивании ListOfCodes в область значений вы увидите список кодов для каждой ячейки в области значений.

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

Удивительно, но когда вы переупорядочиваете поля в строках и столбцах, CONCATENATEX обновляется.

После изменения полей в строках и столбцах

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

Посмотреть видео

Загрузить файл Excel

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

Excel Мысль дня

Я попросил совета у своих друзей-мастеров Excel по поводу Excel. Сегодняшняя мысль для размышления:

На этой неделе, работая над клиентским проектом Excel, я столкнулся с некоторыми проблемами с таблицами Excel при добавлении новых данных. Вместо расширения для включения строк таблица их просто игнорировала.

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

Новые данные игнорируются

Таблицы — отличная функция новых версий Excel. Они упрощают добавление, сортировку и фильтрацию данных. Обычно таблицы ведут себя довольно хорошо, если, конечно, вы не пытаетесь добавить в книгу Custom View или сделать что-то с группой листов, содержащих таблицы.

Файл содержит пользовательскую форму для ввода данных, похожую на показанную ниже, которую вы можете загрузить с моего сайта Contextures. Когда вы нажимаете кнопку «Добавить эту часть», новая запись добавляется в конец существующих строк.

На моем компьютере все работало хорошо, поэтому я отправил файл своему клиенту для тестирования. К сожалению, на его компьютере дела пошли не так.

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

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

Решить проблему

Изучив несколько вариантов, я наконец понял, что проблема была вызвана настройкой автозамены. Кто вообще помнит, что в Excel есть настройки автозамены?

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

ПРИМЕЧАНИЕ. На вкладке «Дизайн инструментов для работы с таблицами» на ленте много места — возможно, эти настройки можно будет добавить туда в будущей версии Excel. А пока вы можете добавить команду «Параметры автозамены» на одну из вкладок, чтобы упростить доступ к ней.

Например, поместите его на вкладку "Рецензирование" вместе с другими инструментами проверки правописания.

Изменить настройки автозамены

ПРИМЕЧАНИЕ. Изменение этих параметров повлияет на все книги, которые вы открываете в Excel на своем компьютере.

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

  1. В левом конце ленты перейдите на вкладку "Файл".
  2. Затем нажмите "Параметры".
  3. В окне параметров Excel слева нажмите Проверка правописания.
  4. В разделе "Параметры автозамены" нажмите "Параметры автозамены".
  5. Перейдите на вкладку "Автоформат при вводе". Вы наконец-то добрались до настроек!
  6. Добавьте флажки к параметрам "Включить новые строки и столбцы в таблицу" и "Заполнить формулы в таблицах для создания вычисляемых столбцов".
  7. Дважды нажмите "ОК", чтобы вернуться в Excel.

    8. Видео: таблица Excel не расширяется автоматически

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

    Загрузить образец файла

    Чтобы поэкспериментировать с таблицами Excel и получить код для программного изменения настроек автозамены, перейдите на страницу таблицы Excel на моем веб-сайте Contextures.

    27 мыслей о «Таблица Excel не расширяется для новых данных»

    Спасибо за участие. Вот новый сценарий, для которого я ищу вашу помощь. Я работаю над таблицей (в Excel), где я защитил некоторые строки (с формулами) от модификации, и листы будут доступны пользователю. кто может добавлять/удалять строки из таблицы. я ищу вашу поддержку, чтобы понять, как я могу добавить новую строку с формулами из строки выше без использования VBA.

    Очень хорошее и полезное объяснение!
    Поздравление

    Спасибо, но нет, не решает, так как в моем Excel были обе эти галочки. Я добавил столбец слева от всех столбцов, чтобы использовать его в качестве категории для сортировки. Он обрабатывает это как отдельную таблицу.

    У меня была такая же проблема.
    Оказалось, что под моей таблицей скрывались невидимые данные.
    (Ячейки с одним пробелом в них, которые были вставлены во время какого-то специального тестирования давным-давно.)
    Я предполагаю, что Excel видит заполненные ячейки под таблицей и не добавляет их в новую listRow в таблице.
    Удалил эти значения, и все снова заработало как положено.

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