Microsoft Excel ka disa truke cool dhe duke përdorur formulimet SUM dhe INDIRECT dinamike janë vetëm dy mënyra për të manipuluar lehtë të dhënat që keni.
SUM - INDIRECT Përmbledhja e Formula
Duke përdorur funksionin INDIRECT në Excel formulat e bën të lehtë për të ndryshuar gamën e referencave qelizore të përdorura në formulë pa pasur nevojë të redaktojnë vetë formulën.
INDIRECT mund të përdoret me një numër funksionesh që pranojnë një referencë qelizore si një argument të tillë si funksioni OFFSET dhe SUM.
Në rastin e fundit, përdorimi i INDIRECT si argument për funksionin SUM mund të krijojë një sferë dinamike të referencave të qelizave që funksioni SUM shton më pas.
INDIRECT bën këtë duke iu referuar të dhënave në qeliza indirekt përmes një vendndodhjeje të ndërmjetme.
Shembull: SUM - formula indirekte e përdorur për të Gjithsej një Gama Dinamike të Vlerave
Ky shembull bazohet në të dhënat e paraqitura në imazhin e mësipërm.
Formula SUM-INDIRECT e krijuar duke përdorur hapat tutorial më poshtë është:
= SUM (INDIRET ("D" & E1 & ": D" & E2))
Në këtë formulë, argumenti i funksionit të mbivendosur INDIRECT përmban referenca ndaj qelizave E1 dhe E2. Numrat në ato qeliza, 1 dhe 4, kur kombinohen me pjesën tjetër të argumentit INDIRECT, formojnë referencat e qelizave D1 dhe D4.
Si rezultat, varg i numrave të mbledhura nga funksioni SUM janë të dhënat që gjenden në rangun e qelizave D1 deri në D4 - që është 50.
Duke ndryshuar numrat e vendosur në qelizat E1 dhe E2; megjithatë, vargu që do të arrihet mund të ndryshohet lehtësisht.
Ky shembull do të përdorë së pari formulën e mësipërme për të totalizuar të dhënat në qelizat D1: D4 dhe pastaj të ndryshojë vargun e përmbledhur në D3: D6 pa editing formulën në qelizën F1.
01 nga 03
Hyrja e Formula - Opsionet
Opsionet për të hyrë në formulë përfshijnë:
- duke shtypur formulën e mësipërme direkt në qelizën F1 dhe duke shtypur butonin Enter në tastierë
- duke hyrë në funksionin INDIRECT si një argument duke përdorur kutinë e dialogut të funksionit SUM
Shumica e funksioneve në Excel kanë një kuti dialogu, e cila ju lejon të futni secilën prej argumenteve të funksionit në një vijë të veçantë pa u shqetësuar rreth sintaksës .
Në këtë rast, kutia e dialogut të funksionit SUM mund të përdoret për të thjeshtuar formulën në një masë të caktuar. Për shkak se funksioni INDIRECT është i mbivendosur brenda SUM, Funksioni INDIRECT dhe argumentet e tij ende duhet të futen manualisht.
Hapat e mëposhtëm përdorin kutinë e dialogut SUM për të hyrë në formulën.
Hyrja e të Dhënave të Tutorial
Të dhënat e qelizës D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Shkruani të dhënat e mëposhtme në qelizat D1 deri në E2
Fillimi i formulës SUM - INDIRECT - Hapja e kutisë së dialogut të funksionit SUM
- Klikoni në qelizën F1 - këtu do të shfaqen rezultatet e këtij shembulli
- Klikoni në tab Formulat e menysë së fjongo
- Zgjidhni Math & Trig nga shiriti për të hapur drop down listën e funksionit
- Klikoni në SUM në listën për të hapur kutinë e dialogut të funksionit
02 nga 03
Hyrja e funksionit INDIRECT - Kliko për të parë imazhin më të madh
Formati INDIRECT duhet të futet si argument për funksionin SUM.
Në rastin e funksioneve të mbivendosur, Excel nuk lejon hapjen e kutisë së dialogut të funksionit të dytë për të futur argumentet e tij.
Funksioni INDIRECT, pra, duhet të futet manualisht në rreshtin Number1 të kutisë së dialogut të SUM Function.
- Në kutinë e dialogut, klikoni në vijën Numër1
- Shkruani funksionin e mëposhtëm INDIRECT: INDIRECT ("D" & E1 & ": D" & E2)
- Kliko OK për të përfunduar funksionin dhe mbyllni kutinë e dialogut
- Numri 50 duhet të shfaqet në qelizën F1 pasi që kjo është totali për të dhënat e vendosura në qelizat D1 deri në D4
- Kur klikoni në qelizën F1, formulari i plotë = SUM (INDIRECT ("D" & E1 & ": D" & E2)) shfaqet në shiritin e formulës mbi fletën e punës
Thyerja e funksionit INDIRECT
Për të krijuar një varg dinamik në kolonën D duke përdorur INDIRECT, ne duhet të kombinojmë shkronjën D në argumentin e funksionit INDIRECT me numrat që gjenden në qelizat E1 dhe E2.
Kjo arrihet me sa vijon:
- ampersand ( & ) përdoret për të bashkërenduar ose bashkuar të dhënat e tekstit (në këtë rast, shkronja D) me një referencë qelizore (E1 dhe E2)
- Më tej, të dhënat e tekstit që janë duke u lidhur me referencat e qelizave duhet të rrethohen me thonjëza të dyfishta ( "" )
- Së fundi, pikat përfundimtare të intervalit janë të ndara nga zorrës së trashë ( :)
Prandaj, pika fillestare e diapazonit përcaktohet nga shkronjat: "D" & E1 .
Grupi i dytë i karaktereve: ": D" & E2 kombinon dy pika me pikën përfundimtare. Kjo është bërë për shkak se zorrës së trashë është një karakter tekst dhe, për këtë arsye, duhet të përfshihet brenda thonjëza.
E treta dhe në mes është përdorur për të bashkërenduar të dy pjesët në një argument :
"D" & E1 & ": D" & E203 nga 03
Dinamikisht Ndryshimi i Range Funksioni SUM
Pika e tërë e kësaj formule është për ta bërë të lehtë për të ndryshuar gamën e arritur nga funksioni SUM pa pasur nevojë të redaktoni argumentin e funksionit.
Duke përfshirë funksionin INDIRECT në formulë, ndryshimi i numrave në qelizat E1 dhe E2 do të ndryshojë gamën e qelizave të lexuara nga funksioni SUM.
Siç mund të shihet në imazhin e mësipërm, kjo gjithashtu rezulton në përgjigjen e formulës që ndodhet në qelizën F1 duke ndryshuar pasi ajo arrin vargun e ri të të dhënave.
- Klikoni në qelizën E1
- Shkruani numrin 3
- Shtypni butonin Enter në tastierë
- Klikoni në qelizën E2
- Shkruani numrin 6
- Shtypni butonin Enter në tastierë
- Përgjigja në qelizën F1 duhet të ndryshojë në 90 - që është totali i numrave që gjenden në qelizat D3 deri në D6
- Testi më tej formulën duke ndryshuar përmbajtjen e qelizave B1 dhe B2 në çdo numër ndërmjet 1 dhe 6
INDIRECT dhe #REF! Vlera e gabimit
#REF! vlera e gabimit do të shfaqet në qelizën F1 nëse argumenti i funksionit INDIRECT:
- nuk është një referencë e vlefshme qelizore
- përmban një referencë të jashtme në një libër pune të ndryshëm dhe se libri i punës nuk është i hapur
- i referohet një varg qelizash jashtë kufijve të fletës së punës (rreshti 1,048,576 ose kolona XFD)