Вычисление значения математического выражения в MS Excel

Обновлено: 21.11.2024

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

В этой статье

Обзор выражений

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

Это выражение состоит из функции DatePart и двух значений аргументов: "yyyy" и [Customers]![BirthDate].

Давайте рассмотрим это выражение более подробно.

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

2 Аргумент интервала сообщает Access, какую часть даты следует вернуть. В данном случае "гггг" сообщает Access, что вы хотите вернуть только часть даты, содержащую год.

3 Аргумент даты сообщает Access, где искать значение даты — в данном случае [Клиенты]![Дата рождения] сообщает Access, что нужно искать дату в поле Дата рождения таблицы «Клиенты».

Способы использования выражений

Вы можете использовать выражения следующими способами:

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

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

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

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

Вычислить значения

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

В этом случае амперсанд ( & ) объединяет значение в поле "Имя", пробел (пробел, заключенный в кавычки) и значение в поле "Фамилия".

Определить значение по умолчанию

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

Создайте правило проверки

Кроме того, вы можете использовать выражение для установки правила проверки. Например, вы можете использовать правило проверки в поле элемента управления или таблицы, чтобы требовать, чтобы введенная дата была больше или равна текущей дате. В этом случае вы устанавливаете значение в поле свойства Validation Rule:

Определить критерии запроса

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

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

Примеры выражений

В следующей таблице перечислены некоторые примеры выражений Access и их обычное использование:

Вычисляет разницу между значениями дат в двух текстовых полях (называемых RequiredDate и ShippedDate) в отчете.

Устанавливает значение по умолчанию для поля таблицы Дата/Время на текущую дату.

Задает критерии для поля даты/времени в запросе.

Возвращает значение элемента управления OrderSubtotal в подформе Orders, которая находится в форме Orders.

Устанавливает правило проверки для числового поля в таблице — пользователи должны вводить значения больше нуля.

Некоторые выражения начинаются с оператора равенства (=), а другие — нет. Когда вы вычисляете значение элемента управления в форме или отчете, вы используете оператор =, чтобы начать выражение. В других случаях, например при вводе выражения в запросе или в свойстве DefaultValue или ValidationRule поля или элемента управления, вы не используете оператор =, если только не добавляете выражение в текстовое поле в таблице.В некоторых случаях, например при добавлении выражений в запросы, Access автоматически удаляет оператор =.

Компоненты выражений

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

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

Операторы Например, + (плюс) или - (минус).

Функции Например, SUM или AVG.

Константы. Неизменяемые значения, например текстовые строки или числа, не вычисляемые выражением.

Значения Строки, например "Введите число от 1 до 10". или числа, например 1254, которые используются в операциях.

В следующих разделах эти компоненты описаны более подробно.

Идентификаторы

Идентификатор – это имя поля, свойства или элемента управления. Вы используете идентификатор в выражении для ссылки на значение, связанное с полем, свойством или элементом управления. Например, рассмотрим выражение =[RequiredDate]-[ShippedDate]. Это выражение вычитает значение поля или элемента управления ShippedDate из значения поля или элемента управления RequiredDate. В этом выражении и RequiredDate, и ShippedDate служат идентификаторами.

Операторы

Access поддерживает различные операторы, включая распространенные арифметические операторы, такие как + , - , * (умножение) и / (деление). Вы также можете использовать операторы сравнения, такие как (меньше) или > (больше), для сравнения значений, текстовые операторы, такие как & и +, для объединения (объединения) текста, логические операторы, такие как Not и And, для определения истинных или ложных значений, и другие операторы, характерные для Access.

Функции

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

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

В этом примере используются два аргумента:

Первый аргумент — это функция Date(), которая возвращает текущую дату. Если аргументов нет, вам все равно нужно добавить круглые скобки функции.

