Baixe a apostila de exemplo
Este tutorial demonstra como listar os nomes das planilhas de uma pasta de trabalho com uma fórmula no Excel.
Lista de nomes de planilhas usando intervalo nomeado e fórmula
Não há nenhuma função interna do Excel que possa listar todas as planilhas em uma pasta de trabalho. No entanto, isso pode ser alcançado usando uma combinação de funções diferentes.
Para listar os nomes das planilhas em uma pasta de trabalho, faremos o seguinte:
- Crie um intervalo nomeado de “planilhas”
- Use uma fórmula para listar todos os nomes de planilhas.
Criar intervalo de nomes para nomes de planilhas
Para criar um intervalo nomeado para os nomes das planilhas, no Faixa de opções do Excel: Fórmulas> Gerenciador de nomes> Novo
Digite “planilhas” na caixa de nome:
Na seção “Refere-se a” da caixa de diálogo, precisaremos escrever a fórmula
| 1 | = GET.WORKBOOK (1) & T (NOW ()) " |
Esta fórmula armazena os nomes de todas as planilhas (como uma matriz neste formato: “[pasta de trabalho.xlsm]. Visão geral”) na pasta de trabalho para o intervalo nomeado “Planilhas”.
A função “GET.WORKBOOK” é uma função de macro, então sua pasta de trabalho deve ser salva como uma pasta de trabalho habilitada para macro (formato de arquivo: .xlsm) para que os nomes das planilhas sejam atualizados cada vez que a pasta de trabalho for aberta.
Observação: ao preencher a caixa de diálogo Editar nome, a pasta de trabalho deve ser selecionada como o escopo do intervalo de nomes.
Usando a fórmula para listar os nomes das folhas
Agora usamos uma fórmula para listar os nomes das planilhas. Precisaremos das funções INDEX, MID, FIND e ROWS:
| 1 | = INDEX (MID (planilhas, FIND ("]", planilhas) +1.255), LINHAS ($ B $ 5: B5)) |
- A fórmula acima pega o array “Planilhas” e exibe o nome de cada folha com base em sua posição.
- As funções MID e FIND extraem os nomes das planilhas da matriz (removendo o nome da pasta de trabalho).
- Em seguida, as funções INDEX e ROW exibem cada valor nessa matriz.
- Aqui, “Visão geral” é a primeira folha das pastas de trabalho e “Limpeza” é a última.
Para obter mais detalhes sobre como as funções MID e FIND funcionam para obter nomes de planilhas, consulte o link get-sheet-name.
Método Alternativo
Você também tem a opção de criar a lista de nomes de planilhas no Gerenciador de Nomes. Ao invés de
| 1 | = GET.WORKBOOK (1) & T (NOW ()) |
defina o campo “Refere-se a” como
| 1 | = REPLACE (GET.WORKBOOK (1), 1, FIND ("]", GET.WORKBOOK (1)), "") |
Agora não há necessidade de MID, FIND e ROWS em sua fórmula. Seu intervalo nomeado já é composto apenas de nomes de planilhas.
Use esta fórmula INDEX mais simples para listar as planilhas:
| 1 | = INDEX (SheetName, B3) |
