segunda-feira, 20 de junho de 2011

Manipulação de texto no Excel

Por recomendação do caro amigo @epsouza, vou mostrar algumas funções  para manipular textos e extrair valores dos mesmos.
Eu explico. Talvez você encontre uma tabela de dados ou apenas alguns números em algum site ou programa que gostaria de poder usar em sua planilha. Você de cara da um ctrl+c e depois um ctrl+v na sua planilha e pra sua surpresa, os dados não são colados como o desejado.
Como digitar dá trabalho e sempre vale a máxima "quando o crânio é fraco, o corpo padece". Sou arredio ao serviço braçal, portanto, vamos às fórmulas.

Como exemplo, infelizmente não vou usar maçãs, laranjas ou uma lista pequena comum nos tutoriais por aí. Vamos pra algo mais "hardcore".
Vou utilizar os dados da janela de texto do AutoCAD. Vou criar uma sequencia de retas no CAD, usar o comando "list" e manipular o resultado no Excel para extrair as coordenadas dos vértices. De quebra, desenhar no Excel estas mesmas linhas. Ousado? Também to achando hehe

Vamos lá. Primeiro o desenho.
Desenhei 6 retas aleatórias.

Em seguida, executamos o comando "list" e selecionamos as 6 linhas.
Com o F2, podemos abrir a janela com os dados.


Observe, que para deixar mais interessante nosso tutorial, o comando "list", interrompe a listagem por página, quebrando o que seria um padrão (a cada reta descrita em 7 linhas de texto) que facilitaria um pouco. O comando insere no final de cada página, um texto: "Press ENTER to continue:"
Pra nosso tutorial, isso serve perfeitamente.

Vamos agora abrir o Excel e colar este texto lá. Seleciona tudo, e com o famoso ctrl+c na janela do CAD e ctrl+v em uma planilha em branco no Excel, temos a seguir:


Clique entre o título de coluna A e B, para ajustar a largura (apenas para deixar mais organizado).

Na coluna B, criaremos nossa primeira fórmula. Nesta, vamos pesquisar quais linhas contém o texto "from". Com isso, saberemos que o X e o Y desta linha são o ponto de partida da reta.
A fórmula ficaria assim:

=PESQUISAR("from";A1)

Note que ela retorna um erro: #VALOR!
Isto ocorre pois na célula A1, não há o texto "from". Copie e cole a fórmula até a célula B5. O resultado #VALOR! se repete até a célula B4, mas na célula B5 retorna o valor 15. Ou seja, a letra "f" do texto "from" inicia-se no 15º caractere.


Uma forma que eu uso muito (apenas no Excel 2007/2010) para evitar os erros, é a função SEERRO.
Nossa fórmula ficaria assim:

=SEERRO(PESQUISAR("from";A1);"")   O string vazio "" como segundo argumento da função SEERRO, é o resultado que a fórmula irá retornar quando o primeiro argumento PESQUISAR("from";A1) retornar um erro.

Vamos agora identificar o texto "to point" criando uma fórmula parecida com a primeira na coluna C.


Pronto, agora temos identificadas as linhas onde estão os dados que queremos. Na verdade, eu poderia ter retornado um valor "VERDADEIRO"  ao invés da posição do texto (o 15 e o 17 indicados na figura), mas seria mais linha de comando a toa.

Vou utilizar uma coluna para cada fórmula, para não deixar muito extenso e não parecer mais complicado do que realmente é.

Bom, agora que sabemos qual linha contém os dados de origem e qual linha contém os dados de destino de cada segmento de reta, vamos identificar onde começa e onde termina, no texto, o X e o Y. Para saber onde termina o Y, vou procurar o início do Z. E o final do X, é o início do Y.

Vamos precisar de 3 colunas para origem e 3 colunas para destino encontrar estes 6 valores (em azul na figura acima).
Inseri 2 linhas antes da primeira linha, para dar espaço para os títulos das colunas, senão, começamos a nos perder.


Vou dar uma formatada pra não ficar muito "boca de porco" (jargão técnico). Apenas bordas e uma centralizada nos textos.



Agora vamos a algumas fórmulas:
Célula D3)     =SE(ÉNÚM($B3);PESQUISAR("X";$A3);"")
Célula E3)     =SE(ÉNÚM($B3);PESQUISAR("Y";$A3);"")
Célula F3)     =SE(ÉNÚM($B3);PESQUISAR("Z";$A3);"")
Célula G3)     =SE(ÉNÚM($C3);PESQUISAR("X";$A3);"")
Célula H3)     =SE(ÉNÚM($C3);PESQUISAR("Y";$A3);"")
Célula I3)     =SE(ÉNÚM($C3);PESQUISAR("Z";$A3);"")
Selecione a célula D3 até a I3 (D3:I3 na língua excel), copie e cole para baixo até o final do texto colado do CAD.

Mais uns ajustes... E teoricamente, a sua planilha, se estiver acompanhando, ficaria assim

Agora, nas colunas seguintes, digitemos:
Célula J3)     =SE(ÉNÚM(D3);EXT.TEXTO($A3;D3+2;E3-D3-2);"")
Célula K3)     =SE(ÉNÚM(E3);EXT.TEXTO($A3;E3+2;F3-E3-2);"")
Célula L3)     =SE(ÉNÚM(G3);EXT.TEXTO($A3;G3+2;H3-G3-2);"")
Célula M3)     =SE(ÉNÚM(H3);EXT.TEXTO($A3;H3+2;I3-H3-2);"")

Após colar essas fórmulas abaixo, deve obter um resultado assim: 

Como podemos observar, a partir deste momento, já temos os dados que queremos. Mas se tentarmos fazer alguma operação com eles, vamos perceber que ainda são textos. Para converter um texto em número, vamos utilizar o comando =VALOR(texto)
Antes, é necessário substituir o ponto decimal para vírgula.
Fazemos assim:

=SUBSTITUIR(J3;".";",")

Como podem observar, vai substituir o ponto "." pela vírgula "," no texto J3. Basta então, copiar para o lado e para baixo essa fórmula para funcionar para o resto.

Agora, pra não usarmos mais colunas, vou completar a fórmula anterior assim:

=SE(J3<>"";VALOR(SUBSTITUIR(J3;".";","));0)

ou assim:

=SEERRO(VALOR(SUBSTITUIR(J3;".";","));0)

O comando =SEERRO() foi incorporado no Excel 2007 em diante. Portanto, quem usar um Excel anterior, terá que usar a fórmula de cima.

Com isso, nossa planilha fica assim: 


Bom, acho que é isso. Eu pretendia ligar os pontos utilizando esse resultado, mas vou deixar pra outra postagem. Senão não termino essa e os comandos necessários, servem para outro assunto.

A planilha e o arquivo em CAD estão nesta pasta: Postagem 09 - Manipulação de texto no Excel.

Abraços

Nenhum comentário:

Postar um comentário

"Be cool" quando for escrever seu comentário. Seja construtivo!