Deci, am aflat deja ce este referința 3D în Excel. Faptul distractiv este că referințarea Excel 3D normală nu funcționează cu funcții condiționale, cum ar fi funcția SUMIF. În acest articol, vom învăța cum să obținem referințe 3D funcționând cu funcția SUMIF.
Formula generică pentru SUMIF cu referință 3D în Excel
Pare complicat, dar nu este (atât de mult).
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Criteria_range"), criterii, INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Sum_range"))) |
"'" name_range_of_sheet_names "'":Este un interval numit care conține numele foilor. Este foarte important.
„gama_criteriilor”:Este referința textului criteriilor care conțin interval. (Ar trebui să fie același în toate foile la lucrările de referință 3D).
criterii:Este pur și simplu condiția pe care doriți să o puneți pentru însumare. Poate fi un text sau o referință de celulă.
„sum_range”:Este referința textului intervalului sumă. (Ar trebui să fie același în toate foile la lucrările de referință 3D).
Destul de teorie, să funcționăm referințarea 3D cu funcția SUMIF.
Exemplu: Suma pe regiuni din mai multe foi folosind referințarea 3D a Excel:
Luăm aceleași date pe care le-am luat în exemplul simplu de referință 3D. În acest exemplu, am cinci foi diferite care conțin date similare. Fiecare foaie conține date de o lună. În foaia Master, vreau suma unităților și colecția pe regiuni din toate foile. Să o facem mai întâi pentru Unități. Unitățile sunt în intervalul D2: D14 în toate foile.
Acum, dacă utilizați referințarea 3D normală cu funcția SUMIF,
= SUMIF (ianuarie: aprilie! A2: A14, Maestră! B4, ianuarie: aprilie! D2: D14)
Va reveni #VALUE! eroare. Deci nu o putem folosi. Vom folosi formula generică menționată mai sus.
Folosind formula SUMIF generică 3D de referință de mai sus, scrieți această formulă în celula C3:
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & Luni & "'!" & "A2: A14"), Master! B3, INDIRECT ("'" & Luni & "'!" & "D2: D14"))) |
Aici luni este un interval numit care conține numele foilor. Acest lucru este crucial.
Când apăsați Enter, obțineți rezultatul exact.
Cum functioneazã?
Nucleul formulei este funcția INDIRECTĂ și gama denumită. Aici șirul"'"&Luni&"'!" & "A2: A14"se traduce printr-o serie de referințe de gamă pentru fiecare foaie din gama denumită.
| {"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"} |
Această matrice conține referință textde intervale, nu de intervale reale. Acum, deoarece este o referință text, poate fi folosit de funcția INDIRECT pentru a le converti în intervale reale. Acest lucru se întâmplă pentru ambele funcții INDIRECTE. După rezolvarea textelor din funcțiile INDIRECTE (țineți strâns), formula arată astfel:
| = SUMPRODUCT (SUMIF (INDIRECT (({"'Jan'! A2: A14"; "'Feb'! A2: A14"; "'Mar'! A2: A14"; "'Apr'! A2: A14"}) , Maestru! B3, INDIRECT ({"'Jan'! D2: D14"; "'Feb'! D2: D14"; "'Mar'! D2: D14"; "'Apr'! D2: D14"})))) |
Acum, funcția SUMIF intră în acțiune (nu INDIRECT, așa cum ați fi ghicit). Condiția este potrivită în prima gamă„„ Jan ”! A2: A14”. Aici funcția INDIRECT funcționează dinamic și convertește acest text în intervalul real (de aceea, dacă încercați mai întâi să rezolvați INDIRECT folosind cheia F9, nu veți obține rezultatul). Apoi rezumă valorile potrivite din interval„„ Jan ”! D2: D14”.Acest lucru se întâmplă pentru fiecare interval din matrice. În cele din urmă, vom avea o matrice returnată de funcția SUMIF.
| = SUMPRODUCT ({97; 82; 63; 73}) |
Acum SUMPRODUCT face ceea ce face cel mai bine. Rezumă aceste valori și funcția noastră SUMIF 3D funcționează.
Deci, băieți, așa puteți realiza o funcție 3D SUMIF. Este un pic complex, sunt de acord cu asta. Există o mulțime de posibilități pentru erori în această formulă 3D. Aș sugera să utilizați funcția SUMIF pe fiecare foaie într-o celulă definită și apoi să utilizați referințarea 3D normală pentru a însuma aceste valori.
Sper că am fost suficient de explicativ. Dacă aveți îndoieli cu privire la referențierea Excel la orice altă interogare legată de Excel / VBA, întrebați în secțiunea de comentarii de mai jos.
Referință relativă și absolută în Excel | Referința în Excel este un subiect important pentru fiecare începător. Chiar și utilizatorii Excel cu experiență fac greșeli în referință.
Referință dinamică a foii de lucru | Oferiți foi de referință dinamic folosind funcția INDIRECTĂ a excel. Acest lucru este simplu …
Extinderea referințelor în Excel | Referința în expansiune se extinde atunci când este copiată în jos sau în dreapta. Pentru a face acest lucru, folosim semnul $ înainte de numărul coloanei și al rândului. Iată un exemplu …
Totul despre referințe absolute | Tipul de referință implicit în Excel este relativ, dar dacă doriți ca referința celulelor și a intervalelor să fie absolută utilizați semnul $. Iată toate aspectele referințării absolute în Excel.
Articole populare:
50 de comenzi rapide Excel pentru a vă crește productivitatea | Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Funcția VLOOKUP în Excel | Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
COUNTIF în Excel 2016 | Numărați valorile cu condiții folosind această funcție uimitoare. Nu este nevoie să vă filtrați datele pentru a număra o anumită valoare. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel | Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.