ztevenreal
Solía entretenerse a la ruleta de fórmulas cada vez que arrastraba el compensador de relleno, esperando que Excel no malinterpretara mis intenciones. ¿Se suponía que debía circunvalar la columna, la fila o ambas? Luego, los signos de dólar esparcidos por mis fórmulas se sintieron como minas terrestres esperando hacer chasquear mi dialéctica. Perdí más horas de las que quiero tolerar tratando de entender por qué una narración de apariencia perfectamente inocente como Sheet3!$AB$142 insistía en señalar poco erróneo.
Lo que finalmente me ayudó a romper ese ciclo fue asimilar a usar rangos con nombre en Excel no solo como anclajes estables sino igualmente como referencias relativas flexibles que se comportaban exactamente como yo pretendía. Una vez que comencé a encargar en ellos, dejé de forzar a Excel a recalcular las mismas cosas miles de veces y ya no tuve que decodificar direcciones crípticas de celdas u hojas solo para evitar que una fórmula colapsara.
Usar rangos con nombre como referencias estáticas y relativas
No se comercio solo de legibilidad
La mayoría de las personas aprenden primero acerca de los rangos con nombres como una aparejo de legibilidad, y yo igualmente. Inicialmente supuse que eran solo una forma de reemplazar referencias crípticas de celdas de Excel como A1:A100 con poco más claro, como UnitsSold. Sin incautación, con el tiempo, me di cuenta de que los rangos con nombre hacen mucho más que hacer que las fórmulas parezcan más limpias o más fáciles de descubrir.
Si alguna vez ha utilizado $A$1 para fijar una narración en su circunstancia, ya sabe lo tedioso que es nutrir esos signos de dólar en docenas de fórmulas. Una fila que se desalinea puede alterar los totales, hacer que las búsquedas devuelvan títulos incorrectos y hacer que la comprobación de errores parezca ficticio. El uso de un rango con nombre elimina estos problemas.
Cuando tienes una celda que siempre debe permanecer fija, como la momento de hoy, una tasa de descuento, un tipo de cambio o cualquier otra entrada constante, puedes asignarle un nombre como TodayDate o DiscountRate. Una vez que ese nombre existe, puede colocarlo en cualquier fórmula y Excel siempre lo extraerá de la celda correcta, independientemente de dónde se encuentre la fórmula o qué tan acullá se copie:
=B5*(1-(DiscountRate))
Los rangos con nombre igualmente ofrecen más flexibilidad de lo que mucha muchedumbre cree. No se limitan a referencias absolutas; pueden imitar referencias relativas a través de definiciones cuidadosamente construidas. Por ejemplo, si usa poco como =!A$1 en un rango con nombre Se refiere a campo, Excel siempre devolverá la celda A1 de cualquier hoja en la que se encuentre la fórmula. Normalmente, un rango con nombre incluye una narración de hoja absoluta como=”Hoja1″!$A$1, lo que lo obliga a apuntar a esa hoja para siempre. Sin incautación, si define un rango con nombre sin un nombre de hoja, o utiliza referencias de celda relativas en el interior de la definición, puede crear un rango con nombre relativo. Esto convierte el nombre en un componente de fórmula reutilizable que se ajusta según la hoja en la que aparece.
Este truco de Excel me permite escribir fórmulas como un humano
Fórmulas de Excel más inteligentes con la simplicidad del verbo periódico.
Como ejemplo, imagine que tiene un ejemplar de presupuesto con una hoja separada para cada mes, donde la celda B7 siempre contiene el ingreso neto de ese mes. Es posible que desee una forma sencilla y coherente de hacer narración a ese valía en cada hoja. Prensa Ctrl + F3 o ir a Fórmulas –> Administrador de nombres -> Nuevo. Nombra la celda NetIncome e ingresa lo próximo en el Se refiere a campo:
=!$B$7
Luego, haga clic DE ACUERDO. El signo de interjección sin nombre de hoja le dice a Excel que mire la hoja presente y, oportuno a que tanto la fila como la columna están bloqueadas, el nombre siempre apunta a la celda B7 en cualquier hoja que albergue la fórmula que usa NetIncome.
Una vez definido, puede utilizar NetIncome en cualquier hoja del ejemplar. Si hubiera utilizado una narración absoluta como “=HojaA!$B$7” y luego hubiera escrito “=Ingreso Neto” en la Hoja B, Excel devolvería incorrectamente el ingreso neto de la Hoja A. El rango con nombre relativo evita ese problema por completo, asegurando que la narración siga teniendo en cuenta el contexto.
Este truco es mejor cuando tienes que crear informes o plantillas que repitan la misma estructura en varias hojas. Siempre que aparecen datos secreto en la misma ubicación relativa, ya sea un encabezado en A1, una momento en B1 o un total en Z10, un rango con nombre relativo mantiene sus fórmulas limpias, estables y muy fáciles de nutrir.
Para manejar funciones volátiles de guisa efectiva
Un nombre que reemplaza miles de recálculos
Funciones como HOY, AHORA, COMPENSACIÓN e INDIRECTA se recalculan cada vez que se actualiza su ejemplar, incluso si no ha cambiado mínimo en sus argumentos. Si su hoja de cálculo contiene solo unas pocas instancias de ellos, todo funcionará sin problemas. Sin incautación, si estas funciones aparecen en miles de filas, su ejemplar comenzará a parecer demasiado complicado.
En circunstancia de intercalar “=HOY()” en cada fórmula que necesita la momento presente, calcúlela una vez, ya sea en una celda auxiliar o como un rango con nombre creado a través del Administrador de nombres. Posteriormente de eso, puedes usar el nombre que definiste (como “HoyFecha”) donde lo necesites. Luego, un solo cálculo reemplaza cientos o miles de cálculos redundantes, que es esencialmente la lectura de hoja de cálculo del almacenamiento en gusto del navegador. Y puedes hacer lo mismo con todas las demás funciones volátiles.
Sin incautación, los rangos con nombre pueden aumentar la volatilidad de estas funciones si las usa incorrectamente. Cuando define un rango con nombre usando una función volátil como OFFSET, cada fórmula que se saco en ese nombre igualmente se volverá volátil, lo que a veces puede ser desastroso.
Por ejemplo, imagine que crea un rango con nombre llamado WholeSales para cubrir automáticamente todos los datos en la columna A a medida que el número de filas se expande o contrae usando OFFSET:
=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)
Cualquier fórmula que contenga WholeSales se volverá a calcular cada vez que haya un cambio en su ejemplar de trabajo, simplemente porque contiene OFFSET. Si diez fórmulas se refieren a WholeSales, las diez se recalculan con cada cambio. Si un panel arduo contiene mil fórmulas vinculadas a ese rango, todas las mil se recalculan continuamente, incluso cuando sus datos de entrada no han cambiado. El resultado es un recálculo constante e innecesario que puede parar modelos grandes o sofisticados y provocar retrasos o fallos.
El mejor enfoque es evitar funciones volátiles como OFFSET en los rangos con nombre y encargar en alternativas no volátiles como INDEX:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Dicho esto, no es necesario evitar por completo las funciones volátiles. Puede usarlos en rangos con nombre cuando cumplan un propósito necesario, siempre que comprenda el costo de rendimiento y los aplique con cuidado.
El nuevo categoría de funciones de Excel te romperá el cerebro en el buen sentido
Una vez que los comprende, las hojas de cálculo se sienten vivas.
El flanco equívoco de los rangos con nombre
Potente, sí, pero acomodaticio de usar mal
Los rangos con nombre son extremadamente bártulos, pero es acomodaticio entender por qué algunas personas los evitan por completo. La veterano preocupación es que cuando un ejemplar acumula cientos de nombres, cada uno de los cuales apunta a una celda o fórmula diferente, resulta difícil rastrear la dialéctica subyacente. He pabellón a muchedumbre describir este caos como Name Spaghetti, y el término encaja. Varios escenarios pueden causar problemas, pero si los conoce, estará un paso más cerca de evitarlos.
En primer circunstancia, los rangos con nombre pueden existir a nivel de hoja (almacén) o a nivel de ejemplar (integral). Cuando duplica una hoja que contiene nombres locales, Excel duplica automáticamente esos nombres y actualiza sus Se refiere a campos. Por ejemplo, Hoja1!$D$11 se convierte en Hoja1 (2)’!$D$11, y si luego cambia el nombre de la hoja, Excel ajusta la narración en consecuencia, como Cambiar!$D$11. Este comportamiento funciona correctamente hasta que comienza a fusionar libros de trabajo o encuentra una situación en la que ya existe un nombre a nivel de ejemplar de trabajo. Cuando copia en una hoja que contiene un nombre almacén que coincide con uno integral, Excel puede solicitarle que elija qué nombre conservar. En ese punto, puede resultar difícil nominar la opción correcta, por lo que es mejor nutrir nombres claros y únicos antiguamente de combinar archivos.
Excel igualmente almacena nombres internamente, por lo que puede acumular fácilmente cientos de nombres huérfanos o no utilizados en sus libros antiguos o muy editados. Es una buena idea depurar su Administrador de nombres con regularidad, porque si continúa agregando rangos de nombres en muchos archivos, puede pasar fácilmente los 100.000 nombres. En ese punto, el rendimiento moroso se convierte en una posibilidad, dependiendo de su lectura de Excel.
Esta facilidad regular es importante por otra razón: cuando eliminas celdas que alguna vez pertenecieron a un rango con nombre, el nombre no se repara solo. ¡En cambio, se convierte en una narración inválida que difunde #REF! errores dondequiera que aparezca el rango famoso, y esos son uno de los errores de Excel más comunes con los que altercar.
No utilizarás Excel hasta que domines estas funciones de error
Estas funciones harán que sus hojas de Excel sean resistentes.
Menos fórmulas rotas, hojas más estables y una forma más sencilla de construir
Cuando utiliza cuidadosamente los rangos con nombre, sus fórmulas serán más limpias, las hojas serán más fáciles de establecer y los parámetros secreto tendrán un hogar claro y centralizado. En circunstancia de despabilarse entre filas y columnas tratando de rememorar qué celda contiene el tipo de cambio o el valía comienzo, puede encargar en nombres significativos que sigan su dialéctica dondequiera que vaya.
Son especialmente potentes en modelos estructurados donde las constantes se encuentran en una hoja dedicada, lo que simplifica las actualizaciones y reduce drásticamente la posibilidad de errores. Cuando necesite auditar una fórmula, seleccione el nombre y presione Ctrl + GRAMO lo lleva directamente a su fuente, ahorrando minutos que de otro modo se convertirían en horas.
