Как да направя сложен падащ списък в ексел?

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

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

Създайте прост падащ списък

За да направите това, в клетки A1: A7 въвеждаме данните, които ще бъдат показани в списъка. Сега изберете клетката, в която ще създадем падащ списък - B2.

Отидете в раздела „Данни“ и щракнете върху бутона „Проверка на данните“.

В раздела "Опции", в полето "Тип данни", изберете "Списък". Можете да въведете стойности в полето Източник по различни начини:

1 - въведете ръчно стойностите за списъка, разделени с точка и запетая;

2 - задайте диапазона от клетки, в който се въвеждат данните за падащия списък;

3 - изберете клетките с имена, щракнете с десния бутон върху тях и изберете "Присвояване на име" от менюто.

Изберете клетка B2 и поставете "=" в полето "Източник", след което напишете създаденото име.

Така създадохме прост падащ списък в Excel.

Ако имате заглавка на колона и трябва да попълните всеки ред със стойности, тогава изберете не една клетка, а диапазон от клетки - B2:B9. След това ще бъде възможно да изберете желаната стойност във всяка клетка от падащия списък.

Добавяне на стойности към падащия списък - динамичен списък

В този случай ще добавим стойности към желания диапазон и те автоматично ще бъдат добавени към падащия списък.

Изберете диапазона от клетки - D1:D8, след това в раздела "Начало" щракнете върху "Форматиране като таблица" и изберете произволен стил.

Потвърждаваме местоположението на данните и поставяме отметка в полето „Таблица със заглавки“.

Най-отгоре пишем заглавието на таблицата - "Служители", и я попълваме с данни.

Изберете клетката, която ще съдържа падащия списък и щракнете върху бутона „Проверка на данни“. В следващия прозорец в полето "Източник" напишете следното: =INDIRECT("Table1"). Имам една таблица на лист, така че пиша „Таблица1“, ако има втора - „Таблица2“ и т.н.

Сега нека добавим ново име на служител към нашия списък: Ира. Появи се в падащия списък. Ако премахнем някое име от таблицата, то също ще бъде премахнато от списъка.

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

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

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

Отидете на лист 1, поставете курсора в полето "Източник" и изберете желания диапазон от клетки.

Сега можете да добавяте имена на лист 1, те ще бъдат добавени към падащите списъци на лист 2.

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

Да кажем, че имаме три диапазона: имена, фамилии и бащи имена на служителите. За всеки трябва да зададете име. Избираме клетките от този диапазон, той също може да бъде празен - с течение на времето ще бъде възможно да добавите данни към тях, които ще се появят в падащия списък. Щракваме върху тях с десния бутон на мишката и избираме "Присвояване на име" от списъка.

Първият се нарича "Име", вторият - "Фамилия", третият - "Баща".

Нека направим друг диапазон, в който ще бъдат записани зададените имена. Нека го наречем "Служители".

Правим първия падащ списък, който ще се състои от имената на диапазоните. Изберете клетка E1 и в раздела Данни изберете Проверка на данни.

В полето "Тип данни" изберете "Списък", в полето източник - или въведете "=Служители", или изберете диапазон от клетки, на които е дадено име.

Първият падащ списък е създаден. Сега в клетка F2 ще създадем втори списък, който трябва да зависи от първия. Ако в първия изберем “Собствено име”, във втория ще се покаже списък с фамилни имена, ако изберем “Фамилно име” – списък с фамилни имена.

Изберете клетката и щракнете върху бутона Проверка на данните. В полето "Тип данни" изберете "Списък", в полето източник въведете следното: =INDIRECT($E$1). Тук E1 е клетката с първия падащ списък.

По този принцип можете да правите зависими падащи списъци.

Ако в бъдеще ще трябва да въведете стойностите в диапазона, на който е дадено името, например „Фамилно име“. Отидете в раздела „Формули“ и щракнете върху „Мениджър на имена“. Сега в името на диапазона изберете "Фамилия", а в долната част вместо последната клетка C3 напишете C10. Кликнете върху отметката. След това диапазонът ще се увеличи и към него ще бъде възможно да се добавят данни, които автоматично ще се появят в падащия списък.

Сега знаете как да направите падащ списък в Excel.

Как да създадете падащ списък, състоящ се от няколко клетки наведнъж (да кажем, че името е с цена)

Благодаря, всичко работи добре.

Падащият списък със стойности от друг лист не работи, тъй като прозорецът, когато проверката на данните е отворена, не позволява работа с други прозорци, особено с друг лист!

Зависимото падащо меню ви позволява да направите трик, който често се хвали от потребителите на шаблони на Excel. Трик, който прави работата по-лесна и по-бърза. Трик, който ще направи вашите форми удобни и приятни.

Пример за създаване на зависим падащ списък в клетка на Excel

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

Всеки продавач първо идентифицира продуктова група, а след това конкретен продукт от тази група. Формулярът трябва да включва пълното име на групата и конкретен индекс на артикула. Тъй като писането на това на ръка би отнело твърде много време (и досадно), измислих много бързо и лесно решение - 2 зависими падащи менюта.

Първият беше списък на всички продуктови категории, вторият беше списък на всички продукти в избраната категория. Затова създадох падащ списък в зависимост от избора, направен в предишния списък (тук ще намерите материал за това как да създадете два зависими падащи списъка).

Потребителят на шаблона за домашен бюджет иска да получи същия резултат, където са необходими категорията и подкатегорията на разходите. Пример за данни е на фигурата по-долу:

Така например, ако изберем категорията Развлечения, тогава списъкът с подкатегории трябва да бъде: Кино, Театър, Басейн. Много бързо решение, ако искате да анализирате по-подробна информация в домашния си бюджет.

Списък на категории и подкатегории в зависимо падащо меню на Excel

Признавам, че в предложената от мен версия на домашния бюджет съм ограничен само до категория, тъй като за мен такова разделение на разходите е напълно достатъчно (името на разходите / приходите се счита за подкатегория). Въпреки това, ако трябва да ги подкатегоризирате, тогава методът, който описвам по-долу, е идеален. Чувствайте се свободни да използвате!

И крайният резултат изглежда така:

Зависим падащ списък с подкатегории

За да постигнем това, трябва да направим малко по-различна таблица с данни, отколкото ако създаваме единичен падащ списък. Таблицата трябва да изглежда така (диапазон G2:H15):

Работен източник на Excel електронна таблица

В тази таблица трябва да въведете категория и до нея нейните подкатегории. Името на категорията трябва да се повтори толкова пъти, колкото има подкатегории. Много е важно данните да са сортирани по колоната Категория. Това ще бъде изключително важно, когато напишем формулата по-късно.

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

Добре тогава. Сега, една по една, ще опиша стъпките за създаване на зависим падащ списък.

1. Имена на диапазони от клетки

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

Нека назовем двата диапазона. Списък с всички категории и работен списък с категории. Това ще бъдат диапазоните A3:A5 (списъкът с категории в зелената таблица на първото изображение) и G3:G15 (списъкът с дублирани категории в лилавия работен лист).

За да наименувате списък с категории:

1. Изберете диапазон A3:A5.
2. В полето за име (полето отляво на лентата с формули) въведете името „Категория“.
3. Потвърдете с клавиша Enter.

Направете същото за диапазона от работен списък на категории G3:G15, който можете да наречете WorkList. Ще използваме този диапазон във формулата.

2. Създайте падащ списък за категория

Ще бъде просто:

1. Изберете клетката, в която искате да поставите списъка. В моя случай е А12.
2. От менюто DATA изберете инструмента Data Validation. Появява се прозорецът за проверка на входните стойности.
3. Изберете „Списък“ като тип данни.
4. За източника въведете: =Категория (снимката по-долу).
5. Потвърдете с OK.

Резултатът е следният:

Падащ списък за категория.

3. Създайте зависим падащ списък за подкатегория

Сега ще е забавно. Ние знаем как да създаваме списъци - просто го направихме за категория. Само един въпрос: "Как да кажа на Excel да избере само тези стойности, които са за определена категория?" Както вероятно се досещате, тук ще използвам работен лист и, разбира се, формули.

Нека започнем с това, което вече знаем, а именно да създадем падащ списък в клетка B12. Така че изберете тази клетка и щракнете върху Данни/Проверка на данни и задайте типа данни на Списък.

В източника на списъка въведете следната формула:

Изглед на прозореца "Проверка на входните стойности":

Валидиране на входни стойности за подкатегория в зависим падащ списък

Както можете да видите, целият трик със зависим списък е да използвате функцията OFFSET. Е, почти всички. Функциите MATCH и COUNTIF й помагат. Функцията OFFSET ви позволява да дефинирате динамично диапазони. Първо дефинираме клетката, от която трябва да започне изместването на диапазона, а в следващите аргументи дефинираме нейния размер.

В нашия пример диапазонът ще се премести през колоната Подкатегория в работния лист (G2:H15). Ще започнем да се движим от клетка H2, която е и първият аргумент на нашата функция. Във формулата клетка H2 е написана като абсолютна препратка, защото предполагам, че ще използваме падащия списък в много клетки.

Тъй като работният лист е сортиран по категория, диапазонът, който трябва да бъде източник за падащия списък, ще започне там, където избраната категория се появява за първи път. Например, за категорията Храна искаме да покажем диапазона H6:H11, за категорията Транспорт, диапазона H12:H15 и т. н. Имайте предвид, че се движим по колона H през цялото време и единственото нещо, което промени е началото на диапазона и неговата височина (т.е. броя на елементите в списъка).

Началото на диапазона ще бъде преместено спрямо клетка H2 с толкова клетки надолу (на брой), колкото е номерът на позицията на първата срещана категория в колоната Категория. Ще бъде по-лесно за разбиране с пример: обхватът за категория Храни се премества с 4 клетки надолу спрямо клетка H2 (започва от 4 клетки от H2). В 4-та клетка на колоната Подкатегория (без заглавието, тъй като това е диапазон с име WorkList), има думата Food (първата й поява). Използваме този факт, за да определим действително началото на диапазона. Функцията MATCH ще ни служи за това (въведена като втори аргумент на функцията OFFSET):

Височината на диапазона се определя от функцията COUNTIF. Тя брои всички срещания в категорията, тоест думата Хранене. Колко пъти се среща тази дума, толкова позиции ще има в нашия диапазон. Броят на позициите в диапазон е неговата височина. Ето функцията:

Разбира се, и двете функции вече са включени във функцията OFFSET, описана по-горе. Също така имайте предвид, че както в MATCH, така и в COUNTIF има препратка към диапазон, наречен WorkList. Както споменах по-рано, не е нужно да използвате имена на диапазони, можете просто да въведете $H3: $H15. Използването на имена на диапазони във формула обаче я прави по-проста и лесна за четене.

Това е всичко:

Изтеглете пример за зависим падащ списък в Excel

Една формула, добре, не толкова проста, но улеснява работата и предпазва от грешки при въвеждане на данни!