lunes, 29 de agosto de 2011

Cómo asociar una imagen al valor de una celda en Excel 2007.

Durante mi periodo de vacaciones, curiosamente, recibí dos correos que planteaban una misma situación. Se trataba de establecer la manera de asociar una imagen al valor de una celda:

...el concepto es esto: es un carnet, en la cual al cambiar un codigo cambia la foto y todo sus datos.
Lo que quiero saber cómo se hace para que cambie la imagen...


En esta primera explicación obviaremos macros y códigos VBA, centrándonos en las herramientas estándar de las que disponemos en Excel 2007.
Supondremos que tenemos una primera hoja 'BaseDatos' con un banco de imágenes:

Cómo asociar una imagen al valor de una celda en Excel 2007.


Vemos que nuestra base de datos o banco de imágenes es muy sencillo.
Nuestro trabajo comienza por asignar nombres a ciertos rangos (ver como); anticipando que nuestro criterio de búsqueda será el primer campo de nuestro origen de datos 'Código', lógicamente uno de los nombres creados será:
Código =BaseDatos!$A$2:$A$5
Para generar el segundo nombre emplearemos la función DESREF con su funcionalidad de búsqueda (y no de creación de rango), anidándo dentro de ella la funcíon COINCIDIR.
El segundo nombre será:
foto =DESREF(BaseDatos!$D$2;COINCIDIR(Ficha!$A$2;Código;0)-1;0)
función que mostrará de la columna D (o campo 'Foto') la imagen que coincida con el valor buscado en la celda A2.

Estos nombres los usaremos en la segunda de nuestras hojas ('Ficha'), donde asociaremos al valor de una celda la foto o imagen que le corresponda:

Cómo asociar una imagen al valor de una celda en Excel 2007.


En la celda A2 hemos generado una Validación de datos tipo lista, empleando el rango 'Código':

Cómo asociar una imagen al valor de una celda en Excel 2007.


La clave de todo este asunto viene ahora, ya que para asociar al valor desplegado en la celda A2 la imagen que le corresponda del origen de datos (o banco de imágenes) deberemos insertar un objeto.
Desde Ficha Insertar > Grupo opciones Texto > Objeto
seleccionaremos Imagen de mapa de bits:

Cómo asociar una imagen al valor de una celda en Excel 2007.


Observemos cómo aparece el objeto añadido

Cómo asociar una imagen al valor de una celda en Excel 2007.


aunque lo más importante será trabajar en la barra de fórmulas con el objeto seleccionado...
Después de darle un tamaño adecuado al objeto (lo he ajustado al tamaño de la celda C2):

Cómo asociar una imagen al valor de una celda en Excel 2007.


Con el objeto seleccionado, reemplazaremos en la barra de fórmulas donde pone =INCRUSTAR("Paint.Picture";"") por =foto, inmediatamente veremos cómo dentro de dicho objeto aparece la imagen correspondiente al valor de la celda A2 buscado.

Cómo asociar una imagen al valor de una celda en Excel 2007.



También es posible, repitiendo todos los pasos exactamente igual, insertar un objeto desde la Ficha Programador > Controles > Insertar > Controles ActiveX > Imagen:

Cómo asociar una imagen al valor de una celda en Excel 2007.


y seguir el proceso de manera identica a lo explicado anteriormente.

62 comentarios:

  1. Hola gracias por publicar esta info, tengo problemas en la parte donde hay que validar los datos en el origen hay que poner =codigo
    pero no me lo esta tomando, si bien cree el rango en la hoja "base de datos" como codigo, no me lo esta tomando.
    aceptando esto me trae el nombre de la imagen que corresponde al codigo pero no veo la imagen en si espero que me puedas ayudar.
    saludos,

    ResponderEliminar
  2. el error que me tira al intentar validar la lista con el origen =codigo es el siguiente
    "La fuente de la lista debe ser una lista delimitada o bien una referencia a una fila o columna individual"
    desde ya gracias y espero que me puedan ayudar

    ResponderEliminar
  3. Hola,
    asegúrate de crear el nombre Código:
    Código =BaseDatos!$A$2:$A$5
    previamente, se trata de Asignar un nombre a un rango de celdas. Una vez generado el nombre lo utilizaremos para generar una lista desplegable en una celda empleando la Validación de datos.
    Debes verificar que has escrito igual el rango...

    Los pasos de este ejemplo son muy sencillos y no tienen ningún truco, por lo que no deberían fallar...
    Slds

    ResponderEliminar
    Respuestas
    1. Hola, tengo el mismo problema con la parte de crear la lista desplegable, ya que me dice que no puede encontrar el rango especificado, y los tengo a ambos nombres creados, con su rango correspondiente cada uno, y de igual manera que aqui, y aun asi me tira ese error.
      Que hago?
      Creo que solo me falta eso para me funcione

      Tengo office 2007

      Eliminar
    2. Hola, si has verificado que los Nombres definidos (todos) ya los tienes creados como se indica en el post, sólo por descartar, comprueba el ámbito de éstos (que sean Libro y no una Hoja concreta).

      Como decía en un comentario anterior los pasos son tal cual explico.

      De todas formas si lo único que te falla es la Validación de datos (celda con la lista desplegable, igual puedes verificar que la imágen cambia, introduciendo los datos manualmente...

      Verifica lo comentado, y si te sigue fallando puedes enviarme el fichero para que revise lo que te falta a
      excelforo@gmail.com

      Slds cordiales

      Eliminar
  4. Wow! very nice information. i was searching article like this.

    Thanks

    http://mlmdevelopers.com/products/mlm-software/mlm-software-beta/features.html

    ResponderEliminar
  5. Hago todo y maravilla aparece a foto, pero cuando le cambio el número en la primera columna, tengo que volver a escribir =Foto para que cambie, además sólo me funciona en la fila 2. No se que hacer, llevo un día tratando de hacer esto y para los macros si no tengo idea. ¿Alguna ayuda?

    ResponderEliminar
  6. Hola Emerson,
    es normal que funciene sólo a partir de la fila 2, ya que hemos definido 'foto' como:
    =DESREF(BaseDatos!$D$2;COINCIDIR(Ficha!$A$2;Código;0)-1;0)
    es decir, coge las imágenes a partir de la segunda fila.
    Realmente todo el trabajo parte de una correcta definición y asignación de nombres definidos; yo he replicado tu caso, y no me devuelve ningún error, esto es, funciona correctamente y no tengo ni que cambiar fórmulas ni reescribir =Foto.
    Asegúrate que tienes bien definidos los nombres, y la celda validación.. si ya reemplazaste
    =INCRUSTAR("Paint.Picture";"") por =foto
    olvídate de nada más.
    Slds

    ResponderEliminar
  7. YAAAAAAAA es que estaba tratando de poner otro desplegable en A2 e incrustar al frente otra de las fotos, sólo cree otro rango foto2 pero dependiendo ya de Ficha!A3 y cuando inserto el objeto al frente de la celda de Ficha!A3 ya le doy es Foto2 y así lo haré sucesivamente porque tengo que poner es algo así como 20 fotos. Gracias
    Todo el día de ayer hasta que alguien dió una solución sencilla sin tener que usar alta ingeniería de programación. Eso es lo bello de la inteligencia "La sencillez"

    ResponderEliminar
  8. Estimado, me ha sido de gran ayuda la información, el único problema es que no he podido ingresar
    =foto
    ya que me dice

    "La referencia no es válida"
    He puesto com Ámbito BaseDatos, Ficha y libro. Aun así, nada de nada.
    TE agradezco de antemano la ayuda.
    Saludos!


    PD: Uso Office 2010, será ese el problema??

    ResponderEliminar
    Respuestas
    1. Hola Danilo,
      ummm.. pues parece que algo falla en 2010, por que a mi me pasa lo mismo. Investigaré sobre el tema.
      Un saludo

      Eliminar
  9. y para el 2010 como hago

    ResponderEliminar
    Respuestas
    1. Hola...
      Revisa esta entrada donde se explica los pasos para construir una macro que inserta imágenes en una celda:
      http://excelforo.blogspot.com.es/2012/05/vba-una-macro-en-excel-para-insertar.html
      Slds

      Eliminar
  10. DISCULPA LLEVO 2 DIAS Y NO ME SALE NADA DE FOTO, USO OFFICE 2007,HE CREADO 2 HOJAS UNA BASE DATOS Y OTRA FICHA, REEMPLACÉ FOTO POR INCRUSTAR SÓLO QUEDA EL MARCO EN BLANCO DE LA FOTO, TE AGRADECERE TU AYUDA.
    PEDRO

    ResponderEliminar
    Respuestas
    1. Hola Pedro,
      tienes que asegurarte de seguir los pasos explicados. Es fundamental definir priviamente los nombres y asignarlos a los rangos correctos.
      Comprueba que has dado el nombre 'foto' como se indica en el post.
      Slds
      P.D.: Por favor, evita escribir en mayúsculas... significa que estás gritando ;-)

      Eliminar
  11. Hola, he utilizado esta herramienta para mis archivos de Excel sobre ligas de fútbol en mi blog www.economiaemergente.com
    El Problema es que cuando actualicé a Excel 2010, ya no me funciona. Al cambiar en la barra de fórmulas el nombre del rango me aparece referencia no valida. Me puedes ayudar a encontrar la solución? quedo muy atento. Gracias!!!! Excelente Blog ...

    ResponderEliminar
  12. tengo un problema al imprimir la imagen no se imprime correctamente podrias ayudarme gracias

    ResponderEliminar
    Respuestas
    1. Hola Hugo,
      necesitaría saber a qué te refieres con 'correctamente', ¿quizá queda fuera del área de impresión?...prueba ajustando el escalado o los saltos de página, para que toda la imagen quede en la página impresa.
      Slds cordiales

      Eliminar
  13. Muchas gracias por compartir este truco.
    Me resolvió un problema que creía imposible de resolver y me agilizó mucho el trabajo.

    ResponderEliminar
    Respuestas
    1. Me alegro te haya sido útil...
      ese es el espíritu de mi blog.
      Cordiales saludos

      Eliminar
  14. por dios, por fin. :)
    llevo un monton de tiempo buscando una buena explicacion.
    gracias, realmente no sabes como me has ayudado con este esplicacion.


    ResponderEliminar
  15. Hola Ismael,
    Gracias por el post. Está genial.
    ¿Sabes qué fórmula o proceso aplicar para que los resultados sean visibles en varias filas a la vez?. Estoy creando una tarifa con 1500 referencias a las que tengo que asignar 100 dibujos (muchas referencias utilizan un mismo dibujo). Muchas gracias.

    ResponderEliminar
    Respuestas
    1. Hola, y perdona no haberte contestado antes...
      tu comentario se me escapó, lo siento.

      Respecto de tu cuestión, no sé si lo entiendo bien.
      Entiendo que quieres visualizar para cada una de esas 1500 referencias uno de esos 100 dibujos (repetidos), pero no quieres realizar la incrustación de manera manual... si es así, podrías leer esta entrada donde se explica una macro:
      http://excelforo.blogspot.com.es/2012/05/vba-una-macro-en-excel-para-insertar.html

      Espero te sirva.
      Slds

      Eliminar
  16. Muchas gracias por esta explicación. Una vez comprendidos los pasos, realmente es muy sencillo conseguirlo.
    Sólo una duda.
    Al escribir la fórmula del rango "foto", al prinicipio pensé que faltaba un ´;´ después del paréntesis de cierre de COINCIDIR (Donde he puesto el asterisco), <<<..COINCIDIR(Ficha!$A$2;Código;0)*-1;0)>>>>, pero si lo ponía, la foto no se refrescaba con el cambio de referencia a "Código". Una vez que quito el ';', todo va de maravilla.

    ¿Se debe a un error? porque ambas sintaxis las tolera como correctas.

    Un saludo y gracias por el post

    ResponderEliminar
    Respuestas
    1. Hola, muchas gracias a tí...

      no es un tema de COINCIDIR, si no de la función DESREF, ésta admite hasta 5 argumento:
      =DESREF(ancal;fila;columna;alto;ancho)
      la función COINCIDIR se emplea, al anidarla, como argumento 'fila'.
      En el caso que planteas, si reemplazas el asterisco por un ';' estás llevando el -1 como argumento 'columna' y el '0' como argumento 'alto'.
      Es decir, la función no te dará error, ya que estás completando argumentos válidos, pero el sistema fallará por que estarías refiriéndote a cualquier otra cosa.

      Espero haberme explicado con claridad ;-)

      Sdls cordiales

      Eliminar
  17. Creo que he comprendido mi error: El argumento fila de tu expresión es el resultado de COINCIDIR menos 1, y el '0' estaría haciendo referencia al argumento 'columna'.

    Claridad meridiana.

    Un saludo y Gracias de nuevo por tu respuesta

    EM

    ResponderEliminar
  18. Hola,

    En el caso de querer insertar una imagen en un formulario, ¿como voy añadiendo imágenes a la siguiente fila? Me explico:
    voy introduciendo datos, y cada dato tiene una imagen asociada.
    Dato Imagen
    A imagen1
    B imagen2
    C imagen3
    A imagen1

    La tabla se va llenando cada vez que se calcula (pulsando F9).
    ¿cómo lo puedo hacer?
    lo he intentado con =DESREF(BaseDatos!$D$2;COINCIDIR(Ficha!$A$2;Código;0)-1;0), pero Ficha!$A$2 va cambiando de posición, es una variable por lo que no sirve...

    gracias de antemano

    ResponderEliminar
    Respuestas
    1. Hola,
      aunque no acabo de entender cuál es el proceso que quieres hacer, creoq ue te pude ser útil leer esta entrada
      http://excelforo.blogspot.com.es/2012/05/vba-una-macro-en-excel-para-insertar.html

      Estoy suponiendo que cuando hablas de 'formulario' (por el contexto) te refieres a una plantilla en una hoja de cálculo, y no un UserForm de VBA...

      Espero te pueda orientar...
      Slds cordiales

      Eliminar
  19. no entendí na-dita, me podrían poner un documento como ejemplo???

    ResponderEliminar
    Respuestas
    1. Hola,que tal?
      Espero estés bien.

      Sólo sigue los pasos tal cual se indican.
      También puedes ver otro ejemplo en
      http://excelforo.blogspot.com.es/2012/11/pegar-imagen-vinculada-en-excel-2010.html
      Un cordial saludo

      Eliminar
    2. gracias pero no creo k m funcione a lo k necesito hacer debo de hacer ke la imagen me aparesca junto con el numero de control de una persona... y lo intente con el jemplo ke dan al inicio de la pagina pero no me funciona no sale y esquee tengo el 2010..
      estoy acabada :(

      Eliminar
  20. hola! he seguido alpie de la letra tus instrucciones pero no logro desplegar la imagen solo me aparace un cuadro en blanco. cres que sea la version? uso excel 2007 en ingles.

    ResponderEliminar
    Respuestas
    1. Hola Gabino,
      la explicación está dada sobre la versíon Excel 2007, no creo que tu versión en inglés tenga nada que ver... yo repasaría bien los pasos, especialmente el tema de Nombres definidos y la situación de las diferentes celdas o rangos respecto a estos.
      Comentas cuando lo hayas verificado.
      Slds cordiales

      Eliminar
  21. Una consulta al foro: Al tratar de insertar un cuadro de texto en excel pasa lo siguiente: me enmarca el cuadro pero no puedo escribir dentro del mismo, me sale una advertencia que dice "La Referencia no es Válida" si alguien puede ayudarme le agradeceré, a modo de info tengo en Windows 8

    ResponderEliminar
    Respuestas
    1. Hola,
      no creo tenga nada que ver con la versión de Windows que tengas...
      asegúrate tienes correctamente instalado Office, y en particular Excel.
      De todas formas ese error de 'Referencia no válida' normalmente se dá cuando trabajamos con Tablas dinámicas o con fórmulas que operan sobre rangos o celdas (referencias de Excel) o quizá con nombres definidos; por lo que es muy extraño que al intentar insertar un sencillo objeto (un Cuadro de texto, sin más..) aparezca un error de Referencia, cuando el onjeto no está aún asociado a nada.

      Lo siento, pero no puedo darte una solución...
      :-(

      Eliminar
  22. Hola ismael tengo el siguiente problema

    me indica que existe un error en la formula =DESREF(Explicación!$I$2;COINCIDIR(Circunstancia!$B$16;CLAVE;0)-1;0, el error lo muesta en la siguiente columna "Explicación!$I$2" ya que esa columna donde me indica que existe el erro es donde se encuentra la primerra imagne como en tu ejemplo, ya estube buscando cual es el error y no puedo solucionarlo, me podrias ayudar.

    de antemano gracias

    saludos y excelente información.

    ResponderEliminar
    Respuestas
    1. Hola!! muchas gracias
      ;-=

      Necesitaría ver el fichero para encontrar el error, en principio tiene buena pinta. Si es posible, envíame tu fichero a
      excelforo@gmail.com

      Slds cordiales

      Eliminar
  23. me podrias mandar el ejemplo de tu archivo por favor, mi correo es michael_20891@hotmail.com

    ResponderEliminar
    Respuestas
    1. Hola Michael,
      lo siento de verdad, pero recientemente cambié de equipo y perdí todos los ficheros antigüos.
      De todas formas, si sigues las indicaciones paso a paso, podrás reconstruir el trabajo... y en todo caso, si hubiera alguna cosa que te fallara, puedes enviármelo a
      excelforo@gamil.com

      De nuevo lo siento, un saludo

      Eliminar
  24. Buenas Tardes Ismael

    Logre finalizar la formula unicamente cambiando los (;) por (,) pero ahora que quise implementar este producto en mi trabajo con mis compañeros, no esta cambiando la imagen, ya que cuentan con la version 2010 y no esta funcionando, me podrian apoyar indicandome que necesito hacer para que funcione en el excel 2010.

    De antemano gracias

    Saludos.

    ResponderEliminar
    Respuestas
    1. Hola,
      no debería existir diferencia entre 2007 y 2010.
      Si quieres envíame el fichero a
      excelforo@gmail.com
      y lo reviso.
      Sdls cordiales

      Eliminar
  25. Hola Ismael

    Gracias por tu pronta respuesta, sin embargo no puedo proporcionarte el archivo, ya que son políticas de la empresa, espero y entiendas eso, por otra parte no quisiera que esto cause alguna problemática y perder el apoyo que han venido brindando.

    Si me pudieras explicar cual es la causa a esta problemática que presento seria de muchísima ayuda.

    De antemano gracias

    Agradezco su comprensión

    Saludos.

    ResponderEliminar
    Respuestas
    1. Hola!
      no te preocupes... sería más fácil viendo el fichero.
      Verifica los nomnres definidos y los objetos incrustados, es lo único que se me ocurre... ya te digo que debe funcionar exactametne igual en 2007 y 2010.

      Suerte!!!

      Eliminar
  26. Muy buenas Excelforo, he intentado elaborar un libro en excel en donde a una hoja la he nombrado "base de datos" y a la otra "ficha", he seguido los pasos mas arriba mencionado y encuentro un pequeño obstaculo, nose exactamente en donde debo realizar la carga de esta asignacion, "foto =DESREF(BaseDatos!$D$2;COINCIDIR(Ficha!$A$2;Código;0)-1;0)". Si me pudiera ayudar lo antes posible le estaria muy agradecido.

    ResponderEliminar
    Respuestas
    1. Hola,
      lo importante de este ejercicio es asignar esos Nombres correctamente:
      Código =BaseDatos!$A$2:$A$5
      foto =DESREF(BaseDatos!$D$2;COINCIDIR(Ficha!$A$2;Código;0)-1;0)

      Para ello accede al Administrador de Nombres en la Ficha Fórmulas > grupo Nombres definidos, y crea los nuevos Nombres, escribiendo en el campo Se refiere a: las fórmulas:
      =BaseDatos!$A$2:$A$5
      y
      =DESREF(BaseDatos!$D$2;COINCIDIR(Ficha!$A$2;Código;0)-1;0)
      correspondientes.
      Slds cordiales

      Eliminar
  27. Hola! al introducir el nombre foto =DESREF(BaseDatos!$D$2;COINCIDIR(Ficha!$A$2;Código;0)-1;0) me da error en la fórmula. el error me indica que hay algo mal en $D$2. No termino de entender por qué. Espero vuestra ayuda. Desde ya muchas gracias. Saludos...Ignacio.

    ResponderEliminar
    Respuestas
    1. Hola Ignacio!
      bueno, si has seguido los pasos, la hoja se llama BaseDatos (como indicas en la fórmula) y has generado el Nombre definido Código no debería darte error la fórmula...Tendría que ver el fichero. Si quieres puedes enviármelo a
      excelforo@gmail.com
      Saludos

      Eliminar
  28. Hola, yo tengo excel 2010, al principio tuve el mismo invonveniente con la formula, que se soluciona reemplazando los ; por , . Pero al insertar la imagen y querer reemplazar =INCRUSTAR("Paint.Picture";"") por =foto, me da un error de referencia. Estuve viendo los otros post que sugieren y no me doy cuenta cual es la solucion.

    Muchas Gracias
    Carolina

    ResponderEliminar
  29. Hola Ismael:
    Te felicito por tu gran aporte al mundo de Excel.
    Tengo una cuestión que preguntarte que me está dando problemas.
    Últimamente Excel, al introducirle un valor entero en cualquer celda registra la centésima parte del valor. Es decir, si introduzco un valor de 120 Excel muestra 1,20.
    He verificado y no están formateadas las celdas, tampoco condicionadas.
    No se exactamente como resolver este problema.
    Te agradeceré cualquier sugerencia.
    Saludos,

    ResponderEliminar
    Respuestas
    1. Hola!
      creo que debería leer la explicación de esta entrrada
      http://excelforo.blogspot.com.es/2013/01/insertar-automaticamente-un-punto.html

      diría que ahí encontrarás la solución a tu problema.
      Cordiales saludos

      Eliminar
    2. Muchas gracias por facilitarme la solución a mi problema decimal.
      Aunque mi versión de Excel es muy vieja, 2002,pero con tu orientación pude localizar en Herramientas - Opciones - Modificar --> Número fijo de decimales, cuya casilla estaba tildada y fijada en 2 posiciones decimales.
      Te reitero mi agradecimiento y te felicito por tu vasta experiencia.
      Un cordial saludo,

      Eliminar
  30. Muchas gracias por el tutorial. Tengo un problema con la fórmula DESREF. Me marca error con el primer parámetro (En tu caso: BaseDatos!$D$2). El problema está relacionado con que la celda, o la fórmula, no me aceptan el valor de tipo Mapa de bits. Me logran devolver valores numéricos cuando me cambio a la columna de los códigos, pero no los de las imágenes. ¿Podrías explicarme qué sucede? Gracias.

    ResponderEliminar
    Respuestas
    1. Hola Aram,
      mejor envíame el fichero que tengas a
      excelforo@gmail.com
      para que lo pueda revisar...
      Como he dicho en comentarios anteriores la clave de este ejercicio está en los Nombres definidos, poco más...
      Si te has cerciorado que los nombres están bien creados, con los nombres de las hojas correctamente indicados, así como de los rangos, debería funcionar sin ningún problema.

      Slds

      Eliminar
  31. Hola, me ha funcionado perfecto y me ha sido muy util, el inconveniente lo tengo cuando quiero imprimir, no se muestra la imagen, solo un recuadro vacio. En propiedades de la imagen esta tildado imprimir objeto, he buscado en las opciones de impresion y no he encontrado otra cosa que modificar. He intentado guardarlo como pdf pero se genera de la misma manera, con el recuadro en blanco. Uso Excel 2007, que podra ser???

    Nuchas Gracias

    ResponderEliminar
    Respuestas
    1. Hola!
      pues si es algo raro... no creo sea la versión, yo lo he probado en alguna ocasión e imprime correctamente.
      Envíame el fichero a
      excelforo@gmail.com

      y lo revisaré, a ver si encuentro alguna cosa 'rara'.
      Saludos cordiales

      Eliminar
  32. Muchas Gracias por tu ayuda, te envie el mail con los archivos.

    Saludos

    ResponderEliminar
  33. Hola e visto tus tutoriales y me parecen buenos para uno que no save de todo, yo tengo una duda jala me la puedas audar a aclarar.
    Tengo un listado de peliculas digmos 10, Nombre, Genero , Imagen, lo que necesito es que cuando yo le de filtrar por genero me muestre por ejemplo las 3 peliculas de Comedia con sus Imagenes pero no el resto de ellas SI me hago entender?, al hacer filtrar por genero desaparescan las imaganes que no son de esas peliculas gracias

    ResponderEliminar