Një nga karakteristikat më të dobishme të programit Excel është operatori INDEX. Kërkon të dhëna në një varg në kryqëzimin e rreshtit dhe kolonës së specifikuar, duke e kthyer rezultatin në një qelizë të caktuar më parë. Por mundësitë e plota të këtij funksioni zbulohen kur përdoren në formula komplekse në kombinim me operatorët e tjerë. Le të shohim mundësi të ndryshme për aplikimin e tij.
Duke përdorur funksionin INDEX
operator INDEX i përket një grupi funksionesh nga kategoria Referenca dhe vargje. Ka dy lloje: për vargje dhe për referenca.
Opsioni për vargje ka sintaksën e mëposhtme:
= INDEX (varg; rresht_numër; kolonë_numër)
Në të njëjtën kohë, dy argumentet e fundit në formulë mund të përdoren të dy së bashku dhe cilindo prej tyre, nëse vargu është një-dimensionale. Për një gamë shumëdimensionale, të dy vlerat duhet të përdoren. Duhet gjithashtu të merret në konsideratë që numri i rreshtit dhe kolonës nuk kuptohet të jetë numri në koordinatat e fletës, por rendi brenda vetë grupit të specifikuar.
Sintaksa për opsionin e referencës është si më poshtë:
= INDEX (lidhje; row_number; column_number; [area_number])
Këtu, në të njëjtën mënyrë, mund të përdorni vetëm një argument nga dy: Numri i linjës ose Numri i kolonës. argument "Numri i zonës" përgjithësisht është opsionale dhe zbatohet vetëm kur disa varg përfshihen në operacion.
Kështu, operatori kërkon të dhëna në rangun e specifikuar kur specifikon një rresht ose kolonë. Kjo veçori është shumë e ngjashme me Operatori VLR, por ndryshe nga ajo, ai mund të kërkojë pothuajse kudo, dhe jo vetëm në kolonën e majtë të tabelës.
Metoda 1: përdorni operatorin INDEX për vargje
Para së gjithash, le të analizojmë operatorin duke përdorur shembullin më të thjeshtë INDEX për vargje.
Kemi një tryezë pagash. Kolona e parë tregon emrat e të punësuarve, e dyta tregon datën e pagesës dhe e treta tregon sasinë e fitimeve. Ne duhet të shfaqim emrin e punonjësit në rreshtin e tretë.
- Zgjidhni qelizën në të cilën do të shfaqet rezultati i përpunimit. Klikoni në ikonën "Vendos funksionin", e cila ndodhet menjëherë në të majtë të shiritit të formulës.
- Procedura e aktivizimit në zhvillim e sipër Magjistarët e funksionit. Në kategori Referenca dhe vargje ky mjet ose "Lista e plotë e alfabetit" duke kërkuar një emër "INDEX". Pasi ta gjeni këtë operator, zgjidhni atë dhe klikoni në butonin "OK", e cila është e vendosur në fund të dritares.
- Hapet një dritare e vogël në të cilën ju duhet të zgjidhni një nga llojet e funksioneve: "Array" ose "Link". Ne kemi nevojë për një mundësi "Array". Ajo është e vendosur së pari dhe e theksuar si parazgjedhje. Prandaj, thjesht duhet të klikojmë në butonin "OK".
- Hapet dritarja e argumenteve të funksionit INDEX. Siç u përmend më lart, ajo ka tre argumente, dhe në përputhje me rrethanat, tre fusha për t'i plotësuar.
Në fushë "Array" Ju duhet të specifikoni adresën e diapazonit të të dhënave që po përpunohen. Mund të drejtohet me dorë. Por për të lehtësuar detyrën, do të bëjmë ndryshe. Vendoseni kursorin në fushën e përshtatshme, dhe më pas rrethoni tërë fletën e të dhënave tabelare në fletë. Pas kësaj, adresa e diapazonit do të shfaqet menjëherë në fushë.
Në fushë Numri i linjës vendos numrin "3", pasi me kusht ne duhet të përcaktojmë emrin e tretë në listë. Në fushë Numri i kolonës vendosni numrin "1", pasi kolona me emra është e para në rangun e zgjedhur.
Pasi të keni përfunduar të gjitha cilësimet e specifikuara, klikoni në butonin "OK".
- Rezultati i përpunimit shfaqet në qelizë që u tregua në paragrafin e parë të këtij udhëzimi. Përkatësisht, mbiemri i zbritur është i treti në listë në rangun e të dhënave të zgjedhura.
Ne shqyrtuam aplikimin e funksionit INDEX në një varg shumëdimensional (kolona të shumta dhe rreshta). Nëse diapazoni ishte një-dimensionale, plotësimi i të dhënave në dritaren e argumenteve do të ishte edhe më i lehtë. Në fushë "Array" me të njëjtën metodë si më lart, ne tregojmë adresën e saj. Në këtë rast, diapazoni i të dhënave përbëhet nga vetëm vlera në një kolonë. "Emri". Në fushë Numri i linjës tregoni vlerën "3", pasi ju duhet të zbuloni të dhënat nga rreshti i tretë. Fushë Numri i kolonës në përgjithësi, ju mund ta lini atë bosh, pasi kemi një gamë një-dimensionale në të cilën përdoret vetëm një kolonë. Klikoni në butonin "OK".
Rezultati do të jetë saktësisht i njëjtë si më lart.
Ky ishte shembulli më i thjeshtë për ju për të parë se si funksionon ky funksion, por në praktikë, një version i ngjashëm i përdorimit të tij përdoret ende rrallë.
mësim: Magjistar i Karakteristikave Excel
Metoda 2: përdorimi në bashkëpunim me operatorin Kërko
Në praktikë, funksioni INDEX më shpesh përdoret me argument MATCH. bandë INDEX - MATCH është një mjet i fuqishëm kur punoni në Excel, i cili në funksionalitetin e tij është më fleksibël sesa analogu i tij më i afërt - operatori CDF.
Objektivi kryesor i funksionit MATCH është një tregues i numrit në rend të një vlere të caktuar në rangun e zgjedhur.
Sintaksë e operatorit MATCH është si vijon:
= K SERKONI (search_value, lookup_array, [match_type])
- Vlera e menduar - kjo është vlera, pozicioni i së cilës në varg që kërkojmë;
- Shikuar vargun është diapazoni në të cilin ndodhet kjo vlerë;
- Lloji i ndeshjes - Ky është një parametër opsional që përcakton nëse duhet të kërkoni vlera me saktësi ose përafërsisht. Ne do të kërkojmë vlera të sakta, prandaj ky argument nuk përdoret.
Duke përdorur këtë mjet ju mund të automatizoni inputin e argumenteve Numri i linjës dhe Numri i kolonës në funksion INDEX.
Le të shohim se si kjo mund të bëhet me një shembull specifik. Po punojmë me të njëjtën tryezë, për të cilën u diskutua më lart. Më vete, ne kemi dy fusha shtesë - "Emri" dhe "Shuma". Shtë e nevojshme të siguroheni që kur të vendosni emrin e punonjësit, shuma e parave të fituara shfaqet automatikisht. Le të shohim se si kjo mund të zbatohet duke zbatuar funksione INDEX dhe MATCH.
- Para së gjithash, ne zbulojmë se çfarë pagash merr punonjësi Parfenov D.F. Vendosni emrin e tij në fushën e duhur.
- Zgjidhni një qelizë në fushë "Shuma"në të cilën do të shfaqet rezultati përfundimtar. Nis dritaren e argumenteve të funksionit INDEX për vargje.
Në fushë "Array" ne hyjmë në koordinatat e kolonës në të cilën ndodhen pagat e punonjësve.
Fushë Numri i kolonës e lëmë atë bosh, pasi ne përdorim gamën një-dimensionale si shembull.
Por në fushë Numri i linjës thjesht duhet të shkruajmë një funksion MATCH. Për ta shkruar atë, ne i përmbahemi sintaksës së diskutuar më lart. Vendosni menjëherë emrin e operatorit në fushë "Match" pa thonjëza. Pastaj menjëherë hapni kllapën dhe tregoni koordinatat e vlerës së dëshiruar. Këto janë koordinatat e qelizës në të cilën veç e kemi regjistruar emrin e punonjësit Parfenov. Vendosëm një pikëpresje dhe tregojmë koordinatat e diapazonit që po shikohen. Në rastin tonë, kjo është adresa e kolonës me emrat e punonjësve. Pas kësaj, mbyllni kllapën.
Pasi të jenë futur të gjitha vlerat, klikoni në butonin "OK".
- Rezultati i sasisë së fitimeve D. Parfenov pas përpunimit shfaqet në fushë "Sum".
- Tani nëse në fushë "Emri" ne do t'i ndryshojmë përmbajtjet me "Parfenov D.F."psh "Popova M. D.", atëherë vlera e pagave në fushë do të ndryshojë automatikisht "Shuma".
Metoda 3: trajtoni tabela të shumta
Tani le të shohim se si përdoret operatori INDEX Ju mund të përpunoni tabela të shumta. Për këtë qëllim do të zbatohet një argument shtesë. "Numri i zonës".
Kemi tre tavolina. Do tabelë tregon pagat e punonjësve për një muaj të vetëm. Detyra jonë është të zbulojmë pagën (kolona e tretë) e punonjësit të dytë (rreshti i dytë) për muajin e tretë (rajoni i tretë).
- Zgjidhni qelizën në të cilën rezultati do të dalë dhe në mënyrën e zakonshme të hapur Wizard Feature, por kur zgjidhni llojin e operatorit, zgjidhni pamjen e referencës. Ne kemi nevojë për këtë sepse ky lloj mbështet trajtimin e argumenteve. "Numri i zonës".
- Hapet dritarja e argumentit. Në fushë "Link" duhet të specifikojmë adresat e të tre shkallëve. Për ta bërë këtë, vendoseni kursorin në fushë dhe zgjidhni gamën e parë me butonin e majtë të miut të shtypur. Pastaj vendosni një pikëpresje. Kjo është shumë e rëndësishme, sepse nëse menjëherë shkoni në zgjedhjen e grupit tjetër, atëherë adresa e tij thjesht do të zëvendësojë koordinatat e asaj të mëparshme. Kështu që, pasi të keni hyrë në pikëpresje, zgjidhni rangun tjetër. Pastaj përsëri vendosim një pikëpresje dhe zgjedhim renditjen e fundit. E gjithë shprehja që është në fushë "Link" merreni në kllapa.
Në fushë Numri i linjës tregoni numrin "2", pasi ne po kërkojmë një mbiemër të dytë në listë.
Në fushë Numri i kolonës tregoni numrin "3"pasi kolona e pagave është e treta me radhë në secilën tabelë.
Në fushë "Numri i zonës" vendos numrin "3", pasi që ne duhet të gjejmë të dhënat në tabelën e tretë, e cila përmban informacione mbi pagat për muajin e tretë.
Pasi të futen të gjitha të dhënat, klikoni në butonin "OK".
- Pas kësaj, rezultatet e llogaritjes shfaqen në qelizën e zgjedhur më parë. Ajo tregon masën e pagës së punonjësit të dytë (V. M. Safronov) për muajin e tretë.
Metoda 4: llogaritni shumën
Forma e referencës nuk përdoret aq shpesh sa forma e grupit, por mund të përdoret jo vetëm kur punoni me varg të shumëfishtë, por edhe për nevoja të tjera. Për shembull, mund të përdoret për të llogaritur shumën në kombinim me një operator SUM.
Kur shtoni shumën SUM ka sintaksën e mëposhtme:
= SUM (array_address)
Në rastin tonë të veçantë, shuma e fitimeve të të gjithë punonjësve në muaj mund të llogaritet duke përdorur formulën e mëposhtme:
= SUM (C4: C9)
Por mund ta modifikoni pak duke përdorur funksionin INDEX. Pastaj do të ketë formën e mëposhtme:
= SUM (C4: INDEX (C4: C9; 6))
Në këtë rast, koordinatat e fillimit të vargut tregojnë qelizën me të cilën fillon. Por në koordinatat që tregojnë fundin e vargut, përdoret operatori INDEX. Në këtë rast, argumenti i parë i operatorit INDEX tregon një varg, dhe e dyta - në qelizën e fundit - e gjashta.
mësim: Karakteristika të dobishme në Excel
Siç mund ta shihni, funksioni INDEX mund të përdoret në Excel për të zgjidhur detyra mjaft të ndryshme. Edhe pse kemi konsideruar larg nga të gjitha opsionet e mundshme për aplikimin e tij, por vetëm ato më të njohurat. Ekzistojnë dy lloje të këtij funksioni: referenciale dhe për vargje. Mund të përdoret në mënyrë më efektive në kombinim me operatorët e tjerë. Formulat e krijuara në këtë mënyrë do të jenë në gjendje të zgjidhin problemet më komplekse.
