I want to know how to find the minimum and maximum value in excel sheets using criteria? I try to use this formula but it's not working. I need specific text if is match it will show minimum or maximum value. For your information, I am using Excel 2013.
我想知道如何使用条件在Excel工作表中找到最小值和最大值?我尝试使用这个公式,但它不起作用。如果匹配,我需要特定的文本,它将显示最小值或最大值。为了您的信息,我使用的是Excel 2013。
4 个解决方案
#1
2
I believe using a pivot table would be simplest, but that said, you can use the following array formulae (Use Ctrl+Shift+Enter instead of Enter):
我相信使用数据透视表是最简单的,但是说,您可以使用以下数组公式(使用Ctrl + Shift + Enter而不是Enter):
=MAX(IF(ISERROR(SEARCH(D1,A2:A6)),MIN(B2:B6),B2:B6))
=MIN(IF(ISERROR(SEARCH(D1,A2:A6)),MAX(B2:B6),B2:B6))
#2
1
Like
=MAX(IF(IFERROR(FIND("Shoaib",$A$2:$A$6),0)>0,$B$2:$B$6))
And
=MIN(IF(IFERROR(FIND("Shoaib",$A$2:$A$6),0)>0,$B$2:$B$6))
Entered as array formulas with Ctrl + Shift + Enter
使用Ctrl + Shift + Enter输入为数组公式
Data
#3
0
For purposes of your reporting, one sensible option here would be to just add a filter on the Name column. Then, just take the max and min of the entire Marks column. This would let you quickly obtain the min or max marks for any person using only the Excel GUI. And it avoids the need to edit a formula for each different name.
出于报告目的,此处的一个明智选择是在“名称”列上添加过滤器。然后,只需获取整个Marks列的最大值和最小值。这样,您只需使用Excel GUI即可快速获取任何人的最小或最大标记。并且它避免了为每个不同名称编辑公式的需要。
#4
0
Input these as ARRAY formula. Means press CTRL+SHIFT+ENTER after putting formula in cell.
输入这些作为ARRAY公式。将公式放入单元格后,按CTRL + SHIFT + ENTER。
=MAX(IF(ISNUMBER(SEARCH("Shoaib",A2:A6)),B2:B6,""))
=MIN(IF(ISNUMBER(SEARCH("Shoaib",A2:A6)),B2:B6,""))
#1
2
I believe using a pivot table would be simplest, but that said, you can use the following array formulae (Use Ctrl+Shift+Enter instead of Enter):
我相信使用数据透视表是最简单的,但是说,您可以使用以下数组公式(使用Ctrl + Shift + Enter而不是Enter):
=MAX(IF(ISERROR(SEARCH(D1,A2:A6)),MIN(B2:B6),B2:B6))
=MIN(IF(ISERROR(SEARCH(D1,A2:A6)),MAX(B2:B6),B2:B6))
#2
1
Like
=MAX(IF(IFERROR(FIND("Shoaib",$A$2:$A$6),0)>0,$B$2:$B$6))
And
=MIN(IF(IFERROR(FIND("Shoaib",$A$2:$A$6),0)>0,$B$2:$B$6))
Entered as array formulas with Ctrl + Shift + Enter
使用Ctrl + Shift + Enter输入为数组公式
Data
#3
0
For purposes of your reporting, one sensible option here would be to just add a filter on the Name column. Then, just take the max and min of the entire Marks column. This would let you quickly obtain the min or max marks for any person using only the Excel GUI. And it avoids the need to edit a formula for each different name.
出于报告目的,此处的一个明智选择是在“名称”列上添加过滤器。然后,只需获取整个Marks列的最大值和最小值。这样,您只需使用Excel GUI即可快速获取任何人的最小或最大标记。并且它避免了为每个不同名称编辑公式的需要。
#4
0
Input these as ARRAY formula. Means press CTRL+SHIFT+ENTER after putting formula in cell.
输入这些作为ARRAY公式。将公式放入单元格后,按CTRL + SHIFT + ENTER。
=MAX(IF(ISNUMBER(SEARCH("Shoaib",A2:A6)),B2:B6,""))
=MIN(IF(ISNUMBER(SEARCH("Shoaib",A2:A6)),B2:B6,""))