Подсчитать максимальные значения по сравнению с предыдущим массивом

289
R.T.

Я пытаюсь посчитать количество значений в строке, которые равны, больше, чем значение MAX () (часть) столбца выше.

Например: по сравнению с 3 ячейками над собой. В Е приходит «граф». я пометил клетки, которые он должен считать *.

enter image description here

Мой окончательный массив размером около 200x5000 ... Я уже пробовал (например, ячейка E4): 

{=SUM(IF(A4:D4>=MAX(OFFSET(A1,ROW($1:$4)-1,0,3,1)),1,0))}  {=COUNTIF(A4:D4,">="&MAX(OFFSET(A1,0,ROW($1:$4)-1,3,1)))}

Надеясь, что функция ROW () и OFFSET () разделит функцию MAX по столбцам ... Но ни один из них не даст достойного результата. Конечно, я могу разделить MAX () на 200 различных столбцов на другом листе. Но я получаю кошмары, потому что я не могу сделать это в одной формуле массива.

1

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

2
pat2015

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

Этот UDF принимает Range и возвращает массив Max каждого столбца в этом диапазоне.

Нажмите ALT+ F11для доступа к VBA Editor. Вставьте модуль из меню вставки. Теперь вставьте в него следующий код.

Function Ret_Arr(inpt As Range) As Variant   Dim v As Variant  ReDim v(1 To inpt.Columns.Count)  For i = 1 To inpt.Columns.Count  v(i) = Application.Max(inpt.Columns(i))  Next i  Ret_Arr = v   End Function

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

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

Сохраните код VBA и рабочую таблицу в виде рабочей таблицы с поддержкой макросов XLSM на случай, если вы используете Excel 2007 и выше.

В этом примере пример данных находится в ячейках A1: D8. В E2 поместите следующую формулу и нажмите CTRL+ SHIFT+ на панели формул, ENTERчтобы создать формулу массива.

Формула должна быть заключена в фигурные скобки, чтобы указать, что это формула массива

=SUM(IF($A2:$D2>=Ret_Arr($A$1:D1),1,0))

Теперь для каждого диапазона выше Ret_Arr должен возвращать массив Max каждого столбца. Если вы не передадите правильный диапазон в Ret_Arr, вы можете получить ошибочные результаты. Поэтому, если ваши столбцы имеют значения от A до D, убедитесь, что передали UDF одинаковый диапазон столбцов от A до D.

Работаю, отлично 10 ☺ Rajesh S 5 лет назад 0
Благодарю. Я пойду с VBA, это элегантное решение. Но я все еще удивлен, что это не может быть сделано на основе формулы. R.T. 5 лет назад 0
0
XOR LX

В E4, формула массива **:

=COUNT(1/(MMULT(TRANSPOSE(ROW(A$1:D3)^0),N(A4:D4>=A$1:D3))=ROWS(A$1:D3)))

Скопируйте при необходимости.

С уважением

** Формулы массива вводятся не так, как «стандартные» формулы. Вместо того, чтобы просто нажимать ENTER, вы сначала удерживаете CTRL и SHIFT, и только потом нажимаете ENTER. Если вы сделали это правильно, вы заметите, что Excel заключает в формулу фигурные скобки {} (хотя не пытайтесь вставить их вручную).

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