Tutorial: Limpar e normalizar dados de livros do Excel

Este tutorial ensina-o a ler dados de um livro com um Script do Office para Excel. Você estará escrevendo um novo script que formatará um extrato bancário e normalizará os dados desse extrato. Como parte desta limpeza de dados, seu script lerá os valores das células de transação, aplicará uma fórmula simples a cada valor e gravará a resposta resultante na pasta de trabalho. A leitura os dados da pasta de trabalho permite a automatização de alguns dos seus processos de tomada de decisão no script.

Pré-requisitos

Precisará de acesso aos Scripts do Office para este tutorial. Reveja o Suporte da plataforma se o separador Automatizar não for apresentado.

Ler uma célula

Os scripts feitos com o Gravador de Ação só podem gravar informações na pasta de trabalho. Com o Editor de Códigos, é possível editar e criar scripts que também leem dados de uma pasta de trabalho.

Comece por criar um script que leia dados e aja com base no que foi lido. Ao longo do tutorial, irá trabalhar com um extrato bancário de exemplo. Essa instrução é um relatório combinado de verificação de crédito. Infelizmente, o saldo dos relatórios bancários muda de forma diferente. A declaração de verificação exibe o rendimento como crédito positivo e custos como débito negativo. O demonstrativo de crédito faz o oposto.

Durante o resto do tutorial, irá normalizar estes dados com um script. Primeiro, tem de ler dados do livro.

1. Crie uma nova planilha na pasta de trabalho usada para o resto do tutorial.

2. Copie os seguintes dados e cole-os na nova planilha, começando na célula A1.

   Data	Conta	Descrição	Débito	Crédito
   10/10/2019	Verificando	Vinícola Coho	-20.05
   11/10/2019	Crédito	A Companhia Telefônica	99.95
   13/10/2019	Crédito	Vinícola Coho	154.43
   15/10/2019	Verificando	Depósito externo		1000
   20/10/2019	Crédito	Vinícola Coho – Reembolso		-35.45
   25/10/2019	Verificando	Ideal para sua empresa de produtos orgânicos	-85.64
   01/11/2019	Verificando	Depósito externo		1000

3. Aceda ao separador Automatizar e selecione Novo Script>Criar no Editor de Código.

4. Limpe a formatação. Trata-se de um documento financeiro, pelo que o script altera a formatação de números nas colunas Débito e Crédito para mostrar valores como montantes em dólares. Faça também com que o script ajuste a largura da coluna aos dados.

   Substitua o conteúdo do script pelo código a seguir:

   function main(workbook: ExcelScript.Workbook) {
       // Get the current worksheet.
       let selectedSheet = workbook.getActiveWorksheet();
   
       // Format the range to display numerical dollar amounts.
       selectedSheet.getRange("D2:E8").setNumberFormat("$#,##0.00");
   
       // Fit the width of all the used columns to the data.
       selectedSheet.getUsedRange().getFormat().autofitColumns();
   }
   

5. Agora, leia um valor a partir de uma das colunas de número. Adicione o seguinte código ao final do script (antes de fechar }).

   // Get the value of cell D2.
   let range = selectedSheet.getRange("D2");
   console.log(range.getValues());
   

6. Execute o script.

7. Você deverá ver [Array[1]] no console. Isso não é um número porque os intervalos são matrizes bidimensionais de dados. Esse intervalo bidimensional está sendo registrado diretamente no console. Felizmente, o Editor de Códigos permite visualizar o conteúdo da matriz.

8. Quando uma matriz bidimensional é registrada no console, ela agrupa os valores de coluna em cada linha. Expanda o registro da matriz selecionando o triângulo azul.

9. Expanda o segundo nível da matriz ao selecionar o triângulo azul recentemente revelado. Agora, você deverá ver isto:

   

Modificar o valor de uma célula

Agora que o script pode ler dados, utilize esses dados para modificar o livro. Torne o valor da célula D2 positivo com a Math.abs função . O objeto Matemática contém várias funções às quais seus scripts têm acesso. É possível encontrar mais informações sobre Math e outros objetos internos Usando objetos JavaScript internos nos scripts do Office.

1. Utilize getValue e setValue métodos para alterar o valor da célula. Esses métodos funcionam em uma única célula. Ao lidar com intervalos de várias células, use getValues e setValues. Adicione o seguinte código ao final do script.

   // Run the `Math.abs` method with the value at D2 and apply that value back to D2.
   let positiveValue = Math.abs(range.getValue() as number);
   range.setValue(positiveValue);
   

   Observação

   Estamos lançando o valor retornado de range.getValue() para um number usando a palavra-chave as. Isso é necessário porque um intervalo pode ser cadeias de caracteres, números ou booleanas. Nesta instância, precisamos explicitamente de um número.

2. O valor da célula D2 agora deverá ser positivo.

Modificar os valores de uma coluna

Agora que sabe como ler e escrever numa única célula, pode generalizar o script para trabalhar em todas as colunas Débito e Crédito .

1. Remova o código que afeta apenas uma única célula (o código de valor absoluto anterior), de modo que o script agora se pareça com este:

   function main(workbook: ExcelScript.Workbook) {
       // Get the current worksheet.
       let selectedSheet = workbook.getActiveWorksheet();
   
       // Format the range to display numerical dollar amounts.
       selectedSheet.getRange("D2:E8").setNumberFormat("$#,##0.00");
   
       // Fit the width of all the used columns to the data.
       selectedSheet.getUsedRange().getFormat().autofitColumns();
   }
   

2. Adicione um loop que percorra as linhas nas duas últimas colunas. Para cada célula, o script define o valor para o valor absoluto do valor atual.

   Observe que a matriz que define a localização das células é baseada em zero. Isso significa que a célula A1 é range[0][0].

   // Get the values of the used range.
   let range = selectedSheet.getUsedRange();
   let rangeValues = range.getValues();
   
   // Iterate over the fourth and fifth columns and set their values to their absolute value.
   let rowCount = range.getRowCount();
   for (let i = 1; i < rowCount; i++) {
       // The column at index 3 is column "4" in the worksheet.
       if (rangeValues[i][3] != 0) {
           let positiveValue = Math.abs(rangeValues[i][3] as number);
           selectedSheet.getCell(i, 3).setValue(positiveValue);
       }
   
       // The column at index 4 is column "5" in the worksheet.
       if (rangeValues[i][4] != 0) {
           let positiveValue = Math.abs(rangeValues[i][4] as number);
           selectedSheet.getCell(i, 4).setValue(positiveValue);
       }
   }
   

   Essa parte do script faz várias tarefas importantes. Primeiro, ela obtém os valores e a contagem de linhas do intervalo usado. Isto permite que o script analise os valores e saiba quando parar. Segundo, ela reitera através do intervalo usado, verificando cada célula nas colunas Débito ou Crédito. Por fim, se o valor na célula não for 0, ele será substituído pelo valor absoluto. O script ignora zeros, para que possa deixar as células em branco tal como estavam.

3. Execute o script.

   O seu extrato bancário deverá ter agora números positivos formatados corretamente.

   

Próximas etapas

Abra o Editor de Código e experimente alguns dos nossos Scripts de exemplo para Scripts do Office no Excel. Também pode visitar Noções Básicas para Scripts do Office no Excel para saber mais sobre a criação de Scripts do Office.

A próxima série de tutoriais de Scripts do Office tem foco na utilização de Scripts do Office com o Power Automate. Saiba mais sobre as vantagens de combinar as duas plataformas em Executar Scripts do Office com o Power Automate ou experimente o Tutorial: Atualizar uma folha de cálculo a partir de um fluxo do Power Automate para criar um fluxo do Power Automate que utiliza um Script do Office.