lunes, octubre 15, 2007

Rangos tridimensionales en Excel (3D ranges)

En la nota anterior sobre CONTAR.SI con rangos en varias hojas de Excel mencionábamos los rangos tridimensionales (rangos 3D).
Rangos tridimensionales son aquellos que se extienden a más de una hoja. Supongamos un cuaderno con tres hojas, donde cada hoja contiene datos de ventas de los agentes de una empresa. Cada hoja contiene los datos de un año determinado. Por ejemplo, la tabla de la Hoja1 se ve así:




La Hoja2 y la Hoja3







Si queremos sumar el total del Agente 1 para los años 2002 (Hoja1), 2003 (Hoja2) y 2004 (Hoja3) podemos usar esta fórmula:

=SUMA(B2;Hoja2!B2;Hoja3!B2)

Pero en lugar de usar referencias separadas para cada rango (la celda B2 en cada hoja, en nuestro ejemplo), podemos usar un rango tridimensional:

SUMA(Hoja1:Hoja3!B2)

De la misma manera, si queremos sumar las ventas de todos los agentes a lo largo de los tres años, usamos la fórmula

=SUMA(Hoja1:Hoja3!B2:B6)

O el promedio de ventas de los agentes a lo largo de los tres años

=PROMEDIO(Hoja1:Hoja3!B2:B6)


No todas las funciones pueden ser usadas con rangos tridimensionales. De acuerdo a la ayuda de Excel, las funciones que permiten el uso de rangos 3D son:
SUMA, PROMEDIO, PROMEDIOA, CONTAR, CONTARA, MAX, MAXA, MIN, MINA, PRODUCTO, DESVEST, DESVESTA, DESVESTP, DESVESTPA, VAR, VARA, VARP y VARPA.
A juzgar por un artículo del año 1999 del Excel Experts E-letter (EEE) publicado por David Hager, hay más funciones que aceptan rangos 3D que las mencionadas por la ayuda de Excel. Entre ellas: PERCENTIL, CUARTIL, K.ESIMO.MENOR, K.ESIMO.MAYOR, JERARQUIA, MEDIANA, MEDIA.ACOTADA, COEFICIENTE.ASIMETRIA, Y, O, DESVPROM, DESVIA2, SUMA.CUADRADOS.


Como con todo rango en una fórmula, este puede ser introducido manualmente, escribiendo cada uno de los términos, o "apuntando" al rango con el mouse.
En este caso, empezamos escribiendo el nombre de la función



Luego, manteniendo la tecla Mayúsculas (Shift) apretada señalamos con el mouse la pestaña de la última hoja en nuestro rango



Finalmente marcamos el rango de celdas



Los rangos tridimensionales no pueden ser usados con fórmulas matriciales. Es posible hallar muchas UDF's (funciones definidas por el usuario) en la red que dan respuesta al uso de rangos tridimensionales en fórmulas matriciales o funciones que no usan este tipo de rangos.

Mi favorito es el complemento MOREFUNC.XLL de Laurent Longre, ya mencionado en mi nota anterior, y que puede descargarse aquí.
Este complemento trae varias funciones que trabajan con rangos 3D y amplían su uso más allá de la capacidad de las funciones nativas de Excel:

COUNTIF.3D: la función CONTAR.SI con rangos 3D

THREED: convierte un rango 3D en un rango simple, permitiendo de esta manera el uso de rangos tridimensionales en fórmulas matriciales.

Por ejemplo, si queremos contar todos los valores mayores a 5000 en las tres hojas de nuestro ejemplo, al usar la fórmula

=CONTAR.SI(Hoja1:Hoja3!B2:B6;">5000")

el resultado es #¡VALOR!

En cambio usando =COUNTIF.3D(Hoja1:Hoja3!B2:B6;">5000") obtenemos el resultado "10".

Para usar estas fórmulas debemos primero instalar el complemento. Luego podeemos usar el asistente de funciones



y seleccionar la función





Technorati Tags:

13 comentarios:

  1. Me parece muy interesante tu post,me sirve para una aplicación que le voy a sacar partido.

    Muy buen post

    ResponderBorrar
  2. Alguien me puede ayudar como usar esto con la funcion sumar.si, aun no lo capto

    ResponderBorrar
  3. Fijate en la ayuda en línea de Excel. La explicación incluye ejemplos.

    ResponderBorrar
  4. Jorge, muchas gracias por contestar tan rapido, pero me refiero a usar la formula sumar.si con los rangos tridimensionales, mencionastes la formula threed (ya instale el plugin) pero no aplicaste el ejemplo, intente buscar una funcion sumar.si usando rangos tridimensionales pero aun no logro encontrarlo.
    Tengo un libro con 31 hojas, cada una representa un dia del mes, en cada hoja hay 2 columnas una nombres y la otra cantidades, pero los nombres no son constantes en todas las hojas, lo que deseo es hacer una hoja resumen al final del libro, creo que con la funcion sumar.si solucionaria este problema.

    Gracias

    ResponderBorrar
  5. Hay algún motivo en particular por el que tienes que manejar una hoja por cada día?
    Tal vez tablas dinámicas pueden darte una solución más eficiente para tu modelo.
    Si me mandas el archivo podría darme una idea más cabal del problema.

    ResponderBorrar
  6. Es muy intersante tu articulo, pero tengo una duda. Se puede usar rangos tridimensionales para realizar graficas y si es asi, cómo?

    Gracias y un saludo

    ResponderBorrar
  7. Si te refieres a usar el rango 3D directamente para generar la gráfica, la respuesta es no.
    Pero si puedes usar celdas de una hoja que contengan rangos 3D para generar la gráfica.

    ResponderBorrar
  8. Hola Jorge,
    Utilizo mucho los rangos tridimensionales. Sin embargo hoy, intenté hacer unos acumulados de ventas y me di cuenta que la función INDIRECTO no es compatible con ellos. La función que escribí es la siguiente:
    =SUMA(INDIRECTO("1:"&A1&"!B6"))
    (en A1 escribía el mes actual, el cual era asimismo el nombre de la hoja, en B6 el valor a acumular.
    ¿Se te ocurre alguna otra solución?
    Gracias!

    ResponderBorrar
  9. Hola Rita

    no tengo muy claro cual es el valor que aparece en la celda B6. Puedes mandarme el archivo para que le de un vistazo?

    ResponderBorrar
  10. Hola Jorge, te quería preguntar si existe alguna forma de manejar tablas de tres entradas en excel y poder visualizarlas a través de algún plug in. Es decir a partir de una matriz tradicional, de dos ejes, agregar un tercero que componga un tercer eje. Si existiera te agradezco la info.
    Gonzalo

    ResponderBorrar
  11. Visualizar la tabla o un gráfico con tres ejes?

    ResponderBorrar
  12. se pueden consolidar varias hojas en exel en una sola pero si estas tienen datos textos y datos numericos

    ResponderBorrar
  13. ¿Es una pregunta o una afirmación?

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.