jueves, 28 de mayo de 2015

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.



jueves, 21 de mayo de 2015

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.

miércoles, 20 de mayo de 2015

Fechas vacaciones

Archivo de Excel utilizado: FechasVacaciones.xlsx

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.

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

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

El evento SelectionChange

Archivo de Excel utilizado: SelectionChange.xlsm

Vamos a crear un caso práctico sencillo que nos permitirá comprender cómo funciona el evento SelectionChange. Primero veamos lo que es un Evento.

Programación por Eventos

En muchos de los lenguajes de programación modernos existe la denominada programación por eventos que permite lanzar procedimientos cuando se detecta que ha sucedido un evento. Los eventos pueden ser de muchos tipos y dependen del lenguaje utilizado. Por ejemplo, un evento puede ser pulsar una vez el ratón, o pulsar dos veces sobre el ratón, o pulsar sobre el botón derecho, o escribir en una celda, o actualizar la hoja de cálculo, o ir a otra hoja. En otros lenguajes, un evento podría ser pasar el cursor del ratón sobre un banner que hace que la publicidad que contiene se agrande o que comience un vídeo o un sonido.

En Excel se están aumentando los eventos disponibles en cada versión, en especial los relacionados con Tablas dinámicas. En Excel 2010 son los siguientes.


El evento Worksheet.SelectionChange

El evento Worksheet.SelectionChange nos permite lanzar un procedimiento cada vez que seleccionamos un rango de celdas diferente.

Caso práctico

El caso práctico que planteamos consiste en proporcionar en una celda la suma de los valores numéricos que contengan el rango que seleccionemos. De tal forma que al ir cambiando la selección inmediatamente y sin lanzan ninguna macro por nuestra parte en la celda D4 (de color amarillo) nos de continuamente el valor de la suma de los elementos seleccionados.



La programación del evento es la siguiente que se debe colocar en la zona de código correspondiente a la Hoja1 que es donde están los elementos que deseamos sumar.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim celda As Range
Dim suma As Double
For Each celda In Target
  If IsNumeric(celda) Then
    suma = suma + celda.Value
  End If
Next
[D4] = suma
End Sub

domingo, 17 de mayo de 2015

Eliminar espacios en blanco

Archivo de Excel utilizado: apellidos.xlsm

Es frecuente que necesitemos eliminar espacios en blanco especialmente cuando hemos importado datos de algún sistema de reporting y hemos utilizado Datos > Texto en columnas.

Excel dispone de dos funciones que nos permiten eliminar espacios de una cadena alfanumérica. Son las siguientes.

=Espacios(texto)


Función de la categoría Texto. Quita todos los espacios del texto excepto los espacios individuales entre palabras. Esto es, elimina los espacios que existen antes y después de una frase y de los interiores deja uno entre palabras, pero si existe más de uno solo deja uno.

Ejemplo


Tenemos el siguiente texto:     Hola Madrid  y  Lisboa   

Contiene tres espacios al inicio de la frase, tres al final, uno antes de Madrid y una pareja de espacios antes de la 'y' otra pareja después.

Aplicando la función

=Espacios("   Hola Madrid  y  Lisboa   ")

obtenemos lo siguiente

Hola Madrid y Lisboa

Es una frase normal donde únicamente existen tres espacios situados entre palabras. Por tanto, se han eliminado 3 espacios al inicio, 3 al final, y 2 interiores.


=Sustituir(Texto;Texto_original;Texto_nuevo;[Núm_de_ocurrencia])


Reemplaza el texto existente con texto nuevo en una cadena.

Ejemplo


Disponemos de la siguiente frase en la celda A1

Balance de Enero y Cuenta de Resultados de Enero

Deseamos cambiar el mes de Enero por el de Febrero, para ello podemos usar la función Sustituir

=Sustitutir(A1;"Enero";"Febrero")

El cuarto argumento Núm_de_ocurrencia es optativo y si no se indica ningún valor se sustituyen todos los Eneros que se encuentren.

El resultado obtenido será el siguiente

Balance de Febrero y Cuenta de Resultados de Febrero


El carácter 160 en Excel


El carácter 160 en Excel se ve como un espacio en blanco pero no lo es, ya que el espacio en blanco se corresponde con el carácter 32. Cuando importamos texto o datos de Internet o de algún sistema de contabilidad, facturación, logística, etc, pudieran importarse caracteres que aparentemente son espacios en blanco pero en realidad no lo son ya que se trata del famoso carácter 160.

Reemplazar


Lo que procede en muchos casos es sustituir ese carácter por un verdadero espacio en blanco, o en otras ocasiones, lo que interesa es eliminarlo completamente.

Para sustituirle podemos usar Buscar y Reemplazar, por ejemplo pulsando Control+L, y así poder reemplazar el carácter 160 por un verdadero espacio en blanco.

El carácter 160 se puede generar en una celda escribiendo la siguiente fórmula.

=CARACTER(160)

También se puede obtener pulsando Alt+255. El número se debe marcar con el teclado numérico mientras se mantiene presionada la tecla Alt.

Macro 1


Otro método para reemplazar todo es usar la siguiente macro.

Como las macros no se puden deshacer es conveniente que grabe antes de lanzar la macro ya que el resultado de la macro sobre nuestra hoja aún no está probado.


Sub Reemplazalo()
Cells.Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart, SearchOrder _
  :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub



Macro 2


Existe una segunda macro que es posible que le guste más ya que se trata de una función programada en VBA que lo que hace es ir recorriendo todos y cada uno de los caracteres de una celda y elimina todos los caracteres 160 y también todos los caracteres 32. Esto es, deja el contenido de la celda sin ningún espacio. La función es la siguiente.


Function EliminaEspacios(texto As String) As String
Dim i As Byte
Dim n As Byte 'es la longitud de la cadena
Dim Letra As String * 1
Dim NewTexto As String 'nueva cadena sin espacios
n = Len(texto)
For i = 1 To n
  Letra = Mid(texto, i, 1)
  'chr(32) es un espacio en blanco
  'chr(160) es similar a un espacio en blanco en Excel
  If Letra <> Chr(160) And Letra <> Chr(32) Then
    NewTexto = NewTexto & Letra
  End If
Next i
EliminaEspacios = NewTexto
End Function


Caso práctico


Paso 1

Vamos a tomar un listado de los apellidos más frecuentes en España de la siguiente página web. Los tomamos simplemente seleccionando y copiando y pegando en Excel.

El resultado lo encontrará en la Hoja1 del fichero de Excel que hemos dejado al inicio de este post.

Paso 2

En la Hoja2 separamos en tres columnas lo importado usando para ello Datos > Texto en columnas.


Si probamos con "Delimitados" y pedimos que use como delimitador el 'Espacio' podemos comprobar que no conseguimos nuestro objetivo ya que no se detecta ningún espacio. El motivo es que lo que parecen ser espacios en realidad se trata del carácter 160 que no es detectado como espacio.

Esto nos obliga usar la separación de columnas "De ancho fijo" obteniendo lo que se puede ver en la Hoja2.

Paso 3

Para eliminar las finas vacías y las filas donde únicamente aparece la letra del abecedario lo que hacemos es lo siguiente.

  • Seleccionamos todo el área de datos incluida cabecera
  • Pulsamos F5 que es Ir a... y pulsamos sobre el botón Especial
  • Elegimos la opción 'Celdas en blanco'
  • Eso lo que hace es seleccionar las filas que deseamos eliminar
  • Ahora vamos al menú Inicio > Eliminar > Eliminar filas de hoja
  • Con esto hemos eliminado las filas que no nos interesaban, las vacías y las del abecedario
  • El resultado lo puede ver en la Hoja3

Paso 4

En la Hoja4 vamos a utilizar la fórmula =EliminaEspacios(texto) para quitar tanto el carácter 160 como el carácter 32, que es el espacio en blanco, en caso de que lo hubiera.

