Fórmate en Excel en la playa, en el monte... donde estés.

Cursos de Excel y Macros en modalidad elearning con tutor personal, para permitir el acceso a ellos a cualquier persona desde cualquier parte del mundo...
No lo dudes haz de Excel tu mejor aliado!
Aprende con los mejores y adquiere una buena base: Edición de Cursos de Excel y Macros online con tutor personal de Agosto de 2015.
Nunca estudiar fue tan fácil.

Los cursos de Excel y Macros abiertos para este mes de Agosto son:

Curso Excel Avanzado

(ver más)

Curso Macros Medio

(ver más)

Curso Macros Iniciación

(ver más)

Curso Excel Nivel Medio

(ver más)

Curso Tablas dinámicas en Excel

(ver más)

Curso preparación MOS Excel 2010 (Examen 77-882)

(ver más)

Curso Excel Financiero

(ver más)


Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) comienzan el día 1 de Agosto de 2015; y la matrícula estará abierta hasta el día 10.

Excelforo: con la confianza de siempre....estás a tiempo!!

También formación Excel a empresas. Explota los recursos a tu alcance (ver más).

Informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com.

Seguir leyendo...

VBA: Crear una tabla dinámica con macros para Excel.

Sin duda una de las acciones que más fallos y errores nos puede causar es emplear el asistente de grabación de macros para replicar la creación de una tabla dinámica...
Por ello, hoy veremos una forma válida para crear y configurar nuestras tablas tablas dinámicas empleando la programación VBA para Excel.

Partiremos del siguiente origen de datos ('Tabla1'), con tres campos: 'Departamento', 'Zona' e 'Importe'.

El objetivo es crear una tabla dinámica con esta forma:

Generamos nuestro código en un módulo estándar de nuestro proyecto de VB:

Sub CrearTablaDinamica()
'www.excelforo.com
Dim ws As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable

'definimos la hoja destino....
Set ws = Worksheets("Hoja2")

'Creamos la memoria cahce de la TD (Pivot cache)
Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, "Tabla1")

'Ahora generamos la TD
Set pt = pc.CreatePivotTable(ws.Range("B3"))

'configuramos la estructura de la TD
With pt
    'llevamos al área de filas el campo 'Departamento'
    With .PivotFields("Departmento")
    .Orientation = xlRowField
    .Position = 1
    End With
    'llevamos al área de columnas el campo 'Zona'
    With .PivotFields("Zona")
    .Orientation = xlColumnField
    .Position = 1
    End With
    'y al área de valores el campo 'Importe'... también le damos formato...
    Set campo = .AddDataField(.PivotFields("Importe"), "Total", xlSum)
    campo.NumberFormat = "#,##0.00"
End With

End Sub

Estamos listos para ejecutar nuestra macro 'CrearTablaDinamica' y generar nuestra nueva Tabla dinámica...

El código generado con el asistente de grabación sería...

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Hoja1").Select
    Range("B9").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Tabla1", Version:=xlPivotTableVersion15).CreatePivotTable TableDestination _
        :="Hoja2!R3C2", TableName:="Tabla dinámica1", DefaultVersion:= _
        xlPivotTableVersion15
    Sheets("Hoja2").Select
    Cells(3, 2).Select
    With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("Departmento")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("Zona")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Tabla dinámica1").AddDataField ActiveSheet.PivotTables _
        ("Tabla dinámica1").PivotFields("Importe"), "Suma de Importe", xlSum
    With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("Suma de Importe")
        .NumberFormat = "#.##0,00"
    End With
End Sub

Creo que no existe duda cuál de los dos códigos es más claro (y fiable!!).

Seguir leyendo...

Mostrar elementos sin datos en una tabla dinámica.

Me preguntaba un lector hace algunos días por la posibilidad de que nuestras Tablas dinámicas mostraran todos los elementos existentes aunque no tuvieran datos que reflejar, según la configuración o el tipo de filtro aplicado.
La respuesta fue afirmativa, además es bien sencillo...

Lo habitual, por defecto, es que las tablas dinámicas sólo muestren aquellos elementos en los que hay datos...
Veamos un ejemplo en la imagen:

Podemos observar que, a pesar de existir tres zonas: Sur, Norte y Centro, para cada comercial sólo aparece aquella zona con dato...
Esto es un problema (podría serlo) si siempre queremos mantener una misma estructura de tabla dinámica, o bien por que desde ésta, generemos un gráfico dinámico y necesitemos siempre tenga la misma forma:

Nuestro objetivo es, por tanto, que se muestren todos los elementos (con o sin datos!!).
El paso a seguir es sencillo, accederemos a la Configuración de campo (de aquel que deseemos mostrar todos los items.. en nuestro ejemplo, campo Zona), iremos a la pestaña Diseño e Impresión, y marcaremos la opción Mostrar elementos sin datos:

El resultado es claro:

haz clic en la imagen

Se observa como se muestran todos los elementos existentes en el origen para el campo Zona, tenga o no dato... tal como perseguíamos.

Seguir leyendo...

Gráfico combinado de columnas y dispersión especial...

Hoy trataremos un tema interesante sobre gráficos.. de nuevo engañaremos a Excel para que nos muestre un gráfico según nuestra necesidad, esto es, combinar unas series de datos con tipo de gráfico de columnas, y otras series de líneas, adaptando o ajustando sus puntos a las columnas...
¿Raro?, seguro se ve mejor en la imagen siguiente.
Gráfico buscado:

Debemos fijarnos dónde parecen los marcadores de las 'líneas'...
Lo normal de un gráfico combinado sería:

La diferencia es clara, en nuestro gráfico objetivo, cada marcador se superpone a su columna correspondiente, mientras que en el gráfico 'normal', los marcadores de las tres líneas aparecen punteados en la misma posición media del punto X.

Partiremos del siguiente origen de datos, donde vemos nuestras series de datos...

Unas representan 'Importes' y otras tres 'Cantidades', ambas de magnitudes muy diferentes.. lo que nos obliga a llevar las 'Cantidades' al Eje Secundario

Vamos a generar unas columnas auxiliares que utilizaremos como valores de nuestro eje horizontal (eje X), en lugar del rango A2:A6 que tenemos...

haz clic en la imagen

Como podemos comprobar no es más que el valor de cada año restándole 0,20, cero o sumándole 0,20.
Por ejemplo, para el primer año 2010, tendríamos: 2009,80 (2010-0,20), 2010 y 20101,20 (2010+0,20).
Con esto conseguiremos desplazar el punto del marcador de cada valor de las tres series.

Con todos los datos dispuestos, ya podemos empezar a construir nuestro gráfico.
Seleccionaremos el rango discontinuo: A1:A6 y E1:G6 e insertaremos un gráfico de dispersión con líneas rectas y marcadores, consiguiendo el siguiente gráfico:

Seguir leyendo...

Gráfico de dispersión con barras de error.

Quizá pienses que es un tema más que visto... hacer un gráfico de dispersión con barras de error, pero comprobarás enseguida que con un poco de trabajo conseguirás un gráfico que presenta los aumentos o disminuciones entre puntos de nuestra serie (en color rojo o verde).
Este será nuestro gráfico final:

Partiremos de los siguientes datos de nuestro rango A1:B18, y al que añadiremos las siguientes columnas auxiliares:

haz clic en la imagen

Antes de explicar el sentido de estas columnas auxiliares, detallo las fórmulas:
1- para el rango C2:C18: =SI(B3="";NOD();(B3>B2)*(A3-A2))
2- para el rango D2:D18: =(B2>B1)*(B1-B2)
por otro lado
3- para el rango E2:E18: =SI(B3="";NOD();(B3<=B2)*(A3-A2))
4- para el rango F2:F18: =(B2<B1)*(B1-B2)

Estos cuatro rangos obtenidos con las fórmulas anteriores nos servirán para representar con valores personalizados las barras de error para cada punto representado de nuestro origen de datos...
Por un lado para el caso en que crezca el valor de Y (color verde) o que decrezca (color rojo), mostrando este efecto las barras de error horizontal y verticalmente.
Nota: Recuerda que a un punto se le pueden añadir barras de error verticales y horizontales.

Los rangos C2:C18 y E2:E18, con sus fórmulas, nos sirven para representar las Barras de Error Horizontales, distinguiendo cuando haya aumento o disminución, el intervalo entres puntos de las Serie_X (rango A2:A18).
De forma similar, los rangos D2:D18 y F2:F18, con su formulación, representan mediante las Barras de Error Verticales los incrementos y decrementos de los valores de la Serie_Y.
Lo interesante es que añadiremos DOS series de datos iguales, una para representar las barras de error rojas y una segunda que muestre las barras de error verdes...

Pero vayamos al inicio para construir nuestro gráfico.
Lo primero será seleccionar el rango A1:B18 e insertar un Gráfico de dispersión.
A continuación Agregaremos una segunda serie exactamente igual, sobre los mismos datos!!:

Los parámetros de ambas series serán:
1. Nombre de la Serie: ="SeriePPal"
Valores X de la serie: =Hoja4!$A$2:$A$18
Valores Y de la serie: =Hoja4!$B$2:$B$18
y
2. Nombre de la Serie: ="Serie2"
Valores X de la serie: =Hoja4!$A$2:$A$18
Valores Y de la serie: =Hoja4!$B$2:$B$18

Repito, necesitamos dos series iguales por que emplearemos cuatro barras de error...

Añadimos para las dos Series de datos ('SeriePPal' y 'Serie2') sus barras de error verticales y horizontales, con la configuración:
1- Dirección: Más
2- Estilo final: Sin remate

En el siguiente paso, desde la sección Cuantía de error seleccionaremos tipo Personalizado > Especificar valor para cada una de las cuatro barras de error existentes..., de acuerdo a la codificación de los rangos auxiliares (barra_h_verde, barra_v_verde, barra_h_roja, barra_v_roja):

Estos pasos nos han llevado a este gráfico:

Seguir leyendo...

Vincular o combinar consultas con Power Query.

En una entrada anterior vimos un ejemplo de cómo consolidar datos en una tabla empleando el complemento Power Query (ver).
En el día de hoy veremos una alternativa a la función BUSCARV para relacionar información de dos tablas, aprendiendo a Combinar consultas con Power Query.

Partimos de dos tablas situadas en dos hojas distintas (TblPpal y TblPrecios):

Comenzaremos entonces a añadir ambas tablas desde la ficha POWER QUERY > grupo Datos de Excel > botón Desde Tabla:

Para evitar, de momento, generar una nueva hoja por cada consulta, tendremos la precaución desde el Editor de consultas de Guardar y cargar en...:

y en la ventana siguiente marcaremos Crear solo conexión:

Seguir leyendo...

Un gráfico de superficie en Excel para representar un plano.

Emplearemos hoy un gráfico de superficie para representar un mapa o plano, del que conozcamos su diferentes altitudes para un par de coordenadas (latitud + longitud).
Nota: En ese ejemplo he optado (ya que es algo inventado) por numerar la latitud y longitud con valores enteros de 1 hasta 15...

Para el ejemplo simplificaremos con un replica de mapa, en el que visualizamos su alzado:

Lo primero que haremos será trasladar a un rango de doble entrada los valores de las diferentes altitudes para cada par ordenado.
Para que sea más visual a este rango I2:W11 le he aplicado un formato condicional con escala de color... Vemos nuestros datos:

Con poca imaginación, incluso de esta manera (con el formato condicional aplicado), podemos visualizar el contorno de nuestra zona geográfica representada por esas curvas de nivel.

Pasamos a construir nuestro gráfico de superficie, para lo cual seleccionamos el rango H1:W11 y desde la ficha Insertar > grupo Gráficos > botón Gráfico de superficie 3D:

El resultado es inmediato:

Haremos algunos ajustes a nuestro gráfico.
Primero ajustaremos el mínimo y máximo para el valor del eje vertical:
-límite mínimo: 100
-límite máximo: 220
Igualmente las unidades mayor y menor de 20

Seguir leyendo...

VBA: Arrastrando datos entre ListBox de un formulario de Excel.

Un lector me planteaba cómo podríamos mover datos de un ListBox a otro dentro de UserForm de Excel, y es lo que procedo a explicar hoy...

Para esto deberemos emplear algunos eventos poco habituales en nuestros ListBox:
Evento _BeforeDragOver para lanzar un proceso cuando el usuario ejecute una acción de arrastrar y colocar...
Evento _BeforeDropOrPaste para lanzar un proceso cuando el usuario esté a punto de colocar o pegar datos en un objeto...
y Evento _MouseMove cuando el usuario mueve el mouse.

Además usaremos un método concreto asociado a la acción de arrastrar:
El método .StartDrag da inicio a una operación de arrastrar y colocar para un objeto DataObject.

Construiremos un sencillo UserForm que contiene dos ListBox renombrados como:
LB_Origen
LB_Destino
En la propiedad .RowSource del primer ListBox (LB_Origen) hemos tomado los datos de la Tabla1 de nuestra hoja de cálculo para cargarlo de datos...

Ya estamos en disposición de incorporar el código necesario...
Para ello insertamos nuestro código asociándolo a nuestro UserForm de nuestro proyecto de VBA desde el editor de VB... en la ventana de código específica del UserForm1:

Private Sub LBo_Destino_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As _
    MSForms.DataObject, ByVal x As Single, _
    ByVal Y As Single, ByVal DragState As Long, _
    ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    'este evento se produce cuando se está ejecutando una operación de arrastrar y colocar.
    Cancel = True
    Effect = 1
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub LBo_Destino_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, _
    ByVal Action As Long, ByVal Data As MSForms.DataObject, ByVal x As Single, _
    ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    'este otro evento se produce cuando el usuario está a punto de colocar o pegar datos en un objeto.
    Cancel = True
    Effect = 1

    Dim iIndex As Long, col As Integer
    With LBo_Origen
        'determinamos cuál ha sido el elemento seleccionado
        iIndex = LBo_Origen.ListIndex
        'añadimos un elemento al ListBox de destino...
        LBo_Destino.AddItem .List(iIndex, 0), 0
        'incorporamos los valores de las diferentes columnas
        'desde el LB de origen al LB destino
        For col = 1 To LBo_Origen.ColumnCount - 1
            LBo_Destino.List(0, col) = .List(iIndex, col)
        Next col
    End With
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub LBo_Origen_MouseMove(ByVal Button As Integer, _
    ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    'este evento se produce cuando el usuario mueve el mouse...
    Dim MyDataObject As DataObject
    If Button = 1 Then
        Set MyDataObject = New DataObject
        Dim Effect As Integer
        'Con el método .SetText copiamos una cadena de texto al objeto DataObject...
        MyDataObject.SetText LBo_Origen.Value
        'el método .StartDrag da inicio a una operación de arrastrar y colocar para un objeto DataObject.
        Effect = MyDataObject.StartDrag
    End If
End Sub

Podemos ver el efecto aquí:

Seguir leyendo...