Guru do Excel e Power BI

Fórum de Dúvidas sobre Excel e Power BI
https://gurudoexcel.com/

Otimização ÍNDICE e CORRESP

https://gurudoexcel.com/viewtopic.php?t=1662

Página 1 de 1

Otimização ÍNDICE e CORRESP

Enviado: 13 Mar 2016 às 21:53
por CleuberZago
Boa noite amigos!

Seguinte, preciso da ajuda de vocês sobre como posso otimizar uma pasta que criei e que contém diversas linhas de dados e fórmulas, dentre elas a matricial ÍNDICE + CORRESP para fazer o PROCV "ao contrário"...

O que acontece é que é uma planilha com muitos dados (muitos mesmo, coisa de 700 linhas novas por dia) e que a fórmula matricial está reduzindo muito o tempo de cálculo das fórmulas...

O fato é que, existe uma forma de otimizar esse tempo de cálculo ou infelizmente terei que alterar meu layout para tenha que deixar o procv padrão fazer essa busca?

Não vou conseguir postar o arquivo pois o mesmo está na minha máquina no trabalho, amanhã disponibilizo pra terem uma melhor ideia do que digo.

Desde já, abraços.

Re: Otimização ÍNDICE e CORRESP

Enviado: 14 Mar 2016 às 08:49
por alexandrevba
Bom dia!!

Leia:
http://ambienteoffice.com.br/blog/melho ... -do-procv/

Obs: Quanto mais formulas, mais pesado vai ficar!!!

Isso pode variar de acordo com os dados e quantidade dados, a forma com os dados estão expostos, as formulas e forma como elas estão calculando etc.

Att

Re: Otimização ÍNDICE e CORRESP

Enviado: 14 Mar 2016 às 23:42
por CleuberZago
Perfeito, Alexandre!

Porém infelizmente creio que não será possível o uso da busca binária pois os dados que extraio diariamente do SAP não são ordenados e tampouco só números, o que impossibilita o uso da mesma...

A não ser que os inclua numa tabela dinâmica no BD e classifique onde busco o retorno com número, nesse caso, creio que pelo menos em 1 campo poderei usar a busca binária.

Não me dá a cura pro câncer, mas me ajuda a medicar...

Re: Otimização ÍNDICE e CORRESP

Enviado: 15 Mar 2016 às 08:47
por alexandrevba
Bom dia!!
Não me dá a cura pro câncer, mas me ajuda a medicar...
A quimioterapia (formulas) é dolorosa no seu caso, eis que eu tenho a cura (VBA), o que acha?

Tem como postar um arquivo modelo com uma quantidade de dados não muito grande, e explicar com detalhes o que precisa?

Att

Otimização ÍNDICE e CORRESP

Enviado: 15 Mar 2016 às 20:19
por CleuberZago
Opa, tem sim... Acabei me esquecendo de anexar para entender melhor.

Deixei o arquivo apenas com 10 linhas de dados, mas pensa em algo como 700 dessas linhas por dia, dá pra se ter uma noção da magnitude da "coisa". Ainda não adaptei da forma como disse que poderia dar certo.

Apenas explicando + ou - o funcionamento da mesma: é um simples acompanhamento que vou fazer das transferências de materiais dentro de um ambiente fabril, as quais tenho 3 etapas: a de transferência em si, o bloqueio e o posterior desbloqueio do material. Para cada transferência tenho obrigatoriamente que ter 1 bloqueio e 1 desbloqueio, caso não tenha é onde devo atuar analisando o porque da falta dos procedimentos.
Como "padrão de busca" pros 3 movimentos tenho o número de reserva (coluna B - aba Monitor) e essa busca deve retornar os números do bloqueio (coluna M - aba Monitor) e do desbloqueio (coluna U - aba Monitor).

Tenho as duas bases de dados que puxo do SAP: Base Transferência (que como o próprio nome diz, é a relação de todas as transferências que são feitas no dia) e a Base Bloqueio-Desbloqueio (que também puxo do SAP e é onde está TODOS os movimentos de bloqueio e desbloqueio da empresa, não só dessas transferências).

No Monitor, apenas as colunas B até a G são preenchidas "manualmente" (Ctrl+c e Ctrl+v da base de transferências e removo as duplicatas), o resto é preenchido pelas fórmulas.

Dá uma luz aee...

Re: Otimização ÍNDICE e CORRESP

Enviado: 16 Mar 2016 às 12:57
por alexandrevba
Boa tarde!!

Faça o teste usando o botão de título "Aperte Aqui", veja se é o resultado esperado, caso tenha algo de errado e caso queira, vc mesmo pode editar as fórmulas dentro do Editor VB.



Att

Otimização ÍNDICE e CORRESP

Enviado: 16 Mar 2016 às 22:41
por CleuberZago
Matou a pau!

Tive que fazer só alguns ajustes, porém não consegui corrigir a inconsistência que tenho na fórmula matricial...
Ela está retornando o mesmo valor para todas as outras linhas abaixo. Acredito que seja alguma amarração que a fórmula esteja fazendo fidedignamente à célula B3 e retorna o mesmo valor obtido às demais pra baixo na coluna M....

O arquivo em anexo está com todas as outras correções, só falta essa... ;)

Re: Otimização ÍNDICE e CORRESP

Enviado: 17 Mar 2016 às 08:16
por alexandrevba
Bom dia!!

Seria isso?
Código: Selecionar todos
Sub AleVBA_1662()
Dim Rng As Range
Dim lstRow As Long
    lstRow = Cells(Rows.Count, "B").End(xlUp).Row

With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With
    Range("H3:H" & lstRow).Formula = "=VLOOKUP(D3,'Base Transferência'!$C:$L,10,1)"
    Range("H3:H" & lstRow).Interior.Color = RGB(0, 32, 96)
    Range("A3:A" & lstRow).Formula = "=CONCATENATE(B3,C3,H3)"
    Range("A3:A" & lstRow).Interior.Color = RGB(0, 32, 96)
    Range("I3:I" & lstRow).Formula = "=IF(ISERROR(VLOOKUP(G3,base_nomes!$A:$B,2,0)),"""",(VLOOKUP(G3,base_nomes!$A:$B,2,0)))"
    Range("I3:I" & lstRow).Interior.Color = RGB(0, 32, 96)
    Range("J3:J" & lstRow).Formula = "=UPPER(TEXT(E3,""MMM""))"
    Range("J3:J" & lstRow).Interior.Color = RGB(0, 32, 96)
    Range("L3:L" & lstRow).Formula = "=CONCATENATE(B3&344)"
    Range("L3:L" & lstRow).Interior.Color = RGB(0, 32, 96)
    With Sheets("Monitor")
        Set Rng = .Range("M3:M" & .Range("B" & Rows.Count).End(xlUp).Row)
        With .Range("M3")
            .FormulaArray = "=IFERROR(INDEX('Base Bloqueio-Desbloqueio'!$D:$D,MATCH(344&""RESERVA ""&Monitor!B3,'Base Bloqueio-Desbloqueio'!$C:$C&'Base Bloqueio-Desbloqueio'!$K:$K,0)),"""")"
            .AutoFill Rng
        End With
    End With
    Range("N3:N" & lstRow).Formula = "=IFERROR(VLOOKUP(M3,'Base Bloqueio-Desbloqueio'!$D:$I,2,0),"""")"
    Range("O3:O" & lstRow).Formula = "=IFERROR(VLOOKUP(M3,'Base Bloqueio-Desbloqueio'!$D:$I,3,0),"""")"
    Range("P3:P" & lstRow).Formula = "=IFERROR(VLOOKUP(M3,'Base Bloqueio-Desbloqueio'!$D:$I,4,0),"""")"
    Range("Q3:Q" & lstRow).Formula = "=IF(ISERROR(VLOOKUP(P3,base_nomes!$A:$B,2,0)),"""",(VLOOKUP(P3,base_nomes!$A:$B,2,0)))"
    Range("Q3:Q" & lstRow).Interior.Color = RGB(0, 32, 96)
    Range("R3:R" & lstRow).Formula = "=UPPER(TEXT(N3,""MMM""))"
    Range("R3:R" & lstRow).Interior.Color = RGB(0, 32, 96)
    Range("T3:T" & lstRow).Formula = "=CONCATENATE(B3&343)"
    Range("T3:T" & lstRow).Interior.Color = RGB(0, 32, 96)
    Range("U3:U" & lstRow).Formula = "=IFERROR(VLOOKUP(T3,'Base Bloqueio-Desbloqueio'!$A:$K,4,0),"""")"
    Range("V3:V" & lstRow).Formula = "=IFERROR(VLOOKUP(U3,'Base Bloqueio-Desbloqueio'!$D:$I,2,0),"""")"
    Range("W3:W" & lstRow).Formula = "=IFERROR(VLOOKUP(U3,'Base Bloqueio-Desbloqueio'!$D:$I,3,0),"""")"
    Range("X3:X" & lstRow).Formula = "=IFERROR(VLOOKUP(U3,'Base Bloqueio-Desbloqueio'!$D:$I,4,0),"""")"
    Range("Y3:Y" & lstRow).Formula = "=IF(ISERROR(VLOOKUP(X3,base_nomes!$A:$B,2,0)),"""",(VLOOKUP(X3,base_nomes!$A:$B,2,0)))"
    Range("Y3:Y" & lstRow).Interior.Color = RGB(0, 32, 96)
    Range("Z3:Z" & lstRow).Formula = "=UPPER(TEXT(V3,""MMM""))"
    Range("Z3:Z" & lstRow).Interior.Color = RGB(0, 32, 96)
    Range("A3:Z" & lstRow).Value = Range("A3:Z" & lstRow).Value
With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

End Sub
Att

Otimização ÍNDICE e CORRESP

Enviado: 17 Mar 2016 às 19:15
por CleuberZago
Continua na mesma, Alexandre...

Imagem

O critério de busca muda (coluna L), porém o resultado encontrado é sempre o mesmo (coluna M)...

Otimização ÍNDICE e CORRESP

Enviado: 18 Mar 2016 às 15:33
por gdomingos
Cleuber, se entendi bem, seu desejo é descartar o uso de fórmula matricial e deixa-la como fórmula comum, correto?
Não sei se é viável para você fazer alguma alteração no layout da planilha "Base Bloqueio-Desbloqueio", mas se sim...

Eu particularmente são sou muito adepto do uso de colunas auxiliares, más em muitas vezes é a melhor saída para a dispensa de fórmulas matriciais.

Então vamos ao que interessa.

Vamos criar uma coluna auxiliar na planilha "Base Bloqueio-Desbloqueio" utilizando a coluna "L"(ou qual você preferir), e colocando a formula na célula "L2" e depois arrastando para baixo até o fim da planilha.
Código: Selecionar todos
=C2&K2
Você pode ocultá-la para que não fique visível.

Agora vamos na planilha "Monitor", para transformar as formulas matriciais em fórmulas normais.
Na célula "M3" coloque essa fórmula e arraste para baixo.
Código: Selecionar todos
=SEERRO(ÍNDICE('Base Bloqueio-Desbloqueio'!$D:$D;CORRESP(344&"RESERVA "&Monitor!B3;'Base Bloqueio-Desbloqueio'!$L$2:$L$2234;0));"")
Pronto, você tem uma fórmula "normal", que ajudará na velocidade de cálculos.

O que fizemos foi simplesmente substituir as matrizes de busca da função corresp() que estavam concatenadas, para uma matriz única.

Espero que resolva seu problema.

Abraço.

Re: Otimização ÍNDICE e CORRESP

Enviado: 25 Mar 2016 às 21:40
por CleuberZago
Pessoal, consegui resolver de forma relativamente simples, porém não era beeemmm como queria. Fiz uma gambiarra na base de dados e no fim não precisei alterar em nada o layout que já havia feito e consegui reduzir o tempo de cálculo sem usar fórmulas matriciais...

De qualquer forma obrigado a todos...
Todos os horários são UTC-03:00
Página 1 de 1
Powered by phpBB® Forum Software © phpBB Limited