Как сравнить числа в excel
Табличный процессор Эксель – одна из самых популярных программ для работы с электронными таблицами. И нередко у пользователя возникает вопрос – можно ли сравнить в Excel несколько столбцов на наличие совпадений. Особенно это важно для тех, кто работает с огромными объемами информации и, соответственно, большими таблицами.
Колонки сравнивают для того, чтобы, например, в отчетах не было дубликатов. Или, наоборот, для проверки правильности заполнения — с поиском непохожих значений. И проще всего выполнять сравнение двух столбцов на совпадение в Excel — для этого есть 6 способов.
1 Сравнение с помощью простого поиска
При наличии небольшой по размеру таблицы заниматься сравнением можно практически вручную. Для этого достаточно выполнить несколько простых действий.
- Перейти на главную вкладку табличного процессора.
- В группе «Редактирование» выбрать пункт поиска.
- Выделить столбец, в котором будет выполняться поиск совпадений — например, второй.
- Вручную задавать значения из основного столбца (в данном случае — первого) и искать совпадения.
Если значение обнаружено, результатом станет выделение нужной ячейки. Однако с помощью такого способа можно работать только с небольшими столбцами. И, если это просто цифры, так можно сделать и без поиска — определяя совпадения визуально. Впрочем, если в колонках записаны большие объемы текста, даже такая простая методика позволит упростить поиск точного совпадения.
2 Операторы ЕСЛИ и СЧЕТЕСЛИ
Еще один способ сравнения значений в двух столбцах Excel подходит для таблиц практически неограниченного размера. Он основан на применении условного оператора ЕСЛИ и отличается от других методик тем, что для анализа совпадений берется только указанная в формуле часть, а не все значения массива. Порядок действий при использовании методики тоже не слишком сложный и подойдет даже для начинающего пользователя Excel.
- Сравниваемые столбцы размещаются на одном листе. Не обязательно, чтобы они находились рядом друг с другом.
- В третьем столбце, например, в ячейке J6, ввести формулу такого типа: =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(H6;$I$6:$I$14;0));»;H6)
- Протянуть формулу до конца столбца.
Результатом станет появление в третьей колонке всех совпадающих значений. Причем H6 в примере — это первая ячейка одного из сравниваемых столбцов. А диапазон $I$6:$I$14 — все значения второй участвующей в сравнении колонки. Функция будет последовательно сравнивать данные и размещать только те из них, которые совпали. Однако выделения обнаруженных совпадений не происходит, поэтому методика подходит далеко не для всех ситуаций.
Еще один способ предполагает поиск не просто дубликатов в разных колонках, но и их расположения в пределах одной строки. Для этого можно применить все тот же оператор ЕСЛИ, добавив к нему еще одну функцию Excel — И. Формула поиска дубликатов для данного примера будет следующей: =ЕСЛИ(И(H6=I6); «Совпадают»; «») — ее точно так же размещают в ячейке J6 и протягивают до самого низа проверяемого диапазона. При наличии совпадений появится указанная надпись (можно выбрать «Совпадают» или «Совпадение»), при отсутствии — будет выдаваться пустота.
Тот же способ подойдет и для сравнения сразу большого количества колонок с данными на точное совпадение не только значения, но и строки. Для этого применяется уже не оператор ЕСЛИ, а функция СЧЕТЕСЛИ. Принцип написания и размещения формулы похожий.
Она имеет вид =ЕСЛИ(СЧЕТЕСЛИ($H6:$J6;$H6)=3; «Совпадают»;») и должна размещаться в верхней части следующего столбца с протягиванием вниз. Однако в формулу добавляется еще количество сравниваемых колонок — в данном случае, три.
Если поставить вместо тройки двойку, результатом будет поиск только тех совпадений с первой колонкой, которые присутствуют в одном из других столбцов. Причем, тройные дубликаты формула проигнорирует. Так же как и совпадения второй и третьей колонки.
3 Формула подстановки ВПР
Принцип действия еще одной функции для поиска дубликатов напоминает первый способ использованием оператора ЕСЛИ. Но вместо ПОИСКПОЗ применяется ВПР, которую можно расшифровать как «Вертикальный Просмотр». Для сравнения двух столбцов из похожего примера следует ввести в верхнюю ячейку (J6) третьей колонки формулу =ВПР(H6;$I$6:$I$15;1;0) и протянуть ее в самый низ, до J15.
4 Функция СОВПАД
Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:
- В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
- Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
- Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.
Результатом такого сравнения будет вывод уже не найденного совпадающего значения, а булевой переменной. В случае нахождения это будет «ИСТИНА». Если ни одного совпадения не было обнаружено — в ячейке появится надпись «ЛОЖЬ».
Стоит отметить, что функция СОВПАД сравнивает и числа, и другие виды данных с учетом верхнего регистра. А одним из самых распространенных способом использования такой формулы сравнения двух столбцов в Excel является поиска информации в базе данных. Например, отдельных видов мебели в каталоге.
5 Сравнение с выделением совпадений цветом
В поисках совпадений между данными в 2 столбцах пользователю Excel может понадобиться выделить найденные дубликаты, чтобы их было легко найти. Это позволит упростить поиск ячеек, в которых находятся совпадающие значения. Выделять совпадения и различия можно цветом — для этого понадобится применить условное форматирование.
Предположим, что есть два столбца десятичных чисел, и вы просто хотите сравнить два числа в каждой строке только десятичными числами, как показано ниже. Как быстро решить эту задачу в Excel?
Сравните два десятичных числа с формулой
Чтобы сравнить только два десятичных числа между двумя числами, вы можете применить следующие формулы по мере необходимости.
Выберите пустую ячейку рядом с двумя числами, которые вы хотите сравнить, введите эту формулу =IF((FLOOR(A1, 0.01)-FLOOR(B1, 0.01))=0,1,-1) , Нажмите Enter и перетащите дескриптор заполнения вниз, чтобы сравнить два столбца. В расчетных результатах 1 указывает, что два десятичных числа одинаковы, а -1 отмечает, что два десятичных числа различны. Смотрите скриншот:
 |  |  |
