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
As opcións para introducir a fórmula inclúen:
- escribindo a fórmula anterior directamente na célula F1 e premendo a tecla Enter no teclado
- introducindo a función INDIRECT como argumento usando a caixa de diálogo da función SUM
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- Introduza os seguintes datos nas celas D1 a E2
Comezando a Fórmula SUM - INDIRECT - Apertura do cuadro de diálogo SUM Función
- Faga clic na cela F1 - aquí móstranse os resultados deste exemplo
- Fai clic na pestana Fórmulas do menú da cinta
- Escolla Math & Trig da cinta para abrir a lista despregábel da función
- 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
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.
- No cadro de diálogo, prema na liña Número 1
- Introduza a seguinte función INDIRECTO: INDIRECTO ("D" & E1 & ": D" & E2)
- Faga clic en Aceptar para completar a función e pechar o cadro de diálogo
- O número 50 debería aparecer na cela F1 xa que este é o total dos datos localizados nas celas D1 a D4
- 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:
- o ampersand ( & ) úsase para concatenar ou xuntar datos de texto (neste caso, a letra D) cunha referencia de cela (E1 e E2)
- Ademais, os datos de texto que están sendo concatenados con referencias celulares deben estar rodeados por comiñas dobres ( "" )
- por último, os puntos finais do intervalo están separados por dous puntos (:)
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 E203 de 03
Cambio dinámico do intervalo da función SUM
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.
- Fai clic na cela E1
- Escriba o número 3
- Prema a tecla Intro no teclado
- Fai clic na cela E2
- Escriba o número 6
- Prema a tecla Intro no teclado
- A resposta na cela F1 debería cambiar a 90 - que é o total dos números contidos nas celas D3 a D6
- 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:
- non é unha referencia de cela válida
- contén unha referencia externa a un libro diferente e ese libro non está aberto
- refírese a un intervalo de celas fóra dos límites da folla de traballo (fila 1.048.576 ou columna XFD)