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

Skip to content

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

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

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

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

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

  • Как найти уникальные значения формулами.
  • Фильтр для уникальных данных.
  • Выделение цветом и условное форматирование.
  • Быстрый и простой способ — Duplicate Remover.

Как найти уникальные значения при помощи формул.

Самый простой способ сделать это – использовать функции ЕСЛИ и СЧЁТЕСЛИ. В зависимости от типа данных, которые вы хотите найти, может быть несколько вариантов формулы, как показано в следующих примерах.

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

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

Чтобы найти уникальные значения в Excel:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$10;  $A2)=1; «Уникальный»; «»)

Чтобы определить различные значения:

=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2;  $A2)=1; «Различный»; «»)

Во второй формуле есть только одно небольшое отличие во второй ссылке на ячейку, что, однако, имеет большое значение:

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

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

Аналогичным образом вы можете найти уникальные строки в таблице Excel на основе изучения записей не в одном, а в двух или более столбцах. В этом случае вам необходимо использовать СЧЁТЕСЛИМН вместо СЧЁТЕСЛИ для оценки значений (до 127 пар диапазон/критерий можно обработать в одной формуле).

Формула для получения уникальных строк:

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A$10; $A2; $B$2:$B$10; $B2)=1; «Уникальная»; «»)

Формула для поиска различных строк:

=ЕСЛИ(СЧЁТЕСЛИМН($A$2:$A2; $A2; $B$2:$B2; $B2)=1; «Различная»; «»)

В нашем случае уникальная комбинация Имя+Фамилия встречается 2 раза. А всего в списке 6 человек, из которых трое дублируются.

Как найти уникальные записи с учетом регистра?

Если вы работаете с набором данных, где важен регистр букв, вам понадобится немного более сложная формула массива.

Поиск уникальных значений с учетом регистра :

{=ЕСЛИ(СУММ((—СОВПАД($A$2:$A$10;A2)))=1;»Уникальный»;»»)}

Поиск различных значений с учетом регистра :

{=ЕСЛИ(СУММ((—СОВПАД($A$2:$A2;$A2)))=1;»Различный»;»»)}

Поскольку обе они являются формулами массива, обязательно нажмите Ctrl + Shift + Enter, чтобы правильно их записать.

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

Фильтр для уникальных значений.

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

  1. Примените одну из приведенных выше формул для определения уникальных или различных ячеек или строк.
  2. Выберите диапазон и нажмите кнопку «Фильтр» на вкладке «Данные». 
  3. Щелкните стрелку фильтрации в заголовке столбца, содержащего формулу, и выберите то, что хотите просмотреть:

Как выбрать уникальные из фильтра.

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

Чтобы быстро выбрать весь получившийся список, включая заголовки столбцов, отфильтруйте уникальные значения, щелкните любую ячейку в получившемся списке, а затем нажмите Ctrl + A.

Чтобы выбрать уникальные значения без заголовков столбцов, отфильтруйте их, выберите первую ячейку с данными и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней ячейки.

Примечание. В некоторых редких случаях, в основном в очень больших книгах, рекомендованные выше комбинации клавиш могут выбирать как видимые, так и невидимые ячейки. Чтобы исправить это, нажмите сначала либо Ctrl + A или же Ctrl + Shift + End, а затем нажмите Alt +;  для выбора только видимых ячеек, игнорируя скрытые строки.

Если вам сложно запомнить такое количество комбинаций, используйте этот визуальный способ: выделите весь список, затем перейдите на вкладку «Главная» > «Найти и выделить» > «Выделить группу ячеек» и выберите «Только видимые ячейки».

Как скопировать уникальные значения в другое место?

Чтобы скопировать список на новое место, сделайте следующее:

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

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

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

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

  1. Выберите столбец данных, в котором вы хотите выделить уникальные.
  2. На вкладке Главная в группе Стили щелкните Условное форматирование > Правила выделения ячеек > Повторяющиеся значения …

  1. В диалоговом окне « Повторяющиеся значения » выберите «Уникальный» в левом поле и выберите желаемое форматирование в правом поле, затем нажмите « ОК» .

Совет. Если вас не устраивает какой-либо из предопределенных форматов, щелкните «Пользовательский формат …» (последний элемент в раскрывающемся списке) и установите цвет заливки и / или шрифта по своему вкусу.

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

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

Как создать правило для условного форматирования уникальных значений?

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

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

  1. Перейдите на вкладку «Главная » и щелкните « Условное форматирование» > « Новое правило» > «Использовать формулу», чтобы с ее помощью определить, какие ячейки нужно форматировать .
  2. Введите формулу в поле «Форматировать значения …».
  3. Нажмите кнопку «Формат …» и выберите нужный цвет заливки и/или цвет шрифта.
  4. Наконец, нажмите кнопку ОК , чтобы применить правило.

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

Выделяем цветом отдельные уникальные значения.

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

=СЧЁТЕСЛИ($A$2:$A$10;$A2)=1

Где A2 — первая, а A10 — последняя ячейка диапазона.

Чтобы выделить все различные значения в столбце, то есть встречающиеся хотя бы однажды, используйте это выражение:

= СЧЁТЕСЛИ($A$2:$A2;$A2)=1

Где A2 — самая верхняя ячейка диапазона.

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

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

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

Как видите, формула

=СЧЁТЕСЛИ($A$2:$A$10;$A2)=1

та же самая, что и раньше, но строка в диапазоне выделена вся.

А можно использовать и такое выражение:

=СУММ(Ч($A2&$B2=$A$2:$A$10&$B$2:$B$10))<2

Результат будет таким же.

Как выделить уникальные строки?

Если вы хотите выделить строки на основе значений в двух или более столбцах, используйте функцию СЧЁТЕСЛИМН, которая позволяет указать несколько критериев в одной формуле.

Чтобы выделить уникальные строки:

=СЧЁТЕСЛИМН($A$2:$A$10;$A2; $B$2:$B$10;$B2)=1

Чтобы выделить различные строки:

=СЧЁТЕСЛИМН($A$2:$A2;$A2; $B$2:$B2;$B2)=1

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

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

Однако все эти решения сложно назвать интуитивно понятными и простыми в использовании, поскольку они требуют запоминания нескольких различных формул. Конечно, для профессионалов Excel в этом нет ничего страшного :)

Для тех пользователей Excel, которые хотят сэкономить свое время и силы, позвольте мне показать быстрый и простой способ поиска уникальных значений в Excel.

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

Давайте посмотрим.

  1. Выберите любую ячейку в таблице, в которой вы хотите найти уникальные значения, и нажмите кнопку DuplicateRemover на вкладке AblebitsData в группе Dedupe.

Мастер запустится, и вся таблица будет выбрана автоматически. Итак, просто нажмите « Далее», чтобы перейти к следующему шагу.

  1. В зависимости от вашей цели выберите один из следующих вариантов и нажмите Далее :
    • Уникальные
    • Уникальные + 1е вхождения (различные)

  1. Выберите один или несколько столбцов, в которых вы хотите проверить значения.
    В этом примере мы хотим найти уникальные сочетания Заказчик + Товар на основе значений в двух столбцах. Их и выбираем при помощи галочки.
  2. Выберите один или несколько столбцов, в которых вы хотите проверить значения.

Если у вашей таблицы есть заголовки, обязательно установите флажок Mytable has headers. И если в вашей таблице могут встретиться пустые ячейки, то убедитесь, что установлен флажок Skipempty cells. Оба параметра находятся в верхней части диалогового окна и обычно выбираются по умолчанию.

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

Также наш поиск буден нечувствителен к регистру, то есть не будем при сравнении данных различать прописные и строчные буквы. Поэтому не активируем опцию Case-sensitive match.

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

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

В нашем случае чтобы найти уникальные значения, вполне достаточно будет просто выделить их цветом. Поэтому выберем Highlight with color.

Нажмите кнопку «Готово» и получите результат:

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

Я рекомендую вам загрузить полнофункциональную ознакомительную версию Ultimate Suite и попробовать в работе Duplicate Remover и множество других инструментов, которые помогут сэкономить вам кучу времени при работе в Excel.

Извлечение уникальных элементов из диапазона

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

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

remove-duplicates1.png

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

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

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

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

uniqe1.gif

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

uniqe2.gif

В нем:

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

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

uniqe3.gif

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

remove-duplicates2.png

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

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

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

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

uniqe4.gif

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

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

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

=IF(COUNTIF(B$1:B2;B2)=1;MAX(A$1:A1)+1;»»)

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

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

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

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

=ЕСЛИ(МАКС(NameCount)<СТРОКА(1:1);»»;ВПР(СТРОКА(1:1);NameList;2))

или в английской версии Excel:

=IF(MAX(NameCount)

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

uniqe5.gif

Ссылки по теме

  • Выделение дубликатов по одному или нескольким столбцам в списке цветом
  • Запрет ввода повторяющихся значений
  • Извлечение уникальных значений при помощи надстройки PLEX

Хитрости »

1 Май 2011              532150 просмотров


Как получить список уникальных(не повторяющихся) значений?

Представим себе большой список различных наименований, ФИО, табельных номеров и т.п. А необходимо из этого списка оставить список все тех же наименований, но чтобы они не повторялись — т.е. удалить из этого списка все дублирующие записи. Как это иначе называют: создать список уникальных элементов, список неповторяющихся, без дубликатов. Для этого существует несколько способов: встроенными средствами Excel, встроенными формулами и, наконец, при помощи кода Visual Basic for Application(VBA) и сводных таблиц. В этой статье рассмотрим каждый из вариантов.

  • При помощи встроенных возможностей Excel 2007 и выше
  • При помощи Расширенного фильтра
  • При помощи формул
  • При помощи кодов Visual Basic for Application(VBA) — макросы, включая универсальный код выборки из произвольного диапазона
  • При помощи сводных таблиц

при помощи встроенных возможностей Excel 2007 и выше

В Excel 2007 и 2010 это сделать проще простого — есть специальная команда, которая так и называется — Удалить дубликаты (Remove Duplicates). Расположена она на вкладке Данные (Data) подраздел Работа с данными (Data tools)

Как использовать данную команду. Выделяете столбец(или несколько) с теми данными, в которых надо удалить дублирующие записи. Идете на вкладку Данные (Data)Удалить дубликаты (Remove Duplicates).

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

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

Ставите галочки напротив тех столбцов, дубликаты в которых надо удалить и жмете Ок. Если в выделенном диапазоне так же расположены заголовки данных, то лучше поставить флаг Мои данные содержат заголовки, чтобы случайно не удалить данные в таблице(если они вдруг полностью совпадают со значением в заголовке).


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

В случае с Excel 2003 все посложнее. Там нет такого инструмента, как Удалить дубликаты. Но зато есть такой замечательный инструмент, как Расширенный фильтр. В 2003 этот инструмент можно найти в ДанныеФильтрРасширенный фильтр. Прелесть этого метода в том, с его помощью можно не портить исходные данные, а создать список в другом диапазоне.

В 2007-2010 Excel, он тоже есть, но немного запрятан. Расположен на вкладке Данные (Data), группа Сортировка и фильтр (Sort & Filter)Дополнительно (Advanced)

Как его использовать: запускаем указанный инструмент — появляется диалоговое окно:

  • Обработка: Выбираем Скопировать результат в другое место (Copy to another location).
  • Исходный диапазон (List range): Выбираем диапазон с данными(в нашем случае это А1:А51).
  • Диапазон критериев (Criteria range): в данном случае оставляем пустым.
  • Поместить результат в диапазон (Copy to): указываем первую ячейку для вывода данных — любую пустую(на картинке — E2).
  • Ставим галочку Только уникальные записи (Unique records only).
  • Жмем Ок.

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

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

Для этого надо просто в пункте Обработка выбрать Фильтровать список на месте (Filter the list, in-place).


Способ 2: Формулы

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

А

(

А1:А51

, где

А1

— заголовок). Выводить список мы будем в столбец

С

, начиная с ячейки

С2

. Формула в

C2

будет следующая:

{=ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1))}
{=INDEX($A$2:$A$51;SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1))}
Детальный разбор работы данной формулы приведен в статье: Как просмотреть этапы вычисления формул

Надо отметить, что эта формула является формулой массива. Об этом могут сказать фигурные скобки, в которые заключена данная формула. А вводится такая формула в ячейку сочетанием клавиш —

Ctrl

+

Shift

+

Enter

(при этом сами скобки вводить не надо — они появятся сами после ввода формулы тремя клавишами

Ctrl

+

Shift

+

Enter

). После того, как мы ввели эту формулу в

C2

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

#ЧИСЛО!(#NUM!)

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

для Excel 2007 и выше:
{=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1));»»)}
{=IFERROR(INDEX($A$2:$A$51;SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1));»»)}
для Excel 2003:
{=ЕСЛИ(ЕОШ(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1));»»;ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1)))}
{=IF(ISERR(SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1));»»;INDEX($A$2:$A$51;SMALL(IF(COUNTIF($C$1:C1;$A$2:$A$51)=0;ROW($A$1:$A$50));1)))}

Тогда вместо ошибки 

#ЧИСЛО!(#NUM!)

