☰

tag-icon • tag-icon microsoft-excel worksheet-function

Я имею дело с файлом Excel, похожим на этот (изменен/упрощен в целях конфиденциальности):

введите описание изображения здесь

Заказ # Задача Машину мыли в
рамках этого заказа?                                                                      
1 Обзор Да
1 Поменять шины Да
1 Мыть машину Да
2 Обзор Нет
2 Поменять шины Нет
2 Поменять масло Нет

ДругойЗаданиямогут поделиться тем жеЗаказ #.

Мне нужна формула для заполнения третьего столбца (желтого на изображении) значением «Да», еслиЗадачаявляется частьюЗаказ #что включает в себяЗадача«Помойте машину» или ответьте «Нет», если это не так.

Я безуспешно искал решение в Интернете.

решение1

Ниже приведена последовательность из 5 формул. Каждая формула основана на предыдущей. Надеюсь, что такой прогресс сделает более легкими для понимания последующие.

[ Формула 1 из 5 ]   Основополагающая формула

=IFERROR(IF(
   MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
   "yes"),"no")
  1. ЕСЛИфункция используетСООТВЕТСТВОВАТЬкак его logical_test.
    • СООТВЕТСТВОВАТЬиспользует каждый заказ по очереди какsearch_key
    • Аргумент rangeзаполняется с помощьюФИЛЬТРфункция возврата списка заказов, где задача «Помыть машину»
  2. TheЕСЛИvalue_if_trueАргумент функции —"Yes"
  3. value_if_falseопущено, так как logical_testиспользуемый может возвращать только TRUEили#N/A
  4. ЕСЛИОШИБКАзаменяет ошибки на"No"

[ Формула 1 из 5 ] Основополагающая формула

[ Формула 2 из 5 ]   Игнорировать пустые строки

=IF(A2:A7<>0, IFERROR(IF(
   MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
   "Yes"),"No"),"")
  1. Чтобы игнорировать пустые строки,ЕСЛИФункция добавляется с value<>0. logical_testОна вернет:
    • TRUEдля любой ячейки, содержащей ненулевое число, а также любую текстовую строку, даже содержащую цифры. например.("0"<>0)=TRUE
    • FALSEдля числа 0, а также пустых ячеек, которые Excel приводит к нулю. например, если OR(ISBLANK(A1),A1=0)то(A1<>0)=FALSE
  2. TheОсновная формулаиспользуется какvalue_if_true
  3. value_if_falseявляется пустой строкой "".
    Этот подход пропускает возврат "No", предотвращая смещение строк из-за пробелов в данных.

[ Формула 2 из 5 ] Игнорировать пустые строки

[ Формула 3 из 5 ]   Формула в строке заголовка

Перемещение формулы в строку заголовка может предотвратить ее перезапись.

=VSTACK("Wash on Order?",
  IF(A2:A7<>0, IFERROR(IF(
     MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
     "Yes"),"No"),""))
  1. ВСТЕКпозволяет складывать массивы вертикально
  2. Первый использованный массив — заголовок"Wash on Order?"
  3. Второй массив — это предыдущая формула.

[ Формула 3 из 5 ] Формула в строке заголовка

[ Формула 4 из 5 ]   Добавлена ​​функция LET

ПокаПОЗВОЛЯТЬсама по себе не добавляет много к предыдущей формуле, она применяется на этом этапе для пояснения преобразования. По мере усложнения формул полезностьПОЗВОЛЯТЬстановятся более ясными.

=LET(rng,A2:B7,
   a,DROP(rng,,-1), b,DROP(rng,,1),
   VSTACK("Wash on Order?",
     IF(a<>0, IFERROR(IF(
       MATCH(a, FILTER(a, b="Wash car"), 0),
       "Yes"), "No"), "")))
  1. TheПОЗВОЛЯТЬФункция позволяет сохранять значения и формулы в переменных для повторного использования. Это может уменьшить повторение кода и длину формулы, а также может сделать сложные формулы более простыми для понимания и управления.
  2. Исходный диапазон сохраняется в rng.
  3. Столбец A aи столбец B bвозвращаются с помощьюУРОНИТЬдля отправки ненужной колонки.
  4. aи bтеперь используются вместо A2:A7и B2:B7в формуле.

[ Формула 4 из 5 ] Добавлена ​​функция LET

[ Формула 5 из 5 ]   Автоматически определить размер диапазона

Позволяет данным расти без необходимости изменять формулу. Принимает полные столбцы, а затем изменяет размер массива по мере необходимости на основе заполненных данных. ЗначениеПОЗВОЛЯТЬполностью выставлен напоказ.

=LET(rng,A:B,  
  arr,DROP(FILTER(rng, ROW(rng)<=
    MAX((rng<>0)*(ROW(rng)))),1),
  a,DROP(arr,,-1), b,DROP(arr,,1),
  VSTACK("Wash on Order?",
    IF(a<>0, IFERROR(IF(MATCH(a,
      FILTER(a, b="Wash car"), 0),
      "yes"),"no"),"")))
  1. ФИЛЬТРвозвращает массив arrстрок, номер строки которых совпадает <=с номером последней строки, содержащей идентификатор заказа.
  2. Последняя строка рассчитывается путем примененияМАКСв массив номеров строк, строки которых также содержат ненулевое значение. Звездочка *— это оператор «и»:MAX(ROW(rng)*rng<>0)
  3. Этот результирующий массив включает заголовок (строка 1), заказы, а также любые пробелы. Пробелы включены для того, чтобы гарантировать, что намеренные или случайные пробелы не приведут к смещению результатов относительно исходного диапазона.
  4. TheУРОНИТЬФункция используется для удаления строки заголовка. В качестве альтернативы, это можно сделать, добавивФИЛЬТРсостояниеROW(rng)<>1

[ Формула 5 из 5 ] Автоматически определить размер диапазона

решение2

Следующее решение, по-видимому, соответствует целям, изложенным в вашем образце данных.

  1. В пустом столбце Cобъедините Aи Bстолбцы с =concat(A2,B2)(в ячейке C2) для каждой строки.

  2. В пустой столбец Dдобавьте следующую формулу, начиная с D2. Это формула массива, поэтому вам нужно будет нажать CTRL+SHIFT+ENTERодновременно, чтобы она заработала.

    =IF(OR($A$1:$A$6=A1)*(OR($C$1:$C$6=CONCAT(A1,"Wash car"))),"Yes","No")

  3. Скопируйте D2в оставшиеся строки.

Если я что-то упускаю из виду (а мне кажется, что я это делаю, поскольку мой набор данных для выборки очень ограничен), пожалуйста, дайте мне знать, и я постараюсь это исправить. Если это решение решит вашу проблему, пожалуйста, отметьте его как ответ.

Связанный контент