Kuinka tehdä avattava luettelo Excelissä

Avattava luettelo on uskomattoman hyödyllinen työkalu, joka voi helpottaa tietojen käsittelyä. Se mahdollistaa useiden arvojen sisällyttämisen soluun kerralla, joiden kanssa voit työskennellä, kuten muidenkin kanssa. Voit valita tarvitsemasi vaihtoehdon napsauttamalla nuolikuvaketta, jonka jälkeen näkyviin tulee luettelo arvoista uXNUMXbuXNUMXbis. Kun olet valinnut tietyn, solu täytetään automaattisesti sillä ja kaavat lasketaan uudelleen sen perusteella.

Excel tarjoaa monia erilaisia ​​tapoja luoda alasvetovalikko ja lisäksi voit muokata niitä joustavasti. Analysoidaan näitä menetelmiä yksityiskohtaisemmin.

Listan luontiprosessi

Voit luoda ponnahdusvalikon napsauttamalla valikkokohtia polulla "Data" - "Data Validation". Näyttöön tulee valintaikkuna, josta sinun on löydettävä "Parameters" -välilehti ja napsauta sitä, jos sitä ei ole avattu aiemmin. Siinä on paljon asetuksia, mutta "Data Type" -kohta on meille tärkeä. Kaikista merkityksistä "Lista" on oikea.

Menetelmien määrä, joilla tiedot syötetään ponnahdusluetteloon, on melko suuri.

1. Puolipisteellä eroteltujen luetteloelementtien riippumaton merkintä "Lähde"-kentässä, joka sijaitsee saman valintaikkunan samalla välilehdellä.
   

   

2. Arvojen alustava ilmoitus. Lähde-kenttä sisältää alueen, josta vaaditut tiedot ovat saatavilla.
   

   

3. Nimetyn alueen määrittäminen. Menetelmä, joka toistaa edellisen, mutta on tarpeen vain alustavasti nimetä alue.
   

   

Mikä tahansa näistä menetelmistä tuottaa halutun tuloksen. Katsotaanpa menetelmiä avattavien luetteloiden luomiseksi tosielämän tilanteissa.

Perustuu listan tietoihin

Oletetaan, että meillä on taulukko, jossa kuvataan eri hedelmien tyypit.

Jos haluat luoda luettelon avattavassa valikossa näiden tietojen perusteella, sinun on toimittava seuraavasti:

1. Valitse tulevaa luetteloa varten varattu solu.
2. Etsi nauhasta Tiedot-välilehti. Siellä napsautamme "Tarkista tiedot".
   

   

3. Etsi kohta "Data Type" ja vaihda arvoksi "Lista".
   

   

4. Syötä "Lähde" ​​-vaihtoehtoa osoittavaan kenttään haluamasi alue. Huomaa, että absoluuttiset viittaukset on määritettävä, jotta luetteloa kopioitaessa tiedot eivät siirry.
   

Lisäksi on toiminto, jolla voidaan luoda luetteloita kerralla useampaan kuin yhteen soluun. Tämän saavuttamiseksi sinun tulee valita ne kaikki ja suorittaa samat vaiheet kuin aiemmin. Jälleen, sinun on varmistettava, että absoluuttiset viittaukset on kirjoitettu. Jos osoitteessa ei ole dollarimerkkiä sarakkeiden ja rivien nimien vieressä, sinun on lisättävä ne painamalla F4-näppäintä, kunnes $-merkki on sarakkeiden ja rivien nimien vieressä.

Manuaalisella tiedontallennuksella

Yllä olevassa tilanteessa lista kirjoitettiin korostamalla tarvittava alue. Tämä on kätevä tapa, mutta joskus on tarpeen tallentaa tiedot manuaalisesti. Näin vältetään päällekkäiset tiedot työkirjassa.

Oletetaan, että meidän on tehtävä luettelo, joka sisältää kaksi mahdollista vaihtoehtoa: kyllä ​​ja ei. Tehtävän suorittamiseksi tarvitaan:

