Crear un índice de hojas en un cuaderno de Excel.

Una de las consultas que recibo con cierta frecuencia es cómo crear un índice de las hojas de un cuaderno Excel.

Cuando queremos navegar de una a otra hoja del cuaderno podemos usar varios métodos, por ejemplo usar las flechas de navegación en la esquina inferior izquierda de la hoja o hacer un clic con el botón derecho del mouse sobre ellas para abrir el menú de navegación



Pero cuando tenemos muchas hojas en un cuaderno, por ejemplo una por semana del año, estos métodos resultan incómodos.
La solución es crear una hoja que haga de índice. Las entradas del índice son los nombres de las hojas, a las cuales le hemos incorporado un enlace (hyperlink), de manera que con un solo clic podamos navegar a la hoja deseada.

Cuando se trata de pocas hojas, podemos hacer esto en forma manual. Pero cuando tenemos un cuaderno con un gran número de hojas, lo mejor es utilizar una macro.
Esta macro, que pueden descargar aquí, nos ofrece la opción de crear una nueva hoja que contenga el índice




Si aceptamos la opción, la macro crea la nueva hoja, en la celda A1 pone el título "Índice" y a partir de la fila 3, pondrá los nombres de las hojas con un enlace a la celda A1 de cada una de ellas.



Si no aceptamos la opción de crear una nueva hoja, el índice es creado a partir de la celda activa del cuaderno.




Technorati Tags: MS Excel

Búsqueda aproximada en Excel – tercera nota

En las últimas dos notas vimos cómo encontrar en una lista el valor más cercano al valor buscado, o extraer el valor asociado a él.
La fórmula matricial que expusimos, y que he mejorado gracias al aporte de Natxo (ver comentario en la nota anterior), nos da el valor más cercano al valor buscado. A veces buscamos el mayor valor más cercano y a veces el menor. Por ejemplo, si observamos la tabla del ejemplo de la nota anterior



vemos que Pedro, con 76, es el más cercano al promedio, 76,2. Pero si buscamos quién tiene la calificación más cercana por encima del promedio, la respuesta es María con 79.

Esta fórmula matricial nos permite encontrar el valor más cercano por encima del valor buscado

={=INDICE($A$2:$A7;COINCIDIR(MIN(SI(B2:B7-B9>=0;B2:B7;FALSO));SI(B2:B7-B9>=0;B2:B7;FALSO);FALSO))}

Esta fórmula crea una matriz con las diferencias entre los valores de la lista y el valor buscado; luego encuentra el menor que sea mayor a cero, es decir, el más cercano que sea mayor al valor buscado.

De la misma manera, esta fórmula no da el valor más cercano que es menor que el buscado

={INDICE($A$2:$A7;COINCIDIR(MIN(SI(B2:B7-C9>=0;B2:B7;FALSO));SI(B2:B7-C9>=0;B2:B7;FALSO);FALSO))}

El archivo con las fórmulas se puede descargar aquí.

Technorati Tags: MS Excel

Búsqueda aproximada en Excel - segunda nota

Ayer vimos como superar el problema de búsqueda aproximada al valor más cercano en Excel.
Podemos ampliar esta técnica para extraer el valor asociado al valor más cercano al valor buscado. Veamos un ejemplo. Supongamos esta lista de alumnos con notas en distintas asignaturas

En la fila 9 hemos calculado el promedio de cada asignatura. Si queremos averiguar quién es el alumno más cercano al promedio, podemos usar la fórmula que expusimos ayer



La fórmula

={INDICE($A$2:B7,COINCIDIR(MIN(ABS(B2:B7-B9)),B2:B7-B9),1)}

que hemos puesto en la celda B11 nos da como resultado Pedro, que es el alumno con la nota más cercana al promedio.

La única diferencia con la fórmula matricial que presentamos ayer es que hemos agregado el argumento "número de columna" a la función INDICE, que al argumento "matriz" comprende dos columnas y el nombre de los alumnos se encuentra en la primera.

Esta fórmula tiene un serio inconveniente. Si la lista no está ordenada en orden ascendiente, el resultado puede ser erróneo. Si copiamos la fórmula a las celdas contiguas

vemos que el resultado para Literatura es "María", cuando tendría que se "Mercedes". Lo mismo con Matemáticas, donde el resultado es "Pedro" siendo el resultado correcto "Mercedes".

Una solución posible es reordenar las listas antes de aplicar la fórmula. Pero en casos como el nuestro esto nos obligaría a dividir nuestra lista en tres listas separadas.

Otra solución es emplear una fórmula que no sea dependiente del orden de los resultados. La fórmula que aplicaremos es la siguiente:

={ INDIRECTO((DIRECCION(COINCIDIR(MIN(ABS(C9-C2:C7)),ABS(C9-C2:C7),0)+1,1)))}


Para hacer la fórmula más legible, la he dividido en la barra de fórmulas usando Alt+Enter



Esta fórmula emplea la función INDIRECTO que devuelve la referencia especificada por una cadena de texto, como ya hemos explicado.

La función DIRECCION crea una referencia a una celda, en forma de texto, cuyo valor es interpretado por INDIRECTO.

La función COINCIDIR calcula cuál es la posición del valor más cercano al promedio, usando los valores absolutos de las diferencias (como vimos en la nota de ayer).

El valor 1 se refiere a la columna A.

La combinación de estas funciones en la fórmula da como resultado $A$6. IINDIRECTO convierte este resultado al contenido de la celda, "Pedro".

Esta fórmula también tiene un serio inconveniente. Si movemos la lista hacia abajo, o agregamos líneas por encima de ella, obtenemos resultados erróneos. Esto se debe a que COINCIDIR sigue dando la posición correcta, pero ahora esta no esta coincide con los números de fila.

Para evitar esto corregimos nuestra fórmula de la siguiente manera:

={INDIRECTO((DIRECCION(FILA(D2:D7)+COINCIDIR(MIN(ABS(D9-D2:D7)),ABS(D9-D2:D7),0)-1,1)))}



El cuaderno se puede descargar aquí

Technorati Tags: MS Excel