打印单元格颜色的 Excel 公式(ColorIndex 或 RGB) [英] Excel formula that prints cell color (ColorIndex or RGB)

问题描述

在 Excel 中是否有一个公式可以检索单元格的颜色索引(或 RGB)?

Is there, in Excel, a Formula that retrieve the ColorIndex (or RGB) of a cell?

我找到了以下功能:

CELL(info_type, the_cell)

记录在此处，但确实如此没有任何关于单元格颜色的参考信息.

documented here, but it does not have any reference information for cell color.

这是一个 color 信息，但对我来说没用.其实是这样描述的:

The is a color info, but it's useless for me. In fact, it is described as follows:

"color" 如果单元格的格式为负值的颜色，则值为 1；否则返回 0(零).

"color" The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).

有什么想法吗?

此外，我发现执行此操作的 VBA 属性是 Cell.Interior.Color 但实际上我没有使用宏，而是使用简单的 Excel 公式.有没有办法用公式模拟 VBA 函数?

Also, I turned out that the VBA property that do this is Cell.Interior.Color but actually I'm not using Macros, but simple Excel formulas. Is there maybe a way to emulate VBA functions with a formula?

推荐答案

这里有一些小功能供您参考.从您的工作表中，按 Alt-F11 进入 VBA 编辑器，插入一个新模块，粘贴以下代码，返回到您的工作表并按名称使用它们，例如 =FillColor(A1)

Here are some small functions for you. From your sheet, press Alt-F11 to reach the VBA editor, insert a new module, paste the below code, go back to your worksheet and use them by their names, like in =FillColor(A1)

前两个是承诺的3-liners"，为字体和背景颜色提供十进制值 - 虽然不是很有用

The first two are the promised "3-liners" giving decimal values for font and background colors - not very useful though

第二对将十进制数转换为RGB并返回格式为N,N,N的字符串

The second pair converts the decimal number to RGB and returns a string of format N, N, N

第三对是数组公式——连续选中3个单元格，输入公式后按Ctrl+Shift+输入获得3个相邻单元格的RGB数值

The third pair are array formulas - select 3 cells in a row, enter the formula and press Ctrl+Shift+Enter to obtain numeric RGB values in 3 neighboring cells

Function FillColor(Target As Range) As Variant
    FillColor = Target.Interior.Color
End Function

Function FontColor(Target As Range) As Variant
    FontColor = Target.Font.Color
End Function

Function FillColorRGB(Target As Range) As Variant
Dim N As Double

    N = Target.Interior.Color
    FillColorRGB = Str(N Mod 256) & ", " & Str(Int(N / 256) Mod 256) & ", " & Str(Int(N / 256 / 256) Mod 256)
End Function

Function FontColorRGB(Target As Range) As Variant
Dim N As Double

    N = Target.Font.Color
    FontColorRGB = Str(N Mod 256) & ", " & Str(Int(N / 256) Mod 256) & ", " & Str(Int(N / 256 / 256) Mod 256)
End Function

Function FillColorRGBArray(Target As Range) As Variant
Dim N As Double, A(3) As Integer

    N = Target.Interior.Color
    A(0) = N Mod 256
    A(1) = Int(N / 256) Mod 256
    A(2) = Int(N / 256 / 256) Mod 256
    FillColorRGBArray = A
End Function

Function FontColorRGBArray(Target As Range) As Variant
Dim N As Double, A(3) As Integer

    N = Target.Font.Color
    A(0) = N Mod 256
    A(1) = Int(N / 256) Mod 256
    A(2) = Int(N / 256 / 256) Mod 256
    FontColorRGBArray = A
End Function

注意事项:更改单元格的颜色不会通过上述函数/公式开始重新计算，因为通常重新着色单元格不会驱动重新计算.您必须使用 Ctrl+Alt+Shift+F9

A word of caution: changing the color of a cell does not start recalculation by the above functions/formulas, as recoloring a cell in general is not supposed to drive recalculation. You have to manually start a full recalculation using Ctrl+Alt+Shift+F9

这篇关于打印单元格颜色的 Excel 公式(ColorIndex 或 RGB)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！