Xlfn ceiling math выдает ошибку

With the following MWE:

ExcelPackage package = new ExcelPackage(@"C:Usersmpaulustest.xlsx");
ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Sheet");
sheet.Cells[1, 1].Formula = "CEILING.MATH(1.6)";
package.Save();

I get a #NAME() error when opening the output xlsx. There is no EPPlus exception for unknown function.

When I look at the output XML in the sheetData element, everything looks fine:

<sheetData><row r="1"><c r="A1" s="0"><f>CEILING.MATH(1.6)</f></c></row></sheetData>

If I make a spreadsheet within Excel itself and look at that, it looks like:

<sheetData><row r="1" spans="1:1" x14ac:dyDescent="0.35"><c r="A1"><f>_xlfn.CEILING.MATH(1.6)</f><v>3</v></c></row></sheetData>

When I looked through the code base, it looks like the _xlfn is removed on purpose in a few places.

src/EPPlus/FormulaParsing/ExpressionGraph/FunctionExpression.cs
62:                // older versions of Excel (pre 2007) adds "_xlfn." in front of some function names for compatibility reasons.
64:                if (funcName.StartsWith("_xlfn.", StringComparison.OrdinalIgnoreCase)) funcName = funcName.Replace("_xlfn.", string.Empty);

src/EPPlus/DataValidation/Formulas/ExcelDataValidationFormula.cs
102:            formula = formula.Replace("_xlfn.", string.Empty).Replace("_xlws.", string.Empty);

I did confirm if I manually add that _xlfn back in, there is no error, meaning:

sheet.Cells[1, 1].Formula = "_xlfn.CEILING.MATH(1.6)";

did work.

Is this a case where that prefix is required?

I am using EPPlus version 6.1.1.
Excel version is 2210 (Build 15726.20202)

Хитрости »

30 Ноябрь 2022              872 просмотров

Иногда, при работе с «чужими» файлами(т.е. с теми, которые были созданы или отредактированы на другом ПК), в формулах можно встретить формулы, которые начинаются на =_xlfn.(нечто вроде: =_xlfn.MAXIFS, _xlfn.IFERROR и т.п.)

При первом открытии файла такие функции показывают последний успешно вычисленный результат и при вычислении не обновляются, а если попытаться их пересчитать вручную, то в результате можно получить ошибку #ИМЯ!(#NAME!).

Что это и откуда? Для начала попробуем разобраться откуда берется это загадочное _xlfn. и что оно означает. Таким префиксом обозначаются функции, которые были созданы в более новых версиях Excel(чем та, в которой на данный момент открыт файл) и в текущей версии отсутствуют. По сути они читаются как =_xlfn.ИМЯ_ФУНКЦИИ. Например, =_xlfn.MAXIFS – это функция МАКСЕСЛИ(MAXIFS), _xlfn.IFERROR – ЕСЛИОШИБКА(IFERROR), _xlfn.UNIQUE – УНИК(UNIQUE) и т.д. Т.е. после самого префикса _xlfn идет как раз имя недоступной функции на английском. Если Вы увидели такой префикс в своем Excel в какой-либо формуле на листе — значит ваш Excel не поддерживает ту функцию, которая указана после префикса _xlfn.
Ниже перечень некоторых функций с их переводом на русскую локализацию и версии, начиная с которых эти функции будут работать(соответственно для версий ниже функции недоступны):

Конечно, в этой таблице перечислены далеко не все функции и их может быть гораздо больше и список будет расти, т.к. Microsoft на данный момент постоянно пополняет коллекцию функций в новых версиях.

Как же появляется это самое _xlfn.ИМЯ_ФУНКЦИИ и откуда Excel вообще понимает, какой именно функции нет и что писать после _xlfn, если в самом текущем Excel такой функции нет? Здесь все достаточно просто — _xlfn.ИМЯ_ФУНКЦИИ создается в тот момент, когда на лист записывается функция, у которой могут быть проблемы с вычислением в более старых версиях. Т.е. создается это в самой исходной книге и еще в той версии Excel, которая эту функцию успешно может вычислить. А записывается в диспетчер имен, как новая именованная формула(вкладка Формулы(Formulas) —Диспетчер имен(Name Manager)). При этом имена эти создаются скрытыми – т.е. просто вызвав диспетчер имен мы эти все _xlfn не увидим. И удалить эти имена тоже нельзя, даже если до них добраться.

Так же можно встретить и чуть иной префикс: _xlfn._xlws.SORT и _xlfn._xlws.FILTER. Это как правило относится к формулам динамических массивов. SORT – СОРТ и FILTER – ФИЛЬТР. Эти функции записываются всегда только в одну ячейку, но результат возвращают сразу в несколько. При этом количество занимаемых ячеек в итоге динамически изменяется в зависимости от того, сколько строк и столбцов передано в качестве исходных данных и сколько после обработки было возвращено функцией.

А еще есть и такие имена: _xlfn.ANCHORARRAY и _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
ссылки
статистика

Проблема возникла из-за использования функции ОКРВНИЗ.МАТ (англ. FLOOR.MATH).
Это относительно новая функция, которая есть только в Excel 2013+ (т.е. начиная с Excel 2013).

Если книгу с такой формулой открыть, например, в Excel 2010, то вместо указанной функции появится _xlfn.FLOOR.MATH.
Это означает, что данная функция новая и не поддерживается в данной версии Excel, при сохранении книги она так и сохранится, но при открытии книги в MS Excel 2013+ формула автоматически восстановится, в других же приложениях а-ля Excel формула не восстанавливается.

Для совместимости со всеми версиями предлагаю такую замену:
1. Русский вариант формулы (запятая – разделитель целой и дробной частей числа)
=ЕСЛИ(D2>G2/0,94;
 D2/0,9;
 ЦЕЛОЕ(
   G2/ВЫБОР(СУММПРОИЗВ(—(G2>{-9E+307;5000;10000;50000;100000;500000}));0,94;0,945;0,95;0,955;0,96;0,965) /ВЫБОР(СУММПРОИЗВ(—(G2>{-9E+307;10;50;100})); 0,01;0,05;0,1;1)
 ) * ВЫБОР(СУММПРОИЗВ(—(G2>{-9E+307;10;50;100})); 0,01;0,05;0,1;1)
)

2. Английский вариант формулы
=IF(D2>G2/0.94,
 D2/0.9,
 INT(
   G2/CHOOSE(SUMPRODUCT(—(G2>{-9E+307,5000,10000,50000,100000,500000})),0.94,0.945,0.95,0.955,0.96,0.965) /CHOOSE(SUMPRODUCT(—(G2>{-9E+307,10,50,100})), 0.01,0.05,0.1,1)
 ) * CHOOSE(SUMPRODUCT(—(G2>{-9E+307,10,50,100})), 0.01,0.05,0.1,1)
)

Во вложении старая и новая формулы встроены, чтобы посмотреть их в любой локализации с любым разделителем целой и дробной частей.

1. 01-21-2014, 09:59 PM

   
   

   #1

   

   
   Registered User
   

   

   ---

   Changing an CEILING.PRECISION formula to work in Excel 2007
   

   I am having an issue making an excel 2010 formula work with 2007.

   =_xlfn.CEILING.PRECISE(IF(H21>30,((H21-30)/40)+2-H28,IF(H21>4.99,2-H28,1)))

   I believe it needs to be the CEILING function but I can’t get it to work

   Any ideas?
   Cheers

   ---

2. 01-21-2014, 10:37 PM

   
   

   #2

   Re: Changing an CEILING.PRECISION formula to work in Excel 2007
   

   That function must be part of an add-in. It’s not a native Excel function.
   What are you trying to do? Maybe we can put something together for you.

   ---

3. 01-21-2014, 10:49 PM

   
   

   #3

   

   
   Registered User
   

   

   ---

   Re: Changing an CEILING.PRECISION formula to work in Excel 2007
   

   Isn’t CEILING.PRECISION a standard excel function in excel 2010 onwards?

   ---

4. 01-22-2014, 07:37 AM

   
   

   #4

   Re: Changing an CEILING.PRECISION formula to work in Excel 2007
   

   Interesting…The prefix you posted: «_xlfn.» threw me off. So, I assumed it was an add-in. However, when I typed it in a formula, Excel discarded it.
   Excel 2013 doesn’t list it in formula auto-complete, but Excel 2010 does.
   Excel 2013 can use CEILING.PRECISE, but it has been replaced by CEILING.MATH. (I wonder what Microsoft is up to)

   Anyway…Does this do the same thing as your posted formula?

   ---

5. 01-22-2014, 02:58 PM

   
   

   #5

   

   
   Registered User
   

   

   ---

   Re: Changing an CEILING.PRECISION formula to work in Excel 2007
   

   Thanks Ron,
   Yes that was what I was after.
   I missed the final argument off.
   It was one of those days.

   ---

Функция ОКРВВЕРХ.МАТ округляет число с избытком до ближайшего целого или до ближайшего кратного указанному значению точности.

Описание функции ОКРВВЕРХ.МАТ

Округляет число с избытком до ближайшего целого или до ближайшего кратного указанному значению точности.

Синтаксис

=ОКРВВЕРХ(число; [точность]; [режим])

Аргументы

Замечания

• По умолчанию точность равна +1 для положительных чисел и -1 для отрицательных.
• По умолчанию положительные числа с дробной частью округляются до ближайшего целого числа. Например, 6,3 округляется до 7.
• По умолчанию отрицательные числа с дробной частью округляются (в сторону 0) до ближайшего целого числа. Например, -6,7 округляется до -6.
• Указывая аргументы «точность» и «режим», можно изменить направление округления отрицательных чисел. Например, округление -6,3 с точностью 1 и режимом 1 округляет в отрицательную сторону до -7. Существует много сочетаний значений точности и режима, которые по-разному влияют на округление отрицательных чисел.
• Аргумент «режим» не влияет на положительные числа.
• Аргумент «точность» округляет число с избытком до ближайшего целого числа, кратного указанной точности, кроме случаев, когда округляемое число ― целое. Например, с точностью 3 число округляется в сторону увеличения до следующего целого числа, кратного 3.
• Если при делении числа на точность 2 или больше возникает остаток, результат округляется в сторону увеличения.

Пример