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

Как вернуть несколько совпадающих значений на основе одного или нескольких критериев в Excel?

Найти конечный результат на основе двух наборов критериев

Я пытаюсь определить соответствие между столбцами D и K и TO из соответствия, которое есть в столбцах A и H. В принципе, столбцы D (ABC) и A (123) связаны, а K (ABC) и H (123) связаны. Таким образом, основываясь на совпадении между D (ABC) и K (ABC), которое также имеет совпадение между A (123) и H (123). Я знаю, что есть много совпадающих значений в столбцах D и K, но меня интересуют только те значения, которые также имеют совпадающие значения в соответствующих столбцах A и H.

Я хотел бы узнать результаты следующих матчей (окончательный результат): 1. D & K, TO 2. A & H

Таким образом, может быть 3546 совпадений на #1, но только 450 из них соединяют #1 и #2. Это похоже на сценарий IF/THEN. Я думаю, что это вариант vlookup в Excel, но я не могу заставить его работать. Я также думаю, что запрос Access может работать, сравнивая лист 1 с листом 2 и вытаскивая лист 3, который показывает соответствия между столбцом A в листе 1 и столбцом A в листе 2 и столбцом B в листе 1 и столбцом B в листе 2.

1 ответ 1

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

  1. Те, в которых совпадают как значения в столбцах D и K, так и значения в столбцах A и H.
  2. Те, в которых совпадают только значения в столбцах A и H.

Если, например, ваши данные находятся в диапазоне: A2 — K1001, то вам следует использовать этот метод. В нем используется один вспомогательный столбец.

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

Следующая формула должна быть введена в ячейку M2 и скопирована в ячейку М1001:

Каждой строке формулы присваивается номер. Существует от 1 до 1000 совпадений, если совпадают столбцы D & K и A & H; существует от 1001 до 2000 совпадений, если совпадают только столбцы A & H; в противном случае существует от 2001 до 3000 совпадений.

В ячейку M1 введите приведенную ниже формулу. С помощью этих значений в столбцах D & K и A & H она подсчитает количество строк, которые вы хотите вернуть (то есть их соответствующие значения):

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

От центра формулы наружу функция SMALL вычисляет n-ое наименьшее из только что заданных чисел, т.е. наименьшее, затем второе наименьшее и так далее. Функция MATCH подсчитывает, сколько строк в столбце M содержат это число и соответствующую строку в вашей таблице данных. Наконец, функция INDEX возвращает значение данных в нескольких строках столбца A.

Скопируйте формулу в ячейку Y2, чтобы получить первый ряд данных, а затем умножьте формулу на строки, указанные в ячейке M1.

Как вернуть несколько совпадающих значений на основе одного или нескольких критериев в Excel?

Функция IF позволяет логически сравнивать значения и ожидаемые результаты. В зависимости от истинности условия она возвращает результат.

= IF(это правда, сделайте это, иначе сделайте другое)

Можно ли проверить сразу несколько условий, когда все условия должны быть истинными или ложными (И), одно условие должно иметь такое-то значение (ИЛИ), или если вы хотите убедиться, что данные не соответствуют условию? Хотя эти функции можно использовать самостоятельно, гораздо чаще они сочетаются с функцией ЕСЛИ.

Читайте так же:
Как быстро преобразовать формат даты в евро в дату в США в Excel?

Используйте функцию IF в сочетании с функциями AND, OR и NOT для оценки нескольких условий.

ЕСЛИ(И()): ЕСЛИ(И(лог_выражение1; [лог_выражение2]; …), значение_если_истина; [значение_если_ложь]))

ЕСЛИ(ИЛИ()): ЕСЛИ(ИЛИ(лог_выражение1; [лог_выражение2]; …), значение_если_истина; [значение_если_ложь]))

ЕСЛИ(НЕ()): ЕСЛИ(НЕ(лог_выражение1), значение_если_истина; [значение_если_ложь]))

Название аргументов

Журнал_выражение (обязательно)

Условие, которое необходимо проверить.

Значение_если_истинный (obligatorio)

Если log_expression имеет значение TRUE, то будет возвращено это значение.

Значение_если_ложно (valgfri)

Значение, которое возвращается, если log_expression равно FALSE.

Общую информацию о том, как использовать эти функции по отдельности, вы можете найти в следующих статьях: AND, OR, NOT. Комбинация этих функций с оператором IF позволяет расшифровать их следующим образом:

И: =If(AND(условие; другое условие); значение, если TRUE; значение, если FALSE)

OR : = SI(OR(условие ; другое условие) ; значение если TRUE ; значение если FALSE)

