Supongo que para muchos de mis lectores éstas son noticias de ayer: Microsoft ha puesto a disposición de los usuarios la nueva versión del paquete Office, el Office 365.
Como es de esperar el paquete incluye la nueva versión de Excel – Excel 2013. Microsoft ofrece también la posibilidad de descargar el paquete en sus distintas configuraciones (tomar en cuenta que se requiere Windows 7 o superior).
Para descargar el paquete hay que abrir una cuenta en Office 365.
No he probado aun la nueva versión, lo que iré haciendo en los momentos libres en las próximas semanas.
Como ya sabemos, la función SUBTOTALES nos permite realizar cálculos con once operaciones distintas sin tomar en cuenta celdas ocultas. Esto es muy práctico cuando usamos Autofiltro
El resultado de la fórmula refleja sólo los valores de las filas visibles. Un lector me pregunta si se puede hacer el cálculo de tal manera que SUBTOTALES sólo tome en cuenta los valores positivos (o negativos, o distintos de cero, es decir, aplicando algún criterio al cálculo).
Una forma de hacerlo sería agregando una columna auxiliar y luego aplicar el filtro a la columna del criterio y a la columna auxiliar. Por ejemplo
También se puede hacer con fórmulas, que es lo que vamos a mostrar en esta nota. La ventaja de hacerlo con fórmulas es que podemos mostrar distintos resultados, por ejemplo el total de los montos positivos y el total de los negativos, en una misma vista de la hoja.
Supongamos que queremos ver el total de los montos positivos en la celda B1 y el total de los negativos en D1
Al filtrar por el criterio “a”, por ejemplo, veremos
subtcrit04
Analicemos la fórmula:
DESREF(B4,FILA($B$4:$B$14)-FILA($B$4),,1) crea una matriz que contiene los valores del rango B4:B14
La expresión ($B$4:$B$14<0) crea una martiz de valores FALSO o VERDADERO
Al multiplicar ambas expresiones entre sí obtenemos un matriz donde los valores negativos han sido remplazados por ceros
Usamos estas expresiones como argumentos de la función SUBTOTALES con el operador 9 (suma).
La función SUMAPRODUCTO opera en forma matricial, por lo que nos permite realizar todas estas operaciones en una única celda.
Para calcular el subtotal de los valores negativos usamos ($B$4:$B$14<0); si quisiéramos hacer cálculos que no incluya ceros usaríamos ($B$4:$B$14<>0).
Ambas fueron muy populares, con más de 120000 vistas y 250 comentarios. Hasta hoy en día sigo recibiendo comentarios y consultas relacionados con el tema de las notas. Una de las consultas más corrientes es como construir el modelo pero usando el control combobox en lugar de validación de datos, que es la técnica que muestro en esas notas.
Hay varias razones para usar el control combobox en lugar de listas desplegables de validación de datos. Una de ellas es la posibilidad de usar la propiedad de autocompletar del combobox. Otra es el hecho de que con validación de datos, la opción se hace evidente sólo cuando se elige la celda que la contiene. Sólo cuando seleccionamos la celda que contiene la lista veremos la flecha que nos permite desplegar la lista de opciones. En cambio el cuadro combinado (combobox) es un objeto visible permanentemente.
Empecemos por recordar que Excel cuenta con dos colecciones de objetos que pueden ser incluidos en una hoja: controles de formulario y controles ActiveX
Los controles de Formulario son más fáciles de implementar, pero no nos sirven para nuestro modelo ya que no aceptan nombres que se refieren a rangos y tampoco se pueden programar.
El primer paso es crear los nombres definidos que alimentan las listas desplegables. A los efectos de este ejemplo, los rangos serán estáticos. En un modelo más avanzado crearíamos nombres definidos con rango dinámicos.
La forma más práctica de crear nombres definidos con rangos estáticos es usar la funcionalidad “Crear desde la selección”
En la hoja “Ciudades” los rangos tienen tamaños distintos por lo que usaremos un pequeño truco para no tener que definir cada nombre por separado. En la hoja “Ciudades” tenemos de hecho una tabla donde los nombres de los países figuran en la primer fila. El proceso es el siguiente:
Elegimos una de las celdas de la tabla (en nuestro ejemplo A1)
Apretamos Ctrl + * para seleccionar todo el rango de la tabla
Accionamos F5 (Ir A), apretamos el botón Especial y seleccionamos la opción Constantes-Texto. Esto hace que sólo las celdas que contienen los nombres de las ciudades sean seleccionadas.
Finalmente usamos “Crear desde la selección-Fila superior”
Este video muestra el proceso
Ahora tenemos que insertar los controles en la hoja. Elegimos el control cuadro combinado (combobox) de la colección ActiveX y lo insertamos en la hoja
Al insertar el objeto en la hoja se activa al modo Diseño; en este estado, con el sontrol seleccionado, abrimos el menú Propiedades para definir dos propiedades el control: Linked Cell y ListFIllRange
LinkedCell es la celda donde aparecerá el resultado de la elección; en este control ponemos A2. ListFillRange es el rango que contiene los valores de la lista desplegable (en nuestro caso el nombre definido Continentes)
Una vez definidas las propiedades apretamos el icono Diseño para pasar a la situación normal de uso del control. Ahora podemos elegir un contienente y éste se registrara en la celda A2
Como explicamos en las notas anteriores, los nombres definidos no aceptan espacios entre las palabras. Por lo tanto usamos la función SUSTITUIR para transformar el resultado del control al nombre definido. En la celda A4 ponemos
=SUSTITUIR(A2," ","_")
Antes de agregar un nuevo control para la lista desplegable de los países definimos el nombre “continente elegido” que se refiere a esta fórmula:
=INDIRECTO(eleccion!$A$4)
En las propiedades del control definimos:
LinkedCell: A6
ListFillRange: continente_elegido
De esta manera la lista de las ciudades depende del continente elegido y el país elegido se registra en la celda A6
Nos resta insertar el control para elegir las ciudades. Empezamos por poner la fórmula
=SUSTITUIR(A6," ","_")
en la celda A8 para transformar la elección del cuadro combinado en el nombre definido que contiene las ciudades del país elegido.
Luego creamos el nombre definido “país_elegido” que se refiere a la fórmula:
=INDIRECTO(eleccion!$A$8)
Ahora definimos las propiedades del control:
LinkedCell: A10 (o cualquier otra celda donde queramos que aparezca la ciudad)
ListFillRange: pais_elegido
Un último toque es programar un evento que limpie el contenido de las combobox cuando se cambia la elección del continente. En el módulo de la hoja del editor Vbe ponemos este código
Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.
