Я работаю над проблемой Excel уже почти 24 часа. У меня есть формула, которая почти делает то, что мне нужно, но она не возвращает то, что, как я себе представляю, она должна возвращать:
=INDEX(Data!D:D,SUMPRODUCT((Data!C:C=Total!A5)*(Data!A:A="Total")*(Data!B:B="Total")*(ROW(Data!D:D))))
Я предполагаю, что строка для индексирования столбца D на листе Data! — это фактическая строка, в которой происходит совпадение, но ДЛЯ КАЖДОГО ЗНАЧЕНИЯ, КРОМЕ ПЕРВОГО, возвращаемое значение берется из 3 строк ниже.
Да, поэтому для первой ячейки, в которой я применяю эту формулу, я получаю обратно правильное значение из столбца D данных!. Но для каждой последующей ячейки с формулой я получаю обратно значение из столбца D данных! на 3 строки ниже совпадения.
Я перепробовал все комбинации вычитания 3 из формулы (чтобы вернуть нужную строку), но безрезультатно. Но это все равно не объясняет, почему первая ячейка, в которой применяется формула, возвращает ожидаемое значение!
Конечно, любая помощь будет высоко оценена...
решение1
Мне не совсем понятно, хотите ли вы найтипервый, второй и т.д.совпадения с Total!A5 или если вы пытаетесь найти первое совпадение с A5, то первое совпадение с A6 и т. д. Это предлагаемое решение предназначено для первого случая.
Я предпочитаю использовать метод математического исключения для захвата множественных возвратов по совпадениям. Если вы исключите то, что не совпадает, то то, что останется, должно быть набором результатов совпадения. Я также достаточно нетерпелив, чтобы считать задержку вычисления ссылок на полные столбцы раздражающей, SUMPRODUCTпоэтому я обрезал ваши ссылки на диапазоны до первых ~1K строк.
=IFERROR(INDEX(Data!$D$2:$D$999, SMALL(INDEX(ROW($1:$998)+((Data!$C$2:$C$999<>Total!$A$5)+(Data!$A$2:$A$999<>"Total")+(Data!$B$2:$B$999<>"Total"))*1E+99,,),ROW(1:1))),"")
Это стандарт (не массив) формула. Заполните по мере необходимости, чтобы поймать все соответствующие возвраты. Когда вы расшифруете это для своих собственных целей, помните, что ROW(1:998)этоположение в пределах Data!D2:D999, а не фактический номер строки на рабочем листе. ROW(1:1)это просто счетчик, который увеличивается по мере заполнения. Иногда a COUNTIFболее уместен и может использоваться для захвата нескольких соответствующих возвратов для нескольких значений поиска.