Excel SUM e INDIRECT Fórmula Dynamic Range

Microsoft Excel ten algúns trucos interesantes e usando as fórmulas de intervalo dinámico SUM e INDIRECT son só dúas formas de manipular facilmente os datos que ten.

SUM - INDIRECT Descrición xeral da fórmula

Usar a función INDIRECT nas fórmulas de Excel fai que sexa fácil cambiar o alcance de referencias celulares empregadas na fórmula sen ter que editar a fórmula.

INDIRECT pode usarse con varias funcións que aceptan unha referencia de cela como un argumento como as funcións OFFSET e SUM.

Neste último caso, usar INDIRECT como argumento para a función SUM pode crear un intervalo dinámico de referencias de celas que a función SUMA engade.

INDIRECT fai isto ao referirse aos datos das celas indirectamente a través dunha localización intermedia.

Exemplo: Fórmula SUM - INDIRECTA usada para un rango dinámico de valores

Este exemplo baséase nos datos que se mostran na imaxe anterior.

A fórmula SUM - INDIRECT creada usando os pasos de tutorial seguintes é:

= SUM (INDIRECTO ("D" & E1 & ": D" & E2))

Nesta fórmula, o argumento da función INDIRECT anidada contén referencias ás celas E1 e E2. Os números destas celas, 1 e 4, cando se combinan co resto do argumento INDIRECT, forman as referencias celulares D1 e D4.

Como resultado, o rango de números totalizado pola función SUM son os datos contidos no rango de celas D1 a D4, que é de 50.

Ao cambiar os números localizados nas celas E1 e E2; Con todo, o rango a ser total pode ser facilmente modificado.

Este exemplo utilizará por primeira vez a fórmula anterior para completar os datos nas celas D1: D4 e logo cambiar o rango sumado a D3: D6 sen editar a fórmula na cela F1.

01 de 03

Introdución á fórmula - Opcións

Crea un intervalo dinámico en fórmulas de Excel. © Ted Francés

As opcións para introducir a fórmula inclúen:

A maioría das funcións en Excel teñen un cadro de diálogo que permite introducir cada un dos argumentos da función nunha liña separada sen ter que preocuparse pola sintaxe .

Neste caso, a caixa de diálogo da función SUM pode usarse para simplificar a fórmula en certa medida. Porque a función INDIRECT está aninhada dentro do SUMA, a función INDIRECT e os seus argumentos aínda deben ingresarse manualmente.

Os pasos a seguir usan a caixa de diálogo SUM para ingresar a fórmula.

Introducir os datos do titorial

Datos da célula D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4
  1. Introduza os seguintes datos nas celas D1 a E2

Comezando a Fórmula SUM - INDIRECT - Apertura do cuadro de diálogo SUM Función

  1. Faga clic na cela F1 - aquí móstranse os resultados deste exemplo
  2. Fai clic na pestana Fórmulas do menú da cinta
  3. Escolla Math & Trig da cinta para abrir a lista despregábel da función
  4. Faga clic en SUM na lista para abrir o diálogo da función

02 de 03

Introdución á función INDIRECT - Faga clic para ver a imaxe máis grande

Faga clic para ver a imaxe máis grande. © Ted Francés

A fórmula INDIRECT debe ser ingresada como argumento para a función SUM.

No caso das funcións aniñadas, Excel non permite abrir a caixa de diálogo da segunda función para introducir os seus argumentos.

A función INDIRECT, polo tanto, debe ingresarse manualmente na liña Número 1 do cadro de diálogo da función SUMA.

  1. No cadro de diálogo, prema na liña Número 1
  2. Introduza a seguinte función INDIRECTO: INDIRECTO ("D" & E1 & ": D" & E2)
  3. Faga clic en Aceptar para completar a función e pechar o cadro de diálogo
  4. O número 50 debería aparecer na cela F1 xa que este é o total dos datos localizados nas celas D1 a D4
  5. Cando fai clic na cela F1, a fórmula completa = SUM (INDIRECT ("D" & E1 & ": D" & E2)) aparece na barra de fórmulas situada enriba da folla de cálculo

Rompendo a función indirecta

Para crear un rango dinámico na columna D usando INDIRECTO, debemos combinar a letra D no argumento da función INDIRECT cos números contidos nas celas E1 e E2.

Isto é realizado polo seguinte:

Polo tanto, o punto de inicio do intervalo defínese polos personaxes: "D" e E1 .

O segundo conxunto de caracteres: ": D" e E2 combina o punto de coma co punto final. Isto faise porque o colono é un carácter de texto e, polo tanto, debe incluírse dentro das comiñas.

O terceiro e no medio utilízase para concatenar as dúas partes nun só argumento :

"D" e E1 e ": D" e E2

03 de 03

Cambio dinámico do intervalo da función SUM

Cambio dinámico do intervalo de fórmulas. © Ted Francés

O punto completo desta fórmula é facer que sexa fácil cambiar o rango totalizado pola función SUM sen ter que editar o argumento da función.

Ao incluír a función INDIRECT na fórmula, cambiar os números nas celas E1 e E2 cambiará o alcance de celas lidas pola función SUM.

Como se pode ver na imaxe anterior, isto tamén resulta na resposta da fórmula localizada na célula F1 cambiando xa que equivale ao novo intervalo de datos.

  1. Fai clic na cela E1
  2. Escriba o número 3
  3. Prema a tecla Intro no teclado
  4. Fai clic na cela E2
  5. Escriba o número 6
  6. Prema a tecla Intro no teclado
  7. A resposta na cela F1 debería cambiar a 90 - que é o total dos números contidos nas celas D3 a D6
  8. Proba ademais a fórmula cambiando o contido das celas B1 e B2 a calquera número entre 1 e 6

INDIRECTO e #REF! Valor de erro

O #REF! O valor de erro aparecerá na cela F1 se o argumento da función INDIRECT: