Excel счет только уникальные значения

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

Постановка задачи

Есть диапазон с данными, в котором некоторые значения повторяются больше одного раза:

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

Рассмотрим несколько способов ее решения.

Способ 1. Если нет пустых ячеек

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

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

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

Потом вычисляются дроби 1/Число вхождений для каждого элемента и все они суммируются, что и даст нам количество уникальных элементов:

Способ 2. Если есть пустые ячейки

Если в диапазоне встречаются пустые ячейки, то придется немного усовершенствовать формулу, добавив проверку на пустые ячейки (иначе получим ошибку деления на 0 в дроби):

Вот и все дела.

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

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

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

​Смотрите также​ и 3 столбцов,​ формируется сводная таблица,​Использовал словарь коллекций​

1. ​ СВОД_УСЛ = USL()​​ выводится​​ — 1) Select​ определенному требованию. Желательно​ эта сработала {=СУММ(ЕСЛИ(ЧАСТОТА(A:A;A:A)>0;1;0))}​​3401​​ было в посте​

   ​: Для тех, кто создает​
​B5​

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

   ​ Public Function СЧЕТ_УСЛ(Диапазон​
​ End Select End​

   

3. ​массив на листе​​ Case metod Case​​ решить задачу стандартными​

   

​ — разве не​​3402​ писать.​ список уникальных значений​);​CTRL + SHIFT +​5. В зависимости от​ меньше или равна​ из двух столбцов:​ создать формулу массива,​

​ ввести​

• ​ будет каждая из​ As Range, Условие​​ Function​​, когда я использую​ КОЛИЧЕСТВО_УНИ СВОД_УСЛ =​ средствами эксель (так​ по уникальным счет​
• ​3402​Юрий М​ для того, чтобы​ИНДЕКС() – восстанавливает значение​ ENTER​ значения Переключателя, уникальные​ Конечной дате (ячейка​ Даты продаж и​ которая подсчитает уникальные​
• ​=MyFunc()​ 282 введенных, тут​
• ​ As Range, Диапазон_условия​Добавлено через 5 минут​ метод в функции​​ UBound(USL) Case МАССИВ_УНИ​​ как оно может​ идет?!. -10536-​

​3501​: Серж, про уникальность​
​ в дальнейшем сформировать​
​ по его позиции​

​. Затем нужно скопировать​

office-guru.ru

Подсчет Уникальных и Неповторяющихся ТЕКСТовых значений в заданном интервале в MS EXCEL

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

​ As Range) As​Используя параметр «МАССИВ_УНИ»​ «МАССИВ_УНИ» ?​ СВОД_УСЛ = USL()​ и не проще​Юрий М​3501​ в названии темы​

Задача

​ на его основе Выпадающий​ в диапазоне Исходный_список;​ формулу вниз, например,​ в диапазоне подсчета​

​). =ВПР(E7;Весь_диапазон_Дат;1;ИСТИНА) Результат помещаем​ с датами должен​Воспользуемся функцией​ Ctrl+Shift+Enter.​ с сумме будет​ Variant() Dim q(),​ ты получаешь массив.​KoGG​ End Select End​ , но быстрее​: Сориентировали Вас как​просто надо чтобы​ :-)​ список, необходимо учитывать,​

​ЕСЛИОШИБКА() подавляет ошибку, возникающую,​ с помощью Маркера​ будут выделены соответственно​ в ячейку​ быть отсортирован по​COUNTIF​Попробуйте, у вас​ быстрее.​ w(), x, i&​Вызывая функцию из​: Если диапазон «Диапазон»​ Function​ точно).​ раз правильно: ведь​ в ячейке стояла​kim​ что вышеуказанные формулы​ когда функция ПОИСКПОЗ()​

Решение

​ заполнения. Чтобы все​ зеленым (см. рисунок​​J7​​ возрастанию (см. файл​(СЧЁТЕСЛИ). Для примера​ все получится .​Использование процедуры с​ q = Intersect(Диапазон,​

​ ячейки листа, ты​ из одной колонки,​SlavaRus​Написал функцию для​ требуется посчитать -​ цифра 3 (т.к.​: =СУММПРОИЗВ((ПОИСКПОЗ(B2:B12&C2:C12;B2:B12&C2:C12;)=СТРОКА(B2:B12)-1)*(C2:C12=»июнь»))​

​ возвращают значение Пустой​ пытается в массиве​ значения исходного списка​ выше) и синим​

• ​.​ примера).​ подсчитаем количество чисел​Bati4eli​ заполнением было бы​ Диапазон.Worksheet.UsedRange).Value w =​ не можешь весь​​ то​​: Расширенный фильтр не​
• ​ этой задачи, но​ сколько уникальных значений.​ кол-во чисел 3401,​​Serge​​ текст «», который​ нулей и единиц,​​ были гарантировано отображены​​ цветом (см. рисунок​
• ​Найдем нижнюю границу диапазона​Подсчитаем уникальные и неповторяющиеся​ 5 в диапазоне​:​
  ​ оптимальнее.​
  ​ Intersect(Диапазон_условия, Диапазон_условия.Worksheet.UsedRange).Value On​ массив поместить в​​Set RC =​​ подойдет? Поставить пустое​
• ​ 6000 строк она​anton_sp​​ 3402, 3501) невзирая​​: Вот именно. А​ требует аккуратного обращения,​
  ​ возвращенном СЧЁТЕСЛИ(), найти​

​ в списке уникальных​ ниже).​ (номер позиции в​ значения в столбце​A1:A6​​Aksima​​А еще оптимальнее​
​ Error Resume Next​

​ одну ячейку.​ Диапазон.Rows(IC.Row).Cells(1)Да и RC​ условие и галку​ глотает 30 секунд​: Помогите подобрать функцию​

​ на повторы…​ надо было в​ особенно при подсчете​ 0, которого нет​ значений, необходимо сделать​СОВЕТ:​​ исходном списке) с​​ Проданный товар в​
​, используя следующую формулу:​

​,​ — смотри выше.​ With New Scripting.Dictionary​Возможно помогут ввод​ вовсе не нужна.​ на уникальных значениях.​

Тестируем

​ ..=(​​ по следующему описанию:​​North_Rain​ посте.​ значений (вместо обычной функции​

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

​=COUNTIF(A1:A6,5)​честно говоря я​KoGG​ ‘ ===== Tools​ массива твоих функций​rcv = Диапазон.Rows(IC.Row).Cells(1).Value​

​ Потом посчитать видимые​Public Enum MTD​​необходимо подсчитать количество​​: коллеги, посмотрите пожалуйста​Serge​ СЧЕТЗ() нужно использовать​

​ ячейке​ значений равным размеру​ можно также выполнить​=СУММПРОИЗВ(МАКС((J7=Весь_диапазон_Дат)*(СТРОКА(Весь_диапазон_Дат))))-СТРОКА($A$6)​Решение данной задачи позволит​=СЧЁТЕСЛИ(A1:A6;5)​ всю жизнь думал,​: Офис 2007, Windows​ — References -​

​ в нужный диапазон​
​А загнав данные​ строки, если фильтровать​ [КОЛИЧЕСТВО_УНИ] = 0​ уникальных значений из​ этот примерчик, там​

excel2.ru

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

​: Вот с уникальными.​ СЧЕТЕСЛИ() со специальными​B12​ исходного списка (на​ с помощью стандартного​Результат помещаем в​ подсчитать сколько наименований​Чтобы подсчитать уникальные значения,​

​ что массивы строчные​​ 8, процессор Intel(R)​​ Microsoft Scripting Runtime​ как формул массива​ в массивы будет​ на месте.​

Задача

​ [МАССИВ_УНИ] = 1​ второго столбца, которые​ необходим подсчет уникальных​23,5 КБ​ аргументами). Например, см.​

​, когда все уникальные​ тот случай, когда​ инструмента EXCEL -​ ячейку​

Решение

​ товара было продано​ добавим функцию​ (т.е. одномерный массив​ Core(TM) i7 CPU​ ===== .CompareMode =​ ({}), хотя легче​ еще быстрее.​mcherry​ End Enum Public​ отвечают заданному критерию​

​ значений по условию​

• ​lapink2000​ статью Динамический выпадающий список​ значения уже извлечены​ все значения исходного​
• ​ Сводных таблиц.​K7​
• ​ в определенный промежуток​SUM​
• ​ представлен в виде​

​ 930 @ 2.8​ TextCompare For Each​​ и понятнее будет​​Кликните здесь для​: можно так:​ Function СВОД_УСЛ(Диапазон As​ первого столбца и​ совпадения ГОДА!! от​​: Такой достаточно шустрый​​ в MS EXCEL.​

​ из исходного списка).​

​ списка не повторяются).​​Имея список с повторяющимися​​.​​ времени (т.е. уникальных​(СУММ), 1/, и​​ одной колонки из​ GHz, Оперативка 4​ x In w​ вызывать функцию из​ просмотра всего текста​=ЕСЛИ(И(СЧЁТЕСЛИ(B:B;B2)=1;C2=$C$1);1;0)​ Range, Условие As​ имеют совпадения в​ даты.. помогите пожалуйста​ вариант без конкатенации:​Примечание​Формула будет работать и​ В случае наличия​ значениями, создадим список,​По определенным позициям границ​ наименований), а также​ заменим 5 на​ множества строк).​ Гб.​ i = i​ процедуры и в​ Public Function СВОД_УСЛ(Диапазон​B:B — столбец​

​ Variant, _ Диапазон_условия​

• ​ третьем столбце.​ с этим примером​{=СУММ(—(ЧАСТОТА(ЕСЛИ(C2:C12=»июнь»;ПОИСКПОЗ(B2:B12;B2:B12;));СТРОКА(B2:B12)-1)>0))}​: В статье Восстанавливаем последовательности​ в случае если​ в исходном списке​ состоящий только из​ диапазона (ячейки​ выделить эти наименования.​A1:A6​А как представить массив,​Bati4eli​ + 1 If​ ней же делать​​ As Range, Условие​​ в котором значение​ As Range, metod​Все значения текстовы​​Z​​Globba​ из списка без​ исходный список содержит​ большого количества повторяющихся​ уникальных значений. При​K6 K7​​ А подсчет неповторяющихся​​.​ чтобы он распределялся​​: К сожалению в​​ Not IsObject(.Item(x)) Then​ вывод на лист​ As Variant, _​​ должно быть уникальным​​ As MTD) Dim​В примере: нужно​
• ​: Причем уникальные не​: Serge 007 большое​ повторов в MS​ числовые значения.​ значений, список уникальных​ добавлении новых значений​), формируем Динамический диапазон​ значений даст информацию​=SUM(1/COUNTIF(A1:A6,A1:A6))​ по строкам, а​​ том то и​​ Set .Item(x) =​
• ​ итогов работы функции.​ Диапазон_условия As Range,​B2 — проверяемое​
• ​ IC As Range​ найти только автомобили​ понял… Может просто​ спасибо.​ EXCEL решена обратная задача:​Примечание​ значений можно сделать​ в исходный список,​​ для товаров (Текущий_диапазон_Товаров):​​ о том, сколько​=СУММ(1/СЧЁТЕСЛИ(A1:A6;A1:A6))​ не столбцам в​

​ дело, что одной​ New Collection .Item(x).Add​Hugo121​ metod As MTD)​

​ значение​​ Dim RC As​ из перечня совпадающих​ — считаем кол-во​Вот только одного​ из списка уникальных​. Функция ЕСЛИОШИБКА() будет работать начиная​ меньшего размера, удалив​ список уникальных значений​=СМЕЩ(лист1!$B$7;лист1!$K$6-1;;лист1!$K$7-лист1!$K$6+1))​ и каких товаров​

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

​Закончим ввод формулы нажатием​ экселе?​ сводной здесь не​ 0, q(i, 1)​: Если не работать​ Dim i&, j&​
​C — столбец​
​ Range Dim icv​
​ во втором и​

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

​ определенного года?.. ;)​ не могу понять​ значений, в котором​ с версии MS​ лишние формулы, чтобы​ должен автоматически обновляться.​Теперь найдем количество уникальных​ было продано только​Ctrl+Shift+Enter​

​Казанский​​ решишь задачу, так​ Next ReDim w(1​ с ячейками -​ Dim Dia_Usl As​ «соседний, в котором​ As Variant Dim​ третьем столбцах​ -90211-​ — почему, если​ для каждого значения​ EXCEL 2007, чтобы​ исключить ненужные вычисления,​Пусть в столбце​ (различающихся) значений в​ в количестве одной​.​,​ как таким методом​ To Условие.Count, 1​ будет ещё раз​

​ Variant Dim Dia​​ должно выполняться условие»​ rcv As Variant​anton_sp​Владимир​ я копирую присланную​ задано количество повторов,​ обойти это ограничение​ тормозящие пересчет листа.​А​ определенном нами диапазоне.​ партии.​Примечание:​К сожалению ваш​ мы получим не​ To 1) i​ так в 40​ As Variant Dim​

​$C$1 — условие​​ Dim x As​: Сам даю ответ​: =СУММ(—(ПОИСКПОЗ(ЕСЛИ(ЕОШ(ГОД($D$3:$D$35)=G2);ЛОЖЬ;ГОД($D$3:$D$35)=G$2)*$B$3:$B$35;ЕСЛИ(ЕОШ(ГОД($D$3:$D$35)=G$2);ЛОЖЬ;ГОД($D$3:$D$35)=G$2)*$B$3:$B$35;0)=СТРОКА($B$3:$B$35)-2))-1​ Вами формулу в​ создается список этих​ читайте статью про​Разберем работу формулу подробнее:​имеется список с​ Результат поместим в​В статьях Подсчет уникальных​

excel2.ru

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

​ ячейку​​ текстовых значений и​

​ что это формула​​ верно считает. Он​ по одной профессии,​

​ Each x In​​Вообще удивляюсь -​
​ Dim y As​
​ весь столбец и​

​ как вы все​​ Long Dim USL()​ посчитать получившуюся сумму….​

​ USL() As Variant​​ уникальные значения?​
​ он прмер неправильно​ она тут же​: Добрый вечер.​

​ Для 2003, где​​ в качестве критерия​ компаний.​:​

​ было показано, что​​ в фигурные скобки​

​ платежей по выбранной​​ совершенные по этой​ i + 1​ так виртуально коды​

​ эта функция не​​ (второй аргумент) указано​В некоторых ячейках исходного​
​=СУММПРОИЗВ((Текущий_Диапазон_Товаров<>»»)/ СЧЁТЕСЛИ(Текущий_Диапазон_Товаров;Текущий_Диапазон_Товаров&»»))​ такое уникальные и ​ {}. Их не​ профессии, а функция​ профессии.​ w(i, 1) =​ разбираете…​ SHT As Boolean​: Замени с 23​ Boolean Application.Volatile x​

​: Уникальные, так как​​ возможно.​ выдает #ЗНАЧ ?​В ней в​ используется.​ не одно значение,​ списка имеются повторы​Напомним, что Уникальные значения​

​ неповторяющиеся значения и​​ нужно вводить самостоятельно.​ должна высчитывать кол-во​Честно говоря извеняюсь​ .Item(x).Count Next End​Судя по описанию​ Application.Volatile x =​

​ по 44 строку,​​ = Диапазон.Row +​
​ в третьем столбце​———​

​Globba​​ столбце В -​
​Если исходная таблица содержит​ а целый массив​
​ — новый список​ соответствуют, в нашем​
​ как их подсчитать​ Они исчезнут, когда​ уникальных табелей по​ за то, что​
​ With СЧЕТ_УСЛ =​
​ — на 6000​ Диапазон.row + Диапазон.Rows.Count​ будет быстрее:​

​ Диапазон.Rows.Count — 1​​ есть повторения. Но​

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

​ списке. Отличие этой​​ формулу.​Казанский​ Я думал, что​ надо вводить в​ отработает за доли​

​ = Диапазон.Parent.UsedRange.Rows.Count If​​ In Диапазон_условия icv​

​Для наглядности уникальные значения​​ указанный временной интервал.​ статьи в том,​Пояснение:​: Можете попробовать так:​ этот файл придется​ столбец С4:С282 с​

​ секунды. Но без​​ x > y​

​ = IC.Value If​​ y Then x​ поиске таких значений​ что автор создал​ формулу в ячейку​ они работали над​ с пропусками файла​ массив нулей и​ в исходном списке​

​Число неповторяющихся значений в​​ что пользователь может​Диапазон (массив констант), созданный​Function MyFunc() As​ перекидывать по предприятию​ помощью Ctrl+Shift+Enter. Расчет​ файла делать…​ Then x =​ LCase(icv) = LCase(Условие)​ = y -​ — второй​ сообщение-дубликат. Вот и​ D7. Там она​ проектом.​ примера):​ единиц. Возвращается 0,​ выделены цветом с​

​ определенном нами диапазоне​​ настроить диапазон подсчета:​ с помощью функции​ Variant MyFunc =​ (а вы знаете,​ занимает доли секунды,​Bati4eli​

​ y — Диапазон.row​​ Then For Each​ Диапазон.Row + 1​CrazyNice​ ломаю голову -​

planetaexcel.ru

Счет уникальных значений из дублирующихся по критерию (Формулы/Formulas)

​ уже почему-то не​​Как подсчитать (по​=ЕСЛИОШИБКА(ИНДЕКС($A$5:$A$19;​
​ если значение из​ помощью Условного форматирования.​ можно найти с​ теперь можно подсчитать​COUNTIF​ Application.Transpose(Array(«Arg1», «Arg2», «Arg3»))​ что макросы включены​
​ результат совпадает с​
​:​ + 1 Dia​ RC In Диапазон.Rows(IC.row).Cells​ Set Диапазон =​: Доброго дня!​

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

​Для начала создадим Динамический​​ помощью нижеследующей формулы.​ значения не во​(СЧЁТЕСЛИ), хранится в​ End FunctionС уважением,​ не у всех​ существующим: Код =СЧЕТ_УСЛ(ИСТОЧНИК!A:A;A4:A282;ИСТОЧНИК!B:B)​Hugo121​

excelworld.ru

Счёт уникальных видимых значений

​ = Range(Диапазон.Cells(1), Диапазон.Rows(x).Cells(1))​​ rcv = RC.Value​
​ Range(Диапазон.Cells(1), Диапазон.Rows(x).Cells(1)) Set​Есть умная таблица​
​ удалить. Или оба?​Юрий М​ работали над проектом,​
​);»»)​ найдено в диапазоне​ диапазон, представляющий собой​ Результат поместим в​
​ всем исходном списке,​ памяти Excel, а​ Aksima​
​ и ошибка типа​
​KoGG​, Как можно не​ Dia_Usl = Диапазон.Offset(0,​ If rcv <>​ Диапазон_условия = Диапазон.Offset(0,​
​ со срезами.​

​Владимир​​: У Сергея там​ например в​Для отбора уникальных значений​B4:B4B4:B5​

planetaexcel.ru

Количество уникальных значений по условию

​ исходный список. Если​​ ячейку​
​ а в нужном​ не в ячейках​Bati4eli​ #ИМЯ введет в​: Кстати, оптимальнее всего​ работать с ячейками?​ Диапазон_условия.Column — Диапазон.Column)​ «» And rcv​ Диапазон_условия.Column — Диапазон.Column)​Мне нужно по​: Я за оба.​ формула массива, а​Июне?​
​ можно обойтись без​и т.д.), и​ в исходный список​H7​ диапазоне (границы которого​
​ листа.​: Вот для чего​ ступор непродвинутых людей).​ в данной задаче​Посмотрите, пожалуйста, пример.​ ReDim USL(1 To​ <> 0 Then​ ReDim USL(1 To​ текстовому столбцу посчитать​Z​ завершается ввод такой​Dophin​ использования формул массива.​ 1 если найдено.​ будет добавлено новое​:​ легко настраиваются).​Массив констант выглядит следующим​ транспонирование в экселе​Казанский​ вообще убрать функцию,​Bati4eli​ 1) For i​ SHT = True​ 1) For Each​ количество уникальных значений.​: На одно (из​ формулы не просто​: =СЧЁТЕСЛИ(C2:C12;»июнь»)​ Для этого создайте​ Например, в ячейке​ значение, то оно​=СУММПРОИЗВ(—(СЧЁТЕСЛИ(Текущий_Диапазон_Товаров;Текущий_Диапазон_Товаров)=1))​Границы интервала будут задаваться​ образом: {3;1;1;1;3;3}, что​ нужно =)​, ваш метод я​ а на листе​:​ = 1 To​ For x =​ IC In Диапазон_условия​Формула должна считать​ 2-х в 2-х​ нажатием на Enter,​Юрий М​ дополнительный служебный столбец​B5​ будет автоматически включено​Напомним, что неповторяющиеся значения​ пользователем в ячейках​ обозначает: три числа​Спасибо, большое!​ обязательно завтра испробую.​ источник рядом с​KoGG​ UBound(Dia_Usl, 1) If​ 1 To UBound(USL)​ icv = IC.Value​ уникальные значения только​ темах) ТС сам​ а Ctrl+Shift+Enter​: Андрей, вроде уникальные​ для промежуточных вычислений​формулой СЧЁТЕСЛИ(B$4:B5;Исходный_список) возвращается​ в Динамический диапазон​ соответствуют, в нашем​

​Е6Е7​​ 7, одно значение​На примере, который​А так написал​ суммой добавить колонку​,​ LCase(Dia_Usl(i, 1)) =​ If USL(x) =​

​ If LCase(icv) =​​ по видимым (отфильтрованным​
​ нашел ответ (),​
​Павел А​ нужны​ (см. лист «Без​
​ массив {1:0:0:0:0:0:0:1:0:0:0:0:1:1:0}. Т.е.​ и нижеследующие формулы​
​ случае, количеству Фруктов,​. Строки таблицы, которые​ «sun», одно значение​
​ Вы привели в​
​ тупо макрос, выводящий​ численность, и по​попробовал использовать массив..​

​ LCase(Условие) Then If​​ rcv Then SHT​ LCase(Условие) Then For​ срезом) ячейкам.​
​ а на второе,​: Добрый вечер!​Guest​ CSE» в файле​ в исходном списке​ не придется модифицировать.​ проданных в указанный​ попадают в заданный​ «moon», одно число​ сообщении #8, моя​ массив профессий и​ всей колонке проставить​ и почему то,​ Dia(i, 1) <>​ = False End​ Each RC In​Данные в столбце​ здесь… еще ищет,​Объясните пожалуйста «=СТРОКА(B2:B12)-1)»​: Вариант — PT.​ примера).​ найдено 4 значения​Для создания Динамического диапазона:​ интервал, по 1​

​ интервал выделены с​​ 5, три числа​ функция дает точно​ численность рядом с​ 1. В сводной​ функция стала работать​
​ «» And Dia(i,​​ If Next If​ Диапазон rcv =​ только текстовые и​ наверное… Подождем-с?.. ;)​
​ в формуле.​Z.​СОВЕТ: ​ «ООО Рога и​​на вкладке Формулы в​​ партии.​ помощью Условного форматирования​ 7, три числа​

​ такой же результат,​​ ними.​ таблице добавить поле​ в разы дольше,​
​ 1) <> 0​ SHT Then USL(UBound(USL))​ RC.Value If rcv​
​ без пустых ячеек.​
​ -58476-​Serge​79226​
​Список уникальных значений можно​ копытца» (​ группе Определенные имена​1. В ячейку​ серым цветом.​ 7.​ как ваша функция​Самый последнейший вопрос:​ колонки численность и​ чем вариант работы​ Then SHT =​ = rcv ReDim​ <> «» And​Excel 2016.​North_Rain​: Здравствуйте.​Serge​ создать разными способами,​B5​ выберите команду Присвоить​Е6​Сначала для удобства определим​Все это сводится к​ — я выводил​Почему функция​ она будет суммироваться.​ с ячейками .​ True For x​ Preserve USL(1 To​ rcv <> 0​Во вложении маленький​: Юрий, тему: Счет​А что именно​: Как подсчитать (по​ например, с использованием​). Массив легко увидеть​ имя;​введите начальную дату​ Именованный диапазон Весь_диапазон_Дат​ {1/3;1/1;1/1;1/1;1/3;1/3}.​ в другой столбец​Function MyFunc() as​KoGG​Посмотрите, пожалуйста, пример​ = 1 To​ UBound(USL) + 1)​ Then If RC.Row​ пример. Реальная таблица​ повторяющихся значений можно​ не понятно?​ формуле)…​ Расширенного фильтра (см.​ с помощью клавиши​в поле Имя введите:​ (верхняя граница временного​ как ссылку на​Этот массив констант используется​ и сравнивал.​ variant MyFunc =​
​: А вот тестовый​
​ из предыдущего сообщения.​ UBound(USL) If USL(x)​
​ End If End​ = IC.Row Then​ порядка 3 тысяч​ закрыть, просто меня​=СТРОКА(B2:B12)-1 возвращает такой​
​23,5 Кб​ статью Отбор уникальных​F9​ Исходный_список;​ интервала) — 02.01.2008​ диапазон =$A$7:$A$41 (т.е.​ в качестве аргумента​Покажите на конкретных​ array(«Арг1″,»Арг2″,»Арг3») End Functionпри​ пример, где работают​ (Там используется старая​

​ = Dia(i, 1)​​ If Next End​ SHT = True​ строк и 20​ неправильно сориентировали на​ массив {1:2:3:4:5:6:7:8:9:10:11}.​
​Guest​ строк с помощью​(выделите в Строке​в поле Диапазон введите​
​2. В ячейку​ полностью на весь​ для функции​ данных, что неверно.​ вводе её в​ три метода.​

​ функция)​​ Then SHT =​​ If Next​​ For x =​ колонок.​
​ «уникальные» значения:) вот​

​Для чего? Что​​: да, именно уникальные​​ Расширенного фильтра), Сводных​​ формул выражение СЧЁТЕСЛИ(B$4:B5;Исходный_список),​
​ формулу =СМЕЩ(УникальныеЗначения!$A$5;;; СЧЁТЗ(УникальныеЗначения!$A$5:$A$30))​Е7​ диапазон дат исходной​SUM​Казанский​ Excel (как массив)​
​Тестовая процедура bb​Казанский​ False End If​Bati4eli​

​ 1 To UBound(USL)​​Заранее спасибо.​ я потом и​ бы сравнить с​то есть фамилии​ таблиц или через​
​ нажмите​нажмите ОК.​введите конечную дату​ таблицы).​​(СУММ), давая результат​​,​ у меня отображается​Результаты:​
​: Лучше создать функцию,​ Next If SHT​: Вот я дурак..​ If USL(x) =​a.i.mershik​ нашел уникальные значения:)​ массивом полученным при​ могут повторяться в​ меню Данные/ Работа​F9​Список уникальных значений создадим​ (нижняя граница временного​Затем определим диапазон, в​ 4.​наверно я не​ только самый первый​Исправленный оригинал -​ которая вводится в​ Then USL(UBound(USL)) =​ я думал, что​ rcv Then SHT​: CrazyNice, Добрый день,​ и тут задал​ работе​ одном месяце и​ с данными/ Удалить​: вместо формулы отобразится​ в столбце​ интервала) — 10.01.2008​ котором будем подсчитывать​Урок подготовлен для Вас​ выспался и плюс​ элемент массива?​ СВОД_УСЛ время 0,06​ столбец как формула​ Dia(i, 1) ReDim​ этот цикл в​ = False End​ вот обсуждалась темы​ вопрос.. на самом​=ПОИСКПОЗ(B2:B12&C2:C12;B2:B12&C2:C12;).​

​ повторяющиеся считать не​​ дубликаты. У каждого​ ее результат);​B​3. Новый временной интервал​ уникальные и неповторяющиеся​ командой сайта office-guru.ru​ никогда не пользовался​Весь день матюкался!​ сек​ массива и возвращает​ Preserve USL(1 To​ цикле не особо​

​ If Next If​​ и последний УДФ​ деле пример правильный​Если элементы массива​
​ нужно​
​ способа есть свои​
​ПОИСКПОЗ() – возвращает позицию​с помощью формулы​ будет выделен серым​
​ значения. Для этого:​Источник: http://www.excel-easy.com/examples/count-unique-values.html​ коллекциями, поэтому не​
​Bati4eli​Мой вариант с​
​ массив.​ UBound(USL) + 1)​ увеличит время работы!​ SHT Then USL(UBound(USL))​ корректно работает.​ и возможно решение​ совпадают, то значение​причем нужна именно​ преимущества и недостатки.​ первого нуля в​ массива (см. файл​
​ цветом. Подсчет наименований​Найдем верхнюю границу диапазона​Перевела: Ольга Гелих​
​ совсем врубаюсь в​: Одномерный массив в​

​ массивами — СВОД_УСЛ2​​При этом в​ End If End​KoGG​ = rcv ReDim​Bati4eli​ для вас очевидно..​

​ уникально. Всё.​​ формула, а не​ Преимущество использования формул​ массиве из предыдущего​ примера). Для этого​ товара будет производиться​ (номер позиции в​Автор: Антон Андронов​ принцип работы вашей​ Excel представляется 1​ время 0,02 сек​ функции достаточно один​ If End If​
​, да по идеии​ Preserve USL(1 To​: Привет, всем!​ сейчас посмотрю какое​Павел А​ сводная таблица​ состоит в том,​ шага. Первый нуль​ введите следующую формулу​ только в этом​ исходном списке), воспользовавшись​Произведем подсчет уникальных и​
​ функции.​​ строкой и несколькими​Вариант Казанского СЧЕТ_УСЛ​
​ раз пройти по​ Next ReDim Preserve​ нужно сразу адресовать​ UBound(USL) + 1)​Столкнулся со следующей​
​ решение предложенное форумчанами​
​: Спасибо!​
​Guest​ чтобы при добавлении​ соответствует значению еще​ в ячейку​ интервале.​ решением из статьи​ неповторяющихся текстовых значений​При выводе результата​
​ столбцами.​

​ время 0,11 сек.​​ массивам​ USL(1 To UBound(USL)​ на аналогичную строку​ End If End​
​ проблемой: требуется подсчитать​ сработает. просьба ногами​North_Rain​: формула неправильная, проверь​ новых значений в​ не найденному в​B5​​4. После ввода границ​​ Ближайшее событие =ПОИСКПОЗ(МИН(ЕСЛИ(Весь_диапазон_Дат>=E6;Весь_диапазон_Дат;»»));Весь_диапазон_Дат;0)​ не во всем​
​ у меня везде​В данном случае​

​При вызове множества​​Диапазон, Диапазон_условия​​ — 1) Select​​ в проверяемом диапазоне.​
​ If End If​ кол-во уникальных значений​ не бить:)​: поднимая эту тему​ по сентябрю​ исходный список, список​ исходном списке (т.е.​​:​ диапазона, в ячейках​ Эту формулу массива​ списке, а только​ получается​
​ вам надо выделить​​ функций из сводной​
​и посчитать уникальные,​ Case metod Case​Set RC =​ Next End If​ в одном столбце,​Z​ нужен подсчет уникальных​Serge​ уникальных значений автоматически​

​ значению «ОАО Уважаемая​​=ЕСЛИОШИБКА(ИНДЕКС(Исходный_список; ПОИСКПОЗ(0;СЧЁТЕСЛИ(B$4:B4;Исходный_список);0));»»)​
​H6 Н7​ помещаем в ячейку​ в определенном пользователем​152​ на листе область​

​ таблицы время будет​​ а потом сформировать​ КОЛИЧЕСТВО_УНИ СВОД_УСЛ =​ Диапазон.Rows(IC.Row).CellsЕдинственный вопрос: почему​
​ Next ReDim Preserve​

​ если в соседнем​: Странно, а как​ значений без условия​: Формула правильная.​ обновлялся.​ компания» для формулы​После ввода формулы вместо​будет подсчитано число​K6​
​ диапазоне.​.​​ из 1 строки​​ другое, если заново​
​ выходной массив.​ UBound(USL) Case МАССИВ_УНИ​ у меня не​ USL(1 To UBound(USL)​ столбце значение удовлетворяет​ же это: «вот​3401​
​Про уникальные надо​СОВЕТ2​ в ячейке​​ENTER​​ неповторяющихся и уникальных​

CyberForum.ru

​.​

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

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

Подсчет Уникальных ТЕКСТовых значений в MS EXCEL

и

Подсчет Уникальных ЧИСЛОвых значений в MS EXCEL

. В этой статье рассмотрим более сложные варианты с условиями.

Задача1

Пусть имеется таблица с перечнем продаж по продавцам.

Требуется подсчитать сколько различных товаров продал конкретный продавец. Например, Вася продал 1 товар1, 2 Товара2 и 1 Товар4 (выделено зеленым). Всего 3 разных товара.

Это можно подсчитать формулой

=СУММПРОИЗВ((A13:A21=A7)/СЧЁТЕСЛИМН(B13:B21;B13:B21;A13:A21;A13:A21))

, которая будет работать только с версии MS EXCEL 2007 из-за функции

СЧЁТЕСЛИМН()

.

Изменив в ячейке

А7

имя продавца (в

файле примера

для удобства сделан

выпадающий список

), формула пересчитает количество уникальных.

Задача2

Аналогичным образом можно решить задачу с двумя условиями.

=СУММПРОИЗВ(((A14:A23=A8)*(B14:B23=B8))/СЧЁТЕСЛИМН(A14:A23;A14:A23;B14:B23;B14:B23;C14:C23;C14:C23))

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

В

файле примера на листе Два критерия

приведено решение этой задачи с помощью

Сводной таблицы

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

Задача3

Теперь рассмотрим другую таблицу (столбцы А:С на рисунке ниже).

Необходимо вывести количество заключенных договоров в каждом месяце.

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

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

Название месяца прописью в MS EXCEL

). Выведем из этого столбца только уникальные месяцы (см. статью

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

) и поместим их в столбец F.

И, наконец, помощью формулы

=СУММПРОИЗВ(($D$9:$D$26=F9)/СЧЁТЕСЛИ($A$9:$A$26;$A$9:$A$26))

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

Решение также возможно с помощью

Сводной таблицы.

Хитрости »

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

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

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

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

Как использовать данную команду. Выделяете столбец(или несколько) с теми данными, в которых надо удалить дублирующие записи. Идете на вкладку Данные (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 430 скачиваний)

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

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

    Видеоуроки

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



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

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

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

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

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

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

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

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

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

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

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

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

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

Подсчет разных значений в сводной таблице Excel

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

Нажмите здесь, чтобы загрузить файл примера и делать все вместе со мной:

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

• Сколько сотрудников в каждом регионе (а это не что иное, как количество разных сотрудников в каждом регионе)?
• Сколько сотрудников продали принтер в 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.
• Так как мы используем формулу Excel, это может замедлить работу вашей книги Excel, если в данных тысячи строк.

Добавить данные в модель данных и суммировать, используя «Число различных элементов»

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

В случае, если вы используете предыдущую версию, вы не сможете использовать этот метод (используйте метод, описанный выше).

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

Ниже приведены шаги для получения количества разных сотрудников в сводной таблице:

• Выберите любую ячейку в таблице.
• Нажмите вкладку «Вставка».

• Нажмите на кнопку Сводная таблица.

• В диалоговом окне «Создание сводной таблицы» убедитесь, что таблица / диапазон указаны правильно и выбран новый рабочий лист.
• Установите флажок «Добавить эти данные в модель данных».

• Нажмите ОК.

Приведенные выше шаги вставят новый лист с новой сводной таблицей.

Перетащите регион в область «Строки» и «Сотрудник» в область «Значения». Вы получите такую сводную таблицу:

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

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

• Щелкните правой кнопкой мыши по любой ячейке в «Число элементов в столбце Сотрудник»
• Нажмите на «Параметры полей значений».

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

• Нажмите ОК.

Обратите внимание, что название столбца изменится с «Число элементов в столбце Сотрудник» на «Число разных элементов в столбце Сотрудник». Вы можете изменить его.

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

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

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

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

Помните — уникальные значения и разные значения не одно и то же. Нажмите здесь, чтобы узнать разницу.

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

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

Вот формула для этого случая:

= ЕСЛИ (ЕСЛИ (СЧЁТЕСЛИМН ($C$2:$C$1001; С2; $B$2:$B$1001; В2) / СЧЁТЕСЛИ ($C$2:$C$1001; С2) <1;0;1); ЕСЛИ (СЧЁТЕСЛИ ($С2:С$22; С2) > 1;0;1);0)

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

Если имя встречается в нескольких регионах, формула возвращает 0, в противном случае возвращает единицу.

Формула также проверяет, повторяется ли имя в том же регионе или нет. Если имя повторяется, только первый экземпляр имени возвращает значение 1, а все остальные экземпляры возвращают 0.

Это может показаться немного сложным, но это опять-таки зависит от того, чего вы пытаетесь достичь.

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

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

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

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

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

Допустим значения содержатся в диапазоне С2:С9

Водим диапазон E2:E9 в ячейках которого содержится количество повторов каждого значения в диапазоне С2:С9

Формула количества повторов для значения C2 =СЧЁТЕСЛИ($C$2:$C$9;C2)

Эта формула растягивается на весь диапазон

Количество уникальных значений определится по формуле =СУММЕСЛИ(E2:E9;»=1″)

Чтобы увидеть уникальные элементы нужно наложить фильтр на столбец E

Если возникла задача посчитать количество уникальных значений в Экселе (Excel), то её можно решить несколькими способами — с формулами и без формул.

1) В том случае, если диапазон данных (числовых или текстовых) является статичным и меняться не будет, то можно воспользоваться командой «Удалить дубликаты».

Для этого выделяем диапазон (в нашем примере — это B1:B14) и на панели инструментов Excel выбираем «Данные» -> «Удалить дубликаты».

Появится окно, в котором указываются столбец / диапазон, из которых удаляются дубликаты.

Нужно, чтобы обязательно стояла галочка «Мои данные содержат заголовки» — иначе заголовок тоже будет учитываться при отборе уникальных значений.

Теперь нажимаем на кнопку «ОК».

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

Таким образом, количество уникальных значений в выбранном диапазоне будет равно 10.

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

Функция СЧЁТЕСЛИ будет использоваться для того, чтобы посчитать, сколько раз встречается то или иное значение в диапазоне.

Далее с помощью функции СУММ суммируются дроби вида:

1 / Число вхождений каждого элемента.

Для нашего примера формула будет выглядеть так:

При этом вводить её нужно как формулу массива — через комбинацию Ctrl + Shift + Enter.

В результате получится всё те же 10 уникальных значений.

А вот примеры работы этой формулы, если в таблице Excel имеются текстовые данные:

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

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

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

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

Если применить такую формулу для массива с пустыми значениями, она вернет ошибку #ДЕЛ/0. Как это обойти – читайте дальше.

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

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

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

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

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

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

Чтобы в Excel посчитать количество уникальных значений в столбце с числами, используем функцию ЧАСТОТА :

Количество уникальных строк с текстом

Похожая «скоростная» но для счёта текстовых строк:

=СУММПРОИЗВ(—(ЧАСТОТА(ПОИСКПОЗ(диапазон; диапазон;0);СТРОКА(диапазон)-СТРОКА(диапазон.первая ячейка)+1)>0))

Такая формула не подходит, когда в массиве есть пустые клетки. Если это критично, можно еще усложнить:

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

Это формула массива, после ввода нажимайте не Enter , а Ctrl+Shift+Enter . Иначе работать не будет.

Выглядит монструозно, а результат верный. Можно пользоваться!

Пересчёт числовых значений с условием

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

Формула получилась такая:

Это формула массива, подтверждаем ввод комбинацией Ctrl+Shift+Enter !

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

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

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

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

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

Это всё на сегодня, Задавайте ваши вопросы в комментариях. Понравилась статья – поделись с другом!

Подсчет Уникальных ТЕКСТовых значений в MS EXCEL

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

Сначала поясним, что значит подсчет уникальных значений. Пусть имеется массив текстовых значений <«а»,»b»,«а»,»а»,»а»>. При подсчете уникальных игнорируются все повторы, т.е. значения выделенные жирным. Соответственно, подсчитываются остальные значения, т.е. «а» и «b». Ответ очевиден: количество уникальных значений равно 2.

Произведем подсчет числа уникальных текстовых значений в диапазоне A7:A15 (см. файл примера ). Диапазон может содержать пустые ячейки.

Решение

Запишем формулу =СУММПРОИЗВ(( A7:A15<>«» )/СЧЁТЕСЛИ(A7:A15;A7:A15))

Если в диапазоне кроме текстовых значений содержатся также и числа, то формула подсчитает и их. Чтобы игнорировать числовые значения нужно записать формулу =СУММПРОИЗВ(ЕТЕКСТ( A7:A15 )/СЧЁТЕСЛИ(A7:A15;A7:A15))

Если в список постоянно добавляются значения, то можно создать Динамический именованный диапазон Исходный_список.

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

Примечание: Уникальные значения в файле примера выделены с помощью Условного форматирования (см. статью Выделение уникальных значений в MS EXCEL).

Часто вместо формулы =СУММПРОИЗВ(( A7:A15<>«» )/СЧЁТЕСЛИ(A7:A15;A7:A15)) используют более простую формулу =СУММПРОИЗВ(1 /СЧЁТЕСЛИ(A7:A15;A7:A15)) . Разница между формулами состоит в том, что вторая формула учитыват значения Пустой текст («»), а первая их игнорирует.

Приведем пример, когда это бывает важно.

Пусть дана таблица продаж товаров (см. рисунок ниже, столбцы А и В). С помощью формулы =ЕСЛИ(МЕСЯЦ(B26)=1;A26;»») определяются товары, которые были проданы в январе. Если товар продан не в январе, то формула возвращает значение Пустой текст. Пользователь решает подсчитать количество уникальных товаров в январе (их всего 3: Товар1, Товар2 и Товар3).

Формула =СУММПРОИЗВ(( A7:A15<>«» )/СЧЁТЕСЛИ(A7:A15;A7:A15)) вернет правильный результат 3, а формула =СУММПРОИЗВ(1 /СЧЁТЕСЛИ(A7:A15;A7:A15)) вернет 4, т.к. в «пустых» ячейках С31:С34 на самом деле содержатся 4 значения «», которые воспринимаются ей как некое текстовое значение, хотя и нулевой длины.

СОВЕТ: Как подсчитать уникальные числовые значения показано в одноименной статье Подсчет уникальных числовых значений.

СОВЕТ: Как подсчитать уникальные числовые значения с дополнительными условиями (критериями) показано в статье Подсчет Уникальных ЧИСЛОвых значений в MS EXCEL.

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

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

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

Значения 5, 6, 7 и 6 выводятся в виде трех уникальных значений: 5, 6 и 7.

Значения «Кирилл», «Сергей», «Сергей», «Сергей», результат — это два уникальных значения — «Кирилл» и «Сергей».

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

С помощью диалогового окна » Расширенный фильтр » можно извлекать уникальные значения из столбца данных и вставлять их в новое место. Затем с помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.

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

Убедитесь, что диапазон ячеек содержит заголовок столбца.

На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.

Откроется диалоговое окно » Расширенный фильтр «.

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

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

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

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

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

В пустой ячейке, расположенной под последней ячейкой диапазона, введите функцию строки . Используйте диапазон уникальных значений, которые вы только что скопировали в качестве аргумента, исключая заголовки столбцов. Например, если диапазон уникальных значений — B2: B45, вы вводите = Rows (B2: B45).

Для выполнения этой задачи используйте сочетание функций Если, сумм, Частота, ПОИСКПОЗи ДЛСТР .

Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.

Добавьте итог с помощью функции сумм .

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

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

Поиск пустых ячеек с помощью функции Len . Длина пустых ячеек равна 0.

Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Office 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

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

Функция ПОИСКПОЗ осуществляет поиск определенного элемента в диапазоне ячеек и возвращает относительное расположение этого элемента в диапазоне. Например, если в диапазоне a1: A3 содержатся значения 5, 25 и 38, функция формула = Match (25; a1: A3; 0) возвращает число 2, так как значение 25 является вторым элементом диапазона.

Функция ДЛСТР возвращает число символов в текстовой строке.

Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции. Например, функция сумм (a1: A5) складывает все числа, содержащиеся в ячейкАх от a1 до A5.

Функция Если возвращает одно значение, если указанное условие имеет значение true, и другое, если условие имеет значение false.

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

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

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

Постановка задачи

Есть диапазон с данными, в котором некоторые значения повторяются больше одного раза:

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

Рассмотрим несколько способов ее решения.

Способ 1. Если нет пустых ячеек

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

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

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

Потом вычисляются дроби 1/Число вхождений для каждого элемента и все они суммируются, что и даст нам количество уникальных элементов:

Способ 2. Если есть пустые ячейки

Если в диапазоне встречаются пустые ячейки, то придется немного усовершенствовать формулу, добавив проверку на пустые ячейки (иначе получим ошибку деления на 0 в дроби):