Dinamički raspon s automatskim dimenzioniranjem

Da li imate tabele sa podacima u Excel-u kojima se može menjati veličina, odnosno broj redova (kolona) može da se povećava ili smanjuje u toku rada? Ako veličine tablice "plutaju", morat ćete stalno pratiti ovaj trenutak i ispravljati ga:

  • veze u formulama izvještaja koje se odnose na našu tabelu
  • početni rasponi stožernih tabela koji su napravljeni prema našoj tabeli
  • početni rasponi grafikona napravljeni prema našoj tabeli
  • rasponi za padajuće menije koji koriste našu tablicu kao izvor podataka

Sve ovo ukupno vam neće dozvoliti da dosadite 😉

Biće mnogo praktičnije i ispravnije kreirati dinamički „gumeni“ raspon, koji će se automatski prilagođavati veličini stvarnom broju redova i stupaca podataka. Da biste to implementirali, postoji nekoliko načina.

Metoda 1. Pametni sto

Označite svoj raspon ćelija i odaberite na kartici Početna – Formatiranje kao tabela (Početna – Formatiranje kao tabela):

Dinamički raspon s automatskim dimenzioniranjem

Ako vam ne treba prugasti dizajn koji je dodan tablici kao nuspojava, možete ga isključiti na kartici koja se pojavi Konstruktor (dizajn). Svaka tabela kreirana na ovaj način dobija ime koje se može zameniti pogodnijim na istom mestu na kartici Konstruktor (dizajn) na terenu Naziv tabele (Naziv tabele).

Dinamički raspon s automatskim dimenzioniranjem

Sada možemo koristiti dinamičke veze do našeg "pametnog stola":

  • Tabela 1 – veza do cijele tabele osim reda zaglavlja (A2:D5)
  • Tabela 1[#Sve] – link do cijele tabele (A1:D5)
  • Sto 1[Petar] – referenca na kolonu raspona bez prvog zaglavlja ćelije (C2:C5)
  • Tabela 1[#Headers] – link na “header” sa nazivima kolona (A1:D1)

Takve reference odlično funkcioniraju u formulama, na primjer:

= SUM (Tabela 1[Moskva]) – obračun sume za kolonu „Moskva“

or

=VPR(F5;Tabela 1;3;0) – pretražite u tabeli mjesec iz ćelije F5 i za njega izdajte sumu iz Sankt Peterburga (šta je VLOOKUP?)

Takve veze se mogu uspješno koristiti prilikom kreiranja pivot tablica odabirom na kartici Insert – Pivot Table (Insert – Pivot Table) i unošenje imena pametne tablice kao izvora podataka:

Dinamički raspon s automatskim dimenzioniranjem

Ako odaberete fragment takve tablice (na primjer, prve dvije kolone) i kreirate dijagram bilo koje vrste, tada će se prilikom dodavanja novih linija automatski dodati u dijagram.

Prilikom kreiranja padajućih lista ne mogu se koristiti direktne veze do elemenata pametne tablice, ali možete lako zaobići ovo ograničenje pomoću taktičkog trika – koristite funkciju INDIREKTNO (INDIREKTNO), koji tekst pretvara u vezu:

Dinamički raspon s automatskim dimenzioniranjem

One. veza do pametne tabele u obliku tekstualnog niza (pod navodnicima!) pretvara se u punopravnu vezu, a padajuća lista je normalno percipira.

Metoda 2: Dinamički imenovani raspon

Ako je pretvaranje vaših podataka u pametnu tablicu iz nekog razloga nepoželjno, onda možete koristiti malo kompliciraniju, ali mnogo suptilniju i svestraniju metodu – kreirati dinamički imenovani raspon u Excelu koji se odnosi na našu tablicu. Zatim, kao iu slučaju pametne tablice, možete slobodno koristiti naziv kreiranog raspona u bilo kojoj formuli, izvještaju, grafikonu itd. Počnimo s jednostavnim primjerom:

Dinamički raspon s automatskim dimenzioniranjem

zadatak: napravite dinamički imenovani raspon koji će se odnositi na listu gradova i automatski se rasteže i smanjuje u veličini kada dodajete nove gradove ili ih brišete.

Trebat će nam dvije ugrađene Excel funkcije dostupne u bilo kojoj verziji − POICPOZ (UTAKMICA) za određivanje zadnje ćelije raspona, i INDEX (INDEX) da kreirate dinamičku vezu.

Pronalaženje posljednje ćelije pomoću MATCH

MATCH(vrijednost_pretraživanja, raspon, vrsta_podudaranja) – funkcija koja traži datu vrijednost u rasponu (redak ili stupac) i vraća redni broj ćelije u kojoj je pronađena. Na primjer, formula MATCH(“Mart”;A1:A5;0) će kao rezultat vratiti broj 4, jer se riječ “Mart” nalazi u četvrtoj ćeliji u koloni A1:A5. Posljednji argument funkcije Match_Type = 0 znači da tražimo točno podudaranje. Ako ovaj argument nije naveden, funkcija će se prebaciti u mod 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, teoretski, treba da se zaustavi kada pronađe najmanju vrijednost najbližu datoj. Ako navedete vrijednost koja je očito veća od bilo koje dostupne u tabeli kao željenu vrijednost, tada će MATCH doći do samog kraja tabele, pronaći ništa i dati redni broj posljednje popunjene ćelije. I treba nam!

Ako u našem nizu postoje samo brojevi, onda možemo odrediti broj kao željenu vrijednost, koja je očito veća od bilo kojeg od onih u tabeli:

Dinamički raspon s automatskim dimenzioniranjem

Za garanciju možete koristiti broj 9E + 307 (9 puta 10 na stepen od 307, tj. 9 sa 307 nula) – maksimalni broj s kojim Excel može raditi u principu.

Ako u našoj koloni postoje tekstualne vrijednosti, onda 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 abeceda. Budući da Excel zapravo uspoređuje kodove znakova prilikom pretraživanja, bilo koji tekst u našoj tablici tehnički će biti „manji“ od tako dugačkog reda „yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy“:

Dinamički raspon s automatskim dimenzioniranjem

Generirajte vezu koristeći INDEX

Sada kada znamo poziciju posljednjeg nepraznog elementa u tabeli, ostaje da formiramo vezu za cijeli naš raspon. Za to koristimo funkciju:

INDEX(opseg; broj_reda; broj_kolone)

Daje sadržaj ćelije iz raspona po broju reda i stupca, tj. na primjer, funkcija =INDEX(A1:D5;3;4) u našoj tabeli sa gradovima i mjesecima iz prethodne metode će dati 1240 – sadržaj iz 3. reda i 4. kolone, odnosno ćelije D3. Ako postoji samo jedna kolona, ​​onda se njen broj može izostaviti, tj. formula INDEX(A2:A6;3) će dati „Samara“ na zadnjem snimku ekrana.

A postoji i jedna ne sasvim očigledna nijansa: ako se INDEX ne unese samo u ćeliju nakon znaka =, kao i obično, već se koristi kao završni dio reference na raspon nakon dvotočke, onda se više ne daje sadržaj ćelije, ali njena adresa! Dakle, formula poput $A$2:INDEX($A$2:$A$100;3) će dati referencu na opseg A2:A4 na izlazu.

I tu dolazi funkcija MATCH, koju ubacujemo unutar INDEX kako bismo dinamički odredili kraj liste:

=$A$2:INDEX($A$2:$A$100; MEČ(REP(“I”;255);A2:A100))

Kreirajte imenovani raspon

Ostaje sve to upakovati u jednu celinu. Otvorite karticu formula (Formule) I kliknite na Ime Manager (Upravitelj imena). U prozoru koji se otvori kliknite na dugme stvoriti (novo), unesite naziv našeg raspona i formulu u polje domet (Referenca):

Dinamički raspon s automatskim dimenzioniranjem

Ostaje kliknuti na OK a spremni raspon se može koristiti u bilo kojoj formulaciji, padajućim listama ili grafikonima.

  • Korištenje funkcije VLOOKUP za povezivanje tablica i vrijednosti pretraživanja
  • Kako kreirati padajuću listu koja se automatski popunjava
  • Kako napraviti pivot tablicu za analizu velike količine podataka

 

Ostavite odgovor