Hojas de cálculo y sus capacidades
El principio por el cual funcionan las hojas de cálculo modernas (Microsoft Excel, LibreOffice Calc o Google Sheets) apareció a finales de los 70 y mediados de los 80. En 1979 apareció en VisiCalc una matriz bidimensional de celdas como modelo de datos y la capacidad de calcular automáticamente usando fórmulas. Una matriz tridimensional de celdas (la capacidad de usar varias hojas) apareció por primera vez en 1985 en Boeing Calc.
En teoría, las hojas de cálculo son tan buenas como cualquier lenguaje de programación. Existe una máquina de Turing basada en fórmulas de Excel ( enlace ), lo que significa que cualquier algoritmo que se pueda implementar mediante una computadora se puede implementar en Excel. La única pregunta es la conveniencia y eficiencia de tal implementación.
En la práctica, me he encontrado con sistemas muy complejos implementados en Excel. Por ejemplo, un modelo financiero para el desarrollo de un aeropuerto internacional con capacidad para aportar muchos tipos diferentes de instalaciones (estacionamientos, almacenes, carriles, ...) y recalcular metros cuadrados y plazas de estacionamiento en flujo de caja (costos por años de construcción vs beneficio por años de operación), teniendo en cuenta diferentes modelos. inflación. Puede llevar desde varios meses-persona hasta varios años-persona "reescribir" tal "excelencia" en Java utilizando una base de datos relacional. En este caso particular, el modelo relacional de la base de datos constaba de más de 50 tablas. Lo más interesante es que esta "reescritura" podría haberse evitado si las hojas de cálculo no solo permitieran la creación de software, sino que también hicieran posible el mantenimiento y la escalabilidad.Para el usuario final (economista), el sistema Java es un paso atrás, porque ya no ve resultados intermedios y no puede cambiar o complementar el modelo por sí mismo.
Resulta que el mismo problema se puede resolver como una hoja de cálculo y un lenguaje de programación universal. Esto significa que podemos comparar las fortalezas y debilidades de estas dos herramientas como medio para crear aplicaciones comerciales. Aquí intentaremos ver Excel a través de los ojos de un programador arquitecto y aplicar las reglas de la arquitectura de software que ya están bien establecidas en el desarrollo de software clásico.
Beneficios de las hojas de cálculo
- Concepto intuitivo : cada uno de nosotros en la escuela vimos y llenamos carteles en trozos de papel en una caja y jugamos batalla naval. La mayoría de las personas que trabajan con Excel nunca han recibido ningún entrenamiento especial (en el mejor de los casos, un colega mostró qué botones presionar en media hora). Esta es una gran ventaja sobre los lenguajes de programación donde "C ++ en 21 días" suena incluso demasiado optimista.
- : , , - . breakpoints . . , .
- : , . , UI, .
- : . Notepad, Java . . . , E5 . VLOOKUP . -, .
- : DRY (Don’t repeat yourself — ). , (, /) . . , , , . . .
- Falta de interactividad de la interfaz : las hojas de cálculo no le permiten cambiar dinámicamente la forma en que se muestran los datos. Tampoco es posible crear operaciones programadas realizadas, por ejemplo, presionando un botón.
¿Cómo mejorar las hojas de cálculo?
Mi nombre es Vadim. Soy CTO en CubeWeaver y he estado desarrollando una nueva hoja de cálculo durante bastante tiempo. Hace varios años ya escribí ( enlace ) sobre una versión inicial del sistema, pero desde entonces ha cambiado mucho y este año el proyecto ha alcanzado la etapa comercial.
Aquí hay una lista de innovaciones de mi proyecto, que permite eliminar los inconvenientes antes mencionados, mientras se busca preservar los beneficios de una hoja de cálculo:
Modelo de datos multidimensional
El modelo de datos multidimensionales se utiliza ampliamente en sistemas OLAP y Business Intelligence para el análisis de datos. La esencia del modelo es almacenar datos en las celdas de un cubo multidimensional, cuyos bordes están firmados por los encabezados de los objetos comerciales:
La interfaz del programa no muestra el cubo multidimensional completo, sino su corte bidimensional correspondiente a la combinación de filtros que hemos elegido:
Al implementar un modelo de este tipo en un sistema de BI relacional, a menudo se usa el esquema de copo de nieve. Los cubos se implementan mediante tablas de hechos y los encabezados de caras se almacenan en tablas de dimensiones.
En mi sistema, los cubos se denominan hojas de trabajo y los títulos en los bordes del cubo se denominan elementos de lista.
Cada celda de una hoja de trabajo multidimensional tiene una dirección única, que consta de etiquetas en los bordes. Por ejemplo, el valor 935 en la imagen tiene la dirección: Bicicletas, 2020, París.
Cada elemento de la lista tiene un nombre y una identificación. Las referencias de celda usan identificadores, y la dirección anterior en una fórmula podría verse así (las referencias están entre corchetes):
[PROD:23, YEAR:2020, CITY:24], donde PROD es el identificador de la lista de "productos" y 23 es el identificador del artículo "Bicicletas".
El uso de un modelo multidimensional puede mejorar significativamente la situación con la desventaja número 1. Primero, los encabezados ahora se almacenan por separado de los datos numéricos. En segundo lugar, la introducción de una dimensión adicional "métrica" (o "posición del informe") permite abordar las celdas no por su número ordinal, sino por su significado semántico, eliminando los errores debidos a la adición o eliminación de columnas o filas.
Por supuesto, hay que decir que este enfoque estropea ligeramente la situación con la ventaja número 1. Todos jugaron batallas navales y solo unos pocos estudiantes de matemáticas jugaron ajedrez 4-D. Pero la experiencia muestra que gracias a la representación bidimensional del cubo, la mayoría de los usuarios se acostumbran rápidamente al nuevo modelo de datos.
Función UNIR y metadatos
El modelo multidimensional le permite usar metadatos para describir celdas. El método de direccionamiento descrito anteriormente significa que cada celda de la hoja de trabajo corresponde a un conjunto específico de elementos de la lista (por ejemplo, año, producto y punto de venta). Las listas, a su vez, pueden tener atributos (columnas), lo que las hace parecer tablas relacionales normales. Por ejemplo, puede agregar una columna de moneda a una lista de punto de venta, vinculando así las listas de punto de venta y moneda en una relación de cardinalidad de muchos a uno.
La función JOIN hace posible hacer referencia dinámicamente a celdas usando dicha relación. Esta función reemplaza BUSCARV, eliminando la necesidad de trabajar con índices.
Ejemplo: para calcular la cantidad de ventas para el mundo, primero debe convertir la cantidad de ventas para cada país en una sola moneda (multiplicar la posición de “ventas” por el tipo de cambio). En Excel, almacenaríamos 2 tablas: una lista de países con una moneda para cada país y una lista de monedas con un tipo de cambio. Para encontrar la tasa correcta, usaríamos la función BUSCARV dos veces: busque el código de moneda por el nombre del país y busque la tasa de cambio por el código de moneda.
Una referencia a una celda con el tipo de cambio podría verse así:
EX_RATES.[COUNTRY.join(CURRENCY)]donde
EX_RATESestá el nombre de la hoja de trabajo con los tipos de cambio
COUNTRY- dimensión con países
CURRENCY- dimensión con monedas Las
cadenas de enlaces pueden tener cualquier longitud, por ejemplo:
STORE.join(COUNTRY).join(CURRENCY)
De hecho, cuando construimos el modelo, creamos un contorno de copo de nieve. La función JOIN permite que las fórmulas hagan referencia dinámicamente a celdas en hojas de trabajo usando enlaces entre tablas (listas) de ese esquema. En este caso, las dependencias entre las celdas se especifican explícitamente en los argumentos de la función JOIN.
Área de validez de las fórmulas
La capacidad de especificar el área de efecto elimina la necesidad de copiar fórmulas.
Para cada dimensión del cubo, definimos un conjunto de elementos sobre los que actúa la fórmula, tales como: todos los años, productos del tipo "bicicleta", el rubro del informe "ingresos". En la práctica, se ve así (el objetivo de la fórmula está marcado en azul, sus argumentos están marcados en rojo y naranja. La lista de elementos seleccionados para cada dimensión está en la parte inferior de la pantalla):
Este enfoque corrige el defecto número 2 y le permite agregar y eliminar elementos o incluso dimensiones sin cambiar la fórmula. También elimina la necesidad de buscar todas las celdas donde se copió la fórmula cada vez que queremos cambiarla.
Interactividad celular
Esta innovación le permite crear interfaces interactivas utilizando fórmulas. Las fórmulas se pueden usar no solo para calcular el valor de una celda, sino también para formatear celdas (formato de celda), cambiar el color de las celdas (color de celda) y para ocultar o mostrar un grupo de celdas o columnas o filas enteras (visibilidad de celda). Las celdas pueden formatearse no solo como números, fechas y texto, sino también como botones, casillas de verificación y menús desplegables.
Así, por ejemplo, el color de las celdas puede cambiar según el valor de la celda. Una casilla de verificación o lista de selección en una hoja puede mostrar, ocultar o bloquear celdas en otra hoja.
Los botones en las celdas le permiten crear operaciones bastante complejas en los valores de las celdas. Al crear el botón, establecemos el destino de la operación (rango de celdas) y la fórmula, que se ejecuta una vez para cada una de las celdas de destino. Puede haber varias operaciones en un botón. Entonces, presionar un botón puede, por ejemplo, copiar datos del año anterior al siguiente, o distribuir el contenido de una celda entre varias otras celdas, proporcionalmente a algún valor (salpicaduras).
Los botones combinados con las restricciones de acceso de los usuarios permiten una funcionalidad irreversible. Entonces, por ejemplo, un usuario que accedió a un botón, pero no tuvo acceso a la celda de destino, podrá escribir en la celda solo lo que le permita la fórmula en el botón.
Conclusión
El nuevo procesador de hojas de cálculo permite modelos mucho más complejos de lo que es posible en otros sistemas. Al mismo tiempo, los modelos siguen siendo claros y fáciles de mantener. La probabilidad de errores en las fórmulas también se reduce significativamente.
El precio a pagar por estos beneficios es la mayor complejidad del sistema. Antes de comenzar a trabajar, el usuario debe crear un modelo de datos en forma de listas y cubos.
En general, el sistema está diseñado para un usuario técnicamente más alfabetizado que Excel (por ejemplo, economistas con conocimientos básicos de programación o programadores que trabajan en modelos económicos).
Estaré encantado de responder a sus preguntas en los comentarios o mensajes privados. Además, en Internet, puede encontrar documentación para el sistema y varios videos de capacitación.