Excel SUM și INDIRECT Formula dinamică

Microsoft Excel are câteva trucuri interesante și folosind formulele dinamice SUM și INDIRECT sunt doar două moduri de a manipula cu ușurință datele pe care le aveți.

SUM - Prezentare generală a formulei INDIRECTE

Folosind funcția INDIRECT în formulele Excel este ușor să modificați intervalul referințelor de celule utilizate în formula fără a trebui să editați formula însăși.

INDIRECT poate fi utilizat cu un număr de funcții care acceptă o referință de celule ca argument, cum ar fi funcțiile OFFSET și SUM.

În acest din urmă caz, folosirea INDIRECT ca argument pentru funcția SUM poate crea o gamă dinamică de referințe de celule pe care apoi se adaugă funcția SUM.

INDIRECT acest lucru se referă la datele din celule indirect prin intermediul unei locații intermediare.

Exemplu: SUM - FORMĂ INDIRECTĂ utilizată pentru a genera o gamă dinamică de valori

Acest exemplu se bazează pe datele afișate în imaginea de mai sus.

Formula SUM - INDIRECT creată prin utilizarea pașilor tutorial de mai jos este:

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

În această formulă, argumentul funcției INDIRECT imbricate conține referințe la celulele E1 și E2. Numerele din aceste celule, 1 și 4, când sunt combinate cu restul argumentului INDIRECT, formează referințele celulei D1 și D4.

Ca rezultat, intervalul de numere alocate de funcția SUM este datele cuprinse în intervalul de celule D1 - D4 - care este de 50.

Prin schimbarea numerelor situate în celulele E1 și E2; totuși, intervalul care trebuie completat poate fi schimbat cu ușurință.

Acest exemplu va folosi mai întâi formula de mai sus pentru a totaliza datele din celulele D1: D4 și apoi modificați intervalul sumat la D3: D6 fără a modifica formula din celula F1.

01 din 03

Introducerea formulei - Opțiuni

Opțiunile pentru introducerea formulei includ:

• tastând formula de mai sus direct în celula F1 și apăsând tasta Enter de pe tastatură
• introducerea funcției INDIRECT ca argument folosind caseta de dialog a funcției SUM

Cele mai multe funcții din Excel au o casetă de dialog, care vă permite să introduceți fiecare dintre argumentele funcției pe o linie separată fără a fi nevoie să vă faceți griji în legătură cu sintaxa .

În acest caz, caseta de dialog a funcției SUM poate fi utilizată pentru a simplifica formula într-o anumită măsură. Deoarece funcția INDIRECT este imbricată în SUM, funcția INDIRECT și argumentele acesteia trebuie să fie încă introduse manual.

Pașii de mai jos utilizează caseta de dialog SUM pentru a introduce formula.

Introducerea datelor Tutorial

Datele de celule D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4

1. Introduceți următoarele date în celulele D1 până la E2

Pornirea formulării SUM - INDIRECT - Deschiderea casetei de dialog Funcție SUM

1. Faceți clic pe celula F1 - aici se vor afișa rezultatele acestui exemplu
2. Faceți clic pe fila Formule din meniul de panglică
3. Alegeți Math & Trig din panglică pentru a deschide lista verticală de funcții
4. Faceți clic pe SUM din listă pentru a deschide caseta de dialog a funcției

02 din 03

Introducerea funcției INDIRECT - Faceți clic pentru a vizualiza o imagine mai mare

Formula INDIRECT trebuie introdusă ca argument pentru funcția SUM.

În cazul funcțiilor imbricate, Excel nu permite deschiderea casetei de dialog a celei de-a doua funcții pentru a introduce argumentele sale.

Prin urmare, funcția INDIRECT trebuie introdusă manual în linia Number1 a casetei de dialog a funcției SUM.

1. În caseta de dialog, faceți clic pe linia Număr1
2. Introduceți următoarea funcție INDIRECT: INDIRECT ("D" & E1 & ": D" & E2)
3. Faceți clic pe OK pentru a finaliza funcția și a închide caseta de dialog
4. Numărul 50 ar trebui să apară în celula F1, deoarece acesta este totalul datelor din celulele D1-D4
5. Când faceți clic pe celula F1, formularul complet = SUM (INDIRECT ("D" și E1 & ": D" & E2)) apare în bara de formula de deasupra foii de lucru

Înlăturarea funcției INDIRECT

Pentru a crea o gamă dinamică în coloana D folosind INDIRECT, trebuie să combină litera D în argumentul funcției INDIRECT cu numerele conținute în celulele E1 și E2.

Acest lucru se realizează prin următoarele:

• ampersa ( & ) este folosită pentru a concatena sau a uni datele de text (în acest caz, litera D) cu o referință de celule (E1 și E2)
• în plus, datele de text care sunt concatenate cu referințe de celule trebuie să fie înconjurate de ghilimele duble ( "" )
• în cele din urmă, punctele finale ale intervalului sunt separate de colon (:)

Prin urmare, punctul de start al intervalului este definit de caracterele: "D" și E1 .

Cel de-al doilea set de caractere: ": D" & E2 combină colonul cu punctul final. Acest lucru se face deoarece colonul este un caracter de text și, prin urmare, trebuie inclus în ghilimele.

Al treilea ampersand din mijloc este folosit pentru a concatena cele două părți într-un singur argument :

"D" & E1 & ": D" & E2

03 din 03

Modificarea dinamică a intervalului funcției SUM

Întregul punct al acestei formule este de a facilita modificarea domeniului de activitate al funcției SUM fără a fi necesară editarea argumentului funcției.

Prin includerea funcției INDIRECT în formula, modificarea numerelor din celulele E1 și E2 va schimba gama de celule citite de funcția SUM.

Așa cum se poate observa în imaginea de mai sus, acest lucru are ca rezultat și răspunsul formulării localizat în celula F1, schimbându-se, deoarece totalizează noul domeniu de date.

1. Faceți clic pe celula E1
2. Introduceți numărul 3
3. Apăsați tasta Enter de pe tastatură
4. Faceți clic pe celula E2
5. Introduceți numărul 6
6. Apăsați tasta Enter de pe tastatură
7. Răspunsul din celula F1 ar trebui să se modifice la 90 - care este totalul numărului conținut în celulele D3-D6
8. Testați în continuare formula prin modificarea conținutului celulelor B1 și B2 la orice număr între 1 și 6

INDIRECT și #REF! Valoare eroare

#REF! valoarea de eroare va apărea în celula F1 dacă argumentul funcției INDIRECT:

• nu este o referință de celule valabilă
• conține o referință externă la un alt registru de lucru și acest registru de lucru nu este deschis
• se referă la o gamă de celule din afara limitelor din foaia de lucru (rândul 1.048.576 sau coloana XFD)