英文:
Need Help Filtering for Max Occurring Value before a Date
问题
I am trying to extract the most common occurrence of a value (string) in a particular column before a particular date. I would like to find the most occurring value in cells F1:F that occurred before the date in cell A1. Cell A1 uses a formula to find the start of the workweek.
我试图提取特定列中在特定日期之前的一个值（字符串）的最常见出现次数。我想找到在单元格A1中的日期之前在单元格F1:F中发生的最常见的值。单元格A1使用一个公式来找到工作周的开始。
I have used the following formula, but it is not providing the results that I expected.
我使用了以下公式，但它没有提供我期望的结果。
=UNIQUE(FILTER(F1:F,E1:E<$A$1,COUNTIF(E1:E,E1:E) = MAX(COUNTIF(E1:E,E1:E))))
=UNIQUE(FILTER(F1:F,E1:E<$A$1,COUNTIF(E1:E,E1:E) = MAX(COUNTIF(E1:E,E1:E))))
Here is a screenshot of my Sample Spreadsheet:
这是我的示例电子表格的截图：
The formula in cell C2 is returning the results "Yes" and "No". I want the result to be "No" since there are two occurrences of "Yes" before the date in A1 and four occurrences of "No" before the date in A1.
单元格C2中的公式返回结果是"Yes"和"No"。我希望结果是"No"，因为在A1中的日期之前有两次出现"Yes"，而在A1之前有四次出现"No"。
Can anyone point out the problem with my formula or a better formula to extract the desired information? I have tried tweaking this one every which way but to no avail.
有人能指出我的公式存在什么问题，或者提供一个更好的公式来提取所需的信息吗？我已经尝试了各种方式来调整这个公式，但都没有成功。
英文:
I am trying to extract the most common occurrence of a value (string) in a particular column before a particular date. I would like to find the most occurring value in cells F1:F that occurred before the date in cell A1. Cell A1 uses a formula to find the start of the workweek.
I have used the following formula, but it is not providing the results that I expected.
=UNIQUE(FILTER(F1:F,E1:E<$A$1,COUNTIF(E1:E,E1:E) = MAX(COUNTIF(E1:E,E1:E))))
Here is a screenshot of my Sample Spreadsheet:
The formula in cell C2 is returning the results "Yes" and "No". I want the result to be "No" since there are two occurrences of "Yes" before the date in A1 and four occurrences of "No" before the date in A1.
Can anyone point out the problem with my formula or a better formula to extract the desired information? I have tried tweaking this one every which way but to no avail.
答案1
得分: 1
你可以尝试：
=let(
dates,tocol(E:E,1),
text,tocol(F:F,1),
counts,map(unique(text),lambda(each,counta(filter(text,text=each,dates<$A$1)))),
filter(unique(text),counts=max(counts)))
我们正在对列F中的文本值进行MAP操作（符合日期条件），将其映射到列F中的每个唯一文本值，然后使用此表中的最大值来筛选唯一文本值的列表。
英文:
You can try:
=let(
dates,tocol(E:E,1),
text,tocol(F:F,1),
counts,map(unique(text),lambda(each,counta(filter(text,text=each,dates<$A$1)))),
filter(unique(text),counts=max(counts)))
We are MAPping the count of text values in colF (subject to the date criterion) onto each unique text value in colF, then using the max value in this table to filter the list of unique text values.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论