Aller au contenu principal
x
Excel

Mes principaux commandements sur Excel

 

Pour un meilleur confort de lecture, nous vous invitons à télécharger ce post (au format pdf).

Un récapitulatif des abréviations utilisées dans ce document se trouve en dernière page. Elle est complétée des références utilisées pour réaliser le présent document et de liens complémentaires.

 

L’actualité semble se focaliser ces derniers mois sur le phénomène de la dénommée « intelligente artificielle » : bientôt, nous promet-on, à partir de centaines de millions de données collectées, nous pourrons avoir des éléments d’analyse, prédictifs et d’aide à la décision.

Le secteur du contrôle de gestion et de l’aide au pilotage, quel que soit le secteur d’activité, n’est pas en reste, et les outils décisionnels ont fait de grands progrès dans leur ergonomie et leur mise en œuvre.
 
Pourtant, ces projets et leurs possibilités techniques – quand elles sont disponibles, implantées et en bon état de marche (le vécu….) – sont loin de réduire le temps passé sur les tableurs. Un récent article paru dans le Monde [1] citant une étude du cabinet IDC informe que les « contrôleurs [de gestion] disent passer 80 % de leur temps dans la « production » de chiffres, et bien peu dans leur analyse. […] Ils passent en moyenne quatre jours par semaine derrière leur tableur Excel, dont une journée rien qu’en manipulations très routinières de mise en forme et de préparation des données ». L’utilisation et le recours aux tableurs, bien qu’en évolution, ne semblent pas prêts de changer radicalement.
 
Qu’en est-il du domaine hospitalier ? Sans avoir de données détaillées à ce sujet, il n’est pas excessif de dire que beaucoup d’outils de suivi, de pilotage et de tableaux de bord se font, pour tout ou partie, sur Excel. Il faut dire que les systèmes d’information métier (dossier patient, gestion du personnel, dispensation et gestion pharmaceutique, planification opératoire, gestion des examens et résultats d’imagerie ou de laboratoire etc…) sont nombreux, très différents dans leurs objectifs, leur contenu et leur nature, et pas toujours satisfaisants, surtout si l’on tend l’oreille aux utilisateurs…
 
Quelques éléments nous renseignent cependant : dans la cinquième édition de l’Atlas des SIH (2017) [1], il est indiqué que les établissements de santé ont consacré, en moyenne en 2015, 1,7 % de leurs charges d’exploitation au système d’information hospitalier. 5 100 ETP sont dédiés aux SIH. Présentés ainsi, les ressources paraissent très importantes. Rappelons que le secteur représentent pas moins de 90 milliards de dépenses et que pour la partie dite « D5 – Pilotage médico-économique », seuls 48 % des établissements sont au niveau des prérequis attendus dans ce domaine. Pourquoi évoquer cette thématique ? Car ce domaine vise à « s'assurer que l'établissement produit un tableau de bord par pôle ou entité corrélant des indicateurs sur l'activité, la performance financière, les ressources humaines et la qualité et de son utilisation effective. » Pour évaluer l’atteinte de ce domaine, trois indicateurs nationaux sont suivis:
- D5.1 Production du tableau de bord de pilotage médico-économique fondée sur un SI alimenté des données de production
- D5.2 Tableau de bord de pilotage infra-annuel par pôle ou entité corrélant des indicateurs sur l’activité, la performance financière, les ressources humaines et la qualité, produit dans un délai de trois mois post période
- D5.3 Utilisation du tableau de bord de pilotage dans le cadre du dialogue de gestion
 
Voyant le verre à moitié vide, ces résultats suggèrent que les outils de pilotage ne sont pas encore entièrement produits et/ou informatisés dans la majorité des établissements de santé français. Heureusement, les choses avancent, un peu bousculées par les fortes contraintes économiques et organisationnelles auxquelles les établissements sont confrontés. Vu les marches restant à gravir, le recours à Excel n’est pas prêt de se tarir. Constatons que les agences et les tutelles elles-mêmes construisent des fichiers à utiliser par les établissements dans le cadre de reportings nationaux, comme les Retraitements Comptables annuels…
 
 
Utilisant quotidiennement, pour ne pas dire intensément et parfois même passionnément les tableurs, précédemment en établissements, puis pour nos clients, nos missions, notre propre entreprise, … les années de pratique, et de déboires occasionnels, m’ont permis de me forger un cadre d’utilisation d’Excel. Et parce que le sujet intéresse assurément bien des contrôleurs de gestion, assistants de pôles et autres techniciens et statisticiens de l’information, il nous a paru ludique et pédagogique de faire part de notre « expérience » sous forme de commandements, frappés du sceau du pragmatisme, de la ténacité et du bon sens. Ajoutons qu’ils ne prétendent pas à l’exhaustivité (il y aurait tant à dire…), mais qu’ils constituent une sorte d’aide-mémoire pour toute création de fichiers tableurs.
 

#1  « Toujours  ‘une solution sur Excel, il doit y avoir’  tu te diras »

Après plus de trente ans d’existence (la première commercialisation date de 1985), de nombreuses versions progressives et plus d’un milliard de licences commercialisées, le tableur de Microsoft est une application particulièrement étudiée et qui a fait l’objet d’évolutions techniques profondes. Cela signifie que pour des utilisateurs débutants, confirmés, voire experts, il est fort probable que la majorité de leurs besoins de traitements et de calculs de données, tout autant que de gestion de bases d’informations, trouve une solution.
 
J’utilise personnellement Excel depuis 1992, date d’entrée en école de management. Si quelques cours nous furent dispensés, c’est surtout par une pratique régulière et toujours interrogative que j’ai découvert, petit à petit, au fur et à mesure des besoins d’informations tels qu’ils se présentaient, que j’ai pu approfondir ma connaissance de l’application. Si l’on pense bien entendu à la connaissance des différentes formules disponibles (il y en a aujourd’hui plus de 400 !), c’est une sorte d’état d’esprit qui peut se résumer ainsi : quand il m’est demandé de réaliser une étude ou une analyse avec des données, je peux trouver une solution, à tout le moins une approche, pour construire ma réponse avec Excel.
 
Prenons un exemple : il y a quelques années, contrôleur de gestion dans un grand établissement français, j’ai dû travailler sur des durées de séjour en hospitalisation. Ces dernières montraient une palette très étendue, de quelques heures, à plusieurs mois ou années. Bien entendu, médiane, moyenne, médiane, minimum et maximum ont apporté une compréhension de ce phénomène. Afin de clarifier (ce principe est aussi un grand guide pour tout contrôleur de gestion) les résultats et de les rendre encore plus compréhensibles, j’ai imaginé que ces résultats gagneraient à être présentés sous forme de segments de durées : < 1 jour ; [1 – 10 jours] ; [11 – 20] ; [20 – 30] etc…
Après quelques tests et tâtonnements, ma démarche a été la suivante :
  • 1ère étape : créer une colonne dans la feuille intitulée « Segment de durée de séjour »
  • 2ème étape : démarrer une formule, dans laquelle le premier test cherche si la durée de séjour est inférieure ou égale à un jour. Concrètement, il s’agit d’utiliser la fonction « si » :
          =Si(cellule contenant la durée de séjour<1;’ < 1 jour’ ;
  • 3ème étape : déterminer automatiquement les bornes basses et hautes. Pour cela, je me suis remémorée quelques cours de mathématique relatifs à la fonction partie entière. En effet, pour rappel, cette fonction associe à tout réel x l’unique entier relatif n tel que n £ x < n + 1. Elle existe dans Excel sous le nom « ENT ». Pourquoi faire ainsi ? Ce calcul permet, en divisant par 10 la durée de séjour de déterminer la borne basse. Ainsi, une DMS de 11,2 jours, divisée par 10, a un résultat de 1,12, dont la partie entière est 1. Pour déterminer la borne haute, il suffit d’ajouter 1 à la partie entière précédemment identifiée. La formule pour la borne basse peut alors s’écrire ainsi, en mettant bien les parenthèses :  ENT( cellule contenant la durée de séjour / 10)
           Pour la borne haute, on peut saisir ENT( cellule contenant la durée de séjour / 10)+1
  • 4ème étape : Pour finaliser, il suffit de recourir à la très utile fonction CONCATENER, qui permet d’associer des chaînes de caractères dans Excel. Nous y ajouterons donc volontiers quelques croches entre parenthèses ‘[‘ ‘]’, sans oublier les points virgules, pour finaliser notre segment.
 
Au total, la formule se présente ainsi : 
 
=Si(cellule contenant la durée de séjour<1;’< 1 jour’ ; concatener( ‘[‘ ;ENT(cellule contenant la durée de séjour / 10) ; ‘ – ‘ ; ENT(cellule contenant la durée de séjour / 10) +1).
 
 
Voici un exemple ci-contre avec la thématique de l’ancienneté par salarié, construite sur le même schéma :
 
 
Les résultats seront bien construits sous forme de segments de durée. Les tableurs n’excluent pas la mémoire des mathématiques et l’imagination…
 
 
 
 

#2  « Les tableaux croisés dynamiques, tu utiliseras »

S’il fallait résumer ce que sont les tableaux croisés dynamiques, on pourrait écrire qu’ils permettent, de façon quasi-intuitive et très rapide, de générer synthèse, regroupements et calculs, à partir d’une table de données brutes. En plus des fonctionnalités strictes de calculs (moyennes, sommes, comptages etc…), les dernières versions logicielles permettent de réaliser de très bons formats, qui se mettent à jour de façon automatique (quand on les actualise).
L’intérêt des tableaux croisés dynamiques réside dans leurs grandes puissances de traitements (on peut, dans l’absolu, se baser sur une table aussi grande qu’une feuille Excel le permet aujourd’hui, soit plus d’un million de lignes (à considérer comme autant d’ « enregistrements ») et plus de 17 000 colonnes (à voir comme les champs d’une base de données).
 
Dans ma pratique, cela signifie que je procède en deux temps : une première étape va consister à interroger le système d’information (l’application RH ou PMSI par exemple) et à réaliser une extraction avec des données les plus « brutes » possibles (pas de sous-totaux, ni de calculs ou de regroupements). J’exporte ces résultats dans un fichier Excel et conserve dans une feuille dédiée, que je nomme souvent « données importées ».
Je crée ensuite par la suite autant d’onglets que de tableaux croisés, sans jamais intervenir sur la feuille de base. Ce système diminue les risques d’erreurs consécutives à des manipulations. Finalement, ce deuxième commandement pourrait se conclure par un nouveau principe « Comme une base de données ton fichier tu construiras ».
 
 

#3  «  ‘Excel n’est pas un outil de saisie’ tu te répéteras

Accessible, peu cher, connu de tous, très souple, équipant la majorité des postes informatiques, Excel a le défaut de ses qualités : des questions réelles de sécurité se posent, et plus exactement de la capacité à conserver l’intégrité des données et les résultats produits ; une forme d’instabilité est consécutive à des créations et des maintiens de fichiers hors des procédures techniques et rigoureuses des applications conventionnelles ; une absence ou un manque de traçabilité du contenu des fichiers sont fréquents ; des liens et des méthodes de calculs utilisées peuvent être sujets à caution ; une grande dépendance à la personne ayant créé ou maintenant le fichier est souvent problématique ; la très grande facilité à modifier peut impacter la qualité des résultats, sans possiblement s’en rendre compte, sur les contenus et les formules ; etc…
 
C’est pourquoi, loin de mettre en opposition système d’information décisionnel et tableurs, Excel est à considérer comme un outil aidant à faire des évaluations et des mesures, en appui de ce qui peut être fait directement dans les applications d’aide au pilotage. L’absence de réponse technique ne devrait pas amener à utiliser systématiquement les tableurs, mais à entamer une réflexion approfondie sur les besoins en outils de pilotage.
D’un autre côté, la saisie de données peut se révéler chronophage, à très faible valeur et soumise à des risques d’erreurs.
Pour ces motifs, ma pratique s’appuie sur les éléments suivants : on peut utiliser Excel en aval du recueil de données, pour son intérêt autour de la capacité de calculs et de restitutions, mais on évitera d’y recourir pour saisir des informations de quelque nature que ce soit. Cela suppose un dialogue fort et vivant entre les services informatiques et de contrôle de gestion. Ce commandement pourrait avoir pour synonyme « Le moins de manipulations de données, de développements informatiques et de références, tu feras ».
 
 

#4  « Une navigation intra fichier tu créeras »

L’avènement d’internet, voire des outils courants digitaux, a bouleversé la manière dont un individu prend connaissance, lit des documents et organise ses informations. Le phénomène le plus remarquable à ce sujet est celui du « surf », c’est-à-dire de l’habitude désormais acquise et de la capacité d’un individu à naviguer d’une partie de document à l’autre. Les suites bureautiques ne s’y sont pas trompées, proposant déjà depuis plusieurs années ce type de fonctionnalités par la création de liens hypertextes. Ces liens, très faciles à créer, peuvent renvoyer à un autre onglet d’un fichier Excel, à un adresse sur le web, à un répertoire sur un ordinateur ou un serveur.
 
Les fichiers tableurs peuvent s’avérer très complets et divers. 
C’est pourquoi, je crée désormais systématiquement un onglet que je baptise « Feuille de navigation », pour lequel je décoche « quadrillage » dans le menu « affichage ». Ceci donne l’impression d’avoir un écran blanc comme si l’on était sur un site ou une application.
Je crée ensuite des cellules de navigation au libellé explicite : « Données de l’ENC ex-DGF 2015 », « Résultats consolidés par UF », « Détail par activité et par pôle », « Fichier structure utilisé », « Balance comptable 2017 » … Chaque onglet et tableau dispose alors, en évidence, d’un lien qui renvoie systématiquement vers ce menu.
 
 

 

#5  « Plusieurs fonctions incontournables tu maîtriseras »

Il n’est pas utile de connaître toutes les fonctions disponibles sur Excel d’autant que, comme nous l’avons précédemment, elles sont en très grand nombre.
Dans le cadre d’un contrôle de gestion classique et quotidien, trois grands champs de fonctions sont à connaître : celles qui relèvent des mathématiques et statistiques, des bases de données et générales. Pour ma part, voici, entre autres, celles auxquelles j’ai recours quotidiennement, avec leurs fondements :
 
 
Appliquées à des données brutes et dans la perspective de réaliser des tableaux croisés dynamiques, ces quelques fonctions permettront de remettre en forme certaines données, d’en créer de nouvelles et d’obtenir des mesures déjà intéressantes. Une mention spéciale est à faire à deux fonctions incontournables en contrôle de gestion : la fonction « Recherche(v) », qui permet de faire des renvois avec des tableaux ou des données de base (par exemple les types de séjours, les tarifs, les codes postaux et communes etc…) et la fonction « Concatener ».
 
 

#6  « Les touches de raccourci tu utiliseras »

Oubliez votre souris ! Le monde professionnel va vite, et exige – souvent – des réponses rapides. Le contrôle de gestion n’échappe pas à cette tendance.
Une manière de gagner en efficacité réside dans l’utilisation des touches de raccourci : je conseille même en complément d’apprendre par cœur certaines fonctions utilisées régulièrement, et de saisir par le clavier la structure de la fonction, avec les cellules correspondantes.
Cette pratique permet de réduire significativement son temps de traitement, d’autant plus si on recourt systématiquement aux touches de raccourci (qui ont l’avantage d’être les mêmes pour toute la suite bureautique Microsoft). 
Pour ma part, voici celles que j’utilise quotidiennement :
 

 

#7  « Toujours à la lisibilité et au lecteur tu penseras »

Qui n’a pas découvert avec soupir, par mail ou sous format papier, des tableaux Excel remplis de chiffres, épuisants et repoussants au seul premier coup d’œil ? La communication et la compréhension des résultats font partie intégrante des missions du contrôle de gestion. C’est pourquoi, un soin constant et particulier doit être apporté à tous les tableaux produits.
 
Quelques principes permettent de faciliter ce point :
 
Sur le fond :
- Avant de se lancer, toujours penser à l’avenir du fichier et à ses possibles extensions et ramifications. Verrues, coquilles et « usine à gaz » pourront en partie être évitées par ce principe de « précaution » et d’anticipation
- Construire un tableau par thématique, et une thématique maximum par onglet
 
Sur la forme :
- Donner des noms explicites aux onglets
- Ne pas hésiter à donner des noms aux tableaux et en haut de chaque onglet
- Donner des noms explicites aux colonnes et éviter les acronymes et abréviations 
- Utiliser des polices lisibles par tous (ne pas descendre en dessous de 8 au final, en pensant aux éventuelles diminutions lors des impressions)
- Chaque tableau doit tenir sur, au maximum, une page A4 (et non pas A3…)
- Eviter les bordures et les soulignements. Ils alourdissent inutilement les présentations. Préférer les couleurs, en adoptant des principes explicites (rouge pour les dépenses, vert pour les recettes ou les points positifs etc…)
- Dans le même esprit, enlever le quadrillage dans l’affichage. Il ne sert, finalement, qu’à celui ou celle qui a construit le tableau (et encore).
- Soigner chaque tableau et présentation, et avoir un vrai souci de « faire beau » et constant. On peut à ce titre prévoir une charte graphique préalable qui définira les grands choix formels et obligatoires (logo, date, mentions « confidentiel », provisoire » etc…)
 
 

#8  « Le contrôle, le contrôle, le contrôle de tes données et formules systématiquement et plusieurs fois tu feras »

Principe fondamental en contrôle de gestion, et précaution toujours utile et valable sur Excel, les formules et les résultats doivent toujours être vérifiés avant tout envoi ou analyses.
Pour cela, on n’hésitera pas à prévoir des colonnes de test, pour vérifier par exemple, que les sommes en lignes, en colonnes, intra ou inter-fichiers sont bien égales. Tenter de calculer le résultat d’une autre manière, ou avec d’autres données et formules, fait aussi partie de la panoplie possible pour ces contrôles. Outre ces points techniques, la contextualisation reste toujours importante, où l’on pourra ainsi se poser la cohérence des mesures obtenues avec des données de référence.
C’est aussi l’une des raisons qui milite en faveur de l’approche données de base + tableaux croisés dynamiques, car elle permet d’éviter de manipuler trop de données ou de cellules.
 

 

#9  « De la veille et de la recherche sur les tableurs tu feras »

Sortir de sa zone de confort et d’habitudes permet de faire des découvertes. Les tableurs, malgré leur apparente stabilité fonctionnelle, font l’objet de développement parfois conséquents. Par exemple, Excel permet, depuis deux ans environ, de réaliser des cartes géographiques de façon très simple et convaincante, à partir d’un simple fichier, quelle que soit la nature des données qu’il contient. Les modalités de réalisation sont très simples de surcroît.
Pour mener cette veille, je recommanderai à ce titre un excellent blog de professionnels de la Belle Province, très régulièrement tenu et mis en pratique par d’authentiques « Microsoft Most Valuable Profesional » et « Data Platform » québécois. Il est accessible librement ici : http://www.lecfomasque.com/mon-cher-watson/
 
 

Abréviations

ATIH    Agence Technique de l’Information sur l’Hospitalisation
DGOS   Direction Générale de l’Offre de Soins
ETP      Equivalent Temps Plein
PMSI    Programme de Médicalisation des Systèmes d’Information
RH        Ressources Humaines
SIH      Système d’Information Hospitalier
 
 
 
Posté le 30 avril 2018 par Nathalie L'Hostis
 
 
Pilar est spécialisée dans la formation, le conseil et le recrutement en contrôle de gestion et aide au pilotage hospitalier. Nos programmes sont spécialement conçus pour vous accompagner sur ces sujets. Nous vous recommandons particulièrement dans cette optique nos programmes suivants, spécialement conçus pour les organisations innovantes et au bénéfice tant des patients que des établissements :
 
 
 
 
 

[1] Ministère chargé de la Santé, Direction Générale de l’Offre de Soins (DGOS), Agence Technique de l’Information sur l’Hospitalisation (ATIH), « Atlas des SIH 2017 », http://solidarites-sante.gouv.fr/IMG/pdf/dgos_atlas_sih_2017.pdf
 

[1] Michel Baldellon, Anne Vinagre, [Tribune] « Les contrôleurs de gestion ne peuvent lutter contre l’évolution technologique », Le Monde, 7 décembre 2017   http://www.lemonde.fr/idees/article/2017/12/07/les-controleurs-de-gestion-ne-peuvent-lutter-contre-l-evolution-technologique_5226188_3232.html