Manejo de Tablas dinámicas en Excel

Imaginemos que se tiene un detalle de compras de un año (factura, fecha, categoría, descripción, importe) y necesita tener – en hojas individuales por cada mes – el resumen de las compras agrupadas por categoría.Esto es, necesitamos las fechas individuales de cada transacción, agrupadas por mes y que cada uno de ellos esté en una hoja diferente.Por supuesto, sin tener que armar manualmente una a una la hoja de cada mes.Más o menos como se muestra en la siguiente imagen:Para hacer esto vamos a aprovechar propiedades poco conocidas de las tablas dinámicas: Agrupar y Mostrar páginas de filtros.A partir del archivo de trabajo (el  link para descargar está al inicio del post).1. Crear tabla dinámica.Asumo que más o menos conoces como utilizar tablas dinámicas, así que iré rápido en esta parte.Simplemente te paras en una celda del rango donde están los datos (en nuestro caso, puede ser la celda A1), seleccionas Intertar Tabla Dinámica.Deberías ver todo el rango de datos seleccionado y le das Aceptar.2. Armar Tabla DinámicaLa Tabla Dinámica final que necesitamos utiliza solo dos campos: Tipo y Suma de Valor.Sin embargo, en esta etapa debemos insertar también la fecha para poder agruparla por mes (veremos esto más adelante).En la Lista de Campos, arrastramos Fecha y Tipo a Rótulos de Fila y Valor al campo de Suma de Valores.Si no ves “Suma de Valor” sino, por ejemplo “Contar Valor”, seleccionas el triángulo negro a la derecha -> Configuración de campo de valor -> Suma.3. Agrupar por mesesNo nos interesa tener cada una de las fechas individualmente, sino que estén agrupadas por meses.Para esto, seleccionamos una fecha cualquiera (NO el Rótulo, sino una fecha, digamos la celda A4.Ahora hacemos clic en el botón derecho y elegimos Agrupar.Esto nos desplegará un nuevo menú el cual nos indica distintas opciones para agrupar.Excel interpreta que es un campo de fecha-hora y nos da distintas opciones: segundos, minutos, horas, días, meses, trimestres y años.Incluso se pueden combinar.  Por ejemplo, modemos elegir Meses y Años.En este caso, Fecha estará agrupada por meses y creará una nueva variable que se llama años.Para este ejemplo, solo vamos a seleccionar Meses.4. Convertir cada Mes en una Hoja.En este punto, tu Excel debería verse más o menos así:Para poder tener los resultados de cada mes en una hoja distina, primero debemos tener las fechas como filtros.Nos paramos en la tabla dinámica y arrastramos el campo fecha desde Rótulos de Fila a Filtro de Informe.Ahora debemos convertir cada uno de los elementos del filtro en hojas separadas.Para esto, teniendo seleccionada alguna de las celdas de la tabla dinámica, tendremos habilitado el menú superior Tabla Dinámica.Vamos al submenú Opciones.  El primer menú de la izquierda nos indicará el nombre de la tabla dinámica y un nuevo submenú de opciones.Al desplegar este menú de opciones, seleccionamos “Mostrar páginas de filtro de informes …”.Automáticamente Excel armará una hoja de cálculo para cada una de las opciones del filtro.Si llegaran a armarse hojas de más, simplemente eliminamos las que no necesitamos. (Tomado de David Vasquez 2008)


Me permito mostrar este vídeo para tener una mejor óptica de como elaborar tablas dinámicas(Tomado youtube- canal MKTNGCREATIVO)

Al mismo tiempo facilito unas imágenes para poder observar mas detenidamente el proceso

Fuente: http://www.google.com.co/imgres?imgurl=http://media.exceltotal.com/2011/05/que-es-una-tabla-dinamica-01.jpg&imgrefurl=http://exceltotal.com/que-es-una-tabla-dinamica/&usg=__VGmTpVpdYqsO61WyJhm1hcTy1Kw=&h=446&w=580&sz=68&hl=es&start=5&sig2=mCp7mxrMhVPlv5n2bwN7zQ&zoom=1&tbnid=RYYc6ZwRDw_LcM:&tbnh=103&tbnw=134&ei=vJ_FT_3wF5Gm8gTfu-GaBg&um=1&itbs=1

Creación de Gráficos en Excel 2010

Excel incorpora una utilidad muy cómoda que nos permitirá pasar los datos de una tabla a una gráfica en la que situaremos los valores en los ejes de la gráfica.

Para llevar a cabo el proceso primero debemos introducir nuestros datos en una tabla. Es importante introducirlos en forma de columna, es decir, si introducimos valores de resistencias electrónicas, deberán ir todas seguidas en una columna. Después en la columna de al lado pondremos los valores que deseamos relacionar con cada resistencia.

En la imagen se ve que en la columna A se han introducido una serie de intensidades extraídas de un estudio y en la B sus Voltajes correspondientes. Antes de empezar con la gráfica seleccionamos los valores que queremos plasmar en ella.

Hecho esto, podemos empezar a configurar la gráfica. Para ello nos dirigimos a Insertar y vemos los distintos tipos de gráficos que podemos crear. En este caso, por las características de nuestros valores, elegiremos Dispersión/Dispersión solo con marcadores.

Ahora veremos que ha aparecido de forma automática una tabla en frente de nosotros. Sin embargo, normalmente querremos editarla. Algunos aspectos importantes cuando editamos son:

- Color de los puntos. Se cambia con una barra que se encuentra en la parte superior de Excel.

- Nombre del eje X e Y: Se cambia pulsando sobre la frase Título del eje que aparece en el gráfico.

- Título del gráfico: Se cambia pulsando sobre la frase Título del gráfico.

- Diseño: Aquí eliges la forma de representación del gráfico. Se elige en un cuadro que aparece en la parte superior.

Como veis el proceso es bastante sencillo e intuitivo. Con este tipo de gráficas conseguirás un resultado bastante profesional como el que podéis ver a continuación:

Con este vídeo veremos un tutorial para aprender mejor como realizar gráficos en excel 2010

Manejo de la Función Consultar V en Excel

Excel dispone de funciones de búsqueda y referencia para poder consultar valores de otras tablas para nuestros cálculos.

Imaginemos que diponemos de una tabla en la que tenemos almacenados los datos de nuestros productos:



Esta tabla la tenemos en una hoja aparte llamada artículos.

Ahora si queremos elaborar facturas, hojas de pedido o cualquier otro tipo de informe no nos hace falta copiar el precio "a mano", o saber en qué celda está ya que podemos usar una función de búsqueda de la que Excel dispone llamada ConsultaV (en versiones anteriores era BuscarV).

Veamos primero lo que queremos conseguir:




Se trata de una factura en la que los valores calculados están con fondo amarillo, y los únicos datos que hemos introducido son el código los productos y el número de unidades vendidas.


La función que hemos escrito para obtener la descripción de los productos, celda B2, es la función =ConsultaV, que nos sirve para buscar un valor en la primera columna de una tabla y obtener el valor de su misma fila y en la columna especificada. Es decir, busco en la tabla de la figura 1, los productos por referecias y puedo extraer los datos de descripción, precio y stock.


Su sintaxis es la siguiente:

= ConsultaV(Valor Buscado; Rango de búsqueda; indicador de columna)

En nuestro ejercicio, pues, pondríamos:
= ConsultaV(SX-3313; tabla de hoja Artículos; 2)

que pasado a celdas y rango queda:
= ConsultaV(B4;Artículos!$B$3:$E$13;2) .- para la descripción, y
= ConsultaV(B4;Artículos!$B$3:$E$13;3) .- para el precio.

Es importante observar que hemos especificado el rango con referencias absolutas y el artículo con referencias relativas, para de este modo, escribir las fórmulas en la primera fila y poder copiarlas en las demás.

En el indicador de columnas hemos puesto un 2 para la descripción y un 3 para el precio, ya que la columna 1 es la referencia, la 2 es la descripción, 3 precio y 4 stock.

Para obtener el importe tan sólo hemos mutiplicado el precio por el número de unidades vendidas ( = C2*D2) y la función suma para obtener el importe total ( = SUMA(E2:E4)).

Para el cálculo del IVA simplemente hemos multiplicado el importe total por 18% (=E11*18%) y para el TOTAL, = E11 + E12.