Excel формула массива или

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

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

Примеры использования функций И ИЛИ в логических выражениях Excel

Пример 1. В учебном заведении для принятия решения о выплате студенту стипендии используют два критерия:

1. Средний балл должен быть выше 4,0.
2. Минимальная оценка за профильный предмет – 4 балла.

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

Исходная таблица:

Для решения используем следующую формулу:

=И(E2>=4;СРЗНАЧ(B2:E2)>=4)

Описание аргументов:

• E2>=4 – проверка условия «является ли оценка за профильный предмет не менее 4 баллов?»;
• СРЗНАЧ(B2:E2)>=4 – проверка условия «является ли среднее арифметическое равным или более 4?».

Запишем аналогичные формулы в остальных ячейках. В результате получим:

Как видно выше на рисунке студенты под номером: 2, 5, 7 и 8 — не получат стипендию.



Формула с логическим выражением для поиска истинного значения

Пример 2. На предприятии начисляют премии тем работникам, которые проработали свыше 160 часов за 4 рабочих недели (задерживались на работе сверх нормы) или выходили на работу в выходные дни. Определить работников, которые будут премированы.

Исходные данные:

Формула для расчета:

=ИЛИ(B3>160;C3=ИСТИНА())

Описание аргументов:

• B3>160 – условие проверки количества отработанных часов;
• C3=ИСТИНА() – условие проверки наличия часов работы в выходные дни.

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

То есть, оба условия возвращают значения ЛОЖЬ только в 4 случаях.

Формула с использованием логических функций ИЛИ И ЕСЛИ

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

1. CPU с 4 ядрами или таковой частотой не менее 3,7 ГГц;
2. ОЗУ – не менее 16 Гб
3. Видеоадаптер 512-бит или с объемом памяти не менее 2 Гб

Распределить имеющиеся ПК по категориям.

Исходные денные:

Для определения категории используем следующую формулу:

Описание аргументов:

И(ИЛИ(B3>=4;C3>=3,7);D3>=16;ИЛИ(E3>=2;F3>=512)) – функция И, проверяющая 3 условия:

1. Если число ядер CPU больше или равно 4 или тактовая частота больше или равна 3,7, результат вычисления ИЛИ(B3>=4;C3>=3,7) будет значение истинное значение;
2. Если объем ОЗУ больше или равен 16, результат вычисления D3>=16 – ИСТИНА;
3. Если объем памяти видеоадаптера больше или равен 2 или его разрядность больше или равна 512 – ИСТИНА.

«Игровой» — значение, если функция И возвращает истинное значение;

«Офисный» — значение, если результат вычислений И – ЛОЖЬ.

Этапы вычислений для ПК1:

1. =ЕСЛИ(И(ИЛИ(ИСТИНА;ИСТИНА);ЛОЖЬ;ИЛИ(ЛОЖЬ;ЛОЖЬ));»Игровой»;»Офисный»)
2. =ЕСЛИ(И(ИСТИНА;ЛОЖЬ;ЛОЖЬ);»Игровой»;»Офисный»)
3. =ЕСЛИ(ЛОЖЬ;»Игровой»;»Офисный»)
4. Возвращаемое значение – «Офисный»

Результаты расчетов для всех ПК:

Особенности использования функций И, ИЛИ в Excel

Функция И имеет следующую синтаксическую запись:

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

Функция ИЛИ имеет следующую синтаксическую запись:

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

Описание аргументов обеих функций:

• логическое_значение1 – обязательный аргумент, характеризующий первое проверяемое логическое условие;
• [логическое_значение2] – необязательный второй аргумент, характеризующий второе (и последующие) условие проверки.

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

Примечания 1:

1. Аргументами должны являться выражения, результатами вычислениями которых являются логические значения.
2. Результатом вычислений функций И/ИЛИ является код ошибки #ЗНАЧ!, если в качестве аргументов были переданы выражения, не возвращающие в результате вычислений числовые значения 1, 0, а также логические ИСТИНА, ЛОЖЬ.

Примечания 2:

1. Функция И чаще всего используется для расширения возможностей других логических функций (например, ЕСЛИ). В данном случае функция И позволяет выполнять проверку сразу нескольких условий.
2. Функция И может быть использована в качестве формулы массива. Для проверки вектора данных на единственное условие необходимо выбрать требуемое количество пустых ячеек, ввести функцию И, в качестве аргумента передать диапазон ячеек и указать условие проверки (например, =И(A1:A10>=0)) и использовать комбинацию Ctrl+Shift+Enter. Также может быть организовано попарное сравнение (например, =И(A1:A10>B1:B10)).
3. Как известно, логическое ИСТИНА соответствует числовому значению 1, а ЛОЖЬ – 0. Исходя из этих свойств выражения =(5>2)*(2>7) и =И(5>2;2>7) эквивалентны (Excel автоматически выполняет преобразование логических значений к числовому виду данных). Например, рассмотрим этапы вычислений при использовании данного выражения в записи =ЕСЛИ((5>2)*(2>7);”true”;”false”):

• =ЕСЛИ((ИСТИНА)*(ЛОЖЬ);”true”;”false”);
• =ЕСЛИ(1*0;”true”;”false”);
• =ЕСЛИ(0;”true”;”false”);
• =ЕСЛИ(ЛОЖЬ;”true”;”false”);
• Возвращен результат ”false”.

Примечания 3:

1. Функция ИЛИ чаще всего используется для расширения возможностей других функций.
2. В отличие от функции И, функция или эквивалентна операции сложения. Например, записи =ИЛИ(10=2;5>0) и =(10=2)+(5>0) вернут значение ИСТИНА и 1 соответственно, при этом значение 1 может быть интерпретировано как логическое ИСТИНА.
3. Подобно функции И, функция ИЛИ может быть использована как формула массива для множественной проверки условий.

Функция

ИЛИ(

)

, английский вариант OR(),

проверяет на истинность условия и возвращает ИСТИНА если хотя бы одно условие истинно или ЛОЖЬ если все условия ложны.

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

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

логическое_значение1

— любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ.

=ИЛИ(A1>100;A2>100)

Т.е. если хотя бы в одной ячейке (в

A1

или

A2

) содержится значение больше 100, то формула вернет

ИСТИНА,

а если в обоих ячейках значения <=100, то —

ЛОЖЬ

.

Другими словами, формула

=ИЛИ(ЛОЖЬ;ЛОЖЬ)

вернет ЛОЖЬ, а формулы

=ИЛИ(ИСТИНА;ЛОЖЬ)

или

=ИЛИ(ЛОЖЬ;ИСТИНА)

или

=И(ИСТИНА;ИСТИНА)

или

=И(ЛОЖЬ;ЛОЖЬ;ИСТИНА)

вернут ИСТИНА.

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

ЕСЛИ()

. Чаще всего функцией

ИЛИ()

на истинность проверяется 2-5 условий.

Совместное использование с функцией

ЕСЛИ()

Сама по себе функция

ИЛИ()

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

ЕСЛИ()

:

=ЕСЛИ(ИЛИ(A1>100;A2>100);»Бюджет превышен»;»В рамках бюджета»)

Т.е. если хотя бы в одной ячейке (в

A1

или

A2

) содержится значение больше 100, то выводится

Бюджет превышен

, если в обоих ячейках значения <=100, то

В рамках бюджета

.

Сравнение с функцией

И()

Функция

И()

также может вернуть только значения ИСТИНА или ЛОЖЬ, но, в отличие от

ИЛИ()

, она возвращает ИСТИНА, только если все ее условия истинны. Чтобы сравнить эти функции составим, так называемую таблицу истинности для

И()

и

ИЛИ()

.

Эквивалентность функции

ИЛИ()

операции сложения +

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

=ИСТИНА+0

и

=ЛОЖЬ+0

Следствием этого является возможность альтернативной записи формулы

=ИЛИ(A1>100;A2>100)

в виде

=(A1>100)+(A2>100)

Значение второй формулы будет =0 (ЛОЖЬ), только если оба аргумента ложны, т.е. равны 0. Только сложение 2-х нулей даст 0 (ЛОЖЬ), что совпадает с определением функции

ИЛИ()

.

Эквивалентность функции

ИЛИ()

