如何基于二维坐标及其值对单元格进行着色?新的Excel函数

I am new to Excel EVB, I am just a normal excel user but intend to do some pro stuff. I did some google, yet, I have no clue to start with.

我是Excel EVB的新手，我只是一个普通的Excel用户，但是我想做一些专业的东西。我做了一些谷歌，但我还没有开始的线索。

I am trying to create a new excel function that allow user to color the cell based on the X,Y coordinate and its value.

我正在尝试创建一个新的excel函数，允许用户根据X、Y坐标及其值给单元格着色。

Firstly, I have a predefined X,Y mapping (see below Colum A:E) and Cell C3 is my origin point (0,0).

首先，我有一个预定义的X,Y映射(参见Colum a:E)， Cell C3是我的原点(0,0)。

Secondly, I have a X,Y data table which contain value for each of the (X,Y) coordinate.

其次，我有一个包含每个(X,Y)坐标值的X,Y数据表。

Now, I want to color the X,Y map based on the data from the table with multiple condition.

现在，我想根据表格中具有多重条件的数据给X,Y映射上色。

If Value >= 0 AND <=4E-06, color cell (map) = green, cell value = Value

如果值>= 0，<=4E-06, color cell (map) = green, cell Value = Value

If Value >4E-06 AND <=9.9E-06, color cell (map) = blue, cell value = Value

如果值>4 -06和<=9.9E-06，颜色单元(map) =蓝色，单元格值=值。

If Value >9.9E-6, color cell (map) = red, cell value = Value

如果值>9.9E-6，颜色单元格(map) =红色，单元格值=值

Eg. From the table, if X=1, Y=2, Value = 2.2E-05, the cell D1 will be colored in red and the call value will set to 2.2E-05

如。从表中可以看出，如果X=1, Y=2, Value = 2.22 -05，则单元格D1将被标记为红色，调用值将被设置为2.22 -05

The final result will be look like this:

最终的结果是这样的:

Question: How can I create a new excel function based on the above requirement? Any idea and input?

问:如何根据上述要求创建新的excel函数?知道和输入吗?

3 个解决方案

#1

use the below formula to populate the cells

使用下面的公式填充单元格

=SUMPRODUCT(($G$2:$G$26=COLUMN()-3)*($H$2:$H$26=-ROW()+3)*$I$2:$I$26)

Use conditional formatting to get the desired color in the cell.

使用条件格式在单元格中获取所需的颜色。

Formula Explanation as requested:

公式解释要求:

COLUMN()-3 and -ROW()+3 give you the correct (X,Y) pair in each cell.

列()-3和-ROW()+3给出了每个单元格中正确的(X,Y)对。

($G$2:$G$26=COLUMN()-3) checks each cell in the Range G2:G26 and compares to the X value output by COLUMN()-3. this will output and array of TRUE(Where the values match) and FALSE.

($G$2:$G$26=列()-3)检查G2:G26范围内的每个单元格，并与按列()-3输出的X值进行比较。这将输出和数组TRUE(其中值匹配)和FALSE。

($H$2:$H$26=-ROW()+3) checks for the Y values.

($H$2:$H$26=-ROW()+3)检查Y值。

the product of the above two array will result in an array of 1's and 0's (which is equivalent to TRUE and FALSE). There will be only one 1 in this array as there is only one X,Y pair matching.

上述两个数组的乘积将产生一个1和0的数组(它等价于TRUE和FALSE)。这个数组中只有一个1，因为只有一个X,Y对匹配。

This array multiplied( and summed ) by the Values array gives you the final value in the cell.

该数组与值数组相乘(并求和)得到单元格中的最终值。

#2

The approach I would take would be with a sub routine rather than a function. Then iterate down cols G:I with some thing like while not isempty (.cells (intRow,7).value). On each row you could then set the relevant cells colour property using cols G and H coordinates using offsets from the origin. You can flip the direction by multiplying it by -1 if necessary. You could use a private function to return the colour from col I value using a select case. Sorry, not near a computer to write the code but should be easy enough if you know VBA.

我采用的方法是使用子例程而不是函数。然后迭代cols G:我用了一些东西，比如while不是空的。细胞(intRow 7)value)。然后，您可以在每一行上使用cols G和H坐标设置相关的单元格颜色属性，使用原点偏移量。如果有必要，你可以把方向乘以-1。您可以使用一个私有函数使用一个select case返回col I值的颜色。不好意思，不是靠近电脑写代码，但是如果你知道VBA的话应该很简单。

#3

your problem like as Battleship, my solution is:

你的问题就像战舰一样，我的解决方案是:

Sub Battleship()
Dim cc As Range 'set center cell
Dim arrVal() As Variant
Dim lrw As Integer
lrw = ActiveSheet().Range("G2").End(xlDown).Row
arrVal = Range("G2:I" & lrw)
Set cc = Range("C3")

For i = 1 To UBound(arrVal())
    cc.Offset(arrVal(i, 1), arrVal(i, 2)).Select
        Selection.Value = arrVal(i, 3)
    Select Case arrVal(i, 3)
    Case 0 To 0.000004
        Selection.Interior.Color = 5296274
    Case 0.000004 To 0.0000099
        Selection.Interior.Color = 15773696
    Case Is > 0.0000099
        Selection.Interior.Color = 255
    End Select
Next i

End Sub

#1