TABLAS DINAMICAS

Una tabla dinámica consiste en el resumen de un conjunto de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos. Es dinámica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos, visualizando o no los datos origen, etc...

Para aquellos que tengan conocimientos de Access es lo más parecido a una consulta de referencias cruzadas, pero con más interactividad.

Veamos cómo podemos crear una tabla dinámica a partir de unos datos que ya tenemos.

Para crear una tabla dinámica, Excel nos proporciona el Asistente para tablas y gráficos dinámicos.

Supongamos que tenemos una colección de datos de los artículos del almacén con el número de referencia y el mes de compra, además sabemos la cantidad comprada y el importe del mismo. 

Vamos a crear una tabla dinámica a partir de estos datos para poder examinar mejor las ventas de cada artículo en cada mes. 

 

Para acceder al asistente accedemos al menú Datos - Informe de tablas y gráficos dinámicos.

Aparece la primera pantalla de tres de las que se compone el asistente para tablas dinámicas, donde nos solicita que indiquemos la situación de los datos a analizar y qué tipo de informe vamos a crear. 

 

En nuestro caso indicamos que vamos a seleccionar los de la Lista de Excel y que vamos a crear una Tabla dinámica. En la segunda pantalla seleccionamos el rango dónde tenemos los datos a utilizar en la tabla dinámica.

Debemos seleccionar todas las celdas incluyendo las cabeceras. 

 

A continuación nos aparece el tercer paso del asistente...

 

 

En esta última pantalla debemos indicar dónde vamos a situar el resultado de la tabla dinámica. Hemos escogido situarla en una Hoja de cálculo nueva. 

Si pulsamos sobre Diseño... accedemos a un cuadro de diálogo donde podemos personalizar la forma en que van a verse los datos en la tabla dinámica.

 

En la imagen podemos apreciar que nos aparece una estructura formada por las filas, columnas, datos y además un campo para el nº de página. Y en el lateral derecho tenemos los campos disponibles de la tabla, los cuales podemos arrastrar a las diferentes partes del diagrama de la izquierda.

Por ejemplo podríamos construir una tabla dinámica con la siguiente estructura:

- En el campo Fila arrastramos REF, las referencias de nuestros artículos.

- En el campo Columna arrastramos MES.

- Y en Datos arrastramos IMPORTE

Nos quedaría una estructura como podemos ver en la imagen de abajo.

REFLEXION : Las tablas dinamicas permite comparar valores en gran cantidad.Son muy utiles para adjuntar archivos segun un criterio especificado.

Las tablas dinamicas solo sirven para resumir los datos pero no permite modelarlo 

dentro de una tabla.Permite visualizar diferentes totales,flitrando los datos y visualizar o no los datos de origen.

LISTA, ORDENAMIENTO Y FILTROS

Una lista de Microsoft Excel proporciona características diseñadas para facilitar la administración y el análisis de grupos de datos relacionados en una hoja de cálculo de Excel. Si designa un rango como una lista, podrá administrar y analizar los datos de la lista sin tener en cuenta los datos externos a la lista. Por ejemplo, puede filtrar columnas, agregar una fila para totales, e incluso crear un informe de tabla dinámica utilizando únicamente los datos de la lista.

Puede tener varias listas en una hoja de cálculo, lo que le ofrece una gran flexibilidad para separar los datos en función de sus necesidades, en distintos conjuntos manejables.

 Nota :  No puede crear una lista en un libro compartido. Si desea crear una lista, primero debe dejar de compartir el libro.

Las listas de Excel facilitan el uso compartido de datos entre varios usuarios mediante integración con Microsoft Windows SharePoint Services. Sólo necesita tener la dirección Web y los derechos de creación en el sitio de SharePoint para compartir la lista con el fin de que otras personas puedan verla, editarla y actualizarla. Si elige vincular la lista de Excel a una lista de un sitio de SharePoint, puede sincronizar los cambios con el sitio de SharePoint de forma que los otros usuarios puedan ver los datos actualizados.

Al crear una lista en Excel, las características de la lista y los elementos visuales diseñados para mejorar estas características permiten identificar y modificar fácilmente el contenido de la lista.

Cada columna de la lista tiene el Autofiltro habilitado de forma predeterminada en la fila de encabezado. El Autofiltro permite filtrar u ordenar rápidamente los datos.

El borde de color azul oscuro que rodea a la lista permite distinguir claramente el rango de celdas que la forman.

La fila que contiene un asterisco se denomina fila de inserción. Al escribir información en esta fila, se agregarán los datos automáticamente a la lista y se expandirá el borde de la misma.

Puede agregar una fila Total (fila de total: fila especial de una lista que proporciona una selección de funciones de agregado que son útiles para trabajar con datos numéricos.) a la lista. Cuando haga clic en una celda de la fila Total, estará disponible una lista desplegable de funciones agregadas.

Puede modificar el tamaño de la lista arrastrando el controlador de tamaño de la esquina inferior del borde de la lista.

Botones

Excel le permite ordenar en orden alfabético normal y en orden inverso, mediante los Botones, Orden Ascendente y Orden Descendente. 

Se pueden ordenar las filas completas o solamente las celdas seleccionadas, basado en la primer columna de la selección. Desde luego, si sus datos consisten en filas de hechos relacionados entre sí, ordenar por filas completas es más seguro. De otra manera, sus registros pueden mezclarse fácilmente.

Diálogo

Usar el diálogo Ordenar es más flexible que hacerlo mediante los botones de la barra de herramientas. El diálogo le permitirá seleccionar cuales columna(s) usar como base del ordenamiento.   

Puede establecer Ordenar en  tres niveles. Excel ordenará primero todas las filas que se hayan seleccionado basadas en la primer columna que haya elegido. A continuación, Excel ordena las filas que tengan los mismos valores que esa primer columna a Ordenar, usando la segunda columna que haya elegido. Por último Excel ordena las filas que tengan los mismos primeros y segundos valores de columna, usando la tercer columna que usted eligió. Un ejemplo sería un ordenamiento de tres columnas usado para disponer listas de nombres en orden alfabético. Cuando cada nombre consiste de tres celdas - Apellido - Nombre de pila - Nombre del medio.

Crear Lista 

2. Resalte el rango de datos que desea convertir en una lista.
NOTA:   También puede seleccionar el rango de celdas que desea especificar como una lista en el cuadro de diálogo Crear lista.
2. En el menú Datos, elija Lista y, a continuación, haga clic en Crear lista.
3. Si los datos seleccionados tienen encabezados, active la casilla de verificación La lista tiene encabezados y haga clic en Aceptar.
El indicador de lista resalta el rango de datos seleccionado y se ofrece en la barra de herramientas Lista la funcionalidad más común para listas.
NOTA :  Si no ve la barra de herramientas Lista, seleccione el menú Ver, elijaBarras de herramientas y, a continuación, haga clic en Lista.
Una vez creada la lista, se identificará mediante un borde azul. Además, se habilitarán automáticamente listas desplegables de Autofiltro para cada columna de la lista y se agregará la fila de inserción como última fila de la lista. Si elige agregar una fila Total haciendo clic en Alternar fila Total  en la barra de herramientas Lista, se mostrará una fila Total bajo la fila de inserción.
Si selecciona una celda, fila o columna fuera de la lista, la lista pasará a estar inactiva. Una lista inactiva está rodeada por un borde azul y no muestra la fila de inserción ni las listas desplegables de Autofiltro.
NOTA : Si seleccionó Ocultar el borde de las listas inactivas en el menú Lista, no se mostrará el borde.

Filtrar el contenido de la tabla

Filtrar una lista no es ni más ni menos que de todos los registros almacenados en la tabla, seleccionar aquellos que se correspondan con algún criterio fijado por nosotros.
Excel nos ofrece dos formas de filtrar una lista.

- Utilizando el Filtro (autofiltro).

- Utilizando filtros avanzados.

 Utilizar el Filtro.
Para utilizar el Filtro nos servimos de las listas desplegables asociadas a las cabeceras de campos (podemos mostrar u ocultar el autofiltro en la pestaña Datosmarcando o desmarcando el botón Filtro).


Si pulsamos, por ejemplo, sobre la flecha del campo 1er Apellido, nos aparece un menú desplegable como este, donde nos ofrece una serie de opciones para realizar el filtro.
Por ejemplo, si sólo marcamos Moreno, Excel filtrará todos los registros que tenganMoreno en el 1er apellido y las demás filas 'desaparecerán' de la lista.
Otra opción, es usar los Filtros de texto, donde se despliegan una serie de opciones:

En cualquier opción, accedemos a una ventana donde podemos elegir dos condiciones de filtro de texto, y exigir que se cumpla una condición o las dos. Excel evaluará la condición elegida con el texto que escribamos, y si se cumple, mostrará la fila. Usaremos el carácter ? para determinar que en esa posición habrá un carácter, sea cual sea, y el asterisco * para indicar que puede haber o no un grupo de caracteres.

En el ejemplo de la imagen, solo se mostrarán los registros cuyo 1er Apellido tenga una o en el segundo carácter y no contenga la letra z.
Para indicarnos que hay un filtro activo, la flecha de la lista desplegable cambia de icono.
Para quitar el filtro, volvemos a desplegar la lista y elegimos la opción (Seleccionar Todo), reaparecerán todos los registros de la lista. También podemos quitar el filtro pulsando en Borrar filtro  en la pestaña Datos.
 Utilizar Filtros avanzados.
Si queremos filtrar los registros de la lista por una condición más compleja, utilizaremos el cuadro de diálogo Filtro avanzado. Previamente deberemos tener en la hoja de cálculo, unas filas donde indicaremos los criterios del filtrado.
Si deseas saber más sobre cómo definir criterios de filtrado, haz clic aquí .

Para abrir el cuadro de diálogo Filtro avanzado, pulsaremos en  en la sección Ordenar y filtrar.
Rango de la lista: Aquí especificamos los registros de la lista a los que queremos aplicar el filtro.
Rango de criterios: Aquí seleccionamos la fila donde se encuentran los criterios de filtrado (la zona de criterios).
También podemos optar por guardar el resultado del filtrado en otro lugar, seleccionando la opción Copiar a otro lugar, en este caso rellenaremos el campoCopiar a: con el rango de celdas que recibirán el resultado del filtrado.
Si marcamos la casilla Sólo registros únicos, las repeticiones de registros (filas con exactamente los mismos valores) desaparecerán.
Para volver a visualizar todos los registros de la lista, acceder al menú Datos - Filtro - Mostrar todo.
REFLEXION: 
Los filtros Excel facilitan la busqueda de un subconjunto de datos dentro de un rango para poder trabajar con el mismo. Este subconjunto de datos o rango filtrado muestra las filas que cumplen el criterio que se especifique para una columna.
Al utilizar un filtro este no reorganiza el resultado mostrado, pero sí permite aplicar formato, representar en gráficos e imprimir dicho rango. El filtrado oculta temporalmente las filas que no se desean mostrar.
Excel proporciona dos formas de filtrar celdas de datos:
1. Autofiltro.
2. Filtro avanzado.
Autofiltro

Función SI

Función SI

Devuelve un valor si la condición especificada es VERDADERO y otro valor si dicho argumento es FALSO.

Sintaxis:

=SI(prueba_lógica;valor_si_verdadero;valor_si_falso)

Prueba_lógica: Es cualquier valor o expresión que pueda evaluarse como VERDADERO o FALSO. Por ejemplo, A10=100 es una expresión lógica; si el valor de la celda A10 es igual a 100, la expresión se evalúa como VERDADERO. De lo contrario, se evaluará como FALSO. Este argumento puede utilizar cualquier operador de comparación.

Valor_si_verdadero: Es el valor que se devuelve si el argumento prueba_lógica es VERDADERO. Por ejemplo, si este argumento es la cadena de texto "Dentro de presupuesto" y el argumento prueba_lógica se evalúa como VERDADERO, la función SI muestra el texto "Dentro de presupuesto".

