查找一行中特定值的最后一次出现 [英] Find last occurrence of specific value in a row

问题描述

我有一张桌子，可以让幻想足球联赛得分。得分最低的一周不包括在总计中。

我可以使用 = min函数[= min（B2：R2）]找到最低分数

我可以使用 = match函数找到第一个实例。 [= MATCH（S2，B2：R2,0）]

然而，我想要一个公式返回最低得分的最新出现

例如，Portia最低分是8，第一次出现是第4周，但我希望返回第11周的最新发生。

解决方案

你也可以这使用 LOOKUP 函数并避免数组公式，例如假设 S2 具有最小值，您可以在 T2 中复制

= LOOKUP（2,1 /（B2：R2 = S2），B $ 1：R $ 1）

这是因为（B2：R2 = S2）返回一个数组 TRUE / FALSE 值，然后将1除以该数组，您将获得一个数组 1 s或＃DIV / 0！错误。当您在该数组中使用 2 作为查找值时，将找不到任何 2 最后一个数字（最后一个）匹配。对应于最后一周的最小值，因此使用顶行作为返回向量表示该公式根据需要返回相应的周号

I have a table that keeps scores from fantasy football league. The lowest scoring week is not included in the total.

I'm able to find the lowest score using the =min function [=min(B2:R2)]

I can find the first instance using the =match function. [=MATCH(S2,B2:R2,0)]

However I would like a formula that returns the latest occurrence of the lowest scoring week.

For example Portia lowest score is 8 the first occurrence is week 4 but I would like it to return the latest occurrence which is week 11

解决方案

You can also do this using LOOKUP function and avoiding array formulas, e.g. assuming S2 has the minimum value you can get the last week with that value with this formula in T2 copied down

=LOOKUP(2,1/(B2:R2=S2),B$1:R$1)

This works because (B2:R2=S2) returns an array of TRUE/FALSE values, and then dividing 1 by that array gives you an array of 1s or #DIV/0! errors. When you use 2 as the lookup value on that array it won't find any 2s so will therefore match with the last number (the last 1) in the array. That corresponds to the last week with your smallest value so using the top row as the "return vector" means that the formula returns the corresponding week number as required

这篇关于查找一行中特定值的最后一次出现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！