Формула Excel для группировки строк для создания групп с разницей в 2 между значениями в одном столбце
У меня есть набор данных, который выглядит так:
Массив имеет переменную длину, но фиксированную ширину.
Я ищу формулу, которая может создавать группы (по 3 строки в группе, но если бы это можно было настраивать, это было бы идеально), чтобы каждая группа не имела значений в столбце D в пределах 2 друг от друга.
Итак, для примера выше возвращаемые две группы могут быть
и
Если существует несколько способов создания группы, не имеет значения, какой способ выбран, если данные не дублируются.
Насколько велик ваш набор данных? Надеетесь ли вы, что сможете сгруппировать все это в наборы по три штуки?
Группы должны находиться на одном листе, одна под другой, либо с пустой строкой между каждой, либо без и со столбцом, содержащим номер группы.
Набор данных имеет 51 строку (но, если возможно, я бы хотел использовать формулу с наборами данных в будущем, в которых может быть больше или меньше строк), и я хотел бы сгруппировать все это в наборы по 3 «да».
Вы готовы использовать VBA? Для этого достаточно просто написать небольшой код.
Как эту проблему очень легко решить с помощью VBA. Мне действительно любопытно, поэтому, пожалуйста, просто напишите ответ @FrankBall.
Ответы 3
Забавный вопрос, но сложная проблема, поскольку в D может быть достаточное количество значений, находящихся на расстоянии <= 2 друг от друга. Мы могли бы немного перебрать это, используя рекурсию:
Формула в F2:
=LET(_x,LAMBDA(_f,_r,_n,_l,LET(_s,ROUNDDOWN((SEQUENCE(ROWS(_r))-1)/_n+1,0),IF(OR(MAP(DROP(_r,,3),_s,LAMBDA(_v,_w,SUM((ABS(DROP(_r,,3)-_v)<=_l)*(_s=_w))))>1),_f(_f,SORTBY(_r,RANDARRAY(ROWS(_r))),_n,_l),HSTACK(_s,_r)))),_x(_x,A2:D7,2,2))
Итак, вы получаете здесь рекурсию до тех пор, пока ваш исходный массив не будет отсортирован таким образом, что на абсолютном расстоянии 2 не будет значений (я тоже сделал это динамически). По сути, вы вызываете эту функцию с помощью этой части:
_x(_x,A2:D7,3,2)
Третий параметр указывает размер вашей группы, а последний — ограничение, которое вы хотите ей задать.
Вот результат для: _x(_x,A2:D7,2,2)
Вот еще один результат для: _x(_x,A2:D7,2,10)
Очень хорошо. Насколько я вижу, ни одно решение методом грубой силы не масштабируется до 51 строки, и я не вижу каких-либо очевидных коротких путей, если только вы, возможно, не знали что-то о распределении данных (и не были математиком!), так что, как вы говорите, это трудная проблема, которую можно решить в целом.
Спасибо @TomSharpe, нет, я просто подумал, что мы просто пытаемся перемешать данные, пока распределение не будет в порядке :)
Возможно, это глупый вопрос для новичков, но что это за синтаксис LAMBDA(Function, Parameters)? как это написать без каких-либо инструментов отладки?
Вполне возможно, что это результат того, что я слишком большой ботаник. По сути, это обычная LAMBDA() функция с перегрузкой, где мы продолжаем вызывать функцию в рекурсии до тех пор, пока определение в IF() не будет удовлетворено, то есть рекурсивная лямбда. Честно говоря, я никогда не использовал инструменты отладки. Что вы можете порекомендовать? @rachel
Легенда! Я использую github.com/microsoft/Excel-Labs/blob/main/… для IntelliSense и упрощения проверки.
Это решение сработало для меня лучше всего, спасибо! Для моего набора данных из 51 строки расчет был почти мгновенным. Я попробовал это на наборе из примерно 4000 рядов просто для развлечения, и это было гораздо менее мгновенно.
@AdamW, пожалуйста. И да, ~4000 строк означает гораздо больше шансов на неверную группировку, что приведет к повторному вращению :)
Я думаю, вы можете сначала отсортировать данные.
Затем выберите наименьшее число, например 189.21, затем получите следующее число, которое больше или равно 189.21 + 2 в вашем наборе данных, затем получите следующее число, которое больше или равно next_number+2. Повторяйте процесс, пока не достигнете своего group_size.
Для этого вы можете написать рекурсивную лямбду под названием GetGroupData().
Как только вы получите все числа, которые можно поместить в одну группу с 189.21, вы удалите эти числа из своего набора данных. Позвоните GetGroupData() в оставшемся наборе данных, удалите числа для второй группы... повторяйте процесс до тех пор, пока в вашем наборе данных не останется ни одного числа.
Для этого вы можете написать еще одну рекурсивную лямбду под названием SplitData.
Вот как я определил GetGroupData():
=IF(
ROWS(row_indices) >= group_size,
row_indices,
LET(
current_index, @TAKE(row_indices, -1),
current_num, INDEX(sorted_data, current_index, 4),
next_index, XMATCH(current_num + diff, INDEX(sorted_data, , 4), 1, 1),
IF(
ISNA(next_index),
row_indices,
GetGroupData(sorted_data, VSTACK(row_indices, next_index), group_size, diff)
)
)
)
Ниже SplitData()
=LET(
current_row_indices, TAKE(row_indices, , -1),
IF(
ROWS(current_row_indices) >= ROWS(sorted_data),
groups,
LET(
remaining, ISNA(XMATCH(SEQUENCE(ROWS(sorted_data)), current_row_indices)),
remaining_sorted_data, FILTER(sorted_data, remaining, ""),
next_row_indices, GetGroupData(remaining_sorted_data, {1}, group_size, diff),
next_group, CHOOSEROWS(remaining_sorted_data, next_row_indices),
SplitData(
remaining_sorted_data,
HSTACK(row_indices, next_row_indices),
VSTACK(groups, EXPAND("", 1, 4, ""), next_group),
group_size,
diff
)
)
)
)
Результат:
=LET(
group_size, 3,
diff, 2,
row_indices, GetGroupData(SortedData, {1}, group_size, diff),
groups, CHOOSEROWS(SortedData, row_indices),
SplitData(SortedData, row_indices, groups, group_size, diff)
)
Честно говоря, я не уверен в вашей логике. Кажется, это работает случайно с данным набором данных. Давайте упростим список значений и применим это: [1,3,5,6,8,9]. Теперь вы бы сгруппировали [1,3,5], но это сделало бы [6,8,9] недействительным. Это касается только набора из 6 чисел, но представьте себе, какие проблемы вам придется пережить с 51. Проще говоря, вам всегда придется убедиться, что все оставшиеся числа действительно имеют возможность создать действительный набор, прежде чем вы начинаете делать эти наборы. В любом случае, думаю, вам захочется услышать положительную критику. +1 от меня в любом случае.
@JvdV, для [1,3,5,6,8,9] я намерен сгруппировать его в [1,3,5]; [6,8]; [9]. Но если нам нужно разделить их на группы одинакового размера, мое решение не сработает.
Я так прочитал вопрос. Не имеет значения, какие строки группируются, если ни одно из значений не находится на абсолютном расстоянии <2. В этом небольшом примере мы могли бы создать, скажем, [1,5,8] и [3,6,9].
Я не мог не привлечь к этому моего друга-математика :)
Если количество значений в наборе данных делится на размер группы и существует хотя бы одно решение с требованием расстояния, следующее решение даст решение:
- Отсортируйте значения в порядке возрастания
- Выберите каждый элемент в строке, который будет чередоваться с элементами из следующей группы, поскольку они будут находиться как минимум на требуемом расстоянии друг от друга.
Вот как будут выглядеть индексы для элементов 51:
Итак, применяя это:
=LET(
data, SORT(A1:D6, 4),
num_rows, ROWS(data),
num_cols, 3,
indices_for_result, TOCOL(WRAPCOLS(SEQUENCE(num_rows), num_rows / num_cols)),
result, DROP(
REDUCE(
"",
SEQUENCE(num_rows),
LAMBDA(acc, cur,
VSTACK(
acc,
IF(
MOD(cur, num_cols) = 0,
EXPAND(CHOOSEROWS(data, INDEX(indices_for_result, cur)), 2, , ""),
CHOOSEROWS(data, INDEX(indices_for_result, cur))
)
)
)
),
1
),
result
)
Выглядит очень интересно, сегодня попробую и протестирую.
Спасибо @JvdV, я очень ценю это!
Провел некоторое тестирование, но, к сожалению, я не думаю, что он делает то, что, по вашему мнению, он должен делать и здесь. Я считаю, что это та же проблема, что и с другими концепциями: вы не можете создать набор, если не уверены на 100%, что сможете создать действительный набор с остатком. Например. Измените эти цифры в 4-м столбце на [1,2,2,6,8,9] или поставьте после них несколько небольших десятичных чисел. Группировка неправильная. К сожалению, я не математик, поэтому не могу указать, где концептуальный подход идет не так.
Спасибо @JvdV, этот метод работает, только если есть решение; иначе не будет. Есть ли решение с этим набором?
аххх вот так! Нет, здесь не должно быть решения. Хотя это имеет смысл. Я думаю, это очень умно :). Единственное, что я все еще замечаю, это то, что в наборе, где у вас, скажем, 5 рядов, это не сработает. Я предполагаю, что ваше решение основано на n-кратном размере группировки? Это означает, что если каждая группа должна быть равна 3, ваши данные должны быть кратны 3?
Да, именно так я интерпретирую вопрос. Я попытался улучшить объяснение. Пожалуйста, дайте мне знать, если его нужно уточнить. Еще раз спасибо!
Эти две группы находятся на разных листах, на одном листе, но рядом, или одна под другой с пустой строкой??? У каждого из них будет другое решение.