у вас будут пустые ячейки(не совсем пустые, конечно — с формулами :-)).
Чуть подробнее про отличия и нюансы формул ЕСЛИОШИБКА и ЕСЛИ(ЕОШ можно прочесть в этой статье: Как в ячейке с формулой вместо ошибки показать 0


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

УНИК(UNIQUE)

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

=УНИК($A$2:$A$51)
=UNIQUE($A$2:$A$51)

Что самое важное в данном случае — это функция динамического массива и вводить её надо только в одну ячейку C2, а результат она поместит сама в нужное количество ячеек.


Способ 3: код VBA

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

  • Что такое макрос и где его искать? к статье приложен видеоурок
  • Что такое модуль? Какие бывают модули? потребуется, чтобы понять куда вставлять приведенные ниже коды

Оба приведенных ниже кода следует помещать в стандартный модуль. Макросы должны быть разрешены.

Исходные данные оставим в том же порядке — список с данными расположен в столбце «А«(А1:А51, где А1 — заголовок). Только выводить список мы будем не в столбец С, а в столбец Е, начиная с ячейки Е2:

Sub Extract_Unique()
    Dim vItem, avArr, li As Long
    ReDim avArr(1 To Rows.Count, 1 To 1)
    With New Collection
        On Error Resume Next
        For Each vItem In Range("A2", Cells(Rows.Count, 1).End(xlUp)).Value
            'Cells(Rows.Count, 1).End(xlUp) – определяет последнюю заполненную ячейку в столбце А
            .Add vItem, CStr(vItem)
            If Err = 0 Then
                li = li + 1: avArr(li, 1) = vItem
            Else: Err.Clear
            End If
        Next
    End With
    If li Then [E2].Resize(li).Value = avArr
End Sub

С помощью данного кода можно извлечь уникальные не только из одного столбца, но и из любого диапазона столбцов и строк. Если вместо строки
Range(«A2», Cells(Rows.Count, 1).End(xlUp)).Value
указать Selection.Value, то результатом работы кода будет список уникальных элементов из выделенного на активном листе диапазона. Только тогда неплохо бы и ячейку вывода значений изменить — вместо [E2] поставить ту, в которой данных нет.
Так же можно указать конкретный диапазон:

Или другой столбец:

Range("C2", Cells(Rows.Count, 3).End(xlUp)).Value

здесь отдельно стоит обратить внимание то, что в данном случае помимо изменения А2 на С2 изменилась и цифра 1 на 3. Это указание на номер столбца, в котором необходимо определить последнюю заполненную ячейку, чтобы код не просматривал лишние ячейки. Подробнее про это можно прочитать в статье: Как определить последнюю ячейку на листе через VBA?

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

Sub Extract_Unique()
    Dim x, avArr, li As Long
    Dim avVals
    Dim rVals As Range, rResultCell As Range
 
    On Error Resume Next
    'запрашиваем адрес ячеек для выбора уникальных значений
    Set rVals = Application.InputBox("Укажите диапазон ячеек для выборки уникальных значений", "Запрос данных", "A2:A51", Type:=8)
    If rVals Is Nothing Then 'если нажата кнопка Отмена
        Exit Sub
    End If
    'если указана только одна ячейка - нет смысла выбирать
    If rVals.Count = 1 Then
        MsgBox "Для отбора уникальных значений требуется указать более одной ячейки", vbInformation, "www.excel-vba.ru"
        Exit Sub
    End If
    'отсекаем пустые строки и столбцы вне рабочего диапазона
    Set rVals = Intersect(rVals, rVals.Parent.UsedRange)
    'если указаны только пустые ячейки вне рабочего диапазона
    If rVals Is Nothing Then
        MsgBox "Недостаточно данных для выбора значений", vbInformation, "www.excel-vba.ru"
        Exit Sub
    End If
    avVals = rVals.Value
    'запрашиваем ячейку для вывода результата
    Set rResultCell = Application.InputBox("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8)
    If rResultCell Is Nothing Then 'если нажата кнопка Отмена
        Exit Sub
    End If
    'определяем максимально возможную размерность массива для результата
    ReDim avArr(1 To Rows.Count, 1 To 1)
    'при помощи объекта Коллекции(Collection)
    'отбираем только уникальные записи,
    'т.к. Коллекции не могут содержать повторяющиеся значения
    With New Collection
        On Error Resume Next
        For Each x In avVals
            If Len(CStr(x)) Then 'пропускаем пустые ячейки
                .Add x, CStr(x) 'если добавляемый элемент уже есть в Коллекции - возникнет ошибка
                'если же ошибки нет - такое значение еще не внесено,
                'добавляем в результирующий массив
                If Err = 0 Then
                    li = li + 1
                    avArr(li, 1) = x
                Else
                    'обязательно очищаем объект Ошибки
                    Err.Clear
                End If
            End If
        Next
    End With
    'записываем результат на лист, начиная с указанной ячейки
    If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr
End Sub

Способ 4: Сводные таблицы

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

  • Выделяем один или несколько столбцов в таблице, переходим на вкладку Вставка(Insert) -группа Таблица(Table)Сводная таблица(PivotTable)
  • В диалоговом окне Создание сводной таблицы(Create PivotTable) проверяем правильность выделения диапазона данных (или установить новый источник данных)
  • указываем место размещения Сводной таблицы:
    • На новый лист (New Worksheet)
    • На существующий лист (Existing Worksheet)
  • подтверждаем создание нажатием кнопки OK

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

  • выделил диапазон A1:B51 на листе Извлечение по критерию
  • вызвал меню вставки сводной таблицы: вкладка Вставка(Insert) -группа Таблица(Table)Сводная таблица(PivotTable)
    выбрал вставить на новый лист(New Worksheet)
  • назвал этот лист Уникальные сводной таблицей
  • поле Данные поместил в область строк
  • поле ФИО в область фильтра. Почему? Чтобы удобно было выбирать одно или несколько ФИО и в сводной отображался бы список уникальных месяцев только для выбранных фамилий
    Отбор уникальных сводной таблицей

В чем неудобство работы со сводными в данном случае: при изменении в исходных данных сводную таблицу придется обновлять вручную: Выделить любую ячейку сводной таблицы -Правая кнопка мыши —Обновить(Refresh) или вкладка Данные(Data)Обновить все(Refresh all)Обновить(Refresh). А если исходные данные пополняются динамически и того хуже — надо будет заново указывать диапазон исходных данных. И еще один минус — данные внутри сводной таблицы нельзя менять. Поэтому если с полученным списком необходимо будет работать в дальнейшем, то после создания нужного списка при помощи сводной его надо скопировать и вставить на нужный лист.

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


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

Скачать пример:

  Tips_All_ExtractUnique.xls (108,0 KiB, 18 431 скачиваний)

Также см.:
Работа с дубликатами
Как подсчитать количество повторений
Общие сведения о сводных таблицах


Статья помогла? Поделись ссылкой с друзьями!

  Плейлист   Видеоуроки


Поиск по меткам



Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще…Меньше

Функция УНИК возвращает список уникальных значений в списке или диапазоне. 

Возвращение уникальных значений из списка значений

Пример использования =УНИК(B2:B11) для возврата уникального списка чисел

Возвращение уникальных имен из списка имен

Применение функции УНИК для сортировки списка имен

=УНИК(массив,[by_col],[exactly_once])

Функция УНИК имеет следующие аргументы:

Аргумент

Описание

массив

Обязательный

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

[by_col]

Необязательный

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

Значение ИСТИНА сравнивает столбцы друг с другом и возвращает уникальные столбцы

Значение ЛОЖЬ (или отсутствующее значение) сравнивает строки друг с другом и возвращает уникальные строки

[exactly_once]

Необязательно

Аргумент exactly_once является логическим значением, которое возвращает строки или столбцы, встречающиеся в диапазоне или массиве только один раз. Это концепция базы данных УНИК.

Значение ИСТИНА возвращает из диапазона или массива все отдельные строки или столбцы, которые встречаются только один раз

Значение ЛОЖЬ (или отсутствующее значение) возвращает из диапазона или массива все отдельные строки или столбцы

Примечания: 

  • Массив может рассматриваться как строка или столбец со значениями либо комбинация строк и столбцов со значениями. В примерах выше массивы для наших формул УНИК являются диапазонами D2:D11 и D2:D17 соответственно.

  • Функция УНИК возвращает массив, который будет рассеиваться, если это будет конечным результатом формулы. Это означает, что Excel будет динамически создавать соответствующий по размеру диапазон массива при нажатии клавиши ВВОД. Если ваши вспомогательные данные хранятся в таблице Excel, тогда массив будет автоматически изменять размер при добавлении и удалении данных из диапазона массива, если вы используете Структурированные ссылки. Дополнительные сведения см. в статье Поведение рассеянного массива.

  • Приложение Excel ограничило поддержку динамических массивов в операциях между книгами, и этот сценарий поддерживается, только если открыты обе книги. Если закрыть исходную книгу, все связанные формулы динамического массива вернут ошибку #ССЫЛКА! после обновления.

Примеры

Пример 1

В этом примере СОРТ и УНИК используются совместно для возврата уникального списка имен в порядке возрастания.

Использование УНИК с СОРТ для возврата списка имен по возрастанию

Пример 2

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

Использование УНИК с аргументом occurs_once, для которого задано значение true, для возврата списка имен, которые встречаются только один раз.

Пример 3

В этом примере используется амперсанд (&) для сцепления фамилии и имени в полное имя. Обратите внимание, что формула ссылается на весь диапазон имен в массивах A2:A12 и B2:B12. Это позволяет Excel вернуть массив всех имен.

Использование УНИК с несколькими диапазонами для объединения столбцов имени и фамилии в столбец полного имени.

Советы: 

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

  • Чтобы отсортировать список имен, можно добавить функцию СОРТ: =СОРТ(УНИК(B2:B12&» «&A2:A12))

Пример 4

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

Использование УНИК для возврата списка продавцов.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Функция ФИЛЬТР

Функция СЛУЧМАССИВ

Функция ПОСЛЕДОВ

Функция СОРТ

Функция СОРТПО

Ошибки #SPILL! в Excel

Динамические массивы и поведение массива с переносом

Оператор неявного пересечения: @

Нужна дополнительная помощь?

Содержание

  1. Разные значения против уникальных значений
  2. Подсчет разных значений в сводной таблице Excel
  3. Добавление вспомогательного столбца в набор данных
  4. Подсчет количества определенного значения ячейки в Excel при условии
  5. Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel
  6. Способ 1: счетчик на строке состояния
  7. Способ 2: функция СЧЁТЗ
  8. Способ 3: функция СЧЁТ
  9. Как посчитать количество пустых и непустых ячеек?
  10. Нулевые строки.
  11. СЧЕТЕСЛИ с несколькими условиями.
  12. Количество чисел в диапазоне
  13. Количество ячеек с несколькими условиями ИЛИ.
  14. Считаем числа и значения
  15. Ставим экселю условия
  16. Разбираемся в критериях
  17. Задачи на логику
  18. Средние значения и множественные формулы
  19. Считаем данные в массиве с пустыми ячейками
  20. Счёт текстовых строк по условию

Разные значения против уникальных значений

Кажется, что это одно и то же, но это не так.

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

Разница между уникальным и разными значениями

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

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

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

Предположим, у вас есть данные о продажах:

С этим набором данных вам нужно найти ответ на следующие вопросы:

  • Сколько сотрудников в каждом регионе (а это не что иное, как количество разных сотрудников в каждом регионе)?
  • Сколько сотрудников продали принтер в 2019 году?

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

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

А если вы используете Excel 2010 или ранние версии, вам придется изменить исходные данные, добавив вспомогательный столбец.

В этой статье рассматриваются следующие методы:

  • Добавление вспомогательного столбца в исходный набор данных для подсчета разных значений (работает во всех версиях).
  • Добавление данных в модель данных и использование параметра «Число различных элементов» (доступно в Excel 2013 и последующих версиях).

Существует третий метод, он называет метод сводной таблицы в сводной таблице.

Добавление вспомогательного столбца в набор данных

Примечание. Если вы используете Excel 2013 и более поздние версии, пропустите этот метод и перейдите к следующему (вам доступна встроенная функция).

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

Хотя это простой обходной путь, у него есть некоторые недостатки (которые будут рассмотрены далее).

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

Предположим, у меня есть набор данных, как показано ниже:

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

= ЕСЛИ (СЧЁТЕСЛИМН ($C$2:C2; C2; $B$2:B2; B2) > 1;0;1)

Приведенная выше формула использует функцию СЧЁТЕСЛИМН для подсчета количества раз, когда имя появляется в данном регионе. Также обратите внимание на диапазоны критериев: $C$2:C2 и $B$2:B2. Это означает, что они продолжают расширяться, когда вы идете вниз по столбцу.

Например, в ячейке F2 диапазон критериев составляет $C$2:C2 и $B$2:B2, а в ячейке F3 эти диапазоны расширяются до $C$3:C3 и $B$3:B3.

Это гарантирует, что функция СЧЁТЕСЛИМН считает первый экземпляр имени как 1, второй экземпляр имени как 2 и так далее.

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

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

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

Ниже приведены шаги, как сделать это:

  • Выберите любую ячейку в таблице.
  • Нажмите вкладку «Вставка».
  • Нажмите на кнопку Сводная таблица.
  • В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно (и включает вспомогательный столбец), и выбран «На новый лист» в качестве места размещения.
  • Нажмите ОК.

Вышеуказанные шаги вставят новый лист со сводной таблицей.

Перетащите поле «Регион» в область «Строки» и поле «Помощник» в область «Значения».

Вы получите вот такую сводную таблицу:

Теперь вы можете изменить заголовок столбца с «Сумма по полю Помощник» на «Количество сотрудников».

Подсчет количества определенного значения ячейки в Excel при условии

Пример 2. По итогам сдачи экзаменов необходимо составить таблицу, в которой содержатся данные о количестве студентов, сдавших предмет на 5, 4, 3 балла соответственно, а также тех, кто не сдал предмет.

Вид исходной таблицы:

Предварительно выделим ячейки E2:E5, введем приведенную ниже формулу:

=СЧЁТЕСЛИ(B3:B19;D2:D5)

Описание аргументов:

  • B3:B19 – диапазон ячеек с оценками за экзамен;
  • D2:D5 – диапазон ячеек, содержащих критерии для подсчета числа совпадений.

В результате получим таблицу:

Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel

Пример 3. В таблице Excel хранятся данные о просмотрах страниц сайта за день пользователями. Определить число пользователей сайта за день, а также сколько раз за день на сайт заходили пользователи с логинами default и user_1.

Вид исходной таблицы:

Поскольку каждый пользователь имеет свой уникальный идентификатор в базе данных (Id), выполним расчет числа пользователей сайта за день по следующей формуле массива и для ее вычислений нажмем комбинацию клавиш Ctrl+Shift+Enter:

Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив дробных чисел 1/количество_вхождений, например, для пользователя с ником sam это значение равно 0,25 (4 вхождения). Общая сумма таких значений, вычисляемая функцией СУММ, соответствует количеству уникальных вхождений, то есть, числу пользователей на сайте. Полученное значение:

Для определения количества просмотренных страниц пользователями default и user_1 запишем формулу:

В результате расчета получим:

Способ 1: счетчик на строке состояния

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

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

Способ 2: функция СЧЁТЗ

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

  1. Выделяем область, в которую будет выводиться результат подсчета. Кликаем по значку «Вставить функцию».
  2. Открывается окно Мастера функций. Ищем в представленном списке элемент «СЧЁТЗ». После того, как это наименование выделено, жмем на кнопку «OK».
  3. Запускается окно аргументов. Аргументами этой функции являются ссылки на ячейки. Ссылку на диапазон можно прописать вручную, но лучше установить курсор в поле «Значение1», куда нужно ввести данные, и выделить соответствующую область на листе. Если нужно произвести подсчет заполненных ячеек в нескольких удаленных друг от друга диапазонах, то координаты второго, третьего и последующего диапазона нужно вводить в поля под названием «Значение2», «Значение3» и т.д. Когда все данные введены. Жмем на кнопку «OK».
  4. Данную функцию также можно ввести вручную в ячейку или строку формул, придерживаясь следующего синтаксиса:

    =СЧЁТЗ(значение1;значение2;…)

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

Способ 3: функция СЧЁТ

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

  1. Как и в предыдущем случае, выделяем ячейку, куда будут выводиться данные и таким же способом запускаем Мастер функций. В нём выбираем оператора с наименованием «СЧЁТ». Жмем на кнопку «OK».
  2. Запускается окно аргументов. Аргументы те же самые, что и при использовании предыдущего способа. В их роли выступают ссылки на ячейки. Вставляем координаты диапазонов на листе, в которых нужно подсчитать количество заполненных ячеек числовыми данными. Жмем кнопку «OK».

    Для ручного введения формулы придерживаемся следующего синтаксиса:

    =СЧЁТ(значение1;значение2;…)

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

Как посчитать количество пустых и непустых ячеек?

Посмотрим, как можно применить функцию СЧЕТЕСЛИ в Excel для подсчета количества пустых или непустых ячеек в указанном диапазоне.

Непустые.

В некоторых руководствах по работе с СЧЕТЕСЛИ вы можете встретить предложения для подсчета непустых ячеек, подобные этому:

СЧЕТЕСЛИ(диапазон;”*”)

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

Если вам нужно универсальное решение для подсчета всех непустых ячеек в указанном диапазоне, то введите:

СЧЕТЕСЛИ(диапазон;”<>” & “”)

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

Также непустые ячейки в диапазоне можно подсчитать:

=СЧЁТЗ(E2:E22).

Пустые.

Если вы хотите сосчитать пустые позиции в определенном диапазоне, вы должны придерживаться того же подхода – используйте в условиях символ подстановки для текстовых значений и параметр “” для подсчета всех пустых ячеек.

Считаем клетки, не содержащие текст:

СЧЕТЕСЛИ( диапазон; “<>” & “*”)

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

Для подсчета пустых клеток (все типы значений):

=СЧЁТЕСЛИ(E2:E22;””)

Конечно, для таких случаев есть и специальная функция

=СЧИТАТЬПУСТОТЫ(E2:E22)

Но не все знают о ее существовании. Но вы теперь в курсе …

Нулевые строки.

Также имейте в виду, что СЧЕТЕСЛИ и СЧИТАТЬПУСТОТЫ считают ячейки с пустыми строками, которые только на первый взгляд выглядят пустыми.

Что такое эти пустые строки? Они также часто возникают при импорте данных из других программ (например, 1С). Внешне в них ничего нет, но на самом деле это не так. Если попробовать найти такие “пустышки” (F5 -Выделить – Пустые ячейки) – они не определяются. Но фильтр данных при этом их видит как пустые и фильтрует как пустые.

Дело в том, что существует такое понятие, как «строка нулевой длины» (или «нулевая строка»). Нулевая строка возникает, когда программе нужно вставить какое-то значение, а вставить нечего.

Проблемы начинаются тогда, когда вы пытаетесь с ней произвести какие-то математические вычисления (вычитание, деление, умножение и т.д.). Получите сообщение об ошибке #ЗНАЧ!. При этом функции СУММ и СЧЕТ их игнорируют, как будто там находится текст. А внешне там его нет.

И самое интересное – если указать на нее мышкой и нажать Delete (или вкладка Главная – Редактирование – Очистить содержимое) – то она становится действительно пустой, и с ней начинают работать формулы и другие функции Excel без всяких ошибок.

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

=ЧСТРОК(E2:E22)*ЧИСЛСТОЛБ(E2:E22)-СЧЁТЕСЛИ(E2:E22;”<>”&””)

Откуда могут появиться нулевые строки в ячейках? Здесь может быть несколько вариантов:

  1. Он есть там изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе (вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет – они просто заполняются строкой нулевой длины.
  2. Была создана формула, результатом которой стал текст нулевой длины. Самый простой случай:

=ЕСЛИ(Е1=1;10;””)

В итоге, если в Е1 записано что угодно, отличное от 1, программа вернет строку нулевой длины. И если впоследствии формулу заменять значением (Специальная вставка – Значения), то получим нашу псевдо-пустую позицию.

Если вы проверяете какие-то условия при помощи функции ЕСЛИ и в дальнейшем планируете производить с результатами математические действия, то лучше вместо “” ставьте 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл -Параметры -Дополнительно – Показывать нули в позициях, которые содержат нулевые значения.

СЧЕТЕСЛИ с несколькими условиями.

На самом деле функция Эксель СЧЕТЕСЛИ не предназначена для расчета количества ячеек по нескольким условиям. В большинстве случаев я рекомендую использовать его множественный аналог – функцию СЧЕТЕСЛИМН. Она как раз и предназначена для вычисления количества ячеек, которые соответствуют двум или более условиям (логика И). Однако, некоторые задачи могут быть решены путем объединения двух или более функций СЧЕТЕСЛИ в одно выражение.

Количество чисел в диапазоне

Одним из наиболее распространенных применений функции СЧЕТЕСЛИ с двумя критериями является определение количества чисел в определенном интервале, т.е. меньше X, но больше Y.

Например, вы можете использовать для вычисления ячеек в диапазоне B2: B9, где значение больше 5 и меньше или равно 15:

=СЧЁТЕСЛИ(B2:B11;”>5″)-СЧЁТЕСЛИ(B2:B11;”>15″)

Количество ячеек с несколькими условиями ИЛИ.

Когда вы хотите найти количество нескольких различных элементов в диапазоне, добавьте 2 или более функций СЧЕТЕСЛИ в выражение. Предположим, у вас есть список покупок, и вы хотите узнать, сколько в нем безалкогольных напитков.

Сделаем это:

=СЧЁТЕСЛИ(A4:A13;”Лимонад”)+СЧЁТЕСЛИ(A2:A11;”*сок”)

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

Как вы понимаете, сюда можно добавить и больше условий.

Считаем числа и значения

Три полезных вещи, помогающих в работе с программой.

  1. Сколько чисел находится в массиве, можно рассчитать с помощью формулы СЧЁТ(значение1;значение2;…). Она учитывает только те элементы, которые включают в себя цифры.То есть если в некоторых из них будет прописан текст, они будут пропущены, в то время как даты и время берутся во внимание. В данной ситуации не обязательно задавать параметры по порядку: можно написать, к примеру, =СЧЁТ(А1:С3;В4:С7;…).
  2. Другая статистическая функция — СЧЕТЗ — подсчитает вам непустые клетки в диапазоне, то есть те, которые содержат буквы, числа, даты, время и даже логические значения ЛОЖЬ и ИСТИНА.
  3. Обратное действие выполняет формула, показывающая численность незаполненных секций — СЧИТАТЬПУСТОТЫ(массив). Она применяется только к непрерывным выделенным областям.

Ставим экселю условия

Когда нужно подсчитать элементы с определённым значением, то есть соответствующие какому-то формату, применяется функция СЧЁТЕСЛИ(массив;критерий). Чтобы вам было понятнее, следует разобраться в терминах.

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

Разбираемся в критериях

Примеры критериев:

  • «>0» — считаются ячейки с числами от нуля и выше;
  • «Товар» — подсчитываются секции, содержащие это слово;
  • 15 — вы получаете сумму элементов с данной цифрой.

Для большей ясности приведу развернутый пример.

Чтобы посчитать ячейки в зоне от А1 до С2, величина которых больше прописанной в А5, в строке формул необходимо написать =СЧЕТЕСЛИ(А1:С2;«>»&А5).

Задачи на логику

Хотите задать экселю логические параметры? Воспользуйтесь групповыми символами * и ?. Первый будет обозначать любое количество произвольных символов, а второй — только один.

К примеру, вам нужно знать, сколько имеет электронная таблица клеток с буквой Т без учета регистра. Задаем комбинацию =СЧЕТЕСЛИ(А1:D6;«Т*»). Другой пример: хотите знать численность ячеек, содержащих только 3 символа (любых) в том же диапазоне. Тогда пишем =СЧЕТЕСЛИ(А1:D6;«???»).

Средние значения и множественные формулы

В качестве условия может быть задана даже формула. Желаете узнать, сколько у вас секций, содержимое которых превышают среднее в определенном диапазоне? Тогда вам следует записать в строке формул следующую комбинацию =СЧЕТЕСЛИ(А1:Е4;«>»&СРЗНАЧ(А1:Е4)).

Если вам нужно сосчитать количество заполненных ячеек по двум и более параметрам, воспользуйтесь функцией СЧЕТЕСЛИМН. К примеру, вы ищите секций с данными больше 10, но меньше 70. Вы пишете =СЧЕТЕСЛИМН(А1:Е4;«>10»;А1:Е4;«<70»).

Помимо этого, у вас есть возможность задать условия И/ИЛИ. Только во втором случае придется использовать сразу несколько правил. Смотрим: вам необходимо найти клетки, в которых слова начинаются с буквы В или Р — пишете =СЧЕТЕСЛИ(А1:Е4;«В*»)+ СЧЕТЕСЛИ(А1:Е4;«Р*»).

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

Считаем данные в массиве с пустыми ячейками

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

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(диапазондиапазон &»»))

Легко заметить, что результат получился на 1 больше, чем в прошлом примере. Это потому, что формула посчитала и пустую ячейку, как уникальные данные. Чтобы этого не было, еще доработаем функцию:

=СУММПРОИЗВ((диапазон<>””)/СЧЁТЕСЛИ(диапазондиапазон &»»))

Теперь получили правильный результат без учёта пустот.

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

Счёт текстовых строк по условию

Похожее задание, но теперь нужно считать, сколько менеджеров продавали товар с кодом 1001.

А формула такая:

=СУММ(—(ЧАСТОТА(ЕСЛИ(диапазон<>»»;ЕСЛИ(условиедиапазон диапазондиапазон)-СТРОКА(диапазон.первая ячейка)+1)>0))

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

Источники

  • https://excelpedia.ru/bez-rubriki/podschet-raznih-znachenii-v-svodnoi-tablice-excel
  • https://exceltable.com/funkcii-excel/primery-funkcii-schetesli
  • https://lumpics.ru/how-count-number-of-filled-cells-in-excel/
  • https://mister-office.ru/funktsii-excel/function-countif.html
  • https://profi-user.ru/podschet-yacheek/
  • https://officelegko.com/2019/09/19/podschet-unikalnyh-znachenij-v-excel/

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

​Смотрите также​​ теряется права на​ находиться на любом​ (можно и без​ «вариант с формулами»​: а сводная таблица​ (из столбцов) -​ меню​:​:​ 5 в диапазоне​ удобства сделан выпадающий​Форматировать только уникальные или​стиль​Снять выделение всех​ на листе и​ мере одна строка​Примечание:​ редактирования результата =(​ листе.​ поп. столбца, только​ или чтобы подсчёт​

​ не подойдет под​ неудается применить формулу.​Вставка — Имя -​Тогда дальнейшая задача будет​

  • ​В открывшемся окне нужно​A1:A6​​ список), формула пересчитает​ ​ повторяющиеся значения​щелкните маленькую стрелку​​и выберите в разделе​
    Группа ''Сортировка и фильтр'' на вкладке ''Данные''

  • ​ нажмите кнопку​ идентичны всех значений​​Мы стараемся как​может кто поможет​ ​верхняя левая ячейка​ ​ формула увеличится в​​ вёлся «динамически»?​
    Удаление дубликатов

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

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

​Условное форматирование​столбцы​Развернуть​ в другую строку.​ можно оперативнее обеспечивать​ разобраться, как макрос​ списка должна иметь​ размере раза в​это таки не​Czeslav​Код {=ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1))} Описание:​(Insert — Name -​ дубликатов уже в​ задать те столбцы,​=COUNTIF(A1:A6,5)​

​Аналогичным образом можно решить​В списке​и затем щелкните​выберите столбцы.​.​ Сравнение повторяющихся значений​ вас актуальными справочными​ сделать на много​ имя «результат».​ три, и считать​ одно и то​: Промучился полтора дня​список с данными​ Define)​ одном столбце.​ по которым необходимо​=СЧЁТЕСЛИ(A1:A6;5)​ задачу с двумя​Формат все​Элемент правила выделения ячеек​Примечание:​

​Установите флажок​ зависит от того,​​ материалами на вашем​ диапазонов, вот, ято​Michael_S​ она будет медленнее)​ же.​Прикладываю файл.​ у Вас расположен​:​Чуть более сложный способ,​ обеспечивать уникальность. Т.е.​

Фильтрация уникальных значений

​Чтобы подсчитать уникальные значения,​

  1. ​ условиями.​Измените описание правила​и выберите​ Данные будут удалены из​

  2. ​только уникальные записи​​ что отображается в​​ языке. Эта страница​​ я сделал, но​​: Я бы для​​Формула получилась нечувствительна​​DStuart​

    Группа ''Сортировка и фильтр'' на вкладке ''Данные''

  3. ​Vlad999​​ в столбце А(А1:А51,​​диапазону номеров (A1:A100) -​ чем первые два,​

​ если включить все​ добавим функцию​=СУММПРОИЗВ(((A14:A23=A8)*(B14:B23=B8))/СЧЁТЕСЛИМН(A14:A23;A14:A23;B14:B23;B14:B23;C14:C23;C14:C23))​

  • ​выберите​​Повторяющиеся значения​​ всех столбцов, даже​

​, а затем нажмите​ ячейке, не базового​

  • ​ переведена автоматически, поэтому​​ не работает =((((​​ себя тоже предпочел​

  • ​ к регистру​​: По поводу формул​​: Ещё один вариант.​ где А1 –​

  • ​ имя​​ но зато -​ Изображение кнопки​ флажки, то будут​SUM​В этом случае будут​уникальные​​.​ Изображение кнопки​ если вы не​

  • ​кнопку ОК​​ значения, хранящегося в​​ ее текст может​​ HELP!!!!​​ макрос… но по​

​Michael_S​ я имел ввиду​Уникальные значения вытягиваются​

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

​ заголовок). Выводить список​NameCount​ динамический, т.е. с​ удалены только полностью​(СУММ), 1/, и​ подсчитаны уникальные товары​или​Введите значения, которые вы​ выбрали всех столбцов​.​ ячейке. Например, если​ содержать неточности и​200?’200px’:»+(this.scrollHeight+5)+’px’);»>​ заявке ТС -​

​: и офис 2003?​ без макросов, т.к​ по алфавиту, только​ мы будем в​всему списку с номерами​ автоматическим пересчетом, т.е.​ совпадающие строки. Если​

​ заменим 5 на​

  1. ​ только в строках,​повторяющиеся​ хотите использовать и​ на этом этапе.​

  2. ​Уникальные значения из диапазона​​ у вас есть​​ грамматические ошибки. Для​​Private Sub Worksheet_Change(ByVal​​ формулы.​​ (впрочем, не особо​​ я в них​

    Удаление дубликатов

  3. ​ надо обратить внимание,​ столбец С, начиная​

    • ​ (A1:B100) — имя​​ если список редактируется​​ включить только флажок​A1:A6​

    • ​ для которых Продавец​.​​ нажмите кнопку Формат.​​ Например при выборе​

    • ​ скопирует на новое​ то же значение​​ нас важно, чтобы​​ Target As Range)​

      ​Vyacheslav​ важно)… тяжеловато для​ не силен. Я​ что для первой​ с ячейки С2.​NameList​​ или в него​​ заказчик, то останется​​.​​ и Месяц совпадают​

      ​Нажмите кнопку​​Расширенное форматирование​ Столбец1 и Столбец2,​ место.​ даты в разных​ эта статья была​Dim d As​: ikki, Отличный макрос!​ формулы будет, может​ хочу понимать что​ строки одна формула,​ Формула в C2​Теперь осталось выбрать из​ дописываются еще элементы,​ только по одной​=SUM(1/COUNTIF(A1:A6,A1:A6))​ с критериями, установленными​Формат​Выполните следующие действия.​

  4. ​ но не Столбец3​​При удалении повторяющихся значений​​ ячейках, один в​ вам полезна. Просим​ Object, arr, el,​А как сделать,​ памяти не хватить…​ я делаю)​​ а для последующих-другая.​​ будет следующая:​ списка​

  5. ​ то они автоматически​ строке для каждого​=СУММ(1/СЧЁТЕСЛИ(A1:A6;A1:A6))​ в желтых ячейках.​

Удаление дубликатов с промежуточными итогами или структурированных данных проблем

​для отображения во​Выделите одну или несколько​ используется для поиска​ на значения в​ формате «3/8/2006», а​ вас уделить пару​ n&​ если необходимо применить​Michael_S​По поводу «динамически»​trash50​Код {=ИНДЕКС($A$2:$A$51;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$51)=0;СТРОКА($A$1:$A$50));1))} вводится​

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

​NameList​​ проверяются на уникальность​ заказчика и т.д.​Закончим ввод формулы нажатием​В файле примера на​ всплывающем окне​

​ ячеек в диапазоне,​

​ дубликатов «ключ» —​

  1. ​ диапазоне ячеек или​ другой — как​ секунд и сообщить,​Dim f As​

  2. ​ этот макрос на​​: С помощью доп.​​ я имею ввиду​​:​​ формула в ячейку​​все элементы имеющие​​ и отбираются. В​​Если у вас Excel​​Ctrl+Shift+Enter​​ листе Два критерия​​Формат ячеек​
    Повторяющиеся значения

  3. ​ таблице или отчете​ значение ОБА Столбец1​ таблице — единственный​

​ «8 мар «2006​

​ помогла ли она​

  1. ​ Object, arw, em,​ несколько диапазонов и​ таблицы, по размеру​ если меняется изменяется​

  2. ​trash50​​ сочетанием клавиш –​​ номер — это​​ предыдущих способах при​​ 2003 или старше,​​.​​ приведено решение этой​​.​​ сводной таблицы.​ & Столбец2.  Если дубликат​​ эффект. Другие значения​​ г. значения должны​
    Меню

  3. ​ вам, с помощью​ k&​

    • ​ каждый диапазон выводить​ равной исходной. Регистр​​ содержимое массива, то​​, что то я​ Ctrl+Shift+Enter. После того,​​ и будут наши​​ изменении исходного списка​

    • ​ то для удаления​Примечание:​ задачи с помощью​​Выберите номер, шрифт, границы​​На вкладке​ находится в этих​ вне диапазона ячеек​ быть уникальными.​​ кнопок внизу страницы.​ Изображение кнопки​If Not Intersect(Target,​​ отдельно?​​ различает. Для проверки​ итоги подсчитываются автоматически​ не пойму что​ как мы ввели​ уникальные представители. Сделать​ нужно будет заново​Изображение кнопки​ дубликатов и вытаскивания​Строка формул указывает,​​ Сводной таблицы. В​​ и заливка формат,​Главная​​ столбцах, затем всей​​ или таблице не​

  4. ​Установите флажок перед удалением​​ Для удобства также​​ [диапазон1]) Is Nothing​​ikki​ -​​ (кол-во совпадений в​

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

  6. ​в группе​​ строки будут удалены,​​ будет изменить или​ дубликаты:​​ приводим ссылку на​​ Then Exit Sub​

  7. ​: например?​F9​ массиве), а не​ вашей формуле диапазон​ C2​ любой пустой ячейке​​Расширенный фильтр​​ (неповторяющихся) элементов можно​ массива, заключая её​ не количество уникальных,​ если значение в​Стили​​ включая другие столбцы​​ переместить.  При удалении​

support.office.com

Подсчет Уникальных значений с условиями в MS EXCEL

​Перед удалением повторяющиеся​ оригинал (на английском​Set d =​

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

Задача1

​щелкните стрелку для​ в таблицу или​ повторяющихся данных, хранящихся​

​ значения, рекомендуется установить​ языке) .​ CreateObject(«scripting.dictionary»)​: есть «диапазон1» и​: То что надо.​ например через «сводную​ на одном листе​ и вставить​

​ туда вот такую​ кнопку​Расширенный фильтр (Advanced Filter) ​ {}. Их не​ товаров.​

​ и нажмите кнопку​​Условного форматирования​​ диапазон.​ в первое значение​ для первой попытке​В Excel существует несколько​d.CompareMode = 0​

Задача2

​ «диапазон2» и нужно​ Работает!!! Всем большое​ таблицу».​

​ большой, а проверку​

​в несколько строк​ формулу с известной​Удаление дубликатов.​из меню (вкладки)​ нужно вводить самостоятельно.​Теперь рассмотрим другую таблицу​ОК​

​и выберите пункт​Нажмите кнопку​ в списке, но​ выполнить фильтрацию по​ способов фильтр уникальных​arr = [диапазон1].Value​ вывести «результат1» и«результат2».​ спасибо!​Michael_S​

Задача3

​ делаете по диапазону​ так, чтобы точно​ функцией ВПР (VLOOKUP) и​

​Итак, снова имеем список​Данные (Data)​ Они исчезнут, когда​

​ (столбцы А:С на​. Вы можете выбрать​Управление правилами​ОК​ других идентичных значений​ — или применить​ значений — или​For Each el​

​Как сложить структуру​ikki​: ваш вариант данных,​ намного меньше указанного​ отобразить все уникальные​ скопировав ее вниз​ беспорядочно повторяющихся элементов.​.​ вы начнете редактировать​ рисунке ниже).​ более одного формата.​, чтобы открыть​, и появится сообщение,​ удаляются.​

​ условное форматирование на​ удаление повторяющихся значений:​ In arr​ макроса, если у​

​: каюсь — был​ даже если попросите​

excel2.ru

Подсчёт уникальных значений в Excel

​ в первом аргументе.​ элементы…​ на весь столбец:​ Например, такой:​

  1. ​Предположим, что у нас​​ формулу.​​Необходимо вывести количество заключенных​ Форматы, которые можно​ всплывающее окно​​ чтобы указать, сколько​​Поскольку данные будут удалены​

    ​ — для подтверждения​
    ​Чтобы фильтр уникальных значений,​

    Подсчёт уникальных значений в Excel

  2. ​If Not IsEmpty(el)​ меня диапазонов может​​ неправ. макрос может​​ — в сводную​Vlad999​​Ссылка: (Способ 2.​​=ЕСЛИ(МАКС(NameCount)​

    ​Первая задача - пронумеровать​
    ​ имеется вот такой​

    Подсчёт уникальных значений в Excel

  3. ​Пояснение:​​ договоров в каждом​​ выбрать, отображаются на​

    Подсчёт уникальных значений в Excel

​Диспетчер правил условного форматирования​​ повторяющиеся значения были​ окончательно, перед удалением​ добиться таких результатов,​ нажмите кнопку​ Then d.Item(el) =​ быть больше 2х​ понадобиться к.-нибудь другому.​ не загнать…​: это ошибочно.​

​ Формулы)​

  • ​или в английской версии​ всех уникальных представителей​​ список беспорядочно повторяющихся​​Диапазон (массив констант), созданный​ месяце.​ панели​.​
  • ​ удалены или остаются​ повторяющихся значений рекомендуется​ предполагается, что уникальные​данных >​ d.Item(el) + 1​ и результат нужно​выкладываю (хотя он​Доп. столбцы тоже,​- проверка должна​
  • ​Немогу применить данную​ Excel:​
  • ​ списка, дав каждому​ названий компаний:​ с помощью функции​​Т.к. в таблице ведется​​предварительного просмотра​Выполните одно из действий,​

​ количества уникальных значений.​ скопировать исходный диапазон​
​ значения.​
​Сортировка и фильтр >​

​Next​

office-guru.ru

Извлечение уникальных элементов из диапазона

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

​ выводить на каждый​ очень простой):​ как и макросы​ осуществлятся по всему​ формулу.​​=IF(MAX(NameCount)​ свой номер (столбец​Выбираем в меню​​COUNTIF​

Поиск уникальных значений вȎxcel формула

​ учет счетов сразу​.​ указанных ниже.​ Нажмите кнопку​ ячеек или таблицу​Выполните следующие действия.​ Дополнительно​n = Application.Min(Rows.Count​ диапазон​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Private Sub Worksheet_Change(ByVal Target​ — не устраивают?​ большому диапазону, но​(P.S. времени и​Эта формула проходит сверху​

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

​ А на рисунке).​Данные — Фильтр -​(СЧЁТЕСЛИ), хранится в​ для всех договоров​Подсчитаем Уникальные значения с​Чтобы добавить условное форматирование,​ОК​​ в другой лист​​Выделите диапазон ячеек или​​.​​ — [результат1].Rows(1).Row +​

​Hugo​ As Range)​ Диапазон в реале​ уменя так не​

Поиск уникальных значений вȎxcel формула

​ методов ‘копирования’ (/’вставки’)​​ вниз по столбцу​ Для этого вставляем​​ Расширенный фильтр​ памяти Excel, а​​ и по каждому​​ одним и двумя​

Поиск уникальных значений вȎxcel формула

​ нажмите кнопку​

  • ​, чтобы закрыть​ или книгу.​​ убедитесь, что активная​​Чтобы удалить повторяющиеся значения,​
  • ​ 1, UBound(arr) *​​: Я бы вместо​Dim d As​ какой?​​ работает.​ потратил недопустимо много).​
  • ​NameCount​​ в ячейку А2​​(Data — Filter -​​ не в ячейках​​ договору может быть​​ условиями.​

​Создать правило​

Поиск уникальных значений вȎxcel формула

​ сообщение.​Выполните следующие действия.​ ячейка находится в​ нажмите кнопку​ UBound(arr, 2))​ макроса на событие​ Object, arr, el,​ikki​опишите словами что​​Прошу Вашей помощи.​​и выводит все​

Поиск уникальных значений вȎxcel формула

​ и копируем затем​ Advanced Filter)​ листа.​ выставлено несколько счетов,​

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

​Про подсчет уникальных текстовых​для отображения во​U тменить отменить изменения,​Выделите диапазон ячеек или​ таблице.​данные > Работа с​Application.ScreenUpdating = False​ применял массивную UDF.​ i&, j&, n&​: на всякий случай​ с чем нужно​AleksSid​ позиции списка с​ вниз до упора​.​​Массив констант выглядит следующим​​ то номера договоров​ и числовых значений​​ всплывающем окне​

​ щелкните (или нажать​ убедитесь, что активная​Нажмите кнопку​

Поиск уникальных значений вȎxcel формула

​ данными​With [результат1].Resize(n, 2)​ Алгоритм на словаре,​If Intersect(Target, [диапазон])​ — макрос готов​ сравнивать и от​: Я думаю будут​ номерами в отдельную​ следующую формулу:​Получаем окно:​

​ образом: {3;1;1;1;3;3}, что​

​ могут повторяться.​ (без условий) можно​

​Создание правила форматирования​

​ клавиши Ctrl +​ ячейка находится в​данные > Дополнительно​>​.ClearContents​ как у​ Is Nothing Then​ (запускается не по​ куда выводить список​ и другие варианты.​

​ таблицу:​=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»»)​В нем:​ обозначает: три числа​Создадим дополнительный столбец для​ прочитать в статье Подсчет​.​ Z на клавиатуре).​ таблице.​​(​Удалить повторения​.Columns(1).Resize(d.Count) = Application.Transpose(d.Keys)​​ikki​ Exit Sub​ кнопке, а автоматически​​ уникальных согласно вашего​ Код =ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;НАИБОЛЬШИЙ(ЕСЛИ(ПОИСКПОЗ($B$2:$B$16;$B$2:$B$16;0)=СТРОКА($A$1:$A$15);СТРОКА($A$1:$A$15));СТРОКА(A1)));»»)​ ​Ktulu​В английской версии это​​Выделяем наш список компаний​

  • ​ 7, одно значение​ определения месяца заключения​​ Уникальных ТЕКСТовых значений​
  • ​Убедитесь, что выбран соответствующий​Нельзя удалить повторяющиеся значения​​На вкладке​

​в​.​​.Columns(2).Resize(d.Count) = Application.Transpose(d.Items)​​.​Set d =​ при изменении данных),​ файла.​Vlad999​: Добрый день, уважаемые​ будет:​ в​ «sun», одно значение​ договора (см. статью​ в MS EXCEL и Подсчет​ лист или таблица​

​ из структуры данных,​

​данные​группа​

​Чтобы выделить уникальные или​

​End With​Правда в исходной​​ CreateObject(«scripting.dictionary»)​​ но выкладывать пока​DStuart​:​ коллеги!​

Поиск уникальных значений вȎxcel формула

planetaexcel.ru

Сумма уникальных значений в диапазоне (Формулы/Formulas)

​=IF(COUNTIF(B$1:B2;B2)=1;MAX(A$1:A1)+1;»»)​​Исходный диапазон (List Range)​ «moon», одно число​
​ Название месяца прописью​ Уникальных ЧИСЛОвых значений​ в списке​
​ структурированный или, в​нажмите кнопку​
​Сортировка и фильтр​
​ повторяющиеся значения, команда​

​If Not Intersect(Target,​​ задаче пришлось бы​​d.CompareMode = 0​​ не буду.​

​: Добрый день! Помогите​​trash50​
​Прошерстил поиск, но​
​Эта формула проверяет сколько​.​
​ 5, три числа​
​ в MS EXCEL).​ в MS EXCEL.​
​Показать правила форматирования для​

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

excelworld.ru

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

​).​​Условного форматирования​ [диапазон2]) Is Nothing​ забивать её на​arr = [диапазон].Value​
​пс. «двумерный», «чтобы»,​
​ пожалуйста автоматизировать процесс​
​, ее надо просто​ точного ответа так​ раз текущее наименование​Ставим переключатель в положение​ 7, три числа​ Выведем из этого​ В этой статье​изменения условного форматирования,​ итоги. Чтобы удалить​(в группе​
​В поле всплывающего окна​в группе​ Then Exit Sub​ весь лист (одну),​For Each el​ «в виду»… что​ выборки и подсчета​​ растянуть, если копировать/вставить​ и не нашёл.​​ уже встречалось в​Скопировать результат в другое​ 7.​ столбца только уникальные​
​ рассмотрим более сложные​ начинается. При необходимости​
​ дубликаты, необходимо удалить​Работа с данными​
​Расширенный фильтр​стиль​Set f =​
​ и при худшем​

​ In arr​​ там ещё у​ количества уникальных значений​ — тогда каждый​

​Нужно просуммировать уникальные​​ списке (считая с​​ место (Copy to​​Все это сводится к​ месяцы (см. статью​ варианты с условиями.​ выберите другой диапазон​ структуры и промежуточные​
​).​выполните одно из​

​на вкладке «​​ CreateObject(«scripting.dictionary»)​​ раскладе места на​​If Not IsEmpty(el)​ вас в загашнике?​ из двухмерного массива.​
​ раз вводить как​ значения в диапазоне​ начала), и если​ another location)​ {1/3;1/1;1/1;1/1;1/3;1/3}.​ Отбор уникальных значений​Пусть имеется таблица с​ ячеек, нажав кнопку​ итоги. Для получения​Выполните одно или несколько​

​ указанных ниже действий.​​Главная​f.CompareMode = 0​ заголовки нет -​

​ Then d.Item(el) =​​DStuart​
​Интересует вариант с​

​ формулу массива.​​Для 2003 Экселя.​
​ это количество =1,​и указываем пустую​Этот массив констант используется​ (убираем повторы из​ перечнем продаж по​Свернуть​

​ дополнительных сведений отображается​​ следующих действий.​​Чтобы отфильтровать диапазон ячеек​​».​arr = [диапазон2].Value​ т.е. если забить​ d.Item(el) + 1​: Доб столбцы как​ формулами. Нужно, что​Приложите файл, в​Заранее спасибо​ т.е. элемент встретился​ ячейку.​

​ в качестве аргумента​​ списка) в MS​
​ продавцам.​во всплывающем окне​ Структура списка данных​В разделе​ или таблицы в​

​Фильтр уникальных значений и​For Each em​ под заголовки, то​Next​ вариант можно (потом​ бы подсчет велся​

CyberForum.ru

Поиск и подсчет количества уникальных значений в массиве (Поиск и подсчет количества уникальных значений в массиве)

​ котором не получается.​​buchlotnik​ первый раз -​Включаем (самое главное!) флажок​ для функции​ EXCEL) и поместим​
​Требуется подсчитать сколько различных​относится к​ на листе «и»​столбцы​ программе:​ удаление повторяющихся значений​ In arw​ при всех уникальных​n = Application.Min(Rows.Count​ спрячу), макросы в​ динамически по мере​
​snipe​

​: можно так (массивка):​​ дает ему последовательно​Только уникальные записи​SUM​ их в столбец​

​ товаров продал конкретный​​временно скрыть ее.​
​ удалить промежуточные итоги.​выберите один или несколько​Выберите​ являются две сходные​
​If Not IsEmpty(em)​ будет ошибка… Но​ — [результат].Rows(1).Row +​

​ крайнем случае.​​ изменения содержимого массива,​:​=СУММ(ЕСЛИ((C4:C33)>0;C4:C33/СЧЁТЕСЛИ(C4:C33;C4:C33);0))​ возрастающий номер.​(Uniqe records only)​(СУММ), давая результат​ F.​
​ продавец. Например, Вася​ Выберите новый диапазон​Примечание:​ столбцов.​фильтровать список на месте​ задачи, поскольку цель​ Then f.Item(em) =​ ошибка будет и​ 1, UBound(arr) *​Диапазон 256×256 ячеек.​

​ а не через​​trash50​чуть короче можноКод=СУММ(ЕСЛИ(C4:C33;C4:C33/СЧЁТЕСЛИ(C4:C33;C4:C33);))​Для упрощения адресации дадим​и жмем​
​ 4.​И, наконец, помощью формулы =СУММПРОИЗВ(($D$9:$D$26=F9)/СЧЁТЕСЛИ($A$9:$A$26;$A$9:$A$26))​ продал 1 товар1,​ ячеек на листе,​ Условное форматирование полей в​

​Чтобы быстро выделить все​​.​ — для представления​ f.Item(em) + 1​ в этом макросе​ UBound(arr, 2))​ Могу собственно приложить​ интерфейс сводной таблицы.​
​, если вдруг не​vikttur​ нашим диапазонам (например,​ОК​wink

​Урок подготовлен для Вас​​ подсчитаем количество уникальных​ 2 Товара2 и​ а затем разверните​ области «Значения» отчета​
​ столбцы, нажмите кнопку​Чтобы скопировать в другое​ списка уникальных значений.​Next​ при таком раскладе.​Application.ScreenUpdating = False​

​ вырезку того, что​​ Примерно в каком​ знаете что это​: Форула массива:​ исходя из того,​.​

​ командой сайта office-guru.ru​​ договоров в соответствующем​ 1 Товар4 (выделено​ узел во всплывающем​ сводной таблицы по​Выделить все​ место результаты фильтрации:​ Есть важные различия,​k = Application.Min(Rows.Count​
​Но если диапазоны​With [результат].Resize(n, 2)​

​ нужно свести. Это​​ виде мне это​ такое:​=СУММ(ЕСЛИОШИБКА(C4:C33/СЧЁТЕСЛИ(C4:C33;C4:C33);))​ что в списке​Получите список без дубликатов:​

​Источник: http://www.excel-easy.com/examples/count-unique-values.html​​ месяце.​ зеленым). Всего 3​ окне еще раз​ уникальным или повторяющимся​.​​Нажмите кнопку​

​ однако: при фильтрации​​ — [результат2].Rows(1).Row +​ поменьше, то вполне​.ClearContents​

​ расчет решетки Пеннета​​ нужно см.в приложенном​при наведении курсора​Как обычная, должна​
​ может быть до​Если требуется искать дубликаты​
​Перевела: Ольга Гелих​Решение также возможно с​
​ разных товара.​. Выберите правило​ значениям невозможно.​
​Чтобы быстро удалить все​Копировать в другое место​ уникальных значений повторяющиеся​
​ 1, UBound(arw) *​ применимо. Сколько диапазонов​
​.Columns(1).Resize(d.Count) = Application.Transpose(d.Keys)​
​ (расчет генотипов).​
​ файле.​ на правый нижний​
​ быть шустрее:​ 100 элементов) имена.​ не по одному,​
​Автор: Антон Андронов​
​ помощью Сводной таблицы.​Это можно подсчитать формулой =СУММПРОИЗВ((A13:A21=A7)/СЧЁТЕСЛИМН(B13:B21;B13:B21;A13:A21;A13:A21)),​ и нажмите кнопку​Быстрое форматирование​
​ столбцы, нажмите кнопку​
​.​
​ значения будут видны​
​ UBound(arw, 2))​
​ - столько применяем​
​.Columns(2).Resize(d.Count) = Application.Transpose(d.Items)​
​DStuart​

​За ранее спасибо!​ угол ячейки курсор​Код=СУММПРОИЗВ(—(ПОИСКПОЗ(C4:C33;—C4:C33;)=СТРОКА(C4:C33)-3);C4:C33)​ Это можно сделать​ а по нескольким​
​Начиная с 2007-й версии​Этот пример показывает, как​ которая будет работать​
​Изменить правило​Выполните следующие действия.​Снять выделение​

​В поле​​ только временно. Тем​Application.ScreenUpdating = False​ UDF.​End With​: Во вложении вырезка​

​DStuart​​ сменит вид на​
​Она же, формула​ в новых версиях​ столбцам, то можно​ функция удаления дубликатов​ создать формулу массива,​ только с версии​

​, чтобы открыть​​Выделите одну или несколько​

​.​​Копировать​ не менее удаление​With [результат2].Resize(k, 2)​
​Vyacheslav​End Sub​ из того, где​: Да, забыл сказать​ крестик, жмем ЛКМ​ массива:​ Excel на вкладке​

​ предварительно склеить их​​ является стандартной -​ которая подсчитает уникальные​ MS EXCEL 2007​ всплывающее окно​ ячеек в диапазоне,​​Если диапазон ячеек или​​введите ссылку на​
​ повторяющихся значений означает,​.ClearContents​: Hugo, Мне нравится​макрос должен быть​ нужно проводить выборку.​ для меня важно,​ (левую кнопку мыши)​Код=СУММ(ЕСЛИ(ПОИСКПОЗ(C4:C33;—C4:C33;)=СТРОКА(C4:C33)-3;C4:C33))​Формулы — Диспетчер имен​ в один, сделав,​ найти ее можно​ значения.​ из-за функции СЧЁТЕСЛИМН().​Изменение правила форматирования​
​ таблице или отчете​ таблица содержит много​ ячейку.​ что вы окончательное​.Columns(1).Resize(f.Count) = Application.Transpose(f.Keys)​

​ эта идея! сейчас​​ размещен в модуле​ Комбинации могут быть​ что бы процесс​

​ и не отпуская​​Ktulu​ (Formulas — Name​ своего рода, составной​ на вкладке​
​Воспользуемся функцией​Изменив в ячейке​.​ сводной таблицы.​ столбцов, чтобы выбрать​Кроме того нажмите кнопку​ удаление повторяющихся значений.​
​.Columns(2).Resize(f.Count) = Application.Transpose(f.Items)​
​ буду пробовать​ листа (того, где​
​ сколь угодно разными.​ выборки учитывал регистр.​ её тянем вниз​
​: Большое спасибо!​ manager)​ ключ с помощью​
​Данные - Удаление дубликатов​COUNTIF​А7​
​В разделе​На вкладке​
​ несколько столбцов только​
​Свернуть диалоговое окно​
​Повторяющееся значение входит в​End With​
​Vyacheslav​ находится исх. диапазон​MCH​
​ikki​
​ или в нужном​trash50​или в старых​ функции​
​ (Data - Remove​
​(СЧЁТЕСЛИ). Для примера​
​имя продавца (в​
​выберите тип правила​
​Главная​
​ может проще нажмите​
​временно скрыть всплывающее​ котором все значения​End Sub​
​: Хорошо конечно применять​ с именем "диапазон")​
​: Вариант формульного решения​
​: давайте уточним?​
​ вам направлении.​: Получить список уникальных​
​ версиях - через​СЦЕПИТЬ (CONCATENATE)​ Duplicates)​
​ подсчитаем количество чисел​
​ файле примера для​нажмите кнопку​в группе​ кнопку​
​ окно, выберите ячейку​
​ в по крайней​
​Оформляйте коды тегами!​
​ массивную UDF, но​
​результирующий список может​
​ с доп. столбцом​
​вас интересует именно​

​trash50​

excelworld.ru

​ (не повторяющихся) значений​

  • Редакция Кодкампа

17 авг. 2022 г.
читать 2 мин


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

=INDIRECT(TEXT(MIN(IF(( $A$2:$C$11 <>"")*(COUNTIF( $E$1:E1 , $A$2:$C$11 )=0),ROW( $2:$11 )*100+COLUMN( $A:$C ),7^8)),"R0C00"),)&""

Эта конкретная формула находит уникальные значения в диапазоне ячеек A2:C11 и возвращает список уникальных значений в столбце E .

В следующем примере показано, как использовать эту формулу на практике.

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

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

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

=INDIRECT(TEXT(MIN(IF(( $A$2:$C$11 <>"")*(COUNTIF( $E$1:E1 , $A$2:$C$11 )=0),ROW( $2:$11 )*100+COLUMN( $A:$C ),7^8)),"R0C00"),)&""

Мы можем ввести эту формулу в ячейку E2 и нажать Ctrl+Shift+Enter :

Затем мы можем щелкнуть и перетащить эту формулу вниз в другие ячейки в столбце E, пока не начнут появляться пустые значения:

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

Из вывода мы видим, что в трех столбцах названий команд есть 11 уникальных названий команд.

Примечание.Чтобы использовать эту формулу с большим количеством столбцов, просто измените диапазон ячеек A2:C11 в формуле, чтобы включить больше столбцов.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:

Как подсчитать количество вхождений в Excel
Как подсчитать частоту текста в Excel
Как рассчитать относительную частоту в Excel

Написано

Редакция Кодкампа

Замечательно! Вы успешно подписались.

Добро пожаловать обратно! Вы успешно вошли

Вы успешно подписались на кодкамп.

Срок действия вашей ссылки истек.

Ура! Проверьте свою электронную почту на наличие волшебной ссылки для входа.

Успех! Ваша платежная информация обновлена.

Ваша платежная информация не была обновлена.

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

А вот еще интересные статьи:

  • Как определить уникальные значения в excel
  • Как определить угол наклона графика в excel
  • Как определить у кого открыт файл excel
  • Как определить точку пересечения графиков в excel
  • Как определить топ в excel

  • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии