adilsonsoledade

Última postagem

2011-04-28T14:10:00.000-03:00

O conteúdo deste blog foi transferido para: https://usuariosdoexcel.wordpress.com/.
Lá você poderá ver além dos posts destes blog, as novas contribuições.

[ ]s

Dica de VBA - Eliminando valores em duplicidade

2010-10-01T10:32:00.000-03:00

O código a seguir pode ser aplicado para eliminar valores duplicados de uma série de dados dispostos em uma coluna:

Sub EliminarDuplicidades()
Dim wf As WorksheetFunction
Dim rg As Range
Dim k As Integer
Dim Col As Variant

'Solicitar a informação sobre a coluna que será pesquisada
Col = Application.InputBox(Prompt:="Informe o número ou letra da coluna a ser pesquisada", _
Title:="eliminar dados duplicados em uma coluna", Type:=3)

'Atribuir o objeto Worksheetfunction à variável wf
Set wf = Application.WorksheetFunction

'Loop para percorrer as células da coluna indicada
'O loop se inicia na última célula preenchida da coluna em direção à célula da linha 1
For k = Cells(Rows.Count, Col).End(xlUp).Row To 1 Step -1

'Se houver mais repetições do valor na coluna selecionada, a linha correspondente é apagada
If wf.CountIf(Range(Cells(1, Col), Cells(Cells(Rows.Count, Col).End(xlUp).Row, Col)) _
, Cells(k, Col)) > 1 Then Rows(k).Delete
Next k
End Sub

[ ]s

Explorando funções de texto - Parte 6

2010-09-15T22:29:00.000-03:00

O Excel possui uma função que permite retornar o caminho completo de um arquivo salvo.
Para isto, podemos digitar em uma célula qualquer: =CÉL("filename";A1)
Mas se quisermos retornar apenas o nome da planilha, ou apenas a pasta na qual o arquivo está salvo?
Valendo-se das funções de texto já comentadas anteriormente, podemos apresentar as seguintes soluções:
Nome da planilha:
=DIREITA(CÉL("filename";A1);NÚM.CARACT(CÉL("filename";A1))-LOCALIZAR("]";CÉL("filename";A1)))

Localização do arquivo (pasta):
=ESQUERDA(CÉL("filename";A1);LOCALIZAR("[";CÉL("filename";A1))-1)

Para quem, como eu, já teve que manter atualizados documentos do sistema de qualidade, estas funções permitem que controlemos a localização dos arquivos de forma automática, tornando o seu acesso mais rápido e disponível para os interessados.

[ ]s

Explorando funções de texto - Parte 5

2010-09-13T11:14:00.000-03:00

Depois da postagem anterior, quando utilizei uma UDF para retornar o primeiro nome e o último sobrenome, alguns amigos me perguntaram se seria possível realizar o mesmo utilizando funções nativas.
A resposta é sim.
Vejam abaixo como fica a fórmula matricial:

=ESQUERDA(A1;LOCALIZAR(" ";A1))&DIREITA(A1;NÚM.CARACT(A1)-MÁXIMO(SE(ÉERROS(LOCALIZAR(" ";A1;LIN(INDIRETO("A1:A"&NÚM.CARACT(A1)))));0;LOCALIZAR(" ";A1;LIN(INDIRETO("A1:A"&NÚM.CARACT(A1)))))))

PS: Para aqueles que não estão habituados a utilizar fórmulas matriciais, é necessário digitar CTRL+SHFT+ENTER após inserir a fórmula numa célula. O resultado final é exibido entre chaves ({Fórmula})

[ ]

Explorando funções de texto - Parte 4

2010-09-09T21:35:00.000-03:00

Embora tenhamos tantas funções de texto nativas no Excel, na minha opinião ainda poderíamos ter, por exemplo:
1. Uma função para contar o número de ocorrências de uma cadeia de caracteres numa cadeia de texto;
2. Uma função para concatenar as células de um intervalo informado como argumento da função;
3. Uma função que retorna-se o primeiro nome e o último sobrenome de nomes de pessoas.

Bem, como o Excel não possui estas funções nativas, eis minhas sugestões de UDF (User Defined Function).

1. Função CONTCARACT
O que faz: Conta o número de ocorrências de uma seqüência de caracteres em uma cadeia de texto
Sintaxe:
CONTCARACT(Texto; Pesquisar;Tipo)
Texto: Texto no qual a cadeia será pesquisada;
Pesquisar: Cadeia a ser pesquisada;
Tipo: FALSO (ou omitido) >> não serão diferenciadas maiúsculas e minúsculas)
Código:

Function CONTCARACT(Texto As String, Pesquisar As String, Optional Tipo As Boolean)
Dim i As Integer
Application.Volatile
For i = 1 To Len(Texto)
   Select Case Tipo
   Case True
   If (Mid(Texto, i, Len(Pesquisar))) = (Pesquisar) Then CONTCARACT = CONTCARACT + 1
   Case Else
   If UCase(Mid(Texto, i, Len(Pesquisar))) = UCase(Pesquisar) Then CONTCARACT = CONTCARACT + 1
   End Select
Next i
End Function

2. Função CONCATENARINTERVALO
O que faz: Concatena todas as células de um intervalo contínuo informado.
Sintaxe: CONCATENARINTERVALO(Intervalo)
Código:

Function CONCATENARINTERVALO(Intervalo As Range)
Dim Célula As Range
Application.Volatile
For Each Célula In Intervalo
CONCATENARINTERVALO = CONCATENARINTERVALO & Célula
Next Célula
End Function

3. Função ENCURTARNOMES
O que faz: Exibe apenas o primeiro e o último nome de uma cadeia de texto cujos elementos são separados por espaços
Sintaxe: ENCURTARNOMES(Nome)
Código:

Function ENCURTARNOMES(Nome As String)
Dim PrimNome As String
Dim UltNome As String
Application.Volatile
PrimNome = Left(Nome, InStr(1, Nome, Space(1)) - 1)
UltNome = Right(Nome, Len(Nome) - InStrRev(Nome, Space(1), -1))
ENCURTARNOMES = PrimNome & Space(1) & UltNome
End Function

continua...

Explorando funções de texto - Parte 3

2010-09-08T06:44:00.000-03:00

11. MAIÚSCULA()

O que faz: Transforma todas as letras da cadeia de texto em suas versões maiúsculas

Exemplo:

Se temos em A1: “Microsoft Excel”, MAIÚSCULA(A1;9) retorna “MICROSOFT EXCEL”

12. MINÚSCULA()

O que faz: Transforma todosas letras da cadeia de texto em suas versões minúsculas

Exemplo:

Se temos em A1: “Microsoft Excel”, MINÚSCULA(A1;9) retorna “microsoft excel”

13. MOEDA()

O que faz: Transforma um número em moeda (utilizando o formato corrente do computador), permitindo controlar o número de decimais exibidos.

Exemplo:

Se temos em A1: 5345,678, MOEDA(A1,1) retorna "R$ 5.345,7" (caso seu computador tenha a configuração padrão do windows para português)

14. MUDAR()

O que faz: Substitui parte de uma cadeia de caracteres pelo trecho informado.

Exemplo:

=MUDAR("Microsoft Excel 2003";17;4;2007), retorna "Microsoft Excel 2007"

15. NÚM.CARACT()

O que faz: Retorna o número de caracteres presentes em uma cadeia de texto.

Exemplo:

Se temos em A1: “Microsoft Excel”, NÚM.CARACT(A1) retorna 15

16. PRI.MAIÚSCULA()

O que faz: Converte a primeira letra de cada palavra de uma cadeia de texto em maíscula e as demais em minúsculas

Exemplo:

Se temos em A1: “microsoft excel”, PRI.MAIÚSCUA(A1) retorna “Microsoft Excel”

17. PROCURAR()

O que faz: Retorna a posição de uma cadeia de texto pesquisada em outra cadeia de texto. Diferencia maiúsculas e minúsculas

Exemplo:

=PROCURAR("Excel";"Microsoft Excel") retorna o valor 11

18. REPT()

O que faz: Repete o caractere informado, o número de vezes determinado.

Exemplo:

REPT(“x”,5) retorna “xxxxx”

