Uso de la función JERARQUIA combinada con SUMAPRODUCTO - Ranking de listas con más de un criterio

viernes, mayo 26, 2006

A tono con el mundial de fútbol que se acerca, uno de mis lectores me pregunta como es posible organizar una tabla de posiciones usando MS Excel, de manera que si hay empate en la cantidad de puntos, la posición se defina de acuerdo a la cantidad de goles a favor. En caso de persistir la igualdad, la posición se definiría por mayor diferencia de goles. Todo esto utilizando solamente fórmulas!!
Ya hemos visto en una entrada anterior sobre el uso de la
función JERARQUIA (RANK en inglés), para dar un número de orden a los miembros de una lista de acuerdo a su posición relativa. En una nota adicional sobre la función JERARQUIA (RANK) vimos que esta función tiene un problema: en caso de "empate" ambos miembros en la lista reciben el mismo número de orden. Este "problema" puede ser usado a nuestro favor para resolver la pregunta sobre la tabla de posiciones.

Supongamos esta
tabla de un campeonato imaginario




Como podemos ver los equipos 4,5 y 6 comparten la cuarta posición; los equipos 7,8 y 9 comparten la quinta posición

Empezamos creando columnas auxiliares (K, L y M), para facilitar el cálculo.



En la columna auxiliar K anotamos la fórmula =JERARQUIA(J5,$J$4:$J$19) para calcular el número de orden de acuerdo a la cantidad de puntos de cada equipo.

En la columna L anotamos =SUMAPRODUCTO((J5=$J$4:$J$19)*(G5<$G$4:$G$19)) para generar un ranking entre los equipos de igual cantidad de puntos, de acuerdo a los goles a favor.

En la columna M anotamos =SUMAPRODUCTO((J5=$J$4:$J$19)*(I5<$I$4:$I$19)) para general el ranking entre los equipos con igual cantidad de puntos, de acuerdo a la diferencia de goles.
Finalmente, combinamos las tres fórmulas en esta "mega-fórmula" que anotamos en la columna A
=JERARQUIA(J5,$J$4:$J$19)+SUMAPRODUCTO((J5=$J$4:$J$19)*(G5<$G$4:$G$19))+SUMAPRODUCTO((K5=$K$4:$K$19)*(L5=$L$4:$L$19)*(M5>$M$4:$M$19))

Una vez obtenido el ranking de los equipos de acuerdo a los tres criterios (puntaje, goles a favor y diferencia de goles) podemos ordenar nuestra tabla utilizando el menú Ordenar de acuerdo a la columna del ranking (A).


Esta fórmula funciona de la siguiente manera:


- el primer miembro, la función JERARQUIA, calcula el número de orden de acuerdo al puntaje.

- El segundo miembro, la primera función SUMAPRODUCTO, calcula un número de orden interno sólo para los equipos con el mismo puntaje. Este orden interno comienza con el número cero. Si el puntaje del equipo evaluado es único, esta fórmula da cero.

- El tercer miembro, la segunda función SUMAPRODUCTO, calcula un número de orden interno sólo para los equipos que tienen el mismo puntaje y la misma cantidad de goles a favor. Si esto no se cumple, da como resultado cero.

Esta fórmula puede ser escrita en forma más compacta utilizando nombres en lugar de rangos explícitos.



Categorías: Funciones&Formulas_

Technorati Tags: ,

77 comments:

Willem F. Schol 31 mayo, 2006 23:18  

Excelente maestro!! te pasaste! sirvio muy bien! te aviso si le encuentro alguna falencia jajaja nooo, debe de estar bien en su 100%! Gracias!

Oscar,  02 junio, 2007 04:10  

SENSACIONAL!!!! Es justo lo que necesitaba, ya que estaba utilizando Jerarquía para calcular unos cocientes del sistema d'hont y tenía un incoveniente con aquellos que eran iguales y debía ordenarlos de acuerdo a la cantidad de votos totales de sus respectivas listas.

Te puedo enviar el libro que armé para que lo estudies. Funciona perfectamente.

Saludos

Oscar.

Jorge L. Dunkelman 02 junio, 2007 09:03  

Hola Oscar
me alero que te haya sido útil. Enviame el libro y si resulta de interés general lo publicaré en el blog (siempre y cuando estés de acuerdo).

Alex 25 septiembre, 2007 22:36  

Bien, pues estoy intentando reacer esta formula en mi clasificación y no consigo el resultado esperado.

