Перейти к содержимому

Функция xlfn ifs excel чем заменить

  • автор:

Проблема: _xlfn. префикс отображается перед формулой

Объект _xlfn. Префикс отображается перед функцией в формуле. Когда формула вычисляется, отображается #NAME? (значение ошибки).

Причина

Книга Excel содержит функцию, которая не поддерживается в версии Excel, которая выполняется в данный момент. Например, вы могли открыть книгу, содержащую функцию IFERROR, которая не поддерживается в версиях Excel более ранних версий, чем Excel 2007. Функции, которые не поддерживаются в версиях Excel, предшествующих Excel 2007, включают: AVERAGEIF, AVERAGEIFS, MAXIFS, MINIFS, IFS, CUBEKPIMEMBER, CUBEMEMBER, CUBEMEMBERPROPERTY, CUBERANKEDMEMBER, CUBESET, CUBESETCOUNT, CUBEVALUE, COUNTIFS, IFERROR и SUMIFS.

Решение

Удалите неподдерживаемые функции или, если возможно, замените неподдерживаемые функции поддерживаемыми функциями.

Примечание: Эти функции доступны, если у вас есть Microsoft 365 подписки. Найдите Office версии.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.

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

Функция имеет следующий синтаксис.

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

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

значение_если_истина – значение или формула, которое возвращается при наступлении указанного в первом параметре события.

значение_если_ложь – это альтернативное значение или формула, которая возвращается при невыполнении условия. Данное поле не обязательно заполнять. В этом случае при наступлении альтернативного события функция вернет значение ЛОЖЬ.

Очень простой пример. Нужно проверить, превышают ли продажи отдельных товаров 30 шт. или нет. Если превышают, то формула должна вернуть «Ок», в противном случае – «Удалить». Ниже показан расчет с результатом.

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

