martes, 16 de diciembre de 2014

Tabla Dinámica: cambiar contar por sumar

Archivo de Excel utilizado: 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.


viernes, 26 de septiembre de 2014

Separar en varias celdas un texto

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

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 siguiente 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.

Separar por un carácter


En la Hoja2 hemos creado otro ejemplo donde el usuario decide el carácter separador. Por ejemplo, podemos separar un texto por la coma.

En la celda D2, de color rosa, ponemos el carácter separador, por ejemplo una coma. En la celda D3 se calcula el código que corresponde a ese carácter. La coma tiene el código 44.

En la celda amarilla B5 escribimos el texto que contiene una o varias comas, o el carácter separador que nos interese. Con el cursor situado en B5 pulsamos el botón que lanza la macro y se producirá la separación del texto en varias celdas.


Observamos que Dato1 ha quedado bien, pero que Dato2 y los siguientes tienen un espacio en blanco al inicio. Si deseamos que este espacio inicial no aparezca lo que haremos es usar la otra macro que hemos habilitado que se lanza pulsando en el icono de la goma de borrar.


En esta ocasión no hemos lanzado las macros con los botones habituales, hemos optado por un control ActiveX para la primera y una imagen para la segunda macro.

La macro que se lanza con el botón rotulado como "Separa por carácter" es un botón de comando (control ActiveX). Para editarlo tenemos que ir a Programador y pulsar sobre el icono de escuadra para entrar en modo diseño y luego ir a las propiedades, donde podremos cambiar ligeramente el color de fondo 'BackColor'.

Estando en modo diseño pulsamos con el botón derecho del ratón sobre el botón y elegimos 'Ver código'. La macro es un evento de tipo click.


Private Sub CommandButton1_Click()
  Call SeparaPorCaracter
End Sub


No olvidar pulsar de nuevo sobre la escuadra para salir del modo diseño.

La segunda macro se lanza pulsando sobre el icono de la goma de borrar. Conseguir asociar una macro a una imagen es más sencillo ya que simplemente se ha de pulsar sobre el imagen con el botón derecho del ratón y elegir 'Asignar macro'.

sábado, 20 de septiembre de 2014

Arrays

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

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

viernes, 19 de septiembre de 2014

Variables Objeto

Los ficheros de Excel utilizados en los vídeos se pueden descargar de los siguiente enlaces.

El lenguaje de programación VBA (Visual Basic for Applications) en Excel es un lenguaje orientado a objetos. Los objetos son:
  • libros (Worksheet)
  • rangos (Range)
  • filas (Row)
  • columnas (Column)
  • tablas dinámicas (PivotTable)
  • gráficos (Chart)
  • entre otros muchos, ya que prácticamente todo es un objeto
  • incluso la propia aplicación Excel (Application)

Primer Vídeo

  • Definir variables objeto
  • Establecer su valor con SET
  • Bucle For Each ... Next


Segundo Vídeo

Creamos una hoja Index al inicio y después 100 hojas más que vamos renombrar de forma automática con una macro que se lanzará al elegir de un desplegable un mes de entre una lista con los doce meses del año.


jueves, 18 de septiembre de 2014

Trabajar con fechas y horas

Archivo de Excel utilizado: excelavanzado_fechas.xlsx

Excel permite trabajar bastante bien con fechas y horas. Veamos algunas funciones que permiten este tratamiento.

Primer Vídeo

Tratamiento de fechas.



Segundo Vídeo

Tratamiento de horas, minutos y segundos.



Tercer Vídeo

Determinación del día de la semana.


Cuarto Vídeo

Calendario perpetuo.


Para saber más

Si quiere ampliar otros aspectos del tratamiento de fechas y horas puede visitar estos enlaces.



martes, 16 de septiembre de 2014

Programación VBA 1

Introducción a la programación en Visual Basic para Aplicaciones (VBA). Variables, Bucles, Condicionales, funciones definidas por el usuario

  1. Mi primera macro
  2. Escribir en una hoja desde una macro
  3. Nuevas formas de escribir con macro
  4. Lanzar macros con iconos y botones
  5. Desplazamiento en una macro con Offset
  6. Macros con With ... End With
  7. Variables en VBA
  8. InputBox y MsgBox
  9. Bucles del tipo FOR...NEXT
  10. Lluvia
  11. Condicionales
  12. Estructuras repetitivas: Do While ... Loop
  13. Función Personalizada
  14. Programación por eventos: Fechador

Condicionales

Archivo de Excel utilizado: excelavanzado_condicionales.xlsm

El condicional IF nos permite redirigir al flujo del programa en función de si se cumple o no una condición.

If Condición Then
   sentencia 1
   sentencia 2
   ... ... ...
   sentencia n
End If
Cuando deseamos establecer unas sentencias para cuando no se cumple usamos ELSE.

If Condición Then
   sentencia 1
   sentencia 2
Else
   sentencia 3
   sentencia 4
End If

Primer Vídeo

  • Condicional IF con ELSE
  • Programación por eventos.
  • TARGET.Address
  • Evento Change

Segundo Vídeo

Operadores lógicos NOT, AND, OR.



Tercer Vídeo

Condicionales con ELSEIF.
Else If se escribe junto Elseif.




Cuarto Vídeo

Condicionales del tipo Select Case.


lunes, 15 de septiembre de 2014

Bucles del tipo FOR...NEXT

Archivo de Excel utilizado: excelavanzado_fornext.xlsm

La estructura repetitiva FOR...NEXT nos permite realizar las instrucciones interiores a ella tantas veces como se indique. Es ideal cuando se sabe el número de ciclos que hemos de realizar. Se utiliza una variable auxiliar que podemos denominar con el nombre que queramos, mientras no sea una palabra reservada, y que inicialmente llamaremos i por costumbre.

El siguiente es un ejemplo de bucle For Next que se repite 10 veces.

For i=1 to 10
   MsgBox i
Next i

En esta macro de ejemplo las líneas de código interiores al For Next se repetirán 10 veces. Para el primer ciclo la variable i tomará el valor 1, para el segundo ciclo tomará el valor 2, y así sucesivamente hasta llegar al último ciclo donde i toma el valor 10.

La línea de código que hemos puesto en este ejemplo es un MsgBox cuya acción es lanzar una ventana emergente que irá mostrándonos los números del 1 al 10 a medida que la variable i va tomando esos valores. Por tanto, tendremos que pulsar 10 veces sobre el botón Aceptar para ir pasando a las siguientes ventanas.

Primer Vídeo

Option Explicit
Sub Lista()
Dim i As Byte
For i = 1 To 10
    Cells(i, 1) = i
Next
End Sub



Segundo Vídeo

  • RND() para generar números aleatorios
  • INT poma la parte entera
  • Inicializar variables
  • Contador
  • Acumulador

Sub Lista2()
Dim i As Long
For i = 1 To 10000
    Cells(i, 2) = i
Next
End Sub
Sub Lista3()
Dim i As Long
For i = 1 To 10
    Cells(i + 4, 3) = i
Next
End Sub
Sub Lista4()
Dim i As Long, fila As Long
fila = 5 'Inicializamos la variable
For i = 1 To 10
    Cells(fila, "D") = i
    fila = fila + 1 'Contador
Next
End Sub
Sub Lista5()
Dim i As Byte, s As Integer, ale As Byte
s = 0 'Inicializamos la variable
For i = 1 To 10
    'generamos un nº aleatorio entre 1 y 100
    ale = Int(Rnd() * 100) + 1
    Cells(i + 4, "E") = ale
    s = s + ale 'Aculador
    Cells(i + 4, "F") = s
Next i
End Sub

Tercer Vídeo

  • Fecha y hora del sistema con NOW
  • NumberFormat para formatear una los valores de una celda
  • Application.ScreenUpdating = False
  • Application.ScreenUpdating = True

Sub Cronometro()
Dim i As Long
Worksheets("Hoja2").Activate
Range("C4") = Now
Application.ScreenUpdating = False
For i = 1 To 10000
    Cells(i, 2) = Int(Rnd() * 50000) + 1
Next i
Application.ScreenUpdating = True
Range("C5") = Now
Range("C6") = Range("C5") - Range("C4")
Range("C6").NumberFormat = "[hh]:mm:ss"
End Sub


Cuarto Vídeo

  • Bucles For...Next anidados
  • Llamar a una macro con CALL

Sub Multiplica()
Dim i As Byte, j As Byte
Worksheets("Hoja3").Activate
'Call Borra
Borra
For i = 1 To 10
    For j = 1 To 10
        Cells(i + 1, j + 1) = i * j
    Next j
Next i
End Sub
Sub Borra()
Range("B2:K11").ClearContents
End Sub


Quinto Vídeo

  • Ejecución de bucles For...Next con un paso distinto de 1 usando STEP
  • Paso negativo
  • Reducción de variables para aligerar el código




Sexto Vídeo

  • Acumular con macro. Tres métodos.
  • Uso de Offset (desplazamiento) para trabajar usando varias celdas de dos formas:
    • sin desplazar el cursor
    • desplazando el cursor

viernes, 12 de septiembre de 2014

InputBox y MsgBox en macros de Excel

El archivo de Excel utilizado en los ejemplos es el siguiente. También se incluye un enlace a un documento con la ayuda de InputBox y MsgBox.

InputBox y MsgBox son dos funciones que permiten interactuar con el usuario al ejecutar una macro en Excel.

Primer Vídeo

Veamos MsgBox.



Segundo Vídeo

Veamos InputBox.



Tercer Vídeo

Podemos utilizar MsgBox para que muestre un mensaje y aparezcan uno o varios botones como los siguientes.
  1. Aceptar  (botón único)
  2. Si, No
  3. Si, No, Cancelar
  4. Anular, Reintentar, Omitir
  5. Aceptar, Cancelar
  6. Reintentar, Cancelar

Iconos que podemos elegir que aparezcan junto al mensaje de texto.
  1. Crítico
  2. Interrogación
  3. Advertencia
  4. Información

miércoles, 10 de septiembre de 2014

Variables en VBA

Archivo de Excel utilizado: excelavanzado_variables.xlsm

En la programación de macros para Excel en VBA (Visual Basic para Aplicaciones) es muy recomendable el uso de variables para trabajar. Normalmente simplifican la compresión del código y en otros casos son imprescindibles.

Primer Vídeo

Veamos los tipos de variables. Disponemos de un documento compartido con las diferentes variables que podemos utilizar al programar macros. Es un documento de Google Drive que se puede ver en el siguiente enlace.



Segundo Vídeo

Veamos una macro que define las variables, asigna valores a esas variables y finalmente usa los valores de las variables para escribir en nuevas celdas. Se ejecuta la macro paso a paso pulsando F8. 
Aprenderemos a usar la "Ventana Locales" donde se muestra el valor de cada variable a medida que se ejecuta la macro paso a paso.



Tercer Vídeo

Definir o dimensionar las variables en Excel con DIM es optativo pero muy recomendable.

Con OPTION EXPLICIT conseguimos auto-obligarnos a definir previamente las variables utilizadas.

Para que automáticamente se añada OPTION EXPLICIT al inicio de cada nuevo Módulo podemos ir a Herramientas, Opciones y marcar la casilla correspondiente a "Requerir declaración de variables".



jueves, 4 de septiembre de 2014

Macros con With ... End With

Archivo de Excel utilizado: excelavanzado_with.xlsm

La estructura With ... End Whit permite resumir varias líneas de programa cuando existen partes repetitivas. Sirve para ejecutar una serie de acciones sobre un mismo objeto, sin tener que repetir toda su jerarquía.


Sub Saluda()
Worksheets("Hoja1").Activate
ActiveSheet.Range("B5").Value = "¿Cómo esta usted?"
ActiveSheet.Range("B5").Font.Bold = True
ActiveSheet.Range("B5").Font.Italic = True
ActiveSheet.Range("B5").Font.Color = RGB(255, 0, 0)
End Sub

Sub Saluda2()
Worksheets("Hoja1").Activate
' Estructura Whith...End With
With ActiveSheet.Range("B5")
    .Value = "¿Cómo esta usted?"
    .Font.Bold = True
    .Font.Italic = True
    .Font.Color = RGB(255, 0, 0)
End With
End Sub

La calculadora de colores se puede encontrar en el siguiente enlace.

Desplazamiento en una macro con Offset

Archivo de Excel utilizado: excelavanzado_offset.xlsm

La programación de macros en Excel se hace en VBA (Visual Basic for Application). Es un lenguaje de programación orientado a objetos. Ejemplos de objetos son los rangos, celdas, filas, columnas, gráficos, tablas dinámicas, entre otros muchos. Ya que prácticamente todo son objetos. Se encuentran jerarquizados siendo el superior Application que hace referencia a la propia aplicación Excel.

Offset significa desplazamiento y hemos de indicar cuantas filas hacia abajo y columnas a la derecha nos tenemos que desplazar. Si las filas son negativas indica que el desplazamiento es hacia arriba y si las columnas son negativas indica que nos desplazamos hacia la izquierda.

Offset(fila,columna)


miércoles, 3 de septiembre de 2014

Creación de Macros

En este módulo aprenderemos a crear macros con grabadora de forma sencilla sin necesidad de programar código. Las macros pueden ocupar siempre el mismo lugar dentro de la hoja de cálculo o bien ejecutarse desde la posición donde tengamos puesto el cursor. En este segunda caso se denominan macros de referencias relativas. También aprenderemos a depurar el código creado mediante la ejecución paso a paso pulsando F8. Veremos que la macro puede crear grabada en el libro en el que estamos trabajando o en el libro Personal. Veremos diferentes formas de lanzar la ejecución de las macros. Finalmente trataremos el tema de la seguridad en macros.

  1. Ficha Programador y ficha Desarrollador
  2. Macros con Grabadora
  3. Lanzar macros con iconos y botones
  4. Libro Personal
  5. Libro Personal en Excel
  6. Macros, iconos, seguridad y depuración paso a paso
  7. Grabación de un Formulario

Macros, iconos, seguridad y depuración paso a paso

Archivo de Excel utilizado: excelavanzado_macro_icono.xlsm

Dentro del tema de la programación de macros con grabadora y antes de introducirnos en la programación en VBA (Visual Basic for Applications) vamos a tratar tres temas de interés.
  • Lanzar macros con iconos
  • Seguridad en macros. Ubicaciones de confianza
  • Depuración de macros paso a paso pulsando F8

Libro Personal

También puede consultar esta otra entrada del blog que habla también sobre este tema.



El libro Personal en Excel es un libro muy especial (PERSONAL.XLSB) ya que todas las macros que se graben en él quedaran disponibles para cualquier libro que abramos.

Al grabar una macro con grabadora nos preguntan si deseamos que se grabe "En este Libro" o en el "Libro de macros Personal". Si se graba "en este libro" la macro únicamente estará disponible en ese libro, pero si se graba en el libro de macros Personal podremos invocar la macro desde cualquier libro.