Второй аргумент "mmmm d, yyyy" , отделенный от первого аргумента запятой, указывает текстовую строку, которая указывает функции FORMAT, как форматировать возвращаемое значение даты. Обратите внимание, что текстовая строка должна быть заключена в кавычки.

Это выражение также показывает, что часто можно вложить значение, возвращаемое одной функцией, в качестве аргумента другой функции. В этом случае Date() действует как аргумент.

Константы

Константа — это элемент, значение которого не меняется во время работы Access. Константы True , False и Null часто используются в выражениях.

Значения

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

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

Сравнение выражений Access и формул Excel

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

Идентификаторы В Excel идентификаторы — это имена отдельных ячеек или диапазонов ячеек в книге, например A1, B3:C6 или Sheet2!C32. В Access идентификаторы — это имена полей таблицы (например, [Контакты]![Имя] ), элементы управления в формах или отчетах (например, Формы![Список задач]![Описание] ) или свойства этих полей или элементы управления (такие как Forms![Список задач]![Описание].ColumnWidth ).

Операторы Как в Access, так и в Excel операторы используются для сравнения значений или выполнения простых вычислений с данными. Примеры включают + (плюс) или - (минус).

Функции Как в Access, так и в Excel функции и аргументы используются для выполнения задач, которые невозможно выполнить с помощью одних только операторов. Например, вы можете найти среднее значение в поле или преобразовать результат расчет в валютном формате. Примеры функций включают СУММ и СТАНДОТКЛОН. Аргументы — это значения, которые предоставляют информацию функциям. Обратите внимание, что в Access и Excel есть много функций, из которых можно выбирать, но имена похожих функций в программах иногда различаются. Например, функция СРЗНАЧ в Excel соответствует функции СРЗНАЧ в Access.

Константы Как в Access, так и в Excel константы — это значения, которые не изменяются — такие числа, которые не вычисляются с помощью выражения.

Значения В Access и Excel значения используются одинаково.

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

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

Создание вычисляемых полей в таблицах и запросах

Использование критериев в запросах

Проверка данных, вводимых в поле или элемент управления в форме

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

Вы можете использовать либо выражение Access, либо формулу Excel для вычисления числовых значений или значений даты/времени с помощью математических операторов. Например, чтобы рассчитать цену со скидкой для клиента, можно использовать формулу Excel =C2*(1-D2) или выражение Access = [Цена за единицу]*(1-[Скидка]) .

Вы также можете использовать выражение Access или формулу Excel, чтобы объединять, разделять или иным образом манипулировать строками с помощью строковых операторов. Например, чтобы объединить имя и фамилию в одну строку, можно использовать формулу Excel =D3 & " " & D4 или выражение Access = [First Name] & " " & [Last Name] .

Вместо калькулятора используйте Microsoft Excel для расчетов!

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

Вычитание в Excel

Умножение в Excel

Разделить в Excel

Подробнее о простых формулах

Все записи формул начинаются со знака равенства (=). Для простых формул просто введите знак равенства, за которым следуют числовые значения, которые вы хотите вычислить, и математические операторы, которые вы хотите использовать: знак плюс (+) для добавления, знак минус (-) для вычитания, звездочка ( *) для умножения и косая черта (/) для деления. Затем нажмите клавишу ВВОД, и Excel мгновенно рассчитает и отобразит результат формулы.

Например, если вы введете =12,99+16,99 в ячейку C5 и нажмете клавишу ВВОД, Excel вычислит результат и отобразит 29,98 в этой ячейке.

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

Использовать автосумму

Самый простой способ добавить формулу СУММА на лист — использовать автосуммирование. Выберите пустую ячейку непосредственно над или под диапазоном, который вы хотите суммировать, и на вкладках Главная или Формула ленты щелкните Автосумма > Сумма. AutoSum автоматически определит диапазон для суммирования и построит для вас формулу. Это также работает по горизонтали, если вы выбираете ячейку слева или справа от диапазона, который вам нужно суммировать.

Примечание. Автосумма не работает для несмежных диапазонов.

Автосумма по вертикали

На рисунке выше видно, что функция автосуммирования автоматически определяет ячейки B2:B5 как диапазон для суммирования. Все, что вам нужно сделать, это нажать ENTER, чтобы подтвердить это. Если вам нужно добавить/исключить больше ячеек, вы можете удерживать клавишу Shift + клавишу со стрелкой по вашему выбору, пока ваш выбор не будет соответствовать тому, что вы хотите. Затем нажмите Enter, чтобы завершить задачу.

Руководство по функциям Intellisense: плавающий тег SUM(number1,[number2], …) под функцией является руководством по Intellisense. Если щелкнуть SUM или имя функции, оно изменится на синюю гиперссылку на раздел справки для этой функции. Если щелкнуть отдельные функциональные элементы, их репрезентативные части в формуле будут выделены. В этом случае будут выделены только B2:B5, так как в этой формуле есть только одна ссылка на число. Тег Intellisense будет отображаться для любой функции.

Автосумма по горизонтали

Подробнее читайте в статье о функции СУММ.

Не переписывайте одну и ту же формулу

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

Например, когда вы копируете формулу из ячейки B6 в ячейку C6, формула в этой ячейке автоматически изменяется, чтобы обновить ссылки на ячейки в столбце C.

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

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

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

Например, следующая формула умножает 2 на 3, а затем добавляет к этому результату 5, чтобы получить ответ 11.

В следующей формуле используется функция ПЛТ для расчета платежа по ипотеке (1073,64 долл. США), основанного на процентной ставке 5 % (5 %, разделенные на 12 месяцев, равняется месячной процентной ставке) за 30-летний период (360 месяцев). ) для кредита в размере 200 000 долларов США:

Вот несколько дополнительных примеров формул, которые можно ввести на листе.

=A1+A2+A3 Складывает значения в ячейках A1, A2 и A3.

=SQRT(A1) Использует функцию SQRT для возврата квадратного корня из значения в A1.

=TODAY() Возвращает текущую дату.

=ПРОПИСН("привет") Преобразует текст "привет" в "ПРИВЕТ" с помощью функции листа ПРОПИСН.

=IF(A1>0) Проверяет ячейку A1, чтобы определить, содержит ли она значение больше 0.

Части формулы

Формула также может содержать некоторые или все из следующих элементов: функции, ссылки, операторы и константы.

<р>1. Функции: функция PI() возвращает значение числа пи: 3,142.

<р>2. Ссылки: A2 возвращает значение в ячейке A2.

<р>3. Константы: числа или текстовые значения, введенные непосредственно в формулу, например 2.

<р>4. Операторы: оператор ^ (вставка) возводит число в степень, а оператор * (звездочка) умножает числа.

Использование констант в формулах

Использование операторов вычисления в формулах

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

Типы операторов

Существует четыре различных типа операторов вычисления: арифметические операции, сравнение, конкатенация текста и ссылка.

Арифметические операторы

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

Арифметический оператор

Операторы сравнения

Вы можете сравнить два значения с помощью следующих операторов. Когда два значения сравниваются с помощью этих операторов, результатом является логическое значение — либо ИСТИНА, либо ЛОЖЬ.

Оператор сравнения

> (знак больше)

= (знак больше или равно)

Больше или равно

(не равно знаку)

Оператор объединения текста

Используйте амперсанд (&), чтобы соединить (объединить) одну или несколько текстовых строк, чтобы получить единый фрагмент текста.

Текстовый оператор

Соединяет или объединяет два значения для создания одного непрерывного текстового значения

"Север"&"ветер" приводит к "Борей"

Ссылочные операторы

Объедините диапазоны ячеек для вычислений с помощью следующих операторов.

Оператор ссылки

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

Оператор объединения, который объединяет несколько ссылок в одну ссылку

Оператор пересечения, создающий одну ссылку на ячейки, общие для двух ссылок

Порядок, в котором Excel для Интернета выполняет операции в формулах

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

Порядок расчета

Формулы вычисляют значения в определенном порядке. Формула всегда начинается со знака равенства (=). Excel в Интернете интерпретирует символы, следующие за знаком равенства, как формулу. После знака равенства следуют вычисляемые элементы (операнды), такие как константы или ссылки на ячейки. Они разделены операторами вычисления. Excel в Интернете вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.

Приоритет оператора

Если вы объединяете несколько операторов в одной формуле, Excel в Интернете выполняет операции в порядке, указанном в следующей таблице. Если формула содержит операторы с одинаковым приоритетом (например, если формула содержит оператор умножения и деления), Excel в Интернете оценивает операторы слева направо.

Описание

Отрицание (как в –1)

Умножение и деление

Сложение и вычитание

Соединяет две строки текста (объединение)

Использование скобок

Чтобы изменить порядок вычисления, заключите в круглые скобки ту часть формулы, которая будет вычисляться первой. Например, следующая формула дает 11, так как Excel в Интернете выполняет умножение перед сложением. Формула умножает 2 на 3, а затем добавляет к результату 5.

Наоборот, если вы используете круглые скобки для изменения синтаксиса, Excel для Интернета суммирует 5 и 2, а затем умножает результат на 3, чтобы получить 21.

В следующем примере круглые скобки, заключающие первую часть формулы, заставляют Excel для Интернета сначала вычислить B4+25, а затем разделить результат на сумму значений в ячейках D5, E5 и F5.< /p>

Использование функций и вложенных функций в формулах

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

Синтаксис функций

Следующий пример функции ОКРУГЛ, округляющей число в ячейке A10, иллюстрирует синтаксис функции.

<р>1. Структура. Структура функции начинается со знака равенства (=), за которым следует имя функции, открывающая скобка, аргументы функции, разделенные запятыми, и закрывающая скобка.

<р>2. Имя функции. Чтобы просмотреть список доступных функций, щелкните ячейку и нажмите SHIFT+F3.

<р>4. Подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, введите =ROUND( и появится всплывающая подсказка. Подсказки появляются только для встроенных функций.

Ввод функций

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

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

Вложенные функции

В некоторых случаях вам может понадобиться использовать функцию в качестве одного из аргументов другой функции. Например, следующая формула использует вложенную функцию СРЗНАЧ и сравнивает результат со значением 50.

<р>1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

Ограничения уровня вложенности Формула может содержать до семи уровней вложенности функций.Когда одна функция (назовем ее Функцией Б) используется в качестве аргумента в другой функции (назовем ее Функцией А), Функция Б действует как функция второго уровня. Например, функция СРЗНАЧ и функция СУММ являются функциями второго уровня, если они используются в качестве аргументов функции ЕСЛИ. Функция, вложенная во вложенную функцию СРЗНАЧ, становится функцией третьего уровня и т. д.

Использование ссылок в формулах

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

Справочный стиль A1

Стили ссылок по умолчанию По умолчанию Excel в Интернете использует стиль ссылок A1, который ссылается на столбцы с буквами (от A до XFD, всего 16 384 столбца) и ссылается на строки с номерами (от 1 до 1 048 576). Эти буквы и цифры называются заголовками строк и столбцов. Чтобы сослаться на ячейку, введите букву столбца, а затем номер строки. Например, B2 относится к ячейке на пересечении столбца B и строки 2.

Для ссылки

Ячейка в столбце A и строке 10

Диапазон ячеек в столбце А и строках с 10 по 20

Диапазон ячеек в строке 15 и столбцах с B по E

Все ячейки в строке 5

Все ячейки в строках с 5 по 10

Все ячейки в столбце H

Все ячейки в столбцах с H по J

Диапазон ячеек в столбцах от A до E и строках с 10 по 20

Создание ссылки на другой лист В следующем примере функция листа AVERAGE вычисляет среднее значение для диапазона B1:B10 на листе Marketing в той же книге.

<р>1. Относится к рабочему листу под названием "Маркетинг"

<р>2. Относится к диапазону ячеек от B1 до B10 включительно

<р>3. Отделяет ссылку на рабочий лист от ссылки на диапазон ячеек

Разница между абсолютными, относительными и смешанными ссылками

Относительные ссылки Относительная ссылка на ячейку в формуле, например A1, основана на относительном положении ячейки, содержащей формулу, и ячейки, на которую ссылается ссылка. Если положение ячейки, содержащей формулу, изменяется, ссылка изменяется. Если вы скопируете или заполните формулу между строками или столбцами, ссылка будет автоматически скорректирована. По умолчанию в новых формулах используются относительные ссылки. Например, если вы скопируете или заполните относительную ссылку из ячейки B2 в ячейку B3, она автоматически изменится с =A1 на =A2.

Абсолютные ссылки Абсолютная ссылка на ячейку в формуле, например $A$1, всегда указывает на ячейку в определенном месте. Если положение ячейки, содержащей формулу, изменяется, абсолютная ссылка остается прежней. Если вы скопируете или заполните формулу между строками или столбцами, абсолютная ссылка не изменится. По умолчанию в новых формулах используются относительные ссылки, поэтому вам может потребоваться переключить их на абсолютные ссылки. Например, если вы скопируете или заполните абсолютную ссылку из ячейки B2 в ячейку B3, она останется одинаковой в обеих ячейках: =$A$1.

Смешанные ссылки Смешанная ссылка имеет либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец имеет вид $A1, $B1 и т. д. Абсолютная ссылка на строку принимает форму A$1, B$1 и т. д. Если положение ячейки, содержащей формулу, изменяется, относительная ссылка изменяется, а абсолютная ссылка не изменяется. Если вы копируете или заполняете формулу по строкам или столбцам, относительная ссылка корректируется автоматически, а абсолютная ссылка не корректируется. Например, если вы скопируете или заполните смешанную ссылку из ячейки A2 в ячейку B3, она изменится с =A$1 на =B$1.

Трехмерный эталонный стиль

Удобные ссылки на несколько листов Если вы хотите анализировать данные в одной и той же ячейке или диапазоне ячеек на нескольких листах в книге, используйте трехмерную ссылку. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, которому предшествует диапазон имен рабочих листов. Excel в Интернете использует все листы, хранящиеся между начальным и конечным именами ссылки. Например, =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5, на всех листах между листами 2 и 13 включительно.

Трехмерные ссылки можно использовать для ссылки на ячейки на других листах, для определения имен и создания формул с помощью следующих функций: СУММ, СРЗНАЧ, СРЗНАЧ, СЧЕТ, СЧЕТ, МАКС, МАКС, МИН, МИН, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA и VARPA.

Объемные ссылки нельзя использовать в формулах массива.

Трехмерные ссылки нельзя использовать с оператором пересечения (один пробел) или в формулах, использующих неявное пересечение.

Что происходит при перемещении, копировании, вставке или удалении листов В следующих примерах показано, что происходит при перемещении, копировании, вставке или удалении листов, включенных в трехмерную ссылку. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для добавления ячеек с A2 по A5 на листах со 2 по 6.

Вставка или копирование Если вы вставляете или копируете листы между Листами2 и Лист6 (конечными точками в этом примере), Excel в Интернете включает в расчеты все значения в ячейках с A2 по A5 из добавленных листов.

Удалить. Если вы удалите листы между Листами2 и Лист6, Excel в Интернете удалит их значения из расчета.

Переместить. Если вы перемещаете листы между Листами2 и Лист6 в место за пределами указанного диапазона листов, Excel в Интернете удаляет их значения из расчета.

Перемещение конечной точки. Если вы перемещаете Лист2 или Лист6 в другое место в той же книге, Excel в Интернете корректирует расчет, чтобы учесть новый диапазон листов между ними.

Удалить конечную точку. Если вы удаляете Лист2 или Лист6, Excel в Интернете корректирует расчет, чтобы учесть диапазон листов между ними.

Стиль ссылок R1C1

Вы также можете использовать справочный стиль, в котором и строки, и столбцы на листе пронумерованы. Справочный стиль R1C1 полезен для вычисления позиций строк и столбцов в макросах. В стиле R1C1 Excel для Интернета указывает расположение ячейки буквой "R", за которой следует номер строки, и буквой "C", за которой следует номер столбца.

Относительная ссылка на ячейку двумя строками выше и в том же столбце

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

Абсолютная ссылка на ячейку во второй строке и во втором столбце

Относительная ссылка на всю строку над активной ячейкой

Абсолютная ссылка на текущую строку

При записи макроса Excel в Интернете записывает некоторые команды, используя стиль ссылок R1C1. Например, если вы записываете команду, например нажатие кнопки "Автосумма", чтобы вставить формулу, которая добавляет диапазон ячеек, Excel в Интернете записывает формулу, используя стиль R1C1, а не стиль A1, ссылки.

Использование имен в формулах

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

поиск меню

Урок 2. Простые формулы

Введение

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

Посмотрите видео ниже, чтобы узнать, как создавать формулы в Excel.

Математические операторы

Excel использует стандартные операторы для формул, такие как знак "плюс" для сложения (+), знак "минус" для вычитания (-), звездочка для умножения (*), косая черта для деления (/) и знак вставки. (^) для показателей степени.

Стандартные операторы

Все формулы в Excel должны начинаться со знака равенства (=). Это связано с тем, что ячейка содержит или равна формуле и вычисляемому ею значению.

Ссылки на ячейки

Хотя вы можете создавать простые формулы в Excel вручную (например, =2+2 или =5*5), в большинстве случаев для создания формулы вы будете использовать адреса ячеек. Это называется созданием ссылки на ячейку. Использование ссылок на ячейки гарантирует, что ваши формулы всегда будут точными, поскольку вы можете изменить значение ячеек, на которые ссылаетесь, без необходимости переписывать формулу.

Использование ссылок на ячейки для пересчета формулы

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

Примеры простых формул

Чтобы создать формулу:

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

Выбор ячейки B3

Ввод знака =

Ссылка на ячейку B1

Ссылка на ячейку B2

Полная формула и расчетное значение

Изменение значений со ссылками на ячейки

Истинное преимущество ссылок на ячейки заключается в том, что они позволяют обновлять данные на листе без необходимости переписывать формулы. В приведенном ниже примере мы изменили значение ячейки B1 с 1200 до 1800 долларов. Формула в ячейке B3 автоматически пересчитает и отобразит новое значение в ячейке B3.

Пересчитанное значение ячейки

Excel не всегда сообщит вам, если ваша формула содержит ошибку, поэтому вы должны проверить все свои формулы. Чтобы узнать, как это сделать, вы можете прочитать урок «Перепроверьте свои формулы» из нашего учебника по формулам Excel.

Чтобы создать формулу с помощью метода «укажи и щелкни»:

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

Выбор ячейки D3

Ссылка на ячейку B3

Ссылка на ячейку C3

Заполненная формула и вычисленное значение

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

Копирование формулы в соседние ячейки с помощью маркера заполнения

Чтобы изменить формулу:

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

Выбор ячейки B3

Выбор формулы для редактирования

Неуместная ссылка на ячейку

Редактирование формулы

Новое рассчитанное значение

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

Чтобы отобразить все формулы в электронной таблице, вы можете, удерживая клавишу Ctrl, нажать ` (ударение). Клавиша серьезного акцента обычно находится в верхнем левом углу клавиатуры. Вы можете снова нажать Ctrl+`, чтобы вернуться к обычному виду.

Читайте также: