Список значений Excel, где данные совпадают в нескольких строках
У меня есть данные (более 40000 строк) в формате (включая пустые строки)
Я хочу получить список «всех» моделей, где Тип модели = Интерес и соответствующая Мардж (иногда после 2 строк, а иногда после 3 строк) = Да.
Итак, в этом примере он должен вернуть A, D
Перепробовал множество комбинаций фильтров, XMatch и т. д. Преобразование данных (строка в столбец не является допустимым вариантом из-за размера данных и переменного количества строк в каждом разделе). Любые предложения.
пример реальных данных:
@MayukhBhattacharya добавил сейчас, раньше у него были данные, которыми я не хотел делиться..поэтому удалил их
Ответы 3
То, как вы написали этот вопрос, заставляет меня думать, что вы имели в виду, что блоки из 4 строк могут иметь перепутанные атрибуты. Я попытался представить это в следующем примере:
Формула в E2:
=TOCOL(LET(a,A2:A25,b,B2:B25,c,C2:C25,MAP(UNIQUE(a),LAMBDA(x,IFS(COUNTIFS(a,x,b,"ModelType",c,"Interest")COUNTIFS(a,x,b,"Marge",c,"Yes"),FILTER(c,(a=x)(b = "Model")))))),3)
Ух ты! Я пытаюсь осмыслить формулу (похоже, мне понадобится больше времени, чтобы понять ее, чем время, которое вы потратили на ее написание). К сожалению, они не объединены в группы по 4 человека. Максимальный размер блока, который я вижу в данных, составляет 37 строк.
@Ани, исправлено!
@JvdV обновленная формула, похоже, не работает
Вы также можете попробовать использовать следующее, и это должно работать, даже если Marge иногда находится после 2 или 3 строк:
Вариант первый:
=LET(
_Data, WRAPCOLS(FILTER(C2:C26,1-ISNA(XMATCH(B2:B26,{"Model","ModelType","Marge"}))),3),
FILTER(TAKE(_Data,1),(INDEX(_Data,2) = "Interest")*(INDEX(_Data,3) = "Yes"),""))
Или вариант второй:
=LET(
_Data, WRAPROWS(FILTER(C2:C26,1-ISNA(XMATCH(B2:B26,{"Model","ModelType","Marge"}))),3),
FILTER(TAKE(_Data,,1), (INDEX(_Data,,2) = "Interest")*(INDEX(_Data,,3) = "Yes"),""))
Пошаговый метод: --> Попробуйте изменить последнюю переменную на другие переменные, чтобы понять, как работают приведенные выше формулы. Логика та же. Сначала найдите позиции каждого атрибута, после обнаружения проверьте, возвращает ли он число или нет, если да, извлеките их с помощью функции FILTER(), затем используйте либо WRAPCOLS(), либо WRAPROWS(), наконец, используя FILTER(), чтобы получить результат на основе определенных столбцов. критерии. Просто понять и легко отладить.
=LET(
_Position, XMATCH(B2:B26,{"Model","ModelType","Marge"}),
_Include, 1-ISNA(_Position),
_Extract, FILTER(C2:C26,_Include,""),
_Wrap, WRAPROWS(_Extract,3),
_Output, FILTER(TAKE(_Wrap,,1),(INDEX(_Wrap,,2) = "Interest")*(INDEX(_Wrap,,3) = "Yes"),""),
_Output)
Обновленное решение на основе нового обновленного скриншота OP:
=LET(
_Extract, WRAPROWS(FILTER(F2:F28,(1-ISNA(XMATCH(E2:E28,{"Model","ModelType","Marge"})))*(B2:B28 = "Nil")*(MAP(D2:D28,E2:E28,LAMBDA(a,b, COUNTIFS(a:D2,a,b:E2,b))))=1),3),
FILTER(TAKE(_Extract,,1),(INDEX(_Extract,,2) = "Rating Base")*(INDEX(_Extract,,3) = "Row/Column"),""))
ух ты! это так полезно... Я пытаюсь имитировать это на своем реальном примере с помощью вашего примера... мой упрощенный вариант кажется слишком простым
Я очень старался понять эту часть формулы =MAP(D2:D28,E2:E28,LAMBDA(a,b, COUNTIFS(a:D2,a,b:E2,b))) (часть счетчиков с a: Часть D2), и я не могу понять это..вручную это переводится как =COUNTIFS(D2:D28:D2,D2:D28,E2:E28:E2,E2:E28) ..что это за первая часть D2 :D28:D2
@Ani, это переводится как: =COUNTIFS(D$2:D2,D2,E$2:E2,E2) текущие итоги. Попробуйте поместить это в другую ячейку: =MAP(D2:D28,E2:E28,LAMBDA(a,b, COUNTIFS(a:D2,a,b:E2,b)))
Спасибо, все ясно. Большое спасибо за ваше терпение. Для меня это совершенно новый способ добиться успеха. Позвольте мне посмотреть, смогу ли я расширить это, чтобы также возвращать идентификатор сегмента.
Последующий вопрос. Я расширил формулу, включив в нее столбец D как LET( _Extract, WRAPROWS(LET(c, CHOOSECOLS(FILTER(D2:F28,(1-ISNA(XMATCH(E2:E28,{"Model","ModelType", "Мардж"})))*(B2:B28 = "Ноль")*(MAP(D2:D28, E2:E28,LAMBDA(a,b,COUNTIFS(a:D2,a,b:E2) ,b))))=1),1,3), TAKE(c,,1)&"."&TAKE(c,,-1)),3), FILTER(TAKE(_Extract,,1), ( ISNUMBER(SEARCH("База рейтинга", INDEX(_Extract,,2))) )*( ISNUMBER(SEARCH("Строка/столбец", INDEX(_Extract,,3))) ),"") ). оптимальный способ? Сделали бы вы это по-другому?
@Ани поняла, ты можешь попробовать использовать одно из следующих: 1. =LET( _SegmentID, D2:D28, _Property, E2:E28, _Values, F2:F28, _RollingCounts, MAP(_SegmentID,_Property,LAMBDA(r,c,COUNTIFS(D2:r,r,E2:c,c))), _Extract, WRAPROWS(FILTER(_SegmentID&"."&_Values,(B2:B28 = "Nil")*(1-ISNA(XMATCH(_Property,{"Model","ModelType","Marge"})))*_RollingCounts,""),3), FILTER(TAKE(_Extract,,1),BYROW(1-ISERR(FIND({"Rating Base","Row/Column"},TAKE(_Extract,,-2))),AND)))
@Ани или 2. =LET( _SegmentID, D2:D28, _Property, E2:E28, _Values, F2:F28, _RollingCounts, MAP(_SegmentID,_Property,LAMBDA(r,c,COUNTIFS(D2:r,r,E2:c,c))), _Extract, WRAPROWS(FILTER(_SegmentID&"."&_Values,(B2:B28 = "Nil")*(1-ISNA(XMATCH(_Property,{"Model","ModelType","Marge"})))*_RollingCounts,""),3), FILTER(TAKE(_Extract,,1),MMULT(1-ISERR(FIND({"Rating Base","Row/Column"},TAKE(_Extract,,-2))),{1;1})>1))
Спасибо. Мне нравится это новое решение... его легко читать и понимать. Очень ценю вашу помощь
@Ani, просто для информации, первый использует BYROW() --> без конструкции LAMBDA(), я не уверен, есть ли у вас доступ к программе предварительной оценки Office или нет, если нет, то добавьте конструкцию LAMBDA(), я уверен, что вы сможете это сделать это.
вот почему я получал ошибки... я изменил умножение массива, например... ( ISNUMBER(SEARCH("Rating Base", INDEX(_Extract,,2))) )*( ISNUMBER(SEARCH("Row/Column", INDEX (_Extract,,3))) )
@Ани, вот что тебе нужно сделать: =LET( _SegmentID, D2:D28, _Property, E2:E28, _Values, F2:F28, _RollingCounts, MAP(_SegmentID,_Property,LAMBDA(r,c,COUNTIFS(D2:r,r,E2:c,c))), _Extract, WRAPROWS(FILTER(_SegmentID&"."&_Values,(B2:B28 = "Nil")*(1-ISNA(XMATCH(_Property,{"Model","ModelType","Marge"})))*_RollingCounts,""),3), FILTER(TAKE(_Extract,,1),BYROW(1-ISERR(FIND({"Rating Base","Row/Column"},TAKE(_Extract,,-2))),LAMBDA(ε,AND(ε)))))
Используя идентификаторы в качестве результатов поиска совпадений, попробуйте эту функцию с переменными,header, data, id_col_name, match_attr_name, match_val_name, match_attr_1, match_val_1, match_attr_2, match_val_2, return_attr_name
для первоначального образца:(A1:C1, A2:C100, "id", "attribute", "value", "ModelType", "Interest", "Marge", "Yes", "Model")
со значениями для последнего набора данных:(A1:E1, A2:E100, C1, D1, E1, M6, N6, M7, N7, "Model")
=LAMBDA(
header,
data,
id_col_name,
match_attr_name,
match_val_name,
match_attr_1,
match_val_1,
match_attr_2,
match_val_2,
return_attr_name,
LET(
comment_1, "Remove blanks",
data, FILTER(data, INDEX(data, 0, XMATCH(id_col_name, header)) <> ""),
comment_2, "Get range(column_header(name)), range defaults to data",
data_col, LAMBDA(name, [d],
LET(d, IF(ISOMITTED(d), data, d), INDEX(d, 0, XMATCH(name, header)))
),
id, LAMBDA([d], IF(ISOMITTED(d), data_col(id_col_name), data_col(id_col_name, d))),
attr, data_col(match_attr_name),
val, data_col(match_val_name),
match_1_ids, FILTER(id(), (attr = match_attr_1) * (val = match_val_1), "No matches"),
match_2_ids, FILTER(id(), (attr = match_attr_2) * (val = match_val_2), "No matches"),
FILTER(
val,
(attr = return_attr_name) *
ISNUMBER(
XMATCH(
id(),
FILTER(
match_1_ids,
ISNUMBER(XMATCH(match_1_ids, match_2_ids)),
"No matches"
)
)
),
"No matches"
)
)
)(A1:E1, A2:E100, C1, D1, E1, M6, N6, M7, N7, "Model")
спасибо за ваши усилия по этому решению и объяснению. Наконец-то я использую решение Маюха, потому что уже провел его отладку и понимание.
Кстати, согласно вашему своду изменений,
additional screenshotнет!