Как создать вычисляемое. Создание вычисляемого поля в форме

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

Рис. 1. Необходимо создать в имеющейся сводной таблице вычисляемое поле Средняя выручка за час , которое придаст новый смысл анализируемым данным

Скачать заметку в формате или , примеры в формате

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

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

Теперь посмотрите на рис. 5.1 и спросите себя: «Зачем понадобилось добавлять вычисляемые поля? Почему бы не использовать обычные формулы ячеек или не провести необходимые вычисления непосредственно в исходной таблице для получения требуемой информации?» Чтобы получить ответы на эти вопросы, рассмотрим различные методы, которые можно использовать при создании вычисляемого поля, показанного на рис.1.

Способ 1. Добавление вычисляемого поля в источник данных

Можно добавить вычисляемое поле в источник данных, как показано на рис. 2, позволив сводной таблице использовать это поле как стандартное поле данных. Если расположение информации на листе с источником данных позволяет это сделать, то какое решение может быть наилучшим. Кстати, добавление нового столбца потребует не просто обновить сводную таблицу, а изменить область, на которой основана сводная. Для этого кликните на сводной и пройдите по меню Анализ → Источник данных → Источник данных .

Я стараюсь превратить исходные данные в Таблицу (что сделано на рис. 2 и в прилагаемом Excel-файле). При этом, во-первых, выделяются заголовки, во-вторых, появляются кнопки с фильтрами, что часто полезно для исследования данных, в-третьих, строки окрашиваются через одну, и, наконец, самое важное, – при добавлении строк и столбцов не требуется менять область данных для сводной таблицы. В качестве таковой области указывается не прямоугольный диапазон, а имя Таблицы. В нашем случае – Источник (рис. 3).

Рис. 3. Формирование сводной на основе специального инструмента Excel– Таблица

Но такой способ имеет и недостатки: во-первых, не всегда удобно добавить столбец в исходные данные, во-вторых, ограничиваются возможности в случае изменения структуры исходных данных (например, вы экспортируете данные из 1С, и в новом экспорте на один столбец больше; этот новый столбец затрет ваш вычислительный столбец).

Способ 2. Использование формулы вне сводной таблицы для создания вычисляемого поля

Можно добавить вычисляемое поле рядом со сводной таблицей. На рис. 4 каждая ячейка в столбце Средняя выручка за час снабжена формулой, ссылающейся на сводную таблицу. Кстати, если при попытке сослаться на ячейку сводной таблицы у вас в формуле «вылазит» функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, то вы не сможете «протащить» формулу. Чтобы преодолеть это затруднение ознакомьтесь с заметкой .

Рис. 4. При вводе формулы после создания сводной таблицы вы по сути добавляете вычисляемое поле, которое изменяется при обновлении самой сводной таблицы

Несмотря на то что этот метод обеспечивает добавление вычисляемого поля, которое обновляется при обновлении сводной таблицы, любые изменения в структуре сводной таблицы могут привести к тому, что формула станет бесполезной. Например, в результате перетаскивания поля Рынок сбыта в область ФИЛЬТРЫ списка полей сводной таблицы структура отчета изменяется, в результате чего в вычисляемом поле появляется сообщение об ошибке (рис. 5). Если же добавить еще одно поле в область сводной таблицы КОЛОННЫ, столбец Средняя выручка за час будет затерт.

Способ 3. Непосредственная вставка вычисляемого поля в сводную таблицу

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

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

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

Рис. 6. Созданное ранее вычисляемое поле остается «действительным» даже при изменении структуры сводной таблицы, приводящем к отображению средней выручки за час для каждой услуги и каждого рынка сбыта

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

Перед созданием вычисляемого поля необходимо открыть или сгенерировать сводную таблицу. В нашем примере используется сводная таблица, показанная на рис. 1, но без столбца D. Чтобы создать вычисляемое поле, активизируйте диалоговое окно Вставка вычисляемого поля . Для этого кликните на сводной таблице, чтобы активизировать группу контекстных вкладок Работа со сводными таблицами . Перейдите на вкладку Анализ , в группу Вычисления , щелкните на кнопке Поля, элементы и наборы и выберите в меню команду Вычисляемое поле (рис. 7).

