向图表添加颜色条 [英] Adding a color bar to a chart

问题描述

如果我在Excel中创建一个xy-scatter图，并使用

创建颜色条的策略是通过将非常短的行高的单元格的单元格背景染色以创建颜色条，然后使用单元格边框来创建刻度线。刻度标记标签将使用公式，以便颜色条是动态的。由于行的高度很小，一些单元格被合并。所有这些都是由下面的宏自动完成的。然后我们在图表上创建一个颜色条和位置的链接图像。

这是宏。你需要做几个更改取决于你的表。例如，在这个宏中，定义着色（所以不是颜色本身）的数据在 Color Map（Divergent）'！I：I 定义颜色条的最大值和最小值。使用发散数据，你可能实际想要使这些值相同（即选择绝对最大值和负值）。它还假定您的颜色三元组在颜色图表的列 C - E （Divergent）。

在新的空白工作表上执行此巨集：

  Sub MakeColourBar（）
'NB！只能在一张空白纸上运行！ 
'NB！您需要自己手动将最小（开始），最大（结束）放在表单上
 Range（A260）。Value =Start
 Range（D260）。Value == MIN（'Color Map（Divergent）'！I：I）
 Range（A261）。Value =End
 Range（D261）。Value == MAX （D262）。Value ==（D261-D260）/ 8（D261-D260）
范围（A262 
 
 Dim n为整数$​​ b $ bn = 256 
 
'假设在另一张纸上有RGB颜色数据。根据需要更改工作表名称和列
 Dim sheetMap作为工作表
设置sheetMap = Worksheets（Color Map（Divergent））
 Dim row As Integer 
对于row = 1到256 
范围（B& row + 1）.Interior.color = RGB（sheetMap.Range（C& n- row + 1）.Value，sheetMap.Range（D& ; n  -  row + 1）.Value，sheetMap.Range（E& n  -  row + 1）.Value）
下一行
 
 ActiveWindow.DisplayGridlines = False 
 Rows（2：257）RowHeight = 2 
行（1：1）RowHeight = 7.5'这是刻度标记的标签
行（258：258）。 RowHeight = 7.5'这是用于标记标记
列（B：B）。ColumnWidth = 2.14 
 
使用Range（B2：B257）
。 （xlEdgeTop）.Weight = xlMedium 
 .Borders（xlEdgeRight）.Weight = xlMedium 
 .Borders（xlEdgeBottom）.Weight = xlMedium 
。 End with 
 
范围（D1：D6）。合并
范围（D1）。Value == D261
范围（D253：D258）。合并
范围（D253）。Value == D260
'合并刻度线的行
 Dim mark As Integer 
对于mark = 1至8 
范围（C （标记-1）*（256/8）+ 2& ：C& （mark）*（256/8）+ 1）.Merge 
范围（C&（mark  -  1）*（256/8）+ 2）.Borders（xlEdgeTop）.Weight = xlMedium 
'通过合并列D中围绕每个标记标签的10个单元格来创建刻度标记
如果mark> 1然后
范围（D&（标记-1）*（256/8）+2-5&：D& 4）.Merge 
范围（D&（mark-1）*（256/8）+2-5）.Value == D& （标记）*（256/8）+ 2  -  5& + D262
结束如果
下一个标记
范围（C257）。边界（xlBottom）.Weight = xlMedium 
 
列）.ColumnWidth = 0.42 
列（D：D）VerticalAlignment = xlCenter 
列（D：D）Horizo​​ntalAlignment = xlLeft 
 
 End Sub 
 

这会在新工作表中创建以下内容：

注意我已经把这个图片的网格线重新放在这里。但是为了这个工作很好，你必须关闭网格线，这是上面的代码为你。网格线显示颜色条是由一列单元格组成，其背景颜色调整为非常小的行高（列B）。使用合并单元格的单元格边框（列C）创建刻度标记，刻度标记标记也使用以刻度标记（列D）为中心的合并单元格。

新图表的单元格 B3：D258 ，然后在图表上粘贴图表作为链接图像：

在图表上，调整绘图区域大小以在图表区域右侧显示一些空白区域：

然后最后调整您的链接图片大小确保你保持长宽比恒定！），并将其定位在这个空白的空间。然后，您可以调整新颜色栏工作表 D 列的字体大小，以获得与图表上匹配的数字大小。

最终结果：

If I create an x-y-scatter plot in Excel and use Coloring each point of a chart based on data using sequential or divergent color scales to colour the points according to some data, how can I add a colour bar to the chart to show the colour scale? For example like MATLAB's colorbar.

解决方案

For this answer, I am assuming you used the last method in this answer to colour your data. In other words, somewhere on your sheet, you have a a list of RGB triples over 3 columns, 1 colour per row. So something like columns C - D in this screenshot:

The strategy for creating the colour bar is to create it on a blank sheet by colouring cell backgrounds of cells with very short row height to make the colour bar and then using cell border to make the tick marks. The tick mark labels will use formulae so that the colour bar is dynamic. Some cells are merged due to the tiny row height. All of this is automated by the macro that follows. We then create a linked image of the colour bar and position that over our chart.

This is the macro. You need to make a couple of changes depending on your sheet. For example, in this macro that data that defined the colouring (so not the colours themselves) is in the range Colour Map (Divergent)'!I:I which you need to define the max and min of your colour bar. With divergent data you might actually want to make these values the same (i.e. choose the absolute maximum and the negative of that). It also assumes that your colour triples are in columns C - E of the sheet Colour Map (Divergent).

Run this macro on a new blank sheet:

Sub MakeColourBar()
    'NB!!! Only run this on a blank sheet!
    'NB!!! You need to put the min (Start), max (End) on the sheet yourself manually
    Range("A260").Value = "Start"
    Range("D260").Value = "=MIN('Colour Map (Divergent)'!I:I)"
    Range("A261").Value = "End"
    Range("D261").Value = "=MAX('Colour Map (Divergent)'!I:I)"
    Range("A262").Value = "Step"
    Range("D262").Value = "=(D261-D260)/8"

    Dim n as integer
    n = 256

    'This assumes there are RGB colour data on another sheet. Change the sheet name and columns below as needed
    Dim sheetMap As Worksheet
    Set sheetMap = Worksheets("Colour Map (Divergent)")
    Dim row As Integer
    For row = 1 To 256
        Range("B" & row + 1).Interior.color = RGB(sheetMap.Range("C" & n - row + 1).Value, sheetMap.Range("D" & n - row + 1).Value, sheetMap.Range("E" & n - row + 1).Value)
    Next row

    ActiveWindow.DisplayGridlines = False
    Rows("2:257").RowHeight = 2
    Rows("1:1").RowHeight = 7.5 'This is for the tick mark labels
    Rows("258:258").RowHeight = 7.5 'This is for the tick mark labels
    Columns("B:B").ColumnWidth = 2.14

    With Range("B2:B257")
        .Borders(xlEdgeTop).Weight = xlMedium
        .Borders(xlEdgeRight).Weight = xlMedium
        .Borders(xlEdgeBottom).Weight = xlMedium
        .Borders(xlEdgeLeft).Weight = xlMedium
    End With

    Range("D1:D6").Merge
    Range("D1").Value = "=D261"
    Range("D253:D258").Merge
    Range("D253").Value = "=D260"
    'Merge rows for tick marks
    Dim mark As Integer
    For mark = 1 To 8
        Range("C" & (mark - 1) * (256 / 8) + 2 & ":C" & (mark) * (256 / 8) + 1).Merge
        Range("C" & (mark - 1) * (256 / 8) + 2).Borders(xlEdgeTop).Weight = xlMedium
        'Make the tick mark labels by merging the 10 cells in column D that center around each tick label
        If mark > 1 Then
            Range("D" & (mark - 1) * (256 / 8) + 2 - 5 & ":D" & (mark - 1) * (256 / 8) + 2 + 4).Merge
            Range("D" & (mark - 1) * (256 / 8) + 2 - 5).Value = "=D" & (mark) * (256 / 8) + 2 - 5 & " + D262"
        End If
    Next mark
    Range("C257").Borders(xlBottom).Weight = xlMedium

    Columns("C:C").ColumnWidth = 0.42
    Columns("D:D").VerticalAlignment = xlCenter
    Columns("D:D").HorizontalAlignment = xlLeft

End Sub

This creates the following in your new sheet:

Note I have put the grid lines back in for this image just to illustrate what is going on here. But for this to work nicely you must turn the gridlines off, which the above code does for you. The grid lines show how the colour bar is just made up of a column of cells with their background colours adjusted with very small row height (Column B). The tickmarks are made using cell borders of merged cells (column C) and the tick mark labels also use merged cells centred around the tick marks (column D).

Now highlight cells B3:D258 of the new sheet, copy and then on the sheet with your chart paste as a linked image:

On your chart, resize the plot area to show some white space on the right of your chart area:

And then finally resize your linked image (making sure you keep the aspect ration constant!) and position it in this white space. You can then adjust the font size of column D of you new colour bar sheet to get the number sizes to match those on your chart.

The final result:

这篇关于向图表添加颜色条的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！