From dc25f00e499d4ece26fab2e17427583e99e45adf Mon Sep 17 00:00:00 2001 From: Olivier Hallot Date: Sat, 2 Jun 2018 13:11:59 -0300 Subject: Better use of embed for similar contents Save translation work for 'IFS functions Change-Id: I7e9c9fa9d84419f4faf28622be818eb7f2fe6d2a Reviewed-on: https://gerrit.libreoffice.org/55211 Tested-by: Jenkins Reviewed-by: Olivier Hallot (cherry picked from commit 3e5bd012ebefd160ff4d7070b6e1794e44d91ae5) Reviewed-on: https://gerrit.libreoffice.org/55448 Tested-by: Olivier Hallot --- source/text/scalc/01/ex_data_stat_func.xhp | 17 +++++++++++++++++ source/text/scalc/01/func_averageifs.xhp | 24 +++++++++--------------- source/text/scalc/01/func_countifs.xhp | 18 +++++++++--------- source/text/scalc/01/func_maxifs.xhp | 21 ++++++--------------- source/text/scalc/01/func_minifs.xhp | 21 ++++++--------------- source/text/scalc/01/func_sumifs.xhp | 22 ++++++---------------- source/text/shared/optionen/01060500.xhp | 2 ++ 7 files changed, 55 insertions(+), 70 deletions(-) (limited to 'source') diff --git a/source/text/scalc/01/ex_data_stat_func.xhp b/source/text/scalc/01/ex_data_stat_func.xhp index bc87524d9c..a0f2bc98dd 100644 --- a/source/text/scalc/01/ex_data_stat_func.xhp +++ b/source/text/scalc/01/ex_data_stat_func.xhp @@ -119,6 +119,23 @@ +In all examples below, ranges for calculation contain the row #6, which is ignored because it contains text. + +Func_Range; Range1; Criterion1 [ ; Range2; Criterion2 [;...]]) +
+ The logical relation between criteria can be defined as logical AND (conjunction). In other words, if and only if all given criteria are met, a value from the corresponding cell of the given Func_Range is taken into calculation. +
+
+ Func_Range and Range1, Range2... must have the same size, otherwise the function returns err:502 - Invalid argument. +
+
+ Criterion1 – required argument. A string expression representing a logical condition or a cell reference to such string expression. The expression can contain text, numbers, regular expressions or wildcards (if enabled in calculation options). + Range2 – Optional. Range2 and all the following mean the same as Range1. + Criterion2 – Optional. Criterion2 and all the following mean the same as Criterion1. +
+
+ The function can have up to 255 arguments, meaning that you can specify 127 criteria ranges and criteria for them. + If a cell contains TRUE, it is treated as 1, if a cell contains FALSE – as 0 (zero).
diff --git a/source/text/scalc/01/func_averageifs.xhp b/source/text/scalc/01/func_averageifs.xhp index a0a94c2923..6a38f4e4d8 100644 --- a/source/text/scalc/01/func_averageifs.xhp +++ b/source/text/scalc/01/func_averageifs.xhp @@ -27,22 +27,16 @@ Returns the arithmetic mean of all cells in a range that satisfy given multiple criteria. The AVERAGEIFS function sums up all the results that match the logical tests and divides this sum by the quantity of selected values. Syntax -AVERAGEIFS(Average_range; Criterion_range1; Criterion1 [; Criterion_range2; Criterion2 [; ...]]) -Average_range – required argument. It is a range of cells, a name of a named range or a label of a column or a row containing values for calculating the mean. -Criterion_range1 – required argument. It is a range of cells, a name of a named range or a label of a column or a row containing values for finding the corresponding criterion. -Criterion1 – required argument. A condition in the form of expression or a cell reference to expression that defines what cells should be used to calculate the mean. The expression can contain text, numbers, regular expressions (if enabled in calculation options) or wildcards (if enabled in calculation options). -Criterion_range2 – Optional. Criterion_range2 and all the following mean the same as Criterion_range1. -Criterion2 – Optional. Criterion2 and all the following mean the same as Criterion1. -The logical relation between criteria can be defined as logical AND (conjunction). In other words, if and only if all given criteria are met, a value from the corresponding cell of the given Average_range is taken into calculation of the mean.
-The Criterion needs to be a string expression, in particular, the Criterion needs to be enclosed in quotation marks ("Criterion") with the exception of the names of functions, cell references and the operator of a string concatenation (&).
-The operators equal to (=), not equal to (<>), greater than (>), greater than or equal to (>=), less than (<), and less than or equal to (<=) can be used in criterion arguments for comparison of numbers.
-The function can have up to 255 arguments, meaning that you can specify 127 criteria ranges and criteria for it. -If a cell in a range of values for calculating the mean is empty or contains text, the function AVERAGEIFS ignores this cell.
-If a cell contains TRUE, it is treated as 1, if a cell contains FALSE – as 0 (zero).
-If the whole range is empty, contains only text or all values of the range do not satisfy the condition (or any combination of those), the function returns the #DIV/0! error.
-If the range of values for calculating the mean and any range for finding criterion have unequal sizes, the function returns err:502. +AVERAGEIFS() +Func_range – required argument. It is a range of cells, a name of a named range or a label of a column or a row containing values for calculating the mean. + + + + + + + -In all examples below, ranges for average calculation contain the row #6, but it is ignored, because it contains text. Simple usage =AVERAGEIFS(B2:B6;B2:B6;">=20") Calculates the average for values of the range B2:B6 that are greater than or equal to 20. Returns 25, because the fifth row does not meet the criterion. diff --git a/source/text/scalc/01/func_countifs.xhp b/source/text/scalc/01/func_countifs.xhp index bb0280dc42..8a46e0f592 100644 --- a/source/text/scalc/01/func_countifs.xhp +++ b/source/text/scalc/01/func_countifs.xhp @@ -30,16 +30,16 @@ Syntax COUNTIFS(Range1; Criterion1 [; Range2; Criterion2 [; ...]]) Range1 – required argument. It is a range of cells, a name of a named range or a label of a column or a row containing values for counting and finding the corresponding criterion. -Criterion1 – required argument. A condition in the form of expression or a cell reference to expression that defines what cells should be used for counting. The expression can contain text, numbers, regular expressions (if enabled in calculation options) or wildcards (if enabled in calculation options). -Range2 – Optional. Range2 and all the following mean the same as Range1. -Criterion2 – Optional. Criterion2 and all the following mean the same as Criterion1. -The logical relation between criteria can be defined as logical AND (conjunction). In other words, if and only if all given criteria are met, a row or a column is taken into counting.
-The Criterion needs to be a string expression, in particular, the Criterion needs to be enclosed in quotation marks ("Criterion") with the exception of the names of functions, cell references and the operator of a string concatenation (&).
-The operators equal to (=), not equal to (<>), greater than (>), greater than or equal to (>=), less than (<), and less than or equal to (<=) can be used in criterion arguments for comparison of numbers.
-The function can have up to 500 arguments, meaning that you can specify 250 pairs of ranges and criteria. + + + +Range1, Range2, ... and Criterion1, Criterion2, ... must have the same size, otherwise the function returns err:502 - Invalid argument. + + + + -If a cell contains TRUE, it is treated as 1, if a cell contains FALSE – as 0 (zero).
-If ranges for arguments Range and Criterion have unequal sizes, the function returns err:502. + Simple usage =COUNTIFS(B2:B6;">=20") diff --git a/source/text/scalc/01/func_maxifs.xhp b/source/text/scalc/01/func_maxifs.xhp index 238f9cfb4b..43c1ac083e 100644 --- a/source/text/scalc/01/func_maxifs.xhp +++ b/source/text/scalc/01/func_maxifs.xhp @@ -28,25 +28,16 @@ Syntax -MAXIFS( Max_Range ; Criterion_range1 ; Criterion1 [ ; Criterion_range2 ; Criterion2 [;...]]) +MAXIFS() -Max_Range – required argument. A range of cells, a name of a named range or a label of a column or a row containing values for calculating the maximum. -Criterion_range1 – required argument. A range of cells, a named range or a label of a column or a row containing values for finding the corresponding criterion. -Criterion1 – required argument. A string expression of the conditon that defines what cells should be used to calculate the maximum. The expression can contain text, numbers, regular expressions (if enabled in calculation options) or wildcards (if enabled in calculation options). -Criterion_range2 – Optional. Criterion_range2 and all the following mean the same as Criterion_range1. -Criterion2 – Optional. Criterion2 and all the following mean the same as Criterion1. -Max_Range and Criterion_range1, Criterion_range2... must have the same size, otherwise the function returns err:502 - Invalid argument. - -The logical relation between criteria can be defined as logical AND (conjunction). In other words, if and only if all given criteria are met, a value from the corresponding cell of the given Max_Range is taken into calculation of the maximum. - -The function can have up to 255 arguments, meaning that you can specify 127 criteria ranges and criteria for them. +Func_Range – required argument. A range of cells, a name of a named range or a label of a column or a row containing values for calculating the maximum. + + + + - -If a cell contains TRUE, it is treated as 1, if a cell contains FALSE – as 0 (zero). - -In all examples below, ranges for maximum calculation contain the row #6, but it is ignored, because it contains text. Simple usage =MAXIFS(B2:B6;B2:B6;"<35") diff --git a/source/text/scalc/01/func_minifs.xhp b/source/text/scalc/01/func_minifs.xhp index e09ec40a4d..0f5d54f551 100644 --- a/source/text/scalc/01/func_minifs.xhp +++ b/source/text/scalc/01/func_minifs.xhp @@ -28,25 +28,16 @@ Syntax -MINIFS( Min_Range ; Criterion_range1 ; Criterion1 [ ; Criterion_range2 ; Criterion2 [;...]]) +MINIFS() -Min_Range – required argument. A range of cells, a name of a named range or a label of a column or a row containing values for calculating the minimum. -Criterion_range1 – required argument. A range of cells, a named range or a label of a column or a row containing values for finding the corresponding criterion. -Criterion1 – required argument. A string expression of the conditon that defines what cells should be used to calculate the minimum. The expression can contain text, numbers, regular expressions (if enabled in calculation options) or wildcards (if enabled in calculation options). -Criterion_range2 – Optional. Criterion_range2 and all the following mean the same as Criterion_range1. -Criterion2 – Optional. Criterion2 and all the following mean the same as Criterion1. -Min_Range and Criterion_range1, Criterion_range2... must have the same size, otherwise the function returns err:502 - Invalid argument. - -The logical relation between criteria can be defined as logical AND (conjunction). In other words, if and only if all given criteria are met, a value from the corresponding cell of the given Min_Range is taken into calculation of the minimum. - -The function can have up to 255 arguments, meaning that you can specify 127 criteria ranges and criteria for them. +Func_Range – required argument. A range of cells, a name of a named range or a label of a column or a row containing values for calculating the minimum. + + + + - -If a cell contains TRUE, it is treated as 1, if a cell contains FALSE – as 0 (zero). - -In all examples below, ranges for minimum calculation contain the row #6, but it is ignored, because it contains text. Simple usage =MINIFS(B2:B6;B2:B6;"<35") diff --git a/source/text/scalc/01/func_sumifs.xhp b/source/text/scalc/01/func_sumifs.xhp index 641da17892..cf1c5503fb 100644 --- a/source/text/scalc/01/func_sumifs.xhp +++ b/source/text/scalc/01/func_sumifs.xhp @@ -28,26 +28,16 @@ Syntax -SUMIFS( Sum_Range ; Criterion_range1 ; Criterion1 [ ; Criterion_range2 ; Criterion2 [;...]]) +SUMIFS() -Sum_Range – required argument. It is a range of cells, a name of a named range or a label of a column or a row containing values for calculating the sum. -Criterion_range1 – required argument. It is a range of cells, a name of a named range or a label of a column or a row containing values for finding the corresponding criterion. -Criterion1 – required argument. A condition in the form of expression or a cell reference to expression that defines what cells should be used to calculate the sum. The expression can contain text, numbers, regular expressions (if enabled in calculation options) or wildcards (if enabled in calculation options). -Criterion_range2 – Optional. Criterion_range2 and all the following mean the same as Criterion_range1. -Criterion2 – Optional. Criterion2 and all the following mean the same as Criterion1. -Sum_Range and Criterion_range1, Criterion_range2... must have the same size or the function returns err:502 - Invalid argument. - -The logical relation between criteria can be defined as logical AND (conjunction). In other words, if and only if all given criteria are met, a value from the corresponding cell of the given Sum_Range is taken into calculation of the sum.
-The Criterion needs to be a string expression, in particular, the Criterion needs to be enclosed in quotation marks ("Criterion") with the exception of the names of functions, cell references and the operator of a string concatenation (&).
-The operators equal to (=), not equal to (<>), greater than (>), greater than or equal to (>=), less than (<), and less than or equal to (<=) can be used in criterion arguments for comparison of numbers.
-The function can have up to 255 arguments, meaning that you can specify 127 criteria ranges and criteria for them. +Func_Range – required argument. It is a range of cells, a name of a named range or a label of a column or a row containing values for calculating the sum. + + + + - -If a cell contains TRUE, it is treated as 1, if a cell contains FALSE – as 0 (zero). - -In all examples below, ranges for sum calculation contain the row #6, but it is ignored, because it contains text. Simple usage =SUMIFS(B2:B6;B2:B6;">=20") diff --git a/source/text/shared/optionen/01060500.xhp b/source/text/shared/optionen/01060500.xhp index c7e0941469..3c6f348c59 100644 --- a/source/text/shared/optionen/01060500.xhp +++ b/source/text/shared/optionen/01060500.xhp @@ -193,6 +193,7 @@ If Search criteria = and <> must apply to whole cells is not enabled, the "win" search pattern acts like "*win*". The search pattern can be at any position within the cell when searching with the Calc database functions. Enable whole cell match for spreadsheets that need to be interoperable with Microsoft Excel. +
Enable wildcards in formulas Specifies that wildcards are enabled when searching and also for character string comparisons. This relates to the database functions, and to VLOOKUP, HLOOKUP, MATCH, AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIFS, SUMIF, SUMIFS and SEARCH. @@ -208,6 +209,7 @@ Enable wildcards in formulas for spreadsheets that need to be interoperable with Microsoft Excel. +
Enable regular expressions in formulas -- cgit