Un gráfico de columnas apiladas especial...

Me llegó una consulta de una usuaria para ver la manera de elaborar un gráfico de columnas apiladas algo especial:

... Resulta que tengo dos sitios de muestreo PT y Z39, en cada sitio evalué 3 estaciones (invierno, primavera y verano).El otro dato que tengo son los taxones de animales encontrados en esas estaciones por sitio de muestreo.Yo lo que quiero es construir un gráfico que tenga en el eje x los taxones.Por cada taxón quiero tener dos barras (una por cada sitio de muestreo) y lo que quiero acumular son las estaciones (3 colores distintos para cada barra). El eje y es la abundancia de esos taxones....

Estos son los datos aportados...

Para llegar al resultado deseado tendríamos que reestructurar la manera en que los datos están dispuestos en la hoja de cálculo, por ello, opté por construir el gráfico a partir de una Tabla dinámica, esto es, un gráfico dinámico.
Así que nuestro primer paso es construir una Tabla dinámica.
Los campos que se agregarán serán:
Campo 'Taxones' al área de filas
Campo 'Estaciones' al área de columna
Campo Z39 y PT al área de valores
y MUY IMPORTANTE, el Sumatorio de Valores lo añadiremos al área de filas (normalmente lo incluimos en le área de columnas).

Y estamos listos para construir nuestro gráfico dinámico a partir de la Tabla recién creada, basta seleccionar la Tabla dinámica e Insertar un gráfico del tipo necesitado, en nuestro ejemplo: columnas apiladas.:

Objetivo conseguido....

Seguir leyendo...

Formación profesional en Excel.

Cinco años que empecé con mi proyecto: Excelforo.

Este ha sido un año especialmente gratificante, ya que he sido premiado por Microsoft con el título MVP (Most Vauable Professional) en Excel... (ver perfil).
Aunque la mayor satisfacción es y ha sido poder contestar personalmente todas las consultas presentadas, bien a través del correo bien a través de los comentarios del blog...
Recientemente, además, he obtenido la Certificación MOS Excel Expert, cosa que llevaba un tiempo rondándome por la cabeza y que me parecía interesante poseer.

Como en ocasiones anteriores, mostraré algunos datos estadísticos acumulados hasta la fecha; respecto al blog diré que son ya más 2.300.000 visitas únicas, con más de 3.500.000 páginas vistas, y un 1.800.000 usuarios de todo el mundo (España, México, Colombia, Chile, Argentina.. y un largo etcétera)...
Son ya más de 580 entradas publicadas, de casos prácticos propuestos por vosotros, solucionados y explicados; más de 5.500 comentarios, y muchísimas horas dedicadas.

Por supuesto, la aventura formativa y de consultoría también cumple años, puedes visitarme en mi web www.excelforo.com.
No es sólo una web, pretende ser un portal de referencia donde poder realizar todo tipo de cursos de Excel online o cursos de Macros online siempre en modalidad elearning con tutor, donde yo personalmente dirijo y asisto a los alumnos matriculados.

A fecha de hoy puedes ver los siguientes cursos:
Los cursos de Excel abiertos para el próximo Junio del 2014 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 Excel Financiero

(ver más)

Curso Tablas dinámicas en Excel

(ver más)

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

(ver más)

Más información en cursos@excelforo.com

Un cordial saludo y muchas gracias a todos por estar ahí.
Ismael Romero - Excelforo

Seguir leyendo...

VBA: Renombrar los Nombres definidos.

La idea de hoy es renombrar fácilmente los Nombres definidos creados en nuestro Libro, para seguir una regla concreta.. y así agilizar nuestras fórmulas, referencias, etc.

Partiremos, para nuestro ejemplo, de tres Nombres definidos:

Para modificar los Nombres actuales haremos una acción algo 'retorcida', ya que en primer lugar, con una Function (NombreValores) trasladaremos el Nombre y el rango a que se refiere cada Nombre definido, a una matriz.. para posteriormente recuperarla con un procedimiento Sub (RenombrarNombreDefinidos), donde, en base a los datos guardados en la function, iremos eliminando cada Nombre existente y reemplezándolo con los nuevos datos.

En un módulo del proyecto de VBA para nuestro Libro de trabajo, dentro del Editor de VB:

Function NombreValores() As String()
Dim NV As Name
Dim i As Integer
Dim arrNV() As String

'redefinimos la dimensión del Array
ReDim arrNV(1 To Names.Count, 1 To 2)
 
i = 1
'recorremos los diferentes nombres definidos
'y cargamos la matriz....
For Each NV In Names
    arrNV(i, 1) = NV.Name
    arrNV(i, 2) = NV.RefersTo
    i = i + 1
Next
NombreValores = arrNV
End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub RenombrarNombreDefinidos()

Dim NVs() As String
Dim i As Integer
Dim NuevoNombre As String
Dim SheetName As String

'llamamos a la Function creada más arriba...
'y rellenamos la matriz/array NVs con los valores de la Function
NVs = NombreValores()

'recorremos los valores de la matriz generada con valores de los Nombres
For i = 1 To UBound(NVs)
    'definimos la norma para el nuevo nombre a partir del viejo
    'tres caracteres del nombre actual y un numerador.
    NuevoNombre = Left(NVs(i, 1), 3) & "_" & i
    'borramos el 'viejo nombre'
    ActiveWorkbook.Names(NVs(i, 1)).Delete
    'Creamos el nuevo nombre definido (con el nuevo nombre)
    ActiveWorkbook.Names.Add Name:=NuevoNombre, RefersTo:=NVs(i, 2)
Next i

End Sub

Como vemos, realmente no estamos renombrando, si no que eliminamos primero y luego contruimos...
Lo que me parece interesante es el empleo de matrices/arrays para guardar la información y su posterior uso.
El resultado es el esperado:

Seguir leyendo...

VBA: Comprobar si una hoja está o no protegida.

Hoy veremos una pequeña utilidad con la instrucción .ProtectContents, que detecta si la Hoja está o no protegida...

Para completar un poco este método tan sencillo, tomaremos algunas acciones según la respuesta a la pregunta:
1 mostraremos un Mensaje advirtiendo de la situación
2 cambiaremos el color de fondo de la celda B1
3 cambiaremos el color de la pestaña de la hoja

Todo lo asociaremos al evento _Activate de la hoja de trabajo.

Por tanto, en la ventana de código de la hoja que nos interese... asociándolo a un evento _Activate, es decir, cuando activemos la hoja de trabajo (en mi ejemplo la Hoja1):

Private Sub Worksheet_Activate()
'comprobamos si la hoja en cuestión tiene activado la protección
If Hoja1.ProtectContents = True Then
    'en caso afirmativo (está protetida)
    
    'desprotegemos la hoja para realizar las acciones siguientes
    Hoja1.Unprotect
    'la celda B1 la ponemos de color Rojo
    'y cambiamos el color de la pestaña de la hoja a Rojo
    Hoja1.Range("B1").Interior.color = vbRed
    Hoja1.Tab.color = vbRed
    'marcamos un mensaje
    MsgBox "La Hoja está Protegida."
    'finalizamos dejando la hoja protegida.. como estaba
    Hoja1.Protect
Else
    'en caso afirmativo (está desprotegida)
    'la celda B1 la ponemos de color Verde
    'y cambiamos el color de la pestaña de la hoja a Verde
    Hoja1.Range("B1").Interior.color = vbGreen
    Hoja1.Tab.color = vbGreen
    'marcamos un mensaje
    MsgBox "La hoja está Desprotegida."
End If
End Sub

Podemos comprobar cómo al activar (entrar) en la Hoja1, se dispara el evento y en consecuencia el control con .ProtectContents:

O bien..

Seguir leyendo...

La barra de Estado en un Libro de Excel.

Toca hoy recordar a esa barra tan útil, que aporta tanta información al usuario.. y que por su localización natural está olvidada y casi denostada: La Barra de Estado:

haz clic en la imagen

Con esta Barra de estado es posible configurar y personalizar un largo número de características, que de forma muy sencilla (botón derecho del ratón sobre la barra), podemos listar:

Es decir, esta barra de estado nos muestra información sobre todas ellas... OJO!, no las modifica.

Pero hoy hablaremos de algo más 'mundano'.. de cómo Ocultar o Mostrar nuestra Barra de Estado.

Aunque en versiones 2003 y anteriores era posible hacerlo desde los menús (menú Ver > Barra Estado):

Para las versiones superiores (2007 y +) parece ser ha desaparecido esta posibilidad.. por lo que sólo es controlable desde el Editor de VB. Por tanto, accederemos al Editor (presionar Alt + F11) y bien en la ventana de Inmediato (Ctrl+G) o en un módulo, dentro de un procedimiento Sub, podremos insertar las siguientes instrucciones:

