У меня есть две таблицы со значениями в столбцах A и B. Для обеих таблиц A является числовым значением, а B является текстовым значением. Пример ниже. Я хочу выделить ячейки в таблице 2, столбец B, для которых ячейка в столбце Table2-B соответствует ЛЮБОЙ ячейке в столбце Table1-B И ячейка Table2-столбца A больше, чем ячейка в Table1-столбце A для соответствующего соответствия. (Таким образом, ниже таблицы 2 будут выделены ячейки B2, B4 и B5).
Это возможно?
Вы можете использовать формулу в условном форматировании, чтобы сделать выделение для соответствующего значения в столбце B table2 и более высокого значения в той же строке для столбца A:
=COUNTIFS(Sheet1!$B$1:$B$3,B1,Sheet1!$A$1:$A$3,"<"&A1)>0В этой формуле:
=COUNTIFS(Sheet1!$B$1:$B$3,B1,Sheet1!$A$1:$A$3,"<"&A1)>0
Лист1!$ B $ 1:$ B $ 3 - это столбец B в Таблице 1, измените его, чтобы он соответствовал вашим ссылкам
B1 - первая ячейка ваших данных в столбце B таблицы2, если ваши данные начинаются с B2, вы должны изменить их на B2
Да, это возможно с помощью условного форматирования.
Вы показали таблицы 1 и 2 выше, как будто они находятся на одном листе, но обе таблицы начинаются с колонки А, что невозможно, если они не на отдельных листах. Ниже я покажу решения для обоих случаев.
Начиная с обеих таблиц на одном листе, выберите E1:E5 и выберите «Условное форматирование ...» в меню "Формат". Нажмите знак "плюс", чтобы создать новое правило, и выберите "Классический" в раскрывающемся списке "Стиль". В другом раскрывающемся списке выберите «Использовать формулу ...» и введите или вставьте его в поле формулы:
=AND(COUNTIF(B$1:B$3,E1)>0,D1>IFERROR(OFFSET(B$1,MATCH(E1,B$1:B$3,0)-1,-1),0))
(Обратите внимание, что вы также можете вставить эту формулу в ячейку на рабочем листе, чтобы проверить ее. Он должен возвращать FALSE, TRUE, FALSE, TRUE, FALSE при вставке в строки 1 - 5 соответственно).
Вот как это работает: COUNTIF() ...> 0 возвращает TRUE, если значение в столбце E встречается в B1: B3. MATCH() находит положение значения в столбце E в диапазоне B1: B3, а OFFFSET() находит соответствующее значение в столбце A, а IFERROR() избавляется от значений # N/A. Если D1 больше, чем число в столбце A, эта часть формулы возвращает TRUE, а AND() возвращает TRUE, если обе части верны: значение в столбце E должно быть в B1: B3 AND, значение в столбце D должно быть больше, чем значение в столбце A, соответствующее значению в столбце B, которое совпадает со значением в столбце E.
Здесь это приводит к выделению E2 и E4.
Теперь, если Таблица 2 находится на другом Листе, мы должны использовать функцию INDIRECT() вместе со ссылками на Лист / ячейку в кавычках, например:
=AND(COUNTIF(INDIRECT("Sheet1!B$1:B$3"),B1)>0,A1>IFERROR(OFFSET(INDIRECT("Sheet1!B$1"),MATCH(B1,INDIRECT("Sheet1!B$1:B$3"),0)-1,-1),0))
Изменить: Может быть, это зависит от версии Excel (я использую Excel 2011 для Mac), но оказывается, что ссылки INDIRECT () не нужны:
=AND(COUNTIF(Sheet22!B$1:B$3,B1)>0,A1>IFERROR(OFFSET(Sheet22!B$1,MATCH(B1,Sheet22!B$1:B$3,0)-1,-1),0))
Это дает правильное форматирование для таблицы 2, расположенной в столбцах A и B на другом листе, как показано ниже:
