Excel每5行计数一次模式 [英] Excel counting pattern every 5th rows

问题描述

所以，我有这12万行excel格式的文件.

So, I have this 120k rows in excel with format.

id number description
1    1     apple
2    1     banana
3    1     pineapple
4    1     b
5    1     c
6    6     d
7    6     e
8    6     f
9    6     g
10   6     e

我可以在number列中使用什么公式，使其与上表相同?每隔第5行，我将根据id列第6行的数字更改number列的数目.请注意，数字列现在为空白.

what formula can I use in the number column so that it will be the same as the table above? every 5th rows I will change the number of number column based on number on the 6th row of id column. Note that number column is blank right now.

推荐答案

尽管Enigmativity的解决方案可为您提供所需的结果，但结果会因您从开始列的行而有所不同.这是我建议这样做的方式.

Although Enigmativity's solution gives you the desired result, the results will vary by the row from where you start your column. Here is how i would recommend to do it.

在要从中开始模式的第一个单元格中输入1(在我的情况下为B2). 在下一个单元格(B3)中输入

Enter 1 in the first cell you want to start the pattern from (B2 in my case) . in the next cell(B3) enter

=IF(MOD(ROW()-ROW($B$2),5)=0,B2+5,B2)

，然后将公式复制下来.只要您更新起始单元格地址(B2和$B$2)

and copy the formula down. This should give you the desired pattern as long as you update the starting cell address (B2 and $B$2)

这篇关于Excel每5行计数一次模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！