Reto 3 (enunciado).- La vaca y el prado circular

En este vídeo se plantea un nuevo problema para resolver con Excel: Se quiere atar una vaca a un poste situado en el borde de un prado circular de modo que alcance solamente a comer la hierba de la mitad del prado. ¿De qué longitud deberá ser la cuerda? Esperamos vuestras ideas y vuestras propuestas. ¡Ánimo!
Más información sobre Reto 03 (solución): • Reto 3 (solución).- La...
Donaciones: www.paypal.me/...
Excel Avanzado para Administración de Empresas en RRSS:
en Facebook: / excelrpv
en Twitter: / excelrpv

[@TrackLander]

Pues otro interesante reto, veremos si se me da mejor que el anterior. Saludos cordiales profesor.

[@eaae]

Ánimo. Espero sus propuestas.

[@dralejandro25]

Lo resolví de una forma no muy elegante, ni eficiente y ni tampoco tan exacta pero me gusto mucho. Primero en las primeras celdas puse los datos del radio del campo, largo de la cuerda , centro del radio, posición del nudo de la cuerda, área hermano 1 y 2. Luego abajo genere una tabla que representa un plano cartesiano con coordenas desde el origen hasta (100,100) con intervalos de 0.1. Cada celda de la tabla le puse una formula que hacía más o menos lo siguiente : - si la coordenada se encuentra fuera del terreno ponga un - 1 - si la coordenada se encuentra en el área de la vaca ponga un 0 - si no cumple los requisitos anteriores pondrá un 1 que representa el terreno del hermano que venderá el pasto Con un formato condicional puse que - 1 fuera negro, 0 café y 1 verde Al alejar el zoom se dibujó una situación muy parecida a lo que ud puso en el video. Para calcular el "área" de cada uno puse un contar 0 y contar 1 respectivamente Finalmente para solucionar el problema utilicé el solver que minimiza la diferencia entre ambas áreas variando el largo de la cuerda.

[@eaae]

El método que describe es impecable. La solución que yo propongo (kzbin.info/www/bejne/mquseZxmr7F_bJI ) sigue los mismos pasos casi al pie de la letra (con pequeñísimas diferencias que no cambian su esencia). Le felicito sinceramente por su detallada respuesta.

[@XURXOSM]

A mi edad volver a querer sabe mas de EXCEL..... mente sana. No solo los sudokus. Estos retos ya se me escapan.... :) El mío por ahora es que he conseguido otra hoja con una columna con una lista de números, uno en cada celda ;( En otra columna 5 números que se han creado aleatoriamente. A su derecha una celda con la fórmula =SI(CONTAR.SI(B$3:B$30;E3)>0;"Sí";"No") Lo que hace es revisar si alguno de estos 5 números aparecen en la primera colunma y si es así, aparecería en la celda, Si y en caso contrario No. A su vez le mete colores con un formato condicional. Solo falta, que en la primera columna tambien cambien de color los que coincidan. Ejemplo, en la primera columna está el número 1, y en el aleatorio, tambien aparece el número 1. Entoces aparecerá Si y la celda coloreada. Lo que falta ahora es que se cambien de color en la primera colunma la celda de ese número que ha coincidido. Esto tendría que ser en este caso para 5 números aleatorios que si coinciden con los de la primera columna, estos han de cambiar de color. Con buscar V quizás, aunque ya se que no es muy "amigo" de esta función. Saludos

[@eaae]

Todas las edades son buenas para aprender más Excel, así que le animo a seguir buscando nuevos retos... En cuanto al problema que plantea, creo que lo tiene prácticamente resuelto. De hecho, ha elegido una formulación idónea para su propósito (CONTAR.SI anidado en un SI). Y si el formato condicional le funciona correctamente en la segunda columna (la E, según creo entender), lo único que tiene que hacer es reproducir la misma fórmula en la primera columna (la B). Utilice otra vez la misma anidación (invirtiendo las columnas, y ajustando las filas) y un formato condicional similar. En este canal tiene un tutorial sobre comparación de listas (kzbin.info/www/bejne/mH3WkmqnapWYd5o ) en el que, como verá, aconsejo usar la misma fórmula que usa usted, así que le felicito y le animo a replicar el cálculo. Si encuentra algún problema o no consigue su objetivo, vuelva a preguntarme y trataré de ayudarle.

[@XURXOSM]

@@eaae mañana sin falta veré ese tutorial. Muchas gracias por contestar.

[@XURXOSM]

@@eaae Buenas de nuevo. Ese vídeo ya lo había visto y fué con el que "aprendí" a usar en mi "reto" y conseguir lo que pretendía. Pero ahora el caso, es el mismo "reto" continúa. Lo quise hacer más "reto". Ahora lo que se trata es una vez resuelto el problema, conseguir que ciertas celdas cambien de color, para remarcar el resultado. Son 2 columnas. La primera con varias celdas con números. La segunda columna tiene, en este caso 5 celdas. En cada celda cada vez que actualizo la hoja, se rellenan con 5 números aleatorios. Con la función CONTAR.SI, comprueba esos 5 números si están en la otra columna y si es así, aparece en la siguiente columna un "Si", caso contraraio "No". En esta misma columna de SI y NO, se colorea de un color en función de si es SI o NO, y en una celda independiente suma cuantos "Si" hay. Lo que falta es que esos números que se repiten en ambas columnas, en la primera, aparezcan coloreados los que coinciden. Por si alguien le quiere dar un vistado, os dejo el enlace. Aviso que viene con una "mini macro". Por si alguien no la quiere abrir, tambien dejo una imagen de la hoja. Saludos y gracias a este fantástico "lugar" de aprendizaje. Imagen: drive.google.com/open?id=1bN_92qpeuXvI9WQwWm_b4nG8MwYiNsvK Archivo drive.google.com/open?id=14sZjj8ilCPD3S6mURbVu_cyK4CI6TdhX Saludos

[@eaae]

@@XURXOSM Le reitero lo que le propuse en mi comunicación anterior: tiene la solución delante de sus ojos ;-) Usted está asociando a la columna E un detector para comprobar si la bolita corresponde a un tema estudiado, y un formato condicional le da el color. ¿Por qué no prueba a hacer lo mismo en la columna B? Lo que quiere es comprobar si a los temas estudiados les ha correspondido una bolita... Asócieles un detector análogo (cambie las B por E y ajuste los números de fila) y aplique un formato condicional del mismo modo. Si quiere que el color aparezca sin asociar una nueva columna a la B, puede incluir en el detector en el propio formato condicional, pero quizás por coherencia sea preferible hacer explícita la detección en ambas columnas.

