L’outil le plus puissant du tableur : le tableau croisé dynamique (2020)

 (qz.com)
3 points par GN⁺ 2023-10-10 | 1 commentaires | Partager sur WhatsApp
  • Le tableau croisé dynamique est une fonctionnalité clé qui transforme le tableur en outil d’analyse en permettant de résumer rapidement de grands volumes de données, sans programmation ni mathématiques avancées
  • Chez Lotus, Pito Salas a imaginé un outil basé sur des clics face à l’inconfort des utilisateurs contraints d’écrire des formules complexes pour produire des statistiques récapitulatives par catégorie et des tableaux croisés
  • Steve Jobs, à la recherche d’un killer software capable de créer une demande pour les ordinateurs NeXT, a perçu le potentiel de cette fonctionnalité, ce qui a conduit au développement de Lotus Improv
  • Les flexible views de l’équipe Lotus ont ensuite été intégrées à Lotus 1-2-3 et à Excel, où l’appellation pivot table s’est imposée
  • Aujourd’hui, les tableaux croisés dynamiques sont largement utilisés dans Excel et Google Sheets, et permettent de condenser des centaines de milliers de lignes de données en un rapport de synthèse d’une page en quelques clics

Un outil pour résumer les données sans formules

  • Le tableau croisé dynamique est une fonctionnalité de tableur qui permet aux utilisateurs ordinaires d’analyser rapidement de grands jeux de données
  • Sans compétences en programmation ni connaissances mathématiques poussées, l’utilisateur peut résumer ses données en pointant et en cliquant avec la souris
  • Le cofondateur d’Apple Steve Jobs apparaît lui aussi comme quelqu’un qui a très vite compris la valeur de cette fonctionnalité

Une histoire de développement qui mène à NeXT et Lotus Improv

  • En 1985, Jobs, après avoir échoué à battre IBM sur le marché des ordinateurs professionnels, a été écarté de la présidence du conseil d’administration d’Apple, puis a aussitôt fondé NeXT pour tenter une nouvelle fois de défier IBM
  • En préparant le lancement des ordinateurs NeXT prévu pour 1988, Jobs cherchait un killer software capable de créer une demande pour le produit
    • Jobs estimait que le grand succès de l’Apple II, lancé en 1979, devait beaucoup au premier tableur largement adopté, VisiCalc
  • C’est au cours de ses échanges avec Lotus, créateur du populaire tableur pour PC IBM Lotus 1-2-3, que Jobs a découvert une forme initiale du tableau croisé dynamique
  • À l’époque, Pito Salas, chez Lotus R&D, observait la manière dont les gens utilisaient les tableurs pour calculer des statistiques récapitulatives par catégorie, autrement dit des tableaux croisés
    • Une entreprise de vente de vélos peut par exemple vouloir consulter les quantités vendues par mois ou le chiffre d’affaires par pays
    • À l’époque, il fallait écrire soi-même des formules complexes, ce qui était fastidieux et propice aux erreurs
  • Salas en a conclu qu’il fallait un logiciel permettant d’obtenir ces statistiques récapitulatives par clic, plutôt que par saisie de formules
  • L’équipe Lotus a appelé cet outil flexible views ; aujourd’hui, les fonctionnalités similaires de Microsoft Excel et Google Sheets sont appelées tableaux croisés dynamiques

La réaction de Jobs et la mise sur le marché

  • Lorsque l’équipe Lotus a montré le prototype initial à Jobs, celui-ci l’a considéré, selon Salas, comme « la chose la plus cool »
  • Jobs a convaincu Lotus de développer le logiciel de tableau croisé dynamique en exclusivité pour les ordinateurs NeXT
  • Le résultat a été Lotus Improv
    • Les ordinateurs NeXT ont été un échec commercial, mais Lotus Improv a laissé une forte empreinte
    • Les éléments de flexible views d’Improv ont ensuite été intégrés à Lotus 1-2-3 et à Excel
    • Excel est en fait le premier produit à avoir utilisé le terme pivot table
  • Bill Jelen, évangéliste Excel et coauteur de Pivot Table Data Crunching, considère Salas comme « le père du tableau croisé dynamique »
  • Salas considère sa contribution au tableau croisé dynamique comme l’une des réalisations les plus satisfaisantes de sa vie, tout en estimant qu’elle repose sur les bases posées par beaucoup d’autres

