Nos códigos acima, como podem notar, eu deixei um comentário de uma dica de atalho para cada alteração no caractere de milhar e decimal no Excel, ou seja, para manter no formato Brasil basta pressionar Ctrl+Q, caso queira para mudar para o formato EUA basta pressionar Ctrl+W.
Para incluir essas macros abra o Visual Basic no Excel, pressione Alt+F11 no seu teclado, e insira um módulo e digite o código acima. Após essa digitação, salve o arquivo e feche a tela do Visual Basic.
Para incluir os atalhos mencionados acima. Vá para guia Desenvolvimento em sua barra de ferramentas e selecione a opção Macros. Selecione a macro FormatBrazil e clique no botão opções. Irá abrir um outra janela com a opção para incluir uma tecla de atalho. Digite "q", clique em OK e repita o mesmo processo para a macro FormatUSA.
Caso tenha duvidas sobre macros e queira aprender um pouco mais acesse os links abaixo com outros artigos super interessantes sobre esse assunto.
Muitas pessoas já me perguntaram o que é essa tal de macro do Excel? Pra que ela serve? O que ela faz?
Hoje eu tenho certeza que a melhor resposta para essa pergunta é uma outra pergunta:
O que uma macro no Excel NÃO faz?
Atualmente, o Excel tem se tornado uma ferramenta muito utilizada, seja para uso pessoal ou profissional. E todo dia nos deparamos com a necessidade de executar tarefas repetidas dentro do Excel, seja na digitação de textos, na classificação de dados, nos filtrosou até mesmo na necessidade de alteração de dados em massa. É nesse momento que as macros podem ser úteis em qualquer um desses trabalhos.
Uma macro é nada mais que, uma sequência de comandos ou funções que são armazenadas em um módulo VBA (Visual Basic) e pode ser usada para automatizar qualquer tarefa repetitiva, visando melhorar a produtividade nas etapas mais demoradas de um processo padrão.
Agora, você deve estar se perguntando: Mas como eu crio essas macros? Basicamente, existem duas formas: usando o gravador de macro padrão do Excel ou atráves do uso de um código de VBA. Abaixo deixo alguns links para introdução ao uso das macros:
Uma tarefa muito importante antes de inicar a gravação de uma macro é analisar e planejar todas as etapas e comandos que você deseja que seja automatizado. Qualquer erro cometido durante a gravação serão armazenados dentro na macro, quando utilizado o metódo padrão gravador de macro do Excel, e executados todas vezes que a macro for ativado. Portanto, é muito importante planejar e desenhar o passo a passo da tarefa em que deseja aplicar essa ferramenta, pois só assim será possivel aproveitar toda sua funcionalidade. Abaixo foi deixar alguns links mostrando como as macros podem ser uteis:
Em resumo, macros economizam nosso tempo. Elas nos permitem realizar trabalhos demorados em alguns minutos ou até mesmo em segundos. Sem contar, que uma boa gravação de macro traz benefícios maiores, tais como: diminuição de erros e padronização de processos.
Não posso deixar de comentar que: "A pratica leva a perfeição". Portanto, estudem a ferramenta, comecem a utilizar com tarefas simples e vá evoluindo aos poucos.
A internet nos permite ter acesso a uma infinidade de exemplos e de artigos para qualquer tipo de estudo e a Excel VBA para Negócios surgiu com base nessa ideia.
Alterando o caractere separador de milhares ou decimais - Excel VBA para Negócios
Olá Pessoal, tudo bem?
Todos que usam o Excel sabem que por padrão os separadores de sistema de milhares ou decimais é de acordo com o que está definido nas configurações regionais no painel de controle do seu computador. Ou seja, se seu computador estiver em Inglês o formato de numero será 1,000.00 e se o computador estiver em Português o formato será 1.000,00. E se eu quiser alterar isso? Como eu faço?
É simples e não iremos modificar as configurações regionais do seu computador. Segue abaixo o passo a passo:
Clique em Arquivo e depois em Opções.
Em algumas versões do office, clique no Botão do Microsoft Office imagem do botão office e em Opções do Excel.
Na guia Avançado, em Opções de edição, desmarque a caixa de seleção Usar separadores do sistema.
Digite novos separadores nas caixas Separador decimal e Separador de milhar.
Pronto! Dessa forma você pode configurar seu Excel da forma que achar melhor para o seu dia a dia.
Em breve estarei postando aqui, duas macros de como alterar para o formato USA ou BRASIL com um atalho de teclado.
Já parou para pensar porque os cursos de Excel de hoje em dia não te ajudam a alcançar os seus objetivos? Você já passou por problemas do tipo:
Fazer um curso durante algum tempo e não conseguir aprender muita coisa;
Esquecer as funções que aprendeu e não conseguir aplicar em sua empresa;
Ouvir alguém falar do Excel e parecer que está em outro País.
Pela experiência que tenho posso afirmar que o Excel é uma ferramenta em que precisa ser utilizada com certa frequência, ou seja, você precisa fazer do Excel uma prática diária. Comece a praticar com tarefas simples, seja colocando um filtro em sua planilha ou então utilizando a formatação condicional para sinalizar os itens mais importantes.
Pare de fazer trabalhos manuais, ou seja, comece a gravar macros em suas planilhas e também aproveite todo o potencial do Excel através da ferramenta de localizar e substituir.
Existem varias formas de você aplicar o aprendizado adquirido no Excel em sua rotina de trabalho, por exemplo, através do uso das teclas de atalhos ou criando gráficos para facilitar a analise de seus resultados ou projetos executados.
O Excel tem uma infinidade de recursos que podem ajudar você a automatizar tarefas e ganhar tempo em suas tarefas ou até mesmo impressionar seu chefe com a criação de um filtro avançado para facilitar a busca em suas planilhas com cadastros em geral.
A Excel VBA para Negócios quer te conectar com essas possibilidades e fazer com que sua evolução no conhecimento do Excel, te traga benefícios pessoais e profissionais. Por isso, acompanhe a página junto comigo e vamos crescer juntos, pois todo mundo tem algo a aprender.
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.
É exatamente isso. O Excel tem seus limites! Essa semana
mesmo, estava trabalhando em uma planilha de Excel 2013 e me deparei com um
erro.
Ao investigar o problema, notei que tinha atingido o limite
máximo de hiperlinks (66.530 hiperlinks) na minha
pasta de trabalho e para minha surpresa descobri que o Excel tem vários outros limites
para cada recurso disponível. Vou listar os que considero mais importante
abaixo:
*aplicável para Excel 2016-2013-2010-2007
Recurso
Limite máximo
Largura da coluna
255 caracteres
Altura da linha
409 pontos
Número total de caracteres que uma
célula pode conter
32.767 caracteres
Formatos/estilos de célula exclusivos
64000
Formatos de número em uma pasta de
trabalho
Entre 200 e 250, dependendo da versão
do idioma do Excel instalado
Hiperlinks em uma planilha
66.530 hiperlinks
Referências classificadas
64 em uma única classificação;
ilimitado quando usar classificações seqüenciais
Campos em um formulário de dados
32
Itens exibidos nas listas suspensas
do filtro
10.000
Comprimento do conteúdo da fórmula
8.192 caracteres
Comprimento interno da fórmula
16.384 bytes
Argumentos em uma função
255
Estou disponibilizando abaixo o link de suporte da microsoft
com todas as especificações e limites de planilha, caso encontrem um problema
diferente dos mencionados acima.
Essa pode parecer uma função simples do Excel, mas se usada
da maneira correta ela pode lhe ajudar a corrigir datas digitadas
incorretamente (Ex: 01/01/2016 ao invés de 01/01/2017) , ou então alterar em
massa a classificação de quantidade de um produto cadastrado. (Ex: alterar de
CAIXA para PEÇA ou UNIDADE).
Vamos ao que interessa, segue abaixo como utilizamos esse
recurso no Excel:
*aplicável no Excel 2016, 2013, 2010
e 2007.
1. Clique em qualquer célula da
planilha em que deseja localizar o valor.
2. Na guia/aba Página Inicial, grupo Edição,
clique em Localizar e Selecionar.
3. Selecione uma das opções:
Localizar: Para apenas procurar o valor.
Substituir: Para localizar o valor e substituir com o
novo valor.
Função Localizar
Na guia localizar podemos usar caracteres curingas, como por exemplo um asterisco (*) ou o interrogação (?) para incluir um critério de pesquisa.
Asterisco (*): Use para localizar uma cadeia de caracteres. Por exemplo, p*t localizará “produto” e “protocolo”.
Interrogação (?): Use para localizar um único caractere. Por exemplo,p?o localizará “programado” e “processado”, mas não localizará “privado”.
Existe também uma opção para definir ainda melhor sua pesquisa, ou seja, clique em Opções e siga uma das instruções abaixo:
Na caixa Em, selecione por Planilha ou Pasta de Trabalho.
Na caixa Pesquisar, clique em Por linhas ou Por Colunas.
Na Caixa Examinar, selecione por Formulas, Valores ou Comentários.
Função Substituir
Na guia Substituir, digite o texto de substituição na caixa Substituir por (ou deixe em branco para substituir células vazias) e clique em Localizar ou Localizar Tudo. Nessa guia existem as mesmas funções do item 4 explicado acima.
Caso queira cancelar uma pesquisa em andamento, basta pressionar a tecla ESC do seu teclado.
Agora é só estudar essa ferramenta, entender o seu funcionamento e adaptar em sua rotina diária. Eu particularmente uso muito por aqui e acho que ela é essencial para minha atividade.
Em caso de duvidas, deixe seu comentário no blog ou no Facebook. Não deixe de compartilhar e divulgar esse post com outras pessoas do seu grupo de contatos.
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.
Neste post veremos como usar a função Soma do Excel para somar linhas, colunas e células separadas.
Hoje estou inaugurando o meu canal no youtube com a primeira vídeo aula e gostaria de saber a opinião de todos. Seu comentário e sugestão é de grande valor! Abaixo link do vídeo:
Estou disponibilizando o artigo abaixo para aqueles que gostam de acompanhar um passo a passo.
Para esse exemplo vamos utilizar a planilha abaixo como exemplo:
Para ter acesso a planilha de exemplo, baixe o arquivo em Excel no link abaixo:
Iremos calcular na coluna J o total das despesas por mês, por trimestre e por ano. Na linha 16 iremos calcular o valor trimestral e anual para cada tipo de despesa listada na linha 2. Todos os cálculos serão feitos utilizando a função Soma.
Para facilitar, vamos separar essa explicação em duas partes:
Soma horizontal
Soma Vertical
Soma com seleção de células diversas
Soma Horizontal
Vamos começar somando as despesas mensais por mês, com o intuito de descobrir o total gasto por mês de uma determinada família. A Soma será feita na coluna J onde iremos escrever a seguinte função =SOMA(C4:I4)
Após pressionar a tecla Enter você terá como resultado a somo dos valores das células C4 até I4 no total de 2.248,40
Para repetir a soma da linha J4 para os outros meses do ano (célula J5 até J15) iremos utilizar a função Arrastar do Excel. Basta clicar no canto inferior direito da célula J4 e arrastar para baixo até a célula J15
Note que o Excel irá, automaticamente, somar todas as linhas com o resultado total de despesas por mês.
Soma Vertical
Depois de concluirmos a soma horizontal das despesas mensais, podemos agora realizar a soma total de cada mês criando assim um total trimestral e anual para cada despesas individualmente, assim como um total trimestral e anual com base na coluna Total de Despesas
Para iniciar a soma iremos selecionar a célula C16 e inserir a seguinte função: =SOMA(C4:C15) e vamos arrastar a fórmula da célula C16 para a células D16 até J16. Seguem abaixo resultados:
Passo 1 - inserindo na célula C16 a função: =SOMA(C4:C15)
Passo 2 - Arrastando formula da célula C16 até J16
Passo 3 - Resultado da SOMA Total Anual (linha 16)
Soma por célula
Agora iremos criar as funções de SOMA para calcular o total de Despesas Mensais para o primeiro e segundo semestre do ano. Segue passo a passo:
Somando as despesas mensais do Primeiro Semestre
Passo 1 - Inserindo na célula C18 a função =SOMA(C4;C5;C6;C7;C8;C9)
*Como as células para cálculo são sequenciais, para esse caso poderia ser utilizado também a função soma na mesma lógica do total anual =SOMA(C4:C9)
Passo 2 - Arrastando formula da célula C18 até J18
Somando as despesas mensais do Segundo Semestre
Passo 1 - Inserindo na célula C20 a função =SOMA(C10;C11;C12;C13;C14;C15)
*Como as células para cálculo são sequenciais, para esse caso poderia ser utilizado também a função soma na mesma lógica do total anual =SOMA(C10:C15)
Passo 2 - Arrastando formula da célula C18 até J18
Resultado do total do primeiro e segundo trimestre ( linhas 18 e 20)
Abaixo podemos ver o resultado final das somas realizadas na planilha de despesas mensais.
Podemos analisar a planilha por tipo de despesas, por mês, por ano e por semestre. Ou seja, temos uma planilha completa para analise.
Espero que esse post lhe ajude a elaborar planilhas em seu trabalho ou sua em casa. Qualquer dúvida ou sugestão, deixe seu comentário.
