Что значит два минуса в формуле excel
Обновлено: 16.05.2024
Функция ЕСЛИ() относится к наиболее часто используемым функциям. Поэтому рассмотрим ее подробно.
Синтаксис функции
ЕСЛИ(лог_выражение;значение_если_истина;[значение_если_ложь])
Функция имеет 3 аргумента.
Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, выражение A1>100. Если в ячейке А1 содержится значение 102, то выражение A1>100 вернет ИСТИНА, т.к. 102>100. Если в ячейке А1 содержится значение 88, то выражение вернет ЛОЖЬ, т.к. неравенство 88>100 неверно. Полностью формула может выглядеть так =ЕСЛИ(A1>100;"Бюджет проекта превышен";"ОК!") Если в ячейке A1 содержится значение большее 100, то формула вернет текстовое значение Бюджет проекта превышен, а если меньше 100 или равное 100, то будет выведено ОК!
Второй аргумент - Значение_если_истина и третий аргумент значение_если_ложь
Как было показано выше, если логическое выражение (первый аргумент функции) принимает значение ИСТИНА, то функция возвращает значение из второго аргумента значение_если_истина. Соответственно, если логическое выражение принимает значение ЛОЖЬ, то функция возвращает значение из третьего аргумента значение_если_ложь .
Второй и третий аргумент могут быть любым значением (число или текст) или являться выражением (формулой с функциями). Например формула =ЕСЛИ(A1>100;0;А1*1%) позволяет вычислять премию для проектов, в которых не было превышения бюджета (>100). Если бюджет превышен (в А1 введено значение 200 или любое другое более 100), то формула вернет 0, если в А1 введено значение менее 100, то будет вычислено значение премии А1*1% (т.е. если в А1 число 90, то формула вернет 0,9).
Если в качестве второго и третьего аргументов введено выражение, то оно может содержать и функции, например: =ЕСЛИ(A1>100;СУММ(B1:B10);СУММ(C1:C10)) Т.е. если в ячейке A1 содержится значение >100, то суммирование ведется по столбцу B , а если меньше или равно 100, то по столбцу С . Не забывайте, что при значении А1 = 100 в данном случае будет срабатывать выражение из аргумента значение_если_ложь.
Аргумент значение_если_ложь можно опустить (не вводить), тогда при значении логического выражения =ЛОЖЬ будет выведено ЛОЖЬ. Например, если в А1 содержится 600, то формула =ЕСЛИ(A1>=1000;"Больше или равно 1000") вернет просто ЛОЖЬ. Понятно, что при значении логического выражения =ИСТИНА будет выведено "Больше или равно 1000", т.е. второй аргумент функции ЕСЛИ() - Значение_если_истина.
Вложенные ЕСЛИ (несколько условий)
Для построения более сложных проверок в одной формуле можно использовать несколько функций ЕСЛИ(). Для этого в качестве значений аргументов значение_если_истина и значение_если_ложь введите еще одно условие с функцией ЕСЛИ(). Например, для проверки в какой диапазон попадает значение из ячейки А8 запишите формулу
- Если значение из ячейки А8 менее 5, то будет выведено 1
- Если значение из ячейки А8 более 10, то будет выведено 3
- Если значение из ячейки А8 между 5 и 10 (включая 5 и 8), то будет выведено 2
Примечание : В версии EXCEL 2007 можно использовать до 64 вложенных друг в друга функций ЕСЛИ(). Но, пожалуйста, не делайте так. Такой подход чреват ошибками.
Примечание : Формулу =ЕСЛИ(A8 10;3;2)) можно переписать как =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) или ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1)
или более сложный вариант =ЕСЛИ(A8
Формула =ЕСЛИ(A8 10;3;2)) работает следующим образом: сначала вычисляется логическое выражение A8 =5, то EXCEL переходит к вычислению выражения ЕСЛИ(A8>10;3;2). Опять сначала вычисляется логическое выражение A8>10. Если оно истинно, то выводится 3, вычисления завершены. Если оно ложно, то выводится 2, вычисления завершены.
Чтобы грамотно написать формулу с вложенными ЕСЛИ() следуйте следующим простым правилам:
1) Определитесь с диапазонами:
1.1) они не должны пересекаться. В примере выше - 3 диапазона и значение из А8 гарантированно попадет только в один из них. Было бы ошибкой написать формулу типа =ЕСЛИ(A8 10;3;ЕСЛИ(A8 10;3;2)) на интересует вся числовая ось от минус бесконечности до +бесконечности. И все 3 диапазона полностью покрывают эту область.
1.3.) Не забудьте ставить знак равно, чтобы при значениях А8 совпадающих с границами интервалов не было неправильных вычислений. Например, если в формуле =ЕСЛИ(A8>=5;ЕСЛИ(A8>10;3;2);1) забыть указать знак = в логическом выражении A8>=5, то при А8=5 формула =ЕСЛИ(A8>5;ЕСЛИ(A8>10;3;2);1) вернет 1, тогда как исходная формула вернет 2.
Примечание: В перечисленных выше случаях логические выражения вычисляются на основании одной переменной А8. Иногда нужно придумывать формулы с ЕСЛИ(), зависящие от 2-х и более ячеек: =ЕСЛИ(B8>=5;ЕСЛИ(A8>10;3;2);1). Это гораздо сложнее, но, конечно, возможно. Следуйте правилам 1.1)-1.3) и следите за диапазонами. Еще сложнее, если обе переменных являются зависимыми, т.е. например B8 вычисляется на основании А8. Написание таких формул требует предельной аккуратности и напрямую связано с корректностью формулирования самой задачи (возможно вы не продумали до конца как использовать такую сложную зависимость). Советую по возможности разбить вычисления на 2 или более формулы, чтобы проще контролировать результат вычисления.
2) Правильно записывайте логические выражения. Например, условие 5 10;3;2)) в строке формул можно записать как
Для этого поставьте курсор перед вторым ЕСЛИ(), нажмите и удерживайте клавишу ALT, нажмите ENTER.
4) для отладки вложенных ЕСЛИ вместо реальных вычислений (V8*10%-37500) используйте простые значения - последовательности 1; 2; 3 . Это покажет какой блок вложенных ЕСЛИ() срабатывает.
5) В ряде случаев от вложенных ЕСЛИ можно отказаться. Рассмотрим пример определения размера проекта от его бюджета
=ЕСЛИ(A1>=100;"Очень крупный проект";
ЕСЛИ(A1>=90;"Крупный проект";
ЕСЛИ(A1>=50;"Средний проект";"Малый проект ")))
Вышеуказанную формулу можно усложнять и дальше, но есть другие подходы. Ниже приведено 2 эквивалентные формулы:
Для функции ВПР() необходимо создать в диапазоне A3:B6 таблицу значений:
Формула =ПРОСМОТР(A1;;) выглядит необычно, но по сути второй аргумент , как и третий аргумент это запись содержимого 4-х ячеек. Вместо можно записать ссылку на диапазон ячеек, содержащих 4 значения 0;50;90;100. Круглые скобки нужны, чтобы сообщить, что в функцию передается не одно значение, а целый массив.
Еще пример. Пусть требуется вывести разный текст (например " 0") в случае наличия в ячейке А1 отрицательного значения, положительного значения или 0. Это можно сделать без вложенных ЕСЛИ():
Примечание : -1E+307 - это самое маленькое отрицательное число в EXCEL, 1E-307 - это самое маленькое положительное число. Таким образом задается 3 диапазона: от минус бесконечности до 0 (0 не включая); 0 (диапазон состоит только из одного значения 0); от 0 до +бесконечности (0 не включая).
Второй массив можно заменить ссылкой на диапазон, например для того чтобы вывести результат вычисления формул:
Диапазон A27:A29 должен, понятно, содержать формулы (см. файл примера).
Опущен третий аргумент [значение_если_ложь]
Третий аргумент функции не обязателен. Если логическое условие не выполнено, т.е. ЛОЖЬ, а третий аргумент функции не указан, то функция вернет также значение ЛОЖЬ. Например:
Если в ячейке A1 содержится значение 1, то вышеуказанная формула вернет значение ЛОЖЬ.
Сложные логические выражения
В аргументе Лог_выражение можно использовать как простые сравнения типа F1>10, так и более сложные конструкции на основе функций И(), ИЛИ(), НЕ() и их комбинаций.
Основное правило - логические функции и их комбинации возвращают только одно из 2х логических значений: ЛОЖЬ или ИСТИНА.
Приведем несколько полезных примеров.
Формула =ЕСЛИ(ИЛИ(A1;A2);0;2) проверяет одновременность равенства значений А1 и А2 нулю. Если оба значения равны 0, то формула вернет 2. Во всех других случаях будет возвращен 0. А1 и А2 не должны быть пустыми ячейками.
Формула =ЕСЛИ(ИЛИ(A1=0;A2=0);0;2) проверяет равенство 0 хотя бы одного значения из А1 и А2.
Формула =ЕСЛИ(НЕ(A1>0);1;2) эквивалентна формуле =ЕСЛИ(A1 10") . Аналогичный результат (23) можно получить с помощью формулы массива =СУММ(ЕСЛИ(A6:A11>10;A6:A11)) (для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER )
Теперь подсчитаем количество вхождений чисел больше 10 в диапазоне ячеек A6:A11 =СЧЁТЕСЛИ(A6:A11;">10") . Аналогичный результат (2) можно получить с помощью формулы массива =СЧЁТ(ЕСЛИ(A6:A11>10;A6:A11))
Теперь, когда принцип понятен, с помощью функции ЕСЛИ() можно конструировать и другие формулы с условиями. Например, нахождение минимального значения среди чисел больше 10: =МИН(ЕСЛИ(A6:A11>10;A6:A11))
Если вы еще не 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. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.
<c0>Предельное количество уровней вложенности функций</c0>. В формулах можно использовать до семи уровней вложенных функций. Если функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, в приведенном выше примере функции СРЗНАЧ и СУММ являются функциями второго уровня, поскольку обе они являются аргументами функции ЕСЛИ. Функция, вложенная в качестве аргумента в функцию СРЗНАЧ, будет функцией третьего уровня, и т. д.
Использование ссылок в формулах
Ссылка указывает на ячейку или диапазон ячеек на сайте и сообщает 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
<c0>Ссылка на другой лист</c0>. В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения диапазона B1:B10 на листе «Маркетинг» той же книги.
1. Ссылка на лист «Маркетинг».
2. Ссылка на диапазон ячеек с B1 по B10 включительно.
3. Ссылка на лист, отделенная от ссылки на диапазон значений.
Различия между абсолютными, относительными и смешанными ссылками
<c0>Относительные ссылки</c0>. Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании или заполнении формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании или заполнении относительной ссылки из ячейки B2 в ячейку B3 она автоматически изменяется с =A1 на =A2.
<c0>Абсолютные ссылки</c0>. Абсолютная ссылка на ячейку в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам и столбцам абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, а для использования абсолютных ссылок надо активировать соответствующий параметр. Например, при копировании или заполнении абсолютной ссылки из ячейки B2 в ячейку B3 она остается прежней в обеих ячейках: =$A$1.
Смешанные ссылки Смешанная ссылка имеет абсолютный столбец и относительную строку либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец принимает форму $A 1, $B 1 и так далее. Абсолютная ссылка на строку имеет форму A$1, B$1 и так далее. При изменении позиции ячейки, содержаной формулу, изменяется относительная ссылка, а абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам или вниз по столбцам относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в B3 она будет меняться с =A$1 на =B$1.
Стиль трехмерных ссылок
Удобный способ для ссылки на несколько листов Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка содержит ссылку на ячейку или диапазон, перед которой указываются имена листов. Excel в Интернете использует все таблицы, которые хранятся между начальным и конечним именами ссылки. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от листа 2 до листа 13 включительно.
При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.
Трехмерные ссылки нельзя использовать в формулах массива.
Трехмерные ссылки нельзя использовать вместе с оператор пересечения (один пробел), а также в формулах с неявное пересечение.
<c0>Что происходит при перемещении, копировании, вставке или удалении листов</c0>. Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой.
Вставка или копирование Если вставить листы между листами 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 в Интернете таблиц. Имя — это значимое краткое обозначение, поясняющее предназначение ссылки на ячейку, константы, формулы или таблицы, так как понять их суть с первого взгляда бывает непросто. Ниже приведены примеры имен и показано, как их использование упрощает понимание формул.
Англоязычный вариант: =SUM(5; 5) или =SUM(A1; B1) или =SUM(A1:B5)
Функция СУММ позволяет вычислить сумму двух или более чисел. В этой формуле вы также можете использовать ссылки на ячейки.
С помощью формулы вы можете:
- посчитать сумму двух чисел c помощью формулы: =СУММ(5; 5)
- посчитать сумму содержимого ячеек, сссылаясь на их названия: =СУММ(A1; B1)
- посчитать сумму в указанном диапазоне ячеек, в примере во всех ячейках с A1 по B6: =СУММ(A1:B6)
Англоязычный вариант: =COUNT(A1:A10)
Данная формула подсчитывает количество ячеек с числами в одном ряду. Если вам необходимо узнать, сколько ячеек с числами находятся в диапазоне c A1 по A30, нужно использовать следующую формулу: =СЧЁТ(A1:A30).
СЧЁТЗ
Англоязычный вариант: =COUNTA(A1:A10)
С помощью данной формулы можно подсчитать количество заполненных ячеек в одном ряду, то есть тех, в которых есть не только числа, но и другие знаки. Преимущество формулы – её можно использовать для работы с любым типом данных.
ДЛСТР
Англоязычный вариант: =LEN(A1)
Функция ДЛСТР подсчитывает количество знаков в ячейке. Однако, будьте внимательны – пробел также учитывается как знак.
СЖПРОБЕЛЫ
Англоязычный вариант: =TRIM(A1)
Данная функция помогает избавиться от пробелов, не включая при этом пробелы между словами. Эта опция может быть чрезвычайно полезной, особенно в тех ситуациях, когда вы вносите в таблицу данные из другого источника и при вставке появляются лишние пробелы.
Мы добавили лишний пробел после фразы “Я люблю Excel”. Формула СЖПРОБЕЛЫ убрала его, в этом вы можете убедиться, взглянув на количество знаков с использованием формулы и без.
ЛЕВСИМВ, ПСТР и ПРАВСИМВ
=ЛЕВСИМВ(адрес_ячейки; количество знаков)
=ПРАВСИМВ(адрес_ячейки; количество знаков)
=ПСТР(адрес_ячейки; начальное число; число знаков)
Англоязычный вариант: =RIGHT(адрес_ячейки; число знаков), =LEFT(адрес_ячейки; число знаков), =MID(адрес_ячейки; начальное число; число знаков).
Эти формулы возвращают заданное количество знаков текстовой строки. ЛЕВСИМВ возвращает заданное количество знаков из указанной строки слева, ПРАВСИМВ возвращает заданное количество знаков из указанной строки справа, а ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.
Мы использовали ЛЕВСИМВ, чтобы получить первое слово. Для этого мы ввели A1 и число 1 – таким образом, мы получили «Я».
Мы использовали ПСТР, чтобы получить слово посередине. Для этого мы ввели А1, поставили 3 как начальное число и затем ввели число 6 – таким образом, мы получили «люблю» из фразы «Я люблю Excel».
Мы использовали ПРАВСИМВ, чтобы получить последнее слово. Для этого мы ввели А1 и число 6 – таким образом, мы получили слово «Excel» из фразы «Я люблю Excel».
Формула: =ВПР(искомое_значение; таблица; номер_столбца; тип_совпадения)
Англоязычный вариант: =VLOOKUP (искомое_значение; таблица; номер_столбца; тип_совпадения)
Функция ВПР работает как телефонная книга, где по фрагменту известных данных – имени, вы находите неизвестные сведения – номер телефона. В формуле необходимо задать искомое значение, которое формула должна найти в столбце таблицы.
Например, у вас есть два списка: первый с паспортными данными сотрудников и их доходами от продаж за последний квартал, а второй – с их паспортными данными и именами. Вы хотите сопоставить имена с доходами от продаж, но, делая это вручную, можно легко ошибиться.
- В первом списке данные записаны с А1 по В13, во втором – с D1 по Е13.
- В ячейке B17 поставим формулу: =ВПР(B16; A1:B13; 2; ЛОЖЬ)
- B16 = искомое значение, то есть паспортные данные. Они имеются в обоих списках.
- A1:B13 = таблица, в которой находится искомое значение.
- 2 – номер столбца, где находится искомое значение.
- ЛОЖЬ – логическое значение, которое означает то, что вам требуется точное совпадение возвращаемого значения. Если вам достаточно приблизительного совпадения, указываете ИСТИНА, оно также является значением по умолчанию.
Эта формула не такая простая, как предыдущие, тем не менее она очень полезна в работе.
Формула: =ЕСЛИ(логическое_выражение; "текст, если логическое выражение истинно; "текст, если логическое выражение ложно")
Англоязычный вариант: =IF(логическое_выражение; "текст, если логическое выражение истинно; "текст, если логическое выражение ложно")
Когда вы проводите анализ большого объёма данных в Excel, есть множество сценариев для взаимодействия с ними. В зависимости от каждого из них появляется необходимость по‑разному воздействовать на данные. Функция «ЕСЛИ» позволяет выполнять логические сравнения значений: если что‑то истинно, то необходимо сделать это, в противном случае сделать что‑то ещё.
Снова обратимся к примеру из сферы продаж: допустим, что у каждого продавца есть установленная норма по продажам. Вы использовали формулу ВПР, чтобы поместить доход рядом с именем. Теперь вы можете использовать оператор «ЕСЛИ», который будет выражать следующее: «ЕСЛИ продавец выполнил норму, вывести выражение «Норма выполнена», если нет, то «Норма не выполнена».
В примере с ВПР у нас был доход в столбце B и имя человека в столбце E. Мы можем поместить квоту в столбце C, а следующую формулу – в ячейку D1:
=ЕСЛИ(B1>C1; "Норма выполнена"; "Норма не выполнена")
Функция «ЕСЛИ» покажет нам, выполнил ли первый продавец свою норму или нет. После можно скопировать и вставить эту формулу для всех продавцов в списке, значение автоматически изменится для каждого работника.
СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ
Формула: =СУММЕСЛИ(диапазон; условие; диапазон_суммирования) =СЧЁТЕСЛИ(диапазон; условие)
=СРЗНАЧЕСЛИ(диапазон; условие; диапазон_усреднения)
Англоязычный вариант: =SUMIF(диапазон; условие; диапазон_суммирования), =COUNTIF(диапазон; условие), =AVERAGEIF(диапазон; условие; диапазон_усреднения)
Эти формулы выполняют соответствующие функции – СУММ, СЧЁТ, СРЗНАЧ, если выполнено заданное условие.
Формулы с несколькими условиями – СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН – выполняют соответствующие функции, если все указанные критерии соответствуют истине.
Используя функции на предыдущем примере, мы можем узнать:
СУММЕСЛИ – общий доход только для продавцов, выполнивших норму.
СРЗНАЧЕСЛИ – средний доход продавца, если он выполнил норму.
СЧЁТЕСЛИ – количество продавцов, выполнивших норму.
Конкатенация
Формула: =(ячейка1&" "&ячейка2)
За этим причудливым словом скрывается объединение данных из двух и более ячеек в одной. Сделать объединение можно с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если в ячейке A1 находится имя «Иван», в ячейке B1 – фамилия «Петров», их можно объединить с помощью формулы =A1&" "&B1. Результат – «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между " ", чтобы между объединёнными данными появился пробел.
Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;" "; B1) или в англоязычном варианте =concatenate(A1;" "; B1).
Кстати, все перечисленные формулы можно применять и в Google‑таблицах.
Эта статья является лишь верхушкой айсберга в изучении Excel. Для профессионального использования программы рекомендуем учится у профессионалов на курсах по Microsoft Excel.
Сервис для работы с таблицами Microsoft Excel часто используется для систематизации данных в числовом формате и проведения вычислений с ними. Вычитание – одно из основных математических действий, без него не обходится ни одно сложное вычисление. Есть несколько способов внедрить в таблицу ячейки с вычитанием, каждый из которых будет подробно разобран ниже.
Как в Экселе сделать функцию вычитания
Вычитание в таблице происходит так же, как и на бумаге. Выражение должно состоять из уменьшаемого, вычитаемого и знака «-» между ними. Можно вписать уменьшаемое и вычитаемое вручную или выбрать ячейки с этими данными.
Обратите внимание! Существует одно условие, отличающее вычитание в Excel от обычного действия. Любая функция в этой программе начинается со знака равенства. Если не поставить этот знак перед составленным выражением, результат не появится в ячейке автоматически. Программа воспримет написанное как текст. По этой причине важно всегда ставить в начале знак «=».
Необходимо составить формулу со знаком «-», проверить правильность выбора ячеек или записи чисел и нажать «Enter». В ячейке, где была написана формула, немедленно появится разность двух или большего количества чисел. К сожалению, в Менеджере функций нет готовой формулы вычитания, поэтому придется пойти другими путями. Воспользоваться каталогом формул получится только для более сложных вычислений, например, тех, где используются комплексные числа. Рассмотрим все работающие способы далее.
Процедура вычитания
Сначала, как и говорилось, необходимо написать в сроке функций или в самой ячейке знак равенства. Это показывает, что значение ячейки равно результату математического действия. Далее в выражении должно появится уменьшаемое – число, которое станет меньше в результате расчета. Второе число – это вычитаемое, на него становится меньше первое. Между числами ставится минус. Не нужно делать тире из дефиса, иначе действие не сработает. Исследуем пять способов вычитания в таблицах Excel. Каждый пользователь сможет выбрать для себя удобный метод из этого списка.
Пример 1: разность конкретных чисел
Таблица составлена, ячейки заполнены, но теперь нужно вычесть один показатель из другого. Попробуем вычесть одно известное число из другого.
- Для начала необходимо выбрать ячейку, в которой окажется результат вычисления. Если на листе составлена таблица, и в ней есть колонка для таких значений, следует остановиться на одной из ячеек в этой колонке. В примере мы рассмотрим вычитание в случайной ячейке.
- Кликните по ней дважды, чтобы внутри появилось поле для заполнения. В это поле нужно вписать выражение по форме, которая была описана ранее: знак «=», уменьшаемое, знак минус и вычитаемое. Писать выражение можно и в строке функций, которая расположена над листом. Результат всех этих действий выглядит так:
Обратите внимание! Вычитаемых может быть сколько угодно, это зависит от цели вычисления. Перед каждым из них необходим минус, иначе расчеты не будут проведены правильно.
- Если числа в выражении и прочие его части записаны верно, следует нажать клавишу «Enter» на клавиатуре. В выбранной ячейке тут же появится разность, а в строке функций можно будет просмотреть записанное выражение и проверить его на ошибки. После проведения автоматических вычислений экран выглядит так:
Табличный процессор Microsoft Excel предназначен в том числе для удобного проведения расчетов, поэтому он работает с положительными и отрицательными числами. Не обязательно, чтобы уменьшаемым было большее число, но тогда результат будет меньше нуля.
Пример 2: вычитание числа из ячейки
Работа с ячейками таблиц – главная задача Эксель, поэтому с ними можно проводить самые разные действия. Например, можно составить математическое выражение, где уменьшаемым будет ячейка, а вычитаемым – число или наоборот.
- Первым действием снова выбираем ячейку для формулы и ставим в ней знак равенства.
- Далее нужно действовать иначе, чем в первом способе – необходимо найти в таблице ячейку со значением, которое уменьшится в результате вычитания, и кликнуть по ней. Вокруг этой ячейки образуется подвижный пунктирный контур, а в формуле появится ее обозначение в виде буквы и числа.
- Далее ставим знак «-», а после него вручную записываем в формулу вычитаемое. Должно получиться выражение такого вида:
- Чтобы запустить расчет, нужно нажать клавишу «Enter». Во время вычислений программа вычтет число из содержимого ячейки. Точно так же в ячейке с формулой появится результат. Пример результата:
Пример 3: разность между числами в ячейках
Не обязательно, чтобы в выражении было даже одно конкретное число – все действия можно совершать только с ячейками. Это удобно, когда в таблице много столбцов, и нужно быстро подсчитать итоговый результат с помощью вычитания.
- Расчет начинается с постановки знака равенства в выбранной ячейке.
- После этого необходимо найти ячейку, в которой содержится уменьшаемое. Важно не перепутать части таблицы между собой, потому что вычитание отличается от сложения строгим порядком записи выражения.
- После клика по ней в функции окажется название в виде обозначений строки и столбца, например, A2, C12 и прочее. Ставим минус и находим в таблице ячейку с вычитаемым.
- По ней тоже нужно кликнуть, и выражение станет законченным – в него автоматически попадет обозначение вычитаемого. Можно добавить сколько угодно вычитаемых и действий – программа все автоматически вычислит. Взгляните на то, как выглядит финальное выражение:
- Жмем клавишу «Enter» и получаем разность содержимого нескольких ячеек без лишних действий в виде копирования или повторного ручного введения чисел.
Важно! Главное правило использования этого способа – следить, чтобы ячейки в выражении оказались на своих местах.
Пример 4: вычитание одного столбца из другого
Возникают случаи, когда нужно вычесть содержимое ячеек одного столбца из ячеек другого. Пользователи нередко начинают прописывать отдельные формулы для каждой строки, но это очень трудоемкий процесс. Чтобы сэкономить время, которое тратится на составление нескольких десятков выражений, можно вычесть один столбец из другого одной функцией.
Причины воспользоваться этим методом могут быть разными, но одна из самых распространенных – необходимость высчитать прибыль. Для этого нужно вычесть из суммы дохода себестоимость сбытой продукции. Рассмотрим способ вычитания на таком примере:
- Необходимо дважды кликнуть по верхней ячейке пустого столбца, вписать знак «=».
- Далее нужно составить формулу: выделяем ячейку с выручкой, ставим после ее обозначения в функции минус и нажимаем на ячейку с себестоимостью.
Внимание! Если ячейки выбраны правильно, не стоит нажимать на другие элементы листа. Легко не заметить, что уменьшаемое или вычитаемое случайно изменилось из-за такой ошибки.
- Разность появится в ячейке после нажатия клавиши «Enter». Перед тем, как выполнять остальные шаги, нужно запустить вычисление.
- Взгляните на правый нижний угол выбранной ячейки – там находится маленький квадрат. При наведении на него курсора стрелка превращается в черный крест — это маркер заполнения. Теперь нужно зажать курсором правый нижний угол ячейки и потянуть вниз до последней ячейки, входящей в таблицу.
Важно! Выделение нижних ячеек после зажатия контура верхней ячейки в других местах не приведет к переносу формулы на строки ниже.
Пример 5: вычитание конкретного числа из столбца
Иногда пользователям необходимо, чтобы при копировании происходило только частичное смещение, то есть, чтобы одна ячейка в функции оставалась неизменной. Такое тоже возможно благодаря табличному процессору Microsoft Excel.
- Начать снова следует с выбора свободной ячейки и элементов выражения, постановки знаков «=» и «-». Представим, что в конкретном случае вычитаемое должно оставаться неизменным. Формула приобретает стандартный вид:
- Перед обозначениями ячейки вычитаемого, буквой и цифрой нужно поставить знаки доллара. Это закрепит вычитаемое в формуле, не позволит ячейке изменяться.
- Запустим расчет кликом по клавише «Enter», в первой строке столбца появится новое значение.
- Теперь можно заполнить весь столбец. Необходимо зажать маркер в правом нижнем углу первой ячейки и выделить оставшиеся части столбца.
- Проведется вычисление со всеми нужными ячейками, при этом вычитаемое не изменится. Проверить это можно кликом на одну из выделенных ячеек – в строке функций появится выражение, которым она заполнена. Итоговый вариант таблицы выглядит так:
Бессменной ячейкой может стать и уменьшаемое – зависит от того, где поставить знаки «$». Показанный пример – это частный случай, не всегда формула должна выглядеть именно так. Количество компонентов выражения может быть любым.
Вычитание чисел в интервалах
Вычесть одно число из содержимого столбца можно с помощью функции СУММ.
- Выберем свободную ячейку и откроем «Менеджер функций».
- Необходимо найти функцию СУММ и выбрать ее. Появится окно для заполнения функции значениями.
- Выделяем все ячейки строки уменьшаемого, где есть значения, интервал попадет в строку «Число 1», следующую строку заполнять не нужно.
- После нажатия на кнопку «ОК» в окне выбора чисел в ячейке появится сумма всех ячеек уменьшаемого, но это еще не конец – нужно провести вычитание.
- Дважды кликаем на ячейку с формулой и добавляем после закрывающей скобки минус.
- Далее нужно выбрать ячейку-вычитаемое. В итоге формула должна выглядеть так:
- Теперь можно нажать «Enter», и в ячейке появится нужный результат.
- Вычитаемым может стать еще один интервал, для этого нужно еще раз использовать функцию СУММ после минуса. В итоге один интервал вычитается из другого. Немного дополним таблицу значениями в столбце вычитаемого для наглядности:
Функция IMSUBTR
На русском эта функция называется МНИМ.РАЗН. Это одна из инженерных функций, с ее помощью можно вычислить разность комплексных чисел. Комплексное число состоит из действительных и мнимых единиц. Несмотря на то, что между единицами стоит плюс, эта запись является единым числом, а не выражением. В реальности представить такое явление невозможно, оно является сугубо математическим. Комплексные числа можно изобразить на плоскости в виде точек.
Мнимая разность – это сочетание разностей действительных и мнимых частей комплексного числа. Результат вычитания вне таблицы:
(10+2i)-(7+10i) = 3-8i
2i-10i= -8i
- Чтобы провести вычисления, выберем пустую ячейку, откроем «Менеджер функций» и найдем там функцию МНИМ.РАЗН. Она находится в разделе «Инженерные».
- В окне выбора чисел нужно заполнить обе строки – в каждой должно быть по одному комплексному числу. Для этого необходимо кликнуть по первой строке, а потом – по первой ячейке с числом, то же сделать со второй строкой и ячейкой. Итоговая формула выглядит так:
- Далее нажимаем «Enter» и получаем результат. В формуле не бывает больше одного вычитаемого, можно рассчитать мнимую разность только двух ячеек.
Заключение
Благодаря инструментам Excel вычитание превращается в легкую математическую операцию. Программа позволяет выполнять как самые простые действия со знаком «минус», так и заниматься узконаправленными вычислениями с использованием комплексных чисел. Пользуясь описанными выше методами, можно успеть намного больше во время работы с таблицами.
В данной статье рассмотрим функцию MS Excel, которая называется ЕСЛИ. Данная функция позволяет проверить, соответствует ли содержащиеся в указанной ячейки данные заданному критерию или нет. В зависимости от результата проверки функция выводит заданное значение. Чтобы понять суть и возможности функции ЕСЛИ, рассмотрим примеры с ее использованием.
Функция ЕСЛИ в MS Excel с одним условием.
Функцией ЕСЛИ в Excel, первый пример.
Рассмотрим простой пример применения функции ЕСЛИ в MS Excel. У нас есть столбец №1, в котором содержаться числа от 1 до 10.
Необходимо проверить, соответствует ли числа находящиеся в столбце №1 нашему критерию. Критерий: является ли числа находящиеся в столбце №1 числами, значения которых больше числа 4.
Взываем функцию ЕСЛИ в ячейку С3.
В диалоговом окне Аргументы функции видим три поля для заполнения:
Нажимаем ОК в диалоговом окне Аргументы функции.
Как видим, число содержащиеся в ячейки В3 не является числом, которое больше числа 4. Функция ЕСЛИ вернула значение_если_ложь (неправда). Протянем функцию ЕСЛИ вниз по столбцу, чтобы проверить значения находящиеся в остальных ячейках столбца №1, на соответствие нашему критерию (логическое_выражение).
Как видно из результата, ячейки в диапазоне В7:В12 содержат в себе числа, которые больше числа 4. Функция ЕСЛИ вернула значение_если_истина (правда).
Функция ЕСЛИ в Excel, второй пример.
Рассмотрим еще один пример. Предположим, что у нас есть таблица. В ней фамилии студентов, которые сдавали зачет. Часть студентов сдали зачет, а часть не сдали.
Теперь добавим в нашу таблицу столбец: Допуск к экзамену. Используем функцию ЕСЛИ для того, чтобы определить допущен студент к экзамену или нет. Диалоговое окно Аргументы функции будет выглядеть вот так:
Важно: текстовый критерий в поле логическое_выражение должен быть заключен в кавычки. Иначе функция выдаст ошибку.
Если студен сдал зачет, он допущен к экзамену, если нет, то не допущен. Нажимаем ОК в диалоговом окне Аргументы функции и протягиваем функцию ЕСЛИ вниз, по всему столбцу Допуск к экзамену. Получаем:
Видим, что в зависимости от результатов сдачи зачета, студент допущен к экзамену, либо не допущен.
Обращаю внимание, что при работе с функцией ЕСЛИ, можно использовать разные варианты равенств: больше (>), меньше(<), больше или равно (>=), меньше или равно (<=), равно (=), не равно (<>).
Функция ЕСЛИ в MS Excel с несколькими условиями.
Предположим, что у нас есть таблица с результатами сдачи контрольной работы студентами ВУЗа. Результаты контрольной работы оцениваются в баллах. От 0 до 100. Где все, что выше 90 баллов, отлично. Выше 80 баллов, это хорошо. Выше 70 баллов, это удовлетворительно. Ниже 70 баллов, это плохо.
Формула функции ЕСЛИ будет выглядеть вот так:
Где ячейка С3, первая из проверяемых ячеек в столбце Оценка. Далее функция протянута вниз по столбцу Результат, до конца таблицы.
Внимание: в данном случае формула функции ЕСЛИ прописана в ручную, прямо в строе формулы.
Таблица выглядит вот так:
В столбце Результат, в зависимости от количества баллов, функция ЕСЛИ вернула значение, согласно заданному критерию в формуле функции.
Использование операторов «И» и «ИЛИ» в функции ЕСЛИ в MS Excel.
Формула функции ЕСЛИ выглядит вот так:
Внимание: в данном случае формула функции ЕСЛИ прописана в ручную, прямо в строе формулы.
Формула функции ЕСЛИ выглядит вот так:
Внимание: в данном случае формула функции ЕСЛИ прописана в ручную, прямо в строе формулы.
Читайте также: