🔡Сведение в одну ячейку данных, найденных по поиску по листу

Базовые принципы Google таблиц

Пример таблицы

Допустим, у нас есть таблица с названиями дорам (азиатских сериалов) и перечислением актёров, игравших главные роли в них.

Таблица доступна здесь (лист «Дорамы»).

Вывод уникальных значений (и их сортировка)

Представлено на листе «Все актёры»

В первую очередь мы хотим понять, какие вообще актёры есть в нашей таблице.

Для этого мы добавляем следующие формулы в следующие ячейки:

	A	B	C
1	Актёр	Актриса	Все актёры и актрисы
2	=UNIQUE(FILTER('Дорамы'!B2:B;'Дорамы'!B2:B<>""))	=UNIQUE(FILTER('Дорамы'!C2:C;'Дорамы'!C2:C<>""))	={UNIQUE(FILTER('Дорамы'!B2:B;'Дорамы'!B2:B<>""));UNIQUE(FILTER('Дорамы'!C2:C;'Дорамы'!C2:C<>""))}

1. С помощью формулы =FILTER('Дорамы'!B2:B;'Дорамы'!B2:B<>"") мы получаем список всех актёров-мужчин, перечисляющихся на листе «Дорамы»

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

3. Далее мы то же делаем с актрисами

4. А чтобы объединить значения из этих двух столбцов в один, мы можем две этих формулы прописать, соединив их в {формула 1; формула 2} — таким образом у нас получится один массив

Получение списка дорам с указанным актёром

Представлено на листе «Актёры и их дорамы»

Список актёров и актрис без дублей мы научились выводить выше. Осталось вывести дорамы, в которых они снимались.

Сразу уточню, что здесь мы не разберём автозаполнение столбца B (с дорамами), тк оно менее тривиально. Но в любом случае мы сможем облегчить себе жизнь.

	A	B
1	Актёр/Актриса	Дорамы
2	={UNIQUE(FILTER('Дорамы'!B2:B;'Дорамы'!B2:B<>""));UNIQUE(FILTER('Дорамы'!C2:C;'Дорамы'!C2:C<>""))}	=JOIN("; "; FILTER('Дорамы'!A:A; REGEXMATCH('Дорамы'!B:B&'Дорамы'!C:C; A2))

Итак, в ячейке B2:

1. С помощью 'Дорамы'!B:B&'Дорамы'!C:C мы создаём эфимерное объединение значений из ячеек, находящихся в одной строке, в одну ячейку. То есть, у нас получается следующий вид таблицы:

   B	C	B:B&C:C
   Ли Чонсок		Ли Чонсок
   Ли Джунки		Ли Джунки
   Ли Джунки	Ли Джиын	Ли ДжункиЛи Джиын
   Хёнбин	Пак Шинхе	ХёнбинПак Шинхе
   	Юн Ынхе	Юн Ынхе
   Ан Хёсоп	Ким Седжон	Ан ХёсопКим Седжон
   Ли Чонсок		Ли Чонсок
   Чжи Чанук	Ха Дживон	Чжи ЧанукХа Дживон
   Ли Джунки		Ли Джунки
   	Ли Джиын	Ли Джиын
   Чжи Чанук		Чжи Чанук
   Хёнбин	Ха Дживон	ХёнбинХа Дживон
   Чжи Чанук		Чжи Чанук
   Ли Минхо		Ли Минхо

2. Далее с помощью зелёной части формулы =REGEXMATCH('Дорамы'!B:B&'Дорамы'!C:C;A2) мы ищем, в каких строках столбца B:B&C:C содержится имя «Ли Чонсок»

   B:B&C:C	Результат проверки
   Ли Чонсок	Нашлось
   Ли Джунки	Не нашлось
   Ли ДжункиЛи Джиын	Не нашлось
   ХёнбинПак Шинхе	Не нашлось
   Юн Ынхе	Не нашлось
   Ан ХёсопКим Седжон	Не нашлось
   Ли Чонсок	Нашлось
   Чжи ЧанукХа Дживон	Не нашлось
   Ли Джунки	Не нашлось
   Ли Джиын	Не нашлось
   Чжи Чанук	Не нашлось
   ХёнбинХа Дживон	Не нашлось
   Чжи Чанук	Не нашлось

3. А фиолетовая часть формулы =FILTER('Дорамы'!A:A;REGEXMATCH('Дорамы'!B:B&'Дорамы'!C:C;A2) позволяет нам получить названия тех дорам, где играл актёр, имя которого указано в ячейке A2

   Дорама
   W: Меж двух миров
   Доктор-чужестранец

4. Наконец, осталось перечислить все дорамы, в которых он играл, через точку с запятой и пробел. Реализуем мы это с помощью JOIN, а именно: =JOIN("; "; FILTER('Дорамы'!A:A; REGEXMATCH('Дорамы'!B:B&'Дорамы'!C:C; A2)) Где первая часть — "; " — отвечает, через какой символ (или набор символов) мы будем объединять наши значения, а вторая — какие значения мы, собственно, будем объединять И в результате мы получаем следующие данные:

   Актёр/Актриса	Дорамы
   Ли Чонсок	W: Меж двух миров; Доктор-чужестранец

Заполнение данных в столбце

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

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

1. Нажимаем на ячейку с формулой (в нашем случае — ячейка B2)

2. Наводим курсор мыши на её правый нижний угол (где ещё расположена жирная точка)

3. Когда курсор превращается во что-то, похожее на крестик, дважды по нему кликаем

4. Готово! Наша формула распространилась на весь столбец 👏