Hola a Tod@s.

Llevo dos días intentando buscar la solución a este Tema y no lo consigo, la idea es contar, la cantidad de valores que puedan existir Vecinos, Cercanos o Colindantes, en cada uno de los Números de cada Sorteo, según un Cuadro o una Tabla.

En el ejemplo que subo explico algo más.

Desde ya Agradecido por su colaboración.

Un Saludo.

Lázaro

Contar Vecinos.rar

Editado el el 8 de mayo5 años por Lázaro

Lo puedes revisar?

Si cumple,  clic en el corazoncito inferior derecho

Saludos.

VIC-SORT.xlsm

hola Lázaro (y Janlui ?)

Lázaro,  aqui con mis megaformulas...

?

y a ver si funciona....

 

 

Contar Vecinos 1.xlsb

Saludos @Janlui y @Silvia

Ante todo Muchísimas Gracias  por responder a mi Tema, la verdad que muy contento, por tener dos Opciones diferentes y en ambos casos me dan los Resultados Esperados.

Solo necesito que No se cierre el Tema un tiempo, para intentar adaptar a mis Archivos Originales los dos Aportes, teniendo en cuenta por ejemplo, que hay un Archivo donde el  Sorteo  son 20 Números y el Cuadro de 80 Valores.

Entre hoy y mañana le dedicare todas las horas posible y ya os comento.

Una vez más Agradecido por su Colaboración.

Un Abrazo para ambos @Janlui y @Silvia

Lázaro.

Saludos @Janlui

Perfecto!!! tu Aporte, que bien has estructurado todas esas Instrucciones compadre (aunque no las entiendo todas), he realizado cambios de la configuración del Cuadro e incluso de los Sorteos y todo muy bien, en las Hojas 3 y 4 están los ejemplos, solo me quedan algunas dudas y quisiera de ser posible alguna explicación, en la Hoja4 he escrito lo que significan (Creo yo) algunas instrucciones y otras que te he señalado de color rojo escribiendo <<< Aquí, para que si puedes me digas que significan.

Una de las Dudas más grande es, como sabe la macros HASTA que Columna tiene que calcular la Macros, en la parte de “Sorteos”,  en la parte del “Cuadro”, así como en los Resultados Esperados. (aunque igual es por celdas ocupadas, pero no sé donde están esa instrucciones en la macros)

Muchas Gracias mi hermano.

Un Abrazo.

Lázaro.

CONTAR-VECINOS JANLUI.xlsm

Saludos @Silvia

Bueno tu Aporte, como bien tú dices, es una Megaformula, pero que Curro has metido!!!!, (hablándote en buen cubano), desde que me surgió esta idea, pensaba resolverlo con formulas más o menos complicadas, pero al cabo de un día entero me percaté que era casi imprescindible Nombrar Rangos y ya tu solución me lo demuestra, para ser sincero, no me he atrevido a llevarlo a uno de mis Archivos Originales, porque casi seguro me perderé en algún momento, así que como ya sabes cuál es la idea central de lo que necesito, te voy a explicar la idea que intente desarrollar, pero que no logre y disculpa por abusar de tu tiempo.

A groso modo es, crear Rangos con cada Valor y de esa forma no importa la cantidad de  Números en los Sorteos ni la configuración del Cuadro.

Mira por favor el archivo que subo en la Hoja1, ahí te explico con más detalle la idea.

Muchas Gracias.

Un Abrazo.

Lázaro.

Contar Vecinos-SILVIA.xlsb

Hola @Lázaro, @Janlui y @Silvia

@Silvia Yo también lo iba a hacer con rangos con nombre y me ganaste!!! Muy buena solución. Para no quedarme atrás... lo hice de dos formas diferentes.

1. Con megafórmula, sin nombres de rango, tomando como idea fundamental el uso de desref
2. Con una UDF (User Defined Function) que, mediante macros, se crea una función de Excel para ser usada. Desde el punto de vista del usuario es la forma más "limpia".

¿Servira? Dado que Lázaro se autodefine como macrofílico, supongo que será de interés.Contar Vecinos 2.xlsb

Editado el el 9 de mayo5 años por DiegoPC

¡Hola, a todos!

¡Me encantó la forma como @DiegoPC resolvió con macros! Dejo una opción con formulación.  ¡Bendiciones para todos!

