jueves, 16 de febrero de 2012

Fórmulas 3D tridimensionales en Excel.

Es muy habitual cuando trabajamos resumiendo o consolidando datos en nuestros libros de trabajo de Excel el empleo de las fórmulas tridimensionales o fórmulas 3D, estas fórmulas son especiales por que nos permiten trabajar u operar al tiempo sobre celdas de diferentes hojas de nuestro libro, en especial, cuando las disposiciones de esas celdas responden a un mismo patrón o estructura en las diferentes hojas. Según la ayuda de Excel, a esto le llamamos Referencia 3D.
Es importante mencionar que este tipo de fórmula 3D no admite operar con todas las funciones estándar existentes, sólo podremos hacerlo con las siguientes:
SUMA, PROMEDIO, PROMEDIOA, CONTARA, MAXA,MIN, MINA, PRODUCTO, DESVEST, DESVESTA, DESVESTP, DESVESTPA, VAR, VARA, VARP y VARPA.

El método para componer estas funciones consiste en seleccionar la celda en la que desee escribir la función, después escribiremos el nombre de alguna de las funciones anteriores e iniciaremos o abriremos un paréntesis. Seguidamente, con el ratón, haremos clic en la pestaña de la primera hoja de cálculo a la que desea hacer referencia.; y con la tecla MAYUSC presionada (sin soltar) haremos clic en la pestaña de la última hoja de cálculo a la que desea hacer referencia. Es importante aclarar que al trabajar con estas fórmulas 3D, se hace necesario que todas las hojas con datos estén contiguas (una al lado de la otra), ya que realmente estamos indicando una operación entre la primera hoja y la última seleccionada, es decir, un intervalo de hojas.
Por último seleccionaremos la celda o el rango de celdas sobre el que deseamos trabajar en las diferentes hojas; completaremos nuestra fórmula 3D y, a continuación, presionaremos ENTER.
El aspecto de una función 3D para una suma sería este:
=SUMA(Hoja1:Hoja5!A1:C3)
que sumaría el rango A1:C3 de cada una de las hojas del libro de trabajo situadas entre las Hoja1 y la Hoja5, ambas incluidas.


Veamos un ejemplo sencillo. Partiremos de los datos de tres empresas, situados cada uno en una hoja (llamadas 'Datos1', 'Datos2' y 'Datos3'); serán datos sencillos de Ingresos y Gastos. Datos en todo caso que queremos tener tratados o consolidados en una cuarta hoja (Resumen-Consolidado):

Fórmulas 3D tridimensionales en Excel.


En la hoja 'Resumen-Consolidado', en la celda B3 escribiremos:
=SUMA(
y a continuación con el ratón apuntaremos a la pestaña de la hoja 'Datos1', con lo que veremos en la celda:
=suma(Datos1!
ahora, con la tecla MAYUSC presionada, nuevamente con el ratón, haremos clic en la última de las hojas por la derecha que queramos incluir en el consolidado, en nuestro ejemplo, la hoja 'Datos3', ya vemos en nuestra celda activa:
=suma('Datos1:Datos3'!
Finalmente nos queda seleccionar, para esta celda, la referencia a incluir en nuestra fórmula a sumar, en el ejemplo planteado, la B3. Cerramos el paréntesis y validamos:
=SUMA(Datos1:Datos3!B3)

Fórmulas 3D tridimensionales en Excel.


En estos casos, como decíamos al principio de la entrada, se hace necesario que la distribución de información en las diferentes hojas responda a un mismo patrón, es decir, que en la celda B3 exista información equivalente o acumulable, en nuestro ejemplo, en todas las hojas, en la celda B3 aparece la información de 'Ingresos', y en la B4 la de 'Gastos'.
Para completar nuestro resumen, sólo es necesario arrastrar nuestra fórmula 3D hacia abajo, ya que en este sentido (referencias absolutas y relativas), se comportan igual al resto de funciones.
Nuestro 'Resumen-Consolidado' queda finalmente:

Fórmulas 3D tridimensionales en Excel.

17 comentarios:

  1. Hola ExcelForo
    Muy bueno este ejercicio, creo haber leído en alguna ocasión sobre las Formulas 3D, pero aquí está bien explicado como poder ejecutarla, una lástima que este tan limitada las Funciones a emplear, de seguro hoy me podre dormir sin problema, tan temprano y he aprendido algo nuevo.

    Un Cordial Saludo
    Lázaro.

    ResponderEliminar
    Respuestas
    1. Muchas gracias... cierto es que la limitación es alta, pero sin duda las ventajas de trabajar con estos rangos tridimiensionales (fila x columna x hoja) la compensa.
      Slds

      Eliminar
    2. soy milagro estoy muy alegre porque ahora....tendo tiempo para
      hacer otras cosas...me gusta esta pagina!!!!

      Eliminar
  2. esto me va a ahorrar muuucho tiempo.
    muchísimas gracias!!

    ResponderEliminar
  3. me gustaria que se pueda almacenar data en una celda, no solo para sumarla o trabajarla como funcion

    ResponderEliminar
    Respuestas
    1. Hola que tal!?
      para introducir daot en una celda sólo tienes que escribir en ella... no hace falta que sea un número o una función.
      Una celda admite valores de tipo fecha, número o texto.

      Realmente no sé a qué te refieres.

      Un atento saludo

      Eliminar
  4. AAMMMMM NO EN TENDI NADA
    EXPLICAME MEJOR

    ResponderEliminar
    Respuestas
    1. Hola, que tal estás?.Espero te encuentres bien.
      Lo primero que te diría es que, por favor, evites escribir en mayúsculas... esto es igual que si gritaras.
      Y en segundo lugar, estaré encantado de ayudarte si especificas qué no has entendido.

      Cordiales saludos

      Eliminar
  5. Buenas, cual sería la solución cuando los nombres de hoja tienen espacio. Por ejemplo
    Hoja1-> Ventas
    Hoja2-> Ventas Lima
    Hoja3-> Ventas Lince

    Saludos.

    ResponderEliminar
    Respuestas
    1. Hola Rick,
      el proceso es idéntico, no influye cómo se llamen las hojas, basta que selecciones la primera y la última hoja, quedaría:
      =SUMA('Ventas:Ventas Lince'!A2)
      Saludos cordiales

      Eliminar
    2. Estimados..
      En este mismo ejemplo... cómo quedaría si A2 hay que multiplicarla por B2 y luego hacer la suma. ??

      Eliminar
    3. Hola,
      lo más sencillo sería en cada hoja multiplicar en otra celda (C2) el producto A2*B2, para luego sumar sobre esa celda de manera tridimensional...

      Otra opción sería construir una UDF (función personalizada con VBA) siguiendo las pautas expuestas en:
      http://excelforo.blogspot.com.es/2014/01/vba-una-funcion-personalizada-de.html

      Espero te oriente...
      Slds

      Eliminar
  6. Hola Ismael:
    Un gusto de Saludarte.¿que pasa cuando los datos a consolidar son el resultado de una formula que genera un nombre?

    saludos

    Antonio Luis

    ResponderEliminar
    Respuestas
    1. Hola Antonio,
      no sé si entendí bien.. pero estad fórmulas 3D son muy 'especiales'.
      ¿podrías ponerme en antecedentes con el ejemplo que propones???

      Gracias!!

      Eliminar
  7. Mira hasme el trabajo de comercio ..😂😂✌
    Que es como tal la fórmula tridimensional..?

    ResponderEliminar
    Respuestas
    1. Hola,
      solo lee el post..
      Un fórmula 3D es una fórmula que trabaja sobre diferentes hojas a la vez, realizando algún tipo de operación simultánea sobre rangos/celdas de éstas.
      Slds

      Eliminar