Используя формулы массива Excel Office365, как удалить дубликаты, сохранив последнее значение?
С данными в A1 – B5:
A 1 //remove
A 2
B 3 //remove
B 2
C 1
Как удалить дубликаты в столбце A, сохранив последний набор значений в других столбцах? Результаты должны выглядеть следующим образом:
A 2
B 2
C 1
Я пробовал комбинации Filter, Unique и xlookup, но пока не нашел работающего подхода.
Ответы 5
Вероятно, есть много вариантов. Вот один из них, использующий XMATCH для поиска от последнего к первому.
=CHOOSEROWS(A1:B5,UNIQUE(XMATCH(A1:A5,A1:A5,0,-1)))
Обновлено:
Аналогичный вариант: сначала посчитайте предметы UNIQUE (спасибо @ScottCraner):
=CHOOSEROWS(A1:B5,XMATCH(UNIQUE(A1:A5),A1:A5,0,-1))
Это работает, но обработка 50 000 строк занимает много времени. Есть ли более эффективный способ?
Возможно. Я подозреваю, что эта тема превратится в нечто вроде Code Golf.
=CHOOSEROWS(A1:B5,TAKE(GROUPBY(A1:A5,ROW(B1:B5),MAX,0,0),,-1)) НЕ уверен, быстрее или нет.
Все еще очень медленно, когда дубликатов всего несколько (менее 1%)... но это работает. Было бы здорово, если бы он мог работать и с 500 000 строк.
Я сомневаюсь, что какая-либо формула будет работать быстро с таким количеством строк с таким количеством дубликатов. Возможно, лучше использовать VBA и словарь. Но даже в этом случае при использовании массивов все равно будет выполняться цикл. @ciso
Динамические массивы работают очень быстро со многими сложными структурами и большим количеством строк. Кажется, это простая вещь, но я еще не разобрался в этом.
Но вы говорите сначала 50 тысяч строк, а в другом комментарии говорите 500 тысяч строк, какую из них следует учитывать?
Я работаю примерно с 50 тысячами строк, но если он будет достаточно быстрым, чтобы обрабатывать 500 тысяч строк, даже лучше.
Еще один вариант выбора CHOOSEROWS — использование GROUPBY (на момент написания статьи доступно только инсайдерам):
=CHOOSEROWS(A1:B5,TAKE(GROUPBY(A1:A5,SEQUENCE(ROWS(B1:B5)),MAX,0,0),,-1))
Не могу заставить это работать. Что такое «МАКС»? приведенная формула, вероятно, не соответствует тому, что указано в вашей электронной таблице (не видно полностью).
Что вы получаете взамен? Я предполагаю, что вы получаете ошибку #Name. Я думал, что они выпустили его для всех, но я могу ошибаться, и в настоящее время он доступен только инсайдерам.
Да, ошибка #Имя. Не инсайдер.
Ах, я сделаю это предостережение в ответе, извините.
Есть много возможностей сделать это, я предполагаю, что это еще два метода, которые можно применить, хотя я еще не проводил никаких тестов скорости.
Способ первый:
=LET(α, A1:A5, δ, UNIQUE(α), HSTACK(δ, LOOKUP(δ,α,B1:B5)))
Способ второй:
=LET(α, A1:A5, δ, UNIQUE(α), HSTACK(δ, XLOOKUP(δ,α,B1:B5,,,-1)))
Метод третий: (с использованием обратного двоичного поиска xlook для определения скорости)
=LET(d,SORT(A1:B5,,-1), a,CHOOSECOLS(d,1), b,CHOOSECOLS(d,2), u,SORT(UNIQUE(a)),
HSTACK(u,XLOOKUP(u,a,b,"",0,-2)) )
Очень быстро. Использовался второй метод, поскольку он использует xlookup, но был изменен на использование обратного двоичного поиска путем сортировки данных в обратном порядке, но сохранена сортировка уникальных переменных по возрастанию, поэтому результаты получаются по возрастанию. Этот метод простой, быстрый и интуитивно понятный. Спасибо!!
Для потомков... Бинарная модификация ответа: =LET(d,SORT(A1:B5),,-1),a,ВЫБЕРИТЕ ECOLS(d,1),b,ВЫБЕРИТЕ ECOLS(d,2),u, SORT(UNIQUE(a)),HSTACK(u,XLOOKUP(u,a,b,"",0,-2)))
@ciso, если хотите, можете отредактировать ответ и разместить там свою идею. Кроме того, я вижу, что у вас неправильный синтаксис для функции сортировки, скобки после b5 там не должно быть, но я понял, что ваша формула может быть опечаткой. Также я предпочту индекс вместо выбора столбцов.
Почему вы предпочитаете индекс вместо выбора столбцов?
@ciso он короче по сравнению с другим.
Вот интересный метод:
=LET(
a, GROUPBY(A1:A5, B1:B5, ARRAYTOTEXT, , 0),
HSTACK(CHOOSECOLS(a, 1), --RIGHT(CHOOSECOLS(a, 2)))
)
Сэр, использование RIGHT() не вернет результат, если значения в столбце B двузначные или трехзначные. =LET(a, GROUPBY(A1:A5,B1:B5,ARRAYTOTEXT,,0), HSTACK(TAKE(a,,1), --TEXTAFTER(", "&TAKE(a,,-1),", ",-1)))
Нижний уникальный
- Я не уверен, что вы подразумеваете под медленными формулами БигБена.
- Вот более динамичная версия второй, которая выполняется менее чем за секунду для 500 тысяч строк в моей старой 64-битной конфигурации Windows и 64-битной конфигурации Office. Вы не можете серьезно ожидать, что он будет работать быстрее, я имею в виду, что
=SEQUENCE(500000)занимает почти полсекунды.
=LET(data,A2:F500001,unique_col,1,
du,INDEX(data,,unique_col),
u,UNIQUE(du),
CHOOSEROWS(data,XMATCH(u,du,,-1)))
- Данные были
=RANDARRAY(500000,,1000,9999,1)вA2и=SEQUENCE(500000)вB2, затем скопировали/вставили значения и скопировали все это вC2:F2. В результате получилось ожидаемое 9000 уникальных строк (1,8%). - Интересное наблюдение: если
INDEX(data,,unique_col)заменить наCHOOSECOLS(data,unique_col), это займет 2 секунды (TAKE(data,,unique_col)выполняет то же самое). - Возможно, последняя или какая-то другая формула в вашей рабочей тетради является причиной плохой успеваемости. Поделитесь отзывами.
Для 50 000 строк БигБену потребовалось около минуты, чтобы вернуться. Маюх около секунды.
Почему бы не использовать
XLOOKUP()сUNIQUE()-->=LET(α, A1:A5, δ, UNIQUE(α), HSTACK(δ, XLOOKUP(δ,α,B1:B5,,,-1)))? Это тоже будет медленно? или почему бы не использоватьLOOKUP()-->=LET(α, A1:A5, δ, UNIQUE(α), HSTACK(δ, LOOKUP(δ,α,B1:B5)))