Jobber med VB-prosjekt (12)
Betinget formatering (5)
Lister og områder (5)
Makroer (VBA-prosedyrer) (63)
Diverse (39)
Excel-feil og feil (3)

Relaterte rullegardinlister

Last ned filen som ble brukt i videoopplæringen:

Hjelpet artikkelen? Del lenken med vennene dine! Video leksjoner

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"venstre","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; bakgrunnsfarge:#333333; opasitet:0.6; filter:alpha(opacity=60);","titlecss":"display:block; stilling:slektning; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; stilling:slektning; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; farge:#fff; margin-top:8px;","buttoncss":"display:block; stilling:slektning; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Alternativ Eksplisitt Alternativ Sammenlign tekst Dim bu As Boolean Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub If Target.Row = 1 Then Me.TextBox1.Visible = False: Me.ListBox1.Visible = False: Avslutt Sub Hvis Target.Column = 3 Deretter "nummer på kolonnen der vi legger inn verdiene bu = True With Me.TextBox1 .Top = Target.Top: .Text = Target.Value: .Activate End With Me. ListBox1 .Top = Target .Top + 5 If (.Top + .Height + ActiveWindow.PointsToScreenPixelsY(0) * Application.InchesToPoints(1) * 15 / 1440) > _ (ActiveWindow.Application.Height + ActiveWindow.Top)Application.Top Deretter _ .Top = .Top - .Height + Target.Height "* ActiveWindow.Zoom / 100 .Clear End With bu = False Me.TextBox1.Visible = True: Me.ListBox1.Visible = True Else Me.TextBox1.Visible = False: Me.ListBox1 .Visible = False End If End Sub Private Sub TextBox1_Change() If Len(TextBox1.Text) = 0 Eller bu Deretter Avslutt Sub "hvis det ikke er noen tegn å søke etter - avslutt Dim x, i As Long, txt As String, lt As Long, s As String txt = TextBox1.Text: lt = Len(TextBox1.Text) "Hvor vi ser etter verdiene x = Sheets("nomenklatur";).Columns(1).SpecialCells( 2).Offset(1).Verdi "(! LANG: For i = 1 To UBound(x, 1)" поиск по первым буквам "If txt = Mid(x(i, 1), 1, lt) Then s = s & x(i, 1) & "~" For i = 1 To UBound(x, 1) "поиск по любому вхождению If InStr(x(i, 1), txt) Then s = s & "~" & x(i, 1) Next i ListBox1.List = Split(s, "~";) End Sub Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Or KeyCode = 9 Then With Me.TextBox1 ActiveCell.Value = .Value .Visible = False: ListBox1.Visible = False End With ActiveCell(2, 1).Select End If End Sub Private Sub ListBox1_Click() If ListBox1.ListIndex = -1 Then Exit Sub Application.EnableEvents = False bu = True With Me.ListBox1 ActiveCell.Value = .Value Me.TextBox1.Text = .Value Me.TextBox1.Visible = False: .Visible = False End With Application.EnableEvents = True bu = False End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Column = 2 Then Exit Sub If Not Intersect(Target, Range("C2:C100000";)) Is Nothing Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Sheets("номенклатура";).Columns(1), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список", vbYesNo + vbQuestion) If lReply = vbYes Then Worksheets("номенклатура";).Range("номенклатура";).Cells(Worksheets("номенклатура";).Range("номенклатура";).Rows.Count + 1, 1) = Target End If End If End If Sheets("номенклатура";).Range("номенклатура";).Sort Key1:=Sheets("номенклатура";).Range("A1";), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal "этот код и поможет отсортировать в алфавитном порядке" End Sub !}

For å redusere unøyaktigheter i dataregistrering bruker jeg ofte lister basert på en rekke celler. La oss vurdere bruken av dette verktøyet ved å bruke eksempelet på timeregistrering.

Til å begynne med eget ark(dette er ikke nødvendig) vi legger ut en liste akseptable verdier i en kolonne eller en rad (fig. 1a); se også Excel-fil, "Liste"-arket.

Ris. 1. Liste over etternavn: (a) i tilfeldig rekkefølge; (b) i alfabetisk rekkefølge.

Last ned i format, eksempler i format

Denne listen over etternavn vil vises i nedtrekkslisten i den rekkefølgen de vises i denne kolonnen. Det er klart at det er bedre å sortere dem alfabetisk for å lette søket (fig. 1b).