Mis tres formulas son:
A=JERARQUIA(W209;$W$209:$W$228)
B=SUMAPRODUCTO((W209=$W$209:$W$228)*(AB209<$AB$209:$AB$228))
C=SUMAPRODUCTO((W209=$W$209:$W$228)*(AD209<$AD$209:$AD$228))

La tercera es la que falla, y aún no entiendo por qué ...
A B C D
15 0 0 15
6 0 0 6
6 5 4 15
4 0 0 4
17 0 1 18
6 6 6 18
15 1 1 17
17 1 2 20
20 0 0 20
6 1 1 8
6 6 4 16
17 2 0 19
1 0 0 1
6 8 8 22
6 1 6 13
5 0 0 5
2 0 1 3
6 1 2 9
2 0 0 2
6 1 2 9

Datos origen:
W AB AD
4 6 0
5 7 3
5 4 -1
8 5 3
2 4 -2
5 3 -2
4 4 -1
2 2 -4
1 1 -5
5 5 2
5 3 -1
2 1 -1
10 11 8
5 2 -3
5 7 -2
6 9 5
9 6 0
5 5 0
9 6 1
5 5 0

Jorge L. Dunkelman 04 octubre, 2007 21:59  

Alex,
mandame el archivo con el ejemplo.

Samuel Delgado 18 junio, 2008 11:33  

Jerarquia Solicitada

Tengo un detalle que me gustaria compartirlo, en este momento la Jerarquia que me da su ejemplo en cuanto a un empate en puntos es que toma como jerarquia principal los partidos ganados y despues si los goles a favor. En el caso del Rentado Colombiano , no es asi, aca el privilegio es para mayor numero de Goles Anotados y mayor recibidos es decir como ejemplo:

Nombre_ Eq PG PE PP GF GC DG
1.Equipo A 7 4 7 20 17 3
2.Equipo B 6 7 5 25 22 3

Aqui Observamos que la Jerarquia la tiene quien haya ganado mas , y la diferencia de goles es la misma, pero aca en Colombia el Privilegio se le da al que tenga mayor numero de Goles Anotados y mayores recibidos en caso de empates en Diferencia de Goles.

Como soluciono el problema?

Jorge L. Dunkelman 19 junio, 2008 23:53  

Hola Samuel

no entendí del todo como funciona (también goles en contra suman puntos?), pero lo que puedes hacer es dar un peso distinto de acuerdo a la prioridad. Esto quiere decir que creas un número ponderado. Por ejemplo, si lo más importante son los puntos obtenidos, los multiplicas por 10000. El segundo factor en importancia, digamos los goles a favor, los multiplicas por 1000 y el tercer factor por 100. Todos estos productos los sumas en un número y sobre él calculas la JERARQUIA.

Samuel Delgado 20 junio, 2008 00:36  

Don Jorge, el detalle es que la prioridad en caso de empate en puntos aca en Colombia es la diferencia de goles , pero en caso de persistir todavia igualdad en este Item se desempata tomando cual de esos dos equipos tenga el mayor numero de Goles Anotados y el Mayor de recibidos

Jorge L. Dunkelman 20 junio, 2008 06:57  

Hola Samuel
como te decía en comentario anterior. A cada equipo le calculamos un factor que pondera los distintos parámetros segun la prioridad:

Puntos X 100000 + DG X 10000 + (GF+GC)*1000

En tu ejemplo, el equipo A tiene un factor de 2567000 y el equipo B uno de 2577000.
En una columna auxiliar calculamos la posición relativa de cada equipo con la función JERARQUIA. El equipo A recibe una JERRQUIA de 2 y el B de 1.
En una tabla auxiliar (que será la visible) ordenamos las líneas de la tabla usando una fórmula con INDICE y COINCIDIR.
Aquí puedes descargar un ejemplo que te preparé.

Carlos Rojas,  26 julio, 2008 08:51  

Hola Jorge. He estado leyendo muy atentamente lo de las tablas de posiciones que usted publica. la verdad de las cosas es que yo no se practicamente nada de todas esas funciones pues uso exel solo para lo basico. el caso es que baje esta tabla de posiciones para mas de un criterio y al poner los resultados no se modifican los partidos ganados, perdidos o empatados, sino solo los goles. agradeceria pudiese contestarme pues me interesa mucho un documento de estas caracteristicaas

Jorge L. Dunkelman 26 julio, 2008 10:07  

Hola Carlos

puedes mandarme tu archivo para ver dónde pueda estar el problema.
También puedes descargar el ejemplo simplicado en esta nota.

Carlos Rojas,  31 julio, 2008 05:08  

Gracioas Jorge pero ya he solucionado el problema. tenia que bajar la seguridad de las macros. en realidad era un tramite que habia pasado por alto. Gracias de todas maneras y por la prontitud de tu respuesta.

Anónimo,  06 octubre, 2008 23:45  

bueno yo estoy buscando la definicion de como funciona el orden de procedencia!
pueden ayudarme!!!

Jorge L. Dunkelman 07 octubre, 2008 19:18  

A qué orden de procedencia te refieres? De las operaciones en Excel?

Anónimo,  03 abril, 2009 09:49  

Hola Excelente la página y la elegancia de sus soluciones.

Sólo una cosa, en el tercer sumando de la "megafórmula", no sería más adecuado para seguir el mismo criterio que en segundo sumando, utilizar: +SUMAPRODUCTO((K5=$K$4:$K$19)*(L5=$L$4:$L$19)*(I5<$I$4:$I$19)) en vez de =JERARQUIA(J5,$J$4:$J$19)+SUMAPRODUCTO((J5=$J$4:$J$19)*(G5<$G$4:$G$19))+SUMAPRODUCTO((K5=$K$4:$K$19)*(L5=$L$4:$L$19)*(M5>$M$4:$M$19))

Un saludo.

Alberto,  19 noviembre, 2009 00:40  

Hola, me ha gustado mucho esto. Pero no se que link dar para descargarme el excel, porque me gustaria ver las formulas mas de cerca y modificarlas a mis necesidades. Si me puedes dar el link para descargarlos, lo agradeceria mucho. Enhorabuena por tus explicaciones. Saludos.

Jorge L. Dunkelman 19 noviembre, 2009 07:00  

Alberto,
en las próximas horas estaré arreglando el enlace.

Anónimo,  27 diciembre, 2009 07:33  

Hola, excelente página

Tengo un problema cuando hay igualdad en todos los criterios de desempate, ya sean pts, dg, gf. Al suceder esto, en la TABLA DE POSICIONES se visualiza solo el 1er equipo que aparece en la TABLA AUXILIAR y los demás aparecen como "#N/A" (incluidos los demás datos del equipo: pts, gf, gc...etc). A que se debe esto? estoy haciendo algo mal o hay manera de solucionarlo?

A modo de ej:

si en un grupo de 4 equipos todos empatan 0 a 0.

Hay igualdad en todos los criterios de desempate, por lo tanto en posicion todos van a tener el valor 1.
Es ahí cuando aparece el error de "#N/A" en la Tabla de Posiciones. Hay manera de solucionarlo?

Muchas gracias por la pagina, espero puedas ayudarme!

Jorge L. Dunkelman 27 diciembre, 2009 17:18  

Hola,

te sugiero que veas el modelo que muestro en esta nota.
También podés mandarme el archivo con el que estás trabajando para que le de un vistazo.

Ezequiel,  13 febrero, 2010 05:33  

Hola Jorge..

Quiza comentaste esto en alguna de tus publicaciones, pero como no lo encuentro te pregunto.

Siguiendo con este ultimo caso en el que todos los equipos empatan 0-0 por ejemplo.

Seria posible, al factor de ponderancia que propones, restar el valor de la fila donde se encuentra cada equipo?.

Por ejemplo:

Puntos X 100000 + DG X 10000 + (GF+GC)*1000 - FILA()

Donde el valor de la fila quitaria el empate entre los otros datos, generando un numero unico para cada equipo.

En este caso, el ultimo factor que daria la jerarquia seria la posicion original de cada equipo. Cuando mas abajo se encuentre la fila de ese quipo, menor sera el valor de la formula.

Todavia no lo probe, pero seria una opcion viable?.

Desde ya, gracias por tu atencion..

Jorge L. Dunkelman 13 febrero, 2010 07:17  

Si, es posible.
En (GF+GC)*1000 ¿no tendría que ser (GF-GC)*1000)?

Ezequiel,  13 febrero, 2010 07:54  

Si, tendria que ser asi, es que yo solamente copie el ejemplo q diste mas arriba para mostrar a lo q me referia, no me fije en ese valor porque no lo tengo en cuenta.

Bueno ya despejaste mi duda, intentare aplicarlo. Seguramente pronto me dare una vuelta con otra duda que me surja.

Gracias, que sigas bien.

lazonaps2 31 mayo, 2010 07:56  

Amigo el enlace de la tabla esta malo, lo podrias arreglar??? saludos

Jorge L. Dunkelman 31 mayo, 2010 20:22  

Lamentablemente no guardé una copia del archivo. Te sugiero descargar el archivo en el enlace de mi comentario del 20 de junio del 2008.

Reynaldo,  10 junio, 2010 09:55  

Buenas madrugadas, para mí, jeje. Hey Dr. Dunkelman, gracias, sabia que no me defraudaría su blog. Justamente estoy haciendo un control para el mundial 2010 (existen varios y muy buenos pero lo que me interesa es saber aplicar esa funcionabilidad en excel), comencé tarde pues acabo de terminar el primer semestre de este año en la U y no habia tenido tiempo. En realidad estoy aprendiendo lo que comenta de jerarquizar tablas ya que no tenía ni idea y en fín otras dudas porque en todos los calendarios que hay en la WEB no puedes ver las funciones utilizadas y eso complica el aprendizaje.
Ayudeme con lo siguiente ¿COMO PUEDO MANTENER COMO UN SOLO BLOQUE UNA IMAGEN (bandera del pais) CON TEXTO (nombre del pais) dentro de la misma celda, para que aparezca la bandera del pais con su respectivo nombre cuando se actualice la tabla de posiciones en un grupo X? De antemano GRACIAS.

Jorge L. Dunkelman 11 junio, 2010 08:51  

Reynaldo,
las banderas son imágenes y no están en la celda sino que "flotan" sobre la hoja (como los gráficos). Una posibilidad es que tengas imágenes de las banderas que incluyan el nombre del país. Otra es usar una celda para la bandera (haciendo coincidir el tamaño de la imagen con el de la celda) y en la celda contigua poner el nombre del país.

Carlos 03 noviembre, 2010 21:55  

Hay un error, el equipo 8 deberia estar arriba del equipo 7 por diferencia de goles

Jorge L. Dunkelman 04 noviembre, 2010 07:31  

Así parece ser. Estaré publicando la corrrección.

Samuel Delgado 08 noviembre, 2010 09:57  

Maestro el link esta caido, Gracias por el aporte

Jorge L. Dunkelman 11 noviembre, 2010 18:54  

Trataré de reemplazar el enlace. Mientras tanto puedes descargar el ejemplo de esta nota.

Anónimo,  23 septiembre, 2011 19:47  

¿Qué pasó con este tema? Estoy muy interesado en hacer una tabla de posiciones de 200 equipos y aún no logro hacerlo con estas prioridades:

> Pts., > diferencia de goles, > Goles a favor, < goles en contra, ordenado el nombre del equipo de A-Z.

¿Alguien sigue viendo esto para que pueda hecharme la mano?

Jorge L. Dunkelman 23 septiembre, 2011 19:53  

Estimado,

esta nota tiene más de cinco años!! Bien, fijate en esta nota.
Si tienes buenas nociones de fórmulas y Vba (macros) puedes adaptar este modelo para 200 equipos (donde en el munfo existe semejnate torneo!)

Colombiano 20 octubre, 2011 12:47  

Hola Jorge, he estado trabajando en una tabla de posiciones para las eliminatorias al mundial de la Conmebol, hace 4 años lo hice con base en tus recomendaciones de éste y otros artículos relacionados. Este año decidí hacer nuevamente el archivo tratando de encontrar formulaciones alternativas, creo que por fin quedó listo, me gustaría saber si le puedes echar una ojeada y darme tu opinión al respecto

Saludos

Jorge L. Dunkelman 20 octubre, 2011 13:23  

Lo puedes mandar a la dirección que aparece en el enlace Ayuda.

sergio,  01 noviembre, 2011 11:28  

hola buenas estoy intentando haecr una tabla de clasificacion tal cual tines en el ejemplo ,lo que esa no me vale por que solo tiene 6 equipos me gustaria tener una de 12 equipos me sería de gran ayuda si pudieses dejarme algun ejemplo para poder descargar muchas graciass la verda que no tengo ni idea de como hacerla un saludo

Jorge L. Dunkelman 01 noviembre, 2011 17:33  

Sergio

la tabla en la nota tiene 15 equipos

sergio,  03 noviembre, 2011 02:02  

si pero el enlace me lleva a otra página q tiene contenidos xxx

Jorge L. Dunkelman 03 noviembre, 2011 06:41  

Si, lo descubrí después de responder a tu comentario. He removido el enlace. Lamentablemente no tengo una copia del archivo. Espero rehacerlo durante el fin de semana y volver a postearlo.

Sergio,  03 noviembre, 2011 21:25  

Muchas gracias me vendría de mucha ayuda,enorabuena por la página me es de gran ayuda saludoss

JJOTALB 28 febrero, 2012 02:10  

Amigo, e realizado la tabla con los articulos publicados, pero quisiera agregarle un 3 criterio, en este momento tengo, puntos, juego limpio, goles diferencia, como agrego uno mas que seria, mayoría de goles marcados....

Jorge L. Dunkelman 29 febrero, 2012 19:29  

Um método es darle un peso distinto a cada criterio de manera de obtener un índice que podamos usar como argumento en la función JERARQUIA.

vitorique,  02 marzo, 2012 03:23  

dos columnas
adicionales
A B «---- Información de la tabla original ----» M N O
↑ 1 ↑
↑ 2 ↑
=O13+SUMAPRODUCTO((O13=O$13:O$22)*(N13>N$13:N$22)) 3 ↑
etc. ↑
=K13+L13+SUMAPRODUCTO((K13=K$13:K$22)*(L13=L$13:L$22)*(M13>M$13:M$22))

Con estos cambios se resuelve el problema que muchos cibernautas han encontrado
al aplicar las fórmulas publicadas. Este cambio introduce un cuarto criterio que elimina
la posibilidad del error "#N/A" y asegura un buen funcionamiento del proyecto.
Elimina también la "mega-fórmula" porque usa los calculos previamente realizados
en las columnas K y L por lo que es mucho mas corta y evita recalcularlas.
Espero que los aficionados al football disfruten haciendo sus propios fixtures, ahora que
se acerca el mundial de Brasil 2014 y los partidos clasificatorios para ese evento.
Aunque algo escueta, ojalá haya sido bastante didactica mi explicación.
Saludos y buena suerte a todos.

JJOTALB 02 marzo, 2012 19:37  

vitorique.... no entiendo mucho tu explicación, efectivamente e utilzado

=K13+L13+SUMAPRODUCTO((K13=K$13:K$22)*(L13=L$13:L$22)*(M13>M$13:M$22))

donde K es rank; L es RankgGF; M es Rank GD
hay siguen los mismos tres criterios de la formula del post, no veo como clocar el 4 criterio. podrias ser un poco mas grafico.

en mi caso los criterios son:
1: Rank puntos "funcionando"
2: Raak Juego limpio "funcionando"
3: Rank Goles Diferencia "Funcionando"
4: rank Goles marcados "no lo logro poner"

si quieres te puedo mandar algun correo personal o algo para q me ayuden... Gracias

vitorique,  03 marzo, 2012 15:17  

Es cierto, no puse atención a la vista previa, y me disculpo por eso.
La primera fórmula debe colocarse en la columna A y la segunda en la columna O luego en la columna N va el cuarto criterio que en este caso son los números consecutivos desde el 1 hasta la cantidad de participantes que haya y su objetivo es solamente evitar el error "#N/A".
Pero podrías utilizarlo a tu elección.
Ojalá te haya ayudado.
Saludos y nuevamente mis sinceras disculpas del caso

vitorique,  03 marzo, 2012 15:43  

No quiero olvidar de saludar, felicitar y agradecer a Jorge por su blog, del cual obtuve y aprendí justamente la forma más efectiva de usar y realizar la clasificación de datos con las claras instrucciones dadas en él y permitir que otros puedan opinar en su espacio.
Muy democrático, muchas gracias.

JJOTALB 04 marzo, 2012 09:07  

Muchas gracias vitorique, ya logre que funcione el 4 criterio. mucho mejor y mas corta la formula... felicitaciones, ustedes saben mucho.

luego los seguiré molestando con otras dulas, un abrazo

JJOTALB 06 marzo, 2012 02:15  

buenas, quisiera saber si me pueden ayudar con lo siguiente.

quiero hacer una lista desplegable con 20 equipos "Equipo1, Equipo2, Equipo3, Equipo#" esta lista desplegable repetirla en la columna A 10 veces de A1:A10 y en la columna B otras 10 de B1:B10.... hasta hay creo que es fácil...
lo que no se hacer es que si yo escojo en la primera lista "A1" "el Equipo1" en las otras 19 listas desaparezca, de tal modo que al llegar a la lista "A10" y "B10" solo queden 2 equipos por seleccionar.

