Ejemplo de la función INDIRECTO y HORA

indirecto_hora.xlsx

Descargar XLSX • 15KB

El siguiente ejemplo consiste en que somos una persona que trabajamos en una empresa donde desde las ocho de la mañana hasta las dos de la tarde, estamos atendiendo un teléfono.

Tenemos hecho un planing, podíamos decir, con las horas, y, queremos en estas columnas se vayan introduciendo las horas que correspondan, por ejemplo, una hora 8:30, debe de colocarse en la columna de las ocho.

Por último, queremos saber en qué hora hubo más llamadas.

En el siguiente modelo es donde introducimos las horas.

Decir que trabajamos con un modelo donde ya hay horas introducidas.

Pues vamos a buscar la primera hora que son las ocho.

Lo haremos en la celda G2, que es la hora ocho.

Tengo que preguntar que si la hora en el rango desde A2 donde está la primera hora de llamada hasta la última fila ocupada, por lo que vamos a usar la función HORA para extraer la hora, es igual a la hora ocho, pues, que devuelva las columnas A y B, es decir, hora de entrada, y, hora de salida, para ello, vamos a usar el condicional SI.

Como última fila voy a poner la fila 100.

=SI(HORA(A2:A100)=G1;A2:B100;""))

Aceptamos, y, tenemos una matriz desbordada con las horas que pertenecen a las ocho.

Con la función ORDENAR, ordenamos las horas.

=ORDENAR(SI(HORA(A2:A100)=G1;A2:B100;""))

Fijamos los rangos de búsquedas.

=ORDENAR(SI(HORA($A$2:$A$100)=G1;$A$2:$B$100;""))

Seleccionamos la función y pulsamos CTRL más C para copiar.

Seleccionamos las celdas donde vamos a copiar con CTRL, tenemos que marcar todas las horas.

Pulsamos CTRL más V para pegar.

Ya tenemos nuestras horas cada una en su sitio.

Ahora en vertical voy a poner las horas, para calcular la suma de los minutos para cada hora.

Vamos a preguntar que si la hora con la función HORA en el rango A2:A100 es igual a la primera hora, pues que nos reste ambas columnas, en caso contrario, que ponga un texto en blanco.

=SI(HORA(A2:A100)=D2;B2:B100-A2:A100;"")

Sumamos los resultados.

=SUMA(SI(HORA(A2:A100)=D2;B2:B100-A2:A100;""))

Ya tenemos el total de minutos para la primera hora.

Fijamos los rangos de búsquedas, y, arrastramos.

=SUMA(SI(HORA($A$2:$A$100)=D2;$B$2:$B$100-$A$2:$A$100;""))

Ya tenemos los totales de minutos para cada hora.

Lo siguiente que vamos a hacer es aplicar una regla de formato condicional para que quede resaltada la fila de más minutos, así sabremos de un simple vistazo cual ha sido la hora de más conversación.

Para ello, seleccionamos ambas columnas, desplegamos formato condicional y elegimos nueva regla.

Ya lo tenemos también.

Todo esto está bien, pero hemos puesto como última fila la fila 100, pero ¿y si tenemos más de 100 llamadas?

Veamos cómo hacerlo en una celda.

Si escribo la siguiente expresión, obtendré el número de filas alfanuméricas ocupadas de la columna A.

=CONTARA(A:A)

Debemos de tener en cuenta, que este resultado cuenta el encabezado, que no interesa, por lo que a la función CONTARA voy a restarle 1, como resultado tenemos 12.

Si escribo la letra A y lo concateno con CONTARA obtendré una referencia en formato de texto.

="a" & CONTARA(A:A)-1

También podría usar CONTARA junto con DIRECCION de la siguiente manera:

=DIRECCION(CONTARA(A:A);1)

Obtengo la referencia en absoluta.

Entonces, vamos a modificar la primera expresión, la que corresponde con la hora de las ocho.

Nosotros hemos pues desde A2 a A100, es este 100, el que tenemos que hacer dinámico.

Para ello, voy a poner entre comillas dobles desde A2:A, concateno, y, pongo la función CONTARA, y, toda esta función la encerramos en la función INDIRECTO, para que nos lo convierta en una referencia real.

=ORDENAR(SI(HORA(INDIRECTO("$A$2:$A$" & CONTARA(A:A)-1))=G1;INDIRECTO("$A$2:$b$" & CONTARA(A:A)-1);""))

Para el segundo rango que va desde A2 a B100, hacemos lo mismo.

=ORDENAR(SI(HORA(INDIRECTO("$A$2:$A$" & CONTARA(A:A)-1))=G1;INDIRECTO("$A$2:$b$" & CONTARA(A:A)-1);""))

Aceptamos y tenemos los mismos resultados, pero sabiendo cual es la última fila ocupada.

Lo siguiente que queremos es saber cuál fue la llamada de más duración, la hora de inicio y de fin.

Lo primero que voy a hacer es restar ambas columnas.

=Tabla1[Salida]-Tabla1[Entrada])

Obtengo la diferencia de todas las horas.

Voy a sacar el valor máximo de esta resta.

=MAX(Tabla1[Salida]-Tabla1[Entrada])

Voy a comparar el valor máximo con cada resta.

=SI(MAX(Tabla1[Salida]-Tabla1[Entrada])=Tabla1[Salida]-Tabla1[Entrada]

Si hay coincidencia le voy a decir que me devuelva ambas columns, en caso contrario, que ponga un texto en blanco.

=SI(MAX(Tabla1[Salida]-Tabla1[Entrada])=Tabla1[Salida]-Tabla1[Entrada];Tabla1;"")

Ordenamos.

=ORDENAR(SI(MAX(Tabla1[Salida]-Tabla1[Entrada])=Tabla1[Salida]-Tabla1[Entrada];Tabla1;""))

Ya tenemos la hora de inicio y de fin de la llamada de más duración.

Por último, vamos a aplicar una regla de formato condicional para resaltar dentro del modelo dicha llamada.

Ya sabemos que la función a usar en formato condicional debe de ser una que devuelva verdadero, o, falso, por lo que voy a usar el operador lógico Y, para preguntar por cada valor de la columna A y B.