Sadržaj
Imate li tablice s podacima u Excelu kojima se može mijenjati veličina tj. povećavati ili smanjivati broj redaka (stupaca) u tijeku rada? Ako veličine tablice "plutaju", morat ćete stalno pratiti ovaj trenutak i ispravljati ga:
- poveznice u formulama izvješća koje se odnose na našu tablicu
- početni rasponi pivot tablica koje su izgrađene prema našoj tablici
- početni rasponi grafikona izgrađenih prema našoj tablici
- rasponi za padajuće izbornike koji koriste našu tablicu kao izvor podataka
Sve to skupa neće vam dopustiti da vam bude dosadno 😉
Bit će mnogo praktičnije i ispravnije stvoriti dinamički "gumeni" raspon, koji će se automatski prilagoditi veličini stvarnom broju redaka i stupaca podataka. Da biste to implementirali, postoji nekoliko načina.
Metoda 1. Pametna tablica
Označite svoj raspon ćelija i odaberite s kartice Početna – Oblikuj kao tablicu (Početna stranica – Oblikuj kao tablicu):
Ako vam nije potreban prugasti dizajn koji je dodan tablici kao nuspojava, možete ga isključiti na kartici koja se pojavi Konstruktor (Dizajn). Svaka tablica kreirana na ovaj način dobiva naziv koji se može zamijeniti prikladnijim na istom mjestu kartice Konstruktor (Dizajn) u području Naziv tablice (Naziv tablice).
Sada možemo koristiti dinamičke veze na našu "pametnu tablicu":
- Tablica 1 – veza na cijelu tablicu osim na redak zaglavlja (A2:D5)
- Tablica 1[#Sve] – poveznica na cijelu tablicu (A1:D5)
- Stol1[Petar] – referenca na stupac raspona bez prvog zaglavlja ćelije (C2:C5)
- Tablica 1[#Zaglavlja] – poveznica na “zaglavlje” s nazivima stupaca (A1:D1)
Takve reference izvrsno funkcioniraju u formulama, na primjer:
= SUM (Tablica 1 [Moskva]) – izračun zbroja za stupac “Moskva”
or
=VPR(F5;Tablica 1;3;0) – pretražite u tablici mjesec iz ćelije F5 i izdajte zbroj Sankt Peterburga za njega (što je VLOOKUP?)
Takve se poveznice mogu uspješno koristiti pri izradi pivot tablica odabirom na kartici Umetni – zaokretna tablica (Umetni – zaokretna tablica) i unos naziva pametne tablice kao izvora podataka:
Ako odaberete fragment takve tablice (na primjer, prva dva stupca) i stvorite dijagram bilo koje vrste, tada će se pri dodavanju novih redaka automatski dodati dijagramu.
Prilikom izrade padajućih popisa ne mogu se koristiti izravne poveznice na elemente pametne tablice, ali to ograničenje možete lako zaobići koristeći taktički trik – upotrijebite funkciju INDIREKTNO (Neizravno), koji pretvara tekst u poveznicu:
Oni. poveznica na pametnu tablicu u obliku tekstualnog niza (u navodnicima!) pretvara se u punopravnu vezu, a padajući popis to normalno percipira.
Metoda 2: Dinamički imenovani raspon
Ako je pretvaranje vaših podataka u pametnu tablicu iz nekog razloga nepoželjno, tada možete upotrijebiti nešto kompliciraniju, ali mnogo suptilniju i svestraniju metodu – stvoriti dinamički imenovani raspon u Excelu koji se odnosi na našu tablicu. Zatim, kao u slučaju pametne tablice, možete slobodno koristiti naziv kreiranog raspona u bilo kojim formulama, izvješćima, grafikonima itd. Počnimo s jednostavnim primjerom:
Zadatak: napravite dinamički imenovani raspon koji bi se odnosio na popis gradova i automatski rastezao i smanjivao u veličini prilikom dodavanja novih gradova ili njihovog brisanja.
Trebat će nam dvije ugrađene Excel funkcije dostupne u bilo kojoj verziji − POICPOZ (UTAKMICA) za određivanje zadnje ćelije raspona i INDEKS (INDEKS) za stvaranje dinamičke veze.
Pronalaženje zadnje ćelije pomoću MATCH
MATCH(tražena_vrijednost, raspon, vrsta_podudaranja) – funkcija koja traži zadanu vrijednost u rasponu (redak ili stupac) i vraća redni broj ćelije u kojoj je pronađena. Na primjer, formula MATCH(“March”;A1:A5;0) vratit će broj 4 kao rezultat jer se riječ “March” nalazi u četvrtoj ćeliji u stupcu A1:A5. Zadnji argument funkcije Match_Type = 0 znači da tražimo točno podudaranje. Ako ovaj argument nije naveden, funkcija će se prebaciti u način pretraživanja za najbližu najmanju vrijednost – to je upravo ono što se može uspješno koristiti za pronalaženje posljednje zauzete ćelije u našem nizu.
Suština trika je jednostavna. MATCH traži ćelije u rasponu od vrha do dna i, u teoriji, trebao bi prestati kada pronađe najmanju vrijednost najbližu zadanoj. Ako kao željenu vrijednost navedete vrijednost koja je očito veća od bilo koje dostupne u tablici, tada će MATCH doći do samog kraja tablice, pronaći ništa i dati redni broj zadnje ispunjene ćelije. I treba nam!
Ako u našem nizu postoje samo brojevi, tada kao željenu vrijednost možemo navesti broj koji je očito veći od bilo kojeg u tablici:
Za garanciju možete koristiti broj 9E + 307 (9 puta 10 na potenciju 307, tj. 9 sa 307 nula) – najveći broj s kojim Excel načelno može raditi.
Ako u našem stupcu postoje tekstualne vrijednosti, tada kao ekvivalent najvećem mogućem broju možete umetnuti konstrukciju REPEAT(“i”, 255) – tekstualni niz koji se sastoji od 255 slova “i” – posljednje slovo od Abeceda. Budući da Excel zapravo uspoređuje kodove znakova prilikom pretraživanja, bilo koji tekst u našoj tablici će tehnički biti "manji" od tako dugog retka "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy"
Generirajte vezu koristeći INDEX
Sada kada znamo poziciju posljednjeg nepraznog elementa u tablici, preostaje nam formirati poveznicu na cijeli naš raspon. Za to koristimo funkciju:
INDEX(raspon; broj_reda; broj_stupca)
Daje sadržaj ćelije iz raspona po broju retka i stupca, tj. npr. funkcija =INDEX(A1:D5;3;4) u našoj tablici s gradovima i mjesecima iz prethodne metode dat će 1240 – sadržaj iz 3. retka i 4. stupca, odnosno ćelije D3. Ako postoji samo jedan stupac, tada se njegov broj može izostaviti, tj. formula INDEX(A2:A6;3) će dati "Samara" na zadnjoj snimci zaslona.
I postoji jedna ne sasvim očita nijansa: ako se INDEKS ne unosi samo u ćeliju nakon znaka =, kao i obično, već se koristi kao završni dio reference na raspon nakon dvotočke, tada se više ne daje sadržaj ćelije, ali njenu adresu! Stoga će formula poput $A$2:INDEX($A$2:$A$100;3) dati referencu na raspon A2:A4 na izlazu.
I tu dolazi funkcija MATCH, koju umećemo unutar INDEX-a kako bismo dinamički odredili kraj popisa:
=$A$2:INDEX($A$2:$A$100; MATCH(REP(“I”;255);A2:A100))
Stvorite imenovani raspon
Ostaje da se sve to spakuje u jednu cjelinu. Otvorite karticu formula (Formule) I kliknite gumb Upravitelj imena (Upravitelj naziva). U prozoru koji se otvori kliknite gumb stvoriti (novi), unesite naziv našeg raspona i formulu u polje Raspon (Referenca):
Ostaje kliknuti na OK a spremni raspon se može koristiti u svim formulama, padajućim popisima ili grafikonima.
- Korištenje funkcije VLOOKUP za povezivanje tablica i vrijednosti pretraživanja
- Kako stvoriti padajući popis koji se automatski popunjava
- Kako izraditi zaokretnu tablicu za analizu velike količine podataka