在动态为表分配排名时使用 XLOOKUP 不会提取正确的数据
我遇到的问题是尝试对使用排名公式生成的表列使用 XLOOKUP。
Rank 列是使用以下公式创建的，因此它会根据可见列动态调整（自动过滤），因为 RANK.AVG 或 EQ 不会考虑这一点。
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Table2[[#All],[Sales]],ROW(Table2[[#All],[Sales]])-MIN(ROW(Table2[[#All],[Sales]])),0,1)),--([@Sales]<Table2[[#All],[Sales]]))
我使用的 XLOOKUP 很简单，
=XLOOKUP(1,Table2[Rank], Table2[Rep])
其想法是顶部的字段将根据可见行显示排名最高的执行者。问题是，在过滤数据时，排名列会按应有的方式进行调整，但 XLOOKUP 无法按我的预期运行。它坚持最初排名第一，甚至做了一些完全奇怪的事情，在区域列中运行过滤器组合时我无法解释。
任何人都可以提出任何建议，我如何更好地构建它，使其以预期的方式运行。任何帮助将不胜感激。
The issue I am having is trying to use XLOOKUP on a table column that has been generated using a ranking formula.
The Rank column is created using the following formula so that it adjusts dynamically depending on the visible columns (auto-filtering) since RANK.AVG or EQ does not take that into account.
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Table2[[#All],[Sales]],ROW(Table2[[#All],[Sales]])-MIN(ROW(Table2[[#All],[Sales]])),0,1)),--([@Sales]<Table2[[#All],[Sales]]))
The XLOOKUP I am using is simple
=XLOOKUP(1,Table2[Rank], Table2[Rep])
The idea is that the field at the top would show the top ranked performer based on the visible rows. The problem is that when filtering the data, the rank column adjusts as it should, but the XLOOKUP does not function as I would expect. It sticks with the originally ranked 1 or even does something complete weird that I can't explain when running combinations of filters in the region column..
Anyone have any advice how I could better structure this to make it function in the intended manner. Any help at all would be appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您需要采用类似的结构来仅考虑可见行，例如：
=LOOKUP(1,QUOTIENT(0,SUBTOTAL(3,OFFSET(INDEX(Table2[Sales],1)),ROW(Table2 [Sales])-MIN(ROW(Table2[Sales])),))*(Table2[Rank]=1)),Table2[Rep])不确定为什么要引用其他公式中的表标题行。
You'll need to employ a similar construction to account for visible rows only, for example:
=LOOKUP(1,QUOTIENT(0,SUBTOTAL(3,OFFSET(INDEX(Table2[Sales],1),ROW(Table2[Sales])-MIN(ROW(Table2[Sales])),))*(Table2[Rank]=1)),Table2[Rep])Not sure why you're referencing the table header row in your other formula.