0017-Criando o primeiro projeto com macro

Já tendo sido apresentados ao conceito de macros e ao ambiente de programação, vamos criar o nosso primeiro projeto utilizando macro, a partir de uma situação fictícia, porém nada longe da realidade de algumas empresas.

“Todos os meses, um analista recebe da matriz da sua organização, uma base de dados das vendas realizadas, que necessita de uma série de ajustes para adequar-se às suas necessidades.

A base recebida possui as seguintes informações:
Coluna A: Dia em que ocorreu a venda;
Coluna B: Matrícula do vendedor;
Coluna C: Código do produto vendido;
Coluna D: Nº de unidades vendidas;

O analista recebe tabelas adicionais, para complementar seus dados:

Tabela 1: Fonte: RH
Coluna A: Matrícula do vendedor;
Coluna B: Município em que atua;
Coluna C: Estado correspondente

Tabela 2: Fonte: Contabilidade
Coluna A: Código do produto;
Coluna B: Preço unitário de venda;
Coluna C: Estado correspondente

Com base nestas informações, o analista precisa determinar:
1. Qual o produto com maior faturamento;
2. Evolução semanal do faturamento;
3. Quais os estados com melhor e pior resultados de faturamento.”

Antes de enfiarmos a mão na massa, vamos tecer algumas considerações, às questões levantadas
1. Embora a base de dados possua as informações de unidades vendidas, não possui os dados referentes aos preços praticados. Teremos que obter esta informação da tabela 2.
2. Os dados informados, trazem o dia em que foram realizadas as vendas, mas não informam o número da semana.
3. Da mesma forma, não temos dados de venda por área geográfica, esta informação deverá ser obtida através das informações da tabela 1.

O arquivo original com os dados de vendas e as tabelas auxiliares está disponível aqui: Arquivo Original
Há 3 planilhas neste arquivo:
1. Base_vendas: Este é o arquivo enviado pela matriz. Como é exportado a partir do sistema da empresa, podemos verificar que há “lixo” em meio aos dados, correspondentes às quebras de linha do relatório originalmente emitido.
2. Tabela 1, contém as informações obtidas junto ao RH
3. Tabela 2, contém as informações enviadas pela contabilidade.

Vamos estabelecer agora os passos necessários para darmos cabo à missão:
a. Ajustar a base de dados, limpando o “lixo” e ajustando o formato da coluna que contém os dias para um formato adequado de datas
b. Adicionar uma coluna ao relatório com as informações dos estados;
c. Adicionar uma coluna ao relatório para inserirmos as informações dos preços unitários, lembrando que estes valores dependem do produto e do estado no qual são vendidos.
d. Adicionar uma coluna com o número da semana correspondente à data da venda.

A maior dificuldade enfrentadas por quem dá os primeiros passos na construção de códigos, em qualquer linguagem de programação, não é a linguagem propriamente dita, mas a definição de algoritmos que permitam compreender o problema e guiar a construção do código. Por esta razão, tentarei ser o mais detalhista possível no processo de construção dos códigos.

Limpando o lixo de Base_vendas

Podemos observar que a base de dados, além da linha cabeçalho possui linhas cujos dados da coluna A podem ser divididos em:

  • Informações de data, de forma que o terceiro caractere é sempre um ponto, como em “01.02.2011”
  • Cadeias de texto diferentes relacionadas a informações de controle do relatório, que são exportadas junto com os dados de venda.

Temos então que manter a linha de cabeçalho (linha 1) e eliminar as linhas que não contenham informações relacionadas a datas na coluna A.

Podemos traduzir isto em um comando de pseudo-linguaguem:

Se o terceiro caractere do conteúdo da coluna A, for diferente de um ponto (“.”), excluir a linha correspondente

A linha acima é que o chamamos de estrutura condicional, que em VBA será escrita por meio de um comando do tipo “If…Then…Else…”.

Como informar ao VBA que este comando deve ser executado em todas linhas da base de dados?

Resposta: temos que determinar qual a última linha que contém dados.

Há uma linha de comando específica para isto, cuja pseudo-linguagem pode ser intepretada como:

Do conjunto de células desta planilha, retorne o número da linha correspondente à última célula que contém dados

Temos que criar uma estrutura chamada de Loop, que informa quantas vezes um comando deve ser repetido. O que ocorrerá desde a linha 2 até a última linha da base.

Dica: Quando construir códigos que eliminam valores de uma base de dados, utilizando uma estrutura de loop, faça com que este seja um loop decrescente. A razão é que ao eliminarmos uma linha, o total se altera e a base é “empurrada” para cima.

Finalmente podemos escrever o código como:

Sub Eliminar_Linhas()
 'Declaração de variáveis
 Dim i As Integer
 Dim wsBase As Worksheet
'Determinar qual a planilha que será trabalhada
 Set wsBase = Sheets("Base_vendas")
'Definir o Loop que se inicia na última linha com valores da planilha
 'wsBase e conclui na 2ª linha. O Loop é decrescente o que é informado
 'pelo trecho Step -1
For i = wsBase.Cells.SpecialCells(xlCellTypeLastCell).Row To 2 Step -1
'Testar se o terceiro caractere da célula da coluna A, na linha
 'correspondente ao valor do contador i, excluindo-a se o caractere
 'não for um ponto "."
If Mid(wsBase.Cells(i, 1), 3, 1)  "." Then wsBase.Rows(i).Delete
Next i
End Sub

Formatando os dados da coluna A como Data

Esta etapa pode ser realizada de inúmeras formas. Particularmente, gosto desta que segue abaixo, e que é baseada na instrução Texto para coluna, utilizado em conversão de dados importados pelo Excel.

Sub Ajustar_Datas()
    Columns("A:A").TextToColumns _
    DataType:=xlDelimited, _
    FieldInfo:=Array(1, 4)
End Sub

Inserindo uma coluna com os estados

Embora na base original não sejamos informados sobre a localização geográfica na qual ocorreu a venda, na Tabela 1 temos uma correlação entre os números de matrícula dos vendedores e os correspondentes estados nos quais atuam.

Os usuários mais experientes dirão que basta realizar um PROCV trazendo as informações correspondentes para a base de vendas. E é exatamente o que faremos, mas utilizando o VBA ao invés de escrevermos as fórmulas.

As funções que utilizamos nas planilhas podem ser “chamadas” via VBA através de um conjunto chamado Worksheetfunction. Falando especificamente do PROCV, vamos utilizar o seu correspondente em inglês VLOOKUP.

Os nomes dos estados serão inseridos na coluna E, ao lado da base de dados modificada.

Sub Inserir_Estados()

'Declaração de variáveis
Dim i       As Integer
Dim wsBase  As Worksheet
Dim Tabela1 As Range

'Determinar qual a planilha que será trabalhada
Set wsBase = Sheets("Base_vendas")

'Determinar o intervalo que contém os dados a serem retornados
Set Tabela1 = Sheets("Tabela 1").[B2:D8]

'Inserir o cabeçalho de dados
[E1] = "Sigla_Estado"

'Loop para percorrer as linhas da base de dados
For i = 2 To wsBase.Cells.SpecialCells(xlCellTypeLastCell).Row
On Error Resume Next
'Inserir na coluna E, o estado correspondente à matrícula do vendedor
wsBase.Cells(i, 5) = Application.WorksheetFunction.VLookup(wsBase.Cells(i, 2), Tabela1, 3, False)
Next i

End Sub

Inserindo uma coluna com os preços unitários e calculando o faturamento

Observando a Tabela 2, temos que o valor do preço unitário depende do produto e do estado no qual é vendido. Ao contrário da etapa anterior, nesta não poderemos utilizar um PROCV já que este aceita um critério apenas como pesquisa. Ao invés disto, utilizaremos a função SOMASES (SUMIFS) já que esta permite múltiplos critérios para filtragem de um valor. Desta forma será possível incluir na coluna F o valor unitário de venda e em seguida obteremos o valor do faturamento na coluna G, através do produto do valor unitário pela quantidade vendida.

Chegamos então ao seguinte código:

Sub Preencher_Valores()

'Declaração de variáveis
Dim i           As Integer
Dim wsBase      As Worksheet
Dim rgValores   As Range
Dim rgEstados   As Range
Dim egProdutos  As Range

Set wsBase = Sheets("Base_vendas")
Set rgValores = Sheets("Tabela 2").[C1:C13]
Set rgEstados = Sheets("Tabela 2").[A1:A13]
Set rgProdutos = Sheets("Tabela 2").[B1:B13]

'Inserir os cabeçalhos de dados
[F1] = "Valor_Unit"
[G1] = "Faturamento"

'Loop para percorrer as linhas da base de dados
For i = 2 To wsBase.Cells.SpecialCells(xlCellTypeLastCell).Row
On Error Resume Next

'Inserir na coluna F, o valor de venda unitário com base no estado e no produto e formatar a célula como moeda
wsBase.Cells(i, 6) = Application.WorksheetFunction.SumIfs(rgValores, rgEstados, wsBase.Cells(i, 5), rgProdutos, wsBase.Cells(i, 3))
wsBase.Cells(i, 6).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

