Hoe om 'n aftreklys in Excel te maak

Die aftreklys is 'n ongelooflike nuttige hulpmiddel wat kan help om die werk met inligting gemakliker te maak. Dit maak dit moontlik om verskeie waardes tegelyk in 'n sel te bevat, waarmee jy kan werk, soos met enige ander. Om die een te kies wat jy nodig het, klik net op die pyltjie-ikoon, waarna 'n lys van waardes uXNUMXbuXNUMXbis vertoon word. Nadat 'n spesifieke een gekies is, word die sel outomaties daarmee gevul, en die formules word herbereken op grond daarvan.

Excel bied baie verskillende metodes om 'n aftreklys te genereer, en boonop laat dit jou toe om dit buigsaam aan te pas. Kom ons ontleed hierdie metodes in meer detail.

Lys skepping proses

Om 'n opwip-kieslys te genereer, klik op die menu-items langs die pad "Data" - "Data Validation". 'n Dialoogkassie sal oopmaak waar jy die "Parameters"-oortjie moet vind en daarop klik as dit nog nie voorheen oopgemaak is nie. Dit het baie instellings, maar die "Data Type"-item is vir ons belangrik. Van al die betekenisse is "Lys" die regte een.

Die aantal metodes waarop inligting in die opwiplys ingevoer word, is redelik groot.

1. Onafhanklike aanduiding van lyselemente geskei deur 'n kommapunt in die "Bron"-veld op dieselfde oortjie van dieselfde dialoogkassie.
   

   

2. Voorlopige aanduiding van waardes. Die Bron-veld bevat die reeks waar die vereiste inligting beskikbaar is.
   

   

3. Spesifiseer 'n benoemde reeks. 'n Metode wat die vorige een herhaal, maar dit is slegs nodig om die reeks voorlopig te benoem.
   

   

Enige van hierdie metodes sal die gewenste resultaat lewer. Kom ons kyk na metodes om aftreklyste in werklike situasies te genereer.

Gebaseer op data uit die lys

Kom ons sê ons het 'n tabel wat die tipes verskillende vrugte beskryf.

Om 'n lys in 'n aftreklys te skep gebaseer op hierdie stel inligting, moet jy die volgende doen:

1. Kies die sel wat vir die toekomstige lys gereserveer is.
2. Vind die Data-oortjie op die lint. Daar klik ons ​​op "Verifieer data".
   

   

3. Soek die item "Data Tipe" en verander die waarde na "Lys".
   

   

4. Voer die verlangde reeks in in die veld wat die opsie "Bron" aandui. Neem asseblief kennis dat absolute verwysings gespesifiseer moet word sodat die inligting nie verskuif wanneer die lys gekopieer word nie.
   

Daarbenewens is daar 'n funksie om lyste gelyktydig in meer as een sel te genereer. Om dit te bereik, moet jy hulle almal kies en dieselfde stappe uitvoer as wat vroeër beskryf is. Weereens moet jy seker maak dat absolute verwysings geskryf is. As die adres nie 'n dollarteken langs die kolom- en ryname het nie, moet jy dit byvoeg deur die F4-sleutel te druk totdat die $-teken langs die kolom- en ryname is.

Met handmatige data-opname

In die situasie hierbo is die lys geskryf deur die vereiste reeks uit te lig. Dit is 'n gerieflike metode, maar soms is dit nodig om die data handmatig op te teken. Dit sal dit moontlik maak om duplisering van inligting in die werkboek te vermy.

Gestel ons staan ​​voor die taak om 'n lys te skep wat twee moontlike keuses bevat: ja en nee. Om die taak uit te voer, is dit nodig:

1. Klik op die sel vir die lys.
2. Maak "Data" oop en vind daar die afdeling "Data Check" wat aan ons bekend is.
   

   

3. Weereens, kies die tipe "Lys".
   

   

4. Hier moet jy "Ja; Nee” as die bron. Ons sien dat inligting met die hand ingevoer word met behulp van 'n kommapunt vir opsomming.

Nadat ons OK geklik het, het ons die volgende resultaat.

Vervolgens sal die program outomaties 'n aftreklys in die toepaslike sel skep. Alle inligting wat die gebruiker as items in die opwiplys gespesifiseer het. Die reëls vir die skep van 'n lys in verskeie selle is soortgelyk aan die voriges, met die enigste uitsondering dat jy die inligting met die hand met 'n kommapunt moet spesifiseer.

Skep 'n aftreklys deur die OFFSET-funksie te gebruik

Benewens die klassieke metode, is dit moontlik om die funksie te gebruik BESKIKKINGaftrekkieslys te genereer.

Kom ons maak die blad oop.

Om die funksie vir die aftreklys te gebruik, moet jy die volgende doen:

1. Kies die sel van belang waar jy die toekomstige lys wil plaas.
2. Maak die "Data"-oortjie en die "Data Validation"-venster in volgorde oop.
   

   

