Cómo desplazarse por las hojas de un libro con macros.

En la entrada HIPERVINCULO asociado a un desplegable de celda validada vimos una manera de moverse entre las diferentes hojas de nuestros libros de trabajo empleando la función HIPERVINCULO, siempre en función de un desplegable extraido de una celda validada tipo lista.
En esta entrada relacionada obtendremos un resultado similar pero utilizando macros.
Nuestro punto de partida es idéntico al de la entrada anteriormente comentada, esto es, incluiremos en cada hoja de trabajo, por ejemplo en las celdas A1, una celda validada tipo lista, con elementos los nombres de nuestras hojas Hoja1, Hoja2 y Hoja3

El siguiente paso será incluir nuestro código VBA en cada una de las hojas del Explorador de proyectos dentro del Editor de VBA (Alt+F11), ya que el procedimiento que emplearemos es el evento change:

Private Sub Worksheet_Change(ByVal Target As Range)
'cuando la celda seleccionada sea la celda A1 de la hoja activa
If Target.Address = Range("A1").Address Then
'entonces activaremos/seleccionaremos la hoja del libro
'cuyo nombre o valor refleje la celda activa.
Sheets(Target.Value).Select
End If
End Sub

Con esta macro tan sencilla conseguimos igual efecto que con la función HIPERVINCULO, esto es, según seleccionemos una hoja de trabajo u otra desde la celda A1 validada, la macro nos llevará a dicha hoja.
Es importante remarcar que para que esta macro funcione correctamente los valores de la celda validada sólo pueden ser o bien los nombre reales de las hojas de trabajo o bien la numeración ordinal (1, 2, 3 ...) que corresponderían a cada hoja de nuestro Libro; esto es así por que el código VBA de la macro incluye la instrucción Sheets que únicamente admite estos dos valores.

Seguir leyendo...

HIPERVINCULO asociado a un desplegable de celda validada.

Aprenderemos hoy a generar un listado de hipervínculos mediante funciones que nos dirija a cada una de las hojas de nuestro libro de trabajo desplegados mediante una Celda validada tipo lista:

...Tengo 1 archivo Excel con 14 hojas. Quiero que desde cualquiera de esas hojas donde me encuentre (ejemplo: Hoja 3) pueda acceder a cualquier otra Hoja, cuyos nombres los tengo en una lista de validación: selecciono el nombre de la hoja a la que quiero ir y debería acceder a la misma mediante hipervínculo. En cada una de esas hojas puse una celda con validación, listas, desde donde puede seleccionar el nombre de cualquiera de las hojas restantes del Excel.....

Tiempo atrás trabajamos con la herramienta HIPERVINCULO (ver), pero en esta ocasión deberemos emplear la función:
=HIPERVINCULO(ubicación_enlace; [nombre_enlace])
Lo ideal sería, para responder la cuestión planteada, poder combinar esta función hipervínculo dentro de los elementos a desplegar con una Celda validada tipo lista... pero como con el estándar de Excel esto no es posible (al menos yo no lo he logrado), anexaremos una función a los valores mostrado de una celda validada.

Nuestro trabajo comienza entonces construyendo una Celda con validación tipo lista, que nos muestre los diferentes nombres de las hojas de nuestro libro de trabajo.
En nuestro pequeño ejemplo, supondremos un libro de trabajo con tres hojas Hoja1, Hoja2 y Hoja3.

Seguir leyendo...

VBA: Otro ejemplo de macro en Excel con INTERSECT.

Hace unos días expliqué una instrucción de VBA interesante, el método INTERSECT, y en este post, lo volveré a emplear para contestar la cuestión de un usuario:

...Necesitaría obtener la dirección de la la celda activa para utilizarla en un formato condicional de una celda diferente (en la cabecera) , de manera que al seleccionar una celda, se me activara o no el formato según el valor de la celda seleccionada. Ejemplo: A B C D 1 Titulo 1 Titulo 2 Titulo 3 2 fecha 1 1 0 0 3 fecha 2 1 1 0 4 fecha 3 1 0 0 5 fecha 4 0 1 1 6 fecha 5 1 1 1 En esta tabla debería configurar el formato condicional de las celdas B3, C3 y D3 de forma que, si selecciono cualquier celda de la fila 3, se deberían colorear las celdas B1 y C1 (porque las celdas B3 y C3 tienen el valor 1). Si selecciono cualquier celda de la fila 6, se deberían colorear la B1, C1 y D1, etc....

Veamos una imagen de nuestra tabla de trabajo:

La macro a construir debe evaluar varios condicionantes, en primer lugar que nuestro cursor (nuestra celda activa) se halla dentro del rango adecuado de la tabla, para nuestro ejemplo en el rango B2:D6; otra condición a cumplir será verificar que una vez dentro del rango definido las celdas de fila activa tiene un valor igual a 1; en cuyo caso, asignaremos un color a la cabecera de la tabla.
En caso contrario, ya sea que la celda activa queda fuera del rango B2:D6 o las celdas de la fila activa dentro del rango definido tengan un valor diferente a 1 la cabecera de la tabla debe quedarse sin modificaciones.
Nuestro código VBA a incluir en una Hoja del Explorador del proyecto dentro del Editor de VBA es:

Sub worksheet_selectionchange(ByVal target As Range)
Dim celdaactiva As Object
Dim numfila As Variant
'definimos el número de fila de la celda activa
numfila = ActiveCell.Row

'concretamos la observación dentro del rango B2:D6
For Each celdaactiva In Range("B2:D6")
If Intersect(ActiveCell, Range("B2:D6")) Is Nothing Then
'La celda activa NO cruza con las celdas del rango
Range("B1:D1").Interior.Pattern = xlNone
Else
'La celda activa SI cruza con las celdas del rango
For i = 1 To 3
'con For pasamos por todas las columnas de la tabla
''con If verificamos el valor de la celda
If Cells(numfila, i + 1).Value = 1 Then
Cells(1, i + 1).Interior.Color = 65535
Else
Cells(1, i + 1).Interior.Pattern = xlNone
End If
Next
End If
Next
End Sub

Verificando que, efectivamente, sólo cambia el color de la cabecera de la tabla, cuando la celda activa se encuentra en el rango B2:D6 y los valores de la 'fila activa' son iguales a 1.

Seguir leyendo...

Cómo construir en Excel Tablas estadísticas.

Siguiendo con una serie de entradas referidas a temas estadísticos, y en respuesta a una lectora que recientemente preguntaba por la forma de contruir Tablas estadísticas de algunas distribuciones habituales:

...si fuera tan amable de ayudarme se lo agradecería. El enunciado dice lo siguiente: Construir las tablas de algunas variables aleatorias clásicas: a) Binomial (con n desde 1 hasta la suma de los 3 últimos dígitos del DNI) en mi caso suman 3 b) Normal (0,1) c) t-Student (desde 1 grado de libertad hasta la suma de los 3 últimos dígitos del DNI). d) Chi-cuadrado (desde 1 grado de libertad hasta la suma de los 3 últimos dígitos del DNI)....

Realmente para saber qué debemos hacer con los ejercicios planteados deberíamos conocer esas variable estadísticas de contraste y, sobre todo, cómo funcionan.
Lo que haremos en esta ocasión es emplear las funciones de Excel correspondientes:
a) =BINOM.CRIT o =DISTR.BINOM para una binomial
b) =DISTR.NORM.ESTAND para una normal (0, 1)
c) =DISTR.T para una t-Student
d) =DISTR.CHI para una CHI
empleando cada función para los valores que solicitados...

Elegiré, para no hacer interminable esta entrada, sólo algunas de las planteadas en el ejercicio: DISTR.NORM.ESTAND y DISTR.T.

Empecemos por la tabla para una distribución normal de media 0 y desviación 1: N(0,1), para lo que ejecutaremos la función:
=DISTR.NORM.ESTAND(z)
¿Cuál era la gráfica que explica esta distribución?

Replicaremos la tabla de la N(0,1) al modo tradicional (ver tabla www.uam.es). En primer lugar en una fila dispondremos los valores que hacen las centésimas y en una columna lo números decimales hasta 3 (por no alargar innecesariamente el ejemplo); para luego aplicar la función:
=DISTR.NORM.ESTAND(B$1+$A2)
que permite calcular la probabilidad, según una N(0,1), hasta el punto z, suma de los valores de filas y columnas.

Seguir leyendo...

Contraste de hipótesis: La función INTERVALO.CONFIANZA.

Analizaremos hoy cómo realizar un contraste de hipótesis con Excel, aprovechándonos de la función INTERVALO.CONFIANZA.
Partiremos de una muestra de valores de calificaciones tomadas como resultado de un examen, con los siguientes datos.
La desviación típica de las notas de nuestro examen es 2,7777. Para una muestra de 30 estudiantes se obtuvo una nota media de 5,09. ¿Sirven estos datos para confirmar la hipótesis de que la nota media del examen puede ser de 5,25 con un nivel de confianza del 90%?.

Comenzaremos verificando que nuestros datos de MEDIA y DESVIACION TIPICA, así como el tamaño de la MUESTRA coinciden con nuestro enunciado.
Aplicaremos en la celda F1 la fórmula =CONTAR(B2:B31) para el tamaño de la muestra;
en la celda F2: =PROMEDIO(B2:B31) para la media muestral;
en la celda F3: =DESVEST(B2:B31) para la desviación estandar; cálculo que lo podríamos realizar de manera 'artesana':

Seguir leyendo...

La función SI dentro de un campo calculado en una tabla dinámica.

Días atrás llegó a mi correo el problema planteado por un lector, preguntando por la forma de emplear en la configuración de los campos calculados de las tablas dinámicas la función SI condicional.
No es frecuente ver el uso de los campos calculados, y menos emplear fórmulas complicadas para su configuración, sin embargo, esta herramienta admite muchas más funciones y fórmulas que las simples aritméticas... pero con cuidado.
Nos centraremos con el ejemplo planteado por el lector. Partimos de un libro mayor de contabilidad, con un resumen de movimientos de distintas cuentas contables y sus cargos y abonos al debe y haber:

Seguir leyendo...

Segundo aniversario de www.excelforo.blogspot.com

¡Cómo se me ha podido pasar!!, acabo de cumplir mi segundo año como blogger!!!... y no puedo evitar incluir un comentario al respecto. Un pequeño resumen de este segundo año.
He de comenzar agradeciendo a todos aquellos que o bien a través de los comentarios o bien a través del mail, han felicitado mi pequeño aporte a este mundo de Excel, y que han entendido la utilidad (o al menos la intención) de cada una de mis entradas. Siempre ha sido mi idea el poder enseñar a cualquier lector algo de lo que yo mismo aprendí o sigo aprendiendo.
Me siento orgulloso de ver como cada día que pasa se incrementa el número de 'readers' a través de utilidades feed (son casi 600!!!), o de los seguidores registrados (115), o de los casi 900 comentarios volcados en el blog, y de los no se cuantos correos recibidos.
Por dar datos concretos respecto al número de visitas recibidas en estos dos años:

Pedir perdón a los pocos que han pedido ayuda y que por diversas causas no he sido capaz de ayudar, y a todos aquellos que consideran mi trabajo como inútil, decir que intento mejorar cada momento.

No quiero acabar sin mencionar los ataques o el plagio que ha recibido este blog (www.excelforo.blogspot.com); como comentario personal me parece lamentable que alguien se aproveche del trabajo de otras personas. Por suerte, existen herramientas que controlan estas copias de contenidos, y las medidas legales o de otro tipo pueden ser tomadas con firmeza.

Por acabar con buen sabor de boca, espero que el próximo año se mejor que este recién acabado, y que mi proyecto personal pueda alcanzar las metas soñadas.

Muchas gracias a todos!!!

Seguir leyendo...

Cómo asignar un acceso directo o tecla de método abreviado a una macro existente.

Hola de nuevo!!
Explicaré hoy una manera de asignar un acceso directo con teclado a una macro ya generada.
Supongamos una macro llamada 'Existente' que realiza cualquier procedimiento, y en la que cuando se generó se decidió no asignarle ningún acceso directo o tecla de método abreviado para su ejecución.
¿Cuál es la forma a posteriorí de asignarle dicha tecla de métdo abreviado?; muy sencillo, deberemos ir a Ficha Programador > Código > Macros, o bien pulsar Alt + F8, y veremos una ventana diálogo con un listado de nuestras macros:

Con la macro de nuestro listado seleccionada, pulsaremos el botón de Opciones..., mostrando una nueva ventana en la que podremos asignar una tecla para llamar a nuestra macro:

Seguir leyendo...

Combinar BUSCARV con tabla dinámica.

Daré respuesta a la consulta de un usuario que necesita realizar unas agrupaciones sobre un campo de un Tabla dinámica (ver ejemplo), con la peculiaridad que los intervalos de agrupación no responden a un rango determinado:

...con una serie de datos me exigen que los agrupe en los siguientes intervalos: 0-30 30-50 50-70 70-100 100-150 150-170 y yo lo realizo creando una tabla dinámica con los datos y poniendo en rotulos de fila la superficie y en valores los empleados, y después doy a agrupar y se abre una ventana donde me dice -Empezar por: donde yo pongo 0 , y -Terminar en: donde yo pongo 30 y luego aparece -Por: y yo pongo 30. Pero aquí llega el problema ya que como todos los intervalos no tienen la misma amplitud no se como puedo realizarlo ...

Una posible solución pasaría por realizar agrupaciones manuales sobre los diferentes elementos del campo a tratar, aunque la tarea podría a llegar a ser larga y tediosa; por lo que optaré por un método 'hibrido', por el cual, incorporando una columna auxiliar a nuestro origen de datos, determinar para cada elemento del campo a estudiar a qué intervalo requerido pertenece.

Veamos el listado de datos de partida:

Seguir leyendo...

VBA: El método INTERSECT

Hace bastante que no escribo ningún post en el blog, pero por fin puedo empezar a dedicarle, de nuevo, algo de tiempo... el trabajo hace imposible a veces que nos empleemos en lo que nos gusta.
Intentaré ir poniéndome al día contestando correos antiguos.
Uno de ellos preguntaba por la manera de automatizar el bloqueo de celdas concretas:

...¿Existe alguna posibilidad o alguna macro?, que te permita introducir datos en algunas celdas y una vez introducidas las bloquee automáticamente, por ejemplo al guardar la hoja, de forma que no puedas eliminarlos o modificarlos sin desbloquear la hoja...

Aprenderemos hoy a emplear el método INTERSECT para obtener el cruce de dos o más rangos en un objeto tipo Range; en nuestro caso lo emplearemos para determinar si nuestra celda activa se encuentra dentro de un rango determinado.
La idea consiste en contrastar si nuestra celda activa se halla dentro de un rango previamente determinado, y si es cierta esta premisa forzar el desbloqueo, para tras una posterior protección de la hoja de cálculo, dejarla bloqueada e impedir su modificación o eliminación, tal como pedía el usuario.

Empecemos entonces definiendo cuáles son las celdas que desearemos impedir se modifiquen en el futuro, por ejemplo, el rango de celdas A1:A3, B2 y C3. Con la aplicación del método INTERSECT conseguiremos que cuando se seleccione alguna de dichas celdas de nuestro rango, éstas queden desbloqueadas.
Nuestro código VBA a incluir en una Hoja del Explorador del proyecto dentro del Editor de VBA es:

Sub worksheet_selectionchange(ByVal target As Range)
If Intersect(ActiveCell, Range("A1:A3,B2,C3")) Is Nothing Then
'La celda activa NO cruza con las celdas del rango
ActiveCell.Locked = True
Else
'La celda activa SI cruza con las celdas del rango
ActiveCell.Locked = False
End If
End Sub

Sólo nos quedaría forzar la protección de nuestra Hoja de cálculo, lo que conseguiremos con un procedimiento Workbook_BeforeClose que ejecute dicha protección.

Seguir leyendo...