Cómo se crea una Tabla dinámica

Las Tablas dinámicas sirven para resumir tablas de datos y obtener subtotales, promedios, porcentajes, máximos, mínimos... Pero, antes de hacer nada debemos tener muy claro lo que deseamos obtener. Por ejemplo, partimos de una tabla de los años 2007 a 2012 con los Beneficios mensuales y los gastos de Compras, Impuestos e I+D.

Nuestro objetivo es hallar los totales de cada año en los cuatro conceptos descritos. Algo semejante a esto:

 Con Tabla dinámica resolveremos este problema en unos pocos segundos. Hacemos clic en cualquier celda de la tabla de datos y accedemos a Insertar + Tabla dinámica. En el cuadro de diálogo correspondiente observaremos que Excel ha seleccionado todo el rango de la tabla en el apartado Tabla o rango. Podemos poner la tabla dinámica en nuestra hoja de cálculo o en otra nueva. En nuestro ejemplo, la dejaremos en nuestra propia hoja a partir de la celda I5. 

Cuando pulsemos Aceptar se abrirá un panel en la parte derecha de la pantalla y un recuadro donde irá la Tabla dinámica.

De acuerdo con el esquema que hemos creado a mano para saber donde deben ir las cosas, bastará arrastrar cada campo al lugar que le corresponda. Así, Año lo arrastraremos al apartado Etiquetas de fila; Compras, I+D, Impuestos y Beneficios los arrastraremos al apartado ∑ Valores. El orden en que los pongamos en dicho apartado será el mismo que muestre la Tabla dinámica. En el apartado Etiquetas de columna se mostrará automáticamente ∑ Valores.

La tabla está creada. Ahora podemos personalizarla tanto como se nos ocurra, pero la tabla está creada y no nos ha costado mas que unos pocos segundos. ¡Y no hemos puesto ninguna fórmula!

Si nos interesan únicamente los años pares, abrimos la lista Etiquetas de fila y desmarcamos los años impares:

Resultado:

Las filas muestran las sumas de los diferentes conceptos acumulando las cantidades de todos los meses de los años pares.

Con esta tabla no podemos conocer el total de los Beneficios habidos en el año 2008 durante los meses de enero, febrero y marzo. Para conseguirlo necesitaríamos un filtro que nos permitiera eliminar los meses no deseados. Eso es tan sencillo como arrastrar el campo Mes al apartado Filtro de informe.

Ahora, abrimos la lista que ha aparecido en la parte superior de la Tabla dinámica y dejamos marcados únicamente los tres primeros meses. Como es lógico, la nueva tabla tiene valores más bajos.

Si intercambiamos el campo Mes y el campo Año, el filtro general permitirá elegir los años y las etiquetas de las filas serán los meses (que también podremos filtrar).

Partiendo de esta situación, nos interesa agrupar la tabla por trimestres. ¿Cómo se hace?

Seleccionamos los tres primeros meses (enero, febrero y marzo) y, en el menú contextual, elegimos la opción Agrupar.

Enero, febrero y marzo se agrupan dentro del apartado Grupo1. La expresión Grupo1 no nos dice nada, así que la sustituimos por Trimestre1. Repetimos el mismo proceso con los meses restantes agrupándolos de tres en tres y sustituyendo las etiquetas Grupo2, Grupo3 y Grupo4 por Trimestre2, Trimestre3 y Trimestre4, respectivamente.

En el panel de la derecha ha aparecido un nuevo campo, llamado Mes2, aunque sería más adecuado llamarle Trimestre. Para cambiar el nombre, hacemos clic con el botón derecho en Trimestre1 y, en el menú contextual,  elegimos Configuración de campo.

En el apartado Nombre personalizado, cambiamos Mes2 por Trimestre y pulsamos Aceptar. El cambio se reflejará en el panel de la derecha.

Se pueden hacer muchísimas cosas, pero terminaremos este artículo viendo cómo se cambia el formato por defecto de la Tabla. Para ello, accedemos a Herramientas de tabla dinámica + Diseño y elegimos un estilo en el grupo Estilos de tabla dinámica. También podemos ir a Herramientas de tabla dinámica + Diseño + Diseño de informe y elegir, por ejemplo, Mostrar en forma de esquema. Si queremos mostrar la suma de los conceptos en cada trimestre, iremos a Herramientas de tabla dinámica + Diseño + Subtotales y elegiremos Mostrar todos los subtotales en la parte superior del grupo (o en la inferior). Para poner puntuación de miles en los números, seleccionamos J6:N22, pulsamos Ctrl + 1 para abrir la ventana Formato de celdas y, en la categoría Número, ponemos cero decimales y separador de miles. Marcamos Filas con bandas y Columnas con bandas. Terminamos desmarcando Ver en el apartado Líneas de cuadrícula del grupo Opciones de hoja de la pestaña Diseño de página.

