Tutorial: Limpieza y normalización de los datos del libro de Excel

Artículo
11/29/2023

En este tutorial se explica cómo leer datos de un libro con un script de Office para Excel. Escribirá un nuevo script que dé formato a un extracto bancario y normalice los datos en ese extracto. Como parte de la limpieza de datos, el script leerá valores de las celdas de transacción, aplicará una fórmula simple a cada valor y escribirá la respuesta resultante en el libro. La lectura de datos del libro le permite automatizar algunos de los procesos de toma de decisiones en el script.

Sugerencia

Si no está familiarizado con los scripts de Office, se recomienda empezar por Tutorial: Creación y formato de una tabla de Excel. Scripts de Office usa TypeScript y este tutorial está diseñado para las personas con conocimientos de nivel intermedio de JavaScript o TypeScript. Si no está familiarizado con JavaScript, le recomendamos que comience con el Tutorial de JavaScript de Mozilla.

Requisitos previos

Necesitará acceso a scripts de Office para este tutorial. Revise el soporte técnico de la plataforma si no aparece la pestaña Automatizar.

Leer una celda

Los scripts creados con la Grabadora de acciones solo pueden escribir información en el libro. Con el Editor de código, puede además editar y escribir scripts que lean datos de un libro.

Empiece por crear un script que lea datos y actúe en función de lo que se leyó. A lo largo del tutorial, trabajará con un extracto bancario de ejemplo. Este ejemplo es una declaración combinada de cuenta corriente y crédito. Desafortunadamente, el banco informa de que el saldo cambia de manera diferente. La declaración de cuenta corriente muestra los ingresos como crédito positivo y los costes como débito negativo. En cambio, la declaración de crédito funciona de manera contraria.

En el resto del tutorial, normalizará estos datos mediante un script. En primer lugar, debe leer datos del libro.

Cree una nueva hoja de cálculo en el libro que ha usado para el resto del tutorial.

Copie los siguientes datos y péguelos en la nueva hoja de cálculo, comenzando por la celda A1.

Fecha	Cuenta	Descripción	Débito	Crédito
10/10/2019	Cuenta corriente	Coho Vineyard	-20,05
11/10/2019	Crédito	The Phone Company	99,95
13/10/2019	Crédito	Coho Vineyard	154,43
15/10/2019	Cuenta corriente	Depósito externo		1000
20/10/2019	Crédito	Coho Vineyard - Devolución		- 35,45
25/10/2019	Cuenta corriente	Best For You Organics Company	- 85,64
01/11/2019	Cuenta corriente	Depósito externo		1000

Vaya a la pestaña Automatizar y seleccione Nuevo script.

Limpie el formato. Se trata de un documento financiero, así que haga que el script cambie el formato de número en las columnas Débito y Crédito para mostrar los valores como importes en dólares. También haga que el script ajuste el ancho de columna a los datos.

Reemplace el contenido del script por el siguiente código:

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

Ahora lea un valor de una de las columnas numéricas. Agregue el código siguiente al final del script (antes del cierre }).
```
// Get the value of cell D2.
let range = selectedSheet.getRange("D2");
console.log(range.getValues());
```
Ejecute el script.
Debe ver [Array[1]] en la consola. No es un número por que los rangos son matrices bidimensionales de datos. Este rango bidimensional se ha registrado en la consola directamente. Afortunadamente, el Editor de código le permite ver el contenido de la matriz.
Cuando se registra una matriz bidimensional en la consola, se agrupan los valores de columna en cada fila. Expanda el registro de matriz seleccionando el triángulo azul.
Expanda el segundo nivel de la matriz seleccionando el triángulo azul recién revelado. Ahora debería ver lo siguiente:

Cambiar el valor de una celda

Ahora que el script puede leer datos, use esos datos para modificar el libro. Haga que el valor de la celda D2 sea positivo con la Math.abs función . El objeto Math contiene varias funciones a las que tienen acceso los scripts. Puede encontrar más información sobre Math y otros objetos integrados en Usar objetos integrados de JavaScript en los scripts de Office.

Use getValue métodos y setValue para cambiar el valor de la celda. Estos métodos funcionan en una sola celda. Cuando trabaje con rangos de varias celdas, es mejor usar getValues y setValues. Agregue el código siguiente al final del 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);
```
Nota:

Se está cambiando el valor devuelto de range.getValue() a un number con la palabra clave as. Esto es necesario porque un rango puede estar conformado por cadenas, números o booleanos. En este caso, específicamente necesitamos un número.
El valor de la celda D2 debería ahora ser positivo.

Modificar los valores de una columna

Ahora que sabe cómo leer y escribir en una sola celda, puede generalizar el script para que funcione en todas las columnas débito y crédito .

Quite el código que afecta a una sola celda (el código de valor absoluto anterior), para que el script tenga el siguiente aspecto:

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

Agregue un bucle al final del script que itere las filas de las dos últimas columnas. En cada celda, el script establece el valor absoluto del valor actual iterado.

Tenga en cuenta que la matriz que define las ubicaciones de la celda está basada en cero. Esto significa que la celda A1 es 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);
    }
}
```
Esta parte del script realiza varias tareas importantes. En primer lugar, obtiene los valores y cuenta las filas del rango usado. Esto permite que el script examine los valores y sepa cuándo se debe detener. En segundo lugar, itera el rango usado, verificando cada celda en las columnas Débito y Crédito. Por último, si el valor de la celda no es 0, se reemplaza por su valor absoluto. El script omite ceros, por lo que puede dejar las celdas en blanco tal y como estaban.
Ejecute el script.

Su estado de cuenta bancario debería tener ahora números positivos con formato correcto.

Pasos siguientes

Abra el Editor de código y pruebe algunos de nuestrosEjemplos para scripts de Office en Excel También puede visitar Aspectos básicos para scripts de Office en Excel para obtener más información sobre cómo crear scripts de Office.

La siguiente serie de tutoriales de Scripts de Office se centra en el uso de Scripts de Office con Power Automate. Obtenga más información sobre las ventajas de combinar las dos plataformas en Ejecutar scripts de Office con Power Automate o pruebe tutorial: Actualización de una hoja de cálculo desde un flujo de Power Automate para crear un flujo de Power Automate que use un script de Office.

Compartir a través de