Como pesquisar rapidamente um valor em várias planilhas ou pastas de trabalho?

Você já precisou encontrar um valor específico que pode aparecer em diferentes planilhas ou até mesmo em várias pastas de trabalho no Excel? Este é um cenário comum, especialmente ao trabalhar com grandes projetos, relatórios mensais ou ao consolidar informações mantidas em vários arquivos. Procurar manualmente em cada planilha ou arquivo não só consome muito tempo, mas também está sujeito a erros. Neste tutorial, você descobrirá vários métodos eficazes para pesquisar dados de forma eficiente, seja dentro de uma pasta de trabalho, várias pastas de trabalho, ou se precisar de uma solução baseada em fórmulas ou de dados consolidados. Essas abordagens atendem às necessidades práticas que você pode encontrar em fluxos de trabalho rotineiros do Excel ou em projetos de análise de dados.

Pesquisar um valor em várias planilhas de uma pasta de trabalho com a função Localizar e Substituir

Pesquisar um valor em todas as pastas de trabalho de uma pasta com VBA

Pesquise rapidamente um valor em várias pastas de trabalho abertas com o Kutools para Excel

Pesquise um valor em várias planilhas usando fórmulas do Excel

Pesquisar um valor em várias planilhas de uma pasta de trabalho com a função Localizar e Substituir

A função Localizar e Substituir do Excel é uma maneira básica, mas eficaz, de encontrar rapidamente valores específicos em várias planilhas dentro da mesma pasta de trabalho. Esse método é mais útil quando você sabe quais planilhas deseja pesquisar ou quando seus dados estão relativamente bem estruturados em um único arquivo. Ele não suporta pesquisa em diferentes arquivos ou pastas de trabalho fechadas, mas oferece uma maneira direta para buscas rápidas.

1. Para começar, selecione as guias de planilhas que deseja incluir na sua pesquisa segurando a tecla Ctrl e clicando em cada planilha individualmente na Barra de Guias de Planilha. Isso garante que a pesquisa será aplicada a todas as planilhas selecionadas simultaneamente. Veja a captura de tela:

2. Depois de selecionar as planilhas desejadas, pressione Ctrl + F para abrir a caixa de diálogo Localizar e Substituir. Digite o valor que deseja encontrar no campo Localizar o que, na aba Localizar, e clique no botão Localizar Tudo. O Excel exibirá imediatamente uma lista de todas as células nas planilhas selecionadas que contêm o valor procurado, junto com suas localizações. Veja a captura de tela:

Dica: A ferramenta Localizar e Substituir só pesquisa dentro das planilhas selecionadas que estão abertas e visíveis. Se você quiser estender sua pesquisa a outras planilhas, certifique-se de selecioná-las como descrito acima. Se você perder acidentalmente uma planilha, basta repetir a operação após incluí-la na sua seleção.

Precauções: Este método não pesquisa em pastas de trabalho fechadas ou ocultas e não destaca células automaticamente – ele apenas listará os resultados para navegação.

Solução de problemas: Se você não visualizar os resultados esperados, verifique novamente sua seleção de planilhas e confirme que não aplicou nenhum filtro ou proteção de célula que possa ocultar ou restringir a pesquisa.

Encontre e substitua valores facilmente em várias planilhas e pastas de trabalho

O recurso avançado de Localizar e Substituir do Kutools para Excel oferece uma maneira eficiente de pesquisar e substituir valores em várias planilhas ou até mesmo em todas as pastas de trabalho abertas. Com esse recurso avançado, você pode economizar tempo e eliminar erros ao trabalhar com grandes conjuntos de dados, tornando suas tarefas no Excel mais rápidas e precisas.

Kutools para Excel - Potencialize o Excel com mais de 300 ferramentas essenciais. Aproveite recursos de IA permanentemente gratuitos! Obtenha Agora

Pesquisar um valor em todas as pastas de trabalho de uma pasta com VBA

Se você precisa pesquisar um valor específico em várias pastas de trabalho salvas em uma pasta — incluindo arquivos que ainda não foram abertos — as ferramentas internas do Excel não podem fazer isso diretamente. Nesse caso, uma macro VBA (Visual Basic for Applications) pode automatizar o processo para você, abrindo sistematicamente cada pasta de trabalho na pasta, escaneando todas as planilhas e registrando onde as correspondências forem encontradas. Esta abordagem é altamente prática para auditorias periódicas ou para verificar valores em arquivos arquivados ou lotes.

1. Comece abrindo uma nova pasta de trabalho (ou em branco) no Excel. Selecione a célula onde você deseja que os resultados da pesquisa apareçam (geralmente a célula A1). Pressione Alt + F11 para iniciar a janela do editor Microsoft Visual Basic for Applications.

2. Dentro do editor VBA, vá para Inserir > Módulo para criar um novo módulo, e cole o seguinte código VBA na janela do módulo.

VBA: Pesquise um valor em todas as pastas de trabalho de uma pasta.

