Este fórum está sendo desativado

[pjuliocesar]

Ola, tenho um problema e gostaria que esse fosse resolvido apenas com funções aninhadas.
O problema é o seguinte:

- Tenho várias pastas de trabalhos com uma determinada planilha (Vários arquivos e dentro deles há uma planilha especifica).
- Essa planilha específica possui a mesma formatação em todas as pastas de trabalho, apenas difere pela quantidade de linhas existentes em cada pasta de trabalho.
EX: O arquivo 1, possui uma "Lista 1" com os itens: A1, A2, A3, A4, A5 e A6
O arquivo 2, possui uma "Lista 2" com os itens: B1, B2,B3, até B11
O arquivo 3, possui uma "Lista 3" com os itens: C1, C2,C3, até C15
(Sendo que cada item está em uma linha)
- O que eu gostaria é criar uma nova planilha juntando todas as listas, uma após a outra. Ou seja, primeiro todas as linhas da Lista1, depois todas as linhas da Lista 2 e por fim todas as linhas da Lista 3.

Obs:
1) As listas possuem quantidade de linhas diferentes e elas poderão ter acréscimos de linhas com o passar do tempo, logo a planilha resultado deve acompanhar essas modificações "Aumentar de tamanho automaticamente".
Ex: Tempo 1:
Lista 1 com 3 linhas
Lista 2 com 4 linhas
Lista 3 com 5 linhas
- Logo o resultado será uma planilha com 12 linhas, sendo as 3 primeiras referentes a primeira lista, as 4 posteriores referente a segunda lista e por fim as 5 linhas da lista 3.
-Sendo assim, caso eu adicione uma nova linha na Lista 2 a planilha resultado deverá ter 13 linhas, sendo as 3 primeiras referentes a primeira lista, as 5 posteriores referente a segunda lista e por fim as 5 linhas da lista 3.
(Logo, deve haver um vínculo entre a planilha resultado e as planilhas que serão anexadas uma após a outra).

2) Além disso, seria interessante um código de facil manipulação para acrescentar novas listas.
______________________

Como exemplo ilustrativo segue em anexo o "Teste-Colunas", onde cada planilha representa um arquivo diferente de excel e no final tem a planilha resultado.

Desde já agradeço a colaboração;

[Jimmy]

Olá Julio,

Entendi bem o objetivo; você deixou tudo bem claro.

Eu não sei de quantas planilhas estamos falando, se várias significam 3 ou 4, ou 300. Isso é fundamental para a solução.

Fiz a fórmula abaixo que atende perfeitamente à planilha exemplo que você mandou, mas não sei se atenderá no uso real.
Por se tratar de fórmula, ela não consegue trabalhar com centenas de planilhas, ou melhor, até consegue mas vai ser difícil para você fazer manutenção.
Procurei fazer o mais simples possível.

A fórmula que você vai usar na Planilha RESULTADO, célula A2 é: = _T1 & T2 & T2

Onde _T1 é o texto vindo da Planilha1, _T2 da Planilha2 e _T3 da Planilha3.
Quando você tiver que aumentar as planilhas é só incluit _T4, T5, etc...
Mais simples que isso não consegui fazer.

Mas nem tudo são flores!!
Tem um complemento feito pra essa simplicidade acima funcionar.
Você tem que criar uns NOMES. No meu Excel 2013, no menu FÓRMULAS, há o botão GERENCIADOR DE NOMES.
Trata-se de 1 nome fixo (_Q0) e mais 3 nomes por planilha acessada (_Qn / _Dn / _Tn). Como no nosso exemplo temos 3 planilhas ficaram 1+3*3=10 nomes.

Para que tudo funcione não podem haver células em branco na coluna de informações de cada planilha. Uma célula com um espaço em branco também será considerada, embora não se veja o conteúdo. Quando aparecer uma célula vazia no resultado, pode procurar na planilha que tem célula vazia entre preenchidas, ou falsas vazia (com espaço). O deslocamento usando END + Seta pra cima / para baixo pode te ajudar a encontrar as bandidas.

Vou passar a planilha já com os 3 nomes digitados. A partir dai você terá que digitar 3 nomes por planilha.

Considerações finais. Isso também pode ser feito com macro, o que tornaria a manutenção ainda mais simples. Pense a respeito.

Jimmy San Juan

[pjuliocesar]

Legal Jimmy, não conhecia essa ferramenta de gerenciamento de nomes, será útil para mim em outras aplicações.
Agora voltando ao problema inicial, sua solução é excelente mas creio que para a quantidade de planilhas que tenho no meu problema real sua solução não será tão boa.

