Сравнение данных в двух столбцах для поиска дубликатов в Excel
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Office для бизнеса Excel 2010 Excel 2007 Еще. Меньше
Для сравнения данных в двух столбцах листа Microsoft Excel и поиска повторяющихся записей можно использовать следующие методы.
Способ 1. Использование формулы рабочего листа
Способ 2. Использование макроса Visual Basic
Предупреждение: Корпорация Майкрософт предоставляет примеры программирования только для иллюстрации, без явных или подразумеваемых гарантий. Это включает подразумеваемые гарантии товарного состояния или пригодности для конкретной цели, но не ограничивается ими. В этой статье предполагается, что вы знакомы с демонстрируемым языком программирования и инструментами, используемыми для создания и отладки процедур. Специалисты службы поддержки Майкрософт могут помочь объяснить функциональность той или иной процедуры. Однако они не будут изменять эти примеры для предоставления дополнительных функциональных возможностей или создания процедур для удовлетворения ваших конкретных требований.
Чтобы использовать макрос Visual Basic для сравнения данных в двух столбцах, выполните действия, описанные в следующем примере.
- Запустите Excel.
- Нажмите ALT+F11, чтобы запустить редактор Visual Basic.
- В меню Вставка выберите Модуль.
- Введите следующий код на листе модуля:
Sub Find_Matches() Dim CompareRange As Variant, x As Variant, y As Variant ' Set CompareRange equal to the range to which you will ' compare the selection. Set CompareRange = Range("C1:C5") ' NOTE: If the compare range is located on another workbook ' or worksheet, use the following syntax. ' Set CompareRange = Workbooks("Book2"). _ ' Worksheets("Sheet2").Range("C1:C5") ' ' Loop through each cell in the selection and compare it to ' each cell in CompareRange. For Each x In Selection For Each y In CompareRange If x = y Then x.Offset(0, 1) = x Next y Next x End Sub
Примечание: Если вы не видите вкладку Разработчик, возможно, вам понадобится включить ее. Для этого выберите Файл > Параметры > Настроить ленту, а затем выберите вкладку Разработчик в поле настройки справа.
Как сравнить три столбца в Excel (с примером)
Вы можете использовать следующую базовую формулу для сравнения трех столбцов в Excel:
=IF(AND( B2 = C2 , C2 = D2 ),"Equal","Not Equal") Эта конкретная формула сравнивает значения в ячейках B2 , C2 и D2 .
Если все значения равны, формула возвращает Equal.В противном случае возвращается Not Equal .
В следующем примере показано, как использовать эту формулу на практике.
Пример: сравнение трех столбцов в Excel
Предположим, у нас есть следующий набор данных, показывающий лучший результат в разных баскетбольных командах в трех разных играх:
Мы можем ввести следующую формулу в ячейку E2 , чтобы проверить, равны ли все три значения в первой строке:
=IF(AND( B2 = C2 , C2 = D2 ),"Equal","Not Equal") На следующем снимке экрана показано, как использовать эту формулу на практике:
Формула возвращает Not Equal , поскольку не все три имени в первой строке совпадают.
Затем мы можем перетащить эту формулу из ячейки E2 в оставшиеся ячейки в столбце E, чтобы проверить совпадения в каждой строке:
Обратите внимание, что формула возвращает значение « Равно » только для тех строк, в которых значения ячеек равны в каждом из трех столбцов.
Вы также можете применить условное форматирование к строкам, в которых все три значения ячеек равны, выделив диапазон ячеек E2:E11 , затем нажав кнопку « Условное форматирование » на вкладке «Главная», затем щелкнув « Выделить правила ячеек» и нажав «Равно »:
В появившемся новом окне введите « Равно » в поле и выберите цвет заливки, затем нажмите « ОК »:
Строки с совпадающими значениями во всех трех столбцах теперь будут окрашены зеленым цветом:
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как сравнить числа в excel
������� ���������� ��������� MS Office 2007: Microsoft Excel
Введем в ячейку А1 формулу =7>5. Она вернет значение ИСТИНА . Скопируем содержимое А1 в А2 и исправим в А2 формулу: =3>5. Эта формула вернет значение ЛОЖЬ . Правые части обеих формул представляют собой высказывания , т.е. утверждения, относительно которых можно заключить, верны они или нет.
Рассмотрим другой пример. Введем в ячейку А4 число 2, а в ячейку В4 формулу =А4>3 . Формула возвращает значение ЛОЖЬ . Введем в А4 число 6. Формула возвращает значение ИСТИНА . В В4 записан предикат , т.е. высказывание с переменными (в данном случае переменная одна). В зависимости от значения переменных предикат может принимать значения ИСТИНА и ЛОЖЬ . В этом примере формула как бы дает ответ на вопрос: «Число (или результат вычислений по формуле), хранящееся в ячейке А4 , превышает 3?» В зависимости от значения А4 ответ будет ДА ( ИСТИНА ) или НЕТ ( ЛОЖЬ ).
В формуле =А4>3 ее составные части ( А4 и 3) можно считать арифметическими выражениями, только очень простыми. Более сложный пример: =(А4 А2-1)>(2 *А4+1) . В этом выражении скобки можно опустить, потому что арифметические операции имеют более высокий приоритет, чем операции сравнения, но скобки придают формуле наглядность.
Операции сравнения можно свести в таблицу (Таблица 4).
Обратите внимание, что символ отношения «больше или равно» изображается двумя знаками: > и =. Причина в том, что на клавиатуре нет знака≥.
Высказывание и предикат имеют общее название — логическое выражение. Имеются логические операции, которые позволяют строить сложные логические выражения. Эти операции реализованы в Excel как функции ( НЕ, И, ИЛИ ).
У логических функций аргументы могут принимать только два значения: ИСТИНА и ЛОЖЬ . Функция НЕ может иметь только один аргумент, а функции И и ИЛИ могут иметь два и более аргументов.
В ячейке А1 (с именем z ) запишите любое число. Выясните, принадлежит ли оно отрезку [2, 5].
Присвоим ячейке А1 имя z ( Формулы Присвоить Имя ). Введем в А1 число 3. Для того чтобы z принадлежал отрезку [2, 5], нужно, чтобы одновременно были истинны два предиката: z≥2 и z≤5.B ячейке В1 разместим формулу = И (z>=2;z<=5). Для ввода в формулу имени ячейки нажмите F3 для открытия списка имен. В ячейке В1 получим значение ИСТИНА .
В ячейке А1 (с именем z) записано число. Выяснить, принадлежит ли оно одному из лучей на числовой оси: (∞,2) или (5,∞).
Для того чтобы z принадлежал хотя бы одному из лучей, нужно, чтобы был истинным хотя бы один из предикатов: z < 2 или z >5. В ячейке D1 поместите формулу = ИЛИ ( z5 ). А1 содержит число 3, поэтому формула возвращает ЛОЖЬ .
Задачу можно было решить иначе с учетом того обстоятельства, что на рабочем листе есть формула проверки принадлежности числа z отрезку [2, 5]. Упомянутые два луча составляют на числовой оси дополнение к этому отрезку. Введем в ячейку Е1 формулу = НЕ ( В1 ). Убедитесь, вводя в ячейку А1 различные числа, что формулы в ячейках D1 и Е1 дают идентичные результаты.
На практике «в чистом виде» логические выражения, как правило, не используются. Логическое выражение используется в функции ЕСЛИ :
ЕСЛИ(лог_выражение, значение_если_истина, значение_если_ложь)
При вызове встроенной функции ЕСЛИ открывается диалоговое окно (рис. 88), имеющее три аргумента. В окне Лог_выражение записывается логическое выражение. Во втором аргументе Значение_если _истина записывается выражение, которое будет вычислено, если лог_выражение возвращает значение ИСТИНА , а в третьем аргументе — выражение, вычисляемое, если лог_выражение возвращает ЛОЖЬ .
1. Введем в ячейку А2 формулу, которая возвращает z+1, если z >1, и z в противном случае: =ЕСЛИ (z>l; z + 1; z). (В Мастере функций ЕСЛИ находится в категории «Логические», также как функции И, ИЛИ, НЕ).
2. Если z>60, то в ячейке В2 выводить сообщение «Превышено пороговое значение», в противном случае выводить z:
=ЕСЛИ(z>60;»Превышено пороговое значение»;z)
Обратите внимание, что текст в формулах вводится в кавычках.
3. Если z е [10,25], то возвращать z, если z < 10, то возвращать
если z > 25, то возвращать 25. Выражение для этого условия будет выглядеть примерно следующим образом (запишем формулу в С2):
Теперь попробуйте менять значение z в ячейке А1, следя за тем, как меняются значения в ячейках с формулами.
Построить график движения тела, брошенного под углом к горизонту, используя предыдущие результаты. Максимальная высота полета на графике должна быть отмечена кружком.
Таблица зависимости высоты (h m ) и дальности полета (S m ) от времени (t m ).
На рис.88 представлена расчетная таблица в режиме отображения формул, а на рис.89 представлен график зависимости высоты (h m ) от дальности полета (S m ) и таблица в режиме отображения данных, по которым построен график.
В диапазон G3:G24 вводятся значения времени (t m ) от 0 до 4,2, в колонку н — формулы для вычисления дальности полета (S m ), в колонку
I — формулы для вычисления высоты полета.
Выделив элементы этого ряда, с помощью диалогового окна Формат ряда данных, вызванного с помощью контекстного меню, можно изменить тип, цвет и размер маркера данного ряда.
Формулы в ячейках J3:J24 возвращают максимальное значение столбца I либо значение #Н/Д (нет данных). Полученные данные используются для того, чтобы отметить на графике максимальную высоту полета.
После выделения построенной диаграммы в нее добавляется новый ряд с помощью команды меню Конструктор — Данные- Выбрать данные — Добавить ряд, значения которого содержатся в ячейках J3: J24 .
Условия сравнения чисел и строк в Excel
При работе с функциями Excel часто приходится использовать различные логические условия. В зависимости от соблюдения или не соблюдения таких условий меняется возвращаемый результат вычислений. С самыми простыми условиями мы все знакомы со школьных времен:
- Условие больше, обозначается символом «>», например «3>2»;
- Условие меньше, обозначается символом «
- Условие равно, символ «=», «5=5»;
- Совмещенные условия, больше или равно («>=»), меньше или равно (« <=»). Притом условия равно или больше, равно или меньше не существует, поэтому приложение вернет ошибку в случае их использования.
- Условие не равно, в Excel обозначается так – «<>».
Данные примеры удобны для сравнения числовых значений, но приложение Excel позволяет сравнивать с их помощью и строки, например «“А” = “А”», «“А” < “Б”», « “Привет” >“Пока” » и т.д.. Все эти примеры являются истинными.
Если при сравнении чисел, все предельно ясно, то при сравнении текста, могут возникнуть вопросы:
- Какая строка больше, с одним символом или из нескольких сотен символов;
- Как сравнивать строки на разных языках;
- Можно ли сравнивать числа и строки;
- Можно ли сравнивать пустые строки.
Таких вопросов можно придумать много. Все они имеют один ответ – чем ближе строка в условиях сортировки «от А до Я» имеет положение к началу, тем меньший вес она имеет. Т.е. строка «апельсин» будет располагаться в начале списка, нежели строка «яблоко», т.к. первое начинается с символа «а», а второе с символа «я». Так же строка «аа» имеет меньший вес по сравнению со строкой «аб». Причина в том, первые символы у них равны, поэтому сравнение переходит ко второму символу, а символ «а» в сортировке располагается выше, чем «б». Про порядок сортировки можно также прочитать в статье Сортировка в Excel.
При задании условий для строк можно использовать специальные символы:
- ? – обозначает один любой знак;
- * – обозначает любое количество любых знаков.
Следующие условия будут являться истинными:
- «аБББа» = «а. а»;
- «аБББа» = «а*а»;
- «аБББа» = «*»;
Если понадобится найти в сроке символы специальных знаков, то в условии перед ними ставится символ тильда «~»:
- «ааа*ААА» = «*~**», что обозначает строку, содержащую любое количество символов, среди которых имеется символ звездочки.
- «Как дела?» = «*~?», что обозначает строку, заканчивающуюся на знак вопроса.
А если понадобится найти символ тильда, то действовать нужно по тем же правилам, а именно экранировать его самим собой = «~~».
Описанные правила действуют во всех случаях, кроме использования специальных символов. Последнее могут использоваться только в некоторых функциях, типа «СЧЁТЕСЛИ» и т.п. либо при фильтрации данных (Фильтр в Excel).
- Логические функции Excel
- Текстовые функции Excel
- Массивы Excel
- Математические функции Excel
- Функции дата и время Excel
- Функции ссылок и массивов Excel
- Статистические функций Excel
- Функции ВПР2 ГПР2
- Функция ПЕРТЕКСТ
- Критерий Манна-Уитни
- Подключение MySQL в Excel
- Подключение Excel к SQL Server
Если материалы office-menu.ru Вам помогли, то поддержите, пожалуйста, проект, чтобы я мог развивать его дальше.