000818.ru

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

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

Как найти совпадение в двух столбцах excel

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

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

Мы будем использовать Условное форматирование.

  • Выбираем ячейки, которые необходимо сравнить;
  • Во вкладке Главная переходим "Условное форматирование -> Правила выделения ячеек -> Повторяющиеся значения";

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

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

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

Чтобы повторить значения, выберите таблицу и перейдите в раздел Повторные значения. Все совпадения будут выделены.

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

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

С помощью этого метода можно искать буквы, слова, символы, тексты и т.д.

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

Как найти уникальные значения в Google Таблицах

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

  • Использование инструмента «Удаление дубликатов», встроенного в Google Таблицы.
  • Использование функции UNIQUE.

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

Обратите внимание, что в строках 2 и 5, а также в строках 6 и 7 есть повторяющиеся записи.

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

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

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

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

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

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

Вот упрощенное изображение этой проблемы.

Вы можете получить этот результат, поиграв с функцией БПФ:

То есть, мы нашли пересечение двух диапазонов, где VRP вернул значение. Фильтруя по #N/D в каждом массиве, мы получаем список значений для того, что слева, но отсутствует справа, и того, что справа, но отсутствует слева. Это все, что мы можем получить от стандартного подхода VRP.

Недостатки стандартного подхода:

  • Мы приложили к этому много усилий. Если у нас много таких таблиц, этот метод не подходит. Это и трудоемко, и слишком легко запутаться.
  • Как мы знаем, VRP ищет первое совпадение, что означает, что мы можем не знать, если, например, в левой таблице есть два значения 040310475653, а в правой — только одно, особенно если таблицы большие.
  • В случае #H/D мы также не поймем структуру аномалий в наших данных. Например, тот факт, что значение 40310307297 справа встречается дважды.
  • У нас не будет никакой информации о том, сколько уникальных значений встречается в каждой таблице.

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

Основные возможности предлагаемого инструмента:

  • Комплексное сравнение двух списков с выявлением всех возможных аномалий в данных
  • Сравнение происходит либо в одном месте с заданными пользователем интервалами (столбец вставляется справа), либо в отдельной новой электронной таблице
  • Сравнение результатов по цвету
  • Предоставление подробной статистики по результатам сравнения (вставляется в комментарии в ячейке заголовка столбца)
  • Автоматическая регистрация столбца данных,
  • Две модели сравнения
  • Сравнение в текстовом поле, если требуется
  • Интервалы с/без заголовков
  • Два типа сортировки
  • Возможность изменить стандартные режимы сравнения на свои собственные
Читайте так же:
Как открывать гиперссылки без мыши в Excel?

Используемая концепция сравнения списков

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

  1. Две сравниваемые области называются левой и правой областями. Это очевидно и естественно, если таблицы находятся на одном листе. Если листы разные, левой областью может быть стол, лист которого находится слева от листа другого стола.
  2. Каждому значению в сравниваемых списках присваивается СТАТУС, который указывает, как это значение соотносится с соответствующим значением в другой таблице и (!) с такими же значениями в собственной таблице, если значение повторяется.
  3. Существует 2 набора статусов: упрощенный и обычный.
  4. Упрощенные статусы: ОБА, ЛЕВЫЙ, ПРАВЫЙ. BOTH — значение есть в обоих столбцах. Например, если значение «5» встречается дважды в левой таблице и трижды в правой таблице, все эти строки будут иметь статус BOTHLEFT — значение встречается только в левой таблицеRIGHT — значение встречается только в правой таблице
  5. BOTH — значение встречается в обоих столбцах. Например, если значение «5» появляется дважды в левой таблице и трижды в правой таблице, все эти строки будут иметь статус BOTH
  6. LEFT — значение появляется только в левой таблице
  7. RIGHT — значение появляется только в правой таблице
  8. Ordinary Statuses (можно выбрать набор статусов, которые будут использоваться в форме управления для данного инструмента): Группа BOTH : L1R1 — левая и правая области имеют по одному значениюLnRn — левая и правая области имеют несколько (более 1) значений с каждой стороны. L1Rn — левая область имеет одно значение, правая — несколькоLnR1 — левая область имеет несколько значений, правая — одно значениеЛевая группа : L1R0 — левая область имеет одно значение, правая — ни одногоLnR0 — левая область имеет несколько одинаковых значений, правая — ни одногоПравая группа : L0R1 — нет таких значений в левой области, одно в правой областиL0Rn — нет таких значений в левой области, несколько (более одного) в правой области
  9. BOTHGroup : L1R1 — левая и правая области имеют по одному значениюLnRn — левая и правая области имеют по несколько (более одного) значений с каждой стороны. L1Rn — слева — одно значение, справа — одно значение. […]

Результаты работы инструмента

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

Мы видим, что получается следующее изображение:

  1. В левом списке есть 12 значений, которых нет в правом списке (статус L1R0 )
  2. В левом списке есть 040310475653 дублирующихся значений (статус LnR1 )
  3. В списках совпадений один-к-одному есть только 3 значения (статус L1R1 )
  4. В правом списке есть 7 значений которые не появляются в левом списке (статус L0R1)
  5. В правом списке есть 4 строки (две с двумя значениями), которые не появляются в левом списке и имеют дублирующие значения в правом списке (статус L0Rn)
  6. В левом и правом списках есть 2 значения, которые появляются в левом списке, но имеют дублирующие значения в правом списке (статус L1Rn)
Читайте так же:
Как начать нумерацию строк с 0 вместо 1 в Excel?

И режим комбинированного списка :

Вы можете видеть, что моя утилита все хорошо организовала!

Пользовательский интерфейс

Описание элементов управления:

  1. Списки для сравнения — Левый список и Правый список
    • Сравниваемые списки должны находиться в одной рабочей книге Excel
    • Списки могут находиться на разных листах рабочей книги
    • Если они находятся на одном листе, то столбец левого списка должен находиться слева от правого столбца
    • Рекомендуется заполнять эти поля ввода с помощью кнопок Выбор
    • С помощью кнопки Выбор достаточно указать ячейку нужного столбца, программа автоматически распространит свой выбор на всю область того столбца, к которому принадлежит данная ячейка. В этом смысле рекомендуется заменять пустые ячейки сравниваемых столбцов текстовыми константами, например, «Пусто».
  2. Разное — Выделить цветом
    • Если указана данная опция, то колонки со сравниваемыми значениями и колонка со статусами получают стандартное цветовое акцентирование, образцы которого вы можете видеть ниже
    • Простые статусы:
    • Обычные статусы:
  3. Разное — Объединить списки
    • По умолчанию эта опция не выбрана, и списки сравниваются там, где они расположены. Справа от столбца данных вставляется столбец, содержащий состояния сравнения.
    • Если выбрана эта опция, создается новый лист, на котором размещается таблица с объединенными единичными значениями сравниваемых списков. Эта таблица имеет 4 столбца: Значения, Количество слева, Количество справа, Статус.
    • Этот режим удобен для детального анализа всех аномалий в данных. В частности, это единственный способ увидеть конкретные номера рядов для штатов с буквой «n». Например, LnRn или L0Rn .
    • Только в режиме слияния списков вы можете сортировать таблицу, так как в противном случае вы рискуете повредить свои данные — поскольку указанный столбец может быть частью более крупной таблицы, но определение координат этой таблицы (для сортировки) слишком далеко выходит за рамки данного инструмента.
  4. Разное — Учёт регистра
    • Это полезно при разборе списков текста, когда может быть важен регистр текстовых значений.
  5. Разное — Статистика в комм .
    • При этом создаётся комментарий к ячейке, содержащей заголовок столбца со статусом сравнения данных.
    • Таких комментариев 2, если списки сравниваются на своих оригинальных местах, и один, если они объединены.
    • Комментарий выглядит примерно так
  6. Разное — Простые статусы
    • Вы можете выбрать между простым и обычным статусом. Эти статусы описаны выше.
  7. Разное — Есть заголовки
    • Укажите, должны ли списки иметь заголовки столбцов. Эта функция включена по умолчанию.
  8. Где сравниваем? — Оригинальное место или Отдельный лист
    • Этот параметр заблокирован, если выбрана опция Объединить списки.
    • В противном случае вы можете сами выбрать место.
  9. Сортировка — По значению или По статусу
    • По умолчанию эта опция отключена, если не выбрана опция Объединить списки, так как объединение происходит только на одном листе.
  10. Дополнительно — Альтернативные статусы
    • По умолчанию отключен. Включите его, если вы хотите по какой-то причине использовать свои статусы.
  11. Дополнительно — Обычные статусы или Упрощённые статусы
    • Статистика сохраняется в скрытом листе рабочей книги, в которой вы использовали этот инструмент, и будет загружена оттуда в форму в следующий раз. Поэтому вам не придется вводить их каждый раз.
  12. Сравнить списки
    • Кнопка, нажимаемая для запуска процедуры сравнения после выбора всех необходимых опций.
Читайте так же:
Как объединить ячейки без центрирования содержимого в Excel?

Выделить повторяющиеся значения в Excel цветом

В Excel слово «найти» часто используется в значении «найти и выделить» повторяющиеся значения.

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

Процедура находится на вкладке Home (Главная):

Условное форматирование - выделение повторяющихся значений на панели ExcelВызов процедуры условного форматирования для подсветки повторяющихся значений

Процедура интуитивно понятна и не требует перевода:

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

Выбор цвета для повторяющихся ячеекВторой шаг процедуры — выбор цветов заливки и текста

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

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

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

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

Однако есть и другие решения — подробнее об этом позже.

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

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

Выбор столбца в Google Таблицах

Когда столбец выделен, в строке меню нажмите Формат -> Условное форматирование.

Параметр меню «Условное форматирование»

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

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

Добавление правила условного форматирования Google Таблиц

Затем щелкните по полю «Формат ячеек Если» и выберите «Пользовательская формула. «.

Выбор правил условного форматирования настраиваемой формулы

Введите = countif (A: A, A1)> 1 в поле Значение или формула или замените все вхождения буквы A в формуле на выбранный вами столбец.

Предположим, вы хотите отформатировать столбец M, поэтому введите = countif (M: M, M1)> 1.

Добавление правил настраиваемой формулы

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

Изменение стилей условного форматирования

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

Поиск дубликатов в нескольких таблицах с помощью INNER JOIN

Ниже представлен упрощенный синтаксис для поиска дубликатов в нескольких таблицах:

Запрос на услугу описан здесь:

  • Col: имя столбцов, которые необходимо проверить и выбрать.
  • : функция, используемая для объединения двух таблиц.

В нашей базе данных есть две таблицы, «order1» и «order2», с колонкой «OrderNo» в обеих, как показано ниже.

Мы будем использовать соединение INNER join для объединения дубликатов двух таблиц по определенному столбцу. Оператор INNER JOIN извлекает все данные из обеих таблиц, соединяя их, а оператор ON соединяет столбцы с одинаковыми именами из обеих таблиц, например, OrderNo.

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

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