Guru do Excel e Power BI

Ok, temos um programa antigo de controle de abastecimentos com horímetros. Toda vez que um horímetro menor que o anterior é inserido por acidente, ele cria um valor acima de mil horas pra que o operador do programa "perceba" e então corrija.

Problema é que operador não sabia e ao longo de mais de uma década, temos centenas de horímetros errados inseridos.

Exportei os últimos 10 anos de dados pra uma planilha do Excel. Um total de 25 mil linhas.

Selecionei a coluna com a diferença de horas entre os abastecimentos e usei formatação condicional para que qualquer valor acima de 1000 horas fique em vermelho. Perfeito, funciona.

Entretanto, com 25 mil linhas, até assim é quase impossível achar no meio os horímetros errados.

Percebam que eu não quero somente saber quais os horímetros errados. Eu preciso achar eles NO MEIO das linhas, para poder analisar os horímetros anteriores e posteriores e então modificar com um valor compatível.

Única maneira que entendo para fazer isso é usando a caixa LOCALIZAR/SUBSTITUIR. Entretanto, ela não permite selecionar uma formatação CONDICIONAL para procura.


E infelizmente tb não posso utilizar FÓRMULAS na caixa LOCALIZAR/SUBSTITUIR, e isso seria de grande valia (por exemplo, Localizar células com valores maiores que 1000)


Talvez uma solução fosse numa outra planilha fazer uma lista, sem espaços, que me desse o número da linha para todos valores na coluna X que fossem acima de 1000?

rogerpenna

Uma forma que você pode fazer é, numa aba traz todos os errados, depois faz o HIPERLINK para ir na linha da aba onde esta todos, ai você corrige o que precisa

Decio

opa, podes me dar mais informações sobre as fórmulas a usar em ambas etapas desta solução? Obrigado.

rogerpenna escreveu: ... Eu preciso achar eles NO MEIO das linhas, para poder analisar os horímetros anteriores e posteriores e então modificar com um valor compatível.

O que você quer dizer com "um valor compatível" ?

Não sei como estão distribuidos os valores na sua tabela, então supondo um exemplo:
D50 = 200 horas
D51 = 1.700 horas (valor acima de 1.000 que você quer subsituir)
D52 = 300 horas
Qual o "valor compatível" a ser colocado em D51 (em substituição ao valor 1.700) ?
Seria (D50 + D52) / 2 = 250 horas ?

Se você estabelecer um critério (ou mais de um) para calcular o valor a ser inserido no lugar dos valores acima de 1.000 horas, esse trabalho poderá ser feito em alguns segundos/minutos via macro.

rogerpenna

Posso sim fazer, porem poste um modelo pois as formula são especifica para cada caso de planilha, principalmente HIPERLINK, então poste um modelo com dados fictícios e mostrando (por exemplo, Localizar células com valores maiores que 1000).

Decio

osvaldomp escreveu:

rogerpenna escreveu: ... Eu preciso achar eles NO MEIO das linhas, para poder analisar os horímetros anteriores e posteriores e então modificar com um valor compatível.

O que você quer dizer com "um valor compatível" ?

Não sei como estão distribuidos os valores na sua tabela, então supondo um exemplo:
D50 = 200 horas
D51 = 1.700 horas (valor acima de 1.000 que você quer subsituir)
D52 = 300 horas
Qual o "valor compatível" a ser colocado em D51 (em substituição ao valor 1.700) ?
Seria (D50 + D52) / 2 = 250 horas ?

Se você estabelecer um critério (ou mais de um) para calcular o valor a ser inserido no lugar dos valores acima de 1.000 horas, esse trabalho poderá ser feito em alguns segundos/minutos via macro.

Eu quero dizer exatamente que preciso analisar os horímetros anteriores e posteriores. Por isso que preciso ACHAR os valores errados, visualmente, na tabela. Não me adianta simplesmente trazer eles pra outro lugar. Consertar os valores com uma média eu saberia fazer.

Pq preciso analisar?

1 - porque eu não preciso consertar no Excel. Eu preciso consertar os valores NO PROGRAMA que usamos pra cadastro de horímetros. Quero usar o Excel pra achar os valores somente (poderia usar o Notepad, mas fica mais dificil ainda de achar valores maiores que X em 25 mil linhas de um banco de dados)

2 - porque não é a diferença entre dois horímetros diferentes que preciso consertar e sim os horímetros inseridos. A diferença é o programa que faz. Se a diferença está maior que 1000 é pq os horímetros inseridos estão errados.

3 - pq nem sempre a linha das horas que a máquina trabalhou (diferença) errada corresponde à linha do horímetro errado. Pode acontecer por exemplo de ter horímetros 1500, 1520, 1940, 1960, 1580, etc

O horímetro maior que 1000 que o programa vai inserir pra mostrar onde houve a inserção de um horímetro menor que o valor anterior é a na linha do horímetro 1580. Mas na verdade, os errados são o 1940 e 1960, onde o cara que anotou leu errado e trocou o 5 por 9. O horímetro saltou "400" (dos 1500 pra 1900) e depois qdo ele voltou a inserir certo, voltou "400" (para os 1500).


O programa usado pelo setor de Manutenção na verdade impede de lançar horímetros menores, mas é possível editar posteriormente e deixar menor que o anterior. Isso foi feito durante muitos anos, pois os responsáveis por lançar os dados no programa recebiam horímetros errados dos operadores, o programa não deixava eles lançar. Então lançava um horímetro posterior, editava e colocava o que estava no papel, até por não haver tempo para "pesquisar" o horímetro certo.

Deciog escreveu:rogerpenna

