[Libreoffice-commits] core.git: 2 commits - sc/qa sc/source
Eike Rathke
erack at redhat.com
Fri Jun 9 22:47:03 UTC 2017
sc/qa/unit/ucalc_formula.cxx | 141 ++++++++++++++++++++-
sc/source/core/tool/interpr1.cxx | 259 +++++++++++++++++++++++++++++++--------
2 files changed, 342 insertions(+), 58 deletions(-)
New commits:
commit 076ed447f694239d5c67adee528ea6e471d909ff
Author: Eike Rathke <erack at redhat.com>
Date: Sat Jun 10 00:46:00 2017 +0200
Unit test for COUNTIFS, SUMIFS, AVERAGEIFS with array of references, tdf#58874
Change-Id: Ie833716fcde0b82dcaa2e5b66deb2952ee912b7f
diff --git a/sc/qa/unit/ucalc_formula.cxx b/sc/qa/unit/ucalc_formula.cxx
index dffcfff11dda..93c500a1dbfa 100644
--- a/sc/qa/unit/ucalc_formula.cxx
+++ b/sc/qa/unit/ucalc_formula.cxx
@@ -7936,15 +7936,140 @@ void Test::testFuncSUMIFS()
// Matrix formula in E8:E10 with AVERAGEIFS
m_pDoc->InsertMatrixFormula(4, 7, 4, 9, aMark, "=AVERAGEIFS(B1:B7;A1:A7;A9:A11)");
- // Result B1+B5, B2+B6, B3+B7 and counts and averages.
- std::vector<std::vector<const char*>> aCheck = {
- { "17", "2", "8.5" },
- { "34", "2", "17" },
- { "68", "2", "34" }
- };
+ {
+ // Result B1+B5, B2+B6, B3+B7 and counts and averages.
+ std::vector<std::vector<const char*>> aCheck = {
+ { "17", "2", "8.5" },
+ { "34", "2", "17" },
+ { "68", "2", "34" }
+ };
+ bool bGood = checkOutput(m_pDoc, ScRange(2,7,0, 4,9,0), aCheck,
+ "SUMIFS, COUNTIFS and AVERAGEIFS in array context");
+ CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS in array context failed", bGood);
+ }
+
+ // Matrix formula in G8:G10 with SUMIFS and reference list arrays.
+ m_pDoc->InsertMatrixFormula(6, 7, 6, 9, aMark, "=SUMIFS(OFFSET(B1;ROW(1:3);0;2);OFFSET(B1;ROW(1:3);0;2);\">4\")");
+ // Matrix formula in H8:H10 with COUNTIFS and reference list arrays.
+ m_pDoc->InsertMatrixFormula(7, 7, 7, 9, aMark, "=COUNTIFS(OFFSET(B1;ROW(1:3);0;2);\">4\")");
+ // Matrix formula in I8:I10 with AVERAGEIFS and reference list arrays.
+ m_pDoc->InsertMatrixFormula(8, 7, 8, 9, aMark, "=AVERAGEIFS(OFFSET(B1;ROW(1:3);0;2);OFFSET(B1;ROW(1:3);0;2);\">4\")");
+
+ {
+ // Result sums, counts and averages.
+ std::vector<std::vector<const char*>> aCheck = {
+ { "0", "0", "#DIV/0!" },
+ { "8", "1", "8" },
+ { "24", "2", "12" }
+ };
+ bool bGood = checkOutput(m_pDoc, ScRange(6,7,0, 8,9,0), aCheck,
+ "SUMIFS, COUNTIFS and AVERAGEIFS with reference list arrays");
+ CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list arrays failed", bGood);
+ }
+
+ // Matrix formula in K8:K10 with SUMIFS and reference list array condition
+ // and "normal" data range.
+ m_pDoc->InsertMatrixFormula(10, 7, 10, 9, aMark, "=SUMIFS(B1:B2;OFFSET(B1;ROW(1:3);0;2);\">4\")");
+ // Matrix formula in L8:L10 with AVERAGEIFS and reference list array
+ // condition and "normal" data range.
+ m_pDoc->InsertMatrixFormula(11, 7, 11, 9, aMark, "=AVERAGEIFS(B1:B2;OFFSET(B1;ROW(1:3);0;2);\">4\")");
+
+ {
+ // Result sums and averages.
+ std::vector<std::vector<const char*>> aCheck = {
+ { "0", "#DIV/0!" },
+ { "2", "2" },
+ { "3", "1.5" }
+ };
+ bool bGood = checkOutput(m_pDoc, ScRange(10,7,0, 11,9,0), aCheck,
+ "SUMIFS, COUNTIFS and AVERAGEIFS with reference list array and normal range");
+ CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list array and normal range failed", bGood);
+ }
+
+ // Matrix formula in G18:G20 with SUMIFS and reference list arrays and a
+ // "normal" criteria range.
+ m_pDoc->InsertMatrixFormula(6, 17, 6, 19, aMark, "=SUMIFS(OFFSET(B1;ROW(1:3);0;2);OFFSET(B1;ROW(1:3);0;2);\">4\";B1:B2;\">1\")");
+ // Matrix formula in H18:H20 with COUNTIFS and reference list arrays and a
+ // "normal" criteria range.
+ m_pDoc->InsertMatrixFormula(7, 17, 7, 19, aMark, "=COUNTIFS(OFFSET(B1;ROW(1:3);0;2);\">4\";B1:B2;\">1\")");
+ // Matrix formula in I18:I20 with AVERAGEIFS and reference list arrays and
+ // a "normal" criteria range.
+ m_pDoc->InsertMatrixFormula(8, 17, 8, 19, aMark, "=AVERAGEIFS(OFFSET(B1;ROW(1:3);0;2);OFFSET(B1;ROW(1:3);0;2);\">4\";B1:B2;\">1\")");
+
+ {
+ // Result sums, counts and averages.
+ std::vector<std::vector<const char*>> aCheck = {
+ { "0", "0", "#DIV/0!" },
+ { "8", "1", "8" },
+ { "16", "1", "16" }
+ };
+ bool bGood = checkOutput(m_pDoc, ScRange(6,17,0, 8,19,0), aCheck,
+ "SUMIFS, COUNTIFS and AVERAGEIFS with reference list arrays and a normal criteria range");
+ CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list arrays and a normal criteria range failed", bGood);
+ }
+
+ // Matrix formula in K18:K20 with SUMIFS and reference list array condition
+ // and "normal" data range and a "normal" criteria range.
+ m_pDoc->InsertMatrixFormula(10, 17, 10, 19, aMark, "=SUMIFS(B1:B2;OFFSET(B1;ROW(1:3);0;2);\">4\";B1:B2;\">1\")");
+ // Matrix formula in L18:L20 with AVERAGEIFS and reference list array
+ // condition and "normal" data range and a "normal" criteria range.
+ m_pDoc->InsertMatrixFormula(11, 17, 11, 19, aMark, "=AVERAGEIFS(B1:B2;OFFSET(B1;ROW(1:3);0;2);\">4\";B1:B2;\">1\")");
+
+ {
+ // Result sums and averages.
+ std::vector<std::vector<const char*>> aCheck = {
+ { "0", "#DIV/0!" },
+ { "2", "2" },
+ { "2", "2" }
+ };
+ bool bGood = checkOutput(m_pDoc, ScRange(10,17,0, 11,19,0), aCheck,
+ "SUMIFS, COUNTIFS and AVERAGEIFS with reference list array and normal data and criteria range");
+ CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list array and normal data and criteria range failed", bGood);
+ }
+
+ // Same, but swapped normal and array criteria.
+
+ // Matrix formula in G28:G30 with SUMIFS and reference list arrays and a
+ // "normal" criteria range, swapped.
+ m_pDoc->InsertMatrixFormula(6, 27, 6, 29, aMark, "=SUMIFS(OFFSET(B1;ROW(1:3);0;2);B1:B2;\">1\";OFFSET(B1;ROW(1:3);0;2);\">4\")");
+ // Matrix formula in H28:H30 with COUNTIFS and reference list arrays and a
+ // "normal" criteria range, swapped.
+ m_pDoc->InsertMatrixFormula(7, 27, 7, 29, aMark, "=COUNTIFS(B1:B2;\">1\";OFFSET(B1;ROW(1:3);0;2);\">4\")");
+ // Matrix formula in I28:I30 with AVERAGEIFS and reference list arrays and
+ // a "normal" criteria range, swapped.
+ m_pDoc->InsertMatrixFormula(8, 27, 8, 29, aMark, "=AVERAGEIFS(OFFSET(B1;ROW(1:3);0;2);B1:B2;\">1\";OFFSET(B1;ROW(1:3);0;2);\">4\")");
- bool bGood = checkOutput(m_pDoc, ScRange(2,7,0, 4,9,0), aCheck, "COUNTIFS, SUMIFS and AVERAGEIFS in array context");
- CPPUNIT_ASSERT_MESSAGE("COUNTIFS, SUMIFS or AVERAGEIFS in array context failed", bGood);
+ {
+ // Result sums, counts and averages.
+ std::vector<std::vector<const char*>> aCheck = {
+ { "0", "0", "#DIV/0!" },
+ { "8", "1", "8" },
+ { "16", "1", "16" }
+ };
+ bool bGood = checkOutput(m_pDoc, ScRange(6,27,0, 8,29,0), aCheck,
+ "SUMIFS, COUNTIFS and AVERAGEIFS with reference list arrays and a normal criteria range, swapped");
+ CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list arrays and a normal criteria range failed, swapped", bGood);
+ }
+
+ // Matrix formula in K28:K30 with SUMIFS and reference list array condition
+ // and "normal" data range and a "normal" criteria range, swapped.
+ m_pDoc->InsertMatrixFormula(10, 27, 10, 29, aMark, "=SUMIFS(B1:B2;B1:B2;\">1\";OFFSET(B1;ROW(1:3);0;2);\">4\")");
+ // Matrix formula in L28:L30 with AVERAGEIFS and reference list array
+ // condition and "normal" data range and a "normal" criteria range,
+ // swapped.
+ m_pDoc->InsertMatrixFormula(11, 27, 11, 29, aMark, "=AVERAGEIFS(B1:B2;B1:B2;\">1\";OFFSET(B1;ROW(1:3);0;2);\">4\")");
+
+ {
+ // Result sums and averages.
+ std::vector<std::vector<const char*>> aCheck = {
+ { "0", "#DIV/0!" },
+ { "2", "2" },
+ { "2", "2" }
+ };
+ bool bGood = checkOutput(m_pDoc, ScRange(10,27,0, 11,29,0), aCheck,
+ "SUMIFS, COUNTIFS and AVERAGEIFS with reference list array and normal data and criteria range, swapped");
+ CPPUNIT_ASSERT_MESSAGE("SUMIFS, COUNTIFS or AVERAGEIFS with reference list array and normal data and criteria range failed, swapped", bGood);
+ }
m_pDoc->DeleteTab(0);
}
commit c47fc935a135b4728b452d6f94a856040552a90c
Author: Eike Rathke <erack at redhat.com>
Date: Sat Jun 10 00:42:57 2017 +0200
COUNTIFS, SUMIFS, AVERAGEIFS, MINIFS, MAXIFS with reference arrays, tdf#58874
Change-Id: I3959d67bd206f68ba1d20499d919838773b2e7df
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 180d1400127f..df787af469e3 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -5637,11 +5637,12 @@ void ScInterpreter::IterateParametersIfs( double(*ResultFunc)( const sc::ParamIf
sal_uInt8 nParamCount = GetByte();
sal_uInt8 nQueryCount = nParamCount / 2;
- sc::ParamIfsResult aRes;
std::vector<sal_uInt32> vConditions;
double fVal = 0.0;
SCCOL nDimensionCols = 0;
SCROW nDimensionRows = 0;
+ const SCSIZE nRefArrayRows = GetRefListArrayMaxSize( nParamCount);
+ std::vector<std::vector<sal_uInt32>> vRefArrayConditions;
while (nParamCount > 1 && nGlobalError == FormulaError::NONE)
{
@@ -5729,6 +5730,7 @@ void ScInterpreter::IterateParametersIfs( double(*ResultFunc)( const sc::ParamIf
// take range
short nParam = nParamCount;
size_t nRefInList = 0;
+ size_t nRefArrayPos = std::numeric_limits<size_t>::max();
SCCOL nCol1 = 0;
SCROW nRow1 = 0;
SCTAB nTab1 = 0;
@@ -5742,6 +5744,38 @@ void ScInterpreter::IterateParametersIfs( double(*ResultFunc)( const sc::ParamIf
{
case svRefList :
{
+ const ScRefListToken* p = dynamic_cast<const ScRefListToken*>(pStack[sp-1]);
+ if (p && p->IsArrayResult())
+ {
+ if (nRefInList == 0)
+ {
+ if (vRefArrayConditions.empty())
+ vRefArrayConditions.resize( nRefArrayRows);
+ if (!vConditions.empty())
+ {
+ // Similar to other reference list array
+ // handling, add/op the current value to
+ // all array positions.
+ for (auto & rVec : vRefArrayConditions)
+ {
+ if (rVec.empty())
+ rVec = vConditions;
+ else
+ {
+ assert(rVec.size() == vConditions.size()); // see dimensions below
+ for (size_t i=0, n = rVec.size(); i < n; ++i)
+ {
+ rVec[i] += vConditions[i];
+ }
+ }
+ }
+ // Reset condition results.
+ std::for_each( vConditions.begin(), vConditions.end(),
+ [](sal_uInt32 & r){ r = 0.0; } );
+ }
+ }
+ nRefArrayPos = nRefInList;
+ }
ScRange aRange;
PopDoubleRef( aRange, nParam, nRefInList);
aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
@@ -5874,21 +5908,66 @@ void ScInterpreter::IterateParametersIfs( double(*ResultFunc)( const sc::ParamIf
} while ( aCellIter.GetNext() );
}
}
+ if (nRefArrayPos != std::numeric_limits<size_t>::max())
+ {
+ // Apply condition result to reference list array result position.
+ std::vector<sal_uInt32>& rVec = vRefArrayConditions[nRefArrayPos];
+ if (rVec.empty())
+ rVec = vConditions;
+ else
+ {
+ assert(rVec.size() == vConditions.size()); // see dimensions above
+ for (size_t i=0, n = rVec.size(); i < n; ++i)
+ {
+ rVec[i] += vConditions[i];
+ }
+ }
+ // Reset conditions vector.
+ // When leaving an svRefList this has to be emptied not set to
+ // 0.0 because it's checked when entering an svRefList.
+ if (nRefInList == 0)
+ std::vector<sal_uInt32>().swap( vConditions);
+ else
+ std::for_each( vConditions.begin(), vConditions.end(), [](sal_uInt32 & r){ r = 0.0; } );
+ }
}
nParamCount -= 2;
}
+ if (!vRefArrayConditions.empty() && !vConditions.empty())
+ {
+ // Add/op the last current value to all array positions.
+ for (auto & rVec : vRefArrayConditions)
+ {
+ if (rVec.empty())
+ rVec = vConditions;
+ else
+ {
+ assert(rVec.size() == vConditions.size()); // see dimensions above
+ for (size_t i=0, n = rVec.size(); i < n; ++i)
+ {
+ rVec[i] += vConditions[i];
+ }
+ }
+ }
+ }
+
if (nGlobalError != FormulaError::NONE)
{
PushError( nGlobalError);
return; // bail out
}
+ sc::ParamIfsResult aRes;
+ ScMatrixRef xResMat;
+
// main range - only for AVERAGEIFS, SUMIFS, MINIFS and MAXIFS
if (nParamCount == 1)
{
short nParam = nParamCount;
size_t nRefInList = 0;
+ size_t nRefArrayPos = std::numeric_limits<size_t>::max();
+ bool bRefArrayMain = false;
while (nParam-- == nParamCount)
{
bool bNull = true;
@@ -5903,6 +5982,24 @@ void ScInterpreter::IterateParametersIfs( double(*ResultFunc)( const sc::ParamIf
{
case svRefList :
{
+ const ScRefListToken* p = dynamic_cast<const ScRefListToken*>(pStack[sp-1]);
+ if (p && p->IsArrayResult())
+ {
+ if (vRefArrayConditions.empty())
+ {
+ // Replicate conditions if there wasn't a
+ // reference list array for criteria
+ // evaluation.
+ vRefArrayConditions.resize( nRefArrayRows);
+ for (auto & rVec : vRefArrayConditions)
+ {
+ rVec = vConditions;
+ }
+ }
+
+ bRefArrayMain = true;
+ nRefArrayPos = nRefInList;
+ }
ScRange aRange;
PopDoubleRef( aRange, nParam, nRefInList);
aRange.GetVars( nMainCol1, nMainRow1, nMainTab1, nMainCol2, nMainRow2, nMainTab2);
@@ -5961,85 +6058,147 @@ void ScInterpreter::IterateParametersIfs( double(*ResultFunc)( const sc::ParamIf
}
// end-result calculation
- ScAddress aAdr;
- aAdr.SetTab( nMainTab1 );
+
+ // This gets weird.. if conditions were calculated using a
+ // reference list array but the main calculation range is not a
+ // reference list array, then the conditions of the array are
+ // applied to the main range each in turn to form the array result.
+
+ size_t nRefArrayMainPos = (bRefArrayMain ? nRefArrayPos :
+ (vRefArrayConditions.empty() ? std::numeric_limits<size_t>::max() : 0));
+ const bool bAppliedArray = (!bRefArrayMain && nRefArrayMainPos == 0);
+
+ if (nRefArrayMainPos == 0)
+ xResMat = GetNewMat( 1, nRefArrayRows);
+
if (pMainMatrix)
{
std::vector<double> aMainValues;
pMainMatrix->GetDoubleArray(aMainValues, false); // Map empty values to NaN's.
- if (vConditions.size() != aMainValues.size())
- {
- PushError( FormulaError::IllegalArgument);
- return;
- }
- std::vector<sal_uInt32>::const_iterator itRes = vConditions.begin(), itResEnd = vConditions.end();
- std::vector<double>::const_iterator itMain = aMainValues.begin();
- for (; itRes != itResEnd; ++itRes, ++itMain)
+ do
{
- if (*itRes != nQueryCount)
- continue;
+ if (nRefArrayMainPos < vRefArrayConditions.size())
+ vConditions = vRefArrayConditions[nRefArrayMainPos];
+
+ if (vConditions.size() != aMainValues.size())
+ {
+ PushError( FormulaError::IllegalArgument);
+ return;
+ }
- fVal = *itMain;
- if (GetDoubleErrorValue(fVal) == FormulaError::ElementNaN)
- continue;
+ std::vector<sal_uInt32>::const_iterator itRes = vConditions.begin(), itResEnd = vConditions.end();
+ std::vector<double>::const_iterator itMain = aMainValues.begin();
+ for (; itRes != itResEnd; ++itRes, ++itMain)
+ {
+ if (*itRes != nQueryCount)
+ continue;
- ++aRes.mfCount;
- if (bNull && fVal != 0.0)
+ fVal = *itMain;
+ if (GetDoubleErrorValue(fVal) == FormulaError::ElementNaN)
+ continue;
+
+ ++aRes.mfCount;
+ if (bNull && fVal != 0.0)
+ {
+ bNull = false;
+ aRes.mfMem = fVal;
+ }
+ else
+ aRes.mfSum += fVal;
+ if ( aRes.mfMin > fVal )
+ aRes.mfMin = fVal;
+ if ( aRes.mfMax < fVal )
+ aRes.mfMax = fVal;
+ }
+ if (nRefArrayMainPos != std::numeric_limits<size_t>::max())
{
- bNull = false;
- aRes.mfMem = fVal;
+ xResMat->PutDouble( ResultFunc( aRes), 0, nRefArrayMainPos);
+ aRes = sc::ParamIfsResult();
}
- else
- aRes.mfSum += fVal;
- if ( aRes.mfMin > fVal )
- aRes.mfMin = fVal;
- if ( aRes.mfMax < fVal )
- aRes.mfMax = fVal;
}
+ while (bAppliedArray && ++nRefArrayMainPos < nRefArrayRows);
}
else
{
- std::vector<sal_uInt32>::const_iterator itRes = vConditions.begin();
- for (SCCOL nCol = 0; nCol < nDimensionCols; ++nCol)
+ ScAddress aAdr;
+ aAdr.SetTab( nMainTab1 );
+ do
{
- for (SCROW nRow = 0; nRow < nDimensionRows; ++nRow, ++itRes)
+ if (nRefArrayMainPos < vRefArrayConditions.size())
+ vConditions = vRefArrayConditions[nRefArrayMainPos];
+
+ std::vector<sal_uInt32>::const_iterator itRes = vConditions.begin();
+ for (SCCOL nCol = 0; nCol < nDimensionCols; ++nCol)
{
- if (*itRes == nQueryCount)
+ for (SCROW nRow = 0; nRow < nDimensionRows; ++nRow, ++itRes)
{
- aAdr.SetCol( nCol + nMainCol1);
- aAdr.SetRow( nRow + nMainRow1);
- ScRefCellValue aCell(*pDok, aAdr);
- if (aCell.hasNumeric())
+ if (*itRes == nQueryCount)
{
- fVal = GetCellValue(aAdr, aCell);
- ++aRes.mfCount;
- if ( bNull && fVal != 0.0 )
+ aAdr.SetCol( nCol + nMainCol1);
+ aAdr.SetRow( nRow + nMainRow1);
+ ScRefCellValue aCell(*pDok, aAdr);
+ if (aCell.hasNumeric())
{
- bNull = false;
- aRes.mfMem = fVal;
+ fVal = GetCellValue(aAdr, aCell);
+ ++aRes.mfCount;
+ if ( bNull && fVal != 0.0 )
+ {
+ bNull = false;
+ aRes.mfMem = fVal;
+ }
+ else
+ aRes.mfSum += fVal;
+ if ( aRes.mfMin > fVal )
+ aRes.mfMin = fVal;
+ if ( aRes.mfMax < fVal )
+ aRes.mfMax = fVal;
}
- else
- aRes.mfSum += fVal;
- if ( aRes.mfMin > fVal )
- aRes.mfMin = fVal;
- if ( aRes.mfMax < fVal )
- aRes.mfMax = fVal;
}
}
}
+ if (nRefArrayMainPos != std::numeric_limits<size_t>::max())
+ {
+ xResMat->PutDouble( ResultFunc( aRes), 0, nRefArrayMainPos);
+ aRes = sc::ParamIfsResult();
+ }
}
+ while (bAppliedArray && ++nRefArrayMainPos < nRefArrayRows);
}
}
}
else
{
- std::vector<sal_uInt32>::const_iterator itRes = vConditions.begin(), itResEnd = vConditions.end();
- for (; itRes != itResEnd; ++itRes)
- if (*itRes == nQueryCount)
- ++aRes.mfCount;
+ // COUNTIFS only.
+ if (vRefArrayConditions.empty())
+ {
+ for (auto const & rCond : vConditions)
+ {
+ if (rCond == nQueryCount)
+ ++aRes.mfCount;
+ }
+ }
+ else
+ {
+ xResMat = GetNewMat( 1, nRefArrayRows);
+ for (size_t i=0, n = vRefArrayConditions.size(); i < n; ++i)
+ {
+ double fCount = 0.0;
+ for (auto const & rCond : vRefArrayConditions[i])
+ {
+ if (rCond == nQueryCount)
+ ++fCount;
+ }
+ if (fCount)
+ xResMat->PutDouble( fCount, 0, i);
+ }
+ }
}
- PushDouble( ResultFunc( aRes));
+
+ if (xResMat)
+ PushMatrix( xResMat);
+ else
+ PushDouble( ResultFunc( aRes));
}
void ScInterpreter::ScSumIfs()
More information about the Libreoffice-commits
mailing list