Вычисляемое поле в Сводных таблицах в MS Excel
history 2 марта 2019 г.
Научимся добавлять и редактировать Вычисляемое поле в Сводной таблице MS EXCEL 2010.
Нашей задачей будет:
В итоге у нас должна получиться вот такая сводная таблица.
Исходная таблица
Исходную таблицу подготовим в специальном формате таблиц MS EXCEL (см. статью Таблицы в формате EXCEL 2007 ).
Сводная таблица
Для создания сводной таблицы выделите любую ее ячейку и в меню Вставка/ Таблицы нажмите кнопку Сводная таблица. В результате появится диалоговое окно.
Нажав ОК, сводная таблица автоматически создастся на новом листе.
Перед тем как создать Вычисляемое поле перетащите поле Номер месяца в Названия строк.
Создаем вычисляемое поле
Для решения задачи нам потребуется вычислить % выполнения плана по формуле =’Продано, руб.’/’План, руб.’
Для этого выделите ячейку в Сводной таблице, в появившемся меню Работа со сводными таблицами выберите Параметры/ Вычисления/ Поля, элементы и наборы/ Вычисляемое поле :
Появится диалоговое окно:
Интерфейс этого окна не относится к интуитивно понятным вещам, поэтому требует дополнительного пояснения:
После проведенных манипуляций в списке поле Сводной таблицы появится еще одно поле. Завершите формирование Сводной таблицы как показано на рисунке ниже, разместив Вычисляемое поле в область Значения.
После несложного форматирования Сводная таблица приобретет законченный вид (необходимо убрать ошибку #ДЕЛ/0!, изменить названия столбцов и изменить формат ячеек на процентный ).
Обратите внимание, что Сводная таблица содержит Общий итог как по столбцам, так и по строкам.
Теперь разберемся, что Вычисляемое поле нам насчитало.
Вычисляемое поле. Алгоритм расчета
Теперь проверим итоги по месяцам. За январь итоговым значением является 93,00%. Как это значение получилось?
Сначала программа вычислила СУММУ продаж за январь по всем годам, затем, вычислила СУММУ всех плановых значений. Разделив одно на другое, было получено 93,00%. В этом можно убедиться проделав вычисления самостоятельно (см. строку 10 на листе Сводная таблица, столбцы H:J).
Аналогично расчет ведется и для итогов по столбцам: находится сумма продаж и плана по годам, затем вычисляется их отношение.
Если бы для каждого месяца в исходной таблице было бы несколько сумм продаж и плановых значений, то расчет был бы аналогичен подсчету итоговых значений.
В итоговом столбце теперь будет отображаться средний процент выполнения плана.
Изменяем и удаляем Вычисляемое поле
Вызовите тоже диалоговое окно, которое мы использовали для создания Вычисляемого поля. В выпадающем списке выберите нужное поле. Появится его формула, которую можно отредактировать, также как и название этого Вычисляемого поля.
Там же можно удалить это поле.
Еще одно ограничение
Еще одно ограничение Вычисляемого поля проявляется при попытке использовать его в качестве названия Строк или Столбцов Сводной таблицы. Этого сделать нельзя. Покажем это на нашем примере.
Изначально в исходной таблице номер месяца и года вычислялись в отдельных столбцах. Попробуем сделать эти вычисления в Вычисляемом поле.
Однако, перенести его в качестве строк сводной таблицы не получается.
Как сделать вычисляемое поле?
Как сделать запрос с вычисляемым полем в Access?
Рекомендуемый клип · 120 сек.
Как создать ВЫЧИСЛЯЕМЫЕ запросы в базе Access — YouTube
Начало рекомендуемого клипа
Конец рекомендуемого клипа
Что такое вычисляемое поле в запросе?
В отличие от вычисляемых полей таблицы, вычисляемые поля запроса в исходных таблицах базы данных новых полей не создают. При каждом выполнении запроса производятся вычисления на основе текущих значений полей. В выражениях вычисляемых полей помимо имен полей могут использоваться константы и функции.20 мар. 2015 г.
Как сделать запрос на выборку?
Создание запроса на выборку
Что служит источником данных для формы?
Форма в Access — это объект базы данных, который можно использовать для создания пользовательского интерфейса для приложения базы данных. «Связанная» форма напрямую связана с источником данных, например таблицей или запросом, и может использоваться для ввода, редактирования или отображения данных из этого источника.
Как создать вычисляемое поле в запросе?
Создание вычисляемого поля в режиме таблицы
Как создать группировку в Access?
Вы можете сортировать записи в порядке возрастания или убывания по 1–4 полям.
Как создать вычисляемое поле в сводной таблице?
Добавление вычисляемого поля
Что такое построитель выражений?
Выражения используются в Access в множестве различных контекстов и похожи на формулы в Excel. Построитель выражений обеспечивает простой доступ к именам полей и элементов управления в базе данных, а также ко многим встроенным функциям, которые можно включать в выражения.
Как добавить поле в Access?
Добавление связанного текстового поля в форму или отчет путем перетаскивания поля из области «Список полей»
Как сделать выборку?
Рекомендуемый клип · 113 сек.
Как в Excel сделать выборку из табл. по неск. условиям? — YouTube
Начало рекомендуемого клипа
Конец рекомендуемого клипа
Как создать запрос с параметрами?
Создание параметра аналогично добавлению обычного условия в запрос:
Как сделать запрос в SQL?
Рекомендуемый клип · 104 сек.
(SQL запросы) 1. Запрос на выборку данных при помощи
Начало рекомендуемого клипа
Конец рекомендуемого клипа
Как сделать форму в Аксесс?
Инструкции
Как создать кнопочную форму?
Рекомендуемый клип · 114 сек.
Как создать главную кнопочную форму в Microsoft Access за 9
Начало рекомендуемого клипа
Конец рекомендуемого клипа
Как создать форму для заполнения базы данных?
Рекомендуемый клип · 119 сек.
Как создать формы в Microsoft Access за 10 минут — YouTube
Начало рекомендуемого клипа
Конец рекомендуемого клипа
Как создать перекрестный запрос в Access?
Рекомендуемый клип · 112 сек.
Как быстро создать ПЕРЕКРЕСТНЫЙ запрос в базе данных
Начало рекомендуемого клипа
Конец рекомендуемого клипа
Работа с вычисляемыми полями
Для вычисляемого поля можно определить заголовок, ограничение доступности, выражение представления, выражения упорядочивания, тип значения, доступные значения и оформление также как для полей наборов данных. Кроме того, вычисляемое поле может быть определено как ресурс, для этого необходимо добавить данное поле в список ресурсов и указать выражение, по которому будет вычисляться итог.
Пример
В приведенном запросе получаются данные из регистра продаж и регистра планов продаж. Результатом запроса будет таблица, где для каждого подразделения и каждой номенклатуры будет получено плановое и фактическое количество продаж.
Определим вычисляемое поле. Для этого перейдем в конструкторе схемы компоновки данных на вкладку Вычисляемые поля и добавим поле.
Результат отчета будет выглядеть так:
| Номенклатура | Подразделение | Стоимость план | Стоимость факт | Перевыполнение плана |
| Вентилятор настольный | Отдел оптовой продажи | 238,32 | 737,55 | 209,48 |
| Мужские сапоги с искусственным мехом | Отдел оптовой продажи | 234,24 | -100,00 | |
| Мишка (конфеты) | Отдел розничной продажи | 114,91 | Не запланировано |
Т.е. для каждой строчки было рассчитано значение вычисляемого поля.
Теперь в итог отчета будет выводиться сумма по полям, в том числе и по полю ПеревыполнениеПлана :
| Номенклатура | Подразделение | Стоимость план | Стоимость факт | Перевыполнение плана |
| Вентилятор настольный | Отдел оптовой продажи | 238,32 | 737,55 | 209,48 |
| Мужские сапоги с искусственным мехом | Отдел оптовой продажи | 234,24 | -100,00 | |
| Мишка (конфеты) | Отдел розничной продажи | 114,91 | Не запланировано | |
| Итого | 472,56 | 852,46 | 109,48 | |
Для того чтобы получить правильный итог по выполнению плана изменим формулу расчета итога по полю Выполнение плана :
В этой формуле уже получается не сумма отношений факта к плану, а отношения суммы факта к сумме плана. Кроме того, в итоге учитываются только те записи, для которых был установлен план.
Введем данную формулу в выражение ресурса:
Теперь результат отчета будет выглядеть так:
Создание вычисляемых элементов управления
Для отображения результатов вычислений в формах и отчетах в базах данных Access можно использовать вычисляемые элементы управления. Например, если в отчете отображается количество проданных товаров и цена каждого товара, можно добавить вычисляемую текстовое поле, в которое перемножатся эти два поля, чтобы отобразить общую цену. Свойство «Данные» вычисляемого текстового поля содержит выражение, в которое умножаются два поля (количество элементов, умноженное на цену за единицу), чтобы получить результат.
В этой статье
Создание вычисляемых элементов управления
Эта процедура позволяет создать вычисляемую дес.в., не используя мастер.
В области навигации щелкните форму или отчет правой кнопкой мыши и выберите «Конструктор».
На вкладке «Конструктор» в группе «Элементы управления» выберите нужный тип элементов управления.
Список типов элементов управления, которые можно использовать в качестве вычисляемого, см. в разделе «Узнайте, какие типы элементов управления можно использовать в качестве вычисляемого.»
Наведите указатель мыши на форму или отчет и щелкните форму или отчет, чтобы вставить его.
Если запускается мастер управления, нажмите кнопку «Отмена», чтобы закрыть его.
Выберите его, нажмите F4, чтобы отобразить окно свойств, а затем введите выражение в поле свойства «Control Source». Чтобы использовать Построитель выражений для создания выражения, щелкните 
рядом с полем свойства «Control Source».
Переключиться в представление формы или отчета и убедитесь, что вычисляемая система управления работает так, как вы ожидаете.
Перед каждым выражением должен быть оператор =. Например: =[UnitPrice]*.75.
Дополнительные сведения о создании выражений см. в статье Создание выражений.
Если вам нужно больше места для ввести выражение в поле свойства «Control Source», нажмите shift+F2, чтобы открыть окно «Масштаб».
Если форма или отчет основаны на запросе, вы можете поместить выражение в запрос, а не в вычисляемый элемент управления. Это повышает производительность и, если вы собираетесь вычислять итоги для групп записей, проще использовать имя записи вычисляемое поле в агрегатная функция.
При сортировке вычисляемого объекта управления в форме или отчете убедитесь, что свойство Format (Формат) этого объекта задано соответствующим образом. В противном случае вычисляемая числовая или дата может отсортироваться по алфавиту, а не по числу.
Изменение связанного управления на вычисляемой
Чтобы создать вычисляемую рамку, можно сначала создать связанный (например, перетащив поле из области «Список полей» в форму или отчет), а затем отредактировать его свойство «Источник», чтобы создать выражение. Это работает хорошо, если имя этого управления не конфликтует ни с одним из имен полей, содержащихся в выражении. Дополнительные сведения о создании связанного управления путем перетаскиния поля из области «Список полей» см. в статье «Добавление поля в форму или отчет».
Если связанный контроль уже создан, воспользуйтесь процедурой ниже, чтобы сделать его вычисляемой и избежать конфликтов имен.
В области навигации щелкните форму или отчет правой кнопкой мыши и выберите «Конструктор».
Щелкните поле, который вы хотите изменить, и нажмите F4, чтобы открыть лист свойств.
Если свойство «Имя» на вкладке «Все» в окне свойств соответствует свойству «Источник управления», отредактируем его так, чтобы оба свойства не совпадали. Убедитесь, что имя не является зарезервированным словом или именем другого поля базы данных. Дополнительные сведения о зарезервированных словах см. в статье о зарезервированных словах и символах Access.
Отредактируем строку в поле свойства «Данные», чтобы она содержала нужное выражение.
Чтобы сохранить изменения, нажмите сочетание клавиш CTRL+S.
Переключение в представление макета или формы и убедитесь, что вычисляемая система управления работает так, как вы ожидаете.
Если в средстве управления не отображаются нужные данные (например, если Access отображает #Name? в этом средстве), проверьте источник записей формы или отчета, чтобы убедиться, что все поля, используемые в выражении, доступны. Если источником записей является запрос, для работы выражения может потребоваться добавить в запрос одно или несколько полей.
Перед каждым выражением должен быть оператор =. Например: =[UnitPrice]*.75.
Если вам нужно больше места для ввести выражение в поле свойства «Control Source», нажмите shift+F2, чтобы открыть окно «Масштаб».
Если форма или отчет основаны на запросе, вы можете поместить выражение в запрос, а не в вычисляемый элемент управления. Это повышает производительность и, если вы собираетесь вычислять итоги для групп записей, проще использовать имя записи вычисляемое поле в агрегатная функция.
При сортировке вычисляемого объекта управления в форме или отчете убедитесь, что свойство Format (Формат) этого объекта задано соответствующим образом. В противном случае вычисляемая числовая или дата может отсортироваться по алфавиту, а не по числу.
Узнайте, какие типы элементов управления можно использовать в качестве вычисляемого
Текстовые поля являются наиболее популярным вариантом для вычисляемого управления, так как в них может отображаться большое количество различных типов данных. Тем не менее любой объект управления, который имеет свойство «Исходный объект», можно использовать в качестве вычисляемой. Во многих случаях не имеет смысла использовать определенный тип управления в качестве вычисляемого, так как его нельзя обновить так, как вы можете обновить связанный или связанный. Например, если вы поместите в форму поле «поле с квадратиконом» и введите выражение в его свойство «Источник управления», то его больше нельзя будет выбрать или очистить, щелкнув его. На основе результатов выражения этот поле будет выбрано или очищено. Если вы щелкнули этот поле, в панели состояния Access отобразилось оповещение «Не может быть изменено» —оно привязано к >. Однако в отчете может быть полезно использовать элемент управления «квадрат» на основе результатов вычисления, так как элементы управления в отчетах используются только для отображения данных.
