Хитрости »
30 Ноябрь 2022
Дмитрий 4312 просмотров
Что такое _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 не увидим. И удалить эти имена тоже нельзя, даже если до них добраться.
Так же можно встретить и чуть иной префикс: _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) так просто заменить не получится - там уже необходимо применять достаточно серьезные связки функций, да еще и заранее определять минимально необходимое количество ячеек для вывода результата и обрабатывать ошибки "лишних" строк.
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика
