vsebina
Ali imate tabele s podatki v Excelu, ki jim je mogoče spreminjati velikost, torej število vrstic (stolpcev) med delom povečati ali zmanjšati? Če velikosti tabele "plavajo", boste morali nenehno spremljati ta trenutek in ga popraviti:
- povezave v formulah poročil, ki se nanašajo na našo tabelo
- začetni obsegi vrtilnih tabel, ki so zgrajene v skladu z našo tabelo
- začetni obsegi grafikonov, zgrajenih v skladu z našo tabelo
- obsege za spustne menije, ki uporabljajo našo tabelo kot vir podatkov
Vse to skupaj vam ne bo pustilo dolgčas 😉
Veliko bolj priročno in pravilno bo ustvariti dinamični "gumijasti" obseg, ki bo samodejno prilagodil velikost dejanskemu številu vrstic in stolpcev podatkov. Za izvedbo tega obstaja več načinov.
Metoda 1. Pametna miza
Označite obseg celic in izberite na zavihku Domov – Oblikuj kot tabelo (Domov – Oblikuj kot tabelo):
Če črtastega dizajna, ki je v tabelo dodan kot stranski učinek, ne potrebujete, ga lahko izklopite na zavihku, ki se prikaže Konstruktor (Design). Vsaka tako ustvarjena tabela dobi ime, ki ga lahko na istem mestu zavihka zamenjamo z bolj priročnim Konstruktor (Design) na področju Ime tabele (Ime tabele).
Zdaj lahko uporabimo dinamične povezave do naše "pametne tabele":
- Tabela 1 – povezava na celotno tabelo razen na naslovno vrstico (A2:D5)
- Tabela1[#Vse] – povezava do celotne tabele (A1:D5)
- Tabela1[Peter] – sklicevanje na stolpec obsega brez prve glave celice (C2:C5)
- Tabela1[#Headers] – povezava do “glave” z imeni stolpcev (A1:D1)
Takšne reference odlično delujejo v formulah, na primer:
= SUM (Tabela1[Moskva]) – izračun vsote za stolpec “Moskva”
or
=VPR(F5;Tabela 1;3;0) – poiščite v tabeli za mesec iz celice F5 in izdajte vsoto Sankt Peterburga zanj (kaj je VLOOKUP?)
Takšne povezave lahko uspešno uporabimo pri ustvarjanju vrtilnih tabel z izbiro na zavihku Vstavi – vrtilna tabela (Vstavi – vrtilna tabela) in vnos imena pametne tabele kot vira podatkov:
Če izberete del takšne tabele (na primer prva dva stolpca) in ustvarite diagram katere koli vrste, bodo pri dodajanju novih vrstic samodejno dodane v diagram.
Pri ustvarjanju spustnih seznamov ni mogoče uporabiti neposrednih povezav do elementov pametne tabele, vendar lahko to omejitev preprosto obidete s taktičnim trikom – uporabite funkcijo INDIRECT (POSREDNO), ki spremeni besedilo v povezavo:
Tisti. povezava do pametne tabele v obliki besedilnega niza (v narekovajih!) se spremeni v polnopravno povezavo in spustni seznam jo normalno zazna.
2. način: dinamični imenovani obseg
Če pretvarjanje vaših podatkov v pametno tabelo iz nekega razloga ni zaželeno, potem lahko uporabite nekoliko bolj zapleteno, a veliko bolj subtilno in vsestransko metodo – ustvarite dinamično poimenovano območje v Excelu, ki se nanaša na našo tabelo. Nato lahko, tako kot v primeru pametne tabele, poljubno uporabite ime ustvarjenega obsega v poljubnih formulah, poročilih, grafikonih itd. Začnimo s preprostim primerom:
Naloga: naredite dinamičen imenovani obseg, ki bi se nanašal na seznam mest in se samodejno raztezal in krčil po velikosti, ko bi dodajal nova mesta ali jih brisal.
Potrebovali bomo dve vgrajeni Excelovi funkciji, ki sta na voljo v kateri koli različici − POICPOZ (TEKMA) za določitev zadnje celice obsega in INDEX (KAZALO) ustvariti dinamično povezavo.
Iskanje zadnje celice z uporabo MATCH
MATCH(iskalna_vrednost, obseg, vrsta_ujemanja) – funkcija, ki išče dano vrednost v obsegu (vrstica ali stolpec) in vrne zaporedno številko celice, kjer je bila najdena. Na primer, formula MATCH(“marec”;A1:A5;0) bo kot rezultat vrnila številko 4, ker se beseda “marec” nahaja v četrti celici v stolpcu A1:A5. Zadnji argument funkcije Match_Type = 0 pomeni, da iščemo natančno ujemanje. Če ta argument ni naveden, bo funkcija preklopila v iskalni način za najbližjo najmanjšo vrednost – to je točno tisto, kar lahko uspešno uporabimo za iskanje zadnje zasedene celice v naši matriki.
Bistvo trika je preprosto. MATCH išče celice v območju od vrha do dna in bi se teoretično moral ustaviti, ko najde najmanjšo vrednost, ki je najbližja dani. Če kot želeno vrednost določite vrednost, ki je očitno večja od katere koli razpoložljive v tabeli, bo MATCH dosegel sam konec tabele, ne bo našel ničesar in dal zaporedno številko zadnje izpolnjene celice. In potrebujemo ga!
Če so v naši matriki samo števila, lahko kot želeno vrednost določimo število, ki je očitno večje od katerega koli v tabeli:
Za garancijo lahko uporabite število 9E + 307 (9 krat 10 na potenco 307, torej 9 s 307 ničlami) – največje število, s katerim Excel načeloma deluje.
Če so v našem stolpcu besedilne vrednosti, lahko kot ekvivalent največjega možnega števila vstavite konstrukcijo REPEAT("i", 255) - besedilni niz, sestavljen iz 255 črk "i" - zadnja črka abeceda. Ker Excel pri iskanju dejansko primerja kode znakov, bo vsako besedilo v naši tabeli tehnično "manjše" kot tako dolgo "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" linija:
Ustvarite povezavo z uporabo INDEX
Zdaj, ko poznamo položaj zadnjega nepraznega elementa v tabeli, je treba oblikovati povezavo do celotnega obsega. Za to uporabljamo funkcijo:
INDEX(obseg; številka_vrstice; številka_stolpca)
Poda vsebino celice iz obsega po številki vrstice in stolpca, tj. funkcija =INDEX(A1:D5;3;4) v naši tabeli z mesti in meseci iz prejšnje metode bo dala 1240 – vsebina iz 3. vrstice in 4. stolpca, torej celice D3. Če obstaja samo en stolpec, lahko njegovo številko izpustite, tj. formula INDEX(A2:A6;3) bo na zadnjem posnetku zaslona dala »Samara«.
In obstaja en ne povsem očiten odtenek: če INDEKS ni samo vnesen v celico za znakom =, kot običajno, ampak se uporablja kot končni del sklica na obseg za dvopičjem, potem ne izda več vsebino celice, ampak njen naslov! Tako se bo formula, kot je $A$2:INDEX($A$2:$A$100;3), na izhodu sklicevala na obseg A2:A4.
In tukaj nastopi funkcija MATCH, ki jo vstavimo v INDEX, da dinamično določimo konec seznama:
=$A$2:INDEX($A$2:$A$100; UJEMANJE(REP(“I”;255);A2:A100))
Ustvarite imenovan obseg
Ostaja, da vse skupaj združimo v eno celoto. Odprite zavihek Formula (Formule) In kliknite na Upravitelj imen (Upravitelj imen). V oknu, ki se odpre, kliknite gumb ustvarjanje (novo), v polje vnesite ime obsega in formulo Območje (Sklic):
Ostaja še klik OK in pripravljen obseg je mogoče uporabiti v poljubnih formulah, spustnih seznamih ali grafikonih.
- Uporaba funkcije VLOOKUP za povezovanje tabel in iskalnih vrednosti
- Kako ustvariti spustni seznam s samodejnim izpolnjevanjem
- Kako ustvariti vrtilno tabelo za analizo velike količine podatkov