山寨HeatMap注意事项

来源：互联网发布：ef执行sql语句编辑：程序博客网时间：2024/05/15 02:07

今天翻邮件，翻到一封HeatMap。感受下。

要从空的Scatter开始做，加入X轴和Y轴数据，SeriesName其实可以不用。接下来就是调背景色了。

每个小箭头，都可以选颜色的。

然后最后附上一段宏。可用来把小标签名字都换成行名称替换。

选的DataRange其实是B:C 结果标签居然用的是ColumnA哦。全因为一段VBA。当你要用这段VBA时候，鼠标要选定图哦。

'出自https://support.microsoft.com/zh-cn/kb/213750Sub AttachLabelsToPoints()   'Dimension variables.   Dim Counter As Integer, ChartName As String, xVals As String   ' Disable screen updating while the subroutine is run.   Application.ScreenUpdating = False   'Store the formula for the first series in "xVals".   xVals = ActiveChart.SeriesCollection(1).Formula   'Extract the range for the data from xVals.   xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _      Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))   xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)   Do While Left(xVals, 1) = ","      xVals = Mid(xVals, 2)   Loop   'Attach a label to each data point in the chart.   For Counter = 1 To Range(xVals).Cells.Count     ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _         True      ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _         Range(xVals).Cells(Counter, 1).Offset(0, -1).Value 'So the name must at left side.   Next CounterEnd Sub

另外如果X value 是序列的话，那么X value就直接选行名称，没必要运行宏。所以上图那种ColumnB是按顺序的，DataSource直接A列C列就好了。

0 0