Cursos Excel y Macros - Excelforo - noviembre 2012.

Comienza una nueva edición de Cursos de Excel y Macros en modalidad elearning (online), donde podrás disfrutar y aprender de un nuevo curso que te ofrezco:

Curso Tablas dinámicas en Excel (Nuevo!!!)

(ver más)

Curso Excel Avanzado para versiones 2007/2010

(ver más)

Curso Macros Iniciación

(ver más)

Curso Macros Medio

(ver más)

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

(ver más)

Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) dará comienzo el próximo día 1 de noviembre de 2012.
Con la confianza de siempre....Anímate!!

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

En próximas ediciones un nuevo curso Excelforo: Curso Excel para contables-financieros

Seguir leyendo...

VBA: El objeto OLEObject en macros de Excel.

Toca hoy aprender un poco más sobre los controles ActiveX y la forma de interactuar con ellos. En particular hablaré del objeto OLEObject. Hay que saber que este objeto representa un control ActiveX o un objeto OLE (incrustado o vinculado) en una hoja de cálculo.
Probablemente lo sepamos, pero un control ActiveX de una hoja tiene dos nombres: el nombre de la forma que contiene el control, visible en el cuadro Nombre al ver la hoja, y el nombre en código del control, presente en la celda situada a la derecha de (Name) en la ventana Propiedades.

Veamos un ejemplo de aplicación de este objeto. En la Hoja1 de nuestro libro tenemos dispuestos varios controles, algunos son controles de formulario y otros controles ActiveX y también alguna autoforma; entre ellos existen controles de toda clase, pero en concreto hay varios controles tipo CommandButton. Esto son lo que nos interesan, y en particular aquellos con el texto en su Caption 'Excel', escrito de cualquier manera (sin distinguir entre mayúsculas y minúsculas).

Insertaremos en la hoja del Explorador de proyectos del Editor de VBA el siguiente código, asociado al CommandButton de fondo rojo de nuestra hoja (que hemos llamado, i.e., con Name = CommandButtonIdentifica) formando nuestra macro de Excel:

Private Sub CommandButtonIdentifica_Click()
Dim ctrl As OLEObject
Dim boton As CommandButton

'Recorre los objetos de la hoja de cálculo
For Each ctrl In Worksheets(1).OLEObjects
    'Typeof identifica el tipo de objeto, en nuestro ejemplo exclusivamente no sinteresan los CommandButton
    If TypeOf ctrl.Object Is CommandButton Then
    'Enmascaramos el objeto identificado en una variable de tipo botón para poder manipular sus propiedades
    Set boton = ctrl.Object
    'Identificamos los botones cuyo texto(Caption) sea 'Excel' para deshabilitarlos, ocultarlos, o lo que queramos
    If LCase(boton.Caption) = "excel" Then
    'Dependiendo de la propiedad que quieras manipular, deberás acceder a ella o bien por el objeto botón o bien por el objeto control
    'Si las propiedades Enabled y Locked están ambas como True,
    'el control puede recibir el enfoque y se muestra en formato normal (no atenuado) en el formulario.
    'El usuario puede copiar, pero no editar, los datos del control.
    boton.Enabled = True
    boton.Locked = True
    ctrl.Visible = True
    'para ssegurarnos fácilmente que la macro corre adecuadamente, le asignamos al CommnandButton un color de fondo Ciano
    boton.BackColor = vbCyan
    End If
    End If
Next
End Sub

Si presionamos el botón rojo, en el que hemos incluido mediante el evento _click nuestra macro, obtendríamos:

Como esperábamos la macro sólo ha afectado a los CommandButton con un Caption 'Excel'.

Seguir leyendo...

VBA: Macro para abrir y cerrar un libro de Excel - Workbooks.Open.

Explicaré hoy una sencilla macro que nos permite trabajar sobre libros de trabajo en Excel cerrados, pero de los que necesitamos copiar parte de su contenido.
Para ello emplearemos el método Open del objeto Workbooks: WorkBooks.Open; ojo no confundir con otro objeto que es Workbook.
De igual forma para cerrar un libro podemos emplear el método Close del objeto Workbooks: WorkBooks.Close.
Estos dos métodos son la esencia de nuestra futura macro. Macro de Excel que nos permitirá abrir un libro cerrado desde nuestro fichero de trabajo, para seleccionar un rango de celdas, pegarlo en nuestro destino y finalmente cerrarlo.

Supongamos dos ficheros, uno principal (Principal.xlsm) donde pegaremos el contenido del segundo fichero origen (Origen.xlsx). Lógicamente el 'Principal' tiene una extensión .xlsm por que contiene el código de nuestra macro.
Veamos nuestros ficheros de trabajo. El libro 'Origen.xlsx' en su hoja 'Datos' contiene un listado de cursos de Excel:

