agwebrunner.com

Uma das funções do Excel mais poderosas, mas raramente usadas, é a capacidade de criar facilmente tarefas automatizadas e lógica personalizada dentro de macros. As macros fornecem uma maneira ideal de economizar tempo em tarefas repetitivas e previsíveis, bem como padronizar formatos de documentos - muitas vezes sem ter que escrever uma única linha de código.

Se você está curioso para saber o que são macros ou como realmente criá-las, não há problema - nós o acompanharemos por todo o processo.

Observação:o mesmo processo deve funcionar na maioria das versões do Microsoft Office. As capturas de tela podem parecer um pouco diferentes.

O que é uma macro?

Uma macro do Microsoft Office (já que essa funcionalidade se aplica a vários aplicativos do MS Office) é simplesmente código do Visual Basic for Applications (VBA) salvo dentro de um documento. Para uma analogia comparável, pense em um documento como HTML e uma macro como Javascript. Da mesma forma que o Javascript pode manipular HTML em uma página da web, uma macro pode manipular um documento.

As macros são incrivelmente poderosas e podem fazer praticamente qualquer coisa que sua imaginação possa imaginar. Como uma (muito) pequena lista de funções que você pode executar com uma macro:

• Aplique estilo e formatação.
• Manipule dados e texto.
• Comunique-se com fontes de dados (banco de dados, arquivos de texto, etc.).
• Crie documentos totalmente novos.
• Qualquer combinação, em qualquer ordem, de qualquer um dos itens acima.

Criando uma macro: uma explicação por exemplo

Começamos com seu arquivo CSV de variedades de jardim. Nada de especial aqui, apenas um conjunto de números 10 × 20 entre 0 e 100 com cabeçalho de linha e coluna. Nosso objetivo é produzir uma planilha de dados bem formatada e apresentável que inclua os totais resumidos para cada linha.

Como afirmamos acima, uma macro é um código VBA, mas uma das coisas boas sobre o Excel é que você pode criá-los / gravá-los sem a necessidade de codificação - como faremos aqui.

Para criar uma macro, vá para Exibir> Macros> Gravar macro.

Atribua um nome à macro (sem espaços) e clique em OK.

Feito isso, tudo de suas ações são registradas - cada mudança de célula, ação de rolagem, redimensionamento de janela, você escolhe.

Existem alguns lugares que indicam que o Excel está no modo de registro. Uma é visualizando o menu Macro e observando que Parar Gravação substituiu a opção de Gravar Macro.

O outro está no canto inferior direito. O ícone 'parar' indica que está no modo macro e pressionar aqui irá parar a gravação (da mesma forma, quando não estiver no modo de gravação, este ícone será o botão Gravar Macro, que você pode usar em vez de ir para o menu Macros).

Agora que estamos gravando nossa macro, vamos aplicar nossos cálculos de resumo. Primeiro adicione os cabeçalhos.

Em seguida, aplique as fórmulas apropriadas (respectivamente):

• = SOMA (B2: K2)
• = MÉDIA (B2: K2)
• = MIN (B2: K2)
• = MAX (B2: K2)
• = MEDIAN (B2: K2)

Agora, destaque todas as células de cálculo e arraste o comprimento de todas as nossas linhas de dados para aplicar os cálculos a cada linha.

Feito isso, cada linha deve exibir seus respectivos resumos.

Agora, queremos obter os dados de resumo de toda a planilha, então aplicamos mais alguns cálculos:

Respectivamente:

• = SOMA (L2: L21)
• = MÉDIA (B2: K21) *Isso deve ser calculado em todos os dados porque a média das médias das linhas não é necessariamente igual à média de todos os valores.
• = MIN (N2: N21)
• = MAX (O2: O21)
• = MEDIAN (B2: K21) * Calculado em todos os dados pelo mesmo motivo acima.

Agora que os cálculos estão feitos, vamos aplicar o estilo e a formatação. Primeiro aplique a formatação de número geral em todas as células fazendo um Selecionar tudo (Ctrl + A ou clique na célula entre os cabeçalhos de linha e coluna) e selecione o ícone “Estilo de vírgula” no menu inicial.

Em seguida, aplique alguma formatação visual aos cabeçalhos de linha e coluna:

• Negrito.
• Centrado.
• Cor de preenchimento do plano de fundo.

E, finalmente, aplique algum estilo aos totais.

Quando tudo estiver concluído, nossa folha de dados será assim:

Como estamos satisfeitos com os resultados, pare a gravação da macro.

Parabéns - você acabou de criar uma macro do Excel.

Para usar nossa macro recém-gravada, temos que salvar nossa pasta de trabalho do Excel em um formato de arquivo habilitado para macro. No entanto, antes de fazermos isso, primeiro precisamos limpar todos os dados existentes para que não sejam incorporados em nosso modelo (a ideia é que sempre que usarmos esse modelo, importaremos os dados mais atualizados).

Para fazer isso, selecione todas as células e exclua-as.

Com os dados agora limpos (mas as macros ainda incluídas no arquivo Excel), queremos salvar o arquivo como um arquivo de modelo habilitado para macro (XLTM). É importante notar que se você salvar este como um arquivo de modelo padrão (XLTX), as macros irão não ser capaz de ser executado a partir dele. Como alternativa, você pode salvar o arquivo como um arquivo de modelo legado (XLT), o que permitirá a execução de macros.

Depois de salvar o arquivo como um modelo, vá em frente e feche o Excel.

Usando uma macro do Excel

Antes de abordar como podemos aplicar essa macro recém-gravada, é importante cobrir alguns pontos sobre macros em geral:

• As macros podem ser maliciosas.
• Veja o ponto acima.

O código VBA é realmente muito poderoso e pode manipular arquivos fora do escopo do documento atual. Por exemplo, uma macro pode alterar ou excluir arquivos aleatórios na pasta Meus Documentos. Como tal, é importante ter certeza de que você só execute macros de fontes confiáveis.

Para colocar nossa macro de formato de dados em uso, abra o arquivo de modelo do Excel que foi criado acima. Ao fazer isso, supondo que as configurações de segurança padrão estejam ativadas, você verá um aviso na parte superior da pasta de trabalho informando que as macros estão desativadas. Como confiamos em uma macro criada por nós mesmos, clique no botão ‘Ativar Conteúdo’.

A seguir, vamos importar o conjunto de dados mais recente de um CSV (esta é a fonte que a planilha usou para criar nossa macro).

Para concluir a importação do arquivo CSV, pode ser necessário definir algumas opções para que o Excel o interprete corretamente (por exemplo, delimitador, cabeçalhos presentes etc.).

Depois que nossos dados forem importados, basta ir ao menu Macros (na guia Exibir) e selecionar Exibir macros.

Na caixa de diálogo resultante, vemos a macro “FormatData” gravada acima. Selecione-o e clique em Executar.

Uma vez executado, você pode ver o cursor pular por alguns momentos, mas ao fazer isso, você verá os dados sendo manipulados exatamente como nós gravamos. Quando tudo estiver dito e feito, deve ser parecido com o nosso original - exceto com dados diferentes.

Olhando sob o capô: O que faz uma macro funcionar

Como mencionamos algumas vezes, uma macro é orientada pelo código do Visual Basic for Applications (VBA). Quando você “grava” uma macro, o Excel está na verdade traduzindo tudo o que você faz em suas respectivas instruções VBA. Para simplificar - você não precisa escrever nenhum código porque o Excel está escrevendo o código para você.

Para visualizar o código que faz nossa macro funcionar, na caixa de diálogo Macros, clique no botão Editar.

A janela que se abre exibe o código-fonte que foi gravado de nossas ações ao criar a macro. Claro, você pode editar esse código ou até mesmo criar novas macros inteiramente dentro da janela de código. Embora a ação de gravação usada neste artigo provavelmente atenda à maioria das necessidades, ações mais customizadas ou ações condicionais exigiriam que você edite o código-fonte.

Levando nosso exemplo um passo adiante ...

Hipoteticamente, suponha que nosso arquivo de dados de origem, data.csv, seja produzido por um processo automatizado que sempre salva o arquivo no mesmo local (por exemplo, C: \ Data \ data.csv são sempre os dados mais recentes). O processo de abrir este arquivo e importá-lo também pode ser facilmente transformado em uma macro:

1. Abra o arquivo de modelo do Excel contendo nossa macro “FormatData”.
2. Grave uma nova macro chamada “LoadData”.
3. Com a gravação da macro, importe o arquivo de dados como faria normalmente.
4. Assim que os dados forem importados, pare de gravar a macro.
5. Exclua todos os dados da célula (selecione tudo e exclua).
6. Salve o modelo atualizado (lembre-se de usar um formato de modelo habilitado para macro).

Feito isso, sempre que o modelo for aberto, haverá duas macros - uma que carrega nossos dados e a outra que os formata.

Se você realmente queria sujar as mãos com um pouco de edição de código, você poderia facilmente combinar essas ações em uma única macro, copiando o código produzido de “LoadData” e inserindo-o no início do código de “FormatData”.

Baixe este modelo

Para sua conveniência, incluímos o modelo do Excel produzido neste artigo, bem como um arquivo de dados de amostra para você brincar.

Baixe o modelo de macro do Excel do How-To Geek