TABLAS DINÁMICAS.

Poder analizar todos los datos puede ayudarle a tomar mejores decisiones empresariales. Pero a veces es difícil saber por dónde empezar, especialmente cuando tiene muchos datos. Excel puede ayudarle recomendándole y, a continuación, creando automáticamente tablas dinámicas, que son una gran forma de resumir, analizar, explorar y presentar los datos.

1. Asegúrese de que los datos tienen encabezados de columna o encabezados de tabla y que no hay ninguna fila en blanco.
2. Haga clic en cualquier celda del rango de celdas o la tabla.
3. Haga clic en Insertar > Tablas dinámicas recomendadas.
   
4. En el cuadro de diálogo Tablas dinámicas recomendadas, haga clic en cualquier diseño de tabla dinámica para obtener una vista previa y, a continuación, seleccione el que muestra los datos en la forma que desea.
   
5. Haga clic en Aceptar.
   Excel coloca la tabla dinámica en una nueva hoja de cálculo y muestra la Lista de campos para que pueda reorganizar los datos de la tabla dinámica según sea necesario.
6. Para centrarse en los datos y organizarlos de la manera que desee, puede filtrar y ordenar la tabla dinámica. Vea Filtrar los datos en una tabla dinámica y Ordenar los datos en una tabla dinámica.
   En el ejemplo que se muestra a continuación, Etiquetas de fila se filtra para mostrar únicamente las ventas de las regiones Sur y Oeste.
   
   NOTAS: 
   • El uso de una tabla dinámica recomendada es una forma rápida de empezar a trabajar en el camino correcto. Sin embargo, todavía puede crear una tabla dinámica vacía para agregar sus propios campos y diseño. Simplemente haga clic en Tabla dinámica en la pestaña Insertar, en vez de en Tablas dinámicas recomendadas.
   • También puede crear una tabla dinámica de datos externos como un origen de datos de procesamiento analítico en línea (OLAP) o basar una tabla dinámica en el modelo de datos para poder analizar datos en varias tablas.
   • En vez del Asistente para tablas y gráficos dinámicos que podía usar en versiones anteriores de Excel, usará los comandos Tabla dinámica o Tablas dinámicas recomendadas de la cinta de opciones para crear tablas dinámicas recomendadas. Sin embargo, si echa de menos el asistente, todavía está disponible. Puede agregarlo a la barra de herramientas de acceso rápido o presionar Alt, D, P para iniciarlo.

GRÁFICOS DINÁMICOS.

Puede ser difícil obtener una visión global cuando los datos están en un gráfico dinámico grande o cuando la mayoría de los datos son complejos e incluyen texto y números con encabezados de columna, como el siguiente:

Con un gráfico dinámico le será más fácil darle sentido a los datos. Aunque un gráfico dinámico muestre las series de datos, las categorías y los ejes de gráficos igual que un gráfico estándar, también incluye controles de filtrado interactivos en el mismo gráfico, lo que le permite analizar rápidamente un subconjunto de datos.

En los casos de datos de hojas de cálculo, puede crear un gráfico dinámico sin crear primero una tabla dinámica. Puede incluso crear un gráfico dinámico recomendado para sus datos. Excel creará automáticamente un gráfico dinámico acoplado. Para ello, haga lo siguiente:

1. Haga clic en cualquier punto de los datos.
2. En la pestaña Insertar, en el grupo Gráficos, seleccione Gráficos recomendados.
   
3. En la ficha Gráficos recomendados, seleccione cualquier gráfico que tenga el icono de gráfico dinámico  en la esquina superior. Aparecerá una vista previa del gráfico dinámico en el panel Vista previa.
   
4. Cuando encuentre el gráfico dinámico que le guste, haga clic en Aceptar.

NOTA:  Si no encuentra un gráfico dinámico que le interese, haga clic en Gráfico dinámico en la pestaña Insertar, en vez de hacer clic en Gráficos recomendados.

1. En el gráfico dinámico que aparezca, haga clic en cualquier control interactivo y después seleccione las opciones de ordenación o de filtrado que desee.
   
   SUGERENCIA:  Para eliminar un gráfico dinámico que ya no quiera, selecciónelo y después presione Eliminar.

Otras formas de crear gráficos dinámicos

Si ya tiene una tabla dinámica, puede basar un gráfico dinámico en la tabla dinámica. O si se ha conectado a un origen de datos de modelo de datos o de proceso analítico en línea (OLAP), puede crear un gráfico dinámico independiente y "desacoplado", sin tener que crear una tabla dinámica.

Crear un gráfico dinámico para una tabla dinámica ya existente

Conectarse a datos externos para crear un gráfico dinámico

Usar una conexión de datos externos para crear un gráfico dinámico

Crear un gráfico dinámico para una tabla dinámica ya existente

1. Haga clic en cualquier punto de la tabla dinámica para que aparezcan las herramientas de la misma en la cinta.
   
2. Haga clic en Analizar > Gráfico dinámico.
   
3. En el cuadro de diálogo Insertar gráfico, haga clic en el tipo y en el subtipo de gráfico que desee. Puede usar cualquier tipo de gráfico salvo un gráfico XY (dispersión), un gráfico de burbujas o un gráfico de cotizaciones.
   

Para más información sobre los tipos de gráficos vea Tipos de gráficos disponibles.

1. Haga clic en Aceptar.
2. En el gráfico dinámico que aparezca, haga clic en cualquier control interactivo y después seleccione las opciones de ordenación o de filtrado que desee.

Principio de página

Conectarse a datos externos para crear un gráfico dinámico

1. Haga clic en Datos > Desde otros orígenes y después elija el origen de datos que desee. Por ejemplo, seleccione Desde Analysis Services para conectarse con un archivo de cubos de procesamiento analítico en línea (OLAP).
   
2. Siga los pasos del Asistente para la conexión de datos y haga clic en Finalizar.
3. En el cuadro de diálogo Importar datos, seleccione Gráfico dinámico y la ubicación en la que desee poner los datos, y después haga clic en Aceptar.
   Aparecerá un gráfico dinámico vacío y la lista de campos para que pueda agregar o cambiar el orden de los campos del gráfico dinámico.
4. En la Lista de campos, seleccione los campos que quiere que aparezcan en el gráfico dinámico.

1. Después de crear un gráfico dinámico, puede personalizarlo, al igual que lo haría con cualquier gráfico estándar. Cuando seleccione el gráfico dinámico:
2. Aparecerán dos botones al lado del gráfico para que pueda agregar o cambiar rápidamente elementos del gráfico como títulos o etiquetas de datos o cambiar el estilo o los colores del gráfico dinámico igual que los cambiaría en un gráfico estándar.
   
3. Las Herramientas del gráfico dinámico aparecen en la cinta. En las pestañas Analizar, Diseño y Formato, puede elegir las opciones para trabajar con el gráfico dinámico o personalizarlo.
   

VALIDACIÓN DE DATOS.

La validación de datos en Excel es una herramienta que no puede pasar desapercibida por los analistas de datos ya que nos ayudará a evitar la introducción de datos incorrectos en la hoja de cálculo de manera que podamos mantener la integridad de la información en nuestra base de datos.

Importancia de la validación de datos en Excel

De manera predeterminada, las celdas de nuestra hoja están listas para recibir cualquier tipo de dato, ya sea un texto, un número, una fecha o una hora. Sin embargo, los cálculos de nuestras fórmulas dependerán de los datos contenidos en las celdas por lo que es importante asegurarnos que el usuario ingrese el tipo de dato correcto.

Por ejemplo, en la siguiente imagen puedes observar que la celda C5 muestra un error en el cálculo de la edad ya que el dato de la celda B5 no corresponde a una fecha válida.

Este tipo de error puede ser prevenido si utilizamos la validación de datos en Excel al indicar que la celda B5 solo aceptará fechas válidas. Una vez creada la validación de datos, al momento de intentar ingresar una cadena de texto, obtendremos un mensaje de advertencia como el siguiente:

Más adelante veremos que es factible personalizar los mensajes enviados al usuario de manera que podamos darle una idea clara del problema, pero este pequeño ejemplo nos muestra la importancia de la validación de datos en Excel al momento de solicitar el ingreso de datos de parte del usuario.

El comando Validación de datos en Excel

El comando Validación de datos que utilizaremos a lo largo de este artículo se encuentra en la ficha Datos y dentro del grupo Herramientas de datos.

Al pulsar dicho comando se abrirá el cuadro de diálogo Validación de datos donde, de manera predeterminada, la opción Cualquier valor estará seleccionada, lo cual significa que está permitido ingresar cualquier valor en la celda. Sin embargo, podremos elegir alguno de los criterios de validación disponibles para hacer que la celda solo permita el ingreso de un número entero, un decimal, una lista, una fecha, una hora o una determinada longitud del texto.

Cómo aplicar la validación de datos

Para aplicar la validación de datos sobre una celda específica, deberás asegurarte de seleccionar dicha celda y posteriormente ir al comando Datos > Herramientas de Datos > Validación de datos.

Por el contrario, si quieres aplicar el mismo criterio de validación a un rango de celdas, deberás seleccionar dicho rango antes de ejecutar el comando Validación de datos y eso hará que se aplique el mismo criterio para todo el conjunto de celdas.

Ya que es común trabajar con una gran cantidad de filas de datos en Excel, puedes seleccionar toda una columna antes de crear el criterio de validación de datos.