Sub SearchFolders()
'UpdatebyKutoolsforExcel20200913
    Dim xFso As Object
    Dim xFld As Object
    Dim xStrSearch As String
    Dim xStrPath As String
    Dim xStrFile As String
    Dim xOut As Worksheet
    Dim xWb As Workbook
    Dim xWk As Worksheet
    Dim xRow As Long
    Dim xFound As Range
    Dim xStrAddress As String
    Dim xFileDialog As FileDialog
    Dim xUpdate As Boolean
    Dim xCount As Long
    Dim xAWB As Workbook
    Dim xAWBStrPath As String
    Dim xBol As Boolean
    Set xAWB = ActiveWorkbook
    xAWBStrPath = xAWB.Path & "\" & xAWB.Name
    On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a forlder"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    xStrSearch = "KTE"
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    Set xOut = Worksheets.Add
    xRow = 1
    With xOut
        .Cells(xRow, 1) = "Workbook"
        .Cells(xRow, 2) = "Worksheet"
        .Cells(xRow, 3) = "Cell"
        .Cells(xRow, 4) = "Text in Cell"
        Set xFso = CreateObject("Scripting.FileSystemObject")
        Set xFld = xFso.GetFolder(xStrPath)
        xStrFile = Dir(xStrPath & "\*.xls*")
        Do While xStrFile <> ""
            xBol = False
            If (xStrPath & "\" & xStrFile) = xAWBStrPath Then
                xBol = True
                Set xWb = xAWB
            Else
                Set xWb = Workbooks.Open(Filename:=xStrPath & "\" & xStrFile, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False)
            End If
            For Each xWk In xWb.Worksheets
                If xBol And (xWk.Name = .Name) Then
                Else
                Set xFound = xWk.UsedRange.Find(xStrSearch)
                If Not xFound Is Nothing Then
                    xStrAddress = xFound.Address
                End If
                Do
                    If xFound Is Nothing Then
                        Exit Do
                    Else
                        xCount = xCount + 1
                        xRow = xRow + 1
                        .Cells(xRow, 1) = xWb.Name
                        .Cells(xRow, 2) = xWk.Name
                        .Cells(xRow, 3) = xFound.Address
                        .Cells(xRow, 4) = xFound.Value
                    End If
                    Set xFound = xWk.Cells.FindNext(After:=xFound)
                Loop While xStrAddress <> xFound.Address
                End If
            Next
            If Not xBol Then
            xWb.Close (False)
            End If
            xStrFile = Dir
        Loop
        .Columns("A:D").EntireColumn.AutoFit
    End With
    MsgBox xCount & " cells have been found", , "Kutools for Excel"
ExitHandler:
    Set xOut = Nothing
    Set xWk = Nothing
    Set xWb = Nothing
    Set xFld = Nothing
    Set xFso = Nothing
    Application.ScreenUpdating = xUpdate
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

3. Pressione a tecla F5 ou clique no botão Executar para executar essa macro. Uma janela de diálogo Selecionar uma pasta aparecerá, permitindo que você escolha a pasta que contém as pastas de trabalho que deseja pesquisar. Veja a captura de tela:

4. Clique em OK. Após a conclusão da pesquisa, uma caixa de mensagem informará o número total de células encontradas que contêm o valor especificado. Veja a captura de tela:

5. Clique em OK para fechar a mensagem. Todas as localizações onde o valor foi encontrado são listadas em uma nova planilha, incluindo o nome da pasta de trabalho, o nome da planilha, a referência da célula e o conteúdo exato da célula para sua revisão.

Dica: O termo de pesquisa atual está definido no VBA como “KTE”. Você pode personalizá-lo alterando a linha xStrSearch = "KTE" no código para qualquer valor que desejar procurar.

Precauções: Antes de executar, certifique-se de que todas as pastas de trabalho relevantes estejam salvas e fechadas (exceto aquela onde você executa sua macro, que pode estar aberta). Pastas grandes com muitos ou arquivos complexos do Excel podem levar algum tempo para serem processadas. Não interrompa a macro durante a execução.

Solução de problemas: Se você encontrar erros, verifique se todos os arquivos são pastas de trabalho genuínas do Excel (não corrompidas ou protegidas por senha) e se suas configurações de segurança de macros permitem a execução de códigos. Se a macro não terminar, tente executá-la em um conjunto menor de arquivos primeiro.

Pesquise rapidamente um valor em várias pastas de trabalho abertas com o Kutools para Excel

Quando você deseja realizar uma pesquisa em várias pastas de trabalho que já estão abertas em sua sessão do Excel, o Kutools para Excel oferece um painel dedicado de Localizar e Substituir que torna o processo muito mais fácil e organizado. Isso é especialmente útil para varrer rapidamente suas pastas de trabalho sem a necessidade de scripts ou configurações complicadas, ideal para usuários que frequentemente trabalham com vários arquivos ao mesmo tempo e precisam de uma ferramenta amigável e direta para gerenciar pesquisas.

1. Em qualquer uma de suas pastas de trabalho abertas, vá para a Kutools aba e selecione Navegação. Em seguida, clique no Localizar e Substituir botão  para trazer o Localizar e Substituir painel, geralmente localizado à esquerda da sua janela do Excel. Veja a captura de tela:

2. No Localizar aba, insira o valor que deseja pesquisar no Localizar o que campo de texto. Escolha Todas as pastas de trabalho no menu suspenso Dentro para realizar a pesquisa em todas as pastas de trabalho abertas. Em seguida, clique em Localizar Tudo para exibir instantaneamente uma lista de todas as células correspondentes, junto com suas localizações. Veja a captura de tela:

Dica: O utilitário avançado de Localizar e Substituir do Kutools para Excel permite que você pesquise e substitua dados não apenas em todas as pastas de trabalho abertas, mas também especificamente em planilhas selecionadas, na pasta de trabalho ativa, na planilha atual ou até mesmo apenas na sua seleção atual. Isso lhe dá controle personalizado dependendo de suas necessidades.

Precauções: Certifique-se de que todas as pastas de trabalho que você deseja pesquisar estejam abertas antes de iniciar a pesquisa, pois esta ferramenta não pode pesquisar arquivos que não estão atualmente abertos no Excel.

Solução de problemas: Se alguns arquivos não aparecerem nos resultados da pesquisa, verifique se eles estão totalmente carregados no Excel e não estão em modo de visualização protegida ou somente leitura, o que pode restringir o acesso à pesquisa.

Demonstração: Pesquisar Valor em Múltiplas Pastas de Trabalho Abertas com o Kutools para Excel

Pesquisar um valor em várias planilhas usando fórmulas do Excel

Em situações em que você tem vários nomes de planilhas conhecidos em sua pasta de trabalho e precisa verificar se e onde um valor específico existe entre essas planilhas, você pode usar fórmulas do Excel para pesquisar dinamicamente entre elas. Essa abordagem é particularmente adequada quando você deseja manter seus resultados de pesquisa atualizados automaticamente e sua lista de planilhas é relativamente estática ou gerenciada em uma tabela separada.

Este método requer que você já saiba ou liste os nomes de todas as planilhas a serem pesquisadas. É mais eficaz para verificações automatizadas, dashboards ou sempre que você quiser criar uma referência resumida sem executar uma verificação completa a cada vez.

Vantagens: Os resultados são atualizados automaticamente conforme os dados mudam; não há necessidade de scripts ou suplementos; tudo é gerenciado dentro da pasta de trabalho.
Desvantagens: Não é adequado para situações em que os nomes das planilhas mudam frequentemente, ou se você tem um número muito grande de planilhas.

Cenário de exemplo: Suponha que você tenha três planilhas chamadas Plan1, Plan2 e Plan3. Você quer saber em qual(is) planilha(s) um valor específico, como "NotaFiscal123", aparece na célula A1, ou simplesmente verificar se o valor existe em alguma dessas planilhas.

Passo 1. Suponha que você tenha uma lista de nomes de planilhas em D2:D4 (D2: Plan1, D3: Plan2, D4: Plan3). Insira o valor a ser pesquisado (por exemplo, "NotaFiscal123") em E1. Em seguida, em F2, insira esta fórmula:

=IF(COUNTIF(INDIRECT("'"&D2&"'!A:A"), $E$1) >0, "Found", "Not Found")

Passo 2. Arraste a fórmula de F2 para F4 para verificar todas as planilhas listadas em D2:D4. Isso retornará "Encontrado" ou "Não Encontrado" para cada planilha.

Como funciona: A fórmula usa INDIRETO para criar uma referência a cada planilha listada e CONT.SE para verificar se o valor em E1 aparece na coluna A de cada planilha. Ajuste o intervalo A:A para direcionar outra coluna ou um intervalo específico (por exemplo, A1:Z100), se necessário.

Dica adicional: Para recuperar o(s) nome(s) da(s) planilha(s) que contém o valor, use a seguinte fórmula de matriz (inserida com Ctrl + Shift + Enter em versões antigas do Excel ou como uma fórmula regular no Microsoft 365/Excel 2021+):

=TEXTJOIN(", ",TRUE,IF(COUNTIF(INDIRECT("'"&D2:D4&"'!A:A"), $E$1)>0, D2:D4, ""))

Isso retornará uma lista separada por vírgulas de todos os nomes de planilhas onde o valor foi encontrado. Tenha cuidado com INDIRETO — ele só funciona com pastas de trabalho abertas e não permite pesquisar arquivos fechados.

Precauções: Se os nomes das planilhas forem alterados ou excluídos, a fórmula retornará um erro #REF!; sempre verifique se a lista de nomes de planilhas está correta. Para pastas de trabalho maiores, fórmulas baseadas em INDIRETO podem diminuir o desempenho da sua pasta de trabalho.

Solução de problemas: Se você vir erros, verifique se todas as planilhas referenciadas existem e se o intervalo de pesquisa está correto. Para listas dinâmicas de planilhas, considere usar intervalos nomeados ou Validação de Dados para manter a lista de planilhas atualizada automaticamente.

Artigos Relacionados:

• Como localizar e substituir rapidamente em várias planilhas ou na pasta de trabalho inteira?
• Como localizar e substituir rapidamente em vários arquivos do Excel abertos?
• Como encontrar o maior valor negativo (menor que 0) no Excel?
• Como extrair todos os duplicados de uma coluna no Excel?