Formación personal en Excel - Cursos Macros y Excel.

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 Marzo de 2015.
Nunca estudiar fue tan fácil.

Los cursos de Excel y Macros abiertos para este mes de Marzo 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 Marzo 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...

Cálculo de la magnitud Richter para terremotos en Excel.

Recién ocurrido (23 febrero 2015) un terremoto o movimiento sísmico con epicentro en Albacete (España) me ha parecido oportuno mostrar cómo se realiza el cálculo de ese famoso número que nos informan por todos lados: terremoto de 5,2 grados en la escala Richter.

¿Qué es esa escala y cómo se calcula dicha magnitud?.
Tomaré la información del Instituto Nacional de Prevención Sísmica, aunque nuestra amiga wikipedia también nos aporta luz (ver.)
Esta escala se debe al Dr. Charles F. Richter, sismólogo norteamericano quien demostró que demostró que a mayor energía intrínseca de un terremoto, mayor era la "amplitud" relacionada al movimiento del suelo, a una distancia dada, ocasionado por el paso de una onda sísmica.
Si bien, inicialmente su trabajo fue calculado únicamente para ciertos sismómetros específicos, y sólo para terremotos en el sur de California, los sismólogos han desarrollado factores de escala para ampliar la escala de magnitud Richter a muchos otros tipos de medición en todo tipo de sismómetros, y alrededor del mundo.

En la imagen siguiente se ilustra cómo usar el método original de Richter para calcular la Magnitud (punto 3) a partir de un sismograma.

El cálculo se inicia con la medición de la mayor Amplitud A(dato 1), de la onda sísmica del terremoto,
y de la diferencia de tiempo ∆t_S-P(dato 2), entre el arribo de la onda P y la onda S.

Ambos valores están relacionados por la ecuación dada por Richter:
ML = log₁₀A(mm) + 3*log₁₀ [8 ∆t_S-P(s)] - 2.92

siendo
A(mm) = amplitud de las ondas en milímetros, tomada directamente en el sismograma.
∆t_S-P = tiempo en segundos desde el inicio de las ondas P (Primarias) al de las ondas S (Secundarias).

También se puede determinar rápidamente el valor de la Magnitud a partir del nomograma, conformado por las escalas ilustradas en el diagrama de la primera imagen.
Se fija el valor de la Amplitud A, en la escala de la derecha, y se une con una línea (color rojo) al valor de la diferencia de tiempo entre el arribo de la onda S y P, en segundos, correspondiente a la escala de la izquierda. El valor de la Magnitud queda determinado por el punto de intersección de esa línea con la escala de Magnitud.

¿Para qué nos sirve Excel en este caso?, para una vez recopilados los valores, obtener mediante la aplicación de la función estándar LOG10 - logaritmo en base 10 el valor de la magnitud - la magnitud de Richter:
Puesto que en el momento de escribir este post no existen datos oficiales sobre el terremoto comentado, tomaré los de la primera imagen, es decir:
A(mm): Amplitud de onda en mm:= 23mm
∆t_S-P: tiempo (seg) entres P y S:=22 segundos

Una forma muy sencilla de emplear nuestra hoja de cálculo en los sucesos extraordinarios de la naturaleza.

Por completar el artículo, a modo de curiosidad, terminaré indicando que existen en la actualidad diversas formas de cálculo para la Magnitud de un sismo, todas ellas derivadas a partir de técnica desarrollada por Richter:
1- El cálculo de magnitud desarrollado por Richter en 1935, se conoce y aplica actualmente con el nombre de M_L. Es utilizada para calcular magnitudes de sismos superficiales con distancia epicentral menor a 2.000 km (la descrita arriba).
2- En 1945, B. Gutemberg desarrolla la magnitud M_b, la cual se obtiene a partir de las ondas internas de los sismos y es utilizada principalmente para sismos profundos con distancias epicentrales mayores a 2.000 km y menores a 11.000 km.
3- En 1956, en sociedad, B. Gutemberg y C. F. Richter idearon la magnitud M_S, la cual es utilizada para terremotos superficiales con distancia epicentral entre 2.000 km y 17.000 km aproximadamente. Para el cálculo de esta magnitud se considera la amplitud de las ondas superficiales que se generan en la corteza terrestre.
4- Por último, en 1979 M. Hanks y H. Kanamori desarrollaron el cálculo para la magnitud de un sismo denominada M_w. Esta se obtiene a partir de un espectro de desplazamientos obtenido a partir del sismograma y puede ser utilizada para calcular la magnitud de eventos a diferentes profundidades y diferentes distancias epicentrales.

La información expuesta ha sido tomada de la Wikipedia y del Instituto de Prevención Sísmica de Argentina y del IGN (Instituo Geográfico Nacional de España).

Seguir leyendo...

VBA: Trasladar datos de inventario....

En varias ocasiones me han solicitado 'ayuda' para resolver un mismo problema; lo que me hace pensar este ejercicio corresponde a algún Curso de Excel o formación...???.
El ejercicio consiste en trasladar los datos completados, de un inventario, al item correspondiente.
Veamos la pregunta planteada por el último de los usuarios:

...como puedo poner un botón en Excel para que al presionarlo, las ENTRADAS Y/O SALIDAS se pongan automáticamente en las coordenadas correspondientes...

El modelo de datos es el siguiente:

El objetivo es, entonces, que el usuario en la celda A2 introduzca el ID del item a completar, para luego rellenar (según corresponda) el dato de Entrada o Salida en las celdas D2, E2, para a continuación trasladar el dato al inventario de la parte inferior, a su fila correspondiente.

Insertamos el siguiente procedimiento 'InsertaInfo', al que luego asignaremos al botón 'Agregar dato':

Sub InsertaInfo()
'Buscamos la fila que corresponde al item seleccionado en A2
With Range("A5:A19")
    Set c = .Find(Range("A2").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            fila = c.Row    'fila buscada
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

'trasladamos los datos de Entradas/Salidas - celdas D2:E2
'acumulando el dato al ya existente!!
Cells(fila, "D").Value = Cells(fila, "D").Value + Cells(2, "D").Value
Cells(fila, "E").Value = Cells(fila, "E").Value + Cells(2, "E").Value

End Sub

El resto lo hace las sencillas fórmulas/funciones del modelo:

Seguir leyendo...

Insertar salto de página después de cada elemento en tabla dinámica.

Hace unos días tuve que recordar cómo imprimir los datos de una tabla dinámica separando en cada hoja los diferentes elementos de un campo en particular.
Para demostrar las acciones que debemos seguir partiremos del siguiente origen de datos, a modo de ejemplo:

Sobre este origen construiremos la siguiente tabla dinámica:

La configuración es sencilla. Seleccionamos el campo sobre cuyos elementos queremos insertar los saltos de página, y presionamos Configuración de campo (bien botón derecho de ratón o bien ficha Analizar > grupo Campo Activo > botón Configuración de campo)
Se abrirá la siguiente ventana, donde navegaremos hacia la pestaña: Diseño e Impresión

En la parte inferior de la pestaña, en la sección 'Impresión' marcaremos la opción: Insertar salto de página después de cada elemento y aceptaremos...

Hemos cumplido el requisito importante, ahora solo queda dar la 'puntilla'.
Accederemos a la Configuración de página, a la pestaña 'Hoja', en la sección Imprimir títulos, donde seleccionaremos Repetir filas en el extremo superior y marcaremos la/s fila/s donde aparezcan los encabezados con las etiquetas de los nombres de los campos en la tabla dinámica:

haz clic en la imagen

El objetivo de esta última operación es, simplemente, ver además de los datos del elemento, el encabezado.. para no perder la referencia visual.

Listo. Podemos imprimir nuestra tabla dinámica en diferentes páginas, una por cada elemento del campo configurado...

Seguir leyendo...

Mapas de Bing en Excel.

Hablare´hoy de una App gratuita para nuestro Excel, la cual nos permitirá incluir gráficos sobre Mapas de una forma rápida, sencilla y siempre vistosa. Hablo de la aplicación de Mapas de Bing.
Si no la tienes instalada, sólo descárgatela gratuitamente de la tienda de Microsoft
OJO!! sólo disponible para Excel 2013 y Office 365.

Una vez instalada accederemos a ella desde la Ficha Insertar > grupo Aplicaciones > botón Mapas de Bing:

Esta aplicación tiene un poco limitado el tipo de gráficos a incluir en los mapas (Circular o de Tarta), pero a cambio distribuye nuestros datos sobre el mapa reconociendo: Continentes, Países, Ciudades, Códigos Postales, incluso calles (aunque aquí se debe ser muy preciso y puede fallar en algunos casos), y por supuesto coordenadas (latitud-Longitud).

Veremos un sencillo ejemplo en el que emplearé códigos postales para representar unos datos de Ventas de dos años diferentes:

Accedemos a la aplicación:
Ficha Insertar > grupo Aplicaciones > botón Mapas de Bing
y tras seleccionar el rango (como haríamos para construir cualquier tipo de gráfico), presionaremos el botón que aparece en la ventana de Mostrar Ubicaciones:

La aplicación reconocerá las Ubicaciones (países, ciudades, códigos postales, etc.) y mostrará los datos sobre ellos:

Sobre el Mapa de Bing podremos realizar las acciones habituales de Zoom o Movernos por el mapa.
También situar la Leyenda donde deseemos.
De igual forma haciendo clic sobre alguno de los gráficos dispuestos se desplegará un bocadillo con la información de esa ubicación:

Seguir leyendo...

Control Windows Media Player en un UserForm.

Aprenderemos hoy un par de aspectos sobre el control de Windows Media Player al ser usado en un UserForm. También veremos las tres propiedades fundamentales para controlarlo: .Play, .Pause y .Stop

Lo primero que deberemos hacer será extraer al Cuadro de herramientas dicho control. Para ello presionaremso botón derecho del ratón y buscaremos Controles adicionales, en la ventana que aparezca buscaremos Windows Media Player:

A continuación incluiremos dicho control en un UserForm previamente insertado... además, de tres botones para controlar el Inicio, Pausa y Paro (aunque el control ya posee estas acciones incorporadas); estos botone sservirán para ejemplarizar el uso de las propiedades comentadas: .Play, .Pause y .Stop
Este sería el resultado:

Los nombres asignados a cada control:
control Windows Media Player: WindowsMediaPlayer1
control botón Play: BtnPlay
control botón Pausa: BtnPausa
control botón Stop: BtnParo

A continuación lo importante en un UserForm, el desarrollo del código:

Private Sub UserForm_Initialize()
'carga la pista desde la ubicación indicada...
WindowsMediaPlayer1.URL = "C:\Users\Public\Music\Sample Music\Kalimba.mp3"
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub BtnPlay_Click()
WindowsMediaPlayer1.Controls.Play
End Sub
Private Sub BtnPausa_Click()
WindowsMediaPlayer1.Controls.pause
End Sub
Private Sub BtnParo_Click()
WindowsMediaPlayer1.Controls.stop
End Sub

El uso práctico es que podremos controlar el sonido desde cualquier otro evento, incluso otros UserForm...

Algo interesante es que este control de WMP admite prácticamente todos los formatos de audio y vídeo.A tener en cuenta...

Seguir leyendo...

Un rango variable con la función INDICE.

Explicaré en el post del día como utilizar la función INDICE para construir un rango variable, que en este ejemplo utilizaremos para configurar una Validación de datos.
Con esto trataré de dar respuesta a la cuestión planteada por un lector:

...Como seleccionar un Rango Variable según dos celdas: En una hoja POR tengo una columna con datos de fecha y en otra hoja (Hoja Y) quiero seleccionar un rango variable de la columna de datos con fecha de la hoja POR, asignarle un nombre a dicho rango seleccionado (Para luego insertar una lista desplegable), pero este rango de fechas de la hoja POR tiene que empezar según una fecha seleccecionada en la otra hojas Y (Celda b1) y terminar según la fecha seleccionada en la celda B2 de la hoja Y, Osea yo elijo el inicio y el final del rango mediante dos celdas que tienen una lista desplegable (B1 y B2 de la hoja Y)...

Partimos de las siguiente información:

la idea es, por tanto, clara. A partir de las fechas dadas en las celdas B1 y B2, deberá aparecer en la celda validada D2 los valores correspondientes al intervalo de fechas dadas.

Antes de desplegar la fórmula construida, deberé recordar la característica de la función INDICE que permite tomar como referencia el dato devuelto!!.

El trabajo consiste, entonces, en construir la siguiente fórmula:
=DESREF((INDICE(Hoja1!$F:$F;COINCIDIR(Hoja1!$B$1;Hoja1!$F:$F;0)):INDICE(Hoja1!$F:$F;COINCIDIR(Hoja1!$B$2;Hoja1!$F:$F;0)));0;1)

que he asignado a un Nombre Definido (le he llamado: fechas_V).
precisamente este nombre definido es el que emplearemos al generar la validación de datos sobre la celda D2:

Una vez mostrados los pasos a seguir, explicaremos la fórmula en cuestión:
=DESREF((INDICE(Hoja1!$F:$F;COINCIDIR(Hoja1!$B$1;Hoja1!$F:$F;0)):INDICE(Hoja1!$F:$F;COINCIDIR(Hoja1!$B$2;Hoja1!$F:$F;0)));0;1)
Lo importante es la primera parte, o el ancla de la función DESREF:
INDICE(Hoja1!$F:$F;COINCIDIR(Hoja1!$B$1;Hoja1!$F:$F;0)):INDICE(Hoja1!$F:$F;COINCIDIR(Hoja1!$B$2;Hoja1!$F:$F;0))
el primer INDICE, por si solo, devuelve el día coincidente con B1; de igual forma el segundo INDICE devuelve el del día que aparece en B2... lo interesante es que además de devolver el día correspondiente, internamente devuelve la referencia concreta.
Para nuestro ejemplo, para B1 (día 05/01/2015) sería la celda/referencia: F7
y para B2 (día 12/01/2015) sería la referencia: F14
finalmente construimos el rango que necesitábamos uniendo ambas referencias por los dos puntos, esto es, consiguiendo el rango F7:F14.

Para conseguir no las fechas si no los valores asociados, empleamos la función DESREF(rango;0;1), que no retorna los valores de una columna a la derecha (es decir, de G7:G14).

Seguir leyendo...

Inquire: el complemento de Excel para analizar nuestro Libro de trabajo.

¿Qué es el INQUIRE y para qué podemos utilizarlo?.
El INQUIRE es un complemento de Micrososft para nuestras versiones de Excel 365 o Excel 2013 Professional Plus... y seguro se mantendrá para el próximos Office 16.
La finalidad de este complemento es auditar nuestros libros de trabajo, analizarlos a fondo (en cuanto fórmulas, funciones, formatos, etc), detallar las relaciones de nuestras celdas, nuestras hojas de cálculo y nuestros libros de trabajo con otras, o incluos comparar dos libros y ver sus diferencias... todo ello de una manera muy sencilla.

Pero vamos por partes, si dispones de las versiones comentadas, lo que debes hacer para instalar este complemento INQUIRE es muy sencillo, basta que accedas a la Ficha Desarrollador > grupo Complementos > botón Complementos .COM:

Esto nos abrirá una ventana desde donde poder seleccionar qué complementos queremos tener a la vista, en este caso Inquire:

Esta selección nos habilitará una nueva Ficha 'INQUIRE' con el siguiente aspecto:

Llegados a este punto, detallaré las posibilidades que ofrece este complemento. Iremos viendo las opciones de izquierda a derecha.

Lo primero que nos encontramos es el botón de Análisis de libro.
Esta funcionalidad nos permite crear un informe interactivo donde se muestra información detallada acerca del libro de trabajo (su estructura, fórmulas, celdas, rangos y advertencias, entre otras cosas).
La imagen siguiente muestra un libro muy sencillo que contiene algunas fórmulas y conexiones de datos a otros ficheros de Excel.

Seguir leyendo...