Как работает функция или в Excel

Как работает функция или в Excel?

Функция ИЛИ( ) , английский вариант OR(), проверяет на истинность условия и возвращает ИСТИНА если хотя бы одно условие истинно или ЛОЖЬ если все условия ложны.

Как правильно написать формулу в Excel если?

Функция ЕСЛИ, одна из логических функций, служит для возвращения разных значений в зависимости от того, соблюдается ли условие. Например: =ЕСЛИ(A2>B2;»Превышение бюджета»;»ОК») =ЕСЛИ(A2=B2;B4-A4;»»)

Как обозначается или в Excel?

ИЛИ : =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ) НЕ : =ЕСЛИ(НЕ(условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

Как поставить условие в Excel?

Как задать условие в Excel

=A1=B1 – Данное условие вернет ИСТИНА, если значения в ячейках A1 и B1 равны, или ЛОЖЬ в противном случае. Задавая такое условие, можно сравнивать текстовые строки без учета регистра.

Как работает функция Счетеслимн?

Функция СЧЁТЕСЛИМН считает количество ячеек по условию в Excel. Функция СЧЁТЕСЛИМН предназначена для подсчета числа ячеек из диапазона, удовлетворяющих установленным одному или нескольким критериям, и возвращает соответствующее числовое значение.

Как работает функция впр?

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

Как написать математическую формулу в Excel?

1. Выделите на листе ячейку, в которую необходимо ввести формулу.
2. Введите = (знак равенства), а затем константы и операторы (не более 8192 знаков), которые нужно использовать при вычислении. В нашем примере введите =1+1. Примечания: .
3. Нажмите клавишу ВВОД (Windows) или Return (Mac).

Как записывается логическая команда в Excel если?

а) если (условие, действие1, действие 2); б) (если условие, действие1, действие 2); +в) =если (условие, действие1, действие 2);

Как записывается логическая функция Если в Excel?

Чтобы решить поставленную задачу, воспользуемся логической функцией ЕСЛИ. Формула будет выглядеть так: =ЕСЛИ(C2>=8;B2/2;B2). Логическое выражение «С2>=8» построено с помощью операторов отношения «>» и «=». Результат его вычисления – логическая величина «ИСТИНА» или «ЛОЖЬ».

Как поставить звездочку в Excel?

1. Щелкните ячейку, в которую нужно вставить символ звездочки, щелкните Вставить > Символ.
2. Затем в Символ диалог, в шрифт выпадающий список, выберите Wingdings 2, затем перетащите полосу прокрутки, чтобы найти нужный символ звездочки.

Как вставить математические символы в Excel?

1. На вкладке Вставка в группе Символы щелкните стрелку рядом с надписью Формула и выберите Вставить новую формулу.
2. В области Работа с формулами в группе Символы на вкладке Конструктор щелкните стрелку Еще.

Как ввести больше или равно в Excel?

Операторы сравнения используются для сравнения двух значений.
.
Использование операторов в формулах Excel.

Как в Excel правильно записать условие неверно?

