Иногда, при работе с "чужими" файлами(т.е. с теми, которые были созданы или отредактированы на другом ПК), в формулах можно встретить формулы, которые начинаются на =_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) так просто заменить не получится - там уже необходимо применять достаточно серьезные связки функций, да еще и заранее определять минимально необходимое количество ячеек для вывода результата и обрабатывать ошибки "лишних" строк.
Дмитрий, большое спасибо, что уделили внимание данной теме и рассказали, откуда вообще появляется этот префикс _xlfn.
А не могли бы Вы дополнить статью, также пояснив, что делать, если такие комбинации символов (_xlfn.Y, _xlfn.Yf и т.п.) встречаются не в формуле на листе, а как раз-таки в скрытых именах в виде "конфликта имен". Например, при попытке изменить стиль ссылок R1C1/A1 возникает конфликт имен, с требованием изменить имя. То есть, получается, что откуда-то появляются еще и дубликаты тех имен, которые были созданы "...в тот момент, когда на лист записывается функция, у которой могут быть проблемы с вычислением в более старых версиях", а такие функции, действительно, есть и были.
Здесь особо и пояснять нечего. Такие имена появляются, если в режиме стиля ссылок R1C1 были созданы имена, которые совпадают с адресами ячеек стиля А1. Т.е. имя "Y" совпадает с именем столбца Y, что приводит к конфликту, если переключиться на стиль ссылок А1. Но, т.к. Excel не проверяет, используется ли имя на листе или еще где-то в формулах - он просто создает такую вот "заглушку" в именах.
А как найти источник этой проблемы (все эти имена скрытые)? Их нужно или можно переименовывать при запуске системного диалога "Конфликт имен"? Хотелось бы конечно просто удалить их, но где они...
Sub All_Names_Visible()
Dim objName AsObject, wsSh AsObjectForEach objName In ActiveWorkbook.Names
objName.Visible = TrueNext objName
ForEach wsSh In Sheets
ForEach objName In wsSh.Names
objName.Visible = TrueNext objName
Next wsSh
EndSub
Sub All_Names_Visible()
Dim objName As Object, wsSh As Object
For Each objName In ActiveWorkbook.Names
objName.Visible = True
Next objName
For Each wsSh In Sheets
For Each objName In wsSh.Names
objName.Visible = True
Next objName
Next wsSh
End Sub
Он сделает все имена видимыми. А там уже можно и решить - удалять их или нет.
Дмитрий, большое спасибо, что уделили внимание данной теме и рассказали, откуда вообще появляется этот префикс _xlfn.
А не могли бы Вы дополнить статью, также пояснив, что делать, если такие комбинации символов (_xlfn.Y, _xlfn.Yf и т.п.) встречаются не в формуле на листе, а как раз-таки в скрытых именах в виде "конфликта имен". Например, при попытке изменить стиль ссылок R1C1/A1 возникает конфликт имен, с требованием изменить имя. То есть, получается, что откуда-то появляются еще и дубликаты тех имен, которые были созданы "...в тот момент, когда на лист записывается функция, у которой могут быть проблемы с вычислением в более старых версиях", а такие функции, действительно, есть и были.
Здесь особо и пояснять нечего. Такие имена появляются, если в режиме стиля ссылок R1C1 были созданы имена, которые совпадают с адресами ячеек стиля А1. Т.е. имя "Y" совпадает с именем столбца Y, что приводит к конфликту, если переключиться на стиль ссылок А1. Но, т.к. Excel не проверяет, используется ли имя на листе или еще где-то в формулах - он просто создает такую вот "заглушку" в именах.
А как найти источник этой проблемы (все эти имена скрытые)? Их нужно или можно переименовывать при запуске системного диалога "Конфликт имен"? Хотелось бы конечно просто удалить их, но где они...
Посмотреть имена можно только через макросы. В статьеКак уменьшить размер файла приводил такой код:
Он сделает все имена видимыми. А там уже можно и решить - удалять их или нет.
Как просто и понятно! Наконец-то избавилась от этого префиксного хлама. Спасибо большое.