Posso sim fazer, porem poste um modelo pois as formula são especifica para cada caso de planilha, principalmente HIPERLINK, então poste um modelo com dados fictícios e mostrando (por exemplo, Localizar células com valores maiores que 1000).

Decio

Em anexo, uma pequena parte da planilha, entre as linhas 14399 e 14416. Na verdade são 86 mil linhas, não 25 mil.

rogerpenna, Bom Dia

Confere se é desta forma que desejas

A formula é especifica estuda ela bem antes de mudar algo

Décio

Não funcionou.

Troquei na primeira aba os dados pelo banco de dados completo da minha planilha. Troquei na tua fórmula o 18 por 9000, pra expandir a matriz de procura até tal linha.

Alguns links funcionaram. Outros, enviavam para a mesma célula que outros links. E alguns links enviavam para células em linhas onde não há números maiores que 1000.

imagino que uma solução seria numa segunda aba na Coluna A ter igual a primeira célula da coluna I da aba1.

Na coluna B na aba2, ter o hiperlink para essa mesma célula 1 da aba1.

Arrasto até o 90000, pegando portanto TODOS valores de horas e vazios da coluna de horas da Aba 1, e do lado, um hiperlink pra mesma célula. Após, seleciono tudo (ainda na Aba2) e ponho em ordem de maior pro menor. Os hiperlinks se matem, acredito.

Problema é que não consigo criar hiperlinks com a fórmula, nem pelo que li online nem analisando tua fórmula.

coloco assim
=hiperlink(Planilha1!A1). O link pra planilha 1 com o texto da célula A1 da planilha 1 aparece.

Mas quando clico, em vez de me levar pra célula A1 da Planilha 1, dá msg de erro dizendo que não conseguiu abrir o SITE e para eu corrigir a URL.

Se a questão é destacar e verificar os valores q vem antes e depois de uma célula com valor superior a 1000 vc poderia fazer isso com formatação condicional, como pode ser verificado na planilha anexa.

Se quiser usar a função HIPERLINK como o Deciog recomendou não se esqueça de usar '#', do contrário o Excel vai inferir q vc está se referindo a um endereço da internet.

rogerpenna , Bom DIa.

No modele que postou não exite dados repetidos, provavelmente tem dados repetidos, então vou ver se consigo uma nova solução aguarde

Decio

rogerpenna

Coloquei uma contagem de linha na coluna Q, alterei a função porem tive que fazer função matricial ou seja tem que clicar CRTL+SHIFT+ENTER para funcionar e isso pode deixar a planilha um puco lenta, faz um teste.

Isso para trazer mesmo se o dado for repetido

Décio

DJunqueira escreveu:Se a questão é destacar e verificar os valores q vem antes e depois de uma célula com valor superior a 1000 vc poderia fazer isso com formatação condicional, como pode ser verificado na planilha anexa.

Se quiser usar a função HIPERLINK como o Deciog recomendou não se esqueça de usar '#', do contrário o Excel vai inferir q vc está se referindo a um endereço da internet.

Obrigado DJunqueira, mas já estou usando formatação condicional. Digamos que, pra 80 mil linhas de banco de dados, a formatação condicional não ajuda muito (minha opinião).


quanto a usar # na função hiperlink, o suporte do Office não menciona em nenhum lugar isso! Onde coloco?
https://support.office.com/pt-br/articl ... e9b76f577f

rogerpenna

Você testou o ultimo que mandei?

Decio

rogerpenna escreveu:Obrigado DJunqueira, mas já estou usando formatação condicional. Digamos que, pra 80 mil linhas de banco de dados, a formatação condicional não ajuda muito (minha opinião).
quanto a usar # na função hiperlink, o suporte do Office não menciona em nenhum lugar isso! Onde coloco?
https://support.office.com/pt-br/articl ... e9b76f577f

Veja com atenção a fórmula q o Deciog montou:

=SEERRO(HIPERLINK("#Planilha1!Q"&ÍNDICE(Planilha1!$I$1:$Q$18;MENOR(SE(Planilha1!$I$1:$I$18>=1000; LIN(Planilha1!$I$1:$I$18)-4);LIN(A2));9)+4;"Linha "&ÍNDICE(Planilha1!$I$1:$Q$18;MENOR(SE(Planilha1!$I$1:$I$18>=1000; LIN(Planilha1!$I$1:$I$18)-4);LIN(A2));9)+4);"")

Vc fala em 80 mil linhas, mas quantos registros estão acima de 1000? Vc já fez uma fórmula p/ contar isso?
Vc acha preferível ficar indo e voltando de um link p/ corrigir esses erros?
Cada erro vai ser corrigido manualmente ou vc vai montar uma fórmula?

Concordo plenamente com o colega DJunqueir, fazer uma formula para calcular esse valor

Decio

No post 20408 explico pq não adianta nem tem como calcular com fórmulas.

O primeiro e mais importante motivo é que preciso achar os valores errados e suas datas, mas o conserto tem que ser em outro programa, não no Excel. Consertar automaticamente no Excel não vai consertar o banco de dados do programa.

r rogerpenna

Voce testou a versão que mandei neste dia Por Deciog 23 Fev 2017 às 06:44 pois mudei para pegar repetidos que é o caso

Decio

rogerpenna escreveu:No post 20408 explico pq não adianta nem tem como calcular com fórmulas.

O primeiro e mais importante motivo é que preciso achar os valores errados e suas datas, mas o conserto tem que ser em outro programa, não no Excel. Consertar automaticamente no Excel não vai consertar o banco de dados do programa.

Não seria então o caso de vc filtrar a lista?