Busco una fórmula un tanto compleja de coincidencias

12 envíos / 0 nuevos
Último envío
eugeniocol
Imagen de eugeniocol
Offline
última acción: Hace 2 años 2 meses
Nivel 1 - 200 puntos
alta: 29/08/2017 - 17:21
Puntos: 1675
Busco una fórmula un tanto compleja de coincidencias

 

Hola Buenas.

Busco una fórmula que no doy con ella un tanto compleja de coincidencias.

Como ejemplo con filtros C:D >4 son 5 coincidencias de 3 valores y ahora el resultado manual en AB:AE y es aquí donde seria la formula con el resultado que se ve, Coincidencias 5 situadas en las posiciones 1/2/3 .

Los datos en C:Z  a buscar coincidencias(1,2,3,8 ect..) de 3 valores podrían estar en cualquiera de las mil posibles combinaciones de columnas de C:Z 

Espero explicarme bien.

Salud2….

AdjuntoTamaño
File coincidencia_resumen_foro.rar11.17 KB
Cron
Imagen de Cron
Offline
última acción: Hace 7 meses 3 semanas
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
HolaNo estoy seguro de saber

Hola

No estoy seguro de saber exactamente qué es lo que necesitas.

Si lo que quieres es contar las veces que se da la coincidencia de que los valores de las columnas 1, 2 y 3 son >4 (en las tres columnas a la vez), entonces tu fórmula será algo como esto:

=CONTAR.SI.CONJUNTO(C5:C50;">4";D5:D50;">4";E5:E50;">4")

Si las celdas azules son las columnas que quieres comparar, entonces tenemos que modificar un poco las fórmulas y hacerlas sensibles a los valores que pongas aquí, por ejemplo utilizando la función DESREF así:

=CONTAR.SI.CONJUNTO(DESREF(B5;0;AC3;45;1);">4";DESREF(B5;0;AD3;45;1);">4";DESREF(B5;0;AE3;45;1);">4")

Esto funcionará en versiones de Excel desde la 2007.

Si es otra cosa, no dudes en concretar la pregunta.

 

 

Un saludo

eugeniocol
Imagen de eugeniocol
Offline
última acción: Hace 2 años 2 meses
Nivel 1 - 200 puntos
alta: 29/08/2017 - 17:21
Puntos: 1675
mas concreto

Bueno explicarse a la primera no es siempre fácil.


El resultado que muestro como ejemplo cogido por filtros.

Quita los filtros y veras la plantilla completa, C5:Z46 (AMARILLO) son valores de enlace de otros libros que son variables nunca a menos, se rellenaran de acuerdo a los otros libros.

Con la plantilla sin filtros que es como debe ir, busco el resultado en AB(nº de coincidencias) y AC:AE( la posición de esas coincidencias) de las coincidencias por filas de 3 valores mayores de 4 de los valores en C5:Z46 .

En el ejemplo con filtros que doy pues podemos ver AB=5 coincidencias correspondientes a la PSC 1/2/3
Hay muchas más coincidencias en todo el rangoC5:Z46, como en PSC 2/3/6  coincidencia AB=5 este resultado iría debajo del anterior en la fila siguiente y así hasta completar todas las coincidencias de mayor de 4 de 3 valores por filas.

Quizás lo que pretendo con fórmulas es muy complicado porque puede haber un montón de combinaciones y seria una macro ¿?? Que de esto ni idea.



Salud2…

Cron
Imagen de Cron
Offline
última acción: Hace 7 meses 3 semanas
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
¿Has probado las fórmulas que

¿Has probado las fórmulas que te he puesto? Es que creo que resuelven lo que pides.

eugeniocol
Imagen de eugeniocol
Offline
última acción: Hace 2 años 2 meses
Nivel 1 - 200 puntos
alta: 29/08/2017 - 17:21
Puntos: 1675
esto no es..

 

Si coloco esta fórmula en AB 3

=CONTAR.SI.CONJUNTO(C5:C50;">4";D5:D50;">4";E5:E50;">4")

Lo único que me da es que en ese rango hay 5 coincidencias

Y esta fórmula:

=CONTAR.SI.CONJUNTO(DESREF(B5;0;AC3;45;1);">4";DESREF(B5;0;AD3;45;1);">4";DESREF(B5;0;AE3;45;1);">4")

Lo situó en AC3 AD3 AD3 y 0

No veo por ningún sitio el resultado del ejemplo:

Coincidencias

PSC

PSC

PSC

5

1

2

3

5 coincidencias de 3 valores en posiciones según cabecera 1/2/3

Y como digo hay más

Coincidencias

PSC

PSC

PSC

5

2

3

6

Que iría debajo de la anterior  y así sucesivamente.

5 coincidencias de 3 valores en posiciones según cabecera 2/3/6

Además las formulas solo hacen referencia a un rango concreto del ejemplo y el rango a buscar las coincidencias de >4 de 3 valores  es de toda la plantilla C5:Z50 y pueden ser en su momento y no ahora de muchas.

Ten en cuenta que lo que se adapte lo acoplare ejemplo desde >1 a >100

Esto puede dar infinidad de combinaciones en todo el rango C5:Z50

Creo que una formula no vale solo macro.

Salud2…...

 

 

 

Cron
Imagen de Cron
Offline
última acción: Hace 7 meses 3 semanas
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
No sé, yo creo que la fórmula

No sé, yo creo que la fórmula que he puesto sigue siendo lo que pides.

Si cambias los valores bajo PSC te calcula la columna correspondiente. Si quieres copiar la fórmula, habrá que fijar las referencias convenientemente. Si eliminas la fila de los filtros, también habrá que cambiar las referencias correspondientes. Pero tal cual está, funciona.

Te adjunto tu hoja con la fórmula puesta en las celdas verdes de la columna AB. He modificado la fórmula ligeramente para que fije correctamente las celdas, filas y columnas.

Además, he añadido en la celda AC1 el valor que quieres que sea el umbral. En mi ejemplo lo he puesto fijo para todas las filas, pero luego puedes hacer lo que quieras con él modificando convenientemente la fórmula.

Tal cual te lo he dejado, cuenta dos ejemplos. Uno es el número de filas en las que se dan valores mayores que 3 en las posiciones 2, 3 y 6. El otro es el número de filas en las que se dan valores mayores que 3 en las posiciones 1, 2 y 23.

La otra modificación es estética: cuando no están las tres posiciones rellenas en las celdas azules (o blancas) de las columnas AC, AD y AE, la fórmula deja la celda en blanco.

Si quieres una macro, también se puede hacer, pero en este caso no merece la pena.

 

Un saludo

AdjuntoTamaño
File coincidencia_resumen_foro.xlsm18.78 KB
eugeniocol
Imagen de eugeniocol
Offline
última acción: Hace 2 años 2 meses
Nivel 1 - 200 puntos
alta: 29/08/2017 - 17:21
Puntos: 1675
no me entiendes....

bueno ya veo que no me explico demasiado bien y ya no se como hacerlo con libro y ejmplos.

 

A ver voy a preguntarte:

La fórmula que me das coge el rango AC:AE ¿??

En fila 4 de donde sale el resultado en AC4:AE4 de 1/2/23 ¿??

Esto es un resultado manual y esto debe ser por formula o macro.

Si tiras de filtros para comprobar en esas columnas 1/2/23  no hay 6 coincidencias de 3 valores>4

Hay 8 coincidencias >4 de 2 valores por fila, haz la prueba, en la columna 23 solo hay 0.

El resultado del libro ejemplo en AC3:AE3 de  1/2/3 es manual y estos datos manual  los tiene que dar o formula o macro, porque puede llegar a existir coincidencias >4 en PSC en columnas 2/8/15 y este 2/8/15 se tiene que reflejar mediante formula o macro en la fila correspondiente de AC:AE no colocar el resultado manual. Si no  tendría ningún sentido nada de esto.  

El resultado de formula o macro va en AC:AE (nada manual) de las coincidencias buscadas >4 de 3 valores de C:Z la formula o macro tiene que trabajar sobre el rango C4:Z 49 y en AC:AE el resultado obtenido.

Puf…… si de esta no me entiendes pues ya no sé qué más decir.

Salud2....

Cron
Imagen de Cron
Offline
última acción: Hace 7 meses 3 semanas
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
Hola Voy a responder a lo que

Hola

 

Voy a responder a lo que me preguntas concretamente, a ver si entre los dos somos capaces de entendernos.

La fórmula que me das coge el rango AC:AE ¿??

Sí. La fórmula que te doy coge la columna que le indiques manualmente en las celdas de las columnas AC, AD y AE. Si en estas celdas pones 1, 2 y 3, entonces hace referencia a las columnas C, D y E. Si pones 23, contará la columna Y.

Esto es un resultado manual y esto debe ser por formula o macro.

Lo único manual es poner los datos en las celdas de AC, AD y AE.

En fila 4 de donde sale el resultado en AC4:AE4 de 1/2/23 ¿??

Cuenta los valores mayores que el número que hemos puesto en la celda AC1 en las columnas de posición 1, 2 y 23, es decir, entre las columnas C, D e Y. Es decir, como lo he puesto el resultado no está en AC4:AE4, sino que ese es el valor de entrada y el resultado está en la columna AB.

Si tiras de filtros para comprobar en esas columnas 1/2/23  no hay 6 coincidencias de 3 valores>4

No. Y eso es lo que sale. El cálculo está en la celda AB4, y sale 0. De todas maneras, lo que está contando son valores > 3, porque se lo hemos puesto así en la celda AC1. Esto lo he hecho así porque me dijiste que querías poder modificar el criterio de comparación.

Hay 8 coincidencias >4 de 2 valores por fila, haz la prueba, en la columna 23 solo hay 0.

La fórmula está puesta de manera que tú le dices en qué columnas quiere que cuente, y te responde cuántas coincidencias hay. Es decir, introduces manualmente las columnas 1, 2 y 23 y te responde en la columna AB que hay 0 coincidencias. en la fila anterior le hemos puesto que busque en las filas 2, 3 y 6 y dice que hay 6 coincidencias de valores >3. Si quieres comprobar los valores >4, cambia el valor de la celda AC3.

El resultado del libro ejemplo en AC3:AE3 de  1/2/3 es manual y estos datos manual  los tiene que dar o formula o macro, porque puede llegar a existir coincidencias >4 en PSC en columnas 2/8/15 y este 2/8/15 se tiene que reflejar mediante formula o macro en la fila correspondiente de AC:AE no colocar el resultado manual. Si no  tendría ningún sentido nada de esto.

Entiendo que lo que quieres no es lo que he puesto, sino al revés. Es decir, comprobar las combinaciones de columnas de 3 en 3, y poner en las celdas AC3, AD3 y AE3 en qué columnas se dan los mayores resultados???

Si esto es así, en la fila 4, que es la que está marcada con el número 1 en la columna de color rosa, si queremos ver las combinaciones con valores >4, tendríamos las posiciones 1, 2, 8 y 10. Si no tenemos en cuenta el orden, tendremos 4 formas de combinarlo. Si el orden es importante, tendremos 24 combinaciones. Como tú solo quieres una, entonces necesitamos establecer el criterio para seleccionar ese resultado entre los 24 o entre los 4.

 

Si esta última interpretación es correcta, por favor, dime cuál es el criterio concreto. Si puede ser, en notación matemática o pseudomatemática, lógica o pseudocódigo.

 

Un saludo

eugeniocol
Imagen de eugeniocol
Offline
última acción: Hace 2 años 2 meses
Nivel 1 - 200 puntos
alta: 29/08/2017 - 17:21
Puntos: 1675
a ver ahora...

 

Pufff a ver si de esta me entiendes, me dices:

Entiendo que lo que quieres no es lo que he puesto, sino al revés. Es decir, comprobar las combinaciones de columnas de 3 en 3, y poner en las celdas AC3, AD3 y AE3 en qué columnas se dan los mayores resultados???

Más menos SI. Voy de Nuevo, Céntrate en el texto siguiente:

Combinaciones de columnas de 3 en 3

De 3 en 3 en rango C:Z mayor de 4 por filas combinaciones por filas no por columnas (por lo menos yo lo veo así), los filtros están colocados por columnas (no se hacerlo de otra forma) solo para explicarme y ver las coincidencias de ejemplo.

Y poner en las celdas AC3, AD3 y AE3 en qué columnas se dan los mayores resultados???

En las celdas AC3, AD3 y AE3 va el resultado esperado nunca manual, en el ejemplo lo se PSC 1/2/3 o el que fuese del encabezado de C:Z.

 

Pdta.:

Insisto  resultado en AC3, AD3 y AE3 de formula o macro nunca resultado manual.

Salud2….

 

Cron
Imagen de Cron
Offline
última acción: Hace 7 meses 3 semanas
Nivel 1 - 200 puntosNivel 2 - 500 puntosNivel 3 - 1000 puntosNivel 4 - 2000 puntosNivel 5 - 4000 puntos
alta: 23/06/2010 - 12:30
Puntos: 12735
Vale, entiendo que quieres

Vale, entiendo que quieres que se te calculen tres resultados que se deben colocar en las celdas AC3, AD3 y AE3. Lo que no dices es cómo se llega a ese resultado.

Dices "de 3 en 3 en rango C:Z mayor de 4 por filas"

Yo te he contestado a eso que se puede dar el caso de que haya más de 3 columnas en las que, en la misma fila, el valor sea mayor que 4. ¿Cómo seleccionas los tres valores a presentar en las celdas AC3, AD3 y AE3?

Cuando empezaste, el ejemplo que pusiste era con filtros. Tú filtrabas manualmente (es decir, ya seleccionabas a mano con qué columnas trabajar). Pero lo que entiendo que quieres es que las columnas a trabajar se seleccionen automáticamente. Pues di cuál es el criterio para seleccionar esas columnas, pues aún no lo has mencionado.

Solo dices que el valor sea >4, pero ese no es el criterio para seleccionar la columna.

Lo siento, si no eres más claro, no podré ayudarte.

Por favor, dime cómo lo harías a mano, pero paso a paso. Necesito cada paso y por qué en cada uno.

Un saludo

eugeniocol
Imagen de eugeniocol
Offline
última acción: Hace 2 años 2 meses
Nivel 1 - 200 puntos
alta: 29/08/2017 - 17:21
Puntos: 1675
paso a paso

 

Bueno si de esta no nos aclaramos tiro la tolla jiji….

Des pues de un buen rato de buscar algunas coincidencias y resultados manualmente:

Hoja PLANTILLA RESUMEN

Resultados esperados desde AB : AE  ( ESTAN A MANO) aquí es donde los reflejara la macro o formula, esto es una muestra podrían ser a base de todas las coincidencias desde 1 a 10000 pues hasta la AB AE 10000……

 

De donde salen los resultados ejemplos pero reales puestos copia pego de :

5

1

2

3

5

2

3

6

4

2

3

5

3

1

2

6

 

Pues se ven en las hojas 1,2,3 y 4 claro buscadas a mano con filtros, que es lo que quiero evitar y sustituir por formula o macro, pero dentro de mi poco saber creo que es una macro, porque coincidencias desde 1 a x pues……… (veo que esta parte ultima no está nada bien explicada) eso si lo de >4 que aprovecho para lo que se puede hacer que sea adaptable a > desde 1 a 5,6,7,8 ect..

 

Salud2….

 

AdjuntoTamaño
File coincidencia_resumen_foro.rar21.12 KB
eugeniocol
Imagen de eugeniocol
Offline
última acción: Hace 2 años 2 meses
Nivel 1 - 200 puntos
alta: 29/08/2017 - 17:21
Puntos: 1675
otra idea

 

Veo que esto se complica de momento, tengo otra cosa entre manos creo quizás más sencilla es esta:


Que formula o macro podía acoplar en F:I ??

Ejemplos de resultados a mano:

F3:I3 en F3 filas el valor de A (orden)4 G3:I3 6/2/6
F4:I4 en F3 filas el valor de A (orden)14 G4:I4 6/2/6

ect.......

Todo marcado con colores diferentes.

Otra solución más resumida seria también:

K:N este es el resumen de F:I

Una de las dos me vale, mejor la 2º. opción .

Salud2....

AdjuntoTamaño
File coincidencia_3_valores.rar7.07 KB