sábado, 20 de junio de 2009

Cálculos con Rangos Tridimensionales



En el artículo de hoy vamos a ver cómo podemos trabajar con lo que se denomina Rangos Tridimensionales en Excel. Un Rango Tridimensional lo podemos definir como aquel rango que se extiende a varias hojas. Supongamos, por ejemplo, que tenemos en varias hojas las ventas relativas a varios comerciales en distintas zonas:
En la hoja Zona1 tenemos:
En la hoja Zona2 tenemos:
En la hoja Zona3 tenemos:
En la hoja Zona4 tenemos:
Finalmente tenemos una hoja RESUMEN donde queremos recoger las ventas totales de cada comercial y el promedio de cada uno de ellos:
Para realizar estos cálculos NO es necesaria la siguiente fórmula en B3:
=Zona1!B3+Zona2!B3+Zona3!B3+Zona4!B3
Para solucionar este cálculo de una manera más sencilla y rápida debe hacer lo siguiente:
1. Nos situamos en la hoja RESUMEN en la celda B3 y escribimos =SUMA(
2. A continuación hacemos clic en la hoja Zona1 y en la celda B3.
3. A continuación pulsamos SHIFT (Mayús) y manteniendo dicha tecla pulsada hacemos clic en la última hoja, es decir, en la hoja Zona4, y pulsamos enter.
El resultado es la siguiente fórmula:
=SUMA(Zona1:Zona4!B3)
Como puede comprobar estamos sumando un rango que se extiende a las hojas zona1, zona2, zona3 y zona4. Esto es lo que se denomina un rango tridimensional en excel.
4. Nos situamos en C3 de la hoja RESUMEN y escribimos =PROMEDIO( y repetimos los mismos pasos que en el caso anterior para solucionar esta fórmula:
=PROMEDIO(Zona1:Zona4!B3)
5. Seleccionamos B3 y C3 y hacemos doble clic en la parte inferior derecha de dicha selección (copiado inteligente) para rellenar todas las fórmulas:
La ayuda de Excel nos indica qué funciones pueden utilizarse con referencias 3D. A saber: "Pueden utilizarse referencias 3D a las celdas de otras hojas para definir nombres y crear fórmulas mediante las siguientes funciones: SUMA, PROMEDIO, PROMEDIOA, CONTAR, CONTARA, MAX, MAXA, MIN, MINA, PRODUCTO, DESVEST, DESVESTA, DESVESTP, DESVESTPA, VAR, VARA, VARP y VARPA. No pueden utilizarse referencias 3D en fórmulas matriciales".
Como es habitual en las "ayudas" de excel no nos cuenta toda la verdad... De hecho hay muchas más funciones que permiten referencias 3D. Estas son algunas que he comprobado personalmente (y seguro que hay más) y que no aparecen en el listado de la ayuda:
CUARTIL, K.ESIMO.MENOR, K.ESIMO.MAYOR, JERARQUÍA, Y, O, PERCENTIL.

10 comentarios:

  1. ME HA VENIDO MU BIEN GRACIAS

    ResponderEliminar
  2. muchas gracias, es un muy buen aporte :)

    ResponderEliminar
  3. Excelente, creo que me sera muy util para realizar mi tarea.

    Saludos y Gracias

    ResponderEliminar
  4. rangos es lo mismo que llamarle nombres tridimensionales???

    ResponderEliminar
  5. No es exactamente lo mismo. Un rango tridemensional es aquel que se extiende a varias hojas, como en el ejemplo de este post. Si a dicho rango le damos un nombre concreto entonces sí estaríamos hablando de un "nombre tridimensional".

    ResponderEliminar
  6. holaaa

    muyyy buen post...:D

    pero entonces que seria un nombre tridimensional?

    algun ejemplo?

    es que ami tampoko me queda muy claro la diferencia

    ResponderEliminar
  7. hola!!! me gustaria saber acerca de los nombres tridimensionales que mencionan arriba. Gracias!!!!

    ResponderEliminar
  8. Os sugiero la lectura del post que publiqué en Julio de 2009 titulado "Crear Rangos con Datos en Distintas Hojas". Cuando tenemos datos en distintas hojas podemos seleccionar dichos datos (con lo que tendríamos un rango tridimensional) y, posteriormente darle un nombre (con lo que tendríamos un nombre tridimensional o nombre de rango tridimensional). Espero que con esta lectura se aclaren vuestras dudas. Un saludo

    ResponderEliminar
  9. Estimado Kiko:
    ¿Es posible hacer validaciones en rangos tridimensionales?
    Tengo un libro con varias hojas, cada una con el horario de un curso diferente. Debo evitar poner al un profesor en dos clases al mismo tiempo...

    ResponderEliminar
    Respuestas
    1. Mira este post y me dices si es lo que buscas: http://lareboticadeexcel.blogspot.com.es/2012/03/resaltar-duplicados-en-distintas-hojas.html
      Un saludo

      Eliminar