19. SUBSTITUIR()

O que faz: Substitui um trecho de uma cadeia de texto por outro.

Exemplo:

SUBSTITUIR("Microsoft Excel 2003";2003;2007), retorna "Microsoft Excel 2007

20. T()

O que faz: Retorna uma cadeia vazia ("") se a célula não contiver texto. Caso contenha, retorna a cadeia de texto.

Exemplo:

T(10) retorna ""

21. TEXTO()

O que faz: Converte um valor em texto, exibindo-o de acordo com o formato especificado.

Exemplo:

Se temos em A1: 5345,678, TEXTO(A1;"#.#00") retorna '5.346"

22. TIRAR()

O que faz: Remove do texto os caracteres não imprimíveis. (OBS: A função TIRAR foi desenvolvida para remover os 32 primeiros caracteres não-imprimíveis no código ASCII de 7 bits (valores de 0 a 31) do texto.)

Exemplo:

Se temos em A1: "25| ", TIRAR(A1) retorna "25"

23. VALOR()

O que faz: Converte uma cadeia de caracteres que representa um valor numérico, no respectivo valor.

Exemplo:

Se temos em A1 o valor “01/11/10”, =VALOR(A1) retorna 40483

E para completar, não podemos deixar de citar o operador "&" que é utilizado para concatenar cadeias de texto, da mesma forma que a função CONCATENAR.

Exemplo: Se A1: "Microsoft" e A2: "Excel", a fórmula =A1 & " " & A2, retorna "Microsoft Excel".

[ ]s

Explorando funções de texto - Parte 2

2010-09-07T21:32:00.000-03:00

5. DEF.NÚM.DEC()
O que faz: retorna uma cadeia de texto que representa o arredondamento de um número fornecido para o número de casas decimais desejadas. Podem ser exibidos, ou não, os separadores de decimais.
Exemplo:
Se temos em A1: 5345,678
=DEF.NÚM.DEC(A1,1,VERDADEIRO), retorna "5345,7"
=DEF.NÚM.DEC(A1,12,FALSO), retorna "5.345,68"

6. DIREITA()

O que faz: Retorna os n caracteres mais à direita da cadeia de texto.

Exemplo:

Se temos em A1: “Microsoft Excel”, DIREITA(A1;5) retorna “Excel”

7. ESQUERDA()

O que faz: Retorna os n caracteres mais à esquerda da cadeia de texto.

Exemplo:

Se temos em A1: “Microsoft Excel”, ESQUERDA(A1;9) retorna “Microsoft”

8. EXATO()

O que faz: Compara duas cadeias de caracteres, diferenciados maiúsculas e minúsculas, retornando VERDADEIRO se forem iguais

Exemplo:

Se temos em A1: "Excel" e em A2: "excel", EXATO(A1;A2) retorna FALSO

9. EXT.TEXTO()

O que faz: Retorna um trecho da cadeia de texto a partir de uma posição inicial, com n caracteres de comprimento.

Exemplo:

Se temos em A1: “Microsoft Excel 2007”, EXT.TEXTO(A1;11;5) retorna “Excel”

10. LOCALIZAR()

O que faz: retorna a posição de uma cadeia de texto pesquisada, estabelecida com base no seu caractere inicial, contada da esquerda para a direita. Não diferencia maiúsculas e minúsculas

Exemplo:

LOCALIZAR("Excel"; "Microsoft Excel") retorna 11

continua...

Explorando funções de texto - Parte 1

2010-09-03T23:07:00.000-03:00

Já me deparei diversas vezes com usuários penando para remover "lixo" do conteúdo de células, ou gastando horas para modificar o conteúdo de linhas e mais linhas de forma a adequá-las a uma necessidade específica.
Utilizando as funções de texto, podemos poupar bastante trabalho.
Listaremos as funções, explicando o que realizam e colocando um exemplo de utilização.
Inicialmente vamos explorar as funções isoladamente, para, em seguida começarmos a combiná-las de forma a criar fórmulas mais complexas.

1. ARRUMAR()
O que faz: remove os espaços excedentes de uma cadeia de texto, deixando apenas os espaços entre palavras
Exemplo:
Se temos em A1: " Microsoft Excel Versão 2007 "
A função =ARRUMAR(A1), retorna "Microsoft Excel Versão 2007"

2. CARACT()
O que faz: retorna o caractere correspondente ao código ASCII informado
Exemplo:
CARACT(65), retorna o caractere "A"

3. CÓDIGO()
O que faz: retorna o código ASCII correspondente ao primeiro caractere à esquerda de uma cadeia de texto
Exemplo:
CÓDIGO("A"), retorna o código 65

4. CONCATENAR()
O que faz: combina diversas cadeias de caracteres, números ou referências de células numa única cadeia.
Exemplo:
Sendo as células:
A1: "Combinar"
A2: "textos"
CONCATENAR(A1;" ";A2), retorna a cadeia "Combinar textos"

continua....

Utilizando eventos do Excel - Parte 3

2010-09-02T21:30:00.000-03:00

Fechando nossos exemplos sobre uso de eventos no Excel, vamos ver exemplos utilizando os eventos do objeto Workbook.

Primeiramente o evento Workbook_Open, que ativado quando o arquivo é aberto.
No exemplo a seguir são realizadas uma série de alterações no modo em que o arquivo do Excel é exibido.

Private Sub Workbook_Open()

Dim Plan

'Faz que o código continue sendo executado caso ocorra um erro
On Error Resume Next

'Alteração dos parâmetros de exibição do Excel
With Application
   'Ativar exibição de tela inteira
   .DisplayFullScreen = True
   'Desativar a exibição da barra de fórmulas
   .DisplayFormulaBar = False
   'Desativar a exibição da barra de status
   .DisplayStatusBar = False
End With

'Alteração dos parâmetros de exibição da janela
With ActiveWindow
   'Desativar a exibição dos cabeçalhos
   .DisplayHeadings = False
   'Desativar a exibição da barra de rolagem horizontal
   .DisplayHorizontalScrollBar = False
   'Desativar a exibição da barra de rolagem vertical
   .DisplayVerticalScrollBar = False
   'Desativar a exibição das guias de planilha
   .DisplayWorkbookTabs = False
End With

For Each Plan In ThisWorkbook.Worksheets
   'Manter exibida apenas a planilha "Principal
   If Plan.Name <> "Principal" Then Plan.Visible = False
Next

End Sub

É interessante desfazer as alterações que afetam o Excel (Application) quando o arquivo é fechado. Para isso podemos lançar mão do evento Workbook_BeforeClose.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Faz que o código continue sendo executado caso ocorra um erro
On Error Resume Next

'Restauração dos parâmetros de exibição do Excel
With Application
   'Inibir exibição de tela inteira
   .DisplayFullScreen = False
   'Ativar a exibição da barra de fórmulas
   .DisplayFormulaBar = True
   'Ativar a exibição da barra de status
   .DisplayStatusBar = True
End With
End Sub

[ ]s

Utilizando eventos do Excel - Parte 2

2010-09-01T23:02:00.000-03:00

Vamos ver agora outros exemplos, utilizando o evento Worksheet_Change

O objetivo neste exemplo é colorir qualquer célula alterada que esteja no intervalo A1:C50 da planilha. A célula ficará colorida em vermelho com a fonte formatada para a cor branca.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rgFormat As Range
Dim rgInter As Range

'Intervalo dentro do qual será realizada a formatação
Set rgFormat = ActiveSheet.[A1:C50]

'É obtida a intersecção da célula alterada com o intervalo rgFormat
Set rgInter = Application.Intersect(Target, rgFormat)

'Se houver a intersecção, ou seja se Target pertencer ao intervalo
'rgFormat, a célula será colorida de vermelho e a fonte será
'formatada para a cor branca
If Not rgInter Is Nothing Then
Target.Interior.Color = vbRed
Target.Font.Color = vbWhite
End If

End Sub

No próximo exemplo, iremos colocar em letras maiúsculas qualquer valor de texto inserido nas células do intervalo A1:C50. É importante observar a utilização da linha de comando

Application.EnableEvents = False

Como o código altera o conteúdo da célula, é necessário que desabilitemos os eventos, pois caso contrário cria-se um loop infinito, pois a alteração dispara um novo evento.

Também é importante lembrar que os eventos devem ser reabilitados antes do término da execução do código, ou poderemos afetar outras funcionalidades do arquivo.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rgCaps As Range
Dim rgInter As Range

'Intervalo dentro do qual será realizada a formatação
Set rgCaps = ActiveSheet.[A1:C50]

'É obtida a intersecção da célula alterada com o intervalo rgChange
Set rgInter = Application.Intersect(Target, rgCaps)

'Se houver a intersecção, ou seja se Target pertencer ao intervalo
'rgChange, os eventos são desabilitados
If Not rgInter Is Nothing Then
Application.EnableEvents = False

'Em seguida o conteúdo de Target é colocado em maiúsculas
Target = UCase(Target)

'Finalmente os eventos são reabilitados
Application.EnableEvents = True

End If

End Sub

[ ]s

Utilizando eventos do Excel - Parte 1

2010-08-31T22:31:00.000-03:00

Primeiramente podemos conceituar um evento como a ocorrência de mudança de status de um objeto.
Ao selecionarmos uma planilha, por exemplo, podemos capturar esta ocorrência por meio do evento Worksheet_Activate.
Desta forma podemos controlar de que maneira uma planilha ou outro objeto se comporta quando ocorrer uma série de diferentes alterações.
Vamos começar explorando alguns eventos de planilha.
Para que possam ser inseridos, é necessário acessar a página de código associado à planilha. A forma mais comum é clicar com o botão direito na aba de planilha e selecionar a opção Exibir Código.
Eis dois exemplos de códigos utilizando eventos de planilha

1. Worsksheet_Activate.
Ao selecionar a planilha, o usuário é informado sobre a forma de inserir os dados. Isto pode ser utilizado como um help simplificado.

Private Sub Worksheet_Activate()
Dim msg As String
msg = "Planilha para cálculo de juros" & Chr(13)
msg = msg & "Informe em A1 o valor do principal." & Chr(13)
msg = msg & "Informe em B1 o tempo de aplicação, expresso em meses" & Chr(13)
msg = msg & "Informe em C1 a taxa de juros, expressa em % a.m."
MsgBox msg
End Sub

2. Worksheet_Change
Ao modificar o valor de uma célula, o interior da mesma é colorido com base no valor digitado.

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target
Case Is < 0
Target.Interior.Color = vbRed
Case 0 To 100
Target.Interior.Color = vbGreen
Case 100 To 1000
Target.Interior.Color = vbBlue
Case Else
Target.Interior.Color = xlNone
End Select
End Sub

[ ]s

Testando múltiplas condições em código VBA (Select Case)

2010-08-30T22:50:00.000-03:00

Muitas vezes temos que testar inúmeras possibilidades para uma variável durante a execução de um código em VBA.
Normalmente pensamos em construir uma estrutura do tipo If...Then...Else.
Se o conjunto de possibilidades for relativamente pequena, a abordagem acima é de fácil implantação e interpretação, mas à medida que aumentam os testes, a estrutura tende a ficar mais complexa.
Tomemos o seguinte exemplo: Construir uma função que estabeleça o status do fornecedor com base na nota obtida numa auditoria de qualidade, com base nos seguintes critérios:
- Se a nota for menor que 30, o fornecedor deve ser "desqualificado"
- Se a nota estiver entre 30 e 40, "situação crítica"
- Se a nota estiver entre 40 e 50, "estado de alerta"
- Se a nota estiver entre 50 e 60, "sob monitoramento"
- Se a nota estiver entre 60 e 70, "aceitável"
- Se a nota estiver entre 70 e 80, "bom"
- Se a nota estiver entre 80 e 90, "muito excelente"
- Se a for superior a 90, "excelente".

Vejamos como ficaria o código, utilizando a estrutura If Then ElseIf.

Function StatusNotaV1nota)
If nota < 30 Then
StatusNotaV1 = "desqualificado"
ElseIf nota >= 30 And nota < 40 Then
StatusNotaV1 = "situação crítica"
ElseIf nota >= 40 And nota < 50 Then
StatusNotaV1 = "estado de alerta"
ElseIf nota >= 50 And nota < 60 Then
StatusNotaV1 = "sob monitoramento"
ElseIf nota >= 60 And nota < 70 Then
StatusNotaV1 = "aceitável"
ElseIf nota >= 70 And nota < 80 Then
StatusNotaV1 = "bom"
ElseIf nota >= 80 And nota < 90 Then
StatusNotaV1 = "muito bom"
ElseIf nota >= 50 And nota < 60 Then
StatusNotaV1 = "excelente"
End If
End Function

O código acima é completamente funcional, mas acho-o meio poluído.

Vejamos como fica o mesmo código, utilizando o comando Select Case

Function StatusNotaV2(nota)

Select Case nota

Case Is < 30

StatusNotaV2 = "desqualificado"

Case 30 To 39

StatusNotaV2 = "situação crítica"

Case 40 To 50

StatusNotaV2 = "estado de alerta"

Case 50 To 60

StatusNotaV2 = "sob monitoramento"

Case 60 To 70

StatusNotaV2 = "aceitável"

Case 70 To 80

StatusNotaV2 = "bom"

Case 80 To 90

StatusNotaV2 = "muito bom"

Case Is > 90

StatusNotaV2 = "excelente"

End Select

End Function

O resultado é o mesmo, mas com uma estrutura mais clean.

[ ]s

Executando macros em planilhas protegidas

2010-08-27T21:54:00.000-03:00

Algumas vezes temos planilhas que estão protegidas para evitar que os usuários alterem resultados ou formatação, mas sobre as quais é necessário realizar alterações via macros.
A solução é desproteger a planilha nos primeiros passos do código, realizar as alterações e, em seguida, tornar a protegê-la.
Veja um exemplo de como pode ser feito:

Sub ExemploProtegerDesproteger()
   'Atribuição da planilha que será manipulada
   Set ws = Worksheets("Plan1")
   'Desproteger a planilha ws
   ws.Unprotect Password:="1234"

   '...linhas de código a serem executadas

   'Proteger a planilha ws
   ws.Protect Password:="1234"
End Sub

[ ]s

Determinar os n valores mais freqüentes

2010-08-26T22:37:00.000-03:00

Se quisermos determinar o valor mais freqüente de um conjunto de dados no excel, podemos utilizar a função MODO.
No entanto, o excel não tem nenhuma função nativa para calcular os n valores mais freqüentes.
A partir desta necessidade, desenvolvi a função personalizada (UDF) abaixo:

Option Base 1
'Adaptado a partir de exemplo disponível em:
' http://spreadsheetpage.com/index.php/tip/identifying_unique_values_in_an_array_or_range/

Function ItensÚnicos(ConjuntoValores As Range) As Variant
' Aceita um intervalo como input

   Application.Volatile

   Dim ValÚnicos() As Variant ' Matriz que contém os valores únicos
   Dim Elemento As Variant
   Dim Transf As Variant
   Dim i As Integer
   Dim j As Integer
   Dim Correspondência As Boolean

' Contador para os valores únicos
   NumValÚnicos = 0
' Loop através da matriz ou intervalo
   For Each Elemento In ConjuntoValores
   Correspondência = False
' Verificar se o valor já foi inserido
   For i = 1 To NumValÚnicos
   If Elemento = ValÚnicos(2, i) Then
   Correspondência = True
   Exit For '(Saída do Loop)
   End If
   Next i
AddItem:
' Caso não esteja na lista, o valor é inserido na matriz
   If Not Correspondência And Not IsEmpty(Elemento) Then
   NumValÚnicos = NumValÚnicos + 1
   ReDim Preserve ValÚnicos(2, NumValÚnicos)
   'Inserção do valor
   ValÚnicos(2, NumValÚnicos) = Elemento
   'Inserção da frequência correspondente
   ValÚnicos(1, NumValÚnicos) = Application.WorksheetFunction.CountIf(ConjuntoValores, Elemento)
   End If
   Next Elemento


' Ordenar matriz com base nas frequências
   For i = 1 To NumValÚnicos
   For j = i + 1 To NumValÚnicos
   If ValÚnicos(1, i) <= ValÚnicos(1, j) Then
   Transf = ValÚnicos(1, j)
   ValÚnicos(1, j) = ValÚnicos(1, i)
   ValÚnicos(1, i) = Transf
   End If
   Next j
   Next i

' Reorganizar a matriz
   For i = 1 To NumValÚnicos
   Transf = ValÚnicos(1, i)
   ValÚnicos(1, i) = ValÚnicos(2, i)
   ValÚnicos(2, i) = Transf
   Next i

' Atribuição de valor para a função
   ItensÚnicos = ValÚnicos
End Function

Eis aqui um exemplo de uso: http://www.4shared.com/file/h8WHpoUE/ContagemMltipla.html

[ ]s

Loop para percorrer intervalos descontínuos

2010-08-26T22:25:00.000-03:00

Imagine que você deseja percorrer todas as células dos intervalos A1:A100, A150:A200 e A300:A400, ocultando as linhas que não tem valores preenchidos.
Poderíamos realizar 3 loops diferentes, colocando para cada um, o intervalo de variação das linhas dos intervalos.
Mas existe uma forma mais simples.
Para isso vamos utilizar o operador Union para reunir todos os intervalos e em seguida percorrê-los de forma única.

Sub PercorrerIntervalo()
Dim rgComposto As Range
Dim rg As Range
Set rgComposto = Union([A1:A100],[A150:A200],[A300:A400])
   For Each rg in rgComposto
   If rg <> "" Then Rows(rg.Row).Hidden = False Else Rows(rg.Row).Hidden = True
   Next rg
End Sub

[ ]s

Voltando à ativa.... "Transpor" valores de célula para uma coluna

2010-08-25T22:24:00.000-03:00

Boa noite,
Esta dúvida foi postada no fórum do Julio Battisti:
"Tenho da célula A2 o seguinte conteúdo: '119346 / 119347 / 119348 / 119349 / 119350 / 119351 / 119352 / 119353 / 119354 / 119355 / 119356' .
Como posso transferí-lo para as células de uma coluna?"

Eis a solução apresentada:

Sub TransporCélulaparaVertical()

Dim rgOrigem    As Range

Dim rgDestino   As Range

Dim n           As Variant

Dim i           As Integer

Set rgOrigem = Application.InputBox(Prompt:="Informe a célula de origem", Type:=8)

Set rgDestino = Application.InputBox(Prompt:="Informe a célula de destino", Type:=8)

arrayNomes = Split(rgOrigem.Value, "/")

For i = LBound(arrayNomes) To UBound(arrayNomes)

rgDestino.Offset(i, 0) = arrayNomes(i)

Next i

End Sub

É interessante observar alguns dos elementos utilizados:

InputBox: este recurso permite interação do usuário com o código, inserindo valores ou referências que direcionam o fluxo de execução. No código acima, o InputBox foi utilizado para que sejam informadas: a célula que contém o texto a ser transferido e a célula da coluna a partir da qual será expandida.

Split: este comando permite que se "quebre" um string (cadeia de texto) a partir de um caractere de separação. Desta forma, cada um dos valores separados por "/" passa a constituir um elemento de um vetor.

LBoud e UBound: Quando não conhecemos o número de elementos de um vetor ou não queremos cometer erros em relação à seleção da base 0 ou 1 para numeração de seus elementos, podemos utilizar os dois comandos para garantir que um loop percorra todos os elementos do vetor.

[ ]s

Modificando a precisão / expressão de valores no excel

2010-02-04T23:33:00.000-02:00

Muitas vezes precisamos adequar a precisão do cálculo ou a forma de exprimirmos os resultados quando operamos com valores numéricos.
O Excel permite várias possibilidades, que veremos em seguida.
Para começar, digite na célula A1 de uma planilha a seguinte função: =PI(). Se a sua célula estiver com o formato numérico Geral, será exibido o valor 3,141593. Embora sejam exibidos 6 algarismos após a vírgula, o Excel trabalha internamente com 14 algarismos significativos, armazenando na sua memória o valor 3,14159265358979.

Sem alterar o valor, apenas modificando a forma de exibição, podemos aumentar ou reduzir o número de algarismos à esquerda da vírgula, modificando a formatação numérica. Se aplicarmos, por exemplo, a formatação 0,00, o valor será exibido como 3,14. Quaisquer cálculos envolvendo a célula A1, utilizarão todos os algarismos significativos disponíveis.

Em algumas aplicações específicas é necessário reduzir o nível de precisão, utilizando apenas uma parcela dos algarismos significativos. Isto pode ser realizado utilizando-se diferentes funções do Excel.

1. ARRED(Núm, Núm_digitos). Arredonda o número informado para o número de algarismos significativos informados. Desta forma, =ARRED(A1;4), retorna o valor 3,1416. Se calcularmos a diferença =A1-ARRED(A1;4), obteremos o valor -7,34641020994076E-06, o que mostra que alteramos não apenas a forma de exibição, mas também a precisão do número.

2. TRUNCAR(Núm, Núm_digitos). Trunca o número, desprezando os algarismos significativos posteriores. TRUNCAR(A1;4), retorna o valor 3,1415.

3. ARREDONDAR.PARA.BAIXO(Núm, Núm_digitos). Força o arredondamento para baixo independente do algarismo posterior ao algarismo significativo. ARREDONDAR.PARA.BAIXO(A1;3), retorna o valor 3,141. Observe que não é levado em consideração o fato do próximo algarismo ser 6.

4. ARREDONDAR.PARA.CIMA(Núm, Núm_digitos). Força o arredondamento para cima independente do algarismo posterior ao algarismo significativo. ARREDONDAR.PARA.CIMA(A1;2), retorna o valor 3,15. Observe que não é levado em consideração o fato do próximo algarismo ser 1.

5. ARREDMULTB(Núm, Significância). Arredonda para o múltiplo mais próximo e menor que o valor informado em significância. A função ARREDMULTB(A1;0,03), retorna o valor 3,12.

6. TETO(Núm, Significância). Arredonda para o múltiplo mais próximo e maior que o valor informado em significância. A função TETO(A1;0,03), retorna o valor 3,15.

7. INT(Núm). Retorna apenas a parte inteira do número. INT(A1), retorna o valor 3.

Além das fórmulas acima, é possível controlar a forma pela qual o Excel realiza os cálculos através do caminho Ferramentas >> Opções >> Cálculo, marcando-se o checkbox Precisão conforme exibido. Com esta opção ativa apenas serão utilizado para os cálculos, os algarismos significativos exibidos pela formatação das células.

Abraços e até a próxima.

Criando um gráfico de colunas com cores condicionais

2010-01-24T16:41:00.000-02:00

Já precisou criar um gráfico de barras de forma que os valores acima de 5% sejam da cor verde e abaixo deste valor da cor vermelha?
Eis um passo a passo para construí-lo:
1. Vamos criar uma série de dados hipotéticos referentes aos valores mensais e estabelecer o valor de 5% como a meta a ser alcançada.
O resultado final seria algo como este aqui:

2. Vamos agora criar as fórmulas de forma que os valores maiores ou iguais à meta estejam numa coluna e os valores abaixo da meta estejam em outra.
A imagem a seguir mostra como ficariam as fórmulas:

3. Agora podemos inserir o gráfico de colunas, a partir do intervalo com os dados. No nosso exemplo, A2:E9.
Vejamos o resultado:

4. Eliminando as colunas correspondentes a valores e meta, o gráfico assume esta aparência:

5. Ajustando a sobreposição das séries para 100% e mais um pequeno ajuste nas cores das séries, chegamos ao formato final:

Para quem desejar ver o exemplo pronto, eis o link: Gráfico_Condicional.xls

[ ]s

Validação avançada de dados - Excel 2007

2010-01-05T22:52:00.000-02:00

Para aqueles que desejam montar uma validação de dados, de forma que os dados de uma lista sejam condicionados com base em seleções anteriores, eis um excelente artigo de autoria do Robert Martin: Leia aqui
[ ]s

Trabalhando com tempo no Excel - Parte II

2009-12-28T20:30:00.001-02:00

- Na Parte 1 deste texto, vimos como o Excel interpreta e trabalha com o tempo. Vimos também como realizar operações básicas envolvendo datas.

- Agora daremos continuidade, vendo formas de utilização das funções de tempo disponíveis no Excel, agrupadas como funções de data e hora.

- Lista das funções (extraído do help do Excel)

DATA	Retorna o número de série de uma data específica
DATA.VALOR	Converte uma data na forma de texto para um número de série
DIA	Converte um número de série em um dia do mês
DIAS360	Calcula o número de dias entre duas datas com base em um ano de 360 dias
DATAM	Retorna o número de série da data que é o número indicado de meses antes ou depois da data inicial
FIMMÊS	Retorna o número de série do último dia do mês antes ou depois de um número especificado de meses
HORA	Converte um número de série em uma hora
MINUTO	Converte um número de série em um minuto
MÊS	Converte um número de série em um mês
DIATRABALHOTOTAL	Retorna o número de dias úteis inteiros entre duas datas
AGORA	Retorna o número de série seqüencial da data e hora atuais
SEGUNDO	Converte um número de série em um segundo
HORA	Retorna o número de série de uma hora específica
VALOR.TEMPO	Converte um horário na forma de texto para um número de série
HOJE	Retorna o número de série da data de hoje
DIA.DA.SEMANA	Converte um número de série em um dia da semana
NÚMSEMANA	Converte um número de série em um número que representa onde a semana cai numericamente em um ano
DIATRABALHO	Retorna o número de série da data antes ou depois de um número específico de dias úteis
ANO	Converte um número de série em um ano
FRAÇÃOANO	Retorna a fração do ano que representa o número de dias entre data_inicial e data_final

Além destas, é importante lembrar da existência da função:

DATADIF

Calcula o número de dias, mês ou anos entre duas datas. Essa função é fornecida por compatibilidade com o Lotus 1-2-3.

Trabalhando com tempo no Excel - Parte I

2009-12-19T23:37:00.000-02:00

1. Como o Excel interpreta o transcorrer do tempo?

Toda contagem de tempo é relativa. Contamos sempre o tempo transcorrido entre dois momentos, em outras palavras, avariamos a duração de um dado evento. Tendo isso em mente, foi estabelecido para o Excel que o momento t = 0, ocorreu exatamente à zero hora do dia 1º de janeiro de 1900 (00:00 01/01/1900). Cada dia contado a partir desta data representa uma unidade de tempo. Se digitarmos uma data no Excel, como, por exemplo, 13/12/2009 e modificarmos a formatação para número com separador de milhares (#.##0) veremos o valor 40.160. Isto significa que se passaram 40.160 dias desde o dia 01/01/1900 até o dia 13/12/2009.
Compreendido então, que a unidade fundamental de marcação de tempo é o dia, deduzimos então que 1 hora é representada pela fração 1/24, 1 minuto pela fração (1/1.440) e 1 segundo, por (1/86.400)

2. Operações fundamentais com unidades de tempo

SOMA : Podemos adicionar, por exemplo, o número de dias a uma determinada data para calcular uma data futura.
Ex: Sabendo que a validade da água mineral é de 180 dias, para calcular a data de validade de uma garrafa produzida em 23/02/2009, basta realizar a operação 23/02/2009 + 180. Ou não?
Neste ponto, surgem alguns detalhes cruciais e que devem ser compreendidos para obter os resultados desejados no Excel.
Se em uma célula colocarmos diretamente a expressão =23/02/2009 + 180, obteremos como resultado o valor 180,0057.
Obviamente não é isto que desejamos....
Mas por que o Excel errou o cálculo?
Conforme comentado anteriormente, a exibição de um número na forma de data é uma questão de formatação. Por outro lado, a célula possui um identificador de fórmula como o sinal de igual (=) ou de soma (+), as barras são interpretadas como operadores de divisão. Resumindo, a expressão =23/02/2009 + 180 é equivalente a =23 dividido por 2 dividido por 2009 somado a 180.
Desta forma, ao realizarmos operações envolvendo datas, a melhor forma é separar as parcelas em diferentes células.
Poderíamos, por exemplo, colocar em A1 a data 23/02/2009, em A2 a quantidade de dias a serem somados, 180 e em A3 a fórmula =A1+A2, obtendo o resultado 22/08/2009.
Mas, e se, ainda assim, estivermos determinados em somar data e número numa mesma célula, é possível?
A resposta é sim. O truque é colocar a data entre aspas. Ficaria assim = "23/02/2009" + 180.

SUBTRAÇÃO: As duas formas mais comuns de subtração envolvendo data são: i) Subtrair um determinado número de dias de uma data, ou ii) Determinar a quantidade de dias entre duas datas.
i) Para este caso, são válidas as mesmas regras e observações comentadas sobre a operação de soma.
ii) Caso desejemos determinar esta quantidade basta subtrairmos a data menor da data maior. O mais normal é colocarmos cada data em uma célula e utilizar uma terceira célula para inserirmos a função de subtração. Também é possível obter o resultado utilizando o truque, já comentado, de utilizar data como texto no interior da célula.

Até a próxima

Excel 2010 - Primeiras impressões

2009-12-13T23:23:00.000-02:00

Este final de semana, realizei a instalação do pacote Office 2010, versão Beta.
À primeira vista, o layout é basicamente o mesmo da versão 2007, tendo sido preservada a mesma disposição do Ribbon.
O que me chamou logo a atenção, foi a substituição do botão do Office (canto superior esquerdo), pela aba File por meio da qual é possível acessar as propriedades do arquivo e alterar propriedades do próprio excel.
Ao acessar esta opção fui surpreendido por uma seção: Customize Ribbon. Será que poderemos agora personalizar o Ribbon sem ter que lançar mão de XML?
Continuando este overview, é possível observar na aba Insert, um botão chamado Screenshot, duas novas seções: Sparline e Slicer, e um novo botão para inserção de Equações.
Não há outras alterações de interface visíveis.

Feita esta visão inicial, estarei , em breve, analisando as novas funcionalidades descritas acima.

[ ]s

PROCV Turbinado - Funções personalizadas

2009-12-10T22:58:00.000-02:00

Criei estas duas funções para atender à limitação do PROCV de só exibir a primeira ocorrência de um valor procurado.
A função PROCVMÚLTIPLO(NomePesquisa; IntervaloPesquisa; IntervaloRetorno) pesquisa um valor num intervalo e retorna todas as ocorrências correspondentes num outro intervalo informado, separadas por ponto e vírgula.
Código:

Function PROCVMÚLTIPLO(NomePesquisa As String, IntervaloPesquisa As Range, IntervaloRetorno As Range) As String
Dim Valor, Nome
Dim k As Integer
Application.Volatile
k = 1
For Each Nome In IntervaloPesquisa
If Nome = NomePesquisa Then
Valor = IntervaloRetorno(k, 1)
PROCVMÚLTIPLO = PROCVMÚLTIPLO & Valor & "; "
End If
k = k + 1
Next Nome
PROCVMÚLTIPLO = Left(PROCVMÚLTIPLO, Len(PROCVMÚLTIPLO) - 2)
End Function

Já a função PROCVVÁRIOS(NomePesquisa; IntervaloPesquisa; IntervaloRetorno;Ocorrencia) utiliza um argumento adicional para determinar o nº da ocorrência correspondente.
Código:

Function PROCVVARIOS(NomePesquisa As String, IntervaloPesquisa As Range, IntervaloRetorno As Range, Ocorrencia As Integer)
Dim Nome
Dim k As Integer, i As Integer
Application.Volatile
k = 1
i = 1
For Each Nome In IntervaloPesquisa
If Nome = NomePesquisa Then
If k = Ocorrencia Then PROCVVARIOS = IntervaloRetorno(i, 1)
k = k + 1
End If
i = i + 1
Next Nome
End Function

No arquivo PROCV_MULT.XLS é possível visualizar detalhadamente a utilização dos códigos.

Abraços

Códigos para realizar ordenação de vetores

2009-12-10T22:36:00.000-02:00

Seguem abaixo dois códigos que realizam a ordenação de vetores, transferidos a partir de outras rotinas:

Function OrdenarCrescente(Vetor)

Dim k As Long

Dim i As Long

Dim Aux As Variant

For k = LBound(Vetor) To UBound(Vetor)

For i = LBound(Vetor) To UBound(Vetor) - 1

If Vetor(i) > Vetor(i + 1) Then

Aux = Vetor(i)

Vetor(i) = Vetor(i + 1)

Vetor(i + 1) = Aux

End If

Next i

Next k

End Function

Function OrdenarDecrescente(Vetor)

Dim k As Long

Dim i As Long

Dim Aux As Variant

For k = LBound(Vetor) To UBound(Vetor)

For i = LBound(Vetor) To UBound(Vetor) - 1

If Vetor(i) < Vetor(i + 1) Then

Aux = Vetor(i)

Vetor(i) = Vetor(i + 1)

Vetor(i + 1) = Aux

End If

Next i

Next k

End Function

[ ]s

Código para pesquisar um valor em todas as planilhas de um arquivo

2009-12-10T21:40:00.000-02:00

O exemplo de código abaixo, permite realizar a pesquisa de um determinado valor em todas as planilhas da pasta de trabalho:

Sub PesquisaremVáriasPlanilhas1()
Dim ws          As Worksheet
Dim Pesquisa    As Variant
Dim c           As Variant
Dim Mensagem    As String
Dim k           As Integer

Pesquisa = Application.InputBox("Informe valor a ser pesquisado.", "Valor para pesquisa.")
k = 0
For Each ws In ThisWorkbook.Worksheets
With ws.Cells
Set c = .Find(Pesquisa, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Mensagem = Mensagem & c.Address & vbTab & ws.Name & vbLf

k = k + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress

End If
End With
Next ws
If k = 0 Then
MsgBox "Não foram encontradas ocorrências do valor ''" & Pesquisa & "'' nas planilhas desta pasta de trabalho."
Else
MsgBox "O valor pesquisado : ''" & Pesquisa & "'', foi localizado em :" & vbLf & Mensagem
End If
End Sub

Criando fórmulas que verifiquem múltiplas condições

2009-11-27T00:08:00.000-02:00

É comum utilizarmos a função SE para retornarmos um valor ou executarmos uma operação tomando como base o atendimento ou não de determinadas condições.
Podemos por exemplo definir a fórmula =SE(A1>=7;"Aprovado";"Reprovado") para definirmos com base na nota digitada na célula A1 se um aluno foi aprovado ou reprovado.
Poderíamos aumentar a complexidade, criando conceitos baseados no valor da nota: =SE(B8>=9;"Excelente";"")&SE(E(B8<9;B8>=8);"Muito bom";"")&SE(E(B8<8;B8>=7);"Bom";"")&SE(E(B8<7;B8>=6);"Regular";"")&SE(E(B8<6;B8>=5);"Fraco";"")&SE(E(B8<5;B8>=4);"Muito fraco";"")&SE(E(B8<4;B8>=3);"Ruim";"")&SE(E(B8<3;B8>=2);"Muito Ruim";"")&SE(B8<2;"Péssimo";"").
Desta forma, se a nota for 8,3, será atribuído o conceito "Muito bom".
Embora solucione a questão da atribuição dos conceitos a fórmula é muito extensa e pouco flexível caso desejemos revisar os intervalos de definição de cada conceito.
Para obter a mesma resposta de forma mais simples, lançaremos mão da função PROCV.
Vamos construir a seguinte tabela para parametrizar a fórmula:

	A	B	C
1	Inferior	Superior	Conceito
2	0	2	Péssimo
3	2	3	Muito ruim
4	3	4	Ruim
5	4	5	Muito fraco
6	5	6	Fraco
7	6	7	Regular
8	7	8	Bom
9	8	9	Muito bom
10	9	10	Excelente

Agora colocando o valor da nota na célula A12 e construir a fórmula: =PROCV(A12;$A$2:$C$10;3;VERDADEIRO).

Comentários:
- O uso do PROCV pode substituir a necessidade de fórmulas complexas para cobrir múltiplas condições
- Além da simplicidade da fórmula resultante, esta abordagem permite uma rápida reconfiguração de valores, bastando para isso alterar os parâmetros diretamente na tabela.

________________________________________________________________________

Identificando o caminho e o nome do arquivo

2009-11-26T22:54:00.000-02:00

Quando temos que imprimir documentos feitos no excel, é interessante identificarmos onde o arquivo se encontra armazenado, para facilitar a localização por outros usuários.
Quem trabalha com diversos formulários e precisa atender a requisitos de sistemas de certificação, sabe da necessidade de manter os seus registros sempre rastreáveis e acessíveis.
A forma mais simples de fazê-lo é utilizando a fórmula =CÉL("filename";A1), que pode ser digitada em qualquer célula da planilha do arquivo a ser identificado.
[ ]s

Função para contar ocorrências de uma palavra num intervalo

2009-11-25T16:47:00.000-02:00

O exemplo abaixo de UDF (Função Definida pelo Usuário), permite com que sejam contadas ocorrências de uma palavra no conjunto de todas as células fornecidas de um intervalo:

Function CONTARPALAVRAS(Intervalo As Range, Palavra As String) As Long
'Declarar variáveis
Dim Célula  As Range
Dim i       As Integer
'Zerar a contagem
CONTARPALAVRAS = 0
'Percorrer todas as células do intervalo
For Each Célula In Intervalo
    'Percorrer todos os caracteres da célula
    For i = 1 To Len(Célula)
        'Fazer a comparação sem diferenciar maiúsculas e minúsculas
        If UCase(Mid(Célula, i, Len(Palavra))) = UCase(Palavra) Then CONTARPALAVRAS = CONTARPALAVRAS + 1
    Next i
Next Célula
End Function

[ ]s

Tabela Dinâmica com Intervalo Dinâmico

2009-11-21T22:20:00.000-02:00

Quem trabalha ou já trabalhou com tabelas dinâmicas (TD) sabe o quanto esta ferramenta é poderosa para criação e análise de resumos de dados.
Também já deve ter passado pela tarefa de ao acrescentar novos registros ou uma nova coluna à base de dados, ter que redefinir o intervalo de dados utilizado para a construção da TD.
Para evitar este trabalho, é possível utilizar um intervalo dinâmico de forma bem simples para servir de fonte de informações para a TD.
Vamos imaginar que os dados estejam na Plan1 de um arquivo, com os cabeçalhos ocupando a linha 1.
Podemos definir o intervalo dinâmico com o nome de FonteDadosTD, com a seguinte referência:
=DESLOC(Dados!$A$1;0;0;CONT.VALORES(Dados!$A:$A);CONT.VALORES(Dados!$1:$1)).

Agora ao contruirmos a TD, no primeiro passo do tutorial, quando é informado o intervalo de dados, pressiona-se F3 e seleciona-se o nome (FonteDadosTD, no exemplo).
Pronto. Agora para novas colunas ou linhas sejam adicionadas TD, basta clicar em Atualizar Dados.

Abraços

_________________________________________________________________________

Gráficos x Intervalos Dinâmicos - Exemplo 2

2009-11-20T11:17:00.000-02:00

Dando prosseguimento às aplicações de intervalos dinâmicos em gráficos, vamos ver um exemplo de construção de um gráfico móvel, com base nos n últimos valores de uma serie de dados.
Crie um novo arquivo e nomeie-o como Gráfico_Móvel.
Na Plan1, insira os valores:

	A	B
1	Rótulos	Valores
2	A	12
3	B	15
4	C	16
5	D	17
6	E	18
7	F	15
8	G	25
9	H	26
10	I	15
11	J	14
12	K	29
13	L	35
14	M	65
15	N	25

Agora, defina os seguintes intervalos nomeados:
N_Pontos; N2
Dados_Gráfico; =DESLOC(Plan1!$A$2;CONT.VALORES(Plan1!$A:$A)-N_Pontos-1;0;N_Pontos;1)
Rótulos_Gráfico; =DESLOC(Plan1!$B$2;CONT.VALORES(Plan1!$B:$B)-N_Pontos-1;0;N_Pontos;1)
Insira na célula N2 o valor 14.
Em seguida, crie um gráfico com os dados das colunas A e B.
O resultado obtido, deve ser similar a este aqui:

Clique na série de dados, para que apareça a “função” SÉRIES na barra de ferramentas.
Edite a função, alterando-a de
=SÉRIE(Plan1!$B$1;Plan1!$A$2:$A$15;Plan1!$B$2:$B$15;1)
Para
=SÉRIE(Plan1!$B$1;Gráfico_Móvel.xls!Rótulos_Gráfico;Gráfico_Móvel.xls!Dados_Gráfico;1)
Na célula N4, digite a fórmula ="Gráfico com os últimos "&N_Pontos&" pontos."
Selecione o Título do gráfico, clique na barra de fórmulas e digite =N4.
Altere o valor de N2 para 5. O resultado deve ser parecido com este aqui:

Experimente outros valores e observe como o gráfico se ajusta automaticamente.
Comentários:
- Você pode restringir os valores a serem digitados em N2 para evitar erros na montagem do gráfico, por meio da validação de intervalos.
- Acesse o arquivo com o gráfico pronto, em:
http://www.4shared.com/file/37537658/e7496807/Grfico_Mvel.html

________________________________________________________________________

Gráficos x Intervalos Dinâmicos - Exemplo 1

2009-11-19T23:01:00.000-02:00

Conforme vimos no tópico sobe intervalos dinâmicos, é possível extender sua utilização para diferentes funcionalidades do Excel.
Veremos agora uma forma de utilização em um gráfico.
Praticamente todo usuário de excel já teve que passar pela atividade de atualizar seus gráficos de acompanhamento de algum indicador, a cada novo conjunto de dados adicionado à planilha. E provavelmente passou pelo constrangimento de descobrir que o gráfico não estava atualizado no momento em que realizava uma apresentação importante.
O objetivo é criar um gráfico que incorpore à série plotada, novos valores à medida que são digitados, sem necessidade de adicionar os novos dados manualmente.
Crie um novo arquivo no Excel e nomei-o como Gráfico_Intervalo_Dinamico.xls.
Na Plan1 deste arquivo, digite os seguintes valores nas células:

	A	B
1	Meses	Valores
2	Janeiro	10.000
3	Fevereiro	20.000

Vamos agora definir os intervalos dinâmicos:
Meses: =DESLOC(Plan1!$A$2;0;0;CONT.VALORES(Plan1!$A:$A)-1;1)
Valores: =DESLOC(Plan1!$B$2;0;0;CONT.VALORES(Plan1!$B:$B)-1;1)

Agora construa um gráfico, por exemplo colunas, a partir do intervalo Plan1!A1:B3

O resultado obtido, deve assemelhar-se a este aqui:

Se clicarmos na série de dados, veremos que na barra de fórmulas aparece o conteúdo destacado abaixo:

Edite a fórmula =SÉRIE(Plan1!$B$1;Plan1!$A$2:$A$3;Plan1!$B$2:$B$3;1); deixando-a com a seguinte sintaxe =SÉRIE(Plan1!$B$1;Gráfico_Intervalo_Dinamico.xls!Meses;Gráfico_Intervalo_Dinamico.xls!Valores;1)

Experimente agora digitar novos valores, nas células das colunas A e B, como no exemplo a seguir e observe o que ocorre com o gráfico.

Comentários:
- Observe que além do nome do intervalo, é necessário inserir na fórmula SÉRIES, o nome do arquivo.
- O raciocínio mostrado nese tópico pode ser estendido para os outros modelos de gráficos do Excel, independentemente do número e do tamanho das séries dos gráficos.
- Acesse o exemplo pronto, no link: http://www.4shared.com/file/34489507/657cb090/Grfico_Intervalo_Dinamico.html

Até a próxima
_________________________________________________________________________

Criando intervalos dinâmicos

2009-11-18T23:27:00.000-02:00

Uma das formas de melhorar a sintaxe e a interpretação de fórmulas complexas é a utilização de intervalos nomeados (Inserir >> Nomes >> Definir).
Uma outra funcionalidade para os intervalos nomeados é a possibilidade de criação de intervalos que se ajustem à medida que novas informações sejam inseridas.
Se tivermos, por exemplo, dados digitados no intervalo A1:A10 e quisermos somá-los, basta criar a função =SOMA(A1:A10).
Mas, e se depois tivermos que acrescentar mais informações ao conjunto de dados, de que forma podemos acrescentar esses dados automaticamente à função, eliminando a necessidade de alterar a fórmula?
Neste contexto é que entram os intervalos dinâmicos.
Antes de realizarmos um exemplo de utilização, vamos analisar o uso da função DESLOC.
Esta função, em sua sintaxe completa torna-se =DESLOC(Ref;Deslocamento_Vertical;Deslocamento_Horizontal;Altura;Largura).
Ref: É a célula a partir da qual será construído um novo intervalo;
Deslocamento_Vertical: Nº de linhas acima (valor negativo) ou abaixo (valor positivo) em que se iniciará o novo intervalo;
Deslocamento_Horizontal: Nº de colunas à direita (valor negativo) ou à esquerda (valor positivo) em que se iniciará o novo intervalo;
Altura: Nº de linhas do novo intervalo;
Largura: Nº de colunas do novo intervalo.
Exemplo =DESLOC(B5;-1;2;4;3).
Interpretação: Criar um intervalo, tal que o seu canto superior esquerdo está uma linha acima e duas colunas à direita de B5 (Ou seja, a célula D4). Este intervalo terá 4 linhas e 3 colunas (Ou seja, D4:F7).
Criada a familiaridade com a função DESLOC, vamos utilizá-la para construir um intervalo dinâmico.
No exemplo que citamos para a função SOMA(A1:A10), precisamos que o intervalo se adapte ao número de linhas inseridas na coluna A. Podemos então, utilizando a lógica abordada, utilizar a função DESLOC da seguinte forma =DESLOC(A1;0;0;CONT.VALORES($A:$A);1). Neste contexto, utilizamos a função CONT.VALORES para determinar o número de linhas da coluna A que possuem valores. (É importante frisar que estamos assumindo que o intervalo é contínuo, neste exemplo).
Poderíamos então reescrever a função SOMA como =SOMA(DESLOC(A1;0;0;CONT.VALORES($A:$A);1)).
Particularmente, acho que é uma sintaxe de difícil interpretação.
Fazendo uso de um intervalo nomeado, podemos seguir o caminho (Inserir >> Nomes >> Definir). Em Nomes da pasta de trabalho, podemos escrever Valores_da_Coluna_A, e na caixa Refere-se a, digitamos =DESLOC(A1;0;0;CONT.VALORES($A:$A);1), pressionando Ok em seguida.
Assim poderemos simplificar a nossa soma para =SOMA(Valores_da_Coluna_A), tornando-a muito mais fácil de ser interpretada.

Comentários adicionais.

Para os usuários da versão 2007, o caminho para inserção de nomes é: Aba Fórmulas >> Seção: Nomes Definidos; Botão: Definir Nomes). Em seguida, seguir os mesmos passos já descritos.

Esta é apresentação inicial da utilização, voltaremos ao tema com aplicações conjunta com outras ferramentas do Excel, tais como Gráficos e Tabelas Dinâmicas.

Até breve.

_____________________________________________________________

Ordenação automática de um intervalo de células

2009-11-15T22:12:00.000-02:00

Esta dica permite criar um intervalo automaticamente ordenado, de forma decrescente, a partir de um outro intervalo.
Ex: Temos uma lista de nomes de Regiões do Brasil e os correspondentes valores de receitas de uma empresa. Ao lado desta lista temos a lista resultante já ordenada automaticamente

A seguir, temos a visão da mesma planilha exibindo, desta vez as fórmulas que realizam esta ordenação:

Explicações:

Na coluna F temos as fórmulas matricias que geram uma lista ordenada decrescentemente

Utilizamos a função MAIOR para retornar a série de valores.

O argumento inicial da função (Intervalo + LIN(Intervalo/1000000000)) cria uma série de valores introduzindo uma diferenciação para o caso de haver valores repetidos. O segundo argumento (LIN(Célula)) permite que ao ser copiada, a fórmula automaticamente se ajuste para comportar o 1º maior, depois o 2º maior e assim sucessivamente.

Nas fórmulas da coluna E, utilizamos a função =CORRESP(Valor; Intervalo+LIN(Intervalo)/1000000000;0) para retornar a posição ocupada pelo elemento Valor na série ordenada. Finalmente a função ÍNDICE() retorna o nome equivalente de região.

OBS:

O conceito pode ser aplicado para intervalos de qualquer tamanho.

Para obter-se uma lista ordenada de forma crescente, basta substituir a função MAIOR() pela função MENOR().

Calculando o dígito verificador (módulo 11)

2009-11-14T23:33:00.000-02:00

Eis uma solução que realiza o cálculo, utilizando apenas as funções do Excel:

=MOD(SOMARPRODUTO((EXT.TEXTO(A1;LIN(INDIRETO("A1:A"&NÚM.CARACT(A1)-1));1))*MAIOR(LIN(INDIRETO("A2:A"&NÚM.CARACT(A1)));LIN(INDIRETO("A1:A"&NÚM.CARACT(A1)-1))))*10;11)

_______________________________________________________

Somas e contagens condicionais

2009-11-14T22:43:00.000-02:00

Uma das dúvidas mais frequentes que vejo aparecer nos fóruns é como realizar uma soma ou contagem de valores, com base em mais de uma condição ou utilizando condições mais complexas.
As funções SOMASE e CONT.SE embora resolvam uma boa gama de problemas, não atendem a questões com maior número de condições. Com a versão 2007 do Excel, fomos brindados com as funções SOMASES e CONT.SES, que ampliaram a capacidade de suas antecessoras, mas que possuem limitações no seu uso. Tente, por exemplo, contar quantos usuários em uma lista possuem a letra "A" como inicial do seu primeiro nome, utilizando CONT.SE ou CONT.SES.
O uso de fórmulas matriciais provê a solução para quase 100% destas questões, porém geram dificuldades de compreensão em usuários não iniciados.
Por outro lado, a função SOMARPRODUTO se ajusta muito bem aos mais diferentes casos de contagens e somas condicionais, tendo uma sintaxe relativamente simples e consumindo menos recursos de memória do que as soluções matriciais equivalentes.
Vamos ilustrar algumas possibilidades de uso desta função, utilizando uma base de dados bem simples:

Data	Cliente	Valor da Compra
1/mar	João	R$ 1.665
1/mar	José	R$ 1.384
1/mar	Antônio	R$ 1.577
1/mar	Pedro	R$ 1.675
1/mar	Paulo	R$ 1.738
1/mar	Maria	R$ 2.252
1/mar	Isabel	R$ 2.482
2/mar	João	R$ 1.328
2/mar	José	R$ 955
2/mar	Antônio	R$ 888
2/mar	Pedro	R$ 1.452
2/mar	Paulo	R$ 2.465
2/mar	Maria	R$ 1.651
2/mar	Isabel	R$ 1.875
3/mar	João	R$ 1.698
3/mar	José	R$ 659
3/mar	Antônio	R$ 1.618
3/mar	Pedro	R$ 2.180
3/mar	Paulo	R$ 1.436
3/mar	Maria	R$ 620
3/mar	Isabel	R$ 1.599
4/mar	João	R$ 717
4/mar	José	R$ 1.179
4/mar	Antônio	R$ 2.489
4/mar	Pedro	R$ 778
4/mar	Paulo	R$ 2.424
4/mar	Maria	R$ 895
4/mar	Isabel	R$ 685
5/mar	João	R$ 2.290
5/mar	José	R$ 902
5/mar	Antônio	R$ 2.315
5/mar	Pedro	R$ 1.738
5/mar	Paulo	R$ 2.199
5/mar	Maria	R$ 2.046
5/mar	Isabel	R$ 1.922

Se inserirem a listagem acima numa planilha a partir da célula A1, verão que o intervalo dos dados se estenderá até a linha 36 da coluna C.
Proponho em seguida algumas questões que responderemos em seguida utilizando a função SOMARPRODUTO.
1ª Questão: Qual o valor comprado pelos clientes no dia 04/mar?
2ª Questão: Qual o valor comprado entre os dias 01 e 03/mar?
3ª Questão: Qual o valor das compras realizadas pelo Pedro?
4ª Questão: Qual o valor das compras realizadas por Maria e Isabel
5ª Questão: Qual o valor das compras do Antônio nos dias 01/mar e 05/mar?
6ª Questão: Quantas compras foram realizadas por clientes com a inicial "J"?

Usaremos a seguinte sintaxe para a função: =SOMARPRODUTO((Argumento1)*(Argumento2)*(Argumento3)*...*(ArgumentoN)), onde cada argumento representa um intervalo de dados que pode, ou não, apresentar uma condição. A função trabalha, multiplicando o 1º elemento do 1º argumento, pelo 1º elemento do 2º argumento, e assim por diante até o 1º elemento do argumento N. Depois faz o mesmo com o 2º elemento, com o 3º e assim por diante, somando em seguida todos esses produtos.

1ª Questão
Antes da resposta é importante compreender como podemos informar ao Excel um valor de data, sendo que não podemos digitar 04/03/2009 diretamente em uma fórmula, pois seria interpretado como uma divisão de valores. As formas mais comuns, e igualmente eficazes, são:
a. Informar o número serial correspondente: 39876, que corresponde ao número de dias contados desde 01/01/1900 até a data corrente;
b. Utilizar a função DATA (=DATA(2009;3;4));
c. Utilizar a função Valor (=VALOR("04/03/2009").
d. Indicar uma célula que contenha o valor da data (por exemplo; $D$2)
Agora que sabemos com inserir uma data numa fórmula, vamos à condição propriamente dita. Queremos contabilizar todos os dias, presentes na coluna A, que sejam iguais a 04/mar. Desta forma nosso primeiro argumento será ($A$2:$A$36=VALOR("04/03/2009"))
Agora teremos que inserir o argumento que representa os valores a serem somados. No nosso caso ($C$2:$C$36). Temos então que a nossa fórmula fica assim =SOMARPRODUTO(($A$2:$A$36=VALOR("04/03/2009"))*($C$2:$C$36)), sendo o resultado igual a R$ 9.167.

2ª Questão:
Creio que podemos partir direto para a solução
=SOMARPRODUTO(($A$2:$A$36>=VALOR("01/03/2009"))*($A$2:$A$36<=VALOR("03/03/2009"))*($C$2:$C$36))
Onde :
Argumento1 representa todos os dias a partir de 01/03;
Argumento2 representa todos os dias anteriores a 03/03, incluindo também esta data.
O produto de Argumento1 * Argumento2, tem como resultante todos os dias compreendidos neste intervalo (matematicamente falamos que é a intersecção dos intervalos)
Finalmente Argumento3 representa os valores a serem somados com base nestas condições.

3ª Questão
=SOMARPRODUTO(($B$2:$B$36="Pedro")*($C$2:$C$36))

4ª Questão
=SOMARPRODUTO((($B$2:$B$36="Maria")+($B$2:$B$36="Isabel"))*($C$2:$C$36))
Vale a pena destacar o uso do sinal de adição para as condições, criando um argumento de uma forma diferente, cuja leitura pode ser "quero destacar todos os valores comprados por Maria somados os valores comprados por Isabel".

5ª Questão
=SOMARPRODUTO(($B$2:$B$36="Antônio")*(($A$2:$A$36=VALOR("01/03/2009"))+($A$2:$A$36=VALOR("05/03/2009")))*($C$2:$C$36))
Vale lembrar que não estamos falando do intervalo entre os dias e sim de cada data individualmente.

6ª Questão
=SOMARPRODUTO(1*(ESQUERDA($B$2:$B$36)="J"))
Este último é um exemplo de contagem e que só tem um argumento. Neste caso usamos o truque de multiplicar o argumento por 1.

Espero ter lançado alguma luz sobre o tema e facilitar a vida de quem se confronta com este tipo de questão no dia a dia.
A planilha com os dados e as soluções está disponível em:
http://www.4shared.com/file/91929774/85 ... IONAL.html
______________________________________________________________________________