diff options
Diffstat (limited to 'sc')
| -rw-r--r-- | sc/inc/strings.hrc | 23 | ||||
| -rw-r--r-- | sc/qa/uitest/statistics/data/regression.ods | bin | 0 -> 19549 bytes | |||
| -rw-r--r-- | sc/qa/uitest/statistics/regression.py | 314 | ||||
| -rw-r--r-- | sc/source/ui/StatisticsDialogs/RegressionDialog.cxx | 707 | ||||
| -rw-r--r-- | sc/source/ui/StatisticsDialogs/StatisticsTwoVariableDialog.cxx | 28 | ||||
| -rw-r--r-- | sc/source/ui/StatisticsDialogs/TableFillingAndNavigationTools.cxx | 6 | ||||
| -rw-r--r-- | sc/source/ui/inc/RegressionDialog.hxx | 49 | ||||
| -rw-r--r-- | sc/source/ui/inc/StatisticsTwoVariableDialog.hxx | 2 | ||||
| -rw-r--r-- | sc/source/ui/inc/TableFillingAndNavigationTools.hxx | 2 | ||||
| -rw-r--r-- | sc/uiconfig/scalc/ui/regressiondialog.ui | 156 |
10 files changed, 882 insertions, 405 deletions
diff --git a/sc/inc/strings.hrc b/sc/inc/strings.hrc index 470f06161690..a91f295e1591 100644 --- a/sc/inc/strings.hrc +++ b/sc/inc/strings.hrc @@ -218,6 +218,7 @@ #define STR_EXPONENTIAL_SMOOTHING_UNDO_NAME NC_("STR_EXPONENTIAL_SMOOTHING_UNDO_NAME", "Exponential Smoothing") /* AnalysisOfVarianceDialog */ #define STR_ANALYSIS_OF_VARIANCE_UNDO_NAME NC_("STR_ANALYSIS_OF_VARIANCE_UNDO_NAME", "Analysis of Variance") +#define STR_LABEL_ANOVA NC_("STR_LABEL_ANOVA", "Analysis of Variance (ANOVA)") #define STR_ANOVA_SINGLE_FACTOR_LABEL NC_("STR_ANOVA_SINGLE_FACTOR_LABEL", "ANOVA - Single Factor") #define STR_ANOVA_TWO_FACTOR_LABEL NC_("STR_ANOVA_TWO_FACTOR_LABEL", "ANOVA - Two Factor") #define STR_ANOVA_LABEL_GROUPS NC_("STR_ANOVA_LABEL_GROUPS", "Groups") @@ -228,6 +229,7 @@ #define STR_ANOVA_LABEL_DF NC_("STR_ANOVA_LABEL_DF", "df") #define STR_ANOVA_LABEL_MS NC_("STR_ANOVA_LABEL_MS", "MS") #define STR_ANOVA_LABEL_F NC_("STR_ANOVA_LABEL_F", "F") +#define STR_ANOVA_LABEL_SIGNIFICANCE_F NC_("STR_ANOVA_LABEL_SIGNIFICANCE_F", "Significance F") #define STR_ANOVA_LABEL_P_VALUE NC_("STR_ANOVA_LABEL_P_VALUE", "P-value") #define STR_ANOVA_LABEL_F_CRITICAL NC_("STR_ANOVA_LABEL_F_CRITICAL", "F critical") #define STR_ANOVA_LABEL_TOTAL NC_("STR_ANOVA_LABEL_TOTAL", "Total") @@ -295,18 +297,37 @@ #define STR_HYPOTHESIZED_MEAN_DIFFERENCE_LABEL NC_("STR_HYPOTHESIZED_MEAN_DIFFERENCE_LABEL", "Hypothesized Mean Difference") #define STR_OBSERVATIONS_LABEL NC_("STR_OBSERVATIONS_LABEL", "Observations") #define STR_OBSERVED_MEAN_DIFFERENCE_LABEL NC_("STR_OBSERVED_MEAN_DIFFERENCE_LABEL", "Observed Mean Difference") +#define STR_LABEL_RSQUARED NC_("STR_LABEL_RSQUARED", "R^2") +#define STR_LABEL_ADJUSTED_RSQUARED NC_("STR_LABEL_ADJUSTED_RSQUARED", "Adjusted R^2") +#define STR_LABEL_XVARIABLES_COUNT NC_("STR_LABEL_XVARIABLES_COUNT", "Count of X variables") #define STR_DEGREES_OF_FREEDOM_LABEL NC_("STR_DEGREES_OF_FREEDOM_LABEL", "df") #define STR_P_VALUE_LABEL NC_("STR_P_VALUE_LABEL", "P-value") #define STR_CRITICAL_VALUE_LABEL NC_("STR_CRITICAL_VALUE_LABEL", "Critical Value") #define STR_TEST_STATISTIC_LABEL NC_("STR_TEST_STATISTIC_LABEL", "Test Statistic") +#define STR_LABEL_LOWER NC_("STR_LABEL_LOWER", "Lower") +#define STR_LABEL_UPPER NC_("STR_LABEL_Upper", "Upper") /* RegressionDialog */ #define STR_LABEL_LINEAR NC_("STR_LABEL_LINEAR", "Linear") #define STR_LABEL_LOGARITHMIC NC_("STR_LABEL_LOGARITHMIC", "Logarithmic") #define STR_LABEL_POWER NC_("STR_LABEL_POWER", "Power") +#define STR_MESSAGE_XINVALID_RANGE NC_("STR_MESSAGE_XINVALID_RANGE", "Independent variable(s) range is not valid.") +#define STR_MESSAGE_YINVALID_RANGE NC_("STR_MESSAGE_YINVALID_RANGE", "Dependent variable(s) range is not valid.") +#define STR_MESSAGE_INVALID_OUTPUT_ADDR NC_("STR_MESSAGE_INVALID_OUTPUT_ADDR", "Output range is not valid.") +#define STR_MESSAGE_INVALID_CONFIDENCE_LEVEL NC_("STR_MESSAGE_INVALID_CONFIDENCE_LEVEL", "Confidence level must be in the interval (0, 1).") +#define STR_MESSAGE_YVARIABLE_MULTI_COLUMN NC_("STR_MESSAGE_YVARIABLE_MULTI_COLUMN", "Y variable range cannot have more than 1 column.") +#define STR_MESSAGE_YVARIABLE_MULTI_ROW NC_("STR_MESSAGE_YVARIABLE_MULTI_ROW", "Y variable range cannot have more than 1 row.") +#define STR_MESSAGE_UNIVARIATE_NUMOBS_MISMATCH NC_("STR_MESSAGE_UNIVARIATE_NUMOBS_MISMATCH", "Univariate regression : The observation count in X and Y must match.") +#define STR_MESSAGE_MULTIVARIATE_NUMOBS_MISMATCH NC_("STR_MESSAGE_MULTIVARIATE_NUMOBS_MISMATCH", "Multivariate regression : The observation count in X and Y must match.") #define STR_LABEL_REGRESSION_MODEL NC_("STR_LABEL_REGRESSION_MODEL", "Regression Model") -#define STR_LABEL_RSQUARED NC_("STR_LABEL_RSQUARED", "R^2") +#define STR_LABEL_REGRESSION_STATISTICS NC_("STR_LABEL_REGRESSION_STATISTICS", "Regression Statistics") +#define STR_LABEL_RESIDUAL NC_("STR_LABEL_RESIDUAL", "Residual") +#define STR_LABEL_CONFIDENCE_LEVEL NC_("STR_LABEL_CONFIDENCE_LEVEL", "Confidence level") +#define STR_LABEL_COEFFICIENTS NC_("STR_LABEL_COEFFICIENTS", "Coefficients") +#define STR_LABEL_TSTATISTIC NC_("STR_LABEL_TSTATISTIC", "t-Statistic") #define STR_LABEL_SLOPE NC_("STR_LABEL_SLOPE", "Slope") #define STR_LABEL_INTERCEPT NC_("STR_LABEL_INTERCEPT", "Intercept") +#define STR_LABEL_PREDICTEDY NC_("STR_LABEL_PREDICTEDY", "Predicted Y") +#define STR_LINEST_RAW_OUTPUT_TITLE NC_("STR_LINEST_RAW_OUTPUT_TITLE", "LINEST raw output") /*F Test */ #define STR_FTEST_P_RIGHT_TAIL NC_("STR_FTEST_P_RIGHT_TAIL", "P (F<=f) right-tail") #define STR_FTEST_F_CRITICAL_RIGHT_TAIL NC_("STR_FTEST_F_CRITICAL_RIGHT_TAIL", "F Critical right-tail") diff --git a/sc/qa/uitest/statistics/data/regression.ods b/sc/qa/uitest/statistics/data/regression.ods Binary files differnew file mode 100644 index 000000000000..e38d0a7ab26b --- /dev/null +++ b/sc/qa/uitest/statistics/data/regression.ods diff --git a/sc/qa/uitest/statistics/regression.py b/sc/qa/uitest/statistics/regression.py index b586d7af7c49..ccf1258a776e 100644 --- a/sc/qa/uitest/statistics/regression.py +++ b/sc/qa/uitest/statistics/regression.py @@ -5,285 +5,99 @@ # file, You can obtain one at http://mozilla.org/MPL/2.0/. # from uitest.framework import UITestCase +from uitest.path import get_srcdir_url from uitest.uihelper.common import get_state_as_dict from uitest.uihelper.common import select_pos from uitest.uihelper.calc import enter_text_to_cell from libreoffice.calc.document import get_sheet_from_doc -from libreoffice.calc.conditional_format import get_conditional_format_from_sheet from uitest.debug import sleep from libreoffice.calc.document import get_cell_by_position from libreoffice.uno.propertyvalue import mkPropertyValues +def get_url_for_data_file(file_name): + return get_srcdir_url() + "/sc/qa/uitest/statistics/data/" + file_name + class regression(UITestCase): + def test_regression_row(self): + self._regression_check(data_groupedby_column = False) + def test_regression_column(self): - calc_doc = self.ui_test.create_doc_in_start_center("calc") + self._regression_check(data_groupedby_column = True) + + def _regression_check(self, data_groupedby_column = True): + calc_doc = self.ui_test.load_file(get_url_for_data_file("regression.ods")) xCalcDoc = self.xUITest.getTopFocusWindow() gridwin = xCalcDoc.getChild("grid_window") document = self.ui_test.get_component() - #fill data - enter_text_to_cell(gridwin, "A1", "Time") - enter_text_to_cell(gridwin, "A2", "1") - enter_text_to_cell(gridwin, "A3", "2") - enter_text_to_cell(gridwin, "A4", "3") - enter_text_to_cell(gridwin, "A5", "4") - enter_text_to_cell(gridwin, "A6", "5") - enter_text_to_cell(gridwin, "A7", "6") - enter_text_to_cell(gridwin, "A8", "7") - enter_text_to_cell(gridwin, "A9", "8") - enter_text_to_cell(gridwin, "A10", "9") - enter_text_to_cell(gridwin, "A11", "10") - enter_text_to_cell(gridwin, "B1", "Measurement") - enter_text_to_cell(gridwin, "B2", "2.7") - enter_text_to_cell(gridwin, "B3", "4") - enter_text_to_cell(gridwin, "B4", "4.4") - enter_text_to_cell(gridwin, "B5", "7.1") - enter_text_to_cell(gridwin, "B6", "4.9") - enter_text_to_cell(gridwin, "B7", "3.6") - enter_text_to_cell(gridwin, "B8", "4") - enter_text_to_cell(gridwin, "B9", "0.6") - enter_text_to_cell(gridwin, "B10", "1") - enter_text_to_cell(gridwin, "B11", "4.3") + # Initially the final check status is "FALSE" (failed). + self.assertEqual(get_cell_by_position(document, 11, 1, 5).getString(), "FALSE", + "Check status must be FALSE before the test") + self._do_regression(regression_type = "LINEAR", data_groupedby_column = data_groupedby_column) + self._do_regression(regression_type = "LOG", data_groupedby_column = data_groupedby_column) + self._do_regression(regression_type = "POWER", data_groupedby_column = data_groupedby_column) + self.assertEqual(get_cell_by_position(document, 11, 1, 5).getString(), "TRUE", + "One of more of the checks failed for data_groupedby_column = {}, manually try with the document". + format(data_groupedby_column)) + self.ui_test.close_doc() - gridwin.executeAction("SELECT", mkPropertyValues({"RANGE": "A2:B11"})) + def _do_regression(self, regression_type, data_groupedby_column = True): + assert(regression_type == "LINEAR" or regression_type == "LOG" or regression_type == "POWER") self.ui_test.execute_modeless_dialog_through_command(".uno:RegressionDialog") xDialog = self.xUITest.getTopFocusWindow() xvariable1rangeedit = xDialog.getChild("variable1-range-edit") xvariable2rangeedit = xDialog.getChild("variable2-range-edit") xoutputrangeedit = xDialog.getChild("output-range-edit") + xwithlabelscheck = xDialog.getChild("withlabels-check") xgroupedbyrowsradio = xDialog.getChild("groupedby-rows-radio") xgroupedbycolumnsradio = xDialog.getChild("groupedby-columns-radio") - xlinearcheck = xDialog.getChild("linear-check") - xlogarithmiccheck = xDialog.getChild("logarithmic-check") - xpowercheck = xDialog.getChild("power-check") + xlinearradio = xDialog.getChild("linear-radio") + xlogarithmicradio = xDialog.getChild("logarithmic-radio") + xpowerradio = xDialog.getChild("power-radio") + ## Set the X, Y and output ranges + xvariable1rangeedit.executeAction("FOCUS", tuple()) # Without this the range parser does not kick in somehow xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"CTRL+A"})) xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"BACKSPACE"})) - xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$Sheet1.$A$2:$A$11"})) + if data_groupedby_column: + xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$DataInColumns.$A$1:$C$11"})) + else: + xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$DataInRows.$A$1:$K$3"})) + + xvariable2rangeedit.executeAction("FOCUS", tuple()) # Without this the range parser does not kick in somehow xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"CTRL+A"})) xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"BACKSPACE"})) - xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$Sheet1.$B$2:$B$11"})) + if data_groupedby_column: + xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$DataInColumns.$D$1:$D$11"})) + else: + xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$DataInRows.$A$4:$K$4"})) + # The data ranges have labels in them + if (get_state_as_dict(xwithlabelscheck)["Selected"]) == "false": + xwithlabelscheck.executeAction("CLICK", tuple()) + + xoutputrangeedit.executeAction("FOCUS", tuple()) # Without this the range parser does not kick in somehow xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"CTRL+A"})) xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"BACKSPACE"})) - xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"F1"})) - xgroupedbycolumnsradio.executeAction("CLICK", tuple()) - if (get_state_as_dict(xlinearcheck)["Selected"]) == "false": - xlinearcheck.executeAction("CLICK", tuple()) - if (get_state_as_dict(xlogarithmiccheck)["Selected"]) == "false": - xlogarithmiccheck.executeAction("CLICK", tuple()) - if (get_state_as_dict(xpowercheck)["Selected"]) == "false": - xpowercheck.executeAction("CLICK", tuple()) + if regression_type == "LINEAR": + xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$ActualLinear.$A$1"})) + elif regression_type == "LOG": + xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$ActualLog.$A$1"})) + else: + xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$ActualPower.$A$1"})) + + if data_groupedby_column: + xgroupedbycolumnsradio.executeAction("CLICK", tuple()) + else: + xgroupedbyrowsradio.executeAction("CLICK", tuple()) + + if regression_type == "LINEAR": + xlinearradio.executeAction("CLICK", tuple()) + elif regression_type == "LOG": + xlogarithmicradio.executeAction("CLICK", tuple()) + else: + xpowerradio.executeAction("CLICK", tuple()) + xOKBtn = xDialog.getChild("ok") self.ui_test.close_dialog_through_button(xOKBtn) - #Verify - self.assertEqual(get_cell_by_position(document, 0, 5, 0).getString(), "Regression") - self.assertEqual(get_cell_by_position(document, 0, 5, 2).getString(), "Regression Model") - self.assertEqual(get_cell_by_position(document, 0, 5, 3).getString(), "R^2") - self.assertEqual(get_cell_by_position(document, 0, 5, 4).getString(), "Standard Error") - self.assertEqual(get_cell_by_position(document, 0, 5, 6).getString(), "Slope") - self.assertEqual(get_cell_by_position(document, 0, 5, 7).getString(), "Intercept") - self.assertEqual(get_cell_by_position(document, 0, 5, 9).getValue(), 1) - self.assertEqual(get_cell_by_position(document, 0, 5, 10).getValue(), 2) - self.assertEqual(get_cell_by_position(document, 0, 5, 11).getValue(), 3) - self.assertEqual(get_cell_by_position(document, 0, 5, 12).getValue(), 4) - self.assertEqual(get_cell_by_position(document, 0, 5, 13).getValue(), 5) - self.assertEqual(get_cell_by_position(document, 0, 5, 14).getValue(), 6) - self.assertEqual(get_cell_by_position(document, 0, 5, 15).getValue(), 7) - self.assertEqual(get_cell_by_position(document, 0, 5, 16).getValue(), 8) - self.assertEqual(get_cell_by_position(document, 0, 5, 17).getValue(), 9) - self.assertEqual(get_cell_by_position(document, 0, 5, 18).getValue(), 10) - - self.assertEqual(get_cell_by_position(document, 0, 6, 2).getString(), "Linear") - self.assertEqual(round(get_cell_by_position(document, 0, 6, 3).getValue(),13), 0.1243901235130) - self.assertEqual(round(get_cell_by_position(document, 0, 6, 4).getValue(),9), 1.869256861) - self.assertEqual(round(get_cell_by_position(document, 0, 6, 6).getValue(),8), -0.21939394) - self.assertEqual(round(get_cell_by_position(document, 0, 6, 7).getValue(),8), 4.86666667) - - self.assertEqual(round(get_cell_by_position(document, 0, 6, 9).getValue(),12), 4.647272727273) - self.assertEqual(round(get_cell_by_position(document, 0, 6, 10).getValue(),11), 4.42787878788) - self.assertEqual(round(get_cell_by_position(document, 0, 6, 11).getValue(),11), 4.20848484848) - self.assertEqual(round(get_cell_by_position(document, 0, 6, 12).getValue(),11), 3.98909090909) - self.assertEqual(round(get_cell_by_position(document, 0, 6, 13).getValue(),12), 3.769696969697) - self.assertEqual(round(get_cell_by_position(document, 0, 6, 14).getValue(),11), 3.55030303030) - self.assertEqual(round(get_cell_by_position(document, 0, 6, 15).getValue(),11), 3.33090909091) - self.assertEqual(round(get_cell_by_position(document, 0, 6, 16).getValue(),10), 3.1115151515) - self.assertEqual(round(get_cell_by_position(document, 0, 6, 17).getValue(),11), 2.89212121212) - self.assertEqual(round(get_cell_by_position(document, 0, 6, 18).getValue(),12), 2.672727272727) - - self.assertEqual(get_cell_by_position(document, 0, 7, 2).getString(), "Logarithmic") - self.assertEqual(round(get_cell_by_position(document, 0, 7, 3).getValue(),13), 0.0362835060138) - self.assertEqual(round(get_cell_by_position(document, 0, 7, 4).getValue(),9), 1.961048360) - self.assertEqual(round(get_cell_by_position(document, 0, 7, 6).getValue(),8), -0.48941120) - self.assertEqual(round(get_cell_by_position(document, 0, 7, 7).getValue(),8), 4.39922687) - self.assertEqual(round(get_cell_by_position(document, 0, 7, 9).getValue(),12), 4.399226869524) - self.assertEqual(round(get_cell_by_position(document, 0, 7, 10).getValue(),11), 4.05999287553) - self.assertEqual(round(get_cell_by_position(document, 0, 7, 11).getValue(),11), 3.86155371008) - self.assertEqual(round(get_cell_by_position(document, 0, 7, 12).getValue(),11), 3.72075888154) - self.assertEqual(round(get_cell_by_position(document, 0, 7, 13).getValue(),12), 3.611549928136) - self.assertEqual(round(get_cell_by_position(document, 0, 7, 14).getValue(),11), 3.52231971609) - self.assertEqual(round(get_cell_by_position(document, 0, 7, 15).getValue(),11), 3.44687664676) - self.assertEqual(round(get_cell_by_position(document, 0, 7, 16).getValue(),10), 3.3815248876) - self.assertEqual(round(get_cell_by_position(document, 0, 7, 17).getValue(),11), 3.32388055063) - self.assertEqual(round(get_cell_by_position(document, 0, 7, 18).getValue(),12), 3.272315934145) - - self.assertEqual(get_cell_by_position(document, 0, 8, 2).getString(), "Power") - self.assertEqual(round(get_cell_by_position(document, 0, 8, 3).getValue(),13), 0.0884254697227) - self.assertEqual(round(get_cell_by_position(document, 0, 8, 4).getValue(),9), 0.774632105) - self.assertEqual(round(get_cell_by_position(document, 0, 8, 6).getValue(),8), -0.31030853) - self.assertEqual(round(get_cell_by_position(document, 0, 8, 7).getValue(),8), 4.81267293) - self.assertEqual(round(get_cell_by_position(document, 0, 8, 9).getValue(),12), 4.812672931007) - self.assertEqual(round(get_cell_by_position(document, 0, 8, 10).getValue(),12), 3.881272835552) - self.assertEqual(round(get_cell_by_position(document, 0, 8, 11).getValue(),11), 3.42240619237) - self.assertEqual(round(get_cell_by_position(document, 0, 8, 12).getValue(),11), 3.13012727853) - self.assertEqual(round(get_cell_by_position(document, 0, 8, 13).getValue(),11), 2.92072046513) - self.assertEqual(round(get_cell_by_position(document, 0, 8, 14).getValue(),12), 2.760065430808) - self.assertEqual(round(get_cell_by_position(document, 0, 8, 15).getValue(),10), 2.6311476385) - self.assertEqual(round(get_cell_by_position(document, 0, 8, 16).getValue(),11), 2.52435146791) - self.assertEqual(round(get_cell_by_position(document, 0, 8, 17).getValue(),10), 2.4337544465) - self.assertEqual(round(get_cell_by_position(document, 0, 8, 18).getValue(),11), 2.35547130753) - - #undo - self.xUITest.executeCommand(".uno:Undo") - self.assertEqual(get_cell_by_position(document, 0, 5, 0).getString(), "") - - self.ui_test.close_doc() - -#doesn't work in test -# def test_regression_row(self): -# calc_doc = self.ui_test.create_doc_in_start_center("calc") -# xCalcDoc = self.xUITest.getTopFocusWindow() -# gridwin = xCalcDoc.getChild("grid_window") -# document = self.ui_test.get_component() -# #fill data -# enter_text_to_cell(gridwin, "A1", "Time") -# enter_text_to_cell(gridwin, "A2", "Measurement") -# enter_text_to_cell(gridwin, "B1", "1") -# enter_text_to_cell(gridwin, "B2", "2.7") -# enter_text_to_cell(gridwin, "C1", "2") -# enter_text_to_cell(gridwin, "C2", "4") -# enter_text_to_cell(gridwin, "D1", "3") -# enter_text_to_cell(gridwin, "D2", "4.4") -# enter_text_to_cell(gridwin, "E1", "4") -# enter_text_to_cell(gridwin, "E2", "7.1") -# enter_text_to_cell(gridwin, "F1", "5") -# enter_text_to_cell(gridwin, "F2", "4.9") -# enter_text_to_cell(gridwin, "G1", "6") -# enter_text_to_cell(gridwin, "G2", "3.6") -# enter_text_to_cell(gridwin, "H1", "7") -# enter_text_to_cell(gridwin, "H2", "4") -# enter_text_to_cell(gridwin, "I1", "8") -# enter_text_to_cell(gridwin, "I2", "0.6") -# enter_text_to_cell(gridwin, "J1", "9") -# enter_text_to_cell(gridwin, "J2", "1") -# enter_text_to_cell(gridwin, "K1", "10") -# enter_text_to_cell(gridwin, "K2", "4.3") - -## gridwin.executeAction("SELECT", mkPropertyValues({"RANGE": "B1:K1"})) -# sleep(5) -# self.ui_test.execute_modeless_dialog_through_command(".uno:RegressionDialog") -# xDialog = self.xUITest.getTopFocusWindow() -# xvariable1rangeedit = xDialog.getChild("variable1-range-edit") -# xvariable2rangeedit = xDialog.getChild("variable2-range-edit") -# xoutputrangeedit = xDialog.getChild("output-range-edit") -# xgroupedbyrowsradio = xDialog.getChild("groupedby-rows-radio") -# xgroupedbycolumnsradio = xDialog.getChild("groupedby-columns-radio") -# xlinearcheck = xDialog.getChild("linear-check") -# xlogarithmiccheck = xDialog.getChild("logarithmic-check") -# xpowercheck = xDialog.getChild("power-check") -# sleep(4) - -# xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"CTRL+A"})) -# xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"BACKSPACE"})) -# xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$Sheet1.$B$1:$K$1"})) -# xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"CTRL+A"})) -# xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"BACKSPACE"})) -# xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"TAB"})) -# xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"CTRL+A"})) -# xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"BACKSPACE"})) -# xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$Sheet1.$B$2:$K$2"})) -# xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"CTRL+A"})) -# xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"BACKSPACE"})) -# xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$M$1"})) -# xgroupedbyrowsradio.executeAction("CLICK", tuple()) -# if (get_state_as_dict(xlinearcheck)["Selected"]) == "false": -# xlinearcheck.executeAction("CLICK", tuple()) -# if (get_state_as_dict(xlogarithmiccheck)["Selected"]) == "false": -# xlogarithmiccheck.executeAction("CLICK", tuple()) -# if (get_state_as_dict(xpowercheck)["Selected"]) == "false": -# xpowercheck.executeAction("CLICK", tuple()) -# xOKBtn = xDialog.getChild("ok") -# sleep(5) -# self.ui_test.close_dialog_through_button(xOKBtn) -# sleep(6) -# #Verify -# self.assertEqual(get_cell_by_position(document, 0, 12, 0).getString(), "Regression") -# self.assertEqual(get_cell_by_position(document, 0, 12, 2).getString(), "Regression Model") -# self.assertEqual(get_cell_by_position(document, 0, 12, 3).getString(), "R^2") -# self.assertEqual(get_cell_by_position(document, 0, 12, 4).getString(), "Standard Error") -# self.assertEqual(get_cell_by_position(document, 0, 12, 6).getString(), "Slope") -# self.assertEqual(get_cell_by_position(document, 0, 12, 7).getString(), "Intercept") -# self.assertEqual(get_cell_by_position(document, 0, 12, 9).getValue(), 1) -# self.assertEqual(get_cell_by_position(document, 0, 12, 10).getValue(), 2) -# self.assertEqual(get_cell_by_position(document, 0, 12, 11).getValue(), 3) -# self.assertEqual(get_cell_by_position(document, 0, 12, 12).getValue(), 4) -# self.assertEqual(get_cell_by_position(document, 0, 12, 13).getValue(), 5) -# self.assertEqual(get_cell_by_position(document, 0, 12, 14).getValue(), 6) -# self.assertEqual(get_cell_by_position(document, 0, 12, 15).getValue(), 7) -# self.assertEqual(get_cell_by_position(document, 0, 12, 16).getValue(), 8) -# self.assertEqual(get_cell_by_position(document, 0, 12, 17).getValue(), 9) -# self.assertEqual(get_cell_by_position(document, 0, 12, 18).getValue(), 10) - -# self.assertEqual(get_cell_by_position(document, 0, 13, 2).getString(), "Linear") -# self.assertEqual(round(get_cell_by_position(document, 0, 13, 3).getValue(),13), 0.1243901235130) -# self.assertEqual(round(get_cell_by_position(document, 0, 13, 4).getValue(),9), 1.869256861) -# self.assertEqual(round(get_cell_by_position(document, 0, 13, 6).getValue(),8), -0.21939394) -# self.assertEqual(round(get_cell_by_position(document, 0, 13, 7).getValue(),8), 4.86666667) - -# self.assertEqual(round(get_cell_by_position(document, 0, 13, 9).getValue(),12), 4.647272727273) -# self.assertEqual(round(get_cell_by_position(document, 0, 13, 10).getValue(),11), 4.42787878788) -# self.assertEqual(round(get_cell_by_position(document, 0, 13, 11).getValue(),11), 4.20848484848) -# self.assertEqual(round(get_cell_by_position(document, 0, 13, 12).getValue(),11), 3.98909090909) -# self.assertEqual(round(get_cell_by_position(document, 0, 13, 13).getValue(),12), 3.769696969697) -# self.assertEqual(round(get_cell_by_position(document, 0, 13, 14).getValue(),11), 3.55030303030) -# self.assertEqual(round(get_cell_by_position(document, 0, 13, 15).getValue(),11), 3.33090909091) -# self.assertEqual(round(get_cell_by_position(document, 0, 13, 16).getValue(),10), 3.1115151515) -# self.assertEqual(round(get_cell_by_position(document, 0, 13, 17).getValue(),11), 2.89212121212) -# self.assertEqual(round(get_cell_by_position(document, 0, 13, 18).getValue(),12), 2.672727272727) - -# self.assertEqual(get_cell_by_position(document, 0, 14, 2).getString(), "Logarithmic") -# self.assertEqual(round(get_cell_by_position(document, 0, 14, 3).getValue(),13), 0.0362835060138) -# self.assertEqual(round(get_cell_by_position(document, 0, 14, 4).getValue(),9), 1.961048360) -# self.assertEqual(round(get_cell_by_position(document, 0, 14, 6).getValue(),8), -0.48941120) -# self.assertEqual(round(get_cell_by_position(document, 0, 14, 7).getValue(),8), 4.39922687) -# self.assertEqual(round(get_cell_by_position(document, 0, 14, 9).getValue(),12), 4.399226869524) -# self.assertEqual(round(get_cell_by_position(document, 0, 14, 10).getValue(),11), 4.05999287553) -# self.assertEqual(round(get_cell_by_position(document, 0, 14, 11).getValue(),11), 3.86155371008) -# self.assertEqual(round(get_cell_by_position(document, 0, 14, 12).getValue(),11), 3.72075888154) -# self.assertEqual(round(get_cell_by_position(document, 0, 14, 13).getValue(),12), 3.611549928136) -# self.assertEqual(round(get_cell_by_position(document, 0, 14, 14).getValue(),11), 3.52231971609) -# self.assertEqual(round(get_cell_by_position(document, 0, 14, 15).getValue(),11), 3.44687664676) -# self.assertEqual(round(get_cell_by_position(document, 0, 14, 16).getValue(),10), 3.3815248876) -# self.assertEqual(round(get_cell_by_position(document, 0, 14, 17).getValue(),11), 3.32388055063) -# self.assertEqual(round(get_cell_by_position(document, 0, 14, 18).getValue(),12), 3.272315934145) - -# self.assertEqual(get_cell_by_position(document, 0, 15, 2).getString(), "Power") -# self.assertEqual(round(get_cell_by_position(document, 0, 15, 3).getValue(),13), 0.0884254697227) -# self.assertEqual(round(get_cell_by_position(document, 0, 15, 4).getValue(),9), 0.774632105) -# self.assertEqual(round(get_cell_by_position(document, 0, 15, 6).getValue(),8), -0.31030853) -# self.assertEqual(round(get_cell_by_position(document, 0, 15, 7).getValue(),8), 4.81267293) -# self.assertEqual(round(get_cell_by_position(document, 0, 15, 9).getValue(),12), 4.812672931007) -# self.assertEqual(round(get_cell_by_position(document, 0, 15, 10).getValue(),12), 3.881272835552) -# self.assertEqual(round(get_cell_by_position(document, 0, 15, 11).getValue(),11), 3.42240619237) -# self.assertEqual(round(get_cell_by_position(document, 0, 15, 12).getValue(),11), 3.13012727853) -# self.assertEqual(round(get_cell_by_position(document, 0, 15, 13).getValue(),11), 2.92072046513) -# self.assertEqual(round(get_cell_by_position(document, 0, 15, 14).getValue(),12), 2.760065430808) -# self.assertEqual(round(get_cell_by_position(document, 0, 15, 15).getValue(),10), 2.6311476385) -# self.assertEqual(round(get_cell_by_position(document, 0, 15, 16).getValue(),11), 2.52435146791) -# self.assertEqual(round(get_cell_by_position(document, 0, 15, 17).getValue(),10), 2.4337544465) -# self.assertEqual(round(get_cell_by_position(document, 0, 15, 18).getValue(),11), 2.35547130753) -# #undo -# self.xUITest.executeCommand(".uno:Undo") -# self.assertEqual(get_cell_by_position(document, 0, 5, 0).getString(), "") -# self.ui_test.close_doc() -# vim: set shiftwidth=4 softtabstop=4 expandtab:
\ No newline at end of file +# vim: set shiftwidth=4 softtabstop=4 expandtab: diff --git a/sc/source/ui/StatisticsDialogs/RegressionDialog.cxx b/sc/source/ui/StatisticsDialogs/RegressionDialog.cxx index a07e94286c58..f7bdbd3271ba 100644 --- a/sc/source/ui/StatisticsDialogs/RegressionDialog.cxx +++ b/sc/source/ui/StatisticsDialogs/RegressionDialog.cxx @@ -8,13 +8,10 @@ * */ -#include <memory> #include <sfx2/dispatch.hxx> #include <svl/zforlist.hxx> #include <svl/undo.hxx> -#include <formulacell.hxx> -#include <rangelst.hxx> #include <scitems.hxx> #include <docsh.hxx> #include <document.hxx> @@ -26,8 +23,90 @@ #include <scresid.hxx> #include <strings.hrc> +/* + Some regression basics + ---------------------- + + 1. Linear regression fits using data, a linear function between the dependent variable and the independent variable(s). + The basic form of this function is :- + + y = b + m_1*x_1 + m_2*x_2 + ... + m_k*x_k + + where y is the dependent variable + x_1, x_2, ..., x_k are the k independent variables + b is the intercept + m_1, m_2, ..., m_k are the slopes corresponding to the variables x_1, x_2, ..., x_k respectively. + + + This equation for n observations can be compactly written using matrices as :- + + y = X*A + + where y is the n dimensional column vector containing dependent variable observations. + where X is matrix of shape n*(k+1) where a row looks like [ 1 x_1 x_2 ... x_k ] + A is the k+1 dimensional column vector [ b m_1 m_2 ... m_k ] + + Calc formula LINEST(Y_array ; X_array) can be used to compute all entries in "A" along with many other statistics. + + + 2. Logarithmic regression is basically used to find a linear function between the dependent variable and + the natural logarithm of the independent variable(s). + So the basic form of this functions is :- + + y = b + m_1*ln(x_1) + m_2*ln(x_2) + ... + m_k*ln(x_k) + + This can be again written in a compact matrix form for n observations. + + y = ln(X)*A + + where y is the n dimensional column vector containing dependent variable observations. + where X is matrix of shape n*(k+1) where a row looks like [ e x_1 x_2 ... x_k ] + A is the k+1 dimensional column vector [ b m_1 m_2 ... m_k ] + + To estimate A, we use the formula =LINEST(Y_array ; LN(X_array)) + + + 3. Power regression is used to fit the following model :- + + y = b * (x_1 ^ m_1) * (x_2 ^ m_2) * ... * (x_k ^ m_k) + + To reduce this to a linear function(so that we can still use LINEST()), we take natural logarithm on both sides + + ln(y) = c + m_1*ln(x_1) + m_2*ln(x_2) + ... + m_k*ln(x_k) ; where c = ln(b) + + + This again can be written compactly in matrix form as :- + + ln(y) = ln(X)*A + + where y is the n dimensional column vector containing dependent variable observations. + where X is matrix of shape n*(k+1) where a row looks like [ e x_1 x_2 ... x_k ] + A is the k+1 dimensional column vector [ c m_1 m_2 ... m_k ] + + To estimate A, we use the formula =LINEST(LN(Y_array) ; LN(X_array)) + + Once we get A, to get back y from x's we use the formula :- + + y = exp( ln(X)*A ) + + + + Some references for computing confidence interval for the regression coefficients :- + + [1] https://en.wikipedia.org/wiki/Student%27s_t-test#Slope_of_a_regression_line + [2] https://en.wikipedia.org/wiki/Simple_linear_regression#Normality_assumption + [3] https://onlinecourses.science.psu.edu/stat414/node/280 + + */ + namespace { + enum class ScRegType { + LINEAR, + LOGARITHMIC, + POWER + }; + const char* constRegressionModel[] = { STR_LABEL_LINEAR, @@ -35,53 +114,51 @@ namespace STR_LABEL_POWER }; - OUString constTemplateRSQUARED[] = - { - "=RSQ(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE%)", - "=RSQ(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%))", - "=RSQ(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%))" - }; - - OUString constTemplatesSTDERR[] = - { - "=STEYX(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE%)", - "=STEYX(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%))", - "=STEYX(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%))" - }; - - OUString constTemplatesSLOPE[] = - { - "=SLOPE(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE%)", - "=SLOPE(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%))", - "=SLOPE(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%))" - }; - - OUString constTemplatesINTERCEPT[] = + OUString constTemplateLINEST[] = { - "=INTERCEPT(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE%)", - "=INTERCEPT(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%))", - "=EXP(INTERCEPT(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%)))" + "=LINEST(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE% ; TRUE ; TRUE)", + "=LINEST(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%) ; TRUE ; TRUE)", + "=LINEST(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%) ; TRUE ; TRUE)" }; OUString constRegressionFormula[] = { - "=%A% * %ADDRESS% + %B%", - "=%A% * LN(%ADDRESS%) + %B%", - "=%B% * %ADDRESS% ^ %A%" + "=MMULT(%XDATAMATRIX_RANGE% ; %SLOPES_RANGE%) + %INTERCEPT_ADDR%", + "=MMULT(LN(%XDATAMATRIX_RANGE%) ; %SLOPES_RANGE%) + %INTERCEPT_ADDR%", + "=EXP(MMULT(LN(%XDATAMATRIX_RANGE%) ; %SLOPES_RANGE%) + %INTERCEPT_ADDR%)" }; } // end anonymous namespace +static size_t lcl_GetNumRowsColsInRange(const ScRange& rRange, bool bRows) +{ + if (bRows) + return rRange.aEnd.Row() - rRange.aStart.Row() + 1; + + return rRange.aEnd.Col() - rRange.aStart.Col() + 1; +} + ScRegressionDialog::ScRegressionDialog( SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow, vcl::Window* pParent, ScViewData* pViewData ) : ScStatisticsTwoVariableDialog( pSfxBindings, pChildWindow, pParent, pViewData, - "RegressionDialog", "modules/scalc/ui/regressiondialog.ui" ) + "RegressionDialog", "modules/scalc/ui/regressiondialog.ui" ), + mbUnivariate(true), + mnNumIndependentVars(1), + mnNumObservations(0), + mbUse3DAddresses(false) { - get(mpLinearCheckBox, "linear-check"); - get(mpLogarithmicCheckBox, "logarithmic-check"); - get(mpPowerCheckBox, "power-check"); + get(mpWithLabelsCheckBox, "withlabels-check"); + get(mpLinearRadioButton, "linear-radio"); + get(mpLogarithmicRadioButton, "logarithmic-radio"); + get(mpPowerRadioButton, "power-radio"); + get(mpConfidenceLevelField, "confidencelevel-spin"); + get(mpCalcResidualsCheckBox, "calcresiduals-check"); + get(mpErrorMessage, "error-message"); + mpWithLabelsCheckBox->SetToggleHdl(LINK(this, ScRegressionDialog, CheckBoxHdl)); + mpConfidenceLevelField->SetModifyHdl(LINK(this, ScRegressionDialog, NumericFieldHdl)); + mpCalcResidualsCheckBox->SetToggleHdl(LINK(this, ScRegressionDialog, CheckBoxHdl)); } ScRegressionDialog::~ScRegressionDialog() @@ -96,9 +173,13 @@ bool ScRegressionDialog::Close() void ScRegressionDialog::dispose() { - mpLinearCheckBox.disposeAndClear(); - mpLogarithmicCheckBox.disposeAndClear(); - mpPowerCheckBox.disposeAndClear(); + mpWithLabelsCheckBox.disposeAndClear(); + mpLinearRadioButton.disposeAndClear(); + mpLogarithmicRadioButton.disposeAndClear(); + mpPowerRadioButton.disposeAndClear(); + mpConfidenceLevelField.disposeAndClear(); + mpCalcResidualsCheckBox.disposeAndClear(); + mpErrorMessage.disposeAndClear(); ScStatisticsTwoVariableDialog::dispose(); } @@ -112,131 +193,509 @@ ScRange ScRegressionDialog::ApplyOutput(ScDocShell* pDocShell) AddressWalkerWriter aOutput(mOutputAddress, pDocShell, mDocument, formula::FormulaGrammar::mergeToGrammar( formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv)); FormulaTemplate aTemplate(mDocument); - aTemplate.autoReplaceUses3D(false); + aTemplate.autoReplaceUses3D(mbUse3DAddresses); + + // max col of our output should account for + // 1. constant term column, + // 2. mnNumIndependentVars columns + // 3. Actual Y column + // 4. Predicted Y column + // 5. Residual Column + SCCOL nOutputMaxCol = mOutputAddress.Col() + mnNumIndependentVars + 3; + + ScRange aXDataRange(GetDataRange(mVariable1Range)); + ScRange aYDataRange(GetDataRange(mVariable2Range)); + + aTemplate.autoReplaceRange("%VARIABLE1_RANGE%", aXDataRange); + aTemplate.autoReplaceRange("%VARIABLE2_RANGE%", aYDataRange); + size_t nRegressionIndex = GetRegressionTypeIndex(); + ScRegType eRegType = static_cast<ScRegType>(nRegressionIndex); + bool bTakeLogX = eRegType == ScRegType::LOGARITHMIC || eRegType == ScRegType::POWER; + + WriteRawRegressionResults(aOutput, aTemplate, nRegressionIndex); + WriteRegressionStatistics(aOutput, aTemplate); + WriteRegressionANOVAResults(aOutput, aTemplate); + WriteRegressionEstimatesWithCI(aOutput, aTemplate, bTakeLogX); + if (mpCalcResidualsCheckBox->IsChecked()) + WritePredictionsWithResiduals(aOutput, aTemplate, nRegressionIndex); + + ScAddress aMaxAddress(aOutput.mMaximumAddress); + aMaxAddress.SetCol(std::max(aMaxAddress.Col(), nOutputMaxCol)); + return ScRange(aOutput.mMinimumAddress, aMaxAddress); +} - std::unique_ptr<DataRangeIterator> pVariable1Iterator; - if (mGroupedBy == BY_COLUMN) - pVariable1Iterator.reset(new DataRangeByColumnIterator(mVariable1Range)); - else - pVariable1Iterator.reset(new DataRangeByRowIterator(mVariable1Range)); +bool ScRegressionDialog::InputRangesValid() +{ + if (!mVariable1Range.IsValid()) + { + mpErrorMessage->SetText(ScResId(STR_MESSAGE_XINVALID_RANGE)); + return false; + } - std::unique_ptr<DataRangeIterator> pVariable2Iterator; - if (mGroupedBy == BY_COLUMN) - pVariable2Iterator.reset(new DataRangeByColumnIterator(mVariable2Range)); - else - pVariable2Iterator.reset(new DataRangeByRowIterator(mVariable2Range)); + if (!mVariable2Range.IsValid()) + { + mpErrorMessage->SetText(ScResId(STR_MESSAGE_YINVALID_RANGE)); + return false; + } - aTemplate.autoReplaceRange("%VARIABLE1_RANGE%", pVariable1Iterator->get()); - aTemplate.autoReplaceRange("%VARIABLE2_RANGE%", pVariable2Iterator->get()); + if (!mOutputAddress.IsValid()) + { + mpErrorMessage->SetText(ScResId(STR_MESSAGE_INVALID_OUTPUT_ADDR)); + return false; + } - aOutput.writeBoldString(ScResId(STR_REGRESSION)); - aOutput.newLine(); - aOutput.newLine(); - aOutput.push(); + { + double fConfidenceLevel = mpConfidenceLevelField->GetValue(); + if ( fConfidenceLevel <= 0.0 || fConfidenceLevel >= 100.0 ) + { + mpErrorMessage->SetText(ScResId(STR_MESSAGE_INVALID_CONFIDENCE_LEVEL)); + return false; + } + } - // REGRESSION MODEL - aOutput.writeString(ScResId(STR_LABEL_REGRESSION_MODEL)); - aOutput.nextRow(); + mVariable1Range.PutInOrder(); + mVariable2Range.PutInOrder(); - // RSQUARED - aOutput.writeString(ScResId(STR_LABEL_RSQUARED)); - aOutput.nextRow(); + bool bGroupedByColumn = mGroupedBy == BY_COLUMN; - // Standard Error - aOutput.writeString(ScResId(STRID_CALC_STD_ERROR)); - aOutput.nextRow(); + bool bYHasSingleDim = ( + (bGroupedByColumn && + mVariable2Range.aStart.Col() == mVariable2Range.aEnd.Col()) || + (!bGroupedByColumn && + mVariable2Range.aStart.Row() == mVariable2Range.aEnd.Row())); - aOutput.nextRow(); + if (!bYHasSingleDim) + { + if (bGroupedByColumn) + mpErrorMessage->SetText(ScResId(STR_MESSAGE_YVARIABLE_MULTI_COLUMN)); + else + mpErrorMessage->SetText(ScResId(STR_MESSAGE_YVARIABLE_MULTI_ROW)); + return false; + } - // Slope - aOutput.writeString(ScResId(STR_LABEL_SLOPE)); - aOutput.nextRow(); + bool bWithLabels = mpWithLabelsCheckBox->IsChecked(); - // Intercept - aOutput.writeString(ScResId(STR_LABEL_INTERCEPT)); - aOutput.nextRow(); + size_t nYObs = lcl_GetNumRowsColsInRange(mVariable2Range, bGroupedByColumn); + size_t nNumXVars = lcl_GetNumRowsColsInRange(mVariable1Range, !bGroupedByColumn); + mbUnivariate = nNumXVars == 1; + // Observation count mismatch check + if (lcl_GetNumRowsColsInRange(mVariable1Range, bGroupedByColumn) != nYObs) + { + if (mbUnivariate) + mpErrorMessage->SetText(ScResId(STR_MESSAGE_UNIVARIATE_NUMOBS_MISMATCH)); + else + mpErrorMessage->SetText(ScResId(STR_MESSAGE_MULTIVARIATE_NUMOBS_MISMATCH)); + return false; + } + + mnNumIndependentVars = nNumXVars; + mnNumObservations = bWithLabels ? nYObs - 1 : nYObs; + + mbUse3DAddresses = mVariable1Range.aStart.Tab() != mOutputAddress.Tab() || + mVariable2Range.aStart.Tab() != mOutputAddress.Tab(); + + mpErrorMessage->SetText(""); - aOutput.nextRow(); + return true; +} + +size_t ScRegressionDialog::GetRegressionTypeIndex() +{ + if (mpLinearRadioButton->IsChecked()) + return 0; + if (mpLogarithmicRadioButton->IsChecked()) + return 1; + return 2; +} - size_t nVariable1Size = pVariable1Iterator->size(); +ScRange ScRegressionDialog::GetDataRange(const ScRange& rRange) +{ + if (!mpWithLabelsCheckBox->IsChecked()) + return rRange; - OUString sFormula; + ScRange aDataRange(rRange); if (mGroupedBy == BY_COLUMN) - sFormula = "=INDEX(%VARIABLE1_RANGE%; %VAR1_CELL_INDEX%; 1)"; + aDataRange.aStart.IncRow(1); else - sFormula = "=INDEX(%VARIABLE1_RANGE%; 1; %VAR1_CELL_INDEX%)"; + aDataRange.aStart.IncCol(1); + + return aDataRange; +} + +OUString ScRegressionDialog::GetVariableNameFormula(bool bXVar, size_t nIndex, bool bWithLog) +{ + if (bXVar && nIndex == 0) + return "=\"" + ScResId(STR_LABEL_INTERCEPT) + "\""; - for (size_t i = 0; i < nVariable1Size; i++) + if (mpWithLabelsCheckBox->IsChecked()) { - aTemplate.setTemplate(sFormula); - aTemplate.applyNumber("%VAR1_CELL_INDEX%", i + 1); - aOutput.writeFormula(aTemplate.getTemplate()); - aOutput.nextRow(); + ScAddress aAddr(bXVar ? mVariable1Range.aStart : mVariable2Range.aStart); + if (mGroupedBy == BY_COLUMN) + aAddr.IncCol(nIndex - 1); + else + aAddr.IncRow(nIndex - 1); + + ScRefFlags eAddrFlag = mbUse3DAddresses ? ScRefFlags::ADDR_ABS_3D : ScRefFlags::ADDR_ABS; + return bWithLog ? OUString("=CONCAT(\"LN(\";" + + aAddr.Format(eAddrFlag, mDocument, mDocument->GetAddressConvention()) + ";\")\")") : + OUString("=" + aAddr.Format(eAddrFlag, mDocument, mDocument->GetAddressConvention())); } - aOutput.reset(); + OUString aDefaultVarName; + + if (bXVar) + aDefaultVarName = "X" + OUString::number(nIndex); + else + aDefaultVarName = "Y"; + + return bWithLog ? OUString("=\"LN(" + aDefaultVarName + ")\"") : + OUString("=\"" + aDefaultVarName + "\""); +} + +OUString ScRegressionDialog::GetXVariableNameFormula(size_t nIndex, bool bWithLog) +{ + assert(nIndex <= mnNumIndependentVars); + return GetVariableNameFormula(true, nIndex, bWithLog); +} - bool aEnabledRegressionTypes[3]; +OUString ScRegressionDialog::GetYVariableNameFormula(bool bWithLog) +{ + return GetVariableNameFormula(false, 1, bWithLog); +} - aEnabledRegressionTypes[0] = mpLinearCheckBox->IsChecked(); - aEnabledRegressionTypes[1] = mpLogarithmicCheckBox->IsChecked(); - aEnabledRegressionTypes[2] = mpPowerCheckBox->IsChecked(); +void ScRegressionDialog::WriteRawRegressionResults(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate, + size_t nRegressionIndex) +{ + rOutput.writeBoldString(ScResId(STR_REGRESSION)); + rOutput.newLine(); + // REGRESSION MODEL + rOutput.writeString(ScResId(STR_LABEL_REGRESSION_MODEL)); + rOutput.nextColumn(); + rOutput.writeString(ScResId(constRegressionModel[nRegressionIndex])); + rOutput.newLine(); + rOutput.newLine(); + + rOutput.writeString(ScResId(STR_LINEST_RAW_OUTPUT_TITLE)); + rOutput.newLine(); + rOutput.push(); + rTemplate.setTemplate(constTemplateLINEST[nRegressionIndex]); + rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1 + mnNumIndependentVars, 5); + // Add LINEST result components to template + // 1. Add ranges for coeffients and standard errors for indep. vars and the intercept. + // Note that these two are in the reverse order(m_n, m_n-1, ..., m_1, b) w.r.t what we expect. + rTemplate.autoReplaceRange("%COEFFICIENTS_REV_RANGE%", ScRange(rOutput.current(), rOutput.current(mnNumIndependentVars))); + rTemplate.autoReplaceRange("%SERRORSX_REV_RANGE%", ScRange(rOutput.current(0, 1), rOutput.current(mnNumIndependentVars, 1))); + + // 2. Add R-squared and standard error for y estimate. + rTemplate.autoReplaceAddress("%RSQUARED_ADDR%", rOutput.current(0, 2)); + rTemplate.autoReplaceAddress("%SERRORY_ADDR%", rOutput.current(1, 2)); + + // 3. Add F statistic and degrees of freedom + rTemplate.autoReplaceAddress("%FSTATISTIC_ADDR%", rOutput.current(0, 3)); + rTemplate.autoReplaceAddress("%DoFRESID_ADDR%", rOutput.current(1, 3)); + + // 4. Add regression sum of squares and residual sum of squares + rTemplate.autoReplaceAddress("%SSREG_ADDR%", rOutput.current(0, 4)); + rTemplate.autoReplaceAddress("%SSRESID_ADDR%", rOutput.current(1, 4)); + + rOutput.push(0, 4); + rOutput.newLine(); +} - sal_Int16 nColumn = 0; +void ScRegressionDialog::WriteRegressionStatistics(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate) +{ + rOutput.newLine(); + rOutput.writeString(ScResId(STR_LABEL_REGRESSION_STATISTICS)); + rOutput.newLine(); - for (size_t nRegressionIndex = 0; nRegressionIndex < SAL_N_ELEMENTS(aEnabledRegressionTypes); ++nRegressionIndex) + const char* aMeasureNames[] = { - if (!aEnabledRegressionTypes[nRegressionIndex]) - continue; + STR_LABEL_RSQUARED, + STRID_CALC_STD_ERROR, + STR_LABEL_XVARIABLES_COUNT, + STR_OBSERVATIONS_LABEL, + STR_LABEL_ADJUSTED_RSQUARED + }; - aOutput.nextColumn(); - nColumn += 1; + OUString aMeasureFormulas[] = + { + "=%RSQUARED_ADDR%", + "=%SERRORY_ADDR%", + "=" + OUString::number(mnNumIndependentVars), + "=" + OUString::number(mnNumObservations), + "=1 - (1 - %RSQUARED_ADDR%)*(%NUMOBS_ADDR% - 1)/(%NUMOBS_ADDR% - %NUMXVARS_ADDR% - 1)" + }; + + rTemplate.autoReplaceAddress("%NUMXVARS_ADDR%", rOutput.current(1, 2)); + rTemplate.autoReplaceAddress("%NUMOBS_ADDR%", rOutput.current(1, 3)); + + for (size_t nIdx = 0; nIdx < SAL_N_ELEMENTS(aMeasureNames); ++nIdx) + { + rOutput.writeString(ScResId(aMeasureNames[nIdx])); + rOutput.nextColumn(); + rTemplate.setTemplate(aMeasureFormulas[nIdx]); + rOutput.writeFormula(rTemplate.getTemplate()); + rOutput.newLine(); + } +} + +void ScRegressionDialog::WriteRegressionANOVAResults(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate) +{ + rOutput.newLine(); + rOutput.writeString(ScResId(STR_LABEL_ANOVA)); + rOutput.newLine(); - // REGRESSION MODEL - aOutput.writeString(ScResId(constRegressionModel[nRegressionIndex])); - aOutput.nextRow(); + const size_t nColsInTable = 6; + const size_t nRowsInTable = 4; + OUString aTable[nRowsInTable][nColsInTable] = + { + { + "", + ScResId(STR_ANOVA_LABEL_DF), + ScResId(STR_ANOVA_LABEL_SS), + ScResId(STR_ANOVA_LABEL_MS), + ScResId(STR_ANOVA_LABEL_F), + ScResId(STR_ANOVA_LABEL_SIGNIFICANCE_F) + }, + { + ScResId(STR_REGRESSION), + "=%NUMXVARS_ADDR%", + "=%SSREG_ADDR%", + "=%SSREG_ADDR% / %DoFREG_ADDR%", + "=%FSTATISTIC_ADDR%", + "=FDIST(%FSTATISTIC_ADDR% ; %DoFREG_ADDR% ; %DoFRESID_ADDR%)" + }, + { + ScResId(STR_LABEL_RESIDUAL), + "=%DoFRESID_ADDR%", + "=%SSRESID_ADDR%", + "=%SSRESID_ADDR% / %DoFRESID_ADDR%", + "", + "" + }, + { + ScResId(STR_ANOVA_LABEL_TOTAL), + "=%DoFREG_ADDR% + %DoFRESID_ADDR%", + "=%SSREG_ADDR% + %SSRESID_ADDR%", + "", + "", + "" + } + }; + + rTemplate.autoReplaceAddress("%DoFREG_ADDR%", rOutput.current(1, 1)); + + // Cell getter lambda + std::function<CellValueGetter> aCellGetterFunc = [&aTable](size_t nRowIdx, size_t nColIdx) -> const OUString& + { + return aTable[nRowIdx][nColIdx]; + }; - // RSQUARED - aTemplate.setTemplate(constTemplateRSQUARED[nRegressionIndex]); - aOutput.writeMatrixFormula(aTemplate.getTemplate()); - aTemplate.autoReplaceAddress("%RSQUARED%", aOutput.current()); - aOutput.nextRow(); + // Cell writer lambda + std::function<CellWriter> aCellWriterFunc = [&rOutput, &rTemplate] + (const OUString& rContent, size_t /*nRowIdx*/, size_t /*nColIdx*/) + { + if (!rContent.isEmpty()) + { + if (rContent.startsWith("=")) + { + rTemplate.setTemplate(rContent); + rOutput.writeFormula(rTemplate.getTemplate()); + } + else + rOutput.writeString(rContent); + } + }; - // Standard Error - aTemplate.setTemplate(constTemplatesSTDERR[nRegressionIndex]); - aOutput.writeMatrixFormula(aTemplate.getTemplate()); - aTemplate.autoReplaceAddress("%STD_ERROR%", aOutput.current()); - aOutput.nextRow(); + WriteTable(aCellGetterFunc, nRowsInTable, nColsInTable, rOutput, aCellWriterFunc); - aOutput.nextRow(); + // User given confidence level + rOutput.newLine(); + rOutput.writeString(ScResId(STR_LABEL_CONFIDENCE_LEVEL)); + rOutput.nextColumn(); + rOutput.writeString(OUString::number(mpConfidenceLevelField->GetValue() / 100.0)); + rTemplate.autoReplaceAddress("%CONFIDENCE_LEVEL_ADDR%", rOutput.current()); + rOutput.newLine(); +} - // Slope - aTemplate.setTemplate(constTemplatesSLOPE[nRegressionIndex]); - aOutput.writeMatrixFormula(aTemplate.getTemplate()); - aTemplate.autoReplaceAddress("%A%", aOutput.current()); - aOutput.nextRow(); +// Write slopes, intercept, their standard errors, t-statistics, p-value, confidence intervals +void ScRegressionDialog::WriteRegressionEstimatesWithCI(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate, + bool bTakeLogX) +{ + rOutput.newLine(); + SCROW nLastRow = rOutput.current(0, 1 + mnNumIndependentVars).Row(); + + // Coefficients & Std.Errors ranges (column vectors) in this table (yet to populate). + rTemplate.autoReplaceRange("%COEFFICIENTS_RANGE%", + ScRange(rOutput.current(1, 1), + rOutput.current(1, 1 + mnNumIndependentVars))); + rTemplate.autoReplaceRange("%SLOPES_RANGE%", // Excludes the intercept + ScRange(rOutput.current(1, 2), + rOutput.current(1, 1 + mnNumIndependentVars))); + rTemplate.autoReplaceAddress("%INTERCEPT_ADDR%", rOutput.current(1, 1)); + rTemplate.autoReplaceRange("%SERRORSX_RANGE%", + ScRange(rOutput.current(2, 1), + rOutput.current(2, 1 + mnNumIndependentVars))); + // t-Statistics range in this table (yet to populate) + rTemplate.autoReplaceRange("%TSTAT_RANGE%", + ScRange(rOutput.current(3, 1), + rOutput.current(3, 1 + mnNumIndependentVars))); + + const size_t nColsInTable = 7; + const size_t nRowsInTable = 2; + OUString aTable[nRowsInTable][nColsInTable] = + { + { + "", + ScResId(STR_LABEL_COEFFICIENTS), + ScResId(STRID_CALC_STD_ERROR), + ScResId(STR_LABEL_TSTATISTIC), + ScResId(STR_P_VALUE_LABEL), - // Intercept - aTemplate.setTemplate(constTemplatesINTERCEPT[nRegressionIndex]); - aOutput.writeMatrixFormula(aTemplate.getTemplate()); - aTemplate.autoReplaceAddress("%B%", aOutput.current()); - aOutput.nextRow(); + "=CONCAT(\"" + ScResId(STR_LABEL_LOWER) + + " \" ; INT(%CONFIDENCE_LEVEL_ADDR% * 100) ; \"%\")", - aOutput.nextRow(); + "=CONCAT(\"" + ScResId(STR_LABEL_UPPER) + + " \" ; INT(%CONFIDENCE_LEVEL_ADDR% * 100) ; \"%\")", + }, - for (size_t i = 0; i < nVariable1Size; i++) + // Following are matrix formulas of size numcols = 1, numrows = (mnNumIndependentVars + 1) { - aTemplate.setTemplate(constRegressionFormula[nRegressionIndex]); - aTemplate.applyAddress("%ADDRESS%", aOutput.current(-nColumn), false); - aOutput.writeFormula(aTemplate.getTemplate()); + "", + // This puts the coefficients in the reverse order compared to that in LINEST output. + "=INDEX(%COEFFICIENTS_REV_RANGE%; 1 ; " + OUString::number(nLastRow + 2) + " - ROW())", + // This puts the standard errors in the reverse order compared to that in LINEST output. + "=INDEX(%SERRORSX_REV_RANGE%; 1 ; " + OUString::number(nLastRow + 2) + " - ROW())", + // t-Statistic + "=%COEFFICIENTS_RANGE% / %SERRORSX_RANGE%", + // p-Value + "=TDIST(ABS(%TSTAT_RANGE%) ; %DoFRESID_ADDR% ; 2 )", + // Lower limit of confidence interval + "=%COEFFICIENTS_RANGE% - %SERRORSX_RANGE% * " + "TINV(1 - %CONFIDENCE_LEVEL_ADDR% ; %DoFRESID_ADDR%)", + // Upper limit of confidence interval + "=%COEFFICIENTS_RANGE% + %SERRORSX_RANGE% * " + "TINV(1 - %CONFIDENCE_LEVEL_ADDR% ; %DoFRESID_ADDR%)" + } + }; - aOutput.nextRow(); + // Cell getter lambda + std::function<CellValueGetter> aCellGetterFunc = [&aTable](size_t nRowIdx, size_t nColIdx) -> const OUString& + { + return aTable[nRowIdx][nColIdx]; + }; + + // Cell writer lambda + size_t nNumIndependentVars = mnNumIndependentVars; + std::function<CellWriter> aCellWriterFunc = [&rOutput, &rTemplate, nNumIndependentVars] + (const OUString& rContent, size_t nRowIdx, size_t /*nColIdx*/) + { + if (!rContent.isEmpty()) + { + if (rContent.startsWith("=")) + { + rTemplate.setTemplate(rContent); + if (nRowIdx == 0) + rOutput.writeFormula(rTemplate.getTemplate()); + else + rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, 1 + nNumIndependentVars); + } + else + rOutput.writeString(rContent); } + }; + + WriteTable(aCellGetterFunc, nRowsInTable, nColsInTable, rOutput, aCellWriterFunc); + + // Go back to the second row and first column of the table to + // fill the names of variables + intercept + rOutput.push(0, -1); - aOutput.resetRow(); + for (size_t nXvarIdx = 0; nXvarIdx <= mnNumIndependentVars; ++nXvarIdx) + { + rOutput.writeFormula(GetXVariableNameFormula(nXvarIdx, bTakeLogX)); + rOutput.newLine(); } - return ScRange(aOutput.mMinimumAddress, aOutput.mMaximumAddress); +} + +// Re-write all observations in group-by column mode with predictions and residuals +void ScRegressionDialog::WritePredictionsWithResiduals(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate, + size_t nRegressionIndex) +{ + bool bGroupedByColumn = mGroupedBy == BY_COLUMN; + rOutput.newLine(); + rOutput.push(); + + // Range of X variables with rows as observations and columns as variables. + ScRange aDataMatrixRange(rOutput.current(0, 1), rOutput.current(mnNumIndependentVars - 1, mnNumObservations)); + rTemplate.autoReplaceRange("%XDATAMATRIX_RANGE%", aDataMatrixRange); + + // Write X variable names + for (size_t nXvarIdx = 1; nXvarIdx <= mnNumIndependentVars; ++nXvarIdx) + { + // Here we write the X variables without any transformation(LN) + rOutput.writeFormula(GetXVariableNameFormula(nXvarIdx, false)); + rOutput.nextColumn(); + } + rOutput.reset(); + + // Write the X data matrix + rOutput.nextRow(); + OUString aDataMatrixFormula = bGroupedByColumn ? OUString("=%VARIABLE1_RANGE%") : OUString("=TRANSPOSE(%VARIABLE1_RANGE%)"); + rTemplate.setTemplate(aDataMatrixFormula); + rOutput.writeMatrixFormula(rTemplate.getTemplate(), mnNumIndependentVars, mnNumObservations); + + // Write predicted values + rOutput.push(mnNumIndependentVars, -1); + rOutput.writeString(ScResId(STR_LABEL_PREDICTEDY)); + rOutput.nextRow(); + rTemplate.setTemplate(constRegressionFormula[nRegressionIndex]); + rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, mnNumObservations); + rTemplate.autoReplaceRange("%PREDICTEDY_RANGE%", ScRange(rOutput.current(), rOutput.current(0, mnNumObservations - 1))); + + // Write actual Y + rOutput.push(1, -1); + rOutput.writeFormula(GetYVariableNameFormula(false)); + rOutput.nextRow(); + OUString aYVectorFormula = bGroupedByColumn ? OUString("=%VARIABLE2_RANGE%") : OUString("=TRANSPOSE(%VARIABLE2_RANGE%)"); + rTemplate.setTemplate(aYVectorFormula); + rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, mnNumObservations); + rTemplate.autoReplaceRange("%ACTUALY_RANGE%", ScRange(rOutput.current(), rOutput.current(0, mnNumObservations - 1))); + + // Write residual + rOutput.push(1, -1); + rOutput.writeString(ScResId(STR_LABEL_RESIDUAL)); + rOutput.nextRow(); + rTemplate.setTemplate("=%ACTUALY_RANGE% - %PREDICTEDY_RANGE%"); + rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, mnNumObservations); +} + +// Generic table writer +void ScRegressionDialog::WriteTable(std::function<CellValueGetter>& rCellGetter, + size_t nRowsInTable, size_t nColsInTable, + AddressWalkerWriter& rOutput, + std::function<CellWriter>& rFunc) +{ + for (size_t nRowIdx = 0; nRowIdx < nRowsInTable; ++nRowIdx) + { + for (size_t nColIdx = 0; nColIdx < nColsInTable; ++nColIdx) + { + rFunc(rCellGetter(nRowIdx, nColIdx), nRowIdx, nColIdx); + rOutput.nextColumn(); + } + rOutput.newLine(); + } +} + +IMPL_LINK_NOARG(ScRegressionDialog, CheckBoxHdl, CheckBox&, void) +{ + ValidateDialogInput(); +} + +IMPL_LINK_NOARG(ScRegressionDialog, NumericFieldHdl, Edit&, void) +{ + ValidateDialogInput(); } /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/StatisticsTwoVariableDialog.cxx b/sc/source/ui/StatisticsDialogs/StatisticsTwoVariableDialog.cxx index 0fa8c96f0333..85fff5b57ad2 100644 --- a/sc/source/ui/StatisticsDialogs/StatisticsTwoVariableDialog.cxx +++ b/sc/source/ui/StatisticsDialogs/StatisticsTwoVariableDialog.cxx @@ -199,11 +199,7 @@ void ScStatisticsTwoVariableDialog::SetReference( const ScRange& rReferenceRange } } - // Enable OK if all ranges are set. - if (mVariable1Range.IsValid() && mVariable2Range.IsValid() && mOutputAddress.IsValid()) - mpButtonOk->Enable(); - else - mpButtonOk->Disable(); + ValidateDialogInput(); } IMPL_LINK_NOARG( ScStatisticsTwoVariableDialog, OkClicked, Button*, void ) @@ -246,6 +242,8 @@ IMPL_LINK_NOARG( ScStatisticsTwoVariableDialog, GroupByChanged, RadioButton&, vo mGroupedBy = BY_COLUMN; else if (mpGroupByRowsRadio->IsChecked()) mGroupedBy = BY_ROW; + + ValidateDialogInput(); } IMPL_LINK_NOARG( ScStatisticsTwoVariableDialog, RefInputModifyHandler, Edit&, void ) @@ -313,11 +311,7 @@ IMPL_LINK_NOARG( ScStatisticsTwoVariableDialog, RefInputModifyHandler, Edit&, vo } } - // Enable OK if all ranges are set. - if (mVariable1Range.IsValid() && mVariable2Range.IsValid() && mOutputAddress.IsValid()) - mpButtonOk->Enable(); - else - mpButtonOk->Disable(); + ValidateDialogInput(); } void ScStatisticsTwoVariableDialog::CalculateInputAndWriteToOutput() @@ -333,4 +327,18 @@ void ScStatisticsTwoVariableDialog::CalculateInputAndWriteToOutput() pDocShell->PostPaint( aOutputRange, PaintPartFlags::Grid ); } +bool ScStatisticsTwoVariableDialog::InputRangesValid() +{ + return mVariable1Range.IsValid() && mVariable2Range.IsValid() && mOutputAddress.IsValid(); +} + +void ScStatisticsTwoVariableDialog::ValidateDialogInput() +{ + // Enable OK button if all inputs are ok. + if (InputRangesValid()) + mpButtonOk->Enable(); + else + mpButtonOk->Disable(); +} + /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/source/ui/StatisticsDialogs/TableFillingAndNavigationTools.cxx b/sc/source/ui/StatisticsDialogs/TableFillingAndNavigationTools.cxx index 5200e541a8f5..16fc8f3c84f7 100644 --- a/sc/source/ui/StatisticsDialogs/TableFillingAndNavigationTools.cxx +++ b/sc/source/ui/StatisticsDialogs/TableFillingAndNavigationTools.cxx @@ -162,11 +162,15 @@ void AddressWalkerWriter::writeFormula(const OUString& aFormula) new ScFormulaCell(mpDocument, mCurrentAddress, aFormula, meGrammar), true); } -void AddressWalkerWriter::writeMatrixFormula(const OUString& aFormula) +void AddressWalkerWriter::writeMatrixFormula(const OUString& aFormula, SCCOL nCols, SCROW nRows) { ScRange aRange; aRange.aStart = mCurrentAddress; aRange.aEnd = mCurrentAddress; + if (nCols > 1) + aRange.aEnd.IncCol(nCols - 1); + if (nRows > 1) + aRange.aEnd.IncRow(nRows - 1); mpDocShell->GetDocFunc().EnterMatrix(aRange, nullptr, nullptr, aFormula, false, false, OUString(), meGrammar ); } diff --git a/sc/source/ui/inc/RegressionDialog.hxx b/sc/source/ui/inc/RegressionDialog.hxx index 254562e93dbc..722fece34279 100644 --- a/sc/source/ui/inc/RegressionDialog.hxx +++ b/sc/source/ui/inc/RegressionDialog.hxx @@ -15,9 +15,18 @@ class ScRegressionDialog : public ScStatisticsTwoVariableDialog { - VclPtr<CheckBox> mpLinearCheckBox; - VclPtr<CheckBox> mpLogarithmicCheckBox; - VclPtr<CheckBox> mpPowerCheckBox; + VclPtr<CheckBox> mpWithLabelsCheckBox; + VclPtr<RadioButton> mpLinearRadioButton; + VclPtr<RadioButton> mpLogarithmicRadioButton; + VclPtr<RadioButton> mpPowerRadioButton; + VclPtr<FixedText> mpErrorMessage; + VclPtr<NumericField> mpConfidenceLevelField; + VclPtr<CheckBox> mpCalcResidualsCheckBox; + + bool mbUnivariate; + size_t mnNumIndependentVars; + size_t mnNumObservations; + bool mbUse3DAddresses; public: ScRegressionDialog( @@ -32,6 +41,40 @@ protected: void dispose() override; virtual const char* GetUndoNameId() override; virtual ScRange ApplyOutput(ScDocShell* pDocShell) override; + virtual bool InputRangesValid() override; + +private: + + using CellValueGetter = const OUString&(size_t, size_t); + using CellWriter = void(const OUString&, size_t, size_t); + + size_t GetRegressionTypeIndex(); + ScRange GetDataRange(const ScRange& rRange); + OUString GetVariableNameFormula(bool bXVar, size_t nIndex, bool bWithLog); + OUString GetXVariableNameFormula(size_t nIndex, bool bWithLog); + OUString GetYVariableNameFormula(bool bWithLog); + + // Helper methods for writing different parts of regression results. + void WriteRawRegressionResults(AddressWalkerWriter& rOutput, + FormulaTemplate& rTemplate, + size_t nRegressionIndex); + void WriteRegressionStatistics(AddressWalkerWriter& rOutput, + FormulaTemplate& rTemplate); + void WriteRegressionANOVAResults(AddressWalkerWriter& rOutput, + FormulaTemplate& rTemplate); + void WriteRegressionEstimatesWithCI(AddressWalkerWriter& rOutput, + FormulaTemplate& rTemplate, + bool bTakeLogX); + void WritePredictionsWithResiduals(AddressWalkerWriter& rOutput, + FormulaTemplate& rTemplate, + size_t nRegressionIndex); + // Generic table writer + void WriteTable(std::function<CellValueGetter>& rCellGetter, size_t nRowsInTable, + size_t nColsInTable, AddressWalkerWriter& rOutput, + std::function<CellWriter>& rFunc); + + DECL_LINK( CheckBoxHdl, CheckBox&, void ); + DECL_LINK( NumericFieldHdl, Edit&, void ); }; diff --git a/sc/source/ui/inc/StatisticsTwoVariableDialog.hxx b/sc/source/ui/inc/StatisticsTwoVariableDialog.hxx index bcfdcb319940..ceb14e4f4e79 100644 --- a/sc/source/ui/inc/StatisticsTwoVariableDialog.hxx +++ b/sc/source/ui/inc/StatisticsTwoVariableDialog.hxx @@ -43,6 +43,8 @@ protected: virtual ScRange ApplyOutput(ScDocShell* pDocShell) = 0; virtual const char* GetUndoNameId() = 0; + virtual bool InputRangesValid(); + virtual void ValidateDialogInput(); // Widgets VclPtr<FixedText> mpVariable1RangeLabel; diff --git a/sc/source/ui/inc/TableFillingAndNavigationTools.hxx b/sc/source/ui/inc/TableFillingAndNavigationTools.hxx index 9c00bf31e351..073c89d98b0d 100644 --- a/sc/source/ui/inc/TableFillingAndNavigationTools.hxx +++ b/sc/source/ui/inc/TableFillingAndNavigationTools.hxx @@ -85,7 +85,7 @@ public: formula::FormulaGrammar::Grammar eGrammar ); void writeFormula(const OUString& aFormula); - void writeMatrixFormula(const OUString& aFormula); + void writeMatrixFormula(const OUString& aFormula, SCCOL nCols = 1, SCROW nRows = 1); void writeString(const OUString& aString); void writeString(const char* aCharArray); void writeBoldString(const OUString& aString); diff --git a/sc/uiconfig/scalc/ui/regressiondialog.ui b/sc/uiconfig/scalc/ui/regressiondialog.ui index 9db73773cf24..4ef37adfafdd 100644 --- a/sc/uiconfig/scalc/ui/regressiondialog.ui +++ b/sc/uiconfig/scalc/ui/regressiondialog.ui @@ -1,5 +1,5 @@ <?xml version="1.0" encoding="UTF-8"?> -<!-- Generated with glade 3.18.3 --> +<!-- Generated with glade 3.22.1 --> <interface domain="sc"> <requires lib="gtk+" version="3.18"/> <requires lib="LibreOffice" version="1.0"/> @@ -8,6 +8,9 @@ <property name="border_width">6</property> <property name="title" translatable="yes" context="regressiondialog|RegressionDialog">Regression</property> <property name="type_hint">dialog</property> + <child> + <placeholder/> + </child> <child internal-child="vbox"> <object class="GtkBox" id="dialog-vbox1"> <property name="can_focus">False</property> @@ -96,7 +99,7 @@ <object class="GtkLabel" id="variable1-range-label"> <property name="visible">True</property> <property name="can_focus">False</property> - <property name="label" translatable="yes" context="regressiondialog|variable1-range-label">Variable 1 range:</property> + <property name="label" translatable="yes" context="regressiondialog|variable1-range-label">Independent variable(s) (X) range:</property> <property name="use_underline">True</property> <property name="mnemonic_widget">variable1-range-edit</property> <property name="xalign">0</property> @@ -134,7 +137,7 @@ <object class="GtkLabel" id="variable2-range-label"> <property name="visible">True</property> <property name="can_focus">False</property> - <property name="label" translatable="yes" context="regressiondialog|variable2-range-label">Variable 2 range:</property> + <property name="label" translatable="yes" context="regressiondialog|variable2-range-label">Dependent variable (Y) range:</property> <property name="use_underline">True</property> <property name="mnemonic_widget">variable2-range-edit</property> <property name="xalign">0</property> @@ -169,6 +172,20 @@ </packing> </child> <child> + <object class="GtkCheckButton" id="withlabels-check"> + <property name="label" translatable="yes" context="regressiondialog|withlabels-check">Both X and Y ranges have labels</property> + <property name="visible">True</property> + <property name="can_focus">True</property> + <property name="receives_default">False</property> + <property name="halign">start</property> + <property name="draw_indicator">True</property> + </object> + <packing> + <property name="left_attach">0</property> + <property name="top_attach">2</property> + </packing> + </child> + <child> <object class="GtkLabel" id="output-range-label"> <property name="visible">True</property> <property name="can_focus">False</property> @@ -179,7 +196,7 @@ </object> <packing> <property name="left_attach">0</property> - <property name="top_attach">2</property> + <property name="top_attach">3</property> </packing> </child> <child> @@ -192,7 +209,7 @@ </object> <packing> <property name="left_attach">1</property> - <property name="top_attach">2</property> + <property name="top_attach">3</property> </packing> </child> <child> @@ -203,9 +220,15 @@ </object> <packing> <property name="left_attach">2</property> - <property name="top_attach">2</property> + <property name="top_attach">3</property> </packing> </child> + <child> + <placeholder/> + </child> + <child> + <placeholder/> + </child> </object> </child> </object> @@ -321,13 +344,14 @@ <property name="row_spacing">6</property> <property name="column_spacing">12</property> <child> - <object class="GtkCheckButton" id="linear-check"> - <property name="label" translatable="yes" context="regressiondialog|linear-check">Linear Regression</property> + <object class="GtkRadioButton" id="linear-radio"> + <property name="label" translatable="yes" context="regressiondialog|linear-radio">Linear Regression</property> <property name="visible">True</property> <property name="can_focus">True</property> <property name="receives_default">False</property> + <property name="halign">start</property> + <property name="valign">center</property> <property name="use_underline">True</property> - <property name="xalign">0</property> <property name="active">True</property> <property name="draw_indicator">True</property> </object> @@ -337,14 +361,16 @@ </packing> </child> <child> - <object class="GtkCheckButton" id="logarithmic-check"> - <property name="label" translatable="yes" context="regressiondialog|logarithmic-check">Logarithmic Regression</property> + <object class="GtkRadioButton" id="logarithmic-radio"> + <property name="label" translatable="yes" context="regressiondialog|logarithmic-radio">Logarithmic Regression</property> <property name="visible">True</property> <property name="can_focus">True</property> <property name="receives_default">False</property> + <property name="halign">start</property> + <property name="valign">center</property> <property name="use_underline">True</property> - <property name="xalign">0</property> <property name="draw_indicator">True</property> + <property name="group">linear-radio</property> </object> <packing> <property name="left_attach">0</property> @@ -352,14 +378,16 @@ </packing> </child> <child> - <object class="GtkCheckButton" id="power-check"> - <property name="label" translatable="yes" context="regressiondialog|power-check">Power Regression</property> + <object class="GtkRadioButton" id="power-radio"> + <property name="label" translatable="yes" context="regressiondialog|power-radio">Power Regression</property> <property name="visible">True</property> <property name="can_focus">True</property> <property name="receives_default">False</property> + <property name="halign">start</property> + <property name="valign">center</property> <property name="use_underline">True</property> - <property name="xalign">0</property> <property name="draw_indicator">True</property> + <property name="group">linear-radio</property> </object> <packing> <property name="left_attach">0</property> @@ -387,6 +415,104 @@ <property name="position">2</property> </packing> </child> + <child> + <object class="GtkFrame" id="frame-options"> + <property name="visible">True</property> + <property name="can_focus">False</property> + <property name="label_xalign">0</property> + <property name="shadow_type">none</property> + <child> + <object class="GtkAlignment" id="alignment6"> + <property name="visible">True</property> + <property name="can_focus">False</property> + <property name="left_padding">12</property> + <child> + <object class="GtkGrid" id="grid5"> + <property name="visible">True</property> + <property name="can_focus">False</property> + <property name="row_spacing">6</property> + <property name="column_spacing">12</property> + <child> + <object class="GtkLabel" id="label5"> + <property name="visible">True</property> + <property name="can_focus">False</property> + <property name="halign">start</property> + <property name="valign">center</property> + <property name="label" translatable="yes" context="regressiondialog|label5">Confidence level</property> + <property name="mnemonic_widget">confidencelevel-spin</property> + </object> + <packing> + <property name="left_attach">0</property> + <property name="top_attach">0</property> + </packing> + </child> + <child> + <object class="GtkCheckButton" id="calcresiduals-check"> + <property name="label" translatable="yes" context="regressiondialog|calcresiduals-check">Calculate residuals</property> + <property name="visible">True</property> + <property name="can_focus">True</property> + <property name="receives_default">False</property> + <property name="active">True</property> + <property name="draw_indicator">True</property> + </object> + <packing> + <property name="left_attach">0</property> + <property name="top_attach">1</property> + </packing> + </child> + <child> + <object class="GtkSpinButton" id="confidencelevel-spin"> + <property name="visible">True</property> + <property name="can_focus">True</property> + <property name="halign">start</property> + <property name="valign">center</property> + <property name="text" translatable="yes" context="regressiondialog|confidencelevel-spin">0.95</property> + <property name="digits">2</property> + <property name="numeric">True</property> + <property name="value">0.94999999999999996</property> + </object> + <packing> + <property name="left_attach">1</property> + <property name="top_attach">0</property> + </packing> + </child> + <child> + <placeholder/> + </child> + </object> + </child> + </object> + </child> + <child type="label"> + <object class="GtkLabel" id="label4"> + <property name="visible">True</property> + <property name="can_focus">False</property> + <property name="label" translatable="yes" context="regressiondialog|label4">Options</property> + <attributes> + <attribute name="weight" value="bold"/> + </attributes> + </object> + </child> + </object> + <packing> + <property name="expand">False</property> + <property name="fill">True</property> + <property name="position">3</property> + </packing> + </child> + <child> + <object class="GtkLabel" id="error-message"> + <property name="visible">True</property> + <property name="can_focus">False</property> + <property name="wrap">True</property> + <property name="track_visited_links">False</property> + </object> + <packing> + <property name="expand">True</property> + <property name="fill">True</property> + <property name="position">4</property> + </packing> + </child> </object> </child> <action-widgets> |