Firxa dinamika b'daqs awtomatiku

Għandek tabelli b'dejta f'Excel li jistgħu jiġu rranġati, jiġifieri n-numru ta 'ringieli (kolonni) jista' jiżdied jew jonqos fil-kors tax-xogħol? Jekk id-daqsijiet tal-mejda "float", allura jkollok timmonitorja kontinwament dan il-mument u tikkoreġih:

• links fil-formuli tar-rapport li jirreferu għat-tabella tagħna
• firxiet inizjali ta 'tabelli pern li huma mibnija skond it-tabella tagħna
• firxiet inizjali ta 'charts mibnija skond it-tabella tagħna
• firxiet għal dropdowns li jużaw it-tabella tagħna bħala sors tad-dejta

Dan kollu b'kollox ma jhallikx tiddejjaq 😉

Ikun ħafna aktar konvenjenti u korrett li tinħoloq firxa dinamika ta '"lastku", li awtomatikament taġġusta fid-daqs għan-numru attwali ta' ringieli u kolonni ta 'dejta. Biex timplimenta dan, hemm diversi modi.

Metodu 1. Tabella intelliġenti

Enfasizza l-firxa ta 'ċelluli tiegħek u agħżel mit-tab Home – Format bħala Tabella (Dar – Format bħala Tabella):

Jekk m'għandekx bżonn id-disinn strixxat li huwa miżjud mat-tabella bħala effett sekondarju, allura tista 'titfih fuq it-tab li tidher Kostruttur (Disinn). Kull tabella maħluqa b'dan il-mod tirċievi isem li jista 'jiġi sostitwit b'wieħed aktar konvenjenti fl-istess post fuq it-tab Kostruttur (Disinn) fil-qasam Isem tal-mejda (Isem tat-Tabella).

Issa nistgħu nużaw links dinamiċi għat-"mejda intelliġenti" tagħna:

• Tabella 1 – link mat-tabella kollha ħlief għar-ringiela ta' l-intestatura (A2:D5)
• Tabella 1[#Kull] – link għat-tabella kollha (A1:D5)
• Tabella 1[Pietru] – referenza għal kolonna ta' firxa mingħajr l-ewwel header taċ-ċellula (C2:C5)
• Tabella 1[#Headers] – link għall-"header" bl-ismijiet tal-kolonni (A1:D1)

Referenzi bħal dawn jaħdmu tajjeb fil-formuli, pereżempju:

= Somma (Tabella 1[Moska]) – kalkolu tas-somma għall-kolonna "Moska"

or

=VPR(F5;Tabella 1;3;0) – fittex fit-tabella għax-xahar miċ-ċellula F5 u ħarġilha s-somma ta' San Pietruburgu (x'inhi VLOOKUP?)

Links bħal dawn jistgħu jintużaw b'suċċess meta jinħolqu tabelli tal-pern billi tagħżel fuq it-tab Daħħal – Pivot Table (Daħħal – Pivot Table) u ddaħħal l-isem tat-tabella intelliġenti bħala s-sors tad-dejta:

Jekk tagħżel framment ta 'tabella bħal din (per eżempju, l-ewwel żewġ kolonni) u toħloq dijagramma ta' kwalunkwe tip, allura meta żżid linji ġodda, dawn se jiżdiedu awtomatikament mad-dijagramma.

Meta toħloq listi drop-down, links diretti għal elementi tal-mejda intelliġenti ma jistgħux jintużaw, iżda tista 'faċilment tevita din il-limitazzjoni billi tuża trick tattiku - uża l-funzjoni INDIRETTA (INDIRETT), li jibdel it-test f'link:

Dawk. link għal tabella intelliġenti fil-forma ta 'sekwenza ta' test (bil-virgoletti!) tinbidel f'rabta sħiħa, u l-lista drop-down normalment tipperċepixxiha.

Metodu 2: Firxa imsemmija dinamika

Jekk iddawwar id-dejta tiegħek f'tabella intelliġenti mhix mixtieqa għal xi raġuni, allura tista 'tuża metodu kemmxejn aktar ikkumplikat, iżda ħafna aktar sottili u versatili - toħloq firxa imsemmija dinamika f'Excel li tirreferi għat-tabella tagħna. Imbagħad, bħal fil-każ ta 'tabella intelliġenti, tista' tuża liberament l-isem tal-firxa maħluqa fi kwalunkwe formuli, rapporti, charts, eċċ. Nibdew b'eżempju sempliċi:

Segretarjali u klerikali.: tagħmel firxa imsemmija dinamika li tirreferi għal lista ta 'bliet u awtomatikament tistira u tiċkien fid-daqs meta żżid bliet ġodda jew tħassarhom.

Ikollna bżonn żewġ funzjonijiet Excel integrati disponibbli fi kwalunkwe verżjoni - POICPOZ (TAQBIL) biex tiddetermina l-aħħar ċellula tal-firxa, u INDIĊI (INDIĊI) biex toħloq rabta dinamika.

Sib l-aħħar ċellola billi tuża MATCH

MATCH(lookup_value, range, match_type) – funzjoni li tfittex valur partikolari f'firxa (ringiela jew kolonna) u tagħti lura n-numru ordinali taċ-ċellula fejn instab. Pereżempju, il-formula MATCH ("Marzu"; A1:A5;0) se tirritorna n-numru 4 bħala riżultat, minħabba li l-kelma "Marzu" tinsab fir-raba 'ċellola fil-kolonna A1:A5. L-aħħar argument tal-funzjoni Match_Type = 0 ifisser li qed infittxu taqbila eżatta. Jekk dan l-argument ma jkunx speċifikat, allura l-funzjoni se taqleb għall-mod ta 'tfittxija għall-eqreb valur iżgħar - dan huwa eżattament dak li jista' jintuża b'suċċess biex issib l-aħħar ċellula okkupata fil-firxa tagħna.

L-essenza tal-trick hija sempliċi. MATCH tfittex ċelluli fil-medda minn fuq għal isfel u, fit-teorija, għandha tieqaf meta ssib l-eqreb valur iżgħar għal dak mogħti. Jekk tispeċifika valur li huwa ovvjament akbar minn kwalunkwe disponibbli fit-tabella bħala l-valur mixtieq, allura MATCH se tasal sal-aħħar nett tat-tabella, ma ssib xejn u tagħti n-numru tas-sekwenza tal-aħħar ċellola mimlija. U għandna bżonnha!

Jekk hemm biss numri fil-firxa tagħna, allura nistgħu nispeċifikaw numru bħala l-valur mixtieq, li ovvjament huwa akbar minn kwalunkwe wieħed minn dawk fit-tabella:

Għal garanzija, tista 'tuża n-numru 9E + 307 (9 darbiet 10 għall-qawwa ta' 307, jiġifieri 9 b'żerijiet 307) - in-numru massimu li Excel jista 'jaħdem miegħu fil-prinċipju.

Jekk hemm valuri tat-test fil-kolonna tagħna, allura bħala l-ekwivalenti tal-akbar numru possibbli, tista 'ddaħħal il-kostruzzjoni REPEAT ("i", 255) - sekwenza ta' test li tikkonsisti minn 255 ittra "i" - l-aħħar ittra ta ' l-alfabett. Peress li Excel fil-fatt iqabbel il-kodiċijiet tal-karattri meta jfittex, kwalunkwe test fit-tabella tagħna se jkun teknikament "iżgħar" minn linja "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy":

Iġġenera link billi tuża INDIĊI

Issa li nafu l-pożizzjoni tal-aħħar element mhux vojt fit-tabella, għad fadal li tifforma link għall-firxa kollha tagħna. Għal dan nużaw il-funzjoni:

INDIĊI (firxa; row_num; column_num)

Jagħti l-kontenut taċ-ċellula mill-firxa b'numru ta 'ringiela u kolonna, jiġifieri, per eżempju, il-funzjoni = INDIĊI (A1: D5; 3; 4) fit-tabella tagħna ma' bliet u xhur mill-metodu preċedenti se tagħti 1240 - il-kontenut mit-3 ringiela u r-4 kolonna, jiġifieri ċ-ċelloli D3. Jekk ikun hemm kolonna waħda biss, allura n-numru tagħha jista 'jitħalla barra, jiġifieri l-formula INDEX(A2:A6;3) se tagħti "Samara" fl-aħħar screenshot.

U hemm sfumatura waħda mhux kompletament ovvja: jekk l-INDIĊI ma jiddaħħalx biss fiċ-ċellula wara s-sinjal =, bħas-soltu, iżda jintuża bħala l-parti finali tar-referenza għall-firxa wara l-kolon, allura ma jibqax jagħti barra il-kontenut taċ-ċellula, iżda l-indirizz tagħha! Għalhekk, formula bħal $A$2:INDEX($A$2:$A$100;3) se tagħti referenza għall-firxa A2:A4 fl-output.

U dan huwa fejn tidħol il-funzjoni MATCH, li aħna ndaħħlu ġewwa l-INDIĊI biex niddeterminaw b'mod dinamiku t-tmiem tal-lista:

=$A$2:INDIĊI($A$2:$A$100; TQABBA(REP(“I”;255);A2:A100))

Oħloq firxa msemmija

Jibqa 'jippakkjah kollu f'sħiħ wieħed. Iftaħ tab formula (Formuli) U kklikkja fuq Maniġer tal-Ismijiet (Isem Maniġer). Fit-tieqa li tiftaħ, ikklikkja l-buttuna Oħloq (ġdid), daħħal l-isem u l-formula tal-firxa tagħna fil-qasam Medda (Referenza):

Fadal biex tikklikkja fuq OK u l-firxa lesta tista 'tintuża fi kwalunkwe formuli, listi drop-down jew charts.

• L-użu tal-funzjoni VLOOKUP biex tgħaqqad it-tabelli u l-valuri ta' tfittxija
• Kif toħloq lista dropdown ta' popolazzjoni awtomatika
• Kif toħloq tabella pivot biex tanalizza ammont kbir ta 'dejta