Utilisation dans Excel et Google Sheets

  • Le tableau croisé dynamique est aujourd’hui l’une des fonctions les plus importantes et les plus utilisées dans la boîte à outils des utilisateurs avancés de tableurs
  • Selon Jelen, le tableau croisé dynamique permet, en 4 à 6 clics seulement, de produire un rapport de synthèse d’une page à partir de centaines de milliers de lignes de données, et constitue le moyen le plus rapide d’obtenir des réponses dans de grands jeux de données
  • Il est difficile de connaître le nombre exact d’utilisateurs quotidiens d’Excel, mais le total mondial des utilisateurs d’Excel se compte en centaines de millions
  • Le tableau croisé dynamique figure généralement tout en haut, ou tout près du sommet, des listes des fonctionnalités les plus utiles d’Excel
  • Les analystes de données utilisent les tableaux croisés dynamiques à de nombreuses fins, notamment en santé publique, pour la croissance économique ou pour l’analyse de l’efficacité publicitaire
  • Justine Shakespeare, senior program manager de l’ONG de défense des droits du travail Verité, utilise les tableaux croisés dynamiques pour analyser des données d’enquêtes issues d’entretiens avec des travailleurs migrants dans les chaînes d’approvisionnement mondiales

Exemples concrets et ressources d’apprentissage

  • Une journaliste de Quartz avait besoin d’un récapitulatif du montant total des transferts du premier semestre pour chaque année dans des données mensuelles sur les envois de fonds des États-Unis vers le Mexique depuis 1995
  • Les chiffres nécessaires pouvaient être obtenus en insérant un tableau croisé dynamique, en sélectionnant les données voulues et en filtrant les mois à exclure
  • Pour apprendre davantage à utiliser les tableaux croisés dynamiques, on peut regarder la vidéo de Cody Baldwin, « Excel ninja »
  • Les utilisateurs de Google Sheets peuvent consulter l’introduction aux tableaux croisés dynamiques par l’expert Sheets Ben Collins

À lire aussi