Esto es posible ya que el libro de macros PERSONAL una vez creado se abre de forma oculta siempre que iniciemos la aplicación Excel. Podemos elegir que se abra no de forma oculta sino que se muestre y en ese caso también podemos aprovechar para convertirle en la platilla de inicio.


El libro PERSONAL.XLSB se graba en la siguiente ruta.
C:\Users\Adolfo\AppData\Roaming\Microsoft\Excel\XLSTART
El usuario en mi caso es Adolfo, siendo necesario que cada uno adapte la ruta a su propio usuario.
Esta es la ruta en Windows 8 y en Excel 2010. La ruta puede cambiar según la versión de Windows y la versión de Excel que se esté usando.

El fichero que se ha generado en mi ordenador es el siguiente.

Nota

Existe una entrada anterior que trata el tema del libro personal en Excel 2007.

sábado, 30 de agosto de 2014

Grabación de un Formulario

Archivo de Excel utilizado: excelavanzado_grabacion_formulario.xlsm

Veamos cómo crear un formulario y como incorporar los valores a una base de datos de forma automática con una macro que grabe los registros simplemente pulsando un botón. La macro que haremos no requiere programación de código y se realiza con la grabadora de macros.

Primer Vídeo

En este vídeo veremos cómo se crea el formulario usando una de estas dos herramientas.
  • Validación de datos
  • Controles de formulario

Segundo Vídeo

Realizaremos de forma manual la incorporación de los datos del formulario a una base de datos y luego crearemos una macro con grabadora que permita automatizar el proceso. De esta forma para alimentar la tabla que se crea lo único que hemos de hacer es rellenar y seleccionar los distintos valores del formulario y cuando ya los tengamos pulsaremos un botón que lanza la macro y se añade el nuevo registro a la base de datos.


Tercer Vídeo

Vamos a retocar un poco el código que ha generado la grabadora de macros. El objetivo es conseguir que los nuevos registros de la base de datos no se queden arriba sino que se incorporen en la parte baja de la tabla.

martes, 26 de agosto de 2014

Graficar por fechas

Archivo de Excel utilizado: excelavanzado_graficar_fechas.xlsm

Vamos a realizar una macro que nos permite representar gráficamente una serie de valores de una tabla pudiendo elegir el intervalo de fechas. El usuario selecciona una fecha inicial y una fecha final que van en el eje horizontal, y automáticamente el gráfico se adapta a ese intervalo de fechas.

Sub Eje_Personal()
ActiveSheet.ChartObjects("Temporal").Activate
ActiveChart.Axes(xlCategory).MinimumScale = [F4]
ActiveChart.Axes(xlCategory).MaximumScale = [F8]
End Sub


Funciones FILA, INDICE y RESIDUO

Archivo de Excel utilizado: excelavanzado_filas_alternas.xlsx

Veamos algunas funciones de Excel con las que luego realizaremos un caso práctico para seleccionar filas alternas en una tabla comenzando en una dada y con cierta frecuencia. Por ejemplo, comenzando en la fila doce y luego tomando una fila cada tres. Marcamos las filas seleccionadas con un cierto color usando Formato condicional y luego las copiamos y pegamos en una hoja nueva.

Primer Vídeo

La función FILA se puede utilizar de dos formas. Haciendo referencia a una celda nos da la fila en la que se encuentra esa celda. Y también funciona sin poner nada como argumento, simplemente poniendo =FILA(), en este caso nos da la fila en la se se encuentra la fórmula.

La función INDICE nos permite extraer un valor de una tabla o matriz dada la fila y columna de la celda que deseamos extraer.


Segundo Vídeo

La función RESIDUO calcula el resto o módulo que se obtiene al dividir dos números. Es muy útil para estudiar fenómenos repetitivos. Veremos dos casos, uno para determinar la letra del DNI (Documento Nacional de Identidad) y otro caso donde calcularemos el día de la semana que corresponde a cierta fecha.


Página de la Wikipedia que contiene las letras del DNI (Documento Nacional de Identidad) o NIF (Número de Identificación Fiscal) . Esto se usa en España y es una letra de control que se añade al número del documento de identidad que tiene asignado cada ciudadano.

Tercer Vídeo

Caso práctico donde se utilizan las funciones siguientes.

  • FILA
  • RESIDUO
  • INDICE
  • BUSCARV
  • SI

Lo que hacemos es seleccionar una serie de filas de una tabla comenzando por una fila inicial y con una cierta frecuencia que nos indica el usuario. Usamos Formato condicional para marcarlas con un cierto color y luego las copiamos y pegamos en otra hoja.

jueves, 21 de agosto de 2014

Macros con Grabadora

Archivo de Excel utilizado: excelavanzado_grabadora.xlsm

Una macro es un programa que realizamos para automatizar procesos repetitivos. No solo se pueden crear macros en Excel, muchos otros programas pueden crear sus propias macros, por ejemplo Photoshop.

Vamos a crear una macro con grabadora y vamos a lanzarla de varias formas:
  • Con un atajo de teclado: Control + Letra
  • Con el menú de Macros
  • Desde el Editor de Visual Basic
  • Con botones
  • Con imágenes