На экране появится диалоговое окно Вставка вычисляемого поля (рис. 8). В верхней части диалогового окна имеются два текстовых поля: Имя и Формула . В этих полях следует задать имя вычисляемого поля и создать формулу, указав необходимые поля данных и математические операторы. Вычисляемому полю присвоено описательное имя Средняя выручка за час . Это имя следует выбирать таким образом, чтобы точно охарактеризовать тип выполняемой математической операции. По умолчанию текстовое поле Формула диалогового окна Вставка вычисляемого поля содержит выражение = 0. Перед вводом формулы следует удалить нуль. Выберите одно из полей в области Поля ; в нашем случае – Объем продаж и кликните Добавить поле . Название поле появится в строке Формула . Введите знак деления /, а затем выберите второе поле – Период продаж (в часах) . Кликните Добавить , а затем ОК для активизации нового вычисляемого поля.

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

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

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

Создание вычисляемых элементов

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

В сводной таблице, показанной на рис. 9, указывается общий объем продаж для определенных отчетных периодов (поле Отчетный период ). Представьте себе, что нужно сравнить средний объем продаж последних шести периодов со средним объемом продаж предыдущих семи периодов. Если быть более точным, то нам требуется определить среднее для периодов Р01–Р07 и сравнить его со средним для периодов Р08–Р13.

Поместите указатель в любую ячейку поля Отчетный период, перейдите на контекстную вкладку ленты Анализ в группу Вычисления, и щелкните на кнопке Поля, элементы и наборы . В открывшемся меню выберите команду Вычисляемый объект (рис. 10).

Откроется диалоговое окно (рис. 11). Обратите внимание на то, что в верхней части диалогового окна указано, с каким полем вы работаете. В данном случае это поле Отчетный период . Кроме того, список содержит все элементы поля Отчетный период . Присвойте вычисляемому элементу имя, а затем создайте формулу, указав необходимую комбинацию элементов данных и операторов, которые обеспечат правильный результат. Формула вводится в поле Формула , а необходимые элементы данных, применяемые в ней, выбираются в списке Элементы . Щелкните на кнопке ОК , и вычисляемый элемент добавится в сводную таблицу (рис. 12).

Рис. 11. Диалоговое окно Вставка вычисляемого элемента

Аналогичным образом создайте вычисляемый элемент, который будет представлять средний объем продаж для периодов Р08–Р13. После этого скройте отдельные отчетные периоды, оставив только два вычисляемых элемента. После небольшого форматирования наши вычисляемые элементы будут применяться для сравнения средних объемов продаж за предыдущие и последующие шесть месяцев (рис. 13).

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

Преимущества и недостатки вычислений в сводных таблицах

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

Приоритет выполнения операций. Как и в электронных таблицах, в формулах вычисляемых элементов и полей можно использовать любой оператор: +, –, *, /, %, ^ и т.п. Более того, как и в электронных таблицах, вычисления в сводных таблицах выполняются в соответствии с приоритетом операторов. При выполнении вычисления, в котором комбинируются несколько операторов, например (2+3) *4/50%, Excel оценивает выражение и проводит расчет в определенном порядке. Знание этого порядка убережет вас от многих ошибок.

Итак, порядок выполнения операций в Excel следующий:

обработка выражений в круглых скобках;
обработка диапазонов (:);
обработка пересечений (областей);
обработка объединений (;);
выполнение операции отрицания;
преобразование процентных значений (например, 50% преобразуется в 0,50);
возведение в степень (^);
умножение (*) и деление (/); эти операции имеют равный приоритет;
сложение (+) и вычитание (–); эти операции имеют равный приоритет;
объединение текстовых данных (&) эта операция называется также конкатенацией;
выполнение операций сравнения (=, <>, <=, >=).

Равнозначные операторы в одном выражении всегда выполняются в порядке следования (слева направо).

Рассмотрим простой пример. Как известно, выражение (2+3)*4 возвращает результат 20. Если же вы удалите скобки и оставите выражение 2+3*4, то Excel вычислит: 3*4 = 12 + 2 = 14.

