总和n最大的数字EPPlus(Sum n largest numbers EPPlus)

我试图总结一个范围的n个最大数字。

我的公式是： SUM(LARGE(A10:A15, {1,2}))

但是我得到了一个#VALUE结果。

如果我调试公式执行到一个日志文件，我得到了：

Worksheet: Sheet 1 Address: A9 OfficeOpenXml.FormulaParsing.Exceptions.ExcelErrorValueException: #VALUE! em OfficeOpenXml.FormulaParsing.Excel.Functions.IntArgumentParser.Parse(Object obj) em OfficeOpenXml.FormulaParsing.Excel.Functions.ExcelFunction.ArgToInt(IEnumerable`1 arguments, Int32 index) em OfficeOpenXml.FormulaParsing.Excel.Functions.Math.Large.Execute(IEnumerable`1 arguments, ParsingContext context) em OfficeOpenXml.FormulaParsing.ExpressionGraph.FunctionCompilers.DefaultCompiler.Compile(IEnumerable`1 children, ParsingContext context) em OfficeOpenXml.FormulaParsing.ExpressionGraph.FunctionExpression.Compile()

看起来大函数不接受数组作为第二个参数。

我如何获得EPPlus中n个最大值公式的总和？

I'm trying to sum the n largest numbers of a range.

My formula is: SUM(LARGE(A10:A15, {1,2}))

But I'm getting a #VALUE as result.

If I debug the formula execution to a log file I got that:

Worksheet: Sheet 1 Address: A9 OfficeOpenXml.FormulaParsing.Exceptions.ExcelErrorValueException: #VALUE! em OfficeOpenXml.FormulaParsing.Excel.Functions.IntArgumentParser.Parse(Object obj) em OfficeOpenXml.FormulaParsing.Excel.Functions.ExcelFunction.ArgToInt(IEnumerable`1 arguments, Int32 index) em OfficeOpenXml.FormulaParsing.Excel.Functions.Math.Large.Execute(IEnumerable`1 arguments, ParsingContext context) em OfficeOpenXml.FormulaParsing.ExpressionGraph.FunctionCompilers.DefaultCompiler.Compile(IEnumerable`1 children, ParsingContext context) em OfficeOpenXml.FormulaParsing.ExpressionGraph.FunctionExpression.Compile()

Looks like the Large Function doesn't accept an array as a second argument.

How can I get the sum of n largest values formula in EPPlus?

最满意答案

我有Excel 2016和EPPlus 4.1.1.0，我测试了你的工作，并且工作完美。

我的猜测是你的单元格“A10：A15”保存格式不正确的值（也许是字符串），或者你使用的是旧版本的EPPlus / Excel。

请通过在另一个保存数字值的新列上尝试公式来检查您的数据，并尝试更新您的EPPLUS版本。

我使用的代码：

using (ExcelPackage pkg = new ExcelPackage(new FileInfo(@"D:\testSheet.xlsx"))) { pkg.Workbook.Worksheets.Add("sheet"); pkg.Workbook.Worksheets.ElementAt(0).Cells["A10:A15"].Value = 2; pkg.Workbook.Worksheets.ElementAt(0).Cells["C5"].Formula = "SUM(LARGE(A10:A15, {1,2}))"; pkg.Save(); }

结果：

Excel使用EPPLUS的大功能

Looks like this kind of formula only works when save the excel to file. In my case I don't want a file, just the calculated value.

The solution was to extend de ExcelFunction class and create my own implementation:

public class SumLargest: ExcelFunction { public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context) { ValidateArguments(arguments, 2); //n largest values var n = ArgToInt(arguments, 1); var cells = ArgsToDoubleEnumerable(arguments, context); var values = cells.ToList(); values.RemoveAt(values.Count() - 1); var result = values .OrderByDescending(x => x) .Take(n) .Sum(); return CreateResult(result, DataType.Decimal); } }

Then I added it to my package:

using (ExcelPackage excelPackage = new ExcelPackage()) { excelPackage.Workbook.FormulaParserManager.AddOrReplaceFunction("SUMLARGEST", new SumLargest()); .... }

And I can use it in a much better way then the original formula:

Cells["C5"].Formula = "SUMLARGEST(A10:A15, 2)"

Reference: https://github.com/JanKallman/EPPlus/wiki/Implementing-missing-or-new-Excel-functions

更多推荐