martes, 9 de junio de 2015

Formato condicional usando el operador lógico Y

Veamos un caso de práctico del uso que se da a Formato Condicional cuando lo usamos con una fórmula que requiere el uso del operador lógico Y. Lo que hacemos es añadir dos condiciones lógicas de forma que se exige que se cumplan ambas. Cuando se piden ambas se requiere usar el Y.
  • pedimos que el valor sea inferior al promedio de su columna, y
  • pedimos que no se trate de una celda vacía

Puede descargar el archivo del caso práctico.


jueves, 28 de mayo de 2015

Ranking de ventas por año

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

Puede descargar el siguiente archivo de Excel.


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.



jueves, 21 de mayo de 2015

Reordenar Tablas dinámicas

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.

El archivo de Excel que contiene el caso práctico se puede descargar del siguiente enlace.

miércoles, 20 de mayo de 2015

Fechas vacaciones

Cómo saber si se solapan las vacaciones de los trabajadores de una empresa.

Mostramos un caso donde se ve de forma gráfica si los periodos vacacionales se solapan o no.

Puede descargar el archivo de Excel siguiente.


Pulse la tecla F9 de recálculo manual para ver cómo cambia la simulación.


Se ve repetido el informe. Arriba todas las barras son azules. Abajo se ha marcado la zona de solapamiento en color naranja.

Los colores salen usando Formato condicional. La fórmula del Formato condicional para la celda F3 es la siguiente.

  • =Y(F$1>=$D3;F$1<=$E3)


Si se cumple esa condición se pondrá de color azul. Luego se copia o extiende ese formato a todo el rango F3:VN17.

Para los colores de la tabla inferior usamos dos Formatos condicionales para la celda F20 que son los de las dos fórmulas siguientes.

  • Para el azul  =Y(F$1>=$D20;F$1<=$E20)
  • Para el naranja  =Y(F$1>=$D20;F$1<=$E20;SUMA(F$3:F$17)>1)
Luego se copia o extiende el formato condicional al rango F20:VN34.

lunes, 18 de mayo de 2015

Agrupa tablas

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.



Puede descargar el siguiente archivo de Excel que contiene el caso desarrollado en el vídeo.

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