Как строить выпадающие списки в excel. выпадающий список в excel

Как нам это может пригодиться?

Часто случается так, что в какой-то из колонок вашей таблицы нужно вводить одинаковые повторяющиеся значения. К примеру, фамилии сотрудников, названия товаров. Что может случиться? Конечно, в первую очередь будут ошибки при вводе. Человеческий фактор ведь никто не отменял. Чем нам сие грозит? К примеру, когда мы решим подсчитать, сколько заказов выполнил каждый из менеджеров, то окажется, что фамилий больше, чем сотрудников. Далее придётся искать ошибки, исправлять их и вновь повторять расчет.

Ну и конечно же, все время руками вводить одни и те же слова – просто бессмысленная работа и потеря времени. Вот здесь-то выпадающие списки нам и пригодятся. При нажатии выпадает перечень заранее определённых значений, из которых необходимо указать только одно.

Важно то, что вы теперь будете не вводить, а выбирать их с помощью мыши или клавиатуры. Это значительно ускоряет работу, а также гарантирует защиту от случайных ошибок

Проверка того, что мы вписали в таблицу, теперь уже не нужна.

Второй способ создания двухуровнего списка

Второй способ удобно применять, когда данные выпадающего списка записаны в два столбца. В первом идет наименование группы, а во втором – подгруппы.

ВАЖНО! Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается

Для создания выпадающего групп нам понадобится дополнительный столбец, содержащий уникальные значения групп из исходной таблицы. Для создания этого списка используйте функцию удаления дубликатов или воспользуйтесь командой Уникальные из надстройки VBA-Excel.

Теперь создадим выпадающий список групп. Для этого выполните 4 первых пункта из первого способа создания двухуровнего списка. В качестве Источника укажите диапазон уникальных значений групп. Тут все стандартно.

Теперь самая сложная часть — указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, , ), которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.

Понижение и повышение элементов списка

Понижением мы будем называть увеличение глубины вложенности элемента, а повышение — это обратный процесс.

Начните с того, что поместите курсор в начале строки, которую вы хотите понизить или повысить.

Чтобы понизить эту строку до нижнего уровня списка, просто нажмите клавишу Tab.

Вы также можете понизить строку более одного раза, просто нажав Tab столько раз, сколько вам нужно. Здесь мы дважды понизили третью строчку в нашем списке.

Повторите эти шаги, если вы хотите добавить четыре, пять или даже больше уровней в свой многоуровневый список.

Вы также можете повысить строку (вернуть её обратно на один уровень), поместив курсор в начало строки.

Вы также можете понизить или повысить несколько строк одновременно, выбрав их

а затем нажмите клавиши Tab или Shift+Tab.

Как выбрать определённый уровень для элемента списка

Хотя использование комбинаций клавиш «Tab» и «Shift+Tab», вероятно, является самым простым способом повышения и понижения строк в списке, в Word есть пункт меню для выбора определённого уровня.

Поместите курсор в любое место строки, которую вы хотите изменить (или выберите несколько строк, если вы хотите изменить более одной строки).

На вкладке «Главная» нажмите кнопку «Многоуровневый список», в раскрывающемся списке выберите параметр «Изменить уровень списка», а затем выберите нужный уровень.

И выбранный вами пункт (или пункты) изменятся на этот уровень.

Постановка задачи

Имеется перечень Регионов . Для каждого Региона имеется свой перечень Стран . Для каждой Страны имеется свой перечень Городов .

Пользователь должен иметь возможность, выбрав определенный Регион , в соседней ячейке выбрать из Выпадающего (раскрывающегося) списка нужную ему Страну из этого Региона . В другой соседней ячейке пользователь должен иметь возможность выбрать нужный ему Город из этой Страны (см. файл примера ).

В окончательном виде трехуровневый связанный список должен работать так:

Сначала выберем, например, Регион «Америка» с помощью Выпадающего списка .

