Добавить вычисляемое поле в сводную таблицу excel
Обновлено: 04.07.2024
Сводные таблицы – одно из мощных средств обработки табличных массивов. По данным исходной таблицы, построим отчет сводной таблицы, где вычислим по каждому наименованию общее количество и количество брака.
Построение такого отчета, как правило, не вызывает трудностей у любого пользователя хоть немного знакомым со сводными таблицами.
Однако, если возникает необходимость вычислить какую долю составляет количество брака от общего количества по каждому наименованию, то здесь многие пользователи сталкиваются с трудностями, так как в исходной таблице таких данных нет и получается, что требуется сделать вычисления непосредственно в отчете сводной таблицы.
Для решения этой задачи требуется создать вычисляемое поле.
На вкладке Анализ [Options] (в 2010 версии вкладка Параметры [Options]), в группе Вычисления [Calculations], раскрыть список Поля, элементы и наборы [Fields, Items, &Sort] и выбрать Вычисляемое поле [Calculated Field]:
- В поле Имя [Name] ввести имя нового поля.
- В поле Формула [Formula] составить формулу, начиная со знака = и выбирая доступные поля в списке Поля [Fields] (для вставки можно использовать двойной щелчок по полю или выделить поле и нажать кнопку Добавить поле [Insert Field]).
- Нажать Добавить [Add], ОК.
Настройка внешнего вида вычисляемого поля, как и любого другого поля, происходит в окне Параметры полей значений [Value Field Settings], где нужно установить процентный формат.
Преимущество этого метода заключается в том, что можно изменять структуру сводной таблицы и не беспокоиться об ошибках в формулах или нарушении ссылок на ячейки.
Оставьте комментарий!
Прекрасно! А это есть во всех версиях Excel?
Как узнать, есть ли в отчете вычисляемые поля?
вычисляемы поля нужно обновлять?
функции в вычисляемых полях представлены очень-очень бедно
Женя, вычисляемые поля не требуют обновления сами со себе, требует обновлений сами данные сводной таблицы
теперь и я знаю 🙂
Для простых вычислений очень удобно! Подтверждаю!
Вот оказывается как это делается. А я в сторонке подсчитываю 🙂 Теперь буду правильно. Благодарю!
Можно ли сделать так, чтобы другие пользователи не видели, как были созданы эти вычисляемые поля?
Если поставить защиту листа, то вообще никто ничего не увидит. Правда и со сводной работать не сможет 🙂
Можно ли сделать так, чтобы другие не видели созданные вычисляемые поля?
Светлана, стандартными средствами программы, можно сделать защиту листа. После чего любые манипуляции со сводными таблицами будут недоступны.
Когда вы создали сводную таблицу на основе исходных данных, а иногда вы все еще надеетесь, что данные сводной таблицы могут выполнять дальнейшие вычисления для получения дополнительной информации. В этом случае Расчетное поле Функция может помочь вам добавить настраиваемое вычисляемое поле по своему усмотрению и не будет предупреждать исходные данные. Чтобы узнать больше об этой функции, прочтите эту статью.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Добавить вычисляемое поле в сводную таблицу в Excel
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Предположим, у меня есть следующая сводная таблица, и теперь я хочу рассчитать среднюю продажу на человека в каждой компании. В этом примере я покажу вам, как применить функцию вычисляемого поля для решения этой задачи.
1. Щелкните любую ячейку в сводной таблице, чтобы отобразить Инструменты сводной таблицы Вкладки.
2, Затем нажмите Параметры > Поля, предметы и наборы > Расчетное поле, см. снимок экрана:
3. В Вставить вычисляемое поле В диалоговом окне введите нужное имя поля в текстовое поле Имя, см. снимок экрана :.
4. Затем в Формула текстовое поле, удалите исходную формулу и введите = только и нажмите Цена продажи, затем нажмите Вставить поле кнопку, чтобы вставить ваше поле в Формула box, см. снимок экрана :.
5. После вставки Цена продажи поле и введите знак деления / , и продолжайте щелкать Число людей поле, а затем щелкните Вставить поле кнопку, вы получите полностью настраиваемое вычисляемое поле. Смотрите скриншот :.
6. Затем нажмите Добавить в Вставить вычисляемое поле диалоговое окно, затем щелкните OK , чтобы закрыть диалоговое окно, и вы получите новый столбец поля средней продажи за исходными данными в сводной таблице.
Внимание: В этом примере я представляю простую формулу, если вам нужны сложные вычисления, вам просто нужно вставить формулу в Формула текстовое поле, как вам нужно ..
Перед созданием вычисляемого поля необходимо открыть или сгенерировать сводную таблицу. В нашем примере используется сводная таблица, показанная на рис. 5.6.
Теперь, когда в нашем распоряжении имеется сводная таблица, наступило время для создания, вычисляемого поля. Чтобы создать вычисляемое поле, активизируйте диалоговое окно Вставка вычисляемого поля (Insert Calculated Field).
Рис. 5.6. Создайте сводную таблицу, в которую будет добавляться вычисляемое поле
Рис. 5.7. Создание вычисляемого поля начинается с этого раскрывающегося меню
На экране появится диалоговое окно Вставка вычисляемого поля (Insert Calculation Field), как показано на рис. 5.8.
Рис. 5.8. Диалоговое окно Вставка вычисляемого поля поможет в создании вычисляемого поля для сводной таблицы
В верхней части диалогового окна имеются два текстовых поля: Имя (Name) и Формула (Formula). В этих полях следует задать имя вычисляемого поля и создать формулу, указав необходимые поля данных и математические операторы. На рис. 5.9 показано, что вычисляемому полю присвоено описательное имя Средняя выручка за час. Это имя должно точно характеризовать тип выполняемой математической операции.
Рис. 5.9. Выбор для вычисляемого поля описательного имени
Далее перейдите к списку Поля (Fields) и дважды щелкните на поле Объем продаж. Введите символ косой черты /, чтобы указать программе Excel, что вы будете делить значение поля Объем продаж на другой элемент.
По умолчанию текстовое поле Формула диалогового окна Вставка вычисляемого поля содержит выражение = 0. Перед вводом собственной формулы следует удалить нуль.
С этого момента диалоговое окно должно выглядеть так, как показано на рис. 5.10.
Закончите ввод формулы, дважды щелкнув на поле Период продаж (в часах). Готовая формула показана на рис. 5.11.
Щелкните на кнопке Добавить (Add), а затем — на кнопке ОК для активизации нового вычисляемого поля. Как видно на рис. 5.12, в результате вы получите вычисляемое поле внутри сводной таблицы.
Рис. 5.12. Вы успешно добавили вычисляемое поле в сводную таблицу. Теперь можно изменять настройки этого нового поля точно так же, как и параметры любого другого поля (например, имя поля, числовой формат или цвет)
Обратите внимание на то, что после создания нового поля Средняя выручка за час оно добавляется в список полей.
Результат, полученный с помощью вычисляемого поля, не форматируется. При желании его можно отформатировать с помощью методики, описанной в разделе Создание базовой сводной таблицы.
Означает ли это, что вы добавили столбец в источник данных? Нет. Вычисляемые поля похожи на элементы Промежуточная сумма (Subtotal) и Общая сумма (Grand Total) сводной таблицы, задаваемые по умолчанию, так как все они являются математическими функциями, которые выполняют перерасчет данных при изменении или обновлении сводной таблицы. Вычисляемые поля просто имитируют строго заданные поля в источнике данных. Можете перетаскивать их, изменять настройки полей, а также использовать вместе с другими вычисляемыми полями.
Взгляните внимательно на рис. 5.11. Обратите внимание на то, что формат введенной вами формулы похож на используемый в стандартной строке формул Excel. Очевидное различие состоит в том, что вместо использования строгих ссылок на ячейки или числовых значений вы ссылаетесь на поля данных сводной таблицы для определения аргументов, применяемых в расчетах. Если вы уже работали с формулами в Excel, то быстро усвоите концепции создания вычисляемых полей.
Сводная таблица, являющаяся самой известной функцией Excel, включает в себя множество опций для управления данными желаемым образом. После создания сводной таблицы из исходных данных вы можете добавлять поля и элементы, не изменяя исходные данные, что очень удобно для быстрых вычислений. В этом посте мы продемонстрируем добавление вычисляемого поля в сводную таблицу.
Для начала запустите электронную таблицу Excel 2010, содержащую сводную таблицу. Для иллюстрации источник данных сводной таблицы содержит поля; Название курса, количество изученных (количество студентов), общее количество полученных баллов и общее количество баллов.
Сводная таблица, созданная из вышеупомянутого источника данных, очень похожа, за исключением предварительно оцененного общего итога.
Теперь в сводной таблице нам нужно вставить новое поле без вставки поля в источник данных, которое будет оценивать процент общих оценок, полученных студентами. Для этого перейдите на вкладку «Параметры работы со сводными таблицами» в группе «Расчет» в разделе Поля, предметы и наборы параметры, щелкните Расчетные поля
Откроется диалоговое окно «Вставить вычисляемое поле», введите соответствующее имя вычисляемого поля. В Формуле введите формулу для оценки расчетного поля.
При вычислении процентов мы введем простую процентную формулу, которая включает Всего полученных баллов поле и Всего оценок поле. Чтобы быстро добавить имя поля на панели ввода формул, дважды щелкните имя поля под Поля коробка. После ввода формулы нажмите «Добавить», а затем закройте диалоговое окно.
В результате в сводную таблицу будет добавлено поле «Процент», содержащее проценты от соответствующих полученных итоговых оценок.
Вы также можете ознакомиться с нашими ранее рассмотренными руководствами о том, как рассчитать рабочие дни в Excel 2010 и Как создать настраиваемое правило условного форматирования в Excel 2010.
Читайте также: