Меню

Как создать таблицу с вычисляемыми полями

Как создать таблицу с вычисляемыми полями

x

x

x

Как добавить вычисляемое поле в сводную таблицу?

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

  • Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
  • Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
  • Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
  • Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
  • Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

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

Популярные

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

док-добавить-вычисленное-поле-1

1. Щелкните любую ячейку в сводной таблице, чтобы отобразить Инструменты сводной таблицы Вкладки.

2, Затем нажмите Опции > Поля, предметы и наборы > Расчетное поле, см. снимок экрана:

док-добавить-вычисленное-поле-1

3. В Вставить вычисляемое поле В диалоговом окне введите нужное имя поля в текстовое поле Имя, см. снимок экрана :.

док-добавить-вычисленное-поле-1

4. Затем в Формула текстовое поле, удалите исходную формулу и введите = только и нажмите Цена продажи, А затем нажмите Вставить поле кнопку, чтобы вставить ваше поле в Формула box, см. снимок экрана :.

док-добавить-вычисленное-поле-1

5. После вставки Цена продажи поле и введите знак деления / , и продолжайте щелкать Число людей поле, а затем щелкните Вставить поле кнопку, вы получите полностью настраиваемое вычисляемое поле. Смотрите скриншот :.

док-добавить-вычисленное-поле-1

6. Затем нажмите Добавить в Вставить вычисляемое поле диалоговое окно, затем щелкните OK , чтобы закрыть диалоговое окно, и вы получите новый столбец поля средней продажи за исходными данными в сводной таблице.

док-добавить-вычисленное-поле-1

Внимание: В этом примере я представляю простую формулу, если вам нужны сложные вычисления, вам просто нужно вставить формулу в Формула текстовое поле, как вам нужно ..

Источник



Вычисляемое поле в Сводных таблицах в MS Excel

02 марта 2019 г.

Научимся добавлять и редактировать Вычисляемое поле в Сводной таблице MS EXCEL 2010.

Простые Сводные таблицы мы научились строить в статье Сводные таблицы в MS Excel . Теперь научимся создавать и изменять Вычисляемое поле в Сводной таблице.

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

Нашей задачей будет:

  • вычислить % выполнения плана
  • представить полученные данные по годам для каждого месяца (каждый год — отдельный столбец)

В итоге у нас должна получиться вот такая сводная таблица.

Исходная таблица

Исходную таблицу подготовим в специальном формате таблиц MS EXCEL (см. статью Таблицы в формате EXCEL 2007 ).

На основе даты продажи в столбце А, в таблице рассчитываются 2 столбца: Номер месяца =МЕСЯЦ() и Год =ГОД() . Для форматирования ячеек столбца А в виде окт11 использован пользовательский формат Даты [$-419]МММГГ;@.

Читайте также:  Электронные таблицы для поддержки принятия решения

Столбец План представляет собой линейный тренд (это не важно для целей данной статьи), столбец Продано — фактический объем продаж.

Сводная таблица

Для создания сводной таблицы выделите любую ее ячейку и в меню Вставка/ Таблицы нажмите кнопку Сводная таблица. В результате появится диалоговое окно.

Нажав ОК, сводная таблица автоматически создастся на новом листе.

В окне Список полей будут отражены названия всех столбцов исходной таблицы. Таким образом, поле — это просто столбец. Вычисляемое поле — это, по сути, вычисляемый столбец.

Перед тем как создать Вычисляемое поле перетащите поле Номер месяца в Названия строк.

Создаем вычисляемое поле

Для решения задачи нам потребуется вычислить % выполнения плана по формуле =’Продано, руб.’/’План, руб.’

Это можно сделать непосредственно в Сводной таблице , создав Вычисляемое поле ПроцентВыполнения.

Для этого выделите ячейку в Сводной таблице, в появившемся меню Работа со сводными таблицами выберите Параметры/ Вычисления/ Поля, элементы и наборы/ Вычисляемое поле :

Появится диалоговое окно:

Интерфейс этого окна не относится к интуитивно понятным вещам, поэтому требует дополнительного пояснения:

  • Вместо Поле1 введите название Вычисляемого поля, например, ПроцентВыполнения
  • В списке полей выделите поле Продано, руб. и нажмите кнопку Добавить поле или дважды кликните на него. Название поля будет введено в поле Формула
  • Введите символ деления / в поле Формула
  • В списке полей выделите поле План, руб. и нажмите кнопку Добавить поле
  • Нажмите ОК

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

После несложного форматирования Сводная таблица приобретет законченный вид (необходимо убрать ошибку #ДЕЛ/0!, изменить названия столбцов и изменить формат ячеек на процентный ).

Обратите внимание, что Сводная таблица содержит Общий итог как по столбцам, так и по строкам.

Теперь разберемся, что Вычисляемое поле нам насчитало.

Вычисляемое поле. Алгоритм расчета

Для каждого месяца у нас есть только одно значение фактических продаж (столбец Продажи) и плана. Вычисляемое поле ПроцентВыполнения возвращает значение равное их отношению. Например, для января 2012 года — это 50,19% (продано было 36992,22, а план был 73697,76). 36992,22/73697,76=0,5019 (см. строку 10 на листе Исходная таблица).

Теперь проверим итоги по месяцам. За январь итоговым значением является 93,00%. Как это значение получилось?

Сначала программа вычислила СУММУ продаж за январь по всем годам, затем, вычислила СУММУ всех плановых значений. Разделив одно на другое, было получено 93,00%. В этом можно убедиться проделав вычисления самостоятельно (см. строку 10 на листе Сводная таблица, столбцы H:J).

В этом состоит одно из ограничений Вычисляемого поля — итоговые значения вычисляются только на основании суммирования.

Аналогично расчет ведется и для итогов по столбцам: находится сумма продаж и плана по годам, затем вычисляется их отношение.

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

Чтобы обойти данное ограничение и вычислить, например, средний % выполнения плана для всех январских месяцев, придется отказаться от Вычисляемого поля. Создайте в исходной таблице новый столбец — отношение продажи к плану для каждого месяца (см. лист Исходная таблица2). Затем, создайте на ее основе другую сводную таблицу. В окне параметров полей значений установите Среднее.

В итоговом столбце теперь будет отображаться средний процент выполнения плана.

Изменяем и удаляем Вычисляемое поле

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

Источник

Работа со сводными таблицами в Excel на примерах

Пользователи создают сводные таблицы для анализа, суммирования и представления большого объема данных. Такой инструмент Excel позволяет произвести фильтрацию и группировку информации, изобразить ее в различных разрезах (подготовить отчет).

Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».

Читайте также:  Электронная таблица как электронный документ реферат

А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.

Как сделать сводную таблицу из нескольких файлов

Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).

Дальнейшая работа по созданию сводной таблицы из нескольких файлов будет зависеть от типа данных. Если информация однотипная (табличек несколько, но заголовки одинаковые), то Мастер сводных таблиц – в помощь.

Мастер сводных таблиц.

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

Гораздо сложнее сделать сводную таблицу на основе разных по структуре исходных таблиц. Например, таких:

Разнотипная структура таблицы 1. Разнотипная структура таблицы 2.

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

Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.

Но два заголовка в этих таблицах идентичны. Поэтому мы можем объединить данные, а потом создать сводный отчет.

  1. В ячейке-мишени (там, куда будет переноситься таблица) ставим курсор. Пишем = — переходим на лист с переносимыми данными – выделяем первую ячейку столбца, который копируем. Ввод. «Размножаем» формулу, протягивая вниз за правый нижний угол ячейки. Заполнение данными из другой таблицы.
  2. По такому же принципу переносим другие данные. В результате из двух таблиц получаем одну общую. Общая таблица.
  3. Теперь создадим сводный отчет. Вставка – сводная таблица – указываем диапазон и место – ОК. Создание сводной таблицы.

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

Сводный отчет по продажам.

Покажем, к примеру, количество проданного товара.

Количество проданного товара.

Можно выводить для анализа разные параметры, перемещать поля. Но на этом работа со сводными таблицами в Excel не заканчивается: возможности инструмента многообразны.

Детализация информации в сводных таблицах

Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:

Детальный отчет.

Как обновить данные в сводной таблице Excel?

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

Обновление данных.

Курсор должен стоять в любой ячейке сводного отчета.

Обновление таблицы.

Правая кнопка мыши – обновить.

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

  1. Курсор стоит в любом месте отчета. Работа со сводными таблицами – Параметры – Сводная таблица. Работа со сводными таблицами.
  2. Параметры. Настройка параметров.
  3. В открывшемся диалоге – Данные – Обновить при открытии файла – ОК. Обновить при открытии файла.

Изменение структуры отчета

Добавим в сводную таблицу новые поля:

  1. На листе с исходными данными вставляем столбец «Продажи». Здесь мы отразим, какую выручку получит магазин от реализации товара. Воспользуемся формулой – цена за 1 * количество проданных единиц. Исходные отчет по продажам.
  2. Переходим на лист с отчетом. Работа со сводными таблицами – параметры – изменить источник данных. Расширяем диапазон информации, которая должна войти в сводную таблицу. Источник данных сводной таблицы.

Если бы мы добавили столбцы внутри исходной таблицы, достаточно было обновить сводную таблицу.

После изменения диапазона в сводке появилось поле «Продажи».

Добавилось поле продажи.

Как добавить в сводную таблицу вычисляемое поле?

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

Это виртуальный столбец, создаваемый в результате вычислений. В нем могут отображаться средние значения, проценты, расхождения. То есть результаты различных формул. Данные вычисляемого поля взаимодействуют с данными сводной таблицы.

Инструкция по добавлению пользовательского поля:

  1. Определяемся, какие функции будет выполнять виртуальный столбец. На какие данные сводной таблицы вычисляемое поле должно ссылаться. Допустим, нам нужны остатки по группам товаров. Добавление пользовательского поля.
  2. Работа со сводными таблицами – Параметры – Формулы – Вычисляемое поле. Вычисляемое поле.
  3. В открывшемся меню вводим название поля. Ставим курсор в строку «Формула». Инструмент «Вычисляемое поле» не реагирует на диапазоны. Поэтому выделять ячейки в сводной таблице не имеет смысла. Из предполагаемого списка выбираем категории, которые нужны в расчете. Выбрали – «Добавить поле». Дописываем формулу нужными арифметическими действиями. Вставка вычисляемого поля.
  4. Жмем ОК. Появились Остатки. Добавилось поле остатки.
Читайте также:  Таблица название местоположение особенности арктический заповедник

Группировка данных в сводном отчете

Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

Исходная сводная таблица.

Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».

Группировать.

В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».

Шаг-годы.

Получаем суммы заказов по годам.

Суммы заказов по годам.

По такой же схеме можно группировать данные в сводной таблице по другим параметрам.

Источник

Как создать таблицу с вычисляемыми полями

x

x

x

Как добавить вычисляемое поле в сводную таблицу?

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

  • Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
  • Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
  • Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
  • Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
  • Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
  • Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
  • Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

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

Популярные

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

док-добавить-вычисленное-поле-1

1. Щелкните любую ячейку в сводной таблице, чтобы отобразить Инструменты сводной таблицы Вкладки.

2, Затем нажмите Опции > Поля, предметы и наборы > Расчетное поле, см. снимок экрана:

док-добавить-вычисленное-поле-1

3. В Вставить вычисляемое поле В диалоговом окне введите нужное имя поля в текстовое поле Имя, см. снимок экрана :.

док-добавить-вычисленное-поле-1

4. Затем в Формула текстовое поле, удалите исходную формулу и введите = только и нажмите Цена продажи, А затем нажмите Вставить поле кнопку, чтобы вставить ваше поле в Формула box, см. снимок экрана :.

док-добавить-вычисленное-поле-1

5. После вставки Цена продажи поле и введите знак деления / , и продолжайте щелкать Число людей поле, а затем щелкните Вставить поле кнопку, вы получите полностью настраиваемое вычисляемое поле. Смотрите скриншот :.

док-добавить-вычисленное-поле-1

6. Затем нажмите Добавить в Вставить вычисляемое поле диалоговое окно, затем щелкните OK , чтобы закрыть диалоговое окно, и вы получите новый столбец поля средней продажи за исходными данными в сводной таблице.

док-добавить-вычисленное-поле-1

Внимание: В этом примере я представляю простую формулу, если вам нужны сложные вычисления, вам просто нужно вставить формулу в Формула текстовое поле, как вам нужно ..

Источник

Adblock
detector