Este fórum está sendo desativado

[Micsterminador]

Bom dia Pessoal,

Estou procurando desenvolver uma macro para resolver a seguinte situação:
No trabalho precisamos realizar uma Seleção Amostral de Forma Recorrente. Ela é feita da seguinte forma: Preciso Selecionar o lançamento com o maior valor de cada Categoria Contábil (coluna D), e após isso, os maiores lançamentos sem considerar as Categorias Contábeis, de forma a fechar 20% do total. Gostaria de desenvolver uma macro que faça este processo de forma automática. Alguma dica de por onde começar? Anexo segue a planilha "Seleção Amostral", para que possa ser desenvolvida a Macro e planilha "Resultado", referente ao resultado esperado que a macro efetue.

Agradeço a atenção, e qualquer dúvida estou à disposição.

[Jimmy]

Olá,

Veja se a planilha abaixo te atende.

Jimmy San Juan

[Micsterminador]

Bom dia Jimmy,

A princípio sim. Vou testá-la ao longo da semana com algumas variáveis que aparecem neste trabalho, e volto para uma resposta mais concreta.
Obrigado pela sua ajuda. Abraços.

[Jimmy]

Bom dia Mister,

O comentário que eu acrescentaria é que é possível haver mais de um resultado para a seleção, pois pode haver um mesmo valor repetidos em categorias contábeis diferentes, e este valor ser um dos 20% maiores . Nesse caso, pode ser que apenas uma delas complete os 20%, e a outra fique de fora, mas qualquer uma que entrar na lista, atende ao problema.

Outro comentário é que os 20%, após calculados sobre a quantidade de lançamentos, foi arredondado PARA CIMA, para definir a quantidade de amostras. Esse critério pode ser mudado para o arredondamento simples, o truncamento (pegar só a parte inteira do valor), ou outro critério a ser definido.

Mais um comentário. Se houver poucos lançamentos, e muitas categorias, é possível que ao pegar o maior valor de cada categoria, o resultado seja maior que os 20%. Nesse caso a macro não acrescenta nenhum outro lançamento. Há a alternativa de respeitar os 20% acima de tudo, mesmo que algumas categorias fiquem de foram.

Jimmy San Juan

[Micsterminador]

Bom dia,

Sobre o 1º Comentário: Exatamente, quando existem dois ou mais valores iguais que sejam o maior valor de uma categoria contábil, a seleção entre eles pode ser feita de forma aleatória, e os demais podem ficar fora da amostra.

Sobre o 2º Comentário: O que preciso é isso mesmo, é melhor pegar 21% do que 19,99%.

Sobre o 3º Comentário: Certo. Em alguns casos teremos 8 categorias contábeis e apenas um lançamento para cada categoria, neste caso o ideal é que sejam selecionados 100% dos lançamentos.

Seguem algumas observações que talvez possam ser ajustadas:

1 - Testamos a macro com outras quantidades de lançamentos, e em alguns casos ela selecionou 100% dos lançamentos de forma errônea. Não sei se ela está calculando pela quantidade de linhas, mas o ideal seria que o range fosse calculado pela informação da célula E9.

2 - Após a seleção, a macro filtra os lançamentos selecionados na Coluna E. Seria possível adicionar borda inferior expessa na última linha?

3 - Após a macro, as células M81 e M82 ficaram com preenchimento de cor. Seria possível ajustar para que isso não aconteça? Esta seleção é encaminhada para clientes, portanto a formatação é muito importante.

4 - Seria possível apagar automaticamente as informações das colunas M e N quando o valor total de alguma categoria contábil estiver zerado?

Novamente agradeço pela disposição. Abs,

[Jimmy]

Olá,

Acho que tudo isso pode ser feito. Vou verificar com mais profundidade.

Gostaria que me enviasse um exemplo de quando a macro pega erroneamente 100% dos lançamentos. Só é possível pegar o erro vendo-o acontecer.

As células M81 e M82 devem ter cor antes de acionar a amostragem, mas não depois. É isso, ou podemos tirar a cor delas na planilha antes da execução?

Há linhas ocultas (as não filtradas) que permitem desfazer a amostragem a qualquer hora. Se apagarmos as linhas totalizadoras das categorias zeradas, isso não poderá ser desfeito. Se formos seguir esse caminho, podemos apagar as linhas não selecionadas para a amostragem, em vez de oculta-las por filtro.

Aguardo seus comentários.

Jimmy San Juan

[Micsterminador]

Bom dia,

Segue anexo um exemplo como o sr. pediu.

Quanto à cor das células M81 e M82, realmente estavam pintadas no primeiro modelo que lhe enviei, não tendo nenhuma relação com a execução da macro. No modelo anexo já tirei o preenchimento desnecessário.

O ideal seria apagar as linhas totalizadoras das categorias zeradas, mas deixando as linhas não selecionadas para amostragem ocultas pelo filtro. É possível?

Outra coisa que não é tão importante, mas seria bom, seria apagar as linhas que não possuem informações dentro da margem vermelho e cinza, em vez de simplesmente filtrá-las.

Novamente, agradeço sua ajuda, e aguardo retorno.
Abs,

[Jimmy]

Bom dia!

Já corrigi a planilha, e não está mais selecionando 100% das linhas. Segue abaixo.

Seleção SEM defeito.xlsm

O ideal seria apagar as linhas totalizadoras das categorias zeradas, mas deixando as linhas não selecionadas para amostragem ocultas pelo filtro. É possível?

Veja que você tem 2 tabelas: a de lançamentos e a de totais. Elas ocupam as mesmas linhas. Se reparar, na planilha que você me mandou neste seu último post, irá notar que após a amostragem, quando as linhas não amostradas são ocultas, ocultam também as linhas da tabela de totais, pois a filtragem culta a linha TODA.

O ideal seria não ter as duas tabelas lado a lado, mas se puder ser, teremos que, no final da execução, varrer as 2 tabelas pra limpar as linhas que não interessam. É possível.

Outra coisa que não é tão importante, mas seria bom, seria apagar as linhas que não possuem informações dentro da margem vermelho e cinza, em vez de simplesmente filtrá-las.

Isso pode ser feito junto com a limpeza que mencionei acima.

Esqueci de te dizer... eu criei 3 nomes na planilha: CatCont, SelAmos e ValTota. A macro necessita deles pra identificar onde estão as colunas de Categorias, Amostrados e Valor total, e não devem ser apagados.

Aguardo seu retorno quanto à possibilidade ou não de mudar o leiaute, e tirar a tabela de totais do lado da de lançamentos.

Jimmy

[Micsterminador]

Bom dia,

Quanto ao problema dos 100% realmente está ok. Estarei testando a planilha ao longo da semana e te aviso que ocorrer algo de errado.

Para a questão das duas tabelas, imaginei o seguinte: Anexei um nova versão da planilha, à qual alterei um pouco o layout. Ambas as tabelas continuam lado a lado. Após a macro selecionar os lançamentos, ela poderia classificar a coluna D (categoria contábil) de A a Z, e depois a coluna E (Seleção Amostral) também de A a Z. Apenas após este procedimento a macro ocultaria os lançamentos não selecionados, os quais estarão na parte de baixo da tabela, e desta forma, não alteraria a tabela de totalizadores das categorias contábeis. É assim que fazemos quando realizamos a seleção de forma manual. O que acha da ideia?

Abs,
Michael

[Jimmy]

Olá Michael,

Com a alteração de leiaute que fez basta classificar por Seleção Amostral de forma que todas fiquem na parte de cima, e depois apagar as linhas em branco. Dessa forma, como sempre haverá pelo menos uma amostra de cada Categoria, nunca uma linha em branco estará na mesma linha de uma linha de total.

A questão é que você pediu pra apagar as linhas com total zero. Então, de qualquer forma, a tabela de totais terá que ser varrida pra excluir linhas zeradas.

Se for pra fazer isso, sugiro outra forma. Deixamos a tabela de totais sem linha alguma preenchida (apenas o cabeçalho). No final da amostragem a macro já tem uma relação de Categorias, que ela precisou montar quando pegou um lançamento de cada. Pegamos essa ralação de categorias e preenchemos a tabela de totais com as categorias não zeradas.

Essa segunda forma evita um problema que pode ocorrer se formos pelo caminho de deixar as linhas lá e depois limpar as zeradas.
Imagine que lançe zero, sem querem, num lançamento único de uma categoria. Roda a amostragem e a linha dessa categoria é apagada da tabela de totais. Ai percebe o lançamento zerado, e corrige para o valor correto. ROda novamente a amostragem, mas essa categoria não está mais na tabela de totais. Como perceberá isso?

Pela forma que proponho, não há como ter erro, porque a macro é quem irá montar essa tabela com base nos lançamentos lidos, cada vez que rodar a amostragem.

Pense sobre isso, e diga o que acha melhor.

