#google-sheets
Вопрос:
Я ищу, чтобы построить формулу, которая будет проверять столбцы на наличие истинного значения и, если значение true, возвращать имя первого столбца по порядку…
Ниже приведена формула, которую я пытался построить, но она работает не так, как ожидалось, хотя я думаю, что я близок к этому.
=ARRAYFORMULA(IF(HLOOKUP($A16,{Sheet1!$B$2:$E$5,Sheet2!$B$8:$E$11},{3,4,5},0)=TRUE,{Sheet2!$A$3:$A$5,Sheet1!$A$3:$A5,""))
Позвольте мне проиллюстрировать это. Листы 1 и 2 содержат имена с указанием того, у кого что есть (пункты с флажками). С формулой в ячейке B16, которая будет заполнена как справа, так и внизу всеми именами, содержащими ИСТИННОЕ значение в искомом значении (A:A).
Изображение выше было введено вручную, формула вообще не работает.
Пожалуйста, помогите!
РЕДАКТИРОВАТЬ: Ссылка на лист
Комментарии:
1. могут ли имена повторяться в обеих ваших таблицах?
2. Я добавил ссылку на лист. Имена могут повторяться «да», но не на одном листе. Просто на разных листах, и проверка 1, проверка 2 и т. Д. Всегда будут уникальными
Ответ №1:
пробовать:
=INDEX(IFERROR(VLOOKUP(A14:A, SUBSTITUTE(REGEXREPLACE(SPLIT(FLATTEN(
QUERY(SUBSTITUTE(QUERY(SPLIT(FLATTEN(
IF({B3:E5; B9:E11}=TRUE,
{B2:E2amp;"♦"amp;A3:A5amp;"♣"amp;ROW(A3:A5);
B8:E8amp;"♦"amp;A9:A11amp;"♣"amp;ROW(A9:A11)}, )), "♦"),
"select max(Col2) where Col2 is not null group by Col2 pivot Col1"),
" ", "♥"),,9^9)), " "), "(♣d )", ), "♥", " "),
SEQUENCE(1, COUNTUNIQUE(B2:E2, B8:E8)) 1, 0)))
демонстрационный лист
Комментарии:
1. Похоже, это работает в вашем примере, но я не могу изменить его, чтобы он соответствовал нескольким листам. Как я связал в своем первоначальном посте примерный лист и изменил ваш код, чтобы отразить ячейки, на которые ссылаются. Также ничего себе, какая безумная формула. Хотел бы я понять хотя бы половину из этого…
2. @КевинП. посмотрите свой лист
3. Потрясающая работа! Я перенесу это в свой реальный мир, и мне будет с чем поиграть. Пара вопросов, чтобы я мог обдумать это. Существуют ли коды ключей для присвоения каждому экземпляру уникального значения? Если у меня будет более 20 имен, понадобится ли мне более 20 идентификаторов? Что делает 9^9? ♣d ? а последнее «♥»?
4. все, что вам нужно, — это расширить ассортимент в соответствии с вашими потребностями. если вы подразумеваете под «кодами ключей» *789 — это просто дополнительный материал для смещения диапазонов. скорее всего, вы можете отказаться от него. 9^9 — это просто смехотворно большое число-может быть любым числом, которое вы пожелаете, если оно превышает общее количество строк всех диапазонов вместе взятых. то, что он делает, заставляет ЗАПРОС сжимать все строки в одну строку. ♥ ♦ ♣ — это просто уникальные символы… вы можете использовать любые нужные символы, которых нет в вашем наборе данных. ♣d ? является выражением регулярного выражения и означает удаление♣, за которым следует любое число.
5. замена пробелов в вашем наборе данных на ♥ происходит из — за ЗАПРОСА(,,9^9), который имеет небольшой побочный эффект — сжимая все ячейки в одну, он соединяет все ячейки с пустым пространством-что в данном случае довольно удобно, потому что мы можем красиво разделить его на это пустое пространство (но не начальные пробелы, потому что это все испортит, поэтому мы преобразуем все пробелы для сердец, делаем запрос разбитым и разделенным, а затем преобразуем обратно сердца в пробелы
Ответ №2:
Вот еще один подход (аналогичный подходу player0, но достаточно другой, чтобы оправдать отдельный ответ:
=ArrayFormula(IFERROR(TRIM(SPLIT(VLOOKUP(FILTER(A3:A,A3:A<>""),REGEXREPLACE(SPLIT(FLATTEN(QUERY(QUERY(SPLIT(QUERY(FLATTEN({IF(Sheet1!B2:E<>TRUE,,Sheet1!B1:E1amp;"~|"amp;Sheet1!A2:Aamp;",");IF(Sheet2!B2:E<>TRUE,,Sheet2!B1:E1amp;"~|"amp;Sheet2!A2:Aamp;",")}),"Select * WHERE Col1 Is Not Null"),"|"), "Select MAX(Col2) where Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1"),, 9^9)),"~"),"[,s] $",""),2,FALSE),","))))
Я добавил его в вашу электронную таблицу на новом листе («Справка Эрика»).
Если вам нужно включить более двух листов, просто найдите этот раздел формулы и продолжите шаблон:
{ IF(Sheet1!B2:E<>TRUE,,Sheet1!B1:E1amp;"~|"amp;Sheet1!A2:Aamp;","); IF(Sheet2!B2:E<>TRUE,,Sheet2!B1:E1amp;"~|"amp;Sheet2!A2:Aamp;",") }
Комментарии:
1. Большое вам спасибо! Мне нравится не полагаться на смайлики или строки char (). Это помогает разобрать эту формулу для использования на НЕСКОЛЬКИХ листах. Я был далек от своей формулы примера… По крайней мере, оставшиеся 75% lol.
2. Пожалуйста. На самом деле не имеет значения, что такое временный символ соединения/разделения, если он наверняка никогда не появится в фактических данных листа (в вашем случае имена или контрольные метки). Вот почему вы часто увидите таких персонажей, как символ клуба player0: потому что мы знаем, что это никогда не появится. Я склонен использовать символы канала (если только этот символ не будет использоваться где-то в формуле в выражениях типа регулярных выражений) или тильду. Но это всего лишь вопрос личного стиля.
3. Я заметил, что не могу настроить диапазон после A:Z в первых двух параметрах. Есть ли что-то, чего мне не хватает, или это просто ограничения Листов? Например, у меня есть:
{ IF(Sheet1!B2:AZ<>TRUE,,Sheet1!B1:AZ1amp;"~|"amp;Sheet1!A2:Aamp;","); IF(Sheet2!B2:AZ<>TRUE,,Sheet2!B1:AZ1amp;"~|"amp;Sheet2!A2:Aamp;",") }И он ломается.4. Имейте в виду, что мы не можем видеть то, что видите вы. Вы показываете здесь только часть формулы. И мы не знаем, в чем ошибка (только «она ломается»). Так что я не могу сказать вам больше ничего, не увидев ни полной формулы, как вы ее адаптировали, ни самой таблицы (желательно и то, и другое).
5. Полностью поймите, когда я делал этот пост, я понятия не имел, что окажусь за пределами диапазона A:Z, пока не начал делать лист. Я еще не создал его. Предоставленный вами код работает идеально, пока я не расширю диапазон за пределы A:Z (все остальные диапазоны в порядке при изменении). Хотите ссылку на копию моего листа?