Sitúese en la celda E2 y escriba la fórmula siguiente.

=EliminaEspacios(B2)

Copie esa fórmula a su derecha a las celdas F2 y G2.

Copie la celda E2:G2 y copie sus fórmulas hasta abajo, hasta la fila 1035.

Observe que los datos numéricos de la columna F no son verdaderamente numéricos. Son texto, se puede ver que están alineados al la izquierda. Para convertirlos en verdaderamente numéricos disponemos de dos métodos.

Método 1. Cambien la fórmula de la celda F3 por la siguiente y cópiela hacia abajo.
=VALOR(EliminaEspacios(C3))

Método 2. Cambien la fórmula de la celda F3 por la siguiente y cópiela hacia abajo.
=--EliminaEspacios(C3)

Si todo ha funcionado bien ya tiene la base de datos limpia de espacios y limpia del famoso carácter 160.

Paso 5

Ahora solo falta copiar la base de datos, que son las columnas E, F y G, y pegarlas con pegado especial valores en la Hoja5.


Otra posibilidad

Otra posibilidad para resolver este caso práctico consiste en importar de página web la tabla de apellidos y luego usar Datos > Texto en columnas. La ventaja de este método es que en este caso no necesitamos usar la macro que quita el carácter 160 ya que al vincular con página web este carácter no nos da problemas. Finalmente convendría romper el vínculo.



viernes, 15 de mayo de 2015

Completar los espacios en blanco

Archivo de Excel utilizado: completar.xlsm

En esta entrada vamos a utilizar en tres casos la tecla F5 que es Ir a y luego el botón Especial...

Hoja1

Utilizaremos dos métodos para completar los espacios en blanco que hay debajo de ciertas palabras.



El resultado obtenido es el siguiente.



También podemos utilizar Macros.



Para que la macro funcione correctamente primero se ha de seleccionar el rango E4:E23.
Observe el código que con Set Rng = Secection lo que hacemos es recogen en la variable objeto Rng el rango que previamente teníamos seleccionado. De este rango deseamos obtener la fila y columna de inicio que es la fila 4 y columna 5, y el número de celdas que son 20. Ahora ya podemos trabajar con estos valores y meternos en un For...Next que nos permitirá recorrer el rango y analizar si la celda está vacía o no. Si está vacía hacemos que tome el valor de la previa.

Sub Completa()
Dim Rng As Range
Dim FilIn As Long 'Fila de inicio del rango
Dim ColIn As Integer 'Columna de inicio del rango
Dim NumCeldas As Long
Dim Fila As Long
Set Rng = Selection
FilIn = Rng.Application.ActiveCell.Row
ColIn = Rng.Application.ActiveCell.Column
NumCeldas = Rng.Cells.Count
For Fila = FilIn To FilIn + NumCeldas - 1
  If Cells(Fila, ColIn).Value = Empty Then
    Cells(Fila, ColIn).Value = Cells(Fila - 1, ColIn).Value
  End If
Next Fila
End Sub

Hoja2

Deseamos obtener un resumen de la siguiente tabla.


 El resumen que pretendemos obtener es el siguiente.



Para conseguir nuestro objetivo realizaremos los dos procedimientos siguientes. Con el primero obtendremos un listado de ciudades y con el segundo las sumas que corresponden a cada ciudad.



Hoja3

Para eliminar filas en blanco ya se realizó un procedimiento con macro en otro post cuyo enlace es el siguiente.

En esta ocasión vamos a eliminar filas sin usar una macro. Existe una limitación de este método que consiste en que eliminará la fila donde todas las celdas estén vacías, pero también se eliminarán las filas que contengan alguna celda vacía aunque no queramos. Esto es, si alguna fila tiene datos y no deseamos eliminarla pero contiene una celda vacía, simplemente por existir una celda vacía eliminará la fila completa, aunque esta tenga otros datos.