Индекс Excel / формула соответствия, но диапазон индекса меняется ежемесячно

Question

Индекс Excel / формула соответствия, но диапазон индекса меняется ежемесячно

Моя формула для получения значения "Совокупный доход" для каждой компании:

=IFERROR(INDEX(YTD2016!$H$19:$ZZ$19,MATCH(RECAP!$A14,YTD2016!$H$4:$ZZ$4,0)),0)

Работает красиво. Никаких проблем. ЛЮБИТЕ Индекс / Матч.

Проблема в указателе диапазона индекса $H$19:$ZZ$19 может измениться на строку 20 в следующем месяце, если будут добавлены новые строки (каждый месяц вставляется новый отчет, поэтому он не знает, что ячейки были перемещены). Если бы я делал это каждый месяц, достаточно просто найти и заменить все, и все готово, но я нет, а другие пользователи не могут.

Поэтому я стараюсь, чтобы это работало каждый месяц, несмотря ни на что. Я нашел способ вернуть номер строки с помощью Match("Total Income"...), но есть ли способ найти строку текста "Общий доход", чтобы получить номер строки в моей формуле индекса?

Так как строка соответствия работала, возвращая номер строки, я даже пытался поместить это уравнение в диапазон индекса (...$H$MATCH("Общий доход", "Ежемесячно по классам"!D:D,0). Работает не так, как ожидалось, но я просто хотел, чтобы он поставил туда строку 19. Должен быть способ.

^{Индексный файл}

1

microsoft-excel worksheet-function search-indexing

Источник

Shelly Billings 23 мар '17 в 04:59

2 ответа

Другие вопросы по тегам microsoft-excel worksheet-function search-indexing

Alex M 28 фев '19 в 20:10 2019-02-28 20:10 · Answer 1 · 2019-02-28 20:10

Я даже пытался поместить это уравнение в индексный диапазон (...$H$MATCH("Total Income", "Monthly by Class"!D:D,0). Это не сработало, как ожидалось, но я просто хотел это поставить строку 19. Там должен быть способ.

Это действительно так, и у тебя это было. Вам просто нужно использовать INDIRECT в любое время, когда вы хотите использовать формулу для генерации ссылки на ячейку, в отличие от простого удаления формулы в середине ссылки на ячейку. Это великолепно! INDIRECT использует двойные кавычки для построения ссылки на ячейку в виде текста.

Так что, если это вся ваша, действующая формула:

=IFERROR(INDEX(YTD2016!$H$19:$ZZ$19,MATCH(RECAP!$A14,YTD2016!$H$4:$ZZ$4,0)),0)

Ваша текущая жестко заданная ссылка на ячейку для диапазона INDEX является:

YTD2016!$H$19:$ZZ$19

И мы просто хотим заменить номера строк на простые MATCH,

Давайте посмотрим на это так

YTD2016!$H$ 19 :$ZZ$ 19

и поменять в MATCH формула для значений строки

YTD2016!$H$ MATCH("Total Income",'Monthly by Class'!D:D,0) :$ZZ$ MATCH("Total Income",'Monthly by Class'!D:D,0)

Оберните это INDIRECT и использовать кавычки и амперсанды для построения текстовой строки

INDIRECT( "YTD2016!$H$"& MATCH("Total Income",'Monthly by Class'!D:D,0) &":$ZZ$"& MATCH("Total Income",'Monthly by Class'!D:D,0) )

Положил все это вместе

INDIRECT("YTD2016!$H$"&MATCH("Total Income",'Monthly by Class'!D:D,0)&":$ZZ$"&MATCH("Total Income",'Monthly by Class'!D:D,0))

и вернуться на свое место в общей функции

=IFERROR(INDEX(INDIRECT("YTD2016!$H$"&MATCH("Total Income",'Monthly by Class'!D:D,0)&":$ZZ$"&MATCH("Total Income",'Monthly by Class'!D:D,0)),MATCH(RECAP!$A14,YTD2016!$H$4:$ZZ$4,0)),0)

..и Роберт брат твоей матери.

fisknils 23 мар '17 в 06:42 2017-03-23 06:42 · Answer 2 · 2017-03-23 06:42

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

В качестве примера,

=ADDRESS(1,1)

вернул бы $1 $

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

Я надеюсь, что это помогает