Eurotehnik.ru

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

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

Exceltip

99-0-создание уникального списка лого

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

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

99-1-список сотрудников

Формула массива будет выглядеть следующим образом:

=ЕСЛИОШИБКА(ИНДЕКС(Список;ПОИСКПОЗ(СУММ(СЧЁТЕСЛИ(B$1:B1; Список));СЧЁТЕСЛИ(Список;»<«& Список);0));»»)

В данном случае Список – это именованный диапазон ячеек А2:А100.

Скопируйте формулу в ячейку B2, нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы превратить формулу в формулу массивов, и протяните ее до ячейки B20.

Давайте разберемся, как работает формула:

  1. СЧЁТЕСЛИ(Список;»<«& Список) – возвращает массив (1) с количеством сотрудников, которые меньше сотрудника, находящегося в текущей ячейке. Т.е. массив будет иметь вид <5; 20; 19; 21; 12 …>, например, Дима Билан имеет значение 5, это значит, что перед ним имеется еще 4 сотрудника, которые расположены раньше по алфавиту, Филипп Киркоров = 20, значит перед ним имеется еще 19 сотрудников расположенных ближе к букве А. Таким образом, мы получаем что-то наподобие отсортированного листа.
  2. СЧЁТЕСЛИ(B$1:B…; Список) – возвращает массив (2) с единицами для тех позиций, которые уже имеются на отсортированном листе. Например, в ячейке B2 будет массив <0;0;0;0;0…>, а в ячейке B8 — <0;1;0;0;0; … ;0;1>– так как Валерий Леонтьев встречается два раза.
  3. СУММ – суммирует значения, которые уже были представлены на отсортированном списке.
  4. ПОИСКПОЗ – ищет сумму встречающихся значений массива (2) в массиве (1)
  5. ЕСЛИОШИБКА – скрывает #Н/А, когда расчеты достигают конца списка.

Долго думал, как можно было бы проще объяснить работу формулы, но ничего путного в голову так и не пришло. Чтобы лучше понять, как работает формула, можно разобрать ее по частям и понять, как отрабатывает и какой результат возвращает та или иная часть формулы массива.

Скачать файл с примером можно по ссылке.

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, используйте обычные пробелы. А перед тем, как применять в формуле, мы их специальным образом обработаем при помощи функции ПОДСТАВИТЬ.

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

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

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

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

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

Этот фокус основан на применении функции ДВССЫЛ (INDIRECT) , которая умеет делать одну простую вещь — преобразовывать содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. То есть, если в ячейке лежит текст «А1», то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово «Маша», то функция выдаст ссылку на именованный диапазон с именем Маша и т.д. Такой, своего рода, «перевод стрелок» 😉

Возьмем, например, вот такой список моделей автомобилей Toyota, Ford и Nissan:

Связанные списки с ДВССЫЛ

Выделим весь список моделей Тойоты (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota на вкладке Формулы (Formulas) с помощью Диспетчера имен (Name Manager) . Затем повторим то же самое со списками моделей Ford и Nissan, задав соответственно имена диапазонам Ford и Nissan.

При задании имён помните о том, что имена диапазонов в Excel не должны содержать пробелов, знаков препинания и начинаться обязательно с буквы. Поэтому если бы в одной из марок автомобилей присутствовал бы пробел (например Ssang Yong), то его пришлось бы заменить в ячейке и в имени диапазона на нижнее подчеркивание (т.е. Ssang_Yong).

Теперь создадим первый выпадающий список для выбора марки автомобиля. Выделите пустую ячейку (на картинке выше — зелёную) и нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data) . Затем из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и в поле Источник (Source) выделите ячейки с названиями марок (желтые ячейки в нашем примере). После нажатия на ОК первый выпадающий список готов.

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

где F2 — адрес ячейки с первым выпадающим списком (замените на свой).

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

Минусы такого способа:

  • Надо руками создавать много именованных диапазонов (если у нас много марок автомобилей).
  • В качестве вторичных (зависимых) диапазонов не могут выступать динамические диапазоны задаваемые формулами типа СМЕЩ (OFFSET) . Для первичного (независимого) списка их использовать можно, а вот вторичный список должен быть определен жестко, без формул. Однако, это ограничение можно обойти, создав справочник соответствий марка-модель (см. Способы 3 и 4).
  • Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т.е. если в нем есть текст с пробелами, то придется их заменять на подчеркивания с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) , т.е. формула будет выглядеть как:

Способ 2. Умные таблицы

Этот подход очень похож на предыдущий, но использует вместо именованных диапазонов — «умные» динамические таблицы, которые будут выступать источником данных для вторичного списка:

Связанные списки на умных таблицах и ДВССЫЛ

Таким образом, мы:

  1. Сначала преобразуем наши справочники в «умные» таблицы, используя сочетание клавиш Ctrl + T или команду Главная — Форматировать как таблицу (Home — Format as Table) и
  2. Даём им имена (Фрукты, Овощи, Зелень) на вкладке Конструктор (Design) в поле Имя таблицы (Table Name) .
  3. Создаём первый и второй (связанный) выпадающие списки точно так же, как в предыдущем способе с функцией ДВССЫЛ (INDIRECT).

Способ 3. Отсортированный справочник

Этот способ требует наличия отсортированного списка соответствий марка-модель вот такого вида:

Связанные списки на отсортированном справочнике

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

А вот для зависимого списка моделей придется создать именованный диапазон с функцией СМЕЩ (OFFSET), который будет динамически ссылаться только на ячейки моделей определенной марки. Для этого:

  • Нажмите сочетание клавиш Ctrl + F3 или воспользуйтесь кнопкой Диспетчер имен (Name manager) на вкладке Формулы (Formulas) .
  • Создайте новый именованный диапазон с любым именем (например Модели) и в поле Ссылка (Reference) в нижней части окна введите руками следующую формулу:

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

Работает это следующим образом. Функция СМЕЩ (OFFSET) умеет выдавать ссылку на диапазон нужного размера, сдвинутый относительно исходной ячейки на заданное количество строк и столбцов. В более понятном варианте синтаксис этой функции таков:

=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; высота_диапазона_в_строках; ширина_диапазона_в_столбцах)

  • начальная ячейка — берем первую ячейку нашего списка, т.е. А1
  • сдвиг_вниз — нам считает функция ПОИСКПОЗ (MATCH), которая, попросту говоря, выдает порядковый номер ячейки с выбранной маркой (G7) в заданном диапазоне (столбце А)
  • сдвиг_вправо = 1, т.к. мы хотим сослаться на модели в соседнем столбце (В)
  • высота_диапазона_в_строках — вычисляем с помощью функции СЧЕТЕСЛИ (COUNTIF), которая умеет подсчитать количество встретившихся в списке (столбце А) нужных нам значений — марок авто (G7)
  • ширина_диапазона_в_столбцах = 1, т.к. нам нужен один столбец с моделями

В итоге должно получиться что-то вроде этого:

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

  • выделяем ячейку G8
  • выбираем на вкладке Данные (Data) команду Проверка данных (Data validation)
  • из выпадающего списка выбираем вариант проверки Список (List) и вводим в качестве Источника (Source) знак равно и имя нашего диапазона, т.е. =Модель

И наш связанный список моделей готов.

Способ 4. Неотсортированный справочник

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

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

Первый уровень выпадающего списка (диапазон А2:А14 на рисунке выше) здесь делается классическим образом через команду Данные — Проверка данных — Список (Data — Validation — List) и в качестве источника указать зелёные ячейки с названиями категорий.

А для связанных выпадающих списков во втором столбце (B2:B14) в поле Источник (Source) мы используем хитрую формулу:

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

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

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

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

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

Теперь для каждого значения категории создается свой список, после чего ему присваивается имя в диспетчере имен (вкладка «Формулы» -> Область «Определенные имена») согласно этому значению.

Присвоенные имена для связанных выпадающих списков excel

В завершении задаем для ячеек созданные нами связанные выпадающие списки с помощью проверки данных (вкладка «Данные» -> Область «Работа с данными»).

Для главного списка источником достаточно указать ссылку на диапазон, а для зависимых указывается формула:

=ДВССЫЛ($A$1), где:

  • $A$1 – ячейка с главным списком.

Параметры зависимых списков в excel

На этом зависимые выпадающие списки созданы, а так выглядит готовый результат:

Лист excel с перечнем для списков и ячейки с настроенными выпадающими связанными списками

  • < Назад
  • Вперёд >
  • Проверка данных Excel
  • Критерий Манна-Уитни
  • Подключение MySQL в Excel
  • Подключение Excel к SQL Server

Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.

Комментарии

Добрый день, Александр!

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

Пример использования списков

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

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

Отображение связанных списков при выборе основного элементаВ выпадающем списке отображаются только привязанные элементы

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

Если у вас возникли трудности при работе с корпоративным порталом, можете воспользоваться услугами наших специалистов. Техническая поддержка Битрикс24 от компании Первый БИТ включает настройку и автоматизацию списков, а также консультации и обучение сотрудников при необходимости. Детально ознакомится с услугами техподдержки можно обратившись по номеру в шапке или заказав обратный звонок на сайте.

голоса
Рейтинг статьи
Читайте так же:
Как включить ява скрипт в хроме
Ссылка на основную публикацию
Adblock
detector