Mini Excel Tutorial: использование логической логики для обработки сложных данных

Логические операции лежат в основе программирования и расширенного онлайн-поиска.

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

Функция ЕСЛИ

Если, как вы можете себе представить, оценивает, верно ли определенное условие. Этот тип функции очень прост в программировании

и обычно возвращает 1, если условие истинно, и 0, если оно ложно. К счастью, Excel в IF немного более гибок, чем этот. Синтаксис Excel выглядит следующим образом:

=IF(logical_test, [value_if_true], [value_if_false])

Как видите, вам нужно указать логический тест (например, A1C16 или D5 = «alpha») и два возвращаемых значения. Если условие выполнено, в ячейке, в которой вы ввели синтаксис, будет отображаться значение, введенное вами для [value_if_true]. Если нет, он отобразит [value_if_false]. Давайте посмотрим, как вы можете использовать это в реальной таблице.

В нашей примерной электронной таблице (созданной случайным образом с помощью www.generatedata.com) у нас есть список имен, фамилий, городов, штатов и число, представляющее оценку SAT. Во-первых, мы попробуем что-то простое: посмотрим, какие оценки выше 1600. Вот синтаксис, который мы будем использовать:

=IF(E2>1600, "true", "false")

В столбце F будет указано «true» для каждого студента, набравшего более 1600 баллов, и «false» для всех остальных.

После использования автозаполнения

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

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

, Но сейчас мы рассмотрим некоторые способы их объединения с AND и OR, чтобы сделать его более мощным.

Функция НЕ

NOT является противоположностью IF: он возвращает «TRUE», если указанное вами условие не выполняется. Вот синтаксис:

=NOT([logical_test])

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

Функция И

В то время как функция IF проверяет, было ли выполнено одно условие, функция AND проверяет, были ли выполнены два. Если они есть, функция возвращает TRUE, а если нет, FALSE — вы не можете выбирать пользовательские возвращаемые значения, как вы можете с IF; хотя есть способы обойти это путем объединения операторов, если вам нужно.

Синтаксис Excel функции AND выглядит следующим образом:

=AND([logical_test1], [logical_test2], [logical_test3]...)

Многоточие в конце указывает, что вы можете включить столько разных логических тестов, сколько захотите, — И вернет ИСТИНА, только если все эти тесты пройдены. Возвращаясь к нашей примерной электронной таблице, допустим, вы хотите найти студентов, которые набрали средние баллы по SAT, между 1050 и 1950. Это синтаксис, который мы будем использовать:

=AND(E2>1050, E2<1950)

Вот как это выглядит в Excel:

И вот результат.

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

=AND(E2>1050, E2<1950, D2="Wisconsin")

Функция ИЛИ

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

=OR([logical_test1], [logical_test2]...)

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

=OR(D2="Minnesota", "D2="Wisconsin", D2="Iowa", D2="Nebraska", D2="Missouri", D2="North Dakota", D2="South Dakota", D2="Indiana", D2="Michigan", D2="Ohio", D2="Illinois", D2="Kansas")

Применительно к электронной таблице вы можете видеть, что каждый ученик Среднего Запада теперь помечен как ИСТИНА.

В Excel 2013 также есть эксклюзивное ИЛИ, которое будет возвращать ИСТИНА, только если выполняется одно и только одно из условий. Функция XOR используется так же, как и OR, но вернет false, если выполняется более одного из условий. Это полезно, но, вероятно, не стоит обновлять Office

за.

Сочетание IF, NOT, AND и OR

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

Скажем, рекрутер колледжа был назначен для вызова высокопроизводительных студентов в Калифорнии, средних учеников в Орегоне и низкоэффективных студентов в Вашингтоне или Неваде. Как мы можем реализовать это, используя эти функции? Мы должны вложить несколько из них:

=OR(AND(D2="California", E2>1950), AND(D2="Oregon", AND(E2>1050, E2<1950)), AND(OR(D2="Washington", D2="Nevada"), E2<1050))

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

• AND(D2="California", E2>1950)

• AND(D2="Oregon", AND(E2>1050, E2<1950))

• AND(OR(D2="Washington", D2="Nevada"), E2<1050)

Первый аргумент AND содержит два простых логических теста. Второй аргумент AND имеет вложенную функцию AND, поэтому он вернет истину только в том случае, если студент из Орегона и имеет оценку выше 1050 и ниже 1950. Третий аргумент содержит функцию OR, которая создает аналогичное требование. Давайте посмотрим, что произойдет, когда мы запустим это в нашей таблице рекрутинга:

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

Включите Excel

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

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

Как вы используете эти логические операторы? Какие другие советы Excel у вас есть для читателей? Поделитесь своими мыслями ниже!