Tag Archives: Range

Excel – Criando listas e ranges dinâmicos com a fórmula DESLOC


Eis um tópico curioso. Curioso no sentido de, depois que você descobre como fazer, você se pergunta: como consegui viver sem isso até hoje?

Temos que admitir que vivemos com base em listas. E o Excel é o rei das listas. Quando avançamos com nossas planilhas, nossas listas só crescem. Temos listas para tudo! Elas vão desde listas para validação, até listas que preenchem gráficos, ou ao menos que proporcionam o uso do nosso querido PROCV (que faríamos sem ele?). As listas são tudo, são demais!

Só tem um problema. Elas são estáticas.

Quando precisamos atualizá-las, ou seja, inserir um novo item ou valor, qualquer coisa que faça referência a ela, uma fórmula, gráfico, etc, mesmo uma lista nomeada, precisa ser atualizada. Nem o PROCV escapa dessa.A saída é, ou sair atualizando tudo, ou fazer um bocado (mas nem tanto) código em VBA, ou…. USAR A FÓRMULA/FUNÇÃO DESLOC!

Função o quê? Vamos por partes.

Explicando a Função DESLOC

Não vou me ater em cada detalhe da função, já que a documentação oficial da Excel explica isso muito bem: http://support.microsoft.com/kb/324991/pt-br.

Vamos ao que interessa para atingir o objetivo do artigo. Tirando as fórmulas matriciais, a função DESLOC é uma das mais interessantes que o Excel oferece. Isso porque ela tem um comportamento diferente da maioria das fórmulas.

A função DESLOC, ao invés de retornar uma valor fixo, retorna um RANGE, ou um intervalo de células no Excel. Por exemplo, quando você faz uma soma, usando a fórmula de mesmo nome, usa-se a seguinte sintaxe:

=SOMA(A1:A20)

Ou algo parecido.

No exemplo acima, A1:A20 é um range, ou seja, o intervalo de células o qual a função SOMA vai coletar os valores desse intervalo e somá-los, retornando um valor numérico como resultado para quem a chamar.

Até aqui, temos o uso tradicional das fórmulas do Excel que usamos no dia a dia. No caso do DESLOC, é um pouco diferente. Vamos começar usando de fato a fórmula para ter uma ideia de como funciona.

O exemplo da página de ajuda do Excel é perfeito para isso. Digite os seguintes dados em uma planilha do Excel em branco:

A B C
1  Nome  Depto  Idade
2  Henry 501 28
3  Stan 201 19
4  Mary 101 22
5  Larry 301 29

Digite a seguinte fórmulas na célula E2 (ou em qualquer célula em branco disponível):

=DESLOC(C2;2;-1;1;1)

Onde os parâmetros são os seguintes:

  • C2 – A célula referenciada.
  • 2 – Indica o número de linhas a mover. Números positivos significam para mover para baixo e números negativos significam para mover para cima.
  • -1 – Indica o número de colunas a mover. Números positivos significam para mover para a direita e números negativos significam para mover para a esquerda.
  • 1 (penúltimo valor) – (Opcional) Indica quantas linhas de dados devem ser retornadas. Este número deve ser positivo.
  • 1 (último valor) – (Opcional) Indica quantas colunas de dados devem ser retornadas. Este número deve ser positivo.

 

Exemplos:

A função DESLOC
A função DESLOC

Ao usar a fórmula com esses parâmetros, a função DESLOC retorna o valor da célula que está localizada duas linhas abaixo (2) e 1 linha à esquerda (-1) da célula C2 (que é a célula B4).

O valor na célula B4 é “101”. Portanto, a fórmula retorna “101”.

Até aqui é a explicação da Ajuda do Excel, bem didática até. Mas vamos dar uma ajudinha para entender o que acontece. Usando a função como apresentada acima, ela fará exatamente o seguinte.

O primeiro parâmetro, que é a célula referenciada, é onde tudo começa. ou seja, o primeiro lugar para onde a fórmula vai olhar:

A célula referenciada
A célula referenciada

O dois próximos parâmetros significam o movimento que a fórmula vai fazer a partir da célula de referência (C2). Esse movimento se dá por linhas (vertical) e colunas (horizontal). No caso da nossa fórmula, temos o valor 2 para linhas e -1 para colunas. No caso das linhas, valores positivos movimentam para baixo, negativos, para cima. Para colunas, valores positivos movimentam para a direita, negativos, para esquerda. Neste caso, temos o seguinte:

Linhas a mover (vertical/horizontal)
Linhas a mover (vertical/horizontal)

Como os últimos parâmetros são opcionais, não precisamos considerá-los por enquanto. Neste caso, o resultado final do “movimento” das células, será:

Resultado final
Resultado final

Que é o resultado esperado. Como o resultado é apenas 1 célula, o valor resultante é automaticamente apresentado.

Mas, e os outros dois parâmetros? Apesar de opcionais, são eles que tornam a função interessante. No caso da fórmula que usamos até então, os valores informados foram 1 e 1, o que quer dizer, fique aí no mesmo lugar. O resultado, neste caso, permanece:

Opcional (linhas e colunas)
Opcional (linhas e colunas)
Opcional (linhas e colunas) (cont.)
Opcional (linhas e colunas) (cont.)

Mas porque? Se as duas primeiras “coordenadas” são o movimento, as próximas são a expansão da função DESLOC. O seja, o quanto a partir do resultado ele abrange. Visualmente fica melhor de entender. No caso, estamos mudando as coordenadas do segundo parâmetro para 2 e 2, portanto:

Opcional (linhas e colunas) (cont.)
Opcional (linhas e colunas) (cont.)

O resultado para este caso é o range B4:C5, que é um range de células. Mas o que acontece com o resultado? Ao ajustar a fórmula, o retorno  será “#VALOR!”. Porque isso? Porque como o resultado é um range (conjunto de células), não é possível mostrar isso como um valor.

Mas, experimente envolver a fórmula na função SOMA, que espera um range com o argumento:

=SOMA(DESLOC(C2;2;-1;1;1))

O resultado é 453. A soma foi aplicada em cima da área destacada em verde na figura anterior, no caso, B4:C5. Lindo!

Criando o problema

Chegamos até aqui, espero, com um bom entendimento do que a função DESLOC pode fazer. É era de utilizá-la para atingir o objetivo deste artigo, que são as listas dinâmicas.

Mas o que quer dizer uma lista dinâmica? O nome é auto explicativo, mas vamos dar uma ajuda. Lembram da nossa tabela?

A B C
1  Nome  Depto  Idade
2  Henry 501 28
3  Stan 201 19
4  Mary 101 22
5  Larry 301 29

Vamos nos concentrar na coluna A, que é onde estão os nomes:

A
1  Nome
2  Henry
3  Stan
4  Mary
5  Larry

E vamos fazer um bom uso dela, através do recurso de validação, criando uma lista apontando para o range A2:A5 (A2 pois a primeira linha é o cabeçalho da tabela).

Validação de Dados
Validação de Dados

O resultado será este:

Validação em Lista com range fixo
Validação em Lista com range fixo

Até aqui está tudo perfeito. O problema com essa lista é que, se adicionarmos mais uma linha na nossa tabela, nossa lista não é atualizada. Se adicionarmos mais um nome na nossa lista (Ana), como abaixo:

A
1  Nome
2  Henry
3  Stan
4  Mary
5  Larry
6  Ana

A lista de validação que criamos anteriormente não será atualizada:

Lista com validação fixa não atualizada
Lista com validação fixa não atualizada

Para que Ana e outros nomes apareçam na lista, precisaremos mudar a fonte de dados na validação para, por exemplo, A2:A6.

É aqui que criamos o problema que queremos resolver, claro, com ao função DESLOC!

Fazendo bom uso da função DESLOC

Agora que temos o problema, vamos aplicar a solução. Estudamos a função DESLOC e criamos uma situação onde precisamos de uma lista dinâmica. Mas como fazer?

Para que a função DESLOC faça o trabalho que precisamos, vamos precisar da ajuda da função CONT.VALORES. Veja como fica a fórmula abaixo:

=DESLOC(A2;0;0;CONT.VALORES(A2:A100);1)

Na lista tela de Validação de Dados:

Validação com DESLOC
Validação com DESLOC

Observando o resultado:

A lista dinâmica em ação
A lista dinâmica em ação

Agora, o que aconteceu quando aplicamos a fórmula acima? Por partes:

  • A função DESLOC foi aplicada a partir do primeiro item da coluna de Nomes
  • Nos parâmetros de movimentação, deixamos 0 (zero) para linha e coluna, pois não queremos mexer na referência, mantendo-a em A2
  • O mais importante, é onde usamos o CONT.VALORES. Essa função retorna a quantidade de células preenchidas num range. Usamos o range A2:A100 para cobrir uma lista que pode crescer até 99 nomes, mas você pode aumentar isso para o quanto você quiser. Uma vez que a função CONT.VALORES vai retorna a quantidade de células preenchidas neste range, o resultado será 5, já contando com o nome Ana, que faz ele aparecer na lista.
  • O último parâmetro é mantido como 1, pois só queremos uma coluna

Agora que entendemos como funciona, tente adicionar mais um nome abaixo de Ana. Em seguida, confira a lista:

A lista dinâmica sendo de fato dinâmica
A lista dinâmica sendo de fato dinâmica

Concluindo

Ufa! Chegamos até aqui. A intenção do artigo foi fazer um passo a passo, para que mesmo não sendo um exímio conhecedor do Excel, conseguirá esta façanha.

Abaixo deixo o arquivo utilizado no exemplo.

Bom divertimento!

Download do arquivo

 Excel – Trabalhando com Ranges Dinâmicos em Fórmulas, Validação e Gráficos.zip (532.34 KiB)

VBA – Concatenando Ranges (Função CONCATENAR)

Velha conhecida dos veteranos de Excel, a função CONCATENAR permite que você faça a união de textos em contidos em células. O único problema da função é que, quando se quer concatenar células que estejam na sequência, não é possível informar o Range.

Por exemplo, caso o texto que se deseja concatenar esteja entre A1 e A10, você precisará usar a função da seguinte maneira:

=CONCATENAR(A1;A2;A3;A4;A5;A6;A7;A8;A9;A10)

Não seria mais simples se isto pudesse ser feito?

=CONCATENAR(A1:A10)

Para isso, nada melhor que uma porção de código VBA. O pessoal do site Excel Tips publicou um código que possibilita exatamente isso. Duas Funções foram disponibilizadas, como mostra o código abaixo.

Function Concat1(myRange As Range, Optional myDelimiter As String)
    Dim r As Range
 
    Application.Volatile
    For Each r In myRange
        Concat1 = Concat1 & r & myDelimiter
    Next r
    If Len(myDelimiter) > 0 Then
        Concat1 = Left(Concat1, Len(Concat1) - Len(myDelimiter))
    End If
End Function
 
Function Concat2(myRange As Range, Optional myDelimiter As String)
    Dim r As Range
 
    Application.Volatile
    For Each r In myRange
        If Len(r.Text) > 0 Then
            Concat2 = Concat2 & r & myDelimiter
        End If
    Next r
    If Len(myDelimiter) > 0 Then
        Concat2 = Left(Concat2, Len(Concat2) - Len(myDelimiter))
    End If
End Function

Nas duas, há um segundo parâmetro, opcional, que é o delimitador, que é um texto que irá separar todas os textos contidos na célula do range informado. Uma mão na roda. A diferença está em que na primeira (Concat1), caso uma das células do range esteja vazia, ele vai repetir o delimitador. No caso da Concat2, o problema é tratado. A figura abaixo mostra o exemplo em ação.

Exemplo de uso das Funções de Concatenação de Ranges
Exemplo de uso das Funções de Concatenação de Ranges

Bom proveito!

Referências

http://excel.tips.net/Pages/T003062_Concatenating_Ranges_of_Cells.html

VBA – Preenchendo um Listbox com valores únicos de uma lista

Fonte: http://www.exceltip.com

A macro abaixo preenche um ListBox (que também poderia ser um ComboBox) em um UserForm com os valores únicos de um range. No VBA, crie um UserForm, insira um ListBox e coloque o código abaixo.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Private Sub UserForm_Initialize()
    Dim MyUniqueList As Variant, i As Long
    With Me.ListBox1
        .Clear    ' limpa o conteúdo do listbox
        MyUniqueList = UniqueItemList(Range("A1:A30"), True)
        For i = 1 To UBound(MyUniqueList)
            .AddItem MyUniqueList(i)
        Next i
        .ListIndex = 0    ' seleciona o primeiro item
    End With
End Sub
 
Private Function UniqueItemList(InputRange As Range, _
                                HorizontalList As Boolean) As Variant
    Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant
    Application.Volatile
    On Error Resume Next
    For Each cl In InputRange
        If cl.Formula <> "" Then
            cUnique.Add cl.Value, CStr(cl.Value)
        End If
    Next cl
    UniqueItemList = ""
    If cUnique.Count > 0 Then
        ReDim uList(1 To cUnique.Count)
        For i = 1 To cUnique.Count
            uList(i) = cUnique(i)
        Next i
        UniqueItemList = uList
        If Not HorizontalList Then
            UniqueItemList = _
            Application.WorksheetFunction.Transpose(UniqueItemList)
        End If
    End If
    On Error GoTo 0
End Function

Neste caso, estou supondo que existe uma lista de valores entre as células A1 e A30 da planilha ativa. A função pode facilmente ser transformada para retornar um array com valores únicos.