Как да направите падащ списък в Excel

Падащият списък е невероятно полезен инструмент, който може да ви помогне да направите работата с информация по-удобна. Тя дава възможност да съдържа няколко стойности в клетка наведнъж, с които можете да работите, както с всички други. За да изберете този, от който се нуждаете, просто щракнете върху иконата със стрелка, след което се показва списък със стойности uXNUMXbuXNUMXbis. След избиране на конкретна, клетката автоматично се попълва с нея и формулите се преизчисляват въз основа на нея.

Excel предоставя много различни методи за генериране на падащо меню и освен това ви позволява гъвкаво да ги персонализирате. Нека анализираме тези методи по-подробно.

Процес на създаване на списък

За да генерирате изскачащо меню, щракнете върху елементите от менюто по пътя „Данни“ – „Проверка на данни“. Ще се отвори диалогов прозорец, където трябва да намерите раздела „Параметри“ и да щракнете върху него, ако не е бил отварян преди. Има много настройки, но елементът „Тип данни“ е важен за нас. От всички значения „Списък“ е правилното.

Броят на методите, чрез които се въвежда информация в изскачащия списък, е доста голям.

1. Независима индикация на елементите на списъка, разделени с точка и запетая в полето „Източник“, разположено в същия раздел на същия диалогов прозорец.
   

   

2. Предварително посочване на стойности. Полето Източник съдържа диапазона, в който е налична необходимата информация.
   

   

3. Задаване на именуван диапазон. Метод, който повтаря предишния, но е необходимо само предварително име на диапазона.
   

   

Всеки от тези методи ще доведе до желания резултат. Нека да разгледаме методите за генериране на падащи списъци в ситуации от реалния живот.

По данни от списъка

Да кажем, че имаме таблица, описваща видовете различни плодове.

За да създадете списък в падащо меню въз основа на този набор от информация, трябва да направите следното:

1. Изберете клетката, запазена за бъдещия списък.
2. Намерете раздела Данни на лентата. Там кликваме върху „Проверка на данните“.
   

   

3. Намерете елемента „Тип данни“ и превключете стойността на „Списък“.
   

   

4. В полето, обозначаващо опцията „Източник“, въведете желания диапазон. Моля, имайте предвид, че абсолютните препратки трябва да бъдат посочени, така че при копиране на списъка информацията да не се измества.
   

Освен това има функция за генериране на списъци наведнъж в повече от една клетка. За да постигнете това, трябва да ги изберете всички и да изпълните същите стъпки, както е описано по-рано. Отново трябва да се уверите, че са написани абсолютни препратки. Ако адресът няма знак за долар до имената на колоните и редовете, тогава трябва да ги добавите, като натискате клавиша F4, докато знакът $ е до имената на колоните и редовете.

С ръчно записване на данни

В горната ситуация списъкът е написан чрез маркиране на необходимия диапазон. Това е удобен метод, но понякога се налага ръчно записване на данните. Това ще позволи да се избегне дублирането на информация в работната книга.

Да предположим, че сме изправени пред задачата да създадем списък, съдържащ два възможни избора: да и не. За да се изпълни задачата е необходимо:

1. Кликнете върху клетката за списъка.
2. Отворете „Данни“ и там намерете познатия ни раздел „Проверка на данни“.
   

   

3. Отново изберете типа „Списък“.
   

   

4. Тук трябва да въведете „Да; Не“ като източник. Виждаме, че информацията се въвежда ръчно с помощта на точка и запетая за изброяване.

След като щракнете върху OK, имаме следния резултат.

След това програмата автоматично ще създаде падащо меню в съответната клетка. Цялата информация, която потребителят е посочил като елементи в изскачащия списък. Правилата за създаване на списък в няколко клетки са подобни на предишните, с единственото изключение, че трябва да посочите информацията ръчно с точка и запетая.

Създаване на падащ списък с помощта на функцията 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. Кликнете върху OK.

Това съдържа функция COUNTIF, за да определите веднага колко клетки са запълнени (въпреки че има много по-голям брой употреби, ние просто го записваме тук за конкретна цел).

За да функционира нормално формулата е необходимо да се проследи дали има празни клетки по пътя на формулата. Те не трябва да бъдат.

Падащ списък с данни от друг лист или Excel файл

Класическият метод не работи, ако трябва да получите информация от друг документ или дори лист, съдържащ се в същия файл. За целта се използва функцията НЕПРЯК, което ви позволява да въведете в правилния формат връзка към клетка, намираща се в друг лист или най-общо – файл. Трябва да направите следното:

1. Активирайте клетката, където поставяме списъка.
2. Отваряме прозореца, който вече познаваме. На същото място, където преди това посочихме източници за други диапазони, във формата е посочена формула =INDIRECT(“[Списък1.xlsx]Лист1!$A$1:$A$9”). Естествено, вместо List1 и Sheet1, можете да вмъкнете съответно имената на книгата и листа. 

внимание! Името на файла е дадено в квадратни скоби. В този случай Excel няма да може да използва файла, който в момента е затворен, като източник на информация.

Трябва също да се отбележи, че самото име на файла има смисъл само ако необходимият документ се намира в същата папка като тази, в която ще бъде вмъкнат списъкът. Ако не, тогава трябва да посочите пълния адрес на този документ.

Създаване на зависими падащи менюта

Зависимият списък е този, чието съдържание се влияе от избора на потребителя в друг списък. Да предположим, че имаме отворена таблица пред нас, която съдържа три диапазона, на всеки от които е дадено име.

Трябва да следвате тези стъпки, за да генерирате списъци, чийто резултат се влияе от опцията, избрана в друг списък.

1. Създайте 1-ви списък с имена на диапазони.
   

   

2. На входната точка на източника необходимите индикатори се маркират един по един.
   

   

3. Създайте втори списък в зависимост от вида растение, което човекът е избрал. Като алтернатива, ако посочите дървета в първия списък, тогава информацията във втория списък ще бъде „дъб, габър, кестен“ и повече. Необходимо е да запишете формулата на мястото на въвеждане на източника на данни =ИНДИРЕКТНО(E3). E3 – клетка, съдържаща името на диапазона 1.=INDIRECT(E3). E3 – клетка с името на списъка 1.

Сега всичко е готово.

Как да изберете няколко стойности от падащ списък?

Понякога не е възможно да се даде предпочитание само на една стойност, така че трябва да се изберат повече от една. След това трябва да добавите макрос към кода на страницата. Използването на клавишната комбинация Alt + F11 отваря редактора на Visual Basic. И кодът се вмъква там.

Private Sub Worksheet_Change (ByVal Target As Range)

    При грешка се възобнови при следващото

    Ако 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) = Цел

        крайна сметка, ако

        Target.ClearContents

        Application.EnableEvents = Вярно

    крайна сметка, ако

End Sub 

За да се покаже съдържанието на клетките по-долу, вмъкваме следния код в редактора.

Private Sub Worksheet_Change (ByVal Target As Range)

    При грешка се възобнови при следващото

    Ако Not Intersect(Target, Range(«Н2:К2»)) е нищо и Target.Cells.Count = 1 тогава

        Application.EnableEvents = False

        Ако Len (Target.Offset (1, 0)) = 0 Тогава

            Target.Offset (1, 0) = Цел

        още

            Target.End (xlDown) .Offset (1, 0) = Цел

        крайна сметка, ако

        Target.ClearContents

        Application.EnableEvents = Вярно

    крайна сметка, ако

End Sub

И накрая, този код се използва за запис в една клетка.

Private Sub Worksheet_Change (ByVal Target As Range)

    При грешка се възобнови при следващото

    Ако Not Intersect(Target, Range(«C2:C5»)) е нищо и Target.Cells.Count = 1 Тогава

        Application.EnableEvents = False

        newVal = Цел

        Приложение. Отмяна

        oldval = Цел

        Ако Len (oldval) <> 0 И oldval <> newVal Тогава

            Target = Target & «,» & newVal

        още

            Цел = newVal

        крайна сметка, ако

        Ако Len (newVal) = 0, тогава Target.ClearContents

        Application.EnableEvents = Вярно

    крайна сметка, ако

End Sub

Диапазоните могат да се редактират.

Как да направя падащ списък с търсене?

В този случай първоначално трябва да използвате различен тип списък. Отваря се разделът „Разработчик“, след което трябва да щракнете или докоснете (ако екранът е сензорен) върху елемента „Вмъкване“ – „ActiveX“. Има комбинирана кутия. Ще бъдете подканени да нарисувате този списък, след което той ще бъде добавен към документа.

Освен това се конфигурира чрез свойства, където диапазонът е посочен в опцията ListFillRange. Клетката, в която се показва дефинираната от потребителя стойност, се конфигурира с помощта на опцията LinkedCell. След това просто трябва да запишете първите знаци, тъй като програмата автоматично ще предложи възможни стойности.

Падащ списък с автоматично заместване на данни

Има и функция, че данните се заместват автоматично след добавянето им към диапазона. Лесно е да направите това:

1. Създайте набор от клетки за бъдещия списък. В нашия случай това е набор от цветове. Ние го избираме.
   

   

2. След това трябва да се форматира като таблица. Трябва да щракнете върху бутона със същото име и да изберете стила на таблицата.
   

   

   

След това трябва да потвърдите този диапазон, като натиснете бутона „OK“.

Избираме получената таблица и й даваме име чрез полето за въвеждане, разположено в горната част на колона A.

Това е всичко, има таблица и тя може да се използва като основа за падащ списък, за който ви трябва:

1. Изберете клетката, в която се намира списъкът.
2. Отворете диалоговия прозорец за проверка на данните.
   

   

3. Задаваме типа данни на „Списък“ и като стойности даваме името на таблицата чрез знака =.
   

   

   

Всичко, клетката е готова и имената на цветовете са показани в нея, както първоначално ни трябваше. Сега можете да добавяте нови позиции просто като ги напишете в клетка, разположена малко по-ниско непосредствено след последната.

Това е предимството на таблицата, че диапазонът автоматично се увеличава при добавяне на нови данни. Съответно това е най-удобният начин за добавяне на списък.

Как да копирам падащ списък?

За копиране е достатъчно да използвате клавишната комбинация Ctrl + C и Ctrl + V. Така падащият списък ще бъде копиран заедно с форматирането. За да премахнете форматирането, трябва да използвате специална паста (в контекстното меню тази опция се появява след копиране на списъка), където е зададена опцията „условия за стойности“.

Изберете всички клетки, съдържащи падащ списък

За да изпълните тази задача, трябва да използвате функцията „Избор на група клетки“ в групата „Намиране и избиране“.

След това ще се отвори диалогов прозорец, в който трябва да изберете елементите „Всички“ и „Същите“ в менюто „Проверка на данни“. Първият елемент избира всички списъци, а вторият избира само тези, които са подобни на определени.