La oss gi listen vår navnet på utvalget. For å gjøre dette, velg et område; i vårt tilfelle er dette område A2:A21 og skriv inn navnet på området, som vist i fig. 2; i vårt tilfelle er disse "etternavn":

Ris. 2. Navngi området

La oss velge området der vi skal legge inn etternavn (se Excel-fil, "Inndata"-ark). I vårt eksempel – A2:A32 (fig. 3). La oss gå til Data-fanen, Dataarbeidsgruppe, velg kommandoen Datavalidering:

Ris. 3. Dataverifisering

I dialogboksen "Sjekker angitte verdier" går du til fanen Parameters (fig. 4). I "Datatype"-feltet velger du "Liste". I feltet "Kilde" angir vi: (a) celleområdet der listen er lagret; dette alternativet er egnet hvis listen er plassert på samme Excel ark; (b) områdenavn; Dette alternativet kan brukes både når listen ligger på samme Excel-ark, og når listen ligger på et annet Excel-ark (som i vårt tilfelle). I begge tilfeller, sørg for at det er et likhetstegn (=) foran lenken eller navnet.

Ris. 4. Velge en datakilde for listen: (a) på samme ark; (b) på et hvilket som helst ark

Og om to alternativer til på "Alternativer"-fanen:

• Ignorer tomme celler. Hvis avmerkingsboksen er merket av, lar Excel deg la cellen stå tom. Hvis avmerkingsboksen ikke er merket, kan du gå ut av cellen først etter å ha valgt ett av navnene i listen. Det særegne med alternativet er at Excel lar deg flytte mellom celler (for eksempel ved å bruke Enter-tasten eller opp/ned-pilene), men du kan ikke begynne å skrive, deretter slette alle tegnene og flytte til en annen celle.
• Liste over gyldige celler. Hvis det ikke er noen hake, vil ikke listeikonet vises ved siden av cellen når du plasserer markøren i en inndatacelle, og du vil derfor ikke kunne velge fra listen. Selv om alle andre egenskaper ved å jobbe med en liste vil være i kraft, vil ikke Excel tillate deg å legge inn en vilkårlig verdi i en celle.

La oss gå til "Input Messages"-fanen i "Checking Input Values"-vinduet. Merk av i boksen "Vis verktøytips hvis cellen er gjeldende". Skriv inn tittelen og teksten til meldingen i de aktuelle feltene (fig. 5). Deretter, når brukeren står på en av cellene i inndataområdet (i eksempelet i fig. 5 - i celle A6), vil meldingen vi opprettet vises.

Ris. 5. Stille inn inngangsmeldingen

I vinduet "Sjekker angitte verdier" går du til fanen "Feilmelding" (Figur 6). Merk av for "Vis feilmelding". I «Type»-feltet velger du meldingstypen. Skriv inn tittelen og teksten til meldingen. Meldingen vi opprettet vil vises hver gang brukeren prøver å angi en ugyldig verdi.

Ris. 6. Stille inn feilmeldinger

Akseptable typer feilmeldinger (fig. 7):

• Stoppe– forhindrer å legge inn ugyldige data; knapp Gjenta lar deg gå tilbake til input, knappen Avbryt Gjenta.
• Advarsel– advarer om å legge inn ugyldige data, men forbyr ikke slik inndata; knapp Ja lar deg godta ugyldig inndata; knapp Nei lar deg fortsette å skrive (verdien som tidligere ble skrevet inn i cellen blir tilgjengelig for redigering); knapp Avbryt sletter en celle og lar deg begynne å skrive over eller gå videre til å skrive i andre celler; knappen er valgt som standard Nei.
• Beskjed– varsler om å legge inn ugyldige data; selv om han lar dem bli introdusert. Denne meldingstypen er den mest fleksible. Når informasjonsmelding brukeren kan klikke på knappen OK for å godta ugyldig datainntasting, eller trykk på knappen Avbryt for å avbryte oppføringen; knappen er valgt som standard OK.

Ris. 7. Velge feilmeldingstype

Noen kommentarer. 1. Hvis du også skrev inn en feilmelding i meldingsvinduet i kategorien Feilmelding lang tekst, da vil feilmeldingsvinduet være for bredt (som i fig. 7); bruk linjeskift Shift + Enter på punktet i meldingen der du vil dele linjene (Figur 8).

Ris. 8. Feilmeldingsboks for redusert bredde

