Как сравнить две строки переменных данных с помощью формулы
Моя таблица выглядит следующим образом (хотя это образец, фактическая таблица — 12Rx20C):
На листе также есть список всех производителей (список MARKERLIST):
Столбец «Порядок» и «Маркеры» заполняются по формуле.
Я пытаюсь придумать формулу, которая заполнит столбец «Результат». Должны применяться следующие правила:
- Добавление маркера не является проблемой, поэтому NEG (то есть от 50К до 601 добавляется маркер B);
- Когда маркер заменяется или сбрасывается, это POS (с 601 по 97Y, маркер A заменяется на маркер C/от K3S до K12, маркер F сбрасывается);
- Переход от одного набора маркеров к другому — это POS (от 95Y до M22 — другой набор);
- Изменение порядка, но сохранение маркеров — это NEG (от M22 до K3S);
До сих пор я пробовал COUNTIF: =TRANSPOSE(COUNTIFS(B2:B4 , MARKERLIST) для всех строк.
Затем примените функцию MAP между двумя строками, чтобы найти различия по принципу =MAP(COUNTIFS row 1, COUNTIFS row2, LAMBDA(a, b, IF(a>=b, 0, 1); который затем суммируется.
Но этот метод дает много ложных результатов, особенно когда маркер (набор) появляется несколько раз подряд.
Я попытался добавить функцию СКАНИРОВАНИЯ в список заказов, чтобы обнаружить там изменения (один заказ всегда имеет один и тот же набор маркеров), но это все равно не исправляет ситуацию полностью. =SCAN(0, Ordercolumn, LAMBDA(a, b, IF(b = OFFSET(b, 1, 0), 1, "")
Предпочтительно это было бы сделано в одном столбце, но допускается использование вспомогательных столбцов, хотя я бы хотел свести их к минимуму.
Я надеюсь, что это имеет смысл, если что-то неясно, дайте мне знать, и я постараюсь отредактировать и дать лучшее объяснение.
Ответы 2
Я думаю, вы ищете что-то подобное?
Формула в F3 приведена ниже и может быть заполнена:
=IF(AND((ISNUMBER(XMATCH(B2:D2,B3:D3,0,1))+(B2:D2=0))>0),"NEG","POS")
ISNUMBER(XMATCH(B2:D2,B3:D3,0,1))+(B2:D2=0) проверяет, находится ли каждый маркер в строке 2 либо в строке 3, либо равен 0.
Если все маркеры в строке 2 доступны в строке 3 или имеют значение только 0, то строка 3 является NEG, в противном случае POS.
Просто сопоставление, я имею в виду отображение хорошего ответа Рэйчел в виде динамической формулы (без необходимости заполнения); если в заказе есть одинаковые маркеры, они будут в одном и том же положении. Если это не так, дайте мне знать.
Обновлено 2 августа 2024 г.
=LET(markers, B2:D15, orders, A2:A15, MAP(SEQUENCE(ROWS(markers)), LAMBDA(i, IFS(
AND(ISBLANK(INDEX(orders, i)), i > 1, OR(INDEX(markers, i - 1,0) <> 0)), "POS",
ISBLANK(INDEX(orders, i)), "",
i = 1, "NEG",
AND((INDEX(markers, i - 1, 0) = 0)
+ (INDEX(markers, i, 0) = INDEX(markers, i - 1, 0))), "NEG",
TRUE, "POS"))))
Отметьте POS после последнего заказа маркерами.
С пустым заказом в качестве последнего заказа
Аккуратный! Спасибо за Ваш ответ. Одна вещь (о которой, кажется, я не упомянул): после того, как будет выполнен последний заказ с маркером, также должен быть «POS». Хотя в вашем примере это работает, поскольку есть пустой заказ (K12), похоже, этого не происходит, когда последний заказ в списке имеет маркеры. Например, если бы K3 были последним заказом, следующая строка результата должна быть «POS» (а остальные «NEG» или пустые). Маркеры всегда выбираются из списка вниз, и каждый заказ всегда содержит одни и те же маркеры, поэтому их порядок всегда один и тот же.
@Excellor, спасибо за тестирование! Я обновил формулу — пожалуйста, проверьте ее, когда у вас будет время. Надеюсь, я правильно понял требование.
работает как шарм! Спасибо за ваше обновление, я протестирую оба варианта и посмотрю, что лучше всего работает в реальном файле.
только что узнал еще одну вещь, список может начинаться с «пустых» заказов, но если после этого есть заказ с маркером, он также пометит его как «POS». Хотя я это исправил, подкорректировав формулу, принимающую маркеры из списка. (Это функция FILTER, часть «не найдено» была заполнена "", изменена на 0, теперь она работает нормально.)
Рад это слышать - спасибо за ваш отзыв.
Насколько я могу это проверить, кажется, работает отлично!