MINIFS 函数

返回多个区域中满足多个条件的区域中的单元格的最小值.

tip

This function is available since LibreOffice 5.2.


语法

MINIFS(Func_Range; Range1; Criterion[; Range2; Criterion2][; … ; [Range127; Criterion127]])

函数范围 – 必填参数。它是一个单元格区域、命名区域的名称或列/行标签, 包含用于计算最小值的值。

Range1 – required argument. It is a range of cells, a name of a named range, or a label of a column or a row, to which the corresponding criterion is to be applied.

Criterion: A criterion is a single cell Reference, Number or Text. It is used in comparisons with cell contents.

A reference to an empty cell is interpreted as the numeric value 0.

A matching expression can be:

范围2 – 可选。「范围2」和以下所有内容的含义与「区域1」相同。

Criterion2 – Optional. Criterion2 and all the following mean the same as Criterion.

The search supports wildcards or regular expressions. With regular expressions enabled, you can enter "all.*", for example to find the first location of "all" followed by any characters. If you want to search for a text that is also a regular expression, you must either precede every character with a "\" character, or enclose the text into \Q...\E. You can switch the automatic evaluation of wildcards or regular expression on and off in - LibreOffice Calc - Calculate.

warning

When using functions where one or more arguments are search criteria strings that represents a regular expression, the first attempt is to convert the string criteria to numbers. For example, ".0" will convert to 0.0 and so on. If successful, the match will not be a regular expression match but a numeric match. However, when switching to a locale where the decimal separator is not the dot makes the regular expression conversion work. To force the evaluation of the regular expression instead of a numeric expression, use some expression that can not be misread as numeric, such as ".[0]" or ".\0" or "(?i).0".


Func_Range」和「区域1, 区域2...」必须具有相同的大小, 否则该函数返回错误: 502 - 无效的参数。

note

条件之间的逻辑关系可以定义为逻辑「与」(联接)。换句话说, 当且仅当所有给定条件均满足的情况下, 给定「Func_Range」函数范围的相应单元格中的值才会纳入计算。


该函数最多可以有 255 个参数, 这意味着您可以为它们指定 127 组条件范围和条件。

如果单元格内容为 TRUE,则视为 1, 如果内容为 FALSE – 则视为 0 (零)。

note

此功能是「用于 Office 应用程序的开放文档格式 (OpenDocument)」标准 1.2 版本的一部分。(ISO/IEC 26300:2-2015)


示例

考虑下表

A

B

C

1

产品名称

销售

收入

2

铅笔

20

65

3

钢笔

35

85

4

笔记本

20

190

5

图书

17

180

6

笔袋

not

not


warning

在下面的所有示例中, 计算范围都包含行 #6, 该行将被忽略, 因为它包含文本。


简单用法

=MINIFS(B2:B6;B2:B6;"<35")

计算 B2:B6 范围小于或等于 20 的数值的最小值。返回 17。

=MINIFS(C2:C6;B2:B6;">=20";C2:C6;">90")

计算范围 C2:C6 中小于 90,且 B2:B6 范围内对应单元格的数值大于或等于 20 的数值的最小值。返回 190。

使用正则表达式和嵌套函数

=MINIFS(C2:C6;B2:B6;">"&MIN(B2:B6);B2:B6;"<"&MAX(B2:B6))

Calculates the minimum of values of the range C2:C6 that correspond to all values of the range B2:B6 except its minimum and maximum. Returns 65.

=MINIFS(C2:C6;A2:A6;".*book";B2:B6;">"&MIN(B2:B6))

Calculates the minimum of values of the range C2:C6 that correspond to all cells of the A2:A6 range ending with "book" and to all cells of the B2:B6 range except its minimum. Returns 190.

引用单元格作为条件

如果您需要轻松地更改一个条件, 可以考虑在单独的单元格中指定条件, 并在 MINIFS 函数的条件中使用对该单元格的引用。例如, 可以按如下方式重写上述函数:

=MINIFS(C2:C6;A2:A6;".*"&E2;B2:B6;"<"&MAX(B2:B6))

如果 E2 = "book", 函数将返回 180, 因为对单元格的引用将替换为其内容。

打开文件示例:

请支持我们!