000818.ru

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

Как найти повторяющиеся значения в двух столбцах в Excel?

Как подсчитывать повторения в Excel?

Kak poschitat povtory 1 Как подсчитывать повторения в Excel? Добрый день, уважаемый читатель!

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

Для начала рассмотрим, как выглядят столбики с данными, повторения в которых, мы собственно и будем считать. К примеру, возьмём список сотрудников, которые совершают продажи. Kak poschitat povtory 2 Как подсчитывать повторения в Excel? Теперь можно посчитать, сколько раз сотрудник производил продажи, то есть попросту считаем сколько повторений его фамилии в столбике. Это возможно произвести несколькими способами:

  1. С помощью функции CUTESLY;
  2. С помощью функции CUTESLYMN;
  3. С помощью функции DLSTR;
  4. С помощью функций VBA.

Используя функцию СЧЁТЕСЛИ

Рассчитать такую сумму в Excel очень просто, достаточно использовать COUNCEL, и инструмент сделает все за вас. В нашем случае формула будет выглядеть следующим образом:

Kak poschitat povtory 3 1 Как подсчитывать повторения в Excel?

Первый аргумент, диапазон, имеет значения $B$2:$B$11, где B15 — диапазон ячеек для подсчета повторяющихся данных. Важно! Указание произвольного диапазона данных недопустимо. Особенность этого идентификатора в том, что он может ссылаться только на диапазон ячеек или на конкретную ячейку.

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

Дополнительная информация! Наряду с прямыми указаниями для поиска данных, функция КОНТРОЛЛЕР поддерживает подстановочные знаки. Такие знаки бывают двух типов «?» и «*», их можно использовать только при работе с текстовыми символами. Знак «*» позволяет заменить любое количество значений, а знак «?» — обратиться к кому-либо за помощью. знак заменяет только один символ.

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

Для работы с числовыми значениями используйте операторы сравнения: «>», «<", "<>«, «<>» i «=». Например, если вы хотите подсчитать числовые значения больше «нуля», введите «>0», а если вы хотите подсчитать непустые ячейки, введите «<>«.

Используем функцию СЧЁТЕСЛИМН

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

Для моего примера я добавлю категорию города и буду использовать формулы для сбора повторов из каждого города:

Kak poschitat povtory 4 Как подсчитывать повторения в Excel?

=CountLIMN($B$2:$B$11;B14;$C$2:$C$11;C14) Обратите внимание, что написание функции точно такое же, как и у предыдущей функции CountLIMN, разница только в числе. В нашем примере их два, но функция может обрабатывать и 127 диапазонов.

Работаем с функцией ДЛСТР

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

Kak poschitat povtory 5 Как подсчитывать повторения в Excel?

В результате при применении TLSTR к ячейкам «$B$2» и «B5″ мы получаем следующее значение: LSTR(TLSTR($B$2;B5;»»))/LSTR(B5). В ячейке «$B$2» и «B5» 71 символ. Затем мы воспользуемся функцией SUBSTITUTE, чтобы заменить текущее значение на ноль, в результате чего получим значение 47. Затем возьмем остаток нашего анализа, «71-47 = 24», и разделим его на количество символов в значении, «24/6 = 4», и в результате получим, сколько раз встречается искомый результат. Ответ: 4. Исходя из первой строки поиска, получается вот такой результат.

С помощью функций VBA

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

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

Kak poschitat povtory 6 Как подсчитывать повторения в Excel?

Для начала вам нужно запустить редактор макросов VBA и вставить новый модуль с помощью команд «Insert» — «Module». В созданное окно модуля вы вставляете код функции:

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

Выделите диапазон для поиска: конкретный столбец или таблицу, где будут распознаны дубликаты. Чтобы выделить таблицу, используйте сочетание двух клавиш «Ctrl+A».

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

Удалить дубликаты в Экселе

Найти дубликаты в программе Эксель

Как посчитать

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

Выделяем все, выделяем заголовки, нажимаем на вкладку «Вставить» и «Сводную таблицу».

Чтобы узнать больше о работе с перекрестными таблицами в Excel, прочитайте статью по этой ссылке.

Затем нажмите «OK» и выберите «На новый лист» из выпадающего списка. Ячейки диапазона уже выбраны в следующем окне.

С правой стороны перетащите первые три заголовка в область «Имена строк», а поле «Код» в область «Значения».

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

Чтобы упростить этот процесс, давайте выберем всех в колонке «Сумма по полю Код» и отсортируем их в порядке уменьшения.

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

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

Обнаружение повторяющихся строк

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

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

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

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

Предположим, что данные хранятся в столбцах A:C. Запишите в ячейку D2 :

Добавьте следующую формулу в ячейку E2. Он показывает, сколько раз встречается значение, которое мы нашли в столбце D:

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

Колонка E показывает количество вхождений этой строки в колонку D. Строки, которые не повторяются, имеют значение 1. Повторяющиеся строки имеют значение больше 1, что указывает на то, сколько раз встречалась эта строка.

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

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

excel объединить одинаковые значения

Перед тем как найти повторяющиеся ячейки в Excel, отсортируем поставщиков по их идентификатору. Переходим в ячейку A2 и выбираем на закладке «ДАННЫЕ» в разделе «Сортировка и фильтр» инструмент «Сортировка от А до Я».

excel объединить одинаковые значения

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

  1. Выделите диапазон A2:A11 и выберите инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматированных ячеек:». excel объединить одинаковые значения
  2. В поле для ввода формулы вводим следующе значение: =$A2<>$A3 excel объединить одинаковые значения
  3. Щелкните на кнопку «Формат», на вкладке «Граница» следует задать оформление для нижних границ строк. И ОК.
Читайте так же:
Как настроить размер пузырьков на пузырьковой диаграмме в Excel?

excel объединить одинаковые значения

В свете этого вы получите результат, показанный на рисунке.

excel объединить одинаковые значения

Excel смог объединить и изолировать повторяющиеся ячейки. В накладной все отсортированные поставщики теперь визуально разделены линиями.

Так как мы сортировали поставщиков по их идентификатору в формуле мы зафиксировали смешанной ссылкой столбец листа $A. Если значения в соседних ячейках столбца $A равные между собой тогда формула возвращает значения ЛОЖЬ и форматирование границе не применяется. Но если верхнее значение неравно (оператор <>) нижнему значению тогда формула возвращает значение ИСТИНА и применяется форматирования нижней границы целой строки (так как в смешанной ссылке номер строки не есть абсолютным, а является относительным адресом).

Полезный совет! Если нужно разграничить данные не по первому столбцу таблицы, по любому другому, тогда соответственно отсортируйте и просто укажите адрес столбца. Например, разграничим по повторяющимся датам, а не по поставщикам. Для этого сначала сортируем данные по датам, а потом используем условное форматирование немного изменив формулу: =$C2<>$C3

excel объединить одинаковые значения

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

Данная функция является частью надстройки MulTEx

  • Описание, установка, удаление и обновление
  • Полный список команд и функций MulTEx
  • FAQ по MulTEx
  • Скачать MulTEx

Вызов команды:
MulTEx -группа Ячейки/ДиапазоныЯчейкиОбъединить по значению

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

Вполне логично было бы перед тем как распечатать подобный отчет каждый город объединить визуально:
excel объединить одинаковые значения
Делать это руками будет долго и неинтересно, особенно, если в отчете более пары тысяч строк. Как раз для того, чтобы объединить несколько ячеек с одинаковыми значениями, идущими подряд, и предназначена команда «Объединить по значению». Для этого необходимо выделить диапазон ячеек для объединения и вызвать команду: MulTEx -группа Ячейки и ДиапазонЯчейкиОбъединить по значению.

Читайте так же:
Как объединить несколько строк в одну на основе дубликатов в Excel?

Можно объединить либо по строкам, либо по столбцам.
Направление:

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

Его нельзя отменить, поэтому будьте осторожны при его использовании. Вы можете выполнить обратную операцию, используя Disconnect с Fill.

Выводим все строки с повторяющимися значениями на T-SQL

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

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

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

Скриншот 4

Как вы можете видеть, сначала у нас есть все строки со значением 200, затем все строки со значением 300. Сортировка выполняется с помощью ORDER BY. Если вам интересно, что такое DuplicateValue, то это просто название выражения CTE, но вы можете назвать его как-то иначе.

Осторожно!

Мои книги и курсы могут дать вам исчерпывающее понимание T-SQL.

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