0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро создать уникальный список (удалить дубликаты) из заданного столбца в Excel?

Извлечение уникальных значений из диапазона ячеек или массива

Функция UniqueValuesFromArray позволяет найти в указанном столбце двумерного массива все уникальные значения, и получить новый массив, содержащий все найденные уникальные значения.
Это может пригодиться, если надо, к примеру, заполнить ComboBox на форме возможными вариантами значений из базы данных:

Код самой функции:

Прилагается пример использования этой функции в макросе (для экспонирования уникальных записей в другой столбец электронной таблицы) и пользовательская функция уникальности для использования в формулах электронных таблиц Excel.

Макрос и дополнительная функция из прикрепленного файла:

Unique.xls41.5 КБ
  • 70972 Vistas (на английском)

Комментарии

Спасибо за вашу работу. Я столкнулся с проблемой выбора дубликатов из массива, состоящего примерно из 80000 строк, и переноса значений массива на лист ошибок. Модифицировал UniqueValuesFromArray вместе с ArraySearchResults, чтобы решить проблему. По сравнению с поиском и выбором из другого массива, 40 секунд — это очень хорошее время. Вы можете добавить на свой сайт страницу с использованием функции DoubleValuesFromArray (Поиск UniqueValuesFromArray)

Еще раз спасибо за вашу работу. Вы не раз мне помогали.

Игорь, спасибо!
Подскажите, как доработать функцию так, чтобы уникальные значения переносились в том формате, в котором они находились в начальном массиве? В моем случае начальный массив — это числа, которые выгружаются из другой программы и не отформатированы, как числа, в то время, как переносятся они в виде чисел. Из-за этого в последствии не работает функция ВПР.
Или может быть лучше сделать так, чтобы функция сначала преобразовывала начальный массив в числа и только потом переносила уникальные?

Это потому, что вы перенесли только часть кода в другой файл (и забыли скопировать код UniqueValuesFromArray).

Добрый день!
На родном файле макрос работает отлично, на другом, с тем же расширением не работает, выдает ошибку Sub or Function not defined на UniqueValuesFromArray. В чем может быть причина?
Расширения обоих файлов поменял на .xlsm
Спасибо!

РАЗОБРАЛСЯ.
СПАСИБО БОЛЬШОЕ ЗА ФУНКЦИЮ1111111

Нет.
выглядит так:
А B
1 текст 1 текст 1
2 текст 2 текст 2
3 текст 3 текст 3
4 текст 4 текст 4
5 текст 5 текст 5
6 текст 6 текст 6
7 текст 7 текст 7
.
33 текст 33 текст 33
34 текст 34 текст 34
35 текст 35 текст 1
36 текст 36 текст 1
37 текст 37 текст 1
и так делее
Т.е. в столбце А я вставил только уникальные значения до строки 80 (текст 80), но на 35 строке функция перестает работать как-будто, может я что не так сделал?
Помогите пожалуйста, я могу это сделать формулами, ног проблема в нагрузке, в моем файле просматриваемый массив имеет длину 2000 строк, excel умирает на расчетах.(((((

Читайте так же:
Как вставить гистограмму в ячейку в Excel?

Видимо, 36-е значение (которое «первое уникальное») — только с виду похоже на первое
(например, есть лишний пробел, или одна русская буква заменена похожей английской)

Сравнить посимвольно 2 ячейки можно этой надстройкой:
http://excelvba.ru/tools/CharCodes

Добрый день.
Сразу скажу, что в макросах не че не понимаю.
Мне очень понравилась ваша функция, но появилась проблема.
Функция выбирает 35 уникальных значений, далее возвращает первое уникальное.
В чем проблема?

Статья гласит

Пользовательская функция — используется как матричная формула

выделяете НЕСКОЛЬКО ячеек, вводите эту формулу, и завершаете ввод нажатием Ctrl + Shift + Enter (вместо обычного Enter)
Тогда будет выводиться несколько уникальных значений

Как правило, уникальные данные можно вывести с помощью формул, без макросов.

Добрый день. Несмотря на то, что макрос работал нормально, функция «Unique» работала только в файле Unique.xls. Проблема в том, что все функции правильно добавлены в модуль, но она отказывается работать правильно, вместо того, чтобы выдавать уникальные значения, она возвращает первое попавшееся значение на всех ячейках, где прописана функция. Прошу помочь разобраться.

Не знаю, увидите ли вы, но спасибо!
И автору тоже!

Добрый вечер. Вот разбираю макрос. С VBA не работал. Можно ли усложнить функцию вывода уникальных: вывод за период времени с исключением некоторых уникальных значений, т.е. может это значение встречается в этом периоде несколько раз, но выводить его не надо, т.к. это не интересно. Буду очень благодарен за помощь

Доброе утро. Сейчас я работаю над вашим макросом. Я не очень хорошо в этом разбираюсь. Пожалуйста, помогите мне. Я буду очень благодарен за любую помощь.

Тут самое главное, вот это: On Error Resume Next: Dim coll As New Collection
то есть при ошибке продолжить заполнение коллекции, а так как в коллекцию добавляется ключ такой же как и само значение
txt$ = Trim(cell): If Len(txt$) Then coll.Add txt$, txt$ ‘коллекция.добавить значение, ключ
то в коллекции автоматически остается только один уникальный ключ, по определению, и вместе с ним одно уникальное значение

Владимир, ваш вопрос не имеет никакого отношения к теме статьи
В вашем случае, макросы не нужны, — достаточно одной формулы
Обратитесь на любой форум по Excel, прикрепив пример файла, — вам помогут с формулой.

Читайте так же:
Как автоматически расширить панель формул в Excel?

Здравствуйте!
Давно пытаюсь найти решение подобной задачи. Знаний к сожалению не хватает. И вот нашёл Ваш пример. У меня задача хоть и подобная но масштабы больше. В одной книге есть 2 листа:
1 лист (таблица заказов) имеет 159 столбцов с данными, 165 столбец с датой. Каждый столбец с данными пронумерован № продукции 0001,0002,0003, и т.д.0159.
2 лист (отчёт) В отчёте 2 столбец №-ра продукции построчно и 4 столбец для копирования данных продукции с 1 листа.
И вот надо решить задачу: как выбрать продукцию с 1 листа и скопировать во 2 лист по определённой дате заданной во 2 листе.
Вроде бы кажется, что всё просто, но ничего не получается. Прошу Вас подскажите как всё это решить.

Спасибо большое! Действительно быстро работает.
Единственное, что добавил — пропуск пустых ячеек:
For i = LBound(arr) To UBound(arr)
If Trim(arr(i, col)) = «» Then GoTo 1
txt$ = Trim(arr(i, col)): coll.Add txt$, txt$
1: Next i

Вопрос не актуален.
Прочитал ниже, что мне надо использовать http://excelvba.ru/code/JoinedArray

Может ли функция UniqueValuesFromArray вернуть массив размером с исходный массив вместо N * 1?

В дополнительном столбце, при помощи формулы типа =СЦЕПИТЬ(A1;»//»;B1), объедините значения этих 2 столбцов,
и потом по этому доп.столбцу отбирайте уникальные

Я хотел бы узнать, как изменить ваши процедуры, чтобы можно было выбирать уникальные значения не только для одного столбца, но и для нескольких столбцов. В столбце A, например, есть значения a,c,a,c. В соседних ячейках столбца В находятся четыре значения c,e,c,f. Требуется макрос, который даст массив, приведенный ниже: В первом столбце a,c,d. В соседнем c,e,f.

Спасибо! Очень полезно.

Спасибо за функцию.
Работает отлично.
Это уже вторая Ваша функция в течение двух дней, которую я применил в своей работе.
Завтра еще третью буду пробовать — поиск в массиве 🙂

Здравствуйте EducatedFool (Игорь). Мне очень понравилась ваша функция (
Извлечение уникальных значений из диапазона ячеек или массива), но меня еще больше заинтересовал ответ #3, в котором прикреплен пример (Пример в файле: http://excelvba.ru/XL_Files/Sample__21-08-2011__17-14-22.zip).
Вопрос по данной функции, а можно сделать чтобы поиск уникальных значений ввелся с конца массива? И значение записывалось первое найденное в конце массива?
Спасибо за ответ

Да, Sample__21-08-2011__17-14-22.zip — очень хороший.

Спасибо вам большое, еще раз.

Спасибо. Я посмотрю. Я изучу этот вопрос.

Так сделать можно, но.
одно дело — выбрать уникальные значения (тут всё ясно, вариантов особо нет),
и совсем другое — выбрать строки с уникальными значениями в каком-то столбце.

Читайте так же:
Как анализировать данные опроса в Excel?

К примеру, есть у нас 3 строки с одинаковыми значениями в 1-м столбце.
Какую из этих трёх строк выводить в результат? Первую, третью, вторую?

Вообще, у меня есть уже такая функция (даже с большей функциональностью, чем вам требуется):
http://excelvba.ru/code/JoinedArray

Пример использования в том или ином случае:

Извините за надоедливость ))
Возни еще один вопрос.

Сейчас выборка уникальный происходит только по столбцу «A».
Возможно ли сделать выборку уникальных по столбцу «A», но с условием, чтобы в диапазон фильтрования попадали также столбцы B и C ?

Т.е. для того, чтобы иметь ту же функциональность, что и в Excel 2007: Я выбираю столбцы A,B,C => Данные => Удалить дубликаты => Я выбираю только столбец A в качестве столбца, который будет использоваться для удаления дубликатов.

Таким образом, будут выбраны уникальные значения для столбца «A», но записи из столбцов B и C также будут сохраняться.

Вроде разобрался:
Dim ПервыйСтолбец As Range: Set ПервыйСтолбец = Range(Sheets(«данные»).Range(«A1»), Sheets(«данные»).Range(«A» & Rows.Count).End(xlUp))

Подскажите, пожалуйста. Сейчас макрос срабатывает в случае если активен лист с массивом.
Как правильно добавить название листа в запись, чтобы активация листа с массивом не была обязательной?

Я попробовал это, но макрос выдает ошибку:

Dim ПервыйСтолбец As Range: Set ПервыйСтолбец = Sheets(«данные»).Range([A1], Sheets(«данные»).Range(«A» & Rows.Count).End(xlUp))

Все это хорошо, но не совсем понятно, что происходит… если бы только был файл с рабочим примером, можно было бы хотя бы использовать результаты, чтобы понять, что происходит.

Хороший пример того, как создавать уникальные записи с помощью коллекций. Хотя я уже давно использую коллекции, но не задумывался об этом. А работает быстро ))

Простая сводная таблица для подсчета уникальных значений

Это выглядит как простая электронная таблица для изучения. Я хотел бы подсчитать уникальные значения для определенного значения, которое я группирую.

Например, у меня есть следующее:

Моя электронная таблица должна показать следующее:

Созданная мной простая сводная таблица дает только это (количество строк):

Но вместо этого мне нужно несколько уникальных значений.

На самом деле я пытаюсь выяснить, какие значения в первом столбце отличаются от значений во втором столбце для каждой строки. Другими словами, «ABC» — это «хорошо», «DEF» — «плохо

Я уверен, что есть более простой способ сделать это, но я выбрал электронную таблицу.

Читайте так же:
Быстро создавать случайные группы для списка данных в Excel

Вставьте третий столбец и в ячейку C2 вставьте эту формулу

И скопируйте его. Теперь создайте сводную таблицу на основе столбцов 1 и 3.

введите описание изображения здесь

П РЕДУПРЕЖДЕН: Excel 2013 теперь делает это за вас. Я создал этот ответ как новый, потому что мой предыдущий ответ на самом деле решает немного другую проблему.

Когда вы создаете таблицу, убедитесь, что вы установили флажок «Добавить эти данные в модель данных» (см. ниже), если у вас есть такая версия.

Установите флажок «Добавить эти данные в модель данных»

Затем, когда откроется ваша сводная таблица, обычно создайте строки, столбцы и значения. Затем щелкните поле, для которого вы хотите рассчитать различное количество, и измените настройки значения поля: Изменить настройки значения поля

Наконец, прокрутите вниз до самого последнего варианта и выберите «Отличное количество». Выберите опцию «Отличное количество»

Значения вашей сводной таблицы должны обновиться и отобразить нужные данные.

Кроме того, я хотел бы добавить вариант вычисления, который не требует формулы, но может быть полезен, если вам нужно вычислить уникальные значения в двух разных столбцах. В исходном примере у меня не было такого варианта:

И вы хотите, чтобы это выглядело вот так:

И хотел, чтобы это выглядело:

Для меня было проще всего получить данные в этом формате, а затем манипулировать ими с помощью следующих программ:

введите описание изображения здесь

После выбора «Intermediate Total In» выберите заголовок для вторичного набора данных (в данном случае это будет заголовок или заголовок столбца набора данных, который включает 123, 456 и 567). В вашем первичном наборе данных вы получите максимальное значение с общим количеством элементов для этого набора.

Данные были скопированы, вставлены в значения, а затем помещены в другую сводную таблицу, чтобы ими было легче манипулировать.

Для справки, отмечу, что у меня было около четверти миллиона строк данных, поэтому этот метод работал гораздо лучше, чем некоторые методы, основанные на формулах, особенно те, которые пытаются сравнить два столбца/набора данных, потому что программа постоянно аварийно завершалась.

Как быстро создать уникальный список (удалить дубликаты) из заданного столбца в Excel?

x

x

x

Как быстро создать уникальный список (удалить дубликаты) из заданного столбца в Excel?

Если у вас есть список дублирующихся имен и вы хотите создать новый список уникальных имен из этого столбца, что вы можете сделать с помощью Excel? В следующем руководстве представлены некоторые приемы, которые помогут вам быстро создать уникальный список из заданного столбца.

Быстро создать уникальный список из столбца с помощью Kutools for Excel хорошая идея3

Читайте так же:
Как быстро добавить запятую между словами в Excel?
стрелка синий правый пузырьСоздайте уникальный список из столбца с расширенным фильтром

В Excel можно использовать функцию расширенной фильтрации, чтобы отсеять уникальные данные из списка.

1. Нажмите Данные > Дополнительно. См. снимок экрана:

документ создать уникальный список 1

Как показано ниже, нажмите Advanced Filter в диалоговом окне Filter:

Отметьте опцию Копировать в другом месте и опцию Только уникальные записи;

Во-вторых, выберите текстовое поле Дублировать столбец в диапазон списка и выберите ячейку, в которой будет отображаться уникальный список. Скопируйте в текстовое поле.

документ создать уникальный список 2

3. Нажмите OK. Теперь создан единый список. См. скриншот :

документ создать уникальный список 3

стрелка синий правый пузырьСоздать уникальный список из столбца с VBA

В Excel вы также можете использовать код VBA для создания единого списка из заданного столбца.

1. нажмите Alt + F11, чтобы отобразить окно Microsoft Visual Basic for Applications, и нажмите Insert > Modules, затем скопируйте и вставьте следующий код VBA в окно нового модуля.

Функция VBA создает уникальный список на основе данных в столбце.

2. Когда код VBA включен, нажмите клавишу F5 или кнопку Run, в появившемся диалоговом окне выберите список дубликатов, затем выберите ячейку для отображения результата, после чего нажмите OK. См. скриншоты:

документ создать уникальный список 4
документ создать уникальный список 5

3. Список уникальных имен уже создан.

стрелка синий правый пузырьБыстро создать уникальный список из столбца с помощью Kutools for Excel

В Kutools for Excel вы также можете использовать функцию Выбрать повторяющиеся и уникальные ячейки для быстрого создания уникального списка из определенного столбца.

Выберите повторяющиеся и уникальные ячейки» является частью пакета Kutools для Excel.

1. Чтобы создать уникальный список, выберите список (не обязательно выбирать заголовок столбца) и выберите Cthulz > Select > Select Repeating and Unique Cells. См. скриншот:

док создать уникальный 4

2. В появившемся диалоговом окне отметьте опцию Все уникальные (включая 1-е дубликаты) в разделе Правило. См. снимок экрана:

док создать уникальный 5

3. После нажатия кнопки Ok появится диалоговое окно, показывающее количество выбранных ячеек. См. снимок экрана:

док создать уникальный 2

4 Нажмите OK, затем нажмите Ctrl + C для копирования выделенных ячеек, выделите ячейку и нажмите Ctrl + V для размещения результата.

док создать уникальный 3

СОВЕТ: Если вы хотите выбирать значения, чувствительные к регистру, вы можете отметить опцию Чувствительный к регистру в диалоговом окне Выбор повторяющихся и уникальных ячеек.

При использовании функции выбора дубликатов и уникальных ячеек можно выбрать все дубликаты, включая первый, все дубликаты, кроме первого, и все уникальные значения, кроме первого. Получите ее сейчас и упростите свою работу с Excel.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector