Extrair valores específicos para colunas separadas

Dúvida. Uma tabela tem uma coluna no formato “cor1 / cor2 / cor3“. Como fazer uma fórmula para separar cada cor numa coluna?

Duas soluções abaixo.

1)

Vou fazer em etapas para ficar didático.

Imagine que você tem uma string: “amarelo 11/azul 18/verde 1“

A fórmula Localizar, informa a posição do primeiro caracter “/”

=LOCALIZAR(“/”;B7;1)

A partir disso, extraio o primeiro pedaço com a fórmula =EXT.TEXTO(B7;1;C7-1), informando a localização do “/’ obtida anteriormente.

(Poderia ser “esquerda” também)

Para a segunda string, é a mesma coisa.

O resto do texto puxo com um “direita”

=DIREITA(B7;NÚM.CARACT(B7)-C7)

Mando localizar o “/”, extraio à esquerda e à direita.

A única diferença é que, se não achar o “/”, vai dar erro. Então, utilizar a fórmula “seerro”.

Vide arquivo para download.

2) Sem fórmulas, é possível em Dados -> Texto para colunas

Colocar “/” como critério de separação.

Ideias técnicas com uma pitada de filosofia: https://ideiasesquecidas.com

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/

Performance VBA – worksheet function

Esta é uma dica de performance um pouco mais avançada.

Existem várias funções no VBA que também existem no Excel, e são chamadas por um comando do tipo appplication.worksheetfunction. Alguns exemplos são funções como arredondar, mínimo, máximo, etc…

Porém, nos casos que precisamos de alta performance, faz sentido essas funções ou utilizar a versão VBA delas, se houver.

Isto porque o worksheetfunction faz o equivalente a jogar na planilha, aplicar a função e voltar para o código.

Um exemplo simples.

A macro1 utiliza application.worksheetfunction.round 25 milhões de vezes. No meu computador, demora 139 segundos, ou seja, mais de 2 minutos.

For i = 1 To 5000

    For j = 1 To 5000

        y = Application.WorksheetFunction.Round(i / j, 0)

    Next j

Next i

Já a macro2 utiliza math.round (função nativa VBA), demorando 2 segundos.

For i = 1 To 5000

    For j = 1 To 5000

        y = Math.Round(i / j, 0)

    Next j

Next i

Segue link da planilha para testar.

Ideias técnicas com uma pitada de filosofia: https://ideiasesquecidas.com

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/

Algumas dicas para lidar com arquivos grandes

O Excel é uma ferramenta de análise de dados extremamente poderosa. Porém, quando ele fica grande demais, começa a travar, os cálculos ficam demorados, etc.

Há algumas dicas a respeito, que podem ajudar a melhorar bastante a performance.

1) Restringir o Procv 

Para fazer cruzamento entre bases, usa-se bastante o procv. Um procv irrestrito é quando o critério pega a coluna inteira, como a seguir.

=PROCV(C2;F:F;1)

Embora seja fácil e garanta que uma nova base com mais linhas entre na conta, a performance diminui. Isso porque o Excel vai tentar procurar desde a primeira linha (1) até a última linha (1.058.576).

Deve-se limitar o range de buscas, para um número suficientemente grande para a base. Por exemplo, se 5000 linhas forem suficientes:

=PROCV(C2;F1:F5000;1)

2) Restringir Somase, somases, contses,  e outros do tipo.

A mesma dica de restringir o range vale para fórmulas como somases e outras do tipo, pela mesma razão descrita acima.

3)

Excluir (não é apagar, é excluir) linhas vazias. Às vezes, uma planilha pode estar com 10 linhas com informação, mas estar utilizando 1 milhão de linhas! Isto ocorre, por exemplo, se alguém utilizar as 1 milhão de linhas, e apagar o conteúdo (com a tecla del). Isto vai apagar o valor da célula, mas o Excel vai continuar considerando que o range útil é de 1 milhão de linhas. O mesmo vale para colunas vazias.

Deve-se excluir (não apagar) esse range (selecionar as linhas, clicar com o botão direito e excluir – ou utilizar o atalhao ctrl -).

Uma forma de conferir o range utilizado é através do atalho ctrl end. Isto vai levar o cursor à última célula livre.

RangeNaoVazio.JPG

4)

