以每行为单位返回最大值的列标题 [英] Returning the column header of max value on per row basis
问题描述
我有一个电子表格,每月需要根据该月份的表格返回顶级产品。我已经复制了我当前设置的屏幕截图。
I have a spreadsheet whereby on a monthly basis I need to return the top product based on a table for that month. I have copied a screenshot of my current setup below.
我目前正在创建一个额外的列(列 H
),它使用 INDEX
, MATCH
和 MAX
函数返回该行中最高产品的名称。
I am currently doing this by creating an additional column (column H
) which uses the INDEX
, MATCH
and MAX
functions to return the name of the highest product in that row.
然后使用另一个 INDEX
MATCH
作为查询在单元格 K4
和 L4
中返回该月份的值。
I then use another INDEX
MATCH
as a lookup in cells K4
and L4
to return the value for that month.
问题是我的表在每一个月都会展开,因为添加了一个新行,我想知道是否有一种方法将这两个公式组合成一个。所以我需要做的就是在单元格 K3
和 L3
中更新当前和前几个月。我有相同的设置在相当多的表单,所以想要尽可能自动化。
The problem is that my table expands each month as a new row is added and I wanted to find out if there was a way to combine both the formulas into one. So that all I would need to do is update the current and previous months in cells K3
and L3
. I have the same setup across quite a few sheets so want to automate as much as possible.
希望有一些帮助,理想情况下,如果可能的话,不用VBA。
Would love some help, ideally without using VBA if possible at all.
推荐答案
您可以在 K4
中使用此公式,并将其拖动到 L4
:
You could use this formula in K4
and drag it across to L4
:
= INDEX($ B1:$ H1,1,MATCH (INDEX($ B:$ H,MATCH(K2,$ A:$ A,0),0)),INDEX($ B:$ H,MATCH(K2,$ A:$ A,0),0), 0))
这篇关于以每行为单位返回最大值的列标题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!