#google-sheets #filter #google-sheets-formula
#google-sheets #Фильтр #google-sheets-формула
Вопрос:
У меня есть две вкладки: одна со всеми заголовками моих столбцов (вкладка «Данные»), другая вкладка с меньшим количеством заголовков, но все со вкладки «Данные».
Я хочу выполнить поиск в заголовках вкладки «данные», используя заголовки со второй вкладки, начиная с столбца I через BI. Заголовки второй вкладки будут периодически меняться, но всегда будут присутствовать на вкладке «Данные». Если критерии поиска отсутствуют, я хочу оставить этот столбец пустым. Если оно присутствует, верните приведенные ниже значения со вкладки «Данные».
Я попробовал несколько формул, но не могу получить то, что ищу.
Эта формула работала для поиска данных:
«ОТФИЛЬТРОВАНО»!I2
=FILTER(data!I2:AK,COUNTIFS($I$1:$1,data!I1:AK1))
Эта формула получила правильное размещение, но выдала неверную информацию:
«Копия ОТФИЛЬТРОВАННОГО»!I3
=ArrayFormula(IF(ISBLANK(I2:2),,FILTER(data!I2:AK,COUNTIFS($I$1:$1,data!I1:AK1))))
Вот мой лист.
Комментарии:
1. вы сказали в своем сообщении: «Если критерии поиска отсутствуют, я хочу оставить этот столбец пустым». Что вы собираетесь делать с / в этих пустых столбцах? Прежде чем я предложу решение, я хочу убедиться, что вы знаете, что если вы заполните сетку в целевом источнике с помощью формулы, вы НЕ СМОЖЕТЕ вручную ввести что-либо в эти ячейки на целевом листе. Например, прямо сейчас у вас нет ничего в таблице «данные» для заголовка «Сила»; поэтому, если одна формула будет заполнять столбец I в пункте назначения, вы не сможете заполнить «Силу» другой формулой или вручную.
2. Спасибо за предложение помощи, Эрик! Второй лист — это просто вспомогательный лист для поддержания размещения столбцов. Фактические изменения в данных происходят на вкладке «Данные». Иногда при загрузке данных загружается не каждый столбец. Следовательно, необходимо определить наличие заголовка столбца, чтобы заполнить соответствующий столбец на второй вкладке.
Ответ №1:
У вас есть большой диапазон для обработки, поэтому может возникнуть небольшая задержка с заполнением сетки по формуле (возможно, 2 или 3 секунды). Но я добавил лист («Справка Erik») со следующей формулой в I2:
=ArrayFormula(IF(ROW(A2:A),IFERROR(VLOOKUP(ROW(data!A2:A),{ROW(data!A2:A),INDIRECT("data!I2:"amp;ROWS(data!A:A))},HLOOKUP(FILTER(I1:1,I1:1<>""),{data!I1:1;SEQUENCE(1,COLUMNS(data!I1:1),2)},2,FALSE),FALSE))))
Честно говоря, трудно объяснить, как это работает, но я постараюсь охватить основы.
=ArrayFormula(...)
Это просто означает, что формула будет обрабатывать диапазон, а не одну ячейку.
IF(ROW(A2:A), ... HLOOKUP(FILTER(I1:1,I1:1<>""),{data!I1:1;SEQUENCE(1,COLUMNS(data!I1:1),2)},2,FALSE) ...)
IF(ROW(A2:A) это важно, потому что это сигнализирует о необходимости сделать что-то для каждой строки, тем самым создавая 2D-сетку вместо простой обработки текущей строки. Будет отображаться HLOOKUP каждый заголовок, I1:1 который не является пустым [ FILTER(I1:1,I1:1<>"") ] , который, как и лист сейчас, будет всем из них. Они будут просматриваться в виртуальном массиве, сформированном из верхней строки, состоящей из всех заголовков в data!I1:1 нижней строке, состоящей из ПОСЛЕДОВАТЕЛЬНОСТИ чисел, состоящей из 1 строки и того же количества столбцов, что и в data!I1:1 , начиная с номера 2 и двигаясь вверх. (Он начинается с 2, потому что часть виртуального массива VLOOKUP, который я еще не объяснил, будет формировать столбец 1.)
VLOOKUP(ROW(A2:A),{ROW(data!A2:A),INDIRECT("data!I2:"amp;ROWS(data!A:A))}, *the HLOOKUP RESULT NUMBER*,FALSE)
Теперь начнется VLOOKUP. Он будет искать каждую строку data!A2:A в виртуальном массиве, состоящем из двух столбцов; первый столбец будет содержать те же номера строк, а второй будет содержать все от data!I2 начала и до конца ( INDIRECT настройка позволяет использовать динамическую сетку на случай, если вы добавите или удалите столбцы позже). Что касается того, какой столбец из этого должен быть возвращен, это будет извлечено из результатов поиска, описанных выше (которые, как вы помните, будут соответствовать заголовкам).
Наконец, IFERROR(...) вернет null, если какой-либо шаг в этом процессе вернет ошибку, которая может быть вызвана тем, что что-то не было найдено.
Комментарии:
1. Большое тебе спасибо, Эрик! Отлично работает! Но вы правы, слишком много данных для быстрой обработки.
2. Привет, Эрик! Еще раз благодарю вас за эту формулу. Когда я использую полученные данные, я заметил, что они не соответствуют первоначальному порядку или, скорее, не связаны с соответствующими символами. Я пробовал вносить различные коррективы, но, боюсь, это выходит за рамки моих возможностей.
3. Я не понимаю, что вы имеете в виду (в отношении «полученных данных»), поскольку «они не соответствуют первоначальному порядку или не связаны с соответствующими символами». Если вы хотите быть более конкретным (например, точная ячейка / диапазон, что там написано сейчас и чего вы ожидали), я попытаюсь взглянуть еще раз. Но при первоначальной настройке он выдавал именно ваши результаты «Как это должно выглядеть», что заставляет меня задуматься, не пытались ли вы отредактировать мою формулу и просто не знаете, как это сделать правильно.
4. Я только что снова просмотрел лист, и мои результаты в точности совпадают с вашими результатами. Итак, опять же, я думаю, что вы скопировали и вставили мою формулу на другой лист и не знаете, как внести в нее изменения на другом листе.
Ответ №2:
смотрите:
=ARRAYFORMULA(QUERY(VLOOKUP(ROW(A2:A), {ROW(A2:A),
A2:C},
MATCH(HLOOKUP(E1:G1,
{E1, F1, G1;
B1, A1, C1},
2, 0), A1:C1, 0)
1,
0),
"select *"))
для отсутствующих заголовков:
Комментарии:
1. Привет, player0, спасибо, что посмотрел на эту формулу для меня. Я попробовал вашу формулу, но она, похоже, не сработала для меня (я уверен, что я что-то делаю неправильно). Я внес некоторые правки в лист, которые могли бы прояснить мою цель, если у вас будет время взглянуть еще раз.