Esas cosas siempre tienen un grado de complejidad si lo quieres hacer con fórmulas, y está bien para quienes dominan las formulas madre de Excel y/o trabajan con datos que cambian constantemente en vivo. PERO no creo que sea tu caso SALVO quieras aprender y profundizar en el uso de fórmulas y matrices.
Lo normal y que cualquiera puede hacer es ordenar los datos con la herramienta Ordenar, tanto mejor si tu "tabla de datos" es una Tabla porque ya tiene los botones de Ordenar y Filtrar, en tu imagen de arriba solo resta poner =L131 y =L132 respectivamente:
Sin Tabla (empezar la selección desde la celda o columna que será la base de la ordenación):
Con Tabla:
Desventajas:- No son automáticas, debes asegurarte de volver a Ordenar luego de ingresar/cambiar los datos
- Modifican el orden original de tus datos, dependerá si te importa o no cambiar el orden perdiendo el orden original
Ventajas:- Es facilísimo
---
Ahora bien,
si lo quieres hacer con fórmulas hay pasos que tienes que seguir y dominar:
1. Obtener el número de orden que ocupa un valor dentro de un rango de valores ordenados, en tu caso de mayor a menor. La forma convencional de hacer esto es tomar un valor dentro de un rango de valores y contar la cantidad de valores que son mayores, si dicha cantidad es 0 implica que estamos ante el valor mayor, si la cantidad es 1 será el segundo mayor, y así sucesivamente. Entonces, si le sumamos a esa cantidad: +1 obtendremos su número de orden, porque si la cantidad fue 0 implica que es el primer lugar, si la cantidad fue 1 implica que es el segundo lugar, etc
2. Valores repetidos, si es el caso hay que decidir que orden tendrán los valores repetidos, por lo general se suele usar una condición adicional que actúe sobre otra columna, por ejemplo si fuera una tabla de nombres y apellidos, y se desea ordenar por los nombres:
carlos | lópez
maría | tórres
carlos | flores
- Tenemos problemas con el nombre "carlos" ya que se repite, Si hiciéramos el paso 1 obtendríamos:
carlos | lópez | 1
maría | tórres | 3
carlos | flores | 1
- La solución está en hacer una condición adicional para comprobar el orden de los apellidos dándoles valores igual que en el paso 1 pero sin sumarles el +1, y al final contar/sumar ambos números de orden para obtener un número de orden sin repeticiones
carlos | lópez | 2
maría | tórres | 3
carlos | flores | 1
- Para tu caso puedes comprobar el orden de los valores de la columna "ataque tipo avanzado" correspondiente a los valores repetidos, justo como se hizo con el ejemplo de nombres y apellidos
- Su funcionamiento es sencillo, solo hay que despejar los valores repetidos y al resultante asignarle un número de orden, esta vez no sumaremos el +1 para que empiece en 0, al final se suma ese orden obtenido al número de orden del paso 1:
-- Despejado
carlos | lópez
carlos | flores
-- Número de orden con respecto al apellido
carlos | lópez | 1
carlos | flores | 0
-- Sumado al número de orden del paso 1:
carlos | lópez | 1 + 1
carlos | flores | 0 + 1
-- De esa manera "carlos flores" queda en primer lugar (1) y "carlos lópez" en segundo (2)
3. Localizar el valor de la columna requerida a partir de lo calculado anteriormente, en los pasos anteriores se obtiene el número de orden para cada fila, ya solo falta obtener que número de fila tiene el número de orden 1 y 2
carlos | lópez | 2
maría | tórres | 3
carlos | flores | 1
- Tan simple como decirle que primero quiero el valor de la columna de apellidos cuyo número de orden es 1, y luego quiero el valor de la columna apellidos cuyo número de orden es 2. Listo, esos serán los 2 primeros apellidos ordenados por nombres de forma ascendente.
carlos |
flores | 1
carlos |
lópez | 2
---
Resolución:Paso 1: Basta con usar una columna auxiliar, por ejemplo la que está al lado (M131) y aplicar la función
=CONTAR.SI($L$131:$L$137;">"&L132)+1Paso 2: Como hay valores repetidos modificamos la fórmula con la segunda condición:
=CONTAR.SI($L$131:$L$137;">"&L132)+1+CONTAR.SI.CONJUNTO($L$131:$L$137;L132;$B$131:$B$137;"<"&B132)- Con CONTAR.SI.CONJUNTO se pueden hacer varias condiciones que van reduciendo el rango de datos para la subsiguiente condición, lo primero era despejar los repetidos, eso se hace con la parte en rojo:
CONTAR.SI.CONJUNTO($L$131:$L$137;L132;$B$131:$B$137;"<"&B132)- Una vez despejado/marcado los repetidos aplicamos la condición:
$B$131:$B$137;"<"&B132 que similar al paso 1 ordenará los valores de la columna "ataque tipo avanzado" pero esta vez de menor a mayor (a-z), todo esto considerando el despeje anterior, es decir, solo las filas correspondientes a los repetidos, de esta manera se obtiene un segundo orden que se puede sumar al CONTAR.SI inicial y tener un orden secuencial sin repeticiones SALVO que también haya repeticiones en la columna de la segunda condición XD, si es el caso hay que agregar más condiciones con más columnas o especificar una condición personal.
Paso 3: Primero hay que localizar el número de fila dentro de un rango que tiene el número de orden 1 (el valor mayor), esto se hace con la fórmula COINCIDIR, una vez obtenido el número de fila solo queda obtener el valor de la columna "ataque tipo avanzado" con la fila hallada con COINCIDIR, esto se hace con INDICE, lo mismo para el número de orden 2 (segundo mayor) todo sería:
=INDICE(B131:B137;COINCIDIR(1;M131:M137;0))=INDICE(B131:B137;COINCIDIR(2;M131:M137;0))- Pues ya está, esto todo SI vas a usar la columna auxiliar que se ha creado en M131, pero si no quieres columna auxiliar se tiene que juntar todo, usar matrices donde las fórmulas esperan valores simples y obviamente presionar Ctrl + Shift + Enter para que haga valer las matrices que se han puesto en lugar de los valores simples, en resumen:
- La fórmula es:
=INDICE(B131:B137;COINCIDIR({1;2};CONTAR.SI(L131:L137;">"&L131:L137)+1+CONTAR.SI.CONJUNTO(L131:L137;L131:L137;B131:B137;"<"&B131:B137);0))- Como ves, se han reemplazado todas las referencias a valores simples por rangos para que trabaje de manera matricial, incluso se ha reemplazado el número 1 o 2 que antes se usaba en dos fórmulas aparte, por una matriz manual de tal manera que el resultado a devolver también sea una matriz de dos filas que son justo las filas que hay que seleccionar para poner el resultado en ellas.
- Si no se conoce bien las matrices no se va a entender XD, pero puedo dar una explicación sencilla con la parte de CONTAR.SI(L131:L137;">"&L131:L137) que supongo que es una parte fácil de entender.
-- La función CONTAR.SI(rango;condición) tiene una par de argumentos base, primero un rango y luego la condición, dicha condición que se espera de valor simple, sin embargo yo le he puesto una condición de rango, por haber hecho esto ya se convierte en una fórmula matricial al que se tiene que presionar Ctrl + Shift + Enter y que además devuelve la matriz resultante.
-- Lo normal sería que dicha función tome un rango y una condición, lo que hará es tomar uno a uno los valores del rango, compararlos usando la condición y si se cumple cuenta +1:
CONTAR.SI(A1:A3;"<"&A1)
carlos | lópez | 0
maría | tórres | 2
carlos | flores | 0
1. Toma el primer miembro del rango (A1) y compara si es menor que A1, si es menor +1, sino 0
2. Toma el segundo miembro del rango (A2) y compara si es menor que A1, si es menor +1, sino 0
3. Toma el tercer miembro del rango (A3) y compara si es menor que A1, si es menor +1, sino 0
En los casos anteriores da 0;0;0 se cuentan y da 0, ahora la siguiente:
CONTAR.SI(A1:A3;"<"&A2)
1. Toma el primer miembro del rango (A1) y compara si es menor que A2, si es menor +1, sino 0
2. Toma el segundo miembro del rango (A2) y compara si es menor que A2, si es menor +1, sino 0
3. Toma el tercer miembro del rango (A3) y compara si es menor que A2, si es menor +1, sino 0
En los casos anteriores da 1;0;1 se cuentan y da 2
Como se ve se tiene que poner una fórmula por fila, es como hacer un bucle simple:
=CONTAR.SI(A1:A3;"<"&A1)
rango = ["carlos", "maría", "carlos"]
valor = rango[0] //"carlos"
contar1 = 0
for (var i = 0; i < rango.length; i++) {
if (rango[i] < valor) {
contar1++
}
}
valor = rango[1] //"maría"
contar2 = 0
for (var i = 0; i < rango.length; i++) {
if (rango[i] < valor) {
contar2++
}
}
valor = rango[2] //"carlos"
contar3 = 0
for (var i = 0; i < rango.length; i++) {
if (rango[i] < valor) {
contar3++
}
}
Devuelve un solo valor cada uno (contar1, contar2, contar3) y la comparación es simple, rango vs valor, y hay que hacer lo mismo con cada valor. Al final devuelve contar1 = 0; contar2 = 2; contar 3 = 0
Pero si usamos otro rango en lugar del valor simple (A1 => A1:A3):
CONTAR.SI(A1:A3;"<"&A1:A3)
rango1 = ["carlos", "maría", "carlos"]
rango2 = ["carlos", "maría", "carlos"]
contar = [0,0,0]
for (var i = 0; i < rango1.length; i++) {
for (var j = 0; j < rango2.length; j++) {
if (rango1[j] < rango2[i]) {
contar[i]++
}
}
}
Devuelve un array contar = [0,2,0], más simple, más directo PERO devuelve una matriz de valores que se deben tratar como tales
---
Otra opción es utilizar las nuevas fórmulas de filtro dinámicas que trae las nuevas versiones de Excel, creo que del 2021 para arriba
Autor
En línea
