Ranking de ventas por año

Archivo de Excel utilizado: ranking_ventas.xlsx

Vamos a crear una Tabla Dinámica para jerarquizar a los vendedores según las venta realizadas en un par de años.

Disponemos de una base de datos con 1.000 registros donde la columna 'Año' se calcula mediante la función =AÑO(fecha)

Deseamos llegar a la siguiente Tabla Dinámica.

Método 1

El método 1 utiliza la creación de un Campo calculado.

Para lograr nuestro objetivo utilizamos la siguiente Lista de campos de tabla dinámica.

Observe que en Valores hemos añadido el Campo calculado %Imp. que nos dará el porcentaje del importe de las ventas sobre el total de ventas de su columna. Observe que abajo el total de cada columna es el 100%.

El campo calculado es el siguiente. En la fórmula simplemente introducimos el Importe.

En configuración de campo de valor elegimos % sobre el total de columnas.

Finalmente ordenamos de mayor a menor por las ventas del primer año.

Método 2

El método 2 logra el mismo objetivo si necesidad de crear un Campo calculado. Lo que hacemos es arrastrar el Importe dos veces a 'Valores'.

En este caso como el Campo calculado es tan simple que es únicamente el nombre de un campo y no se hace ninguna operación matemática con él, se puede hacer simplemente arrastrando nuevamente el campo Importe y a este segundo Importe luego le pedimos que se exprese en forma de porcentaje sobre el total de su columna.

Reordenar Tablas dinámicas

Archivo de Excel utilizado: ReordenarTabla.xlsx

Vamos a generar una Tabla dinámica partiendo de una Base de Datos. La idea es ver que las Tablas dinámicas nos permiten organizar los campos (columnas de la base de datos) como mejor nos parezca para llegar a obtener el informe deseado.

Agrupa tablas

Archivo de Excel utilizado: agrupa_tablas.xlsm

Deseamos agrupar varias tablas de doble entrada para formar con ellas una base de datos y luego poderla atacar con Tablas dinámicas. Vamos a resolverlo mediante dos métodos.

• Método 1: usando Tablas dinámicas con rángos de consolidación múltiple
• Método 2: con macro

Método 1: usando Tablas dinámicas con rángos de consolidación múltiple

Usaremos Tablas dinámicas con rangos de consolidación múltiple para poder agrupar o consolidar varias tablas de doble entrada.

Método 2: con macro

Esta es la macro que permite agrupar varias tablas de doble entrada para formar una verdadera base de datos con 4 campos.

Sub CreaBD()
Dim n As Byte 'número de tablas de doble entrada
Dim i As Integer, j As Long, k As Integer, r As Long, p As Byte, q As Long
Dim nBD As Long 'número de registros de la BD completa
Dim Celda As Range
Dim Tabla() As Range 'Es un objeto en forma matricial
Dim rngStart As Range
Dim rngEnd As Range
Dim A()
Dim BD() 'Base Datos en forma matricial. 4 campos
n = InputBox("¿Cuántas tablas de doble entrada desea agrupar?", , 3)
ReDim A(n, 5)
ReDim Tabla(n) 'Si n fuera 3 tendríamos 3 tablas que son objetos de tipo Range
For i = 1 To n 'por cada tabla
  Set Celda = Application.InputBox( _
    prompt:="Seleccione una celda de la Tabla " & i, Type:=8)
    'Type:=8 significa: una celda de referencia como un objeto Range
  A(i, 1) = Celda.Worksheet.Name 'nombre de la Hoja donde está la tabla
  Set Tabla(i) = Worksheets(A(i, 1)).Range(Celda.Address).CurrentRegion
  A(i, 2) = Tabla(i).Rows.Count 'Número de filas de la tabla
  A(i, 3) = Tabla(i).Columns.Count 'Número de columnas de la tabla
  Set rngStart = Tabla(i).Cells(1, 1)
  Set rngEnd = Tabla(i).Cells(Tabla(i).Rows.Count, Tabla(i).Columns.Count)
  A(i, 4) = rngStart 'Primera celda de la tabla
  A(i, 5) = rngEnd 'Última célda de la tabla
  nBD = nBD + (A(i, 2) - 1) * (A(i, 3) - 1) 'acumula el número de registros de cada tabla
Next i
'Como ya sabemos la dimensión de BD la redimensionamos
ReDim BD(4, nBD)
'Escribimos la base de datos BD
For i = 1 To n 'para cada Tabla
  For j = 1 To A(i, 2) - 1  'Para cada fila de la tabla (ciudades)
    For k = 1 To A(i, 3) - 1  'Para cada columna de la tabla (meses)
      r = r + 1 ' r es el número de registro de la base de datos BD
      BD(1, r) = Tabla(i).Cells(j + 1, 1) 'Ciudad
      BD(2, r) = Tabla(i).Cells(1, k + 1) 'Mes
      BD(3, r) = Tabla(i).Cells(j + 1, k + 1) 'Valor
      BD(4, r) = A(i, 1) 'Tabla
    Next k
  Next j
Next i
'Añadimos una hoja nueva
Sheets.Add After:=Sheets(Sheets.Count)
'Vamos a la hoja nueva
Worksheets(Sheets.Count).Activate
'Creamos la cabecera de la BD
[B2] = "Base de datos consolidada"
[B4] = "Ciudad"
[C4] = "Mes"
[D4] = "Valor"
[E4] = "Tabla"
'Escribimos la base de datos
For p = 1 To 4 'las 4 columnas
  For q = 1 To r ' r es el número de registros de la base de datos BD
    Cells(q + 4, p + 1) = BD(p, q)
  Next q
Next p
End Sub

Tabla Dinámica: cambiar contar por sumar

Archivo de Excel utilizado: cambia_contar_por_sumar.xlsm

En ocasiones al crear una tabla dinámica aparece CONTAR cuando lo que nosotros deseamos es que aparezca SUMAR. A veces aparece CONTAR y otras veces aparece SUMAR ¿cuál es el motivo?

Si partimos de una base de datos en forma de tabla donde no existen celdas vacías la Tabla Dinámica se creará con la opción SUMAR, pero si existe alguna celda vacía en un campo la Tabla Dinámica se creará con la opción CONTAR.

Si lo que deseamos es SUMAR debemos cambiar una a una todas las columnas (todos los campos) ya que Excel no permite cambiar todos ellos simultáneamente. Cuando tenemos muchas columna que cambiar esto supone una tarea repetitiva bastante tediosa. Para estos casos disponemos de una macro que efectuará el cambio en todos los campos.

Hoja 1

Disponemos de una pequeña tabla con dos campos numéricos: Unidades y Facturación. Existen dos celdas vacías que hemos puesto de color amarillo. Al hacer la tabla dinámica sobre esta base de datos aparece:

• Cuenta de Unidades
• Cuenta de Facturación

que son dos columnas de la tabla dinámica que lo que hacen es contar el número de registros existentes de cada uno de esos tipos. Nosotros deseamos que efectúe la SUMA en lugar de la CUENTA. Podemos solucionarlo simplemente poniendo ceros en las celdas amarillas y al crear la tabla dinámica lo hará con SUMA que el lo que pretendíamos.

También podemos emplear la siguiente macro.

Sub Cambia_Contar_por_Sumar()
Dim pf As PivotField
With Selection.PivotTable
  For Each pf In .DataFields
    With pf
      .Function = xlSum
      .NumberFormat = "#,##0"
      .Name = Replace(.Name, "Cuenta", "Suma")
    End With
  Next pf
End With
End Sub

Antes de ejecutar la macro debe crear la Tabla Dinámica y situar el cursor dentro de la tabla dinámica.

Hoja 2

Disponemos de una base de datos similar a la anterior pero con 1.000 registros y 27 campos numéricos en los que, al menos, existe una celda vacía en cada uno de ellos que hemos puesto de color amarillo.

Proponemos como ejercicio que realice la tabla dinámica y que emplee la macro para convertir CUENTA en SUMA.

Primero cree la tabla dinámica tal y como se ve en la siguiente imagen.

Finalmente sitúe el cursor dentro de la tabla dinámica y ejecute la macro.

Ventas del periodo

Descargar el archivo: caso_ventasperiodo.xlsx

Vamos a resolver un caso práctico.

Se trata de determinar las ventas del periodo para un cliente concreto.

Comparar Tablas dinámicas entre Excel 2010 y 2003

Descargar el fichero: TablaDinamica2010.xlsx

Podemos comparar las Tablas Dinámicas en la versión 2010 con la versión 2003, adaptando la nueva versión al estilo clásico.

 

Tablas Dinámicas y Elementos Calculados

Descargar el fichero: TDelementocalculado.xlsx

En un post anterior habíamos hablado de Campos Calculados de una Tabla Dinámica de #Excel. Ahora vamos a ver un caso un tanto especial en el que necesitamos calcular la variación porcentual de cierta magnitud económica entre los años 2010 y 2011. El problema es que la información de partida no distingue entre ambos años, sino que simplemente existe un campo (una columna) con la fecha. Lo vamos a resolver por dos métodos. Primero emplearemos fórmulas matriciales y en segundo lugar lo haremos creando un Elemento calculado en una Tabla dinámica.

Puede consultar el post donde hablábamos de:

• Tablas dinámicas y Campos Calculados

Datos de origen (Hoja1)

Partimos de una pequeña base de datos, de únicamente dos columnas:

1. Fecha. El primer día de cada mes de los años 2010 y 2011. En total 24 registros (filas)
2. Importe. Es el valor de cierta magnitud económica. Podría se por ejemplo la facturación de una empresa

Resolución con fórmulas matriciales

En la propia Hoja1 vamos a resolver nuestro caso aplicando una función matricial. Para saber cómo se ha de trabajar con este tipo de funciones puede consultar el post siguiente:

• Funciones matriciales en Excel

La celda F5 es:

{=SUMA(Importe*(AÑO(Fecha)=F$4)*(MES(Fecha)=$E5))}

La fórmula está entre llaves lo que indica que es una fórmula matricial. Cuando nosotros escribimos la fórmula no ponemos las llaves, esto lo hace Excel, al validar la fórmula. Las fórmulas matriciales no se validan pulsando ENTER, se han de pulsar simultáneamente las tres teclas siguientes:

CONTROL + MAYÚSCULAS + ENTER

La tecla de MAYÚSCULAS es la tecla SHIFT.

Los rangos empleados son:

• Fecha =Hoja1!$B$5:$B$28
• Importe =Hoja1!$C$5:$C$28

La fórmula se ha de copiar al rango F5:G16.

Para crear la columna H, simplemente aplicamos la fórmula que nos da la variación porcentual entre los valores del año 2010 y los valores del año 2011, que es lo que estamos buscando. La celda H5 es:

=+G5/F5-1

Luego se la dota con formato de porcentaje, y obtenemos un 20% que es el incremento experimentado entre el importe del año 2010 (1.000) y el importe del año 2011 (1.200), para el mes de enero. Se copia hacia abajo y se obtienen los porcentajes de variación por cada mes.

Añadimos columnas para el año y el mes (Hoja2)

No es muy ortodoxo añadir a una base de datos nuevos campos (columnas) que se calculan con información ya contenida en la propia base de datos. Pese a ésta recomendación general, en este caso, añadiremos la columna Mes y la columna Año, que se calculan utilizando las fórmulas de Excel MES y AÑO. Estas fórmulas al aplicarse a una fecha válida nos proporcionan precisamente el mes y el año de esa fecha.

Es imprescindible que estas fórmulas se apliquen a una fecha válida en Excel. Por este motivo hemos necesitado que la columna de Fecha sea una fecha válida, y hemos tenido que tomar el primer día de cada uno de los meses considerados. Así la primera fecha es el 1-enero-2010, y no hubiera valido haber puesto simplemente enero-2010 sin indicar el día. Una fecha válida debe indicar el día, el mes y el año aunque luego en el formato que demos únicamente pidamos que se muestre el mes y el año.

En la columna B aparece la fecha en formato mmm/aaaa. Son fechas válidas ya que aunque no veamos el día,  esto es por el formato, pero la fecha está introducida como dia-mes-año.

La celda D5 es:

=MES(B5)

La celda E5 es:

=AÑO(B5)

Resolución con Tabla Dinámica

Primero creamos una Tabla dinámica como la siguiente.

Hemos puesto el Mes en rótulos de fila, el Año en rótulos de columna y el Importe como datos en Valores.

En Excel 2007, con el cursor situado en la tabla dinámica pinchamos arriba sobre la pestaña "Herramientas de tabla dinámica". Luego sobre Fórmulas.

Deseamos elegir Elemento calculado pero vemos que aparece deshabilitado, en color gris. Para que podamos tener disponible esta opción debemos situar el cursor del ratón exactamente sobre la celda B4 o C4 de la tabla dinámica que es donde se encuentran los indicadores de los años 2010 y 2011.

Ahora si podemos insertar un Elemento calculado.

Aparecerá la ventana donde podemos construir nuestro elemento calculado.

Al elemento calculado le denominaremos Var.% ya que representará la variación porcentual. La fórmula que pondremos es:

='2011'/ '2010'-1

Pero no debemos escribir la fórmula tecleando los años sino eligiendo los Elementos con el ratón. Elegimos el elemento del año 2011, pulsamos luego sobre el símbolo de dividir (/), pulsamos sobre el elemento del año 2010, y finalmente restamos uno.

Con ello se genera la columna correspondiente al elemento calculado Var.%. La nueva columna se formatea en formato de porcentaje y tendremos ya creada nuestra Tabla dinámica con elemento calculado.

Crear lista de fórmulas

Podemos crear una lista con todas las fórmulas creadas en la tabla dinámica tanto de elementos calculados como de campos calculados. Para ello, sitúe el cursor en la tabla dinámica y elija 'Herramientas de tabla dinámica', Fórmulas, y 'Crear lista de fórmulas'.

Esto genera una nueva hoja en la que obtendremos el listado solicitado.

Bajo este listado aparece el siguiente comentario.

Notas:

• Cuando una celda se actualiza con más de una fórmula, el valor lo establece la fórmula con la última orden de resolución.
• Para cambiar el orden de resolución de varios elementos o campos calculados, en la ficha Opciones, en el grupo Herramientas, haga clic en Fórmulas y, a continuación, seleccione Orden de resolución.

Caso práctico propuesto

Descargue el archivo siguiente.

• caso_TD_compara_años.xlsx

Disponemos de una base de datos con dos campos: Fecha e Importe de ventas.

En este caso práctico nos proponemos crear una tabla dinámica como la siguiente.

Otro archivo para practicar.

• caso_TDelementocalculado.xlsx

¿Aceptas el reto?
Ánimo, a ver si lo consigues.

Rango Dinámico

Descargar el fichero: RangoDinamico.xlsx

Si tenemos una tabla que cambia de tamaño nos vemos obligados a tener que redefinir el rango de los datos que queremos incluir en un gráfico o en una tabla dinámica. Si la tabla contiene un número de filas que varían con frecuencia es bastante tedioso tener que cambiar el rango de datos para el gráfico, y lo mismo con la tabla dinámica. Vamos a exponer un método por el cual no tendrá que preocuparse por el tamaño de su tabla ya que tanto el gráfico como la tabla dinámica tomará el tamaño necesario de forma automática.

En nuestro fichero de ejemplo disponemos de cuatro columnas con datos generados de forma aleatoria. Si queremos ampliar el número de filas de la tabla lo único que debemos hacer es copiar su última fila hacia abajo. Para reducir su número de filas borraremos, por abajo, tantas como deseemos.

Inicialmente la tabla tiene 20 registros (filas de datos).

Deseamos crear un gráfico como el siguiente donde se muestren los 20 puntos que corresponden a los 20 datos de Facturación a lo largo del tiempo. Hemos añadido una línea de tendencia (recta de regresión).

Si queremos que el gráfico ajuste el número de puntos a los que contenga la tabla independientemente de su tamaño y de forma automática debemos definir los rangos que tomará el gráfico. La definición de rangos será un tanto curiosa ya que no nos limitaremos a seleccionar el área que deseamos asociar al rango sino que trabajaremos con fórmulas.

Vamos a utilizar la función DESREF y la función CONTARA de forma anidada.

Función DESREF

La función DESREF es una función que puede emprearse de dos maneras:

1. Utilizando los 3 primeros argumentos permite extraer un valor de una celda que se encuentre en una tabla. Para ello debemos indicar desde que celda debemos comenzar a contar (punto de partida), indicando el número de filas hacia abajo que nos hemos de desplazar, y el número de columnas a la derecha que nos debemos mover. Esta forma de emplear DESREF no es matricial.
2. Utilizando los 5 argumentos de la función. En este caso la función es matricial y permite extraer un bloque de celdas. Se ha de indicar el punto de partida, la celda de la esquina superior izquierda y las dimensiones del bloque.

La sintaxis de DESREF en su versión matricial es:

=DESREF(ref;filas;columnas;alto;ancho)

donde

• ref es la celda desde la que partimos. Es el punto de partida (Km. 0). Para los que conozcan Madrid es el kilómetro cero de la Puerta del Sol
• filas es el número de filas hacia abajo que nos hemos de desplazar para llegar a la celda que marca la equina superior izquierda del rango que deseamos extraer. Si las filas son negativas nos movemos hacia arriba, siempre tomando como punto de partida nuestro kilómetro cero
• columnas es el número de columnas a la derecha que nos hemos de desplazar para llegar a la celda que marca la esquina superior izquierda del rango que deseamos extraer. Si las columnas son negativas indica que nos movemos a la izquierda de nuestro kilómetro cero
• alto es el número de filas que contiene el rango que deseamos extraer
• ancho es el número de columnas que contiene el rango que deseamos extraer

Si queremos utilizar esta fórmula debemos considerar que se trata de una fórmula matricial y que por tanto se han de dar los tres pasos que siempre requieren este tipo de fórmulas:

1. Paso 1. Primero debemos seleccionar el rango donde la fórmula matricial dejará su resultado. Esto es importante, ya que muchas fórmula matriciales no dejan su resultado un una sola celda, sino en un rango de celdas. Este primer paso indica que este rango debemos seleccionarle.
2. Escribimos la fórmula matricial de que se trate. En este caso sería DESREF si queremos probar con ella.
3. Para validar la fórmula no pulse ENTER. Se deben pulsar simultáneamente las tres teclas siguientes: CONTROL + MAYUSCULAS + ENTER

Función CONTARA

Hablemos ahora de la función CONTARA. Para contar existen dos funciones:

• CONTAR: cuenta las celdas numéricas del rango que indiquemos
• CONTARA: Cuenta todo, números y texto

Aplicadas estas funciones sobre la columna E, donde se encuentra la facturación los resultados obtenidos son:

• =CONTAR(E:E)      da como resultado 20, que son los registros de la tabla
• =CONTARA(E:E)   da como resultado 21, que son los registros numéricos más la cabecera

Creación de Rangos

Vamos a crear un nombre de rango denominado laFactura que incluya todos los datos de facturación. Incluirá desde la celda E3 hacia abajo hasta el final de la columna E. Inicialmente el rango será E3:E22 ya que  la tabla inicialmente tiene 20 registros. Pero al indicar el rango que se asocia a ese nombre de rango lo haremos con fórmula para que sera automático y se ajuste a la longitud de la columna E que exista en cada momento.

La fórmula para el nombre de rango laFactura es:

=DESREF(Hoja1!$E$2;1;0;CONTARA(Hoja1!$E:$E)-1;1)

Ahora vamos a crear el nombre de rango laFecha para se ajuste al rango que marca los datos de fecha de la columna B, desde la celda B3 hasta la última fecha que se encuentre en la columna B.

La fórmula para el nombre de rango laFecha es:

=DESREF(Hoja1!$B$2;1;0;CONTARA(Hoja1!$B:$B)-1;1)

Los dos rangos anteriores se utilizaran para crear el gráfico dinámico. Para la tabla dinámica que crearemos posteriormente vamos a definir el nombre de rango Tabla que abarca toda la tabla incluida la cabecera, y las cuatro columnas que la constituyen.

La fórmula para el nombre de rango Tabla es:

=DESREF(Hoja1!$B$2;0;0;CONTARA(Hoja1!$E:$E);4)

Nombrar rangos

Ahora debemos nombrar los rangos.

• En Excel 2003 la forma ortodoxa de definir un rango es seleccionando Insertar, Nombre, Definir. 
• En Excel 2007 debemos ir a la pestaña de Fórmulas y pulsar sobre la opción Administrador de Nombres.

Gráfico Dinámico

Creamos un gráfico de tipo Dispersión del tipo Dispersión solo con marcadores.

Indicamos el origen de los datos indicando los nombres de rango. Para el eje X utilizamos el rango laFecha, y para el eje Y utilizamos el rango laFactura.

Importante

Al indicar el rango para el eje X e Y debemos indicar no solo el nombre del rango sino también el nombre del fichero, sin olvidar el signo de admiración (!) y el signo igual al inicio de la expresión (=).

Para los valores X de la serie debemos escribir exactamente lo siguiente:

=RangoDinamico.xls!laFecha

Para los valores Y de la serie debemos escribir exactamente lo siguiente:

=RangoDinamico.xls!laFactura

Si copiamos la última fila de la base de datos hacia abajo hasta la fila 102 habremos obtenido 100 registros que automáticamente quedarán representados en el gráfico sin necesidad de que toquemos los rangos. El resultado podría ser el siguiente.

Observamos que los puntos representados en el gráfico han aumentado automáticamente pasando de los 20 iniciales a los 100 que ahora.

Tabla Dinámica

Vamos a crear una Tabla Dinámica utilizando los 20 datos iniciales de nuestra tabla.

Al confeccionar la Tabla dinámica nos piden el rango de los datos y en ese momento indicaremos el nombre de rango Tabla que previamente hemos creado.

 

En Configuración de campo elegimos Cuenta.

Obtenemos una Tabla Dinámica similar a la siguiente, donde el número de elementos totales es de 20.

Al ampliar el número de filas de la tabla hasta 100 filas seguimos viendo en la Tabla dinámica que el número de elementos es de 20. Para poder ver los 100 elementos debemos actualizar la Tabla dinámica. Esto se consigue pulsando con el botón derecho del ratón sobre la propia Tabla dinámica y eligiendo en el menú contextual la opción Actualizar.

Tras actualizar la Tabla dinámica podemos observar que ahora son 100 los datos considerados.

Convertir una Tabla de doble entrada en una BD

Descargar el fichero: Cronograma.xlsx

Vamos a convertir una tabla de doble entrada (con cabeceras de fila y columna) en una auténtica base de datos, esto es, una tabla donde la información se organiza por campos (columnas). Para ello utilizaremos Tablas Dinámicas con Rangos de Consolidación Múltiple. Posteriormente, partiendo de la base de datos, resumiremos la información utilizando nuevamente Tablas Dinámicas, o la utilidad que tiene Excel denominada Subtotales.

Tablas Dinámicas y Campos calculados

Descargar el fichero: TDcampocalculado.xlsx

Las Tablas Dinámicas han supuesto la revolución en Hojas de Cálculo de los últimos años. Permiten generar informes rápidos y flexibles. Si usted llega a conocer bien su funcionamiento puede cambiar radicalmente la gestión de su departamento o unidad de negocio.

En este artículo vamos a crear una Tabla Dinámica partiendo de una base de datos. En la tabla dispondremos de los costes de diferentes departamentos de la empresa para el año 2010 y la previsión para 2011. Crearemos un campo calculado que nos permita observar el incremento de cada departamento en estos años.

La base de datos de partida es sencilla.

Coste por Proyecto y Departamento

En Excel 2007 vamos al menú Insertar y luego Tabla Dinámica. Siguiendo unos sencillos pasos llegamos a crear una tabla dinámica como la que se muestra en la siguiente imagen:

Disponemos de los costes del año 2010 y la previsión para 2011 por cada uno de los departamentos. Los cuatro proyectos se han establecido como filtro de página en la parte superior de la tabla dinámica.

Ahora deseamos disponer de una columna más que nos indique la variación porcentual experimentada por los costes entre los años 2010 y 2011. Este objetivo se podría lograr por varios métodos:

1. Escribiendo en la celda D5 la fórmula: =C5/B5-1. Esta fórmula nos da el incremento en tanto por uno. Para verlo en porcentaje basta pulsar sobre el icono de porcentaje (%).
2. Establecer la fórmula anterior pero vinculando sobre las celdas C5 y B5. En este caso veremos que la fórmula utiliza la función IMPORTARDATOSDINAMICOS. Esta forma de trabajar tiene la ventaja de que ésta función apunta a la tabla dinámica y por tanto no perdemos el vínculo dinámico con la base de datos.
3. Crear un campo calculado. Este es el método que utilizaremos en este artículo.

Creación del campo calculado

En Excel 2007 con el cursor sobre la tabla dinámica veremos arriba una nueva opción denominada:

Herramientas de tabla dinámica

Al pulsar sobre ella se abren un nuevo menú sobre el que pulsaremos sobre Formulas.

La imagen anterior puede diferir de la que usted pueda ver en pantalla, ya que en Excel 2007 la cinta de opciones muestra diferentes iconos, o los muestra más o menos resumidos en función de la resolución de su pantalla y del tamaño de ventana que utilice.

Al pulsar sobre Fórmulas elegimos Campo calculado.


Aparece una ventana denominada Insertar campo calculado en el que crearemos la fórmula:

=’2011′/’2010′-1

La fórmula se crea introduciendo los campos (columnas) de la tabla dinámica. En este caso calculamos el porcentaje de variación por la clásica fórmula:

Valor Final / Valor Inicial -1

Expresión que es igual a la siguiente:

(Valor Final – Valor Inicial) / Valor Inicial

En nuestro caso los costes del año 2010 son los valores iniciales y las previsiones para 2011 son los valores finales.

Esto genera una nueva columna que denominamos Var.% que recoge la variación porcentual de los costes entre los años 2010 y 2011. Inicialmente los valores que nos dan están en tanto por uno y hemos de ser nosotros los que debemos dar formato a esos valores como Porcentaje de dos decimales.

Los campos calculados son muy útiles al trabajar con tablas dinámicas y tienen la ventaja de que no perdemos el vínculo dinámico con la base de datos.

Gráfico Dinámico

Sitúa el cursor sobre la tabla dinámica y pulsa sobre la opción que verás arriba denominada Herramientas de tabla dinámica. Luego pulsa sobre el icono que te permite crear un gráfico dinámico tal y como se muestra en la siguiente imagen.

Elegimos el tipo de gráfico y de forma instantánea dispondremos de un gráfico muy flexible con muchas opciones que podemos modificar.

Ejercicio propuesto

En la Hoja3 disponemos de una base de datos con 200 registros con los siguientes campos: Fecha, Artículo, Facturación y Unidades. Nuestro objetivo es crear una tabla dinámica agrupada por meses y trimestres en la que introducimos un campo calculado que nos proporcione el precio medio de venta en cada mes.

Todos los datos de la base de datos son aleatorios. Así la fecha es un valor aleatorio del primer semestre del año 2011, y se genera con la fórmula:

=ALEATORIO.ENTRE(FECHA(2011;1;1);FECHA(2011;6;30))

Los posibles artículos son cinco y se generan aleatoriamente con la fórmula:

=ELEGIR(ALEATORIO.ENTRE(1;5);”Art1″;”Art2″;”Art3″;”Art4″;”Art5″)

En Excel 2003 y anteriores para que no de error la fórmula ALEATORIO.ENTRE debemos haber activado el complemento de Herramientas para análisis. Esto se puede activar en el menú Herramientas, Complementos.

Agrupando las fechas simultáneamente por meses y por trimestres obtenemos la tabla dinámica que se muestra en la imagen.

Ahora hemos de crear el campo calculado que insertará una nueva columna en la tabla dinámica. Pretendemos calcular el precio medio, por tanto hemos de dividir la facturación entre el número de unidades.

La tabla dinámica que obtenemos ya incorpora el campo calculado Precio medio.

Los resultados numéricos que usted obtenga serán diferentes de los que se muestran en la anterior imagen, esto es debido a que la base de datos trabaja con valores aleatorios.

Podemos ver cómo cambian los valores de la tabla dinámica al actualizarla. Para ello pulse con el botón derecho del ratón sobre la tabla dinámica y elija Actualizar.

Excel 2010

Para crear un campo calculado en Excel 2010 sigue estos pasos:

1. Partimos de los datos originales
2. Creamos la tabla dinámica pulsando sobre: Insertar, Tabla dinámica, y diseñamos la tabla según nuestras preferencias
3. La tabla dinámica ya esta creada. Ahora nos situamos con el cursor dentro de cualquier celda de la tabla dinámica y veremos arriba una pestaña denominada “Herramientas de tabla dinámica”. Esta pestaña tiene dos sub-pestañas denominadas: Opciones y Diseño. Nos situamos en Opciones.
4. Pinchamos sobre Cálculos y luego sobre Campos, elementos y conjuntos, y finalmente pinchamos sobre Campo calculado.
5. Luego se siguen los pasos vistos en Excel 2007 ya que el proceso de creación del campo calculado es similar.

Ordenar dentro de una Tabla Dinámica

Podemos ordenar dentro de una Tabla Dinámica por los campos de fila o columna según orden alfabético, o incluso manualmente indicando nosotros mismos el orden deseado. También podemos ordenar por el importe del Total General. Veamos cómo se hacen estas tareas en Excel 2007.

Supongamos una tabla dinámica sencilla, con el importe facturado según tres productos: verduras, carnes y lácteos. Únicamente disponemos de dos regiones: norte y sur.

Ordenación de un campo

Pulsar con el botón derecho del ratón sobre uno de los elementos del campo Producto. Elegir Ordenar y seguidamente elegir "Más opciones de ordenación....".

Podemos elegir entre ordenar de forma ascendente, de forma descendente o manualmente. Si elegimos hacerlo manualmente podremos arrastrar los elementos para reorganizarlos.

Podemos pulsar sobre el botón: "Más opciones..." y veremos la siguiente ventana.

En las opciones habitualmente tenemos marcada la opción: "Ordenar automáticamente cada vez que se actualice el informe". Si desmarcamos esta opción podemos ver que en el primer criterio de ordenación podemos elegir por cualquiera de las lista personalizadas que tuviéramos creadas en nuestro Excel.

Ordenar por el Total general

Supongamos que nuestro jefe está muy interesado en ordenar por el importe del Total General. Esto se puede hacer en orden ascendente, en orden descendente.

También podemos pulsas sobre "Más opciones de ordenación..."