Переместить данные в другую таблицу на основе значения массива
У меня есть две таблицы Excel:
Процесс таблицы:
Заказы столов:
Когда есть тег со значением ProcessID, я хотел бы заполнить столбец OrderID в таблице Process идентификаторами OrderID из таблицы Orders.
Я изучал массивы Excel, поэтому при необходимости знаю, как изменить теги на что-то вроде {217,218,229,230}.
Вот какой результат я ищу:
МОЕ СОБСТВЕННОЕ РЕШЕНИЕ:
После обнаружения проблем с решением, предложенным в ответах ниже - для полей большего размера (тегов), я подумал, что нашел другое решение, которое разместил здесь, но сейчас удаляю его, поскольку оно не работает должным образом, как я позже понял.
В итоге я добавил первую и последнюю скобки в строку «Теги», а затем перенес свои данные в Access, где смог легко добиться того, чего хотел, с помощью простого SQL-запроса:
UPDATE Processes, Orders
SET Processes.OrderID = [Orders].[ID]
WHERE (([Orders].[Tags] Like "*|" & [Processes].[ID] & "|*"));
Ответы 3
Вероятно, с Excel365 есть более простой способ, но, по крайней мере, в более старых версиях это работает:
Формула в столбце C:
=INDEX($E$2:$E$9;SUMPRODUCT(--(IFERROR(SEARCH("|"&A2&"|";"|"&$F$2:$F$9&"|");0)>0)*ROW($F$2:$F$9))-1)
Чтобы это работало, вам нужно ввести формулу как формулу массива, нажав CTRL+SHIFT+ENTER.
@sasos — Да, у вас недостаточно известности, чтобы проголосовать, но вы можете принять один из двух ответов, сделав принятый ответ (отметив флажок слева от кода). Тот, который вам больше нравится...
Принял к сведению и сделал. Я вернусь, когда наберу достаточно баллов для голосования :) Еще раз спасибо.
Вы можете сделать это с помощью формулы:
=IFERROR(INDEX(OrderTab!A:A,MATCH("*|"&A2&"|*","|"&OrderTab!B:B&"|",0)),"Not Found")
Предположим, что ваша таблица заказов находится на листе под названием OrderTab. Измените это.
Результат:
Проголосовали за, действительно более короткую формулу массива.
Сначала я получил сообщение «Проблема с формулой», но решил эту проблему, заменив все запятые (,) точками с запятой (;). Я думаю, что это правильная настройка для моего языка. Но теперь я получаю сообщение «Не найдено» во всех полях. :/
Я добавил скриншот в свой первый пост
Это тоже работает! Я не использовал CTRL+SHIFT+ENTER, как предложено в сообщении Foxfire, так что спасибо вам обоим :)
Хотя здесь диапазоны рассматриваются как массивы, это не формула массива. Я рад, что у тебя все получилось.
Кажется, возникла проблема с большим количеством тегов в порядках таблиц. Если количество тегов около 50 и выше, эта функция больше не работает. Если я разобью теги на две строки, сохраняя одинаковое значение идентификатора, все снова будет работать нормально. Есть какие-нибудь советы, как это автоматизировать? У меня около 1500 строк в таблице Orders. Было бы лучше опубликовать для этого новый вопрос?
Кажется, существует ограничение на количество символов, которые могут находиться в одной ячейке «тегов» (255). Возможно, вам все-таки нужен vba.
Если у вас есть Microsoft 365, вы можете попробовать это (не соревнуясь за короткий ответ здесь :)
- для каждого идентификатора процесса
MAP(process_ids, LAMBDA(process_id - в тегах для идентификаторов заказов, для тегов для каждого идентификатора заказа
MAP(tags_for_order_ids, LAMBDA(tags_for_order_id, - разделите теги и найдите совпадения
OR(process_id = TRIM(TEXTSPLIT(tags_for_order_id, "|"))` - вернуть идентификатор заказа, если он совпадает, удалив несоответствия (
#NA) с помощьюTOCOL - Введите следующее в первую ячейку (
C2в примере), заполнять не нужно
=LET(
process_ids, TRIM(A2:A9),
tags_for_order_ids, OrderTab!B2:B9,
order_ids, OrderTab!A2:A9,
MAP(
process_ids,
LAMBDA(process_id,
IFERROR(
TOCOL(
IF(
MAP(
tags_for_order_ids,
LAMBDA(tags_for_order_id,
OR(
process_id =
TRIM(TEXTSPLIT(tags_for_order_id, "|"))
)
)
),
order_ids,
NA()
),
3
),
"no match"
)
)
)
)
Начало новой строки текста внутри ячейки в Excel - Служба поддержки Microsoft
Этот работает! Спасибо! Если кто-то может проголосовать, пожалуйста, у меня недостаточно баллов :(