Como traduzir cores de células do Excel em informação para análise de dados.
Imagine que para um estudo é preciso coletar informações de uma (ou várias) planilhas do Excel, organizar esses dados de forma que façam sentido e deles buscar possíveis informações de valor talvez antes “invisíveis”.
Isto é tarefa corriqueira na fase de coleta e organização dos dados e existem várias formas de se fazer isso, inclusive com o próprio Excel através do Power Query ou mesmo com o Power BI.
“ Mas como a vida não é nenhum mar de rosas…” alguém decidiu destacar as informações mais importantes da planilha marcando com cores as suas células.
O que fazer então, já que “naturalmente” o Power Query não lê isso?
Após algumas horas de busca e leitura descobri que é possível ler as cores das células e mais outros atributos que ficam registrados nos arquivos .xlsx.
Nesta busca encontrei três soluções viáveis:
Opção 1: Leitura usando o Power Query do Excel ou PowerBI;
Opção 2: Utilizar Python;
Opção 3: Utilizar R.
Opção 1:
Para pequenas consultas encontrei um código no Power Query que funciona bem:
O exemplo dado faz a extração dos atributos e depois tem uma sequência de consultas que são então mescladas (merge) para compor uma tabela final com os dados das células.
Cada código abaixo deve ser inserido no Power Query como uma nova consulta (em branco).
1.1 ) Descompactação dos atributos do .xlsx
O código cria uma função que descompacta o arquivo .xlsx.
1.2 ) Extração dos atributos existentes “dentro” de um arquivo .xlsx
let
Source = File.Contents("C:\caminho_do_seu_arquivo\Pasta.xlsx"),
MyData = DecompressFiles(Source, 0, "", null),
#"Added Custom" = Table.AddColumn(MyData, "XmlContent", each Xml.Document([Content]))in
#"Added Custom"
Esta pequena consulta inicia a extração de dados à partir do arquivo indicado na etapa “Source” e cada linha traz um conteúdo em forma de tabela que pode ser então aberto e avaliado.
1.3 ) Consulta #1 -Criação da primeira tabela índice.
Esta consulta faz uma sequência de filtros e aberturas até chegar na tabela referência abaixo que será utilizada posteriormente.
1.4 ) Consulta #2 -Criação da tabela índice com as cores.
Esta etapa fornece ao final as cores e depois será mesclada com outras 2 para consolidar que células possuem cores diferenciadas.
1.5 ) Consulta #3 -Consolidação da tabela com as células coloridas.
Esta é a etapa mais longa, faz uma consulta e agrupa com as etapas anteriores “cellXfs” e “fills”, entregando a tabela final com as células e respectivas cores.
Há outros atributos, além das cores, que poderiam ser pesquisados mas que não foram foco deste estudo.
Os arquivos .xlsx com os códigos na linguagem M estão no site do www.excelguru.ca mencionado anteriormente.
Observações sobre a Opção 1:
Em testes com apenas uma planilha pequena ele funcionou bem. Mas quando apontei para um diretório com mais de 600 arquivos .xlsxm de 1,2Mb cada, tive que interromper a rotina no momento em que o cálculo mostrava um gasto de 1.000Mb de memória.
Não sei então se este método é realmente pesado ou se foi o fato dos meus arquivos serem grandes demais e cheios de conexões, macros, etc…
Opção 2:
Utilizar um código em Python e tratar essas planilhas antes de submetê-las ao Power Query.
É uma solução híbrida e tem como “desvantagem” ter que se usar outro programa além do PowerBI, mas é inegavelmente mais rápido e abre uma porta para o uso desta potente ferramenta de programação.
Outro ponto é que toda a preparação dessa base de dados (fluxo de abertura, tratamento e consolidação de vários arquivos), pode ser feita diretamente no Python e depois ser exportada em um arquivo .csv ou mesmo .xslx para sua análise posterior pelo Excel ou mesmo Power BI.
O código abaixo faz a leitura da planilha com OPENPYXL, seleciona uma aba específica para a extração de dados e salva em um dataframe duas informações selecionadas: o valor da célula e o código de sua cor quando diferente da cor branca.
Em tempo:
É possível também utilizar um código em Python rodando dentro do Power BI, mas de qualquer forma é necessário instalar o Python na máquina e também programar algumas linhas. Abaixo seguem três referências para consulta:
Opção 3:
Utilizar um código em R.
Eu não testei nenhuma das soluções apresentadas abaixo, ficam apenas como referência para aqueles que queiram estudar.
A análise de dados é hoje uma atividade com relativo destaque dentro das grandes organizações porque consegue encontrar e mostrar informações relevantes ao negócio.
Mas para que qualquer análise tenha realmente valor é fundamental que seja bem feita a etapa de coleta de dados e, (com o que coloquei acima) não há mais desculpas para simplesmente desconsiderar as cores em destaque das células no Excel (desde que elas signifiquem alguma coisa).