Затем выберем Страну «США» из Региона «Америка».

Причем перечень стран в выпадающем списке будет содержать только страны из выбранного на предыдущем шаге Региона «Америка».

И, наконец, выберем Город «Атланта» из Страны «США».

Причем перечень городов в выпадающем списке будет содержать только города из выбранной на предыдущем шаге Страны, т.е. из «США».

Первый способ создания двухуровнего списка

Первый способ основывается на создание «умной» таблицы, заголовок которой содержит значения первого выпадающего списка (группы), а строки таблицы соответствуют значениям второго выпадающего списка (подгруппы). Значения элементов подгруппы должны располагаться в соответствующем столбце группы, как на рисунке ниже.

Теперь приступим к созданию первого выпадающего списка группы (в моем случае — список стран):

1. Выберите ячейку, в которую будете вставлять выпадающий список;
2. Переходим на вкладку ленты Данные;
3. Выбираем команду Проверка данных;
4. В выпадающем списке выбираем значение Список;
5. В поле Источник указываем следующую формулу =ДВССЫЛ(«Таблица1»).

Осталось создать второй зависимый выпадающий список – список подгрупп.

Смело повторяем 4 первых пункта описанных выше. Источником в окне Проверка данных для второго выпадающего списка будет служить формула =ДВССЫЛ(«Таблица1»). Ячейка F2 в данном случае — значение первого выпадающего списка.

Второй способ создания двухуровнего списка

Второй способ удобно применять, когда данные выпадающего списка записаны в два столбца. В первом идет наименование группы, а во втором – подгруппы.

ВАЖНО! Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается

Для создания выпадающего групп нам понадобится дополнительный столбец, содержащий уникальные значения групп из исходной таблицы. Для создания этого списка используйте функцию удаления дубликатов или воспользуйтесь командой Уникальные из надстройки VBA-Excel.

Теперь создадим выпадающий список групп. Для этого выполните 4 первых пункта из первого способа создания двухуровнего списка. В качестве Источника укажите диапазон уникальных значений групп. Тут все стандартно.

Теперь самая сложная часть — указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, , ), которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.

• Ссылка в нашем случае — $A$1 — верхний левый угол исходной таблицы;
• Смещ_по_строкам — ПОИСКПОЗ(F3;$A$1:$A$67;0)-1 — номер строки со значением искомой группы (в моем случае страны ячейка F3) минус единица;
• Cмещ_по_столбцам — 1 — так как нам необходим столбец с подгруппами (городами);
• — СЧЁТЕСЛИ($A$1:$A$67;F3) — количество подгрупп в искомой группе (количество городов в стране F3);
• — 1 — так как это ширина нашего столбца с подгруппами.

Справка:

Формула СМЕЩ

СМЕЩ выдает ссылку на диапазон ячеек, находящийся в указанном количестве ячеек от исходной. Ссылка определяется с учетом заданного в формуле размера диапазона – числа строк и столбцов. Другими словами, этой формулой вы можете «сказать» Excel-ю на сколько ячеек он должен отступить и какой диапазон «захватить».

Синтаксис формулы СМЕЩ такой:

СМЕЩ(ссылка ; смещ_по_строкам ; смещ_по_столбцам ; ; ), где

• ссылка – ссылка, от которой вычисляется смещение, может быть адресом ячейки или группы ячеек;
• смещ_по_строкам – количество строк, которые требуется отсчитать вверх или вниз от начальной ссылки;
• смещ_по_столбцам – количество столбцов, которые требуется отсчитать влево или вправо от начальной ссылки;
• – число строк возвращаемой ссылки (необязательный);
• – число столбцов возвращаемой ссылки (необязательный).

Формула ПОИСКПОЗ

Ищет нужный нам элемент в диапазоне ячеек и выдает его порядковый номер в диапазоне.

Синтаксис ПОИСКПОЗ такой:

ПОИСКПОЗ( искомое_значение ; просматриваемый_массив ; )

Подробнее про эту формулу можно посмотреть в видеоинструкции: Какая формула лучше ВПР и работает с несколькими критериями

Связанные выпадающие списки

Связанные выпадающие списки.xls (216,5 KiB, 1 364 скачиваний)

Чтобы понять о чем пойдет речь в статье сначала необходимо понимать что такое выпадающий список и как его создать. Теперь попробуем разобраться что значит выражение «связанный выпадающий список». Я бы еще назвал такой список зависимым. Т.е. когда список значений одного выпадающего списка зависит от значения, выбранного в другом выпадающем списке или просто забитого в ячейку. Представим ситуацию: есть ячейка А2 . В ней создан выпадающий список со значениями: Овощи, Фрукты, Мясо, Напитки . А в ячейке В2 нам нужен такой список, чтобы значения этого самого списка изменялись в зависимости от того, какое значение мы укажем в ячейке А2 — т.е. список выбранной категории продуктов. Например выбрали в А2 значение Овощи — в В2 появился выпадающий список, содержащий значения: Морковь, Капуста, Картошка, Редиска, Помидоры. Выбрали в А2 Мясо — в В2 появился выпадающий список, содержащий значения: Говядина, Телятина, Свинина, Курица, Индейка . И т.д.

Подготовка Для начала нам потребуется создать все эти списки. Что-то вроде этого:

Далее для каждого из этих списков необходимо назначить именованный диапазон. Создать можно любым способом из описанных в этой статье. Главное помнить — если сами списки расположено на листе, отличном от того, на котором списки выпадающие — то обязательно создавать именованный диапазон с назначением области действия — Книга. В приложенном к статье примере диапазоны имеют имена категорий — их можно видеть в заголовках. Если ваши категории содержат пробел — необходимо заменить его на нижнее подчеркивание (_) или удалить, т.к. в качестве именованного диапазона такое значение не подойдет и ничего в результате не получится.

Создание зависимых списков В ячейке А2 создаем «список списков» — основной список, на основании значений которого будет создаваться второй список. Этот список может быть создан любым способом (как создать выпадающий список). Назовем его Список категорий. В ячейке В2 потребуется создать список на основании формулы, хоть по сути и так же, как и остальные: вкладка Данные (Data) —Проверка данных (Data validation) —Список (List) . Но теперь вместо прямого указания имени списка необходимо указать ссылку на именованный диапазон, который мы выберем в Списке категорий(ячейка А2 ), на основании его имени. В этом нам поможет функция ДВССЫЛ (INDIRECT)

Просто записываем эту формулу в поле Источник (Source) : =ДВССЫЛ( $A2 ) На что обратить внимание: если вы планируете распространять такой список на столбец, то ссылка должна выглядеть именно так: $A2. Перед цифрой не должно быть знака доллара ($A$2 — неправильно)

Иначе зависимый список будет всегда формироваться исключительно на основании значении ячейки А2.

Источник из другой книги Сами списки товара могут находится и в другой книге

Если книга называется Книга со списком.xls и на Лист1 в ячейке А1 в этой книге находится имя нужного нам списка, то формула будет выглядеть так: =ДВССЫЛ(«‘Лист1’!»&$A$1) На что обратить внимание: лучше всегда перед именем книги и после имени листа ставить апостроф — ‘. Так вы избежите проблем и недопонимания, если имя листа или книги содержит пробелы и иные специфические символы

В отличии от списков внутри одной книги в данном случае знак доллара должен быть и перед буквой и перед цифрой. В ином случае возможны ошибки (если, конечно, это не было сделано специально с пониманием того, что делалось).

Ограничения : данный способ создания списков хорош, но не обошлось и без ложки дегтя. Даже двух:

1. обе книги должны быть открыты. Если вы закроете книгу со списками, то получите ошибку — выпадающие списки просто перестанут работать
2. созданные подобным образом связанные списки не будут работать с динамическими именованными диапазонами

И ничего с этими ограничениями не поделать при подобном подходе.

Tips_Lists_Connect_Validation.xls (26,5 KiB, 16 309 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

Здравствуйте! В связанных списках для заголовков одной таблицы использую ДВССЫЛ таким образом =ДВССЫЛ(«Таблица»), дабы при добавлении нового столбца или изменении заголовков в динамической таблице автоматом отображались данные изменения и добавления. Но в некоторых случаях происходит некое зависание, данный список не раскрывается, он будто не видит данных которые необходимо отобразить. Подскажите в чем может быть причина?

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме — добро пожаловать на Форум

Определение нового формата маркера

Выполните одно из указанных ниже действий.

Изменение маркера на символ

Выделите текст или маркированный список, который вы хотите изменить.

На вкладке Главная в группе Абзац щелкните стрелку рядом с кнопкой Маркированный список и выберите пункт Определить новый маркер.

Изменение маркера на рисунок

Выделите текст или маркированный список, который вы хотите изменить.

На вкладке Главная в группе Абзац щелкните стрелку рядом с кнопкой Маркированный список и выберите пункт Определить новый маркер.

Щелкните Рисунок и выберите изображение из файла или с помощью поиска изображений Bing.

Просмотрите, как будет выглядеть рисунок в качестве маркера, и нажмите кнопку ОК, чтобы добавить его, либо замените рисунок, повторив шаги 3–5.

Изменение шрифта маркера

Выделите текст или маркированный список, который вы хотите изменить.

На вкладке Главная в группе Абзац щелкните стрелку рядом с кнопкой Маркированный список и выберите пункт Определить новый маркер.

Вкладка Шрифт

Изменение шрифта, начертания, размера, цвета, стиля и цвета подчеркивания, а также эффектов для маркера.

Вкладка Дополнительно

Настройка межзнаковых интервалов и возможностей OpenType для шрифта маркера.

Изменение выравнивания маркера

Выделите текст или маркированный список, который вы хотите изменить.

На вкладке Главная в группе Абзац щелкните стрелку рядом с кнопкой Маркированный список и выберите пункт Определить новый маркер.

В группе «Выравнивание» выберите Слева, По центру или Справа.

Как сделать выпадающий список в Excel? Пошаговая инструкция

Разработчиками ПО предусмотрено несколько методов, как создать выпадающий список в «excel». Далее рассмотрим все доступные способы:

Метод №1 – самый простой

Данный метод является наиболее простым, однако его функциональность имеет определенные ограничения. Его реализация достаточно проста, но есть определенная особенность, этой функцией можно воспользоваться только в графе, под ранее заполненными ячейками, на любые другие сегменты документа она не будет распространяться:

Вариант – 1 переходим в поле под перечнем значений, открываем контекстное меню, и в открывшемся окне выбираем функцию: «Выбрать из раскрывающегося списка»;

Вариант – 2 переходим в поле под заполненным списком, и нажимаем сочетание клавиш:  Alt+V.

Метод №2 – стандартный алгоритм

Теперь рассмотрим метод с расширенным функционалом, для создания, в «excel» выпадающего списка в ячейке.

Присваиваем наименование заданному диапазону значений — для начала необходимо выделить желаемый диапазон сегментов с данными, которые должны будут находиться в раскрывающемся меню. Теперь этому диапазону сегментов, присваиваем имя – которое будет служить источником.

Способ – 1: выделив необходимый диапазон рабочих сегментов, открываем контекстное меню, и выбираем пункт: «Присвоить имя».

Способ – 2: выделив диапазон рабочих сегментов, переходим в раздел Формулы-Диспетчер имен-Создать, и в графе «источник» прописываем уникальное наименование для будущего реестра значений.

№2-2.

Проще говоря, создаем перечень наименований, а его имя, которое должно быть уникальным (не повторяющемся в документе), будет служить ссылкой на обозначенный диапазон ячеек с данными.

Создаем реестр данных — пройдя этап создания, так называемой «ссылки» на необходимый диапазон ячеек, перейдем непосредственно к созданию в «excel» выпадающего списка на несколько значений, для любой ячейки документа.

Выделяем нужную ячейку, она может располагаться в любом месте документа (как на листе где размещены значения, так и на всех дополнительных листах документа). Теперь необходимо перейти в раздел: Данные-Проверка данных, в открывшемся окне в графе «тип данных» из общего перечня выбираем «Список», и в графе «источник» прописываем имя реестра (в данном случае «продукты»).

Важно – перед именем, необходимо вставить знак равенства «=»

Таким образом мы рассмотрели еще 1 метод, как сделать выпадающий список в ячейке «Excel».

Важно – создавать данную опцию, можно не для одной графы, а сразу для любого их количества, расположенных в любой области «Документа». Выделяем через «CTRL» все ячейки, которым необходимо задать опцию конкретного перечня данных, и проделываем вышеописанный алгоритм действий

Как сделать списки в Ворде

Итак, как составлять списки в Word? Для их успешного создания мы будем следовать простой инструкции:

• писать текст в Word;
• также переместите курсор компьютерной мыши перед любым словом в тексте;
• нажмите на раздел «Главная» и левой кнопкой мыши выберите типы объявлений чуть ниже раздела «Обзор». Или через контекстное меню;
• после этого появятся нужные вам списки, которые вы сможете установить.

Воспользуемся этой инструкцией при создании списков на практике.

Как сделать маркированный список в Ворде

Прежде всего, давайте создадим маркированный список. Откройте документ Word с текстом. Затем щелкните один раз перед текстом левой кнопкой мыши. Например, в начале предложения. И щелкните правой кнопкой мыши «Маркированный список» (снимок экрана 1).

В панели управления Word сначала появляются другие списки. После нажатия вы увидите точки перед словами в тексте (снимок экрана 2).

Это будет маркированный список.

Как изменить текст в список

Текст можно редактировать в списке без использования списков слов. Если в документе есть слова, написанные полностью, их можно разделить. Для этого щелкните один раз мышью перед словом, которое вы хотите пропустить (снимок экрана 3).

Затем нажмите кнопку «Ввод» на клавиатуре, чтобы переместить слово вниз и превратить его в список. Вы также можете вручную вводить цифры в начале слова или английские буквы.

Как поменять стиль маркированного списка (внешний вид)

вы можете изменить маркированный список в другом представлении. Для этого вам нужно выделить маркированный текст. Затем щелкните по нему в верхней части панели управления списком и выберите соответствующий вид списка (снимок экрана 4).

Существует несколько типов маркированных списков:

1. Список галочек.
2. Круг.
3. Квадрат.
4. Много квадратов.
5. Стрелка.

И другие варианты доступны. Если вы нажмете кнопку «Определить новый маркированный список», вы можете установить параметры списка в другом окне и загрузить свое изображение со своего компьютера. Щелкните раздел «Изображение» и загрузите новое представление списка.

Как сделать нумерованный список в Ворде

Приступим к созданию нумерованного списка по тексту. Снова выберите место в тексте, где вы хотите установить нумерованный список. Затем щелкните нумерованный список вверху текстового редактора Word (снимок экрана 5).

Как видно на рисунке 5, нумерованный список успешно установлен. Кроме того, вы можете использовать контекстное меню для выбора представления списка.

Делаем нумерованный список, порядок работы

Разберем порядок работы с нумерованным списком. После его установки у вас будет номер в списке. Чтобы все записать, вы должны по очереди щелкнуть левой кнопкой мыши перед каждым словом и щелкнуть пронумерованный список. Впоследствии все слова будут пронумерованы от 1 до 5 и выше.

После каждого предложения в тексте вы можете нажимать кнопку «Ввод», чтобы на следующей строке появилось число. И после этого напишите слово, предложение — номер появится автоматически.

Как изменить номер в списке Ворд

Изменить число в списке слов довольно просто. Выделите нумерованный список. Затем откройте его (скриншот 6).

Затем выберите нужный номер из предложенных вариантов. Форматы номеров в документе разные. Внизу списков вы можете выбрать другие параметры для изменения чисел:

1. Измените уровень списка.
2. Определите новый числовой формат.
3. Установите начальное значение.

Текстовый редактор Word предлагает пользователям широкий выбор номеров для нумерованного списка.

Способ 2: Создание списка со случайными неповторяющимися числами

К сожалению, пока возможности Excel в плане рандомного генератора ограничены описанной выше функцией, и создать список со случайными неповторяющимися числами в пару кликов не получится. Однако можно при помощи вспомогательных инструментов достичь желаемого результата, зная всего пару хитростей.

Вы можете растянуть этот список вниз самостоятельно, создав уникальные числа с определенным шагом увеличения.

Однако, если нужно получить больше 100 единиц в перечне, растягивать его не очень удобно. Можно пойти более простым путем. Сначала скопируйте первое значение.

После этого нажмите сочетание клавиш Ctrl + G и в поле «Ссылка» введите номер последней ячейки списка.

Перейдите к ней, зажмите Ctrl + Shift + Стрелка Вверх для выделения всех ячеек до первой.

Первое значение у вас уже скопировано, поэтому остается только вставить саму функцию, используя сочетание клавиш Ctrl + V. Теперь у вас в распоряжении есть большой список с неповторяющимися числами.

Скопируйте весь перечень, откройте контекстное меню, щелкнув по любой ячейке правой кнопкой мыши, и используйте специальную вставку, чтобы вставить только значения. Так вы удалите функцию суммы, которая больше в списке не нужна.

Выделите любую клетку с числом и убедитесь, что функция отсутствует и осталось только значение.

Список с неповторяющимися числами есть, но все они расположены в порядке возрастания. Нужно исправить эту ситуацию, сделав их случайными. В этом поможет инструмент сортировки данных и вспомогательная функция.

Создайте название для текущего списка, а рядом пропишите еще один заголовок.

После этого выделите первую ячейку второго списка и задайте там формулу =СЛЧИС(). Она генерирует случайное нецелое число в ячейке.

Растяните функцию, чтобы она заканчивалась там, где заканчивается последнее число созданного ранее списка.

Выделите первый заголовок и перейдите на вкладку «Данные».

Включите функцию «Фильтр».

Теперь нажмите кнопку со стрелкой, расположенную справа от названия списка с нецелыми числами, и выберите один из вариантов сортировки.

Вместе со вторым столбцом отсортирован и первый – все числа в нем не повторяются и являются случайными.

Удалите ненужные вспомогательные элементы, оставив только готовый перечень. Теперь его можно использовать в личных целях.

Может показаться, что генерирование случайных чисел в Excel занимает много времени. Однако вам понадобится всего пара минут, чтобы ознакомиться с моей инструкцией и реализовать ее самостоятельно. Никаких сложностей и тонкостей нет, поэтому у вас обязательно все получится.

Как выполнить разгруппировку

Когда необходимо исправить данные, зафиксированные в группировку, либо распечатать готовую таблицу, можно попробовать разгруппировку. Чтобы это сделать, перейдем к следующему алгоритму.

1. Выделяем созданную группировку, затем переходим во вкладку «Данные», где находим блок «Структура».

1. Далее нажимаем на инструмент «Разгруппировать», ставим флажок напротив записи «Строки» и нажимаем кнопку «ОК». В случае, если вы делали группировку дважды, то необходимо снять элемент структуры столько же раз. Не забывайте при любом действии с иерархией выделять необходимый диапазон данных.