Estou procurando uma maneira de fazer referência a células dentro do intervalo nomeado e que seja compreensível para a pessoa que procura na planilha.
Eu tenho esta planilha:
A B C D E F G H
1 jan feb mar apr may sum sum2
2 car 1 2 3 4 5
3 bot 3 4 5 6 7
4 top 10 20 30 40 50
Então "carro" é o intervalo nomeado B2: F2 e "jan é o intervalo nomeado para B2: B4
No mundo ideal, eu me vejo fazendo isso:
G2 = car jan
Então, quando copio e cole a fórmula acima em H2, o Excel aumenta automaticamente "jan" para "feb" e eu obtenho
G3 = car feb
Isso obviamente não está funcionando. Eu vi maneiras diferentes de as pessoas sugerirem obter tais resultados, mas isso me deixa com algumas fórmulas de pesquisa longas, enquanto eu quero que a pessoa que lê a planilha seja capaz de entender intuitivamente todas as referências sem saber o que as fórmulas significam.
Se isso não for absolutamente possível, eu ficaria feliz em pelo menos poder referir-se às células pelo nome da linha e da letra da coluna. Então eu posso fazer:
G2 = SOMEFORMULA(car, C)
Com os intervalos nomeados como você diz, escreva em G2 a próxima fórmula arrastável:
=INDEX($A$1:$F$4,ROW(INDIRECT($A2)),COLUMN(INDIRECT(B$1)))
E é claro que você seria capaz de escrever algo como
=INDEX($A$1:$F$4,ROW(car),COLUMN(feb))
mas essa fórmula não seria arrastável.
(Nota $ A $ 1: $ F $ 4 é a referência do seu conjunto de dados)
Espero que funcione!
Aqui está uma solução VBA. Usando macros para algo que pode ser feito com fórmulas de planilha não é a melhor prática, no entanto, dá-lhe a funcionalidade desejada. Crie um módulo em sua pasta de trabalho e adicione esse código ao módulo. Não esqueça de salvar seu arquivo como .xlsm
Function SOMEFORMULA(item As Variant, month As Variant)
Dim rng As Range
'set your lookup range
Set rng = Worksheets("Sheet1").Range("A1:F4")
SOMEFORMULA = Application.WorksheetFunction.Index(rng, WorksheetFunction.Match(item, WorksheetFunction.Index(rng, 0, 1), 0), WorksheetFunction.Match(month, WorksheetFunction.Index(rng, 1, 0), 0))
End Function
Minha recomendação é usar um intervalo nomeado para sua matriz de dados, de forma que esta linha:
Set rng = Worksheets("Sheet1").Range("A1:F4")
Torna-se:
Set rng = Worksheets("Sheet1").Range("named_range")
E pode ser editado a partir da pasta de trabalho.
Você também pode querer alterar o nome da formulação de "SOMEFORMULA ()" para algo menor / mais apropriado.
Os resultados são os seguintes:
