Иногда, при работе с "чужими" файлами(т.е. с теми, которые были созданы или отредактированы на другом ПК), в формулах можно встретить формулы, которые начинаются на =_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.FILTER
ФИЛЬТР (доступна, начиная с 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 не увидим. И удалить эти имена тоже нельзя, даже если до них добраться.
Так же можно встретить и чуть иной префикс: _xlfn._xlws.SORT и _xlfn._xlws.FILTER. Это как правило относится к формулам динамических массивов. SORT – СОРТ и FILTER – ФИЛЬТР. Эти функции записываются всегда только в одну ячейку, но результат возвращают сразу в несколько. При этом количество занимаемых ячеек в итоге динамически изменяется в зависимости от того, сколько строк и столбцов передано в качестве исходных данных и сколько после обработки было возвращено функцией.
А еще есть и такие имена: _xlfn.ANCHORARRAY и _xlfn.SINGLE. Они тоже относятся к динамическим массивам, но не являются напрямую именно функциями:
_xlfn.ANCHORARRAY - означает, что в формуле используется ссылка на динамический массив, вроде такой: =A2#, где =A2 – это ячейка, в которую записана формула динамического массива, а решетка(#) означает, что вернуть необходимо все ячейки, которые входят в динамический массив, созданный формулой в =A2#.
_xlfn.SINGLE – означает, что в формуле используется оператор неявного пересечения @. Появляется, когда в формуле указывается ссылка на диапазон ячеек, но использовать в функции надо только одну. Появился вместе с динамическими массивами для избежания неверных вычислений в новых версиях.
Можно ли исправить? Как же быть, если одна из таких функций попалась в файле? Можно ли как-то её все же сделать вычисляемой?
Для того, чтобы ответить на эти вопросы разберемся для чего это придумано. Сделано такое именование(_xlfn.ИМЯ_ФУНКЦИИ) неподдерживаемых функций для того, чтобы можно было безошибочно определить, какая именно функция не доступна и при этом не сломать всю формулу, в которой такая функция используется. Но вычислить её, увы, не получится никак. Ведь раз появилось _xlfn - функции просто нет в текущей версии, а значит вычислить именно её невозможно. Но если определить имя функции(которое записано после префикса) - можно обратиться к справке Microsoft, найти эту функцию и попробовать заменить её доступной функцией(или связкой функций), которая будет выполнять ту же задачу. Да, далеко не всегда для этого может хватить знаний, а в некоторых случаях и вовсе придется использовать VBA для восполнения функционала, т.к. какие-то функции будет невозможно воспроизвести встроенными. Но других вариантов все равно нет: либо так, либо устанавливать ту версию Excel, в которой эти функции есть.
В данной статье я, к сожалению, тоже никаких однозначных рекомендаций по замене _xlfn не дам, т.к. все зависит от конкретной задачи, которая выполняется функцией. Могу лишь привести пару примеров простой замены. Например, возьмем классическую ситуацию - есть таблица отгрузок товара:
надо определить самую минимальную, но при этом не учитывать нулевые отгрузки, т.к. в выходные отгрузка не производится, но в отчете нулевые отгрузки все же есть. В Excel 2019 и новее можно применить одну функцию: =МИНЕСЛИ(B2:B10;B2:B10;">0") =MINIFS(B2:B10,B2:B10,">0")
в более старых версиях вместо неё появится =_xlfn.MINIFS(B2:B10;B2:B10;">0") и её придется заменить такой формулой массива: =МИН(ЕСЛИ(B2:B10>0;B2:B10)) =MIN(IF(B2:B10>0,B2:B10))
Или другая задача, которая в Excel 2019 и новее легко решается функцией МАКСЕСЛИ(MAXIFS). Из таблицы ниже необходимо определить максимальную сумму по операции "приход": =МАКСЕСЛИ(A2:A10;B2:B10;"приход") =MAXIFS(A2:A10,B2:B10,"приход")
в более старых версиях придется опять же решать задачу при помощи нескольких функций формулой массива: =МАКС(ЕСЛИ(B2:B10="приход";A2:A10)) =MAX(IF(B2:B10="приход",A2:A10))
А такие функции как СОРТ(SORT) и ФИЛЬТР(FILTER) так просто заменить не получится - там уже необходимо применять достаточно серьезные связки функций, да еще и заранее определять минимально необходимое количество ячеек для вывода результата и обрабатывать ошибки "лишних" строк.
