Como substituir dados filtrados sem desativar o filtro no Excel?

Por exemplo, se você encontrar alguns erros de ortografia ou precisar padronizar entradas em uma lista filtrada, pode querer corrigir tudo de uma vez apenas para as linhas visíveis, sem modificar os dados ocultos (filtrados). O Excel fornece um atalho útil que permite selecionar apenas as células visíveis no intervalo filtrado. Essa operação é eficiente para fazer substituições uniformes ou atualizações rápidas em lote.

Observação: A substituição com este método sobrescreverá todas as células visíveis selecionadas com o mesmo valor; se cada célula precisar de uma entrada única, considere outras soluções abaixo.

1. Selecione as células no intervalo filtrado que você precisa substituir. Em seguida, pressione Alt + ; simultaneamente. Esta ação destacará apenas as células visíveis (filtradas), ignorando quaisquer linhas ocultas.

Dica de solução de problemas: Se Alt + ; não funcionar, certifique-se de que sua seleção abrange as células que você realmente deseja alterar e que o filtro está aplicado corretamente.

2. Digite o valor que deseja inserir e, em seguida, pressione Ctrl + Enter juntos. Este comando insere seu novo valor em todas as células selecionadas (visíveis) de uma só vez.

Ao pressionar essas teclas, todas as células filtradas visíveis dentro do intervalo selecionado serão atualizadas para o novo valor instantaneamente, com as linhas ocultas permanecendo inalteradas.

Vantagens: Simples e rápido para substituições unificadas; nenhum complemento necessário. Limitação: Todas as células selecionadas serão substituídas pelo mesmo valor exato.

Dica: Para desfazer as alterações, basta pressionar Ctrl + Z após a operação.

Às vezes, atualizar dados filtrados requer mais do que uma simples substituição de valor – você pode querer trocar seu intervalo filtrado por outro intervalo de tamanho igual, sem perturbar o filtro. Isso é particularmente útil para comparação de dados, controle de versão de conjuntos de dados ou restauração de valores anteriores. Com o utilitário Trocar Intervalos do Kutools para Excel, você pode realizar essa troca de forma tranquila.

1. Vá para a faixa de opções do Excel e escolha Kutools > Intervalo > Trocar Intervalos, que ativa a caixa de diálogo Trocar Intervalos.

2. Na caixa de diálogo, defina a primeira caixa (Intervalo de Troca 1) para o intervalo dos seus dados filtrados visíveis e defina a segunda caixa (Intervalo de Troca 2) para o intervalo de dados com o qual você deseja trocar. Certifique-se de que ambos os intervalos tenham o mesmo número de linhas e colunas para uma troca bem-sucedida.

3. Clique em OK. O Kutools trocará instantaneamente os valores entre os dois intervalos, mantendo seu filtro intacto. A configuração do filtro permanece inalterada; apenas os conteúdos das células especificadas são trocados.

Após realizar esta ação, verifique o conteúdo trocado quanto à precisão. A operação não afeta outros dados filtrados.

Vantagens: Manipula intervalos inteiros para operações de troca em dados filtrados; útil para análise comparativa. Observação: Os intervalos trocados devem coincidir em tamanho; caso contrário, ocorrerá um erro.

Além da troca, às vezes você tem novos dados prontos para colar na sua área filtrada, mas deseja atualizar apenas as linhas visíveis (mostradas) e pular as ocultas. O utilitário Colar no Intervalo Visível do Kutools para Excel fornece uma maneira conveniente de colar dados copiados diretamente apenas nas células visíveis dentro de uma lista filtrada. Isso é útil para atualizações rápidas em lote, importações de dados ou cópias de resultados de outra parte da sua pasta de trabalho.

1. Selecione o intervalo contendo os dados que deseja usar para substituição. Em seguida, vá para Kutools > Intervalo > Colar no Intervalo Visível para ativar a ferramenta.

2. Na caixa de diálogo pop-up, selecione o intervalo de destino nos seus dados filtrados onde os novos valores serão colados. Clique em OK para aplicar.

