Poldet1.ru

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

Как разрешить только уникальные значения в Excel?

Excel формула уникальные значения из столбца

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

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

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

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

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

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

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

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

Читайте так же:
Как преобразовать лист или ячейки Excel в таблицу HTML?

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

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

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

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

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

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

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

По мере роста благосостояния растут и заботы. Гораций

/>

Информация на веб-сайте

Инструменты и настройки

Excel Windows
и
Excel Macintosh

Проблемы и пути решения

Работа и общение

Как работает форум и сайт

Функции электронных таблиц Excel

= Мир MS Excel/Статьи об Excel

  • 1
  • 2
  • 3

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

П роблема: Как извлекать уникальные значения с помощью формул?

Решение: Формула массива (вводится с помощью комбинации клавиш Ctrl+Shift+Enter):

КАК ЭТО РАБОТАЕТ: Функция ПОИСКПОЗ, сравнивающая два массива возвращает ИСТИНА только в том случае, если вхождение искомого значения в массив является первым. В результате работы ПОИСКПОЗ мы получаем массив: <ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ и т.д.>.
С помощью функции ЕСЛИ мы сравниваем получившийся массив с массивом <1:2:3:4:5:6:7:8 и т.д.>, полученном в результате работы функции СТРОКА с заданным диапазоном и в случае если ПОИСКПОЗ вернул ИСТИНА получаем номер строки вхождения.

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

Задание выполнено. Остается только преобразовать итог в список. Для этого мы используем функцию LARGE, которая создаст ряд вариаций сначала из чисел, а затем из значений FALSE, и функцию INDEX, которая вернет необходимые текстовые значения из соответствующей таблицы. Желтые клетки содержат общее количество :

МИНУС: Формулы массивов значительно замедляют скорость преобразования электронных таблиц.

Область применения: все версии Excel

Н ЕОБХОДИМО: Вы можете использовать проверку ошибок в соответствии с вашей версией Excel или использовать условное форматирование для устранения ошибочного значения

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

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

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

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

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

У нас есть этот случайный список названий компаний:

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

  • Выберите наш список компаний в диапазоне списка.
  • Установите переключатель в положение Копировать в другое место и укажите пустую ячейку.
  • Включите (это самое важное!) флажок Только записи Uniqe и нажмите OK.

Получите список, в котором нет дубликатов:

Если вы хотите искать дубликаты в более чем одном столбце, вы можете сначала объединить их, чтобы создать своего рода составной ключ, используя функцию КОНКАТЕНАТ:

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

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

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

Читайте так же:
Как применить заморозить / разморозить панели сразу к нескольким листам?

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

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

В английском языке это будет :

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

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

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

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

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

Формула проходит сверху вниз по столбцу NameCount и выводит все элементы списка с номерами в отдельной таблице:

Как найти уникальные значения в Google Таблицах

В этом уроке вы узнаете два простых метода быстрого поиска уникальных значений в данных с одним или несколькими столбцами

  • Используйте инструмент «Удаление дубликатов», встроенный в Google Таблицы.
  • Использование функции UNIQUE .

Мы хотим удалить дублирующие записи из следующего набора данных:

Обратите внимание, что в строках 2 и 5, а также в строках 6 и 7 есть повторяющиеся записи.

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

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

Как пользоваться расширенным фильтром в Excel

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

Читайте так же:
Как проверить, является ли число простым числом в Excel?

Условие.

Заполните расширенное меню фильтра:

Параметры1.

Мы получаем таблицу, строки которой отбираются по заданному критерию:

Пример2.

Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.

Критерий.

Заполняем параметры фильтрации. Нажимаем ОК.

Пример3.

Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» — значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.

Критерий1.

Примените инструмент расширенной фильтрации:

Пример4.

С помощью этого инструмента вы сможете решить практически любую проблему выбора значений из массивов с помощью формул.

  1. Результат формулы является критерием отбора.
  2. Введенная формула возвращает результат TRUE или FALSE.
  3. Исходный диапазон задается абсолютными ссылками, а критерии выбора (в форме формулы) — относительными ссылками.
  4. Если возвращается значение TRUE, то строка будет отображаться после применения фильтра. ЛОЖЬ — нет.

Отобразим строки, содержащие количество, превышающее среднее. В ячейку I1 введите название «Наибольшее количество» в стороне таблицы с критериями. Ниже приведена формула. Воспользуемся функцией CERTAIN.

СРЗНАЧ.

Вызовите «Расширенный фильтр» для любой ячейки в исходном диапазоне. В критериях отбора используем I1:I2 (ссылки относительные!). ).

В таблице остаются только те строки, в которых значения в колонке «Количество» выше среднего.

Пример5.

Если вы хотите оставить в таблице только уникальные строки, в окне «Расширенный фильтр» необходимо установить флажок «Только уникальные записи».

Уникальные значения.

Нажмите OK. Повторяющиеся линии будут скрыты. На листе останутся только уникальные записи.

Использование быстрых фильтров

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

Читайте так же:
Как распечатать все варианты в выпадающем списке в Excel?

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

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

Установите или снимите флажок напротив данных, которые вы хотите просмотреть или скрыть.

Совет. Если вы работаете с большим набором данных и хотите, чтобы отображались только те элементы, которые в данный момент выбраны в списке, нажмите «Показать выбранные».

Щелкните в любом месте листа, когда закончите работу.

Правила быстрых фильтров можно просмотреть на вкладке «Фильтр» в боковом меню .

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

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

Шаги по использованию формул Excel для извлечения уникальных значений из диапазона

Одним из первых, которые будут использоваться в листе, является " SUMPRODUCT ». Как следует из названия, он отвечает за добавление продуктов или значений определенного диапазона. Хотя также возможно добавить данные из ячеек разных листов .

Так же, " COUNTIF Функция, которая пытается определить параметры, называемые условиями, чтобы оценить, соответствуют ли данные им, чтобы они были частью формулы.

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

  • » = СУММПРОИЗВ (1 / СЧЁТЕСЛИ ( диапазон столбцов )) ».

С другой стороны, через «Библиотеку формул» вы также можете использовать вышеуказанные функции для ручного заказа счета-фактуры.

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

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

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

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