[@XURXOSM]

@@eaae Mañana sin falta lo probaré. Muchas gracias

[@robertoarauz4206]

Ok, para resolver el ejercicio hay que utilizar trigonometría analítica, en el vídeo solo nos dice que la vaca debe comer la mitad del prado que tiene forma circular y nada mas, no nos da números ni medidas por lo que esos datos son variables... ahora, aplicando trigonometría sabemos que el diámetro es el segmento de recta que pasa por el centro y une dos puntos del círculo, si dividimos ese diámetro obtendremos el radio que es la distancia o longitud de un extremo del círculo hacia el centro, ya está la primera parte del problema resuelto. La segunda parte imagino que es calcular el área del pasto que puede consumir la vaca, explicarlo con palabras es muy difícil ya que se aprecia mejor con procedimientos matemáticos, solo le dejo una forma de visualizarlo y es dibujar otro círculo con el mismo diámetro del prado y debe estar interceptado de manera que su centro sea donde esté el poste que contiene la soga de la vaca. Me tomó un tiempo analizarlo ya que hace mucho salí de la universidad y recordaba poco de esto jejeje, espero que sea así 😉

[@eaae]

El problema se podría resolver usando trigonometría analítica, sí, pero no es lo que buscamos en este reto. Piense que los dos hermanos que se enfrentan a este problema quizás no sean matemáticos y quizás no hayan oído hablar de la trigonometría y, sin embargo, deben ser capaces de hacer el cálculo que les permita compartir la finca y darle cada uno un uso tan distinto, sin que una simple vaca se vaya a interponer en la armonía familiar… En el enunciado no se dan cifras, es verdad. No se dice cuál es la medida del prado. Pero ello no es ningún obstáculo para resolver el problema, porque se puede asumir un radio de dimensión igual a una unidad y obtener una longitud de cuerda relativa a ese radio unitario. Con multiplicar esa medida por el verdadero radio del prado ya tendríamos la medida de la cuerda, así que esta asunción es idónea porque permite calcular de una vez la longitud de cuerda adecuada para prados más pequeños y para prados más grandes. En cuanto a la solución que propone (superponer otro círculo de radio igual al del prado con centro en el poste), me temo que es fácil demostrar que no permitiría a la vaca comer la mitad de la hierba, sino algo menos… Vamos a demostrarlo sin necesidad de recurrir a fórmulas ni a ecuaciones. Si quiere medir la mitad del prado podría trazar un eje vertical (un diámetro vertical). Lo que queda a un lado y a otro es idéntico: ya tenemos dos mitades. Si el centro de este nuevo círculo que usted sugiere superponer (o sea, el que define la cuerda) está en el poste, y su radio es el mismo del prado original, entonces la vaca alcanzaría exactamente hasta el centro del prado en el eje horizontal (según se ve en el dibujo del vídeo). Es decir, en esa línea recta horizontal llegaría hasta la mitad del prado, pero por encima y por debajo de esa horizontal no llegaría hasta el diámetro vertical. Es decir, la mitad izquierda del prado quedaría intacta, pero también quedarían fuera del alcance de la voraz vaca unas áreas pseudo-triangulares en la otra mitad del prado, lo que demuestra que el pobre animal no llegaría a comer medio prado, sino bastante menos… Dicho en otras palabras, la cuerda no debe ser de longitud igual al radio, sino mayor… Su planteamiento, en cualquier caso, es interesante porque permite ir visualizando la relación que hay entre el radio del prado, la longitud de la cuerda y el porcentaje de superficie que se llega a abarcar. De momento, ya hemos conseguido una cota inferior para la longitud de la cuerda: debe ser necesariamente mayor que el radio… Y, razonando de modo similar, es fácil deducir que si fuese igual al diámetro (o sea, dos veces el radio), la vaca alcanzaría el otro extremo del eje horizontal, así que comería toda la hierba porque la cuerda le permitiría alcanzar todo el prado. Así que ya sabemos que la cuerda debe tener una longitud entre uno y dos radios… No es un avance desdeñable, pero… ¿dónde interviene Excel en este razonamiento? 😉

[@yaredglez8177]

¿Se podría atar a la vaca al centro? Y que la longitud de la cuerda sea de la mitad del radio.

[@eaae]

No se puede atar a la vaca al centro del prado porque el poste está en el borde. Pero aunque se pudiese, el área de un círculo de radio r/2 no es la mitad del área del círculo de radio r, porque el área del círculo es igual a pi por el radio al cuadrado, así que el círculo interior tendría la cuarta parte del área del prado, no la mitad.

[@juanda8022]

Con esta respuesta queda desarmado hasta un balin. Felicitaciones.

[@eaae]

@@juanda8022 Pero el objetivo no es desarmar argumentos, sino ir acotando el camino hacia la solución... ;-)

[@juanda8022]

@@eaae Totalmente de acuerdo. Solo que respuestas tan técnicas y concretas son una demostración mas de sus estupendos conocimientos y no solo de EXCEL. Buena Tarde.

[@juanda8022]

@@eaae Totalmente de acuerdo. Solo que respuestas tan técnicas y concretas son una demostración mas de sus estupendos conocimientos y no solo de EXCEL. Buena Tarde.

[@omibarram21]

Ok, buen ejercicio para resolver por círculos concéntricos. Envié mi respuesta a su correo.

[@eaae]

Los círculos concéntricos son los que comparten centro, y me temo que en este caso el centro del prado no coincidirá con el centro del círculo al que podría acceder la vaca, porque ésta estará atada a un poste (su centro) situado en el borde del prado. Se trata de un problema que, a pesar de su aparente sencillez, encierra alguna complejidad analítica cuando se afronta usando matemáticas... Y, sin embargo, es posible resolverlo con Excel de un modo relativamente sencillo, sin grandes conocimientos de matemáticas.

[@omibarram21]

@@eaae Más bien círculos inscritos, pero estaré intentando ser más cercano a lo propuesto.

[@eaae]

@@omibarram21 El círculo inscrito sería interior al prado, a cuyo borde podría ser tangente. Pero creo que tampoco es un círculo inscrito el que abarcará la vaca, porque teniendo centro en el borde del prado, una parte de ese círculo quedaría fuera del prado. El área que nos ocupa (el área que debe ser igual a la mitad de la del prado) es la formada por la intersección de dos círculos con centros distintos y radios distintos.

[@XURXOSM]

Pues era la digestión, Me tomé un par de copas de agua mineral, et voilá. Ambas preguntas, resueltas. El coloreado ya aparece drive.google.com/open?id=1NmE9Ch-ct8vhjQxjQ_RrxM0si38HqDvR y el ver donde guarda las fórmulas condicionantes también, drive.google.com/open?id=1B0sIgGPLG77AC273QDlR8j-ydRkW3BA1

[@eaae]

Me alegro de que le haya sido útil.

[@LEO_rumano]