Рассмотрим другой пример. Если вы введете в качестве формулы выражение 10^2, т.е. укажете возвести число 10 в квадрат, то программа вернет в качестве ответа значение 100. Если же вы введете выражение –10^2, то можете ожидать возврата значения –100. Однако Excel опять вернет значение 100. Причина в том, что программа выполняет операцию отрицания перед возведением в степень, т.е. значение 10 вначале преобразуется в –10, а результат выражения –10*–10 действительно равен 100. Использование круглых скобок в формуле –(10^2) гарантирует, что программа вначале выполнит возведение в степень и только потом операцию отрицания, вернув ожидаемый результат –100.

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

Функции рабочих листов. Можно применять любую функцию рабочего листа, не использующую в качестве аргументов ссылки на ячейки или именованные объекты. В действительности можно использовать любую функцию рабочего листа, не требующую обязательного применения ссылок на ячейки или именованные объекты. В эту категорию попадают такие функции, как СЧЁТ, СРЗНАЧ, ЕСЛИ, И, НЕТ и ИЛИ.

Константы. В вычислениях, выполняемых в сводных таблицах, можно использовать любые константы. Константы представляют собой статические значения, которые не изменяются во времени. Например, в следующей формуле число 5 является константой: [Продано единиц] *5. Несмотря на то что значение Продано единиц может изменяться на основе доступных данных, число 5 всегда будет одним и тем же.

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

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

Рис. 14. Несмотря на то что вычисляемое поле справедливо для отдельных элементов данных, для промежуточных сумм его применять математически некорректно

В каждом квартале вам нужно получить итоговые объемы продаж для каждого товара, для чего количество проданных единиц товара умножается на цену единицы этого товара. Если вы вначале просмотрите данные за первый квартал, то сразу же определите проблему. Вместо расчета суммы 220+150+220+594, которая равна 1184, сумма количества единиц товара умножается на сумму цен на товары, в результате чего возвращается неверное значение. К сожалению, эту проблему решить невозможно, но ее можно обойти. Исключите промежуточные и итоговые суммы из сводной таблицы, а затем вычислить новую итоговую сумму внизу за пределами сводной.

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

Управление вычислениями и их поддержка в сводных таблицах

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

Изменение и удаление вычислений в сводных таблицах. При изменении параметров вычислений или отсутствии необходимости в вычисляемом поле или вычисляемом элементе можно открыть соответствующее диалоговое окно, чтобы изменить или удалить вычисление. Активизируйте диалоговое окно Вставка вычисляемого поля или Вставка вычисляемого элемента (см. описание к рис. 7 и 10) и выберите в нем раскрывающийся список Имя (рис. 15).

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

Рис. 16. Откройте диалоговое окно Порядок выполнения вычислений

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

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

Рис. 18. Команда Вывести формулы позволяет легко и быстро документировать имеющиеся в сводной таблице вычисления

Заметка написана на основе книги Джелен, Александер. . Глава 5.

Слово Таблица написано с большой буквы, так как это не просто таблица, а отдельный инструмент Excel.

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

Чтобы добавить к отчету вычисляемое поле "Сумма":

Откройте отчет "Товары на складе" в режиме Конструктора.
На панели элементов нажмите кнопку Надпись (Label) и поместите новую надпись справа от надписи "На складе" (Units in Stock) в разделе верхнего колонтитула. Введите в качестве текста надписи слово "Сумма" (Sum).
Чтобы создать вычисляемое поле "Сумма" (Sum), нажмите кнопку Поле (Text Box) на панели элементов и добавьте текстовое поле справа от текстового поля "НаСкладе" (UnitsInStock) в разделе данных. Удалите метку нового текстового поля.
Щелкните внутри текстового поля и введите в качестве его значения выражение:
= [НаСкладе]* [Цена] (= * ).
Откройте окно свойств поля, дважды щелкнув по нему, и на вкладке Макет (Format) в поле со списком свойства Формат поля (Format) выберите значение Денежный (Currency).

Замечание

