Иногда, при работе с "чужими" файлами(т.е. с теми, которые были созданы или отредактированы на другом ПК), в формулах можно встретить формулы, которые начинаются на =_xlfn.(нечто вроде: =_xlfn.MAXIFS, _xlfn.IFERROR и т.п.)
xlfn в Excel
При первом открытии файла такие функции показывают последний успешно вычисленный результат и при вычислении не обновляются, а если попытаться их пересчитать вручную, то в результате можно получить ошибку #ИМЯ!(#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 – ФИЛЬТР. Эти функции записываются всегда только в одну ячейку, но результат возвращают сразу в несколько. При этом количество занимаемых ячеек в итоге динамически изменяется в зависимости от того, сколько строк и столбцов передано в качестве исходных данных и сколько после обработки было возвращено функцией.

Если еще не знаете что такое динамические массивы - Динамические массивы в Excel.

А еще есть и такие имена: _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) так просто заменить не получится - там уже необходимо применять достаточно серьезные связки функций, да еще и заранее определять минимально необходимое количество ячеек для вывода результата и обрабатывать ошибки "лишних" строк.

    Так же см.:
    Динамические массивы в Excel
    Ошибка - Cant find project or library

    Loading

    5 комментариев

    1. Дмитрий, большое спасибо, что уделили внимание данной теме и рассказали, откуда вообще появляется этот префикс _xlfn.
      А не могли бы Вы дополнить статью, также пояснив, что делать, если такие комбинации символов (_xlfn.Y, _xlfn.Yf и т.п.) встречаются не в формуле на листе, а как раз-таки в скрытых именах в виде "конфликта имен". Например, при попытке изменить стиль ссылок R1C1/A1 возникает конфликт имен, с требованием изменить имя. То есть, получается, что откуда-то появляются еще и дубликаты тех имен, которые были созданы "...в тот момент, когда на лист записывается функция, у которой могут быть проблемы с вычислением в более старых версиях", а такие функции, действительно, есть и были.

      1. Здесь особо и пояснять нечего. Такие имена появляются, если в режиме стиля ссылок R1C1 были созданы имена, которые совпадают с адресами ячеек стиля А1. Т.е. имя "Y" совпадает с именем столбца Y, что приводит к конфликту, если переключиться на стиль ссылок А1. Но, т.к. Excel не проверяет, используется ли имя на листе или еще где-то в формулах - он просто создает такую вот "заглушку" в именах.

    2. А как найти источник этой проблемы (все эти имена скрытые)? Их нужно или можно переименовывать при запуске системного диалога "Конфликт имен"? Хотелось бы конечно просто удалить их, но где они...

      1. Посмотреть имена можно только через макросы. В статье Как уменьшить размер файла приводил такой код:

        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

        Он сделает все имена видимыми. А там уже можно и решить - удалять их или нет.

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

    This site uses Akismet to reduce spam. Learn how your comment data is processed.