В чем различие использования в формулах ms excel символов и
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel 2007 для разработчиков Excel 2010 для разработчиков Excel 2013 для разработчиков Excel для Windows Phone 10 Excel Starter 2010 Еще. Меньше
Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.
=ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)
Но что делать, если необходимо проверить несколько условий, где, допустим, все условия должны иметь значение ИСТИНА или ЛОЖЬ (И), только одно условие должно иметь такое значение (ИЛИ) или вы хотите убедиться, что данные НЕ соответствуют условию? Эти три функции можно использовать самостоятельно, но они намного чаще встречаются в сочетании с функцией ЕСЛИ.
Используйте функцию ЕСЛИ вместе с функциями И, ИЛИ и НЕ, чтобы оценивать несколько условий.
ЕСЛИ(И()): ЕСЛИ(И(лог_выражение1; [лог_выражение2]; …), значение_если_истина; [значение_если_ложь]))
ЕСЛИ(ИЛИ()): ЕСЛИ(ИЛИ(лог_выражение1; [лог_выражение2]; …), значение_если_истина; [значение_если_ложь]))
ЕСЛИ(НЕ()): ЕСЛИ(НЕ(лог_выражение1), значение_если_истина; [значение_если_ложь]))
Имя аргумента
лог_выражение (обязательно)
Условие, которое нужно проверить.
значение_если_истина (обязательно)
Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.
значение_если_ложь (необязательно)
Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.
Общие сведения об использовании этих функций по отдельности см. в следующих статьях: И, ИЛИ, НЕ. При сочетании с оператором ЕСЛИ они расшифровываются следующим образом:
И: =ЕСЛИ(И(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)
ИЛИ: =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ)
НЕ: =ЕСЛИ(НЕ(условие); значение, если ИСТИНА; значение, если ЛОЖЬ)
Примеры
Ниже приведены примеры распространенных случаев использования вложенных операторов ЕСЛИ(И()), ЕСЛИ(ИЛИ()) и ЕСЛИ(НЕ()). Функции И и ИЛИ поддерживают до 255 отдельных условий, но рекомендуется использовать только несколько условий, так как формулы с большой степенью вложенности сложно создавать, тестировать и изменять. У функции НЕ может быть только одно условие.
Ниже приведены формулы с расшифровкой их логики.
Если A2 (25) больше нуля и B2 (75) меньше 100, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае оба условия имеют значение ИСТИНА, поэтому функция возвращает значение ИСТИНА.
Если A3 ("синий") = "красный" и B3 ("зеленый") равно "зеленый", возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае только одно условие имеет значение ИСТИНА, поэтому возвращается значение ЛОЖЬ.
Если A4 (25) больше нуля или B4 (75) меньше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае только первое условие имеет значение ИСТИНА, но поскольку для ИЛИ требуется, чтобы только один аргумент был истинным, формула возвращает значение ИСТИНА.
Если значение A5 ("синий") равно "красный" или значение B5 ("зеленый") равно "зеленый", возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае второй аргумент имеет значение ИСТИНА, поэтому формула возвращает значение ИСТИНА.
Если A6 (25) НЕ больше 50, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае значение не больше чем 50, поэтому формула возвращает значение ИСТИНА.
Если значение A7 ("синий") НЕ равно "красный", возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ.
Обратите внимание, что во всех примерах есть закрывающая скобка после условий. Аргументы ИСТИНА и ЛОЖЬ относятся ко внешнему оператору ЕСЛИ. Кроме того, вы можете использовать текстовые или числовые значения вместо значений ИСТИНА и ЛОЖЬ, которые возвращаются в примерах.
Вот несколько примеров использования операторов И, ИЛИ и НЕ для оценки дат.
Ниже приведены формулы с расшифровкой их логики.
Если A2 больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае 12.03.14 больше чем 01.01.14, поэтому формула возвращает значение ИСТИНА.
Если A3 больше B2 И меньше C2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае оба аргумента истинны, поэтому формула возвращает значение ИСТИНА.
Если A4 больше B2 ИЛИ меньше B2+60, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае первый аргумент равен ИСТИНА, а второй — ЛОЖЬ. Так как для оператора ИЛИ требуется, чтобы один из аргументов был истинным, формула возвращает значение ИСТИНА. Если вы используете мастер вычисления формул на вкладке "Формулы", вы увидите, как Excel вычисляет формулу.
Если A5 не больше B2, возвращается значение ИСТИНА, в противном случае возвращается значение ЛОЖЬ. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ.
Использование операторов И, ИЛИ и НЕ с условным форматированием
Вы также можете использовать операторы И, ИЛИ и НЕ в формулах условного форматирования. При этом вы можете опустить функцию ЕСЛИ.
На вкладке Главная выберите Условное форматирование > Создать правило. Затем выберите параметр Использовать формулу для определения форматируемых ячеек, введите формулу и примените формат.
Вот как будут выглядеть формулы для примеров с датами:
Если A2 больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий.
Если A3 больше B2 И меньше C2, отформатировать ячейку, в противном случае не выполнять никаких действий.
Если A4 больше B2 ИЛИ меньше B2 + 60, отформатировать ячейку, в противном случае не выполнять никаких действий.
Если A5 НЕ больше B2, отформатировать ячейку, в противном случае не выполнять никаких действий. В этом случае A5 больше B2, поэтому формула возвращает значение ЛОЖЬ. Если изменить формулу на =НЕ(B2>A5), она вернет значение ИСТИНА, а ячейка будет отформатирована.
Примечание: Распространенная ошибка — ввод формулы в условное форматирование без знака "равно" (=). При этом вы увидите, что в диалоговом оке Условное форматирование в формулу будут добавлены знаки "равно" и кавычка: ="ИЛИ(A4>B2;A4
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
СКАЧАТЬ: metody-optimizacii.rar [308,62 Kb] (cкачиваний: 116)
Лабораторная работа №1
"Решение задач линейного программирования с использованием Microsoft Excel”
ЦЕЛЬ РАБОТЫ: Приобретение навыков решения задач линейного программирования (ЛП) в табличном редакторе Microsoft Excel.
1 ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ
1.1 Использование Microsoft Excel для решения задач ЛП
Для того чтобы решить задачу ЛП в табличном редакторе Microsoft Excel, необходимо выполнить следующие действия.
1. Ввести условие задачи:
a) создать экранную форму для ввода условия задачи:
• переменных,
• целевой функции (ЦФ),
• ограничений,
• граничных условий;
b) ввести исходные данные в экранную форму:
• коэффициенты ЦФ,
• коэффициенты при переменных в ограничениях,
• правые части ограничений;
c) ввести зависимости из математической модели в экранную форму:
• формулу для расчета ЦФ,
• формулы для расчета значений левых частей ограничений;
d) задать ЦФ (в окне "Поиск решения"):
• целевую ячейку,
• направление оптимизации ЦФ;
e) ввести ограничения и граничные условия (в окне "Поиск решения"):
• ячейки со значениями переменных,
• граничные условия для допустимых значений переменных,
• соотношения между правыми и левыми частями ограничений.
2. Решить задачу:
a) установить параметры решения задачи (в окне "Поиск решения");
b) запустить задачу на решение (в окне "Поиск решения");
c) выбрать формат вывода решения (в окне "Результаты поиска решения").
1.2 Одноиндексные задачи ЛП
Рассмотрим пример нахождения решения для следующей одноиндексной задачи ЛП:
(1.1)
1.2.1. Ввод исходных данных
Создание экранной формы и ввод в нее условия задачи
Экранная форма для ввода условий задачи (1) вместе с введенными в нее исходными данными представлена на рисунке1.
Рисунок 1 – Экранная форма задачи
Дальнейшие действия производятся в окне "Поиск решения", которое вызывается из меню "Сервис" (рис. 2):
• поставьте курсор в поле "Установить целевую ячейку";
• введите адрес целевой ячейки $G$7 или сделайте одно нажатие левой клавиши мыши на целевую ячейку в экранной форме это будет равносильно вводу адреса с клавиатуры;
• введите направление оптимизации ЦФ, щелкнув один раз левой клавишей мыши по селекторной кнопке "максимальному значению".
Рисунок 2 – Окно "Поиск решения" задачи
Рисунок 3 – Экранная форма задачи после ввода всех необходимых формул
1.3 Целочисленное программирование
При целочисленном программировании мы получим те же результаты, так как в строке значение у нас уже целые коэффициенты.
1.4 Двухиндексные задачи ЛП
Двухиндексные задачи ЛП вводятся и решаются в Excel аналогично одноиндексным задачам. Специфика ввода условия двухиндексной задачи ЛП состоит лишь в удобстве матричного задания переменных задачи и коэффициентов ЦФ.
Рисунок 4 – Ограничения и граничные условия задачи
Рисунок 5 – Экранная форма после получения решения задачи
1.5 Задачи с булевыми переменными
Частным случаем задач с целочисленными переменными являются задачи, в результате решения которых искомые переменные могут принимать только одно из двух значений: 0 или 1. Такие переменные в честь предложившего их английского математика Джорджа Буля называют булевыми. На рис.6 представлена экранная форма с решением некоторой двухиндексной задачи с булевыми переменными.
Рисунок 6 – Решение двухиндексной задачи с булевыми переменными
В задаче с булевыми переменными в окне "Поиск решения" добавить граничные условия, имеющие смысл ограничения значений переменных по их единичной верхней границе.
Рисунок 7 – Добавление условия единичной верхней границы значений переменных двухиндексной задачи с булевыми переменными.
1. Каковы основные этапы решения задач ЛП в MS Excel?
Для того чтобы решить задачу ЛП в табличном редакторе MicrosoftExcel, необходимо выполнить следующие действия.
1. Ввести условие задачи:
a) создать экранную форму для ввода условия задачи:
• переменных,
• целевой функции (ЦФ),
• ограничений,
• граничных условий;
b) ввести исходные данные в экранную форму:
• коэффициенты ЦФ,
• коэффициенты при переменных в ограничениях,
• правые части ограничений;
c) ввести зависимости из математической модели в экранную форму:
• формулу для расчета ЦФ,
• формулы для расчета значений левых частей ограничений;
d) задать ЦФ (в окне "Поиск решения"):
• целевую ячейку,
• направление оптимизации ЦФ;
e) ввести ограничения и граничные условия (в окне "Поиск решения"):
• ячейки со значениями переменных,
• граничные условия для допустимых значений переменных,
• соотношениямежду правыми и левыми частями ограничений.
2. Решить задачу:
a) установить параметры решения задачи (в окне "Поиск решения");
b) запустить задачу на решение (в окне "Поиск решения");
c) выбрать формат вывода решения (в окне "Результаты поиска решения").
2. Каков вид и способы задания формул для целевой ячейки и ячеек левых частей ограничений?
Формулу для расчета ЦФ (1.2) можно записать как сумму произведений каждой из ячеек, отведенных для значений переменных задачи (B3, C3, D3, E3), на соответствующую ячейку, отведенную для коэффициентов ЦФ (B6, C6, D6, E6), то есть
=СУММПРОИЗВ(B$3:E$3;B6:E6)
Существует другой способ задания функций в Excel с помощью режима "Вставка функций", который можно вызвать из меню "Вставка" или при нажатии кнопки " "на стандартной панели инструментов. Так, например, формулу (1.4) можно задать следующим образом:
курсор в поле F6;
• нажав кнопку " ",вызовите окно"Мастер функций – шаг 1 из 2";
• выберите в окне "Категория" категорию "Математические";
• в окне "Функция" выберитефункцию СУММПРОИЗВ;
• в появившемся окне "СУММПРОИЗВ" в строку "Массив 1" введите выражение B$3:E$3, а в строку "Массив 2" – выражение B6:E6 (рис.1.3);
• после ввода ячеек в строки "Массив 1" и "Массив 2" в окне "СУММПРОИЗВ" появятся числовые значения введенных массивов (см. рис.1.3), а в экранной форме в ячейке F6 появится текущее значение, вычисленное по введенной формуле, то есть 0 (так как в момент ввода формулы значения переменных задачи нулевые).
Левые части ограничений задачи (1.1) представляют собой сумму произведений каждой из ячеек, отведенных для значений переменных задачи (B3, C3, D3, E3), на соответствующую ячейку, отведенную для коэффициентов конкретного ограничения (B10, C10, D10, E10 – 1-е ограничение; B11, C11, D11, E11 – 2-е ограничение и B12, C12, D12, E12 – 3-е ограничение).
3. В чем смысл использования символа $ в формулах MS Excel?
Символ $ перед номером строки 3 означает, что при копировании этой формулы в другие места листа Excel номер строки 3 не изменится.
4. В чем различие использования в формулах MS Excel символов ; и : ?
Символ: означает, что в формуле будут использованы все ячейки, расположенные между ячейками, указанными слева и справа от двоеточия. Символ;используется для разделения массивов.
5. Почему при вводе формул в ячейки ЦФ и левых частей ограничений в них отображаются нулевые значения?
В экранной форме в ячейке F6 появится текущее значение, вычисленное по введенной формуле, то есть 0 (так как в момент ввода формулы значения переменных задачи нулевые).
6. Каким образом в MS Excel задается направление оптимизации ЦФ?
В окне поиска решения.
7. Какие ячейки экранной формы выполняют иллюстративную функцию, а какие необходимы для решения задачи?
Ячейки, которые не используются для вычисления ограничений или ЦФ (например, заголовки или ячейки со знаками равенства) выполняют иллюстративную функцию.
12. Объясните смысл параметров, задаваемых в окне "Параметры поиска решения".
• Параметр "Максимальное время" служит для назначения времени (в секундах), выделяемого на решение задачи. В поле можно ввести время, не превышающее 32 767 секунд (более 9 часов).
• Параметр "Предельное числоитераций"служит для управления временем решения задачи путем ограничения числа промежуточных вычислений. В поле можно ввести количество итераций, не превышающее 32 767.
• Параметр "Относительная погрешность" служит для задания точности, с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 до 1. Чем меньше количество десятичных знаков во введенном числе, тем ниже точность. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации.
• Параметр "Допустимое отклонение" служит для задания допуска на отклонение от оптимального решения в целочисленных задачах. При указании большего допуска поиск решения заканчивается быстрее.
• Параметр "Сходимость" применяется только при решении нелинейных задач.
• Установка флажка "Линейная модель" обеспечивает ускорение поиска решения линейной задачи за счет применение симплекс-метода.
• Подтвердите установленные параметры нажатием кнопки "OK".
13. Каковы особенности решения в MS Excel целочисленных задач ЛП?
Добавилось требование целочисленности значений всех переменных. В этом случае в поиске решения добавляем требование целочисленности функции.
14. Каковы особенности решения в MS Excelдвухиндексных задач ЛП?
Двухиндексные задачи ЛП вводятся и решаются в Excel аналогично одноиндексным задачам. Специфика ввода условия двухиндексной задачи ЛП состоит лишь в удобстве матричного задания переменных задачи и коэффициентов ЦФ.
15. Каковы особенности решения в MS Excel задач ЛП с булевыми переменными?
Частным случаем задач с целочисленными переменными являются задачи, в результате решения которых искомые переменные могут принимать только одно из двух значений: 0 или 1. В окне "Поиск решения" добавить граничные условия, имеющие смысл ограничения значений переменных по их единичной верхней границе (рис.1.19).
b) ввести исходные данные в экранную форму:
· коэффициенты при переменных в ограничениях,
· правые части ограничений;
c) ввести зависимости из математической модели в экранную форму:
· формулу для расчета ЦФ,
· формулы для расчета значений левых частей ограничений;
d) задать ЦФ (в окне "Поиск решения"):
· направление оптимизации ЦФ;
e) ввести ограничения и граничные условия (в окне "Поиск решения"):
· ячейки со значениями переменных,
· граничные условия для допустимых значений переменных,
· соотношениямежду правыми и левыми частями ограничений.
2. Решить задачу:
a) установить параметры решения задачи (в окне "Поиск решения");
b) запустить задачу на решение (в окне "Поиск решения");
c) выбрать формат вывода решения (в окне "Результаты поиска решения").
2 Каков вид и способы задания формул для целевой ячейки и ячеек левых частей ограничений?
Зависимость для ЦФ
В ячейку F6, в которой будет отображаться значение ЦФ, необходимо ввести формулу, по которой это значение будет рассчитано. Согласно значение ЦФ определяется выражением
. | (1.2) |
Используя обозначения соответствующих ячеек в Exceл, формулу для расчета ЦФ можно записать как сумму произведений каждой из ячеек, отведенных для значений переменных задачи (B3, C3, D3, E3), на соответствующую ячейку, отведенную для коэффициентов ЦФ (B6, C6, D6, E6), то есть
. | (1.3) |
Чтобы задать формулу (1.3) необходимо в ячейку F6 ввести следующее выражение и нажать клавишу "Enter"
=СУММПРОИЗВ(B$3:E$3;B6:E6), | (1.4) |
где символ $ перед номером строки 3 означает, что при копировании этой формулы в другие места листа Excel номер строки 3 не изменится;
символ : означает, что в формуле будут использованы все ячейки, расположенные между ячейками, указанными слева и справа от двоеточия (например, запись B6:E6 указывает на ячейки B6, C6, D6 и E6). После этого в целевой ячейке появится 0 (нулевое значение) (рис.1.2).
3 В чем смысл использования символа $ в формулах MS Excel?
Символ $ перед номером строки 3 означает, что при копировании этой формулы в другие места листа Excel номер строки 3 не изменится;
В чем различие использования в формулах MS Excel символов ; и :?
Символ : означает, что в формуле будут использованы все ячейки, расположенные между ячейками, указанными слева и справа от двоеточия (например, запись B6:E6 указывает на ячейки B6, C6, D6 и E6).
Почему при вводе формул в ячейки ЦФ и левых частей ограничений в них отображаются нулевые значения?
Так как при вводе формулы в ячейках нулевые значения
Каким образом в MS Excel задается направление оптимизации ЦФ?
направление оптимизации ЦФ задается нажатием один раз левой клавишей мыши по селекторной кнопке "максимальному значению".
ПРИМЕРНЫЕ ВОПРОСЫ НА ЗАЩИТЕ РАБОТЫ
Каковы основные этапы решения задач ЛП в MS Excel?
1. Ввести условие задачи:
a) создать экранную форму для ввода условия задачи:
· целевой функции (ЦФ),
b) ввести исходные данные в экранную форму:
· коэффициенты при переменных в ограничениях,
· правые части ограничений;
c) ввести зависимости из математической модели в экранную форму:
· формулу для расчета ЦФ,
· формулы для расчета значений левых частей ограничений;
d) задать ЦФ (в окне "Поиск решения"):
· направление оптимизации ЦФ;
e) ввести ограничения и граничные условия (в окне "Поиск решения"):
· ячейки со значениями переменных,
· граничные условия для допустимых значений переменных,
· соотношениямежду правыми и левыми частями ограничений.
2. Решить задачу:
a) установить параметры решения задачи (в окне "Поиск решения");
b) запустить задачу на решение (в окне "Поиск решения");
c) выбрать формат вывода решения (в окне "Результаты поиска решения").
2 Каков вид и способы задания формул для целевой ячейки и ячеек левых частей ограничений?
Зависимость для ЦФ
В ячейку F6, в которой будет отображаться значение ЦФ, необходимо ввести формулу, по которой это значение будет рассчитано. Согласно значение ЦФ определяется выражением
. | (1.2) |
Используя обозначения соответствующих ячеек в Exceл, формулу для расчета ЦФ можно записать как сумму произведений каждой из ячеек, отведенных для значений переменных задачи (B3, C3, D3, E3), на соответствующую ячейку, отведенную для коэффициентов ЦФ (B6, C6, D6, E6), то есть
. | (1.3) |
Чтобы задать формулу (1.3) необходимо в ячейку F6 ввести следующее выражение и нажать клавишу "Enter"
=СУММПРОИЗВ(B$3:E$3;B6:E6), | (1.4) |
где символ $ перед номером строки 3 означает, что при копировании этой формулы в другие места листа Excel номер строки 3 не изменится;
символ : означает, что в формуле будут использованы все ячейки, расположенные между ячейками, указанными слева и справа от двоеточия (например, запись B6:E6 указывает на ячейки B6, C6, D6 и E6). После этого в целевой ячейке появится 0 (нулевое значение) (рис.1.2).
3 В чем смысл использования символа $ в формулах MS Excel?
Символ $ перед номером строки 3 означает, что при копировании этой формулы в другие места листа Excel номер строки 3 не изменится;
Если вы еще не Excel в Интернете, скоро вы увидите, что это не просто сетка для ввода чисел в столбцах или строках. Да, с помощью Excel в Интернете можно найти итоги для столбца или строки чисел, но вы также можете вычислять платежи по ипотеке, решать математические или инженерные задачи или находить лучшие сценарии в зависимости от переменных чисел, которые вы подключали.
Excel в Интернете делает это с помощью формул в ячейках. Формула выполняет вычисления или другие действия с данными на листе. Формула всегда начинается со знака равенства (=), за которым могут следовать числа, математические операторы (например, знак "плюс" или "минус") и функции, которые значительно расширяют возможности формулы.
Ниже приведен пример формулы, умножающей 2 на 3 и прибавляющей к результату 5, чтобы получить 11.
Следующая формула использует функцию ПЛТ для вычисления платежа по ипотеке (1 073,64 долларов США) с 5% ставкой (5% разделить на 12 месяцев равняется ежемесячному проценту) на период в 30 лет (360 месяцев) с займом на сумму 200 000 долларов:
Ниже приведены примеры формул, которые можно использовать на листах.
=A1+A2+A3 Вычисляет сумму значений в ячейках A1, A2 и A3.
=КОРЕНЬ(A1) Использует функцию КОРЕНЬ для возврата значения квадратного корня числа в ячейке A1.
=СЕГОДНЯ() Возвращает текущую дату.
=ПРОПИСН("привет") Преобразует текст "привет" в "ПРИВЕТ" с помощью функции ПРОПИСН.
=ЕСЛИ(A1>0) Анализирует ячейку A1 и проверяет, превышает ли значение в ней нуль.
Элементы формулы
Формула также может содержать один или несколько из таких элементов: функции, ссылки, операторы и константы.
1. Функции. Функция ПИ() возвращает значение числа Пи: 3,142.
2. Ссылки. A2 возвращает значение ячейки A2.
3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.
4. Операторы. Оператор ^ ("крышка") применяется для возведения числа в степень, а оператор * ("звездочка") — для умножения.
Использование констант в формулах
Использование операторов в формулах
Операторы определяют операции, которые необходимо выполнить над элементами формулы. Вычисления выполняются в стандартном порядке (соответствующем основным правилам арифметики), однако его можно изменить с помощью скобок.
Типы операторов
Приложение Microsoft Excel поддерживает четыре типа операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.
Арифметические операторы
Арифметические операторы служат для выполнения базовых арифметических операций, таких как сложение, вычитание, умножение, деление или объединение чисел. Результатом операций являются числа. Арифметические операторы приведены ниже.
Арифметический оператор
Возведение в степень
Операторы сравнения
Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ.
Оператор сравнения
>= (знак «больше или равно»)
Больше или равно
Меньше или равно
Текстовый оператор конкатенации
Амперсанд (&) используется для объединения (соединения) одной или нескольких текстовых строк в одну.
Текстовый оператор
Соединение или объединение последовательностей знаков в одну последовательность
Выражение «Северный»&«ветер» дает результат «Северный ветер».
Операторы ссылок
Для определения ссылок на диапазоны ячеек можно использовать операторы, указанные ниже.
Оператор ссылки
Оператор диапазона, который образует одну ссылку на все ячейки, находящиеся между первой и последней ячейками диапазона, включая эти ячейки.
; (точка с запятой)
Оператор объединения. Объединяет несколько ссылок в одну ссылку.
Оператор пересечения множеств, используется для ссылки на общие ячейки двух диапазонов.
Порядок выполнения Excel в Интернете формулах
В некоторых случаях порядок вычисления может повлиять на возвращаемое формулой значение, поэтому для получения нужных результатов важно понимать стандартный порядок вычислений и знать, как можно его изменить.
Порядок вычислений
Формулы вычисляют значения в определенном порядке. Формула всегда начинается со знака равно(=).Excel в Интернете интерпретирует знаки после знака равно как формулу. После знака равно вычисляются элементы (операнды), такие как константы или ссылки на ячейки. Они разделены операторами вычислений. Excel в Интернете вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.
Приоритет операторов
Если в одной формуле несколько операторов, Excel в Интернете выполняет операции в том порядке, который показан в таблице ниже. Если формула содержит операторы с одинаковым приоритетом, например операторы деления и умножения, Excel в Интернете эти операторы оцениваются слева направо.
Возведение в степень
Умножение и деление
Сложение и вычитание
Объединение двух текстовых строк в одну
Использование круглых скобок
Чтобы изменить порядок вычисления формулы, заключите ее часть, которая должна быть выполнена первой, в скобки. Например, следующая формула дает результат 11, так как Excel в Интернете умножение выполняется перед с добавлением. В этой формуле число 2 умножается на 3, а затем к результату прибавляется число 5.
Если же изменить синтаксис с помощью скобок, Excel в Интернете сбавляет 5 и 2, а затем умножает результат на 3, чтобы получить 21.
В следующем примере скобки, в которые заключена первая часть формулы, принудительно Excel в Интернете сначала вычислить ячейки B4+25, а затем разделить результат на сумму значений в ячейках D5, E5 и F5.
Использование функций и вложенных функций в формулах
Функции — это заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления.
Синтаксис функций
Приведенный ниже пример функции ОКРУГЛ, округляющей число в ячейке A10, демонстрирует синтаксис функции.
1. Структура. Структура функции начинается со знака равно (=), за которым следуют имя функции, открывая скобка, аргументы функции, разделенные запятой, и закрывая скобка.
2. Имя функции. Чтобы отобразить список доступных функций, щелкните любую ячейку и нажмите клавиши SHIFT+F3.
4. Всплывающая подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, всплывающая подсказка появляется после ввода выражения =ОКРУГЛ(. Всплывающие подсказки отображаются только для встроенных функций.
Ввод функций
Диалоговое окно Вставить функцию упрощает ввод функций при создании формул, в которых они содержатся. При вводе функции в формулу в диалоговом окне Вставить функцию отображаются имя функции, все ее аргументы, описание функции и каждого из аргументов, текущий результат функции и всей формулы.
Чтобы упростить создание и редактирование формул и свести к минимуму количество опечаток и синтаксических ошибок, пользуйтесь автозавершением формул. После того как вы введите знак " ocpSection" role="region" aria-label="Вложенные функции">
Вложенные функции
В некоторых случаях может потребоваться использовать функцию в качестве одного из аргументов другой функции. Например, в приведенной ниже формуле для сравнения результата со значением 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
Диапазон ячеек: столбцы А-E, строки 10-20
1. Ссылка на лист «Маркетинг».
2. Ссылка на диапазон ячеек с B1 по B10 включительно.
3. Ссылка на лист, отделенная от ссылки на диапазон значений.
Различия между абсолютными, относительными и смешанными ссылками
Смешанные ссылки Смешанная ссылка имеет абсолютный столбец и относительную строку либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец принимает форму $A 1, $B 1 и так далее. Абсолютная ссылка на строку имеет форму A$1, B$1 и так далее. При изменении позиции ячейки, содержаной формулу, изменяется относительная ссылка, а абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам или вниз по столбцам относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в B3 она будет меняться с =A$1 на =B$1.
Стиль трехмерных ссылок
Удобный способ для ссылки на несколько листов Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка содержит ссылку на ячейку или диапазон, перед которой указываются имена листов. Excel в Интернете использует все таблицы, которые хранятся между начальным и конечним именами ссылки. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от листа 2 до листа 13 включительно.
При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.
Трехмерные ссылки нельзя использовать в формулах массива.
Трехмерные ссылки нельзя использовать вместе с оператор пересечения (один пробел), а также в формулах с неявное пересечение.
Вставка или копирование Если вставить листы между листами 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 в Интернете таблиц. Имя — это значимое краткое обозначение, поясняющее предназначение ссылки на ячейку, константы, формулы или таблицы, так как понять их суть с первого взгляда бывает непросто. Ниже приведены примеры имен и показано, как их использование упрощает понимание формул.
Начните создавать формулы и использовать встроенные функции, чтобы выполнять расчеты и решать задачи.
Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.
Важно: В этой статье мы обсудим похожие проблемы с просмотром и просмотром. Попробуйте использовать новую функцию ПРОСМОТРX , улучшенную версию функции ВЛОП, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее в использовании, чем предшественницу.
Создание формулы, ссылающейся на значения в других ячейках
Введите знак равенства " ocpAlert">
Примечание: Формулы в Excel начинаются со знака равенства.
Выберите ячейку или введите ее адрес в выделенной.
Введите оператор. Например, для вычитания введите знак "минус".
Выберите следующую ячейку или введите ее адрес в выделенной.
Нажмите клавишу ВВОД. В ячейке с формулой отобразится результат вычисления.
Просмотр формулы
При вводе в ячейку формула также отображается в строке формул.
Чтобы просмотреть формулу, выделите ячейку, и она отобразится в строке формул.
Ввод формулы, содержащей встроенную функцию
Выделите пустую ячейку.
Введите знак равенства "=", а затем — функцию. Например, чтобы получить общий объем продаж, нужно ввести "=СУММ".
Введите открывающую круглую скобку "(".
Выделите диапазон ячеек, а затем введите закрывающую круглую скобку ")".
Нажмите клавишу ВВОД, чтобы получить результат.
Скачивание книги "Учебник по формулам"
Мы подготовили для вас книгу Начало работы с формулами, которая доступна для скачивания. Если вы впервые пользуетесь Excel или даже имеете некоторый опыт работы с этой программой, данный учебник поможет вам ознакомиться с самыми распространенными формулами. Благодаря наглядным примерам вы сможете вычислять сумму, количество, среднее значение и подставлять данные не хуже профессионалов.
Подробные сведения о формулах
Чтобы узнать больше об определенных элементах формулы, просмотрите соответствующие разделы ниже.
Формула также может содержать один или несколько таких элементов, как функции, ссылки, операторы и константы.
Части формулы
1. Функции. Функция ПИ() возвращает значение числа пи: 3,142.
2. Ссылки. A2 возвращает значение ячейки A2.
3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.
4. Операторы. Оператор ^ (крышка) применяется для возведения числа в степень, а * (звездочка) — для умножения.
Ссылка указывает на ячейку или диапазон ячеек листа и сообщает Microsoft 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
Диапазон ячеек: столбцы А-E, строки 10-20
Создание ссылки на ячейку или диапазон ячеек с другого листа в той же книге
В приведенном ниже примере функция СРЗНАЧ вычисляет среднее значение в диапазоне B1:B10 на листе "Маркетинг" в той же книге.
1. Ссылка на лист "Маркетинг".
2. Ссылка на диапазон ячеек от B1 до B10
3. Восклицательный знак (!) отделяет ссылку на лист от ссылки на диапазон ячеек.
Примечание: Если название упоминаемого листа содержит пробелы или цифры, его нужно заключить в апострофы ('), например так: '123'!A1 или ='Прибыль за январь'!A1.
Различия между абсолютными, относительными и смешанными ссылками
Относительные ссылки . Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании или заполнении формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании или заполнении относительной ссылки из ячейки B2 в ячейку B3 она автоматически изменяется с =A1 на =A2.
Скопированная формула с относительной ссылкой
Абсолютные ссылки . Абсолютная ссылка на ячейку в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам и столбцам абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, а для использования абсолютных ссылок надо активировать соответствующий параметр. Например, при копировании или заполнении абсолютной ссылки из ячейки B2 в ячейку B3 она остается прежней в обеих ячейках: =$A$1.
Скопированная формула с абсолютной ссылкой
Смешанные ссылки . Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец имеет вид $A1, $B1 и т. д. Абсолютная ссылка на строку имеет вид A$1, B$1 и т. д. Если положение ячейки с формулой изменяется, относительная ссылка меняется, а абсолютная — нет. При копировании или заполнении формулы по строкам и столбцам относительная ссылка автоматически изменяется, а абсолютная ссылка не корректируется. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в ячейку B3 она автоматически изменяется с =A$1 на =B$1.
Скопированная формула со смешанной ссылкой
Стиль трехмерных ссылок
Удобный способ для ссылки на несколько листов . Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка содержит ссылку на ячейку или диапазон, перед которой указываются имена листов. В Microsoft Excel используются все листы, указанные между начальным и конечным именами в ссылке. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист13 включительно.
При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.
Трехмерные ссылки нельзя использовать в формулах массива.
Трехмерные ссылки нельзя использовать вместе с оператор пересечения (один пробел), а также в формулах с неявное пересечение.
Что происходит при перемещении, копировании, вставке или удалении листов . Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой.
Вставка или копирование . Если вставить листы между листами 2 и 6, Microsoft Excel прибавит к сумме содержимое ячеек с A2 по A5 на новых листах.
Удаление . Если удалить листы между листами 2 и 6, Microsoft Excel не будет использовать их значения в вычислениях.
Перемещение . Если листы, находящиеся между листом 2 и листом 6, переместить таким образом, чтобы они оказались перед листом 2 или после листа 6, Microsoft Excel вычтет из суммы содержимое ячеек с перемещенных листов.
Перемещение конечного листа . Если переместить лист 2 или 6 в другое место книги, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.
Удаление конечного листа . Если удалить лист 2 или 6, Microsoft Excel скорректирует сумму с учетом изменения диапазона листов.
Стиль ссылок R1C1
Можно использовать такой стиль ссылок, при котором нумеруются и строки, и столбцы. Стиль ссылок R1C1 удобен для вычисления положения столбцов и строк в макросах. При использовании стиля R1C1 в Microsoft Excel положение ячейки обозначается буквой R, за которой следует номер строки, и буквой C, за которой следует номер столбца.
относительная ссылка на ячейку, расположенную на две строки выше в том же столбце
Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее
Абсолютная ссылка на ячейку, расположенную во второй строке второго столбца
Относительная ссылка на строку, расположенную выше текущей ячейки
Абсолютная ссылка на текущую строку
При записи макроса в Microsoft Excel для некоторых команд используется стиль ссылок R1C1. Например, если записывается команда щелчка элемента Автосумма для вставки формулы, суммирующей диапазон ячеек, в Microsoft Excel при записи формулы будет использован стиль ссылок R1C1, а не A1.
Чтобы включить или отключить использование стиля ссылок R1C1, установите или снимите флажок Стиль ссылок R1C1 в разделе Работа с формулами категории Формулы в диалоговом окне Параметры. Чтобы открыть это окно, перейдите на вкладку Файл.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Читайте также: