viernes, 1 de agosto de 2014

Módulo 0: Presentación

Presentamos el curso gratuito de Excel Avanzado impartido por la Universidad Rey Juan Carlos mediante la plataforma Miriadax.

Puedes ver el vídeo de presentación.



Si deseas inscribirte en el curso puedes hacerlo gratuitamente mediante el siguiente enlace.

INSCRIPCIÓN

jueves, 17 de julio de 2014

Vincular entre Excel y Word o Power Point

Vincular es más que copiar y pegar.

Veamos en el siguiente vídeo cómo se vincula una tabla o un gráfico entre Excel y Word y Power Point.


Puede descargar el fichero de Excel: excelavanzado_vincular.xlsx

viernes, 13 de junio de 2014

Sumar según el color de la tinta en Excel

Descargue el fichero: excelavanzado_color_tinta.xlsm

Vamos a sumar y contar según el color de la fuente de un texto. En otra entrada anterior de este Blog ya vimos cómo contar según el color de fondo de las celdas. Es la siguiente:
Ahora vamos a programar una pequeña función en VBA que nos da el código de color según una codificación de colores que va entre 1 y 56. Esto supone que no se distingue entre colores parecidos.


La función programada como macro en Excel es la siguiente.

Function ColorTinta(celda As Range) As Byte
ColorTinta = celda.Font.ColorIndex
End Function


domingo, 11 de mayo de 2014

Ocultar columnas marcadas con un color

Descargue el fichero ocultar_columnas_amarillas.xlsm

Disponemos de una tabla en la que la fila 5 hace de cabecera y en ella hemos marcado de color amarillo ciertas celdas cuyas columnas nos gustaría ocultar. Hemos desarrollado una macro en VBA para Excel que logra ocultar las columnas amarillas.

Luego hemos creado otra macro que muestra todas las columnas. Se ejecutan con sendos botones que figuran al inicio de la hoja.




Sub Oculta_columnas_amarillas()
Dim n As Integer 'ultima columna
Dim R As Range
Dim i As Integer
Rem utiliza la fila 5 para localizar las amarillas
Range("A5").Select
Set R = ActiveCell.SpecialCells(xlLastCell)
n = R.Column
For i = 1 To n
    If Cells(5, i).Interior.ColorIndex = 6 Then
        Columns(i).Hidden = True
    End If
Next i
End Sub
Sub Muestra_columnas()
Cells.EntireColumn.Hidden = False
Range("A5").Select
End Sub

viernes, 2 de mayo de 2014

Media Móvil a intervalos fijos

Puede descargar el archivo de ejemplo denominado MM10.xlsm

Si disponemos de una serie de datos, por ejemplo, 200 y deseamos hacer una media móvil de 10 (MM10) lo que haremos es tomar el promedio de los 10 primeros y luego para obtener el siguiente valor de la media móvil lo que hacemos es eliminar de nuestros cálculos el primero y tomar uno nuevo, y así sucesivamente. Esto en Excel se resuelve de una forma muy sencilla con la función PROMEDIO. Véase el ejemplo siguiente.

Primero generamos aleatorios en la columna B con la expresión:
=ELEGIR(ALEATORIO.ENTRE(1;4);ALEATORIO.ENTRE(1;9);ALEATORIO.ENTRE(10;99);ALEATORIO.ENTRE(100;999);ALEATORIO.ENTRE(1000;1999))

y luego los convertimos en valores con copiar y pegar con pegado especial valores.


En la celda C11 calculamos la primera media móvil de 10 valores (MM10) con la expresión:

=PROMEDIO(B2:B11)

Observe que el rango B2:B11 abarca los 10 primeros valores de la columna B y que no se ponen con dólares, por lo que al ser libres y copiar hacia abajo la fórmula el siguiente valor de la media móvil, situado en la celda C12, hace el promedio del rango B3:B12. Esto supone haber movido hacia abajo el rango una posición, pero siguen siendo 10 valores ya que estamos haciendo una media móvil de 10.

Realizar una media móvil es así de sencillo en Excel, pero ahora nos proponemos otro reto que consiste en tomar la media de los 10 primeros valores de la columna B, y anotarlos por ejemplo en la columna F, y luego tomar los siguientes 10 valores pero sin incluir a los anteriores, simplemente los que van de la posición 11 a la 20, y lo volvemos a anotar en la columna F, y así sucesivamente. Como tenemos 200 datos en la columna B y deseamos tomarlos de 10 en 10 al final tendremos 200/10 = 20 valores anotados en la columna F.

Esto se puede conseguir de tres formas:
  • Método 1: de forma manual.
  • Método 2: con fórmula
  • Método 3: con macro

Método 2

En la colman F se obtiene la media de cada 10 valores de la columna B. Observe que se corresponden los valores obtenidos en la columna F con los marcados en azul.


La fórmula de la celda F2 se copia hacia abajo y es la siguiente.

=PROMEDIO(INDIRECTO("F"&E2*10-8&"C2";0):INDIRECTO("F"&E2*10+1&"C2";0))

Para marcar el color azul hemos utilizado Formato Condicional. La condición se ha aplicado a la celda C11 y luego se ha extendido hacia abajo. La fórmula de la condición es la que nos detecta los múltiplos de 10.

=RESIDUO(A11;10)=0