Excel nolaižamajā sarakstā atlasiet vairākus vienumus

Viens no maniem kolēģiem man jautāja, vai ir iespējams veikt vairākas atlases Excel nolaižamajā sarakstā.

Veidojot nolaižamo sarakstu, varat veikt tikai vienu izvēli. Ja izvēlaties citu vienumu, pirmais tiek aizstāts ar jauno atlasi.

Viņš vēlējās no viena nolaižamā saraksta veikt vairākas atlases tā, lai atlases tiktu pievienotas jau esošajai šūnas vērtībai.

Kaut kas, kā parādīts attēlā:

To nekādā veidā nevar izdarīt, izmantojot Excel iebūvētās funkcijas.

Vienīgais veids ir izmantot VBA kodu, kas tiek palaists ikreiz, kad veicat atlasi, un pievieno atlasīto vērtību esošajai vērtībai.

Skatieties video - kā atlasīt vairākus vienumus no Excel nolaižamā saraksta

Kā veikt vairākas izvēles nolaižamajā sarakstā

Šajā apmācībā es parādīšu, kā veikt vairākas atlases Excel nolaižamajā sarakstā (ar atkārtojumu un bez atkārtošanās).

Šī ir bijusi viena no populārākajām Excel apmācībām šajā vietnē. Tā kā man ir daudz līdzīgu jautājumu, esmu nolēmis šīs sadaļas beigās izveidot FAQ sadaļu. Tātad, ja jums ir kādi jautājumi pēc šīs lasīšanas, lūdzu, vispirms izlasiet sadaļu BUJ.

Nolaižamā saraksta izveidei, kas ļauj veikt vairākas izvēles, ir divas daļas:

• Nolaižamā saraksta izveide.
• VBA koda pievienošana aizmugurē.

Nolaižamā saraksta izveide programmā Excel

Lai izveidotu nolaižamo sarakstu programmā Excel, veiciet tālāk norādītās darbības.

1. Atlasiet šūnu vai šūnu diapazonu, kurā vēlaties parādīt nolaižamo sarakstu (šajā piemērā C2).
2. Dodieties uz Dati -> Datu rīki -> Datu validācija.
3. Dialoglodziņa Datu validācija cilnē Iestatījumi atlasiet “Saraksts” kā validācijas kritērijus.
4. Laukā Avots atlasiet šūnas, kuru nolaižamajā izvēlnē ir vajadzīgie vienumi.
5. Noklikšķiniet uz Labi.

Tagad šūnai C2 ir nolaižamais saraksts, kurā parādīti vienumu nosaukumi A2: A6.

Pašlaik mums ir nolaižamais saraksts, kurā varat vienlaikus atlasīt vienu vienumu (kā parādīts zemāk).

Lai iespējotu šo nolaižamo izvēlni, lai mēs varētu veikt vairākas izvēles, aizmugurē jāpievieno VBA kods.

Šīs apmācības nākamās divas sadaļas sniegs jums VBA kodu, lai nolaižamajā sarakstā varētu veikt vairākas izvēles (ar atkārtojumu un bez tā).

VBA kods, lai nolaižamajā sarakstā atļautu vairākas izvēles (ar atkārtojumu)

Zemāk ir Excel VBA kods, kas ļaus mums nolaižamajā sarakstā atlasīt vairāk nekā vienu vienumu (ļaujot atlasē atkārtot):

Privāta apakšdarblapa_maiņa (ByVal mērķis kā diapazons) 'Sumit Bansal kods no vietnes https://trumpexcel.com' Lai atlasītu vairākas saites Excel nolaižamajā sarakstā Dim Dim Oldvalue kā virkne Dim Newvalue kā virkne kļūda GoTo Exitsub If Target. Adrese = "$ C $ 2" Tad If Target.SpecialCells (xlCellTypeAllValidation) is Nothing GoTo Exitsub Else: If Target.Value = "" then GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target.Value Application.Undo Oldvalue = Target.Value If Oldvalue = "" Then Target.Value = Newvalue Else Target.Value = Oldvalue & "," & Newvalue End if End if End if Application.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub 

