Можно ли в access использовать формулы как в эксель
Например, если у вас есть поля Количество и ЦенаЗаЕдиницу, как заставить Аксесс записать Количество * ЦенаЗаЕдиницу в поле Стоимость?
Лучший ответ: "Не делайте этого!"
Место вычисляемых полей ― в запросах, а не в таблицах.
Вычисляемые столбцы являются неотъемлемой частью электронных таблиц типа Excel и ему подобных, но им не место в таблице базы данных. Никогда не храните значение, которое зависит от других полей: это базовое правило нормализации. Нарушьте это правило ― и сразу появится забота, как поддерживать корректность вычисления во всех возможных ситуациях.
Спросите себя: "Если в этом поле окажется значение, отличное от вычисленного, будет ли это ошибкой?" Если ответ «Да», вам не стоит заводить это поле. Если разница будет нести определенный смысл, тогда вам это поле действительно нужно.
Итак, как же получить вычисленное поле, если его нельзя хранить в таблице? Используйте запрос:
- Создайте запрос, основанный на этой таблице.
- Запишите выражение в строке конструктора запроса:
Стоимость: [Количество] * [ЦенаЗаЕдиницу]
Это создаст поле с именем Стоимость. Любая форма или отчет, основанные на этом запросе, воспримут это вычисленное поле как любое другое, так что вы легко сможете просуммировать результат. Это просто, эффективно и надежно.
Вы все еще хотите хранить вычисляемые поля?
Есть обстоятельства, когда хранение вычисленного результата имеет смысл ― типичный случай, когда нужна возможность иногда хранить отличное от вычисленного значение.
Скажем, плата за монтаж составляет дополнительные 10%, но чтобы выиграть тендер вы хотите отменить эту плату. Вычисляемое поле не сработает. В этом случае вполне разумно иметь запись с платой 0% вместо 10%, так что вам придется хранить это как поле в таблице.
Чтобы получить это, используйте событие AfterUpdate контрола на вашей форме, чтобы автоматически посчитать плату:
Теперь, каждый раз, когда меняется Количество или ЦенаЗаЕдиницу, Аксесс автоматически пересчитает новую плату, но пользователь может перезаписать вычисление и ввести другое число, если потребуется.
Если у вас возник вопрос, как бы помочь пользователю с вычислением с помощью описанного приема, см. Enter text in calculated controls.
А как насчет вычисляемых полей в Аксессе 2010?
Аксесс 2010 позволяет создать вычисляемое поле в таблице вот таким образом:
Просто выберите «Вычисляемый» из типов данных и ниже появится строка «Выражение». Впишите выражение. Аксесс будет пересчитывать его каждый раз, когда вы вводите запись.
Поскольку нельзя проиндексировать вычисляемые поля, вы не получите выигрыша в производительности, используя их. Вы нарушите фундаментальное правило нормализации, не получив ничего взамен. Мы рекомендуем использовать запросы для вычисляемых полей ― как и в предыдущих версиях.
Выражения в Microsoft Access можно применять для решения широкого круга задач (например, для математических вычислений, объединения и извлечения текста или проверки данных). В этой статье приведены основные сведения о выражениях, в том числе инструкции по их использованию, сравнение с формулами Microsoft Excel, а также описание их компонентов.
В этом разделе.
Общие сведения о выражениях
Это можно представить себе следующим образом: если вы хотите, чтобы приложение Access сделало что-то, вам нужно обратиться к нему на его языке. Предположим, что вы хотите попросить Access проверить поле BirthDate в таблице Customers и сообщить год рождения заказчика. Вы можете записать эту просьбу в виде такого выражения:
Это выражение содержит функцию DatePart и два аргумента: "yyyy" и [Customers]![BirthDate] .
Рассмотрим его более подробно.
1 DatePart — это функция, которая проверяет даты и возвращает определенный фрагмент. В этом случае используются первые два аргумента.
2 Аргумент интервала сообщает Access, какую часть даты нужно вернуть. В данном случае значение "yyyy" указывает на то, что требуется вернуть только год.
3 Аргумент даты говорит о том, где искать значение даты. В данном случае значение [ Customers]![BirthDate] указывает, что дата находится в поле BirthDate таблицы Customers.
Способы использования выражений
С помощью выражений можно делать следующее:
Вычислять значения, которые отсутствуют в данных. Можно вычислять значения в полях таблиц и запросов и в элементах управления форм и отчетов.
Присваивать значения по умолчанию полям таблиц или элементам управления в формах и отчетах. Эти значения по умолчанию отображаются при каждом открытии таблицы, формы или отчета.
Создавать правила проверки, чтобы определять значения, которые пользователь может вводить в поле или элемент управления.
Определять условия запроса для ограничения результатов нужным подмножеством.
Вычисление значений
Один из наиболее распространенных способов использования выражений в Access — вычисление значений, которые присутствуют непосредственно в данных. Столбец в таблице или запросе, в котором сохраняются результаты таких вычислений, называется вычисляемым полем. Вы можете создать вычисляемое поле, в котором объединяются данные из двух или нескольких полей таблицы. Например, имена и фамилии часто хранятся в разных полях. Если вы хотите объединить имена и фамилии, а затем отобразить их в одном поле, можно создать вычисляемое поле в таблице или запросе:
Здесь амперсанд ( & ) используется для объединения значения в поле FirstName , символа пробела (он заключен в кавычки) и значения в поле LastName .
Определение значения по умолчанию
В Microsoft Access с помощью выражений можно установить значения по умолчанию для поля в таблице или элемента управления. Например, чтобы по умолчанию задать для поля даты текущую дату, в поле свойства DefaultValue (Значение по умолчанию) необходимо ввести такое выражение:
Создание правила проверки
Кроме того, выражения можно использовать для настройки правила проверки. Например, правило проверки можно использовать в элементе управления или поле таблицы, чтобы требовать ввода даты, которая не предшествует текущей. В этом случае в поле свойства ValidationRule (Правило проверки) нужно ввести выражение:
Определение условий запроса
Наконец, выражения можно использовать для определения условий запросов. Предположим, что необходимо получить данные о продажах для заказов, поставленных в определенный период времени. Можно ввести условия для определения диапазона данных, а Microsoft Access вернет только те строки, которые соответствуют им. Например, выражение может выглядеть следующим образом:
При добавлении условий в запрос и выполнении этого запроса возвращаются только те значения, которые соответствуют указанным датам.
Примеры выражений
В приведенной ниже таблице представлены некоторые примеры выражений Access и типичные способы их использования.
Вычисляет разницу между значениями дат в двух текстовых полях (RequiredDate и ShippedDate) отчета.
Устанавливает текущую дату в качестве значения по умолчанию для поля даты и времени в таблице.
Определяет условия для поля даты и времени в запросе.
Возвращает значение элемента управления OrderSubtotal подчиненной формы Orders в форме Orders.
Задает правило проверки для числового поля таблицы: пользователи должны вводить значения больше нуля.
Одни выражения начинаются с оператора равенства ( = ), а другие — нет. При вычислении значения для элемента управления в форме или отчете в начале выражения указывается оператор = . В других случаях, например при вводе выражения в запрос или в свойство DefaultValue либо ValidationRule поля или элемента управления оператор = использовать не нужно, если только выражение не добавляется в текстовое поле таблицы. В некоторых случаях, например при добавлении выражений в запросы, Access автоматически удаляет оператор = .
Компоненты выражений
Выражение представляет собой набор компонентов, используемых по одному или в сочетании друг с другом, который дает определенный результат. Вот эти компоненты:
Идентификаторы — имена полей таблицы или элементов управления в форме или отчете либо свойства этих полей или элементов управления;
Операторы — например: + (плюс) или - (минус);
Функции — например: SUM или AVG;
Константы — постоянные значения, например текстовые строки или числа, не вычисляемые выражением;
Значения — строки, например "Введите число от 1 до 10", или числа, такие как 1254, которые используются в операциях.
Эти компоненты описаны более подробно в разделах ниже.
Идентификаторы
Идентификатор — это имя поля, свойства или элемента управления. Идентификаторы используются в выражении для ссылки на значение, связанное с полем, свойством или элементом управления. Например, рассмотрим выражение =[RequiredDate]-[ShippedDate] . В этом выражении значение поля или элемента управления ShippedDate вычитается из значения поля или элемента управления RequiredDate . В этом выражении как RequiredDate , так и ShippedDate являются идентификаторами.
Операторы
Access поддерживает большое количество операторов, в том числе стандартные арифметические операторы, такие как + , - , * (умножение) и / (деление). Также можно использовать операторы сравнения, например < (меньше) или >(больше), для сравнения значений; текстовые операторы, например & и + , для сцепления (объединения) текста; логические операторы, например Not и And , для определения значений True и False, а также специальные операторы Access.
Функции
Функции — это встроенные процедуры, которые можно использовать в выражениях. Функции можно применять для широкого ряда операций, например для вычисления значений, работы с текстом и датами, а также для подведения итогов. Например, одной из распространенных функций является DATE, которая возвращает текущую дату. Функцию DATE можно использовать различными способами (например, в выражении, которое задает значение по умолчанию для поля или таблицы). В данном примере при добавлении новой записи в качестве значения для поля по умолчанию устанавливается текущая дата.
Для некоторых функций требуются аргументы. Аргумент — это значение, которое передает функции входные параметры. Если функции требуется несколько аргументов, они разделяются точкой с запятой. Например, рассмотрим функцию DATE в следующем примере выражения:
В этом примере используются два аргумента:
Первый аргумент — функция Date() , которая возвращает текущую дату. Даже если аргументов нет, после имени функции необходимо ввести скобки.
Второй аргумент — "mmmm d, yyyy" . Он отделен от первого аргумента точкой с запятой и задает текстовую строку, чтобы указать функции FORMAT, как отформатировать возвращаемое значение даты. Обратите внимание: текстовую строку необходимо заключить в кавычки.
Это пример также иллюстрирует вложение функций: результат, возвращенный одной функцией, часто служит аргументом другой функции. В этом случае Date() выступает в качестве аргумента.
Константы
Константа — это элемент, значение которого не изменяется во время работы Access. В выражениях часто используются константы True , False и Null .
Значения
В выражениях можно использовать литералы (например, число 1254 или строку "Введите число от 1 до 10"). Вы также можете использовать числовые значения, представляющие собой последовательность цифр и при необходимости содержащие знак и десятичный разделитель.
При использовании текстовых строк заключайте их в кавычки, чтобы они правильно интерпретировались в Access. В некоторых ситуациях Access вводит кавычки за пользователя. Например, при вводе текста в выражение для правила проверки или условий запроса Access автоматически заключает текстовые строки в кавычки.
Сравнение выражений Access и формул Excel
Выражения Access напоминают формулы Excel, так как для получения результатов в них используются похожие элементы. И в формулах Excel, и в выражениях Access содержатся некоторые или все из указанных ниже элементов.
Идентификаторы В Excel идентификаторы — это имена отдельных ячеек или диапазонов ячеек в книге, например A1, B3:C6 или Лист2!C32. В Access идентификаторы — это имена полей таблиц (например, [Contacts]![First Name] ), элементов управления в формах и отчетах (например, Forms![Task List]![Description] ) или свойства этих полей или элементов управления (например, Forms![Task List]![Description].ColumnWidth ).
Операторы Как в Access, так и в Excel операторы используются для сравнения данных или выполнения простых вычислений с ними. В качестве примеров можно привести операторы + (плюс) и - (минус).
Функции Как в Access, так и в Excel функции и аргументы используются для выполнения задач, которые невозможно выполнить с помощью одних только операторов. Например, можно найти среднее для значений в поле или преобразовать результаты вычислений в формат денежной единицы. Примерами функций могут служить SUM и STDEV (в Excel — СУМ и СТАНДОТКЛОН). Аргументы — это значения, используемые для передачи данных функциям. И в Access, и в Excel есть много функций, но имена схожих функций в этих приложениях различаются. Например, функция СРЗНАЧ в Excel соответствует функции AVG в Access.
Константы И в Access, и в Excel константы — это значения, которые не изменяются (например, числа, не вычисляемые с помощью выражений).
Значения Значения используются в Access и Excel одинаково.
В выражениях Access используются такие же операторы и константы, как и в формулах Excel, но другие идентификаторы и функции. В то время как формулы Excel, как правило, используется только в ячейках листа, выражения Access используются во многих частях приложения для решения широкого круга задач, включая следующие:
создание вычисляемых элементов управления в формах и отчетах;
создание вычисляемых полей в таблицах и запросах;
составление условий в запросах;
проверка данных, вводимых в поле, элемент управления или форму;
группировка данных в отчетах.
Выражения Access и формулы Excel можно использовать для вычисления числовых значений или значений даты и времени с помощью математических операторов. Например, для вычисления цены со скидкой для клиента можно использовать формулу Excel =C2*(1-D2) или выражение Access = [Unit Price]*(1-[Discount]) .
Выражение Access или формулу Excel можно использовать для объединения, разбиения или другой обработки строк с помощью строковых операторов. Например, для объединения имени и фамилии в одну строку можно использовать формулу Excel =D3 &" " & D4 или выражение Access = [First Name] & " " & [Last Name] .
Функция IIF в запросах Access
Всем привет, сегодня разбираем функцию IIF в запросах Access. Если вы не знаете, как создать условие «ЕСЛИ ТО» в базе данных Microsoft Access, то вы пришли по адресу.
На простых запросах мы разберем принцип работы встроенной функции IIF. С помощью IIf можно определить, является ли выражение истиной или ложью. Если выражение истинно, IIf вернет одно значение; если ложно, IIf вернет другое значение.
Синтаксис функции IIF: IIf (условие; если истина; если ложь).
1 аргумент — наше условие. Если это условие выполняется, то функция IIF в запросах Access вернет значение второго аргумента, если же условие не выполняется, то функция IIF в запросах Access вернет значение третьего аргумента.
Рассмотрим базу данных «Студенты». Если вы хотите более подробно ознакомится с тем, как создавалась база данных Access Студенты, то переходите по ССЫЛКЕ.
Рассмотрим 1 пример.
У нас есть таблица СТУДЕНТЫ, известно, что некоторые студенты получают стипендию. Предположим, что сумма базовой стипендии 5000 руб.
Создадим такой запрос, который начислит 5000 руб тем студентам, кто получает стипендию, а всем остальным — 0!
Перейдем в конструктор запросов, нам понадобится дополнительное вычисляемое поле, а также функция IIF в запросах Access.
Результат запроса с функцией IIF представлен ниже:
Рассмотрим 2 пример.
Для 2 примера необходимо в таблицу СТУДЕНТЫ добавить новое логическое поле МЕДАЛЬ.
Студентам, которые имеют стипендию и медаль начислим 7000 рублей, у кого просто стипендия — 5000 руб, остальным -0!
Снова нам поможем функция IIF в запросах Access.
Обратите внимание, что функцию IIF можно использовать в качестве аргумента другой функции IIF.
Рассмотрим 3 пример.
Работаем с таблицей ПРЕПОДАВАТЕЛИ. Мы знаем, сколько получает каждый преподаватель и знаем, сколько у него детей.
Сделаем так, чтобы преподавателям, у которых больше 2 детей, начислялась надбавка 10000 рублей, тем у кого от 1 до 2 детей — 5000 рублей, у кого нет детей — 0!
Хотите больше примеров, где разбирается функция IIF в запросах Access? Переходите по ССЫЛКЕ.
Если вам нужна готовая база данных Access, то ознакомьтесь со списком представленным ЗДЕСЬ.
Формулы – это хорошо. Они автоматически пересчитываются при любом изменении исходных данных, превращая Excel из "калькулятора-переростка" в мощную автоматизированную систему обработки поступающих данных. Они позволяют выполнять сложные вычисления с хитрой логикой и структурой. Но иногда возникают ситуации, когда лучше бы вместо формул в ячейках остались значения. Например:
- Вы хотите зафиксировать цифры в вашем отчете на текущую дату.
- Вы не хотите, чтобы клиент увидел формулы, по которым вы рассчитывали для него стоимость проекта (а то поймет, что вы заложили 300% маржи на всякий случай).
- Ваш файл содержит такое больше количество формул, что Excel начал жутко тормозить при любых, даже самых простых изменениях в нем, т.к. постоянно их пересчитывает (хотя, честности ради, надо сказать, что это можно решить временным отключением автоматических вычислений на вкладке Формулы – Параметры вычислений).
- Вы хотите скопировать диапазон с данными из одного места в другое, но при копировании "сползут" все ссылки в формулах.
В любой подобной ситуации можно легко удалить формулы, оставив в ячейках только их значения. Давайте рассмотрим несколько способов и ситуаций.
Способ 1. Классический
Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:
- Выделите диапазон с формулами, которые нужно заменить на значения.
- Скопируйте его правой кнопкой мыши – Копировать(Copy) .
- Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values) :
либо наведитесь мышью на команду Специальная вставка (Paste Special) , чтобы увидеть подменю:
Из него можно выбрать варианты вставки значений с сохранением дизайна или числовых форматов исходных ячеек.
В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special - Values) в открывшемся диалоговом окне:
Способ 2. Только клавишами без мыши
При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:
- Копируем выделенный диапазон Ctrl + C
- Тут же вставляем обратно сочетанием Ctrl + V
- Жмём Ctrl , чтобы вызвать меню вариантов вставки
- Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter :
Способ 3. Только мышью без клавиш или Ловкость Рук
Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:
- Выделяем диапазон с формулами на листе
- Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
- В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only) .
После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали ;)
Способ 4. Кнопка для вставки значений на Панели быстрого доступа
Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл - Параметры - Панель быстрого доступа (File - Options - Customize Quick Access Toolbar) . В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:
Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:
Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt , то Excel подскажет цифру, которая за это отвечает:
Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу
Если вас не пугает слово "макросы", то это будет, пожалуй, самый быстрый способ.
Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:
Если вам нужно преобразовать в значения текущий лист, то макрос будет таким:
И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:
Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt + F11 чтобы попасть в Visual Basic, далее Insert - Module). Запускать их потом можно через вкладку Разработчик - Макросы (Developer - Macros) или сочетанием клавиш Alt + F8 . Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить - применяйте их с осторожностью.
Способ 6. Для ленивых
Если ломает делать все вышеперечисленное, то можно поступить еще проще - установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:
Знаете ли вы, что вы можете вычислять поля в запросе в Microsoft Access? Если пользователю требуется вычисляемое поле в запросе, ему необходимо ввести имя вычисляемого поля, двоеточие и вычисления в одном из столбцов в строке поля сетки конструктора запроса.
Вычисляемое поле — это вычисление отдельной записи, поскольку каждое вычисление включает только поля в определенной записи.
Создание вычисляемых полей в Access
Откройте Microsoft Access.
Создайте таблицу или существующую таблицу.
Чтобы создать запрос, перейдите на вкладку «Создать» и нажмите Дизайн запросов в Запросы группа.
Справа вы увидите окно «Добавить таблицу»; выберите таблицу для расчета.
Затем нажмите Добавить в избранное Таблицы в нижней части окна «Добавить таблицу».
Таблица появится в верхней части окна.
В нижнем окне вы увидите Сетка дизайна.
А Сетка дизайна — это инструмент, который создает запрос, помещая поля из выбранной таблицы в верхних частях окна в нижние части окна.
На Поле в строке, щелкните стрелку раскрывающегося списка и выберите поля для каждого столбца, который вы хотите включить в запрос.
В этой статье мы хотим подсчитать общее количество рождаемости за три года. Годы включают 2018, 2019 и 2020.
В последний столбец мы добавим поле; ‘Общее количество рождений за три года. ‘
Мы щелкнем правой кнопкой мыши «Общее количество рождений за три года» поле.
В контекстном меню нажмите Увеличить.
Откроется диалоговое окно «Масштаб».
в Увеличить диалоговое окно, вы увидите текст поля внутри него; из-за того, что вы его выбрали.
Напишите формулу в соответствии с тем расчетом, который вы производите.
Например — Общее количество рождений за три года: [2018] +[2019] +[2020].
Затем нажмите хорошо.
Затем нажмите Запустить в Результаты группа слева.
Вы увидите результат своего расчета.
Надеюсь, это поможет; если у вас есть вопросы, оставьте комментарий ниже.
Читайте: Как добавлять комментарии в слайды PowerPoint.
.
Читайте также: