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 <[email protected]> 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 <[email protected]> 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() _______________________________________________ Libreoffice-commits mailing list [email protected] https://lists.freedesktop.org/mailman/listinfo/libreoffice-commits