Jimmy San Juan

[Micsterminador]

Boa tarde Jimmy,

Concordo com sua opinião, ficará melhor desta forma.

Quando você diz "Pegamos essa ralação de categorias e preenchemos a tabela de totais com as categorias não zeradas", está dizendo que a macro fará isso de forma automática, após a seleção dos lançamentos, certo? Se for isso, estou de acordo com o seu modelo e aguardo a planilha atualizada.

Obrigado pela ajuda!
Abs,
Michael Day

[Jimmy]

Sim, é isso Michael.

Vou tentar fazer hoje. Se não conseguir, só segunda.

Jimmy

[Micsterminador]

Certo, fico no aguardo.

abs,
MFD

[Jimmy]

Olá,

Macro alterada, necessitando de testes fortes.

A tabela de totais é apagada a cada execução (exceto cabeçalho) e refeita com base nas categorias dos lançamentos encontrados.

Alterei a fórmula da N12, para apontar erro se houver diferença entre a soma dos valores da coluna de lançamentos e a soma de valores na coluna de totais.

Dê retorno.

Jimmy San Juan

[Micsterminador]

Obrigado.

Realizarei os testes e te retorno.
Abs,

[Micsterminador]

Boa tarde Jimmy,

Seguem algumas considerações referentes ao teste anexo:

1 - A fórmula referente ao valor total dos totalizadores apresenta erro.

2 - Algumas fórmulas de soma dos totalizadores estão trazendo valores incorretos.

3 - Como neste caso temos menos categorias contábeis, ficam sobrando algumas linhas sem informação nos totalizadores (M19, M20, N19, N20). Seria possível que essas linhas sumisses? Acredito que o ideal não seria apagar a linha toda, e sim, alterar a formatação para a célula ficar sem bordas. Continuamente, o ideal seria que a tabela de totalizadores tenha as bordas externas expessas.

Agradeço a ajuda e aguardo retorno.
Abs,

Michael F. Day

[Jimmy]

Ok, vou ver e dou retorno.

[Jimmy]

Ok,

1 - A fórmula referente ao valor total dos totalizadores apresenta erro.

O Valor total foi corrigido. Como a soma era feita de duas formas distintas, para verificação de possíveis erros, as vezes não são iguais, dando uma diferença pra lá da 10a casa decimal. Resolvi arredondando na 5a casa decimal.

2 - Algumas fórmulas de soma dos totalizadores estão trazendo valores incorretos.

Poderia apontar onde isso está acontecendo? Pode ser na planilha que você postou, antes das minhas correções.

3 - Como neste caso temos menos categorias contábeis, ficam sobrando algumas linhas sem informação nos totalizadores (M19, M20, N19, N20). Seria possível que essas linhas sumisses? Acredito que o ideal não seria apagar a linha toda, e sim, alterar a formatação para a célula ficar sem bordas.

Já corrigi as linhas em branco.

3 - Continuamente, o ideal seria que a tabela de totalizadores tenha as bordas externas expessas.

Coloquei borda mais grossa.

Acho que tínhamos mais uma pendência a ser feita, mas não me recordo qual era...
Você pode recordar?

Jimmy San Juan

[Micsterminador]

Bom dia,

"O Valor total foi corrigido. Como a soma era feita de duas formas distintas, para verificação de possíveis erros, as vezes não são iguais, dando uma diferença pra lá da 10a casa decimal. Resolvi arredondando na 5a casa decimal."

Ok.

"Poderia apontar onde isso está acontecendo? Pode ser na planilha que você postou, antes das minhas correções."
Por exemplo, na planilha que você anexou, o totalizador da categoria Transporte traz o valor de R$ 858,19. Entretanto, se você filtrar todos lançamentos desta categoria, o valor correto seria R$ 840,88.

"Já corrigi as linhas em branco."

Ok.

"Coloquei borda mais grossa."
O totalizador da categoria Transporte ficou de fora da borda.

"Acho que tínhamos mais uma pendência a ser feita, mas não me recordo qual era...
Você pode recordar?"

Não estou recordado. Se lembrar mando por aqui.

Abs,
Michael Day

[Jimmy]

Bom dia Michael,

Segue a planilha corrigida. Por favor, teste novamente.

A pendencia que tinha era confirmar com você como deseja classificada a tabela após a amostragem. Da forma que está, a macro deixa a tabela classificada por valor descendente, mas pode ser alterado para Data, ou Categoria, ou outra.

Prefere mudar?

Jimmy San Juan