Referencias circulares en Excel: ¿error o acierto?
ฝัง
- เผยแพร่เมื่อ 26 ก.ค. 2024
- En este vídeo se explica qué son las referencias circulares en Excel, qué tipos hay, y cómo se pueden utilizar intencionadamente en cálculos complejos, porque las referencias circulares no siempre son un error, sino que pueden ser un recurso muy interesante.
Más información sobre referencias circulares: ayudaexcel.com/referencias-ci...
Más información sobre el método PERT en Excel: • Gestión de proyectos (...
Más información sobre usos de las referencias circulares para fijar fecha y hora de registro de datos en Excel: • Fecha y hora invariabl...
Más información sobre búsqueda con varios criterios: • Técnicas avanzadas de ...
Más información sobre la herramienta Buscar Objetivo: • Resolución de ecuacion...
Más información sobre el complemento Solver: • Optimización en Excel ...
Donaciones: www.paypal.me/ExcelRPV
Excel Avanzado para Administración de Empresas en RRSS:
en Facebook: / excelrpv
en Twitter: / excelrpv
Excelente, tu explicación solucionó mi problema.
Te agradezco por la información brindada!!
Saludos desde Costa Rica.
Muchas gracias, Juan. Me alegro de que este tutorial le haya sido útil.
Excelente, me ayudo full para cerrar los balances finales... mil gracias
Gracias a usted. Me alegro de que le haya sido útil.
Excelente. Me sacó de un intríngulis que tenia con una formula y que no veia por donde solucionar, Gracias
Gracias, Mauricio. Me alegro de que le haya sido útil.
Que buena explicacion...me dio mas claridad en el asunto...nota 10
Muchas gracias. Me alegro de que el tutorial le haya sido útil.
Excelente, muchas gracias por sus aportes...
Muchas gracias a usted por su comentario. Me alegro de que le haya gustado.
Muy agradeido, gracias por su aporte.
Gracias a usted.
Dios mio, se puede suscribir dos veces ¿? por que este vídeo lo merece...
Recién conozco el canal (ahorita) y mi trabajo no requiere excel avanzado, ni siquiera intermedio, pero me gusta excel soy autodidacta, y me apasiona resolver problemas con las formulas de excel y este vídeo merece 1 millón de vistas...
Recontra suscrito maestro...
pdta: uso php (intermedio) y macros (a nivel básico pero las formulas me encantan)...
Me alegro de que le haya gustado este vídeo. No creo que pueda suscribirse dos veces, pero puede ver los vídeos tantas veces como quiera... ;-) Y puede recomendarlo.
Le agradezco mucho su comentario.
Qué gran coincidencia, profe, ayer me preguntaba si había algún tutorial referente a referencias circulares. Gracias por la clase
Gracias a usted por su comentario. Me alegro de que este vídeo le haya gustado.
Didáctico a más no poder. Muchísimas gracias.
Le agradezco el comentario. Me alegro de que le haya gustado el tutorial.
Genial!!!! funciona de maravilla
Muchas gracias. Me alegro de que le haya funcionado.
Genial la explicación. Ni había oído hablar del _cálculo iterativo_ ni sabía a qué se refería. Muy didáctico, como siempre.
Me alegro de que este contenido le haya resultado novedoso. Su uso puede resultar muy útil también en otras situaciones.
Una explicación excelente . Había oído hablar del calculo iterativo por un problema de referencia cruzadas, ( en otros videos que he visualizado ,se explicaban el porque ocurrían, pero no detallaban con claridad el uso del calculo iterativo) que para mi las formulas estaban bien, pero las alarmas de Excel a este respecto y me tenían loco para solventarlas, mi enfoque era bueno, pero esas alarmas me desconcertaban. Ahora me funciona todo correctamente. Gracias !!! Me suscribo !!!
Muchas gracias, Javier. Efectivamente, las referencias circulares pueden surgir por un error o intencionadamente, y en este último caso constituyen un recurso de cálculo muy interesante. En este canal tenemos varios ejemplos de uso intencionado de referencias circulares que pueden resultarle novedosos. A modo de ejemplo, puede ver nuestro vídeo sobre el juego de la vida, de Conway (th-cam.com/video/fGJQRFFJrgY/w-d-xo.html) o el que dedicamos a la generación de números aleatorios a demanda, no volátiles (th-cam.com/video/gKozULf7ftY/w-d-xo.html).
Claro y muy bien explicado. Gracias
Muchas gracias a usted por su comentario.
Como dirían los italiano Bravo bravisimo!!!! un ejemplo muy bueno de como utilizar excel en modo avanzado.
Muchas gracias por el vídeo. Un saludo.
Me alegro de que le haya gustado. Gracias por comentar.
Después de usar Excel desde su anterior generación, ma acabo de enterar de una utilidad que ni sospechaba.
Elude tener que programar, en muchas situaciones.
Muchas gracias.
La referencias circulares (intencionadas) son un recurso muy útil. Le sugiero que les busque utilidad. En este canal tiene algunos ejemplos, como esta aplicación a la gestión de proyectos (th-cam.com/video/qnZJ_tIRDas/w-d-xo.html ) o esta otra para generar sellos de tiempo (th-cam.com/video/icups7cslUs/w-d-xo.html ).
Excelente tutorial.
Nunca había podido entender las famosas referencias circulares.
Si este es un ejemplo sencillo, como sera uno complicado.
Mil gracias.
Buen día.
Gracias por su comentario.
El ejemplo de las edades de Ataúlfo y Veneranda no es muy complicado... Puede ver uno "un poco" más complicado en este tutorial: th-cam.com/video/qnZJ_tIRDas/w-d-xo.html. De todos modos, lo que hace el caso más complicado es el tipo de problema que se afronta, porque las referencias circulares siempre tienen la misma estructura: una sucesión de referencias que acaban remitiendo al punto de partida.
En próximas fechas les mostraré una nueva aplicación muy interesante de las referencias circulares.
Excelente, muchas gracias aclarado el tema. Genial
Gracias, Raúl. Me alegro de que le haya gustado.
Excelente! Muchas gracias por compartir esta información, perfecto 👏
Muchas gracias, Fernando. Me alegro de que le haya gustado.
Muy muy interesante tutoríal. Muchísimas Gracias
Muchas gracias. Me alegro de que le haya gustado.
¡Excelente profesor! ¡Excelente video!
Muchas gracias. Me alegro de que le haya gustado. Las referencias circulares son un recurso muy útil, del que nos aprovechamos en diversos cálculos explicados en otros vídeos.
Gracias! Tenía días buscando hasta q encontré tu video, quería mandar yo y poner una referencia a propósito jaja gracias amigo!
¿Quién iba a mandar en sus hojas de cálculo? ¿Excel o usted? ;-)
Muchas gracias por su comentario.
No solo es un vídeo interesante, sino que es instructivo. En mis inicios con Excel enseguida tuve que tratar con referencias circulares "buscadas" y la iteración. Un caso "común" es cuando se trata de calcular un sueldo bruto habiendo pactado un sueldo líquido.No es buena práctica, pero si muy común. Este es un cálculo que, si bien lo resuelven los programas de nóminas, siempre acaba surgiendo en el uso de Excel. Gracias por el aporte y saludos.
Muchas gracias por el comentario.
El caso que plantea puede originar referencias circulares como la que en el vídeo sirve de ejemplo de las de tipo indirecto (o mediatas). Son muchos los casos en los que un cálculo “de ida y vuelta” entre dos variables (o sea, que puede tener interés en ambos sentidos de la posible relación causal) provoca una autorreferencia: la relación entre sueldos brutos y netos, la relación entre capital inicial o final de una capitalización, los precios con impuesto y sin impuesto, etc. En todos estos casos (y en muchos otros), tiene perfecto sentido calcular el primer término a partir del segundo, o el segundo a partir del primero. Formular directamente cada una de las variables en función de la otra provoca una referencia circular, pero en este tipo de casos creo (y es una mera conjetura, porque esta opinión dependería de la estructura de la correspondiente hoja) que es preferible evitar la autorreferencia. Según las necesidades de cálculo, una opción sería formular uno (sólo uno) de los dos sentidos, y usar Buscar Objetivo cuando se deba calcular en el sentido opuesto. Esta opción simplifica la hoja de cálculo y la introducción de datos exógenos, pero genera más trabajo para obtener los resultados. Otra opción sería habilitar celdas específicas para indicar exógenamente qué tipo de dato se está introduciendo (si un sueldo bruto o neto, o un capital inicial o final, o un precio con impuestos o sin ellos) y formular el resultado con una condicional que permita obtener el resultado correcto en el sentido que se requiera. De esta forma, habría sólo una fórmula (que podría calcular las dos cosas), así que no habrá referencia circular.
Las referencias circulares admiten distintas tipologías. En el vídeo distingo las directas y las indirectas. Pero también podríamos distinguir las evitables y las inevitables… Su comentario, como de costumbre, ha iluminado este aspecto, que me parece también muy interesante.
Su amplia respuesta me ha hecho ver que es un tema en cuyo conocimiento se puede profundizar mucho con Excel. Gracias
Verdaderamente es la primera vez que veo un video sobre lo de referencia circular y lo del calculo iteractivo, excelente explicacion y demostracion en algo tan sencillo como lo de calular edades basados en problemas de calculo elemental. excelente mil gracias
Le agradezco su comentario y me alegro de que le haya gustado este vídeo. Hoy mismo subiré un nuevo tutorial donde se hace un uso intencional de las referencias circulares con una finalidad completamente distinta. Esté atento. Espero que le parezca interesante.
cuales el link que usted tiene un video de como crear tu sistema de contabilidad
@@robertoleon3965 Aún no está listo, pero tengo intención de preparar algún tutorial sobre la materia.
Dobabes... Excelente video!!!! No tenía claro no que era una referencia circular y me acabas de despejar mi duda!!! Te acabas de ganar un sub
Me alegro de que le haya resultado útil. Bienvenido al canal.
Muy interesante. Saludos!
Muchas gracias, Iván.
Me ha quedado clariiiiisimo. Gracias!!
Muchas gracias. Me alegro de que le haya resultado comprensible.
@@eaae Donde la encuentro?
El que manda es el usuario... muy bueno, excelente video muy didáctico...gracias
Efectivamente, el usuario es quien debe regir los procedimientos. En el ámbito empresarial es muy frecuente que la gestión se adapte a los requerimientos impuestos por un determinado programa de gestión. La gran ventaja que tiene Excel es que permite invertir esa subordinación y que sea el programa o la herramienta de gestión la que se doblegue ante las decisiones o las técnicas de gestión por las que que la dirección de la empresa haya optado. En resumen: la empresa no debe conformarse con lo que el programa hace, sino que el programa debe hacer lo que la empresa necesita.
GRACIAS......
Muchas gracias a usted, José.
Excelente.
Muchas gracias. Me alegro de que le haya gustado.
Brillante explicación, muy extensa en el problema y un poco más breve en los ejemplos del uso correcto la las referencias circulares.
Ya conocía este tipo de "errores" y ahora, gracias a este tutorial, descubro que no siempre son errores.
Bueno, más impactado me quedé con el otro vídeo que utiliza esta técnica de autoreferencia, el que explica como registrar la fecha de creación de un apunte o registro, y la fecha de eliminación de dicho registro. Parece casi magia.
(Por cierto, tengo que reconocer que no he sido capaz de resolver el problema de calcular, sin utilizar macros, la fecha de modificación de una celda. ¿Seguro que se puede hacer? 😇)
Gracias por la lección. 👍
Me alegro de que le hayan parecido interesantes este vídeo y el relativo a datación automática (th-cam.com/video/icups7cslUs/w-d-xo.html). Como usted resume con acierto, las referencias circulares no siempre son errores, aunque el mismo Excel parece darles esa condición con carácter general. Usarlas a propósito puede ofrecer resultados sorprendentes, casi magia, como usted dice. Le invito a que vea otro posible uso en una formulación algo más compleja para calcular tiempos de proyectos: th-cam.com/video/qnZJ_tIRDas/w-d-xo.html.
En cuanto al problema del cálculo de la fecha de modificación de una celda, creo que puede ser una buena cuestión para inaugurar una sección de "retos"... ;-)
Muchas gracias por su comentario.
gracias
Gracias a usted.
Excelente
Muchas gracias, Ernesto.
Muy bueno !!! Hoy tuve un error de referencia circular y este vídeo es uno de los que tenia pendiente de ver （╹◡╹）
Muchas gracias por su comentario. Espero que le haya resultado de ayuda.
Ere muy bueno con el excel pero no dejes de explicar como lo haces por que no hay comunicadores o profesores que expliquen como tu lo haces. Nuevamente muchas gracias.
Espero seguir ofreciendo contenidos que le resulten de interés. Muchas gracias por su comentario.
Crack!!
Muchas gracias.
¡Me encanta su humor!
Muchas gracias por su comentario, José Manuel. Me alegro de que le guste el modo de exponer estos contenidos.
muy buen video, si se utilizara la función redondear para poder obtener los valores más acertados a lo que deseamos, tendría alguna ventaja sobre el cálculo iterativo ??
La función REDONDEAR no cambia la esencia del método. Únicamente le permitiría obtener valores enteros o con el número de decimales (precisión) que elija, pero las referencias mutuas entre las celdas seguirán existiendo y el cálculo iterativo deberá estar habilitado en cualquier caso. Gracias por su comentario.
Excelente ... o más bien "Excel"ente.
Muchas gracias. Me alegro de que le haya gustado.
Buenos días. Nos aparece el mensaje de referencia circular y en la opción comprobación de errores, no se activa referencia circular para corregir el error. ¿Como podemos identificar y corregir el error para que el mensaje no aparezca mas?
En el vídeo se explica cómo activar el cálculo iterativo, lo que le permitirá calcular las fórmulas que intervienen en las referencias circulares sin problema.
@@eaae
Ok, haremos la prueba. Mil gracias
Una duda. En el ejemplo de Veneranda y Ataulfo, cuando se habilita el cálculo iterativo el resultado es aproximado, pero si en la opción de cambio máximo le ponemos un 0 el resultado es exacto, ¿es esto correcto? ¿Si queremos un resultado exacto podemos indicar siempre un 0? Un cordial saludo y felicidades por su canal.
Sí. En este caso parece que la solución llega a ofrecer números enteros. Pero tenga en cuenta que Excel trabaja con una mantisa de hasta quince decimales. y al exigir una precisión como esa (total), en determinados cálculos pudiera no alcanzarse una solución entera o con la precisión exigida.
Yo conocía de los errores xq me ocurren a menudo por error al arrastrar el rango de suma, pero los soluciono de inmediato. Pero con la profundidad conque usted los aborda, no
Muchas gracias, Carlos. Me alegro de que estos contenidos le hayan resultado de interés.
Hola! Antes de nada dar las gracias por la explicación, como siempre muy didáctica y clara. Por supuesto Like como una catedral como en todos los demás vídeos que he visto.
Tengo una duda que me gustaría que me diese alguna pista para resolverla. Resulta que tengo una fila con16 datos (A) que son la suma de los 16 datos de otras dos filas, una de las filas es exógena (B), pero a la otra fila (C) tengo que ir sumándole a cada celda el valor de una celda en concreto (en números enteros) hasta que la suma de cada una de las celdas del rango de 16 datos (A) sea igual o lo más próximo a un valor en concreto, el 32, pero sin pasarse, es decir, que si en la celda que voy variando pongo un 15 y resulta que la suma da 32,1, tengo que poner un 14.
Esto lo hago normalmente a mano, y aunque es relativamente sencillo dar con la solución, me gustaría automatizarlo sin tener que recurrir a macros.
Claramente es una referencia circular, y habiendo visto otros vídeos creo que con la herramienta solver podría resolverse, ¿o no?
¡Una pista por favor! (o dos)
Gracias y un cordial saludo.
Muchas gracias por su comentario.
Si no entiendo mal su consulta, en cada celda endógena sólo tendría que calcular la parte entera de la diferencia entre el número que consta en la celda exógena y 32. Si esta interpretación no es correcta o la sugerencia no es útil, comparta una hoja de cálculo de ejemplo e intentaremos ayudarle.
@@eaae Hola de nuevo. He subido una hoja de excel donde se ve cómo son las operaciones, creo que se entenderá mejor: mega.nz/file/PUkwHIib#8u2oj9dg3gsUaDShxJASyp6u8yH1yfyTX0kQJr9bWDw
Muchas gracias nuevamente y un saludo!
@@Xoalde La fórmula de A3, que es muy larga y no es consistente, se puede simplificar mucho si la sustituye por esta alternativa matricial:
=SUMA(SI(D5:S5>0;D5:S5;0))
A partir de ahí, creo que basta con usar Solver con la siguiente configuración:
Maximizar $A$3
Variable: $A$2
Sujeto a (restricción): $A$3
@@eaae Buenos días. Antes de nada agradecerle el interés y la ayuda. La alternativa matricial de la fórmula A3 es tan bella como simple. Lo que me queda por aprender! En cuanto a lo utilizar Solver, funciona a la perfección, pero me calcula el valor exacto que da 32, con decimales, y yo lo que busco es un valor entero, sin redondear. Por ejemplo, en el caso de la hoja que le he compartido, la celda A2 me muestra el valor 25, pero en realidad es 24,7349824440271, así que debería devolverme un 24, el valor entero más próximo cuyas operaciones dan una suma de 32.
En cualquier caso es un gran paso hacia adelante y me evita el tener que anda tanteando continuamente, ahora sólo tendré que darle a solver, comprobar el resultado y decidir si lo dejo como está o lo modifico una única vez. A mí me vale perfectamente.
¡M U C H Í S I M A S G R A C I A S!
@@Xoalde Le sugiero que haga ese redondeo en una nueva celda a partir de la que calcule Solver (que podrá tener decimales). Otra posibilidad sería añadir una nueva condición al programa de optimización exigiendo que la celda de solución sea entera (integer). Pruebe estas nuevas ideas y dígame, por favor, si le resultan útiles.
Muy bueno el video, soy de Argentina, tengo un problema pero con tres celdas que dependen de lo que escriba en cualquier celda, servirá este método? Saludos gracias
Las referencias circulares se pueden presentar con cualquier número de celdas. Si detalla la estructura de las relaciones entre ellas y la naturaleza de los datos que contienen podríamos ofrecerle alguna sugerencia más precisa.
Claro y meridiano. Y más si se van siguiendo las explicaciones con Excel abierto y reproduciendo los ejemplos.
Muchas gracias por el comentario.
Por supuesto, el mejor aprendizaje es el que se consigue "haciendo", más que "viendo"... y probando las técnicas en casos distintos y con finalidades diferentes.
Genial!!!! Disculpa hay alguna forma de "decirle a Excel" que empiece la iteración con un valor determinado en una de las referencias involucradas?
Muchas gracias por el comentario.
Por favor, precíseme un poco la razón de iniciar la iteración con un determinado valor en una celda. Quizás ese 'punto de partida' pueda ser incorporado al algoritmo mediante alguna opción distinta del cálculo iterativo (que, por cierto, también podría reproducirse tabularmente).
@@eaae Si, mi duda surgió del hecho que ví en tu video que haciendo uso de la referencia circular "Excel resuelve sistemas de ecuaciones mediante cálculo iterativo", el problema es que surgen situaciones en el que las ecuaciones planteadas tienen más de una única solución, en dicho caso el punto de partida de la iteración influye en la solución que te ofrecería Excel.
Por ejemplo, para encontrar la raíz de una función cuadrática por medio iterativo se puede usar el método de newton raphson (es método iterativo), al tener la función 2 raíces (osea 2 soluciones válidas) dependiendo del punto de partida Excel te ofrecería una u otra raiz, y quizás a mi por alguna razón me importe conocer la solución de la brazo derecho de la función cuadrática y en ese caso es útil hacer que la iteración comienza en un valor determinado.
Espero haberme hecho entender.
Muchas gracias por tomarte el tiempo :)
@@ricardolescano6451 Tiene razón. Se explica perfectamente.
Me temo que las herramientas nativas de Excel que utilizan cálculo iterativo apuntan a una única solución. Cuando he tenido que resolver algún problema con varias soluciones (por ejemplo, en programación lineal con alguna restricción paralela a la función objetivo o, por supuesto, el caso que plantea para hallar las raíces de alguna función cuadrática), he optado por un método más parecido a la "fuerza bruta", desarrollando los cálculos tabularmente. Usando muchas filas y acotando adecuadamente el intervalo que se quiere analizar se suelen obtener buenos resultados. Le sugiero que pruebe esta alternativa y me consulte de nuevo si encuentra alguna dificultad.
@@eaae Excel Avanzado para Administración de Empresas A desarrollar de forma tabular se refiere a hacer el procedimiento/algoritmo en una fila y luego "Arrastrar" hasta obtener el resultado/solución?
En caso afirmativo, sí , eso es lo que había hecho anteriormente y me funcionó, lo que veía con esto de la referencia circular que no sería necesario Arrastrar osea sería mucho más compacto, se reduciria todo en una sola fila.
Te agradezco el tiempo, tenes un muy buen canal, tus explicaciones son re claras, estaba viendo fugazmente recientemente en tu canal acerca de la funciones como solver o buscar objetivo(no las conocía) ... Todavía no he terminado de ver esos videos... pero me parece que se podría utilizar la función solver para resolver ecuaciones (del tipo hallar raíz de un polinomio de grado 4 por ejemplo, osea con múltiples soluciones validas) quizás esa sería la función más versátil/potente para resolver este tipo de casos ya que permite acotar según lo que entendí...
Osea todo con el fin de evitar "Arrastrar" pero sin llegar a tener que programar jejjejejej (re vago jajajaja)... Bueno uno busca la eficiencia que no? Jejeje
Mil gracias :)
Saludos desde Argentina.
¿Cómo puedo eliminar una referencia circular que me impide realizar bien una suma?
Depende de cuál sea la causa de la referencia circular. Describa con precisión las relaciones entre las celdas e intentaré ayudarle.
@@eaae Ya pude resolver mi problema, había hecho una suma y entre las celdas que se estaba sumando había puesto que se reste el total de la suma con otra celda. Muchas gracias 😊
@@Minmusic_ O sea, la referencia circular estaba originada por un error al referenciar celdas, no por un problema de planificación del proyecto. No debe preocuparse. Me alegro de que haya conseguido resolver su problema.
Buen video. Tengo un problema, activo el calculo iterativo y me funciona la formula; pero cada vez que cierro el archivo y después lo vuelvo a abrir, el mensaje me vuelve a aparecer y me doy cuenta que la opción de calculo iterativo, está desactivada de nuevo.
Me puede decir cuál es el problema?
Gracias de antemano.
¿Ocurre lo mismo si graba el archivo antes de cerrarlo? ¿Abre algún otro archivo entre el cierre de éste y la siguiente ocasión en la que lo vuelve a abrir?
Le invito a que se suscriba a nuestro canal.
@@eaae Voy a intentar hacer esto y le escribo, gracias
@@ivanaguilar5873 Por supuesto, vuelva a consultarnos si no consigue el comportamiento deseado.
@@eaae Ya lo intenté de nuevo, funciona un par de minutos después de haberlo cerrado, ahorita lo dejé cerrado por más tiempo y lo acabo de abrir y otra vez está deshabilitada la opción de cálculo iterativo.
@@ivanaguilar5873 ¿Pero guarda el archivo o lo cierra sin guardar?