Ввести выражение в текстовое поле можно несколькими способами: выделите его и нажмите комбинацию клавиш +, чтобы войти в режим редактирования содержимого поля, или сделайте двойной щелчок по полю для отображения диалогового окна Свойства (Properties), а затем введите выражение как значение свойства Данные (Source).

Рис. 6.27.

Повторите шаги 3 и 4 для того, чтобы создать текстовое поле в разделе примечаний группы "Категория" (Category), но в качестве выражения для вычисления промежуточного значения следует ввести: =Sum([НаСкладе] * [Цена]) (=Sum( * )). Нажмите кнопку Полужирный (Bold) на панели форматирования, чтобы установить полужирный шрифт для текста в новом поле. Откройте диалоговое окно Свойства (Properties) и раскройте в нем вкладку Другие (Other), в поле Имя (Name) введите имя текстового поля "Сумма" (Sum). Затем раскройте вкладку Макет (Format) и в поле со списком свойства Формат поля (Format) выберите значение Денежный (Currency).
Поскольку ширина области данных нашего отчета увеличилась, уменьшите ширину полей страницы, чтобы все поля поместились на одной странице. Для этого выберите команду Файл, Параметры страницы (File, Page Setup) и задайте вместо ширины левого и правого полей, заданных по умолчанию, значение 10 мм (см. разд. "Просмотр и печать отчетов" в этой главе).
Нажмите кнопку Вид (View) на панели инструментов Конструктор отчетов (Report Design), чтобы оценить результаты своей работы. Отчет будет выглядеть так, как показано на рис. 6.27. К прежним данным добавился крайний правый столбец со значениями стоимости каждого товара в имеющемся количестве на складе и промежуточных итоговых сумм по стоимости товаров для каждой категории.

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

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

Задача 1. В таблице ТОВАР имеются поля ЦЕНА и СТАВКА_НДС, вычислите цену с учетом НДС и сравните ее с полученной в вычисляемом поле таблицы Цена с НДС.

Создайте в режиме конструктора запрос на выборку для таблицы ТОВАР. Перетащите в бланк запроса поля НАИМ_ТОВ, ЦЕНА, СТАВКА_НДС и Цена с НДС (рис. 4.6).
Для подсчета цены с учетом НДС создайте вычисляемое поле, записав в пустой ячейке строки Поле (Field) выражение [ЦЕНА]+[ЦЕНА]*[СТАВКА_НДС].
Для отбора записей со значением выше 5000 в вычисляемом поле в строку Условие отбора (Criteria) введите > 5000
После ввода выражения система по умолчанию формирует имя вычисляемого поля Выражение 1, которое становится заголовком столбца в таблице с результатами выполнения запроса. Это имя вставится перед выражением [ЦЕНА]+[ЦЕНА]*[СТАВКА_НДС]. Для каждого нового вычисляемого поля в запросе номер выражения увеличивается на единицу. Имя вычисляемого поля отделяется от выражения двоеточием. Для изменения имени установите курсор мыши в вычисляемом поле бланка запроса и нажмите правую кнопку мыши. В контекстно-зависимом меню выберите Свойства (Properties) поля и в строку Подпись (Caption) введите новое имя поля ― Цена с НДС1 . Теперь в таблице с результатами выполнения запроса в заголовке вычисляемого столбца отобразится это имя. Имя поля может быть исправлено также непосредственно в бланке запроса.
Для отображения результата выполнения запроса щелкните на кнопке Выполнить (Run) в группе Результаты (Results). Вычисляемое поле таблицы и за-проса имеют одинаковые значения.
Измените в одной из записей запроса цену товара. Значения в обоих вычисляемых полях будут моментально пересчитаны.
Для формирования сложного выражения в вычисляемом поле или условии отбора целесообразно использовать построитель выражений. Построитель позволяет выбрать необходимые в выражении имена полей из таблиц, запросов, знаки операций, функции. Удалите выражение в вычисляемом поле и используйте построитель для его формирования.
Вызовите построитель выражений (Expression Builder), нажав кнопку Построитель (Builder) в группе Настройка запроса (Query Setup) ленты Конструктор (Design), или выбрав Построить (Build) в контекстно-зависимом меню. Курсор мыши должен быть установлен предварительно в ячейке ввода выражения.
В левой части окна Построитель выражений (Expression Builder) (рис. 4.7) выберите таблицу ТОВАР, на которой построен запрос. Справа отобразится список ее полей. Последовательно выбирайте нужные поля и операторы, двойным щелчком вставляя в выражение. Выражение сформируется в верхней части окна. Обратите внимание, построитель перед именем поля указал имя таблицы, которой оно принадлежит, и отделил его от имени поля восклицательным знаком.
Завершите процесс построения выражения в вычисляемом поле, щелкнув на кнопке ОК.
Сохраните запрос под именем ― Цена с НДС и закройте его.
Выполните сохраненный , выделив его в области навигации и выбрав в контекстном меню команду Открыть (Open).