2. Bredden på rullegardinlisten bestemmes av bredden på cellen som datavalidering brukes for. La inngangscellene være brede nok til å unngå avskjæring gyldige oppføringer, hvis bredde mer bredde nedtrekksliste. Ellers kan du gjøre feil valg basert på de første bokstavene i ordet.

3. Maksimalt antall oppføringer i nedtrekkslisten er begrenset, men ikke for mye :), nemlig med tallet 32.767.

4. Hvis du ikke vil at brukerne skal redigere vurderingslisten, plasser den på et eget ark, og skjul og beskytt dette arket.

Nedtrekksliste i Excel er kanskje en av de mest praktiske måter arbeider med data. Du kan bruke dem både når du fyller ut skjemaer og lager dashbord og voluminøse tabeller. Nedtrekkslister brukes ofte i applikasjoner på smarttelefoner og nettsider. De er intuitive for den gjennomsnittlige brukeren.

Klikk på knappen nedenfor for å laste ned en fil med eksempler på nedtrekkslister i Excel:

Videoopplæring

Hvordan lage en nedtrekksliste i Excel basert på data fra listen

La oss forestille oss at vi har en liste over frukt:

For å lage en rullegardinliste må vi gjøre følgende trinn:

• Gå til "fanen" Data" => seksjon " Arbeid med data " på verktøylinjen => velg elementet " Datakontroll “.

• I popup-vinduet " Validering av angitte verdier " på fanen " Alternativer" i datatypen velg " Liste “:

• I feltet " Kilde” angi en rekke fruktnavn =$A$2:$A$6 eller bare plasser musepekeren i verdiinntastingsfeltet " Kilde" og velg deretter dataområdet med musen:

Hvis du vil lage rullegardinlister i flere celler om gangen, velger du alle cellene du vil opprette dem i, og følger deretter trinnene ovenfor. Det er viktig å sikre at cellereferanser er absolutte (f.eks. $A$2), i stedet for relativ (f.eks. A2 eller A$2 eller $A2 ).

Hvordan lage en rullegardinliste i Excel ved hjelp av manuell dataregistrering

I eksemplet ovenfor la vi inn en liste over data for en rullegardinliste ved å velge et celleområde. I tillegg til denne metoden kan du legge inn data for å lage en rullegardinliste manuelt (det er ikke nødvendig å lagre det i noen celler).

Tenk deg for eksempel at vi ønsker å vise to ord "Ja" og "Nei" i en rullegardinmeny. Til dette trenger vi:

• Velg cellen der vi vil lage en rullegardinliste;
• Gå til "fanen" Data" => seksjon " Arbeid med data " på verktøylinjen => velg " Datakontroll “:

• I popup-vinduet " Validering av angitte verdier " på fanen " Alternativer" i datatypen velg " Liste “:

• I feltet " Kilde" skriv inn verdien "Ja; Nei".
• Klikk " OK “

Systemet vil da lage en nedtrekksliste i den valgte cellen. Alle elementer oppført i " Kilde", atskilt med semikolon, vil gjenspeiles i forskjellige linjer i rullegardinmenyen.

Hvis du samtidig vil lage en rullegardinliste i flere celler, velg de nødvendige cellene og følg instruksjonene ovenfor.

Hvordan lage en nedtrekksliste i Excel ved å bruke OFFSET-funksjonen

Sammen med metodene beskrevet ovenfor, kan du også bruke en formel for å lage rullegardinlister.

For eksempel har vi en liste med en liste over frukt:

For å lage en rullegardinliste ved hjelp av en formel, må du gjøre følgende:

• Velg cellen der vi vil lage en rullegardinliste;
• Gå til "fanen" Data" => seksjon " Arbeid med data " på verktøylinjen => velg " Datakontroll “:

• I popup-vinduet " Validering av angitte verdier " på fanen " Alternativer" i datatypen velg " Liste “:

• I feltet " Kilde"skriv inn formelen: =OFFEST(2$$;0;0;5)
• Klikk " OK “

Systemet vil lage en rullegardinliste med en liste over frukt.

Hvordan fungerer denne formelen?

I eksemplet ovenfor brukte vi formelen = OFFSET(lenke, radforskyvning, kolonneforskyvning, [høyde], [bredde]).

Denne funksjonen inneholder fem argumenter. I argumentasjonen " link” (i eksemplet $A$2) indikerer hvilken celle du skal begynne å skifte fra. I argumenter «offset_by_rows"Og «offset_by_columns»(i eksemplet er verdien "0") – hvor mange rader/kolonner som må flyttes for å vise dataene. I argumentasjonen " [høyde]” er verdien “5” spesifisert, som indikerer høyden på celleområdet. Argument " [bredde]” angir vi ikke, siden i vårt eksempel består området av én kolonne.

Ved å bruke denne formelen, returnerer systemet til deg som data for rullegardinlisten en rekke celler som starter med celle $A$2, bestående av 5 celler.

Hvordan lage en rullegardinliste i Excel med dataerstatning (ved å bruke OFFSET-funksjonen)

Hvis du bruker formelen i eksemplet ovenfor for å lage en liste, lager du en liste over data som er fanget opp i et spesifikt celleområde. Hvis du vil legge til en verdi som et listeelement, må du justere formelen manuelt. Nedenfor lærer du hvordan du lager en dynamisk rullegardinliste som automatisk laster inn nye data for visning.

For å lage en liste trenger du:

• Velg cellen der vi vil lage en rullegardinliste;
• Gå til "fanen" Data" => seksjon " Arbeid med data " på verktøylinjen => velg " Datakontroll “;
• I popup-vinduet " Validering av angitte verdier " på fanen " Alternativer" i datatypen velg " Liste “;
• I feltet " Kilde"skriv inn formelen: =OFFEST(A$2$,0,0,COUNTIF($A$2:$A$100;"<>”))
• Klikk " OK “

I denne formelen, i argumentet "[ høyde]” spesifiserer vi som et argument som angir høyden på listen med data – en formel som beregner i et gitt område A2:A100 mengde ikke tomme celler.

Merk: Til riktig drift formler, er det viktig at det ikke er tomme linjer i listen over data som skal vises i rullegardinmenyen.

Hvordan lage en nedtrekksliste i Excel med automatisk dataerstatning

For at nye data automatisk skal lastes inn i rullegardinlisten du opprettet, må du gjøre følgende handlinger:

• Vi lager en liste over data som skal vises i rullegardinlisten. I vårt tilfelle er dette en liste over farger. Velg listen med venstre museknapp:

• På verktøylinjen klikker du på " Formater som tabell “:

• Velg borddesignstilen fra rullegardinmenyen:

• Ved å trykke på " OK" i popup-vinduet, bekreft det valgte celleområdet:

• Velg deretter tabelldataområdet for rullegardinlisten og gi det et navn i venstre marg over kolonne "A":

Tabellen med dataene er klar, nå kan vi lage en nedtrekksliste. For å gjøre dette trenger du:

• Velg cellen der vi vil lage en liste;
• Gå til "fanen" Data" => seksjon " Arbeid med data " på verktøylinjen => velg " Datakontroll “:

• I popup-vinduet " Validering av angitte verdier " på fanen " Alternativer" i datatypen velg " Liste “:

• I kildefeltet angir vi ="navnet på bordet ditt" . I vårt tilfelle kalte vi det " Liste “:

• Klar! En rullegardinliste er opprettet, den viser alle dataene fra den angitte tabellen:

• For å legge til en ny verdi i rullegardinlisten, legg til informasjon i cellen ved siden av tabellen med dataene:

• Tabellen utvider automatisk dataområdet. Rullegardinlisten vil bli fylt opp tilsvarende med en ny verdi fra tabellen:

Hvordan kopiere en rullegardinliste i Excel

Excel har muligheten til å kopiere opprettede rullegardinlister. For eksempel, i celle A1 har vi en rullegardinliste som vi ønsker å kopiere til en rekke celler A2:A6 .

Slik kopierer du en rullegardinliste med gjeldende formatering:

• venstreklikk på cellen med rullegardinlisten du vil kopiere;
• CTRL+C ;
• velg celler i et område A2:A6, hvor du vil sette inn rullegardinlisten;
• trykk på hurtigtasten CTRL+V .

Så du vil kopiere rullegardinlisten og lagre originalformat liste (farge, skrift osv.). Hvis du vil kopiere/lime inn en rullegardinliste uten å lagre formatet, gjør du følgende:

• venstreklikk på cellen med rullegardinlisten du vil kopiere;
• trykk på hurtigtasten CTRL+C ;
• velg cellen der du vil sette inn rullegardinlisten;
• høyreklikk => få opp rullegardinmenyen og klikk " Spesialinnsats “;

• I vinduet som vises, i " Sett inn" Velg en " betingelser for verdier “:

• Klikk " OK “

Etter dette kopierer Excel kun dataene fra rullegardinlisten, uten å bevare formateringen av den opprinnelige cellen.

Hvordan velge alle celler som inneholder en rullegardinliste i Excel

Noen ganger er det vanskelig å forstå hvor mange celler som er i Excel-fil inneholder nedtrekkslister. Det er en enkel måte å vise dem på. For dette:

• Klikk på fanen " hjem" på verktøylinjen;
• Klikk " Finn og fremhev " og velg " Velg en gruppe med celler “:

• I dialogboksen velger du " Datakontroll ". I dette feltet kan du velge elementene " Alle sammen"Og" Disse samme “. “Alle sammen” lar deg velge alle nedtrekkslistene på arket. Avsnitt " disse samme" vil vise rullegardinlister med lignende datainnhold i rullegardinmenyen. I vårt tilfelle velger vi " alle “:

Legger til en rullegardinliste til Excel regneark bidrar til å gjøre datainntasting raskere ved å gi brukerne en liste over elementer de kan velge fra, i stedet for å måtte legge inn informasjon hver gang. Når du plasserer slike data i en regnearkcelle, begynner en pil å vises i cellen. Du legger inn data ved å klikke på pilen og deretter velge ønsket oppføring. Du kan sette opp en rullegardinliste i Excel i løpet av noen få minutter, og dermed forbedre hastigheten på dataregistrering betydelig. Nedenfor er en beskrivelse av denne prosessen.

Instruksjoner for Excel

Åpne tabellen på siden du vil lage en nedtrekksliste på. Lag en liste over elementer som skal gjenspeiles i listen. Skriv inn dataene i den rekkefølgen de senere vil bli åpnet i. Oppføringer må være i samme rad eller kolonne og må ikke inneholde tomme celler.

For å lage en liste nødvendige elementer i et eget ark, bør du klikke på fanen på arket der du vil legge inn dataene. Velg en type og merk deretter innholdet som vises i listen. Klikk Høyreklikk Hold musen over det valgte celleområdet og skriv inn et navn for området i Navn-feltet, og velg deretter OK. Du kan beskytte eller skjule et ark for å forhindre at andre brukere gjør endringer i listen.

Grunnleggende om hvordan du lager en nedtrekksliste i Excel

Klikk på cellen du har valgt for dette formålet. Gå til "Data"-fanen på båndet Microsoft Excel. Den vil vises under navnet "Data Check". Gå til Innstillinger-fanen og klikk Liste fra Tillat-menyen i rullegardinlisten. Klikk på veksleknappen i Kilde-menyen. Velg listen over elementer du vil inkludere i rullegardinlisten din i Excel.

Hvis du opprettet et områdenavn i Kilde-feltet, skriv inn et likhetstegn etterfulgt av navnet på området.

Nødvendige innstillinger for oppretting

Velg eller fjern avmerkingen for Ignorer blank avhengig av om cellen som inneholder rullegardinlisten kan forbli tom.

Klikk på Inndatamelding-fanen for å vise en meldingsboks som åpnes når du klikker. Sørg for at menyvalget Vis inndatamelding når cellevalg er merket av, og skriv inn en tittel. Dette vil fullføre alle instruksjonene om hvordan du lager en rullegardinliste i Excel.

Klikk kategorien Alert Error for å vise en feilmelding hvis feil data er angitt. Sørg for at "Vis feil"-advarselen er aktivert ved å merke av i den aktuelle boksen.

Klikk "OK" for å lagre valideringskriteriene og lage rullegardinlisten.

Hvordan lage en nedtrekksliste i Excel - nyttige notater

Hvis rullegardinoppføringen i listen er større enn størrelsen på cellen som inneholder denne listen, kan du endre cellebredden for å se all teksten.

For å fjerne en rullegardinliste, velg cellen som inneholder den. Gå til fanen Data i Microsoft Excel-båndet. Klikk på "Datavalidering"-knappen fra "Dataverktøy"-gruppen. Gå til kategorien Innstillinger, klikk på Fjern alt-knappen og deretter OK.

Som du kan se, er opplæringen om hvordan du lager en rullegardinliste i Excel ganske enkel. Denne instruksen krever ingen spesiell kunnskap, er det bare nødvendig å inngangsnivå kunne jobbe med regneark. Det er også verdt å merke seg at i forskjellige versjoner Excel-menyelementer kan ha noen forskjeller fra ovenstående, dette bør tas i betraktning når du arbeider.