Excel公式比较两列的颜色并获取具有琥珀色的列的数据 [英] Excel formula to compare two columns with colour and get the data of the column which has Amber colour

问题描述

我有两列 AA 和 AB 列，它们用不同的颜色填充，我的公式应该比较 AA 和 AB 列并找出哪一列具有琥珀色并在 J 列中显示该列的值

I have two columns AA and AB columns which are filled with different colours, my formula should compare AA and AB column and find out which column have the Amber colour and display the value of that column in J column

我尝试从 AB 中提取值并在 J 列中更新它，但无法将 AA 和 AB 列与颜色进行比较并基于该值提取值.

I have tried to extract the value from AB and update it in the J column, but not able to compare AA and AB column with colour and based on that extract the value.

=INDEX($1:$1048576,I11,28)

推荐答案

你可能需要 VBA 来解决这个问题，所以也许 UDF 会帮助你(很快写的):

You probably need VBA for this, so maybe an UDF would help you out (something quickly written):

Function AMBER(RNG1 As Range, RNG2 As Range) As String

If RNG1.Interior.Color = 48895 Then AMBER = RNG1.Value
If RNG2.Interior.Color = 48895 Then AMBER = RNG2.Value

End Function

调用 J1 像:

=AMBER(AA1,AB1)

往下拉..

这篇关于Excel公式比较两列的颜色并获取具有琥珀色的列的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！