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

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

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

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

В ячейке G2 указываем нужного нам заказчика, а в H2 записываем эту формулу массива:

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

Усложним задачу. Определим список не только для этого покупателя, но также и для определённого менеджера.

Вот наша формула массива:

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

В случае, если условий будет больше, нужно просто добавить соответствующий критерий в функцию ЕСЛИ и изменить число 2 на 3 или большее (в зависимости от количества условий).

Подсчет различных чисел.

Чтобы посчитать различные числовые значения (числа, даты и время), используйте функцию ЕЧИСЛО:

Считаем, сколько имеется различных чисел в диапазоне A2: A10:

Результат вы можете посмотреть ниже.

Это достаточно простое и элегантное решение, но работает оно гораздо медленнее, чем выражения, которые используют функцию ЧАСТОТА для подсчета уникальных значений. Если у вас большие наборы данных, то целесообразно переключиться на формулу, основанную на расчёте частот.

И вот еще один способ подсчета чисел:

Применительно к примеру ниже:

=СУММ(—(ЧАСТОТА(A2:A10; A2:A10)>0))

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

Пошагово разберём, как это работает.

Функция ЧАСТОТА возвращает массив цифр, которые соответствуют интервалам, заданным имеющимися числами. В этом случае мы сравниваем один и тот же набор чисел для массива данных и для массива интервалов.

Результатом является то, что ЧАСТОТА() возвращает массив, который представляет собой счетчик для каждого числового значения в массиве данных.

Это работает, потому что ЧАСТОТА() возвращает ноль для любых чисел, которые ранее уже появились в списке. Ноль возвращается и для текстовых данных. Поэтому полученный массив выглядит следующим образом: 

{3:0:0:2:0:0}

Как видите, обрабатываются только числа. Ячейки A7:A10 игнорируются, потому что там текст. А функция ЧАСТОТА() работает только с числами.

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

Получаем:

{ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ}

Теперь превращаем ИСТИНА и ЛОЖЬ в 1 и 0 соответственно. Делаем это при помощи двойного отрицания. Проще говоря, это двойной минус, который не меняет величину числа, но позволяет получить реальные числа, когда это вообще возможно:

{1:0:0:1:0:0}

А теперь функция СУММ складывает всё и получаем результат: 2.

Извлечь уникальные значения из диапазона.

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

Используем формулу массива

Здесь A2:C9 обозначает диапазон, из которого вы хотите извлечь уникальные значения. E1 – это первая ячейка столбца, в который вы хотите поместить результат. $2:$9 указывает на строки, содержащие данные, которые вы хотите использовать. $A:$C указывает на столбцы, из которых вы берёте исходные данные. Пожалуйста, измените их на свои собственные.

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

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

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

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

# take an input list as lst 
lst =  
print("Input list : ",lst)

#Empty list 
lst1 = [] 

count = 0

# traverse the array 
for i in lst: 
	if i not in lst1: 
		count = count + 1
		lst1.append(i) 

# printing the output 
print("Output list : ",lst1)
print("No. of unique items are:", count) 

Выход:

Input list :  
Output list :  
No. of unique items are: 6

Объяснение:

Здесь, во-первых, мы взяли входной список и напечатали входной список. Во-вторых, мы взяли пустой список и переменную count, которая установлена в 0. В-третьих, мы прошли список с самого начала и проверили, нет ли значения в пустом списке или нет. Если значение отсутствует в пустом списке, мы увеличиваем значение счетчика на 1 и добавляем это значение в пустой список. Если мы обнаруживаем, что элементы присутствуют в списке, мы не добавляем их в пустой список и не увеличиваем значение счетчика на 1. Наконец, мы напечатали пустой список, который теперь содержит уникальные значения и количество списка. Таким образом, мы можем видеть все уникальные элементы в списке.

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

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

Для целей этого урока я назову диапазон A2: A10 как NAMES. В дальнейшем мы будем использовать этот именованный диапазон в формулах.

В этом наборе данных есть повторение в диапазоне NAMES. Чтобы получить количество уникальных имен из этого набора данных (A2: A10), мы можем использовать комбинацию функций COUNTIF и SUMPRODUCT, как показано ниже:

=SUMPRODUCT(1/COUNTIF(NAMES,NAMES))

Как работает эта формула?

Давайте разберем эту формулу, чтобы лучше понять:

• COUNTIF(NAMES,NAMES)

  Эта часть формулы возвращает массив. В приведенном выше примере это будет {2; 2; 3; 1; 3; 1; 2; 3; 2}. Числа здесь показывают, сколько раз значение встречается в заданном диапазоне ячеек. Например, имя Боб, которое встречается в списке дважды, поэтому для Боба будет возвращено число 2. Точно так же Стив встречается трижды, и поэтому Стиву возвращается 3.

• 1/COUNTIF(NAMES,NAMES)
• SUMPRODUCT(1/COUNTIF(NAMES,NAMES))

Эта формула работает до тех пор, пока в диапазоне не останется пустых ячеек. Но если у вас есть пустые ячейки, он вернет # DIV / 0! ошибка.

Как обращаться с пустыми ячейками?

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

Теперь, если мы используем ту же формулу, которую использовали выше, часть формулы COUNTIF возвращает массив {2; 0; 3; 1; 3; 1; 2; 3; 1}. Поскольку в ячейке A3 нет текста, ее счетчик возвращается как 0.

И поскольку мы делим 1 на весь этот массив, возвращается # DIV / 0! ошибка.

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

=SUMPRODUCT((1/COUNTIF(NAMES,NAMES&””)))

Одно изменение, которое мы внесли в эту формулу, — это часть критериев функции COUNTIF. Мы использовали NAMES& ”” вместо NAMES. Таким образом формула вернет количество пустых ячеек (раньше она возвращала 0 —  там, где была пустая ячейка).

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

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

=SUMPRODUCT((NAMES<>””)/COUNTIF(NAMES,NAMES&””))

В этой формуле вместо 1 в качестве числителя мы использовали NAMES <> ””. Это возвращает массив значений ИСТИНА и ЛОЖЬ. Он возвращает FALSE всякий раз, когда есть пустая ячейка. Поскольку в расчетах ИСТИНА равно 1, а ЛОЖЬ равно 0, пустые ячейки не учитываются, так как числитель равен 0 (ЛОЖЬ).

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

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

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

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

=SUMPRODUCT((ISTEXT(NAMES)/COUNTIF(NAMES,NAMES&””)))

Все, что мы сделали, это использовали формулу ISTEXT (NAMES) в качестве числителя. Он возвращает ИСТИНА, если ячейка содержит текст, и ЛОЖЬ, если нет. Он не будет считать пустые ячейки, но будет считать ячейки с пустой строкой («»).

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

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

=SUMPRODUCT((ISNUMBER(NAMES))/COUNTIF(NAMES,NAMES&””))

Здесь мы используем ISNUMBER (NAMES) в качестве числителя. Он возвращает ИСТИНА, если ячейка содержит числовой тип данных, и ЛОЖЬ, если нет. Пустые ячейки не учитываются.

Функция COUNTIF — подсчет уникальных значений в Excel

4 ответа

Лучший ответ

Просто сделать :

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

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

7

Giorgos Betsos
20 Июл 2017 в 12:04

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

Ваш запрос будет в конечном итоге

SantiMunoz
13 Апр 2020 в 13:41

Оператор DISTINCT используется для возврата только различных (разных) значений

-4

sanay shukla
20 Июл 2017 в 07:02

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

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

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

Расширяя исходные данные примера следующим образом — давайте получим два набора данных на основе GroupID (1 и 2):

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

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

Вот решение:

Объяснено — FIRST_VALUE оценивает внутри каждого отдельного GroupID (так как мы установили PARTITION BY GroupID). В этом окне он оценивает строки для этого GroupID, случаи с ненулевыми значениями, а затем упорядочивает по идентификатору (по возрастанию или по убыванию). Поскольку это оконная функция, это помещает значение на уровне записей в их исходную гранулярность, поэтому мы также добавляем DENSE_RANK для ранжирования и даем нам возможность во внешнем запросе перейти к одной строке на GroupID.

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

2

troy
1 Фев 2019 в 14:31

Подсчет различных значений.

Используйте следующую универсальное выражение:

Помните, что это формула массива, поэтому вам следует нажать , вместо обычного Enter.

Кроме того, вы можете использовать функцию СУММПРОИЗВ и записать формулу обычным способом:

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

Например, чтобы сосчитать различные значения в диапазоне A2: A10, вы можете использовать выражение:

