Saturs
Vai jums programmā Excel ir tabulas ar datiem, kuru izmērus var mainīt, ti, rindu (kolonnu) skaits darba gaitā var palielināties vai samazināties? Ja tabulas izmēri “peld”, tad jums būs pastāvīgi jāuzrauga šis brīdis un jālabo:
- saites pārskatu formulās, kas attiecas uz mūsu tabulu
- sākotnējie rakurstabulu diapazoni, kas ir veidoti saskaņā ar mūsu tabulu
- sākotnējie diagrammu diapazoni, kas izveidoti saskaņā ar mūsu tabulu
- diapazoni nolaižamajiem izvēlnēm, kas izmanto mūsu tabulu kā datu avotu
Tas viss kopā neļaus garlaikoties 😉
Daudz ērtāk un pareizāk būs izveidot dinamisku “gumijas” diapazonu, kura izmērs automātiski pielāgosies faktiskajam datu rindu un kolonnu skaitam. Lai to īstenotu, ir vairāki veidi.
1. metode. Viedais galds
Iezīmējiet savu šūnu diapazonu un atlasiet cilnē Sākums — formatēt kā tabulu (Sākums — formatēt kā tabulu):
Ja jums nav nepieciešams svītrains dizains, kas tabulai tiek pievienots kā blakus efekts, varat to izslēgt parādītajā cilnē Konstruktors (dizains). Katra šādā veidā izveidotā tabula saņem nosaukumu, ko tajā pašā cilnes vietā var aizstāt ar ērtāku Konstruktors (dizains) laukā Tabulas nosaukums (Tabulas nosaukums).
Tagad mēs varam izmantot dinamiskās saites uz mūsu “viedo tabulu”:
- Tabula 1 – saite uz visu tabulu, izņemot galvenes rindu (A2:D5)
- 1. tabula[#Visi] – saite uz visu tabulu (A1:D5)
- 1. tabula [Pēteris] – atsauce uz diapazona kolonnu bez pirmās šūnas galvenes (C2:C5)
- 1. tabula[#Headers] - saite uz "galveni" ar kolonnu nosaukumiem (A1:D1)
Šādas atsauces lieliski darbojas formulās, piemēram:
= SUM (1. tabula[Maskava]) – summas aprēķins ailei “Maskava”
or
=VPR(F5;Tabula 1;3;0) – meklējiet tabulā mēnesi no šūnas F5 un izrakstiet par to Sanktpēterburgas summu (kas ir VLOOKUP?)
Šādas saites var veiksmīgi izmantot, veidojot rakurstabulas, atlasot cilnē Ievietošana — rakurstabula (ievietošana — rakurstabula) un ievadot viedās tabulas nosaukumu kā datu avotu:
Ja atlasāt šādas tabulas fragmentu (piemēram, pirmās divas kolonnas) un izveidojat jebkura veida diagrammu, tad, pievienojot jaunas rindas, tās automātiski tiks pievienotas diagrammai.
Veidojot nolaižamos sarakstus, nevar izmantot tiešās saites uz viedās tabulas elementiem, taču jūs varat viegli apiet šo ierobežojumu, izmantojot taktisku triku – izmantojiet funkciju NETIEŠAIS (NETIEŠI), kas pārvērš tekstu par saiti:
Tie. saite uz viedo tabulu teksta virknes formā (pēdiņās!) pārvēršas par pilnvērtīgu saiti, un nolaižamais saraksts to parasti uztver.
2. metode: dinamiski nosauktais diapazons
Ja datu pārvēršana viedtabulā kāda iemesla dēļ nav vēlama, tad varat izmantot nedaudz sarežģītāku, taču daudz smalkāku un daudzpusīgāku metodi – programmā Excel izveidot dinamisku nosaukumu diapazonu, kas attiecas uz mūsu tabulu. Pēc tam, tāpat kā viedās tabulas gadījumā, izveidotā diapazona nosaukumu varat brīvi izmantot jebkurās formulās, atskaitēs, diagrammās utt. Sāksim ar vienkāršu piemēru:
Uzdevums: izveidojiet dinamisku nosaukumu diapazonu, kas atsauktos uz pilsētu sarakstu un automātiski izstieptu un samazinātu izmēru, pievienojot jaunas pilsētas vai dzēšot tās.
Mums būs nepieciešamas divas iebūvētas Excel funkcijas, kas pieejamas jebkurā versijā − POICPOZ (MATCH) lai noteiktu diapazona pēdējo šūnu, un INDEX (INDEX) lai izveidotu dinamisku saiti.
Pēdējās šūnas atrašana, izmantojot MATCH
ATBILST(meklēšanas_vērtība, diapazons, atbilstības_veids) – funkcija, kas meklē noteiktu vērtību diapazonā (rindā vai kolonnā) un atgriež tās šūnas kārtas numuru, kurā tā tika atrasta. Piemēram, formula MATCH(“Marts”;A1:A5;0) rezultātā atgriezīs skaitli 4, jo vārds “Marts” atrodas kolonnas A1:A5 ceturtajā šūnā. Pēdējais funkcijas arguments Match_Type = 0 nozīmē, ka mēs meklējam precīzu atbilstību. Ja šis arguments nav norādīts, funkcija pārslēgsies uz tuvākās mazākās vērtības meklēšanas režīmu – tieši to var veiksmīgi izmantot, lai atrastu pēdējo aizņemto šūnu mūsu masīvā.
Trika būtība ir vienkārša. MATCH meklē šūnas diapazonā no augšas uz leju, un teorētiski tai jāapstājas, kad tiek atrasta norādītajai vērtībai tuvākā mazākā vērtība. Ja kā vēlamo vērtību norādāt vērtību, kas acīmredzami ir lielāka par jebkuru tabulā pieejamo vērtību, tad MATCH sasniegs tabulas pašās beigas, neko neatradīs un norādīs pēdējās aizpildītās šūnas kārtas numuru. Un mums to vajag!
Ja mūsu masīvā ir tikai skaitļi, tad kā vēlamo vērtību varam norādīt skaitli, kas acīmredzami ir lielāks par jebkuru no tabulā norādītajiem:
Garantijai var izmantot skaitli 9E + 307 (9 reizes 10 līdz 307 pakāpei, ti, 9 ar 307 nullēm) – maksimālais skaitlis, ar kādu Excel principā var strādāt.
Ja mūsu kolonnā ir teksta vērtības, tad kā ekvivalentu lielākajam iespējamajam skaitlim var ievietot konstrukciju REPEAT(“i”, 255) – teksta virkni, kas sastāv no 255 burtiem “i” – pēdējā burta alfabēts. Tā kā Excel faktiski salīdzina rakstzīmju kodus, veicot meklēšanu, jebkurš mūsu tabulas teksts tehniski būs “mazāks” nekā tik gara “yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy” rinda:
Ģenerējiet saiti, izmantojot INDEX
Tagad, kad mēs zinām pēdējā netukšā elementa pozīciju tabulā, atliek izveidot saiti uz visu mūsu diapazonu. Šim nolūkam mēs izmantojam funkciju:
INDEKSS(diapazons; rindas_numurs; kolonnas_numurs)
Tas dod šūnas saturu no diapazona pēc rindas un kolonnas numura, proti, piemēram, funkcija =INDEX(A1:D5;3;4) mūsu tabulā ar pilsētām un mēnešiem no iepriekšējās metodes dos 1240 – saturu. no 3. rindas un 4. kolonnas, ti, šūnas D3. Ja ir tikai viena kolonna, tad tās numuru var izlaist, ti, formula INDEX(A2:A6;3) pēdējā ekrānuzņēmumā dos “Samara”.
Un ir viena ne gluži acīmredzama nianse: ja INDEKSS netiek vienkārši ievadīts šūnā aiz = zīmes, kā parasti, bet tiek izmantots kā pēdējā atsauces daļa uz diapazonu aiz kola, tad tas vairs neizdodas. šūnas saturu, bet tās adresi! Tādējādi tāda formula kā $A$2:INDEX($A$2:$A$100;3) izvadā sniegs atsauci uz diapazonu A2:A4.
Un šeit parādās funkcija MATCH, ko ievietojam INDEX, lai dinamiski noteiktu saraksta beigas:
=$A$2:INDEX($A$2:$A$100; ATBILST(REP("I";255);A2:A100))
Izveidojiet nosauktu diapazonu
Atliek to visu salikt vienā veselumā. Atveriet cilni formula (Formulas) Un noklikšķiniet uz Nosaukuma vadītājs (Vārdu pārvaldnieks). Atvērtajā logā noklikšķiniet uz pogas izveidot (jauns), ievadiet laukā mūsu diapazona nosaukumu un formulu Diapazons (Atsauce):
Atliek noklikšķināt OK un gatavu diapazonu var izmantot jebkurā formulā, nolaižamajos sarakstos vai diagrammās.
- Izmantojot funkciju VLOOKUP, lai saistītu tabulas un uzmeklēšanas vērtības
- Kā izveidot automātiski aizpildošu nolaižamo sarakstu
- Kā izveidot rakurstabulu, lai analizētu lielu datu apjomu