Si el argumento prueba_lógica es VERDADERO y el argumento valor_si_verdadero está en blanco, este argumento devuelve 0 (cero). Para mostrar la palabra VERDADERO, utilice el valor lógico VERDADERO para este argumento. Valor_si_verdadero puede ser otra fórmula.

Valor_si_falso: Es el valor que se devuelve si el argumento prueba_lógica es FALSO. Por ejemplo, si este argumento es la cadena de texto "Presupuesto excedido" y el argumento prueba_lógica se evalúa como FALSO, la función SI muestra el texto "Presupuesto excedido". Si el argumento prueba_lógica es FALSO y se omite valor_si_falso, (es decir, después de valor_si_verdadero no hay ninguna coma), se devuelve el valor lógico FALSO. Si prueba_lógica es FALSO y valor_si_falso está en blanco (es decir, después de valor_si_verdadero hay una coma seguida por el paréntesis de cierre), se devuelve el valor 0 (cero). Valor_si_falso puede ser otra fórmula.


Ejemplo:

Quedando de esta manera:

REFLEXION: La funcion "SI" sirve para crear una operacion basada en el criterio del resultado de otra dando asi una funcion muy importante y facil de usar.Es una funcion un tanto especial por lo que se puede hacer con ella, se puede modificar segun las especificaciones que el usuario necesite para facilitarle la operacion o en si a no escribir tanto,esta funcion tambien permite el uso de comparaciones de rotulos en dos o mas celdas.

GRAFICOS

Supongamos que tenemos la siguiente tabla:

Hay varias formas de crear gráficos en Excel. Por ejemplo se pueden seleccionar todos los datos de la tabla, presionar el asistente de gráficos  y seguir las indicaciones. Sin embargo esta vez vamos a proceder de la siguiente manera:
Sin seleccionar celdas de la tabla presionamos el botón asistente de gráficos  y vamos a completar los 4 pasos de este asistente.

Primero elegiremos el tipo de gráfico y un subtipo que estén de acuerdo a lo que queramos expresar mediante nuestra figura. En nuestro caso elegiremos un gráfico simple de líneas.

El siguiente paso será elegir los datos que participarán de nuestro gráfico. Como no hemos seleccionado previamente ni un rango de datos, vamos a agregar las series una a una. Presionamos la pestaña serie. En esta ventana podemos agregar, quitar o modificar series de nuestro gráfico. Las series corresponden a cada uno de los registros de nuestra tabla, o filas (aunque podemos también hacer que las series sean columnas si es necesario). Si es que no hay ni una serie presionamos el botón agregar. De lo contrario si ya está presente la Serie1 la molificaremos.

Debemos rellenar 3 campos para cada serie.
El campo nombre corresponde al nombre de la serie, en nuestro caso seleccionaremos el campo nombre y en la tabla seleccionaremos la celda donde está el nombre de nuestra serie (por ejemplo Mañío).
El campo valores corresponde a los valores de nuestra serie, seleccionamos las celdas de nuestra tabla donde estén los valores de nuestra serie, sin incluir el nombre, pues ya fue especificado en el campo nombre.
En el campo Rótulos del Eje de Categorías (X), indicaremos cuales serán los rótulos del eje X. Para nuestro caso vamos a seleccionar la fila donde se encuentran los años, que sería lo que debería aparecer en el eje X.

Así sucesivamente podemos ir agregando otras series (quitando o modificando), y el asistente nos irá mostrando un vista previa de como lucirá el gráfico.

El paso 3 nos permite definir entre otras cosas el título del gráfico y el nombre de las unidades de medida de los eje X e Y.

En el último paso debemos decidir si el gráfico aparecerá en la misma hoja donde tenemos la tabla con datos u en otra hoja existente, o si no como una nueva hoja que se podrá acceder a través de las pestañas que están en la parte inferior izquierda del Excel (Hoja1,Hoja2, Hoja3, Grafico1, etc.)

Finalmente nuestro gráfico debería lucir aproximadamente de la siguiente manera:

En el caso de quieres cambiar el tipo de gráfico, o los datos comprometidos, posicionamos el puntero del mouse sobre el gráfico y presionamos el botón derecho, con lo cual aparecerá un menú secundario y luego seleccionaremos Tipo de gráfico para cambiar el tipo de gráfico, o Datos de origen para modificar las series.

Por último hay que agregar que podemos cambiar cualquier aspecto visual (colores, fuentes) del gráfico seleccionando una de sus partes y haciendo doble click sobre dicho componente.

REFLEXION: Las graficas son un componente de excel para crear una representacion de los datos creados en una hoja.Su principal funcion es crear una vista "grafica" por ello el nombre para el usuario.Ademas de que puede agregar algun formato alos ejes de la grafica.
Un ejemplo de grafica es el incremento de precios al mes de algun producto segun el incremento mensual.

Operadores Relacionales

Los operadores relacionales sirven para comparar números. La comparación se realiza y
se devuelve 1 si es cierto (si se cumple) y 0 si no lo es. Por ejemplo:
 
3 < 5 Esto es cierto, entonces devuelve 1
5 < 3 Esto es falso, entonces devuelve 0

Los operadores relacionales son:

• = = igual que se cumple si son iguales
• != distinto que se cumple 1 si son diferentes
• > mayor que se cumple si el primero es mayor que el segundo
• < menor que se cumple si el primero es menor que el segundo
• >= mayor o igual que se cumple si el primero es mayor o igual que el segundo
• <= menor o igual que se cumple si el primero es menor o igual que el segundo

Se debe tener especial cuidado en no confundir el operador asignación con el operador relacional igual a. Las asignaciones se realizan con el símbolo =, las comparaciones con ==.

Por ejemplo, 8>4 (ocho mayor que cuatro) es verdadera, se representa por el valor true del tipo básico boolean, en cambio, 8<4 (ocho menor que cuatro) es falsa, false. En la primera columna de la tabla, se dan los símbolos de los operadores relacionales, el la segunda, el nombre de dichos operadores, y a continuación su significado mediante un ejemplo.
 

Operador	nombre	ejemplo	significado
<	menor que	a<b	a es menor que b
>	mayor que	a>b	a es mayor que b
==	igual a	a==b	a es igual a b
!=	no igual a	a!=b	a no es igual a b
<=	menor que o igual a	a<=5	a es menor que o igual a b
>=	mayor que o igual a	a>=b	a es menor que o igual a b

REFLEXION:

Los operadores racionales de excel sirven para comparar dos rotulos o numeros ya sea por kien es mayor o por si son iguales.En el caso de los rotulos procesador de excel compara :

1.    Numero de caracteres
2.    Si las letras coinciden o si son diferentes, si coincide la primera letra el procesador sigue con la segunda y asi logra saber si son iguales.

En el caso de los numeros el procesador de excel compara ambos numeros y si el primero es mayor k el de el segundo indica "VERDADERO" y si no es asi indica "FALSO"
EJEMPLO:

2>1 =VERDADERO
2<1=FALSO

Que es una funciòn?

Una función es una fórmula creada por Excel que opera sobre uno o más valores (argumentos) en un orden determinado (estructura). El resultado se mostrará en la celda donde se introdujo la formula.

 El tipo de argumento que utiliza una función es específico de esa función. Así, los argumentos pueden ser números, rotulos, valores lógicos como verdadero o falso, matrices, valores de error como #N/A o referencias de celda. Un argumento puede ser una constante, una fórmula o incluso otra función.

Excel cuenta con una gran variedad de funciones dependiendo del tipo de operación o cálculo que realizan. Estas funciones pueden ser matemáticas y trigonométricas, estadísticas, financieras, de texto, de fecha y hora, lógicas, de base de datos, de búsqueda y referencia y de información.

 Estructura de una funciòn

La sintaxis de cualquier función es:

=nombre_funcion(argumento1;argumento2;…;argumentoN)

Esto es:

1. Signo igual (=).
2. Nombre de la función.
3. Paréntesis de apertura.
4. Argumentos de la función separados por puntos y comas.
5. Paréntesis de cierre.

Insertar función

Cuando cree una fórmula que contenga una función, el cuadro de diálogo Insertar función le ayudará a introducir las funciones de la hoja de cálculo. A medida que se introduzca una función en la fórmula, el cuadro de diálogo Insertar función irá mostrando el nombre de la función, cada uno de sus argumentos, una descripción de la función y de cada argumento, el resultado actual de la función y el resultado actual de toda la fórmula.

Para introducir una fórmula, cuenta con dos opciones:

1. Puede escribir la formula directamente en la celda que mostrará el resultado. Si el nombre de la formula es correcto Excel indicará los argumentos de la misma.
2. Puede utilizar la ayuda de Excel (cuadro de diálogo Insertar función), este asistente mostrará el nombre de la función, los argumentos, ademas una descripción de la función y de cada argumento.

Funciones Anidadas

Excel permite el utilizar funciones como uno de los argumentos de otra función, esto se conoce como funciones anidadad. Por ejemplo:

=SI(PROMEDIO(A1:A10)>50;SUMA(B1:B10);0)

La siguiente función SI compara con 50 el resultado producido por la función PROMEDIO (función anidada), si es mayor a 50, invoca a la función SUMA (función anidada).

Resultados válidos

Cuando se utiliza una función anidada como argumento, ésta deberá devolver el mismo tipo de valor que el que utilice el argumento.

Límites del nivel de anidamiento

Una fórmula puede contener como máximo siete niveles de funciones anidadas.

En las secciones siguientes se muestran todas las funciones de las hojas de cálculo por categoría.

En este artículo

Categorias de las funciones de excel

Las funiones de excel se dividen en varias categorias segun la funcion.

• Funciones de complementos y automatización
• Funciones de cubo
• Funciones de fecha y hora
• Funciones de ingeniería
• Funciones financieras
• Funciones de información
• Funciones lógicas
• Funciones de búsqueda y referencia
• Funciones matemáticas y trigonométricas
• Funciones estadísticas
• Funciones de texto

Utilizar la funcion promedio

La función PRMEDIO() sirve para obtener el promedio o media aritmética de un conjunto de valores. Como argumentos puede tener celdas individuales y/o rangos de celdas que tengan valores numéricos.

Ejemplo de operación: Obtener el promedio de 2 valores: 10 y 6

En este caso la funcion promedio se escribiria el la celda A3de la siguiente forma =PROMEDIO(A1,A2)como se ve en la imagen.Los num.ros fueron escritos individualmente se puede ver porque los separa una coma.

En esta imagen el rango esta representado como (A1:A2) y de esta forma el resultado es el mismo.

UTILIZAR DOS FUNCIONES DE EXCEL



SUMA

Descripción:
Suma todos los números de uno o varios rangos de celdas.

Ejemplos de Operación:

Suma de un rango.

=SUMA(A1:A4)

En el ejemplo anterior se sumarán todos los números que se encuentren en el rango A1:A4, es decir A1+A2+A3+A4.

Resultado: A1+A2+A3+A4

Suma de celdas separadas

=SUMA(A1,B2,C3)

En el ejemplo anterior se sumarán los valores de las celdas A1,A2 y A3

Resultado: A1+B2+C3

Suma de varios rangos

=SUMA(A1:A3,B2:B4)

En el ejemplo anterior se sumarán las celdas del rango A1:A3 (A1,A2,A3) más las celdas del rango B2:B4 (B2,B3,B4)

 MULTIPLICACION

La funcion multiplicacion en excel se puede utilizar de diversas maneras:

Se puede representar de dos formas =(5)*(10)  ó  =5*10

La primera manera sirve cuando se necesitan sumar antes de multiplicar.

Por ejemplo:

=(5+1)*(10+1)

REFLEXION: Las funciones son formulas creadas por excel para resolver operaciones tales como las mostradas en esta entrada,algunas llegan a ser muy simples como la hora y la fecha y otras muy complejas como de trigonometria.En realidad excel se caracteriza por las funciones sin ellas seri como un simple programa para hacer facturas con colores y formas.