Efnisyfirlit
Ertu með töflur með gögnum í Excel sem hægt er að breyta stærð, þ.e. fjölda raða (dálka) getur fjölgað eða fækkað í vinnunni? Ef borðstærðirnar „fljóta“, þá verðurðu stöðugt að fylgjast með þessu augnabliki og leiðrétta það:
- tenglar í skýrsluformúlum sem vísa í töfluna okkar
- upphafssvið af snúningstöflum sem eru byggðar samkvæmt töflunni okkar
- upphafssvið korta byggð í samræmi við töfluna okkar
- svið fyrir fellilista sem nota töfluna okkar sem gagnagjafa
Allt þetta í heild mun ekki láta þér leiðast 😉
Það verður mun þægilegra og réttara að búa til kraftmikið „gúmmí“ svið, sem mun sjálfkrafa aðlagast stærðinni að raunverulegum fjölda lína og dálka af gögnum. Til að framkvæma þetta eru nokkrar leiðir.
Aðferð 1. Snjallt borð
Auðkenndu svið þitt af frumum og veldu af flipanum Heim – Snið sem töflu (Heima – Snið sem töflu):
Ef þú þarft ekki röndóttu hönnunina sem er bætt við borðið sem aukaverkun, þá geturðu slökkt á henni á flipanum sem birtist Smiður (hönnun). Hvert borð sem búið er til á þennan hátt fær nafn sem hægt er að skipta út fyrir þægilegra á sama stað á flipanum Smiður (hönnun) á vellinum Heiti töflu (Nafn töflu).
Nú getum við notað kraftmikla tengla á „snjallborðið“ okkar:
- Tafla 1 – tengill á alla töfluna nema hauslínuna (A2:D5)
- Tafla1[#Allt] - hlekkur á alla töfluna (A1:D5)
- Tafla 1[Pétur] – tilvísun í sviðsdálk án fyrsta frumuhauss (C2:C5)
- Tafla1[#hausar] – tengill á „hausinn“ með nöfnum dálkanna (A1:D1)
Slíkar tilvísanir virka frábærlega í formúlum, til dæmis:
= SUM (Tafla 1[Moskva]) - útreikningur á summan fyrir dálkinn „Moskvu“
or
=VPR(F5;Tafla 1;3;0) – leitaðu í töflunni fyrir mánuðinn úr reit F5 og gefðu út upphæð Sankti Pétursborgar fyrir hann (hvað er VLOOKUP?)
Slíka tengla er hægt að nota með góðum árangri þegar búið er til pivot-töflur með því að velja á flipanum Setja inn – snúningstafla (Insert – Pivot Tafla) og sláðu inn nafn snjalltöflunnar sem gagnagjafa:
Ef þú velur brot af slíkri töflu (til dæmis fyrstu tvo dálkana) og býrð til skýringarmynd af hvaða gerð sem er, þá bætast þær sjálfkrafa við skýringarmyndina þegar nýjum línum er bætt við.
Þegar þú býrð til fellilista er ekki hægt að nota bein tengla á snjallborðseiningar, en þú getur auðveldlega komist í kringum þessa takmörkun með taktískum bragði - notaðu aðgerðina ÓBEIN (Óbein), sem breytir textanum í hlekk:
Þeir. hlekkur á snjalltöflu í formi textastrengs (í gæsalappa!) breytist í fullgildan hlekk og fellilistann skynjar það venjulega.
Aðferð 2: Dynamic named range
Ef það er óæskilegt að breyta gögnunum þínum í snjalla töflu af einhverjum ástæðum, þá geturðu notað aðeins flóknari, en miklu lúmskari og fjölhæfari aðferð - búið til kraftmikið nafnsvið í Excel sem vísar til töflunnar okkar. Síðan, eins og þegar um snjalltöflu er að ræða, geturðu frjálslega notað heiti sviðsins sem búið er til í hvaða formúlu sem er, skýrslur, töflur osfrv. Byrjum á einföldu dæmi:
Verkefni: búðu til kraftmikið nafnsvið sem myndi vísa til lista yfir borgir og teygjast sjálfkrafa og minnka að stærð þegar nýjum borgum er bætt við eða þeim eytt.
Við munum þurfa tvær innbyggðar Excel aðgerðir tiltækar í hvaða útgáfu sem er − POICPOZ (MATCH) til að ákvarða síðasta reitinn á sviðinu, og INDEX (VÍSITALA) til að búa til kraftmikinn hlekk.
Að finna síðasta reitinn með því að nota MATCH
MATCH(leitargildi, svið, samsvörunargerð) – fall sem leitar að tilteknu gildi á bili (röð eða dálki) og skilar raðtölu reitsins þar sem það fannst. Til dæmis mun formúlan MATCH(“Mars”;A1:A5;0) skila tölunni 4 vegna þess að orðið „Mars“ er staðsett í fjórða reitnum í dálki A1:A5. Síðasta fallsviðið Match_Type = 0 þýðir að við erum að leita að nákvæmri samsvörun. Ef þessi rök eru ekki tilgreind mun aðgerðin skipta yfir í leitarham fyrir næsta minnsta gildi - þetta er nákvæmlega það sem hægt er að nota til að finna síðasta upptekna reitinn í fylkinu okkar.
Kjarninn í bragðinu er einfaldur. MATCH leitar að frumum á bilinu frá toppi til botns og ætti í orði að hætta þegar það finnur næsta minnsta gildi við það sem gefið er. Ef þú tilgreinir gildi sem er augljóslega hærra en nokkurt sem er tiltækt í töflunni sem æskilegt gildi, þá mun MATCH ná alveg á enda töflunnar, finna ekkert og gefa upp raðnúmer síðasta fyllta reitsins. Og við þurfum þess!
Ef það eru aðeins tölur í fylkinu okkar, þá getum við tilgreint tölu sem æskilegt gildi, sem er augljóslega hærra en nokkur þeirra í töflunni:
Fyrir tryggingu geturðu notað töluna 9E + 307 (9 sinnum 10 í krafti 307, þ.e. 9 með 307 núllum) – hámarksfjöldan sem Excel getur unnið með í grundvallaratriðum.
Ef það eru textagildi í dálkinum okkar, þá sem jafngildi stærstu mögulegu tölu, geturðu sett inn smíði REPEAT(“i”, 255) – textastreng sem samanstendur af 255 stöfum „i“ – síðasta stafinn í stafrófið. Þar sem Excel ber saman stafakóða þegar leitað er, mun texti í töflunni okkar tæknilega vera "minni" en svo löng "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" línan:
Búðu til tengil með því að nota INDEX
Nú þegar við vitum staðsetningu síðasta ótóma þáttarins í töflunni er eftir að mynda tengil á allt úrvalið okkar. Til þess notum við aðgerðina:
INDEX(svið; röð_númer; dálkur_tal)
Það gefur innihald reitsins úr bilinu eftir röð og dálknúmeri, þ.e. fallið =INDEX(A1:D5;3;4) í töflunni okkar með borgum og mánuðum frá fyrri aðferð gefur 1240 – innihaldið úr 3. röð og 4. dálki, þ.e. hólfum D3. Ef það er aðeins einn dálkur, þá er hægt að sleppa fjölda hans, þ.e. formúla INDEX(A2:A6;3) gefur „Samara“ í síðustu skjámynd.
Og það er einn ekki alveg augljós blæbrigði: ef INDEX er ekki bara slegið inn í reitinn á eftir = tákninu, eins og venjulega, heldur er hann notaður sem lokahluti tilvísunarinnar í bilið á eftir tvípunktinum, þá gefur það ekki lengur út. innihald frumunnar, en heimilisfang hennar! Þannig mun formúla eins og $A$2:INDEX($A$2:$A$100;3) gefa tilvísun í bilið A2:A4 við úttakið.
Og þetta er þar sem MATCH aðgerðin kemur inn, sem við setjum inn í INDEX til að ákvarða endann á listanum á kraftmikinn hátt:
=$A$2:INDEX($A$2:$A$100; MATCH(REP(“I“;255);A2:A100))
Búðu til nafngreint svið
Það á eftir að pakka þessu öllu saman í eina heild. Opnaðu flipa uppskrift (Formúlur) Og smelltu á Nafnastjóri (Nafnastjóri). Smelltu á hnappinn í glugganum sem opnast Búa til (nýtt), sláðu inn sviðsheiti okkar og formúlu í reitinn Range (Tilvísun):
Það á eftir að smella á OK og tilbúið svið er hægt að nota í hvaða formúlu sem er, fellilista eða töflur.
- Notkun VLOOKUP aðgerðarinnar til að tengja töflur og uppflettingargildi
- Hvernig á að búa til sjálfvirkan fellilista
- Hvernig á að búa til snúningstöflu til að greina mikið magn af gögnum