diff options
Diffstat (limited to 'sc/source/ui/StatisticsDialogs/AnalysisOfVarianceDialog.cxx')
| -rw-r--r-- | sc/source/ui/StatisticsDialogs/AnalysisOfVarianceDialog.cxx | 559 |
1 files changed, 559 insertions, 0 deletions
diff --git a/sc/source/ui/StatisticsDialogs/AnalysisOfVarianceDialog.cxx b/sc/source/ui/StatisticsDialogs/AnalysisOfVarianceDialog.cxx new file mode 100644 index 0000000000..6f1fccd9cb --- /dev/null +++ b/sc/source/ui/StatisticsDialogs/AnalysisOfVarianceDialog.cxx @@ -0,0 +1,559 @@ +/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */ +/* + * This file is part of the LibreOffice project. + * + * This Source Code Form is subject to the terms of the Mozilla Public + * License, v. 2.0. If a copy of the MPL was not distributed with this + * file, You can obtain one at http://mozilla.org/MPL/2.0/. + * + */ + +#include <memory> +#include <string_view> + +#include <rangelst.hxx> +#include <reffact.hxx> +#include <TableFillingAndNavigationTools.hxx> +#include <AnalysisOfVarianceDialog.hxx> +#include <scresid.hxx> +#include <strings.hrc> + +namespace +{ + +struct StatisticCalculation { + TranslateId aLabelId; + const char* aFormula; + const char* aResultRangeName; +}; + +StatisticCalculation const lclBasicStatistics[] = +{ + { STR_ANOVA_LABEL_GROUPS, nullptr, nullptr }, + { STRID_CALC_COUNT, "=COUNT(%RANGE%)", "COUNT_RANGE" }, + { STRID_CALC_SUM, "=SUM(%RANGE%)", "SUM_RANGE" }, + { STRID_CALC_MEAN, "=AVERAGE(%RANGE%)", "MEAN_RANGE" }, + { STRID_CALC_VARIANCE, "=VAR(%RANGE%)", "VAR_RANGE" }, + { {}, nullptr, nullptr } +}; + +const TranslateId lclAnovaLabels[] = +{ + STR_ANOVA_LABEL_SOURCE_OF_VARIATION, + STR_ANOVA_LABEL_SS, + STR_ANOVA_LABEL_DF, + STR_ANOVA_LABEL_MS, + STR_ANOVA_LABEL_F, + STR_ANOVA_LABEL_P_VALUE, + STR_ANOVA_LABEL_F_CRITICAL, + {} +}; + +constexpr OUString strWildcardRange = u"%RANGE%"_ustr; + +OUString lclCreateMultiParameterFormula( + ScRangeList& aRangeList, const OUString& aFormulaTemplate, + std::u16string_view aWildcard, const ScDocument& rDocument, + const ScAddress::Details& aAddressDetails) +{ + OUStringBuffer aResult; + for (size_t i = 0; i < aRangeList.size(); i++) + { + OUString aRangeString(aRangeList[i].Format(rDocument, ScRefFlags::RANGE_ABS_3D, aAddressDetails)); + OUString aFormulaString = aFormulaTemplate.replaceAll(aWildcard, aRangeString); + aResult.append(aFormulaString); + if(i != aRangeList.size() - 1) // Not Last + aResult.append(";"); + } + return aResult.makeStringAndClear(); +} + +void lclMakeSubRangesList(ScRangeList& rRangeList, const ScRange& rInputRange, ScStatisticsInputOutputDialog::GroupedBy aGroupedBy) +{ + std::unique_ptr<DataRangeIterator> pIterator; + if (aGroupedBy == ScStatisticsInputOutputDialog::BY_COLUMN) + pIterator.reset(new DataRangeByColumnIterator(rInputRange)); + else + pIterator.reset(new DataRangeByRowIterator(rInputRange)); + + for( ; pIterator->hasNext(); pIterator->next() ) + { + ScRange aRange = pIterator->get(); + rRangeList.push_back(aRange); + } +} + +} + +ScAnalysisOfVarianceDialog::ScAnalysisOfVarianceDialog( + SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, + weld::Window* pParent, ScViewData& rViewData ) + : ScStatisticsInputOutputDialog( + pSfxBindings, pChildWindow, pParent, rViewData, + "modules/scalc/ui/analysisofvariancedialog.ui", + "AnalysisOfVarianceDialog") + , meFactor(SINGLE_FACTOR) + , mxAlphaField(m_xBuilder->weld_spin_button("alpha-spin")) + , mxSingleFactorRadio(m_xBuilder->weld_radio_button("radio-single-factor")) + , mxTwoFactorRadio(m_xBuilder->weld_radio_button("radio-two-factor")) + , mxRowsPerSampleField(m_xBuilder->weld_spin_button("rows-per-sample-spin")) +{ + mxSingleFactorRadio->connect_toggled( LINK( this, ScAnalysisOfVarianceDialog, FactorChanged ) ); + mxTwoFactorRadio->connect_toggled( LINK( this, ScAnalysisOfVarianceDialog, FactorChanged ) ); + + mxSingleFactorRadio->set_active(true); + mxTwoFactorRadio->set_active(false); + + FactorChanged(); +} + +ScAnalysisOfVarianceDialog::~ScAnalysisOfVarianceDialog() +{ +} + +void ScAnalysisOfVarianceDialog::Close() +{ + DoClose( ScAnalysisOfVarianceDialogWrapper::GetChildWindowId() ); +} + +TranslateId ScAnalysisOfVarianceDialog::GetUndoNameId() +{ + return STR_ANALYSIS_OF_VARIANCE_UNDO_NAME; +} + +IMPL_LINK_NOARG( ScAnalysisOfVarianceDialog, FactorChanged, weld::Toggleable&, void ) +{ + FactorChanged(); +} + +void ScAnalysisOfVarianceDialog::FactorChanged() +{ + if (mxSingleFactorRadio->get_active()) + { + mxGroupByRowsRadio->set_sensitive(true); + mxGroupByColumnsRadio->set_sensitive(true); + mxRowsPerSampleField->set_sensitive(false); + meFactor = SINGLE_FACTOR; + } + else if (mxTwoFactorRadio->get_active()) + { + mxGroupByRowsRadio->set_sensitive(false); + mxGroupByColumnsRadio->set_sensitive(false); + mxRowsPerSampleField->set_sensitive(false); // Rows per sample not yet implemented + meFactor = TWO_FACTOR; + } +} + +void ScAnalysisOfVarianceDialog::RowColumn(ScRangeList& rRangeList, AddressWalkerWriter& aOutput, FormulaTemplate& aTemplate, + const OUString& sFormula, GroupedBy aGroupedBy, ScRange* pResultRange) +{ + if (pResultRange != nullptr) + pResultRange->aStart = aOutput.current(); + if (!sFormula.isEmpty()) + { + for (size_t i = 0; i < rRangeList.size(); i++) + { + ScRange const & rRange = rRangeList[i]; + aTemplate.setTemplate(sFormula); + aTemplate.applyRange(strWildcardRange, rRange); + aOutput.writeFormula(aTemplate.getTemplate()); + if (pResultRange != nullptr) + pResultRange->aEnd = aOutput.current(); + aOutput.nextRow(); + } + } + else + { + TranslateId pLabelId = (aGroupedBy == BY_COLUMN) ? STR_COLUMN_LABEL_TEMPLATE : STR_ROW_LABEL_TEMPLATE; + OUString aLabelTemplate(ScResId(pLabelId)); + + for (size_t i = 0; i < rRangeList.size(); i++) + { + aTemplate.setTemplate(aLabelTemplate); + aTemplate.applyNumber(u"%NUMBER%", i + 1); + aOutput.writeString(aTemplate.getTemplate()); + if (pResultRange != nullptr) + pResultRange->aEnd = aOutput.current(); + aOutput.nextRow(); + } + } +} + +void ScAnalysisOfVarianceDialog::AnovaSingleFactor(AddressWalkerWriter& output, FormulaTemplate& aTemplate) +{ + output.writeBoldString(ScResId(STR_ANOVA_SINGLE_FACTOR_LABEL)); + output.newLine(); + + double aAlphaValue = mxAlphaField->get_value() / 100.0; + output.writeString(ScResId(STR_LABEL_ALPHA)); + output.nextColumn(); + output.writeValue(aAlphaValue); + aTemplate.autoReplaceAddress("%ALPHA%", output.current()); + output.newLine(); + output.newLine(); + + // Write labels + for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId; i++) + { + output.writeString(ScResId(lclBasicStatistics[i].aLabelId)); + output.nextColumn(); + } + output.newLine(); + + // Collect aRangeList + ScRangeList aRangeList; + lclMakeSubRangesList(aRangeList, mInputRange, mGroupedBy); + + output.push(); + + // Write values + for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId; i++) + { + output.resetRow(); + ScRange aResultRange; + OUString sFormula = OUString::createFromAscii(lclBasicStatistics[i].aFormula); + RowColumn(aRangeList, output, aTemplate, sFormula, mGroupedBy, &aResultRange); + output.nextColumn(); + if (lclBasicStatistics[i].aResultRangeName != nullptr) + { + OUString sResultRangeName = OUString::createFromAscii(lclBasicStatistics[i].aResultRangeName); + aTemplate.autoReplaceRange("%" + sResultRangeName + "%", aResultRange); + } + } + + output.nextRow(); // Blank row + + // Write ANOVA labels + output.resetColumn(); + for(sal_Int32 i = 0; lclAnovaLabels[i]; i++) + { + output.writeString(ScResId(lclAnovaLabels[i])); + output.nextColumn(); + } + output.nextRow(); + + aTemplate.autoReplaceRange("%FIRST_COLUMN%", aRangeList[0]); + + // Between Groups + { + // Label + output.resetColumn(); + output.writeString(ScResId(STR_ANOVA_LABEL_BETWEEN_GROUPS)); + output.nextColumn(); + + // Sum of Squares + aTemplate.setTemplate("=SUMPRODUCT(%SUM_RANGE%;%MEAN_RANGE%)-SUM(%SUM_RANGE%)^2/SUM(%COUNT_RANGE%)"); + aTemplate.autoReplaceAddress("%BETWEEN_SS%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // Degree of freedom + aTemplate.setTemplate("=COUNT(%SUM_RANGE%)-1"); + aTemplate.autoReplaceAddress("%BETWEEN_DF%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // MS + aTemplate.setTemplate("=%BETWEEN_SS% / %BETWEEN_DF%"); + aTemplate.autoReplaceAddress("%BETWEEN_MS%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // F + aTemplate.setTemplate("=%BETWEEN_MS% / %WITHIN_MS%"); + aTemplate.applyAddress(u"%WITHIN_MS%", output.current(-1, 1)); + aTemplate.autoReplaceAddress("%F_VAL%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // P-value + aTemplate.setTemplate("=FDIST(%F_VAL%; %BETWEEN_DF%; %WITHIN_DF%"); + aTemplate.applyAddress(u"%WITHIN_DF%", output.current(-3, 1)); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // F critical + aTemplate.setTemplate("=FINV(%ALPHA%; %BETWEEN_DF%; %WITHIN_DF%"); + aTemplate.applyAddress(u"%WITHIN_DF%", output.current(-4, 1)); + output.writeFormula(aTemplate.getTemplate()); + } + output.nextRow(); + + // Within Groups + { + // Label + output.resetColumn(); + output.writeString(ScResId(STR_ANOVA_LABEL_WITHIN_GROUPS)); + output.nextColumn(); + + // Sum of Squares + OUString aSSPart = lclCreateMultiParameterFormula(aRangeList, "DEVSQ(%RANGE%)", strWildcardRange, mDocument, mAddressDetails); + aTemplate.setTemplate("=SUM(%RANGE%)"); + aTemplate.applyString(strWildcardRange, aSSPart); + aTemplate.autoReplaceAddress("%WITHIN_SS%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // Degree of freedom + aTemplate.setTemplate("=SUM(%COUNT_RANGE%)-COUNT(%COUNT_RANGE%)"); + aTemplate.autoReplaceAddress("%WITHIN_DF%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // MS + aTemplate.setTemplate("=%WITHIN_SS% / %WITHIN_DF%"); + output.writeFormula(aTemplate.getTemplate()); + } + output.nextRow(); + + // Total + { + // Label + output.resetColumn(); + output.writeString(ScResId(STR_ANOVA_LABEL_TOTAL)); + output.nextColumn(); + + // Sum of Squares + aTemplate.setTemplate("=DEVSQ(%RANGE_LIST%)"); + aTemplate.applyRangeList(u"%RANGE_LIST%", aRangeList, ';'); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // Degree of freedom + aTemplate.setTemplate("=SUM(%COUNT_RANGE%) - 1"); + output.writeFormula(aTemplate.getTemplate()); + } + output.nextRow(); +} + +void ScAnalysisOfVarianceDialog::AnovaTwoFactor(AddressWalkerWriter& output, FormulaTemplate& aTemplate) +{ + output.writeBoldString(ScResId(STR_ANOVA_TWO_FACTOR_LABEL)); + output.newLine(); + + double aAlphaValue = mxAlphaField->get_value() / 100.0; + output.writeString("Alpha"); + output.nextColumn(); + output.writeValue(aAlphaValue); + aTemplate.autoReplaceAddress("%ALPHA%", output.current()); + output.newLine(); + output.newLine(); + + // Write labels + for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId; i++) + { + output.writeString(ScResId(lclBasicStatistics[i].aLabelId)); + output.nextColumn(); + } + output.newLine(); + + ScRangeList aColumnRangeList; + ScRangeList aRowRangeList; + + lclMakeSubRangesList(aColumnRangeList, mInputRange, BY_COLUMN); + lclMakeSubRangesList(aRowRangeList, mInputRange, BY_ROW); + + // Write ColumnX values + output.push(); + for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId; i++) + { + output.resetRow(); + ScRange aResultRange; + OUString sFormula = OUString::createFromAscii(lclBasicStatistics[i].aFormula); + RowColumn(aColumnRangeList, output, aTemplate, sFormula, BY_COLUMN, &aResultRange); + if (lclBasicStatistics[i].aResultRangeName != nullptr) + { + OUString sResultRangeName = OUString::createFromAscii(lclBasicStatistics[i].aResultRangeName); + aTemplate.autoReplaceRange("%" + sResultRangeName + "_COLUMN%", aResultRange); + } + output.nextColumn(); + } + output.newLine(); + + // Write RowX values + output.push(); + for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId; i++) + { + output.resetRow(); + ScRange aResultRange; + OUString sFormula = OUString::createFromAscii(lclBasicStatistics[i].aFormula); + RowColumn(aRowRangeList, output, aTemplate, sFormula, BY_ROW, &aResultRange); + + if (lclBasicStatistics[i].aResultRangeName != nullptr) + { + OUString sResultRangeName = OUString::createFromAscii(lclBasicStatistics[i].aResultRangeName); + aTemplate.autoReplaceRange("%" + sResultRangeName + "_ROW%", aResultRange); + } + output.nextColumn(); + } + output.newLine(); + + // Write ANOVA labels + for(sal_Int32 i = 0; lclAnovaLabels[i]; i++) + { + output.writeString(ScResId(lclAnovaLabels[i])); + output.nextColumn(); + } + output.nextRow(); + + // Setup auto-replace strings + aTemplate.autoReplaceRange(strWildcardRange, mInputRange); + aTemplate.autoReplaceRange("%FIRST_COLUMN%", aColumnRangeList[0]); + aTemplate.autoReplaceRange("%FIRST_ROW%", aRowRangeList[0]); + + // Rows + { + // Label + output.resetColumn(); + output.writeString("Rows"); + output.nextColumn(); + + // Sum of Squares + aTemplate.setTemplate("=SUMPRODUCT(%SUM_RANGE_ROW%;%MEAN_RANGE_ROW%) - SUM(%RANGE%)^2 / COUNT(%RANGE%)"); + aTemplate.autoReplaceAddress("%ROW_SS%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // Degree of freedom + aTemplate.setTemplate("=MAX(%COUNT_RANGE_COLUMN%) - 1"); + aTemplate.autoReplaceAddress("%ROW_DF%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // MS + aTemplate.setTemplate("=%ROW_SS% / %ROW_DF%"); + aTemplate.autoReplaceAddress("%MS_ROW%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // F + aTemplate.setTemplate("=%MS_ROW% / %MS_ERROR%"); + aTemplate.applyAddress(u"%MS_ERROR%", output.current(-1, 2)); + aTemplate.autoReplaceAddress("%F_ROW%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // P-value + aTemplate.setTemplate("=FDIST(%F_ROW%; %ROW_DF%; %ERROR_DF%"); + aTemplate.applyAddress(u"%ERROR_DF%", output.current(-3, 2)); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // F critical + aTemplate.setTemplate("=FINV(%ALPHA%; %ROW_DF%; %ERROR_DF%"); + aTemplate.applyAddress(u"%ERROR_DF%", output.current(-4, 2)); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + } + output.nextRow(); + + // Columns + { + // Label + output.resetColumn(); + output.writeString("Columns"); + output.nextColumn(); + + // Sum of Squares + aTemplate.setTemplate("=SUMPRODUCT(%SUM_RANGE_COLUMN%;%MEAN_RANGE_COLUMN%) - SUM(%RANGE%)^2 / COUNT(%RANGE%)"); + aTemplate.autoReplaceAddress("%COLUMN_SS%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // Degree of freedom + aTemplate.setTemplate("=MAX(%COUNT_RANGE_ROW%) - 1"); + aTemplate.autoReplaceAddress("%COLUMN_DF%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // MS + aTemplate.setTemplate("=%COLUMN_SS% / %COLUMN_DF%"); + aTemplate.autoReplaceAddress("%MS_COLUMN%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // F + aTemplate.setTemplate("=%MS_COLUMN% / %MS_ERROR%"); + aTemplate.applyAddress(u"%MS_ERROR%", output.current(-1, 1)); + aTemplate.autoReplaceAddress("%F_COLUMN%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // P-value + aTemplate.setTemplate("=FDIST(%F_COLUMN%; %COLUMN_DF%; %ERROR_DF%"); + aTemplate.applyAddress(u"%ERROR_DF%", output.current(-3, 1)); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // F critical + aTemplate.setTemplate("=FINV(%ALPHA%; %COLUMN_DF%; %ERROR_DF%"); + aTemplate.applyAddress(u"%ERROR_DF%", output.current(-4, 1)); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + } + output.nextRow(); + + // Error + { + // Label + output.resetColumn(); + output.writeString("Error"); + output.nextColumn(); + + // Sum of Squares + aTemplate.setTemplate("=SUMSQ(%RANGE%)+SUM(%RANGE%)^2/COUNT(%RANGE%) - (SUMPRODUCT(%SUM_RANGE_ROW%;%MEAN_RANGE_ROW%) + SUMPRODUCT(%SUM_RANGE_COLUMN%;%MEAN_RANGE_COLUMN%))"); + aTemplate.autoReplaceAddress("%ERROR_SS%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // Degree of freedom + aTemplate.setTemplate("=%TOTAL_DF% - %ROW_DF% - %COLUMN_DF%"); + aTemplate.applyAddress(u"%TOTAL_DF%", output.current(0,1)); + aTemplate.autoReplaceAddress("%ERROR_DF%", output.current()); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // MS + aTemplate.setTemplate("=%ERROR_SS% / %ERROR_DF%"); + output.writeFormula(aTemplate.getTemplate()); + } + output.nextRow(); + + // Total + { + // Label + output.resetColumn(); + output.writeString("Total"); + output.nextColumn(); + + // Sum of Squares + aTemplate.setTemplate("=SUM(%ROW_SS%;%COLUMN_SS%;%ERROR_SS%)"); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + + // Degree of freedom + aTemplate.setTemplate("=COUNT(%RANGE%)-1"); + output.writeFormula(aTemplate.getTemplate()); + output.nextColumn(); + } +} + +ScRange ScAnalysisOfVarianceDialog::ApplyOutput(ScDocShell* pDocShell) +{ + AddressWalkerWriter output(mOutputAddress, pDocShell, mDocument, + formula::FormulaGrammar::mergeToGrammar(formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv)); + FormulaTemplate aTemplate(&mDocument); + + if (meFactor == SINGLE_FACTOR) + { + AnovaSingleFactor(output, aTemplate); + } + else if (meFactor == TWO_FACTOR) + { + AnovaTwoFactor(output, aTemplate); + } + + return ScRange(output.mMinimumAddress, output.mMaximumAddress); +} + +/* vim:set shiftwidth=4 softtabstop=4 expandtab: */ |