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

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

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще. Меньше

Проблема

Объект _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

Средством проверки совместимости обнаружены проблемы совместимости формул с более ранними версиями Microsoft Excel.

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

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

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

В этой статье

• Проблемы, которые приводят к существенной потере функциональности
• Проблемы, которые приводят к небольшой потере точности

Проблемы, которые приводят к существенной потере функциональности

На некоторых из них больше формул массива, ссылок на другие, чем поддерживается выбранным форматом файла. Некоторые из этих формул массива не будут сохранены и будут преобразованы в #VALUE! исчезнут.

Что это означает. В Excel 2007 г. и более поздних книгах массивы, ссылались на другие книги, ограничены только доступной памятью, но в Excel 97–2003 книги могут содержать не более 65 472 массивов книг, которые ссылаются на другие. Массивы книг после максимального предела будут преобразованы в и отобразить #VALUE! исчезнут.

Действия В окте проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие формулы массива, которые ссылаются на другой #VALUE! исчезнут.

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

Что это означает. В Excel 2010 и более поздних годах можно использовать формулы массива, содержащие элементы для более чем 256 столбцов и 65 536 строк. В Excel 2007 г. это превышает ограничение для элементов массива и может возвращать другие результаты.

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

Некоторые формулы содержат больше значений, ссылок и имен, чем поддерживается выбранным форматом файла. Эти формулы не будут сохранены и будут преобразованы в #VALUE! исчезнут.

Что это означает. В Excel 2007 и более поздних гг. максимальная длина содержимого формулы составляет 8 192 символа, а максимальная внутренняя длина формулы — 16 384байта. В Excel 97–2003 максимальная длина содержимого формулы составляет 1024 символа, а внутренняя — 1800 bytes. Если сочетание аргументов формулы (включая значения, ссылки и/или имена) превышает предельное Excel 97–2003, результатом формул будет #VALUE! при сохранение книги в формате более ранней Excel файла.

Действия В окне средства проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие формулы, длина которых превышает предельное значение в Excel 97–2003, и внести необходимые изменения, чтобы избежать #VALUE! исчезнут.

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

Что это означает. В Excel 2007 и более поздних годах формула может содержать до 64 уровней вложенности, а в Excel 97–2003 — всего 7.

Действия В окте проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие формулы с более чем 7 уровнями вложенности, и внести необходимые изменения, чтобы избежать #VALUE! исчезнут.

Некоторые формулы содержат функции, у которых больше аргументов, чем поддерживается выбранным форматом файла. Формулы, которые имеют более 30 аргументов на одну функцию, не будут сохранены и будут преобразованы в #VALUE! исчезнут.

Что это означает. В Excel 2007 и более поздних годах формула может содержать до 255 аргументов, но в Excel 97–2003 число аргументов в формуле не может быть больше 30.

Действия В окте проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие формулы с более чем 30 аргументами, и внести необходимые изменения, чтобы избежать #VALUE! исчезнут.

В некоторых формулах операндов больше, чем разрешено выбранным форматом файла. Эти формулы не будут сохранены и будут преобразованы в #VALUE! исчезнут.

Что это означает. В Excel 2007 г. и более поздних гг. число операндов, которые можно использовать в формулах, составляет 1024, а в Excel 97–2003 — всего 40.

Действия В окте проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие формулы с более чем 40 операндами, и внести необходимые изменения, чтобы избежать #VALUE! исчезнут.

Некоторые формулы содержат функции с большим размером аргументов, чем поддерживается выбранным форматом файла. Формулы с более чем 29 аргументами функции не будут сохранены и будут преобразованы в #VALUE! исчезнут.

Что это означает. В Excel 2007 и более поздних User-Defined функция User-Defined(UDF), создаемая с помощью Visual Basic для приложений (VBA), может содержать до 60 аргументов, но в Excel 97–2003 число аргументов в UDF ограничено VBA только 29.

Действия В окте проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие функции, которые используют более 29 аргументов, и внести необходимые изменения, чтобы избежать #VALUE! исчезнут. Для изменения пользовательских функций может потребоваться использовать код VBA.

Одна или несколько функций в этой книге недоступны в более ранних версиях Excel. При пересчете в более ранних версиях эти функции возвращают #NAME? вместо текущих результатов.

Что это означает. Excel 2007 и более поздних лет предоставляют новые и переименованные функции. Так как эти функции недоступны в Excel 97–2003, они возвращают #NAME? вместо ожидаемых результатов, если книга открыта в более ранней версии Excel.)

В некоторых случаях к формуле добавляется префикс _xlfn, например: =_xlfn.ЕСЛИОШИБКА (1;2).

Действия В средстве проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие функции, недоступные в более ранних версиях Excel, и внести необходимые изменения, чтобы избежать #NAME? исчезнут.

