Macro para Agrupar varias guias no Excel - Automatização de processos

Olá Pessoal,

Esse artigo foi elaborado pela necessidade de um amigo meu que trabalha com Departamento Pessoal e utiliza relatórios exportados do sistema ERP para Excel para analisar as horas extras dos funcionários. Veja abaixo o questionamento dele:

"Todo final de mês eu gero um relatório do sistema da empresa para analisar as quantidades de Horas Extras 50% e 100% dos colaboradores. O sistema sempre gera uma guia por mês de competência ou seja se precisa analisar os últimos três meses  ao exportar para o Excel ele gera três guias. Por Exemplo: He jan, He fev e  He mar. Todas as guias geradas tem o mesmo numero de colunas e na mesma ordem, porém a quantidade de linha sempre tem uma variação. Tem como criar uma macro para agrupar as guias em uma só?"

Para ajudar a solucionar esse problema, resolvi pesquisar na internet e consegui encontrar uma macro que fazia algo um pouco diferente, porem ela foi de grande ajuda para elaborar esse artigo. Depois de analisar esse código VBA , fiz varias alterações e testes para que a macro ficasse pronta para executar o agrupamento solicitado. Segue abaixo macro final:

Para exemplificar o uso da macro eu utilizei o modelo de planilha abaixo:

A planilha Agrupado é onde o Excel irá armazenar todos os dados gerados nas planilhas HE jan, HE Fev e HE mar. Lembrando que as planilhas de HE (Hora Extra) precisam ter a mesma quantidade e ordem de colunas, porem podem ter quantidades diferentes de linhas.

A ordem das colunas do exemplo atual é demostrada na figura acima e nas colunas J e K na planilha Agrupado eu adicionei comentários de qual planilha a informação foi gerada e a data de execução da macros.

Atenção: toda vez que clicar no botão Macro o código VBA irá limpar os dados da planilha Agrupado e os novos dados serão gerados de acordo com os dados existentes nas planilhas HE.

Para ter acesso a essa planilha, deixe seu email nos comentários desse artigo.

Qualquer duvida ou sugestão entre em contato em um dos canais.

Grande Abraço,

Vagston Bueno.

Como aplicar validação de dados no Excel

Olá  Pessoal, tudo bem?

Já ouviram falar de validação de dados no Excel?

A validação de dados, assim como vários outros recursos do Excel, é muito importante e um grande facilitador no uso diário de planilhas com campos pré determinados.

Essa validação é utilizada para definir dados que podem ser digitados dentro de cada célula ou intervalo de células. Ela também pode ser utilizada para restringir o tipo dados que podem ser inseridos em uma célula. Como por exemplo: Data, Hora, Decimal, etc.

Porque utilizar a validação de Dados?

Muitas vezes já fui chamado por colegas de trabalho para verificar um problema na fórmula de uma planilha criada ou melhor dizendo verificar um "pau" no resultado de uma fórmula. Ao parar para analisar, descobri que o problema não era na fórmula e sim no texto inserido no campo originário dela. Ou seja, grande maioria dos problemas encontrados são nas entradas de dados e não na fórmula final. Nesse sentido o recurso de validação de dados obriga o usuário a entrar apenas com dados previamente cadastrados, diminuindo assim as falhas em suas planilhas e fórmulas.

Como adicionar a validação de dados em suas planilhas?

Primeiramente devemos, criar um cadastro para limitar os campos que terão entrada de dados pré definidas. Para isso, vamos utilizar um exemplo de despesas mensais onde será necessário classificar os itens por tipo de despesa, sendo elas: Administrativa, Operacional ou Comercial.

Criando a planilha cadastro e incluindo validação de dados

Crie uma planilha simples com o nome cadastro e digite conforme abaixo:

Após finalizar o cadastro vá na planilha Despesa e siga o passo a passo abaixo:

• Selecione o intervalo de células (Nesse exemplo: "C2:C17") no qual queremos incluir a validação de dados

• Clique na guia Dados > Ferramenta de Dados e em Validação de Dados

• A janela abaixo irá abrir e na guia configurações vamos selecionar na caixa Permitir a opção Lista

• Após selecionar a opção Lista irá habilitar a opção de selecionar a Fonte de dados para a validação , que nesse exemplo será a nossa planilha Cadastro e no intervalo de células "A2:A4"

• Feito isso, vamos clicar em OK e a validação de dados foi incluída na coluna Tipo de Despesas para as células C2 até C17. 

Para verificar se está funcionando vá na planilha Despesas e clica na célula C2. Você vai notar que agora existe uma caixa de seleção ao lado da célula onde você pode clicar e escolher entre as opções cadastradas na planilha Cadastro

Pronto agora você pode selecionar apenas entre as opções: Administrativa, Operacional ou Comercial. Se tentar inserir um texto diferente o Excel vai exibir uma mensagem de erro.

Com essa validação, você poderá incluir esses textos em qualquer fórmula que não terá falhas por erro de digitação.

Para acesso a planilha de exemplo, deixe seu e-mail nos comentários.

Grande abraço e até mais,

Vagston Bueno

Leia também:

Como aplicar formatação condicional no Excel

Como usar a função SE no Excel

Como aplicar formatação condicional no Excel

Olá Pessoal,

Hoje, você aprenderá como usar a formação condicional do Excel para facilitar a identificação de valores em sua planilha com case em alguns critérios, sendo eles: 5 primeiros valores, valores duplicados e valores menores que 1000.

O que é a formatação condicional?

É um recurso muito útil do Excel para aplicar cores a células com determinados valores de sua planilha. Após criada as regras e se os valores sofrerem qualquer alteração, a formatação se ajustará automaticamente, pois a formatação condicional é dinâmica.

Em resumo, a formatação condicional irá destacar os valores definidos nas regras criadas, facilitando na hora de analisar sua dados e informações. Vou dar um exemplo simples:

Vamos supor que você tenha uma grande diversidade de despesas mensais em sua empresa e você gostaria de descobrir rapidamente quais são os 5 maiores valores que elevaram seus custos administrativos.

• Selecione a coluna onde estão os valores a serem analisados

• Clique na guia Inicio > Estilos > Formatação Condicional > Regras de Primeiros/Últimos > 10 primeiros itens

• Na janela abaixo insira 5 (cinco maiores valores) e  com Preenchimento Verde e Texto Verde Escuro e clique em OK.

• O resultado da formatação condicional será as despesas Aluguel, Anuncio e publicidade, Telefone, Treinamentos e Viagens

Existem outras formas de encontrar os Top 5 no Excel e a ferramente de formatação condicional é uma delas, a diferença é que com ela você consegue colocar efeitos visuais em suas planilhas.

Tipos de Regras que podem ser criadas

Existe outros tipos de regras que você do criar na formatação condicional, sendo elas:

• Formatar todas as células com base em seus respectivos valores
• Formatar apenas células que contenham
• Formatar apenas os primeiros ou último valores (demonstrada acima)
• Formatar apenas valores acima ou abaixo da média
• Formatar apenas valores exclusivos ou duplicados
• Usar uma fórmula para determinar quais células devem ser formatadas

Como citado no inicio do artigo ainda valor exemplificar o uso das regras para valores duplicados e células que contenham valores maior que 1000.

Aplicando formatação condicional para itens duplicados

Para esse exemplo não iremos limpar a formatação já incluída para os 5 primeiros valores do exemplo anterior, portanto agora vamos seguir os passos abaixo:

• Selecione a coluna B ou melhor a coluna descrição

• Clique na guia Inicio > Estilos > Formatação Condicional > Realçar Regras das Células > Valores Duplicados

• Na janela abaixo insira células que contêm Duplicados e com Preenchimento Vermelho Claro e Texto Vermelho Escuro e clique em OK

