martes, 7 de abril de 2015

Días por meses

Descargue el archivo siguiente:
Dada una fecha inicial y una fecha final vamos a calcular cuantos días hay en cada mes de entre los meses de ese intervalo. Por ejemplo, entre el 20-enero-2016 y el 5-abril-2016 el resultado sería el siguiente:
  • para enero-2016: 12 días
  • para febrero-2016: 29 días ya que es bisiesto
  • para marzo-2016: 31 días
  • para abril-2016: 5 días
En este cálculo se incluyen los dos extremos, esto es, se incluye el día inicial y el día final. El número de días totales es de 77 que se obtienen en Excel restando el día final memos el día inicial y sumando 1. Se ha de sumar 1 ya que hemos decidido incluir los dos extremos.




Esto se puede calcular con fórmulas de Excel, lo cual hacemos en la Hoja2 de archivo proporcionado, pero también hemos querido crear una función personalizada, una macro, que calcule esto.

La función es la siguiente 

=dias_del_mes(fecha inicial;fecha final;mes)

Tiene tres argumentos, los dos primeros son obvios, y en el tercero se ha de indicar una fecha correspondiente al mes que queremos analizar. Da igual la fecha que usemos, lo importante es el mes. Así, por ejemplo, si deseamos calcular los días correspondientes al mes de enero-2016 pondremos cualquier fecha válida de ese mes, tanto da poner el día 1, el 31 o cualquier otro de enero de 2016.

La macro es la siguiente.


Function dias_del_mes(fechaInicio, fechaFin, fechaMes)
Dim inicioMes As Date
Dim finMes As Date
finMes = Application.WorksheetFunction.EoMonth(fechaMes, 0)
'veamos una forma curiosa de calcular el inicio de mes correspondiente a fechaMes
inicioMes = Application.WorksheetFunction.EoMonth(finMes - 45, 0) + 1
If fechaInicio >= inicioMes And fechaInicio <= finMes Then
  dias_del_mes = finMes - fechaInicio + 1
ElseIf fechaInicio < inicioMes And fechaFin > finMes Then
  dias_del_mes = finMes - inicioMes + 1
ElseIf fechaFin >= inicioMes And fechaFin <= finMes Then
  dias_del_mes = fechaFin - inicioMes + 1
Else
  dias_del_mes = 0
End If
End Function

martes, 16 de diciembre de 2014

Tabla Dinámica: cambiar contar por sumar

Descargue el fichero 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.


martes, 30 de septiembre de 2014

Bienvenido al Curso de Excel Avanzado

Bienvenido al curso gratuito de Excel Avanzado que se imparte mediante la plataforma Miriadax. Los enlaces a los archivos de Excel y los vídeos estarán disponibles en este Blog, lo cual nos permitirá dar continuidad a la comunidad que se forme, incluso después de haber concluido el curso. Se irán añadiendo nuevos contenidos a lo largo del tiempo. Esto convierte al curso en un entorno dinámico de formación donde puedes participar añadiendo tus comentarios al final de cada entrada.




Disponemos en Twitter de un hashtag para el curso de Excel Avanzado. Es el siguiente.


Mi nombre de usuario en Twitter es el siguiente.



Nota

El curso actualmente, septiembre de 2014, está en fase de INSCRIPCIÓN. Os podéis apuntar todos los interesados, o recomendarlo a vuestros contactos. Aún no tenemos fecha de inicio. Cuando ya se sepa la fecha desde la plataforma Miriadax se enviará un correo a todos los inscritos. Mientras llega la fecha de inicio puedes disfrutar del material del curso desde este blog de excelavanzado.com. Visita los enlaces a los módulos, luego a las entradas del blog que tratan los diferentes temas. Finalmente puedes ver los vídeos que se han colgado en YouTube.

En el curso usaremos la versión 2010 de Excel en un computador con sistema operativo Windows. Puedes ir preparando tu equipo ya que lo interesante no es solo que veas los vídeos sino que también puedas ir reproduciendo los pasos que ves en los ejemplos y casos prácticos que se van desarrollando. Puedes pausar el vídeo tantas veces como sea necesario y realizar tu mismo los pasos de los ejercicios.

Te contaré un secreto. Preparando los 12 módulos del curso yo también he aprendido muchas cosas de Excel. Y es que se trata de un programa fantástico, mucho más extenso de lo que imaginamos. Se podría decir que es todo un universo, y gracias a los usuarios que comparten en Internet sus conocimientos, está al alcance de todos.

viernes, 26 de septiembre de 2014

Separar en varias celdas un texto

En Excel se puede escribir un texto en una sola celda que ocupe varios párrafos. Cuando estamos escribiendo y queremos crear un nuevo párrafo dentro de la misma celda no tenemos que pulsar ENTER ya que en ese caso la celda se valida y no nos deja seguir escribiendo el segundo párrafo.

Lo que debemos hacer para pasar al sigueinte párrafo es pulsar ALT+ENTER. Esto es, debemos pulsar la tecla ALT y simultáneamente la tecla ENTER, y así pasamos al segundo párrafo y podemos seguir escribiendo.

Supongamos que tenemos una única celda con dos o más párrafos y deseamos que cada párrafo ocupe una celda diferente. Esto se puede hacer de forma manual de la siguiente forma.

  1. Editamos la celda que contiene varios párrafos. Para editarla debemos estar situados en ella y pulsar la tecla de función F2. También se edita pulsando doble click sobre la propia celda.
  2. Seleccionamos todo el contenido de la celda
  3. Pulsamos Control+C para copiar al portapapeles
  4. Nos situamos en cualquier celda vacía y que debajo de ella también tenga algunas celdas vacías tantas. Vamos a necesitar tantas celdas vacías como párrafos contenga nuestro texto.
  5. Pulsamos Control+V que pega el contenido del portapapeles
Con este método hemos conseguido de forma manual separar los diferentes párrafos en y que cada uno de ellos ocupe una celda diferente.

Ahora queremos hacer esto de forma automática mediante una Macro de Excel, creando un código VBA (Visual Basic para Aplicaciones).

La macro es la siguiente.

Sub Separa()
Dim toma As String, linea As String
Dim largo As Integer, i As Integer, s As Byte
Dim caracter As String * 1
Dim fila As Long, columna As Integer
Dim A() As Integer
fila = ActiveCell.Row
columna = ActiveCell.Column
toma = ActiveCell.Value
largo = Len(toma)
ReDim A(largo)
For i = 1 To largo
    caracter = Mid(toma, i, 1)
    If caracter = Chr(10) Then
        s = s + 1
        A(s) = i
    End If
Next i
If s = 0 Then MsgBox ("En la celda activa no se detecta ningún ENTER"): End
A(s + 1) = largo + 1
For i = 0 To s
    linea = Mid(toma, A(i) + 1, A(i + 1) - A(i) - 1)
    Cells(fila + i + 1, columna) = linea
Next i
End Sub

Para que funcione correctamente debemos situarnos previamente en una celda que contenga dos o más párrafos de texto creados pulsando ALT+ENTER para separar cada uno de ellos.

La macro lo que hace es respetar la celda en la que estamos sin cambiarla y justo debajo de ella utiliza las celdas que existen para poner en cada una de ellas uno de los párrafos. Se necesitan tantas celdas vacías debajo de la celda que contiene el texto como párrafos existan.

Por ejemplo, si tenemos una única celda con el texto siguiente:

Linea1
Linea2
Linea3
Linea4

La macro detecta que existen 27 caracteres. Esto se puede medir con la función =LARGO
Los caracteres son:

  • 6 de la primera línea
  • 6 de la segunda líena
  • 6 de la tercera líena
  • 6 de la cuarta línea
  • 3 retornos de carro
Los retornos de carro son las veces que hemos pulsado ALT+ENTER y el código de carácter que llevan en CHR 10.





Al final la macro crea debajo de la celda que contiene ese texto (celda amarilla) cuatro celdas con el texto separado correspondiente a cada uno de los 4 párrafos.

Puede descargar el siguiente fichero de Excel con la macro y un ejemplo para practicar su uso.



sábado, 20 de septiembre de 2014

Arrays

Un Array es un conjunto de variables.
  • Si son de una dimensión hablamos de vectores
  • Si son de dos dimensiones hablamos de matrices
  • Si son de más de dos dimensiones hablamos de matrices multidimensionales
En general, denominaremos matriz a cualquier Array sea de la dimensión que sea.

Por ejemplo, definimos la matriz A que es un vector de 5 componentes. Se define así:

Dim A(5) as Integer

Con ello hemos definido sus 5 componentes de tipo Integer. Los componentes son:
  • A(1)
  • A(2)
  • A(3)
  • A(4)
  • A(5)
Esto es un vector de cinco componentes, donde cada uno de ellos puede contener un valor de tipo integer.

Una matriz de dos dimensiones, rectangular de 5 filas y 7 columnas se define así:

Dim A(5,7) as integer

En este ejemplo todos los componentes son de tipo integer y serían los siguientes:
  • A(1,1)  A(1,2)  A(1,3)  A(1,4)  A(1,5)  A(1,6)  A(1,7)
  • A(2,1)  A(2,2)  A(2,3)  A(2,4)  A(2,5)  A(2,6)  A(2,7)
  • A(3,1)  A(3,2)  A(3,3)  A(3,4)  A(3,5)  A(3,6)  A(3,7)
  • A(4,1)  A(4,2)  A(4,3)  A(4,4)  A(4,5)  A(4,6)  A(4,7)
  • A(5,1)  A(5,2)  A(5,3)  A(5,4)  A(5,5)  A(5,6)  A(5,7)

Primer Vídeo

  • ¿Qué es un array?
  • Arrays, Vectores, Matrices.


Segundo Vídeo

  • ReDim para redimensionar matrices.
  • Randomize para barajar la semilla del generador de números aleatorios.



Tercer Vídeo

Ordenar con macro.



Cuarto Vídeo

  • Ordenar aleatorios en VBA
  • Uso de matrices
  • Tiempo de cálculo



Quinto Vídeo

  • Usamos la función de VBA Array
  • Dejamos valores de un Array en fila y en columna
  • Capturamos esos valores con otro Array
  • Una macro llama a otra macro con parámetros



Caso práctico

Puede ver un ejemplo del uso de matrices y ReDim en un caso práctico que nos permite separar el texto de una celda que está escrito en varios párrafos y que finalmente quede cada párrafo en una celda separada. Está en el siguiente enlace:

Para saber más



El archivo utilizado en los vídeos se puede descargar del siguiente enlace.