Primero aprenderemos a obtener la ficha PROGRAMADOR para poder trabajar con macros.

Realizaremos macros de posición absoluta y de posición relativa, que son las que se ejecutan a partir de donde tengamos situado el cursor cuando la lanzamos. El icono se denomina "Usar referencias relativas".

Grabaremos el archivo con extensión XLSM que son los habilitados para macros.

Veremos el Editor de Visual Basic al que podemos acceder con Alt+F11.

Las macros se pueden detener presionando la tecla Esc y si esto no funciona el método general para detenerlas es presionando la tecla: Control+Pausa. Es una tecla que suele estar el los teclados arriba a la derecha. Junto a la palabra pausa en ocasiones pone la palabra Interrumpir o una abreviatura (Inter) o en inglés Break.


Primer Vídeo

Veamos el vídeo para crear nuestra macro.


Segundo Vídeo

Veamos diferentes formas de lanzar la macro. Para que la macro no se ejecute siempre en la misma posición dentro de la hoja veremos cómo crear macros de posición relativa.


miércoles, 20 de agosto de 2014

Veces que se repiten ciertos números

Archivo de Excel utilizado: matricial_veces_repetidos.xlsx

Nos han planteado un caso práctico donde se necesitaba conocer cuántas veces se repite cada uno de los números entre el 1 y el 33 dentro de una tabla donde se encuentra esa información. Además se requería en un formato concreto. Para resolver este caso hemos tenido que utilizar funciones matriciales de las de tamaño grande.

Para entender este caso se tendría que revisar previamente el siguiente post.

Tabla 1

En la zona azul se generan números aleatorios entre 1 y 33.


Tabla 2

En la zona naranja contamos cuántas veces a parece cada uno de los 33 números.
En la zona verde se representa cada número según las veces que aparece usando diferentes columnas según el número de veces que aparezca.
Tenemos columnas que van entre 0 y 15. Estamos suponiendo que el número máximo de veces que puede aparecer un número son 15 veces. Esto se tendría que verificar en cada caso.


Tabla 3

La zona amarilla es el transpuesto de la zona verde. Se podría haber obtenido con la función matricial TRANSPONER.


Tabla 4

La zona rosa es la que queríamos obtener. En ella se ve cuantas veces aparecen cada uno de los números generados aleatoriamente.

viernes, 15 de agosto de 2014

Gráfico incremental

El ejemplo utilizado en el vídeo se puede encontrar en la Hoja4 del siguiente archivo.


Utilizando la capacidad de la función DESREF para crear Rangos dinámicos vamos a crear el denominado Gráfico incremental. Se trata de un gráfico en el que podemos elegir de forma sencilla, presionando sobre un control de formulario, cuántos valores se verán representados en la serie de datos del gráfico.

Conviene entender previamente lo que es un Rango dinámico consultado el siguiente post.
La sintaxis de la función DESREF es la siguiente.
=DESREF(ref, filas, columnas, [alto], [ancho])

sábado, 9 de agosto de 2014

Registros únicos

Archivo utilizado para realizar el caso práctico: excelavanzado_registros_unicos.xlsx

Vamos a extraer de una base de datos los registros únicos. Utilizaremos dos métodos:

  1. Datos, Eliminar duplicados
  2. Usando CONTAR.SI y con Filtro avanzado


viernes, 8 de agosto de 2014

Media móvil y línea de tendencia en un gráfico de dispersión

Archivo utilizado para realizar el caso práctico: excelavanzado_boletin_electrico.xlsx

Vamos a trabajar con datos reales de demanda eléctrica mensual en España. Vamos a construir un gráfico de dispersión y sobre él vamos a calcular la media móvil de 12 meses. También crearemos la Línea de tendencia o Recta de regresión. Aprenderemos a establecer su ecuación mediante el término independiente de la recta y la pendiente. Finalmente veremos la función PRONOSTICO dentro de la categoría de funciones estadísticas.

Primer vídeo





Segundo vídeo

  • Recta de regresión
  • Término independiente
  • Pendiente
  • Coeficiente R2 (Coeficiente de determinación)
  • Coeficiente R (Coeficiente de correlación)
  • PRONOSTICO


BUSCARV para valores repetidos

Archivo utilizado en el vídeo: excelavanzado_buscarv_repetidos.xlsx

La función BUSCARV siempre busca el primer valor que encuentra en el caso de que existan varios repetidos. Vamos a proponer un procedimiento que nos permitirá extraer de una base de datos aquellos registros que indiquemos aunque estén repetidos.



Nota

En el rango W2:Z2 se encuentran las fórmulas aleatorias con las que se han generado los valores de la base de datos.

Hoja1

Hoja2

Usamos la siguiente función matricial.

=SI.ERROR(INDICE($B$6:$B$24;K.ESIMO.MENOR(SI(C6:C24=0;FILA());FILA()-5)-5);"")


Hoja3

En la Hoja3 se mustran los pasos que permite obtener la fórmula de la Hoja3 de la columan amarilla.


Hoja4

Introducimos una fórmula matricial que extrae los registros que cumplen el criterio. En la celda Q9 vemos la siguiene fórmula matricial.

=SI.ERROR(INDICE(factura;K.ESIMO.MENOR(SI($I$5=Comercial;FILA()-8);FILA()-8);1);"")

Esta fórmula matricial no pertenece únicamente a esa celda, sino que abarca el rango Q9:Q58.

Las fórmulas matriciales requieren tres pasos para establecerlas correctamente.
  1. Paso 1. Seleccionar el rango donde la fórmula matricial actua. En este caso es el rango Q9;Q58
  2. Paso 2. Escribir la fórmula matricial
  3. Paso 3. Validar la fórmula pulsando simultáneamente las tres siguiente teclas: Contro+Shift+Enter


miércoles, 6 de agosto de 2014

Módulo 2: Manejo de datos y gráficos

Menús, Plantillas, Formato condicional, validación de datos, formularios, Agrupar y Subtotales

  1. Rellenar series
  2. Gráficos
  3. Minigráficos
  4. Suma en 3D e Hipervínculos
  5. Vincular entre Excel y Word o Power Point
  6. Importación de texto
  7. Insertar Tabla en Excel 2010
  8. Control de duplicados

Gráficos

Todos los ejemplos de estos vídeos están en el siguiente archivo de Excel. También se incluyen los archivos descargados de Yahoo Finanzas con las cotizaciones de Starbucks y de Apple.
Veremos tanto aspectos básicos como avanzados para mejorar la calidad de nuestros gráficos. Insertaremos una imagen prediseñada dentro de las barras de un gráfico de columnas.


Daremos especial importancia a la diferencia que existe entre un gráfico de Líneas y un gráfico de Dispersión (XY).

Crearemos líneas de tendencia con los gráficos de Dispersión. Veremos qué es una nube de puntos y cómo calcular la recta de regresión. Realizaremos alguna predicción o pronóstico sobre el comportamiento de los datos futuros siguiendo la curva de tendencia.

También tratamos la creación de medias móviles y el tema de la interpolación.

Veremos la incorporación de dos series en un mismo gráfico con dos escalas. Son los denominados gráficos combinados.

Primer vídeo

Gráfico de columnas. Introducción de una imagen representando la altura de las columnas.


Segundo vídeo

Gráfico circular.


Tercer vídeo

Gráfico de Líneas.


Cuarto vídeo

Gráfico de dispersión.



Quinto vídeo

Diferencia entre un gráfico de Líneas y un gráfico de Dispersión.

Sexto vídeo

Escala logarítmica en un gráfico de dispersión para representar variaciones porcentuales. El caso de los gráficos bursátiles.



Séptimo vídeo

Trabajar con dos escalas. El eje primario y el eje secundario.



Octavo vídeo

Comparación de la cotización histórica de Starbucks y de Apple. Ambas acciones cotizan en el Nasdaq. Es un caso práctico para explicar los gráficos con la misma base, en este caso, usamos base 100.



martes, 5 de agosto de 2014

Importación de texto

Estos son los archivos utilizados como ejemplo.

En ocasiones importamos datos provenientes de un ordenador central o de un paquete de contabilidad, personal, almacén o cualquier otro que nos proporciona los datos en texto puro. Lo que en ocasiones llamamos texto plano o código ASCII.

En Datos, Obtener datos externos, desde texto disponemos de una herramienta que nos permite importar texto plano y transformarlo en columnas que luego Excel puede manejar.

Insertar Tabla en Excel 2010

Descargar el archivo excelavanzado_Tablas.xlsx


Veamos cómo trabajar con una Tabla en Excel 2010 y que actúe con Rango dinámico al crear un gráfico, una tabla dinámica o al hacer una validación de datos de tipo lista.


Primer vídeo


Vamos a crear una tabla y un gráfico sobre ella. Luego introduciremos nuevas filas y columnas en la tabla y veremos cómo se actualiza automáticamente el gráfico incorporándose a él las nuevas filas y columnas. Esto es lo que denominamos Rango dinámico.

Si hubiéramos creado la tabla sin haber pedido a Excel que la de carácter de Tabla al introducirse nuevas filas y columnas estas no se hubieran incorporado al gráfico ya que el gráfico se realiza sobre un área concreta marcada con un cierto rango, pero este rango no se amplia o reduce de forma dinámica.



Segundo vídeo


Lo mismo sucede si tenemos una Tabla dinámica. Si hemos pedido a Excel que dote de carácter de Tabla a nuestros datos al modificar las filas, aumentándolas o disminuyéndolas, la Tabla dinámica se actualiza sin más que pulsar sobre ella y pedir como es habitual que se actualice. Si no hubiéramos dotado a nuestra base de datos del carácter de Tabla de Excel la Tabla dinámica estaría apuntando siempre a un mismo rango de datos y no se actualizaría su tamaño de forma dinámica.



Tercer vídeo


  • Veamos cómo se puede volver a dejar una Tabla de Excel en un rango normal: Convertir en rango
  • Totalizar al final de la tabla: Fila de totales
  • Resaltar Primera columna y Última columna
  • Insertar columna en una Tabla de Excel
  • Añadir columna de totales
  • Validación de Datos tipo Lista
  • Rango dinámico en Validación de Datos tipo Lista
  • Función BUSCARV aplicada a una Tabla de Excel




Rango Dinámico


Ya hemos visto que se pude trabajar con Rangos dinámicos al Insertar Tabla de Excel. Antes de disponer de esta opción existía un truco que conseguía estos resultados para que al hacer un gráfico o una tabla dinámica al variar los registros de una base de datos no tuviéramos que modificar el rango del gráfico o de la tabla dinámica. Esto se puede ver en el siguiente post.

Se trabajaba con la función DESREF y con las funciones CONTAR o CONTARA creando nombres de rango de tipo fórmula. Aún podemos seguir trabajando de esta forma, con fórmula, pero el sistema actual que tenemos al introducir Tablas de Excel ha mejorado mucho la forma de trabajar.

Si lo desea puede practicar partiendo de la información en blanco e introduciendo las tablas que hemos visto en el video usando el siguiente archivo.

lunes, 4 de agosto de 2014

Suma en 3D e Hipervínculos

Descargar el archivo excelavanzado_consolida.xlsx

Vamos a trabajar con varias hojas de cálculo y las vamos a consolidar mediante la suma en 3D, en tres dimensiones. Esto lo que quiere decir es que vamos a trabajar con hojas que tienen la misma estructura y sobre las que vamos a sumar cierta celda "en profundidad", esto es, a lo largo de todas las hojas.

En el ejemplo disponemos de 10 filiales con una cuenta de resultados que tiene la misma estructura en todas ellas y deseamos consolidar la información contable en la matriz. Excel lo que nos permite es sumar una misma celda de todas esas hojas.

También aprenderemos a trabajar en Grupo, esto es, seleccionando hojas y aplicando todo lo que hagamos a la selección.

Primer vídeo

Suma 3D.


Segundo vídeo

Aprenderemos a crear Hipervínculos en Excel. Los crearemos de varias formas:
  • sobre texto dirigidos a "lugar de este documento"
  • sobre Formas
  • sobre imágenes prediseñadas
  • sobre imágenes dirigidos a página web

domingo, 3 de agosto de 2014

Minigráficos

Descargar el archivo excelavanzado_minigraficos.xlsx

Los minigráficos se introdujeron en la versión Excel 2010. Son una gran ayuda para representar rápidamente la evolución de una serie de datos.

Los hay de tres tipos:
  • Líneas
  • Columnas
  • Ganancia o pérdida
Veamos el siguiente vídeo donde se explica su creación y opciones.

sábado, 2 de agosto de 2014

Control de duplicados

Descargar el archivo duplicados.xlsx

En Excel existen varias formas de detectar duplicados y de eliminarlos. Utilizaremos una opción propia de Excel que se llama Quitar duplicados. También los detectaremos marcado con un color mediante Formato condicional y el uso de la función CONTAR.SI.

Primer vídeo



Segundo vídeo





Tercer vídeo


Función BUSCARV

Descargar el archivo excelavanzado_buscarv.xlsx

Una de las funciones más utilizadas en Excel, después de la de suma, es la función BUSCARV.

  • BUSCARV nos permite hacer una búsqueda vertical en una tabla
  • BUSCARH nos permite hacer una búsqueda horizontal en una tabla

Primer Vídeo


Segundo Vídeo


Tercer Vídeo

Módulo 1: Conceptos previos


Introducción de la materia y repaso de los elementos básicos de Excel y sus versiones.


  1. Descripción básica de Excel 2010
  2. Aspectos básicos de Excel 2010
  3. Fórmulas básicas
  4. Referencias relativas y absolutas
  5. Métodos abreviados de teclado
  6. Trabajar con Nombres de Rango
  7. Función lógica SI
  8. Función BUSCARV

Función lógica SI

Descargar el archivo excelavanzado_funcion_si.xlsx

La función SI nos permite escribir una de dos cosas en una celda según que una condición se cumpla o no.

Vamos a ver la función SI y cómo se trabaja con operadores lógicos.

Primer vídeo




Segundo vídeo




Tercer vídeo




Cuarto vídeo



jueves, 31 de julio de 2014

Trabajar con Nombres de Rango


Veamos cómo podemos trabajar con nombres de rango.
  • Creación de nombres de rango. No valen espacios en el nombre
  • Uso de los nombres de rango en las fórmulas
  • 'Crear desde la selección' permite crear rangos usando los indicadores de cabecera de fila y columna de una tabla.
  • Uso de la función INDIRECTO
  • No es necesario usar las funciones DESREF o INDICE para encontrar valores dentro de una tabla indicando la fila y la columna que corresponde a esa celda. Esto se puede hacer de forma sencilla si hemos nombrado rangos.

Primer vídeo


Segundo vídeo




Podemos ver un post anterior donde se explica cómo borrar nombres de rango. Es el siguiente.

También se recomienda la lectura de la siguientes post que son de un nivel algo mayor.

miércoles, 30 de julio de 2014

Métodos abreviados de teclado

Descargar el archivo excelavanzado_metodos_abreviados.xlsx

Disponemos de varios vídeos y de un documento de texto con los métodos abreviados de teclado para Excel 2010.

No están todos, únicamente están los que considero más habituales. Muchos de ellos son válidos no solo para Excel, por ejemplo CONTROL+C, que es copiar, se utiliza en cualquier aplicación que corra en Windows.

El documento de texto es el siguiente.

Primer vídeo



Segundo vídeo



Tercer vídeo



Cuarto vídeo



Quinto vídeo




Alguno más

  • Ctrl+J Copia una fórmula hacia abajo sobre el rango seleccionado.

martes, 29 de julio de 2014

Eliminar Filas sin Macro


Ya disponemos de un post en este blog que nos cuenta cómo
Relacionado con este tema también disponemos de otras dos entradas.
También hemos trabajado ocultando columnas con macro. Lo que se hacía es marcar algunas celdas de un cierto color y luego ocultar las columnas que corresponden a esas celdas. Modificando un poco la macro en lugar de eliminar columnas se podrían eliminar filas.
En esta ocasión vamos a ver cómo eliminar filas sin necesidad de usar una macro. Si son pocas las filas a eliminar se pude hacer de forma manual sin mayores problemas. Para ello marcamos el indicador de la primera fila y luego, mientras pulsamos la tecla CONTROL, marcamos el indicador del resto de filas. Así quedarían seleccionadas todas las filas que deseamos eliminar. Luego, pulsamos con el botón derecho del ratón sobre cualquiera de las filas marcadas y elegimos Eliminar. De esta forma eliminaríamos las filas de forma manual.

Cuando tenemos que eliminar muchas filas dispersas a lo largo de una tabla debemos recurrir a una macro o realizar el truco que se describe en el siguiente vídeo.



Aspectos básicos de Excel 2010

Repasemos algunos aspectos básicos de Excel 2010.
  • Hojas que componen un Libro
  • Modificar el número de hojas con las que se abre un libro por defecto
  • Cálculo Automático y Manual de fórmulas
  • Guardar libro con formato XLSX o con formato antiguo XLS
  • Cargar los complementos: Solver, Herramientas para el euro, Herramientas para análisis y Herramientas para análisis VBA
  • Después de presionar Entrar, mover selección 'Hacia arriba' o cambiar esta opción
  • Usar separadores del sistema: coma para decimales y punto para miles
  • Formato de celda. Tipos de formatos
  • Formato personalizado
  • Minimizar cinta de opciones

lunes, 28 de julio de 2014

Referencias relativas y absolutas

Descargar el archivo excelavanzado_dolares.xlsx

Veamos el uso de los dólares en Excel.

Con el siguiente vídeo aprenderemos a fijar ciertas referencias a celda antes de copiar una fórmula. Ya que, si no se fijan las celdas, Excel, por defecto, lo que hace es copiar de forma relativa.

Este es una aspecto que probablemente ya conozcas si has trabajado mucho con fórmulas en Excel, pero nunca viene mal recordarlo.

Primer vídeo

Se basa en la Hoja1 del archvio que hemos indicado para descargar.



Segundo vídeo

Se basa en la Hoja2 del archvio que hemos indicado para descargar.

En el segundo vídeo vamos a ver un ejemplo de cálculo del pago mensual constante que es necesario abonar para amortizar un préstamo dados los datos de principal, duración y tipo de interés nominal.

La mensualidad de calcula con la función financiera =PAGO

Posteriormente se crea una tabla donde se puede ver como evoluciona la mensualidad ante diferentes plazos y tipos de interés. Esto supone efectuara un análisis de sensibilidad de la variable pago antes las variables plazo y tipo de interés.



Nota

Esto mismo se contó para la versión Excel 2007 en el siguiente post. Aunque realmente el uso de los dólares en Excel no ha cambiado con las versiones.

Fórmulas básicas

Descargar el archivo: excelavanzado_formulas_basicas.xlsx

Vamos a jugar a ver si en el vídeo te cuento algún detalle que no supieras. Al menos uno, .... o quizás dos.

  • comenzar la formula por +
  • =CONTAR vs =CONTARA
  • Editar una celda pulsando F2
  • =PROMEDIO
  • =MAX  =MIN
  • =PRODUCTO
  • elevar con ^ (también con =POTENCIA)
  • concatenar con &
  • recuento, máximo, mínimo, suma, promedio, etc. del rango seleccionado




Asistente de funciones


También disponemos del Asistente de funciones. Se puede llegar a él por varios métodos. Yo el método que más utilizo es pulsando el icono fx que existe en la barra de fórmulas.


Se abre la siguiente ventana donde podemos escoger la función que necesitamos.



El Asistente de funciones está dividido en categorías. Podemos seleccionar la categoría o si no sabemos en qué categoría está la función que buscamos podemos escoger Todo.


Otra forma de activar la ventana del Asistente de funciones es ir a la ficha Fórmulas y a la izquierda podemos elegir Insertar función.