Условное форматирование на 4-й записи?

566
James

Я знаю, как изменить цвет ячейки для дублированной записи, но как / можно изменить цвет для каждой четвертой записи? Значение будет представлять собой комбинацию с неизвестным числом и буквой, которая будет выделяться каждый 4-й раз, когда вводится одно и то же сочетание.

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

Я вручную выделил 4-е повторение букв / цифр комбо C020, G020, B004 и F028

Как видите, повторы не обязательно будут происходить в одном ряду или после 4 столбцов.

http://s000.tinyupload.com/?file_id=56226468952646159686

0
Означает ли это, что 5-й повтор не должен быть выделен? Следует выделить 4 повторения, затем 8 повторений и так далее? pat2015 5 лет назад 0
Можете ли вы предоставить пример данных? Пожалуйста, посмотрите на [спросить] и возьмите наш [тур], чтобы узнать, как улучшить ваш вопрос. Burgi 5 лет назад 0
Спасибо pat2015, это правильно, 5-7 не будет выделено, 8 будет и т. Д. James 5 лет назад 0
Какой программой ты пользуешься? Вы смотрели на тестирование номера строки, чтобы увидеть, является ли оно кратным 4? fixer1234 5 лет назад 0
Привет fixer1234 Я использую Excel 2016, соответствующие данные находятся в неактивном столбце, повторение может не произойти в течение 15 дней / столбцов, это может произойти 7-го или 10-го и т. Д., Оно не является единообразным. На данный момент мы используем 2 листа Excel, открытых бок о бок, и вручную проверяем каждую запись, чтобы выделить 4-е событие тех же данных. James 5 лет назад 0
Если вы хотите просто выделить каждую четвертую запись повторов, то вспомогательный столбец и формула могут помочь или могут проверять номер строки, кратный 4, как указано в fixer1234. Однако для каждого отдельного 4-го повтора, если вам нужен другой цвет, он может стать сложным и, возможно, потребуется решение VBA (программирование). pat2015 5 лет назад 0
Продолжение ... например, 1,2,2,1,1,1 (4-й 1 с красным), 2,3,2 (4-й с 2 ​​красным) и так далее. Но если вы хотите один цвет для 4-го 1 и другой для 4-го 2, то это сложно. Пожалуйста, подробно опишите свой вопрос. Отредактируйте его, чтобы предоставить больше информации, возможные образцы данных и ожидаемый результат. Также добавьте любой подход или формулу, которые вы, возможно, пробовали до сих пор, которые не сработали. pat2015 5 лет назад 0
Спасибо, pat2015. Я буду исследовать вспомогательные колонки и отредактирую оригинальный вопрос более подробно. James 5 лет назад 0
Тоже хочешь через колонки? например, столбцы CEG & I, т.е. говорят, что запись B004 встречается 3 раза в столбце C, затем 4-й столбец появляется в столбце E (что следует выделить) и т. д.? pat2015 5 лет назад 0
Привет pat2015 записи не должны повторяться в том же столбце, если они есть, это ошибка. (Мы используем правило дублирования для перекрестной проверки). Повторные записи, вероятно, будут находиться в разных строках. James 5 лет назад 0

2 ответа на вопрос

0
Scott

Я не совсем понимаю , что вы хотите, потому что образец таблицы, которую вы предоставили, похоже, не имеет никакого отношения к вашему вопросу. Вы говорите: «соответствующие данные находятся в сером столбце», но я не вижу повторяющихся значений в сером столбце. Вы имеете в виду «RETURN», «RETURN / DC», «TOTAL» и «TOTAL / DC», которые повторяются исключительно в столбцах, не выделенных серым цветом?

Но данные, которые вы предоставляете в (запутанной / неясной) форме повествования, дают мне возможность поработать. Я предполагаю, что числа находятся в строке 1. Начнем с техники обнаружения дублированных записей:

=COUNTIF($A1:B1,B1) 

который подсчитывает, сколько раз значение в этой ячейке появилось в строке до текущей ячейки включительно . Это будет 1 для первого вхождения значения и 2 или более для дубликатов. Но вы не хотите проверять, больше ли это число, чем 1; Вы хотите проверить, является ли это кратным 4. Итак, тест

=MOD(COUNTIF($A1:B1,B1),4)=0 

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

Ниже

  • Строка 1 - это ваши данные (из вопроса), условно отформатированные на основе второй формулы, приведенной выше,
  • Ряд 2 - первая формула выше, и
  • Ряд 3 - вторая формула выше.

spreadsheet with OP's data

Таким образом, строка 2 показывает количество повторений в строке 1, а строка 3 показывает столбцы, где строка 2 кратна 4 (и это столбцы, где строка 1 окрашена).

Привет, Скотт, спасибо за твою помощь, первая строка выглядит ближе всего к тому, что я ищу. Я разместил ссылку в оригинальном вопросе выше с желаемым результатом. James 5 лет назад 0
0
pat2015

Исходя из моего понимания, я предлагаю решение, которое использует немного VBA UDF и вспомогательный столбец.

Небольшой упрощенный пример листа приведен ниже. Соответствующие данные находятся в столбцах C, E, G и I. Справа от каждого из этих столбцов находится столбец Helper, который вы можете при желании скрыть.

enter image description here

Прежде всего, на рабочем листе нажмите ALT+, F11чтобы получить доступ к VBA Editor. Вставьте модуль из меню вставки и вставьте в него следующий код UDF (пользовательская функция).

Function prmarr(ParamArray arg()) As Variant  Dim arr1 cnt = 0  For i = LBound(arg) To UBound(arg)  cnt = cnt + arg(i).Rows.Count ' get total rows from all ranges  Next i  ReDim arr1(cnt) ' re dim the array for those many total rows  cnt = 0 ' reuse the counter now  'create a one dimentional list of array from all of the above ranges For i = LBound(arg) To UBound(arg) For Each cell In arg(i) arr1(cnt) = cell.Value cnt = cnt + 1 Next cell Next i  prmarr = arr1 ' pass this array as return parameter End Function 

Обратите внимание, что это очень простой код VBA, и в нем нет никаких проверок или проверки ошибок. Если вы передадите горизонтальный массив или перекрывающиеся массивы или многомерные массивы, это может дать сбой. Предполагается, что для правильной работы вы передадите ему только массив столбцов.

Эта функция принимает переменное число диапазонов массива столбцов и возвращает одномерный массив, который содержит все значения ячеек из него, которые мы будем использовать для подсчета общего числа вхождений текущего значения с момента запуска ячейки из первого столбца данных.

Так как в вашем Excel есть код VBA, вам нужно сохранить файл как .XLSM Macro Enabled Excel Worksheet.

В D1 поместите следующую формулу и перетащите ее вниз к намеченным строкам.

=COUNTIF($C$1:C1,C1) 

Теперь, когда вы прогрессируете через последующие колонны помощников. Каждый столбец Helper требует небольшого изменения формулы. Хотя структура остается неизменной, количество аргументов увеличивается.

В F2 поместите следующую формулу и нажмите CTRL+ SHIFT+ ENTERвнутри панели формул, чтобы создать формулу массива. Excel теперь заключит формулу в фигурные скобки, чтобы указать, что это формула массива. Этот шаг, создание формулы массива, необходим, иначе он даст неверный результат.

=SUM(IF(prmarr(C$1:C$9,E$1:E1)=E1,1,0)) 

Поймите эту формулу. Вы передаете C1: C9 и E $ 1: E1 в качестве параметров в UDF, т. Е. Предыдущие столбцы (столбцы) + текущее значение первого столбца до значения условия теста и проверяете, совпадает ли текущая ячейка. Если да, SUM будет производить общее количество этого значения с начала первого столбца. Перетащите его вниз до предполагаемых рядов.

Точно так же теперь формула массива в H1 становится

=SUM(IF(prmarr(C$1:C$9,E$1:E$9,G$1:G1)=G1,1,0)) 

И так далее.

Заполните это для всех столбцов.

Теперь перейдем к условному форматированию.

В этом случае выберите самую первую ячейку, т.е. С1. Перейдите в Условное форматирование -> Новое правило -> Используйте формулу, чтобы определить, какие ячейки форматировать.

Теперь в правиле ставим следующую формулу

=MOD(D1,4)=0 

Выберите цвет фона по вашему выбору и нажмите OK, чтобы применить форматирование к ячейке C1.

Теперь, когда выбрано С1, дважды щелкните «Редактор формата» и нарисуйте это форматирование для всех применимых столбцов данных.

enter image description here

Обратите внимание, что.

  • Excel может иметь ограничение на количество параметров, передаваемых в UDF. Я не слишком уверен, если и как это может применяться, если он объявлен какParamArray as Variant
  • Я предлагаю вам сначала протестировать его в тестовом рабочем листе с образцами данных, имитирующих различные условия, чтобы получить подтверждение того, что это работает, как ожидалось, прежде чем применять его в своем рабочем листе.
  • Если вы все еще сталкиваетесь с какими-либо проблемами или если есть какие-либо ошибки, обновите здесь, и я постараюсь исправить это, если позволит время

Похожие вопросы