Excel: criação automatizada de intervalos com base no conteúdo de células individuais

Question

Ainda não entrei em macros e é por isso que estou perguntando aqui. Eu tenho uma planilha que eu quero definir gradientes para um conjunto de células diagonais, mas é muito demorado quando você tem mais de 60 colunas. Eu tenho um modelo que tem as células numeradas na diagonal. Aqui está um pequeno exemplo do modelo:

Issocontinuaatualmentepara60,paradireitaeparabaixo.Eupossopreencherosnúmerosfacilmente,masformatá-losnadiagonalcondicionalmentelevarámuitotempo.Euquerosabersehaviaumamaneiradeapenasselecionarumaáreaedizerselecionartodasascélulasnessaáreaquecontenhamomesmonúmeroecriarumintervalodefinidochamadoqualquerquesejaonúmeroeseesseintervalojáexistir,emseguida,adicioneessacélulaaoexistenteintervalo.

Oexemploseriapegartodasascélulasquecontêm"1" dentro do intervalo "main", em seguida, criar o intervalo "_1" e adicionar células a ele. repita até que todas as 60 faixas estejam completas.

Então, eu também precisaria de um modo de usar os intervalos "_1" a "_60" e adicionar facilmente formatação condicional a eles para gradientes de 3 cores para destacar os números maiores em cada intervalo depois de remover os números e substituir por uma fórmula. Eu poderia ter que fazer essa parte manualmente, mas seria muito mais fácil se já houvesse intervalos definidos para cada diagonal, então eu não tenho que ir e selecionar área encontrar célula contendo "5", definir intervalo "_5", então condicionalmente formata para cada número 1-60 (somente 60 atualmente, mas pode acabar sendo maior).

Veja um exemplo de como uma linha com gradiente se parece. Terá gradiente em cada diagonal. Pode acabar sendo 3 cores diferentes para diferenciar facilmente entre cada diagonal, para que possa ver rapidamente o que procura. Exemplo do gradiente:

Eupossofazerosgradientesmanualmenteapósosintervalosseremfeitos.

Euseiqueissoéprovavelmenteimpossível,masgostariadevercomotenhoalgunsdelesparafazerenãogostodepassarhorasapenasformatandocentenasdecélulasmanualmente.

Umavisãogeralsimplistadoqueprecisoé:

forallcellsinsiderange"Main"
[  
    read cell

    if range "-[cell]" exists  
    [  
        add cell to range  
    ]  
    else  
    [  
        define range named "-[cell]"  
        add cell to range  
    ]  
]

Quando todas as células estiverem em um intervalo, gostaria que algo fizesse os gradientes para mim. Algo como "para cada intervalo de -1 a -60, faça o formato condicional de 3 cores".

Eu sei que haverá muito mais do que isso, mas isso é essencialmente tudo que eu preciso.

Deixe-me saber se isso é impossível. Provavelmente é, mas não há mal nenhum em perguntar.

    

Answer 1

Isso demorou um pouco, mas acho que posso ajudá-lo. Eu escrevi três sub-rotinas que podem nomear as diagonais secundárias. O primeiro é o select_diagonal sub

Sub select_diagonal(matriz As Range, m As Integer, name As String)

Dim n As Integer, i As Integer, first As Boolean
Dim diag As Range

n = matriz.Rows.Count
'm must be at most 2n-1 (number of diagonals)
first = True

If m <= n Then
    For i = 0 To m - 1 Step 1
        If first Then
            Set diag = matriz.Item(1).Offset(i, m - i - 1)
            first = False
        Else
            Set diag = Union(diag, matriz.Item(1).Offset(i, m - i - 1))
        End If
    Next i

Else
    For i = (m Mod n) To m - (m Mod n) - 1 Step 1
        If first Then
            Set diag = matriz.Item(1).Offset(i, m - i - 1)
            first = False
        Else
            Set diag = Union(diag, matriz.Item(1).Offset(i, m - i - 1))
        End If
    Next i
End If

ActiveWorkbook.Names.Add name:=name, RefersTo:=diag

End Sub

Ele recebe como parâmetros uma matriz quadrada como um intervalo (seu intervalo de 60x60), um inteiro m (que deve ser no máximo 2 * n-1, o número de diagonais da matriz) e uma string name para name a m-ésima diagonal secundária da matriz.

Existe também o name_range sub, que recebe uma matriz quadrada como um intervalo e faz loop em cada diagonal secundária da matriz e fornece um intervalo nomeado (de acordo com suas instruções, a k-ésima diagonal secundária é denominada " _k ")

Sub name_range(matriz As Range)

    Dim n As Integer, ii As Integer
    n = matriz.Rows.Count

    For ii = 1 To 2 * n - 1
        Call select_diagonal(matriz, ii, "_" & CStr(ii))
    Next ii

End Sub

Finalmente, a sub-rotina que você deve executar é simplesmente o sub principal. Basta selecionar todo o seu alcance e executar esta sub-rotina, para chamar os outros dois que farão o trabalho por você.

Sub main()

    Dim matriz As Range

    Set matriz = Selection

    Call name_range(matriz)

End Sub

Depois disso, basta aplicar a formatação condicional a todos os intervalos. Como você acabou de dizer, você pode aplicar manualmente os gradientes após os intervalos serem feitos. Minha sugestão, no entanto, é aplicá-lo através de um procedimento VBA. Basta especificar os limites de cor e, com os intervalos nomeados já definidos, aplicá-lo a todos os intervalos nomeados.

PS: não consegui encontrar uma maneira de fazer isso sem o VBA. O maior problema aqui é definir os intervalos nomeados para a diagonal secundária e usar o dimensionamento de cores com um intervalo dinâmico ou com alguns critérios. Espero que isso ajude.