Como renumerar uma coluna classificada para iniciar do zero no Excel

Question

Eu tenho 2 colunas de números no excel. Depois de classificá-los, quero converter os números em números ordenados de zero, que o número mínimo converte em 0, o segundo número converte em 1 e .... Por exemplo, eu tenho estas duas colunas:

eeuqueroumresultadocomoeste:

Alguém por favor pode me ajudar?!

Obrigado

    

Answer 1

Assumindo que seus dados são (conforme mostrado) em A1: B3

1. Em D1 , digite =SUMPRODUCT((A1>$A$1:$B$3)/COUNTIF($A$1:$B$3,$A$1:$B$3))

2. Preencha a fórmula até D3, e depois até E3.

Se você quiser substituir os dados classificados originais de forma destrutiva, prossiga para a etapa 3 opcional:

3. Selecione + Copiar D1: E3 - > Cole como valores em A1.

Explicação da fórmula :

Existem duas partes fundamentais para entender essa fórmula.

1. (A1>$A$1:$B$3) gera uma matriz de valores booleanos: TRUE para valores em A1: B3 que são menores que A1, FALSE para aqueles maiores que A1. Como essa matriz está sendo dividida na fórmula, o Excel converte esses valores booleanos em uns e zeros.
2. SUMPRODUCT(1/COUNTIF($A$1:$B$3,$A$1:$B$3)) é uma construção usada para contar valores exclusivos em uma matriz. Isso funciona ponderando cada valor em 1/(the number of times the number appears in the array) . Isso garante que a soma de todos os valores associados a esse valor seja somada a 1 . Pense desta maneira. Se X aparecer n vezes e cada vez for contado como 1/n , a soma dessas contagens será n*(1/n)=1 . Ou seja, cada valor único é contado apenas uma vez.

Portanto, com isso em mente, considere a fórmula de exemplo =SUMPRODUCT((B1>$A$1:$B$3)/COUNTIF($A$1:$B$3,$A$1:$B$3)) , que atribui o ID exclusivo 3 ao valor 2000 no exemplo. Passo a passo, é assim que a fórmula é avaliada:

1. =SUMPRODUCT((2000>{1001,1001,1002,2000,1002,1003})/COUNTIF($A$1:$B$3,$A$1:$B$3))
2. =SUMPRODUCT({TRUE,TRUE,TRUE,FALSE,TRUE,TRUE}/COUNTIF($A$1:$B$3,$A$1:$B$3))
3. =SUMPRODUCT({TRUE,TRUE,TRUE,FALSE,TRUE,TRUE}/COUNTIF({1001,1001,1002,2000,1002,1003},$A$1:$B$3))
4. =SUMPRODUCT({TRUE,TRUE,TRUE,FALSE,TRUE,TRUE}/{2,2,2,1,2,1})
5. =SUMPRODUCT({1/2,1/2,1/2,0/1,1/2,1/1})
6. =3

Answer 2

1. duplicar a coluna A na coluna D
2. duplique todos os valores da coluna B na coluna D, com o destino começando na primeira célula vazia abaixo da coluna A (agora você tem todos os valores na coluna D)
3. selecione o conteúdo da coluna D e remova duplicatas
4. digite valor 0 na célula E1
5. digite valor 1 na célula E2
6. selecione as células E1: E2
7. clique duas vezes no quadrado pequeno que aparece no canto inferior direito do limite de seleção (a sequência de números agora é estendida para o último número)
8. na célula tipo F1 =VLOOKUP(A1;$D:$E;2;FALSE)
9. concentre a célula G1 e pressione Ctrl + R
10. selecione as células F1: G1
11. clique duas vezes no quadrado pequeno que aparece no canto inferior direito do limite de seleção

Agora você tem seus resultados nas colunas F e G. (Ignore os valores restantes de #N/A na parte inferior dessas colunas.)

Sinta-se à vontade para perguntar se algo não está claro.