** NORMAS DEL FORO **
Inicio del foro Inicio del foro > Otros de Microsoft: Windows y Office > Excel
  Mensajes nuevos Mensajes nuevos RSS - Cuadro de Puntaje
  Preguntas frecuentes Preguntas frecuentes  Buscar en el foro   Eventos   Registro Registro  Iniciar sesion Iniciar sesion

Cuadro de Puntaje
 Responder Responder
Autor
Mensaje
  Tema Buscar Tema Buscar  Opciones del Tema Opciones del Tema
edisito Ver desplegable
Asiduo
Asiduo
Avatar

Unido: 23/Octubre/2005
Localización: Ecuador
Estado: Sin conexión
Puntos: 406 		Opciones de entrada Opciones de entrada   Gracias (0) Gracias(0)   Cita edisito Cita  ResponderRespuesta Enlace directo a este mensaje Tema: Cuadro de Puntaje
    Enviado: 29/Diciembre/2020 a las 18:42
Hola amigos, tengo un problema con buscar datos en una matriz. El asunto es el siguiente, tengo un grupo de personas a las cuales se les toma una evaluacion fisica, para ello se usa el Test de Cooper, el cual tiene la siguiente tabla:

CATEGORIA  EDAD30 EDAD39 PUNTOS
MUY MALA 16:30 17:30 5
MALA 14:31 15:31 10
REGULAR 12:21 13:01 15
BUENA 10:16 11:01 18
EXCELENTE 10:15 11:00 20

como se puede ver, segun la edad y el tiempo que haga la persona se le asigna un puntaje, para ello yo uso INDICE y COINCIDIR.

El inconveniente es que los puntajes no son los correctos, por ejemplo, si una persona de 30 años de edad hace 11:30 minutos, su puntaje deberia ser de 18, pero le toma como 15.

Si alguien me da una mano para la busqueda de valores del tipo hora me ayuda un monton.

Saludos.


Editado por edisito - 29/Diciembre/2020 a las 18:49
Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5976 		Opciones de entrada Opciones de entrada   Gracias (0) Gracias(0)   Cita AnSanVal Cita  ResponderRespuesta Enlace directo a este mensaje Enviado: 29/Diciembre/2020 a las 23:55

Suponiendo que  18 puntos corresponden a:  >=10:16 y  <12:21

1.- Invierte los valores de la tabla, de modo que las horas estén ordenadas de menor a mayor.

2.- Fórmula:  =INDICE($D$2:$D$6;COINCIDIR(B10;$B$2:$B$6;1))   (posiblemente en Ecuador en vez de «;» sea  «,»)


CATEGORIA       EDAD30      EDAD39      PUNTOS
EXCELENTE 10:15,0 11:00 20
BUENA 10:16,0 11:01 18
REGULAR 12:21,0 13:01 15
MALA 14:31,0 15:31 10
MUY MALA 16:30,0 17:30 5
  NOMBRE      EDAD30      PUNTOS
Paco 11:30,0 18
Andrea 12:27,0 15
José 15:10,0 10
Martín 10:15,5 20
Isabel 16:34,0 5


También te vale:  =ELEGIR(COINCIDIR(B10;$B$2:$B$6);20;18;15;10;5)   ( B10 contiene 11:30 )
Saludos desde Tenerife.
Arriba
edisito Ver desplegable
Asiduo
Asiduo
Avatar

Unido: 23/Octubre/2005
Localización: Ecuador
Estado: Sin conexión
Puntos: 406 		Opciones de entrada Opciones de entrada   Gracias (0) Gracias(0)   Cita edisito Cita  ResponderRespuesta Enlace directo a este mensaje Enviado: 30/Diciembre/2020 a las 01:41
GRAACIAS por la ayuda, les voy a poner las soluciones, si alguien puede simplificar mas la formula le pone en este hilo.

Solucion de AnSanVal (Con los datos ordenados de Mayor a Menor Puntaje)

=INDICE($D$2:$D$6;COINCIDIR(B10;$B$2:$B$6;1))
=ELEGIR(COINCIDIR(B10;$B$2:$B$6);20;18;15;10;5) 

Tiene un error con tiempos menores de 10:15, pero se puede controlar con un SI... (Gracias AnSanVal)

Soluciones mias sin ordenar datos:

=INDICE(G4:K8;COINCIDIR(M3;G4:G8;-1);5): Esta es la formular que utilice al inicio y tenia el problema descrito. G4:K8 son los datos de la tabla, M3 el valor buscado (Ej. 11:00), G4:G8 la columna donde busco, -1 valor mayor y 5 para la columna de puntaje.

=BUSCARV(M18;G18:K22;5): Con los datos ordenados de mayor a menor puntaje M18 valor buscado; G18:K22 matriz de datos; 5 columna de puntaje, tiene el mismo error de los tiempos menores a 10:15

=INDIRECTO("K"&BUSCARV(SUMAPRODUCTO(--($G$4:$G$8<=M3);FILA($G$4:$G$8));$D$4:$E$9;2;FALSO): Formula final con los datos sin ordenar, aqui utilice dos columnas de ayuda quedando la tabla de la siguiente manera:

DEFGHIJK
MatrizFilaCategoría<3030-3940-49>50Puntos
4304Muy Mala16:3017:3018:3019:005
5265Mala14:3115:3116:3117:0110
6216Regular12:2113:0114:0114:3115
7157Buena10:1611:0111:3112:0118
888Excelente10:1511:0011:3012:0020
908

Un poco enredada: SUMAPRODUCTO me da un resultado de la multiplicacion de dos matrices "--(BD!$G$4:$G$8<=M3);FILA(BD!$G$4:$G$8)", estos valores son los que estan el la columna D4, luego uso BUSCARV para el valor de la Fila con un valor exacto, y luego con indirecto obtengo la posicion de la celda y por ende el valor del puntaje (Ej. K7). En la celda M3 estaria el valor a comparar/buscar

Como pueden ver la formula es un poco larga, y se hace mucho mas cuando le combino con la edad, ya que se tiene que evaluar a personas <30 años hasta personas >50 años.

Saludos desde Ecuador.
Arriba
AnSanVal Ver desplegable
Administrador
Administrador
Avatar

Unido: 16/Marzo/2005
Localización: España
Estado: Sin conexión
Puntos: 5976 		Opciones de entrada Opciones de entrada   Gracias (0) Gracias(0)   Cita AnSanVal Cita  ResponderRespuesta Enlace directo a este mensaje Enviado: 30/Diciembre/2020 a las 10:56
«... Tiene un error con tiempos menores de 10:15...»

Eso suele ocurrir cuando la exposición de la consulta no se parece a la realidad, seguramente mas adelante habrá problema con el puntaje de un sujeto de 28 años (< 30) y un usuario de 18 (<30), etc. aunque se resuelve fácil con ELEGIR/COINCIDIR sin alargar la fórmula. Otra cosa es incluir la edad, eso si alarga (o cambiar de método a por ejemplo una UDF).

Como esta parece una de esas consultas que amenazan eternizarse, yo (de momento) paso a «Modo lectura».
Saludos desde Tenerife.
Arriba
 Responder Responder
  Compartir tema   

Ir al foro Permisos de foro Ver desplegable