lunes, 15 de agosto de 2011

Convertir en una BD (sin repetición)

Descargar el fichero: matricial_extrae.xlsx

En Excel podemos convertir una tabla de doble entrada en una base de datos. Los datos de la tabla de doble entrada tienen que ser sin repetición. En este caso se trata de países que no se pueden repetir.

En la Hoja1 del fichero matricial_extrae.xlsx estudiamos cómo extraer información de una tabla de doble entrada. Puede verse el siguiente post:



Hoja2

En esta ocasión trabajamos en la Hoja2 con la siguiente tabla de doble entrada.


Queremos obtener un listado donde figuren en columna todos los países y junto a cada uno de ellos la fecha y el empresa que le corresponde. El resultado será el siguiente.


La fecha la hemos calculado por un único método pero la empresa se ha calculado por 8 métodos diferentes.

Calcular la fecha es más sencillo al tratarse de un valor numérico. La fórmula matricial empleada en la celda C19 es la siguiente.

{=SUMA((tabla=B19)*Dias)}

Para saber más sobre fórmulas matriciales consulte el siguiente post.

http://trucosexcel.blogspot.com/2008/11/funciones-matriciales-en-excel.html

Los nombres de rango utilizados en la Hoja2 son estos.


Expliquemos la fórmula anterior. La expresión (tabla=B19) es una igualdad que Excel evalúa y nos dice si es verdadera o falsa. Al tratarse de una matriz tendremos una matriz de valores que pueden ser verdaderos o falsos. Como estamos igualando a B19 que es Angola, nos dará la siguiente matriz.


Todos los valores dan FALSO, salvo el primero que da VERDADERO. Esto es así porque Angola ocupa justo el primer lugar de la tabla.

En informática los verdaderos son unos y los faltos son ceros, por lo que en realidad lo que tenemos es la siguiente tabla, con la que podemos operar como si se trataran de números.


Si la tabla anterior se multiplica por el rango 'Dias' que es un vector columna de 10 elementos obtenemos lo siguiente.


El valor 39083 en formato fecha corresponde al día 1-enero-2007.

Ahora sólo falta sumar los valores anteriores, y mostrar el resultado con formato fecha. Esto es lo que se puede observar en la celda C19.


Gracias a que la fecha en realidad es un número hemos podido multiplicar y luego sumar. El problema viene cuando lo que buscamos no es un número sino texto. Es el caso de las Empresas, donde debemos buscar qué empresa corresponde a cada país. Así, por ejemplo, a Croacia le corresponderá la Emp2. Para averiguar esto el método anterior no es válido y hemos tenido que recurrir a otros métodos. Concretamente planteamos 8 método para conseguir nuestro objetivo.

Veamos la fórmulas para Angola, fila 19.

Método 1

{=BUSCARV(SUMA((tabla=B19)*NumEmp);TablaEmp;2)}

Utiliza la tabla auxiliar TablaEmp que está en el rango H12:I15.

Método 2

{="Emp"&SUMA((tabla=B19)*NumEmp)}

Utiliza las celdas auxiliares C4:F4 que se han nombrado con el nombre de rango NumEmp.


Método 3

{=INDICE(empresa;1;SUMA((tabla=B19)*NumEmp))}

Utiliza el rango auxiliar NumEmp.


Método 4

{=BUSCARH(SUMA((tabla=B19)*NumEmp);$C$4:$F$5;2;0)}

Utiliza el rango auxiliar NumEmp.


Método 5

{=INDICE(empresa;1;SUMA((tabla=B19)*{1;2;3;4}))}

La expresión {1;2;3;4} está relacionada con el número de columnas. En este caso son 4 columnas, pero si estuviéramos en un caso con un número mayor o menor de columnas tendríamos que adaptar la fórmula. Por ejemplo, para 5 columnas tendríamos que escribir  {1;2;3;4;5}.


Método 6

{=DESREF($B$5;0;COINCIDIR(1;SIGNO(CONTAR.SI(DESREF(tabla;;COLUMNA(tabla)-CELDA("columna";tabla);;1);B19));0))}


Método 7

{=INDICE(empresa;1;COINCIDIR(1;SIGNO(CONTAR.SI(DESREF(tabla;;COLUMNA(tabla)-CELDA("columna";tabla);;1);B19));0))}


Método 8

{=INDICE(empresa;1;(SUMA((tabla=B19)*(COLUMNA(tabla))))-COLUMNA(tabla)+1)}

Los método 6, 7 y 8 no requieren tablas auxiliares de datos.

Podríamos crear un método más, convirtiendo las celdas que contienen los nombres de las empresas (rango C5:F5) en un rango de valores numéricos. Numerando las empresas del 1 al 4, y para que en lugar de verse el número en esas celdas se vea tal y como se ve ahora: Emp1, Emp2, Emp3, Emp4, podríamos dar a esas celdas un formato personalizado poniendo esto: "Emp"Estandar


