Quizás no sepas cómo utilizar la función DESREF en Excel

¿Sabías que la función DESREF en Microsoft Excel puede ayudarte a hacer muchas otras cosas, no solo referencias de celda? A continuación se muestran formas únicas de usar OFFSET en Excel que quizás no conozca.

La función OFFSET de Microsoft Excel es una herramienta flexible y poderosa. Debido a su complejidad y cantidad de funciones similares, lamentablemente esta función de Excel está infrautilizada.

Esta función relativamente simple puede ayudar a los usuarios a ahorrar tiempo y esfuerzo. Simplifica la obtención de puntos de datos precisos a partir de un conjunto de datos maestros bien ordenado. También permite la creación sencilla de informes detallados, visualizaciones interactivas y paneles complejos.

Crear un conjunto de datos de tamaño personalizado

Uno de los usos más comunes de la función DESREF en Excel es recopilar conjuntos de datos de tamaño variable. Con un conjunto de datos grande, es posible que desee considerar sólo una pequeña porción de los datos en un momento dado.

Por ejemplo, para proporcionar un informe automatizado de los totales de ventas de los empleados, debe obtener el total de una semana o semanas determinadas.

Escribir una función SUMA diferente para cada total puede resultar bastante sencillo. Sin embargo, usar la función OFFSET será más fácil de usar y más rápido de reutilizar.

Primero, ingrese la fecha con la que desea comenzar en la celda B6. Luego, ingrese la cantidad de días que desea ejecutar el informe en B7. A continuación, utilice las funciones simples SUMA y PROMEDIO para obtener más datos para cada empleado durante el período seleccionado:

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))

=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

Ajustar la fecha u hora de inicio es tan sencillo como editar una celda, pero puedes arrastrar y cambiar el tamaño de grupos de datos mucho más fácilmente.

Permite crear informes personalizados

Utilice el mismo método anterior para crear un informe completo personalizando las variables con bastante facilidad. Puede hacer que el primer argumento de la función DESREF haga referencia a otra hoja. Esto le permite separar la entrada de datos y los informes.

La acción anterior también permite arrastrar datos seleccionados de una hoja de trabajo a otra. Además, le permite mostrar solo información relevante para el informe actual. Configurar un informe que muestre los totales diarios utilizando los mismos datos anteriores es bastante sencillo. Puede hacer esto sin recuperar datos de fechas no relacionadas.

La siguiente función de compensación colocada en la celda B5 recuperará la cantidad de días especificada en B2. Comienza en la semana seleccionada en B1 y solo muestra datos del período relevante:

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

Es posible restringir las entradas en B1 y B2 y crear una lista desplegable en Excel con las semanas disponibles mediante la validación de datos.

Paginación de hoja de cálculo

La función DESREF también puede hacer posible la paginación simple de hojas de cálculo, una tarea a menudo asociada con el uso de la pestaña Desarrollador de Excel. Esto puede hacer que el procesamiento de grandes grupos de datos sea más sencillo y eficaz.

Cuando se trabaja con hojas de cálculo que contienen hasta 30.000 filas de datos, puede resultar difícil revisar todos los datos a la vez. Cree un menú desplegable que acepte un rango de números de 1 a 300, lo que le permitirá separar rápidamente los datos por página con 100 resultados por página.

La función DESPLAZAMIENTO siempre tiene su punto de anclaje ubicado en la celda de datos superior. Sin embargo, el segundo argumento es el desplazamiento vertical. Este valor es igual al número de página seleccionado menos 100. El desplazamiento es horizontal, el tercer argumento se puede establecer en 0.

El cuarto argumento se establece en 100, el quinto argumento es el número de columnas del conjunto de datos. Este método hace que dividir y administrar tablas de datos sea extremadamente fácil.

A continuación se muestra un ejemplo de datos paginados. Incluye 500 datos registrados desde un sensor en una hoja y un informe paginado en la otra hoja.

El informe utiliza la validación de datos en la celda B1 para crear un menú desplegable con páginas dentro. La siguiente expresión en la celda A3 crea el cuerpo del informe:

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

A continuación se muestran algunas formas de utilizar la función de compensación en Excel que pocas personas conocen pero que son realmente útiles, especialmente cuando necesita procesar grandes cantidades de datos.

Espero que el artículo te sea útil.