【原】问：怎么给带颜色的单元格添加序号？

你好，我是刘卓。欢迎来到我的公号，excel函数解析。最近天冷了，大家注意保暖。昨天有人问了我一个问题，怎么给带颜色的单元格添加序号。正好我手头也没什么案例了，今天就来分享下ta这个问题吧。

-01-

具体应用

下图A列是数据源，有些单元格设置为红色底纹。B列是想要的序号结果。题目的要求是：当A列的单元格是红色底纹时，在B列的对应位置输入序号0；当A列的单元格没有设置底纹(默认为白色)时，在B列的对应位置输入的序号要从1递增。

如果你也遇到过类似的问题，可以先自己想想怎么办？

下面先说说我的公式，然后再慢慢讲解。在B1单元格输入公式=IF(ys,0,INDIRECT("r[-1]c",0)+1)，下拉填充。

你可能看到公式里有个ys，这是个什么东西？其实它是一个定义的名称。我们在之前的文章《宏表函数get.cell获取单元格的信息》也说过，想要用函数获取单元格的底纹颜色，只能用宏表函数get.cell。而宏表函数只能在定义名称里使用。

第一步，先获取单元格的底纹颜色。

首先选中B1单元格，然后点击【公式】-【定义名称】，弹出新建名称对话框。在名称里输入ys，在引用位置输入=GET.CELL(63,$A1)，点确定。这样名称就定义好了。用定义名称，一定要注意相对引用的位置关系。

接下来就可以用ys这个名称来获取单元格的底纹颜色了。在B1单元格输入公式=ys，按回车，然后双击填充柄填充。可以看到红色底纹的单元格返回的数字是3（颜色索引值），无填充底纹的单元格返回的数字是0。至此，第一步工作就完成了。

第二步，添加序号。

有了上一步B列的颜色索引值，就可以对其进行判断了。当颜色索引值大于0时，即单元格是红色底纹时，让其返回0；否则，当颜色索引值不大于0时，即单元格无底纹时，让其返回上一个单元格再加1。

有了这个思路就好写公式了，比如我在B2单元格输入公式=IF(ys>0,0,B1+1)，向下填充，发现结果是对的。

可是当公式填充到B1单元格就会出现问题。因为B1单元格已经是第一个单元格了，它的上一个单元格是谁呢？

我们用r1c1的相对引用来解决这个问题，INDIRECT("r[-1]c",0)返回活动单元格的上一个单元格。r[-1]c中的r表示行，c表示列。[]表示相对引用，-1表示活动单元格向上偏移一行。c后面没有列号，表示活动单元格所在的列。

虽然用这种方法还是返回上一个单元格，但是它更为奇特。当在B1单元格输入公式=INDIRECT("r[-1]c",0)时，不但不会出错，还会返回B列的最后一个单元格(B1048576)的值。

也就是说B1单元格的上一个单元格是B1048576。而B1048576基本不会有东西，是空白单元格，所以结果是0。从而解决了第一行不能引用上一个单元格的问题。

按照这个思路我又想了下vba的方法，也挺方便的。

代码如下：

Sub 给带颜色的单元格添加序号() Dim rng As Range, r As Range, n As Integer Set rng = Range("a1:a" & Cells(Rows.Count, 1).End(xlUp).Row) For Each r In rng If r.Interior.ColorIndex > 0 Then r.Offset(0, 1) = 0 n = 0 Else n = n + 1 r.Offset(0, 1) = n End If NextEnd Sub


链接：

https://pan.baidu.com/s/1MDg5pVeb9vv3mt-oYaV3Lw

提取码：bdq7