Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий
Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.
Функция ЕСЛИ в Excel
Функция имеет следующий синтаксис.
ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])
лог_выражение – это проверяемое условие. Например, A2<100. Если значение в ячейке A2 действительно меньше 100, то в памяти эксель формируется ответ ИСТИНА и функция возвращает то, что указано в следующем поле. Если это не так, в памяти формируется ответ ЛОЖЬ и возвращается значение из последнего поля.
значение_если_истина – значение или формула, которое возвращается при наступлении указанного в первом параметре события.
значение_если_ложь – это альтернативное значение или формула, которая возвращается при невыполнении условия. Данное поле не обязательно заполнять. В этом случае при наступлении альтернативного события функция вернет значение ЛОЖЬ.
Очень простой пример. Нужно проверить, превышают ли продажи отдельных товаров 30 шт. или нет. Если превышают, то формула должна вернуть «Ок», в противном случае – «Удалить». Ниже показан расчет с результатом.
Продажи первого товара равны 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 ЕСЛИМН.
Как видно, запись формулы выглядит гораздо проще и понятнее.
Стоит обратить внимание на следующее. Условия по-прежнему перечисляем в правильном порядке, чтобы не произошло ненужного перекрытия диапазонов. Последнее альтернативное условие, в отличие от обычной ЕСЛИ, также должно быть обязательно указано. В ЕСЛИ задается только альтернативное значение, которое наступает, если не выполняется ни одно из перечисленных условий. Здесь же нужно указать само условие, которое в нашем случае было бы B2>=1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.
Теперь вы знаете, как пользоваться функцией ЕСЛИ в Excel, а также ее более современным вариантом для множества условий ЕСЛИМН.
Функция xlfn ifs excel чем заменить
I am using Excel 2016, since few days, I am not able to use IFS formulae in my excel sheet. the formulae is now replaced with _xlfn.IFS and things are not working fine as expected. I made sure that my excel version is up to date. I tried performing repair as well. Please let me know how to fix this issue.
- Изменен тип Emi Zhang Microsoft contingent staff 20 августа 2018 г. 9:38
Все ответы
What’s version of Excel 2016 do you use? IFS function was introduced in "Excel 2016". But really the Office 365 subscription service, not standalone Excel 2016.
Please go to File- Account and make sure you’re using Office 365 subscription version for Excel:
So if you open the Excel file in a previous version (including standalone Excel 2016), Excel replaces the function name with _xlfn.IFS. That means Excel recognizes that it is an Excel function, but not a function that is supported by the current version of Excel.
If you want to fix this problem in Excel, you need to use If function to recreate a new formula.
Hope it’s helpful.
Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.
Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.
19 советов по работе с вложенными функциями ЕСЛИ
Функция ЕСЛИ (IF) является одной из наиболее часто используемых функций в Excel. ЕСЛИ — простая функция, и люди любят ее, потому что она дает им возможность заставить Excel реагировать, когда информация вводится в электронную таблицу. С ЕСЛИ, вы можете оживить вашу таблицу.
Но использование одной функции ЕСЛИ часто приводит к использованию второй, и как только вы объединяете более пары ЕСЛИ, ваши формулы могут начать выглядеть как маленькие Франкенштейны ��
Являются ли вложенные ЕСЛИ опасными? Всегда ли они необходимы? Какие есть альтернативы?
Читайте дальше, чтобы узнать ответы на эти вопросы и многое другое …
1. Базовый ЕСЛИ
Прежде чем говорить о вложенном ЕСЛИ, давайте быстро рассмотрим базовую структуру:
= ЕСЛИ (лог_выражение; [значение_если_истина];[значение_если_ложь])
Функция ЕСЛИ запускает тест и выполняет различные действия в зависимости от того, является ли результат истинным или ложным.
Обратите внимание на квадратные скобки … это означает, что аргументы необязательны. Однако вы должны указать либо значение ИСТИНА, либо значение ЛОЖЬ.
Чтобы проиллюстрировать это, мы используем ЕСЛИ, чтобы проверить результаты и вернуть «Зачтено» для баллов не менее 65:
Базовая функция ЕСЛИ — вернуть «Зачтено» для баллов не менее 65
Ячейка D4 в примере содержит эту формулу:
Что можно прочитать так: если количество баллов в ячейке C4 составляет не менее 65, вернуть «Зачтено».
Однако обратите внимание, что если оценка меньше 65, ЕСЛИ возвращает ЛОЖЬ, так как мы не указали «значение_если_ложь». Чтобы отобразить «Не зачтено» для непроходных оценок, мы можем добавить «Не зачтено» в качестве ложного аргумента следующим образом:
Базовая функция ЕСЛИ — с добавленным значением_если_ложь
2. Что значит вложение
Вложенность означает объединение формул, одна внутри другой, так что одна формула обрабатывает результат другой. Например, вот формула, в которой функция СЕГОДНЯ (TODAY) вложена в функцию МЕСЯЦ (MONTH):
Функция СЕГОДНЯ (TODAY) возвращает текущую дату внутри функции МЕСЯЦ (MONTH). Функция МЕСЯЦ (MONTH) берет эту дату и возвращает текущий месяц. Даже в формулах средней сложности часто используются вложения, поэтому вы увидите их в более сложных формулах.
3. Простой вложенный ЕСЛИ (IF)
Вложенный ЕСЛИ — это всего лишь два оператора ЕСЛИ в формуле, где один оператор ЕСЛИ появляется внутри другого.
Чтобы проиллюстрировать это, ниже я расширил оригинальную формулу «Зачтено/Не зачтено», приведенную выше, для обработки «пустых» результатов, добавив функцию еще одну функцию ЕСЛИ:
Простой вложенный ЕСЛИ
Внешний ЕСЛИ запускается первым и проверяет, является ли ячейка C4 пустой. Если это так, внешний ЕСЛИ возвращает «Неявка», а внутренний ЕСЛИ никогда не запускается.
Если ячейка не пуста, внешний ЕСЛИ возвращает ЛОЖЬ, и запускается вторая функция ЕСЛИ.
4. Вложенный ЕСЛИ (IF) для шкал
Вам часто будут встречаться вложенные ЕСЛИ, настроенные для обработки «шкал» … например, для назначения оценок, стоимости доставки, налоговых ставок или других значений, которые изменяются в шкале с числовым вводом. Пока в шкале не слишком много уровней, вложенные ЕСЛИ работают нормально. Но нужно быть внимательными, иначе формула может стать не читабельной.
Хитрость заключается в том, чтобы выбрать направление (от высокого к низкому или от низкого к высокому), а затем соответствующим образом структурировать условия. Например, чтобы присвоить оценки в порядке «от низкого до высокого», мы можем представить решение, отраженное в следующей таблице. Обратите внимание, что нет условия для «Отлично», потому что, как только мы выполним все остальные условия, мы знаем, что баллов должно быть больше 90, и, следовательно, «Отлично».
| Баллы | Оценка | Условие |
| 0 — 63 | Неуд. | < 64 |
| 64 — 72 | Удовл. | < 73 |
| 73 — 89 | Хорошо | < 90 |
| 90 — 100 | Отлично |
С четко понятными условиями мы можем ввести первый оператор ЕСЛИ:
Мы позаботились о «Неуд.». Теперь, чтобы обработать «Удовл.», нам нужно добавить еще одно условие:
Обратите внимание, что я просто добавил еще один ЕСЛИ в первый для «ложного» результата. Чтобы расширить формулу для обработки оценки «Хорошо», мы повторяем процесс:
Мы обработали все оценки и дошли до последнего уровня «Отлично». Вместо добавления еще одного ЕСЛИ, просто добавьте итоговую оценку для ЛОЖЬ.
Вот последняя вложенная формула ЕСЛИ в действии:
Завершенный вложенный пример ЕСЛИ
для расчета оценок
5. Логика вложенных ЕСЛИ
Многие формулы решаются изнутри, потому что «внутренние» функции или выражения должны решаться в первую очередь, чтобы остальная часть формулы работала.
Вложенные ЕСЛИ имеют свою логику, поскольку «внешние» ЕСЛИ действуют как ворота к «внутренним» ЕСЛИ. Это означает, что результаты внешних ЕСЛИ определяют, работают ли внутренние ЕСЛИ. Диаграмма ниже визуализирует логический ход формулы расчета оценок выше.
6. Используйте функцию «Вычислить формулу»
В Windows вы можете использовать функцию «Вычислить формулу», чтобы шаг за шагом посмотреть, как Excel решает ваши формулы. Это отличный способ «увидеть» логический поток более сложных формул и устранить неполадки, если что-то не работает. Кнопку «Вычислить формулу» можно найти на ленте на вкладке Формулы.
Функция Вычислить формулу в ленте
На экране ниже показано «окно Вычисление формулы», открытое и готовое к работе. Каждый раз, когда вы нажимаете кнопку «Вычислить», вычисляется «следующий шаг» в формуле.
Окно Вычисление формулы
К сожалению, версия Excel для Mac не содержит этой функции, но вы можете использовать прием, описанный ниже.
7. Используйте F9, чтобы определить результаты проверки
Когда вы выбираете выражение в строке формул и нажимаете клавишу F9, Excel решает только выбранную часть. Это крутой способ проверить, что на самом деле делает формула. В приведенном ниже примере я использую окна с подсказками для выбора различных частей формулы. Затем нажимаю F9, чтобы увидеть, результат вычисления этой части формулы:
Проверка вложенного ЕСЛИ
Результат проверки с клавишей F9
Используйте Ctrl + Z (Command + Z на Mac), чтобы отменить F9. Вы также можете нажать Esc, чтобы выйти из редактора формул без каких-либо изменений.
8. Помни об ограничениях
В Excel есть ограничения на то, насколько глубоко вы можете вкладывать функции ЕСЛИ. До Excel 2007 Excel допускал до 7 уровней вложенных ЕСЛИ. Excel после 2007 поддерживает до 64 уровней.
Однако то, что вы можете вкладывать много ЕСЛИ, не означает, что это нужно делать. Каждый дополнительный уровень, который вы добавляете, усложняет понимание формулы и устранение неполадок. Если вы работаете с вложенным ЕСЛИ глубиной более чем на несколько уровней, вам стоит рассмотреть альтернативные варианты, приведенные ниже.
9. Расставляй круглые скобки как профессионал
Одной из проблем с вложенными ЕСЛИ является сопоставление или «балансировка» скобок. Если круглые скобки стоят не там, где нужно или их меньше, чем требует формула, результат вы не получите. К счастью, Excel предоставляет несколько инструментов, которые помогут вам убедиться, что круглые скобки «сбалансированы» при редактировании формул.
Во-первых, если у вас несколько наборов скобок, круглые скобки имеют цветовую кодировку, поэтому открывающие скобки соответствуют закрывающим скобкам. Эти цвета нелегко рассмотреть, но при желании — можно:
Цветные скобки
Во-вторых, когда вы закроете круглые скобки, Excel выделит соответствующую пару. Вы также можете щелкнуть в формуле и использовать клавишу со стрелкой для перемещения по скобкам, и Excel будет выделять обе скобки, когда есть совпадающая пара. Если нет совпадений, вы не увидите жирного шрифта.
Пара скобок выделена жирным
К сожалению, выделение шрифтом — это функция только для Windows. Если вы используете Excel на Mac для редактирования сложных формул, иногда имеет смысл скопировать и вставить формулу в хороший текстовый редактор, чтобы получить лучшие инструменты для сопоставления скобок. Вы можете вставить формулу обратно в Excel после того, как вы все исправите.
10. Используйте окно подсказки для навигации и выбора
Когда дело доходит до навигации и редактирования вложенных ЕСЛИ, окно подсказки — ваш лучший друг. С его помощью вы можете перемещаться и точно выбирать все аргументы во вложенном ЕСЛИ:
Выберите аргументы формулы с помощью подсказки на экране
11. Будьте осторожны с текстом и цифрами
При работе с функцией ЕСЛИ, убедитесь, что вы правильно сопоставляете цифры и текст. Я часто вижу вот такие формулы ЕСЛИ:
Является ли результат теста в А1 действительно текстом, а не числом? Нет? Тогда не используйте кавычки с числом. В противном случае логический тест вернет ЛОЖЬ, даже если значение является проходным баллом, потому что «100» не совпадает с 100. Если тестовый балл является числовым, используйте вот такую формулу:
12. Добавляйте разрывы строк, чтобы облегчить чтение вложенных ЕСЛИ
Когда вы работаете с формулой, которая содержит много уровней вложенных ЕСЛИ, довольно сложно уследить за правильностью формулы. Поскольку Excel не беспокоят разрывы строк в формулах, вы можете значительно улучшить читаемость вложенных ЕСЛИ, добавив их.
Например, на приведенном ниже экране показан вложенный ЕСЛИ, который рассчитывает комиссионную ставку на основе суммы продажи. Здесь вы можете увидеть типичную вложенную ЕСЛИ-структуру, которую трудно расшифровать:
Вложенные ЕСЛИ без разрывов строк трудно читать
Однако, если я добавляю разрывы строк перед каждым «значением_если_ ложь», логика формулы легко читается. Кроме того, формулу легче редактировать:
Разрывы строк облегчают чтение вложенных ЕСЛИ
Вы можете добавить разрывы строк в Windows с помощью Alt + Enter, на Mac — Control + Option + Return.
13. Уменьшите количество ЕСЛИ с И и ИЛИ
Вложенные ЕСЛИ — мощный инструмент, но формулы быстро становятся громоздкими, когда вы добавляете больше уровней. Один из способов избежать большего количества уровней — использовать ЕСЛИ в сочетании с функциями И (AND) и ИЛИ (OR). Эти функции возвращают простой результат ИСТИНА / ЛОЖЬ, который отлично работает внутри ЕСЛИ, поэтому вы можете использовать их для расширения логики одного ЕСЛИ.
Например, в приведенной ниже задаче мы хотим поставить «х» в столбце D, чтобы отметить строки, где цвет «красный», а размер «маленький».
ЕСЛИ с функцией И проще, чем два вложенных ЕСЛИ
Мы могли бы написать формулу с двумя вложенными ЕСЛИ, вот так:
Однако, заменив одну проверку на функцию И, мы можем упростить формулу:
Таким же образом, мы можем легко расширить эту формулу с помощью функции ИЛИ, чтобы проверить наличие красного ИЛИ синего И маленького:
Все то же самое можно сделать с помощью вложенных ЕСЛИ, но формула быстро станет сложной.
14. Замените вложенные ЕСЛИ на ВПР
Когда вложенный ЕСЛИ просто присваивает значения на основе одного значения, его можно легко заменить функцией ВПР (VLOOKUP). Например, этот вложенный ЕСЛИ присваивает номера пяти различным цветам:
= ЕСЛИ (F2 = «красный»; 100; ЕСЛИ (F2 = «синий»; 200; ЕСЛИ (F2 = «зеленый»; 300; ЕСЛИ (F2 = «оранжевый»; 400;500))))
Мы можем легко заменить все ЕСЛИ одним ВПР:
Вложенный ЕСЛИ против ВПР
Хотя в приведенной выше формуле используется точное соответствие, вы можете легко использовать ВПР для приблизительного поиска.
15. Выберите ВЫБОР
Функция ВЫБОР (CHOOSE) может предоставить элегантное решение, когда вам необходимо отобразить простые последовательные числа (1,2,3 и т.д.) для произвольных значений.
В приведенном ниже примере ВЫБОР (CHOOSE) используется для создания пользовательских сокращений дней недели:
Функция ВЫБОР
Конечно, вы можете использовать длинный и сложный вложенный ЕСЛИ, чтобы сделать то же самое, но, пожалуйста, не надо ��
16. Используйте ЕСЛИМН вместо вложенных ЕСЛИ
Если вы используете Excel 2016, у Office 365 есть новая функция, которую вы можете использовать вместо вложенных ЕСЛИ: функция ЕСЛИМН (IFS). Функция ЕСЛИМН (IFS) предоставляет специальную структуру для оценки нескольких условий без вложенности
Перепишем формулу из примера про оценки с использованием ЕСЛИМН:
Обратите внимание, в формуле всего одна пара скобок!
Что происходит, когда вы открываете электронную таблицу, которая использует функцию ЕСЛИМН (IFS) в более старой версии Excel? В Excel 2013 и 2010 (и я верю в Excel 2007, но не могу проверить) вы увидите «_xlfn» в ячейке. Ранее вычисленное значение все еще будет там, но если формула пересчитается, вы увидите ошибку #ИМЯ?.
17. Используйте МАКС
Иногда вы можете использовать МАКС (MAX) или МИН (MIN) очень интересным способом, избегая оператора ЕСЛИ (IF). Предположим, что у вас есть расчет, который должен привести к положительному числу или нулю. Другими словами, если вычисление возвращает отрицательное число, вы просто хотите показать ноль.
Функция МАКС дает вам способ сделать это без ЕСЛИ:
Этот метод возвращает результат вычисления, если положительный, и ноль в противном случае.
Я люблю эту конструкцию, потому что она очень проста.
18. Перехват ошибок с помощью ЕСЛИОШИБКА
Классическим использованием ЕСЛИ является перехват ошибок и предоставление другого результата при возникновении ошибки, например:
= ЕСЛИ (ЕОШИБКА (формула); значение_если_ошибка; формула)
Это уродливо и неудобно, так как одна и та же формула вводится дважды, и Excel должен вычислять одно и то же несколько раз, если ошибки нет.
В Excel 2007 была введена функция ЕСЛИОШИБКА (IFERROR), которая позволяет более элегантно отлавливать ошибки:
= ЕСЛИОШИБКА (формула; значение_если_ошибка)
Теперь, когда формула выдает ошибку, ЕСЛИОШИБКА просто возвращает указанное вами значение.
19. Используйте «логическую» логику
Вы также можете иногда избегать вложенных ЕСЛИ, используя так называемую «логическую логику». Слово логическое относится к значениям ИСТИНА / ЛОЖЬ. Хотя Excel отображает слова ИСТИНА и ЛОЖЬ в ячейках, внутренне Excel воспринимает ИСТИНА как 1, а ЛОЖЬ как ноль.
Вы можете использовать этот факт для написания умных и очень быстрых формул. Например, в приведенном выше примере с ВПР (VLOOKUP) у нас есть вложенная формула ЕСЛИ, которая выглядит следующим образом:
= ЕСЛИ (F2 = «красный»; 100; ЕСЛИ (F2 = «синий»; 200; ЕСЛИ (F2 = «зеленый»; 300; ЕСЛИ (F2 = «оранжевый»; 400;500))))
Используя логическую логику, вы можете переписать формулу следующим образом:
Каждое выражение выполняет тест, а затем умножает результат теста на «значение, если оно истинно». Поскольку тесты возвращают значение ИСТИНА или ЛОЖЬ (1 или 0), результаты ЛОЖЬ фактически отменяют формулу.
Для числовых результатов логическая логика проста и чрезвычайно быстра, так как нет разветвлений. С другой стороны, логическая логика может сбивать с толку людей, которые не привыкли к ней. Тем не менее, это отличная техника, чтобы знать о ней.
Когда вам нужен вложенный ЕСЛИ?
Со всеми этими опциями для избежания вложенных ЕСЛИ, вы можете задаться вопросом: «А когда же его использовать?»
Я думаю, что вложенные ЕСЛИ имеют смысл, когда вам нужно оценить несколько различных входных данных для принятия решения.
Например, предположим, что вы хотите определить статус счета-фактуры «Оплачено», «Ожидание», «Просрочено» и т.д. Для этого необходимо посмотреть дату счета-фактуры и остаток задолженности:
Расчет статуса счета-фактуры с помощью вложенного ЕСЛИ
Что такое _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 не увидим. И удалить эти имена тоже нельзя, даже если до них добраться.