Уникальные значения из массива excel

Уникальные значения из массива excel

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

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

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

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

Вложение Размер Загрузки Последняя загрузка
Unique.xls 41.5 КБ 227 1 год 23 недели назад
  • 63309 просмотров

Комментарии

Благодарю за Вашу работу. Столкнулся с проблемой выборки дубликатов из массива около 80000 строк и записью строки из 6 значений другого массива на лист ошибок. Решил вопрос доработкой функции UniqueValuesFromArray плюс быстрый поиск ArraySearchResults. Временные показатели очень порадовали 40 секунд вместо 10 минут перебора и выборки из другого массива. Прошу дополнить страницу или создать новую с функцией DoubleValuesFromArray (Поиск уникальных ПОВТОРЯЮЩИХСЯ значений в массиве)

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

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

Это потому что вы в другой файл перенесли только часть кода (а код функции 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 умирает на расчетах.(((((

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

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

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

В статье написано:

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

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

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

Доброго времени суток. Макрос у меня заработал отлично а вот функция "Уникальные" почему то работает только в файле примере Unique.xls. Ситуация такая: все как положено добавил функцию в модуль, но она на отрез отказывается корректно работать, вместо уникальных значений выдает первое попавшееся значение причем оно возвращается во всех ячейках где прописана функция. Помогите пожалуйста разобраться.

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

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

Читайте также:  Вархаммер 40000 что это

Добрый день. Разбираю сейчас Ваш макрос. Не сильна в них. Прошу помочь. В целом все работает отлично, но не могу понять как поменять его, чтобы новый массив уникальных вставал на другой (заданный) лист и не столбцом, а строкой. Буду очень признательна за помощь.

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

Владимир, ваш вопрос не имеет никакого отношения к теме статьи
В вашем случае, макросы не нужны, — достаточно одной формулы
Обратитесь на любой форум по 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

Function UniqueValuesFromArray может возвращать массив размерностью исходного массива, а не размерностью N * 1?

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

Подскажите пожалуйста, как изменить ваши процедуры, чтобы можно было выбрать уникальные значения не одного столбца, а нескольких. Например в Стобце A значения а,в,а,в. В столбце B в соседних ячейках значения с,д,с,е. Нужно чтобы макрос вдавал массив: В первом столбце: а,в,в. В соседнем с,д,е.

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

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

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

Да, пример Sample__21-08-2011__17-14-22.zip — самое то.

Еще раз большое спасибо!

Спасибо. Буду изучать.

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

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

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

Пример её использования для вашего случая:

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

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

Читайте также:  Что сегодня с триколором нет сигнала

Т.е. чтобы был аналог функционалу Excel 2007: Выделяю столбцы A,B,C => Данные => Удалить дубликаты => В качестве столбца, по которому будет происходить удаление дубликатов выбираю только столбец 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))

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

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

Здравствуй уважаемый пользователь!

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

Список уникальных значений возможно создать 6-ю способами:

Создать список уникальных значений с помощью специальной функции

Это очень простой способ для владельцев Excel выше 2007 версии как произвести отбор уникальных значений. Вам нужно на вкладке «Данные», в разделе «Работа с данными», использовать специальную команду «Удалить дубликаты».

В появившемся диалоговом окне «Удалить дубликаты», вы выделяете те столбики, где необходимо произвести отсев уникальных значений и нажимаете «Ок». В случае, когда в выделенном диапазоне размещается и заголовок таблицы, то поставьте галочку на пункте «Мои данные содержат заголовки», что бы вы случайно не удалили данные. Внимание! Когда вы будете производить отсев уникальных значений в таблице, где столбиков больше 2 и они взаимосвязаны информацией, Excel предложит вам расширить диапазон выбора, с чем вы должны, согласится, иначе будет нарушена логическая связь с другими столбиками.

Создать список уникальных значений с помощью расширенного фильтра

Это также не сложный способ произвести отбор уникальных значений в таблице. Использовать этот инструмент возможно на вкладке «Данные», потом выбрать «Фильтр», и наконец «Расширенный фильтр», этот путь подходит для Excel 2003, а вот владельцы более юных версий, от 2007 и выше стоит пройти по пути: «Данные» — «Сортировка и фильтр» — «Дополнительно». Огромный плюс этого способа в том, что вы можете создать новый список уникальных значений в другом месте. После появления диалогового окна «Расширенный фильтр», устанавливаем галочку напротив пункта «Скопировать результат в другое место», потом указываем диапазон с вашими данными в поле «Исходный диапазон», при необходимости указываем критерий отбора, но для общего отсева поле оставляем пустым «Диапазон критериев», в третьем поле «Поместить результат в диапазон» указываем первую ячейку куда будут помещаться наши данные, отмечаем галочкой пункт «Только уникальные записи» и нажимаем «Ок». Если же вам не нужно никуда переносить ваши данные, то просто установите флажок для пункта «Фильтровать список на месте», данные не пострадают, произойдет наложение обыкновенного фильтра.

Внимание! Если программа запрещает вам переносить отфильтрованные данные на другой лист, вы просто запустите «Расширенный фильтр» на том листе, куда вам надо перенести отобранные уникальные значения.

Создать список уникальных значений с помощью формул

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

Читайте также:  Календарь жителей планеты плюк

Пример 1. Вам нужно пронумеровать, уникальные, значение в списке значений, для этого нужно использовать функцию ЕСЛИ в формуле следующего вида:

=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;"«)

Суть формулы в том, что она проверяет сколько раз, текущее значение встречается в вашем диапазоне (начиная с начала), и если это значение равно 1, то есть это первое уникальное значение, формула ставит последовательно возвращающий номер по порядку. Теперь можно произвести отбор уникальных значений, которые были ранее пронумерованы. Сделать это возможно в любом из соседних столбиков используя функцию ВПР и копируя ее вниз:

=ЕСЛИ(МАКС(A1:A100)

С ростом богатства растут и заботы. Гораций

Способ 1. Штатная функция в Excel 2007

Начиная с 2007-й версии функция удаления дубликатов является стандартной — найти ее можно на вкладке Данные — Удаление дубликатов (Data — Remove Duplicates) :

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

Способ 2. Расширенный фильтр

Если у вас Excel 2003 или старше, то для удаления дубликатов и вытаскивания из списка уникальных (неповторяющихся) элементов можно использовать Расширенный фильтр (Advanced Filter) из меню (вкладки) Данные (Data) .

Предположим, что у нас имеется вот такой список беспорядочно повторяющихся названий компаний:

Выбираем в меню Данные — Фильтр — Расширенный фильтр (Data — Filter — Advanced Filter) . Получаем окно:

  • Выделяем наш список компаний в Исходный диапазон (List Range) .
  • Ставим переключатель в положение Скопировать результат в другое место (Copy to another location) и указываем пустую ячейку.
  • Включаем (самое главное!) флажок Только уникальные записи(Uniqe records only) и жмем ОК.

Получите список без дубликатов:

Если требуется искать дубликаты не по одному, а по нескольким столбцам, то можно предварительно склеить их в один, сделав, своего рода, составной ключ с помощью функции СЦЕПИТЬ (CONCATENATE) :

Тогда дальнейшая задача будет сводиться к поиску дубликатов уже в одном столбце.

Способ 3. Выборка уникальных записей формулой

Чуть более сложный способ, чем первые два, но зато — динамический, т.е. с автоматическим пересчетом, т.е. если список редактируется или в него дописываются еще элементы, то они автоматически проверяются на уникальность и отбираются. В предыдущих способах при изменении исходного списка нужно будет заново запускать Расширенный фильтр или жать на кнопку Удаление дубликатов.

Итак, снова имеем список беспорядочно повторяющихся элементов. Например, такой:

Первая задача — пронумеровать всех уникальных представителей списка, дав каждому свой номер (столбец А на рисунке). Для этого вставляем в ячейку А2 и копируем затем вниз до упора следующую формулу:

В английской версии это будет:

Эта формула проверяет сколько раз текущее наименование уже встречалось в списке (считая с начала), и если это количество =1, т.е. элемент встретился первый раз — дает ему последовательно возрастающий номер.

Для упрощения адресации дадим нашим диапазонам (например, исходя из того, что в списке может быть до 100 элементов) имена. Это можно сделать в новых версиях Excel на вкладке Формулы — Диспетчер имен (Formulas — Name manager) или в старых версиях — через меню Вставка — Имя — Присвоить (Insert — Name — Define) :

  • диапазону номеров (A1:A100) — имя NameCount
  • всему списку с номерами (A1:B100) — имя NameList

Теперь осталось выбрать из списка NameList все элементы имеющие номер — это и будут наши уникальные представители. Сделать это можно в любой пустой ячейке соседних столбцов, введя туда вот такую формулу с известной функцией ВПР (VLOOKUP) и скопировав ее вниз на весь столбец:

=ЕСЛИ(МАКС(NameCount) или в английской версии Excel:

Эта формула проходит сверху вниз по столбцу NameCount и выводит все позиции списка с номерами в отдельную таблицу:

Ссылка на основную публикацию
Умный браслет с функцией измерения давления
Вы посвящаете свою жизнь спорту или просто стараетесь всеми возможными способами следить за своим здоровьем? Придерживаетесь того, что во время...
Тонны в сутки в кг в секунду
Сколько Килограмм в секунду в Метрическая тонна в сутки: 1 Килограмм в секунду = 86.4 Метрическая тонна в сутки 1...
Тонер для заправки картриджей canon 725
Совместимость: Картридж Canon 728 подходит к принтерам MF-4410, 4430, 4450, 4550, 4570, 4580, 4730, 4750, 4780, 4870, 4890. Аналог —...
Умный выключатель zigbee aqara
Протокол передачи данных в домашних системах автоматизации. Реле Xiaomi Aqara Xiaomi Aqara wireless relay Систему "Умного дома" сложно представить без...
Adblock detector