1 commentaires

 
GN⁺ 2023-10-10
Commentaires sur Hacker News

  • Je l’ai appris ce week-end : pour regrouper des lignes/cellules selon la valeur d’une cellule, on peut utiliser la fonction FILTER
    =FILTER(Stories!B2:D13,Stories!F2:F13=A2)
    Le premier argument, Stories!B2:D13, est la plage de cellules contenant les stories, et le second, Stories!F2:F13=A2, est la colonne qui compare chaque cellule à la valeur de A2. Les lignes correspondantes sont copiées à l’endroit où se trouve la formule =FILTER
    Je l’utilise pour organiser automatiquement une liste de stories par sprint, et c’est utile pour des tâches comme la planification d’incréments de programme
    Une autre formule Excel utile que j’ai apprise récemment est =IF(NOT(ISBLANK(A2)),HYPERLINK("https://jira-instance.atlassian.net/browse/…),"")
    Si A2 n’est pas vide, elle ajoute cette valeur à la fin de l’URL et affiche comme lien le texte PROJECT- suivi de la valeur de A2. J’aurais pu faire quelque chose de plus élégant avec Emacs et org-mode, mais comme il fallait le partager avec les personnes métier, Excel était le bon choix

    • Ce genre d’exemple est vraiment utile. La fonction QUERY de Google Sheets m’a aussi beaucoup aidé, tout comme IMPORTRANGE
      Ça aide à documenter ce qui a été fait pour obtenir le nombre de lignes
    • Je me demande ce qu’est une “story” dans un tableur

  • Les tableaux croisés dynamiques basés sur des cubes SSAS sont peut-être le meilleur outil d’analyse en self-service que j’aie vu jusqu’ici. Par « meilleur », j’entends selon le critère de l’usage réel par les utilisateurs
    La capacité à les faire utiliser immédiatement dans un environnement que les utilisateurs métier ont déjà est énorme pour l’adoption réelle, surtout par rapport à des outils comme Looker. Looker est aussi catastrophique pour d’autres raisons
    En plus, dans 9 cas sur 10, les gens veulent ajouter de légères transformations ou des calculs aux données, donc rien ne vaut quelque chose qui se trouve déjà dans Excel
    Le regret, c’est que la prise en charge d’OSX est quasiment inexistante et que l’écriture de MDX est vraiment pénible

    • D’accord, et je fais ça avec Power BI. On importe les données dans un rapport Power BI, on crée un modèle de données avec des mesures calculées en DAX, puis on le publie sur le service en ligne ; les utilisateurs peuvent alors cliquer sur “Analyze in Excel” pour télécharger un classeur Excel contenant un tableau croisé dynamique connecté à ce modèle de données
      Je fournis cela aux PM du produit dont je m’occupe, et ils peuvent répondre à beaucoup de questions avec de simples tableaux croisés, sans écrire de SQL séparé
    • Lors de mon premier stage, une entreprise utilisait la combinaison cube OLAP + tableau croisé dynamique, et ça m’avait vraiment impressionné
      J’y repense assez souvent quand on parle de bons outils d’analyse dans le domaine de la donnée. Même des outils de BI bien évolués comme metabase ne gèrent pas les dimensions aussi bien qu’un tableau croisé dynamique
    • Au milieu des années 2000, notre killer app était une application d’exemple du magazine MSDN. Elle intégrait Excel dans une simple page web et permettait de manipuler un cube SSAS avec des tableaux croisés dynamiques
      Nous avions fait un peu de développement web pour la gestion des droits et l’enregistrement des vues, mais le temps de développement total a probablement été inférieur à une semaine
    • Je me demande si tu as essayé Easy Data Transform. C’est un outil ETL léger qui effectue des transformations de données de type pivot sur Excel, CSV et plusieurs formats de fichiers, et qui tourne nativement sous Windows et Mac
    • Il me semble que Looker permettait aussi de créer des tableaux croisés dynamiques dans l’interface Explore
      Dans Looker Explore, on peut aussi télécharger en CSV ou en Excel ; je me demande ce qui manquait à ce niveau

  • Les tableaux croisés dynamiques sont en fait une approximation assez pauvre d’un vrai tableur multidimensionnel, comme l’était par exemple Lotus Improv : https://instadeq.com/blog/posts/no-code-history-lotus-improv...

    • L’un des « crimes » de Microsoft Office, pour exagérer un peu, est d’avoir laissé très peu d’espace pour respirer, dans le domaine de la productivité bureautique hors graphisme, à tout ce qui n’était pas Office ou une imitation presque directe
      Le modèle du tableur en est un bon exemple. Excel est peut-être le meilleur élément de Microsoft Office, mais le résultat, c’est que si Word ne suffit pas vraiment pour faire de la publication assistée par ordinateur, on doit généralement passer à InDesign, ce qui est souvent excessif quand on n’est pas un professionnel de l’édition
    • Je ne savais pas qu’Improv avait existé. Il existe des outils proches de la vision d’Improv
      J’utilise Anaplan tous les jours au travail, et une version SaaS moderne basée sur le cloud d’Improv ressemblerait exactement à ça. La raison pour laquelle c’est devenu une entreprise valant des milliards de dollars semble être qu’elle n’a pas attaqué frontalement le marché des tableurs, mais a choisi une approche qui s’intègre bien avec eux
      La conclusion de l’article sur Improv est que l’erreur stratégique majeure a été de vouloir vendre Improv au marché existant des tableurs. S’ils l’avaient vendu à un segment où un modèle plus structuré était une « fonctionnalité » et non un « bug », Lotus aurait gagné du temps pour apprendre, améliorer et affiner le modèle afin de satisfaire ensuite un marché plus large ; Anaplan semble ne pas avoir commis cette erreur. Ils ont créé une niche dans l’EPM, c’est-à-dire le marché de la gestion de la performance d’entreprise
    • En soi, c’est aussi assez proche du groupby du pauvre
      À un moment, il faut reconnaître qu’on a mal choisi l’abstraction. Ça dégage une forte impression de Zalgo
    • Je n’ai pas souvent utilisé de tableurs depuis le milieu des années 1990. L’une des raisons est que j’avais misé sur le potentiel d’Improv, puis j’ai été déçu en comprenant qu’il n’avait pas d’avenir
      À l’époque, je ne savais pas que les tableaux croisés dynamiques constituaient une autre approche du même concept
      Une autre raison pour laquelle j’ai abandonné les tableurs était la performance. Je ne me souviens pas à quel point Improv était bon ou mauvais sur ce plan, mais les jeux de données que je manipulais alors n’étaient pas vraiment adaptés aux tableurs, donc je n’aurais probablement pas continué à les utiliser
    • Je me souviens être allé, quand j’étais jeune journaliste, au lancement britannique de Lotus Improv. Son côté sophistiqué et professionnel par rapport aux autres produits ressortait assez nettement, et avec le recul, j’imagine que c’était dû à l’influence de Jobs
      Malgré tout, ils semblaient avoir beaucoup de mal à expliquer ce qu’était un tableau croisé dynamique, et en quoi cette seule fonctionnalité pouvait constituer une raison suffisante pour passer à une nouvelle application et à une nouvelle plateforme matérielle

  • Dans Google Ads, on avait créé tout un tas de tableaux de bord qui en jetaient, mais les recherches utilisateurs montraient assez clairement qu’au final, ce que les gens voulaient, c’était simplement voir leurs données sous forme de tableau croisé dynamique

    • Les tableaux de bord qui en jettent sont trop rigides. On ne peut pas ajouter soi-même des agrégations qui vont au-delà du trivial, ni colorer une valeur précise qui nous préoccupe, ni produire directement un rapport lisible avec du texte explicatif.
      Export vers un tableur + tableau croisé dynamique permet de faire tout cela. Un employé de bureau un minimum à l’aise peut le faire sans passer par une boucle infinie backlog-spécification-sprint-développement-test-respécification-sprint
    • Les utilisateurs essayaient en quelque sorte de dire que ce tableau de bord ne suffisait pas pour répondre aux questions métier que leur supérieur leur posait chaque jour, mais on dirait que ce signal n’a pas été capté.
      Si ça ne sert pas à faire le travail, personne ne se soucie que ce soit joli. En plus, le côté « joli » est très subjectif. Là où l’équipe pensait ajouter de la valeur, elle perdait en réalité du temps
    • J’ai travaillé à créer des tableaux de bord qui en jetaient, et un jour les utilisateurs ont demandé qu’on ajoute « une colonne où l’on peut mettre des fonctions comme SUM ».
      Peu après, j’ai démissionné et je suis passé dans l’équipe Google Sheets

  • Microsoft a directement volé les tableaux croisés dynamiques à mon ancien employeur, Brio Technology, et à son produit DataPivot : https://en.wikipedia.org/wiki/Brio_Technology
    D’après le CEO, Microsoft l’a fait venir à Redmond pour lui faire une offre d’achat insultante par sa faiblesse, et l’a menacé, s’il refusait, de créer son propre produit pour couler Brio. Il a refusé, puis MS a ajouté les tableaux croisés dynamiques à Excel.
    L’article mentionne aussi Lotus, qui semble avoir créé un produit similaire à peu près à la même époque. Les fondateurs de Brio avaient aussi été impliqués dans une société appelée Metaphor, et certaines idées ont peut-être été développées là-bas.
    Le produit de Brio était fourni avec une base de données d’exemple contenant le contenu de la cave à vin du CEO, et ces mêmes données apparaissaient dans les premières boîtes de Microsoft Office

    • Quelque chose de similaire est arrivé à Stac Electronics : https://en.wikipedia.org/wiki/Stac_Electronics
      Sauf que Stac avait des brevets, a pris des avocats et a poursuivi Microsoft pour violation de brevet. Microsoft ne s’est pas laissé faire, mais après que le tribunal a jugé que les deux parties avaient besoin d’une licence pour commercialiser leurs produits, Stac est allé voir les grands clients OEM pour leur proposer une licence.
      C’était soit prendre une licence de brevet, soit ne pas livrer le produit, et la pression des OEM sur Microsoft a abouti à un accord pas si défavorable pour Stac. Je vois cela comme l’un des meilleurs exemples où les brevets logiciels peuvent aider la concurrence
    • Je me demande ce que MS a exactement volé. Le concept de tableau croisé dynamique lui-même, comme la synthèse de données par catégories de lignes, existe depuis très longtemps, donc j’imagine que ce n’est pas de cela qu’il s’agit
    • Je me demande ce qui aurait été volé. La technologie sous-jacente, ou le nom pivot table ?
      Le concept consistant à agréger des colonnes de données catégorisées n’a pas été inventé par Brio Technology
    • Ça me rappelle aussi l’épisode où Microsoft a essayé de concéder CPM sous licence à IBM alors qu’il ne le possédait pas, puis a demandé à quelqu’un d’en créer un clone avant de le concéder à IBM
    • Lotus Improv est en fait sorti avant le produit de Brio. Je me souviens très bien de la sortie d’Improv : à l’époque, c’était une avancée vraiment passionnante.
      Mais à mon avis, son gros problème était qu’il ne faisait pas partie de Lotus 123. Du coup, quand Excel a intégré les tableaux croisés dynamiques directement dans le tableur, c’est devenu beaucoup plus utile. En tout cas pour moi

  • Je suis ingénieur et à l’aise avec SQL, mais la capacité à travailler rapidement dans Excel et à créer des tableaux croisés dynamiques à la volée a énormément aidé ma carrière. Surtout à mesure que je me suis orienté davantage vers le produit et la gestion business.
    Les tableaux croisés dynamiques permettent de voir très vite des décompositions selon plusieurs dimensions, de faire varier l’analyse dans tous les sens et de prendre des décisions rapidement. En dehors des outils existants pour des domaines bien définis, il n’y a pas grand-chose qui rivalise

    • J’ai plutôt le sentiment inverse. Je charge rapidement les données dans BigQuery et je commence tout de suite à les découper.
      Je peux lancer des requêtes SQL beaucoup plus vite que cliquer dans Excel, et la reproductibilité est meilleure, parce qu’il est plus facile de copier-coller une requête depuis l’historique que de refaire toute la mise en forme dans une feuille Excel plus tard
    • Je n’ai jamais vraiment exploré les tableaux croisés dynamiques en profondeur, parce que tout au long de ma carrière les données se trouvaient généralement dans des bases de données relationnelles.
      Mais l’effort nécessaire pour déplacer des données d’un tableur vers une base de données est souvent excessif, donc même pour un ingénieur, utiliser des tableaux croisés dynamiques n’est pas forcément un mauvais choix

  • J’utilise ça tout le temps dans Pandas. Je viens d’ailleurs de donner un cours à ce sujet : une fois la syntaxe comprise, c’est très utile.
    Des outils intelligents comme DuckDB ont reconnu cette utilité, ainsi que la difficulté pénible de faire cela en SQL standard, et ont ajouté PIVOT à leur implémentation. C’est extrêmement utile

    • Je n’aime pas particulièrement travailler en R, mais les fonctions de pivot/restructuration de tidyr sont difficiles à battre. Elles sont bien plus faciles à utiliser que les fonctions similaires en Python
    • J’aime bien l’implémentation de dplyr en R. Il y a pivot_longer et pivot_wider, et j’aimerais que Postgres ait quelque chose comme ça
    • Je me souviens qu’il existait autrefois un plugin pour interroger des tableaux croisés dynamiques dans Postgres, mais il était tellement pénible à utiliser qu’une approche en deux étapes était plus simple, et peut-être même plus performante : interroger d’abord les noms de colonnes avec du code de connexion Python/SQLAlchemy, puis générer une seconde requête à partir de ces noms.
      J’espère que la situation s’est améliorée, parce que les tableaux croisés dynamiques sont vraiment utiles
    • J’aimais bien l’outil de requête visuel de Microsoft Access. C’était très intuitif, même si peut-être un peu trop abstrait pour le grand public.
      Il générait aussi du SQL, et c’est grâce à cela que j’ai appris un peu de SQL
    • Oracle a aussi PIVOT. La dernière fois que je l’ai utilisé, il fallait spécifier les colonnes manuellement

  • Je me demande donc ce qu’est exactement un tableau croisé dynamique. Je n’en ai jamais utilisé ni vu, et la vidéo intégrée ne s’est pas chargée dans mon navigateur
    Ce que j’ai appris dans cet article, c’est que c’est une façon simple d’afficher des données dans une feuille de calcul, et que cela a peut-être été inventé chez Lotus
    Mais je n’ai aucune idée de ce que ça fait réellement ni de la façon dont on s’en sert

    • C’est une interface en glisser-déposer qui regroupe les éléments d’un tableau par catégories. Les utilisateurs métier ont généralement du mal à la comprendre, d’où les nombreuses courtes vidéos sur YouTube. Il y a un exemple ici : https://www.youtube.com/watch?v=qu-AK0Hv0b4
      Personnellement, je trouve que c’est paradoxalement à la fois très utile et complètement bancal

  • Cela concerne probablement davantage certaines implémentations que les tableaux croisés dynamiques en général, mais dès qu’on essaie de faire quelque chose d’un peu plus avancé, on a souvent l’impression qu’ils manquent de capacités
    Pour citer quelques exemples, dans Excel comme dans Google Sheets, la manière dont les « valeurs » sont générées est très limitée, mais de façons différentes
    Le fonctionnement du filtrage/tri dans les tableaux croisés dynamiques n’est pas non plus le plus intuitif ni le plus flexible
    Il est très difficile de contrôler les éléments d’UI comme les en-têtes ou les styles, et il arrive souvent qu’après avoir créé un tableau croisé dynamique, on le copie ailleurs pour corriger manuellement tout un tas de choses. Dès lors, il ne peut plus se mettre à jour dynamiquement, ce qui en dilue l’intérêt
    Je ne suis pas spécialiste des tableurs, donc il est possible que quelque chose m’échappe

    • Je suis assez proche d’un spécialiste des tableurs, et je classerais les tableaux croisés dynamiques comme une visualisation de données temporaire, rapide et brouillonne. Je ne les utilise pas pour des présentations ni quand le style compte
      Je m’en sers surtout pour repérer rapidement des valeurs inattendues dans les colonnes de données source. Cela dit, avec l’arrivée des formules à débordement comme =unique(), je les utilise de moins en moins même pour cet usage
      Quand les gens attendent davantage des tableaux croisés dynamiques, j’ai l’impression qu’ils demandent en fait plutôt Power Query. La fonctionnalité existe déjà, mais beaucoup de gens en ont peur. Peut-être pas sur HN, mais c’est le cas du grand public
    • D’accord. Les implémentations actuelles sont complètement fragiles et très loin du niveau de fonctionnalité qu’un utilisateur expérimenté ou power user pourrait attendre
      Cela m’agace tellement que j’ai décidé d’écrire mon propre moteur de tableur, et c’est l’un des points douloureux que je veux corriger
    • Vous pouvez essayer ce type de tableau croisé dynamique en ligne : https://www.seektable.com
      Il permet de modifier l’ordre des lignes/colonnes simplement en cliquant sur les en-têtes, et d’appliquer des filtres en indiquant, via une saisie simple, les éléments à conserver ou à exclure

  • Les tableaux croisés dynamiques sont clairement puissants, mais leur nom est mal choisi, et il faut toujours un certain temps pour vraiment maîtriser l’UI dans Excel
    J’aime bien la fonction Categories de Numbers sur MacOS, qui est beaucoup plus facile à utiliser, mais quand on atteint ses limites, ses contraintes deviennent assez pénibles
    Ces temps-ci, pour les traitements complexes, le plus simple est souvent d’exporter la feuille de calcul en CSV puis de lancer des requêtes SQL avec CSVQ