Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как подсчитать дубликаты между двумя столбцами в Excel?

Как подсчитать дубликаты между двумя столбцами в Excel?

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

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

Значения Branley, Doyle, Doyle, Doyle и Doyle представляют собой два уникальных значения: Andrew и Doyle.

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

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

Установите активную ячейку в пустой диапазон или выделите диапазон ячеек.

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

Чтобы использовать опцию расширенной фильтрации, перейдите на вкладку «Сортировка и фильтр».

Откроется диалоговое окно Advanced Filter.

Нажмите кнопку Скопировать в другое место.

В поле Copy to введите ссылку на ячейку.

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

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

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

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

Для решения этой задачи объедините функции ЕСЛИ,СУММ, ФАКТОР, ЛЕН:

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

Сложите итоги, используя функцию суммирования.

Читайте так же:
Как переместить каждую вторую строку в столбец в Excel

Подсчитывает количество уникальных значений с FREQUENCY. В QUANTITY текст и null игнорируются. Первое вхождение определенного значения возвращается с подсчетом вхождений. Для каждого вхождения того же значения после первого возвращается ноль.

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

Найдите пустые ячейки с помощью функции LEN. В пустой ячейке длина равна 0.

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

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

Чтобы оценить функцию шаг за шагом, оставьте ячейку с формулой, а затем нажмите Evaluate Formula на вкладке Formulas (Формулы) в группе Formula Relations (Отношения формул).

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

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

L EN возвращает количество символов в строке текста.

Читайте так же:
Как показать линию тренда на графике только в Excel?

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

В функции IF возвращается одно значение, если указанное вами условие дает результат TRUE, и другое — если FALSE.

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

Если вам нужна помощь, вы можете спросить в сообществе Excel Tech Community или сообществе Answers.

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

Количество одинаковых пар в двух столбцах

Для создания подсчета значений, которые появляются в списке или таблице, можно использовать функцию СКРИН. В примере формула в D5 имеет вид:

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

В паре 1 диапазон B:B (вводится полная ссылка на столбец), а критерий — B5. При этом будет подсчитываться каждое значение в столбце B само по себе.

В паре 2 диапазон составляет C:C, а критерий — C5. Сама пара вернет подсчет для каждого значения в столбце C.

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

Количество длинных чисел без СЧЁТЕСЛИ

Предисловие

Это слишком длинное вступление, но контекст важен, извините!

Если вы пытаетесь подсчитать очень длинные числа (16+ цифр) в диапазоне с СЧЁТЕСЛИ, вы можете увидеть неправильные результаты, из-за ошибки в том, как определенные функции обрабатывают длинные числа, даже если эти числа сохраняются в виде текста. Рассмотрим приведенное ниже изображение. Все счетчики в колонке D являются неправильными — Хотя каждое число в столбце B является уникальным, количество, рассчитанное СЧЁТЕСЛИ предлагает эти цифры, являющиеся дубликатами.

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

Количество длинных чисел

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

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

Однако если вы попытаетесь использовать SCHOTESLY для вычисления числа с более чем 15 цифрами (даже если оно хранится в виде текста), вы можете получить ненадежные результаты. Это происходит потому, что SCHOTESLY внутренне преобразует длинное значение обратно в число в какой-то момент во время обработки, что приводит к 15-значному ограничению, описанному выше. Если не все цифры присутствуют, некоторые числа могут быть посчитаны как дубликаты при подсчете с помощью BATCHESLY.

Решение

Одно из решений — заменить формулу для ACCOUNT формулой с использованием SUM или SUMPROST. В приведенном примере формула E5 выглядит следующим образом:

S UMM PROVEN используется для генерации правильного числа в диапазоне B5:B9.

В качестве первого шага SUMMproV сравнивает диапазон с колонкой B, значением из текущей строки. Результатом является массив True/False.

Кроме того, двойное отрицание позволяет преобразовать значения TRUE / FALSE в значения 1/0.

Наконец, SUMPROIZE просто складывает элементы матрицы и рассчитывает результат.

Формула таблицы

Читайте так же:
Как переместить или преобразовать заголовки столбцов в заголовки строк в Excel?

Также вместо SUMPROIZE можно использовать функцию SUMM, но эта функция представляет собой формулу массива, которую нужно вводить с помощью Ctrl + Shift + Enter:

Другие особенности с данной проблемой

Я сам не проверял, но похоже, что некоторые функции имеют те же проблемы, включая SUMMESLI, SUMMESLIMN, SUMMESLIMN, SUMMESLIMN, SUMMESLIMN и SUMMESLIMN.

Количество проданных и оставшихся

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

Количество проданных и оставшихся

В примере формула в F7 имеет вид:

Функция SCAN подсчитывает непустые ячейки, содержащие числа или текст. Первый SCAN подсчитывает непустые ячейки в диапазоне B5: B11 и вычисляет число 7:

Вторая функция SCRTZ сделает то же самое с диапазоном C5:C11 и вычислит 3, поскольку в этом диапазоне три непустые ячейки:

Таким образом, вся формула сводится к 7 — 3 и рассчитывает 4.

Обратите внимание, что в этом случае значения, которые появляются в колонке C не имеет значения. Они могут быть кодами из столбца B , словом «да», или просто «х».

Тестовый матч.

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

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