I have a spreadsheet that I am putting together for some coworkers who are not tech savvy at all, so it needs to be super easy to use.
我有一个电子表格,我正在为一些技术娴熟的同事组装,所以它需要非常容易使用。
Column A shows each store's rank [formula used is =RANK(C3, C$3:C$16)].
A列显示每个商店的等级[使用的公式是= RANK(C3,C $ 3:C $ 16)]。
Column B shows each store's name [example A, B, C, D, E all the way to N]. This information will not change.
B列显示每个商店的名称[例如A,B,C,D,E一直到N]。此信息不会更改。
Column C shows each store's score. This is the ONLY information people can change as the scores change each week.
C列显示了每个商店的得分。这是人们可以随着每周分数变化而改变的唯一信息。
So the formula in column A looks at the scores in column C and assigns a rank to them, number 1 being the highest score, number 14 being the lowest score.
因此,A栏中的公式会查看C栏中的分数并为其分配排名,1号是最高分,14号是最低分。
Then a few columns over I have it setup to streamline the data so the coworkers don't have to do too much with it (I don't want them to have to click "Sort", I just want it to sort it automatically).
然后在我的几个列上设置简化数据,以便同事不必对它做太多(我不希望他们必须点击“排序”,我只是想让它自动排序) 。
Column E has a list of numbers from 1 to 14, this has no formulas, it's literally just a list of numbers.
E列有一个从1到14的数字列表,它没有公式,它只是一个数字列表。
Column F looks at the number in column E, searches the rankings in column A for the same number and brings the store name over to F [formula used =VLOOKUP(E3,A$3:C$16,2,FALSE)].
列F查看E列中的数字,在A列中搜索相同数字的排名,并将商店名称带到F [使用的公式= VLOOKUP(E3,A $ 3:C $ 16,2,FALSE)]。
Column G shows the corresponding score [formula used =VLOOKUP(F3,B$3:D$16,2,FALSE)].
G栏显示相应的分数[使用的公式= VLOOKUP(F3,B $ 3:D $ 16,2,FALSE)]。
All of this works perfectly. The only data that needs to change is the scores in column C. Column F and G update accordingly. All my coworkers have to do is copy and paste columns E, F and G into an email and send out the update of everyone's scores.
所有这一切都很完美。唯一需要更改的数据是C列中的分数。列F和G相应地更新。我的同事所要做的就是将E,F和G列复制并粘贴到电子邮件中,并发送每个人的分数更新。
It's convenient and easy, until there are duplicate scores.
这是方便和容易的,直到有重复的分数。
If two (or more) stores have the same scores, it just returns #N/A. I know there is a way to have the Rank formula assign decimals to duplicate rankings, but we don't want it to show someone is ranked 3.5 out of 14. I need everyone to be assigned a number between 1 and 14 with no decimals.
如果两个(或更多)商店具有相同的分数,则只返回#N / A.我知道有一种方法可以让Rank公式指定小数来重复排名,但是我们不希望它显示有人在14中排名3.5。我需要为每个人分配1到14之间没有小数的数字。
I have played with so many different formulas out of similar posts, but none of them work for what I need. I found one that was promising, but when I changed someone's score to the exact same as someone else, it showed me the same store twice.
我用类似的帖子玩了很多不同的公式,但没有一个能用于我需要的东西。我找到了一个有希望的,但是当我把某人的分数改为与其他人完全相同的时候,它给我看了两次相同的商店。
Is there anyway to get it to do what I need it to do?
反正有没有让它做我需要它做的事情?
1 个解决方案
#1
5
To get all ranks different use this formula in A3 copied down
要使所有等级不同,请使用A3中的此公式复制下来
=RANK(C3,C$3:C$16)+COUNTIF(C$3:C3,C3)-1
so if you have a 3-way tie for 1st place the first instance will be ranked 1, the second 2, the third 3 etc.
所以,如果你有第三名的三方平局,第一个实例将排名第一,第二个实例排名第一,第三个实例排名第三。
#1
5
To get all ranks different use this formula in A3 copied down
要使所有等级不同,请使用A3中的此公式复制下来
=RANK(C3,C$3:C$16)+COUNTIF(C$3:C3,C3)-1
so if you have a 3-way tie for 1st place the first instance will be ranked 1, the second 2, the third 3 etc.
所以,如果你有第三名的三方平局,第一个实例将排名第一,第二个实例排名第一,第三个实例排名第三。