Мне нужно извлечь максимальное значение из переменного количества строк и столбцов, где заголовок строки равен определенному значению; 300 в приведенном выше примере вернет максимальное значение 15.
Я пытаюсь создать формулу, в которой заголовок строки (например, 300) был бы переменной, чтобы можно было вернуть максимальное значение для заголовков строк 300 или 600 и так далее.
Как получить диапазон или массив всех строк с заголовком 300, чтобы можно было использовать функцию Max для этого диапазона?
решение1
Положите вашу300вФ1, затем вД1поместите формулу массива:
=MAX(IF(A1:A100=F1,B1:C100))
Формулы массиванеобходимо вводить с помощью Ctrl+ Shift+, Enterа не просто с помощью Enterклавиши. Если это сделано правильно, формула появится в фигурных скобках на панели формул.
Из-за колонныАкритерии,101в ряду14игнорируется.
решение2
Если у вас Office 365 Excel, обычная формула:
=MAX(MAXIFS(B:B,A:A,300),MAXIFS(C:C,A:A,300))
Вы можете заменить оба 300 на адрес ячейки. Поскольку это не формула массива, она позволит ссылаться на полные столбцы без ущерба.
решение3
Вот несколько более общий ответ:
Предположим, вы хотите вычислить МАКСИМУМ (или МИНИМУМ, или МЕДИАНУ и т. д.) числовых значений, появляющихся в столбцах C и D, для непрерывного подмножества строк i-j в массиве, простирающемся от строк 6 до N, со значением «строка», введенным в столбец B. Пусть явное значение i будет указано в B2, а значение j — в B3 (--возможно, эти значения были вычислены в другом месте вашей электронной таблицы, априори); здесь, ради прозрачности, пусть i = 9 и j = 14, где n = 15.
Вот разработанный мной код Excel2003, который, похоже, работает отлично:
{=MAX(IF(B6:B15>=B2,IF(B6:B15<=B3,C6:C15),""))} , что возвращает MAX(C9:C14) ; и,
{=MAX(IF(B6:B15>=B2,IF(B6:B15<=B3,D6:D15),""))} , что возвращает MAX(D9:D14) .
Просто измените MAX на MIN или другое имя функции, чтобы выполнить аналогичные вычисления. Но не забудьте переустановить (Ctrl-Shift-Enter) эти "фигурные скобки", которые будут исчезать каждый раз, когда вы будете манипулировать оператором.