From c57dc741a6f221f53f3c8da2f521c6ece63246c2 Mon Sep 17 00:00:00 2001 From: Winfried Donkers Date: Tue, 3 May 2016 21:19:19 +0200 Subject: tdf#97831 [part] Add Excel 2016 functions to Calc Functions MINIFS and MAXIFS Change-Id: I4bd2e8b82f8377af81f4373d0c33ac286588b8df Reviewed-on: https://gerrit.libreoffice.org/24619 Tested-by: Jenkins Reviewed-by: Eike Rathke Tested-by: Eike Rathke --- formula/source/core/resource/core_resource.src | 14 +++++ include/formula/compiler.hrc | 4 +- include/formula/opcode.hxx | 2 + sc/inc/helpids.h | 2 + sc/qa/unit/ucalc.cxx | 2 + sc/source/core/inc/interpre.hxx | 6 +- sc/source/core/tool/interpr1.cxx | 14 ++++- sc/source/core/tool/interpr4.cxx | 2 + sc/source/core/tool/interpr8.cxx | 12 ++++ sc/source/filter/excel/xlformula.cxx | 4 +- sc/source/filter/oox/formulabase.cxx | 4 +- sc/source/ui/src/scfuncs.src | 78 ++++++++++++++++++++++++++ 12 files changed, 139 insertions(+), 5 deletions(-) diff --git a/formula/source/core/resource/core_resource.src b/formula/source/core/resource/core_resource.src index acc6ba899dab..551de2fa1c74 100644 --- a/formula/source/core/resource/core_resource.src +++ b/formula/source/core/resource/core_resource.src @@ -291,6 +291,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF String SC_OPCODE_TEXTJOIN_MS { Text = "COM.MICROSOFT.TEXTJOIN" ; }; String SC_OPCODE_IFS_MS { Text = "COM.MICROSOFT.IFS" ; }; String SC_OPCODE_SWITCH_MS { Text = "COM.MICROSOFT.SWITCH" ; }; + String SC_OPCODE_MINIFS_MS { Text = "COM.MICROSOFT.MINIFS" ; }; + String SC_OPCODE_MAXIFS_MS { Text = "COM.MICROSOFT.MAXIFS" ; }; String SC_OPCODE_MAT_VALUE { Text = "MVALUE" ; }; String SC_OPCODE_MAT_DET { Text = "MDETERM" ; }; String SC_OPCODE_MAT_INV { Text = "MINVERSE" ; }; @@ -729,6 +731,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML String SC_OPCODE_TEXTJOIN_MS { Text = "_xlfn.TEXTJOIN" ; }; String SC_OPCODE_IFS_MS { Text = "_xlfn.IFS" ; }; String SC_OPCODE_SWITCH_MS { Text = "_xlfn.SWITCH" ; }; + String SC_OPCODE_MINIFS_MS { Text = "_xlfn.MINIFS" ; }; + String SC_OPCODE_MAXIFS_MS { Text = "_xlfn.MAXIFS" ; }; String SC_OPCODE_MAT_VALUE { Text = "MVALUE" ; }; String SC_OPCODE_MAT_DET { Text = "MDETERM" ; }; String SC_OPCODE_MAT_INV { Text = "MINVERSE" ; }; @@ -1167,6 +1171,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH String SC_OPCODE_TEXTJOIN_MS { Text = "TEXTJOIN" ; }; String SC_OPCODE_IFS_MS { Text = "IFS" ; }; String SC_OPCODE_SWITCH_MS { Text = "SWITCH" ; }; + String SC_OPCODE_MINIFS_MS { Text = "MINIFS" ; }; + String SC_OPCODE_MAXIFS_MS { Text = "MAXIFS" ; }; String SC_OPCODE_MAT_VALUE { Text = "MVALUE" ; }; String SC_OPCODE_MAT_DET { Text = "MDETERM" ; }; String SC_OPCODE_MAT_INV { Text = "MINVERSE" ; }; @@ -2349,6 +2355,14 @@ Resource RID_STRLIST_FUNCTION_NAMES { Text [ en-US ] = "SWITCH" ; }; + String SC_OPCODE_MINIFS_MS + { + Text [ en-US ] = "MINIFS" ; + }; + String SC_OPCODE_MAXIFS_MS + { + Text [ en-US ] = "MAXIFS" ; + }; String SC_OPCODE_MAT_VALUE { Text [ en-US ] = "MVALUE" ; diff --git a/include/formula/compiler.hrc b/include/formula/compiler.hrc index 0bb83608e28b..7a7bfe74ab94 100644 --- a/include/formula/compiler.hrc +++ b/include/formula/compiler.hrc @@ -499,7 +499,9 @@ #define SC_OPCODE_TEXTJOIN_MS 488 #define SC_OPCODE_IFS_MS 489 #define SC_OPCODE_SWITCH_MS 490 -#define SC_OPCODE_STOP_2_PAR 491 /* last function with two or more parameters' OpCode + 1 */ +#define SC_OPCODE_MINIFS_MS 491 +#define SC_OPCODE_MAXIFS_MS 492 +#define SC_OPCODE_STOP_2_PAR 493 /* last function with two or more parameters' OpCode + 1 */ #define SC_OPCODE_STOP_FUNCTION SC_OPCODE_STOP_2_PAR /* last function's OpCode + 1 */ #define SC_OPCODE_LAST_OPCODE_ID (SC_OPCODE_STOP_FUNCTION - 1) /* last OpCode */ diff --git a/include/formula/opcode.hxx b/include/formula/opcode.hxx index facaaabfa89b..30dc94ff578c 100644 --- a/include/formula/opcode.hxx +++ b/include/formula/opcode.hxx @@ -284,6 +284,8 @@ enum OpCode : sal_uInt16 ocRawSubtract = SC_OPCODE_RAWSUBTRACT, ocIfs_MS = SC_OPCODE_IFS_MS, ocSwitch_MS = SC_OPCODE_SWITCH_MS, + ocMinIfs_MS = SC_OPCODE_MINIFS_MS, + ocMaxIfs_MS = SC_OPCODE_MAXIFS_MS, // Database functions ocDBSum = SC_OPCODE_DB_SUM, ocDBCount = SC_OPCODE_DB_COUNT, diff --git a/sc/inc/helpids.h b/sc/inc/helpids.h index db24bed2e6d6..9c15904016be 100644 --- a/sc/inc/helpids.h +++ b/sc/inc/helpids.h @@ -640,5 +640,7 @@ #define HID_FUNC_TEXTJOIN_MS "SC_HID_FUNC_TEXTJOIN_MS" #define HID_FUNC_IFS_MS "SC_HID_FUNC_IFS_MS" #define HID_FUNC_SWITCH_MS "SC_HID_FUNC_SWITCH_MS" +#define HID_FUNC_MINIFS_MS "SC_HID_FUNC_MINIFS_MS" +#define HID_FUNC_MAXIFS_MS "SC_HID_FUNC_MAXIFS_MS" /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx index d3e7567b458c..228fafa23ef4 100644 --- a/sc/qa/unit/ucalc.cxx +++ b/sc/qa/unit/ucalc.cxx @@ -2688,9 +2688,11 @@ void Test::testFunctionLists() "LOGNORMDIST", "MAX", "MAXA", + "MAXIFS", "MEDIAN", "MIN", "MINA", + "MINIFS", "MODE", "MODE.MULT", "MODE.SNGL", diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx index 8321e3b18748..a01b60bcb937 100644 --- a/sc/source/core/inc/interpre.hxx +++ b/sc/source/core/inc/interpre.hxx @@ -100,7 +100,9 @@ enum ScIterFuncIfs { ifSUMIFS, // Multi-Conditional sum ifAVERAGEIFS, // Multi-Conditional average - ifCOUNTIFS // Multi-Conditional count + ifCOUNTIFS, // Multi-Conditional count + ifMINIFS, // Multi-Conditional minimum + ifMAXIFS // Multi-Conditional maximum }; enum ScETSType @@ -608,6 +610,8 @@ void ScConcat_MS(); void ScTextJoin_MS(); void ScIfs_MS(); void ScSwitch_MS(); +void ScMinIfs_MS(); +void ScMaxIfs_MS(); void ScExternal(); void ScMissing(); void ScMacro(); diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx index 0a5ed78dfc17..dcaf3746e18b 100644 --- a/sc/source/core/tool/interpr1.cxx +++ b/sc/source/core/tool/interpr1.cxx @@ -5249,6 +5249,8 @@ double ScInterpreter::IterateParametersIfs( ScIterFuncIfs eFunc ) double fMem = 0.0; double fRes = 0.0; double fCount = 0.0; + double fMin = std::numeric_limits::max(); + double fMax = std::numeric_limits::min(); short nParam = 1; size_t nRefInList = 0; SCCOL nDimensionCols = 0; @@ -5484,7 +5486,7 @@ double ScInterpreter::IterateParametersIfs( ScIterFuncIfs eFunc ) if (nGlobalError) return 0; // bail out - // main range - only for AVERAGEIFS and SUMIFS + // main range - only for AVERAGEIFS, SUMIFS, MINIFS and MAXIFS if (nParamCount == 1) { nParam = 1; @@ -5587,6 +5589,10 @@ double ScInterpreter::IterateParametersIfs( ScIterFuncIfs eFunc ) } else fSum += fVal; + if ( fMin > fVal ) + fMin = fVal; + if ( fMax < fVal ) + fMax = fVal; } } else @@ -5612,6 +5618,10 @@ double ScInterpreter::IterateParametersIfs( ScIterFuncIfs eFunc ) } else fSum += fVal; + if ( fMin > fVal ) + fMin = fVal; + if ( fMax < fVal ) + fMax = fVal; } } } @@ -5631,6 +5641,8 @@ double ScInterpreter::IterateParametersIfs( ScIterFuncIfs eFunc ) case ifSUMIFS: fRes = ::rtl::math::approxAdd( fSum, fMem ); break; case ifAVERAGEIFS: fRes = div( ::rtl::math::approxAdd( fSum, fMem ), fCount); break; case ifCOUNTIFS: fRes = fCount; break; + case ifMINIFS: fRes = ( fMin < std::numeric_limits::max() ? fMin : 0 ); break; + case ifMAXIFS: fRes = ( fMax > std::numeric_limits::min() ? fMax : 0 ); break; default: ; // nothing } return fRes; diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx index 684913b3601a..e6985dc5447e 100644 --- a/sc/source/core/tool/interpr4.cxx +++ b/sc/source/core/tool/interpr4.cxx @@ -3944,6 +3944,8 @@ StackVar ScInterpreter::Interpret() case ocTextJoin_MS : ScTextJoin_MS(); break; case ocIfs_MS : ScIfs_MS(); break; case ocSwitch_MS : ScSwitch_MS(); break; + case ocMinIfs_MS : ScMinIfs_MS(); break; + case ocMaxIfs_MS : ScMaxIfs_MS(); break; case ocMatValue : ScMatValue(); break; case ocMatrixUnit : ScEMat(); break; case ocMatDet : ScMatDet(); break; diff --git a/sc/source/core/tool/interpr8.cxx b/sc/source/core/tool/interpr8.cxx index 5cb33f06e6db..aa7088624944 100644 --- a/sc/source/core/tool/interpr8.cxx +++ b/sc/source/core/tool/interpr8.cxx @@ -2019,4 +2019,16 @@ void ScInterpreter::ScSwitch_MS() PushError( errUnknownStackVariable ); } + +void ScInterpreter::ScMinIfs_MS() +{ + PushDouble( IterateParametersIfs( ifMINIFS ) ); +} + + +void ScInterpreter::ScMaxIfs_MS() +{ + PushDouble( IterateParametersIfs( ifMAXIFS ) ); +} + /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx index dadb2e7d8bad..c79a432c0601 100644 --- a/sc/source/filter/excel/xlformula.cxx +++ b/sc/source/filter/excel/xlformula.cxx @@ -583,7 +583,9 @@ static const XclFunctionInfo saFuncTable_2016[] = EXC_FUNCENTRY_V_VR( ocConcat_MS, 1, MX, 0, "CONCAT" ), EXC_FUNCENTRY_V_VR( ocTextJoin_MS, 3, MX, 0, "TEXTJOIN" ), EXC_FUNCENTRY_V_VR( ocIfs_MS, 2, MX, 0, "IFS" ), - EXC_FUNCENTRY_V_VR( ocSwitch_MS, 3, MX, 0, "SWITCH" ) + EXC_FUNCENTRY_V_VR( ocSwitch_MS, 3, MX, 0, "SWITCH" ), + EXC_FUNCENTRY_V_VR( ocMinIfs_MS, 3, MX, 0, "MINIFS" ), + EXC_FUNCENTRY_V_VR( ocMaxIfs_MS, 3, MX, 0, "MAXIFS" ) }; #define EXC_FUNCENTRY_ODF( opcode, minparam, maxparam, flags, asciiname ) \ diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx index c4636305bcaf..3778f2c1a582 100644 --- a/sc/source/filter/oox/formulabase.cxx +++ b/sc/source/filter/oox/formulabase.cxx @@ -913,7 +913,9 @@ static const FunctionData saFuncTable2016[] = { "COM.MICROSOFT.CONCAT", "CONCAT", NOID, NOID, 1, MX, V, { VR }, FUNCFLAG_MACROCALL_NEW }, { "COM.MICROSOFT.TEXTJOIN", "TEXTJOIN", NOID, NOID, 3, MX, V, { VR }, FUNCFLAG_MACROCALL_NEW }, { "COM.MICROSOFT.IFS", "IFS", NOID, NOID, 2, MX, R, { VO, RO }, FUNCFLAG_MACROCALL_NEW }, - { "COM.MICROSOFT.SWITCH", "SWITCH", NOID, NOID, 3, MX, R, { VO, RO }, FUNCFLAG_MACROCALL_NEW } + { "COM.MICROSOFT.SWITCH", "SWITCH", NOID, NOID, 3, MX, R, { VO, RO }, FUNCFLAG_MACROCALL_NEW }, + { "COM.MICROSOFT.MINIFS", "MINIFS", NOID, NOID, 3, MX, R, { VO, RO }, FUNCFLAG_MACROCALL_NEW }, + { "COM.MICROSOFT.MAXIFS", "MAXIFS", NOID, NOID, 3, MX, R, { VO, RO }, FUNCFLAG_MACROCALL_NEW } }; diff --git a/sc/source/ui/src/scfuncs.src b/sc/source/ui/src/scfuncs.src index 04c9388985c2..4ce4a7590502 100644 --- a/sc/source/ui/src/scfuncs.src +++ b/sc/source/ui/src/scfuncs.src @@ -11662,6 +11662,84 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2 { Text [ en-US ] = "Value to return when corresponding value argument matches expression." ; }; + }; + Resource SC_OPCODE_MINIFS_MS + { + String 1 // Description + { + Text [ en-US ] = "Returns the minimum value in a range that meet multiple criteria in multiple ranges." ; + }; + ExtraData = + { + 0; + ID_FUNCTION_GRP_STATISTIC; + HID_FUNC_MINIFS_MS; + PAIRED_VAR_ARGS + 1; 0; 0; 0; + 0; + }; + String 2 // Name of Parameter 1 + { + Text [ en-US ] = "min_range" ; + }; + String 3 // Description of Parameter 1 + { + Text [ en-US ] = "The range from which the minimum will be determined." ; + }; + String 4 // Name of Parameter 2 + { + Text [ en-US ] = "range" ; + }; + String 5 // Description of Parameter 2 + { + Text [ en-US ] = "The range from which the minimum value is to be taken." ; + }; + String 6 // Name of Parameter 3 + { + Text [ en-US ] = "criteria" ; + }; + String 7 // Description of Parameter 3 + { + Text [ en-US ] = "Criteria 1, criteria 2,... are the criteria to be applied to the ranges given." ; + }; + }; + Resource SC_OPCODE_MAXIFS_MS + { + String 1 // Description + { + Text [ en-US ] = "Returns the maximum value in a range that meet multiple criteria in multiple ranges." ; + }; + ExtraData = + { + 0; + ID_FUNCTION_GRP_STATISTIC; + HID_FUNC_MAXIFS_MS; + PAIRED_VAR_ARGS + 1; 0; 0; 0; + 0; + }; + String 2 // Name of Parameter 1 + { + Text [ en-US ] = "max_range" ; + }; + String 3 // Description of Parameter 1 + { + Text [ en-US ] = "The range from which the maximum will be determined." ; + }; + String 4 // Name of Parameter 2 + { + Text [ en-US ] = "range" ; + }; + String 5 // Description of Parameter 2 + { + Text [ en-US ] = "The range from which the minimum value is to be taken." ; + }; + String 6 // Name of Parameter 3 + { + Text [ en-US ] = "criteria" ; + }; + String 7 // Description of Parameter 3 + { + Text [ en-US ] = "Criteria 1, criteria 2,... are the criteria to be applied to the ranges given." ; + }; }; // -=*# Resource for function EXACT #*=- Resource SC_OPCODE_EXACT -- cgit v1.2.3