Условное выделение Excel на основе других ячеек

Question

У меня проблемы с условным выделением.

Сценарий: у меня есть список идентификаторов (A1), каждый из которых соответствует одному имени (B2). Для каждого идентификатора может быть один или несколько кодов (C2), соответствующих ему. Таким образом, я хотел бы выделить каждый идентификатор, в котором указано более одного кода. В приведенном мной примере снимка я выделил бы строку 5 или ячейку A5, потому что с ней связаны C5, C9, C13 и C24. После выделения строки 5 или ячейки A5 я могу фильтровать по цвету ячейки, чтобы оценить значения в столбцах E и F.

Пожалуйста, смотрите обновленное изображение для справки, ячейки не объединены.

ID  Name    Code    Name    #   Alpha
1234    Doe, Jane   WORK    WORK 1  5017    ABC
                11147   DEF
5678    Doe, John   OUT OUT 1   5014    JKL
                5017    MNO
                11147   PQRS
                16000   TUV
        OPEN    OPEN 1  5010    DEZ
                5017    ABC
                11123   CCC
                16000   TUV
        CLOSED  CLOSED 1    2665    BB
                5003    DD
                5004    EE
                5005    FF
                5006    GG
                5007    HH
                5008    II
                5009    JJ
                11052   JQR
                11121   RQJ
                11124   GIH
        LIFT    LIFT 1  5003    DD
                5004    EE
                5005    FF
                5006    GG
                5007    HH
                5008    II
                5009    JJ
                11052   JQR
                11121   RQJ
                11124   GIH
                15022   BLUE
8876    Blue, Jean  WORK    WORK 1  5017    ABC
                11147   DEF
                16000   GHI

Answer 1

К вашему сведению, наличие двух столбцов под названием « Name очень сбивает с толку.

Я не могу придумать простой способ выделить строку R5 потому что B5 не имеет прямой связи с C9 . Это должна быть грязная формула, которая проверяет, сколько пустых ячеек ниже. Гораздо более простым решением было бы выделить любые дополнительные записи Code , то есть те, которые непосредственно не примыкают к Name:

Для этого вы применяете условное форматирование к столбцам C и D (вы можете применить его ко всей ширине таблицы, если хотите). Вы выбираете правило highlight и Use a formula to determine which cells to format (я на Mac, использую другую версию Excel, поэтому ваше диалоговое окно может выглядеть немного иначе):

И вы применяете правило:

=AND(ISBLANK($B1),NOT(ISBLANK($C1)))

Это говорит выделить любую ячейку , где Bx является пустым и Cx не является пустым. Знаки доллара важны.

Answer 2

Для моей собственной практики я хотел посмотреть, можно ли / как это сделать в своем первоначальном намерении. Как и следовало ожидать, это ужасный беспорядок

=AND(NOT(ISBLANK($B2)),SUMPRODUCT(--($C2:INDIRECT("$C"&MIN(IF(NOT(ISBLANK($B3:INDIRECT("$B"&ROW()+50))),ROW($B3:INDIRECT("$B"&ROW()+50))-1)))<>""))>1)

Я разобью его по частям.

Для каждого значения в столбце B мы хотим смотреть вниз, пока не найдем другое значение. Используя приведенный выше пример, если мы смотрим на B2, мы смотрим дальше вниз, пока не увидим следующее значение на B5. Я не могу найти функцию Excel, чтобы «продолжать поиск, пока он что-то не найдет», поэтому мы всегда будем смотреть на 50 строк вниз.

Мы можем сгенерировать этот диапазон по формуле:

=$B2:INDIRECT("$B"&ROW()+50)

Примечание: этот диапазон не имеет смысла, если вводить непосредственно в одну ячейку.
INDIRECT генерирует ссылку на ячейку из предоставленного текста.

Чтобы найти следующую заполненную (не пустую) ячейку, нам нужно работать с массивами. Чтобы сэкономить место, я пока жестко закодирую диапазон. Я использовал пример кода с этой страницы:

=MIN(IF(NOT(ISBLANK(B2:B52))), ROW(B2:B52))

Обратите внимание, что при вводе формул, содержащих массивы, в ячейки таблицы необходимо использовать сочетание клавиш CTRL - SHIFT - ENTER (необязательно для ввода формулы условного форматирования). Нормальный ENTER даст разные результаты и приведет к тому, что вы вырвете волосы Поверь мне на слово.

Поэтому найдите все ячейки в указанном диапазоне и убедитесь, что они не пустые. Если они не являются пустыми, вернуть минимальный номер строки этого элемента. За исключением того, что это будет включать B5 , и мы действительно хотим просмотреть 1 ячейку назад, поэтому ROW - 1:

=MIN(IF(NOT(ISBLANK(B2:B52))), ROW(B2:B52) - 1)

За исключением, конечно, наша настоящая формула сейчас:

=MIN(IF(NOT(ISBLANK(B2:INDIRECT("$B"&ROW()+50)))), ROW(B2:INDIRECT("$B"&ROW()+50)) - 1)

Затем нам нужно проверить, есть ли в столбце C более одного значения в диапазоне от C2 до C4 . Этот умный код проверяет, все ли ячейки в диапазоне пустые:

=SUMPRODUCT(--(range<>""))=0

range<>"" проверяет, не равна ли каждая ячейка в диапазоне "" (пусто). Это вернет массив логических значений, таких как:

{TRUE,FALSE,TRUE}

Двойной дефис затем преобразует значения TRUE FALSE в единицу и нули:

{1,0,1}

Где 1 в этом массиве соответствуют ячейкам, которые не являются пустыми или пустыми. SUMPRODUCT добавляет (суммирует) все элементы в массиве. Если ваш конечный результат не равен нулю, то были заполненные ячейки.

Конечно, мы ожидаем одно значение в C2 которое соответствует записи в B2 , поэтому мы ожидаем 1 значение. Мы изменим это на это:

=SUMPRODUCT(--(range<>""))>1

Связав все это вместе, мы получим:

=SUMPRODUCT(--($C2:INDIRECT("$C"&MIN(IF(NOT(ISBLANK($B3:INDIRECT("$B"&ROW()+50))),ROW($B3:INDIRECT("$B"&ROW()+50))-1)))<>""))>1

ЗА ИСКЛЮЧЕНИЕМ это вернет true не только для строк, содержащих имя / идентификатор. Поэтому нам нужно обернуть его в некоторые дополнительные проверки:

=AND(NOT(ISBLANK($B2)), the rest)

И это ваша окончательная формула, дающая ИСТИНА или ЛОЖЬ, как и ожидалось:

=AND(NOT(ISBLANK($B2)),SUMPRODUCT(--($C2:INDIRECT("$C"&MIN(IF(NOT(ISBLANK($B3:INDIRECT("$B"&ROW()+50))),ROW($B3:INDIRECT("$B"&ROW()+50))-1)))<>""))>1)

ЗА ИСКЛЮЧЕНИЕМ ОБЩЕГО ФОРМАТИРОВАНИЯ НЕ ПРИНИМАЕТЕ ЕГО

Мне пришлось добавить еще один столбец (который можно скрыть), чтобы сохранить результат, и на этом основывать условное форматирование.

Рекомендации:
КОСВЕННЫЕ
СТРОКА
MIN
ЕСЛИ
НЕ
ISBLANK
SUMPRODUCT