Исходя из моего понимания, я предлагаю решение, которое использует немного 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, вам нужно сохранить файл как.Рабочий лист Excel с поддержкой макросов XLSM.
В 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
- Я предлагаю вам сначала протестировать его в тестовом рабочем листе с образцами данных, имитирующих различные условия, чтобы получить подтверждение того, что это работает, как ожидалось, прежде чем применять его в своем рабочем листе.
- Если вы по-прежнему сталкиваетесь с какими-либо проблемами или если есть какие-либо ошибки, обновите их здесь, и я постараюсь исправить их, если позволит время.