{=СУММ(1/СЧЁТЕСЛИ(A2:A10;A2:A10))}

или же

=СУММПРОИЗВ(1/СЧЁТЕСЛИ(A2:A10;A2:A10))

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

Этот метод подходит для текста, чисел, дат.

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

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

Тогда в расчёт попадёт и будет засчитана и пустая ячейка.

Как это работает?

Как вы уже знаете, мы используем функцию СЧЁТЕСЛИ, чтобы узнать, сколько раз каждый отдельный элемент встречается в указанном диапазоне. В приведенном выше примере, результат работы функции СЧЕТЕСЛИ представляет собой числовой массив:   {3:2:2:1:3:2:1:2:3}.

После этого выполняется ряд операций деления, где единица делится на каждую цифру из этого массива. Это превращает все неуникальные значения в дробные числа, соответствующие количеству повторов. Например, если число или текст появляется в списке 2 раза, в массиве создаются 2 элемента равные 0,5 (1/2 = 0,5). А если появляется 3 раза, в массиве создаются 3 элемента 0,333333. 

В нашем примере результатом вычисления выражения  1/СЧЁТЕСЛИ(A2:A10;A2:A10) является массив {0.333333333333333:0.5:0.5:1:0.333333333333333:0.5:1:0.5:0.333333333333333}.

Пока не слишком понятно? Это потому, что мы еще не применили функцию СУММ / СУММПРОИЗВ. Когда одна из этих функций складывает числа в массиве, сумма всех дробных чисел для каждого отдельного элемента всегда дает 1, независимо от того, сколько раз он появлялся. И поскольку все уникальные элементы отображаются в массиве как единицы (1/1 = 1), окончательный результат представляет собой общее количество всех встречающихся значений.

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

Помните, что все приведенные ниже выражения являются формулами массива и требуют нажатия .

Jump Search

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

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

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

С каждым прыжком мы сохраняем предыдущее значение и его индекс. Когда мы находим множество значений (блок), где < element < , мы выполняем линейный поиск с в качестве самого левого элемента и в качестве самого правого элемента в нашем множестве:

import math

def JumpSearch (lys, val):
    length = len(lys)
    jump = int(math.sqrt(length))
    left, right = 0, 0
    while left < length and lys <= val:
        right = min(length - 1, left + jump)
        if lys <= val and lys >= val:
            break
        left += jump;
    if left >= length or lys > val:
        return -1
    right = min(length - 1, right)
    i = left
    while i <= right and lys <= val:
        if lys == val:
            return i
        i += 1
    return -1

Поскольку это сложный алгоритм, давайте рассмотрим пошаговое вычисление для следующего примера:

>>> print(JumpSearch(, 5))

• Jump search сначала определит размер прыжка путем вычисления . Поскольку у нас 9 элементов, размер прыжка будет √9 = 3.
• Далее мы вычисляем значение переменной . Оно рассчитывается как минимум из двух значений: длины массива минус 1 и значения , которое в нашем случае будет 0 + 3 = 3. Поскольку 3 меньше 8, мы используем 3 в качестве значения переменной .
• Теперь проверим, находится ли наш искомый элемент 5 между и . Поскольку 5 не находится между 1 и 4, мы идем дальше.
• Затем мы снова делаем расчеты и проверяем, находится ли наш искомый элемент между и , где 6 — это 3 + jump. Поскольку 5 находится между 4 и 7, мы выполняем линейный поиск по элементам между и и возвращаем индекс нашего элемента:

4

Временная сложность jump search равна O(√n), где √n — размер прыжка, а n — длина списка. Таким образом, с точки зрения эффективности jump search находится между алгоритмами линейного и бинарного поиска.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Различные значения с учетом регистра.

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

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

Как вы помните, все формулы массива в Excel требуют нажатия .

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

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

Как видите, обе формулы дают одинаковые результаты.

Библиотека Pandas

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

В Pandas можно работать с данными трех структур:

• последовательности (Series) — одномерные массивы данных;
• фреймы (Data Frames) — объединение нескольких одномерных массивов в двумерный, то есть привычная таблица из строк и столбцов. Этот формат чаще всего используют аналитики;
• панели (Panels) — трехмерная структура из нескольких фреймов.

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

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

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

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

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

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

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

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

Сделаем это:

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

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

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

Если ваш список содержит как числа так и текст, и вы хотите посчитать только уникальные текстовые строки, добавьте функцию ЕТЕКСТ() в формулу массива, описанную выше:

Функция ЕТЕКСТ возвращает ИСТИНА, если исследуемое содержимое ячейки является текстом, и ЛОЖЬ в противоположном случае. Поскольку звездочка (*) в формулах массива работает как оператор И, то функция ЕСЛИ возвращает 1, только если рассматриваемое одновременно текстовое и уникальное, в противном случае получаем 0. И после того, как функция СУММ сложит все числа, вы получите количество уникальных текстовых значений в указанном диапазоне.

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

Рис3

Как вы можете видеть на скриншоте выше, мы получили общее количество уникальных текстовых значений, исключая пустые ячейки, числа, логические выражения ИСТИНА и ЛОЖЬ, а также ошибки.

Различные текстовые значения.

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

Как вы можете легко догадаться, мы просто добавим функцию ЕТЕКСТ и проверку условия:

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

{=СУММ(ЕСЛИ(ЕТЕКСТ(A2:A10);1/СЧЁТЕСЛИ(A2:A10; A2:A10);»»))}

Не забываем, что это формула массива.

Если в вашей таблице нет пустых ячеек и ошибок, то вы можете применить формулу, которая использует несколько функций: ЧАСТОТА, ПОИСКПОЗ, СТРОКА и СУММПРОИЗВ.

В общем виде это выглядит так:

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

Применяем формулу массива:

Она является более сложной, чем аналогичная, которая использует функцию ЧАСТОТА() для подсчета различных чисел. Это потому, что ЧАСТОТА() не работает с текстом. Поэтому ПОИСКПОЗ преобразует имена в номера позиций, которые может обрабатывать ЧАСТОТА().

Если какая-либо из ячеек в диапазоне пустая, вам необходимо использовать более сложную формулу массива, которая включает в себя функцию ЕСЛИ:

Примечание: поскольку логическая проверка в операторе ЕСЛИ содержит массив, то наше выражение сразу становится формулой массива, которая требует ввода через Ctrl+Shift+Enter. Поэтому же СУММПРОИЗВ была заменена на СУММ.Применительно к нашему примеру это выглядит так:

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

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

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

Уникальные значения — это значения, которые присутствуют в списке только один раз. Например:

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

Формула уникальных значений массива (заполняется нажатием Ctrl + Shift + Enter):

Можно воспользоваться и обычной формулой (вводится нажатием Enter):

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

• A2: A10 – исходных перечень данных.
• B1 — верхняя ячейка уникального списка минус одна строка. В этом примере мы начинаем создавать список уникальных в B2, и поэтому мы записываем B1 в формулу (B2 — 1 строка = B1). Если ваш список начинается, скажем, с ячейки C3, измените $B$1:B1 на $C$2:C2.

В этом примере мы извлекаем уникальные имена из столбца A (точнее из диапазона A2: A10), а следующий скриншот демонстрирует формулу в действии:

Вот наш порядок действий:

• Измените любую из формул в соответствии с вашим диапазоном данных.
• Введите ее в первую ячейку, с которой начнётся формирование списка (в данном примере B2).
• Если вы используете формулу массива, нажмите . Если вы выбрали обычную, нажмите просто клавишу .
• Скопируйте вниз настолько, насколько это необходимо, перетащив мышкой маркер заполнения. Поскольку обе формулы заключены в функцию ЕСЛИОШИБКА, вы можете скопировать вниз с запасом. Это не испортит ваши данные какими-либо ошибками, независимо от того, сколько уникальных значений было извлечено.

1. Встроенная функция range.

Встроенная функция range() используется для построения числовых последовательностей. В примере используем команду и функцию range для выполнения ровно 10 итераций с выводом значения от 0 до 10.

>>> for x in range(10)
…     print(x, end=’ ‘)
…0 1 2 3 4 5 6 7 8 9

При передаче только одного аргумента, последовательность будет начинаться с 0 и до значения аргумента (10), не включая его. Одна из самых распространённых ошибок, это когда разработчик предполагает, что значение аргумента функции range включается в сгенерированную последовательность. 

Приведем пример с двумя аргументами. Выведем последовательность от 20 до 30.

