como se aplica en un ejercicio

Una empresa de exportación de máquinas agrícolas tiene la siguiente tabla en una Hoja de Excel 2007.donde figuran los datos del 1º trimestre del año

a partir de ella se quiere crear una nueva tabla en la que se informe la cantidad de maquinarias exportadas y el detalle de cuantas se vendieron de cada una.

Para crear la tabla que nos responda a estas preguntas, nos ubicamos en cualquier celda de la tabla, luego vamos a la pestaña "insertar" panel "Tablas"

en el que pulsamos en "Tabla dinámica", al hacer esto aparece un menú en el que tenemos las opciones de "Gráfico dinámico" y "Tabla dinámica" , pulsaremos en este último como se puede ver

a continuación aparece el panel "Crear tabla dinámica"

en este caso el rango de la tabla base (tabla de partida) queda automáticamente seleccionado, incuyendo los rótulos, también seleccionamos "Nueva hoja de cálculo" y  pulsamos en aceptar y automáticamente se crea la Hoja4 en la que se destacan:

1-Una nueva cinta de opciones denominada "Herramienta de tabla dinámica"

2-Un panel llamado "Lista de campos de tabla dinámica"  que es una novedad de Excel 2007 y que tiene un rectángulo en la parte superior, donde se ubican los campos o rótulos de la tabla de origen, también hay cuatro rectángulos, en la parte inferior, denominados " Filtro de informe", "Rótulos de columna", "Rótulos de fila" y  "Valores" donde irán apareciendo los rótulos de la tabla a medida que los seleccionemos en la parte suprior en forma de botones como el que se nuestra

Los botones se pueden arrastrar de un rectángulo a otro aunque los rótulos que tienen valores   numéricos, siempre aparecen en rectángulo  "Valores".                           

como se ve, hasta este momento, tiene las casillas de verificación de rótulos sin marcar , pues bien, es justamente seleccionar las casillas  "MAQUINA"   y   "CANTIDAD"  lo debemos hacer en el próximo paso

                                                                                                                                                             Observar que aparecen automáticamente 2 botones.

Listo ya tenemos la primera tabla con las respuestas pedidas recuadradas en rojo

si nos interesara saber solamente el dato de cuantas fertilizadora y sembradora se exportaron, junto con su total, tenemos un comando de filtrado en la parte superior y así obtenemos la siguiente tabla

luego de aplicar el filtro.

Este es un ejemplo didáctico para hacer una introducción, pero se le puede sacar mucho mas el jugo a esta herramienta.

Podemos querer saber el detalle de las máquinas que fueron exportadas y por cual vendedor. En este caso tendremos que seleccionar la casilla del rótulo VENDEDOR y en la nueva Hoja aparece una tabla y el panel   "Lista de campos de tabla dinámica"

se ve que en los rectángulos,  mas precisamente en el llamado Rótulo de fila, aparece un nuevo botón, el botón  VENDEDOR  en forma simultánea a la la selección de la casilla de verificación VENDEDOR.

La tabla responde a lo que queremos saber, pero le podemos dar otro aspecto arrastrando el botón VENDEDOR al rectángulo  "Rótulo de columna"

y la tabla queda como la que esta abajo , luego de haberle dado algo de formato

En esta tabla se puede ver, por ejemplo, que Peña vendió 16 fertilizadoras y un tractor.

Sería interesante saber el número de maquinarias exportadas a que país y por cual vendedor.

Para hacer lo , verificamos la casilla "País" y filtramos MAQUINA

y se genera la tabla

donde se puede ver que Chuan le vendió 30 máquinas a China.

Hasta ahora nuestra tabla dinámica efectúa sumas, pero puede hacer otras operaciones tales como porcentajes, máximos, mínimo y otras mas que iremos viendo.

Podemos preguntarnos cual fue la máxima cantidad de maquinarias que vendió Peña. Para hacer esto nos ubicamos en una celda cualquiera de la tabla de arriba y apretando el botón derecho del mouse aparece el siguiente menú emergente

