Priklausomo išskleidžiamojo sąrašo kūrimas „Excel“ (žingsnis po žingsnio pamoka)

Žiūrėti vaizdo įrašą - priklausomo išskleidžiamojo sąrašo kūrimas „Excel“

„Excel“ išskleidžiamasis sąrašas yra naudinga funkcija kuriant duomenų įvedimo formas arba „Excel“ informacijos suvestines.

Jame elementų sąrašas rodomas kaip langelio išskleidžiamasis meniu, o vartotojas gali pasirinkti iš išskleidžiamojo meniu. Tai gali būti naudinga, kai turite sąrašą pavadinimų, produktų ar regionų, kuriuos dažnai reikia įvesti į langelių rinkinį.

Žemiau yra „Excel“ išskleidžiamojo sąrašo pavyzdys:

Pirmiau pateiktame pavyzdyje aš naudoju A2: A6 elementus, kad sukurtumėte išskleidžiamąjį meniu C3.

Skaityti: Čia yra išsamus vadovas, kaip sukurti „Excel“ išskleidžiamąjį sąrašą.

Tačiau kartais „Excel“ galite naudoti daugiau nei vieną išskleidžiamąjį sąrašą, kad antrame išskleidžiamajame sąraše esantys elementai priklausytų nuo pirmojo išskleidžiamojo sąrašo pasirinkimo.

„Excel“ jie vadinami priklausomais išskleidžiamaisiais sąrašais.

Žemiau yra pavyzdys, ką turiu omenyje turėdamas priklausomą išskleidžiamąjį „Excel“ sąrašą:

Matote, kad 2 išskleidžiamojo meniu parinktys priklauso nuo pasirinkimo, atlikto 1 išskleidžiamajame meniu. Jei 1 išskleidžiamajame meniu pasirenku „Vaisiai“, man rodomi vaisių pavadinimai, tačiau jei išskleidžiamajame meniu 1 pasirenku daržoves, tada aš „2“ išskleidžiamajame meniu rodomi daržovių pavadinimai.

Tai vadinama sąlyginiu arba priklausomu „Excel“ išskleidžiamuoju sąrašu.

Priklausomo išskleidžiamojo sąrašo kūrimas „Excel“

Štai „Excel“ priklausomo išskleidžiamojo sąrašo sukūrimo veiksmai:

• Pasirinkite langelį, kuriame norite pirmą (pagrindinį) išskleidžiamąjį sąrašą.
• Eikite į Duomenys -> Duomenų patvirtinimas. Bus atidarytas duomenų patvirtinimo dialogo langas.
• Duomenų tikrinimo dialogo lango nustatymų skirtuke pasirinkite Sąrašas.
• Lauke Šaltinis nurodykite diapazoną, kuriame yra elementai, kurie turi būti rodomi pirmajame išskleidžiamajame sąraše.
• Spustelėkite Gerai. Tai sukurs išskleidžiamąjį meniu 1.
• Pasirinkite visą duomenų rinkinį (šiame pavyzdyje A1: B6).
• Eikite į Formulės -> Apibrėžti vardai -> Sukurti iš pasirinkimo (arba galite naudoti sparčiuosius klavišus „Control“ + „Shift“ + F3).
• Dialogo lange „Sukurti pavadinimą iš pasirinkimo“ pažymėkite viršutinės eilutės parinktį ir panaikinkite visų kitų žymėjimą. Tai padarius sukuriami 2 pavadinimų diapazonai („Vaisiai“ ir „Daržovės“). Vaisiai, pavadinti asortimentu, reiškia visus sąraše esančius vaisius, o daržovių pavadintas asortimentas - visas sąraše esančias daržoves.
• Spustelėkite Gerai.
• Pasirinkite langelį, kuriame norite turėti priklausomą/sąlyginį išskleidžiamąjį sąrašą (šiame pavyzdyje E3).
• Eikite į Duomenys -> Duomenų patvirtinimas.
• Dialogo lange Duomenų tikrinimas nustatymų skirtuke įsitikinkite, kad pasirinktas sąrašas.
• Lauke Šaltinis įveskite formulę = NETIESIOGINĖ (D3). Čia D3 yra langelis, kuriame yra pagrindinis išskleidžiamasis meniu.
• Spustelėkite Gerai.

Dabar, kai pasirenkate 1 išskleidžiamajame meniu, 2 išskleidžiamajame sąraše pateiktos parinktys bus automatiškai atnaujintos.

Atsisiųskite pavyzdinį failą

Kaip tai veikia? - Sąlyginis išskleidžiamasis sąrašas (E3 langelyje) reiškia = NETIESIOGINIS (D3). Tai reiškia, kad kai D3 langelyje pasirenkate „Vaisiai“, E3 išskleidžiamasis sąrašas nurodo pavadintą diapazoną „Vaisiai“ (naudojant funkciją „NETIESIOGINĖ“) ir todėl išvardijami visi tos kategorijos elementai.

Svarbi pastaba: Jei pagrindinė kategorija yra daugiau nei vienas žodis (pvz., „Sezoniniai vaisiai“, o ne „Vaisiai“), tada vietoj paprasta INDIRECT funkcija, parodyta aukščiau.

• Taip yra todėl, kad „Excel“ neleidžia tarpų pavadintuose diapazonuose. Taigi, kai kuriate pavadintą diapazoną naudodami daugiau nei vieną žodį, „Excel“ automatiškai įterpia pabraukimą tarp žodžių. Pvz., Kai kuriate pavadintą diapazoną su „Seasonal Fruits“, jis bus pavadintas „Season_Fruits“. Funkcija PAKEITIMAS naudojant INDIRECT funkciją užtikrina, kad tarpai būtų laisvi yra paversta pabraukomis.

Automatiškai iš naujo nustatyti/išvalyti priklausomo išskleidžiamojo sąrašo turinį

Kai pasirinksite ir pakeisite pirminį išskleidžiamąjį meniu, priklausomas išskleidžiamasis sąrašas nepasikeis ir todėl bus neteisingas įrašas.

Pvz., Jei kaip kategoriją pasirinksite „Vaisiai“, tada kaip elementą pasirinksite „Apple“, o tada grįšite atgal ir pakeisite kategoriją į „Daržovės“, priklausomas išskleidžiamasis meniu ir toliau rodys „Apple“ kaip elementą.

Galite naudoti VBA, kad įsitikintumėte, jog priklausomo išskleidžiamojo sąrašo turinys iš naujo nustatomas, kai keičiamas pagrindinis išskleidžiamasis sąrašas.

Čia yra VBA kodas, skirtas išvalyti priklausomo išskleidžiamojo sąrašo turinį:

Privatus antrinis darbalapio keitimas („ByVal Target“ kaip diapazonas) Klaida Tęskite toliau, jei taikinys. Stulpelis = 4 Tada, jei tikslas. Validation.Type = 3 Tada Application.EnableEvents = False Target.Offset (0, 1). ClearContents End End If End Application.EnableEvents = Tikras išėjimo antrinis pabaiga

Šio kodo nuopelnas priklauso šiai „Debra“ pamokai, kaip išvalyti priklausomus išskleidžiamuosius sąrašus „Excel“, kai pasirinkimas pakeičiamas.

Štai kaip priversti šį kodą veikti:

• Nukopijuokite VBA kodą.
• „Excel“ darbaknygėje, kurioje yra priklausomas išskleidžiamasis sąrašas, eikite į skirtuką Kūrėjas ir grupėje „Kodas“ spustelėkite „Visual Basic“ (taip pat galite naudoti sparčiuosius klavišus - ALT + F11).
• VB redaktoriaus lange, projekto naršyklės kairėje, pamatysite visus darbalapio pavadinimus. Dukart spustelėkite tą, kuriame yra išskleidžiamasis sąrašas.
• Įklijuokite kodą dešinėje esančiame kodo lange.
• Uždarykite VB redaktorių.

Dabar, kai pakeisite pagrindinį išskleidžiamąjį sąrašą, VBA kodas bus suaktyvintas ir bus išvalytas priklausomo išskleidžiamojo sąrašo turinys (kaip parodyta žemiau).

Jei nesate VBA gerbėjas, taip pat galite naudoti paprastą sąlyginio formatavimo triuką, kuris paryškins langelį, kai bus neatitikimų. Tai gali padėti vizualiai pamatyti ir ištaisyti neatitikimą (kaip parodyta žemiau).

Toliau pateikiami žingsniai, kaip išryškinti neatitikimus priklausomuose išskleidžiamuosiuose sąrašuose:

• Pasirinkite langelį, kuriame yra priklausomas (-i) išskleidžiamasis sąrašas (-ai).
• Eikite į pagrindinį puslapį -> Sąlyginis formatavimas -> Nauja taisyklė.
• Dialogo lange Nauja formatavimo taisyklė pasirinkite „Naudokite formulę, kad nustatytumėte, kurias langelius reikia formatuoti“.
• Formulės lauke įveskite šią formulę: = ISERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
• Nustatykite formatą.
• Spustelėkite Gerai.

Formulė naudoja funkciją VLOOKUP, kad patikrintų, ar elementas priklausomame išskleidžiamajame sąraše yra tas, kuris yra iš pagrindinės kategorijos, ar ne. Jei taip nėra, formulė pateikia klaidą. Funkcija ISERROR ją naudoja, kad grąžintų TRUE, kuri nurodo sąlyginį formatavimą, kad paryškintų langelį.

Jums taip pat gali patikti šios „Excel“ pamokos:

• Ištraukite duomenis pagal išskleidžiamojo sąrašo pasirinkimą.
• Išskleidžiamojo sąrašo su paieškos pasiūlymais kūrimas.
• Išskleidžiamajame sąraše pasirinkite kelis elementus.
• Sukurkite kelis išskleidžiamuosius sąrašus be pakartojimų.
• Sutaupykite laiko naudodami „Excel“ duomenų įvedimo formas.