Задача 2. В вычисляемых полях и условиях отбора можно использовать встроенные функции. В Access определено более 150 функций.
Пусть необходимо выбрать все накладные, по которым производилась отгрузка в заданном месяце. В НАКЛАДНАЯ дата отгрузки хранится в поле ДАТА_ОТГ с типом данных Дата/время (Date/Time).

Создайте в режиме конструктора запрос на выборку для таблицы НАКЛАДНАЯ. Перетащите в бланк поля НОМ_НАКЛ и КОД_СК (рис. 4.8).
Создайте вычисляемое поле в пустой ячейке строки Поле (Field), записав туда одно из выражений: Format([НАКЛАДНАЯ]![ДАТА_ОТГР];»mmmm») ― эта функция возвратит пол-ное название месяца
или Format([НАКЛАДНАЯ]![ДАТА_ОТГР];»mm») ― эта функция возвратит номер месяца.
Для отбора накладных, выписанных в заданном месяце, в вычисляемом поле в строку Условие отбора (Criteria) введите название месяца, например март (рис. 4.8), или номер месяца, например 3 в соответствии с параметром в функции Format.
Выполните запрос, нажав кнопку Выполнить (Run) в группе Результаты (Results) на вкладке ленты Работа с запросами | Конструктор (Query Tools | Design).
Запишите в вычисляемом поле функцию Month(НАКЛАДНАЯ!ДАТА_ОТГ), и убедитесь, что эта функция возвращает выделенный из даты номер месяца.
Для выборки всех строк, относящихся ко второму кварталу, в строку Условие отбора (Criteria) введите оператор Between 4 And 6, определяющий, попадает ли значение выражения в указанный интервал.
Запишите в вычисляемом поле выражение MonthName(Month(НАКЛАДНАЯ!ДАТА_ОТГ)) и убедитесь, что функция MonthName преобразует номер месяца в его полное на-звание.

Для закрепления смотрим видеоурок.

Известно, что в БД Microsoft Access вычисляемые поля могут быть созданы в запросе, форме или отчете. Рассмотрим создание вычисляемых полей в отчете базы данных Access 2003. Для ввода Выражения в форму ее необходимо открыть в режиме Конструктора и создать свободное текстовое поле, используя кнопку "ab " (Поле) на панели инструментов. Слева от созданного свободного поля располагается метка, связанная с текстовым полем, в которой отображается порядковый номер введенного поля.

Свободное поле означает, что оно не связано ни с одним полем. В свободное поле можно вводить поле таблицы БД или Выражение. Метка используется для ввода имени поля. При вводе в свободное поле "Выражения", которое вычисляет требуемое значение, оно становится вычисляемым. В вычисляемых полях можно использовать функции, например функцию дата или время, а можно ввести выражение, использующее в качестве входных данных значения полей таблицы БД Access. На форму можно также вставить автономную текстовую метку, т.е. метку не связанную с текстовым полем, для этого надо использовать кнопку "Aa " (Надпись) на панели инструментов.

Рассмотрим создание вычисляемого поля в форме Студенты на примере базы данныхTraining_students_VP . Постановка задачи: в форме БД Access Студенты_конструктор создать одно поле, в котором должна выводиться текущая дата, и другое поле, которое объединяет фамилию, имя и отчество студента. Для решения этой задачи откроем форму Студенты_конструктор в режиме конструктора (рис. 1).

