Как создать уникальный список из нескольких листов с помощью формулы Excel
Я работаю над платежной ведомостью, и мне нужно извлечь Уникальный идентификатор сотрудника из нескольких листов рабочей книги и поместить их в одну и ту же рабочую книгу на другом листе.
Хотя я могу создать формулу, чтобы получить эти Уникальный список, я не могу сделать это динамичный, так как каждый месяц я буду импортировать новый лист в книгу, и это следует учитывать, что не работает с моя формула.
Я пытался использовать функцию INDIRECT для динамической ссылки на все листы, но напрасно, может быть, я делаю что-то не так. Я знаю, что это можно сделать с помощью Power Query, но я не хочу менять структуру базы данных, также это возможно с помощью VBA, но я не хочу этого, особенно хочу выполнить это с помощью Формула Excel.
Приведенная ниже формула, которую я использовал в ячейке Master_List А2
= "ID_"&SORT(SUBSTITUTE(UNIQUE(
FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",,Blad1:Blad3!A2:A1000)
,",","</b><b>")&"</b></a>","//b")),"ID_","")+0)
Я пытался использовать это как SHEETS Определенное имя в формуле, но выдает ошибку #ССЫЛКА
=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")&T(NOW())
Приведенная выше формула используется для захвата данных других листов для расчетов, пытался реализовать ее в формуле, обернув ее в функцию INDIRECT, но не работает, я знаю, почему нельзя сделать ее динамической или есть любой обходной путь. надеюсь смогу объяснить. Спасибо за усилия и время.
Примечание. Это пример данных, созданный для запроса.
Ответы 1
Если вы настаиваете на формулах, вот что я сделал, чтобы это сработало:
- Я создал формулу имени в менеджере имен:
SHEETNAME. Это относится к:=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"") - Предполагая, что у вас есть как минимум два листа («Мастер» и любые другие листы имеют идентификаторы в столбце A;
Теперь я использовал в A2 в мастерлисте:
=UNIQUE(FILTERXML("<t><s>"&REDUCE("",SEQUENCE(SHEETS()-1,,2),LAMBDA(a,b,TEXTJOIN("</s><s>",,a,INDIRECT(INDEX(SHEETNAME,b)&"!A2:A100"))))&"</s></t>","//s"))
Примечание1: Я предположил, что у вас есть только идентификаторы в диапазоне A2:A100, чтобы упростить задачу.
Заметка 2: Такое использование TEXTJOIN() может довольно быстро выйти за пределы своих возможностей.
Заметка 3: Вы можете попытаться вложить 2-й UNIQUE(), чтобы убедиться, что каждая итерация обрабатывает как можно меньше записей. Мы надеемся, что это гарантирует, что пределы TEXTJOIN() не будут достигнуты так быстро.
SHEETS() — это нативная функция, а не функция, которую вы должны создать сами @MayukhBhattacharya. Так что удалите это из своего менеджера имен.
Сэр, как я понял, я говорю, нужно ли мне использовать другое определенное имя для SHEETNAME, поскольку я уже использую одно, определенное как Sheets -> =SUBSTITUTE(GET.WORKBOOK(1),"["&GET.WORKBOOK(16)&"]","")&T(NOW()) , и сэр, как вы думаете, то, что я пытался достичь, действительно.
Если это работает, это действительно. INDIRECT() уже изменчив, поэтому я думаю, что использование NOW() больше не окажет такого негативного влияния.
Сэр, это сработало отлично, как шарм! Действительно
FILTERXMLэто удовольствие! Итак, у меня есть 5 листов до сих пор, и я должен учитывать {3,4,5} листов, поэтому я отредактировал эту часть в своей книгеSEQUENCE(SHEETS()-2,,3). Кроме того, поскольку эти идентификаторы являются числовыми, я также использовал функциюSORTв начале, у меня уже естьSheetsопределено, нужно ли мне использовать тот, который вы дали, или я должен продолжать использовать тот, который я использую.