Para seleccionar una columna completa será suficiente con hacer clic sobre el encabezado de la columna. Una vez que hayas hecho esta selección, podrás crear la validación de datos la cual será aplicada sobre todas las celdas de la columna.

La opción Omitir blancos

Absolutamente todos los criterios de validación mostrarán una caja de selección con el texto Omitir blancos. Ya que esta opción aparece en todos ellos, es conveniente hacer una breve explicación.

De manera predeterminada, la opción Omitir blancos estará seleccionada para cualquier criterio, lo cual significará que al momento de entrar en el modo de edición de la celda podremos dejarla como una celda en blanco es decir, podremos pulsar la tecla Entrar para dejar la celda en blanco.

Sin embargo, si quitamos la selección de la opción Omitir blancos, estaremos obligando al usuario a ingresar un valor válido una vez que entre al modo de edición de la celda. Podrá pulsar la tecla Esc para evitar el ingreso del dato, pero no podrá pulsar la tecla Entrar para dejar la celda en blanco.

La diferencia entre dejar esta opción marcada o desmarcada es muy sutil y casi imperceptible para la mayoría de los usuarios al momento de introducir datos, así que te recomiendo dejarla siempre seleccionada.

Crear validación de datos en Excel

Para analizar los criterios de validación de datos en Excel podemos dividirlos en dos grupos basados en sus características similares. El primer grupo está formado por los siguientes criterios:

• Número entero
• Decimal
• Fecha
• Hora
• Longitud de texto

Estos criterios son muy similares entre ellos porque comparten las mismas opciones para acotar los datos que son las siguientes: Entre, No está entre, Igual a, No igual a, Mayor que, Menor que, Mayor o igual que, Menor o igual que.

Para las opciones “entre” y “no está entre” debemos indicar un valor máximo y un valor mínimo pero para el resto de las opciones indicaremos solamente un valor. Por ejemplo, podemos elegir la validación de números enteros entre los valores 50 y 100 para lo cual debemos configurar del criterio de la siguiente manera:

Por el contrario, si quisiéramos validar que una celda solamente acepte fechas mayores al 01 de enero del 2015, podemos crear el criterio de validación de la siguiente manera:

Una vez que hayas creado el criterio de validación en base a tus preferencias, será suficiente con pulsar el botón Aceptar para asignar dicho criterio a la celda, o celdas, que hayas seleccionado previamente.

Lista de validación de datos

A diferencia de los criterios de validación mencionados anteriormente, la Lista es diferente porque no necesita de un valor máximo o mínimo sino que es necesario indicar la lista de valores que deseamos permitir dentro de la celda. Por ejemplo, en la siguiente imagen he creado un criterio de validación basado en una lista que solamente aceptará los valores sábado y domingo.

Puedes colocar tantos valores como sea necesario y deberás separarlos por el carácter de separación de listas configurado en tu equipo. En mi caso, dicho separador es la coma (,) pero es probable que debas hacerlo con el punto y coma (;). Al momento de hacer clic en el botón Aceptar podrás confirmar que la celda mostrará un botón a la derecha donde podrás hacer clic para visualizar la lista de opciones disponibles:

Para que la lista desplegable sea mostrada correctamente en la celda deberás asegurarte que, al momento de configurar el criterio validación de datos, la opciónCelda con lista desplegable esté seleccionada.

En caso de que los elementos de la lista sean demasiados y no desees introducirlos uno por uno, es posible indicar la referencia al rango de celdas que contiene los datos. Por ejemplo, en la siguiente imagen puedes observar que he introducido los días de la semana en el rango G1:G7 y dicho rango lo he indicado como el origen de la lista.

TABLA DE DATOS.

El ejemplo más sencillo de una tabla de datos es aquél que utiliza una variablepara realizar los cálculos. Supongamos el siguiente escenario:

En este ejemplo estoy realizando una proyección de ventas para el año 2011 basándome en las ventas del año 2010 y esperando una tasa de crecimiento del 2.5%. Lo que deseo hacer es saber cuál sería la proyección de ventas para el 2011 si la tasa de crecimiento fuera diferente. Para hacer este análisis colocaré las tasas de crecimiento que deseo utilizar de la siguiente manera:

Para crear la tabla de datos debo seleccionar el rango de celdas tal como se muestra en la imagen anterior y entonces ir a la ficha Datos, y dentro del grupoHerramientas de datos pulsar el botón Análisis Y si para posteriormente seleccionar la opción Tabla de datos.

Se mostrará el cuadro de diálogo Tabla de datos y en la caja de texto Celda de entrada (columna) se debe seleccionar la celda B2 que es la celda que contiene el porcentaje de crecimiento.

Al hacer clic en el botón Aceptar se llenarán las celdas contiguas a las tasas de crecimiento con el valor de la proyección de ventas correspondiente a cada una de las tasas.

Excel ha creado la tabla de datos en el rango B5:B13 y de esta manera puedo analizar las diferentes proyecciones de ventas para una tasa de crecimiento diferente. Una vez que he terminado de analizar la información, si intento eliminar alguna de las celdas pertenecientes al rango de la tabla de datos, Excel desplegará un mensaje advirtiendo que no se puede cambiar parte de una tabla de datos. Si deseas eliminar la tabla de datos deberás primero seleccionar el rango completo antes de oprimir la tecla suprimir.

SUBTOTALES.

Devuelve un subtotal en una lista o base de datos. Generalmente es más fácil crear una lista con subtotales usando el comandoSubtotales del grupo Esquema de la pestaña Datos de la aplicación de escritorio de Excel. Una vez creada la lista de subtotales, puede modificar la fórmula SUBTOTALES y cambiar la lista.

Sintaxis

SUBTOTALES(núm_función,ref1,[ref2],...)

La sintaxis de la función SUBTOTALES tiene los siguientes argumentos:

• Núm_función    Obligatorio. El número 1-11 o 101-111 que especifica la función que se usará para el subtotal. 1-11 Incluye las filas ocultadas manualmente, mientras que 101-111 las excluye; las celdas filtradas siempre se excluyen.

Núm_función (incluye valores ocultos)	Núm_función (pasa por alto valores ocultos)	Función
1	101	PROMEDIO
2	102	CONTAR
3	103	CONTARA
4	104	MAX
5	105	MIN
6	106	PRODUCTO
7	107	DESVEST
8	108	DESVESTP
9	109	SUMA
10	110	VAR
11	111	VARP

• Ref1     Obligatorio. Es el primer rango o referencia con nombre para el que desea obtener subtotales.
• Ref2,...     Opcional. De 1 a 254 rangos o referencias para los cuales desea calcular el subtotal.

Observaciones

• Si hay otros subtotales dentro de ref1 (o subtotales anidados), estos subtotales anidados se pasarán por alto para no repetir los cálculos.
• Para las constantes núm_función de 1 a 11, la función SUBTOTALES incluye los valores de las filas ocultas por el comandoOcultar filas, situado en el submenú Ocultar y mostrar del comando Formato, en el grupo Celdas de la pestaña Inicio de la aplicación de escritorio de Excel. Use estas constantes para calcular subtotales de los números ocultos y visibles de una lista. Para las constantes núm_función de 101 a 111, la función SUBTOTALES pasa por alto los valores de filas ocultos por el comando Ocultar filas. Use estas constantes cuando solo desee calcular subtotales de los números visibles de una lista.
• La función SUBTOTALES pasa por alto las filas que no incluidas en el resultado de un filtro, independientemente del valor de núm_función que use.
• La función SUBTOTALES está diseñada para columnas de datos o rangos verticales. No está diseñada para filas de datos ni rangos horizontales. Por ejemplo, cuando desea calcular el subtotal de un rango horizontal mediante un núm_función de 101 o superior, como SUBTOTALES(109,B2:G2), si oculta una columna no afecta al subtotal. Sin embargo, si oculta una fila en un subtotal de un rango vertical, sí afectará al subtotal.

FILTRAR DATOS.

Al colocar datos en una tabla, los controles de filtrado se agregan automáticamente a los encabezados de tabla.

Para realizar un filtrado rápido, haga lo siguiente:

1. Haga clic en la flecha  del encabezado de tabla de la columna que desea filtrar.
2. En la lista de texto o números, desactive la casilla (Seleccionar todo) de la parte superior de la lista y, a continuación, active las casillas de los elementos que desea mostrar en su tabla.

SUGERENCIA:  Para ver más elementos en la lista, arrastre el controlador de la esquina inferior derecha de la galería de filtros para ampliarla.

1. Haga clic en Aceptar.

La flecha de filtrado del encabezado de tabla cambia a este icono  para indicar que hay un filtro aplicado. Haga clic en el filtro para cambiarlo o borrarlo.

ORDENAR DATOS.

1. Seleccione una columna de datos numéricos en un rango de celdas o asegúrese de que la celda activa está en una columna de tabla que contiene datos numéricos.
2. En la ficha Datos, en el grupo Ordenar y filtrar, siga uno de estos procedimientos:
   • Para ordenar de números de menor a mayor, haga clic en  Ordenar de menor a mayor.
   • Para ordenar de números de mayor a menor, haga clic en  Ordenar de mayor a menor.

Problema: comprobar que todos los números están almacenados como números    Si los resultados no son los que esperaba, es posible que la columna contenga números almacenados como texto y no como números. Por ejemplo, los números negativos importados de algunos sistemas de contabilidad o los números con un apóstrofo inicial (') se almacenan como texto. Para más información, vea Convertir números almacenados como texto a números.