Como exibir somente dados que atendem a determinados critérios no Excel

Question

Eu tenho uma lista de nomes, com uma coluna mais adiante, com dados numéricos, para o argumento, como mostrado abaixo:

Dave    1
Bob     2
Frank   1
Dennis  3

Eu gostaria de ter outra tabela em uma planilha diferente que seleciona e exibe os nomes, dependendo do número ao lado do nome.

Assim, por exemplo, a coluna 1 teria os nomes de Dave e Frank listados abaixo

Isso é possível?

    

Answer 1

Você pode fazer isso com uma fórmula de matriz. Faça o seguinte:

1. Configure sua tabela em uma nova planilha. Crie cabeçalhos de coluna 1 , 2 , 3 , etc.

2. No cabeçalho 1 (em A2 ), digite o seguinte.

   =IFERROR(INDEX(Sheet1!$A$1:$A$5,SMALL(IF(Sheet1!$B$1:$B$5=Sheet2!A$1,ROW(Sheet1!$B$1:$B$5)),ROWS(B$2:B2))),"")

   onde Sheet1 é a planilha com os dados originais, A1:A5 é a lista de nomes nos dados originais (incluindo o cabeçalho da coluna) e B1:B5 é a lista de números nos dados originais (incluindo o cabeçalho da coluna ).

3. Após digitar a fórmula, coloque o cursor na barra de fórmulas e pressione Ctrl + Deslocar + Enter . Isto irá inserir a fórmula como uma fórmula de matriz. A fórmula aparecerá na barra de fórmulas cercada por chaves.

4. Agora preencha esta fórmula para todas as colunas da sua nova tabela. Em seguida, preencha a fórmula até onde for necessário para que todos os nomes apareçam.

Explicação da fórmula:

A parte SMALL(IF(Sheet1!$B$1:$B$5=Sheet2!A$1,ROW(Sheet1!$B$1:$B$5)),ROWS(B$2:B2)) da fórmula examina os dados originais dos registros que correspondem ao número especificado pelo cabeçalho da coluna. Se uma correspondência for encontrada, o número da linha de cada correspondência é retornado quando ela é preenchida.

A parte INDEX(Sheet1!$A$1:$A$5, ...) da fórmula retorna o nome do número da linha encontrado.

A parte IFERROR(..., "") verifica o que é retornado por INDEX para um erro. Se um erro for encontrado, a fórmula não retornará nada.

    

Answer 2

Aqui está uma solução usando o VBA:

Public Sub columns()
    Dim wks, wks1 As Worksheet
    Set wks = ThisWorkbook.Sheets(1)
    Set wks1 = ThisWorkbook.Sheets(2)
    firstrowsource = 1
    wks1.Application.ScreenUpdating = False
    wks1.Cells.Clear
    Last = wks.Cells(Rows.Count, "A").End(xlUp).Row
    For i = firstrowsource To Last
        Name = wks.Cells(i, 1)
        Position = wks.Cells(i, 2)
        j = 1
        looking = True
            While looking
                If wks1.Cells(j, Position) = "" Then
                    If j <> 1 Then
                        wks1.Cells(j, Position) = Name
                    Else
                        wks1.Cells(j, Position) = Position
                        wks1.Cells(j + 1, Position) = Name
                    End If
                    looking = False
                Else
                    j = j + 1
                End If
            Wend
    Next i
    wks1.Application.ScreenUpdating = True
    Final = MsgBox("Finished", vbInformation)
End Sub

Você tem que ir para Macros / Visual Basic, sob ThisWorkbbok inserir um novo módulo e cole o código no lado direito.

Ele pressupõe que a planilha de origem é Sheet1 e o destino é Sheet2. Se sua planilha de origem tiver títulos, modifique o valor da variável firstrowsource .