Si se modifica algún valor de la tabla de datos, la Tabla dinámica no refleja el cambio. Hay que actualizarla manualmente abriendo el menú contextual y eligiendo Actualizar.

Descargar archivo (ST001-Tablasd.xlsx)

Listas aleatorias (2 de 2)

Continuando con el artículo anterior, vamos a ordenar aleatoriamente una lista de nombres utilizando una macro. Nos servirá la macro empleada en la entrada Números aleatorios no repetidos, aunque habrá que hacer algunas adaptaciones.

Para comenzar, preparamos la lista de nombres en la "Hoja3":

Las columnas E, F y G serán columnas auxiliares. La lista aleatoria la pondremos en la columna C.

Necesitaremos pasarle a la macro la cantidad de nombres que tiene la lista. Por ello, la primera operación consistirá en realizar este cálculo y almacenarlo en E2.

En E2:

=CONTARA(B:B)-1     [Resultado: 8]

Accedemos al editor de VBA pulsando Alt + F11, seleccionamos Insertar + Módulo y escribimos el siguiente procedimiento:

Salimos del editor pulsando Alt + Q.

Para ejecutar esta macro lo más cómodo es poner un botón en la hoja. Se puede usar un Botón de Formulario (Programador + Insertar + Botón (control formulario)), un Botón ActiveX (Programador + Insertar + Botón de comando (control ActiveX)) o una forma cualquiera; por ejemplo, un Rectángulo redondeado.

Si elegimos la última opción, tendremos que acceder a Insertar + Formas + Rectángulo redondeado y dibujar un rectángulo en algún sitio cómodo de la hoja, como la celda C1. Redondeamos los laterales y, en Herramientas + Formato + Estilos de forma, escogemos el estilo que más nos guste. En el menú contextual, elegimos Modificar texto y escribimos: Nueva lista

Volvemos a abril el menú contextual, seleccionamos Asignar macro y, en el cuadro de diálogo correspondiente, seleccionamos Números_no_repetidos. Terminamos pulsando el botón Aceptar.

El botón ya es plenamente operativo. Hacemos clic sobre él y en la columna F aparecerá una lista de números no repetidos comprendidos entre 1 y 100000. 

Ahora, asignamos a cada número un orden jerárquico. Lo haremos en la columna G.

En G2:

=SI.ERROR(JERARQUIA.EQV(F2;$F:$F;1);"")     [Extendemos la fórmula hasta la fila 30]

Si prevemos que la lista va a ser muy grande, habrá que extender la fórmula mucho más abajo.

En C2:

=SI.ERROR(INDICE($B$2:$B$200;G2);"")     [Extendemos la fórmula hasta la fila 30]

Cada vez que pulsemos el botón, Excel devolverá una nueva lista. Si añadimos nuevos nombres, serán incorporados a la lista aleatoria en la próxima pulsación. Naturalmente, también podemos eliminar nombres.

En apariencia, la columna G es superflua. Podríamos haber diseñado la macro de manera que generara números aleatorios no repetidos comprendidos entre 1 y el valor de la celda E2. De ese modo, conseguiríamos directamente en la columna F una lista aleatoria similar a la que hemos obtenido en la columna G. 

La macro sería ésta:

También habría que modificar ligeramente la fórmula de C2:

En C2:

=SI(F2<>"";INDICE($B$2:$B$200;F2);"")

La hoja quedaría de este así:

Si esta macro resuelve el problema con una columna auxiliar menos, ¿no sería lógico utilizarla y descartar la anterior?