Espero me puedan ayudar con esto. o si alguno me puede dar el correo para tratar estos temas, lo considerare un regalo de cumpleaños jeje

Jorge L. Dunkelman 07 marzo, 2012 20:08  

Fijate en la técnica que muestro en esta nota.

Anónimo,  25 marzo, 2012 00:39  

hola jorge, muchas gracias por la explicacion. Me sirvio mucho, lo que no entendi es el tema de que cuando dos equipos me dan el mismo puntaje y me da el error ese antes mencionado. Como lo soluciono??
gracias nuevamente

Gastón Eder 06 julio, 2012 04:25  

Hola Jorge... Excelente aporte! Tengo una consulta, hice el fixture para la liga de mi región, el 1er criterio de desempate entre los equipos que tienen igualdad en puntos es el resultado del partido disputado entre ellos, el 2do criterio es la diferencia de goles entre ellos, y el 3er criterio por los goles a favor en esos encuentros, el campeonato es ida y vuelta por eso se puede definir así y si llegan a quedar empatados en todo eso se define como esta explicado acá en TU ejemplo que lo entiendo bien y me anda perfecto, pero no se como hacer esos 3 primeros criterios, espero q me puedas ayudar y que me halla podido explicar...

Desde ya Muchísimas Gracias!!

Jorge L. Dunkelman 06 julio, 2012 17:22  

Muy interesante el sistema. La solución no me parece trivial, veré de dedicarla un poco de tiempo este fin de semana.

Mauro,  15 julio, 2012 02:33  

Excelente!! Justo lo que estaba buscando, lo que no encuentro es el link para bajar el ejemplo!.
Desde ya muchas gracias.
Mauro.

Jorge L. Dunkelman 15 julio, 2012 18:52  

El motivo es que no puse ningún enlace al archivo. Pero te será suficiente con reconstruir las fórmulas tal como lo muestro en la nota.

Anónimo,  23 agosto, 2013 19:04  

Hola, lo primero felicitarte, estoy aprendiendo mucho de excel con tu blog, están siendo mis apuntes de estudio.
La conclusión que llego aquí es que sumaproducto es una función muy importante y que
JERARQUIA(J5,$J$4:$J$19)-1 es igual a SUMAPRODUCTO((J5<$J$4:$J$19)*1).
la fórmula anterior la he puesto sin columnas auxiliares como:
=JERARQUIA(J5;$J$5:$J$9)+SUMAPRODUCTO((J5=$J$5:$J$9)*(G5<$G$5:$G$9))+SUMAPRODUCTO((J5=$J$5:$J$9)*(G5=$G$5:$G$9)*(I5<$I$5:$I$9))

me quedo asombrada con SUMAPRODUCTO. ¿Opinas igual que yo?? ¿con sumaproducto puedes hacer muchas cosas?
Mª Carmen

Anónimo,  23 agosto, 2013 21:19  

Hola, lo primero felicitarte, estoy aprendiendo mucho de excel con tu blog, están siendo mis apuntes de estudio.
La conclusión que llego aquí es que sumaproducto es una función muy importante y que
JERARQUIA(J5,$J$4:$J$19)-1 es igual a SUMAPRODUCTO((J5<$J$4:$J$19)*1).
la fórmula anterior la he puesto sin columnas auxiliares asignando el nombre "puntos" al rango $J$4:$J$19
la fórmula nos queda:

=JERARQUIA(J5;puntos)+SUMAPRODUCTO((J5=puntos)*(G5<$G$5:$G$19))+SUMAPRODUCTO((J5=puntos)*(G5=$G$5:$G$19)*(I5<$I$5:$I$19))

opinas igual que yo, que sumaproducto es muy muy importante?? se pueden hacer muchas cosas, bueno tu la has hecho en este blog..
saludos y gracias por tu blog,
Mª Carmen

Jorge Dunkelman 24 agosto, 2013 11:02  

Asi es. En el blog hay varias notas sobre SUMAPRODUCTO. Puedes hacer una busqueda en el blog con SUMAPRODUCTO para verlas.

Jhonnathan Leonardo Cañas Parada 02 mayo, 2014 23:09  

Buenas tardes, en base a todo lo anterior explicado y aportado por los demás, eh logrado crear una base de datos muy completa, enlazando varias hojas y diferentes formulas, sin embargo aun no logro encontrar una formula que solucione la parte de empates, me explico, sabiendo que los partidos ganados suman 3, los empatados suman 1 y los perdidos no suman, quisiera saber que formula uso para el caso de los empates, la cual sea diferente a =SI(B2=C2;1;0) ya que esta formula asume que, el hecho que las celdas B2 y C2 esten vacias son iguales.

