Excel Pesquisa com até 5 critérios

[diego_janjao]

Pessoal, bom dia.

 

Eu tenho essa planilha de recebimento o qual fazem os lançamentos diários de chegada de insumos, e nela há uma sheet de pesquisa que ao escolher o Fornecedor, ele lista os recebimentos desse fornecedor, e caso escolha um dos códigos dos insumos que este fornecedor tem, ele lista os itens de acordo com o fornecedor. E assim segue se caso preencherem os campos Lote e Data de Recebimento.

 

O problema é que isso deixa meu arquivo muito grande e eu gostaria de saber se há um modo de fazer sem que precise ser desse jeito.

 

O arquivo com a Pesquisa = 1,40 Mb

O arquivo sem a Pesquisa = 371 Kb

 

Segue o arquivo: rec2019.zip

 

[Edson Luiz Branco]

10 horas atrás, diego_janjao disse:

O arquivo com a Pesquisa = 1,40 Mb

O arquivo sem a Pesquisa = 371 Kb

 

Diego, como você chegou a essa conclusão? Fiz um teste aqui e o original está com 1444 KB. Eliminando a planilha "Pesquisa" e salvando o arquivo reduz para 1394KB, ou seja, menos de 3,5% de redução, portanto talvez não seja ela a responsável pelo gigantismo.

Um dos maiores vilões em termos de tamanho de arquivo para o Excel é sabidamente o excesso de formatações. Deixa tudo mais bonitinho, mas tem seu custo.

 

Outro é o excesso de fórmulas repetidas/matriciais. O tamanho das fórmulas (e a quantidade de recálculo também) pode ser bastante reduzido se você utilizar intervalos nomeados ao invés de referências e também fórmulas nomeadas.

 

Ou, polêmicas à parte, utilizar VBA ao invés de fórmulas (a quantidade de linhas de código geralmente é muito pequena em relação à quantidade de fórmulas necessárias à planilha).

[diego_janjao]

@Edson Luiz Branco Bom dia, Edson. 

 

Realmente, se você simplesmente apagar a planilha Pesquisa diminui muito pouco. Mas essa planilha funciona por causa da coluna AB da Sheet Recebimento 2019, que vai do AB7:AB9000 (é uma média de recebimentos que temos por ano). E o cálculo dela é muito grande, como mencionado, pois tem que considerar se uma célula está vazia e outra preenchida, se a outra está preenchida e a outra vazia e assim vai. Eliminando-a chegamos aos 400Kb.

 

Sobre as fórmulas, eu já tentei reduzi-las ao máximo com o que eu sei de Excel. Acredito que a melhor opção aqui seria realmente por VBA (acho que ListView, não sei) mas eu tô tentando aprender ainda. Aí coloquei no fórum pra ver se alguém me apontava uma outra saída.

 

de antemão, muito obrigado pelas considerações.

[Márcio Rodrigues]

Olá diego_janjao

 

Sugestão:

Os dados incluídos na plan Registro de Recebimento 2019 poderiam ser via formulário vba, assim você poderia excluir todas as fórmulas desta planilha.

Explique melhor as entradas e saídas do que você precisa para que possamos te ajudá-lo.

 

Té +

Márcio

[diego_janjao]

Então, @Márcio Rodrigues . Eu já tentei colocar o cadastro via VBA por form (clica em no botão cadastrar, abre o form, preenche e salva) mas o pessoal que trabalha com esse Registro de Recebimento (eu sou do departamento de Qualidade) não aceitaram e querem na forma que está hoje (linha a linha porque eles nem sempre finalizam o recebimento de imediato, aí a linha do recebimento fica até 5h em aberto até finalizar o descarregamento do insumo). 

Pra mim, por VBA seria bem melhor uma vez que são vários computadores usando a mesma planilha (aí eles tem que esperar um usar pra depois o outro usar [facepalm.jpg]).

 

Enfim, do jeito que eles querem, mesmo mostrando todas as vantagens do outro formato, é na forma que está hoje.

O que eu gostaria era que a sheet(Pesquisar) fosse algum tipo de listview, por exemplo.

 

[Márcio Rodrigues]

Olá diego_janjao

 

Dúvida:

Para que serve na sua pesquisa o campo LOTE, DESDE e SÓ COM ESTOQUE.

FORN e CÓD eu entendi.

 

Té +

Márcio

 

[diego_janjao]

@Márcio Rodrigues basicamente é um filtro essa aba.

 

O campo lote é para mostrar todos os produtos que tenham o lote que colocar. 

 

O desde é uma data, ou seja, a partir da data x mostrar tais itens.

 

O só com estoque é para mostrar itens que ainda há no estoque, que não foram consumidos.

 

E a fórmula aí fica muito grande (na coluna AB da Sheet Registro de Recebimento) porque ela funciona com todos os campos preenchidos ou apenas alguns.

[Márcio Rodrigues]

Olá diego_janjao

 

Mas a busca pode ser só por lote?

Ou o lote esta associado a um produto que esta associado a um fornecedor?

Como defino se há estoque?

A formula grande está na AC... Qual o objetivo dela?

 

Té +

Márcio

[diego_janjao]

@Márcio Rodrigues

 

Mas a busca pode ser só por lote?

As buscas geralmente são com os seguintes critérios:

 

Fornecedor 

Fornecedor + Código

Fornecedor + Código + Desde
Fornecedor + Código + Lote
Fornecedor + Código + Lote + Desde
Fornecedor + Desde

E por isso a minha fórmula Se teve que abranger todas essas possibilidades.

 

Ou o lote esta associado a um produto que esta associado a um fornecedor?

O lote é sempre associado a um código que é associado a um produto, mas pode haver o mesmo lote para produtos diferentes, por exemplo, eu fabrico pão e defino que o lote dele é N1, só que você tem uma empresa e na sua, você faz café e também pode colocar o lote de N1, então geralmente a pesquisa é sempre feita com Código + Lote

 

Como defino se há estoque?

Na Sheet Registro de Recebimento há um botão azul BAIXA, lá é onde o pessoal dará baixa no estoque. Assim que eles preenchem o form e clicam em enviar, a quantidade é colocada na coluna AD7:AD9006. Com esse valor aí, eu simplesmente faço M - AD (M é onde ficam as quantidades recebidas). Então quando M for igual a AD, o estoque será zero.

 

A fórmula grande está na AC... Qual o objetivo dela?

Essa coluna é que faz a sheet Pesquisa funcionar. Se o campo FORN da sheet Pesquisa (e as demais estiverem vazias) for igual a célula D da sheet Recebimento ele mantém o número de análise visível para fazer o índice x corresp na Sheet Pesquisa.

 

Foi a única maneira que consegui pensar em fazer isso funcionar. Caso tenha outras sugestões, estou aberto.