'para Ocultarla
Application.DisplayStatusBar = False

'para Mostrarla
Application.DisplayStatusBar = True

También podemos forzar, al tener sólo dos posibilidades (Mostrar/Ocultar), el movimiento contrario

'para forzar el contrario al estado actual....
Application.DisplayStatusBar = Not Application.DisplayStatusBar

No es algo directo como antaño, pero al menos es algo sencillo de aplicar...

Otra posibilidad que tenemos es cambiar o mostrar un texto determinado en esta Barra de Estado. Para ello entraremos en el Editor de VBA, e insertaremos la instrucción:

'para mostrar un texto personal
Application.StatusBar = "Espera!!, que estoy trabajando..."

para recuperar el texto norma, bastará con:

'para recuperar el estado norma...
Application.StatusBar = False

ATENCIÓN!!, para mostrar/ocultar la Barra empleamos .DisplayStatusBar, para tratar el texto empleamos .StatusBar

Seguir leyendo...

Un truco para la Suma tridimensional en diferentes hojas...

Hoy contaré un pequeño truco que sirve para sumar el valor de una celda o rango de diferentes hojas de cálculo.. esto es, hablaré de una fórmula tridimensional.
Veremos cómo de una manera muy sencilla puede conseguir operar sobre todas las hojas de cálculo del libro excepto con la de la hoja activa.

Tiempo atrás ya conté algunas cosas de estas fórmulas 3D:
ver ejemplo 1
y
ver ejemplo2

Comenzaremos este sencillo procedimiento mostrando datos en la celda B2 de las tres hojas de mi Libro de trabajo de Excel:

Vemos valores de 100, 200 y 300 para las Hojas1, 2, y 3 respectivamente...
La idea es, a continuación, sumar todos los valores de la celda B2 de todas las hojas salvo la de la hoja donde introduzco la fórmula tridimensional (en mi ejemplo Hoja2):
=SUMA('*'!B2)
fijémonos de la forma en que lo he escrito, en lugar de un rango u hoja, introduzco un asterisco entre apostrofes!!!

El resultado era el comentado, la suma del valor de B2 de la Hoja1 y de B2 de la Hoja3, además Excel se ha encargado de transformar el literal transcrito a:
=SUMA(Hoja1!B2;Hoja3!B2)

Seguir leyendo...

Obtener línea de tendencia polinómica matricialmente.

Hace ya bastante tiempo, al inicio de mi aventura como Blogger, expuse un sencillo ejemplo de la función ESTIMACION.LINEAL (ver), y cómo construir una línea de regresión. Hoy avanzaremos sobre este tema y veremos cómo conseguir, apoyándonos en constantes matriciales, una ecuación polinómica de regresión de diferente orden o grado.

En este ejemplo hay varias fórmulas que complementan nuestro objetivo, pero iremos por partes, centrándonos en la manera de conseguir nuestro polinomio.

haz clic en la imagen

Empecemos por los datos a partir de los cuales obtendremos nuestra ecuación. Los datos de la variable independiente 'datos_x' están en el rango C3:C13; mientras que los de la variable Y dependiente 'datos_y' los hallamos en el rango de celdas B3:B13.
El objetivo es, según nuestra elección en la celda E14, conseguir el polinomio que represente la línea de tendencia, de grado 1 a 6, junto a su coeficiente de determinación R², y a partir de la ecuación el valor estimado de y. Lo que se muestra en la imagen en las celdas F3:M8.

Importante también el uso de los nombres definidos, que serán los siguientes.
Para los datos del origen:
datos_x =Hoja1!$C$3:$C$13
datos_y =Hoja1!$B$3:$B$13

Para los coeficientes de las futuras ecuaciones:
coef_1 =Hoja1!$F$3:$G$3
coef_2 =Hoja1!$F$4:$H$4
coef_3 =Hoja1!$F$5:$I$5
coef_4 =Hoja1!$F$6:$J$6
coef_5 =Hoja1!$F$7:$K$7
coef_6 =Hoja1!$F$8:$L$8

Para los valores de las potencias del valor x a estimar:
x_1 =Hoja1!$K$10:$L$10
x_2 =Hoja1!$J$10:$L$10
x_3 =Hoja1!$I$10:$L$10
x_4 =Hoja1!$H$10:$L$10
x_5 =Hoja1!$G$10:$L$10
x_6 =Hoja1!$F$10:$L$10

Para ajustar el texto del encabezado en F2:L2:
para_1 =Hoja1!$P$8:$V$8
para_2 =Hoja1!$P$7:$V$7
para_3 =Hoja1!$P$6:$V$6
para_4 =Hoja1!$P$5:$V$5
para_5 =Hoja1!$P$4:$V$4
para_6 =Hoja1!$P$3:$V$3

Lógicamente no es el mismo número de parámetros de una ecuación de grado 1 que de grado 6, siendo sus formas:
Grado 1: y=mx + b (linea recta)
Grado 2: y=m₂x² + m₁x + b (parábola)
Grado 3: y=m₃x³ + m₂x² + m₁x + b
etc...

Así, para la ecuación de la línea recta (grado1), seleccionaremos el rango F3:L3 e introduciremos y ejecutaremos matricialmente:
=ESTIMACION.LINEAL(datos_y;datos_x)
Para la ecuación de grado 2 seleccionaremos el rango F4:L4 e introduciremos y ejecutaremos matricialmente:
=ESTIMACION.LINEAL(datos_y;POTENCIA(datos_x;{1\2}))
Para la ecuación de grado 3 seleccionaremos el rango F5:L5 e introduciremos y ejecutaremos matricialmente:
=ESTIMACION.LINEAL(datos_y;POTENCIA(datos_x;{1\2\3}))
Para la ecuación de grado 4 seleccionaremos el rango F6:L6 e introduciremos y ejecutaremos matricialmente:
=ESTIMACION.LINEAL(datos_y;POTENCIA(datos_x;{1\2\3\4}))
Para la ecuación de grado 5 seleccionaremos el rango F7:L7 e introduciremos y ejecutaremos matricialmente:
=ESTIMACION.LINEAL(datos_y;POTENCIA(datos_x;{1\2\3\4\5}))
Para la ecuación de grado 6 seleccionaremos el rango F8:L8 e introduciremos y ejecutaremos matricialmente:
=ESTIMACION.LINEAL(datos_y;POTENCIA(datos_x;{1\2\3\4\5\6}))

Como vemos la estructura es similar para las diferentes ecuaciones, basta adaptar la constante matricial al grado del polinomio.

Para los valores potenciados de x dado para estimar y en el rango de celdas F10:L10 introducimos y ejecutamos matricialmente:
=POTENCIA($C$14;{6\5\4\3\2\1\0})

Para el cálculo del R² para cada polinómio aplicamos la función INDICE a la función ESTIMACION.LINEAL, detallando qué 'estadístico' (qué situación) es el que nos interesa (fila 3 columna 1 de la matriz de estadísticos).
Por tanto para la ecuación de grado 1 tendríamos en la celda M3:
=INDICE(ESTIMACION.LINEAL(datos_y;datos_x;;1);3)
para la de grado 2 en M4:
=INDICE(ESTIMACION.LINEAL(datos_y;datos_x^{1\2};;1);3)
para la de grado 3 en M5:
=INDICE(ESTIMACION.LINEAL(datos_y;datos_x^{1\2\3};;1);3)
y sucesivamente hasta M8.

Con lo que tendríamos lo que buscamos, el valor y estimado para un x dado, según el grado del polinomio que nos interese seleccionándolo en la celda validada E14:

Vemos como del gráfico resultante (ajustado con macros) obtenemos la misma ecuación y el mismo coeficiente de determinación R².

Finalmente en la celda B14 es donde hemos conseguido el valor de y estimado aplicando la función SUMAPRODUCTO, ajustándola al grado del polinomio seleccionado:
=SUMAPRODUCTO(INDIRECTO("coef_"&$E$14);INDIRECTO("x_"&$E$14))
donde vemos que realizamos el producto elemento a elemento y luego la suma de la matriz de coeficientes con los de los valores de x potenciados... es decir, donde conseguimos el producto y la suma posterior resultante de la ecuación del tipo:
y=m₆x⁶ + ... +m₃x³ + m₂x² + m₁x + b

Seguir leyendo...

Encontrar el primer valor NO vacío de un rango.

Algunos días atrás me plantearon la forma de obtener, sobre un rango de celdas, el valor correspondiente a la primera celda no vacía (esto es, con datos).

... necesito encontrar la primera celda que contenga datos (que no esté vacía) en esa misma columna (llamémosla celdaX)...

Mostraré algunas fórmulas, similares en el concepto, que nos llevan a conseguir nuestra meta.

Vemos nuestras fórmulas matrciales:
=INDICE(B2:B11;COINCIDIR(VERDADERO;INDICE((B2:B11<>0);0);0))
o la simplificada
=INDICE(B2:B11;COINCIDIR(VERDADERO;(B2:B11<>0);0);0)
y
=INDICE(B2:B11;COINCIDIR(FALSO;ESBLANCO(B2:B11); 0))
o su complementaria
=INDICE(B2:B11;COINCIDIR(VERDADERO;NO(ESBLANCO(B2:B11)); 0))

En todas ellas lo interesante es cómo con la función COINCIDIR y su primer argumento con un valor lógico VERDADERO o FALSO encontramos dentro del rango virtual conseguido la coincidencia. Ya que, no olvidemos, que esta función de búsqueda COINCIDIR devuelve la primera coincidencia que encuentre...

Por ejemplo, en
=INDICE(B2:B11;COINCIDIR(VERDADERO;(B2:B11<>0);0);0)
en el rango 'virtual' B2:B11<>0 que devuelve una matriz de VERDADEROS o FALSOS según la celda tenga valor o esté vacía (respectivamente), con COINCIDIR y su primer argumento VERDADERO encontramos el primer VERDADERO, es decir, el primer valor NO vacío del rango B2:B11 que buscábamos.

O también en
=INDICE(B2:B11;COINCIDIR(FALSO;ESBLANCO(B2:B11); 0)
de manera análoga obtenemos la misma matriz de VERDADEROS y FALSOS con el rango 'virtual' ESBLANCO(B2:B11)...

Posteriormente al aplicar la función INDICE sobre el rango B2:B11 conseguimos, no la posición, sino el valor de la celda correspondiente.

Por supuesto este razonamiento nos servirá para conseguir la primera celda vacía del rango.
Tenemos nuevamente nuevas alternativas.. pero todas ellas siguen el mismo patrón ya explicado (todas matriciales):
=DIRECCION(COINCIDIR(VERDADERO;INDICE((B2:B11="");0);0)+1;2)
o
=DIRECCION(COINCIDIR(FALSO;NO(ESBLANCO(B2:B11)); 0)+1;2)
o incluso
=DIRECCION(COINCIDIR(VERDADERO;ESBLANCO(B2:B11); 0)+1;2)

En este caso he optado por mostrar con la función DIRECCION la referencia de la celda correspondiente.

Seguir leyendo...

VBA: Diferencias entre UsedRange y CurrentRegion.

Dedicaré un espacio a explicar la diferencia entre las propiedades UsedRange y CurrentRegion.
Aunque en apariencia podrían ser similares hay un matiz diferenciador, que debemos conocer.
.UsedRange: devuelve un objeto Range que representa el rango utilizado en la hoja de cálculo especificada.
.CurrentRegion: devuelve un objeto Range que representa la región actual (Nota: la región actual es un rango limitado por cualquier combinación de filas y columnas vacías).

Dicho de otro modo, CurrentRegion es el rango rectangular de celdas no en blanco que rodea el objeto Range a la que estamos llamando CurrentRegion.
CurrentRegion deberemos utilizarlo con cuidado, ya que a veces tiene un comportamiento 'complicado'.

UsedRange es una propiedad del objeto WorkSheet; y CurrentRegion es una propiedad del objeto Range.

Para explicarlo más claramente mostraremos un sencillo ejemplo sobre nuestra hoja de cálculo:

Vemos claramente don rangos discontinuos de celdas con valores con celdas vacías entremedias: A1:C3 y E6:H7.
Si empleamos .CurrentRegion:

Sub MsgBox_CurrentRegion()
MsgBox Sheets("Hoja1").Cells(1, 1).CurrentRegion.Address(RowAbsolute:=True, ColumnAbsolute:=True, ReferenceStyle:=xlA1)
End Sub 

Devolverá: $A$1:$C$3 ó lo que es igual el Rango R1C1:R3C3 en estilo R1C1

Si utilizamos .UsedRange:

Sub MsgBox_UsedRange()
MsgBox Sheets("Hoja1").UsedRange.Address(RowAbsolute:=True, ColumnAbsolute:=True, ReferenceStyle:=xlA1)
End Sub

Devolverá: $A$1:$H$7 ó lo que es igual el Rango R1C1:R7C8 en estilo R1C1

Conociendo las diferencias entre ambas podremos aprovecharnos de ellas para conseguir definir variables para nuestros procedimientos... de manera especial para aquellas que nos sirvan para delimitar rangos.

Seguir leyendo...