Рис. 1.

Для создания вычисляемого поля в форме БД Access, отображающего текущие дату, необходимо выполнить следующие операции:

нажать кнопку "ab " (Поле) на панели инструментов, а затем щелкнуть на форме в том месте, где планируется вставить свободное текстовое поле;
затем установить курсор в поле "Свободный" (рис. 2) и ввести функцию: =Date();
следующий шаг - изменить название метки текстового поля (рис. 2), для этого надо удалить порядковый номер (в нашем примере Поле30) и ввести слово "Дата: ";

Рис. 2.

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

Рис. 3.

закрыть окно Поле;
просмотреть форму БД Access в режиме формы, выполнив щелчок по кнопке Открыть (рис. 4);

Рис. 4.

Для создания еще одного вычисляемого поля в форме БД Access, отображающего выражение, использующее в качестве входных данных значения полей таблицы (Фамилия, Имя, Отчество), надо выполнить следующие действия:

добавить свободное поле на форму (рисунок 5);

Рис. 5.

изменить название метки текстового поля на ФИО;
вызвать построитель выражений, для этого щелкнуть на панели инструментов на пиктограмме "Построитель", откроется окно Построитель, в котором надо выделить "выражение" (рисунок 6) и щелкнуть ОК;

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

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

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

2. На панели элементов выберите элемент Поле и поместите его в необходимое место области данных.

3. Сделайте выравнивание надписи поля и самого поля по своему усмотрению.

4. Двойным щелчком кнопки мыши войдите в режим редактирования надписи поля и введите название данного поля (название того, что будет считать формула).

5. Щелчком правой кнопки мыши выполните вызов контекстного меню для самого поля и выберите пункт Свойства.

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

· Двойной щелчок по имени поля формы (средняя область построителя) поместит его в создаваемую формулу;

· Щелчок мыши по арифметическому знаку позволит Вам также поместить его в создаваемую формулу.

7. Примечание: Вы можете не использовать построитель выражений для создания формулы, а внести ее в режиме редактирования части «Данные» (где указано «Свободный») выбранного поля. При этом имена полей таблиц заключаются в квадратные скобки, и они должны полностью соответствовать названию, указанному при создании таблицы с соблюдением регистров и всех знаков, включая пробелы.

8. Сохраните созданную форму и откройте ее в режиме формы. В случае обнаружения ошибок вернитесь в режим конструктора.

Задания

1. Создайте поле, вычисляющее стоимость каждой поставки для Экземпляров медиатеки.

2. Для этого воспользуйтесь представленной выше последовательностью действий и следующими замечаниями:

· вычисляемое поле необходимо создавать в форме «Поставка подчиненная форма»;

· откройте эту форму в режиме «Конструктор»;

· выберите элемент управления «поле»;

· в надписи поля внесите «Стоимость поставки», в поле формулу =[Цена]*[Количество] (где [Цена] и [Количество]– это имена соответствующих полей таблицы «Поставка», которые используются в форме для отображения необходимых данных).

3. Откройте форму «Поставка подчиненная форма» и убедитесь, что вычисляемое поле работает верно.

4. Откройте форму «Экземпляр», посмотрите, видно ли вновь созданное поле. При необходимости внесите изменения в размер подчиненной формы, чтобы все данные отображались.

5. Сохраните изменения, результат продемонстрируйте преподавателю.

Создание главной кнопочной формы

Главная кнопочная форма – это форма, которая загружается первой при открытии БД. Данная форма должна содержать кнопки, по нажатию которых Вы сможете иметь доступ (открывать, распечатывать и т.д.) ко всем объектам Вашей БД: формам, запросам, отчетам. Кроме того, на ней должна быть кнопка выхода (как и на каждой из форм), либо кнопка возврата на главную форму.

При создании Главной кнопочной формы можно использовать:

1) режим Конструктора ();

2) Диспетчер кнопочных форм (меню Сервис/Служебные программ/Диспетчер кнопочных форм).

Для создания кнопочной формы в режиме Конструктора необходимо выполнить представленную ниже последовательность действий.

