Таблицы Google: найдите строку, соответствующую только нескольким конкретным характеристикам

#google-sheets #google-sheets-formula

Вопрос:

Кажется, я не могу найти правильное уравнение, чтобы найти ячейку из строки, которая соответствует только нескольким конкретным характеристикам. В этом примере я пытаюсь найти уравнение для столбца D, которое будет ячейкой в A, содержащей те же ячейки для B amp; C.

Надеюсь, это имеет смысл!

1. Здесь все еще есть неизвестные, Бретт. Что должно произойти, если в столбце D возможно более одного совпадения (например, если у «Боба Смита» также было «Apple» в столбце C)? Или это никогда не будет возможно? И должен ли столбец D указывать «Нет», если совпадения нет, или значение null возвращается нормально? Это также помогло бы сдвинуть дело с мертвой точки, если бы вы могли предоставить ссылку на лист с реалистичным набором образцов данных, которые уже есть в нем

2. Хорошие замечания, @ErikTyler. С моими данными никогда не должно быть больше, чем результат. Столбец D может указывать на отсутствие совпадений.

Ответ №1:

Я приведу два варианта.

Если вы уверены, что ваши данные будут иметь только ноль или одно совпадение, вы можете поместить следующую формулу в D2 пустого диапазона D2:D…

=ArrayFormula(IF(A2:A="",,SUBSTITUTE(VLOOKUP(B2:Bamp;C2:C,{B2:Bamp;C2:C,A2:A},2,FALSE)amp;VLOOKUP(B2:Bamp;C2:C,SORT({B2:Bamp;C2:C,A2:A,ROW(A2:A)},3,0),2,FALSE),A2:A,"")))

Однако, если вы считаете, что может появиться более одного совпадения, и вы хотите, чтобы в случае отсутствия совпадения возвращалось «None», вы можете использовать следующую формулу в D2 или в противном случае пустой диапазон D2: D…

=ArrayFormula(IF(A2:A="",,REGEXREPLACE(REGEXEXTRACT(REGEXREPLACE(SUBSTITUTE(VLOOKUP(B2:Bamp;C2:C,TRIM(SPLIT(FLATTEN(QUERY(QUERY({B2:Bamp;C2:Camp;"~",A2:Aamp;","}, "Select MAX(Col2) where Col2 IS NOT NULL GROUP BY Col2 PIVOT Col1"),, 9^9)),"~")),2,FALSE),A2:A,""),"^[,s] $","None"),"([^,s]. [^,s])[,s]*$"),"[,s] ",", ")))

Вторая формула будет работать, даже если совпадений будет всего ноль или одно; просто необязательно, чтобы она была такой длинной. И вторая формула такая же длинная, потому что из ваших опубликованных примеров было неясно, будут ли данные в столбцах A, B и C действительно состоять только из одного слова или нет; поэтому формула построена так, чтобы предполагать, что в этих столбцах не всегда будут строки из одного слова.

Любая формула предоставит результаты для всего столбца без перетаскивания.

Ответ №2:

Вот вариант, вы можете использовать эту формулу в столбце D2:

 =iferror(textjoin(", ",true,query($A$2:$C,"Select A where A is not null and A != '"amp;$A2amp;"' and B = '"amp;$B2amp;"' and C = '"amp;$C2amp;"'",0)),"None")

Ограничение:

Вам нужно вручную перетащить формулу в последующие строки. Arrayformula() не может использоваться для циклического преобразования значений строки запроса.

Что он делает?

Используя query(), отфильтруйте данные из A2: C, которые имеют ту же фамилию текущей строки (столбец B) и food (столбец C), но в то же время имеют другое имя (столбец A)
Если результатов несколько, используйте textjoin() для их объединения в ", " качестве разделителя.
Если совпадение не найдено, будет возвращена ошибка, поэтому используйте iferror(), чтобы установить значение по умолчанию на «None»