У меня есть список идентификаторов и описаний на Листе 1. Затем эти данные используются на втором листе (Лист 2), с которым пользователь может взаимодействовать.
Список на Листе 1 имеет повторяющийся идентификатор, но с другим описанием. Как я смогу использовать проверку данных на Листе 2, чтобы объединить все повторы идентификатора номер 4 в 1, но позволить пользователю выбрать желаемое описание (используя раскрывающийся список) после того, как сделан выбор в Столбце A?
Я бы хотел воздержаться от использования макросов в этом случае, используя только встроенные функции.
Любая помощь приветствуется!
решение1
Объедините повторы в Sheet1. Формулы поиска возвращают только первое найденное значение, поэтому, если у вас есть несколько одинаковых идентификаторов, вам нужно будет создать поиск с уникальными идентификаторами. Подготовьте это во вспомогательной таблице и используйте вспомогательную таблицу для проверки данных, если вы не хотите или не можете изменить исходную таблицу.
Совершенно ошибочно полагать, что идентификатор (который не зря так называется) не является уникальным.
решение2
Вы не упомянули версию Excel, которую используете. Я предлагаю возможное решение, основанное на моем понимании вашего вопроса. Однако оно также использует функции с именами ЕСЛИОШИБКА и СЧИТАТЬПУСТОТЫ, которые, как я предполагаю, не были доступны до Excel 2007. Так что в случае, если у вас Excel 2003, может потребоваться другой подход.
Это может быть не совсем идеальным решением, поскольку оно использует вспомогательные столбцы, а также вспомогательный лист!
В этом примере данные образца находятся на Листе1!A2:B10, как показано на этом снимке экрана.
Сначала нам нужно создать вспомогательный столбец в столбце D, чтобы получить уникальный список из ваших идентификаторов.
В D2 введите следующую формулу, а затем нажмите CTRL+ SHIFT+ ENTERв строке формул, чтобы создать формулу массива. Вам также нужно оставить строку (предпочтительно первую строку) в качестве заголовка, чтобы это работало правильно.
Формула должна быть заключена в фигурные скобки, чтобы указать, что это формула массива.
Перетащите его вниз до предполагаемых строк на основе ожидаемого количества уникальных значений в вашем главном столбце. В конце концов он начнет возвращать пробелы внизу, тогда вы можете остановиться. Это создаст уникальный главный список идентификаторов.
=IFERROR(INDEX($A$2:$A$10, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$10), 0)),"")
Теперь нам нужно знать точное начало и конец этого списка, чтобы заполнить список проверки.
Теперь в G3 поместите следующую формулу.
=INDEX(Sheet1!D2:D10,1)
и в G4 поместите следующую Формулу массива.
=MIN(IF(Sheet1!D2:D10="",ROW(Sheet1!D2:D10),9^99))-2
Не забудьте поставить CTRL+ SHIFT+ ENTERза это.
Теперь перейдите в Диспетчер имен и создайте новое имя под названием MyList.
Вставьте в него следующую формулу:
=INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$3):INDEX(Sheet1!$D$2:$D$10,Sheet1!$G$4)
Здесь мы используем INDEX для возврата ссылки на ячейку, а не значения.
Вставьте новый лист с именем Sheet2.
Теперь в столбце A2 и ниже создайте список проверки и поместите =MyListтуда.
Теперь все уникальные значения отображаются в первом раскрывающемся списке идентификаторов.
На этом Часть 1 завершена.
Теперь следующая задача — заполнить второй список проверки на основе данных, извлеченных из столбца «Описание».
Вставьте третий вспомогательный лист под названием Sheet3. Вы можете просто скрыть этот лист.
В этом примере я взял данные в Sheet3!B2:H10. Определите количество столбцов на основе максимального количества дубликатов в ваших идентификаторах из основного списка. Также оставьте первый столбец A, чтобы это работало правильно.
В B2 введите следующую формулу и нажмите CTRL+ SHIFT+ ENTERв строке формул, чтобы создать формулу массива.
Перетащите его вниз по горизонтали и ниже, как и предполагалось.
=IFERROR(INDEX(Sheet1!$B$2:$B$10, SMALL(IF(Sheet2!$A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$A$2:$A$10)-1,999),COLUMN()-COLUMN($A$2))),"")
Вернитесь в Диспетчер имен, создайте новое имя с именем Trimmed и введите в него следующую формулу.
=OFFSET(Sheet3!$B1,0,0):OFFSET(Sheet3!$B1,0,COUNTA(Sheet3!$B1:$H1)-COUNTBLANK(Sheet3!$B1:$H1)-1)
Это создаст список, исключающий пробелы, чтобы список был правильно обрезан.
Теперь на Листе 2 для ячеек B2 и ниже создайте Список проверки и поместите =Trimmedтуда.
Теперь при изменении данных в Sheet1!A2:A10 список уникальных значений автоматически отразится в MyList, а затем имя Trimmed будет извлекать только предполагаемое описание из столбца «Описание» Sheet1.
Посмотрите на GIF-изображение экрана ниже, чтобы понять, как все это работает.
