viernes, 3 de abril de 2009

Obtener datos de una tabla con INDIRECTO y NOMBRES

Supongamos que tenemos una tabla como la que se muestra en la siguiente figura donde se encuentran las ventas de nuestras distintas delegaciones a lo largo de todo un año.

Lo que buscamos es tener una zona de entrada de datos donde escribamos, o mejor dicho, seleccionemos de una lista la Delegación (celda C3) y de otra lista el mes (celda C5) y nos proporcione la cifra de ventas en cuestión.
Hay diversas maneras de solucionar este problema. Nosotros lo haremos por medio de Crear Nombres, Validación de Datos y de la función INDIRECTO. Veamos cómo.
1. Seleccionamos el rango A10:E22 y vamos al menú Insertar/Nombre/Crear. Automáticamente se abrirá la siguiente ventana:Por defecto nos muestra seleccionadas las casillas de verificación de Fila Superior y Columna izquierda. Esto es debido a que se ha encontrado nombres dentro del rango que hemos seleccionados tanto en la fila superior como en la columna izquierda de nuestra selección. Pulsamos aceptar. A partir de este momento podemos utilizar este tipo de entradas para encontrar la información requerida: =orense may_09 y nos devolverá el valor 10.300€ (fíjese que entre el nombre orense y may_09 debe dejar un espacio).

Esta es una manera sencilla pero requiere que escribamos cada vez el nombre de la delegación y el del mes para obtener la cifra de ventas deseada. A continuación veremos una forma más ágil de solucionar el problema:

1. En el rango G10:G14 escribimos la lista de delegaciones tal y como excel las ha nombrado. Dichos nombres los podemos consultar en el menú Insertar/Nombre/Definir. También podemos proceder directamente a pegar en un rango de excel los distintos nombres creados yendo a Insertar/Nombre/Pegar. En el rango I10:I22 escribimos los nombres referentes a los meses.


2. Nos situamos en la celda C3, abrimos el menú Datos/Validación y seleccionamos la opción Permitir: Lista. Como origen marcamos el rango donde tenemos las delegaciones (sin incluir el título), es decir, G11:G14 y pulsamos aceptar.



3. A partir de este momento en la celda C3 ya tenemos disponible una lista desplegable con el nombre de nuestras delegaciones.

4. Nos situamos en la celda C5 y realizamos el mismo paso anterior pero en este caso el origen será I11:I22 (la lista de meses).
Con esto hemos conseguido que en C3 y en C5 aparezcan listas desplegables con las opciones disponibles en delegaciones y meses. Ahora sólo nos falta conseguir que seleccionando elementos de estas listas la fórmula que debe proporcionarnos la cifra de ventas funcione. Para ello vamos a utilizar la función INDIRECTO. Esta función (que encontrará dentro del apartado de funciones de búsqueda y referencia) devuelve la referencia especificada por una cadena de texto. En nuestro ejemplo nos situamos en C7 y escribimos la siguiente fórmula:

=INDIRECTO(C3) INDIRECTO(C5) (deje un espacio entre ambas expresiones)

Esta fórmula convertirá el nombre que aparezca en C3 y el nombre que aparezca en C5 en una referencia válida para excel y nos mostrará el resultado correcto: 10.300€



7 comentarios:

  1. Hola Kiko,

    En priumer lugar felicitarte publicamente por tu blog (ya lo hice en persona)

    Poco a poco estoy leyendo del primer al ultimo post. En este en concreto me surge la duda de si es posible hacer esto en excel 2007. No consigo encontrar la opcion!

    Muchas gracias por adelantado

    ResponderEliminar
  2. Muchas gracias Jaime. Espero que te resulte útil (que ese es el objetivo).
    Respecto a lo que me preguntas comentarte que la opción de crear nombres en excel 2007 la encontrarás en la Ficha "FÓRMULAS" y dentro de esta ficha debes pulsar la opción "Asignar Nombre a un Rango". Te aparecerá la ventana de Crear nombres que puedes ver en este post. Por otro lado la "Validación de Datos" la puedes en contrar con este mismo nombre dentro de la ficha "DATOS". Por lo demás no deberías tener ningún problema.
    Un saludiño

    ResponderEliminar
  3. Utilizo bastante la función INDIRECTO, pero este truco no me lo sabía! Mil Gracias por compartirlo

    ResponderEliminar
  4. !HOLA KIKO! ACABO DE PONER EN PRACTICA LO APRENDIDO EN ESTE POST, PERO ME DA UN ERROR DE REF,TE REMITO EL ARCHIVO A TU CORREO PARA QUE ME INDIQUES DONDE ME EQUIVOCO, ES MUY INTERESANTE ESTA FUNCION . MUY AGRADECIDA POR TODO.
    JUANA GARCIA

    ResponderEliminar
    Respuestas
    1. Ya te he mandado el archivo con las correcciones. Tenías errores en los nombres de los meses y de la delegación de A Coruña. Saludiños

      Eliminar
  5. disculpen , pero alguien me podría ayudar con un problema ... lo que quiero es obtener el valor de 10.100,00$ de la tabla anterior(tabla DELEGACIONES) pero con la función indirecta y haciendo uso de los nombres de las columnas y filas .. por ejm de manera directa seria "=FEB La_Coruña" .. please gracias de antemano

    ResponderEliminar
    Respuestas
    1. Perdona Wilmer pero creo que no entiendo bien la pregunta ya que me parece que es precisamente lo que soluciona el post.

      Eliminar