как удалить вычисляемое поле в сводной таблице
Вычисляемое поле в Сводных таблицах в MS Excel
history 2 марта 2019 г.
Научимся добавлять и редактировать Вычисляемое поле в Сводной таблице MS EXCEL 2010.
Нашей задачей будет:
В итоге у нас должна получиться вот такая сводная таблица.
Исходная таблица
Исходную таблицу подготовим в специальном формате таблиц MS EXCEL (см. статью Таблицы в формате EXCEL 2007 ).
Сводная таблица
Для создания сводной таблицы выделите любую ее ячейку и в меню Вставка/ Таблицы нажмите кнопку Сводная таблица. В результате появится диалоговое окно.
Нажав ОК, сводная таблица автоматически создастся на новом листе.
Перед тем как создать Вычисляемое поле перетащите поле Номер месяца в Названия строк.
Создаем вычисляемое поле
Для решения задачи нам потребуется вычислить % выполнения плана по формуле =’Продано, руб.’/’План, руб.’
Для этого выделите ячейку в Сводной таблице, в появившемся меню Работа со сводными таблицами выберите Параметры/ Вычисления/ Поля, элементы и наборы/ Вычисляемое поле :
Появится диалоговое окно:
Интерфейс этого окна не относится к интуитивно понятным вещам, поэтому требует дополнительного пояснения:
После проведенных манипуляций в списке поле Сводной таблицы появится еще одно поле. Завершите формирование Сводной таблицы как показано на рисунке ниже, разместив Вычисляемое поле в область Значения.
После несложного форматирования Сводная таблица приобретет законченный вид (необходимо убрать ошибку #ДЕЛ/0!, изменить названия столбцов и изменить формат ячеек на процентный ).
Обратите внимание, что Сводная таблица содержит Общий итог как по столбцам, так и по строкам.
Теперь разберемся, что Вычисляемое поле нам насчитало.
Вычисляемое поле. Алгоритм расчета
Теперь проверим итоги по месяцам. За январь итоговым значением является 93,00%. Как это значение получилось?
Сначала программа вычислила СУММУ продаж за январь по всем годам, затем, вычислила СУММУ всех плановых значений. Разделив одно на другое, было получено 93,00%. В этом можно убедиться проделав вычисления самостоятельно (см. строку 10 на листе Сводная таблица, столбцы H:J).
Аналогично расчет ведется и для итогов по столбцам: находится сумма продаж и плана по годам, затем вычисляется их отношение.
Если бы для каждого месяца в исходной таблице было бы несколько сумм продаж и плановых значений, то расчет был бы аналогичен подсчету итоговых значений.
В итоговом столбце теперь будет отображаться средний процент выполнения плана.
Изменяем и удаляем Вычисляемое поле
Вызовите тоже диалоговое окно, которое мы использовали для создания Вычисляемого поля. В выпадающем списке выберите нужное поле. Появится его формула, которую можно отредактировать, также как и название этого Вычисляемого поля.
Там же можно удалить это поле.
Еще одно ограничение
Еще одно ограничение Вычисляемого поля проявляется при попытке использовать его в качестве названия Строк или Столбцов Сводной таблицы. Этого сделать нельзя. Покажем это на нашем примере.
Изначально в исходной таблице номер месяца и года вычислялись в отдельных столбцах. Попробуем сделать эти вычисления в Вычисляемом поле.
Однако, перенести его в качестве строк сводной таблицы не получается.
Как удалить вычисляемое поле в сводной таблице
Как удалить вычисляемое поле из сводной таблицы?
Если вы добавили несколько новых вычисляемых полей в сводную таблицу как Как добавить вычисляемое поле в сводную таблицу статья упоминается. И после просмотра анализа данных нового пользовательского вычисления в сводной таблице вы хотите удалить вычисляемое поле, чтобы восстановить сводную таблицу. Сегодня я расскажу о том, как временно или навсегда удалить вычисляемое поле из сводной таблицы.
Временно удалить вычисляемое поле из сводной таблицы
Временно удалить вычисляемое поле из сводной таблицы
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Если вы хотите временно удалить вычисленное поле, а затем применить его снова, вам просто нужно скрыть поле в списке полей.
1. Щелкните любую ячейку в сводной таблице, чтобы отобразить Список полей сводной таблицы панель.
2. В Список полей сводной таблицы панели, пожалуйста, снимите флажок с вычисляемого поля, которое вы создали, см. снимок экрана:
3. После снятия флажка настраиваемого вычисляемого поля это поле будет удалено из сводной таблицы.
Внимание: Если вам нужно, чтобы появилось вычисляемое поле, просто проверьте поле еще раз в Список полей сводной таблицы панель.
Удалить вычисляемое поле из сводной таблицы навсегда
Чтобы окончательно удалить вычисляемое поле, выполните следующие действия:
1. Щелкните любую ячейку в сводной таблице, чтобы отобразить Инструменты сводной таблицы Вкладки.
2. Затем нажмите Опции > Поля, предметы и наборы > Расчетное поле, см. снимок экрана:
3. В Вставить вычисляемое поле диалоговом окне выберите имя настраиваемого вычисляемого поля из Имя выпадающий список, см. снимок экрана:
4. Затем нажмите Удалить кнопку и нажмите OK чтобы закрыть это диалоговое окно, и теперь ваше вычисляемое поле было удалено навсегда, вы больше не сможете его восстанавливать.
Удаление формулы из сводной таблицы
Если удалить формулу из сводной таблицы, восстановить ее будет невозможно. Если вы не хотите удалять формулу безвозвратно, то можете просто скрыть поле или элемент, перетащив его за пределы отчета сводной таблицы.
1. Определите, к чему относится формула: к вычисляемому полю или вычисляемому объекту.
Вычисляемые поля отображаются в списке полей сводной таблицы. Вычисляемые объекты отображаются в виде элементов в других полях.
2. Выполните одно из указанных ниже действий.
o Если нужно удалить вычисляемое поле, щелкните в любом месте отчета сводной таблицы.
o Если нужно удалить вычисляемый объект, в сводной таблице щелкните поле, которое его содержит.
Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
3. На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле или Вычисляемый объект.
4. В поле Имя выберите поле или элемент, который нужно удалить.
5. Нажмите кнопку Удалить.
Копирование сводной таблицыExcel без источника данных с сохранением исходного форматирования
В вашей работе возможно появится такая ситуация, когда вам потребуется послать некоторое количество информации из отчета сводной таблицы Excel, но при этом вы не хотите показывать источник исходных данных. То есть, если говорить другими словами, вы хотите «отключить» сводную таблицу от источника данных.
Отформатированная сводная таблица выглядит изначально в таком виде (рисунок 71).
Рисунок 71 – пример отформатированной сводной таблицы
Excel не имеет возможности отключения сводной таблицы от источника данных, но в Excel есть гибкая функция которая называется Специальная вставка.
1. В сводной таблице выберите необходимый вам диапазон и нажмите скопируйте его (воспользуйтесь сочетанием клавиш Ctrl+C).
2. На новом листе или другом свободном месте текущего листа выделите ячейку отобразите диалоговое окно Специальная вставка.
3. В диалоговом окне Специальная вставка, выберите Значения параметров и нажмите OK.
Необходимый вам диапазон будет скопирован без указания источника данных, но при этом к скопированным данным не будет применено форматирование и стили оформления, это надо будет сделать заново.
Как удалить из фильтров сводной таблицы “старые” данные?
Кулешова Ольга Владимировна: Как удалить из фильтров сводной таблицы “старые” данные?
Ситуация довольна простая. По источнику данных происходит построение отчета сводной таблицы. Затем в источнике происходит изменение данных, но при обновлении отчета в фильтрах видны как прежние, так и новые значения. Как избавиться от “старых” (прежних) данных?
Например, строим отчет по источнику, в котором есть данные 4-х городов:
Теперь произведем замену в исходных данных, например, город Казань заменим на Ульяновск:
Однако, выполнив команду Обновить [Refresh] в сводной таблице (клавиши Alt+F5), данные отображаются в ячейках корректно в соответствии с данными источника:
Чтобы убрать из фильтра уже не существующие данные, следуют в контекстном меню отчета выбрать команду Параметры сводной таблицы [PivotTable Options] и на вкладке Данные [Data] в списке Число элементов, сохраняемых для каждого поля [Number of items to retain per field] выбрать Нет [None].
выполнить обновление (Alt+F5), после чего элементы в фильтре содержатся актуальные:
Заказ добавлен в Корзину.
Для завершения оформления, пожалуйста, перейдите в Корзину!
Отображение и скрытие промежуточных и общих итогов в сводной таблице
При создании стеблицы, в которых отображаются суммы значений, автоматически отображаются итоги и общие итоги, но их также можно показать или скрыть.
Отображение и скрытие промежуточных итогов
Щелкните любое место сводной таблицы, чтобы отобразить вкладку Работа со сводными таблицами.
На вкладке Конструктор нажмите кнопку Итоги.
Выберите подходящий вариант.
Не показывать подытожи
Показать все итоги в нижней части группы
Показывать все подытогов в верхней части группы
Совет: Чтобы включить в общие итоги отфильтрованные элементы, щелкните пункт Включить отобранные фильтром элементы в итоги. Чтобы отключить эту функцию, выберите тот же пункт еще раз. Дополнительные параметры итогов и отфильтрованных элементов можно найти на вкладке Итоги & Фильтры в диалоговом окне Параметры таблицы (Анализ параметров >).
Вот как отобразить или скрыть общие итоги.
Щелкните любое место сводной таблицы. На ленте появится вкладка Работа со сводными таблицами.
На вкладке Конструктор нажмите кнопку Общие итоги.
Выберите подходящий вариант.
Отключено для строк и столбцов
В строках и столбцах
Только для столбцов
Совет: Если вы не хотите показывать общие итоги для строк или столбцов, сняйте флажок Показывать общие итоги для строк или Показывать общие итоги для столбцов на вкладке Итоги & Фильтры в диалоговом окне Параметры таблицы (Анализ> Параметры).
При создании стеблицы, в которых отображаются суммы значений, автоматически отображаются итоги и общие итоги, но их также можно показать или скрыть.
Совет: Общие итоги в строке отображаются только в том случае, если данные состоят из одного столбца, так как общие итоги для группы столбцов обычно не имеют смысла (например, если один из них содержит значения количества, а второй — цены). Чтобы отобразить общие итоги по значениям в нескольких столбцах, создайте вычисляемый столбец в исходных данных и отобразите его в сводной таблице.
Отображение и скрытие промежуточных итогов
Вот как отобразить или скрыть промежуточные итоги.
Щелкните любое место сводной таблицы. На ленте появятся вкладки Анализ сводной таблицы и Конструктор.
На вкладке Конструктор нажмите кнопку Итоги.
Выберите подходящий вариант.
Не показывать промежуточные итоги
Показывать все промежуточные итоги в нижней части группы
Показывать все промежуточные итоги в заголовке группы
Совет: Чтобы включить в расчет отфильтрованные значения, щелкните Включить отобранные фильтром элементы в итоги. Чтобы отключить этот параметр, щелкните его еще раз.
Вот как отобразить или скрыть общие итоги.
Щелкните любое место сводной таблицы. На ленте появятся вкладки Анализ сводной таблицы и Конструктор.
На вкладке Конструктор нажмите кнопку Общие итоги.
Выберите подходящий вариант.
Отключить для строк и столбцов
Включить для строк и столбцов
Включить только для строк
Включить только для столбцов
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.