Agradezco una respuesta oportuna y precisa.
att, Jhonathan C.

Jorge Dunkelman 03 mayo, 2014 17:53  

Jhonatan, dos celdas vacías son iguales (ambas contienen el valor NULL). La expresión =SI(B2=C2;1;0) no aparece ne mi modelo. Sin ver el tuyo no puedo figurarme cómo la estás usando y con qué objetivo
Tendrías que dar una explicación más precisa o enviarme el archivo.

Jhonnathan Leonardo Cañas Parada 05 mayo, 2014 17:47  

Profe gracias por responder, ¿podría por favor regalarme un correo para el envió del archivo con la explicación de la formula que busco?

Jorge Dunkelman 06 mayo, 2014 07:39  

Fijate en el enlace Ayuda, en la parte superior de la plantilla.

Mome 09 mayo, 2014 19:12  

Buenísima hermano. Ya tenía algo avanzado pero me faltaba el último criterio por GF. Grandísima aportación la tuya

Pablo,  06 julio, 2014 16:00  

Hola, estoy haciendo una excel de una porra para mis amigos, para el mundial de baloncesto. Tengo un problema con la clasificación cuando hay empate, ya que el criterio es mirar los partidos directos entre ellos, y no se como hacerlo. Me puedes ayudar, muchas gracias y enhorabuena por el blog.

Jorge Dunkelman 07 julio, 2014 08:25  

Pablo, si bien se puede hacer la opción más práctica es hacerlo manualmente.

Guillermo García Gutiérrez 26 julio, 2014 08:01  

Hola, que formulas se utilizarían para desempatar en el siguiente ejemplo:
Grupo PJ PG PE PP GF GC GT Puntos
Equipo A 3 1 0 2 8 10 -2 3
Equipo B 3 3 0 0 9 3 6 9
Equipo C 3 1 0 2 8 10 -2 3
Equipo D 3 1 0 2 8 10 -2 3

SE PUEDE VER EN ESTE GRUPO DE 4 EQUIPOS QUE 3 DE ELLOS HAN EMPATADO EN PUNTOS, DIFERENCIA DE GOLES Y EN GOLES A FAVOR, HE COMPROBADO Y SI SE PUEDE DAR ESTA POSIBILIDAD, CONJUGANDO CON RESULTADOS ENTRE TODOS LOS PARTIDOS DE ESE GRUPO DE EQUIPOS, AHORA MI PREGUNTA ES ¿QUE FORMULAS NECESITARÍA PARA DESEMPATAR A A LOS EQUIPOS EN TAL EXTREMA IGUALDAD? CONSIDERANDO QUE EL REGLAMENTO INDICA QUE LLEGADO A ESE EXTREMO SE DESEMPATARÍAN CONSIDERANDO LOS RESULTADOS DE LOS PARTIDOS DE LOS EQUIPOS ENTRE SI EN CONFLICTO POR TAL IGUALDAD,

Jorge Dunkelman 26 julio, 2014 16:08  

Guillermo, no es una cuestión de fórmulas sino de datos. Tendrías que manejar los daatos de los partidoos entre los equipos. La alternativa má sencilla (y racional en mi opinión) es determinar las posiciones manualmente.

Anónimo,  22 agosto, 2014 00:50  

Hola soy nueva en excel me gustaria saber como puedo ocupar la posicion 1 del a jerarquia este es mi codigo. necesito restar el valor de la posicion 1 - el valor de la posicion 3 y si es posision 3 restar la suma de las posiciones 1 y 2. Disculpen mi ignorancia .. gracias :) =IF(RANK.EQ(D14,$D$14:$F$14,1)=1,E14-RANK.EQ(D14,$D$14:$F$14,1)=2)

Jorge Dunkelman 23 agosto, 2014 19:44  

No termino de entender el planteo. Te sugiero enviarme el archivo o un ejemplo con la descripción del problema (fijate en el enlace Ayuda, en la parte superior de la plantilla del blog).

Santiago Vaca San José 02 julio, 2015 02:15  

Estoy tratando de crear una tabla con más criterios de desempate, pero se me hace bastante difícil seguirle el rollo a todo, tal vez usted pueda ayudarme un poco con esto.
Los criterios son puntaje, diferencia de goles, goles a favor, el resultado del partido disputado entre los que empatan, y si persiste el empate, la posición en la tabla que les tocó en el sorteo.

Sé más o menos cómo organizar todo, lo que no logro hacer es sumar todos estos criterios en una única posición final para luego poder ordenarlos con JERARQUÍA.
¿Podrá ayudarme?

Jorge Dunkelman 02 julio, 2015 07:46  

Tendrías que crear una columna auxiliar donde calculas un índice para cada equipo. En este índice le das un peso específico a cada factor. Por ejemplo 1 x puntos + 0.1 x diferencia de goles + 0.01 x goles a favor.
Así un equipo con 20 puntos, diferencia de goles de 10 y 30 goles a favor tendría un índice de 21.3.
Otro equipo también con 20 puntos y diferencia de goles también 10 pero con 25 goles a favor tendría un índice de 21.25.
Al aplicar JERARQUIA a la columna de los índices, el primero equipo recibe la primera posición.

Santiago Vaca San José 13 julio, 2015 04:44  

Muchas gracias por ayudarme.
Hice eso que usted me dijo y funcionó bastante bien, pero empecé a ingresar resultados de prueba para comprobar que funcionara como quería, y ocurrió un problema:

Equipo 1 4 pts -7 DG 1 GF
Equipo 2 9 pts
Equipo 3 4 pts -5 DG 3 GF
Equipo 4 3 pts 4 DG 6 GF

Según el sistema que yo quise armar, el equipo 2 obviamente queda en primer lugar, y el equipo 3 en segundo lugar. El problema surge cuando el equipo 4 queda en tercer lugar y no es el equipo 1 el que ocupa esta posición, a pesar de que el Eq. 4 tiene 3 puntos y el Eq. 1 tiene 4.

Descubrí que el problema está en que la diferencia de goles es negativa, entonces al multiplicar los valores, el índice me da un valor menor al del equipo 4, cuando debería ser al revés según mi sistema.

¿Cómo podría hacer para solucionar este problema?

Jorge Dunkelman 13 julio, 2015 13:10  

Santiago,
me parece que tu problema está en el peso asignado a cada factor. En tu ejemplo, si usamos la expresión P+DF*0.1+GF*0.001 para calcular el índice. la ubicación de los equipos en la tabla es la correcta (el equipo 1 queda en tercer lugar con un índice de 3.931 y el equpo 4 en cuarto lugar con un índice de 3.046).

Guillermo García Gutiérrez 02 agosto, 2015 08:27  

Sr. Jorge L. Dunkelman,Que formulas emplearía si tendría por ejemplo esta tabla de posiciones:
Equipo PJ PG PE PP GF GC GT ptos
A 2 1 0 1 5 8 3 3
B 2 1 0 1 8 5 3 3
C 2 1 0 1 8 10 -2 3
D 2 1 0 1 6 5 1 3
E 2 1 0 1 5 6 -1 3
F 2 1 0 1 10 8 2 3
para posiciones finales sabiendo que 1° criterio es los puntos (ptos) en este ejemplo hay empate total de puntos, como 2° criterio es la diferencia de goles (GT) en este ejemplo hay solamente empate entre el equipo A y B, como 3° criterio los goles a favor (GF) que en este caso el equipo B tendría mayor cantidad de goles a favor, muchas gracias

Jorge Dunkelman 02 agosto, 2015 17:41  

Fijate en este post.
También podés ver el modelo que publiqué en este post.

Divar Mauricio Vargas Urbano 25 septiembre, 2015 01:52  

Buenas tardes
Tengo un documento en el que deseo organizar los puestos de estudiantes del mismo grado, pero de diferente curso.
en el listado, entre otras cosas, están el curso al cual pertenece, el periodo académico de las notas, la cantidad de áreas perdidas, el promedio.
Es posible ordenar en el mismo listado los estudiantes por periodo, por curso y por grado, de menor a mayor cantidad de áreas perdidas y por promedios de mayor a menor.
agradezco la colaboracion.

Jorge Dunkelman 25 septiembre, 2015 07:31  

Si, es posible. Te sugiero que sigamos la consulta por mail privado (fijate en el enlace Ayuda, en la parte superior del blog).

andrés pacheco 14 diciembre, 2015 22:18  

Te envié un correo electrónico con una consulta, pudiste verlo? Gracias, saludos

Jorge Dunkelman 15 diciembre, 2015 21:00  

Acabo de volver del exterior. Me va a llevar un tiempo ponerme al tanto. En cuando pueda, lo veo.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP