Як зрабіць выпадальны спіс у Excel

Выпадальны спіс - гэта неверагодна карысны інструмент, які можа дапамагчы зрабіць працу з інфармацыяй больш камфортнай. Гэта дае магчымасць утрымліваць у ячэйцы адразу некалькі значэнняў, з якімі можна працаваць, як і з любымі іншымі. Каб выбраць той, які вам патрэбны, проста націсніце на значок стрэлкі, пасля чаго адлюструецца спіс значэнняў uXNUMXbuXNUMXbis. Пасля выбару пэўнай вочка аўтаматычна запаўняецца ёю, і на яе аснове пераразлічваюцца формулы.

Excel дае шмат розных метадаў для стварэння выпадальнага меню, і, акрамя таго, ён дазваляе гнутка іх наладжваць. Разбяром гэтыя спосабы падрабязней.

Працэс стварэння спісу

Каб згенераваць ўсплывальнае меню, націсніце на пункты меню па шляху «Дадзеныя» - «Праверка даных». Адкрыецца дыялогавае акно, у якім трэба знайсці ўкладку «Параметры» і націснуць на яе, калі яна раней не адкрывалася. У ім шмат налад, але для нас важны пункт «Тып дадзеных». З усіх значэнняў «Спіс» з'яўляецца правільным.

Колькасць метадаў, якімі інфармацыя ўносіцца ва ўсплывальны спіс, даволі вялікая.

1. Самастойнае ўказанне элементаў спісу праз кропку з коскай у полі «Крыніца», якое знаходзіцца на той жа ўкладцы таго ж дыялогавага акна.
   

   

2. Папярэдняе ўказанне значэнняў. Поле "Крыніца" змяшчае дыяпазон, дзе даступная неабходная інфармацыя.
   

   

3. Вызначэнне найменнага дыяпазону. Спосаб, які паўтарае папярэдні, але трэба толькі папярэдне назваць дыяпазон.
   

   

Любы з гэтых метадаў дасць жаданы вынік. Давайце разгледзім метады стварэння выпадальных спісаў у рэальных сітуацыях.

На аснове дадзеных са спісу

Дапусцім, у нас ёсць табліца з апісаннем відаў розных садавіны.

Каб стварыць спіс у выпадальным меню на аснове гэтай інфармацыі, вам трэба зрабіць наступнае:

1. Выберыце вочка, зарэзерваваны для будучага спісу.
2. Знайдзіце ўкладку «Дадзеныя» на стужцы. Там націскаем «Праверыць дадзеныя».
   

   

3. Знайдзіце пункт «Тып дадзеных» і пераключыце значэнне на «Спіс».
   

   

4. У поле, якое пазначае параметр «Крыніца», увядзіце патрэбны дыяпазон. Звяртаем вашу ўвагу на тое, што абсалютныя спасылкі павінны быць указаны, каб пры капіраванні спісу інфармацыя не зрушвалася.
   

Акрамя таго, ёсць функцыя для стварэння спісаў адразу ў больш чым адной вочку. Каб дасягнуць гэтага, вы павінны выбраць іх усе і выканаць тыя ж крокі, што апісаны раней. Зноў жа, вам трэба пераканацца, што напісаны абсалютныя спасылкі. Калі ў адрасе няма знака даляра побач з імёнамі слупкоў і радкоў, то вам неабходна дадаць іх, націскаючы клавішу F4, пакуль знак $ не з'явіцца побач з імёнамі слупкоў і радкоў.

З ручным запісам дадзеных

У прыведзенай вышэй сітуацыі спіс быў напісаны шляхам вылучэння неабходнага дыяпазону. Гэта зручны спосаб, але часам даводзіцца запісваць дадзеныя ўручную. Гэта дасць магчымасць пазбегнуць дубліравання звестак у працоўнай кніжцы.

Выкажам здагадку, што перад намі стаіць задача стварыць спіс з двума магчымымі варыянтамі: так і не. Для выканання задачы неабходна:

1. Націсніце на вочка для спісу.
2. Адкрываем «Дадзеныя» і знаходзім там знаёмы нам раздзел «Праверка дадзеных».
   

   

3. Зноў абярыце тып «Спіс».
   

   

4. Тут трэба ўвесці «Так; Не» як крыніца. Мы бачым, што інфармацыя ўводзіцца ўручную з дапамогай кропкі з коскай для пералічэння.

Пасля націску ОК мы маем наступны вынік.

Далей праграма аўтаматычна створыць выпадальнае меню ў адпаведнай вочку. Уся інфармацыя, якую карыстальнік пазначыў у якасці элементаў ва ўсплываючым спісе. Правілы стварэння спісу ў некалькі вочак аналагічныя папярэднім, з тым толькі выключэннем, што ўказваць інфармацыю трэба ўручную праз кропку з коскі.

Стварэнне выпадальнага спісу з дапамогай функцыі OFFSET

Акрамя класічнага метаду, магчыма выкарыстанне функцыі ВЫКАРЫСТАННЕдля стварэння выпадаючых меню.

Адкрыем аркуш.

Каб выкарыстоўваць функцыю выпадальнага спісу, вам трэба зрабіць наступнае:

1. Выберыце цікавую ячэйку, у якую вы хочаце змясціць будучы спіс.
2. Паслядоўна адкрыйце ўкладку «Дадзеныя» і акно «Праверка даных».
   

   

3. Усталюйце «Спіс». Робіцца гэта гэтак жа, як і ў папярэдніх прыкладах. Нарэшце, выкарыстоўваецца наступная формула: =ЗРУШЭННЕ(A$2$;0;0;5). Ўводзім яго там, дзе пазначаны вочкі, якія будуць выкарыстоўвацца ў якасці аргументу.

Затым праграма створыць меню са спісам садавіны.

Сінтаксіс гэтага:

=ЗРУШЭННЕ(спасылка,зрушэнне_радка,зрушэнне_слупка,[вышыня],[шырыня])

Мы бачым, што гэтая функцыя мае 5 аргументаў. Спачатку задаецца першы адрас ячэйкі, які будзе зрушаны. Наступныя два аргументы вызначаюць, колькі радкоў і слупкоў трэба зрушыць. Калі казаць пра нас, аргумент Height роўны 5, таму што ён уяўляе вышыню спісу. 

Выпадальны спіс у Excel з падстаноўкай дадзеных (+ выкарыстанне функцыі OFFSET)

У дадзеным выпадку ВЫКАРЫСТАННЕ дазваляе ствараць усплывальнае меню, размешчанае ў фіксаваным дыяпазоне. Недахопам гэтага метаду з'яўляецца тое, што пасля дадання элемента вам прыйдзецца самастойна рэдагаваць формулу.

Каб стварыць дынамічны спіс з падтрымкай уводу новай інфармацыі, неабходна:

1. Выберыце цікавую ячэйку.
2. Разгарніце ўкладку «Дадзеныя» і націсніце «Праверка даных».
3. У якое адкрылася акне зноў абярыце пункт «Спіс» і ў якасці крыніцы дадзеных пакажыце наступную формулу: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Націсніце кнопку ОК.

Гэта змяшчае функцыю COUNTIF, каб адразу вызначыць, колькі вочак запоўнена (хоць ён мае значна большую колькасць ужыванняў, мы проста пішам яго тут для пэўнай мэты).

Для нармальнага функцыянавання формулы неабходна прасачыць, ці няма на шляху формулы пустых вочак. Яны не павінны быць.

Выпадальны спіс з дадзенымі з іншага аркуша або файла Excel

Класічны метад не працуе, калі вам трэба атрымаць інфармацыю з іншага дакумента або нават ліста, які змяшчаецца ў тым жа файле. Для гэтага выкарыстоўваецца функцыя ускосна, які дазваляе ўвесці ў правільным фармаце спасылку на вочка, размешчаную на іншым аркушы або наогул – файл. Вам трэба зрабіць наступнае:

1. Актывуйце ячэйку, у якую мы змяшчаем спіс.
2. Адкрываем знаёмае нам акно. Там жа, дзе мы раней паказвалі крыніцы для іншых дыяпазонаў, у фармаце паказваецца формула =INDIRECT(“[Спіс1.xlsx]Аркуш1!$A$1:$A$9”). Натуральна, замест List1 і Sheet1 вы можаце ўставіць назвы вашай кнігі і аркуша адпаведна. 

Увага! Імя файла даецца ў квадратных дужках. У гэтым выпадку Excel не зможа выкарыстоўваць у якасці крыніцы інфармацыі закрыты файл.

Варта таксама адзначыць, што сама назва файла мае сэнс толькі ў тым выпадку, калі неабходны дакумент знаходзіцца ў той жа тэчцы, што і тая, у якую будзе ўстаўлены спіс. Калі няма, то неабходна ўказаць адрас гэтага дакумента цалкам.

Стварэнне залежных выпадаючых спісаў

Залежны спіс - гэта спіс, на змест якога ўплывае выбар карыстальніка ў іншым спісе. Выкажам здагадку, што перад намі адкрытая табліца, якая змяшчае тры дыяпазоны, кожнаму з якіх дадзена імя.

Вам неабходна выканаць наступныя крокі, каб стварыць спісы, на вынік якіх уплывае параметр, абраны ў іншым спісе.

1. Стварыце першы спіс з назвамі дыяпазонаў.
   

   

2. У кропцы ўваходу ў крыніцу па чарзе выдзяляюцца патрэбныя індыкатары.
   

   

3. Стварыце 2-і спіс у залежнасці ад тыпу расліны, які абраў чалавек. У іншым выпадку, калі вы ўказваеце дрэвы ў першым спісе, то інфармацыя ў другім спісе будзе «дуб, граб, каштан» і далей. Запісваць формулу неабходна ў месцы ўводу крыніцы дадзеных =УСКОСНЫ(E3). E3 – ячэйка, якая змяшчае імя дыяпазону 1.=INDIRECT(E3). E3 – ячэйка з назвай спісу 1.

Цяпер усё гатова.

Як выбраць некалькі значэнняў з выпадальнага спісу?

Часам немагчыма аддаць перавагу толькі аднаму значэнню, таму трэба выбраць некалькі. Затым вам трэба дадаць макрас у код старонкі. Спалучэнне клавіш Alt + F11 адкрывае рэдактар ​​Visual Basic. І код туды ўстаўляецца.

Прыватны падрабочы ліст_Change (ByVal Target As Range)

    On Error Resume Next

    Калі Not Intersect(Target, Range(«Е2:Е9»)) нічога і Target.Cells.Count = 1 Тады

        Application.EnableEvents = False

        Калі Len (Target.Offset (0, 1)) = 0 Тады

            Target.Offset (0, 1) = Мэта

        яшчэ

            Target.End (xlToRight) .Offset (0, 1) = Мэта

        End If

        Target.ClearContents

        Application.EnableEvents = Праўда

    End If

End Sub 

Каб змесціва вочак было паказана ніжэй, мы ўстаўляем наступны код у рэдактар.

Прыватны падрабочы ліст_Change (ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(«Н2:К2»)) Is Nothing And Target.Cells.Count = 1 Тады

        Application.EnableEvents = False

        Калі Len (Target.Offset (1, 0)) = 0 Тады

            Target.Offset (1, 0) = Мэта

        яшчэ

            Target.End (xlDown) .Offset (1, 0) = Мэта

        End If

        Target.ClearContents

        Application.EnableEvents = Праўда

    End If

End Sub

І, нарэшце, гэты код выкарыстоўваецца для запісу ў адну ячэйку.

Прыватны падрабочы ліст_Change (ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(«C2:C5»)) is Nothing And Target.Cells.Count = 1 Тады

        Application.EnableEvents = False

        newVal = Мэта

        Прыкладанне.Адмяніць

        oldval = Мэта

        Калі Len (oldval) <> 0 і oldval <> newVal Тады

            Target = Target & «,» & newVal

        яшчэ

            Мэта = newVal

        End If

        Калі Len (newVal) = 0, то Target.ClearContents

        Application.EnableEvents = Праўда

    End If

End Sub

Дыяпазоны можна рэдагаваць.

Як зрабіць выпадальны спіс з пошукам?

У гэтым выпадку вы павінны першапачаткова выкарыстоўваць іншы тып спісу. Адкрываецца ўкладка «Распрацоўшчык», пасля чаго трэба клікнуць або тапнуть (калі экран сэнсарны) па элеменце «Устаўка» - «ActiveX». Ён мае камбінаваны спіс. Вам будзе прапанавана намаляваць гэты спіс, пасля чаго ён будзе дададзены ў дакумент.

Далей ён наладжваецца праз уласцівасці, дзе ў параметре ListFillRange задаецца дыяпазон. Ячэйка, у якой адлюстроўваецца вызначанае карыстальнікам значэнне, наладжваецца з дапамогай опцыі LinkedCell. Далей трэба толькі запісаць першыя сімвалы, як праграма аўтаматычна прапануе магчымыя значэння.

Выпадальны спіс з аўтаматычнай падстаноўкай дадзеных

Таксама ёсць функцыя аўтаматычнай замены дадзеных пасля іх дадання ў дыяпазон. Зрабіць гэта лёгка:

1. Стварыце набор вочак для будучага спісу. У нашым выпадку гэта набор колераў. Выбіраем яго.
   

   

2. Далей яго трэба аформіць у выглядзе табліцы. Трэба націснуць аднайменную кнопку і выбраць стыль табліцы.
   

   

   

Далей трэба пацвердзіць гэты дыяпазон, націснуўшы кнопку «ОК».

Выбіраем атрыманую табліцу і даем ёй імя праз поле ўводу, размешчанае зверху слупка А.

Вось і ўсё, табліца ёсць, і яе можна выкарыстоўваць як аснову для выпадальнага спісу, для якога неабходна:

1. Выберыце ячэйку, дзе знаходзіцца спіс.
2. Адкрыйце дыялогавае акно праверкі даных.
   

   

3. Задаем тып дадзеных «Спіс», а ў якасці значэнняў даем назву табліцы праз знак =.
   

   

   

Усё, клетка гатовая, і ў ёй намаляваныя назвы кветак, як нам і трэба было першапачаткова. Цяпер вы можаце дадаваць новыя пазіцыі, проста запісваючы іх у вочка, размешчаную крыху ніжэй, адразу пасля апошняй.

Гэта перавага табліцы, што дыяпазон аўтаматычна павялічваецца пры даданні новых дадзеных. Адпаведна, гэта самы зручны спосаб дадання спісу.

Як скапіяваць выпадальны спіс?

Каб скапіяваць, дастаткова скарыстацца камбінацыяй клавіш Ctrl + C і Ctrl + V. Так выпадальны спіс будзе скапіяваны разам з фарматаваннем. Для выдалення фарматавання неабходна скарыстацца спецыяльнай устаўкай (у кантэкстным меню гэтая опцыя з'яўляецца пасля капіравання спісу), дзе задаецца параметр «Умовы на значэнні».

Выберыце ўсе ячэйкі, якія змяшчаюць выпадальны спіс

Для выканання гэтай задачы неабходна выкарыстоўваць функцыю «Вылучыць групу вочак» у групе «Знайсці і вылучыць».

Пасля гэтага адкрыецца дыялогавае акно, у якім у меню «Праверка дадзеных» неабходна выбраць пункты «Усе» і «Тыя самыя». Першы пункт выбірае ўсе спісы, а другі - толькі тыя, якія падобныя на пэўныя.