Exceli arvutustabelid sisaldavad sageli lahtrisse rippmenüüd andmete sisestamise lihtsustamiseks ja / või standardimiseks. Need rippmenüüd on loodud andmete valideerimise funktsiooni abil, et määrata lubatud kirjete loend.
Lihtsa rippmenüü seadistamiseks valige lahtr, kuhu andmed sisestatakse, seejärel klõpsake vahekaardil Andmed valideerimine (vahekaardil Andmed ), valige Andmete valideerimine, valige loend (Luba :) ja sisestage seejärel loendiüksused (komadega eraldatud) ) väljal Source : (vt joonis 1).
Sellist tüüpi rippmenüüst on lubatud valikute loetelu täpsustatud andmete valideerimisel; seetõttu peab kasutaja loendi muutmiseks avama ja muutma andmete valideerimist. See võib aga olla kogenematu kasutajate jaoks keeruline või juhul, kui valikute nimekiri on pikk.
Teine võimalus on paigutada loend tabelisse nimepiirkonda ja seejärel määrata selle valideerimise alale (see on näidatud joonisel 2) selle vahemiku nime (võrdne tähisega).
See teine meetod lihtsustab loendi valikute muutmist, kuid üksuste lisamine või eemaldamine võib olla problemaatiline. Kuna mainitud vahemik (FruitChoices, meie näites) viitab fikseeritud rakupiirkonnale ($ H $ 3: $ H $ 10, nagu näidatud), kui lahtritesse H11 või alla lisatakse rohkem valikuid, ei kuvata neid rippmenüüst (kuna need rakud ei kuulu FruitChoices'i vahemikku).
Samamoodi, kui näiteks Pears ja Strawberries kirjed kustutatakse, ei kuvata neid enam rippmenüüst, kuid rippmenüüst on kaks „tühja“ valikut, kuna rippmenüü viitab kogu FruitChoices vahemikule, kaasa arvatud tühjad lahtrid H9 ja H10.
Nendel põhjustel, kui kasutate rippmenüüst tavapärast nimesagedust nimekirjaallikana, tuleb nimesarja ise muuta, et lisada rohkem või vähem rakke, kui kanded lisatakse nimekirjast või kustutatakse.
Selle probleemi lahenduseks on kasutada rippmenüüde allikana dünaamilise vahemiku nime. Dünaamilise vahemiku nimi on selline, mis automaatselt laieneb (või lepingud), et täpselt vastata andmeploki suurusele, kuna kanne lisatakse või eemaldatakse. Selleks kasutage nimiväärtuse määramiseks valemi asemel kindlat vahemikku.
Dünaamilise vahemiku seadistamine Excelis
Tavaline (staatiline) vahemiku nimi viitab teatud rakkude vahemikule ($ H $ 3: $ H $ 10 meie näites, vt allpool):
Kuid dünaamiline vahemik on määratletud valemiga (vt allpool, võetud eraldi tabelist, mis kasutab dünaamilisi vahemiku nimesid):
Enne alustamist veenduge, et laadite alla meie Exceli näidisfaili (sortige makroid on keelatud).
Vaatleme seda valemit üksikasjalikult. Puuviljade valikud on lahtri plokis, mis on otseselt rubriigi all ( FRUITS ). Sellele pealkirjale on määratud ka nimi: FruitsHeading :
Kogu valem, mida kasutatakse puuviljade valiku dünaamilise vahemiku määratlemiseks, on järgmine:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX) (ISBLANK (OFFSET (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
FruitsHeading viitab rubriigile, mis on ühe rea ees nimekirjas oleva esimese kirje kohal. Number 20 (kasutatakse kaks korda valemis) on loendi maksimaalne suurus (ridade arv) (seda saab vastavalt soovile reguleerida).
Pange tähele, et selles näites on nimekirjas ainult 8 kirjet, kuid nende all on ka tühjad lahtrid, kuhu võib lisada täiendavaid kirjeid. Number 20 viitab kogu plokile, kuhu saab teha kirjeid, mitte tegelikku kirjete arvu.
Nüüd lõhkume valem valemitesse (iga värvi kodeerimine), et mõista, kuidas see toimib:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX) (ISBLANK ( OFFSET (FruitsHeading, 1, 0, 20, 1) ), 0, 0), 0) -1, 20), 1)
" Sisemine " tükk on OFFSET (FruitsHeading, 1, 0, 20, 1) . See viitab 20 raku plokile (puuviljajälje all), kus võib sisestada valikuid. See OFFSET funktsioon ütleb põhimõtteliselt: Alusta FruitsHeading rakust, minge alla 1 rida ja üle 0 veeru, seejärel valige ala, mis on 20 rida pikk ja 1 veerg lai. Nii et see annab meile 20-rea ploki, kus sisestatakse puuviljavalikud.
Järgmine valem on ISBLANKi funktsioon:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX ( ISBLANK (eespool), 0, 0), 0) -1, 20), 1)
Siin on OFFSET-funktsioon (mida on selgitatud ülalpool) asendatud sõnaga „eespool” (selleks, et asju lihtsamini lugeda). Kuid ISBLANKi funktsioon töötab 20-reaga reas, mida OFFSET-funktsioon määratleb.
Seejärel loob ISBLANK 20 TRUE- ja FALSE-väärtuste kogumi, mis näitab, kas iga 20-rida vahemiku OFFSET-funktsiooniga seotud lahtri tühi (tühi) või mitte. Selles näites on komplekti esimesed 8 väärtust FALSE, kuna esimesed 8 rakku ei ole tühjad ja viimased 12 väärtust TRUE.
Järgmine valem on INDEX-funktsioon:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (eespool, 0, 0), 0) -1, 20), 1)
Jällegi viitab "ülalpool" eespool kirjeldatud ISBLANK ja OFFSET funktsioonidele. INDEX funktsioon tagastab massiivi, mis sisaldab 20 TRUE / FALSE väärtust, mis on loodud ISBLANKi funktsiooniga.
Indeksit kasutatakse tavaliselt kindla väärtuse (või väärtuste vahemiku) valimiseks andmeplokist, täpsustades teatud rea ja veeru (selle ploki sees). Kuid rea ja veeru sisendite nulli seadmine (nagu siin tehakse) põhjustab INDEXi kogu andmeploki sisaldava massiivi tagastamise.
Järgmine valem on MATCH-funktsioon:
= OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (TRUE, ülaltoodud, 0) -1, 20), 1)
Funktsioon MATCH tagastab esimese TRUE väärtuse positsiooni massiivis, mille INDEX funktsioon on tagastanud. Kuna loendi esimesed 8 kirjet ei ole tühjad, on massiivi esimesed 8 väärtust FALSE ja üheksas väärtus TRUE (kuna 9. rida on tühi).
Nii tagastab MATCH-funktsioon väärtuse 9 . Sellisel juhul tahame siiski teada, kui palju kirjeid on nimekirjas, seega lahutab valem 1 MATCH-väärtusest (mis annab viimase kirje positsiooni). Lõpuks tagastab MATCH (TRUE, ülaltoodud, 0) -1 väärtuse 8 .
Järgmine valem on IFERRORi funktsioon:
= OFFSET (FruitsHeading, 1, 0, IFERROR (eespool, 20), 1)
IFERRORi funktsioon tagastab alternatiivse väärtuse, kui esimene määratud väärtus annab vea. See funktsioon on lisatud, sest kui kogu rakkude plokk (kõik 20 rida) on kirjetega täidetud, siis MATCH-funktsioon tagastab vea.
Seda seetõttu, et me ütleme MATCH-funktsiooni otsima esimest TRUE-väärtust (ISBLANK-funktsiooni väärtuste massiivis), kuid kui ükski rakkudest ei ole tühi, siis täidetakse kogu massiiv FALSE-väärtustega. Kui MATCH ei leia otsitavast väärtusest (TRUE), mida ta otsib, tagastab see vea.
Seega, kui kogu loend on täis (ja seetõttu MATCH tagastab vea), siis IFERRORi funktsioon tagastab selle asemel väärtuse 20 (teades, et nimekirjas peab olema 20 kirjet).
Lõpuks tagastab OFFSET (FruitsHeading, 1, 0, ülaltoodud, 1) tulemuste vahemiku, mida me tegelikult otsime: Alusta FruitsHeading rakust, mine alla 1 rida ja üle 0 veeru, seejärel valige ala, mis on siiski palju ridu, kui nimekirjas on kirjed (ja 1 veerg lai). Nii saadab kogu valem koos vahemiku, mis sisaldab ainult tegelikke kirjeid (kuni esimese tühja lahtri juurde).
Kasutades seda valemit rippmenüü allikaks oleva vahemiku määratlemiseks, saate seda nimekirja vabalt redigeerida (kirjete lisamine või eemaldamine, kui ülejäänud kirjed algavad ülemisest lahtrist ja on külgnevad) ja rippmenüü kajastab alati praegust (vt joonis 6).
Siin kasutatav näitefail (dünaamilised loendid) on sellel veebilehel allalaaditav. Makrod ei tööta, sest WordPress ei meeldi makroid sisaldavate Exceli raamatute jaoks.
Alternatiivina loendiplokis olevate ridade arvu määramisele võib loendiplokile omistada oma vahemiku nime, mida saab seejärel kasutada modifitseeritud valemis. Näitefailis kasutab teine meetod (Nimed) seda meetodit. Siin on kogu loendiplokk (pealkirja „NAMES” all, 40 faili rida näitefailis ) määratud NameBlocki vahemiku nimega. Nimede loendi määratlemiseks kasutatav alternatiivne valem on siis:
= OFFSET (nimedHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock) ), 1)
kus NamesBlock asendab OFFSET (FruitsHeading, 1, 0, 20, 1) ja ROWS (NamesBlock) asendab 20 (ridade arvu) eelmises valemis.
Niisiis, proovige kasutada dünaamilise vahemiku nimesid rippmenüüde puhul, mida saab hõlpsasti redigeerida (ka teised kasutajad, kellel ei ole kogemusi)! Pange tähele, et kuigi see artikkel on keskendunud rippmenüüdele, saab dünaamilise vahemiku nimesid kasutada kõikjal, kus peate viitama vahemikule või loetelule, mis võib erineda. Naudi!
