Encontrar el encabezamiento en una matriz con Excel

Supongamos esta tabla de datos, que muestra la ubicación de ciertos agentes en ciertas zonas por día de la semana

Para encontrar qué agente estará en qué zona en determinada fecha podemos usar una fórmula como ésta

=INDICE(agentes,COINCIDIR(C12,fechas,0),COINCIDIR(C13,zonas,0))

dónde usamos los nombres

agentes =indice!$C$3:$F$9

fechas =indice!$B$3:$B$9

zonas =indice!$C$2:$F$2

Es decir, dados los valores de la fecha y la zona en la matriz, podemos encontrar el agente.
Pero la pregunta es, ¿cómo encontramos la zona sabiendo la fecha y el agente? Es decir, ¿en qué zona se encontrará Roberto el 04/09/2009? Es decir, tenemos que hacer una búsqueda “hacia arriba”.

Empezamos por agregar dos nuevos nombres para hacer más legible nuestra fórmula

tabla =zona!$C$3:$F$10
agente =zona!$C$4:$C$10

En la celda C13 ponemos la fecha, en la celda C14 el nombre del agente y en la celda C15 esta fórmula:
 
=DESREF(tabla,0, COINCIDIR(C14, DESREF(tabla,COINCIDIR(C13,fechas,0),0,1,COLUMNAS(tabla)),0)-1,1,1)

Para explicar esta fórmula veamos cuál es la función de cada uno de sus componentes.

Empezamos con

=DESREF(tabla,COINCIDIR(C13,fechas,0),0,1,COLUMNAS(tabla))

Si ponemos esta fórmula en un rango de una fila por cuatro columnas obtenemos los nombres que corresponden a la fecha en la matriz (nótese que entrado la fórmula como matricial)

La formula anterior es uno de los argumentos de

=COINCIDIR(C14, DESREF(tabla,COINCIDIR(C13,fechas,0),0,1,COLUMNAS(tabla)),0)

Si pegamos esta fórmula en una celda veremos que el resultado es 4

ya que el valor de C14 es “Mario” y este valor es el cuarto en el vector que hemos obtenido con la fórmula anterior.

Si reducimos ahora la fórmula en C15 a =DESREF(tabla,0,4) vemos que el “ancla” de DESREF la celda C3, con 0 filas des desvío y 4 columnas a la derecha.

Technorati Tags: MS Excel

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