在excel公式中创建动态单元格块引用

I am at a loss as to how to create a dynamic cell block reference in an Excel formula. As a simple example, if I have 5 id's with scores per block and I needed to rank the scores within each block, what would be the easiest way to create formulas to do this? I am aware that you can create the formulas in the first 5 cells using relative references (i.e. =RANK.EQ(B#, B2:B6)), copy those cells, and paste in the range that highlights the 15 cells.

我不知道如何在Excel公式中创建动态单元格引用。举个简单的例子，如果我有5个id，每个块都有分数，我需要对每个块中的分数进行排序，那么创建公式最简单的方法是什么呢?我知道您可以使用相对引用(即=RANK)在前5个单元格中创建公式。EQ(B#， B2:B6))，复制这些细胞，粘贴在突出显示15个细胞的范围内。

1 个解决方案

#1

If the blocks are 5 rows every time, then you can create ongoing 5-row-blocks in column B using INDEX the following way:

如果块每次是5行，那么您可以使用索引在B列中创建正在进行的5行块:

Formula in helper column F beginning in F2 downwards:

从F2向下的辅助列F中的公式:

=2+INT((ROW(A1)-1)/5)*5

Formula in helper column G beginning in G2 downwards:

由G2向下开始的辅助G列公式:

=2+4+INT((ROW(A1)-1)/5)*5

Formula in C2 downwards:

在C2向下公式:

=RANK(B2,INDEX(B:B,F2):INDEX(B:B,G2))

Formula in D2downwards (without helper columns):

下式(无辅助列):

=RANK(B2,INDEX(B:B,2+INT((ROW(A1)-1)/5)*5):INDEX(B:B,2+4+INT((ROW(A1)-1)/5)*5))

#1