Как сравнить три столбца в 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
Часто вам может понадобиться сопоставить значения в двух столбцах и вывести третий столбец в Excel.
К счастью, это легко сделать с помощью функции ВПР() , которая использует следующий синтаксис:
ВПР(искомое_значение, массив_таблиц, номер_индекса_столбца, [диапазон_просмотра])
- lookup_value : значение, которое вы хотите найти.
- table_array : диапазон ячеек для просмотра.
- col_index_num : номер столбца в диапазоне, который содержит возвращаемое значение.
- range_lookup : найти приблизительное совпадение (по умолчанию) или точное совпадение.
В следующем примере показано, как использовать эту функцию для сопоставления двух столбцов и возврата третьего в Excel.
Пример: сопоставление двух столбцов и возврат третьего в Excel
Предположим, у нас есть следующие наборы данных в Excel:
Предположим, мы хотим сопоставить значения команды в столбце A и столбце D и вернуть значения очков в столбце B в столбец E.
Мы можем использовать следующий синтаксис ВПР, чтобы сопоставить первое значение в столбце А:
=VLOOKUP( D2 , $A$2:$B$16 , 2, FALSE) На следующем снимке экрана показано, как использовать этот синтаксис на практике:
Обратите внимание, что значение «points» в столбце B, которое соответствует «Suns», равно 96 , поэтому это значение возвращается в столбце E.
Затем мы можем перетащить эту формулу в каждую оставшуюся ячейку в столбце E:
Примечание.Полную документацию по функции ВПР можно найти здесь .
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:
Как в Excel сравнить 4 столбца по 2-м параметрам
Мне нужны общие данные столбцов A и C, с условием что дата в столбце B больше чем в D. Я попробовала функцию =IF(AND(COUNTIF(C:C;A1); B1>D:D); 1; 0) Затем через фильтр выбрала все 1, все вроде получилось но в некоторых случаях он считает некоторые строчки как true, даже если дата B меньше чем дата D. Подскажите пожалуйста, почему эта функция не точно вычисляет, и какой формулой можно получить нужный результат? Хочу получить такой результат
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | aa | 10.09.2023 3:52:30 | dd | 26.09.2023 12:52:19 | 1 |
| 2 | bb | 13.09.2023 4:15:22 | aa | 10.09.2023 3:50:31 | 0 |
| 3 | cc | 19.09.2023 16:03:07 | ee | 19.09.2023 16:03:07 | 1 |
| 4 | aa | 10.09.2023 3:45:10 | cc | 18.09.2023 14:36:12 | 0 |
Пример функции ПОИСКПОЗ для поиска совпадения значений в Excel
Функция ПОИСКПОЗ в Excel используется для поиска точного совпадения или ближайшего (меньшего или большего заданному в зависимости от типа сопоставления, указанного в качестве аргумента) значения заданному в массиве или диапазоне ячеек и возвращает номер позиции найденного элемента.
Примеры использования функции ПОИСКПОЗ в Excel
Например, имеем последовательный ряд чисел от 1 до 10, записанных в ячейках B1:B10. Функция =ПОИСКПОЗ(3;B1:B10;0) вернет число 3, поскольку искомое значение находится в ячейке B3, которая является третьей от точки отсчета (ячейки B1).
Данная функция удобна для использования в случаях, когда требуется вернуть не само значение, содержащееся в искомой ячейке, а ее координату относительно рассматриваемого диапазона. В случае использования для констант массивов, которые могут быть представлены как массивы элементов «ключ» — «значение», функция ПОИСКПОЗ возвращает значение ключа, который явно не указан.
Например, массив содержит элементы, которые можно представить как: 1 – «виноград», 2 – «яблоко», 3 – «груша», 4 – «слива», где 1, 2, 3, 4 – ключи, а названия фруктов – значения. Тогда функция =ПОИСКПОЗ(«яблоко»;;0) вернет значение 2, являющееся ключом второго элемента. Отсчет выполняется не с 0 (нуля), как это реализовано во многих языках программирования при работе с массивами, а с 1.
Функция ПОИСКПОЗ редко используется самостоятельно. Ее целесообразно применять в связке с другими функциями, например, ИНДЕКС.
Формула для поиска неточного совпадения текста в Excel
Пример 1. Найти позицию первого частичного совпадения строки в диапазоне ячеек, хранящих текстовые значения.
Вид исходной таблицы данных:
Для нахождения позиции текстовой строки в таблице используем следующую формулу:
- D2&»*» – искомое значение, состоящее и фамилии, указанной в ячейке B2, и любого количества других символов (“*”);
- B:B – ссылка на столбец B:B, в котором выполняется поиск;
- 0 – поиск точного совпадения.
Из полученного значения вычитается единица для совпадения результата с id записи в таблице.
Сравнение двух таблиц в Excel на наличие несовпадений значений
Пример 2. В Excel хранятся две таблицы, которые на первый взгляд кажутся одинаковыми. Было решено сравнить по одному однотипному столбцу этих таблиц на наличие несовпадений. Реализовать способ сравнения двух диапазонов ячеек.
Вид таблицы данных:
Для сравнения значений, находящихся в столбце B:B со значениями из столбца A:A используем следующую формулу массива (CTRL+SHIFT+ENTER):
Функция ПОИСКПОЗ выполняет поиск логического значения ИСТИНА в массиве логических значений, возвращаемых функцией СОВПАД (сравнивает каждый элемент диапазона A2:A12 со значением, хранящимся в ячейке B2, и возвращает массив результатов сравнения). Если функция ПОИСКПОЗ нашла значение ИСТИНА, будет возвращена позиция его первого вхождения в массив. Функция ЕНД возвратит значение ЛОЖЬ, если она не принимает значение ошибки #Н/Д в качестве аргумента. В этом случае функция ЕСЛИ вернет текстовую строку «есть», иначе – «нет».
Чтобы вычислить остальные значения «протянем» формулу из ячейки C2 вниз для использования функции автозаполнения. В результате получим:
Как видно, третьи элементы списков не совпадают.
Поиск ближайшего большего знания в диапазоне чисел Excel
Пример 3. Найти ближайшее меньшее числу 22 в диапазоне чисел, хранящихся в столбце таблицы Excel.
Вид исходной таблицы данных:
Для поиска ближайшего большего значения заданному во всем столбце A:A (числовой ряд может пополняться новыми значениями) используем формулу массива (CTRL+SHIFT+ENTER):
Функция ПОИСКПОЗ возвращает позицию элемента в столбце A:A, имеющего максимальное значение среди чисел, которые больше числа, указанного в ячейке B2. Функция ИНДЕКС возвращает значение, хранящееся в найденной ячейке.
Для поиска ближайшего меньшего значения достаточно лишь немного изменить данную формулу и ее следует также ввести как массив (CTRL+SHIFT+ENTER):
Особенности использования функции ПОИСКПОЗ в Excel
Функция имеет следующую синтаксическую запись:
=ПОИСКПОЗ( искомое_значение;просматриваемый_массив; [тип_сопоставления])
- искомое_значение – обязательный аргумент, принимающий текстовые, числовые значения, а также данные логического и ссылочного типов, который используется в качестве критерия поиска (для сопоставления величин или нахождения точного совпадения);
- просматриваемый_массив – обязательный аргумент, принимающий данные ссылочного типа (ссылки на диапазон ячеек) или константу массива, в которых выполняется поиск позиции элемента согласно критерию, заданному первым аргументом функции;
- [тип_сопоставления] – необязательный для заполнения аргумент в виде числового значения, определяющего способ поиска в диапазоне ячеек или массиве. Может принимать следующие значения:
- -1 – поиск наименьшего ближайшего значения заданному аргументом искомое_значение в упорядоченном по убыванию массиве или диапазоне ячеек.
- 0 – (по умолчанию) поиск первого значения в массиве или диапазоне ячеек (не обязательно упорядоченном), которое полностью совпадает со значением, переданным в качестве первого аргумента.
- 1 – Поиск наибольшего ближайшего значения заданному первым аргументом в упорядоченном по возрастанию массиве или диапазоне ячеек.
- Если в качестве аргумента искомое_значение была передана текстовая строка, функция ПОИСКПОЗ вернет позицию элемента в массиве (если такой существует) без учета регистра символов. Например, строки «МоСкВа» и «москва» являются равнозначными. Для различения регистров можно дополнительно использовать функцию СОВПАД.
- Если поиск с использованием рассматриваемой функции не дал результатов, будет возвращен код ошибки #Н/Д.
- Если аргумент [тип_сопоставления] явно не указан или принимает число 0, для поиска частичного совпадения текстовых значений могут быть использованы подстановочные знаки («?» — замена одного любого символа, «*» — замена любого количества символов).
- Если в объекте данных, переданном в качестве аргумента просматриваемый_массив, содержится два и больше элементов, соответствующих искомому значению, будет возвращена позиция первого вхождения такого элемента.
- Excel Formula Examples
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Макросы VBA
- Скачать примеры