„Excel“ skaičiuoklėse dažnai yra langelių išskleidžiamajame sąraše, kad supaprastinti ir (arba) standartizuoti duomenų įvedimą. Šie išskleidžiamieji meniu yra sukurti naudojant duomenų patvirtinimo funkciją, kad būtų galima nustatyti leistinų įrašų sąrašą.
Jei norite nustatyti paprastą išskleidžiamąjį sąrašą, pasirinkite langelį, kuriame bus įvesti duomenys, tada spustelėkite Duomenų patvirtinimas (skirtuke Duomenys ), pasirinkite Duomenų patvirtinimas, pasirinkite Sąrašas (po Leisti :) ir tada įveskite sąrašo elementus (atskirtus kableliais ) lauke Source : (žr. 1 pav.).
Šio tipo pagrindiniame išskleidžiamajame sąraše leistinų įrašų sąrašas nurodomas pačiame duomenų patvirtinime; todėl, norint atlikti sąrašo pakeitimus, naudotojas turi atidaryti ir redaguoti duomenų patvirtinimą. Tačiau tai gali būti sunku nepatyrusiems naudotojams arba tais atvejais, kai pasirinkimų sąrašas yra ilgas.
Kitas variantas yra įterpti sąrašą į nurodytą intervalą skaičiuoklėje ir tada nurodyti duomenų diapazono pavadinimą (nurodytą lygiaverte ženklu) duomenų šaltinio lauke Source : (kaip parodyta 2 paveiksle).
Šis antrasis metodas palengvina sąrašo parinkčių redagavimą, tačiau elementų pridėjimas arba pašalinimas gali būti problemiškas. Kadangi pavadintas diapazonas („FruitChoices“, mūsų pavyzdyje) reiškia fiksuotą ląstelių diapazoną ($ H $ 3: $ H $ 10, kaip parodyta), jei prie ląstelių H11 ar žemiau yra daugiau pasirinkimų, jie nebus rodomi išskleidžiamajame (kadangi šios ląstelės nėra „FruitChoices“ grupės dalis).
Panašiai, jei, pvz., Ištrinami „Kriaušės ir braškės“ įrašai, jie nebebus rodomi išskleidžiamajame sąraše, bet išplečiamajame sąraše bus du „tuščios“ pasirinkimai, nes išskleidžiamajame lange vis dar nurodoma visa „FruitChoices“ sritis, įskaitant tuščias ląsteles H9 ir H10.
Dėl šių priežasčių, naudojant įprastą pavadinimo diapazoną kaip sąrašo šaltinį išskleidžiamam sąrašui, pats pavadintas diapazonas turi būti redaguojamas, kad įtrauktų daugiau ar mažiau ląstelių, jei įrašai yra įtraukiami arba ištrinami iš sąrašo.
Šios problemos sprendimas yra naudoti dinaminio diapazono pavadinimą kaip išskleidžiamojo pasirinkimo šaltinį. Dinaminis diapazono pavadinimas yra tas, kuris automatiškai išsiplečia (arba sutarčių), kad tiksliai atitiktų duomenų bloko dydį, nes įrašai pridedami arba pašalinti. Jei norite tai padaryti, naudokite formulę, o ne fiksuotą ląstelių adresų diapazoną, kad apibrėžtumėte nurodytą diapazoną.
Kaip nustatyti dinaminį diapazoną programoje „Excel“
Įprastas (statinis) diapazono pavadinimas reiškia nurodytą ląstelių diapazoną ($ H $ 3: $ H $ 10 mūsų pavyzdyje, žr. Toliau):
Tačiau dinaminis diapazonas apibrėžiamas naudojant formulę (žr. Toliau, paimtas iš atskiros skaičiuoklės, kurioje naudojami dinaminiai diapazono pavadinimai):
Prieš pradėdami, įsitikinkite, kad atsisiunčiate „Excel“ pavyzdinį failą (rūšiuoti makrokomandas buvo išjungtas).
Išsiaiškinkime šią formulę. Vaisių pasirinkimas yra ląstelių bloke, esančiose tiesiai po antrašte ( FRUITS ). Antraštė taip pat priskiriama pavadinimui: „ Vaisiai“ .
Visa formulė, naudojama norint nustatyti vaisių pasirinkimo dinaminį diapazoną, yra:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX) (ISBLANK (OFFSET (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
VaisiaiPagalba nurodo antraštę, kuri yra viena eilutė virš pirmojo sąrašo įrašo. Skaičius 20 (naudojamas du kartus formulėje) yra didžiausias sąrašo dydis (eilučių skaičius) (tai galima reguliuoti pagal pageidavimą).
Atkreipkite dėmesį, kad šiame pavyzdyje sąraše yra tik 8 įrašai, tačiau taip pat yra tuščių langelių, kuriuose gali būti papildomų įrašų. Numeris 20 reiškia visą bloką, kuriame galima įrašyti įrašus, o ne faktinį įrašų skaičių.
Dabar suskirstykite formulę į gabalus (kiekvienos detalės spalvos kodavimas), kad suprastume, kaip jis veikia:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX) (ISBLANK ( OFFSET (FruitsHeading, 1, 0, 20, 1) ), 0, 0), 0) -1, 20), 1)
„ Vidinis “ gabalas yra OFFSET (FruitsHeading, 1, 0, 20, 1) . Tai nurodo 20 ląstelių bloką (po vaisių viršūnės langeliu), kur galima įvesti pasirinkimus. Ši „OFFSET“ funkcija iš esmės sako: Pradėkite nuo „ FruitsHeading“ langelio, eikite žemyn 1 eilute ir daugiau nei 0 stulpelių, tada pasirinkite plotą, kuris yra 20 eilučių ilgio ir 1 stulpelio pločio. Tai suteikia mums 20 eilių bloką, kuriame įvedami vaisių pasirinkimai.
Kitas formulės gabalas yra ISBLANK funkcija:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX) ( ISBLANK (aukščiau), 0, 0), 0) -1, 20), 1)
Čia OFFSET funkcija (paaiškinta aukščiau) buvo pakeista „aukščiau“ (kad būtų lengviau skaityti). Tačiau ISBLANK funkcija veikia 20 eilių diapazone, kurį apibrėžia OFFSET funkcija.
Tada ISBLANK sukuria 20 TRUE ir FALSE reikšmių rinkinį, nurodant, ar kiekviena iš atskirų elementų 20 eilių diapazone, nurodyta OFFSET funkcija, yra tuščia (tuščia) arba ne. Šiame pavyzdyje pirmosios 8 vertės rinkinyje bus FALSE, nes pirmosios 8 ląstelės nėra tuščios ir paskutinės 12 reikšmių bus TRUE.
Kitas formulės gabalas yra INDEX funkcija:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (aukščiau, 0, 0), 0) -1, 20), 1)
Vėlgi „aukščiau“ reiškia anksčiau aprašytas ISBLANK ir OFFSET funkcijas. INDEX funkcija grąžina masyvą, kuriame yra 20 TRUE / FALSE reikšmių, sukurtų ISBLANK funkcijos.
INDEX paprastai naudojamas tam tikros vertės (arba reikšmių diapazono) iš duomenų bloko pasirinkimui, nurodant tam tikrą eilutę ir stulpelį (toje bloke). Bet eilutės ir stulpelio įvesties nustatymas į nulį (kaip tai daroma) INDEX grąžina masyvą, kuriame yra visas duomenų blokas.
Kitas formulės gabalas yra MATCH funkcija:
= OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (TRUE, aukščiau, 0) -1, 20), 1)
Funkcija MATCH grąžina pirmosios TRUE reikšmės masyvą, kurį grąžina INDEX funkcija. Kadangi pirmieji 8 įrašai sąraše yra neužpildyti, pirmosios 8 matricos reikšmės bus FALSE, o devintoji vertė bus TRUE (nuo 9 eilutės tuščios).
Taigi MATCH funkcija grąžins 9 vertę. Tačiau šiuo atveju mes tikrai norime žinoti, kiek įrašų yra sąraše, todėl formulė atima 1 iš MATCH vertės (kuri suteikia paskutinio įrašo poziciją). Taigi galiausiai MATCH (TRUE, aukščiau, 0) -1 grąžina 8 vertę.
Kitas formulės gabalas yra IFERROR funkcija:
= OFFSET (FruitsHeading, 1, 0, IFERROR (aukščiau, 20), 1)
IFERROR funkcija grąžina alternatyvią vertę, jei pirmoji nurodyta vertė sukelia klaidą. Ši funkcija įtraukta, nes jei visas langelių blokas (visos 20 eilučių) yra užpildytas įrašais, MATCH funkcija grąžins klaidą.
Taip yra todėl, kad mes nurodome MATCH funkcijai ieškoti pirmojo TRUE reikšmės (ISBLANK funkcijos reikšmių grupėje), bet jei NAKTAS iš langelių yra tuščias, visa masyvas bus užpildytas FALSE reikšmėmis. Jei MATCH neranda tikslinės reikšmės (TRUE), kurią ji ieško, ji grąžina klaidą.
Taigi, jei visas sąrašas yra pilnas (ir todėl MATCH grąžina klaidą), IFERROR funkcija grąžins 20 reikšmę (žinant, kad sąraše turi būti 20 įrašų).
Galiausiai, OFFSET (FruitsHeading, 1, 0, aukščiau, 1) grąžina diapazoną, kurio mes iš tikrųjų ieškome: Pradėkite nuo „FruitsHeading“ langelio, eikite 1 eilutėje ir daugiau nei 0 stulpelių, tada pasirinkite sritį, kuri vis dėlto yra daug eilučių sąraše yra įrašų (ir 1 stulpelis pločio). Taigi visa formulė kartu grąžins diapazoną, kuriame yra tik faktiniai įrašai (iki pirmojo tuščio langelio).
Naudojant šią formulę, norėdami nustatyti diapazoną, kuris yra išskleidžiamojo šaltinio šaltinis, galite laisvai redaguoti sąrašą (įtraukti arba pašalinti įrašus, kol likusieji įrašai prasideda viršutiniame langelyje ir yra gretimi) ir išskleidžiamajame sąraše visada atsispindės dabartinė sąrašą (žr. 6 pav.).
Čia naudojamas pavyzdinis failas (dinaminiai sąrašai) yra įtrauktas ir yra parsisiunčiamas iš šios svetainės. Tačiau makrokomandos neveikia, nes „WordPress“ nemėgsta „Excel“ knygų su makrokomandomis.
Kaip alternatyvą sąrašų bloko eilių skaičiaus nustatymui, sąrašo blokas gali būti priskirtas savo diapazono pavadinimui, kuris vėliau gali būti naudojamas modifikuotoje formulėje. Pavyzdiniame faile antrasis sąrašas (pavadinimai) naudoja šį metodą. Čia visam sąrašo blokui (po antrašte „NAMES“, 40 eilučių pavyzdžio faile) priskiriamas „ NameBlock“ intervalo pavadinimas. Tada alternatyvi NamesList apibrėžimo formulė yra:
= OFFSET (pavadinimaiHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock) ), 1)
kur „ NamesBlock“ pakeičia „OFFSET“ („FruitsHeading“, 1, 0, 20, 1) ir „ ROWS“ („NamesBlock“) pakeičia 20 (eilių) skaičių ankstesnėje formulėje.
Taigi, išskleidžiamuosiuose sąrašuose, kuriuos galima lengvai redaguoti (įskaitant kitus, nepatyrusius naudotojus), pabandykite naudoti dinaminius diapazono pavadinimus! Atkreipkite dėmesį, kad nors šis straipsnis buvo sutelktas į išskleidžiamuosius sąrašus, dinaminiai diapazono pavadinimai gali būti naudojami bet kur, kur reikia nurodyti diapazoną ar sąrašą, kuris gali skirtis. Mėgautis!