Contar Vecinos.xlsb

¡Hola, de nuevo!

Inclusive... dejo otra un poco más corta, con CSE (la anterior propuesta era no CSE).  ¡Bendiciones!

Contar Vecinos2.xlsb

hola a todooooos..

John

que cosa es estooooooooo MEU DEUS DO CEU??

"F0C0000"

"F4C0066" = Ese resultado se convierte en por ejm.  35 ¿???????????

??

FILA 4 COLUMNA .................???

 

saludos

Silvia

 

Hace 8 horas, Lázaro dijo:

Mira por favor el archivo que subo en la Hoja1, ahí te explico con más detalle la idea.

Lázaro... ,  aquí va algo según lo que entendí.?

Saludos a toda la distinguida concurrencia.

Silvia

Contar Vecinos-SILVIA2.xlsb

Saludos @DiegoPC

Ante todo Muchas Gracias por responder a mi Tema.

Pues Sí, es la pura realidad, las Macros (las cuales conocí de su Existencia en este Foro) me deparan mucho respeto, sobre todo por el idioma, nunca estudie el Ingles y para no hacerte “la Historia del Tabaco” (refrán cubano) después del bachiller, estudie 4 años en Moscú y otra cuestión son mis archivos, suelen ser extremadamente cargados de formulas unas dependiente de otras, pero bueno, una vez más voy intentar con ese Curso de Macros de Sergio, aunque con los 60 al doblar de la esquina, igual no aprendo mucho.

Sobre tus dos Aportes, en el de formulas muy interesante, de hecho la Función  SIGNO es primera vez que la veo y por supuesto tu solución muchísimo mejor que mi idea.

Con relación a la UDF pues otro tema más para estudiar, no entiendo que si no haces alusión a ningún Rango en la macros, pues después pueda poner los Rango en la formula y de la solución, sin dudas de esta forma puedo cambiar lo que sea de los parámetros y me dirá el resultado correcto, pero ya eso lo voy a ejercitar después.

Muchísimas Gracias mi hermano por tus dos Aportes.

Un Abrazo.

Lázaro.

Saludos @Silvia

Ante todo debo disculparme contigo, la verdad que si llego a saber, que obtendría mas respuestas de otros compañeros con formulas, pues no te hubiese preguntado, con mi idea hay que hacer muchas cosas para llegar al resultado y las otras propuestas son muchísima más sencillas.

No obstante me guardo tu formula, más que nada, porque no tiene nada que ver con lo que tenía pensado e intentaré aprender un poco de cómo poder mezclar esas funciones para obtener ese "cinco", "siete",etc.

Muchísimas Gracias una vez mas y mis disculpas nuevamente.

Un Abrazo mi amiga

Lázaro.

Saludos @John Jairo V

Muchas Gracias por responder también a mi Tema.

Tus dos Aportes no me abren a la primera, Windows lo rechaza y ya después reparados es que puedo verlos.

En el primero donde está la  función AGGREGATE, pues supongo que sea para un Excel superior al 2007 (ya tengo en mis planes cambiar este 2007, pero con la Pandemia del Coronavirus hay que esperar), no obstante mi hijo  tiene el 365 e intentare probarlo en su ordenador.

En el segundo ya después de reparado si puedo verlo, sin dudas es otra Muy Buena Solución, lo que parecido a @Silvia  no entiendo para nada un segmento,

(FILA($BN$2:$BR$11)-1)/1%%+COLUMNA($BN$2:$BR$11)-1));"F0C0000"););;;3;3)

 

Principalmente lo que está en rojo (1%% "F0C0000"););;;3;3)  si fueses tan amable de explicármelo, ya que supongo de cambiar la estructura del Cuadro, igual debe variar la formula en esta parte.

Una vez más Muchísimas Gracias mi hermano, por responder a mi Tema.

Un Abrazo.

Lázaro.

Editado el el 10 de mayo5 años por Lázaro

Hola a todos!

No me quedare fuera de la foto

Adjunto 2 alternativas

 

Saludos!

Contar Vecinos_GP.zip

Qué bonita vecindad... es la vecindad de Gerson!!

Este tema ha unido a la vecindad con tanto aporte ? y parece que nadie se quiere quedar fuera. Pero lamento comunicarles que ya está comprobado que están loquitos!! y que algunos aportes requieren entrar en trance para entenderlos y me hacen sentir burrito.

En 10/5/2020 at 21:11 , DiegoPC dijo:

Qué bonita vecindad... es la vecindad de Gerson!!

Este tema ha unido a la vecindad con tanto aporte ? y parece que nadie se quiere quedar fuera. Pero lamento comunicarles que ya está comprobado que están loquitos!! y que algunos aportes requieren entrar en trance para entenderlos y me hacen sentir burrito.

Realmente estos temas se extrañan y hacen falta!, todos aprendemos de todos

 

Saludos estimado @DiegoPC

Saludos @Gerson Pineda

Pues muchas Gracias a ti también mi hermano.

Solo tengo una duda en tus dos Aportes y es con relación a esta parte de la formula:

COLUMNA($BN$2:$BR$11)))-2;3;3)

Ese -2 y después los ;3;3.

Como podéis imaginar mi Alegría es Total, ya no solo por tener tantas Opciones de Solución, sino que todo parece indicar que hemos aprendido varios con este Tema (aunque a mí me queda mucho por aprender del mismo) , incluso tenía un Tema sin terminar en unas de mis Carpetas y con la segunda solución de @Silvia ya lo he resuelto.

Solo os pido de ser posible que me aclaréis esas dudas que tengo, si no es mucha molestia.

Mil Gracias a Tod@s  @Janlui;  @Silvia;   @DiegoPC;  @John Jairo V y  @Gerson Pineda

Y bueno para complacer a @Gerson Pineda  jajaj, aquí os dejo la Foto de este Tema.

 

Tema Súper Solucionado.

 

Un Abrazo para Tod@s.

Lázaro.

Editado el el 11 de mayo5 años por Lázaro

En 11/5/2020 at 0:31 , Lázaro dijo:

Saludos @Gerson Pineda

Pues muchas Gracias a ti también mi hermano.

Solo tengo una duda en tus dos Aportes y es con relación a esta parte de la formula:

COLUMNA($BN$2:$BR$11)))-2;3;3)

Ese -2 y después los ;3;3.

Como podéis imaginar mi Alegría es Total, ya no solo por tener tantas Opciones de Solución, sino que todo parece indicar que hemos aprendido varios con este Tema (aunque a mí me queda mucho por aprender del mismo) , incluso tenía un Tema sin terminar en unas de mis Carpetas y con la segunda solución de @Silvia ya lo he resuelto.

¿Qué tal Lázaro, como va todo por allá, con la cuarentena?

Veo que estas un poco confundido y seguramente es por la función Desref, repasa la sintaxis y sus argumentos

Tomando como ejemplo el primer Sorteo, el numero 5

=SUMA(CONTAR.SI(DESREF($A$1,MIN(SI($BN$2:$BR$11=H13,FILA($BN$2:$BR$11)))-2,MIN(SI($BN$2:$BR$11=H13,COLUMNA($BN$2:$BR$11)))-2,3,3),$H13:$L13))-1

Descomponiendo la formula quedaria asi:

Inicio de la Referencia
$A$1

Para el argunento FILA, obtenemos la fila y restamos 2, porque con desref el desplazamiento puede ser cero, que significa que la fila no se mueve, si el numero es positivo se desplaza hacia abajo y si es negarivo, su desplazamiento es hacia arriba
MIN(SI($BN$2:$BR$11=H13,FILA($BN$2:$BR$11)))-2

Para el argumento COLUMNA, obtenemos la columna, es lo que mismo que con fila, podemos desplazarla hacia la derecha o izquierda, sea positivo o negativo su numero
MIN(SI($BN$2:$BR$11=H13,COLUMNA($BN$2:$BR$11)))-2

El primer 3, es para el argumento ALTO y es opcional, este valor lo que hace es redimensionar las filas, por ejemplo si argumento FILA es A1 y el valor para ALTO es 3, entonces pasa de A1 a A1:A3

El segundo 3, es ANCHO y es opcional, este valor lo que hace es redimensionar las columnas, por ejemplo si argumento columna es A y el valor para ANCHO es 3, entonces pasa de A a A:C