Atualmente tenho 11 planilhas, o que já retorna 33 novos "Nomes" certo? e creio que esse número pode aumentar para até umas 20 planilhas ==> 60 nomes. Creio que já começa a ficar inviável.
(Cada planilha com +- 200 linhas de dados)

Além disso, apenas para complementar, a planilha real não tem espaços em branco entre as linhas. Apenas possui algumas linhas do início da planilha em branco devido a cabeçalho do documento, mas creio que isso não será um problema.

Outro ponto que não sei se me expressei bem. As planilhas na realidade são arquivos separados, ou seja "Pasta de trabalhos" separadas, isso exige uma alteração na formulação que você fez no gerenciador de nomes certo,??

Por fim, você comentou sobre a manutenção por macro. Poderia explanar um pouco mais como ficaria a solução com macros?
___________________________________________

Eu estava pesquisando na internet e encontrei uma esse vídeo:
https://www.youtube.com/watch?v=NiNFWs-NSwc
Que creio que pode ser utilizado para o meu problema, utilizando o Query excel.
Pelo o que eu entendi, basta acrescentar o código do SQL após o "Union All" alterando o endereço para a nova planilha e automaticamente ele consolida as planilhas.
No entanto, creio que ficaria ainda mais fácil se houvesse uma macro com botão para que esse acréscimo de código fosse automático. Jimmy, vc que entende mais, isso é possível ?

Sei que foge no proposto, mas achei interessante compartilhar com o fórum essa hipótese.

Desde já agradeço sua ajuda! Já me ajudou muito!

[Jimmy]

Julio,

Não acredito que 20, 30 ou 40 planilhas vão inviabilizar a solução via fórmulas. Podem até ser mais do que 40. O chato disso é se frequentemente saem planilhas ou entram novas. Ai vai pesar um pouco na manutenção. Caso as planilhas sejam bem estanques, dá um trabalhinho inicial colocar os nomes, e pronto, é só usar.

Sim, ficou claro que seriam pastas de trabalho, mas é só digitar os nomes delas, suas respectivas planilhas, dentro dos nomes. Se todas elas tiverem o mesmo nome de planilha, fica mais fácil, pois seria o caso de digitar só o nome da pasta.

Com macro VBA fica mais fácil.
Imagine que você tenha uma planilha a mais nessa pasta onde quer o resultado. Essa pasta teria na coluna A, por exemplo, o nome de 30 Pastas de Trabalho e suas planilhas.

A macro varre esses nomes, vai pegando os dados de uma por uma, colocando na coluna A da planilha resultado, e encerra.

Amanhã olho o video que mandou pra ver do que se trata, mas creio que para a sua aplicação não é o caso de usar SQL.

[babdallas]

Eu usaria PowerQuery para isso (se não tiver, usaria VBA). Uma solução com fórmulas não me parece nada adequada.

[Jimmy]

Ola, tenho um problema e gostaria que esse fosse resolvido apenas com funções aninhadas.

Bom dia Julio,

Você acabou não nos contando o porque dessa definição. Há alguma restrição em soluções fora das funções?

[pjuliocesar]

Ola, tenho um problema e gostaria que esse fosse resolvido apenas com funções aninhadas.

Bom dia Julio,

Você acabou não nos contando o porque dessa definição. Há alguma restrição em soluções fora das funções?

Ola Jimmy,
Então, na realidade não há nenhuma restrição em relação ao problema. Eu sugeri por funções por eu estar mais ambientado com elas e por não ter mexido ainda em macros.

Mas como já estou começando a olhar sobre, caso os amigos queiram sugerir um código em macro aqui. Com certeza será bem vindo e me esforçarei para entender!
Tenho conhecimentos em linguagem Python então creio que com esforço posso aprender VBA tambem.

Apenas quero dar algumas outras informações que podem ser importantes:

- No problema real eu tenho mais de uma coluna de dados por planilha
- As planilhas estão deslocadas algumas linhas para baixo, mas todas na mesma posição (No Ex. em anexo deixei todas começando na linha 6)
- A planilha resultado também está deslocada da origem.

Apenas para melhor entendimento, adaptei o modelo fornecido pelo Jimmy para ficar mais próximo do problema real e estou disponibilizando em anexo.

[babdallas]

Qual a versão do seu Excel? Se for 2010 ou superior, farei um exemplo com 3 pastas de trabalho com mesma estrutura de dados, unificando-as em outra pasta de trabalho através do PowerQuery.

[pjuliocesar]

Qual a versão do seu Excel? Se for 2010 ou superior, farei um exemplo com 3 pastas de trabalho com mesma estrutura de dados, unificando-as em outra pasta de trabalho através do PowerQuery.

Ola babdallas, excelente!!

Então, eu em particular tenho a última versão do excel. No entanto, mais pessoas vão utilizar a planilha e a maioria com Excel 2007.

Visto isso, os amigos poderiam então ajudar em uma macro que seja de fácil utilização, principalmente no momento de adicionar uma nova planilha?

Estou aberto a sugestões.

[Jimmy]

Julio,

Seque arquivo RAR com 6 planilhas, uma Concentradora que tem a macro, 4 da dados (pequenas) e uma última com dados também, mas coloquei 6000 linhas pra dar volume.

A macro coloca fórmulas na Concentradora de forma a pegar dados das demais pastas. A cada execução, se ela percebe que a quantidade de dados de cada pasta não mudou desde a última execução, ela não regera as fórmulas. Se mudou de uma delas, ela e todas as que sem a seguir são refeitas. Isso foi feito por motivos de performance, e ainda dá pra melhorar tentando fazer com que regere apenas a que mudou, e não todas as demais. Vamos começar assim e depois vamos polindo. Não devemos cair de cabeça na macro porque pode ser que a solução do babdallas com PowerQuery apresente melhor resultado.

Para fazer testes regerando todas as pastas, basta apagar a célula D4 da planilha de Arquivos.

Por enquanto execute manualmente para ir testando. Futuramente podemos incluir uma execução automática e periódica, digamos a cada 3 segundos, pra verificar se algo mudou e regerar. O tempo que ela demora só pra verificar, sem regerar nenhum arquivo, é de 0,03 segundos, então não vai atrapalhar a sua operação normal.

Faça testes e vamos dando polimento aos poucos.

Jimmy San Juan

[pjuliocesar]

Julio,

Seque arquivo RAR com 6 planilhas, uma Concentradora que tem a macro, 4 da dados (pequenas) e uma última com dados também, mas coloquei 6000 linhas pra dar volume.

A macro coloca fórmulas na Concentradora de forma a pegar dados das demais pastas. A cada execução, se ela percebe que a quantidade de dados de cada pasta não mudou desde a última execução, ela não regera as fórmulas. Se mudou de uma delas, ela e todas as que sem a seguir são refeitas. Isso foi feito por motivos de performance, e ainda dá pra melhorar tentando fazer com que regere apenas a que mudou, e não todas as demais. Vamos começar assim e depois vamos polindo. Não devemos cair de cabeça na macro porque pode ser que a solução do babdallas com PowerQuery apresente melhor resultado.

Para fazer testes regerando todas as pastas, basta apagar a célula D4 da planilha de Arquivos.

Por enquanto execute manualmente para ir testando. Futuramente podemos incluir uma execução automática e periódica, digamos a cada 3 segundos, pra verificar se algo mudou e regerar. O tempo que ela demora só pra verificar, sem regerar nenhum arquivo, é de 0,03 segundos, então não vai atrapalhar a sua operação normal.

Faça testes e vamos dando polimento aos poucos.

Jimmy San Juan

Jimmy, sensacional o seu trabalho!!
Pelo pouco que li na internet também creio que o PowerQuery pode ser uma alternativa melhor. No entanto, como disse anteriormente, os computadores que executarão as planilhas possuem Excel 2007 e já verifiquei que não possuem o módulo do querry instalado. Então será melhor entrar nas macros mesmo.

Sobre o código que você fez, abri aqui e já comecei a fazer os primeiros testes com o problema simplificado.

Primeiramente agradeço por comentar todo o código! Para mim que estou iniciando o contato com as macros, os comentários serão de grande aproveito! Inclusive, gostaria de estudar melhor, tipo começando la no básico de código "like print Hello World" e seguindo. Você indica algum material gratuito na internet?

Voltando ao problema...
Amanhã vou testar seu código para o caso real, pela olhada por cima que dei, creio que talvez terei problemas porque no problema real as planilhas que serão unidas possuem diversas colunas com dados, mas só algumas em específico que deverão ser copiadas. Ex: "AB, AC, AD e AF" e creio que pelo seu código, está fixo para as 3 primeiras colunas certo?

Sobre sua ideia de verificação a cada 3 segundos, creio que para o meu problema não será necessário. Uma verificação "Execução" ao iniciar a planilha já será suficiente.

Enfim, vamos trabalhando nisso então. Mas desde já, agradeço muito sua ajuda!

[Jimmy]

Julio,

Alterei o código para ler colunas "salteadas" (estipulei a B, E e I) apenas para teste. Segue novo RAR.

Para definir as colunas altere a linha
Cols = Array("dummy", "B", "E", "I")
Inclua mais quantas quiser. Não é preciso alterar mais nenhum lugar.

Se você vai executar a macro apenas no OPEN da planilha, não tem sentido ficar economizando tempo, executando uns arquivos e outros não. Se concordar comigo, tiro fora esse controle. A macro quando roda completa (6000 linhas) demora 0,25 segundos no meu PC (I3 com WIn 7). Faria sentido se fosse ser executada a cada 3 segundos, por exemplo.

Aguardo o resultado dos testes.

Jimmy San Juan

[pjuliocesar]

Jimmy,

É isso mesmo, executarei a macro apenas no OPEN da planilha, então pode alterar o código para que seja automático a verificação quando abrir. A verificação a cada 3 segundos não será necessária.

Sobre as alterações que você já fez no código, vou verificar e posteriormente posto aqui.

[pjuliocesar]

Jimmy,

Copiei o código e testei no problema real e preciso de algumas modificações.

1) Na planilha "Arquivos" deve ter duas colunas a mais, uma com o nome do arquivo "Ex: Arquivo1, Arquivo2 e etc" (Que é diferente do nome da pasta de trabalho "Plan Primavera.xlsx, Plan Inverno.xlsx"
E outra coluna com um número (1,2,3 e etc).

2)O Nome do arquivo expresso na coluna "Arquivo" da planilha "Arquivos" deve ser chamado na planilha Resultado na primeira coluna, mostrando que aqueles dados são referentes aquele nome. (Deixei em anexo planilha exemplo)

3)Outro ponto que preciso alterar é em relação a contagem de linhas das planilhas. No problema real, existem linhas com valor igual a ="". Essas lindas estão ordenadas, ou seja, nas primeiras linhas eu tenho valores diferentes de "", e em uma determinada linha começa "" em todas as linhas seguintes (No arquivo em anexo coloquei uma aba "REAL" demonstrando isso).
Dessa forma, dei uma pesquisada e creio que no lugar do CountA no código, deve-se usar o countIF, no entanto tentei trocar e deu erro.

Assim, se puder alterar esses pontos, agradeço.

[Jimmy]

Olá Julio,

Os itens 1 e 2 já estão ok. Segue novo .RAR.

O item 3 complicou um pouco. Se abrir, olhar e fechar cada arquivo, fica fácil, mas da forma que estavamos fazendo, tudo sem abrir o arquivo, complica.

Pergunta: em vez de contornar o problema, não podemos eliminá-lo, fazendo uma limpeza nas outras pastas de trabalho, e tirando o =""?

Jimmy San Juan

[pjuliocesar]

Olá Julio,

Os itens 1 e 2 já estão ok. Segue novo .RAR.

O item 3 complicou um pouco. Se abrir, olhar e fechar cada arquivo, fica fácil, mas da forma que estavamos fazendo, tudo sem abrir o arquivo, complica.

Pergunta: em vez de contornar o problema, não podemos eliminá-lo, fazendo uma limpeza nas outras pastas de trabalho, e tirando o =""?

Jimmy San Juan

Oi Jimmy,

Sobre o item 3, acho que será mais fácil fazermos um teste no valor da celula antes de copiar as celulas tipo com "Countif" do que limpar as outras planilhas.
Isso porque, na realidade, eu tenho um problema que tem "x" linhas de dados, ou seja uma incógnita. Mas sei que esse valor não ultrapassa 400 linhas. Então o que fiz, foi montar uma planilha, já preenchendo as 400 linhas com um código que le uma outra planilha de dados e reordenada esses dados, preenchendo as primeiras linhas com os dados cheios e as demais que foram lidas e estavam em branco retorna ="".
(Ou seja, é uma maquiagem que da certo).

A não ser que montássemos uma outra macro para acertar essa planilha inicial tambem. Vou fazer uma planilha ex aqui e postar.

[pjuliocesar]

Jimmy,

As planilhas teste que vc fez: Plan Inverno, Plan Outubro são como a planilha "BaseOrdenada" do arquivo em anexo.

- Essa planilha é formada a partir de uma outra planilha "BaseDesordenada".
- O Usuário vai preencher os Itens: "Dados 1 e Dados2" na planilha de BaseDesordenada. Dessa forma, meu código atual verifica as colunas de "Dados1" do setor 1, setor 2 e setor 3 e quando esse valor está preenchido, com valores tipo A, D ou G ele pega esse valor e preenche nas primeiras linhas do BaseOrdenada, como segue no exemplo.
- Ao preencher, o código atual (Que está tudo por fórmula), também pega a informação do Conjunto pertencente, a informação "inf1, inf2 e etc", o setor que pertence e também completa com o "Dado 2"
- Ficando então a planilha de BaseOrdenada com colunas: Conjuntos, Informação, Setor, Dado1 e Dado2
- Percebe-se que o Dado3 não é puxado para a Base Ordenada "Esse não importa"

- E percebe-se que para as demais linhas da coluna E, I, M da planilha "BaseDesordenada" que não possuem valor preenchido, para esses casos é retornado o valor ="" na planilha de Base ordenada.

- Obs: O número de conjuntos é variável, podendo chegar até 15. O Número de setores é fixado em 3, e o número de infs é fixado em 4.

Creio que é isso Jimmy,
Perceba que é bastante coisa.

A posição das colunas e células da planilha em anexo não é a mesma do problema real, mas a "cara" é bem parecida.
Caso você considere apropriado debruçarmos nesse problema apenas para tirar o ="".
Posso passar para você uma planilha já com a posição das colunas e dados corretos, pois creio que caso não estejam, precisará alterar muita coisa nas referencias.

Ahh, outra coisa, para esse problema é preferível o uso de fórmulas. A não ser que tenha como ocultar aquela msg chata de segurança do excel quando usa-se macros.

Att,

[pjuliocesar]

Jimmy,

Só para avisar que consegui arrumar o problema da macro que estava contando as celulas com =""

Apenas troquei a função countA por count que deu certo. Agora vou verificar os proximos pontos aqui.
Então não precisamos alterar a planilha base que mandei na msg anterior blz ??

Obrigado.

[Jimmy]

Olá Julio,

Como você fez para funcionar com o CONT.NÚM, se as colunas não tem números, e sim textos? A não ser que os dados reais são um pouco diferentes dos simulados que você colocou aqui.

Se funcionou com dados de texto, peço que coloque a planilha aqui pra eu dar uma olhada.

Eu consegui fazer funcionar mesmo para dados de texto, mas tive que abrir cada planilha de dados, não fazer nada e fechar, porque tem CONT que, diferentemente do CONT.VALORES, só funciona quando a planilha pesquisada está aberta.

Agora só falta adequar as colunas para as reais. Se tiver algum problema com isso me avise.

Mudando de assunto, já que quer aprender VBA, dá uma lida nestas dicas de macros para eventos.
http://gurudoexcel.com/forum/viewtopic. ... 015#p40015

Jimmy San Juan

[pjuliocesar]

Olá Julio,

Como você fez para funcionar com o CONT.NÚM, se as colunas não tem números, e sim textos? A não ser que os dados reais são um pouco diferentes dos simulados que você colocou aqui.

Se funcionou com dados de texto, peço que coloque a planilha aqui pra eu dar uma olhada.

Eu consegui fazer funcionar mesmo para dados de texto, mas tive que abrir cada planilha de dados, não fazer nada e fechar, porque tem CONT que, diferentemente do CONT.VALORES, só funciona quando a planilha pesquisada está aberta.

Agora só falta adequar as colunas para as reais. Se tiver algum problema com isso me avise.

Mudando de assunto, já que quer aprender VBA, dá uma lida nestas dicas de macros para eventos.
http://gurudoexcel.com/forum/viewtopic. ... 015#p40015

Jimmy San Juan

Ola Jimmy ,

Então, de fato na realidade os dados reais da primeira coluna e algumas outras são números e por isso que deu certo. Desculpe não ter avisado antes, mas por causa da minha pouco experiência com macros não pensei que isso fosse importante naquele momento.

Dessa forma, consegui adequar as colunas para o problema real e o programa rodou perfeitamente.
O problema desse tópico foi solucionado, vou fechar!

Agora tenho outras questões para resolver da planilha, mas creio que é mais adequado em outro tópico.
Dessa forma, só tenho a te agradecer Jimmy pela ajuda!!!

Sobre o link, vou dar uma olhada!

Att Julio