И : =ЕСЛИ(И(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ) ИЛИ : =ЕСЛИ(ИЛИ(условие; другое условие); значение, если ИСТИНА; значение, если ЛОЖЬ) НЕ : =ЕСЛИ(НЕ(условие); значение, если ИСТИНА; значение, если ЛОЖЬ)

Как показать не равно в Excel?

Ставим знак «не равно» с клавиатуры

Среди множества клавиш на клавиатуре нет кнопки с символом ≠, но для проведения вычислений в программе Excel она и не используется. Чтобы соответствующая функция сработала, вместо «не равно» следует набрать на клавиатуре знаки «<» (меньше) и «>» (больше).

Функция ЕСЛИ в Excel

Без преувеличения можно сказать, что функция ЕСЛИ в excel является одной из наиболее часто встречаемых и часто используемых. Работа экономиста в excel немыслима без знания этой функции.

Функция ЕСЛИ относиться к логическим функциям MS Excel, и позволяет создавать весьма затейливые конструкции, основываясь на проверке условия и выборе из двух и более альтернатив.

С использованием функции ЕСЛИ в excel можно строить ветвящиеся алгоритмы, строить дерево решений и другие системы и формулы используя вложение одной функции ЕСЛИ в другую и т.д. В Excel 2010 можно использовать до 64 вложений. Это позволяет построить поистине грандиозный алгоритм вычислений.

В общем виде функция ЕСЛИ в excel имеет следующий синтаксис:

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

Функция возвращает одно из двух значений в звисимости от того, какое значение принимает условие (лог_выраение). Если условие принимает значение ИСТИНА, то функция ЕСЛИ возвращает значение_если_истина, а если условие принимает значение ЛОЖЬ, то возвращается значение_ели_ложь.

Рассмотрим синтаксис данной функции на примере алгоритма вычисления суммы с НДС или без НДС.

Лог_выражение – это может быть любое значение или выражение (формула) которое принимает значение ИСТИНА или ЛОЖЬ. Так, например, С5=“с НДС” – это логическое выражение. Если в ячейке С5 находиться текст «с НДС», то логическое выражение принимает значение ИСТИНА, в противном случае – ЛОЖЬ, например, если там содержится текст «без НДС».

Значение_если_истина — значение или выражение (формула), которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА. Так, например, если этот аргумент равен формуле С12*1,18, то при значении логического выражения равному ИСТИНА, вычисления ведется по этой формуле.

Если аргумент «лог_выражение» имеет значение ИСТИНА, а аргумент «значение_если_истина» не задан, возвращается значение 0 (ноль).

Значение_если_ложь — значение или выражение (формула), которое возвращается, если аргумент «лог_выражение» имеет значение ЛОЖЬ. Так, например, если этот аргумент равен формуле С12*1 или просто С12, то при значении логического выражения равному ЛОЖЬ, вычисления ведется по этой формуле, т.е., как в нашем примере, берется просто значение суммы из ячейки С12

Если аргумент «лог_выражение» имеет значение ЛОЖЬ, а аргумент «значение_если_ложь» опущен (т. е. после аргумента «значение_если_истина» отсутствует точка с запятой), то возвращается логическое значение ЛОЖЬ. Если аргумент «лог_выражение» имеет значение ЛОЖЬ, а аргумент «значение_если_ложь» пуст (т. е. после аргумента «значение_если_истина» стоит точка с запятой, а за ней — закрывающая скобка), то возвращается значение 0 (ноль).

Рассмотрим наиболее часто встречающиеся в работе экономиста и при финансовом моделировании ситуации, в котором нам поможет использование функции ЕСЛИ.

Пример 1. Использование текстовых значений.

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

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

Создаем формулу с использованием функции ЕСЛИ:

=ЕСЛИ(Р20>1000;“превышение лимита”;“в рамках лимита”)

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

Пример 2. Скрытие значений. Скрытие нулевых значений.

Практически повсеместно встречающаяся ситуация – это возникновение ошибки #ДЕЛ/0! при подготовке исходных форматов. Мы готовим исходный формат, в котором в одном из столбцов вычисляем темп роста одного показателя к другому. Как известно, для этого используется формула А/В*100%, но поскольку у нас еще нет данных, то возникает ошибка при делении значений из столбца А на нулевые значения из столбца В.

Чтобы этого избежать, используем, как один из вариантов функцию ЕСЛИ.

=ЕСЛИ(В10=0;0;А10/В10) или =ЕСЛИ(В10=0;””;А10/В10)

В первом случае будет вместо ошибки #ДЕЛ/0! будет возвращено значение 0, а во втором случае будет просто пустая ячейка. Двойные кавычки возвращают пустое значение в ячейку.

Другой пример, проверка сходимости баланса. В этом случае, нам надо сравнить сумму актива и сумму пассива баланса, и в случае если они равны, т.е. например, С85-С160=0, нам надо скрыть нулевое значение, а в случае если есть расхождение – отобразить это значение.

Пример 3. Многоуровневые, вложенные вычисления.

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

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

Типичный пример, расчет скидки в зависимости от нескольких уровней объема продаж. Или заработка, в зависимости от нескольких уровней выработки.

Предположим у нас следующая матрица скидок:

До 100 000 рублей – 0%

От 100 001 до 300 000 рублей – 3%

От 300 001 до 500 000 рублей – 5%

Свыше 500 001 – 7%

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

Создаем формулу используя функцию ЕСЛИ:

Предположим, в ячейке D10 у нас находиться объем продаж.

Эта формула в зависимости от уровня объема продаж устанавливает тот или иной уровень скидок. Количество уровней вложения и соответственно уровней скидок может быть гораздо больше.

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

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

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

<= Меньше или равно

Использование операторов сравнения позволяет более гибко использовать функцию ЕСЛИ в excel для решения прикладных экономических задач.

В примере №3 мы как раз использовали оператор >= больше или равно для задания диапазона ссылок.

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

Функции И, ИЛИ, НЕ

Функция И в excel имеет следующий синтаксис:

Функция возвращает значение ИСТИНА, если в результате вычисления всех аргументов получается значение ИСТИНА; возвращает значение ЛОЖЬ, если в результате вычисления хотя бы одного из аргументов получается значение ЛОЖЬ.

Функция ИЛИ в excel имеет аналогичный синтаксис:

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

Функция НЕ в excel имеет следующий синтаксис:

Функция НЕ используется в тех случаях, когда необходимо убедиться, что значение не равно некой конкретной величине. Она возвращает значение ЛОЖЬ, если значение равно ИСТИНА и наоборот.

Пример 4. Использование нескольких условий.

Предположим, нам надо сделать отбор наших дебиторов, которые задолжали нам за 3 месяца сумму большую 100 000 рублей. Допустим, в этом случае формула должна выводить критерий «злостный задолжник».

В ячейке В10 у нас будет срок задолженности в месяцах, а в ячейке С10 сумма задолженности.

Тогда формула будет иметь следующий вид:

Если мы считаем критичным либо задолженность старше 3 месяцев, либо свыше 100 000 рублей, то формулу можно записать так:

В общем, как вы сами понимаете, возможности применения функции ЕСЛИ в excel безграничны.

Самое главное – это продумать алгоритм вычислений, а уж инструментария в MS Excel для его реализации вполне достаточно.

Microsoft Excel содержит дополнительные функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета числа вхождений текстовой строки или числа в диапазоне ячеек можно использовать функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН. Для вычисления суммы значений, попадающих в интервал, заданный текстовой строкой или числами, можно использовать функции СУММАЕСЛИ и СУММЕСЛИМН.

Функция ЕСЛИ в Excel

Изучим функцию ЕСЛИ в Excel, которая позволяет в зависимости от выполнения условия возвращать разные значения, подробно разберем примеры с одним и несколькими условиями.

Приветствую всех, дорогие читатели блога TutorExcel.Ru!

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

Речь пойдет про функцию ЕСЛИ в Excel.

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

Функция ЕСЛИ в Excel. Синтаксис

Как и всегда, изучение любой функции давайте начнем с самого базового — с синтаксиса функции ЕСЛИ (IF в английской версии):

ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь)

Проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет.

Функция содержит 3 аргумента: первое — логическое выражение (обозначим буквой А), второе — значение если истина (примем как букву B) и третье — значение если ложь (C).

По смыслу функция проверяет логическое выражение A (обычно сюда прописывают условие проверки, например, что одно число больше другого X>Y), если оно истинно, т.е. A выполнено, то функция возвращает одно значение (B), если же выражение ложно, т.е. A не выполнено, то возвращает уже другое (C). Если на словах не до конца понятно, то есть вот такая наглядная схема:

Таким образом, первый аргумент функции ЕСЛИ — логическое условие, второй аргумент — значение если условие истинно, третий аргумент — если нет.

С синтаксисом функции разобрались, самое время переходить к примерам.

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

Формула ЕСЛИ с одним условием

Рассмотрим пример — определим всех сотрудников, по которым выручка составила более 50 тыс. руб. Если условие будет выполняться, то будем возвращать текстовое значение «Да», если нет, то пустое поле.

Прописываем функцию ЕСЛИ с заданными параметрами — делаем проверку на то, что выручка больше 50 тыс. руб. ($B5>$C$1), пишем «Да» если выполнено и пустое поле в противном случае:

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

Формула ЕСЛИ с несколькими условиями

Вложенные ЕСЛИ в Excel. Пример 1