Vínculos: algumas vezes, a tabela pode ter vínculos, como o da imagem a seguir. Isto pode dificultar muito a manipulação da planilha, se o vínculo for de milhares de células, por exemplo.

Este vínculo é mesmo necessário? Se não for, em Dados -> Editar links (no Excel 2010) há uma opção para excluir o vínculo.

EditarLinks.JPG

5)

Nomes. Atrelado ao tema vínculos. De vez em quando, há um nome vinculando outra fonte de informações, e pesando muito. Pode ser que nem o usuário tenha criado, mas algum outro Addin que manipula o excel.

Uma forma de deletar tudo o quanto é nome é rodando o código a seguir.

1 2 3 4 5 6 7 8 9 10 11 12 13 Sub delNames()   Dim nme As Name   On Error Resume Next   For Each nme In ActiveWorkbook.Names   nme.Delete   Next   End Sub

6)

Deletar planilhas desnecessárias.

Será que todos os dados da planilha são úteis para alguma coisa? Deve-se deletar tudo o que não for importante.

7)

Salvar como xlsb.

O xlsb é um formato binário, compactado de dados. A planilha pode ficar bem menor.

8) Se a planilha for mesmo muito pesada, pode ser necessário ativar o cálculo manual.

Em arquivos -> opções -> fórmulas, há a opção de mudar o cálculo de automático para manual e vice-versa.

CalcAutomatico.JPG

Este tipo de boa prática já transformou cálculos que demoravam meia hora para poucos minutos.

Alinhar objetos

O Excel possui algumas funções úteis para alinhar objetos.

Suponha que eu tenha vários objetos como na figura.

Para alinhar, deve-se selecionar todos e ir em Formatar -> alinhar ao meio.

A seguir, Formatar -> Distribuir na horizontal.

Há diversas outras opções, como alinhar à esquerda, em cima, embaixo, etc.

Ideias técnicas com uma pitada de filosofia: https://ideiasesquecidas.com

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/

Artigo sobre torre de controle

Forgotten Lore

Publiquei um artigo na revista Mundo Logística deste mês, sobre a nossa Central de Monitoramento Logístico, com o apoio do time do projeto.

Foi
um mega projeto que envolveu criar toda a infraestrutura de comunicação
(torres e repetidoras), o software de gerenciamento de eventos e a
inteligência matemática para distribuição dos veículos.

Estamos à disposição para compartilhar ideias a respeito.

https://revistamundologistica.com.br.

Ver o post original

Média ses

Exercício na forma de teste. Dada uma tabela, tirar a média dos valores por região, apenas se o campo “Considerar” for igual a 1. Três resoluções abaixo.

1)      O método mais simples é usar a função média.ses.

Conforme o próprio nome indica, a função retorna a média, para vários critérios.

=médiases (B:B; A:A;E4; C:C;1)

               B:B – Porque os valores estão na coluna B

               A:A – primeiro critério, região

               E4 – a região escolhida, digamos Curitiba

               C:C – segundo critério, considerar ou não

               1 – considerar apenas quem é 1

2 – O segundo método é criar uma tabela dinâmica.

A única “pegadinha” é que a tabela dinâmica normalmente faz a soma. Tem que mudar para média.

3 – Nas versões antigas de Excel, a função média.ses não existia. Mas dava para fazer com somases e cont.ses, e depois dividindo um pelo outro. É até mais didático.

Link para download.


Ideias técnicas com uma pitada de filosofia: https://ideiasesquecidas.com

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/

Fórmulas como texto

Este erro é comum quando baixamos relatórios de outros sistemas, como BI.

Às vezes, o campo que deveria ser uma fórmula não faz o cálculo, e sim, fica com a fórmula aparecendo, como no exemplo a seguir.

O motivo disto é que a célula está formatada como texto (string). A solução é mudar a formatação, para número.

Clicar com botão direito na célula com o problema e Formatar células (tecla de atalho: CTRL + 1).

No menu, colocar como Geral, ou Número.

É necessário entrar na célula e dar Enter de novo.

Agora, vai aparecer o valor calculado pela fórmula.

A seguir, contribuição de Michael Wilker.

Também pode ser que esteja com opção de exibição para mostra fórmulas ao invés de resultados;


Ideias técnicas com uma pitada de filosofia: https://ideiasesquecidas.com

Ferramentas Excel-VBA: https://ferramentasexcelvba.wordpress.com/