Еслимн excel 2016 пропала чем заменить
Одной из самых популярных функций Эксель является функция ЕСЛИ , но у нее есть один существенный недостаток - когда условий много, то приходится использовать вложенные функции ЕСЛИ, в результате чего формула становится очень громоздкой. Быстро найти и откорректировать в ней нужный аргумент не получится, да и вероятность допустить ошибку при написании или редактировании формулы резко увеличивается.
Начиная с Excel 2019 появилась функция ЕСЛИМН , которая делает формулу максимально простой и удобной. Также эта функция доступна и в Офисе 365.
Давайте рассмотрим простой пример, чтобы понять логику функции ЕСЛИМН .
Есть перечень сотрудников с их окладами.
Необходимо сравнить оклады с некоторой контрольной суммой и если оклад сотрудника меньше этой суммы то вывести слово « Меньше », а если больше, то, соответственно, « Больше ».
Воспользуемся функцией ЕСЛИМН .
Синтаксис функции очень прост - указываем условие и затем значение, которое будет выводиться при выполнении условия.
В таком же порядке можем вывести попарно условия и значения для 127 ситуаций. Но не стоит с условиями злоупотреблять, так как, чем их больше, тем сложнее становится «читать» формулу, ведь и условия, и значения отделяются друг от друга точкой с запятой. Поэтому проверять или редактировать длинную формулу будет не очень удобно.
Итак, нам нужно сравнить оклад сотрудника с контрольным значением. Так как мы ссылаемся на конкретную ячейку и в дальнейшем формулу планируем размножить на весь столбец, то с помощью клавиши F4 делаем ссылку абсолютной . Если это условие выполняется, то выводим слово « Больше ». По аналогии вводим следующее условие - оклад должен быть меньше контрольного значения.
Если мы ее растиражируем на весь диапазон, то увидим ошибки. Они появились по той причине, что оклады у этих сотрудников равны контрольной сумме, а в условиях функции такая ситуация никак не отражена.
Можем создать еще одно условие, в случае выполнения которого выведем, например, пустоту - двойные кавычки.
Функция ЕСЛИМН проверяет соответствие одному или нескольким условиям и возвращает значение для первого условия, принимающего значение ИСТИНА. Функцию ЕСЛИМН можно использовать вместо нескольких вложенных операторов ЕСЛИ. Ее гораздо проще читать при наличии нескольких условий.
Простой синтаксис
Как правило, синтаксис функции ЕСЛИМН выглядит следующим образом:
=ЕСЛИМН(условие1; значение1; [условие2; значение2]; [условиe3; значение3])
Обратите внимание, что функция ЕСЛИМН позволяет проверить до 127 различных условий. Однако не рекомендуется вкладывать слишком много условий с операторами ЕСЛИ или ЕСЛИМН. Это связано с тем, что несколько условий нужно вводить в правильном порядке и их может быть сложно добавлять, проверять и обновлять.
лог_выражение1 (обязательно)
Условие, принимающее значение ИСТИНА или ЛОЖЬ.
значение_если_истина1 (обязательно)
Результат, возвращаемый, если условие лог_выражение1 принимает значение ИСТИНА. Может не указываться.
лог_выражение2…лог_выражение127 (необязательно)
Условие, принимающее значение ИСТИНА или ЛОЖЬ.
значение_если_истина2. значение_если_истина127 (необязательно)
Результат, возвращаемый, если условие лог_выражениеN принимает значение ИСТИНА. Каждый аргумент значение_если_истинаN соответствует условию лог_выражениеN. Может не указываться.
Пример 1
Формула для ячеек A2:A6:
Расшифровка: ЕСЛИ(значение A2 больше 89, возвращается "A", если значение A2 больше 79, возвращается "B", и т. д. Для всех значений меньше 59 возвращается "F").
Пример 2
В ячейке G7 находится следующая формула:
Расшифровка: ЕСЛИ(значение в ячейке F2 равно 1, возвращаемое значение в ячейке D2, если значение в ячейке F2 равно 2, возвращается значение в ячейке D3, и т. д. Если ни одно из других условий не выполняется, возвращается значение в ячейке D8.
Замечания
Чтобы задать результат по умолчанию, введите значение ИСТИНА для последнего аргумента лог_выражение. Если ни одно из других условий не выполняется, возвращается соответствующее значение. Это показано в строках 6 и 7 примера 1 (с 58 баллами).
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРН 310633031600071
Как заменить ЕСЛИМН, если требуется использовать очень много условий?
Задача, решаемая этой формулой — подставить категорию продукта на основании первой буквы номенклатуры, т.е. Х — это Хлебобулочные, Б — это Бакалея и т.д. В принципе, всё работает.
Но если бы категорий было не 7, а 107, тогда этот способ не подошёл бы из-за неимоверной длины такой формулы.
У считаю, что есть формулы или связки формул, которые позволят отыскать в справочнике эту первую букву, и вернуть смещенное от неё на 1 значение, которое как раз и будет нашим искомым. Длина такой формулы будет сопоставима с тем, что у меня есть сейчас, но при увеличении количества категорий формула не будет изменяться, что очень хорошо.
Я предположил, что мне поможет в этом ПОИСКПОЗ и ДВССЫЛ, попытался применить, но тут что-то пошло не так, в общем, не могу теперь никак эту формулу в голове выстроить.
Получилась такая формула:
Но она возвращает не совсем то, что нужно. Не понятно, как отследить, где там ошибка возникает
Может ли кто-то помочь тут?
Помощь в написании контрольных, курсовых и дипломных работ здесь.
Как перезаписать значения переменых в php файле если их очень много?
Добрый день! Прошу совета у форумчан, потому что сама не смогла разобраться с такой задачей: .
Как лучше организовать список значений, если есть очень много значений?
Есть сводная таблица, в которой есть список значений. Как лучше организовать выборку нужных.
Что делать если TextBox очень много?
В форме Access надо вводить 84 однородных текстбоксов (7 груп по 12 в каждой). При этом еще хочу.
Заменить значение если одно из условий Null
Подскажите как можно подставить значение в ячейку если одно из условий будет равно NULL В столбце.
Решение
, или я какой-то хитрости не разглядел?
Добавлено через 2 минуты
Можно и без ст. J обойтись, по первой букве искать слово
Как или чем заменить функцию ЕСЛИ в формулах Excel
Функция ЕСЛИ позволяет решать большинство задач в Excel, вычисления в которых выполняются не последовательно, шаг за шагом, а с некоторыми ветвлениями. Например, был определен некоторый коэффициент, от значения которого полностью зависит ход дальнейших расчетов. Кроме того, часто требуется выполнить какую-либо операцию над диапазоном данных (суммированием, вычисление среднего значения и т. д.) с использованием какого-либо критерия. Например, найти сумму чисел из диапазона, значения которых не менее 10.
Существует как минимум три способа заменить использование функции ЕСЛИ:
- Заменить данную функцию другой встроенной функцией Excel, при этом необязательно логической. Например, одну и ту же задачу можно решить тремя разными способами (с использованием формулы ЕСЛИ, с помощью СУММЕСЛИ или без логических функций вовсе), что будет показано в одном из примеров.
- Использовать простейшие логические конструкции в связке с арифметическими действиями.
- Создание пользовательских функций с помощью VBA.
Примечание: синтаксис функции ЕСЛИ достаточно прост, поэтому избегать ее использования при решении несложных задач не нужно. Многие формулы с использованием ЕСЛИ выглядят просто и наглядно. Важными критериями итоговых формул является их краткость и понятность. Длинные формулы с большим количеством вложенных функций могут ввести в недоумение других пользователей или в будущем самих их создателей. Если критериев проверки слишком много, лучше создать пользовательскую функцию на VBA, тщательно протестировав ее поведение в различных ситуациях (насколько корректны результаты при различных условиях).
Примеры замены функции ЕСЛИ в Excel с помощью формул
Пример 1. В таблице Excel хранятся данные о доходах компании за каждый месяц (обозначен номером) прошедшего года. Реализовать алгоритм расчета суммы доходов за любых несколько месяцев года без использования функции ЕСЛИ в Excel.
Вид исходной таблицы данных:
Для расчетов суммы доходов за любой из возможных периодов в ячейке C3 запишем следующую формулу:
Описание аргументов функции СМЕЩ:
- A6 – ячейка, относительно которой ведется отсчет;
- ПОИСКПОЗ(A3;A7:A18;0) – функция, возвращающая ячейку, с которой будет начат отсчет номера месяца, с которого ведется расчет суммы доходов за определенный период;
- 1 – смещение по столбцам (нас интересует сумма доходов, а не номеров месяцев);
- ПОИСКПОЗ(B3;A7:A18;0)-ПОИСКПОЗ(A3;A7:A18;0)+1 – выражение, определяющее разность между указанными начальной и конечной позициями в таблице по вертикали, возвращающее число ячеек рассматриваемого диапазона;
- 1 – ширина рассматриваемого диапазона данных (1 ячейка).
Функция СУММ принимает в качестве аргумента диапазон ячеек, возвращаемый функцией СМЕЩ, и вычисляет сумму содержащихся в них значений.
Для примера приведем результат расчетов с 3 по 7 месяц:
Для сравнения, рассмотрим вариант расчета с использованием функции ЕСЛИ. Формула, которая приведена ниже, должна быть выполнена в качестве формулы массива (для ввода CTRL+SHIFT+Enter), а для определения суммы доходов для различных периодов ее придется видоизменять:
Диапазон ячеек, для которых будет выполняться функция СУММ, определяется двумя условиями, созданными с использованием функций ЕСЛИ. В данном случае расчет производится для месяцев с 3 по 7 включительно. Результат:
Как видно, полученные результаты совпадают. Несмотря на кажущуюся сложность формулы с использованием функции СМЕЩ, она является более простой, наглядной и не требует внесения изменений для каждого нового расчета.
Формулы решений при нескольких условиях без функции ЕСЛИ
Пример 2. В таблице Excel содержатся значения вероятностей попадания в цель для стрелков из трех различных видов оружия. Рассчитать вероятность попадания в цель хотя бы из одного оружия для каждого стрелка. В некоторых ячейках содержатся ошибочные данные (значения взяты не из диапазона допустимых значений для вероятности). Для таких случаев рассчитать вероятность как 0. При условии, что в формуле нельзя использовать логическую функцию ЕСЛИ.
Вид исходной таблицы:
Для расчета вероятности используем формулу P(A)=1-q1q2q3, где q1,q2 и q3 – вероятности промахов (событий, противоположным указанным, то есть попаданию в цель). Используем следующую формулу:
Результаты расчета для всех стрелков:
Таким образом, расчет производится только в том случае, если все три ячейки A, B и C содержат корректные данные. Иначе будет возвращен результат 0.
Пример кода макроса как альтернативная замена функции ЕСЛИ
Пример 3. В МФО выдают кредиты на срок от 1 до 30 дней на небольшие суммы под простые проценты (сумма задолженности на момент выплаты состоит из тела кредита и процентов, рассчитанных как произведение тела кредита, ежедневной процентной ставки и количества дней использования финансового продукта). Однако значение процентной ставки зависит от периода, на который берется кредит, следующим образом:
- От 1 до 5 дней – 1,7%;
- От 6 до 10 дней – 1,9%;
- От 11 до 15 дней – 2,2%;
- От16 до 20 дней – 2,5;
- Свыше 21 дня – 2,9%.
Выполнить расчет суммы к возврату для нескольких клиентов организации.
Вид исходной таблицы данных:
Вместо проверки множества условий с использованием функции ЕСЛИ напишем простую пользовательскую функцию с помощью макроса (ALT+F11). Исходный код пользовательской функции MFODebt:
Для определения размера процентной ставки используется простая и наглядная конструкция Select Case. Воспользуемся созданной функцией для расчетов:
«Растянем» формулу на остальные ячейки и получим следующие результаты:
Как видно, формулы с большим числом проверок лучше реализовывать в виде пользовательских функций.
Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.
Долгое время в Excel не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.
Пусть имеются данные
Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:
Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.
Теперь, меняя название группы, можно без всяких фильтров и сводных таблиц видеть максимальное значение внутри этой группы.
Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.
Указанное выражение отбирает только те значения, для которых название группы совпадает с условием в ячейке D2. Вот, как это видит Excel
На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.
Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.
Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение.
Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)
Есть ряд значений, каждое из которых входит в некоторую группу. Нужно рассчитать максимальное значение по группе А. Используем формулу МАКСЕСЛИ.
Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.
Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.
Недоступна функция МАКСЕСЛИ
Помощь в написании контрольных, курсовых и дипломных работ здесь.
Функция недоступна.
Добрый день. Помогите разобраться в трабле. Суть такова. База на 97 акцесе просто переносится на.
Недоступна функция is_user_logged_in() в плагине
Написал маленький плагин, который должен проверять залогинен ли текущий пользователь и является ли.
Как написать свою функцию =МАКСЕСЛИ
Привет, Ребята помогите, кто знает, с написанием функции VBA =МАКСЕСЛИ , тоже самое что и.
Среда передачи недоступна
Здравствуйте! При попытке искусственно открыть порт для Tunngle (руководствовался этим видео).
Подсистема печати недоступна
Есть в бухгалтерии три компа, которые подключены к одному принтеру, недавно на одном из компов при.
Подсистема печати недоступна
С недавних времен у меня сами по себе исчезли все установленные принтеры в моей операционной.
Веб-страница недоступна
Добрый вечер. Я простой пользователь, прошу у вас помощи. Вот такая проблема возникла: .
Система печати недоступна
При попытке установить локальный или сетевой принтер система пишет — сервер печати недоступен.
Как найти минимальное значение по условию
В Excel версии 2016 и выше появилась встроенная функция МИНЕСЛИ, можете пользоваться ей. Если ваш Excel более ранней версии, то эту функцию можно использовать установив надстройку VBA-Excel.
У функции следующие аргументы =МИНЕСЛИ(ДИАПАЗОН;КРИТЕРИЙ;[ ДИАПАЗОН_ПОИСКА ])
ДИАПАЗОН — Диапазон проверяемых ячеек.
Пример 1
В качестве критерия можно указывать значения и логические выражения:
- Рассмотрим следующий пример в котором определяется минимальная оценка по литературе. Для этого в параметр КРИТЕРИЙ указано значение "Литература", а в параметр ДИАПАЗОН — список предметов.
- Если в качестве критерия указать логическое выражение "<>Русский", то определится минимальная оценка по всем предметам за исключением русского языка.
Пример 2
В следующем примере параметр ДИАПАЗОН_ПОИСКА не задан, поэтому минимальное значение определяется среди ячеек указанных в параметре ДИАПАЗОН .
Наконец-то и до меня дошла волна обновлений и в моем Excel 2016 по подписке Office 365 Pro Plus появились новые функции для работы с текстом и логикой. Давайте я пробегусь по ним на примерах:
Функция СЦЕП (CONCAT)
Многим, думаю, известна функция СЦЕПИТЬ (CONCATENATE) , которую можно использовать для склеивания фрагментов текста из нескольких ячеек в одно целое. На практике, часто также используется спецсимвол & для аналогичного действия. Но оба этих способа предполагают указание каждой ячейки с текстовым фрагментом отдельно, что, при большом количестве ячеек, начинает напрягать:
В апрельском обновлении Excel 2016 добавили функцию СЦЕП , которая работает совершенно аналогично, но позволяет задать сразу целый диапазон (даже двумерный), всё содержимое ячеек которого будет склеено в единое целое:
Функция ОБЪЕДИНИТЬ (TEXTJOIN)
В предыдущем примере видно, что для красивого объединения мне пришлось предусмотреть отдельные ячейки с символами-разделителями. Новая функция ОБЪЕДИНИТЬ работает аналогично СЦЕП , но автоматически добавляет еще заданный символ между разными фрагментами:
Также обратите внимание на второй аргумент этой функции - он определяет, нужно ли игнорировать пустые ячейки (ИСТИНА) или нет (ЛОЖЬ).
В реальной практике такая функция представляется весьма полезной. Например, для склейки адресных строк это будет весьма компактным и красивым решением:
Функция УСЛОВИЯ (IFS)
Любой пользователь Excel сталкивается в работе с необходимостью проверять несколько условий и делать потом различные действия, в зависимости от выполнения одного из них. Обычно это реализуется с помощью вложенных друг в друга функций ЕСЛИ (IF) и выглядит при большом количестве условий не очень понятно (новичков, почему-то, особо пугает куча закрывающих скобок в конце - от всех ЕСЛИ):
Теперь же можно ощутимо упростить формулу, если использовать новую функцию УСЛОВИЯ (IFS) . В её аргументах попарно перечисляются условия и то, что нужно вывести при их выполнении:
Получается не сильно компактнее, но заметно понятнее, не правда ли?
- Функция проверяет условия в порядке их указания и при выполнении какого-либо условия уже не проверяет оставшиеся . Поэтому порядок условий играет роль: в приведенном выше примере проверка нарушений скорости идет от большего к меньшему, а не наоборот.
- Если нужно задать результат, который должен выводиться, если ни одно из условий не выполнилось (как Else в VBA), то в конце списка условий можно задать условие ИСТИНА. В нашем примере - это сумма штрафа (0), если ни одного из нарушений скорости не было.
Некий аналог предыдущей функции или конструкции Select Case в языке программирования Visual Basic, если вам знакомы макросы. Сравнивает ячейку с набором заданных значений и выдает один из заданных в наборе результатов. Например:
Очень похоже на предыдущую функцию УСЛОВИЯ , но в ней условия можно задавать гибко (использовать знаки <>= и т.д.), а здесь проверяется только точное совпадение. Последним аргументом можно задать то значение, которое должно выводиться, если ни одно из предыдущих условий не выполнилось. Раньше, чтобы реализовать что-то подобное приходилось шаманить с функцией ИНДЕКС (INDEX) и массивом констант в фигурных скобках внутри формулы:
Функции МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)
Совсем недавно писал статью и делал видео в Приемах на тему поиска максимального и минимального значений в таблице при выполнении одного или нескольких условий . Не суперсложная техника, но без формул массива или функций баз данных не обойтись. Теперь в Excel 2016 появились новые функции, которые все делают красиво:
Как видно, все по аналогии с СУММЕСЛИМН, СЧЁТЕСЛИМН и т.д. - переучиваться не нужно.
Приятно, что Microsoft начала прислушиваться к мнению пользователей и добавлять новые удобные функции с каждым обновлением Excel. Происходит это не так быстро, как хотелось бы, но появилась надежда увидеть сумму прописью в Excel уже при моей жизни
Файл со всеми вышеприведенными примерами новых функций можно скачать ниже:
Читайте также: