在Excel中混合字符串的图表数字

Question

我有一些数据要放到图表中并进行格式化。有一些业务规则，其中一些数据是“受保护的”，就像例子中那样。问题在于，如果在图表中使用单词“Protected”（或任何其他单词），Excel 将绘制指向 X 轴底部的图表。我希望图表看起来像第二张图，但我想避免绘制隐藏列的图表，因为我认为这会使我的高级用户感到困惑。

任何想法或见解都将不胜感激！

- KyleUp

---

它必须有文本吗？还是可以为空白的？ - sous2817

是的，我正在寻找的解决方案最好具有在绘制图表的列中的文本（或者也许一些文档证明无法在该列中使用文本）。 - KyleUp

"protected" 是怎么来的？这是一个公式吗？ - sous2817

这可能不是来自微软文档的内容，但如果Jon Peltier说它无法完成，那么我敢打赌它确实无法完成... - sous2817

“protected”这个词只是文本。在生成Excel文档时填充它。 - KyleUp

除非你能将那个文本更改为a = NA()，否则我感觉你会很倒霉。这里有一个类似的解决方案（再次感谢Jon Peltier在评论中提供的解决方案）：http://superuser.com/questions/480347/how-to-tell-excel-to-ignore-num-errors-in-plotting-a-line-graph - sous2817

Answer 1

当手动构建图表时：

1. 选择数据点
2. 在格式选项卡上，选择“格式设置”
3. 在“格式数据点”中，选择线条，并选择“无线条”。
4. 选择下一个数据点（对应于2013年）。
5. 在“格式数据点”中，选择线条，并选择“无线条”。

这是我的结果...

以下是一小段VBA代码，用于生成图表...

Sub MakeChart()
Dim cell As Range, mySerRng As Range, mySrcRng As Range
Dim mySht As Worksheet, myChrt As Chart
Dim lastRow As Long

Set mySht = Worksheets("Sheet1")
lastRow = mySht.Range("A" & mySht.Rows.Count).End(xlUp).Row

Set mySerRng = mySht.Range(mySht.Cells(1, 2), mySht.Cells(lastRow, 2))
Set mySrcRng = mySht.Range(mySht.Cells(1, 1), mySht.Cells(lastRow, 2))

Set myChrt = mySht.Shapes.AddChart2(-1, xlLine, mySht.Range("C1").Left, mySht.Range("C1").Top).Chart
With myChrt
    .SeriesCollection.Add Source:=mySrcRng, RowCol:=xlColumns, serieslabels:=True, categorylabels:=True, Replace:=True
    For Each cell In mySerRng
        If cell.Value = "Protected" Then
            .SeriesCollection(1).Points(cell.Row - 1).Format.Line.Visible = False
            .SeriesCollection(1).Points(cell.Row).Format.Line.Visible = False
        End If
    Next cell

End With
End Sub

---

替代方法

使用多个系列构建散点图，通过“有问题”的行分隔，并进行格式化，使它们看起来像一个系列...

这样做有以下缺点：

• 可能会更加令人困惑
• 如果您有大量分布在数据中的“Protected”，则需要大量系列

- OldUgly

Answer 2

虽然我同意@OldUgly的答案，但如果您认为这样更容易理解，您也可以在数据旁边添加另一列。

=IF(ISNUMBER($B2),$B2,"")

那应该就可以了。

- Weasemunk

---

这个问题在使用“Protected”时也存在。使用字符串（即使是空字符串），Excel会将其绘制在零点处。我尝试过使用NA()代替""，但结果是Excel只是在前一个和下一个点之间插值线条。使用NA()的隐藏和空单元格设置没有效果。它的行为就像被设置为“连接数据点与线条”。 - KyleUp

如果我理解正确的话，图表会将空字符串填充为0，但对于真正的空单元格则不会。如果是这样的话，您可以按照我提到的方法操作，然后选择您的数据。使用主页->编辑->查找和选择->转到特殊单元格->空白 来查找所有“空白”单元格。然后使用主页->编辑->清除->全部清除来清除所有的单元格数据/格式。 - Weasemunk

Answer 3

我有点失望，因为我无法理解大部分内容，它似乎是一个非常简单的前提。我已经做了一些变通，但肯定需要进一步完善；

我正在使用一个名为Eval的VBA函数：

Public Function Eval(varRange As Range)
Dim varArray() As Variant
varArray = varRange
Dim R As Long
Dim C As Long
For R = 1 To UBound(varArray, 1)
    For C = 1 To UBound(varArray, 2)
        If varArray(R, C) = "Protected" Then
            varArray(R, C) = CVErr(xlErrNA)
        End If
    Next C
Next R
Eval = varArray
End Function

还有一个名为“Chart”的定义范围，其公式如下

=EVAL(INDIRECT("Sheet1!$B$2:$B$"&1+COUNTA(Sheet1!$B$2:$B$31)))

这使得范围成为动态的，但如果需要，您可以使用=EVAL（B2：B6）。

在您的图表中，将系列值范围编辑为Sheet1.xlsm！Chart（或您的工作表叫什么），然后您将获得数据范围，并将“受保护”的任何值转换为空白。唯一的问题是，图表将绘制两点之间的线，无论如何，我无法使图表绘制间隙。也许有比我更好的谷歌技巧的人能够提出解决方案吗？

- AranDG