根据Google表格中其他列的最大值返回单元格内容 [英] Return cell content based on max value of other column in Google Sheets

问题描述

在Google表格中，我有一个带有动态单元格的表，该单元格对来自Google表单的供稿中出现的次数进行计数.左侧的A列中有项目的名称，右侧的列计算的是它们在表单的响应中列出的次数，因此，随着添加更多的响应，此值也会更改.我正在尝试制作一份报告，其中提到每列中哪个实例的实例最多.

In Google Sheets, I have a table with dynamic cells that count the number of occurrences in a feed from Google Forms. At the left, in Column A there are names of items, and the columns at the right count how many times they are listed in the responses from the forms, so this values change as more responses are added. I am trying to make a report that mentions which item had the most instances per column.

我最初使用的公式有效: =INDEX(INDIRECT("A$3:A$6"), 1, MATCH(MAX(B3:B6), B3:B6, 0))

The formula I used initially works: =INDEX(INDIRECT("A$3:A$6"), 1, MATCH(MAX(B3:B6), B3:B6, 0))

但是当我将其移至其他列时，它会出现#NUM错误(参数3的值4超出范围)，例如: =INDEX(INDIRECT("A$3:A$6"), 1, MATCH(MAX(E3:E6), E3:E6, 0))(即使我将公式上的"1"或"0"更改为与列号匹配，错误仍然存​​在)

But when I move it to count on other columns it gives a #NUM error (value 4 of the parameter 3 is out of range), like: =INDEX(INDIRECT("A$3:A$6"), 1, MATCH(MAX(E3:E6), E3:E6, 0)) (even if I change the "1" or the "0" on the formula to match the column number the error persists)

我还发现了两行具有相同值的问题……或者它们都还没有数据，并且全部为"0".

I also found the problem of 2 rows having the same value...or all of them not having data yet and all are "0".

任何人都可以看到错误的确切位置或对重复值的处理方法有什么想法吗? (我考虑过使用IF来命名所有重复的值，除非它们为0，在这种情况下，它可能会显示未找到"消息，但我不确定如何实现它.)

Can anyone see where exactly is the error or have an idea on how to deal with repeated values? (I considered using an IF to name all repeated values unless they are 0, in which case it could display a 'none found' message, but I'm not sure how to implement it.)

我检查了这些:

• https://webapps.stackexchange.com /questions/59293/查找具有单元格范围中最高值的单元格
• Google表格:选择基于单元格的值在另一个单元格上的MAX值

• https://webapps.stackexchange.com/questions/59293/find-cell-with-highest-value-of-a-cell-range
• Google Sheets: selecting cell values based on another cell MAX values

示例表(表1是数据，tab2是公式所在的位置.此问题在第9行中，我尝试了2种可能的公式)在这里:://docs.google.com/spreadsheets/d/1h8_KochnkKnSkwKnSnknSnw2nKnSnw2k1w a>.

Example sheet (Tab1 is data and tab2 is where the formulas are. The ones of this question are in row 9 with 2 possible formulas I tried) is here: https://docs.google.com/spreadsheets/d/1h8_K7nb82hJkDsmUpn6iSkZXhnYEo1NRC2LmRKochgw/edit?usp=sharing .

我尝试了2个公式:

公式1 (B9-给出 #NUM！错误):

Formula 1 (B9 - It gives the #NUM! error):

=INDEX(INDIRECT("A$3:A$6"), 1, MATCH(MAX(B3:B6), B3:B6, 0))

公式2 (C9-给出 #ERROR！):

=INDEX(INDIRECT("A$3:A$6"), 1, IF(C3:C6>0,"MATCH(MAX(C3:C6), C3:C6, "0","NO VALUES"))

第二个公式应该通过仅比较值> 0来解决0值问题，否则显示"NO VALUES"消息.

The second formula was supposed to fix the 0 value problem by only comparing if the values are >0, else show "NO VALUES" message.

推荐答案

粘贴到 B9 中，然后向右拖动:

paste in B9 and drag to the right:

=INDIRECT("A"&MATCH(MAX(B3:B6), B1:B6, 0))

对于 C9 ，您可以使用:

=IFNA(INDIRECT("A"&MATCH(IF(MAX(C3:C6)<>0, MAX(C3:C6), "♥") , C1:C6, 0)), "no values")

这篇关于根据Google表格中其他列的最大值返回单元格内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！