'Inserir na coluna G, o faturamente como o produto do valor unitário pela quantidade vendida e formatar a célula como moeda
wsBase.Cells(i, 7) = wsBase.Cells(i, 6) * wsBase.Cells(i, 4)
wsBase.Cells(i, 7).NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
Next i

End Sub

Inserindo o número da semana

Como último ajuste da base de dados, vamos inserir uma coluna com o número da semana correspondente à data de venda. Vamos fazê-lo utilizando a função NUM.SEMANA (WEEKNUM). Eis como fica o código;

Sub Informar_Semana()

'Declarar variáveis
Dim i       As Integer
Dim wsBase  As Worksheet

Set wsBase = Sheets("Base_vendas")

'Inserir o cabeçalho na coluna H
wsBase.[H1] = "Semana"

'Loop para percorrer as linhas da base de dados
For i = 2 To wsBase.Cells.SpecialCells(xlCellTypeLastCell).Row
wsBase.Cells(i, 8 ) = Application.WorksheetFunction.WeekNum(wsBase.Cells(i, 1))
Next i

End Sub

Respondendo às questões levantadas

Agora que obtivemos uma base de dados devidamente ajustada, podemos responder às questões levantadas, utilizando desta vez duas tabelas dinâmicas, numa nova planilha inserida no arquivo, através das quais podemos facilmente enxergar as respostas.

O código para a sua inclusão é:

Considerações finais

O leitor mais familiarizado com programação irá perceber que há estruturas repetitivas nos diversos códigos anteriores. Realmente isso ocorreu devido à necessidade de explorar cada situação separadamente. Podemos agora reunir os códigos

  • Eliminar_Linhas;
  • Ajustar_Datas;
  • Inserir_Estados;
  • Preencher_Valores;
  • Informar_Semana.

Num código único conforme segue:

Sub Eliminar_Linhas()
'Declaração de variáveis
Dim i       As Integer
Dim wsBase  As Worksheet
Dim Tabela1 As Range

'Determinar qual a planilha que será trabalhada
Set wsBase = Sheets("Base_vendas")

'Determinar o intervalo que contém os dados a serem retornados da Tabela 1
Set Tabela1 = Sheets("Tabela 1").[B2:D8]

'Determinar os intervalo que contêm os dados a serem retornados da Tabela 2
Set rgValores = Sheets("Tabela 2").[C1:C13]
Set rgEstados = Sheets("Tabela 2").[A1:A13]
Set rgProdutos = Sheets("Tabela 2").[B1:B13]

'Ajustar a formatação das datas
Columns("A:A").TextToColumns _
DataType:=xlDelimited, _
FieldInfo:=Array(1, 4)

'Inserir os cabeçalho de dados
wsBase.[E1] = "Sigla_Estado"
wsBase.[F1] = "Valor_Unit"
wsBase.[G1] = "Faturamento"
wsBase.[H1] = "Semana"

'Definir o Loop que se inicia na última linha com valores da planilha
'wsBase e conclui na 2ª linha. O Loop é decrescente o que é informado
'pelo trecho Step -1

For i = wsBase.Cells.SpecialCells(xlCellTypeLastCell).Row To 2 Step -1

'Testar se o terceiro caractere da célula da coluna A, na linha
'correspondente ao valor do contador i, excluindo-a se o caractere
'não for um ponto "."

If Not IsDate(wsBase.Cells(i, 1)) Then

'Se a condição for verdadeira, a linha é excluída
wsBase.Rows(i).Delete

Else

'Inserir na coluna E, o estado correspondente à matrícula do vendedor
wsBase.Cells(i, 5) = Application.WorksheetFunction.VLookup(wsBase.Cells(i, 2), Tabela1, 3, False)

'Inserir na coluna F, o valor de venda unitário com base no estado e no produto e formatar a célula como moeda
wsBase.Cells(i, 6) = Application.WorksheetFunction.SumIfs(rgValores, rgEstados, wsBase.Cells(i, 5), rgProdutos, wsBase.Cells(i, 3))
wsBase.Cells(i, 6).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

'Inserir na coluna G, o faturamente como o produto do valor unitário pela quantidade vendida e formatar a célula como moeda
wsBase.Cells(i, 7) = wsBase.Cells(i, 6) * wsBase.Cells(i, 4)
wsBase.Cells(i, 7).NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"

'Inserir o número da semana na coluna H
wsBase.Cells(i, 8 ) = Application.WorksheetFunction.WeekNum(wsBase.Cells(i, 1))

End If
Next i

End Sub

Desta forma, reduzimos o tempo total de processamento evitando redundâncias.

O arquivo final após os ajustes e a criação das Tabelas Dinâmicas está disponível aqui: Arquivo Final

Anúncios

In Excel We Trust

Marcado com: ,
Publicado em Nível: Avançado

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: