Përmbajtje
A keni tabela me të dhëna në Excel që mund të ndryshohen përmasat, dmth. numri i rreshtave (kolonave) mund të rritet ose të zvogëlohet gjatë punës? Nëse madhësitë e tabelës "lundrojnë", atëherë do të duhet të monitoroni vazhdimisht këtë moment dhe ta korrigjoni atë:
- lidhjet në formulat e raportit që i referohen tabelës sonë
- diapazoni fillestar i tabelave kryesore që janë ndërtuar sipas tabelës sonë
- vargjet fillestare të grafikëve të ndërtuar sipas tabelës sonë
- vargjet për listën rënëse që përdorin tabelën tonë si burim të dhënash
E gjithë kjo në total nuk do t'ju lërë të mërziteni 😉
Do të jetë shumë më e përshtatshme dhe e saktë të krijohet një gamë dinamike "gome", e cila automatikisht do të përshtatet në madhësi me numrin aktual të rreshtave dhe kolonave të të dhënave. Për ta zbatuar këtë, ka disa mënyra.
Metoda 1. Tabela e zgjuar
Theksoni gamën tuaj të qelizave dhe zgjidhni nga skeda Faqja kryesore – Formati si tabelë (Shtëpia – Formati si tabelë):
Nëse nuk ju nevojitet dizajni me vija që i shtohet tabelës si efekt anësor, atëherë mund ta çaktivizoni në skedën që shfaqet Konstruktor (Dizajn). Çdo tabelë e krijuar në këtë mënyrë merr një emër që mund të zëvendësohet me një më të përshtatshëm në të njëjtin vend në skedë Konstruktor (Dizajn) në fushë Emri i tabelës (Emri i tabelës).
Tani mund të përdorim lidhje dinamike në "tabelën tonë të zgjuar":
- Tabela 1 – lidhje me të gjithë tabelën përveç rreshtit të kokës (A2:D5)
- Tabela 1[#Të gjitha] – lidhje me të gjithë tabelën (A1:D5)
- Tabela 1 [Pjetri] – referencë për një kolonë varg pa kokën e parë të qelizës (C2:C5)
- Tabela 1[#Headers] – lidhje me “titullin” me emrat e kolonave (A1:D1)
Referenca të tilla funksionojnë mirë në formula, për shembull:
= SUM (Tabela 1 [Moskë]) - llogaritja e shumës për kolonën "Moska"
or
=VPR(F5;Tabela 1;3;0) – kërkoni në tabelë për muajin nga qeliza F5 dhe jepni shumën e Shën Petersburgut për të (çfarë është VLOOKUP?)
Lidhje të tilla mund të përdoren me sukses gjatë krijimit të tabelave kryesore duke zgjedhur në skedën Fut - Tabela Pivot (Insert - Pivot Tabela) dhe duke futur emrin e tabelës inteligjente si burim të dhënash:
Nëse zgjidhni një fragment të një tabele të tillë (për shembull, dy kolonat e para) dhe krijoni një diagram të çdo lloji, atëherë kur shtoni linja të reja, ato automatikisht do të shtohen në diagram.
Kur krijoni lista rënëse, lidhjet e drejtpërdrejta me elementët e tabelave inteligjente nuk mund të përdoren, por ju mund ta kapërceni lehtësisht këtë kufizim duke përdorur një truk taktik - përdorni funksionin INDIREKT (Indirekt), e cila e kthen tekstin në një lidhje:
ato. një lidhje me një tabelë të zgjuar në formën e një vargu teksti (në thonjëza!) kthehet në një lidhje të plotë dhe lista rënëse normalisht e percepton atë.
Metoda 2: Gama me emrin dinamik
Nëse kthimi i të dhënave tuaja në një tabelë inteligjente është i padëshirueshëm për ndonjë arsye, atëherë mund të përdorni një metodë pak më të komplikuar, por shumë më delikate dhe të gjithanshme - krijoni një gamë dinamike me emër në Excel që i referohet tabelës sonë. Pastaj, si në rastin e një tabele inteligjente, mund të përdorni lirshëm emrin e diapazonit të krijuar në çdo formula, raport, grafik etj. Le të fillojmë me një shembull të thjeshtë:
Detyrë: bëni një gamë dinamike të emërtuar që do t'i referohej një liste qytetesh dhe do të zgjerohej dhe zvogëlohej automatikisht në madhësi kur shtoni qytete të reja ose fshini ato.
Do të na duhen dy funksione të integruara Excel të disponueshme në çdo version - POICPOZ (ndeshje) për të përcaktuar qelizën e fundit të diapazonit, dhe INDEKSI (INDEX) për të krijuar një lidhje dinamike.
Gjetja e qelizës së fundit duke përdorur MATCH
MATCH (vlera_kërkuese, diapazoni, lloji i përputhjes) – një funksion që kërkon një vlerë të caktuar në një varg (rresht ose kolonë) dhe kthen numrin rendor të qelizës ku është gjetur. Për shembull, formula MATCH("Mars"; A1:A5;0) do të kthejë numrin 4 si rezultat, sepse fjala "Mars" ndodhet në qelizën e katërt në kolonën A1:A5. Argumenti i fundit i funksionit Match_Type = 0 do të thotë se ne po kërkojmë një përputhje të saktë. Nëse ky argument nuk specifikohet, atëherë funksioni do të kalojë në modalitetin e kërkimit për vlerën më të vogël më të afërt - kjo është pikërisht ajo që mund të përdoret me sukses për të gjetur qelizën e fundit të zënë në grupin tonë.
Thelbi i mashtrimit është i thjeshtë. MATCH kërkon për qelizat në rangun nga lart poshtë dhe, në teori, duhet të ndalojë kur të gjejë vlerën më të vogël më të afërt me atë të dhënë. Nëse specifikoni një vlerë që është padyshim më e madhe se çdo e disponueshme në tabelë si vlerë e dëshiruar, atëherë MATCH do të arrijë në fund të tabelës, do të gjejë asgjë dhe do të japë numrin e sekuencës së qelizës së fundit të mbushur. Dhe ne kemi nevojë për të!
Nëse ka vetëm numra në grupin tonë, atëherë ne mund të specifikojmë një numër si vlerën e dëshiruar, i cili është padyshim më i madh se cilido nga ata në tabelë:
Për garanci, mund të përdorni numrin 9E + 307 (9 herë 10 në fuqinë 307, pra 9 me 307 zero) - numri maksimal me të cilin mund të punojë Excel në parim.
Nëse ka vlera teksti në kolonën tonë, atëherë si ekuivalent i numrit më të madh të mundshëm, mund të futni konstruksionin REPEAT("i", 255) - një varg teksti i përbërë nga 255 shkronja "i" - shkronja e fundit e alfabeti. Meqenëse Excel në të vërtetë krahason kodet e karaktereve kur kërkon, çdo tekst në tabelën tonë teknikisht do të jetë "më i vogël" se një rresht kaq i gjatë "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy":
Krijo një lidhje duke përdorur INDEX
Tani që e dimë pozicionin e elementit të fundit jo bosh në tabelë, mbetet të formojmë një lidhje me të gjithë gamën tonë. Për këtë përdorim funksionin:
INDEX (varg; rreshti_num; kolona_num)
Ai jep përmbajtjen e qelizës nga diapazoni sipas numrit të rreshtit dhe kolonës, p.sh., funksioni =INDEX(A1:D5;3;4) në tabelën tonë me qytete dhe muaj nga metoda e mëparshme do të japë 1240 - përmbajtjen nga rreshti i 3-të dhe kolona e 4-të, pra qelizat D3. Nëse ka vetëm një kolonë, atëherë numri i saj mund të hiqet, dmth formula INDEX(A2:A6;3) do të japë "Samara" në pamjen e fundit të ekranit.
Dhe ka një nuancë jo plotësisht të dukshme: nëse INDEX nuk futet vetëm në qelizë pas shenjës =, si zakonisht, por përdoret si pjesa përfundimtare e referencës për diapazonin pas dy pikave, atëherë ai nuk jep më përmbajtjen e qelizës, por adresën e saj! Kështu, një formulë si $A$2:INDEX($A$2:$A$100;3) do të japë një referencë për diapazonin A2:A4 në dalje.
Dhe këtu hyn funksioni MATCH, të cilin e fusim brenda INDEX për të përcaktuar në mënyrë dinamike fundin e listës:
=$A$2:INDEX($A$2:$A$100; Ndeshja (REP(“I”;255);A2:A100))
Krijo një varg të emërtuar
Mbetet për t'i paketuar të gjitha në një tërësi të vetme. Hapni një skedë formulë (Formulat) Dhe klikoni Emri Menaxher (Emri i menaxherit). Në dritaren që hapet, klikoni butonin Krijo (i ri), shkruani emrin dhe formulën tonë të gamës në fushë Varg (Referenca):
Mbetet për të klikuar OK dhe diapazoni i gatshëm mund të përdoret në çdo formulë, listë rënëse ose grafik.
- Përdorimi i funksionit VLOOKUP për të lidhur tabelat dhe vlerat e kërkimit
- Si të krijoni një listë drop-down të plotësuar automatikisht
- Si të krijoni një tabelë kryesore për të analizuar një sasi të madhe të dhënash