Planilhas como ferramenta para desenvolver aplicativos de negócios

O Excel é freqüentemente usado como uma ferramenta universal para desenvolver aplicativos de negócios. Neste artigo, quero comparar planilhas que existiram sem muitas mudanças por mais de 30 anos com o paradigma de programação imperativo clássico moderno dos olhos de um arquiteto de software. Em seguida, gostaria de falar sobre meu trabalho em um novo processador de planilhas que corrige muitos dos pontos fracos identificados durante a comparação, permitindo que você crie aplicativos de negócios mais confiáveis, escaláveis ​​e fáceis de manter e desenvolver.







Planilhas e seus recursos



O princípio pelo qual as planilhas modernas funcionam (Microsoft Excel, LibreOffice Calc ou Google Sheets) surgiu no final dos anos 70 - meados dos anos 80. Uma matriz bidimensional de células como um modelo de dados e a capacidade de calcular automaticamente usando fórmulas apareceu no VisiCalc em 1979. Uma matriz tridimensional de células (a capacidade de usar várias folhas) apareceu pela primeira vez em 1985 no Boeing Calc.



Em teoria, planilhas são tão boas quanto qualquer linguagem de programação. Existe uma máquina de Turing baseada em fórmulas do Excel ( link ), o que significa que qualquer algoritmo que pode ser implementado em um computador pode ser implementado no Excel. A única questão é a conveniência e eficiência de tal implementação.



Na prática, encontrei sistemas muito complexos implementados no Excel. Por exemplo, um modelo financeiro para o desenvolvimento de um aeroporto internacional com a capacidade de contribuir com muitos tipos diferentes de objetos (estacionamentos, armazéns, vias, ...) e recalcular metros quadrados e vagas de estacionamento no fluxo de caixa (custos por anos de construção vs lucro por anos de operação), levando em consideração diferentes modelos inflação. Pode levar de várias pessoas-meses a várias pessoas-anos para “reescrever” tal “excel” em Java usando um banco de dados relacional. Nesse caso específico, o modelo relacional no banco de dados consistia em mais de 50 tabelas. O mais interessante é que essa "reescrita" poderia ter sido evitada se as planilhas não apenas permitissem a criação de software, mas também possibilitasse a manutenção e a escalabilidade.Para o usuário final (economista), o sistema Java é um retrocesso, pois ele não vê mais resultados intermediários e não pode alterar ou complementar o modelo sozinho.



Acontece que o mesmo problema pode ser resolvido tanto com uma planilha quanto com uma linguagem de programação universal. Isso significa que podemos comparar os pontos fortes e fracos dessas duas ferramentas como meio de criar aplicativos de negócios. Aqui, tentaremos olhar para o Excel através dos olhos de um programador arquiteto e aplicar as regras da arquitetura de software que já estão bem estabelecidas no desenvolvimento clássico de software.



Benefícios das planilhas



  1. Conceito intuitivo : cada um de nós na escola viu e preencheu placas em pedaços de papel em uma caixa e jogou a batalha naval. A maioria das pessoas que trabalham com Excel nunca recebeu nenhum treinamento especial (na melhor das hipóteses, um colega mostrou quais botões apertar em meia hora). Esta é uma grande vantagem sobre as linguagens de programação onde "C ++ em 21 dias" soa até muito otimista.
  2. : , , - . breakpoints . . , .
  3. : , . , UI, .




  1. : . Notepad, Java . . . , E5 . VLOOKUP . -, .
  2. : DRY (Don’t repeat yourself — ). , (, /) . . , , , . . .
  3. Falta de interatividade da interface : as planilhas não permitem alterar dinamicamente a forma como os dados são exibidos. Também não é possível criar operações programadas realizadas, por exemplo, pressionando um botão.


Como melhorar as planilhas?



Meu nome é Vadim. Sou CTO da CubeWeaver e venho desenvolvendo uma nova planilha há algum tempo. Há vários anos já escrevi ( link ) sobre uma versão inicial do sistema, mas desde então muita coisa mudou e este ano o projeto atingiu a fase comercial.



Aqui está uma lista de inovações em meu projeto que abordam as desvantagens listadas acima enquanto tento manter os benefícios das planilhas:



Modelo de dados multidimensional



O modelo de dados multidimensional é amplamente utilizado em sistemas de Business Intelligence e OLAP para análise de dados. A essência do modelo é armazenar dados nas células de um cubo multidimensional, cujas bordas são assinadas pelos cabeçalhos dos objetos de negócios:





A interface do programa exibe não o cubo multidimensional inteiro, mas sua fatia bidimensional correspondente à combinação de filtros que escolhemos:





Ao implementar esse modelo em um sistema de BI relacional, o esquema em floco de neve é ​​frequentemente usado. Cubos são implementados por tabelas de fatos e cabeçalhos de face são armazenados em tabelas de dimensão.



No meu sistema, os cubos são chamados de planilhas e os títulos nas bordas do cubo são chamados de itens de lista.



Cada célula dessa planilha multidimensional possui um endereço exclusivo, consistindo em rótulos nas bordas. Por exemplo, o valor 935 na imagem tem o endereço: Bikes, 2020, Paris.

Cada item da lista possui um nome e ID As referências de células usam identificadores, e o endereço acima em uma fórmula pode ter a seguinte aparência (as referências estão entre colchetes):



[PROD:23, YEAR:2020, CITY:24]onde PROD é o identificador da lista de "produtos" e 23 é o identificador do item "Bicicletas".



O uso de um modelo multidimensional pode melhorar significativamente a situação com a desvantagem número 1. Primeiro, os cabeçalhos agora são armazenados separadamente dos dados numéricos. Em segundo lugar, a introdução de uma dimensão adicional “métrica” (ou “posição do relatório”) permite tratar as células não pelo seu número ordinal, mas pelo significado semântico, eliminando erros devido à adição ou remoção de colunas ou linhas.



Claro, deve ser dito que essa abordagem estraga um pouco a situação com a vantagem número 1. Todos jogavam batalhas navais, e apenas alguns estudantes de matemática jogavam xadrez 4-D. Mas a experiência mostra que, graças à representação bidimensional do cubo, a maioria dos usuários se acostuma rapidamente com o novo modelo de dados.



Função JOIN e metadados



O modelo multidimensional permite que você use metadados para descrever células. O método de endereçamento descrito acima significa que cada célula na planilha corresponde a um conjunto específico de itens de lista (por exemplo, ano, produto e ponto de venda). As listas, por sua vez, podem ter atributos (colunas), o que as torna semelhantes a tabelas relacionais regulares. Por exemplo, você pode adicionar uma coluna de moeda a uma lista de pontos de venda, vinculando as listas de pontos de venda e moedas em um relacionamento de cardinalidade muitos para um.



A função JOIN torna possível referenciar células dinamicamente usando tal relacionamento. Esta função substitui VLOOKUP, eliminando a necessidade de trabalhar com índices.



Exemplo: para calcular o valor das vendas para o mundo, primeiro você precisa converter o valor das vendas de cada país em uma única moeda (multiplique a posição “vendas” pela taxa de câmbio). No Excel, armazenaríamos 2 tabelas: uma lista de países com uma moeda para cada país e uma lista de moedas com uma taxa de câmbio. Para encontrar a taxa correta, usaríamos a função VLOOKUP duas vezes: encontre o código da moeda pelo nome do país e encontre a taxa de câmbio pelo código da moeda.



Uma referência a uma célula com uma taxa de câmbio poderia ter a seguinte aparência :,

EX_RATES.[COUNTRY.join(CURRENCY)]onde

EX_RATESé o nome da planilha com taxas de câmbio

COUNTRY- dimensão com países

CURRENCY- dimensão com moedas As



cadeias de links podem ter qualquer comprimento, por exemplo:STORE.join(COUNTRY).join(CURRENCY)



Na verdade, quando construímos o modelo, criamos um contorno de floco de neve. A função JOIN permite que fórmulas façam referência dinâmica a células em planilhas usando links entre tabelas (listas) desse esquema. Nesse caso, as dependências entre as células são especificadas explicitamente nos argumentos da função JOIN.



Área de validade das fórmulas



A capacidade de especificar a área de efeito elimina a necessidade de copiar fórmulas.



Para cada dimensão do cubo, definimos um conjunto de elementos sobre os quais atua a fórmula, tais como: todos os anos, produtos do tipo "bicicleta", o item do relatório "receita". Na prática, tem a seguinte aparência (O objetivo da fórmula é marcado em azul, seus argumentos são marcados em vermelho e laranja. A lista de elementos selecionados para cada dimensão está na parte inferior da tela):





Essa abordagem corrige a falha número 2 e permite que você adicione e remova itens ou mesmo dimensões sem alterar a fórmula. Também elimina a necessidade de pesquisar todas as células onde a fórmula foi copiada sempre que quisermos alterá-la.



Interatividade celular



Esta inovação permite criar interfaces interativas usando fórmulas. As fórmulas podem ser usadas não apenas para calcular o valor de uma célula, mas também para formatar células (formatação da célula), alterar a cor das células (cor da célula) e ocultar ou mostrar um grupo de células ou colunas ou linhas inteiras (visibilidade da célula). As células podem ser formatadas não apenas como números, datas e texto, mas também como botões, caixas de seleção e menus suspensos.



Assim, por exemplo, a cor das células pode mudar dependendo do valor da célula. Uma caixa de seleção ou lista de seleção em uma página pode mostrar, ocultar ou bloquear células em outra página.



Os botões nas células permitem criar operações bastante complexas nos valores das células. Ao criar o botão, definimos o destino da operação (intervalo de células) e a fórmula, que é executada uma vez para cada uma das células-alvo. Pode haver várias operações em um botão. Assim, ao premir um botão pode, por exemplo, copiar dados do ano anterior para o seguinte, ou distribuir o conteúdo de uma célula para várias outras células, proporcionalmente a algum valor (salpicos).



Os botões combinados com as restrições de acesso do usuário permitem funcionalidade irreversível. Assim, por exemplo, um usuário que tem acesso a um botão, mas não obtém acesso à célula de destino, será capaz de escrever na célula apenas o que a fórmula no botão permitir a ele.



Conclusão



O novo processador de planilhas permite modelos significativamente mais complexos do que é possível em outros sistemas. Ao mesmo tempo, os modelos permanecem claros e fáceis de manter. A probabilidade de erros nas fórmulas também é significativamente reduzida.



O preço desses benefícios é o aumento da complexidade do sistema. Antes de iniciar o trabalho, o usuário deve criar um modelo de dados na forma de listas e cubos.



Em geral, o sistema é projetado para um usuário tecnicamente mais letrado do que o Excel (por exemplo, economistas com conhecimento básico de programação ou programadores que trabalham com modelos econômicos).



Terei todo o gosto em responder às suas questões nos comentários ou mensagens privadas. Além disso, você pode encontrar na Internet a documentação do sistema e vários vídeos de treinamento.



All Articles