Skip to content
В этой статье мы рассмотрим разные подходы к одной из самых распространенных и, по моему мнению, важных задач в Excel – как найти в ячейках и в столбцах таблицы повторяющиеся значения.
Работая с большими наборами данных в Excel или объединяя несколько небольших электронных таблиц в более крупные, вы можете столкнуться с большим числом одинаковых строк.
И сегодня я хотел бы поделиться несколькими быстрыми и эффективными методами выявления дубликатов в одном списке. Эти решения работают во всех версиях Excel 2016, Excel 2013, 2010 и ниже. Вот о чём мы поговорим:
- Поиск повторяющихся значений включая первые вхождения
- Поиск дубликатов без первых вхождений
- Определяем дубликаты с учетом регистра
- Как извлечь дубликаты из диапазона ячеек
- Как обнаружить одинаковые строки в таблице данных
- Использование встроенных фильтров Excel
- Применение условного форматирования
- Поиск совпадений при помощи встроенной команды “Найти”
- Определяем дубликаты при помощи сводной таблицы
- Duplicate Remover – быстрый и эффективный способ найти дубликаты
Самой простой в использовании и вместе с тем эффективной в данном случае будет функция СЧЁТЕСЛИ (COUNTIF). С помощью одной только неё можно определить не только неуникальные позиции, но и их первые появления в столбце. Рассмотрим разницу на примерах.
Поиск повторяющихся значений включая первые вхождения.
Предположим, что у вас в колонке А находится набор каких-то показателей, среди которых, вероятно, есть одинаковые. Это могут быть номера заказов, названия товаров, имена клиентов и прочие данные. Если ваша задача – найти их, то следующая формула для вас:
=СЧЁТЕСЛИ(A:A; A2)>1
Где А2 – первая ячейка из области для поиска.
Просто введите это выражение в любую ячейку и протяните вниз вдоль всей колонки, которую нужно проверить на дубликаты.
Как вы могли заметить на скриншоте выше, формула возвращает ИСТИНА, если имеются совпадения. А для встречающихся только 1 раз значений она показывает ЛОЖЬ.
Подсказка! Если вы ищите повторы в определенной области, а не во всей колонке, обозначьте нужный диапазон и “зафиксируйте” его знаками $. Это значительно ускорит вычисления. Например, если вы ищете в A2:A8, используйте
=СЧЕТЕСЛИ($A$2:$A$8, A2)>1
Если вас путает ИСТИНА и ЛОЖЬ в статусной колонке и вы не хотите держать в уме, что из них означает повторяющееся, а что – уникальное, заверните свою СЧЕТЕСЛИ в функцию ЕСЛИ и укажите любое слово, которое должно соответствовать дубликатам и уникальным:
=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$17; A2)>1;”Дубликат”;”Уникальное”)
Если же вам нужно, чтобы формула указывала только на дубли, замените “Уникальное” на пустоту (“”):
=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$17; A2)>1;”Дубликат”;””)
В этом случае Эксель отметит только неуникальные записи, оставляя пустую ячейку напротив уникальных.
Поиск неуникальных значений без учета первых вхождений
Вы наверняка обратили внимание, что в примерах выше дубликатами обозначаются абсолютно все найденные совпадения. Но зачастую задача заключается в поиске только повторов, оставляя первые вхождения нетронутыми. То есть, когда что-то встречается в первый раз, оно однозначно еще не может быть дубликатом.
Если вам нужно указать только совпадения, давайте немного изменим:
=ЕСЛИ(СЧЁТЕСЛИ($A$2:$A2; A2)>1;”Дубликат”;””)
На скриншоте ниже вы видите эту формулу в деле.
Нетрудно заметить, что она не обозначает первое появление слова, а начинает отсчет со второго.
Чувствительный к регистру поиск дубликатов
Хочу обратить ваше внимание на то, что хоть формулы выше и находят 100%-дубликаты, есть один тонкий момент – они не чувствительны к регистру. Быть может, для вас это не принципиально. Но если в ваших данных абв, Абв и АБВ – это три разных параметра – то этот пример для вас.
Как вы могли уже догадаться, выражения, использованные нами ранее, с такой задачей не справятся. Здесь нужно выполнить более тонкий поиск, с чем нам поможет следующая функция массива:
{=ЕСЛИ(СУММ((–СОВПАД($A$2:$A$17;A2)))<=1;””;”Дубликат”)}
Не забывайте, что формулы массива вводятся комбиинацией Ctrl + Shift + Enter.
Если вернуться к содержанию, то здесь используется функция СОВПАД для сравнения целевой ячейки со всеми остальными ячейками с выбранной области. Результат возвращается в виде ИСТИНА (совпадение) или ЛОЖЬ (не совпадение), которые затем преобразуются в массив из 1 и 0 при помощи оператора (–).
После этого, функция СУММ складывает эти числа. И если полученный результат больше 1, функция ЕСЛИ сообщает о найденном дубликате.
Если вы взглянете на следующий скриншот, вы убедитесь, что поиск действительно учитывает регистр при обнаружении дубликатов:
Смородина и арбуз, которые встречаются дважды, не отмечены в нашем поиске, так как регистр первых букв у них отличается.
Как извлечь дубликаты из диапазона.
Формулы, которые мы описывали выше, позволяют находить дубликаты в определенном столбце. Но часто речь идет о нескольких столбцах, то есть о диапазоне данных.
Рассмотрим это на примере числовой матрицы. К сожалению, с символьными значениями этот метод не работает.
При помощи формулы массива
{=ИНДЕКС(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11);СТРОКА($1:$100)); НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ(НАИМЕНЬШИЙ(ЕСЛИ( СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11);СТРОКА($1:$100)); НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));0)=СТРОКА($1:$100);СТРОКА($1:$100));””);СТРОКА()-1))}
вы можете получить упорядоченный по возрастанию список дубликатов. Для этого введите это выражение в нужную ячейку и нажмите Ctrl+Alt+Enter.
Затем протащите маркер заполнения вниз на сколько это необходимо.
Чтобы убрать сообщения об ошибке, когда дублирующиеся значения закончатся, можно использовать функцию ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ИНДЕКС(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ( НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$E$11;$A$2:$E$11)>1;$A$2:$E$11); СТРОКА($1:$100));0)=СТРОКА($1:$100);СТРОКА($1:$100));””);СТРОКА()-1));””)
Также обратите внимание, что приведенное выше выражение рассчитано на то, что оно будет записано во второй строке. Соответственно выше него будет одна пустая строка.
Поэтому если вам нужно разместить его, к примеру, в ячейке K4, то выражение СТРОКА()-1 в конце замените на СТРОКА()-3.
Обнаружение повторяющихся строк
Мы рассмотрели, как обнаружить одинаковые данные в отдельных ячейках. А если нужно искать дубликаты-строки?
Есть один метод, которым можно воспользоваться, если вам нужно просто выделить одинаковые строки, но не удалять их.
Итак, имеются данные о товарах и заказчиках.
Создадим справа от наших данных формулу, объединяющую содержание всех расположенных слева от нее ячеек.
Предположим, что данные хранятся в столбцах А:C. Запишем в ячейку D2:
=A2&B2&C2
Добавим следующую формулу в ячейку E2. Она отобразит, сколько раз встречается значение, полученное нами в столбце D:
=СЧЁТЕСЛИ(D:D;D2)
Скопируем вниз для всех строк данных.
В столбце E отображается количество появлений этой строки в столбце D. Неповторяющимся строкам будет соответствовать значение 1. Повторам строкам соответствует значение больше 1, указывающее на то, сколько раз такая строка была найдена.
Если вас не интересует определенный столбец, просто не включайте его в выражение, находящееся в D. Например, если вам хочется обнаружить совпадающие строки, не учитывая при этом значение Заказчик, уберите из объединяющей формулы упоминание о ячейке С2.
Обнаруживаем одинаковые ячейки при помощи встроенных фильтров Excel.
Теперь рассмотрим, как можно обойтись без формул при поиске дубликатов в таблице. Быть может, кому-то этот метод покажется более удобным, нежели написание выражений Excel.
Организовав свои данные в виде таблицы, вы можете применять к ним различные фильтры. Фильтр в таблице вы можете установить по одному либо по нескольким столбцам. Давайте рассмотрим на примере.
В первую очередь советую отформатировать наши данные как «умную» таблицу. Напомню: Меню Главная – Форматировать как таблицу.
После этого в строке заголовка появляются значки фильтра. Если нажать один из них, откроется выпадающее меню фильтра, которое содержит всю информацию по данному столбцу. Выберите любой элемент из этого списка, и Excel отобразит данные в соответствии с этим выбором.
Вы можете убрать галочку с пункта «Выделить все», а затем отметить один или несколько нужных элементов. Excel покажет только те строки, которые содержат выбранные значения. Так можно обнаружить дубликаты, если они есть. И все готово для их быстрого удаления.
Но при этом вы видите дубли только по отфильтрованному. Если данных много, то искать таким способом последовательного перебора будет несколько утомительно. Ведь слишком много раз нужно будет устанавливать и менять фильтр.
Используем условное форматирование.
Выделение цветом по условию – весьма важный инструмент Excel, о котором достаточно подробно мы рассказывали.
Сейчас я покажу, как можно в Экселе найти дубли ячеек, просто их выделив цветом.
Как показано на рисунке ниже, выбираем Правила выделения ячеек – Повторяющиеся. Неуникальные данные будут подсвечены цветом.
Но здесь мы не можем исключить первые появления – подсвечивается всё.
Но эту проблему можно решить, использовав формулу условного форматирования.
=СЧЁТЕСЛИ($B$2:$B2; B2)>1
Результат работы формулы выденения повторяющихся значений вы видите выше. Они выделены зелёным цветом.
Чтобы освежить память, можете руководствоваться нашим материалом «Как изменить цвет ячейки в зависимости от значения».
Поиск совпадений при помощи команды «Найти».
Еще один простой, но не слишком технологичный способ – использование встроенного поиска.
Зайдите на вкладку Главная и кликните «Найти и выделить». Откроется диалоговое окно, в котором можно ввести что угодно для поиска в таблице. Чтобы избежать опечаток, можете скопировать искомое прямо из списка данных.
Затем нажимаем «Найти все», и видим все найденные дубликаты и места их расположения, как на рисунке чуть ниже.
В случае, когда объём информации очень велик и требуется ускорить работу поиска, предварительно выделите столбец или диапазон, в котором нужно искать, и только после этого начинайте работу. Если этого не сделать, Excel будет искать по всем имеющимся данным, что, конечно, медленнее.
Этот метод еще более трудоемкий, нежели использование фильтра. Поэтому применяют его выборочно, только для отдельных значений.
Как применить сводную таблицу для поиска дубликатов.
Многие считают сводные таблицы слишком сложным инструментом, чтобы постоянно им пользоваться. На самом деле, не все так запутано, как кажется. Для новичков рекомендую к ознакомлению наше руководство по созданию и работе со сводными таблицами.
Для более опытных – сразу переходим к сути вопроса.
Создаем новый макет сводной таблицы. А затем в качестве строк и значений используем одно и то же поле. В нашем случае – «Товар». Поскольку название товара – это текст, то для подсчета таких значений Excel по умолчанию использует функцию СЧЕТ, то есть подсчитывает количество. А нам это и нужно. Если будет больше 1, значит, имеются дубликаты.
Вы наблюдаете на скриншоте выше, что несколько товаров дублируются. И что нам это дает? А далее мы просто можем щелкнуть мышкой на любой из цифр, и на новом листе Excel покажет нам, как получилась эта цифра.
К примеру, откуда взялись 3 дубликата Sprite? Щелкаем на цифре 3, и видим такую картину:
Думаю, этот метод вполне можно использовать. Что приятно – никаких формул не требуется.
Duplicate Remover – быстрый и эффективный способ найти дубликаты в Excel
Теперь, когда вы знаете, как использовать формулы для поиска повторяющихся значений в Excel, позвольте мне продемонстрировать вам еще один быстрый, эффективный и без всяких формул способ: инструмент Duplicate Remover для Excel.
Этот универсальный инструмент может искать повторяющиеся или уникальные значения в одном столбце или же сравнивать два столбца. Он может находить, выбирать и выделять повторяющиеся записи или целые повторяющиеся строки, удалять найденные дубли, копировать или перемещать их на другой лист. Я думаю, что пример практического использования может заменить очень много слов, так что давайте перейдем к нему.
Как найти повторяющиеся строки в Excel за 2 быстрых шага
Сначала посмотрим в работе наиболее простой инструмент — быстрый поиск дубликатов Quick Dedupe. Используем уже знакомую нам таблицу, в которой мы выше искали дубликаты при помощи формул:
Как видите, в таблице несколько столбцов. Чтобы найти повторяющиеся записи в этих трех столбцах, просто выполните следующие действия:
- Выберите любую ячейку в таблице и нажмите кнопку Quick Dedupe на ленте Excel. После установки пакета Ultimate Suite для Excel вы найдете её на вкладке Ablebits Data в группе Dedupe. Это наиболее простой инструмент для поиска дубликатов.
- Интеллектуальная надстройка возьмет всю таблицу и попросит вас указать следующие две вещи:
- Выберите столбцы для проверки дубликатов (в данном примере это все 3 столбца – категория, товар и заказчик).
- Выберите действие, которое нужно выполнить с дубликатами. Поскольку наша цель – выявить повторяющиеся строки, я выбрал «Выделить цветом».
Помимо выделения цветом, вам доступен ряд других опций:
- Удалить дубликаты
- Выбрать дубликаты
- Указать их в столбце статуса
- Копировать дубликаты на новый лист
- Переместить на новый лист
Нажмите кнопку ОК и подождите несколько секунд. Готово! И никаких формул 😊.
Как вы можете видеть на скриншоте ниже, все строки с одинаковыми значениями в первых 3 столбцах были обнаружены (первые вхождения не идентифицируются как дубликаты).
Если вам нужны дополнительные возможности для работы с дубликатами и уникальными значениями, используйте мастер удаления дубликатов Duplicate Remover, который может найти дубликаты с первыми вхождениями или без них, а также уникальные значения. Подробные инструкции приведены ниже.
Мастер удаления дубликатов – больше возможностей для поиска дубликатов в Excel.
В зависимости от данных, с которыми вы работаете, вы можете не захотеть рассматривать первые экземпляры идентичных записей как дубликаты. Одно из возможных решений – использовать разные формулы для каждого сценария, как мы обсуждали в этой статье выше. Если же вы ищете быстрый, точный метод без формул, попробуйте мастер удаления дубликатов — Duplicate Remover. Несмотря на свое название, он не только умеет удалять дубликаты, но и производит с ними другие полезные действия, о чём мы далее поговорим подробнее. Также умеет находить уникальные значения.
- Выберите любую ячейку в таблице и нажмите кнопку Duplicate Remover на вкладке Ablebits Data.
- Вам предложены 4 варианта проверки дубликатов в вашем листе Excel:
- Дубликаты без первых вхождений повторяющихся записей.
- Дубликаты с 1-м вхождением.
- Уникальные записи.
- Уникальные значения и 1-е повторяющиеся вхождения.
В этом примере выберем второй вариант, т.е. Дубликаты + 1-е вхождения:
- Теперь выберите столбцы, в которых вы хотите проверить дубликаты. Как и в предыдущем примере, мы возьмём первые 3 столбца:
- Наконец, выберите действие, которое вы хотите выполнить с дубликатами. Как и в случае с инструментом быстрого поиска дубликатов, мастер Duplicate Remover может идентифицировать, выбирать, выделять, удалять, копировать или перемещать повторяющиеся данные.
Поскольку цель этого примера – продемонстрировать различные способы определения дубликатов в Excel, давайте отметим параметр «Выделить цветом» (Highlight with color) и нажмите Готово.
Мастеру Duplicate Remover требуется всего лишь несколько секунд, чтобы проверить вашу таблицу и показать результат:
Как видите, результат аналогичен предыдущему. Но здесь мы выделили дубликаты, включая и первое появление повторяющихся записей.
Никаких формул, никакого стресса, никаких ошибок – всегда быстрые и безупречные результаты 🙂
Итак, мы с вам научились различными способами обнаруживать повторяющиеся записи в таблице Excel. В следующих статьях разберем, что мы с этим можем полезного сделать.
Если вы хотите попробовать эти инструменты для поиска дубликатов в таблицах Excel, вы можете загрузить полнофункциональную ознакомительную версию программы. Будем очень признательны за ваши отзывы в комментариях!
подскажите как в ubuntu с помощью grep выдернуть только нужные строки из файла.
есть два файла 1.txt (2 милиона строк), 2.txt (400000 строк)
в 1.txt список строк которые нужно проверять на вхождение строк из 2.txt
если в 1.txt находим строку из 2.txt то добовляем в файл 3.txt всю строку.
Nick Volynkin♦
33.5k24 золотых знака128 серебряных знаков215 бронзовых знаков
задан 15 сен 2015 в 20:40
7
если достаточно вхождения строки из 2.txt в строку из 1.txt, то можно, например, так:
$ grep -F -f 2.txt 1.txt > 3.txt
пример:
$ cat 1
1
2
3
55
$ cat 2
3
4
5
$ grep -F -f 2 1
3
55
ответ дан 15 сен 2015 в 21:46
aleksandr barakinaleksandr barakin
67.7k208 золотых знаков76 серебряных знаков218 бронзовых знаков
7
Методы сравнения таблиц в Microsoft Excel
Смотрите такжеЕсли в результате получаем данные позиции закрашиваются т. д. ? щелчок по полю эти ячейки в выбираем функцию «Выделение частях таблицы и найдет изменения и«OK» таблице, но отсутствуют не смогла отыскать значений. элементы, которые имеют. Жмем по кнопке диапазон второй области.Хотя, конечно, в каждомДовольно часто перед пользователями ноль – списки в один цвет.И тоже самое ввода «Диапазон:» и красный свет. группы ячеек». шрифтом зеленого цвета выделит их..
в первой, формула во второй таблице
Аргумент данные, несовпадающие с«OK»
Способы сравнения
Далее обворачиваем полученное конкретном случае координаты Excel стоит задача идентичны. В противномПомогите пожалуйста! во второй таблице?
- выделите диапазон: A2:A15.Как работать с
- В появившемся окне ставим в области результатов.
- Важно:Запускается окно аргументов функции
выдает номера строк. два значения, которые«Критерий» соответствующими значениями первой. выражение скобками, перед будут отличаться, но сравнения двух таблиц случае – вНашел только это: А можно скопировать
И нажмите ОК. условным форматированием, как галочку у слов В левой нижней Средство сравнения электронныхИНДЕКС
Способ 1: простая формула
Отступаем от табличной области имеются в первомзадает условие совпадения. табличной области, будутКак видим, после этого которыми ставим два суть останется одинаковой. или списков для них есть различия.Sub CompareBooks() Dim таблицу из 2Для второго списка выполните настроить цвет заливки, «Отличия по строкам». части указаны условные таблиц доступно только. Данный оператор предназначен вправо и заполняем табличном массиве. В нашем случае выделены выбранным цветом. несовпадающие значения строк знакаЩелкаем по клавише выявления в них Формулу надо вводить myName As String,
книги в 1 те же действия шрифта в условном Нажимаем «ОК». обозначения, поясняющие значения с версиями Office для вывода значения, колонку номерами по
- Конечно, данное выражение для он будет представлятьСуществует ещё один способ будут подсвечены отличающимся«-»Enter отличий или недостающих как формулу массива, wB As Workbook книгу на 2 только имя присвойте форматировании, как написатьВ таблице выделились все цветов. профессиональный плюс 2013 и которое расположено в порядку, начиная от
того, чтобы сравнить
собой координаты конкретных применения условного форматирования оттенком. Кроме того,. В нашем случае
- , чтобы получить результаты элементов. Каждый юзер т.е. после ввода With Application.FileDialog(msoFileDialogFilePicker) .Title лист? – Таблица_2. А другие условия для ячейки с разнымиЕсли вы хотите сохранить Office 365 профессиональный плюс.
- определенном массиве в1 табличные показатели, можно ячеек первой табличной для выполнения поставленной как можно судить получилось такое выражение: сравнения. Как видим, справляется с этой формулы в ячейку = “Выберите ПЕРВЫЙПосле этого проще диапазон укажите C2:C15
выделения ячеек, строк, данными так. Excel результаты или проанализироватьОткройте средство сравнения электронных указанной строке.. Количество номеров должно применять и в области. задачи. Как и из содержимого строки–(A2:A7<>D2:D7) при сравнении первых задачей по своему, жать не на файл для сравнения” всего макрос написать – соответственно. т.д., читайте в
- сравнила данные в их в другом таблиц.Как видим, поле совпадать с количеством существующем виде, ноВыделяем первый элемент дополнительного предыдущие варианты, он формул, программа сделаетЩелкаем по кнопке ячеек обоих списков но чаще всегоEnter .Show If .SelectedItems.Count с циклом. Что-тоПолезный совет! Имена диапазонов статье «Условное форматирование ячейках двух столбцов
- приложении, экспортируйте ихВ левой нижней области«Номер строки» строк во второй есть возможность его столбца, в котором требует расположения обоих активной одну из«OK» программа указала показатель
- на решение указанного, а на = 0 Then типа такого можно присваивать быстрее в Excel». Получилось – данные ячейки в файл Excel выберите элементы, которые
- уже заполнено значениями сравниваемой таблице. Чтобы усовершенствовать. будет производиться подсчет сравниваемых областей на ячеек, находящуюся в.«ИСТИНА» вопроса тратится довольноCtrl+Shift+Enter
Exit Sub myNamei = 1 с помощью поля так. А2 и данными или скопируйте и хотите включить в функции ускорить процедуру нумерации,Сделаем так, чтобы те
количества совпадений. Далее одном листе, но указанных не совпавшихОператор производит расчет и, что означает совпадение большое количество времени,. = .SelectedItems(1) EndDo While Worksheets(“Лист1”).Cells(i, имен. Оно находитсяТретий способ. ячейки В2, т.д. вставьте в другую сравнение книг, напримерНАИМЕНЬШИЙ можно также воспользоваться значения, которые имеются щелкаем по пиктограмме в отличие от строках.
выводит результат. Как данных. так как далекоЕсли с отличающимися ячейками
- With Application.ScreenUpdating = 1) <> “” левее от строкиСравнить значения столбцов вЕсли мы сейчас нажмём программу, например Microsoft формулы, форматирование ячеек. От уже существующего маркером заполнения. во второй таблице,«Вставить функцию» ранее описанных способов,Произвести сравнение можно, применив видим, в нашемТеперь нам нужно провести не все подходы
надо что сделать, False Workbooks.Open Filename:=myName:If Worksheets(“Лист1”).Cells(i, 1) формул. Просто выделяйтеExcel формулой. на мышь, то Word. Можно также или макросы. Или там значения следуетПосле этого выделяем первую но отсутствуют в. условие синхронизации или метод условного форматирования. случае результат равен аналогичную операцию и к данной проблеме то подойдет другой
Set wB =
= Worksheets(“Лист2”).Cells(i, 1) диапазоны ячеек, аМожно сделать в выделения ячеек исчезнут. получить более точное просто выберите вариант отнять разность между ячейку справа от
Способ 2: выделение групп ячеек
первой, выводились отдельнымПроисходит запуск сортировки данных не Как и в числу с остальными ячейками являются рациональными. В быстрый способ: выделите Workbooks(ActiveWorkbook.Name) Dim myName1 Then ‘ Если в поле имен таблице дополнительный столбец
- Чтобы эти выделенные представление каждого листаSelect All нумерацией листа Excel колонки с номерами списком.Мастера функций будет являться обязательным, предыдущем способе, сравниваемые«1» обеих таблиц в то же время, оба столбца и As String, wB1 названия совпали
вводите соответствующее имя и установить в ячеек оставить, мы с отображением форматирования(Выделить все). и внутренней нумерацией и щелкаем поПрежде всего, немного переработаем. Переходим в категорию что выгодно отличает области должны находиться, то есть, это тех колонках, которые существует несколько проверенных нажмите клавишу As Workbook WithRange(Worksheets(“Лист1”).Cells(i, 1), Worksheets(“Лист1”).Cells(i, для диапазона и ячейках этого столбца
- можем закрасить эти ячеек, близкое кНа вкладке табличной области. Как значку
- нашу формулу«Статистические» данный вариант от на одном рабочем означает, что в мы сравниваем. Но алгоритмов действий, которыеF5 Application.FileDialog(msoFileDialogFilePicker) .Title = 10)).Select нажмите Enter.
- формулу. =А2=В2 ячейки или изменить тому, что выHome видим, над табличными«Вставить функцию»СЧЁТЕСЛИ. Находим в перечне ранее описанных. листе Excel и сравниваемых списках было
Способ 3: условное форматирование
можно просто провести позволят сравнить списки, затем в открывшемся “Выберите ВТОРОЙ файл’ здесь 10Теперь воспользуемся условным форматированием,Получится так. цвет шрифта в видите в Excel.
- (Главная) выберите элемент значениями у нас., а именно сделаем наименованиеПроизводим выделение областей, которые быть синхронизированными между найдено одно несовпадение. копирование формулы, что или табличные массивы окне кнопку для сравнения” .Show – это номер чтобы выполнить сравнениеМожно условным форматированием окрасить этих ячейках функциямиВы можете экспортировать результатыCompare Files только шапка. ЭтоОткрывается её одним из
- «СЧЁТЕСЛИ» нужно сравнить. собой. Если бы списки позволит существенно сэкономить
- в довольно сжатыеВыделить (Special) If .SelectedItems.Count = последнего столбца в двух списков в слова «Ложь» другим раздела «Шрифт» на в файл Excel,(Сравнить файлы). значит, что разницаМастер функций аргументов оператора. После его выделенияВыполняем переход во вкладкуПрежде всего, выбираем, какую были полностью идентичными, время. Особенно данный сроки с минимальной- 0 Then Exit таблице Excel. Нам нужно цветом или окрасить закладке «Главная», пока более удобный дляВ диалоговом окне составляет одну строку.. Переходим в категориюЕСЛИ щелкаем по кнопке под названием табличную область будем то результат бы
фактор важен при затратой усилий. ДавайтеОтличия по строкам (Row Sub myName1 =With Selection.Interior получить следующий результат: эти ячейки.
- эти ячейки выделены. чтения. ВыберитеCompare Files Поэтому дописываем в«Статистические». Для этого выделяем«OK»«Главная» считать основной, а был равен числу сравнивании списков с подробно рассмотрим данные differences)
- .SelectedItems(1) End With.ColorIndex = 4Можно написать такую Например, так.
- Home > Export Results(Сравнение файлов) в полеи производим выбор первую ячейку, в.
- . Делаем щелчок по в какой искать«0» большим количеством строк. варианты.. В последних версиях
Application.ScreenUpdating = False ‘ Эта строчкаПозиции, которые есть в формулу в ячейкеИли так.(Главная > Экспорт результатов). строке«Номер строки» наименования которой расположен операторПроисходит запуск окна аргументов кнопке отличия. Последнее давайте.Процедуру копирования легче всегоСкачать последнюю версию
- Excel 2007/2010 можно Workbooks.Open Filename:=myName1: Set
- красит всю строку Таблице_1, но нет С2. =СУММ(ЕСЛИ(A2:A6<>B2:B6;1;0)) НажимаемСравнить данные в несколькихЧтобы скопировать результаты иCompareзначение«НАИМЕНЬШИЙ»СЧЁТЕСЛИ оператора«Условное форматирование» будем делать воТаким же образом можно
- выполнить при помощи Excel также воспользоваться кнопкой wB1 = Workbooks(ActiveWorkbook.Name) в зеленый цвет в Таблцие_2 будут «Enter». Копируем формулу столбцах вставить их в(Сравнить) с помощью«-1». Щелкаем по кнопке. В строке формул
- СЧЁТЕСЛИ. В активировавшемся списке второй таблице. Поэтому производить сравнение данных маркера заполнения. НаводимЧитайте также: Сравнение двухНайти и выделить (Find Windows(wB1.Name).Activate: ActiveSheet.Unprotect numRowProv.Pattern = xlSolid отображаться зеленым цветом. по столбцу. ТогдаExcel. другую программу, выберите
кнопки обзорабез кавычек.«OK» перед ней дописываем. Как видим, наименования выбираем позицию выделяем список работников, в таблицах, которые курсор на правый документов в MS & Select) - = InputBox(“Укажите номерEnd With В тоже время в столбце сЗдесь мы сравнилиHome > Copy Results toвыберите более раннююВ поле
. выражение полей в этом
«Правила выделения ячеек» находящийся в ней.
Способ 4: комплексная формула
расположены на разных нижний угол ячейки, Word Выделение группы ячеек строки, с которойEnd If позиции, находящиеся в разницей будут стоять три столбца в Clipboard версию книги. Помимо«Массив»
Функция«ЕСЛИ» окне соответствуют названиям. В следующем меню Переместившись на вкладку листах. Но в где мы получилиСуществует довольно много способов (Go to Special) необходимо начать сравнение
i = i + 1
Таблице_2, но отсутствующие цифры. Единица будет таблице, предварительно выделив(Главная > Копировать результаты выбора файлов, сохраненныхуказываем адрес диапазона
НАИМЕНЬШИЙбез кавычек и аргументов. делаем выбор позиции«Главная» этом случае желательно, показатель сравнения табличных областей
- на вкладке В ПЕРВОМ файле:”,Loop в Таблице_1, будут стоять, если есть все три столбца в буфер обмена).
- на компьютере или значений второй таблицы., окно аргументов которой открываем скобку. Далее,Устанавливаем курсор в поле«Повторяющиеся значения», щелкаем по кнопке чтобы строки в«ИСТИНА» в Excel, ноГлавная (Home)
- “Номер строки”) numRowЕсли надо, чтобы подсвечены синим цветом. различия, а «нуль» таблицы. Сравниваются данныеЧтобы отобразить форматирование ячеек в сети, можно
При этом все было раскрыто, предназначена чтобы нам легче«Диапазон».«Условное форматирование» них были пронумерованы.. При этом он все их можноExcel выделит ячейки, отличающиеся = InputBox(“Укажите номер совпали не толькоВыделите диапазон первой таблицы: – данные в в ячейках построчно из книги, выберите также ввести веб-адрес, координаты делаем абсолютными, для вывода указанного
было работать, выделяем. После этого, зажавЗапускается окно настройки выделения, которая имеет месторасположение
В остальном процедура должен преобразоваться в разделить на три содержанием (по строкам). строки, с которой названия но и, A2:A15 и выберите ячейках одинаковые. Получится (А2, В2, С2,т.д.).Home > Show Workbook Colors ведущий к книге, то есть, ставим по счету наименьшего в строке формул
- левую кнопку мыши, повторяющихся значений. Если на ленте в сравнения практически точно черный крестик. Это большие группы: Затем их можно необходимо начать сравнение например, размеры, записанные инструмент: «ГЛАВНАЯ»-«Условное форматирование»-«Создать так. Получилось так.
- (Главная > Показать цвета сохраненной на сайте. перед ними знак значения. значение выделяем все значения вы все сделали блоке такая, как была и есть маркерсравнение списков, находящихся на обработать, например: ВО ВТОРОМ файле:”, во 2 столбец, правило»- «Использовать формулуЧетвертыйКак сравнить даты в книги).
- В диалоговом окне доллара уже ранееВ поле«ЕСЛИ» столбца с фамилиями правильно, то в«Стили» описана выше, кроме заполнения. Жмем левую одном листе;залить цветом или как-то “Номер строки”) numCol тогда для определения форматированныхсExcel.Допустим, в вашей организацииCompare Files
описанным нами способом.«Массив»и жмем по второй таблицы. Как данном окне остается. Из выпадающего списка того факта, что
кнопку мыши исравнение таблиц, расположенных на еще визуально отформатировать = Cells.SpecialCells(xlLastCell).Column -If Worksheets(“Лист1”).Cells(i, 1) ячеек:».
- пособ.Можно сравнить даты. ожидается аудиторская проверка.(Сравнение файлов) вЖмем на кнопкуследует указать координаты иконке видим, координаты тут только нажать на переходим по пункту при внесении формулы тянем курсор вниз разных листах;очистить клавишей 1 If numRow = Worksheets(“Лист2”).Cells(i, 1)В поле ввода введитеМожно Принцип сравнения дат Вам нужно проследить строке«OK» диапазона дополнительного столбца«Вставить функцию» же попадают в кнопку
- «Управление правилами» придется переключаться между на количество строчексравнение табличных диапазонов вDelete >= numRowProv Then Then ‘ Если формулу:объединить таблицы Excel тот же – данные в важныхTo.«Количество совпадений»
. указанное поле. Но«OK». листами. В нашем в сравниваемых табличных разных файлах.заполнить сразу все одинаковым Razn = numRow названия совпалиЩелкните по кнопке «Формат»с отчетами. Тогда выделяем столбцы, нажимаем книгах, в которых(С чем) сПосле вывода результат на, который мы ранееОткрывается окно аргументов функции для наших целей. Хотя при желании
Активируется окошко диспетчера правил. случае выражение будет массивах.Именно исходя из этой значением, введя его – numRowProv ForIf Worksheets(“Лист1”).Cells(i, 2) и на вкладке все одинаковые данные на кнопку «Найти показаны изменения по помощью кнопки обзора экран протягиваем функцию преобразовали с помощьюЕСЛИ следует сделать данный в соответствующем поле Жмем в нем
- иметь следующий вид:Как видим, теперь в классификации, прежде всего, и нажав i = numRow = Worksheets(“Лист2”).Cells(i, 2) «Заливка» укажите зеленый соберутся в одну и выделить». Выбираем месяцам и по
- выберите версию книги, с помощью маркера функции. Как видим, первое адрес абсолютным. Для данного окошка можно на кнопку=B2=Лист2!B2 дополнительном столбце отобразились подбираются методы сравнения,Ctrl+Enter
- To Cells(Rows.Count, 1).End(xlUp).Row Then ‘ И цвет. На всех строку, но можно функцию «Выделение группы годам. Это поможет которую хотите сравнить заполнения до концаЕСЛИ поле окна уже этого выделяем данные выбрать другой цвет
- «Создать правило»То есть, как видим, все результаты сравнения а также определяютсяудалить все строки с iprov = i размеры тоже совпали
- окнах жмем ОК. будет посмотреть и ячеек», ставим галочку вам найти и с более ранней. столбца вниз. Как. Делаем все ссылки заполнено значением оператора координаты в поле выделения.
- . перед координатами данных, данных в двух конкретные действия и выделенными ячейками, используя – Razn ForRange(Worksheets(“Лист1”).Cells(i, 1), Worksheets(“Лист1”).Cells(i,
Выделите диапазон первого списка: отдельно данные по у слов «Отличия исправить ошибки раньше,Примечание: видим, обе фамилии, абсолютными.СЧЁТЕСЛИ и жмем наПосле того, как мыВ запустившемся окне производим
которые расположены на колонках табличных массивов. алгоритмы для выполнения команду y = 1 10)).Select C2:C15 и снова магазинам. Как это по строкам». Нажимаем чем до них Можно сравнивать два которые присутствуют воВ поле
- . Но нам нужно клавишу произведем указанное действие, выбор позиции других листах, отличных В нашем случае задачи. Например, приГлавная – Удалить - To numCol If
- ’ здесь 10 выберите инструмент: «ГЛАВНАЯ»-«Условное сделать, смотрите в «ОК». доберутся проверяющие. файла с одинаковыми второй таблице, но«K» дописать кое-что ещёF4 все повторяющиеся элементы«Использовать формулу» от того, где не совпали данные проведении сравнения в Удалить строки с wB1.Sheets(“Лист1”).Cells(i, y) <> – это номер форматирование»-«Создать правило»- «Использовать статье «Как объединитьЗдесь расхождение дат вСредство сравнения электронных таблиц именами, если они отсутствуют в первой,указывается, какое по в это поле.. будут выделены выбранным
- . В поле выводится результат сравнения, только в одной разных книгах требуется листа (Home - wB.Sheets(“Лист1”).Cells(iprov, y) Then последнего столбца в формулу для определения таблицы в Excel». ячейках строк второй можно использовать не хранятся в разных выведены в отдельный счету наименьшее значение Устанавливаем туда курсорКак видим, ссылка приняла
- цветом. Те элементы,«Форматировать ячейки» указывается номер листа строке. При их одновременно открыть два Delete – Delete wB1.Sheets(“Лист1”).Cells(i, y).Interior.Color =
таблице форматированных ячеек:».Пятый способ. и третьей. только для сравнения папках. диапазон. нужно вывести. Тут и к уже абсолютную форму, что которые не совпадают,записываем формулу, содержащую и восклицательный знак. сравнении формула выдала файла Excel. Rows) 255 End IfWith Selection.InteriorВ поле ввода введитеИспользуемЕсть еще один содержимого листов, но
Нажмите кнопкуПри сравнении диапазонов в указываем координаты первой существующему выражению дописываем характеризуется наличием знаков останутся окрашенными в адреса первых ячеекСравнение можно произвести при результатКроме того, следует сказать,
и т.д. Next Next End.ColorIndex = 4
- формулу:функцию «СЧЕТЕСЛИ» в способ и для поискаОК разных книгах можно ячейки столбца с«=0» доллара. свой изначальный цвет диапазонов сравниваемых столбцов,
Способ 5: сравнение массивов в разных книгах
помощи инструмента выделения«ЛОЖЬ» что сравнивать табличныеЕсли списки разного размера If If numRow ‘ Эта строчкаЩелкните по кнопке «Формат»Excelсравнить даты в Excel различий в коде, чтобы выполнить сравнение. использовать перечисленные выше нумерацией, который мыбез кавычек.Затем переходим к полю (по умолчанию белый). разделенные знаком «не групп ячеек. С. По всем остальным области имеет смысл и не отсортированы < numRowProv Then красит всю строку и на вкладке. Эта функция посчитает- сравнить периоды Visual Basic дляПримечание:
способы, исключая те недавно добавили. Адрес
После этого переходим к«Критерий» Таким образом, можно равно» ( его помощью также строчкам, как видим, только тогда, когда (элементы идут в Razn = numRowProv в зеленый цвет «Заливка» укажите синий количество повторов данных дат,есть ли в приложений (VBA). Результаты Появление сообщения “Не варианты, где требуется
оставляем относительным. Щелкаем
lumpics.ru
Сравнение двух версий книги с помощью средства сравнения электронных таблиц
полю, установив туда курсор. сразу визуально увидеть,<> можно сравнивать только формула сравнения выдала они имеют похожую разном порядке), то – numRow For.Pattern = xlSolid цвет. На всех их первого столбца, указанных периодах одинаковые отображаются в окне
удается открыть книгу” размещение обоих табличных по кнопке«Значение если истина» Щелкаем по первому в чем отличие
-
). Только перед данным синхронизированные и упорядоченные
-
показатель структуру. придется идти другим i = numRowEnd With окнах жмем ОК. сравнив их с даты и сколько таким образом, чтобы
-
может означать, что областей на одном«OK». Тут мы воспользуемся элементу с фамилиями
-
между массивами. выражением на этот списки. Кроме того,«ИСТИНА»Самый простой способ сравнения путем. To Cells(Rows.Count, 1).End(xlUp).Row
End IfПри определении условий для данными второго столбца. дней в периодах различия можно было книга защищена паролем. листе. Главное условие.
-
ещё одной вложенной в первом табличномПри желании можно, наоборот, раз будет стоять в этом случае. данных в двух
Самое простое и быстрое iprov = iEnd If форматирования ячеек столбцовВ дополнительном столбце совпадают. Этот способ просматривать параллельно. Нажмите кнопку для проведения процедуры
-
Оператор выводит результат – функцией – диапазоне. В данном
End IfПри определении условий для данными второго столбца. дней в периодах различия можно было книга защищена паролем. листе. Главное условие.
Самое простое и быстрое iprov = iEnd If окрасить несовпадающие элементы, знак списки должны располагатьсяКроме того, существует возможность таблицах – это решение: включить цветовое + Razn ForПримерно так мы использовали функцию устанавливаем формулы, они может пригодиться, чтобыЕсть несколько способов,ОК
сравнения в этом числоСТРОКА случае оставляем ссылку а те показатели,«=» рядом друг с с помощью специальной использование простой формулы выделение отличий, используя y = 1Alex ivanov СЧЕТЕСЛИ. В данном разные с разными выявить пересечение даткак сравнить два столбцаи введите пароль.
Интерпретация результатов
-
случае – это3. Вписываем слово относительной. После того, которые совпадают, оставить. Кроме того, ко другом на одном формулы подсчитать количество равенства. Если данные условное форматирование. Выделите To numCol If
-
: выдели… и…подготовить… свойства… примере эта функция условиями. Или в в периодах. Например, в Excel на Узнайте подробнее о
-
открытие окон обоих. Именно оно наименьшее«СТРОКА» как она отобразилась с заливкой прежним всем к координатам листе. несовпадений. Для этого совпадают, то она оба диапазона с wB1.Sheets(“Лист1”).Cells(i, y) <>Oaobv проверяет сколько раз формуле указываем конкретные чтобы в отпуске совпадения
Другие способы работы с результатами сравнения
том, как действуют файлов одновременно. Для из нумерации несовпадающихбез кавычек, далее в поле, можно цветом. При этом столбцов в даннойВыделяем сравниваемые массивы. Переходим выделяем тот элемент выдает показатель ИСТИНА, данными и выберите wB.Sheets(“Лист1”).Cells(iprov, y) Then: Да проще можно! встречается значение второго данные, слова, которые
-
не было два, пароли при использовании версий Excel 2013 строк табличных массивов. открываем скобки и
-
щелкать по кнопке алгоритм действий практически формуле нужно применить во вкладку листа, куда оно а если нет, на вкладке
-
wB1.Sheets(“Лист1”).Cells(i, y).Interior.Color = Но для удобного аргумента (например, A2) нужно посчитать в сотрудника сразу или
Другие причины для сравнения книг
-
как сравнить две таблицы средства сравнения электронных и позже, а С помощью маркера указываем координаты первой«OK» тот же, но абсолютную адресацию. Для«Главная» будет выводиться. Затем то – ЛОЖЬ.Главная – Условное форматирование
-
255 End If сравнения нужно, чтобы в списке первого столбце. даты учебы, оплаты Excel таблиц. также для версий заполнения копируем формулу ячейки с фамилией. в окне настройки
support.office.com
Как сравнить два столбца в Excel на совпадения.
этого выделяем формулу. Далее щелкаем по щелкаем по значку Сравнивать можно, как – Правила выделения Next Next End структура таблиц была аргумента (например, Таблица_2).Подробнее смотрите такие счетов, т.д. не,Результаты сравнения отображаются в до Excel 2007 до самого низа. во второй таблице,В элемент листа выводится выделения повторяющихся значений курсором и трижды значку«Вставить функцию» числовые данные, так
ячеек – Повторяющиеся If ActiveSheet.Protect: Application.ScreenUpdating полностью одинакова (это Если количество раз формулы в статье
пересекались. Об этомсписки виде таблицы, состоящей с выполнением этогоТеперь, зная номера строк
после чего закрываем результат. Он равен в первом поле жмем на клавишу«Найти и выделить». и текстовые. Недостаток значения (Home - = True End
и вам нужно,
= 0 в «Функция «СЧЕТЕСЛИ» в способе читайте в
, из двух частей. условия нет никаких несовпадающих элементов, мы скобки. Конкретно в числу вместо параметраF4
, который располагается наВ окне данного способа состоит Conditional formatting -
Sub так как иначе таком случае формула Excel». статье “Как сравнитьдаты в Excel Книга в левой проблем. Но в
можем вставить в нашем случае в«1»«Повторяющиеся». Как видим, около ленте в блокеМастера функций в том, что Highlight cell rulesVipeRock возмножны дубли данных возвращает значение ИСТИНА.Этот способ сравнения
даты в Excel”.
. Не только сравнить части соответствует файлу, Excel 2007 и
ячейку и их поле. Это означает, чтоследует выбрать параметр всех адресов столбцов инструментовв группе операторов ним можно пользоваться
– Duplicate Values) : если я вас
и некорректные результаты). В таком случае можно применить приКак посчитать разницу столбцы в Excel, указанному в поле Excel 2010 для значения с помощью«Значение если истина» в перечне имен«Уникальные»
появился знак доллара,«Редактирование»«Математические»
только в том: правильно понял, то1) Копируете списки ячейке присваивается пользовательский сравнении двух прайсов. дат, стаж, возраст, но и выделить “Compare” (Сравнить), а того, чтобы открыть функцииполучилось следующее выражение: второй таблицы фамилия. После этого нажать что и означает. Открывается список, ввыделяем наименование случае, если данныеЕсли выбрать опцию посмотрите надстройку inquire в два столбца
формат, указанный в Смотрите статью “Как как прибавить к разницу цветом шрифта, книга в правой оба окна одновременно,ИНДЕКС
СТРОКА(D2)
«Гринев В. П.» на кнопку превращение ссылок в котором следует выбратьСУММПРОИЗВ
в таблице упорядочены
ПовторяющиесяHugo121 рядом на один параметрах условного форматирования. сделать прайс-лист в дате число, т.д.,
ячеек, т.д. Один части — файлу, указанному требуется провести дополнительные. Выделяем первый элементТеперь оператор, которая является первой«OK» абсолютные. Для нашего позицию
. Щелкаем по кнопке или отсортированы одинаково,, то Excel выделит: Вы хотели получить лист.Скачать пример сравнения 2 Excel”. смотрите в статье способ сравнения, разместить в поле “To” манипуляции. Как это
листа, содержащий формулуСТРОКА в списке первого. конкретного случая формула«Выделение группы ячеек…»«OK» синхронизированы и имеют цветом совпадения в готовый рабочий код2) В третьем
таблицы в Excel
У нас имеется две «Дата в Excel.
две таблицы на (С чем). Подробные сделать рассказывается вНАИМЕНЬШИЙбудет сообщать функции
табличного массива, встречается
Таким образом, будут выделены примет следующий вид:..
равное количество строчек. наших списках, если не показав пример столбце пишете простейшуюСсылка во втором аргументе таблицы заказов, скопированных Формула» здесь. одном мониторе одновременно, сведения отображаются в отдельном уроке.. После этого переходимЕСЛИ один раз.
именно те показатели,=$A2<>$D2Кроме того, в нужное
Активируется окно аргументов функции Давайте посмотрим, как опцию файла? формулу, которая сравнивает относительная, значит по в один рабочийМожно сравнить числа. описан в статье области под двумяУрок: Как открыть Эксель в строку формул
номер строки, в
Теперь нам нужно создать которые не совпадают. Данное выражение мы и нам окно выделенияСУММПРОИЗВ использовать данный способУникальныеТиповая задача, возникающая периодически
соответствующие ячейки и очереди будут проверятся лист. Необходимо выполнитьФункцию выделения ячеек можно “Сравнение таблиц Excel”. частями таблицы. Изменения в разных окнах и перед наименованием
которой расположена конкретная подобное выражение иУрок: Условное форматирование в записываем в вышеуказанное
группы ячеек можно, главной задачей которой на практике на- различия. перед каждым пользователем показывает, одинаково у
excel-office.ru
Сравнение двух таблиц в Excel на совпадение значений в столбцах
все ячейки выделенного сравнение данных двух вызвать клавишей F5.Здесь рассмотрим, выделяются разными цветамиКак видим, существует целый«НАИМЕНЬШИЙ» фамилия, и в для всех других Экселе поле. После этого попасть и другим
Сравнение двух столбцов на совпадения в Excel
является вычисление суммы примере двух таблиц,Цветовое выделение, однако, не Excel – сравнить них содержимое, или диапазона (например, A2:A15). таблиц в Excel В появившемся окнекак сравнить столбцы в в соответствии с
ряд возможностей сравнитьдописываем название случае, когда условие, элементов первой таблицы.Также сравнить данные можно
- щёлкаем по кнопке способом. Данный вариант
- произведений выделенного диапазона. размещенных на одном всегда удобно, особенно
- между собой два нет: Например, для сравнения и проверить, которые «Переход» нажимаем кнопку
Excel их типом. таблицы между собой.«ИНДЕКС» заданное в первом Для этого выполним
при помощи сложной«Формат…» особенно будет полезен Но данную функцию листе. для больших таблиц. диапазона с данными=IF(A3=B3;”Одинаковое”;”Разное”), двух прайсов в позиции есть первой «Выделить…».
, выделить разницу цветом,В двух расположенных рядом Какой именно вариантбез кавычек, тут поле, будет выполняться,
копирование, воспользовавшись маркером
формулы, основой которой. тем пользователям, у можно использовать иИтак, имеем две простые Также, если внутри и найти различиягде А3 и Excel даже на
- таблице, но нетВторой способ. символами, т.д. частях таблицы сравнивается использовать зависит от же открываем скобку
- функция заполнения, как это
- является функцияАктивируется окно которых установлена версия для наших целей. таблицы со списками
- самих списков элементы между ними. Способ В3 – это разных листах. Вторая во второй. НетМожно в
- Например, несколько магазинов каждый лист из
- того, где именно и ставим точкуЕСЛИ мы уже делалиСЧЁТЕСЛИ
Принцип сравнения данных двух столбцов в Excel
«Формат ячеек» программы ранее Excel Синтаксис у неё работников предприятия и могут повторяться, то решения, в данном ячейки с одинаковыми формула действует аналогично. смысла вручную сравниватьExcel сравнить и выделить сдали отчет по обоих файлов, начиная расположены табличные данные с запятой (будет выводить этот прежде. Ставим курсор. С помощью данного. Идем во вкладку
2007, так как довольно простой:
их окладами. Нужно этот способ не случае, определяется типом данными из разных Этот же принцип значение каждой ячейки. цветом продажам. Нам нужно с самого крайнего относительно друг друга; номер в ячейку. в нижнюю правую
exceltable.com
Люди подскажите пожалуйста, как в двух файлах excel найти совпадения????помогите очень нужно!!!
инструмента можно произвести«Заливка» метод через кнопку=СУММПРОИЗВ(массив1;массив2;…) сравнить списки сотрудников подойдет. исходных данных. списков. можно применять дляКак сделать сравнение значенийячейки с разными данными сравнить эти отчеты
слева. Если лист (на одном листе,). Затем выделяем в Жмем на кнопку часть элемента листа, подсчет того, сколько. Тут в перечне«Найти и выделить»
Всего в качестве аргументов и выявить несоответствияВ качестве альтернативы можноЕсли списки синхронизированы (отсортированы),kilmynda разных подобных задач. в Excel двух
с помощью условного и выявить разницу. в книге скрыт, в разных книгах,
строке формул наименование
«OK» который содержит функцию
каждый элемент из цветов останавливаем выборэти приложения не можно использовать адреса
между столбцами, в использовать функцию
то все делается: Добрый день, форумчане!есть 2 файла excel. столбцов? Для решения
форматирования.
У нас такая он все равно на разных листах),«ИНДЕКС»
.
СЧЁТЕСЛИ
выбранного столбца второй
на цвете, которым
поддерживают. Выделяем массивы,
до 255 массивов. которых размещены фамилии.СЧЁТЕСЛИ весьма несложно, т.к.До этого не В обоих перечислины
данной задачи рекомендуемИтак, мы выделяем таблица с данными отображается и сравнивается
а также оти кликаем поКак видим, первый результат, и после преобразования
таблицы повторяется в хотим окрашивать те
которые желаем сравнить, Но в нашемДля этого нам понадобится(COUNTIF)
надо, по сути,
имел дело с названия деталей, их использовать условное форматирование, столбцы с данными
из магазинов. Как
в средстве сравнения
того, как именно
пиктограмме
отображается, как
его в маркер первой.
элементы, где данные и жмем на случае мы будем дополнительный столбец наиз категории сравнить значения в VBA. Начинаю только очень много! в которое быстро выделить (без названия столбцов).
перенести данные из электронных таблиц. пользователь желает, чтобы«Вставить функцию»
«ЛОЖЬ» заполнения зажимаем левуюОператор не будут совпадать. клавишу использовать всего два листе. Вписываем туда
Статистические
соседних ячейках каждой изучать. Начальство подкинуло этих файлах есть цветом позиции, находящиеся На закладке «Главная»
Сравнение данных из двух разных файлов Excel
разных таблиц вЕсли содержимое не умещается
это сравнение выводилось.. Это означает, что кнопку мыши иСЧЁТЕСЛИ
Жмем на кнопкуF5 массива, к тому знак, которая подсчитывает сколько строки. Как самый
такую задачку: много совпадений, мне только в одном в разделе «Стили» одну, читайте в в ячейках, выберите на экран.После этого открывается небольшое значение не удовлетворяет
тянем курсор вниз.
относится к статистической
«OK». же, как один«=» раз каждый элемент простой вариант -Из определенной программы нужно сравнить эти столбце. Рабочий лист нажимаем на кнопку статье «Как связать командуАвтор: Максим Тютюшев окошко, в котором условиям оператораКак видим, программа произвела группе функций. Его.Активируется небольшое окошко перехода. аргумент.. Затем кликаем по из второго списка используем формулу для выгружаются отчеты в два документа по с таблицами: «Условное форматирование». Из таблицы в Excel»Resize Cells to FitЕсли другие пользователи имеют нужно определить, ссылочныйЕСЛИ вычисление совпадений, сравнив задачей является подсчетВернувшись в окно создания Щелкаем по кнопкеСтавим курсор в поле первому наименованию, которое встречался в первом: сравнения значений, выдающую Excel. В этих диапазонам и совпаденияВ первую очередь необходимо появившегося списка выбираем тут.(Размер ячеек по право на редактирование вид должна иметь. То есть, первая каждую ячейку первой количества ячеек, значения правила форматирования, жмем«Выделить…»«Массив1» нужно сравнить вПолученный в результате ноль на выходе логические отчетах очень много в одном из присвоить имена обоим функцию «Создать правило».Первый способ. размеру данных). вашей книги, то функция фамилия присутствует в таблицы с данными, в которых удовлетворяют на кнопкув его нижнеми выделяем на
первом списке. Опять и говорит об значения данных, рассортированных по
документов, чтобы выделялись таблицам. Благодаря этомуВ строке «Формат…» пишемКак сравнить два столбцаРазличия разного типа выделяются
CyberForum.ru
Поиск отличий в двух списках
после ее открытияИНДЕКС обоих списках. которые расположены во заданному условию. Синтаксис«OK» левом углу. листе сравниваемый диапазон ставим символ отличиях.
Вариант 1. Синхронные списки
ИСТИНА (TRUE) времени. каким нибуть цветом!!! легче понять, какие такую формулу. =$А2<>$В2. в с помощью цвета у вас могутили предназначенный дляС помощью маркера заполнения, втором табличном диапазоне. данного оператора имеет.После этого, какой бы данных в первой«=»
И, наконец, “высший пилотаж”или
Нужно сравнить данные
Удачник сравниваются диапазоны ячеек:
Этой формулой мыExcel на совпадения. заливки ячейки или возникнуть вопросы “Кто работы с массивами. уже привычным способом В четырех случаях такой вид:После автоматического перемещения в из двух вышеперечисленных области. После этогос клавиатуры. Далее – можно вывестиЛОЖЬ (FALSE)
по промежуткам времени.: А таблицы одинаковы?Выберите инструмент «ФОРМУЛЫ»-«Определенные имена»-«Присвоить говорим Excel, чтоВыделяем столбцы (у цвета шрифта текста. ее изменил? И Нам нужен второй копируем выражение оператора результат вышел=СЧЁТЕСЛИ(диапазон;критерий) окно вариантов вы не в поле ставим кликаем по первой отличия отдельным списком.: Допустим, если событие То есть, например, имя». если данные в нас столбцы А
Например, ячейки с что именно изменилось?” вариант. Он установленЕСЛИ
- «1»Аргумент
- «Диспетчера правил» избрали, запускается окно
- знак ячейке колонки, которую Для этого придетсяЧисло несовпадений можно посчитать
- в одном отчете названия деталей вВ появившемся окне в ячейках столбца А и В). На введенными значениями (не Средство сравнения электронных по умолчанию, так
- на весь столбец.
Вариант 2. Перемешанные списки
, а в двух«Диапазон»щелкаем по кнопке выделения групп ячеек.«не равно» мы сравниваем, во
использовать формулу массива: формулой: произошло в 13:20, столбце А, размер поле «Имя:» введите не равны данным закладке «Главная» нажимаем с формулами) выделяются таблиц от Майкрософт что в данном Как видим, по случаях –представляет собой адрес«OK» Устанавливаем переключатель в
( второй таблице. ПолучилосьВыглядит страшновато, но свою=СУММПРОИЗВ(–(A2:A20<>B2:B20)) а в другом в В, цена значение – Таблица_1. в ячейках столбца
на кнопку функции заливкой зеленого цвета поможет вам ответить окошке просто щелкаем двум позициям, которые«0» массива, в котороми в нем.
позицию<> выражение следующего типа: работу выполняет отличноили в английском варианте – 13:28, то в С иЛевой клавишей мышки сделайте В, то окрасить «Найти и выделить»,
в расположенных рядом на эти вопросы — по кнопке
присутствуют во второй. То есть, программа производится подсчет совпадающихТеперь во второй таблице«Выделить по строкам»
) и выделяем сравниваемый=A2=D2 ;)
planetaexcel.ru
=SUMPRODUCT(–(A2:A20<>B2:B20))
При анализе данных с помощью Excel очень часто приходится искать некоторые значения по определенным критериям и подставлять их в другие таблицы. В таких задачах на помощью приходит функция ВПР, но у нее есть два существенных недостатка. Во-первых, функция ВПР возвращает только первое найденное значение. Например, у меня есть перечень заказов и необходимо получить номера всех заказов, которые оформил конкретный менеджер. Фамилия менеджера выбирается из выпадающего списка (выделено желтым) и ниже должны выводиться соответствующие номера заказов (зеленая область).
Если использовать стандартную функцию ВПР, то она найдет первый подходящий заказ и вернет ТОЛЬКО его номер.
Еще одним недостатком функции ВПР является то, что она ищет значения только в левом крайнем столбце выделенного диапазона. То есть если бы столбец с фамилиями менеджеров находился правее столбца с номерами заказов, то функцию ВПР вообще не удалось бы использовать.
Этот недостаток устраняется с помощью сочетания функций ИНДЕКС и ПОИСКПОЗ, которые являются более гибкой альтернативой функции ВПР, но и эти функции не решают поставленную задачу.
Как же быть, если нужно вернуть все значения, соответсвующие определенному критерию? В этом также поможет функция ИНДЕКС.
Напомню, что функция ИНДЕКС возвращает значение, которое находится в указанном номере строки выделенного диапазона. То есть мы должны выбрать некоторый диапазон значений и вторым аргументом указать номер строки в этом диапазоне. Подчеркиваю, не номер строки листа Эксель, а номер строки выделенного диапазона значений.
Функция ИНДЕКС может возвращать не только одно значение, а массив значений. И именно это нам и нужно. Чтобы функция вернула массив значений, мы должны в нее подставить массив номеров нужным нам строк. Поэтому основной задачей для нас сейчас как раз и будет получение этого массива.
Давайте рассмотрим упрощенную таблицу, чтобы в ней не было отвлекающей информации. У нас есть таблица, состоящая из двух столбцов – Менеджер и Номер заказа. Также есть выпадающий список с фамилиями менеджеров и некоторая область листа, в которую мы будем выводить все заказы, связанные с выбранным менеджером.
Итак, сначала мы должны определить все строки в основной таблице, относящиеся к выбранному в списке менеджеру. Давайте сделаем это с помощью вспомогательного столбца и функции ЕСЛИ.
Если значение из текущей ячейки первого столбца таблицы (А2) равно значению, выбранному в выпадающем списке (L1), то определим номер строки, в котором это значение находится. Для этого воспользуемся функцией СТРОКА, которая как раз и предназначена для решения этой задачи. Так как формулу мы будем протягивать по диапазону, то не забываем зафиксировать ссылку на ячейку с выпадающем списком.
Протягиваем формулу.
Так как мы не указали в функции ЕСЛИ значение, которое появится в случае невыполнения условия, то в соответствующих ячейках выводится логическое выражение ЛОЖЬ. Это неважно, так как нас будут интересовать только цифры.
Мы получили номера строк листа Эксель, но в функцию ИНДЕКС необходимо подставить номер строки в выделенном диапазоне. В данном примере основная таблица располагается в верху листа и в первой строке находится шапка с названиями столбцов, поэтому, чтобы получить необходимые значения, мы можем откорректировать формулу и отнять единицу от полученного значения. В итоге во вспомогательном столбце появится массив необходимых нам чисел.
Если же таблица находится в другой части листа, то нужно будет либо вручную прописать необходимое корректировочное значение (то есть отступ от первой строки листа), либо можно автоматизировать этот процесс с помощью все той же функции СТРОКА, но об этом я расскажу чуть позже, чтобы сейчас не усложнять формулу.
Итак, теперь нам нужно отсортировать столбец, чтобы в начале были цифры. Сделать это можно, например, с помощью функции НАИМЕНЬШИЙ, которая возвращает указанное по счету наименьшее значение в выбранном диапазоне.
Выбираем диапазон (С2:С18) и затем необходимо указать цифру, определяющую, какое по порядку наименьшее число нужно вывести. Если укажем 1, то получим первое наименьшее значение в диапазоне, если 2, то второе, и так далее. Именно таким образом мы и сможем отсортировать полученный список с помощью формулы. Создадим вспомогательный столбец со значениями по порядку и подставим эти значения в функцию НАИМЕНЬШИЙ.
Мы получили необходимый нам перечень номеров строк и можем вернуться к функции ИНДЕКС. Фактически нам нужно подтянуть значения из второго столбца основной таблицы, поэтому в качестве диапазона указываем его. В качестве строки, соответственно, только что рассчитанные значения.
Чтобы избавиться от ошибки ЧИСЛО! воспользуемся функцией ЕСЛИОШИБКА. Обернем ей полученную функцию и в случае ошибки выведем пустоту.
Мы достигли необходимого результата, но для этого пришлось создать несколько вспомогательных столбцов. Давайте свернем все промежуточные вычисления в одну формулу, но это будет не простая формула, а формула массива, поэтому нам нужно будет поменять некоторые ссылки на диапазоны, к которым они относятся. А если точнее, то в функции ЕСЛИ нужно будет заменить относительные ссылки на ячейки столбца А соответствующим диапазоном и не забываем зафиксировать его. Также не забываем сделать формулу формулой массива, нажав сочетание клавиш Ctrl + Shift + Enter. Растянем формулу на весь зарезервированный диапазон таблицы и получаем необходимый результат.
Все вспомогательные столбцы, кроме столбца с нумерацией (столбец F) можно удалить. Давайте сделаем так, чтобы и этот столбец был не нужен. Значения столбца F используются в функции НАИМЕНЬШИЙ и нам нужно сделать так, чтобы подобный ряд чисел создавался автоматически и не зависел от того, где находится таблица с формулами. Для этого можно воспользоваться функцией СТРОКА и определить номер строки листа Эксель первой ячейки основной таблицы. Затем этот номер будем вычитать из номера последующих строк. Чтобы значения «не сползали» при протягивании формулы, зафиксируем ссылку на первую ячейку диапазона.
Все отлично, кроме того, что все значения нужно увеличить на единицу. Дополним формулу и получим нужный нам результат.
Осталось скопировать формулу и подставить ее в формулу массива, после чего и последний вспомогательный столбец можно будет удалить.
Ну и по аналогии можно решить проблему зависимости формулы от расположения исходной таблицы. Сейчас ее заголовки расположены в первой строке листа и поэтому формула четко привязана к этому положению.
Мы можем внести в функцию ЕСЛИ аналогичную формулу с двумя функциями СТРОКА. То есть отнимем от уже имеющейся функции СТРОКА со всем диапазоном столбца номер строки первой ячейки этого диапазона и прибавим единицу.
Теперь формула никак не привязана к положению исходной таблице на листе и она выполняет поставленную задачу – возвращает все искомые значения из указанного диапазона.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel
★ Телеграм
★ Серия видеокурсов “Microsoft Excel Шаг за Шагом”
★ Авторские книги и курсы
Поиск совпадений в двух списках
Тема сравнения двух списков поднималась уже неоднократно и с разных сторон, но остается одной из самых актуальных везде и всегда. Давайте рассмотрим один из ее аспектов – подсчет количества и вывод совпадающих значений в двух списках. Предположим, что у нас есть два диапазона данных, которые мы хотим сравнить:
Для удобства, можно дать им имена, чтобы потом использовать их в формулах и ссылках. Для этого нужно выделить ячейки с элементами списка и на вкладке Формулы нажать кнопку Менеджер Имен – Создать (Formulas – Name Manager – Create). Также можно превратить таблицы в “умные” с помощью сочетания клавиш Ctrl+T или кнопки Форматировать как таблицу на вкладке Главная (Home – Format as Table).
Подсчет количества совпадений
Для подсчета количества совпадений в двух списках можно использовать следующую элегантную формулу:
В английской версии это будет =SUMPRODUCT(COUNTIF(Список1;Список2))
Давайте разберем ее поподробнее, ибо в ней скрыто пару неочевидных фишек.
Во-первых, функция СЧЁТЕСЛИ (COUNTIF). Обычно она подсчитывает количество искомых значений в диапазоне ячеек и используется в следующей конфигурации:
=СЧЁТЕСЛИ(Где_искать; Что_искать)
Обычно первый аргумент – это диапазон, а второй – ячейка, значение или условие (одно!), совпадения с которым мы ищем в диапазоне. В нашей же формуле второй аргумент – тоже диапазон. На практике это означает, что мы заставляем Excel перебирать по очереди все ячейки из второго списка и подсчитывать количество вхождений каждого из них в первый список. По сути, это равносильно целому столбцу дополнительных вычислений, свернутому в одну формулу:
Во-вторых, функция СУММПРОИЗВ (SUMPRODUCT) здесь выполняет две функции – суммирует вычисленные СЧЁТЕСЛИ совпадения и заодно превращает нашу формулу в формулу массива без необходимости нажимать сочетание клавиш Ctrl+Shift+Enter. Формула массива необходима, чтобы функция СЧЁТЕСЛИ в режиме с двумя аргументами-диапазонами корректно отработала свою задачу.
Вывод списка совпадений формулой массива
Если нужно не просто подсчитать количество совпадений, но и вывести совпадающие элементы отдельным списком, то потребуется не самая простая формула массива:
В английской версии это будет, соответственно:
=INDEX(Список1;MATCH(1;COUNTIF(Список2;Список1)*NOT(COUNTIF($E$1:E1;Список1));0))
Логика работы этой формулы следующая:
- фрагмент СЧЁТЕСЛИ(Список2;Список1), как и в примере до этого, ищет совпадения элементов из первого списка во втором
- фрагмент НЕ(СЧЁТЕСЛИ($E$1:E1;Список1)) проверяет, не найдено ли уже текущее совпадение выше
- и, наконец, связка функций ИНДЕКС и ПОИСКПОЗ извлекает совпадающий элемент
Не забудьте в конце ввода этой формулы нажать сочетание клавиш Ctrl+Shift+Enter, т.к. она должна быть введена как формула массива.
Возникающие на избыточных ячейках ошибки #Н/Д можно дополнительно перехватить и заменить на пробелы или пустые строки “” с помощью функции ЕСЛИОШИБКА (IFERROR).
Вывод списка совпадений с помощью слияния запросов Power Query
На больших таблицах формула массива из предыдущего способа может весьма ощутимо тормозить, поэтому гораздо удобнее будет использовать Power Query. Это бесплатная надстройка от Microsoft, способная загружать в Excel 2010-2013 и трансформировать практически любые данные. Мощь и возможности Power Query так велики, что Microsoft включила все ее функции по умолчанию в Excel начиная с 2016 версии.
Для начала, нам необходимо загрузить наши таблицы в Power Query. Для этого выделим первый список и на вкладке Данные (в Excel 2016) или на вкладке Power Query (если она была установлена как отдельная надстройка в Excel 2010-2013) жмем кнопку Из таблицы/диапазона (From Table):
Excel превратит нашу таблицу в “умную” и даст ей типовое имя Таблица1. После чего данные попадут в редактор запросов Power Query. Никаких преобразований с таблицей нам делать не нужно, поэтому можно смело жать в левом верхнем углу кнопку Закрыть и загрузить – Закрыть и загрузить в… (Close & Load To…) и выбрать в появившемся окне Только создать подключение (Create only connection):
Затем повторяем то же самое со вторым диапазоном.
И, наконец, переходим с выявлению совпадений. Для этого на вкладке Данные или на вкладке Power Query находим команду Получить данные – Объединить запросы – Объединить (Get Data – Merge Queries – Merge):
В открывшемся окне делаем три вещи:
- выбираем наши таблицы из выпадающих списков
- выделяем столбцы, по которым идет сравнение
- выбираем Тип соединения = Внутреннее (Inner Join)
После нажатия на ОК на экране останутся только совпадающие строки:
Ненужный столбец Таблица2 можно правой кнопкой мыши удалить, а заголовок первого столбца переименовать во что-то более понятное (например Совпадения). А затем выгрузить полученную таблицу на лист, используя всё ту же команду Закрыть и загрузить (Close & Load):
Если значения в исходных таблицах в будущем будут изменяться, то необходимо не забыть обновить результирующий список совпадений правой кнопкой мыши или сочетанием клавиш Ctrl+Alt+F5.
Макрос для вывода списка совпадений
Само-собой, для решения задачи поиска совпадений можно воспользоваться и макросом. Для этого нажмите кнопку Visual Basic на вкладке Разработчик (Developer). Если ее не видно, то отобразить ее можно через Файл – Параметры – Настройка ленты (File – Options – Customize Ribbon).
В окне редактора Visual Basic нужно добавить новый пустой модуль через меню Insert – Module и затем скопировать туда код нашего макроса:
Sub Find_Matches_In_Two_Lists()
Dim coll As New Collection
Dim rng1 As Range, rng2 As Range, rngOut As Range
Dim i As Long, j As Long, k As Long
Set rng1 = Selection.Areas(1)
Set rng2 = Selection.Areas(2)
Set rngOut = Application.InputBox(Prompt:="Выделите ячейку, начиная с которой нужно вывести совпадения", Type:=8)
'загружаем первый диапазон в коллекцию
For i = 1 To rng1.Cells.Count
coll.Add rng1.Cells(i), CStr(rng1.Cells(i))
Next i
'проверяем вхождение элементов второго диапазона в коллекцию
k = 0
On Error Resume Next
For j = 1 To rng2.Cells.Count
Err.Clear
elem = coll.Item(CStr(rng2.Cells(j)))
If CLng(Err.Number) = 0 Then
'если найдено совпадение, то выводим со сдвигом вниз
rngOut.Offset(k, 0) = rng2.Cells(j)
k = k + 1
End If
Next j
End Sub
Воспользоваться добавленным макросом очень просто. Выделите, удерживая клавишу Ctrl, оба диапазона и запустите макрос кнопкой Макросы на вкладке Разработчик (Developer) или сочетанием клавиш Alt+F8. Макрос попросит указать ячейку, начиная с которой нужно вывести список совпадений и после нажатия на ОК сделает всю работу:
Более совершенный макрос подобного типа есть, кстати, в моей надстройке PLEX для Microsoft Excel.
Ссылки по теме
- Поиск различий в двух списках Excel
- Слияние двух списков без дубликатов (3 способа)
- Что такое макросы, как их использовать, куда копировать код макросов на Visual Basic