N OT состоит из: =If(NOT(condition); значение, если TRUE; значение, если FALSE)

Примеры

Ниже приведены примеры частого использования вложенных операторов IF(AND()), IF(OR()) и IF(NOT()). Функции AND и OR поддерживают до 255 различных условий, но рекомендуется использовать только несколько условий, так как формулы с высокой степенью вложенности сложно создавать, тестировать и изменять. Функция NOT может иметь только одно условие.

Примеры использования функции ЕСЛИ с И, ИЛИ и НЕ для оценки текстовых и числовых значений

Ниже приведены формулы и расшифровка их логики.

Если A2 (25) больше нуля, а B2 (75) меньше 100, то возвращается TRUE, иначе — FALSE. В данном случае оба условия имеют значение TRUE, поэтому функция возвращает значение TRUE.

Если A3 («синий») = «красный» и B3 («зеленый») = «зеленый», возвращается TRUE, иначе FALSE. В данном случае истинно только одно условие, поэтому возвращается FALSE.

Если A4 (25) больше нуля или B4 (75) меньше 50, формула возвращает TRUE, в противном случае она возвращает FALSE. Поскольку только первое условие здесь TRUE, а для истинности OR нужен только один аргумент, формула возвращает TRUE.

Читайте так же:
7 простых способов вставить символ дельты в Excel

Если значение A5 («синий») равно «красный» или значение B5 («зеленый») равно «зеленый», возвращается TRUE, иначе возвращается FALSE. В данном случае второй аргумент равен TRUE, поэтому формула возвращает TRUE.

В случае если значение A6 (25) не больше 50, возвращается TRUE, в противном случае возвращается FALSE. В данном случае значение не больше 50, поэтому формула возвращает TRUE.

Если A7 («синий») НЕ «красный», то возвращаемое значение TRUE, иначе FALSE.

Обратите внимание, что во всех примерах за условиями следует закрывающая скобка. Аргументы TRUE и FALSE относятся к внешнему оператору IF. Вы также можете использовать текстовые или числовые значения вместо значений TRUE и FALSE, возвращаемых в примерах.

Использование операторов AND, OR и NOT для оценки дат показано в следующих примерах.

Примеры использования функции ЕСЛИ с И, ИЛИ и НЕ для оценки дат

Вот формулы и объяснение их логики.

Если A2 больше B2, то возвращается TRUE, в противном случае возвращается FALSE. Следовательно, в данном случае формула вернет TRUE, так как 12.03.14 превышает 01.01.14.

Если A3 больше B2 И меньше C2, то возвращается TRUE, в противном случае возвращается FALSE. Из-за истинности обоих аргументов формула возвращает TRUE.

Другими словами, если A4 больше B2 или меньше B2+60, то возвращается TRUE, в противном случае возвращается FALSE. В данном случае первый аргумент — TRUE, а второй — FALSE. Благодаря оператору OR один из аргументов должен быть истинным, чтобы формула была TRUE. С помощью Мастера формул вы сможете увидеть, как Excel вычисляет формулу.

Она возвращает TRUE, если A5 не больше B2, и FALSE в противном случае. Формула возвращает FALSE. Поскольку A5 больше B2, результатом будет FALSE.

Использование операторов И, ИЛИ и НЕ с условным форматированием

Операторы AND, OR и NOT также можно использовать в формулах условного форматирования. Для этого функция IF может быть опущена.

Читайте так же:
Как в Excel разбить предложение на слова?

На вкладке Главная выберите Условное форматирование > Создать правило. Затем можно выбрать опцию Использовать формулу, чтобы определить ячейки для форматирования, ввести формулу и применить формат.

Как будут выглядеть формулы для примеров с датами:

Примеры использования операторов И, ИЛИ и НЕ с условным форматированием

Не предпринимать никаких действий, если A2 больше B2.

Вы должны отформатировать ячейку, если A3 больше B2 И меньше C2, иначе ничего не делайте.

Выполните форматирование, если A4 больше B2 ИЛИ меньше B2 + 60, в противном случае ничего не делайте.

Если A5 НЕ больше B2, ячейка будет отформатирована, иначе никаких действий не будет выполнено. В данном случае A5 больше B2, поэтому формула возвращает FALSE. Если вы измените формулу на =NEW(B2>A5), она вернет TRUE, и ячейка будет отформатирована.

В условном форматировании ввод формул без знака равенства (=) является распространенной ошибкой. На этот раз вы увидите, что диалоговое окно Условное форматирование добавит знаки «равно» в формулу: =»ИЛИ (A4>B2;A4

Дополнительные сведения

Технологическое сообщество Excel всегда готово ответить на вопросы и оказать помощь.

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