Svinkovod.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как сделать зависимый выпадающий список в Excel

Как сделать зависимый выпадающий список в Excel?

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

Вот примеры таких задач:

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

Выглядеть это может примерно так:

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

Начнем с более простого и стандартного подхода.

1. Именованные диапазоны + функция ДВССЫЛ.

Это может показаться сложным, но на самом деле это очень просто, и является отличным примером того, как можно применить ДВССЫЛ.

создаем зависимый выпадающий список

Рассмотрим небольшой пример. У нас есть перечень автомобилей различных марок. Расположим их каждый в отдельном столбце. В первой ячейке каждого столбца запишем производителя — Toyota, Ford, Nissan. Необходимо, чтобы после того, как первоначально мы выберем, например, Toyota, далее мы видели бы только модели этой марки, и ничего более. То есть, нам нужен двухуровневый связанный список.

Для начала создадим именованные диапазоны с моделями автомашин. Имя каждому из них присвоим в соответствии с маркой авто. Важно, чтобы имя каждого из них точно соответствовало значению, записанному в первой строке соответствующего столбца. Иными словами, если мы создаем именованный диапазон из ячеек A2:A100, то имя его должно совпадать со значением в A1 (регистр символов значения не имеет). Посмотрите на рисунке, как это выглядит.

Итак, у нас получилось 3 именованных диапазона — «toyota», «ford», «nissan». Делать их статическими (фиксированными) или динамически (автоматически пополняемыми) — решайте сами. О том, как создать автоматически пополняемый список, смотрите ссылку в конце этой статьи.

Далее в ячейке F3 создаем выпадающий список первого уровня с моделями автомашин так, как это показано в нашем примере на рисунке. Источник данных — первые ячейки каждого столбца. Обратите внимание, что инструмент проверки вводимых значений, при помощи которого мы создаем его, прекрасно работает как с вертикальными (по строкам), так и с горизонтальными (по столбцам) данными.

И далее выбираем того производителя, который нас интересует. К примеру, «Ford».

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

В этом нам поможет функция ДВССЫЛ. Функция ДВССЫЛ (INDIRECT в английском варианте) преобразует текст в стандартную ссылку Excel.

Если мы запишем

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

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

«Фишка» функции ДВССЫЛ (или INDIRECT) в том, что она позволяет использовать текст точно так же, как обычную ссылку на ячейку . Это обеспечивает нам два ключевых преимущества:

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

В примере на этой странице мы объединяем последнюю идею с именованными диапазонами для создания многоуровневого выпадающего списка. ДВССЫЛ преобразует обычный текст в имя, которое затем превращается в нормальную ссылку и источник данных для него.

Итак, в этом примере мы берем текстовые значения из А1:С1, выбираем из них какое-то одно. К примеру, «Ford». Поскольку такое же название у нас имеет один из именованных диапазонов, то и применяем ДВССЫЛ, чтобы преобразовать текст «Ford» в ссылку =ford. И вот уже ее мы употребляем как источник для связанного выпадающего списка.

Итак, в качестве источника значений применяем формулу

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

В результате функция возвращает в нашу таблицу Excel ссылку

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

Изменяя значения в F3, мы автоматически изменяем и ссылку-источник для списка в F6. В результате источник данных для зависимого выпадающего списка в F6 динамически меняется в зависимости от того, что было выбрано в F3. Если выбираем Ford, то видим только каталог машин этой марки. Аналогично, если выбираем Toyota либо Nissan.

Читайте так же:
Метод автозаполнения в excel

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

А как быть с пробелами?

Может случиться так, что название вашей группы товаров или категории будет содержать пробелы. А именованные диапазоны не позволяют, чтобы в их названии встречался пробел. Принято заменять их символом нижнего подчеркивания «_». Как же нам быть в этом случае? Ведь в таблице названия товарных категорий с символом нижнего подчеркивания будут смотреться несколько непривычно. Например, «Косметические_товары». С непривычки можно и просто забыть ввести нужный символ. И тогда наши формулы работать не будут.

Выход довольно прост. Создавайте именованные перечни, заменяя в их названиях пробелы символом нижнего подчеркивания. В самих же значениях, записанных в ячейках таблицы Excel, используйте обычные пробелы. А перед тем, как применять в формуле, мы их специальным образом обработаем при помощи функции ПОДСТАВИТЬ.

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

То есть, мы проведем предварительную обработку значений, чтобы они соответствовали правилам написания имён. Вместо =ДВССЫЛ($F$3) запишем

Кавычки здесь не нужны, поскольку ПОДСТАВИТЬ возвращает текстовую строку. Если же в нашем тексте нет пробелов и он состоит из одного слова, то он будет возвращен «как есть». Следите только за тем, чтобы в начале и в конце обрабатываемой текстовой переменной у вас случайно не оказались пробелы. Ведь они тоже будут заменены на нижнее подчеркивание. Ну а чтобы не заниматься этим ручным контролем, усложните еще немного свою формулу при помощи функции СЖПРОБЕЛЫ. Она автоматически уберет начальные и конечные пробелы из текста. В итоге получим:

Ну а теперь — еще один способ, как сделать многоуровневый зависимый выпадающий список в Excel.

2. Комбинация СМЕЩ + ПОИСКПОЗ

Итак, у нас снова есть перечень марок и моделей автомобилей. Только записан он немного по-другому.

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

Первое условие — исходные данные должны быть отсортированы по маркам, а внутри марок — по моделям. То есть, нужно отсортировать по столбцу А, а затем — по В.

Начнем с простого. В ячейке D1 создадим выпадающий список из марок автомобилей. Для этого в F1:F3 запишем их названия и затем употребим их в качестве источника. Напомню, что нужно нажать Меню — Данные — Проверка данных.

создаем выпадающий список

Далее нам нужно в D2 создать второй уровень, где будут только модели выбранной марки. В этот раз источник данных мы определим несколько иначе, чем ранее. Воспользуемся тем, что функция СМЕЩ может возвращать массив данных, который мы как раз и можем употребить в качестве наполнения нашего второго перечня. Но для этого ей нужно передать целых 5 параметров:

  • координаты верхней левой ячейки,
  • на сколько строк нужно сместиться вниз — A,
  • на сколько столбцов нужно перейти вправо — B,
  • высота массива (строк) — C,
  • ширина массива (столбцов) D.

Зеленым цветом на рисунке выделен новый диапазон. Если параметры C и D не указать, то будет возвращено содержимое единственной ячейки, в которую мы переместились, сделав несколько шагов вниз и вправо.

Традиционно точкой отсчета для функции СМЕЩ возьмем ячейку A1. Теперь нам нужно решить, на сколько позиций вниз и вправо нужно перейти, чтобы указать левый верхний угол нового перечня с моделями. Предположим, первоначально мы выбрали Ford.

На сколько шагов сместиться вниз? Применим функцию ПОИСКПОЗ, которая возвратит нам номер позиции первого вхождения «Ford».

Если первый раз нужное нам слово встретилось, к примеру, в 7-й позиции, то вычтем 1, чтобы получить количество шагов. То есть, начиная с первого значения, нужно сделать 6 шагов.

Третий параметр установим равным 1, так как нужно перейти на один шаг вправо из A в B. Мы находимся в начальной точке нашего диапазона. Теперь рассчитаем, на сколько ячеек вниз он будет продолжаться. Для этого подсчитаем, сколько раз «Ford» встречается в нашем перечне. Столько и будет значений вниз.

А теперь объединяем все это в СМЕЩ:

Последняя единичка означает, что массив состоит из одной колонки.

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

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

Еще полезная дополнительная информация:

alt=»использование формул для выпадающего списка» width=»141″ height=»150″ /> Создаем выпадающий список в Excel при помощи формул — Задача: Создать выпадающий список в Excel таким образом, чтобы в него автоматически попадали все новые значения. Сделаем это при помощи формул, чтобы этот способ можно было использовать не только в Excel 2007 и старше, но и в Excel 2003. Как…
автоматическое добавление значений в выпадающий список 5 способов создания выпадающего списка в ячейке Excel — Одной из наиболее полезных функций при вводе данных является возможность использовать выпадающий список. Он позволяет выбирать значение из предварительно определенного перечня и разрешает вводить только те данные, которые соответствуют вашим требованиям. Мы предложим вам несколько простых способов, как создавать выпадающие…

Читайте так же:
Мифнс вход в личный кабинет

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

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

Списки

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

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

Список через ДВССЫЛ

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

На что обратить внимание: если вы планируете распространять такой список на столбец, то ссылка должна выглядеть именно так: $A2. Перед цифрой не должно быть знака доллара ($A$2 — неправильно). Иначе зависимый список будет всегда формироваться исключительно на основании значении ячейки А2.

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

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

Двухуровневый выпадающий список в excel

Microsoft Excel • Видеоуроки. Мы хотим, чтобы друзья, однокурсники, одноклассники, соседи и коллеги всегда оставались в контакте. На ленте на вкладке «Данные» щелкните «Проверка данных». Скачать XLTools для Excel – пробный период дает 14 дней полного доступа ко всем инструментам. Связанные выпадающие списки в Excel. Впр выпадающий список в excel 22.09.2019 admin Комментарии Нет комментариев Элен Брэдли объясняет тонкости работы с функцией ВПР в Microsoft Excel при поиске данных в таблице. Урок 8: Динамические списки. Выпадающий список в Excel 2010 можно создавать одним нажатием сочетания клавиш, но он может быть расположен только в одном месте — в ячейке под элементами списка. Как сделать выпадающий список в Excel? Как в экселе автоматически пронумеровать строки? Затем вызываем выпадающий список путем нажатия на правую кнопку мыши. Нужно тщательно подходить к формированию списка Excel… Зависимые выпадающие списки в excel 2020 Двухуровневый выпадающий список в Excel Имеется несколько способов создания выпадающего списка. В общем не буду вас томить, давайте лучше приступим. Не ругается он на это и при сохранении файла, а вот при открытии — ой. Для … Простые и зависимые списки. В Excel есть возможность копировать созданные выпадающие списки. Например, в ячейке А1 у нас есть выпадающий список, который мы хотим скопировать в диапазон ячеек А2:А6. Для того чтобы скопировать выпадающий список с текущим форматированием: нажмите сочетание клавиш на клавиатуре CTRL+V. 12/11/2019. Просмотров 18 Опубликовано 31.08.2019 Выпадающий Список В Excel Функции Excel Бланк путевого листа легкового автомобиля 2018 года Предлагаем бесплатно скачать новую форму 3 для акта путевого листа легкового автомобиля, действительную в 2018 году. Считается самым простым и понятным методом, когда нужно сделать выпадающий список в экселе в другом месте текущего документа: на новом листе, рядом с таблицами. 06 апреля, 2019, 14:00 10659 Сертифицированный тренер Google for Education Джейк Миллер ведет блог, где описывает неочевидные приемы, которые упрощают и автоматизируют работу с документами Google Docs: как настроить шорткаты, как выкачива� В этой статье мы покажем, как изменять списки, созданные разными способами. В комментариях у некоторых пользователей появился интерес к созданию связанных выпадающих списков, поэтому предлагаю вашему внима Столкнулся с следующей неприятной проблемой: Ведем учетную таблицу в гугл доксах, однако она со временем начала тормозить и посему решили перейти на тандем Эксез + Эксель. Вчера коллеги из бухгалтерии задали интересный вопрос, над которым ранее я никогда не задумывался. Бонус — построить график из полученных данных и научить выпадающий список самому подставлять данные в него прямо с листа двумя кликами. Учимся создавать в ячейке выпадающий список. Как в excel 2013 создать выпадающий список 11.08.2019 admin Комментарии Нет комментариев Содержание статьи: Как сделать зависимый выпадающий список в excel 2010? . перенесенный из «Excel» в «Word», в одну строку (горизонтально) . 03.10.2019 в 15:16. Выпадающий список в Excel с помощью инструментов или макросов Под выпадающим списком понимается содержание в одной ячейке нескольких значений. 19.10.2019 admin Комментарии Нет комментариев. Значения («B1:B10») — это диапазон в пределах которого будет работать выпадающий список. Аналогичным образом можно создать выпадающий список со смещением вниз и выпадающий список, записывающий в ячейку несколько значений через знак табуляции или пробел. Часть 1: Выпадающий список в MS EXCEL. Как сделать в Word повтор не шапки таблицы, а только второй строки (с номерами колонок) на каждом листе? Выбор одного из них Теперь выбираем пункт «Специальная вставка», появится дополнительный список, в котором необходимо отметить «Вставить значения». Порой при работе с таблицами Excel необходимо ограничить список вводимых значений или использовать уже заведомо известные данные. Создаете список значений, которые будут в дальнейшем фигурировать в выпадающем списке. Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список) B. Ввод элементов списка в диапазон (на любом листе) Выпадающий список в Excel с помощью инструментов или макросов Это в Microsoft Excel совсем не продумано, поэтому новичкам бывает достаточно трудно ввиду отсутствия интуитивно понятного алгоритма. Создать выпадающий список Как в Excel сделать выпадающий список (простой или зависимый) с выбором нескольких значений? Имеется несколько способов создания выпадающего списка. На вкладке Параметры в поле Тип данных выберите пункт Список. Скачать XLTools для Excel – пробный период дает 14 дней полного доступа ко всем инструментам. Просмотров 214 Опубликовано 23.07.2019. Щелкните поле Источник и выделите диапазон списка. в Excel». Функции, формулы, шаблоны и приемы для работы в Эксель. Когда мы создаём этот список программно, то Excel на это не ругается (в отличие от попытки это сделать через стандартное диалоговое окно). В примере данные находятся на листе «Города» в диапазоне A2:A9. Как сделать выпадающий список в экселе ; Как добавить строку в таблицу; Как сделать таблицу в таблице; Как расположить таблицу; Как в Excel пронумеровать ячейки; Как удалить повторяющиеся значения в экселе; Как выполнит� Автор Архипов Егор На чтение 4 мин. Создать выпадающий список в excel 2020 Как в экселе сделать выпадающий список с выбором нескольких значений? Выбор одного из них Если нужны будут даты до сегодняшнего дня, то просто меняем список. В более старых версиях Excel, нет возможности формировать выпадающий список в ячейке используя данные других листов, поэтому имеет смысл создавать списки в том же листе и прятать их в свёрнутом или скрытом виде. Содержание . Выпадающий список в Excel 2019. 3. Доброго времени на … Выпадающий список (видеоурок Excel) 2019-07-12. Как создать выпадающий список в Excel. Как сделать выпадающий список в Excel без списка. Как создать выпадающий список в Excel 2010 plaub 09.02.2018 Если вы вводите данные в электронную таблицу или создаете электронную таблицу для других пользователей, то, как правило, рекомендуется сделать все как можно проще. Создание списка посредством контекстного меню. Просто списки в excel 2007 работают, списки со значениями из того же листа работают. Как сделать выпадающие списки в Excel, смотрите в статье «Выпидающий список в Excel Выпадающий список в Excel это, пожалуй, один из самых удобных способов работы с данными. Как создать выпадающий список в Excel Автор Глеб Захаров На чтение 6 мин. Существенную помощь в этом могут оказать выпадающие списки Excel. Им прислали файл Excel, но в нем при протяжке формулы ничего не считается в последующих ячейках. Получаем два списка зависящих друг от друга. Ниспадающий список в excel 2020 Двухуровневый выпадающий список в Excel Имеется несколько способов создания выпадающего списка. Обновлено, чтобы включить Excel 2019. Слетел выпадающий список со ссылкой на дргой лист . такие выпадающие списки исчезли, в excel 2013 остались. листе. Автор Влад Каманин На чтение 9 мин. На вкладке Параметры в поле Тип данных выберите пункт Список. При заполнении ячеек данными, бывает необходимо ограничить возможность ввода определенным списком значений – это можно сделать с помощью Выпадающего списка . Microsoft Excel: выпадающие списки Смотрите также нажмите кнопку , т.е. Excel для того, чтобы : Как сделать выбор спасибо, внятно и по делу ) – Мария С. В старых версиях Excel нет такого значка, но можно решить проблему с помощью специальной вставки (Ctrl + Alt + V) и выбора опции «Транспонировать» (Transpose). Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365. Для этого необходимо зайти в Проверка данных и выбрать все значения. Динамический выпадающий список в ms excel. Информация актуальна! Как сделать в Word повтор не шапки таблицы, а только второй строки (с номерами колонок) на каждом листе? Связанные выпадающие списки.xls (216,5 KiB, 2 544 скачиваний) Чтобы понять о чем пойдет речь в статье сначала необходимо понимать что такое выпадающий список и … Недавно дочь обратилась с вопросом, нельзя ли в Excel выпадающий в ячейке список сделать контекстным, например, зависящим от содержания ячейки, Имеется несколько способов создания выпадающего списка. Как сделать связанный выпадающий список в «Эксель», зависящий от значения в соседней ячейке. Актуальные и эффективные приемы работы в Excel, которые помогут значительно сэкономить время и повысить вашу продуктивность. В нужно месте вставлем выпадающий список, источником данных для которого является этот столбик. Выпадающие списки – очень удобный объект, который используется практически во всех приложениях. 20 января 2019 Владимир Половников ответил: 1. Попробуем создать выпадающий список на примере Excel 2007. 2019; Итак, ранее я писал о том, как вы можете создать простой выпадающий список в Excel, который идеально подходит для любого типа проверки данных. Для этого создадим некоторую табличку, к которой мы сможем это применить. При заполнении ячеек данными иногда необходимо ограничить возможность ввода определенным списком значений. Как сделать выпадающий список в Excel? Как в excel 2013 создать выпадающий список 11.08.2019 admin Комментарии Нет комментариев Содержание статьи: Как добавить выпадающий список в ячейку в Excel. Хочу сделать список должников документов в excel. В Excel есть полезные функции, о которых многие не знают. Открываем программу Ексель и прописываем в первой таблице какие-нибудь слова или цифры. 2. Выбор одного из них зависит Статья о том, как подчеркнуть слово двумя чертами (линиями) в Word 2007, 2010, 2013, 2016, 2019 и 2003. Обладает большим количеством новых функций и инструментов. Создание раскрывающегося списка в Excel Выберите ячейки, в которой должен отображаться список. Excel Списки в excel. К� Видео; См. Выделим весь список моделей Тойоты (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota.В Excel 2003 и старше — это можно сделать в меню Вставка — Имя — Присвоить (Insert — Name — Define). Как убрать переносы в ворде? Многие пользователи Office стандартный недооценивает редактор таблиц Excel. И Все-бы ничего, но при доб� К� Как расширить выпадающий список в Excel? Необходимо сделать раскрывающийся список со значениями из динамического диапазона. 3.1 Простой выпадающий список; 3.2 С В этом поле отображается текущий формат выбранной ячейки или диапазона ячеек, но если раскрыть выпадающий список, то мы увидим другие форматы, которые можем применить для выделенного нами ранее диапазона. Выпадающий список в Excel 2019. Как в экселе автоматически пронумеровать строки? Как сделать зависимый выпадающий список в excel 2010? Как сделать выпадающий список в excel. Многие пользователи стандартного офисного пакета от Microsoft явно недооценивают табличный редактор Excel. Excel – закрасить ячейки по условию. Как работать с выпадающим списком в Excel Создание раскрывающегося списка Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Как убрать переносы в ворде? Выпадающий список позволяет вводить лишь те данные, которые мы в нем укажем, соответственно, не может возникнуть ошибки, при вводе данных вручную, да и удобство работы с такой таблицей выше. Выпадающий список в Excel – невероятно полезный инструмент. Для клиентов вебинар проводится в рамках сервисного обслуживания — бесплатно. Выбор одного из них Это всё о выпадающем списке, но если вы хотите усилить свои знания – прочтите практикум, как сделать зависимый выпадающий список.Это когда исходные данные в одном списке зависят от того, что выбрано в другом. Мы хотим, чтобы друзья, однокурсники, одноклассники, соседи и коллеги всегда оставались в контакте. Excel 2013 выпадающий список в ячейке . Выпадающий список в excel 2020 Двухуровневый выпадающий список в Excel Имеется несколько способов создания выпадающего списка. 7. Нажмите на ячейку, которую вы выбрали для этой цели. Выпадающие списки создаются с использованием функции проверки данных. Двухуровневый выпадающий список в Excel. Excel 2020 выпадающий список в ячейке Двухуровневый выпадающий список в Excel Имеется несколько способов создания выпадающего списка. Вставить в таблицу Excel. После того, как вы создали раскрывающийся список, вам может понадобиться добавлять в него дополнительные элементы или удалять имеющиеся. 1 Создание раскрывающегося списка; 2 Создание раскрывающегося списка. После этого в окне появляется список всех математических функций в Excel. Щелкните поле Источник и выделите диапазон списка. Графическое изображение распределения отпусков в excel . Выпадающий список в ячейке Excel 4 апреля, 2019 20 сентября, 2018 Выпадающие списки в Microsoft Excel незаменимы при создании объемных таблиц, работе с базами данных. Это в Microsoft Excel совсем не продумано, поэтому новичкам бывает достаточно трудно ввиду отсутствия интуитивно понятного алгоритма. В файле примера выпадающий список с поиском реализован сразу в двух вариантах: для обычного диапазона (лист Range) и для умной таблицы (лист Table).

Читайте так же:
Можно ли вылечиться от рака навсегда

Зависимые выпадающие списки в Excel

Изучим простой способ создания зависимых (также называют связанных) выпадающих списков в Excel.

Для начала поясним, что же такое связанные выпадающие списки.
Это 2 выпадающих списка, при этом список значений одного из выпадающих списков зависит от выбора значения в другом выпадающем списке. Создать по отдельности выпадающие списки не представляет сложностей, но и связать выпадающие списки можно достаточно просто.
Для наглядности рассмотрим следующую ситуацию: у нас есть несколько категорий блюд, например, пицца, суши и паста, а также виды блюд в каждой категории (пицца Пепперони, суши Филадельфия, паста Феттучини и т.д.):

Список значений

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

Как сделать зависимые выпадающие списки?

В основе создания связанных выпадающих списков лежит применение функции ДВССЫЛ, которая позволяет преобразовывать текст из ячейки в ссылку.
Другими словами, если в ячейку введено текстовое значение «А1», то функция ДВССЫЛ вернет ссылку на ячейку А1.
Теперь зададим имена диапазонам состоящим из всех видов блюд каждой конкретной категории.
Для этого в панели вкладок выбираем Формулы -> Определенные имена -> Присвоить имя:

Вкладка для создания имени диапазона

Выделяем диапазон ячеек A2:A6 и создаем диапазон с именем Пицца, аналогичные действия повторяем и для списков с суши (имя диапазона — Суши) и пастой (имя диапазона — Паста):

Создание имени диапазона

Обратите внимание, что при создании имен диапазонов имя не должно включать в себя пробелы.
В том случае если в названии категории все же содержится пробел (например, Японская кухня), то одним из вариантов решения является использование нижнего подчеркивания вместо пробела в имени диапазона (Японская_кухня).

Читайте так же:
Минус проценты в excel

Создадим первый выпадающий список в ячейке A10, состоящий из категорий блюд (Пицца, Суши и Паста). В панели вкладок выбираем Данные -> Работа с данными -> Проверка данных, указываем тип данных Список и в качестве источника выделяем диапазон A1:C1:

Создание первого всплывающего списка

Теперь создаем второй выпадающий список, полностью повторяем действия с созданием первого списка, только в поле Источник записываем формулу =ДВССЫЛ(A10):

Создание второго всплывающего списка

Имена созданных диапазонов обязательно должны совпадать с элементами первого списка, поэтому если в первом списке есть категории содержащие пробелы, то при обращении к имени диапазона необходимо заменить пробелы на нижние подчеркивания.
Это можно осуществить с помощью функции ПОДСТАВИТЬ, которая позволяет заменить старый текст (пробел) на новый текст (нижнее подчеркивание) в текстовой строке, т.е. в нашем случае формула примет вид =ДВССЫЛ(ПОДСТАВИТЬ(A10;" ";"_")).
Также минусом данного способа создания списков является невозможность использования динамических именованных диапазонов.

Подробно ознакомиться с примером зависимых выпадающих списков — скачать пример.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector