Rangos variables en fórmulas

Descargar el fichero: FormulasVariables.xlsx

Algo con lo que siempre hemos soñado los usuarios avanzados de Excel es con la posibilidad de poder modificar la sintaxis de una fórmula para que se adapte de forma automática a nuestras necesidades.

Hoja1

En este caso, no es que se logre completamente este sueño, pero si seremos capaces de modificar la función SUMA para que el rango que sume sea el que nosotros elijamos sin tener que editar la fórmula, y sin tener que cambiar el rango, de forma manual.

En la columna B disponemos de 100 números aleatorios. En la celda amarilla indicamos cuantos de estos números deseamos sumar.

En la celda verde conseguimos efectuar la suma del número de sumandos indicado en la celda amarilla. Para ello, utilizamos la función:

=SUMA(INDIRECTO("B4"):INDIRECTO("B"&D5+3))

La función INDIRECTO es ya una vieja amiga de este blog y pertenece a las funciones avanzadas con las que conseguimos grandes resultados.

Además usamos el concatenador: &, que nos permite unir (concatenar) cadenas. Esto es, unimos texto o cadenas alfanuméricas.

En la celda D9 hemos efectuado las suma de los 20 primeros datos de la columna B, y para ello hemos utilizado el método tradicional, usando la función SUMA y como rango tomamos los 20 primeros valores:

=SUMA(B4:B23)

Esta fórmula tiene el inconveniente de que si en la celda amarilla cambiamos el valor, que actualmente es de 20, por otro valor, tendríamos que editar la fórmula y cambiar el rango al que se extiende la función SUMA, si realmente queremos obtener la suma del número de valores indicados en la celda amarilla.

Para ver otras maravillas que se pueden conseguir con la función INDIRECTO puede consultar estos Posts,

• Post con la etiqueta: INDIRECTO

Hoja2

Podemos practicar sumando en horizontal.

Hoja3

Calcular el determinante de una submatriz.

En las celdas de color rosa indicamos la posición inicial de fila y columna de la submatriz e indicamos la dimensión. Necesariamente la submatriz ha de ser una matriz cuadrada donde el número de filas y columnas coincide, por lo que únicamente marcamos de color rosa el número de filas (celda E23) y el número de columnas queda igual que el de filas.

Los valores de la matriz se generan de forma aleatoria. Al pulsar la tecla F9 cambian.

Hemos marcado de color verde la submatriz dentro de la matriz principal. Para obtener este color hemos utilizado Formato condicional. Esta es la fórmula empleada en el formato condicional para la celda B5 que luego se extiende al resto de la matriz.

=Y(FILA(B5)>=$C$13+$E$19-1;FILA(B5)<=$C$13+$E$19+$E$23-2;COLUMNA(B5)>=$C$14+$E$20-1;COLUMNA(B5)<=$C$14+$E$20+$E$24-2)

Libro Personal en Excel

Descargar el fichero: RentaGeoFracECO2011.xlsm

Mostramos un vídeo de cómo se crea el libro Personal.xlsb en Excel 2007. Este libro nos permitirá disponer de todas las macros que contenga, para que se puedan ejecutar desde cualquier otro libro que abramos en el futuro.

En este caso vamos a incluir en el libro personal las funciones creadas por el usuario:

• VAgeo
• VFgeo

que calculan en valor actual y el valor final de rentas geométricas.

En Excel 2003

En Excel 2003 el libro se denomina Personal.xls, y disponemos del siguiente truco:

Podemos crear una macro para que este disponible en todas las hojas de cálculo. Esto se hace con el libro PERSONAL.XLS

Personal.xls es un Libro que se carga cada vez que se inicia Excel, pero no esta visible ya que tiene el atributo de Escondido (Hidden), por lo que si necesitamos modificarlo lo debemos hacer a través del comando "Mostrar" del menú "Ventana".

En caso que no exista lo podemos crear con un libro de trabajo nuevo, salvarlo con este nombre y dejarlo en la carpeta "InicioXL" (XLStart), que se encuentra en la carpeta "Office" del directorio donde esta instalado MSOffice. Cada vez que dejamos de trabajar con este archivo, lo debemos salvar y luego activar el comando "Ocultar" del menú "Ventana", para que no se vea cuando iniciamos la aplicación.

La ruta de mi fichero en Windows XP es:

C:\Documents and Settings\Adolfo\Datos de programa\Microsoft\Excel\XLSTART\PERSONAL.XLS

En Excel 2010 y 2013

En Windows 7 y Windows 8 mi ruta para Excel 2010 y Excel 2013 es la siguiente.

C:\Users\Adolfo\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB

Desde la versión 2007 en adelante el libro Personal para a tener la extensión XLSB. En la ruta anterior es posible que no vea la carpeta AppData, si esto es así es debido a que por defecto se encuentra oculta, y hemos de pedir al Explorador de Windows que nos muestre las carpetas ocultas.

En Excel 2016

Facturación por meses

Descargar el fichero: facturacion_mensual.xlsx

Veamos cómo podemos totalizar por meses con varios métodos: SUMAR.SI, SUMAPRODUCTO y sobre todo con funciones matriciales. Destaca el quinto método que no necesita emplear la columna auxiliar 'Mes' (columna D).

Se ha creado un nombre de rango que es en realidad una fórmula. Se denomina 'miMES' y la fórmula es:

=MES(Fechas)

Este nombre de rango es el que nos permite no necesitar el uso de la columna D.

Observar la celda K18 que nos permite totalizar de una forma muy especial:

{=SUMA((miMES={1;2;3;4;5;6;7;8;9;10;11;12})*Ventas)}

Si quisiéramos totalizar únicamente las ventas de los meses 1 y 2 la fórmula a emplear sería:

{=SUMA((miMES={1;2})*Ventas)}


También merece especial mención la celda K20 de color verde:

{=MIN(SI(K6:K17>0;K6:K17;""))}

Con la fórmula matricial anterior hemos conseguido calcular la facturación del mes con menor facturación positiva.

Pulse la tecla de función F9 para observar cómo cambian los datos y los resultados.

Seleccionar datos con un desplegable

Puede descargar los siguientes archivos.

• desplegable.xlsx
• graficosVariables.xlsx

Utilizar un desplegable es una tarea cotidiana e intuitiva. Lo que pretendemos es que al elegir con el desplegable un año, la consecuencia sea que una tabla con datos se rellene automáticamente con datos de ese año.

Conseguiremos nuestro proposito con el uso de la función INDIRECTO de la que podemos ver unos cuantos Post publicados en este mismo Blog. Para verlos simplemente hemos de seguir este enlace:

• Post con la etiqueta: INDIRECTO

En la tabla verde disponemos de los datos correspondientes a tres años. Deseamos que al elegir el año en el desplegable la tabla azul se alimente automáticamente con los datos correspondientes al año elegido.

Observar que la zona azul se ha obtenido con la función INDIRECTO y se ha introducido como función matricial. Esto se puede comprobar al ver las llaves {} que son el rasgo distintivo de las funciones matriciales.

Datos de partida

Los datos de partida son los que se encuentran en la tabla superior (rango B4:E9). Lo que hacemos es crear nombres de rango con los rótulos de columna:

• Año2009
• Año2010
• Año2010

Para crearlos de forma masiva lo que hacemos en la versión de Excel 2007 es seleccionar el rango C4:E9 e ir al menú Fórmulas, y luego elegir Crear desde una selección. De esta forma llegaremos a esta ventana:

Marcamos únicamente donde pone 'Fila superior'.

Si tuviéramos Excel 2003, para llegar a esta ventana el recorrido es: Insertar, Nombre, Crear.

Aceptando esta ventana lo que hemos logrado es crear los tres rangos siguientes:

• Año2009 =Hoja1!$C$5:$C$9
• Año2010 =Hoja1!$D$5:$D$9
• Año2011 =Hoja1!$E$5:$E$9

Tabla para el BUSCARV

Creamos la tabla amarilla para luego poder usar un BUSCARV que nos proporcione el año al elegir desde el desplegable.

Creamos el desplegable

Creamos el desplegable o ComboBox,o también llamado 'Cuadro combinado'.

Esto en Excel 2007 se consigue desde la ficha Programador, y luego en Insertar uno de los Controles de formulario denominado Cuadro combinado.

En Excel 2003 se consigue obteniendo la barra de Formularios y luego eligiendo Cuadro combinado.

Al pulsar con el botón izquierdo del ratón sobre el icono del Cuadro combinado conseguiremos que el cursor se convierta en una cruz finita, y en ese momento crearemos la diagonal del desplegable arrastrando con el ratón sobre la hoja.

Luego, pulsamos el desplegable con el botón derecho del ratón y elegimos 'Formato de control'.

Como rango de entrada ponemos G13:G15 que es donde están escritos los nombres de cabecera que previamente hemos creado.

Donde pone 'Vincular con la celda' ponemos la celda G11. Esto permitirá que al elegir en el desplegable la primera opción en la celda G11 aparezca un 1; si elegimos la segunda opción aparecerá un 2; y si elegimos la tercera opción del desplegable en la celda G11 aparecerá un 3.

BUSCARV

En la celda C11 escribimos la siguiente función:

=BUSCARV(G11;tabla;2;0)

Donde el rango tabla es: F13:G15.

Con ello conseguiremos que al cambiar el valor de la celda G11 según las elecciones que hagamos del desplegable, podamos poner en esta celda (C11) la cabecera de los datos que deseamos obtener.

Son tres posibles cabeceras que podemos obtener:

• Año2009
• Año2010
• Año2010

INDIRECTO como función matricial

Finalmente hemos de emplear la función INDIRECTO. Para familiarizarnos con esta función avanzada de Excel conviene revisar algunos post donde se habla de ella o se utiliza. Para ello se puede seguir el enlace que se ha indicado al inicio de este artículo.

Como vamos a tratarla como una función matricial hemos de seguir los tres pasos típicos de toda función matricial. Para familiarizarnos con esto se aconseja ver el siguiente artículo:

Funciones matriciales en Excel

Primero, señalamos el rango C12:C16, que es donde la función matricial dejará su resultado.

Segundo, escribimos la función matricial que empleamos en este caso, que es la siguiente:

=INDIRECTO(C11)

Tercero, para validar no pulsamos Enter, sino que hemos de pulsar tres teclas simultaneamente: CONTROL+MAYUCULAS+ENTER.

Resultado

Si todo ha ido bien hemos conseguido un desplegable donde al elegir el año la tabla de abajo toma los datos correspondientes a ese año de la tabla de arriba. Con ello, hemos conseguido disponer de un desplegable que permite traernos los datos que deseamos.

Esto con tablas de datos realmente grandes puede llegar a ser muy interesante.

Hoja2

Proponemos un segundo método que incluso puede ser mejor que el primero por ser más sencillo.

Consiste en utilizar como desplegable una celda con Validación de datos de tipo LISTA.

En el siguiente vídeo puede ver el proceso de creación por los dos métodos.

El 10%

Podemos crear fórmulas matriciales que hagan referencia a todo un rango o matriz. Veamos cómo se calcula el 10% del rango C12:C16.

Al variar los datos con el desplegable de la celda C11 nuestro 10% también varía.

Para saber más

Puede consultar el siguiente enlace.

• Selección de Gráficos