• O resultado da formatação condicional será as descrições Condomínio, Consultoria Externa, Veiculo

Aplicando formatação condicional para valores menores que 1000

Novamente vamos manter as formatações já incluídas anteriormente para os 5 primeiros valores do exemplo anterior e para os itens duplicados na coluna descrição, portanto agora vamos seguir os passos abaixo para realçar os valores maiores que 1000:

• Selecione a coluna C ou melhor a coluna Valor

• Clique na guia Inicio > Estilos > Formatação Condicional > Realçar Regras das Células > É menor que

• Na janela abaixo insira células que são menos do que 1000 e com Texto Vermelho e clique em OK

• O resultado da formatação condicional serão os itens abaixo selecionados e com a cor de fonte vermelha

Existem diversas outras formas e métodos para aplicar regras de formatação condicionais. Agora você precisa praticar o uso dessa ferramenta em seu dia a dia para melhor o uso dela. 

Use o Excel, experimente essa ferramente de formatação condicional e encontre o que melhor funciona para você!

Caso tenha alguma dúvida, sugestão ou comentário fique a vontade.

Para acesso a planilha de exemplo, deixe seu e-mail nos comentários.

Grande Abraço,

Vagston Bueno

Leia também:

Tabela Dinâmica - Pra que serve e como criar?

Como usar a função SE no Excel

Como usar a função SE no Excel

Olá Pessoal,

Hoje é dia de falar sobre a função SE do Excel. 

O que é a função SE?

Essa função é uma das mais populares e que nos permite fazer comparações lógicas entre valores e texto para encontrar apenas aquilo que você espera. Ou seja, ele foi criada para oferecer ao usuário resultados de testes lógicos porém se tornou umas das funções mais versáteis e com grande importância dentro do Excel.

Estrutura da função

A função SE tem a seguinte estrutura:

SE(teste_lógico; [valor_se_verdadeiro]; [valor_se_falso])

• Teste_lógico: qualquer valor ou expressão que possa ser avaliada como verdadeira ou falsa. Esse campo é obrigatório
• Valor_se_verdadeiro: é o valor que deseja receber quando o teste lógico tiver como resultado verdadeiro
• Valor_se_falso: é o valor que deseja receber quando o resultado teste lógico for falso.

Para facilitar o entendimento da função SE vou deixar abaixo uma forma mais simples de sua estrutura:

 SE(Algo for verdadeiro; faça tal coisa [verdadeiro]; caso contrario faça isso [falso])

Em resumo, essa função sempre terá dois resultados sendo o primeiro se a comparação for verdadeira e o segundo se for Falsa.

Exemplo simples da função

No exemplo acima eu utilizei uma regra simples para analisar os textos da coluna A e retorno na coluna B a descrição do tipo de cadastro. Ou seja, se o cadastro é de pessoa física ou jurídica.

Para isso eu defini a função SE com a seguinte estrutura:

• Teste_lógico: A2 = "CPF"
• [valor_se_verdadeiro]: "Pessoa Física"
• [valor_se_falso]: "Pessoa Jurídica"

Sempre que na coluna A estiver o texto "CPF" o resultado verdadeiro na coluna B será "Pessoa Física". Se o texto for CNPJ ou qualquer outro valor o resultado na coluna B será "Pessoa Jurídica".

Veja abaixo o resultado ao arrastar a formula para a célula B3:

Lembrando que nesse exemplo a função SE foi usada para avaliar um texto, porem ela também pode ser utilizada para avaliar valores.

Exemplo prático da função

Para exemplificar de uma maneira focada a negócios, vamos utilizar o função SE para classificar a movimentação abaixo de CAIXA em "entrada" ou "saída" de acordo com o valor movimentado. Sendo que valores negativos correspondem a saída de caixa e os valores positivos são as entradas geradas pelas vendas.

Para iniciar o exemplo vamos clicar na célula D5 e incluir a função SE conforme abaixo:

• Teste_lógico: F5>0
• [valor_se_verdadeiro]: "Entrada
• [valor_se_falso]: "Saída"
• Função completa: =SE(F5>0,"Entrada","Saída")

Agora vamos arrasta a formula da célula D5 até a célula D21 para aplicar a função SE em todas as linhas:

Após arrastar teremos a classificação de entrada e saída para todas as movimentações de caixa realizadas:

Nesse exemplo acima utilizamos como Teste_lógico o operador de cálculo "> maior" para diferenciar o resultado verdadeiro ou falso. Caso queira saber mais sobre esses operadores, acesse o artigo abaixo:

Clique aqui para mais detalhes

Problema comum da função SE

• Se o resultado da sua função for #NOME isso normalmente significa que sua fórmula está incorreta;
• Se o resultado for o texto FALSO, significa que nenhum valor ou texto foi inserido no campo valor_se_falso

Espero que tenham gostado desse artigo e que utilizem o conhecimento aqui adquirido em seu trabalho.

Qualquer dúvida ou sugestão deixe seu comentário.

Grande Abraço

Vagston Bueno

Leia também:

Como usar a função SOMASE no Excel

Vídeo Aula - Filtro avançado no Excel com execução via Macro

Operadores de cálculo no Excel

Olá Pessoal,

Já ouviram falar de operadores de Cálculo no Excel?

Eles são de extrema importância na elaboração de fórmulas, pois especificam o tipo de cálculo lógico que se deseja efetuar nos elementos de cada função. São normalmente usados em funções do Excel em que se necessita comparar dois ou mais valores, como for exemplo na função SE. Nessa função, quando dois valores são comparados usando esses operadores, o resultado será um valor lógico, VERDADEIRO ou FALSO.

Abaixo os operadores e seu significado:

Operador de comparação	Significado
= (sinal de igual)	Igual a
> (sinal de maior que)	Maior que
< (sinal de menor que)	Menor que
>= (sinal de maior ou igual a)	Maior ou igual a
<= (sinal de menor ou igual a)	Menor ou igual a
<> (sinal de diferente de)	Diferente de

Existem outros operadores no Excel, são eles: Aritmético, Concatenação de texto e de Referência.

_______________________________________________________________________________

Operador aritmético	Significado
+ (sinal de mais)	Adição
– (sinal de menos)	Subtração
	Negação
* (asterisco)	Multiplicação
/ (sinal de divisão)	Divisão
% (sinal de porcentagem)	Porcentagem

_______________________________________________________________________________

Operador de texto	Significado
& (E comercial)	Conecta, ou concatena, dois valores para produzir um valor de texto contínuo

_______________________________________________________________________________

Operador de referência	Significado
: (dois-pontos)	Operador de intervalo, que produz uma referência para todas as células entre duas referências, incluindo as duas referências
, (vírgula)	Operador de união, que combina diversas referências em uma referência
(espaço)	Operador de interseção, que produz uma referência a células comuns a duas referências

_______________________________________________________________________________

Essa lista de operadores foi tirada do site support.office.com.

Grande abraço e até a próxima,

Vagston Bueno

Leia também:

Teclas de atalho no Microsoft Excel

Especificações e limites do Excel

Principais fórmulas Excel Português X Excel Inglês

Tabela Dinâmica - Pra que serve e como criar?

Olá Pessoal, tudo bem?

Vocês já devem ter ouvido falar sobre a Tabela Dinâmica do Excel e se não ouviram ou não dominam a ferramenta é muito importante ler com atenção esse artigo.

Esse é um tema muito útil no dia a dia das pessoas que trabalham com análise de grandes relatórios/planilhas de dados.

Como em todo novo aprendizado, as vezes pode parecer difícil saber por onde começar. Principalmente, quando se tem planilhas imensas e com um monte de informações para analisar.

Para que serve?

A Tabela Dinâmica é uma ferramenta incrível para analisar os dados rapidamente e ter as informações necessárias para a tomada decisões em apenas um clique (Botão Atualizar). Elas tem um poder de resumir, analisar e apresentar seus dados de maneira mais objetiva e clara e podem ser ajustadas rapidamente, dependente de como você deseja exibir seus resultados.

Acesse o vídeo abaixo para assistir aula completa:

Passo a passo

Como exemplo, vou utilizar como base para análise uma planilha de vendas para demonstrar o uso desse recurso do Excel:

É muito importante antes de começar a pensar em criar uma tabela dinâmica que a tabela base tenha as seguintes configurações:

• Os dados devem estar organizados em formato tabular, ou seja, com cabeçalhos por coluna;
• Os tipos de dados por colunas devem ser os mesmos, ou seja, não misture valores e textos na mesma coluna;
• Para itens chaves, mantenha um texto padrão para todos os dados. Por exemplo: tenha certeza de escrever sempre "Centro-Oeste" ao invés de "Centro Oeste", pois se existir os dois textos a tabela dinâmica erá separar esses dados como se fossem regiões diferentes.

Como criar?

Antes de começar a criar a tabela dinâmica é preciso pensar em quais resultados se deseja obter. Quais informações você precisa? Quais resultados são mais importantes para você nesse momento?

Para esse exemplo, estava buscando os seguintes resultados:

• Total de Vendas por Vendedor e por ano;
• Total de Vendas por Região e por ano;
• Total de Vendas por Produto e por ano.

Após definir o que pretendemos analisar vamos seguir os passos abaixo para criar a tabela dinâmica:

• Vá para guia Inserir > Tabelas > Tabela Dinâmica.

• Após clicar, o Excel irá exibir a caixa de diálogo para que você possa inserir seu intervalo de célula onde estão os dados bases para serem analisados. Nesse exemplo o intervalo é: 'Base Vendas'!$A$2:$H$47. Vamos selecionar a opção Nova Planilha e clicar em OK.

• Será criado uma nova planilha em seu arquivo com uma tabela dinâmica em branco com todos os campos de sua tabela base para adicionar ao relatório.

Agora vamos aplicar os campos de acordo com os resultados que estamos buscando:

• Total de Vendas por Vendedor e por ano;
• Total de Vendas por Região e por ano;
• Total de Vendas por Produto e por ano.

Para saber o Total de Vendas por Vendedor e por ano vamos adicionar os campos conforme abaixo:

• Rótulos de Coluna = Periodo
• Rótulos de Linha = Vendedor
• Valores = Valor_Venda

Para criar as outras duas tabelas dinâmicas para analisar os outros dois itens abaixo, basta alterar o campo Rótulo de Linha para Região e por ultimo para Produto

• Total de Vendas por Região e por ano;
• Total de Vendas por Produto e por ano.

Segue abaixo resultado das três tabelas dinâmicas criadas:

Caso tenha interesse em receber o arquivo modelo para esse artigo, deixe seu e-mail nos comentários.

Grande Abraço,

Vagston Bueno

Leia também:

Como criar sua primeira Macro utilizando o VBA no Excel

Como usar a função SOMASE no Excel

Vídeo Aula - Filtro avançado no Excel com execução via Macro

Olá Pessoal,

Nessa vídeo aula, vou mostrar como criar uma filtro avançado no Excel utilizando a gravação de macro do Excel para executar o filtro quantas vezes for necessário ao clique de uma botão. Veja abaixo:

Acesse o link abaixo para ver o contéudo completo com o passo a passo dessa vídeo aula:

Criando um filtro avançado no Excel com execução via Macro

Grande abraço e até a próxima,

Vagston Bueno

Leia também:

Como criar sua primeira Macro utilizando o VBA no Excel

Como usar a função SOMASE no Excel