La función OFFSET de Excel ralentizará el cálculo de su libro. Existe una alternativa mejor: una sintaxis inusual de INDEX.
Este es un consejo de nicho. Hay una función sorprendentemente flexible llamada OFFSET. Es flexible porque puede apuntar a un rango de diferente tamaño que se calcula sobre la marcha. En la imagen de abajo, si alguien cambia el menú desplegable # Qtrs en H1 de 3 a 4, el cuarto argumento de OFFSET se asegurará de que el rango se expanda para incluir cuatro columnas.
Los gurús de las hojas de cálculo odian el OFFSET porque es una función volátil. Si va a una celda completamente no relacionada e ingresa un número, se calcularán todas las funciones de DESPLAZAMIENTO. Incluso si esa celda no tiene nada que ver con H1 o B2. La mayoría de las veces, Excel tiene mucho cuidado de solo perder el tiempo calculando las celdas que necesitan calcular. Pero una vez que introduce OFFSET, todas las celdas OFFSET, más todo lo que desciende desde OFFSET, comienzan a calcular después de cada cambio en la hoja de trabajo.
Estaba juzgando las finales de ModelOff 2013 en la ciudad de Nueva York cuando algunos de mis amigos de Australia señalaron una extraña solución. En la fórmula siguiente, hay dos puntos antes de la función INDICE. Normalmente, la función INDICE que se muestra a continuación devolvería el 1403 de la celda D2. Pero cuando coloca dos puntos a cada lado de la función INDICE, comienza a devolver la dirección de celda D2 en lugar del contenido de D2. Esto es salvaje que funcione.
¿Por qué importa esto? INDEX no es volátil. Obtiene toda la bondad flexible de OFFSET sin los recálculos que consumen mucho tiempo una y otra vez.
Aprendí este consejo por primera vez de Dan Mayoh en Fintega. Gracias a Access Analytic por sugerir esta función.
Ver video
Transcripción del video
Aprenda Excel del podcast, episodio 2048 -: ¡EL ÍNDICE reemplazará un OFFSET volátil!
Oye, estoy publicando en podcast todos mis consejos de este libro, haz clic en esa "i" en la esquina superior derecha para acceder a la lista de reproducción.
¡Muy bien, OFFSET es una función asombrosa! OFFSET nos permite especificar una celda de la esquina superior izquierda y luego usar variables para definir desde allí cuántas filas hacia abajo, cuántas filas sobre y luego definir una forma, de acuerdo. Así que aquí, si quiero sumar, o hacer un promedio de, digamos 3/4, esta fórmula de aquí echará un vistazo a la fórmula. El OFFSET dice que estamos comenzando desde B2, comenzando desde Q1, vamos hacia abajo 0, sobre 0, la forma tendrá 1 fila de alto y será H1, en otras palabras, 3 celdas de ancho, está bien. Entonces, en este caso, todo lo que realmente estamos haciendo es cambiar la cantidad de celdas que sumamos, siempre comenzamos de nuevo en B2, o B3 o B4 a medida que copio, y luego decide cuántas celdas de ancho. Muy bien, OFFSET es algo genial, hace todo tipo de funciones asombrosas, pero aquí está la molestia, ¡es volátil!Lo que significa que incluso si algo no está en la cadena de cálculo, Excel se tomará el tiempo para volver a calcularlo, lo que ralentizará las cosas, de acuerdo.
Esta increíble versión de ÍNDICE, correcto, normalmente si pregunto por el ÍNDICE de estas 4 celdas, 3, devolverá el número 1403. Sin embargo, cuando pongo dos puntos antes o después del ÍNDICE, sucede algo muy diferente, echaremos un vistazo a esta fórmula aquí. Así que índice de las 4 celdas, cuál quiero, quiero la tercera, pero ves que hay un: allí mismo. Así que siempre vamos a ir de B2: E2, y les mostraré si vamos a Fórmulas, Evaluar Fórmula, está bien, así que aquí mismo se va a calcular el número 3. Y aquí, el ÍNDICE con: siguiente a él, en lugar de decirnos 1403, que es como se calcularía normalmente ÍNDICE, devolverá $ D2, y luego el PROMEDIO hará el promedio de esos 3. Absolutamente asombroso, la forma en que esto funciona y el beneficio adicional, no es volátil, está bien,y esto incluso se puede utilizar para reemplazar un OFFSET increíblemente complejo.
Entonces, aquí con este OFFSET nos basamos en estos valores. Si elijo Q2 y Central, de acuerdo, estas fórmulas usan MATCH y COUNTIF para averiguar cuántas filas hacia abajo, cuántas columnas sobre, qué altura, qué ancho. Y luego, el OFFSET aquí está usando todos esos valores para calcular la mediana. Solo haremos una prueba para asegurarnos de que está funcionando, entonces = MEDIANA de ese rango azul allá, mejor que sea 71, está bien, y elegiremos otra cosa aquí, Q3 y Oeste, entonces. Presione F2, solo voy a arrastrar esto y cambiar su tamaño para reescribir esa fórmula, presione Entrar, y está funcionando con el OFFSET.
Bueno, aquí, usando un ÍNDICE, en realidad tengo dos puntos en el medio con un ÍNDICE en el lado izquierdo y un ÍNDICE en el lado derecho, observe cómo se calcula esto en Evaluar fórmula. Entonces comienza, Evaluará, Evaluará, y aquí mismo, INDICE está a punto de convertirlo en una dirección de celda. Entonces, H16 es el 1 °, Oeste, Q3, y el lado derecho evaluará, emparejará más y luego a la derecha cambiará a I20. Entonces, el frío, el frío no volátil para reemplazar un OFFSET usando la función INDICE. Muy bien, este consejo y muchos más en este libro, haga clic en esa "i" en la esquina superior derecha para comprar el libro.
Muy bien, resumen: OFFSET, una función increíble y flexible, una vez que domines, puedes hacer todo tipo de cosas. Apunta a una celda superior izquierda variable, apunta a un rango que tiene una forma variable, pero es volátil, por lo que calculan en cada cálculo. Excel generalmente hace un cálculo inteligente o recalcula las celdas que deben calcularse, pero con OFFSET siempre se calculará. En lugar de DESPLAZAMIENTO, puede usar INDICE: o: INDEX o incluso INDEX: INDEX, siempre que INDEX tenga dos puntos al lado, devolverá una dirección de celda en lugar del valor de esa celda. ¡Y el beneficio es que INDEX no es volátil!
Bien, quiero agradecerles por pasar, ¡nos vemos la próxima vez para otro netcast de!
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2048.xlsm
