Mostrando las entradas con la etiqueta Tablas/Listas. Mostrar todas las entradas
Mostrando las entradas con la etiqueta Tablas/Listas. Mostrar todas las entradas

lunes, agosto 03, 2015

Rangos con Tablas en listas desplegables y comboboxes

No me avergüenzo de decir que soy un fanático de las Tablas. Una de las mejores herramientas de Excel, la mejor, tal vez, después de las tablas dinámicas, el Power Query y el PowerPivot.
Una de las mejores características de las tablas es que crean rangos dinámicos en todo objeto que dependa de ellas. Por ejemplo, si creamos un gráfico basado en una tabla cada cambio se reflejará automáticamente en el gráfico



Al crear una tabla Excel le asigna un nombre, por defecto Tabla1, que podemos cambiar para usar algo más significativo. Por ejemplo, rebautizamos a nuestra tabla de ventas con  "tblVentas"

También veremos que Excel la incluye en administrador de nombres como un nombre definido que se refiere al rango de la tabla

Esto nos lleva a concluir que podemos crear rangos dinámicos, como aquellos que usamos en listas desplegables, sin necesidad de echar mano a fórmulas con las funciones DESREF o INDICE. Pero para poder usar las tablas o las columnas de una tabla como rangos dinámicos tendremos primero que crear nombres definidos que se refieran a esos rangos.

A los efectos del ejemplo supongamos dos tablas de datos. Una contiene nombres de continentes y la otra contiene una columna por cada continente donde se encuentran los países del continente

A la tabla de los continentes le damos el nombre "Continente"; a la segunda tabla le damos el nombre "Paises". Para poder usar la columna de los continentes en una lista desplegable con validación de datos tenemos que crear un nombre definido que se refiera al rango de la columna


Hemos creado el nombre definido "lstContinente" que se refiere a la tabla Continente usando el lenguaje estructural de las tablas: =Continente[Continente] (en este caso el nombre de la tabla y el de la única columna coinciden).
Ahora para definir la lista desplegable con validación de datos en la celda B2 usamos el nombre definido "lstContinente"

Para crear la lista desplegable dependiente tendremos que referirnos a la columna de la tabla Paises que coincide con el continente elegido en B2. Para eso creamos el nombre definido "PaisSelec" que se refiere a esta fórmula
=INDIRECTO("Paises["&valdat!$B$2&"]")
donde "valdat" es el nombre de la hoja; es decir, creamos una cadena de texto con el operador & que la función INDIRECTO convierte en rango.




El archivo se puede descargar aquí.

Si queremos evitar los espacios en blanco al final de algunas de las listas (el rango se determina según el tamaño de la tabla, no de una columna en particular), tendremos que crear una Tabla para cada continente. En este caso sólo necesitamos crear el nombre definido que se refiere al rango de la tabla de continentes.


La lista desplegable en la celda B2 la creamos como en el caso anterior. Para la validación de datos en la celda B3 usamos la fórmula =INDIRECTO(B2).

El ejemplo puede descargarse aquí.

También podemos usar esta técnica para poblar comboboxes y listboxes. En este ejemplo creamos un Userform con dos combobox, una para los continentes y el segundo combobox para los países cuyos valores dependerán del continente elegido. Como base vamos a usar el modelo con tablas separadas por continentes.

Creamos el Userform y agregamos dos comboboxes. La lista de valores del primer combobox  (el que muestra los continentes) lo definimos directamente en el cuadro de propiedades del objeto



Como puede verse, sencillamente ponemos el nombre definido que se refiere a la tabla de continentes.
La lista de valores del segundo combobox debe depender del valor seleccionado en el combobox de continentes para lo cual debemos definir un evento Change del combo de continentes.
Hacemos un doble clic al combobox de los continentes lo que abre el módulo del userform y agrega, por defecto, el evento Change del objeto donde ponemos este código

Private Sub cbxContinentes_Change()
    With Me
        .cbxPaises.RowSource = .cbxContinentes.Value
    End With
End Sub


Ahora podemos probar el funcionamiento del Userform y las cos comboboxes seleccionando el Userform en el editor de VB y apretando F5



Ahora que vemos que nuestro código funciona vamos a mejorarlo agregando una línea para limpiar el valor del combobox de países si el usuario cambia el continente antes de cerrar el Userform

