Como referenciar a mesma célula de várias planilhas no Excel?

Suponha que você tenha uma pasta de trabalho que contém várias planilhas e agora deseja extrair os dados da mesma célula em várias planilhas para uma planilha mestre. Por exemplo, trazer a célula B8 da Planilha1, Planilha2, Planilha3, Planilha4... para uma planilha mestre, conforme mostrado na captura de tela abaixo. Como você poderia resolver essa tarefa rapidamente e facilmente no Excel?

Referenciar a mesma célula de várias planilhas em uma planilha mestra com fórmula

Referenciar a mesma célula de várias planilhas em uma planilha mestra com código VBA

Referenciar a mesma célula de várias planilhas em uma planilha mestra com um recurso incrível

Referenciar a mesma célula de várias planilhas em uma planilha mestra com fórmula

Se os nomes das suas planilhas são nomes padrão, como Planilha1, Planilha2, Planilha3..., você pode usar uma fórmula para lidar com essa tarefa rapidamente no Excel.

1. Primeiro, você deve criar uma lista de números auxiliares. Insira 1, 2, 3, 4... números sequenciais que indicam as planilhas que você precisa referenciar, veja a captura de tela:

2. Em seguida, insira a fórmula abaixo na célula onde você deseja extrair os dados e arraste a alça de preenchimento para baixo até as células onde deseja aplicar esta fórmula, e todos os valores da mesma célula de várias planilhas serão extraídos, veja a captura de tela:

Observação: Na fórmula acima, E2 é o número auxiliar que você inseriu na etapa 1, e B8 é a referência de célula que você deseja extrair. Essa fórmula só funciona bem se os nomes das planilhas forem Planilha1, Planilha2, Planilha3...

Referenciar a mesma célula de várias planilhas em uma planilha mestra com código VBA

Se houver dezenas de planilhas cujos nomes sejam variados em vez de Planilha1, Planilha2, neste caso, o seguinte código VBA pode ajudá-lo a preencher as mesmas referências de células de várias planilhas em uma planilha de uma só vez.

1. Na planilha Mestre, clique na célula B8, que é a célula que você deseja extrair de outras planilhas.

2. Pressione e segure as teclas ALT + F11, e abrirá a janela Microsoft Visual Basic for Applications.

3. Clique em Inserir > Módulo e cole o seguinte código na Janela do Módulo.

Código VBA: referenciar a mesma célula de várias planilhas

Sub AutoFillSheetNames()
'Update by Extendoffice
Dim ActRng As Range
Dim ActWsName As String
Dim ActAddress As String
Dim Ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ActRng = Application.ActiveCell
ActWsName = Application.ActiveSheet.Name
ActAddress = ActRng.Address(False, False)
Application.ScreenUpdating = False
xIndex = 0
For Each Ws In Application.Worksheets
If Ws.Name <> ActWsName Then
ActRng.Offset(xIndex, 0).Value = "='" & Ws.Name & "'!" & ActAddress
xIndex = xIndex + 1
End If
Next
Application.ScreenUpdating = True
End Sub

4. Em seguida, pressione a tecla F5 para executar este código, e todos os valores da célula B8 de outras planilhas terão sido puxados para a planilha Mestre. Veja a captura de tela:

Observação: Este código VBA é aplicado para preencher as células onde você clica. Por exemplo, se você clicar na célula A1 em uma planilha específica, todos os valores da célula A1 de outras planilhas serão preenchidos nessa planilha.

Referenciar a mesma célula de várias planilhas em uma planilha mestra com um recurso incrível

Se você não está familiarizado com o código VBA, aqui posso apresentar uma ferramenta útil - Kutools para Excel, com seu utilitário Auto-incrementar referências de planilha, você pode preencher referências de células de várias planilhas no Excel com facilidade.

Após instalar o Kutools para Excel, faça o seguinte:

1. Clique na célula B8 dentro da planilha Mestre que você deseja extrair de outras planilhas.

2. Em seguida, clique em Kutools > Mais > Auto-incrementar referências de planilha, veja a captura de tela:

3. Na caixa de diálogo Preencher referências de planilha, escolha Preencher por Coluna, depois por Linha na Ordem de Preenchimento, e clique no pequeno cadeado ao lado da caixa de texto da fórmula, e o cadeado cinza se tornará amarelo, o que significa que a fórmula e a referência de célula foram bloqueadas; então você pode clicar em qualquer célula para extrair as referências da célula B8 de outras planilhas, neste exemplo, vou clicar na célula B2. Depois, marque as planilhas das quais você deseja extrair as referências de célula. Veja a captura de tela:

Observação: Se você não bloquear este pequeno cadeado, a referência de célula que você clicou será alterada com a célula selecionada.

4. Em seguida, clique no botão Preencher Intervalo, e os valores da célula B8 de outras planilhas serão listados verticalmente na planilha Mestre, e depois feche esta caixa de diálogo. Veja a captura de tela:

Clique para Baixar Kutools para Excel e Experimente Grátis Agora!

Mais artigos: