Получить номер строки формулы MIN IF

Question

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

У меня есть эта формула:

Между двумя листами A находится идентификатор B является датой C другой идентификатор на листе 2

{=MIN(IF(Sheet2!A:A=A1,B1-Sheet2!B:B,""))}

Я хочу знать, какой ряд в Sheet2!B:B используется для вычисления формулы, а затем возвращает значение в столбце C листа 2 для этой строки.

Может быть, я мог бы использовать INDEX или MATCH если бы знал только номер строки, используемый в расчете!

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

Answer 1

В первую очередь, это вовсе не хорошая идея , чтобы использовать целые ссылки столбцов в формуле массива (или даже любую другая не-CSE функцию массива обработки, такие как SUMPRODUCT AGGREGATE и т.д.).

В отличие от некоторых функций, например, COUNTIF(S) , SUMIF(S) , использование целых ссылок на столбцы которых практически не влияет на производительность вычислений, функции обработки массива должны рассчитывать все переданные им ячейки . И это в равной степени относится к тем ячейкам, которые находятся за пределами последней непустой ячейки в передаваемом диапазоне.

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

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

Что касается вашей проблемы, вы можете использовать следующую формулу массива:

=INDEX(Sheet2!C:C,MATCH(MIN(IF(Sheet2!A1:A100=A1,B1-Sheet2!B1:B100)),IF(Sheet2!A1:A100=A1,B1-Sheet2!B1:B100),0))

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

Также обратите внимание на повторение условного оператора в MATCH lookup_array, критической точке, которая пропускается невероятное количество раз в предлагаемых решениях такого рода проблемы, ошибка заключается в простом использовании:

=INDEX(Sheet2!C:C,MATCH(MIN(IF(Sheet2!A1:A100=A1,B1-Sheet2!B1:B100)),B1-Sheet2!B1:B100,0))

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

С уважением