Все мы умеем пользоваться сводными таблицами. У них есть куча преимуществ, но есть и куча недостатков. Многие считают, что основная проблема со сводными таблицами - это то, что в ней нет возможности делать вычисления – это заблуждение. Зачем это может быть нужно. Предположим, вы составляете бюджет с помощью сводных таблиц. Данные в исходной таблице представлены в поквартальной разбивке, для целей бюджета будет необходимо сложить эти данные в годовую сумму.
Удалить формулу сводной таблицы
На вкладке «Параметры» в группе «Инструменты» нажмите «Формулы», а затем выберите «Заказ порядка».
- Нажмите формулу, а затем нажмите «Переместить вверх» или «Переместить вниз».
- Продолжайте до тех пор, пока формулы не будут в желаемом порядке расчета.
Чтобы скрыть поле, перетащите его из отчета. Вычисленные поля отображаются в списке полей сводной таблицы, а элементы отображаются как элементы в других полях. Выполните одно из следующих действий. В поле «Имя» выберите поле для удаления. Щелкните поле, содержащее элемент для удаления.
На самом деле такая возможность есть и реализуется она одним из трех способов.
Освежить свои знания об основах сводных таблиц можно .
Первый способ. Сделать вычисления в таблице с исходными данными. Способ, прямо скажем, не фонтан – через неправильное место.
Второй способ. Построить расчеты в рядом со сводной таблицей – опять таки, способ не из лучших. Если сводная таблица будет меняться (а пользоваться сводными таблицами стоит именно тогда, когда мы ожидаем изменения в таблице).
В поле «Имя» выберите элемент для удаления. В сводных таблицах вы можете использовать сводные функции в полях значений для объединения значений исходных исходных данных. Если сводные функции и пользовательские вычисления не дают желаемых результатов, вы можете создавать собственные формулы в вычисленных полях и вычисленных элементах. Например, вы можете добавить вычисленную формулу формулы для комиссии продаж, которая варьируется в зависимости от географической области. В этом случае сводная таблица будет автоматически включать комиссию в промежуточные итоги и общие суммы.
Третий способ. Встроить вычисляемые поля в саму сводную таблицу. А вот это хороший способ. Делается это следующим образом. Когда сводная таблица уже построена, ставим курсор на сводную таблицу (при этом на ленте появляются две закладки, относящиеся к данной сводной таблице) и нажимаем на появившуюся закладку Options , затем Fields , Items , & Sets из блока Calculations , в выпавшем меню выбираем Calculated field . В 2007 офисе эта кнопка была на той же вкладке Options , только кнопка была отдельная.
Дополнительная информация о вычисляемых значениях в сводной таблице. Сводные таблицы предлагают различные режимы расчета данных. В этой статье описываются доступные методы расчета, влияние типа исходных данных на расчет и использование формул в сводных таблицах и сводных диаграммах.
Чтобы вычислить значения в сводной таблице, вы можете использовать один или все из следующих методов расчета. 
Формулы Если итоговые функции и пользовательские вычисления не дают желаемых результатов, вы можете создавать собственные формулы в вычисленных полях и вычисленных элементах. Например, вы можете добавить элемент, рассчитанный с помощью формулы комиссии продаж, которая варьируется в зависимости от географической области. В этом случае отчет будет автоматически включать комиссию в промежуточные итоги и общие суммы.
- Поле «Линия» содержит элементы «Север», «Юг», «Восток» и «Запад».
- Пользовательский расчет.
- Пользовательские вычисления в полях значений доступны следующим образом.
В появившемся окне, в поле Name введите желаемое название поля. В поле Formula введите необходимую вам формулу, вставляя поля из списка внизу и нажимая кнопку Insert field .
Получится таблица с новым расчетным полем. Таблица готова!
Вы не можете изменить способ вычисления предварительно вычисленных значений в сводной таблице. Например, вы не можете изменить сводную функцию, используемую для вычисления полей данных или промежуточных итогов, или добавить вычисленные поля или расчетные элементы. Вы можете выбрать другую функцию «Сводка», такую как «Медиа», «Макс» или «Мин», для анализа и дальнейшей настройки ваших данных. Создав вычисленное поле или вычисленный элемент в поле, вы также можете создавать собственные формулы, в которых используются элементы отчета или данные из других электронных таблиц. Ниже приведен список правил и правил синтаксиса, связанных с формулами, которые вам нужно знать при использовании формул в сводных таблицах.
Экселевский файл можно скачать отсюда.
Интересный факт. При применении третьего способа программа на самом деле делает вычисления для каждой строки и после этого применяет необходимый параметр: суммирование, среднее значение и т.д.
Последние новости
Продвинутый Excel: Почему я перестал пользоваться функцией V…
Я уже писал про то, что функция VLOOKUP (ВПР), наверное, самая полезная функция после простых арифметических операций …
Использование формул в сводных диаграммах. Чтобы использовать формулы в сводной диаграмме, вы должны создать формулы в связанной сводной таблице, где вы можете увидеть отдельные значения, составляющие данные, чтобы вы могли видеть результаты графически в сводной диаграмме.Например, следующая сводная диаграмма показывает продажи каждого торгового агента, деленного на область. Результат немедленно появляется в сводной диаграмме, как показано ниже. Прежде чем приступить к работе, решите, следует ли использовать поле вычисления или вычисляемый элемент в поле. Выбирайте вычисленное поле для использования данных в другом поле формулы. Вместо этого выберите вычисляемый элемент для использования данных одного или нескольких конкретных элементов, содержащихся в поле в формуле.
Как Excel пересчитывает книгу и почему надо избегать волатил…
Если вы часто работаете с большими файлами, которые производят большое количество вычислений, зависящих друг от друга, о…
Как быстро построить график Waterfall (водопад)
В Excel отсутствует стандартные диаграммы типа Waterfall. Поэтому для создания диаграмм этого типа обычно используют гис…
Введите различные формулы ячейки ячейки для вычисленных элементов
Для вычисленных элементов вы можете вводить различные формулы ячеек на ячейку. Если имеется несколько расчетных элементов или формул, вы можете определить порядок расчета.
Определить порядок вычисления для нескольких расчетных элементов или формул
Вы можете просмотреть список всех формул, которые используются в текущей сводной таблице.Окно контрольного значения для отслеживания результатов
Часто бывает, что мы работаем с большими таблицами, которые рассчитывают в конечном итоге 1-2 показателя, но зависят от …
Bullet chart для сравнения планируемых показателей и фактиче…
Часто возникает необходимость сравнить основные финансово-экономические показатели с планируемыми, например, для целей о…
Прежде чем изменять формулу, определите, находится ли она в вычисленном поле или вычисленном элементе. Если формула находится в вычисленном элементе, определите, является ли она единственной формулой, связанной с элементом. Для вычисленных элементов вы можете редактировать отдельные формулы для определенных ячеек.Измените формулу для вычисленного поля
Измените одну формулу для вычисленного элемента
Измените одну формулу для конкретной ячейки вычисляемого элемента. Если вы удалите форму сводной таблицы, она будет удалена навсегда. Если вы не хотите окончательно удалять формулу, вы можете скрыть поле или элемент, перетащив его из сводной таблицы.Просмотреть все формулы, которые используются в сводной таблице
Чтобы просмотреть список всех формул, используемых в текущей сводной таблице, выполните следующие действия.Тацуо Хориучи - 73-летний художник, рисующий в Excel
"Я никогда не пользовался Excel на работе, но я видел, как другие люди делают в нем довольно красивые диаграммы и график…
Как возвести число в степень и извлечь корень
Знаете ли вы, что для того, что для возведения в степень числа в Excel есть специальный символ ^ (на шестерке в английск…
Измените формулу в сводной таблице
В группе «Инструменты» на вкладке «Параметры» нажмите «Формулы», а затем «Списки формул». Если формула находится в вычисленном элементе, определите, является ли она единственной формулой, связанной с элементом, выполнив следующие действия: щелкните сводную таблицу. В списке формул найдите формулу, подлежащую редактированию в вычисленном поле или вычисленном элементе. На панели «Инструменты» на вкладке «Параметры» выберите «Формулы», а затем «Расчетное поле». Щелкните поле, содержащее вычисленный элемент. На панели «Инструменты» на вкладке «Параметры» нажмите «Формулы», а затем «Расчетный элемент». В поле «Имя» выберите вычисленный элемент. Щелкните ячейку, для которой вы хотите изменить формулу. В строке формул введите изменения формулы.
- Нажмите на сводную таблицу.
- В поле «Имя» выберите вычисленное поле, для которого вы хотите изменить формулу.
- В поле «Формула» измените формулу.
Дополнительные | Результат |
|
вычисления | ||
Сортировка от | Sort Smallest to | |
минимального к | ||
максимальному Удалить формулу в сводной таблицеВ группе «Инструменты» на вкладке «Параметры» нажмите «Формулы», а затем «Заказ решения». Продолжайте до тех пор, пока формулы не будут в порядке, в котором они должны быть рассчитаны. Выберите формулу и нажмите «Переместить вверх» или «Переместить вниз». . Если вы не хотите окончательно удалять формулу, вы можете скрыть поле или элемент. Вычисленные поля отображаются в списке полей сводной таблицы, а расчетные элементы отображаются как элементы внутри других полей. Элемент, рассчитанный в сводных таблицахУдалить вычисляемый элемент. Щелкните поле, содержащее элемент, который вы хотите удалить. На панели «Инструменты» на вкладке «Параметры» выберите «Формулы», а затем «Расчетный элемент». Вычисленный элемент сводной таблицы представляет собой формулу, которая вставляется в строку и столбцы таблицы вместо вычисленного поля, которое вместо этого организовано в области значений. Вычисленный элемент позволяет в основном выполнять операции агрегации и описание новые классы значений и могут быть допустимой альтернативой добавлению нового поля в таблицу источника данных. | элементов поля от минимального (1-я позиция) |
|
к максимальному значению |
||
Сортировка от | Определение порядкового номера значения |
|
максимального к | элемента (ранг) по отношению к значениям Как ввести вычисляемый элемент в сводную таблицуПеред установкой вычисленного элемента, если элементы, включенные в поле строк или столбцов, сгруппированы, в группе групп на вкладке «Анализ» нажмите кнопку «Разделить». Пример установки расчетного элементаРассмотрим базу данных, показанную на рисунке ниже, и рассмотрим возможность установки строк сводной таблицы, введя поле «Регион», столбцы, введя поле «Поставщик», а затем «Оборот» в элементе «Значения». Цель установки рассчитанного элемента - создать на линии двух новых записей, которые будут включать в себя Западные регионы и восточные регионы. |
|
минимальному | элементов поля от максимального (1-я позиция) |
|
к минимальному значению |
||
Вычисление значений по формуле: |
||
Значение_ в_ ячейке∙Общий_ итог Введите вычисленный элемент в сводной таблицеНа изображении ниже, в качестве примера используемого источника данных, только небольшая часть базы данных, вставленная в анализ сводной таблицы, показана в правой части окна изображение также является деталью поля полей таблицы сводных данных. Чтобы использовать команду, введите вычисляемый элемент в качестве первого шага, выберите любую запись в сводных таблицах в сводной таблице. Очевидно, что если вычисление было связано с столбцами, любой элемент в этой области был бы выбран. Затем в группе «Вычислить кнопки» на вкладке «Анализ» нажмите кнопку «Лагеря», «Элементы» и «Установка». |
||
Итог_ строки∙Итог_ столбца |
2-й способ: щелкнуть правой кнопкой мыши по значениям нужного поля в области ΣЗначения [Σ Values] и выбрать:
Итоги по , далее выбратьСумма ,Количество ,Среднее .Максимум ,Минимум ,Произведение или
Дополнительно , чтобы выбрать другую функцию из предлагаемого списка, настроить формат поля и изменить имя поля.
Дополнительные вычисления , далее выбрать нужный вариант расчета.
Повторное использование исходного поля в отчете сводной таблицы
По одному и тому же исходному полю можно делать несколько вычислений. Для этого необходимо исходного поле из списка полей сводной таблицы добавить в область значений Σ Значения [Σ
Values] несколько раз, затем настроить параметры поля. Это позволит одновременно видеть как само вычисление, так и вычисление, выполненное на его основе.
Добавление вычисляемых полей в сводную таблицу
В отчет сводной таблицы нельзя добавить новые строки или столбцы для вставки формул или дополнительных расчетов, но можно добавить вычисляемое поле, с помощью которых можно вводить формулы для расчета новых данных на основании существующих.
Вставка вычисляемого поля непосредственно в сводную таблицу – это наилучшее решение. Не потребуется управлять формулами и беспокоиться о расширяемости таблицы при росте или редактировании источника данных, причем сводная таблица останется достаточно гибкой при изменении определенийполей.
Еще одно огромное преимущество этого метода заключается в том, что можно изменять структуру сводной таблицы и даже просчитывать другие поля данных для вычисляемых полей, не беспокоясь об ошибках в формулах илинарушении ссылок на ячейки.
Microsoft Excel 2010. Уровень 2. Расширенные возможности
Таким образом, при добавлении собственных вычисляемых полей в сводную таблицу очевидны преимущества:
Исключение потенциальных ошибок в формулах и ссылках на ячейки.
Возможность добавления и удаления данных из сводной таблицы без изменения исходных вычислений.
Возможность автоматического перерасчета данных при изменении или обновлении сводной таблицы.
Обеспечение гибкости изменения вычислений при изменении определений элементов.
Возможность эффективного управления вычислениями.
Создание вычисляемого поля:
1. Выделить ячейку сводной таблицы;
2. На вкладке Параметры , в группеВычисления , раскрыть списокПоля,
элементы и наборы и выбратьВычисляемое поле .
3. В окне Вставка вычисляемого поля :
В поле Имя ввести имя нового поля.
В поле Формула составить формулу, начиная со знака= и выбирая
доступные поля в списке Поля (для вставки можно использовать двойной щелчок по полю или выделить поле, затем нажать кнопкуДобавить поле ).
Нажать Добавить .
4. ОК .
Настройка внешнего вида вычисляемого поля, как и любого другого поля, происходит в окне
Параметры полей значений .
Редактирование вычисляемого поля
1. На вкладке Параметры , в группеВычисления , раскрыть списокПоля,
элементы и наборы , выбратьВычисляемое поле .