Как удалить вычисляемое поле в сводной таблице
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Как удалить вычисляемое поле в сводной таблице
Когда в сводной таблице есть расчетные поля , может возникнуть необходимость внести исправления в сам расчет, либо удалить данное поле.
Для того, чтобы это сделать, необходимо воспользоваться модулем настройки вычисляемых полей в EXCEL.
В примере вычисляемым поле является поле с валовым доходом с НДС .
Предположим, нам необходимо изменить расчет в поле и перевести в валовый доход БЕЗ НДС.
Для упрощения, определим ставку НДС=18% для всех групп товаров в таблице.
Для этого:
1. Кликнем на любом элементе сводной таблицы и в группе меню «Параметры» на ленте.
2. Кликнем на «Формулы» (или «Поля, элементы, наборы») и выберем «Вычисляемое поле» .
3. В открывшемся окне, необходимо нажать на выпадающий список справа от поля «Имя» . По умолчанию, имена вычисляемых полей сводной таблицы в данном модуле не отображаются.
4. Выберем поле, по которому необходимо изменить расчет и откорректируем формулу в поле «Формула» .
В примере мы переведем Валовый доход в значения без НДС.
=’Т/О в розничных ценах с НДС’ -‘Т/О в ценах закупки с НДС’
=(‘Т/О в розничных ценах с НДС’ -‘Т/О в ценах закупки с НДС’)/1,18
5. Переименуем поле и нажмем кнопку «Изменить» . Закроем модуль.
Итогом будет пересчитанный, уже по новой формуле, столбец.
Для удаления поля, необходимо выбрать имя поля в модуле и нажать «Удалить».
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
Вычисление значений в сводной таблице
В сводных таблицах можно использовать функции сведения в полях значений для объединения значений из исходных данных. Если функции сведения и настраиваемые вычисления не дают желаемых результатов, вы можете создать собственные формулы в вычисляемых полях и вычисляемых объектах. Например, можно добавить вычисляемый объект с формулой расчета комиссионных за продажу, которые могут быть разными в различных регионах. Эти комиссионные будут автоматически включены в промежуточные и общие итоги в сводной таблице.
Другой способ вычислений — использовать меры в Power Pivot, которые вы создаете с помощью формулы выражений анализа данных (DAX). Дополнительные сведения см. в разделе Создание меры в Power Pivot.
В сводных таблицах можно рассчитывать данные разными способами. Вы узнаете о доступных методах вычислений, о влиянии типа исходных данных на вычисления и о том, как использовать формулы в сводных таблицах и на сводных диаграммах.
Доступные методы вычислений
Для вычисления значений в сводной таблице можно использовать любые из описанных ниже методов.
-
Функции сведения в полях значений. В сводной таблице в области значений отображаются сводные данные, вычисленные на основе исходных данных. Рассмотрим пример с такими исходными данными:
| Функция | Сведение данных |
|---|---|
| Сумма | Сумма значений. Функция по умолчанию для числовых данных. |
| Количество | Число значений. Действует аналогично функции СЧЁТЗ. Функция по умолчанию для данных, отличных от числовых. |
| Среднее | Среднее арифметическое. |
| Максимум | Наибольшее значение. |
| Минимум | Наименьшее значение. |
| Произведение | Произведение значений. |
| Количество чисел | Количество числовых значений. Действует аналогично функции СЧЁТ. |
| Стандартное отклонение | Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. |
| СТАНДОТКЛОНП | Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные. |
| ДИСП | Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. |
| Несмещенная дисперсия | Дисперсия генеральной совокупности, которая содержит все сводимые данные. |
| Функция | Результат |
|---|---|
| Без вычислений | Значение, введенное в данное поле. |
| % от общей суммы | Значения в процентах от общей суммы всех значений или точек данных в отчете. |
| % от суммы по столбцу | Все значения в каждом столбце или ряду в процентах от итогового значения по этому столбцу или ряду. |
| % от суммы по строке | Значение в каждой строке или категории в процентах от итогового значения по этой строке или категории. |
| Доля | Значения в процентах от значения базового элемента в соответствующем базовом поле. |
| % от суммы по родительской строке | Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента по строкам). |
| % от суммы по родительскому столбцу | Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента по столбцам). |
| % от родительской суммы | Рассчитывает значения следующим образом: (значение элемента) / (значение родительского элемента в выбранном базовом поле). |
| Отличие | Значения в виде разности по отношению к значению базового элемента в соответствующем базовом поле. |
| Приведенное отличие | Значения в виде разности в процентах по отношению к значению базового элемента в соответствующем базовом поле. |
| С нарастающим итогом в поле | Значение в виде нарастающего итога для последовательных элементов в базовом поле. |
| % от суммы с нарастающим итогом в поле | Значение в виде нарастающего итога в процентах для последовательных элементов в базовом поле. |
| Сортировка от минимального к максимальному | Ранг выбранных значений в определенном поле с учетом того, что наименьшему из них присваивается значение 1, а остальным — значения более высокого ранга соответственно. |
| Сортировка от максимального к минимальному | Ранг выбранных значений в определенном поле с учетом того, что наибольшему значению в поле присваивается значение 1, а каждому меньшему значению — более высокий ранг. |
| Индекс | Рассчитывает значения следующим образом: ((значение в ячейке) x (общий итог)) / ((итог строки) x (итог столбца)). |
- Формулы. Если функции сведения и настраиваемые вычисления не дают желаемых результатов, вы можете создать собственные формулы в вычисляемых полях и вычисляемых объектах. Например, можно добавить вычисляемый объект с формулой расчета комиссионных за продажу, которые могут быть разными в различных регионах. Эти комиссионные будут автоматически включены в промежуточные и общие итоги в отчете.
Влияние типа источника данных на вычисления
Доступность вычислений и параметров в отчете зависит от того, получены ли исходные данные из базы данных OLAP.
- Вычисления на основе исходных данных OLAP. При создании сводных таблиц на основе кубов OLAP сводные значения вычисляются на сервере OLAP еще до отображения результатов в Excel. В сводной таблице невозможно изменить способ вычисления этих значений. Например, вы не сможете выбрать другую функцию сведения для вычисления полей данных или промежуточных итогов и добавить вычисляемые поля или вычисляемые объекты. Кроме того, если сервер OLAP предоставляет вычисляемые поля, называемые «вычисляемыми элементами», вы увидите их в списке полей сводной таблицы. Вы также увидите все вычисляемые поля и вычисляемые объекты, созданные с помощью макросов, которые написаны на языке Visual Basic для приложений (VBA) и хранятся в книге, но не сможете их изменить. Если вам нужны дополнительные типы вычислений, обратитесь к администратору базы данных OLAP. Если исходные данные получены из базы данных OLAP, то при вычислении промежуточных и общих итогов можно включить или исключить значения для скрытых элементов.
- Вычисления на основе исходных данных не из базы данных OLAP. В сводных таблицах, основанных на внешних данных других типов или на данных листа Excel, для вычисления полей значений, содержащих числовые данные, используется функция «Сумма», а для вычисления полей данных, содержащих текст, — функция «Количество». Для дальнейшего анализа и обработки своих данных вы можете выбрать другие функции сведения, например «Среднее», «Максимум» или «Минимум». Кроме того, можно создавать собственные формулы, в которых используются элементы отчета или другие данные листа. Для этого нужно создать вычисляемое поле или вычисляемый объект в поле.
Использование формул в сводных таблицах
Формулы можно создавать только в отчетах, которые основаны на исходных данных, полученных не из источника данных OLAP. В отчетах, основанных на базе данных OLAP, формулы не поддерживаются. При использовании формул в сводных таблицах нужно учитывать описанные ниже правила синтаксиса и поведения формул.
- Элементы формулы сводной таблицы. В формулах, которые создаются для вычисляемых полей и вычисляемых объектов, можно использовать операторы и выражения, как и в других формулах на листе. Также можно использовать константы и ссылаться на данные из отчета, но не допускается использование ссылок на ячейки и определенных имен. Невозможно использовать функции листа, для которых нужны аргументы в виде ссылок на ячейки или определенных имен, а также формулы массива.
- Имена полей и элементов. В Excel имена полей и элементов используются для идентификации этих элементов отчета в формулах. В приведенном ниже примере для данных в диапазоне C3:C9 используется имя поля Молоко. Для вычисляемого объекта в поле Тип, оценивающего объем продаж нового продукта на основе данных о продажах молочных продуктов, можно использовать формулу =Молоко * 115%.
Примечание: На сводной диаграмме имена полей отображаются в списке полей сводной таблицы, а имена элементов можно просмотреть в каждом раскрывающемся списке полей. Не следует путать эти имена с теми, которые отображаются в подсказках к диаграммам и соответствуют именам рядов и точек данных.
Использование формул в сводных диаграммах
Чтобы использовать формулы в сводной диаграмме, их нужно создать в связанной сводной таблице. Там вы увидите отдельные значения, из которых состоят данные, а затем сможете посмотреть на результаты в графическом представлении на сводной диаграмме.
Например, на этой сводной диаграмме представлены данные о продажах для каждого продавца по регионам:
Чтобы посмотреть, как будут выглядеть объемы продаж, если увеличатся на 10 %, можно создать вычисляемое поле в связанной сводной таблице и воспользоваться формулой =Продажи * 110%.
Результат сразу отображается на сводной диаграмме, как показано на этом рисунке:
Чтобы отобразить отдельный маркер данных для продаж в регионе «Север» за вычетом транспортных расходов, которые составляют 8 %, можно создать в поле «Регион» вычисляемый объект с такой формулой: =Север – (Север * 8%).
Диаграмма будет выглядеть следующим образом:
Однако вычисляемый объект, созданный в поле «Продавец», будет отображаться как ряд, представленный в легенде, и появится на диаграмме в виде точки данных в каждой категории.
Создание формул в сводной таблице
Важно: Создать формулу в сводной таблице, подключенной к источнику данных OLAP, невозможно.
Прежде всего определитесь, что нужно создать в поле: вычисляемое поле или вычисляемый объект. Если вы хотите использовать в формуле данные из другого поля, создайте вычисляемое поле. Если нужно использовать в формуле данные из одного или нескольких конкретных элементов в определенном поле, создайте вычисляемый объект.
В случае вычисляемых объектов в отдельных ячейках можно вводить разные формулы. Например, если вычисляемый объект с именем ЯблокиОбласть содержит формулу =Яблоки * 0,25 для всех месяцев, то для июля, августа и сентября ее можно заменить формулой =Яблоки * 0,5.
Если есть несколько вычисляемых объектов или формул, можно настроить порядок вычислений.
Добавление вычисляемого поля
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле.
- В поле Имя введите имя для поля.
- В поле Формула введите формулу для поля. Чтобы использовать в формуле данные из другого поля, щелкните его в списке Поля и нажмите кнопку Добавить поле. Например, чтобы вычислить величину комиссионных, составляющую 15 %, для каждого значения в поле «Продажи», введите формулу =Продажи * 15%.
- Нажмите кнопку Добавить.
Добавление вычисляемого объекта в поле
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- Если элементы в поле сгруппированы, на вкладке Анализ в группе Группировать выберите команду Разгруппировать.
- Щелкните поле, в которое нужно добавить вычисляемый объект.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемый объект.
- В поле Имя введите имя вычисляемого объекта.
- В поле Формула введите формулу для этого объекта. Чтобы использовать в формуле данные из имеющегося элемента, щелкните его в списке Элементы и нажмите кнопку Добавить элемент (он должен находиться в том же поле, что и вычисляемый объект).
- Нажмите кнопку Добавить.
Ввод разных формул для вычисляемых объектов в отдельных ячейках
- Щелкните ячейку, для которой нужно изменить формулу. Чтобы изменить формулу для нескольких ячеек, нажмите клавишу CTRL и, удерживая ее, выделите остальные ячейки.
- В строке формул внесите изменения в формулу.
Изменение порядка вычислений для нескольких вычисляемых объектов или формул
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Порядок вычислений.
- Щелкните одну из формул и нажмите кнопку Вверх или Вниз.
- Повторите эти действия для других формул, чтобы расположить их в нужном порядке.
Просмотр всех формул, используемых в сводной таблице
Вы можете отобразить список всех формул, которые используются в текущей сводной таблице.
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вывести формулы.
Редактирование формулы в сводной таблице
Перед тем как редактировать формулу, определите, к чему она относится: к вычисляемому полю или вычисляемому объекту. Если формула находится в вычисляемом объекте, также определите, является ли она единственной для него.
В случае вычисляемых объектов можно изменять отдельные формулы для конкретных ячеек одного объекта. Например, если вычисляемый объект с именем ЯблокиРасчет содержит формулу =Яблоки * 0,25 для всех месяцев, то для июля, августа и сентября ее можно заменить формулой =Яблоки * 0,5.
Определение принадлежности формулы к вычисляемому полю или вычисляемому объекту
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вывести формулы.
- Найдите в списке формулу, которую нужно изменить. Она может находиться в разделе «Вычисляемое поле» или «Вычисляемый объект».
Редактирование формулы вычисляемого поля
- Щелкните сводную таблицу. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле.
- В списке Имя выберите вычисляемое поле, для которого нужно изменить формулу.
- В поле Формула измените формулу.
- Нажмите кнопку Изменить.
Редактирование одной формулы для вычисляемого объекта
- Щелкните поле, содержащее вычисляемый объект.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемый объект.
- В поле Имя выберите вычисляемый объект.
- В поле Формула измените формулу.
- Нажмите кнопку Изменить.
Редактирование отдельной формулы для конкретной ячейки вычисляемого объекта
- Щелкните ячейку, для которой нужно изменить формулу. Чтобы изменить формулу для нескольких ячеек, нажмите клавишу CTRL и, удерживая ее, выделите остальные ячейки.
- В строке формул внесите изменения в формулу.
Совет: Если есть несколько вычисляемых объектов или формул, можно настроить порядок вычислений. Дополнительные сведения см. в разделе Изменение порядка вычислений для нескольких вычисляемых объектов или формул.
Удаление формулы из сводной таблицы
Примечание: Если удалить формулу из сводной таблицы, восстановить ее будет невозможно. Если вы не хотите удалять формулу безвозвратно, то можете просто скрыть поле или элемент, перетащив его за пределы сводной таблицы.
- Определите, к чему относится формула: к вычисляемому полю или вычисляемому объекту. Вычисляемые поля отображаются в списке полей сводной таблицы. Вычисляемые объекты отображаются в виде элементов в других полях.
- Выполните одно из указанных ниже действий.
- Если нужно удалить вычисляемое поле, щелкните в любом месте сводной таблицы.
- Если нужно удалить вычисляемый объект, в сводной таблице щелкните поле, которое его содержит. Отобразится вкладка «Работа со сводными таблицами» с дополнительными вкладками Анализ и Конструктор.
- На вкладке Анализ в группе Вычисления выберите команду Поля, элементы и наборы, а затем — пункт Вычисляемое поле или Вычисляемый объект.
- В поле Имя выберите поле или элемент, который нужно удалить.
- Нажмите кнопку Удалить.
Для сведения данных в сводной таблице в Excel в Интернете можно использовать такие функции, как СУММ, СЧЁТ и СРЗНАЧ. По умолчанию для чисел в полях значений используется функция СУММ. Вы можете просматривать и редактировать сводную таблицу на основе источника данных OLAP, но не можете создать ее в Excel для Интернета.
Ниже описано, как выбрать другие функции сведения данных.
- Щелкните в любом месте сводной таблицы, а затем выберите Сводная таблица >Список полей. Вы также можете щелкнуть сводную таблицу правой кнопкой мыши и выбрать Показать список полей.
- В списке Поля сводной таблицы в группе Значения щелкните стрелку рядом с полем значений.
Примечание: Функции сведения недоступны в сводных таблицах на базе источников данных OLAP.
| Функция сведения данных | Вычисляемое значение |
|---|---|
| Сумма | Сумма значений. Используется по умолчанию для полей с числовыми значениями. |
| СЧЁТ | Количество заполненных полей. Функция сведения данных СЧЁТ работает так же, как СЧЁТЗ. СЧЁТ по умолчанию используется для пустых полей и полей с нечисловыми значениями. |
| Среднее | Среднее арифметическое. |
| Максимум | Наибольшее значение. |
| Минимум | Наименьшее значение. |
| Произведение | Произведение значений. |
| Количество чисел | Количество значений, содержащих числа (отличается от функции СЧЁТ, в которой учитываются заполненные поля). |
| Смещенное отклонение | Оценка стандартного отклонения генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. |
| СТАНДОТКЛОНП | Стандартное отклонение генеральной совокупности, которая содержит все сводимые данные. |
| ДИСП | Оценка дисперсии генеральной совокупности, где выборка является подмножеством всей генеральной совокупности. |
| Несмещенная дисперсия | Дисперсия генеральной совокупности, которая содержит все сводимые данные. |
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
покупка
Если вы добавили несколько новых вычисляемых полей в сводную таблицу как Как добавить вычисляемое поле в сводную таблицу статья упоминается. И после просмотра анализа данных нового пользовательского вычисления в сводной таблице вы хотите удалить вычисляемое поле, чтобы восстановить сводную таблицу. Сегодня я расскажу о том, как временно или навсегда удалить вычисляемое поле из сводной таблицы.
Временно удалить вычисляемое поле из сводной таблицы
Если вы хотите временно удалить вычисленное поле, а затем применить его снова, вам просто нужно скрыть поле в списке полей.
1. Щелкните любую ячейку в сводной таблице, чтобы отобразить Список полей сводной таблицы панель.
2. В Список полей сводной таблицы панели, пожалуйста, снимите флажок с вычисляемого поля, которое вы создали, см. снимок экрана:
3. После снятия флажка настраиваемого вычисляемого поля это поле будет удалено из сводной таблицы.
Внимание: Если вам нужно, чтобы появилось вычисляемое поле, просто проверьте поле еще раз в Список полей сводной таблицы панель.
Удалить вычисляемое поле из сводной таблицы навсегда
Чтобы окончательно удалить вычисляемое поле, выполните следующие действия:
1. Щелкните любую ячейку в сводной таблице, чтобы отобразить Инструменты сводной таблицы Вкладки.
2. Затем нажмите Опции > Поля, предметы и наборы > Расчетное поле, см. снимок экрана:
3. В Вставить вычисляемое поле диалоговом окне выберите имя настраиваемого вычисляемого поля из Имя выпадающий список, см. снимок экрана:
4. Затем нажмите Удалить кнопку и нажмите OK чтобы закрыть это диалоговое окно, и теперь ваше вычисляемое поле было удалено навсегда, вы больше не сможете его восстанавливать.
Статьи по теме :