Fórmulas e formatações condicionais "infinitas"

Nortonap · 30 de dezembro de 2016

Meus amigos, bom dia.

Primeiramente, gostaria de agradecer ao @Patropi, @CasaDoHardware e ao @DJunqueira, que sabem muito têm me ajudado muito e sempre que preciso.

Bom, me considero um curioso no Excel e me esforço muito para aprender sempre coisas novas. Vejo que tudo tem uma solução, por vários caminhos. Isso é empolgante, mas, enfim, o que estou procurando é uma solução para os "problemas" que listo abaixo: criei uma planilha, anexa, que utilizei as seguintes ideias:

* Na aba Procedimentos, adicionei 2 (duas) FORMATAÇÕES CONDICIONAIS "infinitas" (=$A$5:$H$1048576): 1ª) Em todas as colunas, desde que preenchidas, o Excel adiciona as bordas da célula; 2ª) Coluna G: valores maiores que determinados valores, células vermelhas ou amarelas;

* VBA de classificação da tabela relativos às linhas preenchidas das colunas G e H (o VBA seleciona a última linha preenchida e classifica em ordem crescente e decrescente) (OBS.: lembrando que estou querendo aprender o que o @DJunqueira no post abaixo).

Pois bem, apesar de conseguir fazer o que disse acima, esbarro nos seguintes "problemas":

1º) Nas colunas B, D e E, existem VALIDAÇÕES DE DADOS, e, nas colunas G e H, FÓRMULAS, que eu também gostaria que fossem repetidas "infinitamente" para as demais linhas das respectivas colunas.

Quais os problemas?

a) Se eu seleciono até a última linha de cada coluna (control + shift + seta para baixo) e estendo as validações e fórmulas, a planilha começa a travar, aparentemente sobrecarregada com tantas validações e fórmulas. Um simples DESPROTEGER PLANILHA demora bastante para ser executado.

b- Estendendo as fórmulas e validações, as demais células passam a "existir" para o Excel, embora ainda sejam consideradas vazias. Com "existir", quero dizer que embora isso não afete a formatação condicional para células não vazias, quando vou tentar imprimir o documento, o Excel informa que existem um zilhão de páginas para imprimir.

Assim, apesar de não fazer ideia de como resolver esse problema com os meus conhecimentos atuais, penso que a solução seria que o Excel adicionasse automaticamente a fórmula e as validações à linha posterior à última preenchida. Ex.: A linha 10 está preenchida. A 11 já deve estar preparada para receber os dados. Preenchi a 11, a 12 também já deve ficar preparada para preenchimento, e assim por diante. Assim, a planilha não seria sobrecarregada com tantas informações desnecessárias.

2) Gostaria de estender, também, uma FORMATAÇÃO CONDICIONAL "infinita" para a coluna H, nos seguintes moldes: que cada linha fosse formatada com as cores amarela ou vermelha, conforme algumas condições fossem verificadas, como, por exemplo: O PA tem duração máxima de 90 dias, podendo ser prorrogado por mais 90. A Coluna D informa a classe atual e uma outra coluna que vou criar, Coluna E, por exemplo, vai conter uma data de conversão. Assim, acaso o processo seja um PA e, da data de conversão (Coluna E) até a data atual, tiverem passados de 90 a 100 dias, ficaria amarelo. Passados mais de 100 dias, vermelho. E assim farei com os outros procedimentos (NF, IC, PPIC), criando outras FORMATAÇÕES CONDICIONAIS para cada um.

Qual o problema? Se eu fizer uma formatação dessas (eu acho que consigo) para a primeira linha (5), consigo estender até a última com o COLAR ESPECIAL, de tal modo que cada linha faça seu cálculo com seus próprios dados (da mesma linha)?

Resolvendo isso, e aprendendo o que o @DJunqueira fez no outro post, minha planilha fica perfeita para o que quero: simplicidade, organização e rapidez na obtenção de dados.

PJDE.zip

