Guide d’Utilisation Avancée V1 PDF

ACL GUIDE D’UTILISATION AVANCEE V1 Bilel ZOGHLAMI | CONTROLLEUR PUBLIC | 24 JUIN 2023 Préambule Ce guide est le fruit des éléments suivants : ▪ Le Point de départ : Quelques formations d’initiation sur ACL suivies à la cour des comptes ▪ La lecture de certaines références citées...

ACL GUIDE D’UTILISATION AVANCEE V1 Bilel ZOGHLAMI | CONTROLLEUR PUBLIC | 24 JUIN 2023 Préambule Ce guide est le fruit des éléments suivants : ▪ Le Point de départ : Quelques formations d’initiation sur ACL suivies à la cour des comptes ▪ La lecture de certaines références citées à la fin de ce guide ▪ Des années de pratique de l’ACL et de découverte de ses atouts Table des matières Chapitre 1 : l’intérêt d’apprendre ACL..................................................................................................... 4 Chapitre 2 : Aperçu sur les principales fonctionnalités/commandes de ACL Analytics.......................... 8 Chapitre 3 : installation......................................................................................................................... 11 Chapitre 4 : Démarrage d’un projet ACL (importation des données et vérification de leur correcte importation).......................................................................................................................................... 14 Chapitre 5 : activation des connexions/relations entre les différentes données importées................. 42 Chapitre 6 : définir le périmètre des données à auditer et leur mise en ordre................................... 61 Chapitre 7 : prise de connaissance générale des données de la population cible (audit macro)........ 74 Chapitre 8 : audit des données de la population cible (audit micro)................................................... 93 Chapitre 9 : Maitrise de l’affichage et de l’enregistrement des résultats des tests............................ 111 Chapitre 10 : mise à jour des données importées et utilisation des scripts....................................... 117 Chapitre 11 : quelques références (lecture facultative)..................................................................... 123 Page 3 sur 123 Chapitre 1 : l’intérêt d’apprendre ACL ❖ Qu’est-ce que ACL ? ACL est l’abréviation de « Audit command language » Actuellement ce logiciel s’appelle ACL analytics. Il permet l’importation de données de différents formats et de différentes source et l’analyse de l’ensemble de ces données pour la détection des irrégularités ou modes/comportements particuliers dans les transactions qui peuvent indiquer des faiblesses de contrôle interne ou de fraudes. ❖ Comparaison sommaire entre ACL et Excel Si tu ne sais pas utiliser les modules « Power querry et Power pivot » dans Excel, alors tu ne pourras pas auditer les grandes bases de données grâce à Excel, tu es obligé à apprendre ACL pour pouvoir auditer ces bases. Toutefois, apprendre ACL reste plus facile qu’apprendre les modules « Power querry et Power pivot». Dans ce qui suit, une comparaison à titre indicatif entre ACL et Excel sans tenir compte des modules « Power querry et Power pivot » On a comparé ACL v9.0 vs Excel v2016. Page 4 sur 123 CARACTERISTIQUES ACL EXCEL COMMENTAIRE Capacité de traitement et de protection des données Capacité de traitement de Peut traiter de grandes bases de Excel est limité par le nombre de L’utilisation des modules Power Pivot ou données données lignes (exemple Excel 2019 power Quercy permettent de dépasser la supporte 1048576 lignes) limite d’un million selon la quantité de la mémoire ram du pc (ça n’empêche que le pc devient lourd voir très lourd) ACL reste plus performant Protection des données reçues Les données importées dans ACL Il revient à l’utilisateur d’activer la ACL reste plus performant auprès du gestionnaire sont protégées systématiquement protection des données (dans ACL on ne peut pas modifier une donnée importée) Importation de fichier texte à Peut l’importer Le fichier texte doit être structuré Si on sait utiliser Power Querry on peut définir structure particulière (chaque (soit il est délimité par un la taille de chaque champ. champ (colonne) a un nombre de séparateur ou sous forme de Sinon, ACL est le seul qui peut importer ce caractères spécifique à lui mais la colonnes à largeur fixe) fichier taille du contenu est différente et Dans ce cas, on ne peut pas utiliser il n’ya pas de séparateur. « colonnes à largeur fixe » Exemple : taille du champ 1 est 20 Et donc on ne peut pas importer le caractères mais le contenu du fichier champ varie entre 10 et 15 caractères Type de données dans une Les données dans un champ Les données dans une colonne Dans Excel, lorsque les données sont de type colonne (colonnes) doivent être de même peuvent être de type différent différent dans une colonne et que l’auditeur ne format/type (numérique, texte ou constate pas cela il y a un grand risque que des date) Page 5 sur 123 CARACTERISTIQUES ACL EXCEL COMMENTAIRE erreurs se produiront lorsque des formules seront exécutées sur ces données. Mais si on utilise Power querry, lors de l’importation des données, il est exigé de spécifier le type de données de chaque champ( colonne) ACL reste plus performant Fonctionnalités qui distinguent ACL de l’Excel Relations entre les tableaux Les relations permises dans acl Jointure (comparaison entre deux On utilise le module Power Querry pour faire (Fusion, jointure, liaison) sont généralement similaires à tableaux) peut se faire la fusion et la jointure et Power Pivot pour celles proposées dans access partiellement via la formule faire les liaisons. recherche V/H ou recherche X Sinon, à priori ACL reste plus performant La fusion entre deux tableaux similaires peut se faire manuellement si le volume des tableaux n’est pas important La liaison entre les tableaux via une clé commune ne peut pas se faire sans utiliser Power Pivot Enregistrement des filtres Possible A priori pas possible A priori Acl reste plus performant Permet de donner un nom à un filtre, l'enregistrer et l'exécuter suivant la volonté de l'utilisateur Page 6 sur 123 CARACTERISTIQUES ACL EXCEL COMMENTAIRE Statistique/statistiques Calcule principalement total, Il faut utiliser plusieurs formules Si on active : Dans moyenne, écrat type d'un champ pour arriver au même résultat que Excel/options/compléments/analysis de donnée sélectionné celui de l’ACL toolpack/activer puis menu données/utilitaire d’analyse/puis choisir statistiques descriptives puis on peut sélectionner plusieurs colonnes est possible Similitude entre ACL et Excel (module analysis toolpack) Page 7 sur 123 Chapitre 2 : Aperçu sur les principales fonctionnalités/commandes de ACL Analytics ❖ Enregistrement des filtres Permet de donner un nom à un filtre, l'enregistrer et l'exécuter suivant la volonté de l'utilisateur ❖ Trier sur Équivalent à Excel (tri simple ou tri sur plusieurs critères combinés) ❖ Compter les enregistrements Détermine le nombre de lignes contenues dans le tableau (sans compter les titres de colonnes) et peut déterminer le nombre de lignes satisfaisant une condition ❖ Total des champs Détermine le total d'une ou plusieurs colonnes contenant des valeurs numériques sélectionnée(s) ❖ Totaliser Équivalent dans Excel à sous total (exemple calcul du total des ventes par client) ❖ Classer C’est la commande totaliser + affichage des % d'importance/représentation ❖ Rechercher les doublons Détermine les valeurs doubles (équivalent dans Excel à Valeur en double) ❖ Rechercher les omissions Vérifie s’il y a des valeurs manquantes dans une séquence et affiche les éléments manquants et ou leurs positions. ❖ Statistique/statistiques Calcule principalement total, moyenne, écart type d'un champ de donnée sélectionné ❖ Statistiques/profiler Calcule principalement total, le minimum et le maximum d'un champ de donnée sélectionné Page 8 sur 123  ❖ Histogramme Similaire au graphique dans Excel ❖ Tabulation croisée Équivalent de tableau croisé dynamique dans Excel ❖ Stratifier Permet de créer des intervalles sur un champ numérique (A) sélectionné et calculer pour chaque intervalle le total du même champ A ou d'un autre champ numérique sélectionné ❖ Classement par antériorité Permet de créer des intervalles sur un champ de date et calculer pour chaque intervalle le total d’un champ numérique sélectionné ❖ Séquence : Permet de vérifier si le tri attendu (que l’utilisateur présume son existence) existe. Il revient à l’utilisateur de définir (et non d’appliquer) dans la fonction séquence le tri attendu. Parfois les tables sont de taille très importante, alors le fait de trier ces tables prend du temps, donc il vaut mieux vérifier à priori si déjà le tri existe. Si la fonction séquence indique des erreurs, dans ce cas l’utilisateur grâce à une autre fonction, peut trier les tables. Il y a des opérations et des relations dans ACL qui exigent que les tables soient triées avant leurs exécutions. ❖ Les connexions entre les tables : On peut considérer qu’ACL combine des fonctions de l’Excel et des fonctions de l’Access (les relations) Les connexions possibles dans ACL sont principalement : ▪ Les relations ▪ La jonction ▪ La fusion ❖ Tests d'audit et commande ACL appropriées1 1 Tableau issu du guide de démarrage de ACL Page 9 sur 123 Page 10 sur 123 Chapitre 3 : installation Ce guide est plus approprié pour l’utilisation de la version la française de l’ACL. Toutefois, il n’y a pas à priori une version française gratuite ou une démonstration. Cependant sur internet, il y a une version démo anglaise utilisable pour le besoin d’apprentissage qui peut être installée directement de façon légitime et qui peut être téléchargé sur ce lien. Cette version est plus récente que celle utilisée à la cour des comptes, elle accepte les fichiers Excel «.xlsx ». En cliquant sur l’expression « sur ce lien » ci-dessus mentionnée, vous allez être redirigés vers une page web d’un dossier google drive, vous cliquez ensuite sur télécharger puis, vous décompressez le dossier téléchargé (via winzip ,ou winrar...) puis vous installer ACL c’est tout. Pour faciliter l’utilisation de la version anglaise, dans ce qui suit une comparaison visuelle entre l’interface anglaise et celle française. Page 11 sur 123 Page 12 sur 123 Page 13 sur 123 Chapitre 4 : Démarrage d’un projet ACL (importation des données et vérification de leur correcte importation) ❖ Création d'un Projet Un projet ACL est un ensemble de fichiers crées par ACL (ils sont liés entre eux et ne peuvent être lus que via ACL) Avant de créer un projet ACL, il est recommandé de créer un dossier lui donner un nom par exemple mon projet ACL ou nom de mission. ▪ Puis aller dans le menu Fichier, ▪ Cliquer sur créer un nouveau projet ▪ Donner un nom au projet « exemple nom de mission » ▪ Puis spécifier l’emplacement du projet (choisir le dossier que vous avez déjà créé) Une fenêtre va vous proposer d’importer des fichiers (excel, texte.…) vous pouvez cliquer « annuler » et les importer plus tard. Voilà les fichiers créés au départ lorsque vous créez un nouveau projet ACL (le nombre peut varier d’une version ACL à une autre) Idéalement lorsqu’on crée un dossier qui va abriter le projet acl il est pertinent de créer 3 sous dossiers dans ce dossier : ▪ Un sous dossier appelé « fichiers source » dans lequel on va sauvegarder les fichiers reçus du gestionnaire (les fichiers contenant les données qui sont généralement au format Excel ou texte…) ▪ Un sous dossier appelé « données importées » dans lequel on va sauvegarder les fichiers créés par ACL sous format «.fil » pour y stocker les données contenues dans les fichiers source lors de l’importation des données dans ACL) ▪ Un sous dossiers appelé « les produits » dans lequel on va sauvegarder les fichiers créés par ACL sous format «.fil » qui contiennent les extractions de données ou les tableaux crées par vous…à partir de l’exploitation des données importées Page 14 sur 123  ❖ COMMENT ACL IMPORTE LES FICHIERS SOURCE Lorsqu’on importe un fichier de données dans ACL, celui-ci lit les données et en crée un nouveau fichier dont l’extension est «.fil » et qui n’est lisible que via ACL. Après l’importation, la suppression du fichier source ou la modification du contenu du fichier source…n’a aucun impact sur le fichier crée par ACL. On verra ultérieurement qu’on peut demander à ACL de mettre à jour son fichier crée à la suite de la mise à jour du fichier source mais dans certaines conditions. Lorsqu’on importe un fichier de données dans ACL, on aura deux résultats : Dans le dossier ACL, un fichier «.fil » qui va contenir les données importées du fichier source va être créé et ACL va nous s demander de lui donner un nom Sur l’interface de l’ACL on verra une nouvelle table (approximativement similaire à une feuille dans un classeur Excel) qui affiche visuellement les données importées contenues dans le fichier «.fil » ACL va vous demander de lui donner un nom On conseille généralement de donner le même nom au fichier.fil et à sa table correspondante. Dans ce qui suit, on suppose qu’on a importé un fichier Excel appelé « factures.xls » et on a demandé à ACL d’appeler le fichier.fil « factures.fil » et la table « factures », donc visuellement on peut voir ce qui suit Vue dossier fichier source Vue interface ACL (après l’opération d’importation) Au niveau de l’interface de ACL, les données importées sont stockées sous le format « table ». $ Vue dossier du projet ACL (après l’opération d’importation) Page 15 sur 123  ❖ Gestion des champs (colonnes) par ACL Lorsque ACL importe les données à partir d’un fichier source, il détecte et affiche le type de données de chaque champ (colonne) : Texte (généralement ASCII) Date Numérique (NUMERIC) Il vous donne la possibilité soit en cours de l’importation de corriger le type suggéré par ACL, soit vous pouvez après l’opération d’importation de modifier les types des champs. ❖ Le sort des données importées : Les données importées par ACL sont « figées » / « protégées », on ne peut pas les modifier. Pour le faire il faut importer de nouvelles données. ❖ Comment importer un fichier source ▪ Clic droite sur le nom de projet ▪ Puis nouveau ▪ Puis table ▪ Puis un assistant d’importation se déclenche qui vous aidera à importer le fichier Page 16 sur 123 Page 17 sur 123  ❖ Importer un fichier Excel (cas de données structurées en tabulation) Dans ce qui suit un tableau de correspondance entre ACL et Excel en ce qui concerne les lignes et les commandes ACL EXCEL Enregistrement Ligne Champ Colonne Si vous utilisez cette version anglaise, vous pouvez importer un fichier Excel de type.xlsx, les versions françaises antérieures à celle-ci ne traitent que les fichiers Excel.xls Certaines versions anciennes de ACL ne peuvent pas importer et traiter les fichiers excel.xlsx , dans ce cas vous pouvez essayer de les convertir en fichiers Excel.xls puis les importer (mais ce n’est pas évident car si le nombre de lignes dépassant environ 365000, on ne put pas convertir xlsx en xls. Page 18 sur 123 Page 19 sur 123 NB : On ne peut pas importer toutes les feuilles d’un seul coup, il faut importer une par une. Chaque feuille sera une table à part (un fichier.fil à part) ACL vous demande de donner un nom au fichier «.fil » Page 20 sur 123 ACL vous demander de donner un nom à la nouvelle table NB : chaque table ouverte commence par une colonne à l’extrême gauche qui donne un no à chaque enregistrement (ligne) en négligeant la ligne des entêtes. Ce no est similaire à un ID pour chaque enregistrement. On verra ultérieurement qu’on peut écrire et enregistrer un commentaire pour un enregistrement. ❖ Importer un fichier texte ayant une structure de tabulation Le fichier texte illustratif est une tabulation, dont le séparateur de données est le point-virgule On va suivre la même démarche d’importation, mais, quelques étapes vont différer. On doit préciser à ACL que le séparateur est le « ; » et on peut corriger le type de données (que l’ACL a proposé). Page 21 sur 123 Page 22 sur 123 Page 23 sur 123 Le résultat d’importation est visualisé comme suit : Page 24 sur 123  ❖ Importation d’un fichier LST ou texte sans séparateurs entre les champs Le fichier. lst est généralement caractérisé par ce qui suit : ▪ L’absence d’un séparateur ▪ Le contenu d’un champ n’est pas uniforme entre tous les enregistrements : par exemple ; le code malade peut être 001 pour le malade X et 02 pour le malade Y sachant que la longueur du champ code malade est fixée à 5 caractères. Donc en utilisant la commande « convertir les données en colonnes » dans Excel, en utilisant la longueur fixe ou un séparateur n’est pas utile ici. Il faut demander à ACL de faire le découpage des données en respectant la longueur donnée de chaque champ et en commençant par un no spécifique de caractère de départ. Exemple le premier champ commence dès le premier caractère dans le fichier et s’étend sur 5 caractères. Dans ce qui suit la description de la structure d’un fichier LST Dans l’étape format de fichier, ACL va choisir « autre format de fichier » On va dans la suite cliquer sur « passer à la fin », donc ACL va importer toutes les données dans un seul champ. Après l’importation on va demander à ACL de faire le découpage de champ en plusieurs champs. Page 25 sur 123 Page 26 sur 123 On va ouvrir la nouvelle table créée Puis on va dans le menu Modifier puis format de table On va cliquer sur la petite fenêtre verte, pour créer un nouveau champ en utilisant le fichier de description Page 27 sur 123 On va donner un nom au champ et on peut donner le même nom ou un autre pour le titre de la colonne Par exemple le nom du premier champ sera « code malade » mais le nom du titre de la colonne sera CM Et on spécifie le type de données du champ (Texte, numérique ou date) On spécifie le point de départ du champ et sa longueur selon le fichier de description Puis on clique sur le symbole de Nike pour sauvegarder le nouveau champ Puis on répète la même étape pour créer tous les autres champs. Page 28 sur 123  ❖ VERIFICATION DES TYPES DE DONNEES IMPORTEES Quel que soit le fichier importé (Excel, texte, PDF…) on peut à tout moment décider (s’il y a besoin) de corriger le type de données d’un ou plusieurs champs Les types de données sont généralement ▪ N : numérique ▪ D : date ▪ C : texte (ASCII) Pour le faire, on ouvre la table Puis on va dans le menu modifier Puis Format de table Mais cette fois, on a les champs déjà définis, on choisit le champ cible On clique deux fois sur le champ cible Page 29 sur 123 On modifie le type de données puis on clique sur le symbole de Nike ❖ VERIFICATION DE L'INTEGRALITE DES DONNEES IMPORTEES Pour s'assurer que l'importation s'est déroulée correctement c'est à dire : les données importées contiennent le nombre correct d'enregistrements (lignes). Les totaux numériques des données importées correspondent aux totaux de contrôle fournis par le propriétaire des données. Les champs des données importées contiennent uniquement des données correctes. Tout d’abord, il faut avoir demandé auprès du gestionnaire en plus du fichier de données (le fichier Excel ou texte contenant les données), les données suivantes écrites sur un fichier word ou texte dans l’émail : Le nombre des lignes (enregistrements) contenues dans le fichier de données Les totaux des champs numériques contenus dans le fichier des données Les types de données des champs contenus dans le fichier des données Page 30 sur 123 Maintenant il suffit de : Compter le nombre des enregistrements dans ACL du fichier importé Aller dans le menu Analyser puis compter les enregistrements Cliquer sur ok Lire le nombre des enregistrements et le comparer au nombre de lignes reçus auprès du gestionnaire. NB : le nombre des enregistrements ne prend pas en compte la « ligne » des titres des colonnes. Page 31 sur 123 Puis calculer les totaux des champs du fichier importé dans ACL Dans le menu Analyser, cliquer sur total des champs Cliquer sur total des champs On sélectionne les champs de type Numérique On maintient l’appuie sur CTL et on clique sur les champs numériques pour les sélectionner Puis on clique sur la flèche Page 32 sur 123 Puis on clique sur ok Puis sur ok Voilà le résultat selon ACL On les compare avec les totaux du gestionnaire On peut aussi utiliser la commande Vérifier ACL va revérifier qu’il n’y a pas des erreurs dans les champs (colonnes) c’est-à-dire toutes les données de chaque colonne sont homogènes (de même type) et qu’il n’y a pas des données qui vont causer des erreurs lors des traitements ultérieurs (exemple une colonne dont l’objet est la quantité, le type est numérique, mais dans la ligne no 52 elle contient un texte et non pas un chiffre) Aller dans le menu des Données, puis cliquer sur Vérifier Page 33 sur 123 Cliquer sur Vérifier les champs Puis cliquer sur « tout inclure » Puis cliquer sur ok deux fois S’il n’y a pas des erreurs, on aura ce texte Page 34 sur 123  ❖ Les opérations possibles sur une table créée Si on fait un clic droit sur une table, on aura les options suivantes : Les options les plus utiles sont : Copier : si on veut dupliquer une table (en créer une copie) on clique sur copier puis on fait clic droit sur le nom du projet ACL Page 35 sur 123 Puis on clique sur coller Renommer : il faut fermer la table puis cliquer sur renommer Page 36 sur 123 Supprimer : on peut supprimer une table en cliquant sur « supprimer » mais le fichier de base de la table «.fil » ne sera pas supprimé, si on veut le supprimer, on doit aller à ce fichier et y cliquer supprimer Lier à une nouvelle source de données : sera expliquée ultérieurement ❖ Gestion de l'affichage du contenu d’une table Après avoir importé un fichier de données, et vérifié la bonne importation des données, on peut se poser la question sur comment afficher ou organiser le contenu de la table des données. En bas de la table affichée, il y a une flèche vers la droite et une autre vers la gauche, on peut y cliquer pour parcourir la table à droite et à gauche. On peut aussi modifier l’affichage de la table : Déplacer une colonne (changer sa position) Supprimer une colonne Récupérer la colonne supprimée Ajouter une colonne dupliquée (créer une copie ou des copies) NB : pour sélectionner un champ (une colonne dans ACL) on fait un clic_bouton_gauche de la souris sur le titre de la colonne A la fin de ces modifications, ACL va vous proposer d’enregistrer ces modifications Les modifications de l’affichage peuvent être opérées dans l’affichage par défaut (vue par défaut) mais aussi dans un nouvel affichage (une nouvelle vue). Une table peut avoir plusieurs vues. Page 37 sur 123 Pour créer une nouvelle vue (supplémentaire) Clic droite sur « vue par défaut » puis cliquer sur nouveau Puis donner un nom à la nouvelle vue Puis on sélectionne les colonnes à afficher dans cette nouvelle vue Puis on clique sur ok voilà le résultat Page 38 sur 123 Pour déplacer une colonne, utiliser la souris : faire et maintenir un clic gauche sur le titre de la colonne, déplacer la souris vers l’emplacement souhaité puis relâcher la souris Pour ajouter une colonne qui existe déjà (la dupliquer) Cliquer en haut de la table sur le symbole contenant le symbole « + » en vert Ou On sélectionne n’importe quelle colonne on fait clic droite sur la colonne puis cliquer sur « ajouter des colonnes » Page 39 sur 123 On peut sélectionner n’importe quelle colonne (même déjà affichée) puis on clique sur la flèche orientée vers la gauche puis sur ok Puis on clique sur ok Pour supprimer une colonne, on la sélectionne puis on fait clic droite puis on clique sur « supprimer les colonnes sélectionnées » Page 40 sur 123 NB : Une colonne supprimée (selon cette méthode) est supprimée uniquement au niveau de l’affichage mais elle continue à exister dans la table Pour supprimer une colonne définitivement de la table, aller dans le menu Modifier puis Format de table, faire clic droite sur le champ voulu puis cliquer sur supprimer puis sur ok NB : cette méthode ne supprime pas les données du champ supprimé, elle supprime « la reconnaissance par ACL de ce champ », on peut la reconnaitre (réajouter) ultérieurement. Page 41 sur 123 Chapitre 5 : activation des connexions/relations entre les différentes données importées Après l’importation des fichiers de données, un par un. Peut-être il y a des connexions entre ces tables qu’il faut activer. ❖ Les Connexions possibles entre les tables dans ACL Caractéristiques/Type Fusion Jointure Liaison de relation Nombre de tables Deux Deux Pouvant atteindre 18 Type de table Forcément deux Généralement Généralement tables de même deux tables de des tables de structure et Triés structures structures (même type/sens différentes différentes de tri) Résultat attendu Création Création Pas de création systématique systématique systématique de d’une nouvelle d’une nouvelle nouvelle table (la table de même table qui peut liaison entre les structure avoir une tables permet de nouvelle considérer structure implicitement (sélection de toutes les tables champs comme une (colonnes) issus seule table) mais de ces des deux l’utilisateur grâce tables) à la liaison peut La sélection du ajouter dans la type de jonction table primaire approprié des champs issus détermine quels Page 42 sur 123 Caractéristiques/Type Fusion Jointure Liaison de relation enregistrements des autres tables sont inclus dans liées la table de sortie La commande Relations produit un résultat similaire à celui de la commande Joindre avec l'option « Tout primaire » sélectionnée. Exemple d’utilisation vous pouvez On dispose On dispose utiliser la d’une table des d’une table commande factures et une clients, une table Fusionner pour table des factures, une combiner deux commandes et table produits, tables possédant on veut créer une table des structures une table qui commande, on d'enregistrement comprend des veut créer une identiques champs issus des table ou un couvrant deux tables rapport qui différentes comprend des périodes ou champs issus de filiales, etc plusieurs tables liées entre elles Outil de relation Soit fusion directe via une clé (un via une clé (un Soit fusion via champ commun champ commun une clé commune entre les deux entre les deux ou multiples (un tables) tables) ou des champs Page 43 sur 123  Caractéristiques/Type Fusion Jointure Liaison de relation communs entre les deux tables) Comment ACL voit La première table La première La première il les tables ? ouverte est table ouverte est table ouverte est considérée considérée considérée comme table comme table comme table primaire primaire parent (primaire) et les autres tables « enfants » (secondaires) ❖ Types de connexions dans Access vs ACL Il existe trois types de connexions entre les tables dans Access. Relation un-à-plusieurs Utilisons comme exemple une base de données de suivi des commandes qui comprend une table Clients et une table Commandes. Un client peut passer toutes les commandes qu’il souhaite. De ce fait, à chaque client représenté dans la table Clients peut correspondre un grand nombre de commandes dans la table Commandes. La relation entre la table Clients et la table Commandes est une relation un-à-plusieurs. Pour représenter une relation un-à-plusieurs dans la structure de votre base de données, prenez la clé primaire du côté « un » de la relation et ajoutez-la comme champ supplémentaire à la table du côté « plusieurs » de cette relation. Dans ce cas, vous ajoutez par exemple un nouveau champ (le champ ID de la table Clients) à la table Commandes et vous l’appelez Réf client. Access peut alors utiliser le num Réf client dans la table Commandes pour rechercher le client approprié pour chaque commande. Relation plusieurs-à-plusieurs Examinons maintenant la relation entre une table Produits et une table Commandes. Une même commande peut porter sur plusieurs produits. D’un autre côté, un même produit peut figurer sur plusieurs commandes. Ainsi, vous pouvez avoir plusieurs enregistrements dans la table Produits pour chaque enregistrement de la table Commandes. En outre, vous pouvez avoir plusieurs enregistrements dans la table Commandes pour chaque enregistrement de la table Produits. Cette relation est appelée relation plusieurs-à-plusieurs. Pour identifier les relations plusieurs-à-plusieurs existantes entre vos tables, il est important de prendre en compte les deux côtés de la relation. Pour représenter une relation plusieurs-à-plusieurs, vous devez créer une troisième table, souvent appelée « table de jonction », qui décompose la relation plusieurs-à-plusieurs en deux relations un-à- Page 44 sur 123 plusieurs. Vous devez insérer la clé primaire de chacune des deux tables dans la troisième. Par conséquent, la troisième table enregistre chaque occurrence (ou instance) de cette relation. Par exemple, la table Commandes et la table Produits ont une relation plusieurs-à-plusieurs qui est définie en créant deux relations un-à-plusieurs avec la table Détails commande. Une commande peut porter sur plusieurs produits et chaque produit peut figurer sur plusieurs commandes. Relation un-à-un Dans une relation un-à-un, chaque enregistrement de la première table ne peut avoir qu’un seul enregistrement correspondant dans la deuxième, et chaque enregistrement de la deuxième table ne peut avoir qu’un seul enregistrement correspondant dans la première. Cette relation n’est pas courante sachant que les informations ainsi liées sont en général stockées dans la même table. Vous pouvez utiliser une relation un-à-un pour diviser une table contenant de nombreux champs, pour isoler une partie d’une table pour des raisons de sécurité, ou pour stocker des informations qui s’appliquent uniquement à un sous-ensemble de la table principale. Lorsque vous identifiez une relation de ce type, les deux tables doivent partager un champ commun ❖ Il existe deux types de connexions entre les tables dans ACL: de un à un et de plusieurs à un le sens de la relation dépend de la sélection de la table parent (première table ouverte) cette table émet une relation vers n'importe quelle table enfant mais n'accepte pas de relation l'opération d'association fait correspondre les valeurs clés parent à la première occurrence uniquement d'une valeur clé enfant correspondante. Si d'autres occurrences d'une valeur clé enfant correspondante existent, elles sont ignorées. Vous devez tenir compte de ce comportement lorsque vous planifiez les relations entre vos tables, notamment si une table enfant contient plusieurs occurrences légitimes d'une valeur clé correspondante. L'une des méthodes consiste à essayer d'inverser la relation entre les deux tables, en faisant de la table enfant la table parent et inversement. Dans ACL : la liaison la plus appropriée est de un à un. Il n’est pas faisable de faire une liaison de type : 1 à plusieurs, il faut faire l’inverse : de plusieurs à un. NB : Une clé commune peut être un seul champ exemple n° ID ou n° CIN ou peut être aussi un ensemble de champ exemple n° incident, année et id opérateur. ❖ Les prérequis de la fusion de deux tables On veut fusionner une table « a » (couvrant une période t1) avec une table b (couvrant une période t2), les deux ont le même objet (les commandes passées) Fusionner a +b est identique à fusionner b+a On peut faire le tri au cours de la fusion ou postérieurement. Page 45 sur 123 Avant de fusionner si on n’est pas certain que les deux tables ont la même structure= même nombre de champs + même type de données+même longueur champ, on peut comparer leurs structures, sachant que : Pour garantir le succès de la fusion entre deux fichiers, les enregistrements dans les deux tables doivent avoir la même structure : ▪ Le nombre des champs (colonnes) et l’ordre des champs doivent être identiques entre les deux tables ▪ Les champs communs doivent avoir les mêmes types de données ▪ Le point de départ des champs et la longueur des champs doivent être identiques entre les deux tables ▪ Pour les champs « dates », le format date doit être identique entre les deux tables Cependant : ▪ Le titre des colonnes est négligé par ACL lors de la fusion ▪ Le nombre de lignes (enregistrement) est négligé par ACL lors de la fusion ❖ Comment comparer la structure des deux tables Ouvrir table a Cliquer sur le menu Fenêtre puis sur afficher la ligne de commande Taper « dispaly » dans la barre de formule de la ligne de commande puis cliquer sur le symbole de Nike Page 46 sur 123 Le résultat sera comme suit : Faire De même pour la table « b » Puis copier les deux tableaux de structure sur Excel et faire une comparaison visuelle ou via la fonction exacte. ❖ Comment fusionner deux tables Ouvrir l’une des deux tables Puis aller dans le menu Données puis cliquer sur Fusionner des tables Page 47 sur 123 La table ouverte est considérée comme la table principale ACl va lister dans les clés primaires/secondaires (il va lister tous les champs dont le type est texte) Il faut au moins, sélectionner un champ (le même) dans les clés primaires et les clés secondaires. Cette clé sera utilisée par ACL pour trier les données lors de la fusion. Par défaut, ACL a coché « prétrier » donc il va trier les données. Ajouter le nom de la nouvelle table (qui est la fusion des deux tables) l’écrire à coté de « Vers » Dans l’exemple suivant on a choisi une seule clé (article) On a donné le nom « a+b » pour la nouvelle table Page 48 sur 123 Le résultat de fusion est maintenant visible : une nouvelle table a été créée ❖ Joindre (le cas le plus fréquent : comment ajouter à une table primaire des champs issus d’une table secondaire) Exemple1 : une table primaire liste 150 employés en congé (ID employé) on veut lui ajouter les champs (adresse, no tel) qui se trouvent dans une autre table « employés de la société » Une clé commune entre les deux tables est obligatoire, elle peut être un seul champ ou plusieurs champs (dans Excel « plusieurs champs » est équivalent à « concaténer plusieurs colonnes ») Exemple2 : On veut ajouter une donnée sur l’existence ou non d’un contrat d’assurance pour tout véhicule du parc auto sachant qu’on a une table sur les autos assurées et une table sur tous les autos de la société Ouvrir la table autos de la société (table primaire) Puis aller dans le menu Données puis cliquer sur Joindre des tables Page 49 sur 123 Maintenant il faut remplir le formulaire : A droite, en haut, il faut choisir la table secondaire Choisir la clé primaire et la clé secondaire (dans notre cas, la clé commune est le matricule de l’auto) Spécifier les champs issus de la table primaire qu’on veut afficher (ici on a sélectionné « matricule » et « état ») Spécifier les champs issus la table secondaire qu’on veut afficher (ici on a sélectionné « code contrat assurance ») Taper le nom de la nouvelle table à coté de « vers », ici on a tapé « t1 » Page 50 sur 123 En haut du formulaire, cliquer sur Plus Puis cocher « inclure tous les enregistrements primaires » Puis cliquer sur ok Le résultat sera comme suit : NB : ACL a écrit « 0 » devant chaque auto dont il n’a pas trouvé une correspondance dans la table des autos assurées Page 51 sur 123  ❖ Comment faire la liaison dans ACL Soient les tables suivantes : ▪ Table « clients » ▪ Table « commandes » ▪ Table « produits » Comment expliqué avant, dans ACL on ne peut pas faire la liaison de type 1 à plusieurs il faut faire l’inverse Don au lien de lier entre 1 client et plusieurs commandes, on va lier plusieurs commandes avec un client De même pour la relation produit et commandes Pratiquement, on va ouvrir la table « commandes » qui est la table parent ou mère Ouvrir table parent Page 52 sur 123 Puis cliquer en haut à droite sur le symbole de relation Puis Ou bien aller dans le menu Données puis cliquer sur Associer des tables Page 53 sur 123 La table commandes est déjà affichée Cliquer sur ajouter une table (pour afficher les deux autres tables) On ajoute à chaque fois une table (on clique sur par exemple « clients » puis sur ajouter puis de nouveau, on clique sur ajouter une table …..pour ajouter la table produits On peut à l’aide de la souris, déplacer librement les tables affichées Page 54 sur 123 Maintenant dans la table commande on tient par la souris le champ « code client » et on le « déplace » vers le champ code client de la table clients (juste pour faire la liaison) On peut faire double clic sur la flèche de relation pour lire son contenu et corriger (s’il y a lieu) Page 55 sur 123 De la même manière faire la liaison entre « le code produit » de la table commandes avec le code produit de la table produits Page 56 sur 123 Si on clique sur la dernière flèche on aura ce résultat Cliquer sur ok puis sur terminer Est-ce que « la relation » établie entre les 3 tables crée une nouvelle table ? Non, Pour créer une nouvelle table qui va bénéficier de la relation établie, il faut aller dans le menu Données puis cliquer sur Extraire les données Page 57 sur 123 Puis cliquer sur le bouton champs à extraire Puis sélectionner une table des trois tables puis sélectionner les champs issus de cette table Ici on a sélectionné les champs « code commande » « code produit » « code client » de la table commandes Page 58 sur 123 De la table clients on a ajouté « nom clients » De la table produit on a ajouté « nom produit » Page 59 sur 123 Puis cliquer sur ok Puis donner un nom à la nouvelle table (à écrire à droite de « vers…) ici on a tapé « commande_v2 Le résultat sera le suivant Page 60 sur 123 Chapitre 6 : définir le périmètre des données à auditer et leur mise en ordre Dans ce chapitre on va voir comment définir le périmètre des données à auditer (en l’occurrence définir une sous partie ode la population) et comment mettre en ordre (trier) ces données. ❖ Comment filtrer dans ACL : Dans ACL, il y a deux types de filtres : ▪ Filtre express : c’est un filtre similaire à celui de l’Excel mais qui n’est pas enregistré, si on ferme la table filtrée et on la réouvre le filtre disparaît ▪ Filtre enregistré : ce type de filtre n’existe pas dans Excel, en effet on peut créer et enregistrer plusieurs filtres sur une même table. Chaque filtre enregistré a un nom, l’utilisateur peut afficher la liste des filtres enregistrés et choisir un pour l’appliquer. NB : en cas d’application d’un filtre express ou d’un filtre enregistré, les commandes (totaliser…) vont s'exécuter sur la population filtrée Comment faire un filtre express Ouvrir une table dans notre exemple on a ouvert une table « bulletins de soin » on veut filtrer sur les bulletins uniquement des femmes, donc dans la colonne Sexe, on a va filtrer sur « F » pour le faire Aller sur la colonne Sexe puis faire clic droite sur une valeur « F » n’importe laquelle Page 61 sur 123 Puis choisir la formule du filtre appropriée , dans ce cas, choir « égal à » donc égal à F Dans la barre de formule on voit la formule du filtre SEXE = ‘’F’’ On peut continuer à filtrer sur d’autres colonnes dans la logique de « ET » ou « OU » Page 62 sur 123 Donc on va sur une autre colonne on clique sur une valeur cible (exemple dans la colonne montant remboursé, on fait clic droite sur « 9000 » alors on doit préciser à ACL la logique du nouveau filre Est-ce qu’elle est de type AND (ET) ou de type OR(OU) Dans l’exemple suivant, la formule du filtre indique de filtrer sur les bulletins de soin des hommes et dont le montant remboursé dépasse 9000 dinars Comment annuler un filtre : Cliquer sur le filtre qui porte une X rouge Ou supprimer (effacer) le contenu de la formule fx ❖ Comment enregistrer un filtre déjà créé Cliquer sur fx Page 63 sur 123 taper un nom dans enregistrer sous (ne pas utiliser « espace » dans le nom, ici on a tapé « SM_MRsup9 » puis cliquer sur ok Page 64 sur 123 Pour afficher le filtre ou les filtres enregistrés ultérieurement Cliquer sur fx puis sélectionner le filtre Cliquer sur le filtre deux fois puis sur ok Pour modifier un filtre enregistré Cliquer sur le symbole du filtre Cliquer sur le filtre cible Puis sur ok Page 65 sur 123 Puis lire la formule du filtre et la modifier puis cliquer sur OK ❖ Comment Exporter/Convertir la table filtrée en une nouvelle table Aller dans le menu Données puis cliquer sur Extraire les données Ici on garde la sélection de Enregistrement (et non pas champs) Page 66 sur 123 On tape le nom de la nouvelle table à coté de Vers… Et on aura le résultat suivant ❖ Comment Exporter/Convertir la table filtrée en un fichier (Excel ou texte par exemple) Aller dans le menu Données puis cliquer sur Extraire les données vers d’autres applications A droite, il faut spécifier le format du fichier (texte ou excel….) Cocher « exporter avec les noms des champs » Page 67 sur 123 Cliquer sur vue pour exporter la table ouverte (la table filtrée) Cliquer sur « Choisir champs pour exporter un ou plusieurs champs » de la table ouverte (la table filtrée) Cliquer sur Vers pour spécifier l’emplacement du nouveau fichier et lui donner un nom Page 68 sur 123 Puis cliquer sur ok ACL va afficher un petit rapport sur le succès de l’exportation Comment appliquer un tri (si nécessaire) sur le périmètre choisi (population totale ou population filtrée) Il y a deux types de tri dans ACL ▪ Tri express ▪ Tri enregistré Sélectionner une colonne cible (en cliquant sur le titre de la colonne) Page 69 sur 123 Puis faire clic droite sur le titre de la colonne Puis cliquer sur tri express croissant ou décroissant ❖ Comment enregistrer un tri : Aller dans le menu Données puis cliquer sur Trier les enregistrements Page 70 sur 123 Cliquer sur tier sur On peut ajouter un ou plusieurs champs (facteur de tri selon l’ordre que nous préférons) On va ajouter le premier puis le deuxième…. Pour chaque champ ajouté on doit spécifier le sens « croissant » (flèche vers le haut) ou « décroissant » (flèche vers le bas), en cliquant sur un champ ajouté à la partie à droite on change le sens de la flèche Page 71 sur 123 Dans ce cas, on a demandé à ACL de trier sur le sexe (croissant) puis sur la date (croissant) Cliquer sur ok Puis donner un nom à la nouvelle table triée (un tri enregistré = une nouvelle table triée) Cliquer sur ok , on aura le résultat suivant : Page 72 sur 123 Page 73 sur 123 Chapitre 7 : prise de connaissance générale des données de la population cible (audit macro) On va étudier les principales commandes du menu Analyser ❖ Statistique/statistiques Calcule principalement le total, la moyenne, l’écart type d'un champ ou plusieurs champs de données sélectionnés Ouvrir la table Puis aller dans le menu Analyser Cliquer sur statistiques puis sur statistiques Sélectionner les champs numériques cibles (directement ou en cliquant sur « statistiques sur » Page 74 sur 123 On peut cocher écart type Dans notre cas, on a sélectionné les champs : quantité facturée et le montant facturé Le résultat sera comme suit : ACL va calculer notamment les totaux, la moyenne… Page 75 sur 123 Dans Excel, on a une commande appelée « statistiques descriptives » similaire à celle de l’ACL Aller dans le menu Fichier Puis cliquer sur options sur compléments Page 76 sur 123 Puis en bas, devant « Gérer » choisir Compléments Excel Puis cliquer sur Atteindre Puis cocher Analysis Toolpak Page 77 sur 123 Aller dans le menu Données Puis cliquer sur utilitaire d’analyse Sélectionner statistiques descriptives Puis cliquer sur ok Spécifier la plage d’entrée dans le formulaire qui apparaît : y cliquer et sélectionner les colonnes numériques qu’on veut analyser (y compris les titres des colonnes) Cocher intitulé en première ligne Spécifier la plage de sortie : sélectionner au moins une cellule vide qui servira à définir le début de l’emplacement du tableau statistique qui va être crée Cocher rapport détaillé Voilà le tableau statistique de deux colonnes sélectionnées Page 78 sur 123 ❖ Statistiques/profiler Calcule principalement le total, le minimum et le maximum d'un champ ou de plusieurs champs de données sélectionnés Cette fois ci, on va cliquer sur statistiques/profiler au lieu de statistique/statistiques On va électionner les champs numériques à profiler soit directement soit en cliquant sur le bouton « profiler les champs » Page 79 sur 123 Le résultat sera comme suit ❖ Histogramme Similaire au graphique dans Excel Aller dans le menu Données puis cliquer sur Histogramme Page 80 sur 123 Cliquer sur Histogramme sur puis cliquer sur Histogrammer sur pour choisir l’élemnt des axes des absises (ici on a choisi « id commande ») puis cliquer sur ok Maitenant on doit choisir l’element de l’axe des Y, ici on a choisi le montant puis on doit spécifier l’intervalle, le min et le max Page 81 sur 123 Le résultat sera comme suit ❖ Tabulation croisée Équivalent du tableau croisé dynamique dans Excel. Mais il n’est pas pratique et utile comme celui de l’Excel. Page 82 sur 123 Aller dans le menu Analyser Clier sur Tabulation croisée Remplir le formulaire Page 83 sur 123 Voilà un exemple d’une tabulation croisée dans ACL ❖ Stratifier Permet de créer des intervalles sur un champ numérique « A » sélectionné et calculer pour chaque intervalle le total du même champ « A » ou d'un autre champ numérique sélectionné. l’exemple le plus pertinent pour cette commande est l’analyse entre montant et quantité ou quantité et montant Aller dans le menu Analyser Cliquer sur stratifier On doit remplir le formulaire : Champs à stratifier : y cliquer et choirir le champ numérique à stratifier (ici, on a choisi le champ « montant facture » , on doit spécifier le minimum voulu et le maximum voulu et l’intervalle voulu à afficher pour ce champ Puis on doit sélectionner le champ de sous total (ici on a choisi le champ quantité facturée) Page 84 sur 123 Le résultat sera le suivant ❖ Classement par antériorité Permet de créer des intervalles sur un champ de date et calculer pour chaque intervalle le total d’un champ numérique sélectionné Cliquer sur classmeent par antériorité Page 85 sur 123 Cliquer sur Antériorité sur et choisir la colonne date cible (ici on a choisi la colonne date facture) Puis il faut spécifier la date de référence voulue c’est la date à partir de lquelle on revient en arrière dans l’analyse ( généralement c’est la date de fin de période, si on a les ventes de l’année 2022, la date de réfrence sera 31/12/2022) Puis on spécifie les périodes d’antérorité (exemple 90 jours, 180 jours…) Enfin on spécifie le ou les champs de sous total (ici on a choisi la quantité facturé et le montant facturé) Le résultat sera comme suit Page 86 sur 123  ❖ Total des champs Détermine le total d'une ou plusieurs colonnes contenant des valeurs numériques sélectionnée(s) Cliquer sur Total des champs ACL affiche les champs numériques Sélectionner le ou les champs numériques voulus puis cliquer sur ok Le résultat sera comme suit Page 87 sur 123 ❖ Totaliser Équivalent dans Excel à sous total (exemple calcul du total des ventes par client) Cliquer sur totaliser Spécifier le champ « totaliser sur » ici on a choisi « client » Spécifier le ou les champs de sous total ici on a choisi la quantité et le montant Page 88 sur 123 Cliquer en haut sur l’onglet Sortie et choisir Ecran (pour afficher le tableau sur l’écran) Si on choisit Fichier, ACL va créer une table et son fichier Page 89 sur 123 Le résultat sera comme suit ❖ Classer C’est la commande totaliser + affichage des % d'importance/représentation Cliquer sur classer Page 90 sur 123 Spécifier le champ « classer sur » ici on a choisi client Sélectionner le ou les champs de sous-total ici on a choisi quantité et montant En haut dans l’onglet Sortie, choisir sortie vers Ecran ou vers Fichier (nouvelle table) Page 91 sur 123 Le résultat sera comme suit Page 92 sur 123 Chapitre 8 : audit des données de la population cible (audit micro) ❖ Rechercher les doublons Détermine les valeurs doubles (équivalent dans Excel à Valeur en double) Aller dans le menu Analyser Cliquer sur rechercher les doublons Cliquer sur le bouton Doublons sur Puis sélectionner et ajouter un ou plusieurs éléments caractérisant un doublon (dans notre exemple on cherche un doublon au niveau du no de facture, dans un autre cas on peut chercher doublon sur la date commande, le nom du client et le montant de la commande…) Puis cliquer sur le bouton lister les champas pour déterminer les champs à afficher pour les doublons détectés ici on choisi no facture et no interne Page 93 sur 123 Cliquer sur l’onglet sortie et choisir sortie vers écran ou vers fichier (nouvelle table) Nb :ACL va afficher les doublons (toute valeur qui est doublée une ou plusieurs fois) par exemple s’il y a 5 factures dont chacune affiche le no F5200 alors ACL va afficher en liste ces 5 factures. Page 94 sur 123 Le résultat sera comme suit : ❖ Rechercher les doublons dans Excel : Sélectionner la colonne objet du doublon Aller au menu accueil Cliquer sur règles de mise en surbrillance des cellules puis valeurs en double Page 95 sur 123 On peut faire par la suite tri sur couleur ou filtre de couleur pour afficher les valeurs en double en haut du tableau ❖ Rechercher les omissions Vérifie s’il y a des valeurs manquantes dans une séquence et affiche les éléments manquants ou leurs positions selon ce que choisit l’utilisateur Dans le menu Analyser cliquer sur rechercher les omissions Page 96 sur 123 Cliquer sur le bouton omissions sur puis choisir 1 ou plusieurs éléments. Dans notre cas, on a choisi le champ no de facture En bas du formulaire on choisit : Soit afficher les omissions soit afficher les éléments manquants Page 97 sur 123 Si on choisit afficher les omissions, on aura les positions des factures qui manquent et leurs nombre par exemple en bas du table on voit qu’il manque 2 factures situées entre les n° 1187 et 1190 Si on choisit afficher les éléments manquants on aura une liste des factures qui manquent Page 98 sur 123 ❖ Séquence : Permet de vérifier si le tri attendu (que l’utilisateur présume son existence) existe. Il revient à l’utilisateur de définir (et non d’appliquer) dans la fonction séquence le tri attendu. Parfois les tables sont de taille très importante, alors le fait de trier ces tables prendra du temps, donc il vaut mieux vérifier à priori si déjà le tri existe. Si la fonction séquence indique des erreurs, dans ce cas l’utilisateur grâce à une autre fonction, peut trier les tables. Il y a des opérations et des relations dans ACL qui exigent que les tables soient triées avant leurs exécutions. Séquence dans ACL ne veut pas dire rechercher d’omission ou d’éléments manquants, tout simplement l’utilisateur présume par exemple que les factures sont triées suivant le no de facture de façon croissante, donc il va demander à ACL de vérifier l’exactitude cette présomption Supposons que les factures sont triées comme suit : ▪ 112 ▪ 113 ▪ 116 Si ACL vérifie cette liste, il n’y a pas d’erreur dans la séquence Si la liste est comme suit ▪ 112 ▪ 113 ▪ 116 ▪ 115 ▪ 117 ▪ 118 Alors ACL va détecter la première erreur dans la séquence qui est au niveau du 115, car la première séquence était croissante 112-113-116 puis la séquence a été « cassée » par un no 115 inférieur au no 116 qui lui précède, puis ACL va compter une nouvelle séquence qui commence par 117 ainsi de suite Aller dans le menu Analyser puis cliquer sur Séquence Page 99 sur 123 Puis cliquer sur le bouton séquence et sélectionner un ou plusieurs champs, dans notre cas, on a choisi le champ no interne Il faut Spécifier l’ordre du champ sélectionné (croissant ou décroissant) Puis Aller dans l’onglet plus La limite d’erreur est 10 mais on peut la changer Page 100 sur 123 Cliquer sur ok Le résultat sera comme suit : ACL affiche chaque no interne qui « casse » la séquence Page 101 sur 123  ❖ Ajout/suppression de colonnes pour calculer comme dans Excel et exposé d'une formule prédéfinie de l'ACL On sélectionne une colonne (en cliquant sur son titre) pour spécifier l’emplacement de la nouvelle colonne. La nouvelle va être ajoutée avant la colonne sélectionnée. Puis on clique sur le symbole + en vert en haut Puis on clique sur le bouton Expr…(expression) On aura l’interface suivante composée de : En haut à gauche : la barre de formule similaire à celle dans Excel Sour cette barre, il y a une liste des champs (colonnes) qu’on peut ajouter dans les formules (dans notre cas on va calculer le nombre de jours entre la date émission du paiement et la date de l’ordonnancement En haut à droite, on doit renseigner le nom de la nouvelle colonne en dessous de « enregistrer sous », on peut cliquer sur vérifier pour tester la faisabilité de la formule Page 102 sur 123 En dessous, à droite, il y a une liste des formules prédéfinies qu’on peut utiliser (mathématiques, finance..) similaires à celles dans Excel De façon plus simple : Dans la barre expression écrire la formule : Cas 1 on peut utiliser une fonction déjà prédéfinie et on l’applique sur une colonne déjà existante Cas 2 on va écrire une formule qui fait appel à une ou plusieurs colonnes exemple dans notre cas la formule est « : titre colonne A – titre colonne B » Puis on donne un nom de colonne « enregistrer sous » dans notre cas « délai » Puis OK puis OK, on aura cette nouvelle colonne Si on ferme la table, ACL va vous demande d’enregistrer cette modification On clique sur oui pour enregistrer la nouvelle colonne Page 103 sur 123 Si on veut supprimer cette colonne de l’affichage, on sélectionne la colonne puis clic droite supprimer Si on veut supprimer cette colonne de la table définitivement aller à format de table puis sélectionner la colonne puis cliquer sur supprimer ❖ Les différents types de jointure Une jointure se faite entre une première table ouverte (table principale) et une autre table sélectionnée (table secondaire) lors du remplissage du formulaire de jointure. Exemple : Table primaire comprend les éléments : a, b, d, e, f Table secondaire comprend les éléments : d, e, r, z Les types de jointure sont : Page 104 sur 123 Cas1 : Enregistrements primaires correspondants : c’est-à-dire n’afficher que les enregistrements de la table primaire qui ont des correspondants dans la table secondaires Résultat : d-d, e-e Cas 2 : Enregistrements primaires correspondants + Inclure tous les enregistrements primaires c’est- à-dire afficher tous les enregistrements primaires même ceux n’ayant pas de correspondants dans la table secondaire (mettre 0 devant les primaires sans correspondants) Résultat : a-0, b-0, d-d, e-e Cas 3 Enregistrements primaires correspondants + Inclure tous les enregistrements secondaires : afficher les enregistrements de la table primaire qui ont des correspondants dans la table secondaires et les enregistrements de la table secondaire n’ayant pas de correspondants dans la table primaire Résultat :d-d,e-e,0-r,0-z Cas 4 : Enregistrements primaires correspondants + Inclure tous les enregistrements primaires+ Inclure tous les enregistrements secondaires Résultat :d-d,e-e,a-0,b-0,0-r,0-z Cas 5 : Enregistrements primaires non correspondants : afficher les enregistrements primaires n’ayant pas de correspondants dans la table secondaire Résultat :a-0,b-0 NB : on peut inverser le choix des tables (primaire-secondaire) si on veut inverser la logique de notre test. Comparaison entre la jointure dans ACL et celle dans Access : Page 105 sur 123 On constate que les cas 1, 2, 3 de jointure dans ACL ont leurs homologues dans Access. Les cas 4 et 5 n’ont pas leurs homologues à priori. Cas pratique Soient : La table parc auto qui comprend tous les véhicules de la société (quelque l’état du véhicule) La table parc auto assuré qui comprend uniquement les véhicules assurés Page 106 sur 123 Nos tests : Test1 : Vérifier que tous les véhicules en activité sont assurés (détecter les cas non assurés) Test2 : Vérifier qu’il n’y a pas un véhicule en rebut et qui est assuré (détecter les cas assurés) Test3 : vérifier que tout véhicule assuré existe encore dans le parc auto (c’est-à-dire il n’est cédé) détecter les véhicules assurés et qui n’existent pas dans le parc auto Pour faire le test1 : Ouvrir la table parc auto et filtrer sur en activité Aller dans le menu données puis Joindre les tables Page 107 sur 123 Dans les clés primaires, on va sélectionner le matricule Dans l’onglet table secondaire on sélectionne la table parc auto assuré Dans les clés secondaires on sélectionne matricule Dans les champs primaires, on sélectionne les colonnes de la table primaire à afficher, on sélectionne matricule et état Dans les champs secondaires, on sélectionne le champ code assurance de la table secondaire pour l’afficher On doit donner un nom à la nouvelle table qui sera créée, taper le nom à côté du « vers…) Dans l’onglet Plus On va sélectionner le type de jointure, On peut choisir directement le cas 5 Cas 5 : Enregistrements primaires non correspondants : afficher les enregistrements primaires n’ayant pas de correspondants dans la table secondaire Le résultat sera les autos en activité et qui ne sont pas assurés Puis on clique sur ok Page 108 sur 123 Test2 : Vérifier qu’il n’y a pas un véhicule en rebut et qui est assuré (détecter les cas assurés) Pour faire le test2 : On peut filtrer sur les autos en rebut puis choisir dans les types de jointure le cas : Cas1 : Enregistrements primaires correspondants : c’est-à-dire n’afficher que les enregistrements de la table primaire qui ont des correspondants dans la table secondaires Le résultat sera : les autos en rebut qui sont assurés Test3 : vérifier que tout véhicule assuré existe encore dans le parc auto (c’est-à-dire il n’est cédé) détecter les véhicules assurés et qui n’existent pas dans le parc auto Ouvrir la table parc auto assuré (table primaire) Puis dans les types de jointure, choisir le cas Cas 5 : Enregistrements primaires non correspondants : afficher les enregistrements primaires n’ayant pas de correspondants dans la table secondaire Le résultat sera : les autos assurées mais qui n’existent pas dans le parc auto de la société ❖ Ecrire des commentaires On peut écrire et enregistrer des commentaires sur les lignes (enregistrements) par exemple pour les lignes suspectées ou à risque… ▪ Pour le faire, aller sur la première colonne de la table (la colonne affichant les no des enregistrements) ▪ Choisir la ligne voulue ▪ Puis faire clic droite ▪ Puis cliquer sur modifier le commentaire Page 109 sur 123 ▪ Ecrire le commentaire ▪ Puis cliquer sur ok Page 110 sur 123 Chapitre 9 : Maitrise de l’affichage et de l’enregistrement des résultats des tests ❖ Affichage et enregistrement des résultats de tests La nature de l’affichage et de l’enregistrement des résultats de tests, dépend de la nature des fonctions/commande utilisées. ▪ En effet, le résultat est, dans certains cas, un affichage direct sur écran (exemple commande statistique, tabulation croisée) ▪ Dans certains cas, il est direct sur l’écran par défaut mais ACL nous donne le choix de faire sortir le résultat sous forme d’une nouvelle table (exemple commande classer, totaliser) ▪ Dans certains cas, ACL exige la création d’une nouvelle table et nous demande de la nommer (exemple dans le cas de la commande trier) Exemple pratique ; si on utilise la commande statistique : l’affichage se fait sur l’écran et on peut faire copier-coller les tableaux affichés sur Word ou Excel ou les imprimer sous format PDF (si on a déjà installé une imprimante PDF) ❖ Organisation de l’affichage des tables On peut organiser sur l’interface de l’ACL la disposition des tables du projet ACL et ce en les répartissant sur des dossiers virtuels par exemple on peut créer deux sous dossiers virtuels : ▪ L’un appelé « table fichiers sources » Dossier inputs ▪ L’autre appelé « table résultats de test » Dossier outputs La photo suivante peut illustrée l’idée : Pour créer un dossier virtuel, on fait clic droite sur le nom du projet ACL Puis Nouveau puis Dossier puis donner un nom au dossier Page 111 sur 123 Après la création des sous dossiers virtuels, on glisse les tables dans ces dossiers. NB : les dossiers « d’organisation » sont virtuels c’est-à-dire ils ne sont pas créés physiquement dans le dossier du projet ACL. Ça sera très pertinent si dans le dossier projet ACL, on crée un dossier inputs (pour y mettre les fichiers sources PDF Excel…) un dossier « inputs importés » dans lequel on enregistre le fichier. fil de toute nouvelle table crée suite à l’importation d’un fichier source et un dossier outputs dans lequel on enregistre le fichier.fil de chaque table créée issue d’une extraction ou d’un test. ❖ Consultation et enregistrement de la trace des activités ACL enregistre automatiquement l’historique des activités de l’utilisateur : c’est-à-dire il va écrire tout ce que l’utilisateur a fait étape par étape (ouverture de la table…, Commande totaliser, affichage du résultat de la commande totaliser…) Cet enregistrement est très utile notamment pour le chef de mission qui va pouvoir lire et comprendre parfaitement tout ce qui a fait l’auditeur et comment il est arrivé aux observations. Pour afficher cet historique, il y a deux manières pour le faire Soit : On clique deux fois sur la photo comprenant un stylo Soit : En bas on clique sur l’onglet « trace » Page 112 sur 123 Dans les deux cas, le résultat sera comme suit Pour afficher le contenu des traces, il faut les sélectionner une période (exemple dès la date de départ du projet ou les derniers 7 jours….) et ce en cochant les dates cibles Page 113 sur 123 Puis faire clic droite puis choisir le format d’exportation (on conseille de choisir html) Puis spécifier le nom du fichier à exporter et son emplacement Page 114 sur 123 Puis à l’extérieur de ACL, aller au fichier enregistré et l’ouvrir ouvrir Le résultat de l’ouverture de ce fichier peut être comme suit : Page 115 sur 123 NB : l’auditeur peut supprimer une partie des traces s’il a fait un test faux….et ne garder que les bonnes traces (correctes). Il sélectionne les traces « fausses » et fait clic droite puis supprimer. Page 116 sur 123 Chapitre 10 : mise à jour des données importées et utilisation des scripts ❖ Mettre à jour une table de données importées Si le gestionnaire, après un certain temps, il nous envoie une nouvelle version du fichier des données. Est-ce qu’il faut supprimer la table existante et refaire l’importation du nouveau fichier ? Dans ACL il y a deux techniques de mise à jour des données qui dépendent du type de fichier de données importé. La technique de « lier à une nouvelle source de données » est utilisable pour mettre à jour une table qui a importé les données d’un fichier source non structuré en tabulation (exemple le fichier LST) La technique « rafraichir depuis la source » est utilisable pour mettre à jour une table qui a importé les données d’un fichier Excel ou texte structuré en tabulation. Il faut supprimer le fichier de données source et le remplacer par un fichier plus récent portant le même nom et dans le même emplacement. Pratiquement *si le fichier importé est non structuré en tabulation, on utilise la fonctionnalité "lier à une nouvelle source de données" On fait clic droite sur la table cible puis on clique sur « lier à une nouvelle source de données » On choisit Client puis on clique sur ok Page 117 sur 123 Puis on sélectionne l’emplacement puis le fichier source données le plus récent Si on a un nouveau fichier source de données structuré en tabulation, qui a la même structure que l’ancien fichier mais qui connait des changements au niveau des lignes alors on utilise la technique de rafraichir depuis la source. Mais, avant de l'utiliser il faut Soit supprimer le fichier source et le remplacer par un autre fichier portant le même nom (et dans le même emplacement) et ayant la même structure Soit on garde le même fichier source de données, on l’ouvre et on met à jour le tableau (par copier- coller par exemple) Puis on fait clic droite sur la table cible puis on clique sur rafraichir depuis la source Puis on confirme la mise à jour en cliquant sur ok Page 118 sur 123  ❖ Aperçu sur les scripts Un script ACL est similaire à une macro dans Excel Un script dans ACL est un enregistrement d’un ensemble d’opérations/tests faits par l’utilisateur sur une ou plusieurs tables. Le but du script est d’éviter l’effort de l’auditeur lorsqu’il va réexécuter ses tests de nouveau sur les nouvelles versions des fichiers déjà audités. Le script va reexécuter les tests sur les nouvelles versions des fichiers. Avant d’exécuter un script, il faut importer la nouvelle version du fichier source, donc il faut mettre à jour la ou les tables comme expliqué ci-dessus. Il y a principalement deux méthodes pour créer/enregistrer un script : La méthode assistée (l’utilisateur clique sur les boutons des commandes et ACL écrit le script) On fait clic droite sur le nom du projet ACL puis nouveau puis script Le script (vide) est créé on lui donne un nom Page 119 sur 123 Puis on clique sur la « lettre avec flèche verte » pour démarrer l’enregistrement des tests A la fin on clique sur « la lettre avec flèche rouge » pour finir l’enregistrement des tests On clique sur la disquette en Haut à gauche pour enregistrer le script La méthode de la trace des commandes (à partir de l’historique des opérations/tests enregistrés, l’utilisateur sélectionne certaines opérations et les copie dans un script) Page 120 sur 123 Fait clic droite puis enregistrer les éléments sélectionnés puis script Puis on revient à la vue globale On peut renommer le script Page 121 sur 123 Quelque soit la méthode de création du script, Le contenu du script sera comme suit Après avoir mis à jour les tables source des données on clique sur le script puis sur exécuter NB : Pour exécuter le script sur le ou les tables mises à jour, on peut soit garder l’ancien travail (tables crées) mais on doit modifier le contenu du script en modifiant les noms des nouvelles tables (exemple table achat devient table achat v2) soit supprimer l’ancien travail (toutes les tables outputs existantes) on garde que les tables sources de données qui ont été mises à jour NB : l’exécution du script entraine aussi la création de la trace. Page 122 sur 123 Chapitre 11 : quelques références (lecture facultative) ▪ ACL_guide_de_démarrage https://drive.google.com/file/d/1pZZVusmAZVWYQL6ihwIDnuc1tlktFli4/view?usp=sharing ▪ ACL_guide_d_accès_aux_données https://drive.google.com/file/d/1m_DacJm_FGUSp66C7GMTa9Z3Rg3thEYN/view?usp=sharin g ▪ ACL_en_pratique https://drive.google.com/file/d/1CN4KOBJK1Xbyjv8fDEc3FhGVrrHut2sd/view?usp=sharing ▪ documentation_en_ligne_acl https://help.highbond.com/helpdocs/analytics/13/user-guide/en- us/Content/using_acl_analytics.htm Page 123 sur 123