Feuilles de calcul et leurs capacités
Le principe selon lequel les feuilles de calcul modernes fonctionnent (Microsoft Excel, LibreOffice Calc ou Google Sheets) est apparu à la fin des années 70 - milieu des années 80. Un tableau bidimensionnel de cellules en tant que modèle de données et la possibilité de calculer automatiquement à l'aide de formules sont apparus dans VisiCalc en 1979. Un tableau tridimensionnel de cellules (la possibilité d'utiliser plusieurs feuilles) est apparu pour la première fois en 1985 dans Boeing Calc.
En théorie, les feuilles de calcul sont aussi bonnes que n'importe quel langage de programmation. Il existe une machine de Turing basée sur des formules Excel ( lien ), ce qui signifie que tout algorithme pouvant être implémenté à l'aide d'un ordinateur peut être implémenté dans Excel. La seule question est la commodité et l'efficacité d'une telle mise en œuvre.
En pratique, je suis tombé sur des systèmes très complexes implémentés dans Excel. Par exemple, un modèle financier pour le développement d'un aéroport international avec la capacité d'apporter de nombreux types d'installations (parkings, entrepôts, voies, ...) et recalculer les mètres carrés et les parkings en cash flow (coûts pour les années de construction vs bénéfice pour les années d'exploitation), en tenant compte de différents modèles inflation. Cela peut prendre de plusieurs mois-personnes à plusieurs années-personnes pour «réécrire» un tel «excellent» en Java à l'aide d'une base de données relationnelle. Dans ce cas particulier, le modèle relationnel de la base de données se composait de plus de 50 tables. Plus intéressant encore, cette «réécriture» aurait pu être évitée si les feuilles de calcul permettaient non seulement la création de logiciels, mais rendaient également la maintenance et l'évolutivité possibles.Pour l'utilisateur final (économiste), le système Java est un pas en arrière, car il ne voit plus de résultats intermédiaires et ne peut pas modifier ou compléter le modèle lui-même.
Il s'avère qu'un seul et même problème peut être résolu à la fois par un tableur et par un langage de programmation universel. Cela signifie que nous pouvons comparer les forces et les faiblesses de ces deux outils comme moyen de créer des applications métier. Ici, nous allons essayer de regarder Excel à travers les yeux d'un architecte programmeur et d'appliquer les règles de l'architecture logicielle déjà bien établies dans le développement logiciel classique.
Avantages des feuilles de calcul
- Concept intuitif : chacun de nous à l'école a vu et rempli des pancartes sur des morceaux de papier dans une boîte et joué à la bataille navale. La plupart des personnes qui travaillent avec Excel n'ont jamais reçu de formation particulière (au mieux, un collègue a montré sur quels boutons appuyer en une demi-heure). C'est un gros avantage par rapport aux langages de programmation où "C ++ en 21 jours" semble encore trop optimiste.
- : , , - . breakpoints . . , .
- : , . , UI, .
- : . Notepad, Java . . . , E5 . VLOOKUP . -, .
- : DRY (Don’t repeat yourself — ). , (, /) . . , , , . . .
- Manque d'interactivité de l'interface : les feuilles de calcul ne permettent pas de modifier dynamiquement la façon dont les données sont affichées. Il n'est pas non plus possible de créer des opérations programmées effectuées, par exemple, en appuyant sur un bouton.
Comment améliorer les feuilles de calcul?
Mon nom est Vadim. Je suis CTO chez CubeWeaver et je développe une nouvelle feuille de calcul depuis un certain temps maintenant. Il y a plusieurs années, j'ai déjà écrit ( lien ) à propos d'une première version du système, mais depuis, beaucoup de choses ont changé et cette année le projet est arrivé au stade commercial.
Voici une liste d'innovations dans mon projet qui abordent les inconvénients énumérés ci-dessus tout en essayant de maintenir les avantages des feuilles de calcul:
Modèle de données multidimensionnel
Le modèle de données multidimensionnel est largement utilisé dans les systèmes de Business Intelligence et OLAP pour l'analyse des données. L'essence du modèle est de stocker des données dans les cellules d'un cube multidimensionnel dont les bords sont signés par les en-têtes des objets métier:
L'interface du programme affiche non pas l'intégralité du cube multidimensionnel, mais sa tranche bidimensionnelle correspondant à la combinaison de filtres que nous avons choisie:
Lors de la mise en œuvre d'un tel modèle dans un système de BI relationnel, le schéma flocon de neige est souvent utilisé. Les cubes sont implémentés par des tables de faits et les en-têtes de visage sont stockés dans des tables de dimension.
Sur mon système, les cubes sont appelés feuilles de calcul et les titres sur les bords du cube sont appelés éléments de liste.
Chaque cellule d'une telle feuille de calcul multidimensionnelle a une adresse unique, composée d'étiquettes sur les bords. Par exemple, la valeur 935 dans l'image a l'adresse: Bikes, 2020, Paris.
Chaque élément de la liste a un nom et un identifiant Les références de cellule utilisent des identifiants, et l'adresse ci-dessus dans une formule peut ressembler à ceci (les références sont entre crochets):
[PROD:23, YEAR:2020, CITY:24], où PROD est l'identifiant de la liste "produit" et 23 est l'identifiant de l'article "Bikes".
L'utilisation d'un modèle multidimensionnel peut améliorer considérablement la situation avec le désavantage numéro 1. Premièrement, les en-têtes sont désormais stockés séparément des données numériques. Deuxièmement, l'introduction d'une dimension supplémentaire «métrique» (ou «position du rapport») permet d'adresser les cellules non pas par leur numéro ordinal, mais par sens sémantique, éliminant les erreurs dues à l'ajout ou à la suppression de colonnes ou de lignes.
Bien sûr, il faut dire que cette approche gâche un peu la situation avec l'avantage du numéro 1. Tout le monde a joué à la bataille navale, et seuls quelques étudiants en mathématiques ont joué aux échecs en 4-D. Mais l'expérience montre que grâce à la représentation bidimensionnelle du cube, la plupart des utilisateurs s'habituent rapidement au nouveau modèle de données.
Fonction JOIN et métadonnées
Le modèle multidimensionnel vous permet d'utiliser des métadonnées pour décrire des cellules. La méthode d'adressage décrite ci-dessus signifie que chaque cellule de la feuille de calcul correspond à un ensemble spécifique d'éléments de liste (par exemple, année, produit et point de vente). Les listes, à leur tour, peuvent avoir des attributs (colonnes), ce qui les fait ressembler à des tables relationnelles régulières. Par exemple, vous pouvez ajouter une colonne de devise à une liste de points de vente, liant ainsi les listes de points de vente et de devises dans une relation de cardinalité plusieurs-à-un.
La fonction JOIN permet de référencer dynamiquement des cellules en utilisant une telle relation. Cette fonction remplace RECHERCHEV, éliminant le besoin de travailler avec des index.
Exemple: pour calculer le montant des ventes dans le monde, vous devez d'abord convertir le montant des ventes de chaque pays en une seule devise (multipliez la position «ventes» par le taux de change). Dans Excel, nous stockerions 2 tableaux: une liste de pays avec une devise pour chaque pays et une liste de devises avec un taux de change. Afin de trouver le taux correct, nous utiliserions la fonction RECHERCHEV deux fois: trouver le code de devise par le nom du pays et trouver le taux de change par le code de devise.
Une référence à une cellule avec le taux de change pourrait ressembler à ceci:,
EX_RATES.[COUNTRY.join(CURRENCY)]où
EX_RATESest le nom de la feuille de calcul avec les taux de change
COUNTRY- dimension avec pays
CURRENCY- dimension avec devises Les
chaînes de liens peuvent être de n'importe quelle longueur, par exemple:
STORE.join(COUNTRY).join(CURRENCY)
En fait, lorsque nous construisons le modèle, nous créons un contour de flocon de neige. La fonction JOIN permet aux formules de référencer dynamiquement des cellules dans des feuilles de calcul à l'aide de liens entre des tables (listes) de ce schéma. Dans ce cas, les dépendances entre les cellules sont explicitement spécifiées dans les arguments de la fonction JOIN.
Domaine de validité des formules
La possibilité de spécifier la zone d'effet élimine le besoin de copier des formules.
Pour chaque dimension du cube, on définit un ensemble d'éléments sur lesquels agit la formule, tels que: toutes les années, les produits de type "vélo", la rubrique du rapport "chiffre d'affaires". En pratique, cela ressemble à ceci (le but de la formule est marqué en bleu, ses arguments sont marqués en rouge et orange. La liste des éléments sélectionnés pour chaque dimension se trouve en bas de l'écran):
Cette approche corrige la faille numéro 2 et vous permet d'ajouter et de supprimer des éléments ou même des dimensions sans changer la formule. Cela élimine également le besoin de rechercher toutes les cellules dans lesquelles la formule a été copiée chaque fois que nous voulons la modifier.
Interactivité cellulaire
Cette innovation vous permet de créer des interfaces interactives à l'aide de formules. Les formules peuvent être utilisées non seulement pour calculer la valeur d'une cellule, mais également pour formater des cellules (mise en forme de cellule), changer la couleur des cellules (couleur de cellule) et pour masquer ou afficher un groupe de cellules ou des colonnes ou des lignes entières (visibilité de cellule). Les cellules peuvent être formatées non seulement sous forme de nombres, de dates et de texte, mais également sous forme de boutons, de cases à cocher et de listes déroulantes.
Ainsi, par exemple, la couleur des cellules peut changer en fonction de la valeur de la cellule. Une case à cocher ou une liste de sélection dans une feuille peut afficher, masquer ou verrouiller des cellules dans une autre feuille.
Les boutons dans les cellules vous permettent de créer des opérations assez complexes sur les valeurs de cellule. Lors de la création d'un bouton, nous définissons la cible de l'opération (plage de cellules) et la formule, qui est exécutée une fois pour chacune des cellules cibles. Il peut y avoir plusieurs opérations sur un seul bouton. Ainsi, appuyer sur un bouton peut, par exemple, copier des données de l'année précédente sur la suivante, ou distribuer le contenu d'une cellule à plusieurs autres cellules, proportionnellement à une certaine valeur (éclaboussures).
Les boutons combinés aux restrictions d'accès utilisateur permettent une fonctionnalité irréversible. Ainsi, par exemple, un utilisateur qui a accès à un bouton, mais n'a pas accès à la cellule cible, ne pourra écrire dans la cellule que ce que la formule du bouton lui permettra.
Conclusion
Le nouveau processeur de feuille de calcul permet des modèles beaucoup plus complexes que ce qui est possible dans d'autres systèmes. En même temps, les modèles restent clairs et faciles à entretenir. La probabilité d'erreurs dans les formules est également considérablement réduite.
Le prix de ces avantages est la complexité accrue du système. Avant de commencer le travail, l'utilisateur doit créer un modèle de données sous forme de listes et de cubes.
En général, le système est conçu pour un utilisateur techniquement plus alphabétisé qu'Excel (par exemple, des économistes ayant des connaissances de base en programmation ou des programmeurs travaillant sur des modèles économiques).
Je me ferai un plaisir de répondre à vos questions dans les commentaires ou les messages privés. En outre, sur Internet, vous pouvez trouver la documentation du système et plusieurs vidéos de formation.