Усложним задачу, теперь найдем таких сотрудников, по которым выручка больше 50 тыс. руб. и меньше 70 тыс. руб.

Частично эту задачу мы уже сделали в предыдущем примере. Действительно, мы нашли сотрудников у которых выручка больше 50 тыс. руб., поэтому сейчас нам достаточно определить только тех из них, кто принес меньше 70 тыс. руб.

Также прописываем функцию ЕСЛИ, делаем проверку что выручка больше 50 тыс. руб. ($B5>$D$1), если выполнено, то добавляем еще одну функцию ЕСЛИ где делаем проверку что выручка уже меньше 70 тыс. руб. ($B$5<$D$2).
В случае выполнения всех условий пишем «Да», в иных случаях оставляем пустое поле:

Как видим количество «Да» у нас сократилось, что логично так как мы несколько сузили наше исходное условие по проверке.

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

Логическая функция И в Excel

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

И (логическое_значение1; [логическое_значение2]; [логическое_значение3]; …)

Проверяет, все ли аргументы имеют значение ИСТИНА, и возвращает значение ИСТИНА, если истинны все аргументы.

Возвращаясь к нашему примеру нам как раз нужно проверить одновременное выполнение двух условий: выручка больше 50 тыс. руб. (условие 1) и выручка меньше 70 тыс. руб. (условие 2).

В итоге оба условия проверяем с помощью функции ЕСЛИ и логического выражения в виде формулы И($B5>$E$1;$B5<$E$2), и возвращаем «Да» в случае истины:

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

Вложенные ЕСЛИ в Excel. Пример 2

Предлагаю закрепить материал и решить прямо противоположную предыдущему примеру задачу — найдем сотрудников по которым выручка меньше 50 тыс. руб. (при этом давайте в этом случае вернем значение «Плохо) или больше 70 тыс. руб. (а тут уже значение «Отлично»).

Также воспользуемся вложенным ЕСЛИ и поочередно проверим несколько условий, к примеру, сначала условие на выручку меньше 50 тыс. руб. ($B5<$F$1), в случае истинности возвращаем «Плохо», в иных случаях добавляем еще одну проверку на выручку уже больше 70 тыс. руб. ($B5>$F$2) и пишем значение «Отлично»:

Но мы бы не разбирали этот пример если бы все было так просто 🙂

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

Логическая функция ИЛИ в Excel

Как не трудно догадаться, если мы разобрали функцию И, то где-то рядом обязательно будет и другая очень полезная функция — логическая функция ИЛИ (OR в английской версии), которая позволяет проверить истинность хотя бы одного из выражений (в отличии от И, где проверяется истинность всех значений):

ИЛИ (логическое_значение1; [логическое_значение2]; [логическое_значение3]; …)

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

Возвращаемся к задаче, в нашем примере должно выполняться одно из двух условий: либо выручка меньше 50 тыс. руб. (условие 1), либо больше 70 тыс. руб. (условие 2). Соответственно с помощью функции ЕСЛИ и логического выражения в виде формулы ИЛИ($B5<$G$1;$B5>$G$2) проверяем выполнение условий, и возвращаем «Да» в случае истины:

Результат в целом совпал с предыдущим вариантом, однако такой же вариативности возвращаемых значений, к сожалению, у нас достичь уже не получится из-за особенностей функции ИЛИ, т.е. вместо «Плохо» и «Отлично» мы получили только 1 вариант ответа «Да».

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

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

