Polytech-soft.com

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

Оставить только уникальные значения в excel

6 способов создать список уникальных значений в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как в Экселе найти уникальные значения

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

Конечно, вы можете использовать инструмент удаления дубликатов, но если список динамически обновляется, лучше применять рассмотренные ниже практики, т.к. они будут автоматически «подхватывать» изменения.

Опишу пример из жизни: есть таблица имён для ребёнка, куда записали предложения все члены семьи. Нам нужно убрать повторы из этого перечня:

Алгоритм поиска таков:

  1. Первым уникальным значением будем считать заголовок второй таблицы. Это необходимо, чтобы дальнейшая логика срабатывала
  2. Посчитаем, сколько раз уже найденные уникальные значения встречаются в общем перечне. Получим для каждой строчки массивы с количеством элементов, равным размеру исходного списка. В нашем примере это 9 элементов. Для первой строки результат должен выглядеть так: <0;0;0;0;0;0;0;0;0>. То есть, ни одно из уникальных имён не найден в исходнике
  3. В этом массиве нулями будут помечены те имена, которые еще не были добавлены в список. Добавляем к уникальным то имя, которому соответствует ближайший к началу нолик.
  4. Переходим к следующей строке уникального списка и проделываем всё заново, начиная со второго пункта. При этом, массив уже будет выглядеть так:
  5. Останавливаемся, когда все уникальные имена будут выведены

Надеюсь, принцип ясен. Реализовываем:

      Для подсчёта вхождений уникальных значений в изначальный перечень, применим такую формулу массива:

    Обратите внимание на 2 момента здесь:

    • Интервал $D$1:D1 означает, что первая ячейка $D$1 будет оставаться неизменной, а последняя (D1) – спускаться ниже, когда вы копируете формулу вниз. Таким образом, заданный диапазон всегда будет охватывать все найденные уникальные имена
    • Формула массива СЧЁТЕСЛИ принимает в качестве критерия диапазон, а значит, будет считать количество совпадений уникальных имён с каждой из ячеек интервала-критерия. Таким образом, мы получим массив, о котором говорили выше
  1. Теперь в полученном массиве ищем первое нулевое значение с помощью функции ПОИСКПОЗ:

    Результатом выполнения этого шага будет индекс найденного уникального значения в полном списке

    Скопируем эту формулу в оставшиеся ячейки и получим все уникальные значения. Но видим в последних двух строчках ошибку #Н/Д. Это из-за того, что все уникальные значения найдены, новые уже не находятся.

    Используем функцию перехвата ошибки ЕСНД, чтобы вывести пустую строку («») вместо текста ошибки:

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

    Фильтр уникальных значений или удаление повторяющихся значений

    В этом курсе:

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

    Чтобы отфильтровать уникальные значения, нажмите кнопку данные > отсортировать & фильтр > Дополнительно.

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

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

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

    Повторяющееся значение — это одно из значений, для которого все значения по крайней мере одной строки идентичны всем значениям в другой строке. Сравнение повторяющихся значений зависит от того, что отображается в ячейке, а не на основе значения, хранящегося в ячейке. Например, если у вас есть одинаковое значение даты в разных ячейках, например «3/8/2006», а другое — «Мар 8, 2006», значения будут уникальными.

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

    Выполните указанные ниже действия.

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

    Нажмите кнопку данные > дополнительно (в группе Фильтр сортировки & ).

    В всплывающем окне Расширенный фильтр выполните одно из следующих действий:

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

    Нажмите кнопку Фильтровать список на месте.

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

    Выберите команду скопировать в другое место.

    В поле Копировать в введите ссылку на ячейку.

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

    Установите флажок только уникальные записии нажмите кнопку ОК.

    Уникальные значения из диапазона будут скопированы в новое место.

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

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

    Выполните указанные ниже действия.

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

    На вкладке данные нажмите кнопку Удалить дубликаты (в группе Работа с данными ).

    Выполните одно или несколько из указанных ниже действий:

    В разделе столбцывыберите один или несколько столбцов.

    Чтобы быстро выделить все столбцы, нажмите кнопку выделить все.

    Чтобы быстро удалить все столбцы, нажмите кнопку снять выделение.

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

    Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не будут выделяться все столбцы. Например, если выбрать Столбец1 и Столбец2 (но не Столбец3), то «Key», использованный для поиска дубликатов, — это значение обоих Столбец1 & Столбец2. Если в этих столбцах обнаружена повторяющаяся копия, вся строка удаляется, в том числе другие столбцы в таблице или диапазоне.

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

    Чтобы изменить изменения, нажмите кнопку Отменить (или нажмите клавиши CTRL + Z на клавиатуре).

    Невозможно удалить повторяющиеся значения из структурированных данных структуры или с промежуточными итогами. Для удаления дубликатов необходимо удалить структуру и промежуточные итоги. Дополнительные сведения можно найти в разделе Структурирование списка данных на листе и Удаление промежуточных итогов.

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

    Выполните указанные ниже действия.

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

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

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

    Выполните указанные ниже действия.

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

    На вкладке Главная в группе стили щелкните стрелку рядом с кнопкой условное форматированиеи выберите пункт Управление правилами , чтобы открыть всплывающее окно диспетчера правил условного форматирования .

    Выполните одно из следующих действий.

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

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

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

    В списке форматировать все измените описание правилавыберите значение уникальное или повторяющееся.

    Нажмите кнопку Формат , чтобы открыть всплывающее окно Формат ячеек .

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

    В Excel в Интернете вы можете удалить повторяющиеся значения.

    Удаление повторяющихся значений

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

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

    Выполните указанные ниже действия.

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

    На вкладке данные нажмите кнопку Удалить дубликаты .

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

    Примечание: Данные будут удалены из всех столбцов, даже если на этом этапе не будут выделяться все столбцы. Например, если выбрать Столбец1 и Столбец2 (но не Столбец3), то «Key», использованный для поиска дубликатов, — это значение обоих Столбец1 & Столбец2. При обнаружении дубликата в Столбец1 и Столбец2 вся строка будет удалена, включая данные из Столбец3.

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

    Примечание: Если вы хотите вернуться к данным, просто нажмите кнопку отменить (или нажмите клавиши CTRL + Z на клавиатуре).

    Дополнительные сведения

    Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

    См. также

    Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

    Отбор уникальных значений (убираем повторы из списка) в EXCEL

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

    Пусть в столбце А имеется список с повторяющимися значениями, например список с названиями компаний.

    Задача

    В некоторых ячейках исходного списка имеются повторы — новый список уникальных значений не должен их содержать.

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

    Решение

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

    Для создания Динамического диапазона :

    • на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
    • в поле Имя введите: Исходный_список ;
    • в поле Диапазон введите формулу =СМЕЩ(УникальныеЗначения!$A$5;;; СЧЁТЗ(УникальныеЗначения!$A$5:$A$30))
    • нажмите ОК.

    Список уникальных значений создадим в столбце B с помощью формулы массива (см. файл примера ). Для этого введите следующую формулу в ячейку B5 :

    После ввода формулы вместо ENTER нужно нажать CTRL + SHIFT + ENTER . Затем нужно скопировать формулу вниз, например, с помощью Маркера заполнения . Чтобы все значения исходного списка были гарантировано отображены в списке уникальных значений, необходимо сделать размер списка уникальных значений равным размеру исходного списка (на тот случай, когда все значения исходного списка не повторяются). В случае наличия в исходном списке большого количества повторяющихся значений, список уникальных значений можно сделать меньшего размера, удалив лишние формулы, чтобы исключить ненужные вычисления, тормозящие пересчет листа.

    Разберем работу формулу подробнее:

    • Здесь использование функции СЧЁТЕСЛИ() не совсем обычно: в качестве критерия (второй аргумент) указано не одно значение, а целый массив Исходный_список , поэтому функция возвращает не одно значение, а целый массив нулей и единиц. Возвращается 0, если значение из исходного списка не найдено в диапазоне B4:B4( B4:B5 и т.д.), и 1 если найдено. Например, в ячейке B5 формулой СЧЁТЕСЛИ(B$4:B5;Исходный_список) возвращается массив <1:0:0:0:0:0:0:1:0:0:0:0:1:1:0>. Т.е. в исходном списке найдено 4 значения «ООО Рога и копытца» ( B5 ). Массив легко увидеть с помощью клавиши F9 (выделите в Строке формул выражение СЧЁТЕСЛИ(B$4:B5;Исходный_список) , нажмите F9 : вместо формулы отобразится ее результат);
    • ПОИСКПОЗ() – возвращает позицию первого нуля в массиве из предыдущего шага. Первый нуль соответствует значению еще не найденному в исходном списке (т.е. значению «ОАО Уважаемая компания» для формулы в ячейке B5 );
    • ИНДЕКС() – восстанавливает значение по его позиции в диапазоне Исходный_список ;
    • ЕСЛИОШИБКА() подавляет ошибку, возникающую, когда функция ПОИСКПОЗ() пытается в массиве нулей и единиц, возвращенном СЧЁТЕСЛИ() , найти 0, которого нет (ситуация возникает в ячейке B12 , когда все уникальные значения уже извлечены из исходного списка).

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

    Примечание . Функция ЕСЛИОШИБКА() будет работать начиная с версии MS EXCEL 2007, чтобы обойти это ограничение читайте статью про функцию ЕСЛИОШИБКА() . В файле примера имеется лист Для 2003 , где эта функция не используется.

    Решение для списков с пустыми ячейками

    Если исходная таблица содержит пропуски, то нужно использовать другую формулу массива (см. лист с пропусками файла примера ): =ЕСЛИОШИБКА(ИНДЕКС($A$5:$A$19; ПОИСКПОЗ( 0;ЕСЛИ(ЕПУСТО($A$5:A19);»»;СЧЁТЕСЛИ($B$4:B4;$A$5:$A$19));0) );»»)

    Решение без формул массива

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

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

    СОВЕТ2 : Для тех, кто создает список уникальных значений для того, чтобы в дальнейшем сформировать на его основе Выпадающий список , необходимо учитывать, что вышеуказанные формулы возвращают значение Пустой текст «» , который требует аккуратного обращения, особенно при подсчете значений (вместо обычной функции СЧЕТЗ() нужно использовать СЧЕТЕСЛИ() со специальными аргументами ). Например, см. статью Динамический выпадающий список в MS EXCE L.

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

    Читать еще:  Как снять видео с фоновой музыкой
Ссылка на основную публикацию
ВсеИнструменты 220 Вольт
Adblock
detector