Исходя из моего понимания, я предлагаю решение, которое использует немного VBA UDF и вспомогательный столбец.
Небольшой упрощенный пример листа приведен ниже. Соответствующие данные находятся в столбцах C, E, G и I. Справа от каждого из этих столбцов находится столбец Helper, который вы можете при желании скрыть.
Прежде всего, на рабочем листе нажмите 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, дважды щелкните «Редактор формата» и нарисуйте это форматирование для всех применимых столбцов данных.
Обратите внимание, что.
- Excel может иметь ограничение на количество параметров, передаваемых в UDF. Я не слишком уверен, если и как это может применяться, если он объявлен как
ParamArray as Variant - Я предлагаю вам сначала протестировать его в тестовом рабочем листе с образцами данных, имитирующих различные условия, чтобы получить подтверждение того, что это работает, как ожидалось, прежде чем применять его в своем рабочем листе.
- Если вы все еще сталкиваетесь с какими-либо проблемами или если есть какие-либо ошибки, обновите здесь, и я постараюсь исправить это, если позволит время