>>> for x in range(20, 30)
…     print(x, end=’ ‘)
…20 21 22 23 24 25 26 27 28 29

2.2. Создание числового списка с помощью функции range. 

С помощью функции range удобно строить числовые списки. 

>>> numbers = list(range(5, 25))
>>> print(numbers)

Функция range может создавать последовательности, пропуская числа в заданном диапазоне. Например, приведем пример построения списка от 0 до 100, с шагом 15.

>>> numbers = list(range(0, 100, 15))
>>> print(numbers)

С помощью цикла for и функции range можно создавать практически любой числовой список. К примеру, создадим список квадратов всех числе от 1 до 10. Операция возведения в степень обозначается двумя звездочками (**).

>>> a = []
>>> for i in range(1, 11)
…     a.append(i**2)
…
>>> print(a)

На каждом цикле переменная возводится в квадрат и добавляется в список. 

2.4. Перебор списка по индексам с помощью функции range

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

>>> list_1 =
>>> index ((list_1)):
…     list_1 += 100
…
>>> print(list_1)

В начале определим длину списка с помощью функции. Затем это значение передается функции. В нашем примере длина списка равна 6. С каждым проходом цикла , index принимает значения от 0 до 6 (не включая 6). В теле цикла мы вызываем список list_1 и обращаемся к элементам списка по индексам, равные значению переменной index. Каждое значение индекса мы увеличивает на 100. Затем с помощью функции print выводим список на экран и видим что все значения увеличены на 100. 

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

>>> list_4 =
>>> list_5 =
>>> index ((list_4)):
…     print(list_4, list_5)
…Artem Ivanov
Serg Petrov
Georgy Sidorov
Petr Ulyanov

В результате получили общие данные на экране.

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

>>> list_1 =
>>> list_2 =
>>> list_3 =
>>> list_new = []
>>> index ((list_1)):
…     list_new.(list_1 * list_2 * list_3)
…
>>> print(list_new)

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

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

Использование расширенного фильтра.

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

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

Как видите, мы проверили колонку B, и затем список уникальных наименований товара, найденных в ней, поместили в столбец K.

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

Теперь немного усложним задачу.

Если требуется искать записи не по одному, а по нескольким столбцам, то можно их предварительно «склеить» при помощи функции СЦЕПИТЬ.

Записываем это в столбец F и копируем вниз. Получаем вспомогательную колонку.

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

Но искать уникальные мы по-прежнему можем только в одном столбце. Вот для этого нам и пригодится вспомогательная колонка F с объединенными данными. Ее то мы и указываем в поле «Диапазон условий».

Все остальное – так же, как и в предыдущем примере.

В результате мы получили все имеющиеся в таблице комбинации «Заказчик — Товар» на основе данных во вспомогательном столбце F.

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

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

Различные текстовые значения с условием.

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

Чтобы решить эту проблему, вам может помочь этот вариант:

Введите это в пустую ячейку, куда вы хотите поместить результат, F2, например. А затем нажмите Shift + Ctrl + Enter вместе, чтобы получить правильный результат.

Поясним: здесь A2:A18 это список покупателей, с учётом которого вы ограничиваете область расчётов, B2: B18 — перечень товаров, в котором вы хотите посчитать уникальные значения, Е2 содержит критерий, на основании которого подсчет ограничивается только конкретным покупателем.

Второй способ.

Для уникальных значений в диапазоне с критериями, вы можете использовать формулу массива, основанную на функции ЧАСТОТА.

Применительно к нашему примеру:

С учетом ограничений ЕСЛИ() функция ПОИСКПОЗ определяет порядковый номер только для строк, которые соответствуют критериям.

Если какая-либо из ячеек в диапазоне критериев пустая, вам необходимо скорректировать расчёт, добавив дополнительно ЕСЛИ для обработки пустых ячеек.  Иначе они будут переданы в функцию ПОИСКПОЗ, которая в ответ сгенерирует сообщение об ошибке.

Вот что получилось после корректировки:

То есть все действия и расчёты мы производим, если в столбце B нам встретилась непустая ячейка: ЕСЛИ(B2:B10 <> «»….

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

Поясним. Определим, сколько наименований товара находилось в первой партии первого покупателя.

Критерии запишем в G2 и G3.

В общем виде это выглядит так:

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

В первой партии 2 наименования товара, хотя и 3 позиции.