Me gusta mucho este reto ya que yo soy un granjero y ademas me gusta mucha la geometría trigonometria y todo lo relacionado Tengo entendido que los 2 hermanos saben algo de geometría y tienen un excel para solucionar esto . Me gustaría saber el nivel de geometría de los 2 , para saber que camino voy a elegir para la resolución ¿ Saben el Teorema de Pitagoras o el teorema de cuerdas ? ¿ Saben algo de geometría analítica ? ¿ Saben Integrar ? :))))) Tengo un camino bastante sencillo para esto y creo que voy a usar la formula del área de un segmento circular que es A = R^2*(alfa - sen(alfa))/2 , donde R es el radio y el alfa es el angulo central . Calculo el área de los 2 segmentos circulares que se forman . Uno con radio R igual al radio de la circunferencia del terreno y otro segmento circular de radio igual a la longitud de esta cuerda . Luego la suma de estos 2 segmentos circulares debe ser igual a la mitad del área de la circunferencia de radio R(terreno) Pues , vamos a tener 3 variables (2 ángulos y un radio = la longitud de la cuerda) , los ponemos en 3 campos de variables para el SOLVER y en el objetivo le ponemos esta igualdad , osea la diferencia entre el 1/2 área terreno y la suma de los 2 segmentos sea 0 . Algunas restricciones para ángulos y creo que funciona . Mañana por la tarde lo intento presentar en un excel , a ver que sale . Lo del grosor de la cuerda ¿¿¿??? , no lo pillo

[@eaae]

El reto está pensado para que lo resuelvan dos hermanos "intemporales", probablemente anteriores al desarrollo del cálculo integral y diferencial, que desconocen a Leibniz, a Newton, a Cauchy... y tampoco tienen la más remota idea de trigonometría, ni saben qué es la geometría analítica... Saben contar usando los dedos, o quizás recurriendo a alguna técnica un poco más sofisticada, pero no mucho más... El planteamiento que usted propone parece muy interesante, y me encantará ver cómo lo desarrolla, pero la idea que sugiero es intentar resolver el problema como lo podrían hacer dos granjeros ajenos a las matemáticas, pero con mucho sentido común... De hecho, la solución que tengo desarrollada utiliza Excel únicamente para reproducir a gran velocidad el proceso que podrían seguir estos hermanos, pero le aseguro que mi hoja de cálculo no hará nada que no pudiese hacer cualquier "cowboy" en medio de un terreno, sin Excel, sin ordenador, y sin wi-fi...

[@LEO_rumano]

@@eaae , creo que ya se por donde va el tema . Si estos 2 hermanos saben poco o casi nada de geometría , debe ser porque son de Mónaco , mas preciso de Montecarlo , jejeje . Creo que por ahí va el tema . Ahora si esto , creo que lo que han hecho , ha sido dibujar (crear) un circulo a escala mas pequeña (por ejemplo 1 metro de radio) y han tirado dentro del circulo por ejemplo (granos de trigo) . Luego con la ayuda de una cuerda ajustable y un punzante en la punta han ido dibujando arcos de círculos desde el perímetro del otro circulo y han contado los granos de cada lado . En el momento en el que el conteo de granos de un lado y del otro de la cuerda les han salido igual se han quedado con la longitud de la cuerda . Y por supuesto han trasladado esta longitud a la escala de su terreno(ya que es directamente proporcional al radio de 1 m que han usado antes) . Es que es sorprendente lo que hacían hace cientos y miles de años sin saber matemáticas ni tener excel :))) . A ver si puedo replicar lo dicho , en una simulación con el excel y que sale .

[@eaae]

@@LEO_rumano Su intuición es extraordinaria... Le felicito. Avance por ese camino.

[@LEO_rumano]

Esta es mi respuesta al reto kzbin.info/www/bejne/mZu7k3aGl65ghMk&t Espero que se entienda y lo mas principal , que sea de ayuda .

[@eaae]

Su respuesta es estupenda. Le felicito por su explicación y por el vídeo tan detallado. La idea de usar una simulación con puntos aleatorios para el cálculo del área es muy interesante y tiene aplicación directa en cartografía, por ejemplo. Puede comprobar que en la solución que yo he propuesto (kzbin.info/www/bejne/mquseZxmr7F_bJI ) utilizo un método parecido, en el que no hace falta una matemática muy avanzada y en el que, a diferencia del suyo, en lugar de generar puntos aleatorios hago un estudio sistemático de una serie de puntos regularmente distribuidos por el espacio. La fiabilidad o la precisión en este caso viene determinada por la distancia entre esos puntos o, de modo equivalente, por el número total de puntos que se analizan. Creo que su método complementa al mío y me parece que es muy interesante para todos los seguidores de este canal, así que le reitero mi felicitación y mi agradecimiento por su aportación.

[@XURXOSM]

La "maravillosa" fórmula ya está perfecta. Solo falta adornarla. El libro y sus 2 hojas: drive.google.com/open?id=1hj566E01gTPHmwB5PgMcMS7ENcVQxvye una imagen del resultado después de intentar que las celdas con cifra = a 0, aparezca el resultado en blanco: drive.google.com/open?id=1mPJZYsCAGz-S_CXEst7or96PABySVpPf

[@eaae]

Pruebe a poner la condicional dentro de la función TRANSPONER.

[@XURXOSM]

@@eaae antes o dentro de su fórmula? Ya probé y no salía bien. En la siguiente contestación en este mismo tutorial dejé enlace del libro y foto

[@eaae]

@@XURXOSM Su libro no es editable.

[@XURXOSM]

@@eaae Lo acabo de subir en ZIP drive.google.com/open?id=1gVAUQDiDY4rpvVfHA9Mkm32ujm1nt3Rz

[@XURXOSM]

@@eaae Ahora, si. El condicional lo he usado con su fórmula: ={=SI(TRANSPONER(Hoja1!B15:M15)=0;"";TRANSPONER(Hoja1!B15:M15))}

[@XURXOSM]

Una nueva cuestión: Algo que no doy encontrado. Cuando se trata de rellenar celdas, escribiendo parte de un texto, que lo reconoce por que hay una tabla con los distintos nombres que siempre se repiten. Imaginemos frutas. Rellenamos una columna con distintas frutas. Todos esas celdas con nombres de fruta las asignamos a una tabla. Ahora en otra hoja o en otro especio de la misma hoja, hemos de ir rellenando celdas con un nombre de esas frutas. Con la función de VALIDACION DE DATOS (LISTA) ya lo podríamos hacer, pero no funciona el que al escribir las primeras letras, aparezca la palabra buscada y no tener que estar todo el tiempo pulsando en la flechita y luego buscar la palabra que necesitamos. Saludos

[@eaae]

Para que al escribir las primeras letras aparezca la palabra entera esa palabra debe estar ya en la lista. La primera vez tendrá que escribirla entera o seleccionarla en el desplegable, y en las siguientes ocasiones ya le funcionará.

[@XURXOSM]

@@eaae Muchas gracias, como de costumbre contestar a las consultas. Efectivamente ya lo había comprobado. Una vez escrito la primera vez , la siguiente ya lo hace casi solo. Igual que cualquier otro texto.

[@XURXOSM]

Buscando en su canal, no he encontrado nada sobre referencias absolutas y relativas. En la siguiente exprexión: =+'VENTAS 2020'!B34 lo que hace es recoger una cifra de otra hoja, dentro del mismo libro. Cada mes va cambiando. En la expresión, pertenece al mes de enero. Ahora tocaría el mes de febrero, si en la "mirilla +" cuando pongo el puntero en esa crucecilla y arrastro hacia abajo, aparece =+'VENTAS 2020'!B35 y lo que querría que apareciera es C34. Es decir fijar el 34 y que vaya cambiando la letra de la columna. Ya le he puesto el $ pero ni caso. Como fijar el "34" y que avance en nombre columna C D E, etc?? Ya he puesto el signo B34$ y da error. Si escribo B$34, queda fijo todo y repite el anterior, lo mismo que si escrigo $B$34 Saludos

[@eaae]

Seleccione en la hoja en la que quiera formular un rango vertical de cinco celdas (o las que quiera), y con esas cinco celdas seleccionadas introduzca la fórmula 'TRANSPONER(Ventas!B34:F34)' (o el número de columnas de la hoja de ventas que necesite) y valídela como matricial. Si no consigue lo que busca, facilíteme un enlace a su hoja y se lo resuelvo.

[@XURXOSM]

@@eaae Lo acabo de probar y no funciona. Ocurre lo mismo que usando $. El primer resultado aparece correcto. En cuanto se extiende la formulación de la celda hacia abajo, repite la misma celda. Pero si se extiende a la derecha, funciona correctamente. Había pensado en crear nombres a las celdas y quizá así funcione. Le dejo el enlace de la hoja. Como los resultados son mensuales, hasta que pasa el mes, la celda aparece vacía. Habría que después de resolver el caso y para que ya quede todo automático, meter una condicional de si está vacía la celda, que se quede en blanco y si no, aparecería el resultado. drive.google.com/open?id=1IygRgFLBcrm7k5Sn8GilNHxEvdihdfVX

[@XURXOSM]

@@eaae Se me olvidara validar como matricial, lo acabo de hacer: =TRANSPONER(Hoja1!E15:H15), pero al extender hacia abajo, sigue igual ..