Tutorial: Limpar e normalizar dados da pasta de trabalho do Excel

Artigo
11/29/2023

Este tutorial ensina como ler dados de uma pasta de trabalho 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.

Dica

Se você for novo em Scripts do Office, recomendamos começar com Tutorial: Criar e formatar uma tabela do Excel. Os Scripts do Office usam TypeScript e este tutorial se destina a pessoas com conhecimento de nível iniciante a intermediário em JavaScript ou TypeScript. Se você é novo no JavaScript, recomendamos começar com o tutorial da Mozilla sobre JavaScript.

Pré-requisitos

Você precisará de acesso aos Scripts do Office para este tutorial. Examine o suporte da Plataforma se a guia Automatizar não aparecer.

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 fazendo um script que lê dados e age com base no que foi lido. Ao longo do tutorial, você trabalhará com uma instrução bancária de exemplo. Essa instrução é um relatório combinado de verificação de crédito. Infelizmente, o banco informa que o saldo 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.

Ao longo do restante do tutorial, você normalizará esses dados usando um script. Primeiro, você precisa ler dados da pasta de trabalho.

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

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

Acesse a guia Automatizar e selecioneNovo Script.

Limpe a formatação. Este é um documento financeiro, portanto, faça com que seu script altere a formatação de número nas colunas Débito e Crédito para mostrar valores como valores em dólar. Também faça 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();
}

Agora leia um valor de uma das colunas de número. Adicione o código a seguir ao final do script (antes do fechamento }).
```
// Get the value of cell D2.
let range = selectedSheet.getRange("D2");
console.log(range.getValues());
```
Execute o script.
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.
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.
Expanda o segundo nível da matriz selecionando o triângulo azul recém-revelado. Agora, você deverá ver isto:

Modificar o valor de uma célula

Agora que seu script pode ler dados, use esses dados para modificar a pasta de trabalho. 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.

Use 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 código a seguir 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.
O valor da célula D2 agora deverá ser positivo.

Modificar os valores de uma coluna

Agora que você sabe como ler e gravar em uma única célula, você pode generalizar o script para trabalhar em colunas de débito e crédito inteiras.

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();
}

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. Isso permite que o script examine 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 você possa deixar as células em branco como estavam.
Execute o script.

Sua instrução bancária agora deve ter formatado corretamente números positivos.

Próximas etapas

Abra o Editor de Código e experimente alguns dos nossos Scripts de exemplo para Scripts do Office no Excel. Você também pode visitar fundamentos para scripts do Office no Excel para saber mais sobre como criar 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 que combinam as duas plataformas em Executar Scripts do Office com o Power Automate ou tente Tutorial: atualize uma planilha de um fluxo do Power Automate para criar um fluxo do Power Automate que usa um Script do Office.

Compartilhar via