Por otro lado queremos pegar los datos existentes de esos cursos de Excel en la hoja 'Resumen' del libro 'Principal.xlsm', pero sólo copiaremos los datos de la tabla excluyendo la cabecera de rótulos, ya que ese dato lo tenemos en nuestro destino.

Además, para futuros copiados y pegados, la celda destino de nuestro pegado debería ser la primera sin utilizar; por ejemplo, en este primer caso, la primera celda o fila sin utilizar es la que está debajo de la cabecera, esto es celda A2, pero en la siguiente sería la celda A22.

Nuestro código VBA deberá insertarse en un módulo del libro 'Principal.xlsm':

Sub CompletarLibro()
Dim ruta As String, direccion1 As String
Dim celdadestino As Range
'definimos rutas y archivos como variables
ruta = "E:\excelforo\"
fichero1 = "Origen.xlsx"
direccion1 = ruta & fichero1

'identificamos la celda disponible en el archivo Principal, hoja Resumen
Set celdadestino = Workbooks("Principal.xlsm").Sheets("Resumen").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
'abrimos el fichero desde donde copiar... con el método .Open
Workbooks.Open Filename:=direccion1
Worksheets("Datos").Activate
'seleccionamos qué copiar y donde
Set tbl = Range("A1").CurrentRegion
'con este código únicamente copiamos la tabla excepto la primera fila
'y lo pegamos en la celda destino correcta del libro Principal.xlsm
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Copy _
Destination:=Workbooks("Principal.xlsm").Sheets("Resumen").Range(celdadestino.Address)
'limpiamos el Portapapeles
Application.CutCopyMode = False
'cerramos fichero origen con el método .Close, sin guardar cambios (nos aseguramos...)
Workbooks(fichero1).Close savechanges:=False

End Sub

Podemos probar nuestra macro de Excel. Para ello cerramos el libor 'Origen.xlsx' y ejecutamos la macro 'CompletarLibro' desde 'Principla.xlsm'. Con ello conseguimos lo esperado, hemos seleccionado del libro 'Origen.xlsx' todas las filas menos la cabecera de la tabla de Cursos y se ha pegado en el libro 'Principal.xlsm' a partir de la primera celda libre:

Seguir leyendo...

VBA: UpdateLink - Actualizar vínculos en Excel.

Son muchas las veces que me consultan sobre la forma de Actualizar vínculos externos en nuestras hojas de Excel. Sabemos que disponemos de la herramienta de Edición de vínculos (ver), desde la cual podemos actualizar nuestros valores siempre que lo necesitemos.
Esta herramienta está pensada para aplicarla en aquellos casos que tenemos vínculos con otros libros de trabajo (en nuestro PC o en red) y que se encuentran cerrados, puesto que si estuvieran abiertos no se haría necesario forzar esa actualización.
Así que centrémonos, tenemos al menos dos libros de trabajo diferentes, en uno de ellos el origen de datos (VinculosOrigen.xlsx) y en otro el destino (VinculosDestino.xlsx), como vemos en la siguiente imagen:

Si cerramos el libro VinculosDestino.xlsx y cambiamos nuestra celda de referencia, y cerramos y guardamos este libro origen. La siguiente vez que abramos el libro destino VinculosOrigen.xlsx, aparecerá por defecto un mensaje adviertiendo de la existencia de vínculos, y preguntando si es nuestro deseo actualizarlos:

La celda vinculada no se actualizará hasta que Habilitemos esta opción. Es posible evitar esta pregunta, y que el libro automáticamente actualice al abrir los vínculos existentes. Para ello iremos a la ficha Datos > Conexiones > Editar vínculos, se abrirá una ventana diálogo, y en esta presionaremos el botón Pregunta inicial, y entre las tres opciones desplegadas, elegiríamos la de No mostrar la alerta y actualizar vínculos:

Seguir leyendo...

Curso Tablas dinámicas en Excel.

Comienza una nueva edición de Cursos de Excel y Macros en modalidad elearning (online), donde podrás disfrutar y aprender de un nuevo curso que te ofrezco:

Curso Tablas dinámicas en Excel (Nuevo!!!)

(ver más)

Por supuesto podrás seguir formándote con los demás cursos que imparto...

Curso Excel Avanzado para versiones 2007/2010

(ver más)

Curso Macros Iniciación

(ver más)

Curso Macros Medio

(ver más)

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

(ver más)

Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) dará comienzo el próximo día 1 de noviembre de 2012.
Con la confianza de siempre....Anímate!!

El programa del nuevo curso Tablas dinámicas en Excel es el siguiente, pero puedes informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com:

TEMA 1 - INTRODUCCIÓN TABLAS DINÁMICAS
1. Introducción.
2. Terminología de las tablas dinámicas.
3. Generando una tabla dinámica.
4. El panel lista de campos.
5. Extraer información resumida.
TEMA 2 - MENÚ OPCIONES
1. Introducción.
2. Menú opciones.
3. Nombre y opciones de la tabla dinámica.
1. Opciones.
2. Mostrar páginas de filtros de informes.
3. Generar GetPivotData.
4. Campo activo y la configuración de campo.
1. Descripción de campo activo.
2. Configuración del campo valor.
3. Configuración de campos de filas, columnas y filtros.
4. Expandir y contraer todo el campo.
5. Agrupar selección.
6. Ordenar y filtrar.
1. Ordenar y filtrar.
2. Segmentación de datos.
7. Datos.
1. Actualizar.
2. Cambiar origen de datos.
8. Acciones.
9. Cálculos.
10. Herramientas.
11. Mostrar u ocultar.
TEMA 3 - MENÚ DISEÑO
1. Introducción.
2. Menú diseño.
3. Diseño.
4. Opciones de estilo de tabla dinámica.
5. Estilos de tabla dinámica.
TEMA 4 - PRIMERA TABLA DINÁMICA
1. Introducción.
2. Primer paso: El origen de datos.
3. Segundo paso: Insertar el informe de tabla dinámica.
4. Tercer paso: Estructurar la tabla dinámica.
5. Cuarto paso: Agrupar campos.
6. Quinto paso: Configurar opciones.
7. Sexto paso: Diseño de la tabla dinámica.
8. Una conexión a una base de datos (Access).
TEMA 5 - SEGMENTACIÓN DE DATOS
1. Introducción.
2. Crear una segmentación.
3. Opciones de segmentación.
4. Compartir segmentaciones de datos entre td.
5. Desconectar y eliminar segmentaciones.
TEMA 6 - GRÁFICOS DINÁMICOS
1. Introducción.
2. Maneras de crear un gráfico dinámico.
3. Diseño del gráfico dinámico.
4. Menú Presentación.
5. Menú Formato.
6. Menú Analizar.
TEMA 7 - INDICADORES
1. Introducción.
2. Repaso de reglas de Formato condicional.
3. El formato condicional en una tabla dinámica.
4. Ejemplo de Formato condicional en una tabla dinámica.
5. Los Minigráficos.
6. Minigráficos aplicados a una tabla dinámica.
7. Ejemplo de un Minigráfico asociado a una tabla dinámica.
TEMA 8 - ASISTENTE TABLAS DINÁMICAS 2003
1. Introducción.
2. El asistente de tablas dinámicas 2003.
3. Rangos de consolidación múltiples.
3.1. Asistente para tablas dinámicas con un solo campo de página.
3.2. Asistente para tablas dinámicas con varios campos de página.
4. Peculiaridades de una tabla dinámica con rangos de consolidación múltiple.
5. Tablas dinámicas independientes.
TEMA 9 - FILTROS Y ORDENACIÓN
1. Introducción.
2. Mover elementos dentro de un campo.
3. La ordenación de elementos.
3.1. Ordenar elementos de un campo de fila/columna.
3.2. Ordenar elementos de un campo de valor.
4. Filtros de campo.
4.1. Filtro de informe para mostrar elementos.
4.2. Filtro manual de elementos de campos de etiquetas de filas/columnas.
4.3. Filtrar elementos aplicando un filtro de etiqueta.
4.4. Filtrar elementos aplicando un filtro de valor.
4.5. Filtrar elementos aplicando un filtro de fecha.
4.6. Filtro para mostrar los 10 elementos superiores o inferiores.
4.7. Filtrar por selección para mostrar u ocultar solo los elementos seleccionados.
4.8. Quitar filtros.
TEMA 10 - MOSTRAR VALORES COMO
1. Introducción.
2. Mostrar valores como: % del total general.
3. Mostrar valores como: % del total de columnas.
4. Mostrar valores como: % del total de filas.
5. Mostrar valores como: % de...
6. Mostrar valores como: % del total de filas principales.
7. Mostrar valores como: % del total de columnas principales.
8. Mostrar valores como: % del total principal….
9. Mostrar valores como: diferencia de….
10. Mostrar valores como: % de la diferencia de….
11. Mostrar valores como: total en….
12. Mostrar valores como: % del total en.
13. Mostrar valores como: clasificar de menor a mayor.
14. Mostrar valores como: clasificar de mayor a menor.
15. Mostrar valores como: índice.
TEMA 11 - CAMPOS Y ELEMENTOS CALCULADOS
1. Introducción.
2. Otras herramientas de los campos y elementos calculados.
3. Editar campos y elementos calculados.
4. Eliminar campos y elementos calculados.
5. Campos calculados.
6. Elementos calculados.
7. Fórmulas en gráficos dinámicos.
8. Algo más sobre fórmulas en tablas dinámicas.

Seguir leyendo...

Formato condicional en una tabla dinámica de Excel.

Veremos hoy una característica poco conocida del Formato condicional, o más bien de las Tablas dinámicas en Excel.
Es posible aplicar Formato condicional asociándolo a campos de una tabla dinámica.
Partiremos de un origen de datos en una hoja de cálculo de Excel, convertida en Tabla, por comodidad, a partir de la cual hemos construido una tabla dinámica. Quedando como podemos ver en la imagen:

haz clic en la imagen

El paso siguiente consiste asignar un formato condicional que agregue un icono según el Importe de las ventas y el Comercial. Para ello seleccionaremos el campo 'Suma de Importe', y desde la Ficha Inicio > Estilos > Formato Condicional > Conjunto de iconos > 5 flechas (de color):

Como era de esperar al haber seleccionado el campo 'Suma de Importe' y haber aplicado un formato condicional sobre él, se muestran inmediatamente los iconos seleccionados.
Lo importante viene ahora, y es que aparece en la selección de celdas, un botón de opciones de formato condicional:

En estas tres opciones es donde está la utilidad de la herramienta, ya que podremos optar por asociar el Formato condicional dado bien a:

• las celdas seleccionadas
• al campo marcado, 'Suma de Importe' en nuestro ejemplo.
• o bien a Todas la celdas que muestran valores de 'Suma de Importe' para 'Comercial'

Nosotros marcaremos la última opción:
Todas la celdas que muestran valores de 'Suma de Importe' para 'Comercial'
ya que nuestro objetivo es asociar la variabilidad de los iconos siguiendo esa regla precisamente, esto es, según la importancia de las ventas ('Importes') entre todos los 'Comerciales'.
Tras aplicar la Regla, podemos acceder al Administrador de reglas, seleccionar la regla creada y Editarla, para ver lo especial de esta edición:

Seguir leyendo...

Busqueda matricial por triangulación sobre un rango en Excel.

Hoy explicaré como localizar un valor en un rango dado, en concreto emplearemos un método similar al de triangulación, esto es, identificaremos sus dos coordenadas dentro del rango; lo que conseguiremos calculando en qué fila y columna se encuentra.

Este cálculo tiene inconvenientes cuando existen, dentro del rango de estudio, valores repetidos; pero sin duda para rangos indeterminados sin repeticiones es perfecto.
Partiremos de un rango al que le hemos asignado un nombre definido
Rango=D1:H9,
en el que el valor buscado no está repetido:

La fórmula matricial que nos devuelve esa posición única es:
=SI.ERROR(DIRECCION(1/(MAX(((Rango)=B2)*1/FILA(Rango);0));1/(MAX(((Rango)=B2)*1/COLUMNA(Rango);0)));"No existe")

También podríamos haber optado por una fórmula estándar, empleando la función SUMAPRODUCTO como se ve en la celda B3:
=SI.ERROR(DIRECCION(1/SUMAPRODUCTO(MAX(((Rango)=B2)*1/FILA(Rango);0));1/SUMAPRODUCTO(MAX(((Rango)=B2)*1/COLUMNA(Rango);0)));"No existe")

Lo que hace esta fórmula, en cualquiera de sus formas, es localizar la primera fila en la que aparece (de arriba a abajo) el valor buscado y luego identifica la primera columna en la que aparece (de izquierda a derecha) el valor buscado; logicamente, si el valor buscado es único, en ese cruce fila+columna, obtenemos la dirección de la celda donde se halla el valor buscado único.

Desgranemos la fórmula matricial. Tenemos anidado en la función DIRECCION, como argumento para conseguir la fila de la celda, la fórmula:
=MAX((Rango=B2)*1/FILA(Rango);0).
Podemos ver en la imagen, para cada celda del rango de estudio qué obtenemos:

Seguir leyendo...

VBA: Una función para calcular el área de un polígono irregular.

En la entrada anterior vimos como calcular en nuestra hoja de cálculo el área de un polígono irregular, aplicando el método de producto en cruz, a partir de varios puntos que definian los vértices y lados del polígono (ver).
Una ventaja de la función UDF que vamos a programar es que no requiere la repetición del primer punto al final de la serie, lo que facilita nuestro trabajo.
Esta función personalizada en VBA replica el mismo método de cálculo visto en la entrada anterior, de hecho, si nos fijamos bien, es la forma que tiene Excel de realizar un sumatorio, con lo que efectivamente estamos contruyendo la fórmula del Área, explicada como sumatorio de diferentes productos.
Recordemos nuestros puntos de partida:

haz clic en la imagen

El código que insertaremos en un módulo del Explorador de proyectos del Editor de VBA, formando nuestra Function:

Public Function AreaPolig(x As Range, y As Range) As Double
Dim n As Long, area As Double, i As Long
area = 0
'contamos número de puntos...
n = x.Cells.Count
'controlamos los rangos introducidos
'que los rangos x,y sean de una columna
'que el rango x e y tengan el mismo tamaño
'que haya al menos 3 puntos (o sea que sea un triángulo!!! - polígono cerrado mínimo)
If x.Columns.Count <> 1 Or y.Columns.Count <> 1 Or n <> y.Cells.Count Or n < 3 Then Exit Function

'recorremos el rango de puntos, excepto el último punto!!
For i = 1 To n - 1
    'acumulamos el producto en pares de puntos
    'trabajamos con los ranfos como matrices.
    area = area + (x(i) * y(i + 1) - x(i + 1) * y(i))
Next i
'finalizamos añadiendo el área del último punto con el primero al acumulado anterior
area = area + (x(n) * y(1) - x(1) * y(n))

'obtenemos la función en valor absoluto dividido por dos.
AreaPolig = Math.Abs(area) * 1 / 2

End Function

En esta programación nos aprovechamos de las Array (matrices en VBA) para trabajar con los diferentees elementos de loss rangos, es decir, con cada punto que compone el polígono. La fórmula, como vemos es simple, ya que realiza el producto cruzado de cada par de puntos, acumulando el resultado al siguiente par, para acabar aplicando al resultado final el valor absoluto y dividiéndolo por 2. Obteniendo el área de un polígono irregular

Seguir leyendo...

Cálculo en Excel del área de un polígono irregular - producto en cruz -.

Hace poco un lector me consultaba sobre si existía en Excel alguna función que calculara el área de un polígono irregular, lamentablemente la respuesta es que no; sin embargo, eso no significa que Excel no nos pueda ayudar a realizar dicho cálculo.
En primer lugar debemos estar familiarizados con conceptos geométricos básicos para entender las fórmulas que voy a explicar. Y es que para hallar ese área necesitamos entender el planteamiento del método de producto en cruz, lo que está muy bien explicado en el siguiente sitio Web (latecladeescape.com).
Basicamente este método nos dice que descomponemos nuestro polígono irregular en multitud de triángulos, calculamos su área (recuerdo =base x altura /2), restamos aquellas partes duplicadas, y luego acumulamos esas áreas 'positivas' menos las áreas 'negativas'.
Ese cálculo acumulativo del área es el que vamos a conseguir de una manera muy sencilla aplicando Excel y replicando el método de producto en cruz.

Voy a explicar tres formas diferentes de calcular lo mismo, basado en el mismo método; y para ello, partiremos de un conjunto de puntos en un plano, que entre todos delimitan un polígono irregular o regular.
Por ejemplo, partiremos de seis puntos, seis vértices y por tanto seis lados:

Como vemos, hemos añadido al final, el punto inicial repetido, digamos que eso nos permite cerrar el polígono, además, y fundamental, luego será necesario para el cálculo del área.
Para construir el gráfico, no es necesario para el cálculo pero sí muy visual, seleccionamos el rango de puntos B1:C8 y desde la ficha Insertar > grupo Gráficos > botón Dispersión > opción Dispersión con líneas rectas y marcadores, luego simplemente eliminamos Leyenda y Título y agragamos Etiquetas de datos con valores de ambas series.

Para comprender las diferentes formas de cálculo debemos conocer en qué consiste el método de producto en cruz. El asunto es muy sencillo, ya que sólo requiere multiplicar datos cruzados por la diagonal principal menos el producto de datos cruzados de la diagonal secundaria. En la imagen anterior se mostraba la representación del método en forma aritmética:

Sumaremos los productos de los pares en rojo y restaremos los productos de los pares en azul, el resultado en valor absoluto, lo dividiremos por dos.
Y es es precisamente lo que haremos para calcular el área de la primera forma, con la fórmula:
=+(B2*C3)-(B3*C2)

Seguir leyendo...