3. Stel "Lys". Dit word gedoen op dieselfde manier as die vorige voorbeelde. Ten slotte word die volgende formule gebruik: =AFSET(A$2$;0;0;5). Ons voer dit in waar die selle gespesifiseer word wat as 'n argument gebruik sal word.

Dan sal die program 'n spyskaart met 'n lys vrugte skep.

Die sintaksis hiervoor is:

=AFSET(verwysing,lyn_verskuiwing,kolom_verskuiwing,[hoogte],[wydte])

Ons sien dat hierdie funksie 5 argumente het. Eerstens word die eerste seladres wat verreken moet word, gegee. Die volgende twee argumente spesifiseer hoeveel rye en kolomme om te verreken. Van ons gepraat, die Hoogte-argument is 5 omdat dit die hoogte van die lys verteenwoordig. 

Aftreklys in Excel met datavervanging (+ deur die OFFSET-funksie te gebruik)

In die gegewe geval BESKIKKING toegelaat om 'n opwipkieslys te skep wat in 'n vaste reeks geleë is. Die nadeel van hierdie metode is dat jy die formule self moet wysig nadat jy die item bygevoeg het.

Om 'n dinamiese lys te skep met ondersteuning vir die invoer van nuwe inligting, moet jy:

1. Kies die sel van belang.
2. Brei die "Data"-oortjie uit en klik op "Data Validation".
3. In die venster wat oopmaak, kies die "Lys" item weer en spesifiseer die volgende formule as die databron: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Klik op OK.

Dit bevat 'n funksie COUNTIF, om dadelik te bepaal hoeveel selle gevul is (alhoewel dit 'n baie groter aantal gebruike het, skryf ons dit net hier vir 'n spesifieke doel).

Om die formule normaal te laat funksioneer, is dit nodig om na te spoor of daar leë selle op die pad van die formule is. Hulle behoort nie te wees nie.

Aftreklys met data van 'n ander blad of Excel-lêer

Die klassieke metode werk nie as jy inligting van 'n ander dokument of selfs 'n blad in dieselfde lêer moet kry nie. Hiervoor word die funksie gebruik INDIREKTE, wat jou toelaat om in die korrekte formaat 'n skakel na 'n sel in 'n ander blad of in die algemeen - 'n lêer in te voer. Jy moet die volgende doen:

1. Aktiveer die sel waar ons die lys plaas.
2. Ons maak die venster oop wat ons reeds ken. Op dieselfde plek waar ons voorheen bronne vir ander reekse aangedui het, word 'n formule in die formaat aangedui =INDIREKTE(“[Lys1.xlsx]Blad1!$A$1:$A$9”). In plaas van Lys1 en Blad1 kan jy natuurlik jou boek- en bladname onderskeidelik invoeg. 

Aandag! Die lêernaam word tussen vierkantige hakies gegee. In hierdie geval sal Excel nie die lêer wat tans gesluit is as 'n bron van inligting kan gebruik nie.

Daar moet ook op gelet word dat die lêernaam self slegs sin maak as die vereiste dokument in dieselfde vouer geleë is as die een waar die lys ingevoeg sal word. Indien nie, moet u die adres van hierdie dokument volledig spesifiseer.

Skep afhanklike dropdowns

'n Afhanklike lys is een waarvan die inhoud deur die gebruiker se keuse in 'n ander lys geraak word. Gestel ons het 'n tafel oop voor ons wat drie reekse bevat, waarvan elkeen 'n naam gekry het.

Jy moet hierdie stappe volg om lyste te genereer waarvan die resultaat geraak word deur die opsie wat in 'n ander lys gekies is.

1. Skep 1ste lys met reeksname.
   

   

2. By die brontoegangspunt word die vereiste aanwysers een vir een uitgelig.
   

   

3. Skep 'n 2de lys na gelang van die tipe plant wat die persoon gekies het. Alternatiewelik, as jy bome in die eerste lys spesifiseer, sal die inligting in die tweede lys "eik, haagbeuk, kastaiingbruin" en meer wees. Dit is nodig om die formule neer te skryf in die plek van invoer van die databron =INDIREK(E3). E3 – sel wat die naam van die reeks bevat 1.=INDIREK(E3). E3 – sel met die naam van die lys 1.

Nou is alles gereed.

Hoe om veelvuldige waardes uit 'n aftreklys te kies?

Soms is dit nie moontlik om voorkeur aan slegs een waarde te gee nie, dus moet meer as een gekies word. Dan moet jy 'n makro by die bladsykode voeg. Deur die sleutelkombinasie Alt + F11 te gebruik, maak die Visual Basic Editor oop. En die kode word daar ingevoeg.

Privaat subwerkblad_Verandering (ByVal-teiken as reeks)

    Fout Volgende aflewering

    Indien Nie Sny nie (Teiken, Omvang(«Е2:Е9»)) Is Niks En Teiken.Selle.Tel = 1 Dan

        Application.EnableEvents = Onwaar

        As Len (Teiken.Offset (0, 1)) = 0 Dan

            Teiken.Offset (0, 1) = Teiken

        anders

            Teiken.Einde (xlNaRegs) .Offset (0, 1) = Teiken

        Einde as

        Teiken. Duidelike inhoud

        Application.EnableEvents = Waar

    Einde as

End Sub 

Om die inhoud van die selle hieronder te laat vertoon, voeg ons die volgende kode in die redigeerder in.

Privaat subwerkblad_Verandering (ByVal-teiken as reeks)

    Fout Volgende aflewering

    Indien Nie Sny nie (Teiken, Omvang(«Н2:К2»)) Is Niks En Teiken.Selle.Tel = 1 Dan

        Application.EnableEvents = Onwaar

        As Len (Teiken.Offset (1, 0)) = 0 Dan

            Teiken.Offset (1, 0) = Teiken

        anders

            Teiken.Einde (xlDown) .Offset (1, 0) = Teiken

        Einde as

        Teiken. Duidelike inhoud

        Application.EnableEvents = Waar

    Einde as

End Sub

En laastens word hierdie kode gebruik om in een sel te skryf.

Privaat subwerkblad_Verandering (ByVal-teiken as reeks)

    Fout Volgende aflewering

    Indien Nie Sny nie (Teiken, Omvang(«C2:C5»)) Is Niks En Teiken.Selle.Tel = 1 Dan

        Application.EnableEvents = Onwaar

        newVal = Teiken

        Toepassing.Ontdoen

        oldval = Teiken

        As Len (oldval) <> 0 En oldval <> newVal Toe

            Teiken = Teiken & «,» & newVal

        anders

            Teiken = newVal

        Einde as

        As Len (newVal) = 0 Dan Target.ClearContents

        Application.EnableEvents = Waar

    Einde as

End Sub

Reekse is redigeerbaar.

Hoe om 'n aftreklys met 'n soektog te maak?

In hierdie geval moet jy aanvanklik 'n ander tipe lys gebruik. Die "Ontwikkelaar"-oortjie word oopgemaak, waarna jy moet klik of tik (as die skerm aanraak) op die "Insert" - "ActiveX" element. Dit het 'n kombinasieboks. Jy sal gevra word om hierdie lys te teken, waarna dit by die dokument gevoeg sal word.

Verder word dit gekonfigureer deur eienskappe, waar 'n reeks gespesifiseer word in die ListFillRange-opsie. Die sel waar die gebruikergedefinieerde waarde vertoon word, word gekonfigureer deur die LinkedCell-opsie te gebruik. Vervolgens moet u net die eerste karakters neerskryf, aangesien die program outomaties moontlike waardes sal voorstel.

Aftreklys met outomatiese datavervanging

Daar is ook 'n funksie dat die data outomaties vervang word nadat dit by die reeks gevoeg is. Dit is maklik om dit te doen:

1. Skep 'n stel selle vir die toekomstige lys. In ons geval is dit 'n stel kleure. Ons kies dit.
   

   

2. Vervolgens moet dit as 'n tabel geformateer word. Jy moet op die knoppie met dieselfde naam klik en die tabelstyl kies.
   

   

   

Vervolgens moet u hierdie reeks bevestig deur die "OK"-knoppie te druk.

Ons kies die resulterende tabel en gee dit 'n naam deur die invoerveld bo-op kolom A.

Dit is dit, daar is 'n tabel, en dit kan gebruik word as die basis vir 'n aftreklys, waarvoor u nodig het:

1. Kies die sel waar die lys geleë is.
2. Maak die Data Validation dialoog oop.
   

   

3. Ons stel die datatipe op "Lys", en as waardes gee ons die naam van die tabel deur die =-teken.
   

   

   

Alles, die sel is gereed, en die name van die kleure word daarin gewys, soos ons oorspronklik nodig gehad het. Nou kan jy nuwe posisies byvoeg bloot deur dit in 'n sel wat 'n bietjie laer geleë is onmiddellik na die laaste een te skryf.

Dit is die voordeel van die tabel, dat die reeks outomaties verhoog wanneer nuwe data bygevoeg word. Gevolglik is dit die gerieflikste manier om 'n lys by te voeg.

Hoe om aftreklys te kopieer?

Om te kopieer, is dit genoeg om die sleutelkombinasie Ctrl + C en Ctrl + V te gebruik. Die aftreklys sal dus saam met die formatering gekopieer word. Om formatering te verwyder, moet u 'n spesiale plak gebruik (in die kontekskieslys verskyn hierdie opsie nadat u die lys gekopieer het), waar die opsie "voorwaardes op waardes" ingestel is.

Kies alle selle wat 'n aftreklys bevat

Om hierdie taak uit te voer, moet jy die "Kies 'n groep selle"-funksie in die "Vind en kies"-groep gebruik.

Daarna sal 'n dialoogkassie oopmaak, waar u die items "Alles" en "These same" in die "Data Validation"-kieslys moet kies. Die eerste item kies alle lyste, en die tweede kies slegs dié wat soortgelyk is aan sekeres.