summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--formula/source/core/resource/core_resource.src7
-rw-r--r--include/formula/compiler.hrc3
-rw-r--r--include/formula/opcode.hxx1
-rw-r--r--sc/inc/global.hxx16
-rw-r--r--sc/inc/helpids.h1
-rw-r--r--sc/qa/unit/ucalc.cxx1
-rw-r--r--sc/source/core/inc/interpre.hxx1
-rw-r--r--sc/source/core/tool/interpr1.cxx91
-rw-r--r--sc/source/core/tool/interpr4.cxx1
-rw-r--r--sc/source/core/tool/parclass.cxx1
-rw-r--r--sc/source/filter/excel/xlformula.cxx1
-rw-r--r--sc/source/filter/oox/formulabase.cxx1
-rw-r--r--sc/source/ui/src/scfuncs.src48
13 files changed, 172 insertions, 1 deletions
diff --git a/formula/source/core/resource/core_resource.src b/formula/source/core/resource/core_resource.src
index 90544a41f2b9..1aecc1ae46a2 100644
--- a/formula/source/core/resource/core_resource.src
+++ b/formula/source/core/resource/core_resource.src
@@ -321,6 +321,7 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF
String SC_OPCODE_MODAL_VALUE_MULTI { Text = "COM.MICROSOFT.MODE.MULT" ; };
String SC_OPCODE_Z_TEST { Text = "ZTEST" ; };
String SC_OPCODE_Z_TEST_MS { Text = "COM.MICROSOFT.Z.TEST" ; };
+ String SC_OPCODE_AGGREGATE { Text = "COM.MICROSOFT.AGGREGATE" ; };
String SC_OPCODE_T_TEST { Text = "TTEST" ; };
String SC_OPCODE_T_TEST_MS { Text = "COM.MICROSOFT.T.TEST" ; };
String SC_OPCODE_RANK { Text = "RANK" ; };
@@ -726,6 +727,7 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML
String SC_OPCODE_MODAL_VALUE_MULTI { Text = "_xlfn.MODE.MULT" ; };
String SC_OPCODE_Z_TEST { Text = "ZTEST" ; };
String SC_OPCODE_Z_TEST_MS { Text = "_xlfn.Z.TEST" ; };
+ String SC_OPCODE_AGGREGATE { Text = "_xlfn.AGGREGATE" ; };
String SC_OPCODE_T_TEST { Text = "TTEST" ; };
String SC_OPCODE_T_TEST_MS { Text = "_xlfn.T.TEST" ; };
String SC_OPCODE_RANK { Text = "RANK" ; };
@@ -1133,6 +1135,7 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH
String SC_OPCODE_MODAL_VALUE_MULTI { Text = "MODE.MULT" ; };
String SC_OPCODE_Z_TEST { Text = "ZTEST" ; };
String SC_OPCODE_Z_TEST_MS { Text = "Z.TEST" ; };
+ String SC_OPCODE_AGGREGATE { Text = "AGGREGATE" ; };
String SC_OPCODE_T_TEST { Text = "TTEST" ; };
String SC_OPCODE_T_TEST_MS { Text = "T.TEST" ; };
String SC_OPCODE_RANK { Text = "RANK" ; };
@@ -2362,6 +2365,10 @@ Resource RID_STRLIST_FUNCTION_NAMES
{
Text [ en-US ] = "Z.TEST" ;
};
+ String SC_OPCODE_AGGREGATE
+ {
+ Text = "AGGREGATE" ;
+ };
String SC_OPCODE_T_TEST
{
Text [ en-US ] = "TTEST" ;
diff --git a/include/formula/compiler.hrc b/include/formula/compiler.hrc
index 96ea885d4b8f..2ce723011e5b 100644
--- a/include/formula/compiler.hrc
+++ b/include/formula/compiler.hrc
@@ -467,8 +467,9 @@
#define SC_OPCODE_FLOOR_MS 467
#define SC_OPCODE_NETWORKDAYS_MS 468
#define SC_OPCODE_WORKDAY_MS 469
+#define SC_OPCODE_AGGREGATE 470
-#define SC_OPCODE_STOP_2_PAR 470 /* last function with two or more parameters' OpCode + 1 */
+#define SC_OPCODE_STOP_2_PAR 471 /* 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 f5afa19abca3..0e9fb708adf7 100644
--- a/include/formula/opcode.hxx
+++ b/include/formula/opcode.hxx
@@ -366,6 +366,7 @@ enum OpCodeEnum
ocModalValue_Multi = SC_OPCODE_MODAL_VALUE_MULTI,
ocZTest = SC_OPCODE_Z_TEST,
ocZTest_MS = SC_OPCODE_Z_TEST_MS,
+ ocAggregate = SC_OPCODE_AGGREGATE,
ocTTest = SC_OPCODE_T_TEST,
ocTTest_MS = SC_OPCODE_T_TEST_MS,
ocRank = SC_OPCODE_RANK,
diff --git a/sc/inc/global.hxx b/sc/inc/global.hxx
index 3944f74ed4dd..0614bce65c65 100644
--- a/sc/inc/global.hxx
+++ b/sc/inc/global.hxx
@@ -234,6 +234,10 @@ const sal_uInt16 IDF_AUTOFILL = IDF_ALL & ~(IDF_NOTE | IDF_OBJECTS);
#define SC_SCENARIO_VALUE 32
#define SC_SCENARIO_PROTECT 64
+#define AGGR_IGN_NESTED_ST_AG 0x04
+#define AGGR_IGN_ERR_VAL 0x02
+#define AGGR_IGN_HID_ROW 0x01
+
/** Default cell clone flags: do not start listening, do not adjust 3D refs to
old position, clone note captions of cell notes. */
const int SC_CLONECELL_DEFAULT = 0x0000;
@@ -726,6 +730,18 @@ enum ScSubTotalFunc
SUBTOTAL_FUNC_SELECTION_COUNT = 12
};
+enum ScAggregateFunc
+ {
+ AGGREGATE_FUNC_MEDIAN = 12,
+ AGGREGATE_FUNC_MODSNGL = 13,
+ AGGREGATE_FUNC_LARGE = 14,
+ AGGREGATE_FUNC_SMALL = 15,
+ AGGREGATE_FUNC_PERCINC = 16,
+ AGGREGATE_FUNC_QRTINC = 17,
+ AGGREGATE_FUNC_PERCEXC = 18,
+ AGGREGATE_FUNC_QRTEXC = 19
+ };
+
class ScArea;
struct ScConsolidateParam
diff --git a/sc/inc/helpids.h b/sc/inc/helpids.h
index 2f73f3fbd12c..b5303e221803 100644
--- a/sc/inc/helpids.h
+++ b/sc/inc/helpids.h
@@ -706,5 +706,6 @@
#define HID_FUNC_WORKDAY_MS "SC_HID_FUNC_WORKDAY_MS"
#define HID_FUNC_ERF_MS "SC_HID_FUNC_EFR_MS"
#define HID_FUNC_ERFC_MS "SC_HID_FUNC_ERFC_MS"
+#define HID_FUNC_AGGREGATE "SC_HID_FUNC_AGGREGATE"
/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index 6b5585a8785f..48e25b6f79f9 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -2493,6 +2493,7 @@ void Test::testFunctionLists()
"ACOSH",
"ACOT",
"ACOTH",
+// "AGGREGATE", // fdo73148 function not yet visble in UI
"ASIN",
"ASINH",
"ATAN",
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 40b5206cb3e8..660ecf3c3dd0 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -797,6 +797,7 @@ void QuickSort(::std::vector<double>& rSortArray, ::std::vector<long>* pIndexOrd
void ScModalValue();
void ScModalValue_Multi();
void ScAveDev();
+void ScAggregate();
void ScDevSq();
void ScZTest();
void ScTTest();
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 9412c4269d5c..4b6140a1d457 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -6617,6 +6617,97 @@ void ScInterpreter::ScSubTotal()
}
}
+void ScInterpreter::ScAggregate()
+{
+ sal_uInt8 nParamCount = GetByte();
+ if ( MustHaveParamCountMin( nParamCount, 3 ) )
+ {
+ // fish the 1st parameter from the stack and push it on top.
+ const FormulaToken* p = pStack[ sp - nParamCount ];
+ PushTempToken( *p );
+ int nFunc = ( int ) ::rtl::math::approxFloor( GetDouble() );
+ // fish the 2nd parameter from the stack and push it on top.
+ const FormulaToken* p2 = pStack[ sp - ( nParamCount - 1 ) ];
+ PushTempToken( *p2 );
+ int nOption = ( int ) ::rtl::math::approxFloor( GetDouble() );
+
+ if ( nFunc < 1 || nFunc > 19 )
+ PushIllegalArgument();
+ else
+ {
+ sal_uInt16 nAggrFlags = 0x00;
+ switch ( nOption)
+ {
+ case 0 : // ignore nested SUBTOTAL and AGGREGATE functions
+ nAggrFlags = AGGR_IGN_NESTED_ST_AG;
+ break;
+ case 1 : // ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
+ nAggrFlags = AGGR_IGN_HID_ROW | AGGR_IGN_NESTED_ST_AG;
+ break;
+ case 2 : // ignore error values, nested SUBTOTAL and AGGREGATE functions
+ nAggrFlags = AGGR_IGN_ERR_VAL | AGGR_IGN_NESTED_ST_AG;
+ break;
+ case 3 : // ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
+ nAggrFlags = AGGR_IGN_HID_ROW | AGGR_IGN_ERR_VAL | AGGR_IGN_NESTED_ST_AG;
+ break;
+ case 4 : // ignore nothing
+ break;
+ case 5 : // ignore hidden rows
+ nAggrFlags = AGGR_IGN_HID_ROW ;
+ break;
+ case 6 : // ignore error values
+ nAggrFlags = AGGR_IGN_ERR_VAL ;
+ break;
+ case 7 : // igniore hidden rows and error values
+ nAggrFlags = AGGR_IGN_HID_ROW | AGGR_IGN_ERR_VAL ;
+ break;
+ default :
+ PushIllegalArgument();
+ break;
+ }
+ // TODO: implement filter options
+ if ( nAggrFlags != 0x00 )
+ {
+ PushError( errUnknownVariable );
+ return;
+ }
+
+ cPar = nParamCount - 2;
+ glSubTotal = true;
+ switch ( nFunc )
+ {
+ case SUBTOTAL_FUNC_AVE : ScAverage(); break;
+ case SUBTOTAL_FUNC_CNT : ScCount(); break;
+ case SUBTOTAL_FUNC_CNT2 : ScCount2(); break;
+ case SUBTOTAL_FUNC_MAX : ScMax(); break;
+ case SUBTOTAL_FUNC_MIN : ScMin(); break;
+ case SUBTOTAL_FUNC_PROD : ScProduct(); break;
+ case SUBTOTAL_FUNC_STD : ScStDev(); break;
+ case SUBTOTAL_FUNC_STDP : ScStDevP(); break;
+ case SUBTOTAL_FUNC_SUM : ScSum(); break;
+ case SUBTOTAL_FUNC_VAR : ScVar(); break;
+ case SUBTOTAL_FUNC_VARP : ScVarP(); break;
+ case AGGREGATE_FUNC_MEDIAN : ScMedian(); break;
+ case AGGREGATE_FUNC_MODSNGL : ScModalValue(); break;
+ case AGGREGATE_FUNC_LARGE : ScLarge(); break;
+ case AGGREGATE_FUNC_SMALL : ScSmall(); break;
+ case AGGREGATE_FUNC_PERCINC : ScPercentile( true ); break;
+ case AGGREGATE_FUNC_QRTINC : ScQuartile( true ); break;
+ case AGGREGATE_FUNC_PERCEXC : ScPercentile( false ); break;
+ case AGGREGATE_FUNC_QRTEXC : ScQuartile( false ); break;
+ default : PushIllegalArgument(); break;
+ }
+ glSubTotal = false;
+ }
+ double nVal = GetDouble();
+ // Get rid of the 1st and 2nd (fished) parameters.
+ Pop();
+ Pop();
+ if ( !nGlobalError )
+ PushDouble( nVal );
+ }
+}
+
ScDBQueryParamBase* ScInterpreter::GetDBParams( bool& rMissingField )
{
bool bAllowMissingField = false;
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 038c64966752..1a3294d769e6 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4095,6 +4095,7 @@ StackVar ScInterpreter::Interpret()
case ocEffektiv : ScEffektiv(); break;
case ocNominal : ScNominal(); break;
case ocSubTotal : ScSubTotal(); break;
+ case ocAggregate : ScAggregate(); break;
case ocDBSum : ScDBSum(); break;
case ocDBCount : ScDBCount(); break;
case ocDBCount2 : ScDBCount2(); break;
diff --git a/sc/source/core/tool/parclass.cxx b/sc/source/core/tool/parclass.cxx
index b19b361fca9d..040da290a15d 100644
--- a/sc/source/core/tool/parclass.cxx
+++ b/sc/source/core/tool/parclass.cxx
@@ -210,6 +210,7 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
{ ocZTest_MS, {{ Reference, Value, Value }, 0 }},
{ ocNetWorkdays_MS, {{ Value, Value, Value, Reference }, 0 }},
{ ocWorkday_MS, {{ Value, Value, Value, Reference }, 0 }},
+ { ocAggregate, {{ Value, Value, Reference }, 1 }},
// Excel doubts:
// ocN, ocT: Excel says (and handles) Reference, error? This means no
// position dependent SingleRef if DoubleRef, and no array calculation,
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index 449ebb7d4031..ff3d1c714ece 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -493,6 +493,7 @@ static const XclFunctionInfo saFuncTable_2010[] =
EXC_FUNCENTRY_V_VR( ocFloor_MS, 2, 2, 0, "FLOOR.PRECISE" ),
EXC_FUNCENTRY_V_VR( ocErf_MS, 1, 1, 0, "ERF.PRECISE" ),
EXC_FUNCENTRY_V_VR( ocErfc_MS, 1, 1, 0, "ERFC.PRECISE" ),
+ EXC_FUNCENTRY_V_RX( ocAggregate, 3, MX, 0, "AGGREGATE" ),
};
/** Functions new in Excel 2013.
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index bb38d621f2d4..771e3b8310a6 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -803,6 +803,7 @@ static const FunctionData saFuncTable2010[] =
{ "COM.MICROSOFT.FLOOR.PRECISE", "FLOOR.PRECISE", NOID, NOID, 2, 2, V, { VR }, FUNCFLAG_MACROCALL_NEW },
{ "COM.MICROSOFT.ERF.PRECISE", "ERF.PRECISE", NOID, NOID, 1, 1, V, { VR }, FUNCFLAG_MACROCALL_NEW },
{ "COM.MICROSOFT.ERFC.PRECISE", "ERFC.PRECISE", NOID, NOID, 1, 1, V, { VR }, FUNCFLAG_MACROCALL_NEW },
+ { "COM.MICROSOFT.AGGREGATE", "AGGREGATE", NOID, NOID, 3, MX, V, { VR, RO }, FUNCFLAG_MACROCALL_NEW }
};
/** Functions new in Excel 2013.
diff --git a/sc/source/ui/src/scfuncs.src b/sc/source/ui/src/scfuncs.src
index a248d72e2836..4fa92d8470de 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -4211,6 +4211,54 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
Text [ en-US ] = "The cells of the range which are to be taken into account." ;
};
};
+ // -=*# Resource for function AGGREGATE #*=-
+ Resource SC_OPCODE_AGGREGATE
+ {
+ String 1 // Description
+ {
+ Text [ en-US ] = "Calculates an aggregate in a spreadsheet." ;
+ };
+ ExtraData =
+ {
+ 1;
+ ID_FUNCTION_GRP_MATH;
+ U2S( HID_FUNC_AGGREGATE );
+ VAR_ARGS+3; 0; 0; 0; 1;
+ 0;
+ };
+ String 2 // Name of Parameter 1
+ {
+ Text [ en-US ] = "Function" ;
+ };
+ String 3 // Description of Parameter 1
+ {
+ Text [ en-US ] = "Function index. Is an index of the possible functions Total, Max, ..." ;
+ };
+ String 4 // Name of Parameter 2
+ {
+ Text [ en-US ] = "Options" ;
+ };
+ String 5 // Description of Parameter 2
+ {
+ Text [ en-US ] = "Option index. Is an index of the possible ignore options." ;
+ };
+ String 6 // Name of Parameter 3
+ {
+ Text [ en-US ] = "Ref1 or array " ;
+ };
+ String 7 // Description of Parameter 3
+ {
+ Text [ en-US ] = "The cell(s) of the range which are to be taken into account." ;
+ };
+ String 8 // Name of Parameter 4
+ {
+ Text [ en-US ] = "Ref2..n or k " ;
+ };
+ String 9 // Description of Parameter 4
+ {
+ Text [ en-US ] = "The cells of the range which are to be taken into account or mandatory 2nd argument for certain functions." ;
+ };
+ };
// -=*# Resource for function GANZZAHL #*=-
Resource SC_OPCODE_INT
{
generated by cgit v1.2.3 (git 2.39.1) at 2024-04-19 20:47:52 +0000