Спасибо за внимание!
Если у вас есть вопросы и мысли по теме статьи — прошу делиться в комментариях.

Гайд по использованию условного форматирования в Excel

Что такое «Условное форматирование» и для чего оно нужно?

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

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

Как создать правило? ​

Студенты сдают тест по теме «Рыночная экономика», оценка за тест ставится в формате зачет/незачет. При этом «зачет» ставится, если набрано не менее 80 баллов. Необходимо выделить оранжевым цветом строки со студентами, которые провалили тестирование.

Рассмотрим, какими правилами можно воспользоваться для решения данной задачи.

Правила выделения ячеек ​

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

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

Для этого выделяем диапазон значений, для которого будем применять правило, и выбираем «Правила выделения ячеек» – «Меньше».

После этого видим открывшееся окошко для ввода данных. Вводим количество баллов, необходимое для зачета – 80.

Теперь осталось выбрать формат.

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

Нажимаем «Ок» и видим результат: ячейки, значение которых было меньше 80, выделены оранжевым цветом.

Ту же процедуру можно повторить и с текстовыми значениями, в нашем случае с отметками зачет/незачет. Для этого необходимо выделить новый диапазон, зайти снова в «Правила выделения ячеек» и «Текст содержит».

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

В итоге мы имеем подсвеченные ячейки с нужной отметкой.

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

Для того, чтобы выделить строку целиком, зайдём в раздел «Управление правилами».

В открывшемся окне выберемся из выпадающего списка «Этот лист» (чтобы увидеть, какие правила у нас применены на листе, а не только к ячейке, на которой в данный момент стоит выделение), и нажмём кнопку «Создать правило».

Здесь мы также видим список правил, которые нам предлагается применить.

Форматировать все ячейки на основании их значений ​

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

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

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

Гистограмма тоже вполне наглядна. Берет максимальное значение диапазона за 100% и пропорционально заполняет ячейку цветом (цвет также можно изменить).

Наборы значков – тоже интересное решение. Рядом с текстом в ячейке появляется иконка (или вместо текста если поставить галочку в поле «Показать только значок»). Стили значков можно поменять, а также задать для них параметры (какой значок за какой интервал значений отвечает).

Главное не забывайте указывайте диапазон, для которого данное правило будет применяться (это касается любого правила).

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

Примечание: о том, как правильно и продуктивно работать с правилами фильтрации, читайте в нашей статье «Правила фильтрации в MS Excel».

Форматировать только ячейки, которые содержат

Здесь мы не будем подробно останавливаться, так как это те же самые правила для числовых, которые мы рассматривали вначале: больше, меньше, между, равно и т.д.

Форматировать только первые или последние значения ​

Это правило не так часто применяется, но если Вам нужно выделить, например, 5 ячеек с наивысшим результатом (значения, которые относятся к первым 5), или, наоборот, 10 ячеек с наименьшим результатом (значения, которые относятся к последним 10), то используйте его.

Форматировать только значения, которые находятся выше или ниже среднего ​

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

Форматировать только уникальные или повторяющиеся значения ​

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

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

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

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

Примечание: Если ячейка имеет текстовый формат, но значение ячейки в формуле нужно писать в кавычках.

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

Примечание: Знак $ закрепляет столбец или строку, в зависимости от того, перед буквой (столбец) или цифрой (строка) он стоит. Написание $D$5 показывает, что в формуле будет использоваться только конкретная ячейка.

Так как нам необходимо форматировать всю таблицу, т.е. использовать в формуле весь столбец D, перед строкой символ $ убираем (перед столбцом убирать не нужно). В итоге остается $D5.

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

И теперь мы видим результат: оранжевым цветом выделены строки со студентами, у которых оценка за тест – незачет. Задача выполнена!

Как изменить или удалить правило? ​

На одном листе может применяться более одного правила на один и тот же, либо на разные диапазоны.

По кнопке «Изменить правило» откроется меню, в котором можно отредактировать формулу, изменить параметры форматирования и т.д.

Кнопка «Удалить правило» удалит то, на которым в данный момент стоит выделение.

Также правила можно менять местами, нажимая на стрелочки в этом же меню «вверх» или «вниз». Выполняются правила снизу-вверх, т.е. то, которое сверху, перекрывает нижние (выполняется последним).

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

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

Операторы VBA: арифметические, логические, сравнения, присвоения

Оператор — это наименьшая способная выполняться единица кода VBA. Оператор может объявлять или определять переменную, устанавливать параметр компилятора VBA или выполнять какое-либо действие в программе.

Арифметических операторов в VBA всего 7. Четыре стандартных: сложение (+), вычитание (-), умножение (*), деление (/) и еще три:

• возведение в степень (^), например 2^3 = 8;
• целочисленное деление (). Делит первое число на второе, отбрасывая (не округляя) дробную часть. Например, 52 = 2;
• деление по модулю (Mod). Делит первое число на второе, возвращая только остаток от деления. Например, 5 Mod 2 = 1.

Оператор присвоения в VBA — знак равенства. Можно записывать так: Let nVar = 10

а можно еще проще: nVar = 10

Во втором случае не путайте знак равенства с оператором равенства.

Выражение nVar = 10 значит «присвоить переменной nVar значение 10», а если строка выглядит так:

If (nVar = 10) то это значит «если значение переменной nVar равно 10».

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

Операторов сравнения в VBA всего 8:

• равенство (=), например, If (nVar = 10);
• больше, чем и меньше, чем (> и <), например, If (nVar > 10) ;
• больше или равно и меньше или равно (>= и ≤), например, If (nVar >= 10) ;
• не равно (<>), например, If (nVar<>10) ;
• сравнение объектов (Is). Определяет, ссылаются объектные переменные на тот же объект или на разные, например, If (obj1 is obj2) ;
• подобие (Like). Сравнивает строковый объект с шаблоном и определяет, подходит ли шаблон.

Операторы сравнения всегда возвращают true или false — true, если утверждение истинно, и false, если ложно.

Немного про сравнение строковых значений:

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

Option Compare Text

Чуть подробнее про оператор Like. Общий его синтаксис выглядит как

Выражение1 Like Выражение2

При этом Выражение1 — любое текстовое выражение VBA, а Выражение2 — шаблон, который передается оператору Like. В этом шаблоне можно использовать специальные подстановочные символы

Подстановочные символы для оператора LIKE

Любая цифра (только одна) от 0 до 9

Любое количество любых символов (включая нулевое)

Любой символ (только один)

Любой символ (только один) из приведенного списка

Любой символ (только один), кроме приведенных в списке

Очень часто при проверке нескольких условий используются логические операторы:

• AND — логическое И, должны быть истинными оба условия;
• OR — логическое ИЛИ, должно быть истинным хотя бы одно из условий;
• NOT — логическое отрицание, возвращает TRUE, если условие ложно;
• XOR — логическое исключение. В выражении E1 XOR E2 возвращает TRUE, если только E1 = TRUE или только E2 = TRUE, иначе — FALSE;
• EQV — эквивалентность двух выражений, возвращает TRUE, если они имеют одинаковое значение;
• IMP — импликация, возвращает FALSE, если E1 = TRUE и E2 = FALSE, иначе — TRUE.

Помнить нужно про AND, OR, NOT , остальные логические операторы используются редко.

Почти в любой программе VBA используются операторы конкатенации. В VBA их два — + или &. Рекомендуется всегда использовать &, потому что:

• при использовании & производится автоматическое преобразование числовых значений в строковые — нет опасности допустить ошибку;
• при использовании оператора + сложение строкового значения со значением типа Null дает Null.

Возможно вам это будет интересно!

1 комментарий

Булев тип данных может хранить только два значения: True либо False, истина или ложь. Истинное значение в VB соответствует целочисленной ?1, а ложь равна 0, а при неявном приведении типа, любое численное значение отличное от нуля рассматривается как истина. Такое поведение позволяет использовать логические операторы