Como dividir números em colunas com valores negativos e positivos, sem células vazias?

Question

Dados valores positivos e negativos na coluna A de uma planilha do Excel, quero que a coluna B liste os valores positivos da coluna A e a coluna C para mostrar os valores negativos, na ordem em que aparecem na coluna A.

No entanto, não quero células vazias nas colunas B e C.

Então, eu não quero:

   A     B     C
----------------
 All   >=0    <0
 100   100
-300        -300
-800        -800
 900   900
 700   700
-200        -200
 900   900

Mas, em vez disso, quero:

   A     B     C
----------------
 All   >=0    <0
 100   100  -300
-300   900  -800
-800   700  -200
 900   900
 700
-200
 900

    

Answer 1

Não em uma caixa do windows. Aposto que há uma fórmula que faria isso. VBA com certeza: No VB Editor, clique em Inserir Módulo e digite:

Sub posInBnegInC(ByRef aList as range)
    Dim cll as Range
    For each Cll in AList
        If cll.value2 > 0 then
            Sheet2.Range("B65000").end(xlup).offset(1,0).value2 = cll.value2
        Else
            Sheet2.Range("C65000").end(xlup).offset(1,0).value2 = cll.value2
        End If
    Next cll
End Sub

Você pode adicionar a lógica para manipular o valor de exatamente 0, se tal existir. Como é o código, copiaremos os zeros para a coluna C. Se houver dados não numéricos na coluna A, isso não funcionará, mas suponho que todos sejam A numéricos. Então você pode executar de várias maneiras, incluindo:

Sub DoIt()
    call posInBnegInC(Sheet1.Range("A2:A65000"))
End Sub

    

Answer 2

Você está basicamente perguntando como obter o <-em> valor filtrado de alguma coluna na n-ésima linha de outra coluna.

A Microsoft explica como obter o valor filtrado de n-ésimo valor em Excel: Encontrando o enésimo valor que atende a uma condição . Isso usa "fórmulas de matriz" para executar cálculos em itens em uma matriz (uma lista). O exemplo da Microsoft pode ser estendido para obter o valor correspondente a um número de linha. Se sua matriz estiver em A2:A8 e você quiser mostrar apenas os valores >= 0 , o seguinte poderá ser usado em B2:B8 (não testado):

=iferror(index(A2:A8, small( if(A2:A8 >= 0, row()-1, ""), row()-1) ), "")

Tenha o cuidado de inserir isso como uma fórmula de matriz: primeiro selecione as células B2 thru B8 , cole o acima na barra de fórmulas e salve-o pressionando Ctrl + Shift + Enter (ou Command + Return in Office em um Mac, ou Command + Shift + Return no OpenOffice em um Mac).

Para C2:C8 , repita com a condição >= 0 substituída por < 0 .

Para decifrar este passo-a-passo, adicionei as seguintes fórmulas a uma planilha do OpenOffice Calc, com seus resultados abaixo. Note que no OpenOffice um precisa de um ponto-e-vírgula para separar parâmetros de função , enquanto no Excel pode ser necessário um vírgula, daí os pontos e vírgulas nas próximas fórmulas.

EmD2:D8:

=if(A2:A8>=0;row()-1;"")

Isto mostra o número da linha menos 1 se o valor na mesma linha em A2:A8 for positivo, ou uma célula vazia caso contrário. A subtração 1 converte as linhas de 2 a 8 em uma posição de 1 a 7, para ignorar a primeira linha de cabeçalho.

Agora, sabemos que os valores 1, 4, 5 e 7 de A2:A8 são positivos.

Em seguida, em E2:E8 :

=small( if(A2:A8 >= 0; row()-1; ""); row()-1 )

Isso leva os resultados anteriores como a entrada para small(..., k) que leva o k-ésimo menor número mostrado anteriormente em D2:D8 . (Essa lista é classificada de 1 a 7, mas isso nem é necessário para a função small .) Aqui, definimos k para o número da linha atual menos 1 para ignorar a linha de cabeçalho novamente. Então, para a terceira linha, obtemos a posição do segundo valor positivo em A2:A8 . Mas para E6 e para baixo nenhum valor é encontrado, mostrando um erro.

Em F2:F8 :

=index(A2:A8; small( if(A2:A8 >= 0; row()-1; ""); row()-1 ))

Isso leva as posições 1, 4, 5 e 7 (e os erros) da etapa anterior como entrada para index(..., k) , que encontra o valor k-th de A2:A8 , ou um erro se k é inválido.

Agora, F2:F8 ainda mostra um erro nas últimas linhas. No Excel 2007 e posterior, pode-se usar iferror para não mostrar nada em caso de erro, como usado na primeira fórmula acima. O OpenOffice não suporta esse , mas precisa de if(iserror(...); ""; ...) , substituindo ambos ... pela fórmula de F2:F8 . Não é legal.

Como alternativa, use index(A2:A9; ...) para incluir a célula A9 ao obter o valor real da primeira coluna e, em seguida, faça o OpenOffice usar small(...; 8) se nenhum valor mais for encontrado. Como em G2:G8 :

=if(A2:A8 >= 0; row()-1; 8)

Isso ainda pesquisa números positivos nos 7 valores de A2:A8 , mas agora retorna 8 , se não positivo.

Em H2:H8 :

=small( if(A2:A8 >= 0; row()-1; 8); row()-1 )

E finalmente em I2:I8 :

=index(A2:A9; small( if(A2:A8 >= 0; row()-1; 8); row()-1 ))

Aqui, o valor 8 é mapeado para o valor em A9 . Na captura de tela do exemplo, A9 contém alguns pontos, que são mostrados em H6:H8 , pois nenhum número positivo é encontrado.

Assim como a Microsoft escreve : se você realmente quer dominar fórmulas no Excel, você precisa saber como usar fórmulas de matriz . Aproveite.

    

Answer 3

Arjan já mencionou isso duas vezes, mas estou tentando postar isso como uma resposta real em vez de um comentário. Ele disse que o post está bloqueado, mas eu sou tão novo no Stack Exchange que não entendi porque ele está bloqueado, mas ainda posso postar isso como uma resposta. De qualquer forma, a Microsoft tem uma solução exata para isso. Eu apenas modificaria um pouco, colocando-o em IFERROR para retornar em branco, em vez de #NUM! erros.

link Como o artigo do KB indica, certifique-se de inseri-los como fórmulas de matriz

Para os pontos positivos:

{=IFERROR(INDEX($A$2:$A$8,SMALL(IF($A$2:$A$8>0,ROW($A$2:$A$8),""),ROW()-1)-ROW($A$2:$A$8)+1),"")}

Para os negativos:

{=IFERROR(INDEX($A$2:$A$8,SMALL(IF($A$2:$A$8<0,ROW($A$2:$A$8),""),ROW()-1)-ROW($A$2:$A$8)+1),"")}