Descarga el libro de trabajo de ejemplo
Hemos hablado en otros artículos sobre cómo hay funciones como OFFSET e INDIRECT que son volátiles. Si comienza a usar muchos de estos en una hoja de cálculo o tiene muchas celdas que dependen de la función volátil, puede hacer que su computadora dedique un tiempo notable a recalcular cada vez que intente cambiar una celda. En lugar de frustrarse por cómo su computadora no es lo suficientemente rápida, este artículo explorará formas alternativas de resolver las situaciones comunes que las personas usan OFFSET e INDIRECT.
Reemplazo de OFFSET para crear una lista dinámica
Después de aprender sobre la función OFFSET, es un error común pensar que es la única forma de devolver un resultado con tamaño dinámico utilizando los últimos dos argumentos. Veamos una lista en la columna A donde nuestro usuario podría decidir más adelante agregar elementos adicionales.
Para hacer un menú desplegable en la celda C2, puede definir un rango con nombre con una fórmula volátil como
= COMPENSACIÓN ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
Con la configuración actual, esto ciertamente devolvería una referencia al rango A2: A5. Sin embargo, hay otra forma de utilizar el ÍNDICE no volátil. Para hacer esto, piense en que escribimos una referencia al rango que va de A2 a A5. Cuando escriba "A2: A5", no piense en esto como un solo dato, sino como un "Punto de partida" y "Punto de finalización" separados por dos puntos (por ejemplo, Punto de partida: Punto de finalización). En una fórmula, tanto el punto inicial como el punto final pueden ser el resultado de otras funciones.
Esta es la fórmula que usaremos para crear un rango dinámico usando la función INDICE:
= $ A $ 2: INDICE ($ A: $ A, COUNTA ($ A: $ A))
Tenga en cuenta que hemos indicado que el punto de partida para este rango siempre será A2. En el otro lado de los dos puntos, usamos INDICE para determinar dónde estará el EndingPoint. COUNTA determinará que hay 5 celdas con datos en la columna A, por lo que nuestro INDICE creará una referencia a A5. Por lo tanto, la fórmula se evalúa así:
= $ A $ 2: ÍNDICE ($ A: $ A, COUNTA ($ A: $ A)) = $ A $ 2: ÍNDICE ($ A: $ A, 5) = $ A $ 2: $ A5
Con esta técnica, puede construir dinámicamente una referencia a cualquier lista, o incluso una tabla bidimensional usando la función INDICE. En una hoja de cálculo con una gran cantidad de funciones OFFSET, reemplazar las OFFSET con INDEX permitirá que su computadora comience a funcionar mucho más rápido.
Reemplazo de INDIRECTO por nombres de hoja
La función INDIRECTA a menudo se llama cuando los libros de trabajo se han diseñado con datos dispersos en varias hojas de trabajo. Si no puede obtener todos los datos en una sola hoja, pero no desea utilizar una función volátil, es posible que pueda utilizar ELEGIR.
Considere el siguiente diseño, donde tenemos datos de ventas en 3 hojas de trabajo diferentes. En nuestra hoja Resumen, hemos seleccionado de qué trimestre nos gustaría ver los datos.
Nuestra fórmula en B3 es:
= ELEGIR (PARTIR (B2, D2: D4, 0), Otoño! A2, Invierno! A2, Primavera! A2)
En esta fórmula, la función COINCIDIR determinará qué área queremos devolver. Esto le dice a la función ELEGIR cuál de los siguientes rangos devolver como resultado.
También puede utilizar la función ELEGIR para devolver un rango mayor. En este ejemplo, tenemos una tabla de datos de ventas en cada una de nuestras tres hojas de trabajo.
En lugar de escribir una función INDIRECTA para construir el nombre de la hoja, puede dejar que ELEGIR determine en qué tabla realizar la búsqueda. En mi ejemplo, ya he nombrado las tres tablas tbFall, tbWinter y tbSpring. La fórmula en B4 es:
= BUSCARV (B3, ELEGIR (COINCIDIR (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
En esta fórmula, el PARTIDO va a determinar que queremos los 2Dakota del Norte artículo de nuestra lista. CHOOSE tomará ese 2 y devolverá la referencia a tbWinter. Finalmente, nuestro BUSCARV podrá completar la búsqueda en la tabla dada y encontrará que las ventas totales de Banana en invierno fueron de $ 6000.
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, CHOOSE (2, tbFall, tbWinter, tbSpring), 2, 0) = BUSCARV (B3, tbWinter, 2, 0) = 6000
Esta técnica está limitada por el hecho de que debe completar la función ELEGIR con todas las áreas de las que podría querer obtener un valor, pero le brinda el beneficio de evitar una fórmula volátil. Dependiendo de cuántos cálculos necesite completar, esta habilidad podría resultar bastante valiosa.