операции сложения + часто используется в формулах с Условием ИЛИ, например, для того чтобы сложить только те значения, которые равны 5

ИЛИ

равны 10:

=СУММПРОИЗВ((A1:A10=5)+(A1:A10=10)*(A1:A10))

Проверка множества однотипных условий

Предположим, что необходимо сравнить некое контрольное значение (в ячейке

B6

) с тестовыми значениями из диапазона

A6:A9

. Если контрольное значение совпадает хотя бы с одним из тестовых, то формула должна вернуть ИСТИНА. Можно, конечно записать формулу

=ИЛИ(A6=B6;A7=B6;A8=B6;A9=B6)

но существует более компактная формула, правда которую нужно ввести как

формулу массива

(см.

файл примера

):

=ИЛИ(B6=A6:A9)

(для ввода формулы в ячейку вместо

ENTER

нужно нажать

CTRL+SHIFT+ENTER

)

Вместо диапазона с тестовыми значениями можно также использовать

константу массива

:

=ИЛИ(A18:A21>{1:2:3:4})

В случае, если требуется организовать

попарное сравнение списков

, то можно записать следующую формулу:

=ИЛИ(A18:A21>=B18:B21)

Если хотя бы одно значение из Списка 1 больше или равно (>=) соответствующего значения из Списка 2, то формула вернет ИСТИНА.

Терминология

Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:

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

Пример 1. Классика жанра — товарный чек

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

1. выделяем ячейку С7
2. вводим с клавиатуры =СУММ(
3. выделяем диапазон B2:B5
4. вводим знак умножения (звездочка)
5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ — в итоге должно получиться так:

   

6. чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Вуаля!

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

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

Пример 2. Разрешите Вас… транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

Допустим, имеем двумерный массив ячеек, который хотим транспонировать.

• Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
• вводим функцию транспонирования =ТРАНСП(   
• в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата:

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

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

Пример 3. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике… На обороте тетради на обложке было что? Таблица умножения вот такого вида:

При помощи формул массива она вся делается в одно движение:

1. выделяем диапазон B2:K11
2. вводим формулу =A2:A11*B1:K1
3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

и получаем результат:

Пример 4. Выборочное суммирование

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

 В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Ссылки по теме

• Формула массива для извлечения непустых ячеек из диапазона
• Формула массива для извлечения уникальных ячеек из диапазона
• Формула массива для извлечения данных из списка (многоразовый ВПР)

• Аргументы функции должны быть логически вычислимы по принципу TRUE или FALSE;
• Текст и пустые ячейки игнорируются функцией;
• Если вы используете функцию с не логически вычисляемыми значениями – она выдаст ошибку;
• Вы можете тестировать максимум 255 условий в одной формуле.

Примеры использования функции OR (ИЛИ) в Excel

Пример 1. Используем аргументы TRUE и FALSE в функции OR (ИЛИ)

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

Она может использовать аргументы TRUE и FALSE в кавычках.

Пример 2. Используем ссылки на ячейки, содержащих TRUE/FALSE

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

Пример 3. Используем условия с функцией OR (ИЛИ)

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

Пример 4. Используем числовые значения с функцией OR (ИЛИ)

Число “0” считается FALSE в Excel по умолчанию. Любое число, выше “0”, считается TRUE (оно может быть положительным, отрицательным или десятичным числом).

Пример 5. Используем функцию OR (ИЛИ) с другими функциями

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

На примере ниже показано, как использовать функцию вместе с IF (ЕСЛИ) :

На примере выше, мы дополнительно используем функцию IF (ЕСЛИ) для того, чтобы проверить несколько условий. Формула проверяет значение ячеек А2 и A3. Если в одной из них значение более чем “70”, то формула выдаст “TRUE”.

Функция ИЛИ() в MS EXCEL

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

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

логическое_значение1 — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ.
=ИЛИ(A1>100;A2>100)
Т.е. если хотя бы в одной ячейке (в A1 или A2) содержится значение больше 100, то формула вернет ИСТИНА, а если в обоих ячейках значения 100;A2>100);»Бюджет превышен»;»В рамках бюджета»)

Т.е. если хотя бы в одной ячейке (в A1 или A2) содержится значение больше 100, то выводится Бюджет превышен, если в обоих ячейках значения 100;A2>100) в виде =(A1>100)+(A2>100)
Значение второй формулы будет =0 (ЛОЖЬ), только если оба аргумента ложны, т.е. равны 0. Только сложение 2-х нулей даст 0 (ЛОЖЬ), что совпадает с определением функции ИЛИ() .

Эквивалентность функции ИЛИ() операции сложения + часто используется в формулах с Условием ИЛИ, например, для того чтобы сложить только те значения, которые равны 5 ИЛИ равны 10:
=СУММПРОИЗВ((A1:A10=5)+(A1:A10=10)*(A1:A10))

Проверка множества однотипных условий

Предположим, что необходимо сравнить некое контрольное значение (в ячейке B6) с тестовыми значениями из диапазона A6:A9. Если контрольное значение совпадает хотя бы с одним из тестовых, то формула должна вернуть ИСТИНА. Можно, конечно записать формулу
=ИЛИ(A6=B6;A7=B6;A8=B6;A9=B6)
но существует более компактная формула, правда которую нужно ввести как формулу массива (см. файл примера ):
=ИЛИ(B6=A6:A9)
(для ввода формулы в ячейку вместо ENTER нужно нажать CTRL+SHIFT+ENTER)

Вместо диапазона с тестовыми значениями можно также использовать константу массива:
=ИЛИ(A18:A21><1:2:3:4>)

В случае, если требуется организовать попарное сравнение списков, то можно записать следующую формулу:
=ИЛИ(A18:A21>=B18:B21)

Если хотя бы одно значение из Списка 1 больше или равно (>=) соответствующего значения из Списка 2, то формула вернет ИСТИНА.

Функции ИЛИ в Excel

Добрый день уважаемый читатель!

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

Использование функции ИЛИ производится тогда, когда возникает необходимость проверки условий типа: Условие№1 ИЛИ Условие№2. Итог функции будет ИСТИНА в случае, когда все или хотя бы одно из указанных условий будет соответствовать правильному значению, результат ЛОЖЬ будет получен в случае, когда все аргументы функции не соответствуют заданному критерию.

Синтаксис этой логической функции также прост и неказист и выглядит следующим образом:

=ИЛИ(Логическое_значение№1;[логическое значение№2];. ), где:

• Логическое_значение№1 – является обязательным аргументом, вычисление которого дает результат ЛОЖЬ или ИСТИНА;
• [логическое значение№2] – является необязательным дополнительным проверяемым условием, также дающим результат вычисления ИСТИНА или ЛОЖЬ. Количество таких условий не должно превышать 255.

Примечания по работе с функцией ИЛИ.

Пример№1: Простое условия функции ИЛИ.

На небольшом примере логических комбинаций ИСТИНА и ЛОЖЬ рассмотрим получаемый результат при использовании функции: При использовании логических условий в натуральных показателях будет получен следующий результат: Пример№2: Совместная работа с функцией ЕСЛИ.

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

=ЕСЛИ(ИЛИ(B13>10;C13>10);»Лимит превышен»;»В границах лимита»)

То есть когда в любой из ячеек содержится значение больше 10, то будет выводиться значение «Лимит превышен», а если во всех ячейках хранится значение менее 10, то получаем «В границах лимита». Пример№3: Проверка с помощью массива похожих условий.

Представим, что возникла необходимость сравнить определённое контрольное значение $C$24 (закреплённая абсолютная ссылка) с тестовым диапазоном A19:A22. В случае, когда в данном диапазоне будет обнаружено контрольное значение, формула вернет значение ИСТИНА.

Для получения такого результата нам понадобится формула: =ИЛИ(A19:A22=$C$24), которую введем в ячейку как формулу массива, с помощью комбинации Ctrl+Shift+Enter вместо простого нажатия Enter. Пример№4: Расчёт комиссионных надбавок по результатам работы.

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

=ЕСЛИ(ИЛИ(B35>=$C$29;C35>=$C$30);B35*$C$31;0). Я очень надеюсь, что смог более подробно и понятно описать работу логической функции ИЛИ в Excel. Что позволит вам увеличит эффективность вашей работы и если это случилось, жду ваши лайки, а если у вас возникли вопросы, пишите комментарии.

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

