Как создать несколько зависимых раскрывающихся списков в Excel
Возможно, вы знаете, как создать раскрывающееся меню в Excel, но вы можете не знать, как создать несколько зависимых раскрывающихся меню.
Существуют различные подходы к созданию нескольких раскрывающихся меню, некоторые из которых легко выполнить, а другие – сложно. В этой статье объясняется, как сделать это относительно быстро с помощью одной формулы смещения.
Пример создания нескольких зависимых раскрывающихся списков
Давайте посмотрим на данные ниже, для которых вы собираетесь создать зависимый раскрывающийся список, используя формулу смещения.
Здесь вы можете увидеть три разных лиги, каждая со своим списком команд. Чтобы упростить концепцию, в каждой лиге может быть только небольшое количество команд, а не полный список.
Слева у вас есть два варианта выбора лиги и соответствующей ей команды. Выбор вашей команды будет зависеть от того, какую лигу вы выберете, поскольку второй вариант зависит от первого выбора.
Наша цель – создать простое раскрывающееся меню для названий лиг и зависимое раскрывающееся меню для списка каждой лиги.
Создание простого раскрывающегося меню для футбольных лиг
1. Перейдите на вкладку «Данные» и нажмите « Проверка данных» .
2. Выберите параметр « Список в разрешении» в критериях проверки.
3. Выберите ячейки от E4 до G4 в качестве источника.
4. Щелкните OK, чтобы применить изменения.
Всего за три простых шага вы можете создать простой раскрывающийся список. Затем скопируйте и вставьте формулу в остальные ячейки в строке.
Создание зависимого раскрывающегося списка в Excel
Выпадающее меню футбольной команды основано на только что созданном простом раскрывающемся списке. Если вы выбираете конкретную лигу, у вас должна быть возможность выбрать команду из раскрывающегося меню Футбольной лиги, содержащего только команды из этой лиги.
Использование формулы смещения для создания раскрывающегося списка
Давайте создадим формулу, чтобы убедиться, что она работает безупречно, прежде чем напрямую вставлять ее в поле проверки данных. После этого вы можете приступить к его реализации для всего набора данных.
Давайте посмотрим на синтаксис функции Offset.
Функция смещения имеет пять аргументов. Давайте кратко их обсудим здесь:
1. Ссылка: Относится к отправной точке данных. Функция смещения дает диапазон, близкий к контрольной точке. Следовательно, контрольная точка должна быть близко к набору данных.
2. Строки: аргумент строк указывает на количество строк, которые вы хотите переместить вниз от контрольной точки.
3. Столбец: как и строки, этот аргумент описывает количество мест, которые вы хотите переместить в столбцах набора данных. Поскольку положение столбца зависит от футбольной лиги, включенной в наш простой раскрывающийся список, вам может потребоваться использовать функцию сопоставления в качестве аргумента столбца.
4. Высота и ширина: эти два аргумента относятся к положению ячеек, в которых вы в данный момент находитесь, на основе аргументов строк и столбцов, выбранных ранее. Вы должны подсчитать это вручную, поэтому будьте осторожны при добавлении значения. Кроме того, проверьте его дважды для подтверждения.
Давайте реализуем функцию смещения в этом наборе данных, чтобы лучше понять концепцию.
Реализация функции смещения
Здесь ячейка E4 является ссылкой, поскольку это отправная точка набора данных. Кроме того, планируется скопировать ту же формулу в другие ячейки в строке, чтобы вы могли сделать ее абсолютной ссылкой на ячейку, добавив знак $ .
Поскольку название команды начинается ниже контрольной точки, аргумент строки будет равен 1.
Однако аргумент высоты может изменяться между 0, 1 и 2, и вы не можете добавить его в каждую ячейку вручную. Чтобы заполнить другие вкладки формулой, вы можете использовать функцию сопоставления, которая правильно назначает номер столбца. Давайте обсудим это кратко, не вдаваясь в подробности.
Синтаксис функции соответствия
Искомое_значение поисковый_массив и тип_сопоставления являются три аргумента в функции соответствия.
В этом примере значение в ячейке B5 – это значение поиска, а список названий лиг в ячейках с E4 по G4 – это массив поиска. Из match_type выберем точное совпадение.
Выберите функцию всего матча и нажмите F9, чтобы проверить, правильно ли выбрана позиция столбца для выбранной футбольной лиги в простом раскрывающемся меню. Функция сопоставления начинает отсчет с первого столбца и рассматривает ячейку E4 в позиции один, которая является контрольной точкой.
С другой стороны, смещение начинает отсчет с 0. Функция сопоставления также должна принимать ссылочный столбец в нулевой позиции, чтобы согласовать его с функцией смещения. Чтобы изменить это, вычтите единицу из всей формулы.
Затем установите высоту на максимальное количество значений в раскрывающемся списке и ширину на одно. Это соответствует положению строки и столбца в формуле.
Нажмите клавишу ВВОД, чтобы убедиться, что формула выбрала правильную команду.
Теперь, когда формула готова, давайте добавим ее для проверки данных.
Добавление формулы для проверки данных
1. Нажав CTRL + C , вы можете скопировать формулу из выбранной ячейки.
2. Перейдите к проверке данных .
3. Поместите скопированную формулу в качестве источника после выбора списка в качестве первого варианта.
После выполнения формула сгенерирует несколько зависимых раскрывающихся меню для футбольных команд.
Скопируйте и вставьте формулу для футбольных команд вниз по строке, чтобы реализовать ее по всей строке.
Вот как вы можете использовать формулу смещения и функцию сопоставления в сочетании для создания раскрывающегося списка с множественной зависимостью. Поначалу формула смещения может показаться запутанной, но вы привыкнете к ней, применив ее несколько раз.
Упростите создание раскрывающихся меню с помощью формулы смещения
Многие листы на рабочем месте требуют создания раскрывающихся списков. Подход с использованием формулы смещения – это самый быстрый и простой метод создания всего раскрывающегося списка с использованием только одной формулы.
Кроме того, можно вручную выбирать ячейки для проверки данных для создания раскрывающихся списков. Однако создание раскрывающихся списков для большого набора данных вручную занимает много времени, и вероятность ошибки выше. Как и в Excel, вы также можете создать раскрывающееся меню для Google Таблиц.