FERRETERIAS BETO: EXCEL AVANZADO

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.

formato_condicional.xlsx

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.

ranking_ventas.xlsx

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.

ReordenarTabla.xlsx

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.

FechasVacaciones.xlsx

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.

agrupa_tablas.xlsm

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

FERRETERIAS BETO

domingo, 21 de junio de 2015

EXCEL AVANZADO

ExcelAvanzado.com