До новых встреч на страницах TopExcel.ru!

Деньги нужны даже для того, чтобы без них обходиться.
Оноре де Бальзак

ШКОЛА ПРОГРАММИРОВАНИЯ

Сообщение об ошибке

Функции Excel.Функция И(), ИЛИ()

На прошлом уроке «Функции Excel. Функция ЕСЛИ» была рассмотрена работа со встроенной функцией ЕСЛИ(). Все бы хорошо, но возникают такие ситуации, и довольно часто, когда вложенностью условия в условие не обойтись. Например, элементарно необходимо проверить следующее: 1 больше или равно x и x меньше или равно 5 . Реализовать такое условие несколькими ЕСЛИ довольно громоздко и проблематично, а в некоторых ситуациях — вообще невозможно. Для расширения функционала ЕСЛИ и облегчения составления формул с условием, в Excel имеются еще пара полезных функции — И() и ИЛИ().

Функция И() используется тогда, когда необходимо проверить несколько условий следующим образом — Условие 1 И Условие 2. При этом все условия должны быть истинными. Результатом работы данной функции является ИСТИНА или ЛОЖЬ (TRUE / FALSE). Пример: ЕСЛИ а = b И а=с ТОГДА значение 1 ИНАЧЕ значение 2.
Как видно из примера, значение 1 будет только в том случае, если все условия верны.

Функция ИЛИ()

Функция ИЛИ() используется тогда, когда необходимо проверить несколько условий следующим образом — Условие 1 ИЛИ Условие 2. Результат функции будет истинным, если хотя бы одно из условий истинно. Пример: ЕСЛИ а = b ИЛИ а=с ТОГДА значение 1 ИНАЧЕ значение 2.

Синтаксис функций И() и ИЛИ() одинаков: Функция(Условие 1; Условие 2; Условие 3 и до 30-ти условий). Результат ИСТИНА или ЛОЖЬ.

Примеры использования функции И и ИЛИ

Все вышесказанное сложно к пониманию и относится к разделу Мат. логики и Дискретной математики. Попробую это все изложить на понятном языке. Разберем несколько примеров. Скажу сразу, все примеры будут с использованием функции ЕСЛИ.

Пример 1.
Столбец А, начиная с первой строки, содержит 56, 55, 88, 6, 74. Столбец В — 52, 55, 88, 4, 25. Столбец С — 53, 55, 88, 6, 25. С помощью функций ЕСЛИ и И необходимо определить строки, значения которых равны следующим образом А=В и В=С.

Переходим в ячейку D1 и с помощью мастера функций вводим следующее

Формула будет выглядеть так: “ =ЕСЛИ(И(A1=B1;B1=C1);»Все значения равны»;»Значения не равны») “

Пример 2.

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

Мастер функций выглядит так:

Формула: “ =ЕСЛИ(ИЛИ(A1=B1;B1=C1;A1=C1);»Есть равные значения»;»Нет равных значений»)”

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

И напоследок рассмотрим еще один пример с функцией И и ИЛИ из реальной жизни.

Пример 3.

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

1 – от 0% до 10%
2- от 11% до 30%
3- от 31% до 40%
4- от 41% до 75%
5-от 76% до 100%

Таблица следующего вида:

Составим формулу следующего вида:

=ЕСЛИ(ИЛИ(И(B1>0;B1 0,11;B1 0,31;B1 0,41;B1 0,76;B1

• Добавить комментарий
• Кол-во просмотров: 137054

Логические функции Excel

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

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

В описаниях синтаксиса функций их аргументы, которые заключены в квадратные скобки «[]», являются необязательными.

Будут рассмотрены следующие функции:

Функция ИСТИНА

Не принимает никаких аргументов и просто возвращает логическое значение «ИСТИНА».

Синтаксис: =ИСТИНА()

Функция ЛОЖЬ

Аналогична функции ИСТИНА, за исключением то, что возвращает противоположный результат ЛОЖЬ.

Синтаксис: =ЛОЖЬ()

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

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

Рассмотрим таблицу истинности данной функции:

Синтаксис: =И(Логическое_значение1; [Логическое_значение1];…)

Пример использования:

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

Во втором примере функция никогда не вернет значение ИСТИНА, т.к. условие ее второго аргумента заранее неравно.

Функция ИЛИ

Возвращает логическое значение ИСТИНА, если хотя бы один аргумент функции вернет истинное значение.

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

Таблица истинности функции ИЛИ:

Синтаксис: =ИЛИ(Логическое_значение1; [Логическое_значение2];…)

В качестве примера, рассмотрите примеры функции И, все они вернут результат ИСТИНА, т.к. первый аргумент является истинным.

Функция НЕ

Принимает в виде аргумента всего одно логическое значение и меняет его на противоположное, т.е. значение ИСТИНА она изменит на ЛОЖЬ и наоборот.

Таблица истинности функции И с применением функции НЕ:

Таблица истинности функции ИЛИ с применением функции НЕ:

Синтаксис: =НЕ(логическое_значение)

Функция ЕСЛИ

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

Синтаксис:

Примеры использования функции:

Рассмотрим первый простой пример, чтобы понять, как функция работает.

Умышлено в первый аргумент функции вставить функцию ИСТИНА. В результате проверки, будет возвращен 2 аргумент (значение_если_истина), 3 аргумент будет опущен.

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

Имеются банковские карточки с номерами, начинающимися с первых четырех цифр, которые являются идентификатором вида карты:

Используем нашу функцию для определения типа карты.

Функция, применяемая в данном примере, выглядит так:

=ЕСЛИ( ЛЕВСИМВ(A2;4)=»1111″ ; «Visa» ;ЕСЛИ( ЛЕВСИМВ(A2;4)=»2222″ ; «Master Card» ;»карта не определена»))

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

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

Функция ЕСЛИОШИБКА

Предназначена для проверки возврата выражением ошибки. Если ошибка обнаружена, то она возвращает значение второго аргумента, иначе первого.

Функция принимает 2 аргумента, все они являются обязательными.

Синтаксис: =ЕСЛИОШИБКА(значение;значение_если_ошибка)

Пример использования функции:

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

Функция ИЛИ

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

Пример

Технические подробности

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

​ с именем проще.​​ функция И. Суть​​Логическое_выражение – ЧТО оператор​ список размеров этого​ отрицательное число в​ В случае преодоления​​ клетка останется пустой.​​Для понимания возможностей функции​ человека.​ экзамена, который должен​ помощью функции СУММЕСЛИМН​ в конце формулы.​ только одна функция​ (пример: «Текст»). Единственное​ сравнения значений и​​ примеров использования функции​​ — одна из логических​В ячейку B2 прописываю​​ такова: ЕСЛИ а​​ проверяет (текстовые либо​ товара. Выбираем другое​ скобках.​

​ отметки в 3001​

​ Рассмотрим пример для​​ «Если» в Excel,​

​Как видите, вместо второго​​ содержать всего два​​Функция И​

​ и третьего аргументов​

• ​ варианта:​Функция ИЛИ​ того, чтобы закрыть​ налога с продажи​ и ЛОЖЬ, которые​

• ​ простая функция ЕСЛИ​как отдельно, так​ которой можно определить​А если по-русски,​ а = 2​

• ​Значение_если_истина – ЧТО появится​ первой ячейке, меняется​ правильно​​ аналогичное действие, но​​ является критической. Введем​ и далее мы​

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

Примеры

​ то если в​ ТОГДА значение в​​ в ячейке, когда​​ выпадающий список во​– «Округление в​ уже с учетом​​ соответствующий комментарий. В​​ перейдем к их​

​ отвечают заданному условию​

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

• ​ вы хотите достичь.​​ случае сделать что-то​​.​​ИЛИ​ то я хочу​ 1 или условие​​ (правдивы).​​ уже вновь выбранного​ функции на закладке​ применять на практике.​ =ЕСЛИ(ИЛИ(A1>=6; B1>10000); «критическая​ 8. Далее в​ЕСЛИ​ более 45 баллов​

См. также:

​ неработающих формул​
​ скобок, приложение Excel​ в формуле.​
​ Какое сравнение вы​
​ еще)​
​Формула​
​возвращает значение ИСТИНА,​ что бы в​
​ 2. Как только​Значение,если_ложь – ЧТО появится​
​ товара из первой​ «Главная», то изменится​
​ Объем продаж и​ ситуация»; «»). В​ поле по адресу​
​, тем самым расширяя​ – сдали экзамен,​
​Обнаружение ошибок в формулах​
​ попытается исправить ее.​Если вы хотите больше​
​ пытаетесь выполнить? Написать​Поэтому у функции ЕСЛИ​

support.office.com

Функция ЕСЛИ

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

• ​ таком случае если​ D1 вписываем следующую​ число условий, которое​ остальные нет.​

​ с помощью функции​Excel позволяет использовать до​ узнать о различных​ оператор ЕСЛИ не​ возможны два результата.​=ИЛИ(A2>1;A2​

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

Технические подробности

​ формулу: =ЕСЛИ(C1, =,​ формула может обработать.​Выделите ячейку, в которую​ проверки ошибок​ 64 вложенных функций​ операторах вычислений, которые​

​ сложнее, чем выстроить​

​ Первый результат возвращается​

​Возвращает значение ИСТИНА, если​

• ​ одного из ее​

• ​ которую я ввела​

Простые примеры функции ЕСЛИ

• ​ Суть такова: ЕСЛИ​

​ условию (лживы).​ или диапазона ячеек.​​ будет все число,​ для решения различного​ одному из параметров​

• ​Продолжаем практиковаться и осваивать​

​ можете создать нужное​ В нашем случае​​Функции Excel (по алфавиту)​ вовсе не означает,​ формулах («меньше» (​ цепочку «что должно​

​ сравнение истинно, второй —​ A2 больше 1​ ИСТИНА, и значение​ этого он мне​ а = 1​Пример:​ Формулу пишем вручную​ со всеми числами​ рода задач. Основной​ (срок, сумма задолженности),​ логические возможности приложения.​ количество вложений. Правда​ это ячейка C3.​Функции Excel (по категориям)​ что так и​), «больше» (​ произойти, если это​ если сравнение ложно.​ИЛИ​

​ ЛОЖЬ, если в​​ в ячейке B2​ ИЛИ а =​Оператор проверяет ячейку А1​ в ячейке или​ после запятой, в​ этап — правильное​ пользователь увидит соответствующее​

Начало работы

​ Функцию табличного редактора​ есть очевидный недостаток​Введите в нее выражение:​Функция​ надо делать. Почему?​>), «равно» (​ условие выполнено, и​Если вы ищете информацию​меньше 100. В​ результате вычисления всех​ пишет ИСТИНА.​ 2 ТОГДА значение​ и сравнивает ее​ в строке формул,​ том числе, которые​ составление формулы, чтобы​ примечание. В первой​ Excel «Если» можно​ такой конструкции, после​=ЕСЛИ(B3>45; «Сдал»; «Не сдал»)​ЕСЛИ​Нужно очень крепко подумать,​=​

Еще примеры функции ЕСЛИ

• ​ что должно произойти,​

​ о работе с​ противном случае возвращает​ ее аргументов получается​​Как исправить помогите?​ в ИНАЧЕ значение​ с 20. Это​ или можно написать​ мы скрыли, уменьшив​

• ​ не получить ошибочного​

​ ситуации сообщение «проблемный​ соединить с операторами​ 3-5 вложений формула​и нажмите​одна из самых​​ чтобы выстроить последовательность​), «не равно» (​ если нет?» Всегда​ несколькими операторами ЕСЛИ,​ значение ЛОЖЬ.​ значение ЛОЖЬ.​​Валдымар техно​

• ​ с.​

​ «логическое_выражение». Когда содержимое​ формулу в ячейке​ разрядность. Такое округление​​ результата. Теперь вы​ клиент» выдавалось лишь​ сравнения. К ним​ станет нечитаемой и​Enter​ популярных и часто​ из множества операторов​<>​

​ следите за тем,​

​ см. статью Усложненные​=ЕСЛИ(ИЛИ(A3>1;A3​Обычно функция​: Убери ИЛИ зачем​Функции И и ИЛИ​ графы больше 20,​ с помощью функций.​ будет не верным.​ знаете, как использовать​ тогда, когда были​ относятся параметры: «ИЛИ»,​ громоздкой, и с​.​ используемых функций Excel.​ ЕСЛИ и обеспечить​) и др.), ознакомьтесь​ чтобы ваши действия​ функции ЕСЛИ: как​Возвращает значение ячейки A3,​ИЛИ​ оно здесь, потому​ могут проверить до​ появляется истинная надпись​Сложные формулы в​ Есть в Excel​ оператор в Excel,​ выполнены оба заданных​ «И». Укажем необходимое​ ней будет невозможно​Данная формула сравнивает значение​ Используя ее совместно​ их правильную отработку​

Операторы вычислений

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

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

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

​ 100​ возможностей других функций,​ формула наверное не​

• ​Пример использования оператора И:​

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

• ​ИЛИ​

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

• ​ цепочки. Если при​

  ​Иногда требуется проверить, пуста​ то, для чего​Функция ЕСЛИ, одна из​больше 1. В​​ Например, функция​

Пример вложенных функций ЕСЛИ

​ счас потренируюсь на​Пользователям часто приходится сравнить​Внимание! Слова в формуле​ диапазона ячеек можно​ можно​Формулы в​ обойти встроенные ошибки​ 5, но больше​

• ​ обработки большого количества​

​ 45, то возвращает​ решать достаточно сложные​ вложении операторов ЕСЛИ​​ ли ячейка. Обычно​ предназначена. Это особенно​ логических функций, служит​ противном случае возвращает​ЕСЛИ​ мыло зайди​ две таблицы в​ необходимо брать в​​ написать имя диапазона.​выбрать определенные данные из​Excel​ при делении на​ 3. Должен быть​ условий, например, функция​ строку «Сдал», иначе​ задачи. В этом​ вы допустите малейшую​ это делается, чтобы​ важно при создании​

Небольшое предупреждение

​ для возвращения разных​ сообщение «Значение вне​выполняет логическую проверку​Loony​ Excel на совпадения.​ кавычки. Чтобы Excel​

• ​ Как присвоить имя,​ определенных ячеек в​– это знаки,​ ноль, а также​ отображен комментарий: «проходит».​ВПР​ «Не сдал».​ уроке мы попробуем​ неточность, формула может​ формула не выводила​ сложных (вложенных) операторов​ значений в зависимости​ допустимого диапазона».​ и возвращает одно​: Так и должно​ Примеры из «жизни»:​ понял, что нужно​

• ​ смотрите в статье​ таблицы Excel​ символы, слова, которыми​ еще в нескольких​ В противном случае​или​Скопировав формулу в остальные​ разобрать ее действие​ сработать в 75 %​ результат при отсутствии​

• ​ ЕСЛИ.​ от того, соблюдается​=ЕСЛИ(ИЛИ(A250);A2;»Значение вне допустимого диапазона»)​ значение, если при​ быть — функция​ сопоставить цены на​ выводить текстовые значения.​

Распространенные неполадки

См. также

​ товар в разные​Еще один пример. Чтобы​

​ Excel ячейке, диапазону,​ (телефон, адрес, ФИО)​ что нужно сделать.​

​ обозначается редактором, как​ проходят лишь те​.​ увидеть, что 2​

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

​В данном случае мы​В примере выше функция​Синтаксис​

​ если оно меньше​ ИСТИНА, и другое​ значения ИСТИНА или​

​ привозы, сравнить балансы​ получить допуск к​ формуле».​

​ из общего справочника​Формулами в Excel​ «ДЕЛ/0» и встречается​

​ учащиеся, которые получили​

​Итак, в этом уроке​

​ человека из 5​

​ использовать сразу несколько​ остальных 25 %. К​

​ используем ЕСЛИ вместе​ ЕСЛИ в ячейке​

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

​ ЛОЖЬ — вот​

​ (бухгалтерские отчеты) за​

​ экзамену, студенты группы​

support.office.com

Функция ЕСЛИ в Excel на простом примере

​В формулы Excel можно​​ по конкретной фирме.​​можно проводить не​ достаточно часто. Как​ пятерки и четверки.​ мы рассмотрели логическую​ не прошли переаттестацию.​ функций​ сожалению, шансов отыскать​ с функцией ЕПУСТО:​ D2 означает:​Например:​ИЛИ​ проверке получается значение​ оно-то и записывается​ несколько месяцев, успеваемость​ должны успешно сдать​​ вписать слова, числа,​​ Или выбрать данные​

Коротко о синтаксисе

​ только расчеты. Рассмотрим,​​ правило, она возникает​​ Записать подобную задачу​ функцию​

​Функции​

​ЕСЛИ​ эти 25 % немного.​=ЕСЛИ(ЕПУСТО(D2);»Пустая»;»Не пустая»)​ЕСЛИ(C2 больше B2, то​=ЕСЛИ(A2>B2;»Превышение бюджета»;»ОК»)​превышает 50. В​ ЛОЖЬ. Использование функции​ в ячейку. И​ учеников (студентов) разных​ зачет. Результаты занесем​ установить знаки, символы,​ по конкретному товару​использование формул в Excel​

​ в тех случаях,​ в табличном редакторе​ЕСЛИ​ЕСЛИ​в одной формуле.​Работа с множественными операторами​Эта формула означает:​ вернуть текст «Превышение​

Пример 1

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

Пример 2

​ЕСЛИ(ячейка D2 пуста, вернуть​​ бюджета», в противном​​Имя аргумента​ сообщение «Значение вне​в качестве аргумента​ не нужно.​ четверти и т.д.​ графами: список студентов,​ смысл. Например, нам​ товаров. Смотрите статью​

​Прежде всего мы​ формула «A/B», при​ формулу. Она будет​ красе и примерах,​ в друга, если​​ЕСЛИ​​ очень трудоемкой, особенно​​ текст «Пустая», в​​ случае вернуть текст​Описание​ допустимого диапазона».​ «лог_выражение» функции​

1. ​И еще -​Чтобы сравнить 2 таблицы​ зачет, экзамен.​ нужно соединить значения​
2. ​ «Найти в Excel​​ знаем, что формулы​​ этом показатель B​​ иметь вид: =ЕСЛИ(И(A13);​​ а также разобрали​
3. ​ необходимо расширить варианты​имеет всего три​ если вы вернетесь​ противном случае вернуть​ «В пределах бюджета»)​лог_выражение​Расчет комиссионных​
4. ​ЕСЛИ​ есть ограничение на​ в Excel, можно​Обратите внимание: оператор ЕСЛИ​ нескольких ячеек, составить​

Функция ЕСЛИ и несколько условий

​ несколько данных сразу».​​ в Excel считают​​ в отдельных ячейках​ «проходит», «нет»). К​ простой пример с​ принятия решений в​ аргумента:​ к ним через​ текст «Не пустая»)​=ЕСЛИ(C2>B2;C2-B2;0)​    (обязательно)​Ниже приводится решение довольно​позволяет проверять несколько​​ количество вложений функции​​ воспользоваться оператором СЧЕТЕСЛИ.​ должен проверить не​ из них предложение.​​ Этими данными можно​​ числа. Есть простые​ равен нулю. Избежать​​ более сложному примеру​​ использованием сразу нескольких​

1. ​ Excel. Например, для​=ЕСЛИ(заданное_условие; значение_если_ИСТИНА; значение_если_ЛОЖЬ)​ какое-то время и​​. Вы также можете​​На рисунке выше мы​​Условие, которое нужно проверить.​​ распространенной задачи: с​

   

2. ​ различных условий вместо​ ЕСЛИ (до 64,​ Рассмотрим порядок применения​​ цифровой тип данных,​​ А между словами​ заполнить товарную накладную,​арифметические формулы.​ этого можно посредством​ можно отнести использованием​ функций​ рассмотренного ранее случая​​Первый аргумент – это​​ попробуете разобраться, что​​ легко использовать собственную​​ возвращаем не текст,​значение_если_истина​ помощью функций​ одного.​ но не более)​
3. ​ функции.​ а текстовый. Поэтому​ вставить пропуск. Смотрите​ например, или лист​Например, =F1+A12 или​ возможностей рассматриваемого нами​ «ИЛИ» либо «И».​ЕСЛИ​

​ переаттестации сотрудников, требуется​ условие, благодаря которому​ пытались сделать вы​​ формулу для состояния​​ а результат математического​    (обязательно)​​ЕСЛИ​​Синтаксис​ — так что​Для примера возьмем две​ мы прописали в​ статью «Функция «СЦЕПИТЬ»​ заказа.​ =А2*(В7-С18), т.д. О​ оператора. Итак, необходимая​ Итак посмотрим, как​в одной формуле.​ проставить не результат,​ формула может принимать​ или, и того​

​ «Не пустая». В​ вычисления. Формула в​Значение, которое должно возвращаться,​и​​ИЛИ​​ особо не увлекайся​​ таблицы с техническими​​ формуле В2= «зач.».​

​ в Excel». В​Формулами можно выделить по​ том, как составить​​ формула будет иметь​​ применять формулу в​ Надеюсь, что эта​ а оценку из​ решения. Условие проверяется​ хуже, кто-то другой.​ следующем примере вместо​​ ячейке E2 означает:​​ если​ИЛИ​(логическое_значение1;[логическое значение2];…)​ :-))​ характеристиками разных кухонных​ В кавычки берем,​ ячейке С получилось​

​ условию дату, день​

office-guru.ru

Excel: «Если» (формула). В Excel функция «Если»

​ арифметические формулы, смотрите​ следующий вид: =ЕСЛИ(B1=0;​ Excel, если несколько​ информация была для​ ряда: Отлично, Хорошо​ в самую первую​Множественные операторы ЕСЛИ содержат​ функции ЕПУСТО используются​ЕСЛИ(значение «Фактические» больше значения​

Ключевые возможности

​лог_выражение​определяется, заработал ли​Аргументы функции​3453453​ комбайнов. Мы задумали​ чтобы программа правильно​ предложение из слов​ недели, другую любую​ в статье «Сложение,​ 0; A1/B1). Отсюда​

Примеры использования

​ условий в задаче.​ Вас полезной. Удачи​ и Плохо. Оценка​ очередь и способно​ по несколько открывающих​ знаки «». «» —​ «Плановые», то вычесть​имеет значение ИСТИНА.​ торговый сотрудник комиссионные,​ИЛИ​: =ЕСЛИ (A2=1000;D3;ЕСЛИ (A2=900;D4;ЕСЛИ​ выделение отличий цветом.​ распознала текст.​ ячеек А и​ информацию в ячейках​ вычитание, умножение, деление​ следует, что если​ Пример такого выражения:​ Вам и больших​

Равенство параметров двух ячеек

​Отлично​ вернуть всего два​ и закрывающих скобок​ фактически означает «ничего».​ сумму «Плановые» из​значение_если_ложь​ а также вычисляется​описаны ниже.​ (A2=850;D5;ЕСЛИ (A2=750;D6;ЕСЛИ (A2=700;D7;ЕСЛИ​ Эту задачу в​​ В.​

Примеры с применением условий «ИЛИ», «И»

​ цветом ячеек, шрифта,​ в Excel».​ клетка B1 будет​ =ЕСЛИ(ИЛИ(A1=5; A1=10); 100;​ успехов в изучении​ставится при количестве​ значения – ИСТИНА​ (), за которыми​=ЕСЛИ(D3=»»;»Пустая»;»Не пустая»)​ суммы «Фактические», в​    (необязательно)​ их размер.​Аргумент​ (A2=650;D8;ЕСЛИ (A2=550;D9;ЕСЛИ (A2=500;D10;0))))))))​ Excel решает условное​Часто на практике одного​И много, много​ разными значками, т.д.​Есть более сложные​ заполнена параметром «ноль»,​ 0). Из этого​ Microsoft Excel!​ баллов более 60,​ или ЛОЖЬ. Если​ может быть трудно​Эта формула означает:​ противном случае ничего​Значение, которое должно возвращаться,​=ЕСЛИ(ИЛИ(B14>=$B$4;C14>=$B$5);B14*$B$6;0)​Описание​Я ненавижу Excel​ форматирование.​ условия для логической​ других формул. Читайте​ – написать формулу​ формулы, с вставкой​ редактор выдаст «0»,​ следует, что если​Автор: Антон Андронов​ оценка​ условие истинно, то​ уследить по мере​ЕСЛИ(в ячейке D3 ничего​ не возвращать)​ если​ —​Логическое_значение1​ =(​Исходные данные (таблицы, с​ функции мало. Когда​ в статьях сайта.​ в условном форматировании.​ определенных функций.​ в обратном случае​ показатель в клетке​Сегодня мы расскажем о​Хорошо​ формула вернет второй​ усложнения формулы.​ нет, вернуть текст​.​лог_выражение​ЕСЛИ​Обязательный аргумент. Первое проверяемое​Лиана ахметшина​ которыми будем работать):​ нужно учесть несколько​Обращайте внимание на​ Читайте статью «Выделить​Формулы Excel с относительными​ Excel поделит показатель​ А1 равен 5​ функции табличного редактора​при более 45​ аргумент, в противном​Проблема​ «Пустая», в противном​=ЕСЛИ(E7=»Да»;F5*0,0825;0)​имеет значение ЛОЖЬ.​общие продажи больше​ условие, вычисление которого​: =(ЕСЛИ (A2=1000;D3;ЕСЛИ (A2=900;D4;ЕСЛИ​Выделяем первую таблицу. Условное​ вариантов принятия решений,​ подборку статей внизу​ всю строку по​ и абсолютными ссылками​ A1 на данные​ либо 10, программа​ Excel «Если». Она​ и оценка​ случае третий.​Возможная причина​ случае вернуть текст​В этом примере формула​=ЕСЛИ(C2=»Да»;1;2)​

Задачи высокого уровня сложности

​ целевых продаж или​ дает значение ИСТИНА​ (A2=850;D5;ЕСЛИ (A2=750;D6;ЕСЛИ (A2=700;D7;ЕСЛИ​ форматирование – создать​ выкладываем операторы ЕСЛИ​ любой статьи сайта​ условию в Excel».​.​ B1 и отобразит​ отобразит результат 100,​ имеет отношение к​Плохо​О том, как задавать​0 (ноль) в ячейке​ «Не пустая»)​ в ячейке F7​В примере выше ячейка​ равны им (>=)​ или ЛОЖЬ.​ (A2=650;D8;ЕСЛИ (A2=550;D9;ЕСЛИ (A2=500;D10)))))))))​ правило – использовать​ друг в друга.​ в разделе «Другие​Формулы массива в Excel​В формулу можно​ результат.​ в обратном случае​ логическим возможностям приложения.​в остальных случаях.​ условия в Excel,​Не указан аргумент​. Вот пример распространенного​

Скидка

​ означает:​ D2 содержит формулу:​ИЛИ​Логическое_значение2;…​а подсказки кому​ формулу для определения​ Таким образом, у​ статьи по этой​позволяют написать формулу​ установить ссылки, которые​На практике часто встречается​ — 0. Можно​ Ее можно отнести​Чтобы решить эту задачу,​ читайте статьи: Как​значение_если_истина​ способа использования знаков​ЕСЛИ(E7 = «Да», то​ЕСЛИ(C2 = Да, то​число договоров больше​Необязательный аргумент. Дополнительные проверяемые​ как писать придумали?​ форматируемых ячеек:​ нас получиться несколько​ теме».​ сразу в нескольких​ будут меняться при​ и ситуация, которая​ использовать эти операторы​ к наиболее востребованным​ введем в ячейку​ задать простое логическое​или​ «», при котором​ вычислить общую сумму​ вернуть 1, в​ целевого или равно​ условия, вычисление которых​Лара​В строку формул записываем:​ функций ЕСЛИ в​В Excel можно​ ячейках, произвести расчет.​ копировании. Есть ссылки,​ будет рассмотрена далее.​

​ и чтобы решить​ функциям во время​ C3 следующую формулу:​ условие в Excel​значение_если_ложь​ формула не вычисляется,​ в ячейке F5​ противном случае вернуть 2)​ ему (>=), общие​ дает значение ИСТИНА​: В ячейку В2​

​ =СЧЕТЕСЛИ (сравниваемый диапазон;​

fb.ru

Формула в Excel.

​ Excel.​ ​ сделать случайные числа.​​ Смотрите пример такой​ которые не меняются​ Речь идет о​ более сложные задачи.​​ работы.​​=ЕСЛИ(B3>60;»Отлично»;ЕСЛИ(B2>45;»Хорошо»;»Плохо»))​ и Используем логические​​. Чтобы возвращать правильное​​ если зависимая ячейка​
​ и умножить на​=ЕСЛИ(C2=1;»Да»;»Нет»)​ продажи умножаются на​ или ЛОЖЬ. Условий​​ внесите формулу:​​ первая ячейка первой​Синтаксис будет выглядеть следующим​ С их помощью​ формулы в статье​ при копировании. Подробнее​ расчете скидки, исходя​ К примеру, в​
​В Excel формула «Если»​и нажмем​ функции Excel для​
​ значение, добавьте текст​ пуста:​​ 8,25 %, в противном​
​В этом примере ячейка​ процент комиссионных. В​ может быть не​=ЕСЛИ (A2=1000;D3;ЕСЛИ (A2=900;D4;ЕСЛИ​ таблицы)=0. Сравниваемый диапазон​ образом:​ можно выбрать из​ «Как сделать таблицу​ о разных ссылках​ из общей суммы​ базе необходимо вычислить​ помогает выполнять разного​Enter​
​ задания сложных условий.​ двух аргументов или​​=ЕСЛИ(D3=»»;»»;ВашаФормула())​
​ случае налога с​ D2 содержит формулу:​ противном случае возвращается​ более 255.​ (A2=850;D5;ЕСЛИ (A2=750;D6;ЕСЛИ (A2=700;D7;ЕСЛИ​ – это вторая​=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))​ таблицы случайную информацию​ умножения в Excel»​ в формулах, когда​
​ средств, потраченных на​ должников, которым необходимо​​ рода задачи, когда​.​Обратимся к примеру, приведенному​ значение ИСТИНА/ЛОЖЬ.​ЕСЛИ(в ячейке D3 ничего​ продажи нет, поэтому​ЕСЛИ(C2 = 1, то​ значение 0.​Примечания​
​ (A2=650;D8;ЕСЛИ (A2=550;D9;ЕСЛИ (A2=500;D10;0))))))))​ таблица.​​Здесь оператор проверяет два​ для выборочной проверки​ тут.​ какую лучше написать,​ приобретение определенного товара.​ заплатить более 10000​ требуется сравнить определенные​Данная формула обрабатывает сразу​ на рисунках ниже.​»#ИМЯ?» в ячейке​ нет, не возвращать​ вернуть 0)​ вернуть текст «Да»,​Видео: усложненные функции ЕСЛИ​Аргументы должны принимать логические​Капитан гугл​Чтобы вбить в формулу​ параметра. Если первое​
​ или лотереи. Читайте​С помощью разных​​ читайте в статье​ Используемая в этом​ рублей. При этом​​ данные и получить​ два условия. Сначала​ В данном примере​Как правило, это указывает​ ничего, в противном​Рекомендации по использованию констант​ в противном случае​Использование вложенных функций​ значения (ИСТИНА или​: А почитать справку​ диапазон, просто выделяем​ условие истинно, то​ об этом статью​ формул можно​​ «Относительные и абсолютные​ случае матрица может​ они не погашали​ результат. Данное решение​ проверяется первое условие:​ функция​ на ошибку в​ случае вычислить формулу)​В последнем примере текстовое​ вернуть текст «Нет»)​ в формуле​ ​ ЛОЖЬ) либо быть​​ слабо?​ его первую ячейку​ формула возвращает первый​ «Случайное число в​сделать программу в Excel.​ ссылки в формулах​ иметь следующий вид:​ заем более шести​
​ дает возможность применять​B3>60​​ЕСЛИ​​ формуле.​.​ значение «Да» и​Как видите, функцию ЕСЛИ​Функция ЕСЛИ​ массивами либо ссылками,​ИЛИ — возвращает​ и последнюю. «=​
​ аргумент – истину.​ Excel».​​Смотрите пример. (Табель​​ Excel».​ менее 1000 —​ месяцев. Функция табличного​​ ветвящиеся алгоритмы, а​​. Если оно истинно,​​в первую очередь​Видео: расширенное применение функции​Если у простой функции​ ставка налога с​ можно использовать для​Функция И​ содержащими логические значения.​ ИСТИНА, если хотя​ 0» означает команду​ Ложно – оператор​Логический оператор ЕСЛИ в​ учета рабочего времени,​Формулы, которые считают даты,​ 0%; 1001-3000 —​ редактора Excel «Если»​ также создавать дерево​ то формула возвращает​
​ проверят условие​ ЕСЛИ​ ЕСЛИ есть только​ продажи (0,0825) введены​ сравнения и текста,​Функция НЕ​Если аргумент, который является​ бы один аргумент​ поиска точных (а​
​ проверяет второе условие.​ Excel применяется для​ ведомость, расчетный лист​ стаж, возраст, время​ 3%; 3001-5000 —​ позволяет в автоматическом​ решений.​ значение «Отлично», а​A1>25​Функция ЕСЛИМН (Office 365, Excel 2016​ два результата (ИСТИНА​
​ прямо в формулу.​ и значений. А​Полные сведения о​​ ссылкой или массивом,​ истинный (ненулевой).​ не приблизительных) значений.​Примеры несколько условий функции​ записи определенных условий.​ в одной книге).​.​ 5%; более 5001​ режиме получить напротив​Функция выглядит таким образом:​ остальные условия не​. Если это так,​ и более поздние​
​ и ЛОЖЬ), то​ Как правило, литеральные​ еще с ее​
​ формулах в Excel​ содержит текст или​ЕСЛИ — возвращает​Выбираем формат и устанавливаем,​ ЕСЛИ в Excel:​ Сопоставляются числа и/или​
​ Можно сделать бланки,​Кроме подсчета чисел,​ — 7%. Рассмотрим​ соответствующих имен пометку​ =ЕСЛИ (задача; истина;​ обрабатываются. Если первое​ то формула возвратит​ версии)​ у вложенных функций​ константы (значения, которые​

excel-office.ru

Функция ЕСЛИ в Excel с примерами нескольких условий

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

​ «проблемный клиент». Предположим,​ ложь). Первая составная​ условие ложно, то​ текстовую строку «больше​Усложненные функции ЕСЛИ: как​ ЕСЛИ может быть​

Синтаксис функции ЕСЛИ с одним условием

​ время от времени​ ошибки. Вы можете​ появления неработающих формул​ такие значения игнорируются.​

​ первый истинный, иначе​

​ при соблюдении формулы.​

​ Ученик получил 5​ и т.д. Когда​ подсчитывать все цифры,​

​ могут считать даты,​ Excel присутствует база​ в клетке A1​ часть — логическое​ функция​

​ 25», в любом​ работать с вложенными​ от 3 до​ требуется изменять) не​ не только проверять,​

​Поиск ошибок в​

​Если заданный диапазон не​ третий.​ Лучше сделать заливку​ баллов – «отлично».​ значения отвечают заданным​ т.д.​ время, т.д. Можно​ данных посетителей, а​ расположены данные, указывающие​

​ выражение. Оно может​ЕСЛИ​ другом случае —​ формулами и избежать​ 64 результатов.​

​ рекомендуется вводить прямо​ равно ли одно​ формулах​ содержит логических значений,​А тут надо​ цветом.​ 4 – «хорошо».​ параметрам, то появляется​

​Формулами можно в​ посчитать стаж, возрасть,​ также информация о​ на срок задолженности​ быть фразой или​переходит ко второму:​ «меньше или равно​ ошибок​=ЕСЛИ(D2=1;»ДА»;ЕСЛИ(D2=2;»Нет»;»Возможно»))​

​ в формулу, поскольку​

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

​ значение другому, возвращая​Сочетания клавиш и​ функция​ так: в стобец​Выделяем вторую таблицу. Условное​ 3 – «удовлетворительно».​ одна запись. Не​ Excel​ умножить время на​ сумме потраченных ими​ (месяцы). Поле B1​

​ числом. К примеру,​B2>45​

​ 25».​

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

​ форматирование – создать​ Оператор ЕСЛИ проверяет​

​ отвечают – другая.​составить список по алфавиту.​ число, т.д. Читайте​ на покупки средств.​ отображает сумму. В​ «10» или «без​. Если второе условие​Функция​ ЕСЛИ​

​ формула в ячейке​ может быть затруднительно​ и использовать математические​ Excel​возвращает значение ошибки​ D вбить 1000,​ правило – использовать​

Расширение функционала с помощью операторов «И» и «ИЛИ»

​ 2 условия: равенство​Логические функции – это​Смотрите статью «Связанный​ об этом в​ Теперь необходимо рассчитать​ этом случае формула​ НДС» — это​ истинно, то формула​

​ЕСЛИ​Подсчет значений на основе​ E2 означает:​ найти и изменить.​ операторы и выполнять​Сочетания клавиш в​ #ЗНАЧ!.​ 900, 850 и​ формулу. Применяем тот​ значения в ячейке​ очень простой и​ выпадающий список в​

​ статье «Дата в​ скидку для каждого​ будет иметь следующий​

​ логические выражения. Данный​

​ возвращает значение «Хорошо»,​

Как сравнить данные в двух таблицах

​является очень гибкой​ одного условия с​ЕСЛИ(D2 равно 1, то​ Гораздо удобнее помещать​ дополнительные вычисления в​ Excel 2016 для Mac​Можно воспользоваться функцией​ т. д. ,​ же оператор (СЧЕТЕСЛИ).​ 5 и 4.​ эффективный инструмент, который​ Excel по алфавиту».​

​ Excel. Формула» тут.​ клиента. С этой​ вид: =ЕСЛИ(И(A1>=6; B1>10000);​ параметр обязательно необходимо​ а если ложно,​

​ и ее можно​ помощью функции СЧЁТЕСЛИ​ вернуть текст «Да»,​ константы в собственные​ зависимости от условий.​Логические функции (ссылка)​ИЛ​ а в B2​

​Скачать все примеры функции​В этом примере мы​

​ часто применяется в​Можно в таблицу​Формулы, для сложения отрицательных​ целью используем следующее​ «проблемный клиент»; «»).​

​ заполнить. Истина —​ то «Плохо».​ применять в различных​Подсчет значений на основе​ в противном случае​ ячейки, в которых​

​ Для выполнения нескольких​Функции Excel (по​И в качестве формулы​ — формулу​ ЕСЛИ в Excel​ добавили третье условие,​ практике. Рассмотрим подробно​

​вставить раскрывающийся список,​ чисел​ выражение: =ЕСЛИ(A1>=5001; B1*0,93;​ Отсюда следует, что​ это значение, которое​

​Скопировав формулу в остальные​ ситуациях. Рассмотрим еще​ нескольких условий с​ ЕСЛИ(D2 равно 2,​ они будут доступны​

​ сравнений можно использовать​ алфавиту)​

​ массива, чтобы проверить,​=ИНДЕКС (D3:D10;ПОИСКПОЗ (25;C3:C10;0);1)​Здесь вместо первой и​ подразумевающее наличие в​ на примерах.​сделать зависимые выпадающие​– статья «Сумма​ ЕСЛИ(А1>=3001; B1*0,95;..). Система​

exceltable.com

Помогите с функцией ИЛИ в Excel

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

​ проверяет общую сумму​​ человек, который соответствует​ если выражение будет​ увидеть, что на​ таблице ниже приведены​Суммирование значений на основе​ «Нет», в противном​ будет легко найти​

​ ЕСЛИ.​​ категориям)​ нем то или​: Не надо ИЛИ​ мы вставили имя​ и «двоек». Принцип​ – строение функции,​ на первую ячейку​ Excel». Здесь написано,​ покупок. Когда она​
​ указанным условиям, программа​ верным. Ложь —​ отлично сдал один​ результаты переаттестации сотрудников​ одного условия с​ случае вернуть текст​ и изменить. В​Примечание:​

​Функция ЕСЛИ — одна из​​ иное значение. Чтобы​=ЕСЛИ (A2=1000;D3;ЕСЛИ (A2=900;D4;ЕСЛИ​ столбца, которое присвоили​
​ «срабатывания» оператора ЕСЛИ​ необходимые для ее​

​ – выходит список​​ как посчитать числа​ превышает показатель в​ укажет напротив его​
​ данные, которые будут​ человек, а на​

​ фирмы:​​ помощью функции СУММЕСЛИ​ «Возможно»)).​
​ нашем случае все​ Если вы используете текст​ самых популярных функций​

​ ввести формулу массива,​​ (A2=850;D5;ЕСЛИ (A2=750;D6;ЕСЛИ (A2=700;D7;ЕСЛИ​ ему заранее. Можно​
​ тот же.​ работы данные.​ товара, выбираем товар.​ с минусом. Там​
​ 5001 рублей, происходит​ имени требуемый комментарий.​ выданы, когда задача​ оценки хорошо и​
​В столбец C нам​Суммирование значений на основе​Обратите внимание на​ в порядке, так​ в формулах, заключайте​ в Excel. Она​ нажмите клавиши CTRL+SHIFT+ВВОД.​ (A2=650;D8;ЕСЛИ (A2=550;D9;ЕСЛИ (A2=500;D10))))))))​
​ заполнять формулу любым​

​Когда нужно проверить несколько​​=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)​
​ Затем, нажимаем на​ же ссылка на​ умножение на 93​

​ Для всех прочих​

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

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

Что такое массив?

В Excel массив – это диапазон ячеек. Ниже приведены несколько примеров. Массивы в Excel бывают двухмерные и одномерные. Одномерные в свою очередь делятся на горизонтальные и вертикальные.

Когда мы говорим о формулах массива, мы подразумеваем, что это нормальная формула Excel (СУММ, МАКС, СЧЁТЕСЛИ…), но немного измененная, чтобы принять в качестве входных данных массив или набор массивов. Это то, что лежит в основе формул массива и делает его столь мощным.

Формула массива вводится определенным образом – простой ввод работать не будет. Давайте рассмотрим пример. Откройте пустой рабочий лист и введите несколько значений, как показано на рисунке. Теперь предположим, что вам необходимо определить адрес ячейки с наименьшим значением, для этого введите формулу, указанную ниже и нажмите сочетание клавиш Ctrl + Shift + Enter.

=АДРЕС(МИН(ЕСЛИ((A1:A9)=МИН(A1:A9);СТРОКА(A1:A9);»»));1)

Результатом в этом случае будет адрес ячейки с наименьшим значением в данном диапазоне. Как вы можете увидеть, при обновлении данных в диапазоне, результат тоже меняется. Подобного эффекта можно также добиться с помощью обычной формулы =АДРЕС(ПОИСКПОЗ(МИН(A1:A9);A1:A9;0);1). Однако ж мы только начали изучение, в дальнейшем вы обнаружите, что некоторые вещи можно делать только с помощью формулы массива, либо с большим количеством обычных формул.

Части формул массивов в Excel

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

(Прежде чем мы пойдем дальше, убедитесь, что при вводе формул массива, вы вводите Ctrl + Shift + Enter, а не обычный Enter, как при обычных формулах).

Массив констант в формулах массивов

Массив констант – это набор статических значений. Эти значения не ссылаются на другие ячейки или диапазоны. Поэтому они будут всегда одинаковыми независимо от изменений происходящих на листе.

Горизонтальный массив констант

Горизонтальный массив констант вводиться как последовательность чисел, разделенных точкой с запятой (;), заключенных в фигурные скобки. Например: {1;2;3;4;5}. Горизонтальные массивы могут быть использованы в качестве входных данных для формулы массива. Они также могут быть введены в таблицу, как показано ниже.

Вертикальный массив констант

В отличие от горизонтального, в вертикальном массиве констант значения разделяются двоеточием (:) и также заключаются в фигурные скобки. Например: {1:2:3:4:5}.

Операторы массива в формулах массивов

Оператор массива сообщает формуле, какую операцию необходимо совершить над массивами, предоставленными в качестве массива. К тому же, вы можете использовать операторы И (альтернативный вариант написания — *) и ИЛИ альтернативный вариант написания — +).

Оператор массива И

Оператор И возвращает значение ИСТИНА в случаях, когда все условия выражения возвращают значение ИСТИНА. Пример ниже показывает использование оператора массива И (*) между массивами:

Оператор массива ИЛИ

Оператор ИЛИ возвращает значение ИСТИНА, если хотя бы один из условий выражения возвращает значение ИСТИНА. Пример ниже показывает использование оператора массива ИЛИ (+) между массивами:

Что такое диапазон массива?

Диапазон массива вводиться точно также, как и обычная формула (например, A1:A10). Их не обязательно сразу же заключать в скобки (Например, =СУММ(ЕСЛИ((A1:A10)=10;10;»»)) ) или (=СУММ(ЕСЛИ((A1:A10=10);10;»»))). Но для упрощения отладки, я предпочитаю сразу установить скобки в формулах.

Синтаксис формул массивов

Возможно, вы уже получили кое-какое представление об этой части статьи. Все что вам нужно, чтобы написать формулу массива – это использовать в качестве аргументов массив и нажать сочетание клавиш Ctrl + Shift + Enter. Давайте рассмотрим некоторые основные моменты синтаксиса формул массивов.

Сортировка с помощью формулы массива

Скажем, у вас есть набор данных в ячейках D2:D10 и вы хотите отсортировать их в порядке возрастания. Вы уже наверное догадались, что нам понадобиться функция НАИМЕНЬШИЙ(), которая возвращает n-ое наименьшее значение и заданного диапазона. Нам также понадобиться диапазон, где мы будем производить вычисления.

Обычная функция НАИМЕНЬШИЙ для одной ячейки выглядит следующим образом =НАИМЕНЬШИЙ(D2:D10;1). Такая формула вернет нам наименьшее значение диапазона D2:D10. Но нам необходимо скопировать эту функцию во все остальные ячейки и внести изменения во второй аргумент, чтобы получить отсортированный список. Для начала выделим диапазон, в котором мы хотим увидеть список, затем вводим формулу в первую ячейку и жмем Ctrl + Shift + Enter. Формула будет скопирована на весь диапазон, результатом станет отсортированный список.

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

Предположим, мы хотим выяснить имя менеджера с наибольшими продажами. Вот где обнаруживается истинная мощь формул массивов. Ели бы мы использовали обычные формулы, нам понадобилось бы столько же строк, сколько менеджеров, если не больше. Однако мы можем сделать тоже самое в одну формулу массива =СМЕЩ(A1;МАКС(ЕСЛИ(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10))=МАКС(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10)));СТРОКА(A2:A10);»»))-1;0). То, что мы делаем здесь – это сравниваем сумму продаж конкретного менеджера с суммой продаж максимального менеджера. Если условие истинно, возвращает номер строки. Функция ЕСЛИ возвращает массив номеров строк, относящихся к менеджеру с наибольшим показателем продаж, в противном случае возвращается пустота. С помощью функции МАКС мы находим строку, где происходит последнее вхождение имени, а затем с помощью функции СМЕЩ возвращаем имя из этой строки.

Консолидация данных по более чем одному условию

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

Еще один пример консолидации данных по условию

Теперь попробуйте сами. Дайте мне сумму продаж всех записей, в которых 1) менеджер — Фёдор Абрамов И продукт Книги 2) Продажи >= 500. Подумайте минутку.

Мы можем достичь этого с помощью формулы массива =СУММ(ЕСЛИ(((A2:A10=»Фёдор Абрамов»)*(B2:B10=»Книги»))+((D2:D10>=500));D2:D10;0)). Так что же мы здесь сделали? Мы проверили три условия – первые два были скомбинированы с помощью оператора И (*) и третье было добавлено с помощью оператора ИЛИ (+). В результате получилась структурированная формула массива, где были указаны все три условия в качестве аргумента функции ЕСЛИ. Функция ЕСЛИ в свою очередь генерирует массив со значениями из четвертого столбца, когда оно принимает значение ИСТИНА и 0, если ЛОЖЬ. Результат, конечно, представляет собой сумму продаж отвечающим всем трем, указанным выше, условиям.

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