Turinys
Ar turite Excel lentelių su duomenimis, kurių dydį galima keisti, ty eilučių (stulpelių) skaičius darbo eigoje gali padidėti arba mažėti? Jei lentelės dydžiai „plaukioja“, turėsite nuolat stebėti šį momentą ir taisyti:
- nuorodos ataskaitų formulėse, kurios nurodo mūsų lentelę
- pradiniai sukamųjų lentelių diapazonai, sukurti pagal mūsų lentelę
- pradiniai diagramų diapazonai, sudaryti pagal mūsų lentelę
- diapazonai išskleidžiamiesiems meniu, kuriuose mūsų lentelė naudojama kaip duomenų šaltinis
Visa tai iš viso neleis nuobodžiauti 😉
Daug patogiau ir teisingiau bus sukurti dinaminį „guminį“ diapazoną, kurio dydis automatiškai prisitaikys prie faktinio duomenų eilučių ir stulpelių skaičiaus. Norėdami tai įgyvendinti, yra keletas būdų.
1 būdas. Išmanusis stalas
Pažymėkite langelių diapazoną ir pasirinkite iš skirtuko Pagrindinis puslapis – formatuoti kaip lentelę (pradžia – formatuoti kaip lentelę):
Jei jums nereikia dryžuoto dizaino, kuris pridedamas prie lentelės kaip šalutinis poveikis, galite jį išjungti pasirodžiusiame skirtuke. Konstruktorius (dizainas). Kiekviena tokiu būdu sukurta lentelė gauna pavadinimą, kurį galima pakeisti patogesniu toje pačioje skirtuko vietoje Konstruktorius (dizainas) srityje Lentelės pavadinimas (Lentelės pavadinimas).
Dabar galime naudoti dinamines nuorodas į mūsų „išmaniąją lentelę“:
- Lentelė 1 – nuoroda į visą lentelę, išskyrus antraštės eilutę (A2:D5)
- 1 lentelė[#Visi] – nuoroda į visą lentelę (A1:D5)
- 1 lentelė [Petras] – nuoroda į diapazono stulpelį be pirmosios langelio antraštės (C2:C5)
- 1 lentelė[#Headers] – nuoroda į „antraštę“ su stulpelių pavadinimais (A1:D1)
Tokios nuorodos puikiai veikia formulėse, pavyzdžiui:
= SUM (1 lentelė [Maskva]) – sumos apskaičiavimas stulpeliui „Maskva“
or
=VPR(F5;Lentelė 1;3;0) – ieškokite mėnesio lentelėje iš langelio F5 ir už tai išrašykite Sankt Peterburgo sumą (kas yra VLOOKUP?)
Tokios nuorodos gali būti sėkmingai naudojamos kuriant suvestinės lenteles, pasirinkus skirtuke Įterpimas – Suvestinė lentelė (Įterpimas – Suvestinė lentelė) ir įvesdami išmaniosios lentelės pavadinimą kaip duomenų šaltinį:
Jei pasirinksite tokios lentelės fragmentą (pavyzdžiui, pirmus du stulpelius) ir sukursite bet kokio tipo diagramą, tada, pridedant naujas eilutes, jos bus automatiškai įtrauktos į diagramą.
Kuriant išskleidžiamuosius sąrašus negalima naudoti tiesioginių nuorodų į išmaniosios lentelės elementus, tačiau šį apribojimą galite lengvai apeiti naudodami taktinį triuką – naudokite funkciją NETIESIOGINĖS (NETIESIOGINIS), kuri paverčia tekstą nuoroda:
Tie. nuoroda į išmaniąją lentelę teksto eilutės pavidalu (kabutėse!) virsta visaverte nuoroda, o išskleidžiamasis sąrašas paprastai tai suvokia.
2 metodas: dinaminis pavadintas diapazonas
Jei duomenų paversti išmaniąja lentele dėl kokių nors priežasčių nepageidautina, galite naudoti šiek tiek sudėtingesnį, bet daug subtilesnį ir universalesnį metodą – programoje Excel sukurkite dinaminį vardinį diapazoną, nurodantį mūsų lentelę. Tada, kaip ir išmaniosios lentelės atveju, sukurto diapazono pavadinimą galite laisvai naudoti bet kokiose formulėse, ataskaitose, diagramose ir pan. Pradėkime nuo paprasto pavyzdžio:
užduotis: sukurkite dinaminį pavadinimo diapazoną, kuris būtų susijęs su miestų sąrašu ir automatiškai išplečiamas bei mažėja, kai pridedami nauji miestai arba juos ištrinami.
Mums reikės dviejų integruotų „Excel“ funkcijų, kurias galima rasti bet kurioje versijoje − POICPOZ (MATCH) nustatyti paskutinę diapazono langelį ir RODYKLĖ (INDEKSAS) dinaminiam ryšiui sukurti.
Paskutinio langelio paieška naudojant MATCH
ATITIKTIS(paieškos_vertė, diapazonas, atitikties_tipas) – funkcija, kuri ieško nurodytos reikšmės diapazone (eilutė arba stulpelis) ir grąžina langelio, kuriame ji buvo rasta, eilės numerį. Pavyzdžiui, formulė MATCH(“kovas”;A1:A5;0) grąžins skaičių 4, nes žodis “kovas” yra ketvirtame stulpelio A1:A5 langelyje. Paskutinis funkcijos argumentas Match_Type = 0 reiškia, kad ieškome tikslios atitikties. Jei šis argumentas nenurodytas, funkcija persijungs į artimiausios mažiausios reikšmės paieškos režimą – būtent tai galima sėkmingai panaudoti ieškant paskutinę užimtą langelį mūsų masyve.
Triuko esmė paprasta. MATCH ieško langelių diapazone nuo viršaus iki apačios ir teoriškai turėtų sustoti, kai randa artimiausią mažiausią reikšmę. Jei kaip norimą reikšmę nurodysite vertę, kuri akivaizdžiai didesnė nei bet kuri lentelėje, tada MATCH pasieks pačią lentelės pabaigą, nieko neras ir suteiks paskutinio užpildyto langelio eilės numerį. Ir mums to reikia!
Jei mūsų masyve yra tik skaičiai, tada kaip norimą reikšmę galime nurodyti skaičių, kuris akivaizdžiai yra didesnis nei bet kuris iš lentelėje pateiktų:
Garantijai galite naudoti skaičių 9E + 307 (9 kartus 10 iki 307 laipsnio, ty 9 su 307 nuliais) – maksimalų skaičių, su kuriuo Excel gali dirbti iš esmės.
Jei mūsų stulpelyje yra tekstinių reikšmių, tada kaip didžiausio įmanomo skaičiaus atitikmenį galite įterpti konstrukciją REPEAT("i", 255) – teksto eilutę, susidedančią iš 255 raidžių "i" - paskutinė raidė abėcėlė. Kadangi „Excel“ ieškodama iš tikrųjų lygina simbolių kodus, bet koks tekstas mūsų lentelėje techniškai bus „mažesnis“ nei tokia ilga „yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy“ eilutė:
Sugeneruokite nuorodą naudodami INDEX
Dabar, kai žinome paskutinio netuščio elemento padėtį lentelėje, belieka suformuoti nuorodą į visą mūsų diapazoną. Tam naudojame funkciją:
INDEX(diapazonas; eilutės_skaičius; stulpelio_skaičius)
Jis pateikia langelio turinį iš diapazono pagal eilutės ir stulpelio numerius, ty, pavyzdžiui, funkcija =INDEX(A1:D5;3;4) mūsų lentelėje su miestais ir mėnesiais iš ankstesnio metodo duos 1240 – turinį. iš 3 eilutės ir 4 stulpelio, ty langelių D3. Jei yra tik vienas stulpelis, jo numerio galima praleisti, ty formulė INDEX(A2:A6;3) paskutinėje ekrano kopijoje duos „Samara“.
Ir yra vienas ne visai akivaizdus niuansas: jei INDEX ne tik įvedamas į langelį po = ženklo, kaip įprasta, bet naudojamas kaip paskutinė nuorodos į diapazoną dalis po dvitaškio, tada jis nebeišduoda langelio turinys, bet jo adresas! Taigi tokia formulė kaip $A$2:INDEX($A$2:$A$100;3) išvestyje pateiks nuorodą į diapazoną A2:A4.
Ir čia atsiranda funkcija MATCH, kurią įterpiame į INDEX, kad dinamiškai nustatytų sąrašo pabaigą:
=$A$2:INDEX($A$2:$100; MATCH(REP("I";255);A2:A100))
Sukurkite pavadintą diapazoną
Belieka viską supakuoti į vieną visumą. Atidarykite skirtuką formulė (Formulės) Ir spustelėkite Vardų tvarkytuvė (Vardų vadybininkas). Atsidariusiame lange spustelėkite mygtuką kurti (nauja), lauke įveskite diapazono pavadinimą ir formulę Diapazonas (Nuoroda):
Belieka spustelėti OK ir paruoštas diapazonas gali būti naudojamas bet kokiose formulėse, išskleidžiamuose sąrašuose ar diagramose.
- Funkcijos VLOOKUP naudojimas lentelėms ir paieškos reikšmėms susieti
- Kaip sukurti automatiškai užpildomą išskleidžiamąjį sąrašą
- Kaip sukurti suvestinę lentelę dideliam duomenų kiekiui analizuoti