英文:
How to convert range of values to 1 and 0 for use in sumproduct
问题
0 31/12/2020
0 31/12/2021
7 31/12/2022
0 31/12/2023
0 31/12/2024
我有两个类似上表的数据范围，我想使用内置的Sumproduct公式以一种方式来返回非零值的日期（在这种情况下为31/12/2022）。是否有人知道我该如何做到这一点？
我希望能够在不先创建一个从Values范围计算出1和0的第三行/列的情况下实现这一点。
英文:
Values Dates
0 31/12/2020
0 31/12/2021
7 31/12/2022
0 31/12/2023
0 31/12/2024
I have two ranges of data similar to the table above and I would like to use the built-in Sumproduct formula in such a way to return the date of the non-zero value (in this case 31/12/2022). Do anyone know how I can go about this?
I would prefer if this was possible without first creating a third row/column which calculates ones and zeros from the Values range.
答案1
得分: 2
Using <> within SUMPRODUCT:
=SUMPRODUCT(B2:B6*(A2:A6<>0))
这将在列A中有多个非零值时返回意外的结果。
英文:
Using <> within SUMPRODUCT:
=SUMPRODUCT(B2:B6*(A2:A6<>0))
This will return unexpected results if you have more than one nonzero value in column A.
答案2
得分: 0
你知道这个公式=NOT(NOT(A2)) * 1将非零值变为1吗？
就像这样，你可以使用一个带有上述公式的辅助列来实现类似的操作：
值 | 日期 | 值_1_0
------+-----------+----------
0| 31/12/2020| 0
0| 31/12/2021| 0
7| 31/12/2022| 1
0| 31/12/2023| 0
0| 31/12/2024| 0
Sumproduct公式：=SUMPRODUCT(C2:C6,B2:B6)
=> 结果：31/12/2022
英文:
Are you aware that the formula =NOT(NOT(A2)) * 1 turns a non-zero value into 1?
Like this, you can do something like (using a helper column with the mentioned formula):
Values| Dates|Values_1_0
------+-----------+----------
0| 31/12/2020| 0
0| 31/12/2021| 0
7| 31/12/2022| 1
0| 31/12/2023| 0
0| 31/12/2024| 0
Sumproduct formula: =SUMPRODUCT(C2:C6,B2:B6)
=> result : 31/12/2022
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论