Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Excel - Скрытие данных и признаков на Графике имеющие нулевые значения или ошибку
 
#1
15.12.2021 11:08:54
Добрый день
Прошу помочь с построением динамического графика, в котором, при изменении данных построение исключало нулевые значения или ошибки, а также подбирался нужны диапазон данных

PS ранее была поднята данная тема, название которой "Чтобы графики не воспринимали пустые значения как нули" но данного решения я не нашел
также пробовал использовать форматирование 0,0%;;;
также пробовал Выбрать данные - Скрытые и пустые ячейки
 
 
 
#2
15.12.2021 12:29:28
Вариант макросом.
Код
Option Explicit
'v3
Sub ChangeFirstChart()
    ChangeChart ActiveSheet.ChartObjects(1).Chart
End Sub
    
Sub ChangeChart(ch As Chart)

    Dim sFormula As String
    sFormula = ch.FullSeriesCollection(1).Formula
    If Right(sFormula, 3) = ",1)" Then
        Dim sFormula1 As String
        Dim xBeg As Long
        sFormula1 = Left(sFormula, Len(sFormula) - 3)
        If Right(sFormula1, 1) = ")" Then
            sFormula1 = Left(sFormula1, Len(sFormula1) - 1)
            xBeg = InStrRev(sFormula1, "(")
        Else
            xBeg = InStrRev(sFormula1, ",")
        End If
        sFormula1 = Mid(sFormula1, xBeg + 1)
        
        Dim rn As Range
        Set rn = Range(sFormula1)
        Dim ri As Range
        For Each ri In rn
            Set rn = Range(rn.Cells(1), ri)
        Next
        
        Dim zeroFlag As Boolean
        For Each ri In rn
            If ri.Value = 0 Then
                zeroFlag = True
                Exit For
            End If
        Next
        zeroFlag = True
        If zeroFlag Then
            Dim ru As Range
            For Each ri In rn
                If ri.Value <> 0 Then
                    If ru Is Nothing Then
                        Set ru = ri
                    Else
                        Set ru = Union(ru, ri)
                    End If
                End If
            Next
            
            xBeg = InStr(sFormula, ",")
            sFormula1 = Left(sFormula, xBeg)
            If ru.Areas.Count = 1 Then
                sFormula1 = sFormula1 & "'" & ActiveSheet.Name & "'!" & ru.Offset(0, -1).Address(1, 1, xlA1)
                sFormula1 = sFormula1 & ","
                sFormula1 = sFormula1 & "'" & ActiveSheet.Name & "'!" & ru.Address(1, 1, xlA1)
            Else
                sFormula1 = sFormula1 & "("
                sFormula1 = sFormula1 & "'" & ActiveSheet.Name & "'!" & Replace(ru.Offset(0, -1).Address(1, 1, xlA1), ",", "," & "'" & ActiveSheet.Name & "'!")
                sFormula1 = sFormula1 & "),("
                sFormula1 = sFormula1 & "'" & ActiveSheet.Name & "'!" & Replace(ru.Offset(0, 0).Address(1, 1, xlA1), ",", "," & "'" & ActiveSheet.Name & "'!")
                sFormula1 = sFormula1 & ")"
            End If
            sFormula1 = sFormula1 & ",1)"
            
            ch.FullSeriesCollection(1).Formula = sFormula1
        End If
    End If
End Sub
Изменено: МатросНаЗебре - 15.12.2021 12:46:28
 
 
 
#3
15.12.2021 13:24:59
Ну раз уж макросом, то куда проще просто скрывать строки с нулевыми и ошибочными строками. Тогда они так же будут удалены из графика.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#4
15.12.2021 14:20:39
А просто снять галочку не вариант?
 
 
 
#5
15.12.2021 14:24:16
можно так:
в модуль листа
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B5:B16")) Is Nothing Then
        Макрос6
        Target.Offset(1, 0).Activate
    End If
End Sub

в модуль1
Код
Sub Макрос6()
Dim Rng As Range, r As Range
For Each r In Range("B4:B16")
    If Not r = 0 And Not r = "" Then If Rng Is Nothing Then Set Rng = Union(r, r.Offset(0, -1)) Else Set Rng = Union(Rng, r, r.Offset(0, -1))
Next r
If Not Rng Is Nothing Then
    ActiveSheet.ChartObjects("Диаграмма 1").Activate
    ActiveChart.SetSourceData Source:=Range(Rng.Address)
End If
End Sub

или просто всё в модуль листа, кому как нравится
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range, r As Range
    If Not Intersect(Target, Range("B5:B16")) Is Nothing Then
        For Each r In Range("B4:B16")
            If Not r = 0 And Not r = "" Then If Rng Is Nothing Then Set Rng = Union(r, r.Offset(0, -1)) Else Set Rng = Union(Rng, r, r.Offset(0, -1))
        Next r
        If Not Rng Is Nothing Then
            ActiveSheet.ChartObjects("Диаграмма 1").Activate
            ActiveChart.SetSourceData Source:=Range(Rng.Address)
        End If
        Target.Offset(1, 0).Activate
    End If
End Sub
Изменено: Msi2102 - 15.12.2021 14:32:58
 
 
 
#6
15.12.2021 15:05:40
Цитата
написал:
А просто снять галочку не вариант?
каждый раз снимать галочку, для этого я слишком ленив(
если выбираем (например категорию) то месяц где то апрель выпадет, где-то май и постоянно тыкать не то

Цитата
написал:
Вариант макросом.
а есть что-нибудь попроще?)
пробовал вариант через формулу с использованием нд() но не дает нужных результатов

по сути, когда изменяется массив данных и некоторые данные не имеют продаж, то этот период (месяц или день или час) должен скрываться, тем самым скрывать за собой строку, чтобы график этих строк не видел и тогда будет нужный разрыв, а именно не будет отображения на графике или пустого обрыва
 
 
 
#7
15.12.2021 15:07:50
спасибо большое
отлично работает
 
 
 
#8
15.12.2021 15:25:59
Цитата
ignat-erema написал:
а есть что-нибудь попроще?)
самое простое: ставите фильтр на таблицу для диаграммы(можно заранее перед установкой фильтра выделить диапазон строк побольше - с запасом). В фильтре снимаете галочки напротив 0(и при необходимости напротив ошибок). Все. После поступления новых данных на вкладке Данные -Сортировка и фильтр просто жмете кнопку "Повторить".
Убедитесь, что для настроек диаграммы(правая кнопка мыши -Выбрать данные -Скрытые и пустые ячейки) снята галочка с пункта "Показывать данные в скрытых строках и столбцах"
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
Страницы: 1
Наверх