Cómo establecer un rango de valores en Excel

Cuando se utiliza Microsoft Excel, muy a menudo el usuario no sabe de antemano cuánta información habrá en la tabla como resultado. Por lo tanto, no entendemos en todas las situaciones qué rango debe cubrirse. Después de todo, un conjunto de celdas es un concepto variable. Para deshacerse de este problema, es necesario hacer que la generación de rango sea automática para que se base únicamente en la cantidad de datos que ingresó el usuario.

Cambio automático de rangos de celdas en Excel

La ventaja de los rangos automáticos en Excel es que facilitan mucho el uso de fórmulas. Además, permiten simplificar significativamente el análisis de datos complejos que contienen una gran cantidad de fórmulas, que incluyen muchas funciones. Puede darle un nombre a este rango y luego se actualizará automáticamente según los datos que contenga.

Cómo hacer un cambio de rango automático en Excel

Suponga que usted es un inversionista que necesita invertir en algún objeto. Como resultado, queremos obtener información sobre cuánto puede ganar en total durante todo el tiempo que el dinero funcionará para este proyecto. Sin embargo, para obtener esta información, necesitamos monitorear regularmente cuánto beneficio total nos brinda este objeto. Haga el mismo informe que en esta captura de pantalla.

A primera vista, la solución es obvia: solo necesita sumar toda la columna. Si en él aparecen entradas, el importe se actualizará de forma independiente. Pero este método tiene muchas desventajas:

1. Si el problema se resuelve de esta manera, no será posible utilizar las celdas incluidas en la columna B para otros fines.
2. Tal tabla consumirá mucha RAM, lo que hará que sea imposible usar el documento en computadoras débiles.

Por lo tanto, es necesario resolver este problema a través de nombres dinámicos. Para crearlos, debe realizar la siguiente secuencia de acciones:

1. Vaya a la pestaña "Fórmulas", que se encuentra en el menú principal. Habrá una sección "Nombres definidos", donde hay un botón "Asignar un nombre", en el que debemos hacer clic.
2. A continuación, aparecerá un cuadro de diálogo en el que deberá completar los campos como se muestra en la captura de pantalla. Es importante notar que necesitamos aplicar la función = DESPLAZAMIENTO junto con la función CHEQUEpara crear un rango de actualización automática. 
3. Después de eso, necesitamos usar la función. SUM, que utiliza nuestro rango dinámico como argumento. 

Después de completar estos pasos, podemos ver cómo la cobertura de las celdas pertenecientes al rango de "ingresos" se actualiza a medida que agregamos nuevos elementos allí.

Función DESPLAZAMIENTO en Excel

Veamos las funciones que registramos en el campo "rango" anteriormente. Usando la función ELIMINACIÓN podemos determinar la cantidad de rango dada la cantidad de celdas en la columna B que se llenan. Los argumentos de la función son los siguientes:

1. Celda de inicio. Con este argumento, el usuario puede indicar qué celda del rango se considerará superior izquierda. Informará hacia abajo y hacia la derecha.
2. Rango compensado por filas. Usando este rango, establecemos el número de celdas por las cuales debe ocurrir un desplazamiento desde la celda superior izquierda del rango. Puede usar no solo valores positivos, sino también cero y menos. En este caso, el desplazamiento puede no ocurrir en absoluto, o se llevará a cabo en la dirección opuesta.
3. Rango compensado por columnas. Este parámetro es similar al anterior, solo que le permite establecer el grado de desplazamiento horizontal del rango. Aquí también puede utilizar valores cero y negativos.
4. La cantidad de rango en altura. De hecho, el título de este argumento nos aclara lo que significa. Este es el número de celdas por el cual el rango debe aumentar.
5. El valor del rango en ancho. El argumento es similar al anterior, solo que se refiere a las columnas.

No necesita especificar los dos últimos argumentos si no es necesario. En este caso, el valor del rango será solo una celda. Por ejemplo, si especifica la fórmula =DESPLAZAMIENTO(A1;0;0), esta fórmula se referirá a la misma celda que la del primer argumento. Si el desplazamiento vertical se establece en 2 unidades, en este caso la celda se referirá a la celda A3. Ahora describamos en detalle lo que significa la función CHEQUE.

Función COUNT en Excel

Usando la función CHEQUE determinamos cuántas celdas en la columna B hemos llenado en total. Es decir, utilizando dos funciones, determinamos cuántas celdas del rango se llenan y, en función de la información recibida, determinamos el tamaño del rango. Por tanto, la fórmula final será la siguiente: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Echemos un vistazo a cómo entender correctamente el principio de esta fórmula. El primer argumento apunta a dónde comienza nuestro rango dinámico. En nuestro caso, esta es la celda B2. Otros parámetros tienen coordenadas cero. Esto sugiere que no necesitamos compensaciones relativas a la celda superior izquierda. Todo lo que estamos completando es el tamaño vertical del rango, que usamos la función como CHEQUE, que determina el número de celdas que contienen algunos datos. El cuarto parámetro que completamos es la unidad. Así mostramos que el ancho total del rango debe ser una columna.

Así, utilizando la función CHEQUE el usuario puede usar la memoria de la manera más eficiente posible cargando solo aquellas celdas que contienen algunos valores. En consecuencia, no habrá errores adicionales en el trabajo asociados con el bajo rendimiento de la computadora en la que funcionará la hoja de cálculo.

En consecuencia, para determinar el tamaño del rango según el número de columnas, debe realizar una secuencia de acciones similar, solo que en este caso debe especificar la unidad en el tercer parámetro y la fórmula en el cuarto. CHEQUE.

Vemos que con la ayuda de fórmulas de Excel, no solo puede automatizar cálculos matemáticos. Esto es solo una gota en el océano, pero de hecho te permiten automatizar casi cualquier operación que se te ocurra.

Gráficos dinámicos en Excel

Entonces, en el último paso, pudimos crear un rango dinámico, cuyo tamaño depende completamente de cuántas celdas llenas contiene. Ahora, en función de estos datos, puede crear gráficos dinámicos que cambiarán automáticamente tan pronto como el usuario realice cambios o agregue una columna o fila adicional. La secuencia de acciones en este caso es la siguiente:

1. Seleccionamos nuestro rango, luego de lo cual insertamos un gráfico del tipo "Histograma con agrupación". Puede encontrar este elemento en la sección "Insertar" en la sección "Gráficos - Histograma".
2. Hacemos clic con el botón izquierdo del mouse en una columna aleatoria del histograma, después de lo cual se mostrará la función =SERIES() en la línea de función. En la captura de pantalla puedes ver la fórmula detallada. 
3. Después de eso, se deben realizar algunos cambios en la fórmula. Debe reemplazar el rango después de "Sheet1!" al nombre del rango. Esto dará como resultado la siguiente función: =FILA(Hoja1!$B$1;;Hoja1!ingresos;1)
4. Ahora queda agregar un nuevo registro al informe para verificar si el gráfico se actualiza automáticamente o no.

Echemos un vistazo a nuestro diagrama.

Vamos a resumir cómo lo hicimos. En el paso anterior, creamos un rango dinámico, cuyo tamaño depende de cuántos elementos contiene. Para hacer esto, usamos una combinación de funciones CHEQUE и ELIMINACIÓN. Hicimos este rango con nombre, y luego usamos la referencia a este nombre como el rango de nuestro histograma. Qué rango específico elegir como fuente de datos en la primera etapa no es tan importante. Lo principal es reemplazarlo con el nombre del rango más adelante. De esta manera puedes ahorrar mucha memoria RAM.

Rangos con nombre y sus usos

Ahora hablemos con más detalle sobre cómo crear correctamente rangos con nombre y usarlos para realizar las tareas que están configuradas para el usuario de Excel.

Por defecto, usamos direcciones de celda regulares para ahorrar tiempo. Esto es útil cuando necesita escribir un rango una o más veces. Si necesita usarse todo el tiempo o necesita ser adaptable, entonces se deben usar rangos con nombre. Hacen que la creación de fórmulas sea mucho más fácil, y no será tan difícil para el usuario analizar fórmulas complejas que incluyen una gran cantidad de funciones. Describamos algunos de los pasos involucrados en la creación de rangos dinámicos.

Todo comienza con nombrar la celda. Para hacer esto, simplemente selecciónelo y luego escriba el nombre que necesitamos en el campo de su nombre. Es importante que sea fácil de recordar. Hay algunas restricciones a tener en cuenta al nombrar:

1. La longitud máxima es de 255 caracteres. Esto es suficiente para asignar un nombre que tu corazón desee.
2. El nombre no debe contener espacios. Por lo tanto, si contiene varias palabras, entonces es posible separarlas usando el carácter de subrayado.

Si más adelante en otras hojas de este archivo necesitamos mostrar este valor o aplicarlo para realizar más cálculos, entonces no hay necesidad de cambiar a la primera hoja. Simplemente puede escribir el nombre de esta celda de rango.

El siguiente paso es crear un rango con nombre. El procedimiento es básicamente el mismo. Primero debe seleccionar un rango y luego especificar su nombre. Después de eso, este nombre se puede usar en todas las demás operaciones de datos en Excel. Por ejemplo, los rangos con nombre se usan a menudo para definir la suma de valores.

Además, es posible crear un rango con nombre usando la pestaña Fórmulas usando la herramienta Establecer nombre. Después de que lo seleccionemos, aparecerá una ventana donde debemos elegir un nombre para nuestro rango, así como especificar el área a la que se extenderá manualmente. También puede especificar dónde operará este rango: dentro de una sola hoja o en todo el libro.

Si ya se ha creado un rango de nombres, entonces para usarlo, hay un servicio especial llamado administrador de nombres. Permite no solo editar o agregar nuevos nombres, sino también eliminarlos si ya no se necesitan.

Debe tenerse en cuenta que al usar rangos con nombre en fórmulas, luego de eliminarlo, las fórmulas no se sobrescribirán automáticamente con los valores correctos. Por lo tanto, pueden ocurrir errores. Por lo tanto, antes de eliminar un rango con nombre, debe asegurarse de que no se use en ninguna de las fórmulas.

Otra forma de crear un rango con nombre es obtenerlo de una tabla. Para hacer esto, hay una herramienta especial llamada "Crear a partir de la selección". Según entendemos, para poder usarlo, primero debe seleccionar el rango que editaremos y luego establecer el lugar en el que tenemos los encabezados. Como resultado, en función de estos datos, Excel procesará automáticamente todos los datos y los títulos se asignarán automáticamente.

Si el título contiene varias palabras, Excel las separará automáticamente con un guión bajo.

Por lo tanto, descubrimos cómo crear rangos dinámicos con nombre y cómo le permiten automatizar el trabajo con grandes cantidades de datos. Como puede ver, es suficiente usar varias funciones y las herramientas del programa integradas en la funcionalidad. No hay nada complicado en absoluto, aunque a primera vista pueda parecerlo a un principiante.