Сегодня мы рассмотрим функцию ЕСЛИ .
Функция ЕСЛИ часто используется в Excel для решения многих задач. Знать ее очень полезно. В данной статье мы попробуем рассказать про ее работу на простых примерах, достаточно один раз разобраться с конструкцией функцией ЕСЛИ и вы сможете применять ее и в самых сложных вариантах.
Функция ЕСЛИ проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет.
Синтаксис функции ЕСЛИ очень простой:
ЕСЛИ(лог_выражение ; [значение_если_истина ]; [значение_если_ложь ])
лог_выражение – это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.
Что это значит? Выражение при вычислении дает значение ИСТИНА если это выражение верно.
В этой части необходимо проверить на соответствие выражения.
Например:
ЕСЛИ(А1=10 ; [значение_если_истина]; [значение_если_ложь]) - если А1 равно 10, то выражение А1=10 даст значение ИСТИНА, а если не равно 10, то ЛОЖЬ
Другой пример
ЕСЛИ(А1>30 ; [значение_если_истина]; [значение_если_ложь]) - если в ячейки А1 число больше 30, то А1>30 вернет ИСТИНА, а если меньше, то ЛОЖЬ
Еще пример
ЕСЛИ(С1=”Да” ; [значение_если_истина]; [значение_если_ложь]) - если в ячейки C1 содержится слово “Да” то выражение вернет значение ИСТИНА, а если нет, то С1=”Да” вернет ЛОЖЬ
ЕСЛИ(лог_выражение ; [значение_если_истина ]; [значение_если_ложь ])
значение_если_истина , значение_если_ложь – как видно из их названия, это то что необходимо сделать в зависимости от того, что вернул лог выражения : ИСТИНА и ЛОЖЬ
Пример использования функции ЕСЛИ в Excel
Рассмотрим использование функции ЕСЛИ на практическом примере. У нас есть таблица заказов, которую мы использовали при рассмотрении работы . Нам необходимо заполнить столбец по заказам Ведер (ошибочно на картинке указано «Заказы Cтолов»), то есть необходимо выбрать только заказы с Ведрами. Это можно сделать различными способами, но мы с вами будет использовать функцию ЕСЛИ, чтобы показать ее работу на примере. (см.рисунок)
Для решения поставленной задачи напишем формулу с использованием функции ЕСЛИ
ЕСЛИ(A3="Ведро";D3;"-")
Как вы смогли заметить аргументы функции ЕСЛИ разделены точкой с запятой.
Итак, первый аргумент (лог выражения ) A3="Ведро" проверяет содержится ли в ячейке А3 слово «Ведро», если содержится, то выполняется второй аргумент функции ЕСЛИ (значение_если_истина ), в нашем случае это D3 (т.е стоимость заказа), если в ячейка А3 не равна слову «Ведро», то выполняется третий аргумент функции ЕСЛИ (значение_если_ложь ), в нашем случае это «-» (т.е будет написано тире).
Таким образом, в ячейки E3 появится значение D3, т.е число 240.
Откроется диалоговое окно функции если.
Лог_выражение — это то что мы будет проверять. Поместите курсор в данное поле. В нашем случае как вы помните мы сначала проверяем продажи больше 20 (то есть больше плана) или нет. Продажи Алексея у нас в ячейке B3 — кликните мышкой на B3 и это выражение появится в этом поле (можно просто прописать B3, но удобнее выбирать). Далее нам необходимо проверить эти продажи Алексея с планом. Напишем B3> и выберем ячейку с планом G2, У нас должно получиться выражение B3>G2
Для того, чтобы в дальнейшем применить данную формулу для других сотрудников, протянув ее вниз, нам необходимо закрепить ссылку в ячейке G2, чтобы она стало абсолютной (то есть не менялась, когда мы будет протягивать формулу). Для этого необходимо прописать знаки доллара перед буквой и цифрой или только перед цифрой, так как мы не будет протягивать формулу влево или вправо. Удобнее всего зафиксировать ячейку просто выделив в формуле нужную ячейку и нажав клавишу F4
Должно получиться вот так B3>$G$2 или так B3>G$2
Обратите внимание, Excel автоматически вычисляет выражение и отображает его справа. В нашем случае Excel вычислил, что выражение — ИСТИНА, то есть действительно Алексей сделал продаж в количестве 35 шт, что больше верхней границе плана 20 шт. Обратите внимание, что если бы у Алексея было бы недовыполнение плана, то формулу мы бы все равно писали точно так же — гипотетически рассуждая, как если бы у Алексея был бы выполнен план.
Значение_если_истина . Переведите курс в это поле. Тут нам необходимо рассчитать оплату сотруднику, если он выполнил план. Стандартная оплата 2000 (F4) плюс так как мы рассматриваем ситуацию когда план перевыполнен, нужно прибавить переработку. Для этого вычтем из всего продаж план и получим количество продаж сверх нормы (B3-G2) и умножим их на оплату за перевыполнение 25 рублей (F5). В итоге получаем следующий расчет 2000+(35-20)*25 то есть F4+(B3-G2)*F5
так же не забываем, что данные из таблицы условий нам нужно закрепить, чтобы они сдвигались при протягивании формулы. Для этого выделяем нужные значения и нажимаем F4
Получаем итоговый расчет: $F$4+(B3-$G$2)*$F$5
Переходим к следующему полю.
Значение_если_ложь. Итак, выше мы рассчитали оплату сотруднику, если план продаж выполнен (то есть если наше выражение в первом поле верно — возвращает истину). Если нет, то осталось два варианта: либо сотрудник точно выполнил план продаж (от 18 до 20шт), либо недовыполнил. Снова два условия, потребуется вложенная ЕСЛИ. Будет легче, если мы сначала проверим условие невыполнение плана, а оставшийся вариант будет выполнение плана.
В этом поле можно написать вложенную функцию ЕСЛИ вручную, но это не очень удобно, поэтому мы снова воспользуемся диалоговыми окнами. Перейдите в ячейку Значение_если_ложь откройте раскрывающийся список последних использованных функции в правом углу и выберите ЕСЛИ
После этого у вас снова появится новое диалоговое окно функции ЕСЛИ. Не надо паниковать предыдущая информация не стерлась, а просто свернулась. Вы можете это видеть в строке формул. Это сделано для удобство.
Заполняем поле лог_выражение
, как вы помните мы проверяем условие о невыполнение плана сотрудником. То есть прописываем (выбираем) B3 Далее поле Значение_если_истина
. То есть если действительно B3 Получаем следующую формулу: $F$4-($F$2-B3)*$F$6 Отлично. Если план перевыполнен, то мы посчитали оплату, если не выполнен, мы проверяем сотрудника на невыполнения плана. Если он не выполнил план, то мы считаем ему оплату. Если же он и не перевыполнил план и не недовыполнил план, то значил от сделал ровно план от 18 до 20 шт. Поэтому в следующем поле мы рассчитываем оплату сотруднику, когда он выполнил план, то есть он получает 2000 рублей (ячейка F4). Переходим в поле Значение_если_ложь и прописываем (выбираем) F4. Не забываем закрепить ссылку - $F$4 Вот так будет выглядеть вложенное ЕСЛИ Нажимаем «Ок» — формула готова. Вот так выглядит полная формула: ЕСЛИ(B3>$G$2;$F$4+(B3-$G$2)*$F$5;ЕСЛИ(B3<$F$2;$F$4+($F$2-B3)*$F$6;$F$4)) С помощью диалоговых окон очень просто пользоваться функцией ЕСЛИ и при необходимости не запутаться с вложенными ЕСЛИ, главное изначально подумать о последовательности проверки ЕСЛИ. Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат. ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще) Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй - если сравнение ложно. Операторы ЕСЛИ чрезвычайно надежны и являются неотъемлемой частью многих моделей электронных таблиц. Но они же часто становятся причиной многих проблем с электронными таблицами. В идеале оператор ЕСЛИ должен применяться для минимума условий (например, "Женский"/"Мужской", "Да"/"Нет"/"Возможно"), но иногда сценарии настолько сложны, что для их оценки требуется использовать вместе больше 3 вложенных* функций ЕСЛИ. * "Вложенность" означает объединение нескольких функций в одной формуле. Функция ЕСЛИ, одна из логических функций , служит для возвращения разных значений в зависимости от того, соблюдается ли условие. Синтаксис
ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь]) Например: ЕСЛИ(A2>B2;"Превышение бюджета";"ОК") ЕСЛИ(A2=B2;B4-A4;"") Excel позволяет использовать до 64 вложенных функций ЕСЛИ, но это вовсе не означает, что так и надо делать. Почему? Нужно очень крепко подумать, чтобы выстроить последовательность из множества операторов ЕСЛИ и обеспечить их правильную отработку по каждому условию на протяжении всей цепочки. Если при вложении вы допустите в формуле малейшую неточность, она может сработать в 75 % случаев, но вернуть непредвиденные результаты в остальных 25 %. К сожалению, шансов отыскать эти 25 % немного. Работа с множественными операторами ЕСЛИ может оказаться чрезвычайно трудоемкой, особенно если вы вернетесь к ним через какое-то время и попробуете разобраться, что пытались сделать вы или, и того хуже, кто-то другой. Если вы видите, что ваш оператор ЕСЛИ все разрастается, устремляясь в бесконечность, значит вам пора отложить мышь и пересмотреть свою стратегию. Давайте посмотрим, как правильно создавать операторы с несколькими вложенными функциями ЕСЛИ и как понять, когда пора переходить к другим средствам из арсенала Excel. Ниже приведен пример довольно типичного вложенного оператора ЕСЛИ, предназначенного для преобразования тестовых баллов учащихся в их буквенный эквивалент. ЕСЛИ(D2>89;"A";ЕСЛИ(D2>79;"B";ЕСЛИ(D2>69;"C";ЕСЛИ(D2>59;"D";"F")))) Этот сложный оператор с вложенными функциями ЕСЛИ следует простой логике: Если тестовых баллов (в ячейке D2) больше 89, учащийся получает оценку A. Если тестовых баллов больше 79, учащийся получает оценку B. Если тестовых баллов больше 69, учащийся получает оценку C. Если тестовых баллов больше 59, учащийся получает оценку D. В противном случае учащийся получает оценку F. Этот частный пример относительно безопасен, поскольку взаимосвязь между тестовыми баллами и буквенными оценками вряд ли будет меняться, так что дополнительных изменений не потребуется. Но что если вам потребуется разделить оценки на A+, A и A– (и т. д.)? Теперь ваши четыре условных оператора ЕСЛИ нужно переписать с учетом 12 условий! Вот так будет выглядеть ваша формула: ЕСЛИ(B2>97;"A+";ЕСЛИ(B2>93;"A";ЕСЛИ(B2>89;"A-";ЕСЛИ(B2>87;"B+";ЕСЛИ(B2>83;"B";ЕСЛИ(B2>79;"B-"; ЕСЛИ(B2>77;"C+";ЕСЛИ(B2>73;"C";ЕСЛИ(B2>69;"C-";ЕСЛИ(B2>57;"D+";ЕСЛИ(B2>53;"D";ЕСЛИ(B2>49;"D-";"F")))))))))))) Она по-прежнему точна и будет правильно работать, но вы потратите много времени, чтобы написать ее, а потом протестировать. Еще одна очевидная проблема состоит в том, что вам придется вручную вводить баллы и эквивалентные буквенные оценки. Каковы шансы, что вы не ошибетесь? А теперь представьте, как вы пытаетесь сделать это 64 раза для более сложных условий! Конечно, это возможно. Но неужели вам хочется потратить столько сил без всякой уверенности в отсутствии ошибок, которые потом будет трудно обнаружить? Совет:
Для каждой функции в Excel обязательно указываются открывающая и закрывающая скобки (). При редактировании Excel попытается помочь вам понять, что куда идет, окрашивая разными цветами части формулы. Например, во время редактирования показанной выше формулы при перемещении курсора за каждую закрывающую скобку ")" тем же цветом будет окрашиваться соответствующая открывающая скобка. Это особенно удобно в сложных вложенных формулах, когда вы пытаетесь выяснить, достаточно ли в них парных скобок. Ниже приведен распространенный пример расчета комиссионных за продажу в зависимости от уровней дохода. ЕСЛИ(C9>15000;20%;ЕСЛИ(C9>12500;17,5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>7500;12,5%;ЕСЛИ(C9>5000;10%;0))))) Эта формула означает: ЕСЛИ(ячейка C9 больше 15 000, то вернуть 20 %, ЕСЛИ(ячейка C9 больше 12 500, то вернуть 17,5 % и т. д... На первый взгляд все очень похоже на предыдущий пример с оценками, однако на примере этой формулы хорошо видно, насколько сложно бывает работать с большими операторами ЕСЛИ. Что вы будете делать, если ваша организация решит добавить новые уровни компенсаций или изменить имеющиеся суммы или проценты? У вас появится очень много работы! Совет:
Чтобы сложные формулы было проще читать, вы можете вставить разрывы строк в строке формул. Просто нажмите клавиши ALT+ВВОД перед текстом, который хотите перенести на другую строку. Перед вами пример сценария для расчета комиссионных с неправильной логикой: Видите, что происходит? Посмотрите порядок сравнения доходов в предыдущем примере. А как все идет в этом? Именно! Сравнение идет снизу вверх (от 5 000 до 15 000 ₽), а не наоборот. Ну и что в этом такого? Это важно, потому что формула не может пройти первую оценку для любого значения, превышающего 5 000 ₽. Скажем, ваш доход составил 12 500 ₽ - оператор ЕСЛИ вернет 10 %, потому что это больше 5 000 ₽, и на этом остановится. Это может быть очень проблематично, поскольку ошибки такого типа часто остаются незамеченными, пока не оказывают негативного влияния. Так что же вам делать теперь, когда вы знаете, какие трудности могут ожидать вас при использовании вложенных операторов ЕСЛИ? В большинстве случаев вместо сложной формулы с функциями ЕСЛИ можно использовать функцию ВПР. При использовании функции ВПР вам для начала нужно создать ссылочную таблицу: ВПР(C2;C5:D17;2;ИСТИНА) В этой формуле предлагается найти значение ячейки C2 в диапазоне C5:C17. Если значение найдено, возвращается соответствующее значение из той же строки в столбце D. ВПР(B9;B2:C6;2;ИСТИНА) Эта формула ищет значение ячейки B9 в диапазоне B2:B22. Если значение найдено, возвращается соответствующее значение из той же строки в столбце C. Примечание:
В обеих функциях ВПР в конце формулы используется аргумент ИСТИНА, который означает, что мы хотим найти близкое совпадение. Иначе говоря, будут сопоставляться точные значения в таблице подстановки, а также все значения, попадающие между ними. В этом случае таблицы подстановки нужно сортировать по возрастанию, от меньшего к большему. Функция ВПР подробно рассматривается , но очевидно, что она значительно проще, чем сложный 12-уровневый вложенный оператор ЕСЛИ. Есть и другие, менее очевидные, преимущества: Таблицы ссылок функции ВПР открыты и их легко увидеть. Значения в таблицах просто обновлять, и вам не потребуется трогать формулу, если условия изменятся. Если вы не хотите, чтобы люди видели вашу таблицу ссылок или вмешивались в нее, просто поместите ее на другой лист. Теперь есть функция УСЛОВИЯ , которая может заменить несколько вложенных операторов ЕСЛИ. Так, в нашем первом примере оценок с 4 вложенными функциями ЕСЛИ: ЕСЛИ(D2>89;"A";ЕСЛИ(D2>79;"B";ЕСЛИ(D2>69;"C";ЕСЛИ(D2>59;"D";"F")))) можно сделать все гораздо проще с помощью одной функции ЕСЛИМН: ЕСЛИМН(D2>89;"A";D2>79;"B";D2>69;"C";D2>59;"D";ИСТИНА;"F") Функция ЕСЛИМН - просто находка! Благодаря ей вам больше не нужно переживать обо всех этих операторах ЕСЛИ и скобках. Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Программа Excel, безусловно, обладает очень богатым функционалом. И среди множества разнообразных инструментов особое место занимает оператор «ЕСЛИ». Он помогает в решении совершенно различных задач, и к этой функции пользователи обращаются намного чаще, чем к остальным. В этой статье мы поговорим о том, что же такое оператор «ЕСЛИ», а также рассмотрим область применения и принципы работы с ним. Оператор «ЕСЛИ» – это инструмент программы Эксель для проверки определенного условия (логического выражения) на выполнение. То есть, представим, что у нас есть какое-то условие. Задача “ЕСЛИ” – проверить соблюдение данного условия и вывести в ячейку с функцией значение, основанное на результате проверки. Сама формула функции в программе представляет собой следующее выражение: =ЕСЛИ(условие; [значение если условие выполнено]; [значение если условие не выполнено])
Вероятно, вышеизложенная информация может показаться не такой понятной. Но, на самом деле ничего сложного здесь нет. И для того, чтобы лучше понять назначение функции и ее работу рассмотрим пример ниже. У нас есть таблица с наименованиями спортивной обуви. Представим, что скоро у нас будет распродажа, и всей женской обуви нужно проставить скидку 25%. В одном из столбцов в таблице как раз прописан пол для каждого наименования. Наша задача - в столбце “Скидка” вывести значение «25%» для всех строк с наименованиями для женского пола. И соответственно, значение «0», если в столбце «Пол» стоит значение «мужской» Заполнение данных вручную займет немало времени, и есть высокая доля вероятности где-то ошибиться, особенно, если список длинный. Гораздо проще в этом случае будет автоматизировать процесс при помощи оператора «ЕСЛИ». Для выполнения этой задачи потребуется составить следующую формулу ниже: =ЕСЛИ(B2=”женский”;25%;0)
Данную формулу пишем в самой верхней ячейке столбца “Скидка” и нажимаем Enter. Не забываем ставить знак равно (=) перед формулой. После этого для данной ячейки будет отображен результат согласно нашему логическому условию (на забываем настроить формат ячеек – процентный). Если проверка выявит, что пол «женский» – будет выведено значение 25%. В противном случае значение ячейки будет равно 0. Собственно говоря, что нам и требовалось. Теперь осталось только скопировать данное выражение на все строки. Для этого подведите курсор мыши к правому нижнему краю ячейки с формулой. Указатель мышки при этом должен превратиться в крестик. Зажмите левую кнопку мыши и растяните формулу на все строки, которые нужно проверить согласно заданным условиям. Вот и все, теперь мы применили условие ко всем строкам и получили результат для каждой из них. Мы только что рассмотрели пример использования оператора «ЕСЛИ» с одним логическим выражением. Но в программе также имеется возможность задавать больше одного условия. При этом сначала будет проводиться проверка по первому, и в случае его успешного выполнения сразу отобразится заданное значение. И только если не будет выполнено первое логическое выражение, в силу вступит проверка по второму. Рассмотрим наглядно на примере все той же таблицы. Но на этот раз усложним задачу. Теперь нужно проставить скидку на женскую обувь в зависимости от вида спорта. Первое условия – это проверка пола. Если “мужской” – сразу выводится значение 0. Если же это “женский”, то начинается проверка по второму условию. Если вид спорта бег – 20%, если теннис – 10%. Пропишем формулу для этих условий в нужной нам ячейке. =ЕСЛИ(B2=”мужской”;0; ЕСЛИ(C2=”бег”;20%;10%))
Щелкаем Enter и получаем результат согласно заданным условиям. Также в Эксель существует возможность вывести данные по одновременному выполнению двух условий. При этом значение будет считаться ложным, если хотя бы одно из условий не выполнено. Для этой задачи применяется оператор «И»
. Рассмотрим на примере нашей таблицы. Теперь скидка 30% будет проставлена только, если это женская обувь и предназначена для бега. При соблюдении этих условий одновременно значение ячейки будет равно 30%, в противном случае – 0. Для этого используем следующую формулу: =ЕСЛИ(И(B2=”женский”;С2=”бег”);30%;0)
Нажимаем клавишу Enter, чтобы отобразить результат в ячейке. Аналогично примерам выше, растягиваем формулу на остальные строки. В этом случае значение логического выражения считается истинным при выполнении одного из условий. Второе условие при этом может не выполняться. Поставим задачу следующим образом. Скидка 35% предоставляется только на мужскую обувь для тенниса. Если это мужская обувь для бега или любая женская обувь, скидка равна 0. В данном случае нужна следующая формула: =ЕСЛИ(ИЛИ(B2=”женский”; C2=”бег”);0;35%)
После нажатия Enter мы получим требуемое значение. Растягиваем формулу вниз и скидки для всего ассортимента готовы. Использовать функцию ЕСЛИ можно не только прописывая ее вручную в ячейке или строке формул, но также и через Построитель формул. Давайте разберем, как это работает. Допустим, нам снова как и в первом примере нужно проставить скидку на всю женскую обувь в размере 25%. Одним из самых популярных и полезных инструментов в Excel является функция ЕСЛИ
, которая проверяет данные на совпадение заданным нами условиям и выдает результат в автоматическом режиме, что исключает возможность ошибок из-за человеческого фактора. Поэтому, знание и умение применять этот инструмент позволит сэкономить время не только на выполнение многих задач, но и на поиски возможных ошибок из-за “ручного” режима работы. Логический оператор ЕСЛИ в Excel применяется для записи определенных условий. Сопоставляются числа и/или текст, функции, формулы и т.д. Когда значения отвечают заданным параметрам, то появляется одна запись. Не отвечают – другая. Логические функции – это очень простой и эффективный инструмент, который часто применяется в практике. Рассмотрим подробно на примерах. Синтаксис оператора в Excel – строение функции, необходимые для ее работы данные. ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь) Разберем синтаксис функции: Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки). Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы). Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы). Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20». Внимание! Слова в формуле необходимо брать в кавычки. Чтобы Excel понял, что нужно выводить текстовые значения. Еще один пример. Чтобы получить допуск к экзамену, студенты группы должны успешно сдать зачет. Результаты занесем в таблицу с графами: список студентов, зачет, экзамен. Обратите внимание: оператор ЕСЛИ должен проверить не цифровой тип данных, а текстовый. Поэтому мы прописали в формуле В2= «зач.». В кавычки берем, чтобы программа правильно распознала текст. Часто на практике одного условия для логической функции мало. Когда нужно учесть несколько вариантов принятия решений, выкладываем операторы ЕСЛИ друг в друга. Таким образом, у нас получиться несколько функций ЕСЛИ в Excel. Синтаксис будет выглядеть следующим образом: ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь)) Здесь оператор проверяет два параметра. Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие. Примеры несколько условий функции ЕСЛИ в Excel: Таблица для анализа успеваемости. Ученик получил 5 баллов – «отлично». 4 – «хорошо». 3 – «удовлетворительно». Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4. Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с. Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, то результат будет истинным. Суть такова: ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение в ИНАЧЕ значение с. Функции И и ИЛИ могут проверить до 30 условий. Пример использования оператора И: Пример использования функции ИЛИ: Пользователям часто приходится сравнить две таблицы в Excel на совпадения. Примеры из «жизни»: сопоставить цены на товар в разные привозы, сравнить балансы (бухгалтерские отчеты) за несколько месяцев, успеваемость учеников (студентов) разных классов, в разные четверти и т.д. Чтобы сравнить 2 таблицы в Excel, можно воспользоваться оператором СЧЕТЕСЛИ. Рассмотрим порядок применения функции. Для примера возьмем две таблицы с техническими характеристиками разных кухонных комбайнов. Мы задумали выделение отличий цветом. Эту задачу в Excel решает условное форматирование. Исходные данные (таблицы, с которыми будем работать): Выделяем первую таблицу. Условное форматирование – создать правило – использовать формулу для определения форматируемых ячеек: В строку формул записываем: =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. Сравниваемый диапазон – это вторая таблица. Чтобы вбить в формулу диапазон, просто выделяем его первую ячейку и последнюю. «= 0» означает команду поиска точных (а не приблизительных) значений. Выбираем формат и устанавливаем, как изменятся ячейки при соблюдении формулы. Лучше сделать заливку цветом. Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ). Скачать все примеры функции ЕСЛИ в Excel Здесь вместо первой и последней ячейки диапазона мы вставили имя столбца, которое присвоили ему заранее. Можно заполнять формулу любым из способов. Но с именем проще. Сегодня мы рассмотрим функцию ЕСЛИ
. Функция ЕСЛИ часто используется в Excel для решения многих задач. Знать ее очень полезно. В данной статье мы попробуем рассказать про ее работу на простых примерах, достаточно один раз разобраться с конструкцией функцией ЕСЛИ и вы сможете применять ее и в самых сложных вариантах. Функция ЕСЛИ
проверяет, выполняется ли условие, и возвращает одно значение, если оно выполняется, и другое значение, если нет. Синтаксис функции ЕСЛИ
очень простой: ЕСЛИ(лог_выражение; ;) лог_выражение
– это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ. Что это значит? Выражение при вычислении дает значение ИСТИНА если это выражение верно. В этой части необходимо проверить на соответствие выражения. Например: ЕСЛИ(А1=10; ;) - если А1 равно 10, то выражение А1=10 даст значение ИСТИНА, а если не равно 10, то ЛОЖЬ Другой пример ЕСЛИ(А1>30; ;) - если в ячейки А1 число больше 30, то А1>30 вернет ИСТИНА, а если меньше, то ЛОЖЬ Еще пример ЕСЛИ(С1=”Да”; ;) - если в ячейки C1 содержится слово “Да” то выражение вернет значение ИСТИНА, а если нет, то С1=”Да” вернет ЛОЖЬ ЕСЛИ(лог_выражение; ;) значение_если_истина, значение_если_ложь – как видно из их названия, это то что необходимо сделать в зависимости от того, что вернул лог выражения: ИСТИНА и ЛОЖЬ Рассмотрим использование функции ЕСЛИ на практическом примере. У нас есть таблица заказов, которую мы использовали при рассмотрении работы функции ВПР. Нам необходимо заполнить столбец по заказам Ведер (ошибочно на картинке указано «Заказы Cтолов»), то есть необходимо выбрать только заказы с Ведрами. Это можно сделать различными способами, но мы с вами будет использовать функцию ЕСЛИ, чтобы показать ее работу на примере. (см.рисунок) Для решения поставленной задачи напишем формулу с использованием функции ЕСЛИ ЕСЛИ(A3="Ведро";D3;"-") и нажмем Enter Как вы смогли заметить аргументы функции ЕСЛИ разделены точкой с запятой. Итак, первый аргумент (лог выражения) A3=»Ведро» проверяет содержится ли в ячейке А3 слово «Ведро», если содержится, то выполняется второй аргумент функции ЕСЛИ (значение_если_истина), в нашем случае это D3 (т.е стоимость заказа), если в ячейка А3 не равна слову «Ведро», то выполняется третий аргумент функции ЕСЛИ (значение_если_ложь), в нашем случае это «-» (т.е будет написано тире). Таким образом, в ячейки E3 появится значение D3, т.е число 240. Итоговый результат работы функции ЕСЛИ вы можете посмотреть на рисунке 4. Функцию ЕСЛИ можно использовать в очень многих ситуациях. Находить ошибки, находить уникальные значения в списке, использовать многократные проверки на выполнение условий. Разбор всех этих приемов Excel ждет вас впереди. Если статья была Вам полезна, то будем благодарны, если Вы поделитесь ей со своими друзьями с помощью кнопок расположенных ниже. Спасибо за внимание. Одна из самых популярных функций в приложении Excel – ЕСЛИ
. Это логическое сравнение
значения и получаемого результата. Если говорить проще, то функция может звучать так: ЕСЛИ условие истинно, то выполняем это, а иначе делаем что-либо еще
Синтаксис
этой функции достаточно прост: ЕСЛИ (логическое_условие; значение_в_случае_ИСТИНА; значение в случае_ЛОЖЬ)
Разберем подробнее: На скриншотах ниже показан самый просто вариант использования. Мы сначала проверяем
заданное условие А1>25
. ЕСЛИ это требование выполняется
, тогда выводим в ячейку «больше 25
», иначе «меньше или равно 25
». Теперь более сложное задание. Ниже мы рассмотрим пример переаттестации сотрудников предприятия. Изначально таблица выглядит так: Нам необходимо
в столбце С вывести результаты переаттестации, которые могут принимать бинарное значение: СДАЛ
или НЕ СДАЛ
. Критерии у нас будут такими: кто набрал более 45 балов
, тот считается сдавшим
экзамен, ну а все остальные нет. Для выполнения задачи необходимо
: При необходимости, функцию ЕСЛИ
можно вложить
друг в друга. Таким образом, мы расширим варианты решений. Для примера, возьмем переаттестацию сотрудников, которую рассмотрели раньше. Изменим критерии результата и выставим каждому оценку
: Плохо, Хорошо и Отлично. Отлично
будем ставить, когда баллы превысят 60
. Оценку
Хорошо можно будет получить, набрав от 45 до 60 балов
. Ну и в остальных случаях ставим Плохо
. Как видно из примера, вместо второго и третьего значения функции можно подставлять условие. Таким способом добавляем необходимое число вложений. Однако стоит отметить
, что после добавления 3-5 вложений работать с формулой станет практически невозможно, т.к. она будет очень громоздкой. К примеру, есть необходимость просуммировать каждую вторую ячейку столбца. В этом случае поможет использование оператора СУММЕСЛИ
. Он позволит работать только с теми столбцами, которые попадают под наши требования. Мы будет рассматривать случай, когда необходимо суммировать элементы в нечетных строках. Для решения задачи, нам потребуется вставить
дополнительный столбец и пронумеровать строки 1 и 0. Именно эти данные мы будем использовать в нашей формуле. В примере видно, какую формулу мы использовали. Синтаксис
: СУММЕСЛИ(диапазон_ячеек; используемое_условие;) В этом примере мы считаем, сколько клиентов находится в каждом городе. В формуле мы задаем первым диапазон, а вторым значения, которые следует искать. В примере мы постараемся определить усредненный объем дохода
от клиентов по каждому городу. Для этого поделим суммарный доход города на количество клиентов. Если у нас есть постоянно обновляемый список данных, в котором по мере роста могут появляться дубликаты
, то поиск вхождений в этом списке может оказаться затруднительным. Для решения этой задачи проще всего воспользоваться функцией ВПР
и СЧЕТЕСЛИ
. Для начала, добавим
дополнительный столбец, который добавить порядковый номер вхождения к имени клиента. В результате, первое упоминание элемента будет выдавать Имя1, следующее Имя2 и т.д. Используем поиск
по клиенту Кристина Агилера. Используя формулу ВПР («Кристина Агилера3», диапазон_поиска, 2, ЛОЖЬ), мы получим третий номер этого клиента. В формуле, последним значением ставим ЛОЖЬ, поскольку список не сортирован, и нам необходимо точное совпадение элементов. Версии до EXCEL 2003 включительно поддерживали до 7 уровней
вложенности ЕСЛИ. Начиная с версии Excel 2007, это ограничение убрали
. Но стоит заметить, что ниже 3-4 уровня вложенности мало кто опускается. Для того чтобы уменьшить количество использования вложений ЕСЛИ, можно использовать функцию ВЫБОР
. Она работает со значениями или действиями из заданного списка по номеру индекса. Синтаксис
: ВЫБОР (номер_индекса; значение_индекса1; значение_индекса2; ;…) Для примера, функция ВЫБОР (1; «Третий»; «Второй»; «Первый»)
, вернет нам слово «Третий
», если мы ее добавим в ячейку. Стоит отметить, что имеются некоторые ограничения. В частности, в качестве индекса могут выступать только числа
. Среди многих функций, с которыми работает программа Microsoft Excel, следует выделить функцию «ЕСЛИ». Это один из тех операторов, к которым пользователи прибегают чаще всего, при выполнении задач в приложении. Давайте разберемся, что представляет собой функция «ЕСЛИ», и как с ней работать. «ЕСЛИ» является стандартной функцией программы Microsoft Excel. В её задачи входит проверка выполнения конкретного условия. В случае, когда условие выполнено (истина), то в ячейку, где использована данная функция, возвращается одно значение, а если не выполнено (ложь) – другое. Синтаксис данной функции выглядит следующим образом: «ЕСЛИ(логическое выражение; ;)». Теперь давайте разберем конкретные примеры, где используется формула с оператором «ЕСЛИ». Имеем таблицу заработной платы. Всем женщинам положена премия к 8 марту в 1000 рублей. В таблице имеется колонка, в которой указан пол сотрудников. Таким образом, нам нужно сделать так, чтобы в строчке со значением «жен.» в колонке «Пол» в соответствующую ячейку колонки «Премия к 8 марта» выводилось значение «1000», а в строчках со значением «муж.» в колонках «Премия к 8 марта» стояло значение «0». Наша функция примет такой вид: «ЕСЛИ(B6=»жен.»; «1000»; «0»)». Вводим данное выражение в самую верхнюю ячейку, где должен выводиться результат. Перед выражением ставим знак «=». После этого, жмем на кнопку Enter. Теперь, чтобы данная формула появилась и в нижних ячейках, просто становимся в правый нижний угол заполненной ячейки, жмем на кнопку мышки, и проводим курсором до самого низа таблицы. Таким образом, мы получили таблицу со столбцом, заполненным при помощи функции «ЕСЛИ». В функцию «ЕСЛИ» можно также вводить несколько условий. В этом случае, применяется вложение одного оператора «ЕСЛИ» в другой. При выполнении условия, в ячейке отображается заданный результат, если же условие не выполнено, то выводимый результат зависит уже от второго оператора. Для примера возьмем все ту же таблицу с выплатами премии к 8 марту. Но, на этот раз, согласно условиям, размер премии зависит от категории работника. Женщины, имеющие статус основного персонала получают по 1000 рублей премии, а вспомогательный персонал получает только 500 рублей. Естественно, что мужчинам этот вид выплат вообще не положен независимо от категории. Таким образом, первым условием является то, что если сотрудник мужчина, то величина получаемой премии равна нулю. Если же данное значение ложно, и сотрудник не мужчина (т.е. женщина), то начинается проверка второго условия. Если женщина относится к основному персоналу, то в ячейку будет выводиться значение – «1000», а в обратном случае – «500». В виде формулы это будет выглядеть следующим образом: «=ЕСЛИ(B6=»муж.»;»0″; ЕСЛИ(C6=»Основной персонал»; «1000»;»500″))». Как и в прошлый раз, «протягиваем» формулу вниз. В функции «ЕСЛИ» можно также использовать оператор «И», который позволяет считать истинной только выполнение двух или нескольких условий одновременно. Например, в нашем случае, премия к 8 марта в размере 1000 рублей выдаётся только женщинам, которые являются основным персоналом, а мужчины, и представительницы женского пола, числящиеся вспомогательным персоналом, не получают ничего. Таким образом, чтобы значение в ячейках колонки «Премия к 8 марта» было 1000, нужно соблюдение двух условий: пол – женский, категория персонала – основной персонал. Во всех остальных случаях, значение в этих ячейках будет рано нулю. Это записывается следующей формулой: «=ЕСЛИ(И(B6=»жен.»; C6=»Основной персонал»); «1000»; «0»)». Вставляем её в ячейку. Как и в прошлые разы, копируем значение формулы на ячейки, расположенные ниже. В функции «ЕСЛИ» также может использоваться оператор «ИЛИ». Он подразумевает, что значение является истинным, если выполнено хотя бы одно из нескольких условий. Итак, предположим, что премия к 8 марта в 100 рублей положена только женщинам, которые входят в число основного персонала. В этом случае, если работник мужчина, или относится к вспомогательному персоналу, то величина его премии будет равна нулю, а иначе – 1000 рублей. В виде формулы это выглядит так: «=ЕСЛИ(ИЛИ(B6=»муж.»; C6=»Вспомогательный персонал»); «0»; «1000»)». Записываем эту формулу в соответствующую ячейку таблицы. «Протягиваем» результаты вниз. Как видим, функция «ЕСЛИ» может оказаться для пользователя хорошим помощником при работе с данными в программе Microsoft Excel. Она позволяет отобразить результаты соответствующие определенным условиям. Ничего особенно сложного в овладении принципами использования данной функцией нет. Мы рады, что смогли помочь Вам в решении проблемы.
Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро. Функция «Если» в Excel является одной из самых популярных среди всех существующих в этом редакторе. Благодаря ей вы сможете выполнить различные сравнения при помощи логических выражений. При этом можно указать, что делать в зависимости от результата выполнения условия. Принцип работы очень простой: если выражение истинное, то сделать это, иначе – сделать что-то другое. То есть, в результате проверки условия может произойти два события: Рассмотрим основной синтаксис. При вызове окна «Вставка функции», ниже указывается описание каждого предложенного варианта. Справка по функции «ЕСЛИ» выглядит следующим образом. ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) Для этого нужно выполнить следующие действия: Рассмотрим несколько различных логических выражений, чтобы вы поняли, как пользоваться этой функцией. Смысл этого выражения в следующем: если ячейка C1 содержит пустоту. То есть в ней ничего не находится. ”Ячейка C1 пустая” Именно этот текст будет выводиться в случае выполнения этого условия. ”Ячейка C1 не пустая” Данный текст мы увидим в случае ложного выполнения условия. Как видите, редактор Эксель выдал сообщение, которое мы указывали в случае ложного результата условия. Ниже вы видите список операторов сравнения: А это таблица арифметических операторов.Примечания
Примеры
Дополнительные примеры
Вы знали?
Дополнительные сведения
Определение функции “ЕСЛИ” и ее назначение
Использование функции “ЕСЛИ” на примере
Заключение
Синтаксис функции ЕСЛИ с одним условием
Функция ЕСЛИ в Excel с несколькими условиями
Расширение функционала с помощью операторов «И» и «ИЛИ»
Как сравнить данные в двух таблицах
Пример использования функции ЕСЛИ в Excel
Как использовать функцию
Пример №1
Пример №2
Используем несколько условий
Другие варианты использования функции
Находим сумму столбцов или ячеек
Количество повторений элементов на листе
Используем СЧЕТЕСЛИ и СУММЕСЛИ при подсчете данных
Поиск нескольких вхождений элемента в списке
Как сократить количество вложений ЕСЛИ
Общее определение и задачи
Пример использования
Пример функции с несколькими условиями
Пример с выполнением двух условий одновременно
Пример использования оператора «ИЛИ»
Помогла ли вам эта статья?
Как вставить функцию «ЕСЛИ»
Примеры
Операторы сравнения
Арифметические операторы
Загрузка...