Entonces para obtener ese rango que rodea el sorteo 5 que esta en BR6, le restas 1 fila y le restas una columna, obtenienes BQ5 y a esta referencia, le indicas 3 filas como alto y 3 columnas como ancho, obtienes el rango BQ5:BS7, donde se encuentran el 5 y 37, de los 5 numeros {5,8,21,37,46} del primer sorteo

Seguido a CONTARSI le pasa ese rango obtenido con Desref, luego le indicas que cuente el sorteo de los 5 numeros en su argumento criterio y finalmente con SUMA los agregas y le restas 1, para que no se tome en cuenta el numero buscado

Espero que con esta explicación, se despejen un poco mas tus dudas

Por cierto me encanta como @John Jairo V lo resuelve haciendo uso de INDIRECTO y TEXTO,  aunque @Silvia se sienta perdida 

Eso si @Lázaro toma en cuenta que varias de estas funciones son volátiles, per si tienes una PC con suficientes recursos, no será tan notorio

 

Saludos a todos!

Saludos @Gerson Pineda

Bueno sobre la Cuarenta y este dichoso Coronavirus, pues la verdad que al principio hubo mucha Disciplina Social aquí en España, principalmente cuando empezaron aumentar en decenas de miles  los fallecidos cada día, pero ahora la gente se está relajando, muchos no lleva la Mascarilla, no guardan la distancia mínima de seguridad, si tienen síntomas No se Auto-aíslan y principalmente con todas esas personas que tienen el Virus y están Asintomáticos,(para mí los más peligrosos de todos), esto conducirá a un Rebrote Adelantado (ojalá que me equivoque).

Fíjate Gerson que ahora en Corea del Sur hay un Rebrote, el país que a mi entender ha hecho mejor las cosas desde el principio, ellos lograron parar los contagios en un cortísimo plazo de tiempo, sin embargo ahora al parecer, por una sola persona que estaba contagiada y se fue de fiesta y pachanga por bares y clubes, pues hay más de  85 Contagiados y miles que no se han encontrado y que puede ser que muchos estén Contagiados, todo esto por Una Sola Persona, es más que elocuente de NO seguir protegiéndonos pues las consecuencias serán Catastróficas, al menos hasta que no haya una Vacuna.

Sobre tu explicación mi hermano, pues una vez más de seguro entenderé muchísimo mejor las Formulas, solo lo he leído una vez, pero después con más tiempo ya me lo estudiare con mucha calma, está muy bien detallada, ya sabes de mis limitaciones con este Alucinante Excel.

Sobre mi PC, yo lo considero un Tanque o Caballo de Guerra, por todo lo que le hago trabajar cada día.

Gracias compadre por tu Paciencia y Generosidad.

Un Fortísimo Abrazo

Lázaro.

¡Hola a todos!

He estado un poco ocupado estos días.  Les comento sobre la fórmula que posteé, la cual tiene que ver con la función INDIRECTO.

La fórmula completa es ésta:

=SUMA(CONTAR.SI(DESREF(INDIRECTO(TEXTO(MIN(SI($BN$2:$BR$11=H13;(FILA($BN$2:$BR$11)-1)/1%%+COLUMNA($BN$2:$BR$11)-1));"F0C0000") ; );;;3;3);$H13:$L13))-1

Como es costumbre, arranco de adentro hacia afuera:

1. (FILA($BN$2:$BR$11)-1)/1%%+COLUMNA($BN$2:$BR$11)-1

Esta parte, a pesar de ser la primera, es la central de la solución planteada.  En primera instancia, la idea es entender que el símbolo %, más que un "formato porcentaje" es un operador en Excel.  El colocar el símbolo % es igual que dividir una cantidad por 100.  Entonces:

1%% = 1/100/100 = 1/10000 = 0,0001

Al dividir por esta cantidad, es igual que multiplicar por 10000, o 10^4.

El numerador de la división es el siguiente: FILA($BN$2:$BR$11)-1; el cual le restamos 1 para obtener una fila menos y ubicarnos en el número con fila anterior al buscado (bien lo pudimos hacer con la función DESREF, al igual que el 1 que se le resta a la función COLUMNA).  Esta parte (sin la división) genera lo siguiente:

{1;2;3;4;5;6;7;8;9;10} (cantidad de elementos : 10 - filas -) (mi separador de constante matricial vertical es el ";" (en Excel 2007 el separador, normalmente, es el "\")

Al dividirlo por 1%% (o lo que es lo mismo, multiplicar por 10^4 o 10000), nos daría:

{10000;20000;30000;40000;50000;60000;70000;80000;90000;100000}

La parte de COLUMNA($BN$2:$BR$11)-1 da como resultado:

{65\66\67\68\69} (Cantidad de elementos : 5 - Columnas -) (mi separador de constante matricial horizontal es el "\" (en Excel 2007 el separador, normalmente es el ";")

Entonces, la suma (FILA($BN$2:$BR$11)-1)/1%%+COLUMNA($BN$2:$BR$11)-1 genera la siguiente constante matricial mixta:

{10065\10066\10067\10068\10069;20065\20066\20067\20068\20069;30065\30066\30067\30068\30069;40065\40066\40067\40068\40069;50065\50066\50067\50068\50069;60065\60066\60067\60068\60069;70065\70066\70067\70068\70069;80065\80066\80067\80068\80069;90065\90066\90067\90068\90069;100065\100066\100067\100068\100069}

Esto es, debido que sumamos una constante matricial vertical con una horizontal (cuando esto sucede, cada elemento de la constante matricial vertical se suma con toda la constante matricial horizontal, generando una matriz de 10 filas x 5 columnas = 50 elementos)

El objetivo de armar esta matriz rectangular es evitar realizar la comparación que sigue en la explicación (el SI) 2 veces, haciéndola solamente una vez.  Esto es ventajoso cuando la matriz tiene más de una condición para su construcción (aunque este no sea el caso).

Hay que tener en cuenta que se colocaron dos operadores de porcentaje que, aunque no eran necesarios según la ubicación de la columna (que solo tenía 2 dígitos numéricos), sirve como colchón por si en la fórmula el valor numérico del valor de la columna en cuestión supera estos dos dígitos).

2. SI($BN$2:$BR$11=H13;(FILA($BN$2:$BR$11)-1)/1%%+COLUMNA($BN$2:$BR$11)-1)

Esta parte, lo que hace es solamente colocar los elementos de la matriz rectangular que coinciden con el valor que se está buscando.  Siendo entonces lo que se genera (en la primera celda), lo siguiente:

{FALSO\FALSO\FALSO\FALSO\FALSO;FALSO\FALSO\FALSO\FALSO\FALSO;FALSO\FALSO\FALSO\FALSO\FALSO;FALSO\FALSO\FALSO\FALSO\FALSO;FALSO\FALSO\FALSO\FALSO\50069;FALSO\FALSO\FALSO\FALSO\FALSO;FALSO\FALSO\FALSO\FALSO\FALSO;FALSO\FALSO\FALSO\FALSO\FALSO;FALSO\FALSO\FALSO\FALSO\FALSO;FALSO\FALSO\FALSO\FALSO\FALSO}

Siendo solamente un valor que cumple con la condición (los valores dentro de la matriz de comparación son únicos).

Al envolver esta parte con la función MIN, se devolverá como resultado el 50069.

3. TEXTO(MIN(SI($BN$2:$BR$11=H13;(FILA($BN$2:$BR$11)-1)/1%%+COLUMNA($BN$2:$BR$11)-1));"F0C0000")

Esta parte también es crucial en la construcción:  La idea es armar, con la función TEXTO, una cadena que entienda la función INDIRECTO en su notación numérica FILA - COLUMNA (también conocida como estilo F1C1).  Entonces, al ser de 5 dígitos (por lo menos, porque puede tener más), los 4 últimos caracteres corresponderán a la columna, y los que sigan, corresponderán a las filas.

El resultado de la función TEXTO es el siguiente: "F5C0069"

4. INDIRECTO(TEXTO(MIN(SI($BN$2:$BR$11=H13;(FILA($BN$2:$BR$11)-1)/1%%+COLUMNA($BN$2:$BR$11)-1));"F0C0000") ; )

La función INDIRECTO, en su primer argumento, se coloca la cadena de texto en su notación FILA - COLUMNA (estilo F1C1).  El segundo argumento, puede ser FALSO (o su equivalente numérico por esencia, 0) o VERDADERO (o su equivalente numérico por esencia, 1).  El argumento FALSO trabaja con el estilo F1C1 (que es el que necesitamos), y el argumento VERDADERO trabaja con el estilo A1 (que conocemos normalmente en la hoja de cálculo).

El resultado corresponderá a la referencia (importante que la función devuelva una referencia, y no solamente un valor) que corresponde a la fila 5 y la columna 69.  Tener en cuenta que se ha buscado devolver el valor desplazado una fila a la izquierda y una columna arriba de la encontrada con la condición de la función SI, que serviría como arranque de la referencia que será modificada posteriormente por la función DESREF.

5. DESREF(INDIRECTO(TEXTO(MIN(SI($BN$2:$BR$11=H13;(FILA($BN$2:$BR$11)-1)/1%%+COLUMNA($BN$2:$BR$11)-1));"F0C0000") ; );;;3;3)

La función DESREF es importante aquí, puesto que es la que permite modificar una referencia en cuanto a su tamaño, el cual buscamos sea un cuadrado de 3 filas x 3 columnas, empezando por el valor que se encuentra en la parte superior izquierda del valor encontrado.  Se debe tener en cuenta también que, esta parte, pudo ser también de la siguiente forma:

DESREF(INDIRECTO(TEXTO(MIN(SI($BN$2:$BR$11=H13;FILA($BN$2:$BR$11)/1%%+COLUMNA($BN$2:$BR$11)));"F0C0000") ; );-1;-1;3;3)

Manejando el desplazamiento hacia arriba y hacia la izquierda con la función DESREF directamente.

6. CONTAR.SI(DESREF(INDIRECTO(TEXTO(MIN(SI($BN$2:$BR$11=H13;(FILA($BN$2:$BR$11)-1)/1%%+COLUMNA($BN$2:$BR$11)-1));"F0C0000") ; );;;3;3);$H13:$L13)

Aquí, con la función CONTAR.SI, se busca contar la cantidad de coincidencias que se encuentran en el recuadro, con respecto a cada uno de los valores que se encuentran en el rango H13:L13 (para la primera línea).  Esta parte, nos arrojará una matriz horizontal de 5 elementos (por la cantidad de elementos que tiene el rango H3:L13) como la siguiente:

{1\0\0\1\0}

Cada 1 corresponde a si se encontró el vecino buscado o no. 

7. =SUMA(CONTAR.SI(DESREF(INDIRECTO(TEXTO(MIN(SI($BN$2:$BR$11=H13;(FILA($BN$2:$BR$11)-1)/1%%+COLUMNA($BN$2:$BR$11)-1));"F0C0000") ; );;;3;3);$H13:$L13))-1

Esta parte tiene como objetivo SUMAR los valores que se generaron en la matriz anterior.  Hay que tener en cuenta que, por lo menos, siempre se encuentra un elemento, y es, porque dentro del cuadrado 3 x 3 siempre está el valor de la coincidencia de la función SI que sirvió como base para generar el recuadro (el valor está en toda la mitad del cuadrado 3 x 3).  Este valor unitario debe ser restado al final, puesto que solamente se trata de encontrar vecinos, no el mismo valor que generó el recuadro.

En el ejemplo que se ha estado analizando (la primera celda, BN13), el valor resultante es 1.

Espero se haya entendido, con la explicación realizada, el propósito de la construcción de la fórmula en cada una de sus partes.  ¡Bendiciones para todos!

Saludos @John Jairo V

Pues Sin Palabras por tu explicación mi hermano, la verdad es que has generado una Solución para un Excel MUY Avanzado, no por gusto varias personas ya te lo habían comentado anteriormente en el Tema.

Sin dudas debo dedicarle mucho tiempo a intentar entender todos los detalles que has tenido en cuenta y que a su vez hacen que ese CUADRO y la cantidad de números del Sorteo puedan variar su estructura sin afectar la solución, por supuesto ya lo he probado y la solución es perfecta.

Bueno John muy, pero Muy Agradecido por tus Explicaciones, donde es evidente tus dotes Metodológicas de Profesor.

Aprovecho para comentar que mi Empresa desde ayer me ha encontrado un hueco para trabajar y ya mis visitas al Foro serán muy limitadas.

 

Un Fortísimo Abrazo @John Jairo V.

Lázaro.