Përmbajtje
urime! Ju arritët në ditën e fundit të maratonës 30 funksione Excel në 30 ditë. Ka qenë një udhëtim i gjatë dhe interesant gjatë të cilit keni mësuar shumë gjëra të dobishme rreth funksioneve të Excel.
Në ditën e 30-të të maratonës, ne do t'i kushtojmë studimit të funksionit INDIREKT (INDIRECT), i cili kthen lidhjen e specifikuar nga vargu i tekstit. Me këtë funksion, ju mund të krijoni lista të varura drop-down. Për shembull, kur zgjedh një shtet nga një listë rënëse, përcakton se cilat opsione do të shfaqen në listën rënëse të qytetit.
Pra, le të hedhim një vështrim më të afërt në pjesën teorike të funksionit INDIREKT (INDIREKT) dhe eksploroni shembuj praktikë të zbatimit të tij. Nëse keni informacione ose shembuj shtesë, ju lutemi ndani ato në komente.
Funksioni 30: INDIREKT
funksion INDIREKT (INDIRECT) kthen lidhjen e specifikuar nga vargu i tekstit.
Si mund ta përdorni funksionin INDIRECT?
Që nga funksioni INDIREKT (INDIRECT) kthen një lidhje të dhënë nga një varg teksti, mund ta përdorni për të:
- Krijo një lidhje fillestare jo-ndryshuese.
- Krijo një referencë për një varg me emër statik.
- Krijoni një lidhje duke përdorur informacionin e fletës, rreshtit dhe kolonës.
- Krijoni një grup numrash që nuk lëvizin.
Sintaksa INDIRECT (INDIRECT)
funksion INDIREKT (INDIRECT) ka sintaksën e mëposhtme:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_tekst (link_to_cell) është teksti i lidhjes.
- a1 – nëse është e barabartë me TRUE (TRUE) ose nuk specifikohet, atëherë do të përdoret stili i lidhjes A1; dhe nëse FALSE (FALSE), atëherë stili R1C1.
Kurthe INDIRECT (INDIRECT)
- funksion INDIREKT (INDIRECT) rillogaritet sa herë që ndryshojnë vlerat në fletën e punës Excel. Kjo mund të ngadalësojë shumë librin tuaj të punës nëse funksioni përdoret në shumë formula.
- Nëse funksioni INDIREKT (INDIRECT) krijon një lidhje me një libër tjetër pune në Excel, ai libër pune duhet të jetë i hapur ose formula do të raportojë një gabim # REF! (#LINK!).
- Nëse funksioni INDIREKT (INDIRECT) i referohet një diapazoni që tejkalon kufirin e rreshtit dhe kolonës, formula do të raportojë një gabim # REF! (#LINK!).
- funksion INDIREKT (INDIRECT) nuk mund t'i referohet një diapazoni dinamik me emër.
Shembulli 1: Krijoni një lidhje fillestare jo-ndryshuese
Në shembullin e parë, kolonat C dhe E përmbajnë të njëjtët numra, shumat e tyre të llogaritura duke përdorur funksionin SUM (SUM) janë gjithashtu të njëjta. Megjithatë, formulat janë paksa të ndryshme. Në qelizën C8, formula është:
=SUM(C2:C7)
=СУММ(C2:C7)
Në qelizën E8, funksioni INDIREKT (INDIRECT) krijon një lidhje me qelizën fillestare E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Nëse futni një rresht në krye të fletës dhe shtoni vlerën për janar (janar), atëherë shuma në kolonën C nuk do të ndryshojë. Formula do të ndryshojë, duke reaguar ndaj shtimit të një rreshti:
=SUM(C3:C8)
=СУММ(C3:C8)
Megjithatë, funksioni INDIREKT (INDIRECT) rregullon E2 si qelizën fillestare, kështu që janari përfshihet automatikisht në llogaritjen e totaleve të kolonës E. Qeliza fundore ka ndryshuar, por qeliza fillestare nuk është prekur.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Shembulli 2: Lidhja me një varg me emër statik
funksion INDIREKT (INDIRECT) mund të krijojë një referencë për një varg të emërtuar. Në këtë shembull, qelizat blu përbëjnë gamën NumList. Për më tepër, një diapazon dinamik krijohet gjithashtu nga vlerat në kolonën B NumListDyn, në varësi të numrit të numrave në këtë kolonë.
Shuma për të dy vargjet mund të llogaritet thjesht duke i dhënë emrin e saj si argument funksionit SUM (SUM), siç mund ta shihni në qelizat E3 dhe E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Në vend që të shkruani një emër vargu në një funksion SUM (SUM), Ju mund t'i referoheni emrit të shkruar në një nga qelizat e fletës së punës. Për shembull, nëse emri NumList është shkruar në qelizën D7, atëherë formula në qelizën E7 do të jetë si kjo:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Fatkeqësisht funksioni INDIREKT (INDIRECT) nuk mund të krijojë një referencë të intervalit dinamik, kështu që kur e kopjoni këtë formulë në qelizën E8, do të merrni një gabim # REF! (#LINK!).
Shembulli 3: Krijoni një lidhje duke përdorur informacionin e fletës, rreshtit dhe kolonës
Mund të krijoni lehtësisht një lidhje bazuar në numrat e rreshtit dhe kolonës, si dhe duke përdorur vlerën FALSE (FALSE) për argumentin e dytë të funksionit INDIREKT (INDIREKT). Kështu krijohet lidhja e stilit R1C1. Në këtë shembull, ne shtuam gjithashtu emrin e fletës në lidhjen - 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Shembulli 4: Krijoni një grup numrash që nuk lëvizin
Ndonjëherë ju duhet të përdorni një grup numrash në formulat Excel. Në shembullin e mëposhtëm, ne duam të mesatarizojmë 3 numrat më të mëdhenj në kolonën B. Numrat mund të futen në një formulë, siç bëhet në qelizën D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Nëse keni nevojë për një grup më të madh, atëherë nuk ka gjasa të dëshironi të futni të gjithë numrat në formulë. Opsioni i dytë është përdorimi i funksionit ROW (ROW), siç bëhet në formulën e grupit të futur në qelizën D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Opsioni i tretë është përdorimi i funksionit ROW (STRING) së bashku me INDIREKT (INDIRECT), siç bëhet me formulën e grupit në qelizën D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Rezultati për të 3 formulat do të jetë i njëjtë:
Megjithatë, nëse rreshtat futen në krye të fletës, formula e dytë do të kthejë një rezultat të pasaktë për shkak të faktit se referencat në formulë do të ndryshojnë së bashku me zhvendosjen e rreshtit. Tani, në vend të mesatares së tre numrave më të mëdhenj, formula kthen mesataren e numrave të 3-të, të 4-të dhe të 5-të më të mëdhenj.
Përdorimi i funksioneve INDIREKT (INDIRECT), formula e tretë ruan referencat e sakta të rreshtit dhe vazhdon të tregojë rezultatin e saktë.