ti-enxame.com

Como ordenar tabela usando somente função/equação

Como posso classificar uma tabela na folha 1 como

A B C D E
3 7 3 6 5

em outra tabela na folha 2

A C E D B
3 3 5 6 7

usando apenas função?

9
Season

Uma maneira muito fácil de fazer isso seria apenas ter um índice de classificação e, em seguida, usar o HLOOKUP para encontrar os valores correspondentes:

=RANK(A4,$A$4:$E$4,1)

Example 1

=IF(COUNTIF($A$1:A$1,A1)>1,RANK(A4,$A$4:$E$4,1)+COUNTIF($A$1:A$1,A1)-1,RANK(A4,$A$4:$E$4,1))

Example 2

=HLOOKUP(COLUMN(),$A$2:$E$4,2,FALSE)

Example 3

=HLOOKUP(COLUMN(),$A$2:$E$4,3,FALSE)

Example 4

11
Stepan1010

Ok, aqui está a solução "uma fórmula faz tudo" sem colunas temporárias adicionais:

enter image description here

Fórmula em A6:

 = ÍNDICE ($ A $ 2: $ E $ 2, CORRESPONDÊNCIA (PEQUENA ($ A $ 3: $ E $ 3 + COLUNA ($ A $ 3: $ E $ 3)/100000000, COLUNA ()), $ A $ 3: $ E $ 3 + COLUMN ($ A $ 3: $ E $ 3)/100000000,0)) 

Insira-o como uma fórmula de matriz, ou seja, pressione Ctrl-Shift-Enter. Em seguida, copie-o para as colunas adjacentes.

Para obter também o número, use essa fórmula em A7 (novamente como fórmula de matriz):

 = REDONDO (PEQUENO ($ A $ 3: $ E $ 3 + COLUNA ($ A $ 3: $ E $ 3)/100000000, COLUNA ()), 6) 

Ambas as fórmulas são um pouco inchadas, pois elas também precisam manipular as duplicatas em potencial. A solução é simplesmente adicionar uma fração muito pequena da coluna antes de aplicar a ordenação (função SMALL) - e depois removê-la novamente ...

11
Peter Albert

Acho que uma solução mais fácil é usar a função "Large":  Excel screenshot

As funções da coluna são apenas uma maneira fácil de contar de 5 para 1, mas uma coluna auxiliar pode ser ainda mais fácil.

Você pode ter uma resposta semelhante usando a função "Small" também.

1
Eliyahu

Embora eu tenha usado a técnica "adicionar um pequeno número", acho que é o mais elegante para uma linha/coluna auxiliar: 

=RANK(B4,$B$4:$F$4,0) + COUNTIF($B$4:$F$4,B4)-1

(copiar na coluna da linha) RANK o aproxima e a parte do COUNTIF manipula as duplicatas contando o número de duplicatas da célula até esse ponto. Como sempre há correspondência (em si), você subtrai 1 para a classificação final. Isso "classifica" os vínculos na ordem em que aparecem. Observe que uma célula vazia gerará # N/D e dados de caractere como #Values.

1
DaveM

É factível! :-)

Aqui está o arquivo exemplo .

Explicação

Presumo que seus dados estejam na linha 1 da Planilha1 e que você queira que os dados sejam classificados na coluna 1 de outra planilha - se não, ajuste de acordo.

Coloque essa fórmula na coluna 1 da planilha de destino, digamos na linha 2:

 = ROUND (PEQUENO (Folha1! $ A $ 1: $ E $ 1 + COLUNA (Folha1! $ A $ 1: $ E $ 1)/100000000, COLUNA ()), 6) 

Você precisa inserir a fórmula como uma fórmula de matriz, ou seja, pressione Ctrl-Shift-Enter.

Caso você o coloque em outra coluna além da coluna um, é necessário substituir COLUMN() por COLUMN()-COLUMN($A$2)+1, em que $A$2deve ser uma referência à própria célula.

Essa fórmula retornará o menor número do seu intervalo - se você copiar as próximas 4 colunas, receberá sua lista de números de pedidos.

Para traduzir isso de volta para o número da coluna, precisamos executar duas etapas:

  1. Descubra o número da coluna:
    Isso pode ser feito com esta fórmula:
     = MATCH (SMALL (Folha1! $ A $ 1: $ E $ 1 + COLUNA (Folha1! $ A $ 1: $ E $ 1)/100000000, COLUNA ()), Folha1! $ A $ 1: $ E $ 1 + COLUNA (Folha1! $ A $ 1: $ E $ 1)/100000000,0) 
    
    - novamente, para ser inserido como fórmula de matriz. Se os dados de origem não iniciarem na coluna A, você precisará adicionar +COLUMN(Sheet1!$A$1)-1, em que $ A $ 1 precisa ser substituído pela célula mais à esquerda dos dados de origem.
  2. Converta o número da coluna em uma letra:
    Supondo que seu número de coluna da etapa 1 esteja na célula A6, essa fórmula fará o trabalho:
 = ESQUERDA (ENDEREÇO ​​(1, A6,2), PESQUISA ("$", ENDEREÇO ​​(1, A6,2)) - 1) 

Claro, você também pode combinar os passos 1 e 2, isso resultará nesta megaformula:

 = ESQUERDA (
 ENDEREÇO ​​(
 1, 
 PARTIDA (
 PEQUENA) (Folha1! $ A $ 1: $ E $ 1 + COLUNA (Folha1! $ A $ 1: $ E $ 1)/100000000, COLUMN ()), 
 Folha1! $ A $ 1: $ E $ 1 + COLUMN (Folha1! $ A $ 1: $ E $ 1)/100000000, 
 0), 
 2), 
 BUSCA (
 "$", 
 ENDEREÇO ​​(
 1, 
 MATCH (
 PEQUENA (Folha1! $ A $ 1: $ E $ 1 + COLUNA (Folha1! $ A $ 1: $ E $ 1)/100000000, COLUNA ()), 
 Folha1! $ A $ 1: $ E $ 1 + COLUMN (Folha1! $ A $ 1: $ E $ 1)/100000000,0), 
 2) 
) -1 
) 
Mais uma vez, para ser inserido como fórmula de matriz.

0
Peter Albert