Для всех переименованных функций доступны функции совместимости. Для предотвращения ошибок можно использовать эти функции.

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

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

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

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

Некоторые формулы содержат ссылки на таблицы в других книгах, которые сейчас не открыты в текущем экземпляре Excel. При сохранении в формате Excel 97–2003 эти ссылки будут преобразованы в значения #ССЫЛКА!, так как их невозможно преобразовать в адреса на текущем листе.

Что это означает. В Excel 2007 и более поздних годах можно использовать структурированные ссылки, чтобы работать с данными таблицы проще и понятнее при использовании формул, ссылаясь на части таблиц или целые таблицы. В Excel 97–2003 эта возможность не поддерживается, и структурированные ссылки преобразуются в ссылки на ячейки. Однако если структурированные ссылки указывают на таблицы в других книгах, которые в данный момент не открыты, они преобразуются в значения ошибки #ССЫЛКА!.

Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие формулы со структурированными ссылками на таблицы в других книгах, и измените их для предотвращения ошибок #ССЫЛКА!.

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

Что это означает. В Excel 2010 г. и более поздних гг. правила проверки данных могут ссылаться на более чем 8192 невимех ячеек. В Excel 97–2007 этот тип правила проверки данных не поддерживается и будет не доступен.

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

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

Что это означает. В Excel 2010 г. и более поздних можно использовать правила проверки данных, которые ссылаются на значения на других таблицах. В Excel 97–2007 этот тип проверки данных не поддерживается и не отображается на этом сайте.

Однако все правила проверки данных остаются доступными в книге и применяются при повторном ее повторном Excel 2010 г. или более поздней, если они не были изменены в Excel 97–2007.

Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие правила проверки данных, в которых есть ссылки на значения на других листах, и внесите необходимые изменения на вкладке Параметры диалогового окна Проверка данных (вкладка Данные, группа Работа с данными).

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

Что это означает. В Excel 2007 и более поздних годах диапазоны консолидации данных могут содержать формулы, которые ссылаются на данные за пределами диапазона строк и столбцов выбранного формата файла. В Excel 97–2003 г. размер всего 256 столбцов на 65 536 строк. Формулы, ссылающиеся на данные в ячейках за пределами этого диапазона строк и столбцов, корректируются и могут отображаться неправильно.

Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти диапазоны консолидации данных, содержащие формулы, которые ссылаются на данные за пределами ограничения на число строк и столбцов Excel 97–2003, и внесите необходимые изменения.

Книга содержит настраиваемые описания пользовательских функций VBA. Все настраиваемые описания будут удалены.

Что это означает. В Excel 2010 г. и более поздних Visual Basic для приложений для создания функций User-Defined (UDFs) с настраиваемой описанием. Настраиваемые описания не поддерживаются в Excel 97–2007 и будут удалены.

Что необходимо сделать. Выполнять какие-либо действия не требуется, поскольку все настраиваемые описания будут удалены.

Проблемы, которые приводят к небольшой потере точности

Некоторые формулы массива в этой книге ссылаются на весь столбец. В более ранних версиях Excel эти формулы могут быть преобразованы в #NUM! при пересчете.

Что это означает. Формулы массива, ссылайтесь на весь столбец в Excel 2007 г. и более поздних #NUM! при пересчете в Excel 97–2003.

Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти формулы массива, ссылающиеся на целый столбец, и внесите необходимые изменения для предотвращения ошибок #ЧИСЛО!.

Одно или несколько определенных в данной книге имен содержат формулы, длина которых превышает максимальный размер, допустимый для выбранного формата файла (255 символов). Эти формулы будут сохранены, но будут усечены при редактировании в более ранних версиях Excel.

Что это означает. Если именованные диапазоны в формулах превышают предел в 255 знаков, поддерживаемый в Excel 97–2003, формула работает правильно, но усекается в диалоговом окне Имя и недоступна для редактирования.

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

Книга содержит формулы, которые ссылаются на другие закрытые книги. Если связанные книги закрыты, то при пересчете в более ранних версиях Excel значения этих формул будут ограничены 255 знаками.

Что это означает. Если формулы в одной книге связаны с другими книгами, которые закрыты, при пересчете в Excel 97–2003 эти формулы могут отобразить не более 255 знаков. Результаты формулы могут быть усечены.

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

Формула для проверки данных содержит более 255 символов.

Что это означает. Если формула проверки данных превышает заданный в Excel 97–2003 предел в 255 знаков, формула будет работать правильно, но будет усечена и недоступна для редактирования.

Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти ячейки, содержащие формулы проверки данных, и сократите число знаков в формулах, чтобы пользователи могли редактировать их в Excel 97–2003.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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