La verdad es que la última macro puede dar problemas cuando la lista tiene muchos nombres. Supongamos que la lista tiene 1.000 nombres. El bucle Do While... Loop debe generar 1.000 números al azar comprendidos entre 1 y 1.000, todos diferentes y guardarlos en la matriz número(). Se comprende que muchas veces el número generado ya exista y, en consecuencia, tendrá que descartarlo y volver a generar otro. Sin embargo, si le damos la opción de generar 1.000 números elegidos entre un abanico de números muchísimo mayor (por ejemplo, entre 1 y 100.000), la posibilidad de que un número generado no haya salido anteriormente será mucho mayor y los descartes serán, en consecuencia, mucho menores. El tiempo de ejecución de la macro disminuirá en la misma proporción. Por si esto no fuera suficiente, la segunda macro se cuelga cuando se trabaja con listas grandes.

La conclusión es clara: si no quieres problemas, usa la primera macro.

¿Qué ocurre si en la lista hay nombres repetidos? Nada raro. Cualquiera de las dos macros genera números diferentes, por lo que la función INDICE apuntará siempre a filas distintas. A esta función no le importa lo que haya escrito en una celda; simplemente, lo lee y lo copia en la columna C.

Descargar archivo (SB001-Azar.xlsm)

Descargar archivo (SB001-Azar2.xlsm)

Listas aleatorias (1 de 2)

El problema de hoy consiste en ordenar alatoriamente una lista de nombres. Excel dispone de dos funciones muy parecidas, ALEATORIO y ALEATORIO.ENTRE, que generan números al azar. En el primer caso, los números generados están comprendido entre 0 y 1. En el segundo, entre los dos valores que fijemos. En este ejercicio, emplearemos la función ALEATORIO.

Dada una lista de nombres (C1:C9), queremos que Excel los ordene al azar cada vez que pulsemos la tecla F9.

En la columna B, con la función ALEATORIO, creamos una serie de números aleatorios comprendidos entre 0 y 1. ¿Por qué en la columna B? Pronto comprenderemos la razón.

En B2:

=ALEATORIO()     [Extendemos la fórmula hasta la fila 9]

ALEATORIO es una función volátil, de modo que cada vez que pulsemos F9 o hagamos algún cambio en la hoja, se recalculará y los valores de la columna B cambiarán.

La lista aleatoria la crearemos en tres pasos y la pondremos en la columna D. Usaremos las columnas F y G como columnas auxiliares, que eliminaremos una vez creada la fórmula definitiva.

Primero, ordenamos los números aleatorios de la columna B de menor a mayor. Esto se hace con la función K.ESIMO.MENOR. Cuando pongamos la fórmula, los valores de la columna B se recalcularán y aparecerán otros diferentes.

En F2:

=K.ESIMO.MENOR($B$2:$B$9;FILA(A1))     [Extendemos la fórmula hasta la fila 9]

Ahora, usando la función BUSCARV, extraemos de la matriz B2:C9 los nombres de la columna C cuyo valor asociado (columna A) coincide con el de la columna F. La función BUSCARV realiza la búsqueda en la primera columna de la matriz. Ésta es la razón por la que hemos puesto los números aleatorios en esa posición, a la izquierda de los nombres, en la columna B.

En G2:

=BUSCARV(F2;$B$2:$C$9;2;FALSO)     [Extendemos la fórmula hasta la fila 9]

Excel ha hecho un nuevo recálculo. La lista de la columna G es una lista aleatoria. Si queremos otra, pulsamos F9 o modificamos alguna celda.

Finalmente, creamos la fórmula compuesta en D2 y borramos las columnas F y G.

En D2:

=BUSCARV(K.ESIMO.MENOR($B$2:$B$9;FILA(A1));$B$2:$C$9;2;FALSO)     [Extendemos la fórmula hasta la fila 9]

¿Qué ocurre si no es posible poner la columna de números aleatorios a la izquierda de la columna de nombres? La respuesta es bien sencilla: no se podrá utilizar la función BUSCARV. Habrá que idear algún truco, como crear los números aleatorios en una zona vacía de la hoja y copiar la columna de nombres a su derecha.

Otra solución es utilizar la función INDICE. Veamos cómo.

Nuestra lista de nombres está en la columna B y, por el motivo que sea, no podemos escribir nada en la columna A.

 La lista de números aleatorios la crearemos en la columna E.

En E2:

=ALEATORIO()     [Extendemos la fórmula hasta la fila 9]

Clasificamos de menor a mayor los valores de la columna E usando la función JERARQUIA.EQV (en Excel 2003 hay que usar JERARQUIA).

En G2:

=JERARQUIA.EQV(E2;$E$2:$E$9;1)     [Extendemos la fórmula hasta la fila 9]

Con INDICE ponemos en la columna H el nombre de la columna B que ocupa la posición indicada en la columna G.

En H2:

=INDICE($B$2:$B$9;G2)     [Extendemos la fórmula hasta la fila 9]

Agrupamos todas las fórmulas en la columna C y borramos las columnas G y H.

En C2:

=INDICE($B$2:$B$9;JERARQUIA.EQV(E2;$E$2:$E$9;1))     [Extendemos la fórmula hasta la fila 9]]

Son posibles otras fórmulas; por ejemplo:

En C2:

=INDICE($B$2:$B$9;COINCIDIR(K.ESIMO.MENOR($E$2:$E$9;FILA(A1));$E$2:$E$9;0))

Esta fórmula devuelve una lista diferente de la anterior, pero también es totalmente aleatoria.

Sin embargo, las fórmulas usadas aquí tienen un fallo. Aunque es poco problable en listas pequeñas como la del ejercicio, podría darse el caso de que dos números aleatorios fueran exactamente iguales. En ese caso, uno de los nombres se repetiría. Por ejemplo, consideremos que ALEATORIO ha devuelto una lista de números en los que coinciden los correspondientes a las celdas E4 y E7. El resultado será:

La columna G tendrá dos valores iguales, lo que hará que la fórmula devuelva dos veces el mismo nombre (Carlos). Otro (Iker) habrá desaparecido de la lista aleatoria (columnas C y H).

La solución en el próximo artículo.

Descargar archivo (SB001-Azar.xlsx)

Insertar comillas automáticamente

Con Formato personalizado, sin necesidad de escribirlos, podemos insertar diferentes caracteres delante o detrás de los números y textos. El juego de caracteres depende de la fuente que elijamos. Para los siguientes ejemplos, usaremos la fuente Arial.

Si delante de todos los números positivos queremos que se inserte automáticamente el carácter ♥ pondremos el siguiente formato personalizado: [Azul]♥ #.###,00. El carácter ♥ lo extraeremos de Insertar + Símbolo y lo copiaremos en el portapapeles para poder usarlo en la fórmula. En este caso, hemos elegido el color azul.

Este formato se aplicará tanto a los números positivos como a los negativos. La razón es que el formato personalizado consta de cuatro apartados: el primero, para los los números positivos; el segundo, para los negativos; el tercero, para el cero, y, el cuarto, para los textos. Si sólo se pone formato a los positivos, todos los números adoptan el mismo formato. En una entrada antigua se explica con más detalle la manera de usar los formatos personalizados.

 

Naturalmente, podemos asociar un carácter distinto a los negativos y otro al cero. Valga como ejemplo el siguiente formato: [Azul]♥ #.###,00;[Verde]♦ -#.###,00;[Rojo]☼ "Nulo" ☼

 

En A3, el valor introducido es un cero, pero el formato lo convierte en ☼ "Nulo" ☼. Cervantes es un texto y, como no hemos utilizado el cuarto argumento, adopta el formato por defecto.

 

Si queremos formatear una celda de manera que lo que escribamos quede encerrado entre signos de interrogación, el formato será: ;;;"¿"@"?"

 

La frase que hemos escrito ha sido: Qué hora es. Sin embargo, debido al formato aplicado, se verá: ¿Qué hora es? No hemos puesto formato para los números, de modo que si escribimos un número, no se verá.

 

Como es lógico, el formato ;;;"¡"@"!" encerrará la frase entre signos de admiración. De ese modo, la frase Menuda fiesta, se verá así: ¡Menuda fiesta!

 

Siguiendo con la misma lógica, si quisiéramos encerrar una frase entre comillas el formato debería ser: ;;;"""@""". Sin embargo, esto no funciona. Podemos añadir nuevas comillas para ver si solucionamos el problema... pero seguirá sin funcionar.

 

Para incorporar comillas, éstas deben ir precedidas por la barra invertida (\). El formato correcto es: ;;;\"@\"

 

Una expresión que incluyera formato para números y texto podría ser: [Azul]♥ #.###,00;[Verde]♦ -#.###,00;[Rojo]☼ "Nulo" ☼;[Color7]\"@\"

 

Si combinamos el formato personalizado con el formato condicional, las posibilidades son numerosísimas.

 



Recortar una imagen y eliminar el fondo

La edición y manipulación de imágenes no es el punto fuerte de las hojas de cálculo; para ese trabajo hay programas específicos y bien conocidos. Sin embargo, con Excel se pueden hacer algunas modificaciones que, en casos sencillos, nos permiten prescindir de los programas especializados.

Hoy vamos a ver cómo se elimina el fondo de una imagen para quedarnos con la parte que nos interese. Si en una fotografía hay varias personas y sólo nos interesa una, tendremos que eliminar las restantes y borrar el fondo. La siguiente, es una fotografía doble de Usain Bolt tomada durante una visita a Brasil.

Nos interesa sólo la parte de la derecha, pero no queremos ni el fondo ni las imágenes de las dos personas que se ven en el vértice inferior derecho. Queremos que el resultado sea:

 

Primero incorporamos la imagen a la hoja con Copiar y Pegar o mediante Insertar + Imagen. Luego, hacemos clic en ella para seleccionarla y elegimos Herramientas de imagen + Formato + Recortar. En los vértices de la imagen aparecerán unas escuadras negras y en el medio de cada lado unas barritas del mismo color.

 

Para facilitar el trabajo, eliminaremos la parte izquierda de la fotografía, desplazando la barra del borde izquierdo hasta la línea de separación de las dos fotografías (este paso no es imprescindible).

 

Hacemos clic en Quitar fondo. La barra de herramientas ha cambiado y parte de la fotografía ha quedado de color violeta. Ésta es la parte de la imagen que va a desaparecer.

 

Naturalmente, no queremos que desaparezcan trozos de Usain Bolt; por tanto, debemos recuperar algunas partes de la fotografía y eliminar otras. Pero, antes debemos ampliar el recuadro que ha aparecido, de forma que en su interior esté contenido lo que queremos conservar.

 

La zona violeta ha cambiado. El brazo izquierdo, el balón y las imágenes del vértice inferior derecho se han recuperado. Tendremos que recuperar manualmente el brazo derecho y eliminar las imágenes del vértice inferior. Para ello, usaremos los botones de la barra Herramientas de imagen + Formato.

 

Para recuperar el brazo derecho, hacemos clic en Marcar las áreas para mantener. Una vez elegida la herramienta, hacemos clic en el punto que queremos recuperar o, mejor aún, trazamos una línea en el antebrazo derecho y otra en el resto del brazo. Del mismo modo, recuperaremos todo los que haga falta, como el borde de la camiseta.

 

Haciendo clic en Marcar las áreas para quitar, eliminamos las figuras inferiores.

 

Para terminar, pulsamos el botón Mantener cambios.

 

Para trabajar en zonas pequeñas es conveniente aplicar el zoom a 200 o 400. De este modo, tendremos un control más preciso. Si nos equivocamos, usaremos el botón Eliminar marca.

 

Ya podemos mover la imagen a la zona deseada, reducirla o ampliarla y aplicarle los efectos que queramos. Por ejemplo, abrimos la lista Estilos de imagen en Herramientas de imagen + Formato y vamos probando las diversas opciones. Si elegimos "Girado, blanco", obtendremos:

 

¡Cuasi perfecto!

 

 

Atajos para cambiar el formato

He descubierto de forma casual que ciertas combinaciones de teclas cambian el formato de las celdas. Quizás estos atajos estén documentados, pero yo lo desconozco.

Seleccionamos el rango A1:A4 y pulsamos Ctrl + 1 para acceder a la ventana Formato de celdas (o elegimos Formato de celdas en el menú contextual). En la ficha Número, elegimos la opción Personalizada y escribimos el formato siguiente: [Azul]0;[Rojo]-0;[Verde]"Cero";[Color10]"...?"

Ahora, escribimos unos cuantos datos en la columna A:

 

Con el formato que hemos puesto, se verán así:

 

Para poner formato numérico con dos decimales, con el rango A1:A4 seleccionado, pulsamos Ctrl + Mayús + 1 (el 1 del teclado principal). Con esta combinación se eliminan los colores, los números se muestran en formato de miles con dos decimales y los textos aparecen tal cual.

 

Para establecer el formato de moneda pulsamos Ctrl + Mayús + 4 (el 4 del teclado principal). El símbolo de la moneda depende de cada país. Manzana es un texto y no se ve afectado.

 

Para poner porcentajes pulsamos Ctrl + Mayús + 5. Como es lógico, el texto sigue sin cambiar.

 

Para poner formato científico la combinación es Ctrl + Mayús + 7. 

 

¿Tiene interés práctico todo esto? Hummm...