Formación Excel y Macros VBA para Excel en Noviembre

¿Necesitas aprender Excel?

Cursos de Excel y Macros en modalidad elearning, 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 Noviembre de 2015.
Nunca estudiar fue tan fácil.

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

Curso Excel nivel Avanzado Online-Webinar - 20 horas -

(ver más)

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

Power Query: Unpivot datos o como Anular la dinamización de columnas.

Meses atrás subí al blog una forma de convertir información representada en tablas de referencias cruzadas en una tabla 'estándar' sobre la que poder trabajar directamente... (ver)...
Pues el tema de hoy tiene bastante relación, pero de una forma infinitamente más potente.. empleando Power Query (complemento de Microsoft del que ya hemos hablado alguna vez en este mismo blog).

Aunque el nombre empleada en la herramienta Anular la dinamización de columnas no es muy descriptivo.. al menos en la versión en inglés sí es más gráfico 'Unpivot...', esto es, deshacer lo logrado con una tabla dinámica o similar; en definitiva, volver a tener una estructura de campos mínimo sobre los que trabajar.

Partiremos de nuestros datos en un libro de Excel llamado 'Origen_Tabla Ref Cruz.xlsx' con la siguiente estructura de datos:

Sobre estos datos queremos posteriormente reagruparlos en base a nuevos criterios o estructuras... por eso nuestro siguiente paso será emplear Power Query para tratar, filtrar o transformar la información tal cual existe ahora mismo en esta otra forma:

Desde esa nueva tabla construiremos el informe definitivo consolidado o según nuestras necesidades...

Empezamos entonces desde la ficha de Power Query > grupo Obtener datos externos > botón De Archivo > Desde Excel

Tras buscar el fichero Excel origen y seleccionarlo se abrirá una ventana diálogo mostrando las hojas del libro... seleccionaremos la que contiene la información válida:

Al hacer clic sobre la hoja se abrirá nuestro Editor de consultas de Power Query:

Seguir leyendo...

VBA: Un hipervínculo dentro de un ListBox.

Tiempo atrás un usuario me planteaba cómo conseguir hacer en nuestro ListBox un hipervínculo...
Aunque no conseguiremos el efecto subrayado típico de los vínculos en Excel, desde luego alcanzaremos plenamente la funcionalidad, y al hacer doble clic sobre el item del ListBox se abrirá (si es que existe) la URL que aparezca.
Para ello partiremos de una sencilla tabla en nuestra hoja de cálculo (llamada 'TblCursos'), a partir de la cual, rellenaremos nuestro ListBox:

En el siguiente paso crearemos nuestro Userform y dentro de él un ListBox que he renombrado como 'LstB_vinculos' con las siguiente propiedades importantes:
.ColumnCount = 2
.ColumnWithds = 100 pt; 100 pt
.RowSource = TblCursos

Si lanzamos nuestro formulario esto es lo que veríamos:

El paso siguiente y último es añadir la funcionalidad buscada, esto es, incluir la acción de hipervínculo... lo que conseguiremos con el método .FollowHyperlink.
Para tal fin, insertamos nuestro código dentro de la ventana del formulario desde el editor de VB:

Private Sub LstB_vinculos_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'gestionamos el realizar el hipervínculo
'al accionar doble clic sobre el ListBox

'dirigimos el procedimiento en caso de error en el link
On Error GoTo ControlErrores
ThisWorkbook.FollowHyperlink Address:=LstB_vinculos.List(LstB_vinculos.ListIndex, 1)

'marcamos la salida o fin del procedimiento
'.. tanto si todo ha ido bien
Exit Sub

'controlamos la alerta en caso de error
ControlErrores:
    If Err.Number = -2147221014 Then
        MsgBox "El vínculo no es correcto!!!"
    Else
        MsgBox "Error tipo: " & Err.Description
    End If
    'acabmos el control de errores saliendo del procedimiento
     Exit Sub
End Sub

Hemos acabado. Ahora al realizar doble clic sobre el item elegido se lanzará el evento _DblClick programado y se abrirá, si es el caso, en nuestro navegador web, la página o link correspondiente...

Seguir leyendo...

VBA: Automatizar la actualización de mis tablas dinámicas...

Aprenderemos hoy a tener actualizadas nuestras tablas dinámicas condicionadas al refresco periódico de la base de datos importada.
Seguramente suene extraño a priori, pero el asunto es simple:
Partiremos de una Tabla importada a nuestro libro de trabajo empleando la herramienta Power Query.
Será importante que al realizar el proceso Agreguemos al Modelo de datos la conexión realizada.

Con nuestra Query creada (y agregada al modelo), construiremos sobre ella una Tabla dinámica.
Y ahora lo más importante, para conseguir esa actualización periódica:
1- configuraremos la conexión de datos para que actualice cada x minutos (en mi caso será 1 minuto para que sea fácil visualizar el cambio).
2- insertaremos unas líneas de código VBA asociado al evento Worksheet_TableUpdate dentro de la ventana de código donde se encuentre la Tabla importada... dentro del evento indicaremos que se realice la Actualización de la Tabla dinámica.

Consiguiendo entonces nuestro objetivo.
Fundamental el evento de hoja empleado (OJO !!, para versiones de Excel 2013 y +) que se activa después que la conexión agregada al modelo de datos se actualice sobre la hoja de cálculo....

Expliquemos los pasos.
Partimos de un Origen de datos externo, en nuestro ejemplo será otro Libro de Excel, dentro del cual, en una hoja tenemos una Tabla creada:

En un Libro de trabajo nuevo accedemos a la herramienta Power Query y desde el grupo Obtener datos externos > botón De Archivos > De Excel buscaremos la ruta donde se encuentre nuestro fichero origen:

Seleccionamos la/s Tabla con datos dentro del fichero/base de datos, y lo Cargamos en nuestra hoja de trabajo:

Atención, por que el siguiente paso es importante, ya que indicamos que esta importación la Agregamos al modelo de datos.. además de indicar dónde ubicamos la Tabla importada:

Tras la importación de nuestra Tabla a través de Power Query (en este caso no he realizado ningún tratamiento de datos) creamos una Tabla dinámica tomando como origen esa Tabla conectada:

En el siguiente paso insertamos nuestro evento en la ventana de código donde se encuentre la Tabla, dentro de nuestro proyecto de VBA desde el editor de VB:

Private Sub Worksheet_TableUpdate(ByVal Target As TableObject)
'actualiza la tabla dinámica
ThisWorkbook.Sheets("Hoja1").PivotTables("Tabla dinámica1").PivotCache.Refresh
End Sub

Ahora solo nos queda controlar la periodicidad de la actualización de la conexión creada.. puesto que de esto dependerá, a través del evento TableUpdate, el refresco de nuestra Tabla dinámica.
Desde la ficha Datos > grupo Conexiones > botón Conexiones buscaremos nuestra conexión y presionaremos Propiedades:

En la pestaña de Uso marcaremos la opción Actualizar cada: e indicaremos los minutos deseados.

Hemos acabado. Cada modificación en nuestra tabla origen, se verá reflejada en nuestro libro de trabajo y por ende en nuestra Tabla dinámica, actualizada automáticamente cada minuto.

Seguir leyendo...

VBA: Rellenando celdas vacías con condiciones.

Al hilo de este post del blog (leer), un lector planteaba la siguiente cuestión:

Hola buenas noches! Excelente post! ojalá me pueda ayudar, tengo una tabla en excel y rellene algunas celdas de color verde (estas son salteadas), quiero saber si hay forma de rellenar las que están en blanco de otro color. Muchas gracias por su atención.

La idea sería completar las celdas vacías, sin contenido y sin color de fondo verde!!.
Para ello redefiniremos la macro del post anterior, incorporándole un condicional IF THEN que controle dicha situación...

Insertamos nuestro código en un módulo estándar de nuestro proyecto de VBA desde el editor de VB:

Sub RellenaCeldasenBlanco()
Dim hoja As Worksheet
Dim UltFila As Long
Dim Rng As Range

Set hoja = Sheets("Hoja1")
With hoja
    'Encontramos la última fila con valores
    UltFila = .Range("A" & .Rows.Count).End(xlUp).Row
    'creamos el rango
    Set Rng = .Range("A1:A" & UltFila)
End With

'contador para determinar si es o no la primera celda vacía
x = 0
'recorremos el rango completo de estudio
For Each celda In Rng
    'controlamos cada celda, si está o no vacía y si además el color de fondo es el Verde
    If celda.Value = "" And celda.Interior.Color <> 5287936 Then
        'si son afirmativas ambas condiciones aumentamos el contador
        x = x + 1
        'distinguimos el caso si fuera la primera celda o siguientes
        'creamos un concatenado tipo texto con las referencias de las celdas
        If x = 1 Then
            Rng2 = celda.Address
        Else
            Rng2 = celda.Address & "," & Rng2
        End If
    End If
Next celda

'finalmente seleccionamos el rango compuesto y ejecutamos la acción de Rellenar celdas vacías
Range(Rng2).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C+1"

'liberamos las variables de objeto
Set hoja = Nothing
Set Rng = Nothing

End Sub

Tras lanzar nuestro procedimiento este es el resultado obtenido:

Seguir leyendo...

Mostrar fechas sin datos en una tabla dinámica.

Hace pocos días un lector me planteaba en qué forma se podría mostrar fácilmente datos acumulados por días en un gráfico, a partir de un origen de datos, pero con la condición que se vieran incluso los días no registrados en la tabla origen... es decir, al igual que en un gráfico estándar añadimos un eje tipo fecha.
Lo que pedía el usuario:

... tengo una consulta sobre cómo hacer un gráfico de barras apiladas, o de datos sumados, con eje horizontal con escala de tiempo. La idea es poder tener un gráfico que con una mirada rápida permita ver la proximidad entre los compromisos de pago por fecha, sin que interese para quién es cada importe...

Llegaremos a este gráfico como resultado final:

El asunto pues consiste en visualizar a lo largo del mes completo, con todas las fechas, la caída de los diferentes importes acumulados por fechas...

Partimos de la Tabla1 de datos (rango A1:C6), a par de la cual construimos una tabla dinámica donde llevamos al área de filas el campo Fecha y al área de valores el campo Importe (Resumido por ...Suma):

Observamos como únicamente se muestran los elementos del campo Fecha existentes...

Construimos el Gráfico dinámico asociado a nuestra Tabla dinámica.

Y finalmente el truco.
Primero entramos en la Configuración del campo Fecha > pestaña Diseño e impresión > marcaremos la opción Mostrar elementos sin datos:

Acabamos nuestro ejercicio Agrupando el campo Fecha por Meses y Días (si fuera necesario por Año).... basta seleccionar un elemento o fecha del campo y desde el botón derecho presionar la opción de Agrupar. Ya en la ventana Agrupar marcamos: Meses y Días:

el trabajo estaría acabado.. pero para facilitar la vista, terminaremos aplicando un filtro sobre el campo 'virtual' generado con la agrupación Meses, mostrando solo el mes de Octubre (el mes donde están los datos)...

Seguir leyendo...

La función VALOR.NUMERO: Convertir Números como texto a números (puntos por comas).

En un post anterior (ver) hablé, mediante programación, de una manera de convertir en números ciertos valores almacenados como texto, y que además tenían la peculiaridad de tener intercambiado los caracteres separadores de miles y decimales.

Hoy, y para versiones de Excel 2013 y superiores, explicaré el uso de la función VALOR.NUMERO que nos habilita el intercambio para valores numéricos (sea cual sea la forma en que estén almacenados - texto o números-) independientemente de la configuración regional de Windows...

la sintaxis:
VALOR.NUMERO(valor 'numérico'; [Separador_decimal]; [Separador_miles])
sabiendo que:
1- Si no se especifican los argumentos Separador_decimal y Separador_miles, se usarán los separadores de la configuración regional actual.
2- Si se usan varios caracteres en los argumentos Separador_decimal o Separador_miles, solo se usará el primer carácter.

Veamos un ejemplo, donde en B2 tenemos un valor guardado como texto, y con los separadores contrarios a mi configuración regional de Windows; y en la celda B3 un número guardado como tal que sigue la pauta de mi configuración regional.

Para afianzar la vista, he aplicado en E2:E3 la función lógica ESNUMERO sobre las celdas B2 y B3

La función aplicada en C2:C3 es:
=VALOR.NUMERO(B2;".";",")
donde como primer argumento indicamos qué separador se está utilizando como separador decimal; y en el segundo argumento qué separador como separador de miles.
Observamos que el texto:
123,456.78 lo convierte en el número 123.456,78.

OJO, el tratamiento lo realiza para cualquier valor, por tanto, corremos el riesgo que entre el rango de valores haya un dato 'bien' informado, y que tras aplicarle esta función, nos transforme el número en un dato incorrecto.. tal y como vemos en el segundo caso, celda B3.

Una forma de salvar la situación sería con la fórmula anidada:
=SI(ESNUMERO(B2);B2;VALOR.NUMERO(B2;".";","))
que tiene en cuenta si el valor ya es un número...

Seguir leyendo...

ELEGIR, la función de Excel olvidada...

Veremos un ejemplo avanzado del uso de la función ELEGIR, donde combinándola con una constante matricial y la siempre útil función COINCIDIR, nos permitirá realizar diferentes operaciones en una misma celda...

haz clic en la imgen

La función aplicada en la celda de cálculo E3 es:
=ELEGIR(COINCIDIR(D3;{"suma";"media";"max";"min"};0);SUMA(datos);PROMEDIO(datos);MAX(datos);MIN(datos))
por supuesto, hemos empleado un nombre definido:
datos =Hoja1!$B$3:$B$14

Repasemos la sintaxis de la función ELEGIR:
ELEGIR(núm_índice; valor1; [valor2]; ...)
donde el primer argumento determine qué posición dada por los siguiente argumentos recuperaremos.

¿Qué ha ocurrido en nuestro ejemplo?, que la posición dada por el primer argumento (un número ordinal), lo conseguimos con la función COINCIDIR:
COINCIDIR(D3;{"suma";"media";"max";"min"};0)
que busca el valor exacto de la celda D3 entre los elementos de la constante matricial {"suma";"media";"max";"min"}, devolviendo un número (1,2,3 ó 4).
Será ese número el que aplicará sobre los argumentos restantes de la función ELEGIR, operando con la función SUMA (si COINCIDIR devuelve 1), con la función PROMEDIO (si COINCIDIR devuelve 2), con la función MAX (si COINCIDIR devuelve 3) o finalmente con la función MIN (si COINCIDIR devuelve 4).

En definitiva, con ELEGIR no solo podemos trabajar sobre valores de una lista, o sobre rangos añadidos, si no también optar por diferentes operaciones previamente indicadas... sin duda muy potente.

Seguir leyendo...