Tagad šis kods jāievieto VB redaktora modulī (kā parādīts sadaļā “Kur ievietot VBA kodu”).

Kad esat ievietojis šo kodu aizmugurē (aplūkots vēlāk šajā apmācībā), tas ļaus jums veikt vairākas izvēles nolaižamajā izvēlnē (kā parādīts zemāk).

Ņemiet vērā, ka, atlasot vienumu vairāk nekā vienu reizi, tas tiks ievadīts vēlreiz (atkārtošana ir atļauta).

Izmēģiniet pats … Lejupielādējiet parauga failu

VBA kods, lai nolaižamajā sarakstā atļautu vairākas izvēles (bez atkārtošanās)

Daudzi cilvēki ir jautājuši par kodu, lai bez atkārtošanās atlasītu vairākus vienumus no nolaižamā saraksta.

Šeit ir kods, kas nodrošinās, ka vienumu var atlasīt tikai vienu reizi, lai nebūtu atkārtojumu:

Privāta apakšdarblapa_maiņa (ByVal mērķa diapazons) 'Sumit Bansal kods no vietnes https://trumpexcel.com' Lai atļautu vairākas atlases Excel nolaižamajā sarakstā (bez atkārtošanās) Dim Oldvalue kā virkne Dim Newvalue kā virkņu lietojumprogramma.EnableEvents = True On Error GoTo Exitsub If Target.Address = "$ C $ 2" Then If Target.SpecialCells (xlCellTypeAllValidation) is Nothing GoTo Exitsub Else: If Target.Value = "" then GoTo Exitsub Else Application.EnableEvents = False Newvalue = Target. Vērtība Application.Undo Oldvalue = Target.Value If Oldvalue = "" Tad Target.Value = Newvalue Else If InStr (1, Oldvalue, Newvalue) = 0 Tad Target.Value = Oldvalue & "," & Newvalue Else: Target.Value = Oldvalue beigas Ja beigas Ja beigas Ja beigas Ja lietojumprogramma.EnableEvents = True Exitsub: Application.EnableEvents = True End Sub

Tagad šis kods jāievieto VB redaktora modulī (kā parādīts šīs apmācības nākamajā sadaļā).

Šis kods ļaus nolaižamajā sarakstā atlasīt vairākus vienumus. Tomēr jūs varēsit izvēlēties vienumu tikai vienu reizi. Ja mēģināsit to atlasīt vēlreiz, nekas nenotiks (kā parādīts zemāk).

Izmēģiniet pats … Lejupielādējiet parauga failu

Kur ievietot VBA kodu

Pirms sākat izmantot šo kodu programmā Excel, jums tas jāievada aizmugurē, lai tas tiktu aktivizēts ikreiz, kad notiek izmaiņas nolaižamajā atlasē.

Lai ievietotu VBA kodu Excel aizmugurē, veiciet tālāk norādītās darbības.

1. Dodieties uz cilni Izstrādātājs un noklikšķiniet uz Visual Basic (varat izmantot arī īsinājumtaustiņu - Alt + F11). Tiks atvērts Visual Basic redaktors.
2. Kreisajā pusē jābūt Project Explorer rūtim (ja tā nav, izmantojiet taustiņu kombināciju Control + R, lai tā būtu redzama).
3. Veiciet dubultklikšķi uz darblapas nosaukuma (kreisajā rūtī), kur atrodas nolaižamais saraksts. Tādējādi tiek atvērts šīs darblapas koda logs.
4. Koda logā nokopējiet un ielīmējiet iepriekš minēto kodu.
5. Aizveriet VB redaktoru.

Tagad, atgriežoties nolaižamajā izvēlnē un veicot atlasi, tas ļaus jums veikt vairākas izvēles (kā parādīts zemāk):

Izmēģiniet pats … Lejupielādējiet parauga failu

Piezīme: Tā kā mēs izmantojam VBA kodu, lai to paveiktu, jums ir jāsaglabā darbgrāmata ar .xls vai .xlsm paplašinājumu.

Biežāk uzdotie jautājumi (FAQ)

Es esmu izveidojis šo sadaļu, lai atbildētu uz dažiem visbiežāk uzdotajiem jautājumiem par šo apmācību un VBA kodu. Ja jums ir kādi jautājumi, lūdzu, vispirms izskatiet šo vaicājumu sarakstu.

J: VBA kodā funkcionalitāte ir paredzēta tikai šūnai C2. Kā to iegūt citām šūnām? Atbilde: Lai šo nolaižamo izvēlni iegūtu vairākās šūnās citās šūnās, jums ir jāmaina VBA kods aizmugurē. Pieņemsim, ka vēlaties to iegūt C2, C3 un C4, jums jāaizstāj šāda rindiņa kodā: If Target.Address = "$ C $ 2" Tad ar šo rindu: If Target.Address = "$ C $ 2" Vai Target.Address = "$ C $ 3" ​​Vai Target.Address = "$ C $ 4" Tad

J: Man ir jāizveido vairāki nolaižamie elementi visā kolonnā “C”. Kā to iegūt visām kolonnās esošajām šūnām ar vairāku atlases funkciju? Atbilde: Lai visas slejas nolaižamajās izvēlnēs iespējotu vairākas atlases, koda vietā nomainiet šādu rindiņu: If Target.Address = "$ C $ 2" Tad ar šo rindu: If Target.Column = 3 Tad Līdzīgās rindās, ja ja vēlaties šo funkcionalitāti C un D slejā, izmantojiet zemāk esošo rindiņu: Ja Target.Column = 3 vai Target.Column = 4 Tad

J: Man ir jāizveido vairākas nolaižamās izvēlnes pēc kārtas. Kā es varu to izdarīt? Atbilde: Ja jums ir jāizveido nolaižamie saraksti ar vairākām atlasēm pēc kārtas (pieņemsim, ka otrā rinda), jums jāaizstāj zemāk esošā koda rinda: If Target.Address = "$ C $ 2" Tad ar šo rindu: Ja mērķa.

J: Pašlaik vairākas atlases ir atdalītas ar komatu. Kā es varu to mainīt, lai tos atdalītu ar atstarpi (vai jebkuru citu atdalītāju). Atbilde: Lai tos atdalītu ar atdalītāju, kas nav komats, jums jāaizstāj šāda VBA koda rinda: Target.Value = Oldvalue & "," & Newvalue ar šo VBA koda rindu: Target.Value = Oldvalue & "" & Newvalue Līdzīgi, ja vēlaties mainīt komatu ar citu rakstzīmi, piemēram, |, varat izmantot šādu koda rindu: Target.Value = Oldvalue & "|" & Newvalue

J: Vai es varu iegūt katru atlasi atsevišķā rindā tajā pašā šūnā? A: Jā, jūs varat. Lai to iegūtu, jums jāaizstāj zemākā VBA koda rinda: Target.Value = Oldvalue & "," & Newvalue ar šo koda rindu: Target.Value = Oldvalue & vbNewLine & Newvalue vbNewLine ievieto jaunu rindu tajā pašā šūnā . Tāpēc ikreiz, kad veicat atlasi nolaižamajā izvēlnē, tā tiks ievietota jaunā rindā.

J: Vai es varu panākt, lai vairāku atlases funkcionalitāte darbotos aizsargātā lapā? A: Jā, jūs varat. Lai to paveiktu, jums ir jādara divas lietas: pievienojiet kodam šādu rindu (tūlīt pēc DIM priekšraksta): Me.Protect UserInterfaceOnly: = True Otrkārt, jums jāpārliecinās, ka šūnas, kurām ir nolaižamā izvēlne ar vairāku atlases funkciju, nav aizslēgtas, kad aizsargājat visu lapu. Šeit ir apmācība, kā to izdarīt: Bloķēt šūnas programmā Excel