Внимание: В приведенной выше формуле A1 и B1 - это два числа, которые вы хотите сравнить, 0.01 указывает на сравнение десятичных чисел по двум цифрам.
Чаевые: Если вы хотите сначала округлить десятичную дробь, а затем сравнить, вы можете использовать одну из следующих двух формул.
В формулах 2 указывает на сравнение первых двух цифр в десятичных числах, A1 и B1 - числа, которые вы хотите сравнить.
= ЕСЛИ ((ОКРУГЛ (A1,2) -Круглый (B1,2)) = 0,1; -1)
= ЕСЛИ (ОКРУГЛ (A1,2) = ОКРУГЛ (B1,2), «ОК», «!! ОШИБКА !!»)
Сравните два десятичных числа с извлечением текста
Если вы просто хотите сравнить десятичные числа по первым двум или первым n цифрам, вы можете применить Извлечь текст полезности Kutools for Excel чтобы сначала извлечь нужные десятичные числа, а затем использовать простую формулу для их сравнения.
После установки Kutools for Excel, сделайте следующее: （Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите номера двух столбцов, которые вы хотите сравнить, и нажмите Kutools > Текст > Извлечь текст. Смотрите скриншот:
2. в Извлечь текст диалог, тип . ?? в Текст и нажмите Добавить добавить этот критерий в Извлечь список. Смотрите скриншот:
 | |  |
3. Проверяйте только новый критерий в Извлечь список раздел и нажмите Ok. Затем выберите ячейку для размещения извлеченных десятичных чисел и щелкните OK заканчивать. Смотрите скриншот:
4. В ячейке рядом с извлеченными числами введите эту формулу = ЕСЛИ ((D1-E1) = 0,1; -1) , напишите Enter и перетащите маркер заполнения в нужные ячейки. По результатам расчетов 1 указывает, что два числа совпадают, -1 показывает цифры разные. Смотрите скриншот:
Предположим, есть два списка чисел, и вы хотите быстро выбрать меньшее или большее из них, что вы можете сделать? В этой статье представлены несколько простых формул, которые помогут вам быстро это сделать.
Выберите меньшее или большее значение двух ячеек с формулами
Чтобы выбрать меньшее или большее значение между двумя ячейками в Excel, сделайте следующее.
Выберите меньшее значение между двумя ячейками
Синтаксис
аргументы
- Number1: Это может быть число, ссылка на число или диапазон, содержащий числовые значения.
- Number2 (Необязательно): это может быть число, ссылка на число или диапазон, содержащий числовые значения.
1. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите Enter ключ.
=MIN(A2,B2)
2. Затем перетащите Ручка заполнения вниз, чтобы получить все более низкие значения.
Ноты:
- В формуле A2 и B2 - это ячейки, из которых вы выберете меньшее значение;
- Эта формула также может помочь: =IF(A2<B2,A2,B2) .
Выберите большее значение между двумя ячейками
Синтаксис
аргументы
- Номер 1: Это может быть число, ссылка на число или диапазон, содержащий числовые значения.
- Number2 (Необязательно): это может быть число, ссылка на число или диапазон, содержащий числовые значения.
1. Выберите черную ячейку, скопируйте в нее одну из формул ниже и нажмите Enter ключ. А затем перетащите Ручка заполнения вниз, чтобы получить все более высокие значения.
=MAX(A2,B2)
=IF(A2>B2,A2,B2)
Статьи по теме
Найдите максимальное или минимальное значение на основе критериев в Excel
Предположим, у вас есть диапазон данных, столбец A содержит названия продуктов, а столбец B - объемы заказа. Теперь вы хотите найти максимальную стоимость заказа продукта KTE. Как в Excel извлечь максимальное или минимальное значение на основе одного или нескольких критериев? Методы, описанные в этом руководстве, могут оказать вам услугу.
Найдите максимальное или минимальное значение в группе в Excel
Как правило, поиск максимального или минимального значения в диапазоне может быть легким для большинства пользователей Excel, но как насчет поиска максимального или минимального значения в каждой группе? В этом руководстве рассказывается о приемах поиска максимального или минимального значения в группе в Excel.
Вычислить среднее значение без максимальных и минимальных значений в Excel
Как мы все знаем, обычная функция Average будет получать среднее значение всех выбранных ячеек в Excel. Но здесь вы хотите рассчитать среднее значение, исключая самые высокие и самые низкие значения из выбранных чисел, как вы могли бы решить эту проблему? Пожалуйста, попробуйте методы, описанные в этом руководстве.
Читайте также: