Acaba el verano con Excel - Formación Excel online.

Empieza este mes aprendiendo Excel, por que sin duda una buena base te permitirá avanzar en tu trabajo.
Aprende con los mejores: Edición de Cursos de Excel y Macros online con tutor personal de Septiembre de 2014.

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

Curso Macros Iniciación

(ver más)

Curso Macros Medio

(ver más)

Curso Excel Avanzado para versiones 2007/2010

(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 Septiembre de 2014; 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...

Modelo de Framingham: Cálculo de riesgo cardiovascular.

En este post veremos cómo aplicar a un modelo concreto la formulación estándar de Excel y conseguir un valor. En concreto trataré de dar respuesta a la pregunta presentado por un lector, para lo cual, me he basado en la descripción del modelo colgada en la página de la Asociación de la Sociedad española de Hipertensión (ver):

...necesito calcular el riesgo cardiovascular de acuerdo a la escala de Framingham de una lista de datos de excel, pero no tengo ni idea de como armar la formula...

Desde luego, no pretende ser esta explicación, un estudio del modelo de Framingham, si no tan solo una aplicación práctica de cómo nos puede ayudar Excel a obtener ciertos valores...

Lo primero para entender el modelo de Framingham que utiliza el valor del colesterol total es conocer las variables que entrarán en juego:
1- SEXO,
2- la EDAD en años,
3- el COLESTEROL sérico en mg/dl,
4- fracción de colesterol ligado a lipoproteínas de alta densidad HDL,
5- PRESION SISTOLICA,
6- DIABETES (No-Sí), 
7- FUMADOR (No-Sí).

la fórmula que determina la probabilidad de evento cardiovascular a los 10 años según este modelo de Framingham es:
=1 - S ^B
siendo
S= constante función de supervivencia base a 10 años (para hombre o mujer)
B=EXP(L-G)
L=b_E1 x EDAD + b_E2 x EDAD² + b_C + b_H + b_T + b_D + b_F
G= función evaluada para los valores medios de las variables en el estudio (diferente para hombres o mujeres)

Veamos los diferentes parámetros/tablas plasmados en nuestra hoja de cálculo:

haz clic en la imagen

Fundamental para automatizar al máximo las diferentes formulaciones (sobre todo del cálculo de los parámetros), es la creación de los siguientes Nombres definidos:
B_Edad =CalculoFraminghan!$C$2:$E$4
B_Colesterol =CalculoFraminghan!$A$5:$E$10
B_HDL =CalculoFraminghan!$A$11:$E$16
B_Tension =CalculoFraminghan!$A$17:$E$23
B_Diabetes =CalculoFraminghan!$C$24:$E$26
B_Fumador =CalculoFraminghan!$C$27:$E$29

G_hombre =CalculoFraminghan!$D$31
G_Mujer =CalculoFraminghan!$E$31
S_Hombre =CalculoFraminghan!$D$33
S_Mujer =CalculoFraminghan!$E$33

rango =INDIRECTO("B_"&CalculoFraminghan!$K39)
Prima =INDIRECTO(DIRECCION(FILA(rango);COLUMNA(rango))&":"&DIRECCION(FILA(rango);COLUMNA(rango)+COLUMNAS(rango)-1))
PosCol =COINCIDIR(CalculoFraminghan!$H$10;Prima;0)
De especial interés los últimos nombres (rango, Prima y PosCol), ya que con estos automatizamos la búsqueda de los parámetros en el origen...
Con 'rango' construimos y nos referimos al rango previamente nombrado ('B_Edad', 'B_colesterol', 'B_HDL', 'B_Tension', 'B_Diabetes' y 'B_Fumador').
Con 'Prima' obtenemos el rango de celdas correspondiente a la primera fila de cada rango anterior.
Y finalmente con 'PosCol' conseguimos la posición de la columna, dentro de los rangos comentados, según el Sexo planteado en el ejercicio (celda H10).

La función general aplicada, para conseguir los coeficientes b, es la conocida BUSCARV, que la hacemos trabajar con nuestros rangos-nombres definidos.
Se ven en la imagen anterior, a modo de ejemplo, en la celda M10:
=BUSCARV(K10;rango;PosCol;1)
y en la celda M14:
=BUSCARV(H14;rango;PosCol;1)
Notemos que estamos empleando en todo caso la búsqueda aproximada!!! (cuarto argumento 1 o VERDADERO)... básico para obtener valores dentro de rangos.

El cálculo final responde a las fórmulas indicadas al inicio del post, en la celda H22 obtenemos el valor de L:
=(M10*H11)+(M11*H11^2)+M12+M13+M14+M15+M16
en H23 el valor de B:
=EXP(H22-M18)
y por último en H24 el valor o riesgo cardiovascular:
=1-M19^H23

Vemos un pequeño vídeo del funcionamiento:

Seguir leyendo...

Barras de error personalizadas por puntos de una serie de un gráfico de Excel.

Que bueno que Excel sea capaz de sorprendernos cada día.. y que tengamos la humildad suficiente para reconocer lo poco que sabemos sobre esta fantástica herramienta.
Hace unos días un amigo del blog (gracias Sergio!) me hacía ver una funcionalidad (algo escondida) sobre la aplicación de Barras de error en un gráfico, en concreto al hilo del post Desviaciones por puntos de una serie de un gráfico de Excel, por el que daba respuesta a su vez a la cuestión de un lector que preguntaba por la forma de aplciar una valores de error (desviación) para cada punto de la serie...

Partamos de un ejemplo similar con una serie de datos y unas desviaciones diferentes para cada punto de nuestra serie de datos:

En el rango coloreado de amarillo (celdas C2:C7) tenemos los valores del error para cada punto.. en este caso son los mismos para el caso positivo y negativo (aunque podrían ser diferentes trabajando con dos rangos).

El primer paso consiste en seleccionar el rango A1:B7 (sin el dato de las desviaciones) y construir un gráfico, en este ejemplo elegiré un tipo de gráfico de línea; en el que por estética quitaré la Leyenda y el Título del gráfico, y ajustaré el Eje Vertical entre los valores 32 y 40:

En el siguiente paso añadiremos las Barras de error.
Desde Excel 2010/2007: Herramientas de gráficos > pestaña Presentación > grupo Análisis > botón Barras de error > Más opciones de las barras de error...
Desde Excel 2013: Herramientas de gráficos > pestaña Diseño > grupo Diseño de gráfico > botón Agregar Elemento de gráfico > Barras de error > Más opciones de las barras de error...

A continuación, en la ventana de Formato de barras de error, buscaremos en la sección de Cuantía de error, la opción Personalizado:

Al presionar el botón Especificar valor se abrirá una pequeña ventana diálogo:
Barras de error personalizadas
en las cuales informar en qué rango se encuentran los valores de la desviación por encima del punto de la serie(positivas) y en qué rango los valores de la desviación por debajo del punto de la serie(negativos).
En el ejemplo, ambos valores de error positivos y negativos serán el mismo: rango de celdas C2:C7

Tras Aceptar y modificar el aspecto de las barras un poco (Linea sólida y Ancho a 3 pto) tendríamos lo buscado:

Seguir leyendo...

Reseteando el Rango Usado (UsedRange).

Hoy solucionaremos un problema que, en ocasiones, encontramos en nuestras hojas de cálculo de Excel: El Rango Usado (UsedRange) no responde a la realidad, habiéndose incrementado en demasía.
Lo que podría no tener importancia inicialmente, nos lleva a un tamaño del fichero desmedido, y lo peor, los procesos de cálculo se ralentizan tremendamente...
Una señal clara que tenemos esta situación es fijarnos en las barras de desplazamiento, si éstas no se mueven de manera proporcional a lo que esperamos, probablemente signifique que nuestro UsedRange ha crecido.
Verifica la situación presionando Ctrl+Fin y comprueba dónde te lleva dentro de tu hoja...
Así pues, se hace necesario conocer la manera de 'resetear' el Rango Usado y que tome el tamaño justo.

Lo primero es que debemos saber qué pude generar esta situación.
En ocasiones insertamos/escribimos en alguna celda y luego la suprimimos. Esto es la causa más frecuente. Excel guarda en su memoria que esa celda ha sido empleada, y por tanto la localiza como la 'última celda'.
Otras causas es la mala costumbre de señalar filas o columnas completas y aplicar algún tipo de formato!!. Y es que el formato tiene el mismo resultado que si hubieras escrito en esas celdas.
DE igual manera con los Formatos condicionales.

¿Cómo evitar esta situación?.
En un primer intento realizaríamos los siguientes pasos:
1- presionar Ctrl+Fin para que nos lleve a la Última Celda, si no es realmente nuestra última celda empleada, buscaremos cuál es realmente, y situándonos un fila por debajo y una columna a la derecha, a continuación, seleccionando todas las columnas a la derecha hasta el final presionaremos Eliminar (método abreviado: Ctrl + -); de igual modo con todas las filas hacía abajo hasta la última de la hoja. (Para esta selección emplea el modo Final).
2- Guardar y Salir del fichero.
3- Al volver a entrar el problema estará solucionado. Esto suele ser suficiente... pero cuando no lo sea, tocará realizar un pequeño procedimiento Sub.

Un segundo intento algo más infalible (no totalmente!!) es emplear una sencilla macro.
Dentro del proyecto de VBA de nuestro Libro de trabajo, dentro del Editor de VB, en la Hoja que tenga el problema (NO en un módulo!!!):

Sub ResetUsedRange()
    'resetea la última celda usada!!
    Application.ActiveSheet.UsedRange
End Sub

Tras ejecutar la macro, si todo ha ido bien, comprobaremos que se ha reseteado la situación, y la última celda ahora es la que corresponde...

OJO!!, es preferible emplear la línea de código con el término Application, ya que es menos probable que falle que haciéndolo sólo con ActiveSheet.UsedRange
Por tanto, respecto al procedimiento:
1- insertarlo en la ventana de código de la Hoja afectada
2- emplear la instrucción: Application.ActiveSheet.UsedRange

Veamos la situación...

Recordemos estos procesos no son infalibles.. por desgracia, en alguna ocasión me he topado con una hoja en la que fallaban los métodos expuestos.
:-(

Seguir leyendo...