Lo que veríamos después de aplicar el formato sería lo mismo que teníamos antes.


Con la ventaja de que esas celdas ahora son verdaderos números y podemos emplear una fórmula similar a la que se empleo en el caso de las Fechas. Lo cual hace el cálculo más sencillo.


¿Y si se repiten?

Y ahora la pregunta que seguro que tienes en mente. Y que pasa cuando los datos de la tabla de doble entrada se repiten. En nuestro caso, ¿qué pasa cuando existen países repetidos?

La respuesta es que algunos de estos método dan error y otros en caso de tener dos Angolas nos dan la Empresa de uno de los valores o de otro de ellos. Dicho de otra forma no son válidos estos métodos.

Pero vayamos más allá, y pensemos que esta forma de presentar la información no es válida, ya que no tendría sentido tener un listado con todos los países sin repetición. Este informe no es válido ya que cuando le toque el turno a uno de los países que figura dos o más veces en la tabla de datos, ¿qué fecha y empresa ponemos?

En estos casos se tendría que ir a otro tipo de informe donde en el listado apareciera tantas veces el país como veces se repita, y por cada fila figuraría la fecha y empresa que corresponde a esa aparición.

jueves, 11 de agosto de 2011

Tiempo de Respuesta

Descargar el fichero: TiempoRespuesta.xlsm

Una macro en Excel que calcula el tiempo de respuesta de un servicio técnico, excluyendo domingos y festivos. Se trata de una empresa que proporciona el servicio técnico a sus clientes. Se pretende controlar cuántas horas se tarda en proporcionar el servicio, desde que se toma nota del aviso hasta que queda finalmente solucionado por el técnico que acude hasta las instalaciones del cliente. Deseamos que en el cómputo de horas no se incluyan los domingos, ni las fiestas según un listado de festivos que incluimos en la hoja de cálculo.

La macro que implementamos se crea en forma de función, y es la siguiente.

Código:

Function TR(Inicio As Date, Fin As Date, Optional Festivos As Range) As Double
Dim i As Long
Dim domingo As Integer
Dim c As Variant
Dim F As Long
Dim Fiesta As Boolean
If Festivos Is Nothing Then
  'si en TR no se usa el tercer argumento optativo
  If Int(Inicio) = Int(Fin) Then
    'si es el mismo día
    TR = Fin - Inicio
  Else 'para más de un día
    TR = 1 + Int(Inicio) - Inicio 'lo del primer dia
    TR = TR + Fin - Int(Fin) 'lo del último día
    For i = Int(Inicio) + 1 To Int(Fin)
      'contamos los domingos
      If i Mod 7 = 1 Then domingo = domingo + 1
    Next i
    TR = TR + (Int(Fin) - Int(Inicio)) - domingo - 1
  End If
  'si Inicio o Fin son domingo entonces TR=0
  If Int(Inicio) Mod 7 = 1 Or Int(Fin) Mod 7 = 1 Then TR = 0
Else
  'si en TR se usa el tercer argumento optativo
  If Int(Inicio) = Int(Fin) Then
    'si es el mismo día
    TR = Fin - Inicio
  Else
    For i = Int(Inicio) + 1 To Int(Fin)
      'detecta si el día i es fiesta
      Fiesta = False
      For Each c In Festivos
        F = CDate(c)
        If i = F Then Fiesta = True: Exit For
      Next
      'si el día i es fiesta o es domingo se descuenta de TR
      If Fiesta Or i Mod 7 = 1 Then TR = TR - 1
    Next i
    TR = TR + Fin - Inicio
  End If
  'si Inicio o Fin son domingo entonces TR=0
  If Int(Inicio) Mod 7 = 1 Or Int(Fin) Mod 7 = 1 Then TR = 0
  'si Inicio es fiesta entonces TR=0
  Fiesta = False
      For Each c In Festivos
        F = CDate(c)
        If Int(Inicio) = F Then Fiesta = True: Exit For
      Next
      If Fiesta Then TR = 0
  'si Fin es fiesta entonces TR=0
  Fiesta = False
      For Each c In Festivos
        F = CDate(c)
        If Int(Fin) = F Then Fiesta = True: Exit For
      Next
      If Fiesta Then TR = 0
End If
End Function

Disponemos de una fórmula antigua que únicamente calculaba la diferencia entre la fecha y hora del aviso y la del momento de atención. La nueva fórmula elimina los domingos y las fiestas incluidas en cierto rango que se marca al introducir la fórmula TR.


viernes, 5 de agosto de 2011

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:



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:


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.
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.



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