Как создать выпадающие списки без повторов в Гугл Таблицах

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

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

Подготовка данных и настройка таблицы

Для начала создайте таблицу с тремя основными разделами:

1. Список студентов (столбцы A, B, C). В каждой строке укажите имя ученика и его возможных партнеров за предыдущие раунды. Например:

– A2: Анна (текущий студент)
– B2: Борис (партнер в раунде 1)
– C2: Виктор (партнер в раунде 2).

2. Текущий раунд (столбец D). Здесь будет выпадающий список для выбора нового партнера.

Важно: не оставляйте пустых строк между данными. Если у вас 30 студентов, заполните все строки с A2 по A31. Иначе фильтры могут работать некорректно.

Вставляем формулу для фильтрации повторов

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

=let(
  namesList, filter(A2:C, A2:A<>""),
  roundlist, filter(D2:D, A2:A<>""),
  byrow(namesList, lambda(names,
    ifna(
      index(namesList, match(index(names,,1), roundList, 0), 1),
      torow(
        let(
          f, filter(
            index(namesList,,1),
            index(namesList,,1)<>index(names,,1),
            index(namesList,,1)<>index(names,,2),
            index(namesList,,1)<>index(names,,3)
          ),
          ifna(filter(f,ifna(match(f, roundList, 0)=0, true)))
        )
      )
    )
  ))
)

Поясню, что делает каждая часть:

namesList — выбирает всех студентов из столбцов A-C, игнорируя пустые ячейки.
roundlist — собирает уже сделанные выборы для текущего раунда.
filter(f, …) — последовательно проверяет три условия: новый партнер не должен совпадать с предыдущими (B2, C2 и т.д.).
match(f, roundList, 0)=0 — исключает имена, которые уже выбрали другие студенты в этом раунде.

Если формула кажется сложной, не пугайтесь. Просто вставьте её как есть, предварительно заменив A2:C и D2:D на ваши реальные диапазоны.

Настройка выпадающих списков и проверка работы

1. Выделите ячейки в столбце D (текущий раунд).
2. В меню выберите «Данные» → «Проверка данных».
3. В разделе «Критерии» укажите: «Список из диапазона» и вставьте туда нашу формулу.
4. Поставьте галочку в пункте «Показывать выпадающий список в ячейке».

Чтобы убедиться, что всё работает:

• Введите первого студента (например, Анна) в A2.
• В D2 выберите Бориса из списка.
• Перейдите к следующему студенту (Борис в A3). Его выпадающий список уже не будет содержать Анну — она автоматически исключится.

Важные нюансы:

• Если вы видите ошибку #N/A, проверьте, не остались ли пустые ячейки в столбце A.
• Для добавления новых раундов просто скопируйте столбец D и вставьте его рядом (например, E, F и т.д.), обновив ссылки в формуле.
• Если студентов больше 30, расширьте диапазоны в формуле (замените A2:C31 на A2:C50 и т.д.).

Кстати, если вам нужно больше трёх предыдущих партнеров, добавьте в формулу новые условия:

index(namesList,,1)<>index(names,,4),
index(namesList,,1)<>index(names,,5)

— и так далее, по количеству необходимых проверок.

Заключение

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

1. Заблокировать столбцы с формулами (правая кнопка мыши → Защитить диапазон).
2. Регулярно копировать данные в резервный файл.
3. Проводить тестовые запуски с 3-4 студентами перед заполнением полной версии.

Если что-то пойдет не так, проверьте:

• Все ли имена вписаны без опечаток.
• Нет ли дубликатов в столбце A.
• Корректно ли указаны диапазоны в формуле.

Готово! Остались вопросы? Напишите в комментариях — обязательно помогу разобраться.