Номера диаграмм в Excel со строками, смешанными в

У меня есть некоторые данные, которые я вставляю в диаграмму и форматирование. Существуют некоторые бизнес-правила, в которых некоторые данные являются "Защищенными", как в примере. Проблема, когда графическое выражение со словом "Защищенный" (или любое другое слово) превосходит графики, указывающие в нижней части оси X. Я бы хотел, чтобы график выглядел как 2-й график, но я бы хотел избежать графического отображения скрытого столбца, потому что я думаю, что это смутит моих опытных пользователей.

Любые мысли или идеи оценены!

Ответ 1

При построении диаграммы вручную:

Выберите точку данных
На ленте формата выберите "Выбор формата"
В поле "Форматировать точку данных" выберите "Линия" и выберите "Нет линии".
Выберите следующий пункт данных (соответствующий 2013).
В поле "Форматировать точку данных" выберите "Линия" и выберите "Нет линии".

Вот мои результаты...

Небольшой бит 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

Альтернативный подход

Создайте диаграмму рассеяния с несколькими рядами, разделенную "оскорбительными" строками, и отформатируйте, чтобы они выглядели как одна серия...

У этого есть недостатки:

Вероятнее всего, смущает конечный пользователь
Требуется много серий, если у вас много данных с "Защищенным", разбросанным по всему

Ответ 2

Пока я согласен с ответом @OldUgly, вы можете просто добавить еще один столбец рядом с данными, если вы считаете, что это будет понятно.

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

Это должно сделать трюк.

Ответ 3

Я как бы потрошен, что не мог понять большую часть этого, похоже, такая простая предпосылка. Я сделал несколько обходных решений, но это определенно может сделать с некоторой доработкой;

Я использую функцию VBA под названием Eval:

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 (или независимо от того, что вы написали), и вы получите диапазон данных с любыми значениями "Защищенные", преобразованными в пробелы. Единственное, диаграмма будет строить линию между двумя точками независимо от того, что я не могу сделать диаграмму зазор. Может быть, кто-то с лучшим google-fu, чем я, мог придумать решение?