1. Napsauta solua nähdäksesi luettelon.
2. Avaa ”Data” ja sieltä löydät meille tutun ”Data Check” -osion.
   

   

3. Valitse jälleen "Lista"-tyyppi.
   

   

4. Täällä sinun on kirjoitettava "Kyllä; Ei” lähteeksi. Näemme, että tiedot syötetään manuaalisesti käyttämällä puolipistettä luettelointia varten.

Kun napsautat OK, meillä on seuraava tulos.

Seuraavaksi ohjelma luo automaattisesti pudotusvalikon sopivaan soluun. Kaikki tiedot, jotka käyttäjä on määrittänyt kohteiksi ponnahdusikkunassa. Säännöt luettelon luomiselle useissa soluissa ovat samanlaiset kuin edellisissä, sillä ainoalla poikkeuksella, että tiedot on määritettävä manuaalisesti puolipisteellä.

Avattavan luettelon luominen OFFSET-toiminnolla

Klassisen menetelmän lisäksi on mahdollista käyttää toimintoa HÄVITTÄMINENluodaksesi avattavia valikoita.

Avataan arkki.

Jos haluat käyttää toimintoa avattavassa luettelossa, sinun on toimittava seuraavasti:

1. Valitse kiinnostava solu, johon haluat sijoittaa tulevan luettelon.
2. Avaa "Data"-välilehti ja "Data Validation" -ikkuna peräkkäin.
   

   

3. Aseta "Lista". Tämä tehdään samalla tavalla kuin edellisissä esimerkeissä. Lopuksi käytetään seuraavaa kaavaa: =SIIRTO(A$2$;0;0;5). Syötetään se sinne, missä solut, joita käytetään argumenttina, on määritetty.

Sitten ohjelma luo valikon, jossa on luettelo hedelmistä.

Tämän syntaksi on:

=SIIRTO(viite,viivan_poikkeama,sarakkeen_poikkeama,[korkeus],[leveys])

Näemme, että tällä funktiolla on 5 argumenttia. Ensin annetaan ensimmäinen siirrettävä soluosoite. Seuraavat kaksi argumenttia määrittävät, kuinka monta riviä ja saraketta siirretään. Meistä puhuttaessa Korkeus-argumentti on 5, koska se edustaa luettelon korkeutta. 

Excelin pudotusvalikko tietojen korvaamalla (+ käyttämällä OFFSET-toimintoa)

Annetussa tapauksessa HÄVITTÄMINEN sallitaan ponnahdusvalikon luominen tietyllä alueella. Tämän menetelmän haittana on, että kohteen lisäämisen jälkeen sinun on muokattava kaavaa itse.

Jotta voit luoda dynaamisen luettelon, joka tukee uusien tietojen syöttämistä, sinun on:

1. Valitse kiinnostava solu.
2. Laajenna "Data" -välilehti ja napsauta "Data Validation".
3. Valitse avautuvasta ikkunasta "Lista" uudelleen ja määritä tietolähteeksi seuraava kaava: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Valitse OK.

Tämä sisältää toiminnon LASKE.JOS, määrittääksesi heti, kuinka monta solua on täytetty (vaikka sillä on paljon enemmän käyttötarkoituksia, kirjoitamme sen tähän vain tiettyä tarkoitusta varten).

Jotta kaava toimisi normaalisti, on tarpeen jäljittää, onko kaavan polulla tyhjiä soluja. Niiden ei pitäisi olla.

Pudotusvalikko, jossa on tiedot toisesta arkista tai Excel-tiedostosta

Klassinen menetelmä ei toimi, jos haluat saada tietoja toisesta asiakirjasta tai jopa samassa tiedostossa olevasta arkista. Tätä varten käytetään toimintoa EPÄSUORISTA, jonka avulla voit syöttää oikeassa muodossa linkin toisessa taulukossa olevaan soluun tai yleensä - tiedostoon. Sinun on toimittava seuraavasti:

1. Aktivoi solu, johon lista sijoitetaan.
2. Avaamme jo tuntemamme ikkunan. Samassa paikassa, jossa aiemmin ilmoitimme lähteet muille alueille, kaava on merkitty muodossa =SPIRECT("[List1.xlsx]Sheet1!$A$1:$A$9"). Luonnollisesti List1:n ja Sheet1:n sijasta voit lisätä kirjasi ja arkkien nimet, vastaavasti. 

Huomio! Tiedoston nimi annetaan hakasulkeissa. Tässä tapauksessa Excel ei voi käyttää tällä hetkellä suljettua tiedostoa tietolähteenä.

On myös huomattava, että tiedoston nimessä itsessään on järkeä vain, jos vaadittu asiakirja sijaitsee samassa kansiossa kuin se, johon luettelo lisätään. Jos ei, sinun on määritettävä tämän asiakirjan osoite kokonaisuudessaan.

Riippuvien pudotusvalikoiden luominen

Riippuvainen luettelo on sellainen, jonka sisältöön vaikuttaa käyttäjän valinta toisessa luettelossa. Oletetaan, että edessämme on taulukko, joka sisältää kolme aluetta, joista jokaiselle on annettu nimi.

Sinun on noudatettava näitä ohjeita luodaksesi luetteloita, joiden tulokseen toisessa luettelossa valittu vaihtoehto vaikuttaa.

1. Luo ensimmäinen luettelo alueen nimillä.
   

   

2. Lähteen sisääntulopisteessä vaaditut indikaattorit on korostettu yksitellen.
   

   

3. Luo toinen luettelo henkilön valitseman kasvityypin mukaan. Vaihtoehtoisesti, jos määrität puita ensimmäisessä luettelossa, toisen luettelon tiedot ovat "tammi, valkopyökki, kastanja" ja enemmän. Kaava on tarpeen kirjoittaa tietolähteen syöttöpaikkaan =SUORA(E3). E3 – solu, joka sisältää alueen nimen 1.=SUORA(E3). E3 – solu listan nimellä 1.

Nyt kaikki on valmista.

Kuinka valita useita arvoja avattavasta luettelosta?

Joskus ei ole mahdollista antaa etusijalle vain yksi arvo, joten on valittava useampi kuin yksi. Sitten sinun on lisättävä makro sivukoodiin. Näppäinyhdistelmä Alt + F11 avaa Visual Basic Editorin. Ja koodi laitetaan sinne.

Private Sub Worksheet_Change (ByVal Target as Range)

    On Error Resume Next

    Jos ei leikkaa(kohde, alue(«Е2:Е9»)) on ei mitään ja kohde.solut.määrä = 1 sitten

        Application.EnableEvents = Väärä

        Jos Len (Target.Offset (0, 1)) = 0 Sitten

            Target.Offset (0, 1) = Tavoite

        muu

            Target.End (xlToRight) .Offset (0, 1) = Tavoite

        End If

        Target.ClearContents

        Application.EnableEvents = Tosi

    End If

End Sub 

Jotta solujen sisältö näkyy alla, lisäämme seuraavan koodin editoriin.

Private Sub Worksheet_Change (ByVal Target as Range)

    On Error Resume Next

    Jos ei leikkaa(kohde, alue(«Н2:К2»)) on ei mitään ja kohde.solut.määrä = 1 sitten

        Application.EnableEvents = Väärä

        Jos Len (Target.Offset (1, 0)) = 0 Sitten

            Target.Offset (1, 0) = Tavoite

        muu

            Target.End (xlDown) .Offset (1, 0) = Tavoite

        End If

        Target.ClearContents

        Application.EnableEvents = Tosi

    End If

End Sub

Ja lopuksi, tätä koodia käytetään kirjoittamiseen yhteen soluun.

Private Sub Worksheet_Change (ByVal Target as Range)

    On Error Resume Next

    Jos ei leikkaa(kohde, alue(«C2:C5»)) on ei mitään ja kohde.solut.määrä = 1 sitten

        Application.EnableEvents = Väärä

        newVal = Tavoite

        Application.Undo

        oldval = Kohde

        Jos Len (oldval) <> 0 Ja oldval <> newVal Sitten

            Target = Target & «,» & newVal

        muu

            Tavoite = uusiVal

        End If

        Jos Len (newVal) = 0, niin Target.ClearContents

        Application.EnableEvents = Tosi

    End If

End Sub

Alueet ovat muokattavissa.

Kuinka tehdä avattava luettelo haun avulla?

Tässä tapauksessa sinun on aluksi käytettävä erityyppistä luetteloa. "Kehittäjä"-välilehti avautuu, minkä jälkeen sinun täytyy napsauttaa tai napauttaa (jos näyttö on kosketusnäyttö) "Insert" - "ActiveX" -elementtiä. Siinä on yhdistelmälaatikko. Sinua pyydetään piirtämään tämä luettelo, jonka jälkeen se lisätään asiakirjaan.

Lisäksi se konfiguroidaan ominaisuuksien kautta, joissa alue on määritetty ListFillRange-vaihtoehdossa. Solu, jossa käyttäjän määrittämä arvo näytetään, määritetään LinkedCell-vaihtoehdolla. Seuraavaksi sinun tarvitsee vain kirjoittaa ensimmäiset merkit muistiin, koska ohjelma ehdottaa automaattisesti mahdollisia arvoja.

Pudotusvalikko automaattisella tietojen vaihdolla

On myös toiminto, jolla tiedot korvataan automaattisesti, kun ne on lisätty alueelle. Tämä on helppo tehdä:

1. Luo solujoukko tulevaa luetteloa varten. Meidän tapauksessamme tämä on joukko värejä. Valitsemme sen.
   

   

2. Seuraavaksi se on muotoiltava taulukoksi. Sinun on napsautettava samannimistä painiketta ja valittava taulukon tyyli.
   

   

   

Seuraavaksi sinun on vahvistettava tämä alue painamalla "OK" -painiketta.

Valitsemme tuloksena olevan taulukon ja annamme sille nimen sarakkeen A yläosassa olevan syöttökentän kautta.

Siinä on taulukko, ja sitä voidaan käyttää pohjana avattavalle luettelolle, jota varten tarvitset:

1. Valitse solu, jossa luettelo sijaitsee.
2. Avaa Data Validation -valintaikkuna.
   

   

3. Asetamme tietotyypiksi "Lista" ja arvoiksi annamme taulukon nimen =-merkin kautta.
   

   

   

Kaikki, solu on valmis, ja värien nimet näkyvät siinä, kuten alun perin tarvitsimme. Nyt voit lisätä uusia paikkoja yksinkertaisesti kirjoittamalla ne soluun, joka sijaitsee hieman alempana heti viimeisen jälkeen.

Tämä on taulukon etu, että alue kasvaa automaattisesti, kun uusia tietoja lisätään. Näin ollen tämä on kätevin tapa lisätä luettelo.

Kuinka kopioida avattava luettelo?

Kopiointiin riittää näppäinyhdistelmä Ctrl + C ja Ctrl + V. Näin pudotusvalikko kopioidaan muotoilun mukana. Muotoilun poistamiseksi sinun on käytettävä erityistä liittämistä (kontekstivalikossa tämä vaihtoehto tulee näkyviin luettelon kopioimisen jälkeen), jossa on asetettu arvoehdot.

Valitse kaikki solut, jotka sisältävät avattavan luettelon

Tämän tehtävän suorittamiseksi sinun on käytettävä Etsi ja valitse -ryhmän "Valitse soluryhmä" -toimintoa.

Tämän jälkeen avautuu valintaikkuna, jossa sinun tulee valita "Data Validation" -valikosta kohdat "Kaikki" ja "Nämä samat". Ensimmäinen kohde valitsee kaikki luettelot ja toinen vain ne, jotka ovat samanlaisia ​​kuin tietyt luettelot.