Reglas de validación condicionadas en Excel: Listas desplegables dependientes
ฝัง
- เผยแพร่เมื่อ 13 ก.ค. 2019
- Se explica cómo hacer en Excel listas dependientes usando reglas de validación condicionadas.
Más información sobre ordenación mediante fórmulas: • Ordenación en Excel me... .
Más información sobre funciones de búsqueda y referencia: • ¡Deja de usar BUSCARV!... .
Más información sobre búsquedas con varios criterios mediante concatenación: • Cómo buscar con varios... .
Archivo: drive.google.com/open?id=1fgp...
Donaciones: www.paypal.me/ExcelRPV
Excel Avanzado para Administración de Empresas en RRSS:
en Facebook: / excelrpv
en Twitter: / excelrpv
Rafael, es increíble lo útil que es todo lo que estás compartiendo. Con qué claridad explicas, lo bien que se te entiende. Te estoy muy agradecido. Un saludo.
Muchas gracias por su comentario. Me alegra que estos tutoriales le sean de utilidad.
EXCELENTE VIDEO, ES EL UNICO QUE POR LO MENOS SE ACERCA MUCHO A LO QUE UNO BUSCA CON LAS LISTAS DESPLEGABLES EN BASE DE DATOS
Muchas gracias. Me alegro de que le haya gustado.
Usted es un genio profesor...y empiezo a creer que usted no es de este mundo jaja.. para dominar así a tal magnitud las funciones es sobrehumano!!..Excelente!
Muchas gracias, Óscar. Me alegro de que le resulten interesantes estos contenidos.
Muy agradecido, enhorabuena que encontré este canal y un magnífico tutor. Muchas gracias.
Gracias por su comentario. Es muy amable.
no se que decir, es simplemente magistral la manera que tiene usted de con una sola formula rellenar todo un rango y ademas que funcione para todos los supuestos.. es usted humano?
ademas sus gustos musicales son casi tan buenos como sus formulaciones. encantadisimo de haber encontrado su excepcional canal
Muchas gracias, Antonio. Me alegro de que le guste esta forma de diseñar las fórmulas... y de que también le parezca interesante la selección de los ejemplos.
Mas que Excelente, es Fabulosa su explicación Profesor. Mil gracias
Muchas gracias. Muy amable.
Excelente técnica! Gracias profesor por compartir sus conocimientos, todos sus videos son muy buenos. Los he recomendado a mis amigos.
Muchas gracias por su comentario. Espero que también le gusten a sus amigos.
¡Eres lo máximo, maestro! Muchas gracias.
Muchas gracias a usted por su comentario. Me alegro de que le gusten estos contenidos que divulgamos.
excelente tutorial sera de mucha ayuda Se le Agradece
Gracias por su comentario. Me alegro de que le guste.
Que gran explicación👏, Muchas Gracias por sus videos, y por compartir su conocimiento
Muchas gracias a usted por su comentario.
Muy compreto, buenisimo
Muchas gracias, Carlos.
Excelente como todos sus videos, Gracias
Muchas gracias. Muy amable.
Excelente ejemplo y explicación.
Gracias. Me alegro de que le haya gustado.
Excelente, muy agradecido.
Gracias por su comentario.
Muchas gracias por sus excelentes clases. Es un auténtico placer seguir sus explicaciones. He mejorado mucho mis conocimientos
que entendía que eran de tipo medio en cuanto al Excel.
Gracias por su comentario, Guillermo. Me alegro de que estos tutoriales le estén sirviendo para progresar.
muy interesante !!! gracias , me ayuda a enteder las formulas matriciales un poco más
Muchas gracias. Me alegro de que le ayude.
Soy un Fan de excel y Google Sheets, este canal me ha mostrado mejores formas de hacer las cosas, simplificando las o usando formulas o funciones más potentes de las que había venido usando, cómo BuscarV. Gracias por el excelente trabajo y por su gran capacidad de enseñar!
Me alegro de que le gusten estos contenidos y de que les encuentre utilidad. Le agradezco el comentario.
Excelente tutorial, muy pro. Pero más felicitaciones por escoger buenas bandas para la explicación. \m/
Muchas gracias. Me alegro de que le haya gustado el tutorial... y la selección musical. ;-)
No sé que me parece mejor, si el vídeo o sus gustos musicales. Alguien que escucha a los Dires y a los Stukas, tendrá toda mi atención. 😂
Por cierto, en este vídeo ya he visto el verdadero alcance de su técnica " | " para la concatenación. Ya la había visto, alguna vez, en archivos de texto plano. Ahora ya la he hecho mía también, porque me han convencido sus grandes posibilidades. Gracias.
Me alegra que encuentre útil el recurso a la pleca (o a cualquier otro separador, mejor cuanto más artificioso). Y que comparta gustos musicales...
Muy didáctico el ejercicio. Muchas gracias por su explicación 👏👏👏. Saludos desde Ecuador.
Me alegro de que le haya gustado. Estaré encantado de conocer su opinión sobre otros tutoriales de este canal. Muchas gracias.
Buen vídeo, estoy aprendiendo mucho y hasta a dejar el Buscarv, cuesta pero noté el cambio, gracias por los vídeos y compartir sus conocimientos
Muchas gracias, Fabio. Me alegro de que los vídeos de este canal le estén resultando provechosos.
Esto es nivel recontra mega archi avanzado¡¡
lo unico es que al inicio el video no se ve con buena resolucion y eso hace que no se lea las formulas que ingresa
Muchas gracias, Katia.
Lamento que haya tenido dificultad para ver alguna parte del tutorial, aunque la resolución es la misma en todo el vídeo. No obstante, puede descargarse el archivo para ver las fórmulas con detalle.
Mil gracias, eres todo un capo. Ojalá este y muchos más de tus videos queden para la historia xD. Saludos desde Perú.
Muchas gracias, Edwin. Me alegro de que le haya gustado.
GRACIAS CRACK!
Gracias a usted por su comentario.
Hola, desde Colombia un saludo especial, con mis mejores deseos porque tengas un año 2.020 lleno de éxitos.
Uff, excelente información. Quiero decirte que tengo un conocimiento intermedio - avanzado con esta aplicación y he visto muy buenos vídeos de diferentes expositores también muy buenos; definitivamente el alcance de excel es casi infinito, nunca se termina de aprender, me has puesto a estudiar excel en otro contexto, debo repasar este vídeo de una manera un poco más lento, me has dado información, que yo no tengo. Un millón de gracias. Decir lo que debería decir con respecto a esta información sería muy extenso, así que para no serlo te iré comentando poco a poco. Por supuesto tienes una inmensa ¡ Manito Arriba !
Muchas gracias por su comentario. Me alegro de que el tutorial le haya gustado y le haya ofrecido un nuevo enfoque. Efectivamente, las posibilidades de Excel son inacabables y nunca se termina de aprender. Espero que este canal sirva para, entre todos, ir descubriendo técnicas y aplicaciones novedosas.
Muy Bueno, muchas gracias por el contenido!
Me alegro de que le guste.
@@eaae estoy tratando de ordenar unos cursos, pero son varios cursos en el mismo dia y me esta generando problemas, espero puedas ayudarme, gracias
@@RobClaro Precíseme cuál es su problema y trataré de ayudarle.
La ordenación alfabética me costara implementarla porque no lo acabo de entender, pero lo haré. Gracias.
Aquí tiene un tutorial en el que se explica cómo hacer una ordenación mediante fórmulas (th-cam.com/video/ZvBOdnV9oTw/w-d-xo.html ), y aquí cómo hacer una ordenación cíclica (th-cam.com/video/bjkRSbdZ7A0/w-d-xo.html ). Seguro que con estos tutoriales comprende la técnica. En otro caso, no dude en consultarnos las dificultades que pueda encontrar.
😱 y yo creia que sabia Excel Avanzado. No se nada...
Muchas gracias por su comentario, Alexis. Le animo a seguir viendo vídeos en este canal (y en otros, claro), en los que irá descubriendo aplicaciones novedosas que enriquecerán su capacidad.
Eres un capo
Muchas gracias por su comentario.
Excelente
Me alegro de que le haya gustado. Gracias por el comentario.
Hola, espero se encuentren bien, llegué aquí sin querer, y la verdad quedé entusiasmado. Mi conocimiento de excel es básico, pero me gustaría aprender hasta lo más avanzado. Explica muy bien, dado que este curso es para un nivel avanzado. Hay ciertas cosas que aún no entiendo, pero me gustaría hacerlo. Espero y tenga algún curso completo de excel, de ser a futuro, estaré pendiente porque la verdad , se nota que es un monstruo en esta herramienta. Mis respectos.
Saludos...
Pd:Ya estoy suscrito
Me alegro de que encuentre interesantes estos contenidos, Anthony. Aunque el canal no está estructurado como un curso sistemático, le sugiero que vea los vídeos y vaya accediendo a otros tutoriales que se mencionan. También tiene los enlaces en la Descripción. Con que tenga una cierta práctica, aunque sea con un manejo elemental, podrá seguirlos sin mucho problema. Y, por supuesto, no tenga inconveniente en consultar lo que no entienda. Intentaremos ayudarle.
Bienvenido al canal y muchas gracias por su comentario.
@@eaae Gracias por responder, es posible que una lista despegable, al momento de seleccionarla, aplique el comentario de la opción, es decir por ejemplo:
Celda "C4" (TIENE UN COMENTARIO) y al momento de seleccionarla en la lista, aparezca el comentario correspondiente.
BUSQUÉ MUCHO VIDEOS PERO NINGUNO PLANTEA ALGO SIMILAR, O QUIZÁS NO SE PUEDA, IGUAL SE ME OCURRIO. ESPERO SU RESPUESTA.
GRACIAS.
SLDS..!!!
@@abf880 Creo que para ese fin necesitará alguna programación en VBA (macros). Le sugiero que oriente su búsqueda en esa dirección.
En mis clases uso la siguiente codificación:
amarillo : dato de usuario
cualquiera: resultado
pero utilizaré el tuyo así podré, si me permites, hacer referencia a tus vídeos cuando alguien quiera usarlos.
azul: dato ingresado por usuario
amarillo: resultado esperado
rosado: temporales, construcción de fórmulas complejas.
Muchas gracias!
El sombreado de celdas según su naturaleza es un recurso que uso desde hace mucho años y me resulta de gran utilidad. Por eso lo recomiendo. Aparte de estos colores básicos (azul para datos exógenos y amarillo para resultados de fórmulas) uso el rosa para resultados temporales o cálculos intermedios que luego se integrarán en otras fórmulas, el verde para cálculos de Excel mediante herramientas (o sea, que requieren intervención del usuario), marrón para fórmulas que quiero modificar (aunque funcionen), rojo para fórmulas erróneas (o, a veces, para detectores de errores)... Incluso en algún caso distingo con un amarillo distinto las fórmulas que se deben copiar verticalmente de las que se deben copiar horizontalmente. Los colores pueden ser otros a elección de cada usuario, pero recomiendo seguir algún criterio porque facilita mucho el manejo de las hojas.
Por supuesto, le agradezco que mencione y recomiende estos tutoriales.
Muy agradecido por este video, ya que explicas excelente y además este método de una lista validad que dependa de otra es de gran utilidad y no hay muchos tutoriales que lo expliquen.
Tengo dos preguntas en las cuales necesito ayuda:
1)Cómo no mostrar dentro de una lista validad las filas en blanco. Ya que se alimentan de la tabla auxiliar y trae muchas celdas en blanco, que cuando despliego mi lista validada se ven todos esos espacios.
2) Me esta dando un pequeño error en la columna de ID, ya que a algunos numero me le coloca detras un 0.999999, lo que hace que luego no me muestre lo valores.
En mi caso, trabaje con las mismas formulas tuya, copiándolas y pegándolas. Las listas validadas las tengo en la hoja: Cubicación, y en las columnas "Contratistas" y "Detalle"
Adjunto el documento: drive.google.com/open?id=19YZYASGe0coKSFkosy4hXXU8jV7o9ihK
Muchas gracias por su comentario, Marcos.
Para evitar los valores vacíos en la regla de validación podría intentar que el rango de la lista sea sensible al número de valores no vacíos, mediante una anidación de CONTARA e INDIRECTO (o quizás con DESREF).
El problema de los decimales en las funciones de búsqueda y referencia podría resolverse aplicando un REDONDEAR (con cero decimales) a los cálculos.
Intente poner en prática estas sugerencias y no dude en volver a consultarme si encuentra alguna dificultad.
@@eaae El problema de los decimales ya lo pude resolver como me indicaste, pero no he logrado conseguir "No mostrar los valores vacíos de la lista de validación". Si envuelvo la formula de indirecto que uso para la validación de datos en un Contara, me arroja un valor numérico, y con Desref tampoco pude lograrlo.
@@marcosdalmasi Me alegro de que las orientaciones que le di le hayan ayudado a resolver al menos una de las cuestiones. Sobre la segunda, si le parece, para que resulte de utilidad a otros seguidores del canal, la abordaré en un próximo tutorial. Esté atento al canal.
@@eaae perfecto. Gracias
Gracias por sugerirme el visionado de este otro video. SIn duda me aporta mas información para mi validación de Listas. SIn embargo observo la necesidad de crear el paso intermedio de tener en columnas cada grupo de información base de la siguiente lista de validación, cosa que quería evitar y que ahora ya dudo que pueda. Me explico: Viniendo del desarrollo de aplicaciones en Access quería crear unas "listas de validación en cascada" para poder aplicarla en las hojas de movimiento de inventario basadas en las que usted nos explicó. La lista "Marca" se basaría en un diccionario de Marcas. La siguiente columna, lalista Refrencia, se debería basar en una Lista de los artículos de la casilla Marca a su izquierda. Como en origen todos los datos de referencias están en la hoja de saldos, no encuentro la manera de , a cada fila, reconstruir el origen de los datos para la lista de validación. Seguiremos en el empeño..... Gracias de nuevo por tantos y tan pedagógicos tutoriales !
Gracias a usted por su comentario.
En relación con su caso, tenga en cuenta que en algún estado del proceso deberá "informar" a Excel de cómo se asocian los valores de la lista dependiente a los de la lista primaria. Es muy frecuente hacerlo manualmente, creando las listas dependientes una a una. Esa es la forma habitual que se suele mostrar en muchos tutoriales. Lo que se consigue con la técnica que explico en éste es que no sea necesario construir las listas dependientes expresamente, sino que se construyan solas... pero es requiere que los valores correspondientes en ambas estén incluidos en la misma tabla. En este caso, es necesario que cada título se asocie a su autor, del mismo modo que en el suyo cada referencia deberá estar asociada a una marca.
Si no consigue el funcionamiento que busca, podría facilitarme un enlace a su hoja de cálculo y trataría de ayudarle.
Hola, profe Rafael. Primero que todo, le quiero agradecer por sus maravillosos tutoriales, me han incentivado a aprender acerca del uso avanzado de Excel. Tengo una duda relacionada con la validación de datos y listas desplegables dependientes. Tomando como ejemplo este tutorial pude notar que el nombre de los rangos es estático, entonces si a la base de datos le quisiera añadir las canciones de otro autor ¿cómo haría? Es decir, me gustaría que se tuviese la capacidad de añadir canciones y autores adicionales a gusto, de manera manual. Lo digo también porque la hoja «Tabla auxiliar» tiene capacidad para más autores y canciones.
Otra cosa que pude notar es que en el campo de autores -pero también en el de canciones- de la hoja «Formulario» se puede ingresar cualquier texto, aunque no se encuentre en la base de dato, lo que a mi parecer no cumple del todo con una «validación de datos». Creería que lo anterior pasa porque, en el caso de los autores, el rango con nombre «ListaAutores» contiene espacios en blanco.
Espero que me pueda ayudar con mis dudas, profe. De nuevo muchas gracias por su contenido.
Si añade nuevos registros en la tabla principal debajo de los anteriores, quedarán fuera de los rangos referenciados en las fórmulas y fuera de los rangos vinculados a los nombres, así que tendría que rectificar las fórmulas y los nombre de rango. Por eso, desaconsejo hacerlo así. En cambio, ese trabajo adicional es innecesario si los nuevos datos se intercalan entre las filas ya existentes. Inserte nuevas filas en la base de datos y únicamente tendrá que preocuparse de copiar las fórmulas (celdas amarillas) en vertical.
En cuanto a la segunda cuestión, es cierto que manualmente puede introducir cualquier valor, debido a la existencia de valores vacíos en la lista asociada a la regla de validación, aunque puede crear fácilmente una advertencia que detecte la introducción manual de valores inexistentes. Sin embargo, en el desplegable sí aparecerán únicamente los valores asociados a los respectivos campos.
Como ingresaste toda una canción en una celda? Excelente vídeo... Gracias por compartir...
Gracias por su comentario. La introducción de textos largos en una celda no tiene mayor dificultad que la división de líneas. Si tiene alguna duda más concreta, precísela y trataré de ayudarle. En este canal hay algunos vídeos sobre el tratamiento de textos que podrían serle útiles.
Gracias, Acerté la pantalla así se ve lo que usted hace, así no sabemos que hace
Es cierto que en algunos vídeos las fórmulas se ven demasiado pequeñas. Discúlpeme. En próximos vídeos intentaré mejorar la calidad de la imagen para que se puedan ver con más detalle.
Excelente explicación, acabo de suscribirme.
Tengo una duda, ¿se puede hacer el mismo proceso de alguna otra forma con la información en una sola hoja? Gracias.
Por supuesto, todos los cálculos se podrían incorporar a la misma hoja. En el tutorial hago los cálculos auxiliares en hojas distintas para separar visualmente las etapas del procedimiento. Pero nada impide hacerlo todo en la misma hoja.
Muy buen día profesor, también se puede formular análogamente para dos listas subsidiarias encadenadas ? Vb. Gr. Estado/Ciudad/Municipio ? En ese caso cómo se haría la matriz auxiliar, dos matrices o listas separadas ? Saludos y gracias
La formulación de una tercera lista dependiente sería algo más complicada, pero se podría conseguir con esta misma técnica usando como criterio la concatenación de los dos primeros campos.
De verdad, muchas gracias por el trabajo que realiza. Me gustaría hacerle una pregunta, sobre las listas validadas, quisiera saber si hay alguna manera de validar una lista con respecto a un codigo o un ID, ejemplo. Tengo una tabla con 200 artículos, las columnas están nombradas de la siguiente manera: "ID, NOMBRE, CATEGORIA, SERIAL". Resulta que en otra hoja lo que pretendo es que al escribir el ID:12345 sobre una celda, otra celda (claramente validada) me muestre una lista con los seriales existentes de aquel ID, sin que estos se repitan. En resumen, tengo 100 televisores, 50 con seriales diferentes y otros 50 con un solo serial, lo ideal es que la lista validada me muestre solo los 51 seriales que son unicos.
La formulación de esas listas validadas condicionadas y depuradas de valores repetidos dependerá de la estructura (y de la naturaleza) de los datos. Por favor, envíeme un ejemplo de su proyecto a Consultas.EAAE para que pueda valorarla.
@@eaae Hola buenas tardes, mi duda es similar, tengo una tabla con insumos de fabrica, pero mi caso son con materias primas, cada materia prima tiene un lote, y me gustaría que al escribir el codigo de la materia prima, la celda me mostrara solo los lotes existentes para ese codigo y no los lotes de todas las referencias existentes en mi tabla.
@@juanfeliperodriguezvalenci5353 La formulación requerida dependerá de la estructura de sus datos. Si tiene interés, envíe su proyecto a Consultas.EAAE@gmail.com para que podamos valorarla.
cual es el codigo que coloca en 5:15? ese me sirve para eliminar el contenido cuando de una lista despleglable cuando no hay referencia.
El código que se inserta en el formato personalizado es "0;-0;;@". En este otra tutorial se explica su estructura: th-cam.com/video/gLYfxbpKyok/w-d-xo.html.
Profesor, rehaciendo este ejercicio me ha surgido una duda. En el minuto 18:22 del vídeo, cuando se realizan las formulas de la tabla auxiliar, se validan como formula matricial con CTRL+Shift+Enter y aparecen con {}. Mi duda surgió porque al hacer pruebas las validé como formula normal y me funcionan igual. Como aún no domino las FM tengo la duda de saber cuál es el motivo. ¿Es porque en esta tabla auxiliar trabajamos con formulas que solo van a dar un resultado en la celda?. En estos casos, ¿el no validarlas como FM, con CTRL+Shift+Enter, no perjudica al resultado?
Saludos
Las fórmulas matriciales pueden dar un único resultado o varios. En otras palabras, el hecho de que sólo dé un resultado no significa que la función no tenga que ser matricial. Lo que creo que le está ocurriendo es que, en el caso concreto que menciona, al validar la función como normal obtiene el mismo resultado que con una validación matricial porque el resultado relevante es el primero de los que habría tenido en cuenta la fórmula matricial. Me temo que fallaría cuando el resultado relevante sea otro. Le sugiero que copie la fórmula (validada como normal) para comprobarlo. Y le agradecería que me tenga al tanto del resultado que obtenga.
@@eaae Esa creo que fue mi prueba, porque algo se me debe estar escapando.
Por ejemplo, en la hoja Tabla Auxiliar en la celda C4 con la FM ={SI.ERROR(INDICE(Título;COINCIDIR(C$2&" | "&$B4;ID;0));"")
} si evalúo la celda con F9 veo un solo valor, ="Brothers in Arms". Y eso es lo mismo que me ofrece la formula normal. Copio hacia abajo y a la derecha y no veo diferencia.
Realmente esperaba que al validar como FM, al evaluar con F9, se viese un arreglo entre corchetes, como creo ocurre en las FM. Pero solo veo, en C4 ="Brothers in Arms". Y en las otras celdas celda el título que corresponde. Es decir, mismo comportamiento si valido como FM que si valido como FN. Y al copiar las celdas, como FN, no veo problemas o errores.
Algo se me puede estar escapando, pero no veo el qué. Gracias por su interés
@@signifer6742 Creo que ya he descubierto lo que ocurre... Usted tiene razón y, en este caso, es verdad que no se necesita que la fórmula sea matricial y funciona igualmente como fórmula convencional.
En la fórmula de la que hablamos hay anidada una función COINCIDIR que es la que genera la confusión: en ella estamos buscando una concatenación de dos trozos de texto (el número de autor y el número de canción, con la correspondiente pleca separadora), y buscamos la coincidencia sobre la columna que hemos llamado ID (por supuesto, búsqueda exacta). Esa columna ID no es más que la misma concatenación, pero sobre los datos de origen. Y aquí está el núcleo de nuestra controversia. Habitualmente se hace la búsqueda de la ‘concatenación de dos celdas’ en una ‘concatenación de dos columnas’, y esa concatenación de columnas convierte a la fórmula en matricial. Pero en este caso concreto había hecho la concatenación de columnas previamente en una columna auxiliar, así que nuestra fórmula no está buscando en una ‘concatenación de dos columnas’ sino en una columna normal y corriente (aunque es, ciertamente, una concatenación), y por eso no se precisa que la fórmula sea matricial.
En resumen: si buscamos con COINCIDIR una concatenación sobre una concatenación, la fórmula debe ser matricial. Si la búsqueda se hace sobre una columna normal (aunque en esa columna lo que haya sea una concatenación), la fórmula puede ser normal (aunque validarla como matricial no perjudica los resultados).
Le felicito por su rigor en el análisis de los algoritmos y por su agudeza para analizar las fórmulas. Y le agradezco que comparta sus hallazgos: estoy seguro de que son útiles para todos.
@@eaae Muchas gracias a usted por molestarse revisar los comentarios. No es lo más habitual en los canales de youtube. Su explicación me parece perfecta. Era mi principal hipótesis pero, como no tengo mucha práctica con FM, no estaba seguro de que ese pudiese ser el motivo. Creo que he dado paso más hacia el dominio de las FM . La ciencia y el conocimiento avanzan mucho más cuando surgen las casualidades 😂
@@signifer6742 Sí. Pero las casualidades, por sí solas, sirven de poco. El éxito ocurre si te pillan atento...
Profe ¿que se estudia para aprender a utilizar excel en la forma que usted lo hace?
El mejor consejo que puedo darle es que practique mucho. Que use mucho Excel, para fines laborales o profesionales, para fines personales o domésticos, e incluso como diversión, como reto... Resuelva problemas, aunque no tengan importancia. Haga hojas de cálculo para presentar información, para hacer comprobaciones, para guardar y consultar datos... aunque no tengan importancia o utilidad para usted: lo que será útil es el aprendizaje que irá adquiriendo por el camino. Por supuesto, para que sus hojas de cálculo tengan una buena razón, le convendrá tener los conocimientos propios de su especialidad laboral o profesional, pero el dominio de Excel se consigue practicando.
@@eaae Ame su respuesta, muchisimas gracias! Estoy empezando a ver sus videos, son excelentes!!
Estas reglas aplican si se crece la lista en canciones autores y albunes o en otros casos de oto tipo con más datos?
La ventaja de esta técnica es que es automática: si la lista crece, los datos quedan incorporados a las reglas de validación (y a las listas desplegables) sin más intervención del usuario. Eso sí, recomiendo que las filas con los nuevos datos no se añadan por debajo de las existentes (lo que obligaría a modificar los rangos en las fórmulas), sino por el medio (y que se copien las fórmulas verticalmente, por supuesto).
@@eaae Gracias, por último que sucede si convertimos la base de datos a Tabla, tiene alguna ventaja o por el contrario no es conveniente.
Veo que cuando cambia de autor, permanece el titulo de la canción del anterior autor, hasta que se elige una nueva, hay manera de solucionarlo?
Sí. Borrándola... ;-)
Puede crear una sencilla macro que limpie todos los campos.
Buenos Días Profesor, muy interesante la explicación pero quería hacer 2 consultas:
1) La única razón de utilizar el conector " | " fue evitar las coincidencias con otros valores buscados? Para concatenar se pudo haber utilizado "&".
2) Hay algún otro ejemplo que ayude a entender mejor la utilidad de invertir la suma de valores que dan como resultado el total de canciones por grupo, es decir me fórmula 1/suma( .....)?
Muchas gracias y quedo a la espera de sus comentarios.
Muchas gracias por su comentario.
En cuanto a su consulta sobre el separador que uso en la concatenación, tenga en cuenta que la operación de concatenación se hace en las fórmulas de Excel con el símbolo ampersand (&), o también con la función CONCAT (antes, CONCATENAR). Pero esta unión junta las cadenas sin ninguna separación. Para que en la cadena resultante haya alguna separación entre los trozos originales suelo incluir algún tipo de separador, y dado que en este caso la cadena ensamblada no se va a visualizar, suelo optar por algún símbolo o alguna cadena poco convencional (por ejemplo, la pleca entre espacios, pero también podría ser, si me permite la exageración, algo tan extravagante como “ : x-x-o-x-x : ”). De lo que se trata es de que la cadena resultante sólo puede provenir de una única secuencia de valores.
En el caso de una lista de canciones y grupos, quizás no fuese fácil que se produjese una inconsistencia, pero en otros casos el riesgo es mucho más acusado. Piense, por ejemplo, que tuviese que juntar ciertos códigos numéricos de dos columnas. Un “182” podría ser la unión de un “1” y un “82” o la de un “18” y un “2”. Por eso interesa interponer algún separador entre los trozos. Ahora imagine que las cadenas originales también pueden tener guiones. La cosa se complica, porque un “18-26” podría venir de un “18-2” y un “6”, o de un “1” y un “8-26”, y si usa como separador un guion añadirá un caso más a la lista de conflictos: el “18” y el “26”.
Para evitar riesgos, trato de utilizar un separador lo suficientemente raro como para que sea imposible encontrarlo en los datos originales y provocar así alguna confusión. No olvide que en este caso el objetivo no es visualizar la cadena resultante, sino trabajar con ella en funciones de búsqueda y referencia, así que usando separadores “raros” no vamos a visualizar nada que resulte extraño…
En cuanto a la cuestión de la suma de inversos, es una forma muy interesante de contar cuantos valores distintos hay en una lista de valores repetidos. En el ejemplo del vídeo, es cierto que una canción podría estar asociada a varios grupos, pero lo más frecuente es lo contrario: que a un grupo se le asocien varias canciones (salvo a los “one-hit-wonder”, claro). Si quiere profundizar un poco más es esta técnica de formulación, le aconsejo que vea este vídeo, donde se aborda el problema de depurar listas de valores repetidos: th-cam.com/video/DqgqRspDwEA/w-d-xo.html.
Échele un vistazo y deme su opinión, o consúlteme las dudas que persistan.
Muchas gracias Profesor, pondré en práctica la idea del video sobre la inversión de la suma para ordenar valores sin repetición para que todo quede más claro.
Ojalá en otros videos pueda reforzar la idea mediante más ejemplos.
Que tenga un excelente día!!
@@luiscruceszelaya2542 En la descripción de los vídeos tiene un enlace para descargar el archivo y practicar por su cuenta. Espero que le ayude en sus progresos.
Muchas gracias Profesor y estaré al pendiente de nuevas publicaciones, ojalá que posteriormente pueda tratar el tema de Tablas Dinámicas .
Que tenga un excelente día!!
estimado rafael:
muy buen video, al replicarlo a modo de práctica, se me presentó un problema:
todo funciona bien, solo que en la celda donde aparece la letra de la canción (después de haber selecionado el autor y título), esta aparece en una sola línea o reenglón; no como está en la base de datos (en tu archivo aparece bien).
es algo así como esto (para sultans of swing de dire straits): You get a shiver in the darkIt's raining in the park but meantimeSouth of the river you stop and you hold everythingA band is blowing Dixie double four timeYou feel..........
supongo que es algo que hay que configurar en el formato de celda, pero no doy con la solución.
gracias por tu atención.
Creo que el problema es precisamente el que indica. Pruebe a marcar la casilla "Ajustar texto". Si no le funciona o no encuentra cómo hacerlo, dígamelo y buscamos la solución.
saludos nuevamente rafael:
efectivamente, solo se trataba de JUSTIFICAR el texto de la celda, asunto resuelto.
ahora tengo otra duda, el alto de fila en el excel que tengo instalado (no se si sea en todos) solo acepta hasta 409 (ó 546 pixeles), sin embargo, hay canciones que tienen la letra un poco extensa y superan este alto de celda; por lo tanto la letra no aparece completa.
existe alguna solución a este problema?
gracias por tu atención rafael.
@@hausserzavaletaalcalde6416 Hasta donde sé, se trata de un límite propio de Excel: support.office.com/es-es/article/especificaciones-y-l%C3%ADmites-de-excel-1672b34d-7043-467e-8e27-269d656771c3.
Si necesita que se asocien textos más largos, que no quepan en una celda, se podría reformular la base de datos habilitando varias celdas para la letra. En las funciones de búsqueda sólo habría que añadir un criterio más a la concatenación. No he analizado el problema en profundidad, así que no es más que una solución tentativa, que le invito a poner a prueba.
@@eaae entiendo, si es el límite de Excel pues ni modo, no se podrá hacer en una celda.
La solución q propones sería lo lógico, también se podría combinar celdas, aunque el reto hubiera sido meterla en una celda, pero cuando el programa pone sus límites no queda de otra.
Cómo siempre, quedó agradecido por tu ayuda Rafael, y tu audiencia como yo esperamos te animes hacer tutoriales de VBA, sé q serán muy interesantes, dado el nivel q muestras en la interfaz de usuario, ya no tan sencillos como los que existen en youtube.
Saludos desde Perú.
@@hausserzavaletaalcalde6416 Con VBA se podría intentar alguna solución. Pero la combinación de celdas es un recurso que no aconsejo utilizar. Suele ocasionar problemas de referenciación que, en mi opinión, no compensan la leve mejora visual que proporcionan.
Has empezado a publicar hace poco?. 1._ No conocía tu canal y consulto bastante temas de Excel. 2._ Para la calidad de tu exposición tienes muy pocos seguidores. Me comprometo a promocionarte. Saludos y Chao
El canal es aún muy joven. Aún no ha cumplido el primer año... así que le agradezco que lo promocione.
@@eaae Estoy seguro tendrá muchísimos seguidores.
@@humbertochinchillamora3513 Muchas gracias por sus ánimos y por su ayuda.
Buenas tardes, le agradecería que me explicase como saber si una formula es matricial, disculpe mi ignorancia.
Le sugiero que vea este vídeo donde explico qué son, para qué sirven, cómo se identifican, etc.: th-cam.com/video/-J8fsX9f93E/w-d-xo.html. Consúlteme luego las dudas que tenga y estaré encantado de darle respuesta.
Hola Profesor
Ya sé que es un tanto atrevido, sugerir lo siguiente, pues estoy seguro que lo sabes de manera sobrada, pero puede servirle a alguien que lea, como yo, los comentarios:
Con respecto a los rangos de las listas de validación, para no tener espacios innecesarios y garantizar que a medida que aumente la información, aumente lo que aumente o que disminuya, por alguna razón, esta siempre sea la justa, debo entonces hacer una lista dinámica para lo cual tengo una hoja, en mi caso “Lista_Validacion”, con varias listas, por ejemplo en la columna “D”, tengo la lista “Productos” y para definirla uso la expresión:
=INDIRECTO("Lista_Validacion!$D$2:D"&CONTARA(Lista_Validacion!$D:$D)).
Luego en Validación de datos, permitir datos: “Lista”, en el origen simplemente escribo =Productos
Espero haber sido claro, Tómalo, como un simple comentario: Saludos
Ha explicado muy bien la técnica de referenciación de listas de dimensión variable, a la que tengo previsto dedicar un tutorial desde que preparé el que ahora comenta. Es una forma estupenda para tratar conjuntos de datos cambiantes en tamaño y que es, además, compatible con la formulación de listas dependientes, así que su comentario es muy interesante para todos los usuarios. Muchas gracias por compartir esta forma de trabajar.
¿Y como se le hace si son 3 criterios en lugar de 2? Sobre todo en la parte de la tabla auxiliar
La técnica explicada funciona igual con un número distinto de criterios. Únicamente debe añadir la concatenación que sea precisa. Si no lo consigue y me concreta un poco más en qué fórmula tiene dificultad, intentaré ayudarle.
Pásanos el archivo con las tablas originales...
Tiene el enlace en la descripción del vídeo.
Gracias.
Muchas gracias por este contenido.
Me da un error, en vez de devolverme números enteros, me devuelve decimales, para cuando corro la formula para darle número a los autores, pero no es para todos.
Alguna solución quedaría muy agradecido.
¿Qué fórmula usa?
@@eaae gracias por la pronta respuesta, me refiero a la formula que usted usa:
"=SUMA(SI(Autor
@@jonathanespinoza6398 Pruebe a envolver esa fórmula en un REDONDEAR fijando en cero el número de decimales y dígame, por favor, si soluciona su problema.
@@eaae muchas gracias amigos
@@jonathanespinoza6398 De nada.
Hola, encuentro muy interesantes sus videos. Hay una duda que tengo y la qual no encuentro ningún tipo de explicación.
En los desplegables, hay alguna manera de hacer un desplegable que cuelguen 4 desplegables a raíz de uno?
Te cuento....
Yo tengo un desplegable que me indica: Monofásico y Trifásico.
A raíz de este desplegable cuelgan :
Tensión monofásica con 3 valores.
Tensión Trifásica con 8 valores.
En una celda necesito que muestre los valores dependiendo de lo que hemos elegido anteriormente, o un desplegable u otro.
Y en otra celda que me aparezca la posiblidad de dos desplegables más dependiendo de la primera elección.
En esta otra celda necesitaría escoger entre dos desplegables de potencia ya sea Trifásica o Monofásica.
Del desplegable de Potencia trifásica cuelgan 8 valores y de la Potencia monofásica cuelgan 10 valores.
No encuentro la manera de combinarlo.
Por favor, desarrolle aquí todo el esquema de opciones para comprender correctamente su necesidad. Suba un archivo a algún depósito y cuelgue aquí el enlace e intentaré configurarlo en su hoja de cálculo.
Hola, aqui te comparto un vinculo, a ver si es posible que te lo descargues.
mega.nz/#!mWwWwCSS!LMYfsDpi5Ba4EXnLX_w5ygXR6X9I0KapPOW7e47pafE@@eaae
@@eaae Que pudiste ver el archivo? Se puede hacer lo que te comento?
Resuelto.
¿Es usuario de Facebook?
@@eaae Si, con el mismo nombre que sale aquí....
Profe tengo un problema con este metodo. Lo empece a aplicar en una planilla donde se registran las distintas visitas a distintos pacientes en distintos lugares. para poder hacerlo en una hoja tengo la base de datos, donde tengo replicadas las instituciones con los datos de localidad y direccion, en esa misma hoja pero en otra tabla, registro a los pacientes tantas veces como distintos lugares se lo visite, ya sea el domicilio personal, el hospital, y en algunos casos el domicilio laboral. y al lado tengo una planilla similar donde cada paciente es registrado una vez, con los diferentes lugares, para poder dar curso a la lista desplegable dependiente. Todo esto aplicando los conocimientos que tuve con tus videos, de utilizar formulas para todo, en lugar de utilizar las opciones tradicionales de excel
En fin, cada vez que agrego un dato, ya sea nuevo de un paciente existente o un paciente completamente nuevo. No me se agrega automaticamente a las listas desplegables. Tengo la necesidad de
volver al menu formula y modificar o agregar un nuevo nombre, para luego poder utilizarlo. Pasado a tu planilla de ejemplo. Si yo agregase la banda Baron Rojo a la lista y algunas canciones, tengo la necesidad de administrar nombres nuevamente. siendo incluso que, al igual que vos, tengo espacios sin utilizar, para agregar informacion futura.
Ojala me puedas dar una idea de como seguir para poder implementarlo perfectamente
Gracias profe
Para poder resolver el problema tendría que ver las fórmulas y la estructura de su hoja de cálculo. No obstante, le diré qué intentaría comprobar en primer lugar. Entiendo que su problema es que la tabla final (de donde luego se tomarán los valores para las listas desplegables) no se actualiza con los nuevos datos que se incorporan a las otras tablas. Creo que puede tratarse de un problema de referenciación, es decir, los nuevos valores que se van añadiendo no quedan integrados en los rangos de las fórmulas. Le sugiero que, en vez de añadir los valores al final de sus respectivas tablas lo haga intercalando filas entre valores que sí operen correctamente. Téngame al tanto de los resultados que consigue y, si no le soluciona el problema, seguimos buscando alternativas.
@@eaae
Bien, Revise todas las formulas, y de hecho por las dudas, la realize todas de vuelta, siguiendo y pausando este tutorial, por las dudas
Y puedo decir que el incoveniente, viene en el momento que le doy nombre a las filas (por una cuestion de espacio y comodidad, los nombres de los autores - en mi caso pacientes - al ser un numero muy elevado, un promedio de entre 600 y 700, me convenia desarrollarlos de forma vertical, y no horizontal, y los datos de los lugares de visita, al ser 3 o 4 como maximo, me conviene incorporarlos al costado, por lo que a la hora de nombrarlos para utilizarlos en las desplegables, es inverso al que utiliza usted), ya que por si solos no se agregan nuevos parametros a la lista de nombres. Como le decia en el primer comentario. si en su ejemplo, agregase un nuevo autor, y varias canciones, este se encontraria en la lista desplegable de nombres, pero no asi en la lista dependiente, donde aparecierian listadas las canciones. incluso incorporando dentro de la seleccion espacios vacios.
@@federicocouceiro8751 Para poder dar una solución es necesario poder ver una versión de su hoja de cálculo (por supuesto, con datos inventados). Si puede compartir un enlace, no tengo inconveniente en echarle un vistazo.
@@eaae drive.google.com/drive/folders/1qa_UOPotqitGI68YufVaAtThrK4dRe5B?usp=sharing
Ahi subi el archivo para que pueda descargarlo y poder verlo, muchas gracias profe
@@federicocouceiro8751 He revisado su archivo y he observado una posible inconsistencia en la asignación de nombres: PACIENTE y LUGAR toman datos hasta la fila 8 (rangos =BD!$Q$2:$Q$8 y =BD!$S$2:$S$8, respectivamente), así que hay algunos datos en las filas 9 y 10 de las columnas Q y S de su hoja BD que quedarían fuera del rango. Es muy posible que esta inconsistencia haya sido ocasionada inadvertidamente por el uso de filtros en la hoja. Le aconsejo que evite usarlos cuando cree reglas de validación con este método y, al menos, para resolver este problema. Pruebe a redefinir los rangos asociados a estos nombres y revise (sin aplicar filtros) si los demás nombres están bien asignados. Una vez hecha esta comprobación, vea si la hoja ya funciona como debe. En caso contrario, si sus cálculos siguen fallando vemos más posibles causas.
Disculpa, si es adminstracion porque no utilizar ejemplos que tengar que ver con la administracion.
cuando alguien aprende algo no tiene que ser exclusivo si nos dan algo y no nos gusta, hacemos lo propio saludos
Los tutoriales tienen como finalidad explicar una técnica de análisis de datos. El tratamiento de la información es el mismo con listas sobre grupos y canciones, o sobre departamentos funcionales y empleados, o sobre marcas de coches y modelos, o sobre familias de productos y especificaciones, o sobre provincias y municipios, o sobre cualquier cuestión en que haya una relación de dependencia entre las listas. Creo que el ejemplo propuesto tiene que ver con la administración (quizás de una discoteca...), pero le sugiero que compruebe que la hoja de calculo que facilito sirve para cualquier otro propósito: cambie los datos en las dos primeras columnas de la base de datos y verá que siguen funcionando exactamente igual.
@@eaae Extraordinaria Respuesta.
En muchos escenarios de la vida tenemos que ser DINÁMICOS.