1. Создание «макета» формы:

· на закладке Форма выберите команду Создать;

· в качестве режима создания формы укажите Режим Конструктора;

· источник данных для формы не указывайте (вновь созданная форма имеет только область данных);

· сохраните форму по именем «Главная».

2. Вставка графического объекта в форму:

· на Панели Элементов выберите элемент управления Рисунок;

· поместите его в область данных;

· в появившемся диалоговом окне выберите графический файл (*.bmp) для вставки его в область данных;

· изменить свойства объекта можно, вызвав для него контекстное меню и воспользовавшись необходимыми командами.

3. Создание кнопки:

· на панели элементов выберите элемент управления кнопка;

· поместите его в область данных.

· выберите из списка категорий необходимую для Вас (например, Работа с формой);

· из списка действий выберите то, которое Вы предполагаете осуществлять с помощью данной кнопки (например, Закрытие формы). Нажмите кнопку Далее;

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

· дайте название вновь созданной кнопке. Рекомендуется оставлять нумерацию системы (название по умолчанию «Кнопка N», где N – номер по порядку).

· нажмите кнопку Готово;

· вид и расположение вновь созданной кнопки Вы можете изменить, воспользовавшись контекстным меню и соответствующими его командами;

· откройте форму в режиме формы, проверьте работу кнопки.

Задания

1. Построить главную кнопочную форму для учебной задачи, руководствуясь информацией пункта «Создание главной кнопочной формы» и следующими требованиями:

· на форме должен быть рисунок, соответствующий теме учебной задачи;

· каждые элементы созданной системы должны быть доступны из отдельной вкладки: формы, отчеты, запросы;

· на форме должны быть кнопки запуска элементов и закрытия формы;

· кнопки для запуска запросов и отчетов могут отсутствовать или существовать в виде «заглушек», то есть кнопок без назначенного действия;

· на каждой из созданных ранее форм должны быть кнопки закрытия формы и перехода на Главную форму (создайте на созданных Вами формах «Поставщик» и «Экземпляр» необходимые кнопки: создания новой записи, удаления записи, закрытия формы и перехода на главную) (см. рис. 5, 6).

Примечание: кнопки и другие элементы управления, позволяющие работать с остальными объектами БД, необходимо будет добавить при выполнении последующих лабораторных работ (11 и 12).

2. Сохраните Главную форму.

3. Проверьте ее работу.

4. Используя вновь созданные формы, создайте в каждой из таблиц по одной новой записи.

Вопросы для самоконтроля

1. Для чего используется форма как объект СУБД MS Access?

2. Какие области формы Вы знаете, каково предназначение каждой из них?

3. В чем отличие Ленточного метода компоновки данных на форме (внешний вид) от Табличного?

4. В чем отличие метода компоновки данных на форме «Сводная таблица» от «Сводная диаграмма»?

5. В чем отличие метода компоновки данных на форме «В один столбец» от «Выровненного»?

6. Можно ли изменить выбранный стиль формы, если да, то как это сделать?

7. Если Вы хотите добавить в форму вычисляемое поле, какой элемент управления будете использовать и в какую область формы его поместите?

8. Какая часть элемента управления «поле» может быть изменена, а какая нет и почему?

9. Каков алгоритм создания подчиненной формы в режиме Мастера?

11. Можно ли использовать при записи вычисляемого выражения имена других вычисляемых полей данной формы (ответ обоснуйте)?

12. Определите назначение главной кнопочной формы.

13. Какова последовательность действий при работе с Диспетчером кнопочных форм? Укажите преимущества и недостатки данного режима?

14. Позволяет ли режим Конструктора форм создавать формы на основе нескольких таблиц или запросов, если да, то каким образом это осуществляется?

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

ЛАБОРАТОРНАЯ РАБОТА № 11
Конструирование запросов в СУБД MS Access

1. Конструирование запроса на выборку.

2. Конструирование запросов на удаление и обновление.

3. Конструирование запроса с параметром.

Цель: научиться конструировать запросы на выборку, удаление и параметрические запросы.

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

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

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