Como establecer un intervalo de valores en Excel

Ao usar Microsoft Excel, moitas veces o usuario non sabe de antemán canta información estará na táboa como resultado. Polo tanto, non entendemos en todas as situacións que rango debería cubrirse. Despois de todo, un conxunto de celas é un concepto variable. Para desfacerse deste problema, é necesario facer automática a xeración do rango para que se basee unicamente na cantidade de datos que introduciu o usuario.

Cambiar automaticamente os intervalos de celas en Excel

A vantaxe dos intervalos automáticos en Excel é que facilitan moito o uso das fórmulas. Ademais, permiten simplificar significativamente a análise de datos complexos que conteñen un gran número de fórmulas, que inclúen moitas funcións. Podes darlle un nome a este intervalo e, a continuación, actualizarase automaticamente dependendo dos datos que conteña.

Como facer un cambio automático de rango en Excel

Supoña que es un investidor que precisa investir nalgún obxecto. Como resultado, queremos obter información sobre canto podes gañar en total durante todo o tempo que o diñeiro funcionará para este proxecto. Non obstante, para obter esta información, necesitamos controlar regularmente o beneficio total que nos aporta este obxecto. Fai o mesmo informe que nesta captura de pantalla.

A primeira vista, a solución é obvia: só tes que sumar toda a columna. Se aparecen entradas nel, o importe actualizarase de forma independente. Pero este método ten moitas desvantaxes:

1. Se o problema se resolve deste xeito, non será posible utilizar as celas incluídas na columna B para outros fins.
2. Tal táboa consumirá moita memoria RAM, o que imposibilitará o uso do documento en ordenadores débiles.

Polo tanto, é necesario resolver este problema mediante nomes dinámicos. Para crealos, debes realizar a seguinte secuencia de accións:

1. Vaia á pestana "Fórmulas", que se atopa no menú principal. Haberá unha sección "Nomes definidos", onde hai un botón "Asignar un nome", no que debemos facer clic.
2. A continuación, aparecerá un cadro de diálogo no que cómpre cubrir os campos como se mostra na captura de pantalla. É importante ter en conta que necesitamos aplicar a función = DESPRAZAMENTO xunto coa función vERIFICACIÓNpara crear un intervalo de actualización automática. 
3. Despois diso, necesitamos usar a función SUM, que usa o noso rango dinámico como argumento. 

Despois de completar estes pasos, podemos ver como se actualiza a cobertura das celas pertencentes ao rango "ingresos" a medida que engadimos novos elementos alí.

Función OFFSET en Excel

Vexamos as funcións que rexistramos anteriormente no campo "rango". Usando a función ELIMINACIÓN podemos determinar a cantidade de intervalo dado cantas celas están cubertas na columna B. Os argumentos da función son os seguintes:

1. Iniciar cela. Con este argumento, o usuario pode indicar que cela do intervalo se considerará na parte superior esquerda. Informará abaixo e á dereita.
2. Rango compensado por filas. Usando este intervalo, establecemos o número de celas polo que se debe producir unha compensación desde a cela superior esquerda do intervalo. Podes usar non só valores positivos, senón cero e menos. Neste caso, o desprazamento pode non producirse en absoluto, ou realizarase en sentido contrario.
3. Intervalo compensado por columnas. Este parámetro é semellante ao anterior, só que permite establecer o grao de desprazamento horizontal do rango. Aquí tamén pode usar valores cero e negativos.
4. A cantidade de rango en altura. De feito, o título deste argumento déixanos claro o que significa. Este é o número de celas polo que debería aumentar o intervalo.
5. O valor do intervalo en ancho. O argumento é semellante ao anterior, só se refire ás columnas.

Non precisa especificar os dous últimos argumentos se non é necesario. Neste caso, o valor do intervalo será só unha cela. Por exemplo, se especifica a fórmula =OFFSET(A1;0;0), esta fórmula referirase á mesma cela que a do primeiro argumento. Se a compensación vertical está definida en 2 unidades, neste caso a cela referirase á cela A3. Agora imos describir en detalle o que significa a función vERIFICACIÓN.

Función COUNT en Excel

Usando a función vERIFICACIÓN determinamos cantas celas enchemos en total da columna B. É dicir, mediante dúas funcións, determinamos cantas celas se enchen do intervalo e, en función da información recibida, determinamos o tamaño do intervalo. Polo tanto, a fórmula final será a seguinte: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Vexamos como entender correctamente o principio desta fórmula. O primeiro argumento apunta onde comeza o noso rango dinámico. No noso caso, esta é a cela B2. Outros parámetros teñen coordenadas cero. Isto suxire que non necesitamos compensacións relativas á cela superior esquerda. Todo o que estamos enchendo é o tamaño vertical do intervalo, como usamos a función vERIFICACIÓN, que determina o número de celas que conteñen algúns datos. O cuarto parámetro que enchemos é a unidade. Así, mostramos que o ancho total do intervalo debe ser dunha columna.

Así, empregando a función vERIFICACIÓN o usuario pode utilizar a memoria da forma máis eficiente posible cargando só aquelas celas que conteñan algúns valores. En consecuencia, non haberá erros adicionais no traballo asociados ao mal rendemento do ordenador no que funcionará a folla de cálculo.

En consecuencia, para determinar o tamaño do intervalo dependendo do número de columnas, cómpre realizar unha secuencia similar de accións, só neste caso precisa especificar a unidade no terceiro parámetro e a fórmula no cuarto. vERIFICACIÓN.

Vemos que coa axuda de fórmulas de Excel, non só pode automatizar cálculos matemáticos. Esta é só unha pinga no océano, pero de feito permítenche automatizar case calquera operación que se che ocorra.

Gráficos dinámicos en Excel

Entón, no último paso, puidemos crear un rango dinámico, cuxo tamaño depende completamente de cantas celas cheas contén. Agora, en base a estes datos, pode crear gráficos dinámicos que cambiarán automaticamente en canto o usuario faga algún cambio ou engada unha columna ou fila adicional. A secuencia de accións neste caso é a seguinte:

1. Seleccionamos o noso rango, despois inserimos un gráfico do tipo "Histograma con agrupación". Podes atopar este elemento na sección "Inserir" na sección "Gráficos - Histograma".
2. Facemos un clic esquerdo do rato nunha columna aleatoria do histograma, despois de que a función =SERIES() aparecerá na liña de funcións. Na captura de pantalla podes ver a fórmula detallada. 
3. Despois diso, hai que facer algúns cambios na fórmula. Debes substituír o intervalo despois de "Folla1!" ao nome do intervalo. Isto dará lugar á seguinte función: =FILA(Folla1!$B$1;;Folla1!ingresos;1)
4. Agora queda engadir un novo rexistro ao informe para comprobar se o gráfico se actualiza automaticamente ou non.

Vexamos o noso diagrama.

Imos resumir como o fixemos. No paso anterior, creamos un rango dinámico, cuxo tamaño depende de cantos elementos conteña. Para iso, utilizamos unha combinación de funcións vERIFICACIÓN и ELIMINACIÓN. Fixemos o nome deste intervalo e, a continuación, utilizamos a referencia a este nome como intervalo do noso histograma. Que intervalo específico escoller como fonte de datos na primeira etapa non é tan importante. O principal é substituílo polo nome do rango máis tarde. Deste xeito podes aforrar moita memoria RAM.

Rangos nomeados e os seus usos

Falemos agora con máis detalle sobre como crear correctamente intervalos con nome e utilizalos para realizar as tarefas que están configuradas para o usuario de Excel.

De forma predeterminada, usamos enderezos de celas habituais para aforrar tempo. Isto é útil cando precisa escribir un intervalo unha ou máis veces. Se debe usarse todo o tempo ou debe ser adaptativo, entón deberían usarse intervalos con nome. Facilitan moito a creación de fórmulas e non será tan difícil para o usuario analizar fórmulas complexas que inclúen un gran número de funcións. Imos describir algúns dos pasos implicados na creación de rangos dinámicos.

Todo comeza co nome da cela. Para iso, só tes que seleccionalo e, a continuación, escribir o nome que necesitamos no campo do seu nome. É importante que sexa fácil de lembrar. Hai algunhas restricións a considerar ao nomear:

1. A lonxitude máxima é de 255 caracteres. Isto é suficiente para asignar un nome que o teu corazón desexa.
2. O nome non debe conter espazos. Polo tanto, se contén varias palabras, é posible separalas usando o carácter de subliñado.

Se máis adiante noutras follas deste ficheiro necesitamos mostrar este valor ou aplicalo para realizar máis cálculos, entón non hai que cambiar á primeira folla. Podes simplemente escribir o nome desta cela do intervalo.

O seguinte paso é crear un intervalo con nome. O procedemento é basicamente o mesmo. Primeiro cómpre seleccionar un intervalo e, a continuación, especificar o seu nome. Despois diso, este nome pódese usar en todas as outras operacións de datos en Excel. Por exemplo, os intervalos con nome úsanse a miúdo para definir a suma de valores.

Ademais, é posible crear un intervalo con nome mediante a pestana Fórmulas mediante a ferramenta Establecer nome. Despois de seleccionalo, aparecerá unha xanela onde debemos escoller un nome para o noso rango, así como especificar a zona á que se estenderá manualmente. Tamén podes especificar onde funcionará este rango: dentro dunha soa folla ou ao longo do libro.

Se xa se creou un intervalo de nomes, para usalo, hai un servizo especial chamado xestor de nomes. Permite non só editar ou engadir novos nomes, senón tamén borralos se xa non son necesarios.

Hai que ter en conta que cando se usan intervalos con nome nas fórmulas, despois de eliminalos, as fórmulas non se sobrescribirán automaticamente cos valores correctos. Polo tanto, poden producirse erros. Polo tanto, antes de eliminar un intervalo con nome, cómpre asegurarse de que non se utiliza en ningunha das fórmulas.

Outra forma de crear un intervalo con nome é obtelo dunha táboa. Para iso, hai unha ferramenta especial chamada "Crear a partir da selección". Segundo entendemos, para utilizalo, primeiro debes seleccionar o intervalo que editaremos, e despois establecer o lugar no que temos os títulos. Como resultado, en base a estes datos, Excel procesará automaticamente todos os datos e asignaranse títulos automaticamente.

Se o título contén varias palabras, Excel separaraas automaticamente cun guión baixo.

Así, descubrimos como crear intervalos con nome dinámicos e como permiten automatizar o traballo con grandes cantidades de datos. Como podes ver, é suficiente usar varias funcións e as ferramentas do programa integradas na funcionalidade. Non hai nada complicado, aínda que a primeira vista poida parecelo a un principiante.