O Kutools irá automaticamente combinar seus valores colados apenas com as linhas visíveis (filtradas), deixando as linhas ocultas inalteradas – a solução ideal para substituições precisas e direcionadas em listagens filtradas.

Vantagens: Ótimo para atualizar registros filtrados com vários novos valores de uma só vez; não é necessário copiar/colar linha por linha manualmente. Dicas: Certifique-se de que as origens e os intervalos de destino visíveis contenham o mesmo número de células para evitar desalinhamento de dados.

Para operações de substituição mais complexas ou dinâmicas – como substituir palavras específicas, atualizar valores com base em critérios ou aplicar alterações baseadas em padrões – você pode usar uma macro VBA para substituir seletivamente dados apenas nas células visíveis de um intervalo filtrado. Essa abordagem é particularmente poderosa para grandes conjuntos de dados, lógica personalizada ou automação de atualizações em várias planilhas.

1. Clique em Desenvolvedor > Visual Basic. Na janela Microsoft Visual Basic for Applications, clique em Inserir > Módulo e cole o seguinte código no módulo:

Sub ReplaceVisibleCellsOnly_Advanced()
    ' Updated by ExtendOffice
    Dim rng As Range
    Dim cell As Range
    Dim searchText As String
    Dim replaceText As String
    Dim xTitleId As String

    On Error GoTo ExitSub
    xTitleId = "KutoolsforExcel"

   
    Set rng = Application.InputBox("Select the filtered range:", xTitleId, Selection.Address, Type:=8)
    If rng Is Nothing Then Exit Sub

 
    searchText = Application.InputBox("Enter the text/value to be replaced:", xTitleId, "", Type:=2)
    If searchText = "" Then Exit Sub
    replaceText = Application.InputBox("Enter the new text/value:", xTitleId, "", Type:=2)

    On Error Resume Next
    For Each cell In rng.SpecialCells(xlCellTypeVisible)
        If Not IsError(cell.Value) Then
            If InStr(1, cell.Value, searchText, vbTextCompare) > 0 Then
                cell.Value = Replace(cell.Value, searchText, replaceText, , , vbTextCompare)
            End If
        End If
    Next cell
    On Error GoTo 0

    MsgBox "Replacements completed in visible cells.", vbInformation, xTitleId
ExitSub:
End Sub

2. Clique no Executar botão para executar a macro. Primeiro, selecione o intervalo filtrado. Em seguida, insira o valor que deseja substituir e o novo valor. A macro aplicará as substituições apenas às células visíveis, deixando as linhas ocultas inalteradas.

Para situações em que você deseja um método baseado em fórmulas para “substituir” ou alterar valores exibidos com base em se uma linha é visível (ou seja, não filtrada), você pode usar uma combinação de SUBTOTAL e lógica condicional como SE ou SEERRO. Essa abordagem é ideal para relatórios dinâmicos ou substituições visuais sem modificar os dados originais.

1. Suponha que seus dados estejam no intervalo A2:A100. Na célula adjacente (por exemplo, B2), insira esta fórmula:

=IF(SUBTOTAL(103, OFFSET(A2, 0, 0)), IF(A2 = "oldvalue", "newvalue", A2), "")

Explicação:

• SUBTOTAL(103, DESLOCAMENTO(A2, 0, 0)) retorna 1 se a linha for visível, 0 se oculta.
• Se visível e A2 for igual a "valorantigo", ele mostra "valornovo"; caso contrário, mostra o valor de A2.
• Se a linha estiver filtrada, a fórmula retorna vazio.

2. Pressione Enter e arraste a fórmula para baixo. A lógica é aplicada dinamicamente às linhas visíveis. Para finalizar os resultados, copie a coluna auxiliar e use Colar Especial → Valores para sobrescrever os dados originais.

Como colar valores apenas em células visíveis no Excel?

Como trocar linhas ou colunas no Excel?