Я пытаюсь ссылаться на одну строку, другой статус и соответствующую им дату. Каждое из этих значений хранится в столбцах с присоединенными (= дублирующимися) ссылками, и под каждым статусом имеются пустые ячейки. Единственная хорошая вещь, значения утверждаются от самого большого до самого маленького под каждым соответствующим статусом. Следовательно, мне нужно сопоставить определенное условие, чтобы получить значение Nth (наименьшее или наибольшее) вместо первого соответствия.
Вот один из способов использования формул массива. Вместо использования значений заголовков (инициированных, отозванных и т.д.) Я создал отдельные формулы для каждого столбца. Это упрощает подход и не должно быть обременительным, если у вас не много столбцов статуса. Я также предполагаю, что вы хотите, чтобы эти формулы начинались в строке 3.
Примечание. Обе эти формулы являются массивами, и вам нужно будет ввести их как Ctrl+Shft+Enter (CSE).
Поместите их в обозначенную ячейку, CSE, а затем перетащите их вниз.
K3:
{=IFERROR(INDEX($E$2:$E$14,MATCH(1,(I3=$C$2:$C$14)*(MIN((IF($E$2:$E$14="",1000000,$E$2:$E$14))*(IF(I3=$C$2:$C$14,1,1000000)))=$E$2:$E$14),0),0),"")}
Чтобы заставить MIN работать должным образом в формуле массива, мне пришлось использовать операторы IF которые возвращают 1,000,000 когда он не соответствует вашей ссылке или является пустым. Это значение является произвольным числом и должно быть больше, чем любое значение даты. (Помните, даты хранятся в виде чисел, и 1,000,000 будет датой где-то в 4637 году!) Без этого функция MIN находит самое низкое значение в массиве, которое будет 0 .
L3:
{=INDEX($F$2:$F$14,MATCH(1,(I3=$C$2:$C$14)*(MAX(($F$2:$F$14)*(I3=$C$2:$C$14))=$F$2:$F$14),0),0)}
Обратите внимание, что формула в столбце L вернет 0 если ничего не найдено. Вместо того, чтобы усложнять формулу, вы можете просто установить форматирование на dd.mm.yy;;;@ , которое будет скрывать нулевое значение. Формула в столбце K вернет ошибку, если ничего не найдено, поэтому я обернул ее в оператор IFERROR .
Как это устроено:
Запишите эту формулу в ячейке G2 и заполните.
{=IFERROR(INDEX($A$2:$F$11, MATCH(0,COUNTIF($G$1:G1, $A$2:$A$11), 0)),"")}
NB Это формула массива, которая должна быть подтверждена с помощью Ctrl+Shift+Enter .
В ячейке H2 напишите эту формулу массива, подтвердите нажатием Ctrl+Shift+Enter и заполните.
{=Text(MIN(IF(($A$2:$A$11=G2)*($C$2:$C$11>0),$C$2:$C$11," ")),"dd.mm.yy;;;@")}
Запишите эту формулу массива в ячейке I2 нажмите Ctrl+Shift+Enter и заполните ее.
{=Text(MAX(IF(($A$2:$A$11=G2)*($D$2:$D$11>0),$D$2:$D$11,"")),"dd.mm.yy;;;@")}
Формула массива в ячейке J2 должна быть завершена нажатием Ctrl+Shift+Enter и заполнением вниз.
{=IFERROR(INDEX($E$2:$E$11, MATCH(0,COUNTIF($J$1:J1, $E$2:$E$11), 0)),"")}
Замечания:
Уважаемые, спасибо за ваши ответы, я нашел более простой способ сопоставить и извлечь эти данные.
Формула для получения наименьшего значения:= IFERROR(LOOKUP(1000000; 1/(($ A $ 2:$ A $ 3169 = G2)*($ B $ 2:$ B $ 3169 <> "")); $ B $ 2:$ B $ 3169); "")
Формула для получения наибольшего значения = IFERROR(INDEX(D $ 2:D $ 3149; MATCH(1; -($ A $ 2:$ A $ 3149 = $ G2)* -(D $ 2:D $ 3149 <> ""); 0); 1); "")
С уважением, Решение
