Theo usa una hoja de cálculo de Excel para realizar un seguimiento de las reservas en su empresa. Los datos constan de solo tres columnas. El primero es el nombre de una persona, el segundo el número de la primera semana (1-52) de la reserva y el tercero el número de la última semana de la reserva.

Las personas se pueden reservar para varias semanas (es decir, la semana de inicio es 15 y la semana final es 19). Theo necesita una forma de ingresar un número de semana y luego hacer que una fórmula determine qué nombre (columna A) está asociado con ese número de semana. Los datos no están ordenados en ningún orden en particular y la compañía no permitirá que Theo use una macro para obtener el resultado (tiene que ser una fórmula).

La situación de Theo parece bastante simple, pero está llena de trampas cuando se busca una solución. Mirar los datos potenciales (como se muestra en la siguiente figura) ilustra rápidamente por qué este es el caso. (Ver figura 1)

image

Figura 1. Datos potenciales para el problema de Theo.

Tenga en cuenta que los datos (como dijo Theo) no están en ningún orden en particular.

Tenga en cuenta también que hay algunas semanas en las que no hay reservas (como la semana 5 o 6), las semanas en las que hay varias personas (como la semana 11 o 16) y las semanas en las que hay alguien reservado, pero la semana el número no aparece en la columna B o C (como la semana 12 o 17).

Antes de empezar a buscar posibles soluciones, supongamos que la semana que desea conocer es la celda E1. Debe nombrar este rango como Consulta. Además, nombre el rango que contiene los nombres de las personas (en este ejemplo, celdas A2: A10) como ResNames, las semanas iniciales (B2: B10) como StartWeeks y las semanas finales (C2: C10) como EndWeeks. Finalmente, defina un nombre para toda la tabla (A2: C10), como MyData. Este nombre, aunque no es estrictamente necesario, facilitará mucho la comprensión de las fórmulas.

Una posible solución es agregar lo que comúnmente se conoce como una «columna auxiliar». Agregue lo siguiente a la celda D2:

=IF(AND(Query>=B2,Query<=C2),"RESERVED","")

Copie la fórmula hacia abajo, para tantas celdas como nombres haya en la tabla. (Por ejemplo, cópielo hasta la celda D10.) Cuando coloca un número de semana en la celda E1, la palabra «RESERVADO» aparece a la derecha de cualquier reserva que incluya ese número de semana. También es fácil ver si hay varias personas reservadas para esa semana o si no hay personas reservadas para esa semana. Incluso puede aplicar un autofiltro y seleccionar mostrar solo aquellos registros con la palabra «RESERVADO» en la columna D.

Si lo desea, puede renunciar a la columna de ayuda y considerar el uso de formato condicional para mostrar quién está reservado para una semana deseada.

Simplemente seleccione los nombres en la columna A y agregue una regla de formato condicional que use la siguiente fórmula:

=AND(Query>=B2,Query<=C2)

(La forma en que ingresa las reglas de formato condicional se ha descrito ampliamente en otras ediciones de ExcelTips.) Establezca la regla para que cambie el sombreado (patrón) aplicado a la celda, y podrá ver fácilmente qué reservas se aplican a la semana que le interesa.

Otro enfoque consiste en utilizar una fórmula de matriz. Seleccione algunas celdas más que la cantidad de reservas superpuestas que espera, y luego ingrese lo siguiente en esas celdas presionando Ctrl + Shift + Enter:

=IFERROR(INDEX(ResNames,LARGE((StartWeeks<=Query)(EndWeeks>=Query)(ROW(ResNames)),ROW()-1)-1),"")

Al elegir el número de celdas que desea que ocupe esta fórmula de matriz, observe, por ejemplo, el número de personas que pueden reservarse durante la semana 11. En el ejemplo que se muestra en este consejo, son 2 personas. Seleccione más que ese número de celdas y luego coloque la fórmula de matriz en esas celdas.

Si espera tener 20 personas potencialmente reservadas para la misma semana, entonces querrá elegir un número mayor de celdas, como 20 o 30. Simplemente seleccione las celdas, coloque la fórmula en la barra de fórmulas y luego presione Ctrl + Shift + Enter.

Finalmente, debería considerar revisar cómo se distribuyen sus datos.

Puede crear una hoja de trabajo que tenga números de semana en la columna A (1 a 52 o 53) y luego colocar los nombres en la columna B. Si una persona estuviera reservada por dos semanas, su nombre aparecería en la columna B dos veces, una al lado de cada dos semanas que reservaron.

Con sus datos en este formato, podría escanear fácilmente los datos para ver qué semanas están disponibles, cuáles se toman y quién las toma.

Si desea realizar algún tipo de búsqueda, es fácil usar la función BUSCARV basada en el número de la semana, ya que es la primera columna de los datos, en orden ordenado.

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (11078) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365. Puede encontrar una versión de este consejo para la interfaz de menú anterior de Excel aquí:

link: / excel-Determining_a_Name_for_a_Week_Number [Determinar un nombre para un número de semana].