Excel GroupBy Массив фильтров по функциям
=GROUPBY(row_fields, values, function, [field Headers], [total_depth], [sort order], [**filter_array**], [field relationship])
Я хотел бы использовать FILTER ARRAY для поиска и группировки частичных текстовых совпадений, например, Венера найдет «Venus Printing Company» или «The Planet Venus». Кто-нибудь нашел обходной путь, чтобы сделать это?
=GROUPBY(B:B,C:C,SUM,0,0,,B:B = "Venus Stores Company") успешно извлекает и фильтрует «Компанию магазинов Venus», но мне бы также хотелось, чтобы =GROUPBY(B:B,C:C,SUM,0,0,,B:B = " wildcard Venus wildcard") или =GROUPBY(B:B,C:C,SUM,0,0,,B:B= "wilcard" & "Venus Stores Company" & "wildcard") работали, но ни один из них не работает.
Ответы 4
Не проверялось (на данный момент у меня нет доступа к Excel 365), но что-то вроде этого должно работать:
=GROUPBY(B:B, C:C, SUM, 0, 0, ISNUMBER(SEARCH("Venus", B:B)))
Просто ради интереса, похоже, вы используете бета-канал с синтаксисом GROUPBY() и эта-лямбда:
=GROUPBY(B:B,C:C,SUM,0,0,,REGEXTEST(B:B,"venus",1))
Совет: возможно, я бы использовал некоторые переменные LET(), чтобы найти границы фактических данных с помощью TOCOL(). Должно сэкономить много вычислительного времени.
Будет ли Regextext более эффективным, чем комбинация Is число/поиск?
Я согласен. Не уверены, насколько вы знакомы с регулярным выражением, введите «(венера|плутон)» для любого из них.
Вместо использования всего диапазона я преобразую свои данные в Structured References или Tables и использую функцию GROUPBY() следующим образом:
Синтаксис:
=GROUPBY(row_fields,
values,
function,
[field_headers],
[total_depth],
[sort_order],
[filter_array],
[field_relationship])
=GROUPBY(Company,Values,SUM,,0,,1-ISERR(SEARCH(" Venus "," "&Company&" ")))
Настоятельно рекомендуется не использовать следующую функцию, которая недоступна для текущего канала и доступна только для участников программы предварительной оценки Office. Пожалуйста, прочтите здесь: Выпуск
Поэтому альтернативный вариант:
=LET(
_Filter, FILTER(CompTable,1-ISERR(SEARCH(" Venus "," "&Company&" "))),
_Comp, TAKE(_Filter,,1),
_Uniq, UNIQUE(_Comp),
_Sum, MAP(_Uniq, LAMBDA(α, SUM((α=_Comp)*DROP(_Filter,,1)))),
HSTACK(_Uniq, _Sum))
Или,
=LET(
_Extract, UNIQUE(HSTACK(Company,SUMIF(Company,Company,Values))),
FILTER(_Extract,1-ISERR(SEARCH(" Venus "," "&TAKE(_Extract,,1)&" "))))
У меня возникла ошибка «ИМЯ» во всех трех вариантах?
@RWB, вы должны объяснить и правильно обосновать, почему все мои ответы возвращают #NAME ошибку, в то время как ваше переключение приема ответов между двумя конкретными пользователями не возвращает #NAME ошибку.
Не уверен, что у меня есть эти знания, спасибо, попробую.
Говорить о планетах интересно ;)
Расширив использование Михалом SEARCH, включив в него несколько терминов (почему бы и нет?), и добавив границы, как предложил JvdV,
=LET(
terms, TRIM(TEXTSPLIT($F$1, ",")),
group_by, B1:B10,
sum_range, C1:C10,
filter_arr, BYROW(
group_by,
LAMBDA(row, OR(ISNUMBER(SEARCH(terms, row))))
),
GROUPBY(group_by, sum_range, SUM, 0, 0, , filter_arr)
)
09.08.2024, Еще один вариант для развлечения
=LET(
terms, TEXTSPLIT(TRIM($F$1), ","),
group_by, B1:B10,
sum_range, C1:C10,
is_match, ISNUMBER(SEARCH(terms, group_by)),
matches, IF(is_match, group_by, ""),
sum_for_terms, MMULT(TRANSPOSE(--sum_range), --is_match),
VSTACK(terms, sum_for_terms, matches)
)
Для первого семестра работает хорошо! Однако второй член (Плутон в вашем примере) не суммируется, если существует несколько вторых членов. Это не влияет на мой вопрос, поскольку я спрашивал только один термин с возможными подстановочными знаками -R
Для первого семестра работает хорошо! Однако второй член (в вашем примере Плутон) не суммируется, если существует несколько вторых членов. Это не влияет на мой вопрос, поскольку я просил только один термин с возможными подстановочными знаками.
Моя ошибка выше. У моего второго члена (Плутон) в конце слова был пробел. Когда я очистил данные, общая сумма была объединена со вторым термином. Дааа, есть на что посмотреть
Обычно я тщательно отношусь к обрезке, но забыл добавить это сюда. Теперь я это исправил. Также добавил вариант для развлечения. Спасибо за тестирование и ваш отзыв!
не сработало. Ошибка #VALUE