Продажи первого товара равны 75, т.е. условие о том, что оно больше 30, выполняется. Следовательно, функция возвращает то, что указано в следующем поле – «Ок». Продажи второго товара менее 30, поэтому условие (>30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

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

Прогноз остатков

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

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

В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

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

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

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

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

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

Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2<1, то условия B2<0,9 и B2<0,95 Excel бы просто «не заметил», т.к. они входят в интервал B2<1 который проверился бы первым (если значение менее 0,9, само собой, оно также меньше и 1). И тогда у нас получилось бы только два возможных варианта: менее 1 и альтернативное, т.е. 1 и более.

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

Подсказка функции

В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку

Ошибка из-за нехватки скобки

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

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

В MS Excel 2016 появилась функция ЕСЛИМН, ради которой и написана вся эта статья. Это та же ЕСЛИ, только заточенная специально для проверки множества условий. Теперь не нужно сто раз писать ЕСЛИ и считать открытые скобки. Достаточно перечислить условия и в конце закрыть одну скобку.

Работает следующим образом. Возьмем пример выше и воспользуемся новой формулой Excel ЕСЛИМН.

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

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

Стоит обратить внимание на следующее. Условия по-прежнему перечисляем в правильном порядке, чтобы не произошло ненужного перекрытия диапазонов. Последнее альтернативное условие, в отличие от обычной ЕСЛИ, также должно быть обязательно указано. В ЕСЛИ задается только альтернативное значение, которое наступает, если не выполняется ни одно из перечисленных условий. Здесь же нужно указать само условие, которое в нашем случае было бы B2>=1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.

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

Что такое _xlfn в формуле Excel?

xlfn в Excel

Иногда, при работе с «чужими» файлами(т.е. с теми, которые были созданы или отредактированы на другом ПК), в формулах можно встретить формулы, которые начинаются на =_xlfn. (нечто вроде: =_xlfn.MAXIFS , _xlfn.IFERROR и т.п.)

При первом открытии файла такие функции показывают последний успешно вычисленный результат и при вычислении не обновляются, а если попытаться их пересчитать вручную, то в результате можно получить ошибку #ИМЯ! (#NAME!) .

Что это и откуда? Для начала попробуем разобраться откуда берется это загадочное _xlfn. и что оно означает. Таким префиксом обозначаются функции, которые были созданы в более новых версиях Excel(чем та, в которой на данный момент открыт файл) и в текущей версии отсутствуют. По сути они читаются как =_xlfn. ИМЯ_ФУНКЦИИ . Например, =_xlfn.MAXIFS – это функция МАКСЕСЛИ (MAXIFS) , _xlfn.IFERROR – ЕСЛИОШИБКА (IFERROR) , _xlfn.UNIQUE – УНИК (UNIQUE) и т.д. Т.е. после самого префикса _xlfn идет как раз имя недоступной функции на английском. Если Вы увидели такой префикс в своем Excel в какой-либо формуле на листе — значит ваш Excel не поддерживает ту функцию, которая указана после префикса _xlfn.
Ниже перечень некоторых функций с их переводом на русскую локализацию и версии, начиная с которых эти функции будут работать(соответственно для версий ниже функции недоступны):

Префикс Функция и доступность
_xlfn.IFERROR ЕСЛИОШИБКА (доступна, начиная с Excel 2007)
_xlfn.AGGREGATE АГРЕГАТ (доступна, начиная с Excel 2010)
_xlfn.GAMMA ГАММА (доступна, начиная с Excel 2013)
_xlfn.MAXIFS МАКСЕСЛИ (доступна, начиная с Excel 2019 и в Excel 365)
_xlfn.MINIFS МИНЕСЛИ (доступна, начиная с Excel 2019 и в Excel 365)
_xlfn.CONCAT СЦЕП (доступна, начиная с Excel 2019 и в Excel 365)
_xlfn.SORTBY СОРТПО (доступна, начиная с Excel 2019 и в Excel 365)
_xlfn.XLOOKUP ПРОСМОТРХ (доступна, начиная с Excel 2021 и в Excel 365)
_xlfn.UNIQUE УНИК (доступна, начиная с Excel 2021 и в Excel 365)
_xlfn.LAMBDA LAMBDA (доступна только в Excel 365)
_xlfn.TOCOL ПОСТОЛБЦ (доступна только в Excel 365)
_xlfn.TOROW ПОСТРОК (доступна только в Excel 365)

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

Как же появляется это самое _xlfn.ИМЯ_ФУНКЦИИ и откуда Excel вообще понимает, какой именно функции нет и что писать после _xlfn, если в самом текущем Excel такой функции нет? Здесь все достаточно просто — _xlfn.ИМЯ_ФУНКЦИИ создается в тот момент, когда на лист записывается функция, у которой могут быть проблемы с вычислением в более старых версиях. Т.е. создается это в самой исходной книге и еще в той версии Excel, которая эту функцию успешно может вычислить. А записывается в диспетчер имен, как новая именованная формула(вкладка Формулы (Formulas) —Диспетчер имен (Name Manager) ). При этом имена эти создаются скрытыми – т.е. просто вызвав диспетчер имен мы эти все _xlfn не увидим. И удалить эти имена тоже нельзя, даже если до них добраться.

Как использовать функцию IFS в Excel 2016

Функция ЕСЛИ в Excel – это логическая функция, представленная в Excel 2016. Эта функция заменяет вложенную функцию ЕСЛИ и намного проще в использовании. Функция IFS проверяет одно или несколько условий и возвращает значение, которое соответствует первому условию ИСТИНА.

Рецепт приготовления

= IFS (логический_тест1; Значение1 [logical_test2, Value2] ., [logical_test127, Value127])

  1. Logical_test1 сначала проверяет логическое условие. Это обязательный аргумент и условие, используемое Excel для оценки ИСТИНА или ЛОЖЬ.
  2. Значение1 – это результат, когда логический_тест1 – ИСТИНА. При необходимости вы можете оставить его пустым.

Остальные аргументы logical_test и Value необязательны. Функция позволяет пользователям использовать аргументы logical_test 127.

Как использовать функцию IFS в Excel

Это встроенная функция, которую можно использовать как функцию на листе Excel. Возьмем пример:

Предположим, мы хотим классифицировать точки A, B, C, D, E и F в соответствии с полученными баллами учащихся, мы используем функцию IFS следующим образом:

= IFS (A2> 80, «A», A2> 70, «B», A2> 60, «C», A2> 50, «D», A2> 40, «E», A2> 30, «F» )

Изображение 1: Как использовать функцию IFS в Excel 2016

В частности, в этом наборе функций, если A2 больше 80, возвращается точка A, если A2 больше 70, результат возвращается как B и так далее до точки F.

Используя эту формулу, мы получим следующий результат:

Изображение 2: как использовать функцию IFS в Excel 2016

Пример функции IFS в Excel

Чтобы понять, как использовать эту функцию, давайте рассмотрим несколько примеров:

Пример 1 – Использование IFS с ELSE

Предположим, есть список товаров, и вам нужно разделить их на группы тарелок: овощи, фрукты, зеленые овощи и напитки. При использовании функции IFS у вас будет следующая формула:

= IFS (A2 = «Яблоко», «Фрукты», A2 = «Банан», «Фрукты», A2 = «Шпинат», «Зеленые овощи», A2 = «кофе», «Напиток», A2 = «капуста», «Зеленый овощ», A2 = «перец», «Овощ»)

Изображение 3: Как использовать функцию IFS в Excel 2016

После этого мы получим следующий результат:

Изображение 4: Как использовать функцию IFS в Excel 2016

В этом примере мы установили логическое условие в функции IFS. Когда логическое условие оценивается как ИСТИНА, будет возвращено соответствующее значение. Однако, если логическое условие, оцененное как ИСТИНА, отсутствует, функция IFS выдаст ошибку # N / A. Как в этом примере – это перец в ячейке B8.

Чтобы предотвратить появление ошибки # N / A, мы можем использовать функцию ELSE. Поэтому мы можем установить последнее логическое условие в формуле как ИСТИНА, а затем установить возвращаемое значение.

Изображение 5: Как использовать функцию IFS в Excel 2016

Здесь мы добавили ИСТИНА, «Разное», чтобы гарантировать, что Excel вернет значение «Разное» в случае, если никакое предыдущее логическое условие в функции IFS не оценивается как ИСТИНА.

Теперь результат будет таким:

Изображение 6: Как использовать функцию IFS в Excel 2016

Пример 2 – IFS против вложенной функции IF

Перед функциями IFS мы часто используем вложенные функции IF. Давайте посмотрим, насколько функция IFS более эффективна, чем вложенная функция IF. Предположим, магазин предоставляет покупателям скидку на основе их общего счета за покупку. Таким образом, клиенты получат скидку 5% на счета от 100 до 500 долларов США, скидку 10% на счета от 500 до 750 долларов США, скидку 20% на общую сумму от 750 до 1000 долларов США и скидку 30%. когда покупатели покупают более 1000 долларов США.

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

Изображение 7 из Как использовать функцию IFS в Excel 2016

Теперь посмотрим, что произойдет, если IFS использует функцию:

Изображение 8: Как использовать функцию IFS в Excel 2016

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

Некоторые ошибки возникают при использовании функции IFS

1. Ошибка № N / A возникает, когда в функции IFS не найдено условия ИСТИНА.

2. # ЗНАЧЕНИЕ! Ошибка возникает, когда аргумент логический_тест преобразуется в значение, отличное от ИСТИНА или ЛОЖЬ.

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *