EXCEL获取行中最小值的列标题 [英] EXCEL Get the column header of the lowest value in the row

问题描述

借助

TO

解决方案

  =左(if($ C4 = Min($ C4:$ I4)，$ C $ 3&&" ;,";)& if($ E4 = Min($ C4:$ I4)，$ E $ 3&"&，")&if($ G4 = Min($ C4:$I4)，$ G $ 3"& quot;" if($ I4 = Min($ C4:$ I4)，$ I $ 3"& quot;)，LEN(if($(C4 = Min($ C4:$ I4)I4)，$ E $ 3"& quot;" if($ G4 = Min($ C4:$ I4)，$ G $ 3"& quot;)& if($ I4 = Min($ C4:$ I4)，$ I $ 3&"&，"))-3) 

它很丑...真的很丑.但这确实有效.将该怪兽放到J4中并复制下来.

从描述开始，我认为丑陋和怪异掩盖了它！基本上，整个公式的作用是检查行中的每个数字，以确定其是否为行中的最小值.当该条件为true时，它将在标题行中返回该数字的值，并在"&"做到这一点.然后将其移至下一列并执行相同的检查，并将结果添加到上一列.当数字不是最小值时，它在数字后面加上".结果不算什么.经过所有结果并建立将以&"结尾的字符串.我们将字符串的左侧减去字符串的长度减去3个字符，以删除最后一个"&

概念证明

With the help of teylyn's answer in my previous question, the lowest value in the row are successfully highlighted. How to get the column letter to be displayed in the last column?

FROM

TO

解决方案

=Left(if($C4=Min($C4:$I4),$C$3&" & ","")&if($E4=Min($C4:$I4),$E$3&" & ","")&if($G4=Min($C4:$I4),$G$3&" & ","")&if($I4=Min($C4:$I4),$I$3&" & ",""),LEN(if($C4=Min($C4:$I4),$C$3&" & ","")&if($E4=Min($C4:$I4),$E$3&" & ","")&if($G4=Min($C4:$I4),$G$3&" & ","")&if($I4=Min($C4:$I4),$I$3&" & ",""))-3)

Its ugly...really ugly. But it does work. Put that monstrosity in J4 and copy down.

where to begin with the description, I thought ugly and monstrosity covered it! Basically what the whole formula is doing is checking each number in the row to detemine if it is the minimum in the row. When that condition is true, it returns the value in the header row for that number and tacks on a " & " to it. It then moves on to the next column and performs the same check and adds the results to the previous column. When a number is not the minimum it adds "" to the results which is nothing. After going through all the results and building up a string that will end in "&" we pull the left side of the string, by the length of the string minus 3 characters to remove that last " & ".

Proof of concept

这篇关于EXCEL获取行中最小值的列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！