en el que pulsamos en "Configuración de campo de valor", como indica la flecha, con lo que aparece el panel también llamado "Configuración de campo de valor"

en el que si vamos a la pestaña "Resumen por" están las opciones de resumen en las que elegiremos Máx y  luego de aceptar, la tabla se transforma en

que nos dice que la cantidad Máxima de maquinarias que vendió Peña es 9, como se ve en el recuadro rojo, en forma adicional podemos ver que esta cantidad fue vendida a Brasil ( verificar con la tabla de partida o tabla base)

Este resultado se puede ver con una simple inspección de los datos, que en este caso son  tres, pero cuando estos aumentan es donde vemos la utilidad del cálculo de un máximo.

  

¿QUE SON LAS TABLAS DINÁMICAS? ¿PARA QUE SIRVEN?

Las Tablas Dinámicas son una forma alternativa de presentar o resumir los datos de una lista, es decir, una forma de ver los datos desde puntos de vista diferentes. El nombre Tabla Dinámica se debe a que los encabezados de fila y columna de la lista pueden cambiar de posición y también pueden ser filtrados. Con las Tablas Dinámicas también podremos preparar los datos para ser utilizados en la confección de gráficos.

Es verdad que podes formar muy buenos reportes con lo que ya sabes de Excel, pero imagina la siguiente situación. Ya creaste un gran reporte que compara el total de ventas en las diferentes regiones del país, pero ahora tus superiores te pudieron que hagas otro reporte que compare las ventas de cada región con los tipos de productos vendidos. Y por si fuera poco, después de terminar el segundo reporte, te piden un tercero para comparar las ventas de los productos pero ahora por cada ciudad del país. Es muy probable que tengas que empezar desde cero para crear los nuevos reportes. Afortunadamente Excel tiene la funcionalidad de tablas dinámicas que ayuda a resolver este problema. Al utilizar una tabla dinámica podrás crear los reportes sin escribir una sola fórmula, pero lo más notable será que podrás arreglar el reporte de una manera dinámica de acuerdo a tus necesidades.

TABLA DINAMICA

Ejercicio BUSCARV

FUNCION BUSCARV EXCEL

FUNCION BUSCARV EXCEL

REPASO

Subtotales

Subtotales

Subtotales, detallas su uso explicando las distintas opciones de su ventana.

La función subtotales en Excel nos ayuda a calcular el subtotal de una lista de valores. Lo interesante es que podemos indicar a la función SUBTOTALES el tipo de operación que deseamos aplicar sobre los valores.

Sintaxis de la función subtotales

• Núm_función (obligatorio): Valor numérico (del 1 al 11) que indica el tipo de función que debe ser utilizada en el cálculo como SUMA, CONTAR, PROMEDIO, etc.
• Ref1 (obligatorio): El rango que contiene los valores.
• Ref2 (opcional): Un segundo rango con valores adicionales a considerar. Este argumento es opcional y hasta el argumento 254 para proveer rangos adicionales.

A continuación la tabla de valores numéricos que indican las operaciones que deseamos realizar con la función subtotales:

Ejemplo de la función subtotales

Tengo una lista de valores en el rango A1:A10 y deseo utilizar lafunción subtotales para calcular el promedio para lo cual utilizaré la siguiente fórmula:

Observa que al especificar el número 1 como el primer argumento de la función subtotales estamos indicando que se utilice la función PROMEDIO.  Si por otro lado quisiera obtener la suma de dos listas de números puedo utilizar la función SUBTOTALES de la siguiente manera:

Filas ocultas con la función subtotales

Un posible inconveniente de la función subtotales es que al momento de ocultar alguna de las filas que tienen valores incluidos en la operación se tiene un impacto en el resultado. En la siguiente imagen puedes observar un ejemplo donde hago una cuenta de los elementos del rango A1:A10

.

Sin embargo, si oculto las filas 4 y 5, la función subtotales no refleja ningún cambio y sigue tomando en cuenta las filas ocultas:

Si queremos pasar por alto los valores ocultos podemos hacer uso de un identificador de función diferente para el primer argumento de la función subtotales:

Para el ejemplo anterior, si utilizamos el valor 102 en lugar de 2 como el primer argumento de la función subtotales obtendremos el siguiente resultado:

En este caso la función subtotales ha pasado por alto los valores de las filas ocultas. Es importante mencionar que esta funcionalidad de no tomar en cuenta los valores ocultos solamente funcionará con filas ocultas y no con columnas ocultas.
Recuerda que la función subtotales en Excel siempre aplicará la función indicada sobre los rangos de celdas proporcionados como argumentos.

Ordenar los datos

Ordenar los datos es una parte esencial del análisis de datos. Puede que desee poner una lista de nombres en orden alfabético, compilar una lista de niveles de inventario de productos de mayor a menor u ordenar filas por colores o por iconos. Ordenar los datos ayuda a verlos y a comprenderlos mejor, así como a organizarlos y encontrarlos más fácilmente y a tomar decisiones más eficaces.

 NOTA   Para buscar el valor superior o inferior de un rango de celdas o de una tabla, por ejemplo las 10 mejores calificaciones o las 5 peores cifras de ventas, use Autofiltro o el formato condicional. Para obtener más información, vea Filtrar datos de un rango o una tabla y Agregar, cambiar o borrar formatos condicionales.

Puede ordenar datos por texto (A a Z o Z a A), números (de menor a mayor o de mayor a menor) y fechas y horas (de más antiguos más recientes y de más recientes a más antiguos) en una o varias columnas. También puede ordenar por una lista personalizada (como Grande, Medio y Pequeño) o por formato, incluyendo el color de celda, el color de fuente o el conjunto de iconos. La mayoría de las operaciones de ordenación son ordenaciones de columnas, pero también es posible ordenar por filas.

Los criterios de ordenación se guardan en el libro para que pueda volver a aplicarlos cada vez que lo abra para una tabla de Excel, pero no para un rango de celdas. Si desea guardar los criterios de ordenación para poder volver a aplicar periódicamente una ordenación al abrir un libro, entonces lo mejor es utilizar una tabla. Esto es especialmente importante para ordenaciones de varias columna o para las que se tarda mucho tiempo en crear.

Ordenar texto

1. Seleccione una columna de datos alfanuméricos en un rango de celdas o asegúrese de que la celda activa está en una columna de tabla que contiene datos alfanuméricos.
2. En la ficha Inicio, en el grupo Modificar, haga clic en Ordenar y filtrar.

3. Siga uno de los procedimientos siguientes:
• Para ordenar en orden alfanumérico ascendente, haga clic en Ordenar de A a Z.
• Para ordenar en orden alfanumérico descendente, haga clic en Ordenar de Z a A.
4. También puede ordenar distinguiendo las mayúsculas de las minúsculas.

Cómo ordenar distinguiendo las mayúsculas de las minúsculas

Problema: comprobar si todos los datos están almacenados como texto    Si la columna que desea ordenar contiene números almacenados como números y números almacenados como texto, será necesario que les aplique formato de texto. Si no lo hace, los números almacenados como números se ordenarán antes que los números almacenados como texto. Para aplicar formato de texto a todos los datos seleccionados, en la fichaInicio, en el grupo Fuente, haga clic en el botón Formato de fuente de celda, haga clic en la ficha Número y, en Categoría, haga clic en Texto.

Problema: quitar los espacios a la izquierda    En algunos casos, los datos que se hayan importado de otra aplicación pueden tener insertados espacios a la izquierda delante de los mismos. Antes de ordenar los datos, quite estos espacios.

Ordenar números

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 Inicio, en el grupo Modificar, haga clic en Ordenar y filtrar y, a continuación, siga uno de los procedimientos siguientes:
• Para ordenar de números más bajos a números más altos, haga clic en Ordenar de menor a mayor.
• Para ordenar de números más altos a números más bajos, 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 le ofrecen lo 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 obtener más información, vea Convertir números almacenados como texto a números.

Ordenar fechas u horas

1. Seleccione una columna de fechas u horas en un rango de celdas o asegúrese de que la celda activa está en una columna de tabla que contiene fechas u horas.
2. Seleccione una columna de fechas u horas en un rango de celdas o en una tabla.
3. En la ficha Inicio, en el grupo Modificar, haga clic en Ordenar y filtrar y, a continuación, siga uno de los procedimientos siguientes:
• Para ordenar de una hora o fecha anterior a una posterior, haga clic en Ordenar de más antiguos a más recientes.
• Para ordenar de una hora o fecha posterior a una anterior, haga clic en Ordenar de más recientes a más antiguos.

Problema: comprobar que las fechas y las horas están almacenadas como fechas y horas    Si los resultados obtenidos no son los que esperaba, es posible que la columna contengan fechas u horas almacenadas como texto y no como fechas u horas. Para que Excel las ordene correctamente, todas las fechas y las horas de una columna deben estar almacenadas como números de serie de fecha u hora. Si Excel no puede reconocer un valor como fecha u hora, significa que la fecha o la hora está almacenada como texto. Para obtener más información, vea Convertir fechas almacenadas como texto en fechas.

 NOTA    Si desea ordenar por días de la semana, aplique formato a las celdas para mostrar el día de la semana. Si desea ordenar por el día de la semana independientemente de la fecha que sea, conviértalos en texto utilizando la función TEXTO. Sin embargo, la función TEXTO devuelve un valor de texto y, por tanto, la operación de ordenación estará basada en datos alfanuméricos. Para obtener más información, vea Mostrar la fecha como día de la semana.

Ordenar por color de celda, color de fuente o icono

Si ha aplicado formato manual o condicionalmente a un rango de celdas o a una columna de tabla, por color de celda o color de fuente, también puede ordenar por estos colores. Además, puede ordenar por un conjunto de iconos creado mediante un formato condicional.

1. Seleccione una columna de datos en un rango de celdas o asegúrese de que la celda activa está en una columna de tabla.
2. En el grupo Modificar de la ficha Inicio, haga clic en Ordenar y filtrar y, a continuación, en Personalizar ordenación.

Se mostrará el cuadro de diálogo Ordenar.

3. En Columna, en el cuadro Ordenar por, seleccione la columna que desea ordenar.
4. En Ordenar según, seleccione el tipo de ordenación. Siga uno de los procedimientos siguientes:
• Para ordenar por color de celda, seleccione Color de celda.
• Para ordenar por color de fuente, seleccione Color de fuente.
• Para ordenar por un conjunto de iconos, seleccione Icono de celda.
5. En Criterio de ordenación, haga clic en la flecha situada junto al botón y, a continuación, dependiendo del tipo de formato, seleccione un color de celda, un color de fuente o un icono de celda.
6. En Ordenar, seleccione el modo en que desea ordenar. Siga uno de los procedimientos siguientes:
• Para mover el color de celda, el color de fuente o el icono a la parte superior o a la izquierda, seleccione En la parte superior para una ordenación de columnas y A la izquierda para una ordenación de filas.
• Para mover el color de celda, el color de fuente o el icono a la parte inferior o a la derecha, seleccione En la parte inferior para una ordenación de columnas y A la derecha para una ordenación de filas.

 NOTA   No hay ningún color de celda, color de fuente ni criterio de ordenación de iconos predeterminados. Debe definir el orden que desea para cada operación de ordenación.

7. Para especificar el siguiente color de celda, color de fuente o icono por el que desea ordenar, haga clic enAgregar nivel y, a continuación, repita los pasos del tres al cinco.

Asegúrese de que selecciona la misma columna en el cuadro Luego por y de que realiza la misma selección enOrdenar.

Repita el procedimiento por cada color de celda, color de fuente o icono adicionales que desea incluir en la ordenación.

Ordenar por una lista personalizada

Puede utilizar una lista personalizada para ordenar en un orden definido por el usuario. Excel ofrece listas personalizadas integradas de días de la semana y de meses del año, y puede además crear sus propias listas.

1. También puede crear una lista personalizada.

Cómo crear una lista personalizada

1. Seleccione una columna de datos en un rango de celdas o asegúrese de que la celda activa está en una columna de tabla.
2. En el grupo Modificar de la ficha Inicio, haga clic en Ordenar y filtrar y, a continuación, en Personalizar ordenación.

Se mostrará el cuadro de diálogo Ordenar.

3. En Columna, en el cuadro Ordenar por o en Luego por, seleccione la columna que desea ordenar mediante una lista personalizada.
4. En Ordenar, seleccione Lista personalizada.
5. En el cuadro de diálogo Listas personalizadas, seleccione la lista que desee. En el ejemplo anterior, haría clic en Alto, Medio, Bajo.
6. Haga clic en Aceptar.

Ordenar filas

1. Seleccione una fila de datos en un rango de celdas o asegúrese de que la celda activa está en una columna de tabla.
2. En el grupo Modificar de la ficha Inicio, haga clic en Ordenar y filtrar y, a continuación, en Personalizar ordenación.

Se mostrará el cuadro de diálogo Ordenar.

3. Haga clic en Opciones.
4. En el cuadro de diálogo Opciones de ordenación, en Orientación, haga clic en Ordenar de izquierda a derecha y, a continuación, en Aceptar.
5. En Columna, en el cuadro Ordenar por, seleccione la fila que desea ordenar.
6. Siga uno de los procedimientos siguientes:

Por valor    

1. En Ordenar según, seleccione Valores.
2. En Criterio de ordenación, siga uno de los procedimientos siguientes:
• Para los valores de texto, seleccione A a Z o Z a A.
• Para los valores numéricos, seleccione De menor a mayor o De mayor a menor.
• Para los valores de fecha y hora, seleccione De más antiguos a más recientes o De más recientes a más antiguos.

Por color de celda, color de fuente o icono de celda     

1. En Ordenar según, seleccione Color de celda, Color de fuente o Icono de celda.
2. Haga clic en la flecha situada junto al botón y, a continuación, seleccione un color de celda, un color de fuente o un icono de celda.
3. En Ordenar, seleccione A la izquierda o A la derecha.

 NOTA    Al ordenar filas que forman parte de un esquema de hoja de cálculo, Excel ordenará los grupos de nivel más alto (nivel 1) de modo que las filas o las columnas de detalle permanezcan juntas, aunque estén ocultas.

Ordenar por más de una columna o fila

Puede ordenar por más de una columna o fila cuando tiene datos que desea agrupar por el mismo valor en una columna o fila y ordenar después otra columna o fila dentro de ese grupo de valores iguales. Por ejemplo, si tiene una columna Departamento y empleado, puede ordenar primero por Departamento (para agrupar a todos los empleados del mismo departamento) y, después, ordenar por nombre (para escribir los nombres en orden alfabético dentro de cada departamento). Puede ordenar hasta un máximo de 64 columnas.

 NOTA   Para obtener resultados óptimos, el rango de celdas que se ordene debe tener encabezados de columna.

1. Seleccione un rango de celdas con dos o más columnas de datos, o asegúrese de que la celda activa se encuentra en una tabla con dos o más columnas.
2. En el grupo Modificar de la ficha Inicio, haga clic en Ordenar y filtrar y, a continuación, en Personalizar ordenación.

Se mostrará el cuadro de diálogo Ordenar.

3. En Columna, en el cuadro Ordenar por, seleccione la primera columna que desea ordenar.
4. En Ordenar según, seleccione el tipo de ordenación. Siga uno de los procedimientos siguientes:
• Para ordenar por texto, número o fecha y hora, seleccione Valores.
• Para ordenar por formato, seleccione Color de celda, Color de fuente o Icono de celda.
5. En Ordenar, seleccione el modo en que desea ordenar. Siga uno de los procedimientos siguientes:
• Para los valores de texto, seleccione A a Z o Z a A.
• Para los valores numéricos, seleccione De menor a mayor o De mayor a menor.
• Para los valores de fecha y hora, seleccione De más antiguos a más recientes o De más recientes a más antiguos.
• Para ordenar según una lista personalizada, seleccione Lista personalizada.
6. Para agregar otra columna por la que ordenar, haga clic en Agregar nivel y, a continuación, repita los pasos del tres al cinco.
7. Para copiar una columna por la que ordenar, seleccione la entrada y, a continuación, haga clic en Copiar nivel.
8. Para eliminar una columna por la que ordenar, seleccione la entrada y, a continuación, haga clic en Eliminar nivel.

 NOTA   Debe conservar al menos una entrada en la lista.

9. Para cambiar el orden en el que se ordenan las columnas, seleccione una entrada y, a continuación, haga clic en la flecha Arriba o Abajo para cambiar el orden.

Las entradas más altas de la lista se ordenan antes que las más bajas.

Ordenar una columna en un rango de celdas sin afectar a las demás

 ADVERTENCIA    Tenga cuidado cuando utilice esta característica. Ordenar por una columna en un rango puede producir resultados que no desea, por ejemplo trasladar celdas de dicha columna lejos de las otras celdas de la misma fila.

 NOTA   El siguiente procedimiento no se puede realizar en una tabla.

1. Seleccione una columna en rango de celdas que contenga dos o varias columnas.
2. Para seleccionar la columna que desea ordenar, haga clic en el encabezado de columna.
3. En la ficha Inicio, en el grupo Modificar, haga clic en Ordenar y filtrar y, a continuación, haga clic en uno de los comandos de ordenación que haya disponibles.
4. Aparecerá el cuadro de diálogo Advertencia antes de ordenar.
5. Seleccione Continuar con la selección actual.
6. Haga clic en Ordenar.
7. Seleccione las demás opciones de ordenación que desea en el cuadro de diálogo Ordenar y, a continuación, haga clic en Aceptar.

Si el resultado no es el que desea, haga clic en Deshacer .

Más información sobre problemas generales de ordenación

Si obtiene resultados inesperados al ordenar sus datos, haga lo siguiente:

Comprobar si los valores que devuelve una fórmula han cambiado    Si los datos que ha ordenado contienen una o más fórmulas, los valores devueltos de dichas fórmulas pueden cambiar cuando se actualiza la hoja de cálculo. En este caso, asegúrese de que vuelve a aplicar la ordenación para obtener resultados actualizados.

Mostrar las filas y las columnas antes de ordenar    Las columnas ocultas no se mueven cuando se ordenan columnas, y las filas ocultas no se mueven cuando se ordenan filas. Antes de ordenar los datos, es una buena idea mostrar las columnas o filas ocultas.

Comprobar la configuración local    Los criterios de ordenación varían según la configuración local. Asegúrese de que la configuración local de la Configuración regional o de la Configuración regional y de idioma delPanel de control de su equipo es correcta. Para obtener información sobre cómo cambiar la configuración local, vea el sistema de Ayuda de Windows.

Especificar encabezados de columna solamente en una fila    Si tiene que utilizar rótulos de varias líneas, ajuste el texto dentro de la celda.

Activar o desactivar la fila de título    Normalmente, lo mejor es tener una fila de título cuando se ordena una columna para facilitar la comprensión del significado de los datos. De manera predeterminada, el valor del título no se incluye en la operación de ordenación. A veces, es posible que tenga que activar o desactivar el título para que el valor del mismo se incluya o no se incluya en la operación de ordenación. Siga uno de los procedimientos siguientes:

• Para excluir la primera fila de datos de la ordenación porque se trata de un encabezado de columna, en la fichaInicio, en el grupo Modificar, haga clic en Ordenar y filtrar, en Orden personalizado y, a continuación, active Mis datos tienen encabezados.
• Para incluir la primera fila de datos en la ordenación porque no es un encabezado de columna, en la ficha Inicio, en el grupo Modificar, haga clic en Ordenar y filtrar, en Orden personalizado y, a continuación, desactive Mis datos tienen encabezados.