Како да направите паѓачка листа во Excel

Паѓачката листа е неверојатно корисна алатка која може да помогне да се направи работата со информации поудобна. Овозможува да содржи неколку вредности во ќелија одеднаш, со кои можете да работите, како и со сите други. За да го изберете оној што ви треба, само кликнете на иконата со стрелка, по што ќе се прикаже список со вредности uXNUMXbuXNUMXbis. По изборот на одредена, ќелијата автоматски се пополнува со неа, а формулите повторно се пресметуваат врз основа на тоа.

Excel обезбедува многу различни методи за генерирање на паѓачкото мени, а покрај тоа, ви овозможува флексибилно да ги приспособите. Ајде да ги анализираме овие методи подетално.

Процес на креирање листа

За да генерирате скокачко мени, кликнете на ставките од менито долж патеката „Податоци“ – „Валидација на податоци“. Ќе се отвори дијалог-кутија каде што треба да го пронајдете табулаторот „Параметри“ и да кликнете на него ако претходно не бил отворен. Има многу поставки, но ставката „Тип на податоци“ е важна за нас. Од сите значења, „Список“ е вистинското.

Бројот на методи со кои информациите се внесуваат во скокачката листа е доста голем.

1. Независна ознака на елементите на списокот одделени со точка-запирка во полето „Извор“ што се наоѓа на истото јазиче од истиот дијалог прозорец.
   

   

2. Прелиминарно наведување на вредностите. Полето Извор го содржи опсегот каде што се достапни бараните информации.
   

   

3. Одредување на именуван опсег. Метод кој го повторува претходниот, но потребно е само прелиминарно да се именува опсегот.
   

   

Било кој од овие методи ќе го даде посакуваниот резултат. Да ги погледнеме методите за генерирање паѓачки списоци во реални ситуации.

Врз основа на податоците од списокот

Да речеме дека имаме табела која ги опишува видовите на различни овошја.

За да креирате листа во паѓачкото мени врз основа на овој сет на информации, треба да го направите следново:

1. Изберете ја ќелијата резервирана за идната листа.
2. Најдете го табот Податоци на лентата. Таму кликнуваме на „Потврди податоци“.
   

   

3. Најдете ја ставката „Тип на податоци“ и префрлете ја вредноста на „Список“.
   

   

4. Во полето што ја означува опцијата „Извор“, внесете го саканиот опсег. Ве молиме имајте предвид дека апсолутните референци мора да се наведат така што при копирање на списокот, информациите не се поместуваат.
   

Покрај тоа, постои функција за генерирање списоци одеднаш во повеќе од една ќелија. За да го постигнете ова, треба да ги изберете сите и да ги извршите истите чекори како што е опишано претходно. Повторно, треба да бидете сигурни дека се напишани апсолутни референци. Ако адресата нема знак за долар до имињата на колоните и редовите, тогаш треба да ги додадете со притискање на копчето F4 додека знакот $ не се најде веднаш до имињата на колоните и редовите.

Со рачно снимање на податоци

Во горната ситуација, списокот беше напишан со истакнување на потребниот опсег. Ова е пригоден метод, но понекогаш е неопходно рачно да се снимаат податоците. Ова ќе овозможи да се избегне дуплирање на информациите во работната книга.

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

1. Кликнете на ќелијата за списокот.
2. Отворете „Податоци“ и таму најдете го делот „Проверка на податоци“ познат нам.
   

   

3. Повторно, изберете го типот „Список“.
   

   

4. Тука треба да внесете „Да; Не“, како извор. Гледаме дека информациите се внесуваат рачно со помош на точка-запирка за набројување.

Откако ќе кликнете OK, го имаме следниот резултат.

Следно, програмата автоматски ќе создаде паѓачко мени во соодветната ќелија. Сите информации што корисникот ги навел како ставки во скокачката листа. Правилата за креирање листа во неколку ќелии се слични на претходните, со единствен исклучок што мора рачно да ги наведете информациите со помош на точка-запирка.

Креирање на паѓачка листа со помош на функцијата OFFSET

Покрај класичниот метод, можно е да се користи функцијата РАБОТУВАЕда генерирате паѓачки менија.

Ајде да го отвориме листот.

За да ја користите функцијата за паѓачката листа, треба да го направите следново:

1. Изберете ја ќелијата од интерес каде што сакате да ја поставите идната листа.
2. Отворете го табулаторот „Податоци“ и прозорецот „Валидација на податоци“ во низа.
   

   

3. Поставете „Список“. Ова е направено на ист начин како и претходните примери. Конечно, се користи следнава формула: =ОФСЕТ (А$2$;0;0;5). Го внесуваме таму каде што се наведени ќелиите кои ќе се користат како аргумент.

Потоа програмата ќе создаде мени со листа на овошја.

Синтаксата за ова е:

=OFFSET(референца,поместување_линија,поместување_колона,[висина],[ширина])

Гледаме дека оваа функција има 5 аргументи. Прво, се дава првата адреса на ќелијата што треба да се помести. Следните два аргументи одредуваат колку редови и колони треба да се поместат. Кога сме кај нас, аргументот Висина е 5 бидејќи ја претставува висината на листата. 

Паѓачка листа во Excel со замена на податоци (+ користејќи ја функцијата OFFSET)

Во дадениот случај РАБОТУВАЕ дозволено е да се создаде скокачко мени лоцирано во фиксен опсег. Недостаток на овој метод е што по додавањето на ставката, ќе мора сами да ја уредувате формулата.

За да креирате динамична листа со поддршка за внесување нови информации, мора:

1. Изберете ја ќелијата од интерес.
2. Проширете го табулаторот „Податоци“ и кликнете на „Валидација на податоци“.
3. Во прозорецот што се отвора, повторно изберете ја ставката „Список“ и наведете ја следнава формула како извор на податоци: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Кликнете OK.

Ова содржи функција COUNTIF, веднаш да одредиме колку ќелии се пополнети (иако има многу поголем број на употреби, само го пишуваме овде за одредена цел).

За да може формулата да функционира нормално, потребно е да се следи дали има празни ќелии на патеката на формулата. Тие не треба да бидат.

Паѓачка листа со податоци од друг лист или датотека Excel

Класичниот метод не функционира ако треба да добиете информации од друг документ или дури и лист содржан во истата датотека. За ова се користи функцијата ИНДИРЕКТЕН, кој ви овозможува да внесете во правилен формат врска до ќелија лоцирана во друг лист или воопшто - датотека. Треба да го направите следново:

1. Активирајте ја ќелијата каде што ја ставаме листата.
2. Го отвораме прозорецот што веќе го знаеме. На истото место каде што претходно наведовме извори за други опсези, формулата е означена во форматот =ИНДИРЕКТ („[List1.xlsx]Лист 1!$A$1:$A$9“). Секако, наместо List1 и Sheet1, можете да ги вметнете имињата на вашите книги и листови, соодветно. 

Внимание! Името на датотеката е дадено во квадратни загради. Во овој случај, Excel нема да може да ја користи датотеката што моментално е затворена како извор на информации.

Исто така, треба да се забележи дека самото име на датотеката има смисла само ако бараниот документ се наоѓа во истата папка со онаа каде што ќе се вметне списокот. Ако не, тогаш мора целосно да ја наведете адресата на овој документ.

Креирање на зависни паѓачки списоци

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

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

1. Создадете прва листа со имиња на опсег.
   

   

2. На влезната точка на изворот, потребните индикатори се истакнуваат еден по еден.
   

   

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

Сега сè е подготвено.

Како да изберете повеќе вредности од паѓачката листа?

Понекогаш не е можно да се даде предност само на една вредност, па затоа мора да се изберат повеќе од една. Потоа треба да додадете макро на кодот на страницата. Користењето на комбинацијата на копчиња Alt + F11 го отвора уредникот на Visual Basic. И кодот е вметнат таму.

Приватен под работен лист_Промена (ByVal Цел како опсег)

    На Грешка продолжи следната

    Ако не се вкрстува(Цел, опсег («Е2:Е9»)) Не е ништо и цел. Ќелии. Број = 1 Тогаш

        Application.EnableEvents = Неточно

        Ако Лен (Target.Offset (0, 1)) = 0 Потоа

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

        друго

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

        Ако крајот

        Цел.ClearContents

        Application.EnableEvents = Точно

    Ако крајот

Под крајот 

За да може содржината на ќелиите да биде прикажана подолу, го вметнуваме следниот код во уредникот.

Приватен под работен лист_Промена (ByVal Цел како опсег)

    На Грешка продолжи следната

    If Not Intersect(Target, Range(«Н2:К2»)) Is Nothing And Target.Cells.Count = 1 Потоа

        Application.EnableEvents = Неточно

        Ако Лен (Target.Offset (1, 0)) = 0 Потоа

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

        друго

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

        Ако крајот

        Цел.ClearContents

        Application.EnableEvents = Точно

    Ако крајот

Под крајот

И, конечно, овој код се користи за пишување во една ќелија.

Приватен под работен лист_Промена (ByVal Цел како опсег)

    На Грешка продолжи следната

    Ако не се вкрстува (Цел, опсег («C2: C5»)) не е ништо и цел. клетки. Број = 1 Потоа

        Application.EnableEvents = Неточно

        newVal = Цел

        Апликација.Врати

        олдвал = Цел

        Ако Лен (олдвал) <> 0 И олдвал <> новВал Тогаш

            Target = Target & «,» & newVal

        друго

            Цел = newVal

        Ако крајот

        Ако Лен (newVal) = 0 Потоа Target.ClearContents

        Application.EnableEvents = Точно

    Ако крајот

Под крајот

Опсезите се уредуваат.

Како да направите паѓачка листа со пребарување?

Во овој случај, првично мора да користите друг тип на листа. Се отвора табулаторот „Програмер“, по што треба да кликнете или допрете (ако екранот е допир) на елементот „Вметни“ - „ActiveX“. Има комбинирана кутија. Ќе ви биде побарано да ја нацртате оваа листа, по што ќе биде додадена во документот.

Понатаму, тој е конфигуриран преку својства, каде што опсегот е наведен во опцијата ListFillRange. Ќелијата каде што е прикажана вредноста дефинирана од корисникот е конфигурирана со помош на опцијата LinkedCell. Следно, само треба да ги запишете првите знаци, бидејќи програмата автоматски ќе предложи можни вредности.

Паѓачка листа со автоматска замена на податоци

Исто така, постои функција дека податоците се заменуваат автоматски откако ќе се додадат во опсегот. Лесно е да се направи ова:

1. Направете збир на ќелии за идната листа. Во нашиот случај, ова е збир на бои. Ние го избираме.
   

   

2. Следно, треба да се форматира како табела. Треба да кликнете на копчето со исто име и да го изберете стилот на табелата.
   

   

   

Следно, треба да го потврдите овој опсег со притискање на копчето „OK“.

Ја избираме добиената табела и и даваме име преку полето за внесување кое се наоѓа на врвот на колоната А.

Тоа е сè, има табела и може да се користи како основа за паѓачка листа, за која ви треба:

1. Изберете ја ќелијата каде што се наоѓа списокот.
2. Отворете го дијалогот за валидација на податоци.
   

   

3. Го поставуваме типот на податоци на „Листа“, а како вредности го даваме името на табелата преку знакот =.
   

   

   

Сè, ќелијата е подготвена, а во неа се прикажани имињата на боите, како што првично ни требаше. Сега можете да додавате нови позиции едноставно со запишување во ќелија која се наоѓа малку пониско веднаш по последната.

Ова е предноста на табелата, што опсегот автоматски се зголемува кога се додаваат нови податоци. Според тоа, ова е најзгодниот начин за додавање листа.

Како да се копира паѓачката листа?

За копирање, доволно е да ја користите комбинацијата на копчиња Ctrl + C и Ctrl + V. Така, паѓачката листа ќе се копира заедно со форматирањето. За да го отстраните форматирањето, треба да користите специјална паста (во контекстното мени, оваа опција се појавува по копирањето на списокот), каде што е поставена опцијата „услови за вредности“.

Изберете ги сите ќелии што содржат паѓачка листа

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

После тоа, ќе се отвори дијалог-кутија, каде што треба да ги изберете ставките „Сите“ и „Истите истите“ во менито „Валидација на податоци“. Првата ставка ги избира сите списоци, а втората ги избира само оние што се слични на одредени.