Ranje dinamik ak oto-gwosè

Èske ou gen tab ak done nan Excel ki ka redimansyone, sa vle di kantite ranje (kolòn) ka ogmante oswa diminye nan kou a nan travay? Si gwosè tab la "flote", Lè sa a, w ap oblije toujou ap kontwole moman sa a epi korije li:

• lyen nan fòmil rapò ki fè referans a tablo nou an
• premye seri tab pivot ki bati dapre tab nou an
• premye seri tablo ki bati dapre tablo nou an
• chenn pou dropdowns ki itilize tab nou an kòm yon sous done

Tout bagay sa yo an total pap kite w anwiye 😉

Li pral pi plis pratik ak kòrèk yo kreye yon seri "kawoutchou" dinamik, ki pral otomatikman ajiste nan gwosè a kantite aktyèl la nan ranje ak kolòn nan done. Pou aplike sa a, gen plizyè fason.

Metòd 1. Smart tab

Mete aksan sou seri selil ou yo epi chwazi nan tab la Kay – Fòma kòm Tablo (Lakay – Fòma kòm Tablo):

Si ou pa bezwen konsepsyon trase ki ajoute sou tab la kòm yon efè segondè, Lè sa a, ou ka fèmen li sou tab la ki parèt. Konstriksyon (Design). Chak tab kreye nan fason sa a resevwa yon non ki ka ranplase ak yon sèl ki pi pratik nan menm kote a sou tab la Konstriksyon (Design) nan jaden an Non tab la (Non tab la).

Koulye a, nou ka itilize lyen dinamik nan "tab entelijan" nou an:

• Table 1 - Lien ak tout tab la eksepte ranje header la (A2:D5)
• Tablo 1[#Tout] - lyen pou tout tab la (A1:D5)
• Tablo 1 [Pyè] - referans a yon seri-kolòn san premye selil-tèt la (C2:C5)
• Tablo 1 [# Headers] - lyen nan "tèt la" ak non yo nan kolòn yo (A1: D1)

Referans sa yo travay byen nan fòmil, pou egzanp:

= SOMMA (Tablo 1 [Moskou]) - kalkil sòm total la pou kolòn "Moskou"

or

=VPR(F5;Table 1;3;0) – rechèch nan tablo a pou mwa a soti nan selil F5 epi bay sòm St. Petersburg pou li (ki sa ki VLOOKUP?)

Lyen sa yo ka itilize avèk siksè lè w ap kreye tab pivot lè w chwazi sou tab la Mete - Tablo Pivot (Insert - Tablo Pivote) epi antre non tab entelijan an kòm sous done:

Si ou chwazi yon fragman nan yon tab konsa (pa egzanp, de premye kolòn yo) epi kreye yon dyagram nan nenpòt kalite, Lè sa a, lè ajoute nouvo liy, yo pral otomatikman ajoute nan dyagram nan.

Lè w ap kreye lis drop-down, lyen dirèk ki mennen nan eleman tab entelijan yo pa ka itilize, men ou ka fasilman jwenn limit sa a lè l sèvi avèk yon trick taktik - sèvi ak fonksyon an. ENDRÈK (Endirèk), ki vire tèks la nan yon lyen:

Moun sa yo. yon lyen ki mennen nan yon tab entelijan nan fòm lan nan yon fisèl tèks (nan guillemets!) Vire nan yon lyen plen véritable, ak lis la drop-down nòmalman pèsevwa li.

Metòd 2: dinamik non ranje

Si vire done ou yo nan yon tab entelijan se endezirab pou kèk rezon, Lè sa a, ou ka itilize yon metòd yon ti kras pi konplike, men pi plis sibtil ak versatile - kreye yon seri dinamik ki rele nan Excel ki refere a tab nou an. Lè sa a, tankou nan ka a nan yon tab entelijan, ou ka lib itilize non an nan seri a kreye nan nenpòt fòmil, rapò, tablo, elatriye Ann kòmanse ak yon egzanp senp:

Objektif Travay la: fè yon seri non dinamik ki ta refere a yon lis vil epi otomatikman detire ak retresi nan gwosè lè w ajoute nouvo vil oswa efase yo.

Nou pral bezwen de fonksyon Excel entegre ki disponib nan nenpòt vèsyon - POICPOZ (MATCH) pou detèmine dènye selil ranje a, epi Main Index (ENDEX) pou kreye yon lyen dinamik.

Jwenn dènye selil la lè l sèvi avèk MATCH

MATCH(lookup_value, range, match_type) – yon fonksyon ki chèche yon valè bay nan yon seri (ranje oswa kolòn) epi ki retounen nimewo ordinèl selil kote yo te jwenn li a. Pa egzanp, fòmil MATCH(“Mas”;A1:A5;0) ap retounen nimewo 4 la kòm rezilta, paske mo “Mas” a sitiye nan katriyèm selil la nan kolòn A1:A5. Dènye agiman fonksyon Match_Type = 0 vle di nou ap chèche yon match egzak. Si agiman sa a pa espesifye, Lè sa a, fonksyon an pral chanje nan mòd rechèch la pou valè ki pi pre ki pi piti a - sa a se egzakteman sa ki ka itilize avèk siksè jwenn dènye selil la okipe nan etalaj nou an.

Sans nan jwe fent la se senp. MATCH rechèch pou selil ki nan seri a soti anwo jouk anba epi, an teyori, ta dwe sispann lè li jwenn valè ki pi pre ki pi piti a bay youn nan. Si ou presize yon valè ki evidamman pi gran pase nenpòt ki disponib nan tablo a kòm valè a vle, Lè sa a, MATCH pral rive nan fen tab la, pa jwenn anyen epi bay nimewo sekans dènye selil ki ranpli a. Epi nou bezwen li!

Si gen sèlman nimewo nan etalaj nou an, Lè sa a, nou ka presize yon nimewo kòm valè a vle, ki se evidamman pi gran pase nenpòt nan sa yo ki nan tablo a:

Pou yon garanti, ou ka sèvi ak nimewo a 9E + 307 (9 fwa 10 nan pouvwa a nan 307, sa vle di 9 ak 307 zewo) - nimewo a maksimòm ke Excel ka travay ak nan prensip.

Si gen valè tèks nan kolòn nou an, Lè sa a, kòm ekivalan a nan pi gwo nimewo posib, ou ka mete konstriksyon an REPEAT ("i", 255) - yon fisèl tèks ki gen 255 lèt "i" - dènye lèt la nan. alfabè a. Depi Excel aktyèlman konpare kòd karaktè lè w ap chèche, nenpòt tèks nan tablo nou an pral teknikman "pi piti" pase yon liy "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy":

Jenere yon lyen lè l sèvi avèk INDEX

Kounye a ke nou konnen pozisyon nan dènye eleman ki pa vid nan tablo a, li rete yo fòme yon lyen nan tout seri nou an. Pou sa nou itilize fonksyon an:

INDEX(ranje; ranje_num; kolòn_num)

Li bay sa ki nan selil la soti nan seri a pa ranje ak nimewo kolòn, sa vle di pou egzanp, fonksyon an =INDEX (A1:D5;3;4) nan tablo nou an ak vil ak mwa nan metòd anvan an pral bay 1240 - kontni an. soti nan 3yèm ranje ak 4yèm kolòn, sètadi selil D3. Si gen yon sèl kolòn, Lè sa a, nimewo li yo ka omisyon, sa vle di fòmil INDEX(A2:A6;3) pral bay "Samara" nan dènye ekran an.

Epi gen yon sèl nuans ki pa totalman evidan: si ENDEX la pa jis antre nan selil la apre siy = a, kòm dabitid, men li itilize kòm pati final la nan referans nan seri a apre kolon an, Lè sa a, li pa bay soti ankò. sa ki nan selil la, men adrès li! Kidonk, yon fòmil tankou $A$2:INDEX($A$2:$A$100;3) pral bay yon referans sou seri A2:A4 nan pwodiksyon an.

Epi sa a se kote fonksyon MATCH la antre, ke nou mete andedan INDEX la pou detèmine dinamikman fen lis la:

=$A$2:INDEX($A$2:$A$100; MATCH(REP(“mwen”;255);A2:A100))

Kreye yon seri non

Li rete yo pake li tout nan yon sèl antye. Louvri yon tab fòmil (Fòmil) Epi klike sou la Non Manadjè (Non Manadjè). Nan fenèt la ki ouvè, klike sou bouton an Kreye (nouvo), antre non ranje nou an ak fòmil nan jaden an Range (Referans):

Li rete pou klike sou OK ak seri a pare ka itilize nan nenpòt fòmil, lis drop-down oswa tablo.

• Sèvi ak fonksyon VLOOKUP pou lyen tab ak valè rechèch
• Ki jan yo kreye yon lis deroulan oto-peple
• Ki jan yo kreye yon tablo pivot pou analize yon gwo kantite done