DJunqueira · 30 de dezembro de 2016

@Nortonap você está no ponto p/ aprender o porque tabelas nomeadas são a melhor opção.

Justamente como você notou, ao estender formatação e validação até o infinito a planilha passa a ter um desempenho ruim e o arquivo cresce desproporcionalmente de tamanho. P/ evitar isso use sempre tabelas nomeadas, pois a cada linha acrescentada formatação, validação e fórmulas são copiadas p/ a nova linha.

Montei novamente sua planilha usando tabelas nomeadas, só q agora deixei os botões de filtragem q não serão impressos, com isso você pode deixar de usar os botões com macros p/ filtragem. Sua pequena planilha ficará infinitamente rápida e extraordinariamente leve.

PJDE.xlsx

Nortonap · 2 de janeiro de 2017

@DJunqueira, meu amigo, muito obrigado pelo esforço em me ensinar. Estou mexendo aqui muito, pesquisando e lendo sobre os assuntos que você mencionou. Para ser sincero, ainda não consegui entender tudo e nem reproduzir o que você fez, mas estou tentando. Sei que no início tudo é difícil, como o VBA, que pelo menos já sei o que é e como gravar um macro, por exemplo.

Estou desenvolvendo uma planilha para me ajudar nos procedimentos na Promotoria que trabalho e, nos moldes que você fez naquela planilha Procuradores.xslx, está mais do que excelente. Espero chegar lá.

"Apenas" preciso que minha planilha faça duas coisas:

1) isso aqui que você disse: "(...) P/ evitar isso use sempre tabelas nomeadas, pois a cada linha acrescentada formatação, validação e fórmulas são copiadas p/ a nova linha (...)";

2) Aquele botão de filtro maravilhoso...

De qualquer forma, muito obrigado.

DJunqueira · 2 de janeiro de 2017

S/ Segmentação de dados:

https://support.office.com/pt-pt/article/Utilizar-a-segmenta%C3%A7%C3%A3o-de-dados-para-filtrar-dados-de-tabelas-din%C3%A2micas-249f966b-a9d5-4b0f-b31a-12651785d29d?ui=pt-PT&rs=pt-PT&ad=PT&fromAR=1

Nortonap · 2 de janeiro de 2017

@DJunqueira, meu grande amigo, por incrível que pareça, depois de ler a respeito e ficar tentando decifrar sua planilha, consegui reproduzir na minha as seguintes funcionalidades (pelo menos eu acho que são esses os nomes):

1) tabela nomeada;

2) tabela dinâmica;

3) segmentação de dados;

3) filtro;

Porém, ainda não consegui encontrar solução para o "problema" que dá título a este tópico: Como fazer para em relação às fórmulas e formatações condicionais "INFINITAS"? Talvez eu não tenha explicado direitinho o que eu quero... Vou tentar ser mais claro...

Bom, a planilha que estou fazendo (cópia anexa) contém aquelas funcionalidades que listei acima e mais algumas outras.. Nas colunas I e J, por exemplo, existe uma fórmula simples para contabilizar os dias de VENCIMENTO e o tempo de TRAMITAÇÃO do procedimento administrativo.

Como fazer para que essas fórmulas sejam "copiadas" para a próxima célula, automaticamente, sempre que eu preencher a última linha em branco? Exemplificando: na tabela anexa, a última linha preenchida é a 24. Gostaria que a linha 25 já contivesse as FÓRMULAS que vou usar e, ao preenchê-la, que a linha 26 já recebesse também as FÓRMULAS, e assim por diante.

Vou acrescentar, também, FORMATAÇÕES CONDICIONAIS para cada linha. Acredito que vou colar a formatação condicional até a última célula do Excel. Será que isso vai funcionar e não vai deixar a planilha pesada e lenta também?

porque pensei em fazer assim? Explico: quando copio a fórmula até a última célula (apertando control + seta para baixo), noto que a planilha fica sobrecarregada e começa a travar, além de que acabo preenchendo células que nem sei se um dia serão usadas. Quando vou imprimir o documento, por exemplo, são listada milhares de páginas que estão preenchidas, na verdade, com nenhuma informação, mas, apenas, fórmulas que talvez um dia serão utilizadas. Assim, o documento fica pesado e lento para execução. Por isso, procuro uma solução para esse problema. As células vão sendo preenchidas diariamente, mas, de certo, nunca a planilha chegará a milhares de páginas. Porém, não queria também ter que sempre estar editando e acrescentando mais fórmulas. Existe alguma possibilidade de isso ser adicionado automaticamente, como explicado acima?

PJDE.zip

DJunqueira · 3 de janeiro de 2017

@Nortonap uma das funcionalidades da tabela nomeada é justamente copiar formula e formatações p/ toda linha acrescentada, portanto usando tabelas nomeadas esse problema não existe mais!

De forma simples esse problema já foi sanado.

Caso você queira ver com seus próprios olhos experimente acrescentar mais uma linha apertando TAB na última célula do canto inferior direito ou acrescentando dados na linha imediatamente abaixo da tabela.

Obs.: Não utilizei tabela dinâmica na sua planilha.

Para você entender mais claramente acrescentei uma linha na tabela da aba Procedimentos apertando TAB na planilha anexa. Repare q fórmulas e formatação estão lá e acompanharão qq quantidade de linhas q você venha a inserir.

PJDE.xlsx

Nortonap · 3 de janeiro de 2017

@DJunqueira, meu amigo, muito obrigado. Consegui como você falou, utilizando o TAB. Porém, surgiu apenas uma indagação: minha planilha é bloqueada. Quando apertei o tab, com a proteção de células ativa, o Excel disse que não era possível inserir a linha. Como fazer para conciliar a PROTEÇÃO DE CÉLULAS e esse recurso da TABELA NOMEADA de extensão de fórmulas?

DJunqueira · 3 de janeiro de 2017

Como você pode ver no endereço abaixo isso é um problema de longa data da M$ e só dá p/ contornar com VBA.

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other/how-to-protect-an-excel-2010-table-allow-to-add/ee40ee81-8419-449c-b2c0-80e4f60b2e5d?page=2

Nortonap · 5 de janeiro de 2017

@DJunqueira, meu amigo, você é demais. Ainda não consegui fazer esse VBA, mas já é uma super luz. Sei que insistindo eu consigo.

Tenho uma última dúvida/necessidade (espero eu): como já disse, estou utilizando a tabela nomeada com sucesso, acrescentando a nova linha com o TAB. Até aí tudo bem. Porém, eu adicionei uma formatação condicional com fórmula e não consegui estender a todas as linhas, mesmo com a tabela nomeada. A fórmula é a seguinte:

=E($D36="NF"; $F36<>0)

Eu gostaria que uma determinada células de todas as linhas contivessem essa fórmula, alterando, apenas, o "36" pelo número da linha de verificação.

Alguma luz?

DJunqueira · 6 de janeiro de 2017

Testa isso numa coluna distante da tabela p/ não ser incorporada, se der o retorno desejado você estará no caminho certo, mas p/ ajudar mais anexe a planilha.

diego_janjao · 6 de janeiro de 2017

9 horas atrás, Nortonap disse:

@DJunqueira, meu amigo, você é demais. Ainda não consegui fazer esse VBA, mas já é uma super luz. Sei que insistindo eu consigo.

Tenho uma última dúvida/necessidade (espero eu): como já disse, estou utilizando a tabela nomeada com sucesso, acrescentando a nova linha com o TAB. Até aí tudo bem. Porém, eu adicionei uma formatação condicional com fórmula e não consegui estender a todas as linhas, mesmo com a tabela nomeada. A fórmula é a seguinte:

=E($D36="NF"; $F36<>0)

Eu gostaria que uma determinada células de todas as linhas contivessem essa fórmula, alterando, apenas, o "36" pelo número da linha de verificação.

Alguma luz?