Tutoriel : Nettoyer et normaliser les données des classeurs Excel

Article
12/06/2023

Ce tutoriel vous apprend à lire des données à partir d’un classeur avec un script Office pour Excel. Vous allez écrire un nouveau script qui met en forme un relevé bancaire et normalise les données incluses. Lors de ce nettoyage de données, votre script lira les valeurs des cellules de transaction, appliquera une formule simple à chaque valeur, puis écrira la réponse résultante dans le classeur. La lecture de données du classeur vous permet d’automatiser certains processus décisionnels dans le script.

Conseil

Si vous débutez avec les scripts Office, nous vous recommandons de commencer par Tutoriel : Créer et mettre en forme un tableau Excel. Les scripts Office utilisent TypeScript, et ce didacticiel est destiné aux utilisateurs ayant des connaissances de niveau débutant à intermédiaire en JavaScript ou TypeScript. Si vous découvrez JavaScript, nous vous conseillons de commencer par consulter le didacticiel Mozilla JavaScript.

Conditions préalables

Vous aurez besoin d’accéder aux scripts Office pour ce didacticiel. Vérifiez la prise en charge de la plateforme si l’onglet Automatiser n’apparaît pas.

Lire une cellule

Les scripts créés avec l’enregistreur d’actions peuvent uniquement écrire des informations dans le classeur. L’éditeur de code vous permet de modifier et de créer des scripts qui peuvent également lire les données d’un classeur.

Commencez par créer un script qui lit les données et agit en fonction de ce qui a été lu. Tout au long du tutoriel, vous allez utiliser un exemple de relevé bancaire. Il s’agit d’un relevé combiné de compte courant et de crédit. Malheureusement, la banque signale les changements de solde différemment. Le relevé de compte courant donne les revenus comme crédit positif et les dépenses comme débit négatif. Le relevé de crédit fait l’inverse.

Dans le reste du tutoriel, vous allez normaliser ces données à l’aide d’un script. Tout d’abord, vous devez lire les données du classeur.

Créez une nouvelle feuille de calcul dans le classeur courant, vous l’utiliserez pour le reste du didacticiel.

Copiez les données suivantes et collez-les dans la feuille de calcul en commençant à la cellule A1.

Date	Compte	Description	Débit	Crédit
10/10/2019	Compte courant	Coho Vineyard	−20,05
11/10/2019	Crédit	The Phone Company	99,95
13/10/2019	Crédit	Coho Vineyard	154,43
15/10/2019	Compte courant	Versement externe		1000
20/10/2019	Crédit	Coho Vineyard − Remboursement		−35,45
25/10/2019	Compte courant	Best For You Organics Company	−85,64
01/11/2019	Compte courant	Versement externe		1000

Accédez à l’onglet Automatiser et sélectionnez Nouveau script.

Nettoyez la mise en forme. Il s’agit d’un document financier. Votre script doit donc modifier la mise en forme des nombres dans les colonnes Débit et Crédit pour afficher les valeurs sous forme de montants en dollars. Veillez également à ce que votre script ajuste la largeur de colonne aux données.

Remplacez le contenu du script par le code suivant :

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

À présent, lisez une valeur à partir de l’une des colonnes numériques. Ajoutez le code suivant à la fin du script (avant la fermeture }).
```
// Get the value of cell D2.
let range = selectedSheet.getRange("D2");
console.log(range.getValues());
```
Exécutez le script.
Dans la console, [Array[1]] doit s’afficher. Ce n’est pas un nombre, car les plages sont des tableaux de données à deux dimensions. Cette plage à deux dimensions est directement journalisée dans la console. Heureusement, l’éditeur de code vous permet de voir le contenu du tableau.
Lorsqu’un tableau à deux dimensions est journalisé sur la console, il regroupe les valeurs de colonne sous chaque ligne. Développez le journal du tableau en sélectionnant le triangle bleu.
Développez le deuxième niveau du tableau en sélectionnant le triangle bleu qui vient d’être révélé. Voici ce que vous devez voir :

Modifier la valeur d’une cellule.

Maintenant que votre script peut lire les données, utilisez ces données pour modifier le classeur. Rendez la valeur de la cellule D2 positive avec la Math.abs fonction . L’objet Math contient de nombreuses fonctions auxquelles vos scripts ont accès. Pour plus d’informations sur Math et les autres objets intégrés, voir Utilisation d’objets JavaScript intégrés dans les scripts Office.

Utilisez les getValue méthodes et setValue pour modifier la valeur de la cellule. Ces méthodes fonctionnent sur une seule cellule. Lorsque vous manipulez des plages de plusieurs cellules, vous pouvez utiliser getValues et setValues. Ajoutez le code suivant à la fin du 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);
```
Remarque

Nous transformons la valeur retournée de range.getValue() en number à l'aide du mot-clé as. Ceci est nécessaire, car une plage peut être des chaînes, des nombres ou des valeurs booléennes. Dans ce cas, nous avons explicitement besoin d’un nombre.
La valeur de la cellule D2 doit maintenant être positive.

Modifier les valeurs d’une colonne

Maintenant que vous savez comment lire et écrire dans une seule cellule, vous pouvez généraliser le script pour qu’il fonctionne sur l’ensemble des colonnes de débit et de crédit .

Supprimez le code qui affecte une seule cellule (le code de valeur absolue précédent), de sorte que votre script se présente désormais comme suit :

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

Ajoutez une boucle à la fin du script qui itère au sein des lignes des deux dernières colonnes. Le script remplace la valeur de chaque cellule en la valeur absolue de cette valeur.

Notez que l’indexation du tableau qui définit les emplacements des cellules est basée sur zéro. Par conséquent, la cellule A1 est 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);
    }
}
```
Cette partie du script effectue plusieurs tâches importantes. Premièrement, elle obtient les valeurs et le nombre de lignes de la plage utilisée. Cela permet au script d’examiner les valeurs et de savoir quand s’arrêter. Deuxièmement, elle produit une itération dans la plage utilisée, en vérifiant chaque cellule des colonnes Débit et Crédit. Enfin, si la valeur dans la cellule n’est pas 0, elle est remplacée par sa valeur absolue. Comme le script ignore les zéros, vous pouvez laisser les cellules vides telles qu’elles étaient.
Exécutez le script.

Votre relevé bancaire doit maintenant avoir des nombres positifs correctement mis en forme.

Étapes suivantes

Ouvrez l’éditeur de code et essayez quelques-uns de nos exemples de scripts pour Scripts Office dans Excel. Vous pouvez également consulter Notions de base pour les scripts Office dans Excel pour en savoir plus sur la création de scripts Office.

La prochaine série de didacticiels sur les scripts Office met l’accent sur l’utilisation de scripts Office avec Power Automate. Découvrez les avantages de la combinaison des deux plateformes dans Exécuter des scripts Office avec Power Automate ou essayez le Tutoriel : Mettre à jour une feuille de calcul à partir d’un flux Power Automate pour créer un flux Power Automate qui utilise un script Office.

Share via