Создайте таблицу на новом листе с данными из двух разных таблиц на двух разных листах в Excel
Я новичок в Excel и в настоящее время изо всех сил пытаюсь правильно понять промежуточные формулы.
Вот чего я пытаюсь достичь.
А) У меня есть три листа,
- СОТРУДНИК, 2. ПРОЕКТЫ и 3. ВОВЛЕЧЕННОСТЬ
Б) Лист СОТРУДНИК содержит информацию о сотруднике. Идентификатор сотрудника (EMP_ID) генерируется из введенного имени.
C) Лист ПРОЕКТЫ содержит информацию об обработанных или обрабатываемых проектах. Идентификатор проекта генерируется из названия проекта. На данный момент наиболее важной частью является столбец «Ресурс 1» — «Ресурс 20». На данный момент для проекта может быть выделено не более 20 ресурсов. Ресурсы можно выбрать из выпадающего списка.
То, чего я пытаюсь достичь, это то,
На листе «ВОВЛЕЧЕННОСТЬ» все проекты, в которых задействован ресурс, должны отображаться в столбцах «Проект 1» — «Проект 10» (при условии, что человек может работать не более чем над 10 проектами).
Мне нужно показать статистику по проектам между датой НАЧАЛА и ОКОНЧАНИЕМ на листе ВОВЛЕЧЕННОСТИ. Однако это не является высшим приоритетом.
Обратите внимание, что я хочу добиться этого только с помощью формул, а не скрипта или макроса.
Пожалуйста, посмотрите на прикрепленные скриншоты и лист, который я приложил.
Спасибо, Дэйв
Скриншоты и ссылки можно скачать по приведенным ниже ссылкам.
[1]: https://i.stack.imgur.com/kTcjW.jpg
[2]: https://i.stack.imgur.com/yLyZI.jpg
[3]: https://i.stack.imgur.com/WeoTj.jpg
[4]: https://i.stack.imgur.com/LgDiW.jpg
[5]: https://docs.google.com/spreadsheets/d/1qikvz4X6bvKO8PyB_nrmZA64x6StUMlh/edit?usp=sharing&ouid=109764009612506156621&rtpof=true&sd=true
Ответы 1
=ARRAYFORMULA(IFERROR(SPLIT(IFNA(VLOOKUP(TRIM(A3:A),
SPLIT(SUBSTITUTE(TRIM(FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(QUERY(FLATTEN(IF(DAYS(
IF((PROJECTS!D2:D4 = "")*(PROJECTS!C2:C4<>""), TODAY(), PROJECTS!D2:D4), PROJECTS!C2:C4)>
SEQUENCE(1, 1000, ), ROW(PROJECTS!A2:A4)&"×"&PROJECTS!C2:C4+SEQUENCE(1, 1000, )+1, )),
"where Col1 is not null")&"×"&TRANSPOSE(QUERY(FLATTEN(
IF(PROJECTS!I2:4 = "",,ROW(PROJECTS!A2:A4)&"×"&PROJECTS!I2:4&"×"&PROJECTS!A2:A4)),
"where Col1 is not null"))), "×"), "select max(Col5) where Col1=Col3 "&
IF(D1 = "",," and Col2> = "&VALUE(D1))&
IF(F1 = "",," and Col2< = "&VALUE(F1))&
" group by Col5 pivot Col4"),,9^9))), " ", "×", 1), "×"), 2, 0)), " ")))
Привет player0, Честно говоря, я никогда в жизни не видел такой сложной и длинной формулы. Немного поискав, я понял, что это своего рода формула динамического массива, которая для меня абсолютно нова. Так что, пожалуйста, извините меня, если я делаю что-то глупое. Я скопировал и вставил формулу, которую вы предоставили, в C3. В тот момент, когда я нажимаю ENTER (также пробовал CTRL + SHIFT + ENTER), я получаю сообщение об ошибке «Эта функция недействительна» и выделено SPLIT. Не могли бы вы направить меня дальше? Спасибо, Дэйв.
@DaveGueldner здесь: docs.google.com/spreadsheets/d/…
Спасибо за вашу помощь. Тем не менее, я все еще изо всех сил пытаюсь заставить его работать, поскольку я намереваюсь, чтобы он работал. 1. Если вы проверите лист, которым вы поделились со мной, вы обнаружите, что я добавил нового человека в EMPLOYEE, то есть SAM_GRU, а также добавил новый проект HELL_WOR в PROJECTS. 2. ДАВИ_МОН, СУХ_С. и SAM_GRU был назначен HELL_WOR. Я ожидал, что в листе ЗАЯВЛЕНИЯ будут отражены изменения, которых он не внес. Очевидно, я делаю что-то не так. Но, не могу понять что. Наконец, как я уже говорил, формула работает с Google Sheet. Однако это не с MS Excel.
@DaveGueldner в PROJECTS!C4 тебе нужно свидание. затем см. PROJECTS!K4 и EMPLOYEE!A6, где есть дополнительное пространство, поэтому, если эти два не совпадают, вы не получите никаких результатов. эта формула предназначена только для листов Google. половина используемых функций не поддерживается MS Excel
Благодарю за разъяснение. Пробовал и работает как положено. Однако надеялся найти способ с Excel. Но в любом случае, это будет работать на данный момент.
Привет игрок0. Наконец заполнил лист данными и внес необходимые изменения. Но лист УЧАСТИЯ больше не работает. Меня не интересуют проекты, с которыми не связаны даты. Но, как видите, формула ломается. Предположительно, в формуле есть что-то для диапазонов (похоже, что учитываются только 3 строки). Я не могу это исправить, так как это слишком продвинутая формула для меня. Пожалуйста, помогите мне. Лист находится здесь: docs.google.com/spreadsheets/d/…
@DaveGueldner отредактировал, см. свой лист
Прочтите Как спросить и сузьте свой вопрос. Люди вряд ли возьмутся за скачивание пяти файлов.