Página inicial

 

Sumas condicionales y búsquedas en 3D

 

1) Sumas condicionales en 3D.

 

El término "3D" suele emplearse de manera informal en el mundillo de Excel para referirse a operaciones con celdas o rangos situados en más de una hoja de cálculo.

 

Por ejemplo, tenemos un libro con las ventas de cinco sucursales (una hoja de cálculo por sucursal) con algo parecido a:

 

 

A

B

1

Fecha

Importe

2 01/01/2013 10
3 02/01/2013 20
4 03/01/2013 30
5 04/01/2013 40
6 05/01/2013 50

 

Las hojas se llaman Hoja1, Hoja2, ... Hoja5, y hay una última hoja llamada Resumen.

 

Si necesitásemos la suma de las ventas de todas las sucursales en una celda de la hoja Resumen, la fórmula sería una "3D" típica, bastante sencillita. Lo único que necesitaríamos es comenzar a escribir la fórmula:

 

=SUMA(

 

y entonces pulsar sobre la etiqueta de la primera de las hojas (Hoja1), luego la tecla mayúsculas y, sin soltar esta tecla, pulsar sobre la etiqueta de la última (Hoja5). Las etiquetas quedarían así:

 

 

(lo que indica que se encuentran agrupadas) y la fórmula que estamos escribiendo sería en este momento:

 

=SUMA('Hoja1:Hoja5'!

 

Ya sólo quedaría poner el rango en que se encuentran las cifras de venta (columna B de cada hoja), con lo que la fórmula terminada sería:

 

=SUMA('Hoja1:Hoja5'!B:B)

 

Para desagrupar las hojas, basta con situar el cursor sobre cualquiera de sus etiquetas, pulsar con el botón derecho y seleccionar 'Desagrupar hojas'.

 

 

Cuando se trabaja con agrupaciones de hojas como la que acabamos de ver conviene tener en cuenta que estas agrupaciones son posicionales. Intentaré explicarme: en el ejemplo que acabamos de ver, si entre Hoja1 y Hoja5 hubiese una o más hojas "intrusas" que no tuviesen nada que ver con las hojas de venta de las cinco sucursales, la fórmula que hemos visto las tendría en cuenta a la hora de calcular la función SUMA().

 

Esto podría no representar ningún problema en absoluto (si, por ejemplo, las hojas no tienen datos en el rango a sumar), pero también podría tener consecuencias desastrosas si en el rango hubiese datos numéricos, con el agravante de que si en el momento de crear la fórmula no existen hojas "intrusas" entre ellas pero luego se crean, la fórmula automáticamente las incluirá por el mero hecho de estar situadas entre la primera hoja de la agrupación y la última, y asimismo si se mueve alguna hoja fuera de la agrupación de la que formaba parte pueden ocurrir todo tipo de problemas, incluyendo posibles referencias circulares si la hoja final de la agrupación se mueve de forma que incluya a la que tiene las fórmulas para totalizar.

 

Las fórmulas que veremos a partir de aquí, sin embargo, no tienen estos riesgos puesto que su funcionamiento se basa en la creación de referencias explícitas a las hojas que se desea incluir, por lo que ofrecen más seguridad que las agrupaciones de hojas, sobre todo en el caso de libros en los que trabaja más de una persona.

 

 

Ahora supongamos que necesitásemos las sumas de las ventas que cumpliesen determinada condición, por ejemplo ser inferiores a determinado importe, pongamos que 30. Lo más fácil en este caso sería poder utilizar la función SUMAR.SI del mismo modo que usamos la función SUMA en la fórmula anterior, pero por desgracia SUMAR.SI no es capaz de trabajar en 3D, por lo que la fórmula:

 

=SUMAR.SI('Hoja1:Hoja5'!B:B;"<30")

 

simplemente no funcionará, devolviendo el error #¡VALOR!

 

Parece obvio que para tan sólo cinco hojas la solución más sencilla sería utilizar cinco veces la función SUMAR.SI, una para cada hoja:

 

=SUMAR.SI(Hoja1!B:B;"<30")+SUMAR.SI(Hoja2!B:B;"<30")+SUMAR.SI(Hoja3!B:B;"<30")+SUMAR.SI(Hoja4!B:B;"<30")+SUMAR.SI(Hoja5!B:B;"<30")

 

pero ahora imaginemos que no fuesen cinco hojas sino cincuenta...

 

 

La forma más sencilla de proceder en estos casos es crear un rango con los nombres de todas las hojas, pongamos que en Resumen!B1:B5

 

 

y utilizar las funciones SUMAPRODUCTO, SUMAR.SI e INDIRECTO, con la siguiente sintaxis:

 

=SUMAPRODUCTO(SUMAR.SI(INDIRECTO("'"&B1:B5&"'!B:B");"<30";INDIRECTO("'"&B1:B5&"'!B:B")))

 

Lo que hace la función INDIRECTO() es crear referencias a las columnas B de las cinco hojas, referencias que pueden ser usadas sin problemas por SUMAR.SI

 

Los apóstrofos ' que, aunque casi ni resultan visibles, hay en "'"&B1:B5&"'" no serían necesarios en el caso de este ejemplo, pero sí lo serían si los nombres de las hojas tuviesen uno o más espacios, así que lo mejor es ponerlos siempre por defecto.

 

Esta fórmula se podría acortar un poco si se introdujese como fórmula matricial (pulsando mayúsculas control entrada al mismo tiempo):

 

{=SUMA(SUMAR.SI(INDIRECTO("'"&B1:B5&"'!B:B");"<30";INDIRECTO("'"&B1:B5&"'!B:B")))}

 

(Nota: Las llaves no habría que teclearlas; son la forma convenida para indicar que una fórmula es matricial, y deberían aparecer automáticamente en la barra de fórmulas al introducirla como matricial)

 

 

Utilizando un literal de matriz (también llamados a veces "constantes matriciales") para enumerar los nombres de las hojas, incluso es posible prescindir del rango con nombre:

 

=SUMAPRODUCTO(SUMAR.SI(INDIRECTO("'"&{"Hoja1";"Hoja2";"Hoja3";"Hoja4";"Hoja5"}&"'!B:B");"<30";INDIRECTO("'"&{"Hoja1";"Hoja2";"Hoja3";"Hoja4";"Hoja5"}&"'!B:B")))

 

pero hay que tener en cuenta que los puntos y comas que separan cada uno de los nombres de las hojas dentro de los literales de matriz (que, para que quede más claro, en esta fórmula es todo lo que está encerrado entre las llaves { }) tendrían que ser sustituidos por el separador de filas que esté establecido en la configuración regional del equipo, si dicho separador no fuese el punto y coma.

 

 

Si los nombres de las hojas se atienen a algún tipo lógico de serie, puede ser posible prescindir del rango con los nombres de las hojas (o el literal de matriz), pero obviamente habrá que pagar el precio de que la fórmula resulte más complicada.

 

En el ejemplo que estamos viendo, resulta evidente que la serie es tan sencilla como los números enteros desde el 1 hasta el 5 (Hoja1, Hoja2... Hoja5), así que vamos a ver cómo podemos prescindir del rango con los nombres de las hojas.

 

La fórmula será muy parecida a la anterior. La única diferencia es que en lugar de la referencia al rango donde se encuentran los nombres de las hojas necesitaremos una expresión capaz de generar dichos nombres. En este caso la expresión es:

 

"'Hoja"&FILA(INDIRECTO("1:5"))&"'!B:B"

 

así que la fórmula sería:

 

=SUMAPRODUCTO(SUMAR.SI(INDIRECTO("'Hoja"&FILA(INDIRECTO("1:5"))&"'!B:B");"<30";INDIRECTO("'Hoja"&FILA(INDIRECTO("1:5"))&"'!B:B")))

 

(que, de nuevo, puede ser acortada sustituyendo SUMAPRODUCTO por SUMA e introduciéndola como matricial).

 

 

Ahora supongamos que los nombres de las hojas fuesen los meses del año. En este caso la serie con los nombres de los doce meses se podría conseguir mediante la expresión:

 

"'"&TEXTO(--"9-1-1"+(FILA(INDIRECTO("1:12"))-1)*30;"mmmm")&"'!B:B"

 

así que la fórmula sería:

 

=SUMAPRODUCTO(SUMAR.SI(INDIRECTO("'"&TEXTO(--"9-1-1"+(FILA(INDIRECTO("1:12"))-1)*30;"mmmm")&"'!B:B");"<30";INDIRECTO("'"&TEXTO(--"9-1-1"+(FILA(INDIRECTO("1:12"))-1)*30;"mmmm")&"'!B:B")))

 

Resulta evidente que sería posible seleccionar un conjunto de meses distinto a los doce del año cambiando los números 1:12. Por ejemplo, si se necesitase el total del ultimo semestre del año para las ventas < 30 la fórmula sería:

 

=SUMAPRODUCTO(SUMAR.SI(INDIRECTO("'"&TEXTO(--"9-1-1"+(FILA(INDIRECTO("7:12"))-1)*30;"mmmm")&"'!B:B");"<30";INDIRECTO("'"&TEXTO(--"9-1-1"+(FILA(INDIRECTO("7:12"))-1)*30;"mmmm")&"'!B:B")))

 

o para el 2º trimestre:

 

=SUMAPRODUCTO(SUMAR.SI(INDIRECTO("'"&TEXTO(--"9-1-1"+(FILA(INDIRECTO("4:6"))-1)*30;"mmmm")&"'!B:B");"<30";INDIRECTO("'"&TEXTO(--"9-1-1"+(FILA(INDIRECTO("4:6"))-1)*30;"mmmm")&"'!B:B")))

 

 

Enlace a un libro con estos ejemplos funcionando.

 

 

2) Búsquedas en 3D.

 

Ahora supongamos que quisiésemos encontrar determinado importe en las columnas B de las 5 hojas (Hoja1, Hoja2...Hoja5), por ejemplo 100. Supondremos que esa cifra aparece una sola vez en una de las columnas B de las hojas, dado que si apareciese más de una vez las fórmulas que veremos devolverían la primera aparición.

 

Todas las fórmulas que veremos a partir de aquí son matriciales (hay que introducirlas pulsando mayúsculas control entrada al mismo tiempo).

 

Si en B1:B5 de la hoja Resumen tenemos los nombres de las hojas, la fórmula para averiguar en qué hoja aparece el valor 100 en su columna B sería:

 

{=INDICE($B$1:$B$5;COINCIDIR(1;--(CONTAR.SI(INDIRECTO("'"&$B$1:$B$5&"'!B:B");100)>0);0))}

 

La fórmula para averiguar en qué fila de esa hoja aparece el valor 100 sería:

 

{=COINCIDIR(100;INDIRECTO("'"&INDICE($B$1:$B$5;COINCIDIR(1;--(CONTAR.SI(INDIRECTO("'"&$B$1:$B$5&"'!B:B");100)>0);0))&"'!B:B");0)}

 

y la fórmula capaz de devolvernos la dirección completa de la celda en la que se encuentra el valor buscado sería:

 

{=INDICE($B$1:$B$5;COINCIDIR(1;--(CONTAR.SI(INDIRECTO("'"&$B$1:$B$5&"'!B:B");100)>0);0))&"!B"&COINCIDIR(100;INDIRECTO("'"&INDICE($B$1:$B$5;COINCIDIR(1;--(CONTAR.SI(INDIRECTO("'"&$B$1:$B$5&"'!B:B");100)>0);0))&"'!B:B");0)}

 

Si los nombres de las hojas no estuviesen en un rango sino que debiesen ser generados por las fórmulas, estas se complicarían un poco más, pero su funcionamiento sería básicamente el mismo.

 

Para averiguar en qué hoja aparece el valor 100 en su columna B sería en este caso:

 

{=INDICE("Hoja"&FILA(INDIRECTO("1:5"));COINCIDIR(1;--(CONTAR.SI(INDIRECTO("'Hoja"&FILA(INDIRECTO("1:5"))&"'!B:B");100)>0);0))}

 

Para averiguar el número de fila de la columna B de esa hoja en la que aparece el valor buscado, la fórmula sería:

 

{=COINCIDIR(100;INDIRECTO(INDICE("'Hoja"&FILA(INDIRECTO("1:5"))&"'!B:B";COINCIDIR(1;--(CONTAR.SI(INDIRECTO("'Hoja"&FILA(INDIRECTO("1:5"))&"'!B:B");100)>0);0)));0)}

 

y para averiguar la dirección completa de la celda en la que aparece 100, la fórmula sería:

 

{=INDICE("Hoja"&FILA(INDIRECTO("1:5"));COINCIDIR(1;--(CONTAR.SI(INDIRECTO("'Hoja"&FILA(INDIRECTO("1:5"))&"'!B:B");100)>0);0))&"!B"&COINCIDIR(100;INDIRECTO(INDICE("'Hoja"&FILA(INDIRECTO("1:5"))&"'!B:B";COINCIDIR(1;--(CONTAR.SI(INDIRECTO("'Hoja"&FILA(INDIRECTO("1:5"))&"'!B:B");100)>0);0)));0)}

 

 

Enlace a un libro con estas fórmulas funcionando.

 

 

 

 

 

 

Página inicial