Private Sub cbxContinentes_Change()
    With Me
        .cbxPaises.Value = ""
        .cbxPaises.RowSource = .cbxContinentes.Value
    End With
End Sub


Descargar el archivo del ejemplo.

lunes, febrero 23, 2015

Diagrama Gantt con tablas dinámicas

Reconozco que no hay nada novedoso en crear diagramas Gantt con Excel. Este blog aloja varias notas sobre el tema. Las técnicas más comunes para crear un diagrama de Gantt en Excel se basan en formato condicional o en gráficos de barras.

Todos los modelos que he visto hasta ahora, incluyendo los míos, tienen dos inconvenientes:
  • por lo general tienen un solo nivel de actividad, es decir, no se puede descomponer una actividad central en varias sub-tareas;
  • falta de flexibilidad a la hora de agregar o quitar alguna actividad.

El modelo sencillo que muestro en esta nota soluciona estos dos problemas usando tablas dinámicas.

Supongamos que queremos crear un diagrama de Gantt para un proyecto que se compone de 3 etapas. Cada etapa comprende varias actividades.
Empezamos por organizar los datos en una matriz de datos que convertimos en Tabla. Por ejemplo


A partir de esta tabla creamos esta tabla dinámica


Como puede apreciarse los campos de valores están resumidos con distintas funciones (Suma, Max.). Dado que cada registro aparece una única vez en la tabla de valores (existe una única línea para Etapa 1 - Actividad 1), podemos usar cualquier función que resuma valores excepto Contar.

También establecemos formato de fecha para el campo de fechas (Principio).

La ventaja de usar tablas dinámicas es que no necesitamos guardar ningún orden preestablecido al agregar o quitar filas de la tabla de datos. La tabla dinámica siempre estará agrupada jerárquicamente y también tenemos control total del orden.

Ahora tenemos que crear un gráfico dinámico. Los gráficos dinámicos reflejan la tabla dinámica en la cual se basan, no la base de datos de la tabla dinámica


Eligimos el tipo Barra Apilada y obtenemos este resultado


en el cual introduciremos, obviamente, algunas modificaciones. Empezamos por modificar las definiciones de la serie Principio para volverla "invisible" (ver mi prehistórica nota sobre el tema). Luego quitamos los botones y la leyenda. Este video muestra el proceso




A esta altura de los acontecimientos tenemos que corregir dos aspectos del gráfico: el orden de los valores en el eje de las X y el rango de los valores en el eje de las Y (recordemos que estamos usando un gráfico de barras de manera que el eje de las X es el eje vertical y el de las Y es el horizontal)


Si a pesar de las definiciones de Microsoft, el mínimo del eje de valores no es el mínimo del cuadro de valores, tendremos que hacer la corrección manualmente


En cuanto al eje de las categorías, usamos el menú de formato del eje


El último detalle es actualizar la tabla dinámica cada vez que introduzcamos un cambio en la tabla de datos (el gráfico dinámico que la refleja se actualiza junto con la tabla dinámica).
Si queremos evitar tener que hacer la actualización manualmente con cada cambio, podemos crear un evento usando la técnica que muestro en esta nota.

sábado, marzo 29, 2014

Uso de Tablas en Excel - referencias estructuradas

El tema del uso de Tablas (Listas en Excel 2003) no es nuevo en este blog (un clic en la etiqueta Tablas/Listas en la nube de etiquetas les mostrará todas las notas). En mi opinión es una de las mejores innovaciones en Excel en los últimos años. Sin embargo no creo equivocarme si digo que relativamente pocos usuarios sacan provecho de esta herramienta.
Un posible motivo para esta reticencia sea la nueva sintaxis empleada para referenciar las celdas y rangos dentro de la tabla. Al principio esta sintaxis parece un tanto críptica para el usuario habituado a las referencias clásicas "columna/fila" (por ejemplo, A1).

Consideremos esta tabla de ventas por sucursal y zona

informe dinamico sencillo


En la celda G4 usamos esta fórmula para extraer de la tabla las ventas de cierta zona

=SUMAR.SI($C$3:$C$12,G3,$D$3:$D$12)

Ahora convirtamos esta tabla en  una Tabla (cuando uso tabla con minúscula me refiero a un rango normal de datos) y veamos cómo aparecen las referencias en la fórmula

Tabla de datos

La fórmula aparece ahora así

=SUMAR.SI(TablaVentas[Zona],G3,TablaVentas[Ventas])

La sintaxis es evidente:

"TablaVentas" se refiere al nombre de la tabla

Nombre de la tabla

[Zona] es la referencia al campo (columna) que define la zona de cada sucursal; de la misma manera [Ventas] se refiere a la columna de las ventas.

Para referirnos a otros elementos de la tabla, como la fila de los encabezamientos, usamos distintas formas que resumo en esta tabla

Objeto Sintaxis
Referencia a la tabla =TablaVentas
La celda de la fila en la columna =[@Ventas]
Fila de encabezados TablaVentas[#Encabezados]
Toda la tabla =SUMA(TablaVentas[#Todo])
Fila de totales =TablaVentas[[#Totales],[Ventas]]

Una ventaja evidente de esta sintaxis es la claridad. No hace falta ver el rango referenciado para saber qué calcula la fórmula.

Esto nos permite crear fórmulas con mucha facilidad, como podemos ver

escribir fórmula

Al comenzar a escribir la fórmula Excel nos muestra las tablas como un rango definido; pero la mejor parte viene ahora

definicion de formula

 Al poner el "["Excel nos muestra todos los rangos disponibles para nuestra fórmula. Así que si queremos calcular el total de ventas elegimos Ventas, ponemos el "]" para cerrar la definición del campo, cerramos el o los paréntesis necesarios y apretamos Enter. 

Para referirse a una celda dentro de la Tabla, Excel usa la notación [@nombre del campo]. Por ejemplo, si tenemos una columna de descuentos en nuestra tabla y queremos calcular las ventas neto

referencia a celda dentro de Tabla


Más sobre la sintaxis y el uso de referencias  estructuradas pueden leer en esta nota del sitio Office de Microsoft.

miércoles, diciembre 11, 2013

Reportes dinámicos con segmentación de datos en Tablas Excel

¿Se acuerdan de la funcionalidad Segmentación de Datos? Microsoft introdujo en Excel 2010 esta funcionalidad para las tablas dinámicas. La herramienta permite filtrar reportes de tablas dinámicas con ciertas ventajas sobre el filtro tradicional.

En Excel 2013 también las Tablas cuentan con esta herramienta. Esto abre la posibilidad de crear reportes dinámicos que pueden ser muy útiles en presentaciones de datos. Veamos este reporte hecho con esta herramienta

reporte Excel dinamico con tablas












Como pueden ver en la animación, la tabla oculta todas las filas no relevantes a la elección que efectuamos en los cuadros de segmentación. La ventaja de este método sobre el tradicional Autofiltro, es que vemos las elecciones que hemos efectuado. Además cuando elegimos un valor los cuadros de segmentación oscurecen todos los valores que dejan de ser relevantes.

La mejor parte de todo esto es que es muy fácil crear estos reportes.

Empezamos por un rango que contiene datos de ventas, que hemos convertido en Tabla.

Nuestra tabla tiene fechas por día, pero en nuestro informe queremos seleccionar por años y meses. Las Tablas no tienen la funcionalidad Agrupar de las tablas dinámicas, así que tendremos que dar un rodeo. A la derecha de la columna "Enviado" agregamos las columnas "Año" y "Mes". Los valores los generamos con las funciones AÑO y MES, obviamente

tabla Excel


Ahora tenemos que hacer un poco de espacio por encima y a la izquierda de la tabla para poder ubicar los cuadros de segmentación de datos. Al seleccionar alguna celda de la tabla, se hace visible el menú Herramientas de Tabla donde activamos la segmentación de datos

cuadros de segmentacion de datos


Elegimos los campos con los que queremos filtrar nuestro informe y apretamos Aceptar. Excel crea los cuadros

cuadros de segmentacion sin formato


Nuestra próxima tarea es cambiar el diseño de los cuadros de segmentación. Para hacer esto seleccionamos un cuadro de segmentación para hacer visible el menú de Herramientas de Segmentación

segmentacion de datos


Elegimos un estilo de la galería de estilos y lo aplicamos a los cuadros. Si ninguno de los estilos nos satisface, podemos duplicar y modificar alguno de los estilos existentes

Aplicar formato en segmentos de datos


Otra herramienta importante en el menú es el grupo de comandos Botones. Aquí podemos definir el número de columna que tendrá el cuadro (por defecto tiene una sola), el ancho y el alto

Agregar columnas en segmentos de datos


Finalmente ocultamos las columnas Mes y Año, ubicamos los cuadros en las posiciones que nos parezcan convenientes y quitamos las líneas de cuadrícula de la hoja (Vista-Mostrar)

El resultado final

informe dinamico


El archivo se puede descargar aquí.

viernes, octubre 26, 2012

Registrar entradas con fórmulas – una aclaración

En la nota sobre registro de entradas con fórmulas en Excel, sugerí el uso de Tablas para evitar tener que copiar las fórmulas con cada entrada.

Sin embargo, y como me señalan varios de mis lectores, el uso de tablas en este caso puede dar resultados inesperados. Veamos:



Al introducir el segundo nombre nos hemos movido con las flechas y el resultado es 0, que por el formato fecha-hora vemos como 00/01/1900 00:00:00.

Sin embargo, si nos movemos con la tecla TAB veremos que el resultado es el correcto:



En conclusión: podemos usar un rango común de Excel, copiando las fórmulas o convertir el rango en Tabla y movernos dentro de esta con la tecla TAB.

martes, octubre 23, 2012

Registrar fecha y hora de una entrada en Excel con fórmulas

Poniéndome al día con los 196 mails acumulados durante mis vacaciones, me encuentro con esta consulta:

quiero capturar la hora y la fecha del momento que ingresar este información, la función HOY() y AHORA() no sirven por que estas se actualizan cada vez que ingreso datos en la hoja; es necesario para mi que estas queden estáticas para realizar posteriores cálculos y estadísticas con ellas…

Mi primera respuesta fue que la única forma de hacerlo es programando un evento (macro). Pero después de investigar un poco el tema descubrí (y muchos otros antes de mi) que puede hacerse con fórmulas.

Para hacerlo con fórmulas tenemos que crear una referencia circular, tema mencionado tangencialmente en la prehistoria de este blog en esta nota.

En pocas palabras, ¿qué es una referencia circular? Cuando una fórmula incluye una referencia a la misma celda que la contiene o una celda que se refiere a ésta, Excel genera una advertencia de "referencia circular".

En este ejemplo, queremos calcular la ganancia neta que incluye el pago de comisiones que a su vez son calculadas en base a la ganancia neta, creándose así una referencia circular (la celda B3 contiene la fórmula =B4*15% y la celda B4 la fórmula =B1-B2-B3 que se refiere a la celda B3)








Al apretar Aceptar veremos



Para que Excel pueda resolver el cálculo tenemos que habilitar el cálculo iterativo en Opciones de Excel-Fórmulas


Después de habilitar el cálculo iterativo Excel muestra el resultado


Una vez definido el cálculo iterativo podemos usar esta fórmula para crear una registro de fecha y hora ("timestamp" en inglés)

=SI(A2<>"",SI(B2="",AHORA(),B2),"")



Para evitar tener que copiar la fórmula a lo largo de la columna A, transformamos el rango en Tabla (en un rango definido como Tabla las fórmulas y formato de las columnas son copiadas automáticamente)



26/10/2012 - Aclaración importante sobre el uso de Tablas en este modelo.

En la próxima nota veremos como hacerlo programando un evento (macros).

domingo, julio 29, 2012

Listas desplegables con contenido condicional.

Supongamos una lista de artículos que contiene dos columnas: el código del artículo y el estatus (activo o inactivo). ¿Cómo hacemos para crear una lista desplegable que muestre solamente los artículos activos?

Con anterioridad a la introducción de las “listas” en Excel 2003, luego rebautizadas “tablas” en Excel 2007/10, lo hubiera hecho con una macro. Posiblemente extrayendo los valores que cumplen con el criterio usando Filtro Avanzado y creando un nombre que se refiera a ese rango dinámicamente. Finalmente, usaríamos una macro para automatizar el proceso.

Pero podemos aprovechar las funcionalidades de las tablas para crear un modelo sin macros.

Supongamos que esta es nuestra lista



Vamos a usar una variante de la técnica que mostré en la nota que trató sobre cómo agregar valores únicos a una lista desplegable.

Empezamos por convertir la matriz en “tabla”



Ahora agregamos una columna auxiliar (“Aux1”) con esta fórmula

=SI(B2="Activo",A2,"")



El próximo paso es crear la columna “Aux2” que contiene la fórmula

=SI(CELDA("contenido",C2)="","",FILA())

Lo que hace esta fórmula es evaluar si el resultado de la fórmula es vacío (la celda no está vacía; contiene una fórmula); en caso afirmativo da un resultado vacío, en casi negativo muestra el número de código del artículo.



En esta columna obtenemos un número de orden para los artículos con estatus “activo”.

Finalmente creamos la columna auxiliar “Lista” donde aparecen los artículos activos ordenados por orden de aparición en la tabla. Esto lo hacemos con la fórmula

=INDICE($C$2:$C$15,COINCIDIR(K.ESIMO.MENOR($D$2:$D$15,FILA()-1),$D$2:$D$15))



Como estamos trabajando con una tabla, al agregar un nuevo artículo todas las fórmulas son copiadas automáticamente.

Lo último que nos queda por hacer es crear una nombre que se refiera dinámicamente al rango de la columna Lista en la tabla que no contiene valores #NUM!.

En el administrador de nombres definimos el nombre “ListaArticulos” que se refiere a la fórmula

=DESREF(articulos!$E$2,0,0,SUMAPRODUCTO(--NO(ESERROR(Tabla1[Lista]))),1)



Ahora podemos crear la lista desplegable usando Validación de Datos—Lista



Este video muestra como se adapta el contenido de la lista desplegable a los cambios en la tabla



El archivo con el ejemplo se puede descargar aquí.

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.

sábado, mayo 05, 2012

Uso de tablas en hojas protegidas

No es necesario que nos extendamos sobre las bondades del uso de Tablas (Listas en Excel 2003). Sin embargo existe un inconveniente que aún no ha sido tratado por Microsoft. En una hoja protegida las tablas dejan de expandirse automáticamente.

En general usamos tablas para introducir datos en una base de datos plana y que los objetos que hayamos creado a partir de la tabla (gráficos por ejemplo) se adapten automáticamente y/o para evitar la necesidad de copiar fórmulas a lo largo de una columna cuando agregamos filas. En este tipo de situaciones no existe una necesidad real de proteger la hoja. La necesidad puede surgir si tenemos una o más columnas en la tabla con fórmulas y queremos evitar que el usuario las pueda modificar o queremos ocultarlas.

Si bien el menú de protección incluye las posibilidades Insertar Columnas e Insertar Filas,



la tabla dejará de agregarlas automáticamente en una hoja protegida.

Una solución posible es agregar filas en la tabla de antemano. Esto es relativamente razonable si el modelo tiene lógicamente un número definido de filas (por ejemplo, comparación de ventas-plan por mes de un año determinado).

Una solución más dinámica es usar una macro, más precisamente un evento. Supongamos este modelo



El rango A1:E8 lo hemos definido como tabla y debe expandirse automáticamente a medida que agregamos datos. La tabla H1:I6 contiene el plan de ventas mensual de las sucursales y la usamos en la fórmula de la columna D de la tabla

=BUSCARV([@Sucursal],tblPlanMensual,2,0)

Para que la tabla se autoexpanda, también en una hoja protegida, abrimos el módulo del editor Vba de la hoja (clic con el botón derecho a la pestaña de la hoja)



y ponemos este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    If Target.Row = 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    With ActiveSheet
        .Unprotect
        .ListObjects("Tabla1").Resize Target.CurrentRegion
        .Protect
    End With
 
End Sub


Explicación del código:
Las tres primeras líneas del código interrumpen el evento si:

“If Target.Count > then Exit Sub” – el rango seleccionado incluye más de una celda.

“If Target.Row = 1 Then Exit Sub” - si la fila es 1

“If Target.Column <> 1 Then Exit Sub” – si la columna no es A.

El resto del código quita la protección (.Unprotect), expande el rango de la tabla (.Resize Target.CurrentRegion) y vuelve a proteger la hoja.

“Tabla1” es el nombre de la tabla, visible en el menú Tabla cuando activamos alguna de sus celdas



Es una buena práctica cambiar el nombre por defecto (Tabla1) por un nombre más significativo. El cuaderno con el código puede descargarse aquí.