L’analyse de données fait partie du quotidien des professionnels de la comptabilité et de la finance. Excel est généralement l’outil d’analyse plébiscité pour ce genre de tâches mais le langage de requête SQL (acronyme pour Structured Query Language ou langage de requête structuré) est particulièrement adapté pour réaliser les tâches d’extraction et de mise en forme des données. De nombreux SGBDR, tel ACCESS, disposent de ce langage.
La Revue Française de Comptabilité a publié il y a quelques années un article que j’ai écrit sur ce sujet.
Table de données PlanComptable
En synthèse, le langage SQL permet d’effectuer des calculs plus ou moins complexes et d’extraire (SELECT) des données d’une ou plusieurs tables de données (FROM) répondant à des critères (WHERE). Le résultat de ces extractions sera utilement analysé, par exemple, à l’aide d’un tableau croisé dynamique ACCESS ou EXCEL.
Structure type d’une requête SQL :
SELECT champ1, champ2... FROM table WHERE condition1, condition2... ORDER BY champ1, champ2...;
Une requête SQL est constituée de plusieurs clauses dont a minima les clause SELECT et FROM.
Sélection (SELECT) des champs de données à extraire :
Le clause SELECT précise les champs de données à extraire des tables, FROM le nom de la table contenant les données à extraire.
Par exemple, la requête suivante :
SELECT Compte, LibelléCompte FROM PlanComptable;
Liste le numéro de compte (champ Compte) et le libellé des comptes (champ LibelléCompte) du plan comptable (table PlanComptable) :
Pour sélectionner tous les champs, on utilise le joker (*).
SELECT * FROM PlanComptable;
La clause SELECT (ou SELECT ALL) liste tous les enregistrements alors que la clause SELECT DISTINCT ne rendra pas les enregistrements dont le contenu des champs sélectionnés est en doublon. SELECT DISTINCTROW ne rendra pas les enregistrements entièrement en doublon.
La clause SELECT TOP n permet de ne lister que les n premiers enregistrements. SELECT TOP n PERCENT ne ressort que les n pour cent premiers enregistrements.
Création de champs calculés :
La clause SELECT permet également de créer des champs de données calculés. Par exemple, ajouter un champ racine de compte sur trois positions :
SELECT Compte, LibelléCompte, Left(Compte,3) AS Cpte3 FROM PlanComptable;
La fonction Left(chaîne,longueur) extrait d’une chaîne de caractères nommée chaîne un certain nombre (longueur) de caractères les plus à gauche ; ainsi Left(‘512000’,3) renvoie 512. L’instruction AS affecte un nom au champ de données calculé (chaque champ de données devant comporter un nom). La fonction Right réalise la même chose mais par la droite.
Résultat de la requête :
Autres fonctions utiles :
- Len(champ) : donne le nombre de caractères compris dans le champs,
- Val(champ) : convertit une chaîne alphanumérique en un résultat numérique intégrable dans une formule de calcul,
- Abs(champ) : ôte le signe d’un nombre, ainsi Abs(-1) renvoie 1,
- Year(champ au format date), Month(champ au format date) : renvoie l’année (2017), le mois (07) de la date 24/07/2017,
- Weekday(date,1) : donne le numéro du jour de la semaine d’une date ; 1 étant le dimanche. Cette fonction existe également sous EXCEL sous le nom « JourSem », elle fonctionne de la même manière,
- Hour(date) : fournit l’heure comprise dans un numéro de série donné (date).
Il est également possible de réaliser des calculs plus ou moins complexes mêlant fonctions, opérateurs arithmétiques (+, -, *, /), opérateurs de comparaison (<, >, =) et opérateurs booléens (AND, OR) ; exemple de calcul d’un solde comptable sur une balance générale :
SELECT Compte, LibelléCompte, Débit, Crédit, Débit-Crédit AS Solde FROM Balance;
Pour détaxer un montant TTC (sur la base d’un taux de TVA à 20 %) :
TTC/1.20 AS HT.
L’ordre d’exécution des calculs suit les règles de priorité généralement admises en mathématique et en informatique ; l’utilisation des parenthèses permet de changer l’ordre d’exécution.
Champ calculé conditionnel :
L’instruction iif(condition,résultat1,résultat2) renvoie résultat1 ou résultat2 en fonction de la réalisation de la condition. L’instruction iif correspond au test conditionnel IF… THEN… ELSE du langage BASIC.
Par exemple, pour calculer une remise de fin d’année de 1 % pour chaque client réalisant un chiffre d’affaires dépassant 500 K€ :
iif(CA>500000,0.01,0)*CA AS RFA.
Autres exemples :
- pour éviter une division par zéro, la formule iif([champ1]=0,0,[champ2]/[champ1]) renvoie le résultat 0 (zéro) quand le champ [champ1] est nul, dans le cas contraire la division de [champ2] par [champ1] est effectuée.
- la fonction IIF est également utilisée lorsqu’un champ numérique est vide. En effet, si ce champ est intégré dans une formule de calcul, le calcul ne sera pas exécuté puisque le contenu du champ n’est pas numérique. La formule iif([champ] is null,0,[champ]) remplace les champs vides par zéro.
Conditions (WHERE) :
SELECT Compte, LibelléCompte, Left(Compte,3) AS Cpte3 FROM PlanComptable WHERE Compte="512100000" OR Compte="512200000";
La clause WHERE conditionne l’extraction de données au respect de critères. Dans cet exemple, ne seront listés que les comptes égaux (=) à 512100000 ou (OR) à 512200000 :
L’opérateur OR constate la réalisation de la condition si l’un des deux critères est rempli. L’opérateur AND constate la réalisation de la condition si les deux critères sont remplis. Les opérateurs de comparaison <, >, = sont également utilisables en combinaison ou non avec les opérateurs AND et OR :
WHERE (CA>=1000 AND CA<5000) OR (CA>=50000 AND CA <150000)
Les fonctions Left, Right… et les parenthèses évoquées pour la clause SELECT peuvent également être appelées pour définir les critères dans la clause WHERE.
Tri des résultats (ORDER BY) :
Pour trier les résultats de la requête, il suffit de faire suivre la clause ORDER BY par les champs à classer dans l’ordre ascendant (du plus petit au plus grand) :
SELECT Compte, LibelléCompte, Left(Compte,3) AS Cpte3 FROM PlanComptable WHERE Compte="512100000" OR Compte="512200000" ORDER BY Compte;
Pour trier dans l’ordre descendant : ORDER BY Compte DESC.
Le résultat d’une requête est interrogeable par une autre requête ou au sein de la même requête (dans le cadre d’une sous-requête).
Exemples pratiques :
- Audit & poste clients : détection du dépassement de plafond de crédit / assurance-client (introduction aux jointures, à iif et aux opérateurs de comparaison),
- Echantillonnage de données : sélection aléatoire de données (introduction aux instructions SELECT TOP 10, RND),
- Recherche d’anomalies et détermination de points de contrôle à partir d’une balance générale et des états financiers (introduction aux fonctions Left, Right et Len, aux sous-requêtes, requêtes UNION et requêtes regroupement GROUP BY),
- Accès au système informatique : détection de la fraude ou des usurpations d’identité (introduction aux fonctions Weekday et Hour).
Approfondir le sujet : Maîtriser le langage de requête SQL / les opérateurs / Analyse de données
Derniers articles parBenoît RIVIERE (voir tous)
- Excel : collecter des données juridiques sur les entreprises avec l’API SIRENE – 1ère partie : base SIREN (entreprises) - lundi 15 avril 2024
- Obtenir les données financières de sociétés au format Excel - mardi 2 avril 2024
- Robotic Process Automation (RPA) et Python : piloter des applications web - dimanche 24 mars 2024
- Initiation à la programmation en Basic : nouveau compilateur FreeBASIC - lundi 18 mars 2024
- Retrogaming : (re)jouer aux jeux vidéo d’antan - lundi 11 mars 2024
Pingback: SQL : les requêtes UNION – Audit & Systèmes d'Information
Pingback: Analyse de données et automatisation avec Excel et Access (9ème volet) - Audit & Systèmes d'Information