Olá pessoal. Vocês lembram do post Excel – Criando listas e ranges dinâmicos com a fórmula DESLOC? Pois é, ele cresceu, criou asas e virou um vídeo tutorial!
Confiram:
Bom proveito!
Olá pessoal. Vocês lembram do post Excel – Criando listas e ranges dinâmicos com a fórmula DESLOC? Pois é, ele cresceu, criou asas e virou um vídeo tutorial!
Confiram:
Bom proveito!
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.
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:
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:
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:
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á:
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:
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:
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!
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).
O resultado será este:
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:
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!
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:
Observando o resultado:
Agora, o que aconteceu quando aplicamos a fórmula acima? Por partes:
Agora que entendemos como funciona, tente adicionar mais um nome abaixo de Ana. Em seguida, confira a lista:
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)
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.
Bom proveito!
http://excel.tips.net/Pages/T003062_Concatenating_Ranges_of_Cells.html
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.