Intervalo Dinâmico usando DESLOC()  [Resolvido]

Fórmulas básicas e avançadas do Microsoft Excel, Fórmulas Matriciais, Fórmulas combinadas, Funções como PROCV, PROCH, SOMASE, SOMA, SE, etc.
Regras do fórum
Sua dúvida foi respondida? Marque como RESOLVIDO em seus tópicos, usando o botão com marca verde. Imagem

Intervalo Dinâmico usando DESLOC()  [Resolvido]

Mensagempor fernando.fernandes » Sex Jul 10, 2009 6:23 pm

Range Dinâmico

ao definir um nome (usando Ctrl+F3) ao invés de selecionar o range (que ficaria fixo) use as funções Desloc() e Cont.Valores(), combinadas...

eu escrevi coisas diferentes nas células B2 a B5 de uma planilha...

teclei Ctrl+F3 (ou Inserir > Nome > Definir) e defini um nome qualquer para o intervalo que vou criar

no campo refere-se a, eu escrevi a fórmula abaixo:

=DESLOC(Plan1!$B$2;0;0;CONT.VALORES(Plan1!$B:$B);1)

Detalhe 01: NADA MAIS PODERÁ ESTAR EM NENHUMA CÉLULA DA COLUNA B, pois se tiver algo ali, o cont.valores da coluna B não funcionará e pegará linhas a mais do range flexível, ou range variável, ou Range Dinâmico (como prefiro chamar)

Detalhe 02: o início da lista do range dinamico é sempre o primeiro item da lista, e não o seu cabeçalho, ok?

Detalhe 03: uma vez q um nome definido no excel passa a ter uma fórmula (com ou sem funções, não importa) este nome não mais aparecerá na lista do Ctrl+Y (ir para) nem ao lado da barra de fórmulas... para ver o resultado do range dinamico, deve-se voltar à definição de nomes e clicar no campo refere-se à, daí o range vai BRILHAR na planilha . . .

Detalhe 4: naum pode haver nada em cima da lista tb, exceto se for um título, mas neste caso, ver detalhe 5...

Detalhe 5: se vc tiver um titulo na sua lista nas células do excel, precisa usar um -1, pra não contá-lo no range dinamico

neste caso ficaria assim

=DESLOC(Plan1!$B$2;0;0;CONT.VALORES(Plan1!$B:$B)-1;1)

NOTA/EXTRA:
essa função, se bem utilizada, pode variar em linhas (como explicado acima) e/ou em colunas... fica aí um exercício pra quem quiser brincar de nomear áreas cujo tamanho mude...

----------------------------------
é possível usar um Nome Definido no Excel (seja dinâmico ou não) em qualquer função do excel, inclusive qdo as utilizamos matricialmente:

exemplos:
=SOMA(VENDAS)

=PROCV(vendedor;TabelaVendedores;ColunaSalario;0)

=soma((Produtos="Leite")*(TabelaVendedores=Vendedor)*(ValoresVenda>Limite)*ValoresVenda)
terminando com Ctrl+Shift+Enter

claro q pra esta ultima é necessário ter um conhecimento mais profundo de Excel no que diz respeito a condicionais, mas é praticamente uma SOMASE() com 3 critérios do tipo E...
e se usarmos o q eu chamo de Range Dinâmico, podemos fazer miséria com as fórmulas e funções...

Vantagem: Qualquer pessoa que ler a fórmula, terá mais facilidade em entendê-la, pois não é endereços e sim textos facilmente compreensíveis....

as dicas dadas acima, utilizam fórmulas em portugues...

Traduzindo as funções:

    DESLOC é OFFSET
    SOMA é SUM
    PROCV é VLOOKUP
    SOMASE() é SUMIF()

Exemplo 1:
Se alguém quiser exemplo de rng dinamico com procv

crie um range dinamico chamado tabela, e na fórmula, use:
=desloc(Plan1!A1;1;0;cont.valores(Plan1!A:A);5)

Este Range Dinâmico é uma tabela que compreende um número variável de linhas, por um número fixo de colunas da planilha Plan1, ou seja,

Preencha A1 até E1 com títulos de coluna
Preencha A2 até E20 com valores de acordo com o cabeçalho

noutra plan, pode ser a plan2
Coloque na célula A2 a seguinte fórmula:
=procv(a1;tabela;5;0)

assim, td q vc escrever na célula A1, aparecerá o valor correspondente na célula A2...

Exemplo 2:
Foi definido um nome (use Ctrl+F3 para conferir). Este nome definido é o TblProduto e foi definido com a seguinte fórmula:
=DESLOC(Plan1!$L$1;1;0;CONT.VALORES(Plan1!$L:$L)-1;3)

Isso significa que a função DESLOC() pega como referência uma célula cujo endereço é L1, e a partir da célula DEBAIXO dela, iniciou um intervalo dinâmico de células.

Enfim... Isso significa que ao adicionar itens na tabela de produtos, o intervalo dinâmico já as incluirá, não sendo mais necessário alterar o conteúdo do nome definido, pois este é dinâmico.

Utilizei na tabela exemplo ao lado alguns PROCV() com testes de erro ( ÉERROS() ), e utilizei o range dinâmico TblProdutos no meio da função:
=PROCV($A2;Tblprodutos;2;0)

Vantagens:
1) LER a fórmula e entendê-la;
2) Para efeito de manutenção, não será mais necessário alterar o "alcance" do PROCV();

Desvantagens:
1) Ainda não enxerguei desvantagem... alguém se habilita?

O arquivo está anexado nesta postagem.

Quem tiver dúvidas, é só perguntar... temos verdadeiros mestres em Range Dinâmico por aqui! :geek:

ANEXOS BONS:
Você não está autorizado a ver ou baixar esse anexo.
Mil maneiras...
PDM=Potencial de Dar Merda
Fernando Fernandes - Moderação - MOS Excel Expert
Resposta útil? Clique na mãozinha.
Canal Expresso Excel:
https://www.youtube.com/c/ExpressoExcel
https://www.youtube.com/watch?v=lXmv0d0SqoM&

For this post the author fernando.fernandes thanked: 10
Ajala (Seg Nov 03, 2014 10:21 am) • begansks (Seg Set 19, 2016 10:09 am) • Carlos W (Qui Nov 12, 2015 7:31 am) • felmback (Seg Dez 05, 2016 10:54 am) • Helsinghor (Qua Nov 11, 2015 4:41 pm) • Krishna (Sex Dez 25, 2015 3:32 pm) • nasario (Seg Mai 13, 2013 11:32 am) • neto87 (Qui Abr 20, 2017 7:46 am) • olakunde (Seg Jun 29, 2015 1:37 pm) • victtaum (Sex Abr 10, 2015 10:37 am)
Avatar do usuário
fernando.fernandes
Ninja do Excel
Ninja do Excel
 
Mensagens: 1985
Registrado em: Sex Jul 03, 2009 8:21 pm
Localização: São Paulo, SP, BR
Has thanked: 178 times
Have thanks: 676 times

{ SO_SELECT }

Re: Range Dinâmico

Mensagempor Fábio » Sex Jul 10, 2009 8:11 pm

Muito boa a dica!!

Acho aformula Desloc uma das mais dificeis e magicas do Excel, gosto de usar, outra formula dificil que gostaria de saber mais Fernando e que usei uma vez mais tenho receio de usar por ser dificil de montar, pois oculpa muito tempo é BDCONTAR.

Vlw!!!!
Fábio
Membro
Membro
 
Mensagens: 13
Registrado em: Ter Jul 07, 2009 11:32 pm
Has thanked: 6 times
Have thanks: 0 time

Re: Range Dinâmico

Mensagempor enricobrasil » Sáb Jul 11, 2009 2:03 am

Essa postagem do range dinâmico é uma das mais fodas... merecia um jóia e ou um sticky (y)

For this post the author enricobrasil thanked:
fernando.fernandes (Qui Abr 20, 2017 12:05 pm)
Avatar do usuário
enricobrasil
Membro
Membro
 
Mensagens: 60
Registrado em: Sex Jul 03, 2009 8:48 pm
Localização: BH City
Has thanked: 18 times
Have thanks: 1 time

Re: Range Dinâmico

Mensagempor Henrique Mathias » Sex Set 04, 2009 1:27 pm

Muito legal, ficava alterando manualmente a referência das células.Vou utilizar muito.
Abraços. :D

For this post the author Henrique Mathias thanked:
fernando.fernandes (Seg Ago 11, 2014 1:03 pm)
Henrique Mathias
Membro
Membro
 
Mensagens: 4
Registrado em: Sex Jul 31, 2009 1:33 pm
Has thanked: 0 time
Have thanks: 1 time

Re: Range Dinâmico

Mensagempor melim.gui » Ter Nov 03, 2009 9:22 pm

Muito Sinistro. Util ao extremo, e me fez rever meu antigo conceito de que o procv é a funcao mais util do excel (sim, meus conhecimento sao limitados). Pra quem já tentou estudar VBA e nao chegou a lugar algum, indispensavel entender essa formular pra nao ficar perdido nos offset.
Valeu Fernando! :!: :!: :!: :!: :!:

For this post the author melim.gui thanked:
fernando.fernandes (Qua Ago 27, 2014 10:46 am)
melim.gui
Membro
Membro
 
Mensagens: 1
Registrado em: Qua Out 21, 2009 10:30 pm
Localização: DF
Has thanked: 0 time
Have thanks: 1 time

Re: Range Dinâmico

Mensagempor flavioaq » Ter Jan 31, 2012 1:24 pm

Fala galera,
tentei acessar a planilha em anexo e o link http://www.krazy.com.br/excel/PROCV_RngDinamico.zip , mas os ambos estão com problema, alguem tem essa palnilha salva para postar novamente?

Abraço
flavioaq
Membro
Membro
 
Mensagens: 9
Registrado em: Sex Dez 17, 2010 10:08 pm
Has thanked: 8 times
Have thanks: 0 time

Re: Range Dinâmico

Mensagempor enricobrasil » Qua Abr 04, 2012 11:20 pm

Boa noite, amiguinhos do Excel.

Também tentei acessar o arquivo pra reaprender (se é q eu já tinha aprendido isso antes) a usar o range dinâmico, mas o arquivo realmente tá corrompido.
Eu gostaria de tentar resolver o meu problema sozinho sem ter q incomodar os universitários.

Alguém tem esse arquivo pra postar de novo?

Abraço
Avatar do usuário
enricobrasil
Membro
Membro
 
Mensagens: 60
Registrado em: Sex Jul 03, 2009 8:48 pm
Localização: BH City
Has thanked: 18 times
Have thanks: 1 time

Re: Range Dinâmico

Mensagempor enricobrasil » Qua Abr 04, 2012 11:52 pm

Bom, consegui resolver meu problema seguindo o passo a passo q o Fernando postou.

Mas, de qualquer forma, fica a dica aí pros moderadores pros próximos usuários interessados em baixar a plan.

Abraço
Avatar do usuário
enricobrasil
Membro
Membro
 
Mensagens: 60
Registrado em: Sex Jul 03, 2009 8:48 pm
Localização: BH City
Has thanked: 18 times
Have thanks: 1 time

Re: Range Dinâmico

Mensagempor fernando.fernandes » Sex Jun 22, 2012 9:27 pm

O arquivo está anexado ao tópico em ambos os formatos.
Mil maneiras...
PDM=Potencial de Dar Merda
Fernando Fernandes - Moderação - MOS Excel Expert
Resposta útil? Clique na mãozinha.
Canal Expresso Excel:
https://www.youtube.com/c/ExpressoExcel
https://www.youtube.com/watch?v=lXmv0d0SqoM&
Avatar do usuário
fernando.fernandes
Ninja do Excel
Ninja do Excel
 
Mensagens: 1985
Registrado em: Sex Jul 03, 2009 8:21 pm
Localização: São Paulo, SP, BR
Has thanked: 178 times
Have thanks: 676 times

Re: Range Dinâmico

Mensagempor fernando.fernandes » Sex Mai 03, 2013 9:25 am

subindo arquivo novamente
Você não está autorizado a ver ou baixar esse anexo.
Mil maneiras...
PDM=Potencial de Dar Merda
Fernando Fernandes - Moderação - MOS Excel Expert
Resposta útil? Clique na mãozinha.
Canal Expresso Excel:
https://www.youtube.com/c/ExpressoExcel
https://www.youtube.com/watch?v=lXmv0d0SqoM&

For this post the author fernando.fernandes thanked:
needfsw (Ter Fev 21, 2017 12:46 pm)
Avatar do usuário
fernando.fernandes
Ninja do Excel
Ninja do Excel
 
Mensagens: 1985
Registrado em: Sex Jul 03, 2009 8:21 pm
Localização: São Paulo, SP, BR
Has thanked: 178 times
Have thanks: 676 times

Próximo

Voltar para Fórmulas & Funções Microsoft Excel

Quem está online

Usuários navegando neste fórum: joragen e 3 visitantes