Excel

Ordeneu el text i els números amb la fórmula

Sort Text Numbers With Formula

Fórmula Excel: ordeneu el text i els números amb la fórmulaFórmula genèrica
= COUNTIF (data,'<='&A1)+( COUNT (data)* ISTEXT (A1))
Resum

Per ordenar dinàmicament les dades amb números i text en ordre alfabètic, podeu utilitzar una fórmula per generar un rang numèric en una columna auxiliar, i després utilitzar INDEX i MATCH per mostrar valors basats en el rang. A l'exemple que es mostra la fórmula a C5 és:



quina és la funció country a Excel
 
= COUNTIF (data,'<='&B5)+( COUNT (data)* ISTEXT (B5))

on 'dades' és rang anomenat B5: B13.

Explicació

Aquesta fórmula primer genera un valor de classificació mitjançant una expressió basada en COUNTIF:





 
= COUNTIF (data,'<='&B5)

el qual és aquí s’explica amb més detall . Si les dades contenen tots els valors de text o tots els valors numèrics, la classificació serà correcta. Tot i això, si les dades inclouen tant text com números, hem de 'canviar' el rang de tots els valors de text per tenir en compte els valors numèrics. Això es fa amb la segona part de la fórmula aquí:

 
+( COUNT (data)* ISTEXT (B7))

Aquí, fem servir la funció COUNT per obtenir un recompte de valors numèrics a les dades, i després multiplicar el resultat pel resultat lògic d’ISTEXT, que prova si el valor és text i retorna TRUE o FALSE. Això cancel·la efectivament el resultat de COUNT quan treballem amb un número a la fila actual.



Gestió de duplicats

Si les dades contenen duplicats, la fórmula es pot modificar com es mostra a continuació per assignar un rang seqüencial als valors que apareixen més d'una vegada:

 
= COUNTIF (data,'<'&B5)+( COUNT (data)* ISTEXT (B5))+ COUNTIF ($B:B5,B5)

Aquesta versió ajusta la lògica de la funció COUNTIF inicial i afegeix un altre COUNTIF amb una referència en expansió per incrementar els duplicats.

Mostra els valors ordenats

Per recuperar i mostrar valors ordenats alfabèticament mitjançant el valor de classificació calculat, E5 conté el següent Fórmula ÍNDEX i MATCH :

 
= INDEX (data, MATCH ( ROWS ($E:E5),rank,0))

on 'dades' és rang anomenat B5: B13 i 'rang' és l'interval anomenat C5: C13.

Per obtenir més informació sobre com funciona aquesta fórmula, vegeu l'exemple aquí .

Tractament dels espais en blanc

Les cel·les buides generaran un rang zero. Suposant que voleu ignorar les cel·les buides, funciona bé perquè la fórmula INDEX i MATCH anterior comença a 1. Tanmateix, veureu errors # N / A al final dels valors ordenats, un per a cada cel·la buida. Una manera fàcil de gestionar-ho és embolicar la fórmula INDEX i MATCH a IFERROR així:

 
= IFERROR ( INDEX (data, MATCH ( ROWS ($E:E5),rank,0)),'')
Autor Dave Bruns


^