Mostrar y ocultar líneas de división en Excel 2003

Las líneas de división (o cuadrícula, como ha sido traducido en Excel 2007) aparecen por defecto en las hojas de Excel. Consideraciones de diseño y/o presentación pueden hacer necesario ocultar las líneas de división en la hoja.

En Excel 2007 hacemos esto con un único clic en el icono correspondiente de la pestaña Diseño de Página de la cinta

En Excel clásico (versión 2003 y anteriores) la cuestión en más trabajosa.

Tenemos que usar el menú Herramientas-Opciones, ir a la pestaña Ver y quitar la marca de la opción Líneas de División.

En Excel clásico (versión 2003 y anteriores) la cuestión en más trabajosa. Tenemos que usar el menú Herramientas-Opciones, ir a la pestaña Ver y quitar la marca de la opción Líneas de División.

Si usamos con frecuencia esta opción en Excel clásico podemos crear un atajo con una macro sencilla que luego podemos asociar a un icono que pondremos en alguna de las barras de herramientas existentes.

La macro es muy sencilla y la ponemos en un módulo del cuaderno Personal.xls, de manera que esté disponible para todo cuaderno de Excel

Sub lineas_division()

If ActiveWindow.DisplayGridlines = True Then
ActiveWindow.DisplayGridlines = False
Else
ActiveWindow.DisplayGridlines = True
End If

End Sub


El lugar adecuado para poner un icono ligado a la macro es la barra de herramientas de Formato. Para hacerlo seguimos los siguientes pasos:

1 – Abrimos el menú Ver-Barra de Herramientas-Personalizar. En la pestaña de Comandos elegimos la categoría Macros

2 – Arrastramos el “smiley” al lugar indicado y abrimos el menú contextual con un clic del botón derecho. Activamos la opción Asignar Macro y elegimos la macro apropiada






3- Volvemos a abrir el menú contextual, ponemos un texto adecuado en Nombre y cambiamos la imagen del icono



A partir de ahora tenemos un nuevo icono en la barra de herramientas Formato que nos permite poner o quitar las líneas de división con un solo clic.

Technorati Tags: MS Excel

Redondeo condicional de horas en Excel

Al trabajar con horas en Excel, por ejemplo en una plantilla horaria, suele surgir la necesidad de redondear los intervalos por múltiplos de minutos. Por ejemplo, si pagamos por medias horas o por intervalos de 15 minutos.

En la nota sobre redondeo de horas en Excel mostré algunas técnicas para esta tarea con la función REDONDEAR. La regla general es

=REDONDEAR(A1*(60/m*24),0)/(60/m*24)

donde m es la cantidad de minutos en el múltiplo y la celda A1 contiene la hora a redondear.

También podemos usar la función REDOND.MULT de esta manera

=NSHORA(HORA(A1),REDOND.MULT(MINUTO(A1),m),0)

Si necesitamos redondear hacia arriba o hacia abajo podemos usar las funciones MULTIPLO.SUPERIOR o MULTIPLO.INFERIOR respectivamente.

Para redondear hacia arriba usamos la fórmula

=NSHORA(HORA(A1),MULTIPLO.SUPERIOR(MINUTO(A1),m),0)

Para redondear hacia abajo usamos

=NSHORA(HORA(A1),MULTIPLO.INFERIOR(MINUTO(A1),m),0)


Otra variante es redondear hacia arriba o hacia abajo condicionalmente.

Por ejemplo, los primeros 15 minutos son ajustados hacia abajo; a partir del minuto 16 se ajusta hacia arriba.

Supongamos que hasta los primeros 15 minutos queremos ajustar hacia la hora pasada más cercana y a partir del minuto 16 hacia la hora próxima.

Para calcular este ajuste usamos una fórmula con la función SI, donde

A1 contiene la hora a evaluar

A2 el intervalo de ajuste (15 minutos en nuestro caso)

=SI(MINUTO(A1)<=A2,NSHORA(HORA(A1),MULTIPLO.INFERIOR(MINUTO(A1),60),0),NSHORA(HORA(A1),MULTIPLO.SUPERIOR(MINUTO(A1),60),0))

En esta fórmula usamos la constante 60 ya que ajustamos por horas completas. Buen fin de semana!

Technorati Tags: MS Excel

Valores más frecuentes en una matriz

Esta nota es producto de la consulta de uno de mis lectores, Xisco, quien también, al final de cuentas, aportó una de las soluciones.

Dados un rango o matriz de valores Excel permite calcular el valor más frecuente con facilidad usando la función MODA. Por ejemplo, dada esta matriz de valores (5 columnas X 10 filas = 50 valores aleatorios entre 1 y 10)

si incluimos el rango de la matriz en el nombre “matriz”, usamos esta fórmula para calcular el valor más frecuente (10, que aparece 9 veces)

=MODA(matriz)



El valor de la celda I3 lo calculamos con =CONTAR.SI(matriz,I2).

La pregunta es cómo calculamos el segundo valor más frecuente, o el tercero, etc.

Excel no tiene una función nativa para este cálculo. Mi primera aproximación al problema fue usar columnas auxiliares (técnica que siempre recomiendo ya que hace que los modelos sean más legibles y fáciles de controlar).

Creamos un rango auxiliar que contiene todos los valores únicos que aparecen en la matriz ( en nuestro caso, de 1 al 10) y paralelamente un rango auxiliar que calcula la frecuencia de cada valor

Vemos que, efectivamente, el 10 aparece 9 veces y el segundo valor más frecuente es el 1 con 8 apariciones.

Incluimos el rango B14:B23 en el nombre “valores” y el rango C14:C23 en el nombre “frecuencia”. En la celda I5 ponemos el número de orden de frecuencia buscado (2 por ejemplo) y en la celda I4 ponemos esta fórmula

=INDICE(valores,COINCIDIR(K.ESIMO.MAYOR(frecuencia,I5),frecuencia,0))

En esta fórmula la función K.ESIMO.MAYOR busca el número más grande del rango “frecuencia” de acuerdo al parámetro introducido en la celda I5; luego busca en que fila del rango se encuentra usando la función COINCIDIR y este resultado es usado por la función INDICE para dar el valor adecuado del rango “valores”.

En esta solución hemos usado dos columnas auxiliares (“valores” y “frecuencia”).

El estimado Xisco propone una solución que use sólo una columna auxiliar (o ninguna, si los números vienen ordenados en una sola fila o columna). Dado que en nuestro ejemplo los números están dispuestos en una matriz, debemos transformarlos primero en una columna. Para esto podemos usar la técnica que hemos mostrado en la nota sobre cómo convertir datos de matriz a columna o fila en Excel.

En la celda B13 ponemos esta fórmula

=DESREF(matriz,RESIDUO(FILA()-14,FILAS(matriz)),TRUNCAR((FILA()-14)/FILAS(matriz)),1,1)

que copiamos hasta la celda B63, es decir 50 filas. Este rango lo incluimos en el nombre “valores2”. El resultado es un rango de 1 columna por 50 filas que contiene todos los calores que aparecen en las 50 celdas de la matriz.

Ahora reemplazamos la fórmula de la celda I6 por la siguiente:

=INDICE(valores2,COINCIDIR(K.ESIMO.MAYOR(FRECUENCIA(valores2,valores2),I5),FRECUENCIA(valores2,valores2),0))

La bastante obvia pregunta es: ¿se puede hacer el mismo cálculo sin columnas auxiliares? Por supuesto, con una UDF (función definida por el usuario). Pero, ¿puede hacerse sin usar Vba (macros)?

Toda sugerencia será bienvenida.

Technorati Tags: MS Excel