如何在Excel中的字母数字数据数组中查找最大值和最小值? [英] How to find max and min in an alphanumeric data array in Excel?

问题描述

我有以下类型的数据

A1          B1              C1              D1
II Cm2 447  I Phy Hn 60     II Cm2 457      I Phy Hn 70
II Cm2 448  I Phy Hn 61     II Cm2 458      I Phy Hn 71
II Cm2 449  I Phy Hn 62     II Cm2 459      I Phy Hn 72
II Cm2 450  I Phy Hn 63     II Cm2 460      I Phy Hn 73
II Cm2 451  I Phy Hn 64     II Cm2 461      I Phy Hn 74
II Cm2 452  I Phy Hn 65     II Cm2 462      I Phy Hn 75
II Cm2 453  I Phy Hn 66     II Cm2 463      I PHY ml 76
II Cm2 454  I Phy Hn 67     II Cm2 464      I PHY ml 77
II Cm2 455  I Phy Hn 68     II Cm2 465      I PHY ml 78
II Cm2 456  I Phy Hn 69     II Cm2 466      I PHY ml 79

需要找到与" II Cm2 "，" I Phy Hn "和" I PHY ml "相对应的最小值和最大值

请帮助.

我正在寻找的解决方案

Grid           Min      Max
II Cm2         447      466
I Phy Hn        60       75
I Phy ml        76       79

我已经尝试使用VLOOKUP，RIGHT，INDEX等互联网上的几种选项，但是都没有给我想要的答案.我希望不使用辅助列的解决方案.如果需要使用辅助列，请提出一个解决方案，而无需修改我的cols A:D.

对于Min，我有一个解决方案MIN(VLOOKUP("II Cm2"，$ AE $ 4:$ AF $ 171,2,0)，但它需要一个从单列提取的数字的帮助列我的原始数据.

也欢迎使用宏/脚本的解决方案.

解决方案

如果您只需要从范围中提取最小/最大数，请在单元格G2中使用此公式并将其拖动到您在其中输入的值的底部范围F2:F5. =MIN(IF(ISNUMBER(SUBSTITUTE($A$2:$D$500,$F2,"")+0),SUBSTITUTE($A$2:$D$500,$F2,"")+0))您必须使用CTRL+SHIFT+ENTER组合输入它，因为它是数组公式.对于Max，只需在公式中替换Min.

I have the following kind of data

A1          B1              C1              D1
II Cm2 447  I Phy Hn 60     II Cm2 457      I Phy Hn 70
II Cm2 448  I Phy Hn 61     II Cm2 458      I Phy Hn 71
II Cm2 449  I Phy Hn 62     II Cm2 459      I Phy Hn 72
II Cm2 450  I Phy Hn 63     II Cm2 460      I Phy Hn 73
II Cm2 451  I Phy Hn 64     II Cm2 461      I Phy Hn 74
II Cm2 452  I Phy Hn 65     II Cm2 462      I Phy Hn 75
II Cm2 453  I Phy Hn 66     II Cm2 463      I PHY ml 76
II Cm2 454  I Phy Hn 67     II Cm2 464      I PHY ml 77
II Cm2 455  I Phy Hn 68     II Cm2 465      I PHY ml 78
II Cm2 456  I Phy Hn 69     II Cm2 466      I PHY ml 79

Need to find Minimum and Maximum corresponding to 'II Cm2', 'I Phy Hn' and 'I PHY ml'

Please Help.

The solution I am looking at

Grid           Min      Max
II Cm2         447      466
I Phy Hn        60       75
I Phy ml        76       79

EDIT: I have tried several options available over internet, using VLOOKUP, RIGHT, INDEX,... But none gives me intended answer. I would prefer a solution without using helper columns. If need to use helper columns, please suggest a solution without requiring modification of my cols A:D.

For Min I have a solution MIN(VLOOKUP("II Cm2",$AE$4:$AF$171,2,0), but it requires a helper column of extracted numbers from a single column of my original data.

Solutions using macro/script also welcome.

解决方案

If you need to extract only Min/Max number from range, please use this formula in cell G2 and drag it to the bottom of values you entered in range F2:F5. =MIN(IF(ISNUMBER(SUBSTITUTE($A$2:$D$500,$F2,"")+0),SUBSTITUTE($A$2:$D$500,$F2,"")+0)) you must enter it using CTRL+SHIFT+ENTER combination since it it array formula. For Max just replace Min in formula.

这篇关于如何在Excel中的字母数字数据数组中查找最大值和最小值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！